"""add connect_rank planner key

The baseline (b7c3e1a9d4f2) already creates connect_rank for fresh dbs. This
migration brings dbs already at the baseline (QA) to the same schema: add
connect_rank and rebuild the planner index on it. Idempotent (IF NOT EXISTS)
so it no-ops on a fresh db that ran the baseline.

connect_rank = -epoch(connect time) so a plain ASC index scan returns
newest-connect-first (DSQL has no DESC index keys nor backward scans). Existing
rows keep connect_rank NULL → they sort last (NULLS LAST) until they next
(re)connect; no backfill (a fresh db is empty anyway).

Revision ID: c8d2f4a6b1e9
Revises: b7c3e1a9d4f2
Create Date: 2026-06-26 12:00:00.000000+00:00

"""

from collections.abc import Sequence

from alembic import op

# revision identifiers, used by Alembic.
revision: str = "c8d2f4a6b1e9"
down_revision: str | Sequence[str] | None = "b7c3e1a9d4f2"
branch_labels: str | Sequence[str] | None = None
depends_on: str | Sequence[str] | None = None


def upgrade() -> None:
    # Nullable, no default → metadata-only on DSQL (no table rewrite).
    op.execute(
        "ALTER TABLE fan_connection ADD COLUMN IF NOT EXISTS connect_rank BIGINT"
    )
    op.execute("DROP INDEX IF EXISTS ix_fan_connection_planner")
    op.execute("""
        CREATE INDEX ASYNC IF NOT EXISTS ix_fan_connection_planner
            ON fan_connection (dsp_client_id, status, connect_rank)
            INCLUDE (last_collected_at, last_dispatched_at, consecutive_failures)
    """)


def downgrade() -> None:
    # Restore the baseline planner index exactly (last_collected_at key, with
    # last_connected_at carried in INCLUDE).
    op.execute("DROP INDEX IF EXISTS ix_fan_connection_planner")
    op.execute("""
        CREATE INDEX ASYNC IF NOT EXISTS ix_fan_connection_planner
            ON fan_connection (dsp_client_id, status, last_collected_at NULLS FIRST)
            INCLUDE (last_connected_at, last_dispatched_at, consecutive_failures)
    """)
    # DSQL has no DROP COLUMN; connect_rank stays (nullable, unused after revert).