app/database.py

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from sqlalchemy.pool import StaticPool
import os
from dotenv import load_dotenv

# Cargar variables de entorno
load_dotenv()

# Configuración de base de datos: PostgreSQL en producción, SQLite fallback en desarrollo
DATABASE_URL = os.getenv("DATABASE_URL")

if DATABASE_URL:
    # PostgreSQL para producción
    IS_WORKER = os.getenv("IS_WORKER", "false").lower() == "true"
    p_size = 2 if IS_WORKER else 8
    p_overflow = 4 if IS_WORKER else 12
    
    engine = create_engine(
        DATABASE_URL,
        pool_pre_ping=True,
        pool_size=p_size,
        max_overflow=p_overflow
    )
else:
    # SQLite para desarrollo/testing local
    SQLITE_URL = "sqlite:///./musical_master.db"
    engine = create_engine(
        SQLITE_URL,
        connect_args={"check_same_thread": False},
        poolclass=StaticPool
    )

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

Base = declarative_base()

def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()