1"use client";2import { useEffect, useRef, useState, type KeyboardEvent } from "react";3import { Download, Play } from "lucide-react";4import clsx from "clsx";5import { api, type QueryOut } from "@/lib/api";6import { useAsync, useLocalStorage, useQueryParam } from "@/lib/hooks";7import { useToast } from "@/lib/toast";8import { downloadText, fmtInt, toCsv } from "@/lib/format";9import { Banner, Card, Empty, ErrorState, PageHeader } from "@/components/ui";1011const DISPLAY_CAP = 1000;1213export default function PlaygroundPage() {14 const qSymbol = useQueryParam("symbol");15 const [sql, setSql] = useLocalStorage("findata.playground.sql", "SELECT symbol_as_reported, count(*) bars FROM market_bars_stock_1m_raw GROUP BY 1 ORDER BY 2 DESC LIMIT 20");16 const [maxRows, setMaxRows] = useState(1000);17 const [out, setOut] = useState<QueryOut | null>(null);18 const [busy, setBusy] = useState(false);19 const [error, setError] = useState<Error | null>(null);20 const toast = useToast();21 const ta = useRef<HTMLTextAreaElement>(null);22 const views = useAsync((s) => api.get<{ views: { name: string; files: number }[]; examples: string[] }>("/query/views", undefined, s), []);2324 useEffect(() => {25 if (qSymbol) setSql(`SELECT session_date, min(low) lo, max(high) hi, sum(volume) vol\nFROM market_bars_stock_1m_raw\nWHERE symbol_as_reported = '${qSymbol.toUpperCase().replace(/'/g, "")}'\nGROUP BY 1 ORDER BY 1 DESC LIMIT 100`);26 // eslint-disable-next-line react-hooks/exhaustive-deps27 }, [qSymbol]);2829 async function run() {30 if (!sql.trim()) return;31 setBusy(true);32 setError(null);33 try {34 const o = await api.post<QueryOut>("/query", { sql, max_rows: maxRows });35 setOut(o);36 } catch (e) {37 setError(e instanceof Error ? e : new Error(String(e)));38 setOut(null);39 } finally {40 setBusy(false);41 }42 }4344 function onKey(e: KeyboardEvent<HTMLTextAreaElement>) {45 if ((e.metaKey || e.ctrlKey) && e.key === "Enter") {46 e.preventDefault();47 void run();48 }49 if (e.key === "Tab") {50 e.preventDefault();51 const el = e.currentTarget;52 const s = el.selectionStart, en = el.selectionEnd;53 const next = sql.slice(0, s) + " " + sql.slice(en);54 setSql(next);55 requestAnimationFrame(() => el.setSelectionRange(s + 2, s + 2));56 }57 }5859 const shown = out ? out.rows.slice(0, DISPLAY_CAP) : [];6061 return (62 <>63 <PageHeader title="SQL playground" subtitle="DuckDB over the curated Parquet files — read-only, one statement, sandboxed. Results are logged to the audit trail." />64 <div className="grid gap-4 lg:grid-cols-[minmax(0,1fr)_280px]">65 <div className="flex flex-col gap-4">66 <Card>67 <textarea68 ref={ta}69 className="input min-h-40 resize-y font-mono text-[13px] leading-relaxed"70 spellCheck={false}71 value={sql}72 onChange={(e) => setSql(e.target.value)}73 onKeyDown={onKey}74 aria-label="SQL"75 />76 <div className="mt-2 flex flex-wrap items-center gap-2">77 <button className="btn btn-primary" onClick={() => void run()} disabled={busy || !sql.trim()}>78 <Play className="h-3.5 w-3.5" /> {busy ? "Running…" : "Run"}79 </button>80 <span className="text-xs text-ink-3">81 <kbd className="kbd">⌘</kbd>/<kbd className="kbd">Ctrl</kbd> + <kbd className="kbd">Enter</kbd>82 </span>83 <label className="ml-auto flex items-center gap-2 text-xs text-ink-2">84 max rows85 <input type="number" className="input w-24 py-1" min={1} max={100000} value={maxRows} onChange={(e) => setMaxRows(Math.max(1, Math.min(100000, Number(e.target.value) || 1000)))} />86 </label>87 </div>88 </Card>8990 {error && <ErrorState error={error} retry={() => void run()} />}9192 {out && (93 <Card94 title={95 <span className="flex flex-wrap items-center gap-2 text-sm">96 Results97 <span className="pill bg-surface-2 text-ink-2 tnum">{fmtInt(out.meta.rows)} rows</span>98 <span className="pill bg-surface-2 text-ink-2 tnum">{fmtInt(out.meta.elapsed_ms)} ms</span>99 {out.meta.truncated && <span className="pill bg-warn-soft text-warn-ink">truncated at max_rows</span>}100 {out.rows.length > DISPLAY_CAP && <span className="pill bg-warn-soft text-warn-ink">showing first {fmtInt(DISPLAY_CAP)}</span>}101 </span>102 }103 actions={104 <button className="btn btn-sm" onClick={() => downloadText(`query-${Date.now()}.csv`, toCsv(out.columns.map((c) => c.name), out.rows))} disabled={!out.rows.length}>105 <Download className="h-3.5 w-3.5" /> CSV ({fmtInt(out.rows.length)} rows)106 </button>107 }108 >109 {out.rows.length === 0 ? (110 <Empty>Query returned no rows.</Empty>111 ) : (112 <div className="table-wrap" style={{ maxHeight: "60dvh", overflowY: "auto" }}>113 <table className="table">114 <thead>115 <tr>116 {out.columns.map((c) => (117 <th key={c.name}>118 {c.name}119 <div className="font-normal text-[10px] text-ink-3">{c.type}</div>120 </th>121 ))}122 </tr>123 </thead>124 <tbody>125 {shown.map((r, i) => (126 <tr key={i}>127 {out.columns.map((c) => {128 const v = r[c.name];129 const isNum = typeof v === "number";130 return (131 <td key={c.name} className={clsx("font-mono text-xs whitespace-nowrap", isNum && "num")}>132 {v === null || v === undefined ? <span className="text-ink-3">NULL</span> : typeof v === "object" ? JSON.stringify(v) : String(v)}133 </td>134 );135 })}136 </tr>137 ))}138 </tbody>139 </table>140 </div>141 )}142 <div className="mt-2 text-[11px] text-ink-3">engine: {out.meta.engine}</div>143 </Card>144 )}145 </div>146147 <div className="flex flex-col gap-4">148 <Card title="Views">149 {views.error && <ErrorState error={views.error} retry={views.reload} />}150 {views.data && views.data.views.length === 0 && <Empty>No views (no curated files yet).</Empty>}151 <ul className="flex flex-col gap-1 text-xs">152 {views.data?.views.map((v) => (153 <li key={v.name} className="flex items-center justify-between gap-2">154 <button className="font-mono text-accent-ink hover:underline text-left break-all" onClick={() => setSql(`SELECT * FROM ${v.name} LIMIT 100`)} title="Insert SELECT">155 {v.name}156 </button>157 <span className="tnum text-ink-3 whitespace-nowrap">{fmtInt(v.files)} files</span>158 </li>159 ))}160 </ul>161 </Card>162 <Card title="Examples">163 <ul className="flex flex-col gap-2">164 {views.data?.examples.map((ex, i) => (165 <li key={i}>166 <button className="w-full rounded-md border border-hairline bg-surface-2 p-2 text-left font-mono text-[11px] leading-snug text-ink-2 hover:border-accent/60 whitespace-pre-wrap break-words" onClick={() => setSql(ex)}>167 {ex}168 </button>169 </li>170 ))}171 </ul>172 </Card>173 <Banner tone="accent">174 <div className="text-xs">175 Bars are stamped at <strong>bar open</strong> in UTC. Columns: <code>timestamp_utc, session_date, open, high, low, close, volume, vwap, trade_count, quality_flags, symbol_as_reported, instrument_id</code>. Stored data is <strong>raw</strong>; adjustments live in <code>corporate_actions</code>.176 </div>177 </Banner>178 <p className="text-[11px] text-ink-3" onClick={() => toast.info("Query results are private", "FirstRate Data terms forbid redistribution of raw bars.")}>License: frd-private — no redistribution of raw bars.</p>179 </div>180 </div>181 </>182 );183}184