use super::super::*;

// PostgreSQL SQL years have no year zero; Chrono uses astronomical year
// numbering.
fn sql_year_to_chrono_year(function_name: &str, temporal_kind: &str, year: i32) -> Result<i32> {
    if year == 0 {
        return Err(Error::Other(format!(
            "DB9 function '{}' {} field value out of range",
            function_name, temporal_kind
        )));
    }

    if year < 0 {
        return year.checked_add(1).ok_or_else(|| {
            Error::Other(format!(
                "DB9 function '{}' {} field value out of range",
                function_name, temporal_kind
            ))
        });
    }

    Ok(year)
}

pub(in super::super) fn eval_date_part_function(
    function_name: &str,
    args: Vec<StoredValue>,
) -> Result<StoredValue> {
    if args.len() != 2 {
        return Err(Error::Other(format!(
            "DB9 function '{}' expects 2 args, got {}",
            function_name,
            args.len()
        )));
    }

    let mut iter = args.into_iter();
    let field = match iter.next().unwrap() {
        StoredValue::Text(value) => value.trim().to_uppercase(),
        StoredValue::Null => return Ok(StoredValue::Null),
        _ => return Ok(StoredValue::Null),
    };
    let return_numeric = function_name.eq_ignore_ascii_case("extract");
    let out = match iter.next().unwrap() {
        StoredValue::Timestamp(timestamp) if timestamp == i64::MAX || timestamp == i64::MIN => {
            match non_finite_timestamp_date_part_result(
                function_name,
                &field,
                timestamp == i64::MIN,
            )? {
                Some(result) => result,
                None => return Ok(StoredValue::Null),
            }
        }
        StoredValue::Timestamp(timestamp) => {
            let dt = Utc
                .timestamp_millis_opt(timestamp)
                .single()
                .ok_or_else(|| Error::Other("invalid DB9 timestamp value".to_owned()))?;

            match field.as_str() {
                "YEAR" => DatePartResult::Integer(sql_year_from_chrono_year(dt.year())),
                "MONTH" => DatePartResult::Integer(i64::from(dt.month())),
                "DAY" => DatePartResult::Integer(i64::from(dt.day())),
                "HOUR" => DatePartResult::Integer(i64::from(dt.hour())),
                "MINUTE" => DatePartResult::Integer(i64::from(dt.minute())),
                "SECOND" => DatePartResult::Micros(
                    i128::from(dt.second()) * 1_000_000
                        + i128::from(dt.timestamp_subsec_millis()) * 1000,
                ),
                "DOW" => DatePartResult::Integer(i64::from(dt.weekday().num_days_from_sunday())),
                "DOY" => DatePartResult::Integer(i64::from(dt.ordinal())),
                "WEEK" => DatePartResult::Integer(i64::from(dt.iso_week().week())),
                "QUARTER" => DatePartResult::Integer(i64::from((dt.month() - 1) / 3 + 1)),
                "EPOCH" => DatePartResult::Micros(i128::from(timestamp) * 1000),
                _ => {
                    return Err(Error::Other(format!(
                        "DB9 function '{}' does not support field '{}'",
                        function_name, field
                    )));
                }
            }
        }
        StoredValue::Interval(interval) => {
            interval_date_part_result(function_name, &field, &interval)?
        }
        StoredValue::Null => return Ok(StoredValue::Null),
        _ => return Ok(StoredValue::Null),
    };

    if return_numeric {
        out.to_numeric_value()
    } else {
        Ok(StoredValue::Float64(out.to_float()))
    }
}

#[derive(Debug, Clone, Copy)]
enum DatePartResult {
    Integer(i64),
    Micros(i128),
    Infinity { negative: bool },
}

impl DatePartResult {
    fn to_float(self) -> f64 {
        match self {
            DatePartResult::Integer(value) => value as f64,
            DatePartResult::Micros(value) => value as f64 / 1_000_000.0,
            DatePartResult::Infinity { negative } => {
                if negative {
                    f64::NEG_INFINITY
                } else {
                    f64::INFINITY
                }
            }
        }
    }

