"""Leaver cloud-IAM residue governance table + daily reconcile cron.

Revision ID: 463
Revises: 462
Create Date: 2026-08-12

Closes the blind spot proven on the Tencent tenant: a leaver's grants are
revoked in Helix, the driver's cloud-side revoke silently under-delivers
(group-inherited policies, pre-2026-06-02 driver), and no sweep ever
looks again because every existing sweep filters on ``active`` /
``pending`` grants. ``cmdb_leaver_iam_residue`` records the per-(person,
IAM user) finding so the age clock, the alert dedup, and the dismissal
disposition all survive across runs.

Also seeds ``cmdb.leaver_iam_residue_sweep`` at 02:10 Asia/Shanghai —
deliberately after ``cmdb.leaver_grant_sweep`` (01:30) so the residue
check reads a world where the normal revoke path has already had its
chance, and before the working day starts so the alert is waiting when
IT logs in.
"""
from __future__ import annotations

import uuid

import sqlalchemy as sa

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

revision = "463"
down_revision = "462"
branch_labels = None
depends_on = None

TABLE = "cmdb_leaver_iam_residue"
JOB_NAME = "cmdb.leaver_iam_residue_sweep"
HANDLER_KEY = "cmdb.leaver_iam_residue_sweep"


def _table_exists(bind, table: str) -> bool:
    return bool(bind.execute(
        sa.text(
            "SELECT 1 FROM information_schema.tables "
            "WHERE table_schema = DATABASE() AND table_name = :t LIMIT 1",
        ),
        {"t": table},
    ).scalar())


def upgrade() -> None:
    bind = op.get_bind()

    if not _table_exists(bind, TABLE):
        # Three different parents (cmdb_iam_users, identity_persons,
        # integrations) — each FK column must match ITS OWN parent's
        # type + collation, or TiDB rejects with 3780 while local MySQL 8
        # happily accepts it. See ``id_type_for``'s docstring.
        op.create_table(
            TABLE,
            sa.Column("id", id_type_for("identity_persons"), primary_key=True),
            sa.Column(
                "iam_user_id", id_type_for("cmdb_iam_users"), nullable=False,
            ),
            sa.Column(
                "person_id", id_type_for("identity_persons"), nullable=False,
            ),
            sa.Column(
                "integration_id", id_type_for("integrations"), nullable=False,
            ),
            sa.Column("user_name", sa.String(255), nullable=False),
            sa.Column("provider", sa.String(32), nullable=False),
            sa.Column(
                "policy_count", sa.Integer(), nullable=False,
                server_default="0",
            ),
            sa.Column(
                "group_count", sa.Integer(), nullable=False,
                server_default="0",
            ),
            sa.Column("detail", sa.JSON(), nullable=True),
            sa.Column(
                "status", sa.String(16), nullable=False,
                server_default="open",
            ),
            sa.Column(
                "first_detected_at", sa.DateTime(timezone=True),
                nullable=False, server_default=sa.func.now(),
            ),
            sa.Column(
                "last_detected_at", sa.DateTime(timezone=True),
                nullable=False, server_default=sa.func.now(),
            ),
            sa.Column("resolution", sa.String(24), nullable=True),
            sa.Column(
                "resolved_at", sa.DateTime(timezone=True), nullable=True,
            ),
            sa.Column(
                "resolved_by_person_id", id_type_for("identity_persons"),
                nullable=True,
            ),
            sa.Column("resolution_note", sa.Text(), nullable=True),
            sa.Column(
                "created_at", sa.DateTime(timezone=True), nullable=False,
                server_default=sa.func.now(),
            ),
            sa.Column(
                "updated_at", sa.DateTime(timezone=True), nullable=False,
                server_default=sa.func.now(),
            ),
            sa.UniqueConstraint(
                "iam_user_id", "person_id",
                name="uq_leaver_iam_residue_user_person",
            ),
            sa.ForeignKeyConstraint(
                ["iam_user_id"], ["cmdb_iam_users.id"], ondelete="CASCADE",
            ),
            sa.ForeignKeyConstraint(
                ["person_id"], ["identity_persons.id"], ondelete="CASCADE",
            ),
            sa.ForeignKeyConstraint(
                ["integration_id"], ["integrations.id"],
            ),
            sa.ForeignKeyConstraint(
                ["resolved_by_person_id"], ["identity_persons.id"],
                ondelete="SET NULL",
            ),
            **table_kw_for("identity_persons"),
        )
        op.create_index(
            "ix_leaver_iam_residue_status", TABLE, ["status"],
        )

    if _table_exists(bind, "scheduled_jobs"):
        bind.execute(
            sa.text(
                "INSERT INTO scheduled_jobs ("
                "id, name, display_name, description, handler_key, "
                "trigger_type, cron_expr, timezone, enabled, config, "
                "run_count, failure_count, created_at, updated_at"
                ") SELECT "
                ":id, :name, :display_name, :description, :handler_key, "
                "'cron', :cron_expr, 'Asia/Shanghai', 1, NULL, "
                "0, 0, NOW(), NOW() "
                "FROM DUAL WHERE NOT EXISTS ("
                "SELECT 1 FROM scheduled_jobs WHERE name = :name)",
            ),
            {
                "id": str(uuid.uuid4()),
                "name": JOB_NAME,
                "display_name": "离职人员云 IAM 权限残留对账",
                "description": (
                    "每日 02:10 用云端 IAM 镜像（cmdb_iam_users）对账 HR 离职"
                    "名单：已过最后工作日的人若在云上仍持有策略或用户组，登记"
                    "为治理待办并通知 IT 管理员。只读，不做任何云端写操作 —— "
                    "清理动作在管理端人工确认后执行。"
                ),
                "handler_key": HANDLER_KEY,
                "cron_expr": "10 2 * * *",
            },
        )


def downgrade() -> None:
    bind = op.get_bind()
    if _table_exists(bind, "scheduled_jobs"):
        bind.execute(
            sa.text("DELETE FROM scheduled_jobs WHERE handler_key = :k"),
            {"k": HANDLER_KEY},
        )
    if _table_exists(bind, TABLE):
        op.drop_index("ix_leaver_iam_residue_status", table_name=TABLE)
        op.drop_table(TABLE)