Skip to content

armgit / macro

Public Imported from GitHub
0

Macro is a unified workspace for teams: email, chat, docs, tasks, agents, calls, and CRM — @-linked together with shared AI memory.

31.9 KB
Raw Blame
// @vitest-environment jsdom
import { createMemo, createRoot, createSignal } from 'solid-js';
import { afterEach, describe, expect, it, vi } from 'vitest';
import type { CellCopy } from '../core/calculation';
import {
  GRID_COLUMNS,
  GRID_ROWS,
  parseClipboard,
} from '../core/grid-selection';
import type {
  SpreadsheetCellEdits,
  SpreadsheetCells,
} from '../core/spreadsheet-document';
import { createGridController } from './create-grid-controller';

const cleanups: Array<() => void> = [];
afterEach(() => {
  for (const dispose of cleanups.splice(0)) dispose();
});

function setup(
  initial: SpreadsheetCells = {},
  copyCells?: (copies: CellCopy[]) => Promise<SpreadsheetCellEdits>,
  initialSheetId?: string
) {
  return createRoot((dispose) => {
    cleanups.push(dispose);
    const [cells, updateCells] = createSignal(initial);
    const [canEdit, setCanEdit] = createSignal(true);
    const [rowCount, setRowCount] = createSignal(200);
    const [hiddenRows, setHiddenRows] = createSignal<number[]>([]);
    const [hiddenColumns, setHiddenColumns] = createSignal<number[]>([]);
    const [sheetId, setSheetId] = createSignal(initialSheetId ?? 'sheet1');
    const setCells = vi.fn((edits: SpreadsheetCellEdits) => {
      const next = { ...cells() };
      for (const [address, edit] of Object.entries(edits)) {
        if (edit === null) delete next[address];
        else next[address] = { ...(next[address] ?? { value: '' }), ...edit };
      }
      updateCells(next);
    });
    const publishSelection = vi.fn();
    const undo = vi.fn();
    const redo = vi.fn();
    const grid = createGridController({
      cells,
      hiddenRows,
      hiddenColumns,
      setSelection: publishSelection,
      canEdit,
      setCells,
      undo,
      redo,
      copyCells,
      rowCount,
      sheetId: initialSheetId ? sheetId : undefined,
    });
    function key(value: string, options: KeyboardEventInit = {}) {
      const event = new KeyboardEvent('keydown', {
        key: value,
        cancelable: true,
        ...options,
      });
      grid.keyDown(event);
      return event;
    }
    return {
      grid,
      publishSelection,
      cells,
      setCells,
      setCanEdit,
      setRowCount,
      setHiddenRows,
      setHiddenColumns,
      setSheetId,
      undo,
      redo,
      key,
    };
  });
}

