Skip to content

The OSINT investigator's workbench. One case, one folder, every tool. Local-first: your media and your investigations never leave your machine.

JavaScript
42.9 KB
Raw Blame
import { describe, expect, it } from 'vitest';
import {
  compareSortKeys,
  sortKey,
  sortsByRole,
  BOOLEAN_DEFAULTS,
  COMPUTED_NATURES,
  COUNTING_NATURES,
  LINKED_NATURES,
  ROW_COLOURS,
  DEFAULT_SEPARATOR,
  ROLE_KINDS,
  STATE_COLOURS,
  STATE_DEFAULTS,
  cellChips,
  countedOf,
  columnProgress,
  cycleTick,
  dateSpelling,
  detectRole,
  distanceMetres,
  duplicateGroups,
  editVocabulary,
  flipBoolean,
  formatLatLon,
  formatOffset,
  nearbyPairs,
  normalizeRole,
  numberTotals,
  offsetMoment,
  parseLatLon,
  parseNumber,
  parseOffset,
  picturePath,
  pictureRef,
  pictureUrl,
  parseWhen,
  precisionMetres,
  readable,
  readsCell,
  pickerType,
  sortVocabulary,
  spellWhen,
  splitValues,
  suggestProgressColumn,
  tickState,
  valueTotals,
  vocabularyUse,
  whenShape,
} from './sheetRoles.js';
import shared from './sheetReading.fixture.json';

/** Two cells ordered by what the column knows, the way `visibleRows` ends up ordering
 *  them once it has read a key per row. Written here rather than shipped: the grid never
 *  compares two raw cells, so a module-level version of this would be code the app does
 *  not call. */
const compareByRole = (role, a, b) => compareSortKeys(sortKey(role, a), sortKey(role, b));

