ravenrs_sheet/engine/macros/

resolve.rs

//! Macro tokens: references in step parameters that are worked out when the step runs, so a
//! macro made on one block of data works on another.
//!
//! A token is a base followed by methods:
//!
//! - bases: a declared input (`Input`, `Rates`), `Output` (the output rule's anchor), `Row` or
//!   `Column` (the current item of the Repeat step around it), and `Name:Total` (a defined name or
//!   a table's data; a name can hold periods, and the longest one that exists is used, so
//!   `Name:Sales.Q1.cell(1,1)` is a cell of `Sales.Q1`);
//! - methods, 1-based and chainable: `.col(n)` or `.col("Amount")` (by an input's header row,
//!   also after `.data`), `.row(n)`, `.cols(a..b)`, `.rows(a..)`, `.cell(r,c)` (relative to the
//!   top-left cell; any whole numbers, so `cell(0,1)` is the cell above), `.offset(r,c)`,
//!   `.resize(h,w)`, `.data` (without the header row), `.below(n)` and `.right(n)` (the n rows
//!   below or columns right of the range).
//!
//! In parameters a token is written three ways: `{"$ref": "Input.col(2)"}` anywhere; the whole
//! string `"Input.col(2)"` in a reference parameter (`cell`, `range`, `target`, `to`, keys ending
//! in `Cell` or `Range`…); or `{{Input.col(2)}}` inside any string (`"=SUM({{Input.col(2)}})"`).
//! Each becomes A1 text before the command runs, so commands never see a token. Text that would
//! read as a token is written `{"$text": "Output"}`: it is passed as it is, never resolved or
//! interpolated (a defined name called `Output`, or `Dear {{name}}`). Plain A1
//! references in steps are absolute: they don't follow row inserts (as in VBA); a `Name:` anchor
//! does.

use crate::base::{CellRef, MAX_COLS, MAX_ROWS, RangeRef};
use crate::model::{RepeatOf, Workbook};
use serde_json::{Map, Value as Json};

/// A resolved reference: a block of cells on a sheet.
#[derive(Clone, Copy, Debug, PartialEq, Eq)]
pub struct Place {
    pub sheet: usize,
    pub range: RangeRef,
}

/// Why a reference couldn't be worked out.
#[derive(Clone, Debug, PartialEq, Eq)]
pub enum ResolveError {
    /// It runs past the edge of the sheet.
    Edge(String),
    /// Anything else (an unknown input, a malformed token).
    Bad(String),
}

impl ResolveError {
    pub fn message(&self) -> &str {
        match self {
            ResolveError::Edge(m) | ResolveError::Bad(m) => m,
        }
    }
}

impl std::fmt::Display for ResolveError {
    fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
        f.write_str(self.message())
    }
}

type R<T> = Result<T, ResolveError>;

fn bad<T>(msg: impl Into<String>) -> R<T> {
    Err(ResolveError::Bad(msg.into()))
}

/// What tokens refer to while a step runs.
#[derive(Clone, Copy, Debug)]
pub struct Env<'a> {
    pub wb: &'a Workbook,
    /// The sheet a reference without a sheet name is on.
    pub active_sheet: usize,
    /// Declared inputs, with whether each has a header row.
    pub inputs: &'a [(String, Place, bool)],
    pub output: Option<Place>,
    /// The innermost Repeat step's current row or column.
    pub item: Option<(RepeatOf, Place)>,
}

/// Parameter keys that hold references, where a whole-string token is resolved. Elsewhere a
/// string is text (a cell may hold "Row", a chart's type is "column"), and a token needs
/// `{"$ref": …}` or `{{…}}`.
const REF_KEYS: &[&str] = &[
    "active",
    "anchor",
    "area",
    "areas",
    "at",
    "cell",
    "cells",
    "changing",
    "copyTo",
    "destination",
    "from",
    "fromCell",
    "location",
    "objective",
    "over",
    "range",
    "ranges",
    "reference",
    "resultCells",
    "source",
    "sources",
    "target",
    "to",
    "variables",
];

/// Whether a parameter called `key` holds a reference.
pub fn is_ref_key(key: &str) -> bool {
    let k = key.to_ascii_lowercase();
    REF_KEYS.iter().any(|r| r.eq_ignore_ascii_case(key)) || ["cell", "cells", "range", "ranges", "ref"].iter().any(|end| k.ends_with(end))
}

/// Reference keys that other commands use for plain words: `to` is a replacement word in
/// Review › Change Spelling, `area` is `row` or `column` in a pivot field move. Under these a
/// bare `Row` or `Column` stays text; `Row.col(2)`, `{{Row}}` or `{"$ref": "Row"}` is the token.
fn is_general_key(key: &str) -> bool {
    ["area", "areas", "at", "destination", "from", "location", "source", "sources", "target", "to"].iter().any(|k| k.eq_ignore_ascii_case(key))
}

/// `Row` or `Column` on its own, with no method or sheet after it.
fn is_bare_item_word(text: &str) -> bool {
    let t = text.trim();
    t.eq_ignore_ascii_case("row") || t.eq_ignore_ascii_case("column")
}

/// The most nesting [`resolve_params`] walks into.
const MAX_JSON_DEPTH: usize = 64;

/// Whether `text` starts like a token (its base is one this environment knows). A reference on a
/// sheet with such a name (`Output!A1`) is not a token.
pub fn is_token(text: &str, env: &Env) -> bool {
    starts_like_token(text, |base| env.inputs.iter().any(|(n, ..)| n.eq_ignore_ascii_case(base)))
}

