server.js

// Pythia: general-purpose conversational reporting for FileMaker databases.
// Ask in plain English; answers come from a local DuckDB copy of the connected
// database (synced over OData), with the AI choosing queries and the server
// injecting the real rows, not model-written numbers. See README.md and RULES.md.

import "./env.js";
import express from "express";
import fs from "fs";
import path from "path";
import { fileURLToPath } from "url";
import { fmConfigured, fmConnection, setConnection, withConnection, checkAuth, listDatabases, fetchSchema, fetchCounts, layoutStats, baseName, listDatabasesDataApi, fetchSchemaDataApi } from "./fm.js";
import { sql as cubeSql, syncTables, reconcileCube, cubeManifest, cubeExists, resetCube, dropTables, recommendMemoryMB, machineMemory, cancelWrites, resetCancelWrites } from "./cube.js";
import { KNOWLEDGE, kbLookup } from "./knowledge.js";
import { parseSaxml, mergeHints, hintIndex, matchTable } from "./saxml.js";
import { renderReport, renderReportParts } from "./report.js";
import { SEED_REPORTS } from "./reports.seed.js";

// AI config: env vars are the DEFAULTS; the Settings > AI panel overrides any
// of them per-install (stored on the volume in config.json). Getters read the
// config first, fall back to env – so a blank key field keeps the env key.
//   key      – ANTHROPIC_API_KEY / config.aiKey
//   model    – claude-fable-5 | -sonnet-5 | -haiku-... | -opus-4-8
//   speed    – "fast": fast mode (Opus-only, ~2.5x tok/s, premium, separate
//              quota; needs the beta flag + top-level speed param).
//   thinking – "off" (thinking:disabled, straight-to-output; matters on Sonnet),
//              "deep" (extended thinking with a budget), or "" (adaptive default).
// On any failure with a knob applied, retry once with a plain request.
const ENV_KEY = process.env.ANTHROPIC_API_KEY || "";
const ENV_MODEL = process.env.ANTHROPIC_MODEL || "claude-opus-5";
const ENV_SPEED = process.env.ANTHROPIC_SPEED || "";
const ENV_THINKING = process.env.ANTHROPIC_THINKING || "";
// When the picked model is an OpenAI one, the OpenAI key is the key that
// matters; every "is a key set" gate goes through here.
const aiKey = () => {
  const c = readConfig();
  const m = String(c.aiModel || "");
  if (/^(gpt-|o[0-9])/i.test(m)) return c.openaiKey || process.env.OPENAI_API_KEY || "";
  return c.aiKey || ENV_KEY;
};
// Model families, low to high power. The effective model TRACKS the newest of
// its family (when Anthropic ships Opus 6, an un-pinned Opus 5 config rides
// along automatically); picking the one-previous model in Settings pins it.
const modelFamily = (id = "") => /fable|mythos/.test(id) ? "fable" : /opus/.test(id) ? "opus" : /sonnet/.test(id) ? "sonnet" : /haiku/.test(id) ? "haiku" : "other";
function newestOfFamily(fam) {
  const c = loadModelsCache();
  if (!c?.models?.length) return null;
  const list = c.models.filter((m) => modelFamily(m.id) === fam)
    .sort((a, b) => String(b.created_at || "").localeCompare(String(a.created_at || "")));
  return list[0]?.id || null;
}
const aiModel = () => {
  const c = readConfig();
  const picked = c.aiModel || ENV_MODEL;
  if (c.aiModelPinned) return picked;
  return newestOfFamily(modelFamily(picked)) || picked;
};
// Fast is the default; anthropicFetch only applies it on Opus-family models.
// Fast mode is gone for good (Matt, 2026-08-22). It is premium-priced, it only
// ever applied to Opus, and it was one more switch nobody wanted. Never on.
const aiSpeed = () => "";
const aiThinking = () => { const c = readConfig(); return c.aiThinking !== undefined ? c.aiThinking : ENV_THINKING; };
// Snapshot taken ONCE at the start of a task, so a mid-task Settings change can't
// switch model/params mid-loop. Pass it through every call in a multi-call flow.
const aiSnapshot = () => ({ key: aiKey(), model: aiModel(), speed: aiSpeed(), thinking: aiThinking() });

// --- Chat Completions adapter ------------------------------------------------
// One translation layer so the rest of the app only ever speaks the Anthropic
// shape. When the picked model belongs to a Chat Completions provider (OpenAI's
// gpt-*), the request is translated to Chat
// Completions and the reply is translated back into
// {content:[{type:"text"|"tool_use"}], stop_reason}. The tool loop, the chart
// tools, and every call site stay untouched.
const isOpenAIModel = (m) => /^(gpt-|o[0-9])/i.test(String(m || ""));
const openaiKey = () => { const c = readConfig(); return c.openaiKey || process.env.OPENAI_API_KEY || ""; };

// opts carries the only things the providers disagree about: where to post, how
// to authorize, which knobs that server accepts, and how long to wait.
async function chatCompletionsFetch(body, timeoutMs, opts) {
  const sys = typeof body.system === "string" ? body.system
    : Array.isArray(body.system) ? body.system.map((b) => b.text || "").join("\n") : "";
  const msgs = [];
  if (sys) msgs.push({ role: "system", content: sys });
  for (const m of body.messages || []) {
    if (typeof m.content === "string") { msgs.push({ role: m.role, content: m.content }); continue; }
    const blocks = Array.isArray(m.content) ? m.content : [];
    if (m.role === "assistant") {
      const text = blocks.filter((b) => b.type === "text").map((b) => b.text).join("\n");
      const calls = blocks.filter((b) => b.type === "tool_use").map((b) => ({
        id: b.id, type: "function", function: { name: b.name, arguments: JSON.stringify(b.input || {}) },
      }));
      const am = { role: "assistant", content: text || null };
      if (calls.length) am.tool_calls = calls;
      msgs.push(am);
    } else {
      for (const b of blocks) {
        if (b.type === "tool_result") msgs.push({ role: "tool", tool_call_id: b.tool_use_id, content: typeof b.content === "string" ? b.content : JSON.stringify(b.content) });
        else if (b.type === "text") msgs.push({ role: "user", content: b.text });
      }
    }
  }
  const tools = (body.tools || []).map((t) => ({
    type: "function", function: { name: t.name, description: t.description || "", parameters: t.input_schema || { type: "object" } },
  }));
  const base = { model: opts.model || body.model, max_tokens: body.max_tokens || 2000, messages: msgs, ...(tools.length ? { tools } : {}) };
  if (process.env.PYTHIA_DEBUG_LLM) {
    console.log("[llm]", opts.label, "msgs=", msgs.map((m) => `${m.role}:${String(m.content || "").length}`).join(","), "tools=", tools.length, "| last:", JSON.stringify(String(msgs[msgs.length - 1]?.content || "").slice(0, 160)));
  }
  const req = opts.tune ? opts.tune(base, tools.length > 0) : base;
  const res = await fetch(opts.url, {
    method: "POST",
    headers: { "content-type": "application/json", ...(opts.headers || {}) },
    body: JSON.stringify(req), signal: AbortSignal.timeout(opts.timeoutMs || timeoutMs),
  });
  if (!res.ok) throw new Error(providerError(opts.label, res.status, await res.text()));
  const j = await res.json();
  const ch = j.choices?.[0] || {};
  const content = [];
  // A thinking model can put its whole answer in `reasoning` and leave content
  // empty. Returning nothing there turns a correct answer into "I couldn't turn
  // that into a report". Only when there is no tool call to make instead, so
  // thinking text is never mistaken for a final answer mid-loop.
  const finalText = ch.message?.content
    || ((ch.message?.tool_calls || []).length ? "" : ch.message?.reasoning)
    || "";
  if (finalText) content.push({ type: "text", text: finalText });
  if (process.env.PYTHIA_DEBUG_LLM) {
    console.log("[llm-resp]", opts.label, "finish=", ch.finish_reason, "content=", String(ch.message?.content || "").length,
      "reasoning=", String(ch.message?.reasoning || "").length, "calls=", (ch.message?.tool_calls || []).length,
      "out_tokens=", j.usage?.completion_tokens);
    for (const tc of ch.message?.tool_calls || []) console.log("[llm-call]", tc.function?.name, String(tc.function?.arguments || "").slice(0, 500));
  }
  (ch.message?.tool_calls || []).forEach((tc, i) => {
    let input = {}; try { input = JSON.parse(tc.function?.arguments || "{}"); } catch { /* leave empty */ }
    // Some Chat Completions servers omit the call id that the tool loop matches
    // tool_result against. A synthetic one is fine: unique inside this reply.
    content.push({ type: "tool_use", id: tc.id || `call_${i}`, name: tc.function?.name, input });
  });
  return {
    content,
    stop_reason: ch.finish_reason === "tool_calls" ? "tool_use" : ch.finish_reason === "length" ? "max_tokens" : "end_turn",
    usage: { input_tokens: j.usage?.prompt_tokens || 0, output_tokens: j.usage?.completion_tokens || 0 },
  };
}

function openaiFetch(body, timeoutMs) {
  return chatCompletionsFetch(body, timeoutMs, {
    label: "OpenAI",
    url: "https://api.openai.com/v1/chat/completions",
    headers: { authorization: `Bearer ${openaiKey()}` },
    // GPT-5.x on /v1/chat/completions refuses function tools while reasoning is
    // on; "none" is the documented way to combine them (the /v1/responses API is
    // the alternative, and a bigger rewrite than this adapter wants to be).
    // It also wants max_completion_tokens, not the older max_tokens.
    tune: ({ max_tokens, ...r }, hasTools) => ({ ...r, max_completion_tokens: max_tokens, ...(hasTools ? { reasoning_effort: "none" } : {}) }),
  });
}

async function anthropicFetch(body, timeoutMs, ai) {
  if (isOpenAIModel(body.model)) return openaiFetch(body, timeoutMs);
  const a = ai || aiSnapshot();
  const fast = a.speed === "fast" && modelFamily(a.model) === "opus"; // fast exists only on Opus
  const think = a.thinking; // "off" | "deep" | ""
  const plainHeaders = { "x-api-key": a.key, "anthropic-version": "2023-06-01", "content-type": "application/json" };
  // Current models (Fable/Mythos, Opus 4.7+, Sonnet 5) reject budget_tokens;
  // deep = adaptive thinking + higher effort there. Fable also rejects an
  // explicit disabled - omit instead. Older models keep the legacy shapes.
  // The !res.ok retry below still strips all of this if a model disagrees.
  const fable = /fable|mythos/.test(a.model);
  const modern = fable || !/opus-4-[0-6]\b|sonnet-4|haiku|claude-3/.test(a.model);
  const thinkBody = think === "off" ? (modern ? { output_config: { effort: "low" } } : { thinking: { type: "disabled" } })
    : think === "deep" ? (modern
        ? { ...(fable ? {} : { thinking: { type: "adaptive" } }), output_config: { effort: "xhigh" }, max_tokens: Math.max(body.max_tokens || 2000, 8000) }
        : { thinking: { type: "enabled", budget_tokens: 6000 }, max_tokens: Math.max(body.max_tokens || 2000, 8000) })
    : {};
  // Prompt caching: the system prompt (schema + notes + context) is byte-stable
  // between calls and re-sent 3-4x per question by the tool loop. Marking it
  // ephemeral lets Anthropic serve it from cache: ~10x cheaper prefix and a
  // visibly faster first token on every call after the first. Below the
  // model's cacheable minimum it silently no-ops, so this is always safe.
  if (typeof body.system === "string" && body.system.length > 3000) {
    body = { ...body, system: [{ type: "text", text: body.system, cache_control: { type: "ephemeral" } }] };
  }
  const post = (headers, b) => fetch("https://api.anthropic.com/v1/messages", {
    method: "POST", headers, body: JSON.stringify(b), signal: AbortSignal.timeout(timeoutMs),
  });
  let res = await post(
    { ...plainHeaders, ...(fast ? { "anthropic-beta": "fast-mode-2026-02-01" } : {}) },
    { ...body, ...(fast ? { speed: "fast" } : {}), ...thinkBody }
  );
  if (!res.ok && (fast || think)) res = await post(plainHeaders, body);
  if (!res.ok) throw new Error(providerError("Anthropic", res.status, await res.text()));
  return res.json();
}

// ONE place where a provider's error becomes something a person can read.
// Every call site used to translate its own, which is why fixing the FileMaker
// 501 didn't fix the Anthropic 401: same bug, different catch block. The raw
// body goes to the console for us, never to the screen.
export function providerError(who, status, body = "") {
  const raw = String(body).slice(0, 400);
  console.error(`[${who} ${status}]`, raw);
  let code = "", msg = "";
  try { const j = JSON.parse(raw); code = j?.error?.type || ""; msg = j?.error?.message || ""; } catch { /* not JSON */ }
  if (status === 401 || status === 403 || /authentication/i.test(code))
    return `${who} rejected the API key. Check it in Settings.`;
  if (status === 429 || /rate_limit/i.test(code))
    return `${who} is rate-limiting this key right now. Wait a moment and try again.`;
  if (/prompt is too long|context.*exceed/i.test(msg) || /too.*large/i.test(code))
    return "That question pulled in more data than fits in one request. Ask for a narrower slice — a shorter date range, fewer tables, or one section at a time.";
  if (status === 400) return `${who} refused the request${msg ? ": " + msg.slice(0, 140) : "."}`;
  if (status >= 500 || status === 529) return `${who} is having trouble right now. Try again shortly.`;
  return `${who} returned an unexpected error (${status}).`;
}

const __dirname = path.dirname(fileURLToPath(import.meta.url));

const PORT = Number(process.env.PORT || 8080);
const SITE_PASSWORD = process.env.SITE_PASSWORD || "";
const DATA_DIR = process.env.DATA_DIR || path.join(__dirname, "data");
fs.mkdirSync(DATA_DIR, { recursive: true });
const CONFIG_PATH = path.join(DATA_DIR, "config.json");

const app = express();

// Password gate (Hecate's three-way pattern): if SITE_PASSWORD is set, the
// whole site requires it. Accepted three ways so it works in a browser AND a
// FileMaker web viewer (which cannot answer a Basic-auth prompt):
//   1. ?key=<password> in the URL  -> also drops a cookie for later calls.
//   2. a pythia_auth cookie (set by #1).
//   3. HTTP Basic (browser prompt / curl -u).
if (SITE_PASSWORD) {
  const cookieVal = `pythia_auth=${encodeURIComponent(SITE_PASSWORD)}`;
  app.use((req, res, next) => {
    if (req.query.key === SITE_PASSWORD) {
      // Secure only on HTTPS so local http dev still works; Fly terminates TLS
      // and sets x-forwarded-proto.
      const secure = req.secure || req.headers["x-forwarded-proto"] === "https" ? "; Secure" : "";
      res.setHeader("Set-Cookie", `${cookieVal}; Path=/; Max-Age=2592000; SameSite=Lax; HttpOnly${secure}`);
      return next();
    }
    const cookies = req.headers.cookie || "";
    if (cookies.split(";").some((c) => c.trim() === cookieVal)) return next();
    const header = req.headers.authorization || "";
    if (header.startsWith("Basic ")) {
      const decoded = Buffer.from(header.slice(6), "base64").toString("utf8");
      if (decoded.slice(decoded.indexOf(":") + 1) === SITE_PASSWORD) return next();
    }
    res.set("WWW-Authenticate", 'Basic realm="Pythia"');
    return res.status(401).send("Authentication required. Load with ?key=<password> in a web viewer.");
  });
}

// No caching: FileMaker web viewers cache aggressively.
app.use((_req, res, next) => {
  res.set("Cache-Control", "no-store, no-cache, must-revalidate");
  next();
});

// --- Stay-alive: one hour past the last real touch (Matt, 2026-08-24) -------
// Fly's proxy parks the machine a few MINUTES after the last inbound request
// and offers no idle-timeout knob - it killed an initial sync mid-pull. So
// Pythia keeps itself warm: any real request (or a running sync) counts as
// activity, and while the last activity is under an hour old the app pings
// its own public URL every 3 minutes. The proxy sees traffic and stays up;
// an hour after the last touch the pings stop and the machine parks as usual.
const STAY_ALIVE_MS = 60 * 60 * 1000;
let lastActivity = Date.now();
app.use((req, _res, next) => {
  if (!req.headers["x-pythia-keepalive"]) lastActivity = Date.now(); // self-pings don't extend the hour
  next();
});
if (process.env.FLY_APP_NAME) {
  setInterval(() => {
    const busy = typeof syncJob !== "undefined" && syncJob && !syncJob.done;
    if (busy) lastActivity = Date.now(); // a running job is activity, always
    if (Date.now() - lastActivity > STAY_ALIVE_MS) return; // past the hour: let Fly park it
    fetch(`https://${process.env.FLY_APP_NAME}.fly.dev/api/health`, {
      headers: { "x-pythia-keepalive": "1" }, signal: AbortSignal.timeout(10000),
    }).catch(() => { /* proxy hiccup: the next tick tries again */ });
  }, 3 * 60 * 1000).unref();
}

app.use(express.json({ limit: "5mb" })); // OnWindowTransaction payloads can be chunky
app.use(express.static(path.join(__dirname, "public")));

app.get("/api/health", (_req, res) => {
  res.json({ ok: true, app: "pythia", stage: "config", fm: fmConfigured() });
});

// --- Relevance ranking ------------------------------------------------------
// "Which tables actually matter for reporting?" An AI pass reads each table's
// name, size, field names + developer comments, and how many layouts reference
// it, then tiers them core | reference | system and pre-picks the ones worth
// syncing. Cached to the volume, keyed by the OData schema version, so it costs
// one model call per schema change. Heuristic fallback when no API key.

const RELEVANCE_PATH = path.join(DATA_DIR, "relevance.json");

async function callAnthropic(system, user, maxTokens = 2000, ai) {
  const a = ai || aiSnapshot();
  // A 38-table ranking reply can exceed 60s to generate; 60s here silently
  // degraded the whole pass to heuristic (no display names, no homing).
  const json = await anthropicFetch(
    { model: a.model, max_tokens: maxTokens, system, messages: [{ role: "user", content: user }] },
    180000, a
  );
  return json.content.map((c) => c.text || "").join("");
}

function heuristicRelevance(tables, stats) {
  const SYSTEM = /log|dashboard|chart|field\s*def|field\s*choice|example|subtype|image|selector|self\s|address book/i;
  const REFERENCE = /category|note|review|phone|employee|join|type/i;
  return tables.map((t) => {
    const layouts = stats?.counts?.[t.name] ?? 0;
    const rows = t.rowCount; // null = count failed, NOT an empty table
    let tier = "reference";
    if (SYSTEM.test(t.name) || (rows != null && rows <= 2) || (layouts === 0 && t.fields.length < 6)) tier = "system";
    else if (layouts >= 3 || rows >= 500) tier = "core";
    else if (REFERENCE.test(t.name)) tier = "reference";
    return { name: t.name, tier, include: tier !== "system", reason: `${layouts} layouts, ${rows == null ? "?" : rows.toLocaleString()} rows (heuristic)` };
  });
}

