Skip to content

armgit / bento

Public Imported from GitHub
0

Bento, the office suite that fits in a file

28.4 KB
Raw Blame
// SPDX-License-Identifier: MIT
// Copyright (c) 2026 The Bento authors
// A1 addressing — cell references, ranges, and the only place `#REF!` is minted.
//
// dash's COLUMN formulas do not have the `#REF!` problem. A column expression
// names a column by IDENTITY (model.ts `Column.id`), so inserting a row changes
// nothing, deleting a row changes nothing, and dragging a column changes
// nothing. That is the structural win formula.ts's header claims, and it is
// real.
//
// A1 references give it back, and they have to. An invoice, a scratch pad, a
// totals block — the `CanvasSheet` cases — are POSITIONAL by nature, and a
// person typing `=B4*1.2` is not going to be talked out of it. So the two
// coexist, and the entire cost of admitting them is concentrated in this file:
// an A1 reference is a POSITION, positions move, and every structural edit has
// to move them EXACTLY. Close is the dangerous answer, not the safe one — a
// reference left pointing at whatever slid into the deleted row is a wrong
// number wearing a right number's clothes, the same sin as an error that
// becomes a zero.
//
// EVERYTHING HERE IS 0-BASED, columns AND rows: `A1` parses to {col:0, row:0}.
// The `1` in `A1` is display exactly as the `A` is. The grid, the store's row
// indices and `readCell` are all 0-based, so a 1-based row here would put a ±1
// at every call site and one of them would eventually be wrong. `formatRef` is
// the only place the +1 lives.
//
// `$` PINS AGAINST FILL, NOT AGAINST STRUCTURE — the rule that gets
// re-litigated every time someone reads it. `translateRef` (fill-down,
// copy-paste) leaves `$A$1` alone; `shiftRefsForInsert` (a row or column was
// inserted or deleted) MOVES it, because the cell it names physically moved.
// Insert a row above `$A$5` in Excel and you get `$A$6`. A "$ never moves"
// implementation is wrong in the direction that quietly re-points references at
// other people's data.
//
// WHAT IS NOT A REFERENCE, when rewriting an expression:
//   • text inside "quotes" — `="A1 is "&A1` shifted one row is `="A1 is "&A2`;
//   • a [bracketed name] — formula.ts's escape for a column whose name has
//     spaces, and the escape hatch for a column named like a cell;
//   • a name followed by `(` — `LOG10(x)` is a call, and `LOG10` is otherwise
//     a perfectly good cell address;
//   • a name followed by `[` — `Table1[Col]` qualifies something, and `Table1`
//     is itself cell-shaped;
//   • anything over 3 letters or 7 digits. That is Excel's bound (XFD1048576),
//     and here it is also what keeps `REVENUE2024` a column name instead of a
//     cell address. `REV2024` is genuinely ambiguous and resolves as a CELL, as
//     it does in every spreadsheet on earth — write `[REV2024]` to mean the
//     column.
//
// A NAME BEFORE `!` IS A SHEET, AND THE REFERENCE AFTER IT IS ONE UNIT. This
// file used to skip the name and then scan `A1` on its own, which meant
// `Sheet1!A1` bound to the LOCAL A1: the qualifier was dropped and the formula
// read a different sheet's cell than the one it named. That is the exact
// failure this file exists to prevent, so a qualified reference is now scanned
// whole and carries its sheet on the `Unit`. `'Q3 pipeline'!A1` is the quoted
// form, for names that are not bare words; `''` inside the quotes is one quote.
//
// A DEFINED NAME IS THE THIRD KIND OF NAME, and the scanner now offers it up
// rather than skipping it. `Sheet1!A1` is a sheet qualifier, `Table1[Col]` is a
// structured reference, and a bare word that is neither — not cell-shaped, not
// a call, not a qualifier — is what `TaxRate` looks like. `mapNames` walks with
// the SAME rules the reference rewrites use, because a second walk would be a
// second definition of what a word is, and the two would disagree about a
// quoted string on some Tuesday. This file does not know what a name MEANS;
// cellformula.ts owns the table and the substitution.
//
// AN ERROR LITERAL IS COPIED VERBATIM. `#REF!`, `#DIV/0!`, `#N/A` — the `REF`
// in `#REF!` is a word followed by `!` and would otherwise read as a sheet
// qualifier now that qualifiers are scanned.

