"""Identity-sync outcome taxonomy: ``blocked_count`` on runs.

Part of the outcome-classification work (PR2 of the sync overhaul). A
new person-outcome status ``blocked`` sits between ``failed`` (transient,
retry every sweep) and ``excluded`` (policy no-op): the vendor rejected
the person for a NON-transient, data-dependent reason (missing required
field, unmapped value_map code, resigned vendor account, dept not in the
active org tree). A blocked row persists its payload hash so subsequent
sweeps re-affirm it cheaply without re-burning a vendor call, retries
automatically when the underlying data changes, keeps the run GREEN, and
is surfaced as an actionable, deduplicated backlog rather than a nightly
red run.

``blocked_count`` denormalises the per-run blocked tally onto the run row
(nullable INT, like ``failed_count``) so the 监控看板 can chart it as its
own series without parsing ``result_summary`` JSON per row.
"""
import sqlalchemy as sa

from alembic import op

revision = "404"
down_revision = "403"
branch_labels = None
depends_on = None


def _table_exists(bind, name: str) -> bool:
    return bind.execute(
        sa.text(
            "SELECT 1 FROM information_schema.TABLES "
            "WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :n"
        ),
        {"n": name},
    ).scalar() is not None


def _column_exists(bind, table: str, column: str) -> bool:
    return bind.execute(
        sa.text(
            "SELECT 1 FROM information_schema.COLUMNS "
            "WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :t "
            "AND COLUMN_NAME = :c"
        ),
        {"t": table, "c": column},
    ).scalar() is not None


def upgrade() -> None:
    bind = op.get_bind()
    if _table_exists(bind, "identity_sync_runs") and not _column_exists(
        bind, "identity_sync_runs", "blocked_count",
    ):
        op.add_column(
            "identity_sync_runs",
            sa.Column("blocked_count", sa.Integer(), nullable=True),
        )


def downgrade() -> None:
    bind = op.get_bind()
    if _table_exists(bind, "identity_sync_runs") and _column_exists(
        bind, "identity_sync_runs", "blocked_count",
    ):
        op.drop_column("identity_sync_runs", "blocked_count")