async function aiRelevance(tables, stats) {
  const multiFile = tables.some((t) => t.occByDb && Object.keys(t.occByDb).length > 1);
  const summary = tables.map((t) => ({
    name: t.name,
    occurrences: (t.occurrences || []).slice(0, 8),
    // OData cannot say which FILE a base table lives in (GOTCHAS.md), so give
    // the model each candidate file with the TO names seen there and let it
    // pick the home. Legacy multi-file solutions usually name files after
    // their table, so this is strong signal.
    ...(multiFile ? { candidateFiles: Object.fromEntries(Object.entries(t.occByDb || {}).map(([db, occs]) => [db, occs.slice(0, 4)])) } : {}),
    rows: t.rowCount ?? null,
    layouts: stats?.counts?.[t.name] ?? 0,
    fields: t.fields.slice(0, 14).map((f) => String(f.name).slice(0, 60)),
    comments: t.fields.map((f) => f.comment).filter(Boolean).slice(0, 4).map((c) => String(c).slice(0, 120)),
  }));
  // HARD CEILING. This call once went out at 13.7M tokens and came back a 400,
  // which silently degraded the whole pass to the heuristic - and the heuristic
  // proposes NO display names, which is why tables read as C__Container and
  // D_Org~B. A naming pass that cannot fit must shrink, not fail.
  const MAX_CHARS = 200000;
  let payload = JSON.stringify(summary, null, 1);
  if (payload.length > MAX_CHARS) {
    for (const t of summary) { t.fields = (t.fields || []).slice(0, 6); t.comments = []; t.occurrences = (t.occurrences || []).slice(0, 3); }
    payload = JSON.stringify(summary, null, 1);
  }
  if (payload.length > MAX_CHARS) payload = payload.slice(0, MAX_CHARS) + "\n…(truncated)";
  const system =
    "You are a FileMaker data architect helping decide which base tables of a solution are worth " +
    "replicating into a SQL reporting copy. Tier each table: 'core' (the central entities users " +
    "report on – whatever this solution is about: e.g. transactions, orders, cases, records, people), " +
    "'reference' (lookup/supporting data useful in reports: categories, notes, contact details), or 'system' (utility/UI/dev plumbing not " +
    "worth reporting: logs, dashboards, chart configs, field definitions, example data, join/selector " +
    "helper tables). Signals: many layouts referencing a table and higher row counts suggest importance; " +
    "developer field comments reveal intent. Set include=true for core and reference, false for system. " +
    "Each `name` is derived from the table's occurrence names and may be a cryptic prefixed fragment " +
    "(P__Person, REP_ort, HEXP__Harvest Expense); also propose displayName, the clean human name of the " +
    "underlying entity, inferred from the occurrence names, fields, and comments (e.g. \"Person\", " +
    "\"Report\", \"Harvest Expense\"). Singular, title case, no prefixes. " +
    "Reply ONLY with a JSON array of {name, displayName, tier, include, reason} where reason is <=12 words. " +
    "Where a table has candidateFiles, ALSO set homeFile: the file the base table most likely LIVES in. " +
    "Judge by file naming (a file named after the entity is its home; e.g. SubQuotes lives in 'New Quotes', " +
    "not in a hub UI file like 'New Master' that references everything) and by TO naming per file. " +
    "homeFile MUST be one of that table's candidateFiles keys. " +
    "A row's rows:null means the count failed, not an empty table.";
  const text = await callAnthropic(system, "Tables:\n" + payload, 8192);
  const jsonStr = text.slice(text.indexOf("["), text.lastIndexOf("]") + 1);
  return JSON.parse(jsonStr);
}

async function getRelevance(schema, stats, { light = false } = {}) {
  try {
    const cached = JSON.parse(fs.readFileSync(RELEVANCE_PATH, "utf8"));
    // A "light" ranking (no layout stats – computed for a fast Settings open)
    // satisfies light callers, but /api/schema recomputes it with full stats.
    // A heuristic ranking carries no displayNames, so a scan run before the key
    // was set leaves raw occurrence names (C__Container, D_Org~B) cached as
    // valid. Once a key exists, that cache is stale by definition — re-rank.
    // Provisional means "we could not ask the AI at the time". If we CAN ask
    // now, that cache is stale by definition however recent it looks.
    const staleHeuristic = (cached.provisional || String(cached.source || "").startsWith("heuristic")) && aiKey();
    if (cached.version === schema.version && !(cached.basis === "light" && !light) && !staleHeuristic) return cached;
  } catch { /* no cache yet */ }
  let ranking, source;
  if (aiKey()) {
    try { ranking = await aiRelevance(schema.tables, stats); source = "ai"; }
    catch (e1) {
      console.error("[naming] first attempt failed:", e1?.message || e1);
      {
        // These failures are usually transient (timeout, overload) or size-
        // related, and the second attempt costs far less than shipping a
        // schema full of raw occurrence names.
        try { ranking = await aiRelevance(schema.tables, stats); source = "ai"; }
        catch (e2) { ranking = heuristicRelevance(schema.tables, stats); source = "heuristic (AI failed: " + String(e2.message).slice(0, 80) + ")"; }
      }
    }
  } else {
    ranking = heuristicRelevance(schema.tables, stats);
    source = "heuristic (no AI key set, so table names and ranking are guessed)";
  }
  const out = { version: schema.version, basis: light ? "light" : "full", source, ranking, rankedAt: new Date().toISOString(),
                provisional: source.startsWith("heuristic") };
  try { fs.writeFileSync(RELEVANCE_PATH, JSON.stringify(out, null, 2)); } catch { /* read-only fs ok */ }
  return out;
}

// Re-home merged tables to their AI-proposed home file and re-derive the real
// name from THAT file's occurrence names. OData can't tell us where a base
// table lives (GOTCHAS.md), and the most-occurrences heuristic crowns the hub
// UI file, so the ranking's homeFile – validated against the candidates we
// actually saw – is the authority. Renames flow into ranking entries (rawName
// preserved so a cached ranking still matches a fresh schema fetch) and edges.
function rehomeAndRename(schema, relevance) {
  // IDENTITY IS FROZEN HERE (2026-08-18, after the afternoon that proved why).
  //
  // A table's NAME is its identity: it keys the saved selection, the cube, the
  // watermarks, and the sync plan. This function used to RENAME tables from
  // the AI ranking (re-derived names for re-homed tables, first-claimant
  // suffixing in ranking order) - so every scan could mint different names,
  // orphaning the user's picks. Symptoms in the field: picked tables silently
  // dropped from syncs, "complete" after one table, every checkbox clearing
  // itself. One flaw, four costumes.
  //
  // Now: rehoming still moves a table's HOME (db + query occurrence), because
  // querying the right file matters. But t.name never changes after the
  // schema merge. Everything the AI proposes becomes a DISPLAY name -
  // presentation stacked on identity, never identity itself.
  const byName = new Map(schema.tables.map((t) => [t.name, t]));
  let entriesChanged = false;
  for (const r of relevance.ranking || []) {
    const t = byName.get(r.rawName ?? r.name) || byName.get(r.name);
    if (!t) continue;
    if (r.homeFile && t.occByDb?.[r.homeFile] && r.homeFile !== t.db) {
      t.db = r.homeFile;
      t.occurrences = t.occByDb[r.homeFile];
      // The home file's own occurrence names usually make the better LABEL.
      const derived = baseName(t.occurrences);
      if (derived && derived !== t.name && !r.displayName) { r.displayName = derived; entriesChanged = true; }
    }
    // Ranking entries track the STABLE name so a cached ranking keeps matching.
    if (r.name !== t.name) { if (r.rawName === undefined) r.rawName = r.name; r.name = t.name; entriesChanged = true; }
  }
  if (entriesChanged) { try { fs.writeFileSync(RELEVANCE_PATH, JSON.stringify(relevance, null, 2)); } catch { /* read-only fs ok */ } }
  return schema;
}

// --- SaXML structural hints (ground truth OData can't provide) -------------
// Uploaded FMSaveAsXML gives, per base table: the real name, its home FILE,
// the primary key, the modification-timestamp field, and comments. OData/Data
// API expose none of these, so Pythia otherwise guesses. Hints are matched to
// live tables by field-name overlap and layered on top – additive, so an
// instance with no hints behaves exactly as before.
const HINTS_PATH = path.join(DATA_DIR, "schema-hints.json");
function loadHints() { try { return JSON.parse(fs.readFileSync(HINTS_PATH, "utf8")); } catch { return null; } }
function saveHints(h) { fs.writeFileSync(HINTS_PATH, JSON.stringify(h, null, 2)); }

// Enrich a freshly-fetched schema with hints, in place. Sets, per matched
// table: saxmlName, homeFile, comment, pk, modField, createField, hintFields;
// and re-homes the table to its true file when we hold an occurrence there.
function applyHints(schema) {
  const hints = loadHints();
  if (!hints || !schema?.tables?.length) return schema;
  const idx = hintIndex(hints);
  const dbOf = (file) => file.replace(/\.fmp12$/i, "");
  let matched = 0;
  for (const t of schema.tables) {
    const m = matchTable((t.fields || []).map((f) => f.name), idx, { preferFile: t.db });
    if (!m) continue;
    matched++;
    const ht = m.table;
    t.saxmlName = ht.name;
    t.homeFile = dbOf(m.file);
    if (ht.comment) t.comment = ht.comment;
    if (ht.pk && !t.pk) { t.pk = ht.pk; t.pkKind = ht.pkKind; }
    if (ht.modField && !t.modField) t.modField = ht.modField;
    if (ht.createField && !t.createField) t.createField = ht.createField;
    t.hintFields = ht.fields;
    // SaXML knows which calcs STORE their results; OData does not. Marking
    // them here lets the sync keep cheap stored calcs and skip only the
    // expensive unstored ones.
    const storedSet = new Set((ht.fields || []).filter((f) => f.storedCalc).map((f) => f.name));
    if (storedSet.size) for (const f of t.fields || []) if (storedSet.has(f.name)) f.storedCalc = true;
    // Re-home to the true file only when we actually have an occurrence there
    // to query; otherwise keep the queryable db but still show the true home.
    if (t.occByDb && t.occByDb[t.homeFile] && t.homeFile !== t.db) {
      t.db = t.homeFile;
      t.occurrences = t.occByDb[t.homeFile];
    }
  }
  schema.hintsApplied = matched;
  return schema;
}

// Schema (base tables, counts, per-table layout reference counts, AI relevance
// ranking), cached in memory per boot. ?refresh=1 re-reads from the server and
// re-ranks.
let schemaCache = null;

// Every consumer of table names (sync, chat, reports) must see the SAME homed
// and renamed tables the Settings UI shows, or selections stop matching.
async function rehomedSchema(onEvent = () => {}) {
  if (schemaCache) { onEvent({ type: "phase", message: "Using cached schema" }); return schemaCache; }
  onEvent({ type: "phase", message: "Reading schema from FileMaker…" });
  let schema;
  try {
    schema = await fetchSchema(false, onEvent);
  } catch (e) {
    // OData down – try the Data API fallback before giving up (see fm.js).
    onEvent({ type: "phase", message: "OData is down – switching to Data API fallback…" });
    schema = await fetchSchemaDataApi(onEvent);
  }
  onEvent({ type: "phase", message: "Applying table names & file homes…" });
  try { rehomeAndRename(schema, await getRelevance(schema, { counts: {} }, { light: true })); } catch { /* un-homed is still usable */ }
  applyHints(schema); // SaXML ground truth wins over the AI/heuristic homing
  return schema;
}
app.get("/api/schema", async (req, res) => {
  try {
    if (!fmConfigured()) return res.json({ notConnected: true, tables: [] });
    if (!schemaCache || req.query.refresh) {
      const schema = await fetchSchema(Boolean(req.query.refresh));
      const counts = await fetchCounts(schema.tables);
      for (const t of schema.tables) t.rowCount = counts[t.name] ?? null;
      let stats = { counts: {}, layoutsByTable: {}, aiLayouts: [], totalLayouts: null };
      try { stats = await layoutStats(schema.tables); } catch (e) { schema.layoutStatsError = String(e.message); }
      for (const t of schema.tables) {
        t.layoutCount = stats.counts[t.name] ?? 0;
        t.layoutNames = stats.layoutsByTable[t.name] ?? [];
      }
      if (req.query.refresh) { try { fs.unlinkSync(RELEVANCE_PATH); } catch {} }
      const relevance = await getRelevance(schema, stats);
      schema.relevanceSource = relevance.source;
      rehomeAndRename(schema, relevance);
      applyHints(schema); // SaXML ground truth wins over the AI/heuristic homing
      tablesCache = null; // table names/homes may have changed
      const byName = Object.fromEntries(relevance.ranking.map((r) => [r.name, r]));
      for (const t of schema.tables) {
        const r = byName[t.name] || { tier: "reference", include: true, reason: "" };
        t.tier = r.tier; t.suggestInclude = r.include; t.reason = r.reason;
      }
      schema.counts = counts;
      schema.aiLayouts = stats.aiLayouts;
      schema.totalLayouts = stats.totalLayouts;
      schemaCache = schema;
    }
    res.json(schemaCache);
  } catch (e) {
    res.status(502).json({ error: String(e.message || e) });
  }
});

// --- Model discovery: the list comes from Anthropic, never from hard-code. --
// Cached on the volume + memory (24h); refreshed after every successful sync
// and lazily on boot. The static fallback exists only for no-key/no-network.
const MODELS_PATH = path.join(DATA_DIR, "models.json");
// Offline-only fallback. created_at matters: the UI sorts by it to pick each
// family's current + previous. Keep this list fresh when models ship — a stale
// fallback is how "Opus 5 (no longer offered)" happened.
const FALLBACK_MODELS = [
  { id: "claude-fable-5", display_name: "Claude Fable 5", created_at: "2026-07-24T00:00:00Z" },
  { id: "claude-opus-5", display_name: "Claude Opus 5", created_at: "2026-07-24T00:00:00Z" },
  { id: "claude-opus-4-8", display_name: "Claude Opus 4.8", created_at: "2026-02-05T00:00:00Z" },
  { id: "claude-sonnet-5", display_name: "Claude Sonnet 5", created_at: "2026-05-14T00:00:00Z" },
  { id: "claude-sonnet-4-6", display_name: "Claude Sonnet 4.6", created_at: "2025-11-14T00:00:00Z" },
  { id: "claude-haiku-4-5", display_name: "Claude Haiku 4.5", created_at: "2025-10-01T00:00:00Z" },
];
let modelsCache = null; // { models, fetchedAt }
function loadModelsCache() {
  if (!modelsCache) { try { modelsCache = JSON.parse(fs.readFileSync(MODELS_PATH, "utf8")); } catch { /* none yet */ } }
  return modelsCache;
}
let modelsFailAt = 0; // last failed refresh, for the backoff below
async function refreshModels(force = false) {
  const cached = loadModelsCache();
  if (!force && cached && Date.now() - new Date(cached.fetchedAt).getTime() < 86400000) return cached;
  if (Date.now() - modelsFailAt < 300000) return cached; // failed recently; do not hammer
  // The ANTHROPIC key specifically. aiKey() is provider-aware, so with an
  // OpenAI model selected it would hand that provider's secret to
  // Anthropic's endpoint. Same lesson as the Test button below.
  const key = readConfig().aiKey || ENV_KEY;
  if (!key) return cached;
  try {
    const r = await fetch("https://api.anthropic.com/v1/models?limit=100", {
      headers: { "x-api-key": key, "anthropic-version": "2023-06-01" },
      signal: AbortSignal.timeout(15000),
    });
    if (!r.ok) throw new Error(`models ${r.status}`);
    const models = ((await r.json()).data || []).map((m) => ({ id: m.id, display_name: m.display_name, created_at: m.created_at }));
    if (models.length) {
      modelsCache = { models, fetchedAt: new Date().toISOString() };
      fs.writeFileSync(MODELS_PATH, JSON.stringify(modelsCache, null, 2));
    }
  } catch (e) {
    // Back off after a failure. A bad or absent Anthropic key fails on EVERY
    // settings poll, and the repeated line buries real errors in the log.
    modelsFailAt = Date.now();
    console.error("model list refresh failed:", e?.message || e);
  }
  return modelsCache || cached;
}
// Is this key good? Answered against a key the user just typed but hasn't
// saved (same transient contract as /api/fm/test), or the stored one if the
// field was left blank. /v1/models is the cheapest possible ACK: it costs no
// tokens and still proves the key authenticates.
app.post("/api/ai/test", async (req, res) => {
  // Which provider is being tested is stated by the caller, because the user
  // may be testing a key BEFORE saving the model that would select it.
  if (req.body?.provider === "openai") {
    const okey = String(req.body?.key || "").trim() || openaiKey();
    if (!okey) return res.json({ ok: false, error: "No OpenAI key to test — paste one above, or save one first." });
    try {
      const r = await fetch("https://api.openai.com/v1/models", {
        headers: { authorization: `Bearer ${okey}` }, signal: AbortSignal.timeout(15000),
      });
      if (r.status === 401 || r.status === 403) return res.json({ ok: false, error: "OpenAI rejected that key." });
      if (!r.ok) return res.json({ ok: false, error: `OpenAI answered ${r.status}. The key may be fine; try again shortly.` });
      const ids = ((await r.json()).data || []).map((m) => m.id);
      const gpt = ids.filter((i) => /^gpt-5/.test(i)).sort();
      return res.json({ ok: true, model: gpt[0] || ids[0] || "", count: ids.length });
    } catch (e) {
      return res.json({ ok: false, error: /abort|timeout/i.test(String(e?.message || e)) ? "Timed out reaching OpenAI." : String(e?.message || e).slice(0, 160) });
    }
  }
  // Test the ANTHROPIC key specifically: aiKey() is provider-aware and, with
  // an OpenAI model selected, would hand the OpenAI key to Anthropic's
  // endpoint (the cross-tab Test failure Matt hit 2026-08-23).
  const c0 = readConfig();
  const key = String(req.body?.key || "").trim() || c0.aiKey || ENV_KEY;
  if (!key) return res.json({ ok: false, error: "No key to test — paste one above, or save one first." });
  try {
    const r = await fetch("https://api.anthropic.com/v1/models?limit=1", {
      headers: { "x-api-key": key, "anthropic-version": "2023-06-01" },
      signal: AbortSignal.timeout(15000),
    });
    if (r.status === 401 || r.status === 403) return res.json({ ok: false, error: "Anthropic rejected that key." });
    if (!r.ok) return res.json({ ok: false, error: `Anthropic answered ${r.status}. The key may be fine; try again shortly.` });
    const first = ((await r.json()).data || [])[0];
    res.json({ ok: true, model: first?.display_name || first?.id || "" });
  } catch (e) {
    res.json({ ok: false, error: /abort|timeout/i.test(String(e?.message || e)) ? "Timed out reaching Anthropic." : String(e?.message || e).slice(0, 160) });
  }
});

app.get("/api/models", async (_req, res) => {
  let c = await refreshModels();
  // Self-heal a stale cache: if the model we're configured to USE isn't in the
  // list we're about to serve, the list is wrong, not the model — refetch now.
  // Only meaningful while an Anthropic model is picked. A gpt-* model is
  // never in this list, and forcing a refetch on every call is a
  // guaranteed miss, not a stale cache.
  const own = !isOpenAIModel(aiModel());
  if (own && c?.models?.length && !c.models.some((m) => m.id === aiModel())) c = await refreshModels(true);
  if (c && c.models?.length) return res.json({ ...c, fallback: false });
  res.json({ models: FALLBACK_MODELS, fetchedAt: null, fallback: true });
});

setTimeout(() => { refreshModels().catch(() => {}); }, 3000); // boot warm-up, non-blocking

// --- Scheduled sync: config.syncEvery minutes (0 = off). Honest caveat: on
// Fly the machine sleeps when idle, so ticks run while awake; the FM-Server-
// schedule pattern in SETUP.md wakes the machine and is the reliable clock.
let autoSyncBusy = false;
async function autoSyncTick() {
  try {
    const c = readConfig();
    const every = Number(c.syncEvery || 0);
    if (!every || autoSyncBusy || !fmConfigured() || c.needsScan) return;
    if (syncJob && !syncJob.done) return; // a user-started sync owns the cube right now
    const include = c.includeTables || [];
    if (!include.length) return;
    const last = cubeManifest()?.syncedAt ? new Date(cubeManifest().syncedAt).getTime() : 0;
    if (Date.now() - last < every * 60000) return;
    autoSyncBusy = true;
    console.log(`[auto-sync] due (${every}m); starting a sync job for ${include.length} tables`);
    // As a job: visible in the app if anyone is watching, cancellable, and
    // structurally incapable of overlapping any other sync.
    const job = startSyncJob(include, { origin: "auto" });
    await job.promise;
    if (job.error) console.error("[auto-sync] failed:", job.error);
  } catch (e) { console.error("[auto-sync]", e?.message || e); }
  finally { autoSyncBusy = false; }
}
setInterval(autoSyncTick, 60000);