describe('the role record', () => {
  it('refuses a kind it does not implement rather than storing it', () => {
    expect(normalizeRole({ kind: 'invented' })).toBeNull();
    expect(normalizeRole(null)).toBeNull();
    expect(ROLE_KINDS).toEqual([
      'state',
      'choice',
      'boolean',
      'number',
      'latlon',
      'when',
      'picture',
      'url',
      'row',
      'offset',
      'stamped',
      'computed',
      'locked',
    ]);
  });

  it('reads a row column as the words that name the other rows', () => {
    // Names and not keys: a file whose links read `r7f3a` is a file the collaborator
    // opening it cannot follow, and names are what the binders actually wrote.
    expect(normalizeRole({ kind: 'row' })).toEqual({ kind: 'row', of: null, multi: null });
    const role = normalizeRole({ kind: 'row', of: 'Unit', multi: ', ' });
    expect(splitValues('1st Coy, 2nd Coy', role)).toEqual(['1st Coy', '2nd Coy']);
    // Whether a name finds its row is a question about the whole table, so this reader
    // never calls a cell unreadable on its own.
    expect(readsCell(role, 'a unit nobody listed')).toBe(true);
  });

  it('reads an offset as seconds either side of its anchor', () => {
    const role = normalizeRole({ kind: 'offset', anchor: '  IGLA launch  ' });
    expect(role).toEqual({ kind: 'offset', anchor: 'IGLA launch' });
    expect(parseOffset('-00:01:50')).toBe(-110);
    expect(parseOffset('00:04:04')).toBe(244);
    expect(parseOffset('1:05')).toBe(65);
    expect(parseOffset('-110')).toBe(-110);
    expect(parseOffset('1:05:00')).toBe(3900);
    // Sixty-one seconds is not a time somebody meant to write.
    expect(parseOffset('00:00:61')).toBeNull();
    expect(parseOffset('then')).toBeNull();
    expect(formatOffset(-110)).toBe('-00:01:50');
    // The relative order is usable before anybody has dated the shot, which is the whole
    // reason the column exists.
    expect(compareByRole(role, '-00:01:50', '00:04:04')).toBe(-1);
    expect(offsetMoment('2026-01-03T01:57:00Z', -110)).toBe('2026-01-03T01:55:10Z');
    expect(offsetMoment('', -110)).toBeNull();
  });

  it('reads a picture column as the address its cells hold', () => {
    const role = normalizeRole({ kind: 'picture' });
    expect(role).toEqual({ kind: 'picture' });
    expect(pictureUrl('https://example.org/a.jpg')).toBe('https://example.org/a.jpg');
    expect(pictureUrl('shot at https://example.org/a.jpg.')).toBe('https://example.org/a.jpg');
    // A path off somebody else's disk is elsewhere, not wrong: nothing is drawn for it.
    expect(pictureUrl('C:\\photos\\a.jpg')).toBeNull();
    expect(readsCell(role, 'to be found')).toBe(false);
    expect(readsCell(role, '')).toBe(true);
  });

  it('reads a picture column as a file this case holds, as well as an address', () => {
    // A geolocation index is worked on the images the case already holds, and a column
    // that only understood `http` could draw a stranger's photo and not the case's own.
    expect(picturePath('media/quai-sud.jpg')).toBe('media/quai-sud.jpg');
    expect(pictureRef('media/quai-sud.jpg')).toEqual({ kind: 'case', value: 'media/quai-sud.jpg' });
    expect(pictureRef('https://a.org/1.jpg')).toEqual({
      kind: 'url',
      value: 'https://a.org/1.jpg',
    });
    expect(pictureRef('to be found')).toBeNull();
  });

  it('refuses a path that names a disk this case does not travel with', () => {
    // The cell is text a collaborator reads in a spreadsheet, and `media/quai-sud.jpg` is
    // the only spelling that means the same thing to them as to the app.
    expect(picturePath('/home/someone/a.jpg')).toBeNull();
    expect(picturePath('C:\\photos\\a.jpg')).toBeNull();
    expect(picturePath('media/../../etc/passwd.jpg')).toBeNull();
    expect(picturePath('media/notes.txt')).toBeNull();
    expect(picturePath('')).toBeNull();
  });

  it('says how many columns a counting column counts, for the heading to state once', () => {
    // The denominator is said in the heading and the panel; the cells hold the bare
    // number, because repeating it down four hundred rows is four hundred copies of one
    // fact in cells thirty pixels tall.
    expect(countedOf({ kind: 'computed', of: 'filled_of', columns: ['a', 'b'] })).toBe(2);
    expect(countedOf({ kind: 'computed', of: 'has_point' })).toBe(0);
    expect(countedOf(null)).toBe(0);
  });

  it('suggests a picture off the extension, never off "it is a link"', () => {
    expect(detectRole(['https://a.org/1.jpg', 'https://a.org/2.png'])).toBe('picture');
    expect(detectRole(['https://t.me/c/1', 'https://t.me/c/2'])).not.toBe('picture');
  });

  it('gives a state column the default vocabulary when it has none', () => {
    expect(normalizeRole({ kind: 'state' }).values).toEqual(STATE_DEFAULTS);
  });

  it('paints those four defaults, because a worklist is read at a glance', () => {
    const role = normalizeRole({ kind: 'state' });
    expect(role.colours).toEqual({
      'to do': 'grey',
      'in progress': 'blue',
      'done': 'green',
      'ruled out': 'red',
    });
    expect(STATE_COLOURS).toEqual(role.colours);
    // Every one of them from the palette a row is painted with, so a chip and a row read
    // in the same six colours.
    for (const colour of Object.values(STATE_COLOURS)) expect(ROW_COLOURS).toContain(colour);
  });

  it('lets the column change one of those colours and remove another', () => {
    // The defaults sit under what the role says, value by value. Removing a `#colour`
    // from a line has to stick, or the analyst could never un-paint a value.
    const role = normalizeRole({
      kind: 'state',
      values: [...STATE_DEFAULTS],
      colours: { 'to do': 'orange', 'in progress': 'blue' },
    });
    expect(role.colours).toEqual({ 'to do': 'orange', 'in progress': 'blue' });
  });

  it('does not paint a vocabulary the column brought itself', () => {
    // An imported binder's own words are the vocabulary. `done` among them means what the
    // binder meant, and a colour the app chose for it would be the app reading the case.
    const role = normalizeRole({ kind: 'state', values: ['pass', 'done', 'OK en cours'] });
    expect(role.colours).toEqual({});
  });

  it('keeps a state vocabulary in the order it was given, because that is the ranking', () => {
    const role = normalizeRole({ kind: 'state', values: ['seen', 'geolocated', 'ruled out'] });
    expect(role.values).toEqual(['seen', 'geolocated', 'ruled out']);
  });

  it('drops the fields a kind does not use', () => {
    // A column that was a choice and became a when would otherwise keep a vocabulary
    // nobody reads, and the next reader could not tell which of the two it is.
    const role = normalizeRole({ kind: 'when', values: ['a', 'b'], multi: ', ' });
    expect(role).toEqual({ kind: 'when', shape: 'date', dayFirst: true });
  });

  it('defaults a slash date to day first, because guessing reverses twelve days a month', () => {
    expect(normalizeRole({ kind: 'when' }).dayFirst).toBe(true);
    expect(normalizeRole({ kind: 'when', dayFirst: false }).dayFirst).toBe(false);
  });

  it('pins a computed column to a nature it knows', () => {
    expect(normalizeRole({ kind: 'computed' }).of).toBe('has_point');
    expect(normalizeRole({ kind: 'computed', of: 'invented' }).of).toBe('has_point');
    // The order is the contract: the server falls back to the first of them.
    expect(COMPUTED_NATURES).toEqual([
      'has_point',
      'filled_of',
      'yes_of',
      'point',
      'relations',
      'in_case',
    ]);
    expect(COUNTING_NATURES).toEqual(['filled_of', 'yes_of']);
    expect(LINKED_NATURES).toEqual(['point', 'relations']);
  });

  it('makes a linked nature name the column whose link it follows', () => {
    // A sheet can point at the case from a subject column and a place column both, so
    // "whatever this row points at" would answer about whichever came first.
    expect(normalizeRole({ kind: 'computed', of: 'point', from: 'Subject' })).toEqual({
      kind: 'computed',
      of: 'point',
      from: 'Subject',
    });
    expect(normalizeRole({ kind: 'computed', of: 'point' }).from).toBeNull();
    // A list of relations is written with a separator, so the column reads as chips the
    // way any other multi-valued column does.
    expect(normalizeRole({ kind: 'computed', of: 'relations', from: 'Unit' }).multi).toBe(', ');
    // And a counting nature keeps none of that: the fields a kind does not use go.
    expect(normalizeRole({ kind: 'computed', of: 'yes_of', from: 'Unit' }).from).toBeUndefined();
  });

  it('keeps only the columns a counting nature actually counts', () => {
    // The columns are named in the role, so a column dropped in a spreadsheet must not
    // leave the score counting something that is not there.
    const role = normalizeRole({ kind: 'computed', of: 'filled_of', columns: ['a', 'a', 'b', ''] });
    expect(role.columns).toEqual(['a', 'b']);
    // And a nature that counts nothing is not a nature that counts every column.
    expect(normalizeRole({ kind: 'computed', of: 'yes_of' }).columns).toEqual([]);
    // The one that walks the graph has no columns to name.
    expect(normalizeRole({ kind: 'computed', of: 'has_point' }).columns).toBeUndefined();
  });

  it('deduplicates a vocabulary rather than showing one value twice', () => {
    expect(normalizeRole({ kind: 'choice', values: ['a', 'a', 'b'] }).values).toEqual(['a', 'b']);
  });

});

