import { isComputed, slugifyColumnName, type Filter, type Locale, type Sort } from '@wellformed/shared';
import { recordAudit, SYSTEM_ACTOR } from '../engine/audit.js';
import { newId } from '../engine/ids.js';
import {
  addColumn,
  createTable,
  defaultViewName,
  reorderColumns,
  updateColumn,
  type AddColumnInput,
} from '../engine/schema.js';
import { insertRow } from '../engine/mutate.js';
import { invalidateMeta, transact, type Db } from './driver.js';
import { loadMeta } from './meta.js';

/**
 * The demo: one small business, built whole.
 *
 * Where there used to be four templates to choose between, there is one
 * workspace to look at — a bike workshop selling, stocking, talking and
 * billing, shelved under the sidebar groups (Sales, Inventory, Relations,
 * Projects, Team) and deliberately linked *across* them: orders read prices
 * from products, stock movements move the same products, projects belong to
 * customers, hours multiply by the project's rate. The groups organise the
 * sidebar; the relations ignore them — which is exactly the point being made.
 *
 * Pure data, applied through the same functions the API uses. No demo may
 * contain code, because a demo with code in it is a vertical plugin wearing a
 * hat — and those are the thing this product has decided not to have.
 *
 * Built in the workspace's language. A workspace is full of names in one
 * language — see the languages note in the README — so a Dutch workspace gets
 * Dutch tables, columns, options and rows, not an English demo with a Dutch
 * menu around it. Formulas and views are assembled from the same localized
 * names, so both editions compile identically.
 */

/** Build the whole demo, or only its tables, views and rules. */
export type DemoMode = 'structure' | 'full';

/** What a workspace-creation request may ask for. */
export type DemoChoice = 'none' | DemoMode;

interface DemoTable {
  name: string;
  label: string;
  /** The label of the column that labels a row. `createTable` makes it. */
  display: string;
  /** The sidebar shelf this table goes on. */
  group: string;
  /** Make the display column mandatory, for rows that are worthless unlabelled. */
  requireDisplay?: boolean;
  /** A numbering pattern for the display column: it fills itself on insert. */
  displaySequence?: string;
  columns: AddColumnInput[];
  rows: (Record<string, unknown> & { id: string })[];
}

interface DemoSpec {
  tables: DemoTable[];
  /**
   * The sidebar order, which is not the build order. Tables are built
   * masters-first because a link needs its target to exist, but the first
   * table in the sidebar is the first thing a visitor sees — and a visitor
   * shown three employees learns nothing this product claims. Orders leads:
   * links, a formula, a workflow rule and a saved view, all on one screen.
   */
  order: string[];
  /**
   * Columns added once every table exists — links pointing forwards and the
   * rollups that read back across them. Ordering, not a second kind of column.
   */
  lateColumns: { table: string; column: AddColumnInput }[];
  views: {
    table: string;
    name: string;
    filters: Filter[];
    sort: Sort | null;
    /** Columns this view leaves out of its SELECT. */
    hidden?: string[];
  }[];
  rules: {
    table: string;
    column: string;
    equals: string;
    label: string;
    actions: { type: 'set'; column: string; value: string }[];
  }[];
}

