web/src/customer-grid/fileSelect.ts

// ---------------------------------------------------------------------------
// customer-grid / fileSelect.ts — WAVE 21 item 11 (ruling R10, contract C5):
// "Select from file…", the PURE half.
//
// The owner's ask is small to describe and easy to get quietly wrong: paste or
// upload a list of names, and have the grid tick those records. Every way it
// fails is silent —
//
//   · matching over the ROWS GLIDE HAS PAINTED rather than the rows the view
//     matched. `displayRows` is a capped slice (`DISPLAY_PAGE`); a list of 900
//     names would report ~200 selected and hundreds "not found", all of which
//     exist. This module never sees the slice.
//   · reporting a count without the misses. "812 selected" out of a 900-row
//     file is a number nobody can act on; the 88 that did not match are the
//     whole reason someone runs this ([[no-unverifiable-aggregates]]).
//   · conflating "not in this view" with "not in this table". A filtered view
//     turns half a correct file into apparent garbage, and the user goes
//     looking for a data problem that is a filter.
//
// So this returns THREE buckets, not a count, and the dialog renders what it is
// given. React-free so `verify_grid_ux.py` can run it under node.
// ---------------------------------------------------------------------------

/** The one comparison rule (R10): trim, then casefold. Nothing else — no
 *  punctuation stripping, no number parsing, no fuzzy anything. A match a user
 *  cannot predict is worse than a miss they can see and fix. */
export function normalizeKey(value: unknown): string {
  if (value === null || value === undefined) return "";
  return String(value).trim().toLowerCase();
}

export interface PastedList {
  /** The candidate values, in the order pasted, blanks dropped. */
  values: string[];
  /** How many lines were dropped for being empty. Reported, never hidden. */
  blanks: number;
  /**
   * The paste carried MORE THAN ONE COLUMN (tab-separated) and only the first
   * is being matched. Disclosed in the dialog — a range copied out of Excel is
   * the common case, and silently reading one column of it is the kind of
   * helpfulness that becomes a support question.
   */
  extraColumns: boolean;
}

/**
 * Read a pasted list.
 *
 * ⚠ SPLIT ON NEWLINES AND TABS ONLY — never commas. "Acme, Inc." is a company
 * name, and a comma split would turn one customer into two values, one of which
 * can never match anything. A CSV file goes through the UPLOAD path, where the
 * server parses it properly (C5).
 */
export function parsePastedList(text: string): PastedList {
  const lines = String(text ?? "").split(/\r?\n/);
  const values: string[] = [];
  let blanks = 0;
  let extraColumns = false;
  for (const line of lines) {
    const cells = line.split("\t");
    if (cells.length > 1 && cells.slice(1).some((c) => c.trim() !== "")) extraColumns = true;
    const first = (cells[0] ?? "").trim();
    if (!first) {
      // A trailing newline is not a blank the user needs told about; a gap in
      // the middle of a pasted column is. Both are counted the same way here
      // and the dialog only mentions the total when it is non-zero.
      if (line.trim() === "" && cells.length === 1) blanks += 1;
      else blanks += 1;
      continue;
    }
    values.push(first);
  }
  // The trailing newline every "copy a column" produces would otherwise report
  // one phantom blank on every paste.
  if (blanks > 0 && text.endsWith("\n")) blanks -= 1;
  return { values, blanks, extraColumns };
}

/** The minimum a row must look like for this module. */
export interface MatchRow {
  pid: number;
  [key: string]: unknown;
}

export interface MatchResult {
  /** Records to tick, de-duplicated, in the order the file named them. */
  pids: number[];
  /** File values that matched at least one row IN THE VIEW. */
  matched: string[];
  /**
   * File values that name a record in this TABLE that the current view does not
   * show. A filter, not a data problem — and the difference is the single most
   * useful thing this result carries.
   */
  outsideView: string[];
  /** File values with no record anywhere in this table. */
  missing: string[];
  /** File values that appeared more than once in the file. */
  duplicates: number;
  /**
   * Values matching MORE THAN ONE record. All of them are selected (dropping
   * the ambiguity would be a silent cap), and the count is disclosed so
   * "40 values, 47 selected" is explained rather than mysterious.
   */
  ambiguous: number;
}

/**
 * Match `values` against `columnKey` over the rows the view matched, with the
 * whole table as the second opinion.
 *
 * ⚠ `inView` MUST be the full matched set (`visibleRows`), never the painted
 * slice (`displayRows`). Passing the slice compiles, runs, and lies.
 */
export function matchRowsByValue(
  values: string[],
  columnKey: string,
  inView: MatchRow[],
  wholeTable: MatchRow[]
): MatchResult {
  const index = new Map<string, number[]>();
  for (const row of inView) {
    const k = normalizeKey(row[columnKey]);
    if (!k) continue;
    const at = index.get(k);
    if (at) at.push(row.pid);
    else index.set(k, [row.pid]);
  }
  const elsewhere = new Set<string>();
  for (const row of wholeTable) {
    const k = normalizeKey(row[columnKey]);
    if (k && !index.has(k)) elsewhere.add(k);
  }

  const pids: number[] = [];
  const seenPid = new Set<number>();
  const matched: string[] = [];
  const outsideView: string[] = [];
  const missing: string[] = [];
  const seenValue = new Set<string>();
  let duplicates = 0;
  let ambiguous = 0;

  for (const raw of values) {
    const k = normalizeKey(raw);
    if (!k) continue;
    if (seenValue.has(k)) {
      duplicates += 1;
      continue;
    }
    seenValue.add(k);
    const hits = index.get(k);
    if (hits) {
      matched.push(raw);
      if (hits.length > 1) ambiguous += 1;
      for (const pid of hits)
        if (!seenPid.has(pid)) {
          seenPid.add(pid);
          pids.push(pid);
        }
    } else if (elsewhere.has(k)) {
      outsideView.push(raw);
    } else {
      missing.push(raw);
    }
  }
  return { pids, matched, outsideView, missing, duplicates, ambiguous };
}

/**
 * The result, in one sentence — R10's "N selected · M not found", with the two
 * facts that ruling could not have anticipated folded in only when they are
 * non-zero (a sentence that always recites five clauses is one nobody reads).
 */
export function matchSummary(r: MatchResult): string {
  const parts = [`${r.pids.length.toLocaleString()} selected`];
  if (r.outsideView.length)
    parts.push(`${r.outsideView.length.toLocaleString()} not in this view`);
  if (r.missing.length) parts.push(`${r.missing.length.toLocaleString()} not found`);
  if (r.duplicates) parts.push(`${r.duplicates.toLocaleString()} repeated in the file`);
  if (r.ambiguous)
    parts.push(`${r.ambiguous.toLocaleString()} matched more than one record`);
  return parts.join(" · ");
}