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),
  option_type: z.enum(["index", "statistics", "rewrite", "schema", "app", "ops"]),
  sql_to_run: 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, do not offer one.
When the digest says sql_truncated is true, you were not given the whole statement: never offer option_type
"rewrite", because you cannot reproduce text you never received. Offer an index, statistics, or query-hint
change instead, and say in expected that the statement itself was not rewritten here.

option_type is what the option actually is: index | statistics | rewrite | schema | app | ops.
sql_to_run is the exact T-SQL the user must execute for that option — CREATE INDEX, CREATE STATISTICS,
UPDATE STATISTICS, ALTER TABLE, or the rewritten query — with the real table, column and index names taken from
the digest. Null only when the option needs no SQL at all (query-store verification, app-side batching, an ops
change). An option whose key says index or statistics and whose sql_to_run is null is a wrong answer.
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}],"option_type":"index","sql_to_run":"CREATE INDEX ...","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 {
    // "malformed JSON" after 38 seconds blamed the model for what was usually our own output ceiling:
    // a truncated response has no closing brace, so say the length out loud and let the user raise it
    // through the extra-params field (which overrides max_tokens).
    throw new AnalystError(`The analyst model's JSON stopped after ${s.length} characters without closing. `
      + `Raise the ceiling in settings extra params, e.g. {"max_tokens":8192}.`);
  }
}

// A SqlBlock the user cannot paste is worse than no SqlBlock: it reads as an instruction and breaks on run.
// Models reach for "SELECT ... FROM ..." when the original statement is too long to reproduce verbatim.
// ponytail: "..." is not valid T-SQL, so the ellipsis is a safe tell; a literal '...' inside a string
// literal would be dropped too, which costs a block and never invents one.
export function dropPlaceholderSql<T extends { sql_to_run: string | null }>(candidates: T[]): T[] {
  return candidates.map((c) => (c.sql_to_run && /[\u2026]|\.\.\.\s*(FROM|WHERE|OPTION|;|$)|(FROM|SELECT)\s+\.\.\./i.test(c.sql_to_run)
    ? { ...c, sql_to_run: null } : c));
}

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: 4096, ...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 dropPlaceholderSql(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.

Hard requirements, in order:
1. Every option whose sql_to_run is not null gets its own SqlBlock containing exactly that T-SQL, unaltered and
   runnable as pasted. Never write an ellipsis or a placeholder inside a SqlBlock: if the option's sql_to_run is
   null, that option gets no SqlBlock at all. A report whose first move needs something executed and contains no
   SqlBlock is wrong.
2. Every option gets a ScoreBar for its Jev composite score, carrying the confidence the ranking gave it.
3. VerdictBlock names the first move and states what to run first.`;

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: 4096, ...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);
}