sql-cli 1.67.1

use anyhow::{anyhow, Result};
use chrono::{DateTime, Datelike, NaiveDate, NaiveDateTime, TimeZone, Utc};

use super::{ArgCount, FunctionCategory, FunctionSignature, SqlFunction};
use crate::config::global::get_date_notation;
use crate::data::datatable::DataValue;

// Helper function for parsing dates with multiple format support
pub fn parse_datetime(s: &str) -> Result<DateTime<Utc>> {
    // Try parsing as ISO 8601 with timezone first (most unambiguous)
    if let Ok(dt) = s.parse::<DateTime<Utc>>() {
        return Ok(dt);
    }

    // FIX Protocol timestamp format: YYYYMMDD-HH:MM:SS.sss (UTC)
    // This is a standard financial messaging format (tag 60, 52, etc.)
    if let Ok(dt) = NaiveDateTime::parse_from_str(s, "%Y%m%d-%H:%M:%S%.3f") {
        return Ok(Utc.from_utc_datetime(&dt));
    }
    // FIX format without milliseconds
    if let Ok(dt) = NaiveDateTime::parse_from_str(s, "%Y%m%d-%H:%M:%S") {
        return Ok(Utc.from_utc_datetime(&dt));
    }

    // ISO formats (most common and unambiguous)
    // With T separator
    if let Ok(dt) = NaiveDateTime::parse_from_str(s, "%Y-%m-%dT%H:%M:%S") {
        return Ok(Utc.from_utc_datetime(&dt));
    }
    if let Ok(dt) = NaiveDateTime::parse_from_str(s, "%Y-%m-%dT%H:%M:%S%.3f") {
        return Ok(Utc.from_utc_datetime(&dt));
    }
    // With space separator
    if let Ok(dt) = NaiveDateTime::parse_from_str(s, "%Y-%m-%d %H:%M:%S%.3f") {
        return Ok(Utc.from_utc_datetime(&dt));
    }
    if let Ok(dt) = NaiveDateTime::parse_from_str(s, "%Y-%m-%d %H:%M:%S%.f") {
        return Ok(Utc.from_utc_datetime(&dt));
    }
    // Without milliseconds
    if let Ok(dt) = NaiveDateTime::parse_from_str(s, "%Y-%m-%d %H:%M:%S") {
        return Ok(Utc.from_utc_datetime(&dt));
    }
    if let Ok(dt) = NaiveDate::parse_from_str(s, "%Y-%m-%d") {
        return Ok(Utc.from_utc_datetime(&dt.and_hms_opt(0, 0, 0).unwrap()));
    }

    // Get date notation preference
    let date_notation = get_date_notation();

    // Date notation preference for ambiguous formats like 04/09/2025
    if date_notation == "european" {
        // European formats (DD/MM/YYYY) - try first
        // Date only formats
        if let Ok(dt) = NaiveDate::parse_from_str(s, "%d/%m/%Y") {
            return Ok(Utc.from_utc_datetime(&dt.and_hms_opt(0, 0, 0).unwrap()));
        }
        if let Ok(dt) = NaiveDate::parse_from_str(s, "%d-%m-%Y") {
            return Ok(Utc.from_utc_datetime(&dt.and_hms_opt(0, 0, 0).unwrap()));
        }
        // With time formats (with milliseconds)
        if let Ok(dt) = NaiveDateTime::parse_from_str(s, "%d/%m/%Y %H:%M:%S%.3f") {
            return Ok(Utc.from_utc_datetime(&dt));
        }
        if let Ok(dt) = NaiveDateTime::parse_from_str(s, "%d/%m/%Y %H:%M:%S") {
            return Ok(Utc.from_utc_datetime(&dt));
        }
        if let Ok(dt) = NaiveDateTime::parse_from_str(s, "%d-%m-%Y %H:%M:%S") {
            return Ok(Utc.from_utc_datetime(&dt));
        }

        // US formats (MM/DD/YYYY) - fallback
        // Date only formats
        if let Ok(dt) = NaiveDate::parse_from_str(s, "%m/%d/%Y") {
            return Ok(Utc.from_utc_datetime(&dt.and_hms_opt(0, 0, 0).unwrap()));
        }
        if let Ok(dt) = NaiveDate::parse_from_str(s, "%m-%d-%Y") {
            return Ok(Utc.from_utc_datetime(&dt.and_hms_opt(0, 0, 0).unwrap()));
        }
        // With time formats (with milliseconds)
        if let Ok(dt) = NaiveDateTime::parse_from_str(s, "%m/%d/%Y %H:%M:%S%.3f") {
            return Ok(Utc.from_utc_datetime(&dt));
        }
        if let Ok(dt) = NaiveDateTime::parse_from_str(s, "%m/%d/%Y %H:%M:%S") {
            return Ok(Utc.from_utc_datetime(&dt));
        }
        if let Ok(dt) = NaiveDateTime::parse_from_str(s, "%m-%d-%Y %H:%M:%S") {
            return Ok(Utc.from_utc_datetime(&dt));
        }
    } else {
        // US formats (MM/DD/YYYY) - default, try first
        // Date only formats
        if let Ok(dt) = NaiveDate::parse_from_str(s, "%m/%d/%Y") {
            return Ok(Utc.from_utc_datetime(&dt.and_hms_opt(0, 0, 0).unwrap()));
        }
        if let Ok(dt) = NaiveDate::parse_from_str(s, "%m-%d-%Y") {
            return Ok(Utc.from_utc_datetime(&dt.and_hms_opt(0, 0, 0).unwrap()));
        }
        // With time formats (with milliseconds)
        if let Ok(dt) = NaiveDateTime::parse_from_str(s, "%m/%d/%Y %H:%M:%S%.3f") {
            return Ok(Utc.from_utc_datetime(&dt));
        }
        if let Ok(dt) = NaiveDateTime::parse_from_str(s, "%m/%d/%Y %H:%M:%S") {
            return Ok(Utc.from_utc_datetime(&dt));
        }
        if let Ok(dt) = NaiveDateTime::parse_from_str(s, "%m-%d-%Y %H:%M:%S") {
            return Ok(Utc.from_utc_datetime(&dt));
        }

        // European formats (DD/MM/YYYY) - fallback
        // Date only formats
        if let Ok(dt) = NaiveDate::parse_from_str(s, "%d/%m/%Y") {
            return Ok(Utc.from_utc_datetime(&dt.and_hms_opt(0, 0, 0).unwrap()));
        }
        if let Ok(dt) = NaiveDate::parse_from_str(s, "%d-%m-%Y") {
            return Ok(Utc.from_utc_datetime(&dt.and_hms_opt(0, 0, 0).unwrap()));
        }
        // With time formats (with milliseconds)
        if let Ok(dt) = NaiveDateTime::parse_from_str(s, "%d/%m/%Y %H:%M:%S%.3f") {
            return Ok(Utc.from_utc_datetime(&dt));
        }
        if let Ok(dt) = NaiveDateTime::parse_from_str(s, "%d/%m/%Y %H:%M:%S") {
            return Ok(Utc.from_utc_datetime(&dt));
        }
        if let Ok(dt) = NaiveDateTime::parse_from_str(s, "%d-%m-%Y %H:%M:%S") {
            return Ok(Utc.from_utc_datetime(&dt));
        }
    }

    // Excel/Windows format: DD-MMM-YYYY (e.g., 15-Jan-2024)
    if let Ok(dt) = NaiveDate::parse_from_str(s, "%d-%b-%Y") {
        return Ok(Utc.from_utc_datetime(&dt.and_hms_opt(0, 0, 0).unwrap()));
    }

    // Full month names: January 15, 2024 or 15 January 2024
    if let Ok(dt) = NaiveDate::parse_from_str(s, "%B %d, %Y") {
        return Ok(Utc.from_utc_datetime(&dt.and_hms_opt(0, 0, 0).unwrap()));
    }
    if let Ok(dt) = NaiveDate::parse_from_str(s, "%d %B %Y") {
        return Ok(Utc.from_utc_datetime(&dt.and_hms_opt(0, 0, 0).unwrap()));
    }

    // RFC3339 (e.g., 2024-01-15T10:30:00Z)
    if let Ok(dt) = DateTime::parse_from_rfc3339(s) {
        return Ok(dt.with_timezone(&Utc));
    }

    Err(anyhow!("Could not parse date: {}. Supported formats: YYYYMMDD-HH:MM:SS.sss (FIX), YYYY-MM-DD, MM/DD/YYYY, DD/MM/YYYY, DD-MMM-YYYY, Month DD YYYY", s))
}

