wellformed

import {
  isComputed,
  q,
  type CellValue,
  type ColumnMeta,
  type Reading,
  type StoredColumnType,
  type TightenPlan,
  type TightenProblem,
  type TightenProblemKind,
  type Tightening,
} from '@wellformed/shared';
import { badRequest, conflict, notFound } from '../errors.js';
import { invalidateMeta, transact, type Db } from '../db/driver.js';
import { findColumn, getTable, loadMeta, type TableSchema } from '../db/meta.js';
import { recordAudit, type Actor } from './audit.js';
import { FALSE_WORDS, TRUE_WORDS } from './import.js';
import { inferReading, readDate, readNumber } from './reading.js';
import { dropSqlViews } from './sql-views.js';
import { coerceForColumn, sqlTypeFor } from './values.js';

// The readers moved to `reading.ts` when the importer started using them too;
// re-exported so a caller of the tightening keeps finding them here.
export { readDate, readNumber } from './reading.js';

/**
 * Tightening a column.
 *
 * A spreadsheet arrives with the loosest schema a table can have. One `n/a` in
 * a thousand amounts makes the whole column text, and from then on nothing can
 * be summed, sorted or trusted. The importer is right to be timid — it is
 * guessing, at speed, about a file nobody has looked at yet — so the tightening
 * has to happen afterwards, when somebody is looking.
 *
 * That is what this module is: `ALTER TABLE` with a dry run in front of it.
 * `planTighten` reads the whole column, works out exactly which rows would not
 * fit the proposed constraint and says so by row and by value; `applyTighten`
 * does it, in one transaction, and refuses if anything the plan reported is
 * still outstanding. Nothing here guesses on the operator's behalf without
 * showing its guess first — how a number's decimals are marked and which end of
 * a slashed date the day is on are both offered as answers that can be
 * overruled, because neither can be read off a value on its own.
 *
 * The constraints themselves are ordinary SQL. A type is the affinity of a real
 * column, so changing one rebuilds it; unique is a real `UNIQUE INDEX`, so the
 * database refuses the second Acme Ltd rather than the server remembering to
 * look. Required is the exception and says so: SQLite cannot add `NOT NULL` to
 * a column in place, so it stays a check the server runs on every write — but
 * it is no longer a promise about the future only, because it cannot be turned
 * on until the rows already there keep it.
 */

/** Offenders shown per problem. Enough to recognise the pattern, not a report. */
const SAMPLES = 6;

/** Values are only offered as select options when there are this few of them. */
const MAX_OPTION_OFFER = 25;

/** Rewrites shown, so a conversion can be checked before it happens. */
const MAX_REWRITES = 6;

/** The scratch column a rebuild copies through. `_` puts it out of reach of any real name. */
const SCRATCH = '_wf_tighten';

/** The unique index behind `column.unique`. Named so it can be found again. */
export function uniqueIndexName(table: string, column: string): string {
  return `_uq_${table}_${column}`;
}

/** One row's worth of the column being tightened. */
interface Cell {
  id: string;
  /** The row's display value, so a problem reads as a row rather than an id. */
  label: string;
  /** What is stored now. */
  value: CellValue;
  /** How it reads now — for a link, the label of the row it points at. */
  text: string;
}

/** A value after conversion, or the reason it has none. */
type Read =
  | { ok: true; value: CellValue }
  | { ok: false; kind: TightenProblemKind; message: string };

// ---- reading what is there ------------------------------------------------

/**
 * The whole column, with each row's label beside it.
 *
 * Read in full rather than sampled: a dry run that says "probably fine" is not
 * a dry run. A link is read through to its target's display value, because that
 * is what the column looks like on screen and what it must still say if it is
 * converted to text.
 */