/** The one error this file can produce. Visible, never a silent fallback. */
export const REF_ERR = '#REF!'

/**
 * Cells `expandRange` will materialise before refusing.
 *
 * Excel's sheet is 1,048,576 rows, so `A1:A1048576` is a thing a person can
 * type — and expanding it as objects is a ~200 MB allocation and a dead tab.
 * Refusing with `null` is answerable; a hang is not.
 */
export const RANGE_CELL_MAX = 1_000_000

/** Excel's bound (XFD = 16383), and the fence that keeps `REVENUE2024` a name. */
const MAX_LETTERS = 3
const MAX_DIGITS = 7

export interface CellRef {
  /** 0-based. A = 0. */
  col: number
  /** 0-based. The `1` of `A1` is display. */
  row: number
  absCol: boolean
  absRow: boolean
}

export interface RangeRef { from: CellRef; to: CellRef }

// --- Column letters ---------------------------------------------------------
// BIJECTIVE base-26, which is the part everyone gets wrong. There is no zero
// digit: after Z comes AA, not BA. Plain base-26 (A=0) makes 26 into "BA" and
// gives two spellings for every column, so the round trip stops holding at the
// 27th column — where nobody looks.

/** 0→A, 25→Z, 26→AA, 701→ZZ, 702→AAA. `''` for a non-address. */
export function colToLetters(i: number): string {
  if (!Number.isInteger(i) || i < 0) return ''
  let s = ''
  let n = i
  while (n >= 0) {
    s = String.fromCharCode(65 + (n % 26)) + s
    n = Math.floor(n / 26) - 1
  }
  return s
}

/** The inverse, case-insensitive. `-1` for anything that is not letters. */
export function lettersToCol(s: string): number {
  if (!s) return -1
  let n = 0
  for (let i = 0; i < s.length; i++) {
    // | 32 lowercases a letter and moves everything else out of range, so the
    // rejection is the same branch as the fold.
    const c = s.charCodeAt(i) | 32
    if (c < 97 || c > 122) return -1
    n = n * 26 + (c - 96)
  }
  return n - 1
}

// --- References -------------------------------------------------------------

const REF_RE = new RegExp(`^(\\$?)([A-Za-z]{1,${MAX_LETTERS}})(\\$?)([0-9]{1,${MAX_DIGITS}})$`)

/** `A1`, `$A$1`, `A$1`, `$A1`, any case. `null` if it is not an address. */
export function parseRef(s: string): CellRef | null {
  const m = REF_RE.exec(s.trim())
  if (!m) return null
  const col = lettersToCol(m[2])
  // `A0` is not a cell: rows are 1-based on the page, so 0 is a typo, and
  // parsing it to row -1 would let it format straight back out as `A0`.
  const row = Number(m[4]) - 1
  if (col < 0 || row < 0) return null
  return { col, row, absCol: m[1] === '$', absRow: m[3] === '$' }
}

/**
 * The inverse. Canonical: letters uppercase, `$` where the ref says.
 *
 * An address that cannot exist formats as `#REF!` rather than as `A0` or `''`.
 * This is deliberately the ONLY place the error is minted — every shift below
 * builds a CellRef and hands it here, so no caller can forget the check and
 * emit a plausible address for a cell that fell off the sheet.
 */
export function formatRef(r: CellRef): string {
  const letters = colToLetters(r.col)
  if (!letters || !Number.isInteger(r.row) || r.row < 0) return REF_ERR
  return `${r.absCol ? '$' : ''}${letters}${r.absRow ? '$' : ''}${r.row + 1}`
}

// --- Ranges -----------------------------------------------------------------

/** `A1:B10`. `null` unless BOTH ends are addresses. */
export function parseRange(s: string): RangeRef | null {
  const i = s.indexOf(':')
  if (i < 0) return null
  const from = parseRef(s.slice(0, i))
  const to = parseRef(s.slice(i + 1))
  return from && to ? { from, to } : null
}

export const formatRange = (r: RangeRef): string => `${formatRef(r.from)}:${formatRef(r.to)}`

// --- Sheet qualifiers -------------------------------------------------------

/** A bare sheet name: a word, and not itself a cell address. */
const BARE_SHEET = /^[A-Za-z_][A-Za-z0-9_.]*$/

