from __future__ import annotations

import json
import re
from collections import defaultdict
from datetime import date, datetime, timedelta
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, require_decimal_text, validate_currency
from jarvis_finance.services.budget_overview import _fmt

FREQUENCIES = {"monthly", "quarterly", "yearly", "weekly", "irregular"}
RECURRING_TYPES = {"fixed_cost", "subscription", "variable_recurring"}
STATUSES = {"candidate", "active", "ignored", "paused", "archived"}
EXCLUDED_TERMS = ("migros", "migrol", "restaurant", "coop", "galaxus", "digitec", "tank", "shell", "bp ", "true wealth", "investment", "transfer", "kreditkartenausgleich", "credit card", "credit_card_payment", "investment_transfer", "covered_by")
VARIABLE_TERMS = ("migros", "coop", "restaurant", "tank", "fuel", "galaxus", "digitec")
FIXED_TERMS = ("insurance", "versicherung", "krankenkasse", "miete", "wohnen", "tax", "steuer", "leasing", "schule", "school", "telekom", "sunrise", "salt", "swisscom")
SUBSCRIPTION_TERMS = ("netflix", "apple", "google", "disney", "amazon", "prime", "openai", "chatgpt", "dropbox", "norton", "bitdefender", "tradingview", "babbel", "spotify")


def _dec(value: object) -> Decimal:
    try:
        return Decimal(str(value if value not in (None, "") else "0"))
    except (InvalidOperation, ValueError):
        return Decimal("0")


def _norm(value: str | None) -> str:
    return re.sub(r"\s+", " ", re.sub(r"[^a-z0-9]+", " ", str(value or "").lower())).strip()


def _similar_key(value: str | None) -> str:
    return _norm(value).replace(" ", "")[:18]


def _date(value: str) -> date:
    return date.fromisoformat(str(value)[:10])


def _safe_date(value: Any) -> date | None:
    try:
        if value in (None, ""):
            return None
        return _date(str(value))
    except (TypeError, ValueError):
        return None


def _add_months(d: date, months: int, day: int | None = None) -> date:
    month = d.month - 1 + months
    year = d.year + month // 12
    month = month % 12 + 1
    desired_day = day or d.day
    last = 28
    while True:
        try:
            return date(year, month, min(desired_day, last))
        except ValueError:
            last -= 1


def next_expected_date(last_seen: date, frequency: str, expected_day: int | None = None, expected_month: int | None = None) -> str:
    if frequency == "weekly":
        return (last_seen + timedelta(days=7)).isoformat()
    if frequency == "monthly":
        return _add_months(last_seen, 1, expected_day).isoformat()
    if frequency == "quarterly":
        return _add_months(last_seen, 3, expected_day).isoformat()
    if frequency == "yearly":
        month = expected_month or last_seen.month
        return date(last_seen.year + 1, month, min(expected_day or last_seen.day, 28)).isoformat()
    return ""


def _infer_frequency(dates: list[date]) -> str:
    if len(dates) < 2:
        return "irregular"
    gaps = sorted((dates[i] - dates[i - 1]).days for i in range(1, len(dates)))
    avg = sum(gaps) / len(gaps)
    if 24 <= avg <= 38:
        return "monthly"
    if 75 <= avg <= 105:
        return "quarterly"
    if 330 <= avg <= 400:
        return "yearly"
    if 5 <= avg <= 10:
        return "weekly"
    return "irregular"


def _infer_type(name: str, category: str, amount: Decimal, frequency: str) -> str:
    text = f"{name} {category}".lower()
    if any(t in text for t in VARIABLE_TERMS):
        return "variable_recurring"
    if any(t in text for t in FIXED_TERMS) or amount >= Decimal("100") or frequency in {"quarterly", "yearly"}:
        return "fixed_cost"
    if any(t in text for t in SUBSCRIPTION_TERMS) or amount < Decimal("100"):
        return "subscription"
    return "variable_recurring"


