"""
Database Configuration for BreakPilot Compliance Backend.

Replaces the monorepo's classroom_engine.database with a standalone module
that sets search_path=compliance,core,public for schema isolation.
"""

import os
from sqlalchemy import create_engine, event
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

# Database URL from environment
_raw_url = os.getenv(
    "DATABASE_URL",
    "postgresql://breakpilot:breakpilot123@localhost:5432/breakpilot",
)
# SQLAlchemy 2.0 requires "postgresql://" instead of "postgres://"
DATABASE_URL = (
    _raw_url.replace("postgres://", "postgresql://", 1)
    if _raw_url.startswith("postgres://")
    else _raw_url
)

# Schema search path for compliance service
SCHEMA_SEARCH_PATH = os.getenv("SCHEMA_SEARCH_PATH", "compliance,core,public")

# Engine configuration
engine = create_engine(
    DATABASE_URL,
    pool_pre_ping=True,
    pool_size=5,
    max_overflow=10,
    echo=os.getenv("SQL_ECHO", "false").lower() == "true",
)


@event.listens_for(engine, "connect")
def set_search_path(dbapi_connection, connection_record):
    """Set the PostgreSQL search_path on every new connection."""
    cursor = dbapi_connection.cursor()
    cursor.execute(f"SET search_path TO {SCHEMA_SEARCH_PATH}")
    cursor.close()
    dbapi_connection.commit()


# Declarative Base
Base = declarative_base()

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


def get_db():
    """Database dependency for FastAPI endpoints."""
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()


def init_db():
    """Create all tables (for development)."""
    Base.metadata.create_all(bind=engine)