yamlbase 0.7.2

use crate::YamlBaseError;
use crate::database::Value;

/// MySQL-specific SQL functions that enhance compatibility with MySQL clients
pub struct MySqlFunctions;

impl MySqlFunctions {
    /// Evaluate a MySQL-specific function call
    pub fn evaluate_function(name: &str, args: &[Value]) -> Option<crate::Result<Value>> {
        match name.to_uppercase().as_str() {
            "IFNULL" => Some(Self::ifnull(args)),
            "NULLIF" => Some(Self::nullif(args)),
            "IF" => Some(Self::if_function(args)),
            "FIND_IN_SET" => Some(Self::find_in_set(args)),
            "FIELD" => Some(Self::field(args)),
            "ELT" => Some(Self::elt(args)),
            "INTERVAL" => Some(Self::interval(args)),
            "GREATEST" => Some(Self::greatest(args)),
            "LEAST" => Some(Self::least(args)),
            "CONCAT_WS" => Some(Self::concat_ws(args)),
            "LEFT" => Some(Self::left(args)),
            "RIGHT" => Some(Self::right(args)),
            "MID" | "SUBSTRING" => Some(Self::mid(args)),
            "LOCATE" | "POSITION" => Some(Self::locate(args)),
            "INSTR" => Some(Self::instr(args)),
            "REVERSE" => Some(Self::reverse(args)),
            "REPEAT" => Some(Self::repeat(args)),
            "SPACE" => Some(Self::space(args)),
            "LPAD" => Some(Self::lpad(args)),
            "RPAD" => Some(Self::rpad(args)),
            "STRCMP" => Some(Self::strcmp(args)),
            "CHAR_LENGTH" | "CHARACTER_LENGTH" => Some(Self::char_length(args)),
            "BIT_LENGTH" => Some(Self::bit_length(args)),
            "OCT" => Some(Self::oct(args)),
            "HEX" => Some(Self::hex(args)),
            "UNHEX" => Some(Self::unhex(args)),
            "BIN" => Some(Self::bin(args)),
            "CONV" => Some(Self::conv(args)),
            "FORMAT" => Some(Self::format(args)),
            "INET_ATON" => Some(Self::inet_aton(args)),
            "INET_NTOA" => Some(Self::inet_ntoa(args)),
            "CONNECTION_ID" => Some(Self::connection_id(args)),
            "DATABASE" | "SCHEMA" => Some(Self::database(args)),
            "USER" => Some(Self::user(args)),
            "VERSION" => Some(Self::version(args)),
            _ => None, // Not a MySQL-specific function we handle
        }
    }

    /// IFNULL(expr1, expr2) - returns expr2 if expr1 is NULL, otherwise expr1
    fn ifnull(args: &[Value]) -> crate::Result<Value> {
        if args.len() != 2 {
            return Err(YamlBaseError::TypeConversion(
                "IFNULL requires exactly 2 arguments".to_string(),
            ));
        }

        match &args[0] {
            Value::Null => Ok(args[1].clone()),
            _ => Ok(args[0].clone()),
        }
    }

    /// NULLIF(expr1, expr2) - returns NULL if expr1 = expr2, otherwise expr1
    fn nullif(args: &[Value]) -> crate::Result<Value> {
        if args.len() != 2 {
            return Err(YamlBaseError::TypeConversion(
                "NULLIF requires exactly 2 arguments".to_string(),
            ));
        }

        if args[0] == args[1] {
            Ok(Value::Null)
        } else {
            Ok(args[0].clone())
        }
    }

    /// IF(condition, true_value, false_value) - returns true_value if condition is true
    fn if_function(args: &[Value]) -> crate::Result<Value> {
        if args.len() != 3 {
            return Err(YamlBaseError::TypeConversion(
                "IF requires exactly 3 arguments".to_string(),
            ));
        }

        let condition = match &args[0] {
            Value::Boolean(b) => *b,
            Value::Integer(i) => *i != 0,
            Value::Float(f) => *f != 0.0,
            Value::Text(s) => !s.is_empty() && s != "0",
            Value::Null => false,
            _ => false,
        };

        if condition {
            Ok(args[1].clone())
        } else {
            Ok(args[2].clone())
        }
    }

