as-event/backend/app/db.py

"""自有库（Postgres）ORM 模型与会话。

看板只读自有库；ETL 把内网库数据同步进这里。
建表：正式部署用 ddl/own_db_init.sql；本模块也提供 init_db() 供开发期 create_all。
"""
from __future__ import annotations

from datetime import date, datetime
from typing import Iterator

from sqlalchemy import (
    BigInteger,
    Date,
    DateTime,
    Integer,
    Numeric,
    String,
    Text,
    UniqueConstraint,
    create_engine,
    func,
)
from sqlalchemy.orm import DeclarativeBase, Mapped, Session, mapped_column, sessionmaker

from .config import get_settings

settings = get_settings()

engine = create_engine(settings.own_db_dsn, pool_pre_ping=True, future=True)
SessionLocal = sessionmaker(bind=engine, autoflush=False, expire_on_commit=False, future=True)


class Base(DeclarativeBase):
    pass


class IndexDaily(Base):
    """指数日线（同步落地）。OHLC 缺源时 open=high=low=close，ohlc_source='close_only'。"""

    __tablename__ = "index_daily"
    __table_args__ = (UniqueConstraint("index_code", "trade_date", name="uq_index_daily"),)

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    index_code: Mapped[str] = mapped_column(String(16), index=True)
    trade_date: Mapped[date] = mapped_column(Date, index=True)
    open: Mapped[float | None] = mapped_column(Numeric(16, 4))
    high: Mapped[float | None] = mapped_column(Numeric(16, 4))
    low: Mapped[float | None] = mapped_column(Numeric(16, 4))
    close: Mapped[float | None] = mapped_column(Numeric(16, 4))
    volume: Mapped[int | None] = mapped_column(BigInteger)          # 成交量(手)，如源有
    amount: Mapped[float | None] = mapped_column(Numeric(24, 4))    # 成交额(元)
    pct_chg: Mapped[float | None] = mapped_column(Numeric(10, 4))   # 涨跌幅 %
    turnover_rate: Mapped[float | None] = mapped_column(Numeric(10, 4))
    ohlc_source: Mapped[str] = mapped_column(String(24), default="close_only")
    updated_at: Mapped[datetime] = mapped_column(DateTime, server_default=func.now())


class SentimentDaily(Base):
    """情绪日度（源自 gp_market_sentiment，可扩展两融/开户等）。"""

    __tablename__ = "sentiment_daily"

    trade_date: Mapped[date] = mapped_column(Date, primary_key=True)
    up_down_ratio: Mapped[float | None] = mapped_column(Numeric(10, 4))   # 涨跌家数比
    median_pct_chg: Mapped[float | None] = mapped_column(Numeric(10, 4))  # 全市场涨跌幅中位数
    pct_chg_gt_5_count: Mapped[int | None] = mapped_column(Integer)       # 涨幅>5%家数（涨停潮代理）
    limit_up_count: Mapped[int | None] = mapped_column(Integer)
    limit_down_count: Mapped[int | None] = mapped_column(Integer)
    margin_balance: Mapped[float | None] = mapped_column(Numeric(24, 4))  # 两融余额(元)
    new_accounts: Mapped[int | None] = mapped_column(Integer)             # 新增开户数
    sentiment_score: Mapped[float | None] = mapped_column(Numeric(10, 4)) # 归一情绪分 0-100
    updated_at: Mapped[datetime] = mapped_column(DateTime, server_default=func.now())


class EtfFlow(Base):
    """ETF 流入（人工/导入/自动）。可按单只 ETF 或按类别聚合存。"""

    __tablename__ = "etf_flow"
    __table_args__ = (
        UniqueConstraint("trade_date", "etf_code", name="uq_etf_flow"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    trade_date: Mapped[date] = mapped_column(Date, index=True)
    etf_code: Mapped[str] = mapped_column(String(24), default="")     # 空串=类别聚合行
    etf_name: Mapped[str | None] = mapped_column(String(64))
    category: Mapped[str | None] = mapped_column(String(32))          # broad/chinext/star/...
    related_index: Mapped[str | None] = mapped_column(String(16))     # 关联指数 dot 式
    net_inflow: Mapped[float | None] = mapped_column(Numeric(20, 4))  # 净流入(亿元)
    shares_change: Mapped[float | None] = mapped_column(Numeric(20, 4))  # 份额变化(亿份)
    source: Mapped[str] = mapped_column(String(16), default="manual") # manual/import/auto
    note: Mapped[str | None] = mapped_column(Text)
    created_at: Mapped[datetime] = mapped_column(DateTime, server_default=func.now())


class MarketEvent(Base):
    """重大事件（证监会抓人、巨无霸IPO、政策等）。"""

    __tablename__ = "market_event"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    event_date: Mapped[date] = mapped_column(Date, index=True)
    title: Mapped[str] = mapped_column(String(255))
    category: Mapped[str] = mapped_column(String(32), default="其他")  # 监管/IPO/政策/资金/外部/其他
    impact_direction: Mapped[str] = mapped_column(String(16), default="neutral")  # bullish/bearish/neutral
    severity: Mapped[int] = mapped_column(Integer, default=3)          # 1-5
    related_indices: Mapped[str | None] = mapped_column(String(128))   # CSV，空=全市场
    cycle_tag: Mapped[str] = mapped_column(String(16), default="none") # top/bottom/none 经典顶底标记
    description: Mapped[str | None] = mapped_column(Text)
    source_url: Mapped[str | None] = mapped_column(String(512))
    source: Mapped[str] = mapped_column(String(16), default="manual")  # manual/import/auto
    created_at: Mapped[datetime] = mapped_column(DateTime, server_default=func.now())
    updated_at: Mapped[datetime] = mapped_column(DateTime, server_default=func.now(), onupdate=func.now())


class CycleAnnotation(Base):
    """人工顶底标注（复盘用，看板上手动打点）。"""

    __tablename__ = "cycle_annotation"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    index_code: Mapped[str] = mapped_column(String(16), index=True)
    anno_date: Mapped[date] = mapped_column(Date, index=True)
    kind: Mapped[str] = mapped_column(String(16), default="watch")  # top/bottom/watch
    note: Mapped[str | None] = mapped_column(Text)
    created_at: Mapped[datetime] = mapped_column(DateTime, server_default=func.now())


def init_db() -> None:
    """开发期便捷建表；正式部署建议用 ddl/own_db_init.sql。"""
    Base.metadata.create_all(bind=engine)


def get_session() -> Iterator[Session]:
    """FastAPI 依赖注入用。"""
    with SessionLocal() as s:
        yield s