#!/usr/bin/env python3
"""Normalise the bundled synthetic export. Python 3 standard library only.
No network calls. The selected headings and stage map are teaching assumptions.
"""
import csv
from datetime import date
from decimal import Decimal, InvalidOperation
from pathlib import Path

ROOT = Path(__file__).resolve().parent
SNAPSHOT = "2026-10-14T23:59:59Z"
FIELDS = ["deal_id", "deal_name", "stage", "deal_status", "amount", "currency", "closing_date", "owner_id", "snapshot_at"]

def normalise(rows, stage_map):
    output, seen = [], set()
    for row in rows:
        deal_id = row["Deal Id"].strip()
        if not deal_id or deal_id in seen:
            raise ValueError("Deal Id must be present and unique in this snapshot")
        seen.add(deal_id)
        stage = row["Stage"].strip()
        status = stage_map.get(stage)
        if status not in {"open", "won", "lost"}:
            raise ValueError("Map every included stage to an agreed reporting status")
        if row["Currency"].strip() != "GBP":
            raise ValueError("This teaching report supports GBP only; agree conversion before combining currencies")
        try:
            amount = Decimal(row["Amount"])
        except InvalidOperation as exc:
            raise ValueError("Amount must be a known numeric value") from exc
        if not amount.is_finite() or amount < 0 or amount != amount.quantize(Decimal("0.01")):
            raise ValueError("Amount must be a non-negative amount with at most two decimals")
        closing = row["Closing Date"].strip()
        if closing:
            date.fromisoformat(closing)
        output.append(dict(zip(FIELDS, [deal_id, row["Deal Name"], stage, status, format(amount, ".2f"), "GBP", closing, row["Owner Id"], SNAPSHOT])))
    return sorted(output, key=lambda row: row["deal_id"])

def main():
    with (ROOT / "stage-map.csv").open(newline="", encoding="utf-8") as source:
        stage_map = {row["stage"]: row["deal_status"] for row in csv.DictReader(source)}
    with (ROOT / "zoho-crm-export.csv").open(newline="", encoding="utf-8") as source:
        rows = normalise(list(csv.DictReader(source)), stage_map)
    with (ROOT / "looker-studio-deals.csv").open("w", newline="", encoding="utf-8") as target:
        writer = csv.DictWriter(target, fieldnames=FIELDS, lineterminator="\n")
        writer.writeheader()
        writer.writerows(rows)
    print(f"Wrote {len(rows)} synthetic snapshot rows to looker-studio-deals.csv")

if __name__ == "__main__":
    main()