app/core/database.py

from sqlmodel import SQLModel, create_engine, Session
from typing import Generator
from .config import settings

# Supabase uses PostgreSQL under the hood.
# We disable the connection pool check for better compatibility with Supabase's
# pooler (PgBouncer) by adding the ?prepared_statements=false arg.
connect_args = {}

engine = create_engine(
    settings.DATABASE_URL,
    echo=settings.DEBUG,
    connect_args=connect_args,
    pool_size=20,          # Mantém 20 conexões fixas abertas prontas para uso
    max_overflow=10,       # Permite abrir +10 conexões extras em picos de tráfego
    pool_pre_ping=True,    # Testa se a conexão caiu antes de usá-la (evita erros)
    pool_recycle=1800      # Recicla as conexões a cada 30 minutos
)


def create_db_and_tables() -> None:
    """Create all SQLModel tables. Called on app startup."""
    SQLModel.metadata.create_all(engine)


def get_session() -> Generator[Session, None, None]:
    """FastAPI dependency that yields a DB session per request."""
    with Session(engine) as session:
        yield session