def _excluded(name: str, description: str = "") -> bool:
    text = f"{name} {description}".lower()
    return any(term in text for term in EXCLUDED_TERMS)


def _row_amount(row: Any) -> Decimal:
    return _dec(row["amount_chf"] or row["amount_original"] or "0")


def _expected_budget(amount: Decimal, frequency: str) -> tuple[Decimal, Decimal]:
    if frequency == "monthly":
        return amount, amount * Decimal("12")
    if frequency == "quarterly":
        return amount / Decimal("3"), amount * Decimal("4")
    if frequency == "yearly":
        return amount / Decimal("12"), amount
    if frequency == "weekly":
        return amount * Decimal("52") / Decimal("12"), amount * Decimal("52")
    return Decimal("0"), Decimal("0")


def _upsert_candidate(conn: Connection, item: dict[str, Any]) -> str:
    existing = conn.execute("SELECT recurring_id FROM budget_recurring_payments WHERE status='candidate' AND lower(name)=lower(?) AND category_id=?", (item["name"], item["category_id"])).fetchone()
    rid = existing["recurring_id"] if existing else new_id("rec")
    values = {
        **item,
        "recurring_id": rid,
        "currency": item.get("currency") or "CHF",
        "amount_tolerance_pct": str(item.get("amount_tolerance_pct") or "10"),
        "tolerance_percent": str(item.get("tolerance_percent") or item.get("amount_tolerance_pct") or "10"),
        "tolerance_amount_text": item.get("tolerance_amount_text"),
        "date_tolerance_days": int(item.get("date_tolerance_days") or 5),
        "status": item.get("status") or "candidate",
        "source": item.get("source") or "detected",
        "confidence": str(item.get("confidence") or "0.70"),
        "updated_at": now(),
    }
    if existing:
        conn.execute("""
            UPDATE budget_recurring_payments SET expected_amount_text=?, frequency=?, expected_day_of_month=?, expected_month=?, recurring_type=?, confidence=?, last_seen_date=?, next_expected_date=?, candidate_evidence_json=?, merchant_name=?, tolerance_percent=?, tolerance_amount_text=?, updated_at=? WHERE recurring_id=?
        """, (values["expected_amount_text"], values["frequency"], values.get("expected_day_of_month"), values.get("expected_month"), values["recurring_type"], values["confidence"], values.get("last_seen_date"), values.get("next_expected_date"), values.get("candidate_evidence_json"), values.get("merchant_name") or values.get("name"), values.get("tolerance_percent"), values.get("tolerance_amount_text"), values["updated_at"], rid))
    else:
        conn.execute("""
            INSERT INTO budget_recurring_payments(recurring_id,name,merchant_name,merchant_id,category_id,account_id,expected_amount_text,currency,frequency,expected_day_of_month,expected_month,tolerance_amount_text,tolerance_percent,amount_tolerance_pct,date_tolerance_days,recurring_type,status,source,confidence,last_seen_date,next_expected_date,notes,candidate_evidence_json,created_at,updated_at)
            VALUES (:recurring_id,:name,:merchant_name,:merchant_id,:category_id,:account_id,:expected_amount_text,:currency,:frequency,:expected_day_of_month,:expected_month,:tolerance_amount_text,:tolerance_percent,:amount_tolerance_pct,:date_tolerance_days,:recurring_type,:status,:source,:confidence,:last_seen_date,:next_expected_date,:notes,:candidate_evidence_json,:created_at,:updated_at)
        """, {"merchant_name": values.get("name"), "merchant_id": None, "account_id": None, "expected_month": None, "notes": None, "created_at": now(), **values})
    return rid


