"""
Afton UF → RBFCU Bank Matcher
==============================
Cross-references open UF entries against the RBFCU bank CSV export to find
the real bank deposits that correspond to each UF entry.

Match tiers:
  EXACT       Same date, exact amount match (UF entry = bank deposit)
  BATCH_EXACT Same date, sum of UF entries on that date = bank deposit
  FEE_NEAR    Same date, UF amount within 2-4% of bank amount (CC processor fee)
  BATCH_FEE   Same date batch sum within 2-4% of bank amount
  CLOSE       Date within ±3 days, amount exact
  NO_MATCH    No bank deposit candidate found

Outputs:
  - afton_uf_match_worksheet.md   (human-readable groupings + suggestions)
  - afton_uf_match_worksheet.json (full structured data)
  - afton_uf_match_unmatched.csv  (UF entries with no bank match — manual review)
"""
from __future__ import annotations

import csv
import json
import sys
from collections import 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
UF_JSON = SCRIPT_DIR / "afton_uf_cleanup_worksheet.json"
BANK_CSV = SCRIPT_DIR / "20260513-8529015.CSV"
OUT_MD = SCRIPT_DIR / "afton_uf_match_worksheet.md"
OUT_JSON = SCRIPT_DIR / "afton_uf_match_worksheet.json"
OUT_UNMATCHED = SCRIPT_DIR / "afton_uf_match_unmatched.csv"

# QBO Payments fee range (% of gross). Empirical: typically 2.4% - 3.5% per swipe.
FEE_PCT_MIN = 0.005   # 0.5% — covers ACH and low-rate cards
FEE_PCT_MAX = 0.045   # 4.5% — covers Amex/keyed-in
DATE_PROXIMITY_DAYS = 3


def parse_bank_csv(path: Path) -> list[dict]:
    """Returns list of deposit (positive-amount) bank transactions."""
    deposits = []
    with open(path, encoding="utf-8") as f:
        for row in csv.DictReader(f):
            amt = float(row["Amount"])
            if amt <= 0:
                continue
            date = datetime.strptime(row["Post Date"], "%m/%d/%Y").date()
            deposits.append({
                "date": date,
                "amount": amt,
                "payee": row["Payee"],
                "memo": row["Memo"],
            })
    return deposits


def load_uf_entries() -> tuple[list[dict], date]:
    data = json.load(open(UF_JSON, encoding="utf-8"))
    return data["open_entries"], None


def classify_match(uf_amt: float, bank_amt: float) -> tuple[str, float]:
    """Return (match_class, fee_pct_implied)."""
    if abs(uf_amt - bank_amt) < 0.01:
        return ("EXACT", 0.0)
    if uf_amt > bank_amt:
        implied_fee_pct = (uf_amt - bank_amt) / uf_amt
        if FEE_PCT_MIN <= implied_fee_pct <= FEE_PCT_MAX:
            return ("FEE_NEAR", implied_fee_pct)
    return ("NO_MATCH", 0.0)


