"""Offline deterministic agent — works without DeepSeek keys.

Pattern-matches the question to a SQL template + UI Plan. Always produces a
valid UIPlan with citations grounded in DuckDB results.
"""
from __future__ import annotations

import re
from typing import Any

from ..retrieval.duck import run_sql
from ..ui_plan.schema import (
    Citation,
    Component,
    Encoding,
    GridCell,
    Layout,
    Persona,
    UIPlan,
)

# ---- helpers ----------------------------------------------------------------

ACCENT = "#F47C2C"


def _kpi(id_: str, title: str, value: float | str, sub: str = "") -> Component:
    return Component(
        id=id_,
        type="kpi",
        title=title,
        subtitle=sub,
        data={"value": value, "format": "currency_inr" if isinstance(value, (int, float)) else "text"},
    )


def _table(id_: str, title: str, rows: list[dict[str, Any]]) -> Component:
    return Component(id=id_, type="data-table", title=title, data=rows[:200])


def _bar(id_: str, title: str, rows: list[dict[str, Any]],
         x: str, y: str) -> Component:
    return Component(
        id=id_,
        type="bar",
        title=title,
        data=rows,
        encoding=Encoding(x=x, y=y, color=ACCENT),
        why=f"Bar chart compares {y} across {x}.",
    )


def _line(id_: str, title: str, rows: list[dict[str, Any]],
          x: str, y: str) -> Component:
    return Component(
        id=id_,
        type="line",
        title=title,
        data=rows,
        encoding=Encoding(x=x, y=y, color=ACCENT),
        why=f"Line chart traces {y} over {x}.",
    )


def _wrap(question: str, narrative: str, components: list[Component],
          source_table: str, sql: str) -> UIPlan:
    grid: list[GridCell] = []
    y = 0
    for i, c in enumerate(components):
        if c.type == "kpi":
            grid.append(GridCell(id=f"g{i}", component=c.id, w=4, h=2, x=(i % 3) * 4, y=0))
        else:
            grid.append(GridCell(id=f"g{i}", component=c.id, w=12, h=5, x=0, y=y or 2))
            y += 5
    return UIPlan(
        persona=Persona(name="analyst", accent=ACCENT, mood="investigative"),
        layout=Layout(grid=grid),
        components=components,
        narrative=narrative,
        citations=[
            Citation(
                id="cite1",
                source=source_table,
                snippet=sql[:280],
            )
        ],
    )


# ---- intent matchers --------------------------------------------------------

def _try_top_funders(q: str) -> UIPlan | None:
    if not re.search(r"\b(top|biggest|largest)\b.*\bfunder", q, re.I):
        return None
    sql = """
        SELECT funder_name,
               ROUND(SUM(actual_total)::DOUBLE, 2) AS actual_inr
        FROM bva
        WHERE funder_name IS NOT NULL AND funder_name <> 'NONE'
        GROUP BY funder_name
        ORDER BY actual_inr DESC NULLS LAST
        LIMIT 10
    """
    res = run_sql(sql)
    if res.error or not res.rows:
        return None
    top = res.rows[0]
    grand = sum(float(r["actual_inr"] or 0) for r in res.rows)
    comps = [
        _kpi("k1", "Top funder",
             top["funder_name"], f"₹{top['actual_inr']:,.0f}"),
        _kpi("k2", "Top-10 funders total", grand, f"FY26 actual"),
        _kpi("k3", "Funders shown", len(res.rows), "rows"),
        _bar("c1", "Top 10 funders by FY26 actual spend",
             res.rows, x="funder_name", y="actual_inr"),
        _table("t1", "Detail", res.rows),
    ]
    narrative = (
        f"Across the top 10 funders, **{top['funder_name']}** leads at "
        f"₹{float(top['actual_inr']):,.0f}. Combined top-10 actual is "
        f"₹{grand:,.0f}, sourced from the `bva` ledger."
    )
    return _wrap(q, narrative, comps, "bva", sql)


