"""repair perpetual cloud-admin grants + revived grants carrying closure fields

Data-only repair for the 2026-07-29 cn68 (aws-account 317766989874)
incident. No schema change — three UPDATEs, all idempotent.

Background
----------
"Perpetual" (``expires_at IS NULL``) was decided independently at each
write site, and one site did not decide it at all:

* ``_dispatch_grant`` (approve / owner-add) and ``_renew_grant_locked``
  both consulted the cloud-admin predicate and wrote NULL. Correct.
* ``_apply_discovered_grants`` (grants_sync) stamped
  ``now + scope.default_grant_ttl_days`` on EVERY discovered row,
  cloud admins included.
* ``sweep_expiring_grants`` had owner + prod exemptions but no
  cloud-admin exemption, while its sibling ``sweep_inactive_grants``
  did have one.

So a cloud admin whose access was discovered cloud-side (rather than
granted through Helix) received a 90-day clock and was auto-revoked at
T+0, while the UI displayed 「永久授权」 the entire time. Between
2026-07-24 and 2026-07-29, 162 grants belonging to three cloud admins
were revoked this way; 150 more were queued behind them.

The code fixes land in the same PR (a shared
``load_perpetual_person_ids`` predicate consulted by every writer and
both sweep passes, plus a normalization pass in the sweep). This
migration exists so the repair is applied at deploy time instead of
waiting for the next 08:15 cron tick, and so the repair is reviewable
and reproducible rather than a hand-run UPDATE against production.

What it repairs
---------------
1. Active grants held by a ``cloud_admin`` that carry a TTL → NULL.
   These are the 150 rows one sweep tick away from being revoked
   despite showing 「永久授权」.

2. Grants that are ``active`` but still carry ``revoked_at`` /
   ``revoke_reason`` from a closure they were later revived from. 24 rows
   were in this self-contradictory state. Nothing crashes on it — the
   ``ended_at`` / ``ended_reason`` virtual fields derive from ``status``
   — but every compliance query that counts ``revoke_reason='expired'``
   (reclaim scorecards, auditor exports) counts live access as reclaimed.

Deliberately NOT repaired here: scope-owner grants whose ``expires_at``
has rotted (140 such rows). Their exemption is real, so the stale date
harms nothing until ownership changes hands — and that moment is handled
at its cause in ``rebind_scope_owner``, which re-stamps the outgoing
owner's grants with a fresh TTL so they re-enter the reminder cycle
instead of being revoked with no warning. Writing a synthetic future
expiry onto rows that are exempt would replace one misleading value with
another.

Downgrade is a no-op: the pre-repair values were wrong, and restoring
them would re-arm the auto-revoke. Alembic requires the function to
exist, so it is documented rather than implemented.
"""
import sqlalchemy as sa

from alembic import op

revision = "436"
down_revision = "435"
branch_labels = None
depends_on = None

# 1. Perpetual (cloud_admin) holders must not carry a TTL.
#    ``last_notified_stage`` is cleared alongside so a holder who was
#    already nagged does not keep a stale marker that would suppress a
#    legitimate reminder if they ever lose the role.
_NORMALIZE_PERPETUAL = """
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 AND r.name = 'cloud_admin'
SET g.expires_at = NULL,
    g.last_notified_stage = NULL
WHERE g.status = 'active'
  AND g.expires_at IS NOT NULL
"""

# 2. Revived grants must not keep the closure fields of the sweep that
#    had previously closed them.
_CLEAR_REVIVED_CLOSURE = """
UPDATE cmdb_cloud_access_grants
SET revoked_at = NULL,
    revoke_reason = NULL,
    revoked_by_person_id = NULL
WHERE status = 'active'
  AND (revoked_at IS NOT NULL OR revoke_reason IS NOT NULL)
"""

_STEPS = (
    ("perpetual grants normalized to NULL expiry", _NORMALIZE_PERPETUAL),
    ("revived grants cleared of closure fields", _CLEAR_REVIVED_CLOSURE),
)


def upgrade() -> None:
    bind = op.get_bind()
    for label, stmt in _STEPS:
        rows = bind.execute(sa.text(stmt)).rowcount or 0
        print(f"[436] {label}: {rows}")


def downgrade() -> None:
    """No-op.

    The pre-repair values were incorrect (a TTL on a perpetual grant, a
    rotted expiry on an exempt grant, closure fields on live access).
    Restoring them would re-arm the silent auto-revoke this migration
    exists to disarm, and the original per-row values are not recoverable
    from the repaired state anyway.
    """