from __future__ import annotations

from collections import Counter, defaultdict
from decimal import Decimal
from sqlite3 import Connection
from typing import Any

from jarvis_finance.services.budget_common import row_to_dict
from jarvis_finance.services.analytics_envelope import analytics_envelope
from jarvis_finance.services.budget_overview import _fmt, get_budget_status_by_category

ANALYTIC_TYPES = ("income", "refund", "expense", "fee")
INCOME_TYPES = ("income", "refund")
EXPENSE_TYPES = ("expense", "fee")


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


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


def _merchant(row: Any) -> str:
    return str(row["merchant_display_name"] or row["payee"] or row["description"] or row["source_type"] or "Quelle")


def _base_confirmed_rows(conn: Connection, filters: dict[str, str]) -> list[Any]:
    tx_mode = filters.get("type") or filters.get("tx_type") or "both"
    clauses = ["t.status='confirmed'", f"t.transaction_type IN ({','.join(['?'] * len(ANALYTIC_TYPES))})"]
    params: list[str] = list(ANALYTIC_TYPES)
    year = filters.get("year") or "2026"
    month = filters.get("month") or ""
    date_from = filters.get("date_from") or ""
    date_to = filters.get("date_to") or ""
    if date_from:
        clauses.append("t.transaction_date>=?"); params.append(date_from)
    if date_to:
        clauses.append("t.transaction_date<=?"); params.append(date_to)
    if month:
        clauses.append("substr(t.transaction_date,1,7)=?"); params.append(month)
    elif year:
        clauses.append("substr(t.transaction_date,1,4)=?"); params.append(year)
    if tx_mode == "income":
        clauses.append("t.transaction_type IN ('income','refund')")
    elif tx_mode == "expense":
        clauses.append("t.transaction_type IN ('expense','fee')")
    if filters.get("category_id"):
        clauses.append("t.category_id=?"); params.append(str(filters["category_id"]))
    if filters.get("account_id"):
        clauses.append("t.account_id=?"); params.append(str(filters["account_id"]))
    if filters.get("source_type"):
        clauses.append("t.source_type=?"); params.append(str(filters["source_type"]))
    if filters.get("merchant"):
        clauses.append("lower(COALESCE(t.payee,'') || ' ' || COALESCE(t.description,'') || ' ' || COALESCE(m.display_name,'')) LIKE lower(?)")
        params.append(f"%{filters['merchant']}%")
    if filters.get("search"):
        clauses.append("lower(COALESCE(t.payee,'') || ' ' || COALESCE(t.description,'') || ' ' || COALESCE(c.name,'') || ' ' || COALESCE(a.name,'')) LIKE lower(?)")
        params.append(f"%{filters['search']}%")
    if filters.get("amount_min"):
        clauses.append("CAST(COALESCE(t.amount_chf,t.amount_original,'0') AS REAL) >= CAST(? AS REAL)"); params.append(str(filters["amount_min"]))
    if filters.get("amount_max"):
        clauses.append("CAST(COALESCE(t.amount_chf,t.amount_original,'0') AS REAL) <= CAST(? AS REAL)"); params.append(str(filters["amount_max"]))
    if filters.get("tag"):
        clauses.append("EXISTS (SELECT 1 FROM budget_transaction_tags xt JOIN budget_tags xg ON xg.tag_id=xt.tag_id WHERE xt.budget_transaction_id=t.budget_transaction_id AND lower(xg.name) LIKE lower(?))")
        params.append(f"%{filters['tag']}%")
    return conn.execute(
        f"""
        SELECT t.*, a.name AS account_name, c.name AS category_name, c.category_type, c.color AS category_color, c.icon AS category_icon,
               COALESCE(m.display_name, t.payee, t.description, t.source_type, 'Quelle') AS merchant_display_name,
               GROUP_CONCAT(DISTINCT tag.name) AS tag_names_csv
        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 budget_merchants m ON m.merchant_id=t.merchant_id
        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 AND tag.is_active=1
        WHERE {' AND '.join(clauses)}
        GROUP BY t.budget_transaction_id
        ORDER BY t.transaction_date DESC, t.created_at DESC
        LIMIT 1000
        """,
        params,
    ).fetchall()


