from __future__ import annotations

from contextlib import asynccontextmanager

from psycopg_pool import AsyncConnectionPool

from app.config import get_settings

_pool: AsyncConnectionPool | None = None


def get_pool() -> AsyncConnectionPool:
    global _pool
    if _pool is None:
        settings = get_settings()
        _pool = AsyncConnectionPool(
            conninfo=settings.postgres_readonly_url,
            min_size=1,
            max_size=10,
            open=False,
            # --- ADDED TO FIX CROSS-CLOUD SSL DROPS ---
            max_idle=30.0,  # Recycle connections that sit silent for over 30s
            check=AsyncConnectionPool.check_connection,  # Test the connection health before handing it to your tool
            kwargs={
                "keepalives": 1,          # Turn on TCP Keepalives
                "keepalives_idle": 30,     # Ping Railway every 30 seconds of silence
                "keepalives_interval": 10, # If a ping fails, retry every 10 seconds
                "keepalives_count": 5,     # Drop the socket if 5 pings fail in a row
            },
        )
    return _pool


async def open_pool() -> None:
    """Call on app startup."""
    pool = get_pool()
    if pool.closed:
        await pool.open(wait=True)


async def close_pool() -> None:
    """Call on app shutdown."""
    global _pool
    if _pool is not None and not _pool.closed:
        await _pool.close()
    _pool = None


@asynccontextmanager
async def get_readonly_connection():
    """
    Yields a connection with an extra read-only guard set at the
    session level, on top of the chat_ro_user DB role.
    """
    pool = get_pool()
    async with pool.connection() as conn:
        await conn.execute("SET default_transaction_read_only = on")
        yield conn