src/models/database.py

"""
Database Connection and Session Management

This module provides:
- Async engine for Neon PostgreSQL
- Session factory for database operations
- Base class for SQLAlchemy models
- Dependency injection for FastAPI

Uses async SQLAlchemy with asyncpg driver for optimal performance.
"""

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

from src.config import get_settings


# Global variables (lazy initialization)
_engine = None
_AsyncSessionLocal = None


def _get_engine():
    """Get or create the database engine (lazy initialization)."""
    global _engine, _AsyncSessionLocal

    if _engine is None:
        settings = get_settings()

        # Create async engine
        # pool_pre_ping=True: Check connection health before use
        # pool_recycle=3600: Recycle connections after 1 hour (prevent stale connections)
        _engine = create_async_engine(
            settings.database_url,
            echo=settings.debug_mode,  # Log SQL in debug mode
            future=True,
            pool_pre_ping=True,
            pool_recycle=3600,
        )

        # Create async session factory
        _AsyncSessionLocal = async_sessionmaker(
            _engine,
            class_=AsyncSession,
            expire_on_commit=False,
            autocommit=False,
            autoflush=False,
        )

    return _engine


def _get_session_factory():
    """Get the async session factory."""
    global _AsyncSessionLocal

    if _AsyncSessionLocal is None:
        _get_engine()  # Ensure engine is initialized

    return _AsyncSessionLocal


# Base class for all models
class Base(DeclarativeBase):
    """
    Base class for SQLAlchemy models.

    All models should inherit from this class.
    Provides:
    - Automatic table name generation (lowercase class name)
    - Common columns via mixins (id, created_at, updated_at)
    - Declarative mapping
    """
    pass


async def get_db() -> AsyncGenerator[AsyncSession, None]:
    """
    Dependency injection for FastAPI routes.

    Provides a database session that is automatically closed after use.

    Yields:
        AsyncSession: Database session for async operations

    Example:
        @app.get("/users/{user_id}")
        async def get_user(user_id: str, db: AsyncSession = Depends(get_db)):
            result = await db.execute(select(User).where(User.id == user_id))
            return result.scalar_one_or_none()
    """
    session_factory = _get_session_factory()
    async with session_factory() as session:
        try:
            yield session
            await session.commit()
        except Exception:
            await session.rollback()
            raise


async def init_db() -> None:
    """
    Initialize database connection.

    Use this on application startup to verify database connectivity.
    This does NOT create tables - use Alembic migrations for that.

    Raises:
        Exception: If database connection fails
    """
    engine = _get_engine()
    async with engine.begin() as conn:
        # Test connection
        await conn.execute(text("SELECT 1"))


async def close_db() -> None:
    """
    Close database connections.

    Use this on application shutdown to gracefully close connections.
    """
    global _engine, _AsyncSessionLocal

    if _engine is not None:
        await _engine.dispose()
        _engine = None
        _AsyncSessionLocal = None


# ============================================================================
# SECURITY DOCUMENTATION
# ============================================================================

"""
NEON POSTGRESQL SECURITY RULES
================================

1. DATABASE URL SECURITY
   - DATABASE_URL contains sensitive credentials (username, password)
   - MUST be loaded from environment variables only
   - NEVER commit to version control
   - MUST use SSL (sslmode=require in connection string)

2. CONNECTION POOLING
   - SQLAlchemy manages connection pooling automatically
   - pool_pre_ping checks connection health before use
   - pool_recycle prevents stale connections
   - Configure pool size based on your load

3. SESSION MANAGEMENT
   - Use get_db() dependency injection for FastAPI routes
   - Sessions are automatically committed on success
   - Sessions are automatically rolled back on error
   - Sessions are automatically closed after use

4. SQL INJECTION PREVENTION
   - ALWAYS use SQLAlchemy ORM methods (never raw SQL)
   - NEVER concatenate strings into queries
   - ALWAYS use parameterized queries
   - SQLAlchemy automatically escapes parameters

5. USER DATA ISOLATION
   - Enforce user_id filtering at application layer
   - Never assume database-level security is enough
   - Always validate user owns the data they're accessing
   - Implement defense in depth

EXAMPLE USAGE PATTERNS
========================

✅ CORRECT: Using ORM with parameterized queries
    result = await db.execute(
        select(User).where(User.id == user_id)
    )
    user = result.scalar_one_or_none()

✅ CORRECT: Using dependency injection
    @app.get("/users/{user_id}")
    async def get_user(user_id: str, db: AsyncSession = Depends(get_db)):
        result = await db.execute(select(User).where(User.id == user_id))
        return result.scalar_one_or_none()

✅ CORRECT: Explicit user filtering for data isolation
    result = await db.execute(
        select(Todo).where(Todo.user_id == current_user.id)
    )

❌ WRONG: Raw SQL with string concatenation
    sql = f"SELECT * FROM users WHERE id = '{user_id}'"  # SQL injection risk!
    await db.execute(text(sql))

❌ WRONG: Forgetting to filter by user_id
    result = await db.execute(select(Todo))  # Returns ALL users' todos!

FOR MORE INFORMATION
- Neon Docs: https://neon.tech/docs
- SQLAlchemy Async: https://docs.sqlalchemy.org/en/20/orm/extensions/asyncio.html
- FastAPI Database: https://fastapi.tiangolo.com/tutorial/dependencies/
"""