SupplyAgent/supply_infra/db/models/video_discovery.py

from __future__ import annotations

from datetime import datetime
from decimal import Decimal

from sqlalchemy import (
    BigInteger,
    ForeignKey,
    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"),
        Index(
            "idx_video_discovery_run_platform_version",
            "platform_demand_version_id",
        ),
    )

    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_package_id: Mapped[str | None] = mapped_column(
        String(36), nullable=True, comment="统一每日需求任务包 id"
    )
    daily_demand_task_id: Mapped[str | None] = mapped_column(
        String(36), nullable=True, comment="统一需求任务 id"
    )
    platform_demand_version_id: Mapped[str | None] = mapped_column(
        String(36), nullable=True, comment="平台需求日版本 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="停止搜索的证据或失败原因"
    )
    rule_version: Mapped[str | None] = mapped_column(
        String(64), nullable=True, comment="本次候选门禁规则版本"
    )
    rule_config_json: Mapped[str | None] = mapped_column(
        Text, nullable=True, comment="运行时规则、当前时间和阈值快照 JSON"
    )
    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__ = (
        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__ = (
        Index("idx_video_discovery_candidate_search", "search_id", "id"),
        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")
    search_id: Mapped[int | None] = mapped_column(
        BigInteger,
        ForeignKey(
            "video_discovery_search.id",
            name="fk_video_discovery_candidate_search",
            ondelete="RESTRICT",
        ),
        nullable=True,
        comment="直接关联 video_discovery_search.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"
    )
    publish_at: Mapped[datetime | None] = mapped_column(
        nullable=True, comment="视频真实发布时间，按运行时区解释"
    )
    duration_seconds: Mapped[Decimal | None] = mapped_column(
        Numeric(10, 3), nullable=True, comment="视频时长（秒）"
    )
    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)
    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"
    )
    content_50_plus_ratio: Mapped[Decimal | None] = mapped_column(
        Numeric(8, 6), nullable=True, comment="视频点赞用户中 50+ 占比"
    )
    content_50_plus_tgi: Mapped[Decimal | None] = mapped_column(
        Numeric(10, 4), nullable=True, comment="视频点赞用户 50+ TGI"
    )
    content_portrait_status: Mapped[str | None] = mapped_column(
        String(16), nullable=True, comment="视频画像结论 pass / fail / missing"
    )
    account_50_plus_ratio: Mapped[Decimal | None] = mapped_column(
        Numeric(8, 6), nullable=True, comment="账号粉丝中 50+ 占比"
    )
    account_50_plus_tgi: Mapped[Decimal | None] = mapped_column(
        Numeric(10, 4), nullable=True, comment="账号粉丝 50+ TGI"
    )
    account_portrait_status: Mapped[str | None] = mapped_column(
        String(16), nullable=True, comment="账号画像结论 pass / fail / missing"
    )
    portrait_conflict: Mapped[int] = mapped_column(
        Integer, nullable=False, default=0, comment="视频与账号画像结论是否冲突"
    )
    temporal_type: Mapped[str | None] = mapped_column(
        String(24), nullable=True, comment="daypart / festival / event / seasonal / evergreen"
    )
    temporal_status: Mapped[str | None] = mapped_column(
        String(16), nullable=True, comment="时间有效性 pass / fail / unknown"
    )
    temporal_evidence_json: Mapped[str | None] = mapped_column(
        Text, nullable=True, comment="时间语义、有效窗口和判断理由 JSON"
    )
    gate_status: Mapped[str | None] = mapped_column(
        String(16), nullable=True, comment="硬门槛 pass / fail / pending"
    )
    gate_results_json: Mapped[str | None] = mapped_column(
        Text, nullable=True, comment="逐项硬门槛结果 JSON"
    )
    reject_reason_code: Mapped[str | None] = mapped_column(
        String(64), nullable=True, comment="主要淘汰原因码"
    )
    rule_version: Mapped[str | None] = mapped_column(
        String(64), nullable=True, comment="候选评估使用的规则版本"
    )
    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~1"
    )
    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="更新时间",
    )