from __future__ import annotations

from datetime import date, datetime, timezone
from decimal import Decimal, InvalidOperation
from pathlib import Path
from sqlite3 import Connection
from typing import Any

from jarvis_finance.config.settings import find_repo_root, load_settings
from jarvis_finance.services.budget_monthly_import import get_import_history
from jarvis_finance.services.budget_imports import candidate_user_count_summary
from jarvis_finance.services.budget_overview import get_budget_status_by_category
from jarvis_finance.services.budget_recurring import get_recurring_dashboard
from jarvis_finance.services.portfolio_service import get_overview
from jarvis_finance.services.cash_service import get_cash_summary

SOURCES = ["AKB", "Raiffeisen", "VISA", "Migros"]


def _d(value: Any) -> Decimal:
    try:
        return Decimal(str(value or "0").replace("'", ""))
    except (InvalidOperation, ValueError):
        return Decimal("0")


def _money(value: Any) -> str:
    return f"{abs(_d(value)):.2f}"


def _parse_month(month: str | None) -> date:
    value = month or datetime.now().strftime("%Y-%m")
    try:
        if len(value) != 7 or value[4] != "-":
            raise ValueError
        year = int(value[:4])
        mon = int(value[5:7])
        return date(year, mon, 1)
    except Exception as exc:
        raise ValueError("month_must_be_yyyy_mm") from exc


def _month_bounds(month: str | None) -> tuple[str, str, str]:
    start_date = _parse_month(month)
    next_month = date(start_date.year + (1 if start_date.month == 12 else 0), 1 if start_date.month == 12 else start_date.month + 1, 1)
    return start_date.strftime("%Y-%m"), start_date.isoformat(), next_month.isoformat()


def _sum_actual(conn: Connection, month: str, tx_type: str) -> Decimal:
    _month_label, start, end = _month_bounds(month)
    row = conn.execute(
        """
        SELECT COALESCE(SUM(ABS(CAST(amount_chf AS REAL))), 0)
        FROM budget_transactions
        WHERE status='confirmed'
          AND transaction_type=?
          AND date(transaction_date) >= date(?)
          AND date(transaction_date) < date(?)
          AND COALESCE(source_type, '') NOT IN ('transfer', 'credit_card_payment', 'investment_transfer')
        """,
        (tx_type, start, end),
    ).fetchone()
    return _d(row[0] if row else 0)


def _count(sql: str, conn: Connection, params: tuple[Any, ...] = ()) -> int:
    try:
        return int(conn.execute(sql, params).fetchone()[0] or 0)
    except Exception:
        return 0


def _review_url(**params: Any) -> str:
    qs = "&".join(f"{k}={v}" for k, v in params.items() if v not in (None, ""))
    return f"/planning/budget/expenses/review{('?' + qs) if qs else ''}"


def _latest_import_status(conn: Connection) -> list[dict[str, Any]]:
    history = get_import_history(conn, limit=100)
    by_source: dict[str, dict[str, Any]] = {}
    for h in history:
        src = str(h.get("source") or "").upper()
        label = "Raiffeisen" if "RAIFFEISEN" in src else "Migros" if "MIGROS" in src else "VISA" if "VISA" in src else "AKB" if "AKB" in src else src
        by_source.setdefault(label, h)
    out: list[dict[str, Any]] = []
    for source in SOURCES:
        h = by_source.get(source)
        out.append({
            "source": source,
            "status": "imported" if h else "missing",
            "last_import_at": h.get("created_at") if h else None,
            "file_name": h.get("file_name") if h else None,
            "new_candidates": int(h.get("new_candidates") or 0) if h else 0,
            "already_processed": int(h.get("already_known") or 0) if h else 0,
            "duplicates": int(h.get("duplicate_count") or 0) if h else 0,
            "errors": int(h.get("error_count") or 0) if h else 0,
            "monthly_import_url": "/planning/budget/import",
            "review_url": _review_url(source_type=str(h.get("profile") or "") if h else source.lower(), status="open"),
        })
    return out


def _budget_compact(conn: Connection, year: str) -> dict[str, Any]:
    try:
        rows = get_budget_status_by_category(conn, year=year)
    except Exception:
        rows = []
    def dec(row: dict[str, Any], key: str) -> Decimal: return _d(row.get(key))
    return {
        "top_overruns": sorted([r for r in rows if dec(r, "deviation_year") > 0], key=lambda r: dec(r, "deviation_year"), reverse=True)[:5],
        "categories_without_budget": [r.get("category") for r in rows if not r.get("has_budget")][:5],
        "categories_without_previous_year": [r.get("category") for r in rows if not r.get("has_previous_year")][:5],
        "forecast_risks": [r for r in rows if str(r.get("status", "")).lower() in {"rot", "red", "kritisch"}][:5],
        "budget_planning_url": "/planning/budget/planning",
        "budget_vs_actual_url": "/planning/budget/analysis/budget-vs-actual",
    }