def find_matches(uf_entries: list[dict], bank_deposits: list[dict]) -> dict:
    """Match UF entries to bank deposits. Returns dict of match groups."""
    # Index bank deposits by date
    bank_by_date: dict[date, list[dict]] = defaultdict(list)
    for d in bank_deposits:
        bank_by_date[d["date"]].append(d)

    # Index UF entries by date
    uf_by_date: dict[str, list[dict]] = defaultdict(list)
    for e in uf_entries:
        if not e.get("txn_date"):
            continue
        uf_by_date[e["txn_date"]].append(e)

    matches: list[dict] = []
    matched_bank_keys: set = set()  # (date_iso, idx)
    matched_uf_ids: set = set()     # (entity, id)

    # ── Pass 1: Same-date batch (sum of all UF entries on date) ──────────
    for date_str, uf_list in sorted(uf_by_date.items()):
        uf_date = datetime.fromisoformat(date_str).date() if "T" not in date_str else datetime.fromisoformat(date_str).date()
        uf_sum = sum(e["amount"] for e in uf_list if e["amount"])

        # Try exact match on same date
        candidates = bank_by_date.get(uf_date, [])
        for idx, bank in enumerate(candidates):
            key = (uf_date.isoformat(), idx)
            if key in matched_bank_keys:
                continue
            match_class, fee_pct = classify_match(uf_sum, bank["amount"])
            if match_class in ("EXACT", "FEE_NEAR") and len(uf_list) > 1:
                # Batch match
                matches.append({
                    "tier": "BATCH_EXACT" if match_class == "EXACT" else "BATCH_FEE",
                    "uf_date": date_str,
                    "bank_date": uf_date.isoformat(),
                    "uf_count": len(uf_list),
                    "uf_total": uf_sum,
                    "bank_amount": bank["amount"],
                    "fee_pct": fee_pct,
                    "fee_amount": uf_sum - bank["amount"],
                    "bank_payee": bank["payee"],
                    "uf_entry_ids": [(e["entity"], e["id"]) for e in uf_list],
                    "uf_entries": uf_list,
                })
                matched_bank_keys.add(key)
                for e in uf_list:
                    matched_uf_ids.add((e["entity"], e["id"]))
                break
            elif match_class in ("EXACT", "FEE_NEAR") and len(uf_list) == 1:
                # Single match
                matches.append({
                    "tier": match_class,
                    "uf_date": date_str,
                    "bank_date": uf_date.isoformat(),
                    "uf_count": 1,
                    "uf_total": uf_sum,
                    "bank_amount": bank["amount"],
                    "fee_pct": fee_pct,
                    "fee_amount": uf_sum - bank["amount"],
                    "bank_payee": bank["payee"],
                    "uf_entry_ids": [(e["entity"], e["id"]) for e in uf_list],
                    "uf_entries": uf_list,
                })
                matched_bank_keys.add(key)
                for e in uf_list:
                    matched_uf_ids.add((e["entity"], e["id"]))
                break

    # ── Pass 2: Single UF entry matches a single bank deposit (cross-date) ──
    for e in uf_entries:
        if (e["entity"], e["id"]) in matched_uf_ids:
            continue
        if not e.get("txn_date") or not e.get("amount"):
            continue
        uf_date = datetime.fromisoformat(e["txn_date"]).date()
        amt = e["amount"]

        # Check ±3 days
        for delta in range(-DATE_PROXIMITY_DAYS, DATE_PROXIMITY_DAYS + 1):
            check_date = uf_date + timedelta(days=delta)
            candidates = bank_by_date.get(check_date, [])
            best_match = None
            for idx, bank in enumerate(candidates):
                key = (check_date.isoformat(), idx)
                if key in matched_bank_keys:
                    continue
                match_class, fee_pct = classify_match(amt, bank["amount"])
                if match_class in ("EXACT", "FEE_NEAR"):
                    best_match = (idx, bank, match_class, fee_pct, key)
                    break
            if best_match:
                idx, bank, match_class, fee_pct, key = best_match
                tier = "CLOSE" if delta != 0 else match_class
                matches.append({
                    "tier": tier,
                    "uf_date": e["txn_date"],
                    "bank_date": check_date.isoformat(),
                    "date_delta_days": delta,
                    "uf_count": 1,
                    "uf_total": amt,
                    "bank_amount": bank["amount"],
                    "fee_pct": fee_pct,
                    "fee_amount": amt - bank["amount"],
                    "bank_payee": bank["payee"],
                    "uf_entry_ids": [(e["entity"], e["id"])],
                    "uf_entries": [e],
                })
                matched_bank_keys.add(key)
                matched_uf_ids.add((e["entity"], e["id"]))
                break

    # ── Unmatched ────────────────────────────────────────────────────────
    unmatched_uf = [
        e for e in uf_entries
        if (e["entity"], e["id"]) not in matched_uf_ids
    ]
    unmatched_bank = []
    for date, deposits_on_date in bank_by_date.items():
        for idx, bank in enumerate(deposits_on_date):
            if (date.isoformat(), idx) not in matched_bank_keys:
                unmatched_bank.append(bank)

    return {
        "matches": matches,
        "unmatched_uf": unmatched_uf,
        "unmatched_bank": unmatched_bank,
    }