/// NOW function - Returns current datetime
pub struct NowFunction;

impl SqlFunction for NowFunction {
    fn signature(&self) -> FunctionSignature {
        FunctionSignature {
            name: "NOW",
            category: FunctionCategory::Date,
            arg_count: ArgCount::Fixed(0),
            description: "Returns the current date and time",
            returns: "DATETIME",
            examples: vec![
                "SELECT NOW()",
                "SELECT * FROM orders WHERE created_at > NOW() - 7",
            ],
        }
    }

    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
        self.validate_args(args)?;
        let now = Utc::now();
        Ok(DataValue::DateTime(
            now.format("%Y-%m-%d %H:%M:%S").to_string(),
        ))
    }
}

/// TODAY function - Returns current date
pub struct TodayFunction;

impl SqlFunction for TodayFunction {
    fn signature(&self) -> FunctionSignature {
        FunctionSignature {
            name: "TODAY",
            category: FunctionCategory::Date,
            arg_count: ArgCount::Fixed(0),
            description: "Returns today's date",
            returns: "DATE",
            examples: vec![
                "SELECT TODAY()",
                "SELECT * FROM events WHERE event_date = TODAY()",
            ],
        }
    }

    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
        self.validate_args(args)?;
        let today = Utc::now().date_naive();
        Ok(DataValue::String(today.format("%Y-%m-%d").to_string()))
    }
}

/// DATEDIFF function - Calculate difference between dates
pub struct DateDiffFunction;

impl SqlFunction for DateDiffFunction {
    fn signature(&self) -> FunctionSignature {
        FunctionSignature {
            name: "DATEDIFF",
            category: FunctionCategory::Date,
            arg_count: ArgCount::Fixed(3),
            description: "Calculate the difference between two dates in the specified unit",
            returns: "INTEGER",
            examples: vec![
                "SELECT DATEDIFF('day', '2024-01-01', '2024-01-15')",
                "SELECT DATEDIFF('month', start_date, end_date) FROM projects",
                "SELECT DATEDIFF('year', birth_date, TODAY()) as age",
            ],
        }
    }

    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
        self.validate_args(args)?;

        // First argument: unit
        let unit = match &args[0] {
            DataValue::String(s) => s.to_lowercase(),
            DataValue::InternedString(s) => s.to_lowercase(),
            _ => return Err(anyhow!("DATEDIFF unit must be a string")),
        };

        // Second argument: date1
        let date1 = match &args[1] {
            DataValue::String(s) | DataValue::DateTime(s) => parse_datetime(s)?,
            DataValue::InternedString(s) => parse_datetime(s.as_str())?,
            DataValue::Null => return Ok(DataValue::Null),
            _ => return Err(anyhow!("DATEDIFF requires date/datetime values")),
        };

