"""Identity-sync offboarding zero-trust: seed the leaver-assurance cron.

Part of the offboarding zero-trust closed loop (PR3 of the sync overhaul).
The write path (nightly sweep out-of-scope deactivate, offboard event push,
Feishu account_backstop) ASSERTS it revoked a leaver; this daily job
independently CHECKS it per enabled deactivate/delete target — from the
Helix side (a leaver still linked with a non-terminal state) and the vendor
side (a vendor snapshot record still active + matched to a leaver) — and
raises on any gap so the scheduled-jobs framework failure notifier pages IT
admins (never the leaver / their manager). DB-only; no vendor calls.
"""
import json as _json
import uuid

import sqlalchemy as sa

from alembic import op

revision = "405"
down_revision = "404"
branch_labels = None
depends_on = None


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


HANDLER_KEY = "identity_sync.leaver_assurance"
JOB_NAME = "identity_sync.leaver_assurance"

_DESC = (
    "每日反向核验已离职者在各启用回收目标上是否仍未确认回收(Helix 侧未终态 + "
    "厂商快照仍 active),命中即 raise 触达 IT 失败通知器(只推 IT)。独立于写路径的"
    "离职回收闭环,纯 DB 查询。"
)


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, 'UTC', 1, :config, 0, 0, NOW(), NOW() "
            "FROM DUAL WHERE NOT EXISTS "
            "(SELECT 1 FROM scheduled_jobs WHERE handler_key = :handler)"
        ),
        {
            "id": str(uuid.uuid4()),
            "name": JOB_NAME,
            "display": "数据同步中心 · 离职回收零信任对账",
            "desc": _DESC,
            "handler": HANDLER_KEY,
            # Daily at 03:47 UTC — offset off the top of the hour + after the
            # nightly sweeps (so it verifies the day's revocations, not races
            # them).
            "cron": "47 3 * * *",
            "config": _json.dumps({"max_report": 50}),
        },
    )


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},
        )