def main() -> None:
    print("Loading UF entries...")
    uf_entries, _ = load_uf_entries()
    print(f"  {len(uf_entries)} open UF entries")

    print("Loading bank CSV...")
    bank_deposits = parse_bank_csv(BANK_CSV)
    print(f"  {len(bank_deposits)} bank deposits in CSV")

    # Filter UF entries to the bank CSV date range for cleaner matching
    if bank_deposits:
        min_bank_date = min(d["date"] for d in bank_deposits)
        max_bank_date = max(d["date"] for d in bank_deposits)
        # Allow UF entries within ±7 days of CSV range
        min_uf = (min_bank_date - timedelta(days=7)).isoformat()
        max_uf = (max_bank_date + timedelta(days=7)).isoformat()
        in_range = [
            e for e in uf_entries
            if e.get("txn_date") and min_uf <= e["txn_date"] <= max_uf
        ]
        print(f"  Bank CSV range: {min_bank_date} to {max_bank_date}")
        print(f"  UF entries in range: {len(in_range)}")
        uf_entries = in_range

    print("\nMatching...")
    result = find_matches(uf_entries, bank_deposits)

    matches = result["matches"]
    unmatched_uf = result["unmatched_uf"]
    unmatched_bank = result["unmatched_bank"]

    # Tier counts
    tier_counts: dict[str, dict] = defaultdict(lambda: {"count": 0, "total": 0.0})
    for m in matches:
        tier_counts[m["tier"]]["count"] += 1
        tier_counts[m["tier"]]["total"] += m["uf_total"]

    print(f"\n=== RESULTS ===")
    print(f"In-range UF entries: {len(uf_entries)}")
    print(f"Matched: {sum(len(m['uf_entry_ids']) for m in matches)} UF entries via {len(matches)} bank deposits")
    print(f"Unmatched UF: {len(unmatched_uf)}")
    print(f"Unmatched bank deposits: {len(unmatched_bank)} totaling ${sum(b['amount'] for b in unmatched_bank):,.2f}")
    print(f"\nBy tier:")
    for tier in ("EXACT", "BATCH_EXACT", "FEE_NEAR", "BATCH_FEE", "CLOSE", "NO_MATCH"):
        v = tier_counts.get(tier, {"count": 0, "total": 0.0})
        print(f"  {tier:14s} {v['count']:3d} matches  ${v['total']:>10,.2f}")

    # Write outputs
    OUT_JSON.write_text(json.dumps({
        "generated_at": datetime.utcnow().isoformat() + "Z",
        "bank_csv": str(BANK_CSV.name),
        "matches": matches,
        "unmatched_uf": unmatched_uf,
        "unmatched_bank": [{"date": b["date"].isoformat(), **{k: v for k, v in b.items() if k != "date"}} for b in unmatched_bank],
        "tier_counts": dict(tier_counts),
    }, indent=2, default=str), encoding="utf-8")
    print(f"\nWrote {OUT_JSON}")

    # Markdown summary
    lines = [
        "# Afton UF → RBFCU Match Worksheet",
        f"_Generated: {datetime.utcnow().isoformat()}Z_",
        f"_Source: {BANK_CSV.name}_",
        "",
        "## Headline",
        "",
        f"- **{len(uf_entries)} UF entries** in bank CSV date range",
        f"- **{sum(len(m['uf_entry_ids']) for m in matches)} matched** ({len(matches)} bank deposits cover them)",
        f"- **{len(unmatched_uf)} unmatched UF** — need manual review",
        f"- **{len(unmatched_bank)} unmatched bank deposits** totaling ${sum(b['amount'] for b in unmatched_bank):,.2f} — likely non-UF deposits (transfers, refunds, interest)",
        "",
        "## Match tier breakdown",
        "",
        "| Tier | Description | Matches | UF $ |",
        "|---|---|---|---|",
        f"| EXACT | Same date, exact $ match (1:1) | {tier_counts['EXACT']['count']} | ${tier_counts['EXACT']['total']:,.2f} |",
        f"| BATCH_EXACT | Same date, sum of N UF entries = bank | {tier_counts['BATCH_EXACT']['count']} | ${tier_counts['BATCH_EXACT']['total']:,.2f} |",
        f"| FEE_NEAR | Same date, UF≈bank±CC fee (1:1) | {tier_counts['FEE_NEAR']['count']} | ${tier_counts['FEE_NEAR']['total']:,.2f} |",
        f"| BATCH_FEE | Same date batch sum ≈ bank±CC fee | {tier_counts['BATCH_FEE']['count']} | ${tier_counts['BATCH_FEE']['total']:,.2f} |",
        f"| CLOSE | ±{DATE_PROXIMITY_DAYS}d, exact $ match | {tier_counts['CLOSE']['count']} | ${tier_counts['CLOSE']['total']:,.2f} |",
        "",
        "## Matches detail",
        "",
        "| Tier | Bank Date | Bank $ | UF Date | UF Count | UF $ | Fee | Payee |",
        "|---|---|---|---|---|---|---|---|",
    ]
    for m in sorted(matches, key=lambda x: (x["tier"], x["bank_date"])):
        fee_disp = f"${m['fee_amount']:,.2f} ({m['fee_pct']*100:.1f}%)" if m["fee_amount"] > 0.01 else "—"
        lines.append(
            f"| {m['tier']} | {m['bank_date']} | ${m['bank_amount']:,.2f} | "
            f"{m['uf_date']} | {m['uf_count']} | ${m['uf_total']:,.2f} | {fee_disp} | "
            f"{m['bank_payee'][:40]} |"
        )
    lines.append("")
    lines.append("## Unmatched UF entries (need manual review)")
    lines.append("")
    if not unmatched_uf:
        lines.append("_All in-range UF entries matched._")
    else:
        lines.append("| Date | Customer | Amount | Entity | ID |")
        lines.append("|---|---|---|---|---|")
        for e in sorted(unmatched_uf, key=lambda x: x.get("txn_date") or ""):
            lines.append(
                f"| {e.get('txn_date','')} | {e.get('customer','')} | "
                f"${e.get('amount',0):,.2f} | {e['entity']} | {e['id']} |"
            )
    lines.append("")
    lines.append("## Unmatched bank deposits (likely non-UF: transfers, refunds, interest)")
    lines.append("")
    if not unmatched_bank:
        lines.append("_All bank deposits matched._")
    else:
        lines.append("| Date | Amount | Payee |")
        lines.append("|---|---|---|")
        for b in sorted(unmatched_bank, key=lambda x: x["date"]):
            lines.append(f"| {b['date']} | ${b['amount']:,.2f} | {b['payee'][:60]} |")
    OUT_MD.write_text("\n".join(lines), encoding="utf-8")
    print(f"Wrote {OUT_MD}")

    # Unmatched CSV for triage
    if unmatched_uf:
        with open(OUT_UNMATCHED, "w", newline="", encoding="utf-8") as f:
            w = csv.DictWriter(f, fieldnames=list(unmatched_uf[0].keys()))
            w.writeheader()
            for e in unmatched_uf:
                w.writerow(e)
        print(f"Wrote {OUT_UNMATCHED}")


if __name__ == "__main__":
    main()