sql-server-query-ai/lib/analyst.ts

import { z } from "zod/v4";
import { digestForModel, type Digest } from "./digest.ts";
import { openAiTextDeltas } from "./stream.ts";
import type { AnalystConfig } from "./settings.ts";

export class AnalystError extends Error {}

const Action = z.object({ title: z.string().min(3), detail: z.string().min(3),
  effort: z.enum(["low", "medium", "high"]), requires_change_control: z.boolean() });
const RawCandidate = z.object({ key: z.string().min(2), title: z.string().min(3), diagnosis: z.string().min(15),
  actions: z.array(Action).min(1), rewrite_sql: z.string().nullable(), expected: z.string().min(5) });
const Proposal = z.object({ candidates: z.array(RawCandidate).min(2).max(4) });
export type Action = z.infer<typeof Action>;
export type Candidate = z.infer<typeof RawCandidate>;

export const CANDIDATE_SYSTEM_PROMPT = `You are a SQL Server performance engineer. You receive a JSON digest of one
execution plan. Judge only what the digest contains — never invent operators, waits, or row counts you were not given.

Return 2 to 4 genuinely different remediation options, not one option restated. Real differences: an index change, a
rewritten query, a statistics or parallelism fix, a batching or schema change. At least one option must be the
lowest-risk one. Each option needs a snake_case key, a diagnosis naming WHY the plan is slow, 1-4 ordered actions, an
optional rewritten query, and an expected effect stated as a concrete claim about the plan (logical reads, seek vs
scan, tempdb spill, degree of parallelism).

A rewrite must return the same rows as the original. If you cannot rewrite safely, set rewrite_sql to null.
Output ONE JSON object and nothing else:
{"candidates":[{"key":"idx_covering_orders","title":"...","diagnosis":"...","actions":[{"title":"...","detail":"...",
"effort":"low|medium|high","requires_change_control":false}],"rewrite_sql":null,"expected":"..."}]}`;

export function extractJson(text: string): unknown {
  const fenced = /```(?:json)?\s*([\s\S]*?)```/.exec(text);
  const s = (fenced ? fenced[1] : text).trim();
  const start = s.indexOf("{"); const end = s.lastIndexOf("}");
  if (start === -1 || end <= start) throw new AnalystError("The analyst model returned prose instead of JSON.");
  try { return JSON.parse(s.slice(start, end + 1)); }
  catch { throw new AnalystError("The analyst model returned malformed JSON."); }
}

export async function proposeCandidates(cfg: AnalystConfig, digest: Digest, note: string,
    fetchImpl: typeof fetch = fetch, signal?: AbortSignal): Promise<Candidate[]> {
  const res = await fetchImpl(`${cfg.baseUrl}/chat/completions`, {
    method: "POST", signal,
    headers: { "content-type": "application/json", authorization: `Bearer ${cfg.apiKey}` },
    body: JSON.stringify({ model: cfg.model, temperature: 0.6, max_tokens: 2048, ...cfg.extra, stream: false,
      messages: [{ role: "system", content: CANDIDATE_SYSTEM_PROMPT },
        { role: "user", content: JSON.stringify({ digest: digestForModel(digest), user_note: note || null }) }] }),
  }).catch((e) => { throw new AnalystError(`Could not reach ${cfg.baseUrl}: ${(e as Error).message}`); });

  if (!res.ok) throw new AnalystError(`Analyst model returned HTTP ${res.status}. ${(await res.text()).slice(0, 300)}`);
  const content: string = ((await res.json() as any)?.choices?.[0]?.message?.content) ?? "";
  const parsed = Proposal.safeParse(extractJson(content));
  if (!parsed.success) {
    const n = (extractJson(content) as any)?.candidates?.length ?? 0;
    throw new AnalystError(n < 2
      ? `The analyst model proposed ${n} option(s); at least 2 are needed before Jev can rank anything.`
      : `Analyst output did not match the schema at ${parsed.error.issues[0].path.join(".")}: ${parsed.error.issues[0].message}`);
  }
  return parsed.data.candidates;
}

export const REPORT_SYSTEM_PROMPT = (openuiPrompt: string) => `${openuiPrompt}

You are writing the final answer for a DBA holding one SQL Server execution plan. The ranking you are given was
computed by Jev from typed judgments. It is not your opinion: do not re-rank it, soften it, or hide it. Quote each
option's scores as given. A rewrite must return the same rows as the original — when in doubt keep the original text
and say why. Never invent a number that is not in the input.`;

export async function* streamReport(cfg: AnalystConfig, payload: unknown, openuiPrompt: string,
    fetchImpl: typeof fetch = fetch, signal?: AbortSignal): AsyncGenerator<string> {
  const res = await fetchImpl(`${cfg.baseUrl}/chat/completions`, {
    method: "POST", signal,
    headers: { "content-type": "application/json", authorization: `Bearer ${cfg.apiKey}` },
    body: JSON.stringify({ model: cfg.model, temperature: 0.3, max_tokens: 2048, ...cfg.extra, stream: true,
      messages: [{ role: "system", content: REPORT_SYSTEM_PROMPT(openuiPrompt) },
        { role: "user", content: JSON.stringify(payload) }] }),
  }).catch((e) => { throw new AnalystError(`Could not reach ${cfg.baseUrl}: ${(e as Error).message}`); });
  if (!res.ok || !res.body) throw new AnalystError(`Analyst model returned HTTP ${res.status} while writing the report.`);
  yield* openAiTextDeltas(res);
}