from __future__ import annotations

import json
from collections.abc import Mapping
from decimal import Decimal
from sqlite3 import Connection

from fastapi import HTTPException

from jarvis_finance.api.schemas.positions import EquityPortfolioSummary, EquityPosition
from jarvis_finance.dashboard import data as dashboard_data
from jarvis_finance.services.api_helpers import optional_decimal_text


def _asset_class_label(value: object) -> str:
    return "ETF" if str(value or "").lower() == "etf" else "Aktie"


def _account_label(row: dict[str, str]) -> str:
    platform = str(row.get("platform") or "").strip()
    account = str(row.get("account_name") or "").strip()
    if "postfinance" in platform.lower():
        if not account or "manual" in account.lower():
            return "PostFinance E-Trading"
        return account if "postfinance" in account.lower() or "e-trading" in account.lower() else f"PostFinance · {account}"
    return account or platform


def _latest_analysis(conn: Connection) -> tuple[dict[str, object] | None, dict[tuple[str, str], dict[str, object]], dict[str, str]]:
    row = conn.execute(
        """SELECT pas.*,mdr.completed_at,mdr.missing_instruments_json
             FROM portfolio_analysis_snapshots pas
             JOIN market_data_runs mdr ON mdr.run_id=pas.run_id
            ORDER BY pas.as_of DESC,pas.created_at DESC LIMIT 1"""
    ).fetchone()
    if not row:
        return None, {}, {}
    try:
        payload = json.loads(row["summary_json"] or "{}")
    except json.JSONDecodeError:
        payload = {}
    values = {
        (str(item.get("account_id") or ""), str(item.get("instrument_id") or "")): item
        for item in payload.get("positions", [])
        if isinstance(item, dict)
    }
    try:
        missing_rows = json.loads(row["missing_instruments_json"] or "[]")
    except json.JSONDecodeError:
        missing_rows = []
    missing = {
        str(item.get("instrument_id") or ""): str(item.get("reason_code") or "price_missing")
        for item in missing_rows
        if isinstance(item, dict)
    }
    return dict(row), values, missing


def _status_reason(code: str) -> str:
    labels = {
        "fmp_endpoint_restricted": "Der primäre Provider beschränkt diesen historischen Kurs im aktuellen Tarif.",
        "plan_restricted": "Der Provider beschränkt diesen historischen Kurs im aktuellen Tarif.",
        "fmp_rate_limited": "Das Provider-Limit wurde erreicht. Der Lauf kann kontrolliert fortgesetzt werden.",
        "rate_limited": "Das Provider-Limit wurde erreicht. Der Lauf kann kontrolliert fortgesetzt werden.",
        "currency_mismatch": "Die Kurswährung stimmt nicht mit der bestätigten Instrumentwährung überein.",
        "exchange_mismatch": "Der Handelsplatz des Provider-Ergebnisses ist nicht bestätigt.",
        "future_price_rejected": "Der gelieferte Kurs liegt nach dem zulässigen Börsenstichtag.",
        "stale_price": "Der letzte bestätigte Kurs ist für den Börsenstichtag zu alt und wird nicht bewertet.",
        "mapping_required": "Die Provider-Zuordnung ist noch nicht eindeutig bestätigt.",
        "fx_rate_missing": "Für die CHF-Bewertung fehlt ein bestätigter FX-Kurs.",
    }
    return labels.get(code, "Für den Börsenstichtag konnte noch kein bestätigter Kurs geladen werden.")


def _position_status(row: Mapping[str, object]) -> str:
    """Compatibility status when no audited market-analysis snapshot exists yet."""

    if "quantity" in row and Decimal(str(row.get("quantity") or "0")) <= 0:
        return "Einstand unvollständig"
    market_value = row.get("market_value_chf")
    warnings = str(row.get("quality_warnings") or "")
    if market_value not in {None, ""}:
        if "missing_cost_basis" in warnings or "missing_fx" in warnings:
            return "Einstand unvollständig"
        return "Bewertet"
    if str(row.get("price_status") or "") != "ok":
        return "Kurs fehlt"
    if str(row.get("fx_status") or "") == "missing_fx":
        return "FX fehlt für Bewertung"
    return "Bewertung unvollständig"


def _mapping_details(conn: Connection, instrument_id: str) -> dict[str, str | None]:
    row = conn.execute(
        """SELECT provider,provider_symbol,provider_market,upper(COALESCE(trading_currency,currency,'')) currency
             FROM instrument_price_mappings
            WHERE instrument_id=? AND mapping_status='mapped'
            ORDER BY updated_at DESC,mapping_id LIMIT 1""",
        (instrument_id,),
    ).fetchone()
    return dict(row) if row else {"provider": None, "provider_symbol": None, "provider_market": None, "currency": None}


def _analysis_value_is_eligible(
    analysis: Mapping[str, object] | None,
    candidate: Mapping[str, object] | None,
    row: Mapping[str, object],
) -> bool:
    if not analysis or not candidate or candidate.get("quality_status") != "fresh":
        return False
    analysis_as_of = str(analysis.get("as_of") or "")
    row_as_of = str(row.get("valuation_as_of") or row.get("price_date") or "")
    if row.get("valuation_source") == "postfinance_official_import":
        return analysis_as_of > row_as_of
    return analysis_as_of >= row_as_of