    fn to_numeric_value(self) -> Result<StoredValue> {
        match self {
            DatePartResult::Integer(value) => Ok(StoredValue::Numeric(parse_stored_decimal(
                &value.to_string(),
            )?)),
            DatePartResult::Micros(value) => Ok(StoredValue::Numeric(
                decimal_micros_to_stored_decimal(value)?,
            )),
            DatePartResult::Infinity { negative } => Ok(StoredValue::Float64(if negative {
                f64::NEG_INFINITY
            } else {
                f64::INFINITY
            })),
        }
    }
}

fn sql_year_from_chrono_year(year: i32) -> i64 {
    if year > 0 {
        i64::from(year)
    } else {
        i64::from(year) - 1
    }
}

fn decimal_micros_to_stored_decimal(micros: i128) -> Result<StoredDecimal> {
    let negative = micros < 0;
    let abs = micros.abs();
    let whole = abs / 1_000_000;
    let frac = abs % 1_000_000;
    let sign = if negative { "-" } else { "" };
    parse_stored_decimal(&format!("{sign}{whole}.{frac:06}"))
}

fn non_finite_timestamp_date_part_result(
    function_name: &str,
    field: &str,
    negative: bool,
) -> Result<Option<DatePartResult>> {
    match field {
        "YEAR" | "EPOCH" => Ok(Some(DatePartResult::Infinity { negative })),
        "MONTH" | "DAY" | "HOUR" | "MINUTE" | "SECOND" | "DOW" | "DOY" | "WEEK" | "QUARTER" => {
            Ok(None)
        }
        _ => Err(Error::Other(format!(
            "DB9 function '{}' does not support field '{}'",
            function_name, field
        ))),
    }
}

