"""
Afton Focused TB Map
=====================
Scan each non-zero TB balance, identify source-of-truth, and flag where books
may be off. Outputs:
  - afton_focused_map.json  (structured findings)
  - afton_focused_map.md    (human-readable punch list)

Per account, pulls:
  - Current balance + last update
  - Transaction count + activity span over last 12 months
  - For each account TYPE, what the source-of-truth is and whether we can verify

For Bank/CC: detect Feb-Apr 2026 recording gaps (the pattern from RBFCU 9015)
"""
from __future__ import annotations

import json
import sys
from collections import defaultdict
from datetime import datetime, date
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
import requests

CLIENT = "afton"
OUT_JSON = SCRIPT_DIR / "afton_focused_map.json"
OUT_MD = SCRIPT_DIR / "afton_focused_map.md"

# The bookkeeping pause window from the bank rec finding
GAP_START = date(2026, 2, 7)
GAP_END = date(2026, 4, 17)


def gl_activity(token: str, acct_id: str, start: str, end: str) -> dict:
    """Pull GL summary for an account: txn count, monthly distribution, gap-window check."""
    cfg = qbo.get_client(CLIENT)
    realm = cfg["realm_id"]
    url = f"{qbo.api_base()}/v3/company/{realm}/reports/GeneralLedger"
    resp = requests.get(url, params={
        "account": acct_id,
        "start_date": start,
        "end_date": end,
        "minorversion": "73",
        "columns": "tx_date,txn_type,subt_nat_amount",
    }, headers={"Authorization": f"Bearer {token}", "Accept": "application/json"}, timeout=60)
    if resp.status_code != 200:
        return {"error": resp.text[:200]}
    data = resp.json()

    txns = []

    def walk(rows):
        for r in rows or []:
            if "Rows" in r:
                walk(r["Rows"].get("Row", []))
            else:
                cd = r.get("ColData", [])
                if len(cd) < 3:
                    continue
                tt = cd[1].get("value", "")
                if not tt or tt == "Beginning Balance" or "Total" in tt:
                    continue
                try:
                    d = datetime.strptime(cd[0].get("value", ""), "%Y-%m-%d").date()
                    amt = float(cd[2].get("value", "0").replace(",", ""))
                except ValueError:
                    continue
                txns.append({"date": d, "txn_type": tt, "amount": amt})

    walk(data.get("Rows", {}).get("Row", []))

    if not txns:
        return {"txn_count": 0, "first": None, "last": None, "gap_window_count": 0}

    by_month = defaultdict(lambda: {"count": 0, "total": 0.0})
    for t in txns:
        m = t["date"].strftime("%Y-%m")
        by_month[m]["count"] += 1
        by_month[m]["total"] += t["amount"]

    gap_window_count = sum(1 for t in txns if GAP_START <= t["date"] <= GAP_END)

    return {
        "txn_count": len(txns),
        "first": min(t["date"] for t in txns).isoformat(),
        "last": max(t["date"] for t in txns).isoformat(),
        "gap_window_count": gap_window_count,
        "by_month": dict(by_month),
    }


def pull_aging(token: str, kind: str) -> dict:
    """Pull AR or AP aging summary."""
    cfg = qbo.get_client(CLIENT)
    realm = cfg["realm_id"]
    report = "AgedReceivables" if kind == "AR" else "AgedPayables"
    url = f"{qbo.api_base()}/v3/company/{realm}/reports/{report}"
    resp = requests.get(url, params={"minorversion": "73"}, headers={"Authorization": f"Bearer {token}", "Accept": "application/json"}, timeout=60)
    if resp.status_code != 200:
        return {"error": resp.text[:200]}
    data = resp.json()
    return data


