"""Checks a returned workbook against the ground truth. Usage: python3 check_returned.py returned/<tool>.xlsx"""
import sys, datetime as dt
from openpyxl import load_workbook
from collections import Counter
CANON = {"Olive Oil 500ml","Olive Oil 1L","Flor de Sal 250g","Piri-piri Sauce","Sardines Tin x3","Ceramic Bowl","Linen Tea Towel","Cork Coasters x4","Vinho Verde 750ml","Ginjinha 200ml"}
TRUTH_M = {"April":1538.6,"May":847.4,"June":1009.5}
wb = load_workbook(sys.argv[1], data_only=False)
print("sheets:", wb.sheetnames)
ws = wb[wb.sheetnames[0]]
hdr = [str(c.value).strip() if c.value is not None else "" for c in ws[1]]
print("header:", hdr)
def col(name):
    for i,h in enumerate(hdr):
        if h.lower().replace(" ","").startswith(name): return i
    return None
ci, cd, cp, cc, cq, cu, ct, cr = (col(n) for n in ("orderid","date","product","category","qty","unitprice","total","region"))
rows = [[c.value for c in r] for r in ws.iter_rows(min_row=2) if r[0].value not in (None, "")]
print("data rows:", len(rows))
ids = [r[ci] for r in rows]; print("unique ids:", len(set(ids)), "| duplicated ids still present:", [k for k,v in Counter(ids).items() if v>1])
dates = [r[cd] for r in rows]
print("date types:", Counter(type(d).__name__ for d in dates))
prices = [r[cu] for r in rows]; print("price types:", Counter(type(p).__name__ for p in prices))
prods = Counter(r[cp] for r in rows); noncanon = {p:n for p,n in prods.items() if p not in CANON}
print("product spellings:", len(prods), "| non-canonical:", noncanon)
print("blank category:", sum(1 for r in rows if r[cc] in (None,"")), "| blank qty:", sum(1 for r in rows if r[cq] in (None,"")), "| region values:", Counter(r[cr] for r in rows if r[cr] not in ("Lisboa","Porto","Algarve","Coimbra","Braga")))
# revenue by month from the returned sheet
m = {}
bad=0
for r in rows:
    d=r[cd]; q=r[cq]; p=r[cu]; t=r[ct]
    if not isinstance(d, dt.date):
        try: d=dt.datetime.fromisoformat(str(d)[:10])
        except Exception: bad+=1; continue
    try: v=float(q)*float(p)
    except Exception: v=float(t) if t is not None else 0
    k=d.strftime("%B"); m[k]=round(m.get(k,0)+v,2)
print("revenue by month from returned rows (qty*price):", m, "| unparseable dates:", bad, "| matches truth:", all(abs(m.get(k,0)-v)<0.011 for k,v in TRUTH_M.items()))
mism = sum(1 for r in rows if r[cq] not in (None,"") and isinstance(r[cu],(int,float)) and isinstance(r[ct],(int,float)) and abs(r[cq]*r[cu]-r[ct])>0.01)
print("rows where Total still != Qty*Price:", mism)
for name in wb.sheetnames[1:]:
    s=wb[name]; print(f"--- sheet {name}: {s.max_row} rows; first cells:", [[c.value for c in r][:4] for r in s.iter_rows(min_row=1, max_row=6)])