fn interval_date_part_result(
    function_name: &str,
    field: &str,
    interval: &StoredIntervalValue,
) -> Result<DatePartResult> {
    const MILLIS_PER_MINUTE: i64 = 60 * 1000;
    const MILLIS_PER_HOUR: i64 = 60 * MILLIS_PER_MINUTE;
    const MILLIS_PER_DAY: i64 = 24 * MILLIS_PER_HOUR;

    let days = interval.millis / MILLIS_PER_DAY;
    let remaining_after_days = interval.millis % MILLIS_PER_DAY;
    let hours = remaining_after_days / MILLIS_PER_HOUR;
    let remaining_after_hours = remaining_after_days % MILLIS_PER_HOUR;
    let minutes = remaining_after_hours / MILLIS_PER_MINUTE;
    let remaining_after_minutes = remaining_after_hours % MILLIS_PER_MINUTE;

    match field {
        "YEAR" => Ok(DatePartResult::Integer(i64::from(interval.months / 12))),
        "MONTH" => Ok(DatePartResult::Integer(i64::from(interval.months % 12))),
        "DAY" => Ok(DatePartResult::Integer(days)),
        "HOUR" => Ok(DatePartResult::Integer(hours)),
        "MINUTE" => Ok(DatePartResult::Integer(minutes)),
        "SECOND" => Ok(DatePartResult::Micros(
            i128::from(remaining_after_minutes) * 1000,
        )),
        "EPOCH" => {
            const MICROS_PER_SYMBOLIC_YEAR: i128 = 31_557_600_000_000;
            const MICROS_PER_SYMBOLIC_MONTH: i128 = 2_592_000_000_000;

            // PostgreSQL decomposes the stored month field into full symbolic
            // years and leftover symbolic months for interval EPOCH extraction.
            // Full 12-month groups use 365.25 days, while leftover months use
            // 30 days, so a stored 12-month interval is not 12 one-month epochs.
            let years = i128::from(interval.months / 12);
            let months = i128::from(interval.months % 12);
            Ok(DatePartResult::Micros(
                years * MICROS_PER_SYMBOLIC_YEAR
                    + months * MICROS_PER_SYMBOLIC_MONTH
                    + i128::from(interval.millis) * 1000,
            ))
        }
        _ => Err(Error::Other(format!(
            "DB9 function '{}' does not support field '{}'",
            function_name, field
        ))),
    }
}

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

    fn interval_epoch_micros(months: i32, millis: i64) -> i128 {
        match interval_date_part_result(
            "date_part",
            "EPOCH",
            &StoredIntervalValue { months, millis },
        )
        .unwrap()
        {
            DatePartResult::Micros(value) => value,
            other => panic!("expected interval EPOCH micros, got {:?}", other),
        }
    }

    #[test]
    fn interval_epoch_uses_postgresql_symbolic_year_and_month_lengths() {
        const MICROS_PER_SYMBOLIC_YEAR: i128 = 31_557_600_000_000;
        const MICROS_PER_SYMBOLIC_MONTH: i128 = 2_592_000_000_000;

        assert_eq!(interval_epoch_micros(1, 0), MICROS_PER_SYMBOLIC_MONTH);
        assert_eq!(interval_epoch_micros(12, 0), MICROS_PER_SYMBOLIC_YEAR);
        assert_ne!(interval_epoch_micros(12, 0), 12 * MICROS_PER_SYMBOLIC_MONTH);
        assert_eq!(
            interval_epoch_micros(13, 1_500),
            MICROS_PER_SYMBOLIC_YEAR + MICROS_PER_SYMBOLIC_MONTH + 1_500_000
        );
    }

    #[test]
    fn to_char_checks_output_size_incrementally() {
        let timestamp = chrono::NaiveDate::from_ymd_opt(2024, 1, 2)
            .unwrap()
            .and_hms_opt(12, 34, 56)
            .unwrap()
            .and_utc()
            .timestamp_millis();
        let pattern = "YYYY".repeat(DEFAULT_MAX_STRING_OUTPUT_BYTES / 4 + 1);
        let err = eval_to_char_function(
            "to_char",
            vec![
                StoredValue::Timestamp(timestamp),
                StoredValue::Text(pattern),
            ],
        )
        .unwrap_err();
        assert_eq!(err.to_string(), "requested length too large");

        let escaped_quote_pattern = "\"\"\"\"".repeat(5);
        let err = with_db9_string_output_byte_limit(4, || {
            eval_to_char_function(
                "to_char",
                vec![
                    StoredValue::Timestamp(timestamp),
                    StoredValue::Text(escaped_quote_pattern),
                ],
            )
        })
        .unwrap_err();
        assert_eq!(err.to_string(), "requested length too large");
    }

    #[test]
    fn age_same_infinity_reports_interval_out_of_range_for_mapper_contract() {
        for timestamp in [i64::MAX, i64::MIN] {
            let err = age_infinite_interval(&[
                StoredValue::Timestamp(timestamp),
                StoredValue::Timestamp(timestamp),
            ])
            .unwrap_err();
            assert_eq!(
                err.to_string(),
                "DB9 function 'age' interval is out of range"
            );
        }
    }
}

pub(in super::super) fn eval_date_function(
    function_name: &str,
    args: Vec<StoredValue>,
) -> Result<StoredValue> {
    eval_unary_function(function_name, args, |value| match value {
        StoredValue::Null => Ok(StoredValue::Null),
        StoredValue::Date(value) => Ok(StoredValue::Date(value)),
        StoredValue::Timestamp(value) => {
            timestamp_millis_to_date_days(value).map(StoredValue::Date)
        }
        StoredValue::Text(value) => parse_date_days(&value).map(StoredValue::Date),
        other => Err(Error::Other(format!(
            "DB9 function '{}' cannot convert {} to date",
            function_name,
            stored_value_pg_display_name(&other)
        ))),
    })
}