function readCells(db: Db, table: TableSchema, col: ColumnMeta): Cell[] {
  const label = q(table.display);
  const name = q(col.name);

  if (col.type === 'link' && col.target) {
    const target = loadMeta(db).tables[col.target];
    if (target) {
      const rows = db.sqlite
        .prepare<[], { id: string; lbl: CellValue; val: CellValue; txt: CellValue }>(
          `SELECT r."id" AS id, r.${label} AS lbl, r.${name} AS val, t.${q(target.display)} AS txt
             FROM ${q(table.name)} r
             LEFT JOIN ${q(col.target)} t ON t."id" = r.${name}`,
        )
        .all();
      return rows.map((r) => ({
        id: r.id,
        label: display(r.lbl, r.id),
        value: r.val,
        text: r.txt === null || r.txt === undefined ? '' : String(r.txt),
      }));
    }
  }

  const rows = db.sqlite
    .prepare<[], { id: string; lbl: CellValue; val: CellValue }>(
      `SELECT "id" AS id, ${label} AS lbl, ${name} AS val FROM ${q(table.name)}`,
    )
    .all();

  return rows.map((r) => ({
    id: r.id,
    label: display(r.lbl, r.id),
    value: r.val,
    text: asText(r.val, col),
  }));
}

function display(value: CellValue, fallback: string): string {
  if (value === null || value === undefined || value === '') return fallback;
  return String(value);
}

function asText(value: CellValue, col: ColumnMeta): string {
  if (value === null || value === undefined) return '';
  if (col.type === 'checkbox') return value ? 'true' : 'false';
  return String(value);
}

// ---- converting one cell --------------------------------------------------

/**
 * What one cell becomes under the proposed column, or why it cannot.
 *
 * The lenient part is here and only here: a migration somebody is watching may
 * read `€ 1.234,50` as a number, where an ordinary edit may not. Whatever comes
 * out is then handed to `coerceForColumn` — the same gate every edit passes
 * through — so this can never write a value the grid would later refuse.
 */
function readCell(
  db: Db,
  next: ColumnMeta,
  cell: Cell,
  reading: Required<Reading>,
  linkIndex: Map<string, string | null> | null,
): Read {
  const text = cell.text.trim();

  if (text === '') {
    if (next.required) {
      return { ok: false, kind: 'blank', message: `"${next.name}" would be required, and this row is blank.` };
    }
    return { ok: true, value: next.type === 'checkbox' ? 0 : null };
  }

  let normalized: unknown = text;

  switch (next.type) {
    case 'number':
    case 'currency': {
      const n = readNumber(text, reading.decimal);
      if (n === null) return { ok: false, kind: 'unreadable', message: 'not a number' };
      normalized = n;
      break;
    }

    case 'date': {
      const d = readDate(text, reading.dayFirst);
      if (d === null) return { ok: false, kind: 'unreadable', message: 'not a date' };
      normalized = d;
      break;
    }

    case 'checkbox': {
      const word = text.toLowerCase();
      if (TRUE_WORDS.has(word)) normalized = 1;
      else if (FALSE_WORDS.has(word)) normalized = 0;
      else return { ok: false, kind: 'unreadable', message: 'not a yes or a no' };
      break;
    }

    case 'select': {
      const options = next.options ?? [];
      // No list yet is a question, not a failure — the plan's job here is to
      // hand back the values so a list can be made out of them.
      if (options.length) {
        // Case and spacing are how the same answer gets typed twice; they
        // should not cost a row. Anything else the list does not have.
        const match = options.find((o) => o.toLowerCase() === text.toLowerCase());
        if (!match) return { ok: false, kind: 'unknown_option', message: 'not one of the options' };
        normalized = match;
      }
      break;
    }

    case 'link': {
      const hit = linkIndex?.get(text.toLowerCase());
      if (hit === undefined) {
        return { ok: false, kind: 'no_match', message: `no ${next.target} row is called this` };
      }
      if (hit === null) {
        return { ok: false, kind: 'no_match', message: `more than one ${next.target} row is called this` };
      }
      normalized = hit;
      break;
    }

    default:
      break;
  }

  try {
    return { ok: true, value: coerceForColumn(db, next, normalized) };
  } catch (error) {
    return { ok: false, kind: 'unreadable', message: error instanceof Error ? error.message : 'cannot be stored' };
  }
}

/**
 * The target table by display value, for matching text against it.
 *
 * A column of company names is a foreign key nobody got to declare, and this is
 * what turns it into one. Names that more than one row answers to map to null:
 * an ambiguous match is a wrong link, and a wrong link is worse than a refusal.
 * Row ids are accepted too, so a column that already holds them still converts.
 */
