"""Real-Postgres integration tests for DB maintenance + health.

These run only in the CI integration lane (a real Postgres service + schema
built by `alembic upgrade head`). They exercise the actual async SQLAlchemy
connection path that unit mocks cannot: the un-awaited
`AsyncConnection.execution_options` regression (which made every VACUUM raise
AttributeError, reporting 0/6) passes the unit suite but fails here.
"""
import pytest
import pytest_asyncio

from scribe.models import engine
from scribe.services.db_maintenance import (
    MAINTENANCE_TABLES,
    get_table_health,
    run_maintenance,
)

pytestmark = pytest.mark.integration


@pytest_asyncio.fixture(autouse=True)
async def _dispose_engine():
    """Dispose the app's module-level engine after each test.

    The engine pools asyncpg connections per event loop, but pytest-asyncio runs
    each test on a fresh loop — so without this, test 2 gets handed test 1's
    connection bound to a now-dead loop ("Future attached to a different loop").
    Disposing in the test's own loop teardown clears the pool cleanly.
    """
    yield
    await engine.dispose()


@pytest.mark.asyncio
async def test_run_maintenance_vacuums_real_tables():
    summary = await run_maintenance()
    assert summary["tables"], "no tables were vacuumed"
    # Every allowlisted table exists after `alembic upgrade head`, so every
    # VACUUM (ANALYZE) must succeed. With the un-awaited execution_options bug
    # they would ALL fail with AttributeError — this is the guard.
    failed = [t for t in summary["tables"] if not t["ok"]]
    assert not failed, f"VACUUM failed for: {failed}"
    assert {t["table"] for t in summary["tables"]} <= set(MAINTENANCE_TABLES)


@pytest.mark.asyncio
async def test_table_health_reports_real_stats():
    health = await get_table_health()
    assert health["db_bytes"] > 0
    names = {t["table"] for t in health["tables"]}
    # Core table built by migrations must show up in pg_stat_user_tables.
    assert "notes" in names
    for t in health["tables"]:
        assert t["dead_pct"] >= 0
        assert t["total_bytes"] >= 0