/**
 * A sheet name as a reference must write it.
 *
 * Quoted when it is not a bare word — and ALSO when it is cell-shaped, so a
 * sheet somebody called `A1` reads as `'A1'!B2` and can never be mistaken for
 * an address by the scanner that has to read it back.
 */
export function quoteSheet(name: string): string {
  if (BARE_SHEET.test(name) && !parseRef(name)) return name
  return `'${name.replace(/'/g, "''")}'`
}

/** `Sheet1!A1`, `'Q3 pipeline'!A1:B10`, or just `A1` when there is no sheet. */
export const qualify = (sheet: string | undefined, ref: string): string =>
  sheet === undefined ? ref : `${quoteSheet(sheet)}!${ref}`

/** The inverse of `quoteSheet`, for the quoted form only. */
const unquoteSheet = (text: string): string =>
  text.startsWith("'") ? text.slice(1, -1).replace(/''/g, "'") : text

/**
 * Do two sheet names mean the same sheet? Case-insensitively, as Excel has it —
 * `SUM(sheet1!A1)` typed in a hurry names `Sheet1`.
 */
export const sameSheet = (a: string | undefined, b: string | undefined): boolean =>
  (a ?? '').toLowerCase() === (b ?? '').toLowerCase()

/** Error literals — `#REF!`, `#DIV/0!`, `#N/A`. Never references, never touched. */
const ERR_LITERAL = /^#[A-Za-z]+(?:\/[A-Za-z0-9]+)?[!?]?/

/**
 * Every cell in the rectangle, in reading order (row-major), or `null` past
 * RANGE_CELL_MAX.
 *
 * The corners are normalised, so `B10:A1` expands the same rectangle as
 * `A1:B10` — a range is an area, and the order it was typed in is not data.
 * The count is computed before anything is allocated; that is the whole point
 * of the cap.
 *
 * Expanded cells carry `absCol/absRow: false`. They are ADDRESSES, and `$` is a
 * property of how a reference was WRITTEN, not of the cell it lands on.
 */
export function expandRange(r: RangeRef): CellRef[] | null {
  const c0 = Math.min(r.from.col, r.to.col)
  const c1 = Math.max(r.from.col, r.to.col)
  const r0 = Math.min(r.from.row, r.to.row)
  const r1 = Math.max(r.from.row, r.to.row)
  if (c0 < 0 || r0 < 0) return null
  if ((c1 - c0 + 1) * (r1 - r0 + 1) > RANGE_CELL_MAX) return null
  const out: CellRef[] = []
  for (let row = r0; row <= r1; row++) {
    for (let col = c0; col <= c1; col++) out.push({ col, row, absCol: false, absRow: false })
  }
  return out
}

// --- Translation (fill, copy/paste) -----------------------------------------

const shifted = (r: CellRef, dRow: number, dCol: number): string => formatRef({
  col: r.absCol ? r.col : r.col + dCol,
  row: r.absRow ? r.row : r.row + dRow,
  absCol: r.absCol,
  absRow: r.absRow,
})

/**
 * Move a reference by a fill/paste offset: relative parts shift, `$` parts do
 * not. This is what dragging a formula down a column does.
 *
 * Off the top or left edge is `#REF!` — filling `=A1` upward has no answer, and
 * clamping it to `A1` would silently make every row of the fill read the same
 * cell.
 *
 * A string that is not an address comes back UNCHANGED: callers hand this whole
 * tokens, and mangling `SUM` would be worse than ignoring it.
 */
export function translateRef(ref: string, dRow: number, dCol: number): string {
  const r = parseRef(ref)
  return r ? shifted(r, dRow, dCol) : ref
}

// --- The expression scanner -------------------------------------------------
// One walk over the source, used by both rewrites below. It is a SCANNER and
// not the parser from formula.ts on purpose: rewriting must return the author's
// text with only the references changed — comments, spacing and any syntax this
// build does not understand have to survive, and a parse→print round trip would
// quietly normalise all of it away.

interface Unit {
  from: CellRef
  to?: CellRef
  /** The sheet named before `!`, unquoted. Absent = this formula's own sheet. */
  sheet?: string
  /** Exactly what the author wrote, qualifier included — for a mapper that
   *  decides this unit is not its business and must hand it back untouched. */
  text: string
}
type MapUnit = (u: Unit) => string
/** A bare word that is not a reference, not a call and not a qualifier. */
type MapName = (name: string) => string

