app/database/connection.py

"""
============================================
Database Connection Manager
- Async SQLAlchemy Engine with Connection Pooling
- Handles Render's free tier quirks:
  * Sleep mode reconnection
  * Connection drop recovery
  * Pool pre-ping for stale connections
============================================
"""

import logging
from sqlalchemy.ext.asyncio import (
    create_async_engine,
    AsyncSession,
    async_sessionmaker,
    AsyncEngine,
)
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.pool import AsyncAdaptedQueuePool
from sqlalchemy import text

from app.config import get_database_url, settings

logger = logging.getLogger(__name__)


# ============================================
# Base Model Class (all models inherit this)
# ============================================
class Base(DeclarativeBase):
    """Base class for all database models."""
    pass


# ============================================
# Engine Configuration
# ============================================
_engine: AsyncEngine | None = None
_session_factory: async_sessionmaker[AsyncSession] | None = None


def get_engine() -> AsyncEngine:
    """
    Create or return the async SQLAlchemy engine.
    
    Key settings for Render Free Tier:
    - pool_pre_ping=True: Tests connection before using it
      (prevents "connection closed" errors after DB sleep)
    - pool_size=5: Small pool (free tier has limited connections)
    - max_overflow=3: Allow 3 extra connections under load
    - pool_recycle=300: Recycle connections every 5 minutes
      (prevents stale connections from Render's idle timeout)
    - pool_timeout=30: Wait max 30s for a connection from pool
    """
    global _engine

    if _engine is None:
        database_url = get_database_url()

        logger.info("Creating database engine...")
        logger.info(f"Database host: {database_url.split('@')[1].split('/')[0] if '@' in database_url else 'unknown'}")

        _engine = create_async_engine(
            database_url,
            # --- Connection Pool Settings ---
            poolclass=AsyncAdaptedQueuePool,
            pool_pre_ping=True,        # CRITICAL: Check connection health
            pool_size=5,               # Base connections in pool
            max_overflow=3,            # Extra connections allowed
            pool_recycle=300,          # Recycle every 5 min (300 sec)
            pool_timeout=30,           # Wait 30s for available connection
            
            # --- Query Settings ---
            echo=settings.DEBUG,       # Log SQL queries in debug mode
            
            # --- Connection Arguments ---
            connect_args={
                "timeout": 30,                    # Connection timeout
                "command_timeout": 60,             # Query timeout
                "server_settings": {
                    "application_name": "novel_scraper",
                    "idle_in_transaction_session_timeout": "60000",  # 60s
                },
            },
        )
        logger.info("Database engine created successfully.")

    return _engine


def get_session_factory() -> async_sessionmaker[AsyncSession]:
    """
    Create or return the async session factory.
    """
    global _session_factory

    if _session_factory is None:
        engine = get_engine()
        _session_factory = async_sessionmaker(
            bind=engine,
            class_=AsyncSession,
            expire_on_commit=False,    # Don't expire objects after commit
            autocommit=False,
            autoflush=False,
        )
        logger.info("Session factory created.")

    return _session_factory


async def get_db_session() -> AsyncSession:
    """
    Dependency for FastAPI routes.
    Yields an async database session.
    
    Usage in FastAPI:
        @router.get("/something")
        async def my_route(db: AsyncSession = Depends(get_db_session)):
            ...
    """
    factory = get_session_factory()
    async with factory() as session:
        try:
            yield session
            await session.commit()
        except Exception as e:
            await session.rollback()
            logger.error(f"Database session error: {e}")
            raise
        finally:
            await session.close()


async def init_database():
    """
    Initialize database: Create all tables.
    Called on application startup.
    """
    engine = get_engine()

    try:
        # Test connection first
        async with engine.begin() as conn:
            await conn.execute(text("SELECT 1"))
        logger.info("✅ Database connection test successful!")

        # Create all tables
        async with engine.begin() as conn:
            await conn.run_sync(Base.metadata.create_all)
        logger.info("✅ Database tables created/verified!")

    except Exception as e:
        logger.error(f"❌ Database initialization failed: {e}")
        logger.error("Check your DATABASE_URL environment variable!")
        raise


async def close_database():
    """
    Close database connections.
    Called on application shutdown.
    """
    global _engine, _session_factory

    if _engine is not None:
        await _engine.dispose()
        _engine = None
        _session_factory = None
        logger.info("Database connections closed.")