"""Seed the ``security_officer`` (安全员) role and grant it to the desk.

The people who actually run security operations — triage external
vulnerability reports, set severity, decide bounty amounts, reply to
researchers — had no role of their own. They were either given
``global_admin`` (far more authority than the job needs) or ``auditor``
(a governance seat with access-certification decision authority that is
supposed to be segregated from the operators it reviews).

``security_officer`` passes ``can_view_security`` and
``can_manage_security`` and nothing else.

Grants are by work email and are **skipped, with a log line, for anyone
not found** — a missing person is a data question, not a reason to fail
a deploy. Idempotent: re-running grants nobody twice.

Revision ID: 541
Revises: 540
"""
from __future__ import annotations

import uuid

import sqlalchemy as sa

from alembic import op

revision = "541"
down_revision = "540"
branch_labels = None
depends_on = None

ROLE = "security_officer"
DESCRIPTION = "安全员 —— 安全域读写(漏洞赏金、合规控制、证据),不含全局管理员权限"

#: The desk as of 2026-08-25, per the platform owner.
GRANTEES = (
    "zhuoran.li@pingcap.com",
    "dong.zhang@pingcap.com",
    "xiaosong.hu@pingcap.com",
    "mingda.cai@pingcap.com",
)


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


def upgrade() -> None:
    bind = op.get_bind()
    if not (
        _table_exists(bind, "auth_roles")
        and _table_exists(bind, "auth_user_roles")
        and _table_exists(bind, "identity_persons")
    ):
        return

    role_id = bind.execute(
        sa.text("SELECT id FROM auth_roles WHERE name = :n"), {"n": ROLE},
    ).scalar()
    if role_id is None:
        role_id = str(uuid.uuid4())
        bind.execute(
            sa.text(
                "INSERT INTO auth_roles (id, name, description, is_system, "
                " created_at) "
                "VALUES (:id, :n, :d, 1, UTC_TIMESTAMP())"
            ),
            {"id": role_id, "n": ROLE, "d": DESCRIPTION},
        )

    for email in GRANTEES:
        person_id = bind.execute(
            sa.text(
                "SELECT id FROM identity_persons "
                "WHERE work_email = :e AND deleted_at IS NULL LIMIT 1"
            ),
            {"e": email},
        ).scalar()
        if person_id is None:
            # Loud but non-fatal: a name that does not resolve is a data
            # question for the owner, not a reason to abort a deploy
            # mid-chain (DDL above has already committed).
            print(f"[541] {email}: no active person row — grant skipped")
            continue
        already = bind.execute(
            sa.text(
                "SELECT 1 FROM auth_user_roles "
                "WHERE user_id = :u AND role_id = :r LIMIT 1"
            ),
            {"u": person_id, "r": role_id},
        ).scalar()
        if already:
            continue
        bind.execute(
            sa.text(
                "INSERT INTO auth_user_roles (user_id, role_id) "
                "VALUES (:u, :r)"
            ),
            {"u": person_id, "r": role_id},
        )
        print(f"[541] {email}: granted {ROLE}")


def downgrade() -> None:
    bind = op.get_bind()
    if not _table_exists(bind, "auth_roles"):
        return
    role_id = bind.execute(
        sa.text("SELECT id FROM auth_roles WHERE name = :n"), {"n": ROLE},
    ).scalar()
    if role_id is None:
        return
    # Grants first (FK), then the role row itself.
    bind.execute(
        sa.text("DELETE FROM auth_user_roles WHERE role_id = :r"),
        {"r": role_id},
    )
    bind.execute(
        sa.text("DELETE FROM auth_roles WHERE id = :r"), {"r": role_id},
    )