"""Database access: SQLAlchemy Core (async, asyncpg) with plain SQL. One engine per process."""
from __future__ import annotations

import json
from collections.abc import AsyncIterator, Mapping, Sequence
from contextlib import asynccontextmanager
from typing import Any

from sqlalchemy import text
from sqlalchemy.ext.asyncio import AsyncConnection, AsyncEngine, create_async_engine

from aiatlas.config import settings

_engine: AsyncEngine | None = None


def engine() -> AsyncEngine:
    global _engine
    if _engine is None:
        _engine = create_async_engine(settings.database_url, pool_size=8, max_overflow=8, pool_pre_ping=True, pool_recycle=1800,
                                      connect_args={"server_settings": {"application_name": "aiatlas", "jit": "off"}})
    return _engine


async def dispose() -> None:
    global _engine
    if _engine is not None:
        await _engine.dispose()
        _engine = None


@asynccontextmanager
async def connection() -> AsyncIterator[AsyncConnection]:
    async with engine().connect() as conn:
        yield conn


@asynccontextmanager
async def transaction() -> AsyncIterator[AsyncConnection]:
    async with engine().begin() as conn:
        yield conn


def jsonb(value: Any) -> str:
    """Serialise a Python value for a `cast(:x as jsonb)` parameter."""
    return json.dumps(value, default=str, ensure_ascii=False)


async def execute(conn: AsyncConnection, sql: str, /, **params: Any) -> None:
    await conn.execute(text(sql), params)


async def fetch_all(conn: AsyncConnection, sql: str, /, **params: Any) -> list[dict[str, Any]]:
    result = await conn.execute(text(sql), params)
    return [dict(r._mapping) for r in result]


async def fetch_one(conn: AsyncConnection, sql: str, /, **params: Any) -> dict[str, Any] | None:
    result = await conn.execute(text(sql), params)
    row = result.first()
    return dict(row._mapping) if row is not None else None


async def fetch_val(conn: AsyncConnection, sql: str, /, **params: Any) -> Any:
    result = await conn.execute(text(sql), params)
    row = result.first()
    return row[0] if row is not None else None


async def execute_many(conn: AsyncConnection, sql: str, rows: Sequence[Mapping[str, Any]]) -> None:
    if rows:
        await conn.execute(text(sql), list(rows))


__all__ = ["connection", "dispose", "engine", "execute", "execute_many", "fetch_all", "fetch_one", "fetch_val", "jsonb", "transaction"]