"""ORM 模型。

Candle 表设计与 TimescaleDB hypertable 完全兼容：将来在目标 PG 库执行
  SELECT create_hypertable('candles', 'ts');
即可升级为时序表 + Continuous Aggregates 多周期预聚合，无需改表结构。

智能选股三表（stock_basic / market_daily / daily_snapshot）与回测 candles(qfq)
完全隔离：选股用未复权日线按 trade_date 全市场批量落地，避免污染回测复权缓存。
"""
from datetime import datetime

from sqlalchemy import BigInteger, Boolean, DateTime, Float, ForeignKey, Integer, String, Text, UniqueConstraint
from sqlalchemy.orm import Mapped, mapped_column, relationship

from .db import Base


def _utcnow() -> datetime:
    # naive UTC，避免 SQLite 存储时区带来的麻烦
    from datetime import timezone
    return datetime.now(timezone.utc).replace(tzinfo=None)


class Candle(Base):
    __tablename__ = "candles"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    symbol: Mapped[str] = mapped_column(String(16), index=True)
    timeframe: Mapped[str] = mapped_column(String(4), default="1d", index=True)
    ts: Mapped[datetime] = mapped_column(DateTime, index=True)  # bar 开始时间
    open: Mapped[float] = mapped_column(Float)
    high: Mapped[float] = mapped_column(Float)
    low: Mapped[float] = mapped_column(Float)
    close: Mapped[float] = mapped_column(Float)
    volume: Mapped[float] = mapped_column(Float)
    amount: Mapped[float | None] = mapped_column(Float)    # 成交额（元）；TDX 原生 float32
    turnover: Mapped[float | None] = mapped_column(Float)  # 换手率 %（daily_basic，2000 年起）

    __table_args__ = (
        UniqueConstraint("symbol", "timeframe", "ts", name="uq_candle_sym_tf_ts"),
    )


class BacktestRun(Base):
    """回测运行注册表（可复现/可审计/可回归对比的基础）。

    完整版应记录 策略版本 + 参数快照 + 数据快照（复权/数据源/库版本）+ 环境指纹 + 结果指纹。
    MVP 先落关键字段，结构就位。
    """
    __tablename__ = "backtest_runs"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    created_at: Mapped[datetime] = mapped_column(DateTime, default=_utcnow)
    symbol: Mapped[str] = mapped_column(String(16))
    strategy: Mapped[str] = mapped_column(String(64))
    timeframe: Mapped[str] = mapped_column(String(4), default="1d")
    params_json: Mapped[str] = mapped_column(String, default="{}")
    initial_cash: Mapped[float] = mapped_column(Float, default=100000.0)
    total_return: Mapped[float] = mapped_column(Float, default=0.0)
    max_drawdown: Mapped[float] = mapped_column(Float, default=0.0)
    sharpe: Mapped[float] = mapped_column(Float, default=0.0)
    num_trades: Mapped[int] = mapped_column(Integer, default=0)


class StockBasic(Base):
    """A股股票列表（stock_basic 快照；选股展示名称、排除 ST/退市/北交所的依据）。"""
    __tablename__ = "stock_basic"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    ts_code: Mapped[str] = mapped_column(String(12), unique=True, index=True)  # 000001.SZ
    symbol: Mapped[str] = mapped_column(String(10), index=True)                # 000001
    name: Mapped[str] = mapped_column(String(32))
    area: Mapped[str | None] = mapped_column(String(32))
    industry: Mapped[str | None] = mapped_column(String(32))
    market: Mapped[str | None] = mapped_column(String(32))                     # 主板/创业板/科创板/北交所
    exchange: Mapped[str] = mapped_column(String(8))                           # SSE/SZSE/BSE
    list_status: Mapped[str] = mapped_column(String(2), index=True)            # L上市 D退市 P暂停
    list_date: Mapped[str] = mapped_column(String(8), default="")
    delist_date: Mapped[str | None] = mapped_column(String(8))


class MarketDaily(Base):
    """全市场未复权日线（选股专用，与回测 candles(qfq) 隔离）。

    单位沿用 Tushare 原始：vol 手、amount 千元。
    """
    __tablename__ = "market_daily"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    trade_date: Mapped[datetime] = mapped_column(DateTime, index=True)
    ts_code: Mapped[str] = mapped_column(String(12), index=True)
    open: Mapped[float] = mapped_column(Float)
    high: Mapped[float] = mapped_column(Float)
    low: Mapped[float] = mapped_column(Float)
    close: Mapped[float] = mapped_column(Float)
    pre_close: Mapped[float] = mapped_column(Float)
    change: Mapped[float | None] = mapped_column(Float)
    pct_chg: Mapped[float | None] = mapped_column(Float)   # 日涨跌幅 %
    vol: Mapped[float] = mapped_column(Float)              # 手
    amount: Mapped[float] = mapped_column(Float)           # 千元

    __table_args__ = (
        UniqueConstraint("ts_code", "trade_date", name="uq_mkt_code_date"),
    )


