from __future__ import annotations

from datetime import date
from decimal import Decimal
from sqlite3 import Connection

from jarvis_finance.services.budget_common import row_to_dict
from jarvis_finance.services.budget_transactions import list_budget_transactions


def _current_month(conn: Connection) -> str:
    tx = conn.execute("SELECT substr(MAX(transaction_date),1,7) AS month FROM budget_transactions").fetchone()
    if tx and tx["month"]:
        return tx["month"]
    plan = conn.execute("SELECT MIN(plan_month) AS month FROM budget_plan_items WHERE is_active=1").fetchone()
    if plan and plan["month"]:
        return plan["month"]
    return conn.execute("SELECT substr(date('now'),1,7)").fetchone()[0]


def _amount(row) -> Decimal:
    return Decimal(str(row["amount_chf"] or row["amount_original"] or "0"))


def _monthly_plan_amount(plan) -> Decimal:
    if plan["monthly_amount_chf"]:
        return Decimal(str(plan["monthly_amount_chf"]))
    if plan["annual_amount_chf"]:
        return Decimal(str(plan["annual_amount_chf"])) / Decimal("12")
    return Decimal("0")


def _annual_plan_amount(plan) -> Decimal:
    if plan["annual_amount_chf"]:
        return Decimal(str(plan["annual_amount_chf"]))
    if plan["monthly_amount_chf"]:
        return Decimal(str(plan["monthly_amount_chf"])) * Decimal("12")
    return Decimal("0")


def _fmt(value: Decimal) -> str:
    return format(value.quantize(Decimal("0.01")), "f")


def _category_meta(conn: Connection) -> dict[str, dict]:
    rows = conn.execute("SELECT category_id, parent_category_id, name, category_type, sort_order FROM budget_categories WHERE is_active=1").fetchall()
    return {str(r["category_id"]): row_to_dict(r) for r in rows}


def _ancestor_ids(category_id: str | None, meta: dict[str, dict]) -> list[str]:
    if not category_id or category_id not in meta:
        return []
    out: list[str] = []
    parent = meta[category_id].get("parent_category_id")
    seen: set[str] = set()
    while parent and parent in meta and parent not in seen:
        out.append(str(parent))
        seen.add(str(parent))
        parent = meta[str(parent)].get("parent_category_id")
    return out


def get_monthly_summary(conn: Connection, month: str | None = None) -> dict:
    selected_month = month or _current_month(conn)
    rows = conn.execute("SELECT * FROM budget_transactions WHERE status='confirmed' AND substr(transaction_date,1,7)=?", (selected_month,)).fetchall()
    income = Decimal("0")
    expense = Decimal("0")
    for row in rows:
        amount = _amount(row)
        if row["transaction_type"] in {"income", "refund"}:
            income += amount
        elif row["transaction_type"] in {"expense", "fee"}:
            expense += amount
    return {"month": selected_month, "income_chf": _fmt(income), "expense_chf": _fmt(expense), "net_cashflow_chf": _fmt(income - expense), "transaction_count": len(rows)}


def get_category_summary(conn: Connection, month: str | None = None) -> list[dict]:
    selected_month = month or _current_month(conn)
    rows = conn.execute(
        """
        SELECT COALESCE(c.category_id, 'uncategorized') AS summary_category_id,
               COALESCE(c.name, 'Unkategorisiert') AS summary_category_name,
               t.transaction_type,
               t.amount_chf,
               t.amount_original,
               COUNT(*) AS count
        FROM budget_transactions t
        LEFT JOIN budget_categories c ON c.category_id=t.category_id
        WHERE t.status='confirmed' AND substr(t.transaction_date,1,7)=?
          AND t.transaction_type IN ('income','expense','refund','fee')
        GROUP BY COALESCE(c.category_id, 'uncategorized'), COALESCE(c.name, 'Unkategorisiert'), t.transaction_type, t.amount_chf, t.amount_original
        """,
        (selected_month,),
    ).fetchall()
    totals: dict[str, dict] = {}
    for row in rows:
        item = totals.setdefault(row["summary_category_id"], {"category_id": row["summary_category_id"], "category_name": row["summary_category_name"], "amount_chf": Decimal("0"), "transaction_count": 0})
        item["amount_chf"] += Decimal(str(row["amount_chf"] or row["amount_original"] or "0"))
        item["transaction_count"] += int(row["count"])
    result = list(totals.values())
    result.sort(key=lambda x: x["amount_chf"], reverse=True)
    return [{**r, "amount_chf": _fmt(r["amount_chf"])} for r in result]


