`scripts/price-sheets/load-price-sheets.py`

138 lines
#!/usr/bin/env python3
"""
load-price-sheets.py — ingest every GDrive vendor price sheet into
dw_unified.vendor_price_sheets (staging). Keyword-detects the header row + the
SKU / wholesale / retail / unit columns per sheet (formats vary wildly: header in
row 1 or 2, Italian 'Listino', multi-sheet workbooks). Per-file vendor hint.

READ→STAGE only. Resolving staging → shopify_products.cost is a separate gated step
(and must carry unit, per the YARD/ROLL lesson). Idempotent per source_file (deletes
prior rows for that file before reload).
"""
import openpyxl, glob, os, re, json, subprocess, sys

SKU_KEYS   = ['sku','item','articolo','pattern number','js number','pattern_number','mfr','style']
WHLS_KEYS  = ['new wholesale','new cost','wholesale','whls','trade price','listino wholesale','cost of goods','current cost','mid year price','current price','cost','net','price']
RETAIL_KEYS= ['new map','new retail','map','listino retail','current retail','retail','msrp']
UNIT_KEYS  = ['unit of measure','unit','uom']
VENDOR_COL_KEYS = ['vendor','brand']

# per-file vendor hint (None => read a Vendor/Brand column per row) + sheets to skip
VENDOR_HINT = {
  'Kravet Price list 1-20-26.xlsx': None,          # already in kravet tables -> skip below
  'Designer Wallcoverings Exclusives Price Increase 2023.xlsx': 'Designer Wallcoverings',
  'MH 2023 PRICE LIST USD LANDED USD RETAIL.xlsx': 'MH-2023-UNIDENTIFIED',
  'MIssoni Price .xlsx': 'Missoni',
  'Mini Modern - Wallpaper price list.xlsx': None, # has per-row Vendor/Brand
  'Ralph Lauren Price Sheet.xlsx': 'Ralph Lauren',
  'Schumacher_2023 Master Price Increase List- MD 7.18.xlsx': 'Schumacher',
}
SKIP_FILES = {'Kravet Price list 1-20-26.xlsx'}  # redundant w/ kravet_authoritative_pricing

def norm(s): return re.sub(r'[^a-z0-9]','',str(s or '').lower())
def num(v):
    if v is None: return None
    try:
        x=float(str(v).replace('$','').replace(',','').strip())
        return x if x>0 else None
    except: return None
def matchcol(headers, keys):
    hl=[str(h).lower().strip() if h is not None else '' for h in headers]
    # pass 1: exact header == key  (so a column literally 'SKU' beats 'Item Description')
    for k in keys:
        for i,h in enumerate(hl):
            if h==k: return i
    # pass 2: header starts with key
    for k in sorted(keys, key=len, reverse=True):
        for i,h in enumerate(hl):
            if h.startswith(k): return i
    # pass 3: contains (longest key first to prefer 'new wholesale' over 'wholesale')
    for k in sorted(keys, key=len, reverse=True):
        for i,h in enumerate(hl):
            if k in h: return i
    return None

def find_header(rows):
    # the row (within first 8) that contains a SKU-like keyword AND a price-like keyword
    for ri in range(min(8,len(rows))):
        cells=[str(c).lower() if c is not None else '' for c in rows[ri]]
        has_sku=any(any(k in c for k in SKU_KEYS) for c in cells)
        has_price=any(any(k in c for k in WHLS_KEYS+RETAIL_KEYS) for c in cells)
        if has_sku and has_price: return ri
    return None

def best_sheet(wb):
    best=None
    for name in wb.sheetnames:
        ws=wb[name]
        rows=[]
        for i,r in enumerate(ws.iter_rows(values_only=True)):
            rows.append(r)
            if i>=12: break
        hr=find_header(rows)
        if hr is not None:
            # count data rows
            n=sum(1 for _ in ws.iter_rows(values_only=True))
            if not best or n>best[3]: best=(name,hr,rows,n)
    return best

def load_file(f):
    base=os.path.basename(f)
    if base in SKIP_FILES: return (base,'SKIP (redundant)',0)
    wb=openpyxl.load_workbook(f, read_only=True, data_only=True)
    bs=best_sheet(wb)
    if not bs: return (base,'NO HEADER FOUND',0)
    name,hr,_,_=bs
    ws=wb[name]
    it=ws.iter_rows(values_only=True)
    for _ in range(hr): next(it)
    headers=list(next(it))
    ci_sku=matchcol(headers,SKU_KEYS); ci_w=matchcol(headers,WHLS_KEYS)
    ci_r=matchcol(headers,RETAIL_KEYS); ci_u=matchcol(headers,UNIT_KEYS)
    ci_v=matchcol(headers,VENDOR_COL_KEYS)
    if ci_sku is None or (ci_w is None and ci_r is None):
        return (base,f'sheet={name} unmappable (sku={ci_sku} w={ci_w} r={ci_r})',0)
    vhint=VENDOR_HINT.get(base)
    rows_out=[]
    for r in it:
        if not r or ci_sku>=len(r): continue
        sku=r[ci_sku]
        if sku is None or str(sku).strip() in ('','None'): continue
        w=num(r[ci_w]) if ci_w is not None and ci_w<len(r) else None
        rt=num(r[ci_r]) if ci_r is not None and ci_r<len(r) else None
        if w is None and rt is None: continue
        unit=(str(r[ci_u]).upper() if ci_u is not None and ci_u<len(r) and r[ci_u] else None)
        vendor=vhint or (str(r[ci_v]) if ci_v is not None and ci_v<len(r) and r[ci_v] else 'UNKNOWN')
        rows_out.append((vendor,str(sku).strip(),norm(sku),w,rt,unit,base))
    return (base,f'sheet={name} hdr@{hr+1} sku@{ci_sku} w@{ci_w} r@{ci_r} u@{ci_u}',rows_out)

def psql(sql, stdin=None):
    return subprocess.run(['psql','-d','dw_unified','-v','ON_ERROR_STOP=1','-c',sql] if stdin is None
                          else ['psql','-d','dw_unified','-v','ON_ERROR_STOP=1','-c',sql],
                          capture_output=True, text=True)

total=0; report=[]
for f in sorted(glob.glob('/tmp/price-sheets/*.xlsx')):
    base,info,rows=load_file(f)
    if isinstance(rows,int) or not rows:
        report.append(f'  [skip] {base}: {info}'); continue
    # idempotent reload for this file
    subprocess.run(['psql','-d','dw_unified','-c',
                    f"delete from vendor_price_sheets where source_file = $${base}$$;"],
                   capture_output=True, text=True)
    # bulk insert via COPY
    import csv, io
    buf=io.StringIO(); w=csv.writer(buf)
    for row in rows: w.writerow(list(row[:-1])+[base])
    buf.seek(0)
    p=subprocess.run(['psql','-d','dw_unified','-c',
        "copy vendor_price_sheets(vendor,mfr_sku,mfr_sku_norm,wholesale,map_or_retail,unit_of_measure,source_file) from stdin with csv"],
        input=buf.getvalue(), capture_output=True, text=True)
    ok = 'COPY' in (p.stdout+p.stderr)
    n=len(rows)
    total += n if ok else 0
    report.append(f'  [{"OK " if ok else "ERR"}] {base}: {n} rows | {info}' + ('' if ok else ' | '+p.stderr.strip()[:120]))

print('\n'.join(report))
print(f'\nTOTAL loaded into vendor_price_sheets: {total}')