"""Validation and calculation for confirmed PDF line items."""

from __future__ import annotations

import re
from collections import Counter
from typing import Any


def _amount_of(row: dict[str, Any]) -> float:
    if row.get("debit") is not None:
        return float(row["debit"])
    if row.get("credit") is not None:
        return float(row["credit"])
    if row.get("amount") is not None:
        return float(row["amount"])
    return 0.0


def _money_out(row: dict[str, Any]) -> float:
    """Withdrawals / debits from the account."""
    if row.get("debit") is not None:
        return abs(float(row["debit"]))
    # Some extracts only set signed amount (negative = outflow)
    if row.get("credit") is None and row.get("amount") is not None:
        try:
            val = float(row["amount"])
        except (TypeError, ValueError):
            return 0.0
        if val < 0:
            return abs(val)
    return 0.0


def _money_in(row: dict[str, Any]) -> float:
    """Deposits / credits into the account."""
    if row.get("credit") is not None:
        return abs(float(row["credit"]))
    if row.get("debit") is None and row.get("amount") is not None:
        try:
            val = float(row["amount"])
        except (TypeError, ValueError):
            return 0.0
        if val > 0:
            return val
    return 0.0


def validate_rows(
    rows: list[dict[str, Any]],
    *,
    document_kind: str = "unknown",
) -> dict[str, Any]:
    issues: list[str] = []
    if not rows:
        issues.append("No line items to validate.")
        return {"ok": False, "issues": issues, "row_count": 0}

    missing_amount = 0
    for i, row in enumerate(rows, start=1):
        has_num = any(row.get(k) is not None for k in ("debit", "credit", "amount"))
        if not has_num:
            missing_amount += 1
            issues.append(f"Row {i} has no numeric amount.")

    debits = sum(float(r["debit"]) for r in rows if r.get("debit") is not None)
    credits = sum(float(r["credit"]) for r in rows if r.get("credit") is not None)
    has_dc = any(r.get("debit") is not None or r.get("credit") is not None for r in rows)

    # Bank statements normally do NOT balance (money in ≠ money out).
    balance_ok = True
    if document_kind == "bank_statement":
        balance_ok = True
    elif has_dc and debits and credits:
        if abs(debits - credits) > 0.01:
            balance_ok = False
            issues.append(
                f"Debits ({debits:.2f}) and credits ({credits:.2f}) do not balance."
            )

    ok = missing_amount == 0 and balance_ok

    return {
        "ok": ok,
        "issues": issues,
        "row_count": len(rows),
        "debit_total": round(debits, 2),
        "credit_total": round(credits, 2),
        "balanced": None if document_kind == "bank_statement" else (
            balance_ok if has_dc else None
        ),
        "document_kind": document_kind,
    }


def _rough_category(description: str) -> str:
    d = (description or "").lower()
    rules = [
        ("groceries", ("# groceries", "grocery", "zepto", "instamart", "bigbasket", "blinkit")),
        ("food", ("# food", "zomato", "swiggy", "restaurant")),
        ("taxi", ("# taxi", "uber", "ola")),
        ("bills", ("# bill", "recharge", "bill payment")),
        ("salary / payroll", ("salary", "payroll", "wage", "stipend")),
        ("upi / digital pay", ("upi", "gpay", "phonepe", "paytm", "bhim")),
        ("neft / imps / rtgs", ("neft", "imps", "rtgs")),
        ("atm / cash", ("atm", "cash wdl", "cash withdrawal")),
        ("card spend", ("pos", "visa", "mastercard", "card")),
        ("cheque", ("cheque", "chq", "check")),
        ("interest / charges", ("interest", "charge", "fee", "gst", "penalty")),
        ("transfer", ("transfer", "tfr", "self")),
    ]
    for label, keys in rules:
        if any(k in d for k in keys):
            return label
    return "other"