def list_equity_positions(conn: Connection) -> list[EquityPosition]:
    source_rows = [
        row for row in dashboard_data.get_positions(conn)
        if row.get("asset_class", "").lower() in {"stock", "equity", "etf"}
        and row.get("instrument_status", "").lower() != "inactive"
    ]
    analysis, values, missing = _latest_analysis(conn)
    effective_values: dict[tuple[str, str], Decimal | None] = {}
    for row in source_rows:
        key = (str(row.get("account_id") or ""), str(row.get("instrument_id") or ""))
        candidate = values.get(key)
        row_is_audited = row.get("valuation_source") in {
            "postfinance_official_import",
            "audited_market_run",
        }
        candidate_is_newer = _analysis_value_is_eligible(analysis, candidate, row)
        raw = (
            candidate.get("value_chf")
            if candidate_is_newer and candidate
            else row.get("market_value_chf") if analysis is None or row_is_audited else None
        )
        effective_values[key] = Decimal(str(raw)) if raw not in {None, ""} else None
    complete = bool(source_rows) and all(value is not None for value in effective_values.values())
    total = sum((value or Decimal("0") for value in effective_values.values()), Decimal("0"))
    positions: list[EquityPosition] = []
    for row in source_rows:
        account_id = str(row.get("account_id") or "")
        instrument_id = str(row.get("instrument_id") or "")
        snapshot_value = values.get((account_id, instrument_id))
        value = (
            snapshot_value
            if _analysis_value_is_eligible(analysis, snapshot_value, row)
            else None
        )
        row_fallback_allowed = analysis is None or row.get("valuation_source") in {
            "postfinance_official_import",
            "audited_market_run",
        }
        mapping = _mapping_details(conn, instrument_id)
        raw_market_value = (
            value.get("value_chf")
            if value
            else row.get("market_value_chf") if row_fallback_allowed else None
        )
        if Decimal(str(row.get("quantity") or "0")) <= 0:
            raw_market_value = None
        market_value = Decimal(str(raw_market_value)) if raw_market_value not in {None, ""} else None
        share = (market_value / total * Decimal("100")) if complete and market_value is not None and total > 0 else None
        code = "valued" if market_value is not None else ("stale_price" if snapshot_value else missing.get(instrument_id, "price_missing"))
        status = "Bewertet" if market_value is not None else ("Kurs veraltet" if snapshot_value else _position_status(row))
        provenance = value.get("price_input_provenance") if value else None
        provenance_provider = provenance.get("provider") if isinstance(provenance, dict) else None
        positions.append(EquityPosition(
            position_id=f"{account_id}:{instrument_id}",
            instrument_id=instrument_id,
            name=str(row.get("name") or ""),
            ticker=str(row.get("ticker") or ""),
            isin=str(row.get("isin") or ""),
            account=_account_label(row),
            asset_class=_asset_class_label(row.get("asset_class")),
            quantity=optional_decimal_text(row.get("quantity")) or "0",
            currency=str(value.get("currency") if value else mapping.get("currency") or row.get("price_currency") or row.get("currency") or ""),
            price=(
                optional_decimal_text(value.get("close"))
                if value
                else optional_decimal_text(row.get("market_price_original") or row.get("price"))
                if row_fallback_allowed
                else None
            ),
            market_value_chf=optional_decimal_text(market_value, 2) if market_value is not None else None,
            portfolio_share_pct=optional_decimal_text(share, 2) if share is not None else None,
            price_date=(
                str(analysis.get("as_of"))
                if value and analysis
                else str(row.get("valuation_as_of") or row.get("price_date") or "") or None
                if row_fallback_allowed
                else str(analysis.get("as_of")) if analysis else None
            ),
            provider=str((value.get("provider") if value else None) or provenance_provider or row.get("price_provider") or mapping.get("provider") or ""),
            provider_symbol=str(mapping.get("provider_symbol") or ""),
            exchange=str(mapping.get("provider_market") or ""),
            price_currency=str(value.get("currency") if value else mapping.get("currency") or row.get("price_currency") or row.get("currency") or ""),
            data_status="fresh" if market_value is not None else "missing",
            status_code=code,
            status_reason=None if market_value is not None else _status_reason(code),
            status=status,
        ))
    return sorted(positions, key=lambda item: Decimal(item.market_value_chf or "-1"), reverse=True)


def get_equity_summary(conn: Connection) -> EquityPortfolioSummary:
    positions = list_equity_positions(conn)
    valued = [item for item in positions if item.market_value_chf is not None]
    partial = sum((Decimal(item.market_value_chf or "0") for item in valued), Decimal("0"))
    complete = bool(positions) and len(valued) == len(positions)
    analysis, _, _ = _latest_analysis(conn)
    status = "complete" if complete else ("partial" if positions else "unavailable")
    effective_as_of = max((item.price_date or "" for item in valued), default="") or None
    analysis_as_of = str(analysis.get("as_of") or "") if analysis else ""
    response_as_of = effective_as_of or analysis_as_of or None
    last_successful = (
        str(analysis.get("completed_at"))
        if analysis and (not effective_as_of or analysis_as_of >= str(effective_as_of)[:10])
        else effective_as_of
    )
    return EquityPortfolioSummary(
        total_positions=len(positions),
        valued_positions=len(valued),
        unvalued_positions=len(positions) - len(valued),
        coverage_complete=complete,
        equity_value_chf=optional_decimal_text(partial, 2) if complete else None,
        valued_partial_chf=optional_decimal_text(partial, 2) or "0.00",
        value_label="Aktien-/ETF-Gesamtwert" if complete else "Bewerteter Teilwert",
        as_of=response_as_of,
        last_successful_run=last_successful,
        status=status,
        status_label=f"{len(valued)}/{len(positions)} bewertet" if positions else "Keine Aktien-/ETF-Positionen",
        unvalued_tickers=[item.ticker for item in positions if item.market_value_chf is None],
    )


def get_equity_position(conn: Connection, position_id: str) -> EquityPosition:
    position = next((p for p in list_equity_positions(conn) if p.position_id == position_id), None)
    if position is None:
        raise HTTPException(status_code=404, detail="Equity position not found")
    return position