yamlbase 0.7.2

use std::collections::HashMap;
use tracing::debug;

use crate::database::Value;
use crate::sql::executor::QueryResult;

/// MySQL System Variables Handler
///
/// Manages MySQL system variables like @@version, @@sql_mode, etc.
/// These are essential for SQLAlchemy and other MySQL clients to work properly.
pub struct MySqlSystemVariables {
    variables: HashMap<String, String>,
}

impl MySqlSystemVariables {
    pub fn new() -> Self {
        let mut variables = HashMap::new();

        // Core MySQL system variables that clients expect
        variables.insert("version".to_string(), "8.0.35-yamlbase".to_string());
        variables.insert(
            "version_comment".to_string(),
            "yamlbase MySQL-compatible server".to_string(),
        );
        variables.insert("version_compile_machine".to_string(), "x86_64".to_string());
        variables.insert("version_compile_os".to_string(), "Linux".to_string());

        // SQL Mode - start with a safe default
        variables.insert("sql_mode".to_string(), "ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION".to_string());

        // Character set and collation
        variables.insert("character_set_client".to_string(), "utf8mb4".to_string());
        variables.insert(
            "character_set_connection".to_string(),
            "utf8mb4".to_string(),
        );
        variables.insert("character_set_database".to_string(), "utf8mb4".to_string());
        variables.insert("character_set_results".to_string(), "utf8mb4".to_string());
        variables.insert("character_set_server".to_string(), "utf8mb4".to_string());
        variables.insert("character_set_system".to_string(), "utf8mb3".to_string());
        variables.insert(
            "collation_connection".to_string(),
            "utf8mb4_0900_ai_ci".to_string(),
        );
        variables.insert(
            "collation_database".to_string(),
            "utf8mb4_0900_ai_ci".to_string(),
        );
        variables.insert(
            "collation_server".to_string(),
            "utf8mb4_0900_ai_ci".to_string(),
        );

        // Network and connection settings
        variables.insert("max_allowed_packet".to_string(), "67108864".to_string()); // 64MB
        variables.insert("max_connections".to_string(), "151".to_string());
        variables.insert("max_connect_errors".to_string(), "100".to_string());
        variables.insert("connect_timeout".to_string(), "10".to_string());
        variables.insert("interactive_timeout".to_string(), "28800".to_string());
        variables.insert("wait_timeout".to_string(), "28800".to_string());
        variables.insert("net_read_timeout".to_string(), "30".to_string());
        variables.insert("net_write_timeout".to_string(), "60".to_string());

        // Transaction and locking
        variables.insert("autocommit".to_string(), "1".to_string());
        variables.insert(
            "transaction_isolation".to_string(),
            "REPEATABLE-READ".to_string(),
        );
        variables.insert("innodb_lock_wait_timeout".to_string(), "50".to_string());

        // Storage engine
        variables.insert("default_storage_engine".to_string(), "InnoDB".to_string());
        variables.insert("storage_engine".to_string(), "InnoDB".to_string());

        // Time zone
        variables.insert("system_time_zone".to_string(), "UTC".to_string());
        variables.insert("time_zone".to_string(), "SYSTEM".to_string());

        // Lower case table names (important for cross-platform compatibility)
        variables.insert("lower_case_table_names".to_string(), "0".to_string());

        // Other commonly queried variables
        variables.insert("port".to_string(), "3306".to_string());
        variables.insert("protocol_version".to_string(), "10".to_string());
        variables.insert("read_only".to_string(), "1".to_string()); // We're read-only
        variables.insert("skip_networking".to_string(), "0".to_string());
        variables.insert("socket".to_string(), "/tmp/mysql.sock".to_string());

        // License and server info
        variables.insert("license".to_string(), "GPL".to_string());
        variables.insert("hostname".to_string(), "localhost".to_string());

        // Query cache (disabled for yamlbase)
        variables.insert("query_cache_size".to_string(), "0".to_string());
        variables.insert("query_cache_type".to_string(), "OFF".to_string());

        // Performance schema
        variables.insert("performance_schema".to_string(), "OFF".to_string());

        // Information schema
        variables.insert(
            "information_schema_stats_expiry".to_string(),
            "86400".to_string(),
        );

        // Binary logging (disabled)
        variables.insert("log_bin".to_string(), "OFF".to_string());
        variables.insert("binlog_format".to_string(), "ROW".to_string());

        // SQL strict mode components
        variables.insert("strict_trans_tables".to_string(), "1".to_string());
        variables.insert("no_zero_date".to_string(), "1".to_string());
        variables.insert("no_zero_in_date".to_string(), "1".to_string());
        variables.insert("error_for_division_by_zero".to_string(), "1".to_string());

        Self { variables }
    }

