"""The settlement gate — proves the imported books + settlement engine
reproduce the sheet's Monthly Split (ADR #017).

Three layers of proof, strongest first:

1. **Independent oracle, every month since 2023-10:** recomputes each
   month straight from the LIVE sheet CSVs (tab-Invoices/tab-Expenses —
   an implementation independent of the importer) and compares the DB
   engine's output to the cent. 2023-10 is the live sheet's own
   inception month (its "SINCE" block); earlier months involve the 2022
   archive whose rows the importer dedupes, so they are covered by
   reconciliation + the anchor layer instead.
2. **Sheet-displayed anchors:** the Monthly Split tab's visible numbers
   (captured 2026-07-10) for 2026-05 / 2026-04 / 2025-02.
3. **Ground truth:** the three real BW→PlusROI Wave invoices
   (2026035/2026050/2026059) vs the engine's suggested-invoice lines,
   with every delta CLASSIFIED (exact / rounding / ledger-drift /
   ad-hoc-line) — never hidden.

Run host-side from /srv/apps/with:  .venv/bin/python -m scripts.settlement_gate
Exit 0 = layer-1 green (the hard gate). Layers 2-3 report classified.
"""

from __future__ import annotations

import csv
import sys
from collections import defaultdict
from datetime import datetime
from decimal import ROUND_HALF_UP, Decimal
from pathlib import Path
from types import SimpleNamespace

from sqlalchemy import create_engine, text
from sqlalchemy.orm import Session

ROOT = Path(__file__).resolve().parents[1]
SRC = ROOT / "books-import" / "source"
CENT = Decimal("0.01")
ORACLE_FROM = "2023-10"  # live sheet's inception — see module docstring

# Layer-2 anchors: the Monthly Split tab as displayed 2026-07-10.
SHEET_ANCHORS = {
    "2026-05": {"revenue": "55812.80", "expenses": "23098.45", "split": "15519.98"},
    "2026-04": {"revenue": "50907.15", "expenses": "22286.97", "split": "13546.48"},
    "2025-02": {"revenue": "47940.17", "expenses": "30810.86", "split": "7845.55"},
}

# Layer-3 ground truth: the real Wave invoices (settlement month -> lines).
WAVE_INVOICES = {
    "2026-05": {  # Invoice 2026059, 2026-06-23
        "PlusROI Net Profit Split": "15569.42",
        "BowdenWorks Team Cost": "1875.18",
        "PPC Spend": "2551.14",
        "Licences / Fees": "204.16",
    },
    "2026-04": {  # Invoice 2026050, 2026-05-26 (incl. an itemized extra
        "PlusROI Net Profit Split": "13546.48",  # Team Cost line: 2500.00)
        "BowdenWorks Team Cost": "5091.09",  # 2591.09 + 2500.00 Prompt Victoria
        "PPC Spend": "3235.23",
        "Licences / Fees": "202.42",
    },
    "2026-03": {  # Invoice 2026035, 2026-04-21 (incl. ad-hoc AISV 8000.00)
        "PlusROI Net Profit Split": "24634.68",
        "BowdenWorks Team Cost": "11813.39",  # 3813.39 + 8000.00 ad-hoc
        "PPC Spend": "1326.69",
        "Licences / Fees": "205.16",
    },
}


def dec(raw) -> Decimal | None:
    s = str(raw or "").strip().replace("$", "").replace(",", "")
    if not s:
        return None
    neg = s.startswith("(") and s.endswith(")")
    if neg:
        s = s[1:-1]
    try:
        value = Decimal(s)
    except Exception:
        return None
    return -value if neg else value


def month_key(raw) -> str | None:
    s = str(raw or "").strip()
    for fmt in ("%Y-%m-%d %H:%M:%S", "%Y-%m-%d", "%m/%d/%Y"):
        try:
            return datetime.strptime(s[:19], fmt).strftime("%Y-%m")
        except ValueError:
            continue
    return None


def r2(value: Decimal) -> Decimal:
    return value.quantize(CENT, rounding=ROUND_HALF_UP)


def oracle_months() -> dict[str, dict[str, Decimal]]:
    """Recompute every month from the LIVE CSVs, applying the importer's
    per-row 2dp rounding (money is 2dp in the DB) but NOTHING else of the
    importer's logic."""
    months: dict[str, dict[str, Decimal]] = defaultdict(
        lambda: {"gross": Decimal(0), "fees": Decimal(0), "commissions": Decimal(0),
                 "expenses": Decimal(0)}
    )
    with open(SRC / "tab-Invoices.csv", encoding="utf-8") as fh:
        rows = list(csv.reader(fh))
    col = {name: i for i, name in enumerate(rows[0])}
    for row in rows[1:]:
        if len(row) < len(rows[0]):
            row = row + [""] * (len(rows[0]) - len(row))
        month = month_key(row[col["BW Invoice"]])
        if not month:
            continue
        bucket = months[month]
        for key, source in (("gross", "Invoice Amount"), ("fees", "Fee"),
                            ("commissions", "Commission")):
            value = dec(row[col[source]])
            if value is not None:
                bucket[key] += r2(value)
    with open(SRC / "tab-Expenses.csv", encoding="utf-8") as fh:
        rows = list(csv.reader(fh))
    col = {name: i for i, name in enumerate(rows[0])}
    for row in rows[1:]:
        if len(row) < len(rows[0]):
            row = row + [""] * (len(rows[0]) - len(row))
        month = month_key(row[col["BW Invoice"]])
        if not month:
            continue
        value = dec(row[col["Amount"]])
        if value is not None:
            months[month]["expenses"] += r2(value)
    return months


