from __future__ import annotations

from collections.abc import Callable
from typing import Any

from psycopg import connect
from psycopg.connection import Connection
from psycopg.cursor import Cursor
from psycopg.rows import dict_row

from app.core.settings import get_settings

from .helpers import BasePostgresHelpersMixin
from .pilot_queries import BasePostgresPilotMixin


import atexit
from psycopg_pool import ConnectionPool

_GLOBAL_POOL: ConnectionPool | None = None

class BasePostgresClient(
    BasePostgresHelpersMixin,
    BasePostgresPilotMixin,
):
    """Minimal PostgreSQL client using psycopg_pool and env-driven configuration."""

    def __init__(self, database_url: str | None = None) -> None:
        settings = get_settings()
        self._database_url = database_url or settings.database_url
        self._connect_timeout = settings.database_connect_timeout_seconds

    def is_enabled(self) -> bool:
        """Return True when a database URL is configured."""
        return bool(self._database_url)

    def ping(self) -> bool:
        """Check if database connection is alive by executing a lightweight query."""
        if not self.is_enabled():
            return False
        with self.get_connection() as connection:
            with connection.cursor() as cursor:
                cursor.execute("SELECT 1")
                cursor.fetchone()
        return True

    def _get_pool(self) -> ConnectionPool:
        global _GLOBAL_POOL
        if not self._database_url:
            raise ValueError("DATABASE_URL is not configured.")
            
        if _GLOBAL_POOL is None:
            _GLOBAL_POOL = ConnectionPool(
                conninfo=self._database_url,
                min_size=2,
                max_size=20,
                kwargs={
                    "connect_timeout": self._connect_timeout,
                    "row_factory": dict_row
                },
                open=True
            )
            atexit.register(_GLOBAL_POOL.close)
        return _GLOBAL_POOL

    def get_connection(self) -> Any:
        """Get a managed connection context from the global pool."""
        return self._get_pool().connection()

    def _open_connection(self) -> Any:
        """Backward compatibility for direct raw connection usage."""
        # Note: raw connect is expensive. Preferred is `with get_connection()`.
        return connect(
            self._database_url,
            connect_timeout=self._connect_timeout,
            row_factory=dict_row,
        )

    def run_in_transaction(self, operation: Callable[[Connection[Any]], Any]) -> Any:
        """Execute one callback inside a single transaction with explicit commit/rollback."""
        with self.get_connection() as connection:
            try:
                result = operation(connection)
                connection.commit()
                return result
            except Exception:
                connection.rollback()
                raise

    def _execute_with_optional_connection(
        self,
        connection: Connection[Any] | None,
        operation: Callable[[Cursor[Any]], Any],
    ) -> Any:
        """Execute cursor-bound work either inside an existing transaction or a private one."""
        if connection is not None:
            with connection.cursor() as cursor:
                return operation(cursor)

        with self.get_connection() as owned_connection:
            with owned_connection.cursor() as cursor:
                result = operation(cursor)
            owned_connection.commit()
        return result