from __future__ import annotations

from datetime import datetime
from decimal import Decimal

from sqlalchemy import BigInteger, Integer, Numeric, String, UniqueConstraint, func
from sqlalchemy.orm import Mapped, mapped_column

from supply_infra.db.base import Base


class DemandPopularityStats(Base):
    """需求分类热度统计表（六维 avg + count）。"""

    __tablename__ = "demand_popularity_stats"
    __table_args__ = (
        UniqueConstraint("demand_category_id", "biz_dt", name="uk_demand_category"),
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    demand_category_id: Mapped[int] = mapped_column(
        BigInteger, nullable=False, comment="需求分类ID"
    )
    demand_word_name: Mapped[str | None] = mapped_column(
        String(128), nullable=True, comment="需求词名称"
    )
    biz_dt: Mapped[str] = mapped_column(String(32), nullable=False, comment="日期")
    ext_pop_avg: Mapped[Decimal | None] = mapped_column(
        Numeric(12, 2), nullable=True, comment="外部热度-平均值"
    )
    ext_pop_count: Mapped[int] = mapped_column(
        Integer, nullable=False, default=0, comment="外部热度-出现数量"
    )
    plat_sust_pop_avg: Mapped[Decimal | None] = mapped_column(
        Numeric(12, 2), nullable=True, comment="平台持续热度-平均值"
    )
    plat_sust_pop_count: Mapped[int] = mapped_column(
        Integer, nullable=False, default=0, comment="平台持续热度-出现数量"
    )
    plat_ly_pop_avg: Mapped[Decimal | None] = mapped_column(
        Numeric(12, 2), nullable=True, comment="平台去年同期热度-平均值"
    )
    plat_ly_pop_count: Mapped[int] = mapped_column(
        Integer, nullable=False, default=0, comment="平台去年同期热度-出现数量"
    )
    recent_pop_avg: Mapped[Decimal | None] = mapped_column(
        Numeric(12, 2), nullable=True, comment="近期热度-平均值"
    )
    recent_pop_count: Mapped[int] = mapped_column(
        Integer, nullable=False, default=0, comment="近期热度-出现数量"
    )
    real_rov_7d_avg: Mapped[Decimal | None] = mapped_column(
        Numeric(12, 4), nullable=True, comment="近7日ROV相对全局diff-平均值"
    )
    real_rov_7d_count: Mapped[int] = mapped_column(
        Integer, nullable=False, default=0, comment="近7日ROV相对全局diff-出现数量"
    )
    real_vov_7d_avg: Mapped[Decimal | None] = mapped_column(
        Numeric(12, 4), nullable=True, comment="近7日VOV相对全局diff-平均值"
    )
    real_vov_7d_count: Mapped[int] = mapped_column(
        Integer, nullable=False, default=0, comment="近7日VOV相对全局diff-出现数量"
    )
    created_time: Mapped[datetime] = mapped_column(
        nullable=False,
        server_default=func.now(),
        comment="创建时间",
    )
    updated_time: Mapped[datetime] = mapped_column(
        nullable=False,
        server_default=func.now(),
        onupdate=func.now(),
        comment="更新时间",
    )