from sqlalchemy import create_engine, event, text
from sqlalchemy.orm import sessionmaker, declarative_base
from sqlalchemy.pool import NullPool
from app.core.config import settings
from app.core.db_utils import resolve_database_url
import logging, re

logger = logging.getLogger("db.init")

def _mask(url: str) -> str:
    if not url:
        return url
    return re.sub(r":([^:@/]+)@", r":****@", url)

original_url = settings.database_url
resolved_url = resolve_database_url(original_url)
if resolved_url != original_url:
    logger.info("Database URL adjusted for environment (WSL gateway logic applied)")

logger.info(f"Initializing DB engine: original={_mask(original_url)} resolved={_mask(resolved_url)}")

# Use normal pooling with pre-ping to detect stale connections
engine = create_engine(
    resolved_url,
    pool_pre_ping=True,  # Test connections before using them
    pool_recycle=300,    # Recycle connections after 5 minutes
    echo=settings.debug
)

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

def get_db():
    """Dependency to get database session"""
    db = SessionLocal()
    try:
        yield db
    except Exception as e:
        # Rollback on any error to prevent stuck transactions
        db.rollback()
        logger.error(f"Database session error: {e}")
        raise
    finally:
        db.close()
