"""add find_agent raw evidence and versioned gate evaluations

Revision ID: 20260803_11
Revises: 20260803_10
Create Date: 2026-08-03
"""
from __future__ import annotations

from collections.abc import Sequence

import sqlalchemy as sa
from alembic import op
from sqlalchemy.dialects import mysql

revision: str = "20260803_11"
down_revision: str | None = "20260803_10"
branch_labels: str | Sequence[str] | None = None
depends_on: str | Sequence[str] | None = None


def upgrade() -> None:
    op.create_table(
        "video_discovery_evidence",
        sa.Column("id", sa.BigInteger(), autoincrement=True, nullable=False),
        sa.Column("run_id", sa.String(length=64), nullable=False),
        sa.Column("search_id", sa.BigInteger(), nullable=True),
        sa.Column("trigger_candidate_id", sa.BigInteger(), nullable=True),
        sa.Column("aweme_id", sa.String(length=64), nullable=True),
        sa.Column("author_sec_uid", sa.String(length=256), nullable=True),
        sa.Column("subject_key", sa.String(length=320), nullable=False),
        sa.Column("evidence_type", sa.String(length=32), nullable=False),
        sa.Column("provider", sa.String(length=32), nullable=False),
        sa.Column("tool_call_id", sa.String(length=64), nullable=False),
        sa.Column("request_json", mysql.LONGTEXT(), nullable=True),
        sa.Column("raw_response_json", mysql.LONGTEXT(), nullable=True),
        sa.Column("normalized_json", mysql.LONGTEXT(), nullable=True),
        sa.Column("response_sha256", sa.String(length=64), nullable=True),
        sa.Column("fetch_status", sa.String(length=16), nullable=False),
        sa.Column(
            "processing_status",
            sa.String(length=16),
            nullable=False,
            server_default="pending",
        ),
        sa.Column("error_code", sa.String(length=64), nullable=True),
        sa.Column("error_message", sa.Text(), nullable=True),
        sa.Column(
            "schema_version",
            sa.String(length=32),
            nullable=False,
            server_default="find-agent-evidence-v1",
        ),
        sa.Column(
            "fetched_at",
            sa.DateTime(),
            nullable=False,
            server_default=sa.text("CURRENT_TIMESTAMP"),
        ),
        sa.Column("latency_ms", sa.Integer(), nullable=True),
        sa.Column(
            "create_time",
            sa.DateTime(),
            nullable=False,
            server_default=sa.text("CURRENT_TIMESTAMP"),
        ),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint(
            "tool_call_id",
            "evidence_type",
            "subject_key",
            name="uk_video_discovery_evidence_call_subject",
        ),
    )
    op.create_index(
        "idx_video_discovery_evidence_run_type",
        "video_discovery_evidence",
        ["run_id", "evidence_type", "id"],
    )
    op.create_index(
        "idx_video_discovery_evidence_subject",
        "video_discovery_evidence",
        ["run_id", "aweme_id", "evidence_type", "id"],
    )
    op.create_index(
        "idx_video_discovery_evidence_processing",
        "video_discovery_evidence",
        ["processing_status", "id"],
    )

    op.create_table(
        "video_discovery_gate_evaluation",
        sa.Column("id", sa.BigInteger(), autoincrement=True, nullable=False),
        sa.Column("run_id", sa.String(length=64), nullable=False),
        sa.Column("candidate_id", sa.BigInteger(), nullable=False),
        sa.Column("rule_version", sa.String(length=64), nullable=False),
        sa.Column("evidence_version", sa.Integer(), nullable=False),
        sa.Column("gate_input_hash", sa.String(length=64), nullable=False),
        sa.Column("gate_input_json", mysql.LONGTEXT(), nullable=False),
        sa.Column("gate_result_json", mysql.LONGTEXT(), nullable=False),
        sa.Column("gate_status", sa.String(length=16), nullable=False),
        sa.Column("failed_reason_codes_json", sa.Text(), nullable=True),
        sa.Column(
            "evaluated_at",
            sa.DateTime(),
            nullable=False,
            server_default=sa.text("CURRENT_TIMESTAMP"),
        ),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint(
            "candidate_id",
            "rule_version",
            "evidence_version",
            "gate_input_hash",
            name="uk_video_discovery_gate_input",
        ),
    )
    op.create_index(
        "idx_video_discovery_gate_candidate",
        "video_discovery_gate_evaluation",
        ["candidate_id", "id"],
    )
    op.create_index(
        "idx_video_discovery_gate_run_status",
        "video_discovery_gate_evaluation",
        ["run_id", "gate_status", "id"],
    )

    op.add_column(
        "video_discovery_search",
        sa.Column("raw_evidence_id", sa.BigInteger(), nullable=True),
    )
    candidate_columns = (
        sa.Column("latest_search_evidence_id", sa.BigInteger(), nullable=True),
        sa.Column("latest_detail_evidence_id", sa.BigInteger(), nullable=True),
        sa.Column("latest_content_portrait_evidence_id", sa.BigInteger(), nullable=True),
        sa.Column("latest_account_portrait_evidence_id", sa.BigInteger(), nullable=True),
        sa.Column("detail_fetch_status", sa.String(length=16), nullable=True),
        sa.Column("content_portrait_fetch_status", sa.String(length=16), nullable=True),
        sa.Column("account_portrait_fetch_status", sa.String(length=16), nullable=True),
        sa.Column(
            "evidence_version", sa.Integer(), nullable=False, server_default="0"
        ),
        sa.Column("gate_evidence_version", sa.Integer(), nullable=True),
        sa.Column("latest_gate_evaluation_id", sa.BigInteger(), nullable=True),
        sa.Column("gate_evaluated_at", sa.DateTime(), nullable=True),
    )
    for column in candidate_columns:
        op.add_column("video_discovery_candidate", column)
    op.execute(
        sa.text(
            "UPDATE video_discovery_candidate "
            "SET gate_evidence_version = evidence_version "
            "WHERE gate_status IS NOT NULL"
        )
    )


def downgrade() -> None:
    for name in (
        "gate_evaluated_at",
        "latest_gate_evaluation_id",
        "gate_evidence_version",
        "evidence_version",
        "account_portrait_fetch_status",
        "content_portrait_fetch_status",
        "detail_fetch_status",
        "latest_account_portrait_evidence_id",
        "latest_content_portrait_evidence_id",
        "latest_detail_evidence_id",
        "latest_search_evidence_id",
    ):
        op.drop_column("video_discovery_candidate", name)
    op.drop_column("video_discovery_search", "raw_evidence_id")
    op.drop_table("video_discovery_gate_evaluation")
    op.drop_table("video_discovery_evidence")
