backend/database.py

# File: backend/database.py
import os
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
from dotenv import load_dotenv, find_dotenv

# Load environment variables from a .env file if it exists
BASE_DIR = os.path.dirname(os.path.abspath(__file__))
ROOT_DIR = os.path.dirname(BASE_DIR)

load_dotenv(os.path.join(BASE_DIR, '.env'))  # Looks in backend/.env
load_dotenv(os.path.join(ROOT_DIR, '.env'))  # Looks in DocuSort/.env

# 1. Check for a cloud-provided database URL first.
# 2. Fall back to your local PostgreSQL setup if DATABASE_URL isn't set.
SQLALCHEMY_DATABASE_URL = os.environ.get(
    "DATABASE_URL", 
    "postgresql://postgres:admin123@localhost/docusort_db"
)

# Fix for cloud hosting environments (like Render/Neon) that might pass "postgres://"
if SQLALCHEMY_DATABASE_URL.startswith("postgres://"):
    SQLALCHEMY_DATABASE_URL = SQLALCHEMY_DATABASE_URL.replace("postgres://", "postgresql://", 1)

# Create the engine that communicates with the database
engine = create_engine(SQLALCHEMY_DATABASE_URL, pool_pre_ping=True)

# Create a session factory
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

# Base class for our database models
Base = declarative_base()

# Dependency to get the DB session in other files
def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()