app/database/database.py

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
import os
from dotenv import load_dotenv
import logging
import urllib.parse

# Configure logging
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

# Load environment variables
load_dotenv()

# Get database credentials from environment variables
DB_USER = os.getenv("DB_USER", "dev_cbs_admin")
DB_PASSWORD = os.getenv("DB_PASSWORD", "password")
DB_HOST = os.getenv("DB_HOST", "13.126.242.31")
DB_PORT = os.getenv("DB_PORT", "5432")
DB_NAME = os.getenv("DB_NAME", "vst")

# URL encode the password to handle special characters like @
encoded_password = urllib.parse.quote_plus(DB_PASSWORD)

# Log the connection parameters (without password)
logger.info(f"Attempting to connect to PostgreSQL database at {DB_HOST}:{DB_PORT}/{DB_NAME} as {DB_USER}")

# Create database URL for PostgreSQL with encoded password
SQLALCHEMY_DATABASE_URL = f"postgresql://{DB_USER}:{encoded_password}@{DB_HOST}:{DB_PORT}/{DB_NAME}"
# Log the URL with password masked
masked_url = SQLALCHEMY_DATABASE_URL.replace(encoded_password, "****")
logger.info(f"Connection URL: {masked_url}")

try:
    # Create engine
    engine = create_engine(SQLALCHEMY_DATABASE_URL)
    
    # Test connection
    with engine.connect() as conn:
        logger.info("Database connection successful")
except Exception as e:
    logger.error(f"Database connection error: {e}")
    # Create a SQLite engine as fallback for development/testing
    logger.info("Using SQLite as fallback database")
    SQLALCHEMY_DATABASE_URL = "sqlite:///./visitor_management.db"
    engine = create_engine(
        SQLALCHEMY_DATABASE_URL, connect_args={"check_same_thread": False}
    )

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

# Create base class
Base = declarative_base()

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