"""
Database operations module for JSON Business Rules Engine.
Handles MySQL database connections and query execution.
"""
import os
import mysql.connector
from dotenv import load_dotenv

# Load environment variables
load_dotenv()

# Database configuration from environment variables
DB_HOST = os.getenv("DB_HOST")
DB_PORT = os.getenv("DB_PORT", "3306")
DB_USER = os.getenv("DB_USER")
DB_PASSWORD = os.getenv("DB_PASSWORD")
DB_DATABASE = os.getenv("DB_DATABASE")

# Global connection variable
_connection = None


def create_connection():
    """
    Creates and returns a MySQL database connection.
    
    :return: mysql.connector.connection.MySQLConnection object or None if connection fails
    """
    try:
        conn = mysql.connector.connect(
            host=DB_HOST,
            port=int(DB_PORT) if DB_PORT else 3306,
            user=DB_USER,
            password=DB_PASSWORD,
            database=DB_DATABASE
        )

        print("Connecting to MySQL database")
        if conn.is_connected():
            print("Connected to MySQL database")
            return conn
        else:
            print("Failed to connect to MySQL database")
            return None

    except Exception as e:
        print(f"Error connecting to database: {e}")
        return None


def get_connection():
    """
    Gets the current database connection. Creates a new one if it doesn't exist.
    
    :return: mysql.connector.connection.MySQLConnection object or None
    """
    global _connection
    if _connection is None or not _connection.is_connected():
        _connection = create_connection()
    return _connection


def set_connection(conn):
    """
    Sets the global database connection.
    
    :param conn: mysql.connector.connection.MySQLConnection object
    """
    global _connection
    _connection = conn


def close_connection():
    """
    Closes the current database connection if it exists.
    """
    global _connection
    if _connection is not None and _connection.is_connected():
        _connection.close()
        _connection = None
        print("Database connection closed")


def execute_select_query(connection, query):
    """
    Executes a SELECT query and returns the results.
    
    :param connection: mysql.connector.connection.MySQLConnection object
    :param query: SQL SELECT query string
    :return: List of dictionaries containing query results, or None if error occurs
    """
    cursor = None
    try:
        cursor = connection.cursor(dictionary=True)
        cursor.execute(query)
        result = cursor.fetchall()
        return result
    except mysql.connector.Error as err:
        print(f"Database query error: {err}")
        return None
    finally:
        if cursor is not None:
            cursor.close()


def init_connection(data=None):
    """
    Initializes a database connection. This function maintains compatibility
    with the existing codebase while using the new connection management.
    
    :param data: Optional configuration data (for compatibility, not currently used)
    :return: mysql.connector.connection.MySQLConnection object or None
    """
    return create_connection()