`erica/sibling-sweep.py`

57 lines
#!/usr/bin/env python3
"""Does the 'our internal item-number row costs MORE than the mill's own code row' pattern
reach the products ALREADY LIVE?  READ-ONLY sweep of the whole sheet."""
import csv,re,collections,subprocess,json
EV='/Users/macstudio3/.claude/yolo-queue/evidence/TK-10895/mill-sheet-20260910.csv'
rows=list(csv.DictReader(open(EV,newline='',encoding='utf-8-sig')))
g=lambda r,k:(r.get(k) or '').strip()
SUFFIX=re.compile(r'\s+(FABRIC|WP|W/?P)\s*$',re.I)
base=lambda p: SUFFIX.sub('',p).strip().lower()
def plane(p):
    m=SUFFIX.search(p or '');  return None if not m else ('Fabric' if m.group(1).upper()=='FABRIC' else 'Wallpaper')
def money(c):
    n=re.findall(r'\d+(?:\.\d+)?',(c or '').replace(',',''));  return [float(x) for x in n]
# "our" rows = 6-digit internal item numbers; "mill code" rows = anything else
INTERNAL=re.compile(r'^\d{6}$')
idx=collections.defaultdict(list)
for r in rows: idx[(base(g(r,'Pattern')),g(r,'Width in'))].append(r)

flags=[]
for r in rows:
    mfr=g(r,'Mfr #')
    if not INTERNAL.match(mfr): continue
    if plane(g(r,'Pattern'))=='Fabric': continue
    if g(r,'Fabric/Wallpaper')!='Wallpaper': continue
    m=money(g(r,'Cost'))
    if not m or '-' in g(r,'Cost'): continue
    ours=m[1] if ('/' in g(r,'Cost') and len(m)==2) else m[0]
    for s in idx[(base(g(r,'Pattern')),g(r,'Width in'))]:
        sm=g(s,'Mfr #')
        if INTERNAL.match(sm) or plane(g(s,'Pattern'))=='Fabric': continue
        ms=money(g(s,'Cost'))
        if not ms or '-' in g(s,'Cost'): continue
        mc=ms[1] if ('/' in g(s,'Cost') and len(ms)==2) else ms[0]
        if mc < ours-0.5:
            flags.append({'mfr':mfr,'pattern':g(r,'Pattern'),'width':g(r,'Width in'),
                          'our_cost':ours,'mill_code':sm,'mill_cost':mc,'delta':round(ours-mc,2)})
            break
print(f"rows where OUR item-number cost EXCEEDS the mill's own code row (same pattern+width): {len(flags)}")
byp=collections.Counter(f['pattern'] for f in flags)
print(f"distinct patterns affected: {len(byp)}")
for p,n in byp.most_common(15):
    ex=next(f for f in flags if f['pattern']==p)
    print(f"   {p[:30]:<31} n={n:<4} ours ${ex['our_cost']:>7.2f} vs {ex['mill_code']:<13} ${ex['mill_cost']:>7.2f}  (-${ex['delta']:.2f})")
# which of these are LIVE with a sellable variant?
mfrs=[f['mfr'] for f in flags]
q=("select mfr_sku||'\t'||coalesce(replace(shopify_product_id,'gid://shopify/Product/',''),'')||'\t'||coalesce(our_price::text,'')"
   " from china_seas_catalog where mfr_sku in ("+",".join("'"+m+"'" for m in mfrs)+")")
out=subprocess.run(['psql','-h','/tmp','-d','dw_unified','-Atc',q],capture_output=True,text=True)
have={l.split('\t')[0]:l.split('\t')[1:] for l in out.stdout.strip().split('\n') if l and '\t' in l}
withpid=[f for f in flags if have.get(f['mfr']) and have[f['mfr']][0]]
print(f"\nof those, we carry a catalog row: {len([f for f in flags if f['mfr'] in have])}")
print(f"   ... with a shopify product id : {len(withpid)}")
tot=sum(f['delta'] for f in withpid)
print(f"   potential overcharge basis if ALL were joined to the wrong row: ${tot:,.2f} of COST across {len(withpid)} skus")
json.dump(flags,open('sibling-flags.json','w'),indent=1)
print("wrote sibling-flags.json")