    /// FIND_IN_SET(str, strlist) - returns position of str in comma-separated strlist
    fn find_in_set(args: &[Value]) -> crate::Result<Value> {
        if args.len() != 2 {
            return Err(YamlBaseError::TypeConversion(
                "FIND_IN_SET requires exactly 2 arguments".to_string(),
            ));
        }

        let needle = match &args[0] {
            Value::Text(s) => s,
            Value::Null => return Ok(Value::Null),
            _ => return Ok(Value::Integer(0)),
        };

        let haystack = match &args[1] {
            Value::Text(s) => s,
            Value::Null => return Ok(Value::Null),
            _ => return Ok(Value::Integer(0)),
        };

        for (i, item) in haystack.split(',').enumerate() {
            if item == needle {
                return Ok(Value::Integer((i + 1) as i64));
            }
        }

        Ok(Value::Integer(0))
    }

    /// FIELD(str, str1, str2, ...) - returns index of str in the arguments list
    fn field(args: &[Value]) -> crate::Result<Value> {
        if args.is_empty() {
            return Err(YamlBaseError::TypeConversion(
                "FIELD requires at least 1 argument".to_string(),
            ));
        }

        let needle = &args[0];

        for (i, value) in args[1..].iter().enumerate() {
            if needle == value {
                return Ok(Value::Integer((i + 1) as i64));
            }
        }

        Ok(Value::Integer(0))
    }

    /// ELT(N, str1, str2, ...) - returns the Nth string from the list
    fn elt(args: &[Value]) -> crate::Result<Value> {
        if args.len() < 2 {
            return Err(YamlBaseError::TypeConversion(
                "ELT requires at least 2 arguments".to_string(),
            ));
        }

        let index = match &args[0] {
            Value::Integer(i) => *i,
            Value::Float(f) => *f as i64,
            _ => return Ok(Value::Null),
        };

        if index < 1 || index as usize > args.len() - 1 {
            return Ok(Value::Null);
        }

        Ok(args[index as usize].clone())
    }

    /// INTERVAL(N, N1, N2, ...) - returns index of first argument that is greater than N
    fn interval(args: &[Value]) -> crate::Result<Value> {
        if args.len() < 2 {
            return Err(YamlBaseError::TypeConversion(
                "INTERVAL requires at least 2 arguments".to_string(),
            ));
        }

        let needle = match &args[0] {
            Value::Integer(i) => *i as f64,
            Value::Float(f) => *f as f64,
            _ => return Ok(Value::Integer(0)),
        };

        for (i, value) in args[1..].iter().enumerate() {
            let val = match value {
                Value::Integer(iv) => *iv as f64,
                Value::Float(fv) => *fv as f64,
                _ => continue,
            };

            if needle < val {
                return Ok(Value::Integer(i as i64));
            }
        }

        Ok(Value::Integer((args.len() - 1) as i64))
    }

    /// GREATEST(value1, value2, ...) - returns the largest value
    fn greatest(args: &[Value]) -> crate::Result<Value> {
        if args.is_empty() {
            return Err(YamlBaseError::TypeConversion(
                "GREATEST requires at least 1 argument".to_string(),
            ));
        }

        let mut max_val = &args[0];
        for value in &args[1..] {
            if Self::compare_values(value, max_val) > 0 {
                max_val = value;
            }
        }

        Ok(max_val.clone())
    }

    /// LEAST(value1, value2, ...) - returns the smallest value
    fn least(args: &[Value]) -> crate::Result<Value> {
        if args.is_empty() {
            return Err(YamlBaseError::TypeConversion(
                "LEAST requires at least 1 argument".to_string(),
            ));
        }

        let mut min_val = &args[0];
        for value in &args[1..] {
            if Self::compare_values(value, min_val) < 0 {
                min_val = value;
            }
        }

        Ok(min_val.clone())
    }

    /// Helper function to compare values
    fn compare_values(a: &Value, b: &Value) -> i32 {
        match (a, b) {
            (Value::Integer(a), Value::Integer(b)) => a.cmp(b) as i32,
            (Value::Float(a), Value::Float(b)) => {
                a.partial_cmp(b).unwrap_or(std::cmp::Ordering::Equal) as i32
            }
            (Value::Integer(a), Value::Float(b)) => (*a as f32)
                .partial_cmp(b)
                .unwrap_or(std::cmp::Ordering::Equal)
                as i32,
            (Value::Float(a), Value::Integer(b)) => {
                a.partial_cmp(&(*b as f32))
                    .unwrap_or(std::cmp::Ordering::Equal) as i32
            }
            (Value::Text(a), Value::Text(b)) => a.cmp(b) as i32,
            (Value::Null, Value::Null) => 0,
            (Value::Null, _) => -1,
            (_, Value::Null) => 1,
            _ => 0,
        }
    }