/// Whether `text` would read as a token in a macro whose inputs are `inputs` (see [`is_token`]).
pub fn reads_as_token(text: &str, inputs: &[&str]) -> bool {
    starts_like_token(text, |base| inputs.iter().any(|n| n.eq_ignore_ascii_case(base)))
}

fn starts_like_token(text: &str, is_input: impl Fn(&str) -> bool) -> bool {
    let t = text.trim();
    if t.get(..5).is_some_and(|h| h.eq_ignore_ascii_case("name:")) {
        return true;
    }
    let n: usize = t.chars().take_while(|c| c.is_alphanumeric() || *c == '_').map(char::len_utf8).sum();
    let base = t.get(..n).unwrap_or("");
    if t.get(n..).unwrap_or("").trim_start().starts_with('!') {
        return false;
    }
    is_builtin(base) || is_input(base)
}

fn is_builtin(base: &str) -> bool {
    ["Output", "Row", "Column"].iter().any(|b| b.eq_ignore_ascii_case(base))
}

/// Resolves a token, or a plain reference (`B2`, `Sheet2!A1:C3`, a defined name).
pub fn resolve_ref(text: &str, env: &Env) -> R<Place> {
    let t = text.trim();
    if is_token(t, env) {
        return resolve_token(t, env);
    }
    plain_ref(t, env)
}

/// `A1`, `'My Sheet'!A1:B2` or a defined name or table.
fn plain_ref(t: &str, env: &Env) -> R<Place> {
    let (sheet, body) = crate::engine::cmd::split_sheet(t);
    let si = match &sheet {
        Some(name) => env.wb.sheet_index(name).ok_or_else(|| ResolveError::Bad(format!("There is no sheet named '{name}'.")))?,
        None => env.active_sheet,
    };
    if let Some(range) = RangeRef::parse(body) {
        return Ok(Place { sheet: si, range });
    }
    if sheet.is_none() {
        return named(t, env);
    }
    bad(format!("'{t}' isn't a reference."))
}

/// A defined name (by its formula) or a table (its data rows).
fn named(name: &str, env: &Env) -> R<Place> {
    if let Some(n) = env.wb.name(name, env.active_sheet) {
        // The sheet part keeps its characters (a sheet may be called `US$`); only the reference
        // loses its `$` anchors.
        let f = n.formula.trim().trim_start_matches('=');
        let (sheet, body) = crate::engine::cmd::split_sheet(f);
        let si = match sheet {
            Some(s) => env.wb.sheet_index(&s),
            None => n.scope.or(Some(env.active_sheet)),
        };
        if let (Some(sheet), Some(range)) = (si, RangeRef::parse(&body.replace('$', ""))) {
            return Ok(Place { sheet, range });
        }
        return bad(format!("The name '{name}' doesn't refer to a range."));
    }
    if let Some((si, ti)) = env.wb.table(name)
        && let Some(t) = env.wb.sheet(si).and_then(|sh| sh.tables.get(ti))
    {
        // A table stands for its data rows; one with none (a header, perhaps a totals row) has
        // nothing to give.
        let Some(range) = t.data_range() else {
            return bad(format!("The table '{name}' has no data rows."));
        };
        return Ok(Place { sheet: si, range });
    }
    bad(format!("There is no name or table called '{name}'."))
}

/// Token method names.
const METHODS: &[&str] = &["col", "row", "cols", "rows", "cell", "offset", "resize", "data", "below", "right"];

/// Where the name ends in `run`, the text after `Name:` up to the first character a name can't
/// hold: at the end or at a period, the longest choice that is a defined name or table. When
/// none is, the name ends before the first method (`Nope.cell`), so the error names it.
fn name_end(run: &str, env: &Env) -> usize {
    // One pass over the names and tables, as a single lookup makes.
    let fits = |name: &str| {
        !name.is_empty()
            && run.get(..name.len()).is_some_and(|head| head.eq_ignore_ascii_case(name))
            && run.get(name.len()..).is_some_and(|rest| rest.is_empty() || rest.starts_with('.'))
    };
    let names = env.wb.names.iter().filter(|n| n.scope.is_none() || n.scope == Some(env.active_sheet)).map(|n| n.name.as_str());
    let tables = env.wb.sheets.iter().flat_map(|sh| sh.tables.iter().map(|t| t.name.as_str()));
    if let Some(end) = names.chain(tables).filter(|n| fits(n)).map(str::len).max() {
        return end;
    }
    run.match_indices('.')
        .map(|(i, _)| i)
        .find(|&i| {
            let next = run.get(i + 1..).unwrap_or("").split('.').next().unwrap_or("");
            METHODS.iter().any(|m| m.eq_ignore_ascii_case(next))
        })
        .unwrap_or(run.len())
}

