SupplyAgent/alembic/versions/20260731_08_add_find_agent_p0_gates.py

"""add find_agent P0 gate fields

Revision ID: 20260731_08
Revises: 20260730_07
Create Date: 2026-07-31
"""
from __future__ import annotations

from collections.abc import Sequence

import sqlalchemy as sa
from alembic import op

revision: str = "20260731_08"
down_revision: str | None = "20260731_08a"
branch_labels: str | Sequence[str] | None = None
depends_on: str | Sequence[str] | None = None


def upgrade() -> None:
    inspector = sa.inspect(op.get_bind())
    run_column_names = {
        column["name"]
        for column in inspector.get_columns("video_discovery_run")
    }
    run_columns = (
        sa.Column(
            "rule_version",
            sa.String(length=64),
            nullable=True,
            comment="本次候选门禁规则版本",
        ),
        sa.Column(
            "rule_config_json",
            sa.Text(),
            nullable=True,
            comment="运行时规则、当前时间和阈值快照 JSON",
        ),
    )
    for column in run_columns:
        if column.name not in run_column_names:
            op.add_column("video_discovery_run", column)

    candidate_column_names = {
        column["name"]
        for column in inspector.get_columns("video_discovery_candidate")
    }
    columns = (
        sa.Column(
            "publish_at",
            sa.DateTime(),
            nullable=True,
            comment="视频真实发布时间，按运行时区解释",
        ),
        sa.Column(
            "duration_seconds",
            sa.Numeric(precision=10, scale=3),
            nullable=True,
            comment="视频时长（秒）",
        ),
        sa.Column(
            "content_50_plus_ratio",
            sa.Numeric(precision=8, scale=6),
            nullable=True,
            comment="视频点赞用户中 50+ 占比",
        ),
        sa.Column(
            "content_50_plus_tgi",
            sa.Numeric(precision=10, scale=4),
            nullable=True,
            comment="视频点赞用户 50+ TGI",
        ),
        sa.Column(
            "content_portrait_status",
            sa.String(length=16),
            nullable=True,
            comment="视频画像结论 pass / fail / missing",
        ),
        sa.Column(
            "account_50_plus_ratio",
            sa.Numeric(precision=8, scale=6),
            nullable=True,
            comment="账号粉丝中 50+ 占比",
        ),
        sa.Column(
            "account_50_plus_tgi",
            sa.Numeric(precision=10, scale=4),
            nullable=True,
            comment="账号粉丝 50+ TGI",
        ),
        sa.Column(
            "account_portrait_status",
            sa.String(length=16),
            nullable=True,
            comment="账号画像结论 pass / fail / missing",
        ),
        sa.Column(
            "portrait_conflict",
            sa.Integer(),
            nullable=False,
            server_default="0",
            comment="视频与账号画像结论是否冲突",
        ),
        sa.Column(
            "temporal_type",
            sa.String(length=24),
            nullable=True,
            comment="时间内容类型",
        ),
        sa.Column(
            "temporal_status",
            sa.String(length=16),
            nullable=True,
            comment="时间有效性 pass / fail / unknown",
        ),
        sa.Column(
            "temporal_evidence_json",
            sa.Text(),
            nullable=True,
            comment="时间语义、有效窗口和判断理由 JSON",
        ),
        sa.Column(
            "gate_status",
            sa.String(length=16),
            nullable=True,
            comment="硬门槛 pass / fail / pending",
        ),
        sa.Column(
            "gate_results_json",
            sa.Text(),
            nullable=True,
            comment="逐项硬门槛结果 JSON",
        ),
        sa.Column(
            "reject_reason_code",
            sa.String(length=64),
            nullable=True,
            comment="主要淘汰原因码",
        ),
        sa.Column(
            "rule_version",
            sa.String(length=64),
            nullable=True,
            comment="候选评估使用的规则版本",
        ),
    )
    for column in columns:
        if column.name not in candidate_column_names:
            op.add_column("video_discovery_candidate", column)


def downgrade() -> None:
    inspector = sa.inspect(op.get_bind())
    candidate_column_names = {
        column["name"]
        for column in inspector.get_columns("video_discovery_candidate")
    }
    candidate_columns = (
        "rule_version",
        "reject_reason_code",
        "gate_results_json",
        "gate_status",
        "temporal_evidence_json",
        "temporal_status",
        "temporal_type",
        "portrait_conflict",
        "account_portrait_status",
        "account_50_plus_tgi",
        "account_50_plus_ratio",
        "content_portrait_status",
        "content_50_plus_tgi",
        "content_50_plus_ratio",
        "duration_seconds",
        "publish_at",
    )
    for column in candidate_columns:
        if column in candidate_column_names:
            op.drop_column("video_discovery_candidate", column)
    run_column_names = {
        column["name"]
        for column in inspector.get_columns("video_discovery_run")
    }
    if "rule_config_json" in run_column_names:
        op.drop_column("video_discovery_run", "rule_config_json")
    if "rule_version" in run_column_names:
        op.drop_column("video_discovery_run", "rule_version")