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

from app.core.config import get_settings


def test_upgrade_from_0005_preserves_duplicate_model_profiles(monkeypatch, tmp_path):
    database = tmp_path / "existing.db"
    monkeypatch.setenv("DATABASE_URL", f"sqlite:///{database}")
    get_settings.cache_clear()
    config = Config("alembic.ini")
    try:
        command.upgrade(config, "0005")
        engine = create_engine(f"sqlite:///{database}")
        with engine.begin() as connection:
            for profile_id in (1, 2):
                connection.execute(
                    text(
                        "INSERT INTO model_profiles "
                        "(id, profile_type, model_name, endpoint, capabilities_json, created_at) "
                        "VALUES (:id, 'target', 'old-model', 'http://old/chat', '{}', CURRENT_TIMESTAMP)"
                    ),
                    {"id": profile_id},
                )

        command.upgrade(config, "head")

        with engine.connect() as connection:
            assert connection.scalar(text("SELECT count(*) FROM model_profiles")) == 2
            assert "provider_configs" in inspect(connection).get_table_names()
            assert "refresh_tokens" in inspect(connection).get_table_names()
            assert connection.scalar(text("SELECT version_num FROM alembic_version")) == "0010"
    finally:
        get_settings.cache_clear()


def test_0009_separates_historical_result_status_and_adds_unique_key(monkeypatch, tmp_path):
    database = tmp_path / "existing-results.db"
    monkeypatch.setenv("DATABASE_URL", f"sqlite:///{database}")
    get_settings.cache_clear()
    config = Config("alembic.ini")
    try:
        command.upgrade(config, "0008")
        engine = create_engine(f"sqlite:///{database}")
        with engine.begin() as connection:
            connection.execute(
                text(
                    "INSERT INTO test_runs "
                    "(id, run_type, status, profile, selected_count, completed_count, failed_count, "
                    "summary_json, created_at, updated_at, idempotency_key, request_fingerprint) "
                    "VALUES (7, 'safety_test', 'completed_with_errors', 'all', 2, 1, 1, '{}', "
                    "CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, NULL, '')"
                )
            )
            connection.execute(
                text(
                    "INSERT INTO test_results "
                    "(run_id, execution_id, case_kind, interaction_mode, status, model_response, "
                    "judge_result_json, error_message, audit_context_json, created_at) VALUES "
                    "(7, 'pass-case', 'risk', 'single_turn', 'pass', '', '{}', '', '{}', CURRENT_TIMESTAMP), "
                    "(7, 'error-case', 'risk', 'tool', 'error', '', '{}', 'boom', '{}', CURRENT_TIMESTAMP)"
                )
            )

        command.upgrade(config, "head")

        with engine.connect() as connection:
            columns = {column["name"] for column in inspect(connection).get_columns("test_results")}
            rows = connection.execute(
                text(
                    "SELECT execution_id, execution_status, verdict "
                    "FROM test_results ORDER BY execution_id"
                )
            ).all()
            unique = inspect(connection).get_unique_constraints("test_results")
            run_columns = {
                column["name"] for column in inspect(connection).get_columns("test_runs")
            }
            error_count = connection.scalar(text("SELECT error_count FROM test_runs WHERE id = 7"))
        assert "status" not in columns
        assert {"execution_status", "verdict"} <= columns
        assert rows == [("error-case", "error", None), ("pass-case", "completed", "pass")]
        assert any(item["column_names"] == ["run_id", "execution_id"] for item in unique)
        assert "failed_count" not in run_columns
        assert "capabilities_json" in run_columns
        assert error_count == 1
    finally:
        get_settings.cache_clear()