pub(in super::super) fn eval_make_date_function(
    function_name: &str,
    args: Vec<StoredValue>,
) -> Result<StoredValue> {
    if args.len() != 3 {
        return Err(Error::Other(format!(
            "DB9 function '{}' expects 3 args, got {}",
            function_name,
            args.len()
        )));
    }

    let mut iter = args.into_iter();
    let year = integer_value_as_i32(function_name, "year", iter.next().unwrap())?;
    let month = integer_value_as_i32(function_name, "month", iter.next().unwrap())?;
    let day = integer_value_as_i32(function_name, "day", iter.next().unwrap())?;
    let (Some(year), Some(month), Some(day)) = (year, month, day) else {
        return Ok(StoredValue::Null);
    };

    let year = sql_year_to_chrono_year(function_name, "date", year)?;
    let date =
        chrono::NaiveDate::from_ymd_opt(year, month as u32, day as u32).ok_or_else(|| {
            Error::Other(format!(
                "DB9 function '{}' date field value out of range",
                function_name
            ))
        })?;
    naive_date_to_days(date).map(StoredValue::Date)
}

pub(in super::super) fn eval_make_time_function(
    function_name: &str,
    args: Vec<StoredValue>,
) -> Result<StoredValue> {
    if args.len() != 3 {
        return Err(Error::Other(format!(
            "DB9 function '{}' expects 3 args, got {}",
            function_name,
            args.len()
        )));
    }

    let mut iter = args.into_iter();
    let hour = integer_value_as_i32(function_name, "hour", iter.next().unwrap())?;
    let minute = integer_value_as_i32(function_name, "minute", iter.next().unwrap())?;
    let second = numeric_value_as_f64_strict(function_name, "second", iter.next().unwrap())?;
    let (Some(hour), Some(minute), Some(second)) = (hour, minute, second) else {
        return Ok(StoredValue::Null);
    };

    let time_micros = time_fields_to_micros(function_name, "time", hour, minute, second)?;
    if !(0..=86_400_000_000).contains(&time_micros) {
        return Err(Error::Other(format!(
            "DB9 function '{}' time field value out of range",
            function_name
        )));
    }
    Ok(StoredValue::Time(time_micros))
}

#[cfg(test)]
pub(in super::super) fn eval_make_timestamp_function(
    function_name: &str,
    args: Vec<StoredValue>,
) -> Result<StoredValue> {
    if args.len() != 6 {
        return Err(Error::Other(format!(
            "DB9 function '{}' expects 6 args, got {}",
            function_name,
            args.len()
        )));
    }

    let mut iter = args.into_iter();
    let year = integer_value_as_i32(function_name, "year", iter.next().unwrap())?;
    let month = integer_value_as_i32(function_name, "month", iter.next().unwrap())?;
    let day = integer_value_as_i32(function_name, "day", iter.next().unwrap())?;
    let hour = integer_value_as_i32(function_name, "hour", iter.next().unwrap())?;
    let minute = integer_value_as_i32(function_name, "minute", iter.next().unwrap())?;
    let second = numeric_value_as_f64_strict(function_name, "second", iter.next().unwrap())?;
    let (Some(year), Some(month), Some(day), Some(hour), Some(minute), Some(second)) =
        (year, month, day, hour, minute, second)
    else {
        return Ok(StoredValue::Null);
    };

    let time_micros = time_fields_to_micros(function_name, "time", hour, minute, second)?;
    if !(0..=86_400_000_000).contains(&time_micros) {
        return Err(Error::Other(format!(
            "DB9 function '{}' time field value out of range",
            function_name
        )));
    }

    let carry_days = time_micros / 86_400_000_000;
    let day_time_micros = time_micros % 86_400_000_000;
    let seconds = (day_time_micros / 1_000_000) as u32;
    let micros = (day_time_micros % 1_000_000) as u32;
    let year = sql_year_to_chrono_year(function_name, "date", year)?;
    let timestamp = chrono::NaiveDate::from_ymd_opt(year, month as u32, day as u32)
        .and_then(|date| date.checked_add_signed(chrono::Duration::days(carry_days)))
        .and_then(|date| {
            date.and_hms_micro_opt(seconds / 3600, (seconds % 3600) / 60, seconds % 60, micros)
        })
        .ok_or_else(|| {
            Error::Other(format!(
                "DB9 function '{}' date field value out of range",
                function_name
            ))
        })?;
    Ok(StoredValue::Timestamp(
        timestamp.and_utc().timestamp_millis(),
    ))
}

