#!/usr/bin/env python3
"""Task 1: Northline Workshops October 2026 reconciliation.

Applies inputs/policy.md rules to inputs/sales.csv, inputs/refunds.csv,
inputs/ads.csv. Writes output/cleaned-*.csv, output/exceptions.csv,
output/summary.md. Standard library only. Raw inputs are never modified.
"""
import csv
import os
import re
from datetime import date, datetime
from decimal import Decimal

IN = "inputs"
OUT = "output"
os.makedirs(OUT, exist_ok=True)

PERIOD_START = date(2026, 10, 1)
PERIOD_END = date(2026, 10, 31)
PRODUCTS = {"workshop": "Workshop", "course": "Course"}


def read(name):
    with open(os.path.join(IN, name), newline="", encoding="utf-8") as f:
        return list(csv.DictReader(f))


def norm_date(raw):
    """ISO, DD/MM/YYYY, or named English month (e.g. 'Oct 05 2026')."""
    s = (raw or "").strip()
    if not s:
        return None
    for fmt in ("%Y-%m-%d", "%d/%m/%Y", "%b %d %Y", "%B %d %Y", "%d %b %Y", "%d %B %Y"):
        try:
            return datetime.strptime(s, fmt).date()
        except ValueError:
            pass
    return None


def norm_money(raw):
    s = (raw or "").strip()
    if not s:
        return None  # unknown, never zero (policy.md)
    s = re.sub(r"(?i)^cad\s*", "", s)
    s = s.replace("$", "").replace(",", "").strip()
    try:
        return Decimal(s).quantize(Decimal("0.01"))
    except Exception:
        return None


def money(d):
    return "" if d is None else f"{d:.2f}"


def in_period(d):
    return d is not None and PERIOD_START <= d <= PERIOD_END


def norm_product(raw):
    return PRODUCTS.get((raw or "").strip().lower())  # None => unmapped, flagged


exceptions = []  # dicts: source_file,row_id,record_id,issue,action,detail


def flag(src, row, rec, issue, action, detail):
    exceptions.append(dict(source_file=src, row_id=row, record_id=rec,
                           issue=issue, action=action, detail=detail))


# ---------------- SALES ----------------
sales_raw = read("sales.csv")
sales = []
for r in sales_raw:
    sales.append({
        "row_id": r["row_id"], "order_id": r["order_id"].strip(),
        "date_raw": r["date"], "date": norm_date(r["date"]),
        "product": norm_product(r["product"]),
        "status": r["status"].strip().lower(),
        "gross": norm_money(r["gross_cad"]),
        "updated_at": r["updated_at"].strip(),
    })

# collapse exact duplicates (all fields except row_id)
def sales_key(r):
    return (r["order_id"], r["date_raw"], r["product"], r["status"],
            r["gross"], r["updated_at"])

seen = {}
deduped = []
for r in sales:
    k = sales_key(r)
    if k in seen:
        flag("sales.csv", r["row_id"], r["order_id"], "exact_duplicate",
             "collapsed", f"Identical to {seen[k]} on all fields except row_id; kept {seen[k]}.")
    else:
        seen[k] = r["row_id"]
        deduped.append(r)

# conflicting rows, same order_id: keep latest updated_at
by_order = {}
for r in deduped:
    cur = by_order.get(r["order_id"])
    if cur is None:
        by_order[r["order_id"]] = r
    else:
        keep, drop = (r, cur) if r["updated_at"] > cur["updated_at"] else (cur, r)
        by_order[r["order_id"]] = keep
        flag("sales.csv", drop["row_id"], drop["order_id"], "superseded_conflict",
             "excluded", f"Same order_id as {keep['row_id']}; kept latest updated_at "
                         f"({keep['updated_at']}, gross {money(keep['gross'])}) per policy.md.")

sales_clean = sorted(by_order.values(), key=lambda r: r["row_id"])
for r in sales_clean:
    reasons = []
    if r["date"] is None:
        reasons.append("unparseable_date")
    elif not in_period(r["date"]):
        reasons.append("outside_october_2026")
    if r["status"] != "paid":
        reasons.append(f"status_{r['status']}_not_revenue")
    if r["product"] is None:
        reasons.append("unmapped_product")
    if r["gross"] is None:
        reasons.append("missing_or_unparseable_gross")
    r["included"] = not reasons
    r["note"] = ";".join(reasons)
    for reason in reasons:
        flag("sales.csv", r["row_id"], r["order_id"], reason, "excluded",
             f"date={r['date_raw']}, status={r['status']}, gross={money(r['gross']) or '(missing)'}; "
             "policy: only status paid within Oct 1-31 counts as revenue.")

