from __future__ import annotations

import json
from decimal import Decimal, InvalidOperation
from sqlite3 import Connection
from typing import Any

from fastapi import HTTPException

from jarvis_finance.audit.log import record_audit_event
from jarvis_finance.services.budget_common import new_id, now, row_to_dict
from jarvis_finance.services.household_financials import list_household_financial_effects

MONTHS = [f"{idx:02d}" for idx in range(1, 13)]
BUDGET_CADENCES = {"monthly", "annual", "one_time", "seasonal", "fixed", "variable"}


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


def _decimal_text(value: Any, *, required: bool = False, allow_zero: bool = True) -> str | None:
    if value in (None, ""):
        if required:
            raise HTTPException(status_code=422, detail="decimal amount required")
        return None
    try:
        amount = Decimal(str(value).strip())
    except (InvalidOperation, ValueError):
        raise HTTPException(status_code=422, detail="invalid decimal amount") from None
    if amount < 0:
        raise HTTPException(status_code=422, detail="negative budget values are not allowed")
    if amount == 0 and not allow_zero:
        raise HTTPException(status_code=422, detail="amount must be greater than zero")
    return _fmt(amount)


def _expense_category(conn: Connection, category_id: str) -> dict[str, Any]:
    row = conn.execute("SELECT * FROM budget_categories WHERE category_id=? AND is_active=1", (category_id,)).fetchone()
    if not row:
        raise HTTPException(status_code=404, detail="active category not found")
    if row["category_type"] != "expense":
        raise HTTPException(status_code=422, detail="budget planning values require an active expense category")
    return row_to_dict(row)


def _derive_budget(payload: dict[str, Any]) -> tuple[str | None, str | None]:
    monthly = _decimal_text(payload.get("monthly_budget_chf"))
    annual = _decimal_text(payload.get("annual_budget_chf"))
    if monthly and not annual:
        annual = _fmt(Decimal(monthly) * Decimal("12"))
    if annual and not monthly:
        monthly = _fmt(Decimal(annual) / Decimal("12"))
    return monthly, annual


def _normalise_month_map(value: Any) -> dict[str, str]:
    if not value:
        return {}
    if not isinstance(value, dict):
        raise HTTPException(status_code=422, detail="previous_year_months must be a map MM -> decimal text")
    out: dict[str, str] = {}
    for month, amount in value.items():
        key = str(month).zfill(2)
        if key not in MONTHS:
            raise HTTPException(status_code=422, detail="month must be 01..12")
        text = _decimal_text(amount)
        if text is not None:
            out[key] = text
    return out


def _derive_previous_year(payload: dict[str, Any]) -> tuple[dict[str, str], str, list[str]]:
    warnings: list[str] = []
    months = _normalise_month_map(payload.get("previous_year_months"))
    total_text = _decimal_text(payload.get("previous_year_total_chf"))
    distribute = bool(payload.get("distribute_previous_year_total"))
    if months:
        total = sum((Decimal(v) for v in months.values()), Decimal("0"))
        if total_text and Decimal(total_text) != total:
            warnings.append("Jahreswert 2025 weicht von Summe der Monatswerte ab; Monatswerte sind führend.")
        return months, _fmt(total), warnings
    if total_text:
        total = Decimal(total_text)
        if distribute:
            avg = _fmt(total / Decimal("12"))
            months = {m: avg for m in MONTHS}
            remainder = total - sum((Decimal(v) for v in months.values()), Decimal("0"))
            if remainder:
                months["12"] = _fmt(Decimal(months["12"]) + remainder)
            return months, _fmt(total), warnings
        return {}, _fmt(total), warnings
    return {}, "0.00", warnings


FIELD_LABELS = {
    "monthly_budget_chf": "Budget Monat",
    "annual_budget_chf": "Budget Jahr",
    "budget_cadence": "Periodizität",
    "previous_year_total_chf": "Vorjahreswert Jahr",
    "previous_year_months": "Vorjahr Monatswerte",
    "notes": "Notiz",
    "category_id": "Kategorie-Mapping",
}