def _calculate_bank_statement(
    rows: list[dict[str, Any]],
    validation: dict[str, Any],
    *,
    statement_totals: dict[str, Any] | None = None,
) -> dict[str, Any]:
    # Drop ID-like amounts (UPI RRNs / account numbers) that slipped past extraction
    clean_rows: list[dict[str, Any]] = []
    for r in rows:
        vals = []
        for k in ("debit", "credit", "amount"):
            if r.get(k) is not None:
                try:
                    vals.append(abs(float(r[k])))
                except (TypeError, ValueError):
                    pass
        if vals and max(vals) > 50_000_000:
            continue
        if vals and max(vals) >= 1e9 and all(v == int(v) for v in vals):
            continue
        clean_rows.append(r)
    rows = clean_rows or rows

    money_out = round(sum(_money_out(r) for r in rows), 2)
    money_in = round(sum(_money_in(r) for r in rows), 2)

    # Amount-only rows: treat positive as in, negative as out when no debit/credit cols
    amount_only = [
        r for r in rows
        if r.get("debit") is None and r.get("credit") is None and r.get("amount") is not None
    ]
    if amount_only and money_in == 0 and money_out == 0:
        for r in amount_only:
            amt = float(r["amount"])
            if amt >= 0:
                money_in += amt
            else:
                money_out += abs(amt)
        money_in = round(money_in, 2)
        money_out = round(money_out, 2)

    header = statement_totals or {}
    totals_source = "line_items"
    # Prefer explicit statement header totals (Paytm Total Money Paid / Received)
    if header.get("money_out") is not None or header.get("money_in") is not None:
        if header.get("money_out") is not None:
            money_out = round(float(header["money_out"]), 2)
        if header.get("money_in") is not None:
            money_in = round(float(header["money_in"]), 2)
        totals_source = str(header.get("source") or "statement_header")

    net = round(money_in - money_out, 2)
    dates = [str(r.get("date")).strip() for r in rows if r.get("date")]
    date_start = dates[0] if dates else None
    date_end = dates[-1] if dates else None

    out_rows = sorted(rows, key=_money_out, reverse=True)
    in_rows = sorted(rows, key=_money_in, reverse=True)
    largest_out = out_rows[0] if out_rows and _money_out(out_rows[0]) > 0 else None
    largest_in = in_rows[0] if in_rows and _money_in(in_rows[0]) > 0 else None

    cats = Counter(_rough_category(str(r.get("description") or "")) for r in rows)
    top_categories = [
        {"category": name, "count": count}
        for name, count in cats.most_common(8)
    ]

    txn_count = float(len(rows))
    if header.get("payments_made") is not None or header.get("payments_received") is not None:
        made = int(header.get("payments_made") or 0)
        recv = int(header.get("payments_received") or 0)
        if made + recv > 0:
            txn_count = float(made + recv)

    metrics: dict[str, Any] = {
        "document_kind": "bank_statement",
        "line_count": float(len(rows)),
        "money_in": money_in,
        "money_out": money_out,
        "net_cashflow": net,
        "debit_total": money_out,
        "credit_total": money_in,
        "net": net,
        "transaction_count": txn_count,
        "payments_made": header.get("payments_made"),
        "payments_received": header.get("payments_received"),
        "totals_source": totals_source,
        "date_start": date_start,
        "date_end": date_end,
        "largest_withdrawal": round(_money_out(largest_out), 2) if largest_out else None,
        "largest_withdrawal_desc": (largest_out or {}).get("description") if largest_out else None,
        "largest_deposit": round(_money_in(largest_in), 2) if largest_in else None,
        "largest_deposit_desc": (largest_in or {}).get("description") if largest_in else None,
        "top_categories": top_categories,
        # Explicitly no balance-sheet metrics
        "assets_total": None,
        "liabilities_total": None,
        "equity_total": None,
        "current_ratio": None,
        "debt_to_equity": None,
        "working_capital": None,
    }

    period = ""
    if date_start or date_end:
        period = f" | period {date_start or '?'} → {date_end or '?'}"

    summary = (
        f"Bank statement: {len(rows)} transactions{period} | "
        f"money in {money_in:,.2f} | money out {money_out:,.2f} | net {net:,.2f}"
    )
    if totals_source == "statement_header":
        summary = (
            f"Statement totals (from document header): "
            f"money paid/out {money_out:,.2f} | money received/in {money_in:,.2f} | "
            f"net {net:,.2f}"
        )
        if header.get("payments_made") is not None:
            summary += (
                f" | {header.get('payments_made')} payments made, "
                f"{header.get('payments_received') or 0} received"
            )

    return {
        "ok": True,
        "validation": validation,
        "metrics": metrics,
        "summary": summary,
        "document_kind": "bank_statement",
    }


def calculate_from_statement_totals(
    statement_totals: dict[str, Any],
    *,
    rows: list[dict[str, Any]] | None = None,
) -> dict[str, Any]:
    """Build bank-statement metrics from totals parsed out of the PDF text."""
    rows = rows or []
    validation = validate_rows(rows, document_kind="bank_statement") if rows else {
        "ok": True,
        "issues": [],
        "row_count": 0,
        "debit_total": 0,
        "credit_total": 0,
        "balanced": None,
        "document_kind": "bank_statement",
    }
    return _calculate_bank_statement(rows, validation, statement_totals=statement_totals)


