app/core/database.py

import os
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

# Database URL configuration
# For local development, default to SQLite fallback.
# For production/staging, it will read PostgreSQL from environment variables.
DATABASE_URL = os.getenv("DATABASE_URL", "sqlite:///./constructhire.db")

# Support SQLite configuration parameters
if DATABASE_URL.startswith("sqlite"):
    engine = create_engine(DATABASE_URL, connect_args={"check_same_thread": False})
else:
    # Use pool_pre_ping=True to prevent stale connection errors with Supabase/Render
    engine = create_engine(DATABASE_URL, pool_pre_ping=True)


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

def get_db():
    """
    Database session generator to be used as a FastAPI dependency.
    Yields a database session and ensures it is closed after the request is finished.
    """
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()