def _fixed_costs_compact(conn: Connection, today: str | None = None) -> dict[str, Any]:
    try:
        data = get_recurring_dashboard(conn, today=today)
        active = data.get("active", [])
        warnings = data.get("warnings", [])
    except Exception:
        active, warnings = [], []
    return {
        "active_fixed_costs": sum(1 for r in active if r.get("recurring_type") == "fixed_cost"),
        "active_subscriptions": sum(1 for r in active if r.get("recurring_type") == "subscription"),
        "expected_payments_this_month": len(active),
        "missing_expected_payments": sum(1 for w in warnings if w.get("code") == "missing_expected_payment"),
        "amount_changed": sum(1 for w in warnings if w.get("code") == "amount_changed"),
        "warnings": warnings[:5],
        "url": "/planning/budget/fixed-costs",
    }


def _portfolio_crypto_compact(conn: Connection) -> dict[str, Any]:
    try:
        overview = get_overview(conn).model_dump()
    except Exception:
        overview = {}
    total = _d(overview.get("total_value_chf")) or Decimal("1")
    items = [
        {"label": "Cash", "value_chf": overview.get("cash_value_chf", "0.00"), "share_percent": f"{(_d(overview.get('cash_value_chf')) / total * 100):.2f}"},
        {"label": "Portfolio", "value_chf": overview.get("equity_value_chf", "0.00"), "share_percent": f"{(_d(overview.get('equity_value_chf')) / total * 100):.2f}"},
        {"label": "Crypto", "value_chf": overview.get("crypto_value_chf", "0.00"), "share_percent": f"{(_d(overview.get('crypto_value_chf')) / total * 100):.2f}"},
    ]
    return {
        "asset_allocation": items,
        "largest_positions": [],
        "missing_prices": int(overview.get("unpriced_positions_count") or 0),
        "stale_prices": 0,
        "last_price_update": overview.get("last_price_update"),
        "portfolio_url": "/portfolio",
        "crypto_url": "/crypto",
    }


def _todos(conn: Connection, import_status: list[dict[str, Any]], budget: dict[str, Any], fixed: dict[str, Any], kpis: dict[str, Any]) -> list[dict[str, Any]]:
    todos: list[dict[str, Any]] = []
    def add(priority: str, code: str, description: str, link: str, status: str = "open") -> None:
        todos.append({"priority": priority, "code": code, "description": description, "link": link, "status": status})
    if int(kpis.get("open_review_candidates") or 0):
        add("high", "open_review_candidates", "Offene Review-Kandidaten prüfen", _review_url(status="open"))
    if any(i["status"] == "missing" for i in import_status):
        add("medium", "missing_imports", "Datei-Import je Quelle prüfen", "/planning/budget/import")
    if budget.get("categories_without_budget"):
        add("medium", "missing_budget", "Kategorien ohne Budget ergänzen", "/planning/budget/planning")
    if budget.get("categories_without_previous_year"):
        add("low", "missing_previous_year", "Vorjahreswerte prüfen", "/planning/budget/planning")
    add("low", "backup", "Runtime-Backup prüfen/erstellen", "/reports")
    return todos


