sample-data/generate.ts

/**
 * Generate the sample workbooks, with no dependencies.
 *
 * An `.xlsx` is a zip of XML, and the only part that is fiddly is escaping. Writing it by hand rather than
 * adding a spreadsheet library is deliberate: this runs once, the output is committed, and the platform's
 * installs are frozen for reasons that have nothing to do with convenience.
 *
 * Every number is generated from a fixed seed, so re-running this produces byte-identical files and a diff
 * means somebody changed the generator rather than the weather.
 */

import { mkdirSync, rmSync, writeFileSync } from 'node:fs';
import { join } from 'node:path';

// ── A seeded generator, so the data is stable across runs ────────────────────────────────────────────
let seed = 20260825;
const rand = () => {
  seed = (seed * 1664525 + 1013904223) % 4294967296;
  return seed / 4294967296;
};
const pick = <T>(items: T[]): T => items[Math.floor(rand() * items.length)]!;
const between = (min: number, max: number) => min + rand() * (max - min);
const round2 = (n: number) => Math.round(n * 100) / 100;

// ── xlsx ────────────────────────────────────────────────────────────────────────────────────────────

const escape = (value: string) =>
  value.replace(/&/g, '&amp;').replace(/</g, '&lt;').replace(/>/g, '&gt;').replace(/"/g, '&quot;');

const columnName = (index: number): string => {
  let name = '';
  let n = index;
  while (n >= 0) {
    name = String.fromCharCode(65 + (n % 26)) + name;
    n = Math.floor(n / 26) - 1;
  }
  return name;
};

type Cell = string | number;

/** Inline strings rather than a shared-string table: larger on disk, and one fewer part to get wrong. */
const sheetXml = (rows: Cell[][]): string => {
  const body = rows
    .map((row, rowIndex) => {
      const cells = row
        .map((value, colIndex) => {
          const ref = `${columnName(colIndex)}${rowIndex + 1}`;
          return typeof value === 'number'
            ? `<c r="${ref}"><v>${value}</v></c>`
            : `<c r="${ref}" t="inlineStr"><is><t>${escape(String(value))}</t></is></c>`;
        })
        .join('');
      return `<row r="${rowIndex + 1}">${cells}</row>`;
    })
    .join('');

  return `<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><sheetData>${body}</sheetData></worksheet>`;
};

function writeXlsx(path: string, sheetName: string, rows: Cell[][]) {
  const dir = `${path}.parts`;
  rmSync(dir, { recursive: true, force: true });
  mkdirSync(join(dir, '_rels'), { recursive: true });
  mkdirSync(join(dir, 'xl', '_rels'), { recursive: true });
  mkdirSync(join(dir, 'xl', 'worksheets'), { recursive: true });

  writeFileSync(
    join(dir, '[Content_Types].xml'),
    `<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types"><Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/><Default Extension="xml" ContentType="application/xml"/><Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/><Override PartName="/xl/worksheets/sheet1.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/></Types>`,
  );
  writeFileSync(
    join(dir, '_rels', '.rels'),
    `<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"><Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/></Relationships>`,
  );
  writeFileSync(
    join(dir, 'xl', 'workbook.xml'),
    `<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"><sheets><sheet name="${escape(sheetName)}" sheetId="1" r:id="rId1"/></sheets></workbook>`,
  );
  writeFileSync(
    join(dir, 'xl', '_rels', 'workbook.xml.rels'),
    `<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships"><Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet1.xml"/></Relationships>`,
  );
  writeFileSync(join(dir, 'xl', 'worksheets', 'sheet1.xml'), sheetXml(rows));

  rmSync(path, { force: true });
  Bun.spawnSync(['zip', '-q', '-r', '-X', path, '.'], { cwd: dir });
  rmSync(dir, { recursive: true, force: true });
}

const writeCsv = (path: string, rows: Cell[][]) =>
  writeFileSync(
    path,
    rows
      .map((row) =>
        row
          .map((value) => (typeof value === 'number' ? String(value) : /[",\n]/.test(value) ? `"${value.replace(/"/g, '""')}"` : value))
          .join(','),
      )
      .join('\n') + '\n',
  );

// ── The data ────────────────────────────────────────────────────────────────────────────────────────

const out = process.argv[2] ?? '.';
mkdirSync(out, { recursive: true });

/**
 * Two years of daily orders, with the shape a dashboard is built to show: seasonality, a weekend dip,
 * one region growing faster than the rest, and a product mix that shifts over time. Flat noise makes a
 * chart that proves nothing.
 */
const REGIONS = ['North', 'South', 'East', 'West', 'Central'];
const CATEGORIES = ['Hardware', 'Software', 'Services', 'Accessories'];
const PRODUCTS: Record<string, string[]> = {
  Hardware: ['Router X1', 'Switch 24p', 'Access Point Pro', 'Rack Shelf'],
  Software: ['Monitor Suite', 'Backup Agent', 'Firewall Rules', 'Log Indexer'],
  Services: ['Install', 'Migration', 'Support Retainer', 'Audit'],
  Accessories: ['Patch Cable 2m', 'SFP Module', 'Mounting Kit', 'PSU Spare'],
};
const CHANNELS = ['Direct', 'Partner', 'Online'];
const REPS = ['A. Silva', 'B. Nakamura', 'C. Okafor', 'D. Fischer', 'E. Moreau', 'F. Costa'];

const salesRows: Cell[][] = [
  ['Order ID', 'Date', 'Region', 'Category', 'Product', 'Channel', 'Sales Rep', 'Units', 'Unit Price', 'Revenue'],
];

const start = new Date('2024-08-01T00:00:00Z');
let orderId = 10000;

for (let day = 0; day < 730; day += 1) {
  const date = new Date(start.getTime() + day * 86400000);
  const month = date.getUTCMonth();
  const weekday = date.getUTCDay();

  // Q4 lift, summer dip, quiet weekends — the seasonality a chart is supposed to reveal.
  const seasonal = month === 10 || month === 11 ? 1.6 : month === 6 || month === 7 ? 0.7 : 1;
  const weekendDip = weekday === 0 || weekday === 6 ? 0.35 : 1;
  const growth = 1 + day / 900;

  const orders = Math.max(1, Math.round(between(4, 11) * seasonal * weekendDip * growth));

  for (let i = 0; i < orders; i += 1) {
    // East is the one pulling ahead, and only in the second year.
    const region = day > 365 && rand() < 0.28 ? 'East' : pick(REGIONS);
    const category = pick(CATEGORIES);
    const product = pick(PRODUCTS[category]!);
    const units = Math.round(between(1, category === 'Services' ? 4 : 25));
    const unitPrice = round2(
      category === 'Hardware'
        ? between(180, 1400)
        : category === 'Software'
          ? between(90, 600)
          : category === 'Services'
            ? between(400, 2500)
            : between(4, 60),
    );

    salesRows.push([
      `ORD-${orderId++}`,
      date.toISOString().slice(0, 10),
      region,
      category,
      product,
      pick(CHANNELS),
      pick(REPS),
      units,
      unitPrice,
      round2(units * unitPrice),
    ]);
  }
}

writeXlsx(join(out, 'sales.xlsx'), 'Sales', salesRows);
writeCsv(join(out, 'sales.csv'), salesRows);

/**
 * Subscriptions, for the other half of what a dashboard is asked for: a cohort, an MRR line, and churn
 * that is not evenly spread. Deliberately includes cancelled rows with an end date, so "active" is a
 * calculation rather than a column.
 */
const PLANS = [
  { name: 'Starter', mrr: 19 },
  { name: 'Team', mrr: 79 },
  { name: 'Business', mrr: 249 },
  { name: 'Enterprise', mrr: 900 },
];
const COUNTRIES = ['Portugal', 'Germany', 'Brazil', 'United Kingdom', 'Japan', 'Canada', 'Spain', 'Netherlands'];

const subRows: Cell[][] = [
  ['Account ID', 'Company', 'Country', 'Plan', 'MRR', 'Seats', 'Started', 'Cancelled', 'Status'],
];

for (let i = 0; i < 480; i += 1) {
  const plan = pick(PLANS);
  const started = new Date(start.getTime() + Math.floor(between(0, 700)) * 86400000);
  // Smaller plans churn more, which is the pattern anybody looking at this expects to find.
  const churnChance = plan.name === 'Starter' ? 0.34 : plan.name === 'Team' ? 0.18 : 0.07;
  const cancelled =
    rand() < churnChance ? new Date(started.getTime() + Math.floor(between(30, 500)) * 86400000) : null;
  const seats = Math.max(1, Math.round(between(1, plan.name === 'Enterprise' ? 400 : 40)));

  subRows.push([
    `ACC-${2000 + i}`,
    `${pick(['Nimbus', 'Verda', 'Kestrel', 'Alto', 'Brightwater', 'Ferrous', 'Solace', 'Tidal'])} ${pick(['Systems', 'Labs', 'Group', 'Industries', 'Digital'])}`,
    pick(COUNTRIES),
    plan.name,
    plan.mrr,
    seats,
    started.toISOString().slice(0, 10),
    cancelled && cancelled < new Date('2026-08-01T00:00:00Z') ? cancelled.toISOString().slice(0, 10) : '',
    cancelled && cancelled < new Date('2026-08-01T00:00:00Z') ? 'Cancelled' : 'Active',
  ]);
}

writeXlsx(join(out, 'subscriptions.xlsx'), 'Subscriptions', subRows);
writeCsv(join(out, 'subscriptions.csv'), subRows);

console.log(`sales: ${salesRows.length - 1} rows`);
console.log(`subscriptions: ${subRows.length - 1} rows`);