"""
Afton UF Deep Dive — Possibility Analysis
=========================================
For the 179 "no payment method" UF entries, investigates the most likely
explanation: corresponding bank deposits exist but were recorded as direct
cash entries (Dr Bank, Cr Income) instead of LinkedTxn deposits pulling
from UF.

Steps:
  1. Inspect Line[] structure of recent RBFCU Deposit transactions to see
     if they use LinkedTxn or direct DepositLineDetail
  2. For each UF entry, look for an existing RBFCU Deposit on same date
     (±3 days) that contains a matching amount as a direct-cash line
  3. Cross-reference with bank CSV for true bank-side amounts

Output:
  afton_uf_deep_dive_report.md
  afton_uf_deep_dive.json
"""
from __future__ import annotations

import csv
import json
import sys
from collections import Counter, defaultdict
from datetime import datetime, timedelta
from pathlib import Path

if hasattr(sys.stdout, "reconfigure"):
    sys.stdout.reconfigure(encoding="utf-8", errors="replace")

SCRIPT_DIR = Path(__file__).resolve().parent
QBO_APP_DIR = SCRIPT_DIR.parent.parent / "Atlas" / "app"
sys.path.insert(0, str(QBO_APP_DIR))
import qbo_client as qbo  # type: ignore

CLIENT = "afton"
RBFCU_ACCT_ID = "61"
UF_ACCT_ID = "4"
UF_JSON = SCRIPT_DIR / "afton_uf_cleanup_worksheet.json"
FORENSICS_JSON = SCRIPT_DIR / "afton_uf_forensics.json"
BANK_CSV = SCRIPT_DIR / "20260513-8529015.CSV"
OUT_MD = SCRIPT_DIR / "afton_uf_deep_dive_report.md"
OUT_JSON = SCRIPT_DIR / "afton_uf_deep_dive.json"


def page_query(token: str, sql_base: str, entity: str) -> list[dict]:
    out: list[dict] = []
    start = 1
    page = 1000
    while True:
        sql = f"{sql_base} STARTPOSITION {start} MAXRESULTS {page}"
        resp = qbo.query(CLIENT, token, sql)
        rows = resp.get(entity, [])
        if not rows:
            break
        out.extend(rows)
        if len(rows) < page:
            break
        start += page
    return out


def analyze_deposit_lines(deposit: dict) -> dict:
    """For a Deposit transaction, categorize lines as LinkedTxn vs direct cash."""
    linked = 0
    direct = 0
    linked_amt = 0.0
    direct_amt = 0.0
    direct_accts: Counter = Counter()
    for line in deposit.get("Line", []):
        if line.get("DetailType") != "DepositLineDetail":
            continue
        amt = line.get("Amount", 0) or 0
        if line.get("LinkedTxn"):
            linked += 1
            linked_amt += amt
        else:
            direct += 1
            direct_amt += amt
            detail = line.get("DepositLineDetail", {}) or {}
            acct = detail.get("AccountRef", {}) or {}
            direct_accts[f"{acct.get('value')} - {acct.get('name')}"] += 1
    return {
        "n_linked": linked,
        "n_direct": direct,
        "linked_amt": linked_amt,
        "direct_amt": direct_amt,
        "total": linked_amt + direct_amt,
        "direct_accts": dict(direct_accts),
    }


