"""
Afton RBFCU 9015 (acct 61) Bank Reconciliation
================================================
Compares the bank CSV (12/01/25 - 5/11/26) against the book GL transactions
on acct 61 for the same period to identify:
  - Inflows in real bank with NO book match → missing funding sources
  - Outflows in real bank with NO book match → unrecorded real expenses
  - Book entries with NO real bank match → phantom entries

This is the root-cause investigation for the $148K book-vs-real-bank gap
(real $11,099.84 on 5/22 vs book -$136,861.98).
"""
from __future__ import annotations

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

CLIENT = "afton"
ACCT_61 = "61"
BANK_CSV = SCRIPT_DIR / "20260513-8529015.CSV"
START = "2025-12-01"
END = "2026-05-11"

OUT_MD = SCRIPT_DIR / "afton_acct61_bank_rec_report.md"
OUT_JSON = SCRIPT_DIR / "afton_acct61_bank_rec.json"


def parse_bank_csv(path: Path) -> list[dict]:
    txns = []
    with open(path, encoding="utf-8") as f:
        for row in csv.DictReader(f):
            amt = float(row["Amount"])
            d = datetime.strptime(row["Post Date"], "%m/%d/%Y").date()
            txns.append({
                "date": d,
                "amount": amt,
                "payee": row.get("Payee", ""),
                "memo": row.get("Memo", ""),
                "check_num": row.get("Check Number", ""),
            })
    return txns