paid_orders = {r["order_id"]: r for r in sales_clean if r["included"]}

# ---------------- REFUNDS ----------------
refunds_raw = read("refunds.csv")
refunds = []
for r in refunds_raw:
    refunds.append({
        "row_id": r["row_id"], "refund_id": r["refund_id"].strip(),
        "order_id": r["order_id"].strip(),
        "date_raw": r["settled_date"], "date": norm_date(r["settled_date"]),
        "status": r["status"].strip().lower(),
        "amount": norm_money(r["amount_cad"]),
    })

def refund_key(r):
    return (r["refund_id"], r["order_id"], r["date_raw"], r["status"], r["amount"])

seen = {}
deduped = []
for r in refunds:
    k = refund_key(r)
    if k in seen:
        flag("refunds.csv", r["row_id"], r["refund_id"], "exact_duplicate",
             "collapsed", f"Identical to {seen[k]} on all fields except row_id; kept {seen[k]}.")
    else:
        seen[k] = r["row_id"]
        deduped.append(r)

by_refund = {}
for r in deduped:
    if r["refund_id"] in by_refund:
        flag("refunds.csv", r["row_id"], r["refund_id"], "conflicting_refund_id",
             "flagged_unresolved", "Duplicate refund_id with differing fields; no updated_at to resolve. Both kept out pending review.")
        by_refund[r["refund_id"]]["conflict"] = True
        r["conflict"] = True
        by_refund[r["refund_id"] + "_" + r["row_id"]] = r
    else:
        by_refund[r["refund_id"]] = r

refunds_clean = sorted(by_refund.values(), key=lambda r: r["row_id"])
provisional = False
for r in refunds_clean:
    reasons = []
    if r.get("conflict"):
        reasons.append("conflicting_refund_id_unresolved")
    if r["status"] != "settled":
        reasons.append(f"status_{r['status']}_not_deducted")
    if r["date"] is None:
        reasons.append("unparseable_settled_date")
    elif not in_period(r["date"]):
        reasons.append("settlement_outside_october_2026")
    order = paid_orders.get(r["order_id"])
    if order is None:
        reasons.append("no_valid_paid_october_order")
    if r["amount"] is None:
        reasons.append("missing_amount")
        if r["status"] == "settled":
            provisional = True
    r["included"] = not reasons
    r["note"] = ";".join(reasons)
    r["product"] = order["product"] if order else ""
    for reason in reasons:
        detail = (f"order_id={r['order_id']}, settled_date={r['date_raw']}, "
                  f"status={r['status']}, amount={money(r['amount']) or '(missing)'}. ")
        if reason == "no_valid_paid_october_order":
            detail += "Order not found among included paid October orders; cannot validate or attribute."
        elif reason == "missing_amount":
            detail += "Amount unknown (never treated as zero); net revenue is provisional per policy.md."
        elif reason.startswith("status_"):
            detail += "Policy: do not subtract pending refunds."
        elif reason == "settlement_outside_october_2026":
            detail += "Policy: only refunds settled Oct 1-31 are deducted."
        flag("refunds.csv", r["row_id"], r["refund_id"], reason, "excluded", detail)

# refunds cannot exceed paid gross (per order, applied refunds)
applied_by_order = {}
for r in refunds_clean:
    if r["included"]:
        applied_by_order[r["order_id"]] = applied_by_order.get(r["order_id"], Decimal("0")) + r["amount"]
for oid, amt in applied_by_order.items():
    if amt > paid_orders[oid]["gross"]:
        flag("refunds.csv", "", oid, "refunds_exceed_paid_gross", "flagged",
             f"Applied refunds {money(amt)} exceed paid gross {money(paid_orders[oid]['gross'])}.")

# ---------------- ADS ----------------
ads_raw = read("ads.csv")
ads = []
for r in ads_raw:
    ads.append({
        "row_id": r["row_id"], "ad_id": r["ad_id"].strip(),
        "date_raw": r["date"], "date": norm_date(r["date"]),
        "product": norm_product(r["product"]),
        "channel": r["channel"].strip().lower(),
        "spend": norm_money(r["spend_cad"]),
    })

