from __future__ import annotations

from decimal import Decimal
from sqlite3 import Connection
from typing import Any

from jarvis_finance.imports.common import stable_id, utc_now


def _fmt(value: Decimal | str | None) -> str:
    return format(value, "f") if isinstance(value, Decimal) else str(value or "")


def upsert_equity_price_point(conn: Connection, *, instrument_id: str, timestamp: str, price: Decimal | str | None, currency: str, provider: str, provider_symbol: str | None = None, interval: str = "quote", source_quality: str = "fresh") -> str:
    now = utc_now()
    point_id = stable_id("eqpoint", instrument_id, provider, provider_symbol or "", timestamp, interval)
    conn.execute(
        """
        INSERT INTO equity_price_points(point_id, instrument_id, provider, provider_symbol, timestamp, price, currency, interval, source_quality, fetched_at)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        ON CONFLICT(instrument_id, provider, provider_symbol, timestamp, interval) DO UPDATE SET
            price=excluded.price, currency=excluded.currency, source_quality=excluded.source_quality, fetched_at=excluded.fetched_at
        """,
        (point_id, instrument_id, provider, provider_symbol, timestamp, _fmt(price), currency.upper(), interval, source_quality, now),
    )
    return point_id


def upsert_crypto_price_point(conn: Connection, *, asset_id: str, timestamp: str, price: Decimal | str | None, currency: str, provider: str, provider_symbol: str | None = None, interval: str = "quote", source_quality: str = "fresh") -> str:
    now = utc_now()
    point_id = stable_id("cryptopoint", asset_id, provider, provider_symbol or "", timestamp, interval, currency.upper())
    conn.execute(
        """
        INSERT INTO crypto_price_points(point_id, asset_id, provider, provider_symbol, timestamp, price, currency, interval, source_quality, fetched_at)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        ON CONFLICT(asset_id, provider, provider_symbol, timestamp, interval, currency) DO UPDATE SET
            price=excluded.price, source_quality=excluded.source_quality, fetched_at=excluded.fetched_at
        """,
        (point_id, asset_id, provider, provider_symbol, timestamp, _fmt(price), currency.upper(), interval, source_quality, now),
    )
    return point_id


def backfill_equity_points_from_market_prices(conn: Connection, instrument_id: str) -> int:
    rows = conn.execute("SELECT price_date, price_timestamp, close, currency, provider, provider_symbol, quality_status FROM market_prices WHERE instrument_id=? AND close IS NOT NULL AND close!='' ORDER BY COALESCE(price_timestamp, price_date)", (instrument_id,)).fetchall()
    count = 0
    for row in rows:
        upsert_equity_price_point(conn, instrument_id=instrument_id, timestamp=row["price_timestamp"] or row["price_date"], price=row["close"], currency=row["currency"] or "CHF", provider=row["provider"] or "local", provider_symbol=row["provider_symbol"], interval="eod", source_quality=row["quality_status"] or "fresh")
        count += 1
    conn.commit()
    return count


def backfill_crypto_points_from_crypto_prices(conn: Connection, asset_id: str, currency: str = "CHF") -> int:
    rows = conn.execute("SELECT fetched_at, provider_timestamp, price, price_currency, provider, coingecko_id, quality_status FROM crypto_prices WHERE asset_id=? AND price_currency=? AND price IS NOT NULL AND price!='' ORDER BY COALESCE(provider_timestamp, fetched_at)", (asset_id, currency.upper())).fetchall()
    count = 0
    for row in rows:
        upsert_crypto_price_point(conn, asset_id=asset_id, timestamp=row["provider_timestamp"] or row["fetched_at"], price=row["price"], currency=row["price_currency"] or currency.upper(), provider=row["provider"] or "CoinGecko", provider_symbol=row["coingecko_id"], interval="quote", source_quality=row["quality_status"] or "fresh")
        count += 1
    conn.commit()
    return count


def get_equity_chart_points(conn: Connection, instrument_id: str, *, limit: int = 365):
    if not conn.execute("SELECT 1 FROM equity_price_points WHERE instrument_id=? LIMIT 1", (instrument_id,)).fetchone():
        backfill_equity_points_from_market_prices(conn, instrument_id)
    return conn.execute("SELECT timestamp, price, currency, provider, source_quality FROM equity_price_points WHERE instrument_id=? ORDER BY timestamp DESC LIMIT ?", (instrument_id, limit)).fetchall()[::-1]


def get_crypto_chart_points(conn: Connection, asset_id: str, *, currency: str = "CHF", limit: int = 365):
    if not conn.execute("SELECT 1 FROM crypto_price_points WHERE asset_id=? AND currency=? LIMIT 1", (asset_id, currency.upper())).fetchone():
        backfill_crypto_points_from_crypto_prices(conn, asset_id, currency)
    return conn.execute("SELECT timestamp, price, currency, provider, source_quality FROM crypto_price_points WHERE asset_id=? AND currency=? ORDER BY timestamp DESC LIMIT ?", (asset_id, currency.upper(), limit)).fetchall()[::-1]


def upsert_equity_intraday_candles(
    conn: Connection,
    *,
    instrument_id: str,
    provider: str,
    provider_symbol: str,
    range_key: str,
    interval_key: str,
    candles: list[dict[str, Any]],
    currency: str | None = None,
    exchange_timezone: str | None = None,
    quality_status: str = "fresh",
) -> int:
    fetched_at = utc_now()
    count = 0
    for candle in candles:
        ts = str(candle["timestamp"])
        candle_id = stable_id("eqcandle", instrument_id, provider, provider_symbol, range_key, interval_key, ts)
        conn.execute(
            """
            INSERT INTO equity_intraday_candles(
                candle_id, instrument_id, provider, provider_symbol, range_key, interval_key,
                timestamp, open, close, low, high, volume, currency, exchange_timezone,
                quality_status, fetched_at
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            ON CONFLICT(instrument_id, provider, provider_symbol, range_key, interval_key, timestamp)
            DO UPDATE SET
                open=excluded.open, close=excluded.close, low=excluded.low, high=excluded.high,
                volume=excluded.volume, currency=excluded.currency,
                exchange_timezone=excluded.exchange_timezone, quality_status=excluded.quality_status,
                fetched_at=excluded.fetched_at
            """,
            (
                candle_id,
                instrument_id,
                provider,
                provider_symbol,
                range_key,
                interval_key,
                ts,
                _fmt(candle.get("open")),
                _fmt(candle.get("close")),
                _fmt(candle.get("low")),
                _fmt(candle.get("high")),
                _fmt(candle.get("volume")) if candle.get("volume") is not None else None,
                (currency or candle.get("currency") or "").upper() or None,
                exchange_timezone or candle.get("exchange_timezone"),
                quality_status,
                fetched_at,
            ),
        )
        count += 1
    return count


def get_equity_intraday_candles(conn: Connection, instrument_id: str, *, range_key: str = "1d", interval_key: str = "5m", max_age_minutes: int = 10):
    return conn.execute(
        """
        SELECT * FROM equity_intraday_candles
        WHERE instrument_id=? AND range_key=? AND interval_key=?
          AND fetched_at >= datetime('now', ?)
        ORDER BY timestamp
        """,
        (instrument_id, range_key, interval_key, f"-{int(max_age_minutes)} minutes"),
    ).fetchall()