// A successful connect is evidence with an expiry date, not a permanent badge:
// a password can be revoked server-side and yesterday's success proves nothing
// about today. Stored WITHOUT the password.
function saveVerified({ host, user }) {
  const c = readConfig();
  c.fmVerified = { at: new Date().toISOString(), host, user };
  fs.writeFileSync(CONFIG_PATH, JSON.stringify(c, null, 2));
}
function verifiedState() {
  const c = readConfig(), v = c.fmVerified;
  const conn = fmConnection();
  if (!conn.host || !conn.user) return { state: "unset" };
  // Edited since it was proven? Then it is unproven again.
  if (!v || v.host !== conn.host || v.user !== conn.user) return { state: "untested" };
  return { state: "verified", at: v.at };
}

app.get("/api/config", (_req, res) => {
  const c = readConfig();
  // AI config for the Settings panel: current model/speed/thinking + whether a
  // key is set (NEVER the key itself). keyIsCustom = a key was saved in-app.
  res.json({ includeTables: c.includeTables || [], displayNames: getDisplayNames(), savedAt: c.savedAt || null,
    syncInstructions: c.syncInstructions || "",
    needsScan: Boolean(c.needsScan), syncEvery: Number(c.syncEvery || 0),
    fm: { ...fmConnection(), verified: verifiedState() }, // never the password
    ai: { model: aiModel(), speed: aiSpeed(), thinking: aiThinking(), hasKey: Boolean(aiKey()), keyIsCustom: Boolean(c.aiKey), keyPreview: ((k) => k ? k.slice(0, 8) + "…" + k.slice(-4) : "")(aiKey()),
      provider: isOpenAIModel(aiModel()) ? "openai" : "anthropic",
      hasOpenaiKey: Boolean(openaiKey()), openaiPreview: ((k) => k ? k.slice(0, 8) + "…" + k.slice(-4) : "")(openaiKey()),
      // hasKey/keyPreview answer "is the AI configured at all", so they follow
      // the picked provider. The Claude card needs the ANTHROPIC key
      // specifically, or it shows an OpenAI preview under an Anthropic label.
      hasAnthropicKey: Boolean(c.aiKey || ENV_KEY), anthropicPreview: ((k) => k ? k.slice(0, 8) + "…" + k.slice(-4) : "")(c.aiKey || ENV_KEY) } });
});

// Lightweight base-table list for the settings panel (names + row counts only,
// no layout/relevance work) so Settings opens fast. Every successful build is
// snapshotted to the volume; when the FileMaker OData engine is down (a
// recurring event on some servers), Settings serves the snapshot with
// stale:true plus a diagnosis instead of a bare 502.
const SNAPSHOT_PATH = path.join(DATA_DIR, "schema-snapshot.json");
let tablesCache = null;
let tablesCacheAt = 0;
let tablesBuild = null; // in-flight build promise (Data API fallback can take minutes)
let scanStatus = null;  // one-line progress text while a build runs; null when idle
// The ONE way to start a build. A connection save nulls tablesBuild while an
// old build still runs; when that orphan finally settles, it must not null
// the slot again and orphan the NEW build (which would let a third start).
function startTablesBuild(refresh) {
  const p = buildTablesCache(refresh);
  tablesBuild = p;
  p.finally(() => { if (tablesBuild === p) tablesBuild = null; }).catch(() => {});
  return p;
}

// The first fallback build probes every layout on the FM server and can run
// for minutes – far past proxy timeouts. So the endpoint never blocks past
// ~20s: a slow build keeps running server-side, the client gets
// {building:true} and polls. The cache is also warmed at boot.
// Are the current display names real (AI/SaXML/user) or a heuristic stopgap?
function namesProvisional() {
  try { return Boolean(JSON.parse(fs.readFileSync(RELEVANCE_PATH, "utf8")).provisional); } catch { return false; }
}
// Re-run the naming pass without a full rescan: getRelevance treats a
// heuristic cache as stale the moment a key exists, so a rebuild re-asks the AI.
app.post("/api/names/refresh", async (_req, res) => {
  try {
    if (!aiKey()) return res.status(400).json({ error: "Set the AI key first - naming needs it." });
    if (!tablesBuild) startTablesBuild(false);
    await tablesBuild;
    res.json({ ok: true, namesProvisional: namesProvisional() });
  } catch (e) { res.status(502).json({ error: String(e.message || e) }); }
});

app.get("/api/tables", async (_req, res) => {
  if (!fmConfigured()) return res.json({ notConnected: true, tables: [] });
  const expired = tablesCache && tablesCache.via === "dataapi" && Date.now() - tablesCacheAt > 300000;
  if (tablesCache && !_req.query.refresh && !expired) return res.json({ ...tablesCache, namesProvisional: namesProvisional() });
  if (!tablesBuild) startTablesBuild(Boolean(_req.query.refresh));
  const result = await Promise.race([tablesBuild, new Promise((r) => setTimeout(() => r("__building__"), 20000))]);
  if (result === "__building__") {
    // Serve the expired fallback cache while rebuilding, else the building flag.
    return res.json(tablesCache && expired ? { ...tablesCache, namesProvisional: namesProvisional() } : { building: true });
  }
  // A superseded build resolves with whatever cache exists - possibly none.
  // Answer "building" so the client keeps polling the build that took over.
  if (!result) return res.json(tablesCache ? { ...tablesCache, namesProvisional: namesProvisional() } : { building: true });
  res.json({ ...result, namesProvisional: namesProvisional() });
});

// FileMaker Server version, from the Data API's unauthenticated product-info
// endpoint. Behavior differences across FMS releases are real (OData features
// ship per version), so the connection panel shows what we're talking to.
let fmVersionCache = { host: null, at: 0, info: null };
app.get("/api/fm/version", async (_req, res) => {
  const host = fmConnection().host;
  if (!host) return res.json({});
  if (fmVersionCache.host === host && Date.now() - fmVersionCache.at < 3600000) return res.json(fmVersionCache.info || {});
  try {
    const ctl = new AbortController(); const tm = setTimeout(() => ctl.abort(), 8000);
    const r = await fetch(`https://${host}/fmi/data/v1/productInfo`, { signal: ctl.signal });
    clearTimeout(tm);
    const d = await r.json();
    const p = d?.response?.productInfo || {};
    fmVersionCache = { host, at: Date.now(), info: { name: p.name || "FileMaker Server", version: p.version || null, dataApiVersion: p.dateFormat ? "v1" : "v1" } };
  } catch { fmVersionCache = { host, at: Date.now(), info: {} }; }
  res.json(fmVersionCache.info || {});
});

// The scan popup polls this while /api/tables reports building:true.
app.get("/api/scan/progress", (_req, res) => {
  if (!scanStatus) return res.json({ idle: true });
  if (scanStatus.phase === "file") {
    const secs = Math.floor((Date.now() - scanStatus.t0) / 1000);
    // Quick files come and go with a plain line; a file at it for 5+ seconds
    // gets an elapsed count that updates every 5s so the screen visibly
    // moves. No editorializing about why (Matt, 2026-08-26: "dumb").
    const text = secs < 5
      ? `Reading the structure of ${scanStatus.db}…`
      : `Still reading ${scanStatus.db} — ${Math.floor(secs / 5) * 5}s so far`;
    return res.json({ text });
  }
  res.json(scanStatus);
});

// Incremental-sync readiness, mirroring cube.js syncTable: needs a primary key
// (a `keys` entry or a field named "id") AND a modification timestamp
// (a DateTimeOffset field with "mod" in the name). Missing either = every sync
// is a full-table pull (slow). Surfaced per-table so Settings can warn.
const syncReadiness = (t) => ({
  hasKey: Boolean(t.pk) || Boolean(t.keys?.[0]) || (t.fields || []).some((f) => /^id$/i.test(f.name)),
  hasMod: Boolean(t.modField) || (t.fields || []).some((f) => f.type === "DateTimeOffset" && /mod/i.test(f.name)),
});

let connGen = 0; // bumped on every connection save; stale builds see it and stop

async function buildTablesCache(refresh) {
  const gen = connGen;
  scanStatus = { text: "Contacting the server…" };
  try {
      const schema = await fetchSchema(Boolean(refresh), (evt) => {
        // The user changed the plan mid-scan (picked different files). This
        // build is now scanning the WRONG list - it used to keep going and
        // its progress lines interleaved with the new build's ("still
        // scanning everything", Matt 2026-08-26). Die at the file boundary.
        if (gen !== connGen) { const e = new Error("superseded by a newer connection"); e.superseded = true; throw e; }
        if (evt.type === "schema-dbs") scanStatus = { text: `Found ${evt.dbs.length} database file${evt.dbs.length === 1 ? "" : "s"}` };
        // A slow link makes each file's structure read take a minute or more;
        // without a line at the START of each file the whole scan looks hung
        // (Matt, local instance, 2026-08-26).
        if (evt.type === "schema-file-start") scanStatus = { phase: "file", db: evt.db, t0: Date.now() };
        if (evt.type === "schema-file" && !evt.error) scanStatus = { text: `${evt.db}: schema read · ${evt.tables} tables` };
      });
      const counts = await fetchCounts(schema.tables,
        (done, totalN) => { if (gen === connGen) scanStatus = { text: `Counting records · ${done} of ${totalN} tables` }; },
        () => gen !== connGen);
      for (const t of schema.tables) t.rowCount = counts[t.name] ?? null; // attach BEFORE renames
      // Apply AI homing + display naming so Settings shows tables under the
      // file they live in. Uses the cached ranking when present; otherwise a
      // "light" ranking (no layout stats) so the first Settings open works.
      try { rehomeAndRename(schema, await getRelevance(schema, { counts: {} }, { light: true })); } catch { /* FM-only view is fine */ }
      applyHints(schema); // SaXML ground truth: home file, real name, PK, mod field, comments
      // A count that failed over the wire (a ~ in every occurrence name, a
      // timeout) is not "no records": the cube's manifest knows how many rows
      // the last sync actually landed. Never show a dash for a synced table.
      try {
        const manRows = new Map((cubeManifest().tables || []).map((mt) => [mt.name, mt.rows]));
        for (const t of schema.tables) if (t.rowCount == null && manRows.has(t.name)) t.rowCount = manRows.get(t.name);
      } catch { /* no cube yet */ }
      if (!schema.tables.length && (schema.dbErrors || []).length) throw new Error(`No file reachable over OData: ${schema.dbErrors[0].error}`);
      // Sizing advice belongs at scan time: this is the moment we first know
      // how big the data is AND how big the machine is (Matt, 2026-08-25).
      const largest = Math.max(0, ...schema.tables.map((t) => Number(t.rowCount) || 0));
      const mem = machineMemory();
      const recMB = recommendMemoryMB(largest);
      if (gen !== connGen) { const e = new Error("superseded by a newer connection"); e.superseded = true; throw e; }
      tablesCache = { db: schema.db, dbs: schema.dbs, dbErrors: schema.dbErrors || [], scannedAt: new Date().toISOString(), hints: Boolean(schema.hintsApplied),
        sizing: { usableMB: mem.usableMB, recommendedMB: recMB, largestTableRows: largest,
                  // A nominal 1GB machine reports ~962MB usable; grace of 10%
                  // of the recommendation stops the false nag at exact size.
                  short: mem.usableMB < recMB * 0.9 },
        tables: schema.tables.map((t) => ({ name: t.name, db: t.db, rowCount: t.rowCount, fields: t.fields.length, occurrences: t.occurrences.length, occurrenceNames: t.occurrences.slice(0, 20), comment: t.comment || null, ...syncReadiness(t) })) };
      tablesCacheAt = Date.now();
      try { fs.writeFileSync(SNAPSHOT_PATH, JSON.stringify({ savedAt: new Date().toISOString(), ...tablesCache })); } catch { /* read-only fs ok */ }
      clearScanGate();
      scanStatus = null;
    return tablesCache;
  } catch (e) {
    // A superseded build is not a broken server: the user changed the plan
    // and a NEWER build owns the scan now. Die quietly - running the Data API
    // fallback here spent minutes probing layouts, then overwrote the fresh
    // build's cache with a false "OData is not answering" diagnosis.
    if (e && e.superseded) return tablesCache;
    // OData is down. First choice: full Data API fallback – live tables,
    // reachable through layouts, syncable (slower, full pulls). Only if THAT
    // fails too do we drop to the snapshot + diagnosis.
    try {
      const schema = await fetchSchemaDataApi();
      if (gen !== connGen) return tablesCache; // superseded while falling back
      try { rehomeAndRename(schema, await getRelevance(schema, { counts: {} }, { light: true })); } catch { /* names as-is */ }
      applyHints(schema); // SaXML ground truth: home file, real name, PK, mod field, comments
      try {
        const manRows = new Map((cubeManifest().tables || []).map((mt) => [mt.name, mt.rows]));
        for (const t of schema.tables) if (t.rowCount == null && manRows.has(t.name)) t.rowCount = manRows.get(t.name);
      } catch { /* no cube yet */ }
      tablesCache = { db: schema.db, dbs: schema.dbs, dbErrors: schema.dbErrors || [], via: "dataapi", scannedAt: new Date().toISOString(), hints: Boolean(schema.hintsApplied),
        diagnosis: "OData on your FileMaker Server is not answering right now, so Pythia is working through the Data API instead: tables stay reachable via layouts, syncs still run (full pulls unless SaXML supplies keys). This usually clears on its own; Pythia keeps checking and switches back automatically.",
        tables: schema.tables.map((t) => ({ name: t.name, db: t.db, rowCount: t.rowCount, fields: t.fields.length, occurrences: t.occurrences.length, occurrenceNames: t.occurrences.slice(0, 20), comment: t.comment || null, ...syncReadiness(t) })) };
      tablesCacheAt = Date.now();
      clearScanGate();
      scanStatus = null;
      return tablesCache;
    } catch { /* Data API fallback also failed */ }
    scanStatus = null;
    let serverFiles = null;
    try { serverFiles = await listDatabasesDataApi(); } catch { /* whole server down */ }
    let snapshot = null;
    try { snapshot = JSON.parse(fs.readFileSync(SNAPSHOT_PATH, "utf8")); } catch { /* none yet */ }
    const diagnosis = serverFiles
      ? "OData on your FileMaker Server is not answering, though the server itself is up (the Data API responds). Pythia will keep retrying; this usually clears on its own. If it persists for hours, mention it to whoever manages that FileMaker Server."
      : "Your FileMaker Server is not responding at all right now. Check that the machine is up and reachable from the internet.";
    return { ...(snapshot || { db: null, dbs: [], dbErrors: [], tables: [] }), stale: true,
      staleSavedAt: snapshot?.savedAt || null, serverFiles, error: String(e.message || e), diagnosis };
  }
}

// Save table selection and/or friendly display names. Merges into the existing
// config so it never clobbers dataSource or the other field.
app.post("/api/config", async (req, res) => {
  const config = readConfig();
  if (Array.isArray(req.body?.includeTables)) config.includeTables = req.body.includeTables;
  if (req.body?.displayNames && typeof req.body.displayNames === "object") config.displayNames = { ...(config.displayNames || {}), ...req.body.displayNames };
  if (typeof req.body?.syncInstructions === "string") config.syncInstructions = req.body.syncInstructions.slice(0, 20000);
  const ai = req.body?.ai;
  if (ai && typeof ai === "object") {
    // A key slot must never accept a URL. A provider-switch bug once sent a
    // server URL into aiKey and DESTROYED a stored Anthropic key with no
    // warning (2026-08-26). Overwriting a secret is unrecoverable, so the
    // shape is checked here rather than trusted from the caller.
    const looksLikeUrl = (v) => /^https?:\/\//i.test(v) || /^[a-z0-9.-]+:\d{2,5}$/i.test(v);
    if (typeof ai.aiKey === "string" && ai.aiKey.trim()) {
      if (looksLikeUrl(ai.aiKey.trim())) return res.status(400).json({ error: "That looks like a server URL, not an API key. The Anthropic key was left unchanged." });
      config.aiKey = ai.aiKey.trim(); // blank => keep current key
    }
    // OpenAI lives beside Anthropic, never replacing it: the model picked
    // decides which key is used, so both can be stored at once.
    if (typeof ai.openaiKey === "string" && ai.openaiKey.trim()) {
      if (looksLikeUrl(ai.openaiKey.trim())) return res.status(400).json({ error: "That looks like a server URL, not an API key. The OpenAI key was left unchanged." });
      config.openaiKey = ai.openaiKey.trim();
    }
    if (typeof ai.model === "string") {
      config.aiModel = ai.model;
      // Picking anything other than the family's current model is a deliberate pin.
      const newest = newestOfFamily(modelFamily(ai.model));
      config.aiModelPinned = Boolean(newest && newest !== ai.model);
    }
    if (typeof ai.speed === "string") config.aiSpeed = ai.speed;
    if (typeof ai.thinking === "string") config.aiThinking = ai.thinking;
  }
  if (req.body?.syncEvery !== undefined) config.syncEvery = Math.max(0, Number(req.body.syncEvery) || 0);
  const ctx = req.body?.context;
  if (ctx && typeof ctx === "object") {
    if (typeof ctx.aboutDatabase === "string") { config.aboutDatabase = ctx.aboutDatabase.trim(); config.contextEdited = true; }
    if (typeof ctx.reportingPriorities === "string") config.reportingPriorities = ctx.reportingPriorities.trim();
  }
  config.savedAt = new Date().toISOString();
  if (req.body?.fm && typeof req.body.fm === "object") {
    const cur = fmConnection(), nf = req.body.fm;
    const changed = (nf.host !== undefined && String(nf.host).trim() !== cur.host)
      || (nf.user !== undefined && String(nf.user).trim() !== cur.user)
      || (typeof nf.pass === "string" && nf.pass !== "");
    if (changed) delete config.fmVerified; // proof belongs to the OLD credentials
  }
  fs.writeFileSync(CONFIG_PATH, JSON.stringify(config, null, 2));
  if (req.body?.fm && typeof req.body.fm === "object") saveFmConnection(req.body.fm);
  res.json({ saved: true }); // never echo the config back (holds the key + password)
});

// Verify a FileMaker connection from Settings: apply the entered values, persist
// them, then ask the server which databases this account can actually see.
app.post("/api/fm/test", async (req, res) => {
  const fm = req.body?.fm || {};
  // persist:false (the Settings UI) = test the candidate transiently; nothing
  // is written unless the user Saves. Default (old behavior) persists first.
  const persist = req.body?.persist !== false;
  const authFail = { ok: false, error: "The server answered, but the username/password was rejected (or the account lacks the fmodata privilege)." };
  const doTest = async () => {
    if (!fmConfigured()) return { ok: false, error: "Fill in server, username, and password." };
    let databases = [], via = "odata";
    // FMS doesn't always answer a bad login with 401 on the OData root: it can
    // return 501 carrying FileMaker error 9 (insufficient privileges), which
    // used to reach the user as a raw JSON dump. Same cause, same message.
    const isAuthErr = (e) => {
      const s = String(e?.message || e);
      return /\b401\b|unauthorized/i.test(s) || (/\b501\b/.test(s) && /"code":\s*"?9"?/.test(s));
    };
    try { databases = await listDatabases(); }
    catch (e) {
      if (isAuthErr(e)) return authFail;
      try { databases = await listDatabasesDataApi(); via = "dataapi"; } catch { throw e; }
    }
    const want = fmConnection().db;
    // want is a comma list (or blank for "every visible file"), so every name
    // gets checked — testing only the first reported a clean connection while
    // later files in the list were quietly unreachable.
    const wanted = want.split(",").map((s) => s.trim()).filter(Boolean);
    const missing = wanted.filter((w) => !databases.includes(w));
    // The root list proves the SERVER answers, not that the login works (the
    // root and the Data API list both answer without auth on many servers).
    // The real credential check is opening one actual file over OData.
    let credentialsProven = via !== "odata"; // dataapi listing already needed auth
    if (via === "odata") {
      const target = wanted.find((w) => databases.includes(w)) || databases[0];
      if (target) {
        try { await checkAuth(target); credentialsProven = true; }
        catch (e) { if (isAuthErr(e)) return authFail; }
        // any other failure: server flaked mid-check - no badge either way
      }
    }
    if (credentialsProven && persist) saveVerified({ host: fmConnection().host, user: fmConnection().user });
    return { ok: true, databases, found: missing.length === 0, want, missing, verified: credentialsProven };
  };
  try {
    let result;
    if (persist) { saveFmConnection(fm); result = await doTest(); }
    else result = await withConnection(fm, doTest);
    res.json(result);
  } catch (e) {
    res.json({ ok: false, error: String(e.message || e).slice(0, 300) });
  }
});