def main() -> None:
    token = qbo.refresh_access_token(CLIENT)

    # Pull TB YTD
    cfg = qbo.get_client(CLIENT)
    realm = cfg["realm_id"]
    url = f"{qbo.api_base()}/v3/company/{realm}/reports/TrialBalance"
    resp = requests.get(url, params={
        "start_date": "2026-01-01",
        "end_date": "2026-05-24",
        "accounting_method": "Accrual",
        "minorversion": "73",
    }, headers={"Authorization": f"Bearer {token}", "Accept": "application/json"}, timeout=60)
    tb = resp.json()

    # Extract non-zero balances from TB
    balances = []
    for row in tb.get("Rows", {}).get("Row", []):
        if "Summary" in row:
            continue
        cd = row.get("ColData", [])
        if len(cd) < 3:
            continue
        name = cd[0].get("value", "")
        acct_id = cd[0].get("id", "")
        dr = cd[1].get("value", "")
        cr = cd[2].get("value", "")
        try:
            dr_v = float(dr) if dr else 0
        except ValueError:
            dr_v = 0
        try:
            cr_v = float(cr) if cr else 0
        except ValueError:
            cr_v = 0
        if dr_v == 0 and cr_v == 0:
            continue
        balances.append({
            "id": acct_id,
            "name": name,
            "dr": dr_v,
            "cr": cr_v,
            "net": dr_v - cr_v,
        })

    print(f"Found {len(balances)} non-zero TB accounts")

    # Pull account detail (type) for each
    accts_resp = qbo.query(CLIENT, token, "SELECT * FROM Account MAXRESULTS 1000")
    accts = {str(a["Id"]): a for a in accts_resp.get("Account", [])}

    # Categorize and analyze
    findings = []
    for b in balances:
        a = accts.get(b["id"], {})
        b["account_type"] = a.get("AccountType", "?")
        b["account_subtype"] = a.get("AccountSubType", "?")
        b["active"] = a.get("Active", True)
        b["last_updated"] = a.get("MetaData", {}).get("LastUpdatedTime", "")[:10]

        # For Bank, Credit Card: pull GL activity to look for gaps
        category = "other"
        flags = []
        sot = "unknown"  # source of truth
        if b["account_type"] == "Bank":
            category = "bank"
            sot = "bank statement"
            activity = gl_activity(token, b["id"], "2025-12-01", "2026-05-24")
            b["activity"] = activity
            if activity.get("gap_window_count", -1) == 0 and activity.get("txn_count", 0) > 0:
                flags.append(f"NO ACTIVITY in Feb-Apr 2026 gap window — possible recording pause")
            if activity.get("txn_count", 0) == 0:
                flags.append("NO activity in last 6 months — may be dormant")
            print(f"  bank {b['id']:>4s} {b['name']:<35s}  net=${b['net']:>12,.2f}  txns={activity.get('txn_count','?')}  gap_txns={activity.get('gap_window_count','?')}")
        elif b["account_type"] == "Credit Card":
            category = "credit_card"
            sot = "credit card statement"
            activity = gl_activity(token, b["id"], "2025-12-01", "2026-05-24")
            b["activity"] = activity
            if activity.get("gap_window_count", -1) == 0 and activity.get("txn_count", 0) > 0:
                flags.append(f"NO ACTIVITY in Feb-Apr 2026 gap window — possible recording pause")
            if activity.get("txn_count", 0) == 0:
                flags.append("NO activity in last 6 months — may be dormant")
            print(f"  CC   {b['id']:>4s} {b['name']:<35s}  net=${b['net']:>12,.2f}  txns={activity.get('txn_count','?')}  gap_txns={activity.get('gap_window_count','?')}")
        elif b["account_type"] in ("Accounts Receivable", "Other Current Asset"):
            category = "AR" if b["account_type"] == "Accounts Receivable" else "other_asset"
            sot = "AR aging detail" if category == "AR" else "varies"
        elif b["account_type"] == "Accounts Payable":
            category = "AP"
            sot = "AP aging detail"
        elif b["account_type"] == "Other Current Liability":
            category = "current_liability"
            sot = "subledger / liability detail"
        elif b["account_type"] == "Long Term Liability":
            category = "loan"
            sot = "loan amortization schedule"
        elif b["account_type"] == "Equity":
            category = "equity"
            sot = "historical / cumulative"
        elif b["account_type"] in ("Income", "Other Income"):
            category = "income"
            sot = "year-to-date P&L activity"
        elif b["account_type"] in ("Expense", "Cost of Goods Sold", "Other Expense"):
            category = "expense"
            sot = "year-to-date P&L activity"
        elif b["account_type"] == "Fixed Asset":
            category = "fixed_asset"
            sot = "fixed asset register"

        b["category"] = category
        b["source_of_truth"] = sot
        b["flags"] = flags
        findings.append(b)

    # Output
    OUT_JSON.write_text(json.dumps({
        "generated_at": datetime.utcnow().isoformat() + "Z",
        "gap_window": [GAP_START.isoformat(), GAP_END.isoformat()],
        "findings": findings,
    }, indent=2, default=str), encoding="utf-8")
    print(f"\nWrote {OUT_JSON}")

    # Markdown — grouped by category, ordered by $ impact
    lines = [
        "# Afton Focused TB Map",
        f"_Generated: {datetime.utcnow().isoformat()}Z_",
        f"_TB period: 2026-01-01 to 2026-05-24_",
        f"_Bookkeeping gap window: {GAP_START} to {GAP_END}_",
        "",
        f"**{len(findings)} non-zero accounts on TB**",
        "",
        "## Summary by category",
        "",
        "| Category | Count | $ Net |",
        "|---|---|---|",
    ]
    by_cat = defaultdict(lambda: {"count": 0, "net": 0.0})
    for f in findings:
        by_cat[f["category"]]["count"] += 1
        by_cat[f["category"]]["net"] += abs(f["net"])
    for cat, v in sorted(by_cat.items(), key=lambda x: -x[1]["net"]):
        lines.append(f"| {cat} | {v['count']} | ${v['net']:,.2f} |")
    lines.append("")

    for cat in ["bank", "credit_card", "AR", "AP", "current_liability", "loan", "equity", "other_asset", "fixed_asset", "income", "expense", "other"]:
        items = [f for f in findings if f["category"] == cat]
        if not items:
            continue
        lines.append(f"## {cat.upper()} ({len(items)} accounts)")
        lines.append("")
        for f in sorted(items, key=lambda x: -abs(x["net"])):
            balance_str = f"${f['dr']:,.2f} Dr" if f['dr'] else f"${f['cr']:,.2f} Cr"
            lines.append(f"### {f['id']} — {f['name']} ({balance_str})")
            lines.append(f"- Type: {f['account_type']} / {f['account_subtype']}")
            lines.append(f"- Source-of-truth: {f['source_of_truth']}")
            if f.get("activity"):
                act = f["activity"]
                lines.append(f"- Activity Dec 2025–May 2026: {act.get('txn_count', '?')} txns ({act.get('first', '?')} to {act.get('last', '?')}); {act.get('gap_window_count', '?')} txns in gap window")
            if f["flags"]:
                for flag in f["flags"]:
                    lines.append(f"- ⚠️ **{flag}**")
            lines.append("")

    OUT_MD.write_text("\n".join(lines), encoding="utf-8")
    print(f"Wrote {OUT_MD}")


if __name__ == "__main__":
    main()