        // Third argument: date2
        let date2 = match &args[2] {
            DataValue::String(s) | DataValue::DateTime(s) => parse_datetime(s)?,
            DataValue::InternedString(s) => parse_datetime(s.as_str())?,
            DataValue::Null => return Ok(DataValue::Null),
            _ => return Err(anyhow!("DATEDIFF requires date/datetime values")),
        };

        // Calculate difference based on unit
        let diff = match unit.as_str() {
            "day" | "days" => {
                let duration = date2.signed_duration_since(date1);
                duration.num_days()
            }
            "month" | "months" => {
                // Approximate months as 30.44 days
                let duration = date2.signed_duration_since(date1);
                duration.num_days() / 30
            }
            "year" | "years" => {
                // Approximate years as 365.25 days
                let duration = date2.signed_duration_since(date1);
                duration.num_days() / 365
            }
            "hour" | "hours" => {
                let duration = date2.signed_duration_since(date1);
                duration.num_hours()
            }
            "minute" | "minutes" => {
                let duration = date2.signed_duration_since(date1);
                duration.num_minutes()
            }
            "second" | "seconds" => {
                let duration = date2.signed_duration_since(date1);
                duration.num_seconds()
            }
            "millisecond" | "milliseconds" | "ms" => {
                let duration = date2.signed_duration_since(date1);
                duration.num_milliseconds()
            }
            "microsecond" | "microseconds" | "us" => {
                let duration = date2.signed_duration_since(date1);
                duration.num_microseconds().unwrap_or(0)
            }
            _ => {
                return Err(anyhow!(
                    "Unknown DATEDIFF unit: {}. Use: day, month, year, hour, minute, second, millisecond, microsecond",
                    unit
                ))
            }
        };

        Ok(DataValue::Integer(diff))
    }
}

/// DATEADD function - Add interval to date
pub struct DateAddFunction;

impl SqlFunction for DateAddFunction {
    fn signature(&self) -> FunctionSignature {
        FunctionSignature {
            name: "DATEADD",
            category: FunctionCategory::Date,
            arg_count: ArgCount::Fixed(3),
            description: "Add a specified interval to a date",
            returns: "DATETIME",
            examples: vec![
                "SELECT DATEADD('day', 7, '2024-01-01')",
                "SELECT DATEADD('month', -1, NOW())",
                "SELECT DATEADD('year', 1, hire_date) FROM employees",
            ],
        }
    }

    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
        self.validate_args(args)?;

        // First argument: unit
        let unit = match &args[0] {
            DataValue::String(s) => s.to_lowercase(),
            DataValue::InternedString(s) => s.to_lowercase(),
            _ => return Err(anyhow!("DATEADD unit must be a string")),
        };

        // Second argument: amount to add
        let amount = match &args[1] {
            DataValue::Integer(i) => *i,
            DataValue::Float(f) => *f as i64,
            DataValue::Null => return Ok(DataValue::Null),
            _ => return Err(anyhow!("DATEADD amount must be a number")),
        };

        // Third argument: base date
        let base_date = match &args[2] {
            DataValue::String(s) | DataValue::DateTime(s) => parse_datetime(s)?,
            DataValue::InternedString(s) => parse_datetime(s.as_str())?,
            DataValue::Null => return Ok(DataValue::Null),
            _ => return Err(anyhow!("DATEADD requires date/datetime values")),
        };

        // Add the specified amount based on unit
        let result_date = match unit.as_str() {
            "day" | "days" => base_date + chrono::Duration::days(amount),
            "month" | "months" => {
                // For months, we need to be careful about month boundaries
                let naive = base_date.naive_utc();
                let mut year = naive.year();
                let mut month = naive.month() as i32;
                let day = naive.day();

                month += amount as i32;

                // Handle month overflow/underflow
                while month > 12 {
                    month -= 12;
                    year += 1;
                }
                while month < 1 {
                    month += 12;
                    year -= 1;
                }

                // Create new date, handling day overflow (e.g., Jan 31 + 1 month = Feb 28/29)
                let target_date =
                    NaiveDate::from_ymd_opt(year, month as u32, day).unwrap_or_else(|| {
                        // If day doesn't exist in target month, use the last day of that month
                        // Try decreasing days until we find a valid one
                        for test_day in (1..=day).rev() {
                            if let Some(date) =
                                NaiveDate::from_ymd_opt(year, month as u32, test_day)
                            {
                                return date;
                            }
                        }
                        NaiveDate::from_ymd_opt(year, month as u32, 1).unwrap()
                    });

                Utc.from_utc_datetime(&target_date.and_time(base_date.naive_utc().time()))
            }
            "year" | "years" => {
                let naive = base_date.naive_utc();
                let new_year = naive.year() + amount as i32;
                let month = naive.month();
                let day = naive.day();

                // Handle leap year edge case (Feb 29 -> Feb 28 in non-leap year)
                let target_date =
                    NaiveDate::from_ymd_opt(new_year, month, day).unwrap_or_else(|| {
                        // If the date doesn't exist (e.g., Feb 29 in non-leap year), use Feb 28
                        NaiveDate::from_ymd_opt(new_year, month, day - 1).unwrap()
                    });

                Utc.from_utc_datetime(&target_date.and_time(base_date.naive_utc().time()))
            }
            "hour" | "hours" => base_date + chrono::Duration::hours(amount),
            "minute" | "minutes" => base_date + chrono::Duration::minutes(amount),
            "second" | "seconds" => base_date + chrono::Duration::seconds(amount),
            _ => {
                return Err(anyhow!(
                    "Unknown DATEADD unit: {}. Use: day, month, year, hour, minute, second",
                    unit
                ))
            }
        };

        // Return as datetime string
        Ok(DataValue::DateTime(
            result_date.format("%Y-%m-%d %H:%M:%S").to_string(),
        ))
    }
}

