"""cmdb_cloud_scopes — backfill NULLs and add server defaults.

Revision ID: 113
Revises: 112
Create Date: 2026-05-03

`environment`, `auto_suspend_after_days`, `default_grant_ttl_days`, and
`status` were declared NOT NULL with Python-side `default=`, but no
`server_default`. Rows inserted via raw SQL (or via the UPSERT path in
`sync_scopes_for_integration` before that path was hardened) could
land with NULL values, which would later crash code paths that read
those fields without a None-guard — e.g. `sweep_expiring_grants` doing
`scope.environment.lower()` on every active grant: one bad row aborts
the entire revoke pass mid-loop.

This migration:
1. Backfills any pre-existing NULLs to safe defaults.
2. Adds explicit DB-side server defaults so future inserts cannot
   regress.
"""
from alembic import op


revision = "113"
down_revision = "112"
branch_labels = None
depends_on = None


def upgrade() -> None:
    import sqlalchemy as sa

    op.execute(
        "UPDATE cmdb_cloud_scopes SET environment='dev' WHERE environment IS NULL"
    )
    op.execute(
        "UPDATE cmdb_cloud_scopes SET auto_suspend_after_days=60 "
        "WHERE auto_suspend_after_days IS NULL"
    )
    op.execute(
        "UPDATE cmdb_cloud_scopes SET default_grant_ttl_days=90 "
        "WHERE default_grant_ttl_days IS NULL"
    )
    op.execute(
        "UPDATE cmdb_cloud_scopes SET status='active' WHERE status IS NULL"
    )
    op.execute(
        "ALTER TABLE cmdb_cloud_scopes "
        "MODIFY COLUMN environment VARCHAR(16) NOT NULL DEFAULT 'dev'"
    )
    op.execute(
        "ALTER TABLE cmdb_cloud_scopes "
        "MODIFY COLUMN auto_suspend_after_days INTEGER NOT NULL DEFAULT 60"
    )
    op.execute(
        "ALTER TABLE cmdb_cloud_scopes "
        "MODIFY COLUMN default_grant_ttl_days INTEGER NOT NULL DEFAULT 90"
    )
    op.execute(
        "ALTER TABLE cmdb_cloud_scopes "
        "MODIFY COLUMN status VARCHAR(32) NOT NULL DEFAULT 'active'"
    )
    # Daily-spike alert dedupe stamp. Read+set inside
    # ``sync_billing_daily_for_integration`` so re-running the cron
    # (or admin clicking "run sync now") cannot double-fire the same
    # spike alert. NULL = never fired.
    bind = op.get_bind()
    insp = sa.inspect(bind)
    existing = {c["name"] for c in insp.get_columns("cmdb_cloud_scopes")}
    if "last_spike_alert_date" not in existing:
        op.add_column(
            "cmdb_cloud_scopes",
            sa.Column("last_spike_alert_date", sa.Date(), nullable=True),
        )


def downgrade() -> None:
    import sqlalchemy as sa

    bind = op.get_bind()
    insp = sa.inspect(bind)
    existing = {c["name"] for c in insp.get_columns("cmdb_cloud_scopes")}
    if "last_spike_alert_date" in existing:
        op.drop_column("cmdb_cloud_scopes", "last_spike_alert_date")
    op.execute(
        "ALTER TABLE cmdb_cloud_scopes MODIFY COLUMN environment VARCHAR(16) NOT NULL"
    )
    op.execute(
        "ALTER TABLE cmdb_cloud_scopes "
        "MODIFY COLUMN auto_suspend_after_days INTEGER NOT NULL"
    )
    op.execute(
        "ALTER TABLE cmdb_cloud_scopes "
        "MODIFY COLUMN default_grant_ttl_days INTEGER NOT NULL"
    )
    op.execute(
        "ALTER TABLE cmdb_cloud_scopes MODIFY COLUMN status VARCHAR(32) NOT NULL"
    )