def detect_recurring_payment_candidates(conn: Connection, *, today: str | None = None) -> dict[str, Any]:
    rows = conn.execute("""
        SELECT t.*, c.name AS category_name FROM budget_transactions t
        LEFT JOIN budget_categories c ON c.category_id=t.category_id
        WHERE t.status='confirmed' AND t.transaction_type IN ('expense','fee')
        ORDER BY t.transaction_date
    """).fetchall()
    groups: dict[tuple[str, str], list[Any]] = defaultdict(list)
    for row in rows:
        name = row["payee"] or row["description"]
        if not row["category_id"] or _excluded(name, row["description"]):
            continue
        groups[(_similar_key(name), row["category_id"])].append(row)
    candidates: list[dict[str, Any]] = []
    for (_key, cid), items in groups.items():
        if len(items) < 2:
            continue
        dates = [_date(r["transaction_date"]) for r in items]
        freq = _infer_frequency(dates)
        amounts = [_row_amount(r) for r in items]
        avg = sum(amounts, Decimal("0")) / Decimal(len(amounts))
        max_delta = max(abs(a - avg) for a in amounts) if amounts else Decimal("0")
        name = str(items[-1]["payee"] or items[-1]["description"])
        cat = str(items[-1]["category_name"] or "")
        if freq == "irregular" and not (len(items) >= 3 and avg and max_delta <= avg * Decimal("0.15") and any(t in f"{name} {cat}".lower() for t in SUBSCRIPTION_TERMS + FIXED_TERMS)):
            continue
        rtype = _infer_type(name, cat, avg, freq)
        last = max(dates)
        item = {
            "name": name,
            "merchant_name": name,
            "category_id": cid,
            "account_id": items[-1]["account_id"],
            "expected_amount_text": _fmt(avg),
            "currency": items[-1]["currency_original"] or "CHF",
            "frequency": freq,
            "expected_day_of_month": int(round(sum(d.day for d in dates) / len(dates))),
            "expected_month": last.month if freq == "yearly" else None,
            "recurring_type": rtype,
            "confidence": "0.86" if len(items) >= 3 else "0.70",
            "last_seen_date": last.isoformat(),
            "next_expected_date": next_expected_date(last, freq, int(round(sum(d.day for d in dates) / len(dates))), last.month if freq == "yearly" else None),
            "candidate_evidence_json": json.dumps({"confirmed_transaction_count": len(items), "sources": ["confirmed_transactions"]}, sort_keys=True),
        }
        item["candidate_id"] = _upsert_candidate(conn, item)
        candidates.append(item)
    conn.commit()
    return {"purpose": "recurring_detection", "today": today, "source_scope": "confirmed_budget_transactions_only", "candidates": sorted(candidates, key=lambda c: c["name"])}


def _validate_payload(payload: dict[str, Any]) -> dict[str, Any]:
    name = str(payload.get("name") or "").strip()
    if not name:
        raise HTTPException(status_code=422, detail="name required")
    amount = require_decimal_text(str(payload.get("expected_amount_text") or ""), "expected_amount_text")
    freq = str(payload.get("frequency") or "monthly")
    if freq not in FREQUENCIES:
        raise HTTPException(status_code=422, detail="invalid frequency")
    rtype = str(payload.get("recurring_type") or "subscription")
    if rtype not in RECURRING_TYPES:
        raise HTTPException(status_code=422, detail="invalid recurring_type")
    return {
        "name": name,
        "merchant_id": payload.get("merchant_id"),
        "category_id": str(payload.get("category_id") or ""),
        "account_id": payload.get("account_id"),
        "expected_amount_text": amount,
        "merchant_name": payload.get("merchant_name") or name,
        "currency": validate_currency(str(payload.get("currency") or "CHF")),
        "frequency": freq,
        "expected_day_of_month": payload.get("expected_day_of_month"),
        "expected_month": payload.get("expected_month"),
        "tolerance_amount_text": require_decimal_text(str(payload["tolerance_amount_text"]), "tolerance_amount_text") if payload.get("tolerance_amount_text") not in (None, "") else None,
        "tolerance_percent": require_decimal_text(str(payload.get("tolerance_percent") or payload.get("amount_tolerance_pct") or "10"), "tolerance_percent"),
        "amount_tolerance_pct": require_decimal_text(str(payload.get("amount_tolerance_pct") or payload.get("tolerance_percent") or "10"), "amount_tolerance_pct"),
        "date_tolerance_days": int(payload.get("date_tolerance_days") or 5),
        "recurring_type": rtype,
        "notes": payload.get("notes"),
    }