describe('spreadsheet grid editing', () => {
  it('inserts checkboxes, toggles them with Space, and unchecks then removes them with Delete', () => {
    const { grid, key, cells, setCanEdit } = setup({
      A2: { value: 'TRUE' },
    });
    grid.selectRange({ row: 0, column: 0 }, { row: 2, column: 0 });
    grid.insertCheckboxes();
    expect(cells()).toEqual({
      A1: { value: 'FALSE', checkbox: true },
      A2: { value: 'TRUE', checkbox: true },
      A3: { value: 'FALSE', checkbox: true },
    });
    expect(key(' ').defaultPrevented).toBe(true);
    expect(grid.editing()).toBeUndefined();
    expect(['A1', 'A2', 'A3'].map((address) => cells()[address].value)).toEqual(
      ['TRUE', 'TRUE', 'TRUE']
    );
    key(' ');
    expect(['A1', 'A2', 'A3'].map((address) => cells()[address].value)).toEqual(
      ['FALSE', 'FALSE', 'FALSE']
    );

    grid.select({ row: 1, column: 0 });
    key(' ');
    expect(cells().A2.value).toBe('TRUE');
    grid.select({ row: 1, column: 1 });
    key(' ');
    expect(grid.editing()).toBe('cell');
    expect(grid.draft()).toBe(' ');
    grid.cancel();

    grid.selectRange({ row: 0, column: 0 }, { row: 2, column: 0 });
    key('Delete');
    expect(cells().A2).toEqual({ value: 'FALSE', checkbox: true });
    key('Backspace');
    expect(cells().A2).toEqual({ value: '', checkbox: false });

    grid.select({ row: 4, column: 0 });
    grid.insertCheckboxes();
    setCanEdit(false);
    key(' ');
    expect(cells().A5).toEqual({ value: 'FALSE', checkbox: true });
    expect(grid.editing()).toBeUndefined();
  });

  it('keeps Home and typing into a whole-row/column selection out of hidden cells', () => {
    const { grid, key, setHiddenRows, setHiddenColumns, cells } = setup();
    setHiddenRows([0]);
    setHiddenColumns([0]);
    grid.select({ row: 2, column: 4 });
    key('Home');
    expect(grid.activeAddress()).toBe('B3');
    key('Home', { ctrlKey: true });
    expect(grid.activeAddress()).toBe('B2');
    grid.selectRange({ row: 0, column: 0 }, { row: 0, column: 25 });
    key('x');
    grid.commit();
    expect(cells().B2.value).toBe('x');
    expect(cells().A1).toBeUndefined();
    key('ArrowLeft');
    expect(grid.activeAddress()).toBe('B2');
  });
  it('publishes the current range for direct selection, keyboard movement, and tab restoration', async () => {
    const { grid, publishSelection, setSheetId, key } = setup(
      {},
      undefined,
      'sheet1'
    );
    await Promise.resolve();
    grid.selectRange({ row: 3, column: 1 }, { row: 8, column: 4 });
    expect(publishSelection).toHaveBeenLastCalledWith({
      anchor: 'B4',
      focus: 'E9',
    });
    setSheetId('other');
    expect(publishSelection).toHaveBeenLastCalledWith({
      anchor: 'A1',
      focus: 'A1',
    });
    key('ArrowDown');
    expect(publishSelection).toHaveBeenLastCalledWith({
      anchor: 'A2',
      focus: 'A2',
    });
    setSheetId('sheet1');
    expect(publishSelection).toHaveBeenLastCalledWith({
      anchor: 'B4',
      focus: 'E9',
    });
  });

  it.each(['cell', 'formula'] as const)(
    'picks a live range in the %s editor without writing or moving the active cell',
    (location) => {
      const { grid, cells, setCells } = setup();
      grid.select({ row: 9, column: 5 });
      grid.beginEdit(location, '=SUM(');
      expect(grid.beginReference({ row: 3, column: 1 })).toBe(true);
      expect(grid.draft()).toBe('=SUM(B4');
      grid.updateReference({ row: 6, column: 2 });
      expect(grid.draft()).toBe('=SUM(B4:C7');
      expect(grid.activeAddress()).toBe('F10');
      expect(grid.editing()).toBe(location);
      expect(grid.editorSelection()).toEqual({ start: 10, end: 10 });
      expect(setCells).not.toHaveBeenCalled();
      grid.endReference();
      expect(grid.pickingReference()).toBe(false);
      grid.setDraft(grid.draft() + ')');
      grid.commit();
      expect(cells().F10.value).toBe('=SUM(B4:C7)');
      expect(setCells).toHaveBeenCalledOnce();
      expect(grid.referenceSelection()).toBeUndefined();
    }
  );

  it('replaces a picked range, inserts subsequent arguments, and cancels without saving', () => {
    const { grid, setCells } = setup();
    grid.beginEdit('cell', '=SUM(');
    grid.beginReference({ row: 7, column: 3 });
    grid.updateReference({ row: 3, column: 1 });
    grid.endReference();
    expect(grid.draft()).toBe('=SUM(B4:D8');
    grid.beginReference({ row: 0, column: 0 });
    grid.endReference();
    expect(grid.draft()).toBe('=SUM(A1');
    grid.setDraft(grid.draft() + ',');
    grid.beginReference({ row: 2, column: 2 });
    grid.endReference();
    expect(grid.draft()).toBe('=SUM(A1,C3');
    grid.cancel();
    expect(setCells).not.toHaveBeenCalled();
    expect(grid.referenceSelection()).toBeUndefined();
  });

  it('inserts at the caret and honors permissions', () => {
    const { grid, setCanEdit } = setup();
    grid.beginEdit('formula', '=SUM(,C1)');
    grid.setTextSelection(5, 5);
    grid.beginReference({ row: 1, column: 1 });
    expect(grid.draft()).toBe('=SUM(B2,C1)');
    setCanEdit(false);
    grid.updateReference({ row: 2, column: 2 });
    expect(grid.draft()).toBe('=SUM(B2,C1)');
    grid.endReference();
    expect(grid.beginReference({ row: 4, column: 4 })).toBe(false);
  });

  it('starts typing into a draft and commits to the original cell before moving', () => {
    const { grid, cells, setCells, key } = setup({ A1: { value: 'before' } });
    expect(key('n').defaultPrevented).toBe(true);
    expect(grid.editing()).toBe('cell');
    expect(grid.draft()).toBe('n');
    expect(setCells).not.toHaveBeenCalled();
    grid.setDraft('new value');
    grid.select({ row: 1, column: 1 });
    expect(cells().A1.value).toBe('new value');
    expect(cells().B2).toBeUndefined();
    expect(grid.editing()).toBeUndefined();
    expect(grid.activeAddress()).toBe('B2');
  });

  it('cancels an edit with Escape and avoids committing unchanged input', () => {
    const { grid, setCells, key } = setup({ A1: { value: '=SUM(B1:B3)' } });
    key('F2');
    expect(grid.draft()).toBe('=SUM(B1:B3)');
    grid.commit();
    expect(setCells).not.toHaveBeenCalled();
    grid.beginEdit('formula');
    grid.setDraft('discard this');
    key('Escape');
    expect(grid.editing()).toBeUndefined();
    expect(grid.draft()).toBe('');
    expect(setCells).not.toHaveBeenCalled();
  });

  it('ignores IME composition keystrokes', () => {
    const { grid, key } = setup();
    const event = key('a', { isComposing: true });
    expect(event.defaultPrevented).toBe(false);
    expect(grid.editing()).toBeUndefined();
  });

  it('clears selected contents while preserving formatting', () => {
    const { grid, cells, setCells, key } = setup({
      A1: { value: '5', bold: true, format: 'currency' },
      B1: { value: '10' },
    });
    grid.selectRange({ row: 0, column: 0 }, { row: 0, column: 1 });
    key('Delete');
    expect(setCells).toHaveBeenCalledTimes(1);
    expect(cells().A1).toEqual({ value: '', bold: true, format: 'currency' });
    expect(cells().B1.value).toBe('');
  });
});

