"""Dedicated tests for migration 0016 (drop orphaned ``cache`` + ``search_cache``).

Mirrors the 0013/0014 convention: seed the pre-0016 state, run the upgrade,
and assert the destructive effect.

This file exists because the ``Cache``/``SearchCache`` models were removed in
the same PR, so no other test (and no migration) ever creates these tables.
Without this file, migration 0016 always reaches ``inspector.has_table()`` as
False in the test suite and its ``op.drop_table`` branch — the only code that
runs against real user databases — is never exercised. Here we recreate the
legacy tables at the revision before 0016 to simulate an existing user DB that
still carries them, then prove the upgrade drops them.

(0016 was renumbered from 0015 after a separate 0015_drop_document_notes
landed on main; it now chains after that revision.)
"""

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,
)
from local_deep_research.database.encrypted_db import DatabaseManager

# DDL for the two tables as they existed before this migration (created via
# Base.metadata from the now-removed ORM models). Recreated here so the test
# can simulate a pre-existing user database that still carries them, including
# every index the models declared.
_SEARCH_CACHE_DDL = [
    """
    CREATE TABLE search_cache (
        query_hash VARCHAR NOT NULL PRIMARY KEY,
        query_text TEXT NOT NULL,
        results JSON NOT NULL,
        created_at INTEGER NOT NULL,
        expires_at INTEGER NOT NULL,
        access_count INTEGER,
        last_accessed INTEGER NOT NULL
    )
    """,
    "CREATE INDEX idx_expires_at ON search_cache (expires_at)",
    "CREATE INDEX idx_last_accessed ON search_cache (last_accessed)",
]

_CACHE_DDL = [
    """
    CREATE TABLE cache (
        id INTEGER NOT NULL PRIMARY KEY,
        cache_key VARCHAR(255) NOT NULL,
        cache_value JSON,
        cache_text TEXT,
        cache_type VARCHAR(50),
        source VARCHAR(100),
        size_bytes INTEGER,
        ttl_seconds INTEGER,
        expires_at DATETIME,
        hit_count INTEGER,
        created_at DATETIME,
        accessed_at DATETIME
    )
    """,
    "CREATE UNIQUE INDEX ix_cache_cache_key ON cache (cache_key)",
    "CREATE INDEX ix_cache_expires_at ON cache (expires_at)",
    "CREATE INDEX idx_type_expires ON cache (cache_type, expires_at)",
    "CREATE INDEX idx_source_key ON cache (source, cache_key)",
]

_CACHE_INDEXES = {
    "idx_expires_at",
    "idx_last_accessed",
    "ix_cache_cache_key",
    "ix_cache_expires_at",
    "idx_type_expires",
    "idx_source_key",
}


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 _create_legacy_cache_tables(engine, *, populate):
    with engine.begin() as conn:
        for stmt in _SEARCH_CACHE_DDL + _CACHE_DDL:
            conn.execute(text(stmt))
        if populate:
            conn.execute(
                text(
                    "INSERT INTO search_cache "
                    "(query_hash, query_text, results, created_at, "
                    " expires_at, access_count, last_accessed) "
                    "VALUES ('h1', 'q', '[]', 1, 2, 1, 1)"
                )
            )
            conn.execute(
                text(
                    "INSERT INTO cache (cache_key, cache_text, hit_count) "
                    "VALUES ('k1', 'v', 0)"
                )
            )


def _tables(engine):
    return set(inspect(engine).get_table_names())


def _index_names(engine):
    with engine.begin() as conn:
        rows = conn.execute(
            text("SELECT name FROM sqlite_master WHERE type = 'index'")
        ).fetchall()
    return {r[0] for r in rows}


@pytest.fixture
def migrated_to_0015_engine(tmp_path):
    """A database upgraded through 0015 (the revision before 0016).

    At 0015 the cache/search_cache tables do NOT exist (their models were
    removed, so 0001's metadata-based create never produces them); tests that
    need the legacy tables add them explicitly.
    """
    engine = create_engine(f"sqlite:///{tmp_path / 'test_0016.db'}")
    _run_upgrade_to(engine, "0015")
    yield engine
    engine.dispose()


class TestMigration0016:
    def test_drops_both_tables_even_when_populated(
        self, migrated_to_0015_engine
    ):
        """Destructive path: tables present (with rows) at 0015 -> gone at 0016."""
        engine = migrated_to_0015_engine
        _create_legacy_cache_tables(engine, populate=True)
        assert {"cache", "search_cache"} <= _tables(engine)

        _run_upgrade_to(engine, "0016")

        remaining = _tables(engine)
        assert "cache" not in remaining
        assert "search_cache" not in remaining

    def test_indexes_dropped_with_tables(self, migrated_to_0015_engine):
        """Dropping the tables removes their indexes too (SQLite behavior)."""
        engine = migrated_to_0015_engine
        _create_legacy_cache_tables(engine, populate=False)
        assert _CACHE_INDEXES <= _index_names(engine)

        _run_upgrade_to(engine, "0016")

        assert not (_CACHE_INDEXES & _index_names(engine))

    def test_clean_noop_when_tables_absent(self, migrated_to_0015_engine):
        """Fresh-install / idempotent path: tables never existed -> clean no-op.

        This is exactly the state every fresh DB reaches (models removed, so
        0001 never creates the tables). The has_table guard must skip the drop
        without raising.
        """
        engine = migrated_to_0015_engine
        assert "cache" not in _tables(engine)
        assert "search_cache" not in _tables(engine)

        _run_upgrade_to(engine, "0016")  # must not raise

        remaining = _tables(engine)
        assert "cache" not in remaining
        assert "search_cache" not in remaining

    def test_0016_chains_to_0015(self, migrated_to_0015_engine):
        # Head-alignment (which revision is the tip) is asserted in the newest
        # migration's test file; here we only guard 0016's own chain link.
        from alembic.script import ScriptDirectory

        cfg = get_alembic_config(migrated_to_0015_engine)
        script = ScriptDirectory.from_config(cfg)
        rev = script.get_revision("0016")
        assert rev.down_revision == "0015"


class TestMigration0016Encrypted:
    """0016 must drop the tables on a real SQLCipher-encrypted user DB.

    Per-user encrypted databases are the production database type. The DDL is
    dialect-agnostic, but exercising the drop through the actual SQLCipher
    engine and the real DatabaseManager creation path guards against any
    encryption/connection-layer surprise. Skips only if a functional SQLCipher
    backend is genuinely unavailable.
    """

    def test_drops_tables_on_encrypted_user_db(self, tmp_path, monkeypatch):
        monkeypatch.setattr(
            "local_deep_research.database.encrypted_db.get_data_directory",
            lambda: tmp_path,
        )
        manager = DatabaseManager()
        if not manager.has_encryption:
            pytest.skip("Functional SQLCipher backend not available")

        username, password = "encuser", "TestPassword123!"
        engine = manager.create_user_database(username, password)
        try:
            # create_user_database migrated to head; roll the recorded version
            # back to 0015 and add the legacy tables to simulate a pre-0016
            # encrypted user DB that still carries them.
            stamp_database(engine, "0015")
            _create_legacy_cache_tables(engine, populate=True)
            assert {"cache", "search_cache"} <= _tables(engine)

            _run_upgrade_to(engine, "0016")

            remaining = _tables(engine)
            assert "cache" not in remaining
            assert "search_cache" not in remaining
        finally:
            manager.close_user_database(username)