/// UNIX_TIMESTAMP - Convert datetime to Unix epoch timestamp
pub struct UnixTimestamp;

impl SqlFunction for UnixTimestamp {
    fn signature(&self) -> FunctionSignature {
        FunctionSignature {
            name: "UNIX_TIMESTAMP",
            category: FunctionCategory::Date,
            arg_count: ArgCount::Fixed(1),
            description:
                "Convert datetime to Unix epoch timestamp (seconds since 1970-01-01 00:00:00 UTC)",
            returns: "INTEGER (seconds since epoch)",
            examples: vec![
                "SELECT UNIX_TIMESTAMP('2024-01-01 00:00:00')",
                "SELECT UNIX_TIMESTAMP('2024-01-01T12:30:45')",
                "SELECT UNIX_TIMESTAMP(trade_time) FROM trades",
            ],
        }
    }

    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
        if args.len() != 1 {
            return Err(anyhow!("UNIX_TIMESTAMP expects exactly 1 argument"));
        }

        match &args[0] {
            DataValue::DateTime(dt_str) | DataValue::String(dt_str) => {
                let dt = parse_datetime(dt_str)?;
                Ok(DataValue::Integer(dt.timestamp()))
            }
            DataValue::InternedString(dt_str) => {
                let dt = parse_datetime(dt_str)?;
                Ok(DataValue::Integer(dt.timestamp()))
            }
            DataValue::Null => Ok(DataValue::Null),
            _ => Err(anyhow!(
                "UNIX_TIMESTAMP expects a datetime or string argument"
            )),
        }
    }
}

/// FROM_UNIXTIME - Convert Unix epoch timestamp to datetime
pub struct FromUnixTime;

impl SqlFunction for FromUnixTime {
    fn signature(&self) -> FunctionSignature {
        FunctionSignature {
            name: "FROM_UNIXTIME",
            category: FunctionCategory::Date,
            arg_count: ArgCount::Fixed(1),
            description: "Convert Unix epoch timestamp to datetime string",
            returns: "DATETIME string in ISO format",
            examples: vec![
                "SELECT FROM_UNIXTIME(1704067200)",
                "SELECT FROM_UNIXTIME(timestamp_col) FROM data",
            ],
        }
    }

    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
        if args.len() != 1 {
            return Err(anyhow!("FROM_UNIXTIME expects exactly 1 argument"));
        }

        match &args[0] {
            DataValue::Integer(timestamp) => {
                let dt = DateTime::<Utc>::from_timestamp(*timestamp, 0)
                    .ok_or_else(|| anyhow!("Invalid timestamp: {}", timestamp))?;
                Ok(DataValue::DateTime(
                    dt.format("%Y-%m-%d %H:%M:%S").to_string(),
                ))
            }
            DataValue::Float(timestamp) => {
                let secs = timestamp.floor() as i64;
                let nanos = ((timestamp - timestamp.floor()) * 1_000_000_000.0) as u32;
                let dt = DateTime::<Utc>::from_timestamp(secs, nanos)
                    .ok_or_else(|| anyhow!("Invalid timestamp: {}", timestamp))?;
                Ok(DataValue::DateTime(
                    dt.format("%Y-%m-%d %H:%M:%S%.f").to_string(),
                ))
            }
            DataValue::Null => Ok(DataValue::Null),
            _ => Err(anyhow!("FROM_UNIXTIME expects a numeric timestamp")),
        }
    }
}

/// TIME_BUCKET - Round timestamps down to bucket boundaries
pub struct TimeBucket;

impl SqlFunction for TimeBucket {
    fn signature(&self) -> FunctionSignature {
        FunctionSignature {
            name: "TIME_BUCKET",
            category: FunctionCategory::Date,
            arg_count: ArgCount::Fixed(2),
            description: "Round timestamp down to bucket boundary (for time-based grouping)",
            returns: "INTEGER (bucket timestamp)",
            examples: vec![
                "SELECT TIME_BUCKET(300, UNIX_TIMESTAMP(trade_time)) as bucket FROM trades -- 5 minute buckets",
                "SELECT TIME_BUCKET(3600, UNIX_TIMESTAMP(trade_time)) as hour FROM trades -- 1 hour buckets",
                "SELECT TIME_BUCKET(60, timestamp_col) as minute FROM data -- 1 minute buckets",
            ],
        }
    }

    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
        if args.len() != 2 {
            return Err(anyhow!(
                "TIME_BUCKET expects exactly 2 arguments: bucket_size, timestamp"
            ));
        }

        let bucket_size = match &args[0] {
            DataValue::Integer(size) => *size,
            DataValue::Float(size) => *size as i64,
            _ => return Err(anyhow!("TIME_BUCKET bucket_size must be numeric")),
        };

        if bucket_size <= 0 {
            return Err(anyhow!("TIME_BUCKET bucket_size must be positive"));
        }

        match &args[1] {
            DataValue::Integer(timestamp) => {
                let bucket = (timestamp / bucket_size) * bucket_size;
                Ok(DataValue::Integer(bucket))
            }
            DataValue::Float(timestamp) => {
                let ts = *timestamp as i64;
                let bucket = (ts / bucket_size) * bucket_size;
                Ok(DataValue::Integer(bucket))
            }
            DataValue::Null => Ok(DataValue::Null),
            _ => Err(anyhow!("TIME_BUCKET timestamp must be numeric")),
        }
    }
}

/// DAYOFWEEK function - returns day of week as number (0=Sunday, 6=Saturday)
pub struct DayOfWeekFunction;

