version: '3.8'
services:
  postgres-test:
    image: postgres:16-alpine
    environment:
      POSTGRES_USER: test_user
      POSTGRES_PASSWORD: test_password
      POSTGRES_DB: test_db
    ports:
      - "5433:5432"
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U test_user"]
      interval: 1s
      timeout: 5s
      retries: 10
    tmpfs:
      - /var/lib/postgresql/data

import pytest
import subprocess
import time
from sqlalchemy import create_engine, text
from sqlalchemy.pool import NullPool
from sqlalchemy.orm import sessionmaker, Session

# Import your models
from app.models import Base
from app.database import get_db


@pytest.fixture(scope="session")
def docker_compose():
    """Spin up Docker Compose once per test session."""
    subprocess.run(
        ["docker-compose", "-f", "docker-compose.test.yml", "up", "-d"],
        check=True,
        cwd=".",
    )

    # Wait for postgres to be ready
    max_retries = 30
    for i in range(max_retries):
        try:
            engine = create_engine(
                "postgresql://test_user:test_password@localhost:5433/test_db"
            )
            with engine.connect() as conn:
                conn.execute(text("SELECT 1"))
            break
        except Exception as e:
            if i == max_retries - 1:
                raise
            time.sleep(0.5)

    yield

    # Tear down
    subprocess.run(
        ["docker-compose", "-f", "docker-compose.test.yml", "down"],
        check=True,
        cwd=".",
    )


@pytest.fixture
def db_session(docker_compose):
    """Create a fresh database session for each test."""
    # Create engine with NullPool—don't reuse connections between tests
    engine = create_engine(
        "postgresql://test_user:test_password@localhost:5433/test_db",
        poolclass=NullPool,
    )

    # Run migrations (I use Alembic; you could also use Base.metadata.create_all)
    subprocess.run(
        ["alembic", "upgrade", "head"],
        check=True,
        cwd=".",
    )

    SessionLocal = sessionmaker(bind=engine, expire_on_commit=False)
    session = SessionLocal()

    yield session

    # Cleanup: rollback any uncommitted transactions
    session.rollback()
    session.close()

    # Drop all tables between tests for true isolation
    Base.metadata.drop_all(engine)


@pytest.fixture
def client(db_session):
    """Provide a FastAPI test client with dependency injection."""
    from fastapi.testclient import TestClient
    from app.main import app

    def override_get_db():
        yield db_session

    app.dependency_overrides[get_db] = override_get_db

    return TestClient(app)

from app.models import User
from datetime import datetime


def test_create_user_success(client, db_session):
    """Test successful user creation."""
    response = client.post(
        "/users",
        json={"email": "alice@example.com", "name": "Alice"},
    )

    assert response.status_code == 201
    assert response.json()["email"] == "alice@example.com"

    # Verify it's actually in the database
    user = db_session.query(User).filter_by(email="alice@example.com").first()
    assert user is not None
    assert user.name == "Alice"


def test_create_duplicate_email_fails(client, db_session):
    """Test that duplicate emails are rejected.

    This catches:
    - Whether your database constraint actually exists
    - Whether your error handling returns the right status code
    - Whether the transaction rolled back properly
    """
    # Insert first user
    client.post(
        "/users",
        json={"email": "bob@example.com", "name": "Bob"},
    )

    # Try to insert duplicate
    response = client.post(
        "/users",
        json={"email": "bob@example.com", "name": "Bob 2"},
    )

    assert response.status_code == 409  # Conflict
    assert "already exists" in response.json()["detail"].lower()

    # Verify only one user exists
    count = db_session.query(User).filter_by(email="bob@example.com").count()
    assert count == 1


def test_n_plus_one_query_detection(client, db_session):
    """Catch N+1 queries against real PostgreSQL."""
    from sqlalchemy import event

    # Track queries
    queries = []

    @event.listens_for(engine, "before_cursor_execute")
    def receive_before_cursor_execute(conn, cursor, statement, params, context, executemany):
        queries.append(statement)

    # Create users with posts
    for i in range(5):
        user = User(email=f"user{i}@example.com", name=f"User {i}")
        db_session.add(user)
    db_session.commit()

    # Fetch users and their posts efficiently
    users = db_session.query(User).options(joinedload(User.posts)).all()

    # Against a real database, you can verify query patterns
    # Mocks can't catch these subtle performance bugs
    assert len(queries) < 10  # Rough check; adjust for your schema

def test_concurrent_writes(db_session):
    """Test actual concurrent database writes."""
    from concurrent.futures import ThreadPoolExecutor
    from app.database import SessionLocal

    def create_user(email: str):
        session = SessionLocal()
        try:
            user = User(email=email, name=email.split("@")[0])
            session.add(user)
            session.commit()
            return user.id
        finally:
            session.close()

    # Run 10 writes concurrently
    with ThreadPoolExecutor(max_workers=10) as executor:
        futures = [
            executor.submit(create_user, f"user{i}@example.com")
            for i in range(10)
        ]
        results = [f.result() for f in futures]

    # Verify all succeeded
    assert len(set(results)) == 10  # All IDs unique