"""Add composite indexes for high-traffic query patterns.

Composite indexes added:
  musehub_repos:      (owner, visibility)                      explore page
  musehub_repos:      (deleted_at)                             soft-delete filter
  musehub_commits:    (repo_id, branch)                        HEAD lookup
  musehub_commits:    (repo_id, timestamp)                     recent commits feed
  musehub_objects:    (repo_id, deleted_at)                    quota SUM
  musehub_issues:     (repo_id, state)                         open/closed filter
  musehub_issues:     (repo_id, number)                        per-repo lookup
  musehub_proposals:  (repo_id, state)                         open/closed/merged filter
  musehub_proposals:  (repo_id, pr_number)                     per-repo proposal lookup
  musehub_symbol_index: (repo_id, built_at)                    latest-index query

Revision ID: 0022
Revises: 0021
"""
from __future__ import annotations

from alembic import op

revision = "0022"
down_revision = "0021"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.create_index("ix_musehub_repos_owner_visibility", "musehub_repos", ["owner", "visibility"])
    op.create_index("ix_musehub_repos_deleted_at", "musehub_repos", ["deleted_at"])
    op.create_index("ix_musehub_commits_repo_branch", "musehub_commits", ["repo_id", "branch"])
    op.create_index("ix_musehub_commits_repo_timestamp", "musehub_commits", ["repo_id", "timestamp"])
    op.create_index("ix_musehub_objects_repo_deleted_at", "musehub_objects", ["repo_id", "deleted_at"])
    op.create_index("ix_musehub_issues_repo_state", "musehub_issues", ["repo_id", "state"])
    op.create_index("ix_musehub_issues_repo_number", "musehub_issues", ["repo_id", "number"])
    op.create_index("ix_musehub_proposals_repo_state", "musehub_proposals", ["repo_id", "state"])
    op.create_index("ix_musehub_proposals_repo_number", "musehub_proposals", ["repo_id", "pr_number"])
    op.create_index("ix_musehub_symbol_index_repo_built_at", "musehub_symbol_index", ["repo_id", "built_at"])


def downgrade() -> None:
    op.drop_index("ix_musehub_repos_owner_visibility", table_name="musehub_repos")
    op.drop_index("ix_musehub_repos_deleted_at", table_name="musehub_repos")
    op.drop_index("ix_musehub_commits_repo_branch", table_name="musehub_commits")
    op.drop_index("ix_musehub_commits_repo_timestamp", table_name="musehub_commits")
    op.drop_index("ix_musehub_objects_repo_deleted_at", table_name="musehub_objects")
    op.drop_index("ix_musehub_issues_repo_state", table_name="musehub_issues")
    op.drop_index("ix_musehub_issues_repo_number", table_name="musehub_issues")
    op.drop_index("ix_musehub_proposals_repo_state", table_name="musehub_proposals")
    op.drop_index("ix_musehub_proposals_repo_number", table_name="musehub_proposals")
    op.drop_index("ix_musehub_symbol_index_repo_built_at", table_name="musehub_symbol_index")