# application/db/database.py
from mysql.connector import pooling
import os
from dotenv import load_dotenv

load_dotenv()

DATABASES = {
    "default": {
        "HOST": os.getenv("DB_HOST", "localhost"),
        "NAME": os.getenv("DB_NAME", "solar_app"),
        "USER": os.getenv("DB_USER", "root"),
        "PASSWORD": os.getenv("DB_PASSWORD", "")
    }
}

class Database:
    def __init__(self, commit=True):
        try:
            self.commit = commit
            self._connection = None
            self._cursor = None
            self._pool = pooling.MySQLConnectionPool(
                pool_name="emi_checkout_pool",
                pool_size=5,   # increase if needed for concurrency
                pool_reset_session=True,
                host=DATABASES["default"]["HOST"],
                database=DATABASES["default"]["NAME"],
                user=DATABASES["default"]["USER"],
                password=DATABASES["default"]["PASSWORD"]
            )
        except Exception as ex:
            raise Exception(f"Failed to initialize MySQL connection pool: {str(ex)}")

    def __enter__(self):
        # Get a connection from the pool
        if self._connection is None:
            self._connection = self._pool.get_connection()
        self._cursor = self._connection.cursor(dictionary=True)
        return self._cursor

    def __exit__(self, exc_type, exc_val, exc_tb):
        try:
            if exc_type:
                self._connection.rollback()
            elif self.commit:
                self._connection.commit()
        finally:
            if self._cursor:
                self._cursor.close()
            if self._connection:
                # Important: this does NOT destroy the connection,
                # it returns it back to the pool for reuse
                self._connection.close()
            self._cursor = None
            self._connection = None