"""Seed the ``cmdb.manual_revoke_retry_sweep`` scheduled job.

Revision ID: 172
Revises: 171
Create Date: 2026-05-16

Closes the cloud-side leak surfaced by the pre-launch round-N audit
(CR4 P1): ``_revoke_grant_locked(suppress_driver_error=True)`` stamps
the Helix row REVOKED + ``detail.requires_manual_cloud_revoke=True``
whenever the cloud-side call failed (5xx / throttle / IAM lock /
propagation delay). The drift sweep (``cmdb.grants_sync``) only closes
discovered-stale rows; it does NOT re-attempt the failed cloud revoke
on a Helix-revoked-but-cloud-still-active row. End state without this
sweep: leaver permission persists indefinitely on the cloud side
despite Helix showing it revoked.

The new ``cmdb.manual_revoke_retry_sweep`` cron runs hourly:
  * Discovers grants where ``status='revoked'`` AND
    ``detail.requires_manual_cloud_revoke=true`` AND
    ``revoked_at < now - 1h``.
  * Calls ``driver.revoke_access`` again per row in its own session.
  * On success / already_absent: clears the flag, writes audit
    ``cloud.grant.revoke_retry`` (success).
  * On failure: keeps the flag, updates ``last_error``, writes audit
    failure. Next hourly tick retries.

Idempotent — INSERT gated on a name-based NOT EXISTS check.
"""
from __future__ import annotations

import uuid

import sqlalchemy as sa

from alembic import op

revision = "172"
down_revision = "171"
branch_labels = None
depends_on = None


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


def upgrade() -> None:
    bind = op.get_bind()
    if not _table_exists(bind, "scheduled_jobs"):
        return
    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, :desc, :handler, 'cron', "
            ":cron, 'Asia/Shanghai', 1, :config, 0, 0, NOW(), NOW() "
            "FROM DUAL WHERE NOT EXISTS "
            "(SELECT 1 FROM scheduled_jobs WHERE name = :name)"
        ),
        {
            "id": str(uuid.uuid4()),
            "name": "cmdb.manual_revoke_retry_sweep",
            "display": "云端权限手动 revoke 重试",
            "desc": (
                "每小时扫一遍 status=revoked 且 detail.requires_manual_"
                "cloud_revoke=true 的 cmdb_cloud_access_grants:再次调 "
                "driver.revoke_access。云上确实已经回收就清掉标记,还失败"
                "就保留等下一轮。修复 _revoke_grant_locked("
                "suppress_driver_error=True) 之后云侧权限永久残留的漏洞。"
            ),
            "handler": "cmdb.manual_revoke_retry_sweep",
            # Every hour at :15 — staggered so it doesn't collide with
            # the top-of-hour cron stampede.
            "cron": "15 * * * *",
            "config": '{"min_age_seconds": 3600}',
        },
    )


def downgrade() -> None:
    bind = op.get_bind()
    if not _table_exists(bind, "scheduled_jobs"):
        return
    bind.execute(
        sa.text(
            "DELETE FROM scheduled_jobs "
            "WHERE handler_key = 'cmdb.manual_revoke_retry_sweep'"
        )
    )