    /// CONCAT_WS(separator, str1, str2, ...) - concatenate with separator
    fn concat_ws(args: &[Value]) -> crate::Result<Value> {
        if args.len() < 2 {
            return Err(YamlBaseError::TypeConversion(
                "CONCAT_WS requires at least 2 arguments".to_string(),
            ));
        }

        let separator = match &args[0] {
            Value::Text(s) => s,
            Value::Null => return Ok(Value::Null),
            _ => return Ok(Value::Null),
        };

        let mut parts = Vec::new();
        for value in &args[1..] {
            match value {
                Value::Text(s) => parts.push(s.clone()),
                Value::Integer(i) => parts.push(i.to_string()),
                Value::Float(f) => parts.push(f.to_string()),
                Value::Boolean(b) => parts.push(if *b { "1" } else { "0" }.to_string()),
                Value::Null => {} // Skip NULL values
                _ => parts.push(value.to_string()),
            }
        }

        Ok(Value::Text(parts.join(separator)))
    }

    /// LEFT(str, len) - returns leftmost len characters
    fn left(args: &[Value]) -> crate::Result<Value> {
        if args.len() != 2 {
            return Err(YamlBaseError::TypeConversion(
                "LEFT requires exactly 2 arguments".to_string(),
            ));
        }

        let text = match &args[0] {
            Value::Text(s) => s,
            Value::Null => return Ok(Value::Null),
            _ => return Ok(Value::Text(args[0].to_string())),
        };

        let len = match &args[1] {
            Value::Integer(i) => *i as usize,
            Value::Float(f) => *f as usize,
            _ => return Ok(Value::Null),
        };

        let chars: Vec<char> = text.chars().collect();
        let result = chars.iter().take(len).collect::<String>();
        Ok(Value::Text(result))
    }

    /// RIGHT(str, len) - returns rightmost len characters
    fn right(args: &[Value]) -> crate::Result<Value> {
        if args.len() != 2 {
            return Err(YamlBaseError::TypeConversion(
                "RIGHT requires exactly 2 arguments".to_string(),
            ));
        }

        let text = match &args[0] {
            Value::Text(s) => s,
            Value::Null => return Ok(Value::Null),
            _ => return Ok(Value::Text(args[0].to_string())),
        };

        let len = match &args[1] {
            Value::Integer(i) => *i as usize,
            Value::Float(f) => *f as usize,
            _ => return Ok(Value::Null),
        };

        let chars: Vec<char> = text.chars().collect();
        let start = if chars.len() > len {
            chars.len() - len
        } else {
            0
        };
        let result = chars.iter().skip(start).collect::<String>();
        Ok(Value::Text(result))
    }

    /// MID/SUBSTRING(str, pos, len) - returns substring
    fn mid(args: &[Value]) -> crate::Result<Value> {
        if args.len() < 2 || args.len() > 3 {
            return Err(YamlBaseError::TypeConversion(
                "MID requires 2 or 3 arguments".to_string(),
            ));
        }

        let text = match &args[0] {
            Value::Text(s) => s,
            Value::Null => return Ok(Value::Null),
            _ => return Ok(Value::Text(args[0].to_string())),
        };

        let pos = match &args[1] {
            Value::Integer(i) => (*i - 1) as usize, // MySQL is 1-indexed
            Value::Float(f) => (*f - 1.0) as usize,
            _ => return Ok(Value::Null),
        };

        let chars: Vec<char> = text.chars().collect();

        if pos >= chars.len() {
            return Ok(Value::Text(String::new()));
        }

        let result = if args.len() == 3 {
            let len = match &args[2] {
                Value::Integer(i) => *i as usize,
                Value::Float(f) => *f as usize,
                _ => return Ok(Value::Null),
            };
            chars.iter().skip(pos).take(len).collect::<String>()
        } else {
            chars.iter().skip(pos).collect::<String>()
        };

        Ok(Value::Text(result))
    }