def _current_planning_snapshot(conn: Connection, category_id: str, year: str = "2026") -> dict[str, Any]:
    plan = _plan_amounts(conn, year).get(category_id, {})
    budget_month = Decimal(str(plan.get("budget_month", "0"))) if plan else Decimal("0")
    budget_year = Decimal(str(plan.get("budget_year", "0"))) if plan else Decimal("0")
    previous = _previous_year_months(conn, str(int(year) - 1)).get(category_id, {})
    if "__year__" in previous:
        prev_months = {}
        prev_total = previous["__year__"]
    else:
        prev_months = {m: _fmt(previous.get(m, Decimal("0"))) for m in MONTHS if previous.get(m, Decimal("0")) > 0}
        prev_total = sum((previous.get(m, Decimal("0")) for m in MONTHS), Decimal("0"))
    notes_row = conn.execute("SELECT notes FROM budget_plan_items WHERE category_id=? AND substr(plan_month,1,4)=? AND is_active=1 ORDER BY created_at LIMIT 1", (category_id, year)).fetchone()
    return {
        "monthly_budget_chf": _fmt(budget_month) if budget_month else None,
        "annual_budget_chf": _fmt(budget_year) if budget_year else None,
        "budget_cadence": str(plan.get("cadence") or "monthly") if plan else None,
        "previous_year_total_chf": _fmt(prev_total),
        "previous_year_months": prev_months,
        "notes": notes_row["notes"] if notes_row else None,
    }


def _changes(old: dict[str, Any], new: dict[str, Any]) -> list[dict[str, str]]:
    out: list[dict[str, str]] = []
    for field, label in FIELD_LABELS.items():
        ov = old.get(field)
        nv = new.get(field)
        if ov != nv:
            out.append({
                "field": field,
                "field_label": label,
                "old_value": json.dumps(ov, sort_keys=True) if isinstance(ov, dict) else ("" if ov is None else str(ov)),
                "new_value": json.dumps(nv, sort_keys=True) if isinstance(nv, dict) else ("" if nv is None else str(nv)),
            })
    return out


def _plausibility_checks(*, budget_year: Decimal, previous_total: Decimal, actual_ytd: Decimal, forecast: Decimal, has_budget: bool, has_previous: bool) -> list[dict[str, str]]:
    checks: list[dict[str, str]] = []
    if not has_budget and actual_ytd > 0:
        checks.append({"severity": "warning", "code": "actuals_without_budget", "message": "Kategorie hat Ist-Ausgaben, aber kein Budget."})
    if has_budget and actual_ytd == 0:
        checks.append({"severity": "info", "code": "budget_without_actuals", "message": "Kategorie hat Budget, aber noch keine Ist-Daten."})
    if not has_previous:
        checks.append({"severity": "info", "code": "missing_previous_year", "message": "Kategorie hat keine Vorjahreswerte."})
    if has_budget and has_previous and previous_total > 0:
        ratio = budget_year / previous_total
        if ratio < Decimal("0.80"):
            checks.append({"severity": "info", "code": "budget_lower_than_previous_year", "message": "Budget Jahr 2026 liegt mehr als 20% unter Vorjahr 2025."})
        elif ratio > Decimal("1.20"):
            checks.append({"severity": "info", "code": "budget_higher_than_previous_year", "message": "Budget Jahr 2026 liegt mehr als 20% über Vorjahr 2025."})
    if has_budget and actual_ytd > budget_year:
        checks.append({"severity": "warning", "code": "actual_over_budget", "message": "Ist 2026 liegt bereits über Budget."})
    if has_budget and budget_year > 0 and forecast / budget_year > Decimal("1.15"):
        checks.append({"severity": "critical", "code": "forecast_critical", "message": "Forecast liegt kritisch über Budget."})
    return checks


