import mysql.connector
import json
from franai.services.config import Config

class AppSettingsModel:
    """
    Model for managing application-wide settings in a key-value store.
    The `setting_value` is stored as a JSON string to accommodate various data types.
    """

    @staticmethod
    def get_db_connection():
        """Establishes a database connection."""
        try:
            return mysql.connector.connect(
                host=Config.DB_HOST,
                port=Config.DB_PORT,
                user=Config.DB_USER,
                password=Config.DB_PASSWORD,
                database=Config.DB_NAME
            )
        except mysql.connector.Error as e:
            print(f"Error connecting to MySQL Database: {e}")
            return None

    @staticmethod
    def create_table_if_not_exists():
        """Creates the app_settings table if it doesn't already exist."""
        conn = AppSettingsModel.get_db_connection()
        if not conn:
            return

        try:
            cursor = conn.cursor()
            cursor.execute("""
                CREATE TABLE IF NOT EXISTS app_settings (
                    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                    setting_key TEXT NOT NULL,
                    setting_value TEXT NOT NULL,
                    UNIQUE INDEX idx_setting_key (setting_key(191))
                )
            """)
            conn.commit()
            print("[OK] 'app_settings' table exists or was successfully created.")
        except mysql.connector.Error as e:
            print(f"Error creating 'app_settings' table: {e}")
        finally:
            if conn.is_connected():
                cursor.close()
                conn.close()

    @staticmethod
    def get_all_settings():
        """Retrieves all settings and returns them as a dictionary."""
        conn = AppSettingsModel.get_db_connection()
        if not conn:
            return {}

        settings = {}
        try:
            cursor = conn.cursor(dictionary=True)
            cursor.execute("SELECT setting_key, setting_value FROM app_settings")
            for row in cursor.fetchall():
                try:
                    # Manually decode the JSON string from the TEXT column
                    settings[row['setting_key']] = json.loads(row['setting_value'])
                except (json.JSONDecodeError, TypeError):
                    # Handle cases where the value is invalid JSON or not a string
                    print(f"⚠️ Warning: Could not decode setting '{row['setting_key']}'. Storing as raw value.")
                    settings[row['setting_key']] = row['setting_value']
            return settings
        except mysql.connector.Error as e:
            print(f"Error fetching all settings: {e}")
            return {} # Return empty dict on error
        finally:
            if conn.is_connected():
                cursor.close()
                conn.close()

    @staticmethod
    def update_settings(settings_dict):
        """
        Updates or inserts multiple settings from a dictionary.
        Returns True on success, False on failure.
        """
        conn = AppSettingsModel.get_db_connection()
        if not conn:
            return False

        try:
            cursor = conn.cursor()
            query = """
                INSERT INTO app_settings (setting_key, setting_value)
                VALUES (%s, %s)
                ON DUPLICATE KEY UPDATE
                    setting_value = VALUES(setting_value)
            """
            # Prepare data for executemany: list of tuples
            data_to_insert = [
                (key, json.dumps(value)) for key, value in settings_dict.items()
            ]
            cursor.executemany(query, data_to_insert)
            conn.commit()
            print(f"[OK] Successfully saved {len(data_to_insert)} settings to the database.")
            return True
        except mysql.connector.Error as e:
            print(f"Error saving settings: {e}")
            conn.rollback()
            return False
        finally:
            if conn.is_connected():
                cursor.close()
                conn.close()

    @staticmethod
    def seed_default_settings():
        """
        Populates the database with default settings if they don't already exist.
        This is safe to run on startup.
        """
        default_thresholds = {
            "quality_thresholds": {
                "envBrightnessMin": 80,
                "envBrightnessMax": 180,
                "glareMaxPercentage": 0.025,
                "backlightThreshold": 40,
                "faceBrightnessMin": 100,
                "faceBrightnessMax": 160,
                "darkPixelThreshold": 75,
                "darkPixelPercentage": 0.6,
                "lightingStdDevMax": 55,
                "sharpnessMin": 100
            }
        }

        conn = AppSettingsModel.get_db_connection()
        if not conn:
            return

        try:
            cursor = conn.cursor()
            
            for key, value in default_thresholds.items():
                # Use INSERT IGNORE to avoid overwriting existing settings
                query = "INSERT IGNORE INTO app_settings (setting_key, setting_value) VALUES (%s, %s)"
                cursor.execute(query, (key, json.dumps(value)))
            
            conn.commit()
            if cursor.rowcount > 0:
                print(f"[OK] Seeded {cursor.rowcount} default setting(s).")
        except mysql.connector.Error as e:
            print(f"Error seeding default settings: {e}")
        finally:
            if conn.is_connected():
                cursor.close()
                conn.close()