describe('a column with two answers', () => {
  it('starts on the words the binders write, yes first', () => {
    // Yes first because that order is the ranking the sort reads, and because `computed`
    // writes the same pair — one column of YES/NO in the file either way.
    expect(normalizeRole({ kind: 'boolean' }).values).toEqual(BOOLEAN_DEFAULTS);
    expect(BOOLEAN_DEFAULTS).toEqual(['YES', 'NO']);
  });

  it('takes the column own two words', () => {
    expect(normalizeRole({ kind: 'boolean', values: ['oui', 'non'] }).values).toEqual([
      'oui',
      'non',
    ]);
  });

  it('holds exactly two, because one click has to be a toggle', () => {
    const three = normalizeRole({ kind: 'boolean', values: ['a', 'b', 'c'] });
    expect(three.values).toEqual(['a', 'b']);
    expect(normalizeRole({ kind: 'boolean', values: ['only'] }).values).toEqual(['only', 'NO']);
  });

  it('flips to the other word, and answers anything else with the first', () => {
    const role = normalizeRole({ kind: 'boolean' });
    expect(flipBoolean(role, 'YES')).toBe('NO');
    expect(flipBoolean(role, 'NO')).toBe('YES');
    expect(flipBoolean(role, '')).toBe('YES');
    expect(flipBoolean(role, 'maybe')).toBe('YES');
  });

  it('sorts what is true above what is not', () => {
    const role = normalizeRole({ kind: 'boolean' });
    expect(compareByRole(role, 'YES', 'NO')).toBe(-1);
  });

  it('can be drawn as a tick box without becoming another kind of column', () => {
    // A drawing choice and nothing more: the file holds the same two words, so the CSV
    // a collaborator opens reads the same whether the grid drew chips or boxes.
    const role = normalizeRole({ kind: 'boolean', values: ['oui', 'non'], tick: true });
    expect(role.kind).toBe('boolean');
    expect(role.tick).toBe(true);
    expect(role.values).toEqual(['oui', 'non']);
    expect(normalizeRole({ kind: 'boolean' }).tick).toBe(false);
  });

  it('cycles a box through three states, because it is drawn on an empty cell too', () => {
    // Two states would make "nobody has been through this row" and "no" identical, and
    // leave no way back to the first.
    const role = normalizeRole({ kind: 'boolean', tick: true });
    expect(cycleTick(role, '')).toBe('YES');
    expect(cycleTick(role, 'YES')).toBe('NO');
    expect(cycleTick(role, 'NO')).toBe('');
    // A word the pair does not hold is settled by clicking it, as a flip is.
    expect(cycleTick(role, 'maybe')).toBe('YES');
  });

  it('names the three states a box draws, plus the word it cannot read', () => {
    const role = normalizeRole({ kind: 'boolean', values: ['oui', 'non'], tick: true });
    expect(tickState(role, 'oui')).toBe('yes');
    expect(tickState(role, 'non')).toBe('no');
    expect(tickState(role, '  ')).toBe('blank');
    expect(tickState(role, 'peut-être')).toBe('other');
  });

  it('is guessed only when both words are a yes or a no', () => {
    expect(detectRole(['YES', 'NO', 'YES', 'NO'])).toBe('boolean');
    expect(detectRole(['true', 'false', 'true', 'false'])).toBe('boolean');
    expect(detectRole(['oui', 'non', 'oui', 'non'])).toBe('boolean');
    // Two arbitrary words are a set of values, not a question with two answers.
    expect(detectRole(['VID', 'PIC', 'VID', 'PIC'])).toBe('choice');
  });
});

describe('a column of numbers', () => {
  it('reads what a European export writes', () => {
    expect(parseNumber('1 200')).toBe(1200);
    expect(parseNumber("1'200")).toBe(1200);
    expect(parseNumber('12,5')).toBe(12.5);
    expect(parseNumber('-3')).toBe(-3);
    expect(parseNumber('42%')).toBe(42);
  });

  it('reads an estimate, because an estimate is still a number', () => {
    expect(parseNumber('~9')).toBe(9);
    expect(parseNumber('>100')).toBe(100);
  });

  it('refuses a digit buried in prose', () => {
    // `Only 9 in service?` is a real cell. Extracting the 9 would also turn `AB-123` into
    // 123, and a total built on that is a total nobody can check.
    expect(parseNumber('Only 9 in service?')).toBeNull();
    expect(parseNumber('AB-123')).toBeNull();
    expect(parseNumber('')).toBeNull();
    expect(parseNumber('-')).toBeNull();
  });

  it('totals the rows it is given, which is what is on screen', () => {
    const table = {
      columns: ['id', 'Count'],
      rows: [['r1', '3'], ['r2', '1 200'], ['r3', 'Only 9 in service?'], ['r4', '-5']],
    };
    expect(numberTotals(table, 1, [0, 1, 2, 3])).toEqual({
      count: 3,
      // `Only 9 in service?` is filled and unread, which is what the badge and the
      // "to check" filter are made of: a total over three of four cells says so.
      unreadable: 1,
      sum: 1198,
      mean: 399.333333,
      min: -5,
      max: 1200,
    });
    // Filtered to one row, the answer is about that row.
    expect(numberTotals(table, 1, [0])).toEqual({
      count: 1, unreadable: 0, sum: 3, mean: 3, min: 3, max: 3,
    });
    expect(numberTotals(table, 1, [2])).toEqual({
      count: 0, unreadable: 1, sum: 0, mean: null, min: null, max: null,
    });
  });

  it('keeps a footer free of floating-point noise', () => {
    const table = { columns: ['id', 'n'], rows: [['r1', '0.1'], ['r2', '0.2']] };
    expect(numberTotals(table, 1, [0, 1]).sum).toBe(0.3);
  });

  it('sorts by the number, and sends what it cannot read to the end', () => {
    const role = normalizeRole({ kind: 'number' });
    expect(compareByRole(role, '9', '10')).toBe(-1);
    expect(compareByRole(role, '1 200', '9')).toBe(1);
    expect(compareByRole(role, '9', 'Only 9 in service?')).toBe(-1);
    expect(compareByRole(role, 'a', 'b')).toBeNull();
  });
});

