"""Add (research_id, status) composite index to download_queue

The ``documents`` table already has ``idx_research_documents`` covering the
``(research_id, status)`` filter pattern, but ``download_queue`` only had
a single-column index on ``research_id``.  Every ``download_bulk``,
``queue_all_undownloaded``, and background-scheduler query filters
``LibraryDownloadQueue`` by both columns, so a composite index lets
SQLite/SQLCipher satisfy the filter in one seek.

Mirrors the model-level declaration added to ``DownloadQueue`` in the
same change.

Revision ID: 0017
Revises: 0016
Create Date: 2026-06-20

Index Added:
=============
download_queue:
- idx_download_queue_research_status (research_id, status)

Migration Notes:
===============
- Index creation/drop are native SQLite operations (no batch mode needed).
- Guarded with an existence check for idempotency: fresh databases
  created after the model change already have the index via
  ``Base.metadata.create_all()`` in 0001, and 0001 stops producing
  ``download_queue`` once the model declares the index.
- ``if_not_exists=True`` provides additional safety.

Downgrade Behavior:
==================
The downgrade() function removes the index.  Table data is completely
preserved — only the index structure is removed.

Noted in review of #4685.
"""

from alembic import op
from sqlalchemy import inspect

# revision identifiers, used by Alembic.
revision = "0017"
down_revision = "0016"
branch_labels = None
depends_on = None

INDEX_NAME = "idx_download_queue_research_status"
TABLE_NAME = "download_queue"
COLUMNS = ["research_id", "status"]


def _index_exists(index_name: str, table_name: str) -> bool:
    """Check if an index exists on a table.

    Creates a fresh inspector each call because inspector caches
    per-instance — reusing after DDL returns stale data.
    """
    bind = op.get_bind()
    inspector = inspect(bind)

    if not inspector.has_table(table_name):
        return False

    existing_indexes = inspector.get_indexes(table_name)
    return any(idx["name"] == index_name for idx in existing_indexes)


def upgrade():
    """Add the composite (research_id, status) index to download_queue."""
    bind = op.get_bind()
    inspector = inspect(bind)

    if not inspector.has_table(TABLE_NAME):
        # Table doesn't exist yet, will be created by initial migration
        # with the index already in place via Base.metadata.create_all().
        return

    if not _index_exists(INDEX_NAME, TABLE_NAME):
        op.create_index(
            INDEX_NAME,
            TABLE_NAME,
            COLUMNS,
            unique=False,
            if_not_exists=True,
        )


def downgrade():
    """Remove the composite (research_id, status) index from download_queue."""
    bind = op.get_bind()
    inspector = inspect(bind)

    if not inspector.has_table(TABLE_NAME):
        return

    if _index_exists(INDEX_NAME, TABLE_NAME):
        op.drop_index(INDEX_NAME, table_name=TABLE_NAME)