    /// LOCATE(substr, str, pos) - returns position of substr in str
    fn locate(args: &[Value]) -> crate::Result<Value> {
        if args.len() < 2 || args.len() > 3 {
            return Err(YamlBaseError::TypeConversion(
                "LOCATE requires 2 or 3 arguments".to_string(),
            ));
        }

        let needle = match &args[0] {
            Value::Text(s) => s,
            Value::Null => return Ok(Value::Null),
            _ => return Ok(Value::Integer(0)),
        };

        let haystack = match &args[1] {
            Value::Text(s) => s,
            Value::Null => return Ok(Value::Null),
            _ => return Ok(Value::Integer(0)),
        };

        let start_pos = if args.len() == 3 {
            match &args[2] {
                Value::Integer(i) => (*i - 1) as usize, // MySQL is 1-indexed
                Value::Float(f) => (*f - 1.0) as usize,
                _ => 0,
            }
        } else {
            0
        };

        if let Some(pos) = haystack[start_pos..].find(needle) {
            Ok(Value::Integer((start_pos + pos + 1) as i64)) // Convert back to 1-indexed
        } else {
            Ok(Value::Integer(0))
        }
    }

    /// INSTR(str, substr) - returns position of substr in str (like LOCATE but with arguments swapped)
    fn instr(args: &[Value]) -> crate::Result<Value> {
        if args.len() != 2 {
            return Err(YamlBaseError::TypeConversion(
                "INSTR requires exactly 2 arguments".to_string(),
            ));
        }

        // Swap arguments and call locate
        Self::locate(&[args[1].clone(), args[0].clone()])
    }

    /// REVERSE(str) - returns reversed string
    fn reverse(args: &[Value]) -> crate::Result<Value> {
        if args.len() != 1 {
            return Err(YamlBaseError::TypeConversion(
                "REVERSE requires exactly 1 argument".to_string(),
            ));
        }

        let text = match &args[0] {
            Value::Text(s) => s,
            Value::Null => return Ok(Value::Null),
            _ => return Ok(Value::Text(args[0].to_string())),
        };

        let reversed: String = text.chars().rev().collect();
        Ok(Value::Text(reversed))
    }

    /// REPEAT(str, count) - returns string repeated count times
    fn repeat(args: &[Value]) -> crate::Result<Value> {
        if args.len() != 2 {
            return Err(YamlBaseError::TypeConversion(
                "REPEAT requires exactly 2 arguments".to_string(),
            ));
        }

        let text = match &args[0] {
            Value::Text(s) => s,
            Value::Null => return Ok(Value::Null),
            _ => return Ok(Value::Text(args[0].to_string())),
        };

        let count = match &args[1] {
            Value::Integer(i) => *i as usize,
            Value::Float(f) => *f as usize,
            _ => return Ok(Value::Null),
        };

        Ok(Value::Text(text.repeat(count)))
    }

    /// SPACE(N) - returns string of N spaces
    fn space(args: &[Value]) -> crate::Result<Value> {
        if args.len() != 1 {
            return Err(YamlBaseError::TypeConversion(
                "SPACE requires exactly 1 argument".to_string(),
            ));
        }

        let count = match &args[0] {
            Value::Integer(i) => *i as usize,
            Value::Float(f) => *f as usize,
            _ => return Ok(Value::Null),
        };

        Ok(Value::Text(" ".repeat(count)))
    }

    /// LPAD(str, len, padstr) - left-pad string to len characters with padstr
    fn lpad(args: &[Value]) -> crate::Result<Value> {
        if args.len() != 3 {
            return Err(YamlBaseError::TypeConversion(
                "LPAD requires exactly 3 arguments".to_string(),
            ));
        }

        let text = match &args[0] {
            Value::Text(s) => s.clone(),
            Value::Null => return Ok(Value::Null),
            _ => args[0].to_string(),
        };

        let target_len = match &args[1] {
            Value::Integer(i) => *i as usize,
            Value::Float(f) => *f as usize,
            _ => return Ok(Value::Null),
        };

        let pad_str = match &args[2] {
            Value::Text(s) => s,
            Value::Null => return Ok(Value::Null),
            _ => &args[2].to_string(),
        };

        if text.len() >= target_len {
            return Ok(Value::Text(text[..target_len].to_string()));
        }

        let needed = target_len - text.len();
        let padding = pad_str.repeat((needed / pad_str.len()) + 1);
        let result = format!("{}{}", &padding[..needed], text);

        Ok(Value::Text(result))
    }

