src/expon/shared/infrastructure/database.py

# src/expon/shared/infrastructure/database.py
import os
from urllib.parse import urlparse
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, declarative_base
from sqlalchemy.pool import QueuePool

# No tocamos tu .env; lo carga main.py. Aquí solo leemos.
DATABASE_URL = (os.getenv("DATABASE_URL") or "").strip()

# Forzamos driver psycopg2 si usas "postgresql://"
if DATABASE_URL.startswith("postgresql://"):
    DATABASE_URL = DATABASE_URL.replace("postgresql://", "postgresql+psycopg2://", 1)

# Si el host es Azure, fuerza SSL sin tocar tu .env
connect_args = {}
try:
    netloc = urlparse(DATABASE_URL).netloc
    if "azure.com" in netloc:
        connect_args["sslmode"] = "require"
except Exception:
    pass

engine = create_engine(
    DATABASE_URL,
    connect_args=connect_args,
    poolclass=QueuePool,
    pool_pre_ping=True,   # detecta conexiones rotas
    pool_recycle=1800,    # recicla a los 30 min
    pool_size=5,
    max_overflow=5,
)

SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
Base = declarative_base()