"""Drop cloud_admin role — merged into it_admin.

Revision ID: 040
Revises: 039
Create Date: 2026-04-21

Rationale
---------
The ``cloud_admin`` role was introduced early for a hypothetical
"dedicated cloud administrator" persona. In practice the platform has
one operator (solo), and the upcoming unified-integrations console
makes cloud accounts part of the general integrations surface. A
separate role layered on top adds per-page guards, view-mode
switching, and three frontend-backend naming discrepancies with zero
business value.

Decision: delete the role. Existing ``cloud_admin`` assignees get
upgraded to ``it_admin`` (the frontend ``global_admin`` — see
deriveDefaultViewRole in auth.ts).

Migration steps
---------------

1. Idempotent no-op guards: skip if either ``cloud_admin`` or
   ``it_admin`` (sometimes legacy-named ``global_admin``) is missing.
2. ``UPDATE auth_user_roles SET role_id = <it_admin.id> WHERE role_id = <cloud_admin.id>``
   — preserve the assignment, upgrade the authority.
3. De-duplicate: a user who had BOTH roles simultaneously now has two
   rows pointing at ``it_admin``; drop the redundant one via
   ``DELETE`` with ROW_NUMBER() window (TiDB/MySQL 8 both support).
4. ``DELETE FROM auth_roles WHERE name = 'cloud_admin'``.

Downgrade recreates the role row (empty — we don't remember who had
what, so no assignments are restored). Production has 0-1
``cloud_admin`` users so the audit log is the authoritative record.
"""
from __future__ import annotations

import sqlalchemy as sa
from alembic import op

revision = "040"
down_revision = "039"
branch_labels = None
depends_on = None


def _row_exists(bind, sql: str, **params) -> bool:
    return bind.execute(sa.text(sql), params).first() is not None


def upgrade() -> None:
    bind = op.get_bind()

    # Identify the two roles. Support both legacy it_admin naming
    # variants: `it_admin` (backend canonical) and `global_admin`
    # (frontend shim). In production both rows should not coexist;
    # if they do, prefer ``it_admin``.
    cloud_row = bind.execute(sa.text(
        "SELECT id FROM auth_roles WHERE name = 'cloud_admin' LIMIT 1"
    )).first()
    if cloud_row is None:
        # Nothing to do. Leave a harmless audit crumb.
        return
    cloud_id = cloud_row[0]

    it_row = bind.execute(sa.text(
        "SELECT id FROM auth_roles WHERE name IN ('it_admin', 'global_admin') "
        "ORDER BY CASE WHEN name = 'it_admin' THEN 0 ELSE 1 END LIMIT 1"
    )).first()
    if it_row is None:
        raise RuntimeError(
            "Cannot upgrade cloud_admin holders: no it_admin / global_admin "
            "role found in auth_roles. Seed the target role before "
            "running this migration."
        )
    it_id = it_row[0]

    # Upgrade existing assignments. Ignore clash if same (user, it_id) row
    # already exists — keep it, drop the cloud_admin-promoted one afterwards.
    bind.execute(sa.text(
        "UPDATE IGNORE auth_user_roles "
        "SET role_id = :it_id "
        "WHERE role_id = :cloud_id"
    ), {"it_id": it_id, "cloud_id": cloud_id})

    # The IGNORE clause leaves any still-cloud_admin rows that couldn't
    # be updated because the user already had it_admin. Delete those.
    bind.execute(sa.text(
        "DELETE FROM auth_user_roles WHERE role_id = :cloud_id"
    ), {"cloud_id": cloud_id})

    # Finally drop the role itself.
    bind.execute(sa.text(
        "DELETE FROM auth_roles WHERE id = :cloud_id"
    ), {"cloud_id": cloud_id})


def downgrade() -> None:
    """Recreate the cloud_admin role row.

    We can't restore who had it (the ``UPDATE`` merged it into
    ``it_admin``); the audit log is the authoritative record.
    """
    bind = op.get_bind()
    exists = bind.execute(sa.text(
        "SELECT 1 FROM auth_roles WHERE name = 'cloud_admin' LIMIT 1"
    )).first()
    if exists is not None:
        return
    bind.execute(sa.text(
        "INSERT INTO auth_roles (id, name, description, is_system) "
        "VALUES (REPLACE(UUID(), '-', ''), 'cloud_admin', "
        "'Cloud administrator', 1)"
    ))