"""Tests for migration 0017: add (research_id, status) composite index to download_queue.

Covers:
- The upgrade adds the composite index to download_queue.
- Idempotency: re-running upgrade on a DB that already has the index is a no-op.
- Missing-table guard: upgrade is a no-op when download_queue doesn't exist.
- Downgrade removes the index; table data is preserved.
- Head-alignment: 0017 is the latest revision (the guard lives in the newest
  migration's test file; 0016 took it from 0015, 0017 takes it from 0016).
"""

import pytest
from alembic import command
from sqlalchemy import create_engine, inspect, text

from local_deep_research.database.alembic_runner import (
    get_alembic_config,
    stamp_database,
)


def _run_upgrade_to(engine, revision):
    config = get_alembic_config(engine)
    with engine.begin() as conn:
        config.attributes["connection"] = conn
        command.upgrade(config, revision)


def _run_downgrade_to(engine, revision):
    config = get_alembic_config(engine)
    with engine.begin() as conn:
        config.attributes["connection"] = conn
        command.downgrade(config, revision)


def _index_names(engine, table):
    insp = inspect(engine)
    if not insp.has_table(table):
        return set()
    return {idx["name"] for idx in insp.get_indexes(table) if idx["name"]}


@pytest.fixture
def legacy_engine(tmp_path):
    """A DB whose download_queue table lacks the composite index.

    Simulates an installation created before 0017. Stamped at 0016 so 0017's
    upgrade is a clean delta.
    """
    db_path = tmp_path / "legacy_pre_0017.db"
    engine = create_engine(f"sqlite:///{db_path}")

    with engine.begin() as conn:
        conn.execute(
            text(
                "CREATE TABLE download_queue ("
                "id INTEGER PRIMARY KEY AUTOINCREMENT, "
                "research_id INTEGER NOT NULL, "
                "status VARCHAR(50) NOT NULL DEFAULT 'pending', "
                "url TEXT NOT NULL, "
                "created_at TEXT NOT NULL"
                ")"
            )
        )
        conn.execute(
            text(
                "CREATE INDEX idx_download_queue_research_id "
                "ON download_queue (research_id)"
            )
        )
        conn.execute(
            text(
                "INSERT INTO download_queue "
                "(research_id, status, url, created_at) "
                "VALUES (1, 'pending', 'https://example.com', '2026-01-01 00:00:00')"
            )
        )

    stamp_database(engine, "0016")
    yield engine
    engine.dispose()


class TestMigration0017Upgrade:
    def test_adds_composite_index(self, legacy_engine):
        engine = legacy_engine
        assert "idx_download_queue_research_status" not in _index_names(
            engine, "download_queue"
        )
        _run_upgrade_to(engine, "0017")
        assert "idx_download_queue_research_status" in _index_names(
            engine, "download_queue"
        )

    def test_preserves_row_data(self, legacy_engine):
        engine = legacy_engine
        _run_upgrade_to(engine, "0017")
        with engine.begin() as conn:
            count = conn.execute(
                text("SELECT COUNT(*) FROM download_queue")
            ).scalar()
        assert count == 1

    def test_upgrade_is_idempotent(self, legacy_engine):
        engine = legacy_engine
        _run_upgrade_to(engine, "0017")
        _run_upgrade_to(engine, "0017")  # second run is a no-op, no error
        assert "idx_download_queue_research_status" in _index_names(
            engine, "download_queue"
        )

    def test_missing_table_does_not_crash(self, tmp_path):
        """Upgrade is a no-op when download_queue doesn't exist yet."""
        engine = create_engine(f"sqlite:///{tmp_path}/no_queue.db")
        stamp_database(engine, "0016")
        _run_upgrade_to(engine, "0017")  # must not raise
        assert not inspect(engine).has_table("download_queue")
        engine.dispose()


class TestMigration0017Downgrade:
    def test_downgrade_removes_index(self, legacy_engine):
        engine = legacy_engine
        _run_upgrade_to(engine, "0017")
        assert "idx_download_queue_research_status" in _index_names(
            engine, "download_queue"
        )
        _run_downgrade_to(engine, "0016")
        assert "idx_download_queue_research_status" not in _index_names(
            engine, "download_queue"
        )

    def test_downgrade_preserves_row_data(self, legacy_engine):
        engine = legacy_engine
        _run_upgrade_to(engine, "0017")
        _run_downgrade_to(engine, "0016")
        with engine.begin() as conn:
            count = conn.execute(
                text("SELECT COUNT(*) FROM download_queue")
            ).scalar()
        assert count == 1


# Head-alignment guard moved to test_migration_0018_remove_mcp_strategy.py:
# 0017 is no longer the latest revision (0018 chains after it). The guard
# always lives in the newest migration's test file.
