"""
MySQLModel — thin wrapper around mysql-connector-python.
Reads connection settings from Flask app config (MYSQL_DATABASES['default']).
"""

from typing import Dict, List, Optional, Tuple
import mysql.connector

from mysql.connector import Error, pooling
from flask import current_app
from app.utils.helpers import get_logger


class MySQLModel:
    """MySQL database access layer used by DatabaseService and ReportController."""

    def __init__(self, db_name: str = "default"):
        self.db_name = db_name
        self.logger = get_logger("mysql_model")

    def _get_config(self) -> Dict:
        db = current_app.config["MYSQL_DATABASES"][self.db_name]
        return {
            "host": db["host"],
            "port": db["port"],
            "database": db["database"],
            "user": db["username"],
            "password": db["password"],
            "charset": "utf8mb4",
            "autocommit": True,
            "connection_timeout": 10,
            "connect_timeout": 10,
        }

    def _get_connection(self):
        return mysql.connector.connect(**self._get_config())

    def execute_query(self, query: str, params: Optional[Tuple] = None) -> List[Dict]:
        """Execute a SELECT / DML query and return results as a list of dicts."""
        conn = self._get_connection()
        cursor = None
        try:
            cursor = conn.cursor(dictionary=True)
            cursor.execute(query, params)
            results = cursor.fetchall()

            return results
        except Error as e:
            self.logger.error(f"MySQL query error: {e}")
            raise
        finally:
            if cursor:
                cursor.close()
            conn.close()

    def test_connection(self) -> Dict:
        """Ping the database and return a status dict."""
        conn = None
        try:
            conn = self._get_connection()
            conn.ping(reconnect=False)

            return {"status": "connected"}

        except Error as e:
            self.logger.warning(f"MySQL connection test failed: {e}")
            return {"status": "error", "message": str(e)}
        finally:
            if conn:
                conn.close()

    def cleanup(self):
        pass