def _tx_view(row: Any) -> dict[str, Any]:
    item = row_to_dict(row)
    csv = str(item.pop("tag_names_csv", "") or "")
    item["tag_names"] = [x for x in csv.split(",") if x]
    item["merchant_display_name"] = _merchant(row)
    item["amount_chf"] = item.get("amount_chf") or item.get("amount_original") or "0"
    item["data_explorer_url"] = f"/planning/budget/analysis/data-explorer?year={str(row['transaction_date'])[:4]}&transaction_id={row['budget_transaction_id']}"
    if row["transaction_type"] in INCOME_TYPES:
        item["flow_label"] = "Einnahme"
    else:
        item["flow_label"] = "Ausgabe"
    return item


def get_budget_data_explorer(conn: Connection, **filters: str) -> dict[str, Any]:
    rows = _base_confirmed_rows(conn, filters)
    income = Decimal("0"); expense = Decimal("0"); total = Decimal("0")
    category_totals: dict[str, Decimal] = defaultdict(Decimal)
    merchant_totals: dict[str, Decimal] = defaultdict(Decimal)
    merchant_counts: Counter[str] = Counter()
    largest = Decimal("0"); largest_row: dict[str, Any] | None = None
    views = []
    for row in rows:
        amount = _amount(row)
        total += amount
        if row["transaction_type"] in INCOME_TYPES:
            income += amount
        elif row["transaction_type"] in EXPENSE_TYPES:
            expense += amount
        cat = str(row["category_name"] or "Review nötig")
        merch = _merchant(row)
        category_totals[cat] += amount
        merchant_totals[merch] += amount
        merchant_counts[merch] += 1
        view = _tx_view(row)
        try:
            from jarvis_finance.services.budget_recurring import classify_transaction_recurring_type
            view["recurring_type"] = classify_transaction_recurring_type(conn, row)
        except Exception:
            view["recurring_type"] = "unknown"
        requested_recurring_type = filters.get("recurring_type") or ""
        if requested_recurring_type and view["recurring_type"] != requested_recurring_type:
            continue
        views.append(view)
        if amount > largest:
            largest = amount; largest_row = view
    count = len(rows)
    avg = Decimal("0") if count == 0 else total / Decimal(count)
    top_category = max(category_totals.items(), key=lambda kv: kv[1])[0] if category_totals else "—"
    top_merchant = max(merchant_totals.items(), key=lambda kv: kv[1])[0] if merchant_totals else "—"
    merchant_rows = []
    previous_by_merchant = _previous_month_totals(conn, filters)
    for merchant, amount in sorted(merchant_totals.items(), key=lambda kv: kv[1], reverse=True)[:20]:
        m_rows = [r for r in rows if _merchant(r) == merchant]
        cats = Counter(str(r["category_name"] or "Review nötig") for r in m_rows)
        last_date = max(str(r["transaction_date"]) for r in m_rows) if m_rows else None
        prev = previous_by_merchant.get(merchant, Decimal("0"))
        change = amount - prev
        merchant_rows.append({
            "merchant": merchant,
            "transaction_count": merchant_counts[merchant],
            "sum_chf": _fmt(amount),
            "average_chf": _fmt(amount / Decimal(merchant_counts[merchant])),
            "last_transaction_date": last_date,
            "top_category": cats.most_common(1)[0][0] if cats else "—",
            "change_vs_previous_month_chf": _fmt(change),
        })
    fixed_variable = _fixed_variable_summary(conn, rows)
    legacy = {
        "purpose": "budget_data_explorer",
        "filters": {k: v for k, v in filters.items() if v},
        "kpis": {
            "income_sum_chf": _fmt(income),
            "expense_sum_chf": _fmt(expense),
            "net_cashflow_chf": _fmt(income - expense),
            "transaction_count": len(views),
            "average_transaction_chf": _fmt(Decimal("0") if len(views) == 0 else sum((_dec(v.get("amount_chf") or v.get("amount_original") or "0") for v in views), Decimal("0")) / Decimal(len(views))),
            "largest_transaction_chf": _fmt(largest),
            "largest_transaction_id": largest_row["budget_transaction_id"] if largest_row else None,
            "top_category": top_category,
            "top_merchant": top_merchant,
        },
        "transactions": views,
        "top_merchants": merchant_rows,
        "fixed_variable": fixed_variable,
        "export": {"enabled": False, "target": "runtime_exports_only", "message": "CSV Export wird runtime-only vorbereitet; in v1 als Roadmap-Button deaktiviert."},
    }
    return analytics_envelope(legacy, view="data_explorer", filters=legacy["filters"], totals=legacy["kpis"], series=merchant_rows, rows=views)


