"""SQLAlchemy 2.0 ORM models for the operational store. JSON columns work on both Postgres and SQLite,
so the same models serve production (Postgres) and dev/test (SQLite)."""
from __future__ import annotations

import datetime as dt

from sqlalchemy import JSON, Boolean, DateTime, Float, Integer, String
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column


class Base(DeclarativeBase):
    pass


class ResearchRunRow(Base):
    __tablename__ = "research_runs"

    run_id: Mapped[str] = mapped_column(String(64), primary_key=True)
    symbol: Mapped[str] = mapped_column(String(32), index=True)
    asset_class: Mapped[str] = mapped_column(String(16))
    venue: Mapped[str] = mapped_column(String(16))
    currency: Mapped[str] = mapped_column(String(8))
    strategy_kind: Mapped[str] = mapped_column(String(32), index=True)
    params: Mapped[dict] = mapped_column(JSON)
    cagr: Mapped[float] = mapped_column(Float)
    ann_vol: Mapped[float] = mapped_column(Float)
    sharpe: Mapped[float] = mapped_column(Float)
    max_drawdown: Mapped[float] = mapped_column(Float)
    n_obs: Mapped[int] = mapped_column(Integer)
    passed: Mapped[bool] = mapped_column(Boolean, index=True)
    dsr: Mapped[float] = mapped_column(Float)
    is_sharpe: Mapped[float] = mapped_column(Float)
    oos_sharpe: Mapped[float] = mapped_column(Float)
    n_trials: Mapped[int] = mapped_column(Integer)
    reasons: Mapped[list] = mapped_column(JSON)
    created_at: Mapped[dt.datetime] = mapped_column(DateTime)


class StrategyRow(Base):
    """The factory's strategy-lifecycle population (regime-tagged specialists + status)."""
    __tablename__ = "strategies"

    strategy_id: Mapped[str] = mapped_column(String(64), primary_key=True)
    markets: Mapped[list] = mapped_column(JSON)
    asset_class: Mapped[str] = mapped_column(String(16))
    strategy_kind: Mapped[str] = mapped_column(String(32), index=True)
    params: Mapped[dict] = mapped_column(JSON)
    regime_fits: Mapped[list] = mapped_column(JSON)   # [{regime, dsr, is_sharpe, oos_sharpe, passed}, ...]
    status: Mapped[str] = mapped_column(String(24), index=True)
    discovered_at: Mapped[dt.datetime] = mapped_column(DateTime)
    updated_at: Mapped[dt.datetime] = mapped_column(DateTime)
    n_trials_at_discovery: Mapped[int] = mapped_column(Integer)
    source: Mapped[str] = mapped_column(String(64))
    forward_started: Mapped[str] = mapped_column(String(16), default="")
    forward_last_date: Mapped[str] = mapped_column(String(16), default="")
    forward_days: Mapped[int] = mapped_column(Integer, default=0)
    forward_sum: Mapped[float] = mapped_column(Float, default=0.0)
    forward_sumsq: Mapped[float] = mapped_column(Float, default=0.0)
    forward_returns: Mapped[list] = mapped_column(JSON, default=list)


class FactoryMetaRow(Base):
    """Key/value for factory-wide counters — notably the GLOBAL trial count (honesty at scale)."""
    __tablename__ = "factory_meta"

    key: Mapped[str] = mapped_column(String(32), primary_key=True)
    value: Mapped[int] = mapped_column(Integer)


class SeenCandidateRow(Base):
    """Every candidate hypothesis ever tested — so re-running the fleet does NOT re-inflate the global
    trial count or waste work re-testing. Honest distinct-hypothesis accounting across cycles."""
    __tablename__ = "seen_candidates"

    key: Mapped[str] = mapped_column(String(96), primary_key=True)
