auth/database.py

import os
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, declarative_base
from sqlalchemy.exc import OperationalError

# ==========================================================
# DATABASE URL
# ==========================================================
DATABASE_URL = os.getenv("DATABASE_URL")

if not DATABASE_URL:
    raise RuntimeError(
        "DATABASE_URL environment variable is not set"
    )

# ==========================================================
# SUPABASE ENTERPRISE ENGINE CONFIG
# ==========================================================
# Optimized for:
# - Supabase Pooler
# - FastAPI async workload
# - Background workers
# - Long-running autonomous services

engine = create_engine(
    DATABASE_URL,

    # --- Pool Stability ---
    pool_pre_ping=True,      # validates dead connections
    pool_recycle=300,        # refresh connections
    pool_size=5,             # safe baseline
    max_overflow=10,         # burst capacity

    # --- Reliability ---
    echo=False,
    future=True,

    # --- Supabase Requirement ---
    connect_args={
        "sslmode": "require",
        "connect_timeout": 10,
    },
)

# ==========================================================
# SESSION FACTORY
# ==========================================================
SessionLocal = sessionmaker(
    autocommit=False,
    autoflush=False,
    bind=engine,
)

# ==========================================================
# BASE MODEL
# ==========================================================
Base = declarative_base()

# ==========================================================
# DEPENDENCY (FASTAPI)
# ==========================================================
def get_db():
    """
    FastAPI dependency injection session.
    Ensures connection cleanup even on crash.
    """
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()


# ==========================================================
# CONNECTION TEST (STARTUP SAFE)
# ==========================================================
def verify_database_connection():
    """
    Validates database connectivity during startup.
    Prevents silent runtime failures.
    """
    try:
        with engine.connect() as conn:
            conn.execute("SELECT 1")
    except OperationalError as e:
        raise RuntimeError(
            f"Database connection failed: {str(e)}"
        )