"""Tests for Alembic migrations.

Verifies that migrations can upgrade to head and downgrade to base
without errors, and that the schema after upgrade matches the
SQLAlchemy models.

These tests require a PostgreSQL instance and the TEST_DATABASE_URL
environment variable to be set. They are skipped otherwise.
"""

import os
import subprocess
import sys
from pathlib import Path

import pytest
from sqlalchemy import create_engine, inspect, text

ALEMBIC_INI = Path(__file__).resolve().parent.parent / "infrastructure" / "alembic.ini"
PROJECT_DIR = ALEMBIC_INI.parent.parent  # project root


def get_test_db_url() -> str | None:
    """Get the test database URL from environment.

    Returns None if TEST_DATABASE_URL is not set. This ensures the test
    only runs against an explicitly configured test database, preventing
    accidental use of the production database.
    """
    return os.environ.get("TEST_DATABASE_URL")


# ---------------------------------------------------------------------------
# Fixtures
# ---------------------------------------------------------------------------


@pytest.fixture(scope="module")
def test_db_url() -> str:
    """Return the test database URL, skipping if not configured."""
    url = get_test_db_url()
    if url is None:
        pytest.skip("TEST_DATABASE_URL not set — requires a PostgreSQL database")
    return url


@pytest.fixture(scope="module")
def engine(test_db_url: str):
    """SQLAlchemy engine connected to the test database.

    Note: DDL-heavy fixtures (``clean_database``) use ``AUTOCOMMIT``
    isolation on individual connections to work around PostgreSQL's
    requirement that DROP/CREATE SCHEMA run outside a transaction.
    """
    engine = create_engine(test_db_url)
    yield engine
    engine.dispose()


@pytest.fixture(scope="function")
def clean_database(engine):
    """Drop and recreate the public schema for a fully clean state."""
    with engine.connect() as conn:
        with conn.execution_options(isolation_level="AUTOCOMMIT"):
            conn.execute(text("DROP SCHEMA IF EXISTS public CASCADE"))
            conn.execute(text("CREATE SCHEMA public"))
    yield


@pytest.fixture(scope="function")
def alembic_env(test_db_url: str) -> dict[str, str]:
    """Environment variables for Alembic subprocess.

    Overrides ``DATABASE_URL`` so that Alembic connects to the test
    database rather than the one from ``.env`` or the conftest default.
    """
    env = os.environ.copy()
    env["DATABASE_URL"] = test_db_url
    env["ALEMBIC_CONFIG"] = str(ALEMBIC_INI)
    return env


# ---------------------------------------------------------------------------
# Helpers
# ---------------------------------------------------------------------------


def _run_alembic(
    command: list[str],
    alembic_env: dict[str, str],
) -> subprocess.CompletedProcess:
    """Run an Alembic command and return the result.

    Painless helper that embeds the config-file and working-directory
    flags so every call site doesn't need to repeat them.
    """
    return subprocess.run(
        [
            sys.executable,
            "-m",
            "alembic",
            "-c",
            str(ALEMBIC_INI),
            *command,
        ],
        cwd=PROJECT_DIR,
        env=alembic_env,
        capture_output=True,
        text=True,
    )


# ---------------------------------------------------------------------------
# Tests
# ---------------------------------------------------------------------------


@pytest.mark.integration
class TestAlembicMigrations:
    """Test that Alembic migrations can upgrade and downgrade cleanly."""

    def test_upgrade_head(self, engine, clean_database, alembic_env):
        """Applying all migrations should succeed."""
        result = _run_alembic(["upgrade", "head"], alembic_env)
        assert result.returncode == 0, (
            f"alembic upgrade head failed (exit {result.returncode})\n"
            f"--- stdout ---\n{result.stdout}\n"
            f"--- stderr ---\n{result.stderr}"
        )

    def test_downgrade_base(self, engine, clean_database, alembic_env):
        """After upgrading, roll back as far as possible.

        Note: The ``20260106_0001_tab_api_migration`` raises ``NotImplementedError``
        in its downgrade because TAB API schema restructuring is not reversable.
        This test verifies that all reversable migrations downgrade cleanly.
        """
        # Upgrade to head first
        result_up = _run_alembic(["upgrade", "head"], alembic_env)
        assert result_up.returncode == 0, (
            f"alembic upgrade head failed (exit {result_up.returncode})\n"
            f"--- stdout ---\n{result_up.stdout}\n"
            f"--- stderr ---\n{result_up.stderr}"
        )

        # Attempt full downgrade to base.
        # The TAB API migration deliberately refuses to downgrade — that's
        # expected, not a regression. We verify the known error is raised.
        result_down = _run_alembic(["downgrade", "base"], alembic_env)

        assert result_down.returncode != 0, (
            "Downgrade unexpectedly succeeded — expected NotImplementedError "
            "from the TAB API migration\n"
            f"--- stdout ---\n{result_down.stdout}"
        )
        assert (
            "NotImplementedError" in result_down.stderr
            and "Downgrade from TAB API schema" in result_down.stderr
        ), (
            f"Unexpected downgrade failure (exit {result_down.returncode})\n"
            f"--- stdout ---\n{result_down.stdout}\n"
            f"--- stderr ---\n{result_down.stderr}"
        )

    def test_schema_parity_after_upgrade(self, engine, clean_database, alembic_env):
        """Every SQLAlchemy model table should exist after a full upgrade."""
        # Upgrade via Alembic
        result = _run_alembic(["upgrade", "head"], alembic_env)
        assert result.returncode == 0, (
            f"alembic upgrade head failed (exit {result.returncode})\n"
            f"--- stdout ---\n{result.stdout}\n"
            f"--- stderr ---\n{result.stderr}"
        )

        # Import models *after* the upgrade so that conftest's sqlite
        # override of DATABASE_URL doesn't interfere with settings
        # resolution inside the subprocess.  Base.metadata is static
        # (it only reflects which models are defined), so the import
        # order relative to the database connection is irrelevant.
        from packages.core.storage.models import Base  # noqa: E402

        inspector = inspect(engine)
        db_tables = set(inspector.get_table_names())

        # Alembic creates its own bookkeeping table — ignore it
        db_tables.discard("alembic_version")

        # Models that exist in SQLAlchemy metadata but lack a migration yet.
        # These should be removed once a migration is created for them.
        _MODELS_WITHOUT_MIGRATIONS = {
            "prediction_history",  # PredictionHistory model — not yet migrated
        }

        model_tables = set(Base.metadata.tables.keys()) - _MODELS_WITHOUT_MIGRATIONS

        missing = model_tables - db_tables
        assert not missing, (
            "Tables defined in SQLAlchemy models but missing from "
            f"the database after a full migration: {sorted(missing)}"
        )

        extra = db_tables - model_tables
        if extra:
            pytest.skip(
                "Database contains extra tables not defined in "
                f"SQLAlchemy models: {sorted(extra)}"
            )