describe('sheet boundaries', () => {
  it('cancels a formula draft and reference picking when the active sheet changes', () => {
    const { grid, setSheetId, setCells } = setup({}, undefined, 'sheet1');
    grid.select({ row: 4, column: 3 });
    grid.beginEdit('formula', '=SUM(');
    grid.beginReference({ row: 2, column: 1 });
    expect(grid.pickingReference()).toBe(true);

    // This is also the path used when a collaborator deletes the active sheet.
    setSheetId('replacement');
    expect(grid.editing()).toBeUndefined();
    expect(grid.draft()).toBe('');
    expect(grid.pickingReference()).toBe(false);
    expect(grid.referenceSelection()).toBeUndefined();
    expect(grid.editorSelection()).toBeUndefined();
    expect(grid.activeAddress()).toBe('A1');
    grid.commit();
    expect(setCells).not.toHaveBeenCalled();
  });

  it('remembers independent selections by stable sheet ID and bounds restored rows', () => {
    const { grid, setSheetId, setRowCount } = setup({}, undefined, 'sheet1');
    grid.selectRange({ row: 4, column: 2 }, { row: 8, column: 5 });
    setSheetId('second');
    expect(grid.activeAddress()).toBe('A1');
    setRowCount(300);
    grid.selectRange({ row: 290, column: 1 }, { row: 299, column: 3 });
    setSheetId('sheet1');
    setRowCount(200);
    expect(grid.selection()).toEqual({
      anchor: { row: 4, column: 2 },
      focus: { row: 8, column: 5 },
    });
    setSheetId('second');
    expect(grid.selection()).toEqual({
      anchor: { row: 199, column: 1 },
      focus: { row: 199, column: 3 },
    });
  });

  it.each([false, true])(
    'invalidates a pending fill across a sheet switch (return=%s)',
    async (returnToOriginal) => {
      let finish!: (edits: SpreadsheetCellEdits) => void;
      const pending = new Promise<SpreadsheetCellEdits>((resolve) => {
        finish = resolve;
      });
      const { grid, setSheetId, setCells } = setup(
        { A1: { value: '=B1' } },
        () => pending,
        'sheet1'
      );
      grid.selectRange({ row: 0, column: 0 }, { row: 1, column: 0 });
      grid.fillDirection('down');
      setSheetId('second');
      if (returnToOriginal) setSheetId('sheet1');
      finish({ A2: { value: '=B2' } });
      await Promise.resolve();
      expect(setCells).not.toHaveBeenCalled();
      expect(grid.notice()).toBe('');
    }
  );
});