def preview_category_budget_values(conn: Connection, category_id: str, payload: dict[str, Any]) -> dict[str, Any]:
    cat = _expense_category(conn, category_id)
    year = str(payload.get("year") or "2026")
    if year != "2026":
        raise HTTPException(status_code=422, detail="budget planning v1 writes only active year 2026")
    cadence = str(payload.get("budget_cadence") or payload.get("cadence") or "monthly")
    if cadence not in BUDGET_CADENCES:
        raise HTTPException(status_code=422, detail="invalid budget cadence")
    monthly, annual = _derive_budget(payload)
    previous_months, previous_total, warnings = _derive_previous_year(payload)
    previous_avg = Decimal(previous_total) / Decimal("12") if Decimal(previous_total) else Decimal("0")
    normalised = {
        "year": year,
        "category_id": category_id,
        "monthly_budget_chf": monthly,
        "annual_budget_chf": annual,
        "budget_cadence": cadence,
        "previous_year": "2025",
        "previous_year_months": previous_months,
        "previous_year_total_chf": previous_total,
        "source": payload.get("source") or "manual",
        "notes": payload.get("notes") or None,
    }
    preview_id = new_id("preview")
    record_audit_event(conn, source="vue_dashboard", action="budget_planning_preview", entity_type="budget_category", entity_id=category_id, new_values={"preview_id": preview_id, "category": cat["name"], "year": year}, created_by="user")
    conn.commit()
    return {
        "preview_id": preview_id,
        "summary": f"Budgetplanung {cat['name']} {year}",
        "warnings": warnings,
        "payload": normalised,
        "review": {
            "category": cat["name"],
            "budget_month_chf": monthly or "0.00",
            "budget_year_chf": annual or "0.00",
            "budget_cadence": cadence,
            "previous_year_total_chf": previous_total,
            "previous_year_monthly_average_chf": _fmt(previous_avg),
            "previous_year_months": {m: previous_months.get(m, "0.00") for m in MONTHS},
        },
        "requires_explicit_confirm": True,
    }


def _upsert_plan(conn: Connection, category_id: str, payload: dict[str, Any]) -> str | None:
    monthly = payload.get("monthly_budget_chf")
    annual = payload.get("annual_budget_chf")
    if not monthly and not annual:
        return None
    row = conn.execute("SELECT plan_item_id FROM budget_plan_items WHERE category_id=? AND substr(plan_month,1,4)=? AND is_active=1 ORDER BY created_at LIMIT 1", (category_id, payload["year"])).fetchone()
    ts = now()
    cadence = "annual" if payload["budget_cadence"] == "annual" else "monthly"
    if row:
        plan_id = str(row["plan_item_id"])
        conn.execute("UPDATE budget_plan_items SET monthly_amount_chf=?, annual_amount_chf=?, cadence=?, source_type='manual', notes=COALESCE(?, notes), updated_at=? WHERE plan_item_id=?", (monthly, annual, cadence, payload.get("notes"), ts, plan_id))
    else:
        plan_id = new_id("bplan")
        cat_name = conn.execute("SELECT name FROM budget_categories WHERE category_id=?", (category_id,)).fetchone()["name"]
        conn.execute("""
            INSERT INTO budget_plan_items(plan_item_id, plan_month, category_id, name, monthly_amount_chf, annual_amount_chf, cadence, is_fixed_cost, source_type, notes, sort_order, is_active, created_at, updated_at)
            VALUES (?, ?, ?, ?, ?, ?, ?, 0, 'manual', ?, 999, 1, ?, ?)
        """, (plan_id, f"{payload['year']}-01", category_id, f"Budget {cat_name}", monthly, annual, cadence, payload.get("notes"), ts, ts))
    return plan_id


def _replace_baselines(conn: Connection, category_id: str, payload: dict[str, Any]) -> None:
    ts = now()
    conn.execute("DELETE FROM budget_category_baselines WHERE category_id=? AND year=? AND baseline_type IN ('actual_previous_year','planned_budget')", (category_id, payload["previous_year"]))
    months = payload.get("previous_year_months") or {}
    for month, amount in months.items():
        conn.execute("""
            INSERT INTO budget_category_baselines(baseline_id, category_id, year, month, amount_text, currency, baseline_type, source, notes, created_at, updated_at)
            VALUES (?, ?, ?, ?, ?, 'CHF', 'actual_previous_year', ?, ?, ?, ?)
        """, (new_id("bbase"), category_id, payload["previous_year"], month, _fmt(Decimal(str(amount))), payload.get("source") or "manual", payload.get("notes"), ts, ts))
    if not months and Decimal(str(payload.get("previous_year_total_chf") or "0")) > 0:
        conn.execute("""
            INSERT INTO budget_category_baselines(baseline_id, category_id, year, month, amount_text, currency, baseline_type, source, notes, created_at, updated_at)
            VALUES (?, ?, ?, NULL, ?, 'CHF', 'actual_previous_year', ?, ?, ?, ?)
        """, (new_id("bbase"), category_id, payload["previous_year"], payload["previous_year_total_chf"], payload.get("source") or "manual", payload.get("notes"), ts, ts))