def _months_denominator(year: str, months_with_bookings: int) -> int:
    today = date.today()
    if str(today.year) == year:
        elapsed = today.month
    else:
        elapsed = 12
    return max(1, min(12, max(months_with_bookings, elapsed)))


def _status(category_type: str, budget_year: Decimal, forecast_year: Decimal, actual_year: Decimal, months_with_bookings: int) -> str:
    if budget_year <= 0 and actual_year > 0:
        return "ohne Budget"
    if actual_year == 0 or months_with_bookings == 0 or budget_year <= 0:
        return "Noch keine Daten"
    ratio = forecast_year / budget_year
    if category_type == "income":
        if ratio >= Decimal("1"):
            return "Grün"
        if ratio >= Decimal("0.90"):
            return "Gelb"
        return "Rot"
    if ratio > Decimal("1.15"):
        return "Kritisch"
    if ratio > Decimal("1"):
        return "Rot"
    if ratio >= Decimal("0.90"):
        return "Gelb"
    return "Grün"


def _month_status(category_type: str, actual: Decimal, budget: Decimal) -> tuple[str, str]:
    if budget <= 0 and actual > 0:
        return "ohne Budget", "slate"
    if budget <= 0 or actual == 0:
        return "Noch keine Daten", "slate"
    ratio = actual / budget
    if category_type == "income":
        if ratio >= Decimal("1"):
            return "Grün", "green"
        if ratio >= Decimal("0.90"):
            return "Gelb", "yellow"
        return "Rot", "red"
    if ratio > Decimal("1"):
        return "Rot", "red"
    if ratio >= Decimal("0.90"):
        return "Gelb", "yellow"
    return "Grün", "green"


def _monthly_analysis(category_type: str, year: str, budget_month: Decimal, monthly: dict[str, Decimal]) -> list[dict]:
    labels = ["Jan", "Feb", "Mrz", "Apr", "Mai", "Jun", "Jul", "Aug", "Sep", "Okt", "Nov", "Dez"]
    out = []
    for idx, label in enumerate(labels, start=1):
        key = f"{idx:02d}"
        actual = monthly.get(key, Decimal("0"))
        status, color = _month_status(category_type, actual, budget_month)
        out.append({
            "month": f"{year}-{key}",
            "month_label": label,
            "actual_chf": _fmt(actual),
            "budget_chf": _fmt(budget_month),
            "difference_chf": _fmt(actual - budget_month),
            "status": status,
            "bar_color": color,
        })
    return out