describe('a colour on a value', () => {
  it('keeps a value that merely contains a hash', () => {
    // `#REF!` is what a binder's own broken formula leaves behind, and it is in this
    // repo's fixtures. The colour is picked from a palette rather than typed at the end
    // of the word, so nothing has to guess where the value stops.
    const role = normalizeRole({ kind: 'choice', values: ['#REF!'], colours: { '#REF!': 'grey' } });
    expect(role.values).toEqual(['#REF!']);
    expect(role.colours).toEqual({ '#REF!': 'grey' });
  });

  it('drops a colour whose value or name it does not know', () => {
    const role = normalizeRole({
      kind: 'state',
      values: ['done'],
      colours: { done: 'green', gone: 'red', gone2: 'chartreuse' },
    });
    expect(role.colours).toEqual({ done: 'green' });
  });

  it('hands a chip the colour of its value', () => {
    const role = normalizeRole({ kind: 'state', values: ['done'], colours: { done: 'green' } });
    expect(cellChips('done', role)[0].colour).toBe('green');
    expect(cellChips('pass', role)[0].colour).toBeNull();
  });

  it('paints the same palette a row is painted with', () => {
    // One colour language in the app: a third list would be the one that goes wrong.
    expect(ROW_COLOURS).toEqual(['red', 'orange', 'yellow', 'green', 'blue', 'grey']);
  });
});

describe('guessing what a column holds', () => {
  it('recognises a column of points', () => {
    expect(detectRole(['48.85, 2.35', '50.10, 3.00', '-33.87, 151.21'])).toBe('latlon');
  });

  it('recognises one through the gaps, because a binder is half-finished', () => {
    // A coordinates column with `To be found` in a third of its rows is still a
    // coordinates column, and refusing to see that is refusing the real file.
    expect(detectRole(['48.85, 2.35', '50.10, 3.00', 'To be found'])).toBe('latlon');
  });

  it('recognises a column of dates', () => {
    expect(detectRole(['03/01/2026', '14/02/2026', '?'])).toBe('when');
  });

  it('recognises a small closed set of answers', () => {
    expect(detectRole(['VID', 'PIC', 'VID', 'SAT', 'VID', 'PIC'])).toBe('choice');
  });

  it('never guesses a state, because that is a judgement and not a shape', () => {
    // Whether `pass` means done or means skipped is a fact about the investigation.
    expect(detectRole(['to do', 'done', 'pass', 'to do', 'done', 'pass'])).toBe('choice');
  });

  it('guesses nothing about prose, or about one cell', () => {
    expect(detectRole(['a note about the quay', 'another, longer note', 'a third one'])).toBeNull();
    expect(detectRole(['48.85, 2.35'])).toBeNull();
    expect(detectRole([])).toBeNull();
  });
});

describe('reading a point', () => {
  it('reads the decimal pair the binders write four hundred times', () => {
    expect(parseLatLon('48.8566, 2.3522')).toMatchObject({ lat: 48.8566, lon: 2.3522 });
    expect(parseLatLon('48.8566,2.3522')).toMatchObject({ lat: 48.8566, lon: 2.3522 });
    expect(parseLatLon(' 48.8566 2.3522 ')).toMatchObject({ lat: 48.8566, lon: 2.3522 });
    expect(parseLatLon('-33.87, 151.21')).toMatchObject({ lat: -33.87, lon: 151.21 });
  });

  it('reads hemispheres as signs', () => {
    expect(parseLatLon('33.87S, 151.21E')).toMatchObject({ lat: -33.87, lon: 151.21 });
    expect(parseLatLon('40.71N, 74.01W')).toMatchObject({ lat: 40.71, lon: -74.01 });
  });

  it('reads degrees, minutes and seconds', () => {
    const point = parseLatLon(`48°51'24"N 2°21'08"E`);
    expect(point.lat).toBeCloseTo(48.8567, 3);
    expect(point.lon).toBeCloseTo(2.3522, 3);
  });

  it('reads a comma decimal, which a European export writes', () => {
    expect(parseLatLon('48,8566; 2,3522')).toMatchObject({ lat: 48.8566, lon: 2.3522 });
    expect(parseLatLon('48,8566 2,3522')).toMatchObject({ lat: 48.8566, lon: 2.3522 });
  });

  it('reads a bare comma as the decimal mark rather than as a pair', () => {
    // `48,8` is one number in the convention the whole app reads, so it is not a point.
    // The comma separates two coordinates only where it cannot be decimal: a space after
    // it, a hemisphere letter, or a full stop already doing the job.
    expect(parseLatLon('48,8')).toBeNull();
    expect(parseLatLon('-1,5')).toBeNull();
    expect(parseLatLon('48, 8')).toMatchObject({ lat: 48, lon: 8 });
    expect(parseLatLon('48.5,2.3')).toMatchObject({ lat: 48.5, lon: 2.3 });
    expect(parseLatLon('48,8N 2,3E')).toMatchObject({ lat: 48.8, lon: 2.3 });
  });

  it('refuses what is not a point, without complaining', () => {
    // `To be found` is on every page of the real binders. A role that threw here would
    // refuse the work.
    expect(parseLatLon('To be found')).toBeNull();
    expect(parseLatLon('-')).toBeNull();
    expect(parseLatLon('')).toBeNull();
    expect(parseLatLon('48.8566')).toBeNull();
  });

  it('reports how precisely the cell was written', () => {
    expect(parseLatLon('48.85, 2.35').decimals).toBe(2);
    expect(parseLatLon('48.8566, 2.3').decimals).toBe(1);
    expect(precisionMetres(2)).toBe(1113); // about a kilometre
    expect(precisionMetres(5)).toBe(1);
  });

  it('reports a transposed pair rather than refusing it', () => {
    // Latitude 151 is a finding about the file, not a parse failure.
    expect(parseLatLon('151.21, 33.87').outOfBounds).toBe(true);
    expect(parseLatLon('48.85, 2.35').outOfBounds).toBe(false);
  });

  it('writes a point back as one canonical form', () => {
    expect(formatLatLon(parseLatLon(`48°51'24"N 2°21'08"E`))).toBe('48.85667, 2.35222');
    expect(formatLatLon(null)).toBe('');
  });

  it('measures the ground between two points', () => {
    // A hundred metres north, near the equator.
    expect(distanceMetres({ lat: 0, lon: 0 }, { lat: 0.0008993, lon: 0 })).toBe(100);
    expect(distanceMetres({ lat: 48.8566, lon: 2.3522 }, { lat: 48.8566, lon: 2.3522 })).toBe(0);
  });
});