#[cfg(test)]
pub(in super::super) fn eval_to_timestamp_function(
    function_name: &str,
    args: Vec<StoredValue>,
) -> Result<StoredValue> {
    if args.len() != 1 {
        return Err(Error::Other(format!(
            "DB9 function '{}' text+format form is not supported yet",
            function_name
        )));
    }

    let epoch_seconds =
        numeric_value_as_f64_strict(function_name, "epoch", args.into_iter().next().unwrap())?;
    let Some(epoch_seconds) = epoch_seconds else {
        return Ok(StoredValue::Null);
    };
    if epoch_seconds.is_nan() {
        return Err(Error::Other(format!(
            "DB9 function '{}' timestamp cannot be NaN",
            function_name
        )));
    }
    if epoch_seconds == f64::INFINITY {
        return Ok(StoredValue::Timestamp(i64::MAX));
    }
    if epoch_seconds == f64::NEG_INFINITY {
        return Ok(StoredValue::Timestamp(i64::MIN));
    }
    if !epoch_seconds.is_finite() {
        return Err(Error::Other(format!(
            "DB9 function '{}' timestamp out of range",
            function_name
        )));
    }

    let seconds = epoch_seconds.floor();
    const I64_MIN_AS_F64: f64 = -9_223_372_036_854_775_808.0;
    const I64_MAX_EXCLUSIVE_AS_F64: f64 = 9_223_372_036_854_775_808.0;
    if !(I64_MIN_AS_F64..I64_MAX_EXCLUSIVE_AS_F64).contains(&seconds) {
        return Err(Error::Other(format!(
            "DB9 function '{}' timestamp out of range",
            function_name
        )));
    }
    let nanos = ((epoch_seconds - seconds) * 1_000_000_000.0).trunc() as u32;
    let dt = Utc
        .timestamp_opt(seconds as i64, nanos)
        .single()
        .ok_or_else(|| {
            Error::Other(format!(
                "DB9 function '{}' timestamp out of range",
                function_name
            ))
        })?;
    Ok(StoredValue::Timestamp(dt.timestamp_millis()))
}