def _calculate_balance_sheet(rows: list[dict[str, Any]], validation: dict[str, Any]) -> dict[str, Any]:
    amounts = [_amount_of(r) for r in rows]
    debits = [float(r["debit"]) for r in rows if r.get("debit") is not None]
    credits = [float(r["credit"]) for r in rows if r.get("credit") is not None]

    total_amount = round(sum(amounts), 2)
    debit_total = round(sum(debits), 2)
    credit_total = round(sum(credits), 2)
    net = round(debit_total - credit_total, 2) if (debits or credits) else total_amount

    assets = 0.0
    liabilities = 0.0
    equity = 0.0
    for r in rows:
        acct = f"{r.get('account') or ''} {r.get('description') or ''}".lower()
        amt = abs(_amount_of(r))
        # Prefer explicit account names; avoid bare "bank" matching every bank-stmt line
        if any(
            k in acct
            for k in (
                "asset", "cash", "receivable", "inventory", "prepaid",
                "equipment", "machinery", "property", "plant", "building",
                "vehicle", "furniture", "goodwill", "investment",
                "bank account", "cash at bank", "cash in bank",
            )
        ) and not any(k in acct for k in ("payable", "loan", "borrow", "overdraft")):
            assets += amt
        elif any(
            k in acct
            for k in (
                "liabilit", "payable", "loan", "debt", "borrow", "accrued",
                "unearned", "overdraft", "mortgage", "bond",
            )
        ):
            liabilities += amt
        elif any(
            k in acct
            for k in (
                "equity", "capital", "retained", "stock", "owner", "shareholder",
                "drawings", "surplus",
            )
        ):
            equity += amt

    if assets == 0 and liabilities == 0 and equity == 0 and debits and credits:
        assets = debit_total
        liabilities_and_equity = credit_total
    else:
        if assets == 0 and debits and (liabilities or equity):
            assets = debit_total
        liabilities_and_equity = round(liabilities + equity, 2)

    current_ratio = round(assets / liabilities, 3) if liabilities else None
    debt_to_equity = round(liabilities / equity, 3) if equity else None
    working_capital = round(assets - liabilities, 2) if (assets or liabilities) else None

    metrics: dict[str, float | None] = {
        "document_kind": "balance_sheet",
        "line_count": float(len(rows)),
        "total_amount": total_amount,
        "debit_total": debit_total,
        "credit_total": credit_total,
        "net": net,
        "average_line": round(total_amount / len(rows), 2) if rows else 0.0,
        "max_line": round(max(amounts), 2) if amounts else 0.0,
        "min_line": round(min(amounts), 2) if amounts else 0.0,
        "assets_total": round(assets, 2) if assets else None,
        "liabilities_total": round(liabilities, 2) if liabilities else None,
        "equity_total": round(equity, 2) if equity else None,
        "liabilities_and_equity": liabilities_and_equity if (assets or liabilities or equity) else None,
        "current_ratio": current_ratio,
        "debt_to_equity": debt_to_equity,
        "working_capital": working_capital,
    }

    extra = []
    if metrics["assets_total"] is not None:
        extra.append(f"assets {metrics['assets_total']:,.2f}")
    if metrics["liabilities_total"] is not None:
        extra.append(f"liabilities {metrics['liabilities_total']:,.2f}")
    if metrics["equity_total"] is not None:
        extra.append(f"equity {metrics['equity_total']:,.2f}")
    if current_ratio is not None:
        extra.append(f"current ratio {current_ratio}")
    if debt_to_equity is not None:
        extra.append(f"D/E {debt_to_equity}")
    if working_capital is not None:
        extra.append(f"working capital {working_capital:,.2f}")

    if debits or credits:
        summary = (
            f"{len(rows)} line items | "
            f"debits {debit_total:,.2f} | credits {credit_total:,.2f} | net {net:,.2f}"
        )
    else:
        summary = (
            f"{len(rows)} line items | "
            "classified balances below (do not use line-sum as assets)"
        )

    if extra:
        summary += " | " + " | ".join(extra)

    return {
        "ok": validation["ok"],
        "validation": validation,
        "metrics": metrics,
        "summary": summary,
        "document_kind": "balance_sheet",
    }


def calculate(
    rows: list[dict[str, Any]],
    *,
    document_kind: str = "unknown",
    statement_totals: dict[str, Any] | None = None,
) -> dict[str, Any]:
    """Deterministic totals — bank statement cashflow or balance-sheet metrics."""
    validation = validate_rows(rows, document_kind=document_kind)
    if not rows and not (statement_totals and (
        statement_totals.get("money_out") is not None or statement_totals.get("money_in") is not None
    )):
        return {
            "ok": False,
            "error": "No rows to calculate.",
            "validation": validation,
            "document_kind": document_kind,
        }

    if document_kind == "bank_statement" or (
        statement_totals
        and (statement_totals.get("money_out") is not None or statement_totals.get("money_in") is not None)
    ):
        return _calculate_bank_statement(
            rows, validation, statement_totals=statement_totals
        )
    return _calculate_balance_sheet(rows, validation)