def get_budget_status_by_category(conn: Connection, *, year: str = "2026") -> list[dict]:
    meta = _category_meta(conn)
    plan_rows = conn.execute(
        """
        SELECT p.*, c.name AS category_name, c.category_type, c.sort_order
        FROM budget_plan_items p
        JOIN budget_categories c ON c.category_id=p.category_id
        WHERE p.is_active=1 AND c.is_active=1 AND substr(p.plan_month,1,4)=?
        ORDER BY c.sort_order, c.name, p.name
        """,
        (year,),
    ).fetchall()
    tx_rows = conn.execute(
        """
        SELECT category_id, transaction_date, COALESCE(amount_chf, amount_original, '0') AS amount
        FROM budget_transactions
        WHERE status='confirmed' AND substr(transaction_date,1,4)=?
          AND transaction_type IN ('income','refund','expense','fee')
        """,
        (year,),
    ).fetchall()
    actual: dict[str, tuple[Decimal, set[str]]] = {}
    monthly_actuals: dict[str, dict[str, Decimal]] = {}
    rollup_child_ids: dict[str, set[str]] = {}

    def add_actual(category_id: str, amount: Decimal, month_no: str, source_child: str | None = None) -> None:
        total, months = actual.get(category_id, (Decimal("0"), set()))
        months.add(month_no)
        actual[category_id] = (total + amount, months)
        monthly_actuals.setdefault(category_id, {})[month_no] = monthly_actuals.setdefault(category_id, {}).get(month_no, Decimal("0")) + amount
        if source_child:
            rollup_child_ids.setdefault(category_id, set()).add(source_child)

    grouped: dict[str, dict] = {}
    for row in tx_rows:
        category_id = str(row["category_id"] or "")
        if not category_id or category_id not in meta:
            continue
        amount = Decimal(str(row["amount"] or "0"))
        month_no = str(row["transaction_date"] or "")[5:7]
        add_actual(category_id, amount, month_no)
        cat = meta[category_id]
        grouped.setdefault(category_id, {"category_id": category_id, "category": cat["name"], "category_type": cat["category_type"], "sort_order": cat.get("sort_order") or 999, "budget_month": Decimal("0"), "budget_year": Decimal("0"), "plans": [], "has_plan": False, "is_rollup": False})
        for ancestor_id in _ancestor_ids(category_id, meta):
            ancestor = meta[ancestor_id]
            add_actual(ancestor_id, amount, month_no, category_id)
            grouped.setdefault(ancestor_id, {"category_id": ancestor_id, "category": ancestor["name"], "category_type": ancestor["category_type"], "sort_order": ancestor.get("sort_order") or 999, "budget_month": Decimal("0"), "budget_year": Decimal("0"), "plans": [], "has_plan": False, "is_rollup": True})["is_rollup"] = True

    for plan in plan_rows:
        category_id = str(plan["category_id"])
        item = grouped.setdefault(category_id, {
            "category_id": category_id,
            "category": plan["category_name"],
            "category_type": plan["category_type"],
            "sort_order": plan["sort_order"] or 999,
            "budget_month": Decimal("0"),
            "budget_year": Decimal("0"),
            "plans": [],
            "has_plan": False,
            "is_rollup": bool(rollup_child_ids.get(category_id)),
        })
        bm = _monthly_plan_amount(plan)
        by = _annual_plan_amount(plan)
        item["budget_month"] += bm
        item["budget_year"] += by
        item["plans"].append(plan["name"])
        item["has_plan"] = True
    results: list[dict] = []
    for category_id, item in grouped.items():
        actual_year, month_set = actual.get(category_id, (Decimal("0"), set()))
        months = len(month_set)
        if actual_year == 0 and not item["has_plan"]:
            continue
        denom = _months_denominator(year, months)
        actual_avg = actual_year / Decimal(denom)
        forecast_year = actual_avg * Decimal("12") if actual_year else Decimal("0")
        forecast_month = forecast_year / Decimal("12") if forecast_year else Decimal("0")
        deviation = forecast_year - item["budget_year"]
        status = _status(item["category_type"], item["budget_year"], forecast_year, actual_year, months)
        category_monthly = monthly_actuals.get(category_id, {})
        current_month_no = f"{date.today().month:02d}" if str(date.today().year) == year else "12"
        results.append({
            "category_id": category_id,
            "category": item["category"],
            "category_type": item["category_type"],
            "budget_month": _fmt(item["budget_month"]) if item["has_plan"] else None,
            "budget_year": _fmt(item["budget_year"]) if item["has_plan"] else None,
            "has_budget": bool(item["has_plan"]),
            "purpose": "category_control",
            "actual_current_month": _fmt(category_monthly.get(current_month_no, Decimal("0"))),
            "actual_year_to_date": _fmt(actual_year),
            "monthly_actuals": {f"{m:02d}": _fmt(category_monthly.get(f"{m:02d}", Decimal("0"))) for m in range(1, 13)},
            "monthly_analysis": _monthly_analysis(item["category_type"], year, item["budget_month"] if item["has_plan"] else Decimal("0"), category_monthly),
            "actual_avg_month": _fmt(actual_avg),
            "forecast_year": _fmt(forecast_year),
            "forecast_avg_month": _fmt(forecast_month),
            "deviation_year": _fmt(deviation),
            "deviation_month": _fmt(deviation / Decimal("12")),
            "status": status,
            "status_hint": "Ausgaben vorhanden, aber kein Budgetplan" if status == "ohne Budget" and item["category_type"] != "income" else ("Einnahmen vorhanden, aber kein Budgetplan" if status == "ohne Budget" else ""),
            "months_with_bookings": months,
            "plan_names": item["plans"],
            "is_rollup": bool(item.get("is_rollup")),
            "rollup_child_count": len(rollup_child_ids.get(category_id, set())),
        })
    results.sort(key=lambda r: (0 if r["category_type"] == "income" else 1, meta.get(r["category_id"], {}).get("sort_order") or 999, r["category"]))
    try:
        from jarvis_finance.services.budget_planning import get_budget_planning_matrix
        planning_by_id = {row["category_id"]: row for row in get_budget_planning_matrix(conn, year=year).get("rows", [])}
        for row in results:
            planning = planning_by_id.get(row["category_id"])
            if not planning or row.get("category_type") != "expense":
                continue
            row.update({
                "budget_month": planning.get("budget_month_chf"),
                "budget_year": planning.get("budget_year_chf"),
                "forecast_year": planning.get("budget_adjusted_forecast_chf"),
                "linear_forecast": planning.get("linear_forecast_chf"),
                "actual_2025_year": planning.get("previous_year_total_chf"),
                "previous_year_total": planning.get("previous_year_total_chf"),
                "previous_year_months": planning.get("previous_year_months"),
                "deviation_previous_year": planning.get("forecast_vs_previous_year_chf"),
                "deviation_year": planning.get("forecast_vs_budget_chf"),
                "status": row.get("status") if planning.get("status") == "Budget fehlt" else (planning.get("status") or row.get("status")),
                "data_explorer_url": planning.get("data_explorer_url"),
                "effective_expenses_url": planning.get("effective_expenses_url"),
                "monthly_chart": planning.get("monthly_chart"),
                "has_previous_year": planning.get("has_previous_year"),
                "known_recurring_month_chf": planning.get("known_recurring_month_chf"),
                "known_recurring_year_chf": planning.get("known_recurring_year_chf"),
                "fixed_cost_quote_percent": planning.get("fixed_cost_quote_percent"),
                "variable_actual_year_to_date_chf": planning.get("variable_actual_year_to_date_chf"),
            })
    except Exception:
        pass
    return results


