"""DuckDB analytical store — columnar timeseries (market prices + backtest returns). Implements
AnalyticalStore. Embedded file with a single PERSISTENT connection (connect-per-op is far too slow
on a growing file — it checkpoints each open/close)."""
from __future__ import annotations

import duckdb
import numpy as np

from fxhnt.domain.models import Market, PriceSeries

_SCHEMA = [
    "CREATE TABLE IF NOT EXISTS prices (symbol VARCHAR, asset_class VARCHAR, date VARCHAR, close DOUBLE)",
    "CREATE TABLE IF NOT EXISTS returns (run_id VARCHAR, date VARCHAR, ret DOUBLE)",
]
_MIGRATIONS = [
    "ALTER TABLE prices ADD COLUMN IF NOT EXISTS high DOUBLE",
    "ALTER TABLE prices ADD COLUMN IF NOT EXISTS low DOUBLE",
]


class DuckDbAnalyticalStore:
    def __init__(self, path: str) -> None:
        self._con = duckdb.connect(path)
        for ddl in _SCHEMA:
            self._con.execute(ddl)
        for ddl in _MIGRATIONS:                     # additive migration: safe on new and existing DBs
            self._con.execute(ddl)

    def save_prices(self, prices: PriceSeries) -> None:
        m = prices.market
        rows = [(m.symbol, m.asset_class.value, d, float(c), float(h), float(lo))
                for d, c, h, lo in zip(prices.dates, prices.close, prices.high, prices.low)]
        self._con.execute("DELETE FROM prices WHERE symbol = ? AND asset_class = ?",
                          [m.symbol, m.asset_class.value])
        self._con.executemany(
            "INSERT INTO prices (symbol, asset_class, date, close, high, low) VALUES (?, ?, ?, ?, ?, ?)",
            rows)

    def load_prices(self, market: Market) -> PriceSeries | None:
        rows = self._con.execute(
            "SELECT date, close, COALESCE(high, close), COALESCE(low, close) FROM prices "
            "WHERE symbol = ? AND asset_class = ? ORDER BY date",
            [market.symbol, market.asset_class.value],
        ).fetchall()
        if not rows:
            return None
        return PriceSeries(market=market, dates=tuple(r[0] for r in rows),
                           close=np.array([r[1] for r in rows], dtype=float),
                           high=np.array([r[2] for r in rows], dtype=float),
                           low=np.array([r[3] for r in rows], dtype=float))

    def save_returns(self, run_id: str, dates: tuple[str, ...], returns: np.ndarray) -> None:
        rows = [(run_id, d, float(r)) for d, r in zip(dates, returns)]
        self._con.execute("DELETE FROM returns WHERE run_id = ?", [run_id])
        self._con.executemany("INSERT INTO returns VALUES (?, ?, ?)", rows)

    def close(self) -> None:
        self._con.close()
