#!/usr/bin/env python3
"""Generate the load SQL. Staging-table based, single transaction, assert-guarded."""
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()
products=load_tsv('db_products.tsv'); locations=load_tsv('db_locations_full.tsv')
plan=list(csv.DictReader(open('plan_load.csv',newline='')))
newp=list(csv.DictReader(open('plan_new_products.csv',newline='')))
shelves=list(csv.DictReader(open('plan_shelves.csv',newline='')))
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]

# GUARD: no new product_id may collide case-insensitively with an existing one
exist_ci={p['product_id'].strip().upper() for p in products}
coll=[r for r in newp if r['product_id'].strip().upper() in exist_ci]
assert not coll, f"case-insensitive collision on new products: {coll[:5]}"
# GUARD: new product ids unique among themselves
ci=collections.Counter(r['product_id'].strip().upper() for r in newp)
assert not [k for k,v in ci.items() if v>1], "duplicate new product ids"
print(f"guards passed: {len(newp)} new products, no collisions")

q=lambda s: "'" + str(s).replace("\\","\\\\").replace("'","''") + "'"
REF='SEPT-RESCAN-2026-08-31'; CD='2026-08-31'; WH=54
L=[]
L.append("SET SESSION innodb_lock_wait_timeout=120;")
L.append("START TRANSACTION;")
L.append("DROP TEMPORARY TABLE IF EXISTS _plan; DROP TEMPORARY TABLE IF EXISTS _newp; DROP TEMPORARY TABLE IF EXISTS _sh;")
L.append("""CREATE TEMPORARY TABLE _plan(
  part_key VARCHAR(255), part_display VARCHAR(255), location_pk BIGINT UNSIGNED,
  qty INT, product_pk BIGINT UNSIGNED NULL, price DECIMAL(10,2) NULL,
  KEY(part_key), KEY(product_pk), KEY(location_pk)) ENGINE=InnoDB;""")
L.append("CREATE TEMPORARY TABLE _newp(product_id VARCHAR(255), spn VARCHAR(255), partslink VARCHAR(255), supplier VARCHAR(255)) ENGINE=InnoDB;")
L.append("CREATE TEMPORARY TABLE _sh(location_id BIGINT UNSIGNED PRIMARY KEY) ENGINE=InnoDB;")

def bulk(table, cols, rows, per=400):
    for i in range(0,len(rows),per):
        vals=",".join("("+",".join(r)+")" for r in rows[i:i+per])
        L.append(f"INSERT INTO {table} ({cols}) VALUES {vals};")

bulk("_plan","part_key,part_display,location_pk,qty",
     [[q(r['part_key']), q(r['part_display']), loc_by_key[r['shelf_key']]['id'], r['qty']] for r in plan])
bulk("_newp","product_id,spn,partslink,supplier",
     [[q(r['product_id']),q(r['supplier_part_number']),q(r['partslink']),q(r['supplier'])] for r in newp])
bulk("_sh","location_id",[[s['location_id']] for s in shelves])

L.append("-- 1. create the missing products at price 0 (enriched afterwards)")
L.append("""INSERT INTO products (product_id, supplier_part_number, partslink, supplier, average_price, status, created_at, updated_at)
SELECT product_id, spn, partslink, supplier, 0.00, 'active', NOW(), NOW() FROM _newp;""")

L.append("-- 2. reactivate the shelves the sheet counts stock on")
L.append("UPDATE locations SET is_active=1, updated_at=NOW() WHERE id IN (SELECT location_id FROM _sh) AND is_active=0;")

L.append("-- 3. resolve plan -> product ids and prices")
L.append("UPDATE _plan p JOIN products pr ON pr.product_id = p.part_display SET p.product_pk=pr.id, p.price=pr.average_price;")
L.append("""UPDATE _plan p JOIN products pr
   ON REPLACE(REPLACE(REPLACE(UPPER(pr.product_id),' ',''),'\\t',''),'\\n','') = p.part_key
 SET p.product_pk=pr.id, p.price=pr.average_price WHERE p.product_pk IS NULL;""")
L.append("SELECT COUNT(*) INTO @unresolved FROM _plan WHERE product_pk IS NULL OR location_pk IS NULL;")

L.append("-- 4. EMPTY every shelf the sheet names")
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 s.product_id,s.warehouse_id,s.location_id,'location_emptied',s.quantity,-s.quantity,0,
       s.average_price,'stock_take',{q(REF)},NULL,
       'September rescan - shelf emptied before reload (UPLOAD 9-1-26.xlsx)',NOW()
FROM stocks s WHERE s.location_id IN (SELECT location_id FROM _sh) AND s.quantity<>0;""")
L.append("SELECT ROW_COUNT() INTO @emptied_rows;")
L.append("UPDATE stocks SET quantity=0, updated_at=NOW() WHERE location_id IN (SELECT location_id FROM _sh) AND quantity<>0;")

L.append("-- 5. LOAD the counted quantities, keyed on (part, shelf)")
L.append(f"""UPDATE stocks s JOIN _plan p ON s.product_id=p.product_pk AND s.location_id=p.location_pk
   SET s.quantity=p.qty, s.average_price=p.price, s.warehouse_id={WH},
       s.last_counted_at={q(CD)}, s.updated_at=NOW();""")
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 p.product_pk,{WH},p.location_pk,NULL,p.qty,0,p.price,1,'new',{q(CD)},NOW(),NOW()
FROM _plan p LEFT JOIN stocks s ON s.product_id=p.product_pk AND s.location_id=p.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 p.product_pk,{WH},p.location_pk,'stock_take',0,p.qty,p.qty,p.price,'stock_take',{q(REF)},NULL,
       'September rescan - counted quantity placed (UPLOAD 9-1-26.xlsx)',NOW() FROM _plan p;""")

L.append("-- 6. VERIFY")
L.append("SELECT COUNT(*) INTO @pos FROM stocks s JOIN _sh h ON h.location_id=s.location_id WHERE s.quantity>0;")
L.append("SELECT IFNULL(SUM(s.quantity),0) INTO @units FROM stocks s JOIN _sh h ON h.location_id=s.location_id WHERE s.quantity>0;")
L.append(f"SELECT @unresolved AS unresolved, @pos AS positions_after, @units AS units_after, {len(plan)} AS expect_pos, {sum(int(r['qty']) for r in plan)} AS expect_units;")
open('load.sql','w').write("\n".join(L)+"\n")
print(f"wrote load.sql  ({len(L)} statements, {len(plan)} plan rows, {sum(int(r['qty']) for r in plan)} units)")