from __future__ import annotations

from datetime import datetime
from decimal import Decimal

from sqlalchemy import (
    BigInteger,
    Index,
    Integer,
    Numeric,
    String,
    Text,
    UniqueConstraint,
    func,
)
from sqlalchemy.orm import Mapped, mapped_column

from supply_infra.db.base import Base


class VideoDiscoveryRun(Base):
    """一次需求找片运行，保存输入、意图解释和完成状态。"""

    __tablename__ = "video_discovery_run"
    __table_args__ = (
        UniqueConstraint("run_id", name="uk_video_discovery_run_id"),
        UniqueConstraint(
            "biz_dt",
            "demand_grade_id",
            name="uk_video_discovery_run_biz_grade",
        ),
        Index("idx_video_discovery_run_demand", "demand_word"),
        Index("idx_video_discovery_run_grade", "demand_grade_id"),
        Index("idx_video_discovery_run_biz_dt", "biz_dt"),
        Index("idx_video_discovery_run_status", "status"),
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    run_id: Mapped[str] = mapped_column(String(64), nullable=False, comment="Agent 运行标识")
    biz_dt: Mapped[str | None] = mapped_column(
        String(32), nullable=True, comment="业务日 YYYYMMDD"
    )
    demand_grade_id: Mapped[int | None] = mapped_column(
        BigInteger, nullable=True, comment="可选 demand_grade.id"
    )
    demand_word: Mapped[str] = mapped_column(
        String(256), nullable=False, comment="用户给定需求词"
    )
    seed_video_id: Mapped[str | None] = mapped_column(
        String(64), nullable=True, comment="参考视频 id"
    )
    seed_video_title: Mapped[str | None] = mapped_column(
        String(512), nullable=True, comment="参考视频标题"
    )
    relevant_points_json: Mapped[str] = mapped_column(
        Text, nullable=False, comment="与需求相关的视频点位 JSON"
    )
    intent_summary: Mapped[str | None] = mapped_column(
        Text, nullable=True, comment="Agent 对真实内容意图的解释"
    )
    status: Mapped[str] = mapped_column(
        String(24), nullable=False, default="running", comment="running / finished / failed"
    )
    search_count: Mapped[int] = mapped_column(
        Integer, nullable=False, default=0, comment="已保存搜索页数"
    )
    primary_count: Mapped[int] = mapped_column(
        Integer, nullable=False, default=0, comment="主推荐数"
    )
    stop_reason: Mapped[str | None] = mapped_column(
        Text, nullable=True, comment="停止搜索的证据或失败原因"
    )
    create_time: Mapped[datetime] = mapped_column(
        nullable=False, server_default=func.now(), comment="创建时间"
    )
    update_time: Mapped[datetime] = mapped_column(
        nullable=False,
        server_default=func.now(),
        onupdate=func.now(),
        comment="更新时间",
    )


class VideoDiscoverySearch(Base):
    """关键词探索图中的一次具体搜索页。"""

    __tablename__ = "video_discovery_search"
    __table_args__ = (
        UniqueConstraint("run_id", "search_key", name="uk_video_discovery_search_key"),
        Index("idx_video_discovery_search_run", "run_id", "id"),
        Index(
            "idx_video_discovery_search_parent",
            "run_id",
            "parent_search_id",
        ),
        Index("idx_video_discovery_search_keyword", "keyword"),
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    run_id: Mapped[str] = mapped_column(String(64), nullable=False, comment="发现运行 run_id")
    search_key: Mapped[str] = mapped_column(
        String(64), nullable=False, comment="关键词/筛选/游标组合哈希"
    )
    keyword: Mapped[str] = mapped_column(
        String(256), nullable=False, comment="Agent 自主确定的搜索词"
    )
    query_reason: Mapped[str] = mapped_column(
        Text, nullable=False, comment="为何形成该搜索词、希望验证什么"
    )
    source_type: Mapped[str] = mapped_column(
        String(32),
        nullable=False,
        comment="demand / seed / point / tag / author / pagination / mixed",
    )
    source_value: Mapped[str | None] = mapped_column(
        Text, nullable=True, comment="来源点位、标签或父关键词"
    )
    parent_search_id: Mapped[int | None] = mapped_column(
        BigInteger, nullable=True, comment="由哪次搜索扩展而来"
    )
    provider: Mapped[str] = mapped_column(
        String(32),
        nullable=False,
        default="internal_keyword",
        comment="internal_keyword / tikhub / internal_blogger",
    )
    provider_state_json: Mapped[str | None] = mapped_column(
        Text, nullable=True, comment="供应方分页状态，如 search_id/backtrace"
    )
    content_type: Mapped[str] = mapped_column(
        String(16), nullable=False, default="视频", comment="搜索内容类型"
    )
    sort_type: Mapped[str] = mapped_column(
        String(32), nullable=False, default="综合排序", comment="搜索排序"
    )
    publish_time: Mapped[str] = mapped_column(
        String(32), nullable=False, default="不限", comment="发布时间筛选"
    )
    cursor: Mapped[str] = mapped_column(
        String(128), nullable=False, default="0", comment="本页游标"
    )
    page_no: Mapped[int] = mapped_column(
        Integer, nullable=False, default=1, comment="该关键词的页码"
    )
    results_count: Mapped[int] = mapped_column(
        Integer, nullable=False, default=0, comment="本页结果数"
    )
    new_candidate_count: Mapped[int] = mapped_column(
        Integer, nullable=False, default=0, comment="本页新增候选数"
    )
    has_more: Mapped[int] = mapped_column(
        Integer, nullable=False, default=0, comment="接口是否有下一页"
    )
    next_cursor: Mapped[str | None] = mapped_column(
        String(128), nullable=True, comment="下一页游标"
    )
    result_ids_json: Mapped[str | None] = mapped_column(
        Text, nullable=True, comment="本页 aweme_id 列表 JSON"
    )
    status: Mapped[str] = mapped_column(
        String(16), nullable=False, default="success", comment="success / failed"
    )
    error_message: Mapped[str | None] = mapped_column(
        Text, nullable=True, comment="搜索失败信息"
    )
    create_time: Mapped[datetime] = mapped_column(
        nullable=False, server_default=func.now(), comment="创建时间"
    )
    update_time: Mapped[datetime] = mapped_column(
        nullable=False,
        server_default=func.now(),
        onupdate=func.now(),
        comment="更新时间",
    )


class VideoDiscoveryCandidate(Base):
    """本次运行发现的视频及其可审计评估快照。"""

    __tablename__ = "video_discovery_candidate"
    __table_args__ = (
        UniqueConstraint(
            "run_id", "aweme_id", name="uk_video_discovery_candidate_run_aweme"
        ),
        Index("idx_video_discovery_candidate_bucket", "run_id", "decision_bucket"),
        Index("idx_video_discovery_candidate_author", "author_sec_uid"),
    )

    id: Mapped[int] = mapped_column(BigInteger, primary_key=True, autoincrement=True)
    run_id: Mapped[str] = mapped_column(String(64), nullable=False, comment="发现运行 run_id")
    aweme_id: Mapped[str] = mapped_column(String(64), nullable=False, comment="抖音视频 id")
    title: Mapped[str | None] = mapped_column(String(512), nullable=True, comment="视频标题")
    content_link: Mapped[str | None] = mapped_column(
        String(1024), nullable=True, comment="抖音页面链接"
    )
    author_name: Mapped[str | None] = mapped_column(
        String(256), nullable=True, comment="作者名"
    )
    author_sec_uid: Mapped[str | None] = mapped_column(
        String(256), nullable=True, comment="作者 sec_uid"
    )
    source_keywords_json: Mapped[str | None] = mapped_column(
        Text, nullable=True, comment="命中过该视频的搜索词 JSON"
    )
    source_search_ids_json: Mapped[str | None] = mapped_column(
        Text, nullable=True, comment="来源搜索轨迹 id JSON"
    )
    tags_json: Mapped[str | None] = mapped_column(
        Text, nullable=True, comment="视频标签/话题 JSON"
    )
    hit_points_json: Mapped[str | None] = mapped_column(
        Text, nullable=True, comment="命中的需求相关点 JSON"
    )
    play_count: Mapped[int | None] = mapped_column(BigInteger, nullable=True)
    like_count: Mapped[int | None] = mapped_column(BigInteger, nullable=True)
    comment_count: Mapped[int | None] = mapped_column(BigInteger, nullable=True)
    collect_count: Mapped[int | None] = mapped_column(BigInteger, nullable=True)
    share_count: Mapped[int | None] = mapped_column(BigInteger, nullable=True)
    publish_timestamp: Mapped[int | None] = mapped_column(BigInteger, nullable=True)
    content_age_evidence_json: Mapped[str | None] = mapped_column(
        Text, nullable=True, comment="视频点赞用户年龄证据 JSON"
    )
    account_age_evidence_json: Mapped[str | None] = mapped_column(
        Text, nullable=True, comment="作者粉丝年龄证据 JSON"
    )
    age_normalization_json: Mapped[str | None] = mapped_column(
        Text, nullable=True, comment="双侧年龄画像标准化结果 JSON"
    )
    detail_verified: Mapped[int] = mapped_column(
        Integer, nullable=False, default=0, comment="是否已核验视频详情"
    )
    content_portrait_attempted: Mapped[int] = mapped_column(
        Integer, nullable=False, default=0, comment="是否已尝试视频点赞画像"
    )
    account_portrait_attempted: Mapped[int] = mapped_column(
        Integer, nullable=False, default=0, comment="是否已尝试作者粉丝画像"
    )
    age_portraits_normalized: Mapped[int] = mapped_column(
        Integer, nullable=False, default=0, comment="是否已执行年龄画像标准化"
    )
    expansion_worthy_tags_json: Mapped[str | None] = mapped_column(
        Text, nullable=True, comment="值得继续搜索的标签 JSON"
    )
    relevance_score: Mapped[Decimal | None] = mapped_column(
        Numeric(8, 6), nullable=True, comment="R，范围 0~1"
    )
    elder_score: Mapped[Decimal | None] = mapped_column(
        Numeric(8, 6), nullable=True, comment="E，范围 0~1"
    )
    share_score: Mapped[Decimal | None] = mapped_column(
        Numeric(8, 6), nullable=True, comment="S，范围 0~1"
    )
    value_score: Mapped[Decimal | None] = mapped_column(
        Numeric(8, 2), nullable=True, comment="联合价值 V，范围 0~100"
    )
    confidence: Mapped[str | None] = mapped_column(
        String(16), nullable=True, comment="high / medium / low"
    )
    relevance_reason: Mapped[str | None] = mapped_column(Text, nullable=True)
    elder_reason: Mapped[str | None] = mapped_column(Text, nullable=True)
    share_reason: Mapped[str | None] = mapped_column(Text, nullable=True)
    decision_reason: Mapped[str | None] = mapped_column(Text, nullable=True)
    decision_bucket: Mapped[str] = mapped_column(
        String(24),
        nullable=False,
        default="pending_evaluation",
        comment="最终为 primary / rejected；pending_evaluation 仅为过程状态",
    )
    aigc_crawler_plan_id: Mapped[str | None] = mapped_column(
        String(64), nullable=True, comment="已创建的 AIGC 爬取计划 id"
    )
    aigc_produce_plan_id: Mapped[str | None] = mapped_column(
        String(64), nullable=True, comment="绑定的 AIGC 生成计划 id"
    )
    aigc_publish_plan_id: Mapped[str | None] = mapped_column(
        String(64), nullable=True, comment="关联的 AIGC 发布计划 id"
    )
    aigc_plan_label: Mapped[str | None] = mapped_column(
        String(64), nullable=True, comment="均匀分发时使用的计划标签"
    )
    create_time: Mapped[datetime] = mapped_column(
        nullable=False, server_default=func.now(), comment="创建时间"
    )
    update_time: Mapped[datetime] = mapped_column(
        nullable=False,
        server_default=func.now(),
        onupdate=func.now(),
        comment="更新时间",
    )