describe('reading a moment', () => {
  it('reads the European slash date the binders use', () => {
    expect(parseWhen('03/01/2026').text).toBe('2026-01-03');
    expect(parseWhen('3/1/26').text).toBe('2026-01-03');
  });

  it('reads a month-first column when the column says so', () => {
    expect(parseWhen('03/01/2026', { dayFirst: false }).text).toBe('2026-03-01');
  });

  it('reads a four-digit lead as a year whatever the convention', () => {
    expect(parseWhen('2026-01-03', { dayFirst: true }).text).toBe('2026-01-03');
  });

  it('keeps a bare clock as a time of day rather than inventing a date', () => {
    // The binder's `Local time` holds `01:57` for an event whose date is in the sheet's
    // title. Pinning it to a date would be inventing evidence.
    const read = parseWhen('01:57');
    expect(read.shape).toBe('time');
    expect(read.key).toBe(1 * 3600 + 57 * 60);
    expect(read.text).toBe('01:57');
  });

  it('reads the full form an email header carries', () => {
    expect(parseWhen('Sat, 03 Jan 2026 06:42:02 GMT').text).toBe('2026-01-03T06:42');
  });

  it('reads a date with a time appended', () => {
    expect(parseWhen('03/01/2026 06:42').text).toBe('2026-01-03T06:42');
  });

  it('refuses what the binders write when they do not know', () => {
    expect(parseWhen('AFTER')).toBeNull();
    expect(parseWhen('?')).toBeNull();
    expect(parseWhen('between 2:00 and 2:10')).toBeNull();
    expect(parseWhen('32/01/2026')).toBeNull();
    expect(parseWhen('03/13/2026')).toBeNull();
    expect(parseWhen('25:00')).toBeNull();
  });

  it('refuses an hour that would roll over into the next day', () => {
    // `Date.UTC` takes 12:30:75 and keeps the day, and it takes 99:00 and moves it — so
    // both used to pass here while the server refused them, and the analyst read a message
    // about a value they never typed.
    expect(parseWhen('12:30:75')).toBeNull();
    expect(parseWhen('03/01/2026 12:30:75')).toBeNull();
    expect(parseWhen('03/01/2026 99:00')).toBeNull();
    expect(parseWhen('Sat, 03 Jan 2026 06:42:99 GMT')).toBeNull();
    expect(parseWhen('23:59:59').text).toBe('23:59');
  });
});

describe('the same cell, read by both readers', () => {
  // The parity gate the role vocabularies had and the readings did not. The other half is
  // `tests/test_sheets.py::test_both_readers_answer_the_same_thing_about_a_shared_list_of_cells`,
  // over this same file: one list of cells, one expected answer, two readers held to it.
  it('answers what the shared fixture says, cell by cell', () => {
    expect(shared.when.length && shared.point.length).toBeTruthy();
    for (const entry of shared.when) {
      const read = parseWhen(entry.cell, entry.role ?? {});
      if (entry.reads === null) {
        expect(read, entry.cell).toBeNull();
        continue;
      }
      expect(read, entry.cell).not.toBeNull();
      expect(read.shape, entry.cell).toBe(entry.reads.shape);
      expect(read.text, entry.cell).toBe(entry.reads.text);
    }
    for (const entry of shared.point) {
      const point = parseLatLon(entry.cell);
      if (entry.reads === null) {
        expect(point, entry.cell).toBeNull();
        continue;
      }
      expect(point, entry.cell).not.toBeNull();
      expect(point.lat).toBeCloseTo(entry.reads.lat, 6);
      expect(point.lon).toBeCloseTo(entry.reads.lon, 6);
      expect(point.decimals, entry.cell).toBe(entry.reads.decimals);
    }
  });
});

describe('a moment chosen from a picker', () => {
  it('reads which way round the column already writes them', () => {
    // Read from the column and not declared: a picker that wrote `2026-01-31` into a
    // column of `31/01/2026` would restyle the file from a click.
    expect(dateSpelling(['31/01/2026', '14/02/2026'])).toBe('slash');
    expect(dateSpelling(['2026-01-31', '2026-02-14'])).toBe('iso');
    expect(dateSpelling(['AFTER', '?'])).toBe('slash', 'ties go to what the binders use');
  });

  it('reads which of the three shapes the column holds', () => {
    // The role is called "a date or a time" because the binders write all three. Offering
    // a calendar on a column of bare clocks would offer to put a date where the analyst
    // deliberately did not.
    expect(whenShape(['31/01/2026', '14/02/2026'])).toBe('date');
    expect(whenShape(['01:57', '02:10'])).toBe('time');
    expect(whenShape(['03/01/2026 06:42', '04/01/2026 07:15'])).toBe('datetime');
    expect(whenShape(['AFTER', '?'])).toBe('date');
  });

  it('picks each shape with the input made for it', () => {
    expect(pickerType('date')).toBe('date');
    expect(pickerType('time')).toBe('time');
    expect(pickerType('datetime')).toBe('datetime-local');
  });

  it('spells the picked moment the way the column does', () => {
    expect(spellWhen('2026-03-04', { spelling: 'slash' })).toBe('04/03/2026');
    expect(spellWhen('2026-03-04', { spelling: 'iso' })).toBe('2026-03-04');
    expect(spellWhen('', {})).toBe('');
  });

  it('spells a time on its own, and a moment with its hour', () => {
    expect(spellWhen('01:57', { shape: 'time' })).toBe('01:57');
    expect(spellWhen('2026-03-04T06:42', { shape: 'datetime', spelling: 'slash' }))
      .toBe('04/03/2026 06:42');
  });

  it('keeps an hour the cell already established on a date-only column', () => {
    // `03/01/2026 06:42` is a moment somebody worked out; a date picker has no business
    // dropping the hour from it.
    expect(spellWhen('2026-03-04', { spelling: 'slash', keep: '03/01/2026 06:42' }))
      .toBe('04/03/2026 06:42');
    expect(spellWhen('2026-03-04', { spelling: 'slash', keep: '03/01/2026' })).toBe('04/03/2026');
  });
});