// --- The cube (local SQL copy) ---------------------------------------------

app.get("/api/cube/status", (_req, res) => {
  res.json({ exists: cubeExists(), ...cubeManifest() });
});

// Rebuild the cube for the currently-configured tables (or ?tables=a,b).
// Compatibility endpoint for FileMaker scripts: same contract as always (the
// response arrives when the sync is DONE, carrying the manifest) but the work
// rides the job system - visible in the app, cancellable, mutually exclusive.
// If the caller's connection dies mid-wait, the job simply finishes without
// them instead of becoming an invisible orphan only a machine restart can kill.
app.post("/api/cube/sync", async (req, res) => {
  try {
    if (!fmConfigured()) return res.json({ notConnected: true, tables: [] });
    if (syncJob && !syncJob.done) return res.status(409).json({ error: "A sync is already running." });
    let include = req.body?.tables;
    // An EXPLICIT list means "sync these" - never "and silently drop the
    // rest". Reconciliation (dropping deselected tables) only runs on the
    // implicit whole-config sync. Found 2026-08-22: a 2-table API sync
    // deleted the other two tables from the cube.
    const explicit = Array.isArray(include) && include.length > 0;
    if (!explicit) include = configuredTables();
    if (!include.length) return res.status(400).json({ error: "No tables selected. Save a config or pass {tables:[...]}." });
    const job = startSyncJob(include, { origin: "api", reconcile: !explicit });
    await job.promise;
    if (job.error) return res.status(502).json({ error: job.error });
    res.json(cubeManifest());
  } catch (e) {
    res.status(502).json({ error: String(e.message || e) });
  }
});

// --- Sync runs HERE, as a job; the browser only watches -----------------------
// The old design ran the whole sync inside the SSE request, so the work's
// lifetime was tied to a browser tab. Now a sync is a server-side job with a
// buffered event log: the UI attaches (and re-attaches, from any point) to
// watch, Cancel is a request the job honors between tables, and a page reload
// mid-sync finds the job still running and picks the story back up.
let syncJob = null; // one at a time: { id, startedAt, events[], waiters[], cancelRequested, done, include }

function jobEmit(job, evt) {
  evt.seq = job.events.length;
  job.events.push(evt);
  for (const w of job.waiters.splice(0)) w();
}

// EVERY sync is a job - user click, FileMaker webhook, auto-sync timer, boot,
// and the blocking compatibility endpoint all come through here. That makes
// "one sync at a time", visibility, and Cancel properties of the SYSTEM: there
// is no door left that can run the engine invisibly (the door that existed
// outlived its dead client by an hour on 2026-08-10 and could only be killed
// by restarting the machine).
//
// reconcile:false syncs ONLY the named tables and drops nothing - the webhook
// path, where "sync what changed" must not delete every table that didn't.
function startSyncJob(include, { reconcile = true, origin = "user" } = {}) {
  resetCancelWrites();
  const job = {
    id: Math.random().toString(36).slice(2, 10),
    startedAt: new Date().toISOString(),
    events: [], waiters: [], cancelRequested: false, done: false, include, origin,
  };
  job.promise = new Promise((r) => { job._finish = r; });
  syncJob = job;
  (async () => {
    try {
      const schema = await rehomedSchema((evt) => jobEmit(job, evt));
      // Attach expected record counts to the plan event so the per-table
      // progress bars always have a denominator. The tables cache usually has
      // them; any chosen table still missing a count gets a quick $count here.
      const expected = {};
      for (const t of (tablesCache?.tables || [])) expected[t.name] = t.rowCount;
      const missing = schema.tables.filter((t) => include.includes(t.name) && expected[t.name] == null);
      if (missing.length) {
        try { Object.assign(expected, await fetchCounts(missing)); } catch { /* bars fall back to pulse */ }
      }
      const dbOf = {};
      for (const t of schema.tables) dbOf[t.name] = t.db || "";
      const onEvent = (evt) => {
        // The plan carries each table's database FILE so the modal can group
        // by file - the same shape the Settings Tables list uses.
        if (evt.type === "plan") evt = { ...evt, tables: evt.tables.map((n) => ({ name: n, expected: expected[n] ?? null, db: dbOf[n] || "" })) };
        jobEmit(job, evt);
      };
      // PRE-SYNC AI PASS (Matt, 2026-08-18: "the AI call before a sync - it is
      // better"). If the instructions box changed since last translated, ONE
      // call turns directive-shaped lines into engine settings: fields to
      // skip, tables that should ride the layout road. Deterministic engine,
      // AI only at the boundary. Failure = sync proceeds untranslated.
      try {
        const c0 = readConfig();
        const txt = (c0.syncInstructions || "").trim();
        const hash = txt ? String(txt.length) + ":" + [...txt].reduce((a, ch) => (a * 31 + ch.charCodeAt(0)) >>> 0, 0) : "";
        if (txt && hash !== c0.syncInstructionsHash && aiKey()) {
          jobEmit(job, { type: "phase", message: "Reading your sync instructions…" });
          const sys = "Translate the user's sync instructions for a FileMaker->DuckDB sync tool into JSON. " +
            'Return ONLY {"skipFields":{"<table>":["<field>",...]},"preferLayoutRoad":["<table>",...]} - empty objects when nothing applies. ' +
            "Only include DIRECTIVES (skip/never pull/use layout); ignore commentary and # learned lines you disagree with nothing.";
          const out = await callAnthropic(sys, "Instructions:\n" + txt.slice(0, 6000), 1500);
          const parsed = JSON.parse(out.slice(out.indexOf("{"), out.lastIndexOf("}") + 1));
          const c1 = readConfig();
          c1.skipFields = parsed.skipFields && typeof parsed.skipFields === "object" ? parsed.skipFields : {};
          c1.preferLayoutRoad = Array.isArray(parsed.preferLayoutRoad) ? parsed.preferLayoutRoad : [];
          c1.syncInstructionsHash = hash;
          fs.writeFileSync(CONFIG_PATH, JSON.stringify(c1, null, 2));
          const nSkips = Object.values(c1.skipFields).reduce((a, v) => a + v.length, 0);
          if (nSkips || c1.preferLayoutRoad.length) jobEmit(job, { type: "phase", message: `Instructions applied: ${nSkips} field skip(s), ${c1.preferLayoutRoad.length} layout preference(s).` });
        }
      } catch (e) { console.log("[instructions] translation skipped:", String(e.message).slice(0, 80)); }

      // Tables that time out ride the Data API road automatically (one strike).
      const learned = loadLearned();
      const slowTables = new Set(Object.keys(learned).filter((k) => k.startsWith("slow:") && (learned[k].count || 0) >= 1).map((k) => k.slice(5)));
      // Learned facts land in the user's instructions box, APPENDED and dated,
      // never replacing what they wrote. They can edit or delete any line.
      const appendInstruction = (line) => {
        try {
          const c = readConfig();
          const txt = c.syncInstructions || "";
          if (txt.includes(line)) return;
          c.syncInstructions = (txt ? txt.replace(/\s+$/, "") + "\n" : "") + `# learned ${new Date().toISOString().slice(0, 10)}: ${line}`;
          fs.writeFileSync(CONFIG_PATH, JSON.stringify(c, null, 2));
        } catch { /* best effort */ }
      };
      const onSlow = (name) => {
        appendInstruction(`${name}: OData rows are slow on this server - prefer the Data API layout road for this table`);
        try {
          const all = loadLearned(); const k = "slow:" + name;
          all[k] = { kind: "transport", note: "OData page timeout - after 2, rows travel via Data API layout", count: (all[k]?.count || 0) + 1, at: new Date().toISOString() };
          fs.writeFileSync(LEARNED_PATH, JSON.stringify(all, null, 2));
        } catch { /* best effort */ }
      };
      const cNow = readConfig();
      for (const t of cNow.preferLayoutRoad || []) slowTables.add(t); // user said: layout road
      const pageSizes = Object.fromEntries(Object.entries(learned)
        .filter(([k, v]) => k.startsWith("page:") && v.size).map(([k, v]) => [k.slice(5), v.size]));
      const engineOpts = { shouldStop: () => job.cancelRequested, slowTables, onSlow,
        pageSizes, skipFields: cNow.skipFields || {},
        onPageSize: (name, size) => { try { const all = loadLearned(); all["page:" + name] = { size, at: new Date().toISOString() }; fs.writeFileSync(LEARNED_PATH, JSON.stringify(all, null, 2)); } catch {} } };
      if (reconcile) {
        await reconcileCube(schema.tables, include, (m) => console.log("[sync]", m), onEvent, engineOpts);
      } else {
        onEvent({ type: "plan", tables: include, toDrop: [] });
        const out = await syncTables(schema.tables, include, (m) => console.log(`[sync:${origin}]`, m), onEvent,
          engineOpts);
        onEvent({ type: "done", totalRows: cubeManifest().totalRows,
                  tables: (cubeManifest().tables || []).map((t) => t.name), cancelled: Boolean(out?.cancelled) });
      }
      refreshModels(true).catch(() => {}); // sync doubles as the model-list heartbeat
      buildDerived().catch(() => {});     // rebuild the date spine + column stats
    } catch (e) {
      job.error = String(e.message || e);
      jobEmit(job, { type: "error", message: job.error });
    } finally {
      // The cancel flag dies WITH the job it cancelled. Left set, it silently
      // refused every later non-job write - webhook record deletes, the
      // calendar spine rebuild - until the next sync happened to reset it.
      resetCancelWrites();
      job.done = true;
      for (const w of job.waiters.splice(0)) w();
      job._finish();
    }
  })();
  return job;
}

// Start (or join) a sync. Tables sync in the order the user sees them in
// Settings: database file alphabetically, then display name within the file.
app.post("/api/cube/sync/start", async (req, res) => {
  if (syncJob && !syncJob.done) return res.json({ id: syncJob.id, already: true });
  if (!fmConfigured()) return res.status(400).json({ error: "FileMaker isn't connected. Open Settings and enter your server, database, and login." });
  let include = Array.isArray(req.body?.tables) && req.body.tables.length ? req.body.tables.slice() : configuredTables();
  if (!include.length) return res.status(400).json({ error: "No tables selected." });
  const names = readConfig().displayNames || {};
  // Run order MUST match the grouped display: file alpha, then friendly name
  // within the file. The old flat name-sort ran one file's tables before
  // another's while the display grouped by file - the eye hunted
  // for what was actually running (Matt, 2026-08-23).
  {
    // Sort from what is already KNOWN, never by awaiting a schema read. This
    // await used to rebuild the whole schema on a cold cache before the job
    // even existed - minutes of dead silence between clicking Save and the
    // first sign of life on a slow link (Matt, 2026-08-26). The job itself
    // re-reads schema WITH progress events; a name-only sort on a cold cache
    // is a cosmetic cost, not a correctness one.
    let dbOf = {};
    if (tablesCache?.tables) for (const t of tablesCache.tables) dbOf[t.name] = t.db || "";
    include.sort((a, b) =>
      String(dbOf[a] || "").localeCompare(String(dbOf[b] || "")) ||
      String(names[a] || a).localeCompare(String(names[b] || b)));
  }
  // Naming tables means "sync THESE", never "make the cube be only these".
  // This used to default to reconcile, so asking for one table dropped every
  // other table in the cube. Its sibling /api/cube/sync already got this
  // right; the two endpoints disagreed and only the narrow case showed it
  // (2026-08-24). The UI always sends the full list, which is why it hid.
  const explicit = Array.isArray(req.body?.tables) && req.body.tables.length > 0;
  // The Settings confirm dialog promises removals and sends reconcile:true;
  // it is the ONE caller allowed to say "and remove what I deselected".
  const reconcile = typeof req.body?.reconcile === "boolean" ? req.body.reconcile : !explicit;
  const job = startSyncJob(include, { reconcile });
  res.json({ id: job.id });
});

// Cancel stops NOW. The in-flight table is abandoned (its staged rows are
// discarded; the cube keeps whatever copy it had) and nothing further runs.
// Tables land whole or not at all either way.
app.post("/api/cube/sync/cancel", (_req, res) => {
  if (!syncJob || syncJob.done) return res.json({ idle: true });
  syncJob.cancelRequested = true;
  cancelWrites(); // a statement mid-flight is terminated, not waited out
  jobEmit(syncJob, { type: "cancelling" });
  res.json({ stopping: true });
});

app.get("/api/cube/sync/job", (_req, res) => {
  if (!syncJob) return res.json({ idle: true });
  res.json({ id: syncJob.id, running: !syncJob.done, startedAt: syncJob.startedAt, events: syncJob.events.length, origin: syncJob.origin || "user" });
});

// Watch the running (or just-finished) job. ?since=N replays the buffer from
// event N, so a reconnect never misses what happened while it was away.
app.get("/api/cube/sync/stream", async (req, res) => {
  res.set({ "Content-Type": "text/event-stream", "Cache-Control": "no-cache", Connection: "keep-alive", "X-Accel-Buffering": "no" });
  res.flushHeaders?.();
  const send = (evt) => { res.write(`data: ${JSON.stringify(evt)}\n\n`); };
  const job = syncJob;
  if (!job) { send({ type: "idle" }); return res.end(); }
  let i = Math.max(0, Number(req.query.since || 0));
  let closed = false;
  req.on("close", () => { closed = true; for (const w of job.waiters.splice(0)) w(); });
  while (!closed) {
    while (i < job.events.length) send(job.events[i++]);
    if (job.done) break;
    await new Promise((r) => job.waiters.push(r));
  }
  res.end();
});

// Ad-hoc SELECT (used by internal tools).
app.post("/api/query", async (req, res) => {
  try {
    const rows = await cubeSql(String(req.body?.sql || ""));
    res.json({ columns: rows.length ? Object.keys(rows[0]) : [], rows });
  } catch (e) { res.status(400).json({ error: String(e.message || e) }); }
});

// --- Config helpers ----------------------------------------------------------
// "copy": query the persisted DuckDB on the Fly volume (fast; as-of last sync).
// "live": re-pull from FileMaker (OData) into that same DuckDB just before the
// query, so numbers are current. Same engine (DuckDB local to Fly) either way;
// live just refreshes it first. Stored in config.json.
// Pythia is general-purpose: no hardcoded tables or names. The table selection
// and any friendly names are per-connection config, derived from the connected
// schema (Settings pre-checks the AI relevance-ranked tables). Empty until set.
function readConfig() { try { return JSON.parse(fs.readFileSync(CONFIG_PATH, "utf8")); } catch { return {}; } }
// Apply + persist a FileMaker connection from the UI. A blank password keeps
// the stored one. Any connection change invalidates the schema caches so the
// next look at the server is a real scan.
function saveFmConnection(fm) {
  connGen++;
  tablesBuild = null; // an in-flight build now belongs to a dead generation
  setConnection(fm);
  const config = readConfig();
  const prev = config.fm || {};
  config.fm = { host: fmConnection().host, db: fmConnection().db, user: fmConnection().user,
                pass: (typeof fm.pass === "string" && fm.pass) ? fm.pass : prev.pass };
  config.savedAt = new Date().toISOString();
  fs.writeFileSync(CONFIG_PATH, JSON.stringify(config, null, 2));
  schemaCache = null; tablesCache = null; tablesCacheAt = 0;
}
// After a reset, needsScan keeps the UI from scanning until the user asks.
// The first successful schema build lifts the gate.
function clearScanGate() {
  const c = readConfig();
  if (c.needsScan) { delete c.needsScan; fs.writeFileSync(CONFIG_PATH, JSON.stringify(c, null, 2)); }
}
function configuredTables() { const c = readConfig(); return c.includeTables && c.includeTables.length ? c.includeTables : []; }
// Friendly names: AI-proposed displayNames (from the cached relevance ranking)
// underneath, the user's own renames on top.
function getDisplayNames() {
  const proposed = {};
  try {
    for (const r of JSON.parse(fs.readFileSync(RELEVANCE_PATH, "utf8")).ranking || [])
      if (r.displayName && r.displayName !== r.name) proposed[r.name] = r.displayName;
  } catch { /* no ranking cached yet */ }
  // SaXML real names (ground truth) sit above the AI guess, below user renames.
  const hinted = {};
  for (const t of schemaCache?.tables || [])
    if (t.saxmlName && t.saxmlName !== t.name) hinted[t.name] = t.saxmlName;
  return { ...proposed, ...hinted, ...(readConfig().displayNames || {}) };
}

// Everything answers from the local copy; freshness comes from incremental
// sync (mod-date watermark), started by the user or a FileMaker script - never
// per query.
// Called at the top of the AI endpoints. It USED to run a full sync inline
// when no cube existed, which meant a chat question silently started minutes
// of work, blocked the HTTP response until it finished (60s+ with no output),
// and did it outside the job system - so no modal, no progress, no Cancel, and
// nothing stopping it from racing a sync the user started themselves.
//
// Syncing is a job the user starts and watches. A question asked before the
// first sync gets an answer that says so (needsSetupMsg), instantly.
async function ensureData() {
  return; // deliberately a no-op: see above
}

// Guard for the AI endpoints: a readable schema, or a friendly pointer.
// "No data yet" arrives two ways - an empty schema, or the read throwing
// because the DuckDB file was never created. Both mean the same thing to a
// user, and neither is an error worth showing them.
async function cubeSchemaOrNull() {
  try {
    const schema = await cubeSchemaText();
    return schema.trim() ? schema : null;
  } catch (e) {
    if (e?.noCube || /database does not exist|Cannot open database/i.test(String(e?.message || e))) return null;
    throw e;
  }
}
// Says what is true RIGHT NOW: connected with tables waiting is a different
// situation from nothing set up at all, and the user needs different words.
function needsSetupMsg() {
  // Read the SAVED state, not the in-memory schema cache: the cache is empty
  // for a moment after every restart, which made this tell a user who had
  // already scanned and picked tables to go scan their server.
  const picked = configuredTables().length;
  const scanned = (tablesCache?.tables || []).length;
  const syncing = Boolean(syncJob && !syncJob.done);
  if (!fmConfigured()) return "There's no data to report on yet. Open Settings (bottom left) and connect your FileMaker server first.";
  if (syncing) return `The first sync is running right now - open Settings & tables (bottom left) to watch it. Ask me again when it finishes and I'll have the data.`;
  if (picked) return `Nothing has been synced yet. ${picked} table${picked === 1 ? " is" : "s are"} already selected in Settings > Tables - click "Sync now" there and I can answer this.`;
  if (scanned) return `Your server is connected and ${scanned} tables are visible, but none are selected yet. Open Settings > Tables, tick the ones you want, and sync.`;
  return "There's no data to report on yet. Open Settings & tables (bottom left), scan your server, pick the tables you want, and sync.";
}

// --- Saved reports ----------------------------------------------------------

const REPORTS_PATH = path.join(DATA_DIR, "reports.json");
function loadReports() {
  try { return JSON.parse(fs.readFileSync(REPORTS_PATH, "utf8")); }
  catch { fs.writeFileSync(REPORTS_PATH, JSON.stringify(SEED_REPORTS, null, 2)); return SEED_REPORTS.slice(); }
}
function saveReports(list) { fs.writeFileSync(REPORTS_PATH, JSON.stringify(list, null, 2)); }
const slug = (s) => String(s).toLowerCase().replace(/[^a-z0-9]+/g, "-").replace(/^-|-$/g, "").slice(0, 48) || "report";

app.get("/api/reports", (_req, res) => res.json(loadReports()));