/// Resolves a token (see the module docs).
pub fn resolve_token(text: &str, env: &Env) -> R<Place> {
    let t = text.trim();
    let mut p = Parser { s: t, at: 0 };
    let (mut place, mut header) = p.base(env)?;
    // The row of column names `col("…")` reads: the input's header row, kept through `data`.
    let mut names_row = header.then_some(place.range.start.row);
    while p.eat('.') {
        let name = p.ident();
        if name.is_empty() {
            return bad(format!("'{t}': expected a method name after '.'."));
        }
        let args = if p.eat('(') { p.args(t)? } else { vec![] };
        let r = place.range;
        let method = name.to_ascii_lowercase();
        let range = match (method.as_str(), args.as_slice()) {
            ("col", [Arg::Int(n)]) => sub(r, None, Some((*n, *n)), t)?,
            ("col", [Arg::Text(h)]) => {
                let Some(row) = names_row else {
                    return bad(format!("'{t}': col(\"{h}\") needs an input with a header row; use col(n)."));
                };
                let c = header_col(env, place, row, h).ok_or_else(|| ResolveError::Bad(format!("'{t}': no column is headed '{h}'.")))?;
                sub(r, None, Some((c, c)), t)?
            }
            ("row", [Arg::Int(n)]) => sub(r, Some((*n, *n)), None, t)?,
            ("cols", [Arg::Span(a, b)]) => sub(r, None, Some(span(*a, *b, i64::from(r.width()))), t)?,
            ("rows", [Arg::Span(a, b)]) => sub(r, Some(span(*a, *b, i64::from(r.height()))), None, t)?,
            ("cell", [Arg::Int(row), Arg::Int(col)]) => {
                let c = shift(r.start, row - 1, col - 1, t)?;
                RangeRef::cell(c)
            }
            ("offset", [Arg::Int(dr), Arg::Int(dc)]) => RangeRef::new(shift(r.start, *dr, *dc, t)?, shift(r.end, *dr, *dc, t)?),
            ("resize", [Arg::Int(h), Arg::Int(w)]) => {
                if *h < 1 || *w < 1 {
                    return bad(format!("'{t}': resize needs at least 1 row and 1 column."));
                }
                RangeRef::new(r.start, shift(r.start, h - 1, w - 1, t)?)
            }
            ("data", []) => {
                if header {
                    // A header row with nothing under it has no data rows to give.
                    if r.height() < 2 {
                        return bad(format!("'{t}': the range is only a header row, with no data under it."));
                    }
                    RangeRef::new(CellRef::new(r.start.row + 1, r.start.col), r.end)
                } else {
                    r
                }
            }
            ("below", []) | ("below", [Arg::Int(_)]) => {
                let n = count_arg(&args, t)?;
                let top = shift(CellRef::new(r.end.row, r.start.col), 1, 0, t)?;
                RangeRef::new(top, shift(CellRef::new(r.end.row, r.end.col), n, 0, t)?)
            }
            ("right", []) | ("right", [Arg::Int(_)]) => {
                let n = count_arg(&args, t)?;
                let left = shift(CellRef::new(r.start.row, r.end.col), 0, 1, t)?;
                RangeRef::new(left, shift(r.end, 0, n, t)?)
            }
            _ => {
                return bad(format!("'{t}': '{name}' with these arguments isn't a token method ({}).", METHODS.join(", ")));
            }
        };
        // After a method the result is plain cells, except that `col` and `cols` keep the input's top
        // row, so a later `data` still takes the header off; only `data` keeps the header row for
        // `col("…")`.
        if !matches!(method.as_str(), "col" | "cols") {
            header = false;
        }
        if method != "data" {
            names_row = None;
        }
        place = Place { sheet: place.sheet, range };
    }
    p.skip_ws();
    if p.at < p.s.len() {
        return bad(format!("'{t}' isn't a token: unexpected '{}'.", p.s.get(p.at..).unwrap_or("")));
    }
    Ok(place)
}

/// The count `below(n)` and `right(n)` take: 1 when left out, and never less than 1.
fn count_arg(args: &[Arg], t: &str) -> R<i64> {
    match args.first() {
        Some(Arg::Int(n)) if *n < 1 => bad(format!("'{t}': the count must be at least 1.")),
        Some(Arg::Int(n)) => Ok(*n),
        _ => Ok(1),
    }
}

/// `a..b` with open ends filled from 1 and `len`.
fn span(a: Option<i64>, b: Option<i64>, len: i64) -> (i64, i64) {
    (a.unwrap_or(1), b.unwrap_or(len))
}

/// Rows `rows` and columns `cols` (1-based, inclusive) of `r`; `None` keeps them all.
fn sub(r: RangeRef, rows: Option<(i64, i64)>, cols: Option<(i64, i64)>, t: &str) -> R<RangeRef> {
    let (h, w) = (i64::from(r.height()), i64::from(r.width()));
    let (r0, r1) = rows.unwrap_or((1, h));
    let (c0, c1) = cols.unwrap_or((1, w));
    if r0 < 1 || c0 < 1 || r1 < r0 || c1 < c0 || r1 > h || c1 > w {
        return bad(format!("'{t}' is outside the range it starts from ({} rows by {} columns).", h, w));
    }
    Ok(RangeRef::new(shift(r.start, r0 - 1, c0 - 1, t)?, shift(r.start, r1 - 1, c1 - 1, t)?))
}

fn shift(c: CellRef, dr: i64, dc: i64, t: &str) -> R<CellRef> {
    let row = i64::from(c.row).checked_add(dr);
    let col = i64::from(c.col).checked_add(dc);
    match (row, col) {
        (Some(row), Some(col)) if (0..i64::from(MAX_ROWS)).contains(&row) && (0..i64::from(MAX_COLS)).contains(&col) => {
            Ok(CellRef::new(row as u32, col as u32))
        }
        (Some(row), Some(_)) if row < 0 => Err(ResolveError::Edge(format!("'{t}' runs past the top of the sheet."))),
        (Some(row), Some(_)) if row >= i64::from(MAX_ROWS) => Err(ResolveError::Edge(format!("'{t}' runs past the last row of the sheet."))),
        _ => Err(ResolveError::Edge(format!("'{t}' runs past the edge of the sheet."))),
    }
}