    /// RPAD(str, len, padstr) - right-pad string to len characters with padstr
    fn rpad(args: &[Value]) -> crate::Result<Value> {
        if args.len() != 3 {
            return Err(YamlBaseError::TypeConversion(
                "RPAD requires exactly 3 arguments".to_string(),
            ));
        }

        let text = match &args[0] {
            Value::Text(s) => s.clone(),
            Value::Null => return Ok(Value::Null),
            _ => args[0].to_string(),
        };

        let target_len = match &args[1] {
            Value::Integer(i) => *i as usize,
            Value::Float(f) => *f as usize,
            _ => return Ok(Value::Null),
        };

        let pad_str = match &args[2] {
            Value::Text(s) => s,
            Value::Null => return Ok(Value::Null),
            _ => &args[2].to_string(),
        };

        if text.len() >= target_len {
            return Ok(Value::Text(text[..target_len].to_string()));
        }

        let needed = target_len - text.len();
        let padding = pad_str.repeat((needed / pad_str.len()) + 1);
        let result = format!("{}{}", text, &padding[..needed]);

        Ok(Value::Text(result))
    }

    /// STRCMP(str1, str2) - compare strings (-1, 0, 1)
    fn strcmp(args: &[Value]) -> crate::Result<Value> {
        if args.len() != 2 {
            return Err(YamlBaseError::TypeConversion(
                "STRCMP requires exactly 2 arguments".to_string(),
            ));
        }

        let str1 = match &args[0] {
            Value::Text(s) => s,
            Value::Null => return Ok(Value::Null),
            _ => &args[0].to_string(),
        };

        let str2 = match &args[1] {
            Value::Text(s) => s,
            Value::Null => return Ok(Value::Null),
            _ => &args[1].to_string(),
        };

        let result = match str1.cmp(str2) {
            std::cmp::Ordering::Less => -1,
            std::cmp::Ordering::Equal => 0,
            std::cmp::Ordering::Greater => 1,
        };

        Ok(Value::Integer(result))
    }

    /// CHAR_LENGTH/CHARACTER_LENGTH(str) - returns character length
    fn char_length(args: &[Value]) -> crate::Result<Value> {
        if args.len() != 1 {
            return Err(YamlBaseError::TypeConversion(
                "CHAR_LENGTH requires exactly 1 argument".to_string(),
            ));
        }

        let text = match &args[0] {
            Value::Text(s) => s,
            Value::Null => return Ok(Value::Null),
            _ => return Ok(Value::Integer(args[0].to_string().chars().count() as i64)),
        };

        Ok(Value::Integer(text.chars().count() as i64))
    }

    /// BIT_LENGTH(str) - returns bit length
    fn bit_length(args: &[Value]) -> crate::Result<Value> {
        if args.len() != 1 {
            return Err(YamlBaseError::TypeConversion(
                "BIT_LENGTH requires exactly 1 argument".to_string(),
            ));
        }

        let text = match &args[0] {
            Value::Text(s) => s.clone(),
            Value::Null => return Ok(Value::Null),
            _ => args[0].to_string(),
        };

        Ok(Value::Integer((text.len() * 8) as i64))
    }

    /// OCT(N) - returns octal representation
    fn oct(args: &[Value]) -> crate::Result<Value> {
        if args.len() != 1 {
            return Err(YamlBaseError::TypeConversion(
                "OCT requires exactly 1 argument".to_string(),
            ));
        }

        let num = match &args[0] {
            Value::Integer(i) => *i,
            Value::Float(f) => *f as i64,
            _ => return Ok(Value::Null),
        };

        Ok(Value::Text(format!("{:o}", num)))
    }

