core/database.py

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from .config import settings

# For Azure SQL, we use the DATABASE_URL from .env
# If not provided, fallback to a local sqlite for safety (optional)
SQLALCHEMY_DATABASE_URL = settings.DATABASE_URL or "sqlite:///./temp.db"

# Create engine with stability settings for Azure SQL
engine = create_engine(
    SQLALCHEMY_DATABASE_URL,
    pool_pre_ping=True,    # Check connection health before every query
    pool_recycle=1800,  # 30 minutes (best practice)
    pool_timeout=30,       # Wait up to 30 seconds for a connection
    pool_size=15,          # Maintain a slightly larger pool
    max_overflow=25,       # Allow more overflow if busy
    connect_args={
        "timeout": 30      # 30 second timeout for queries/connections
    }
)

SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

Base = declarative_base()

def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()

def init_db():
    import models.db_models  # Ensure models are loaded
    import time
    from sqlalchemy.exc import DBAPIError

    max_retries = 5
    retry_delay = 5  # seconds

    for attempt in range(max_retries):
        try:
            print(f"Connecting to database (Attempt {attempt + 1}/{max_retries})...")
            Base.metadata.create_all(bind=engine)
            print("Database initialized successfully!")
            break
        except DBAPIError as e:
            if attempt < max_retries - 1:
                print(f"Database is waking up or unavailable. Retrying in {retry_delay}s...")
                time.sleep(retry_delay)
            else:
                print("Failed to connect to database after multiple attempts.")
                raise e