const isAlpha = (c: string): boolean => (c >= 'A' && c <= 'Z') || (c >= 'a' && c <= 'z')
const isDigit = (c: string): boolean => c >= '0' && c <= '9'
/** The identifier body formula.ts's lexer accepts, so words break identically. */
const isWord = (c: string): boolean => isAlpha(c) || isDigit(c) || c === '_' || c === '.'

/**
 * Walk `src` and hand every reference to `map`, splicing its return in place.
 *
 * Exported because cellformula.ts substitutes references for bound names using
 * the SAME walk the shifting rewrites use. A second scanner would be a second
 * definition of what counts as a reference, and the two would disagree about a
 * quoted string or a `[bracketed name]` on some Tuesday.
 */
export function mapRefs(src: string, map: MapUnit): string { return rewrite(src, map) }
export type { Unit as RefUnit }

/**
 * Walk `src` and hand every DEFINED-NAME candidate to `map`, splicing its
 * return in place. References, calls, sheet qualifiers, quoted text, bracketed
 * names and error literals are all left exactly as written.
 *
 * A word is offered only when nothing else claims it: `SUM` is followed by `(`,
 * `Sheet1` by `!`, `Table1` by `[`, `B4` is cell-shaped, and the word AFTER a
 * `!` belongs to the qualifier that could not be resolved — none of those are
 * a defined name, and substituting into one corrupts the formula.
 *
 * `map` returning the word unchanged is how "there is no name by that spelling"
 * is said; formula.ts then reports `#NAME?`, which is the honest answer and the
 * one it already gives.
 */
export function mapNames(src: string, map: MapName): string {
  return rewrite(src, null, map)
}

/** A sheet a source named, and the text that followed the `!`. */
export interface SheetQualifier {
  sheet: string
  /** `A1` for `Jan!A1`, `Amount` for `Jan!Amount` — an address, or not one. */
  after: string
}

/**
 * Every sheet a source QUALIFIES, in the order it names them, without
 * duplicates.
 *
 * The point is to be able to answer "this expression reaches another sheet"
 * WITHOUT being the thing that resolves it. formula.ts's column language is
 * defined over the columns of one sheet and has no concept of a workbook, so
 * `Jan!A1` reaches its lexer as the bare word `Jan` and comes back
 * `#NAME? unknown name "Jan"` — which is false twice over: the name is not
 * unknown, it is a sheet, and the reason it cannot be used is a boundary rather
 * than a typo. This is how the column path learns enough to say so.
 *
 * IT IS THE SAME SCANNER, deliberately. A second pass over the source looking
 * for `!` would be a second definition of what a qualifier is, and the two
 * would disagree the first time somebody wrote `="not a sheet!" & A1` — where
 * the `!` is inside a string literal this walk already skips.
 *
 * Both qualifier shapes are reported: `Jan!A1`, where a reference follows, and
 * `Jan!Amount`, where a COLUMN NAME follows. The second is the spelling a
 * dataset author reaches for and the one a naive `!`-hunt would find while a
 * reference-only walk would miss.
 */
/**
 * Every qualifier naming `from` respelled as `to` — what a sheet rename owes
 * the formulas on every OTHER sheet. Before this existed, renaming the
 * starter's `Pipeline` tab turned the four cross-sheet totals on `Scratch`
 * into `#REF!`, because a qualifier is a NAME and nothing rewrote it.
 *
 * Only the qualifier changes: the reference after it (and its spacing, its
 * case, its `$`) is copied as the author wrote it, so a formula that named the
 * old sheet differs from its old self by exactly the name. Quoting follows the
 * new name — `'Q3 deals'!A1` when it needs it, `Q3!A1` when it does not.
 */
export function renameSheetRefs(src: string, from: string, to: string): string {
  if (src.indexOf('!') < 0 || sameSheet(from, to)) return src
  return rewrite(src, null, undefined, undefined, { from, to })
}

export function sheetQualifiers(src: string): SheetQualifier[] {
  // Free when there is no `!` at all, which is almost every expression ever
  // evaluated — this runs in the recalculation hot path.
  if (src.indexOf('!') < 0) return []
  const seen = new Set<string>()
  const out: SheetQualifier[] = []
  rewrite(src, null, undefined, (sheet, after) => {
    const k = sheet.toLowerCase()
    if (seen.has(k)) return
    seen.add(k)
    out.push({ sheet, after })
  })
  return out
}

