from collections.abc import Iterator
from contextlib import contextmanager
from typing import Any

from sqlalchemy import create_engine, event, text
from sqlalchemy.engine import Engine
from sqlalchemy.orm import Session, sessionmaker

from app.config import get_settings

_engine: Engine | None = None
_session_factory: sessionmaker[Session] | None = None


def get_engine() -> Engine:
    global _engine
    if _engine is None:
        _engine = create_engine(get_settings().database_url, pool_pre_ping=True)
        if _engine.dialect.name == "sqlite":

            @event.listens_for(_engine, "connect")
            def _sqlite_pragmas(dbapi_connection: Any, _record: Any) -> None:
                cursor = dbapi_connection.cursor()
                cursor.execute("PRAGMA foreign_keys=ON")
                cursor.close()

    return _engine


def get_session_factory() -> sessionmaker[Session]:
    global _session_factory
    if _session_factory is None:
        _session_factory = sessionmaker(bind=get_engine(), expire_on_commit=False)
    return _session_factory


def get_db() -> Iterator[Session]:
    session = get_session_factory()()
    try:
        yield session
        session.commit()
    except Exception:
        session.rollback()
        raise
    finally:
        session.close()


def set_space_context(session: Session, space_id: Any) -> None:
    """Transaction-local RLS context. No-op on SQLite (tests); fail-closed on PostgreSQL."""
    if session.get_bind().dialect.name == "postgresql":
        session.execute(
            text("select set_config('app.space_id', :sid, true)"),
            {"sid": str(space_id)},
        )


@contextmanager
def session_scope(space_id: Any = None) -> Iterator[Session]:
    """Transactional scope for workers/endpoints that own the transaction lifecycle."""
    session = get_session_factory()()
    try:
        if space_id is not None:
            set_space_context(session, space_id)
        yield session
        session.commit()
    except Exception:
        session.rollback()
        raise
    finally:
        session.close()