"""Seed the ``offboarding.revoke_leaver_platform_roles`` scheduled job.

Closes the offboarding gap where a departed account
(``employment_status='left'``) kept manually-granted privileged
platform roles (global_admin / auditor / cloud_admin / hr / finance /
management / asset_global_admin) indefinitely — observed in prod with a
已离职 colleague still holding 全局管理员 as an 附加角色.

The primary cleanup runs inline at offboarding completion
(``OffboardingService.complete_process`` →
``AuthService.revoke_platform_roles_for_leaver``). This cron is the
backstop: it sweeps every ``left`` person who still carries a revocable
role, so it both (a) backfills leavers who completed BEFORE the inline
step existed, and (b) catches drift (a role granted to someone already
left). The handler ``run_revoke_leaver_platform_roles`` (in
:mod:`app.scheduled_jobs.handlers`) is idempotent — a person whose roles
are already gone is a no-op.

Daily cadence (86400s) is enough: a leaver's elevated grants gone within
24h, with the inline path handling the well-behaved case immediately.

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 = "290"
down_revision = "289"
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, interval_seconds, timezone, enabled, config, "
            "run_count, failure_count, created_at, updated_at) "
            "SELECT :id, :name, :display, :desc, :handler, 'interval', "
            ":interval, '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": "offboarding.revoke_leaver_platform_roles",
            "display": "离职人员系统角色清理",
            "desc": (
                "每日扫所有 employment_status='left' 且仍持有特权平台角色"
                "(global_admin / auditor / cloud_admin / hr / finance / "
                "management / asset_global_admin)的人员,吊销这些授予。"
                "离职流程内系统权限清理的兜底:offboarding 完成时已就地"
                "吊销,该 cron 补撤本特性上线前已 left 的历史账号以及"
                "离职后被误授予的漂移。复用 "
                "AuthService.revoke_platform_roles_for_leaver,幂等;"
                "不动 employee 与 asset_admin(后者由 office_admin_sync "
                "派生)。末位 global_admin 跳过并告警,避免锁死系统。"
            ),
            "handler": "offboarding.revoke_leaver_platform_roles",
            "interval": 86400,
            "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 = 'offboarding.revoke_leaver_platform_roles'"
        )
    )