"""Tests for migration 0014: add ldr_version + settings_snapshot to benchmark_runs.

Pins the upgrade/downgrade semantics:
- Upgrade adds both columns as nullable on existing benchmark_runs tables.
- Existing rows survive; their new columns are NULL.
- Downgrade drops both columns cleanly.
- Idempotent: re-running upgrade after partial application is a no-op.
- Missing-table guard: doesn't crash if benchmark_runs doesn't exist yet.

Note on test setup
==================
Migration 0001 calls ``Base.metadata.create_all()``, which creates tables
based on the CURRENT model class — so on a freshly initialised DB, the new
columns are present even before 0014 runs. To pin "0014 is the migration
that adds these columns" we use the same pattern as
``test_migration_0005_resource_document_id.py``: hand-build a "legacy"
``benchmark_runs`` table without the new columns, stamp the DB at
``down_revision``, and run the upgrade against that.
"""

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 _columns(engine, table):
    insp = inspect(engine)
    if not insp.has_table(table):
        return set()
    return {c["name"] for c in insp.get_columns(table)}


def _column_info(engine, table):
    insp = inspect(engine)
    if not insp.has_table(table):
        return {}
    return {c["name"]: c for c in insp.get_columns(table)}


@pytest.fixture
def legacy_engine(tmp_path):
    """A DB with a ``benchmark_runs`` table missing the new 0014 columns.

    Simulates an installation that's been running on revisions 0001–0013
    and never had the new ``ldr_version`` or ``settings_snapshot`` columns.
    Stamped at 0013 so 0014's upgrade is a clean delta.
    """
    db_path = tmp_path / "legacy_pre_0014.db"
    engine = create_engine(f"sqlite:///{db_path}")

    with engine.begin() as conn:
        # Build the pre-0014 benchmark_runs schema by hand. We only need
        # the NOT-NULL columns the migration will inspect; full schema
        # fidelity isn't required for column-add tests.
        conn.execute(
            text(
                "CREATE TABLE benchmark_runs ("
                "id INTEGER PRIMARY KEY AUTOINCREMENT, "
                "run_name VARCHAR(255), "
                "config_hash VARCHAR(16) NOT NULL, "
                "query_hash_list TEXT NOT NULL, "
                "search_config TEXT NOT NULL, "
                "evaluation_config TEXT NOT NULL, "
                "datasets_config TEXT NOT NULL, "
                "status VARCHAR(20) NOT NULL, "
                "created_at TEXT NOT NULL, "
                "updated_at TEXT NOT NULL, "
                "start_time TEXT, "
                "end_time TEXT, "
                "total_examples INTEGER NOT NULL DEFAULT 0, "
                "completed_examples INTEGER NOT NULL DEFAULT 0, "
                "failed_examples INTEGER NOT NULL DEFAULT 0, "
                "overall_accuracy REAL, "
                "processing_rate REAL, "
                "error_message TEXT"
                ")"
            )
        )

    # Stamp at the predecessor so alembic's view of state is consistent.
    stamp_database(engine, "0013")
    yield engine
    engine.dispose()


class TestMigration0014Upgrade:
    def test_adds_ldr_version_column(self, legacy_engine):
        engine = legacy_engine
        assert "ldr_version" not in _columns(engine, "benchmark_runs")
        _run_upgrade_to(engine, "0014")
        assert "ldr_version" in _columns(engine, "benchmark_runs")

    def test_adds_settings_snapshot_column(self, legacy_engine):
        engine = legacy_engine
        assert "settings_snapshot" not in _columns(engine, "benchmark_runs")
        _run_upgrade_to(engine, "0014")
        assert "settings_snapshot" in _columns(engine, "benchmark_runs")

    def test_columns_are_nullable(self, legacy_engine):
        engine = legacy_engine
        _run_upgrade_to(engine, "0014")
        cols = _column_info(engine, "benchmark_runs")
        assert cols["ldr_version"]["nullable"] is True
        assert cols["settings_snapshot"]["nullable"] is True

    def test_existing_row_survives_with_null_new_columns(self, legacy_engine):
        engine = legacy_engine
        with engine.begin() as conn:
            conn.execute(
                text(
                    "INSERT INTO benchmark_runs "
                    "(run_name, config_hash, query_hash_list, search_config, "
                    "evaluation_config, datasets_config, status, created_at, "
                    "updated_at, total_examples, completed_examples, "
                    "failed_examples) "
                    "VALUES ('pre-migration', 'h0', '[]', '{}', '{}', '{}', "
                    "'PENDING', '2026-01-01 00:00:00', '2026-01-01 00:00:00', "
                    "0, 0, 0)"
                )
            )

        _run_upgrade_to(engine, "0014")

        with engine.begin() as conn:
            row = conn.execute(
                text(
                    "SELECT run_name, ldr_version, settings_snapshot "
                    "FROM benchmark_runs WHERE run_name = 'pre-migration'"
                )
            ).fetchone()
        assert row is not None
        assert row[0] == "pre-migration"
        assert row[1] is None
        assert row[2] is None

    def test_can_write_new_columns_after_upgrade(self, legacy_engine):
        """Sanity check — the columns are usable, not just present."""
        engine = legacy_engine
        _run_upgrade_to(engine, "0014")
        with engine.begin() as conn:
            conn.execute(
                text(
                    "INSERT INTO benchmark_runs "
                    "(run_name, config_hash, query_hash_list, search_config, "
                    "evaluation_config, datasets_config, status, created_at, "
                    "updated_at, total_examples, completed_examples, "
                    "failed_examples, ldr_version, settings_snapshot) "
                    "VALUES ('post-migration', 'h1', '[]', '{}', '{}', '{}', "
                    "'COMPLETED', '2026-05-03 00:00:00', "
                    "'2026-05-03 00:00:00', 1, 1, 0, '1.6.10', "
                    """'{"llm.model": {"value": "qwen3.6:27b"}}')"""
                )
            )
            row = conn.execute(
                text(
                    "SELECT ldr_version, settings_snapshot "
                    "FROM benchmark_runs WHERE run_name = 'post-migration'"
                )
            ).fetchone()
        assert row[0] == "1.6.10"
        # SQLite stores JSON as TEXT — values come back as strings here.
        assert "qwen3.6:27b" in row[1]

    def test_upgrade_is_idempotent(self, legacy_engine):
        engine = legacy_engine
        _run_upgrade_to(engine, "0014")
        _run_upgrade_to(engine, "0014")
        cols = _columns(engine, "benchmark_runs")
        assert "ldr_version" in cols
        assert "settings_snapshot" in cols

    def test_missing_table_does_not_crash(self, tmp_path):
        """If benchmark_runs doesn't exist, migration is a clean no-op."""
        db_path = tmp_path / "no_benchmark_table.db"
        engine = create_engine(f"sqlite:///{db_path}")
        stamp_database(engine, "0013")
        # Should not raise.
        _run_upgrade_to(engine, "0014")
        engine.dispose()


class TestMigration0014Downgrade:
    def test_downgrade_removes_columns(self, legacy_engine):
        engine = legacy_engine
        _run_upgrade_to(engine, "0014")
        assert "ldr_version" in _columns(engine, "benchmark_runs")
        _run_downgrade_to(engine, "0013")
        cols = _columns(engine, "benchmark_runs")
        assert "ldr_version" not in cols
        assert "settings_snapshot" not in cols
