app\db\session.py

from typing import AsyncGenerator
from sqlalchemy.ext.asyncio import create_async_engine, async_sessionmaker, AsyncSession

from app.core.config import settings

# ------------------------------------------------------------------
# ENGINE CONFIGURATION (SQLite Optimized)
# ------------------------------------------------------------------
engine = create_async_engine(
    str(settings.SQLALCHEMY_DATABASE_URI),
    echo=settings.DB_ECHO,  # Set to True in .env for SQL debugging
    future=True,
    # 🔥 CRITICAL FOR SQLITE IN FASTAPI: Prevents thread-sharing errors
    connect_args={"check_same_thread": False}
)

# ------------------------------------------------------------------
# SESSION FACTORY
# ------------------------------------------------------------------
# This factory is used by background workers (tasks) to create 
# independent database sessions outside of the request context.
async_session_factory = async_sessionmaker(
    bind=engine,
    class_=AsyncSession,
    expire_on_commit=False,
    autocommit=False,
    autoflush=False,
)

# ------------------------------------------------------------------
# FASTAPI DEPENDENCY
# ------------------------------------------------------------------
async def get_db() -> AsyncGenerator[AsyncSession, None]:
    """
    Dependency for FastAPI routes. 
    Usage: db: AsyncSession = Depends(get_db)
    """
    async with async_session_factory() as session:
        try:
            yield session
            await session.commit()
        except Exception:
            await session.rollback()
            raise
        finally:
            await session.close()