from mysql.connector import pooling
import logging
from app.config import Config
import threading

logger = logging.getLogger(__name__)

class DatabaseManager:
    _instance = None
    _pool = None
    _lock = threading.Lock()

    @classmethod
    def get_instance(cls):
        if cls._instance is None:
            with cls._lock:
                if cls._instance is None:
                    cls._instance = cls()
        return cls._instance

    def __init__(self):
        if DatabaseManager._pool is None:
            self._initialize_pool()

    def _initialize_pool(self):
        try:
            dbconfig = {
                "host": Config.MYSQL_HOST,
                "user": Config.MYSQL_USER,
                'password': Config.MYSQL_PASSWORD,
                'database': Config.MYSQL_DB,
                'pool_name': 'mypool',
                'pool_size': 5,  # Reduced pool size
                'connect_timeout': 10,
                'autocommit': True,
                'charset': 'utf8mb4',
                'use_unicode': True,
                'get_warnings': True,
                'raise_on_warnings': True,
                'pool_reset_session': True
            }
            DatabaseManager._pool = pooling.MySQLConnectionPool(**dbconfig)
            logger.info("Database connection pool initialized")
        except Exception as e:
            logger.error(f"Error initializing database pool: {str(e)}")
            raise

    def get_connection(self):
        try:
            return self._pool.get_connection()
        except Exception as e:
            logger.error(f"Error getting connection from pool: {str(e)}")
            # Try to reset the pool if we get a connection error
            self._reset_pool()
            return self._pool.get_connection()

    def _reset_pool(self):
        """Reset the connection pool if needed"""
        try:
            with self._lock:
                if self._pool:
                    # Close all connections in the pool
                    for _ in range(self._pool.pool_size):
                        try:
                            conn = self._pool.get_connection()
                            conn.close()
                        except:
                            pass
                    # Reinitialize the pool
                    self._initialize_pool()
        except Exception as e:
            logger.error(f"Error resetting pool: {str(e)}")
            raise

# Create a single instance
db_manager = DatabaseManager.get_instance()