describe('spreadsheet keyboard selection', () => {
  it('moves and extends selection within the grid boundaries', () => {
    const { grid, key } = setup();
    key('ArrowUp');
    key('ArrowLeft');
    expect(grid.activeAddress()).toBe('A1');
    key('Tab');
    key('ArrowDown');
    expect(grid.activeAddress()).toBe('B2');
    key('ArrowDown', { shiftKey: true });
    key('ArrowRight', { shiftKey: true });
    expect(grid.selection()).toEqual({
      anchor: { row: 1, column: 1 },
      focus: { row: 2, column: 2 },
    });
    key('Home', { ctrlKey: true });
    expect(grid.activeAddress()).toBe('A1');
    grid.select({ row: GRID_ROWS + 4, column: GRID_COLUMNS + 4 });
    key('ArrowRight');
    key('ArrowDown');
    expect(grid.activeAddress()).toBe('Z200');
  });

  it('selects the whole sheet and bounds programmatic range selections', () => {
    const { grid, key } = setup();
    key('a', { metaKey: true });
    expect(grid.selection()).toEqual({
      anchor: { row: 0, column: 0 },
      focus: { row: GRID_ROWS - 1, column: GRID_COLUMNS - 1 },
    });
    grid.selectRange(
      { row: -20, column: -1 },
      { row: GRID_ROWS, column: GRID_COLUMNS }
    );
    expect(grid.selection()).toEqual({
      anchor: { row: 0, column: 0 },
      focus: { row: GRID_ROWS - 1, column: GRID_COLUMNS - 1 },
    });
  });

  it('routes undo and redo through the supplied document history', () => {
    const { undo, redo, key } = setup();
    key('z', { ctrlKey: true });
    key('Z', { metaKey: true, shiftKey: true });
    expect(undo).toHaveBeenCalledTimes(1);
    expect(redo).toHaveBeenCalledTimes(1);
  });
});

describe('spreadsheet clipboard and formatting', () => {
  it('pastes a rectangular operation atomically and preserves destination styles', () => {
    const { grid, cells, setCells } = setup({
      B2: { value: 'old', bold: true },
      C3: { value: 'clear me', format: 'currency' },
    });
    grid.select({ row: 1, column: 1 });
    grid.paste('one\t"two\nlines"\nthree');
    expect(setCells).toHaveBeenCalledTimes(1);
    expect(cells()).toEqual({
      B2: { value: 'one', bold: true },
      C2: { value: 'two\nlines' },
      B3: { value: 'three' },
      C3: { value: '', format: 'currency' },
    });
    expect(grid.selection().focus).toEqual({ row: 2, column: 2 });
    expect(grid.notice()).toBe('Pasted 4 cells');
  });

  it('rejects overflow and invalid paste before any write', () => {
    const { grid, setCells } = setup();
    grid.select({ row: GRID_ROWS - 1, column: GRID_COLUMNS - 1 });
    grid.paste('first\tsecond');
    expect(grid.notice()).toContain('200 rows and 26 columns');
    grid.select({ row: 0, column: 0 });
    grid.paste(`valid\t${'x'.repeat(10_001)}`);
    expect(grid.notice()).toContain('10,000 characters');
    grid.paste('"unclosed');
    expect(grid.notice()).toContain('unclosed');
    grid.paste('x'.repeat(1_000_001));
    expect(grid.notice()).toContain('1 MB');
    expect(setCells).not.toHaveBeenCalled();
  });

  it('copies raw formulas and quoted multiline cells in visual row order', () => {
    const { grid } = setup({
      A1: { value: '=B2*2' },
      B1: { value: 'line\nbreak' },
      A2: { value: 'a\tb' },
      B2: { value: 'he said "yes"' },
    });
    grid.selectRange({ row: 1, column: 1 }, { row: 0, column: 0 });
    expect(parseClipboard(grid.copy())).toEqual([
      ['=B2*2', 'line\nbreak'],
      ['a\tb', 'he said "yes"'],
    ]);
  });

  it('applies formatting to a selection without overwriting values', () => {
    const { grid, cells, setCells, key } = setup({
      A1: { value: '0.5' },
      B1: { value: '=A1*2' },
    });
    grid.selectRange({ row: 0, column: 0 }, { row: 0, column: 1 });
    grid.format({ format: 'percent' });
    expect(setCells).toHaveBeenCalledTimes(1);
    key('b', { ctrlKey: true });
    expect(cells()).toEqual({
      A1: { value: '0.5', format: 'percent', bold: true },
      B1: { value: '=A1*2', format: 'percent', bold: true },
    });
  });

  it('formats a captured range after the selection has moved', () => {
    const { grid, cells } = setup({
      A1: { value: '1' },
      A2: { value: '2' },
      C3: { value: '3' },
    });
    grid.selectRange({ row: 0, column: 0 }, { row: 1, column: 0 });
    const captured = grid.selection();
    grid.selectRange({ row: 2, column: 2 }, { row: 2, column: 2 });
    grid.format({ format: 'date', numberFormat: 'yyyy-mm-dd' }, captured);
    expect(cells()).toEqual({
      A1: { value: '1', format: 'date', numberFormat: 'yyyy-mm-dd' },
      A2: { value: '2', format: 'date', numberFormat: 'yyyy-mm-dd' },
      C3: { value: '3' },
    });
  });
});

