backend/database.py

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

# 1. Retrieve the Database connection string
DATABASE_URL = os.getenv("DATABASE_URL")

# Fallback to local SQLite if DATABASE_URL is not set or starts with sqlite
if not DATABASE_URL or DATABASE_URL.startswith("sqlite"):
    if not DATABASE_URL:
        DATABASE_URL = "sqlite:///./local_docuflow.db"
    # sqlite requires connect_args for multithreading
    connect_args = {"check_same_thread": False}
    engine_args = {}
else:
    # Normalize legacy postgres:// URIs to postgresql:// for SQLAlchemy compatibility
    if DATABASE_URL.startswith("postgres://"):
        DATABASE_URL = DATABASE_URL.replace("postgres://", "postgresql://", 1)
    
    # Enforce SSL/TLS for PostgreSQL connection (sslmode=require)
    connect_args = {"sslmode": "require"}
    
    # Set connection pooling options for database robustness
    engine_args = {
        "pool_size": 10,
        "max_overflow": 20,
        "pool_pre_ping": True,
        "pool_recycle": 1800,
    }

# 2. Initialize Engine & Session
engine = create_engine(DATABASE_URL, connect_args=connect_args, **engine_args)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

# 3. Base class for SQLAlchemy Models
Base = declarative_base()

# 4. Dependency to get DB session in FastAPI routes
def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()