from __future__ import annotations

from dataclasses import dataclass
from datetime import datetime, timezone
from sqlite3 import Connection

from jarvis_finance.quality.alerts import create_alert, utc_now

CRYPTO_PRICE_ALERT_RULES = {
    "crypto_price_stale",
    "crypto_price_missing",
    "crypto_price_missing_local",
}


@dataclass
class DataQualityCheckResult:
    scope: str
    dry_run: bool = False
    resolve_fixed: bool = False
    active_crypto_assets: int = 0
    fresh_price_assets: int = 0
    active_stale_price_alerts_before: int = 0
    active_stale_price_alerts_after: int = 0
    resolved_alerts: int = 0
    warnings: int = 0
    errors: int = 0
    missing_price_assets: int = 0


def _active_price_alert_count(conn: Connection) -> int:
    placeholders = ",".join("?" for _ in CRYPTO_PRICE_ALERT_RULES)
    return conn.execute(
        f"SELECT COUNT(*) AS n FROM alerts WHERE status='active' AND rule_id IN ({placeholders})",
        tuple(sorted(CRYPTO_PRICE_ALERT_RULES)),
    ).fetchone()["n"]


def _parse_dt(value: str | None) -> datetime | None:
    if not value:
        return None
    try:
        dt = datetime.fromisoformat(value.replace("Z", "+00:00"))
    except ValueError:
        return None
    return dt if dt.tzinfo else dt.replace(tzinfo=timezone.utc)


def _is_stale(timestamp: str | None, *, max_age_seconds: int) -> bool:
    dt = _parse_dt(timestamp)
    if dt is None:
        return True
    return (datetime.now(timezone.utc) - dt).total_seconds() > max_age_seconds


def _latest_crypto_price_row(conn: Connection, asset_id: str, currency: str = "CHF"):
    return conn.execute(
        """
        SELECT * FROM crypto_prices
        WHERE asset_id=? AND price_currency=?
        ORDER BY COALESCE(provider_timestamp, fetched_at, '') DESC, fetched_at DESC
        LIMIT 1
        """,
        (asset_id, currency.upper()),
    ).fetchone()


def has_fresh_crypto_price(conn: Connection, asset_id: str, *, currency: str = "CHF", max_age_seconds: int = 86_400) -> bool:
    latest = _latest_crypto_price_row(conn, asset_id, currency)
    return bool(latest and latest["quality_status"] == "fresh" and latest["price"] not in (None, "") and not _is_stale(latest["fetched_at"], max_age_seconds=max_age_seconds))


def resolve_fixed_crypto_price_alerts(
    conn: Connection,
    *,
    currency: str = "CHF",
    max_age_seconds: int = 86_400,
    dry_run: bool = False,
) -> int:
    """Resolve active stale/missing crypto-price alerts whose asset now has a fresh local price.

    Alert history is retained; rows are never deleted. Updating last_seen_at on resolution makes the
    lifecycle explicit without creating another alert occurrence.
    """
    placeholders = ",".join("?" for _ in CRYPTO_PRICE_ALERT_RULES)
    alerts = conn.execute(
        f"""
        SELECT alert_id, entity_id
        FROM alerts
        WHERE status='active'
          AND entity_type='crypto_asset'
          AND rule_id IN ({placeholders})
        ORDER BY created_at
        """,
        tuple(sorted(CRYPTO_PRICE_ALERT_RULES)),
    ).fetchall()
    now = utc_now()
    resolved = 0
    for alert in alerts:
        asset_id = alert["entity_id"]
        if asset_id and has_fresh_crypto_price(conn, asset_id, currency=currency, max_age_seconds=max_age_seconds):
            resolved += 1
            if not dry_run:
                conn.execute(
                    """
                    UPDATE alerts
                    SET status='resolved', resolved_at=?, last_seen_at=?
                    WHERE alert_id=? AND status='active'
                    """,
                    (now, now, alert["alert_id"]),
                )
    if resolved and not dry_run:
        conn.commit()
    return resolved


def check_crypto_data_quality(
    conn: Connection,
    *,
    currency: str = "CHF",
    max_age_seconds: int = 86_400,
    resolve_fixed: bool = False,
    dry_run: bool = False,
) -> DataQualityCheckResult:
    result = DataQualityCheckResult(scope="crypto", dry_run=dry_run, resolve_fixed=resolve_fixed)
    assets = conn.execute(
        "SELECT asset_id, symbol, coingecko_id FROM crypto_assets WHERE is_active=1 ORDER BY symbol"
    ).fetchall()
    result.active_crypto_assets = len(assets)
    result.active_stale_price_alerts_before = _active_price_alert_count(conn)

    for asset in assets:
        if has_fresh_crypto_price(conn, asset["asset_id"], currency=currency, max_age_seconds=max_age_seconds):
            result.fresh_price_assets += 1
            continue
        result.missing_price_assets += 1
        result.warnings += 1
        if not dry_run:
            create_alert(
                conn,
                priority="warnung",
                category="market_data",
                entity_type="crypto_asset",
                entity_id=asset["asset_id"],
                rule_id="crypto_price_missing_local",
                message="Crypto asset has no fresh local price during data quality recheck.",
                evidence={"symbol": asset["symbol"], "coingecko_id": asset["coingecko_id"], "currency": currency.upper()},
                fingerprint=f"crypto_price_missing_local:{currency.upper()}",
            )

    if resolve_fixed:
        result.resolved_alerts = resolve_fixed_crypto_price_alerts(
            conn,
            currency=currency,
            max_age_seconds=max_age_seconds,
            dry_run=dry_run,
        )
    if not dry_run:
        conn.commit()
    result.active_stale_price_alerts_after = _active_price_alert_count(conn)
    if dry_run and resolve_fixed:
        result.active_stale_price_alerts_after = max(0, result.active_stale_price_alerts_before - result.resolved_alerts)
    return result