def ad_key(r):
    return (r["ad_id"], r["date_raw"], r["product"], r["channel"], r["spend"])

seen = {}
deduped = []
for r in ads:
    k = ad_key(r)
    if k in seen:
        flag("ads.csv", r["row_id"], r["ad_id"], "exact_duplicate",
             "collapsed", f"Identical to {seen[k]} on all fields except row_id; kept {seen[k]}.")
    else:
        seen[k] = r["row_id"]
        deduped.append(r)

by_ad = {}
for r in deduped:
    if r["ad_id"] in by_ad:
        flag("ads.csv", r["row_id"], r["ad_id"], "conflicting_ad_id",
             "flagged_unresolved", "Duplicate ad_id with differing fields; no updated_at to resolve.")
        r["conflict"] = True
        by_ad[r["ad_id"] + "_" + r["row_id"]] = r
    else:
        by_ad[r["ad_id"]] = r

ads_clean = sorted(by_ad.values(), key=lambda r: r["row_id"])
for r in ads_clean:
    reasons = []
    if r.get("conflict"):
        reasons.append("conflicting_ad_id_unresolved")
    if r["date"] is None:
        reasons.append("unparseable_date")
    elif not in_period(r["date"]):
        reasons.append("outside_october_2026")
    if r["product"] is None:
        reasons.append("unmapped_product")
    if r["spend"] is None:
        reasons.append("missing_or_unparseable_spend")
    r["included"] = not reasons
    r["note"] = ";".join(reasons)
    for reason in reasons:
        flag("ads.csv", r["row_id"], r["ad_id"], reason, "excluded",
             f"date={r['date_raw']}, product={r['product']}, spend={money(r['spend']) or '(missing)'}.")

# ---------------- WRITE CLEANED FILES ----------------
def iso(d):
    return d.isoformat() if d else ""

with open(os.path.join(OUT, "cleaned-sales.csv"), "w", newline="", encoding="utf-8") as f:
    w = csv.writer(f)
    w.writerow(["row_id", "order_id", "date", "product", "status", "gross_cad",
                "updated_at", "included", "note"])
    for r in sales_clean:
        w.writerow([r["row_id"], r["order_id"], iso(r["date"]) or r["date_raw"],
                    r["product"] or "", r["status"], money(r["gross"]),
                    r["updated_at"], "yes" if r["included"] else "no", r["note"]])

with open(os.path.join(OUT, "cleaned-refunds.csv"), "w", newline="", encoding="utf-8") as f:
    w = csv.writer(f)
    w.writerow(["row_id", "refund_id", "order_id", "settled_date", "status",
                "amount_cad", "product", "included", "note"])
    for r in refunds_clean:
        w.writerow([r["row_id"], r["refund_id"], r["order_id"],
                    iso(r["date"]) or r["date_raw"], r["status"], money(r["amount"]),
                    r["product"], "yes" if r["included"] else "no", r["note"]])

with open(os.path.join(OUT, "cleaned-ads.csv"), "w", newline="", encoding="utf-8") as f:
    w = csv.writer(f)
    w.writerow(["row_id", "ad_id", "date", "product", "channel", "spend_cad",
                "included", "note"])
    for r in ads_clean:
        w.writerow([r["row_id"], r["ad_id"], iso(r["date"]) or r["date_raw"],
                    r["product"] or "", r["channel"], money(r["spend"]),
                    "yes" if r["included"] else "no", r["note"]])

with open(os.path.join(OUT, "exceptions.csv"), "w", newline="", encoding="utf-8") as f:
    w = csv.DictWriter(f, fieldnames=["source_file", "row_id", "record_id",
                                      "issue", "action", "detail"])
    w.writeheader()
    for e in exceptions:
        w.writerow(e)

# ---------------- TOTALS ----------------
def totals(product=None):
    s = [r for r in sales_clean if r["included"] and (product is None or r["product"] == product)]
    rf = [r for r in refunds_clean if r["included"] and (product is None or r["product"] == product)]
    ad = [r for r in ads_clean if r["included"] and (product is None or r["product"] == product)]
    n = len(s)
    gross = sum((r["gross"] for r in s), Decimal("0"))
    ref = sum((r["amount"] for r in rf), Decimal("0"))
    spend = sum((r["spend"] for r in ad), Decimal("0"))
    net = gross - ref
    contrib = net - spend
    per = (net / n).quantize(Decimal("0.01")) if n else None
    return dict(orders=n, gross=gross, refunds=ref, net=net, spend=spend,
                contrib=contrib, per_order=per)