def main() -> None:
    print("Loading data...")
    uf_data = json.load(open(UF_JSON, encoding="utf-8"))
    forensics = json.load(open(FORENSICS_JSON, encoding="utf-8"))

    # Index forensic rows by id for fast lookup
    forensic_by_id = {r["id"]: r for r in forensics["rows"]}

    # Filter to "no payment method" subset
    no_method_entries = [
        r for r in forensics["rows"] if not r.get("payment_method_id")
    ]
    print(f"  {len(no_method_entries)} UF entries with no payment method")

    token = qbo.refresh_access_token(CLIENT)

    # ── Pull all RBFCU deposits ───────────────────────────────────────────
    print("Pulling all RBFCU Deposits...")
    all_deposits = page_query(token, "SELECT * FROM Deposit", "Deposit")
    rbfcu_deposits = [
        d for d in all_deposits
        if (d.get("DepositToAccountRef") or {}).get("value") == RBFCU_ACCT_ID
    ]
    print(f"  {len(rbfcu_deposits)} RBFCU deposits")

    # ── Aggregate Deposit line structure ──────────────────────────────────
    structure_counts: Counter = Counter()
    total_linked = 0
    total_direct = 0
    direct_acct_totals: Counter = Counter()
    direct_acct_amounts: defaultdict = defaultdict(float)

    for d in rbfcu_deposits:
        a = analyze_deposit_lines(d)
        if a["n_linked"] > 0 and a["n_direct"] == 0:
            structure_counts["pure_linked"] += 1
        elif a["n_direct"] > 0 and a["n_linked"] == 0:
            structure_counts["pure_direct"] += 1
        elif a["n_linked"] > 0 and a["n_direct"] > 0:
            structure_counts["mixed"] += 1
        else:
            structure_counts["empty"] += 1
        total_linked += a["n_linked"]
        total_direct += a["n_direct"]
        for k, v in a["direct_accts"].items():
            direct_acct_totals[k] += v

    # Also compute money-flow by structure type
    money_by_struct = defaultdict(float)
    for d in rbfcu_deposits:
        a = analyze_deposit_lines(d)
        if a["n_linked"] > 0 and a["n_direct"] == 0:
            money_by_struct["pure_linked"] += a["total"]
        elif a["n_direct"] > 0 and a["n_linked"] == 0:
            money_by_struct["pure_direct"] += a["total"]
        elif a["n_linked"] > 0 and a["n_direct"] > 0:
            money_by_struct["mixed_linked"] += a["linked_amt"]
            money_by_struct["mixed_direct"] += a["direct_amt"]

    # ── For each no-method UF entry, find candidate Deposit on same date ──
    deposits_by_date: dict = defaultdict(list)
    for d in rbfcu_deposits:
        if d.get("TxnDate"):
            deposits_by_date[d["TxnDate"]].append(d)

    candidate_matches = []
    no_candidate = []
    for r in no_method_entries:
        if not r.get("txn_date") or not r.get("amount"):
            continue
        td = datetime.fromisoformat(r["txn_date"]).date()
        target_amt = r["amount"]
        found = None
        for delta in range(-7, 8):  # ±7 days
            check_date = (td + timedelta(days=delta)).isoformat()
            for dep in deposits_by_date.get(check_date, []):
                # Check if any line in dep has amount matching target_amt (within $0.01)
                for line in dep.get("Line", []):
                    if line.get("DetailType") != "DepositLineDetail":
                        continue
                    line_amt = line.get("Amount", 0) or 0
                    if abs(line_amt - target_amt) < 0.01:
                        found = {
                            "deposit_id": dep["Id"],
                            "deposit_date": dep["TxnDate"],
                            "deposit_total": dep.get("TotalAmt"),
                            "line_amt": line_amt,
                            "line_is_linked": bool(line.get("LinkedTxn")),
                            "line_acct": (line.get("DepositLineDetail", {}) or {}).get("AccountRef"),
                            "day_delta": delta,
                        }
                        break
                if found:
                    break
            if found:
                break
        if found:
            candidate_matches.append({
                "uf_entry": r,
                "candidate": found,
            })
        else:
            no_candidate.append(r)

    # ── Output ─────────────────────────────────────────────────────────────
    print(f"\n=== HEADLINES ===")
    print(f"\nRBFCU Deposit structure (n={len(rbfcu_deposits)}):")
    for k, v in structure_counts.most_common():
        print(f"  {k:20s} {v:5d}  ${money_by_struct.get(k, money_by_struct.get(k.replace('pure_', ''), 0)):>12,.2f}")
    print(f"\nTotal lines:  {total_linked} LinkedTxn + {total_direct} direct = {total_linked + total_direct}")
    print(f"\nTop direct-line accounts:")
    for k, v in direct_acct_totals.most_common(15):
        print(f"  {v:5d} lines  {k}")

    print(f"\nNo-method UF entry candidate matches:")
    print(f"  Found candidate match: {len(candidate_matches)} entries")
    print(f"  No candidate found:    {len(no_candidate)} entries")
    if candidate_matches:
        linked_matches = [m for m in candidate_matches if m["candidate"]["line_is_linked"]]
        direct_matches = [m for m in candidate_matches if not m["candidate"]["line_is_linked"]]
        print(f"  Candidate IS LinkedTxn (already linked to a payment): {len(linked_matches)}")
        print(f"  Candidate IS direct-cash (THE DUPLICATE-DEPOSIT THEORY): {len(direct_matches)}")

    # Save findings
    OUT_JSON.write_text(json.dumps({
        "generated_at": datetime.utcnow().isoformat() + "Z",
        "n_no_method_entries": len(no_method_entries),
        "rbfcu_deposit_structure": dict(structure_counts),
        "money_by_structure": dict(money_by_struct),
        "direct_acct_totals": dict(direct_acct_totals),
        "candidate_matches": [
            {"uf": m["uf_entry"], "candidate": m["candidate"]}
            for m in candidate_matches
        ],
        "no_candidate_count": len(no_candidate),
    }, indent=2, default=str), encoding="utf-8")

    # Markdown
    lines = [
        "# Afton UF Deep Dive — No-Method Investigation",
        f"_Generated: {datetime.utcnow().isoformat()}Z_",
        "",
        f"Investigating: {len(no_method_entries)} UF entries with no PaymentMethod specified, "
        f"totaling ${sum(r.get('amount', 0) or 0 for r in no_method_entries):,.2f}",
        "",
        "## RBFCU Deposit transaction structure",
        "",
        "How do existing RBFCU Deposits flow money? Either via LinkedTxn pulling from UF "
        "(proper grouping) or via direct cash entries (Dr Bank Cr Income, bypasses UF).",
        "",
        f"- **Total RBFCU Deposits: {len(rbfcu_deposits)}**",
        "",
        "| Structure | # Deposits | Description |",
        "|---|---|---|",
    ]
    for k in ("pure_linked", "pure_direct", "mixed", "empty"):
        c = structure_counts.get(k, 0)
        descr = {
            "pure_linked": "Lines all use LinkedTxn pulling from UF (CORRECT pattern)",
            "pure_direct": "Lines are all direct cash entries (BYPASSES UF — explains buildup)",
            "mixed": "Some LinkedTxn + some direct lines",
            "empty": "Empty Deposit",
        }[k]
        lines.append(f"| {k} | {c} | {descr} |")
    lines.append("")
    lines.append(f"**Line-level totals:** {total_linked} LinkedTxn lines + {total_direct} direct lines")
    lines.append("")

    lines.append("## Top accounts hit by direct-cash deposit lines")
    lines.append("")
    lines.append("If these are income accts, deposits are bypassing UF and crediting income directly.")
    lines.append("")
    lines.append("| Account | # Lines |")
    lines.append("|---|---|")
    for k, v in direct_acct_totals.most_common(20):
        lines.append(f"| {k} | {v} |")
    lines.append("")

    lines.append("## No-method UF entry → candidate Deposit match")
    lines.append("")
    lines.append(f"For each no-method UF entry, searched RBFCU Deposits ±7 days for any line "
                 f"matching the same amount exactly.")
    lines.append("")
    lines.append(f"- **Candidate found** (matching amount in a nearby deposit): {len(candidate_matches)}")
    lines.append(f"- **No candidate** (no nearby deposit has this amount): {len(no_candidate)}")
    lines.append("")
    if candidate_matches:
        linked_matches = [m for m in candidate_matches if m["candidate"]["line_is_linked"]]
        direct_matches = [m for m in candidate_matches if not m["candidate"]["line_is_linked"]]
        lines.append(f"Of the {len(candidate_matches)} found candidates:")
        lines.append(f"- **{len(linked_matches)} candidate is LinkedTxn** — already properly linked to a payment (probably matches a different UF entry)")
        lines.append(f"- **{len(direct_matches)} candidate is direct-cash** — STRONG SIGNAL: this UF entry's deposit was recorded as a direct cash line, bypassing the proper UF link. **These are repair candidates.**")
        lines.append("")
        if direct_matches:
            lines.append("### Sample of direct-cash matches (the repair candidates)")
            lines.append("")
            lines.append("| UF Date | Customer | UF $ | Deposit Date | Deposit ID | Line Acct |")
            lines.append("|---|---|---|---|---|---|")
            for m in direct_matches[:20]:
                uf = m["uf_entry"]
                c = m["candidate"]
                acct = c.get("line_acct", {}) or {}
                lines.append(
                    f"| {uf['txn_date']} | {uf['customer']} | ${uf['amount']:,.2f} | "
                    f"{c['deposit_date']} | {c['deposit_id']} | "
                    f"{acct.get('value','?')} {acct.get('name','')} |"
                )
    OUT_MD.write_text("\n".join(lines), encoding="utf-8")
    print(f"\nWrote {OUT_MD}")


if __name__ == "__main__":
    main()