def format_job_context(job: dict[str, Any]) -> str:
    """Context for the chat LLM: document text + supporting figures."""
    rows = job.get("rows") or []
    calc = job.get("calculation") or {}
    metrics = calc.get("metrics") or {}
    validation = calc.get("validation") or job.get("validation") or {}
    doc_text = (job.get("document_text") or job.get("text_preview") or "").strip()
    kind = (
        job.get("document_kind")
        or calc.get("document_kind")
        or metrics.get("document_kind")
        or "unknown"
    )

    lines = [
        f"Filename: {job.get('filename')}",
        f"Document type: {kind}",
        f"Extraction: {job.get('source_type')} | OCR={job.get('ocr_used')}",
        "",
    ]

    if kind == "bank_statement":
        lines.append("=== BANK STATEMENT SUMMARY (prefer these totals for cashflow questions) ===")
        lines.append(calc.get("summary") or "(no summary yet)")
        if metrics.get("money_in") is not None:
            lines.append(f"Total money in (deposits/credits/received): {metrics.get('money_in')}")
        if metrics.get("money_out") is not None:
            lines.append(f"Total money out (withdrawals/debits/spent/paid): {metrics.get('money_out')}")
        if metrics.get("net_cashflow") is not None:
            lines.append(f"Net cashflow (in − out): {metrics.get('net_cashflow')}")
        if metrics.get("totals_source"):
            lines.append(f"Totals source: {metrics.get('totals_source')}")
        if metrics.get("payments_made") is not None:
            lines.append(
                f"Payments made: {metrics.get('payments_made')}; "
                f"payments received: {metrics.get('payments_received')}"
            )
        if metrics.get("date_start") or metrics.get("date_end"):
            lines.append(
                f"Date range seen: {metrics.get('date_start') or '?'} → {metrics.get('date_end') or '?'}"
            )
        if metrics.get("largest_deposit") is not None:
            lines.append(
                f"Largest deposit: {metrics.get('largest_deposit')} "
                f"({metrics.get('largest_deposit_desc') or ''})"
            )
        if metrics.get("largest_withdrawal") is not None:
            lines.append(
                f"Largest withdrawal: {metrics.get('largest_withdrawal')} "
                f"({metrics.get('largest_withdrawal_desc') or ''})"
            )
        cats = metrics.get("top_categories") or []
        if cats:
            lines.append(
                "Rough narration categories: "
                + ", ".join(f"{c['category']}×{c['count']}" for c in cats[:6])
            )
        lines.append("")
        lines.append("=== SAMPLE TRANSACTIONS (first 25 + last 25) ===")
        sample = rows[:25]
        if len(rows) > 50:
            sample = rows[:25] + [{"description": f"… {len(rows) - 50} more rows …"}] + rows[-25:]
        elif len(rows) > 25:
            sample = rows
        for r in sample:
            if not isinstance(r, dict):
                continue
            if str(r.get("description") or "").startswith("…"):
                lines.append(f"- {r.get('description')}")
                continue
            lines.append(
                f"- {r.get('date') or ''} | {r.get('description') or '?'} | "
                f"out={r.get('debit')} | in={r.get('credit')} | amount={r.get('amount')}"
            )
        lines.append("")
        lines.append("=== PDF DOCUMENT TEXT (may be truncated for long statements) ===")
        # Cap text for model context — summary + samples carry the numbers
        if doc_text:
            lines.append(doc_text[:8000])
        else:
            lines.append("(No readable text extracted.)")
        lines.append("")
        lines.append(
            "Answer using the BANK STATEMENT SUMMARY for totals. "
            "For 'how much spent/paid', use money out. For 'received/deposited', use money in. "
            "Do not invent balance-sheet ratios (current ratio, D/E, assets/liabilities)."
        )
        return "\n".join(lines)

    # Balance sheet / unknown (legacy path)
    lines.append("=== PDF DOCUMENT TEXT (read this carefully; answer from what is written here) ===")
    if doc_text:
        lines.append(doc_text[:12000])
    else:
        lines.append("(No readable text extracted from the PDF pages.)")

    lines.append("")
    lines.append("=== SUPPORTING EXTRACTED LINE ITEMS ===")
    for r in rows[:60]:
        lines.append(
            f"- {r.get('description') or '?'} | account={r.get('account') or '-'} | "
            f"debit={r.get('debit')} | credit={r.get('credit')} | amount={r.get('amount')}"
        )
    if not rows:
        lines.append("(No structured line items.)")

    lines.append("")
    lines.append("=== FIGURE CHECKS (for your reasoning only — do not copy this format to the user) ===")
    if validation.get("balanced") is False:
        lines.append(
            f"Sheet does not balance: debits {validation.get('debit_total')} vs "
            f"credits {validation.get('credit_total')}."
        )
    elif validation.get("balanced") is True:
        lines.append("Debits equal credits.")
    if metrics.get("assets_total") is not None:
        lines.append(f"Assets come to {metrics.get('assets_total')}.")
    if metrics.get("liabilities_total") is not None:
        lines.append(f"Liabilities come to {metrics.get('liabilities_total')}.")
    if metrics.get("equity_total") is not None:
        lines.append(f"Equity comes to {metrics.get('equity_total')}.")
    if metrics.get("current_ratio") is not None:
        lines.append(f"Current ratio is about {metrics.get('current_ratio')}.")
    if metrics.get("debt_to_equity") is not None:
        lines.append(f"Debt-to-equity is about {metrics.get('debt_to_equity')}.")
    if metrics.get("working_capital") is not None:
        lines.append(f"Working capital is about {metrics.get('working_capital')}.")
    if job.get("ocr_used"):
        lines.append("Text may include OCR draft values.")
    return "\n".join(lines)


