"""
Conexión directa a MySQL usando PyMySQL.
Soporta connection pooling para mejor rendimiento.
"""
import pymysql
from app.config import Config
from contextlib import contextmanager
from app.utils.logger import get_logger
import time

logger = get_logger(__name__)

# Variable para usar pooling
USE_POOL = Config.ENVIRONMENT == 'production' or Config.ENVIRONMENT == 'staging'
_pool_initialized = False

def _init_pool_if_needed():
    """Inicializa el pool si está habilitado."""
    global _pool_initialized, USE_POOL
    if USE_POOL and not _pool_initialized:
        try:
            from app.db_pool import initialize_pool
            initialize_pool(min_connections=2, max_connections=10)
            _pool_initialized = True
            logger.info("Database connection pool initialized")
        except Exception as e:
            logger.warning(f"Could not initialize connection pool, using direct connections: {e}")
            USE_POOL = False

def get_db_connection(retries=3, retry_delay=0.5):
    """
    Obtiene una conexión a la base de datos MySQL.
    Usa connection pooling si está habilitado.
    
    Args:
        retries: Número de intentos si falla la conexión
        retry_delay: Tiempo de espera entre intentos (segundos)
    
    Returns:
        pymysql.Connection: Conexión a la base de datos o None
    """
    if USE_POOL:
        _init_pool_if_needed()
        try:
            from app.db_pool import get_db_connection_pooled
            conn = get_db_connection_pooled()
            if conn:
                return conn
        except Exception as e:
            logger.error(f"Error getting connection from pool: {e}")
            # Fallback a conexión directa
    
    # Conexión directa (con retry logic)
    for attempt in range(retries):
        try:
            connection = 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 conexión
            connection.ping(reconnect=True)
            return connection
        except Exception as e:
            if attempt < retries - 1:
                logger.warning(f"Connection attempt {attempt + 1} failed, retrying... Error: {e}")
                time.sleep(retry_delay)
            else:
                logger.error(f"Error al conectar a la base de datos después de {retries} intentos: {e}")
                return None
    
    return None

@contextmanager
def get_db():
    """
    Context manager para obtener una conexión a la base de datos.
    Útil para usar con 'with' statement.
    Automáticamente devuelve la conexión al pool si está usando pooling.
    
    Usage:
        with get_db() as conn:
            cursor = conn.cursor()
            cursor.execute("SELECT 1")
            results = cursor.fetchall()
    """
    conn = get_db_connection()
    if not conn:
        raise Exception("No se pudo conectar a la base de datos")
    try:
        yield conn
        conn.commit()
    except Exception as e:
        conn.rollback()
        raise e
    finally:
        if USE_POOL and _pool_initialized:
            try:
                from app.db_pool import return_db_connection
                return_db_connection(conn)
            except:
                # Si falla devolver al pool, cerrar la conexión
                try:
                    conn.close()
                except:
                    pass
        else:
            try:
                conn.close()
            except:
                pass

def test_connection():
    """Prueba la conexión a la base de datos."""
    try:
        conn = get_db_connection()
        if conn:
            cursor = conn.cursor()
            cursor.execute("SELECT 1")
            cursor.close()
            
            # Devolver conexión al pool si está usando pooling
            if USE_POOL and _pool_initialized:
                try:
                    from app.db_pool import return_db_connection
                    return_db_connection(conn)
                except:
                    try:
                        conn.close()
                    except:
                        pass
            else:
                try:
                    conn.close()
                except:
                    pass
            
            logger.info(f"Conexión exitosa a {Config.DB_NAME}")
            return True
        else:
            logger.error(f"No se pudo conectar a {Config.DB_NAME}")
            return False
    except Exception as e:
        logger.error(f"Error en prueba de conexión: {e}")
        return False