#[cfg(test)]
pub(in super::super) fn eval_make_interval_function(
    function_name: &str,
    args: Vec<StoredValue>,
) -> Result<StoredValue> {
    if args.len() > 7 {
        return Err(Error::Other(format!(
            "DB9 function '{}' accepts at most 7 args, got {}",
            function_name,
            args.len()
        )));
    }

    let years = interval_field_i32(function_name, "years", args.first())?;
    let months = interval_field_i32(function_name, "months", args.get(1))?;
    let weeks = interval_field_i32(function_name, "weeks", args.get(2))?;
    let days = interval_field_i32(function_name, "days", args.get(3))?;
    let hours = interval_field_i32(function_name, "hours", args.get(4))?;
    let mins = interval_field_i32(function_name, "mins", args.get(5))?;
    let (Some(years), Some(months), Some(weeks), Some(days), Some(hours), Some(mins)) =
        (years, months, weeks, days, hours, mins)
    else {
        return Ok(StoredValue::Null);
    };

    let seconds = if args.len() >= 7 {
        let Some(value) = numeric_value_as_f64_named(function_name, "secs", args[6].clone())?
        else {
            return Ok(StoredValue::Null);
        };
        if !value.is_finite() {
            return Err(Error::Other(format!(
                "DB9 function '{}' secs must be finite",
                function_name
            )));
        }
        value
    } else {
        0.0
    };

    let months = years
        .checked_mul(12)
        .and_then(|value| value.checked_add(months))
        .ok_or_else(|| {
            Error::Other(format!(
                "DB9 function '{}' month fields overflowed",
                function_name
            ))
        })?;

    let mut millis = 0_i64;
    millis = checked_add_interval_millis(
        function_name,
        millis,
        i64::from(weeks),
        7 * 24 * 60 * 60 * 1000,
        "weeks",
    )?;
    millis = checked_add_interval_millis(
        function_name,
        millis,
        i64::from(days),
        24 * 60 * 60 * 1000,
        "days",
    )?;
    millis = checked_add_interval_millis(
        function_name,
        millis,
        i64::from(hours),
        60 * 60 * 1000,
        "hours",
    )?;
    millis =
        checked_add_interval_millis(function_name, millis, i64::from(mins), 60 * 1000, "mins")?;
    millis = millis
        .checked_add(seconds_to_interval_millis(function_name, seconds)?)
        .ok_or_else(|| Error::Other(format!("DB9 function '{}' secs overflowed", function_name)))?;

    Ok(StoredValue::Interval(StoredIntervalValue {
        months,
        millis,
    }))
}

pub(in super::super) fn eval_age_function(
    function_name: &str,
    args: Vec<StoredValue>,
) -> Result<StoredValue> {
    if args.len() != 2 {
        return Err(Error::Other(format!(
            "DB9 function '{}' expects 2 args, got {}",
            function_name,
            args.len()
        )));
    }

    if args.iter().any(StoredValue::is_null) {
        return Ok(StoredValue::Null);
    }

    if let Some(interval) = age_infinite_interval(&args)? {
        return Ok(StoredValue::Interval(interval));
    }

    let end_ts = stored_value_to_naive_datetime(function_name, &args[0])?;
    let start_ts = stored_value_to_naive_datetime(function_name, &args[1])?;

    Ok(StoredValue::Interval(age_interval(
        function_name,
        end_ts,
        start_ts,
    )?))
}

pub(in super::super) fn age_infinite_interval(
    args: &[StoredValue],
) -> Result<Option<StoredIntervalValue>> {
    let end_sign = temporal_infinity_sign(&args[0])?;
    let start_sign = if args.len() == 2 {
        temporal_infinity_sign(&args[1])?
    } else {
        None
    };

    match (end_sign, start_sign) {
        (Some(end_negative), Some(start_negative)) => {
            if end_negative == start_negative {
                return Err(Error::Other(
                    "DB9 function 'age' interval is out of range".to_owned(),
                ));
            }
            Ok(Some(stored_interval_infinity(end_negative)))
        }
        (Some(end_negative), None) => Ok(Some(stored_interval_infinity(end_negative))),
        (None, Some(start_negative)) => Ok(Some(stored_interval_infinity(!start_negative))),
        (None, None) => Ok(None),
    }
}

fn temporal_infinity_sign(value: &StoredValue) -> Result<Option<bool>> {
    Ok(match value {
        StoredValue::Timestamp(ts) if *ts == i64::MAX => Some(false),
        StoredValue::Timestamp(ts) if *ts == i64::MIN => Some(true),
        StoredValue::Date(days) if *days == DATE_POS_INFINITY_DAYS => Some(false),
        StoredValue::Date(days) if *days == DATE_NEG_INFINITY_DAYS => Some(true),
        StoredValue::Text(s) => match parse_timestamp_millis(s)? {
            i64::MAX => Some(false),
            i64::MIN => Some(true),
            _ => None,
        },
        _ => None,
    })
}