_PDF_OVERVIEW_RE = re.compile(
    r"(?i)^\s*("
    r"what\s+is\s+(this|the|my)\s+(pdf|file|document|statement|bank\s+statement|balance\s*sheet|sheet|knowledge\s*base)(\s+about)?"
    r"|what('?s|\s+is)\s+(this|it)\s+about"
    r"|what\s+does\s+(this|the|my)\s+(pdf|file|document|statement|bank\s+statement|knowledge\s*base)\s+(contain|show|say|have)"
    r"|describe\s+(this|the|my)\s+(pdf|file|document|statement|bank\s+statement|balance\s*sheet|knowledge\s*base)?"
    r"|summarize\s+(this|the|my)\s+(pdf|file|document|statement|bank\s+statement|balance\s*sheet|knowledge\s*base)?"
    r"|analy[sz]e\s+(this|the|my)?\s*(pdf|file|docu?m?e?n?t|statement|bank\s+statement|upload|uploads)?"
    r"|analy[sz]e\s+(it|this)"
    r"|(give\s+me\s+)?(an?\s+)?(overview|summary)\s+(of\s+)?(this|the|my)?\s*(pdf|file|document|statement|bank\s+statement|knowledge\s*base)?"
    r"|tell\s+me\s+about\s+(this|the|my)\s+(pdf|file|document|statement|bank\s+statement|balance\s*sheet|knowledge\s*base)?"
    r"|tell\s+me\s+about\s+(the\s+)?knowledge\s*base"
    r"|what('?s|\s+is)\s+in\s+(my|the)\s+knowledge\s*base"
    r"|tell\s+me\s+about\s+my\s+(finances?|upi|payments?|transactions?)"
    r")\s*[?.!]?\s*$"
)

# Category filters — only match when the user names a spend category (not whole-statement totals).
_CATEGORY_KEYS: list[tuple[str, tuple[str, ...]]] = [
    (
        "groceries",
        (
            "grocery",
            "groceries",
            "# groceries",
            "zepto",
            "instamart",
            "bigbasket",
            "blinkit",
            "country delight",
            "beejapuri",
            "bbdaily",
            "milkbasket",
            "dunzo",
            "jiomart",
            "nature's basket",
            "natures basket",
        ),
    ),
    ("food", ("# food", "zomato", "swiggy", "restaurant", "food delivery")),
    ("taxi", ("# taxi", "uber", "ola", "taxi", "cab")),
    ("bills", ("# bill", "bill payment", "recharge", "jio", "airtel", "electricity")),
    ("transfers", ("money transfer", "money sent", "self transfer", "# money transfer")),
]

_SPEND_Q_RE = re.compile(
    r"(?i)\b("
    r"how\s+much.*(spent|spend|paid|payment|debit|withdrawn|withdrawal|outflow|money\s+out|total\s+out)"
    r"|total\s+(spent|spend|paid|payments?|debits?|withdrawals?|money\s+paid|money\s+out)"
    r"|(spent|paid)\s+in\s+total"
    r"|total\s+amount\s+(spent|paid)"
    r"|money\s+paid"
    r")\b"
)
_RECEIVED_Q_RE = re.compile(
    r"(?i)\b("
    r"how\s+much.*(received|credited|deposit|money\s+in|inflow|total\s+in)"
    r"|total\s+(received|credits?|deposits?|money\s+received|money\s+in)"
    r"|money\s+received"
    r")\b"
)
_NET_Q_RE = re.compile(
    r"(?i)\b(net\s+(cashflow|cash\s*flow|amount|total)|how\s+much\s+(left|net)|difference\s+between)\b"
)
_TXN_COUNT_Q_RE = re.compile(
    r"(?i)\b("
    r"how\s+many\s+(transactions?|payments?|txns?)(?:\s+\w+){0,4}"
    r"|transactions?\s+(are\s+)?made"
    r"|payments?\s+(are\s+)?made"
    r"|transaction\s+count"
    r"|number\s+of\s+(transactions?|payments?)"
    r")\b"
)


def is_pdf_overview_question(question: str) -> bool:
    return bool(_PDF_OVERVIEW_RE.match((question or "").strip()))


# Merchant aliases that live under a category but should narrow when named alone.
_MERCHANT_ALIAS_KEYS: list[tuple[str, str, tuple[str, ...]]] = []
for _label, _keys in _CATEGORY_KEYS:
    for _k in _keys:
        _kk = _k.lstrip("# ").lower().strip()
        if (
            not _kk
            or len(_kk) <= 3
            or _kk in {
                "grocery",
                "groceries",
                "food",
                "taxi",
                "cab",
                "bills",
                "bill",
                "transfers",
                "transfer",
                "recharge",
                "restaurant",
                "food delivery",
                "bill payment",
                "money transfer",
                "money sent",
                "self transfer",
            }
            or _kk.startswith(_label)
        ):
            continue
        _MERCHANT_ALIAS_KEYS.append((_kk, _label, (_kk,)))