describe('spreadsheet read-only permissions', () => {
  it('blocks every write and history action but permits navigation and copy', () => {
    const { grid, setCells, setCanEdit, undo, redo, key } = setup({
      A1: { value: 'read me' },
    });
    setCanEdit(false);
    grid.beginEdit('cell');
    grid.beginEdit('formula');
    grid.paste('replaced');
    grid.format({ bold: true });
    grid.clear();
    key('x');
    key('Delete');
    key('z', { metaKey: true });
    key('Z', { metaKey: true, shiftKey: true });
    expect(grid.editing()).toBeUndefined();
    expect(grid.copy()).toBe('read me');
    key('ArrowRight');
    expect(grid.activeAddress()).toBe('B1');
    expect(setCells).not.toHaveBeenCalled();
    expect(undo).not.toHaveBeenCalled();
    expect(redo).not.toHaveBeenCalled();
  });

  it('rechecks permission when an already-open editor commits', () => {
    const { grid, setCanEdit, setCells } = setup();
    grid.beginEdit('cell', 'draft');
    setCanEdit(false);
    grid.commit();
    expect(setCells).not.toHaveBeenCalled();
    expect(grid.editing()).toBeUndefined();
  });
});

describe('formula-aware copy coordination', () => {
  it.each([
    ['undo', 'keyboard'],
    ['redo', 'keyboard'],
    ['undo', 'command'],
    ['redo', 'command'],
  ] as const)(
    'cancels a delayed fill before %s through a %s action',
    async (action, entryPoint) => {
      let finish!: (edits: SpreadsheetCellEdits) => void;
      const pending = new Promise<SpreadsheetCellEdits>((resolve) => {
        finish = resolve;
      });
      const view = setup({ A1: { value: '=B1' } }, () => pending);
      view[action].mockImplementation(() => {
        view.setCells({ A1: { value: 'history result' } });
      });
      view.grid.selectRange({ row: 0, column: 0 }, { row: 1, column: 0 });
      view.grid.fillDirection('down');
      if (entryPoint === 'keyboard')
        view.key('z', { ctrlKey: true, shiftKey: action === 'redo' });
      else view.grid[action]();
      const writes = view.setCells.mock.calls.length;
      finish({ A2: { value: '=B2' } });
      await Promise.resolve();
      expect(view[action]).toHaveBeenCalledOnce();
      expect(view.setCells).toHaveBeenCalledTimes(writes);
      expect(view.cells().A1.value).toBe('history result');
      expect(view.cells().A2).toBeUndefined();
      expect(view.grid.notice()).toBe('');
    }
  );

  it.each(['clear', 'format', 'commit'] as const)(
    'cancels a delayed fill after %s even when destination content is unchanged',
    async (action) => {
      let finish!: (edits: SpreadsheetCellEdits) => void;
      const pending = new Promise<SpreadsheetCellEdits>((resolve) => {
        finish = resolve;
      });
      const view = setup(
        {
          A1: { value: '=B1', bold: false },
          A2: { value: '', bold: false },
        },
        () => pending
      );
      view.grid.selectRange({ row: 0, column: 0 }, { row: 1, column: 0 });
      view.grid.fillDirection('down');
      if (action === 'clear') view.grid.clear();
      else if (action === 'format') view.grid.format({ bold: false });
      else view.grid.commit();
      expect(view.cells().A2).toEqual({ value: '', bold: false });
      const writes = view.setCells.mock.calls.length;
      finish({ A2: { value: '=B2' } });
      await Promise.resolve();
      expect(view.setCells).toHaveBeenCalledTimes(writes);
      expect(view.cells().A2.value).toBe('');
      expect(view.grid.notice()).toBe('');
    }
  );

  it.each([false, true])(
    'cancels a fill beyond removed rows with an in-bounds selection (regrow=%s)',
    async (regrow) => {
      let finish!: (edits: SpreadsheetCellEdits) => void;
      const pending = new Promise<SpreadsheetCellEdits>((resolve) => {
        finish = resolve;
      });
      const view = setup({ A1: { value: '=B1' } }, () => pending);
      view.setRowCount(300);
      const filling = view.grid.fill(
        { anchor: { row: 0, column: 0 }, focus: { row: 0, column: 0 } },
        { anchor: { row: 0, column: 0 }, focus: { row: 299, column: 0 } }
      );
      view.setRowCount(200);
      if (regrow) view.setRowCount(300);
      finish({ A300: { value: '=B300' } });
      await Promise.resolve();
      expect(view.setCells).not.toHaveBeenCalled();
      expect(view.cells().A300).toBeUndefined();
      expect(view.grid.selection()).toEqual({
        anchor: { row: 0, column: 0 },
        focus: { row: 0, column: 0 },
      });
      expect(view.grid.notice()).toBe('');
      await filling;
    }
  );

  it('pastes internal metadata with source coordinates in one atomic write', async () => {
    const copy = vi.fn(async (_copies: CellCopy[]) => ({
      B2: { value: '=C3', bold: true },
    }));
    const { grid, cells, setCells } = setup(
      { A1: { value: '=B2', bold: true } },
      copy
    );
    const metadata = grid.copyMetadata();
    grid.select({ row: 1, column: 1 });
    grid.paste('=B2', metadata);
    await Promise.resolve();
    expect(copy.mock.calls[0][0]).toEqual([
      {
        from: { row: 0, column: 0 },
        to: { row: 1, column: 1 },
        cell: { value: '=B2', bold: true },
      },
    ]);
    expect(setCells).toHaveBeenCalledTimes(1);
    expect(cells().B2.value).toBe('=C3');
  });

  it('fills from the top row or left column using keyboard commands', async () => {
    const copy = vi.fn(async (_copies: CellCopy[]) => ({}));
    const { grid, key } = setup({ A1: { value: '=B1' } }, copy);
    grid.selectRange({ row: 0, column: 0 }, { row: 2, column: 0 });
    expect(key('d', { metaKey: true }).defaultPrevented).toBe(true);
    expect(copy.mock.calls[0][0].map((item) => item.to)).toEqual([
      { row: 1, column: 0 },
      { row: 2, column: 0 },
    ]);
    await Promise.resolve();
    grid.selectRange({ row: 0, column: 0 }, { row: 0, column: 2 });
    expect(key('r', { ctrlKey: true }).defaultPrevented).toBe(true);
    expect(copy.mock.calls[1][0].map((item) => item.to)).toEqual([
      { row: 0, column: 1 },
      { row: 0, column: 2 },
    ]);
    await Promise.resolve();
  });

  it.each(['selection', 'edit', 'permission', 'remote'] as const)(
    'does not apply a delayed fill after a %s change',
    async (change) => {
      let finish!: (edits: SpreadsheetCellEdits) => void;
      const pending = new Promise<SpreadsheetCellEdits>((resolve) => {
        finish = resolve;
      });
      const { grid, cells, setCanEdit, setCells } = setup(
        { A1: { value: '=B1' } },
        () => pending
      );
      grid.selectRange({ row: 0, column: 0 }, { row: 1, column: 0 });
      grid.fillDirection('down');
      if (change === 'selection') grid.select({ row: 2, column: 0 });
      if (change === 'edit') grid.beginEdit('cell', 'new draft');
      if (change === 'permission') setCanEdit(false);
      if (change === 'remote') setCells({ A2: { value: 'collaborator' } });
      finish({ A2: { value: '=B2' } });
      await Promise.resolve();
      expect(cells().A2?.value).toBe(
        change === 'remote' ? 'collaborator' : undefined
      );
    }
  );
});

