from __future__ import annotations

from decimal import Decimal
from sqlite3 import Connection

from jarvis_finance.services.budget_common import row_to_dict
from jarvis_finance.services.analytics_envelope import analytics_envelope


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


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


def get_category_analysis(conn: Connection, *, year: str = "2026", month: str | None = None, tx_type: str = "expense") -> dict:
    mode = "income" if tx_type == "income" else "expense"
    types = ("income", "refund") if mode == "income" else ("expense", "fee")
    clauses = ["t.status='confirmed'", f"t.transaction_type IN ({','.join(['?'] * len(types))})"]
    params: list[str] = list(types)
    if month:
        clauses.append("substr(t.transaction_date,1,7)=?")
        params.append(month)
    else:
        clauses.append("substr(t.transaction_date,1,4)=?")
        params.append(year)
    rows = conn.execute(
        f"""
        SELECT COALESCE(c.category_id, 'uncategorized') AS category_id,
               COALESCE(c.name, 'Review nötig') AS category,
               COALESCE(c.color, '') AS color,
               COALESCE(c.icon, '') AS icon,
               COUNT(*) AS transaction_count,
               SUM(CAST(COALESCE(t.amount_chf, t.amount_original, '0') AS TEXT)) AS amount_sum
        FROM budget_transactions t
        LEFT JOIN budget_categories c ON c.category_id=t.category_id
        WHERE {' AND '.join(clauses)}
        GROUP BY COALESCE(c.category_id, 'uncategorized'), COALESCE(c.name, 'Review nötig'), COALESCE(c.color, ''), COALESCE(c.icon, '')
        ORDER BY CAST(amount_sum AS REAL) DESC, category
        """,
        params,
    ).fetchall()
    items = []
    total = Decimal("0")
    for row in rows:
        amount = Decimal(str(row["amount_sum"] or "0"))
        total += amount
        items.append(row_to_dict(row) | {"amount": amount})
    categories = []
    for item in items:
        share = Decimal("0") if total == 0 else (item["amount"] / total * Decimal("100"))
        category_id = str(item["category_id"])
        base_url = "/planning/budget/income/actual" if mode == "income" else "/planning/budget/expenses/actual"
        url = f"{base_url}?year={year}&category_id={category_id}"
        if month:
            url += f"&month={month}"
        categories.append({
            "category_id": category_id,
            "category": item["category"],
            "amount_chf": _fmt(item["amount"]),
            "share_percent": _fmt(share),
            "transaction_count": int(item["transaction_count"] or 0),
            "color": item.get("color") or None,
            "icon": item.get("icon") or None,
            "transactions_url": url,
        })
    return {"year": year, "month": month, "mode": mode, "total_chf": _fmt(total), "categories": categories}


def get_monthly_comparison(conn: Connection, *, year: str = "2026") -> dict:
    tx_rows = conn.execute(
        """
        SELECT t.*, c.name AS category_name, COALESCE(t.payee, t.description, t.source_type, 'Quelle') AS merchant_display_name
        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,4)=?
          AND t.transaction_type IN ('income','refund','expense','fee')
        ORDER BY t.transaction_date
        """,
        (year,),
    ).fetchall()
    months: dict[str, dict] = {}
    for idx in range(1, 13):
        key = f"{year}-{idx:02d}"
        months[key] = {"month": key, "income": Decimal("0"), "expense": Decimal("0"), "transaction_count": 0, "categories": {}, "merchants": {}, "largest": []}
    for row in tx_rows:
        key = str(row["transaction_date"])[:7]
        if key not in months:
            continue
        item = months[key]
        amount = _amount(row)
        item["transaction_count"] += 1
        item["largest"].append({"description": row["description"], "merchant": row["merchant_display_name"] or "Quelle", "amount_chf": _fmt(amount), "transaction_type": row["transaction_type"]})
        if row["transaction_type"] in {"income", "refund"}:
            item["income"] += amount
        else:
            item["expense"] += amount
            cat = row["category_name"] or "Review nötig"
            merchant = row["merchant_display_name"] or "Quelle"
            item["categories"][cat] = item["categories"].get(cat, Decimal("0")) + amount
            item["merchants"][merchant] = item["merchants"].get(merchant, Decimal("0")) + amount
    out = []
    cum_income = Decimal("0")
    cum_expense = Decimal("0")
    for key in sorted(months):
        item = months[key]
        net = item["income"] - item["expense"]
        cum_income += item["income"]
        cum_expense += item["expense"]
        savings_rate = Decimal("0") if item["income"] == 0 else (net / item["income"] * Decimal("100"))
        top_categories = [{"category": name, "amount_chf": _fmt(value)} for name, value in sorted(item["categories"].items(), key=lambda kv: kv[1], reverse=True)[:5]]
        top_merchants = [{"merchant": name, "amount_chf": _fmt(value)} for name, value in sorted(item["merchants"].items(), key=lambda kv: kv[1], reverse=True)[:5]]
        largest = sorted(item["largest"], key=lambda r: Decimal(str(r["amount_chf"])), reverse=True)[:5]
        out.append({
            "month": key,
            "income_chf": _fmt(item["income"]),
            "expense_chf": _fmt(item["expense"]),
            "net_cashflow_chf": _fmt(net),
            "savings_rate_percent": _fmt(savings_rate),
            "cumulative_income_chf": _fmt(cum_income),
            "cumulative_expense_chf": _fmt(cum_expense),
            "transaction_count": int(item["transaction_count"]),
            "top_categories": top_categories,
            "top_merchants": top_merchants,
            "largest_transactions": largest,
            "transactions_url": f"/planning/budget/analysis/data-explorer?year={year}&month={key}",
        })
    legacy = {"year": year, "months": out}
    totals = {
        "income_chf": _fmt(sum((Decimal(m["income_chf"]) for m in out), Decimal("0"))),
        "expense_chf": _fmt(sum((Decimal(m["expense_chf"]) for m in out), Decimal("0"))),
        "transaction_count": sum(int(m["transaction_count"]) for m in out),
    }
    return analytics_envelope(legacy, view="monthly_comparison", filters={"year": year}, totals=totals, series=out, rows=out)