class DailySnapshot(Base):
    """每日指标快照（daily_basic）。total_mv/circ_mv 单位万元（Tushare 原始），API 层换算亿元。"""
    __tablename__ = "daily_snapshot"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    trade_date: Mapped[datetime] = mapped_column(DateTime, index=True)
    ts_code: Mapped[str] = mapped_column(String(12), index=True)
    close: Mapped[float | None] = mapped_column(Float)
    turnover_rate: Mapped[float | None] = mapped_column(Float)     # 换手率 %
    turnover_rate_f: Mapped[float | None] = mapped_column(Float)   # 自由流通换手率 %
    volume_ratio: Mapped[float | None] = mapped_column(Float)      # 量比
    pe: Mapped[float | None] = mapped_column(Float)
    pe_ttm: Mapped[float | None] = mapped_column(Float)
    pb: Mapped[float | None] = mapped_column(Float)
    total_mv: Mapped[float | None] = mapped_column(Float)          # 总市值（万元）
    circ_mv: Mapped[float | None] = mapped_column(Float)           # 流通市值（万元）

    __table_args__ = (
        UniqueConstraint("ts_code", "trade_date", name="uq_snap_code_date"),
    )


class AdjFactor(Base):
    """复权因子（adj_factor，Tushare 原始值；qfq/hfq 本地换算的底座）。

    与 candles(不复权日线) 按 ts_code+trade_date 关联：
      前复权 qfq = 不复权价 × f(t) / f(latest)；后复权 hfq = 不复权价 × f(t)。
    """
    __tablename__ = "adj_factor"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    trade_date: Mapped[datetime] = mapped_column(DateTime, index=True)
    ts_code: Mapped[str] = mapped_column(String(12), index=True)
    adj_factor: Mapped[float] = mapped_column(Float)

    __table_args__ = (
        UniqueConstraint("ts_code", "trade_date", name="uq_adj_code_date"),
    )


class UserPreference(Base):
    """用户偏好键值对（配色/复权口径/MA 周期/副图布局等，value 存 JSON 字符串）。"""
    __tablename__ = "user_preferences"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    user_id: Mapped[int] = mapped_column(BigInteger, ForeignKey("users.id", ondelete="CASCADE"), index=True)
    key: Mapped[str] = mapped_column(String(64))
    value_json: Mapped[str] = mapped_column(Text, default="null")
    updated_at: Mapped[datetime] = mapped_column(DateTime, default=_utcnow, onupdate=_utcnow)

    __table_args__ = (
        UniqueConstraint("user_id", "key", name="uq_user_pref_key"),
    )


class WatchlistItem(Base):
    """自选股（星标置顶）。"""
    __tablename__ = "watchlist_items"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    user_id: Mapped[int] = mapped_column(BigInteger, ForeignKey("users.id", ondelete="CASCADE"), index=True)
    ts_code: Mapped[str] = mapped_column(String(12), index=True)
    created_at: Mapped[datetime] = mapped_column(DateTime, default=_utcnow)

    __table_args__ = (
        UniqueConstraint("user_id", "ts_code", name="uq_watch_user_code"),
    )


class ScreenerQuery(Base):
    """自然语言选股提问历史（文本 + 解析出的条件，便于一键重跑）。"""
    __tablename__ = "screener_queries"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    user_id: Mapped[int] = mapped_column(BigInteger, ForeignKey("users.id", ondelete="CASCADE"), index=True)
    text: Mapped[str] = mapped_column(String(500))
    conditions_json: Mapped[str | None] = mapped_column(Text)
    hit_count: Mapped[int | None] = mapped_column(Integer)
    created_at: Mapped[datetime] = mapped_column(DateTime, default=_utcnow, index=True)


class TradeCalendar(Base):
    """交易日历缓存（trade_cal 拉取一次宽范围后本地维护，低积分 token 限频 1 次/小时）。"""
    __tablename__ = "trade_calendar"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    trade_date: Mapped[str] = mapped_column(String(8), unique=True, index=True)  # YYYYMMDD


class User(Base):
    """后台登录用户。系统不提供注册接口，只能通过服务器命令创建或改密。"""

    __tablename__ = "users"

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    username: Mapped[str] = mapped_column(String(64), unique=True)
    password_hash: Mapped[str] = mapped_column(String(255))
    password_algo: Mapped[str] = mapped_column(String(16), default="argon2id")
    is_active: Mapped[bool] = mapped_column(Boolean, default=True, index=True)
    failed_login_count: Mapped[int] = mapped_column(Integer, default=0)
    locked_until: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    password_changed_at: Mapped[datetime] = mapped_column(DateTime(timezone=True))
    last_login_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True))
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True))

    sessions: Mapped[list["AuthSession"]] = relationship(
        back_populates="user", cascade="all, delete-orphan"
    )


class AuthSession(Base):
    """服务端会话。数据库只保存随机 Token 的 SHA-256 摘要。"""

    __tablename__ = "auth_sessions"

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    user_id: Mapped[int] = mapped_column(
        BigInteger, ForeignKey("users.id", ondelete="CASCADE"), index=True
    )
    token_hash: Mapped[str] = mapped_column(String(64), unique=True)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True))
    expires_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), index=True)
    last_seen_at: Mapped[datetime] = mapped_column(DateTime(timezone=True))
    revoked_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), index=True)
    ip_address: Mapped[str | None] = mapped_column(String(45))
    user_agent: Mapped[str | None] = mapped_column(String(512))

    user: Mapped[User] = relationship(back_populates="sessions")