function buildLinkIndex(db: Db, targetName: string): Map<string, string | null> {
  const meta = loadMeta(db);
  const target = meta.tables[targetName];
  if (!target) throw notFound(`No table named "${targetName}" to link to.`);

  const index = new Map<string, string | null>();
  const rows = db.sqlite
    .prepare<[], { id: string; lbl: CellValue }>(
      `SELECT "id" AS id, ${q(target.display)} AS lbl FROM ${q(targetName)}`,
    )
    .all();

  for (const row of rows) {
    index.set(row.id.toLowerCase(), row.id);
    const label = row.lbl === null || row.lbl === undefined ? '' : String(row.lbl).trim().toLowerCase();
    if (!label) continue;
    index.set(label, index.has(label) ? null : row.id);
  }

  return index;
}

// ---- the plan -------------------------------------------------------------

/**
 * The column as it would be if the proposal were applied.
 *
 * `strict` is the difference between planning and applying. A half-finished
 * proposal — "make it a select", before any options have been named — is a
 * perfectly good question to ask the dry run, and the answer to it is the list
 * of values already in the column. Only the apply insists on a finished one.
 */
function proposedColumn(current: ColumnMeta, input: Tightening, strict: boolean): ColumnMeta {
  const type = (input.type ?? current.type) as StoredColumnType;
  const next: ColumnMeta = {
    ...current,
    type,
    required: input.required ?? current.required ?? false,
    unique: input.unique ?? current.unique ?? false,
  };

  if (type === 'select') {
    const options = (input.options ?? current.options ?? []).map((o) => o.trim()).filter(Boolean);
    if (!options.length && strict) throw badRequest('A select column needs at least one option.');
    if (options.length > 100) throw badRequest('A select column is limited to 100 options.');
    next.options = [...new Set(options)];
  } else {
    delete next.options;
  }

  if (type === 'link') {
    const target = input.target ?? current.target;
    if (!target) throw badRequest('A link column needs a target table.');
    next.target = target;
  } else {
    delete next.target;
  }

  // A type change starts the format over, so a date does not keep printing as
  // money because the column used to be money.
  if (input.type && input.type !== current.type) {
    next.format = null;
  }

  return next;
}

/** Everything a tightening is not allowed to be, checked before any data is read. */
function assertAllowed(table: TableSchema, current: ColumnMeta, next: ColumnMeta): void {
  if (isComputed(current.type)) {
    throw badRequest(`"${current.label}" is computed; its type is its expression.`);
  }
  if (isComputed(next.type)) {
    throw badRequest('A stored column cannot be turned into a formula. Add a formula column instead.');
  }
  if (next.type === 'link' && table.display === current.name) {
    throw badRequest(`"${current.label}" labels this table's rows, so it cannot become a link.`);
  }
  // A self-link is deliberately not refused here: `employees.Manager` pointing
  // at `employees` is a real shape, `addColumn` already allows it, and a
  // tightening that refused what a new column allows would be a second policy.
  if (next.unique && next.type === 'checkbox') {
    throw badRequest('A checkbox has two values, so at most two rows could exist. Unique does not apply.');
  }
}

/** The plan, and the value each row would end up with. */
interface Evaluation {
  current: ColumnMeta;
  next: ColumnMeta;
  plan: TightenPlan;
  /** One entry per row, in table order. `null` where the value would not convert. */
  values: { id: string; value: CellValue; ok: boolean }[];
}

/**
 * Read the column, convert every row, and count what happens.
 *
 * One pass answers both questions — what the plan says and what the apply
 * writes — so the two can never disagree about a row.
 */
