"""Drop the legacy privileged-review tables (folded into access certs).

Revision ID: 253
Revises: 252
Create Date: 2026-06-04

GRC PR3: privileged-role review is now ``campaign_type='privileged_role'``
in the unified access-certification engine
(``access_certifications`` / ``access_certification_items``). The
``auditor_privileged_review_cycles`` + ``auditor_privileged_role_holders``
tables are no longer read or written by any code path (the 安全合规 menu
had no production usage, so there is no data to migrate). This drops them.

Downgrade recreates the two tables (structure only, empty) so the chain
stays reversible; the data is not restorable.
"""
from __future__ import annotations

import sqlalchemy as sa

from alembic import op
from app.core.migration_helpers import id_type_for, table_kw_for

revision = "253"
down_revision = "252"
branch_labels = None
depends_on = None

_CYCLES = "auditor_privileged_review_cycles"
_HOLDERS = "auditor_privileged_role_holders"


def upgrade() -> None:
    # Child first (FK to cycles), then parent. IF EXISTS = idempotent.
    op.execute(f"DROP TABLE IF EXISTS `{_HOLDERS}`")
    op.execute(f"DROP TABLE IF EXISTS `{_CYCLES}`")


def downgrade() -> None:
    persons_id = id_type_for("identity_persons")
    table_kw = table_kw_for("identity_persons")

    op.create_table(
        _CYCLES,
        sa.Column("id", sa.CHAR(36), nullable=False),
        sa.Column("cycle_label", sa.String(32), nullable=False),
        sa.Column("status", sa.String(32), nullable=False,
                  server_default=sa.text("'in_progress'")),
        sa.Column("started_at", sa.DateTime(timezone=True), nullable=False),
        sa.Column("due_at", sa.DateTime(timezone=True), nullable=False),
        sa.Column("completed_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("created_by_person_id", persons_id, nullable=True),
        sa.Column("snapshot_holder_count", sa.Integer(), nullable=False,
                  server_default=sa.text("0")),
        sa.Column("decided_count", sa.Integer(), nullable=False,
                  server_default=sa.text("0")),
        sa.Column("revoked_count", sa.Integer(), nullable=False,
                  server_default=sa.text("0")),
        sa.Column("notes", sa.Text(), nullable=True),
        sa.Column("created_at", sa.DateTime(timezone=True),
                  server_default=sa.func.now(), nullable=False),
        sa.Column("updated_at", sa.DateTime(timezone=True),
                  server_default=sa.func.now(), nullable=False),
        sa.PrimaryKeyConstraint("id"),
        sa.ForeignKeyConstraint(["created_by_person_id"],
                                ["identity_persons.id"]),
        sa.UniqueConstraint("cycle_label", name="uq_auditor_priv_cycle_label"),
        **table_kw,
    )
    op.create_table(
        _HOLDERS,
        sa.Column("id", sa.CHAR(36), nullable=False),
        sa.Column("cycle_id", sa.CHAR(36), nullable=False),
        sa.Column("person_id", persons_id, nullable=True),
        sa.Column("scope", sa.String(32), nullable=False),
        sa.Column("role_name", sa.String(128), nullable=False),
        sa.Column("holder_external_id", sa.String(255), nullable=True),
        sa.Column("holder_email", sa.String(255), nullable=True),
        sa.Column("holder_display_name", sa.String(255), nullable=True),
        sa.Column("snapshot_at", sa.DateTime(timezone=True), nullable=False),
        sa.Column("decision", sa.String(32), nullable=False,
                  server_default=sa.text("'pending'")),
        sa.Column("decided_by_person_id", persons_id, nullable=True),
        sa.Column("decided_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("decision_note", sa.Text(), nullable=True),
        sa.Column("revocation_completed_at", sa.DateTime(timezone=True),
                  nullable=True),
        sa.Column("revocation_trace_id", sa.CHAR(36), nullable=True),
        sa.Column("raw_payload", sa.JSON(), nullable=True),
        sa.Column("assigned_to_person_id", persons_id, nullable=True),
        sa.Column("decided_at_deadline", sa.DateTime(timezone=True),
                  nullable=True),
        sa.Column("escalated_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("created_at", sa.DateTime(timezone=True),
                  server_default=sa.func.now(), nullable=False),
        sa.Column("updated_at", sa.DateTime(timezone=True),
                  server_default=sa.func.now(), nullable=False),
        sa.PrimaryKeyConstraint("id"),
        sa.ForeignKeyConstraint(["cycle_id"], [f"{_CYCLES}.id"],
                                ondelete="CASCADE"),
        sa.ForeignKeyConstraint(["person_id"], ["identity_persons.id"]),
        sa.ForeignKeyConstraint(["assigned_to_person_id"],
                                ["identity_persons.id"], ondelete="SET NULL"),
        **table_kw,
    )
    op.create_index("idx_auditor_priv_holder_cycle_decision",
                    _HOLDERS, ["cycle_id", "decision"])