"""SQL strategy-lifecycle store — implements StrategyStore over SQLAlchemy. Maps the domain StrategyRecord
<-> ORM row; the domain never sees SQLAlchemy. Holds the GLOBAL trial counter in a meta row."""
from __future__ import annotations

from sqlalchemy import create_engine, select
from sqlalchemy.orm import Session

from fxhnt.adapters.persistence.sql_models import Base, FactoryMetaRow, SeenCandidateRow, StrategyRow
from fxhnt.domain.factory import RegimeFit, StrategyRecord, StrategyStatus
from fxhnt.domain.models import AssetClass, StrategySpec

_TRIALS_KEY = "total_trials"


class SqlStrategyStore:
    def __init__(self, dsn: str) -> None:
        self._engine = create_engine(dsn, future=True)
        Base.metadata.create_all(self._engine)

    @staticmethod
    def _to_row(r: StrategyRecord) -> StrategyRow:
        return StrategyRow(
            strategy_id=r.strategy_id, markets=list(r.markets), asset_class=r.asset_class.value,
            strategy_kind=r.spec.kind, params=dict(r.spec.params),
            regime_fits=[f.model_dump() for f in r.regime_fits], status=r.status.value,
            discovered_at=r.discovered_at, updated_at=r.updated_at,
            n_trials_at_discovery=r.n_trials_at_discovery, source=r.source,
            forward_started=r.forward_started, forward_last_date=r.forward_last_date,
            forward_days=r.forward_days, forward_sum=r.forward_sum, forward_sumsq=r.forward_sumsq,
            forward_returns=list(r.forward_returns),
        )

    @staticmethod
    def _to_domain(row: StrategyRow) -> StrategyRecord:
        return StrategyRecord(
            strategy_id=row.strategy_id, markets=list(row.markets), asset_class=AssetClass(row.asset_class),
            spec=StrategySpec(kind=row.strategy_kind, params=row.params),
            regime_fits=[RegimeFit(**f) for f in row.regime_fits], status=StrategyStatus(row.status),
            discovered_at=row.discovered_at, updated_at=row.updated_at,
            n_trials_at_discovery=row.n_trials_at_discovery, source=row.source,
            forward_started=row.forward_started or "", forward_last_date=row.forward_last_date or "",
            forward_days=row.forward_days or 0, forward_sum=row.forward_sum or 0.0,
            forward_sumsq=row.forward_sumsq or 0.0, forward_returns=list(row.forward_returns or []),
        )

    def upsert(self, record: StrategyRecord) -> None:
        with Session(self._engine) as s:
            s.merge(self._to_row(record))
            s.commit()

    def get(self, strategy_id: str) -> StrategyRecord | None:
        with Session(self._engine) as s:
            row = s.get(StrategyRow, strategy_id)
            return self._to_domain(row) if row else None

    def list(self, *, status: StrategyStatus | None = None, regime: str | None = None) -> list[StrategyRecord]:
        stmt = select(StrategyRow).order_by(StrategyRow.updated_at.desc())
        if status is not None:
            stmt = stmt.where(StrategyRow.status == status.value)
        with Session(self._engine) as s:
            records = [self._to_domain(r) for r in s.scalars(stmt)]
        if regime is not None:  # JSON membership is awkward cross-dialect — filter in Python
            records = [r for r in records if regime in r.specialist_regimes()]
        return records

    def set_status(self, strategy_id: str, status: StrategyStatus) -> None:
        with Session(self._engine) as s:
            row = s.get(StrategyRow, strategy_id)
            if row is not None:
                row.status = status.value
                s.commit()

    def add_trials(self, n: int) -> None:
        with Session(self._engine) as s:
            row = s.get(FactoryMetaRow, _TRIALS_KEY)
            if row is None:
                s.add(FactoryMetaRow(key=_TRIALS_KEY, value=n))
            else:
                row.value += n
            s.commit()

    def total_trials(self) -> int:
        with Session(self._engine) as s:
            row = s.get(FactoryMetaRow, _TRIALS_KEY)
            return int(row.value) if row else 0

    def is_seen(self, key: str) -> bool:
        with Session(self._engine) as s:
            return s.get(SeenCandidateRow, key) is not None

    def mark_seen(self, key: str) -> None:
        with Session(self._engine) as s:
            if s.get(SeenCandidateRow, key) is None:
                s.add(SeenCandidateRow(key=key))
                s.commit()