def get_category_month_detail(conn: Connection, *, category_id: str, month: str) -> dict:
    if not category_id or not month or len(month) < 7:
        raise ValueError("category_id and month YYYY-MM are required")
    cat = conn.execute("SELECT category_id, name, category_type FROM budget_categories WHERE category_id=?", (category_id,)).fetchone()
    if not cat:
        raise ValueError("category not found")
    year = month[:4]
    plan_rows = conn.execute(
        """
        SELECT * FROM budget_plan_items
        WHERE is_active=1 AND category_id=? AND substr(plan_month,1,4)=?
        """,
        (category_id, year),
    ).fetchall()
    budget_month = sum((_monthly_plan_amount(p) for p in plan_rows), Decimal("0"))
    tx_rows = conn.execute(
        """
        SELECT t.*, a.name AS account_name, c.name AS category_name,
               COALESCE(li.line_item_count, 0) AS line_item_count
        FROM budget_transactions t
        JOIN budget_accounts a ON a.budget_account_id=t.account_id
        LEFT JOIN budget_categories c ON c.category_id=t.category_id
        LEFT JOIN (
            SELECT transaction_candidate_id, COUNT(*) AS line_item_count
            FROM budget_import_line_items
            GROUP BY transaction_candidate_id
        ) li ON li.transaction_candidate_id=t.source_candidate_id
        WHERE t.status='confirmed' AND t.category_id=? AND substr(t.transaction_date,1,7)=?
          AND t.transaction_type IN ('expense','fee','income','refund')
        ORDER BY t.transaction_date, t.created_at
        """,
        (category_id, month),
    ).fetchall()
    actual = sum((Decimal(str(r["amount_chf"] or r["amount_original"] or "0")) for r in tx_rows), Decimal("0"))
    difference = actual - budget_month
    diff_pct = Decimal("0") if budget_month == 0 else (difference / budget_month * Decimal("100"))
    status, color = _month_status(str(cat["category_type"]), actual, budget_month)
    grouped: dict[str, Decimal] = {}
    for row in tx_rows:
        label = str(row["payee"] or row["account_name"] or row["source_type"] or "Quelle")
        grouped[label] = grouped.get(label, Decimal("0")) + Decimal(str(row["amount_chf"] or row["amount_original"] or "0"))
    breakdown = []
    for label, value in sorted(grouped.items(), key=lambda kv: kv[1], reverse=True):
        share = Decimal("0") if actual == 0 else (value / actual * Decimal("100"))
        breakdown.append({"label": label, "amount_chf": _fmt(value), "share_percent": _fmt(share)})
    transactions = []
    for row in tx_rows:
        item = row_to_dict(row)
        item["line_item_count"] = int(row["line_item_count"] or 0)
        item["article_rows_detail_only"] = bool(item["line_item_count"])
        transactions.append(item)
    return {
        "category_id": category_id,
        "category": str(cat["name"]),
        "category_type": str(cat["category_type"]),
        "month": month,
        "budget_month": _fmt(budget_month),
        "actual_chf": _fmt(actual),
        "difference_chf": _fmt(difference),
        "difference_percent": _fmt(diff_pct),
        "status": status,
        "bar_color": color,
        "breakdown_mode": "merchant" if any(r["payee"] for r in tx_rows) else "source",
        "breakdown": breakdown,
        "transactions": transactions,
        "expense_filter_url": f"/planning/budget/expenses/actual?year={year}&month={month}&category_id={category_id}",
    }


