#!/usr/bin/env python3
"""Build the exact load plan. Keyed on (part, shelf) - never collapsed."""
import csv, collections, re, json
def load_tsv(p):
    with open(p,newline='',encoding='utf-8',errors='replace') as f: return list(csv.DictReader(f,delimiter='\t'))
nk=lambda s: re.sub(r'\s+','',str(s)).upper()
disp=lambda s: re.sub(r'\s+',' ',str(s).strip()).upper()
products=load_tsv('db_products.tsv'); locations=load_tsv('db_locations_full.tsv')
sept=list(csv.DictReader(open('scan_lines.csv',newline='')))
conflict=list(csv.DictReader(open('CONFLICT_shipped_but_in_sheet.csv')))

prod_by_sku={}; [prod_by_sku.setdefault(nk(p['product_id']),p) for p in products]
loc_by_key={}; [loc_by_key.setdefault(nk(l['location']),l) for l in locations]

# ---- target from the sheet, aggregated per (part, shelf)
target=collections.Counter((nk(r['sku']),nk(r['shelf_raw'])) for r in sept)
# keep the original spelling for new product rows
spell={}; meta={}
for r in sept:
    spell.setdefault(nk(r['sku']), r['sku'])
    meta.setdefault(nk(r['sku']), {'partslink':r['partslink'],'supplier':r['supplier']})

# ---- zero out the conflict positions
zero={(nk(c['part_number']), nk(c['shelf'])) for c in conflict}
print(f"sheet positions          : {len(target)}   units {sum(target.values())}")
print(f"conflict positions to 0  : {len(zero)}   units {sum(target[k] for k in zero if k in target)}")
missing=[k for k in zero if k not in target]
print(f"  (conflict rows not in the sheet: {len(missing)})")

plan={k:v for k,v in target.items() if k not in zero}
print(f"LOAD PLAN                : {len(plan)} positions   {sum(plan.values())} units")

# ---- shelves
shelves=sorted({k[1] for k in target})
loc_ids={k:loc_by_key[k]['id'] for k in shelves}
inactive=[k for k in shelves if loc_by_key[k]['is_active']=='0']
print(f"shelves                  : {len(shelves)}   inactive to reactivate: {len(inactive)} "
      f"{[loc_by_key[k]['location'] for k in inactive]}")

# ---- products to create
newp=sorted({k[0] for k in target if k[0] not in prod_by_sku})
print(f"products to create       : {len(newp)}")
with open('plan_new_products.csv','w',newline='') as fh:
    w=csv.writer(fh); w.writerow(['product_id','supplier_part_number','partslink','supplier'])
    for s in newp: w.writerow([spell[s], spell[s], meta[s]['partslink'], meta[s]['supplier']])

# ---- write the load plan
with open('plan_load.csv','w',newline='') as fh:
    w=csv.writer(fh); w.writerow(['part_key','shelf_key','part_display','shelf_display','qty'])
    for (sk,sh),q in sorted(plan.items()):
        w.writerow([sk, sh, spell.get(sk,sk), loc_by_key[sh]['location'], q])
with open('plan_shelves.csv','w',newline='') as fh:
    w=csv.writer(fh); w.writerow(['shelf_key','location_id','location','is_active','type'])
    for k in shelves:
        l=loc_by_key[k]; w.writerow([k,l['id'],l['location'],l['is_active'],l['type']])
json.dump({'positions':len(plan),'units':sum(plan.values()),'shelves':len(shelves),
           'new_products':len(newp),'zeroed':len(zero)}, open('plan_summary.json','w'), indent=1)

multi=collections.Counter(k[0] for k in plan)
print(f"\nsanity: parts on >1 shelf in the plan = {sum(1 for v in multi.values() if v>1)} "
      f"(these MUST stay separate rows)")
print(f"        max shelves for one part        = {max(multi.values())}")