def preview_manual_recurring_payment(conn: Connection, payload: dict[str, Any]) -> dict[str, Any]:
    norm = _validate_payload(payload)
    if not conn.execute("SELECT 1 FROM budget_categories WHERE category_id=? AND category_type='expense'", (norm["category_id"],)).fetchone():
        raise HTTPException(status_code=422, detail="expense category required")
    month, year = _expected_budget(_dec(norm["expected_amount_text"]), norm["frequency"])
    return {"preview_id": new_id("preview"), "summary": f"Fixkosten/Abo anlegen: {norm['name']}", "warnings": [], "payload": norm, "review": {"budget_month_chf": _fmt(month), "budget_year_chf": _fmt(year)}, "requires_explicit_confirm": True}


def confirm_manual_recurring_payment(conn: Connection, payload: dict[str, Any]) -> dict[str, Any]:
    norm = preview_manual_recurring_payment(conn, payload)["payload"]
    rid = new_id("rec")
    created = now()
    conn.execute("""
        INSERT INTO budget_recurring_payments(recurring_id,name,merchant_name,merchant_id,category_id,account_id,expected_amount_text,currency,frequency,expected_day_of_month,expected_month,tolerance_amount_text,tolerance_percent,amount_tolerance_pct,date_tolerance_days,recurring_type,status,source,confidence,last_seen_date,next_expected_date,notes,candidate_evidence_json,created_at,updated_at)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'active', 'manual', '1.00', NULL, NULL, ?, '{}', ?, ?)
    """, (rid, norm["name"], norm["merchant_name"], norm["merchant_id"], norm["category_id"], norm["account_id"], norm["expected_amount_text"], norm["currency"], norm["frequency"], norm["expected_day_of_month"], norm["expected_month"], norm["tolerance_amount_text"], norm["tolerance_percent"], norm["amount_tolerance_pct"], norm["date_tolerance_days"], norm["recurring_type"], norm["notes"], created, created))
    audit_id = record_audit_event(conn, source="vue_dashboard", action="recurring_manual_confirmed", entity_type="budget_recurring_payment", entity_id=rid, new_values=norm, created_by="user")
    conn.commit()
    return {"status": "confirmed", "entity_id": rid, "audit_id": audit_id, "message": "Fixkosten/Abo gespeichert"}


def _recurring_row(conn: Connection, recurring_id: str):
    row = conn.execute("SELECT * FROM budget_recurring_payments WHERE recurring_id=?", (recurring_id,)).fetchone()
    if not row:
        raise HTTPException(status_code=404, detail="recurring payment not found")
    return row


