"""
Afton No-Method UF JE Plug Stager
==================================
For the 179 UF entries with no PaymentMethod (cleanup option A):
  - Follow each Payment's LinkedTxn → Invoice → income account
  - Build one mega-JE: Dr that income account / Cr UF per pair (reverses the
    double-counted income from the original direct-deposit recording)

Two tiers:
  - AUTO-PLUG: same-day entries NOT in the 2026-05-15 catch-up cluster
  - HOLD:      2026-05-15 cluster + 30+ day backdated entries (higher phantom risk)

Defaults to DRY RUN. Pass --apply to stage to write_queue.json.
"""
from __future__ import annotations

import argparse
import json
import sys
from collections import defaultdict
from datetime import 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"
QUEUE_DIR = SCRIPT_DIR.parent.parent / "Team" / "Riv" / "j2_qbo_mcp"
sys.path.insert(0, str(QBO_APP_DIR))
sys.path.insert(0, str(QUEUE_DIR))

import qbo_client as qbo  # type: ignore
from queue_backend import append as queue_append  # type: ignore

CLIENT = "afton"
UF_ACCT_ID = "4"
FORENSICS_JSON = SCRIPT_DIR / "afton_uf_forensics.json"
TXN_DATE = "2026-05-22"  # Same date as today's dup cleanup JE for traceability
DOC_NUMBER = "AFTON-UF-PLUG"  # 13 chars
CATCH_UP_DAY = "2026-05-15"
BACKDATE_THRESHOLD_DAYS = 30

OUT_REPORT = SCRIPT_DIR / "afton_uf_no_method_je_report.md"


def batch_query(token: str, entity: str, ids: list[str]) -> dict[str, dict]:
    out: dict[str, dict] = {}
    BATCH = 30
    for i in range(0, len(ids), BATCH):
        chunk = ids[i : i + BATCH]
        in_clause = ", ".join(f"'{x}'" for x in chunk)
        sql = f"SELECT * FROM {entity} WHERE Id IN ({in_clause})"
        resp = qbo.query(CLIENT, token, sql)
        for r in resp.get(entity, []):
            out[str(r["Id"])] = r
    return out


def get_invoice_income_acct(inv: dict, items_by_id: dict[str, dict]) -> dict | None:
    """Return the income account ref for an invoice. Prefers single-line invoices;
    for multi-line, returns the account of the largest line."""
    best_line_amt = 0
    best_acct = None
    for line in inv.get("Line", []):
        if line.get("DetailType") != "SalesItemLineDetail":
            continue
        detail = line.get("SalesItemLineDetail", {}) or {}
        item_ref = detail.get("ItemRef") or {}
        item = items_by_id.get(str(item_ref.get("value", "")))
        if not item:
            continue
        income_acct = item.get("IncomeAccountRef") or item.get("ExpenseAccountRef")
        if not income_acct:
            continue
        amt = line.get("Amount", 0) or 0
        if amt > best_line_amt:
            best_line_amt = amt
            best_acct = income_acct
    return best_acct


def classify_entry(r: dict) -> str:
    """Return 'auto' or 'hold' for an entry.

    NOTE: 2026-05-15 catch-up cluster was investigated via afton_uf_phantom_check.py
    and all 27 entries classified LIKELY_REAL. So we no longer hold them.
    """
    # Check backdate lag — anything ≥30 days backdated still gets held
    if r.get("create_time") and r.get("txn_date"):
        try:
            ct = datetime.fromisoformat(r["create_time"].replace("Z", "+00:00")).date()
            td = datetime.fromisoformat(r["txn_date"]).date()
            if (ct - td).days >= BACKDATE_THRESHOLD_DAYS:
                return "hold"
        except Exception:
            pass
    return "auto"


