"""Tests for Alembic migrations.

NOTE: On SQLite, Alembic cannot run `ALTER TABLE ADD CONSTRAINT` operations
(which our migration uses for circular FK constraints like projects→prd_documents).
On PostgreSQL, these work fine. The tests use run_migrations() from database.py
which handles this: SQLite → create_all directly, PostgreSQL → Alembic upgrade.
"""
import pytest
from sqlalchemy import inspect as sa_inspect

from app.core.database import DATABASE_URL, Base, engine, run_migrations


def test_alembic_upgrade_head():
    """Test that migrations apply successfully via run_migrations()."""
    run_migrations()

    # Verify tables were created
    tables = sa_inspect(engine).get_table_names()
    assert "users" in tables
    assert "projects" in tables
    assert "prd_documents" in tables
    assert "architecture_sessions" in tables
    assert "checkpoints" in tables
    assert "feedback_entries" in tables


def test_alembic_metadata_is_consistent():
    """Test that model metadata matches database schema (no drift)."""
    run_migrations()

    insp = sa_inspect(engine)
    model_tables = Base.metadata.tables.keys()

    for table_name in model_tables:
        if table_name.startswith("_") or table_name == "alembic_version":
            continue
        assert table_name in insp.get_table_names(), (
            f"Table '{table_name}' declared in models but not found in database"
        )


def test_alembic_model_columns_exist():
    """Test that every model column exists in the database."""
    run_migrations()

    insp = sa_inspect(engine)
    for table_name, table in Base.metadata.tables.items():
        if table_name == "alembic_version":
            continue
        db_columns = {c["name"] for c in insp.get_columns(table_name)}
        for col in table.columns:
            assert col.name in db_columns, (
                f"Column '{table_name}.{col.name}' in model but not in DB"
            )