// eslint-disable-next-line complexity -- one long literal, not logic
function demoSpec(locale: Locale): DemoSpec {
  const L = (en: string, nl: string) => (locale === 'nl' ? nl : en);

  /* Columns are defined by their label; rows, formulas, filters and rules
     address them by identifier. `n` is the same derivation the server applies
     when the column is made, so the two can never disagree. */
  const n = slugifyColumnName;

  /* Every name that appears twice — in a definition and in a formula, filter
     or rule — is named exactly once here, so the two can never disagree. */
  const T = {
    employees: L('employees', 'medewerkers'),
    customers: L('customers', 'klanten'),
    products: L('products', 'producten'),
    orders: 'orders',
    suppliers: L('suppliers', 'leveranciers'),
    movements: L('movements', 'mutaties'),
    contacts: L('contacts', 'contactpersonen'),
    conversations: L('conversations', 'gesprekken'),
    projects: L('projects', 'projecten'),
    tasks: L('tasks', 'taken'),
    hours: L('hours', 'uren'),
  };

  const GROUP = {
    sales: L('Sales', 'Verkoop'),
    inventory: L('Inventory', 'Voorraad'),
    relations: L('Relations', 'Relaties'),
    projects: L('Projects', 'Projecten'),
    team: L('Team', 'Team'),
  };

  const NAME = L('Name', 'Naam');
  const DATE = L('Date', 'Datum');
  const QTY = L('Qty', 'Aantal');
  const KIND = L('Kind', 'Soort');
  const STATUS = 'Status';
  const PRICE = L('Price', 'Prijs');
  const TOTAL = L('Total', 'Totaal');
  const RATE = L('Hourly rate', 'Uurtarief');
  const HOURS = L('Hours', 'Uren');
  const AMOUNT = L('Amount', 'Bedrag');
  const ON_HAND = L('On hand', 'Op voorraad');
  const CUSTOMER = L('Customer', 'Klant');
  const PRODUCT = L('Product', 'Product');
  const PROJECT = 'Project';
  const PAID_ON = L('Paid on', 'Betaald op');
  const DONE_ON = L('Done on', 'Klaar op');

  const PAID = L('Paid', 'Betaald');
  const DONE = L('Done', 'Klaar');

  return {
    order: [
      T.orders,
      T.customers,
      T.products,
      T.movements,
      T.suppliers,
      T.contacts,
      T.conversations,
      T.projects,
      T.tasks,
      T.hours,
      T.employees,
    ],
    tables: [
      {
        name: T.employees,
        label: L('Employees', 'Medewerkers'),
        display: NAME,
        group: GROUP.team,
        columns: [
          {
            label: L('Role', 'Rol'),
            type: 'select',
            options: [L('Sales', 'Verkoop'), L('Workshop', 'Werkplaats'), L('Finance', 'Financiën')],
          },
          { label: L('Started on', 'In dienst sinds'), type: 'date' },
        ],
        rows: [
          { id: 'e1', [n(NAME)]: 'Mara Ilić', [n(L('Role', 'Rol'))]: L('Sales', 'Verkoop'), [n(L('Started on', 'In dienst sinds'))]: '2023-04-12' },
          { id: 'e2', [n(NAME)]: 'Ben Okafor', [n(L('Role', 'Rol'))]: L('Workshop', 'Werkplaats'), [n(L('Started on', 'In dienst sinds'))]: '2024-01-08' },
          { id: 'e3', [n(NAME)]: 'Sofia Reyes', [n(L('Role', 'Rol'))]: L('Finance', 'Financiën'), [n(L('Started on', 'In dienst sinds'))]: '2022-09-01' },
        ],
      },
      {
        name: T.customers,
        label: L('Customers', 'Klanten'),
        display: NAME,
        group: GROUP.sales,
        columns: [
          {
            label: L('Region', 'Regio'),
            type: 'select',
            options: [L('North', 'Noord'), L('East', 'Oost'), L('South', 'Zuid'), L('West', 'West')],
          },
          { label: L('Email', 'E-mail'), type: 'text' },
        ],
        rows: [
          { id: 'c1', [n(NAME)]: 'Van Dijk & ' + L('Sons', 'Zonen'), [n(L('Region', 'Regio'))]: L('East', 'Oost'), [n(L('Email', 'E-mail'))]: 'info@vandijk.example' },
          { id: 'c2', [n(NAME)]: L('Hotel Meridian', 'Hotel Meridiaan'), [n(L('Region', 'Regio'))]: 'West', [n(L('Email', 'E-mail'))]: 'facility@meridiaan.example' },
          { id: 'c3', [n(NAME)]: 'Pronto ' + L('Deliveries', 'Bezorgdienst'), [n(L('Region', 'Regio'))]: L('South', 'Zuid'), [n(L('Email', 'E-mail'))]: 'fleet@pronto.example' },
          { id: 'c4', [n(NAME)]: L('Greenland Nursery', 'Kwekerij Groenland'), [n(L('Region', 'Regio'))]: L('North', 'Noord'), [n(L('Email', 'E-mail'))]: 'ines@groenland.example' },
        ],
      },
      {
        // Before products in build order: product rows link to supplier rows,
        // and a row can only point at one that already exists.
        name: T.suppliers,
        label: L('Suppliers', 'Leveranciers'),
        display: NAME,
        group: GROUP.inventory,
        columns: [
          { label: 'Contact', type: 'text' },
          { label: L('Email', 'E-mail'), type: 'text' },
          { label: L('Lead time (days)', 'Levertijd (dagen)'), type: 'number' },
        ],
        rows: [
          { id: 's1', [n(NAME)]: 'Rhein Cycleparts', contact: 'Dana Fenwick', [n(L('Email', 'E-mail'))]: 'orders@rhein.example', [n(L('Lead time (days)', 'Levertijd (dagen)'))]: 14 },
          { id: 's2', [n(NAME)]: 'Batavia Frames', contact: 'Iris Vos', [n(L('Email', 'E-mail'))]: 'sales@batavia.example', [n(L('Lead time (days)', 'Levertijd (dagen)'))]: 21 },
          { id: 's3', [n(NAME)]: 'NoordAccu', contact: 'Tom Oster', [n(L('Email', 'E-mail'))]: 'hello@noordaccu.example', [n(L('Lead time (days)', 'Levertijd (dagen)'))]: 7 },
        ],
      },
      {
        name: T.products,
        label: L('Products', 'Producten'),
        display: NAME,
        group: GROUP.sales,
        columns: [
          // Unique: two products with one SKU is the mistake this exists to refuse.
          { label: 'SKU', type: 'text', unique: true },
          { label: PRICE, type: 'currency' },
        ],
        // The supplier link is a late column (suppliers are created after
        // products), but rows are inserted later still, so they may carry it.
        rows: [
          { id: 'p1', [n(NAME)]: L('Cargo bike', 'Bakfiets'), sku: 'CB-100', [n(PRICE)]: 3450, [n(L('Supplier', 'Leverancier'))]: 's2' },
          { id: 'p2', [n(NAME)]: L('City e-bike', 'Stads-e-bike'), sku: 'EB-210', [n(PRICE)]: 2190, [n(L('Supplier', 'Leverancier'))]: 's2' },
          { id: 'p3', [n(NAME)]: L('Child seat', 'Kinderzitje'), sku: 'AC-031', [n(PRICE)]: 89, [n(L('Supplier', 'Leverancier'))]: 's1' },
          { id: 'p4', [n(NAME)]: L('Spare battery', 'Reserveaccu'), sku: 'AC-115', [n(PRICE)]: 395, [n(L('Supplier', 'Leverancier'))]: 's3' },
          { id: 'p5', [n(NAME)]: L('Service visit', 'Servicebeurt'), sku: 'SV-001', [n(PRICE)]: 95 },
        ],
      },
      {
        name: T.orders,
        label: 'Orders',
        display: L('Number', 'Nummer'),
        group: GROUP.sales,
        requireDisplay: true,
        // Self-numbering: a new order names itself, continuing from the
        // highest number already in the column.
        displaySequence: '#{nnnn}',
        columns: [
          { label: CUSTOMER, type: 'link', target: T.customers },
          { label: PRODUCT, type: 'link', target: T.products },
          { label: QTY, type: 'number' },
          { label: TOTAL, type: 'formula', expr: `${n(QTY)} * ${n(PRODUCT)}.${n(PRICE)}`, format: 'currency' },
          { label: L('Owner', 'Verkoper'), type: 'link', target: T.employees },
          {
            label: STATUS,
            type: 'select',
            options: [L('Draft', 'Concept'), L('Invoiced', 'Gefactureerd'), PAID, L('Overdue', 'Achterstallig')],
          },
          { label: DATE, type: 'date' },
          { label: PAID_ON, type: 'date' },
        ],
        rows: [
          { id: 'o1', [n(L('Number', 'Nummer'))]: '#1041', [n(CUSTOMER)]: 'c1', [n(PRODUCT)]: 'p1', [n(QTY)]: 1, [n(L('Owner', 'Verkoper'))]: 'e1', [n(STATUS)]: PAID, [n(DATE)]: '2026-06-12', [n(PAID_ON)]: '2026-06-30' },
          { id: 'o2', [n(L('Number', 'Nummer'))]: '#1042', [n(CUSTOMER)]: 'c2', [n(PRODUCT)]: 'p2', [n(QTY)]: 2, [n(L('Owner', 'Verkoper'))]: 'e1', [n(STATUS)]: L('Invoiced', 'Gefactureerd'), [n(DATE)]: '2026-07-08' },
          { id: 'o3', [n(L('Number', 'Nummer'))]: '#1043', [n(CUSTOMER)]: 'c3', [n(PRODUCT)]: 'p1', [n(QTY)]: 2, [n(L('Owner', 'Verkoper'))]: 'e2', [n(STATUS)]: L('Overdue', 'Achterstallig'), [n(DATE)]: '2026-06-20' },
          { id: 'o4', [n(L('Number', 'Nummer'))]: '#1044', [n(CUSTOMER)]: 'c1', [n(PRODUCT)]: 'p3', [n(QTY)]: 2, [n(L('Owner', 'Verkoper'))]: 'e2', [n(STATUS)]: PAID, [n(DATE)]: '2026-07-15', [n(PAID_ON)]: '2026-07-28' },
          { id: 'o5', [n(L('Number', 'Nummer'))]: '#1045', [n(CUSTOMER)]: 'c4', [n(PRODUCT)]: 'p4', [n(QTY)]: 1, [n(L('Owner', 'Verkoper'))]: 'e1', [n(STATUS)]: L('Draft', 'Concept'), [n(DATE)]: '2026-08-05' },
          { id: 'o6', [n(L('Number', 'Nummer'))]: '#1046', [n(CUSTOMER)]: 'c2', [n(PRODUCT)]: 'p5', [n(QTY)]: 3, [n(L('Owner', 'Verkoper'))]: 'e2', [n(STATUS)]: L('Invoiced', 'Gefactureerd'), [n(DATE)]: '2026-08-12' },
        ],
      },
      {
        name: T.movements,
        label: L('Stock movements', 'Voorraadmutaties'),
        display: 'Ref',
        group: GROUP.inventory,
        columns: [
          { label: PRODUCT, type: 'link', target: T.products },
          { label: QTY, type: 'number' },
          {
            label: KIND,
            type: 'select',
            options: [L('Received', 'Ontvangen'), L('Sold', 'Verkocht'), L('Adjusted', 'Gecorrigeerd')],
          },
          { label: DATE, type: 'date' },
        ],
        rows: [
          { id: 'm1', ref: 'IN-001', [n(PRODUCT)]: 'p1', [n(QTY)]: 4, [n(KIND)]: L('Received', 'Ontvangen'), [n(DATE)]: '2026-06-02' },
          { id: 'm2', ref: 'OUT-001', [n(PRODUCT)]: 'p1', [n(QTY)]: -3, [n(KIND)]: L('Sold', 'Verkocht'), [n(DATE)]: '2026-07-01' },
          { id: 'm3', ref: 'IN-002', [n(PRODUCT)]: 'p2', [n(QTY)]: 6, [n(KIND)]: L('Received', 'Ontvangen'), [n(DATE)]: '2026-06-15' },
          { id: 'm4', ref: 'OUT-002', [n(PRODUCT)]: 'p2', [n(QTY)]: -2, [n(KIND)]: L('Sold', 'Verkocht'), [n(DATE)]: '2026-07-10' },
          { id: 'm5', ref: 'IN-003', [n(PRODUCT)]: 'p4', [n(QTY)]: 10, [n(KIND)]: L('Received', 'Ontvangen'), [n(DATE)]: '2026-07-03' },
          { id: 'm6', ref: 'IN-004', [n(PRODUCT)]: 'p3', [n(QTY)]: 8, [n(KIND)]: L('Received', 'Ontvangen'), [n(DATE)]: '2026-06-20' },
          { id: 'm7', ref: 'ADJ-001', [n(PRODUCT)]: 'p3', [n(QTY)]: -1, [n(KIND)]: L('Adjusted', 'Gecorrigeerd'), [n(DATE)]: '2026-07-25' },
        ],
      },
      {
        name: T.contacts,
        label: L('Contacts', 'Contactpersonen'),
        display: NAME,
        group: GROUP.relations,
        columns: [
          { label: CUSTOMER, type: 'link', target: T.customers },
          { label: L('Role', 'Functie'), type: 'text' },
          { label: L('Email', 'E-mail'), type: 'text' },
          { label: L('Phone', 'Telefoon'), type: 'text' },
        ],
        rows: [
          { id: 'ct1', [n(NAME)]: 'Joris van Dijk', [n(CUSTOMER)]: 'c1', [n(L('Role', 'Functie'))]: L('Owner', 'Eigenaar'), [n(L('Email', 'E-mail'))]: 'joris@vandijk.example', [n(L('Phone', 'Telefoon'))]: '+31 20 555 0141' },
          { id: 'ct2', [n(NAME)]: 'Femke Sanders', [n(CUSTOMER)]: 'c2', [n(L('Role', 'Functie'))]: L('Facility manager', 'Facilitair manager'), [n(L('Email', 'E-mail'))]: 'femke@meridiaan.example' },
          { id: 'ct3', [n(NAME)]: 'Marco Bruni', [n(CUSTOMER)]: 'c3', [n(L('Role', 'Functie'))]: L('Fleet lead', 'Wagenparkbeheerder'), [n(L('Email', 'E-mail'))]: 'marco@pronto.example' },
          { id: 'ct4', [n(NAME)]: 'Ines Groen', [n(CUSTOMER)]: 'c4', [n(L('Role', 'Functie'))]: L('Owner', 'Eigenaar'), [n(L('Email', 'E-mail'))]: 'ines@groenland.example' },
        ],
      },
      {
        name: T.conversations,
        label: L('Conversations', 'Gesprekken'),
        display: L('Subject', 'Onderwerp'),
        group: GROUP.relations,
        columns: [
          { label: 'Contact', type: 'link', target: T.contacts },
          { label: CUSTOMER, type: 'link', target: T.customers },
          {
            label: KIND,
            type: 'select',
            options: [L('Call', 'Telefoongesprek'), L('Email', 'E-mail'), L('Meeting', 'Afspraak')],
          },
          { label: DATE, type: 'date' },
          { label: L('Notes', 'Notities'), type: 'text' },
        ],
        rows: [
          { id: 'g1', [n(L('Subject', 'Onderwerp'))]: L('Quote for two cargo bikes', 'Offerte voor twee bakfietsen'), contact: 'ct1', [n(CUSTOMER)]: 'c1', [n(KIND)]: L('Call', 'Telefoongesprek'), [n(DATE)]: '2026-06-05', [n(L('Notes', 'Notities'))]: L('Wants them before the summer rush.', 'Wil ze vóór de zomerdrukte binnen hebben.') },
          { id: 'g2', [n(L('Subject', 'Onderwerp'))]: L('Winter service plan', 'Winteronderhoudsplan'), contact: 'ct2', [n(CUSTOMER)]: 'c2', [n(KIND)]: L('Meeting', 'Afspraak'), [n(DATE)]: '2026-07-14' },
          { id: 'g3', [n(L('Subject', 'Onderwerp'))]: L('Battery range questions', 'Vragen over accubereik'), contact: 'ct3', [n(CUSTOMER)]: 'c3', [n(KIND)]: L('Email', 'E-mail'), [n(DATE)]: '2026-07-22' },
          { id: 'g4', [n(L('Subject', 'Onderwerp'))]: L('Second child seat', 'Tweede kinderzitje'), contact: 'ct1', [n(CUSTOMER)]: 'c1', [n(KIND)]: L('Email', 'E-mail'), [n(DATE)]: '2026-08-02' },
        ],
      },
      {
        name: T.projects,
        label: L('Projects', 'Projecten'),
        display: NAME,
        group: GROUP.projects,
        columns: [
          { label: CUSTOMER, type: 'link', target: T.customers },
          { label: RATE, type: 'currency' },
          {
            label: STATUS,
            type: 'select',
            options: [L('Quoted', 'Offerte'), L('Running', 'Lopend'), L('Done', 'Afgerond')],
          },
          { label: L('Starts', 'Start'), type: 'date' },
        ],
        rows: [
          { id: 'pr1', [n(NAME)]: L('Pronto fleet rollout', 'Wagenpark Pronto'), [n(CUSTOMER)]: 'c3', [n(RATE)]: 95, [n(STATUS)]: L('Running', 'Lopend'), [n(L('Starts', 'Start'))]: '2026-07-01' },
          { id: 'pr2', [n(NAME)]: L('Hotel bike programme', 'Hotelfietsprogramma'), [n(CUSTOMER)]: 'c2', [n(RATE)]: 85, [n(STATUS)]: L('Quoted', 'Offerte'), [n(L('Starts', 'Start'))]: '2026-09-01' },
          { id: 'pr3', [n(NAME)]: L('Bakery delivery setup', 'Bezorgopzet Van Dijk'), [n(CUSTOMER)]: 'c1', [n(RATE)]: 95, [n(STATUS)]: L('Done', 'Afgerond'), [n(L('Starts', 'Start'))]: '2026-05-01' },
        ],
      },
      {
        name: T.tasks,
        label: L('Tasks', 'Taken'),
        display: L('Title', 'Titel'),
        group: GROUP.projects,
        columns: [
          { label: PROJECT, type: 'link', target: T.projects },
          { label: L('Owner', 'Eigenaar'), type: 'link', target: T.employees },
          {
            label: STATUS,
            type: 'select',
            options: [L('To do', 'Te doen'), L('Doing', 'Bezig'), DONE],
          },
          { label: L('Due', 'Deadline'), type: 'date' },
          { label: DONE_ON, type: 'date' },
        ],
        rows: [
          { id: 'tk1', [n(L('Title', 'Titel'))]: L('Survey the routes', 'Routes inventariseren'), [n(PROJECT)]: 'pr1', [n(L('Owner', 'Eigenaar'))]: 'e1', [n(STATUS)]: DONE, [n(L('Due', 'Deadline'))]: '2026-07-10', [n(DONE_ON)]: '2026-07-08' },
          { id: 'tk2', [n(L('Title', 'Titel'))]: L('Fit the trailers', 'Aanhangers monteren'), [n(PROJECT)]: 'pr1', [n(L('Owner', 'Eigenaar'))]: 'e2', [n(STATUS)]: L('Doing', 'Bezig'), [n(L('Due', 'Deadline'))]: '2026-08-20' },
          { id: 'tk3', [n(L('Title', 'Titel'))]: L('Rider training', 'Rijderstraining'), [n(PROJECT)]: 'pr1', [n(L('Owner', 'Eigenaar'))]: 'e1', [n(STATUS)]: L('To do', 'Te doen'), [n(L('Due', 'Deadline'))]: '2026-09-05' },
          { id: 'tk4', [n(L('Title', 'Titel'))]: L('Draft the proposal', 'Voorstel schrijven'), [n(PROJECT)]: 'pr2', [n(L('Owner', 'Eigenaar'))]: 'e1', [n(STATUS)]: L('Doing', 'Bezig'), [n(L('Due', 'Deadline'))]: '2026-08-15' },
          { id: 'tk5', [n(L('Title', 'Titel'))]: L('Handover', 'Oplevering'), [n(PROJECT)]: 'pr3', [n(L('Owner', 'Eigenaar'))]: 'e2', [n(STATUS)]: DONE, [n(L('Due', 'Deadline'))]: '2026-05-28', [n(DONE_ON)]: '2026-05-26' },
        ],
      },
      {
        name: T.hours,
        label: L('Hours', 'Uren'),
        display: 'Ref',
        group: GROUP.projects,
        columns: [
          { label: L('Task', 'Taak'), type: 'link', target: T.tasks },
          { label: PROJECT, type: 'link', target: T.projects },
          { label: L('Person', 'Persoon'), type: 'link', target: T.employees },
          { label: HOURS, type: 'number' },
          { label: AMOUNT, type: 'formula', expr: `${n(HOURS)} * ${n(PROJECT)}.${n(RATE)}`, format: 'currency' },
          { label: DATE, type: 'date' },
        ],
        rows: [
          { id: 'h1', ref: L('H-001', 'U-001'), [n(L('Task', 'Taak'))]: 'tk1', [n(PROJECT)]: 'pr1', [n(L('Person', 'Persoon'))]: 'e1', [n(HOURS)]: 6, [n(DATE)]: '2026-07-05' },
          { id: 'h2', ref: L('H-002', 'U-002'), [n(L('Task', 'Taak'))]: 'tk1', [n(PROJECT)]: 'pr1', [n(L('Person', 'Persoon'))]: 'e1', [n(HOURS)]: 2.5, [n(DATE)]: '2026-07-08' },
          { id: 'h3', ref: L('H-003', 'U-003'), [n(L('Task', 'Taak'))]: 'tk2', [n(PROJECT)]: 'pr1', [n(L('Person', 'Persoon'))]: 'e2', [n(HOURS)]: 8, [n(DATE)]: '2026-08-06' },
          { id: 'h4', ref: L('H-004', 'U-004'), [n(L('Task', 'Taak'))]: 'tk2', [n(PROJECT)]: 'pr1', [n(L('Person', 'Persoon'))]: 'e2', [n(HOURS)]: 5, [n(DATE)]: '2026-08-12' },
          { id: 'h5', ref: L('H-005', 'U-005'), [n(L('Task', 'Taak'))]: 'tk4', [n(PROJECT)]: 'pr2', [n(L('Person', 'Persoon'))]: 'e1', [n(HOURS)]: 3, [n(DATE)]: '2026-08-11' },
          { id: 'h6', ref: L('H-006', 'U-006'), [n(L('Task', 'Taak'))]: 'tk5', [n(PROJECT)]: 'pr3', [n(L('Person', 'Persoon'))]: 'e2', [n(HOURS)]: 4, [n(DATE)]: '2026-05-26' },
        ],
      },
    ],

    lateColumns: [
      // Sales reads back across its orders…
      { table: T.customers, column: { label: L('Lifetime value', 'Omzet'), type: 'rollup', expr: `SUM(${T.orders}.${n(TOTAL)})`, format: 'currency' } },
      { table: T.customers, column: { label: L('Order count', 'Aantal orders'), type: 'rollup', expr: `COUNT(${T.orders})`, format: 'number' } },
      // …and across Relations, one shelf over.
      { table: T.customers, column: { label: L('Last contact', 'Laatste contact'), type: 'rollup', expr: `MAX(${T.conversations}.${n(DATE)})` } },
      // Inventory reaches into Sales: the products being moved are the ones being sold.
      { table: T.products, column: { label: L('Supplier', 'Leverancier'), type: 'link', target: T.suppliers } },
      { table: T.products, column: { label: ON_HAND, type: 'rollup', expr: `SUM(${T.movements}.${n(QTY)})`, format: 'number' } },
      { table: T.products, column: { label: L('Stock value', 'Voorraadwaarde'), type: 'formula', expr: `${n(ON_HAND)} * ${n(PRICE)}`, format: 'currency' } },
      { table: T.suppliers, column: { label: L('Product count', 'Aantal producten'), type: 'rollup', expr: `COUNT(${T.products})`, format: 'number' } },
      // Projects bill through their hours; people carry their own total.
      { table: T.projects, column: { label: L('Hours booked', 'Uren geboekt'), type: 'rollup', expr: `SUM(${T.hours}.${n(HOURS)})`, format: 'number' } },
      { table: T.projects, column: { label: L('Billable', 'Factureerbaar'), type: 'rollup', expr: `SUM(${T.hours}.${n(AMOUNT)})`, format: 'currency' } },
      { table: T.employees, column: { label: L('Hours logged', 'Uren geboekt'), type: 'rollup', expr: `SUM(${T.hours}.${n(HOURS)})`, format: 'number' } },
    ],

    views: [
      // The not-yet-done views hide their done-date column: on these rows it
      // is empty by definition, so the view's SELECT leaves it out entirely.
      { table: T.orders, name: L('Unpaid', 'Nog te betalen'), filters: [{ col: n(STATUS), op: '≠', val: PAID }], sort: { col: n(DATE), dir: 'asc' }, hidden: [n(PAID_ON)] },
      { table: T.products, name: L('Low stock', 'Bijna op'), filters: [{ col: n(ON_HAND), op: '<', val: '5' }], sort: { col: n(ON_HAND), dir: 'asc' } },
      { table: T.tasks, name: L('Still open', 'Nog open'), filters: [{ col: n(STATUS), op: '≠', val: DONE }], sort: { col: n(L('Due', 'Deadline')), dir: 'asc' }, hidden: [n(DONE_ON)] },
      { table: T.projects, name: L('Running', 'Lopend'), filters: [{ col: n(STATUS), op: '=', val: L('Running', 'Lopend') }], sort: { col: n(L('Starts', 'Start')), dir: 'asc' } },
    ],

    rules: [
      {
        table: T.orders,
        column: n(STATUS),
        equals: PAID,
        label: L(`Status → ${PAID} stamps ${PAID_ON}`, `Status → ${PAID} stempelt ${PAID_ON}`),
        actions: [{ type: 'set', column: n(PAID_ON), value: '@today' }],
      },
      {
        table: T.tasks,
        column: n(STATUS),
        equals: DONE,
        label: L(`Status → ${DONE} stamps ${DONE_ON}`, `Status → ${DONE} stempelt ${DONE_ON}`),
        actions: [{ type: 'set', column: n(DONE_ON), value: '@today' }],
      },
    ],
  };
}