def confirm_category_budget_values(conn: Connection, category_id: str, payload: dict[str, Any]) -> dict[str, Any]:
    old_snapshot = _current_planning_snapshot(conn, category_id, str(payload.get("year") or "2026"))
    preview = preview_category_budget_values(conn, category_id, payload)
    normalised = preview["payload"]
    plan_id = _upsert_plan(conn, category_id, normalised)
    _replace_baselines(conn, category_id, normalised)
    new_snapshot = _current_planning_snapshot(conn, category_id, normalised["year"])
    audit_id = record_audit_event(
        conn,
        source="vue_dashboard",
        action="budget_planning_confirmed",
        entity_type="budget_category",
        entity_id=category_id,
        old_values=old_snapshot,
        new_values={**new_snapshot, "plan_id": plan_id, "year": normalised["year"], "previous_year": normalised["previous_year"], "changes": _changes(old_snapshot, new_snapshot)},
        user_text_note=normalised.get("notes"),
        created_by="user",
    )
    conn.commit()
    return {"status": "confirmed", "entity_id": category_id, "plan_item_id": plan_id, "audit_id": audit_id, "message": "Budgetplanung gespeichert"}


def preview_budget_planning_batch(conn: Connection, payload: dict[str, Any]) -> dict[str, Any]:
    changes = payload.get("changes") or []
    if not isinstance(changes, list) or not changes:
        raise HTTPException(status_code=422, detail="changes required")
    previews = []
    normalised = []
    for change in changes:
        if not isinstance(change, dict) or not change.get("category_id"):
            raise HTTPException(status_code=422, detail="category_id required for every change")
        preview = preview_category_budget_values(conn, str(change["category_id"]), change)
        previews.append({"category_id": str(change["category_id"]), "summary": preview["summary"], "warnings": preview["warnings"], "review": preview["review"]})
        normalised.append(preview["payload"])
    preview_id = new_id("preview")
    return {"preview_id": preview_id, "summary": f"{len(normalised)} Budgetänderungen prüfen", "count": len(normalised), "changes": previews, "warnings": [w for p in previews for w in p["warnings"]], "payload": {"changes": normalised}, "requires_explicit_confirm": True}


def confirm_budget_planning_batch(conn: Connection, payload: dict[str, Any]) -> dict[str, Any]:
    preview = preview_budget_planning_batch(conn, payload)
    audit_ids: list[str] = []
    for change in preview["payload"]["changes"]:
        result = confirm_category_budget_values(conn, str(change["category_id"]), change)
        audit_ids.append(result["audit_id"])
    batch_audit_id = record_audit_event(conn, source="vue_dashboard", action="budget_planning_batch_confirmed", entity_type="budget_planning_batch", entity_id=preview["preview_id"], new_values={"confirmed_count": len(audit_ids), "category_audit_ids": audit_ids}, created_by="user")
    conn.commit()
    return {"status": "confirmed", "entity_id": preview["preview_id"], "audit_id": batch_audit_id, "confirmed_count": len(audit_ids), "message": "Budgetänderungen gespeichert"}


def _load_audit_json(value: Any) -> dict[str, Any]:
    try:
        return json.loads(value or "{}")
    except Exception:
        return {}