app.post("/api/reports", (req, res) => {
  const list = loadReports();
  const b = req.body || {};
  let id = b.id || slug(b.name || "report");
  while (list.some((r) => r.id === id)) id += "-2";
  const rep = { id, name: b.name || "Untitled", description: b.description || "", sql: b.sql || "", chart: b.chart || null, ...(Array.isArray(b.parts) && b.parts.length ? { parts: b.parts } : {}), updatedAt: new Date().toISOString() };
  list.push(rep); saveReports(list);
  res.json(rep);
});

// Save/overwrite one report.
app.put("/api/reports/:id", (req, res) => {
  const list = loadReports();
  const i = list.findIndex((r) => r.id === req.params.id);
  if (i < 0) return res.status(404).json({ error: "not found" });
  const b = req.body || {};
  list[i] = { ...list[i], name: b.name ?? list[i].name, description: b.description ?? list[i].description, sql: b.sql ?? list[i].sql, chart: b.chart ?? list[i].chart, updatedAt: new Date().toISOString() };
  saveReports(list);
  res.json(list[i]);
});

// Duplicate.
app.post("/api/reports/:id/duplicate", (req, res) => {
  const list = loadReports();
  const src = list.find((r) => r.id === req.params.id);
  if (!src) return res.status(404).json({ error: "not found" });
  let id = src.id + "-copy";
  while (list.some((r) => r.id === id)) id += "-2";
  const copy = { ...src, id, name: src.name + " (copy)", updatedAt: new Date().toISOString() };
  list.push(copy); saveReports(list);
  res.json(copy);
});

app.delete("/api/reports/:id", (req, res) => {
  const list = loadReports().filter((r) => r.id !== req.params.id);
  saveReports(list);
  res.json({ ok: true });
});

// Run a report against current data (honors the live/copy switch).
const renderedReports = new Map(); // id -> html, for the iframe/print view
function buildArtifact(report, columns, rows) {
  const chart = report.chart;
  const ROWS_TYPES = new Set(["gauge", "stat", "bullet", "range"]);
  const series = chart && chart.type !== "table" && !ROWS_TYPES.has(chart.type) && chart.x && chart.y && columns.includes(chart.x) && columns.includes(chart.y)
    ? rows.map((r) => ({ label: String(r[chart.x] ?? ""), value: Number(r[chart.y]) || 0 })) : null;
  // A chart with a series COLUMN compares groups: the rows pivot into one
  // named series per distinct value ("multi-line", "stacked-bar", "grouped-bar").
  let multi = null;
  // combo carries two measures per x: y as bars, y2 as the line.
  if (chart && chart.type === "combo" && chart.y2 && columns.includes(chart.y2) && series) {
    multi = { labels: series.map((p) => p.label),
              series: [ { name: chart.y, values: series.map((p) => p.value) },
                        { name: chart.y2, values: rows.map((r) => Number(r[chart.y2]) || 0) } ] };
  } else if (chart && chart.series && columns.includes(chart.series) && series) {
    const labels = [...new Set(rows.map((r) => String(r[chart.x] ?? "")))];
    const names = [...new Set(rows.map((r) => String(r[chart.series] ?? "")))].slice(0, 8);
    const cell = {};
    for (const r of rows) cell[`${r[chart.x]}\u0000${r[chart.series]}`] = Number(r[chart.y]) || 0;
    multi = { labels, series: names.map((n) => ({ name: n, values: labels.map((l) => cell[`${l}\u0000${n}`] ?? 0) })) };
  }
  return {
    multi,
    dataAsOf: cubeManifest()?.syncedAt || null,
    chart,
    title: report.name || "Report", chartType: series ? chart.type : "table",
    columns, rows: rows.slice(0, 300), series, saveable: false,
    sql: report.sql, chart: chart || null,
  };
}
async function runReport(report) {
  await ensureData();
  if (report.parts?.length) {
    // Multi-part: run every section fresh, render one document.
    const rendered = [];
    for (const p of report.parts) {
      try {
        const rows = await cubeSql(p.sql);
        rendered.push({ title: p.title, note: p.note || "", chart: p.chart, columns: rows.length ? Object.keys(rows[0]) : [], rows });
      } catch { /* section query failed against current data; skip it */ }
    }
    if (!rendered.length) throw new Error("None of this report's sections ran against the current data.");
    renderedReports.set(report.id, renderReportParts({ report, parts: rendered, meta: cubeManifest() }));
    const artifact = { title: report.name, chartType: "report", sections: rendered.map((p) => ({ title: p.title, rows: p.rows.length })), reportId: report.id, saveable: false };
    return { reportId: report.id, artifact, ts: new Date().toISOString() };
  }
  const rows = await cubeSql(report.sql);
  const columns = rows.length ? Object.keys(rows[0]) : [];
  renderedReports.set(report.id, renderReport({ report, columns, rows, meta: cubeManifest() }));
  return { columns, rows, reportId: report.id, artifact: buildArtifact(report, columns, rows), ts: new Date().toISOString() };
}

app.post("/api/reports/:id/run", async (req, res) => {
  try {
    const report = loadReports().find((r) => r.id === req.params.id);
    if (!report) return res.status(404).json({ error: "not found" });
    res.json(await runReport(report));
  } catch (e) {
    // A saved report run before the first sync is a setup state, not a failure.
    if (e?.noCube) return res.status(200).json({ error: needsSetupMsg(), needsSetup: true });
    res.status(400).json({ error: String(e.message || e) });
  }
});

// Run an unsaved draft (live editor preview).
app.post("/api/run", async (req, res) => {
  try {
    const b = req.body || {};
    res.json(await runReport({ id: "_draft", name: b.name || "Draft", description: b.description || "", sql: b.sql || "", chart: b.chart || null }));
  } catch (e) { res.status(400).json({ error: String(e.message || e) }); }
});

// Rendered HTML lives in memory, and Fly auto-stop wipes it between the run
// and the click ("Report not run yet." on a report that WAS just run - a real
// field report). Saved reports are deterministic SQL, so a miss just re-runs.
const reportRunsInFlight = new Map(); // id -> promise, so parallel opens run once
app.get("/report/:id", async (req, res) => {
  const id = req.params.id;
  let html = renderedReports.get(id);
  if (!html) {
    const report = loadReports().find((r) => r.id === id);
    if (!report) return res.status(404).send("No such report.");
    try {
      if (!reportRunsInFlight.has(id)) {
        reportRunsInFlight.set(id, runReport(report).finally(() => reportRunsInFlight.delete(id)));
      }
      await reportRunsInFlight.get(id);
      html = renderedReports.get(id);
    } catch (e) {
      return res.status(502).send("This report exists but did not run against the current data: " + String(e.message || e));
    }
  }
  res.type("html").send(html);
});

// --- Conversations: independent chat threads (master-detail) -----------------
// Each conversation is its own file with its own message history, so threads
// stay independent. The sidebar lists them; switching loads only that thread.
const CONVOS_DIR = path.join(DATA_DIR, "conversations");
fs.mkdirSync(CONVOS_DIR, { recursive: true });
let convoSeq = 0;
const newConvoId = () => `c_${Date.now().toString(36)}${(convoSeq++).toString(36)}`;
const convoPath = (id) => path.join(CONVOS_DIR, String(id).replace(/[^\w-]/g, "") + ".json");
function loadConvo(id) { try { return JSON.parse(fs.readFileSync(convoPath(id), "utf8")); } catch { return null; } }
function saveConvo(c) { fs.writeFileSync(convoPath(c.id), JSON.stringify(c)); }
function listConvos() {
  return fs.readdirSync(CONVOS_DIR).filter((f) => f.endsWith(".json"))
    .map((f) => { try { const c = JSON.parse(fs.readFileSync(path.join(CONVOS_DIR, f), "utf8")); return { id: c.id, title: c.title, updated: c.updated, created: c.created, running: Boolean(c.running) }; } catch { return null; } })
    .filter(Boolean).sort((a, b) => String(b.updated || "").localeCompare(String(a.updated || "")));
}

// Eager creation: the thread exists (and shows in the sidebar) the moment the
// user fires a question; the reply lands in it whenever it finishes.
app.post("/api/conversations", (req, res) => {
  const now = new Date().toISOString();
  const c = { id: newConvoId(), title: String(req.body?.title || "New chat").slice(0, 60), created: now, updated: now, running: true, messages: [] };
  saveConvo(c);
  res.json({ id: c.id, title: c.title });
});
app.get("/api/conversations", (_req, res) => res.json({ conversations: listConvos() }));
app.get("/api/conversations/:id", (req, res) => { const c = loadConvo(req.params.id); c ? res.json(c) : res.status(404).json({ error: "not found" }); });
app.patch("/api/conversations/:id", (req, res) => {
  const c = loadConvo(req.params.id); if (!c) return res.status(404).json({ error: "not found" });
  if (req.body?.title) { c.title = String(req.body.title).slice(0, 80); c.updated = new Date().toISOString(); saveConvo(c); }
  res.json({ ok: true });
});
app.delete("/api/conversations/:id", (req, res) => { try { fs.unlinkSync(convoPath(req.params.id)); } catch {} res.json({ ok: true }); });

// --- Chat: pure natural-language prompt -> query -> inline chart -------------
// The user only ever sees plain English + charts. SQL is written by the model
// and executed server-side against the local DuckDB copy; it is never shown.
// Two tools: query_data (SELECT) and present (turn the last result into a chart
// or table the UI renders). Numbers come straight from the query rows.

async function callAnthropicTools(system, messages, tools, maxTokens = 1500, ai) {
  const a = ai || aiSnapshot();
  return anthropicFetch({ model: a.model, max_tokens: maxTokens, system, tools, messages }, 60000, a);
}

async function cubeSchemaText() {
  const cols = await cubeSql("SELECT table_name, column_name FROM information_schema.columns WHERE NOT starts_with(table_name, '_pythia_') ORDER BY table_name, ordinal_position");
  const byTable = {};
  for (const c of cols) (byTable[c.table_name] ||= []).push(c.column_name);
  return Object.entries(byTable).map(([t, cs]) => `- "${t}"(${cs.map((c) => `"${c}"`).join(", ")})`).join("\n");
}