T = {p: totals(p) for p in ("Workshop", "Course")}
T["Combined"] = totals(None)

def fmt_row(label, t):
    return (f"| {label} | {t['orders']} | {money(t['gross'])} | {money(t['refunds'])} | "
            f"{money(t['net'])} | {money(t['spend'])} | {money(t['contrib'])} | "
            f"{money(t['per_order'])} |")

# reconciliation counts
def recon(rows_raw, clean, src):
    included = sum(1 for r in clean if r["included"])
    collapsed = sum(1 for e in exceptions if e["source_file"] == src and e["issue"] == "exact_duplicate")
    superseded = sum(1 for e in exceptions if e["source_file"] == src and e["issue"] == "superseded_conflict")
    excluded = sum(1 for r in clean if not r["included"])
    assert len(rows_raw) == included + collapsed + superseded + excluded, src
    return len(rows_raw), included, collapsed, superseded, excluded

rec_sales = recon(sales_raw, sales_clean, "sales.csv")
rec_ref = recon(refunds_raw, refunds_clean, "refunds.csv")
rec_ads = recon(ads_raw, ads_clean, "ads.csv")

inc_sales_ids = ", ".join(r["row_id"] for r in sales_clean if r["included"])
inc_ref_ids = ", ".join(r["row_id"] for r in refunds_clean if r["included"])
inc_ad_ids = ", ".join(r["row_id"] for r in ads_clean if r["included"])

