"""
Afton UF Cleanup Analysis — READ-ONLY
=====================================
Inventories every transaction sitting in Undeposited Funds (acct 4) and
proposes groupings for the Make Deposits workflow.

Sources of UF balance:
  - Payments with DepositToAccountRef=4 and no Deposit in LinkedTxn[]
  - Sales Receipts with DepositToAccountRef=4 and no Deposit in LinkedTxn[]
  - JournalEntry lines that Dr or Cr acct 4 (plug JEs, our own cleanup JE)

Cross-references against existing Deposit txns that hit RBFCU (acct 61) so we
can see which date ranges have already been deposited vs. which are gaps.

Outputs:
  - afton_uf_cleanup_worksheet.json : full structured dump
  - afton_uf_cleanup_worksheet.md   : human-readable groupings + summary
  - afton_uf_cleanup_open_entries.csv : flat CSV of all open UF entries for
                                         Sheets/Excel triage
"""
from __future__ import annotations

import csv
import json
import sys
from collections import defaultdict
from datetime import date, datetime
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"
UF_ACCT_ID = "4"
RBFCU_ACCT_ID = "61"

OUT_JSON = SCRIPT_DIR / "afton_uf_cleanup_worksheet.json"
OUT_MD = SCRIPT_DIR / "afton_uf_cleanup_worksheet.md"
OUT_CSV = SCRIPT_DIR / "afton_uf_cleanup_open_entries.csv"


def page_query(token: str, sql_base: str, entity: str) -> list[dict]:
    """Paginate a SQL query through QBO."""
    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 has_deposit_link(linked: list[dict]) -> bool:
    return any(lt.get("TxnType") == "Deposit" for lt in (linked or []))


