"""Exercise the scenario_version backfill migration against a real SQLite file (ticket 04)."""

from pathlib import Path

from alembic import command
from alembic.config import Config
from sqlalchemy import create_engine, inspect, text


def test_scenario_version_backfill_migration(tmp_path: Path, monkeypatch):
    from agenteval.storage import db as db_module

    database_url = f"sqlite:///{tmp_path / 'migration.db'}"
    monkeypatch.setattr(db_module, "DATABASE_URL", database_url)
    config = Config(str(Path(__file__).resolve().parents[2] / "alembic.ini"))

    legacy_engine = create_engine(database_url)
    with legacy_engine.begin() as connection:
        connection.execute(text("CREATE TABLE scenarios (id VARCHAR PRIMARY KEY, name VARCHAR NOT NULL)"))
        connection.execute(text("CREATE TABLE eval_results (id VARCHAR PRIMARY KEY, run_id VARCHAR NOT NULL)"))
        connection.execute(
            text(
                "CREATE TABLE eval_runs (id VARCHAR PRIMARY KEY, target_id VARCHAR NOT NULL, "
                "scenario_id VARCHAR NOT NULL)"
            )
        )
        connection.execute(text("CREATE TABLE turns (id VARCHAR PRIMARY KEY, run_id VARCHAR NOT NULL)"))
        connection.execute(text("INSERT INTO scenarios (id, name) VALUES ('s1', '场景一')"))
        connection.execute(
            text("INSERT INTO eval_runs (id, target_id, scenario_id) VALUES ('r1', 't1', 's1')")
        )
        connection.execute(
            text("INSERT INTO eval_runs (id, target_id, scenario_id) VALUES ('r2', 't1', 'ghost')")
        )

    # 先升到场景版本迁移，将 s1 手动升到 5，验证回填按场景当前版本 join
    command.upgrade(config, "c8e2f5a7b901")
    with create_engine(database_url).begin() as connection:
        connection.execute(text("UPDATE scenarios SET version = 5 WHERE id = 's1'"))

    command.upgrade(config, "head")

    engine = create_engine(database_url)
    inspector = inspect(engine)
    assert "scenario_version" in {c["name"] for c in inspector.get_columns("eval_runs")}

    with engine.connect() as connection:
        rows = dict(connection.execute(text("SELECT id, scenario_version FROM eval_runs")).fetchall())
    assert rows["r1"] == 5   # 回填为场景当前版本
    assert rows["r2"] == 1   # 孤儿运行（场景已删）回填 1