"""离职 T+1 安全网按 RES 注册表驱动 + 账号终态保障推广到名册(PR3 of 访问对象闭环)。

两件数据层面的事,其余都是代码:

1. **T+1 残留事件的 ``resource_type`` 改成 RES_* 注册表的名字。** ``hr.post_leaver_verify``
   此前自己写了一遍「这个人还持有什么」,四种网络资源用的是它自己发明的名字
   (``cloud_domain`` / ``cloud_dns_record`` / ``cloud_cdn_domain`` / ``cloud_load_balancer``),
   与离职扫描器、转岗复核包、前端标签用的 ``domain`` / ``dns_record`` / ``cdn_domain`` /
   ``load_balancer`` 对不上;而且注册表里 **15 种**资源(VPN / AI 工具 / GitHub 组织 /
   邮件组 / 软件负责人 / 系统负责人 / 审批流负责人 / 身份消费方 …)T+1 根本没扫。
   现在 T+1 走 ``leaver_residuals.scan_person_residuals``(注册表 × 谓词,谓词默认
   复用 ``mover_holdings._LOADERS``,每种一段、互相隔离),所以旧名字要改过来 ——
   否则同一条残留会以新名字再开一条事件,旧的永远挂着。只改 open / accepted 的
   ``leaver_orphan`` 事件;resolved 是历史,不动。

2. **播种 cron ``offboarding.account_assurance_rosters``**(每日 04:10 UTC,排在
   ``identity_sync.leaver_assurance`` 03:47 之后):把每个启用且 on_offboard ∈
   (deactivate, delete) 的身份同步目标的厂商名册快照,按人 × 系统写进
   ``offboarding_account_assurance``(system = ``isync:<slug>``),并给每个目标
   记一条快照新鲜度心跳。此前「账号终态」页只有 Google 与 Atlassian 两个系统;
   身份同步域的零信任对账只会在失败通知里喊一声,页面上看不见。

无新表、无新列。
"""
from __future__ import annotations

import uuid

import sqlalchemy as sa

from alembic import op

revision = "609"
down_revision = "608"
branch_labels = None
depends_on = None

INCIDENTS = "compliance_incidents"
JOBS = "scheduled_jobs"
JOB_ROSTERS = "offboarding.account_assurance_rosters"

RENAMES: tuple[tuple[str, str], ...] = (
    ("cloud_domain", "domain"),
    ("cloud_dns_record", "dns_record"),
    ("cloud_cdn_domain", "cdn_domain"),
    ("cloud_load_balancer", "load_balancer"),
)

_JOB_DESC = (
    "每日 04:10 UTC(排在 identity_sync.leaver_assurance 03:47 之后)。对每个启用且 "
    "on_offboard∈(deactivate,delete) 的身份同步目标,读厂商名册快照 "
    "(identity_sync_vendor_records),给每个离职者写一行 offboarding_account_assurance"
    "(system=isync:<slug>):快照里没有 → ok(absent);is_active=false 或厂商状态"
    "为停用 → ok(disabled);is_active=true 且快照拍摄日晚于离职日 → at_risk(有登记保留"
    "分类则 exempt);快照拍在离职之前或根本没有快照 → unknown(不折叠进 ok)。"
    "同时按目标记快照新鲜度心跳(chain_key identity_sync.snapshot:<slug>)。"
    "纯 DB 读,不调厂商;「立即处理」按钮走 IdentitySyncService 的按人回收。"
)


def _has_table(bind, name: 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": name},
    ).scalar())


def upgrade() -> None:
    bind = op.get_bind()
    if _has_table(bind, INCIDENTS):
        for old, new in RENAMES:
            bind.execute(
                sa.text(
                    f"UPDATE {INCIDENTS} SET resource_type = :new "
                    "WHERE incident_type = 'leaver_orphan' AND resource_type = :old "
                    "AND status IN ('open', 'accepted')",
                ),
                {"old": old, "new": new},
            )
    if _has_table(bind, JOBS):
        bind.execute(
            sa.text(
                f"INSERT INTO {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 "
                f"(SELECT 1 FROM {JOBS} WHERE handler_key = :handler)",
            ),
            {
                "id": str(uuid.uuid4()),
                "name": JOB_ROSTERS,
                "display": "离职账号终态 · 身份同步名册对账",
                "desc": _JOB_DESC,
                "handler": JOB_ROSTERS,
                "cron": "10 4 * * *",
                "config": "{}",
            },
        )


def downgrade() -> None:
    bind = op.get_bind()
    if _has_table(bind, JOBS):
        bind.execute(
            sa.text(f"DELETE FROM {JOBS} WHERE handler_key = :h"), {"h": JOB_ROSTERS},
        )
    if _has_table(bind, INCIDENTS):
        for old, new in RENAMES:
            bind.execute(
                sa.text(
                    f"UPDATE {INCIDENTS} SET resource_type = :old "
                    "WHERE incident_type = 'leaver_orphan' AND resource_type = :new "
                    "AND status IN ('open', 'accepted')",
                ),
                {"old": old, "new": new},
            )