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

# PostgreSQL connection configuration
# Uses docker hostname 'music_db' when running in container, falls back to external IP for local dev
POSTGRES_HOST = os.getenv("POSTGRES_HOST", "music_db")
POSTGRES_PORT = os.getenv("POSTGRES_PORT", "5432")
POSTGRES_USER = os.getenv("POSTGRES_USER", "bnair")
POSTGRES_PASSWORD = os.getenv("POSTGRES_PASSWORD", "Bharath2002")
POSTGRES_DB = os.getenv("POSTGRES_DB", "music_db")

# Build the PostgreSQL URL
# Format: postgresql://user:password@host:port/database
SQLALCHEMY_DATABASE_URL = os.getenv(
    "DATABASE_URL",
    f"postgresql://{POSTGRES_USER}:{POSTGRES_PASSWORD}@{POSTGRES_HOST}:{POSTGRES_PORT}/{POSTGRES_DB}",
)

# PostgreSQL connection pool settings for production
engine = create_engine(
    SQLALCHEMY_DATABASE_URL,
    pool_size=5,  # Maintain 5 connections in the pool
    max_overflow=10,  # Allow up to 10 additional connections
    pool_pre_ping=True,  # Verify connection health before using
)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)

Base = declarative_base()


def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()