describe('a cell holding several values', () => {
  const role = normalizeRole({ kind: 'choice', multi: DEFAULT_SEPARATOR, values: ['Buk-M2E'] });

  it('splits on the separator the column declares, and only then', () => {
    expect(splitValues('Buk-M2E, ZU23-2', role)).toEqual(['Buk-M2E', 'ZU23-2']);
    expect(splitValues('Buk-M2E, ZU23-2', normalizeRole({ kind: 'choice' }))).toEqual([
      'Buk-M2E, ZU23-2',
    ]);
  });

  it('reads a list as its values and nothing else', () => {
    // A quantity used to be read out of `2x S-125` here. It is gone: a count kept in a
    // column of values is a count nothing can total, and the answer is a number column
    // beside a column naming what is counted.
    const list = normalizeRole({ kind: 'choice', multi: ', ' });
    expect(cellChips('Buk-M2E, 2x S-125', list)).toEqual([
      { value: 'Buk-M2E', known: false, colour: null, raw: 'Buk-M2E' },
      { value: '2x S-125', known: false, colour: null, raw: '2x S-125' },
    ]);
  });

  it('marks a value the vocabulary does not know without hiding it', () => {
    const chips = cellChips('Buk-M2E, Mi-17V-5', role);
    expect(chips.map((chip) => chip.known)).toEqual([true, false]);
  });

  it('totals by value, one row counted once however often it says it', () => {
    const list = normalizeRole({ kind: 'choice', multi: ', ' });
    const table = {
      columns: ['id', 'Equipments'],
      rows: [
        ['r1', 'S-125, S-125'],
        ['r2', 'S-125, Buk-M2E'],
      ],
    };
    // How many rows hold it, which is what the filter hands back.
    expect(valueTotals(table, 1, list)).toEqual([
      { value: 'S-125', rows: 2 },
      { value: 'Buk-M2E', rows: 1 },
    ]);
  });
});

describe('sorting by what the column knows', () => {
  it('orders a state column by its vocabulary, not its alphabet', () => {
    const role = normalizeRole({ kind: 'state' });
    // 'in progress' before 'ruled out' by rank; alphabetically it would be the reverse
    // of nothing useful at all.
    expect(compareByRole(role, 'in progress', 'ruled out')).toBe(-1);
    expect(compareByRole(role, 'done', 'to do')).toBe(1);
    expect(compareByRole(role, 'done', 'done')).toBeNull();
  });

  it('puts a word outside the vocabulary after the ones inside it', () => {
    const role = normalizeRole({ kind: 'state' });
    expect(compareByRole(role, 'OK en cours', 'ruled out')).toBe(1);
  });

  it('orders a date column by the date, which is the whole point of the role', () => {
    // Without the role this is text: `01/02/2026` sorts before `31/01/2026`.
    const role = normalizeRole({ kind: 'when' });
    expect(compareByRole(role, '31/01/2026', '01/02/2026')).toBe(-1);
  });

  it('sends the cells a date column cannot read to the end', () => {
    const role = normalizeRole({ kind: 'when' });
    expect(compareByRole(role, '31/01/2026', 'AFTER')).toBe(-1);
    expect(compareByRole(role, 'AFTER', '31/01/2026')).toBe(1);
    expect(compareByRole(role, 'AFTER', '?')).toBeNull();
  });

  it('keeps dates and bare clocks apart, because they are different scales', () => {
    const role = normalizeRole({ kind: 'when' });
    expect(compareByRole(role, '03/01/2026', '01:57')).toBe(-1);
    expect(compareByRole(role, '01:57', '02:10')).toBe(-1);
  });

  it('orders points north to south, so one area reads together', () => {
    const role = normalizeRole({ kind: 'latlon' });
    expect(compareByRole(role, '50.1, 3.0', '48.8, 2.3')).toBe(-1);
    expect(compareByRole(role, '48.8, 2.3', '48.8, 4.0')).toBe(-1);
  });

  it('says nothing about a column of values, which sorts as the words it holds', () => {
    expect(compareByRole(normalizeRole({ kind: 'choice', multi: ', ' }), 'A, B', 'C')).toBeNull();
  });

  it('says nothing when it has nothing to say, so the caller falls back to words', () => {
    expect(compareByRole(null, 'a', 'b')).toBeNull();
    expect(compareByRole({ kind: 'stamped' }, 'a', 'b')).toBeNull();
    expect(compareByRole(normalizeRole({ kind: 'choice' }), 'a', 'b')).toBeNull();
  });
});

