#!/usr/bin/env python3
"""
Rescan vs system variance. Built from raw tables only.
Rules: PRODUCT ID -> products.product_id, exact (case/whitespace-normalised) only.
       The file is authoritative for every shelf it names; any delta either way is reported.
"""
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='')))

# normalisers: uppercase + collapse whitespace runs (handles '1 U'/'1  U'/'1U' spelling drift)
nkey = lambda s: re.sub(r'\s+','', str(s)).upper()      # shelf identity
nsku = lambda s: re.sub(r'\s+','', str(s)).upper()      # sku identity

prod_by_pk={p['id']:p for p in products}
prod_by_sku={}
for p in products: prod_by_sku.setdefault(nsku(p['product_id']),p)
loc_by_pk={l['id']:l for l in locations}
loc_by_key={}
for l in locations: loc_by_key.setdefault(nkey(l['location']),l)
f=lambda x:(float(x) if x not in ('','NULL',None) else 0.0)

# ---------- scope ----------
scan_keys=sorted({nkey(r['shelf_raw']) for r in scan})
scope_pk={loc_by_key[k]['id'] for k in scan_keys if k in loc_by_key}
print(f"scope: {len(scan_keys)} shelves named by the file -> {len(scope_pk)} location records")

# ---------- states ----------
scan_pos=collections.Counter((nsku(r['sku']),nkey(r['shelf_raw'])) for r in scan)
db_pos=collections.Counter(); db_price={}; db_out=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
    k=(nsku(p['product_id']), nkey(l['location']))
    if s['location_id'] in scope_pk:
        db_pos[k]+=q; db_price[k]=f(s['average_price']) or f(p['average_price'])
    else:
        db_out[k]+=q

price=lambda sku,shelf=None: db_price.get((sku,shelf)) or f(prod_by_sku[sku]['average_price']) if sku in prod_by_sku else 0.0
disp={}   # pretty names
for r in scan: disp.setdefault(nkey(r['shelf_raw']), re.sub(r'\s+',' ',r['shelf_raw'].strip()).upper())
skudisp={}
for r in scan: skudisp.setdefault(nsku(r['sku']), r['sku'])
for p in products: skudisp.setdefault(nsku(p['product_id']), p['product_id'])

# ---------- position variance ----------
rows=[]
for k in sorted(set(scan_pos)|set(db_pos)):
    sku,shelf=k
    sc,db=scan_pos.get(k,0),db_pos.get(k,0)
    known=sku in prod_by_sku
    if sc==db:        kind='MATCH'
    elif sc==0:       kind='REMOVE_missing'
    elif db==0:       kind='ADD_new_sku' if not known else 'ADD_to_shelf'
    elif sc>db:       kind='INCREASE'
    else:             kind='DECREASE'
    up=price(sku,shelf)
    rows.append({'sku':skudisp.get(sku,sku),'shelf':disp.get(shelf,shelf),'db_qty':db,'scan_qty':sc,
                 'delta':sc-db,'kind':kind,'in_catalog':int(known),'unit_price':round(up,2),
                 'value_delta':round((sc-db)*up,2),
                 'elsewhere_in_db':sum(v for (s2,sh2),v in db_out.items() if s2==sku)})
with open('variance_positions.csv','w',newline='') as fh:
    w=csv.DictWriter(fh,fieldnames=list(rows[0].keys())); w.writeheader(); w.writerows(rows)

print(f"\n{'='*96}\nPOSITION-LEVEL VARIANCE  (SKU x shelf, within the {len(scan_keys)} scanned shelves)\n{'='*96}")
print(f"{'':22}{'positions':>10}{'system':>9}{'counted':>9}{'delta':>8}{'value impact':>16}")
tot=lambda rs,f_: sum(r[f_] for r in rs)
for k,lbl in [('MATCH','Match — no change'),('INCREASE','Counted MORE'),('DECREASE','Counted LESS'),
              ('ADD_to_shelf','New to shelf (known SKU)'),('ADD_new_sku','New SKU (not in catalog)'),
              ('REMOVE_missing','Not found — missing')]:
    rs=[r for r in rows if r['kind']==k]
    print(f"{lbl:22}{len(rs):>10}{tot(rs,'db_qty'):>9}{tot(rs,'scan_qty'):>9}{tot(rs,'delta'):>+8}{tot(rs,'value_delta'):>+16,.2f}")
print(f"{'-'*96}\n{'TOTAL':22}{len(rows):>10}{tot(rows,'db_qty'):>9}{tot(rows,'scan_qty'):>9}"
      f"{tot(rows,'delta'):>+8}{tot(rows,'value_delta'):>+16,.2f}")
json.dump({'scan_keys':scan_keys},open('scope.json','w'))