"""Add indexes for common query patterns.

Revision ID: 001_add_indexes
Revises: None
Create Date: 2026-03-19

Indexes previously applied via raw SQL in database.py init_db().
This migration formalizes them under Alembic version control.
"""
from typing import Sequence, Union

from alembic import op

# revision identifiers, used by Alembic.
revision: str = "001_add_indexes"
down_revision: Union[str, None] = None
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    # scene_still — project+episode lookup
    op.create_index(
        "idx_scene_still_project_episode",
        "scene_still",
        ["project_id", "episode_id"],
        if_not_exists=True,
    )
    # image_asset — entity+type lookup
    op.create_index(
        "idx_image_asset_entity_type",
        "image_asset",
        ["entity_id", "asset_type"],
        if_not_exists=True,
    )
    # image_asset — still lookup
    op.create_index(
        "idx_image_asset_still",
        "image_asset",
        ["still_id"],
        if_not_exists=True,
    )
    # image_asset — project+episode+type
    op.create_index(
        "idx_image_asset_project_episode",
        "image_asset",
        ["project_id", "episode_id", "asset_type"],
        if_not_exists=True,
    )
    # entity_episode_link — canon+episode
    op.create_index(
        "idx_entity_episode_link_canon",
        "entity_episode_link",
        ["canon_id", "episode_id"],
        if_not_exists=True,
    )
    # pipeline_progress — project+episode+operation
    op.create_index(
        "idx_pipeline_progress_project",
        "pipeline_progress",
        ["project_id", "episode_id", "operation"],
        if_not_exists=True,
    )
    # llm_call_log — project+created_at
    op.create_index(
        "idx_llm_call_log_project",
        "llm_call_log",
        ["project_id", "created_at"],
        if_not_exists=True,
    )
    # activity_log — project+created_at
    op.create_index(
        "idx_activity_log_project",
        "activity_log",
        ["project_id", "created_at"],
        if_not_exists=True,
    )
    # character_outlook — character+outlook pair
    op.create_index(
        "idx_character_outlook_pair",
        "character_outlook",
        ["character_id", "outlook_id"],
        if_not_exists=True,
    )
    # image_asset — still+type+variant
    op.create_index(
        "idx_image_asset_variant",
        "image_asset",
        ["still_id", "asset_type", "variant_type"],
        if_not_exists=True,
    )


def downgrade() -> None:
    op.drop_index("idx_image_asset_variant", table_name="image_asset")
    op.drop_index("idx_character_outlook_pair", table_name="character_outlook")
    op.drop_index("idx_activity_log_project", table_name="activity_log")
    op.drop_index("idx_llm_call_log_project", table_name="llm_call_log")
    op.drop_index("idx_pipeline_progress_project", table_name="pipeline_progress")
    op.drop_index("idx_entity_episode_link_canon", table_name="entity_episode_link")
    op.drop_index("idx_image_asset_project_episode", table_name="image_asset")
    op.drop_index("idx_image_asset_still", table_name="image_asset")
    op.drop_index("idx_image_asset_entity_type", table_name="image_asset")
    op.drop_index("idx_scene_still_project_episode", table_name="scene_still")