import csv, statistics as st, sys
COLS = dict(zip(["invoice","period","customer","product","units","list_price","discount_pct","net_sales","cost"], range(9)))
with open(sys.argv[1], newline="", encoding="utf-8") as f:
    r = csv.reader(f); next(r); rows = [x for x in r]
P0, P1 = "2025-Q3", "2026-Q3"

# 1) Data checks (flag, do not fix)
issues = []
for x in rows:
    u, s, c, d = float(x[4]), float(x[7]), float(x[8]), float(x[6])
    if u == 0 and s != 0: issues.append((x[0], "units = 0 with sales"))
    if s and c > s: issues.append((x[0], "cost above net sales"))
    if d >= 0.25: issues.append((x[0], f"discount {d:.0%}"))
bad = {i for i, _ in issues}
clean = [x for x in rows if x[0] not in bad]

# 2) Aggregate by product and period
agg = {}
for x in clean:
    k = (x[1], x[3]); a = agg.setdefault(k, [0.0, 0.0, 0.0])
    a[0] += float(x[4]); a[1] += float(x[7]); a[2] += float(x[8])
prods = sorted({p for _, p in agg})
Q = {t: {p: agg.get((t, p), [0, 0, 0])[0] for p in prods} for t in (P0, P1)}
S = {t: {p: agg.get((t, p), [0, 0, 0])[1] for p in prods} for t in (P0, P1)}
C = {t: {p: agg.get((t, p), [0, 0, 0])[2] for p in prods} for t in (P0, P1)}
price = {t: {p: S[t][p] / Q[t][p] for p in prods} for t in (P0, P1)}
ucost = {t: {p: C[t][p] / Q[t][p] for p in prods} for t in (P0, P1)}
m0 = {p: price[P0][p] - ucost[P0][p] for p in prods}
q0t, q1t = sum(Q[P0].values()), sum(Q[P1].values())
share0 = {p: Q[P0][p] / q0t for p in prods}

# 3) Margin bridge: volume, mix, price, cost (sums exactly to the change)
volume = (q1t - q0t) * sum(share0[p] * m0[p] for p in prods)
mix = sum((Q[P1][p] - q1t * share0[p]) * m0[p] for p in prods)
price_eff = sum(Q[P1][p] * (price[P1][p] - price[P0][p]) for p in prods)
cost_eff = -sum(Q[P1][p] * (ucost[P1][p] - ucost[P0][p]) for p in prods)
M0 = sum(S[P0].values()) - sum(C[P0].values()); M1 = sum(S[P1].values()) - sum(C[P1].values())
assert abs((volume + mix + price_eff + cost_eff) - (M1 - M0)) < 0.01, "bridge does not tie"

print(f"Gross margin {P0}: {M0:,.0f} ({M0/sum(S[P0].values()):.1%})  {P1}: {M1:,.0f} ({M1/sum(S[P1].values()):.1%})")
for n, v in (("volume", volume), ("mix", mix), ("price", price_eff), ("unit cost", cost_eff)):
    print(f"  {n}: {v:+,.0f}")
for p in prods:
    print(f"  {p}: units {Q[P0][p]:,.0f} -> {Q[P1][p]:,.0f} | net price {price[P0][p]:.2f} -> {price[P1][p]:.2f} | unit cost {ucost[P0][p]:.2f} -> {ucost[P1][p]:.2f}")
print("Data issues (excluded from the bridge):", issues)
ex = [x for x in rows if x[0] in bad]
print(f"Excluded lines: {len(ex)}, net sales {sum(float(x[7]) for x in ex):,.2f}, cost {sum(float(x[8]) for x in ex):,.2f} (reconcile them separately)")