def is_open_uf(entry: dict) -> bool:
    """Open UF = payment/receipt with DepositToAcct=4 AND no Deposit LinkedTxn."""
    if (entry.get("DepositToAccountRef") or {}).get("value") != UF_ACCT_ID:
        return False
    return not has_deposit_link(entry.get("LinkedTxn", []))


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

    # ── 1. Pull all Payments + SalesReceipts (any deposit acct), then filter ──
    print("Pulling Payments...", file=sys.stderr)
    payments = page_query(token, "SELECT * FROM Payment", "Payment")
    print(f"  {len(payments)} total payments")

    print("Pulling SalesReceipts...", file=sys.stderr)
    sales_receipts = page_query(token, "SELECT * FROM SalesReceipt", "SalesReceipt")
    print(f"  {len(sales_receipts)} total sales receipts")

    open_pmts = [p for p in payments if is_open_uf(p)]
    open_srs = [s for s in sales_receipts if is_open_uf(s)]
    print(f"  → {len(open_pmts)} open UF payments, {len(open_srs)} open UF sales receipts")

    # ── 2. Pull JE lines hitting UF (acct 4) ──────────────────────────────────
    print("Pulling JournalEntries...", file=sys.stderr)
    jes = page_query(token, "SELECT * FROM JournalEntry", "JournalEntry")
    uf_je_lines: list[dict] = []
    for je in jes:
        for line in je.get("Line", []):
            detail = line.get("JournalEntryLineDetail", {}) or {}
            acct_ref = detail.get("AccountRef") or {}
            if acct_ref.get("value") == UF_ACCT_ID:
                uf_je_lines.append({
                    "je_id": je.get("Id"),
                    "txn_date": je.get("TxnDate"),
                    "doc_number": je.get("DocNumber"),
                    "private_note": (je.get("PrivateNote") or "")[:200],
                    "amount": line.get("Amount"),
                    "posting_type": detail.get("PostingType"),
                    "description": line.get("Description"),
                })
    print(f"  → {len(uf_je_lines)} JE lines hitting UF")

    # ── 3. Pull all Deposits hitting RBFCU (for context — what's already done) ──
    print("Pulling Deposits to RBFCU...", file=sys.stderr)
    deposits = page_query(
        token,
        f"SELECT * FROM Deposit WHERE DepositToAccountRef = '{RBFCU_ACCT_ID}'",
        "Deposit",
    )
    print(f"  → {len(deposits)} deposits to RBFCU on record")

    # ── 4. Build flat list of open UF entries ─────────────────────────────────
    open_entries = []
    for p in open_pmts:
        open_entries.append({
            "entity": "Payment",
            "id": p.get("Id"),
            "txn_date": p.get("TxnDate"),
            "customer": (p.get("CustomerRef") or {}).get("name"),
            "amount": p.get("TotalAmt"),
            "payment_method": (p.get("PaymentMethodRef") or {}).get("value"),
            "txn_source": p.get("TxnSource"),
            "is_cc": bool(p.get("CreditCardPayment")),
            "doc_number": p.get("PaymentRefNum"),
        })
    for s in open_srs:
        open_entries.append({
            "entity": "SalesReceipt",
            "id": s.get("Id"),
            "txn_date": s.get("TxnDate"),
            "customer": (s.get("CustomerRef") or {}).get("name"),
            "amount": s.get("TotalAmt"),
            "payment_method": (s.get("PaymentMethodRef") or {}).get("value"),
            "txn_source": s.get("TxnSource"),
            "is_cc": bool(s.get("CreditCardPayment")),
            "doc_number": s.get("DocNumber"),
        })
    # Sort by date asc
    open_entries.sort(key=lambda e: (e["txn_date"] or "", e["customer"] or ""))

    open_total = sum(e["amount"] or 0 for e in open_entries)
    print(f"\nOpen UF entries: {len(open_entries)} totaling ${open_total:,.2f}")

    # ── 5. Build groupings (by date) ──────────────────────────────────────────
    by_date: dict[str, list[dict]] = defaultdict(list)
    for e in open_entries:
        by_date[e["txn_date"] or "UNKNOWN"].append(e)
    date_groups = []
    for d in sorted(by_date.keys()):
        entries = by_date[d]
        date_groups.append({
            "date": d,
            "count": len(entries),
            "total": sum(e["amount"] or 0 for e in entries),
            "entries": entries,
        })

    # ── 6. Year breakdown ─────────────────────────────────────────────────────
    by_year: dict[str, dict] = defaultdict(lambda: {"count": 0, "total": 0.0})
    for e in open_entries:
        y = (e["txn_date"] or "????")[:4]
        by_year[y]["count"] += 1
        by_year[y]["total"] += e["amount"] or 0

    # ── 7. RBFCU deposit summary by year ──────────────────────────────────────
    rbfcu_by_year: dict[str, dict] = defaultdict(lambda: {"count": 0, "total": 0.0})
    for d in deposits:
        y = (d.get("TxnDate") or "????")[:4]
        rbfcu_by_year[y]["count"] += 1
        rbfcu_by_year[y]["total"] += d.get("TotalAmt") or 0

    # ── 8. JE plug summary ────────────────────────────────────────────────────
    je_dr = sum(j["amount"] for j in uf_je_lines if j["posting_type"] == "Debit")
    je_cr = sum(j["amount"] for j in uf_je_lines if j["posting_type"] == "Credit")
    print(f"\nUF JE activity: ${je_dr:,.2f} Dr, ${je_cr:,.2f} Cr, net Dr=${je_dr - je_cr:,.2f}")

    # ── 9. Write outputs ──────────────────────────────────────────────────────
    payload = {
        "generated_at": datetime.utcnow().isoformat() + "Z",
        "summary": {
            "open_uf_entries": len(open_entries),
            "open_uf_total": open_total,
            "uf_je_lines": len(uf_je_lines),
            "uf_je_dr": je_dr,
            "uf_je_cr": je_cr,
            "uf_je_net_dr": je_dr - je_cr,
            "rbfcu_deposits_recorded": len(deposits),
            "rbfcu_deposit_total": sum(d.get("TotalAmt") or 0 for d in deposits),
        },
        "by_year_open_uf": dict(by_year),
        "by_year_rbfcu_deposits": dict(rbfcu_by_year),
        "open_entries": open_entries,
        "date_groups": date_groups,
        "uf_je_lines": uf_je_lines,
    }
    OUT_JSON.write_text(json.dumps(payload, indent=2, default=str), encoding="utf-8")
    print(f"\nWrote {OUT_JSON}")

    # CSV — flat open entries
    with open(OUT_CSV, "w", newline="", encoding="utf-8") as f:
        w = csv.DictWriter(f, fieldnames=list(open_entries[0].keys()) if open_entries else [])
        w.writeheader()
        for e in open_entries:
            w.writerow(e)
    print(f"Wrote {OUT_CSV}")

    # Markdown summary
    lines = [
        "# Afton UF Cleanup Worksheet",
        f"_Generated: {datetime.utcnow().isoformat()}Z_",
        "",
        "## Headline",
        "",
        f"- **{len(open_entries):,} open UF entries** totaling **${open_total:,.2f}** waiting to be deposited",
        f"- **{len(uf_je_lines)} JE lines** have hit UF (plug JEs + our cleanup JE)",
        f"  - Total Dr: ${je_dr:,.2f}",
        f"  - Total Cr: ${je_cr:,.2f}",
        f"  - Net Dr (adds to UF): ${je_dr - je_cr:,.2f}",
        f"- **{len(deposits):,} existing Deposits** to RBFCU on record, totaling ${sum(d.get('TotalAmt') or 0 for d in deposits):,.2f}",
        "",
        "**Reconciliation math:**",
        "",
        f"- Sum of open UF entries: ${open_total:,.2f}",
        f"- Plus net JE adjustments: ${je_dr - je_cr:,.2f}",
        f"- = Should equal current UF balance ($263,617.24 post-cleanup-JE)",
        "",
        "## Open UF entries by year",
        "",
        "| Year | Entries | Total |",
        "|---|---|---|",
    ]
    for y in sorted(by_year.keys()):
        v = by_year[y]
        lines.append(f"| {y} | {v['count']:,} | ${v['total']:,.2f} |")
    lines.append("")
    lines.append("## RBFCU deposits already on record, by year")
    lines.append("")
    lines.append("| Year | Deposits | Total |")
    lines.append("|---|---|---|")
    for y in sorted(rbfcu_by_year.keys()):
        v = rbfcu_by_year[y]
        lines.append(f"| {y} | {v['count']:,} | ${v['total']:,.2f} |")
    lines.append("")
    lines.append("## Date-based grouping suggestions (open UF only)")
    lines.append("")
    lines.append("Each row = all UF entries with that TxnDate. These are the most natural Make Deposits candidates — same-day customer payments typically batch into one bank deposit.")
    lines.append("")
    lines.append("| Date | # entries | Total | Notes |")
    lines.append("|---|---|---|---|")
    for g in date_groups:
        note = ""
        if g["count"] == 1:
            note = "single pmt"
        elif g["count"] >= 10:
            note = f"BIG batch — {g['count']} entries"
        lines.append(f"| {g['date']} | {g['count']} | ${g['total']:,.2f} | {note} |")
    lines.append("")
    lines.append("## JE lines hitting UF")
    lines.append("")
    if not uf_je_lines:
        lines.append("_No JE activity on UF._")
    else:
        lines.append("| JE Id | Date | DocNumber | Posting | Amount | Description |")
        lines.append("|---|---|---|---|---|---|")
        for j in sorted(uf_je_lines, key=lambda x: x["txn_date"] or ""):
            lines.append(
                f"| {j['je_id']} | {j['txn_date']} | {j['doc_number'] or ''} | "
                f"{j['posting_type']} | ${j['amount']:,.2f} | "
                f"{(j['description'] or '')[:60]} |"
            )
    OUT_MD.write_text("\n".join(lines), encoding="utf-8")
    print(f"Wrote {OUT_MD}")


if __name__ == "__main__":
    main()