it('bounds navigation to added rows and cancels a draft when empty rows are removed', async () => {
  const { grid, setRowCount, setCells, key } = setup();
  setRowCount(300);
  grid.select({ row: 299, column: 25 });
  key('ArrowDown');
  expect(grid.activeAddress()).toBe('Z300');
  grid.beginEdit('cell', 'uncommitted draft');
  setRowCount(200);
  await Promise.resolve();
  expect(grid.activeAddress()).toBe('Z200');
  expect(grid.editing()).toBeUndefined();
  grid.commit();
  expect(setCells).not.toHaveBeenCalled();
});

function crossSheetSetup() {
  return createRoot((dispose) => {
    cleanups.push(dispose);
    const [sheetId, setSheetId] = createSignal('summary');
    const [sheets, setSheets] = createSignal([
      { id: 'summary', name: 'Summary' },
      { id: 'inputs', name: "Owner's budget" },
    ]);
    const [books, setBooks] = createSignal<Record<string, SpreadsheetCells>>({
      summary: { B4: { value: 'before' } },
      inputs: {
        A1: { value: '10' },
        A2: { value: '20' },
        B4: { value: 'leave me' },
      },
    });
    const [canEdit, setCanEdit] = createSignal(true);
    // Subscribing before the controller makes row-count reactions run before
    // its tab-change handling, an order the store's subscriptions can produce.
    const rowCount = createMemo(() => (sheetId() === 'summary' ? 300 : 200));
    const setSheetCells = vi.fn((id: string, edits: SpreadsheetCellEdits) => {
      if (!sheets().some((sheet) => sheet.id === id)) return;
      const cells = { ...books()[id] };
      for (const [address, value] of Object.entries(edits)) {
        if (value) cells[address] = { ...cells[address], ...value };
      }
      setBooks({ ...books(), [id]: cells });
    });
    const grid = createGridController({
      cells: () => books()[sheetId()],
      sheetId,
      sheets,
      setActiveSheet: setSheetId,
      setSheetCells,
      setCells: (edits) => setSheetCells(sheetId(), edits),
      canEdit,
      rowCount,
      undo: () => {},
      redo: () => {},
    });
    return {
      grid,
      books,
      sheetId,
      setSheetId,
      setSheets,
      setCanEdit,
      setSheetCells,
    };
  });
}