def preview_recurring_candidate_action(conn: Connection, candidate_id: str, payload: dict[str, Any]) -> dict[str, Any]:
    row = _recurring_row(conn, candidate_id)
    action = str(payload.get("action") or "")
    if action not in {"activate", "ignore", "edit", "pause", "archive"}:
        raise HTTPException(status_code=422, detail="invalid action")
    new_values = dict(row)
    if action == "activate":
        new_values["status"] = "active"
        if payload.get("recurring_type") in RECURRING_TYPES:
            new_values["recurring_type"] = payload["recurring_type"]
    elif action == "ignore":
        new_values["status"] = "ignored"
    elif action == "pause":
        new_values["status"] = "paused"
    elif action == "archive":
        new_values["status"] = "archived"
    elif action == "edit":
        for key in ("name", "merchant_name", "category_id", "account_id", "expected_amount_text", "currency", "frequency", "expected_day_of_month", "expected_month", "tolerance_amount_text", "tolerance_percent", "amount_tolerance_pct", "date_tolerance_days", "recurring_type", "notes"):
            if key in payload:
                new_values[key] = payload[key]
        if new_values.get("expected_amount_text"):
            new_values["expected_amount_text"] = require_decimal_text(str(new_values["expected_amount_text"]), "expected_amount_text")
        if new_values.get("frequency") not in FREQUENCIES:
            raise HTTPException(status_code=422, detail="invalid frequency")
        if new_values.get("recurring_type") not in RECURRING_TYPES:
            raise HTTPException(status_code=422, detail="invalid recurring_type")
    month, year = _expected_budget(_dec(new_values["expected_amount_text"]), str(new_values["frequency"]))
    return {"preview_id": new_id("preview"), "summary": f"Recurring-Kandidat {action}: {row['name']}", "payload": {"candidate_id": candidate_id, "action": action, "values": {k: new_values[k] for k in new_values.keys()}}, "warnings": [], "review": {"budget_month_chf": _fmt(month), "budget_year_chf": _fmt(year)}, "requires_explicit_confirm": True}


def confirm_recurring_candidate_action(conn: Connection, payload: dict[str, Any]) -> dict[str, Any]:
    rid = str(payload.get("candidate_id") or payload.get("values", {}).get("recurring_id") or "")
    old = dict(_recurring_row(conn, rid))
    action = str(payload.get("action"))
    values = payload.get("values") or old
    status = values.get("status") or ("active" if action == "activate" else "ignored")
    if status not in STATUSES:
        raise HTTPException(status_code=422, detail="invalid status")
    conn.execute("""
        UPDATE budget_recurring_payments
           SET name=?, merchant_name=?, category_id=?, account_id=?, expected_amount_text=?, currency=?, frequency=?, expected_day_of_month=?, expected_month=?, tolerance_amount_text=?, tolerance_percent=?, amount_tolerance_pct=?, date_tolerance_days=?, recurring_type=?, status=?, notes=?, updated_at=?
         WHERE recurring_id=?
    """, (values.get("name", old["name"]), values.get("merchant_name", old.get("merchant_name") or values.get("name", old["name"])), values.get("category_id", old["category_id"]), values.get("account_id", old["account_id"]), values.get("expected_amount_text", old["expected_amount_text"]), values.get("currency", old["currency"]), values.get("frequency", old["frequency"]), values.get("expected_day_of_month", old["expected_day_of_month"]), values.get("expected_month", old["expected_month"]), values.get("tolerance_amount_text", old.get("tolerance_amount_text")), values.get("tolerance_percent", old.get("tolerance_percent") or values.get("amount_tolerance_pct", old["amount_tolerance_pct"])), values.get("amount_tolerance_pct", old["amount_tolerance_pct"]), values.get("date_tolerance_days", old["date_tolerance_days"]), values.get("recurring_type", old["recurring_type"]), status, values.get("notes", old["notes"]), now(), rid))
    audit_id = record_audit_event(conn, source="vue_dashboard", action=f"recurring_{action}_confirmed", entity_type="budget_recurring_payment", entity_id=rid, old_values=old, new_values={"status": status, "recurring_type": values.get("recurring_type"), "name": values.get("name"), "expected_amount_text": values.get("expected_amount_text")}, created_by="user")
    conn.commit()
    return {"status": "confirmed" if status == "active" or action == "edit" else status, "entity_id": rid, "audit_id": audit_id, "message": "Recurring-Kandidat gespeichert"}