/** How many tables the demo builds, for the plan check before it does. */
export function demoTableCount(): number {
  return demoSpec('en').tables.length;
}

/**
 * Build the demo into a workspace, through the same schema and row functions
 * the HTTP routes use. Nothing here writes SQL of its own, so the demo can
 * never produce a table the rest of the product would not accept.
 *
 * The order matters and is the whole trick: every table and its stored columns
 * first, then the links and rollups that reference them, then rows, then views,
 * then rules. Rules go last so that seeding does not fire them — a "stamp
 * today's date" rule would otherwise overwrite the historical dates that make
 * the sample data look like real history.
 */
export function applyDemo(db: Db, options: { locale: Locale; mode: DemoMode }): void {
  const spec = demoSpec(options.locale);
  const actor = SYSTEM_ACTOR;

  // Three passes, because each kind of column can only resolve once the one
  // before it exists: a link needs its target table, and a formula needs the
  // link it reads across.
  for (const table of spec.tables) {
    createTable(
      db,
      {
        name: table.name,
        label: table.label,
        displayLabel: table.display,
        group: table.group,
        defaultViewName: defaultViewName(table.label, options.locale),
      },
      actor,
    );
  }

  for (const table of spec.tables) {
    for (const column of table.columns) {
      if (isComputed(column.type)) continue;
      addColumn(db, table.name, column, actor);
    }
  }

  for (const table of spec.tables) {
    for (const column of table.columns) {
      if (isComputed(column.type)) addColumn(db, table.name, column, actor);
    }
  }

  for (const { table, column } of spec.lateColumns) {
    addColumn(db, table, column, actor);
  }

  // The passes above append in their own order; put the columns back in the
  // order the demo declared them, which is the order they read in. The spec
  // speaks labels; the catalogue speaks identifiers, derived the same way
  // `addColumn` derived them.
  const derived = (column: { label?: string; name?: string }) =>
    column.name ?? slugifyColumnName(column.label!);
  for (const table of spec.tables) {
    const late = spec.lateColumns.filter((l) => l.table === table.name).map((l) => derived(l.column));
    reorderColumns(
      db,
      table.name,
      [slugifyColumnName(table.display), ...table.columns.map(derived), ...late],
      actor,
    );
  }

  for (const table of spec.tables) {
    const display = slugifyColumnName(table.display);
    if (table.requireDisplay) {
      updateColumn(db, table.name, display, { required: true }, actor);
    }
    if (table.displaySequence) {
      updateColumn(db, table.name, display, { sequence: table.displaySequence }, actor);
    }
  }

  if (options.mode === 'full') {
    for (const table of spec.tables) {
      for (const { id, ...values } of table.rows) {
        insertRow(db, loadMeta(db), table.name, values, actor, id);
      }
    }
  }

  transact(db, () => {
    // The narrative order, not the build order — see the note on `spec.order`.
    // Written directly, like the views and rules below, so seeding does not
    // fill the audit trail with its own scaffolding.
    const place = db.sqlite.prepare('UPDATE _wellformed_table SET position = ? WHERE name = ?');
    spec.order.forEach((name, index) => place.run(index, name));

    const view = db.sqlite.prepare(
      'INSERT INTO _wellformed_view (id, table_name, name, filters, sort, hidden, position, system) VALUES (?, ?, ?, ?, ?, ?, ?, 0)',
    );
    spec.views.forEach((v, index) => {
      view.run(
        newId('vw'),
        v.table,
        v.name,
        JSON.stringify(v.filters),
        JSON.stringify(v.sort),
        JSON.stringify(v.hidden ?? []),
        index + 1,
      );
    });

    const rule = db.sqlite.prepare(
      'INSERT INTO _wellformed_rule (id, table_name, column_name, equals, label, actions, enabled) VALUES (?, ?, ?, ?, ?, ?, 1)',
    );
    for (const r of spec.rules) {
      rule.run(newId('rl'), r.table, r.column, r.equals, r.label, JSON.stringify(r.actions));
    }
  });

  invalidateMeta(db);
  recordAudit(db, actor, {
    kind: 'schema',
    nw: options.mode === 'full' ? 'built the demo workspace' : 'built the demo structure',
  });
}