data-explorer

1import { atom, read, update } from 'claude-code'
2import type { EngineInterface, Register, RenderSurface } from 'claude-code'
3
4import type { QueryPanel, QueryReload, QueryResult, QueryTable, QueryView } from '../types'
5import { captionOf, findTables } from './find'
6import type { Format, Found, TextKind } from './find'
7import { STORE_CAP, TABLE_FILE, parseFile, parsePowerBi } from './parse'
8import { parseXlsx } from './xlsx'
9import {
10  GAP,
11  MEASURES,
12  MIN_WIDTH,
13  bringToFront,
14  canAggregate,
15  columnsText,
16  computed,
17  contextText,
18  copyText,
19  countText,
20  cycleSort,
21  fitColumns,
22  footerText,
23  freshView,
24  headLabel,
25  hideColumn,
26  isDefaultView,
27  isDescendingFirst,
28  lineCount,
29  moveColumn,
30  roleOf,
31  rowSegments,
32  rowsColumn,
33  scopeText,
34  setAggregation,
35  toggleKey,
36  ungroup,
37  viewChanges,
38  viewSignature,
39} from './view'
40
41const PANE = 'data-explorer'
42const TITLE = 'Data'
43const DEFAULT_MAX_ROWS = 250
44const POWER_BI_SERVER = 'claude_ai_Power_BI'
45const POWER_BI_TOOL = 'ExecuteQuery'
46const POWER_BI_NAME = `mcp__${POWER_BI_SERVER}__${POWER_BI_TOOL}`
47const FILE_TOOL = 'show_file'
48const FILE_TOOL_NAME = /^mcp__data-explorer__show_file$/
49const QUIET_TOOL =
50  /^(Edit|MultiEdit|Write|NotebookEdit|TodoWrite|ToolSearch|Skill|EnterPlanMode|ExitPlanMode|EnterWorktree|ExitWorktree|AskUserQuestion|SendMessage|ListAgents|ReadNotifications|ScheduleWakeup|TaskStop|Monitor|LSP|Cron\w*|Artifact\w*|mcp__data-explorer__\w+|mcp__trace-waterfall__\w+)$/
51const SHELL_TOOL = /^(Bash|PowerShell)$/
52const DOCUMENT_FILE = /\.(md|markdown|txt|text|log|out|html?)$/i
53const PARQUET_FILE = /\.parquet$/i
54const XLSX_FILE = /\.xls[xm]$/i
55const PATH_IN_COMMAND = /"([^"]+\.(?:csv|tsv|jsonl?|ndjson))"|'([^']+\.(?:csv|tsv|jsonl?|ndjson))'|([^\s"'|<>;&()]+\.(?:csv|tsv|jsonl?|ndjson))(?![\w.])/gi
56const PRIMARY_FORMATS: Format[] = ['box', 'grid', 'psql', 'pandas', 'delimited']
57const FILE_LIMIT = 4 * 1024 * 1024
58const HISTORY = 12
59const HISTORY_ROWS = 300000
60const ACCENT = 'claude'
61const BAR = 'subtle'
62const CONTROL_WIDTH = 16
63const BUTTON_LABELS = ['group rows', 'show data bars', 'copy table']
64const GROUP_OPTIONS = ['off', 'group', ...MEASURES.filter(measure => measure !== 'off')] as const
65const WAITING = 'Data explorer: a new table waits. Widen the terminal to 144 columns, or run /data-explorer'
66const NO_KEYBOARD = 'Data explorer: empty the prompt box, then click "type a filter" again'
67
68const resultsAtom = atom({ plugin: 'data-explorer', key: 'results' } as const, [] as QueryResult[])
69const shownAtom = atom({ plugin: 'data-explorer', key: 'shown' } as const, '')
70const viewsAtom = atom({ plugin: 'data-explorer', key: 'views' } as const, [] as QueryView[])
71const unseenAtom = atom({ plugin: 'data-explorer', key: 'unseen' } as const, [] as string[])
72const viewAtom = atom({ plugin: 'data-explorer', key: 'view' } as const, null)
73const undoAtom = atom({ plugin: 'data-explorer', key: 'undo' } as const, null)
74const panelAtom = atom({ plugin: 'data-explorer', key: 'panel' } as const, 'table' as QueryPanel)
75const sentAtom = atom({ plugin: 'data-explorer', key: 'sent' } as const, '')
76const firstRowAtom = atom({ plugin: 'data-explorer', key: 'firstRow' } as const, 0)
77const firstColumnAtom = atom({ plugin: 'data-explorer', key: 'firstColumn' } as const, 0)
78const loadingAtom = atom({ plugin: 'data-explorer', key: 'loading' } as const, '')
79const askedAtom = atom({ plugin: 'data-explorer', key: 'asked' } as const, '')
80
81const FILE_TOOL_DESCRIPTION = [
82  'Shows every row of a CSV, TSV, JSON, JSON-lines or Excel (.xlsx) file in the data explorer pane beside the chat, where the user sorts, filters and groups the rows.',
83  'The rows do not enter your context: the tool answers with the row and column count only.',
84  'Use it when the user wants to explore a result that is too large to print: write the full result to a file first (in DuckDB: COPY (SELECT ...) TO \'result.csv\'), then pass the file path.',
85  'A Parquet file needs that step too. Files up to 4 MiB; the pane keeps the first 100,000 rows.',
86].join(' ')
87
88let pageRows = 10
89let loads = 0
90let opensOnTable = true
91
92type Ran = { deny?: unknown; isError?: unknown; result?: unknown; text?: unknown }
93
94type Capture = { table: QueryTable; id: string; source: string; query: string; isPrimary: boolean; reload?: QueryReload | null }
95
96type Placement = 'auto' | 'show' | 'keep'
97
98function succeeded(ran: unknown): Ran | null {
99  if (!ran || typeof ran !== 'object') return null
100  const r = ran as Ran
101  return r.deny !== undefined || r.isError === true ? null : r
102}
103
104function outputText(r: Ran): string | null {
105  if (typeof r.text === 'string' && r.text.trim() !== '') return r.text
106  if (typeof r.result === 'string') return r.result
107  const result = r.result as { stdout?: unknown; content?: unknown } | undefined
108  if (result && typeof result === 'object' && typeof result.stdout === 'string') return result.stdout
109  const content = result?.content
110  if (Array.isArray(content)) {
111    return content
112      .map(block => (block && typeof block === 'object' && typeof (block as { text?: unknown }).text === 'string' ? (block as { text: string }).text : ''))
113      .join('\n')
114  }
115  return null
116}
117
118function sourceOf(tool: string): string {
119  if (tool === POWER_BI_NAME) return 'Power BI'
120  const mcp = /^mcp__(.+?)__(.+)$/.exec(tool)
121  if (!mcp) return tool
122  return (mcp[1] ?? '').replace(/^claude_ai_/, '').replace(/^plugin_[^_]+_/, '').replace(/_/g, ' ')
123}
124
125function queryOf(tool: string, input: Record<string, unknown>): string {
126  for (const key of ['command', 'url', 'file_path', 'description']) {
127    const value = input[key]
128    if (typeof value === 'string' && value.trim() !== '') return value
129  }
130  const args = Object.fromEntries(Object.entries(input).filter(([key]) => !['tool', 'tool_use_id', 'agentId', 'requestMeta', 'consent'].includes(key)))
131  const name = /^mcp__.+?__(.+)$/.exec(tool)?.[1] ?? tool
132  return `${name} ${JSON.stringify(args)}`
133}
134
135function nameWidth(result: QueryResult): number {
136  return result.columns.reduce((width, name) => Math.max(width, name.length), 0)
137}
138
139function blocksText(content: readonly { text?: string }[]): string {
140  return content.map(block => block.text ?? '').join('\n')
141}
142
143function sameColumns(a: QueryTable, b: QueryTable): boolean {
144  return JSON.stringify(a.columns) === JSON.stringify(b.columns)
145}
146
147function sameData(a: QueryTable, b: QueryTable): boolean {
148  return (
149    sameColumns(a, b) &&
150    a.rows.length === b.rows.length &&
151    a.total === b.total &&
152    JSON.stringify(a.rows[0] ?? null) === JSON.stringify(b.rows[0] ?? null) &&
153    JSON.stringify(a.rows[a.rows.length - 1] ?? null) === JSON.stringify(b.rows[b.rows.length - 1] ?? null)
154  )
155}
156
157function askText(result: QueryResult): string {
158  return [
159    'Write every row of the table in the data explorer pane to a CSV file in a temporary folder, without printing the rows here.',
160    "Then show that file with the data explorer's show_file tool.",
161    `The pane holds ${countText(result.rows.length)} rows of a table from ${result.source}, which came from:`,
162    '',
163    '```',
164    result.query,
165    '```',
166  ].join('\n')
167}
168
169function paneColumns(result: QueryResult): number {
170  const width = computed(result, freshView(result)).layout.widths.reduce((sum, w) => sum + w + GAP, 2)
171  const list = GROUP_OPTIONS.reduce((sum, option) => sum + option.length + GAP, nameWidth(result) + 2)
172  return Math.min(100, Math.max(48, width, list))
173}
174
175function oneLine(text: string): string {
176  return text.replace(/\s+/g, ' ').trim()
177}
178
179function currentView(result: QueryResult, stored: QueryView | null): QueryView {
180  return stored && stored.resultId === result.id ? { ...freshView(result), ...stored } : freshView(result)
181}
182
183function entryLabel(entry: QueryResult): string {
184  const rows = entry.rows.length
185  const columns = entry.columns.length
186  return `${entry.source}: ${countText(rows)} ${rows === 1 ? 'row' : 'rows'} × ${columns} ${columns === 1 ? 'column' : 'columns'}`
187}
188
189function kept(results: QueryResult[], keep: string): QueryResult[] {
190  const list: QueryResult[] = []
191  let rows = 0
192  for (const result of results) {
193    if (result.id !== keep && (list.length >= HISTORY || rows + result.rows.length > HISTORY_ROWS)) continue
194    list.push(result)
195    rows += result.rows.length
196  }
197  return list
198}
199
200async function shownResult($: EngineInterface): Promise<QueryResult | null> {
201  const id = await read($, shownAtom)
202  if (id === '') return null
203  return (await read($, resultsAtom)).find(result => result.id === id) ?? null
204}
205
206async function canFollow($: EngineInterface, current: QueryResult | null, next: QueryResult): Promise<boolean> {
207  if (!current) return true
208  if (current.isPrimary && !next.isPrimary) return false
209  if ((await read($, panelAtom)) !== 'table') return false
210  return isDefaultView(current, currentView(current, await read($, viewAtom)))
211}
212
213async function showResult($: EngineInterface, result: QueryResult, keepsView: boolean, before: QueryResult | null): Promise<void> {
214  const stored = await read($, viewAtom)
215  const own = before && stored && stored.resultId === before.id ? stored : null
216  const held = keepsView && before && own && sameColumns(before, result) ? own : null
217  const saved = (await read($, viewsAtom)).find(view => view.resultId === result.id) ?? null
218  await update($, viewsAtom, views => [
219    ...views.filter(view => view.resultId !== result.id && view.resultId !== before?.id),
220    ...(own && !held ? [own] : []),
221  ])
222  await update($, shownAtom, () => result.id)
223  await update($, viewAtom, () => (held ? { ...held, resultId: result.id } : saved ?? freshView(result)))
224  await update($, unseenAtom, ids => ids.filter(id => id !== result.id))
225  await update($, undoAtom, () => null)
226  await update($, firstRowAtom, () => 0)
227  await update($, loadingAtom, () => '')
228  await update($, askedAtom, () => '')
229  if (!held) {
230    await update($, panelAtom, () => 'table' as QueryPanel)
231    await update($, firstColumnAtom, () => 0)
232  }
233}
234
235async function reveal($: EngineInterface, result: QueryResult): Promise<void> {
236  const opened = await $.ui.open({ id: PANE, title: TITLE, columns: paneColumns(result) })
237  $.ui.status(opened.isPlaced ? undefined : WAITING)
238}
239
240async function store($: EngineInterface, capture: Capture, placement: Placement, replaces = ''): Promise<void> {
241  const results = await read($, resultsAtom)
242  const current = await shownResult($)
243  const twin = placement === 'auto' ? results.find(entry => entry.source === capture.source && sameData(entry, capture.table)) : undefined
244  if (twin && twin.id === current?.id) return
245  loads += 1
246  const result: QueryResult = twin ?? {
247    ...capture.table,
248    id: `${capture.id}#${loads}`,
249    source: capture.source,
250    query: capture.query,
251    reload: capture.reload ?? null,
252    isPrimary: capture.isPrimary,
253  }
254  const isShown = placement !== 'auto' || (await canFollow($, current, result))
255  const others = results.filter(entry => entry.id !== result.id && entry.id !== replaces)
256  await update($, resultsAtom, () => kept([result, ...others], isShown ? result.id : current?.id ?? ''))
257  const ids = new Set((await read($, resultsAtom)).map(entry => entry.id))
258  await update($, viewsAtom, views => views.filter(view => ids.has(view.resultId)))
259  await update($, unseenAtom, unseen => unseen.filter(id => ids.has(id)))
260  if (!isShown) {
261    if (ids.has(result.id)) await update($, unseenAtom, unseen => [result.id, ...unseen.filter(id => id !== result.id)])
262    return
263  }
264  await showResult($, result, placement === 'keep', current)
265  if ((result.isPrimary && opensOnTable) || placement !== 'auto') await reveal($, result)
266}
267
268async function storeFound($: EngineInterface, found: Found[], text: string, meta: { id: string; source: string; query: string; isPrimary: boolean }): Promise<void> {
269  for (const [k, f] of found.entries()) {
270    const caption = captionOf(text, f.line)
271    const query = meta.query !== '' ? (found.length > 1 ? `${meta.query} (table ${k + 1} of ${found.length})` : meta.query) : caption
272    await store($, { table: f.table, id: `${meta.id}:${k}`, source: meta.source, query, isPrimary: meta.isPrimary && PRIMARY_FORMATS.includes(f.format) }, 'auto')
273  }
274}
275
276async function captureText($: EngineInterface, text: string, kind: TextKind, meta: { id: string; source: string }): Promise<void> {
277  const found = findTables(text, kind)
278  for (const [k, f] of found.entries()) {
279    await store($, { table: f.table, id: `${meta.id}:${k}`, source: meta.source, query: captionOf(text, f.line), isPrimary: false }, 'auto')
280  }
281}
282
283async function readTable($: EngineInterface, path: string, isAsked: boolean): Promise<QueryTable | string> {
284  if (PARQUET_FILE.test(path)) return `${path} is a Parquet file; write it to CSV first, for example in DuckDB: COPY (FROM '${path}') TO 'result.csv'.`
285  const stat = await $.fs.stat(path).catch(() => undefined)
286  if (!stat || stat.kind !== 'file') return `${path} is not a file.`
287  if (stat.size > FILE_LIMIT) return `${path} is ${(stat.size / 1024 / 1024).toFixed(1)} MiB; the data explorer reads files up to 4 MiB.`
288  if (XLSX_FILE.test(path)) return parseXlsx((await $.fs.read(path, { as: 'bytes' })).base64) ?? `${path} holds no sheet with a table.`
289  const text = await $.fs.read(path)
290  if (!isAsked && /\.json$/i.test(path) && !text.replace(/^/, '').trimStart().startsWith('[')) return `${path} is not a JSON array.`
291  return parseFile(path, text) ?? `${path} holds no table: the data explorer reads CSV, TSV, a JSON array of objects, or JSON lines.`
292}
293
294async function fileBehind($: EngineInterface, command: string, table: QueryTable): Promise<{ table: QueryTable; path: string } | null> {
295  const paths = [...command.matchAll(PATH_IN_COMMAND)].map(match => match[1] ?? match[2] ?? match[3] ?? '').filter(path => path !== '')
296  for (const path of paths.slice(0, 3)) {
297    const drive = /^\/([a-z])\/(.*)$/i.exec(path)
298    for (const candidate of drive ? [path, `${(drive[1] ?? '').toUpperCase()}:/${drive[2] ?? ''}`] : [path]) {
299      const whole = await readTable($, candidate, false).catch(() => '')
300      if (typeof whole !== 'string' && sameColumns(whole, table) && whole.rows.length >= table.rows.length) return { table: whole, path: candidate }
301    }
302  }
303  return null
304}
305
306async function captureRead($: EngineInterface, path: string, id: string, where: string, isRead: boolean): Promise<void> {
307  if (!isRead && !XLSX_FILE.test(path)) return
308  if (TABLE_FILE.test(path) || XLSX_FILE.test(path)) {
309    const table = await readTable($, path, false)
310    if (typeof table !== 'string') await store($, { table, id, source: `File${where}`, query: path, isPrimary: true }, 'auto')
311    else if (table.includes('MiB')) $.ui.toast(`Data explorer: ${table}`)
312    return
313  }
314  if (!DOCUMENT_FILE.test(path)) return
315  const stat = await $.fs.stat(path).catch(() => undefined)
316  if (!stat || stat.kind !== 'file' || stat.size > FILE_LIMIT) return
317  const text = await $.fs.read(path)
318  await storeFound($, findTables(text, 'document'), text, { id, source: `File${where}`, query: path, isPrimary: false })
319}
320
321async function capturePowerBi($: EngineInterface, input: Record<string, unknown>, text: string, id: string, where: string): Promise<void> {
322  const queries = Array.isArray(input.daxQueries) ? input.daxQueries.map(String) : []
323  const maxRows = typeof input.maxRows === 'number' ? input.maxRows : DEFAULT_MAX_ROWS
324  const table = parsePowerBi(text, maxRows)
325  if (!table) return
326  const reload =
327    typeof input.artifactId === 'string' && queries[0] !== undefined
328      ? { server: POWER_BI_SERVER, tool: POWER_BI_TOOL, artifactId: input.artifactId, daxQuery: queries[0], maxRows }
329      : null
330  await store($, { table, id, source: `Power BI${where}`, query: queries.join('\n'), isPrimary: true, reload }, 'auto')
331}
332
333async function captureCall($: EngineInterface, tool: string, input: Record<string, unknown>, ran: unknown): Promise<void> {
334  const where = typeof input.agentId === 'string' ? ' in a subagent' : ''
335  const id = String(input.tool_use_id ?? tool)
336  const r = succeeded(ran)
337  if (tool === 'Read') return captureRead($, String(input.file_path ?? ''), id, where, r !== null)
338  if (!r) return
339  const text = outputText(r)
340  if (text === null || text.trim() === '' || text.length > FILE_LIMIT) return
341  if (tool === POWER_BI_NAME) return capturePowerBi($, input, text, id, where)
342  const found = findTables(text, 'output')
343  if (found.length === 0) return
344  const query = queryOf(tool, input)
345  const isShell = SHELL_TOOL.test(tool)
346  const first = found[0]
347  if (isShell && found.length === 1 && first) {
348    const whole = await fileBehind($, query, first.table)
349    if (whole) return store($, { table: whole.table, id, source: `File${where}`, query: whole.path, isPrimary: true }, 'auto')
350  }
351  await storeFound($, found, text, { id, source: `${sourceOf(tool)}${where}`, query, isPrimary: isShell })
352}
353
354export const register: Register = (on, options) => {
355  opensOnTable = options?.open !== 'off'
356
357  on('session.start', async ($, e, next) => {
358    await $.command.register({
359      name: 'data-explorer',
360      description: 'Show the tables Claude ran into in the data explorer pane',
361    })
362    await $.tool.register({
363      name: FILE_TOOL,
364      description: FILE_TOOL_DESCRIPTION,
365      inputSchema: {
366        type: 'object',
367        properties: { path: { type: 'string', description: 'The file to show, absolute or relative to the working directory' } },
368        required: ['path'],
369      },
370    })
371
372    return next(e)
373  })
374
375  on('tool.call', { tool: FILE_TOOL_NAME }, async ($, e) => {
376    const input = e as unknown as { path?: unknown; tool_use_id?: unknown }
377    const path = String(input.path ?? '')
378    const table = await readTable($, path, true).catch((error: unknown) => `${path} could not be read: ${String(error)}`)
379    if (typeof table === 'string') return { deny: table }
380    const asked = await read($, askedAtom)
381    await store($, { table, id: String(input.tool_use_id ?? 'file'), source: 'File', query: path, isPrimary: true }, asked !== '' ? 'keep' : 'show', asked)
382    const rows = table.isCapped ? `the first ${countText(table.rows.length)} of ${countText(table.total)} rows` : `${countText(table.rows.length)} rows`
383    return { result: `The data explorer pane shows ${rows} and ${table.columns.length} columns of ${path}.` }
384  })
385
386  on('tool.call', async ($, e, next) => {
387    const ran = await next(e)
388    if (!QUIET_TOOL.test(e.tool)) await captureCall($, e.tool, e as unknown as Record<string, unknown>, ran).catch(() => undefined)
389
390    return ran
391  }).catch(($, e, next) => next(e))
392
393  on('turn.step', async function* ($, e, next) {
394    const step = yield* next(e)
395    if (e.agentId === undefined && step && step.answer.trim() !== '') {
396      await captureText($, step.answer, 'message', { id: `reply-${e.turnId}-${e.index}`, source: 'Claude' }).catch(() => undefined)
397    }
398
399    return step
400  })
401
402  on('command.run', { command: 'data-explorer' }, async $ => {
403    const opened = await $.ui.open({ id: PANE, title: TITLE })
404    $.ui.status(undefined)
405
406    return { text: opened.isPlaced ? 'Data explorer pane opened.' : `Data explorer pane is open but not drawn: ${opened.reason}` }
407  })
408
409  on('prompt.submit', async ($, e, next) => {
410    if (e.command || e.isMeta) return next(e)
411    if (e.origin?.kind !== 'plugin') await captureText($, e.text, 'message', { id: 'prompt', source: 'Your message' }).catch(() => undefined)
412    const result = await shownResult($)
413    const stored = await read($, viewAtom)
414    if (!result || !stored || stored.resultId !== result.id) return next(e)
415    const view = currentView(result, stored)
416    if (isDefaultView(result, view)) return next(e)
417    const signature = viewSignature(view)
418    if (signature === (await read($, sentAtom))) return next(e)
419    await update($, sentAtom, () => signature)
420
421    return next({ ...e, context: [...(e.context ?? []), contextText(result, view)] })
422  }).catch(($, e, next) => next(e))
423
424  on('ui.scroll', { requestId: PANE }, async ($, e, next) => {
425    const result = await shownResult($)
426    if (!result) return next(e)
427    const view = currentView(result, await read($, viewAtom))
428    const panel = await read($, panelAtom)
429    if (panel === 'tables') return {}
430    if (panel === 'group') {
431      const last = Math.max(0, result.columns.length - (pageRows - 1))
432      await update($, firstColumnAtom, n => Math.min(last, Math.max(0, n + e.by)))
433      return {}
434    }
435    const last = Math.max(0, computed(result, view).shown.kept.length - pageRows)
436    await update($, firstRowAtom, n => Math.min(last, Math.max(0, n + e.by)))
437
438    return {}
439  }).catch(($, e, next) => next(e))
440
441  on('ui.render', { component: 'Pane', requestId: PANE }, async ($, e, next) => {
442    if (e.surface !== 'terminal' && e.surface !== 'desktop') return next(e)
443    const { Box, Button, Input, Text } = $.ui.resolve(e)
444    const result = await shownResult($)
445
446    if (!result) {
447      return (
448        <Box flexDirection="column">
449          <Text key="status" dimColor>No table yet.</Text>
450          <Text dimColor>
451            Tables Claude runs into show up here: query results, CSV, JSON and Excel files, tables in command output, tool results and web pages, and tables in your messages and Claude's replies.
452          </Text>
453        </Box>
454      )
455    }
456
457    const results = await read($, resultsAtom)
458    const unseen = await read($, unseenAtom)
459    const view = currentView(result, await read($, viewAtom))
460    const undo = await read($, undoAtom)
461    const panel = await read($, panelAtom)
462    const loading = await read($, loadingAtom)
463    const asked = await read($, askedAtom)
464    const canUndo = undo !== null && undo.resultId === result.id && isDefaultView(result, view)
465
466    const apply = async (fn: (v: QueryView) => QueryView) => {
467      const held: { next?: QueryView; reorders?: boolean } = {}
468      await update($, viewAtom, v => {
469        if (!v || v.resultId !== result.id) return v
470        const before = currentView(result, v)
471        const after = fn(before)
472        if (viewSignature(after) === viewSignature(before)) return v
473        held.next = after
474        held.reorders =
475          after.filter !== before.filter ||
476          JSON.stringify(after.sort) !== JSON.stringify(before.sort) ||
477          JSON.stringify(after.group) !== JSON.stringify(before.group)
478        return after
479      })
480      if (!held.next) return
481      await update($, undoAtom, () => null)
482      if (held.reorders) await update($, firstRowAtom, () => 0)
483      if (isDefaultView(result, held.next)) await update($, panelAtom, p => (p === 'changes' ? 'table' : p))
484    }
485    const change = (fn: (v: QueryView) => QueryView) => () => apply(fn)
486    const noop = () => undefined
487    const setFilter = (value: string) => apply(v => ({ ...v, filter: value }))
488    const togglePanel = (target: QueryPanel) => () => update($, panelAtom, p => (p === target ? 'table' : target))
489    const backToTable = () => update($, panelAtom, () => 'table' as QueryPanel)
490    const leaveTables = async () => {
491      await update($, unseenAtom, () => [])
492      await backToTable()
493    }
494    const toggleTables = () => (panel === 'tables' ? leaveTables() : update($, panelAtom, () => 'tables' as QueryPanel))
495    const pick = (entry: QueryResult) => async () => {
496      if (entry.id !== result.id) {
497        await showResult($, entry, false, result)
498        await $.ui.open({ id: PANE, title: TITLE, columns: paneColumns(entry) })
499      }
500      await leaveTables()
501    }
502    const reset = async () => {
503      await update($, undoAtom, () => view)
504      await update($, viewAtom, () => freshView(result))
505      await update($, panelAtom, () => 'table' as QueryPanel)
506      await update($, firstRowAtom, () => 0)
507    }
508    const undoReset = async () => {
509      if (!canUndo || !undo) return
510      await update($, viewAtom, () => undo)
511      await update($, undoAtom, () => null)
512      await update($, firstRowAtom, () => 0)
513    }
514    const typeFilter = async () => {
515      await $.ui.open({ id: PANE, title: TITLE, focus: true })
516      const pane = (await $.ui.panes()).find(p => p.id === PANE)
517      if (!pane?.isFocused) $.ui.toast(NO_KEYBOARD)
518    }
519
520    const loadAll = async () => {
521      const reload = result.reload
522      if (!reload) return
523      await update($, loadingAtom, () => result.id)
524      const answer = await $.mcp
525        .call(reload.server, reload.tool, { artifactId: reload.artifactId, daxQueries: [reload.daxQuery], maxRows: STORE_CAP })
526        .catch((error: unknown) => ({ content: [{ type: 'text', text: String(error) }], isError: true }))
527      const text = blocksText(answer.content)
528      const table = answer.isError ? null : parsePowerBi(text, STORE_CAP)
529      if (table) {
530        const capture = { table, id: result.id, source: result.source, query: reload.daxQuery, isPrimary: true, reload: { ...reload, maxRows: STORE_CAP } }
531        await store($, capture, 'keep', result.id)
532        return
533      }
534      await update($, loadingAtom, () => '')
535      $.ui.toast(`Data explorer: Power BI did not return the rows. ${oneLine(text).slice(0, 160)}`)
536    }
537    const askClaude = async () => {
538      await update($, askedAtom, () => result.id)
539      await $.prompt.submit({ text: askText(result) }).catch(async () => {
540        await update($, askedAtom, () => '')
541        $.ui.toast('Data explorer: the request to Claude did not go through')
542      })
543    }
544    const allRows = !result.isCapped
545      ? null
546      : result.reload && result.reload.maxRows < STORE_CAP
547        ? loading === result.id
548          ? { label: 'loading all rows', isDim: true, press: noop }
549          : { label: 'load all rows', isDim: false, press: loadAll }
550        : result.query.trim() !== '' && !result.source.startsWith('File')
551          ? asked === result.id
552            ? { label: 'asked Claude for all rows', isDim: true, press: noop }
553            : { label: 'ask Claude for all rows', isDim: false, press: askClaude }
554          : null
555
556    const { shown: found, layout } = computed(result, view)
557    const { table, order, kept: keptRows, matched } = found
558    const rows = rowsColumn(result)
559    const name = (c: number) => table.columns[c] ?? `column ${c + 1}`
560    const { kinds, widths } = layout
561    const bodyColumns = Math.max(20, e.props.bodyColumns || 80)
562    const { fitting, past } = fitColumns(order, widths, bodyColumns)
563    const notes = result.note === '' ? '' : `${result.note}.`
564    const changes = viewChanges(result, view)
565    const buttonLines = lineCount([...BUTTON_LABELS.map(label => label.length + 4), CONTROL_WIDTH], bodyColumns, GAP)
566    const showChanges = panel === 'changes' && changes.length > 0
567    const showGroup = panel === 'group'
568    const showTables = panel === 'tables'
569    const showRows = !showChanges && !showGroup && !showTables
570    pageRows = Math.max(1, (e.props.scroll?.bodyRows || 40) - (7 + buttonLines))
571    const first = Math.min(await read($, firstRowAtom), Math.max(0, keptRows.length - pageRows))
572    const indexes = keptRows.slice(first, first + pageRows)
573    const hasMeasures = order.some(c => kinds[c] === 'measure')
574    const cellWidth = (c: number, i: number) => (widths[c] ?? MIN_WIDTH) + (i < fitting.length - 1 ? GAP : 0)
575    const align = (c: number) => (kinds[c] === 'text' ? 'flex-start' : 'flex-end')
576
577    const listed = [...view.order, ...view.hidden]
578    const listRoom = Math.max(1, pageRows - 1)
579    const firstListed = Math.min(await read($, firstColumnAtom), Math.max(0, listed.length - listRoom))
580    const listedShown = listed.slice(firstListed, firstListed + listRoom)
581    const footer = showGroup
582      ? listed.length > listRoom
583        ? `columns ${footerText(firstListed, listedShown.length, listed.length)}`
584        : ''
585      : showTables
586        ? ''
587        : footerText(first, indexes.length, keptRows.length)
588
589    const copy = async (surface: RenderSurface) => {
590      const done = await $.ui.copy({ text: copyText(table, order, keptRows), surface })
591      $.ui.toast(
592        done.isCopied
593          ? `Copied ${countText(keptRows.length)} ${keptRows.length === 1 ? 'row' : 'rows'} and ${order.length} columns, tab-separated`
594          : `Data explorer: nothing copied (${done.reason})`,
595      )
596    }
597
598    const controlLabel = canUndo ? 'Undo reset' : changes.length === 0 ? 'Default view' : `${changes.length} ${changes.length === 1 ? 'change' : 'changes'}`
599    const pressControl = canUndo ? undoReset : changes.length === 0 ? noop : togglePanel('changes')
600    const tablesLabel = `${results.length} ${results.length === 1 ? 'table' : 'tables'}${unseen.length > 0 ? `, ${unseen.length} new` : ''}`
601
602    return (
603      <Box flexDirection="column">
604        <Box flexDirection="row" height={1} overflow="hidden" columnGap={GAP}>
605          <Box flexShrink={1}>
606            <Text wrap="truncate-end">
607              {scopeText(result, view, matched, keptRows.length)}
608              {notes !== '' && <Text dimColor>{` ${notes}`}</Text>}
609            </Text>
610          </Box>
611          {allRows && (
612            <Box flexShrink={0}>
613              <Button key="all-rows" label={allRows.label} dimColor={allRows.isDim} onPress={allRows.press} />
614            </Box>
615          )}
616        </Box>
617        <Box flexDirection="row" height={1} overflow="hidden" columnGap={GAP}>
618          <Box flexShrink={1} flexGrow={1}>
619            <Text dimColor wrap={SHELL_TOOL.test(result.source) ? 'truncate-start' : 'truncate-end'}>
620              {oneLine(result.query) || ' '}
621            </Text>
622          </Box>
623          <Box flexShrink={0}>
624            <Button key="tables" onPress={toggleTables}>
625              <Text color={unseen.length > 0 ? ACCENT : undefined}>{tablesLabel}</Text>
626            </Button>
627          </Box>
628        </Box>
629        <Box flexDirection="row" height={1} overflow="hidden" columnGap={GAP}>
630          <Box flexShrink={0}>
631            <Button key="type-filter" label="type a filter" dimColor={e.props.isFocused} onPress={typeFilter} />
632          </Box>
633          <Box flexGrow={1}>
634            <Input
635              key={`filter-${result.id}`}
636              value={view.filter}
637              autoFocus
638              placeholder={e.props.isFocused ? 'type to keep matching rows, Esc to leave' : 'no filter'}
639              submitLabel="done"
640              onInput={value => setFilter(value)}
641              onSubmit={value => setFilter(value)}
642            />
643          </Box>
644          <Box flexShrink={0}>
645            <Button key="clear-filter" plain label=" × " dimColor={view.filter === ''} onPress={view.filter === '' ? noop : () => setFilter('')} />
646          </Box>
647        </Box>
648        <Box flexDirection="row" flexWrap="wrap" height={buttonLines} overflow="hidden" columnGap={GAP}>
649          <Button key="group" label="group rows" onPress={togglePanel('group')} />
650          <Button
651            key="bars"
652            label={view.bars ? 'hide data bars' : 'show data bars'}
653            dimColor={!hasMeasures}
654            onPress={hasMeasures ? change(v => ({ ...v, bars: !v.bars })) : noop}
655          />
656          <Button key="copy" label="copy table" onPress={press => void copy(press.surface)} />
657          <Box width={CONTROL_WIDTH}>
658            <Button key="view-state" dimColor={changes.length === 0 && !canUndo} onPress={pressControl}>
659              <Text color={changes.length > 0 ? ACCENT : undefined}>{controlLabel}</Text>
660            </Button>
661          </Box>
662        </Box>
663        <Box flexDirection="row" height={1} overflow="hidden" columnGap={GAP}>
664          <Box flexShrink={0}>
665            <Text dimColor>{columnsText(order.length, past)}</Text>
666          </Box>
667          {past.map(c => (
668            <Box flexShrink={0}>
669              <Button key={`front-${c}`} plain label={name(c)} dimColor={c === rows} onPress={c === rows ? noop : change(v => bringToFront(v, c))} />
670            </Box>
671          ))}
672        </Box>
673        <Box flexDirection="row" height={1} overflow="hidden">
674          {fitting.map((c, i) => {
675            const kind = kinds[c] ?? 'text'
676            const isSorted = view.sort?.column === c
677            return (
678              <Box width={cellWidth(c, i)} paddingRight={i < fitting.length - 1 ? GAP : 0} justifyContent={align(c)}>
679                <Button key={`sort-${c}`} plain onPress={change(v => cycleSort(v, c, isDescendingFirst(kind)))}>
680                  <Text bold color={isSorted ? ACCENT : undefined}>{headLabel(name(c), kind, view, c)}</Text>
681                </Button>
682              </Box>
683            )
684          })}
685        </Box>
686        <Box flexDirection="row" height={1} overflow="hidden">
687          {fitting.map((c, i) => (
688            <Box width={cellWidth(c, i)} paddingRight={i < fitting.length - 1 ? GAP : 0} justifyContent={align(c)}>
689              {c !== rows && <Button key={`left-${c}`} plain dimColor label=" ‹ " onPress={change(v => moveColumn(result, v, c, -1))} />}
690              {c !== rows && <Button key={`right-${c}`} plain dimColor label=" › " onPress={change(v => moveColumn(result, v, c, 1))} />}
691              {c !== rows && <Button key={`hide-${c}`} plain dimColor label=" × " onPress={change(v => hideColumn(v, c))} />}
692            </Box>
693          ))}
694        </Box>
695        {showChanges && (
696          <Box flexDirection="column">
697            <Button key="back-to-table" label="Back to table" onPress={backToTable} />
698            {changes.map(item => (
699              <Button key={item.key} plain onPress={change(item.clear)}>
700                <Text wrap="truncate-end">
701                  <Text dimColor>× </Text>
702                  {item.text}
703                </Text>
704              </Button>
705            ))}
706            <Button key="reset" plain onPress={reset}>
707              <Text color={ACCENT}>Reset to default view</Text>
708            </Button>
709          </Box>
710        )}
711        {showGroup && (
712          <Box flexDirection="column">
713            <Box flexDirection="row" height={1} overflow="hidden" columnGap={GAP}>
714              <Button key="back-to-table" label="Back to table" onPress={backToTable} />
715              <Button key="ungroup" label="ungroup" dimColor={!view.group} onPress={view.group ? change(v => ungroup(result, v)) : noop} />
716            </Box>
717            {listedShown.map(c => {
718              const role = roleOf(view, c)
719              return (
720                <Box flexDirection="row" height={1} overflow="hidden" columnGap={GAP}>
721                  <Box width={nameWidth(result)} flexShrink={0}>
722                    <Text dimColor={view.hidden.includes(c)} wrap="truncate-end">
723                      {result.columns[c] ?? ''}
724                    </Text>
725                  </Box>
726                  {GROUP_OPTIONS.map(option => {
727                    const isAvailable = option === 'group' || canAggregate(result, c, option)
728                    const press = !isAvailable
729                      ? noop
730                      : option === 'group'
731                        ? change(v => toggleKey(result, v, c))
732                        : change(v => setAggregation(result, v, c, option))
733                    return (
734                      <Button key={`role-${c}-${option}`} plain dimColor={!isAvailable} onPress={press}>
735                        <Text color={role === option ? ACCENT : undefined}>{option}</Text>
736                      </Button>
737                    )
738                  })}
739                </Box>
740              )
741            })}
742          </Box>
743        )}
744        {showTables && (
745          <Box flexDirection="column">
746            <Button key="back-to-table" label="Back to table" onPress={leaveTables} />
747            {results.slice(0, listRoom).map((entry, n) => (
748              <Button key={`table-${n}`} plain onPress={pick(entry)}>
749                <Text wrap="truncate-end">
750                  <Text color={entry.id === result.id ? ACCENT : undefined}>{entryLabel(entry)}</Text>
751                  {unseen.includes(entry.id) && <Text color={ACCENT}>{' new'}</Text>}
752                  <Text dimColor>{`  ${oneLine(entry.query)}`}</Text>
753                </Text>
754              </Button>
755            ))}
756          </Box>
757        )}
758        {showRows && result.rows.length === 0 && <Text dimColor>The table has no rows.</Text>}
759        {showRows && result.rows.length > 0 && indexes.length === 0 && <Text dimColor>No row matches the filter.</Text>}
760        {(showRows ? indexes : []).map((r, n) => (
761          <Text key={`row-${n}`} wrap="truncate-end">
762            {rowSegments(table, layout, r, fitting, view.bars).map(s =>
763              s.isDim || s.isBar ? (
764                <Text dimColor={s.isDim} backgroundColor={s.isBar ? BAR : undefined}>
765                  {s.text}
766                </Text>
767              ) : (
768                s.text
769              ),
770            )}
771          </Text>
772        ))}
773        <Text dimColor>{footer}</Text>
774      </Box>
775    )
776  })
777}
778

