from sqlalchemy import create_engine, event
from sqlalchemy.orm import sessionmaker
import os
import re

DATABASE_URL = os.getenv("DATABASE_URL")
if not DATABASE_URL:
    raise ValueError("DATABASE_URL must be set in environment")

# Локально: public. Прод (data-bus): 3pl_finntrai — как DB_SCHEMA у Laravel.
_DB_SCHEMA_RAW = os.getenv("DB_SCHEMA", "public").strip() or "public"
if not re.fullmatch(r"[A-Za-z_][A-Za-z0-9_]*", _DB_SCHEMA_RAW):
    raise ValueError(f"Invalid DB_SCHEMA: {_DB_SCHEMA_RAW}")
DB_SCHEMA = _DB_SCHEMA_RAW

engine = create_engine(DATABASE_URL)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)


@event.listens_for(engine, "connect")
def _set_search_path(dbapi_connection, _connection_record) -> None:
    cursor = dbapi_connection.cursor()
    cursor.execute(f'SET search_path TO "{DB_SCHEMA}", public')
    cursor.close()


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