/// The 1-based column of `place` whose header (in sheet row `row`) reads `h`.
fn header_col(env: &Env, place: Place, row: u32, h: &str) -> Option<i64> {
    let sh = env.wb.sheet(place.sheet)?;
    let r = place.range;
    (r.start.col..=r.end.col)
        // The header's value, however the sheet shows it: with Show Formulas on, the cell's
        // display text is its formula.
        .position(|c| crate::engine::display::value_text(env.wb, sh, CellRef::new(row, c)).trim().eq_ignore_ascii_case(h.trim()))
        .map(|i| i as i64 + 1)
}

#[derive(Debug)]
enum Arg {
    Int(i64),
    Span(Option<i64>, Option<i64>),
    Text(String),
}

struct Parser<'a> {
    s: &'a str,
    at: usize,
}

impl Parser<'_> {
    fn rest(&self) -> &str {
        self.s.get(self.at..).unwrap_or("")
    }
    fn skip_ws(&mut self) {
        let n = self.rest().len() - self.rest().trim_start().len();
        self.at += n;
    }
    fn eat(&mut self, c: char) -> bool {
        self.skip_ws();
        if self.rest().starts_with(c) {
            self.at += c.len_utf8();
            true
        } else {
            false
        }
    }
    fn ident(&mut self) -> String {
        self.skip_ws();
        let n: usize = self.rest().chars().take_while(|c| c.is_alphanumeric() || *c == '_').map(char::len_utf8).sum();
        let out = self.rest().get(..n).unwrap_or("").to_string();
        self.at += n;
        out
    }
    fn int(&mut self) -> Option<i64> {
        self.skip_ws();
        let neg = self.rest().starts_with('-');
        let start = self.at + usize::from(neg);
        let digits: usize = self.s.get(start..).unwrap_or("").chars().take_while(char::is_ascii_digit).count();
        if digits == 0 || digits > 9 {
            return None;
        }
        let n: i64 = self.s.get(start..start + digits)?.parse().ok()?;
        self.at = start + digits;
        Some(if neg { -n } else { n })
    }
    /// The base of a token and whether it has a header row.
    fn base(&mut self, env: &Env) -> R<(Place, bool)> {
        self.skip_ws();
        if self.rest().get(..5).is_some_and(|h| h.eq_ignore_ascii_case("name:")) {
            self.at += 5;
            // A name can hold periods (`Sales.Q1`), so it is the longest part of the run, up to
            // a period or the end, that names something; methods follow it.
            let n: usize = self.rest().chars().take_while(|c| c.is_alphanumeric() || matches!(c, '_' | '\\' | '.')).map(char::len_utf8).sum();
            let run = self.rest().get(..n).unwrap_or("").to_string();
            let end = name_end(&run, env);
            let name = run.get(..end).unwrap_or("");
            if name.is_empty() {
                return bad("'Name:' needs a defined name after it.");
            }
            self.at += end;
            return Ok((named(name, env)?, false));
        }
        let base = self.ident();
        if base.eq_ignore_ascii_case("Output") {
            return env.output.map(|p| (p, false)).ok_or_else(|| ResolveError::Bad("'Output' is used, but the macro has no output anchor.".into()));
        }
        if base.eq_ignore_ascii_case("Row") || base.eq_ignore_ascii_case("Column") {
            return match env.item {
                Some((_, p)) => Ok((p, false)),
                None => bad(format!("'{base}' only means something inside a Repeat step.")),
            };
        }
        match env.inputs.iter().find(|(n, ..)| n.eq_ignore_ascii_case(&base)) {
            Some((_, p, header)) => Ok((*p, *header)),
            None => bad(format!("'{base}' isn't an input of this macro.")),
        }
    }
    fn args(&mut self, t: &str) -> R<Vec<Arg>> {
        let mut out = Vec::new();
        if self.eat(')') {
            return Ok(out);
        }
        loop {
            self.skip_ws();
            if self.rest().starts_with('"') {
                self.at += 1;
                let n = self.rest().find('"').ok_or_else(|| ResolveError::Bad(format!("'{t}': a quote isn't closed.")))?;
                out.push(Arg::Text(self.rest().get(..n).unwrap_or("").to_string()));
                self.at += n + 1;
            } else {
                let a = self.int();
                self.skip_ws();
                if self.rest().starts_with("..") {
                    self.at += 2;
                    let b = self.int();
                    out.push(Arg::Span(a, b));
                } else {
                    out.push(Arg::Int(a.ok_or_else(|| ResolveError::Bad(format!("'{t}': expected a whole number.")))?));
                }
            }
            if out.len() > 4 {
                return bad(format!("'{t}': too many arguments."));
            }
            if self.eat(')') {
                return Ok(out);
            }
            if !self.eat(',') {
                return bad(format!("'{t}': expected ',' or ')'."));
            }
        }
    }
}

/// `place` as A1 text, with the sheet name when it isn't the active sheet.
pub fn a1(place: Place, env: &Env) -> String {
    if place.sheet == env.active_sheet {
        return place.range.a1();
    }
    let name = env.wb.sheet(place.sheet).map(|s| s.name.as_str()).unwrap_or("");
    format!("{}!{}", quote_sheet(name), place.range.a1())
}

/// `Sheet1` or `'My Sheet'` (quotes doubled).
pub fn quote_sheet(name: &str) -> String {
    // The engine's one rule: a name that reads as a cell reference, an R1C1 token or a boolean
    // is quoted too, or `Q1!A1` would be read as a reference to cell Q1.
    crate::base::quote_sheet(name)
}

/// Step parameters with every token replaced by A1 text.
pub fn resolve_params(params: &Json, env: &Env) -> R<Json> {
    walk(params, None, env, 0)
}

