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


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 (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, 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)
    stock = dashboard_data.d(summary.get("stock_total_chf"))
    etf = dashboard_data.d(summary.get("etf_total_chf"))
    truewealth = _latest_truewealth_value(conn)
    postfinance_equity = _postfinance_equity_value(conn)
    equity = postfinance_equity if postfinance_equity else stock + etf
    crypto = dashboard_data.d(summary.get("crypto_total_chf"))
    cash = dashboard_data.d(get_cash_summary(conn).cash_chf)
    total = cash + crypto + equity + truewealth
    return PortfolioSummary(
        total_value_chf=decimal_text(total, 2),
        crypto_value_chf=decimal_text(crypto, 2),
        equity_value_chf=decimal_text(equity, 2),
        postfinance_equity_value_chf=decimal_text(equity, 2),
        truewealth_value_chf=decimal_text(truewealth, 2),
        cash_value_chf=decimal_text(cash, 2),
        unpriced_positions_count=int(summary.get("unvalued_position_count") or 0),
        critical_alerts_count=int(summary.get("critical_alert_count") or 0),
        last_price_update=summary.get("last_price_update_at") or None,
        data_quality_status=user_quality_status(summary.get("data_quality_status")),
    )