"""Repository for the cockpit's forward tables. Idempotent upserts (re-ingest is safe). `migrate()` creates
the tables and, on Postgres only, promotes forward_nav to a TimescaleDB hypertable (guarded; on SQLite it
stays a plain table so tests run without Postgres)."""
from __future__ import annotations

import datetime as dt

from sqlalchemy import create_engine, select, text
from sqlalchemy.orm import Session
from sqlalchemy.pool import StaticPool

from fxhnt.adapters.persistence.cockpit_models import (
    CockpitBase,
    ForwardNavRow,
    ForwardSummaryRow,
    StrategyRegistryRow,
)
from fxhnt.application.forward_models import ForwardNavRow as NavRowDTO
from fxhnt.application.forward_models import ForwardSummary
from fxhnt.registry import STRATEGY_REGISTRY


class ForwardNavRepo:
    def __init__(self, dsn: str) -> None:
        # StaticPool keeps a single in-memory SQLite connection alive across sessions; only applied for the
        # in-memory SQLite case (tests). StaticPool is NOT used for Postgres.
        kw: dict = {"future": True}
        if dsn == "sqlite://" or (dsn.startswith("sqlite://") and ":memory:" in dsn):
            kw |= {"connect_args": {"check_same_thread": False}, "poolclass": StaticPool}
        self._engine = create_engine(dsn, **kw)
        self._is_pg = self._engine.dialect.name == "postgresql"

    def migrate(self) -> None:
        CockpitBase.metadata.create_all(self._engine)
        if self._is_pg:
            with self._engine.begin() as c:
                c.execute(text("CREATE EXTENSION IF NOT EXISTS timescaledb"))
                c.execute(text(
                    "SELECT create_hypertable('forward_nav', by_range('date'), if_not_exists => TRUE)"
                ))
        self._seed_registry()

    def _seed_registry(self) -> None:
        with Session(self._engine) as s:
            for sid, e in STRATEGY_REGISTRY.items():
                s.merge(StrategyRegistryRow(
                    strategy_id=sid, display_name=e["display_name"], sleeve=e["sleeve"],
                    venue=e.get("venue", ""), capital_base=float(e.get("capital_base", 0.0)),
                    gate_spec=e["gate_spec"], enabled=True))
            s.commit()

    def upsert_rows(self, rows: list[NavRowDTO], at: dt.datetime) -> None:
        with Session(self._engine) as s:
            for r in rows:
                s.merge(ForwardNavRow(strategy_id=r.strategy_id,
                                      date=dt.date.fromisoformat(r.date),  # DTO str → DB DATE
                                      ret=r.ret, nav=r.nav, src_updated_at=at))
            s.commit()

    def upsert_summary(self, sm: ForwardSummary, gate_status: str, gate_reason: str,
                       at: dt.datetime) -> None:
        with Session(self._engine) as s:
            s.merge(ForwardSummaryRow(
                strategy_id=sm.strategy_id, as_of=sm.as_of, days=sm.days, nav=sm.nav,
                total_return=sm.total_return, sharpe=sm.sharpe, maxdd=sm.maxdd,
                gate_status=gate_status, gate_reason=gate_reason, src_updated_at=at))
            s.commit()

    def all_nav_histories(self) -> dict[str, list[NavRowDTO]]:
        """One query for every strategy's nav series, grouped by strategy_id (avoids N+1 on the cockpit)."""
        stmt = select(ForwardNavRow).order_by(ForwardNavRow.strategy_id, ForwardNavRow.date)
        out: dict[str, list[NavRowDTO]] = {}
        with Session(self._engine) as s:
            for r in s.scalars(stmt):
                out.setdefault(r.strategy_id, []).append(
                    NavRowDTO(strategy_id=r.strategy_id, date=r.date.isoformat(), ret=r.ret, nav=r.nav))
        return out

    def nav_history(self, strategy_id: str) -> list[NavRowDTO]:
        stmt = (select(ForwardNavRow).where(ForwardNavRow.strategy_id == strategy_id)
                .order_by(ForwardNavRow.date))
        with Session(self._engine) as s:
            return [NavRowDTO(strategy_id=r.strategy_id,
                              date=r.date.isoformat(),  # DB DATE → DTO ISO str (datetime.date on both PG and SQLite)
                              ret=r.ret, nav=r.nav)
                    for r in s.scalars(stmt)]

    def all_summaries(self) -> list[ForwardSummaryRow]:
        with Session(self._engine) as s:
            return list(s.scalars(select(ForwardSummaryRow).order_by(ForwardSummaryRow.strategy_id)))

    def registry(self) -> list[StrategyRegistryRow]:
        with Session(self._engine) as s:
            return list(s.scalars(select(StrategyRegistryRow).order_by(StrategyRegistryRow.strategy_id)))
