"""Add performance indexes

Revision ID: 002_performance_indexes
Revises: 001_initial
Create Date: 2025-01-26 00:00:00.000000

"""

from collections.abc import Sequence

from alembic import op

# revision identifiers, used by Alembic.
revision: str = "002_performance_indexes"
down_revision: str | None = "001_initial"
branch_labels: str | Sequence[str] | None = None
depends_on: str | Sequence[str] | None = None


def upgrade() -> None:
    """Add performance indexes for common query patterns."""

    # Composite index for rating snapshot queries (latest rating per entity)
    op.create_index(
        "ix_rating_snapshots_entity_race",
        "rating_snapshots",
        ["entity_type", "entity_id", "as_of_race_id"],
        unique=False,
    )

    # Index for rating snapshots by rating (for top-N queries)
    op.create_index(
        "ix_rating_snapshots_rating",
        "rating_snapshots",
        ["entity_type", "rating"],
        unique=False,
    )

    # Index for venue queries
    op.create_index("ix_meetings_venue", "meetings", ["venue"], unique=False)

    # Composite index for barrier adjustments lookups
    op.create_index(
        "ix_barrier_adjustments_lookup",
        "barrier_adjustments",
        ["venue", "start_type", "distance_bucket", "barrier"],
        unique=False,
    )

    # Composite index for handicap adjustments lookups
    op.create_index(
        "ix_handicap_adjustments_lookup",
        "handicap_adjustments",
        ["venue", "start_type", "distance_bucket", "handicap_m"],
        unique=False,
    )

    # NOTE: ix_starters_horse_id, ix_starters_driver_id, and ix_starters_trainer_id
    # are already created in the initial schema migration (001_initial)
    # No need to create them again here


def downgrade() -> None:
    """Remove performance indexes."""

    # Drop only the indexes created by this migration
    # (ix_starters_* indexes are owned by the initial schema migration)
    op.drop_index("ix_handicap_adjustments_lookup", table_name="handicap_adjustments")
    op.drop_index("ix_barrier_adjustments_lookup", table_name="barrier_adjustments")
    op.drop_index("ix_meetings_venue", table_name="meetings")
    op.drop_index("ix_rating_snapshots_rating", table_name="rating_snapshots")
    op.drop_index("ix_rating_snapshots_entity_race", table_name="rating_snapshots")