    /// Get a system variable value
    pub fn get_variable(&self, name: &str) -> Option<&String> {
        // Handle both @@variable and variable formats
        let clean_name = name
            .trim_start_matches("@@")
            .trim_start_matches("global.")
            .trim_start_matches("session.")
            .to_lowercase();

        self.variables.get(&clean_name)
    }

    /// Set a system variable (for SET commands)
    pub fn set_variable(&mut self, name: &str, value: &str) -> bool {
        let clean_name = name
            .trim_start_matches("@@")
            .trim_start_matches("global.")
            .trim_start_matches("session.")
            .to_lowercase();

        debug!("Setting MySQL system variable: {} = {}", clean_name, value);

        // Only allow setting certain variables (mostly session-level)
        match clean_name.as_str() {
            "sql_mode"
            | "autocommit"
            | "character_set_client"
            | "character_set_connection"
            | "character_set_results"
            | "collation_connection"
            | "time_zone"
            | "transaction_isolation"
            | "session.sql_mode"
            | "session.autocommit" => {
                self.variables.insert(clean_name, value.to_string());
                true
            }
            _ => {
                debug!(
                    "Attempted to set read-only or unknown variable: {}",
                    clean_name
                );
                false
            }
        }
    }

    /// Handle SHOW VARIABLES command
    pub fn handle_show_variables(&self, pattern: Option<&str>) -> QueryResult {
        let mut results = Vec::new();

        for (name, value) in &self.variables {
            let should_include = match pattern {
                Some(p) => {
                    // Simple pattern matching - in a real implementation you'd use proper SQL LIKE pattern matching
                    let pattern_lower = p.to_lowercase();
                    let name_lower = name.to_lowercase();

                    if pattern_lower.contains('%') {
                        // Basic % wildcard support
                        let pattern_parts: Vec<&str> = pattern_lower.split('%').collect();
                        if pattern_parts.len() == 2 {
                            name_lower.starts_with(pattern_parts[0])
                                && name_lower.ends_with(pattern_parts[1])
                        } else {
                            name_lower.contains(&pattern_lower.replace('%', ""))
                        }
                    } else {
                        name_lower.contains(&pattern_lower)
                    }
                }
                None => true,
            };

            if should_include {
                results.push(vec![Value::Text(name.clone()), Value::Text(value.clone())]);
            }
        }

        // Sort results by variable name for consistent output
        results.sort_by(|a, b| {
            if let (Value::Text(a_name), Value::Text(b_name)) = (&a[0], &b[0]) {
                a_name.cmp(b_name)
            } else {
                std::cmp::Ordering::Equal
            }
        });

        QueryResult {
            columns: vec!["Variable_name".to_string(), "Value".to_string()],
            column_types: vec![
                crate::yaml::schema::SqlType::Text,
                crate::yaml::schema::SqlType::Text,
            ],
            rows: results,
        }
    }

    /// Handle SELECT @@variable queries
    pub fn handle_variable_query(&self, variable_name: &str) -> QueryResult {
        let value = self
            .get_variable(variable_name)
            .map(|v| Value::Text(v.clone()))
            .unwrap_or_else(|| {
                debug!("Unknown system variable requested: {}", variable_name);
                Value::Text("".to_string())
            });

        QueryResult {
            columns: vec![format!("@@{}", variable_name)],
            column_types: vec![crate::yaml::schema::SqlType::Text],
            rows: vec![vec![value]],
        }
    }

    /// Handle multiple variable queries like SELECT @@version, @@sql_mode
    pub fn handle_multiple_variables(&self, variables: &[&str]) -> QueryResult {
        let mut columns = Vec::new();
        let mut values = Vec::new();

        for var_name in variables {
            let clean_name = var_name
                .trim_start_matches("@@")
                .trim_start_matches("global.")
                .trim_start_matches("session.");

            columns.push(format!("@@{}", clean_name));

            let value = self
                .get_variable(clean_name)
                .map(|v| Value::Text(v.clone()))
                .unwrap_or_else(|| Value::Text("".to_string()));

            values.push(value);
        }

        QueryResult {
            columns,
            column_types: vec![crate::yaml::schema::SqlType::Text; values.len()],
            rows: vec![values],
        }
    }

    /// Check if a query is requesting system variables
    pub fn is_system_variable_query(&self, query: &str) -> bool {
        let query_upper = query.to_uppercase();
        query_upper.contains("@@")
            || query_upper.starts_with("SHOW VARIABLES")
            || query_upper.starts_with("SHOW SESSION VARIABLES")
            || query_upper.starts_with("SHOW GLOBAL VARIABLES")
    }

