NUMBERVALUE

5 numbervalue_fn "NUMBERVALUE" (1, 3) [V "text", V "decimal_separator", V "group_separator"] |s, a, sv| { numbervalue(s, a) }

/// NUMBERVALUE: the separators default to the locale's.
fn numbervalue(site: &Site<'_>, a: &[CalcValue]) -> Step<CalcValue> {
    let text = text_arg(site, a, 0)?;
    if text.len_utf16_code_units() > MAX_READER_TEXT {
        return fail(WorksheetErrorCode::Value);
    }
    let separator = |index: usize, default: &str| -> Step<Option<char>> {
        let given_text;
        let text = if given(a, index) {
            given_text = text_arg(site, a, index)?.to_string_lossy();
            given_text.as_str()
        } else {
            default
        };
        match text.chars().next() {
            Some(c) => Ok(Some(c)),
            None => fail(WorksheetErrorCode::Value),
        }
    };
    let (decimal_default, group_default) = if given(a, 1) && given(a, 2) {
        (".", ",")
    } else {
        let (_, profile) = profile(site)?;
        (profile.decimal_separator, profile.thousands_separator)
    };
    let decimal = separator(1, decimal_default)?;
    let group = separator(2, group_default)?;
    let host_profile = site.environment().and_then(|environment| environment.profile.as_ref())
        .filter(|profile| value_text::measured_host_reader(profile)
            && decimal == Some('.') && group == Some(' '));
    if host_profile.is_some_and(|profile| text.to_string_lossy().chars()
        .any(|ch| value_text::measured_space(ch, profile))) {
        return fail(WorksheetErrorCode::Value);
    }
    let result = match k::numbervalue(&text, decimal, group) {
        // A numeric range error is final, including a zero coefficient. The host
        // grammar fallback must not admit the same rejected exponent as VALUE.
        Err(k::NumberValueError::Range) => return fail(WorksheetErrorCode::Value),
        result => result.map_err(|_| WorksheetErrorCode::Value),
    };
    if result.is_ok() || host_profile.is_none() || !matches!(a[0].core_ref(), CoreRef::Text(_)) {
        return out_number(site, result);
    }
    // The measured host admits the same currency, digit and date grammar as
    // VALUE after ASCII spaces and trailing percents are removed. Keep the
    // existing numeric parser and its percent arithmetic for its prior forms.
    let compact: String = text.to_string_lossy().chars().filter(|&ch| ch != ' ').collect();
    // Slash/colon forms have no new service evidence; keep their prior route.
    if compact.contains(['/', ':']) {
        return out_number(site, result);
    }
    let base = compact.trim_end_matches('%');
    let percent_count = (compact.len() - base.len()) as u32;
    let value = super::kit::text_value(base);
    match read_value(site, &[value], 0) {
        Ok(reading) => site.number(k::numbervalue_percent(reading.value(), percent_count)),
        Err(Exit::Error(WorksheetErrorCode::Value)) => out_number(site, result),
        Err(other) => Err(other),
    }
}

fn out_number(site: &Site<'_>, result: Result<f64, WorksheetErrorCode>) -> Step<CalcValue> {
    match result {
        Ok(n) => site.number(n),
        Err(code) => fail(code),
    }
}

pub(crate) fn numbervalue(
    text: &ExcelText,
    decimal_separator: Option<char>,
    group_separator: Option<char>,
) -> Result<f64, NumberValueError> {
    if decimal_separator.is_some() && decimal_separator == group_separator {
        return Err(NumberValueError::Syntax);
    }
    let mut raw: String = text
        .to_string_lossy()
        .chars()
        .filter(|ch| !ch.is_whitespace())
        .collect();
    if raw.is_empty() {
        // NUMBERVALUE("") is 0 (Microsoft's NUMBERVALUE page).
        return Ok(0.0);
    }
    let mut percent_count = 0u32;
    while raw.ends_with('%') {
        raw.pop();
        percent_count += 1;
    }
    if raw.is_empty() {
        return Err(NumberValueError::Syntax);
    }
    let mut chars = raw.chars().peekable();
    let mut sign = 1.0;
    if let Some(ch) = chars.peek().copied()
        && (ch == '+' || ch == '-')
    {
        sign = if ch == '-' { -1.0 } else { 1.0 };
        chars.next();
    }
    let mut normalized = String::new();
    let mut seen_digit = false;
    let mut seen_decimal = false;
    let mut seen_exponent = false;
    for ch in chars {
        if ch.is_ascii_digit() {
            normalized.push(ch);
            seen_digit = true;
            continue;
        }
        if decimal_separator == Some(ch) {
            if seen_decimal || seen_exponent {
                return Err(NumberValueError::Syntax);
            }
            normalized.push('.');
            seen_decimal = true;
            continue;
        }
        if group_separator == Some(ch) && !seen_decimal {
            continue;
        }
        if matches!(ch, 'e' | 'E') && seen_digit && !seen_exponent {
            normalized.push('e');
            seen_exponent = true;
            continue;
        }
        if matches!(ch, '+' | '-') && normalized.ends_with('e') {
            normalized.push(ch);
            continue;
        }
        return Err(NumberValueError::Syntax);
    }
    if !seen_digit {
        return Err(NumberValueError::Syntax);
    }
    let (mantissa, exponent) = if let Some((mantissa, exponent)) = normalized.split_once('e') {
        if matches!(exponent, "" | "+" | "-") {
            return Err(NumberValueError::Syntax);
        }
        (mantissa, exponent.parse::<i64>().map_err(|_| NumberValueError::Range)?)
    } else {
        (normalized.as_str(), 0)
    };
    let fraction_digits = mantissa.split_once('.').map_or(0, |(_, fraction)| fraction.len());
    let digits = mantissa.replace('.', "");
    let scale = exponent.checked_sub(fraction_digits as i64).ok_or(NumberValueError::Range)?;
    let position = scale.checked_add(digits.trim_start_matches('0').len() as i64)
        .ok_or(NumberValueError::Range)?;
    // NUMBERVALUE checks this range even for an all-zero coefficient and before
    // applying trailing percents (fn12's explicit-separator Excel controls).
    if !(-308..=308).contains(&position) {
        return Err(NumberValueError::Range);
    }
    let mut value = crate::value_text::decimal(&digits, scale).ok_or(NumberValueError::Range)?;
    value *= sign;
    Ok(numbervalue_percent(value, percent_count))
}

/// The existing NUMBERVALUE percent arithmetic, shared with its host reader.
pub(crate) fn numbervalue_percent(mut value: f64, percent_count: u32) -> f64 {
    for _ in 0..percent_count {
        value /= 100.0;
    }
    value
}