def archive_recurring_payment(conn: Connection, recurring_id: str, payload: dict[str, Any] | None = None) -> dict[str, Any]:
    old = dict(_recurring_row(conn, recurring_id))
    conn.execute("UPDATE budget_recurring_payments SET status='archived', notes=COALESCE(?, notes), updated_at=? WHERE recurring_id=?", ((payload or {}).get("notes"), now(), recurring_id))
    audit_id = record_audit_event(conn, source="vue_dashboard", action="recurring_archived", entity_type="budget_recurring_payment", entity_id=recurring_id, old_values=old, new_values={"status": "archived"}, created_by="user")
    conn.commit()
    return {"status": "archived", "entity_id": recurring_id, "audit_id": audit_id}


def _view(row: Any) -> dict[str, Any]:
    amount = _dec(row["expected_amount_text"])
    month, year = _expected_budget(amount, row["frequency"])
    item = dict(row)
    item["budget_month_chf"] = _fmt(month)
    item["budget_year_chf"] = _fmt(year)
    item["expected_amount_chf"] = _fmt(amount)
    return item


def list_recurring_warnings(conn: Connection, *, today: str | None = None) -> list[dict[str, Any]]:
    today_d = _date(today or date.today().isoformat())
    rows = conn.execute("SELECT * FROM budget_recurring_payments WHERE status='active'").fetchall()
    warnings: list[dict[str, Any]] = []
    for row in rows:
        r = dict(row)
        expected = _dec(r["expected_amount_text"])
        expected_day = r["expected_day_of_month"]
        recent = conn.execute("""
            SELECT * FROM budget_transactions WHERE status='confirmed' AND transaction_type IN ('expense','fee') AND category_id=? AND lower(COALESCE(payee, description,'')) LIKE lower(?) ORDER BY transaction_date DESC LIMIT 1
        """, (r["category_id"], f"%{r['name'].split()[0]}%" )).fetchone()
        if recent:
            seen = _date(recent["transaction_date"])
            nex = next_expected_date(seen, r["frequency"], expected_day, r["expected_month"])
            if nex:
                next_d = _date(nex)
                if today_d > next_d + timedelta(days=int(r["date_tolerance_days"] or 5)):
                    warnings.append({"status": "missing", "priority": "high" if expected >= Decimal("100") else "normal", "recurring_id": r["recurring_id"], "name": r["name"], "expected_date": nex})
                elif today_d <= next_d <= today_d + timedelta(days=7):
                    warnings.append({"status": "due_soon", "priority": "normal", "recurring_id": r["recurring_id"], "name": r["name"], "expected_date": nex})
            actual = _row_amount(recent)
            tol = expected * _dec(r["amount_tolerance_pct"]) / Decimal("100")
            if expected and abs(actual - expected) > tol:
                warnings.append({"status": "amount_changed", "priority": "normal", "recurring_id": r["recurring_id"], "name": r["name"], "expected_amount_chf": _fmt(expected), "actual_amount_chf": _fmt(actual)})
        else:
            warnings.append({"status": "missing", "priority": "normal", "recurring_id": r["recurring_id"], "name": r["name"]})
    for row in conn.execute("SELECT * FROM budget_recurring_payments WHERE status='candidate'").fetchall():
        warnings.append({"status": "new_candidate", "priority": "normal", "recurring_id": row["recurring_id"], "name": row["name"]})
    for row in conn.execute("SELECT * FROM budget_recurring_payments WHERE status='paused'").fetchall():
        warnings.append({"status": "inactive", "priority": "low", "recurring_id": row["recurring_id"], "name": row["name"]})
    seen_keys: dict[tuple[str, str], str] = {}
    for row in rows:
        key = (_similar_key(row["name"]), row["recurring_type"])
        if row["recurring_type"] == "subscription" and key in seen_keys:
            warnings.append({"status": "possible_duplicate_subscription", "priority": "normal", "recurring_id": row["recurring_id"], "duplicate_of": seen_keys[key], "name": row["name"]})
        else:
            seen_keys[key] = row["recurring_id"]
    return warnings


