app/database.py

from sqlalchemy.ext.asyncio import AsyncSession, create_async_engine, async_sessionmaker
from sqlalchemy.orm import DeclarativeBase
from app.config import get_settings

settings = get_settings()

# Normalizar la URL de base de datos para asegurar el uso del driver asincrónico asyncpg
db_url = settings.database_url or "sqlite+aiosqlite:///./crowdata.db"
if db_url:

    if db_url.startswith("postgresql://"):
        db_url = db_url.replace("postgresql://", "postgresql+asyncpg://", 1)
    elif db_url.startswith("postgres://"):
        db_url = db_url.replace("postgres://", "postgresql+asyncpg://", 1)
    
    # asyncpg no soporta 'sslmode=require', requiere 'ssl=require'
    if "sslmode=" in db_url:
        db_url = db_url.replace("sslmode=require", "ssl=require")
        db_url = db_url.replace("sslmode=disable", "ssl=disable")

engine_kwargs = {
    "echo": settings.debug,
    "pool_pre_ping": True,
}
if db_url.startswith("postgresql"):
    engine_kwargs["pool_size"] = settings.db_pool_size
    engine_kwargs["max_overflow"] = settings.db_max_overflow

engine = create_async_engine(
    db_url,
    **engine_kwargs
)

AsyncSessionLocal = async_sessionmaker(
    engine,
    class_=AsyncSession,
    expire_on_commit=False,
)


class Base(DeclarativeBase):
    pass


async def get_db():
    async with AsyncSessionLocal() as session:
        try:
            yield session
            await session.commit()
        except Exception:
            await session.rollback()
            raise
        finally:
            await session.close()


async def init_db():
    from app.auth.models import User  # noqa: F401 - ensure models are loaded
    from app.reports.models import ReportCache  # noqa: F401
    
    # Use Alembic for migrations instead of create_all
    from alembic.config import Config
    from alembic import command
    import os
    
    # Find alembic.ini (could be in backend dir)
    alembic_ini = os.path.join(os.path.dirname(__file__), '..', 'alembic.ini')
    
    try:
        alembic_cfg = Config(alembic_ini)
        # Use the existing database URL from settings
        from app.config import get_settings
        settings = get_settings()
        # Normalize for synchronous alembic
        db_url = settings.database_url
        if db_url.startswith("sqlite+aiosqlite://"):
            db_url = db_url.replace("sqlite+aiosqlite://", "sqlite://")
        elif db_url.startswith("postgresql+asyncpg://"):
            db_url = db_url.replace("postgresql+asyncpg://", "postgresql://")
        alembic_cfg.set_main_option("sqlalchemy.url", db_url)
        command.upgrade(alembic_cfg, "head")
    except Exception as e:
        import logging
        logging.getLogger("app.database").warning(f"Error running migrations: {e}")
        # Fallback to create_all for dev environments
        async with engine.begin() as conn:
            await conn.run_sync(Base.metadata.create_all)