| """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,
|
| )
|
|
|
|
|
|
|
| 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],
|
| )
|
| ],
|
| )
|
|
|
|
|
|
|
|
|
| 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:
|
| plan = None
|
| if plan is not None:
|
| return plan
|
| return _try_overview(question)
|
|
|
