"""One-shot cleanup of false stale-grant alerts surfaced by PR #170.

Revision ID: 118
Revises: 117
Create Date: 2026-05-05

Three production conditions need cleaning up *before* the next
inactivity sweep so the IT admin / cloud admin team stops getting
false ``[云账号不活跃]`` notifications:

  1. **Privileged-role grants flagged as inactive.** Cloud admins
     (Kidd) and global admins (Xiaopeng) hold legitimate Reader-equivalent
     access on every cloud scope so they can investigate billing /
     incidents. ``last_login_at`` is NULL because Helix doesn't track
     SSO logins to cloud consoles. The pre-PR sweep advanced these to
     ``inactive_owner`` in bulk (~180 rows in prod). The fix in
     ``sweep_inactive_grants`` skips these going forward; this
     migration retroactively clears the ``inactive_owner`` /
     ``inactive_it`` markers so the next sweep doesn't pick them up
     again from the wrong stage.

  2. **AWS self-service managed-policy grants.**
     ``IAMUserChangePassword`` / ``IAMUserSSHKeys`` /
     ``IAMSelfManageServiceSpecificCredentials`` are auto-attached by
     AWS for credential rotation; the IAM-derived-grants pipeline
     mistakenly tracked them as discovered grants. The filter in
     ``_derive_grants_from_iam_users`` drops them on future syncs;
     this migration revokes the existing rows with reason
     ``drift_removed`` (consistent with the new mark-and-sweep) so
     they stop showing in ``/my-grants`` and stop being eligible for
     the inactivity sweep.

All operations are idempotent (use UPDATE ... WHERE conditions only
hit the bug-affected rows) and reversible (the downgrade re-flags
the privileged rows / re-actives the self-service rows, but the
fixed code paths would refuse to re-flag them, so a downgrade after
the new code lands is more about completeness than rollback).

Numbers measured 2026-05-05 against prod TiDB:
  * Privileged-role flags to clear: 179
  * Self-service-policy rows to revoke: 23
"""
from __future__ import annotations

import sqlalchemy as sa

from alembic import op

revision = "118"
down_revision = "117"
branch_labels = None
depends_on = None


# Keep aligned with ``_AWS_SELF_SERVICE_MANAGED_POLICIES`` in
# ``app/domains/cmdb/lifecycle_service.py``. Any addition there must
# be reflected here for retroactive cleanup of pre-deploy rows.
_SELF_SERVICE_ROLE_KEYS = (
    "arn:aws:iam::aws:policy/IAMUserChangePassword",
    "arn:aws:iam::aws:policy/IAMUserSSHKeys",
    "arn:aws:iam::aws:policy/IAMSelfManageServiceSpecificCredentials",
    "IAMUserChangePassword",
    "IAMUserSSHKeys",
    "IAMSelfManageServiceSpecificCredentials",
)


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

    # ── Step 1: clear inactive-stage flags on privileged-role grants ──
    bind.execute(sa.text(
        """
        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.last_notified_stage = NULL,
            g.updated_at = NOW()
        WHERE g.last_notified_stage IN ('inactive_owner', 'inactive_it')
          AND g.status = 'active'
          AND r.name IN ('cloud_admin', 'it_admin', 'global_admin')
        """,
    ))

    # ── Step 2: revoke self-service AWS managed-policy grants ────────
    # Use ``bindparam(expanding=True)`` so the IN-list stays
    # parametrised even if a future caller swaps the constant tuple
    # for a config-driven list. Plain string-interpolation here would
    # work today (the constants are hardcoded ASCII) but is a known
    # SQL-injection footgun if the source ever changes.
    bind.execute(
        sa.text(
            """
            UPDATE cmdb_cloud_access_grants
            SET status = 'revoked',
                revoke_reason = 'drift_removed',
                revoked_at = NOW(),
                last_notified_stage = NULL,
                updated_at = NOW()
            WHERE source = 'discovered'
              AND status = 'active'
              AND role_key IN :keys
            """,
        ).bindparams(sa.bindparam("keys", expanding=True)),
        {"keys": list(_SELF_SERVICE_ROLE_KEYS)},
    )


def downgrade() -> None:
    """Best-effort rollback. The exact pre-migration ``last_notified_stage``
    is not preserved (we only know the rows were in ``inactive_owner`` /
    ``inactive_it`` — most were ``inactive_owner``, so we restore that).
    Self-service rows are restored to ``active``; the new filter would
    re-revoke them on the next sync if the new code path is still in
    place, so this downgrade is realistically only used in a code
    rollback scenario.
    """
    bind = op.get_bind()
    bind.execute(sa.text(
        """
        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.last_notified_stage = 'inactive_owner',
            g.updated_at = NOW()
        WHERE g.last_notified_stage IS NULL
          AND g.status = 'active'
          AND r.name IN ('cloud_admin', 'it_admin', 'global_admin')
        """,
    ))

    bind.execute(
        sa.text(
            """
            UPDATE cmdb_cloud_access_grants
            SET status = 'active',
                revoke_reason = NULL,
                revoked_at = NULL,
                updated_at = NOW()
            WHERE source = 'discovered'
              AND status = 'revoked'
              AND revoke_reason = 'drift_removed'
              AND role_key IN :keys
            """,
        ).bindparams(sa.bindparam("keys", expanding=True)),
        {"keys": list(_SELF_SERVICE_ROLE_KEYS)},
    )