"""add isolated find_agent_v2 tables

Revision ID: 20260811_12
Revises: 20260803_11a
Create Date: 2026-08-11
"""

from __future__ import annotations

from collections.abc import Sequence

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

revision: str = "20260811_12"
down_revision: str | None = "20260803_11a"
branch_labels: str | Sequence[str] | None = None
depends_on: str | Sequence[str] | None = None


def _timestamps() -> list[sa.Column]:
    return [
        sa.Column("create_time", sa.DateTime(), server_default=sa.text("CURRENT_TIMESTAMP"), nullable=False),
        sa.Column("update_time", sa.DateTime(), server_default=sa.text("CURRENT_TIMESTAMP"), nullable=False),
    ]


def upgrade() -> None:
    op.create_table(
        "find_agent_v2_run",
        sa.Column("id", sa.BigInteger(), autoincrement=True, nullable=False),
        sa.Column("run_id", sa.String(64), nullable=False),
        sa.Column("demand_grade_id", sa.BigInteger(), nullable=True),
        sa.Column("demand_word", sa.String(256), nullable=False),
        sa.Column("input_json", mysql.LONGTEXT(), nullable=False),
        sa.Column("rule_config_json", sa.Text(), nullable=False),
        sa.Column("status", sa.String(24), server_default="running", nullable=False),
        sa.Column("outcome_status", sa.String(24), nullable=True),
        sa.Column("current_round", sa.Integer(), server_default="0", nullable=False),
        sa.Column("search_count", sa.Integer(), server_default="0", nullable=False),
        sa.Column("candidate_count", sa.Integer(), server_default="0", nullable=False),
        sa.Column("valid_primary_count", sa.Integer(), server_default="0", nullable=False),
        sa.Column("intent_summary", sa.Text(), nullable=True),
        sa.Column("stop_reason", sa.Text(), nullable=True),
        sa.Column("obagent_run_uid", sa.String(64), nullable=True),
        *_timestamps(),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint("run_id", name="uk_find_agent_v2_run_id"),
    )
    op.create_index("idx_find_agent_v2_run_status", "find_agent_v2_run", ["status"])
    op.create_index("idx_find_agent_v2_run_demand", "find_agent_v2_run", ["demand_grade_id"])

    op.create_table(
        "find_agent_v2_round",
        sa.Column("id", sa.BigInteger(), autoincrement=True, nullable=False),
        sa.Column("run_id", sa.String(64), nullable=False),
        sa.Column("round_index", sa.Integer(), nullable=False),
        sa.Column("phase", sa.String(24), server_default="planning", nullable=False),
        sa.Column("status", sa.String(16), server_default="open", nullable=False),
        sa.Column("plan_json", mysql.LONGTEXT(), nullable=True),
        sa.Column("start_snapshot_json", sa.Text(), nullable=True),
        sa.Column("end_snapshot_json", sa.Text(), nullable=True),
        sa.Column("error_message", sa.Text(), nullable=True),
        *_timestamps(),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint("run_id", "round_index", name="uk_find_agent_v2_round"),
    )
    op.create_index("idx_find_agent_v2_round_run", "find_agent_v2_round", ["run_id", "round_index"])

    op.create_table(
        "find_agent_v2_search",
        sa.Column("id", sa.BigInteger(), autoincrement=True, nullable=False),
        sa.Column("run_id", sa.String(64), nullable=False),
        sa.Column("round_index", sa.Integer(), nullable=False),
        sa.Column("keyword", sa.String(256), nullable=False),
        sa.Column("query_reason", sa.Text(), nullable=False),
        sa.Column("source_type", sa.String(32), nullable=False),
        sa.Column("provider", sa.String(32), nullable=False),
        sa.Column("cursor", sa.String(128), server_default="0", nullable=False),
        sa.Column("page_no", sa.Integer(), server_default="1", nullable=False),
        sa.Column("has_more", sa.Integer(), server_default="0", nullable=False),
        sa.Column("next_cursor", sa.String(128), nullable=True),
        sa.Column("provider_state_json", sa.Text(), nullable=True),
        sa.Column("result_count", sa.Integer(), server_default="0", nullable=False),
        sa.Column("status", sa.String(16), nullable=False),
        sa.Column("error_message", sa.Text(), nullable=True),
        sa.Column("raw_response_json", mysql.LONGTEXT(), nullable=True),
        sa.Column("create_time", sa.DateTime(), server_default=sa.text("CURRENT_TIMESTAMP"), nullable=False),
        sa.PrimaryKeyConstraint("id"),
    )
    op.create_index("idx_find_agent_v2_search_run", "find_agent_v2_search", ["run_id", "id"])

    op.create_table(
        "find_agent_v2_candidate",
        sa.Column("id", sa.BigInteger(), autoincrement=True, nullable=False),
        sa.Column("run_id", sa.String(64), nullable=False),
        sa.Column("first_search_id", sa.BigInteger(), nullable=True),
        sa.Column("aweme_id", sa.String(64), nullable=False),
        sa.Column("title", sa.String(512), nullable=True),
        sa.Column("content_link", sa.String(1024), nullable=True),
        sa.Column("author_name", sa.String(256), nullable=True),
        sa.Column("author_sec_uid", sa.String(256), nullable=True),
        sa.Column("source_keywords_json", sa.Text(), nullable=True),
        sa.Column("tags_json", sa.Text(), nullable=True),
        sa.Column("publish_at", sa.DateTime(), nullable=True),
        sa.Column("duration_seconds", sa.Numeric(10, 3), nullable=True),
        sa.Column("play_count", sa.BigInteger(), nullable=True),
        sa.Column("like_count", sa.BigInteger(), nullable=True),
        sa.Column("comment_count", sa.BigInteger(), nullable=True),
        sa.Column("collect_count", sa.BigInteger(), nullable=True),
        sa.Column("share_count", sa.BigInteger(), nullable=True),
        sa.Column("detail_json", mysql.LONGTEXT(), nullable=True),
        sa.Column("portrait_json", mysql.LONGTEXT(), nullable=True),
        sa.Column("detail_status", sa.String(16), server_default="pending", nullable=False),
        sa.Column("portrait_status", sa.String(16), server_default="pending", nullable=False),
        sa.Column("content_50_plus_ratio", sa.Numeric(8, 6), nullable=True),
        sa.Column("account_50_plus_ratio", sa.Numeric(8, 6), nullable=True),
        sa.Column("relevance_score", sa.Numeric(8, 6), nullable=True),
        sa.Column("elder_score", sa.Numeric(8, 6), nullable=True),
        sa.Column("share_score", sa.Numeric(8, 6), nullable=True),
        sa.Column("value_score", sa.Numeric(8, 6), nullable=True),
        sa.Column("gate_status", sa.String(16), nullable=True),
        sa.Column("gate_result_json", sa.Text(), nullable=True),
        sa.Column("decision_bucket", sa.String(24), server_default="pending_evaluation", nullable=False),
        sa.Column("decision_reason", sa.Text(), nullable=True),
        sa.Column("reject_reason_code", sa.String(64), nullable=True),
        *_timestamps(),
        sa.PrimaryKeyConstraint("id"),
        sa.UniqueConstraint("run_id", "aweme_id", name="uk_find_agent_v2_candidate_aweme"),
    )
    op.create_index("idx_find_agent_v2_candidate_bucket", "find_agent_v2_candidate", ["run_id", "decision_bucket"])

    op.create_table(
        "find_agent_v2_evidence",
        sa.Column("id", sa.BigInteger(), autoincrement=True, nullable=False),
        sa.Column("run_id", sa.String(64), nullable=False),
        sa.Column("candidate_id", sa.BigInteger(), nullable=True),
        sa.Column("evidence_type", sa.String(32), nullable=False),
        sa.Column("provider", sa.String(32), nullable=False),
        sa.Column("status", sa.String(16), nullable=False),
        sa.Column("raw_json", mysql.LONGTEXT(), nullable=True),
        sa.Column("normalized_json", mysql.LONGTEXT(), nullable=True),
        sa.Column("error_message", sa.Text(), nullable=True),
        sa.Column("create_time", sa.DateTime(), server_default=sa.text("CURRENT_TIMESTAMP"), nullable=False),
        sa.PrimaryKeyConstraint("id"),
    )
    op.create_index("idx_find_agent_v2_evidence_subject", "find_agent_v2_evidence", ["run_id", "candidate_id", "id"])


def downgrade() -> None:
    op.drop_table("find_agent_v2_evidence")
    op.drop_table("find_agent_v2_candidate")
    op.drop_table("find_agent_v2_search")
    op.drop_table("find_agent_v2_round")
    op.drop_table("find_agent_v2_run")