fn walk(v: &Json, key: Option<&str>, env: &Env, depth: usize) -> R<Json> {
    if depth > MAX_JSON_DEPTH {
        return bad("The step's parameters nest too deeply.");
    }
    Ok(match v {
        Json::Object(o) => {
            if o.len() == 1
                && let Some(Json::String(t)) = o.get("$ref")
            {
                return Ok(Json::String(a1(resolve_ref(t, env)?, env)));
            }
            // Text passed as it is: never a token, and no `{{…}}` in it is read.
            if o.len() == 1
                && let Some(Json::String(t)) = o.get("$text")
            {
                return Ok(Json::String(t.clone()));
            }
            let mut out = Map::new();
            for (k, x) in o {
                out.insert(k.clone(), walk(x, Some(k), env, depth + 1)?);
            }
            Json::Object(out)
        }
        // Items of a list keep their key's kind (`ranges: ["Input", …]`).
        Json::Array(a) => Json::Array(a.iter().map(|x| walk(x, key, env, depth + 1)).collect::<R<Vec<_>>>()?),
        Json::String(s) => {
            if key.is_some_and(|k| is_ref_key(k) && !(is_general_key(k) && is_bare_item_word(s))) && is_token(s, env) {
                Json::String(a1(resolve_token(s, env)?, env))
            } else {
                Json::String(interpolate(s, env)?)
            }
        }
        other => other.clone(),
    })
}