def _named_merchant_alias(question: str) -> tuple[str, tuple[str, ...]] | None:
    """If the user names a specific merchant alias (Zepto, Blinkit, …), return that only."""
    q = (question or "").lower()
    hits: list[tuple[str, tuple[str, ...]]] = []
    for alias, _label, keys in _MERCHANT_ALIAS_KEYS:
        if re.search(rf"(?<![a-z0-9]){re.escape(alias)}(?![a-z0-9])", q):
            hits.append((alias, keys))
    if len(hits) == 1:
        alias, keys = hits[0]
        return alias, keys
    return None


def _detect_spend_category(question: str) -> tuple[str, tuple[str, ...]] | None:
    q = (question or "").lower()
    # Prefer merchant-only when the user names Zepto/Blinkit/Jio/etc.
    narrowed = _named_merchant_alias(question)
    if narrowed:
        return narrowed
    for label, keys in _CATEGORY_KEYS:
        # Require the category word itself (or a strong alias) in the question.
        # Skip merchant aliases here — those are handled by _named_merchant_alias.
        alias_set = {a for a, _, _ in _MERCHANT_ALIAS_KEYS}
        if label in q or any(
            (k.lstrip("# ").lower() in q and k.lstrip("# ").lower() not in alias_set)
            for k in keys
            if len(k) > 3
        ):
            # Avoid matching generic "food" inside unrelated words — keys already specific.
            if label == "food" and "grocery" in q:
                continue
            return label, keys
    # Explicit "on grocery/groceries"
    m = re.search(r"(?i)\bon\s+(grocer(?:y|ies)|food|taxi|bills?|transfers?)\b", q)
    if m:
        word = m.group(1).lower()
        if word.startswith("grocer"):
            return "groceries", _CATEGORY_KEYS[0][1]
        for label, keys in _CATEGORY_KEYS:
            if label.startswith(word) or word.startswith(label):
                return label, keys
    return None


def _row_blob(row: dict[str, Any]) -> str:
    return " ".join(
        str(row.get(k) or "") for k in ("description", "raw", "account", "date")
    ).lower()


def _row_matches_keys(row: dict[str, Any], keys: tuple[str, ...]) -> bool:
    blob = _row_blob(row)
    for key in keys:
        k = key.lower().strip()
        if not k:
            continue
        if k.startswith("#"):
            if k in blob:
                return True
            continue
        # Word-ish match so short tokens don't hit random substrings
        if re.search(rf"(?<![a-z0-9]){re.escape(k)}(?![a-z0-9])", blob):
            return True
    return False


def answer_category_spend_question(job: dict[str, Any], question: str) -> str | None:
    """Sum spend for a named category from extracted statement rows — never the whole-statement total."""
    cat = _detect_spend_category(question)
    if not cat:
        return None
    q = question.lower()
    if not (
        _SPEND_Q_RE.search(question)
        or re.search(r"(?i)\b(spent|spend|paid|cost|expense|expenses)\b", q)
    ):
        return None

    label, keys = cat
    rows = job.get("rows") or []
    matched = [r for r in rows if _row_matches_keys(r, keys) and _money_out(r) > 0]
    cur = _detect_currency_symbol(job)
    if not matched:
        # Fallback: scan raw document text for tagged lines (table extract may miss some).
        text = job.get("document_text") or ""
        total = 0.0
        hits = 0
        for line in text.splitlines():
            low = line.lower()
            if not any(k.lower() in low for k in keys):
                continue
            # Prefer signed outflows on the line
            for m in re.finditer(r"(?i)-\s*(?:₹|rs\.?)\s*([\d,]+\.?\d*)", line):
                try:
                    total += float(m.group(1).replace(",", ""))
                    hits += 1
                except ValueError:
                    pass
        if hits:
            return (
                f"From the uploaded statement text, I found about {hits} {label} outflow line(s) "
                f"totaling {_pretty_money(round(total, 2), cur)}. "
                f"This is not the full-statement total."
            )
        return (
            f"I couldn't find {label} transactions in the extracted lines from "
            f"“{job.get('filename')}”. Try asking about a merchant name that appears on the statement."
        )

    total = round(sum(_money_out(r) for r in matched), 2)
    sample = []
    for r in matched[:5]:
        desc = str(r.get("description") or "payment").split("\n")[0][:60]
        sample.append(f"{desc} ({_pretty_money(_money_out(r), cur)})")
    extra = ""
    if sample:
        extra = " Examples: " + "; ".join(sample) + "."
    more = f" (+{len(matched) - 5} more)" if len(matched) > 5 else ""
    return (
        f"Spent on {label} in “{job.get('filename')}”: {_pretty_money(total, cur)} "
        f"across {len(matched)} payment(s){more}.{extra}"
    )


