"""Make global-admin cloud grants perpetual.

Revision ID: 440
Revises: 439
Create Date: 2026-08-02

The 2026-07-29 repair made ``cloud_admin`` grants perpetual, but explicitly
left ``global_admin`` on the ordinary expiry lifecycle. Product policy was
clarified on 2026-08-02: global administrators are also incident responders
and their cloud access must never be reminded or automatically reclaimed.

This idempotent data repair clears TTLs and stale reminder markers from every
active grant held by a canonical ``global_admin`` or legacy ``it_admin``.
The matching code change teaches the shared perpetual-person predicate about
the same role set, so future approvals, renewals, cloud discovery, and both
expiry passes cannot drift back.

Downgrade is deliberately a no-op. The original per-row expiry cannot be
reconstructed after normalization, and inventing a new TTL during rollback
would silently weaken the approved availability policy.
"""

import sqlalchemy as sa

from alembic import op

revision = "440"
down_revision = "439"
branch_labels = None
depends_on = None

_NORMALIZE_GLOBAL_ADMINS = """
UPDATE cmdb_cloud_access_grants g
JOIN auth_user_roles ur ON ur.user_id = g.person_id
JOIN auth_roles r ON r.id = ur.role_id
SET g.expires_at = NULL,
    g.last_notified_stage = NULL
WHERE r.name IN ('global_admin', 'it_admin')
  AND g.status = 'active'
  AND g.expires_at IS NOT NULL
"""


def upgrade() -> None:
    rows = op.get_bind().execute(sa.text(_NORMALIZE_GLOBAL_ADMINS)).rowcount or 0
    print(f"[440] global-admin grants normalized to perpetual: {rows}")


def downgrade() -> None:
    """No-op: prior expiries are unrecoverable and were policy-invalid."""