"""
Connection pooling para MySQL usando PyMySQL.
Mejora el rendimiento al reutilizar conexiones en lugar de crear nuevas cada vez.
"""
import pymysql
from queue import Queue, Empty
import threading
import time
from app.config import Config
from app.utils.logger import get_logger

logger = get_logger(__name__)

class ConnectionPool:
    """Pool de conexiones MySQL para mejorar el rendimiento."""
    
    def __init__(self, min_connections=2, max_connections=10, connection_timeout=5):
        """
        Inicializa el pool de conexiones.
        
        Args:
            min_connections: Número mínimo de conexiones en el pool
            max_connections: Número máximo de conexiones en el pool
            connection_timeout: Timeout para obtener una conexión del pool (segundos)
        """
        self.min_connections = min_connections
        self.max_connections = max_connections
        self.connection_timeout = connection_timeout
        self.pool = Queue(maxsize=max_connections)
        self.created_connections = 0
        self.lock = threading.Lock()
        self._initialize_pool()
    
    def _create_connection(self):
        """Crea una nueva conexión a la base de datos."""
        try:
            conn = pymysql.connect(
                host=Config.DB_HOST,
                port=Config.DB_PORT,
                user=Config.DB_USER,
                password=Config.DB_PASSWORD,
                database=Config.DB_NAME,
                charset='utf8mb4',
                cursorclass=pymysql.cursors.DictCursor,
                autocommit=False,
                connect_timeout=5,
                read_timeout=30,
                write_timeout=30
            )
            # Verificar que la conexión esté viva
            conn.ping(reconnect=True)
            return conn
        except Exception as e:
            logger.error(f"Error creating database connection: {e}", exc_info=True)
            return None
    
    def _initialize_pool(self):
        """Inicializa el pool con conexiones mínimas."""
        for _ in range(self.min_connections):
            conn = self._create_connection()
            if conn:
                self.pool.put(conn)
                with self.lock:
                    self.created_connections += 1
        logger.info(f"Connection pool initialized with {self.created_connections} connections")
    
    def get_connection(self):
        """
        Obtiene una conexión del pool.
        
        Returns:
            pymysql.Connection o None si no se puede obtener una conexión
        """
        try:
            # Intentar obtener conexión existente del pool
            conn = self.pool.get(timeout=self.connection_timeout)
            
            # Verificar que la conexión siga viva
            try:
                conn.ping(reconnect=True)
                return conn
            except:
                # Conexión muerta, crear una nueva
                logger.warning("Dead connection detected, creating new one")
                conn.close()
                with self.lock:
                    self.created_connections -= 1
                return self._create_connection()
                
        except Empty:
            # Pool vacío, crear nueva conexión si no excedemos el máximo
            with self.lock:
                if self.created_connections < self.max_connections:
                    self.created_connections += 1
                    conn = self._create_connection()
                    if conn:
                        return conn
                    else:
                        self.created_connections -= 1
            
            # Pool lleno, esperar un poco y reintentar
            logger.warning("Connection pool exhausted, waiting...")
            try:
                conn = self.pool.get(timeout=self.connection_timeout)
                conn.ping(reconnect=True)
                return conn
            except Empty:
                logger.error("Could not get connection from pool")
                return None
    
    def return_connection(self, conn):
        """
        Devuelve una conexión al pool.
        
        Args:
            conn: Conexión a devolver
        """
        if conn:
            try:
                # Verificar que la conexión esté viva antes de devolverla
                conn.ping(reconnect=False)
                self.pool.put(conn, block=False)
            except:
                # Conexión muerta, no devolverla al pool
                try:
                    conn.close()
                except:
                    pass
                with self.lock:
                    self.created_connections -= 1
                logger.warning("Dead connection removed from pool")
    
    def close_all(self):
        """Cierra todas las conexiones del pool."""
        with self.lock:
            while not self.pool.empty():
                try:
                    conn = self.pool.get_nowait()
                    conn.close()
                    self.created_connections -= 1
                except:
                    pass
            logger.info("All pool connections closed")
    
    def get_stats(self):
        """Obtiene estadísticas del pool."""
        with self.lock:
            return {
                'created': self.created_connections,
                'available': self.pool.qsize(),
                'in_use': self.created_connections - self.pool.qsize(),
                'min': self.min_connections,
                'max': self.max_connections
            }

# Pool global
_pool = None

def initialize_pool(min_connections=2, max_connections=10):
    """Inicializa el pool global de conexiones."""
    global _pool
    if _pool is None:
        _pool = ConnectionPool(min_connections=min_connections, max_connections=max_connections)
    return _pool

def get_pool():
    """Obtiene el pool global de conexiones."""
    global _pool
    if _pool is None:
        initialize_pool()
    return _pool

def get_db_connection_pooled():
    """
    Obtiene una conexión del pool.
    
    Returns:
        pymysql.Connection o None
    """
    pool = get_pool()
    return pool.get_connection()

def return_db_connection(conn):
    """Devuelve una conexión al pool."""
    pool = get_pool()
    if pool:
        pool.return_connection(conn)