impl SqlFunction for DayOfWeekFunction {
    fn signature(&self) -> FunctionSignature {
        FunctionSignature {
            name: "DAYOFWEEK",
            category: FunctionCategory::Date,
            arg_count: ArgCount::Fixed(1),
            description: "Returns day of week as number (0=Sunday, 6=Saturday)",
            returns: "INTEGER",
            examples: vec![
                "SELECT DAYOFWEEK('2024-01-01')", // Returns 1 (Monday)
                "SELECT DAYOFWEEK(NOW())",
                "SELECT DAYOFWEEK(date_column) FROM table",
            ],
        }
    }

    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
        if args.len() != 1 {
            return Err(anyhow!("DAYOFWEEK expects exactly 1 argument"));
        }

        let date_str = match &args[0] {
            DataValue::String(s) | DataValue::DateTime(s) => s.as_str(),
            DataValue::InternedString(s) => s.as_str(),
            DataValue::Null => return Ok(DataValue::Null),
            _ => return Err(anyhow!("DAYOFWEEK expects a date/datetime string")),
        };

        let dt = parse_datetime(date_str)?;
        // chrono weekday: Mon=0, Tue=1, ..., Sun=6
        // We want: Sun=0, Mon=1, ..., Sat=6
        let chrono_weekday = dt.weekday().num_days_from_monday(); // 0=Mon, 6=Sun
        let our_weekday = (chrono_weekday + 1) % 7; // Convert to 0=Sun, 6=Sat

        Ok(DataValue::Integer(our_weekday as i64))
    }
}

/// DAYNAME function - returns full day name (Monday, Tuesday, etc.)
pub struct DayNameFunction;

impl SqlFunction for DayNameFunction {
    fn signature(&self) -> FunctionSignature {
        FunctionSignature {
            name: "DAYNAME",
            category: FunctionCategory::Date,
            arg_count: ArgCount::Range(1, 2),
            description: "Returns day name. Optional second arg: 'full' (default) or 'short'",
            returns: "STRING",
            examples: vec![
                "SELECT DAYNAME('2024-01-01')",          // Returns 'Monday'
                "SELECT DAYNAME('2024-01-01', 'short')", // Returns 'Mon'
                "SELECT DAYNAME(NOW(), 'full')",
            ],
        }
    }

    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
        if args.is_empty() || args.len() > 2 {
            return Err(anyhow!("DAYNAME expects 1 or 2 arguments"));
        }

        let date_str = match &args[0] {
            DataValue::String(s) | DataValue::DateTime(s) => s.as_str(),
            DataValue::InternedString(s) => s.as_str(),
            DataValue::Null => return Ok(DataValue::Null),
            _ => return Err(anyhow!("DAYNAME expects a date/datetime string")),
        };

        let format = if args.len() == 2 {
            match &args[1] {
                DataValue::String(s) => s.as_str(),
                DataValue::InternedString(s) => s.as_str(),
                DataValue::Null => "full",
                _ => return Err(anyhow!("DAYNAME format must be 'full' or 'short'")),
            }
        } else {
            "full"
        };

        let dt = parse_datetime(date_str)?;

        let day_name = match format {
            "short" => dt.format("%a").to_string(), // Mon, Tue, Wed, etc.
            "full" | _ => dt.format("%A").to_string(), // Monday, Tuesday, etc.
        };

        Ok(DataValue::String(day_name))
    }
}

/// ISLEAPYEAR function - returns true if the year is a leap year
pub struct IsLeapYearFunction;

impl SqlFunction for IsLeapYearFunction {
    fn signature(&self) -> FunctionSignature {
        FunctionSignature {
            name: "ISLEAPYEAR",
            category: FunctionCategory::Date,
            arg_count: ArgCount::Fixed(1),
            description: "Returns true if the year is a leap year",
            returns: "BOOLEAN",
            examples: vec![
                "SELECT ISLEAPYEAR('2024-01-01')", // Returns true
                "SELECT ISLEAPYEAR(2024)",         // Returns true
                "SELECT ISLEAPYEAR(2023)",         // Returns false
            ],
        }
    }

    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
        if args.len() != 1 {
            return Err(anyhow!("ISLEAPYEAR expects exactly 1 argument"));
        }

        let year = match &args[0] {
            DataValue::Integer(y) => *y as i32,
            DataValue::Float(f) => *f as i32,
            DataValue::String(s) | DataValue::DateTime(s) => {
                // Try to parse as a year number first
                if let Ok(y) = s.parse::<i32>() {
                    y
                } else {
                    // Try to parse as a date
                    let dt = parse_datetime(s.as_str())?;
                    dt.year()
                }
            }
            DataValue::InternedString(s) => {
                // Try to parse as a year number first
                if let Ok(y) = s.parse::<i32>() {
                    y
                } else {
                    // Try to parse as a date
                    let dt = parse_datetime(s.as_str())?;
                    dt.year()
                }
            }
            DataValue::Null => return Ok(DataValue::Null),
            _ => return Err(anyhow!("ISLEAPYEAR expects a year number or date")),
        };

        // Leap year calculation
        let is_leap = (year % 4 == 0 && year % 100 != 0) || (year % 400 == 0);

        Ok(DataValue::Boolean(is_leap))
    }
}

/// WEEKOFYEAR function - returns the ISO week number (1-53)
pub struct WeekOfYearFunction;

impl SqlFunction for WeekOfYearFunction {
    fn signature(&self) -> FunctionSignature {
        FunctionSignature {
            name: "WEEKOFYEAR",
            category: FunctionCategory::Date,
            arg_count: ArgCount::Fixed(1),
            description: "Returns the ISO week number of the year (1-53)",
            returns: "INTEGER",
            examples: vec![
                "SELECT WEEKOFYEAR('2024-01-01')", // Returns week number
                "SELECT WEEKOFYEAR(NOW())",
            ],
        }
    }

    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
        if args.len() != 1 {
            return Err(anyhow!("WEEKOFYEAR expects exactly 1 argument"));
        }

