app/database/connection.py

from sqlalchemy import create_engine, Column, String, Integer, Text, DateTime, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship
from datetime import datetime
import uuid

from app.config.settings import settings

# Create SQLAlchemy engine
db_url = settings.DATABASE_URL

# Safety check for unencoded passwords (very common issue)
if "@" in db_url.split("://")[-1].split("@")[0] and not db_url.startswith("sqlite"):
    print("⚠️ DATABASE_URL detected with unencoded '@' in password.")
    # We don't fix it automatically to avoid messing up complex passwords, 
    # but we inform the user in the connection logic if it fails.

# Check if using SQLite (special handling for threads)
if db_url.startswith("sqlite"):
    engine = create_engine(
        db_url,
        connect_args={"check_same_thread": False}
    )
else:
    # Postgres/Other
    try:
        engine = create_engine(db_url)
    except Exception as e:
        print(f"❌ Failed to create engine for {db_url.split('@')[-1]}")
        raise e

# Session factory
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

# Base class for models
Base = declarative_base()


def get_db():
    """Dependency to get database session."""
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()


def init_db():
    """Initialize database tables."""
    Base.metadata.create_all(bind=engine)