backend/app/database.py

import os
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, declarative_base
from dotenv import load_dotenv

load_dotenv()

# Load from environment variable – set this in your .env file
# Handle empty string or missing variable gracefully
DATABASE_URL = os.getenv("DATABASE_URL") or "postgresql://user:password@localhost/facerecog"

# Neon postgres URLs use "postgres://" but SQLAlchemy needs "postgresql://"
if DATABASE_URL.startswith("postgres://"):
    DATABASE_URL = DATABASE_URL.replace("postgres://", "postgresql://", 1)

# Ensure we don't pass an empty string to create_engine
if not DATABASE_URL or DATABASE_URL.strip() == "":
    DATABASE_URL = "postgresql://user:password@localhost/facerecog"

engine = create_engine(
    DATABASE_URL,
    pool_pre_ping=True,  # reconnect if connection dropped (important for Neon)
    pool_size=5,
    max_overflow=10,
)

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


def get_db():
    """FastAPI dependency: yields a DB session and closes it after use."""
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()