"""Backfill ``expires_at`` on existing discovered cloud grants.

Revision ID: 065
Revises: 064
Create Date: 2026-04-24

Renumbered from 063 → 065 post-merge: PR #30 (software_monthly_bills)
also used revision 063, causing alembic's "multiple heads" error on
main CI. Chaining from 064 (seed_software_billing_auto_close).

Context: reverse grants discovery (migration 061 + the
``_apply_discovered_grants`` path) was landing rows with
``source='discovered'`` and ``expires_at=NULL``. That meant the
``cmdb.access_expiry_sweep`` skipped them (the sweep filters on
``expires_at IS NOT NULL``), so pre-existing cloud-side role
assignments never auto-reclaimed — which defeats the whole point
of the auto-reclaim policy.

Fix going forward: the discovery code now stamps
``expires_at = now + scope.default_grant_ttl_days`` on insert.
This migration applies the same rule to the backlog of rows that
predate that change.

Anchor choice: ``COALESCE(last_seen_grants_sync_at, NOW())`` +
scope's ``default_grant_ttl_days``. We deliberately do NOT anchor
to ``granted_at`` — discovered grants often carry a cloud-side
``granted_at`` from years ago, so anchoring there would stamp an
already-past ``expires_at`` and trigger an instant mass-revoke on
the very next expiry sweep. Anchoring to last-sync-time (or now)
gives every existing grant a full fresh TTL window from the
deploy, so admins have time to review.

Only ``source='discovered'`` rows with ``status IN ('active',
'pending')`` AND ``expires_at IS NULL`` are touched. Helix-issued
rows (``source='helix'``) already have ``expires_at`` set by the
approval path — leave them alone.

Idempotent: re-running the migration is a no-op because the WHERE
clause filters out rows that already have ``expires_at`` populated.
"""
from __future__ import annotations

import sqlalchemy as sa

from alembic import op

revision = "065"
down_revision = "064"
branch_labels = None
depends_on = None


def upgrade() -> None:
    bind = op.get_bind()
    # UPDATE ... JOIN so we can pull scope.default_grant_ttl_days per
    # row. MySQL / TiDB both support this exact shape. NOW() is the
    # server-side "now" — gives the whole backlog the same anchor
    # regardless of per-row scheduling.
    bind.execute(sa.text("""
        UPDATE cmdb_cloud_access_grants g
        INNER JOIN cmdb_cloud_scopes s ON s.id = g.scope_id
        SET g.expires_at = DATE_ADD(
            COALESCE(g.last_seen_grants_sync_at, NOW()),
            INTERVAL s.default_grant_ttl_days DAY
        )
        WHERE g.source = 'discovered'
          AND g.expires_at IS NULL
          AND g.status IN ('active', 'pending')
    """))


def downgrade() -> None:
    # Reversing this is destructive (we'd be wiping expires_at on rows
    # that may have been renewed in the meantime). Leave the backfilled
    # values in place — worst case, a subsequent revoke explicitly sets
    # expires_at or a new request replaces the row.
    pass