    /// HEX(N) - returns hexadecimal representation
    fn hex(args: &[Value]) -> crate::Result<Value> {
        if args.len() != 1 {
            return Err(YamlBaseError::TypeConversion(
                "HEX requires exactly 1 argument".to_string(),
            ));
        }

        match &args[0] {
            Value::Integer(i) => Ok(Value::Text(format!("{:X}", i))),
            Value::Float(f) => Ok(Value::Text(format!("{:X}", *f as i64))),
            Value::Text(s) => {
                let hex: String = s.bytes().map(|b| format!("{:02X}", b)).collect();
                Ok(Value::Text(hex))
            }
            _ => Ok(Value::Null),
        }
    }

    /// UNHEX(str) - converts hex string to binary
    fn unhex(args: &[Value]) -> crate::Result<Value> {
        if args.len() != 1 {
            return Err(YamlBaseError::TypeConversion(
                "UNHEX requires exactly 1 argument".to_string(),
            ));
        }

        let hex_str = match &args[0] {
            Value::Text(s) => s,
            _ => return Ok(Value::Null),
        };

        if hex_str.len() % 2 != 0 {
            return Ok(Value::Null);
        }

        let mut result = Vec::new();
        for chunk in hex_str.chars().collect::<Vec<_>>().chunks(2) {
            let hex_byte: String = chunk.iter().collect();
            if let Ok(byte) = u8::from_str_radix(&hex_byte, 16) {
                result.push(byte);
            } else {
                return Ok(Value::Null);
            }
        }

        match String::from_utf8(result) {
            Ok(s) => Ok(Value::Text(s)),
            Err(_) => Ok(Value::Null),
        }
    }

    /// BIN(N) - returns binary representation
    fn bin(args: &[Value]) -> crate::Result<Value> {
        if args.len() != 1 {
            return Err(YamlBaseError::TypeConversion(
                "BIN requires exactly 1 argument".to_string(),
            ));
        }

        let num = match &args[0] {
            Value::Integer(i) => *i,
            Value::Float(f) => *f as i64,
            _ => return Ok(Value::Null),
        };

        Ok(Value::Text(format!("{:b}", num)))
    }

    /// CONV(N, from_base, to_base) - converts number between bases
    fn conv(args: &[Value]) -> crate::Result<Value> {
        if args.len() != 3 {
            return Err(YamlBaseError::TypeConversion(
                "CONV requires exactly 3 arguments".to_string(),
            ));
        }

        let num_str = match &args[0] {
            Value::Text(s) => s,
            Value::Integer(i) => &i.to_string(),
            Value::Float(f) => &((*f as i64).to_string()),
            _ => return Ok(Value::Null),
        };

        let from_base = match &args[1] {
            Value::Integer(i) => *i as u32,
            Value::Float(f) => *f as u32,
            _ => return Ok(Value::Null),
        };

        let to_base = match &args[2] {
            Value::Integer(i) => *i as u32,
            Value::Float(f) => *f as u32,
            _ => return Ok(Value::Null),
        };

        if !(2..=36).contains(&from_base) || !(2..=36).contains(&to_base) {
            return Ok(Value::Null);
        }

        if let Ok(num) = i64::from_str_radix(num_str, from_base) {
            let result = match to_base {
                2 => format!("{:b}", num),
                8 => format!("{:o}", num),
                16 => format!("{:X}", num),
                _ => {
                    // Generic base conversion
                    if num == 0 {
                        "0".to_string()
                    } else {
                        let mut result = String::new();
                        let mut n = num.abs();
                        while n > 0 {
                            let digit = (n % to_base as i64) as u8;
                            let ch = if digit < 10 {
                                (b'0' + digit) as char
                            } else {
                                (b'A' + digit - 10) as char
                            };
                            result.insert(0, ch);
                            n /= to_base as i64;
                        }
                        if num < 0 {
                            result.insert(0, '-');
                        }
                        result
                    }
                }
            };
            Ok(Value::Text(result))
        } else {
            Ok(Value::Null)
        }
    }

    /// FORMAT(X, D) - formats number with D decimal places
    fn format(args: &[Value]) -> crate::Result<Value> {
        if args.len() != 2 {
            return Err(YamlBaseError::TypeConversion(
                "FORMAT requires exactly 2 arguments".to_string(),
            ));
        }

        let num = match &args[0] {
            Value::Integer(i) => *i as f64,
            Value::Float(f) => *f as f64,
            _ => return Ok(Value::Null),
        };

        let decimals = match &args[1] {
            Value::Integer(i) => *i as usize,
            Value::Float(f) => *f as usize,
            _ => return Ok(Value::Null),
        };

        Ok(Value::Text(format!("{:.prec$}", num, prec = decimals)))
    }