function evaluate(
  db: Db,
  tableName: string,
  columnName: string,
  input: Tightening,
  strict: boolean,
): Evaluation {
  const meta = loadMeta(db);
  const table = getTable(meta, tableName);
  const current = findColumn(table, columnName);
  if (!current) throw notFound(`"${tableName}" has no column "${columnName}".`);

  const next = proposedColumn(current, input, strict);
  assertAllowed(table, current, next);

  const cells = readCells(db, table, current);
  const guessed = inferReading(cells.map((c) => c.text));
  const reading: Required<Reading> = {
    decimal: input.reading?.decimal ?? guessed.reading.decimal,
    dayFirst: input.reading?.dayFirst ?? guessed.reading.dayFirst,
  };

  const linkIndex = next.type === 'link' && next.target ? buildLinkIndex(db, next.target) : null;
  const converting = next.type !== current.type;

  const problems = new Map<string, TightenProblem>();
  const rewrites: TightenPlan['rewrites'] = [];
  const seen = new Map<string, string>();
  const duplicates: TightenProblem = {
    kind: 'duplicate',
    message: 'already used by another row',
    count: 0,
    samples: [],
    clearable: false,
  };

  const values: Evaluation['values'] = [];
  let fit = 0;

  for (const cell of cells) {
    const read = readCell(db, next, cell, reading, linkIndex);

    if (!read.ok) {
      note(problems, read.kind, read.message, cell);
      // Clearing is the only remedy this can offer, and a cleared checkbox is
      // unticked rather than absent — the column has no room for absent.
      values.push({ id: cell.id, value: next.type === 'checkbox' ? 0 : null, ok: false });
      continue;
    }

    if (next.unique && read.value !== null && read.value !== '') {
      // The same comparison SQLite is about to make: on the stored value, not
      // on the text it was written as.
      const key = String(read.value);
      if (seen.has(key)) {
        duplicates.count++;
        if (duplicates.samples.length < SAMPLES) {
          duplicates.samples.push({ id: cell.id, label: cell.label, value: cell.text });
        }
        values.push({ id: cell.id, value: read.value, ok: false });
        continue;
      }
      seen.set(key, cell.id);
    }

    // Not for a link: what it becomes is a row id, and `Acme Ltd → cus_7f2k`
    // tells a reader nothing they can check. There the meaning is which names
    // found a row and which did not, and the problems carry that.
    if (converting && next.type !== 'link' && rewrites.length < MAX_REWRITES && cell.text !== '') {
      const after = read.value === null ? '∅' : String(read.value);
      if (after !== cell.text) rewrites.push({ from: cell.text, to: after });
    }

    values.push({ id: cell.id, value: read.value, ok: true });
    fit++;
  }

  if (duplicates.count > 0) problems.set('duplicate', duplicates);

  return {
    current,
    next,
    values,
    plan: {
      total: cells.length,
      fit,
      problems: [...problems.values()],
      rewrites,
      distinct: distinctValues(cells),
      reading,
      ambiguousDates: guessed.ambiguousDates && next.type === 'date',
      sql: tightenSql(table, current, next, input.onProblem ?? 'stop'),
    },
  };
}

export function planTighten(
  db: Db,
  tableName: string,
  columnName: string,
  input: Tightening,
): TightenPlan {
  return evaluate(db, tableName, columnName, input, false).plan;
}

function note(
  problems: Map<string, TightenProblem>,
  kind: TightenProblemKind,
  message: string,
  cell: Cell,
): void {
  const key = `${kind}:${message}`;
  let problem = problems.get(key);
  if (!problem) {
    problem = {
      kind,
      message,
      count: 0,
      samples: [],
      // Emptying a cell settles a value that will not convert. It cannot settle
      // a blank one — that is what blank means — and it cannot settle a
      // duplicate without deciding which row loses its value.
      clearable: kind !== 'blank' && kind !== 'duplicate',
    };
    problems.set(key, problem);
  }
  problem.count++;
  if (problem.samples.length < SAMPLES) {
    problem.samples.push({ id: cell.id, label: cell.label, value: cell.text });
  }
}

/**
 * The distinct values, when they are a list of answers rather than a list of
 * values that happen to be short.
 *
 * The offer this exists for: a column with six answers in three thousand rows
 * is a select somebody had no way to declare. A column of eight invoice
 * amounts, each appearing once, is not — it is eight numbers, and offering to
 * freeze them into a dropdown would be this product misreading its own data at
 * the exact moment it claims to be reading it. So the values have to repeat:
 * on average twice over, which no column of distinct measurements ever does.
 */
function distinctValues(cells: Cell[]): TightenPlan['distinct'] {
  const counts = new Map<string, number>();
  let present = 0;
  for (const cell of cells) {
    const text = cell.text.trim();
    if (!text) continue;
    present++;
    counts.set(text, (counts.get(text) ?? 0) + 1);
    if (counts.size > MAX_OPTION_OFFER) return null;
  }
  if (counts.size === 0 || counts.size * 2 > present) return null;
  return [...counts.entries()]
    .map(([value, count]) => ({ value, count }))
    .sort((a, b) => b.count - a.count || a.value.localeCompare(b.value));
}

