"""
MariaDB connection pool for content pipeline.
"""

import logging
from contextlib import contextmanager
from typing import Any, Generator, Optional

import mysql.connector
from mysql.connector import Error as MySQLError
from mysql.connector.pooling import MySQLConnectionPool

from config.settings import get_settings

logger = logging.getLogger(__name__)

_pool: Optional[MySQLConnectionPool] = None


def get_pool() -> MySQLConnectionPool:
    """Get or create connection pool."""
    global _pool
    if _pool is None:
        settings = get_settings()
        _pool = MySQLConnectionPool(
            pool_name="content_pipeline",
            pool_size=5,
            pool_reset_session=True,
            **settings.db_config,
        )
        logger.info("Database connection pool created")
    return _pool


@contextmanager
def get_connection() -> Generator[mysql.connector.MySQLConnection, None, None]:
    """Get a connection from the pool."""
    pool = get_pool()
    connection = pool.get_connection()
    try:
        yield connection
    finally:
        connection.close()


@contextmanager
def get_cursor(
    dictionary: bool = True, buffered: bool = True
) -> Generator[mysql.connector.cursor.MySQLCursor, None, None]:
    """Get a cursor with automatic connection management."""
    with get_connection() as connection:
        cursor = connection.cursor(dictionary=dictionary, buffered=buffered)
        try:
            yield cursor
            connection.commit()
        except MySQLError as e:
            connection.rollback()
            logger.error(f"Database error: {e}")
            raise
        finally:
            cursor.close()


def execute_query(
    query: str, params: Optional[tuple] = None, fetch: bool = True
) -> Optional[list[dict[str, Any]]]:
    """Execute a query and optionally fetch results."""
    with get_cursor() as cursor:
        cursor.execute(query, params)
        if fetch:
            return cursor.fetchall()
        return None


def execute_many(query: str, params_list: list[tuple]) -> int:
    """Execute a query with multiple parameter sets."""
    with get_cursor() as cursor:
        cursor.executemany(query, params_list)
        return cursor.rowcount


def test_connection() -> bool:
    """Test database connectivity."""
    try:
        result = execute_query("SELECT 1 as test")
        return result is not None and len(result) > 0
    except MySQLError as e:
        logger.error(f"Connection test failed: {e}")
        return False