        let date_str = match &args[0] {
            DataValue::String(s) | DataValue::DateTime(s) => s.as_str(),
            DataValue::InternedString(s) => s.as_str(),
            DataValue::Null => return Ok(DataValue::Null),
            _ => return Err(anyhow!("WEEKOFYEAR expects a date/datetime string")),
        };

        let dt = parse_datetime(date_str)?;
        let week = dt.iso_week().week();

        Ok(DataValue::Integer(week as i64))
    }
}

/// QUARTER function - returns the quarter of the year (1-4)
pub struct QuarterFunction;

impl SqlFunction for QuarterFunction {
    fn signature(&self) -> FunctionSignature {
        FunctionSignature {
            name: "QUARTER",
            category: FunctionCategory::Date,
            arg_count: ArgCount::Fixed(1),
            description: "Returns the quarter of the year (1-4)",
            returns: "INTEGER",
            examples: vec![
                "SELECT QUARTER('2024-01-15')", // Returns 1
                "SELECT QUARTER('2024-07-01')", // Returns 3
            ],
        }
    }

    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
        if args.len() != 1 {
            return Err(anyhow!("QUARTER expects exactly 1 argument"));
        }

        let date_str = match &args[0] {
            DataValue::String(s) | DataValue::DateTime(s) => s.as_str(),
            DataValue::InternedString(s) => s.as_str(),
            DataValue::Null => return Ok(DataValue::Null),
            _ => return Err(anyhow!("QUARTER expects a date/datetime string")),
        };

        let dt = parse_datetime(date_str)?;
        let month = dt.month();
        let quarter = (month - 1) / 3 + 1;

        Ok(DataValue::Integer(quarter as i64))
    }
}

/// YEAR function - extracts year from date
pub struct YearFunction;

impl SqlFunction for YearFunction {
    fn signature(&self) -> FunctionSignature {
        FunctionSignature {
            name: "YEAR",
            category: FunctionCategory::Date,
            arg_count: ArgCount::Fixed(1),
            description: "Returns the year from a date",
            returns: "INTEGER",
            examples: vec![
                "SELECT YEAR('2024-03-15')", // Returns 2024
                "SELECT YEAR(NOW())",        // Returns current year
            ],
        }
    }

    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
        if args.len() != 1 {
            return Err(anyhow!("YEAR expects exactly 1 argument"));
        }

        let date_str = match &args[0] {
            DataValue::String(s) | DataValue::DateTime(s) => s.as_str(),
            DataValue::InternedString(s) => s.as_str(),
            DataValue::Null => return Ok(DataValue::Null),
            _ => return Err(anyhow!("YEAR expects a date/datetime string")),
        };

        let dt = parse_datetime(date_str)?;
        Ok(DataValue::Float(dt.year() as f64))
    }
}

/// MONTH function - extracts month from date (1-12)
pub struct MonthFunction;

impl SqlFunction for MonthFunction {
    fn signature(&self) -> FunctionSignature {
        FunctionSignature {
            name: "MONTH",
            category: FunctionCategory::Date,
            arg_count: ArgCount::Fixed(1),
            description: "Returns the month from a date (1-12)",
            returns: "INTEGER",
            examples: vec![
                "SELECT MONTH('2024-03-15')", // Returns 3
                "SELECT MONTH(NOW())",        // Returns current month
            ],
        }
    }

    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
        if args.len() != 1 {
            return Err(anyhow!("MONTH expects exactly 1 argument"));
        }

        let date_str = match &args[0] {
            DataValue::String(s) | DataValue::DateTime(s) => s.as_str(),
            DataValue::InternedString(s) => s.as_str(),
            DataValue::Null => return Ok(DataValue::Null),
            _ => return Err(anyhow!("MONTH expects a date/datetime string")),
        };

        let dt = parse_datetime(date_str)?;
        Ok(DataValue::Float(dt.month() as f64))
    }
}

/// DAY function - extracts day of month from date (1-31)
pub struct DayFunction;

impl SqlFunction for DayFunction {
    fn signature(&self) -> FunctionSignature {
        FunctionSignature {
            name: "DAY",
            category: FunctionCategory::Date,
            arg_count: ArgCount::Fixed(1),
            description: "Returns the day of month from a date (1-31)",
            returns: "INTEGER",
            examples: vec![
                "SELECT DAY('2024-03-15')", // Returns 15
                "SELECT DAY(NOW())",        // Returns current day
            ],
        }
    }

    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
        if args.len() != 1 {
            return Err(anyhow!("DAY expects exactly 1 argument"));
        }

        let date_str = match &args[0] {
            DataValue::String(s) | DataValue::DateTime(s) => s.as_str(),
            DataValue::InternedString(s) => s.as_str(),
            DataValue::Null => return Ok(DataValue::Null),
            _ => return Err(anyhow!("DAY expects a date/datetime string")),
        };

        let dt = parse_datetime(date_str)?;
        Ok(DataValue::Float(dt.day() as f64))
    }
}

/// MONTHNAME function - returns the month name
pub struct MonthNameFunction;

impl SqlFunction for MonthNameFunction {
    fn signature(&self) -> FunctionSignature {
        FunctionSignature {
            name: "MONTHNAME",
            category: FunctionCategory::Date,
            arg_count: ArgCount::Range(1, 2),
            description: "Returns month name. Optional second arg: 'full' (default) or 'short'",
            returns: "STRING",
            examples: vec![
                "SELECT MONTHNAME('2024-01-15')",          // Returns 'January'
                "SELECT MONTHNAME('2024-01-15', 'short')", // Returns 'Jan'
            ],
        }
    }

    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
        if args.is_empty() || args.len() > 2 {
            return Err(anyhow!("MONTHNAME expects 1 or 2 arguments"));
        }