def get_budget_charts_2026(conn: Connection, *, year: str = "2026") -> dict:
    cash_rows = conn.execute(
        """
        SELECT substr(transaction_date,1,7) AS month,
               SUM(CASE WHEN transaction_type IN ('income','refund') THEN CAST(COALESCE(amount_chf, amount_original, '0') AS REAL) ELSE 0 END) AS income,
               SUM(CASE WHEN transaction_type IN ('expense','fee') THEN CAST(COALESCE(amount_chf, amount_original, '0') AS REAL) ELSE 0 END) AS expense
        FROM budget_transactions
        WHERE status='confirmed' AND substr(transaction_date,1,4)=?
        GROUP BY substr(transaction_date,1,7)
        ORDER BY month
        """,
        (year,),
    ).fetchall()
    cashflow = []
    for r in cash_rows:
        income = Decimal(str(r["income"] or 0)); expense = Decimal(str(r["expense"] or 0))
        cashflow.append({"year": year, "month": r["month"], "income_chf": _fmt(income), "expense_chf": _fmt(expense), "net_cashflow_chf": _fmt(income - expense)})
    status_rows = get_budget_status_by_category(conn, year=year)
    budget_vs_actual = [
        {"year": year, "category_id": r["category_id"], "category": r["category"], "category_type": r["category_type"], "budget_year": r["budget_year"], "actual_year_to_date": r["actual_year_to_date"], "forecast_year": r["forecast_year"], "status": r["status"]}
        for r in status_rows
    ]
    overruns = [r for r in budget_vs_actual if r["status"] in {"Rot", "Kritisch"}]
    return {
        "year": year,
        "empty_state": not cashflow,
        "cashflow_by_month": cashflow,
        "budget_vs_actual_by_category": budget_vs_actual,
        "forecast_overruns": overruns,
    }


def get_income_status(conn: Connection, *, year: str = "2026") -> dict:
    rows = [r for r in get_budget_status_by_category(conn, year=year) if r["category_type"] == "income"]
    def s(key: str) -> Decimal:
        return sum((Decimal(str(r[key] or "0")) for r in rows), Decimal("0"))
    budget_month = s("budget_month")
    budget_year = s("budget_year")
    forecast_year = s("forecast_year")
    actual_year = s("actual_year_to_date")
    current_month_no = f"{date.today().month:02d}" if str(date.today().year) == year else "12"
    actual_current_month = sum((Decimal(str((r.get("monthly_actuals") or {}).get(current_month_no, "0"))) for r in rows), Decimal("0"))
    deviation_current_month = actual_current_month - budget_month
    planned_monthly_average = budget_year / Decimal("12") if budget_year else Decimal("0")
    deviation_year = actual_year - budget_year
    status = _status("income", budget_year, forecast_year, actual_year, max((int(r["months_with_bookings"]) for r in rows), default=0))
    if actual_year == 0:
        status_label = "Noch keine Ist-Daten"
    elif deviation_year >= 0:
        status_label = "Im Plan" if deviation_year == 0 else "Über Plan"
    else:
        status_label = "Unter Plan"
    return {
        "year": year,
        "planned_income_month": _fmt(budget_month),
        "planned_income_year": _fmt(budget_year),
        "planned_income_monthly_average": _fmt(planned_monthly_average),
        "actual_income_current_month": _fmt(actual_current_month),
        "actual_income_year_to_date": _fmt(actual_year),
        "forecast_income_year": _fmt(forecast_year),
        "deviation_current_month": _fmt(deviation_current_month),
        "deviation_year": _fmt(deviation_year),
        "status": status,
        "status_label": status_label,
        "categories": rows,
    }


