"""Repair schema drift — idempotent retry of 0039 changes.

Revision ID: 0040
Revises: 0039
Create Date: 2026-05-07

Context
-------
Migration 0039 dropped ix_musehub_coord_reservations_repo_id but on some
environments that index was absent, causing the migration to fail.  The
deploy script stamped the alembic_version to 0039 and continued, leaving
all 0039 changes unapplied on those environments.

This migration re-applies all 0039 changes using IF EXISTS / IF NOT EXISTS
guards so it is safe to run regardless of which state the DB is in.
"""
from __future__ import annotations

from alembic import op
import sqlalchemy as sa


revision: str = "0040"
down_revision: str | None = "0039"
branch_labels = None
depends_on = None


def upgrade() -> None:
    # muse_tags.tag_id — widen VARCHAR(36) to VARCHAR(128) if not already done
    bind = op.get_bind()
    cols = {c["name"]: c for c in sa.inspect(bind).get_columns("muse_tags")}
    tag_id_col = cols.get("tag_id")
    if tag_id_col and isinstance(tag_id_col["type"], sa.VARCHAR) and tag_id_col["type"].length == 36:
        op.alter_column(
            "muse_tags", "tag_id",
            existing_type=sa.VARCHAR(length=36),
            type_=sa.String(length=128),
            existing_nullable=False,
        )

    # coord_reservations — drop wrong-named index; ensure correct name exists
    op.execute("DROP INDEX IF EXISTS ix_musehub_coord_reservations_repo_id")
    op.execute(
        "CREATE INDEX IF NOT EXISTS ix_coord_reservations_repo_id "
        "ON musehub_coord_reservations(repo_id)"
    )

    # breakage_issues — add missing index
    op.execute(
        "CREATE INDEX IF NOT EXISTS ix_musehub_intel_breakage_issues_repo_id "
        "ON musehub_intel_breakage_issues(repo_id)"
    )

    # refactor_events — add missing index
    op.execute(
        "CREATE INDEX IF NOT EXISTS ix_musehub_intel_refactor_events_repo_id "
        "ON musehub_intel_refactor_events(repo_id)"
    )

    # velocity — drop stale composite index
    op.execute("DROP INDEX IF EXISTS ix_intel_velocity_repo_active")

    # symbol_intel — drop stale DESC-expression blast index
    op.execute("DROP INDEX IF EXISTS ix_symbol_intel_repo_blast")

    # symbol_intel gravity_pct — rebuild without DESC expression
    op.execute("DROP INDEX IF EXISTS ix_symbol_intel_repo_gravity_pct")
    op.execute(
        "CREATE INDEX IF NOT EXISTS ix_symbol_intel_repo_gravity_pct "
        "ON musehub_symbol_intel(repo_id, gravity_pct)"
    )


def downgrade() -> None:
    op.execute("DROP INDEX IF EXISTS ix_symbol_intel_repo_gravity_pct")
    op.execute(
        "CREATE INDEX IF NOT EXISTS ix_symbol_intel_repo_gravity_pct "
        "ON musehub_symbol_intel(repo_id, gravity_pct DESC NULLS LAST)"
    )
    op.execute(
        "CREATE INDEX IF NOT EXISTS ix_symbol_intel_repo_blast "
        "ON musehub_symbol_intel(repo_id, blast DESC NULLS LAST)"
    )
    op.execute(
        "CREATE INDEX IF NOT EXISTS ix_intel_velocity_repo_active "
        "ON musehub_intel_velocity(repo_id, active_commits)"
    )
    op.execute("DROP INDEX IF EXISTS ix_musehub_intel_refactor_events_repo_id")
    op.execute("DROP INDEX IF EXISTS ix_musehub_intel_breakage_issues_repo_id")
    op.execute("DROP INDEX IF EXISTS ix_coord_reservations_repo_id")
    op.execute(
        "CREATE INDEX IF NOT EXISTS ix_musehub_coord_reservations_repo_id "
        "ON musehub_coord_reservations(repo_id)"
    )
    cols = {c["name"]: c for c in sa.inspect(op.get_bind()).get_columns("muse_tags")}
    tag_id_col = cols.get("tag_id")
    if tag_id_col and isinstance(tag_id_col["type"], sa.VARCHAR) and tag_id_col["type"].length == 128:
        op.alter_column(
            "muse_tags", "tag_id",
            existing_type=sa.String(length=128),
            type_=sa.VARCHAR(length=36),
            existing_nullable=False,
        )