def _previous_month_totals(conn: Connection, filters: dict[str, str]) -> dict[str, Decimal]:
    month = filters.get("month") or ""
    if not month:
        return {}
    year, mon = month.split("-")
    m = int(mon) - 1
    y = int(year)
    if m == 0:
        y -= 1; m = 12
    prev_month = f"{y:04d}-{m:02d}"
    prev_filters = dict(filters) | {"month": prev_month}
    rows = _base_confirmed_rows(conn, prev_filters)
    out: dict[str, Decimal] = defaultdict(Decimal)
    for row in rows:
        out[_merchant(row)] += _amount(row)
    return out


def _fixed_variable_summary(conn: Connection, rows: list[Any]) -> dict[str, Any]:
    fixed_categories = {str(r["category_id"]) for r in conn.execute("SELECT DISTINCT category_id FROM budget_plan_items WHERE is_active=1 AND is_fixed_cost=1").fetchall()}
    fixed = variable = one_off = subscription = unclassified = Decimal("0")
    for row in rows:
        if row["transaction_type"] not in EXPENSE_TYPES:
            continue
        amount = _amount(row)
        notes = str(row["notes"] or "").lower()
        cid = str(row["category_id"] or "")
        if cid in fixed_categories or "fixed" in notes or "fixkosten" in notes:
            fixed += amount
        elif "one_off" in notes or "einmal" in notes:
            one_off += amount
        elif "subscription_candidate" in notes or "abo" in notes:
            subscription += amount
        elif "variable" in notes:
            variable += amount
        else:
            unclassified += amount
    total_expense = fixed + variable + one_off + subscription + unclassified
    return {
        "fixed_cost_chf": _fmt(fixed),
        "variable_chf": _fmt(variable),
        "one_off_chf": _fmt(one_off),
        "subscription_candidate_chf": _fmt(subscription),
        "unclassified_chf": _fmt(unclassified),
        "fixed_cost_ratio_percent": _fmt(Decimal("0") if total_expense == 0 else fixed / total_expense * Decimal("100")),
        "classification_status": "teilweise klassifiziert" if any([fixed, variable, one_off, subscription]) else "noch nicht klassifiziert",
    }


