"""Postgres access: a psycopg3 connection pool and raw SQL (no ORM queries)."""

from collections.abc import Iterator

from psycopg import Connection
from psycopg.rows import dict_row
from psycopg.types.json import Jsonb
from psycopg_pool import ConnectionPool

from .settings import get_settings

_pool: ConnectionPool | None = None


def pool() -> ConnectionPool:
    global _pool
    if _pool is None:
        _pool = ConnectionPool(
            get_settings().database_url,
            min_size=1,
            max_size=8,
            kwargs={"row_factory": dict_row},
            open=True,
        )
    return _pool


def close_pool() -> None:
    global _pool
    if _pool is not None:
        _pool.close()
        _pool = None


def get_conn() -> Iterator[Connection]:
    """FastAPI dependency: one transaction per request (commits on success)."""
    with pool().connection() as conn:
        yield conn


def fetchone(conn: Connection, sql: str, params: dict | tuple | None = None) -> dict | None:
    return conn.execute(sql, params).fetchone()


def fetchall(conn: Connection, sql: str, params: dict | tuple | None = None) -> list[dict]:
    return conn.execute(sql, params).fetchall()


__all__ = ["Jsonb", "close_pool", "fetchall", "fetchone", "get_conn", "pool"]