def get_recurring_dashboard(conn: Connection, *, today: str | None = None) -> dict[str, Any]:
    rows = conn.execute("SELECT r.*, c.name AS category_name FROM budget_recurring_payments r LEFT JOIN budget_categories c ON c.category_id=r.category_id ORDER BY r.status, r.name").fetchall()
    candidates = [_view(r) for r in rows if r["status"] == "candidate"]
    active = [_view(r) for r in rows if r["status"] == "active"]
    warnings = list_recurring_warnings(conn, today=today)
    by_cat: dict[str, dict[str, Any]] = {}
    subscriptions_year = Decimal("0"); fixed_year = Decimal("0")
    for item in active:
        month = _dec(item["budget_month_chf"]); year = _dec(item["budget_year_chf"])
        if item["recurring_type"] == "subscription": subscriptions_year += year
        if item["recurring_type"] == "fixed_cost": fixed_year += year
        cid = item["category_id"]
        b = by_cat.setdefault(cid, {"category_id": cid, "category": item.get("category_name"), "known_recurring_month_chf": Decimal("0"), "known_recurring_year_chf": Decimal("0"), "subscription_count": 0, "fixed_cost_count": 0})
        b["known_recurring_month_chf"] += month; b["known_recurring_year_chf"] += year
        if item["recurring_type"] == "subscription": b["subscription_count"] += 1
        if item["recurring_type"] == "fixed_cost": b["fixed_cost_count"] += 1
    category_breakdown = [{**v, "known_recurring_month_chf": _fmt(v["known_recurring_month_chf"]), "known_recurring_year_chf": _fmt(v["known_recurring_year_chf"])} for v in by_cat.values()]
    total_expense = sum((_dec(r["amount_chf"] or r["amount_original"]) for r in conn.execute("SELECT amount_chf, amount_original FROM budget_transactions WHERE status='confirmed' AND transaction_type IN ('expense','fee')").fetchall()), Decimal("0"))
    total_month = sum((_dec(a["budget_month_chf"]) for a in active), Decimal("0"))
    total_year = sum((_dec(a["budget_year_chf"]) for a in active), Decimal("0"))
    fixed_quote = Decimal("0") if total_expense == 0 else total_month / total_expense * Decimal("100")
    largest = sorted(active, key=lambda a: _dec(a["budget_year_chf"]), reverse=True)[:5]
    next_due = sorted((a for a in active if a.get("next_expected_date")), key=lambda a: str(a.get("next_expected_date")))
    kpis = {
        "monthly_fixed_costs_chf": _fmt(total_month),
        "annual_fixed_costs_chf": _fmt(total_year),
        "fixed_cost_quote_percent": _fmt(fixed_quote),
        "active_recurring_count": len(active),
        "open_candidate_count": len(candidates),
        "missing_payment_count": sum(1 for w in warnings if w["status"] == "missing"),
        "next_due": next_due[0] if next_due else None,
        "largest_fixed_costs": largest,
    }
    return {"purpose": "fixed_costs_subscriptions_v1", "candidates": candidates, "active": active, "warnings": warnings, "category_breakdown": category_breakdown, "kpis": kpis, "analytics": {"fixed_cost_quote_percent": _fmt(fixed_quote), "subscriptions_year_chf": _fmt(subscriptions_year), "fixed_costs_year_chf": _fmt(fixed_year), "top_subscriptions": [a for a in active if a["recurring_type"] == "subscription"][:10]}}


def classify_transaction_recurring_type(conn: Connection, row: Any) -> str:
    if row["transaction_type"] not in {"expense", "fee"}:
        return "unknown"
    merchant = str(row["payee"] or row["description"] or "")
    for rec in conn.execute("SELECT * FROM budget_recurring_payments WHERE status='active'").fetchall():
        if rec["category_id"] == row["category_id"] and _similar_key(rec["name"]) and _similar_key(rec["name"]) in _similar_key(merchant):
            return rec["recurring_type"]
    return "one_off"