/// Replaces each `{{token}}` in `s`. `{{{{` is a literal `{{`, for text that needs the braces
/// themselves (`"Dear {{{{name}}"` writes `Dear {{name}}`).
pub fn interpolate(s: &str, env: &Env) -> R<String> {
    if !s.contains("{{") {
        return Ok(s.to_string());
    }
    let mut out = String::with_capacity(s.len());
    let mut rest = s;
    while let Some(i) = rest.find("{{") {
        out.push_str(rest.get(..i).unwrap_or(""));
        if rest.get(i..i + 4) == Some("{{{{") {
            out.push_str("{{");
            rest = rest.get(i + 4..).unwrap_or("");
            continue;
        }
        let after = rest.get(i + 2..).unwrap_or("");
        let Some(j) = after.find("}}") else {
            out.push_str(rest.get(i..).unwrap_or(""));
            return Ok(out);
        };
        let token = after.get(..j).unwrap_or("");
        out.push_str(&a1(resolve_ref(token, env)?, env));
        rest = after.get(j + 2..).unwrap_or("");
    }
    out.push_str(rest);
    Ok(out)
}

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

    fn wb() -> Workbook {
        let mut wb = Workbook::new();
        wb.sheets.push(std::sync::Arc::new(crate::model::Sheet::new("My Data")));
        let sh = wb.sheet_mut(0).unwrap();
        for (i, h) in ["Name", "Amount", "Date"].iter().enumerate() {
            sh.set_value(CellRef::new(1, 1 + i as u32), crate::base::Value::text(*h));
        }
        wb.names.push(crate::model::DefinedName {
            name: "Total".into(),
            scope: None,
            formula: "Sheet1!$F$20".into(),
            comment: String::new(),
            hidden: false,
        });
        wb.names.push(crate::model::DefinedName {
            name: "Rates".into(),
            scope: None,
            formula: "'My Data'!$A$1:$A$5".into(),
            comment: String::new(),
            hidden: false,
        });
        wb
    }

    fn place(a1: &str) -> Place {
        Place { sheet: 0, range: RangeRef::parse(a1).unwrap() }
    }

    fn check(env: &Env, token: &str, want: &str) {
        match resolve_ref(token, env) {
            Ok(p) => assert_eq!(a1(p, env), want, "{token}"),
            Err(e) => panic!("{token}: {e}"),
        }
    }

    #[test]
    fn a_formula_header_matches_while_formulas_are_shown() {
        // The header was read through the cell's display text, which is the formula while Show
        // Formulas is on, so `col("Amount")` found no column headed by a formula.
        let mut wb = wb();
        let sh = wb.sheet_mut(0).unwrap();
        let formula = Some(std::sync::Arc::new(crate::model::Formula::new("\"Amo\"&\"unt\"")));
        sh.set_cell(
            CellRef::new(1, 2),
            crate::model::Cell { value: crate::base::Value::text("Amount"), formula, style: Default::default(), py: None },
        );
        sh.show_formulas = true;
        let inputs = vec![("Input".to_string(), place("B2:D11"), true)];
        let env = Env { wb: &wb, active_sheet: 0, inputs: &inputs, output: None, item: None };
        check(&env, "Input.col(\"Amount\")", "C2:C11");
    }

    #[test]
    fn every_token_resolves() {
        let wb = wb();
        // Input B2:D11 with a header row; Other on another sheet.
        let inputs = vec![
            ("Input".to_string(), place("B2:D11"), true),
            ("Other".to_string(), Place { sheet: 1, range: RangeRef::parse("C3:C4").unwrap() }, false),
        ];
        let env = Env { wb: &wb, active_sheet: 0, inputs: &inputs, output: Some(place("G2")), item: Some((RepeatOf::Rows, place("B5:D5"))) };
        for (token, want) in [
            ("Input", "B2:D11"),
            ("input", "B2:D11"),
            ("Input.col(2)", "C2:C11"),
            ("Input.col(\"Amount\")", "C2:C11"),
            ("Input.col(\"date\").data", "D3:D11"),
            ("Input.cols(2..3).data", "C3:D11"),
            ("Input.col(2).data.col(1)", "C3:C11"),
            ("Input.data", "B3:D11"),
            ("Input.data.col(2)", "C3:C11"),
            ("Input.data.col(\"Amount\")", "C3:C11"),
            ("Input.row(1)", "B2:D2"),
            ("Input.rows(2..)", "B3:D11"),
            ("Input.rows(2..4)", "B3:D5"),
            ("Input.rows(..2)", "B2:D3"),
            ("Input.cols(2..3)", "C2:D11"),
            ("Input.cell(1,1)", "B2"),
            ("Input.cell(3,2)", "C4"),
            ("Input.cell(0,1)", "B1"),
            ("Input.cell(12,3)", "D13"),
            ("Input.offset(0,4)", "F2:H11"),
            ("Input.offset(-1,-1)", "A1:C10"),
            ("Input.cell(1,1).resize(2,3)", "B2:D3"),
            ("Input.below()", "B12:D12"),
            ("Input.below(2)", "B12:D13"),
            ("Input.col(2).below", "C12"),
            ("Input.right", "E2:E11"),
            (" Input . col( 2 ) ", "C2:C11"),
            ("Output", "G2"),
            ("Output.offset(1,0)", "G3"),
            ("Row", "B5:D5"),
            ("Row.cell(1,2)", "C5"),
            ("Column", "B5:D5"),
            ("Name:Total", "F20"),
            ("Name:Rates", "'My Data'!A1:A5"),
            ("Name:Rates.cell(2,1)", "'My Data'!A2"),
            ("Other", "'My Data'!C3:C4"),
            ("B7", "B7"),
            ("'My Data'!A1:B2", "'My Data'!A1:B2"),
            ("Total", "F20"),
        ] {
            check(&env, token, want);
        }
    }

    #[test]
    fn bad_tokens_say_why() {
        let wb = wb();
        // The same cells, read with and without a header row.
        let inputs = vec![("Input".to_string(), place("B2:D11"), false), ("Headed".to_string(), place("B2:D11"), true)];
        let env = Env { wb: &wb, active_sheet: 0, inputs: &inputs, output: None, item: None };
        for (token, needle, edge) in [
            ("Input.col(4)", "outside the range", false),
            ("Input.col(0)", "outside the range", false),
            ("Input.rows(5..2)", "outside the range", false),
            ("Headed.col(\"Nope\")", "no column is headed", false),
            ("Input.col(\"Name\")", "needs an input with a header row", false),
            ("Headed.col(2).col(\"Amount\")", "needs an input with a header row", false),
            ("Name:Rates.col(\"Name\")", "needs an input with a header row", false),
            ("Input.colour(1)", "isn't a token method", false),
            ("Input.col(1", "expected", false),
            ("Input.col(x)", "whole number", false),
            ("Input.col(99999999999)", "whole number", false),
            ("Input.", "method name", false),
            ("Input junk", "unexpected", false),
            ("Input.resize(0,1)", "at least 1 row", false),
            ("Output", "no output anchor", false),
            ("Row", "inside a Repeat", false),
            ("Name:", "needs a defined name", false),
            ("Name:Nothing", "no name or table", false),
            ("Nowhere!A1", "no sheet named", false),
            ("Input.offset(-2,0)", "past the top", true),
            ("Input.offset(0,-2)", "edge of the sheet", true),
            ("Input.offset(1048570,0)", "past the last row", true),
            ("Input.cell(1,16384)", "edge of the sheet", true),
        ] {
            let e = resolve_ref(token, &env).unwrap_err();
            assert!(e.message().contains(needle), "{token}: {e}");
            assert_eq!(matches!(e, ResolveError::Edge(_)), edge, "{token}");
        }
    }

    #[test]
    fn params_resolve_tokens_in_reference_keys_and_braces_anywhere() {
        let wb = wb();
        let inputs = vec![("Input".to_string(), place("B2:D11"), true)];
        let env = Env { wb: &wb, active_sheet: 0, inputs: &inputs, output: Some(place("F2")), item: None };
        let p = json!({
            "cell": "Output",
            "range": "Input.col(2)",
            "ranges": ["Input.row(1)", "A1"],
            "input": "Row",
            "value": "Input",
            "formula": "=SUM({{Input.data.col(2)}})/{{Name:Total}}",
            "nested": {"target": {"$ref": "Input.cell(1,1)"}, "n": 3, "flag": true},
            "text": {"$ref": "Output"},
            "type": "column",
            "sheet": "Input",
            "rowInputCell": "Input.cell(1,1)",
            "unclosed": "a {{b"
        });
        let r = resolve_params(&p, &env).unwrap();
        assert_eq!(
            r,
            json!({
                "cell": "F2",
                "range": "C2:C11",
                "ranges": ["B2:D2", "A1"],
                "input": "Row",
                "value": "Input",
                "formula": "=SUM(C3:C11)/F20",
                "nested": {"target": "B2", "n": 3, "flag": true},
                "text": "F2",
                "type": "column",
                "sheet": "Input",
                "rowInputCell": "B2",
                "unclosed": "a {{b"
            })
        );
        // Deeply nested parameters are refused, not followed.
        let mut deep = json!(1);
        for _ in 0..100 {
            deep = json!([deep]);
        }
        assert!(resolve_params(&deep, &env).is_err());
        assert!(resolve_params(&json!({"cell": "Input.col(9)"}), &env).is_err());
    }

    #[test]
    fn references_on_sheets_named_like_tokens_stay_references() {
        // Sheets named after the built-in bases and after an input: `Output!A1` is a cell on the
        // sheet Output, in a reference key, in `$ref` and in `{{…}}`.
        let mut wb = Workbook::new();
        for name in ["Output", "Row", "Column", "Input", "Rates"] {
            wb.sheets.push(std::sync::Arc::new(crate::model::Sheet::new(name)));
        }
        let inputs = vec![("Input".to_string(), place("B2:D11"), true), ("Rates".to_string(), place("F2:F5"), false)];
        for output in [None, Some(place("H2"))] {
            let env = Env { wb: &wb, active_sheet: 0, inputs: &inputs, output, item: Some((RepeatOf::Rows, place("B5:D5"))) };
            for (sheet, i) in [("Output", 1), ("Row", 2), ("Column", 3), ("Input", 4), ("Rates", 5)] {
                for text in [format!("{sheet}!A1:B2"), format!("'{sheet}'!A1:B2")] {
                    let want = Place { sheet: i, range: RangeRef::parse("A1:B2").unwrap() };
                    assert_eq!(resolve_ref(&text, &env), Ok(want), "{text}");
                    let a = a1(want, &env);
                    let p = json!({"range": text, "target": {"$ref": text}, "formula": format!("=SUM({{{{{text}}}}})")});
                    let r = resolve_params(&p, &env).unwrap();
                    // A plain reference in a reference key is passed on as written.
                    assert_eq!(r, json!({"range": text, "target": a, "formula": format!("=SUM({a})")}), "{text}");
                }
            }
            assert!(!is_token("Output !A1", &env));
            // The bare names are still tokens.
            check(&env, "Input.col(2)", "C2:C11");
            check(&env, "Rates", "F2:F5");
            check(&env, "Row", "B5:D5");
        }
    }

    #[test]
    fn defined_names_can_hold_periods() {
        // `Sales.Q1` is one defined name, beside `Sales`; `Data.row` has a segment that reads like
        // a method. The longest name that exists is used, and methods follow it.
        let mut wb = wb();
        for (name, formula) in [("Sales", "Sheet1!$A$1"), ("Sales.Q1", "Sheet1!$B$2:$C$5"), ("Data.row", "'My Data'!$E$1:$E$3")] {
            wb.names.push(crate::model::DefinedName {
                name: name.into(),
                scope: None,
                formula: formula.into(),
                comment: String::new(),
                hidden: false,
            });
        }
        let env = Env { wb: &wb, active_sheet: 0, inputs: &[], output: None, item: None };
        for (token, want) in [
            ("Name:Sales.Q1", "B2:C5"),
            ("Name:sales.q1", "B2:C5"),
            ("Name:Sales.Q1.cell(1,1)", "B2"),
            ("Name:Sales.Q1.col(2).row(4)", "C5"),
            ("Name:Sales", "A1"),
            ("Name:Sales.offset(1,0)", "A2"),
            ("Name:Data.row", "'My Data'!E1:E3"),
            ("Name:Data.row.row(2)", "'My Data'!E2"),
            ("Name:Data.row.cell(1,1)", "'My Data'!E1"),
            ("Sales.Q1", "B2:C5"),
        ] {
            check(&env, token, want);
        }
        // In a reference parameter, `$ref` and `{{…}}`, with and without a method.
        let p = json!({
            "range": "Name:Sales.Q1",
            "cell": "Name:Sales.Q1.cell(1,1)",
            "a": {"$ref": "Name:Sales.Q1"},
            "b": {"$ref": "Name:Sales.Q1.cell(2,2)"},
            "formula": "=SUM({{Name:Sales.Q1}})+{{Name:Sales.Q1.cell(1,2)}}"
        });
        let r = resolve_params(&p, &env).unwrap();
        assert_eq!(r, json!({"range": "B2:C5", "cell": "B2", "a": "B2:C5", "b": "C3", "formula": "=SUM(B2:C5)+C2"}));
        // Unknown names and bad methods still fail, naming what is wrong.
        for (token, needle) in [
            ("Name:Nope.Q1", "no name or table called 'Nope.Q1'"),
            ("Name:Nope.cell(1,1)", "no name or table called 'Nope'"),
            ("Name:Sales.Q1.bogus(1)", "'bogus' with these arguments isn't a token method"),
            ("Name:Sales.Q1.cell(1", "expected"),
            ("Name:Sales.Q1.", "method name"),
            ("Name:.cell(1,1)", "needs a defined name"),
        ] {
            let e = resolve_ref(token, &env).unwrap_err();
            assert!(e.message().contains(needle), "{token}: {e}");
            let e = resolve_params(&json!({"formula": format!("={{{{{token}}}}}")}), &env).unwrap_err();
            assert!(e.message().contains(needle), "{token} in braces: {e}");
        }
    }

    #[test]
    fn sheet_names_are_quoted_when_needed() {
        // The engine's rule: a name that reads as a cell reference, an R1C1 token or a boolean
        // is quoted too, and a period on its own is not a reason to quote.
        for (name, want) in [
            ("Sheet1", "Sheet1"),
            ("My Data", "'My Data'"),
            ("O'Hara", "'O''Hara'"),
            ("2024", "'2024'"),
            ("", "''"),
            ("Q1", "'Q1'"),
            ("R1C1", "'R1C1'"),
            ("TRUE", "'TRUE'"),
            ("Sales.Q1", "Sales.Q1"),
        ] {
            assert_eq!(quote_sheet(name), want, "{name}");
        }
    }

    #[test]
    fn a_bare_row_or_column_under_a_general_key_is_text() {
        // `to` is a replacement word in Change Spelling and `area` is `row` or `column` in a
        // pivot field move, so a bare `Row` or `Column` there stays text, inside a Repeat or not;
        // the token forms still resolve there, and the bare word resolves under `range`.
        let wb = wb();
        let inputs = vec![("Input".to_string(), place("B2:D11"), true)];
        for item in [None, Some((RepeatOf::Rows, place("B5:D5")))] {
            let env = Env { wb: &wb, active_sheet: 0, inputs: &inputs, output: Some(place("F2")), item };
            let p = json!({"word": "Colum", "to": "Column", "area": "row", "from": "Input", "at": "Output"});
            let r = resolve_params(&p, &env).unwrap();
            assert_eq!(r, json!({"word": "Colum", "to": "Column", "area": "row", "from": "B2:D11", "at": "F2"}), "{item:?}");
        }
        let env = Env { wb: &wb, active_sheet: 0, inputs: &inputs, output: None, item: Some((RepeatOf::Rows, place("B5:D5"))) };
        let p = json!({"to": "Row.col(2)", "range": "Row", "area": {"$ref": "Row"}, "text": "{{Row}}"});
        let r = resolve_params(&p, &env).unwrap();
        assert_eq!(r, json!({"to": "C5", "range": "B5:D5", "area": "B5:D5", "text": "B5:D5"}));
    }

    #[test]
    fn a_header_row_alone_and_a_table_without_data_rows_are_refused() {
        let mut wb = wb();
        let sh = wb.sheet_mut(0).unwrap();
        sh.tables.push(crate::model::Table {
            id: 1,
            name: "Empty".into(),
            range: RangeRef::parse("H1:J1").unwrap(),
            header_row: true,
            totals_row: false,
            columns: Vec::new(),
            style: String::new(),
            banded_rows: false,
            banded_cols: false,
            first_col: false,
            last_col: false,
            filter_button: false,
            filter: Vec::new(),
            table_type: None,
            connection_id: None,
        });
        let inputs = vec![("Input".to_string(), place("B2:D2"), true), ("Plain".to_string(), place("B2:D2"), false)];
        let env = Env { wb: &wb, active_sheet: 0, inputs: &inputs, output: None, item: None };
        assert!(resolve_ref("Input.data", &env).unwrap_err().to_string().contains("only a header row"));
        assert!(resolve_ref("Input.data.col(2)", &env).unwrap_err().to_string().contains("only a header row"));
        check(&env, "Plain.data", "B2:D2");
        check(&env, "Input.col(2)", "C2");
        assert!(resolve_ref("Name:Empty", &env).unwrap_err().to_string().contains("no data rows"));
    }

    #[test]
    fn doubled_braces_are_literal_braces() {
        let wb = wb();
        let inputs = vec![("Input".to_string(), place("B2:D11"), true)];
        let env = Env { wb: &wb, active_sheet: 0, inputs: &inputs, output: None, item: None };
        assert_eq!(interpolate("Dear {{{{name}},", &env).unwrap(), "Dear {{name}},");
        assert_eq!(interpolate("{{{{ {{Input.cell(1,1)}} }}", &env).unwrap(), "{{ B2 }}");
        assert!(interpolate("Dear {{name}},", &env).is_err(), "an unknown token is still an error");
    }

    #[test]
    fn text_marked_as_text_is_never_a_token() {
        // A defined name called Output or Input in a reference parameter, a Name: prefix and
        // braces: `$text` passes each as it is, in any key; unmarked, each reads as a token.
        let wb = wb();
        let inputs = vec![("Input".to_string(), place("B2:D11"), true)];
        let env = Env { wb: &wb, active_sheet: 0, inputs: &inputs, output: None, item: None };
        for text in ["Output", "Input", "Name:Nowhere", "Row", "Dear {{name}}"] {
            let p = json!({"range": {"$text": text}, "input": {"$text": text}});
            assert_eq!(resolve_params(&p, &env).unwrap(), json!({"range": text, "input": text}), "{text}");
            assert_ne!(resolve_params(&json!({"range": text}), &env).ok(), Some(json!({"range": text})), "{text}");
            assert!(reads_as_token(text, &["Input"]) || text.contains("{{"), "{text}");
        }
        assert!(!reads_as_token("Output!A1", &["Input"]) && !reads_as_token("B2", &["Input"]));
    }

    #[test]
    fn a_defined_name_on_a_sheet_with_a_dollar_sign_resolves() {
        let mut wb = wb();
        wb.sheets.push(std::sync::Arc::new(crate::model::Sheet::new("US$")));
        wb.names.push(crate::model::DefinedName {
            name: "Dollars".into(),
            scope: None,
            formula: "'US$'!$F$20".into(),
            comment: String::new(),
            hidden: false,
        });
        let env = Env { wb: &wb, active_sheet: 0, inputs: &[], output: None, item: None };
        check(&env, "Name:Dollars", "'US$'!F20");
    }

    #[test]
    fn below_and_right_refuse_a_count_under_one() {
        let wb = wb();
        let inputs = vec![("Input".to_string(), place("B2:D11"), true)];
        let env = Env { wb: &wb, active_sheet: 0, inputs: &inputs, output: None, item: None };
        check(&env, "Input.below", "B12:D12");
        check(&env, "Input.below(2)", "B12:D13");
        check(&env, "Input.right(1)", "E2:E11");
        for bad in ["Input.below(0)", "Input.below(-3)", "Input.right(0)"] {
            assert!(resolve_ref(bad, &env).unwrap_err().to_string().contains("at least 1"), "{bad}");
        }
    }
}