`erica/size-tranche-c2.py`

100 lines
#!/usr/bin/env python3
"""TK-10895 Tranche C sizing v2 — READ-ONLY. Adds the two guards v1 lacked.

GUARD 1 (plane): the sheet's Fabric/Wallpaper COLUMN is unreliable. mfr 708621 is typed
  'Wallpaper' while its Pattern reads 'Beau Rivage FABRIC' and carries the FABRIC cost $194,
  beside AP980-05 'Beau Rivage WP' also typed 'Wallpaper' carrying the real WP cost $136.
  The PATTERN-NAME SUFFIX overrides the column.
GUARD 2 (fabric-row join): for each of our rows, look for a sibling row of the same pattern
  base + same width carrying a DIFFERENT cost. A cheaper WP-suffixed sibling means our row is
  very likely joined to the mill's FABRIC row — the Group B defect already fixed on this ticket.
Anything either guard trips is HELD, never priced.
"""
import csv, subprocess, json, re, collections

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()
def win(r):
    try: return float(g(r,'Width in'))
    except: return None
def money(c):
    n=re.findall(r'\d+(?:\.\d+)?', (c or '').replace(',',''))
    return [float(x) for x in n]

NINE=['Criss Cross','Zig Zag','Interweave','Camelot','Clementine All Over',
      'Beau Rivage','Isfahan','Cap Ferrat','Deauville']
SUFFIX=re.compile(r'\s+(FABRIC|WP|W/?P)\s*$', re.I)
def base(p): return SUFFIX.sub('', p).strip().lower()
def name_plane(p):
    m=SUFFIX.search(p or '')
    if not m: return None
    return 'Fabric' if m.group(1).upper()=='FABRIC' else 'Wallpaper'

# index every row by (pattern base, width) so we can falsify our own cost
idx=collections.defaultdict(list)
for r in rows: idx[(base(g(r,'Pattern')), g(r,'Width in'))].append(r)

def cohort(r):
    if 'grass' in (g(r,'Material')+g(r,'Pattern')+g(r,'Type')).lower(): return None
    w=win(r)
    if any(base(g(r,'Pattern')).startswith(p.lower()) for p in NINE): return 'A_page_absent'
    if g(r,'Fabric/Wallpaper')!='Wallpaper': return None
    if w is not None and 48<=w<=60: return 'B_commercial_48_60'
    if w is not None and 32<=w<=36.5: return 'C_mid_32_36'
    return None

cand={}
for r in rows:
    c=cohort(r); mfr=g(r,'Mfr #')
    if not c or not mfr: continue
    pat=g(r,'Pattern'); nm=name_plane(pat); col=g(r,'Fabric/Wallpaper')
    holds=[]
    # GUARD 1 — name says FABRIC: this row is a fabric row whatever the column says
    if nm=='Fabric': holds.append('name_suffix_FABRIC_overrides_column')
    if nm is None and col!='Wallpaper': holds.append('column_not_wallpaper')
    nums=money(g(r,'Cost'))
    cost=None; basis=None
    if not nums: holds.append('no_cost')
    elif '-' in g(r,'Cost') and len(nums)==2: holds.append('range_cell_needs_vendor')
    elif '/' in g(r,'Cost') and len(nums)==2: cost,basis=nums[1],'slash_wallpaper_token'
    elif len(nums)==1: cost,basis=nums[0],'single'
    else: holds.append('unparseable')
    # GUARD 2 — a cheaper WP-suffixed sibling at the same pattern+width
    if cost is not None:
        sibs=[s for s in idx[(base(pat), g(r,'Width in'))] if g(s,'Mfr #')!=mfr]
        # WIDENED: any cheaper sibling of the same pattern+width is a flag. The suffix-only
        # version missed Capri, whose mill-code rows CP1060W-0x carry $164 against our $194
        # with no ' WP' suffix anywhere to key on.
        cheaper=[(g(s,'Mfr #'), money(g(s,'Cost'))[0], g(s,'Pattern'))
                 for s in sibs if money(g(s,'Cost')) and money(g(s,'Cost'))[0] < cost-0.5
                 and name_plane(g(s,'Pattern')) != 'Fabric']
        if cheaper:
            holds.append(f'cheaper_WP_sibling:{cheaper[0][0]}@${cheaper[0][1]:.0f}')
    cand[mfr]={'cohort':c,'pattern':pat,'base':base(pat),'name_plane':nm,'col':col,
               'width':g(r,'Width in'),'cost_cell':g(r,'Cost'),'cost':cost,'basis':basis,
               'holds':holds}

ok=[m for m,v in cand.items() if not v['holds']]
held=[m for m,v in cand.items() if v['holds']]
print(f"mill candidate rows: {len(cand)}   clean={len(ok)}   HELD={len(held)}")
hc=collections.Counter(h.split(':')[0] for v in cand.values() for h in v['holds'])
print("  hold reasons:", dict(hc))

q=("select mfr_sku||'\t'||coalesce(replace(shopify_product_id,'gid://shopify/Product/',''),'')"
   " from china_seas_catalog where mfr_sku in (" + ",".join("'"+m.replace("'","''")+"'" for m in ok) + ")")
out=subprocess.run(['psql','-h','/tmp','-d','dw_unified','-Atc',q],capture_output=True,text=True)
pid={l.split('\t')[0]:l.split('\t')[1] for l in out.stdout.strip().split('\n') if l and '\t' in l}
ready={m:{**cand[m],'pid':pid[m]} for m in ok if pid.get(m)}
print(f"\nclean rows we carry WITH a shopify id: {len(ready)}")
print("  by cohort:", dict(collections.Counter(v['cohort'] for v in ready.values())))
print("\n  sample of what SURVIVED both guards:")
for m,v in list(ready.items())[:12]:
    print(f"    {m:<12} {v['cohort']:<19} ${v['cost']:>7.2f} w={v['width']:<7} {v['pattern'][:34]}")
print("\n  sample of what the guards HELD:")
for m in held[:12]:
    v=cand[m]; print(f"    {m:<12} {v['pattern'][:28]:<29} cell={v['cost_cell']:<14} {';'.join(v['holds'])}")
json.dump(ready, open('tranche-c-ready.json','w'), indent=1)
json.dump({m:cand[m] for m in held}, open('tranche-c-held.json','w'), indent=1)
print(f"\nwrote tranche-c-ready.json ({len(ready)}) + tranche-c-held.json ({len(held)})")