describe('how much is left', () => {
  // The 468-row geolocation index: a date, a title, a country, coordinates, a link.
  // No status column anywhere, and its question is how many are left to geolocate.
  const index = () => ({
    columns: ['id', 'Title', 'Coordinates'],
    rows: [
      ['r1', 'Quai sud', '48.85, 2.35'],
      ['r2', 'Pont nord', ''],
      ['r3', 'Gare est', '   '],
      ['r4', 'Dépôt', '50.10, 3.00'],
    ],
  });

  it('reads a fill rate off any column, with no role at all', () => {
    expect(columnProgress(index(), 2, null)).toEqual({
      kind: 'fill',
      total: 4,
      filled: 2,
      empty: 2,
    });
  });

  it('counts whitespace as empty, the way the analyst reads it', () => {
    expect(columnProgress(index(), 2, null).empty).toBe(2);
  });

  it('reads a state column as its buckets, in vocabulary order', () => {
    const table = {
      columns: ['id', 'Status'],
      rows: [['r1', 'done'], ['r2', 'done'], ['r3', 'to do'], ['r4', ''], ['r5', 'pass']],
    };
    expect(columnProgress(table, 1, normalizeRole({ kind: 'state' }))).toEqual({
      kind: 'state',
      total: 5,
      empty: 1,
      other: 1,
      buckets: [
        { value: 'to do', count: 1 },
        { value: 'in progress', count: 0 },
        { value: 'done', count: 2 },
        { value: 'ruled out', count: 0 },
      ],
    });
  });

  it('suggests the declared status column when there is one', () => {
    const table = { columns: ['id', 'Status', 'Notes'], rows: [['r1', '', '']] };
    expect(suggestProgressColumn(table, { Status: { kind: 'state' } }, 'id')).toBe('Status');
  });

  it('otherwise suggests the emptiest column, which is the one being worked through', () => {
    expect(suggestProgressColumn(index(), {}, 'id')).toBe('Coordinates');
  });

  it('suggests nothing when there is nothing left to fill', () => {
    const full = { columns: ['id', 'Title'], rows: [['r1', 'Quai sud']] };
    expect(suggestProgressColumn(full, {}, 'id')).toBeNull();
  });

  it('never suggests the row handle', () => {
    const table = { columns: ['id', 'Title'], rows: [['', ''], ['', '']] };
    expect(suggestProgressColumn(table, {}, 'id')).toBe('Title');
  });
});

describe('what a column says twice', () => {
  const table = () => ({
    columns: ['id', 'Plate'],
    rows: [['r1', 'AB-123'], ['r2', 'ab-123'], ['r3', ''], ['r4', 'CD-456'], ['r5', 'AB-123']],
  });

  it('groups the repeats and leaves the singles out', () => {
    const groups = duplicateGroups(table(), 1, null);
    expect(groups).toHaveLength(1);
    expect(groups[0].rows).toEqual([0, 1, 4]);
  });

  it('reads a difference of case as the same value', () => {
    expect(duplicateGroups(table(), 1, null)[0].value).toBe('AB-123');
  });

  it('does not call four hundred empty cells a duplicate', () => {
    const empty = { columns: ['id', 'Coordinates'], rows: [['r1', ''], ['r2', ''], ['r3', '']] };
    expect(duplicateGroups(empty, 1, null)).toEqual([]);
  });

  it('finds a value repeated inside a multi-value column', () => {
    const role = normalizeRole({ kind: 'choice', multi: ', ' });
    const lists = {
      columns: ['id', 'Equipments'],
      rows: [['r1', 'Buk-M2E, ZU23-2'], ['r2', 'S-125, Buk-M2E']],
    };
    expect(duplicateGroups(lists, 1, role)[0]).toMatchObject({ value: 'Buk-M2E', rows: [0, 1] });
  });
});

describe('points that sit too close to be two places', () => {
  it('finds the pair and says how far apart it is', () => {
    const table = {
      columns: ['id', 'Coordinates'],
      rows: [
        ['r1', '48.85660, 2.35220'],
        ['r2', '48.85670, 2.35230'],
        ['r3', '50.10000, 3.00000'],
      ],
    };
    const { pairs, capped } = nearbyPairs(table, 1, 200);
    expect(capped).toBe(false);
    expect(pairs).toHaveLength(1);
    expect(pairs[0].rows).toEqual([0, 1]);
    expect(pairs[0].metres).toBeLessThan(20);
  });

  it('finds a pair that straddles a bucket edge', () => {
    // The reason the sweep looks at the eight neighbouring cells: two points ten metres
    // apart can fall either side of a boundary, and a single-cell sweep would miss them.
    const table = {
      columns: ['id', 'Coordinates'],
      rows: [['r1', '48.8500000, 2.3500000'], ['r2', '48.8500899, 2.3500000']],
    };
    expect(nearbyPairs(table, 1, 20).pairs).toHaveLength(1);
  });

  it('ignores what it cannot read and what cannot be true', () => {
    const table = {
      columns: ['id', 'Coordinates'],
      rows: [['r1', 'To be found'], ['r2', '151.2, 33.8'], ['r3', '48.85, 2.35']],
    };
    expect(nearbyPairs(table, 1, 500).pairs).toEqual([]);
  });

  it('stays linear on a sheet at the row bound instead of freezing the tab', () => {
    // Twenty thousand rows compared pair by pair is two hundred million comparisons.
    // Bucketed, this is one pass, and the assertion is that it returns at all.
    const rows = Array.from({ length: 20_000 }, (_, index) => [
      `r${index}`,
      `${(index % 900) / 10 + 1}, ${(index % 700) / 10 + 1}`,
    ]);
    const { pairs, capped } = nearbyPairs({ columns: ['id', 'Coordinates'], rows }, 1, 50);
    expect(capped).toBe(true);
    expect(pairs.length).toBeGreaterThan(0);
  });

  it('says when the list was cut rather than reading as complete', () => {
    const rows = Array.from({ length: 60 }, (_, index) => [`r${index}`, '48.85000, 2.35000']);
    const { pairs, capped } = nearbyPairs({ columns: ['id', 'Coordinates'], rows }, 1, 100, {
      cap: 10,
    });
    expect(pairs).toHaveLength(10);
    expect(capped).toBe(true);
  });
});

