"""
PostgreSQL Connection Pool manager for CHECKPOINT_POSTGRES_URL
"""
import logging

from psycopg_pool import ConnectionPool

from config import CHECKPOINT_POSTGRES_URL

logger = logging.getLogger(__name__)

class CanifaDbPool:
    def __init__(self):
        self._pool = None

    def initialize(self):
        if not self._pool:
            try:
                self._pool = ConnectionPool(
                    CHECKPOINT_POSTGRES_URL,
                    min_size=1,
                    max_size=5,
                    max_idle=120,
                    timeout=10,
                    kwargs={"autocommit": True}
                )
                logger.info("✅ PostgreSQL Connection Pool Initialized (CHECKPOINT_POSTGRES_URL)")
            except Exception as e:
                logger.error("❌ Failed to initialize PG Pool: %s", e)

    def get_conn(self):
        """
        Returns a context manager yielding a managed connection. 
        Example:
            with db_pool.get_conn() as conn:
                conn.execute(...)
        """
        from config import USE_LOCAL_SQLITE
        if USE_LOCAL_SQLITE:
            from .sqlite_mock import get_mock_pg_conn
            return get_mock_pg_conn()
            
        if not self._pool:
            self.initialize()
        return self._pool.connection()

    def close_all(self):
        if self._pool:
            self._pool.close()

db_pool = CanifaDbPool()