// --- Derived local tables + column stats: pre-chewed context for speed. -----
// Built after every sync. The date-spine table powers date math without the AI
// improvising; the stats block tells it what's IN the columns, so it writes
// correct SQL on the first try instead of exploring with extra round trips.
const STATS_PATH = path.join(DATA_DIR, "schema-stats.json");
function statsBlock() {
  try {
    const s = JSON.parse(fs.readFileSync(STATS_PATH, "utf8"));
    return s.text ? "COLUMN FACTS (real values from the local copy - trust these over guesses):\n" + s.text +
      "\n\nA helper table \"_pythia_calendar\"(date, year, quarter, month, month_name, year_month, week, day_name, is_weekend) exists for date joins, grouping, and gap-free time series.\n\n" : "";
  } catch { return ""; }
}
async function buildDerived(log = console.log) {
  try {
    await cubeSql(`CREATE OR REPLACE TABLE _pythia_calendar AS
      SELECT d::DATE AS date, EXTRACT(year FROM d)::INT AS year, EXTRACT(quarter FROM d)::INT AS quarter,
             EXTRACT(month FROM d)::INT AS month, strftime(d, '%B') AS month_name, strftime(d, '%Y-%m') AS year_month,
             EXTRACT(week FROM d)::INT AS week, strftime(d, '%A') AS day_name,
             (EXTRACT(dow FROM d) IN (0, 6)) AS is_weekend
      FROM generate_series(DATE '2015-01-01', DATE '2035-12-31', INTERVAL 1 DAY) t(d)`, { allowWrite: true });
    const cols = await cubeSql("SELECT table_name, column_name, data_type FROM information_schema.columns WHERE NOT starts_with(table_name, '_pythia_') ORDER BY table_name, ordinal_position");
    const lines = [];
    const byTable = {};
    for (const c of cols) (byTable[c.table_name] ||= []).push(c);

    // Column facts USED TO stop at the first 30 columns of the first 40 tables
    // and six facts each. A 500-column table is normal in FileMaker, so the
    // model was told about the first sixth of it and nothing else, and the
    // fields a person actually reports on sit at the far end (Matt, 2026-08-24).
    //
    // Every column now counts, and width costs passes, not scans: one pass
    // gathers how full every column is, a second gathers the range of every
    // date and the spread of every text column. Only the few columns that
    // prove interesting get a query of their own. A 500-column table costs
    // about the same as a 30-column one.
    const q = (s2) => String(s2).replace(/"/g, '""');
    // Aggregates go out in batches: one SELECT with 1,000 expressions is a
    // parser problem, not a speed win.
    const inBatches = async (table, exprs, size = 120) => {
      const out = {};
      for (let k = 0; k < exprs.length; k += size) {
        const batch = exprs.slice(k, k + size);
        try {
          const [row] = await cubeSql(`SELECT ${batch.map((e, n) => `${e.sql} AS m${n}`).join(", ")} FROM "${q(table)}"`);
          batch.forEach((e, n) => { out[e.key] = row?.[`m${n}`]; });
        } catch { /* a batch that will not parse costs its own facts, not the table's */ }
      }
      return out;
    };

    for (const [t, cs] of Object.entries(byTable)) {
      const facts = [];
      let filledCols = [];
      try {
        // Pass 1: how many rows actually carry a value, for every column.
        const filled = await inBatches(t, cs.map((c) => ({ key: c.column_name, sql: `count("${q(c.column_name)}")` })));
        filledCols = cs.filter((c) => Number(filled[c.column_name] || 0) > 0);
        const empty = cs.length - filledCols.length;
        // An empty column is a fact in itself: it stops the model reporting a
        // total that looks small when the truth is that nobody fills the field.
        if (empty) facts.push(`${empty} of ${cs.length} columns are empty in every row`);

        // Pass 2: the range of every date, and the spread of every text column.
        const dates = filledCols.filter((c) => /DATE|TIMESTAMP/i.test(c.data_type));
        const texts = filledCols.filter((c) => /VARCHAR/i.test(c.data_type));
        const spans = dates.length ? await inBatches(t, dates.flatMap((c) => [
          { key: c.column_name + "\u0000lo", sql: `min("${q(c.column_name)}")::VARCHAR` },
          { key: c.column_name + "\u0000hi", sql: `max("${q(c.column_name)}")::VARCHAR` },
        ])) : {};
        const spread = texts.length ? await inBatches(t, texts.map((c) =>
          ({ key: c.column_name, sql: `approx_count_distinct("${q(c.column_name)}")` }))) : {};

        for (const c of dates) {
          const lo = spans[c.column_name + "\u0000lo"], hi = spans[c.column_name + "\u0000hi"];
          if (lo) facts.push(`"${c.column_name}" spans ${String(lo).slice(0, 10)}..${String(hi).slice(0, 10)}`);
        }
        // Only a column with few enough distinct values to list is worth a
        // query of its own. Everything else has already told us what it is.
        const listable = texts.filter((c) => { const n = Number(spread[c.column_name] || 0); return n > 1 && n <= 12; });
        for (const c of listable.slice(0, 40)) {
          try {
            const tops = await cubeSql(`SELECT "${q(c.column_name)}" AS v, count(*) AS n FROM "${q(t)}" WHERE "${q(c.column_name)}" IS NOT NULL AND "${q(c.column_name)}" <> '' GROUP BY 1 ORDER BY n DESC LIMIT 6`);
            if (tops.length > 1) facts.push(`"${c.column_name}" values: ${tops.map((x) => x.v).join(" | ")}`);
          } catch { /* per-column detail is best effort */ }
        }
      } catch { /* per-table stats are best effort */ }
      if (facts.length) lines.push(`- "${t}" (${filledCols.length} of ${cs.length} columns carry data): ${facts.join("; ")}`);
    }
    fs.writeFileSync(STATS_PATH, JSON.stringify({ text: lines.join("\n"), builtAt: new Date().toISOString() }, null, 2));
    log(`[derived] date spine + stats for ${lines.length} tables`);
  } catch (e) { console.error("derived build failed:", e?.message || e); }
}

// Generic guidance for the model. Pythia is general-purpose: it learns the
// actual schema at runtime (cubeSchemaText); nothing domain-specific lives here.
const DATA_NOTES = [
  "Working with this data (DuckDB SQL):",
  "- Use ONLY the tables and columns listed above; never assume others exist.",
  '- Quote identifiers containing spaces or punctuation in double quotes, e.g. "Order Date".',
  "- Dates/timestamps: use year(col), month(col), quarter(col), or strftime(col,'%Y-%m') to group.",
  // This bullet used to name ONLY FileMaker's US format while saying "detect
  // this". A model obeys the format it is given: against ISO text it parsed
  // nothing and reported the dates unreadable, on data measured 99.999%
  // castable (Matt's charity file, 2026-08-26). LOOK before parsing.
  "- Some date/timestamp columns are stored as TEXT, and the format VARIES by file. Never assume one. SELECT a non-null sample of the column FIRST, look at it, then parse to match: ISO ('2024-10-14', '2024-10-14T00:00:00.000') casts directly with try_cast(col AS TIMESTAMP); FileMaker's US format ('07/23/2026', '07/23/2026 14:05:00') needs strptime(col,'%m/%d/%Y') or strptime(col,'%m/%d/%Y %H:%M:%S'). Wrap in try_cast/CASE to survive blanks. If a parse yields no rows, the FORMAT GUESS was wrong, not the data - look at a sample and try the other one before telling the user anything is unreadable.",
  "- Numbers stored as TEXT vary the same way ('6210' vs '1.0000'), so CAST BOTH SIDES to DOUBLE before comparing or joining on them; a text comparison of those two silently matches nothing.",
  "- Join tables on their keys (typically a primary-key column and a matching foreign-key column that references the other table).",
  "- For time-series, label each period readably (e.g. '2024-Q1' or '2024-03'), never a bare number.",
  "- When the user asks about a trend and the data covers it, offer the same period one year earlier as a second series and present multi-line - a trend means little without something to compare it to.",
  "- Never show machine UUIDs as columns. Friendly identifiers people say aloud (invoice number, PO number) are welcome.",
  "- List-view discipline: for entity lists show the spoken identifier, the party, the date, the amount, and at most ONE descriptive field - unless the user asks for more detail.",
  "- Reason only from the actual schema and data shown. Do not assume domain facts that aren't visible in the columns.",
  "",
  "DURABLE, FAST REPORTS (a saved report re-runs later against MORE and NEWER data):",
  "- Cover ALL the data, every period. NEVER hard-code a year or date literal (no `year(col) = 2024`, no `BETWEEN 2024 AND 2026`, no fixed month). GROUP BY the period so every year/month present appears automatically, and the report stays correct when new periods are synced.",
  "- For 'recent' / 'last N months' windows, anchor to the DATA's own latest date, not a hard-coded year and not the wall clock: e.g. `WHERE d >= (SELECT max(d) FROM ...) - INTERVAL 12 MONTH`. This keeps the window meaningful as data grows.",
  "- Prefer set-based JOIN + GROUP BY over a per-row correlated subquery. A subquery like `(SELECT sum(x) FROM child WHERE child.fk = parent.id)` in the SELECT list runs once per row and gets slow on large tables; rewrite it as `JOIN child ... GROUP BY` or a CTE that pre-aggregates the child once. Reports must stay fast as the data grows.",
  "",
  "PREDICTIVE ANALYSIS (forecasts, projections, 'where is this heading', completion estimates):",
  "- Forecasts are COMPUTED IN SQL from history, never guessed. Build a periodized history (weekly/monthly aggregates), then:",
  "  * linear trend: regr_slope(y, x) and regr_intercept(y, x) with x as epoch(period_start); project future periods by evaluating slope*x+intercept over generate_series of upcoming dates;",
  "  * momentum: moving averages via window functions (AVG(y) OVER (ORDER BY period ROWS BETWEEN 3 PRECEDING AND CURRENT ROW));",
  "  * seasonality: month-of-year averages from prior years when 2+ years of history exist;",
  "  * run-rate/completion: for jobs and time tracking, compare actual hours/cost accumulated so far against the estimate or against the historical pace of similar completed items, and project the finish (e.g. remaining = estimate - actual, ETA = remaining / recent weekly pace).",
  "- Always chart history and projection together (line chart), with projected periods clearly labeled (e.g. '2026-08 (projected)').",
  "- State the basis in one sentence: how much history, which method, and the biggest caveat. Low history (<6 periods) = say the forecast is rough.",
  "- Never present a projection as fact; it is an extrapolation of the real numbers shown.",
].join("\n");

const CHAT_TOOLS = [
  { name: "query_data", description: "Run one read-only DuckDB SQL SELECT against the local copy of the FileMaker data. Quote identifiers that contain spaces or start oddly with double quotes. Returns rows as JSON plus a queryIndex you can reference in compose_report.",
    input_schema: { type: "object", properties: { sql: { type: "string" } }, required: ["sql"] } },
  { name: "present", description: "Display a query_data result to the user as a chart or table. Pass queryIndex to say WHICH query this shows; without it you get the most recent one, which is wrong whenever you present more than one result in a reply. DEFAULT SHAPES - bar: compare categories; line/area: one trend; multi-line: compare trends across groups (series column); stacked-bar: composition over time; grouped-bar: side-by-side per period; stacked-area: filled composition trend; pct-stacked-bar: share-of-total per period; combo: a measure as bars PLUS a second measure as a line on its own right scale (pass y and y2; SQL returns x,y,y2); donut: share of a whole (max 6); scatter: two numeric columns; histogram: distribution (SQL returns bucket,count); pareto: sorted contribution + cumulative % (x,y); lollipop: cleaner than bars past ~15 categories; waterfall: signed changes in order, start to end (x,y); range: a start-to-end span per row (x=label, y=start, y2=end - project or harvest windows); table: detail rows. DASHBOARD SHAPES, use ONLY when the user asks for a dashboard, target, or single-number view - gauge (SQL returns one row: value[,target][,max]), bullet (x=label,y=value,y2=target), stat (one row: value[,prior value]). heatmap: x, series, y as a colored grid (weekday x hour, month x category). For any group shape pass series: the column naming the group; SQL returns one row per x+series pair.",
    input_schema: { type: "object", properties: { title: { type: "string" }, queryIndex: { type: "number", description: "queryIndex returned by the query_data call whose rows this shows. REQUIRED when the reply presents more than one result." }, chartType: { type: "string", enum: ["table", "bar", "line", "area", "donut", "scatter", "multi-line", "stacked-bar", "grouped-bar", "combo", "stacked-area", "pct-stacked-bar", "waterfall", "histogram", "heatmap", "pareto", "bullet", "lollipop", "range", "gauge", "stat"] }, x: { type: "string" }, y: { type: "string" }, y2: { type: "string" }, series: { type: "string" } }, required: ["title", "chartType"] } },
  { name: "compose_report", description: "Bundle SEVERAL query results from this reply into ONE saved, printable report with multiple sections. Use when the user wants a single report with multiple parts. Run query_data (and present) for each section first, then call this once at the end. Each section references a query by its queryIndex.",
    input_schema: { type: "object", properties: {
      name: { type: "string" }, description: { type: "string" },
      sections: { type: "array", items: { type: "object", properties: {
        title: { type: "string" },
        queryIndex: { type: "number", description: "queryIndex returned by the query_data call whose rows this section shows" },
        chartType: { type: "string", enum: ["table", "bar", "line", "area", "donut", "scatter", "multi-line", "stacked-bar", "grouped-bar", "combo", "stacked-area", "pct-stacked-bar", "waterfall", "histogram", "heatmap", "pareto", "bullet", "lollipop", "range", "gauge", "stat"] },
        x: { type: "string" }, y: { type: "string" }, y2: { type: "string" }, series: { type: "string" },
        note: { type: "string", description: "One plain-English sentence of takeaway for this section" },
      }, required: ["title", "queryIndex", "chartType"] } },
    }, required: ["name", "sections"] } },
];

// What a failed turn should SAY in the transcript. A setup state reads as
// guidance; anything else keeps its message but never pretends to be an answer.
function chatErrorText(e) {
  if (e?.noCube || /database does not exist|Cannot open database/i.test(String(e?.message || e))) return needsSetupMsg();
  return "That question hit an error: " + String(e?.message || e);
}

app.post("/api/chat", async (req, res) => {
  try {
    if (!aiKey()) return res.json({ reply: "I can't answer questions until an AI key is set. Saved reports still work without one.", artifacts: [], needsKey: true });
    await ensureData();
    const schema = await cubeSchemaOrNull();
    if (!schema) return res.json({ reply: needsSetupMsg(), artifacts: [] });
    const dn = getDisplayNames();
    const aliasLine = "Users refer to tables by these friendly names (use them when talking to the user; still write SQL against the REAL names on the left): " +
      Object.entries(dn).map(([real, friendly]) => `${real}="${friendly}"`).join(", ") + ".";
    // Tables the user CAN see in FileMaker but hasn't loaded into the local copy.
    // If they ask about one, name it and point to Settings — don't pretend it's absent.
    const syncedSet = new Set((await cubeSql("SELECT table_name FROM information_schema.tables").catch(() => [])).map((r) => r.table_name));
    const unsynced = (tablesCache?.tables || [])
      .filter((t) => !syncedSet.has(t.name) && !String(t.name).startsWith("_pythia_"))
      .map((t) => dn[t.name] || t.name);
    const unsyncedLine = unsynced.length
      ? "TABLES THAT EXIST IN FILEMAKER BUT AREN'T LOADED YET: " + unsynced.slice(0, 40).join(", ") + ". If the user asks about one of these, tell them it's available but not yet synced — they can add it in Settings > Tables and sync — do NOT say the database can't do it.\n\n"
      : "";
    const system =
      "You are Pythia, a reporting oracle over the user's FileMaker data. Answer their questions in plain, concise English backed by real numbers. " +
      "You work against a local SQL copy of these tables (DuckDB):\n" + schema + "\n\n" + unsyncedLine + contextBlock() + instructionsBlock() + statsBlock() + DATA_NOTES + "\n" + aliasLine + "\n\n" +
      // A small local model reads "quote identifiers with spaces" as advice and
      // writes i.Paid Amount anyway, burning a whole round trip on a parser
      // error before it self-corrects (Matt, 2026-08-26, qwen3.5:9b). Naming
      // the exact wrong output is what makes it stick.
      'Rules: (1) To get numbers, call query_data with a DuckDB SELECT. PUT EVERY TABLE AND COLUMN NAME IN DOUBLE QUOTES, always, even when it contains no space. ' +
      'Correct: SELECT SUM(i."Paid Amount") FROM "Invoice" i JOIN "Product" p ON i."ID_General Product" = p."ID". ' +
      'Wrong: SELECT SUM(i.Paid Amount) FROM Invoice i — that is a parser error and it costs a wasted round trip. ' +
      "(2) After querying, call present to show the result as a chart (bar for rankings/comparisons, line for time-over-time) or table (for detailed row lists). x/y are column names from the result. " +
      // A small model treats this as satisfied by "Here's what I found." The
      // number lands in the chart artifact and never in the sentence, so the
      // written answer says nothing (Matt, 2026-08-26, qwen3.5:9b).
      "(3) Then give a 1-2 sentence plain-English takeaway the user can act on. THE ANSWER ITSELF MUST BE IN YOUR WORDS: name the winning row and state its number in the sentence, e.g. \"Dessie Diekrager leads at $830,893.\" Never end with a bare phrase like \"Here's what I found\" or \"See the chart above\" — the chart is a supplement to your answer, not the answer. " +
      "If the user asks for ONE report with MULTIPLE parts/sections, run query_data (and present) for each part, then call compose_report ONCE at the end to bundle them into a single saved, printable report. " +
      "HONESTY — refuse clearly rather than fabricate. If a question can't be answered from this data, say so plainly and say WHY: (a) the data simply isn't tracked here — name the closest thing you CAN show; (b) the table exists but isn't loaded yet (see the list above) — tell them to sync it in Settings; (c) the rank/join/connection they want isn't possible because the linking key or column doesn't exist — say which piece is missing. Never invent a number, a join, or a column to force an answer. A clear 'I can't do that, here's why' beats a misleading chart. " +
      "PRESENTATION RULES (always): never show SQL, table names, or column names. NEVER show a UUID, internal ID, or raw foreign key in your answer or any table/chart – always resolve keys to the human label (join to the related table and show its name/title column), and never include a bare ID column. Label rows by their name, not their key. Use friendly names and plain, warm, business English.";

    const messages = (req.body?.messages || []).map((m) => ({ role: m.role, content: String(m.content) }));
    const artifacts = [];
    const queryLog = []; // every query_data this reply: {sql, columns, rows} – compose_report references these
    let lastRows = [], lastSql = "", replyText = "";
    let truncated = false, cleanFinish = false; // why the loop ended (never fail silently)
    const MODEL_ROW_CAP = 60;      // rows any tool may hand back to the model
    const PROMPT_TOKEN_CEILING = 700000; // backstop against absurd payloads; real overflows still get providerError's friendly message
    const MAX_HOPS = 14; // headroom for multi-section reports; simple questions still finish in 2-3
    const ai = aiSnapshot(); // one model/speed/thinking snapshot for the whole turn

    for (let hop = 0; hop < MAX_HOPS; hop++) {
      // Roughly 4 chars per token; the exact count is not worth a round trip.
      // What matters is refusing to send 50MB and getting a raw 400 back.
      const approxTokens = JSON.stringify(messages).length / 4;
      if (approxTokens > PROMPT_TOKEN_CEILING) {
        replyText = "That question pulled in more data than fits in one request. Ask for a narrower slice - a shorter date range, fewer tables, or one section at a time.";
        truncated = true; break;
      }
      const resp = await callAnthropicTools(system, messages, CHAT_TOOLS, 4000, ai);
      if (resp.stop_reason === "max_tokens") truncated = true; // response was cut off, not finished
      messages.push({ role: "assistant", content: resp.content });
      const toolUses = resp.content.filter((b) => b.type === "tool_use");
      replyText = resp.content.filter((b) => b.type === "text").map((b) => b.text).join(" ").trim() || replyText;
      if (!toolUses.length) { cleanFinish = true; break; } // model is done on its own terms
      const results = [];
      for (const tu of toolUses) {
        let out;
        try {
          if (tu.name === "query_data") {
            lastSql = tu.input.sql; lastRows = await cubeSql(lastSql);
            queryLog.push({ sql: lastSql, columns: lastRows[0] ? Object.keys(lastRows[0]) : [], rows: lastRows });
            out = { queryIndex: queryLog.length, rowCount: lastRows.length, columns: lastRows[0] ? Object.keys(lastRows[0]) : [], rows: lastRows.slice(0, MODEL_ROW_CAP) };
          } else if (tu.name === "compose_report") {
            const secs = Array.isArray(tu.input.sections) ? tu.input.sections : [];
            const parts = [], rendered = [], badIdx = [];
            for (const s of secs) {
              // 1-based, like present. A 0 or out-of-range index must fail
              // loudly: silently skipping a section bound every later card
              // to the wrong query with no visible error.
              const qi = Number(s.queryIndex);
              const q = Number.isInteger(qi) && qi >= 1 && qi <= queryLog.length ? queryLog[qi - 1] : null;
              if (!q) { badIdx.push(s.queryIndex); continue; }
              if (!q.rows.length) continue;
              const chart = s.chartType !== "table" && s.x && s.y && q.columns.includes(s.x) && q.columns.includes(s.y)
                ? { type: s.chartType, x: s.x, y: s.y, y2: s.y2 || null, series: s.series || null } : null;
              parts.push({ title: s.title || "", note: s.note || "", sql: q.sql, chart });
              rendered.push({ title: s.title || "", note: s.note || "", chart, columns: q.columns, rows: q.rows });
            }
            if (badIdx.length) { out = { error: `queryIndex must be 1-based and at most ${queryLog.length}; got: ${badIdx.join(", ")}. Nothing was saved - fix the indexes and call compose_report again.` }; }
            else if (!parts.length) { out = { error: "No valid sections; check queryIndex values." }; }
            else {
              const list = loadReports();
              let id = slug(tu.input.name || "report"); while (list.some((r) => r.id === id)) id += "-2";
              const rep = { id, name: tu.input.name || "Report", description: tu.input.description || "", parts, updatedAt: new Date().toISOString() };
              list.push(rep); saveReports(list);
              renderedReports.set(id, renderReportParts({ report: rep, parts: rendered, meta: cubeManifest() }));
              artifacts.push({ title: rep.name, chartType: "report", sections: rendered.map((p) => ({ title: p.title, rows: p.rows.length })), reportId: id, saveable: false });
              out = { saved: true, reportId: id, sections: parts.length };
            }
          } else if (tu.name === "present") {
            // Bind this card to the query it is FOR, not to whatever ran last.
            // Presenting several results in one reply used to hand every card
            // the most recent rows: four differently-titled blocks, one query
            // behind all of them, and it looked correct (Matt, 2026-08-25).
            const qi = Number(tu.input.queryIndex);
            const q = Number.isFinite(qi) && qi >= 1 && qi <= queryLog.length ? queryLog[qi - 1] : null;
            const useSql = q ? q.sql : lastSql;
            const useRows = q ? q.rows : lastRows;
            const columns = q ? q.columns : (lastRows[0] ? Object.keys(lastRows[0]) : []);
            const rep = { name: tu.input.title, sql: useSql, chart: tu.input.chartType === "table" ? null : { type: tu.input.chartType, x: tu.input.x, y: tu.input.y, y2: tu.input.y2 || null, series: tu.input.series || null } };
            const art = buildArtifact(rep, columns, useRows); art.saveable = true;
            // Store a printable HTML render so the user can Save as PDF from the card.
            const rid = "chat-" + tu.id.slice(-10);
            renderedReports.set(rid, renderReport({ report: rep, columns, rows: useRows, meta: cubeManifest() }));
            art.reportId = rid;
            artifacts.push(art);
            out = { shown: true, rows: useRows.length, queryIndex: q ? qi : queryLog.length };
          } else out = { error: "unknown tool" };
        } catch (e) { out = { error: String(e.message || e).slice(0, 300) }; }
        results.push({ type: "tool_result", tool_use_id: tu.id, content: JSON.stringify(out) });
      }
      messages.push({ role: "user", content: results });
    }

    // Never fail silently: a prompt that produced nothing must say WHY and what
    // to do next — running out of steps, getting cut off, or not fitting the data.
    let reply;
    if (replyText) reply = replyText;
    else if (artifacts.length) reply = "Here's what I found.";
    else if (!cleanFinish) reply = "That request needed more steps than I can finish in one pass" + (truncated ? ", and my responses were getting cut off" : "") + ". It's a large ask — split it into separate questions (one section, or a few charts, at a time) and I'll nail each one.";
    else if (truncated) reply = "My answer got cut off before I could finish that one. Ask for a smaller piece of it and I'll complete it.";
    else reply = "I couldn't turn that into a report against this data. Tell me which table and which columns you mean, or rephrase it, and I'll try again.";
    // Partial success: real output exists, but we ran out of room mid-way. Say so.
    if (!cleanFinish && (replyText || artifacts.length)) reply += "\n\n(Heads up: this got large and I may not have finished every section. Ask me to continue, or split it up.)";
    const ts = new Date().toISOString();
    // Persist to the conversation thread (create if new). Keep it independent:
    // we store only this thread's plain user/assistant turns, with source+ts.
    const incoming = req.body?.messages || [];
    const lastUser = [...incoming].reverse().find((m) => m.role === "user");
    let convo = req.body?.conversationId ? loadConvo(req.body.conversationId) : null;
    if (!convo) convo = { id: newConvoId(), title: String(lastUser?.content || "New chat").slice(0, 60), created: ts, messages: [] };
    if (lastUser) convo.messages.push({ role: "user", content: String(lastUser.content), ts });
    convo.messages.push({ role: "assistant", content: reply, artifacts, ts });
    convo.updated = ts;
    convo.running = false; // reply landed; the sidebar spinner can stop
    saveConvo(convo);

    res.json({ reply, artifacts, ts, conversationId: convo.id });
  } catch (e) {
    // Land the failure IN the thread too: an eagerly-created conversation must
    // never spin forever because its request died.
    try {
      const convo = req.body?.conversationId ? loadConvo(req.body.conversationId) : null;
      if (convo) {
        const lastUser = [...(req.body?.messages || [])].reverse().find((m) => m.role === "user");
        if (lastUser && !convo.messages.some((m) => m.role === "user" && m.content === String(lastUser.content))) {
          convo.messages.push({ role: "user", content: String(lastUser.content), ts: new Date().toISOString() });
        }
        convo.messages.push({ role: "assistant", content: chatErrorText(e), ts: new Date().toISOString() });
        convo.updated = new Date().toISOString(); convo.running = false;
        saveConvo(convo);
      }
    } catch { /* best effort */ }
    const noCube = e?.noCube || /database does not exist|Cannot open database/i.test(String(e?.message || e));
    if (noCube) return res.status(200).json({ reply: needsSetupMsg(), artifacts: [] });
    res.status(200).json({ reply: "", error: String(e.message || e) });
  }
});

// --- Overview: an AI-designed landing dashboard ------------------------------
// General-purpose: the model reads the connected schema and proposes a dashboard
// spec (KPIs + charts, each backed by one SQL). The server validates every query
// by running it, drops failures, and caches the spec per schema – instant after
// the first build, regenerated when the synced schema changes.
const OVERVIEW_PATH = path.join(DATA_DIR, "overview.json");

async function overviewSpec(schema) {
  try {
    const cached = JSON.parse(fs.readFileSync(OVERVIEW_PATH, "utf8"));
    // A customized spec is OWNED by the user: it survives schema changes (broken
    // pieces just drop at run time). Only AI-generated specs regenerate on drift.
    if (cached.spec && (cached.custom || cached.schema === schema)) return cached.spec;
  } catch { /* no cache yet */ }
  if (!aiKey()) throw new Error("No AI key set, so the overview can't be designed yet.");
  const dn = getDisplayNames();
  const system =
    "You design a landing dashboard for a database. Given the DuckDB tables below, propose the overview its owner would want to see first: " +
    "3-5 KPIs (single headline numbers) and 2-4 charts, each backed by ONE DuckDB SELECT against the real table names, on ONE line. " +
    "CATEGORY CHARTS: exclude NULL/empty group values with a WHERE clause. If the stats show most rows have no value in a category column, chart a different dimension instead - a chart where one '(none)' bucket dwarfs every real category tells the reader nothing. " +
    'Return ONLY JSON, no markdown: {"kpis":[{"label":string,"sql":string,"format":"number"|"money"|"percent","trendSql":string}],"charts":[{"title":string,"type":"bar"|"line","sql":string,"x":string,"y":string}]}. ' +
    "Each KPI SQL returns exactly one row with one column. trendSql is optional: when the KPI has a natural time dimension, return the same measure per month for the last 12 months, two columns (period, value), ordered; it powers a sparkline and a vs-prior-month delta. Omit trendSql for KPIs with no time dimension. " +
    "Each chart SQL returns the columns named in x and y (at most 12 rows, with ORDER BY; readable period labels like '2024-Q1' for time series). " +
    "Quote identifiers with spaces. Only use tables/columns that exist below. Pick 'money' format only for columns that are clearly monetary. " +
    "PRESENTATION RULE: never select a UUID, internal ID, or foreign key as a shown column – identify rows by their human-readable label.\n\n" +
    contextBlock() + instructionsBlock() + statsBlock() + "Tables:\n" + schema + "\n\n" + DATA_NOTES +
    (Object.keys(dn).length ? "\nFriendly names (for labels/titles only; SQL uses real names): " + Object.entries(dn).map(([r, f]) => `${r}=${f}`).join(", ") : "");
  // Thinking off for structured design work (same reason as reports/generate:
  // adaptive thinking can eat the budget or interleave prose that breaks the
  // JSON slice). Parse failures get ONE corrective retry, then an honest error
  // - a raw "Expected ',' or ']' at position N" must never reach the screen.
  const parseSpec = (txt) => JSON.parse(txt.slice(txt.indexOf("{"), txt.lastIndexOf("}") + 1));
  let spec;
  try {
    spec = parseSpec(await callAnthropic(system, "Design the dashboard now. JSON only.", 5000, { ...aiSnapshot(), thinking: "off" }));
  } catch {
    try {
      spec = parseSpec(await callAnthropic(system, "Design the dashboard now. Output STRICTLY one valid JSON object and nothing else - no markdown, no comments, every SQL on one line inside its string.", 5000, { ...aiSnapshot(), thinking: "off" }));
    } catch { throw new Error("The dashboard design came back unreadable twice. Try asking a question instead, or pick a different model in Settings."); }
  }
  try { fs.writeFileSync(OVERVIEW_PATH, JSON.stringify({ schema, spec, savedAt: new Date().toISOString() }, null, 2)); } catch { /* read-only fs ok */ }
  return spec;
}

// Run a dashboard spec: execute every KPI and chart query, keep what works,
// report what didn't. Trust-but-verify: nothing lands on the dashboard unless
// its SQL actually ran.
async function runOverviewSpec(spec) {
  const kpis = [], charts = [], dropped = [];
  for (const k of spec.kpis || []) {
    try {
      const row = (await cubeSql(k.sql))[0] || {};
      const value = Object.values(row)[0];
      if (value === undefined || value === null) { dropped.push(k.label); continue; }
      const kpi = { label: k.label, value, format: k.format || "number" };
      // Optional trend: 12 monthly points -> sparkline + delta vs prior month.
      // A KPI that fails its trend still shows its number; trends never kill KPIs.
      if (k.trendSql) {
        try {
          const tr = await cubeSql(k.trendSql);
          const vals = tr.map((r) => Number(Object.values(r)[1])).filter(Number.isFinite);
          if (vals.length >= 3) {
            kpi.trend = vals.slice(-12);
            const [prev, cur] = vals.slice(-2);
            if (prev) kpi.deltaPct = Math.round(((cur - prev) / Math.abs(prev)) * 100);
          }
        } catch { /* number stands alone */ }
      }
      kpis.push(kpi);
    } catch { dropped.push(k.label); }
  }
  for (const c of spec.charts || []) {
    try {
      const rows = await cubeSql(c.sql);
      if (rows.length && rows[0][c.x] !== undefined && rows[0][c.y] !== undefined)
        charts.push({ title: c.title, type: c.type === "line" ? "line" : "bar", x: c.x, y: c.y, rows: rows.slice(0, 24) });
      else dropped.push(c.title);
    } catch { dropped.push(c.title); }
  }
  return { kpis, charts, dropped };
}

app.get("/api/overview", async (req, res) => {
  try {
    await ensureData();
    if (req.query.refresh) { try { fs.unlinkSync(OVERVIEW_PATH); } catch {} }
    const schema = await cubeSchemaOrNull();
    if (!schema) return res.status(200).json({ error: needsSetupMsg(), needsSetup: true });
    const spec = await overviewSpec(schema);
    const { kpis, charts } = await runOverviewSpec(spec);
    if (!kpis.length && !charts.length) {
      try { fs.unlinkSync(OVERVIEW_PATH); } catch {} // bad spec: don't keep it cached
      throw new Error("The overview design didn't run cleanly. Reload to try again.");
    }
    res.json({ kpis, charts, syncedAt: cubeManifest().syncedAt });
  } catch (e) { res.status(502).json({ error: String(e.message || e) }); }
});

// Customize the dashboard by talking: the model revises the current spec per
// the instruction, every query is validated by running it, and the result is
// saved as a user-owned spec (custom:true) that schema drift can't replace.
app.post("/api/overview/customize", async (req, res) => {
  try {
    const instruction = String(req.body?.instruction || "").trim();
    if (!instruction) return res.status(400).json({ error: "Say what should change." });
    if (!aiKey()) return res.status(400).json({ error: "No AI key set." });
    await ensureData();
    const schema = await cubeSchemaOrNull();
    if (!schema) return res.status(200).json({ error: needsSetupMsg(), needsSetup: true });
    const current = await overviewSpec(schema);
    const system =
      "You revise a dashboard spec for a database. Apply the user's request to the CURRENT SPEC below: add, remove, or change KPIs and charts as asked, leaving everything else untouched. " +
      'Return ONLY the complete revised JSON spec, no markdown: {"kpis":[{"label":string,"sql":string,"format":"number"|"money"|"percent"}],"charts":[{"title":string,"type":"bar"|"line","sql":string,"x":string,"y":string}]}. ' +
      "Each KPI SQL returns exactly one row with one column. Each chart SQL returns the columns named in x and y (at most 12 rows, ORDER BY, readable period labels). " +
      "Quote identifiers with spaces. Only use tables/columns that exist. Never select a UUID or internal key as a shown column.\n\n" +
      contextBlock() + instructionsBlock() + statsBlock() + "CURRENT SPEC:\n" + JSON.stringify(current) + "\n\nTables:\n" + schema + "\n\n" + DATA_NOTES;
    const txt = await callAnthropic(system, instruction, 3000, { ...aiSnapshot(), thinking: "off" });
    const spec = JSON.parse(txt.slice(txt.indexOf("{"), txt.lastIndexOf("}") + 1));
    const { kpis, charts, dropped } = await runOverviewSpec(spec);
    if (!kpis.length && !charts.length) return res.status(502).json({ error: "That change didn't produce a working dashboard, so nothing was saved. Try wording it differently." });
    // Keep only the pieces that ran; the saved spec never contains broken SQL.
    const cleanSpec = {
      kpis: (spec.kpis || []).filter((k) => kpis.some((x) => x.label === k.label)),
      charts: (spec.charts || []).filter((c) => charts.some((x) => x.title === c.title)),
    };
    fs.writeFileSync(OVERVIEW_PATH, JSON.stringify({ schema, spec: cleanSpec, custom: true, savedAt: new Date().toISOString() }, null, 2));
    res.json({ ok: true, kpis, charts, dropped });
  } catch (e) { res.status(502).json({ error: String(e.message || e) }); }
});

// AI "what stands out" – separate so the dashboard renders instantly and the
// insights fill in when the model returns.
app.post("/api/overview/insights", async (req, res) => {
  if (!aiKey()) return res.json({ insights: [] });
  try {
    const txt = await callAnthropic(
      "You are a sharp data analyst. Given these dashboard figures, write 3 short, specific, punchy insights the data's owner would act on. One sentence each, no preamble. Return ONLY a JSON array of strings.",
      JSON.stringify(req.body || {}), 700);
    res.json({ insights: JSON.parse(txt.slice(txt.indexOf("["), txt.lastIndexOf("]") + 1)) });
  } catch { res.json({ insights: [] }); }
});

// Suggested next questions – fuel the manager's drill-down. Loaded async after
// an answer so it never slows the reply.
app.post("/api/followups", async (req, res) => {
  if (!aiKey()) return res.json({ followups: [] });
  try {
    const txt = await callAnthropic(
      "You suggest what the user would naturally ask NEXT about their data. Given their question and the answer, propose 3 short natural-language follow-up questions (max 7 words each) that drill deeper or pivot usefully, answerable from the same data. Return ONLY a JSON array of 3 strings.",
      JSON.stringify({ question: String(req.body?.question || ""), answer: String(req.body?.reply || "") }), 300);
    // Coerce to plain strings: newer models sometimes wrap each suggestion in
    // an object ({question:"…"}), which reached the UI as "[object Object]".
    const arr = JSON.parse(txt.slice(txt.indexOf("["), txt.lastIndexOf("]") + 1));
    const strings = arr.map((x) => typeof x === "string" ? x : (x && typeof x === "object" ? (x.question || x.q || x.text || x.followup || Object.values(x).find((v) => typeof v === "string") || "") : String(x)))
      .map((s) => String(s).trim()).filter(Boolean).slice(0, 3);
    res.json({ followups: strings });
  } catch { res.json({ followups: [] }); }
});

// Turn a raw error into a plain-English diagnosis. Knowledge base first
// (instant, free); an unrecognized error spends one small AI call, with the
// knowledge base as context so the model diagnoses the way we would.
// Fixes we have learned, keyed by error signature. A remedy that worked once
// should not need the AI a second time - and in 2.0 this file is what gets
// reported back centrally, so a fix found on one server helps every install.
const LEARNED_PATH = path.join(DATA_DIR, "learned-fixes.json");
const errSignature = (e) => String(e || "").toLowerCase()
  .replace(/'[^']*'/g, "'X'").replace(/"[^"]*"/g, '"X"')     // table/field names vary
  .replace(/\d+/g, "N").replace(/\s+/g, " ").trim().slice(0, 160);
function loadLearned() { try { return JSON.parse(fs.readFileSync(LEARNED_PATH, "utf8")); } catch { return {}; } }
function rememberFix(error, entry) {
  try {
    const all = loadLearned();
    all[errSignature(error)] = { ...entry, at: new Date().toISOString(), seen: (all[errSignature(error)]?.seen || 0) + 1 };
    fs.writeFileSync(LEARNED_PATH, JSON.stringify(all, null, 2));
  } catch { /* read-only fs is fine */ }
}
app.get("/api/learned", (_req, res) => res.json(loadLearned()));

// What this server IS. Answers "is this machine big enough" before it kills a
// sync, rather than after. Deliberately holds NO infrastructure credential:
// the Fly token belongs to the deploy tooling, never to the running app, so a
// prompt injection or SQL escape can never reach the hosting.
app.get("/api/server", async (_req, res) => {
  let disk = null;
  try {
    const { execFileSync } = await import("child_process");
    const out = String(execFileSync("df", ["-k", DATA_DIR])).trim().split("\n").pop().split(/\s+/);
    disk = { totalMB: Math.round(Number(out[1]) / 1024), usedMB: Math.round(Number(out[2]) / 1024), freeMB: Math.round(Number(out[3]) / 1024) };
  } catch { /* not fatal */ }
  const cube = cubeManifest();
  res.json({
    app: process.env.FLY_APP_NAME || null,
    region: process.env.FLY_REGION || null,
    machine: process.env.FLY_MACHINE_ID || null,
    memoryMB: Number(process.env.FLY_VM_MEMORY_MB || 0) || null,
    // Used memory, so the panel can read "89 MB used of 1024 MB" the same way
    // Disk already does. /proc/meminfo is the container's real view; os.freemem()
    // reports the HOST on some platforms and would lie here.
    memoryUsedMB: (() => {
      try {
        const mi = fs.readFileSync("/proc/meminfo", "utf8");
        const kb = (k) => Number((mi.match(new RegExp(`^${k}:\\s+(\\d+)`, "m")) || [])[1] || 0);
        const total = kb("MemTotal"), avail = kb("MemAvailable");
        if (total && avail) return Math.round((total - avail) / 1024);
      } catch { /* not linux */ }
      try { return Math.round(process.memoryUsage().rss / 1048576); } catch { return null; }
    })(),
    cpus: (await import("os")).cpus().length,
    image: process.env.FLY_IMAGE_REF ? String(process.env.FLY_IMAGE_REF).split(":").pop() : null,
    version: (() => { try { return JSON.parse(fs.readFileSync(path.join(__dirname, "package.json"), "utf8")).version; } catch { return null; } })(),
    node: process.version,
    disk,
    rows: cube?.totalRows ?? 0,
    tables: (cube?.tables || []).length,
    holdsInfraToken: false,
    lastSync: cubeManifest()?.syncedAt || null,
  });
});

app.post("/api/diagnose", async (req, res) => {
  const { error = "", context = "" } = req.body || {};
  const hit = kbLookup(error);
  if (hit) return res.json({ source: "kb", title: hit.title, fix: hit.fix });
  const learned = loadLearned()[errSignature(error)];
  if (learned) return res.json({ source: "learned", title: learned.title, fix: learned.fix, seen: learned.seen });
  if (!aiKey()) return res.json({ source: "none", title: "Unrecognized problem", fix: "Check the server address, credentials, and that OData is enabled for this account." });
  try {
    const sys = "You are Pythia's support brain for FileMaker OData/Data API sync problems. Known issues and fixes:\n" +
      KNOWLEDGE.map((k) => `- ${k.title}: ${k.fix}`).join("\n") +
      '\nGiven an error, reply with ONLY JSON {"title":"...","fix":"..."}. One short sentence each, plain English a FileMaker admin can act on, no jargon.';
    const txt = await callAnthropic(sys, ("Context: " + context + "\nError: " + error).slice(0, 4000), 300, { ...aiSnapshot(), thinking: "off" });
    const m = txt.match(/\{[\s\S]*\}/);
    const out = m ? JSON.parse(m[0]) : { title: "Diagnosis", fix: txt.slice(0, 300) };
    rememberFix(error, out);
    res.json({ source: "ai", ...out });
  } catch {
    res.json({ source: "none", title: "Unrecognized problem", fix: "Check the server address, credentials, and that OData is enabled for this account." });
  }
});

// Reset to fresh install: delete the local copy (the actual data) and every
// derived artifact (reports, conversations, overview, ranking, snapshot), but
// KEEP the credentials: the FileMaker connection and the AI settings survive.
// Nothing rescans until the user clicks Scan database (needsScan gates the UI).
app.post("/api/reset", (_req, res) => {
  try {
    const keep = readConfig();
    fs.writeFileSync(REPORTS_PATH, JSON.stringify(SEED_REPORTS, null, 2));
    for (const f of fs.readdirSync(CONVOS_DIR)) { try { fs.unlinkSync(path.join(CONVOS_DIR, f)); } catch {} }
    fs.writeFileSync(CONFIG_PATH, JSON.stringify({
      savedAt: new Date().toISOString(), needsScan: true,
      fm: keep.fm, aiKey: keep.aiKey, openaiKey: keep.openaiKey, aiModel: keep.aiModel,
      aiModelPinned: keep.aiModelPinned, aiSpeed: keep.aiSpeed, aiThinking: keep.aiThinking,
      syncEvery: keep.syncEvery, displayNames: keep.displayNames, syncInstructions: keep.syncInstructions,
      // openaiKey was missing from this list when the provider switch shipped,
      // so every reset silently deleted the OpenAI key while claiming to keep
      // credentials (Matt, 2026-08-24: "the API keys got deleted").
      // Database context (aboutDatabase / priorities) is CLEARED on reset - it
      // describes THIS scanned database, and reset throws that away. It stays
      // empty until the user re-scans and re-drafts. (Matt's call, 2026-07-30.)
    }, null, 2));
    try { fs.unlinkSync(OVERVIEW_PATH); } catch {}
    try { fs.unlinkSync(RELEVANCE_PATH); } catch {}   // forget the AI table ranking
    try { fs.unlinkSync(SNAPSHOT_PATH); } catch {}    // forget the cached schema
    try { fs.unlinkSync(DDR_DIGESTS_PATH); } catch {} // forget uploaded DDR/SaXML/files
    try { fs.unlinkSync(DDR_DIGEST_PATH_OLD); } catch {}
    resetCube();                                       // delete the local copy, the actual data
    schemaCache = null; tablesCache = null; tablesCacheAt = 0; // whatever scans next scans fresh
    res.json({ ok: true });
  } catch (e) { res.status(500).json({ error: String(e.message || e) }); }
});

// --- Database context: what this database is ABOUT -------------------------
// Two user-owned texts (aboutDatabase, reportingPriorities) feed every AI
// prompt. The AI can draft them from the scan + the relevance ranking + an
// optional DDR/SaXML upload; the user edits and owns the result.
// Uploaded-file digests: an ARRAY (several files feed the context). Migrates
// the old single ddr-digest.json into the list on first read.
const DDR_DIGESTS_PATH = path.join(DATA_DIR, "ddr-digests.json");
const DDR_DIGEST_PATH_OLD = path.join(DATA_DIR, "ddr-digest.json");
function loadDdrDigests() {
  try { return JSON.parse(fs.readFileSync(DDR_DIGESTS_PATH, "utf8")); }
  catch {
    try { const one = JSON.parse(fs.readFileSync(DDR_DIGEST_PATH_OLD, "utf8")); return [{ ...one, id: "u1" }]; } catch { return []; }
  }
}
function saveDdrDigests(list) { fs.writeFileSync(DDR_DIGESTS_PATH, JSON.stringify(list, null, 2)); }
const ddrMeta = (d) => ({ id: d.id, file: d.file, when: d.when, kind: d.kind, counts: d.counts });

// The prompt block injected wherever Pythia thinks about the data.
// The user's standing sync instructions ride into every AI pass that touches
// schema or errors - naming, ranking, diagnosis - so corrections stick.
function instructionsBlock() {
  try { const t = (readConfig().syncInstructions || "").trim(); return t ? "THE USER'S STANDING INSTRUCTIONS (follow these):\n" + t.slice(0, 4000) + "\n\n" : ""; } catch { return ""; }
}
function contextBlock() {
  const c = readConfig();
  let s = "";
  if (c.aboutDatabase) s += "ABOUT THIS DATABASE (approved by the user - treat as ground truth):\n" + c.aboutDatabase + "\n\n";
  if (c.reportingPriorities) s += "WHAT THE USERS CARE ABOUT IN REPORTS:\n" + c.reportingPriorities + "\n\n";
  return s;
}

// Mine a DDR (FMPReport) or SaXML export for context: names and comments,
// nothing structural. Targeted tag extraction, tolerant of both formats -
// deliberately not a full XML parse (no new dependencies, huge files).
function mineDdr(xml, filename) {
  const grab = (re, cap = 1, max = 5000) => {
    const out = new Set(); let m;
    while ((m = re.exec(xml)) && out.size < max) { const v = (m[cap] || "").trim(); if (v) out.add(v); }
    return [...out];
  };
  const kind = /<FMPReport/i.test(xml) ? "ddr" : /<FMSaveAsXML|<FMDynamicTemplate/i.test(xml) ? "saxml" : "xml";
  const tables = grab(/<BaseTable\b[^>]*?\bname="([^"]{1,80})"/g);
  const fields = grab(/<Field\b[^>]*?\bname="([^"]{1,80})"/g, 1, 20000);
  const scripts = grab(/<Script\b[^>]*?\bname="([^"]{1,100})"/g);
  const layouts = grab(/<Layout\b[^>]*?\bname="([^"]{1,100})"/g);
  const valueLists = grab(/<ValueList\b[^>]*?\bname="([^"]{1,80})"/g);
  const relationships = grab(/<Relationship\b[^>]*?>/g, 0, 2000).length;
  const comments = grab(/<Comment\b[^>]*>([^<]{3,300})<\/Comment>/g, 1, 400);
  return {
    file: String(filename || "upload.xml").slice(0, 120), when: new Date().toISOString(), kind,
    counts: { tables: tables.length, fields: fields.length, scripts: scripts.length, layouts: layouts.length, valueLists: valueLists.length, relationships },
    tables: tables.slice(0, 80), scripts: scripts.slice(0, 120), layouts: layouts.slice(0, 80),
    valueLists: valueLists.slice(0, 40), comments: comments.slice(0, 120),
  };
}

