app/core/database.py

import logging
import os

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

load_dotenv()

logger = logging.getLogger(__name__)

DATABASE_URL = os.getenv("DATABASE_URL")

if not DATABASE_URL:
    # Fallback for local dev if not set
    logger.warning("DATABASE_URL not set, using SQLite fallback")
    DATABASE_URL = "sqlite:///./test.db"

# Configure engine with connection pooling and SSL for Neon
engine_args = {
    "pool_pre_ping": True,  # Verify connections before using
    "pool_recycle": 300,  # Recycle connections every 5 minutes
}

# Only add pooling for non-SQLite databases
if not DATABASE_URL.startswith("sqlite"):
    engine_args.update({
        "poolclass": QueuePool,
        "pool_size": 5,
        "max_overflow": 10,
    })
    # SSL is handled via the connection string for Neon (sslmode=require)
    logger.info("Using PostgreSQL with connection pooling")
else:
    # SQLite needs check_same_thread=False for FastAPI
    engine_args["connect_args"] = {"check_same_thread": False}
    logger.info("Using SQLite fallback database")

engine = create_engine(DATABASE_URL, **engine_args)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

Base = declarative_base()


def get_db():
    """
    Database session dependency for FastAPI.
    Yields a database session and ensures cleanup after request.
    """
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()


def check_db_connection() -> bool:
    """
    Check if database connection is working.
    Returns True if connection successful, False otherwise.
    """
    try:
        from sqlalchemy import text
        with engine.connect() as conn:
            conn.execute(text("SELECT 1"))
        return True
    except Exception as e:
        logger.error(f"Database connection check failed: {e}")
        return False