pub(in super::super) fn eval_to_char_function(
    function_name: &str,
    args: Vec<StoredValue>,
) -> Result<StoredValue> {
    if args.len() != 2 {
        return Err(Error::Other(format!(
            "DB9 function '{}' expects 2 args, got {}",
            function_name,
            args.len()
        )));
    }

    let mut iter = args.into_iter();
    let value = iter.next().unwrap();
    let pattern = match iter.next().unwrap() {
        StoredValue::Text(value) => value,
        StoredValue::Null => return Ok(StoredValue::Null),
        other => {
            return Err(Error::Other(format!(
                "DB9 function '{}' format must be text, got {}",
                function_name,
                stored_value_pg_display_name(&other)
            )));
        }
    };

    if matches!(value, StoredValue::Null) {
        return Ok(StoredValue::Null);
    }
    if let Some(negative) = temporal_infinity_sign(&value)? {
        return Ok(StoredValue::Text(if negative {
            "-infinity".to_owned()
        } else {
            "infinity".to_owned()
        }));
    }

    let dt = stored_value_to_naive_datetime(function_name, &value)?;
    Ok(StoredValue::Text(format_to_char_pattern(
        function_name,
        &pattern,
        &dt,
    )?))
}

#[derive(Clone, Copy)]
enum ToCharField {
    Year,
    Month,
    Day,
    Hour,
    Minute,
    Second,
}

fn format_to_char_pattern(
    _function_name: &str,
    pattern: &str,
    dt: &chrono::NaiveDateTime,
) -> Result<String> {
    let mut out = String::new();
    let mut i = 0;
    let mut in_quotes = false;
    while i < pattern.len() {
        let rest = &pattern[i..];
        if rest.starts_with('"') {
            if in_quotes && rest.starts_with("\"\"") {
                push_char_checked(&mut out, '"')?;
                i += 2;
            } else {
                in_quotes = !in_quotes;
                i += 1;
            }
            continue;
        }

        if !in_quotes {
            if starts_with_to_char_token(rest, "HH24") {
                push_str_checked(&mut out, &format_to_char_field(dt, ToCharField::Hour))?;
                i += 4;
                continue;
            }
            if starts_with_to_char_token(rest, "YYYY") {
                push_str_checked(&mut out, &format_to_char_field(dt, ToCharField::Year))?;
                i += 4;
                continue;
            }
            if starts_with_to_char_token(rest, "MM") {
                push_str_checked(&mut out, &format_to_char_field(dt, ToCharField::Month))?;
                i += 2;
                continue;
            }
            if starts_with_to_char_token(rest, "DD") {
                push_str_checked(&mut out, &format_to_char_field(dt, ToCharField::Day))?;
                i += 2;
                continue;
            }
            if starts_with_to_char_token(rest, "MI") {
                push_str_checked(&mut out, &format_to_char_field(dt, ToCharField::Minute))?;
                i += 2;
                continue;
            }
            if starts_with_to_char_token(rest, "SS") {
                push_str_checked(&mut out, &format_to_char_field(dt, ToCharField::Second))?;
                i += 2;
                continue;
            }
        }

        let ch = rest.chars().next().expect("non-empty pattern suffix");
        push_char_checked(&mut out, ch)?;
        i += ch.len_utf8();
    }

    Ok(out)
}

fn format_to_char_field(dt: &chrono::NaiveDateTime, field: ToCharField) -> String {
    if let ToCharField::Year = field {
        return format!(
            "{:0width$}",
            sql_year_from_chrono_year(dt.year()).unsigned_abs(),
            width = 4
        );
    }

    let value = match field {
        ToCharField::Year => unreachable!("handled above"),
        ToCharField::Month => i64::from(dt.month()),
        ToCharField::Day => i64::from(dt.day()),
        ToCharField::Hour => i64::from(dt.hour()),
        ToCharField::Minute => i64::from(dt.minute()),
        ToCharField::Second => i64::from(dt.second()),
    };
    format!("{:0width$}", value, width = 2)
}