def answer_statement_metric_question(job: dict[str, Any], question: str) -> str | None:
    """Deterministic answers for spend/received/net/balance questions — values from the PDF only."""
    calc = job.get("calculation") or {}
    metrics = calc.get("metrics") or {}
    kind = (
        job.get("document_kind")
        or calc.get("document_kind")
        or metrics.get("document_kind")
        or ""
    )
    header = job.get("statement_totals") or {}
    money_out = metrics.get("money_out")
    money_in = metrics.get("money_in")
    if money_out is None and header.get("money_out") is not None:
        money_out = header.get("money_out")
    if money_in is None and header.get("money_in") is not None:
        money_in = header.get("money_in")

    q = (question or "").strip()
    if not q:
        return None

    if is_pdf_overview_question(q):
        return describe_pdf_job(job)

    # Category spend must run before whole-statement spend totals.
    cat_ans = answer_category_spend_question(job, q)
    if cat_ans:
        return cat_ans

    # Whole-statement totals only when the user did NOT name a category.
    if _detect_spend_category(q) and _SPEND_Q_RE.search(q):
        return None

    # Per-date / daily spend is handled in kb_answer.answer_spend_by_date.
    if re.search(
        r"(?i)\b(each|every|per|all)\s+(date|day)\b|\bdate[\s-]?wise\b|\bday[\s-]?wise\b|\bdaily\s+spend\b",
        q,
    ):
        return None

    cur = _detect_currency_symbol(job)
    out_s = _pretty_money(money_out, cur)
    in_s = _pretty_money(money_in, cur)
    net_val = None
    if money_in is not None and money_out is not None:
        try:
            net_val = float(money_in) - float(money_out)
        except (TypeError, ValueError):
            net_val = metrics.get("net_cashflow")
    elif metrics.get("net_cashflow") is not None:
        net_val = metrics.get("net_cashflow")
    net_s = _pretty_money(net_val, cur)

    if kind in {"bank_statement", "unknown"} or out_s or in_s:
        if _SPEND_Q_RE.search(q) and out_s:
            extra = ""
            if metrics.get("payments_made") is not None:
                extra = f" across {metrics.get('payments_made')} payments"
            elif header.get("payments_made") is not None:
                extra = f" across {header.get('payments_made')} payments"
            return f"Total spent (money paid) on this statement is {out_s}{extra}."

        if _RECEIVED_Q_RE.search(q) and in_s:
            extra = ""
            if metrics.get("payments_received") is not None:
                extra = f" across {metrics.get('payments_received')} payments"
            elif header.get("payments_received") is not None:
                extra = f" across {header.get('payments_received')} payments"
            return f"Total received (money in) on this statement is {in_s}{extra}."

        if _NET_Q_RE.search(q) and net_s:
            in_part = _pretty_money(money_in, cur)
            out_part = _pretty_money(money_out, cur)
            if in_part and out_part:
                return (
                    f"Net cashflow is {net_s} "
                    f"(received {in_part} − paid {out_part})."
                )
            return f"Net cashflow is {net_s}."

        if _TXN_COUNT_Q_RE.search(q):
            made = metrics.get("payments_made")
            if made is None:
                made = header.get("payments_made")
            recv = metrics.get("payments_received")
            if recv is None:
                recv = header.get("payments_received")
            bits = []
            if made is not None:
                bits.append(f"{made} payments made")
            if recv is not None:
                bits.append(f"{recv} payments received")
            if bits:
                return f"This statement shows {' and '.join(bits)}."
            rows = job.get("rows") or []
            if rows:
                return f"I extracted {len(rows)} transactions from this statement."

        if re.search(r"(?i)\b(total|totals|cashflow|cash\s*flow|summary)\b", q) and (out_s or in_s):
            bits = []
            if out_s:
                bits.append(f"spent/paid {out_s}")
            if in_s:
                bits.append(f"received {in_s}")
            if net_s:
                bits.append(f"net {net_s}")
            return "Statement totals: " + "; ".join(bits) + "."

    if kind == "balance_sheet" or metrics.get("assets_total") is not None:
        assets = _pretty_money(metrics.get("assets_total"), cur)
        liab = _pretty_money(metrics.get("liabilities_total"), cur)
        equity = _pretty_money(metrics.get("equity_total"), cur)
        if re.search(r"(?i)\bassets?\b", q) and assets:
            return f"Total assets on this statement are {assets}."
        if re.search(r"(?i)\bliabilit", q) and liab:
            return f"Total liabilities on this statement are {liab}."
        if re.search(r"(?i)\bequity|capital\b", q) and equity:
            return f"Total equity on this statement is {equity}."
        if re.search(r"(?i)\bcurrent\s+ratio\b", q) and metrics.get("current_ratio") is not None:
            return f"Current ratio is {metrics.get('current_ratio')}."
        if re.search(r"(?i)\b(d/e|debt[\s-]*to[\s-]*equity)\b", q) and metrics.get("debt_to_equity") is not None:
            return f"Debt-to-equity is {metrics.get('debt_to_equity')}."

    return None