describe('cross-sheet formula editing', () => {
  it('keeps the draft while changing tabs and commits the escaped range to its original sheet', () => {
    const view = crossSheetSetup();
    view.grid.select({ row: 3, column: 1 });
    view.grid.beginEdit('cell', '=SUM(');
    view.grid.switchSheet('inputs');
    expect(view.sheetId()).toBe('inputs');
    expect(view.grid.editing()).toBe('formula');
    expect(view.grid.editingAddress()).toBe('B4');
    expect(view.grid.isEditingActiveSheet()).toBe(false);
    expect(view.grid.beginReference({ row: 0, column: 0 })).toBe(true);
    view.grid.updateReference({ row: 1, column: 0 });
    view.grid.endReference();
    expect(view.grid.draft()).toBe("=SUM('Owner''s budget'!A1:A2");
    view.grid.beginEdit('formula');
    expect(view.grid.draft()).toBe("=SUM('Owner''s budget'!A1:A2");
    view.grid.setDraft(view.grid.draft() + ')');
    view.grid.commit();
    expect(view.books().summary.B4.value).toBe("=SUM('Owner''s budget'!A1:A2)");
    expect(view.books().inputs.B4.value).toBe('leave me');
    expect(view.sheetId()).toBe('summary');
    expect(view.grid.activeAddress()).toBe('B4');
  });

  it('keeps a draft from a row the picked sheet does not have and returns to it on commit', () => {
    const view = crossSheetSetup();
    view.grid.select({ row: 249, column: 2 });
    view.grid.beginEdit('cell', '=IF(D2 <');
    view.grid.switchSheet('inputs');
    expect(view.sheetId()).toBe('inputs');
    expect(view.grid.editing()).toBe('formula');
    expect(view.grid.editingAddress()).toBe('C250');
    expect(view.grid.beginReference({ row: 3, column: 1 })).toBe(true);
    view.grid.endReference();
    expect(view.grid.draft()).toBe("=IF(D2 <'Owner''s budget'!B4");
    view.grid.commit();
    expect(view.books().summary.C250.value).toBe(
      "=IF(D2 <'Owner''s budget'!B4"
    );
    expect(view.sheetId()).toBe('summary');
    expect(view.grid.activeAddress()).toBe('C250');
  });

  it('replaces a prior reference when choosing another sheet and omits the qualifier on the origin', () => {
    const view = crossSheetSetup();
    view.grid.beginEdit('formula', '=');
    view.grid.switchSheet('inputs');
    view.grid.beginReference({ row: 0, column: 0 });
    view.grid.endReference();
    view.grid.switchSheet('summary');
    expect(view.grid.referenceSelection()).toBeUndefined();
    view.grid.beginReference({ row: 0, column: 1 });
    view.grid.endReference();
    expect(view.grid.draft()).toBe('=B1');
    view.grid.cancel();
    expect(view.setSheetCells).not.toHaveBeenCalled();
  });

  it('cancels if the origin is removed, restores the origin on Escape, and rejects revoked permission', () => {
    const view = crossSheetSetup();
    view.grid.beginEdit('cell', '=SUM(');
    view.grid.switchSheet('inputs');
    view.grid.cancel();
    expect(view.sheetId()).toBe('summary');
    view.grid.beginEdit('cell', '=');
    view.grid.switchSheet('inputs');
    view.grid.beginReference({ row: 0, column: 0 });
    view.grid.endReference();
    view.setCanEdit(false);
    view.grid.commit();
    expect(view.setSheetCells).not.toHaveBeenCalled();
    view.setCanEdit(true);
    view.grid.beginEdit('cell', '=');
    view.grid.switchSheet('inputs');
    view.setSheets([{ id: 'inputs', name: 'Inputs' }]);
    expect(view.grid.editing()).toBeUndefined();
    view.grid.commit();
    expect(view.setSheetCells).not.toHaveBeenCalled();
  });

  it('commits non-formulas during normal tab navigation and cancels on unrequested sheet changes', () => {
    const view = crossSheetSetup();
    view.grid.beginEdit('cell', 'New value');
    view.grid.switchSheet('inputs');
    expect(view.books().summary.A1.value).toBe('New value');
    view.grid.beginEdit('cell', '=SUM(');
    view.setSheetId('summary');
    expect(view.grid.editing()).toBeUndefined();
    expect(view.books().inputs.A1.value).toBe('10');
  });
});