/**
 * Is this a spelling a defined name may have?
 *
 * The fence is the same one the header draws around `REVENUE2024`: a name that
 * is CELL-SHAPED is refused, because the scanner resolves `TAX1` as a cell
 * before it ever reaches the name table, so a name spelled that way would be
 * silently unreachable — a definition that exists and never applies is worse
 * than a refusal. The body is formula.ts's identifier, so a name that parses
 * here also lexes there.
 */
export const NAME_RE = /^[A-Za-z_][A-Za-z0-9_.]*$/
export const isNameLike = (s: string): boolean =>
  NAME_RE.test(s) && parseRef(s) === null

/**
 * `map` may be `null`: a walk that is only substituting NAMES must return every
 * reference as the author wrote it, spacing and lower case included. Running
 * them through a mapper that formats a parsed `CellRef` would silently
 * canonicalise `a1 : b2` into `A1:B2` — correct, unasked for, and a diff in
 * every formula a name touches.
 */
function rewrite(
  src: string,
  map: MapUnit | null,
  mapName?: MapName,
  onQualifier?: (sheet: string, after: string) => void,
  renameSheet?: { from: string; to: string },
): string {
  const n = src.length
  const skipSpace = (j: number): number => {
    while (j < n && (src[j] === ' ' || src[j] === '\t' || src[j] === '\n' || src[j] === '\r')) j++
    return j
  }
  /** A maximal word, `$` included so `$A$1` is one token. */
  const word = (j: number): number => {
    while (j < n && (isWord(src[j]) || src[j] === '$')) j++
    return j
  }

  /**
   * A reference or a range starting at `j`, and where it ends — or `null` if
   * what is there is not one. The lookahead guards are the same three as
   * below: a name followed by `(`, `!` or `[` qualifies something else.
   */
  const refAt = (j: number): { unit: Unit; end: number } | null => {
    const e = word(j)
    const from = parseRef(src.slice(j, e))
    if (!from) return null
    const k = skipSpace(e)
    if (src[k] === '(' || src[k] === '!' || src[k] === '[') return null
    // A range is ONE unit: deleting the rows under `A3:A5` has to collapse the
    // whole thing to `#REF!`, which an endpoint on its own cannot know.
    if (src[k] === ':') {
      const m = skipSpace(k + 1)
      const e2 = word(m)
      const to = parseRef(src.slice(m, e2))
      const after = skipSpace(e2)
      if (to && src[after] !== '(' && src[after] !== '!' && src[after] !== '[') {
        return { unit: { from, to, text: src.slice(j, e2) }, end: e2 }
      }
    }
    return { unit: { from, text: src.slice(j, e) }, end: e }
  }

  /** `'Q3 pipeline'` at `j` — the index after the closing quote, or -1. */
  const quoted = (j: number): number => {
    let p = j + 1
    while (p < n) {
      if (src[p] === "'") {
        if (src[p + 1] === "'") { p += 2; continue }
        return p + 1
      }
      p++
    }
    return -1
  }

  /** `<name>!<ref>` starting at the qualifier's end `q`, or null. */
  const qualified = (name: string, at: number, q: number): { text: string; end: number } | null => {
    const k = skipSpace(q)
    if (src[k] !== '!') return null
    const r = refAt(skipSpace(k + 1))
    if (!r) return null
    const text = src.slice(at, r.end)
    return { text: map ? map({ ...r.unit, sheet: name, text }) : text, end: r.end }
  }

  let out = ''
  let i = 0
  while (i < n) {
    const c = src[i]

    // A string literal is copied byte for byte. `="A1 is "&A1` shifted a row is
    // `="A1 is "&A2`: the text is a label a human wrote, not an address.
    if (c === '"') {
      let j = i + 1
      while (j < n) {
        if (src[j] === '"') {
          if (src[j + 1] === '"') { j += 2; continue } // "" is an escaped quote
          j++
          break
        }
        j++
      }
      out += src.slice(i, j)
      i = j
      continue
    }

    // `'Q3 pipeline'!A1` — the quoted sheet form. A stray quote that does not
    // qualify a reference is copied like any other character.
    if (c === "'") {
      const q = quoted(i)
      const name = q > 0 ? unquoteSheet(src.slice(i, q)) : ''
      if (q > 0 && src[skipSpace(q)] === '!') {
        onQualifier?.(name, src.slice(skipSpace(skipSpace(q) + 1), word(skipSpace(skipSpace(q) + 1))))
        // The sheet was renamed: only the qualifier changes, and the `!` and
        // whatever follows it are copied by the loop exactly as written.
        if (renameSheet && sameSheet(name, renameSheet.from)) {
          out += quoteSheet(renameSheet.to); i = q; continue
        }
      }
      const hit = q > 0 ? qualified(name, i, q) : null
      if (hit) { out += hit.text; i = hit.end; continue }
      out += c
      i++
      continue
    }

    // `#REF!`, `#DIV/0!`, `#N/A` — an error a previous edit left behind. Copied
    // verbatim, and consumed HERE so the `REF` of `#REF!` cannot be read as a
    // sheet qualifier by the branch below.
    if (c === '#') {
      const m = ERR_LITERAL.exec(src.slice(i))
      if (m) { out += m[0]; i += m[0].length; continue }
    }

    // [Bracketed name] — formula.ts's column escape, and the way to say "the
    // column called REV2024, not the cell".
    if (c === '[') {
      const close = src.indexOf(']', i)
      const end = close < 0 ? n : close + 1
      out += src.slice(i, end)
      i = end
      continue
    }

    // A number, and anything glued to it. Keeps `2024A1` from contributing a
    // reference nobody wrote.
    if (isDigit(c)) {
      let j = i
      while (j < n && isWord(src[j])) j++
      out += src.slice(i, j)
      i = j
      continue
    }

    if (isAlpha(c) || c === '$' || c === '_') {
      const j = word(i)
      const text = src.slice(i, j)
      const k = skipSpace(j)
      // `!` = a sheet qualifier. The name AND the reference after it are one
      // unit — dropping the name here is what made `Sheet1!A1` read the local
      // A1. When what follows is not a reference, the name is left alone.
      if (src[k] === '!') {
        // A rename spells the qualifier anew and leaves the rest to the loop —
        // `Jan!A1` and `Jan!Amount` alike, since both name the sheet.
        if (renameSheet && sameSheet(text, renameSheet.from)) {
          out += quoteSheet(renameSheet.to); i = j; continue
        }
        // WHOEVER IS WATCHING hears about the qualifier here, in BOTH of the
        // branches below, because both are a formula naming another sheet:
        // `Jan!A1` is one dash cannot mistake, and `Jan!Amount` — a sheet and a
        // COLUMN, which is what a dataset author reaches for — is one it would
        // otherwise pass on to formula.ts as the bare word `Jan`. See
        // `sheetQualifiers`.
        const hit = qualified(text, i, j)
        if (hit) {
          onQualifier?.(text, src.slice(skipSpace(k + 1), hit.end))
          out += hit.text; i = hit.end; continue
        }
        // Not a reference after the `!`, so the whole thing is somebody else's
        // syntax. The WORD AFTER IT is consumed here too, verbatim: leaving it
        // to the loop would offer `Sheet1!Total` up as the defined name
        // `Total`, and substituting a range into it would fabricate
        // `Sheet1!A1:A5` — a reference to a sheet the author never named.
        const after = skipSpace(k + 1)
        const w = word(after)
        // A word DID follow, so this is `Jan!Amount` — a sheet and a column
        // name — and not `a ! b`, which is not this language's syntax at all
        // and must not be reported as somebody reaching for another sheet.
        if (w > after) onQualifier?.(text, src.slice(after, w))
        out += src.slice(i, w)
        i = w
        continue
      }
      // `(` = a call, `[` = a structured reference. Both are followed by a
      // name that is frequently cell-shaped (LOG10, Table1); translating one
      // corrupts the formula.
      if (src[k] === '(' || src[k] === '[') {
        out += text
        i = j
        continue
      }
      const r = refAt(i)
      if (!r) { out += mapName ? mapName(text) : text; i = j; continue }
      out += map ? map(r.unit) : src.slice(i, r.end)
      i = r.end
      continue
    }

    out += c
    i++
  }
  return out
}

