import {
  isComputed,
  q,
  type ColumnMeta,
  type StoredColumnType,
  type TightenWalkPlan,
  type TightenWalkStep,
} from '@wellformed/shared';
import { type Db } from '../db/driver.js';
import { getTable, loadMeta } from '../db/meta.js';
import { inferColumn } from './import.js';
import {
  clustersFrom,
  groupSpellings,
  MAX_VALUE_LENGTH,
  rankSpellings,
  repeats,
  scannable,
} from './normalize.js';

/**
 * The tighten walk: the regie the operations never had.
 *
 * Tightening has always had the right moves — convert a type, bind a select,
 * point a link — but finding them meant opening the right dialog on the right
 * column and knowing what to ask. This walks instead: every stored column
 * once, each answered with what its values say it is. A column of `12-3-2021`s
 * reads as a date; a column of `€ 1.234,50`s reads as money; a column of five
 * answers spread over three thousand rows is a select nobody declared, and if
 * two of those answers are spelled three ways, the merge comes first. The
 * detection is the importer's own (`inferColumn`), so what the walk proposes
 * for an old column is exactly what a fresh import would have guessed for it —
 * and it only speaks when every present value fits, so a proposal here can
 * never cost a row.
 *
 * Read-only. Each step is carried out by the endpoints that already exist —
 * normalize for the merges, tighten for the type — one column at a time, the
 * person deciding at every stop.
 */

/** Options offered for a select, mirroring the tighten plan's own ceiling. */
const MAX_SELECT_OPTIONS = 25;

export function planWalk(db: Db, tableName: string): TightenWalkPlan {
  const table = getTable(loadMeta(db), tableName);
  const stored = table.columns.filter((c) => !isComputed(c.type));

  const steps: TightenWalkStep[] = [];
  for (const col of stored) {
    // Columns already typed — number, date, link… — hold values the grid
    // writes one way; there is nothing left to detect in them.
    if (col.type !== 'text' && col.type !== 'select') continue;

    const values = db.sqlite
      .prepare<[], { value: unknown }>(`SELECT ${q(col.name)} AS value FROM ${q(tableName)}`)
      .all()
      .map((r) => (r.value === null || r.value === undefined ? '' : String(r.value)));
    const total = values.length;
    const present = values.filter((v) => v.trim() !== '').length;
    if (present === 0) continue;

    // Prose is left alone entirely — the whole column, options included.
    if (
      values.some((v) => v.length > MAX_VALUE_LENGTH) ||
      (col.options ?? []).some((o) => o.length > MAX_VALUE_LENGTH)
    ) {
      continue;
    }

    // What the values read as, through the importer's own guess — made only
    // when every present value fits, so applying it can never cost a row.
    if (col.type === 'text') {
      const inferred = inferColumn(values).type;
      if (inferred !== 'text') {
        steps.push({
          column: col.name,
          convert: inferred as StoredColumnType,
          samples: [...new Set(values.filter((v) => v.trim() !== ''))].slice(0, 6),
          clusters: [],
          select: null,
          present,
          total,
        });
        continue;
      }
    }

    // From here it is about spellings, which unique columns sit out.
    if (!scannable(col)) continue;

    const spellings = groupSpellings(
      values.filter((v) => v.trim() !== '').map((value) => ({ value, count: 1 })),
      col.type === 'select' ? (col.options ?? []) : [],
    );
    if (col.type === 'text' && !repeats(spellings)) continue;

    const clusters = clustersFrom(spellings);

    // The payoff a text column is walking toward: its merged spellings as the
    // options of a real select. Projected past the merges, so what is offered
    // is what the column will hold once they are applied.
    const select =
      col.type === 'text' &&
      spellings.groups.size >= 2 &&
      spellings.groups.size <= MAX_SELECT_OPTIONS
        ? [...spellings.groups.values()]
            .map((group) => ({
              value: rankSpellings(group)[0]!.value,
              count: [...group.values()].reduce((n, c) => n + c, 0),
            }))
            .sort((a, b) => b.count - a.count || a.value.localeCompare(b.value))
        : null;

    if (clusters.length || select) {
      steps.push({ column: col.name, convert: null, samples: [], clusters, select, present, total });
    }
  }

  return { steps, scanned: stored.length };
}