from mysql.connector import pooling
from .base_db import BaseDBHandler

class MySQLHandler(BaseDBHandler):
    def connect(self, service, user, password, database=None):
        try:
            self.mysql_pool = pooling.MySQLConnectionPool(
                pool_name='my_pool',
                pool_size=5,
                host=service,
                port=20306,
                user=user,
                password=password
            )
            connection = self.mysql_pool.get_connection()
            connection.close()
            return self.make_connection_response("Successfully connected to MySQL")
        except Exception as e:
            return self.make_error_response(500, f"Internal Server Error, Failed to connect to MySQL: {e}")

    def execute_query(self, query, database=None):
        try:
            connection = self.mysql_pool.get_connection()
            cursor = connection.cursor()

            if database:
                cursor.execute(f"USE {database}")
            cursor.execute(query)

            # Commit transaction if it is a data manipulation language(DML) or data definition language(DDL)
            if query.strip().lower().startswith(("insert", "update", "delete", "create", "drop", "alter")):
                connection.commit()
                results = f"{cursor.rowcount} rows affected"
                columns_names = []
            else:
                results = cursor.fetchall()
                columns_names = [desc[0] for desc in cursor.description]

            cursor.close()
            connection.close()
            return self.make_success_response(
                columns_names=columns_names,
                results=results
            )
        except Exception as e:
            return self.make_error_response(500, f"Internal Server Error, Please check the query and try again: {e}")