from __future__ import annotations

from decimal import Decimal
from sqlite3 import Connection

from jarvis_finance.api.schemas.overview import PortfolioSummary
from jarvis_finance.dashboard import data as dashboard_data
from jarvis_finance.services.api_helpers import decimal_text, user_quality_status
from jarvis_finance.services.cash_service import get_cash_summary
from jarvis_finance.services.equity_service import get_equity_summary


def _latest_truewealth_value(conn: Connection) -> Decimal:
    row = conn.execute(
        """
        SELECT avs.total_value_chf
        FROM account_value_snapshots avs
        JOIN accounts a ON a.account_id = avs.account_id
        JOIN platforms p ON p.platform_id = a.platform_id
        WHERE a.is_active = 1
          AND COALESCE(avs.is_active,1) = 1
          AND (lower(p.name || ' ' || a.account_name) LIKE '%true%wealth%' OR lower(a.account_type) IN ('managed_portfolio','robo_portfolio'))
        ORDER BY avs.valuation_date DESC,
                 CASE avs.source_type WHEN 'truewealth_official_import' THEN 3 WHEN 'truewealth_manual_provisional' THEN 2 ELSE 1 END DESC,
                 COALESCE(avs.valuation_at,avs.created_at) DESC, avs.created_at DESC
        LIMIT 1
        """
    ).fetchone()
    return dashboard_data.d(row["total_value_chf"]) if row else Decimal("0")


def _postfinance_equity_value(conn: Connection) -> Decimal:
    row = conn.execute(
        """
        SELECT COALESCE(SUM(CAST(t.gross_amount_chf AS REAL)), 0) AS value_chf
        FROM transactions t
        JOIN accounts a ON a.account_id = t.account_id
        JOIN platforms p ON p.platform_id = a.platform_id
        JOIN instruments i ON i.instrument_id = t.instrument_id
        WHERE COALESCE(t.is_voided,0)=0
          AND COALESCE(a.is_active,1)=1
          AND lower(i.asset_class) IN ('stock','etf')
          AND lower(p.name || ' ' || a.account_name) NOT LIKE '%true%wealth%'
          AND lower(a.account_type) NOT IN ('managed_portfolio','robo_portfolio')
        """
    ).fetchone()
    return dashboard_data.d(row["value_chf"]) if row else Decimal("0")


def get_overview(conn: Connection) -> PortfolioSummary:
    summary = dashboard_data.get_command_center_summary(conn)
    truewealth = _latest_truewealth_value(conn)
    equity_summary = get_equity_summary(conn)
    if equity_summary.as_of is None:
        # Legacy/read-model compatibility before the first audited market run.
        equity = _postfinance_equity_value(conn)
        equity_partial = equity
        equity_complete = True
    else:
        equity_partial = dashboard_data.d(equity_summary.valued_partial_chf)
        equity = dashboard_data.d(equity_summary.equity_value_chf) if equity_summary.coverage_complete else None
        equity_complete = equity_summary.coverage_complete
    crypto = dashboard_data.d(summary.get("crypto_total_chf"))
    cash = dashboard_data.d(get_cash_summary(conn).cash_chf)
    partial_total = cash + crypto + equity_partial + truewealth
    total = partial_total if equity is not None else None
    return PortfolioSummary(
        total_value_chf=decimal_text(total, 2) if total is not None else None,
        crypto_value_chf=decimal_text(crypto, 2),
        equity_value_chf=decimal_text(equity, 2) if equity is not None else None,
        postfinance_equity_value_chf=decimal_text(equity, 2) if equity is not None else None,
        truewealth_value_chf=decimal_text(truewealth, 2),
        cash_value_chf=decimal_text(cash, 2),
        unpriced_positions_count=equity_summary.unvalued_positions,
        critical_alerts_count=int(summary.get("critical_alert_count") or 0),
        last_price_update=equity_summary.last_successful_run or summary.get("last_price_update_at") or None,
        data_quality_status="ok" if equity_complete else user_quality_status("partial"),
    )