describe('a column vocabulary the analyst edits', () => {
  const role = normalizeRole({
    kind: 'state',
    values: ['to do', 'in progress', 'done'],
    colours: { done: 'green' },
  });

  it('moves a value, because the order is the ranking the sort reads', () => {
    const moved = editVocabulary(role, { value: 'done', at: 2, to: 0 });
    expect(moved.values).toEqual(['done', 'to do', 'in progress']);
    expect(moved.colours).toEqual({ done: 'green' });
  });

  it('adds a value it has never heard of, and refuses one it has', () => {
    expect(editVocabulary(role, { value: 'ruled out' }).values).toContain('ruled out');
    expect(editVocabulary(role, { value: 'done' }).values).toHaveLength(3);
  });

  it('carries a colour through a rename, because the colour belongs to the answer', () => {
    const renamed = editVocabulary(role, { value: 'settled', at: 2 });
    expect(renamed.values).toEqual(['to do', 'in progress', 'settled']);
    expect(renamed.colours).toEqual({ settled: 'green' });
  });

  it('drops a value and the colour hanging on it', () => {
    const dropped = editVocabulary(role, { value: 'done', at: 2, to: -1 });
    expect(dropped.values).toEqual(['to do', 'in progress']);
    expect(dropped.colours).toEqual({});
  });

  it('paints and unpaints one value, refusing a colour off the palette', () => {
    expect(editVocabulary(role, { value: 'to do', at: 0, colour: 'red' }).colours['to do']).toBe('red');
    expect(editVocabulary(role, { value: 'done', at: 2, colour: null }).colours).toEqual({});
    expect(editVocabulary(role, { value: 'to do', at: 0, colour: 'chartreuse' }).colours['to do'])
      .toBeUndefined();
  });

  it('sorts only when asked, in both directions', () => {
    // Never on its own: `to do → in progress → done` is the order of the work, and an
    // editor that alphabetised it would have put `done` first.
    expect(sortVocabulary(role).values).toEqual(['done', 'in progress', 'to do']);
    expect(sortVocabulary(role, { desc: true }).values).toEqual(['to do', 'in progress', 'done']);
  });

  it('says which values are used, which are not, and which words sit outside', () => {
    const table = {
      columns: ['id', 'Status'],
      rows: [['r1', 'done'], ['r2', 'done'], ['r3', 'OK en cours'], ['r4', '']],
    };
    const use = vocabularyUse(table, 1, role);
    expect(use.counts).toEqual({ 'to do': 0, 'in progress': 0, done: 2 });
    expect(use.unused).toEqual(['to do', 'in progress']);
    expect(use.outside.map((entry) => entry.value)).toEqual(['OK en cours']);
  });
});

describe('the cells a lens cannot read', () => {
  it('reads an empty cell, because blank is unknown rather than wrong', () => {
    expect(readsCell({ kind: 'number' }, '')).toBe(true);
    expect(readsCell({ kind: 'number' }, '   ')).toBe(true);
  });

  it('answers per kind, and calls a point off the globe unreadable', () => {
    expect(readsCell({ kind: 'number' }, '1 200')).toBe(true);
    expect(readsCell({ kind: 'number' }, 'about 12')).toBe(false);
    expect(readsCell({ kind: 'latlon' }, '48.85, 2.35')).toBe(true);
    expect(readsCell({ kind: 'latlon' }, '148.85, 2.35')).toBe(false);
    expect(readsCell({ kind: 'when', dayFirst: true }, '31/01/2026')).toBe(true);
    expect(readsCell({ kind: 'when', dayFirst: true }, 'AFTER')).toBe(false);
  });

  it('calls a value outside a vocabulary unreadable, which is what "to check" means', () => {
    const role = normalizeRole({ kind: 'choice', multi: ', ', values: ['a', 'b'] });
    expect(readsCell(role, 'a, b')).toBe(true);
    expect(readsCell(role, 'a, zzz')).toBe(false);
  });

  it('counts what a column reads against what it holds', () => {
    const table = {
      columns: ['id', 'Count'],
      rows: [['r1', '3'], ['r2', 'about 12'], ['r3', '']],
    };
    expect(readable(table, 1, { kind: 'number' })).toEqual({ total: 2, read: 1, unreadable: 1 });
  });
});

describe('a number column that says how it is written', () => {
  it('keeps a unit, where it is written and which answer the footer gives', () => {
    const role = normalizeRole({ kind: 'number', unit: ' % ', summary: 'mean', unitInCells: 1 });
    expect(role).toEqual({ kind: 'number', unit: '%', unitInCells: true, summary: 'mean' });
  });

  it('leaves the unit to the heading unless the column asked for it in the cells', () => {
    expect(normalizeRole({ kind: 'number', unit: 'km' }).unitInCells).toBe(false);
  });

  it('falls back to a total, which is what a column of counts wants', () => {
    expect(normalizeRole({ kind: 'number' }).summary).toBe('sum');
    expect(normalizeRole({ kind: 'number', summary: 'invented' }).summary).toBe('sum');
  });
});


describe('what a cell is worth to a sort, read once', () => {
  it('says nothing for a role that does not order anything', () => {
    expect(sortsByRole(normalizeRole({ kind: 'choice', multi: ', ' }))).toBe(false);
    expect(sortsByRole(null)).toBe(false);
    expect(sortsByRole(normalizeRole({ kind: 'when' }))).toBe(true);
    expect(sortsByRole(normalizeRole({ kind: 'state', values: ['a'] }))).toBe(true);
  });

  it('orders by the key alone, the way the comparator then does', () => {
    const role = normalizeRole({ kind: 'when' });
    const keys = ['31/01/2026', '01/02/2026', 'AFTER'].map((cell) => sortKey(role, cell));

    expect(compareSortKeys(keys[0], keys[1])).toBe(-1);
    // A cell the role cannot read sorts after the ones it can.
    expect(compareSortKeys(keys[0], keys[2])).toBe(-1);
    expect(compareSortKeys(keys[2], keys[0])).toBe(1);
    // Two it cannot read do not settle it, so the caller compares the words.
    expect(compareSortKeys(keys[2], sortKey(role, '?'))).toBeNull();
  });

  it('reads a cell once where the comparator read it on every comparison', () => {
    // The point of the whole shape: n reads rather than 2·n·log n of them.
    const role = normalizeRole({ kind: 'when' });
    let reads = 0;
    const cells = Array.from({ length: 64 }, (_, at) => `0${(at % 9) + 1}/01/2026`);
    const keys = cells.map((cell) => {
      reads += 1;
      return sortKey(role, cell);
    });
    const order = keys.map((_, at) => at).sort((a, b) => compareSortKeys(keys[a], keys[b]) ?? a - b);

    expect(reads).toBe(cells.length);
    expect(order.length).toBe(cells.length);
  });
});