def _pretty_money(value: object, currency: str = "") -> str | None:
    if value is None:
        return None
    try:
        num = f"{float(value):,.2f}"
    except (TypeError, ValueError):
        return None
    prefix = f"{currency} " if currency else ""
    return f"{prefix}{num}"


def _detect_currency_symbol(job: dict[str, Any]) -> str:
    blob = f"{job.get('document_text') or ''} {job.get('text_preview') or ''}"[:3000]
    if "₹" in blob or re.search(r"(?i)\b(?:rs\.?|inr)\b", blob):
        return "₹"
    if "$" in blob or re.search(r"(?i)\busd\b", blob):
        return "$"
    if "€" in blob or re.search(r"(?i)\beur\b", blob):
        return "€"
    if "£" in blob or re.search(r"(?i)\bgbp\b", blob):
        return "£"
    return ""


def describe_pdf_job(job: dict[str, Any]) -> str:
    """Human overview of an uploaded statement — no LLM. Values come only from the PDF extract."""
    filename = str(job.get("filename") or "this PDF")
    rows = job.get("rows") or []
    calc = job.get("calculation") or {}
    metrics = calc.get("metrics") or {}
    header = job.get("statement_totals") or {}
    validation = calc.get("validation") or job.get("validation") or {}
    kind = job.get("document_kind") or calc.get("document_kind") or metrics.get("document_kind")
    cur = _detect_currency_symbol(job)

    if kind == "bank_statement":
        money_in = _pretty_money(metrics.get("money_in"), cur)
        money_out = _pretty_money(metrics.get("money_out"), cur)
        net = _pretty_money(metrics.get("net_cashflow"), cur)
        made = metrics.get("payments_made")
        if made is None:
            made = header.get("payments_made")
        recv = metrics.get("payments_received")
        if recv is None:
            recv = header.get("payments_received")

        parts = [f"“{filename}” is a bank / wallet statement."]
        count_bits = []
        if made is not None:
            count_bits.append(f"{made} payments made")
        if recv is not None:
            count_bits.append(f"{recv} payments received")
        if count_bits:
            parts.append("It lists " + " and ".join(count_bits) + ".")
        elif rows:
            parts.append(f"I extracted {len(rows)} transaction lines from it.")

        bits = []
        if money_out:
            bits.append(f"money paid/out {money_out}")
        if money_in:
            bits.append(f"money received/in {money_in}")
        if net:
            bits.append(f"net {net}")
        if bits:
            parts.append("Totals from the document: " + ", ".join(bits) + ".")

        # Prefer a clean period from document text when available
        text = (job.get("document_text") or job.get("text_preview") or "")[:2000]
        period = re.search(
            r"(?i)(\d{1,2}\s+[A-Z]{3}'?\d{2,4}\s*[-–—]\s*\d{1,2}\s+[A-Z]{3}'?\d{2,4})",
            text,
        )
        if period:
            parts.append(f"Period on the statement: {period.group(1)}.")
        elif metrics.get("date_start") or metrics.get("date_end"):
            start = str(metrics.get("date_start") or "?").split("\n")[0].strip()
            end = str(metrics.get("date_end") or "?").split("\n")[0].strip()
            parts.append(f"Dates seen in lines: {start} → {end}.")

        parts.append(
            "You can ask how much was spent or received, how many payments were made, or about a specific month."
        )
        return " ".join(parts)

    assets = _pretty_money(metrics.get("assets_total"), cur)
    liab = _pretty_money(metrics.get("liabilities_total"), cur)
    equity = _pretty_money(metrics.get("equity_total"), cur)
    cr = metrics.get("current_ratio")
    de = metrics.get("debt_to_equity")
    wc = _pretty_money(metrics.get("working_capital"), cur)
    debit_total = _pretty_money(validation.get("debit_total") or metrics.get("debit_total"), cur)
    credit_total = _pretty_money(validation.get("credit_total") or metrics.get("credit_total"), cur)

    parts: list[str] = []

    if validation.get("balanced") is False:
        issue = ""
        if debit_total and credit_total:
            issue = f" Debits are {debit_total} but credits are {credit_total}."
        parts.append(
            f"“{filename}” is an unbalanced balance sheet with {len(rows)} line items."
            f"{issue}"
        )
    else:
        parts.append(f"“{filename}” is a balance sheet with {len(rows)} line items.")

    balance_bits = []
    if assets:
        balance_bits.append(f"assets {assets}")
    if liab:
        balance_bits.append(f"liabilities {liab}")
    if equity:
        balance_bits.append(f"equity {equity}")
    if balance_bits:
        parts.append("Key balances: " + ", ".join(balance_bits) + ".")

    ratio_bits = []
    if cr is not None:
        ratio_bits.append(f"current ratio {cr}")
    if de is not None:
        ratio_bits.append(f"D/E {de}")
    if wc:
        ratio_bits.append(f"working capital {wc}")
    if ratio_bits:
        parts.append("Ratios: " + ", ".join(ratio_bits) + ".")

    if validation.get("balanced") is True:
        parts.append("The debit and credit columns balance.")

    return " ".join(parts)