import queue, mysql.connector, threading, datetime

class ConnectionPool:
    def __init__(self, pool_name, pool_size, host, user, password, database):
        """
        Initialize the connection pool with the given parameters.

        :param pool_name: Name of the pool (for identification purposes)
        :param pool_size: Maximum number of connections to maintain
        :param host: Database host
        :param user: Database user
        :param password: Database password
        :param database: Database name
        """
        self.pool_name = pool_name
        self.pool_size = pool_size
        self.host = host
        self.user = user
        self.password = password
        self.database = database
        
        # Internal queue to hold the connections
        self.pool = queue.Queue(maxsize=pool_size)
        self.lock = threading.Lock()

        # Initialize the pool with `pool_size` connections
        for _ in range(pool_size):
            conn = self._create_new_connection()
            self.pool.put(conn)

    def _create_new_connection(self):
        """Creates a new database connection."""
        try:
            conn = mysql.connector.connect(
                host=self.host,
                user=self.user,
                password=self.password,
                database=self.database
            )
            return conn
        except mysql.connector.Error as e:
            print(f"{datetime.datetime.now()} | Error creating connection: {e}")
            raise

    def get_connection(self):
        """
        Retrieve a connection from the pool. If no connections are available, wait until one is.
        
        :return: A connection from the pool
        """
        with self.lock:
            try:
                # Get connection from the pool (blocks if none are available)
                conn = self.pool.get(block=True)
                
                # Test if the connection is still alive
                if not conn.is_connected():
                    conn = self._create_new_connection()
                
                return conn
            except queue.Empty:
                print(f"{datetime.datetime.now()} | No connections available in the pool!")
                raise

    def release_connection(self, conn):
        """
        Return a connection back to the pool.
        
        :param conn: The connection to return to the pool
        """
        with self.lock:
            # Return the connection back to the pool
            self.pool.put(conn)

    def close_all_connections(self):
        """Close all connections in the pool."""
        with self.lock:
            while not self.pool.empty():
                conn = self.pool.get()
                conn.close()

    def __del__(self):
        """Ensure all connections are closed when the pool is destroyed."""
        self.close_all_connections()