/**
 * Translate every reference in an expression — what fill-down of a cell formula
 * does. Quoted text, bracketed names, function names and sheet qualifiers are
 * left exactly as written.
 *
 * A range with an off-sheet corner becomes `#REF!` whole. `#REF!:B1` is not a
 * smaller range, it is not a range.
 *
 * A QUALIFIED reference moves like any other: copying `=Sheet1!A1` down a row
 * gives `=Sheet1!A2`, because the formula moved and its reference is relative.
 * The sheet is not what `$` pins — the row is.
 */
export function rewriteFormulaRefs(src: string, dRow: number, dCol: number): string {
  return rewrite(src, (u) => {
    const a = shifted(u.from, dRow, dCol)
    if (!u.to) return a === REF_ERR ? REF_ERR : qualify(u.sheet, a)
    const b = shifted(u.to, dRow, dCol)
    return a === REF_ERR || b === REF_ERR ? REF_ERR : qualify(u.sheet, `${a}:${b}`)
  })
}

/**
 * Move every reference for a structural edit: `count` rows/columns inserted at
 * 0-based index `at`, or REMOVED if `count` is negative.
 *
 * `$` DOES NOT PROTECT A REFERENCE HERE. The cell moved; `$` only ever meant
 * "do not renumber me when I am copied". Excel agrees, and a reader who expects
 * otherwise is expecting their totals to point one row short.
 *
 * A reference INTO a deleted row becomes `#REF!` — never the row that slid into
 * that position, which is the failure this whole file exists to prevent.
 *
 * A RANGE spanning the hole SHRINKS instead: deleting three rows out of the
 * middle of `A1:A10` gives `A1:A7`, because the range still means "the numbers
 * under this heading" and the surviving ones are still there. It becomes
 * `#REF!` only when both ends are gone.
 *
 * A REFERENCE TO ANOTHER SHEET DOES NOT MOVE. `scope` says which sheet the edit
 * happened on (`on`) and which sheet the formula lives on (`self`); a reference
 * moves only when those name the same sheet as the reference does. With no
 * scope the edit is taken to be on the formula's own sheet, which is what every
 * caller meant before sheets could be named at all — so an unqualified
 * reference moves and `Other!A5` stays exactly where it points. Moving it would
 * repoint someone else's data at a hole in this sheet, which is the failure
 * this whole file exists to prevent.
 */
