#!/usr/bin/env python3
"""Separate genuine loss/gain from parts that merely changed shelf."""
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'))
products=load_tsv('db_products.tsv'); locations=load_tsv('db_locations_full.tsv')
stocks=load_tsv('db_stocks.tsv');     scan=list(csv.DictReader(open('scan_lines.csv',newline='')))
nk=lambda s: re.sub(r'\s+','',str(s)).upper()
prod_by_pk={p['id']:p for p in products}; loc_by_pk={l['id']:l for l in locations}
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]
f=lambda x:(float(x) if x not in ('','NULL',None) else 0.0)
scan_keys={nk(r['shelf_raw']) for r in scan}
scope={loc_by_key[k]['id'] for k in scan_keys if k in loc_by_key}

scan_sku=collections.Counter(nk(r['sku']) for r in scan)
scan_shelves=collections.defaultdict(collections.Counter)
for r in scan: scan_shelves[nk(r['sku'])][nk(r['shelf_raw'])]+=1
db_in=collections.Counter(); db_out=collections.Counter()
db_in_sh=collections.defaultdict(collections.Counter); db_out_sh=collections.defaultdict(collections.Counter)
for s in stocks:
    q=int(s['quantity'])
    if q<=0: continue
    p=prod_by_pk.get(s['product_id']); l=loc_by_pk.get(s['location_id'])
    if not p or not l: continue
    sku=nk(p['product_id']); sh=nk(l['location'])
    if s['location_id'] in scope: db_in[sku]+=q; db_in_sh[sku][sh]+=q
    else:                          db_out[sku]+=q; db_out_sh[sku][sh]+=q
pr=lambda sku: f(prod_by_sku[sku]['average_price']) if sku in prod_by_sku else 0.0

cat=collections.defaultdict(lambda:{'skus':0,'units':0,'value':0.0})
detail=[]
for sku in sorted(set(scan_sku)|set(db_in)):
    sc,db=scan_sku.get(sku,0),db_in.get(sku,0)
    out=db_out.get(sku,0); known=sku in prod_by_sku
    d=sc-db; moved = sc==db and set(scan_shelves[sku])!=set(db_in_sh[sku])
    if not known:                       c='NEW_SKU_not_in_catalog'
    elif sc==db and not moved:          c='UNCHANGED'
    elif moved:                         c='MOVED_same_total'
    elif db==0:                         c='APPEARED_in_scope'
    elif sc==0:                         c='VANISHED_from_scope'
    elif d>0:                           c='NET_GAIN'
    else:                               c='NET_LOSS'
    cat[c]['skus']+=1; cat[c]['units']+=abs(d) if c!='MOVED_same_total' else sc
    cat[c]['value']+=abs(d)*pr(sku) if c!='MOVED_same_total' else 0.0
    detail.append({'sku':sku,'category':c,'db_in_scope':db,'counted':sc,'delta':d,
        'db_outside_scope':out,'unit_price':round(pr(sku),2),'value_delta':round(d*pr(sku),2),
        'db_shelves':'|'.join(sorted(db_in_sh[sku])),'scan_shelves':'|'.join(sorted(scan_shelves[sku])),
        'outside_shelves':'|'.join(sorted(db_out_sh[sku]))})
with open('variance_by_sku.csv','w',newline='') as fh:
    w=csv.DictWriter(fh,fieldnames=list(detail[0].keys())); w.writeheader(); w.writerows(detail)

print(f"{'='*94}\nSKU-LEVEL RECONCILIATION (totals across the scanned shelves)\n{'='*94}")
print(f"{'':28}{'SKUs':>7}{'units':>9}{'value':>15}")
for c,lbl in [('UNCHANGED','Unchanged'),('MOVED_same_total','Moved shelf, same qty'),
              ('NET_GAIN','Counted more (net gain)'),('NET_LOSS','Counted less (net loss)'),
              ('APPEARED_in_scope','Appeared (was not here)'),('VANISHED_from_scope','Vanished (gone entirely)'),
              ('NEW_SKU_not_in_catalog','New SKU, no catalog rec')]:
    v=cat[c]; print(f"{lbl:28}{v['skus']:>7}{v['units']:>9}{v['value']:>15,.2f}")

print(f"\n{'='*94}\nDOUBLE-COUNT RISK: counted on a scanned shelf, but system also holds stock OFF-scan\n{'='*94}")
risk=[d for d in detail if d['counted']>0 and d['db_outside_scope']>0]
print(f"{len(risk)} SKUs, {sum(d['db_outside_scope'] for d in risk)} units "
      f"(${sum(d['db_outside_scope']*d['unit_price'] for d in risk):,.2f}) sit on shelves the file does NOT name.")
print("These survive an empty-and-replace and would be ADDED to the counted qty:")
for d in sorted(risk,key=lambda x:-x['db_outside_scope']*x['unit_price'])[:12]:
    print(f"   {d['sku']:<16} counted {d['counted']} on {d['scan_shelves']:<12} | "
          f"+{d['db_outside_scope']} still on {d['outside_shelves']}  (${d['db_outside_scope']*d['unit_price']:,.2f})")