def settle(bucket: dict[str, Decimal]) -> dict[str, Decimal]:
    revenue = bucket["gross"] - bucket["fees"] - bucket["commissions"]
    bookkeeping_full = revenue * Decimal("0.03")
    net_full = revenue - bucket["expenses"] - bookkeeping_full
    return {
        "revenue": r2(revenue),
        "expenses": r2(bucket["expenses"]),
        "bookkeeping": r2(bookkeeping_full),
        "net": r2(net_full),
        "split": r2(net_full * Decimal("0.5")),
    }


def main() -> int:
    import os

    from app.config import settings

    url = (
        os.environ.get("WITH_DB_URL")
        or settings.with_db_url_host
        or settings.with_db_url
    )
    engine = create_engine(url)
    failures = 0

    with Session(engine) as session:
        from app.models import Tenant
        from app.services.money import settlement as engine_svc

        tenant_id = session.execute(
            text(
                "SELECT t.id FROM tenants t JOIN parties p ON p.id = t.party_id"
                " WHERE lower(p.name) = 'plusroi'"
            )
        ).scalar_one()
        actor = SimpleNamespace(
            tenant_id=tenant_id, capabilities={"can_see_income": True}
        )
        summary = engine_svc.settlement_summary(session, actor)
        db_months = {m["month"]: m for m in summary["months"]}

        # ---- Layer 1: independent oracle, every month since 2023-10 ----
        oracle = {
            month: settle(bucket)
            for month, bucket in oracle_months().items()
            if month >= ORACLE_FROM
        }
        print(f"LAYER 1 — independent CSV oracle vs DB engine "
              f"({len(oracle)} months >= {ORACLE_FROM})")
        for month in sorted(oracle, reverse=True):
            expected = oracle[month]
            got = db_months.get(month)
            if got is None:
                print(f"  {month}  MISSING from DB engine")
                failures += 1
                continue
            deltas = {
                key: Decimal(got[key]) - expected[key]
                for key in ("revenue", "expenses", "bookkeeping", "net", "split")
                if Decimal(got[key]) != expected[key]
            }
            if deltas:
                print(f"  {month}  FAIL {deltas}")
                failures += 1
        if failures == 0:
            print(f"  ALL {len(oracle)} months match to the cent ✓")

        # ---- Layer 2: sheet-displayed anchors ----
        print("\nLAYER 2 — sheet-displayed anchors (classified)")
        for month, anchor in SHEET_ANCHORS.items():
            got = db_months.get(month)
            if got is None:
                print(f"  {month}  missing from DB")
                continue
            for key, expected_s in anchor.items():
                delta = Decimal(got[key]) - Decimal(expected_s)
                if delta == 0:
                    verdict = "exact"
                elif abs(delta) <= Decimal("0.02"):
                    verdict = f"rounding artifact ({delta:+})"
                else:
                    verdict = f"DELTA {delta:+} (investigate)"
                print(f"  {month} {key:12} db={got[key]:>12} sheet={expected_s:>12}  {verdict}")

        # ---- Layer 3: the real Wave invoices ----
        print("\nLAYER 3 — real Wave invoices vs suggested lines (classified)")
        for month, lines in WAVE_INVOICES.items():
            detail = engine_svc.settlement_detail(session, actor, month)
            suggested = {ln["label"]: Decimal(ln["amount"])
                         for ln in detail["suggested_invoice"]["lines"]}
            print(f"  {month}:")
            for label, invoiced_s in lines.items():
                invoiced = Decimal(invoiced_s)
                got = suggested.get(label)
                if got is None:
                    print(f"    {label:28} invoiced={invoiced:>10}  engine=—"
                          f"  (no {label} computed)")
                    continue
                delta = got - invoiced
                if delta == 0:
                    verdict = "exact ✓"
                elif abs(delta) <= Decimal("0.02"):
                    verdict = f"rounding ({delta:+})"
                elif month == "2026-03" and label == "BowdenWorks Team Cost":
                    verdict = f"ad-hoc line on invoice (AISV 8000.00; delta {delta:+})"
                elif month == "2026-04" and label == "BowdenWorks Team Cost":
                    verdict = (f"itemized extra on invoice"
                               f" (Prompt Victoria 2500.00; delta {delta:+})")
                elif month == "2026-05" and label == "PlusROI Net Profit Split":
                    verdict = f"post-invoice ledger drift (known $49.44 class; delta {delta:+})"
                elif month == "2026-03" and label == "PPC Spend":
                    # Verified 2026-07-11: the ledger holds exactly 3 BW-paid
                    # Google rows = 1323.69; the extra $3 was invoice-side.
                    verdict = f"invoice-side adjustment (ledger verified; delta {delta:+})"
                else:
                    verdict = f"DELTA {delta:+} (investigate)"
                print(f"    {label:28} invoiced={invoiced:>10} engine={got:>10}  {verdict}")

        _ = Tenant  # imported for model registration side effects

    verdict = ("GREEN — layer 1 fully to the cent" if failures == 0
               else f"RED — {failures} month(s) failed")
    print(f"\nGATE: {verdict}")
    return 0 if failures == 0 else 1


if __name__ == "__main__":
    sys.exit(main())