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 sqlalchemy.dialects.mysql import LONGTEXT

from supply_infra.db.base import Base


_LONG_TEXT = Text().with_variant(LONGTEXT(), "mysql")
_BIGINT_PK = BigInteger().with_variant(Integer, "sqlite")


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="主推荐数"
    )
    valid_primary_count: Mapped[int] = mapped_column(
        Integer,
        nullable=False,
        default=0,
        comment="通过 P0 且按 aweme_id 去重后的主推荐数",
    )
    outcome_status: Mapped[str | None] = mapped_column(
        String(24),
        nullable=True,
        comment="goal_met / partial / no_match / failed",
    )
    attempt_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="搜索失败信息"
    )
    raw_evidence_id: Mapped[int | None] = mapped_column(
        BigInteger, nullable=True, comment="本页完整原始响应证据 id"
    )
    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,
        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="候选评估使用的规则版本"
    )
    latest_search_evidence_id: Mapped[int | None] = mapped_column(
        BigInteger, nullable=True, comment="最新搜索原始证据 id"
    )
    latest_detail_evidence_id: Mapped[int | None] = mapped_column(
        BigInteger, nullable=True, comment="最新详情原始证据 id"
    )
    latest_content_portrait_evidence_id: Mapped[int | None] = mapped_column(
        BigInteger, nullable=True, comment="最新内容画像原始证据 id"
    )
    latest_account_portrait_evidence_id: Mapped[int | None] = mapped_column(
        BigInteger, nullable=True, comment="最新账号画像原始证据 id"
    )
    detail_fetch_status: Mapped[str | None] = mapped_column(
        String(16), nullable=True, comment="pending / success / failed / unavailable"
    )
    content_portrait_fetch_status: Mapped[str | None] = mapped_column(
        String(16), nullable=True, comment="内容画像获取状态"
    )
    account_portrait_fetch_status: Mapped[str | None] = mapped_column(
        String(16), nullable=True, comment="账号画像获取状态"
    )
    evidence_version: Mapped[int] = mapped_column(
        Integer, nullable=False, default=0, comment="门禁输入数据版本"
    )
    gate_evidence_version: Mapped[int | None] = mapped_column(
        Integer, nullable=True, comment="最近门禁使用的证据版本"
    )
    latest_gate_evaluation_id: Mapped[int | None] = mapped_column(
        BigInteger, nullable=True, comment="最近门禁评估记录 id"
    )
    gate_evaluated_at: Mapped[datetime | None] = mapped_column(
        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="更新时间",
    )


class VideoDiscoveryEvidence(Base):
    """工具原始响应及规范化结果的不可变证据快照。"""

    __tablename__ = "video_discovery_evidence"
    __table_args__ = (
        Index("idx_video_discovery_evidence_run_type", "run_id", "evidence_type", "id"),
        Index(
            "idx_video_discovery_evidence_subject",
            "run_id",
            "aweme_id",
            "evidence_type",
            "id",
        ),
        Index("idx_video_discovery_evidence_processing", "processing_status", "id"),
        UniqueConstraint(
            "tool_call_id",
            "evidence_type",
            "subject_key",
            name="uk_video_discovery_evidence_call_subject",
        ),
    )

    id: Mapped[int] = mapped_column(_BIGINT_PK, primary_key=True, autoincrement=True)
    run_id: Mapped[str] = mapped_column(String(64), nullable=False)
    search_id: Mapped[int | None] = mapped_column(BigInteger, nullable=True)
    trigger_candidate_id: Mapped[int | None] = mapped_column(BigInteger, nullable=True)
    aweme_id: Mapped[str | None] = mapped_column(String(64), nullable=True)
    author_sec_uid: Mapped[str | None] = mapped_column(String(256), nullable=True)
    subject_key: Mapped[str] = mapped_column(String(320), nullable=False)
    evidence_type: Mapped[str] = mapped_column(String(32), nullable=False)
    provider: Mapped[str] = mapped_column(String(32), nullable=False)
    tool_call_id: Mapped[str] = mapped_column(String(64), nullable=False)
    request_json: Mapped[str | None] = mapped_column(_LONG_TEXT, nullable=True)
    raw_response_json: Mapped[str | None] = mapped_column(_LONG_TEXT, nullable=True)
    normalized_json: Mapped[str | None] = mapped_column(_LONG_TEXT, nullable=True)
    response_sha256: Mapped[str | None] = mapped_column(String(64), nullable=True)
    fetch_status: Mapped[str] = mapped_column(String(16), nullable=False)
    processing_status: Mapped[str] = mapped_column(
        String(16), nullable=False, default="pending"
    )
    error_code: Mapped[str | None] = mapped_column(String(64), nullable=True)
    error_message: Mapped[str | None] = mapped_column(Text, nullable=True)
    schema_version: Mapped[str] = mapped_column(
        String(32), nullable=False, default="find-agent-evidence-v1"
    )
    fetched_at: Mapped[datetime] = mapped_column(nullable=False, server_default=func.now())
    latency_ms: Mapped[int | None] = mapped_column(Integer, nullable=True)
    create_time: Mapped[datetime] = mapped_column(nullable=False, server_default=func.now())


class VideoDiscoveryGateEvaluation(Base):
    """候选每个输入版本的门禁评估历史。"""

    __tablename__ = "video_discovery_gate_evaluation"
    __table_args__ = (
        Index("idx_video_discovery_gate_candidate", "candidate_id", "id"),
        Index("idx_video_discovery_gate_run_status", "run_id", "gate_status", "id"),
        UniqueConstraint(
            "candidate_id",
            "rule_version",
            "evidence_version",
            "gate_input_hash",
            name="uk_video_discovery_gate_input",
        ),
    )

    id: Mapped[int] = mapped_column(_BIGINT_PK, primary_key=True, autoincrement=True)
    run_id: Mapped[str] = mapped_column(String(64), nullable=False)
    candidate_id: Mapped[int] = mapped_column(BigInteger, nullable=False)
    rule_version: Mapped[str] = mapped_column(String(64), nullable=False)
    evidence_version: Mapped[int] = mapped_column(Integer, nullable=False)
    gate_input_hash: Mapped[str] = mapped_column(String(64), nullable=False)
    gate_input_json: Mapped[str] = mapped_column(_LONG_TEXT, nullable=False)
    gate_result_json: Mapped[str] = mapped_column(_LONG_TEXT, nullable=False)
    gate_status: Mapped[str] = mapped_column(String(16), nullable=False)
    failed_reason_codes_json: Mapped[str | None] = mapped_column(Text, nullable=True)
    evaluated_at: Mapped[datetime] = mapped_column(
        nullable=False, server_default=func.now()
    )