def get_budget_overview(conn: Connection, month: str | None = None) -> dict:
    month = month or _current_month(conn)
    year = month[:4]
    monthly = get_monthly_summary(conn, month)
    rows = conn.execute("SELECT * FROM budget_transactions WHERE status='confirmed' AND substr(transaction_date,1,7)=?", (month,)).fetchall()
    review_count = conn.execute(
        """
        SELECT COUNT(*) FROM budget_transactions t
        LEFT JOIN budget_transaction_tags tt ON tt.budget_transaction_id=t.budget_transaction_id
        LEFT JOIN budget_tags tag ON tag.tag_id=tt.tag_id
        WHERE t.status='confirmed' AND substr(t.transaction_date,1,7)=? AND (t.category_id IS NULL OR tag.name='Review' OR t.fx_status='missing')
        """,
        (month,),
    ).fetchone()[0]
    pending_seed_count = conn.execute("SELECT COUNT(*) FROM budget_seed_candidates WHERE status IN ('pending','needs_review','edited','accepted')").fetchone()[0]
    pending_tx_candidates = conn.execute("SELECT COUNT(*) FROM budget_transaction_candidates WHERE substr(transaction_date,1,4)=? AND status IN ('pending','needs_review')", (year,)).fetchone()[0]
    uncategorized = sum(1 for r in rows if r["category_id"] is None and r["transaction_type"] in {"income", "expense", "refund", "fee"})
    status_rows = get_budget_status_by_category(conn, year=year)
    planned_income = sum((Decimal(str(r["budget_month"] or "0")) for r in status_rows if r["category_type"] == "income"), Decimal("0"))
    planned_expense = sum((Decimal(str(r["budget_month"] or "0")) for r in status_rows if r["category_type"] == "expense"), Decimal("0"))
    actual_income = Decimal(str(monthly["income_chf"]))
    actual_expense = Decimal(str(monthly["expense_chf"]))
    planned_net = planned_income - planned_expense
    actual_net = actual_income - actual_expense
    remaining_total = planned_expense - actual_expense
    open_without_actuals = sum(1 for item in status_rows if item["status"] == "Noch keine Daten")
    uncategorized_rows = [row_to_dict(r) for r in rows if r["category_id"] is None and r["transaction_type"] in {"income", "expense", "refund", "fee"}]
    from jarvis_finance.services.budget_recurring import get_recurring_dashboard
    recurring = get_recurring_dashboard(conn, today=f"{month}-28")
    recurring_kpis = recurring.get("kpis", {})
    return {
        "current_month": month,
        **monthly,
        "review_count": int(review_count),
        "uncategorized_count": uncategorized,
        "pending_seed_candidate_count": int(pending_seed_count),
        "pending_transaction_candidate_count": int(pending_tx_candidates),
        "planned_income_chf": _fmt(planned_income),
        "planned_expense_chf": _fmt(planned_expense),
        "planned_net_chf": _fmt(planned_net),
        "actual_income_chf": _fmt(actual_income),
        "actual_expense_chf": _fmt(actual_expense),
        "actual_net_chf": _fmt(actual_net),
        "open_categories_without_actuals": open_without_actuals,
        "fixed_costs_month_chf": recurring_kpis.get("monthly_fixed_costs_chf", "0.00"),
        "fixed_cost_quote_percent": recurring_kpis.get("fixed_cost_quote_percent", "0.00"),
        "open_fixed_cost_candidate_count": recurring_kpis.get("open_candidate_count", 0),
        "missing_fixed_cost_payment_count": recurring_kpis.get("missing_payment_count", 0),
        "planned_budget_chf": _fmt(planned_expense),
        "remaining_budget_chf": _fmt(remaining_total),
        "budget_status": "no_plans" if not status_rows else ("no_actuals" if not rows else ("over_budget" if remaining_total < 0 else "ok")),
        "actuals_label": "noch keine Buchungen" if not rows else "Buchungen vorhanden",
        "budget_categories": status_rows,
        "uncategorized_transactions": uncategorized_rows,
        "top_categories": get_category_summary(conn, month)[:5],
        "recent_transactions": list_budget_transactions(conn)[:8],
    }