formualizer_eval/builtins/lookup/
dynamic.rs

1//! Dynamic / modern lookup & array helpers: XLOOKUP, FILTER, UNIQUE (initial sprint subset)
2//!
3//! Notes / Simplifications (documented for future refinement):
4//! - XLOOKUP supports: lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]
5//!   * match_mode: 0 exact (default), -1 exact-or-next-smaller, 1 exact-or-next-larger, 2 wildcard (basic * ?)
6//!   * search_mode: 1 forward (default), -1 reverse; (2 / -2 binary not yet implemented -> treated as 1 / -1)
7//!   * Wildcard mode (2) currently case-insensitive ASCII only; TODO: full Excel semantics, escape handling (~)
8//! - FILTER supports: array, include, [if_empty]; Shapes must be broadcast-compatible by rows (include is 1-D).
9//!   * include may be vertical column vector OR same sized 2D; we reduce any non-zero truthy cell to include row.
10//!   * if_empty omitted -> #CALC! per Excel when no matches.
11//! - UNIQUE supports: array, [by_col], [exactly_once]
12//!   * by_col TRUE -> operate column-wise returning unique columns (NYI -> returns #N/IMPL! if TRUE)
13//!   * exactly_once TRUE returns only values with count == 1 (supported in row-wise primitive set)
14//! - All functions return Array literal values (spills) – engine handles spill placement later.
15//!
16//! TODO(backlog):
17//! - Binary search for XLOOKUP approximate modes; currently linear scan.
18//! - Better type coercion parity with Excel (booleans/text vs numbers nuances).
19//! - Match unsorted detection for approximate modes (#N/A) and wildcard escaping.
20//! - PERFORMANCE: streaming FILTER without full materialization; UNIQUE using smallvec for tiny sets.
21
22use super::super::utils::collapse_if_scalar;
23use super::lookup_utils::{cmp_for_lookup, equals_maybe_wildcard, value_to_f64_lenient};
24use crate::args::{ArgSchema, CoercionPolicy, ShapeKind};
25use crate::function::Function; // FnCaps imported via macro
26use crate::traits::{ArgumentHandle, FunctionContext};
27use formualizer_common::{ArgKind, ExcelError, ExcelErrorKind, LiteralValue};
28use formualizer_macros::func_caps;
29use std::collections::HashMap;
30
31/* ───────────────────────── helpers ───────────────────────── */
32
33pub fn super_wildcard_match(pattern: &str, text: &str) -> bool {
34    // public for shared lookup utils
35    // Excel-style wildcards with escape (~): * any seq, ? single char, ~ escapes next (*, ?, ~)
36    // Implement non-recursive DP for performance & to support escapes.
37    #[derive(Clone, Copy, Debug)]
38    enum Token<'a> {
39        AnySeq,
40        AnyChar,
41        Lit(&'a str),
42    }
43    let mut tokens: Vec<Token> = Vec::new();
44    let mut i = 0;
45    let bytes = pattern.as_bytes();
46    let mut lit_start = 0;
47    while i < bytes.len() {
48        match bytes[i] {
49            b'~' => {
50                // escape next if present
51                if i + 1 < bytes.len() {
52                    // flush pending literal
53                    if lit_start < i {
54                        tokens.push(Token::Lit(&pattern[lit_start..i]));
55                    }
56                    tokens.push(Token::Lit(&pattern[i + 1..i + 2]));
57                    i += 2;
58                    lit_start = i;
59                } else {
60                    // trailing ~ treated literal
61                    i += 1;
62                }
63            }
64            b'*' => {
65                if lit_start < i {
66                    tokens.push(Token::Lit(&pattern[lit_start..i]));
67                }
68                tokens.push(Token::AnySeq);
69                i += 1;
70                lit_start = i;
71            }
72            b'?' => {
73                if lit_start < i {
74                    tokens.push(Token::Lit(&pattern[lit_start..i]));
75                }
76                tokens.push(Token::AnyChar);
77                i += 1;
78                lit_start = i;
79            }
80            _ => i += 1,
81        }
82    }
83    if lit_start < bytes.len() {
84        tokens.push(Token::Lit(&pattern[lit_start..]));
85    }
86    // Simplify consecutive AnySeq
87    let mut compact: Vec<Token> = Vec::new();
88    for t in tokens {
89        match t {
90            Token::AnySeq => {
91                if !matches!(compact.last(), Some(Token::AnySeq)) {
92                    compact.push(t);
93                }
94            }
95            _ => compact.push(t),
96        }
97    }
98    // Backtracking matcher
99    fn match_tokens<'a>(tokens: &[Token<'a>], text: &str) -> bool {
100        fn eq_icase(a: &str, b: &str) -> bool {
101            a.eq_ignore_ascii_case(b)
102        }
103        // Convert Lit tokens into lowercase for quick compare
104        let mut ti = 0;
105        let tb = tokens;
106        // Use manual stack for backtracking when encountering AnySeq
107        let mut backtrack: Vec<(usize, usize)> = Vec::new(); // (token_index, text_index after consuming 1 more char by *)
108        let text_bytes = text.as_bytes();
109        let mut si = 0; // text index
110        loop {
111            if ti == tb.len() {
112                // tokens consumed
113                if si == text_bytes.len() {
114                    return true;
115                }
116                // Maybe backtrack
117            } else {
118                match tb[ti] {
119                    Token::AnySeq => {
120                        // try to match zero chars first
121                        ti += 1;
122                        backtrack.push((ti - 1, si + 1));
123                        continue;
124                    }
125                    Token::AnyChar => {
126                        if si < text_bytes.len() {
127                            ti += 1;
128                            si += 1;
129                            continue;
130                        }
131                    }
132                    Token::Lit(l) => {
133                        let l_len = l.len();
134                        if si + l_len <= text_bytes.len() && eq_icase(&text[si..si + l_len], l) {
135                            ti += 1;
136                            si += l_len;
137                            continue;
138                        }
139                    }
140                }
141            }
142            // failed match; attempt backtrack
143            if let Some((tok_star, new_si)) = backtrack.pop() {
144                if new_si <= text_bytes.len() {
145                    ti = tok_star + 1;
146                    si = new_si;
147                    continue;
148                } else {
149                    continue;
150                }
151            }
152            return false;
153        }
154    }
155    match_tokens(&compact, text)
156}
157
158/* ───────────────────────── XLOOKUP() ───────────────────────── */
159
160#[derive(Debug)]
161pub struct XLookupFn;
162
163impl Function for XLookupFn {
164    func_caps!(PURE, LOOKUP);
165    fn name(&self) -> &'static str {
166        "XLOOKUP"
167    }
168    fn min_args(&self) -> usize {
169        3
170    }
171    fn variadic(&self) -> bool {
172        true
173    }
174    fn arg_schema(&self) -> &'static [ArgSchema] {
175        use once_cell::sync::Lazy;
176        static SCHEMA: Lazy<Vec<ArgSchema>> = Lazy::new(|| {
177            vec![
178                // lookup_value
179                ArgSchema {
180                    kinds: smallvec::smallvec![ArgKind::Any],
181                    required: true,
182                    by_ref: false,
183                    shape: ShapeKind::Scalar,
184                    coercion: CoercionPolicy::None,
185                    max: None,
186                    repeating: None,
187                    default: None,
188                },
189                // lookup_array (range)
190                ArgSchema {
191                    kinds: smallvec::smallvec![ArgKind::Range],
192                    required: true,
193                    by_ref: true,
194                    shape: ShapeKind::Range,
195                    coercion: CoercionPolicy::None,
196                    max: None,
197                    repeating: None,
198                    default: None,
199                },
200                // return_array (range)
201                ArgSchema {
202                    kinds: smallvec::smallvec![ArgKind::Range],
203                    required: true,
204                    by_ref: true,
205                    shape: ShapeKind::Range,
206                    coercion: CoercionPolicy::None,
207                    max: None,
208                    repeating: None,
209                    default: None,
210                },
211                // if_not_found (any optional)
212                ArgSchema {
213                    kinds: smallvec::smallvec![ArgKind::Any],
214                    required: false,
215                    by_ref: false,
216                    shape: ShapeKind::Scalar,
217                    coercion: CoercionPolicy::None,
218                    max: None,
219                    repeating: None,
220                    default: None,
221                },
222                // match_mode (number) default 0
223                ArgSchema {
224                    kinds: smallvec::smallvec![ArgKind::Number],
225                    required: false,
226                    by_ref: false,
227                    shape: ShapeKind::Scalar,
228                    coercion: CoercionPolicy::NumberLenientText,
229                    max: None,
230                    repeating: None,
231                    default: Some(LiteralValue::Int(0)),
232                },
233                // search_mode (number) default 1
234                ArgSchema {
235                    kinds: smallvec::smallvec![ArgKind::Number],
236                    required: false,
237                    by_ref: false,
238                    shape: ShapeKind::Scalar,
239                    coercion: CoercionPolicy::NumberLenientText,
240                    max: None,
241                    repeating: None,
242                    default: Some(LiteralValue::Int(1)),
243                },
244            ]
245        });
246        &SCHEMA
247    }
248    fn eval<'a, 'b, 'c>(
249        &self,
250        args: &'c [ArgumentHandle<'a, 'b>],
251        _ctx: &dyn FunctionContext<'b>,
252    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
253        if args.len() < 3 {
254            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
255                ExcelError::new(ExcelErrorKind::Value),
256            )));
257        }
258        let lookup_value = args[0].value()?.into_literal();
259        if let LiteralValue::Error(ref e) = lookup_value {
260            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
261                e.clone(),
262            )));
263        }
264        let lookup_view = match args[1].range_view() {
265            Ok(v) => v,
266            Err(e) => return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(e))),
267        };
268        let ret_view = match args[2].range_view() {
269            Ok(v) => v,
270            Err(e) => return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(e))),
271        };
272
273        let (lookup_rows, lookup_cols) = lookup_view.dims();
274        let (ret_rows, ret_cols) = ret_view.dims();
275
276        // XLOOKUP requires a 1-D lookup array (single row or single column).
277        // If the lookup range is completely empty (used-region trimmed to 0),
278        // fall back to the return range's used-region length and treat missing lookup
279        // cells as Empty.
280        let vertical = if lookup_cols == 1 {
281            true
282        } else if lookup_rows == 1 {
283            false
284        } else if lookup_rows == 0 && lookup_cols == 0 {
285            if ret_cols == 1 {
286                true
287            } else if ret_rows == 1 {
288                false
289            } else {
290                return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
291                    ExcelError::new(ExcelErrorKind::Value),
292                )));
293            }
294        } else {
295            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
296                ExcelError::new(ExcelErrorKind::Value),
297            )));
298        };
299
300        let lookup_len = {
301            let raw = if vertical { lookup_rows } else { lookup_cols };
302            if raw == 0 {
303                if vertical { ret_rows } else { ret_cols }
304            } else {
305                raw
306            }
307        };
308
309        if lookup_len == 0 {
310            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
311                ExcelError::new(ExcelErrorKind::Na),
312            )));
313        }
314
315        let match_mode = if args.len() >= 5 {
316            match args[4].value()?.into_literal() {
317                LiteralValue::Int(i) => i,
318                LiteralValue::Number(n) => n as i64,
319                _ => 0,
320            }
321        } else {
322            0
323        };
324        let search_mode = if args.len() >= 6 {
325            match args[5].value()?.into_literal() {
326                LiteralValue::Int(i) => i,
327                LiteralValue::Number(n) => n as i64,
328                _ => 1,
329            }
330        } else {
331            1
332        };
333
334        let wildcard = match_mode == 2;
335
336        let mut found: Option<usize> = None;
337        let needle = lookup_value;
338        if match_mode == 0 || wildcard {
339            if search_mode == 1 && lookup_rows > 0 && lookup_cols > 0 {
340                found =
341                    super::lookup_utils::find_exact_index_in_view(&lookup_view, &needle, wildcard)?;
342            } else if search_mode == -1 {
343                for i in (0..lookup_len).rev() {
344                    let cand = if vertical {
345                        lookup_view.get_cell(i, 0)
346                    } else {
347                        lookup_view.get_cell(0, i)
348                    };
349                    if equals_maybe_wildcard(&needle, &cand, wildcard) {
350                        found = Some(i);
351                        break;
352                    }
353                }
354            } else {
355                // Fallback linear scan (also used when the lookup view is empty and
356                // we are treating missing cells as Empty).
357                for i in 0..lookup_len {
358                    let cand = if vertical {
359                        lookup_view.get_cell(i, 0)
360                    } else {
361                        lookup_view.get_cell(0, i)
362                    };
363                    if equals_maybe_wildcard(&needle, &cand, wildcard) {
364                        found = Some(i);
365                        break;
366                    }
367                }
368            }
369        } else if match_mode == -1 || match_mode == 1 {
370            let needle_num = value_to_f64_lenient(&needle);
371            let mut best_idx: Option<usize> = None;
372            let mut best_val: f64 = if match_mode == -1 {
373                f64::NEG_INFINITY
374            } else {
375                f64::INFINITY
376            };
377
378            let mut prev: Option<LiteralValue> = None;
379            for i in 0..lookup_len {
380                let cand = if vertical {
381                    lookup_view.get_cell(i, 0)
382                } else {
383                    lookup_view.get_cell(0, i)
384                };
385
386                if let Some(p) = prev.as_ref() {
387                    let sorted_ok = cmp_for_lookup(p, &cand).is_some_and(|o| o <= 0);
388                    if !sorted_ok {
389                        return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
390                            ExcelError::new(ExcelErrorKind::Na),
391                        )));
392                    }
393                }
394                prev = Some(cand.clone());
395
396                if cmp_for_lookup(&cand, &needle).is_some_and(|o| o == 0) {
397                    found = Some(i);
398                    break;
399                }
400
401                if let (Some(nn), Some(vv)) = (needle_num, value_to_f64_lenient(&cand)) {
402                    if match_mode == -1 {
403                        if vv <= nn && vv > best_val {
404                            best_val = vv;
405                            best_idx = Some(i);
406                        }
407                    } else if vv >= nn && vv < best_val {
408                        best_val = vv;
409                        best_idx = Some(i);
410                    }
411                }
412            }
413
414            if found.is_none() {
415                found = best_idx;
416            }
417        } else {
418            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
419                ExcelError::new(ExcelErrorKind::Value),
420            )));
421        }
422
423        if let Some(idx) = found {
424            let (ret_rows, ret_cols) = ret_view.dims();
425            if ret_rows == 0 || ret_cols == 0 {
426                return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Empty));
427            }
428
429            if vertical {
430                if ret_cols == 1 {
431                    return Ok(crate::traits::CalcValue::Scalar(ret_view.get_cell(idx, 0)));
432                }
433                let mut row_out: Vec<LiteralValue> = Vec::with_capacity(ret_cols);
434                for c in 0..ret_cols {
435                    row_out.push(ret_view.get_cell(idx, c));
436                }
437                return Ok(crate::traits::CalcValue::Range(
438                    crate::engine::range_view::RangeView::from_owned_rows(
439                        vec![row_out],
440                        _ctx.date_system(),
441                    ),
442                ));
443            }
444
445            // Horizontal orientation: treat idx as column.
446            if ret_rows == 1 {
447                return Ok(crate::traits::CalcValue::Scalar(ret_view.get_cell(0, idx)));
448            }
449
450            let mut col_out: Vec<Vec<LiteralValue>> = Vec::with_capacity(ret_rows);
451            for r in 0..ret_rows {
452                col_out.push(vec![ret_view.get_cell(r, idx)]);
453            }
454            return Ok(crate::traits::CalcValue::Range(
455                crate::engine::range_view::RangeView::from_owned_rows(col_out, _ctx.date_system()),
456            ));
457        }
458
459        if args.len() >= 4 {
460            return args[3].value();
461        }
462        Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
463            ExcelError::new(ExcelErrorKind::Na),
464        )))
465    }
466}
467
468/* ───────────────────────── XMATCH() ───────────────────────── */
469
470#[derive(Debug)]
471pub struct XMatchFn;
472impl Function for XMatchFn {
473    func_caps!(PURE, LOOKUP);
474    fn name(&self) -> &'static str {
475        "XMATCH"
476    }
477    fn min_args(&self) -> usize {
478        2
479    }
480    fn variadic(&self) -> bool {
481        true
482    }
483    fn arg_schema(&self) -> &'static [ArgSchema] {
484        use once_cell::sync::Lazy;
485        static SCHEMA: Lazy<Vec<ArgSchema>> = Lazy::new(|| {
486            vec![
487                // lookup_value
488                ArgSchema {
489                    kinds: smallvec::smallvec![ArgKind::Any],
490                    required: true,
491                    by_ref: false,
492                    shape: ShapeKind::Scalar,
493                    coercion: CoercionPolicy::None,
494                    max: None,
495                    repeating: None,
496                    default: None,
497                },
498                // lookup_array (range)
499                ArgSchema {
500                    kinds: smallvec::smallvec![ArgKind::Range],
501                    required: true,
502                    by_ref: true,
503                    shape: ShapeKind::Range,
504                    coercion: CoercionPolicy::None,
505                    max: None,
506                    repeating: None,
507                    default: None,
508                },
509                // match_mode (number) default 0
510                // 0 = exact (default), -1 = exact or next smaller, 1 = exact or next larger, 2 = wildcard
511                ArgSchema {
512                    kinds: smallvec::smallvec![ArgKind::Number],
513                    required: false,
514                    by_ref: false,
515                    shape: ShapeKind::Scalar,
516                    coercion: CoercionPolicy::NumberLenientText,
517                    max: None,
518                    repeating: None,
519                    default: Some(LiteralValue::Int(0)),
520                },
521                // search_mode (number) default 1
522                // 1 = first to last (default), -1 = last to first, 2 = binary ascending, -2 = binary descending
523                ArgSchema {
524                    kinds: smallvec::smallvec![ArgKind::Number],
525                    required: false,
526                    by_ref: false,
527                    shape: ShapeKind::Scalar,
528                    coercion: CoercionPolicy::NumberLenientText,
529                    max: None,
530                    repeating: None,
531                    default: Some(LiteralValue::Int(1)),
532                },
533            ]
534        });
535        &SCHEMA
536    }
537    fn eval<'a, 'b, 'c>(
538        &self,
539        args: &'c [ArgumentHandle<'a, 'b>],
540        _ctx: &dyn FunctionContext<'b>,
541    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
542        if args.len() < 2 {
543            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
544                ExcelError::new(ExcelErrorKind::Value),
545            )));
546        }
547        let lookup_value = args[0].value()?.into_literal();
548        if let LiteralValue::Error(ref e) = lookup_value {
549            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
550                e.clone(),
551            )));
552        }
553        let lookup_view = match args[1].range_view() {
554            Ok(v) => v,
555            Err(e) => return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(e))),
556        };
557
558        let (lookup_rows, lookup_cols) = lookup_view.dims();
559
560        // XMATCH requires a 1-D lookup array (single row or single column).
561        let vertical = if lookup_cols == 1 {
562            true
563        } else if lookup_rows == 1 {
564            false
565        } else if lookup_rows == 0 || lookup_cols == 0 {
566            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
567                ExcelError::new(ExcelErrorKind::Na),
568            )));
569        } else {
570            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
571                ExcelError::new(ExcelErrorKind::Value),
572            )));
573        };
574
575        let lookup_len = if vertical { lookup_rows } else { lookup_cols };
576
577        if lookup_len == 0 {
578            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
579                ExcelError::new(ExcelErrorKind::Na),
580            )));
581        }
582
583        let match_mode = if args.len() >= 3 {
584            match args[2].value()?.into_literal() {
585                LiteralValue::Int(i) => i,
586                LiteralValue::Number(n) => n as i64,
587                _ => 0,
588            }
589        } else {
590            0
591        };
592        let search_mode = if args.len() >= 4 {
593            match args[3].value()?.into_literal() {
594                LiteralValue::Int(i) => i,
595                LiteralValue::Number(n) => n as i64,
596                _ => 1,
597            }
598        } else {
599            1
600        };
601
602        let wildcard = match_mode == 2;
603        let needle = lookup_value;
604
605        let mut found: Option<usize> = None;
606
607        if match_mode == 0 || wildcard {
608            // Exact match or wildcard match
609            if search_mode == 1 || search_mode == 2 {
610                // Forward search (first to last) or binary ascending (treated as forward for exact)
611                if lookup_rows > 0 && lookup_cols > 0 {
612                    found = super::lookup_utils::find_exact_index_in_view(
613                        &lookup_view,
614                        &needle,
615                        wildcard,
616                    )?;
617                }
618            } else if search_mode == -1 || search_mode == -2 {
619                // Reverse search (last to first) or binary descending (treated as reverse for exact)
620                for i in (0..lookup_len).rev() {
621                    let cand = if vertical {
622                        lookup_view.get_cell(i, 0)
623                    } else {
624                        lookup_view.get_cell(0, i)
625                    };
626                    if equals_maybe_wildcard(&needle, &cand, wildcard) {
627                        found = Some(i);
628                        break;
629                    }
630                }
631            } else {
632                // Fallback linear scan
633                for i in 0..lookup_len {
634                    let cand = if vertical {
635                        lookup_view.get_cell(i, 0)
636                    } else {
637                        lookup_view.get_cell(0, i)
638                    };
639                    if equals_maybe_wildcard(&needle, &cand, wildcard) {
640                        found = Some(i);
641                        break;
642                    }
643                }
644            }
645        } else if match_mode == -1 || match_mode == 1 {
646            // Approximate match: -1 = exact or next smaller, 1 = exact or next larger
647            let needle_num = value_to_f64_lenient(&needle);
648            let mut best_idx: Option<usize> = None;
649            let mut best_val: f64 = if match_mode == -1 {
650                f64::NEG_INFINITY
651            } else {
652                f64::INFINITY
653            };
654
655            // Determine iteration direction based on search_mode
656            let use_reverse = search_mode == -1 || search_mode == -2;
657            let indices: Box<dyn Iterator<Item = usize>> = if use_reverse {
658                Box::new((0..lookup_len).rev())
659            } else {
660                Box::new(0..lookup_len)
661            };
662
663            // For binary search modes (2, -2), data should be sorted
664            // We verify sorting for approximate modes
665            if (search_mode == 2 || search_mode == -2) && match_mode != 0 {
666                let ascending = search_mode == 2;
667                let mut prev: Option<LiteralValue> = None;
668                for i in 0..lookup_len {
669                    let cand = if vertical {
670                        lookup_view.get_cell(i, 0)
671                    } else {
672                        lookup_view.get_cell(0, i)
673                    };
674                    if let Some(p) = prev.as_ref() {
675                        let sorted_ok = if ascending {
676                            cmp_for_lookup(p, &cand).is_some_and(|o| o <= 0)
677                        } else {
678                            cmp_for_lookup(p, &cand).is_some_and(|o| o >= 0)
679                        };
680                        if !sorted_ok {
681                            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
682                                ExcelError::new(ExcelErrorKind::Na),
683                            )));
684                        }
685                    }
686                    prev = Some(cand);
687                }
688            }
689
690            for i in indices {
691                let cand = if vertical {
692                    lookup_view.get_cell(i, 0)
693                } else {
694                    lookup_view.get_cell(0, i)
695                };
696
697                if cmp_for_lookup(&cand, &needle).is_some_and(|o| o == 0) {
698                    found = Some(i);
699                    break;
700                }
701
702                if let (Some(nn), Some(vv)) = (needle_num, value_to_f64_lenient(&cand)) {
703                    if match_mode == -1 {
704                        // exact or next smaller
705                        if vv <= nn && vv > best_val {
706                            best_val = vv;
707                            best_idx = Some(i);
708                        }
709                    } else {
710                        // match_mode == 1: exact or next larger
711                        if vv >= nn && vv < best_val {
712                            best_val = vv;
713                            best_idx = Some(i);
714                        }
715                    }
716                }
717            }
718
719            if found.is_none() {
720                found = best_idx;
721            }
722        } else {
723            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
724                ExcelError::new(ExcelErrorKind::Value),
725            )));
726        }
727
728        match found {
729            Some(idx) => Ok(crate::traits::CalcValue::Scalar(LiteralValue::Int(
730                (idx + 1) as i64,
731            ))),
732            None => Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
733                ExcelError::new(ExcelErrorKind::Na),
734            ))),
735        }
736    }
737}
738
739/* ───────────────────────── SORT() ───────────────────────── */
740
741#[derive(Debug)]
742pub struct SortFn;
743impl Function for SortFn {
744    func_caps!(PURE);
745    fn name(&self) -> &'static str {
746        "SORT"
747    }
748    fn min_args(&self) -> usize {
749        1
750    }
751    fn variadic(&self) -> bool {
752        true
753    }
754    fn arg_schema(&self) -> &'static [ArgSchema] {
755        use once_cell::sync::Lazy;
756        static SCHEMA: Lazy<Vec<ArgSchema>> = Lazy::new(|| {
757            vec![
758                // array
759                ArgSchema {
760                    kinds: smallvec::smallvec![ArgKind::Range],
761                    required: true,
762                    by_ref: true,
763                    shape: ShapeKind::Range,
764                    coercion: CoercionPolicy::None,
765                    max: None,
766                    repeating: None,
767                    default: None,
768                },
769                // sort_index (default 1)
770                ArgSchema {
771                    kinds: smallvec::smallvec![ArgKind::Number],
772                    required: false,
773                    by_ref: false,
774                    shape: ShapeKind::Scalar,
775                    coercion: CoercionPolicy::NumberLenientText,
776                    max: None,
777                    repeating: None,
778                    default: Some(LiteralValue::Int(1)),
779                },
780                // sort_order (default 1 = ascending, -1 = descending)
781                ArgSchema {
782                    kinds: smallvec::smallvec![ArgKind::Number],
783                    required: false,
784                    by_ref: false,
785                    shape: ShapeKind::Scalar,
786                    coercion: CoercionPolicy::NumberLenientText,
787                    max: None,
788                    repeating: None,
789                    default: Some(LiteralValue::Int(1)),
790                },
791                // by_col (default FALSE = sort rows, TRUE = sort columns)
792                ArgSchema {
793                    kinds: smallvec::smallvec![ArgKind::Logical],
794                    required: false,
795                    by_ref: false,
796                    shape: ShapeKind::Scalar,
797                    coercion: CoercionPolicy::Logical,
798                    max: None,
799                    repeating: None,
800                    default: Some(LiteralValue::Boolean(false)),
801                },
802            ]
803        });
804        &SCHEMA
805    }
806    fn eval<'a, 'b, 'c>(
807        &self,
808        args: &'c [ArgumentHandle<'a, 'b>],
809        _ctx: &dyn FunctionContext<'b>,
810    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
811        let view = match args[0].range_view() {
812            Ok(v) => v,
813            Err(e) => return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(e))),
814        };
815        let (rows, cols) = view.dims();
816        if rows == 0 || cols == 0 {
817            return Ok(crate::traits::CalcValue::Range(
818                crate::engine::range_view::RangeView::from_owned_rows(vec![], _ctx.date_system()),
819            ));
820        }
821
822        let sort_index = if args.len() >= 2 {
823            match args[1].value()?.into_literal() {
824                LiteralValue::Int(i) => i,
825                LiteralValue::Number(n) => n as i64,
826                _ => 1,
827            }
828        } else {
829            1
830        };
831
832        let sort_order = if args.len() >= 3 {
833            match args[2].value()?.into_literal() {
834                LiteralValue::Int(i) => i,
835                LiteralValue::Number(n) => n as i64,
836                _ => 1,
837            }
838        } else {
839            1
840        };
841
842        let by_col = if args.len() >= 4 {
843            matches!(args[3].value()?.into_literal(), LiteralValue::Boolean(true))
844        } else {
845            false
846        };
847
848        let ascending = sort_order >= 0;
849
850        if by_col {
851            // Sort columns by the specified row
852            let sort_row_idx = (sort_index - 1).max(0) as usize;
853            if sort_row_idx >= rows {
854                return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
855                    ExcelError::new(ExcelErrorKind::Value),
856                )));
857            }
858
859            // Extract columns as vectors
860            let mut columns: Vec<(usize, Vec<LiteralValue>)> = Vec::with_capacity(cols);
861            for c in 0..cols {
862                let mut col_vals: Vec<LiteralValue> = Vec::with_capacity(rows);
863                for r in 0..rows {
864                    col_vals.push(view.get_cell(r, c));
865                }
866                columns.push((c, col_vals));
867            }
868
869            // Sort columns by the value in sort_row_idx
870            columns.sort_by(|a, b| {
871                let val_a = &a.1[sort_row_idx];
872                let val_b = &b.1[sort_row_idx];
873                let cmp = cmp_for_lookup(val_a, val_b).unwrap_or(0);
874                if ascending { cmp.cmp(&0) } else { 0.cmp(&cmp) }
875            });
876
877            // Reconstruct the array with sorted columns
878            let mut out: Vec<Vec<LiteralValue>> = vec![Vec::with_capacity(cols); rows];
879            for (_orig_idx, col_vals) in columns {
880                for (r, val) in col_vals.into_iter().enumerate() {
881                    out[r].push(val);
882                }
883            }
884
885            Ok(collapse_if_scalar(out, _ctx.date_system()))
886        } else {
887            // Sort rows by the specified column
888            let sort_col_idx = (sort_index - 1).max(0) as usize;
889            if sort_col_idx >= cols {
890                return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
891                    ExcelError::new(ExcelErrorKind::Value),
892                )));
893            }
894
895            // Extract rows
896            let mut row_data: Vec<Vec<LiteralValue>> = Vec::with_capacity(rows);
897            for r in 0..rows {
898                let mut row_vals: Vec<LiteralValue> = Vec::with_capacity(cols);
899                for c in 0..cols {
900                    row_vals.push(view.get_cell(r, c));
901                }
902                row_data.push(row_vals);
903            }
904
905            // Sort rows by the value in sort_col_idx
906            row_data.sort_by(|a, b| {
907                let val_a = &a[sort_col_idx];
908                let val_b = &b[sort_col_idx];
909                let cmp = cmp_for_lookup(val_a, val_b).unwrap_or(0);
910                if ascending { cmp.cmp(&0) } else { 0.cmp(&cmp) }
911            });
912
913            Ok(collapse_if_scalar(row_data, _ctx.date_system()))
914        }
915    }
916}
917
918/* ───────────────────────── SORTBY() ───────────────────────── */
919
920#[derive(Debug)]
921pub struct SortByFn;
922impl Function for SortByFn {
923    func_caps!(PURE);
924    fn name(&self) -> &'static str {
925        "SORTBY"
926    }
927    fn min_args(&self) -> usize {
928        2
929    }
930    fn variadic(&self) -> bool {
931        true
932    }
933    fn arg_schema(&self) -> &'static [ArgSchema] {
934        use once_cell::sync::Lazy;
935        static SCHEMA: Lazy<Vec<ArgSchema>> = Lazy::new(|| {
936            vec![
937                // array
938                ArgSchema {
939                    kinds: smallvec::smallvec![ArgKind::Range],
940                    required: true,
941                    by_ref: true,
942                    shape: ShapeKind::Range,
943                    coercion: CoercionPolicy::None,
944                    max: None,
945                    repeating: None,
946                    default: None,
947                },
948                // by_array1
949                ArgSchema {
950                    kinds: smallvec::smallvec![ArgKind::Range],
951                    required: true,
952                    by_ref: true,
953                    shape: ShapeKind::Range,
954                    coercion: CoercionPolicy::None,
955                    max: None,
956                    repeating: None,
957                    default: None,
958                },
959                // sort_order1 (optional, default 1)
960                ArgSchema {
961                    kinds: smallvec::smallvec![ArgKind::Number],
962                    required: false,
963                    by_ref: false,
964                    shape: ShapeKind::Scalar,
965                    coercion: CoercionPolicy::NumberLenientText,
966                    max: None,
967                    repeating: None,
968                    default: Some(LiteralValue::Int(1)),
969                },
970                // Additional by_array/sort_order pairs can follow (variadic)
971            ]
972        });
973        &SCHEMA
974    }
975    fn eval<'a, 'b, 'c>(
976        &self,
977        args: &'c [ArgumentHandle<'a, 'b>],
978        _ctx: &dyn FunctionContext<'b>,
979    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
980        if args.len() < 2 {
981            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
982                ExcelError::new(ExcelErrorKind::Value),
983            )));
984        }
985
986        let view = match args[0].range_view() {
987            Ok(v) => v,
988            Err(e) => return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(e))),
989        };
990        let (rows, cols) = view.dims();
991        if rows == 0 || cols == 0 {
992            return Ok(crate::traits::CalcValue::Range(
993                crate::engine::range_view::RangeView::from_owned_rows(vec![], _ctx.date_system()),
994            ));
995        }
996
997        // Parse sort criteria: pairs of (by_array, sort_order)
998        // Arguments after array: by_array1, [sort_order1], [by_array2], [sort_order2], ...
999        let mut sort_criteria: Vec<(Vec<LiteralValue>, bool)> = Vec::new();
1000        let mut arg_idx = 1;
1001
1002        while arg_idx < args.len() {
1003            // by_array
1004            let by_view = match args[arg_idx].range_view() {
1005                Ok(v) => v,
1006                Err(e) => return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(e))),
1007            };
1008            let (by_rows, by_cols) = by_view.dims();
1009
1010            // The by_array should be 1-D and match the number of rows in the main array
1011            let by_values: Vec<LiteralValue> = if by_cols == 1 {
1012                if by_rows != rows {
1013                    return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
1014                        ExcelError::new(ExcelErrorKind::Value),
1015                    )));
1016                }
1017                (0..by_rows).map(|r| by_view.get_cell(r, 0)).collect()
1018            } else if by_rows == 1 {
1019                if by_cols != rows {
1020                    return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
1021                        ExcelError::new(ExcelErrorKind::Value),
1022                    )));
1023                }
1024                (0..by_cols).map(|c| by_view.get_cell(0, c)).collect()
1025            } else {
1026                return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
1027                    ExcelError::new(ExcelErrorKind::Value),
1028                )));
1029            };
1030
1031            arg_idx += 1;
1032
1033            // sort_order (optional)
1034            let ascending = if arg_idx < args.len() {
1035                // TODO(phase6): SORTBY parsing can mis-handle multi-criteria sort_order.
1036                // Check if next arg is a number (sort_order) or a range (next by_array)
1037                match args[arg_idx].value() {
1038                    Ok(v) => {
1039                        let lit = v.into_literal();
1040                        match lit {
1041                            LiteralValue::Int(i) => {
1042                                arg_idx += 1;
1043                                i >= 0
1044                            }
1045                            LiteralValue::Number(n) => {
1046                                arg_idx += 1;
1047                                n >= 0.0
1048                            }
1049                            _ => true, // Next arg is likely a range, use default ascending
1050                        }
1051                    }
1052                    Err(_) => true,
1053                }
1054            } else {
1055                true
1056            };
1057
1058            sort_criteria.push((by_values, ascending));
1059        }
1060
1061        if sort_criteria.is_empty() {
1062            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
1063                ExcelError::new(ExcelErrorKind::Value),
1064            )));
1065        }
1066
1067        // Extract rows with their indices
1068        let mut indexed_rows: Vec<(usize, Vec<LiteralValue>)> = Vec::with_capacity(rows);
1069        for r in 0..rows {
1070            let mut row_vals: Vec<LiteralValue> = Vec::with_capacity(cols);
1071            for c in 0..cols {
1072                row_vals.push(view.get_cell(r, c));
1073            }
1074            indexed_rows.push((r, row_vals));
1075        }
1076
1077        // Sort using all criteria
1078        indexed_rows.sort_by(|a, b| {
1079            for (by_values, ascending) in &sort_criteria {
1080                let val_a = &by_values[a.0];
1081                let val_b = &by_values[b.0];
1082                let cmp = cmp_for_lookup(val_a, val_b).unwrap_or(0);
1083                if cmp != 0 {
1084                    return if *ascending { cmp.cmp(&0) } else { 0.cmp(&cmp) };
1085                }
1086            }
1087            std::cmp::Ordering::Equal
1088        });
1089
1090        // Extract sorted rows
1091        let out: Vec<Vec<LiteralValue>> = indexed_rows.into_iter().map(|(_, row)| row).collect();
1092
1093        Ok(collapse_if_scalar(out, _ctx.date_system()))
1094    }
1095}
1096
1097/* ───────────────────────── RANDARRAY() ───────────────────────── */
1098
1099#[derive(Debug)]
1100pub struct RandArrayFn;
1101impl Function for RandArrayFn {
1102    // Note: RANDARRAY is NOT pure - it returns different values on each evaluation
1103    fn caps(&self) -> crate::function::FnCaps {
1104        crate::function::FnCaps::empty()
1105    }
1106    fn name(&self) -> &'static str {
1107        "RANDARRAY"
1108    }
1109    fn min_args(&self) -> usize {
1110        0
1111    }
1112    fn variadic(&self) -> bool {
1113        true
1114    }
1115    fn arg_schema(&self) -> &'static [ArgSchema] {
1116        use once_cell::sync::Lazy;
1117        static SCHEMA: Lazy<Vec<ArgSchema>> = Lazy::new(|| {
1118            vec![
1119                // rows (default 1)
1120                ArgSchema {
1121                    kinds: smallvec::smallvec![ArgKind::Number],
1122                    required: false,
1123                    by_ref: false,
1124                    shape: ShapeKind::Scalar,
1125                    coercion: CoercionPolicy::NumberLenientText,
1126                    max: None,
1127                    repeating: None,
1128                    default: Some(LiteralValue::Int(1)),
1129                },
1130                // columns (default 1)
1131                ArgSchema {
1132                    kinds: smallvec::smallvec![ArgKind::Number],
1133                    required: false,
1134                    by_ref: false,
1135                    shape: ShapeKind::Scalar,
1136                    coercion: CoercionPolicy::NumberLenientText,
1137                    max: None,
1138                    repeating: None,
1139                    default: Some(LiteralValue::Int(1)),
1140                },
1141                // min (default 0)
1142                ArgSchema {
1143                    kinds: smallvec::smallvec![ArgKind::Number],
1144                    required: false,
1145                    by_ref: false,
1146                    shape: ShapeKind::Scalar,
1147                    coercion: CoercionPolicy::NumberLenientText,
1148                    max: None,
1149                    repeating: None,
1150                    default: Some(LiteralValue::Int(0)),
1151                },
1152                // max (default 1)
1153                ArgSchema {
1154                    kinds: smallvec::smallvec![ArgKind::Number],
1155                    required: false,
1156                    by_ref: false,
1157                    shape: ShapeKind::Scalar,
1158                    coercion: CoercionPolicy::NumberLenientText,
1159                    max: None,
1160                    repeating: None,
1161                    default: Some(LiteralValue::Int(1)),
1162                },
1163                // whole_number (default FALSE)
1164                ArgSchema {
1165                    kinds: smallvec::smallvec![ArgKind::Logical],
1166                    required: false,
1167                    by_ref: false,
1168                    shape: ShapeKind::Scalar,
1169                    coercion: CoercionPolicy::Logical,
1170                    max: None,
1171                    repeating: None,
1172                    default: Some(LiteralValue::Boolean(false)),
1173                },
1174            ]
1175        });
1176        &SCHEMA
1177    }
1178    fn eval<'a, 'b, 'c>(
1179        &self,
1180        args: &'c [ArgumentHandle<'a, 'b>],
1181        _ctx: &dyn FunctionContext<'b>,
1182    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
1183        use rand::Rng;
1184
1185        // Extract numbers (allow float but coerce to i64 for dimensions)
1186        let num = |a: &ArgumentHandle| -> Result<f64, ExcelError> {
1187            Ok(match a.value()?.into_literal() {
1188                LiteralValue::Int(i) => i as f64,
1189                LiteralValue::Number(n) => n,
1190                LiteralValue::Error(e) => return Err(e),
1191                _other => {
1192                    return Err(ExcelError::new(ExcelErrorKind::Value));
1193                }
1194            })
1195        };
1196
1197        let rows = if !args.is_empty() {
1198            num(&args[0])? as i64
1199        } else {
1200            1
1201        };
1202        let cols = if args.len() >= 2 {
1203            num(&args[1])? as i64
1204        } else {
1205            1
1206        };
1207        let min_val = if args.len() >= 3 { num(&args[2])? } else { 0.0 };
1208        let max_val = if args.len() >= 4 { num(&args[3])? } else { 1.0 };
1209        let whole_number = if args.len() >= 5 {
1210            matches!(args[4].value()?.into_literal(), LiteralValue::Boolean(true))
1211        } else {
1212            false
1213        };
1214
1215        // Validate dimensions
1216        if rows <= 0 || cols <= 0 {
1217            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
1218                ExcelError::new(ExcelErrorKind::Value),
1219            )));
1220        }
1221
1222        // Validate min <= max for whole numbers
1223        if whole_number && min_val > max_val {
1224            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
1225                ExcelError::new(ExcelErrorKind::Value),
1226            )));
1227        }
1228
1229        let mut rng = rand::thread_rng();
1230        let mut out: Vec<Vec<LiteralValue>> = Vec::with_capacity(rows as usize);
1231
1232        for _r in 0..rows {
1233            let mut row_vec: Vec<LiteralValue> = Vec::with_capacity(cols as usize);
1234            for _c in 0..cols {
1235                let value = if whole_number {
1236                    // Generate random integer in range [min, max] inclusive
1237                    let min_int = min_val.ceil() as i64;
1238                    let max_int = max_val.floor() as i64;
1239                    if min_int > max_int {
1240                        return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
1241                            ExcelError::new(ExcelErrorKind::Value),
1242                        )));
1243                    }
1244                    let rand_int = rng.gen_range(min_int..=max_int);
1245                    LiteralValue::Int(rand_int)
1246                } else {
1247                    // Generate random float in range [min, max)
1248                    let rand_float = rng.r#gen::<f64>() * (max_val - min_val) + min_val;
1249                    LiteralValue::Number(rand_float)
1250                };
1251                row_vec.push(value);
1252            }
1253            out.push(row_vec);
1254        }
1255
1256        Ok(collapse_if_scalar(out, _ctx.date_system()))
1257    }
1258}
1259
1260/* ───────────────────────── GROUPBY() ───────────────────────── */
1261
1262/// Aggregation type for GROUPBY and PIVOTBY
1263#[derive(Debug, Clone, Copy, PartialEq, Eq)]
1264enum GroupAggregation {
1265    Sum,
1266    Average,
1267    Count,
1268    CountA,
1269    Max,
1270    Min,
1271    Product,
1272    StDev,
1273    StDevP,
1274    Var,
1275    VarP,
1276    Median,
1277}
1278
1279impl GroupAggregation {
1280    fn from_literal(val: &LiteralValue) -> Option<Self> {
1281        match val {
1282            LiteralValue::Text(s) => Self::from_str(s),
1283            LiteralValue::Int(n) => Self::from_num(*n as i32),
1284            LiteralValue::Number(n) => Self::from_num(*n as i32),
1285            _ => None,
1286        }
1287    }
1288
1289    fn from_str(s: &str) -> Option<Self> {
1290        let upper = s.to_ascii_uppercase();
1291        match upper.as_str() {
1292            "SUM" => Some(Self::Sum),
1293            "AVERAGE" | "AVG" => Some(Self::Average),
1294            "COUNT" => Some(Self::Count),
1295            "COUNTA" => Some(Self::CountA),
1296            "MAX" => Some(Self::Max),
1297            "MIN" => Some(Self::Min),
1298            "PRODUCT" => Some(Self::Product),
1299            "STDEV" | "STDEV.S" => Some(Self::StDev),
1300            "STDEVP" | "STDEV.P" => Some(Self::StDevP),
1301            "VAR" | "VAR.S" => Some(Self::Var),
1302            "VARP" | "VAR.P" => Some(Self::VarP),
1303            "MEDIAN" => Some(Self::Median),
1304            _ => None,
1305        }
1306    }
1307
1308    fn from_num(n: i32) -> Option<Self> {
1309        // Excel's AGGREGATE function_num mapping (common subset)
1310        match n {
1311            1 => Some(Self::Average),
1312            2 => Some(Self::Count),
1313            3 => Some(Self::CountA),
1314            4 => Some(Self::Max),
1315            5 => Some(Self::Min),
1316            6 => Some(Self::Product),
1317            7 => Some(Self::StDev),
1318            8 => Some(Self::StDevP),
1319            9 => Some(Self::Sum),
1320            10 => Some(Self::Var),
1321            11 => Some(Self::VarP),
1322            12 => Some(Self::Median),
1323            _ => None,
1324        }
1325    }
1326
1327    fn apply(&self, values: &[f64]) -> f64 {
1328        if values.is_empty() {
1329            return match self {
1330                Self::Count | Self::CountA => 0.0,
1331                Self::Sum | Self::Product => 0.0,
1332                _ => f64::NAN,
1333            };
1334        }
1335
1336        match self {
1337            Self::Sum => values.iter().sum(),
1338            Self::Average => values.iter().sum::<f64>() / values.len() as f64,
1339            Self::Count | Self::CountA => values.len() as f64,
1340            Self::Max => values.iter().copied().fold(f64::NEG_INFINITY, f64::max),
1341            Self::Min => values.iter().copied().fold(f64::INFINITY, f64::min),
1342            Self::Product => values.iter().product(),
1343            Self::StDev => {
1344                if values.len() < 2 {
1345                    return f64::NAN;
1346                }
1347                let mean = values.iter().sum::<f64>() / values.len() as f64;
1348                let variance = values.iter().map(|v| (v - mean).powi(2)).sum::<f64>()
1349                    / (values.len() - 1) as f64;
1350                variance.sqrt()
1351            }
1352            Self::StDevP => {
1353                let mean = values.iter().sum::<f64>() / values.len() as f64;
1354                let variance =
1355                    values.iter().map(|v| (v - mean).powi(2)).sum::<f64>() / values.len() as f64;
1356                variance.sqrt()
1357            }
1358            Self::Var => {
1359                if values.len() < 2 {
1360                    return f64::NAN;
1361                }
1362                let mean = values.iter().sum::<f64>() / values.len() as f64;
1363                values.iter().map(|v| (v - mean).powi(2)).sum::<f64>() / (values.len() - 1) as f64
1364            }
1365            Self::VarP => {
1366                let mean = values.iter().sum::<f64>() / values.len() as f64;
1367                values.iter().map(|v| (v - mean).powi(2)).sum::<f64>() / values.len() as f64
1368            }
1369            Self::Median => {
1370                let mut sorted = values.to_vec();
1371                sorted.sort_by(|a, b| a.partial_cmp(b).unwrap_or(std::cmp::Ordering::Equal));
1372                let mid = sorted.len() / 2;
1373                if sorted.len().is_multiple_of(2) {
1374                    (sorted[mid - 1] + sorted[mid]) / 2.0
1375                } else {
1376                    sorted[mid]
1377                }
1378            }
1379        }
1380    }
1381}
1382
1383/// Helper to convert LiteralValue to a group key string
1384fn literal_to_group_key(v: &LiteralValue) -> String {
1385    match v {
1386        LiteralValue::Text(s) => s.clone(),
1387        LiteralValue::Int(i) => i.to_string(),
1388        LiteralValue::Number(n) => format!("{:.10}", n),
1389        LiteralValue::Boolean(b) => if *b { "TRUE" } else { "FALSE" }.to_string(),
1390        LiteralValue::Empty => String::new(),
1391        LiteralValue::Error(e) => format!("#{:?}", e.kind),
1392        LiteralValue::Array(_) => "[Array]".to_string(),
1393        LiteralValue::Date(d) => d.to_string(),
1394        LiteralValue::DateTime(dt) => dt.to_string(),
1395        LiteralValue::Time(t) => t.to_string(),
1396        LiteralValue::Duration(d) => format!("{:?}", d),
1397        LiteralValue::Pending => "[Pending]".to_string(),
1398    }
1399}
1400
1401/// Helper to extract numeric value for aggregation
1402fn literal_to_num_opt(v: &LiteralValue) -> Option<f64> {
1403    match v {
1404        LiteralValue::Number(n) => Some(*n),
1405        LiteralValue::Int(i) => Some(*i as f64),
1406        LiteralValue::Boolean(b) => Some(if *b { 1.0 } else { 0.0 }),
1407        _ => None,
1408    }
1409}
1410
1411#[derive(Debug)]
1412pub struct GroupByFn;
1413
1414impl Function for GroupByFn {
1415    func_caps!(PURE);
1416    fn name(&self) -> &'static str {
1417        "GROUPBY"
1418    }
1419    fn min_args(&self) -> usize {
1420        3
1421    }
1422    fn variadic(&self) -> bool {
1423        true
1424    }
1425    fn arg_schema(&self) -> &'static [ArgSchema] {
1426        use once_cell::sync::Lazy;
1427        static SCHEMA: Lazy<Vec<ArgSchema>> = Lazy::new(|| {
1428            vec![
1429                // row_fields - range to group by
1430                ArgSchema {
1431                    kinds: smallvec::smallvec![ArgKind::Range],
1432                    required: true,
1433                    by_ref: true,
1434                    shape: ShapeKind::Range,
1435                    coercion: CoercionPolicy::None,
1436                    max: None,
1437                    repeating: None,
1438                    default: None,
1439                },
1440                // values - range of values to aggregate
1441                ArgSchema {
1442                    kinds: smallvec::smallvec![ArgKind::Range],
1443                    required: true,
1444                    by_ref: true,
1445                    shape: ShapeKind::Range,
1446                    coercion: CoercionPolicy::None,
1447                    max: None,
1448                    repeating: None,
1449                    default: None,
1450                },
1451                // function - aggregation function (SUM, AVERAGE, etc.)
1452                ArgSchema {
1453                    kinds: smallvec::smallvec![ArgKind::Any],
1454                    required: true,
1455                    by_ref: false,
1456                    shape: ShapeKind::Scalar,
1457                    coercion: CoercionPolicy::None,
1458                    max: None,
1459                    repeating: None,
1460                    default: None,
1461                },
1462                // field_headers (optional) - 0: no headers, 1: has headers (default), 2: generate headers, 3: has headers + generate
1463                ArgSchema {
1464                    kinds: smallvec::smallvec![ArgKind::Number],
1465                    required: false,
1466                    by_ref: false,
1467                    shape: ShapeKind::Scalar,
1468                    coercion: CoercionPolicy::NumberLenientText,
1469                    max: None,
1470                    repeating: None,
1471                    default: Some(LiteralValue::Int(1)),
1472                },
1473                // total_depth (optional) - 0: no totals, 1: grand total, 2: subtotals, etc.
1474                ArgSchema {
1475                    kinds: smallvec::smallvec![ArgKind::Number],
1476                    required: false,
1477                    by_ref: false,
1478                    shape: ShapeKind::Scalar,
1479                    coercion: CoercionPolicy::NumberLenientText,
1480                    max: None,
1481                    repeating: None,
1482                    default: Some(LiteralValue::Int(0)),
1483                },
1484                // sort_order (optional) - 0: no sorting, 1: ascending, -1: descending, 2: by value asc, -2: by value desc
1485                ArgSchema {
1486                    kinds: smallvec::smallvec![ArgKind::Number],
1487                    required: false,
1488                    by_ref: false,
1489                    shape: ShapeKind::Scalar,
1490                    coercion: CoercionPolicy::NumberLenientText,
1491                    max: None,
1492                    repeating: None,
1493                    default: Some(LiteralValue::Int(0)),
1494                },
1495            ]
1496        });
1497        &SCHEMA
1498    }
1499
1500    fn eval<'a, 'b, 'c>(
1501        &self,
1502        args: &'c [ArgumentHandle<'a, 'b>],
1503        _ctx: &dyn FunctionContext<'b>,
1504    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
1505        if args.len() < 3 {
1506            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
1507                ExcelError::new(ExcelErrorKind::Value),
1508            )));
1509        }
1510
1511        // Get row_fields and values ranges
1512        let row_fields_view = match args[0].range_view() {
1513            Ok(v) => v,
1514            Err(e) => return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(e))),
1515        };
1516        let values_view = match args[1].range_view() {
1517            Ok(v) => v,
1518            Err(e) => return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(e))),
1519        };
1520
1521        // Parse aggregation function
1522        let agg_val = args[2].value()?.into_literal();
1523        let aggregation = match GroupAggregation::from_literal(&agg_val) {
1524            Some(a) => a,
1525            None => {
1526                return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
1527                    ExcelError::new(ExcelErrorKind::Value)
1528                        .with_message("Invalid aggregation function"),
1529                )));
1530            }
1531        };
1532
1533        // Parse optional parameters
1534        let field_headers = if args.len() >= 4 {
1535            match args[3].value()?.into_literal() {
1536                LiteralValue::Int(i) => i as i32,
1537                LiteralValue::Number(n) => n as i32,
1538                _ => 1,
1539            }
1540        } else {
1541            1
1542        };
1543
1544        let total_depth = if args.len() >= 5 {
1545            match args[4].value()?.into_literal() {
1546                LiteralValue::Int(i) => i as i32,
1547                LiteralValue::Number(n) => n as i32,
1548                _ => 0,
1549            }
1550        } else {
1551            0
1552        };
1553
1554        let sort_order = if args.len() >= 6 {
1555            match args[5].value()?.into_literal() {
1556                LiteralValue::Int(i) => i as i32,
1557                LiteralValue::Number(n) => n as i32,
1558                _ => 0,
1559            }
1560        } else {
1561            0
1562        };
1563
1564        let (rf_rows, rf_cols) = row_fields_view.dims();
1565        let (val_rows, val_cols) = values_view.dims();
1566
1567        // Determine if we have headers
1568        let has_headers = field_headers == 1 || field_headers == 3;
1569        let data_start_row = if has_headers { 1 } else { 0 };
1570
1571        // Validate that row counts match (accounting for headers)
1572        if rf_rows != val_rows {
1573            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
1574                ExcelError::new(ExcelErrorKind::Value)
1575                    .with_message("Row fields and values must have same number of rows"),
1576            )));
1577        }
1578
1579        if rf_rows <= data_start_row {
1580            // No data rows
1581            return Ok(crate::traits::CalcValue::Range(
1582                crate::engine::range_view::RangeView::from_owned_rows(vec![], _ctx.date_system()),
1583            ));
1584        }
1585
1586        // Build groups: key -> list of values for each value column
1587        // For multi-column row_fields, concatenate keys
1588        let mut groups: std::collections::HashMap<String, Vec<Vec<f64>>> = HashMap::new();
1589        let mut group_order: Vec<String> = Vec::new();
1590
1591        for r in data_start_row..rf_rows {
1592            // Build composite key from all row_field columns
1593            let mut key_parts: Vec<String> = Vec::with_capacity(rf_cols);
1594            for c in 0..rf_cols {
1595                key_parts.push(literal_to_group_key(&row_fields_view.get_cell(r, c)));
1596            }
1597            let key = key_parts.join("\x00"); // Use null separator for composite keys
1598
1599            // Get values for this row
1600            let mut row_values: Vec<Option<f64>> = Vec::with_capacity(val_cols);
1601            for c in 0..val_cols {
1602                row_values.push(literal_to_num_opt(&values_view.get_cell(r, c)));
1603            }
1604
1605            // Add to groups
1606            if !groups.contains_key(&key) {
1607                group_order.push(key.clone());
1608                groups.insert(key.clone(), vec![Vec::new(); val_cols]);
1609            }
1610
1611            let group_vals = groups.get_mut(&key).unwrap();
1612            for (c, val) in row_values.iter().enumerate() {
1613                if let Some(v) = val {
1614                    group_vals[c].push(*v);
1615                }
1616            }
1617        }
1618
1619        // Sort groups if requested
1620        if sort_order != 0 {
1621            group_order.sort_by(|a, b| if sort_order > 0 { a.cmp(b) } else { b.cmp(a) });
1622        }
1623
1624        // Build output
1625        let mut output: Vec<Vec<LiteralValue>> = Vec::new();
1626
1627        // Add header row if requested
1628        let generate_headers = field_headers == 2 || field_headers == 3;
1629        if generate_headers {
1630            let mut header_row: Vec<LiteralValue> = Vec::new();
1631            // Row field headers
1632            for c in 0..rf_cols {
1633                if has_headers {
1634                    header_row.push(row_fields_view.get_cell(0, c));
1635                } else {
1636                    header_row.push(LiteralValue::Text(format!("Field{}", c + 1)));
1637                }
1638            }
1639            // Value headers
1640            for c in 0..val_cols {
1641                if has_headers {
1642                    header_row.push(values_view.get_cell(0, c));
1643                } else {
1644                    header_row.push(LiteralValue::Text(format!("Value{}", c + 1)));
1645                }
1646            }
1647            output.push(header_row);
1648        }
1649
1650        // Add grouped data rows
1651        for key in &group_order {
1652            let mut row: Vec<LiteralValue> = Vec::new();
1653
1654            // Add row field values (split composite key)
1655            let key_parts: Vec<&str> = key.split('\x00').collect();
1656            for part in &key_parts {
1657                row.push(LiteralValue::Text(part.to_string()));
1658            }
1659
1660            // Add aggregated values
1661            let group_vals = groups.get(key).unwrap();
1662            for col_vals in group_vals {
1663                let result = aggregation.apply(col_vals);
1664                if result.is_nan() {
1665                    row.push(LiteralValue::Error(ExcelError::new(ExcelErrorKind::Na)));
1666                } else if result.fract() == 0.0 && result.abs() < i64::MAX as f64 {
1667                    row.push(LiteralValue::Int(result as i64));
1668                } else {
1669                    row.push(LiteralValue::Number(result));
1670                }
1671            }
1672            output.push(row);
1673        }
1674
1675        // Add grand total if requested
1676        if total_depth >= 1 {
1677            let mut total_row: Vec<LiteralValue> = Vec::new();
1678            // Empty cells for row fields (except first which says "Grand Total")
1679            total_row.push(LiteralValue::Text("Grand Total".to_string()));
1680            for _ in 1..rf_cols {
1681                total_row.push(LiteralValue::Empty);
1682            }
1683
1684            // Aggregate all values across all groups
1685            for c in 0..val_cols {
1686                let mut all_vals: Vec<f64> = Vec::new();
1687                for group_vals in groups.values() {
1688                    all_vals.extend(&group_vals[c]);
1689                }
1690                let result = aggregation.apply(&all_vals);
1691                if result.is_nan() {
1692                    total_row.push(LiteralValue::Error(ExcelError::new(ExcelErrorKind::Na)));
1693                } else if result.fract() == 0.0 && result.abs() < i64::MAX as f64 {
1694                    total_row.push(LiteralValue::Int(result as i64));
1695                } else {
1696                    total_row.push(LiteralValue::Number(result));
1697                }
1698            }
1699            output.push(total_row);
1700        }
1701
1702        if output.is_empty() {
1703            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
1704                ExcelError::new(ExcelErrorKind::Calc),
1705            )));
1706        }
1707
1708        Ok(collapse_if_scalar(output, _ctx.date_system()))
1709    }
1710}
1711
1712/* ───────────────────────── PIVOTBY() ───────────────────────── */
1713
1714#[derive(Debug)]
1715pub struct PivotByFn;
1716
1717impl Function for PivotByFn {
1718    func_caps!(PURE);
1719    fn name(&self) -> &'static str {
1720        "PIVOTBY"
1721    }
1722    fn min_args(&self) -> usize {
1723        4
1724    }
1725    fn variadic(&self) -> bool {
1726        true
1727    }
1728    fn arg_schema(&self) -> &'static [ArgSchema] {
1729        use once_cell::sync::Lazy;
1730        static SCHEMA: Lazy<Vec<ArgSchema>> = Lazy::new(|| {
1731            vec![
1732                // row_fields - range to group rows by
1733                ArgSchema {
1734                    kinds: smallvec::smallvec![ArgKind::Range],
1735                    required: true,
1736                    by_ref: true,
1737                    shape: ShapeKind::Range,
1738                    coercion: CoercionPolicy::None,
1739                    max: None,
1740                    repeating: None,
1741                    default: None,
1742                },
1743                // col_fields - range to group columns by
1744                ArgSchema {
1745                    kinds: smallvec::smallvec![ArgKind::Range],
1746                    required: true,
1747                    by_ref: true,
1748                    shape: ShapeKind::Range,
1749                    coercion: CoercionPolicy::None,
1750                    max: None,
1751                    repeating: None,
1752                    default: None,
1753                },
1754                // values - range of values to aggregate
1755                ArgSchema {
1756                    kinds: smallvec::smallvec![ArgKind::Range],
1757                    required: true,
1758                    by_ref: true,
1759                    shape: ShapeKind::Range,
1760                    coercion: CoercionPolicy::None,
1761                    max: None,
1762                    repeating: None,
1763                    default: None,
1764                },
1765                // function - aggregation function
1766                ArgSchema {
1767                    kinds: smallvec::smallvec![ArgKind::Any],
1768                    required: true,
1769                    by_ref: false,
1770                    shape: ShapeKind::Scalar,
1771                    coercion: CoercionPolicy::None,
1772                    max: None,
1773                    repeating: None,
1774                    default: None,
1775                },
1776                // field_headers (optional)
1777                ArgSchema {
1778                    kinds: smallvec::smallvec![ArgKind::Number],
1779                    required: false,
1780                    by_ref: false,
1781                    shape: ShapeKind::Scalar,
1782                    coercion: CoercionPolicy::NumberLenientText,
1783                    max: None,
1784                    repeating: None,
1785                    default: Some(LiteralValue::Int(1)),
1786                },
1787                // row_total_depth (optional)
1788                ArgSchema {
1789                    kinds: smallvec::smallvec![ArgKind::Number],
1790                    required: false,
1791                    by_ref: false,
1792                    shape: ShapeKind::Scalar,
1793                    coercion: CoercionPolicy::NumberLenientText,
1794                    max: None,
1795                    repeating: None,
1796                    default: Some(LiteralValue::Int(0)),
1797                },
1798                // row_sort_order (optional)
1799                ArgSchema {
1800                    kinds: smallvec::smallvec![ArgKind::Number],
1801                    required: false,
1802                    by_ref: false,
1803                    shape: ShapeKind::Scalar,
1804                    coercion: CoercionPolicy::NumberLenientText,
1805                    max: None,
1806                    repeating: None,
1807                    default: Some(LiteralValue::Int(0)),
1808                },
1809                // col_total_depth (optional)
1810                ArgSchema {
1811                    kinds: smallvec::smallvec![ArgKind::Number],
1812                    required: false,
1813                    by_ref: false,
1814                    shape: ShapeKind::Scalar,
1815                    coercion: CoercionPolicy::NumberLenientText,
1816                    max: None,
1817                    repeating: None,
1818                    default: Some(LiteralValue::Int(0)),
1819                },
1820                // col_sort_order (optional)
1821                ArgSchema {
1822                    kinds: smallvec::smallvec![ArgKind::Number],
1823                    required: false,
1824                    by_ref: false,
1825                    shape: ShapeKind::Scalar,
1826                    coercion: CoercionPolicy::NumberLenientText,
1827                    max: None,
1828                    repeating: None,
1829                    default: Some(LiteralValue::Int(0)),
1830                },
1831            ]
1832        });
1833        &SCHEMA
1834    }
1835
1836    fn eval<'a, 'b, 'c>(
1837        &self,
1838        args: &'c [ArgumentHandle<'a, 'b>],
1839        _ctx: &dyn FunctionContext<'b>,
1840    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
1841        if args.len() < 4 {
1842            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
1843                ExcelError::new(ExcelErrorKind::Value),
1844            )));
1845        }
1846
1847        // Get ranges
1848        let row_fields_view = match args[0].range_view() {
1849            Ok(v) => v,
1850            Err(e) => return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(e))),
1851        };
1852        let col_fields_view = match args[1].range_view() {
1853            Ok(v) => v,
1854            Err(e) => return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(e))),
1855        };
1856        let values_view = match args[2].range_view() {
1857            Ok(v) => v,
1858            Err(e) => return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(e))),
1859        };
1860
1861        // Parse aggregation function
1862        let agg_val = args[3].value()?.into_literal();
1863        let aggregation = match GroupAggregation::from_literal(&agg_val) {
1864            Some(a) => a,
1865            None => {
1866                return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
1867                    ExcelError::new(ExcelErrorKind::Value)
1868                        .with_message("Invalid aggregation function"),
1869                )));
1870            }
1871        };
1872
1873        // Parse optional parameters
1874        let field_headers = if args.len() >= 5 {
1875            match args[4].value()?.into_literal() {
1876                LiteralValue::Int(i) => i as i32,
1877                LiteralValue::Number(n) => n as i32,
1878                _ => 1,
1879            }
1880        } else {
1881            1
1882        };
1883
1884        let row_total_depth = if args.len() >= 6 {
1885            match args[5].value()?.into_literal() {
1886                LiteralValue::Int(i) => i as i32,
1887                LiteralValue::Number(n) => n as i32,
1888                _ => 0,
1889            }
1890        } else {
1891            0
1892        };
1893
1894        let row_sort_order = if args.len() >= 7 {
1895            match args[6].value()?.into_literal() {
1896                LiteralValue::Int(i) => i as i32,
1897                LiteralValue::Number(n) => n as i32,
1898                _ => 0,
1899            }
1900        } else {
1901            0
1902        };
1903
1904        let col_total_depth = if args.len() >= 8 {
1905            match args[7].value()?.into_literal() {
1906                LiteralValue::Int(i) => i as i32,
1907                LiteralValue::Number(n) => n as i32,
1908                _ => 0,
1909            }
1910        } else {
1911            0
1912        };
1913
1914        let col_sort_order = if args.len() >= 9 {
1915            match args[8].value()?.into_literal() {
1916                LiteralValue::Int(i) => i as i32,
1917                LiteralValue::Number(n) => n as i32,
1918                _ => 0,
1919            }
1920        } else {
1921            0
1922        };
1923
1924        let (rf_rows, rf_cols) = row_fields_view.dims();
1925        let (cf_rows, _cf_cols) = col_fields_view.dims();
1926        let (val_rows, _val_cols) = values_view.dims();
1927
1928        // Validate dimensions
1929        if rf_rows != cf_rows || rf_rows != val_rows {
1930            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
1931                ExcelError::new(ExcelErrorKind::Value)
1932                    .with_message("All ranges must have same number of rows"),
1933            )));
1934        }
1935
1936        let has_headers = field_headers == 1 || field_headers == 3;
1937        let data_start_row = if has_headers { 1 } else { 0 };
1938
1939        if rf_rows <= data_start_row {
1940            return Ok(crate::traits::CalcValue::Range(
1941                crate::engine::range_view::RangeView::from_owned_rows(vec![], _ctx.date_system()),
1942            ));
1943        }
1944
1945        // Collect unique row and column keys
1946        let mut row_keys: Vec<String> = Vec::new();
1947        let mut col_keys: Vec<String> = Vec::new();
1948        let mut row_key_set: std::collections::HashSet<String> = std::collections::HashSet::new();
1949        let mut col_key_set: std::collections::HashSet<String> = std::collections::HashSet::new();
1950
1951        // Build pivot data: (row_key, col_key) -> values
1952        let mut pivot_data: HashMap<(String, String), Vec<f64>> = HashMap::new();
1953
1954        for r in data_start_row..rf_rows {
1955            // Build row key
1956            let mut row_key_parts: Vec<String> = Vec::with_capacity(rf_cols);
1957            for c in 0..rf_cols {
1958                row_key_parts.push(literal_to_group_key(&row_fields_view.get_cell(r, c)));
1959            }
1960            let row_key = row_key_parts.join("\x00");
1961
1962            // Build col key (use first column of col_fields for simplicity)
1963            // TODO(phase6): PIVOTBY only uses first col of col_fields/values.
1964            let col_key = literal_to_group_key(&col_fields_view.get_cell(r, 0));
1965
1966            // Get value (use first column of values)
1967            let val = literal_to_num_opt(&values_view.get_cell(r, 0));
1968
1969            // Track unique keys in order
1970            if !row_key_set.contains(&row_key) {
1971                row_key_set.insert(row_key.clone());
1972                row_keys.push(row_key.clone());
1973            }
1974            if !col_key_set.contains(&col_key) {
1975                col_key_set.insert(col_key.clone());
1976                col_keys.push(col_key.clone());
1977            }
1978
1979            // Add to pivot data
1980            let entry = pivot_data.entry((row_key, col_key)).or_default();
1981            if let Some(v) = val {
1982                entry.push(v);
1983            }
1984        }
1985
1986        // Sort keys if requested
1987        if row_sort_order != 0 {
1988            row_keys.sort_by(|a, b| {
1989                if row_sort_order > 0 {
1990                    a.cmp(b)
1991                } else {
1992                    b.cmp(a)
1993                }
1994            });
1995        }
1996        if col_sort_order != 0 {
1997            col_keys.sort_by(|a, b| {
1998                if col_sort_order > 0 {
1999                    a.cmp(b)
2000                } else {
2001                    b.cmp(a)
2002                }
2003            });
2004        }
2005
2006        // Build output grid
2007        let mut output: Vec<Vec<LiteralValue>> = Vec::new();
2008
2009        // Header row: empty cells for row fields + column keys
2010        let generate_headers = field_headers == 2 || field_headers == 3;
2011        if generate_headers || has_headers {
2012            let mut header_row: Vec<LiteralValue> = Vec::new();
2013            // Empty cells for row field columns
2014            for _ in 0..rf_cols {
2015                header_row.push(LiteralValue::Empty);
2016            }
2017            // Column headers
2018            for col_key in &col_keys {
2019                // Split composite key and use the visible parts
2020                let parts: Vec<&str> = col_key.split('\x00').collect();
2021                header_row.push(LiteralValue::Text(parts.join(" ")));
2022            }
2023            // Total column header
2024            if col_total_depth >= 1 {
2025                header_row.push(LiteralValue::Text("Total".to_string()));
2026            }
2027            output.push(header_row);
2028        }
2029
2030        // Data rows
2031        for row_key in &row_keys {
2032            let mut row: Vec<LiteralValue> = Vec::new();
2033
2034            // Row field values
2035            let row_parts: Vec<&str> = row_key.split('\x00').collect();
2036            for part in &row_parts {
2037                row.push(LiteralValue::Text(part.to_string()));
2038            }
2039
2040            // Values for each column
2041            let mut row_total_vals: Vec<f64> = Vec::new();
2042            for col_key in &col_keys {
2043                let key = (row_key.clone(), col_key.clone());
2044                let vals = pivot_data.get(&key).map(|v| v.as_slice()).unwrap_or(&[]);
2045                let result = aggregation.apply(vals);
2046
2047                // Collect for row total
2048                row_total_vals.extend(vals);
2049
2050                if result.is_nan() || vals.is_empty() {
2051                    row.push(LiteralValue::Empty);
2052                } else if result.fract() == 0.0 && result.abs() < i64::MAX as f64 {
2053                    row.push(LiteralValue::Int(result as i64));
2054                } else {
2055                    row.push(LiteralValue::Number(result));
2056                }
2057            }
2058
2059            // Row total
2060            if col_total_depth >= 1 {
2061                let result = aggregation.apply(&row_total_vals);
2062                if result.is_nan() {
2063                    row.push(LiteralValue::Error(ExcelError::new(ExcelErrorKind::Na)));
2064                } else if result.fract() == 0.0 && result.abs() < i64::MAX as f64 {
2065                    row.push(LiteralValue::Int(result as i64));
2066                } else {
2067                    row.push(LiteralValue::Number(result));
2068                }
2069            }
2070
2071            output.push(row);
2072        }
2073
2074        // Grand total row
2075        if row_total_depth >= 1 {
2076            let mut total_row: Vec<LiteralValue> = Vec::new();
2077            total_row.push(LiteralValue::Text("Total".to_string()));
2078            for _ in 1..rf_cols {
2079                total_row.push(LiteralValue::Empty);
2080            }
2081
2082            let mut grand_total_vals: Vec<f64> = Vec::new();
2083            for col_key in &col_keys {
2084                let mut col_vals: Vec<f64> = Vec::new();
2085                for row_key in &row_keys {
2086                    let key = (row_key.clone(), col_key.clone());
2087                    if let Some(vals) = pivot_data.get(&key) {
2088                        col_vals.extend(vals);
2089                    }
2090                }
2091                grand_total_vals.extend(&col_vals);
2092                let result = aggregation.apply(&col_vals);
2093                if result.is_nan() {
2094                    total_row.push(LiteralValue::Error(ExcelError::new(ExcelErrorKind::Na)));
2095                } else if result.fract() == 0.0 && result.abs() < i64::MAX as f64 {
2096                    total_row.push(LiteralValue::Int(result as i64));
2097                } else {
2098                    total_row.push(LiteralValue::Number(result));
2099                }
2100            }
2101
2102            // Grand total of grand totals
2103            if col_total_depth >= 1 {
2104                let result = aggregation.apply(&grand_total_vals);
2105                if result.is_nan() {
2106                    total_row.push(LiteralValue::Error(ExcelError::new(ExcelErrorKind::Na)));
2107                } else if result.fract() == 0.0 && result.abs() < i64::MAX as f64 {
2108                    total_row.push(LiteralValue::Int(result as i64));
2109                } else {
2110                    total_row.push(LiteralValue::Number(result));
2111                }
2112            }
2113
2114            output.push(total_row);
2115        }
2116
2117        if output.is_empty() {
2118            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
2119                ExcelError::new(ExcelErrorKind::Calc),
2120            )));
2121        }
2122
2123        Ok(collapse_if_scalar(output, _ctx.date_system()))
2124    }
2125}
2126
2127/* ───────────────────────── FILTER() ───────────────────────── */
2128
2129#[derive(Debug)]
2130pub struct FilterFn;
2131impl Function for FilterFn {
2132    func_caps!(PURE);
2133    fn name(&self) -> &'static str {
2134        "FILTER"
2135    }
2136    fn min_args(&self) -> usize {
2137        2
2138    }
2139    fn variadic(&self) -> bool {
2140        true
2141    }
2142    fn arg_schema(&self) -> &'static [ArgSchema] {
2143        use once_cell::sync::Lazy;
2144        static SCHEMA: Lazy<Vec<ArgSchema>> = Lazy::new(|| {
2145            vec![
2146                // array
2147                ArgSchema {
2148                    kinds: smallvec::smallvec![ArgKind::Range],
2149                    required: true,
2150                    by_ref: true,
2151                    shape: ShapeKind::Range,
2152                    coercion: CoercionPolicy::None,
2153                    max: None,
2154                    repeating: None,
2155                    default: None,
2156                },
2157                // include
2158                ArgSchema {
2159                    kinds: smallvec::smallvec![ArgKind::Range],
2160                    required: true,
2161                    by_ref: true,
2162                    shape: ShapeKind::Range,
2163                    coercion: CoercionPolicy::None,
2164                    max: None,
2165                    repeating: None,
2166                    default: None,
2167                },
2168                // if_empty optional scalar
2169                ArgSchema {
2170                    kinds: smallvec::smallvec![ArgKind::Any],
2171                    required: false,
2172                    by_ref: false,
2173                    shape: ShapeKind::Scalar,
2174                    coercion: CoercionPolicy::None,
2175                    max: None,
2176                    repeating: None,
2177                    default: None,
2178                },
2179            ]
2180        });
2181        &SCHEMA
2182    }
2183    fn eval<'a, 'b, 'c>(
2184        &self,
2185        args: &'c [ArgumentHandle<'a, 'b>],
2186        _ctx: &dyn FunctionContext<'b>,
2187    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
2188        if args.len() < 2 {
2189            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
2190                ExcelError::new(ExcelErrorKind::Value),
2191            )));
2192        }
2193        let array_view = args[0].range_view()?;
2194        let include_view = args[1].range_view()?;
2195
2196        let (array_rows, array_cols) = array_view.dims();
2197        if array_rows == 0 || array_cols == 0 {
2198            return Ok(crate::traits::CalcValue::Range(
2199                crate::engine::range_view::RangeView::from_owned_rows(vec![], _ctx.date_system()),
2200            ));
2201        }
2202
2203        let (include_rows, include_cols) = include_view.dims();
2204        if include_rows != array_rows && include_rows != 1 {
2205            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
2206                ExcelError::new(ExcelErrorKind::Value),
2207            )));
2208        }
2209
2210        let mut result: Vec<Vec<LiteralValue>> = Vec::new();
2211        for r in 0..array_rows {
2212            let include_r = if include_rows == array_rows { r } else { 0 };
2213            let mut include = false;
2214            for c in 0..include_cols {
2215                if include_view.get_cell(include_r, c).is_truthy() {
2216                    include = true;
2217                    break;
2218                }
2219            }
2220
2221            if include {
2222                let mut row_out: Vec<LiteralValue> = Vec::with_capacity(array_cols);
2223                for c in 0..array_cols {
2224                    row_out.push(array_view.get_cell(r, c));
2225                }
2226                result.push(row_out);
2227            }
2228        }
2229
2230        if result.is_empty() {
2231            if args.len() >= 3 {
2232                return args[2].value();
2233            }
2234            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
2235                ExcelError::new(ExcelErrorKind::Calc),
2236            )));
2237        }
2238
2239        Ok(crate::traits::CalcValue::Range(
2240            crate::engine::range_view::RangeView::from_owned_rows(result, _ctx.date_system()),
2241        ))
2242    }
2243}
2244
2245/* ───────────────────────── UNIQUE() ───────────────────────── */
2246
2247#[derive(Debug)]
2248pub struct UniqueFn;
2249impl Function for UniqueFn {
2250    func_caps!(PURE);
2251    fn name(&self) -> &'static str {
2252        "UNIQUE"
2253    }
2254    fn min_args(&self) -> usize {
2255        1
2256    }
2257    fn variadic(&self) -> bool {
2258        true
2259    }
2260    fn arg_schema(&self) -> &'static [ArgSchema] {
2261        use once_cell::sync::Lazy;
2262        static SCHEMA: Lazy<Vec<ArgSchema>> = Lazy::new(|| {
2263            vec![
2264                ArgSchema {
2265                    kinds: smallvec::smallvec![ArgKind::Range],
2266                    required: true,
2267                    by_ref: true,
2268                    shape: ShapeKind::Range,
2269                    coercion: CoercionPolicy::None,
2270                    max: None,
2271                    repeating: None,
2272                    default: None,
2273                },
2274                ArgSchema {
2275                    kinds: smallvec::smallvec![ArgKind::Logical],
2276                    required: false,
2277                    by_ref: false,
2278                    shape: ShapeKind::Scalar,
2279                    coercion: CoercionPolicy::Logical,
2280                    max: None,
2281                    repeating: None,
2282                    default: Some(LiteralValue::Boolean(false)),
2283                },
2284                ArgSchema {
2285                    kinds: smallvec::smallvec![ArgKind::Logical],
2286                    required: false,
2287                    by_ref: false,
2288                    shape: ShapeKind::Scalar,
2289                    coercion: CoercionPolicy::Logical,
2290                    max: None,
2291                    repeating: None,
2292                    default: Some(LiteralValue::Boolean(false)),
2293                },
2294            ]
2295        });
2296        &SCHEMA
2297    }
2298    fn eval<'a, 'b, 'c>(
2299        &self,
2300        args: &'c [ArgumentHandle<'a, 'b>],
2301        _ctx: &dyn FunctionContext<'b>,
2302    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
2303        let view = match args[0].range_view() {
2304            Ok(v) => v,
2305            Err(e) => return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(e))),
2306        };
2307        let (rows, cols) = view.dims();
2308        if rows == 0 || cols == 0 {
2309            return Ok(crate::traits::CalcValue::Range(
2310                crate::engine::range_view::RangeView::from_owned_rows(vec![], _ctx.date_system()),
2311            ));
2312        }
2313
2314        let by_col = if args.len() >= 2 {
2315            matches!(args[1].value()?.into_literal(), LiteralValue::Boolean(true))
2316        } else {
2317            false
2318        };
2319        let exactly_once = if args.len() >= 3 {
2320            matches!(args[2].value()?.into_literal(), LiteralValue::Boolean(true))
2321        } else {
2322            false
2323        };
2324
2325        if by_col {
2326            #[derive(Hash, Eq, PartialEq, Clone)]
2327            struct ColKey(Vec<LiteralValue>);
2328
2329            let mut order: Vec<ColKey> = Vec::new();
2330            let mut counts: HashMap<ColKey, usize> = HashMap::new();
2331
2332            for c in 0..cols {
2333                let mut col_vals: Vec<LiteralValue> = Vec::with_capacity(rows);
2334                for r in 0..rows {
2335                    col_vals.push(view.get_cell(r, c));
2336                }
2337                let key = ColKey(col_vals);
2338                if !counts.contains_key(&key) {
2339                    order.push(key.clone());
2340                }
2341                *counts.entry(key).or_insert(0) += 1;
2342            }
2343
2344            let mut out: Vec<Vec<LiteralValue>> = Vec::new();
2345            for k in order {
2346                if !exactly_once || counts.get(&k) == Some(&1) {
2347                    out.push(k.0);
2348                }
2349            }
2350            return Ok(collapse_if_scalar(out, _ctx.date_system()));
2351        }
2352
2353        #[derive(Hash, Eq, PartialEq, Clone)]
2354        struct RowKey(Vec<LiteralValue>);
2355
2356        let mut order: Vec<RowKey> = Vec::new();
2357        let mut counts: HashMap<RowKey, usize> = HashMap::new();
2358        for r in 0..rows {
2359            let mut row_vals: Vec<LiteralValue> = Vec::with_capacity(cols);
2360            for c in 0..cols {
2361                row_vals.push(view.get_cell(r, c));
2362            }
2363            let key = RowKey(row_vals);
2364            if !counts.contains_key(&key) {
2365                order.push(key.clone());
2366            }
2367            *counts.entry(key).or_insert(0) += 1;
2368        }
2369
2370        let mut out: Vec<Vec<LiteralValue>> = Vec::new();
2371        for k in order {
2372            if !exactly_once || counts.get(&k) == Some(&1) {
2373                out.push(k.0);
2374            }
2375        }
2376        Ok(collapse_if_scalar(out, _ctx.date_system()))
2377    }
2378}
2379
2380/* ───────────────────────── SEQUENCE() ───────────────────────── */
2381
2382#[derive(Debug)]
2383pub struct SequenceFn;
2384impl Function for SequenceFn {
2385    func_caps!(PURE);
2386    fn name(&self) -> &'static str {
2387        "SEQUENCE"
2388    }
2389    fn min_args(&self) -> usize {
2390        1
2391    }
2392    fn variadic(&self) -> bool {
2393        true
2394    }
2395    fn arg_schema(&self) -> &'static [ArgSchema] {
2396        use once_cell::sync::Lazy;
2397        static SCHEMA: Lazy<Vec<ArgSchema>> = Lazy::new(|| {
2398            vec![
2399                // rows
2400                ArgSchema {
2401                    kinds: smallvec::smallvec![ArgKind::Number],
2402                    required: true,
2403                    by_ref: false,
2404                    shape: ShapeKind::Scalar,
2405                    coercion: CoercionPolicy::NumberLenientText,
2406                    max: None,
2407                    repeating: None,
2408                    default: None,
2409                },
2410                // columns (default 1)
2411                ArgSchema {
2412                    kinds: smallvec::smallvec![ArgKind::Number],
2413                    required: false,
2414                    by_ref: false,
2415                    shape: ShapeKind::Scalar,
2416                    coercion: CoercionPolicy::NumberLenientText,
2417                    max: None,
2418                    repeating: None,
2419                    default: Some(LiteralValue::Int(1)),
2420                },
2421                // start (default 1)
2422                ArgSchema {
2423                    kinds: smallvec::smallvec![ArgKind::Number],
2424                    required: false,
2425                    by_ref: false,
2426                    shape: ShapeKind::Scalar,
2427                    coercion: CoercionPolicy::NumberLenientText,
2428                    max: None,
2429                    repeating: None,
2430                    default: Some(LiteralValue::Int(1)),
2431                },
2432                // step (default 1)
2433                ArgSchema {
2434                    kinds: smallvec::smallvec![ArgKind::Number],
2435                    required: false,
2436                    by_ref: false,
2437                    shape: ShapeKind::Scalar,
2438                    coercion: CoercionPolicy::NumberLenientText,
2439                    max: None,
2440                    repeating: None,
2441                    default: Some(LiteralValue::Int(1)),
2442                },
2443            ]
2444        });
2445        &SCHEMA
2446    }
2447    fn eval<'a, 'b, 'c>(
2448        &self,
2449        args: &'c [ArgumentHandle<'a, 'b>],
2450        _ctx: &dyn FunctionContext<'b>,
2451    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
2452        // Extract numbers (allow float but coerce to i64 for dimensions)
2453        let num = |a: &ArgumentHandle| -> Result<f64, ExcelError> {
2454            Ok(match a.value()?.into_literal() {
2455                LiteralValue::Int(i) => i as f64,
2456                LiteralValue::Number(n) => n,
2457                _other => {
2458                    return Err(ExcelError::new(ExcelErrorKind::Value));
2459                }
2460            })
2461        };
2462        let rows_f = num(&args[0])?;
2463        let rows = rows_f as i64;
2464        let cols = if args.len() >= 2 {
2465            num(&args[1])? as i64
2466        } else {
2467            1
2468        };
2469        let start = if args.len() >= 3 { num(&args[2])? } else { 1.0 };
2470        let step = if args.len() >= 4 { num(&args[3])? } else { 1.0 };
2471        if rows <= 0 || cols <= 0 {
2472            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
2473                ExcelError::new(ExcelErrorKind::Value),
2474            )));
2475        }
2476        let total = rows.saturating_mul(cols);
2477        // TODO(perf): guard extremely large allocations (#NUM!).
2478        let mut out: Vec<Vec<LiteralValue>> = Vec::with_capacity(rows as usize);
2479        let mut current = start;
2480        for _r in 0..rows {
2481            let mut row_vec: Vec<LiteralValue> = Vec::with_capacity(cols as usize);
2482            for _c in 0..cols {
2483                // Use Int when value integral & within i64 range
2484                if (current.fract().abs() < 1e-12) && current.abs() < (i64::MAX as f64) {
2485                    row_vec.push(LiteralValue::Int(current as i64));
2486                } else {
2487                    row_vec.push(LiteralValue::Number(current));
2488                }
2489                current += step;
2490            }
2491            out.push(row_vec);
2492        }
2493
2494        Ok(collapse_if_scalar(out, _ctx.date_system()))
2495    }
2496}
2497
2498/* ───────────────────────── TRANSPOSE() ───────────────────────── */
2499
2500#[derive(Debug)]
2501pub struct TransposeFn;
2502impl Function for TransposeFn {
2503    func_caps!(PURE);
2504    fn name(&self) -> &'static str {
2505        "TRANSPOSE"
2506    }
2507    fn min_args(&self) -> usize {
2508        1
2509    }
2510    fn variadic(&self) -> bool {
2511        false
2512    }
2513    fn arg_schema(&self) -> &'static [ArgSchema] {
2514        use once_cell::sync::Lazy;
2515        static SCHEMA: Lazy<Vec<ArgSchema>> = Lazy::new(|| {
2516            vec![ArgSchema {
2517                kinds: smallvec::smallvec![ArgKind::Range],
2518                required: true,
2519                by_ref: true,
2520                shape: ShapeKind::Range,
2521                coercion: CoercionPolicy::None,
2522                max: None,
2523                repeating: None,
2524                default: None,
2525            }]
2526        });
2527        &SCHEMA
2528    }
2529    fn eval<'a, 'b, 'c>(
2530        &self,
2531        args: &'c [ArgumentHandle<'a, 'b>],
2532        _ctx: &dyn FunctionContext<'b>,
2533    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
2534        let view = match args[0].range_view() {
2535            Ok(v) => v,
2536            Err(e) => return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(e))),
2537        };
2538        let (rows, cols) = view.dims();
2539        if rows == 0 || cols == 0 {
2540            return Ok(crate::traits::CalcValue::Range(
2541                crate::engine::range_view::RangeView::from_owned_rows(vec![], _ctx.date_system()),
2542            ));
2543        }
2544
2545        let mut out: Vec<Vec<LiteralValue>> = vec![Vec::with_capacity(rows); cols];
2546        for (c, col) in out.iter_mut().enumerate().take(cols) {
2547            for r in 0..rows {
2548                col.push(view.get_cell(r, c));
2549            }
2550        }
2551        Ok(collapse_if_scalar(out, _ctx.date_system()))
2552    }
2553}
2554
2555/* ───────────────────────── TAKE() ───────────────────────── */
2556
2557#[derive(Debug)]
2558pub struct TakeFn;
2559impl Function for TakeFn {
2560    func_caps!(PURE);
2561    fn name(&self) -> &'static str {
2562        "TAKE"
2563    }
2564    fn min_args(&self) -> usize {
2565        2
2566    }
2567    fn variadic(&self) -> bool {
2568        true
2569    }
2570    fn arg_schema(&self) -> &'static [ArgSchema] {
2571        use once_cell::sync::Lazy;
2572        static SCHEMA: Lazy<Vec<ArgSchema>> = Lazy::new(|| {
2573            vec![
2574                ArgSchema {
2575                    kinds: smallvec::smallvec![ArgKind::Range],
2576                    required: true,
2577                    by_ref: true,
2578                    shape: ShapeKind::Range,
2579                    coercion: CoercionPolicy::None,
2580                    max: None,
2581                    repeating: None,
2582                    default: None,
2583                },
2584                ArgSchema {
2585                    kinds: smallvec::smallvec![ArgKind::Number],
2586                    required: true,
2587                    by_ref: false,
2588                    shape: ShapeKind::Scalar,
2589                    coercion: CoercionPolicy::NumberLenientText,
2590                    max: None,
2591                    repeating: None,
2592                    default: None,
2593                },
2594                ArgSchema {
2595                    kinds: smallvec::smallvec![ArgKind::Number],
2596                    required: false,
2597                    by_ref: false,
2598                    shape: ShapeKind::Scalar,
2599                    coercion: CoercionPolicy::NumberLenientText,
2600                    max: None,
2601                    repeating: None,
2602                    default: None,
2603                },
2604            ]
2605        });
2606        &SCHEMA
2607    }
2608    fn eval<'a, 'b, 'c>(
2609        &self,
2610        args: &'c [ArgumentHandle<'a, 'b>],
2611        _ctx: &dyn FunctionContext<'b>,
2612    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
2613        let view = match args[0].range_view() {
2614            Ok(v) => v,
2615            Err(e) => return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(e))),
2616        };
2617        let (rows, cols) = view.dims();
2618        if rows == 0 || cols == 0 {
2619            return Ok(crate::traits::CalcValue::Range(
2620                crate::engine::range_view::RangeView::from_owned_rows(vec![], _ctx.date_system()),
2621            ));
2622        }
2623
2624        let height = rows as i64;
2625        let width = cols as i64;
2626
2627        let num = |a: &ArgumentHandle| -> Result<i64, ExcelError> {
2628            Ok(match a.value()?.into_literal() {
2629                LiteralValue::Int(i) => i,
2630                LiteralValue::Number(n) => n as i64,
2631                _ => 0,
2632            })
2633        };
2634        let take_rows = num(&args[1])?;
2635        let take_cols = if args.len() >= 3 {
2636            Some(num(&args[2])?)
2637        } else {
2638            None
2639        };
2640
2641        if take_rows.abs() > height {
2642            return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
2643                ExcelError::new(ExcelErrorKind::Value),
2644            )));
2645        }
2646
2647        let (row_start, row_end) = if take_rows >= 0 {
2648            (0usize, take_rows as usize)
2649        } else {
2650            ((height + take_rows) as usize, height as usize)
2651        };
2652
2653        let (col_start, col_end) = if let Some(tc) = take_cols {
2654            if tc.abs() > width {
2655                return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(
2656                    ExcelError::new(ExcelErrorKind::Value),
2657                )));
2658            }
2659            if tc >= 0 {
2660                (0usize, tc as usize)
2661            } else {
2662                ((width + tc) as usize, width as usize)
2663            }
2664        } else {
2665            (0usize, width as usize)
2666        };
2667
2668        if row_start >= row_end || col_start >= col_end {
2669            return Ok(crate::traits::CalcValue::Range(
2670                crate::engine::range_view::RangeView::from_owned_rows(vec![], _ctx.date_system()),
2671            ));
2672        }
2673
2674        let mut out: Vec<Vec<LiteralValue>> = Vec::with_capacity(row_end - row_start);
2675        for r in row_start..row_end {
2676            let mut row_out: Vec<LiteralValue> = Vec::with_capacity(col_end - col_start);
2677            for c in col_start..col_end {
2678                row_out.push(view.get_cell(r, c));
2679            }
2680            out.push(row_out);
2681        }
2682
2683        Ok(collapse_if_scalar(out, _ctx.date_system()))
2684    }
2685}
2686
2687/* ───────────────────────── DROP() ───────────────────────── */
2688
2689#[derive(Debug)]
2690pub struct DropFn;
2691impl Function for DropFn {
2692    func_caps!(PURE);
2693    fn name(&self) -> &'static str {
2694        "DROP"
2695    }
2696    fn min_args(&self) -> usize {
2697        2
2698    }
2699    fn variadic(&self) -> bool {
2700        true
2701    }
2702    fn arg_schema(&self) -> &'static [ArgSchema] {
2703        use once_cell::sync::Lazy;
2704        static SCHEMA: Lazy<Vec<ArgSchema>> = Lazy::new(|| {
2705            vec![
2706                ArgSchema {
2707                    kinds: smallvec::smallvec![ArgKind::Range],
2708                    required: true,
2709                    by_ref: true,
2710                    shape: ShapeKind::Range,
2711                    coercion: CoercionPolicy::None,
2712                    max: None,
2713                    repeating: None,
2714                    default: None,
2715                },
2716                ArgSchema {
2717                    kinds: smallvec::smallvec![ArgKind::Number],
2718                    required: true,
2719                    by_ref: false,
2720                    shape: ShapeKind::Scalar,
2721                    coercion: CoercionPolicy::NumberLenientText,
2722                    max: None,
2723                    repeating: None,
2724                    default: None,
2725                },
2726                ArgSchema {
2727                    kinds: smallvec::smallvec![ArgKind::Number],
2728                    required: false,
2729                    by_ref: false,
2730                    shape: ShapeKind::Scalar,
2731                    coercion: CoercionPolicy::NumberLenientText,
2732                    max: None,
2733                    repeating: None,
2734                    default: None,
2735                },
2736            ]
2737        });
2738        &SCHEMA
2739    }
2740    fn eval<'a, 'b, 'c>(
2741        &self,
2742        args: &'c [ArgumentHandle<'a, 'b>],
2743        _ctx: &dyn FunctionContext<'b>,
2744    ) -> Result<crate::traits::CalcValue<'b>, ExcelError> {
2745        let view = match args[0].range_view() {
2746            Ok(v) => v,
2747            Err(e) => return Ok(crate::traits::CalcValue::Scalar(LiteralValue::Error(e))),
2748        };
2749        let (rows, cols) = view.dims();
2750        if rows == 0 || cols == 0 {
2751            return Ok(crate::traits::CalcValue::Range(
2752                crate::engine::range_view::RangeView::from_owned_rows(vec![], _ctx.date_system()),
2753            ));
2754        }
2755
2756        let height = rows as i64;
2757        let width = cols as i64;
2758
2759        let num = |a: &ArgumentHandle| -> Result<i64, ExcelError> {
2760            Ok(match a.value()?.into_literal() {
2761                LiteralValue::Int(i) => i,
2762                LiteralValue::Number(n) => n as i64,
2763                _ => 0,
2764            })
2765        };
2766        let drop_rows = num(&args[1])?;
2767        let drop_cols = if args.len() >= 3 {
2768            Some(num(&args[2])?)
2769        } else {
2770            None
2771        };
2772
2773        let (row_start, row_end) = if drop_rows >= 0 {
2774            ((drop_rows as usize).min(height as usize), height as usize)
2775        } else {
2776            (0usize, (height + drop_rows).max(0) as usize)
2777        };
2778
2779        let (col_start, col_end) = if let Some(dc) = drop_cols {
2780            if dc >= 0 {
2781                ((dc as usize).min(width as usize), width as usize)
2782            } else {
2783                (0usize, (width + dc).max(0) as usize)
2784            }
2785        } else {
2786            (0usize, width as usize)
2787        };
2788
2789        if row_start >= row_end || col_start >= col_end {
2790            return Ok(crate::traits::CalcValue::Range(
2791                crate::engine::range_view::RangeView::from_owned_rows(vec![], _ctx.date_system()),
2792            ));
2793        }
2794
2795        let mut out: Vec<Vec<LiteralValue>> = Vec::with_capacity(row_end - row_start);
2796        for r in row_start..row_end {
2797            let mut row_out: Vec<LiteralValue> = Vec::with_capacity(col_end - col_start);
2798            for c in col_start..col_end {
2799                row_out.push(view.get_cell(r, c));
2800            }
2801            out.push(row_out);
2802        }
2803
2804        Ok(collapse_if_scalar(out, _ctx.date_system()))
2805    }
2806}
2807
2808pub fn register_builtins() {
2809    use crate::function_registry::register_function;
2810    use std::sync::Arc;
2811    register_function(Arc::new(XLookupFn));
2812    register_function(Arc::new(FilterFn));
2813    register_function(Arc::new(UniqueFn));
2814    register_function(Arc::new(SequenceFn));
2815    register_function(Arc::new(TransposeFn));
2816    register_function(Arc::new(TakeFn));
2817    register_function(Arc::new(DropFn));
2818    register_function(Arc::new(XMatchFn));
2819    register_function(Arc::new(SortFn));
2820    register_function(Arc::new(SortByFn));
2821    register_function(Arc::new(RandArrayFn));
2822    register_function(Arc::new(GroupByFn));
2823    register_function(Arc::new(PivotByFn));
2824}
2825
2826/* ───────────────────────── tests ───────────────────────── */
2827
2828#[cfg(test)]
2829mod tests {
2830    use super::*;
2831    use crate::test_workbook::TestWorkbook;
2832    use crate::traits::ArgumentHandle;
2833    use formualizer_parse::parser::{ASTNode, ASTNodeType, ReferenceType};
2834    use std::sync::Arc;
2835
2836    #[test]
2837    fn test_all_dynamic_functions_registered() {
2838        // Ensure builtins are registered
2839        crate::builtins::load_builtins();
2840
2841        let functions = [
2842            "XLOOKUP",
2843            "FILTER",
2844            "UNIQUE",
2845            "SEQUENCE",
2846            "TRANSPOSE",
2847            "TAKE",
2848            "DROP",
2849            "XMATCH",
2850            "SORT",
2851            "SORTBY",
2852            "RANDARRAY",
2853            "GROUPBY",
2854            "PIVOTBY",
2855        ];
2856
2857        for name in &functions {
2858            let result = crate::function_registry::get("", name);
2859            assert!(result.is_some(), "Function {} should be registered", name);
2860        }
2861    }
2862
2863    fn lit(v: LiteralValue) -> ASTNode {
2864        ASTNode::new(ASTNodeType::Literal(v), None)
2865    }
2866
2867    fn range(r: &str, sr: u32, sc: u32, er: u32, ec: u32) -> ASTNode {
2868        ASTNode::new(
2869            ASTNodeType::Reference {
2870                original: r.into(),
2871                reference: ReferenceType::range(None, Some(sr), Some(sc), Some(er), Some(ec)),
2872            },
2873            None,
2874        )
2875    }
2876
2877    #[test]
2878    fn xlookup_basic_exact_and_if_not_found() {
2879        let wb = TestWorkbook::new().with_function(Arc::new(XLookupFn));
2880        let wb = wb
2881            .with_cell_a1("Sheet1", "A1", LiteralValue::Text("a".into()))
2882            .with_cell_a1("Sheet1", "A2", LiteralValue::Text("b".into()))
2883            .with_cell_a1("Sheet1", "B1", LiteralValue::Int(10))
2884            .with_cell_a1("Sheet1", "B2", LiteralValue::Int(20));
2885        let ctx = wb.interpreter();
2886        let lookup_range = range("A1:A2", 1, 1, 2, 1);
2887        let return_range = range("B1:B2", 1, 2, 2, 2);
2888        let f = ctx.context.get_function("", "XLOOKUP").unwrap();
2889        let key_b = lit(LiteralValue::Text("b".into()));
2890        let args = vec![
2891            ArgumentHandle::new(&key_b, &ctx),
2892            ArgumentHandle::new(&lookup_range, &ctx),
2893            ArgumentHandle::new(&return_range, &ctx),
2894        ];
2895        let v = f
2896            .dispatch(&args, &ctx.function_context(None))
2897            .unwrap()
2898            .into_literal();
2899        assert_eq!(v, LiteralValue::Number(20.0));
2900        let key_missing = lit(LiteralValue::Text("z".into()));
2901        let if_nf = lit(LiteralValue::Text("NF".into()));
2902        let args_nf = vec![
2903            ArgumentHandle::new(&key_missing, &ctx),
2904            ArgumentHandle::new(&lookup_range, &ctx),
2905            ArgumentHandle::new(&return_range, &ctx),
2906            ArgumentHandle::new(&if_nf, &ctx),
2907        ];
2908        let v_nf = f
2909            .dispatch(&args_nf, &ctx.function_context(None))
2910            .unwrap()
2911            .into_literal();
2912        assert_eq!(v_nf, LiteralValue::Text("NF".into()));
2913    }
2914
2915    #[test]
2916    fn xlookup_match_modes_next_smaller_larger() {
2917        let wb = TestWorkbook::new().with_function(Arc::new(XLookupFn));
2918        let wb = wb
2919            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(10))
2920            .with_cell_a1("Sheet1", "A2", LiteralValue::Int(20))
2921            .with_cell_a1("Sheet1", "A3", LiteralValue::Int(30))
2922            .with_cell_a1("Sheet1", "B1", LiteralValue::Int(1))
2923            .with_cell_a1("Sheet1", "B2", LiteralValue::Int(2))
2924            .with_cell_a1("Sheet1", "B3", LiteralValue::Int(3));
2925        let ctx = wb.interpreter();
2926        let lookup_range = range("A1:A3", 1, 1, 3, 1);
2927        let return_range = range("B1:B3", 1, 2, 3, 2);
2928        let f = ctx.context.get_function("", "XLOOKUP").unwrap();
2929        let needle_25 = lit(LiteralValue::Int(25));
2930        let mm_next_smaller = lit(LiteralValue::Int(-1));
2931        let nf_text = lit(LiteralValue::Text("NF".into()));
2932        let args_smaller = vec![
2933            ArgumentHandle::new(&needle_25, &ctx),
2934            ArgumentHandle::new(&lookup_range, &ctx),
2935            ArgumentHandle::new(&return_range, &ctx),
2936            ArgumentHandle::new(&nf_text, &ctx),
2937            ArgumentHandle::new(&mm_next_smaller, &ctx),
2938        ];
2939        let v_smaller = f
2940            .dispatch(&args_smaller, &ctx.function_context(None))
2941            .unwrap()
2942            .into_literal();
2943        assert_eq!(v_smaller, LiteralValue::Number(2.0));
2944        let mm_next_larger = lit(LiteralValue::Int(1));
2945        let nf_text2 = lit(LiteralValue::Text("NF".into()));
2946        let args_larger = vec![
2947            ArgumentHandle::new(&needle_25, &ctx),
2948            ArgumentHandle::new(&lookup_range, &ctx),
2949            ArgumentHandle::new(&return_range, &ctx),
2950            ArgumentHandle::new(&nf_text2, &ctx),
2951            ArgumentHandle::new(&mm_next_larger, &ctx),
2952        ];
2953        let v_larger = f
2954            .dispatch(&args_larger, &ctx.function_context(None))
2955            .unwrap()
2956            .into_literal();
2957        assert_eq!(v_larger, LiteralValue::Number(3.0));
2958    }
2959
2960    #[test]
2961    fn xlookup_wildcard_and_not_found_default_na() {
2962        let wb = TestWorkbook::new().with_function(Arc::new(XLookupFn));
2963        let wb = wb
2964            .with_cell_a1("Sheet1", "A1", LiteralValue::Text("Alpha".into()))
2965            .with_cell_a1("Sheet1", "A2", LiteralValue::Text("Beta".into()))
2966            .with_cell_a1("Sheet1", "A3", LiteralValue::Text("Gamma".into()))
2967            .with_cell_a1("Sheet1", "B1", LiteralValue::Int(100))
2968            .with_cell_a1("Sheet1", "B2", LiteralValue::Int(200))
2969            .with_cell_a1("Sheet1", "B3", LiteralValue::Int(300));
2970        let ctx = wb.interpreter();
2971        let lookup_range = range("A1:A3", 1, 1, 3, 1);
2972        let return_range = range("B1:B3", 1, 2, 3, 2);
2973        let f = ctx.context.get_function("", "XLOOKUP").unwrap();
2974        // Wildcard should match Beta (*et*) with match_mode 2
2975        let pattern = lit(LiteralValue::Text("*et*".into()));
2976        let match_mode_wild = lit(LiteralValue::Int(2));
2977        let nf_binding = lit(LiteralValue::Text("NF".into()));
2978        let args_wild = vec![
2979            ArgumentHandle::new(&pattern, &ctx),
2980            ArgumentHandle::new(&lookup_range, &ctx),
2981            ArgumentHandle::new(&return_range, &ctx),
2982            ArgumentHandle::new(&nf_binding, &ctx),
2983            ArgumentHandle::new(&match_mode_wild, &ctx),
2984        ];
2985        let v_wild = f
2986            .dispatch(&args_wild, &ctx.function_context(None))
2987            .unwrap()
2988            .into_literal();
2989        assert_eq!(v_wild, LiteralValue::Number(200.0));
2990        // Escaped wildcard literal ~* should not match Beta
2991        let pattern_lit_star = lit(LiteralValue::Text("~*eta".into()));
2992        let args_lit = vec![
2993            ArgumentHandle::new(&pattern_lit_star, &ctx),
2994            ArgumentHandle::new(&lookup_range, &ctx),
2995            ArgumentHandle::new(&return_range, &ctx),
2996            ArgumentHandle::new(&nf_binding, &ctx),
2997            ArgumentHandle::new(&match_mode_wild, &ctx),
2998        ];
2999        let v_lit = f
3000            .dispatch(&args_lit, &ctx.function_context(None))
3001            .unwrap()
3002            .into_literal();
3003        match v_lit {
3004            LiteralValue::Text(s) => assert_eq!(s, "NF"),
3005            other => panic!("expected NF text got {other:?}"),
3006        }
3007        // Not found without if_not_found -> #N/A
3008        let missing = lit(LiteralValue::Text("Zeta".into()));
3009        let args_nf = vec![
3010            ArgumentHandle::new(&missing, &ctx),
3011            ArgumentHandle::new(&lookup_range, &ctx),
3012            ArgumentHandle::new(&return_range, &ctx),
3013        ];
3014        let v_nf = f
3015            .dispatch(&args_nf, &ctx.function_context(None))
3016            .unwrap()
3017            .into_literal();
3018        match v_nf {
3019            LiteralValue::Error(e) => assert_eq!(e.kind, ExcelErrorKind::Na),
3020            other => panic!("expected #N/A got {other:?}"),
3021        }
3022    }
3023
3024    #[test]
3025    fn xlookup_reverse_search_mode_picks_last() {
3026        let wb = TestWorkbook::new().with_function(Arc::new(XLookupFn));
3027        let wb = wb
3028            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(1))
3029            .with_cell_a1("Sheet1", "A2", LiteralValue::Int(2))
3030            .with_cell_a1("Sheet1", "A3", LiteralValue::Int(1))
3031            .with_cell_a1("Sheet1", "B1", LiteralValue::Text("First".into()))
3032            .with_cell_a1("Sheet1", "B2", LiteralValue::Text("Mid".into()))
3033            .with_cell_a1("Sheet1", "B3", LiteralValue::Text("Last".into()));
3034        let ctx = wb.interpreter();
3035        let lookup_range = range("A1:A3", 1, 1, 3, 1);
3036        let return_range = range("B1:B3", 1, 2, 3, 2);
3037        let f = ctx.context.get_function("", "XLOOKUP").unwrap();
3038        let needle_one = lit(LiteralValue::Int(1));
3039        let search_rev = lit(LiteralValue::Int(-1));
3040        let nf_binding2 = lit(LiteralValue::Text("NF".into()));
3041        let match_mode_zero = lit(LiteralValue::Int(0));
3042        let args_rev = vec![
3043            ArgumentHandle::new(&needle_one, &ctx),
3044            ArgumentHandle::new(&lookup_range, &ctx),
3045            ArgumentHandle::new(&return_range, &ctx),
3046            ArgumentHandle::new(&nf_binding2, &ctx),
3047            /* match_mode default */ ArgumentHandle::new(&match_mode_zero, &ctx),
3048            ArgumentHandle::new(&search_rev, &ctx),
3049        ];
3050        let v_rev = f
3051            .dispatch(&args_rev, &ctx.function_context(None))
3052            .unwrap()
3053            .into_literal();
3054        assert_eq!(v_rev, LiteralValue::Text("Last".into()));
3055    }
3056
3057    #[test]
3058    fn xlookup_horizontal_returns_column_vector_for_matrix_return() {
3059        let wb = TestWorkbook::new().with_function(Arc::new(XLookupFn));
3060        let wb = wb
3061            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(10))
3062            .with_cell_a1("Sheet1", "B1", LiteralValue::Int(20))
3063            .with_cell_a1("Sheet1", "C1", LiteralValue::Int(30))
3064            .with_cell_a1("Sheet1", "A2", LiteralValue::Int(1))
3065            .with_cell_a1("Sheet1", "B2", LiteralValue::Int(2))
3066            .with_cell_a1("Sheet1", "C2", LiteralValue::Int(3))
3067            .with_cell_a1("Sheet1", "A3", LiteralValue::Int(4))
3068            .with_cell_a1("Sheet1", "B3", LiteralValue::Int(5))
3069            .with_cell_a1("Sheet1", "C3", LiteralValue::Int(6));
3070        let ctx = wb.interpreter();
3071        let lookup_range = range("A1:C1", 1, 1, 1, 3);
3072        let return_range = range("A2:C3", 2, 1, 3, 3);
3073        let f = ctx.context.get_function("", "XLOOKUP").unwrap();
3074        let needle = lit(LiteralValue::Int(20));
3075        let args = vec![
3076            ArgumentHandle::new(&needle, &ctx),
3077            ArgumentHandle::new(&lookup_range, &ctx),
3078            ArgumentHandle::new(&return_range, &ctx),
3079        ];
3080        let v = f
3081            .dispatch(&args, &ctx.function_context(None))
3082            .unwrap()
3083            .into_literal();
3084        match v {
3085            LiteralValue::Array(a) => {
3086                assert_eq!(
3087                    a,
3088                    vec![
3089                        vec![LiteralValue::Number(2.0)],
3090                        vec![LiteralValue::Number(5.0)]
3091                    ]
3092                );
3093            }
3094            other => panic!("expected array got {other:?}"),
3095        }
3096    }
3097
3098    #[test]
3099    fn xlookup_vertical_returns_row_vector_for_matrix_return() {
3100        let wb = TestWorkbook::new().with_function(Arc::new(XLookupFn));
3101        let wb = wb
3102            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(10))
3103            .with_cell_a1("Sheet1", "A2", LiteralValue::Int(20))
3104            .with_cell_a1("Sheet1", "A3", LiteralValue::Int(30))
3105            .with_cell_a1("Sheet1", "B1", LiteralValue::Int(101))
3106            .with_cell_a1("Sheet1", "C1", LiteralValue::Int(102))
3107            .with_cell_a1("Sheet1", "D1", LiteralValue::Int(103))
3108            .with_cell_a1("Sheet1", "B2", LiteralValue::Int(201))
3109            .with_cell_a1("Sheet1", "C2", LiteralValue::Int(202))
3110            .with_cell_a1("Sheet1", "D2", LiteralValue::Int(203))
3111            .with_cell_a1("Sheet1", "B3", LiteralValue::Int(301))
3112            .with_cell_a1("Sheet1", "C3", LiteralValue::Int(302))
3113            .with_cell_a1("Sheet1", "D3", LiteralValue::Int(303));
3114        let ctx = wb.interpreter();
3115        let lookup_range = range("A1:A3", 1, 1, 3, 1);
3116        let return_range = range("B1:D3", 1, 2, 3, 4);
3117        let f = ctx.context.get_function("", "XLOOKUP").unwrap();
3118        let needle = lit(LiteralValue::Int(20));
3119        let args = vec![
3120            ArgumentHandle::new(&needle, &ctx),
3121            ArgumentHandle::new(&lookup_range, &ctx),
3122            ArgumentHandle::new(&return_range, &ctx),
3123        ];
3124        let v = f
3125            .dispatch(&args, &ctx.function_context(None))
3126            .unwrap()
3127            .into_literal();
3128        match v {
3129            LiteralValue::Array(a) => {
3130                assert_eq!(
3131                    a,
3132                    vec![vec![
3133                        LiteralValue::Number(201.0),
3134                        LiteralValue::Number(202.0),
3135                        LiteralValue::Number(203.0)
3136                    ]]
3137                );
3138            }
3139            other => panic!("expected array got {other:?}"),
3140        }
3141    }
3142
3143    #[test]
3144    fn filter_basic_and_if_empty() {
3145        let wb = TestWorkbook::new().with_function(Arc::new(FilterFn));
3146        let wb = wb
3147            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(1))
3148            .with_cell_a1("Sheet1", "A2", LiteralValue::Int(2))
3149            .with_cell_a1("Sheet1", "B1", LiteralValue::Int(10))
3150            .with_cell_a1("Sheet1", "B2", LiteralValue::Int(20))
3151            .with_cell_a1("Sheet1", "C1", LiteralValue::Boolean(true))
3152            .with_cell_a1("Sheet1", "C2", LiteralValue::Boolean(false));
3153        let ctx = wb.interpreter();
3154        let array_range = range("A1:B2", 1, 1, 2, 2);
3155        let include_range = range("C1:C2", 1, 3, 2, 3);
3156        let f = ctx.context.get_function("", "FILTER").unwrap();
3157        let args = vec![
3158            ArgumentHandle::new(&array_range, &ctx),
3159            ArgumentHandle::new(&include_range, &ctx),
3160        ];
3161        let v = f
3162            .dispatch(&args, &ctx.function_context(None))
3163            .unwrap()
3164            .into_literal();
3165        match v {
3166            LiteralValue::Array(a) => {
3167                assert_eq!(a.len(), 1);
3168                assert_eq!(
3169                    a[0],
3170                    vec![LiteralValue::Number(1.0), LiteralValue::Number(10.0)]
3171                );
3172            }
3173            other => panic!("expected array got {other:?}"),
3174        }
3175        let wb2 = wb
3176            .with_cell_a1("Sheet1", "C1", LiteralValue::Boolean(false))
3177            .with_cell_a1("Sheet1", "C2", LiteralValue::Boolean(false));
3178        let ctx2 = wb2.interpreter();
3179        let f2 = ctx2.context.get_function("", "FILTER").unwrap();
3180        let empty_text = lit(LiteralValue::Text("EMPTY".into()));
3181        let args_empty = vec![
3182            ArgumentHandle::new(&array_range, &ctx2),
3183            ArgumentHandle::new(&include_range, &ctx2),
3184            ArgumentHandle::new(&empty_text, &ctx2),
3185        ];
3186        let v_empty = f2
3187            .dispatch(&args_empty, &ctx2.function_context(None))
3188            .unwrap()
3189            .into_literal();
3190        assert_eq!(v_empty, LiteralValue::Text("EMPTY".into()));
3191    }
3192
3193    #[test]
3194    fn unique_basic_and_exactly_once() {
3195        let wb = TestWorkbook::new().with_function(Arc::new(UniqueFn));
3196        let wb = wb
3197            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(1))
3198            .with_cell_a1("Sheet1", "A2", LiteralValue::Int(1))
3199            .with_cell_a1("Sheet1", "A3", LiteralValue::Int(2))
3200            .with_cell_a1("Sheet1", "A4", LiteralValue::Int(3));
3201        let ctx = wb.interpreter();
3202        let range = range("A1:A4", 1, 1, 4, 1);
3203        let f = ctx.context.get_function("", "UNIQUE").unwrap();
3204        let args = vec![ArgumentHandle::new(&range, &ctx)];
3205        let v = f
3206            .dispatch(&args, &ctx.function_context(None))
3207            .unwrap()
3208            .into_literal();
3209        match v {
3210            LiteralValue::Array(a) => {
3211                assert_eq!(a.len(), 3);
3212                assert_eq!(a[0][0], LiteralValue::Number(1.0));
3213            }
3214            _ => panic!("expected array"),
3215        }
3216    }
3217
3218    #[test]
3219    fn sequence_basic_rows_cols_step() {
3220        let wb = TestWorkbook::new().with_function(Arc::new(SequenceFn));
3221        let ctx = wb.interpreter();
3222        let f = ctx.context.get_function("", "SEQUENCE").unwrap();
3223        let rows = lit(LiteralValue::Int(2));
3224        let cols = lit(LiteralValue::Int(3));
3225        let start = lit(LiteralValue::Int(5));
3226        let step = lit(LiteralValue::Int(2));
3227        let args = vec![
3228            ArgumentHandle::new(&rows, &ctx),
3229            ArgumentHandle::new(&cols, &ctx),
3230            ArgumentHandle::new(&start, &ctx),
3231            ArgumentHandle::new(&step, &ctx),
3232        ];
3233        let v = f
3234            .dispatch(&args, &ctx.function_context(None))
3235            .unwrap()
3236            .into_literal();
3237        match v {
3238            LiteralValue::Array(a) => {
3239                assert_eq!(a.len(), 2);
3240                assert_eq!(a[0][0], LiteralValue::Number(5.0));
3241            }
3242            other => panic!("expected array got {other:?}"),
3243        }
3244    }
3245
3246    #[test]
3247    fn transpose_basic() {
3248        let wb = TestWorkbook::new().with_function(Arc::new(TransposeFn));
3249        let wb = wb
3250            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(1))
3251            .with_cell_a1("Sheet1", "A2", LiteralValue::Int(2))
3252            .with_cell_a1("Sheet1", "B1", LiteralValue::Int(10))
3253            .with_cell_a1("Sheet1", "B2", LiteralValue::Int(20));
3254        let ctx = wb.interpreter();
3255        let arr = range("A1:B2", 1, 1, 2, 2);
3256        let f = ctx.context.get_function("", "TRANSPOSE").unwrap();
3257        let args = vec![ArgumentHandle::new(&arr, &ctx)];
3258        let v = f
3259            .dispatch(&args, &ctx.function_context(None))
3260            .unwrap()
3261            .into_literal();
3262        match v {
3263            LiteralValue::Array(a) => {
3264                assert_eq!(a.len(), 2);
3265                assert_eq!(
3266                    a[0],
3267                    vec![LiteralValue::Number(1.0), LiteralValue::Number(2.0)]
3268                );
3269            }
3270            other => panic!("expected array got {other:?}"),
3271        }
3272    }
3273
3274    #[test]
3275    fn take_basic() {
3276        let wb = TestWorkbook::new().with_function(Arc::new(TakeFn));
3277        let wb = wb
3278            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(1))
3279            .with_cell_a1("Sheet1", "A2", LiteralValue::Int(2));
3280        let ctx = wb.interpreter();
3281        let arr = range("A1:A2", 1, 1, 2, 1);
3282        let f = ctx.context.get_function("", "TAKE").unwrap();
3283        let one = lit(LiteralValue::Int(1));
3284        let args = vec![
3285            ArgumentHandle::new(&arr, &ctx),
3286            ArgumentHandle::new(&one, &ctx),
3287        ];
3288        let v = f
3289            .dispatch(&args, &ctx.function_context(None))
3290            .unwrap()
3291            .into_literal();
3292        assert_eq!(v, LiteralValue::Number(1.0));
3293    }
3294
3295    #[test]
3296    fn drop_basic() {
3297        let wb = TestWorkbook::new().with_function(Arc::new(DropFn));
3298        let wb = wb
3299            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(1))
3300            .with_cell_a1("Sheet1", "A2", LiteralValue::Int(2));
3301        let ctx = wb.interpreter();
3302        let arr = range("A1:A2", 1, 1, 2, 1);
3303        let f = ctx.context.get_function("", "DROP").unwrap();
3304        let one = lit(LiteralValue::Int(1));
3305        let args = vec![
3306            ArgumentHandle::new(&arr, &ctx),
3307            ArgumentHandle::new(&one, &ctx),
3308        ];
3309        let v = f
3310            .dispatch(&args, &ctx.function_context(None))
3311            .unwrap()
3312            .into_literal();
3313        assert_eq!(v, LiteralValue::Number(2.0));
3314    }
3315
3316    #[test]
3317    fn xmatch_exact_match_default() {
3318        let wb = TestWorkbook::new().with_function(Arc::new(XMatchFn));
3319        let wb = wb
3320            .with_cell_a1("Sheet1", "A1", LiteralValue::Text("apple".into()))
3321            .with_cell_a1("Sheet1", "A2", LiteralValue::Text("banana".into()))
3322            .with_cell_a1("Sheet1", "A3", LiteralValue::Text("cherry".into()));
3323        let ctx = wb.interpreter();
3324        let lookup_range = range("A1:A3", 1, 1, 3, 1);
3325        let f = ctx.context.get_function("", "XMATCH").unwrap();
3326        let key = lit(LiteralValue::Text("banana".into()));
3327        let args = vec![
3328            ArgumentHandle::new(&key, &ctx),
3329            ArgumentHandle::new(&lookup_range, &ctx),
3330        ];
3331        let v = f
3332            .dispatch(&args, &ctx.function_context(None))
3333            .unwrap()
3334            .into_literal();
3335        assert_eq!(v, LiteralValue::Int(2));
3336    }
3337
3338    #[test]
3339    fn xmatch_exact_or_next_smaller() {
3340        let wb = TestWorkbook::new().with_function(Arc::new(XMatchFn));
3341        let wb = wb
3342            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(10))
3343            .with_cell_a1("Sheet1", "A2", LiteralValue::Int(20))
3344            .with_cell_a1("Sheet1", "A3", LiteralValue::Int(30));
3345        let ctx = wb.interpreter();
3346        let lookup_range = range("A1:A3", 1, 1, 3, 1);
3347        let f = ctx.context.get_function("", "XMATCH").unwrap();
3348        let needle = lit(LiteralValue::Int(25));
3349        let match_mode = lit(LiteralValue::Int(-1)); // exact or next smaller
3350        let args = vec![
3351            ArgumentHandle::new(&needle, &ctx),
3352            ArgumentHandle::new(&lookup_range, &ctx),
3353            ArgumentHandle::new(&match_mode, &ctx),
3354        ];
3355        let v = f
3356            .dispatch(&args, &ctx.function_context(None))
3357            .unwrap()
3358            .into_literal();
3359        assert_eq!(v, LiteralValue::Int(2)); // 20 is the largest <= 25
3360    }
3361
3362    #[test]
3363    fn xmatch_exact_or_next_larger() {
3364        let wb = TestWorkbook::new().with_function(Arc::new(XMatchFn));
3365        let wb = wb
3366            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(10))
3367            .with_cell_a1("Sheet1", "A2", LiteralValue::Int(20))
3368            .with_cell_a1("Sheet1", "A3", LiteralValue::Int(30));
3369        let ctx = wb.interpreter();
3370        let lookup_range = range("A1:A3", 1, 1, 3, 1);
3371        let f = ctx.context.get_function("", "XMATCH").unwrap();
3372        let needle = lit(LiteralValue::Int(25));
3373        let match_mode = lit(LiteralValue::Int(1)); // exact or next larger
3374        let args = vec![
3375            ArgumentHandle::new(&needle, &ctx),
3376            ArgumentHandle::new(&lookup_range, &ctx),
3377            ArgumentHandle::new(&match_mode, &ctx),
3378        ];
3379        let v = f
3380            .dispatch(&args, &ctx.function_context(None))
3381            .unwrap()
3382            .into_literal();
3383        assert_eq!(v, LiteralValue::Int(3)); // 30 is the smallest >= 25
3384    }
3385
3386    #[test]
3387    fn xmatch_wildcard() {
3388        let wb = TestWorkbook::new().with_function(Arc::new(XMatchFn));
3389        let wb = wb
3390            .with_cell_a1("Sheet1", "A1", LiteralValue::Text("alpha".into()))
3391            .with_cell_a1("Sheet1", "A2", LiteralValue::Text("beta".into()))
3392            .with_cell_a1("Sheet1", "A3", LiteralValue::Text("gamma".into()));
3393        let ctx = wb.interpreter();
3394        let lookup_range = range("A1:A3", 1, 1, 3, 1);
3395        let f = ctx.context.get_function("", "XMATCH").unwrap();
3396        let pattern = lit(LiteralValue::Text("*eta".into()));
3397        let match_mode = lit(LiteralValue::Int(2)); // wildcard
3398        let args = vec![
3399            ArgumentHandle::new(&pattern, &ctx),
3400            ArgumentHandle::new(&lookup_range, &ctx),
3401            ArgumentHandle::new(&match_mode, &ctx),
3402        ];
3403        let v = f
3404            .dispatch(&args, &ctx.function_context(None))
3405            .unwrap()
3406            .into_literal();
3407        assert_eq!(v, LiteralValue::Int(2)); // "beta" matches "*eta"
3408    }
3409
3410    #[test]
3411    fn xmatch_reverse_search() {
3412        let wb = TestWorkbook::new().with_function(Arc::new(XMatchFn));
3413        let wb = wb
3414            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(1))
3415            .with_cell_a1("Sheet1", "A2", LiteralValue::Int(2))
3416            .with_cell_a1("Sheet1", "A3", LiteralValue::Int(1)); // duplicate
3417        let ctx = wb.interpreter();
3418        let lookup_range = range("A1:A3", 1, 1, 3, 1);
3419        let f = ctx.context.get_function("", "XMATCH").unwrap();
3420        let needle = lit(LiteralValue::Int(1));
3421        let match_mode = lit(LiteralValue::Int(0));
3422        let search_mode = lit(LiteralValue::Int(-1)); // last to first
3423        let args = vec![
3424            ArgumentHandle::new(&needle, &ctx),
3425            ArgumentHandle::new(&lookup_range, &ctx),
3426            ArgumentHandle::new(&match_mode, &ctx),
3427            ArgumentHandle::new(&search_mode, &ctx),
3428        ];
3429        let v = f
3430            .dispatch(&args, &ctx.function_context(None))
3431            .unwrap()
3432            .into_literal();
3433        assert_eq!(v, LiteralValue::Int(3)); // last occurrence of 1
3434    }
3435
3436    #[test]
3437    fn xmatch_not_found() {
3438        let wb = TestWorkbook::new().with_function(Arc::new(XMatchFn));
3439        let wb = wb
3440            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(1))
3441            .with_cell_a1("Sheet1", "A2", LiteralValue::Int(2))
3442            .with_cell_a1("Sheet1", "A3", LiteralValue::Int(3));
3443        let ctx = wb.interpreter();
3444        let lookup_range = range("A1:A3", 1, 1, 3, 1);
3445        let f = ctx.context.get_function("", "XMATCH").unwrap();
3446        let needle = lit(LiteralValue::Int(5));
3447        let args = vec![
3448            ArgumentHandle::new(&needle, &ctx),
3449            ArgumentHandle::new(&lookup_range, &ctx),
3450        ];
3451        let v = f
3452            .dispatch(&args, &ctx.function_context(None))
3453            .unwrap()
3454            .into_literal();
3455        match v {
3456            LiteralValue::Error(e) => assert_eq!(e.kind, ExcelErrorKind::Na),
3457            other => panic!("expected #N/A got {other:?}"),
3458        }
3459    }
3460
3461    #[test]
3462    fn sort_basic_ascending() {
3463        let wb = TestWorkbook::new().with_function(Arc::new(SortFn));
3464        let wb = wb
3465            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(30))
3466            .with_cell_a1("Sheet1", "A2", LiteralValue::Int(10))
3467            .with_cell_a1("Sheet1", "A3", LiteralValue::Int(20));
3468        let ctx = wb.interpreter();
3469        let arr = range("A1:A3", 1, 1, 3, 1);
3470        let f = ctx.context.get_function("", "SORT").unwrap();
3471        let args = vec![ArgumentHandle::new(&arr, &ctx)];
3472        let v = f
3473            .dispatch(&args, &ctx.function_context(None))
3474            .unwrap()
3475            .into_literal();
3476        match v {
3477            LiteralValue::Array(a) => {
3478                assert_eq!(a.len(), 3);
3479                assert_eq!(a[0][0], LiteralValue::Number(10.0));
3480                assert_eq!(a[1][0], LiteralValue::Number(20.0));
3481                assert_eq!(a[2][0], LiteralValue::Number(30.0));
3482            }
3483            other => panic!("expected array got {other:?}"),
3484        }
3485    }
3486
3487    #[test]
3488    fn sort_descending() {
3489        let wb = TestWorkbook::new().with_function(Arc::new(SortFn));
3490        let wb = wb
3491            .with_cell_a1("Sheet1", "A1", LiteralValue::Int(30))
3492            .with_cell_a1("Sheet1", "A2", LiteralValue::Int(10))
3493            .with_cell_a1("Sheet1", "A3", LiteralValue::Int(20));
3494        let ctx = wb.interpreter();
3495        let arr = range("A1:A3", 1, 1, 3, 1);
3496        let f = ctx.context.get_function("", "SORT").unwrap();
3497        let sort_index = lit(LiteralValue::Int(1));
3498        let sort_order = lit(LiteralValue::Int(-1)); // descending
3499        let args = vec![
3500            ArgumentHandle::new(&arr, &ctx),
3501            ArgumentHandle::new(&sort_index, &ctx),
3502            ArgumentHandle::new(&sort_order, &ctx),
3503        ];
3504        let v = f
3505            .dispatch(&args, &ctx.function_context(None))
3506            .unwrap()
3507            .into_literal();
3508        match v {
3509            LiteralValue::Array(a) => {
3510                assert_eq!(a.len(), 3);
3511                assert_eq!(a[0][0], LiteralValue::Number(30.0));
3512                assert_eq!(a[1][0], LiteralValue::Number(20.0));
3513                assert_eq!(a[2][0], LiteralValue::Number(10.0));
3514            }
3515            other => panic!("expected array got {other:?}"),
3516        }
3517    }
3518
3519    #[test]
3520    fn sort_by_column() {
3521        let wb = TestWorkbook::new().with_function(Arc::new(SortFn));
3522        let wb = wb
3523            .with_cell_a1("Sheet1", "A1", LiteralValue::Text("Charlie".into()))
3524            .with_cell_a1("Sheet1", "B1", LiteralValue::Int(30))
3525            .with_cell_a1("Sheet1", "A2", LiteralValue::Text("Alice".into()))
3526            .with_cell_a1("Sheet1", "B2", LiteralValue::Int(10))
3527            .with_cell_a1("Sheet1", "A3", LiteralValue::Text("Bob".into()))
3528            .with_cell_a1("Sheet1", "B3", LiteralValue::Int(20));
3529        let ctx = wb.interpreter();
3530        let arr = range("A1:B3", 1, 1, 3, 2);
3531        let f = ctx.context.get_function("", "SORT").unwrap();
3532        let sort_index = lit(LiteralValue::Int(2)); // sort by column B
3533        let args = vec![
3534            ArgumentHandle::new(&arr, &ctx),
3535            ArgumentHandle::new(&sort_index, &ctx),
3536        ];
3537        let v = f
3538            .dispatch(&args, &ctx.function_context(None))
3539            .unwrap()
3540            .into_literal();
3541        match v {
3542            LiteralValue::Array(a) => {
3543                assert_eq!(a.len(), 3);
3544                // Should be sorted by column B: Alice(10), Bob(20), Charlie(30)
3545                assert_eq!(a[0][0], LiteralValue::Text("Alice".into()));
3546                assert_eq!(a[1][0], LiteralValue::Text("Bob".into()));
3547                assert_eq!(a[2][0], LiteralValue::Text("Charlie".into()));
3548            }
3549            other => panic!("expected array got {other:?}"),
3550        }
3551    }
3552
3553    #[test]
3554    fn sortby_basic() {
3555        let wb = TestWorkbook::new().with_function(Arc::new(SortByFn));
3556        let wb = wb
3557            .with_cell_a1("Sheet1", "A1", LiteralValue::Text("Charlie".into()))
3558            .with_cell_a1("Sheet1", "A2", LiteralValue::Text("Alice".into()))
3559            .with_cell_a1("Sheet1", "A3", LiteralValue::Text("Bob".into()))
3560            .with_cell_a1("Sheet1", "B1", LiteralValue::Int(3))
3561            .with_cell_a1("Sheet1", "B2", LiteralValue::Int(1))
3562            .with_cell_a1("Sheet1", "B3", LiteralValue::Int(2));
3563        let ctx = wb.interpreter();
3564        let arr = range("A1:A3", 1, 1, 3, 1);
3565        let by_arr = range("B1:B3", 1, 2, 3, 2);
3566        let f = ctx.context.get_function("", "SORTBY").unwrap();
3567        let args = vec![
3568            ArgumentHandle::new(&arr, &ctx),
3569            ArgumentHandle::new(&by_arr, &ctx),
3570        ];
3571        let v = f
3572            .dispatch(&args, &ctx.function_context(None))
3573            .unwrap()
3574            .into_literal();
3575        match v {
3576            LiteralValue::Array(a) => {
3577                assert_eq!(a.len(), 3);
3578                // Should be sorted by B values: Alice(1), Bob(2), Charlie(3)
3579                assert_eq!(a[0][0], LiteralValue::Text("Alice".into()));
3580                assert_eq!(a[1][0], LiteralValue::Text("Bob".into()));
3581                assert_eq!(a[2][0], LiteralValue::Text("Charlie".into()));
3582            }
3583            other => panic!("expected array got {other:?}"),
3584        }
3585    }
3586
3587    #[test]
3588    fn sortby_descending() {
3589        let wb = TestWorkbook::new().with_function(Arc::new(SortByFn));
3590        let wb = wb
3591            .with_cell_a1("Sheet1", "A1", LiteralValue::Text("Charlie".into()))
3592            .with_cell_a1("Sheet1", "A2", LiteralValue::Text("Alice".into()))
3593            .with_cell_a1("Sheet1", "A3", LiteralValue::Text("Bob".into()))
3594            .with_cell_a1("Sheet1", "B1", LiteralValue::Int(3))
3595            .with_cell_a1("Sheet1", "B2", LiteralValue::Int(1))
3596            .with_cell_a1("Sheet1", "B3", LiteralValue::Int(2));
3597        let ctx = wb.interpreter();
3598        let arr = range("A1:A3", 1, 1, 3, 1);
3599        let by_arr = range("B1:B3", 1, 2, 3, 2);
3600        let sort_order = lit(LiteralValue::Int(-1)); // descending
3601        let f = ctx.context.get_function("", "SORTBY").unwrap();
3602        let args = vec![
3603            ArgumentHandle::new(&arr, &ctx),
3604            ArgumentHandle::new(&by_arr, &ctx),
3605            ArgumentHandle::new(&sort_order, &ctx),
3606        ];
3607        let v = f
3608            .dispatch(&args, &ctx.function_context(None))
3609            .unwrap()
3610            .into_literal();
3611        match v {
3612            LiteralValue::Array(a) => {
3613                assert_eq!(a.len(), 3);
3614                // Should be sorted by B values descending: Charlie(3), Bob(2), Alice(1)
3615                assert_eq!(a[0][0], LiteralValue::Text("Charlie".into()));
3616                assert_eq!(a[1][0], LiteralValue::Text("Bob".into()));
3617                assert_eq!(a[2][0], LiteralValue::Text("Alice".into()));
3618            }
3619            other => panic!("expected array got {other:?}"),
3620        }
3621    }
3622
3623    #[test]
3624    fn randarray_basic() {
3625        let wb = TestWorkbook::new().with_function(Arc::new(RandArrayFn));
3626        let ctx = wb.interpreter();
3627        let f = ctx.context.get_function("", "RANDARRAY").unwrap();
3628
3629        // Test basic 2x3 array with defaults
3630        let rows = lit(LiteralValue::Int(2));
3631        let cols = lit(LiteralValue::Int(3));
3632        let args = vec![
3633            ArgumentHandle::new(&rows, &ctx),
3634            ArgumentHandle::new(&cols, &ctx),
3635        ];
3636        let v = f
3637            .dispatch(&args, &ctx.function_context(None))
3638            .unwrap()
3639            .into_literal();
3640        match v {
3641            LiteralValue::Array(a) => {
3642                assert_eq!(a.len(), 2);
3643                assert_eq!(a[0].len(), 3);
3644                // Check all values are between 0 and 1
3645                for row in &a {
3646                    for cell in row {
3647                        match cell {
3648                            LiteralValue::Number(n) => {
3649                                assert!(*n >= 0.0 && *n < 1.0, "Value {n} not in [0, 1)");
3650                            }
3651                            other => panic!("expected Number got {other:?}"),
3652                        }
3653                    }
3654                }
3655            }
3656            other => panic!("expected array got {other:?}"),
3657        }
3658    }
3659
3660    #[test]
3661    fn randarray_whole_numbers() {
3662        let wb = TestWorkbook::new().with_function(Arc::new(RandArrayFn));
3663        let ctx = wb.interpreter();
3664        let f = ctx.context.get_function("", "RANDARRAY").unwrap();
3665
3666        // Test 3x2 array with whole numbers between 1 and 10
3667        let rows = lit(LiteralValue::Int(3));
3668        let cols = lit(LiteralValue::Int(2));
3669        let min = lit(LiteralValue::Int(1));
3670        let max = lit(LiteralValue::Int(10));
3671        let whole = lit(LiteralValue::Boolean(true));
3672        let args = vec![
3673            ArgumentHandle::new(&rows, &ctx),
3674            ArgumentHandle::new(&cols, &ctx),
3675            ArgumentHandle::new(&min, &ctx),
3676            ArgumentHandle::new(&max, &ctx),
3677            ArgumentHandle::new(&whole, &ctx),
3678        ];
3679        let v = f
3680            .dispatch(&args, &ctx.function_context(None))
3681            .unwrap()
3682            .into_literal();
3683        match v {
3684            LiteralValue::Array(a) => {
3685                assert_eq!(a.len(), 3);
3686                assert_eq!(a[0].len(), 2);
3687                // Check all values are integers between 1 and 10
3688                for row in &a {
3689                    for cell in row {
3690                        let n = match cell {
3691                            LiteralValue::Int(n) => *n as f64,
3692                            LiteralValue::Number(n) => *n,
3693                            other => panic!("expected Int or Number got {other:?}"),
3694                        };
3695                        assert!((1.0..=10.0).contains(&n), "Value {n} not in [1, 10]");
3696                        // Verify it's actually a whole number
3697                        assert!(n.fract() == 0.0, "Value {n} is not a whole number");
3698                    }
3699                }
3700            }
3701            other => panic!("expected array got {other:?}"),
3702        }
3703    }
3704}
formualizer_eval/builtins/lookup/dynamic.rs

formualizer_eval/builtins/lookup/
dynamic.rs