def get_budget_planning_audit_timeline(conn: Connection, category_id: str, *, limit: int = 25) -> dict[str, Any]:
    cat = _expense_category(conn, category_id)
    rows = conn.execute(
        """
        SELECT * FROM audit_log
        WHERE entity_type='budget_category' AND entity_id=? AND action IN ('budget_planning_confirmed','budget_planning_preview')
        ORDER BY timestamp DESC LIMIT ?
        """,
        (category_id, limit),
    ).fetchall()
    entries = []
    for row in rows:
        old = _load_audit_json(row["old_values_json"])
        new = _load_audit_json(row["new_values_json"])
        changes = new.get("changes") if isinstance(new.get("changes"), list) else _changes(old, new)
        entries.append({
            "timestamp": row["timestamp"],
            "action": row["action"],
            "action_label": "Budgetplanung gespeichert" if row["action"] == "budget_planning_confirmed" else "Preview erstellt",
            "source": row["source"],
            "note": row["user_text_note"] or new.get("notes") or "",
            "created_by": row["created_by"] or "system",
            "changes": changes,
            "technical": {"audit_id": row["audit_id"]},
        })
    return {"category_id": category_id, "category": cat["name"], "entries": entries}


def preview_budget_planning_excel_template(conn: Connection, payload: dict[str, Any] | None = None) -> dict[str, Any]:
    year = str((payload or {}).get("year") or "2026")
    return {
        "status": "prepared",
        "title": "Aus Excel-Vorlage übernehmen",
        "summary": "Excel-Budget-Vorlage ist vorbereitet. Dieser Sprint erzeugt keine produktive Übernahme; später nur über kontrollierte Preview mit Mapping und Confirm.",
        "year": year,
        "targets": ["Budget 2026", "Vorjahr 2025", "Income/Budgetwerte"],
        "productive_mutation": False,
        "requires_explicit_confirm": True,
        "warnings": ["Coming soon: keine automatische produktive Übernahme."],
    }


def _plan_amounts(conn: Connection, year: str) -> dict[str, dict[str, Decimal | str]]:
    rows = conn.execute("""
        SELECT p.*, c.name AS category_name
        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 c.category_type='expense' AND substr(p.plan_month,1,4)=?
    """, (year,)).fetchall()
    out: dict[str, dict[str, Decimal | str]] = {}
    for row in rows:
        cid = str(row["category_id"])
        item = out.setdefault(cid, {"budget_month": Decimal("0"), "budget_year": Decimal("0"), "cadence": str(row["cadence"] or "monthly")})
        month = Decimal(str(row["monthly_amount_chf"] or "0")) if row["monthly_amount_chf"] else (Decimal(str(row["annual_amount_chf"] or "0")) / Decimal("12") if row["annual_amount_chf"] else Decimal("0"))
        year_amount = Decimal(str(row["annual_amount_chf"] or "0")) if row["annual_amount_chf"] else month * Decimal("12")
        item["budget_month"] = Decimal(str(item["budget_month"])) + month
        item["budget_year"] = Decimal(str(item["budget_year"])) + year_amount
        item["cadence"] = str(row["cadence"] or item["cadence"])
    return out


def _actual_months(
    conn: Connection, year: str, effect_rows: list[dict] | None = None
) -> dict[str, dict[str, Decimal]]:
    out: dict[str, dict[str, Decimal]] = {}
    rows = effect_rows if effect_rows is not None else list_household_financial_effects(
        conn, date_from=f"{year}-01-01", date_to=f"{year}-12-31"
    )
    for row in rows:
        category_id = row["effective_category_id"]
        if not category_id:
            continue
        category = out.setdefault(str(category_id), {})
        month = str(row["transaction_date"])[5:7]
        category[month] = category.get(month, Decimal("0")) + Decimal(
            str(row["expense_effect"] or "0")
        )
    return out


def _previous_year_months(conn: Connection, year: str = "2025") -> dict[str, dict[str, Decimal]]:
    rows = conn.execute("SELECT category_id, month, amount_text FROM budget_category_baselines WHERE year=? AND baseline_type='actual_previous_year'", (year,)).fetchall()
    out: dict[str, dict[str, Decimal]] = {}
    for row in rows:
        cid = str(row["category_id"])
        if row["month"]:
            out.setdefault(cid, {})[str(row["month"]).zfill(2)] = Decimal(str(row["amount_text"] or "0"))
        else:
            out.setdefault(cid, {})["__year__"] = Decimal(str(row["amount_text"] or "0"))
    return out