// ---- the SQL it comes to --------------------------------------------------

/**
 * The statements this would run.
 *
 * Shown for the same reason the grid shows its SELECT: this product's claim is
 * that there is a real database underneath, and a claim like that is worth
 * exactly as much as what it lets you read. The per-row copy is one prepared
 * statement run once per row, and is written that way rather than pretended
 * into a single UPDATE it is not.
 */
function tightenSql(
  table: TableSchema,
  current: ColumnMeta,
  next: ColumnMeta,
  onProblem: 'stop' | 'clear',
): string {
  const lines: string[] = [];
  const t = q(table.name);
  const name = q(current.name);

  if (current.unique) lines.push(`DROP INDEX ${q(uniqueIndexName(table.name, current.name))};`);

  if (next.type !== current.type || next.target !== current.target) {
    lines.push(
      `ALTER TABLE ${t} ADD COLUMN ${q(SCRATCH)} ${declarationFor(next)};`,
      `UPDATE ${t} SET ${q(SCRATCH)} = ? WHERE "id" = ?;  -- once per row${
        onProblem === 'clear' ? ', NULL where it would not convert' : ''
      }`,
      `ALTER TABLE ${t} DROP COLUMN ${name};`,
      `ALTER TABLE ${t} RENAME COLUMN ${q(SCRATCH)} TO ${name};`,
    );
  } else if (onProblem === 'clear') {
    lines.push(`UPDATE ${t} SET ${name} = NULL WHERE "id" = ?;  -- once per row that will not convert`);
  }

  if (next.unique) {
    lines.push(`CREATE UNIQUE INDEX ${q(uniqueIndexName(table.name, current.name))} ON ${t} (${name});`);
  }

  if (next.required !== (current.required ?? false)) {
    // Said plainly rather than shown as SQL that will not be run: SQLite cannot
    // add NOT NULL to a column in place, and pretending otherwise here would
    // undo the only reason to print any of this.
    lines.push(
      next.required
        ? `-- ${current.name} becomes required: checked by the server on every write, because SQLite cannot add NOT NULL in place.`
        : `-- ${current.name} stops being required.`,
    );
  }

  return lines.length ? lines.join('\n') : '-- Nothing to do.';
}

function declarationFor(col: ColumnMeta): string {
  if (col.type === 'link' && col.target) {
    return `TEXT REFERENCES ${q(col.target)}("id") ON DELETE SET NULL`;
  }
  // Exactly what `addColumn` would have declared, so a column that was
  // tightened into a type is indistinguishable from one created as it.
  return sqlTypeFor(col.type);
}

// ---- doing it -------------------------------------------------------------

export interface TightenResult {
  column: ColumnMeta;
  plan: TightenPlan;
  /** Rows whose value was emptied because it would not convert. */
  cleared: number;
}