    /// INET_ATON(expr) - converts IP address to number
    fn inet_aton(args: &[Value]) -> crate::Result<Value> {
        if args.len() != 1 {
            return Err(YamlBaseError::TypeConversion(
                "INET_ATON requires exactly 1 argument".to_string(),
            ));
        }

        let ip_str = match &args[0] {
            Value::Text(s) => s,
            _ => return Ok(Value::Null),
        };

        let parts: Result<Vec<u32>, _> = ip_str.split('.').map(|s| s.parse()).collect();
        if let Ok(parts) = parts {
            if parts.len() == 4 && parts.iter().all(|&p| p <= 255) {
                let result = (parts[0] << 24) + (parts[1] << 16) + (parts[2] << 8) + parts[3];
                return Ok(Value::Integer(result as i64));
            }
        }

        Ok(Value::Null)
    }

    /// INET_NTOA(expr) - converts number to IP address
    fn inet_ntoa(args: &[Value]) -> crate::Result<Value> {
        if args.len() != 1 {
            return Err(YamlBaseError::TypeConversion(
                "INET_NTOA requires exactly 1 argument".to_string(),
            ));
        }

        let num = match &args[0] {
            Value::Integer(i) => *i as u32,
            Value::Float(f) => *f as u32,
            _ => return Ok(Value::Null),
        };

        let a = (num >> 24) & 0xFF;
        let b = (num >> 16) & 0xFF;
        let c = (num >> 8) & 0xFF;
        let d = num & 0xFF;

        Ok(Value::Text(format!("{}.{}.{}.{}", a, b, c, d)))
    }

    /// CONNECTION_ID() - returns connection ID
    fn connection_id(_args: &[Value]) -> crate::Result<Value> {
        Ok(Value::Integer(1)) // Always return 1 for simplicity
    }

    /// DATABASE()/SCHEMA() - returns current database name
    fn database(_args: &[Value]) -> crate::Result<Value> {
        Ok(Value::Text("yamlbase".to_string())) // Return default database name
    }

    /// USER() - returns current user
    fn user(_args: &[Value]) -> crate::Result<Value> {
        Ok(Value::Text("yamlbase@localhost".to_string()))
    }

    /// VERSION() - returns server version
    fn version(_args: &[Value]) -> crate::Result<Value> {
        Ok(Value::Text("8.0.35-yamlbase".to_string()))
    }
}

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

    #[test]
    fn test_ifnull() {
        let result =
            MySqlFunctions::ifnull(&[Value::Null, Value::Text("default".to_string())]).unwrap();
        assert_eq!(result, Value::Text("default".to_string()));

        let result = MySqlFunctions::ifnull(&[
            Value::Text("value".to_string()),
            Value::Text("default".to_string()),
        ])
        .unwrap();
        assert_eq!(result, Value::Text("value".to_string()));
    }

    #[test]
    fn test_find_in_set() {
        let result = MySqlFunctions::find_in_set(&[
            Value::Text("b".to_string()),
            Value::Text("a,b,c".to_string()),
        ])
        .unwrap();
        assert_eq!(result, Value::Integer(2));

        let result = MySqlFunctions::find_in_set(&[
            Value::Text("x".to_string()),
            Value::Text("a,b,c".to_string()),
        ])
        .unwrap();
        assert_eq!(result, Value::Integer(0));
    }

    #[test]
    fn test_left_right() {
        let result =
            MySqlFunctions::left(&[Value::Text("hello".to_string()), Value::Integer(2)]).unwrap();
        assert_eq!(result, Value::Text("he".to_string()));

        let result =
            MySqlFunctions::right(&[Value::Text("hello".to_string()), Value::Integer(2)]).unwrap();
        assert_eq!(result, Value::Text("lo".to_string()));
    }

    #[test]
    fn test_hex_unhex() {
        let result = MySqlFunctions::hex(&[Value::Text("ABC".to_string())]).unwrap();
        assert_eq!(result, Value::Text("414243".to_string()));

        let result = MySqlFunctions::unhex(&[Value::Text("414243".to_string())]).unwrap();
        assert_eq!(result, Value::Text("ABC".to_string()));
    }
}