app.get("/api/context", (_req, res) => {
  const c = readConfig();
  res.json({
    aboutDatabase: c.aboutDatabase || "", reportingPriorities: c.reportingPriorities || "",
    contextGeneratedAt: c.contextGeneratedAt || null, contextEdited: Boolean(c.contextEdited),
    uploads: loadDdrDigests().map(ddrMeta), // several files may feed the context
  });
});

// DDR/SaXML upload: raw XML body (the JSON parser ignores non-JSON types).
// Mine ANY uploaded file for context. DDR/SaXML gets the deep FileMaker parse;
// anything else (CSV, JSON, notes, docs) is turned into a bounded text digest
// the AI can still learn from. "Just process it and do what you can."
function mineAny(buf, filename) {
  const name = String(filename || "upload").slice(0, 120);
  const head = buf.slice(0, 4000).toString("utf8");
  const isBinary = /�/.test(head) || head.split("").filter((c) => c.charCodeAt(0) === 0).length > 2;
  if (/<\?xml|<FMPReport|<FMSaveAsXML/i.test(head)) {
    const d = mineDdr(buf.toString("utf8"), name);
    if (d.counts.tables || d.counts.scripts || d.counts.layouts) return d;
    // XML but not a FileMaker export: fall through to generic text handling.
  }
  if (isBinary) {
    return { file: name, when: new Date().toISOString(), kind: "binary",
      counts: { bytes: buf.length }, summary: `Binary file "${name}" (${(buf.length / 1024).toFixed(0)} KB). Contents can't be read as text; noting its presence only.` };
  }
  const text = buf.toString("utf8");
  const ext = (name.split(".").pop() || "").toLowerCase();
  let kind = ext || "text", note = "";
  try {
    if (ext === "json" || /^\s*[[{]/.test(text)) {
      const j = JSON.parse(text); kind = "json";
      const keys = Array.isArray(j) ? (j[0] && typeof j[0] === "object" ? Object.keys(j[0]) : []) : Object.keys(j);
      note = `JSON ${Array.isArray(j) ? `array of ${j.length}` : "object"}; keys: ${keys.slice(0, 40).join(", ")}.`;
    } else {
      const lines = text.split(/\r?\n/).filter(Boolean);
      const commas = (s) => (s.match(/,/g) || []).length;
      // Real CSV: the extension says so, or the first two lines share ≥2 commas
      // (a lone comma in a sentence of notes must NOT read as CSV).
      const looksCsv = ext === "csv" || ext === "tsv" ||
        (lines.length >= 2 && commas(lines[0]) >= 2 && commas(lines[0]) === commas(lines[1]));
      if (looksCsv) { kind = "csv"; note = `Delimited data, ${lines.length} rows. Header: ${(lines[0] || "").slice(0, 300)}. Sample: ${(lines[1] || "").slice(0, 300)}`; }
      else kind = ext || "text";
    }
  } catch { /* not valid JSON; treat as text */ }
  return { file: name, when: new Date().toISOString(), kind,
    counts: { bytes: buf.length, lines: text.split("\n").length },
    // A bounded excerpt so the draft can actually use it; whole files can be huge.
    summary: (note ? note + "\n\n" : "") + "Excerpt:\n" + text.slice(0, 6000) };
}

app.post("/api/context/ddr", express.raw({ type: () => true, limit: "64mb" }), (req, res) => {
  try {
    const buf = Buffer.isBuffer(req.body) ? req.body : Buffer.from(String(req.body || ""), "utf8");
    if (!buf.length) return res.status(400).json({ error: "The file came through empty." });
    // A SaXML export of a big solution can be enormous. Refuse politely when
    // it would crowd the data volume rather than dying mid-write.
    try {
      const st = fs.statfsSync(DATA_DIR);
      const freeMB = Math.round(st.bavail * st.bsize / 1048576);
      const needMB = Math.round(buf.length / 1048576);
      if (needMB > freeMB * 0.5) {
        return res.status(413).json({ error: `This XML file is ${needMB}MB but the server's data volume only has ${freeMB}MB free. Give the volume more room, or export a smaller copy (a clone with no records exports the same structure at a fraction of the size).` });
      }
    } catch { /* statfs unavailable: proceed */ }
    // The name can arrive three ways: a ?name= query, an X-Filename header, or
    // a Content-Disposition filename. Take whichever is there. Reading only the
    // query meant a header upload landed in the context called "upload".
    const cd = /filename\*?=(?:UTF-8''|")?([^";]+)/i.exec(String(req.headers["content-disposition"] || ""));
    const given = req.query.name || req.headers["x-filename"] || (cd && decodeURIComponent(cd[1]));
    const digest = mineAny(buf, given);
    digest.id = "u" + Date.now().toString(36);
    const list = loadDdrDigests();
    list.push(digest);
    while (list.length > 8) list.shift(); // keep the newest 8 files
    saveDdrDigests(list);
    res.json({ ok: true, ddr: ddrMeta(digest), uploads: list.map(ddrMeta) });
  } catch (e) { res.status(400).json({ error: String(e.message || e) }); }
});

// Remove one uploaded file from the context.
app.delete("/api/context/ddr/:id", (req, res) => {
  const list = loadDdrDigests().filter((d) => d.id !== req.params.id);
  saveDdrDigests(list);
  res.json({ ok: true, uploads: list.map(ddrMeta) });
});

// --- SaXML upload: structural hints (home file, real name, PK, mod field) ---
// One FMSaveAsXML file per POST; upload all the solution's files to build the
// full picture. Merges by file name (re-uploading a file replaces it).
function hintsSummary(h) {
  if (!h) return null;
  const up = Object.fromEntries((h.uploads || []).map((u) => [u.file, u]));
  const files = (h.files || []).map((f) => ({
    file: f.file, tables: f.tables.length,
    withPk: f.tables.filter((t) => t.pk).length,
    withMod: f.tables.filter((t) => t.modField).length,
    uploadedAt: up[f.file]?.at || h.savedAt || null,
    uploadName: up[f.file]?.uploadName || null,
  }));
  return { savedAt: h.savedAt, files, totalTables: files.reduce((s, f) => s + f.tables, 0) };
}
app.post("/api/schema/saxml", express.raw({ type: () => true, limit: "128mb" }), (req, res) => {
  try {
    const buf = Buffer.isBuffer(req.body) ? req.body : Buffer.from(String(req.body || ""), "utf8");
    if (!buf.length) return res.status(400).json({ error: "The file came through empty." });
    // A SaXML export of a big solution can be enormous. Refuse politely when
    // it would crowd the data volume rather than dying mid-write.
    try {
      const st = fs.statfsSync(DATA_DIR);
      const freeMB = Math.round(st.bavail * st.bsize / 1048576);
      const needMB = Math.round(buf.length / 1048576);
      if (needMB > freeMB * 0.5) {
        return res.status(413).json({ error: `This XML file is ${needMB}MB but the server's data volume only has ${freeMB}MB free. Give the volume more room, or export a smaller copy (a clone with no records exports the same structure at a fraction of the size).` });
      }
    } catch { /* statfs unavailable: proceed */ }
    const parsed = parseSaxml(buf.toString("utf8"));
    const merged = mergeHints(loadHints(), parsed);
    // The receipt: you did upload it, here is when, here is what it was called.
    merged.uploads = (merged.uploads || []).filter((u) => u.file !== parsed.file);
    merged.uploads.push({ file: parsed.file, uploadName: String(req.query.name || "").slice(0, 120) || null, at: new Date().toISOString() });
    saveHints(merged);
    schemaCache = null; tablesCache = null; tablesCacheAt = 0; // re-enrich next scan
    res.json({ ok: true, file: parsed.file, tables: parsed.tables.length, hints: hintsSummary(merged) });
  } catch (e) { res.status(400).json({ error: String(e.message || e) }); }
});
app.get("/api/schema/hints", (_req, res) => res.json({ hints: hintsSummary(loadHints()) }));
app.delete("/api/schema/hints", (_req, res) => {
  try { fs.unlinkSync(HINTS_PATH); } catch {}
  schemaCache = null; tablesCache = null; tablesCacheAt = 0;
  res.json({ ok: true });
});
// Per-FILE structure for the Settings browser: the SaXML truth (real file
// names, real table names, PK, mod field, comments), cross-referenced with
// what's live/synced. Falls back to the raw hints when nothing is scanned yet.
app.get("/api/schema/files", async (_req, res) => {
  try {
    const hints = loadHints();
    if (!hints) return res.json({ hints: false, files: [] });
    let live = null;
    try { live = (schemaCache || await rehomedSchema()); } catch { /* pre-scan: hints only */ }
    const included = new Set(configuredTables());
    const liveByHome = new Map(); // homeFile -> Map(saxmlName -> liveTable)
    for (const t of live?.tables || []) {
      if (!t.saxmlName) continue;
      const home = t.homeFile || t.db;
      if (!liveByHome.has(home)) liveByHome.set(home, new Map());
      liveByHome.get(home).set(t.saxmlName, t);
    }
    const files = hints.files.map((f) => {
      const home = f.file.replace(/\.fmp12$/i, "");
      const lm = liveByHome.get(home);
      return {
        file: f.file, home,
        tables: f.tables.map((t) => {
          const lt = lm?.get(t.name);
          return {
            name: t.name, pk: t.pk, pkKind: t.pkKind, modField: t.modField,
            comment: t.comment, fields: t.fields.length,
            live: Boolean(lt), synced: lt ? included.has(lt.name) : false,
            liveName: lt?.name || null, rowCount: lt?.rowCount ?? null,
          };
        }),
      };
    });
    res.json({ hints: true, files });
  } catch (e) { res.status(500).json({ error: String(e.message || e) }); }
});

// Draft the description from everything Pythia already knows.
app.post("/api/context/generate", async (_req, res) => {
  try {
    if (!aiKey()) return res.status(400).json({ error: "No AI key set." });
    let schema = "";
    try { schema = await cubeSchemaText(); } catch { /* pre-sync: fall back to scan */ }
    if (!schema) {
      const t = tablesCache?.tables || [];
      schema = t.map((x) => `- ${x.name} (${x.rowCount ?? "?"} records)`).join("\n");
    }
    let relevance = [];
    try { relevance = JSON.parse(fs.readFileSync(RELEVANCE_PATH, "utf8")).ranking || []; } catch { /* none */ }
    const digests = loadDdrDigests();
    if (!schema && !relevance.length && !digests.length) {
      return res.status(400).json({ error: "Nothing to describe yet - scan the database (or upload a DDR) first." });
    }
    const relLines = relevance.map((r) => `- ${r.name}: ${r.tier}${r.displayName ? ` "${r.displayName}"` : ""}${r.reason ? ` — ${r.reason}` : ""}`).join("\n");
    // Field comments are the developer talking - use them when FM is reachable.
    let commentLines = "";
    try {
      const s = await rehomedSchema();
      commentLines = s.tables.flatMap((t) => (t.fields || []).filter((f) => f.comment).slice(0, 6).map((f) => `- ${t.name}.${f.name}: ${f.comment}`)).slice(0, 80).join("\n");
    } catch { /* offline is fine */ }
    // Every uploaded file feeds the draft. DDR/SaXML → its FileMaker structure;
    // a generic upload (CSV/JSON/notes) → its text summary.
    const ddrLines = digests.map((d) => d.summary
      ? `Uploaded file "${d.file}":\n${String(d.summary).slice(0, 4000)}`
      : `DDR/SaXML digest (${d.file}): tables ${d.counts.tables}, scripts ${d.counts.scripts}, layouts ${d.counts.layouts}.\n` +
        (d.scripts?.length ? "Script names (a window into the workflows): " + d.scripts.slice(0, 60).join("; ") + "\n" : "") +
        (d.comments?.length ? "Developer comments: " + d.comments.slice(0, 60).join(" | ") : "")
    ).join("\n\n");
    const system =
      "You are helping configure Pythia, a reporting tool, for a FileMaker database you can see the structure of. " +
      "Write a description (~200 words, plain business English, no markdown, no headers) that a NEW report-reader would find orienting: " +
      "what this database is about, the key entities and how they relate, and what kinds of reporting it supports. " +
      "Write declaratively; no hedging like 'appears to'. Do not mention SQL, DuckDB, OData, or Pythia itself.";
    const user = "Tables:\n" + schema + (relLines ? "\n\nAI table ranking (tier + inferred purpose):\n" + relLines : "") +
      (commentLines ? "\n\nField comments from the developer:\n" + commentLines : "") + (ddrLines ? "\n\n" + ddrLines : "");
    const draft = (await callAnthropic(system, user, 1200, { ...aiSnapshot(), thinking: "off" })).trim();
    const config = readConfig();
    config.contextGeneratedAt = new Date().toISOString();
    fs.writeFileSync(CONFIG_PATH, JSON.stringify(config, null, 2));
    res.json({ draft });
  } catch (e) { res.status(502).json({ error: String(e.message || e) }); }
});

// AI auto-generated reports: given the actual synced schema, propose reports a
// manager would want, validate each SQL by running it, keep the ones that work.
app.post("/api/reports/generate", async (req, res) => {
  try {
    if (!aiKey()) return res.status(400).json({ error: "No AI key set." });
    await ensureData();
    const schema = await cubeSchemaText();
    const dn = getDisplayNames();
    const system =
      "You are a data analyst. Given a database (DuckDB tables below), design 6 reports its owner would actually keep and run regularly. " +
      "Cover what THIS data supports: trends over time, top/bottom rankings, distributions and breakdowns, geography or segments, health/exception lists – pick the strongest stories in the schema. " +
      "If the schema tracks work over time (jobs, projects, time entries, timecards, labor, orders), make at least ONE report PREDICTIVE per the predictive-analysis rules below: a trend-plus-projection line (e.g. hours or spend per week with the next periods projected from the trend) or a pace/completion forecast. Projected periods must be labeled as projected. " +
      "For each report return: name, description (one sentence), sql (a single DuckDB SELECT against the real table names, on ONE line), and chart ({type:'bar'|'line', x, y} or null for a list). " +
      "Quote identifiers with spaces. Use readable period labels for time-series. Only use tables/columns that exist. Keep each SQL compact. " +
      "COLUMN DISCIPLINE: long free-text columns (descriptions, notes, comments, review bodies) do NOT belong in tabular reports - select the attributes, not the prose. Include a text column only when the report is explicitly about the text. " +
      "TRUTHFUL TITLES: the name must describe the query that actually runs - its grouping and any time window. Never name a time window ('Next 3 Months', 'Last Year') unless the SQL enforces it with a WHERE clause. A projection report must COMPUTE its projected values in the SQL (e.g. average of recent periods carried forward), never emit an empty column for someone else to fill. " +
      "PRESENTATION RULE: reports must NEVER select a UUID, internal ID, or raw foreign key as a shown column – always join to and select the human label (a name/title column) and never include a bare ID column. Rows are identified by name, not key. " +
      "Return ONLY a JSON array of 6 objects, no markdown.\n\n" +
      contextBlock() + instructionsBlock() + statsBlock() + "Tables:\n" + schema + "\n\n" + DATA_NOTES +
      "\nFriendly names (for the description wording only; SQL uses real names): " + Object.entries(dn).map(([r, f]) => `${r}=${f}`).join(", ");
    // Thinking off + a large budget: adaptive thinking on a big schema can
    // consume the whole allowance and return zero text ("parse failed, raw
    // len 0"); this is structured design work, not open reasoning.
    const txt = await callAnthropic(system, "Design the reports now. JSON array only.", 16000, { ...aiSnapshot(), thinking: "off" });
    let proposed = [];
    try {
      const s = txt.indexOf("["), e = txt.lastIndexOf("]");
      proposed = JSON.parse(txt.slice(s, e + 1));
    } catch { console.log("[generate] parse failed, raw len", txt.length, txt.slice(-160)); return res.status(502).json({ error: "AI did not return valid report specs. Try again." }); }
    const list = loadReports();
    const created = [];
    const rejected = [];
    for (const p of proposed) {
      if (!p?.sql || !p?.name) continue;
      let rows;
      try { rows = await cubeSql(p.sql); if (!rows.length) { rejected.push(`${p.name}: returned no rows`); continue; } } catch (e) { rejected.push(`${p.name}: SQL failed`); continue; }
      // A report must not promise what its query doesn't deliver ("Next 3
      // Months" that returns 2017, a Projected column that is entirely
      // empty). Declared but never-filled columns kill the report.
      const cols = Object.keys(rows[0] || {});
      const empty = cols.filter((c) => rows.every((r) => r[c] == null || r[c] === ""));
      if (empty.length) { rejected.push(`${p.name}: column(s) never filled: ${empty.join(", ")}`); continue; }
      let id = slug(p.name); while (list.some((r) => r.id === id) || created.some((r) => r.id === id)) id += "-2";
      created.push({ id, name: p.name, description: p.description || "", sql: p.sql, chart: p.chart || null, generated: true, updatedAt: new Date().toISOString() });
    }
    if (rejected.length) console.log("[generate] rejected:", rejected.join(" | "));
    if (!created.length) return res.status(502).json({ error: "None of the proposed reports ran cleanly. Try again." });
    saveReports([...list, ...created]);
    res.json({ created: created.map((r) => ({ id: r.id, name: r.name })), count: created.length });
  } catch (e) { res.status(502).json({ error: String(e.message || e) }); }
});

// FileMaker OnWindowTransaction webhook. Body is the trigger's JSON:
//   { "<FileName>": { "<BaseTable>": [ ["New|Modified|Deleted", recordID, fieldContent], ... ] } }
// New/Modified -> incremental sync of that table (fast, mod-date). Deleted with
// a UUID in fieldContent -> remove that row from the copy. Responds immediately
// so a FileMaker commit is never held up; work runs in the background, one at a
// time (DuckDB is single-writer).
let notifyChain = Promise.resolve();
app.post("/api/notify", (req, res) => {
  const payload = req.body || {};
  res.json({ ok: true }); // fast ack; process out of band
  notifyChain = notifyChain.then(async () => {
    if (!fmConfigured()) return;
    const schema = await rehomedSchema();
    const included = new Set(configuredTables()); // only tables the user reports on
    const findTable = (name) => schema.tables.find((t) => t.name === name || t.occurrences.includes(name));
    const toSync = new Set(), deletes = [];
    for (const file of Object.values(payload)) {
      if (!file || typeof file !== "object") continue;
      for (const [baseTable, ops] of Object.entries(file)) {
        const t = findTable(baseTable);
        if (!t || !included.has(t.name) || !Array.isArray(ops)) continue;
        const pk = t.keys?.[0] || t.fields.find((f) => /^id$/i.test(f.name))?.name;
        for (const row of ops) {
          const [op, , fieldContent] = row;
          if (op === "Deleted") { if (fieldContent && pk) deletes.push({ table: t.name, pk, id: fieldContent }); }
          else toSync.add(t.name); // New / Modified
        }
      }
    }
    if (toSync.size) {
      while (syncJob && !syncJob.done) await syncJob.promise; // never overlap; notifyChain already serializes notifies
      const job = startSyncJob([...toSync], { reconcile: false, origin: "webhook" });
      await job.promise;
      if (job.error) console.log("[notify] sync failed:", job.error);
    }
    for (const d of deletes) {
      await cubeSql(`DELETE FROM "${d.table.replace(/"/g, '""')}" WHERE "${d.pk.replace(/"/g, '""')}" = '${String(d.id).replace(/'/g, "''")}'`, { allowWrite: true });
    }
    console.log(`[notify] synced [${[...toSync].join(",")}] deleted ${deletes.length}`);
  }).catch((e) => console.log("[notify] error:", e.message));
});

// Apply a persisted in-app FileMaker connection (from a previous Settings save)
// on top of the env defaults, so an in-browser setup survives restarts.
try { const _c = readConfig(); if (_c.fm) setConnection(_c.fm); } catch { /* env-only is fine */ }

app.listen(PORT, () => {
  console.log(`Pythia listening on :${PORT} (fm=${fmConfigured()}, cube=${cubeExists()})`);
  // Warm the tables cache at boot so Settings opens instantly even when the
  // schema has to come through the slow Data API fallback path.
  if (fmConfigured() && !readConfig().needsScan) {
    startTablesBuild(false);
    tablesBuild.then((r) => console.log(r ? `[warm] tables cache ready (${(r.tables || []).length} tables${r.via ? ", via " + r.via : ""}${r.stale ? ", STALE" : ""})` : "[warm] build ended with nothing to cache"), (e) => console.log("[warm] build failed:", e?.message || e));
  }
  // First-run: if connected but no local copy yet, build it in the background.
  // With no connection at all there is nothing to build; Settings is the door.
  if (fmConfigured() && !cubeExists()) {
    // As a JOB, not a detached promise: the modal can attach to it, show
    // progress, and cancel it, exactly like a user-started sync.
    console.log("[first-run] no local copy; starting the initial sync as a watchable job…");
    try { startSyncJob(configuredTables(), { origin: "boot" }); } catch (e) { console.log("[first-run] sync failed to start:", e.message); }
  } else if (!fmConfigured()) {
    console.log("[first-run] no FileMaker connection yet - open Settings to connect one.");
  }
});