"""Database plumbing — one engine, one session factory, one declarative Base.

The kit's four `bw_*` tables join this same metadata via
`bw_store_sqlalchemy.managed(Base.metadata, ...)` (see accounts.py), so a single
Alembic history owns the whole schema. Schema creation is dialect-split per the
caddie runbook: SQLite (tests/local) may create_all; Postgres is Alembic-only,
so a missing migration fails loudly instead of being masked at startup.
"""

from collections.abc import Callable

from sqlalchemy import create_engine, event
from sqlalchemy.orm import DeclarativeBase, Session, sessionmaker

from app.config import get_settings


class Base(DeclarativeBase):
    pass


_engine = None
_session_factory: Callable[[], Session] | None = None


def get_engine():
    global _engine
    if _engine is None:
        settings = get_settings()
        _engine = create_engine(settings.database_url, pool_pre_ping=True)
        if settings.database_url.startswith("sqlite"):
            # SQLite ships with foreign keys OFF. Production is Postgres, which
            # enforces them — so without this a referential bug passes every
            # test and fails for a real user. It has happened: pins were
            # inserted before the thread they point at, which SQLite accepted
            # silently and Postgres refused, so the app's central feature was
            # broken in production while the suite stayed green.
            @event.listens_for(_engine, "connect")
            def _fk_on(dbapi_connection, _record):  # pragma: no cover - wiring
                cursor = dbapi_connection.cursor()
                cursor.execute("PRAGMA foreign_keys=ON")
                cursor.close()
    return _engine


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


def is_sqlite() -> bool:
    return get_engine().dialect.name == "sqlite"


def reset_for_tests() -> None:
    """Drop the cached engine/factory so a test fixture can point database_url at
    a fresh file. Dispose FIRST — a pooled SQLite connection otherwise keeps
    writing into the unlinked inode (caddie runbook §5)."""
    global _engine, _session_factory
    if _engine is not None:
        _engine.dispose()
    _engine = None
    _session_factory = None