import os
import sqlite3
import subprocess
import sys
from pathlib import Path


def test_alembic_upgrade_from_empty_database(tmp_path: Path) -> None:
    database_path = tmp_path / "audit-migrations.db"
    env = os.environ.copy()
    env["TUXDMX_DATABASE_URL"] = f"sqlite:///{database_path.as_posix()}"

    result = subprocess.run(
        [
            sys.executable,
            "-m",
            "alembic",
            "-c",
            "backend/alembic.ini",
            "upgrade",
            "head",
        ],
        cwd=Path(__file__).resolve().parents[2],
        env=env,
        capture_output=True,
        text=True,
        check=False,
    )

    assert result.returncode == 0, result.stderr
    with sqlite3.connect(database_path) as connection:
        tables = {
            row[0]
            for row in connection.execute(
                "SELECT name FROM sqlite_master WHERE type='table'"
            ).fetchall()
        }
    assert "alembic_version" in tables
    assert "users" in tables
    assert "fixture_sources" in tables
    assert "fixture_definitions" in tables
    assert "fixture_instances" in tables
    assert "midi_bridges" in tables
    assert "midi_bridge_tokens" in tables
    assert "midi_mappings" in tables