1#!/usr/bin/env node
2// Jev's decision history for one song: how often it picks each option,
3// section by section, across every stored performance
4// (worker/src/listening.ts; the rows are performance_segments,
5// worker/migrations/0003_listening.sql).
6//
7//   nix run .#jev-history -- lightning-in-a-bottle          the local dev database
8//   nix run .#jev-history -- jev/dial-up --current          only the song's current code
9//   nix run .#jev-history -- jev/dial-up --remote           the deployed database
10//
11// Local reads `//:dev`'s database (worker/.wrangler/state) with the
12// devshell's wrangler, from the working tree it is run in. --remote reads
13// the real one through the deploy's 1Password-fed wrangler (the flake
14// passes it as JEV_HISTORY_WRANGLER): on a temporary copy of worker/ with
15// the database's id looked up by name, read-only (nothing is created), and
16// the token never passes through this process.
17//
18// The form's question (walk()) is counted by the section playing when Jev
19// picked what comes next ("at drop2, Jev picks outro 50%"); every other
20// choice by the section it plays in. Only Jev's own picks count; a
21// fallback, a forced section, or a part absent from its section is shown
22// beside them as a count.
23import { execFileSync } from 'node:child_process';
24import { cpSync, existsSync, mkdtempSync, readFileSync, readdirSync, rmSync, writeFileSync } from 'node:fs';
25import { tmpdir } from 'node:os';
26import { join } from 'node:path';
27import { fileURLToPath } from 'node:url';
28import { createHash } from 'node:crypto';
29import { songCode } from '../../website/src/jev/songCode.mjs';
30import { parseList } from '../deploy/resources.mjs';
31
32const SONG_ID = /^[a-z0-9-]{1,64}\/[a-z0-9-]{1,64}$/;
33const HASH = /^[0-9a-f]{64}$/;
34
35// "<theme>/<song>", from either that or a song's folder name.
36export function songId(arg, songsRoot) {
37  if (SONG_ID.test(arg ?? '')) return arg;
38  if (!/^[a-z0-9-]{1,64}$/.test(arg ?? '')) throw new Error(`not a song: ${arg}`);
39  const themes = readdirSync(songsRoot, { withFileTypes: true }).filter((d) => d.isDirectory());
40  const found = themes.map((t) => `${t.name}/${arg}`).filter((id) => existsSync(join(songsRoot, id, 'SPEC.md')));
41  if (found.length !== 1) throw new Error(found.length ? `${arg} is in several themes: ${found.join(', ')}` : `no song ${arg}`);
42  return found[0];
43}
44
45// The SHA-256 of the song's code as the page plays it (songCode.mjs), which
46// is what a performance records as its codeHash.
47export function currentHash(songDir) {
48  const raw = readFileSync(join(songDir, 'song.js'), 'utf8');
49  const fm = readFileSync(join(songDir, 'SPEC.md'), 'utf8').split(/^---\s*$/m)[1] ?? '';
50  const title = fm
51    .match(/^title:\s*(.+)$/m)?.[1]
52    ?.trim()
53    .replace(/^(['"])(.*)\1$/, '$2');
54  return createHash('sha256').update(songCode(raw, title)).digest('hex');
55}
56
57// Song ids and hashes are checked against their patterns, so no quote can
58// reach the SQL (wrangler's d1 execute takes no bound parameters).
59const literal = (s, pattern) => {
60  if (!pattern.test(s)) throw new Error(`refusing to query with ${s}`);
61  return `'${s}'`;
62};
63
64export function summaryQuery(song, hash) {
65  return `SELECT count(*) AS performances, coalesce(sum(ended = 'finished'), 0) AS finished,
66  count(DISTINCT code_hash) AS versions, coalesce(sum(code_hash = ${literal(hash, HASH)}), 0) AS current
67FROM performances WHERE song = ${literal(song, SONG_ID)}`;
68}
69
70// Per (how asked, section, question, option, whether it was Jev's own pick): a count.
71export function historyQuery(song, { onlyHash = null } = {}) {
72  const isForm = 's.jev = p.form_jev AND a.key = p.form_question';
73  return `SELECT
74  CASE WHEN ${isForm} THEN 'next' ELSE 'in' END AS asked,
75  CASE WHEN ${isForm} THEN s.after_section ELSE coalesce(s.section, '#' || (s.segment + 1)) END AS at,
76  a.key AS question,
77  json_extract(a.value, '$.choice') AS choice,
78  CASE
79    -- a forced answer is marked a fallback too (jevCore), but was no one's pick
80    WHEN json_extract(a.value, '$.forced') THEN 'forced'
81    WHEN json_extract(a.value, '$.absent') THEN 'absent'
82    WHEN s.status <> 'answered' OR json_extract(a.value, '$.fallback') THEN 'fallback'
83    ELSE 'jev'
84  END AS kind,
85  count(*) AS n
86FROM performances p
87  JOIN performance_segments s ON s.performance = p.id,
88  json_each(s.answers) a
89WHERE p.song = ${literal(song, SONG_ID)} AND json_extract(a.value, '$.choice') IS NOT NULL
90  ${onlyHash ? `AND p.code_hash = ${literal(onlyHash, HASH)}` : ''}
91GROUP BY asked, at, question, choice, kind`;
92}
93
94const pct = (n, of) => `${Math.round((n / of) * 100)}%`;
95
96export function format(song, summary, rows) {
97  const s = summary ?? { performances: 0 };
98  const lines = [
99    `${song}: ${s.performances} performance${s.performances === 1 ? '' : 's'}` +
100      (s.performances
101        ? ` (${s.finished} finished, ${s.performances - s.finished} stopped), ${s.versions} version${s.versions === 1 ? '' : 's'} of its code (${s.current} of the current one)`
102        : ''),
103  ];
104  // question → asked → at → { picks: Map(choice → n), other: { fallback, forced, absent } }
105  const questions = new Map();
106  for (const r of rows) {
107    if (r.at === null) continue; // the opening's section: the form's start, not a pick
108    const q = questions.get(r.question) ?? new Map();
109    questions.set(r.question, q);
110    const key = `${r.asked}\u0000${r.at}`;
111    const at = q.get(key) ?? { asked: r.asked, at: r.at, picks: new Map(), other: {}, total: 0 };
112    q.set(key, at);
113    at.total += r.n;
114    if (r.kind === 'jev') at.picks.set(r.choice, (at.picks.get(r.choice) ?? 0) + r.n);
115    else at.other[r.kind] = (at.other[r.kind] ?? 0) + r.n;
116  }
117  for (const [question, ats] of questions) {
118    const groups = [...ats.values()].sort((a, b) => b.total - a.total || a.at.localeCompare(b.at));
119    const next = groups[0]?.asked === 'next';
120    lines.push(
121      '',
122      next
123        ? `${question}: what Jev picks next, at each section (Jev's own picks)`
124        : `${question}: what Jev picks, in each section (Jev's own picks)`,
125    );
126    const width = Math.max(...groups.map((g) => g.at.length));
127    for (const g of groups) {
128      const jevs = [...g.picks.values()].reduce((a, b) => a + b, 0);
129      const picks = [...g.picks.entries()]
130        .sort((a, b) => b[1] - a[1] || a[0].localeCompare(b[0]))
131        .map(([choice, n]) => `${choice} ${pct(n, jevs)} (${n})`)
132        .join(' · ');
133      const other = Object.entries(g.other)
134        .map(([kind, n]) => `${n} ${kind === 'fallback' && n > 1 ? 'fallbacks' : kind}`)
135        .join(', ');
136      lines.push(`  ${g.at.padEnd(width)} ${next ? '→ ' : ''}${picks || '(no picks of its own)'}${other ? `   + ${other}` : ''}`);
137    }
138  }
139  if (!questions.size && s.performances) lines.push('', 'no choices recorded');
140  return lines.join('\n');
141}
142
143// The results of one statement from `wrangler d1 execute --json`.
144export function results(out) {
145  const [first] = parseList(out, 'd1 execute');
146  if (!first?.success) throw new Error(`the query failed: ${JSON.stringify(first).slice(0, 300)}`);
147  return first.results;
148}
149
150const CONFIG = 'worker/wrangler.json';
151const databaseName = (root) => JSON.parse(readFileSync(join(root, CONFIG), 'utf8')).d1_databases[0].database_name;
152
153function localQuery(root) {
154  const name = databaseName(root);
155  return (sql) =>
156    results(
157      execFileSync(
158        'wrangler',
159        ['d1', 'execute', name, '--local', '--env', 'dev', '--config', CONFIG, '--json', '--command', sql],
160        { cwd: root, encoding: 'utf8', stdio: ['ignore', 'pipe', 'inherit'], env: { ...process.env, WRANGLER_SEND_METRICS: 'false' } },
161      ),
162    );
163}
164
165// The deployed database, through the deploy's wrangler (`<wrangler> <dir>
166// <args…>`), on a copy of worker/ given the database's id. Only reads.
167function remoteQuery(root) {
168  const wrangler = process.env.JEV_HISTORY_WRANGLER;
169  if (!wrangler) throw new Error('--remote needs JEV_HISTORY_WRANGLER; run it as `nix run .#jev-history -- … --remote`');
170  const work = mkdtempSync(join(tmpdir(), 'jev-history-'));
171  process.on('exit', () => rmSync(work, { recursive: true, force: true }));
172  cpSync(join(root, 'worker'), work, { recursive: true, filter: (src) => !src.includes('.wrangler') });
173  const run = (args) =>
174    execFileSync(wrangler, [work, ...args], { encoding: 'utf8', stdio: ['ignore', 'pipe', 'inherit'], env: { ...process.env, WRANGLER_SEND_METRICS: 'false' } });
175  const config = JSON.parse(readFileSync(join(work, 'wrangler.json'), 'utf8'));
176  const db = config.d1_databases[0];
177  const found = parseList(run(['d1', 'list', '--json']), 'd1 list').find((d) => d?.name === db.database_name);
178  if (!found) throw new Error(`the account has no D1 database ${db.database_name}; nothing has been recorded there`);
179  db.database_id = found.uuid;
180  writeFileSync(join(work, 'wrangler.json'), JSON.stringify(config, null, 2));
181  return (sql) => results(run(['d1', 'execute', db.database_name, '--remote', '--json', '--command', sql]));
182}
183
184async function main(args) {
185  const flags = new Set(args.filter((a) => a.startsWith('--')));
186  const [arg] = args.filter((a) => !a.startsWith('--'));
187  const unknown = [...flags].filter((f) => !['--remote', '--current'].includes(f));
188  if (!arg || unknown.length) throw new Error('usage: jev-history <song> [--current] [--remote]');
189  const root = execFileSync('git', ['rev-parse', '--show-toplevel'], { encoding: 'utf8' }).trim();
190  const songsRoot = join(root, 'songs');
191  const song = songId(arg, songsRoot);
192  const hash = currentHash(join(songsRoot, song));
193  const query = flags.has('--remote') ? remoteQuery(root) : localQuery(root);
194  const [summary] = query(summaryQuery(song, hash));
195  const rows = query(historyQuery(song, { onlyHash: flags.has('--current') ? hash : null }));
196  console.log(`${flags.has('--remote') ? 'the deployed database' : 'the local dev database'}${flags.has('--current') ? ', the current code only' : ''}\n`);
197  console.log(format(song, summary, rows));
198}
199
200if (process.argv[1] === fileURLToPath(import.meta.url)) {
201  main(process.argv.slice(2)).catch((e) => {
202    console.error(`jev-history: ${e.message}`);
203    process.exit(1);
204  });
205}