fn starts_with_to_char_token(rest: &str, token: &str) -> bool {
    let rest = rest.as_bytes();
    let token = token.as_bytes();
    rest.len() >= token.len() && rest[..token.len()].eq_ignore_ascii_case(token)
}

pub(in super::super) fn eval_date_trunc_function(
    function_name: &str,
    args: Vec<StoredValue>,
) -> Result<StoredValue> {
    if args.len() != 2 {
        return Err(Error::Other(format!(
            "DB9 function '{}' expects 2 args, got {}",
            function_name,
            args.len()
        )));
    }

    let mut iter = args.into_iter();
    let field = match iter.next().unwrap() {
        StoredValue::Text(value) => value.trim().to_lowercase(),
        StoredValue::Null => return Ok(StoredValue::Null),
        _ => return Ok(StoredValue::Null),
    };
    let timestamp = match iter.next().unwrap() {
        StoredValue::Timestamp(value) => value,
        StoredValue::Null => return Ok(StoredValue::Null),
        _ => return Ok(StoredValue::Null),
    };

    if timestamp == i64::MAX || timestamp == i64::MIN {
        return match field.as_str() {
            "year" | "month" | "day" | "hour" | "minute" | "second" => {
                Ok(StoredValue::Timestamp(timestamp))
            }
            _ => Err(Error::Other(format!(
                "DB9 function '{}' does not support field '{}'",
                function_name, field
            ))),
        };
    }

    if field == "second" {
        return Ok(StoredValue::Timestamp(timestamp.div_euclid(1000) * 1000));
    }

    let dt = Utc
        .timestamp_millis_opt(timestamp)
        .single()
        .ok_or_else(|| Error::Other("invalid DB9 timestamp value".to_owned()))?;

    let truncated = match field.as_str() {
        "year" => chrono::NaiveDate::from_ymd_opt(dt.year(), 1, 1)
            .ok_or_else(|| Error::Other("failed to truncate DB9 timestamp to year".to_owned()))?
            .and_hms_opt(0, 0, 0)
            .ok_or_else(|| Error::Other("failed to truncate DB9 timestamp to year".to_owned()))?
            .and_utc(),
        "month" => chrono::NaiveDate::from_ymd_opt(dt.year(), dt.month(), 1)
            .ok_or_else(|| Error::Other("failed to truncate DB9 timestamp to month".to_owned()))?
            .and_hms_opt(0, 0, 0)
            .ok_or_else(|| Error::Other("failed to truncate DB9 timestamp to month".to_owned()))?
            .and_utc(),
        "day" => chrono::NaiveDate::from_ymd_opt(dt.year(), dt.month(), dt.day())
            .ok_or_else(|| Error::Other("failed to truncate DB9 timestamp to day".to_owned()))?
            .and_hms_opt(0, 0, 0)
            .ok_or_else(|| Error::Other("failed to truncate DB9 timestamp to day".to_owned()))?
            .and_utc(),
        "hour" => chrono::NaiveDate::from_ymd_opt(dt.year(), dt.month(), dt.day())
            .ok_or_else(|| Error::Other("failed to truncate DB9 timestamp to hour".to_owned()))?
            .and_hms_opt(dt.hour(), 0, 0)
            .ok_or_else(|| Error::Other("failed to truncate DB9 timestamp to hour".to_owned()))?
            .and_utc(),
        "minute" => chrono::NaiveDate::from_ymd_opt(dt.year(), dt.month(), dt.day())
            .ok_or_else(|| Error::Other("failed to truncate DB9 timestamp to minute".to_owned()))?
            .and_hms_opt(dt.hour(), dt.minute(), 0)
            .ok_or_else(|| Error::Other("failed to truncate DB9 timestamp to minute".to_owned()))?
            .and_utc(),
        _ => {
            return Err(Error::Other(format!(
                "DB9 function '{}' does not support field '{}'",
                function_name, field
            )));
        }
    };

    Ok(StoredValue::Timestamp(truncated.timestamp_millis()))
}