def pull_book_gl(token: str) -> list[dict]:
    """Pull all GL transactions on acct 61 via GeneralLedger report."""
    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_61,
        "start_date": START,
        "end_date": END,
        "minorversion": "73",
        "columns": "tx_date,txn_type,doc_num,name,memo,subt_nat_amount",
    }, headers={"Authorization": f"Bearer {token}", "Accept": "application/json"}, timeout=120)
    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) < 6:
                    continue
                txn_type = cd[1].get("value", "")
                if not txn_type or txn_type == "Beginning Balance" or "Total" in txn_type:
                    continue
                try:
                    d = datetime.strptime(cd[0].get("value", ""), "%Y-%m-%d").date()
                except ValueError:
                    continue
                try:
                    amt = float(cd[5].get("value", "0").replace(",", ""))
                except ValueError:
                    continue
                txns.append({
                    "date": d,
                    "amount": amt,
                    "txn_type": txn_type,
                    "doc_num": cd[2].get("value", ""),
                    "name": cd[3].get("value", ""),
                    "memo": cd[4].get("value", ""),
                })

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


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

    print("Pulling bank CSV...")
    bank = parse_bank_csv(BANK_CSV)
    # Filter to our period
    start_d = datetime.fromisoformat(START).date()
    end_d = datetime.fromisoformat(END).date()
    bank = [b for b in bank if start_d <= b["date"] <= end_d]
    print(f"  {len(bank)} bank transactions in {START} to {END}")

    bank_inflows = [b for b in bank if b["amount"] > 0]
    bank_outflows = [b for b in bank if b["amount"] < 0]
    print(f"  Inflows: {len(bank_inflows)} totaling ${sum(b['amount'] for b in bank_inflows):,.2f}")
    print(f"  Outflows: {len(bank_outflows)} totaling ${sum(b['amount'] for b in bank_outflows):,.2f}")

    print("\nPulling book GL for acct 61...")
    book = pull_book_gl(token)
    print(f"  {len(book)} book transactions in {START} to {END}")

    book_inflows = [b for b in book if b["amount"] > 0]
    book_outflows = [b for b in book if b["amount"] < 0]
    print(f"  Inflows: {len(book_inflows)} totaling ${sum(b['amount'] for b in book_inflows):,.2f}")
    print(f"  Outflows: {len(book_outflows)} totaling ${sum(b['amount'] for b in book_outflows):,.2f}")

    # ── Match by date + amount (±3 day window) ──
    def match(side_a: list[dict], side_b: list[dict]) -> tuple[list[dict], list[dict], list[dict]]:
        """Returns (matched_a_b_pairs, unmatched_a, unmatched_b)."""
        used_b: set = set()
        matched = []
        unmatched_a = []
        for a in side_a:
            best = None
            for i, b in enumerate(side_b):
                if i in used_b:
                    continue
                if abs(b["amount"] - a["amount"]) > 0.01:
                    continue
                day_delta = abs((b["date"] - a["date"]).days)
                if day_delta > 3:
                    continue
                if best is None or day_delta < best[1]:
                    best = (i, day_delta)
            if best:
                used_b.add(best[0])
                matched.append({"a": a, "b": side_b[best[0]], "day_delta": best[1]})
            else:
                unmatched_a.append(a)
        unmatched_b = [b for i, b in enumerate(side_b) if i not in used_b]
        return matched, unmatched_a, unmatched_b

    print("\nMatching inflows (bank vs book)...")
    inf_matched, inf_bank_only, inf_book_only = match(bank_inflows, book_inflows)
    print(f"  Matched: {len(inf_matched)}")
    print(f"  Bank-only (real inflows missing from book): {len(inf_bank_only)} totaling ${sum(b['amount'] for b in inf_bank_only):,.2f}")
    print(f"  Book-only (book inflows with no bank match): {len(inf_book_only)} totaling ${sum(b['amount'] for b in inf_book_only):,.2f}")

    print("\nMatching outflows...")
    out_matched, out_bank_only, out_book_only = match(bank_outflows, book_outflows)
    print(f"  Matched: {len(out_matched)}")
    print(f"  Bank-only (real outflows missing from book): {len(out_bank_only)} totaling ${sum(b['amount'] for b in out_bank_only):,.2f}")
    print(f"  Book-only (book outflows with no bank match): {len(out_book_only)} totaling ${sum(b['amount'] for b in out_book_only):,.2f}")

    # ── Categorize bank-only entries to find funding sources ──
    inflow_categories: dict = defaultdict(lambda: {"count": 0, "total": 0.0, "samples": []})
    for b in inf_bank_only:
        payee = b["payee"]
        # Heuristic categorization
        if "Transfer from" in payee:
            cat = "Internet Transfer IN"
        elif "eDeposit" in payee:
            cat = "eDeposit (QBO Payments)"
        elif "INTUIT" in payee and "DEPOSIT" in payee:
            cat = "Intuit Payments Deposit"
        elif "VENMO" in payee.upper():
            cat = "Venmo"
        elif "Dividend" in payee or "INTEREST" in payee.upper():
            cat = "Interest/Dividend"
        elif "Overdraft" in payee:
            cat = "Overdraft Protection"
        elif "INSTANTACCEPT" in payee:
            cat = "Instant Accept Refund/Credit"
        else:
            cat = "Other inflow"
        inflow_categories[cat]["count"] += 1
        inflow_categories[cat]["total"] += b["amount"]
        if len(inflow_categories[cat]["samples"]) < 3:
            inflow_categories[cat]["samples"].append(b)

    # ── Output report ──
    lines = [
        "# Afton RBFCU 9015 Bank Reconciliation Report",
        f"_Period: {START} to {END}_",
        f"_Generated: {datetime.utcnow().isoformat()}Z_",
        "",
        "## Headline",
        "",
        f"- **Bank CSV**: {len(bank)} txns ({len(bank_inflows)} in / {len(bank_outflows)} out)",
        f"- **Book GL on acct 61**: {len(book)} txns ({len(book_inflows)} in / {len(book_outflows)} out)",
        f"",
        f"- Bank inflows total: ${sum(b['amount'] for b in bank_inflows):,.2f}",
        f"- Bank outflows total: ${sum(b['amount'] for b in bank_outflows):,.2f}",
        f"- Bank net: ${sum(b['amount'] for b in bank):,.2f}",
        f"",
        f"- Book inflows total: ${sum(b['amount'] for b in book_inflows):,.2f}",
        f"- Book outflows total: ${sum(b['amount'] for b in book_outflows):,.2f}",
        f"- Book net: ${sum(b['amount'] for b in book):,.2f}",
        "",
        "## The Gap",
        "",
        f"- Bank-only inflows (missing from book): {len(inf_bank_only)} totaling **${sum(b['amount'] for b in inf_bank_only):,.2f}**",
        f"- Book-only inflows (phantoms): {len(inf_book_only)} totaling ${sum(b['amount'] for b in inf_book_only):,.2f}",
        f"- Bank-only outflows (missing from book): {len(out_bank_only)} totaling ${sum(b['amount'] for b in out_bank_only):,.2f}",
        f"- Book-only outflows (phantoms): {len(out_book_only)} totaling ${sum(b['amount'] for b in out_book_only):,.2f}",
        "",
        "## Bank-only inflows by category (THE FUNDING SOURCES)",
        "",
        "| Category | Count | Total | Sample |",
        "|---|---|---|---|",
    ]
    for cat, v in sorted(inflow_categories.items(), key=lambda x: -x[1]["total"]):
        sample = v["samples"][0] if v["samples"] else {}
        sample_text = f"{sample.get('date','')} ${sample.get('amount',0):,.2f} {sample.get('payee','')[:40]}"
        lines.append(f"| {cat} | {v['count']} | ${v['total']:,.2f} | {sample_text} |")
    lines.append("")

    lines.append("## Top bank-only inflows (the actual missing recordings)")
    lines.append("")
    lines.append("| Date | Amount | Payee | Memo |")
    lines.append("|---|---|---|---|")
    for b in sorted(inf_bank_only, key=lambda x: -x["amount"])[:30]:
        lines.append(f"| {b['date']} | ${b['amount']:,.2f} | {b['payee'][:50]} | {b['memo'][:30]} |")

    OUT_MD.write_text("\n".join(lines), encoding="utf-8")
    OUT_JSON.write_text(json.dumps({
        "generated_at": datetime.utcnow().isoformat() + "Z",
        "headline": {
            "bank_inflows_total": sum(b["amount"] for b in bank_inflows),
            "bank_outflows_total": sum(b["amount"] for b in bank_outflows),
            "book_inflows_total": sum(b["amount"] for b in book_inflows),
            "book_outflows_total": sum(b["amount"] for b in book_outflows),
            "bank_only_inflows_total": sum(b["amount"] for b in inf_bank_only),
            "bank_only_outflows_total": sum(b["amount"] for b in out_bank_only),
            "book_only_inflows_total": sum(b["amount"] for b in inf_book_only),
            "book_only_outflows_total": sum(b["amount"] for b in out_book_only),
        },
        "inflow_categories": dict(inflow_categories),
    }, indent=2, default=str), encoding="utf-8")
    print(f"\nWrote {OUT_MD}")


if __name__ == "__main__":
    main()