def _status(has_budget: bool, budget_year: Decimal, forecast: Decimal, actual_ytd: Decimal) -> str:
    if not has_budget:
        return "Budget fehlt" if actual_ytd > 0 else "kein Budget"
    if actual_ytd == 0:
        return "Noch keine Daten"
    if budget_year <= 0:
        return "Budget fehlt"
    ratio = forecast / budget_year
    if ratio > Decimal("1.15"):
        return "Kritisch"
    if ratio > Decimal("1"):
        return "Rot"
    if ratio >= Decimal("0.90"):
        return "Gelb"
    return "Grün"


def get_budget_planning_matrix(
    conn: Connection,
    *,
    year: str = "2026",
    current_month: str | None = None,
    _effect_rows: list[dict] | None = None,
) -> dict[str, Any]:
    current_month = current_month or f"{year}-12"
    elapsed = max(1, min(12, int(current_month[5:7])))
    cats = conn.execute("SELECT category_id, name, sort_order FROM budget_categories WHERE is_active=1 AND category_type='expense' ORDER BY sort_order, name").fetchall()
    plans = _plan_amounts(conn, year)
    actuals = _actual_months(conn, year, _effect_rows)
    previous = _previous_year_months(conn, str(int(year) - 1))
    recurring_by_category: dict[str, dict[str, Decimal]] = {}
    try:
        for r in conn.execute("SELECT category_id, expected_amount_text, frequency, recurring_type FROM budget_recurring_payments WHERE status='active'").fetchall():
            amount = Decimal(str(r["expected_amount_text"] or "0"))
            if r["frequency"] == "monthly":
                month_amount, year_amount = amount, amount * Decimal("12")
            elif r["frequency"] == "quarterly":
                month_amount, year_amount = amount / Decimal("3"), amount * Decimal("4")
            elif r["frequency"] == "yearly":
                month_amount, year_amount = amount / Decimal("12"), amount
            elif r["frequency"] == "weekly":
                month_amount, year_amount = amount * Decimal("52") / Decimal("12"), amount * Decimal("52")
            else:
                month_amount, year_amount = Decimal("0"), Decimal("0")
            bucket = recurring_by_category.setdefault(str(r["category_id"]), {"month": Decimal("0"), "year": Decimal("0")})
            bucket["month"] += month_amount; bucket["year"] += year_amount
    except Exception:
        recurring_by_category = {}
    rows = []
    without_budget: list[str] = []
    without_previous: list[str] = []
    for cat in cats:
        cid = str(cat["category_id"])
        plan = plans.get(cid, {})
        budget_month = Decimal(str(plan.get("budget_month", "0"))) if plan else Decimal("0")
        budget_year = Decimal(str(plan.get("budget_year", "0"))) if plan else Decimal("0")
        has_budget = budget_year > 0 or budget_month > 0
        months_2026 = {m: actuals.get(cid, {}).get(m, Decimal("0")) for m in MONTHS}
        actual_ytd = sum((months_2026[m] for m in MONTHS[:elapsed]), Decimal("0"))
        prev_map = previous.get(cid, {})
        if "__year__" in prev_map:
            prev_total = prev_map["__year__"]
            prev_months = {m: Decimal("0") for m in MONTHS}
        else:
            prev_months = {m: prev_map.get(m, Decimal("0")) for m in MONTHS}
            prev_total = sum(prev_months.values(), Decimal("0"))
        has_previous = prev_total > 0
        if not has_budget:
            without_budget.append(cid)
        if not has_previous:
            without_previous.append(cid)
        linear = (actual_ytd / Decimal(elapsed)) * Decimal("12") if actual_ytd else (budget_year if has_budget else Decimal("0"))
        adjusted = actual_ytd + (Decimal("12") - Decimal(elapsed)) * budget_month if actual_ytd or has_budget else Decimal("0")
        current_no = current_month[5:7]
        recurring = recurring_by_category.get(cid, {"month": Decimal("0"), "year": Decimal("0")})
        fixed_cost_quote = Decimal("0") if budget_month == 0 else recurring["month"] / budget_month * Decimal("100")
        plausibility = _plausibility_checks(budget_year=budget_year, previous_total=prev_total, actual_ytd=actual_ytd, forecast=adjusted, has_budget=has_budget, has_previous=has_previous)
        row = {
            "category_id": cid,
            "category": str(cat["name"]),
            "category_type": "expense",
            "budget_month_chf": _fmt(budget_month) if has_budget else None,
            "budget_year_chf": _fmt(budget_year) if has_budget else None,
            "budget_cadence": str(plan.get("cadence") or "monthly") if plan else None,
            "has_budget": has_budget,
            "actual_current_month_chf": _fmt(months_2026[current_no]),
            "actual_months_2026": {m: _fmt(v) for m, v in months_2026.items()},
            "actual_year_to_date_chf": _fmt(actual_ytd),
            "actual_avg_month_chf": _fmt(actual_ytd / Decimal(elapsed) if actual_ytd else Decimal("0")),
            "linear_forecast_chf": _fmt(linear),
            "budget_adjusted_forecast_chf": _fmt(adjusted),
            "forecast_year_chf": _fmt(adjusted),
            "previous_year_months": {m: _fmt(v) for m, v in prev_months.items()},
            "previous_year_total_chf": _fmt(prev_total),
            "previous_year_monthly_average_chf": _fmt(prev_total / Decimal("12") if prev_total else Decimal("0")),
            "has_previous_year": has_previous,
            "actual_vs_budget_chf": _fmt(actual_ytd - budget_year),
            "forecast_vs_budget_chf": _fmt(adjusted - budget_year),
            "current_month_vs_budget_chf": _fmt(months_2026[current_no] - budget_month),
            "current_month_vs_previous_year_chf": _fmt(months_2026[current_no] - prev_months.get(current_no, Decimal("0"))),
            "ytd_vs_previous_year_chf": _fmt(actual_ytd - sum((prev_months[m] for m in MONTHS[:elapsed]), Decimal("0"))),
            "forecast_vs_previous_year_chf": _fmt(adjusted - prev_total),
            "status": _status(has_budget, budget_year, adjusted, actual_ytd),
            "status_color": "gray",
            "known_recurring_month_chf": _fmt(recurring["month"]),
            "known_recurring_year_chf": _fmt(recurring["year"]),
            "fixed_cost_quote_percent": _fmt(fixed_cost_quote),
            "variable_actual_year_to_date_chf": _fmt(max(Decimal("0"), actual_ytd - recurring["month"] * Decimal(elapsed))),
            "data_explorer_url": f"/planning/budget/analysis/data-explorer?year={year}&type=expense&category_id={cid}",
            "effective_expenses_url": f"/planning/budget/expenses/actual?year={year}&category_id={cid}",
            "monthly_detail_url": f"/planning/budget/status?category_id={cid}&month={current_month}",
            "monthly_chart": [{"month": m, "actual_2026_chf": _fmt(months_2026[m]), "budget_chf": _fmt(budget_month), "actual_2025_chf": _fmt(prev_months.get(m, Decimal("0"))), "deviation_chf": _fmt(months_2026[m] - budget_month)} for m in MONTHS],
            "plausibility_checks": plausibility,
        }
        rows.append(row)
    return {
        "purpose": "budget_planning_forecast_v1",
        "year": year,
        "previous_year": str(int(year) - 1),
        "current_month": current_month,
        "forecast_method": "budget_adjusted_forecast = Ist bisher + Restmonate × Budget Monat; linear_forecast zusätzlich ausgewiesen",
        "rows": rows,
        "categories_without_budget": without_budget,
        "categories_without_previous_year": without_previous,
        "categories_with_actuals_without_budget": [r["category_id"] for r in rows if not r["has_budget"] and Decimal(r["actual_year_to_date_chf"]) > 0],
        "categories_with_budget_without_actuals": [r["category_id"] for r in rows if r["has_budget"] and Decimal(r["actual_year_to_date_chf"]) == 0],
        "categories_with_previous_year_without_budget": [r["category_id"] for r in rows if r["has_previous_year"] and not r["has_budget"]],
        "forecast_method_help": {
            "linear_forecast": "Ist bisher / vergangene Monate × 12",
            "budget_adjusted_forecast": "Ist bisher + Restmonate × Monatsbudget",
            "primary": "budget_adjusted_forecast",
        },
    }