"""add find_agent business outcomes and bounded attempts

Revision ID: 20260803_09
Revises: 20260731_08
Create Date: 2026-08-03
"""
from __future__ import annotations

from collections.abc import Sequence

import sqlalchemy as sa
from alembic import op

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


def upgrade() -> None:
    op.add_column(
        "video_discovery_run",
        sa.Column(
            "valid_primary_count",
            sa.Integer(),
            nullable=False,
            server_default="0",
            comment="通过 P0 且按 aweme_id 去重后的主推荐数",
        ),
    )
    op.add_column(
        "video_discovery_run",
        sa.Column(
            "outcome_status",
            sa.String(length=24),
            nullable=True,
            comment="goal_met / partial / no_match / failed",
        ),
    )
    op.add_column(
        "video_discovery_run",
        sa.Column(
            "attempt_count",
            sa.Integer(),
            nullable=False,
            server_default="0",
            comment="调度执行尝试次数",
        ),
    )
    op.execute(
        sa.text(
            "UPDATE video_discovery_run "
            "SET valid_primary_count = ("
            "SELECT COUNT(DISTINCT c.aweme_id) "
            "FROM video_discovery_candidate c "
            "WHERE c.run_id = video_discovery_run.run_id "
            "AND c.decision_bucket = 'primary' "
            "AND c.gate_status = 'pass' "
            "AND c.aweme_id IS NOT NULL AND TRIM(c.aweme_id) <> ''"
            ")"
        )
    )
    op.execute(
        sa.text(
            "UPDATE video_discovery_run SET outcome_status = CASE "
            "WHEN status = 'failed' THEN 'failed' "
            "WHEN status = 'finished' AND valid_primary_count >= 5 THEN 'goal_met' "
            "WHEN status = 'finished' AND valid_primary_count > 0 THEN 'partial' "
            "WHEN status = 'finished' THEN 'no_match' "
            "ELSE NULL END, "
            "attempt_count = 1"
        )
    )


def downgrade() -> None:
    op.drop_column("video_discovery_run", "attempt_count")
    op.drop_column("video_discovery_run", "outcome_status")
    op.drop_column("video_discovery_run", "valid_primary_count")