        let date_str = match &args[0] {
            DataValue::String(s) | DataValue::DateTime(s) => s.as_str(),
            DataValue::InternedString(s) => s.as_str(),
            DataValue::Null => return Ok(DataValue::Null),
            _ => return Err(anyhow!("MONTHNAME expects a date/datetime string")),
        };

        let format = if args.len() == 2 {
            match &args[1] {
                DataValue::String(s) => s.as_str(),
                DataValue::InternedString(s) => s.as_str(),
                DataValue::Null => "full",
                _ => return Err(anyhow!("MONTHNAME format must be 'full' or 'short'")),
            }
        } else {
            "full"
        };

        let dt = parse_datetime(date_str)?;

        let month_name = match format {
            "short" => dt.format("%b").to_string(), // Jan, Feb, Mar, etc.
            "full" | _ => dt.format("%B").to_string(), // January, February, etc.
        };

        Ok(DataValue::String(month_name))
    }
}

/// PARSE_DATETIME - Parse datetime with custom format string
pub struct ParseDateTimeFunction;

impl SqlFunction for ParseDateTimeFunction {
    fn signature(&self) -> FunctionSignature {
        FunctionSignature {
            name: "PARSE_DATETIME",
            category: FunctionCategory::Date,
            arg_count: ArgCount::Fixed(2),
            description: "Parse datetime string with custom format (uses chrono strftime format)",
            returns: "DATETIME",
            examples: vec![
                "SELECT PARSE_DATETIME('15/01/2024', '%d/%m/%Y')",
                "SELECT PARSE_DATETIME('Jan 15, 2024 14:30', '%b %d, %Y %H:%M')",
                "SELECT PARSE_DATETIME('2024-01-15T14:30:00', '%Y-%m-%dT%H:%M:%S')",
                "SELECT PARSE_DATETIME(date_string, '%Y%m%d-%H:%M:%S%.3f') FROM data -- FIX format",
            ],
        }
    }

    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
        self.validate_args(args)?;

        let date_str = match &args[0] {
            DataValue::String(s) | DataValue::DateTime(s) => s.as_str(),
            DataValue::InternedString(s) => s.as_str(),
            DataValue::Null => return Ok(DataValue::Null),
            _ => return Err(anyhow!("PARSE_DATETIME expects a string as first argument")),
        };

        let format_str = match &args[1] {
            DataValue::String(s) => s.as_str(),
            DataValue::InternedString(s) => s.as_str(),
            DataValue::Null => return Ok(DataValue::Null),
            _ => {
                return Err(anyhow!(
                    "PARSE_DATETIME expects a format string as second argument"
                ))
            }
        };

        // Try parsing with time component first
        if let Ok(dt) = NaiveDateTime::parse_from_str(date_str, format_str) {
            return Ok(DataValue::DateTime(
                Utc.from_utc_datetime(&dt)
                    .format("%Y-%m-%d %H:%M:%S%.3f")
                    .to_string(),
            ));
        }

        // Try parsing as date only
        if let Ok(d) = NaiveDate::parse_from_str(date_str, format_str) {
            return Ok(DataValue::DateTime(
                Utc.from_utc_datetime(&d.and_hms_opt(0, 0, 0).unwrap())
                    .format("%Y-%m-%d %H:%M:%S%.3f")
                    .to_string(),
            ));
        }

        Err(anyhow!(
            "Failed to parse '{}' with format '{}'. See https://docs.rs/chrono/latest/chrono/format/strftime/index.html",
            date_str,
            format_str
        ))
    }
}

/// PARSE_DATETIME_UTC - Parse datetime and explicitly interpret as UTC
pub struct ParseDateTimeUtcFunction;

impl SqlFunction for ParseDateTimeUtcFunction {
    fn signature(&self) -> FunctionSignature {
        FunctionSignature {
            name: "PARSE_DATETIME_UTC",
            category: FunctionCategory::Date,
            arg_count: ArgCount::Range(1, 2),
            description: "Parse datetime as UTC. With 1 arg: auto-detect format. With 2 args: use custom format",
            returns: "DATETIME (UTC)",
            examples: vec![
                "SELECT PARSE_DATETIME_UTC('2024-01-15 14:30:00')",
                "SELECT PARSE_DATETIME_UTC('20250925-14:52:15.567') -- FIX format auto-detected",
                "SELECT PARSE_DATETIME_UTC('15/01/2024 14:30', '%d/%m/%Y %H:%M')",
            ],
        }
    }

    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
        if args.is_empty() || args.len() > 2 {
            return Err(anyhow!("PARSE_DATETIME_UTC expects 1 or 2 arguments"));
        }

        let date_str = match &args[0] {
            DataValue::String(s) | DataValue::DateTime(s) => s.as_str(),
            DataValue::InternedString(s) => s.as_str(),
            DataValue::Null => return Ok(DataValue::Null),
            _ => {
                return Err(anyhow!(
                    "PARSE_DATETIME_UTC expects a string as first argument"
                ))
            }
        };

        // If format string provided, use it
        if args.len() == 2 {
            let format_str = match &args[1] {
                DataValue::String(s) => s.as_str(),
                DataValue::InternedString(s) => s.as_str(),
                DataValue::Null => return Ok(DataValue::Null),
                _ => {
                    return Err(anyhow!(
                        "PARSE_DATETIME_UTC expects a format string as second argument"
                    ))
                }
            };

            // Try parsing with time component first
            if let Ok(dt) = NaiveDateTime::parse_from_str(date_str, format_str) {
                return Ok(DataValue::DateTime(
                    Utc.from_utc_datetime(&dt)
                        .format("%Y-%m-%d %H:%M:%S%.3f")
                        .to_string(),
                ));
            }

            // Try parsing as date only
            if let Ok(d) = NaiveDate::parse_from_str(date_str, format_str) {
                return Ok(DataValue::DateTime(
                    Utc.from_utc_datetime(&d.and_hms_opt(0, 0, 0).unwrap())
                        .format("%Y-%m-%d %H:%M:%S%.3f")
                        .to_string(),
                ));
            }

            return Err(anyhow!(
                "Failed to parse '{}' with format '{}'",
                date_str,
                format_str
            ));
        }

