`erica/size-tranche-c.py`

85 lines
#!/usr/bin/env python3
"""TK-10895 Tranche C sizing — READ-ONLY.
Erica Nyarko (Quadrille) 2026-09-10 unblocked three held cohorts:
  (A) the 9 'page-absent' patterns  -> NOT discontinued, standard 5yd roll
  (B) 48-60in 'Commercial Wallcovering' -> "disregard that title", standard 5yd roll handprints
  (C) 32-36.5in wallpaper           -> same
Grasscloth stays sold-by-the-yard and is EXCLUDED.
Mill rows are not our SKUs, so everything is joined to china_seas_catalog on mfr_sku.
"""
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

NINE=['Criss Cross','Zig Zag','Interweave','Camelot','Clementine All Over',
      'Beau Rivage','Isfahan','Cap Ferrat','Deauville']

def cohort(r):
    if g(r,'Fabric/Wallpaper')!='Wallpaper': return None
    if 'grass' in (g(r,'Material')+g(r,'Pattern')+g(r,'Type')).lower(): return None
    w=win(r)
    if any(g(r,'Pattern').lower().startswith(p.lower()) for p in NINE): return 'A_page_absent'
    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

# --- the PLANE rule Erica gave us: slash cell -> first=fabric, second=wallpaper ---
def resolve_cost(r):
    c=g(r,'Cost')
    if not c: return None,'no_cost'
    nums=re.findall(r'\d+(?:\.\d+)?', c.replace(',',''))
    if '-' in c and len(nums)==2: return None,'range_cell_needs_vendor'
    if '/' in c and len(nums)==2:
        # F/W column says which plane this ROW is; these are all Wallpaper -> second token
        return float(nums[1]),'slash_wallpaper_token'
    if len(nums)==1: return float(nums[0]),'single'
    return None,'unparseable'

cand={}
for r in rows:
    c=cohort(r)
    if not c: continue
    mfr=g(r,'Mfr #')
    if not mfr: continue
    cost,basis=resolve_cost(r)
    cand[mfr]={'cohort':c,'pattern':g(r,'Pattern'),'color':g(r,'Color'),
               'width':g(r,'Width in'),'cost_cell':g(r,'Cost'),'cost':cost,'basis':basis}

print(f"mill candidate rows (wallpaper, non-grasscloth): {len(cand)}")
print("  by cohort:", dict(collections.Counter(v['cohort'] for v in cand.values())))
print("  by cost basis:", dict(collections.Counter(v['basis'] for v in cand.values())))

# --- join to OUR catalog ---
mfrs=sorted(cand)
q=("select mfr_sku||'\t'||coalesce(dw_sku,'')||'\t'||coalesce(replace(shopify_product_id,'gid://shopify/Product/',''),'')"
   "||'\t'||coalesce(product_type,'')||'\t'||coalesce(cost_price::text,'')||'\t'||coalesce(our_price::text,'') "
   "from china_seas_catalog where mfr_sku in (" + ",".join("'"+m.replace("'","''")+"'" for m in mfrs) + ")")
out=subprocess.run(['psql','-h','/tmp','-d','dw_unified','-Atc',q],capture_output=True,text=True)
ours={}
for line in out.stdout.strip().split('\n'):
    if not line: continue
    p=line.split('\t')
    ours[p[0]]={'dw_sku':p[1],'pid':p[2],'ptype':p[3],'db_cost':p[4],'db_our':p[5]}

have=[m for m in mfrs if m in ours]
onshop=[m for m in have if ours[m]['pid']]
print(f"\nof those, WE carry a catalog row for : {len(have)}")
print(f"  ... with a shopify_product_id        : {len(onshop)}")
print(f"  ... mill rows we do NOT carry        : {len(mfrs)-len(have)}")

ready=[m for m in onshop if cand[m]['cost'] is not None]
blocked=[m for m in onshop if cand[m]['cost'] is None]
print(f"\nPRICEABLE NOW (on shopify + resolvable cost): {len(ready)}")
print(f"STILL VENDOR-BLOCKED (range/no cost)       : {len(blocked)}")
print("  blocked reasons:", dict(collections.Counter(cand[m]['basis'] for m in blocked)))
for c in ['A_page_absent','B_commercial_48_60','C_mid_32_36']:
    n=[m for m in ready if cand[m]['cohort']==c]
    print(f"    {c:<20} priceable={len(n)}")
json.dump({m:{**cand[m],**ours[m]} for m in ready}, open('tranche-c-candidates.json','w'), indent=1)
print(f"\nwrote tranche-c-candidates.json ({len(ready)})")