def _try_monthly_burn(q: str) -> UIPlan | None:
    if not re.search(r"\b(monthly|month|trend|burn|over time)\b", q, re.I):
        return None
    cols = [f"actual_{m}" for m in [
        "01042025", "01052025", "01062025", "01072025", "01082025", "01092025",
        "01102025", "01112025", "01122025", "01012026", "01022026", "01032026"]]
    months = ["Apr-25", "May-25", "Jun-25", "Jul-25", "Aug-25", "Sep-25",
              "Oct-25", "Nov-25", "Dec-25", "Jan-26", "Feb-26", "Mar-26"]
    union = " UNION ALL ".join(
        f"SELECT '{m}' AS month, {i:02d} AS ord, "
        f"ROUND(SUM({c})::DOUBLE,2) AS actual_inr FROM bva"
        for i, (m, c) in enumerate(zip(months, cols), 1)
    )
    sql = f"SELECT * FROM ({union}) ORDER BY ord"
    res = run_sql(sql)
    if res.error or not res.rows:
        return None
    total = sum(float(r["actual_inr"] or 0) for r in res.rows)
    peak = max(res.rows, key=lambda r: float(r["actual_inr"] or 0))
    comps = [
        _kpi("k1", "FY26 actual (Apr–Mar)", total, "from bva monthly cols"),
        _kpi("k2", "Peak month", peak["month"], f"₹{peak['actual_inr']:,.0f}"),
        _kpi("k3", "Months", len(res.rows), "covered"),
        _line("c1", "Monthly burn — FY26",
              res.rows, x="month", y="actual_inr"),
        _table("t1", "Monthly detail", res.rows),
    ]
    narrative = (
        f"The FY26 monthly burn totals ₹{total:,.0f}, peaking in "
        f"**{peak['month']}** at ₹{float(peak['actual_inr']):,.0f}. "
        f"Trend is computed from the per-month `actual_*` columns of `bva`."
    )
    return _wrap(q, narrative, comps, "bva", sql)


def _try_top_categories(q: str) -> UIPlan | None:
    if not re.search(r"\bcategor", q, re.I):
        return None
    sql = """
        SELECT category_name,
               ROUND(SUM(actual_total)::DOUBLE, 2) AS actual_inr
        FROM bva
        WHERE category_name IS NOT NULL
        GROUP BY category_name
        ORDER BY actual_inr DESC NULLS LAST
        LIMIT 15
    """
    res = run_sql(sql)
    if res.error or not res.rows:
        return None
    comps = [
        _kpi("k1", "Top category", res.rows[0]["category_name"],
             f"₹{res.rows[0]['actual_inr']:,.0f}"),
        _bar("c1", "Top 15 spend categories", res.rows,
             x="category_name", y="actual_inr"),
        _table("t1", "Detail", res.rows),
    ]
    return _wrap(
        q,
        f"Top spending category is **{res.rows[0]['category_name']}** "
        f"at ₹{float(res.rows[0]['actual_inr']):,.0f}.",
        comps, "bva", sql)


def _try_open_po(q: str) -> UIPlan | None:
    if not re.search(r"\b(open\s*po|purchase order|po\b|vendor)\b", q, re.I):
        return None
    sql = "SELECT * FROM open_po LIMIT 200"
    res = run_sql(sql)
    if res.error or not res.rows:
        return None
    comps = [
        _kpi("k1", "Open POs", len(res.rows), "rows"),
        _table("t1", "Open Purchase Orders", res.rows),
    ]
    return _wrap(q, f"{len(res.rows)} open purchase orders found.",
                 comps, "open_po", sql)


def _try_overview(q: str) -> UIPlan:
    """Always-on default overview."""
    funders = run_sql("SELECT COUNT(DISTINCT funder_name) AS n FROM bva "
                      "WHERE funder_name IS NOT NULL AND funder_name <> 'NONE'")
    projects = run_sql("SELECT COUNT(DISTINCT project_id) AS n FROM bva")
    grand = run_sql("SELECT ROUND(SUM(actual_total)::DOUBLE,2) AS v FROM bva")
    region = run_sql("""
        SELECT COALESCE(region,'(unspecified)') AS region,
               ROUND(SUM(actual_total)::DOUBLE,2) AS actual_inr
        FROM bva
        GROUP BY region
        ORDER BY actual_inr DESC NULLS LAST
        LIMIT 12
    """)
    fcount = funders.rows[0]["n"] if funders.rows else 0
    pcount = projects.rows[0]["n"] if projects.rows else 0
    gv = grand.rows[0]["v"] if grand.rows else 0
    comps = [
        _kpi("k1", "Distinct funders", fcount, "in BVA ledger"),
        _kpi("k2", "Distinct projects", pcount, "tracked"),
        _kpi("k3", "FY26 actual (gross)", gv, "₹"),
        _bar("c1", "Spend by region", region.rows,
             x="region", y="actual_inr"),
        _table("t1", "Region breakdown", region.rows),
    ]
    return _wrap(q,
                 f"BVA spans **{fcount} funders** and **{pcount} projects** with "
                 f"a gross actual of ₹{float(gv):,.0f} for FY26.",
                 comps, "bva", region.sql)


MATCHERS = [
    _try_top_funders,
    _try_monthly_burn,
    _try_top_categories,
    _try_open_po,
]


def offline_plan(question: str) -> UIPlan:
    for fn in MATCHERS:
        try:
            plan = fn(question)
        except Exception:  # noqa: BLE001
            plan = None
        if plan is not None:
            return plan
    return _try_overview(question)