        // No format string - use auto-detection (existing parse_datetime function)
        let dt = parse_datetime(date_str)?;
        Ok(DataValue::DateTime(
            dt.format("%Y-%m-%d %H:%M:%S%.3f").to_string(),
        ))
    }
}

/// DATETIME constructor - Create datetime from components
pub struct DateTimeConstructor;

impl SqlFunction for DateTimeConstructor {
    fn signature(&self) -> FunctionSignature {
        FunctionSignature {
            name: "DATETIME",
            category: FunctionCategory::Date,
            arg_count: ArgCount::Range(3, 7),
            description: "Create datetime from components: (year, month, day, [hour], [minute], [second], [is_utc])",
            returns: "DATETIME",
            examples: vec![
                "SELECT DATETIME(2024, 1, 15)",
                "SELECT DATETIME(2024, 1, 15, 14, 30, 0)",
                "SELECT DATETIME(2024, 12, 31, 23, 59, 59)",
                "-- Note: All times are interpreted as UTC",
            ],
        }
    }

    fn evaluate(&self, args: &[DataValue]) -> Result<DataValue> {
        if args.len() < 3 || args.len() > 7 {
            return Err(anyhow!("DATETIME expects 3-7 arguments: year, month, day, [hour], [minute], [second], [is_utc]"));
        }

        // Extract components
        let year = match &args[0] {
            DataValue::Integer(i) => *i as i32,
            DataValue::Float(f) => *f as i32,
            DataValue::Null => return Ok(DataValue::Null),
            _ => return Err(anyhow!("DATETIME year must be numeric")),
        };

        let month = match &args[1] {
            DataValue::Integer(i) => *i as u32,
            DataValue::Float(f) => *f as u32,
            DataValue::Null => return Ok(DataValue::Null),
            _ => return Err(anyhow!("DATETIME month must be numeric")),
        };

        let day = match &args[2] {
            DataValue::Integer(i) => *i as u32,
            DataValue::Float(f) => *f as u32,
            DataValue::Null => return Ok(DataValue::Null),
            _ => return Err(anyhow!("DATETIME day must be numeric")),
        };

        let hour = if args.len() > 3 {
            match &args[3] {
                DataValue::Integer(i) => *i as u32,
                DataValue::Float(f) => *f as u32,
                DataValue::Null => return Ok(DataValue::Null),
                _ => return Err(anyhow!("DATETIME hour must be numeric")),
            }
        } else {
            0
        };

        let minute = if args.len() > 4 {
            match &args[4] {
                DataValue::Integer(i) => *i as u32,
                DataValue::Float(f) => *f as u32,
                DataValue::Null => return Ok(DataValue::Null),
                _ => return Err(anyhow!("DATETIME minute must be numeric")),
            }
        } else {
            0
        };

        let second = if args.len() > 5 {
            match &args[5] {
                DataValue::Integer(i) => *i as u32,
                DataValue::Float(f) => *f as u32,
                DataValue::Null => return Ok(DataValue::Null),
                _ => return Err(anyhow!("DATETIME second must be numeric")),
            }
        } else {
            0
        };

        // is_utc parameter (default true)
        let _is_utc = if args.len() > 6 {
            match &args[6] {
                DataValue::Boolean(b) => *b,
                DataValue::Integer(i) => *i != 0,
                DataValue::Null => true,
                _ => return Err(anyhow!("DATETIME is_utc must be boolean")),
            }
        } else {
            true
        };

        // Create date
        let date = NaiveDate::from_ymd_opt(year, month, day)
            .ok_or_else(|| anyhow!("Invalid date: {}-{}-{}", year, month, day))?;

        // Create datetime
        let dt = date
            .and_hms_opt(hour, minute, second)
            .ok_or_else(|| anyhow!("Invalid time: {}:{}:{}", hour, minute, second))?;

        // For now, always interpret as UTC (local timezone support would require additional dependencies)
        let utc_dt = Utc.from_utc_datetime(&dt);

        Ok(DataValue::DateTime(
            utc_dt.format("%Y-%m-%d %H:%M:%S%.3f").to_string(),
        ))
    }
}

/// Register all date/time functions
pub fn register_date_time_functions(registry: &mut super::FunctionRegistry) {
    registry.register(Box::new(NowFunction));
    registry.register(Box::new(TodayFunction));
    registry.register(Box::new(DateDiffFunction));
    registry.register(Box::new(DateAddFunction));
    registry.register(Box::new(UnixTimestamp));
    registry.register(Box::new(FromUnixTime));
    registry.register(Box::new(TimeBucket));

    // Date extraction functions
    registry.register(Box::new(YearFunction));
    registry.register(Box::new(MonthFunction));
    registry.register(Box::new(DayFunction));
    registry.register(Box::new(DayOfWeekFunction));
    registry.register(Box::new(DayNameFunction));
    registry.register(Box::new(MonthNameFunction));

    // Date utility functions
    registry.register(Box::new(IsLeapYearFunction));
    registry.register(Box::new(WeekOfYearFunction));
    registry.register(Box::new(QuarterFunction));

    // Flexible parsing functions
    registry.register(Box::new(ParseDateTimeFunction));
    registry.register(Box::new(ParseDateTimeUtcFunction));
    registry.register(Box::new(DateTimeConstructor));
}