1import type { QueryCell, QueryTable } from '../types'
2import { capRows, guessNumeric, parseDelimited, parseJsonLines, parseJsonTable, readBox, readMarkdown, tableFromJson } from './parse'
3import type { Block, Json } from './parse'
4
5export type Format = 'box' | 'grid' | 'markdown' | 'psql' | 'dashed' | 'pandas' | 'html' | 'delimited' | 'json'
6
7export type Found = { table: QueryTable; format: Format; line: number | null }
8
9export type TextKind = 'output' | 'document' | 'message'
10
11type Reader = { format: Format; read: (lines: string[], start: number) => Block | null }
12
13const MAX_TABLES = 5
14const GRID_RULE = /^\+[-=:+]*[-=][-=:+]*\+$/
15const GRID_FOOTER = /^(\d[\d,]*) rows? in set/
16const PSQL_RULE = /^-+(\+-+)+$/
17const PSQL_FOOTER = /^\((\d[\d,]*) rows?\)$/
18const DASH_RULE = /^ *-{2,}( +-{2,})* *$/
19const AFFECTED_FOOTER = /^\((\d[\d,]*) rows? affected\)$/
20const PANDAS_FOOTER = /^\[(\d[\d,]*) rows x (\d[\d,]*) columns\]$/
21const NULL_TEXT = /^(NULL|null|None)$/
22const FENCE = /^\s*(`{3,}|~{3,})\s*([\w+-]*)\s*$/
23const HEADER_LIMIT = 64
24const GROUPED_NUMBER = /^[-+]?\d{1,3}(,\d{3})+(\.\d+)?$/
25const PLAIN_NUMBER = /^[-+]?(\d+\.?\d*|\.\d+)([eE][-+]?\d+)?$/
26const ENTITIES: Record<string, string> = { amp: '&', lt: '<', gt: '>', quot: '"', apos: "'", nbsp: ' ' }
27
28function cellOrNull(cell: string): QueryCell {
29  const trimmed = cell.trim()
30  return trimmed === '' || NULL_TEXT.test(trimmed) ? null : trimmed
31}
32
33function toCount(text: string | undefined): number | null {
34  if (text === undefined) return null
35  const n = Number(text.replace(/,/g, ''))
36  return Number.isFinite(n) ? n : null
37}
38
39function tableOf(columns: string[], rows: QueryCell[][], total = rows.length, note = ''): QueryTable {
40  return { columns, numeric: guessNumeric(rows, columns.length), rows, total, isCapped: total > rows.length, note }
41}
42
43function splitBars(line: string): string[] {
44  const parts = line.trim().split('|')
45  return parts.slice(1, parts.length - 1).map(part => part.trim())
46}
47
48function joinCells(group: string[][], width: number): string[] {
49  return Array.from({ length: width }, (_, c) =>
50    group
51      .map(cells => cells[c] ?? '')
52      .filter(cell => cell !== '')
53      .join(' '),
54  )
55}
56
57function readGrid(lines: string[], start: number): Block | null {
58  if (!GRID_RULE.test((lines[start] ?? '').trim())) return null
59  const groups: string[][][] = []
60  let current: string[][] = []
61  let headGroups = -1
62  let end = start
63  for (let i = start + 1; i < lines.length; i++) {
64    const trimmed = (lines[i] ?? '').trim()
65    if (GRID_RULE.test(trimmed)) {
66      if (current.length > 0) groups.push(current)
67      current = []
68      if (trimmed.includes('=') && headGroups < 0) headGroups = groups.length
69      end = i
70      continue
71    }
72    if (trimmed.length > 1 && trimmed.startsWith('|') && trimmed.endsWith('|')) {
73      current.push(splitBars(trimmed))
74      continue
75    }
76    break
77  }
78  if (current.length > 0 || groups.length < 2) return null
79  const width = groups[0]?.[0]?.length ?? 0
80  if (width === 0 || groups.some(group => group.some(cells => cells.length !== width))) return null
81  const headCount = headGroups > 0 ? headGroups : 1
82  const columns = joinCells(groups.slice(0, headCount).flat(), width)
83  const body = groups.slice(headCount)
84  const records = body.length === 1 ? (body[0] ?? []) : body.map(group => joinCells(group, width))
85  const rows = records.map(cells => cells.map(cellOrNull))
86  const footer = GRID_FOOTER.exec((lines[end + 1] ?? '').trim())
87  return { table: tableOf(columns, rows, Math.max(rows.length, toCount(footer?.[1]) ?? 0)), end }
88}
89
90function readPsql(lines: string[], start: number): Block | null {
91  const header = lines[start] ?? ''
92  const rule = (lines[start + 1] ?? '').trim()
93  if (!header.includes('|') || !PSQL_RULE.test(rule)) return null
94  const width = rule.split('+').length
95  const columns = header.split('|').map(cell => cell.trim())
96  if (columns.length !== width || columns.some(name => name === '')) return null
97  const rows: QueryCell[][] = []
98  let end = start + 1
99  let total: number | null = null
100  for (let i = start + 2; i < lines.length; i++) {
101    const line = lines[i] ?? ''
102    const footer = PSQL_FOOTER.exec(line.trim())
103    if (footer) {
104      total = toCount(footer[1])
105      end = i
106      break
107    }
108    const cells = line.split('|')
109    if (line.trim() === '' || cells.length !== width) break
110    rows.push(cells.map(cellOrNull))
111    end = i
112  }
113  return { table: tableOf(columns, rows, Math.max(rows.length, total ?? 0)), end }
114}
115
116type Span = { from: number; to: number }
117
118function dashSpans(rule: string): Span[] {
119  return [...rule.matchAll(/-+/g)].map(match => ({ from: match.index ?? 0, to: (match.index ?? 0) + match[0].length }))
120}
121
122function columnCuts(spans: Span[], texts: string[]): number[] {
123  const cuts: number[] = []
124  for (let k = 0; k + 1 < spans.length; k++) {
125    const from = spans[k]?.to ?? 0
126    const to = spans[k + 1]?.from ?? from
127    let best = { at: to, length: 0 }
128    let run = -1
129    for (let p = from; p <= to; p++) {
130      const isBlank = p < to && texts.every(text => (text[p] ?? ' ') === ' ')
131      if (isBlank && run < 0) run = p
132      if (!isBlank && run >= 0) {
133        if (p - run > best.length) best = { at: run, length: p - run }
134        run = -1
135      }
136    }
137    cuts.push(best.at)
138  }
139  return cuts
140}
141
142function cutLine(line: string, cuts: number[]): string[] {
143  return [0, ...cuts].map((from, k) => line.slice(from, cuts[k]).trim())
144}
145
146function readDashed(lines: string[], start: number): Block | null {
147  const header = lines[start] ?? ''
148  const rule = lines[start + 1] ?? ''
149  if (header.trim() === '' || !DASH_RULE.test(rule) || DASH_RULE.test(header)) return null
150  const spans = dashSpans(rule)
151  if (spans.length < 2) return null
152  const body: string[] = []
153  let end = start + 1
154  let total: number | null = null
155  for (let i = start + 2; i < lines.length; i++) {
156    const line = lines[i] ?? ''
157    const footer = AFFECTED_FOOTER.exec(line.trim())
158    if (footer) {
159      total = toCount(footer[1])
160      end = i
161      break
162    }
163    if (DASH_RULE.test(line)) {
164      end = i
165      break
166    }
167    if (line.trim() === '') break
168    body.push(line)
169    end = i
170  }
171  if (body.length === 0) return null
172  const cuts = columnCuts(spans, [header, ...body])
173  const columns = cutLine(header, cuts)
174  if (columns.some(name => name === '')) return null
175  const rows = body.map(line => cutLine(line, cuts).map(cellOrNull))
176  return { table: tableOf(columns, rows, Math.max(rows.length, total ?? 0)), end }
177}
178
179function readPandas(lines: string[], start: number): Block | null {
180  const header = lines[start] ?? ''
181  if (!/^ +\S/.test(header) || header.trimEnd().endsWith('\\')) return null
182  const heads = [...header.matchAll(/\S+/g)].map(match => ({ name: match[0], end: (match.index ?? 0) + match[0].length }))
183  if (heads.length === 0) return null
184  const rows: QueryCell[][] = []
185  const indexes: number[] = []
186  let hasEllipsis = false
187  let end = start
188  let footer: RegExpExecArray | null = null
189  for (let i = start + 1; i < lines.length; i++) {
190    const line = lines[i] ?? ''
191    if (line.trim() === '') {
192      footer = PANDAS_FOOTER.exec((lines[i + 1] ?? '').trim())
193      if (footer) end = i + 1
194      break
195    }
196    footer = PANDAS_FOOTER.exec(line.trim())
197    if (footer) {
198      end = i
199      break
200    }
201    const first = /^(\S+)/.exec(line)
202    if (!first) return null
203    const index = first[1] ?? ''
204    end = i
205    if (/^\.{2,3}$/.test(index)) {
206      hasEllipsis = true
207      continue
208    }
209    if (!/^\d+$/.test(index)) return null
210    const cells: string[] = []
211    let from = index.length
212    for (let j = 0; j < heads.length; j++) {
213      const to = j === heads.length - 1 ? Math.max(line.length, heads[j]?.end ?? 0) : heads[j]?.end ?? 0
214      if (j < heads.length - 1 && /\S/.test(line[to] ?? ' ') && /\S/.test(line[to - 1] ?? ' ')) return null
215      cells.push(line.slice(from, to))
216      from = to
217    }
218    indexes.push(Number(index))
219    rows.push(cells.map(cellOrNull))
220  }
221  if (rows.length === 0 || indexes.some((n, k) => k > 0 && n <= (indexes[k - 1] ?? -1))) return null
222  if (!footer && indexes[0] !== 0) return null
223  const keep = heads.map(head => !/^\.{3}$/.test(head.name))
224  const columns = heads.filter((_, c) => keep[c]).map(head => head.name)
225  const kept = rows.map(row => row.filter((_, c) => keep[c]))
226  const total = Math.max(kept.length, toCount(footer?.[1]) ?? 0)
227  const allColumns = toCount(footer?.[2])
228  const note = allColumns !== null && allColumns > columns.length ? `The output shows ${columns.length} of ${allColumns} columns` : ''
229  const table = tableOf(columns, kept, total, note)
230  return { table: { ...table, isCapped: table.isCapped || hasEllipsis }, end }
231}
232
233const READERS: Reader[] = [
234  { format: 'box', read: readBox },
235  { format: 'grid', read: readGrid },
236  { format: 'markdown', read: readMarkdown },
237  { format: 'psql', read: readPsql },
238  { format: 'dashed', read: readDashed },
239  { format: 'pandas', read: readPandas },
240]
241
242function decodeEntities(text: string): string {
243  return text.replace(/&(#x[0-9a-f]+|#\d+|[a-z]+);/gi, (whole, name: string) => {
244    if (name.startsWith('#x') || name.startsWith('#X')) return String.fromCodePoint(parseInt(name.slice(2), 16))
245    if (name.startsWith('#')) return String.fromCodePoint(Number(name.slice(1)))
246    return ENTITIES[name.toLowerCase()] ?? whole
247  })
248}
249
250function htmlText(fragment: string): string {
251  return decodeEntities(fragment.replace(/<[^>]*>/g, ' ')).replace(/\s+/g, ' ').trim()
252}
253
254export function parseHtmlTables(text: string): QueryTable[] {
255  const tables: QueryTable[] = []
256  for (const html of text.match(/<table[\s>][\s\S]*?<\/table>/gi) ?? []) {
257    const rows = html
258      .split(/(?=<tr[\s>])/i)
259      .slice(1)
260      .map(row => row.split(/(?=<t[hd][\s>])/i).slice(1).map(htmlText))
261      .filter(cells => cells.length > 0)
262    const [head, ...body] = rows
263    if (!head || body.length === 0) continue
264    const width = head.length
265    const cells = body.map(row => Array.from({ length: width }, (_, c) => cellOrNull(row[c] ?? '')))
266    tables.push(tableOf(head.map((name, c) => name || `column ${c + 1}`), cells))
267  }
268  return tables
269}
270
271function lineWidth(line: string, delimiter: string): number {
272  let width = 1
273  let isQuoted = false
274  for (const char of line) {
275    if (char === '"') isQuoted = !isQuoted
276    else if (char === delimiter && !isQuoted) width += 1
277  }
278  return isQuoted ? 0 : width
279}
280
281function splitLine(line: string, delimiter: string): string[] {
282  const cells: string[] = []
283  let cell = ''
284  let isQuoted = false
285  for (const char of line) {
286    if (char === '"') isQuoted = !isQuoted
287    else if (char === delimiter && !isQuoted) {
288      cells.push(cell.trim())
289      cell = ''
290    } else cell += char
291  }
292  cells.push(cell.trim())
293  return cells
294}
295
296function shapeOf(cell: string): string {
297  return cell
298    .replace(/[0-9]+/g, '9')
299    .replace(/[A-Z]+/g, 'A')
300    .replace(/[a-z]+/g, 'a')
301    .replace(/[^9Aa\s]+/g, '.')
302    .replace(/\s+/g, ' ')
303}
304
305function isDataShaped(header: string, first: string, delimiter: string): boolean {
306  const heads = splitLine(header, delimiter).map(shapeOf)
307  const cells = splitLine(first, delimiter).map(shapeOf)
308  const isMixedTwin = heads.some((shape, c) => /9/.test(shape) && /[^9]/.test(shape) && shape === cells[c])
309  return isMixedTwin || (heads.some(shape => /[9.]/.test(shape)) && heads.every((shape, c) => shape === cells[c]))
310}
311
312function looksLikeHeader(columns: string[]): boolean {
313  return (
314    columns.every(name => name !== '' && name.length <= HEADER_LIMIT && !/^([-*+] |\d+\. )/.test(name) && !/[.!?:]$/.test(name)) &&
315    new Set(columns).size === columns.length
316  )
317}
318
319function isRunTable(table: QueryTable, delimiter: string): boolean {
320  if (!looksLikeHeader(table.columns)) return false
321  if (delimiter === '\t') return table.rows.length >= 1
322  if (delimiter === '|') return table.rows.length >= 2
323  return table.rows.length >= 2 && (table.numeric.some(Boolean) || table.rows.length >= 5)
324}
325
326function findRuns(lines: string[], used: boolean[]): Found[] {
327  const candidates: { delimiter: string; from: number; to: number; size: number }[] = []
328  for (const delimiter of ['\t', ';', ',', '|']) {
329    let i = 0
330    while (i < lines.length) {
331      const width = used[i] ? 0 : lineWidth(lines[i] ?? '', delimiter)
332      if (width < 2) {
333        i += 1
334        continue
335      }
336      let j = i
337      while (j + 1 < lines.length && !used[j + 1] && lineWidth(lines[j + 1] ?? '', delimiter) === width) j += 1
338      if (j > i) candidates.push({ delimiter, from: i, to: j, size: width * (j - i) })
339      i = j + 1
340    }
341  }
342  candidates.sort((a, b) => b.size - a.size)
343  const found: Found[] = []
344  const taken = [...used]
345  for (const candidate of candidates) {
346    if (taken.slice(candidate.from, candidate.to + 1).some(Boolean)) continue
347    if (isDataShaped(lines[candidate.from] ?? '', lines[candidate.from + 1] ?? '', candidate.delimiter)) continue
348    const table = parseDelimited(lines.slice(candidate.from, candidate.to + 1).join('\n'), { delimiter: candidate.delimiter })
349    if (!table || !isRunTable(table, candidate.delimiter)) continue
350    for (let k = candidate.from; k <= candidate.to; k++) taken[k] = true
351    found.push({ table, format: 'delimited', line: candidate.from })
352  }
353  return found
354}
355
356function findFencedJson(lines: string[], used: boolean[]): Found[] {
357  const found: Found[] = []
358  for (let i = 0; i < lines.length; i++) {
359    const open = FENCE.exec(lines[i] ?? '')
360    if (!open || used[i]) continue
361    let close = -1
362    for (let j = i + 1; j < lines.length; j++) {
363      if ((lines[j] ?? '').trim().startsWith(open[1] ?? '```')) {
364        close = j
365        break
366      }
367    }
368    if (close < 0) break
369    const body = lines.slice(i + 1, close).join('\n').trim()
370    if (/^[[{]/.test(body)) {
371      const table = parseJsonTable(body) ?? parseJsonLines(body)
372      if (table) {
373        found.push({ table, format: 'json', line: i })
374        for (let k = i; k <= close; k++) used[k] = true
375      }
376    }
377    i = close
378  }
379  return found
380}
381
382function wholeJson(text: string): QueryTable | null {
383  const trimmed = text.trim()
384  if (!/^[[{]/.test(trimmed)) return null
385  try {
386    return tableFromJson(JSON.parse(trimmed) as Json)
387  } catch {
388    return parseJsonLines(trimmed)
389  }
390}
391
392function withPlainNumbers(table: QueryTable): QueryTable {
393  const grouped = table.columns.map((_, c) => {
394    let hasGrouped = false
395    for (const row of table.rows) {
396      const cell = row[c]
397      if (cell === null || cell === undefined || cell === '') continue
398      if (GROUPED_NUMBER.test(cell)) hasGrouped = true
399      else if (!PLAIN_NUMBER.test(cell)) return false
400    }
401    return hasGrouped
402  })
403  if (!grouped.some(Boolean)) return table
404  const rows = table.rows.map(row => row.map((cell, c) => (grouped[c] && cell ? cell.replace(/,/g, '') : cell)))
405  return { ...table, rows, numeric: table.numeric.map((isNumeric, c) => isNumeric || grouped[c] === true) }
406}
407
408function largest(found: Found[]): Found[] {
409  if (found.length <= MAX_TABLES) return found
410  const size = (f: Found) => f.table.rows.length * f.table.columns.length
411  const kept = new Set([...found].sort((a, b) => size(b) - size(a)).slice(0, MAX_TABLES))
412  return found.filter(f => kept.has(f))
413}
414
415export function findTables(text: string, kind: TextKind): Found[] {
416  if (kind === 'output') {
417    const json = wholeJson(text)
418    if (json) return [{ table: capRows(json), format: 'json', line: null }]
419  }
420  const lines = text.split(/\r?\n/)
421  const used = lines.map(() => false)
422  const found: Found[] = []
423  for (let i = 0; i < lines.length; i++) {
424    for (const reader of READERS) {
425      const block = reader.read(lines, i)
426      if (!block) continue
427      found.push({ table: block.table, format: reader.format, line: i })
428      for (let k = i; k <= block.end; k++) used[k] = true
429      i = block.end
430      break
431    }
432  }
433  found.push(...findFencedJson(lines, used))
434  if (kind !== 'document') found.push(...findRuns(lines, used))
435  if (kind !== 'message' && /<table[\s>]/i.test(text)) found.push(...parseHtmlTables(text).map(table => ({ table, format: 'html' as const, line: null })))
436  if (found.length === 0 && kind === 'output') {
437    const embedded = parseJsonTable(text)
438    if (embedded) found.push({ table: embedded, format: 'json', line: null })
439  }
440  return largest(found.sort((a, b) => (a.line ?? Infinity) - (b.line ?? Infinity))).map(f => ({
441    ...f,
442    table: capRows(f.format === 'delimited' || f.format === 'json' ? f.table : withPlainNumbers(f.table)),
443  }))
444}
445
446export function captionOf(text: string, line: number | null): string {
447  if (line === null) return ''
448  const lines = text.split(/\r?\n/)
449  for (let i = line - 1; i >= Math.max(0, line - 3); i--) {
450    const candidate = (lines[i] ?? '').trim()
451    if (FENCE.test(candidate)) continue
452    if (candidate !== '') return candidate.replace(/^#+\s*/, '').replace(/^\*\*(.*)\*\*:?$/, '$1')
453  }
454  return ''
455}
456

1import type { QueryCell, QueryTable } from '../types'
2
3export const STORE_CAP = 100000
4export const TABLE_FILE = /\.(csv|tsv|json|jsonl|ndjson)$/i
5
6const NUMBER = /^[-+]?(\d+\.?\d*|\.\d+)([eE][-+]?\d+)?$/
7const NUMERIC_TYPE = /^(u?(tiny|small|big|huge)?int(eger)?\d*|float\d*|double|real|decimal.*|numeric.*|[iuf]\d+)$/i
8const TYPE_NAME =
9  /^(u?(tiny|small|big|huge)?int(eger)?\d*|float\d*|double|real|decimal.*|numeric.*|[iuf]\d+|(var)?char.*|text|string|str|bool(ean)?|date.*|time.*|interval|blob|uuid|json|struct.*|map.*|list.*|enum.*|bit|null|duration.*|cat(egorical)?|object|.*\[\])$/i
10const ROW_FOOTER = /(>)?(\d[\d,]*) rows?(?: \((\d[\d,]*) shown\)|, (\d[\d,]*) shown)?/
11const DELIMITERS = ['\t', ';', ',', '|']
12const SAMPLE_LENGTH = 65536
13const COLUMN_FOOTER = /(\d[\d,]*) columns? \((\d[\d,]*) shown\)/
14const SHAPE_LINE = /^shape: \((\d[\d_]*), (\d[\d_]*)\)/
15const BOX_TOP = /^[┌┏╔╭]/
16const BOX_SEPARATOR = /^[├┝┞┟┠┡┢┣╞╟╠]/
17const BOX_BOTTOM = /^[└┕┖┗╘╙╚╰]/
18const BOX_ROW = /^[│┃║]/
19const BOX_BAR = /[│┃║┆┊╎╏]/
20const ELIDED = /^(·|…|\.\.\.?)$/
21const NULL_TEXT = /^(NULL|null)$/
22
23function toCount(text: string | undefined): number | null {
24  if (text === undefined) return null
25  const n = Number(text.replace(/[,_]/g, ''))
26  return Number.isFinite(n) ? n : null
27}
28
29export function guessNumeric(rows: QueryCell[][], width: number): boolean[] {
30  const numeric: boolean[] = []
31  for (let c = 0; c < width; c++) {
32    let seen = 0
33    let isNumeric = true
34    for (const row of rows) {
35      const cell = row[c]
36      if (cell === null || cell === undefined || cell === '') continue
37      seen += 1
38      if (!NUMBER.test(cell)) {
39        isNumeric = false
40        break
41      }
42    }
43    numeric.push(isNumeric && seen > 0)
44  }
45  return numeric
46}
47
48export function capRows(table: QueryTable): QueryTable {
49  if (table.rows.length <= STORE_CAP) return table
50  return {
51    ...table,
52    rows: table.rows.slice(0, STORE_CAP),
53    isCapped: true,
54    total: Math.max(table.total, table.rows.length),
55  }
56}
57
58export type Block = { table: QueryTable; end: number }
59
60function boxCells(line: string): string[] {
61  const parts = line.trim().split(BOX_BAR)
62  return parts.slice(1, parts.length - 1).map(part => part.trim())
63}
64
65function joinHead(head: string[][]): string[] {
66  const width = Math.max(...head.map(cells => cells.length))
67  return Array.from({ length: width }, (_, c) =>
68    head
69      .map(cells => cells[c] ?? '')
70      .filter(cell => cell !== '')
71      .join(' '),
72  )
73}
74
75function isTypeRow(cells: string[] | undefined): boolean {
76  return !!cells && cells.some(cell => cell !== '' && cell !== '…') && cells.every(cell => cell === '' || cell === '…' || TYPE_NAME.test(cell))
77}
78
79function boxHead(head: string[][], hasBody: boolean): { names: string[]; types: string[] | null; extra: string[][] } {
80  if (!hasBody) return { names: head[0] ?? [], types: null, extra: head.slice(1) }
81  const dashes = head.findIndex(cells => cells.length > 0 && cells.every(cell => /^-+$/.test(cell)))
82  if (dashes > 0) return { names: joinHead(head.slice(0, dashes)), types: head[dashes + 1] ?? null, extra: [] }
83  if (head.length === 2 && isTypeRow(head[1])) return { names: head[0] ?? [], types: head[1] ?? null, extra: [] }
84  return { names: joinHead(head), types: null, extra: [] }
85}
86
87function footerCounts(lines: string[], from: number, rows: number): { total: number; isTotalMinimum: boolean; isCapped: boolean; notes: string[] } {
88  let total = rows
89  let isTotalMinimum = false
90  let isCapped = false
91  const notes: string[] = []
92  for (const footer of lines.slice(from, from + 2)) {
93    const rowMatch = ROW_FOOTER.exec(footer)
94    if (rowMatch) {
95      const all = toCount(rowMatch[2])
96      const shown = toCount(rowMatch[3] ?? rowMatch[4])
97      if (all !== null) total = Math.max(total, all)
98      if (rowMatch[1] === '>') isTotalMinimum = true
99      if (shown !== null && all !== null && shown < all) isCapped = true
100    }
101    const columnMatch = COLUMN_FOOTER.exec(footer)
102    if (columnMatch) {
103      const all = toCount(columnMatch[1])
104      const shown = toCount(columnMatch[2])
105      if (all !== null && shown !== null && shown < all) notes.push(`The output shows ${shown} of ${all} columns`)
106    }
107  }
108  return { total, isTotalMinimum, isCapped, notes }
109}
110
111export function readBox(lines: string[], start: number): Block | null {
112  if (!BOX_TOP.test((lines[start] ?? '').trimStart())) return null
113  const head: string[][] = []
114  const body: string[][] = []
115  let hasBody = false
116  let end = -1
117  for (let i = start + 1; i < lines.length; i++) {
118    const trimmed = (lines[i] ?? '').trimStart()
119    if (BOX_BOTTOM.test(trimmed)) {
120      end = i
121      break
122    }
123    if (BOX_SEPARATOR.test(trimmed)) {
124      if (head.length > 0) hasBody = true
125      continue
126    }
127    if (!BOX_ROW.test(trimmed)) return null
128    if (hasBody) body.push(boxCells(trimmed))
129    else head.push(boxCells(trimmed))
130  }
131  if (end < 0 || head.length === 0) return null
132  const { names, types, extra } = boxHead(head, hasBody)
133  if (names.length === 0) return null
134  const rows: QueryCell[][] = []
135  let hasEllipsis = false
136  for (const cells of [...extra, ...body]) {
137    if (cells.length !== names.length) continue
138    if (cells.every(cell => ELIDED.test(cell) || cell === '')) {
139      hasEllipsis = true
140      continue
141    }
142    rows.push(cells.map(cell => (NULL_TEXT.test(cell) ? null : cell)))
143  }
144  const keep = names.map((name, c) => name !== '…' && (types?.[c] ?? '') !== '…')
145  const columns = names.filter((_, c) => keep[c])
146  const keptRows = rows.map(row => row.filter((_, c) => keep[c]))
147  const keptTypes = types?.filter((_, c) => keep[c]) ?? null
148  const numeric = keptTypes ? keptTypes.map(type => NUMERIC_TYPE.test(type)) : guessNumeric(keptRows, columns.length)
149  const counts = footerCounts(lines, end + 1, keptRows.length)
150  const shape = SHAPE_LINE.exec((lines[start - 1] ?? '').trim())
151  const shapeRows = toCount(shape?.[1])
152  const shapeColumns = toCount(shape?.[2])
153  const total = Math.max(counts.total, shapeRows ?? 0)
154  if (shapeColumns !== null && shapeColumns > columns.length) counts.notes.push(`The output shows ${columns.length} of ${shapeColumns} columns`)
155  return {
156    table: {
157      columns,
158      numeric,
159      rows: keptRows,
160      total,
161      isTotalMinimum: counts.isTotalMinimum,
162      isCapped: hasEllipsis || counts.isCapped || total > keptRows.length,
163      note: counts.notes.join('; '),
164    },
165    end,
166  }
167}
168
169export function parseDuckbox(text: string): QueryTable | null {
170  const lines = text.split(/\r?\n/)
171  let found: QueryTable | null = null
172  for (let i = 0; i < lines.length; i++) {
173    const read = readBox(lines, i)
174    if (read) {
175      found = read.table
176      i = read.end
177    }
178  }
179  return found ? capRows(found) : null
180}
181
182export type Json = null | boolean | number | string | Json[] | { [key: string]: Json }
183
184type RawTable = { columns: string[]; rows: Json[][] }
185
186function isPrimitive(value: Json | undefined): boolean {
187  return value === null || ['string', 'number', 'boolean'].includes(typeof value)
188}
189
190function isRecord(value: Json | undefined): value is { [key: string]: Json } {
191  return typeof value === 'object' && value !== null && !Array.isArray(value)
192}
193
194function columnName(value: Json | undefined): string | null {
195  if (typeof value === 'string') return value
196  if (isRecord(value)) {
197    for (const key of ['name', 'Name', 'columnName', 'ColumnName']) {
198      const name = value[key]
199      if (typeof name === 'string') return name
200    }
201  }
202  return null
203}
204
205function isRow(item: Json, isStrict: boolean): boolean {
206  if (!isRecord(item)) return false
207  const values = Object.values(item)
208  return isStrict ? values.every(isPrimitive) : values.some(isPrimitive)
209}
210
211function collectTables(value: Json, found: RawTable[], flags: { isTruncated: boolean }, isStrict: boolean): void {
212  if (Array.isArray(value)) {
213    if (value.length > 0 && value.every(item => isRow(item, isStrict))) {
214      const columns: string[] = []
215      for (const item of value) {
216        if (!isRecord(item)) continue
217        for (const key of Object.keys(item)) if (!columns.includes(key)) columns.push(key)
218      }
219      const rows = value.map(item => columns.map(key => (isRecord(item) ? item[key] ?? null : null)))
220      found.push({ columns, rows })
221      return
222    }
223    for (const item of value) collectTables(item, found, flags, isStrict)
224    return
225  }
226  if (!isRecord(value)) return
227  for (const [key, inner] of Object.entries(value)) {
228    if (/truncat|hasMore|moreRows/i.test(key) && inner === true) flags.isTruncated = true
229  }
230  const columns = value.columns ?? value.Columns
231  const rows = value.rows ?? value.Rows ?? value.data ?? value.Data
232  if (Array.isArray(columns) && Array.isArray(rows) && rows.every(row => Array.isArray(row))) {
233    const names = columns.map(columnName)
234    if (names.every(name => name !== null)) {
235      found.push({ columns: names as string[], rows: rows as Json[][] })
236      return
237    }
238  }
239  for (const inner of Object.values(value)) collectTables(inner, found, flags, isStrict)
240}
241
242function parseJsonValue(text: string): Json | undefined {
243  const trimmed = text.trim()
244  const attempts = [trimmed]
245  const open = trimmed.search(/[[{]/)
246  const close = Math.max(trimmed.lastIndexOf('}'), trimmed.lastIndexOf(']'))
247  if (open > 0 && close > open) attempts.push(trimmed.slice(open, close + 1))
248  for (const attempt of attempts) {
249    try {
250      return JSON.parse(attempt) as Json
251    } catch {
252      continue
253    }
254  }
255  return undefined
256}
257
258function cellOf(value: Json | undefined): QueryCell {
259  if (value === null || value === undefined) return null
260  if (typeof value === 'string') return value
261  if (typeof value === 'number' || typeof value === 'boolean') return String(value)
262  return JSON.stringify(value)
263}
264
265export function parseJsonTable(text: string): QueryTable | null {
266  const value = parseJsonValue(text)
267  return value === undefined ? null : tableFromJson(value)
268}
269
270export function parseJsonLines(text: string): QueryTable | null {
271  const values: Json[] = []
272  for (const line of text.split(/\r?\n/)) {
273    if (line.trim() === '') continue
274    try {
275      values.push(JSON.parse(line) as Json)
276    } catch {
277      return null
278    }
279  }
280  return values.length === 0 ? null : tableFromJson(values)
281}
282
283export function tableFromJson(value: Json): QueryTable | null {
284  const found: RawTable[] = []
285  const flags = { isTruncated: false }
286  collectTables(value, found, flags, true)
287  if (found.length === 0) collectTables(value, found, flags, false)
288  const first = found[0]
289  if (!first || first.columns.length === 0) return null
290  const numeric = first.columns.map((_, c) => {
291    const present = first.rows.map(row => row[c]).filter(cell => cell !== null && cell !== undefined)
292    return present.length > 0 && present.every(cell => typeof cell === 'number')
293  })
294  const rows = first.rows.map(row => first.columns.map((_, c) => cellOf(row[c])))
295  const note = found.length > 1 ? `Showing result 1 of ${found.length}` : ''
296  return { columns: first.columns, numeric, rows, total: rows.length, isCapped: flags.isTruncated, note }
297}
298
299function pipeCells(line: string): string[] {
300  let body = line.trim()
301  if (body.startsWith('|')) body = body.slice(1)
302  if (body.endsWith('|') && !body.endsWith('\\|')) body = body.slice(0, -1)
303  const cells: string[] = []
304  let cell = ''
305  for (let i = 0; i < body.length; i++) {
306    const char = body[i]
307    if (char === '\\' && body[i + 1] === '|') {
308      cell += '|'
309      i += 1
310    } else if (char === '|') {
311      cells.push(cell.trim())
312      cell = ''
313    } else {
314      cell += char
315    }
316  }
317  cells.push(cell.trim())
318  return cells
319}
320
321function markdownCell(cell: string): QueryCell {
322  const plain = cell.replace(/^(\*\*|__)(.*)\1$/, '$2').replace(/^`(.*)`$/, '$1').trim()
323  return plain === '' ? null : plain
324}
325
326const PIPE_SEPARATOR = /^\s*\|?\s*:?-{3,}:?\s*(\|\s*:?-{3,}:?\s*)*\|?\s*$/
327
328export function readMarkdown(lines: string[], start: number): Block | null {
329  const headerLine = lines[start] ?? ''
330  if (!headerLine.includes('|') || !PIPE_SEPARATOR.test(lines[start + 1] ?? '') || PIPE_SEPARATOR.test(headerLine)) return null
331  const columns = pipeCells(headerLine).map(cell => markdownCell(cell) ?? '')
332  const rows: QueryCell[][] = []
333  let end = start + 1
334  for (let j = start + 2; j < lines.length; j++) {
335    const line = lines[j] ?? ''
336    if (!line.includes('|')) break
337    const cells = pipeCells(line)
338    if (cells.length !== columns.length) break
339    rows.push(cells.map(markdownCell))
340    end = j
341  }
342  if (rows.length === 0) return null
343  return { table: { columns, numeric: guessNumeric(rows, columns.length), rows, total: rows.length, isCapped: false, note: '' }, end }
344}
345
346export function parseMarkdownTable(text: string): QueryTable | null {
347  const lines = text.split(/\r?\n/)
348  for (let i = 0; i < lines.length; i++) {
349    const read = readMarkdown(lines, i)
350    if (read) return read.table
351  }
352  return null
353}
354
355export function delimitedRecords(text: string, delimiter: string): string[][] {
356  const records: string[][] = []
357  let record: string[] = []
358  let cell = ''
359  let isQuoted = false
360  const endRecord = () => {
361    record.push(cell.trim())
362    if (record.length > 1 || record[0] !== '') records.push(record)
363    record = []
364    cell = ''
365  }
366  for (let i = 0; i < text.length; i++) {
367    const char = text[i]
368    if (isQuoted) {
369      if (char === '"' && text[i + 1] === '"') {
370        cell += '"'
371        i += 1
372      } else if (char === '"') {
373        isQuoted = false
374      } else {
375        cell += char
376      }
377    } else if (char === '"' && cell.trim() === '') {
378      isQuoted = true
379      cell = ''
380    } else if (char === delimiter) {
381      record.push(cell.trim())
382      cell = ''
383    } else if (char === '\n' || char === '\r') {
384      if (char === '\r' && text[i + 1] === '\n') i += 1
385      endRecord()
386    } else {
387      cell += char
388    }
389  }
390  if (cell !== '' || record.length > 0) endRecord()
391  return records
392}
393
394function detectDelimiter(text: string): string | null {
395  const cut = text.length > SAMPLE_LENGTH ? text.lastIndexOf('\n', SAMPLE_LENGTH) : text.length
396  const sample = text.slice(0, cut > 0 ? cut : text.length)
397  let best: { delimiter: string; width: number } | null = null
398  for (const delimiter of DELIMITERS) {
399    const records = delimitedRecords(sample, delimiter)
400    const width = records[0]?.length ?? 0
401    if (width < 2 || !records.every(record => record.length === width)) continue
402    if (!best || width > best.width) best = { delimiter, width }
403  }
404  return best?.delimiter ?? null
405}
406
407export function parseDelimited(text: string, options: { delimiter?: string; isFile?: boolean } = {}): QueryTable | null {
408  const isFile = options.isFile === true
409  const delimiter = options.delimiter ?? detectDelimiter(text) ?? (isFile ? ',' : null)
410  if (delimiter === null) return null
411  const [columns, ...records] = delimitedRecords(text, delimiter)
412  if (!columns || columns.length < (isFile ? 1 : 2)) return null
413  if (records.length === 0 && !isFile) return null
414  if (!records.every(cells => cells.length === columns.length)) return null
415  const rows = records.map(cells => cells.map(cell => (cell === '' ? null : cell)))
416  return { columns, numeric: guessNumeric(rows, columns.length), rows, total: rows.length, isCapped: false, note: '' }
417}
418
419export function parseFile(path: string, text: string): QueryTable | null {
420  const body = text.replace(/^/, '')
421  const extension = /\.([a-z]+)$/i.exec(path)?.[1]?.toLowerCase()
422  const table =
423    extension === 'tsv'
424      ? parseDelimited(body, { delimiter: '\t', isFile: true })
425      : extension === 'csv'
426        ? parseDelimited(body, { isFile: true })
427        : extension === 'json'
428          ? parseJsonTable(body) ?? parseJsonLines(body)
429          : extension === 'jsonl' || extension === 'ndjson'
430            ? parseJsonLines(body)
431            : null
432  return table ? capRows(table) : null
433}
434
435export function parsePowerBi(text: string, maxRows: number): QueryTable | null {
436  const table = parseJsonTable(text) ?? parseMarkdownTable(text) ?? parseDuckbox(text) ?? parseDelimited(text)
437  if (!table) return null
438  if (table.rows.length >= maxRows && !table.isCapped) {
439    const reached = `The result reached maxRows (${maxRows}), so more rows may exist`
440    return capRows({ ...table, isCapped: true, note: table.note ? `${table.note}; ${reached}` : reached })
441  }
442  return capRows(table)
443}
444

1import type { QueryCell, QueryTable } from '../types'
2import { STORE_CAP } from './parse'
3
4type ZipEntry = { method: number; size: number; compressedSize: number; offset: number }
5
6type Zip = { bytes: Uint8Array; entries: Map<string, ZipEntry> }
7
8type BitStream = { data: Uint8Array; pos: number; buffer: number; count: number }
9
10type Output = { bytes: Uint8Array; length: number }
11
12type Huffman = { table: Uint16Array; bits: number }
13
14type NumberKind = 'number' | 'date' | 'duration'
15
16type Relationship = { type: string; target: string }
17
18type Sheet = { name: string; path: string }
19
20type Book = {
21  sheets: Sheet[]
22  strings: string[]
23  styles: NumberKind[]
24  is1904: boolean
25}
26
27type Cell = { text: string; isNumber: boolean } | null
28
29const END_OF_DIRECTORY = 0x06054b50
30const DIRECTORY_ENTRY = 0x02014b50
31const LOCAL_HEADER = 0x04034b50
32
33const LENGTH_BASE = [3, 4, 5, 6, 7, 8, 9, 10, 11, 13, 15, 17, 19, 23, 27, 31, 35, 43, 51, 59, 67, 83, 99, 115, 131, 163, 195, 227, 258]
34const LENGTH_EXTRA = [0, 0, 0, 0, 0, 0, 0, 0, 1, 1, 1, 1, 2, 2, 2, 2, 3, 3, 3, 3, 4, 4, 4, 4, 5, 5, 5, 5, 0]
35const DISTANCE_BASE = [
36  1, 2, 3, 4, 5, 7, 9, 13, 17, 25, 33, 49, 65, 97, 129, 193, 257, 385, 513, 769, 1025, 1537, 2049, 3073, 4097, 6145, 8193,
37  12289, 16385, 24577,
38]
39const DISTANCE_EXTRA = [0, 0, 0, 0, 1, 1, 2, 2, 3, 3, 4, 4, 5, 5, 6, 6, 7, 7, 8, 8, 9, 9, 10, 10, 11, 11, 12, 12, 13, 13]
40const CODE_LENGTH_ORDER = [16, 17, 18, 0, 8, 7, 9, 6, 10, 5, 11, 4, 12, 3, 13, 2, 14, 1, 15]
41
42const NUMBER = /^[-+]?(\d+\.?\d*|\.\d+)([eE][-+]?\d+)?$/
43const ATTRIBUTE = /([\w:.-]+)\s*=\s*(?:"([^"]*)"|'([^']*)')/g
44const ENTITY = /&(#x[0-9a-fA-F]+|#\d+|amp|lt|gt|quot|apos);/g
45const ESCAPED_CHAR = /_x([0-9a-fA-F]{4})_/g
46const NAMED_ENTITIES: Record<string, string> = { amp: '&', lt: '<', gt: '>', quot: '"', apos: "'" }
47const RELATIONSHIP = /<(?:\w+:)?Relationship\b([^>]*)>/g
48const SHEET = /<(?:\w+:)?sheet\b([^>]*)>/g
49const WORKBOOK_PROPERTIES = /<(?:\w+:)?workbookPr\b([^>]*)>/
50const SHARED_ITEM = /<(?:\w+:)?si\b([^>]*?)(?:\/>|>([\s\S]*?)<\/(?:\w+:)?si>)/g
51const PHONETIC = /<(?:\w+:)?rPh\b[\s\S]*?<\/(?:\w+:)?rPh>/g
52const TEXT = /<(?:\w+:)?t(?:\s[^>]*)?>([\s\S]*?)<\/(?:\w+:)?t>/g
53const NUMBER_FORMAT = /<(?:\w+:)?numFmt\b([^>]*)>/g
54const CELL_FORMATS = /<(?:\w+:)?cellXfs\b[^>]*>([\s\S]*?)<\/(?:\w+:)?cellXfs>/
55const CELL_FORMAT = /<(?:\w+:)?xf\b([^>]*)>/g
56const ROW = /<(?:\w+:)?row\b([^>]*?)(?:\/>|>([\s\S]*?)<\/(?:\w+:)?row>)/g
57const CELL = /<(?:\w+:)?c\b([^>]*?)(?:\/>|>([\s\S]*?)<\/(?:\w+:)?c>)/g
58const VALUE = /<(?:\w+:)?v(?:\s[^>]*)?>([\s\S]*?)<\/(?:\w+:)?v>/
59const INLINE = /<(?:\w+:)?is\b[^>]*>([\s\S]*?)<\/(?:\w+:)?is>/
60const CELL_REF = /^\$?([A-Za-z]{1,3})\$?\d*$/
61const FORMAT_LITERAL = /"[^"]*"|\\.|[_*].|\[[^\]]*\]/g
62const ELAPSED = /^\[(h+|m+|s+)\]$/i
63const DATE_TOKEN = /[ymdhs]/i
64const DATE_FORMAT_IDS = new Set([14, 15, 16, 17, 18, 19, 20, 21, 22, 45, 47])
65const DURATION_FORMAT_ID = 46
66const DAY_MS = 86400000
67const EPOCH_1900 = Date.UTC(1899, 11, 30)
68const EPOCH_1904 = Date.UTC(1904, 0, 1)
69
70function decodeBase64(base64: string): Uint8Array {
71  const binary = atob(base64.replace(/\s+/g, ''))
72  const bytes = new Uint8Array(binary.length)
73  for (let i = 0; i < binary.length; i++) bytes[i] = binary.charCodeAt(i)
74  return bytes
75}
76
77function uint16(bytes: Uint8Array, at: number): number {
78  return (bytes[at] ?? 0) | ((bytes[at + 1] ?? 0) << 8)
79}
80
81function uint32(bytes: Uint8Array, at: number): number {
82  return (uint16(bytes, at) | (uint16(bytes, at + 2) << 16)) >>> 0
83}
84
85function findEndOfDirectory(bytes: Uint8Array): number {
86  const last = Math.max(0, bytes.length - 22 - 65535)
87  for (let at = bytes.length - 22; at >= last; at--) {
88    if (uint32(bytes, at) === END_OF_DIRECTORY) return at
89  }
90  return -1
91}
92
93function readZip(bytes: Uint8Array): Zip | null {
94  const end = findEndOfDirectory(bytes)
95  if (end < 0) return null
96  const count = uint16(bytes, end + 10)
97  let at = uint32(bytes, end + 16)
98  const decoder = new TextDecoder()
99  const entries = new Map<string, ZipEntry>()
100  for (let i = 0; i < count; i++) {
101    if (uint32(bytes, at) !== DIRECTORY_ENTRY) break
102    const nameLength = uint16(bytes, at + 28)
103    const extraLength = uint16(bytes, at + 30)
104    const commentLength = uint16(bytes, at + 32)
105    const name = decoder.decode(bytes.subarray(at + 46, at + 46 + nameLength))
106    entries.set(name.replace(/\\/g, '/').toLowerCase(), {
107      method: uint16(bytes, at + 10),
108      compressedSize: uint32(bytes, at + 20),
109      size: uint32(bytes, at + 24),
110      offset: uint32(bytes, at + 42),
111    })
112    at += 46 + nameLength + extraLength + commentLength
113  }
114  return entries.size > 0 ? { bytes, entries } : null
115}
116
117function readEntry(zip: Zip, name: string): Uint8Array | null {
118  const entry = zip.entries.get(name.toLowerCase())
119  if (!entry) return null
120  const { bytes } = zip
121  if (uint32(bytes, entry.offset) !== LOCAL_HEADER) return null
122  const start = entry.offset + 30 + uint16(bytes, entry.offset + 26) + uint16(bytes, entry.offset + 28)
123  const data = bytes.subarray(start, start + entry.compressedSize)
124  if (entry.method === 0) return data
125  if (entry.method === 8) return inflate(data, entry.size)
126  return null
127}
128
129function readText(zip: Zip, name: string): string | null {
130  const bytes = readEntry(zip, name)
131  return bytes ? new TextDecoder().decode(bytes) : null
132}
133
134function needBits(stream: BitStream, bits: number): void {
135  while (stream.count < bits) {
136    if (stream.pos >= stream.data.length + 4) throw new Error('deflate data ends early')
137    stream.buffer |= (stream.data[stream.pos] ?? 0) << stream.count
138    stream.pos += 1
139    stream.count += 8
140  }
141}
142
143function readBits(stream: BitStream, bits: number): number {
144  if (bits === 0) return 0
145  needBits(stream, bits)
146  const value = stream.buffer & ((1 << bits) - 1)
147  stream.buffer >>>= bits
148  stream.count -= bits
149  return value
150}
151
152function buildHuffman(lengths: ArrayLike<number>): Huffman {
153  let bits = 0
154  const counts = new Array<number>(16).fill(0)
155  for (let symbol = 0; symbol < lengths.length; symbol++) {
156    const length = lengths[symbol] ?? 0
157    counts[length] = (counts[length] ?? 0) + 1
158    if (length > bits) bits = length
159  }
160  if (bits === 0) return { table: new Uint16Array(2), bits: 1 }
161  const next = new Array<number>(16).fill(0)
162  let code = 0
163  for (let length = 1; length <= bits; length++) {
164    code = (code + (length === 1 ? 0 : counts[length - 1] ?? 0)) << 1
165    next[length] = code
166  }
167  const table = new Uint16Array(1 << bits)
168  for (let symbol = 0; symbol < lengths.length; symbol++) {
169    const length = lengths[symbol] ?? 0
170    if (length === 0) continue
171    const assigned = next[length] ?? 0
172    next[length] = assigned + 1
173    let reversed = 0
174    for (let i = 0; i < length; i++) reversed |= ((assigned >> i) & 1) << (length - 1 - i)
175    for (let i = reversed; i < table.length; i += 1 << length) table[i] = (symbol << 4) | length
176  }
177  return { table, bits }
178}
179
180function decodeSymbol(stream: BitStream, huffman: Huffman): number {
181  needBits(stream, huffman.bits)
182  const entry = huffman.table[stream.buffer & ((1 << huffman.bits) - 1)] ?? 0
183  const length = entry & 15
184  if (length === 0) throw new Error('invalid Huffman code')
185  stream.buffer >>>= length
186  stream.count -= length
187  return entry >> 4
188}
189
190function reserve(output: Output, extra: number): void {
191  if (output.length + extra <= output.bytes.length) return
192  let size = Math.max(output.bytes.length * 2, 1024)
193  while (size < output.length + extra) size *= 2
194  const grown = new Uint8Array(size)
195  grown.set(output.bytes.subarray(0, output.length))
196  output.bytes = grown
197}
198
199function copyStored(stream: BitStream, output: Output): void {
200  stream.pos -= stream.count >> 3
201  stream.buffer = 0
202  stream.count = 0
203  const { data } = stream
204  const length = uint16(data, stream.pos)
205  const check = uint16(data, stream.pos + 2)
206  if ((length ^ 0xffff) !== check) throw new Error('stored block length mismatch')
207  const start = stream.pos + 4
208  if (start + length > data.length) throw new Error('stored block ends early')
209  reserve(output, length)
210  output.bytes.set(data.subarray(start, start + length), output.length)
211  output.length += length
212  stream.pos = start + length
213}
214
215function fixedTrees(): [Huffman, Huffman] {
216  const lengths = new Array<number>(288)
217  for (let symbol = 0; symbol < 288; symbol++) {
218    lengths[symbol] = symbol < 144 ? 8 : symbol < 256 ? 9 : symbol < 280 ? 7 : 8
219  }
220  return [buildHuffman(lengths), buildHuffman(new Array<number>(30).fill(5))]
221}
222
223function dynamicTrees(stream: BitStream): [Huffman, Huffman] {
224  const literalCount = readBits(stream, 5) + 257
225  const distanceCount = readBits(stream, 5) + 1
226  const codeLengthCount = readBits(stream, 4) + 4
227  const codeLengths = new Array<number>(19).fill(0)
228  for (let i = 0; i < codeLengthCount; i++) codeLengths[CODE_LENGTH_ORDER[i] ?? 0] = readBits(stream, 3)
229  const codeLengthTree = buildHuffman(codeLengths)
230  const lengths: number[] = []
231  while (lengths.length < literalCount + distanceCount) {
232    const symbol = decodeSymbol(stream, codeLengthTree)
233    if (symbol < 16) {
234      lengths.push(symbol)
235      continue
236    }
237    const previous = symbol === 16 ? lengths[lengths.length - 1] : 0
238    if (previous === undefined) throw new Error('repeat without a previous length')
239    const repeat = symbol === 16 ? 3 + readBits(stream, 2) : symbol === 17 ? 3 + readBits(stream, 3) : 11 + readBits(stream, 7)
240    for (let i = 0; i < repeat; i++) lengths.push(previous)
241  }
242  if (lengths.length > literalCount + distanceCount) throw new Error('too many code lengths')
243  return [buildHuffman(lengths.slice(0, literalCount)), buildHuffman(lengths.slice(literalCount))]
244}
245
246function inflateBlock(stream: BitStream, output: Output, literals: Huffman, distances: Huffman): void {
247  while (true) {
248    const symbol = decodeSymbol(stream, literals)
249    if (symbol < 256) {
250      reserve(output, 1)
251      output.bytes[output.length] = symbol
252      output.length += 1
253      continue
254    }
255    if (symbol === 256) return
256    const index = symbol - 257
257    const base = LENGTH_BASE[index]
258    if (base === undefined) throw new Error('invalid length symbol')
259    const length = base + readBits(stream, LENGTH_EXTRA[index] ?? 0)
260    const distanceSymbol = decodeSymbol(stream, distances)
261    const distanceBase = DISTANCE_BASE[distanceSymbol]
262    if (distanceBase === undefined) throw new Error('invalid distance symbol')
263    const distance = distanceBase + readBits(stream, DISTANCE_EXTRA[distanceSymbol] ?? 0)
264    if (distance > output.length) throw new Error('distance before the start')
265    reserve(output, length)
266    const bytes = output.bytes
267    let from = output.length - distance
268    for (let i = 0; i < length; i++) {
269      bytes[output.length] = bytes[from] ?? 0
270      output.length += 1
271      from += 1
272    }
273  }
274}
275
276function inflate(data: Uint8Array, expectedSize: number): Uint8Array {
277  const stream: BitStream = { data, pos: 0, buffer: 0, count: 0 }
278  const output: Output = { bytes: new Uint8Array(Math.max(expectedSize, 1024)), length: 0 }
279  let isFinal = false
280  while (!isFinal) {
281    isFinal = readBits(stream, 1) === 1
282    const type = readBits(stream, 2)
283    if (type === 0) copyStored(stream, output)
284    else if (type === 1) inflateBlock(stream, output, ...fixedTrees())
285    else if (type === 2) inflateBlock(stream, output, ...dynamicTrees(stream))
286    else throw new Error('invalid block type')
287  }
288  return output.bytes.subarray(0, output.length)
289}
290
291function decodeEntities(text: string): string {
292  if (!text.includes('&')) return text
293  return text.replace(ENTITY, (match, name: string) => {
294    const named = NAMED_ENTITIES[name]
295    if (named !== undefined) return named
296    const code = name.startsWith('#x') ? parseInt(name.slice(2), 16) : parseInt(name.slice(1), 10)
297    return Number.isInteger(code) && code >= 0 && code <= 0x10ffff ? String.fromCodePoint(code) : match
298  })
299}
300
301function decodeString(text: string): string {
302  const decoded = decodeEntities(text)
303  return decoded.includes('_x') ? decoded.replace(ESCAPED_CHAR, (_, hex: string) => String.fromCharCode(parseInt(hex, 16))) : decoded
304}
305
306function readAttributes(tag: string): Record<string, string> {
307  const attributes: Record<string, string> = {}
308  for (const match of tag.matchAll(ATTRIBUTE)) {
309    const name = match[1]
310    if (name !== undefined) attributes[name] = decodeEntities(match[2] ?? match[3] ?? '')
311  }
312  return attributes
313}
314
315function prefixedId(attributes: Record<string, string>): string | undefined {
316  for (const [name, value] of Object.entries(attributes)) if (name.endsWith(':id')) return value
317  return undefined
318}
319
320function relsPath(part: string): string {
321  const slash = part.lastIndexOf('/')
322  return `${part.slice(0, slash + 1)}_rels/${part.slice(slash + 1)}.rels`
323}
324
325function resolvePath(part: string, target: string): string {
326  if (target.startsWith('/')) return target.slice(1)
327  const parts = part.split('/').slice(0, -1)
328  for (const piece of target.split('/')) {
329    if (piece === '..') parts.pop()
330    else if (piece !== '.' && piece !== '') parts.push(piece)
331  }
332  return parts.join('/')
333}
334
335function readRelationships(zip: Zip, part: string): Map<string, Relationship> {
336  const relationships = new Map<string, Relationship>()
337  const xml = readText(zip, relsPath(part))
338  if (xml === null) return relationships
339  for (const match of xml.matchAll(RELATIONSHIP)) {
340    const attributes = readAttributes(match[1] ?? '')
341    if (attributes.Id === undefined || attributes.Target === undefined || attributes.TargetMode === 'External') continue
342    relationships.set(attributes.Id, { type: attributes.Type ?? '', target: resolvePath(part, attributes.Target) })
343  }
344  return relationships
345}
346
347function findTarget(relationships: Map<string, Relationship>, type: string, fallback: string): string {
348  for (const relationship of relationships.values()) if (relationship.type.endsWith(`/${type}`)) return relationship.target
349  return fallback
350}
351
352function richText(xml: string): string {
353  let text = ''
354  for (const match of xml.replace(PHONETIC, '').matchAll(TEXT)) text += match[1] ?? ''
355  return decodeString(text)
356}
357
358function readSharedStrings(xml: string | null): string[] {
359  if (xml === null) return []
360  return Array.from(xml.matchAll(SHARED_ITEM), match => richText(match[2] ?? ''))
361}
362
363function formatKind(code: string): NumberKind {
364  let isElapsed = false
365  const bare = code.replace(FORMAT_LITERAL, token => {
366    if (ELAPSED.test(token)) isElapsed = true
367    return ''
368  })
369  if (isElapsed) return 'duration'
370  return DATE_TOKEN.test(bare) ? 'date' : 'number'
371}
372
373function builtinKind(id: number): NumberKind {
374  if (id === DURATION_FORMAT_ID) return 'duration'
375  return DATE_FORMAT_IDS.has(id) ? 'date' : 'number'
376}
377
378function readStyles(xml: string | null): NumberKind[] {
379  if (xml === null) return []
380  const custom = new Map<number, string>()
381  for (const match of xml.matchAll(NUMBER_FORMAT)) {
382    const attributes = readAttributes(match[1] ?? '')
383    const id = Number(attributes.numFmtId)
384    if (Number.isInteger(id) && attributes.formatCode !== undefined) custom.set(id, attributes.formatCode)
385  }
386  const section = CELL_FORMATS.exec(xml)?.[1] ?? ''
387  return Array.from(section.matchAll(CELL_FORMAT), match => {
388    const id = Number(readAttributes(match[1] ?? '').numFmtId ?? 0)
389    const code = custom.get(id)
390    return code === undefined ? builtinKind(id) : formatKind(code)
391  })
392}
393
394function readSheets(xml: string, relationships: Map<string, Relationship>): Sheet[] {
395  const sheets: Sheet[] = []
396  for (const match of xml.matchAll(SHEET)) {
397    const attributes = readAttributes(match[1] ?? '')
398    const id = attributes['r:id'] ?? prefixedId(attributes)
399    const relationship = id === undefined ? undefined : relationships.get(id)
400    if (relationship) sheets.push({ name: attributes.name ?? '', path: relationship.target })
401  }
402  return sheets
403}
404
405function readBook(zip: Zip): Book | null {
406  const workbookPath = findTarget(readRelationships(zip, ''), 'officeDocument', 'xl/workbook.xml')
407  const workbook = readText(zip, workbookPath)
408  if (workbook === null) return null
409  const relationships = readRelationships(zip, workbookPath)
410  const sheets = readSheets(workbook, relationships)
411  if (sheets.length === 0) return null
412  const properties = readAttributes(WORKBOOK_PROPERTIES.exec(workbook)?.[1] ?? '')
413  const date1904 = properties.date1904 ?? ''
414  return {
415    sheets,
416    strings: readSharedStrings(readText(zip, findTarget(relationships, 'sharedStrings', 'xl/sharedStrings.xml'))),
417    styles: readStyles(readText(zip, findTarget(relationships, 'styles', 'xl/styles.xml'))),
418    is1904: date1904 === '1' || date1904.toLowerCase() === 'true',
419  }
420}
421
422function pad(value: number, width = 2): string {
423  return String(value).padStart(width, '0')
424}
425
426function clockText(seconds: number): string {
427  return `${pad(Math.floor(seconds / 3600))}:${pad(Math.floor(seconds / 60) % 60)}:${pad(seconds % 60)}`
428}
429
430function dateText(serial: number, is1904: boolean): string {
431  let days = Math.floor(serial)
432  let seconds = Math.round((serial - days) * 86400)
433  if (seconds === 86400) {
434    days += 1
435    seconds = 0
436  }
437  if (days === 0) return clockText(seconds)
438  const shifted = !is1904 && days >= 1 && days < 60 ? days + 1 : days
439  const date = new Date((is1904 ? EPOCH_1904 : EPOCH_1900) + shifted * DAY_MS)
440  const day = `${pad(date.getUTCFullYear(), 4)}-${pad(date.getUTCMonth() + 1)}-${pad(date.getUTCDate())}`
441  return seconds === 0 ? day : `${day} ${clockText(seconds)}`
442}
443
444function durationText(serial: number): string {
445  const seconds = Math.round(Math.abs(serial) * 86400)
446  const text = `${Math.floor(seconds / 3600)}:${pad(Math.floor(seconds / 60) % 60)}:${pad(seconds % 60)}`
447  return serial < 0 ? `-${text}` : text
448}
449
450function numberCell(value: string, kind: NumberKind, is1904: boolean): Cell {
451  const serial = Number(value)
452  if (kind === 'number' || !Number.isFinite(serial)) return { text: value, isNumber: NUMBER.test(value) }
453  return { text: kind === 'date' ? dateText(serial, is1904) : durationText(serial), isNumber: false }
454}
455
456function textCell(text: string): Cell {
457  return text === '' ? null : { text, isNumber: false }
458}
459
460function readCell(attributes: Record<string, string>, inner: string, book: Book): Cell {
461  const type = attributes.t ?? 'n'
462  if (type === 'inlineStr') {
463    const inline = INLINE.exec(inner)
464    if (inline) return textCell(richText(inline[1] ?? ''))
465  }
466  const raw = VALUE.exec(inner)?.[1]
467  if (raw === undefined) return null
468  if (type === 's') return textCell(book.strings[Number(raw)] ?? '')
469  if (type === 'b') return { text: raw.trim() === '1' || raw.trim() === 'true' ? 'TRUE' : 'FALSE', isNumber: false }
470  if (type === 'str' || type === 'inlineStr' || type === 'e' || type === 'd') return textCell(decodeString(raw))
471  const value = decodeEntities(raw).trim()
472  if (value === '') return null
473  return numberCell(value, book.styles[Number(attributes.s ?? 0)] ?? 'number', book.is1904)
474}
475
476function columnIndex(ref: string | undefined): number | null {
477  const letters = ref === undefined ? undefined : CELL_REF.exec(ref)?.[1]
478  if (letters === undefined) return null
479  let index = 0
480  for (const letter of letters.toUpperCase()) index = index * 26 + letter.charCodeAt(0) - 64
481  return index - 1
482}
483
484function readRow(xml: string, book: Book): { cells: QueryCell[]; numbers: boolean[]; width: number } {
485  const cells: QueryCell[] = []
486  const numbers: boolean[] = []
487  let column = -1
488  let width = 0
489  for (const match of xml.matchAll(CELL)) {
490    const attributes = readAttributes(match[1] ?? '')
491    column = columnIndex(attributes.r) ?? column + 1
492    const cell = readCell(attributes, match[2] ?? '', book)
493    if (cell === null) continue
494    cells[column] = cell.text
495    numbers[column] = cell.isNumber
496    width = Math.max(width, column + 1)
497  }
498  return { cells, numbers, width }
499}
500
501function fill(cells: QueryCell[], width: number): QueryCell[] {
502  return Array.from({ length: width }, (_, c) => cells[c] ?? null)
503}
504
505function readTable(xml: string, book: Book): QueryTable | null {
506  let header: QueryCell[] | null = null
507  const rows: QueryCell[][] = []
508  const seen: boolean[] = []
509  const notNumber: boolean[] = []
510  let total = 0
511  let width = 0
512  for (const match of xml.matchAll(ROW)) {
513    const row = readRow(match[2] ?? '', book)
514    if (row.width === 0) continue
515    width = Math.max(width, row.width)
516    if (header === null) {
517      header = row.cells
518      continue
519    }
520    total += 1
521    if (rows.length < STORE_CAP) rows.push(row.cells)
522    for (let c = 0; c < row.width; c++) {
523      if (row.cells[c] === undefined) continue
524      seen[c] = true
525      if (!row.numbers[c]) notNumber[c] = true
526    }
527  }
528  if (header === null) return null
529  const names = fill(header, width)
530  return {
531    columns: names.map((name, c) => name ?? `column ${c + 1}`),
532    numeric: names.map((_, c) => seen[c] === true && notNumber[c] !== true),
533    rows: rows.map(cells => fill(cells, width)),
534    total,
535    isCapped: total > STORE_CAP,
536    note: '',
537  }
538}
539
540export function parseXlsx(base64: string): QueryTable | null {
541  try {
542    const zip = readZip(decodeBase64(base64))
543    if (!zip) return null
544    const book = readBook(zip)
545    const first = book?.sheets[0]
546    if (!book || !first) return null
547    const xml = readText(zip, first.path)
548    if (xml === null) return null
549    const table = readTable(xml, book)
550    if (!table) return null
551    const count = book.sheets.length
552    return count > 1 ? { ...table, note: `Sheet 1 of ${count} (${first.name})` } : table
553  } catch {
554    return null
555  }
556}
557

1import type { QueryAggregation, QueryCell, QueryGroup, QueryMeasure, QueryResult, QuerySort, QueryView } from '../types'
2
3export const MIN_WIDTH = 9
4export const GAP = 2
5export const CONTEXT_ROWS = 10
6export const MISSING = '–'
7export const ROWS_NAME = 'rows'
8export const MEASURES: QueryMeasure[] = ['off', 'sum', 'mean', 'min', 'max', 'count', 'distinct']
9
10export type ColumnKind = 'measure' | 'year' | 'code' | 'date' | 'text'
11
12export type Role = 'group' | QueryMeasure
13
14export type Table = {
15  columns: string[]
16  numeric: boolean[]
17  kinds: ColumnKind[]
18  rows: QueryCell[][]
19}
20
21export type Shown = { table: Table; order: number[]; kept: number[]; matched: number }
22
23export type Computed = { shown: Shown; layout: Layout }
24
25type Scale = { lo: number; hi: number }
26
27export type Layout = {
28  kinds: ColumnKind[]
29  cells: string[][]
30  widths: number[]
31  scales: (Scale | null)[]
32}
33
34export type Segment = { text: string; isDim: boolean; isBar: boolean }
35
36export type Change = { key: string; text: string; clear: (view: QueryView) => QueryView }
37
38const YEAR_NAME = /(^|[^a-z])(year|yr)([^a-z]|$)/i
39const CODE_NAME = /(^|[^a-z])(code|id|key)([^a-z]|$)/i
40const DATE_VALUE = /^\d{4}-\d{2}-\d{2}/
41const NUMBER_VALUE = /^(-?)(\d+)(\.\d+)?$/
42
43export function isMissing(cell: QueryCell | undefined): boolean {
44  return cell === null || cell === undefined || cell === '' || /^-?nan$/i.test(cell)
45}
46
47export function freshView(result: QueryResult): QueryView {
48  return {
49    resultId: result.id,
50    sort: null,
51    filter: '',
52    order: result.columns.map((_, c) => c),
53    hidden: [],
54    bars: false,
55    group: null,
56  }
57}
58
59export function columnKinds(result: QueryResult): ColumnKind[] {
60  return result.columns.map((name, c) => {
61    if (result.numeric[c]) {
62      if (YEAR_NAME.test(name)) return 'year'
63      if (CODE_NAME.test(name)) return 'code'
64      return 'measure'
65    }
66    const values = result.rows.map(row => row[c]).filter(cell => !isMissing(cell))
67    return values.length > 0 && values.every(cell => DATE_VALUE.test(cell ?? '')) ? 'date' : 'text'
68  })
69}
70
71export function sourceTable(result: QueryResult): Table {
72  return { columns: result.columns, numeric: result.numeric, kinds: columnKinds(result), rows: result.rows }
73}
74
75export function rowsColumn(result: QueryResult): number {
76  return result.columns.length
77}
78
79export function isDescendingFirst(kind: ColumnKind): boolean {
80  return kind === 'measure' || kind === 'year' || kind === 'date'
81}
82
83export function cycleSort(view: QueryView, column: number, descendingFirst: boolean): QueryView {
84  if (!view.sort || view.sort.column !== column) {
85    return { ...view, sort: { column, isDescending: descendingFirst } }
86  }
87  return { ...view, sort: { column, isDescending: !view.sort.isDescending } }
88}
89
90export function groupKeys(view: QueryView): number[] {
91  const keys = view.group?.keys ?? []
92  return view.order.filter(c => keys.includes(c))
93}
94
95export function shownOrder(result: QueryResult, view: QueryView): number[] {
96  if (!view.group) return view.order
97  const keys = groupKeys(view)
98  return [...keys, rowsColumn(result), ...view.order.filter(c => !keys.includes(c) && roleOf(view, c) !== 'off')]
99}
100
101export function moveColumn(result: QueryResult, view: QueryView, column: number, step: -1 | 1): QueryView {
102  const shown = shownOrder(result, view)
103  const at = shown.indexOf(column)
104  const other = at < 0 ? undefined : shown[at + step]
105  if (other === undefined || !view.order.includes(column) || !view.order.includes(other)) return view
106  return { ...view, order: view.order.map(c => (c === column ? other : c === other ? column : c)) }
107}
108
109export function hideColumn(view: QueryView, column: number): QueryView {
110  if (!view.order.includes(column) || view.order.length <= 1) return view
111  return {
112    ...view,
113    order: view.order.filter(c => c !== column),
114    hidden: [...view.hidden, column],
115    sort: view.sort?.column === column ? null : view.sort,
116  }
117}
118
119export function showColumn(view: QueryView, column: number): QueryView {
120  if (!view.hidden.includes(column)) return view
121  const order = [...view.order]
122  const neighbours = order.filter(c => c < column)
123  const neighbour = neighbours.length === 0 ? -1 : Math.max(...neighbours)
124  order.splice(neighbour < 0 ? 0 : order.indexOf(neighbour) + 1, 0, column)
125  return { ...view, order, hidden: view.hidden.filter(c => c !== column) }
126}
127
128export function bringToFront(view: QueryView, column: number): QueryView {
129  if (!view.order.includes(column)) return view
130  return { ...view, order: [column, ...view.order.filter(c => c !== column)] }
131}
132
133export function startGroup(result: QueryResult): QueryGroup {
134  return { keys: [], aggregations: result.columns.map(() => 'off') }
135}
136
137export function canAggregate(result: QueryResult, column: number, measure: QueryMeasure): boolean {
138  return (measure !== 'sum' && measure !== 'mean') || result.numeric[column] === true
139}
140
141export function roleOf(view: QueryView, column: number): Role | null {
142  if (!view.group || !view.order.includes(column)) return null
143  return view.group.keys.includes(column) ? 'group' : view.group.aggregations[column] ?? 'off'
144}
145
146export function toggleKey(result: QueryResult, view: QueryView, column: number): QueryView {
147  const group = view.group ?? startGroup(result)
148  const keys = roleOf(view, column) === 'group' ? group.keys.filter(c => c !== column) : [...group.keys.filter(c => c !== column), column]
149  return showColumn({ ...view, group: { ...group, keys } }, column)
150}
151
152export function setAggregation(result: QueryResult, view: QueryView, column: number, measure: QueryMeasure): QueryView {
153  if (!canAggregate(result, column, measure)) return view
154  const group = view.group ?? startGroup(result)
155  const aggregations = [...group.aggregations]
156  aggregations[column] = measure
157  const sort = measure === 'off' && view.sort?.column === column ? null : view.sort
158  return showColumn({ ...view, sort, group: { keys: group.keys.filter(c => c !== column), aggregations } }, column)
159}
160
161export function ungroup(result: QueryResult, view: QueryView): QueryView {
162  return { ...view, group: null, sort: view.sort?.column === rowsColumn(result) ? null : view.sort }
163}
164
165function compareCells(a: QueryCell | undefined, b: QueryCell | undefined, isNumeric: boolean): number {
166  if (isNumeric) {
167    const x = Number(a)
168    const y = Number(b)
169    if (Number.isFinite(x) && Number.isFinite(y)) return x - y
170  }
171  const x = (a ?? '').toLowerCase()
172  const y = (b ?? '').toLowerCase()
173  return x < y ? -1 : x > y ? 1 : 0
174}
175
176export function sortRows(table: Table, indexes: number[], sort: QuerySort | null): number[] {
177  if (!sort) return indexes
178  const isNumeric = table.numeric[sort.column] === true
179  const missing: boolean[] = []
180  const numbers: number[] = []
181  const texts: string[] = []
182  for (const r of indexes) {
183    const cell = table.rows[r]?.[sort.column]
184    missing[r] = isMissing(cell)
185    numbers[r] = isNumeric ? Number(cell) : NaN
186    texts[r] = (cell ?? '').toLowerCase()
187  }
188  const sign = sort.isDescending ? -1 : 1
189  return [...indexes].sort((r1, r2) => {
190    const aMissing = missing[r1]
191    const bMissing = missing[r2]
192    if (aMissing || bMissing) {
193      if (aMissing !== bMissing) return aMissing ? 1 : -1
194      return r1 - r2
195    }
196    const x = numbers[r1] ?? NaN
197    const y = numbers[r2] ?? NaN
198    const a = texts[r1] ?? ''
199    const b = texts[r2] ?? ''
200    const order = Number.isFinite(x) && Number.isFinite(y) ? x - y : a < b ? -1 : a > b ? 1 : 0
201    return sign * order || r1 - r2
202  })
203}
204
205export function rowMatches(result: QueryResult, view: QueryView, row: number): boolean {
206  const cells = result.rows[row] ?? []
207  const needle = view.filter.trim().toLowerCase()
208  if (needle === '') return true
209  return view.order.some(c => (cells[c] ?? '').toLowerCase().includes(needle))
210}
211
212export function isNarrowed(view: QueryView): boolean {
213  return view.filter.trim() !== ''
214}
215
216function decimalsOf(cell: string): number {
217  return /\.(\d+)$/.exec(cell)?.[1]?.length ?? 0
218}
219
220function fixed(value: number, decimals: number): string {
221  const text = value.toFixed(Math.min(20, decimals))
222  return text.includes('.') ? text.replace(/\.?0+$/, '') : text
223}
224
225function aggregate(table: Table, column: number, rows: number[], aggregation: QueryAggregation): QueryCell {
226  const cells: string[] = []
227  for (const r of rows) {
228    const cell = table.rows[r]?.[column]
229    if (!isMissing(cell)) cells.push(cell ?? '')
230  }
231  if (aggregation === 'count') return String(cells.length)
232  if (aggregation === 'distinct') return String(new Set(cells).size)
233  if (aggregation === 'min' || aggregation === 'max') {
234    const isNumeric = table.numeric[column] === true
235    const sign = aggregation === 'min' ? -1 : 1
236    return cells.reduce<string | null>((best, cell) => (best === null || sign * compareCells(cell, best, isNumeric) > 0 ? cell : best), null)
237  }
238  let sum = 0
239  let count = 0
240  let decimals = 0
241  for (const cell of cells) {
242    const value = Number(cell)
243    if (!Number.isFinite(value)) continue
244    sum += value
245    count += 1
246    decimals = Math.max(decimals, decimalsOf(cell))
247  }
248  if (count === 0) return null
249  return aggregation === 'sum' ? fixed(sum, decimals) : fixed(sum / count, decimals + 2)
250}
251
252export function shownName(result: QueryResult, view: QueryView, column: number): string {
253  if (column === rowsColumn(result)) return ROWS_NAME
254  const name = result.columns[column] ?? `column ${column + 1}`
255  const role = roleOf(view, column)
256  return role === null || role === 'group' || role === 'off' ? name : `${name} (${role})`
257}
258
259function keepsKind(role: Role | null): boolean {
260  return role === null || role === 'group' || role === 'off' || role === 'min' || role === 'max'
261}
262
263export function groupTable(result: QueryResult, view: QueryView, rows: number[]): Table {
264  const source = sourceTable(result)
265  const keys = groupKeys(view)
266  const roles = result.columns.map((_, c) => roleOf(view, c))
267  const groups = new Map<string, number[]>()
268  for (const r of rows) {
269    const id = JSON.stringify(keys.map(c => (isMissing(result.rows[r]?.[c]) ? null : result.rows[r]?.[c])))
270    const members = groups.get(id)
271    if (members) members.push(r)
272    else groups.set(id, [r])
273  }
274  if (keys.length === 0 && groups.size === 0) groups.set('[]', [])
275  return {
276    columns: [...result.columns.map((_, c) => shownName(result, view, c)), ROWS_NAME],
277    numeric: [...roles.map((role, c) => (keepsKind(role) ? source.numeric[c] === true : true)), true],
278    kinds: [...roles.map((role, c) => (keepsKind(role) ? source.kinds[c] ?? 'text' : 'measure')), 'measure'],
279    rows: [...groups.values()].map(members => [
280      ...roles.map((role, c) => {
281        if (role === null || role === 'off') return null
282        if (role === 'group') return members[0] === undefined ? null : result.rows[members[0]]?.[c] ?? null
283        return aggregate(source, c, members, role)
284      }),
285      String(members.length),
286    ]),
287  }
288}
289
290export function shown(result: QueryResult, view: QueryView): Shown {
291  const matched = result.rows.map((_, r) => r).filter(r => rowMatches(result, view, r))
292  if (!view.group) {
293    const table = sourceTable(result)
294    return { table, order: view.order, kept: sortRows(table, matched, view.sort), matched: matched.length }
295  }
296  const table = groupTable(result, view, matched)
297  return { table, order: shownOrder(result, view), kept: sortRows(table, table.rows.map((_, r) => r), view.sort), matched: matched.length }
298}
299
300export function formatNumber(text: string): string {
301  const m = NUMBER_VALUE.exec(text)
302  if (!m) return text
303  return `${m[1] ?? ''}${(m[2] ?? '').replace(/\B(?=(\d{3})+(?!\d))/g, ',')}${m[3] ?? ''}`
304}
305
306export function countText(n: number): string {
307  return formatNumber(String(n))
308}
309
310function displayCell(cell: QueryCell | undefined, kind: ColumnKind): string {
311  if (isMissing(cell)) return MISSING
312  return kind === 'measure' ? formatNumber(cell ?? '') : cell ?? ''
313}
314
315function scaleOf(table: Table, column: number): Scale | null {
316  let lo = 0
317  let hi = 0
318  for (const row of table.rows) {
319    const cell = row[column]
320    const value = Number(cell)
321    if (isMissing(cell) || !Number.isFinite(value)) continue
322    lo = Math.min(lo, value)
323    hi = Math.max(hi, value)
324  }
325  return hi > lo ? { lo, hi } : null
326}
327
328export function tableLayout(table: Table): Layout {
329  const { kinds } = table
330  const cells = table.rows.map(row => table.columns.map((_, c) => displayCell(row[c], kinds[c] ?? 'text')))
331  const widths = table.columns.map((name, c) =>
332    cells.reduce((width, row) => Math.max(width, (row[c] ?? '').length), Math.max(MIN_WIDTH, name.length + 2)),
333  )
334  const scales = kinds.map((kind, c) => (kind === 'measure' ? scaleOf(table, c) : null))
335  return { kinds, cells, widths, scales }
336}
337
338export function shownLayout(result: QueryResult, view: QueryView, table: Table): Layout {
339  const layout = tableLayout(table)
340  if (!view.group || !isNarrowed(view)) return layout
341  const whole = tableLayout(groupTable(result, view, result.rows.map((_, r) => r)))
342  return { ...layout, widths: layout.widths.map((width, c) => Math.max(width, whole.widths[c] ?? 0)) }
343}
344
345export function fitColumns(order: number[], widths: number[], bodyColumns: number): { fitting: number[]; past: number[] } {
346  const fitting: number[] = []
347  const past: number[] = []
348  let used = 0
349  for (const c of order) {
350    const width = (widths[c] ?? MIN_WIDTH) + GAP
351    if (past.length === 0 && (used + width <= bodyColumns + GAP || fitting.length === 0)) {
352      fitting.push(c)
353      used += width
354    } else {
355      past.push(c)
356    }
357  }
358  return { fitting, past }
359}
360
361function barExtent(scale: Scale | null | undefined, cell: QueryCell | undefined, width: number): [number, number] {
362  const value = Number(cell)
363  if (!scale || isMissing(cell) || !Number.isFinite(value)) return [0, 0]
364  const at = (x: number) => Math.round((width * (x - scale.lo)) / (scale.hi - scale.lo))
365  const zero = at(0)
366  const end = at(value)
367  return [Math.min(zero, end), Math.max(zero, end)]
368}
369
370export function rowSegments(table: Table, layout: Layout, row: number, columns: number[], bars: boolean): Segment[] {
371  const segments: Segment[] = []
372  const push = (text: string, isDim: boolean, isBar: boolean) => {
373    if (text === '') return
374    const last = segments[segments.length - 1]
375    if (last && last.isDim === isDim && last.isBar === isBar) last.text += text
376    else segments.push({ text, isDim, isBar })
377  }
378  columns.forEach((c, i) => {
379    if (i > 0) push(' '.repeat(GAP), false, false)
380    const kind = layout.kinds[c] ?? 'text'
381    const width = layout.widths[c] ?? MIN_WIDTH
382    const cell = table.rows[row]?.[c]
383    const text = layout.cells[row]?.[c] ?? ''
384    const padded = kind === 'text' ? text.padEnd(width) : text.padStart(width)
385    const isDim = isMissing(cell) || kind === 'date'
386    const [from, to] = bars ? barExtent(layout.scales[c], cell, width) : [0, 0]
387    push(padded.slice(0, from), isDim, false)
388    push(padded.slice(from, to), isDim, true)
389    push(padded.slice(to), isDim, false)
390  })
391  return segments
392}
393
394export function headLabel(name: string, kind: ColumnKind, view: QueryView, column: number): string {
395  const arrow = view.sort?.column === column ? (view.sort.isDescending ? '▼' : '▲') : ' '
396  return kind === 'text' ? `${name} ${arrow}` : `${arrow} ${name}`
397}
398
399function sortWords(kind: ColumnKind, isDescending: boolean): string {
400  if (kind === 'text') return isDescending ? 'Z to A' : 'A to Z'
401  if (kind === 'year' || kind === 'date') return isDescending ? 'newest first' : 'oldest first'
402  return isDescending ? 'high to low' : 'low to high'
403}
404
405function isMoved(view: QueryView): boolean {
406  return view.order.some((c, i) => i > 0 && c < (view.order[i - 1] ?? -1))
407}
408
409function groupText(result: QueryResult, view: QueryView): string {
410  const keys = groupKeys(view)
411  if (keys.length === 0) return 'grouped into one total row'
412  return `grouped by ${keys.map(c => result.columns[c] ?? `column ${c + 1}`).join(', ')}`
413}
414
415export function viewChanges(result: QueryResult, view: QueryView): Change[] {
416  const name = (c: number) => result.columns[c] ?? `column ${c + 1}`
417  const changes: Change[] = []
418  if (view.group) changes.push({ key: 'clear-group', text: groupText(result, view), clear: v => ungroup(result, v) })
419  if (view.sort) {
420    const { column, isDescending } = view.sort
421    const kinds = view.group ? groupTable(result, view, []).kinds : columnKinds(result)
422    changes.push({
423      key: 'clear-sort',
424      text: `sorted by ${shownName(result, view, column)}, ${sortWords(kinds[column] ?? 'text', isDescending)}`,
425      clear: v => ({ ...v, sort: null }),
426    })
427  }
428  const filter = view.filter.trim()
429  if (filter !== '') changes.push({ key: 'clear-text', text: `rows contain "${filter}"`, clear: v => ({ ...v, filter: '' }) })
430  for (const c of view.hidden) {
431    changes.push({ key: `clear-hidden-${c}`, text: `${name(c)} hidden`, clear: v => showColumn(v, c) })
432  }
433  if (isMoved(view)) {
434    changes.push({ key: 'clear-order', text: 'columns moved', clear: v => ({ ...v, order: [...v.order].sort((a, b) => a - b) }) })
435  }
436  if (view.bars) changes.push({ key: 'clear-bars', text: 'data bars shown', clear: v => ({ ...v, bars: false }) })
437  return changes
438}
439
440export function scopeText(result: QueryResult, view: QueryView, matched: number, groups: number): string {
441  const n = result.rows.length
442  const rows = isNarrowed(view) ? `${countText(matched)} of ${countText(n)} rows` : `${countText(n)} ${n === 1 ? 'row' : 'rows'}`
443  const holder = result.source.startsWith('File') ? 'file' : 'result'
444  const completeness = !result.isCapped ? 'complete' : result.total > n ? `capped, the ${holder} has ${totalText(result)}` : 'capped'
445  const filter = view.filter.trim()
446  const parts = [`${result.source}: ${rows}, ${completeness}.`]
447  if (filter !== '') parts.push(`Rows contain "${filter}".`)
448  if (view.group && groupKeys(view).length === 0) parts.push('Grouped into one total row.')
449  else if (view.group) parts.push(`${groupText(result, view).replace(/^g/, 'G')}: ${countText(groups)} ${groups === 1 ? 'group' : 'groups'}.`)
450  return parts.join(' ')
451}
452
453export function lineCount(widths: number[], room: number, gap: number): number {
454  let lines = 1
455  let used = 0
456  for (const width of widths) {
457    if (used > 0 && used + gap + width > room) {
458      lines += 1
459      used = width
460    } else {
461      used += (used > 0 ? gap : 0) + width
462    }
463  }
464  return lines
465}
466
467export function footerText(first: number, shown: number, total: number): string {
468  if (total === 0 || shown === 0) return ''
469  return `${countText(first + 1)}–${countText(first + shown)} of ${countText(total)}`
470}
471
472export function columnsText(count: number, past: number[]): string {
473  if (past.length > 0) return `${past.length} other ${past.length === 1 ? 'column' : 'columns'}:`
474  return `All ${count} ${count === 1 ? 'column' : 'columns'} shown.`
475}
476
477export function copyText(table: Table, order: number[], rows: number[]): string {
478  const clean = (text: string) => text.replace(/[\t\r\n]+/g, ' ')
479  const lines = [order.map(c => clean(table.columns[c] ?? '')).join('\t')]
480  for (const r of rows) {
481    lines.push(order.map(c => (isMissing(table.rows[r]?.[c]) ? '' : clean(table.rows[r]?.[c] ?? ''))).join('\t'))
482  }
483  return lines.join('\n')
484}
485
486export function sizeLabel(result: QueryResult): string {
487  const n = result.rows.length
488  if (!result.isCapped) return `${countText(n)} ${n === 1 ? 'row' : 'rows'}, complete`
489  if (result.total > n) return `capped at ${countText(n)} of ${totalText(result)} rows`
490  return `capped at ${countText(n)} rows`
491}
492
493function totalText(result: QueryResult): string {
494  return result.isTotalMinimum ? `more than ${countText(result.total)}` : countText(result.total)
495}
496
497let memo: { key: string; value: Computed } | null = null
498
499export function computed(result: QueryResult, view: QueryView): Computed {
500  const key = `${result.id}\n${viewSignature(view)}`
501  if (memo?.key === key) return memo.value
502  const found = shown(result, view)
503  const value = { shown: found, layout: shownLayout(result, view, found.table) }
504  memo = { key, value }
505  return value
506}
507
508export function viewSignature(view: QueryView): string {
509  return JSON.stringify([view.resultId, view.sort, view.filter.trim(), view.order, view.hidden, view.bars, view.group])
510}
511
512export function isDefaultView(result: QueryResult, view: QueryView): boolean {
513  return viewSignature(view) === viewSignature(freshView(result))
514}
515
516export function contextText(result: QueryResult, view: QueryView): string {
517  const { table, order, kept, matched } = computed(result, view).shown
518  const parts: string[] = []
519  parts.push(`The data explorer pane shows a table from ${result.source} (${sizeLabel(result)}).`)
520  const query = result.query.replace(/\s+/g, ' ').trim()
521  if (query !== '') parts.push(`The table came from: ${query.length > 300 ? `${query.slice(0, 300)}…` : query}.`)
522  const changes = viewChanges(result, view).filter(change => change.key !== 'clear-bars')
523  if (changes.length > 0) parts.push(`The user changed the view: ${changes.map(change => change.text).join('; ')}.`)
524  if (isNarrowed(view)) parts.push(`${countText(matched)} of ${countText(result.rows.length)} rows match.`)
525  if (view.group) parts.push(`The grouped table has ${countText(kept.length)} ${kept.length === 1 ? 'row' : 'rows'}.`)
526  parts.push(`Column order: ${order.map(c => table.columns[c] ?? '').join(', ')}.`)
527  const rows = kept.slice(0, CONTEXT_ROWS)
528  const lines = [order.map(c => table.columns[c] ?? '').join(' | ')]
529  for (const r of rows) lines.push(order.map(c => table.rows[r]?.[c] ?? '').join(' | '))
530  const what = view.group ? 'grouped rows' : 'matching rows'
531  return `${parts.join(' ')}\nThe first ${rows.length} ${what} as the user sees them:\n${lines.join('\n')}`
532}
533

1export type QueryCell = string | null
2
3export type QuerySource = string
4
5export type QueryTable = {
6  columns: string[]
7  numeric: boolean[]
8  rows: QueryCell[][]
9  total: number
10  isTotalMinimum?: boolean
11  isCapped: boolean
12  note: string
13}
14
15export type QueryReload = {
16  server: string
17  tool: string
18  artifactId: string
19  daxQuery: string
20  maxRows: number
21}
22
23export type QueryResult = QueryTable & {
24  id: string
25  source: QuerySource
26  query: string
27  reload?: QueryReload | null
28  isPrimary: boolean
29}
30
31export type QuerySort = { column: number; isDescending: boolean }
32
33export type QueryAggregation = 'sum' | 'mean' | 'min' | 'max' | 'count' | 'distinct'
34
35export type QueryMeasure = QueryAggregation | 'off'
36
37export type QueryGroup = { keys: number[]; aggregations: QueryMeasure[] }
38
39export type QueryPanel = 'table' | 'changes' | 'group' | 'tables'
40
41export type QueryView = {
42  resultId: string
43  sort: QuerySort | null
44  filter: string
45  order: number[]
46  hidden: number[]
47  bars: boolean
48  group: QueryGroup | null
49}
50
51declare module 'claude-code' {
52  interface PluginState {
53    'data-explorer': {
54      results: QueryResult[]
55      shown: string
56      views: QueryView[]
57      unseen: string[]
58      view: QueryView | null
59      undo: QueryView | null
60      panel: QueryPanel
61      firstRow: number
62      firstColumn: number
63      sent: string
64      loading: string
65      asked: string
66    }
67  }
68}
69