    /// Parse and handle SET commands for system variables
    pub fn handle_set_command(&mut self, query: &str) -> crate::Result<bool> {
        let query_trimmed = query.trim();
        let query_upper = query_trimmed.to_uppercase();

        if !query_upper.starts_with("SET ") {
            return Ok(false);
        }

        let set_part = &query_trimmed[4..].trim(); // Remove "SET "

        // Handle various SET command formats
        if let Some(eq_pos) = set_part.find('=') {
            let var_part = set_part[..eq_pos].trim();
            let value_part = set_part[eq_pos + 1..].trim();

            // Remove quotes from value if present
            let clean_value = value_part.trim_matches('\'').trim_matches('"');

            // Handle different variable name formats
            let clean_var_name = var_part
                .trim_start_matches("@@")
                .trim_start_matches("global.")
                .trim_start_matches("session.")
                .trim();

            debug!("Parsed SET command: {} = {}", clean_var_name, clean_value);

            self.set_variable(clean_var_name, clean_value);
            return Ok(true);
        }

        // Handle SET NAMES command
        if query_upper.contains("SET NAMES") {
            debug!("Ignoring SET NAMES command (always using UTF-8)");
            return Ok(true);
        }

        // Handle SET CHARACTER SET command
        if query_upper.contains("SET CHARACTER SET") {
            debug!("Ignoring SET CHARACTER SET command (always using UTF-8)");
            return Ok(true);
        }

        Ok(false)
    }

    /// Get all variables as a vector for debugging
    pub fn get_all_variables(&self) -> Vec<(String, String)> {
        let mut vars: Vec<_> = self
            .variables
            .iter()
            .map(|(k, v)| (k.clone(), v.clone()))
            .collect();
        vars.sort_by(|a, b| a.0.cmp(&b.0));
        vars
    }
}

impl Default for MySqlSystemVariables {
    fn default() -> Self {
        Self::new()
    }
}

#[cfg(test)]
mod tests {
    use super::*;

    #[test]
    fn test_variable_access() {
        let vars = MySqlSystemVariables::new();

        assert_eq!(
            vars.get_variable("version"),
            Some(&"8.0.35-yamlbase".to_string())
        );
        assert_eq!(
            vars.get_variable("@@version"),
            Some(&"8.0.35-yamlbase".to_string())
        );
        assert_eq!(
            vars.get_variable("global.version"),
            Some(&"8.0.35-yamlbase".to_string())
        );
        assert_eq!(
            vars.get_variable("session.version"),
            Some(&"8.0.35-yamlbase".to_string())
        );
    }

    #[test]
    fn test_set_variable() {
        let mut vars = MySqlSystemVariables::new();

        // Should be able to set sql_mode
        assert!(vars.set_variable("sql_mode", "STRICT_TRANS_TABLES"));
        assert_eq!(
            vars.get_variable("sql_mode"),
            Some(&"STRICT_TRANS_TABLES".to_string())
        );

        // Should not be able to set version (read-only)
        assert!(!vars.set_variable("version", "9.0.0"));
        assert_eq!(
            vars.get_variable("version"),
            Some(&"8.0.35-yamlbase".to_string())
        );
    }

    #[test]
    fn test_show_variables() {
        let vars = MySqlSystemVariables::new();

        let result = vars.handle_show_variables(None);
        assert_eq!(result.columns, vec!["Variable_name", "Value"]);
        assert!(result.rows.len() > 10); // Should have many variables

        let result_filtered = vars.handle_show_variables(Some("version%"));
        assert!(result_filtered.rows.len() >= 4); // version, version_comment, etc.
    }

    #[test]
    fn test_variable_query() {
        let vars = MySqlSystemVariables::new();

        let result = vars.handle_variable_query("version");
        assert_eq!(result.columns, vec!["@@version"]);
        assert_eq!(result.rows.len(), 1);

        if let Value::Text(version) = &result.rows[0][0] {
            assert_eq!(version, "8.0.35-yamlbase");
        } else {
            panic!("Expected text value");
        }
    }

    #[test]
    fn test_set_command_parsing() {
        let mut vars = MySqlSystemVariables::new();

        assert!(
            vars.handle_set_command("SET sql_mode = 'STRICT_TRANS_TABLES'")
                .unwrap()
        );
        assert_eq!(
            vars.get_variable("sql_mode"),
            Some(&"STRICT_TRANS_TABLES".to_string())
        );

        assert!(
            vars.handle_set_command("SET @@session.autocommit = 1")
                .unwrap()
        );
        assert_eq!(vars.get_variable("autocommit"), Some(&"1".to_string()));

        assert!(vars.handle_set_command("SET NAMES utf8mb4").unwrap());

        assert!(!vars.handle_set_command("SELECT * FROM users").unwrap());
    }
}