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

Revision ID: 167
Revises: 166
Create Date: 2026-05-14

Closes the gap surfaced on prod 2026-05-14: the T+1 post-leaver-verify
sweep reports "P0546 已离职 但仍持有 cloud_access_grant" — i.e. the user
is in ``employment_status='left'`` but the cmdb_cloud_access_grants row
is still ``active`` / ``pending``. The primary force-transfer in
``hr_lifecycle.advance_service`` is supposed to revoke this at T+0, and
historically did so for current leavers, but two failure modes leave
stale residuals that no cron picks up:

1. The person advanced to ``left`` BEFORE the T+0 cloud_access_grant
   revoke code existed (pre-PR #206 / #214). Their offboarding ran to
   completion but the grants were never touched. Pure historical
   residual — no admin action could clean them up before the manual-
   revoke endpoint was wired to cloud_access_grant in this same PR.
2. The cloud-side role assignment was created AFTER the person was
   already ``left`` — e.g. someone added the role manually in the
   Azure portal, then ``cmdb.grants_sync`` reflected it into Helix.
   The offboarding pipeline is long gone for this person; no one sweeps
   the residual.

The new ``cmdb.leaver_grant_sweep`` cron runs daily at 01:30 (Asia/
Shanghai) — half an hour before ``hr.post_leaver_verify`` at 02:00, so
the residual alert pass sees a clean state after the auto-revoke
landed. No ``recent_days`` cutoff: this MUST scan all left persons
because the residuals it targets are exactly the ones the recent-only
T+1 verify can't reach.

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

import uuid

import sqlalchemy as sa

from alembic import op

revision = "167"
down_revision = "166"
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.leaver_grant_sweep",
            "display": "已离职员工云访问 grant 兜底回收",
            "desc": (
                "每日 01:30 扫描所有 employment_status='left' 的人,"
                "把他们名下任何仍为 active / pending 的 "
                "cmdb_cloud_access_grants 通过 driver 一并 revoke。"
                "兜底 hr.process_advance T+0 强制转让:历史离职 "
                "(P0546-class) 或云侧后于离职日才同步进来的 drift grant "
                "都在这里清掉。suppress_driver_error=True 保证 Helix "
                "侧 revoke 落盘,云侧失败置 requires_manual_cloud_revoke=True。"
                "无 recent_days 截断 —— 老的离职也要扫到。"
            ),
            "handler": "cmdb.leaver_grant_sweep",
            "cron": "30 1 * * *",
            "config": "{}",
        },
    )


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.leaver_grant_sweep'"
        )
    )