#!/usr/bin/env python3
"""Independent extraction of the rescan workbook. No app code, no assumptions carried over."""
import openpyxl, csv, collections, sys

SRC='/home/whgoparts/public_html/whims-dev/dev-documents/UPLOAD 9-1-26.xlsx'
wb=openpyxl.load_workbook(SRC, read_only=True, data_only=True)
ws=wb['Sheet1']
rows=list(ws.iter_rows(values_only=True))
hdr=[(h or '').strip() if isinstance(h,str) else h for h in rows[0]]
print("HEADER:", [f"{i}:{h}" for i,h in enumerate(hdr) if h])

body=[r for r in rows[1:] if any(c is not None and str(c).strip()!='' for c in r)]
print(f"non-empty data rows: {len(body)}")

def s(v):
    return '' if v is None else str(v).strip()

# --- validate assumption: col0 and col2 are the same PRODUCT ID
mismatch=[(i+2,s(r[0]),s(r[2])) for i,r in enumerate(body) if s(r[0]).upper()!=s(r[2]).upper()]
print(f"col0 vs col2 PRODUCT ID mismatches: {len(mismatch)}")
for m in mismatch[:10]: print("   ", m)

# --- validate: quantity always 1?
qc=collections.Counter(r[5] for r in body)
print("QUANTITY values:", dict(qc))

# --- validate: any rows missing sku or shelf?
bad=[(i+2,s(r[0]),s(r[7])) for i,r in enumerate(body) if not s(r[0]) or not s(r[7])]
print(f"rows missing sku or shelf: {len(bad)}"); [print("   ",b) for b in bad[:10]]

# --- write clean scan file
out=[]
for i,r in enumerate(body):
    out.append({
        'row': i+2,
        'sku_raw': s(r[0]),
        'sku': s(r[0]).upper(),
        'partslink': s(r[1]).upper(),
        'supplier': s(r[4]),
        'qty': int(r[5]) if r[5] is not None else 0,
        'shelf_raw': s(r[7]),
        'shelf': s(r[7]).upper(),
        'buy_price': s(r[10]),
        'note': s(r[11]),
    })
with open('scan_lines.csv','w',newline='') as f:
    w=csv.DictWriter(f, fieldnames=list(out[0].keys())); w.writeheader(); w.writerows(out)
print(f"wrote scan_lines.csv ({len(out)} lines)")

# --- casing anomalies in shelf codes
raw_shelves=collections.Counter(o['shelf_raw'] for o in out)
up_shelves=collections.Counter(o['shelf'] for o in out)
print(f"distinct shelf raw={len(raw_shelves)} upper={len(up_shelves)}")
# any shelf whose raw form differs from upper form
diff={k for k in raw_shelves if k!=k.upper()}
print("shelf codes not already uppercase:", sorted(diff)[:20])

# --- duplicate sku+shelf (means qty>1 for that position)
pos=collections.Counter((o['sku'],o['shelf']) for o in out)
dupes={k:v for k,v in pos.items() if v>1}
print(f"distinct (sku,shelf) positions: {len(pos)}; positions scanned more than once: {len(dupes)}")
print("  top dupes:", sorted(dupes.items(), key=lambda x:-x[1])[:8])

# --- same SKU on multiple shelves within the scan
by_sku=collections.defaultdict(set)
for o in out: by_sku[o['sku']].add(o['shelf'])
multi={k:v for k,v in by_sku.items() if len(v)>1}
print(f"distinct SKUs in scan: {len(by_sku)}; SKUs on >1 shelf: {len(multi)}")