it('fills literal series immediately and keeps keyboard fill-down as a copy', async () => {
  const view = setup({ A1: { value: '1' }, A2: { value: '2' } });
  const source = {
    anchor: { row: 0, column: 0 },
    focus: { row: 1, column: 0 },
  };
  const target = {
    anchor: { row: 0, column: 0 },
    focus: { row: 3, column: 0 },
  };
  const done = view.grid.fill(source, target);
  expect(view.cells().A3.value).toBe('3');
  expect(view.cells().A4.value).toBe('4');
  await done;
  view.grid.selectRange({ row: 0, column: 0 }, { row: 3, column: 0 });
  view.key('d', { ctrlKey: true });
  expect(view.cells().A4.value).toBe('1');
});

it('interprets newly typed percentage points without rescaling unchanged stored percentages', () => {
  const view = setup({ A1: { value: '0.05', format: 'percent' } });
  view.grid.beginEdit('cell');
  expect(view.grid.draft()).toBe('5%');
  view.grid.commit();
  expect(view.setCells).not.toHaveBeenCalled();
  view.key('7');
  view.grid.commit();
  expect(view.cells().A1.value).toBe('7%');
  view.grid.beginEdit('formula', '=5/100');
  view.grid.commit();
  expect(view.cells().A1.value).toBe('=5/100');
});

it('moves from the active cell after whole-row or whole-column selections, not the far edge', () => {
  const { grid, key } = setup();
  grid.selectRange({ row: 0, column: 2 }, { row: 199, column: 2 });
  key('ArrowDown');
  expect(grid.activeAddress()).toBe('C2');
  grid.selectRange({ row: 4, column: 0 }, { row: 4, column: 25 });
  key('ArrowRight');
  expect(grid.activeAddress()).toBe('B5');
});

it('highlights the draft references that fall on the sheet being shown', () => {
  const view = createRoot((dispose) => {
    cleanups.push(dispose);
    const [sheetId, setSheetId] = createSignal('sheet1');
    const grid = createGridController({
      cells: () => ({}),
      sheetId,
      sheets: () => [
        { id: 'sheet1', name: 'Sheet1' },
        { id: 'sheet2', name: "Owner's budget" },
      ],
      setActiveSheet: setSheetId,
      setSheetCells: vi.fn(),
      canEdit: () => true,
      rowCount: () => 20,
      columnCount: () => 5,
      setCells: vi.fn(),
      undo: vi.fn(),
      redo: vi.fn(),
    });
    return { grid, setSheetId };
  });
  const { grid } = view;
  expect(grid.referenceHighlights()).toEqual([]);

  grid.beginEdit('cell', "=SUM(A1:B2)+'Owner''s budget'!C3+D:D+$a$1:b2");
  const [first, other, column, repeat] = grid.draftReferences();
  expect(repeat.color).toBe(first.color);
  expect(other.color).not.toBe(first.color);
  expect(grid.referenceHighlights()).toEqual([
    { color: first.color, bounds: { top: 0, bottom: 1, left: 0, right: 1 } },
    { color: column.color, bounds: { top: 0, bottom: 19, left: 3, right: 3 } },
    { color: first.color, bounds: { top: 0, bottom: 1, left: 0, right: 1 } },
  ]);

  grid.switchSheet('sheet2');
  expect(grid.referenceHighlights()).toEqual([
    { color: other.color, bounds: { top: 2, bottom: 2, left: 2, right: 2 } },
  ]);

  grid.cancel();
  expect(grid.draftReferences()).toEqual([]);
  expect(grid.referenceHighlights()).toEqual([]);
});