export interface ShiftScope {
  /** the sheet the rows or columns were inserted into or removed from */
  on?: string
  /** the sheet whose formula is being rewritten — what an unqualified ref means */
  self?: string
}

export function shiftRefsForInsert(
  src: string,
  axis: 'row' | 'col',
  at: number,
  count: number,
  scope: ShiftScope = {},
): string {
  const val = (r: CellRef): number => (axis === 'row' ? r.row : r.col)
  const put = (r: CellRef, v: number): CellRef =>
    (axis === 'row' ? { ...r, row: v } : { ...r, col: v })
  const gone = count < 0 ? -count : 0

  /** New index, or `null` if this line was one of the deleted ones. */
  const moved = (v: number): number | null => {
    if (!gone) return v >= at ? v + count : v
    if (v < at) return v
    if (v < at + gone) return null
    return v - gone
  }

  // Whose sheet each side names. `undefined` on both is the ordinary case: one
  // sheet, one edit, no qualifiers anywhere.
  const edited = scope.on ?? scope.self

  return rewrite(src, (u) => {
    if (!sameSheet(u.sheet ?? scope.self, edited)) {
      // Another sheet's cells did not move, so neither does this reference —
      // not even its spelling, or `'Pipeline'!D4` would come back as
      // `Pipeline!D4` and read as a change to whoever diffs the two.
      return u.text
    }
    const a = moved(val(u.from))
    if (!u.to) return a === null ? REF_ERR : qualify(u.sheet, formatRef(put(u.from, a)))
    const b = moved(val(u.to))
    if (a === null && b === null) return REF_ERR
    // One end survived, so the range clamps to the surviving side of the hole:
    // the low end to the first line after it (`at`), the high end to the last
    // line before it (`at - 1`). Clamping both to `at` would extend the range
    // over whatever moved up into the gap.
    const lowFirst = val(u.from) <= val(u.to)
    const fa = a ?? (lowFirst ? at : at - 1)
    const fb = b ?? (lowFirst ? at - 1 : at)
    if (fa < 0 || fb < 0) return REF_ERR
    return qualify(u.sheet, `${formatRef(put(u.from, fa))}:${formatRef(put(u.to, fb))}`)
  })
}