"""Builds orders-q2.xlsx, a deliberately messy Q2 2026 order export, and prints the ground truth.
Run: python3 make_fixture.py  (needs openpyxl). Seeded, so the file is reproducible."""
import random, datetime as dt
from openpyxl import Workbook

random.seed(20260917)
PRODUCTS = [  # name, category, unit price EUR
    ("Olive Oil 500ml", "Pantry", 12.50), ("Olive Oil 1L", "Pantry", 21.00),
    ("Flor de Sal 250g", "Pantry", 6.80), ("Piri-piri Sauce", "Pantry", 4.90),
    ("Sardines Tin x3", "Pantry", 9.60), ("Ceramic Bowl", "Home", 18.00),
    ("Linen Tea Towel", "Home", 14.00), ("Cork Coasters x4", "Home", 11.00),
    ("Vinho Verde 750ml", "Drinks", 8.90), ("Ginjinha 200ml", "Drinks", 13.50),
]
REGIONS = ["Lisboa", "Porto", "Algarve", "Coimbra", "Braga"]
CUSTOMERS = ["Ana Ferreira", "Bruno Costa", "Carla Santos", "Diogo Martins", "Eva Rocha",
             "Filipe Sousa", "Guilherme Pinto", "Helena Alves", "Inês Carvalho", "João Silva",
             "Lara Mendes", "Miguel Ramos", "Nuno Lopes", "Olívia Gomes", "Pedro Neves",
             "Rita Teixeira", "Sofia Marques", "Tiago Nunes", "Vera Castro", "Xavier Reis"]

rows = []
d0 = dt.date(2026, 4, 1)
for i in range(120):
    date = d0 + dt.timedelta(days=random.randint(0, 90))
    name, cat, price = random.choice(PRODUCTS)
    qty = random.choice([1, 1, 1, 2, 2, 3, 4, 6])
    rows.append(dict(id=f"PT-{1001+i}", date=date, customer=random.choice(CUSTOMERS),
                     product=name, category=cat, qty=qty, price=price,
                     total=round(qty*price, 2), region=random.choice(REGIONS)))
rows.sort(key=lambda r: r["date"])

# --- ground truth on the clean data ---
truth = {"month": {}, "category": {}, "units": {}}
for r in rows:
    m = r["date"].strftime("%B")
    truth["month"][m] = round(truth["month"].get(m, 0) + r["total"], 2)
    truth["category"][r["category"]] = round(truth["category"].get(r["category"], 0) + r["total"], 2)
    truth["units"][r["product"]] = truth["units"].get(r["product"], 0) + r["qty"]

# --- now break things, recording what was broken ---
idx = list(range(120)); random.shuffle(idx)
dup_src = idx[0:4]            # 4 exact duplicate rows appended
mismatch = idx[4:9]           # 5 rows whose Total <> Qty*Price
blank_qty = idx[9:12]         # 3 rows with Qty missing (Total still right, so it is derivable)
blank_region = idx[12:14]     # 2 rows with Region missing
blank_cat = idx[14:22]        # 8 rows with Category missing (inferable from product)
text_price = idx[22:40]       # 18 rows with the price as text "€12,50"
messy_name = idx[40:58]       # 18 rows with product name case/space variants
dmy_date = idx[58:88]         # 30 rows with the date as dd/mm/yyyy text
mon_date = idx[88:100]        # 12 rows with the date as "Apr 5, 2026" text

def variant(name):
    return random.choice([name.lower(), name + "  ", "  " + name, name.replace("ml", " ml").replace("g", " g"), name.upper()])

out = []
for i, r in enumerate(rows):
    c = dict(r)
    if i in mismatch:
        c["total"] = round(c["total"] + random.choice([-3.0, 2.5, 5.0, -1.2, 10.0]), 2)
    if i in blank_qty: c["qty"] = None
    if i in blank_region: c["region"] = None
    if i in blank_cat: c["category"] = None
    if i in text_price: c["price"] = "€" + f"{c['price']:.2f}".replace(".", ",")
    if i in messy_name: c["product"] = variant(c["product"])
    if i in dmy_date: c["date"] = c["date"].strftime("%d/%m/%Y")
    elif i in mon_date: c["date"] = c["date"].strftime("%b %-d, %Y")
    out.append(c)
for i in dup_src:
    out.append(dict(out[i]))
random.shuffle(out)

wb = Workbook(); ws = wb.active; ws.title = "Orders"
ws.append(["Order ID", "Date", "Customer", "Product", "Category", "Qty", "Unit Price", "Total", "Region"])
for c in out:
    ws.append([c["id"], c["date"], c["customer"], c["product"], c["category"], c["qty"], c["price"], c["total"], c["region"]])
for cell in ws["B"]:
    if isinstance(cell.value, dt.date): cell.number_format = "yyyy-mm-dd"
wb.save("orders-q2.xlsx")

print("rows written:", len(out), "(120 orders + 4 duplicates)")
print("revenue per month:", truth["month"])
print("revenue per category:", truth["category"])
top = max(truth["units"], key=truth["units"].get)
print("top product by units:", top, truth["units"][top], "| all:", truth["units"])
print("grand total:", round(sum(truth["month"].values()), 2))
print("duplicate order ids:", sorted(rows[i]["id"] for i in dup_src))
print("mismatched totals:", sorted((rows[i]["id"], rows[i]["total"], out_total) for i, out_total in
      [(i, next(c["total"] for c in out if c["id"] == rows[i]["id"])) for i in mismatch]))
print("blank qty:", sorted(rows[i]["id"] for i in blank_qty))
print("blank region:", sorted(rows[i]["id"] for i in blank_region))
print("blank category:", sorted(rows[i]["id"] for i in blank_cat))
print("text prices:", len(text_price), "| messy names:", len(messy_name), "| dd/mm dates:", len(dmy_date), "| 'Apr 5, 2026' dates:", len(mon_date))