summary = f"""# Northline Workshops — October 2026 results (CAD)

Reporting period: 2026-10-01 to 2026-10-31 inclusive. All amounts CAD, tax excluded.
Rules applied from `inputs/policy.md` (accounting note, October 2026). Sources:
`inputs/sales.csv`, `inputs/refunds.csv`, `inputs/ads.csv`. Generated by
`reconcile.py` (standard-library Python); every figure below is computed from the
cleaned files, which preserve source row IDs.

**Status: PROVISIONAL.** Refund RF05 (row R06, order N1011) is settled in October
but its amount is missing. It is excluded from the calculable total and never
treated as zero, so net revenue, contribution and per-order figures below are
upper bounds pending that amount (`inputs/policy.md`: "Missing refund amounts
mean the resulting net revenue is provisional"). Refund RF04 (row R05) also
references order N9999, which does not exist in the sales file — flagged, not guessed.

## Results

| Metric basis | Paid orders | Gross sales | Applied refunds | Net revenue | Ad spend | Contribution after ads | Net revenue / paid order |
|---|---|---|---|---|---|---|---|
{fmt_row('Workshop', T['Workshop'])}
{fmt_row('Course', T['Course'])}
{fmt_row('Combined', T['Combined'])}

Definitions (per `inputs/policy.md`): revenue = paid order gross; applied refunds =
settled refunds with October settlement dates against valid paid October orders;
contribution after advertising = net revenue − ad spend. **This is not net profit**:
no COGS, payment fees, salaries or overhead were supplied.

Included records:
- Sales (paid, October): {inc_sales_ids}.
- Refunds (settled, October, valid paid order, amount present): {inc_ref_ids}
  (RF01 → N1003 Workshop 240.00; RF02 → N1006 Course 60.00).
- Ads (October, mapped product): {inc_ad_ids}.

Checks performed on applied refunds: RF01 240.00 ≤ N1003 paid gross 480.00;
RF02 60.00 ≤ N1006 paid gross 240.00. No refund exceeds paid gross. RF05's
ceiling cannot be checked because its amount is missing (N1011 gross is 360.00).

## Reconciliation of source rows

| File | Source rows | Included | Exact duplicates collapsed | Superseded (conflict) | Excluded after dedup |
|---|---|---|---|---|---|
| sales.csv | {rec_sales[0]} | {rec_sales[1]} | {rec_sales[2]} | {rec_sales[3]} | {rec_sales[4]} |
| refunds.csv | {rec_ref[0]} | {rec_ref[1]} | {rec_ref[2]} | {rec_ref[3]} | {rec_ref[4]} |
| ads.csv | {rec_ads[0]} | {rec_ads[1]} | {rec_ads[2]} | {rec_ads[3]} | {rec_ads[4]} |

Every source row is accounted for: included + collapsed duplicate + superseded
+ excluded = source rows (asserted in `reconcile.py`). Row-level reasons are in
`output/exceptions.csv`. Key items:

- **S04** duplicates S03 (order N1003) exactly — collapsed, counted once (480.00).
- **S09 vs S10** (order N1008): conflicting gross 120.00 vs 180.00; kept S10
  (updated_at 2026-10-16T09:00:00, latest) per policy; S09 excluded.
- **S05** pending and **S08** cancelled — not revenue per policy.
- **S11** (2026-09-30) and **S15** (2026-11-01) — outside October.
- **R03** duplicates R02 (refund RF02) exactly — collapsed, deducted once (60.00).
- **R04** pending — not deducted per policy.
- **R05** settled 120.00 but order N9999 is not a valid paid order — flagged and excluded.
- **R06** settled, amount missing — excluded from calculable total; makes results provisional.
- **R07** settled 2026-11-02 — November settlement, outside the October window.
- **A04** duplicates A03 (ad AD03) exactly — collapsed, spent counted once (100.00).
- **A07** dated 2026-11-01 — outside October.

## Normalization applied

- Dates: slash dates read as DD/MM/YYYY (S02 03/10/2026 → 2026-10-03; S07
  11/10/2026 → 2026-10-11; R02 14/10/2026 → 2026-10-14); named month S03/S04
  "Oct 05 2026" → 2026-10-05; ISO kept. All valid dates written YYYY-MM-DD.
- Money: "CAD 240.00", "$120.00", "$50.00" parsed to plain two-decimal values.
  The missing amount in R06 is preserved as empty, not zero.
- Product/channel: trimmed and case-folded (" Workshop " → Workshop, "course" →
  Course, " Search "/"SOCIAL" → search/social). No unmapped products occurred.

## Recommendation

Workshop produced {money(T['Workshop']['contrib'])} of the {money(T['Combined']['contrib'])}
combined contribution ({T['Workshop']['contrib'] / T['Combined']['contrib'] * 100:.1f}%)
on {money(T['Workshop']['spend'])} of ad spend, and its net revenue per paid order
({money(T['Workshop']['per_order'])}) is well above Course ({money(T['Course']['per_order'])}).
Notably, contribution per ad dollar is identical for both lines
(Workshop {T['Workshop']['contrib'] / T['Workshop']['spend']:.2f}, Course
{T['Course']['contrib'] / T['Course']['spend']:.2f}), so October data alone does not
justify shifting budget from Course to Workshop on efficiency grounds. Practical steps:

1. **Resolve the two refund exceptions before booking the month**: obtain RF05's
   amount (bounds Course net revenue between {money(T['Course']['net'] - paid_orders['N1011']['gross'])}
   and {money(T['Course']['net'])} if the refund cannot exceed N1011's 360.00 gross)
   and investigate RF04's orphan order N9999 — it may indicate a missing sales record.
2. **Prioritize Workshop capacity/fill rate**: it carries most contribution and the
   higher per-order value; modest spend increases there are reasonable to test, but
   with equal ad efficiency, run a small controlled test rather than a large reallocation.
3. **Do not treat {money(T['Combined']['contrib'])} as profit.** Cost of delivery,
   payment fees, salaries and overhead are absent from the inputs; true profitability
   could be materially lower, especially for delivery-heavy workshops. Gather those
   costs before pricing or budget decisions.

## Assumptions and limitations

- "Exact duplicate" = identical on all fields except row_id (policy allows collapsing).
- Refund RF05 (R06) is attributed to Course via order N1011 for the provisional-range
  note only; it contributes 0 to the calculable totals because its amount is unknown.
- Ads are attributed to the product named in each row (per policy); no allocation
  across products was needed.
- No refunds exceeded paid gross among calculable records; the missing-amount refund
  could not be ceiling-checked.
- Figures are provisional solely due to R06's missing amount; all other exclusions
  are policy-driven and final.

Estimated human finish time (not a measured result): ~20–30 minutes to obtain the
RF05 amount, investigate order N9999, and re-run `reconcile.py`.
"""

with open(os.path.join(OUT, "summary.md"), "w", encoding="utf-8") as f:
    f.write(summary)

print("Totals:")
for k, t in T.items():
    print(k, {kk: (money(vv) if isinstance(vv, Decimal) else vv) for kk, vv in t.items()})
print("Recon (raw, included, dup-collapsed, superseded, excluded):",
      rec_sales, rec_ref, rec_ads)
print("Provisional:", provisional, "| exceptions:", len(exceptions))