def get_category_analysis_v2(conn: Connection, *, year: str = "2026", month: str | None = None, tx_type: str = "expense") -> dict[str, Any]:
    from jarvis_finance.services.budget_analytics import get_category_analysis
    base = get_category_analysis(conn, year=year, month=month, tx_type=tx_type)
    cats = base["categories"]
    top = cats[:10]
    other_amount = sum((_dec(c["amount_chf"]) for c in cats[10:]), Decimal("0"))
    other_count = sum((int(c["transaction_count"]) for c in cats[10:]), 0)
    top10_other = list(top)
    total = _dec(base["total_chf"])
    if other_amount:
        top10_other.append({"category_id": "__other", "category": "Andere", "amount_chf": _fmt(other_amount), "share_percent": _fmt(Decimal("0") if total == 0 else other_amount / total * Decimal("100")), "transaction_count": other_count, "color": "#94a3b8", "icon": None, "transactions_url": f"/planning/budget/analysis/data-explorer?year={year}"})
    filters = {"year": year, "type": "income" if tx_type == "income" else "expense"}
    rows = _base_confirmed_rows(conn, filters)
    trend: dict[str, dict[str, Decimal]] = {f"{year}-{i:02d}": defaultdict(Decimal) for i in range(1, 13)}
    top_names = {c["category"] for c in top[:5]}
    for row in rows:
        cat = str(row["category_name"] or "Review nötig")
        if cat not in top_names:
            continue
        trend[str(row["transaction_date"])[:7]][cat] += _amount(row)
    trend_rows = [{"month": month_key, "categories": {name: _fmt(value) for name, value in values.items()}} for month_key, values in sorted(trend.items())]
    legacy = base | {"top10_other": top10_other, "trend_by_top_category": trend_rows, "mode_options": ["expense", "income", "net"]}
    return analytics_envelope(legacy, view="category_analysis", filters={"year": year, "month": month or "", "tx_type": tx_type}, totals={"total_chf": legacy.get("total_chf", "0.00"), "category_count": len(cats)}, series=trend_rows, rows=legacy.get("categories", []))


def get_merchant_analysis(conn: Connection, **filters: str) -> dict[str, Any]:
    data = get_budget_data_explorer(conn, **filters)
    selected = filters.get("merchant") or (data["top_merchants"][0]["merchant"] if data["top_merchants"] else "")
    rows = _base_confirmed_rows(conn, filters | {"merchant": selected} if selected else filters)
    monthly: dict[str, Decimal] = {f"{filters.get('year') or '2026'}-{i:02d}": Decimal("0") for i in range(1, 13)}
    for row in rows:
        key = str(row["transaction_date"])[:7]
        if key in monthly:
            monthly[key] += _amount(row)
    return {"purpose": "merchant_analysis", "top_merchants": data["top_merchants"], "selected_merchant": selected, "merchant_trend": [{"month": k, "amount_chf": _fmt(v)} for k, v in monthly.items()]}


def get_budget_deviation_analysis(conn: Connection, *, year: str = "2026") -> dict[str, Any]:
    status_rows = get_budget_status_by_category(conn, year=year)
    expenses = [r for r in status_rows if r.get("category_type") == "expense"]
    over = []
    under = []
    no_budget = []
    for r in expenses:
        actual = _dec(r.get("actual_year_to_date"))
        actual_month = _dec(r.get("actual_current_month"))
        budget_month = _dec(r.get("budget_month"))
        budget = _dec(r.get("budget_year"))
        forecast = _dec(r.get("forecast_year"))
        deviation = forecast - budget
        month_deviation = actual_month - budget_month
        item = r | {"budget_year": _fmt(budget), "forecast_year": _fmt(forecast), "deviation_chf": _fmt(deviation), "traffic_light": r.get("status") or "Grau"}
        if not r.get("has_budget") and actual > 0:
            no_budget.append(item)
        elif month_deviation > 0 or deviation > 0:
            over.append(item | {"deviation_chf": _fmt(month_deviation if month_deviation > deviation else deviation), "deviation_scope": "month" if month_deviation >= deviation else "year"})
        elif budget > 0 and deviation < 0:
            under.append(item | {"available_chf": _fmt(abs(deviation))})
    return {"purpose": "budget_deviation_analysis", "year": year, "top_over_budget": sorted(over, key=lambda x: _dec(x["deviation_chf"]), reverse=True)[:10], "top_under_budget": sorted(under, key=lambda x: _dec(x.get("available_chf")), reverse=True)[:10], "categories_without_budget": no_budget}