export function applyTighten(
  db: Db,
  tableName: string,
  columnName: string,
  input: Tightening,
  actor: Actor,
): TightenResult {
  const { current, next, plan, values } = evaluate(db, tableName, columnName, input, true);
  const onProblem = input.onProblem ?? 'stop';

  // Refused on the plan rather than on the first failing row, so the answer is
  // the whole truth about the column and not the first thing that broke.
  const blocking = plan.problems.filter((p) => onProblem === 'stop' || !p.clearable);
  if (blocking.length) {
    throw conflict(
      `${blocking.reduce((n, p) => n + p.count, 0)} of ${plan.total} rows do not fit: ${blocking
        .map((p) => `${p.count} ${p.message}`)
        .join('; ')}.`,
    );
  }

  const rebuilding = next.type !== current.type || next.target !== current.target;
  const cleared = values.filter((v) => !v.ok).length;

  if (
    !rebuilding &&
    !cleared &&
    (next.required ?? false) === (current.required ?? false) &&
    (next.unique ?? false) === (current.unique ?? false) &&
    sameOptions(current.options, next.options)
  ) {
    return { column: current, plan, cleared: 0 };
  }

  transact(db, () => {
    /**
     * The derived `v_*` views select this column by name, and SQLite refuses to
     * drop a column any other part of the schema mentions. They are rebuilt
     * from the catalogue on demand, so dropping them here costs nothing and is
     * the difference between this working and failing on a workspace an
     * assistant has read from.
     */
    dropSqlViews(db);

    if (current.unique) {
      db.sqlite.exec(`DROP INDEX IF EXISTS ${q(uniqueIndexName(tableName, columnName))}`);
    }

    if (rebuilding) {
      db.sqlite.exec(`ALTER TABLE ${q(tableName)} ADD COLUMN ${q(SCRATCH)} ${declarationFor(next)}`);
      const write = db.sqlite.prepare(
        `UPDATE ${q(tableName)} SET ${q(SCRATCH)} = ? WHERE "id" = ?`,
      );
      for (const row of values) write.run(row.value, row.id);
      db.sqlite.exec(`ALTER TABLE ${q(tableName)} DROP COLUMN ${q(columnName)}`);
      db.sqlite.exec(
        `ALTER TABLE ${q(tableName)} RENAME COLUMN ${q(SCRATCH)} TO ${q(columnName)}`,
      );
    } else if (cleared > 0) {
      const write = db.sqlite.prepare(`UPDATE ${q(tableName)} SET ${q(columnName)} = ? WHERE "id" = ?`);
      for (const row of values) if (!row.ok) write.run(row.value, row.id);
    }

    if (next.unique) {
      db.sqlite.exec(
        `CREATE UNIQUE INDEX ${q(uniqueIndexName(tableName, columnName))} ON ${q(tableName)} (${q(columnName)})`,
      );
    }

    db.sqlite
      .prepare(
        `UPDATE _wellformed_column SET type = ?, options = ?, target_table = ?, format = ?, required = ?, uniq = ?, sequence = ?
           WHERE table_name = ? AND name = ?`,
      )
      .run(
        next.type,
        next.options ? JSON.stringify(next.options) : null,
        next.target ?? null,
        next.format ?? null,
        next.required ? 1 : 0,
        next.unique ? 1 : 0,
        // Numbering writes text; a column converted to anything else sheds it.
        next.type === 'text' ? (current.sequence ?? null) : null,
        tableName,
        columnName,
      );

    // A view that filtered on "Amount = 1,200" was filtering on text; once the
    // column is a number that filter matches nothing and looks like data loss.
    // The filter goes rather than the rows, and the audit line says so.
    const strandedFilters = rebuilding ? stripFilters(db, tableName, columnName) : 0;

    recordAudit(db, actor, {
      kind: 'schema',
      table: tableName,
      column: columnName,
      old: describe(current),
      nw:
        describe(next) +
        (cleared ? ` — cleared ${cleared} value${cleared === 1 ? '' : 's'}` : '') +
        (strandedFilters ? ` — dropped ${strandedFilters} stranded filter${strandedFilters === 1 ? '' : 's'}` : ''),
    });
  });

  invalidateMeta(db);

  const updated = findColumn(getTable(loadMeta(db), tableName), columnName);
  if (!updated) throw notFound(`Column "${columnName}" disappeared while it was being tightened.`);
  return { column: updated, plan, cleared };
}

function sameOptions(a: string[] | undefined, b: string[] | undefined): boolean {
  if (!a && !b) return true;
  if (!a || !b) return false;
  return a.length === b.length && a.every((v, i) => v === b[i]);
}

/** Filters that were written against the old type, and now match nothing. */
function stripFilters(db: Db, tableName: string, columnName: string): number {
  const views = loadMeta(db).views.filter(
    (v) => v.table === tableName && v.filters.some((f) => f.col === columnName),
  );
  let dropped = 0;
  for (const view of views) {
    const kept = view.filters.filter((f) => f.col !== columnName);
    dropped += view.filters.length - kept.length;
    db.sqlite.prepare('UPDATE _wellformed_view SET filters = ? WHERE id = ?').run(JSON.stringify(kept), view.id);
  }
  return dropped;
}

/** How a column's constraints read in the audit trail. */
function describe(col: ColumnMeta): string {
  const parts = [col.type === 'link' ? `link → ${col.target}` : col.type];
  if (col.type === 'select' && col.options) parts.push(`(${col.options.join(' | ')})`);
  if (col.required) parts.push('required');
  if (col.unique) parts.push('unique');
  return parts.join(' ');
}