#!/usr/bin/env python3
"""Release conflict positions whose ONLY disqualifier was a manual adjustment before 2026-08-29."""
import csv, collections, re
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()
CUT='2026-08-29'
ship=load_tsv('shipped_notes.tsv'); sept=list(csv.DictReader(open('scan_lines.csv',newline='')))
conf=list(csv.DictReader(open('CONFLICT_shipped_but_in_sheet.csv')))
products=load_tsv('db_products_after.tsv'); locations=load_tsv('db_locations_full.tsv')
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]
S=collections.Counter((nk(r['sku']),nk(r['shelf_raw'])) for r in sept)
keys={(nk(c['part_number']),nk(c['shelf'])) for c in conf}
ev=collections.defaultdict(list)
for e in ship:
    k=(nk(e['sku']),nk(e['shelf']))
    if k in keys: ev[k].append(e)

keep=set(); why={}
for k,es in ev.items():
    for e in es:
        if e['reference_type']=='order': keep.add(k); why[k]='order deduction'; break
        if e['created_at'][:10] >= CUT: keep.add(k); why[k]=f"manual adj on/after {CUT}"; break
rel=sorted(keys-keep)
print(f"cutoff {CUT}")
print(f"  stay zeroed : {len(keep)} positions")
print(f"  RELEASE     : {len(rel)} positions / {sum(S[k] for k in rel)} units")
assert len(rel)==32 and sum(S[k] for k in rel)==33, "unexpected release set"

q=lambda s: "'"+str(s).replace("\\","\\\\").replace("'","''")+"'"
REF='SEPT-RESCAN-2026-08-31-REL'; CD='2026-08-31'; WH=54
L=["START TRANSACTION;",
   "DROP TEMPORARY TABLE IF EXISTS _rel;",
   "CREATE TEMPORARY TABLE _rel(product_pk BIGINT UNSIGNED, location_pk BIGINT UNSIGNED, qty INT, price DECIMAL(10,2)) ENGINE=InnoDB;"]
vals=[]
plan=[]
for k in rel:
    p=prod_by_sku[k[0]]; l=loc_by_key[k[1]]
    vals.append(f"({p['id']},{l['id']},{S[k]},{float(p['average_price'] or 0):.2f})")
    plan.append({'part':p['product_id'],'shelf':l['location'],'qty':S[k],
                 'price':float(p['average_price'] or 0),'reason':'manual adjustment before cutoff'})
L.append("INSERT INTO _rel (product_pk,location_pk,qty,price) VALUES "+",".join(vals)+";")
L.append(f"""UPDATE stocks s JOIN _rel r ON s.product_id=r.product_pk AND s.location_id=r.location_pk
   SET s.quantity=r.qty, s.average_price=r.price, s.warehouse_id={WH},
       s.last_counted_at={q(CD)}, s.updated_at=NOW();""")
L.append("SELECT ROW_COUNT() INTO @updated;")
L.append(f"""INSERT INTO stocks (product_id,warehouse_id,location_id,sub_location,quantity,reserved_quantity,
  average_price,is_selling,`condition`,last_counted_at,created_at,updated_at)
SELECT r.product_pk,{WH},r.location_pk,NULL,r.qty,0,r.price,1,'new',{q(CD)},NOW(),NOW()
FROM _rel r LEFT JOIN stocks s ON s.product_id=r.product_pk AND s.location_id=r.location_pk
WHERE s.id IS NULL;""")
L.append(f"""INSERT INTO stock_history
 (product_id,warehouse_id,location_id,action_type,quantity_before,quantity_change,quantity_after,
  unit_price,reference_type,reference_id,user_id,notes,created_at)
SELECT r.product_pk,{WH},r.location_pk,'stock_take',0,r.qty,r.qty,r.price,'stock_take',{q(REF)},NULL,
 'September rescan - position released: only disqualifier was a manual adjustment before {CUT} (per Mohit)',NOW()
FROM _rel r;""")
L.append(f"SELECT @updated AS rows_updated, (SELECT SUM(qty) FROM _rel) AS units_released, {len(rel)} AS expect_pos, 33 AS expect_units;")
open('release.sql','w').write("\n".join(L)+"\n")
with open('released_positions.csv','w',newline='') as fh:
    w=csv.DictWriter(fh,fieldnames=list(plan[0].keys())); w.writeheader(); w.writerows(plan)
print(f"\nvalue restored: ${sum(p['qty']*p['price'] for p in plan):,.2f}")
print("wrote release.sql + released_positions.csv")