def build_finance_command_center(conn: Connection, month: str | None = None) -> dict[str, Any]:
    month, start, _end = _month_bounds(month)
    income = _sum_actual(conn, month, "income")
    expense = _sum_actual(conn, month, "expense")
    net = income - expense
    savings = (net / income * 100) if income else Decimal("0")
    try:
        overview = get_overview(conn).model_dump()
    except Exception:
        overview = {}
    year = month[:4]
    user_candidate_counts = candidate_user_count_summary(conn, budget_year=year)
    open_review = int(user_candidate_counts.get("open_total") or 0)
    open_review_amount = sum(
        (
            abs(_d(row["amount_original"]))
            for row in conn.execute(
                """SELECT amount_original FROM budget_transaction_candidates
                   WHERE status IN ('open','needs_review')
                     AND COALESCE(classification,'') NOT IN ('transfer_candidate','credit_card_payment','investment_transfer')"""
            ).fetchall()
        ),
        Decimal("0"),
    )
    try:
        cash_summary = get_cash_summary(conn)
    except Exception:
        cash_summary = None
    kpis = {
        "total_wealth_chf": _money(overview.get("total_value_chf")),
        "cash_available_chf": _money(overview.get("cash_value_chf")),
        "postfinance_equity_chf": _money(overview.get("postfinance_equity_value_chf") or overview.get("equity_value_chf")),
        "truewealth_chf": _money(overview.get("truewealth_value_chf")),
        "cash_reconciliation_open": bool(cash_summary and cash_summary.open_reconciliation_count),
        "portfolio_value_chf": _money(overview.get("equity_value_chf")),
        "crypto_value_chf": _money(overview.get("crypto_value_chf")),
        "income_month_chf": _money(income),
        "expense_month_chf": _money(expense),
        "net_cashflow_month_chf": f"{net:.2f}",
        "savings_rate_percent": f"{savings:.2f}",
        "budget_consumption_percent": "0.00",
        "open_review_candidates": open_review,
        "open_review_amount_chf": _money(open_review_amount),
        "missing_prices_fx": int(overview.get("unpriced_positions_count") or 0),
        "critical_alerts": int(overview.get("critical_alerts_count") or 0),
    }
    imports = _latest_import_status(conn)
    budget = _budget_compact(conn, year=month[:4])
    fixed = _fixed_costs_compact(conn, today=start)
    portfolio = _portfolio_crypto_compact(conn)
    return {
        "purpose": "finance_command_center_v1",
        "month": month,
        "kpis": kpis,

        "import_status": imports,
        "budget_status_compact": budget,
        "fixed_costs_compact": fixed,
        "portfolio_crypto_compact": portfolio,
        "todos": _todos(conn, imports, budget, fixed, kpis),
        "data_quality": {"status": overview.get("data_quality_status") or "ok", "issues": []},
    }


def generate_monthly_report(conn: Connection, month: str | None, reports_dir: Path | None = None, repo_root: Path | None = None) -> dict[str, Any]:
    month, _start, _end = _month_bounds(month)
    if reports_dir is None:
        settings = load_settings(repo_root=find_repo_root())
        reports_dir = settings.runtime_paths.reports_dir / "monthly"
    repo_root = (repo_root or find_repo_root()).resolve()
    reports_dir = reports_dir.resolve()
    if repo_root == reports_dir or repo_root in reports_dir.parents:
        raise ValueError("reports_dir_must_be_outside_repo")
    reports_dir.mkdir(parents=True, exist_ok=True)
    center = build_finance_command_center(conn, month=month)
    path = (reports_dir / f"monthly_report_{month}.md").resolve()
    if reports_dir not in path.parents:
        raise ValueError("report_path_must_stay_under_runtime_reports_dir")
    lines = [
        f"# Monatsreport {month}", "", "## Zeitraum", month, "", "## Cashflow",
        f"- Einnahmen: CHF {center['kpis']['income_month_chf']}",
        f"- Ausgaben: CHF {center['kpis']['expense_month_chf']}",
        f"- Netto-Cashflow: CHF {center['kpis']['net_cashflow_month_chf']}",
        "", "## Budget vs Ist", f"- Kritische Kategorien: {len(center['budget_status_compact']['forecast_risks'])}",
        "", "## Top Kategorien", "- Siehe Budgetstatus und Daten-Explorer.",
        "", "## Top Händler", "- Siehe Daten-Explorer.",
        "", "## Fixkosten/Abos", f"- Aktive Abos: {center['fixed_costs_compact']['active_subscriptions']}",
        "", "## Offene Review-Punkte", f"- Kandidaten: {center['kpis']['open_review_candidates']}",
        "", "## Portfolio/Crypto Summary", f"- Gesamtvermögen lokal: CHF {center['kpis']['total_wealth_chf']}",
        "", "## Datenqualität", f"- Status: {center['data_quality']['status']}",
        "", "## Disclaimer", "Technischer Haushalts-/Portfolio-Report auf bestätigten lokalen Daten. Keine Anlage-, Steuer- oder Rechtsberatung. Produktive Änderungen bleiben Preview → Confirm → Audit.",
    ]
    path.write_text("\n".join(lines), encoding="utf-8")
    return {"report_id": str(path.relative_to(reports_dir)), "path": str(path), "format": "markdown", "generated_at": datetime.now(timezone.utc).isoformat(), "runtime_only": True, "command_center": center}