app/database.py

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

# Fallback to local SQLite if SUPABASE_DB_URL is not set
SUPABASE_DB_URL = os.getenv("SUPABASE_DB_URL", "sqlite:///./ba7ath_enriched.db")

if SUPABASE_DB_URL:
    SUPABASE_DB_URL = SUPABASE_DB_URL.strip().strip("'").strip('"')
    if SUPABASE_DB_URL.startswith("SUPABASE_DB_URL="):
        SUPABASE_DB_URL = SUPABASE_DB_URL.replace("SUPABASE_DB_URL=", "", 1)
    if SUPABASE_DB_URL.startswith("postgres://"):
        SUPABASE_DB_URL = SUPABASE_DB_URL.replace("postgres://", "postgresql://", 1)

# For SQLite with FastAPI, check_same_thread is required, but it breaks Postgres
connect_args = {}
if SUPABASE_DB_URL.startswith("sqlite"):
    connect_args["check_same_thread"] = False

engine = create_engine(
    SUPABASE_DB_URL,
    connect_args=connect_args
)

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

Base = declarative_base()


def get_db():
    """Dependency that provides a database session per request."""
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()