def main() -> None:
    ap = argparse.ArgumentParser(description=__doc__)
    ap.add_argument("--apply", action="store_true")
    args = ap.parse_args()

    forensics = json.load(open(FORENSICS_JSON, encoding="utf-8"))
    no_method = [r for r in forensics["rows"] if not r.get("payment_method_id")]
    print(f"Loaded {len(no_method)} no-method UF entries")

    auto_entries = [r for r in no_method if classify_entry(r) == "auto"]
    hold_entries = [r for r in no_method if classify_entry(r) == "hold"]
    print(f"  AUTO-PLUG: {len(auto_entries)} entries (${sum(r['amount'] for r in auto_entries):,.2f})")
    print(f"  HOLD:      {len(hold_entries)} entries (${sum(r['amount'] for r in hold_entries):,.2f})")

    token = qbo.refresh_access_token(CLIENT)

    # ── Pull linked Invoice IDs for each auto entry ──
    pmt_ids = [r["id"] for r in auto_entries if r["entity"] == "Payment"]
    sr_ids = [r["id"] for r in auto_entries if r["entity"] == "SalesReceipt"]
    print(f"\nPulling {len(pmt_ids)} payments + {len(sr_ids)} sales receipts...")
    pmts = batch_query(token, "Payment", pmt_ids)
    srs = batch_query(token, "SalesReceipt", sr_ids)

    # Extract invoice IDs from Payment LinkedTxns
    invoice_ids: set = set()
    pmt_to_invoice: dict[str, str] = {}
    for pid, p in pmts.items():
        for line in p.get("Line", []):
            for lt in line.get("LinkedTxn", []) or []:
                if lt.get("TxnType") == "Invoice":
                    invoice_ids.add(str(lt["TxnId"]))
                    pmt_to_invoice[pid] = str(lt["TxnId"])
                    break
            if pid in pmt_to_invoice:
                break

    print(f"Pulling {len(invoice_ids)} linked invoices...")
    invoices = batch_query(token, "Invoice", list(invoice_ids))

    # Extract all item IDs from those invoices
    item_ids: set = set()
    for inv in invoices.values():
        for line in inv.get("Line", []):
            if line.get("DetailType") != "SalesItemLineDetail":
                continue
            iref = (line.get("SalesItemLineDetail", {}) or {}).get("ItemRef") or {}
            if iref.get("value"):
                item_ids.add(str(iref["value"]))

    print(f"Pulling {len(item_ids)} item definitions for income account lookup...")
    items_by_id = batch_query(token, "Item", list(item_ids))

    # ── Build JE lines ──
    je_lines: list[dict] = []
    dr_total = 0.0
    cr_total = 0.0
    per_pair_audit: list[str] = []
    routing_summary: dict[str, dict] = defaultdict(lambda: {"count": 0, "total": 0.0})
    unmatched_invoice = []

    for r in auto_entries:
        pid = r["id"]
        amt = r["amount"]
        customer = r["customer"]

        inv_id = pmt_to_invoice.get(pid)
        if not inv_id:
            unmatched_invoice.append({**r, "reason": "no LinkedTxn invoice found"})
            continue
        inv = invoices.get(inv_id)
        if not inv:
            unmatched_invoice.append({**r, "reason": f"invoice {inv_id} not found"})
            continue
        income_acct = get_invoice_income_acct(inv, items_by_id)
        if not income_acct:
            unmatched_invoice.append({**r, "reason": "no income acct on invoice items"})
            continue

        desc_base = (
            f"UF plug: {customer} | pmt {pid} | inv {inv_id} | "
            f"reverses systemic double-count from direct-deposit pattern"
        )
        je_lines.append({
            "Description": f"{desc_base} | Dr Income reverse",
            "Amount": amt,
            "PostingType": "Debit",
            "AccountRef": {
                "value": str(income_acct["value"]),
                "name": income_acct.get("name", ""),
            },
        })
        je_lines.append({
            "Description": f"{desc_base} | Cr UF clear",
            "Amount": amt,
            "PostingType": "Credit",
            "AccountRef": {"value": UF_ACCT_ID, "name": "Undeposited Funds"},
        })
        dr_total += amt
        cr_total += amt
        key = f"{income_acct['value']} - {income_acct.get('name','')}"
        routing_summary[key]["count"] += 1
        routing_summary[key]["total"] += amt
        per_pair_audit.append(
            f"{customer}: ${amt:.2f} Dr {income_acct.get('name','')} / Cr UF "
            f"[pmt={pid}, inv={inv_id}]"
        )

    if abs(dr_total - cr_total) > 0.01:
        sys.exit(f"JE unbalanced! Dr=${dr_total:.2f} Cr=${cr_total:.2f}")

    n_plugged = len(je_lines) // 2
    print(f"\nBuilt JE: {len(je_lines)} lines, {n_plugged} pairs, Dr=Cr=${dr_total:,.2f}")
    print(f"  Unmatched-invoice: {len(unmatched_invoice)} (excluded from JE, see report)")
    print(f"\nRouting (Dr lines by income acct):")
    for k, v in sorted(routing_summary.items(), key=lambda x: -x[1]["total"]):
        print(f"  {k:55s}  {v['count']:3d}  ${v['total']:>12,.2f}")

    # ── Report ────────────────────────────────────────────────────────────
    lines = [
        "# Afton UF No-Method JE Plug Report",
        f"_Generated: {datetime.utcnow().isoformat()}Z_",
        "",
        "## Summary",
        "",
        f"- Total no-method UF entries: {len(no_method)} (${sum(r['amount'] for r in no_method):,.2f})",
        f"- **AUTO-PLUG**: {len(auto_entries)} (${sum(r['amount'] for r in auto_entries):,.2f}) — same-day, not in 5/15 cluster, not heavily backdated",
        f"- **HOLD for review**: {len(hold_entries)} (${sum(r['amount'] for r in hold_entries):,.2f}) — phantom-risk entries",
        f"- **Excluded from JE** (no usable invoice link): {len(unmatched_invoice)} (${sum(r['amount'] for r in unmatched_invoice):,.2f})",
        f"- **In JE**: {n_plugged} (${dr_total:,.2f})",
        "",
        "## JE Routing (Dr lines)",
        "",
        "| Income Account | Count | Total |",
        "|---|---|---|",
    ]
    for k, v in sorted(routing_summary.items(), key=lambda x: -x[1]["total"]):
        lines.append(f"| {k} | {v['count']} | ${v['total']:,.2f} |")
    lines.append("")
    lines.append(f"All credits route to acct 4 (Undeposited Funds): {n_plugged} lines / ${cr_total:,.2f}")
    lines.append("")
    lines.append("## HOLD entries — need your eyeball for phantom vs. real")
    lines.append("")
    lines.append("These are excluded from the auto-plug. Review each: if you remember actually receiving the payment, it's real (we can plug it in a follow-up batch). If you DON'T remember it, it's likely a phantom AR-clearing entry from a bookkeeper — those payments should be VOIDED instead (which reopens the invoice for AR aging/collections).")
    lines.append("")
    lines.append("| Create Day | Txn Date | Customer | $ | Pmt Id | Why HELD |")
    lines.append("|---|---|---|---|---|---|")
    for r in sorted(hold_entries, key=lambda x: (x.get("create_day",""), x.get("txn_date",""))):
        reason = "5/15 catch-up" if r.get("create_day") == CATCH_UP_DAY else "30+ day backdate"
        lines.append(
            f"| {r.get('create_day','')} | {r.get('txn_date','')} | {r['customer']} | "
            f"${r['amount']:,.2f} | {r['id']} | {reason} |"
        )
    if unmatched_invoice:
        lines.append("")
        lines.append("## Excluded — no usable invoice link")
        lines.append("")
        lines.append("| Pmt Id | Customer | $ | Reason |")
        lines.append("|---|---|---|---|")
        for r in unmatched_invoice:
            lines.append(f"| {r['id']} | {r['customer']} | ${r['amount']:,.2f} | {r['reason']} |")
    OUT_REPORT.write_text("\n".join(lines), encoding="utf-8")
    print(f"Wrote {OUT_REPORT}")

    if not args.apply:
        print("\nDRY RUN — pass --apply to actually queue the JE")
        return

    # Build full payload
    private_note = (
        f"Afton UF no-method cleanup (option A) — plugs {n_plugged} of {len(no_method)} "
        f"no-PaymentMethod UF entries by reversing the double-counted income from the "
        f"systematic direct-deposit recording pattern (1,331 of 1,893 historical RBFCU "
        f"deposits bypass UF via direct Cr Income lines). HELD: {len(hold_entries)} "
        f"phantom-risk entries (5/15 cluster + 30+ day backdates) — need owner eyeball. "
        f"Per-pair audit:\n" + "\n".join(per_pair_audit)
    )
    payload = {
        "TxnDate": TXN_DATE,
        "Line": je_lines,
        "Adjustment": True,
        "DocNumber": DOC_NUMBER,
        "PrivateNote": private_note[:8000],  # safety cap
    }
    row_id = queue_append({
        "timestamp": datetime.now().isoformat(timespec="seconds"),
        "client": CLIENT,
        "tool_called": "create_journal_entry",
        "payload_json": json.dumps(payload, default=str),
        "requested_by": "Ledger (afton_uf_no_method_je_stager.py)",
        "status": "Pending",
        "tier": 1,
    })
    print(f"\nQUEUED as row {row_id}")
    print("Approve in QBO Write Queue Sheet → 'afton' tab → Y in Approve col → run worker")


if __name__ == "__main__":
    main()