"""Pytest fixtures for the DB-backed integration lane (R6 S3b).

The bulk of the suite is pure-logic and needs no database. This adds an OPT-IN `db` fixture
that hands a test a real session against a throwaway Postgres (the app's own engine, bound to
the test `DATABASE_URL`). When no database is reachable — the default `siftlode check` still
runs the pure-logic tests with no Postgres — any test that asks for `db` is SKIPPED, so the
pure-logic lane is unaffected.

Schema comes from `alembic upgrade head` — not `create_all` — because the real schema depends on
things only migrations set up (the `unaccent` extension + the `public.unaccent_simple` text-search
config the videos.search_vector generated column needs). Building it via migrations also validates
that the migration chain applies cleanly. Each test then gets a clean database via TRUNCATE so
tests that COMMIT (the whole point of this lane — transaction-ownership behaviour) don't leak.
"""
import pytest
from alembic import command
from alembic.config import Config
from sqlalchemy import create_engine, text

import app.models  # noqa: F401 — register every model on Base.metadata
from app.config import settings
from app.db import Base, SessionLocal, engine


def _db_reachable() -> bool:
    # A throwaway engine with a bounded connect_timeout so a slow/firewalled DATABASE_URL can't
    # hang the whole suite at import (the app engine has no connect timeout). The compose lane
    # gates on `service_healthy`, but this keeps a manual run honest too.
    probe = create_engine(settings.database_url, connect_args={"connect_timeout": 3})
    try:
        with probe.connect() as conn:
            conn.execute(text("SELECT 1"))
        return True
    except Exception:
        return False
    finally:
        probe.dispose()


_DB_REACHABLE = _db_reachable()


@pytest.fixture(scope="session")
def _schema():
    if not _DB_REACHABLE:
        pytest.skip("no test database (set DATABASE_URL to a reachable Postgres)")
    # Build the real prod schema via migrations (alembic env.py reads settings.database_url, i.e.
    # the test DATABASE_URL). Idempotent — a re-run against an already-migrated DB is a no-op.
    command.upgrade(Config("alembic.ini"), "head")
    yield


def _truncate_all():
    # RESTART IDENTITY so serial PKs are deterministic per test; CASCADE handles FKs. Truncating
    # BEFORE each test (not after) means every test — including the first, which would otherwise see
    # migration-seeded rows — starts from the same empty slate and seeds only what it needs.
    tables = ", ".join(t.name for t in Base.metadata.sorted_tables)
    with engine.begin() as conn:
        conn.execute(text(f"TRUNCATE {tables} RESTART IDENTITY CASCADE"))


@pytest.fixture
def db(_schema):
    """A real session against the test Postgres, on a clean (empty) database. The code under test
    may commit freely."""
    _truncate_all()
    session = SessionLocal()
    try:
        yield session
    finally:
        session.rollback()
        session.close()