#!/usr/bin/env python3
"""Hunt obvious errors and typos in the rescan workbook."""
import csv, collections, re, difflib
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'); scan=list(csv.DictReader(open('scan_lines.csv',newline='')))
nk=lambda s: re.sub(r'\s+','',str(s)).upper()
cat={nk(p['product_id']):p for p in products}
cat_pl=collections.defaultdict(list)
for p in products:
    if p['partslink'].strip(): cat_pl[nk(p['partslink'])].append(p)

print("="*100); print("FILE DATA-QUALITY AUDIT"); print("="*100)

# 1. supplier typos
sup=collections.Counter(r['supplier'] for r in scan)
print("\n[1] SUPPLIER column")
for s,n in sup.most_common():
    flag='   <-- SUSPECT (single row, not a known supplier)' if n<=2 else ''
    print(f"    {s:<12} {n:>5}{flag}")
for r in scan:
    if sup[r['supplier']]<=2:
        print(f"        row {r['row']}: sku={r['sku']} shelf='{r['shelf_raw']}' supplier='{r['supplier']}'")

# 2. shelf casing
print("\n[2] SHELF spelling drift")
sh=collections.Counter(r['shelf_raw'] for r in scan)
bad=[k for k in sh if k!=re.sub(r'\s+',' ',k.strip()).upper()]
for k in bad: print(f"    '{k}' x{sh[k]}  -> should be '{re.sub(chr(92)+'s+',' ',k.strip()).upper()}'")
grp=collections.defaultdict(set)
for k in sh: grp[nk(k)].add(k)
for k,v in grp.items():
    if len(v)>1: print(f"    two spellings of the same shelf in the file: {sorted(v)}")

# 3. blank partslink
print("\n[3] MISSING partslink")
for r in scan:
    if not r['partslink']: print(f"    row {r['row']}: sku={r['sku']} shelf='{r['shelf_raw']}' supplier={r['supplier']}")

# 4. near-miss SKUs (likely typos of a real catalogue part)
print("\n[4] SKUs NOT in catalogue that closely resemble a real one (likely typos)")
unknown=sorted({nk(r['sku']) for r in scan if nk(r['sku']) not in cat})
keys=list(cat)
hits=[]
for u in unknown:
    m=difflib.get_close_matches(u, keys, n=2, cutoff=0.90)
    if m:
        units=sum(1 for r in scan if nk(r['sku'])==u)
        hits.append((u,m,units))
print(f"    {len(hits)} of {len(unknown)} unknown SKUs have a >=0.90 lookalike in the catalogue")
for u,m,units in sorted(hits,key=lambda x:-x[2])[:25]:
    shelves=sorted({r['shelf_raw'] for r in scan if nk(r['sku'])==u})
    print(f"    {u:<20} x{units:<3} on {','.join(shelves)[:26]:<26} -> did they mean {m}?")

# 5. unknown SKU whose partslink IS in the catalogue (mis-keyed id, right part)
print("\n[5] Unknown SKUs whose PARTSLINK matches the catalogue (id mis-keyed, part is real)")
n=0
for u in unknown:
    rs=[r for r in scan if nk(r['sku'])==u]
    pls={nk(r['partslink']) for r in rs if r['partslink']}
    tgt=[p for pl in pls for p in cat_pl.get(pl,[])]
    if tgt:
        n+=1
        if n<=15:
            print(f"    {u:<20} x{len(rs):<3} partslink={sorted(pls)} -> catalogue has "
                  f"{sorted({p['product_id'] for p in tgt})[:3]}")
print(f"    ...{n} such SKUs in total")

# 6. same SKU spread over an implausible number of shelves
print("\n[6] SKUs scanned across many shelves (possible mis-scan or genuine spread)")
bysku=collections.defaultdict(collections.Counter)
for r in scan: bysku[nk(r['sku'])][r['shelf_raw']]+=1
wide=sorted(((k,v) for k,v in bysku.items() if len(v)>=6), key=lambda x:-len(x[1]))
print(f"    {len(wide)} SKUs on 6+ distinct shelves")
for k,v in wide[:10]:
    print(f"    {k:<18} {sum(v.values()):>3} units over {len(v)} shelves: {', '.join(sorted(v))[:60]}")