"""Exercise the complete Alembic downgrade/upgrade chain on a fresh schema."""

from pathlib import Path

from alembic import command
from alembic.config import Config
from sqlalchemy import create_engine, inspect
from sqlmodel import SQLModel


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

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

    foreign_keys = inspect(engine).get_foreign_keys("eval_runs")
    assert any(key["constrained_columns"] == ["campaign_id"] and key["name"] is None for key in foreign_keys)

    command.stamp(config, "head")
    command.downgrade(config, "base")
    command.upgrade(config, "head")

    inspector = inspect(engine)
    assert "campaigns" in inspector.get_table_names()
    assert {"campaign_id", "campaign_plan_index", "campaign_occurrence_index"} <= {
        column["name"] for column in inspector.get_columns("eval_runs")
    }