#!/usr/bin/env python3 """Task 1: Reconcile Northline Workshops October 2026 (CAD). Rules from inputs/policy.md. Deterministic; standard library only. Reads inputs/*.csv, writes output/cleaned-*.csv and output/exceptions.csv, prints totals used in output/summary.md. """ import csv, os, re from datetime import date from decimal import Decimal, InvalidOperation IN = "inputs" OUT = "output" os.makedirs(OUT, exist_ok=True) START, END = date(2026, 10, 1), date(2026, 10, 31) MONTHS = {m: i + 1 for i, m in enumerate( ["jan", "feb", "mar", "apr", "may", "jun", "jul", "aug", "sep", "oct", "nov", "dec"])} def parse_date(s): """Return (date|None, raw_string). Slash form = DD/MM/YYYY per policy.""" s = s.strip() if not s: return None, s m = re.fullmatch(r"(\d{4})-(\d{2})-(\d{2})", s) if m: try: return date(*map(int, m.groups())), s except ValueError: return None, s m = re.fullmatch(r"(\d{2})/(\d{2})/(\d{4})", s) if m: d, mo, y = map(int, m.groups()) try: return date(y, mo, d), s except ValueError: return None, s m = re.fullmatch(r"([A-Za-z]{3,})\.?\s+(\d{1,2})\s+(\d{4})", s) if m and m.group(1)[:3].lower() in MONTHS: try: return date(int(m.group(3)), MONTHS[m.group(1)[:3].lower()], int(m.group(2))), s except ValueError: return None, s return None, s def parse_money(s): """Return (Decimal|None, raw). Handles CAD / $ prefixes. Empty -> None (unknown).""" t = s.strip() if not t: return None, s t = re.sub(r"^(CAD\s*|\$\s*)", "", t, flags=re.I).strip().replace(",", "") try: return Decimal(t).quantize(Decimal("0.01")), s except InvalidOperation: return None, s def norm_product(s): t = s.strip().lower() return {"workshop": "Workshop", "course": "Course"}.get(t) def load(name): with open(os.path.join(IN, name), newline="", encoding="utf-8") as f: return list(csv.DictReader(f)) exceptions = [] # file,row_id,record_id,disposition,reason,detail def flag(file, row, rec, disp, reason, detail): exceptions.append({"source_file": file, "row_id": row, "record_id": rec, "disposition": disp, "reason": reason, "detail": detail}) # ---------------- SALES ---------------- sales_rows = load("sales.csv") by_order = {} for r in sales_rows: oid = r["order_id"].strip() key = tuple(r[k].strip() for k in ("order_id", "date", "product", "status", "gross_cad", "updated_at")) if oid in by_order: prev = by_order[oid] if key == prev["key"]: flag("sales.csv", r["row_id"], oid, "collapsed_duplicate", "Exact duplicate row (policy: exact duplicate rows can be collapsed)", "Duplicate of " + ",".join(prev["row_ids"]) + "; kept once.") prev["row_ids"].append(r["row_id"]) else: # conflicting: keep latest updated_at (ISO timestamps sort lexically) if r["updated_at"].strip() > prev["row"]["updated_at"].strip(): flag("sales.csv", prev["row"]["row_id"], oid, "superseded_conflict", "Conflicting rows, same order ID; policy keeps latest updated_at", "Superseded by " + r["row_id"] + " (updated_at " + r["updated_at"] + " > " + prev["row"]["updated_at"] + ").") by_order[oid] = {"key": key, "row": r, "row_ids": [r["row_id"]]} else: flag("sales.csv", r["row_id"], oid, "superseded_conflict", "Conflicting rows, same order ID; policy keeps latest updated_at", "Older than kept row(s) " + ",".join(prev["row_ids"]) + ".") prev["row_ids"].append(r["row_id"]) # keep id list? no—only kept rows prev["row_ids"].pop() else: by_order[oid] = {"key": key, "row": r, "row_ids": [r["row_id"]]} paid_orders = {} # oid -> record clean_sales = [] for oid, ent in by_order.items(): r = ent["row"] ids = ";".join(ent["row_ids"]) d, draw = parse_date(r["date"]) prod = norm_product(r["product"]) amt, araw = parse_money(r["gross_cad"]) status = r["status"].strip().lower() problems = [] if d is None: problems.append("unparseable date: " + repr(r["date"])) if prod is None: problems.append("unmapped product: " + repr(r["product"])) if status != "paid": problems.append("status '" + status + "' is not paid (policy: only paid is revenue)") if d is not None and not (START <= d <= END): problems.append("order date " + d.isoformat() + " outside Oct 1-31, 2026") if amt is None: problems.append("gross amount missing/unparseable: " + repr(r["gross_cad"])) if problems: flag("sales.csv", ids, oid, "excluded", "; ".join(problems), "status=" + r["status"].strip() + "; date=" + r["date"] + "; product=" + r["product"].strip() + "; gross=" + r["gross_cad"]) continue paid_orders[oid] = {"product": prod, "gross": amt} clean_sales.append({"row_ids": ids, "order_id": oid, "date": d.isoformat(), "product": prod, "status": "paid", "gross_cad": f"{amt:.2f}", "updated_at": r["updated_at"].strip()}) clean_sales.sort(key=lambda x: (x["date"], x["order_id"])) # ---------------- REFUNDS ---------------- refund_rows = load("refunds.csv") by_refund = {} for r in refund_rows: rid = r["refund_id"].strip() key = tuple(r[k].strip() for k in ("refund_id", "order_id", "settled_date", "status", "amount_cad")) if rid in by_refund: prev = by_refund[rid] if key == prev["key"]: flag("refunds.csv", r["row_id"], rid, "collapsed_duplicate", "Exact duplicate row (policy: refund ID unique; exact duplicates collapse)", "Duplicate of " + ",".join(prev["row_ids"]) + "; kept once.") prev["row_ids"].append(r["row_id"]) else: flag("refunds.csv", r["row_id"], rid, "excluded", "Conflicting rows with same refund ID and no updated_at to resolve", "Kept first row " + ",".join(prev["row_ids"]) + "; conflict unresolved by policy.") else: by_refund[rid] = {"key": key, "row": r, "row_ids": [r["row_id"]]} clean_refunds = [] provisional = False for rid, ent in by_refund.items(): r = ent["row"] ids = ";".join(ent["row_ids"]) oid = r["order_id"].strip() d, _ = parse_date(r["settled_date"]) amt, araw = parse_money(r["amount_cad"]) status = r["status"].strip().lower() problems = [] if status != "settled": problems.append("status '" + status + "' is not settled (policy: do not subtract pending refunds)") if d is None: problems.append("unparseable settled date: " + repr(r["settled_date"])) elif not (START <= d <= END): problems.append("settlement date " + d.isoformat() + " outside Oct 1-31, 2026") if oid not in paid_orders: problems.append("no valid included paid order for order_id " + oid) if amt is None: problems.append("refund amount missing (unknown, never zero); net revenue is provisional") if problems: if amt is None and status == "settled": provisional = True flag("refunds.csv", ids, rid, "excluded/flagged", "; ".join(problems), "order_id=" + oid + "; settled_date=" + r["settled_date"] + "; status=" + r["status"].strip() + "; amount=" + repr(r["amount_cad"])) continue order = paid_orders[oid] if amt > order["gross"]: flag("refunds.csv", ids, rid, "excluded/flagged", "Refund exceeds paid gross for the order (policy: flag)", f"refund {amt:.2f} > paid gross {order['gross']:.2f}") continue clean_refunds.append({"row_ids": ids, "refund_id": rid, "order_id": oid, "settled_date": d.isoformat(), "status": "settled", "amount_cad": f"{amt:.2f}", "product": order["product"]}) clean_refunds.sort(key=lambda x: (x["settled_date"], x["refund_id"])) # ---------------- ADS ---------------- ad_rows = load("ads.csv") by_ad = {} for r in ad_rows: aid = r["ad_id"].strip() # normalize before duplicate comparison (policy: ignore case/spaces on channel & product) normkey = (aid, r["date"].strip(), (norm_product(r["product"]) or r["product"].strip().lower()), r["channel"].strip().lower(), (parse_money(r["spend_cad"])[0])) if aid in by_ad: prev = by_ad[aid] if normkey == prev["normkey"]: flag("ads.csv", r["row_id"], aid, "collapsed_duplicate", "Exact duplicate row after normalization (policy: ad ID unique)", "Duplicate of " + ",".join(prev["row_ids"]) + "; kept once.") prev["row_ids"].append(r["row_id"]) else: flag("ads.csv", r["row_id"], aid, "excluded", "Conflicting rows with same ad ID and no tiebreaker supplied", "Kept first row " + ",".join(prev["row_ids"]) + ".") else: by_ad[aid] = {"normkey": normkey, "row": r, "row_ids": [r["row_id"]]} clean_ads = [] for aid, ent in by_ad.items(): r = ent["row"] ids = ";".join(ent["row_ids"]) d, _ = parse_date(r["date"]) prod = norm_product(r["product"]) amt, _ = parse_money(r["spend_cad"]) chan = r["channel"].strip().lower() problems = [] if d is None: problems.append("unparseable date: " + repr(r["date"])) elif not (START <= d <= END): problems.append("ad date " + d.isoformat() + " outside Oct 1-31, 2026") if prod is None: problems.append("unmapped product: " + repr(r["product"])) if amt is None: problems.append("spend missing/unparseable: " + repr(r["spend_cad"])) if problems: flag("ads.csv", ids, aid, "excluded", "; ".join(problems), "date=" + r["date"] + "; product=" + r["product"].strip() + "; channel=" + r["channel"].strip() + "; spend=" + r["spend_cad"]) continue clean_ads.append({"row_ids": ids, "ad_id": aid, "date": d.isoformat(), "product": prod, "channel": chan, "spend_cad": f"{amt:.2f}"}) clean_ads.sort(key=lambda x: (x["date"], x["ad_id"])) # ---------------- WRITE FILES ---------------- def write_csv(path, rows, fields): with open(path, "w", newline="", encoding="utf-8") as f: w = csv.DictWriter(f, fieldnames=fields) w.writeheader() w.writerows(rows) write_csv(os.path.join(OUT, "cleaned-sales.csv"), clean_sales, ["row_ids", "order_id", "date", "product", "status", "gross_cad", "updated_at"]) write_csv(os.path.join(OUT, "cleaned-refunds.csv"), clean_refunds, ["row_ids", "refund_id", "order_id", "settled_date", "status", "amount_cad", "product"]) write_csv(os.path.join(OUT, "cleaned-ads.csv"), clean_ads, ["row_ids", "ad_id", "date", "product", "channel", "spend_cad"]) write_csv(os.path.join(OUT, "exceptions.csv"), exceptions, ["source_file", "row_id", "record_id", "disposition", "reason", "detail"]) # ---------------- TOTALS ---------------- def totals(prod=None): orders = [r for r in clean_sales if prod is None or r["product"] == prod] refs = [r for r in clean_refunds if prod is None or r["product"] == prod] ads = [r for r in clean_ads if prod is None or r["product"] == prod] n = len(orders) g = sum(Decimal(r["gross_cad"]) for r in orders) rf = sum(Decimal(r["amount_cad"]) for r in refs) ad = sum(Decimal(r["spend_cad"]) for r in ads) net = g - rf contrib = net - ad per = (net / n).quantize(Decimal("0.01")) if n else None return dict(n=n, gross=g, refunds=rf, net=net, ads=ad, contrib=contrib, per=per) for label, p in [("Workshop", "Workshop"), ("Course", "Course"), ("Combined", None)]: t = totals(p) print(f"{label}: orders={t['n']} gross={t['gross']:.2f} refunds={t['refunds']:.2f} " f"net={t['net']:.2f} ads={t['ads']:.2f} contrib={t['contrib']:.2f} per_order={t['per']}") print("provisional (missing settled refund amount):", provisional) print("row reconciliation:") print(f" sales: raw={len(sales_rows)} clean={len(clean_sales)} " f"clean_row_ids={sum(len(r['row_ids'].split(';')) for r in clean_sales)} exceptions={sum(1 for e in exceptions if e['source_file']=='sales.csv')}") print(f" refunds: raw={len(refund_rows)} clean={len(clean_refunds)} " f"clean_row_ids={sum(len(r['row_ids'].split(';')) for r in clean_refunds)} exceptions={sum(1 for e in exceptions if e['source_file']=='refunds.csv')}") print(f" ads: raw={len(ad_rows)} clean={len(clean_ads)} " f"clean_row_ids={sum(len(r['row_ids'].split(';')) for r in clean_ads)} exceptions={sum(1 for e in exceptions if e['source_file']=='ads.csv')}") # sanity: every raw row_id accounted for exactly once across clean row_ids + exceptions for fname, clean in [("sales.csv", clean_sales), ("refunds.csv", clean_refunds), ("ads.csv", clean_ads)]: raw_ids = {r["row_id"] for r in load(fname)} clean_ids = [] for r in clean: clean_ids += r["row_ids"].split(";") dup_ids, excl_ids = [], [] for e in exceptions: if e["source_file"] == fname: (dup_ids if e["disposition"] == "collapsed_duplicate" else excl_ids).extend(e["row_id"].split(";")) # collapsed duplicates are provenance in cleaned row_ids AND explained in exceptions assert set(dup_ids) <= set(clean_ids), (fname, "dup ids not in cleaned provenance") assert not (set(clean_ids) & set(excl_ids)), (fname, "excluded id in cleaned") assert len(excl_ids) == len(set(excl_ids)), (fname, "excluded id listed twice") accounted = clean_ids + excl_ids assert len(accounted) == len(set(accounted)), (fname, "double-counted row ids") assert set(accounted) == raw_ids, (fname, "missing/extra row ids", raw_ids ^ set(accounted)) print("CHECK PASSED: every raw row_id accounted for exactly once (cleaned provenance or exclusion); duplicate IDs also explained in exceptions.csv")