import postgres from "postgres";
import { createHourlyJsonlWriter, logRetentionBytesForService } from "../../../shared/src/rotating-jsonl";
import { codeQueuePromptResponseFields, safePreview } from "./prompt-observation";

type JsonValue = string | number | boolean | null | JsonValue[] | { [key: string]: JsonValue };
type JsonRecord = Record<string, JsonValue>;
type SqlExecutor = postgres.Sql | postgres.TransactionSql;
type TaskStatus = "queued" | "running" | "judging" | "retry_wait" | "succeeded" | "failed" | "canceled";
type CodeExecutionMode = "default" | "windows-native";
type RunMode = "initial" | "retry";
type OutputChannel = "system" | "user" | "assistant" | "reasoning" | "command" | "diff" | "tool" | "error";

interface RuntimeConfig {
  host: string;
  port: number;
  databaseUrl: string;
  logFile: string;
  deployServiceId: string;
  deployRepo: string;
  deployCommit: string;
  deployRequestedCommit: string;
  mgrDatabasePoolMax: number;
  traceDatabasePoolMax: number;
  defaultProviderId: string;
  defaultWorkdir: string;
  remoteDefaultWorkdir: string;
  defaultModel: string;
  codeModels: string[];
  defaultMaxAttempts: number;
  defaultReasoningEffort: string | null;
  modelReasoningEfforts: Record<string, string>;
  defaultQueueId: string;
  maxListLimit: number;
}

interface QueueRecord {
  id: string;
  name: string;
  createdAt: string;
  updatedAt: string;
}

interface WorkdirRecord {
  providerId: string;
  executionMode: CodeExecutionMode;
  path: string;
  createdAt: string;
  updatedAt: string;
}

interface DailyTaskStatsBucket {
  date: string;
  executedTasks: number;
  completedTasks: number;
  retryAttempts: number;
  succeededTasks: number;
  failedTasks: number;
  canceledTasks: number;
  totalDurationMs: number;
  durationSamples: number;
}

interface ReferenceInjectionSummaryItem {
  round: number;
  roundIndex: number;
  taskId: string;
  viaTaskId: string | null;
  status: TaskStatus;
  providerId: string;
  executionMode?: CodeExecutionMode;
  model: string;
  cwd: string;
  createdAt: string;
  updatedAt: string;
  promptChars: number;
  finalResponseChars: number;
  finalResponseAt: string | null;
  finalResponseSource: string;
  referenceTaskIds: string[];
  cliHint: string;
}

interface ReferenceInjectionRecord {
  version: 2;
  injectedAt: string;
  basePrompt: string;
  directReferenceTaskIds: string[];
  maxRounds: number | null;
  truncated: boolean;
  itemCount: number;
  items: ReferenceInjectionSummaryItem[];
}

interface PreparedPrompt {
  prompt: string;
  basePrompt: string;
  referenceTaskIds: string[];
  referenceInjection: JsonValue | null;
}

interface LiveOutput {
  seq: number;
  at: string;
  channel: OutputChannel;
  text: string;
  method?: string;
  itemId?: string;
}

interface AttemptSummary {
  index: number;
  mode: RunMode;
  startedAt: string;
  finishedAt: string;
  providerId?: string;
  executionMode?: CodeExecutionMode;
  terminalStatus: "completed" | "interrupted" | "failed" | null;
  transportClosedBeforeTerminal: boolean;
  appServerExitCode: number | null;
  appServerSignal: string | null;
  error: string | null;
  events?: JsonValue[];
  inputPrompt?: string;
  inputPromptPreview?: string;
  inputPromptChars?: number;
  inputPromptLines?: number;
  finalResponse?: string;
  finalResponsePreview: string;
  finalResponseChars?: number;
  judge?: JsonValue;
  judgeAt?: string | null;
  judgeSeq?: number | null;
  feedbackPrompt?: string;
  feedbackPromptPreview?: string;
  feedbackPromptChars?: number;
  feedbackPromptLines?: number;
  feedbackPromptSource?: string;
  feedbackPromptForAttempt?: number | null;
  stderrTail: string;
  outputStartSeq?: number | null;
  outputEndSeq?: number | null;
  errorCount?: number;
}

interface QueueTask {
  id: string;
  queueId: string;
  queueEnteredAt: string;
  prompt: string;
  basePrompt: string;
  referenceTaskIds: string[];
  referenceInjection: JsonValue | null;
  providerId: string;
  cwd: string;
  model: string;
  reasoningEffort: string | null;
  executionMode: CodeExecutionMode;
  maxAttempts: number;
  status: TaskStatus;
  createdAt: string;
  updatedAt: string;
  startedAt: string | null;
  finishedAt: string | null;
  readAt: string | null;
  currentAttempt: number;
  currentMode: RunMode | null;
  codexThreadId: string | null;
  activeTurnId: string | null;
  schedulerHeartbeat?: JsonRecord | null;
  finalResponse: string;
  stepCount?: number;
  llmStepCount?: number;
  outputMaxSeq?: number;
  lastError: string | null;
  lastJudge: JsonValue | null;
  judgeFailCount: number;
  promptHistory: JsonValue[];
  output: LiveOutput[];
  events: JsonValue[];
  attempts: AttemptSummary[];
  cancelRequested: boolean;
  nextPrompt: string | null;
  nextMode: RunMode | null;
}

interface TaskRow {
  id: string;
  queue_id: string;
  status: TaskStatus;
  provider_id: string;
  execution_mode: CodeExecutionMode;
  model: string;
  cwd: string;
  prompt: string;
  base_prompt: string;
  reference_task_ids: JsonValue;
  reference_injection: JsonValue | null;
  reasoning_effort: string | null;
  max_attempts: number | string;
  current_attempt: number | string;
  current_mode: RunMode | null;
  codex_thread_id: string | null;
  active_turn_id: string | null;
  created_at: Date | string;
  updated_at: Date | string;
  started_at: Date | string | null;
  finished_at: Date | string | null;
  read_at: Date | string | null;
  last_error: string | null;
  last_judge: JsonValue | null;
  output_count: number | string;
  event_count: number | string;
  attempt_count: number | string;
  last_output_seq: number | string;
  task_json: unknown;
}

type TaskStatusRow = Pick<TaskRow, "id" | "queue_id" | "status" | "started_at" | "current_attempt" | "codex_thread_id" | "active_turn_id">;
type TaskJsonRow = Pick<TaskRow, "task_json"> & Partial<Pick<TaskRow,
  "id"
  | "queue_id"
  | "status"
  | "current_attempt"
  | "current_mode"
  | "codex_thread_id"
  | "active_turn_id"
  | "updated_at"
  | "started_at"
  | "finished_at"
  | "read_at"
>>;

interface QueueRow {
  id: string;
  name: string;
  created_at: Date | string;
  updated_at: Date | string;
}

interface OaTraceStepRow {
  scope_id: string;
  step_seq: number | string;
  kind: string;
  title: string | null;
  status: string | null;
  summary_lines: unknown;
  raw_seqs: unknown;
  created_at: Date | string;
  updated_at: Date | string;
}

interface OaTraceStatsRow {
  scope_id: string;
  service_id: string;
  subject_kind: string;
  subject_id: string;
  stats_revision: number | string;
  step_count: number | string;
  llm_step_count: number | string;
  read_count: number | string;
  edit_count: number | string;
  run_count: number | string;
  error_count: number | string;
  trace_line_count: number | string;
  output_max_seq: number | string;
  attempt_stats_json: unknown;
  last_event_sequence: number | string | null;
  updated_at: Date | string;
}

interface TraceStepLine {
  seq: number;
  at: string;
  kind: string;
  label: string;
  title: string;
  status: string | null;
  summaryLines: string[];
  commandPreview?: string;
  commandOmittedLines?: number;
  bodyPreview?: string;
  bodyOmittedLines?: number;
  rawSeqs: number[];
  source: string;
}

interface HttpErrorDetail {
  status: number;
  body: JsonRecord;
}

const serviceStartedAt = new Date().toISOString();
const recentLogs: JsonRecord[] = [];
const defaultQueueId = "default";
const queueIdPattern = /^[A-Za-z0-9][A-Za-z0-9_.-]{0,63}$/u;
const codexTaskIdPattern = /^codex_\d+_[A-Za-z0-9_-]+$/u;
const referenceInjectionMaxRounds: number | null = null;
const resolvedReferenceContextTitle = "# Code Queue 已解析引用上下文";
const currentTaskPromptMarker = "\n# 本次任务\n";
const codeQueueEnvironmentHintTitle = "# Code Queue 运行环境提示";
const codeQueueEnvironmentHint = [
  codeQueueEnvironmentHintTitle,
  "如果当前 Code Queue Docker 容器缺少完成任务所需的环境、系统包或语言依赖，可以先在容器内临时安装以推进当前任务；同时必须把该依赖补到 `src/components/microservices/code-queue/Dockerfile`，让后续任务重建镜像后可直接使用。",
].join("\n");
const maxTaskAttempts = 99;
const workdirMaxLength = 512;
const deepseekChatModel = "deepseek-chat";
const minimaxM27Model = "minimax-m2.7";
const defaultCodeModels = ["gpt-5.5", "gpt-5.4-mini", "gpt-5.4", deepseekChatModel, minimaxM27Model];
const codeExecutionModes: CodeExecutionMode[] = ["default", "windows-native"];
const codexStatsTimeZone = "Asia/Shanghai";
const schedulerHeartbeatStaleMs = 5 * 60 * 1000;
const codexStatsDateFormatter = new Intl.DateTimeFormat("en-CA", {
  timeZone: codexStatsTimeZone,
  year: "numeric",
  month: "2-digit",
  day: "2-digit",
});

let schemaReady = false;
let schemaLastError: JsonRecord | null = null;
let lastDatabaseError: string | null = null;

function envString(name: string, fallback: string): string {
  const value = process.env[name];
  return value === undefined || value.length === 0 ? fallback : value;
}

function envNumber(name: string, fallback: number): number {
  const raw = process.env[name];
  if (raw === undefined || raw.length === 0) return fallback;
  const value = Number(raw);
  return Number.isFinite(value) && value > 0 ? Math.floor(value) : fallback;
}

function envList(name: string, fallback: string[]): string[] {
  const raw = process.env[name];
  const source = raw === undefined || raw.length === 0 ? fallback.join(",") : raw;
  return Array.from(new Set(source.split(",").map((item) => item.trim()).filter(Boolean)));
}

function envReasoningMap(name: string, fallback: Record<string, string>): Record<string, string> {
  const raw = process.env[name];
  const map: Record<string, string> = { ...fallback };
  if (raw === undefined || raw.trim().length === 0) return map;
  for (const item of raw.split(/[,;]+/u)) {
    const [model, effort] = item.split("=", 2).map((part) => part.trim());
    if (model && effort) map[model.toLowerCase()] = effort;
  }
  return map;
}

function clampAttempts(value: number): number {
  return Math.max(1, Math.min(maxTaskAttempts, Math.floor(value)));
}

function configFromEnv(): RuntimeConfig {
  const databaseUrl = process.env.DATABASE_URL || "";
  if (!databaseUrl) throw new Error("DATABASE_URL is required");
  const defaultModel = normalizeCodeModel(envString("CODE_QUEUE_DEFAULT_MODEL", "gpt-5.5"));
  const codeModels = Array.from(new Set([defaultModel, ...envList("CODE_QUEUE_MODELS", defaultCodeModels).map(normalizeCodeModel)]));
  return {
    host: envString("HOST", "0.0.0.0"),
    port: envNumber("PORT", 4278),
    databaseUrl,
    logFile: envString("LOG_FILE", "/var/log/unidesk/code-queue-mgr.jsonl"),
    deployServiceId: envString("UNIDESK_CODE_QUEUE_MGR_DEPLOY_SERVICE_ID", "code-queue-mgr"),
    deployRepo: envString("UNIDESK_CODE_QUEUE_MGR_DEPLOY_REPO", ""),
    deployCommit: envString("UNIDESK_CODE_QUEUE_MGR_DEPLOY_COMMIT", ""),
    deployRequestedCommit: envString("UNIDESK_CODE_QUEUE_MGR_DEPLOY_REQUESTED_COMMIT", ""),
    mgrDatabasePoolMax: Math.max(1, Math.min(2, envNumber("CODE_QUEUE_MGR_DATABASE_POOL_MAX", 2))),
    traceDatabasePoolMax: Math.max(1, Math.min(2, envNumber("CODE_QUEUE_TRACE_DATABASE_POOL_MAX", 1))),
    defaultProviderId: envString("CODE_QUEUE_MAIN_PROVIDER_ID", "D601"),
    defaultWorkdir: envString("CODE_QUEUE_WORKDIR", "/workspace"),
    remoteDefaultWorkdir: envString("CODE_QUEUE_REMOTE_WORKDIR", "/home/ubuntu"),
    defaultModel,
    codeModels,
    defaultMaxAttempts: clampAttempts(envNumber("CODE_QUEUE_MAX_ATTEMPTS", maxTaskAttempts)),
    defaultReasoningEffort: process.env.CODE_QUEUE_DEFAULT_REASONING_EFFORT?.trim() || null,
    modelReasoningEfforts: envReasoningMap("CODE_QUEUE_MODEL_REASONING_EFFORTS", { "gpt-5.5": "xhigh" }),
    defaultQueueId,
    maxListLimit: Math.max(20, Math.min(500, envNumber("CODE_QUEUE_MGR_MAX_LIST_LIMIT", 200))),
  };
}

const config = configFromEnv();
const mgrSql = postgres(config.databaseUrl, {
  max: config.mgrDatabasePoolMax,
  idle_timeout: 20,
  connect_timeout: 5,
  connection: { application_name: "unidesk-code-queue-mgr" },
});
const traceSql = postgres(config.databaseUrl, {
  max: config.traceDatabasePoolMax,
  idle_timeout: 20,
  connect_timeout: 5,
  connection: { application_name: "unidesk-code-queue-trace-read" },
});
const logWriter = config.logFile
  ? createHourlyJsonlWriter({
      baseLogFile: config.logFile,
      service: "code-queue-mgr",
      maxBytes: logRetentionBytesForService("code-queue-mgr"),
    })
  : null;
logWriter?.prune();

function toJsonValue(value: unknown): JsonValue {
  if (value === undefined) return null;
  return JSON.parse(JSON.stringify(value)) as JsonValue;
}

function errorToJson(error: unknown): JsonRecord {
  if (error instanceof Error) return { name: error.name, message: error.message, stack: error.stack ?? null };
  return { message: String(error) };
}

function log(level: "info" | "warn" | "error", event: string, detail: JsonRecord = {}): void {
  const record: JsonRecord = { at: new Date().toISOString(), service: "code-queue-mgr", level, event, ...detail };
  recentLogs.push(record);
  while (recentLogs.length > 300) recentLogs.shift();
  logWriter?.appendJson(record);
  if (level === "error") console.error(JSON.stringify(record));
  else console.log(JSON.stringify(record));
}

function jsonResponse(body: unknown, status = 200): Response {
  return new Response(JSON.stringify(body, null, 2), {
    status,
    headers: {
      "content-type": "application/json; charset=utf-8",
      "access-control-allow-origin": "*",
      "access-control-allow-methods": "GET,HEAD,POST,PATCH,PUT,DELETE,OPTIONS",
      "access-control-allow-headers": "content-type,accept",
    },
  });
}

async function readJson(req: Request): Promise<unknown> {
  const text = await req.text();
  if (text.trim().length === 0) return {};
  return JSON.parse(text) as unknown;
}

function asRecord(value: unknown): Record<string, unknown> | null {
  return typeof value === "object" && value !== null && !Array.isArray(value) ? value as Record<string, unknown> : null;
}

function asJsonRecord(value: unknown): JsonRecord {
  return asRecord(value) as JsonRecord | null ?? {};
}

function nowIso(): string {
  return new Date().toISOString();
}

function timestampToIso(value: Date | string | null | undefined): string | null {
  if (value === null || value === undefined) return null;
  const date = value instanceof Date ? value : new Date(value);
  return Number.isNaN(date.getTime()) ? String(value) : date.toISOString();
}

function timestampMs(value: string | Date | null | undefined): number | null {
  if (value === null || value === undefined) return null;
  const date = value instanceof Date ? value : new Date(value);
  const ms = date.getTime();
  return Number.isFinite(ms) ? ms : null;
}

function positiveNumber(value: unknown): number | null {
  const parsed = Number(value);
  return Number.isFinite(parsed) && parsed >= 0 ? Math.floor(parsed) : null;
}

function prefixPreview(value: unknown, maxChars: number): string {
  const text = String(value ?? "");
  return text.length <= maxChars ? text : `${text.slice(0, Math.max(0, maxChars - 1))}…`;
}

function normalizeQueueId(value: unknown, fallback = config.defaultQueueId): string {
  const text = typeof value === "string" ? value.trim() : "";
  if (text.length === 0) return fallback;
  if (!queueIdPattern.test(text)) throw new Error("queueId must match /^[A-Za-z0-9][A-Za-z0-9_.-]{0,63}$/");
  return text;
}

function safeQueueId(value: unknown): string {
  try {
    return normalizeQueueId(value);
  } catch {
    return config.defaultQueueId;
  }
}

function normalizeQueueName(value: unknown, queueId: string): string {
  const fallback = queueId.length > 0 ? queueId : config.defaultQueueId;
  const text = typeof value === "string" ? value.replace(/[\u0000-\u001F\u007F]+/gu, " ").replace(/\s+/gu, " ").trim() : "";
  if (text.length === 0) return fallback;
  if (text.length > 80) throw new Error("queue name must be 80 characters or fewer");
  return text;
}

function normalizeCodeModel(value: string): string {
  const raw = String(value || "").trim();
  if (raw.length === 0) return raw;
  const lower = raw.toLowerCase();
  const leaf = lower.includes("/") ? lower.split("/").at(-1) ?? lower : lower;
  if (leaf === "minimax-m2.7" || leaf === "m2.7") return minimaxM27Model;
  if (leaf === "deepseek" || leaf === "deepseek-chat") return deepseekChatModel;
  return raw;
}

function codeAgentPortForModel(model: string): "codex" | "opencode" {
  const normalized = normalizeCodeModel(model);
  return normalized === minimaxM27Model || normalized === deepseekChatModel ? "opencode" : "codex";
}

function codeAgentPortInfo(kind: "codex" | "opencode"): JsonRecord {
  return {
    kind,
    protocol: kind === "codex" ? "codex-app-server-jsonrpc-stdio" : "opencode-run-json-events",
    sessionResume: kind === "codex" ? "thread/resume" : "opencode --session with persisted XDG storage and stale-session recovery",
    tracePort: kind === "codex" ? "codex-transcript" : "opencode-json-event",
  };
}

function normalizeExecutionMode(value: unknown): CodeExecutionMode {
  const raw = typeof value === "string" ? value.trim().toLowerCase() : "";
  if (raw === "windows-native" || raw === "windows" || raw === "win32" || raw === "native-windows") return "windows-native";
  return "default";
}

function executionModeInfo(mode: CodeExecutionMode): JsonRecord {
  if (mode === "windows-native") {
    return {
      kind: mode,
      label: "Windows native Codex",
      description: "Run Codex on the provider Windows host while the execution container relays stdio.",
      codexRunsInContainer: false,
      requiresProvider: true,
      requiresWindowsCwd: true,
    };
  }
  return {
    kind: mode,
    label: "Default Codex runtime",
    description: "Use the Code Queue node Codex runtime or the provider execution container.",
    codexRunsInContainer: true,
    requiresProvider: false,
    requiresWindowsCwd: false,
  };
}

function resolveReasoningEffort(model: string, explicit?: string | null): string | null {
  const requested = explicit?.trim();
  if (requested) return requested;
  return config.modelReasoningEfforts[model.toLowerCase()] ?? config.defaultReasoningEffort;
}

function normalizeProviderId(value: unknown): string {
  const text = typeof value === "string" ? value.trim() : "";
  return text.length > 0 ? text : config.defaultProviderId;
}

function normalizeWorkdirProviderId(value: unknown): string {
  const text = typeof value === "string" ? value.trim() : "";
  return /^[A-Za-z0-9_.-]{1,64}$/u.test(text) ? text : config.defaultProviderId;
}

function normalizeCwd(providerId: string, value: unknown): string {
  const text = typeof value === "string" && value.trim().length > 0 ? value.trim() : "";
  if (text.length > 0) return text;
  return providerId === config.defaultProviderId ? config.defaultWorkdir : config.remoteDefaultWorkdir;
}

function defaultWorkdirForProvider(providerId: string): string {
  return providerId === config.defaultProviderId ? config.defaultWorkdir : config.remoteDefaultWorkdir;
}

function normalizeWorkdirPath(value: unknown, providerId: string): string {
  const raw = typeof value === "string" ? value.trim() : "";
  if (raw.length === 0) throw new Error("workdir path is required");
  if (raw.length > workdirMaxLength) throw new Error(`workdir path must be ${workdirMaxLength} characters or fewer`);
  if (raw.includes("\u0000")) throw new Error("workdir path contains an invalid character");
  if (raw.startsWith("/")) return raw.replace(/\/+$/u, "") || "/";
  return `${defaultWorkdirForProvider(providerId).replace(/\/+$/u, "")}/${raw}`.replace(/\/+$/u, "") || "/";
}

function defaultWorkdirRecords(timestamp = nowIso()): WorkdirRecord[] {
  const providerId = normalizeWorkdirProviderId(config.defaultProviderId);
  return [{
    providerId,
    executionMode: "default",
    path: normalizeWorkdirPath(config.defaultWorkdir, providerId),
    createdAt: timestamp,
    updatedAt: timestamp,
  }];
}

function workdirRecordKey(record: Pick<WorkdirRecord, "providerId" | "executionMode" | "path">): string {
  return `${record.providerId}\u0000${record.executionMode}\u0000${record.path}`;
}

function mergeWorkdirRecords(records: WorkdirRecord[]): WorkdirRecord[] {
  const byKey = new Map<string, WorkdirRecord>();
  for (const record of records) {
    byKey.set(workdirRecordKey(record), record);
  }
  return Array.from(byKey.values()).sort((left, right) => {
    const providerDelta = left.providerId.localeCompare(right.providerId);
    if (providerDelta !== 0) return providerDelta;
    const modeDelta = left.executionMode.localeCompare(right.executionMode);
    if (modeDelta !== 0) return modeDelta;
    return left.path.localeCompare(right.path);
  });
}

async function loadWorkdirRecords(): Promise<WorkdirRecord[]> {
  const rows = await mgrSql<Array<{ provider_id: string; execution_mode: string; path: string; created_at: Date | string; updated_at: Date | string }>>`
    SELECT provider_id, execution_mode, path, created_at, updated_at
    FROM unidesk_code_queue_workdirs
    ORDER BY provider_id ASC, execution_mode ASC, path ASC
  `;
  const records = rows.map((row) => {
    const providerId = normalizeWorkdirProviderId(row.provider_id);
    return {
      providerId,
      executionMode: normalizeExecutionMode(row.execution_mode),
      path: normalizeWorkdirPath(row.path, providerId),
      createdAt: timestampToIso(row.created_at) ?? nowIso(),
      updatedAt: timestampToIso(row.updated_at) ?? nowIso(),
    };
  });
  return mergeWorkdirRecords([...defaultWorkdirRecords(), ...records]);
}

async function upsertWorkdir(record: WorkdirRecord): Promise<void> {
  await mgrSql`
    INSERT INTO unidesk_code_queue_workdirs (provider_id, execution_mode, path, created_at, updated_at)
    VALUES (${record.providerId}, ${record.executionMode}, ${record.path}, ${record.createdAt}, ${record.updatedAt})
    ON CONFLICT (provider_id, execution_mode, path) DO UPDATE SET
      updated_at = EXCLUDED.updated_at
  `;
}

function filteredWorkdirRecords(records: WorkdirRecord[], url: URL): WorkdirRecord[] {
  const providerIdParam = url.searchParams.get("providerId");
  const executionModeParam = url.searchParams.get("executionMode");
  const providerId = providerIdParam === null || providerIdParam.trim().length === 0 ? null : normalizeWorkdirProviderId(providerIdParam);
  const executionMode = executionModeParam === null || executionModeParam.trim().length === 0 ? null : normalizeExecutionMode(executionModeParam);
  return records.filter((record) => {
    if (providerId !== null && record.providerId !== providerId) return false;
    if (executionMode !== null && record.executionMode !== executionMode) return false;
    return true;
  });
}

async function listWorkdirs(url: URL): Promise<Response> {
  const records = await loadWorkdirRecords();
  return jsonResponse({
    ok: true,
    workdirs: filteredWorkdirRecords(records, url),
    defaultProviderId: config.defaultProviderId,
    defaultWorkdir: config.defaultWorkdir,
    remoteDefaultWorkdir: config.remoteDefaultWorkdir,
    source: "code-queue-mgr-postgres",
  });
}

async function createWorkdir(req: Request): Promise<Response> {
  const body = asRecord(await readJson(req)) ?? {};
  const providerId = normalizeWorkdirProviderId(body.providerId);
  const executionMode = normalizeExecutionMode(body.executionMode);
  const path = normalizeWorkdirPath(body.path ?? body.cwd ?? body.workdir, providerId);
  const now = nowIso();
  const workdir: WorkdirRecord = { providerId, executionMode, path, createdAt: now, updatedAt: now };
  await upsertWorkdir(workdir);
  return jsonResponse({
    ok: true,
    workdir,
    workdirs: await loadWorkdirRecords(),
    ensure: { ok: true, skipped: true, reason: "code-queue-mgr records workdir menu options only; provider directories are created by the execution plane" },
  }, 201);
}

async function deleteWorkdir(providerIdValue: string, executionModeValue: string, pathValue: string): Promise<Response> {
  const providerId = normalizeWorkdirProviderId(providerIdValue);
  const executionMode = normalizeExecutionMode(executionModeValue);
  const path = normalizeWorkdirPath(pathValue, providerId);
  const rows = await mgrSql<WorkdirRecord[]>`
    DELETE FROM unidesk_code_queue_workdirs
    WHERE provider_id = ${providerId}
      AND execution_mode = ${executionMode}
      AND path = ${path}
    RETURNING provider_id AS "providerId", execution_mode AS "executionMode", path, created_at AS "createdAt", updated_at AS "updatedAt"
  `;
  if (rows.length === 0) return jsonResponse({ ok: false, error: "workdir not found" }, 404);
  return jsonResponse({ ok: true, deleted: rows[0], workdirs: await loadWorkdirRecords() });
}

function normalizeStringArray(value: unknown): string[] {
  if (!Array.isArray(value)) return [];
  return Array.from(new Set(value.map((item) => typeof item === "string" ? item.trim() : "").filter(Boolean)));
}

function normalizePromptText(value: unknown): string {
  if (typeof value !== "string" || value.trim().length === 0) throw new Error("prompt is required");
  return value;
}

function stripAutoReferenceHint(prompt: string): string {
  const trimmed = prompt.trimStart();
  if (!/^引用\s+Code Queue\s+任务\s+codex_\d+_[A-Za-z0-9_-]+/u.test(trimmed)) return prompt;
  const marker = "\n\n本次任务：";
  const index = trimmed.indexOf(marker);
  if (index === -1) return prompt;
  return trimmed.slice(index + marker.length).trimStart();
}

function stripResolvedReferenceContext(prompt: string): string {
  const trimmed = prompt.trimStart();
  if (!trimmed.startsWith(resolvedReferenceContextTitle)) return prompt;
  const offset = prompt.length - trimmed.length;
  const index = prompt.lastIndexOf(currentTaskPromptMarker);
  if (index < offset) return prompt;
  return prompt.slice(index + currentTaskPromptMarker.length).trimStart();
}

function stripCodeQueueEnvironmentHint(prompt: string): string {
  const trimmed = prompt.trimStart();
  if (!trimmed.startsWith(codeQueueEnvironmentHintTitle)) return prompt;
  const offset = prompt.length - trimmed.length;
  const index = prompt.indexOf(currentTaskPromptMarker, offset);
  if (index < offset) return prompt;
  return prompt.slice(index + currentTaskPromptMarker.length).trimStart();
}

function userPromptForDisplay(prompt: string): string {
  return stripAutoReferenceHint(stripResolvedReferenceContext(stripCodeQueueEnvironmentHint(prompt)));
}

function promptWithCodeQueueEnvironmentHint(prompt: string): string {
  if (prompt.trimStart().startsWith(codeQueueEnvironmentHintTitle)) return prompt;
  return [codeQueueEnvironmentHint, "", "# 本次任务", prompt.trim()].join("\n");
}

function isCodexTaskId(value: string): boolean {
  return codexTaskIdPattern.test(value.trim());
}

function addUniqueTaskId(ids: string[], value: string): void {
  const id = value.trim();
  if (isCodexTaskId(id) && !ids.includes(id)) ids.push(id);
}

function collectTaskIdsFromValue(value: unknown, ids: string[]): void {
  if (typeof value === "string") {
    for (const part of value.split(/[\s,，;；]+/u)) addUniqueTaskId(ids, part);
    return;
  }
  if (Array.isArray(value)) {
    for (const item of value) collectTaskIdsFromValue(item, ids);
  }
}

function referenceTaskIdsFromPrompt(prompt: string): string[] {
  const ids: string[] = [];
  const patterns = [
    /引用\s+Code Queue\s+任务\s+(codex_\d+_[A-Za-z0-9_-]+)/giu,
    /\bcodex\s+task\s+(codex_\d+_[A-Za-z0-9_-]+)/giu,
    /(?:引用|上下文|context|reference)[^\n]{0,160}\b(codex_\d+_[A-Za-z0-9_-]+)/giu,
  ];
  for (const pattern of patterns) {
    for (const match of prompt.matchAll(pattern)) addUniqueTaskId(ids, String(match[1] ?? ""));
  }
  return ids;
}

function collectReferenceTaskIds(record: Record<string, unknown>, prompt: string, fallback: string[] = []): string[] {
  const ids: string[] = [];
  const hasExplicitReferenceIds = Object.prototype.hasOwnProperty.call(record, "referenceTaskId")
    || Object.prototype.hasOwnProperty.call(record, "referenceTaskIds");
  if (hasExplicitReferenceIds) {
    collectTaskIdsFromValue(record.referenceTaskId, ids);
    collectTaskIdsFromValue(record.referenceTaskIds, ids);
  } else {
    for (const id of fallback) addUniqueTaskId(ids, id);
  }
  for (const id of referenceTaskIdsFromPrompt(prompt)) addUniqueTaskId(ids, id);
  return ids;
}

function makeTaskId(): string {
  return `codex_${Date.now()}_${Math.random().toString(16).slice(2, 8)}`;
}

function terminalTask(task: QueueTask): boolean {
  return task.status === "succeeded" || task.status === "failed" || task.status === "canceled";
}

function hasClaimMarkers(task: QueueTask): boolean {
  return task.startedAt !== null
    || task.currentAttempt > 0
    || task.codexThreadId !== null
    || task.activeTurnId !== null
    || task.attempts.length > 0;
}

function retryableClaimedQueueState(task: QueueTask): boolean {
  return (task.status === "queued" || task.status === "retry_wait")
    && task.activeTurnId === null
    && hasClaimMarkers(task);
}

function terminalTaskUnread(task: QueueTask): boolean {
  return terminalTask(task) && task.readAt === null;
}

function queuedTaskPromptEditable(task: QueueTask): boolean {
  return task.status === "queued"
    && task.startedAt === null
    && task.currentAttempt === 0
    && task.codexThreadId === null
    && task.nextMode === null
    && task.nextPrompt === null
    && task.attempts.length === 0;
}

function numberField(value: unknown, fallback = 0): number {
  const parsed = Number(value);
  return Number.isFinite(parsed) ? Math.floor(parsed) : fallback;
}

function outputMaxSeq(task: QueueTask): number {
  const outputSeq = task.output.map((item) => Number(item.seq)).filter(Number.isFinite);
  const promptSeq = task.promptHistory.map((item) => numberField(asRecord(item)?.seq, 0));
  const attemptSeq = task.attempts.map((item) => numberField(item.outputEndSeq, 0));
  return Math.max(0, numberField(task.outputMaxSeq, 0), ...outputSeq, ...promptSeq, ...attemptSeq);
}

function activeTask(task: QueueTask): boolean {
  return task.status === "running" || task.status === "judging";
}

function visibleTraceOutputs(task: QueueTask): LiveOutput[] {
  return task.output.filter((item) => item.channel !== "system" || item.method !== "enqueue");
}

function rawTraceStats(task: QueueTask): JsonRecord {
  const steps = visibleTraceOutputs(task);
  const count = (channel: string) => steps.filter((item) => item.channel === channel).length;
  const stepCount = positiveNumber(task.stepCount ?? task.llmStepCount) ?? steps.length;
  return {
    stepCount,
    llmStepCount: positiveNumber(task.llmStepCount ?? task.stepCount) ?? stepCount,
    traceLineCount: steps.length,
    outputMaxSeq: outputMaxSeq(task),
    readCount: count("tool"),
    editCount: count("diff"),
    runCount: count("command"),
    errorCount: count("error"),
  };
}

function traceStatsFallbackPatch(task: QueueTask): JsonRecord {
  const fallback = rawTraceStats(task);
  const rawTracePresent = Number(fallback.stepCount ?? 0) > 0 || Number(fallback.traceLineCount ?? 0) > 0 || Number(fallback.outputMaxSeq ?? 0) > 0;
  return {
    ...fallback,
    traceStats: null,
    statsSource: rawTracePresent ? "raw-trace-fallback" : "raw-trace-empty",
    traceStatsState: rawTracePresent ? "degraded" : "empty",
    traceStatsReason: rawTracePresent ? "oa-event-flow-stats-unavailable-retained-trace-present" : "no-trace-steps-yet",
    statsUnavailable: true,
    statsSyncing: rawTracePresent,
    rawTraceStepCount: fallback.stepCount,
  };
}

function schedulerHeartbeat(task: QueueTask): JsonRecord | null {
  const heartbeat = asRecord(task.schedulerHeartbeat);
  return heartbeat !== null && heartbeat.taskId === task.id ? heartbeat as JsonRecord : null;
}

function heartbeatTimestamp(heartbeat: JsonRecord | null, key: string): string | null {
  const value = heartbeat?.[key];
  return typeof value === "string" && timestampMs(value) !== null ? value : null;
}

function maxTimestamp(values: Array<string | null | undefined>): string | null {
  let best: string | null = null;
  let bestMs = -Infinity;
  for (const value of values) {
    const ms = timestampMs(value ?? null);
    if (ms === null || ms < bestMs) continue;
    best = value ?? null;
    bestMs = ms;
  }
  return best;
}

function heartbeatFresh(heartbeat: JsonRecord | null, nowMs: number): boolean {
  const atMs = timestampMs(heartbeatTimestamp(heartbeat, "lastLocalHeartbeatAt"));
  return atMs !== null && nowMs - atMs < schedulerHeartbeatStaleMs;
}

function traceGap(task: QueueTask, heartbeat: JsonRecord | null, nowMs: number): boolean {
  const traceAt = timestampMs(heartbeatTimestamp(heartbeat, "lastPersistedTraceAt"));
  return traceAt !== null && nowMs - traceAt >= schedulerHeartbeatStaleMs && outputMaxSeq(task) > 0;
}

function controlPlaneExecutionDiagnostics(tasks: QueueTask[], now = nowIso()): JsonRecord {
  const nowMs = timestampMs(now) ?? Date.now();
  const databaseActiveTaskIds = tasks.filter(activeTask).map((task) => task.id).sort();
  const heartbeatRows = tasks.map((task) => ({ task, heartbeat: schedulerHeartbeat(task) })).filter((row): row is { task: QueueTask; heartbeat: JsonRecord } => row.heartbeat !== null);
  const activeHeartbeatTaskIds = heartbeatRows.map((row) => String(row.heartbeat.taskId ?? row.task.id)).sort();
  const heartbeatFreshTaskIds = heartbeatRows.filter((row) => heartbeatFresh(row.heartbeat, nowMs)).map((row) => String(row.heartbeat.taskId ?? row.task.id)).sort();
  const heartbeatFreshSet = new Set(heartbeatFreshTaskIds);
  const heartbeatExpiredTaskIds = heartbeatRows.filter((row) => activeTask(row.task) && !heartbeatFreshSet.has(row.task.id)).map((row) => row.task.id).sort();
  const activeHeartbeatSet = new Set(activeHeartbeatTaskIds);
  const heartbeatMissingTaskIds = databaseActiveTaskIds.filter((taskId) => !activeHeartbeatSet.has(taskId));
  const staleRecoveryCandidateTaskIds = heartbeatExpiredTaskIds.slice();
  const traceGapTaskIds = heartbeatRows.filter((row) => traceGap(row.task, row.heartbeat, nowMs)).map((row) => row.task.id).sort();
  const traceGapNotStaleTaskIds = traceGapTaskIds.filter((taskId) => heartbeatFreshSet.has(taskId));
  const heartbeatRiskTaskIds = Array.from(new Set([
    ...heartbeatExpiredTaskIds,
    ...heartbeatMissingTaskIds,
    ...staleRecoveryCandidateTaskIds,
  ])).sort();
  const splitBrain = databaseActiveTaskIds.length > 0 && heartbeatFreshTaskIds.length > 0;
  const splitBrainLive = splitBrain && heartbeatRiskTaskIds.length === 0;
  const staleActive = staleRecoveryCandidateTaskIds.length > 0;
  const degraded = splitBrain
    || staleActive
    || heartbeatMissingTaskIds.length > 0
    || heartbeatExpiredTaskIds.length > 0
    || traceGapTaskIds.length > 0;
  const state = splitBrain ? "split-brain" : staleActive ? "stale-active" : degraded ? "degraded" : "healthy";
  const effectiveLiveness = heartbeatRiskTaskIds.length > 0 ? "at-risk" : splitBrainLive ? "live" : degraded ? "degraded" : "healthy";
  const recommendedAction = heartbeatRiskTaskIds.length > 0 ? "investigate-heartbeat-risk" : splitBrainLive ? "continue-supervision" : degraded ? "observe-degraded" : "none";
  const livenessSummary = heartbeatRiskTaskIds.length > 0
    ? "Heartbeat is expired, missing, or stale-recovery eligible for at least one database-active task; investigate before assuming the task is still live."
    : splitBrainLive
      ? "PostgreSQL and the local control-plane view are split, but scheduler-owned heartbeat is fresh; treat the task as live and continue supervision."
      : degraded
        ? "Execution diagnostics are degraded; inspect the listed reasons together with heartbeat and trace progress."
        : "Execution diagnostics are healthy.";
  const reasons: string[] = [];
  if (splitBrain) reasons.push("postgres control-plane has database-active tasks while its local active slots are empty, but scheduler heartbeat is fresh");
  if (staleActive) reasons.push("owner heartbeat is expired and scheduler has no local active run for at least one database-active task");
  if (heartbeatMissingTaskIds.length > 0) reasons.push("database-active tasks are missing scheduler-owned heartbeat");
  if (traceGapNotStaleTaskIds.length > 0) reasons.push("trace progress is stale while scheduler heartbeat is fresh; this is a trace gap, not stale active");
  return {
    state,
    health: state,
    degraded,
    splitBrain,
    splitBrainLive,
    effectiveLiveness,
    recommendedAction,
    livenessSummary,
    executionStateSource: "postgres-control-plane",
    controlPlane: "master-code-queue-mgr",
    databaseActiveTaskIds,
    databaseActiveTaskCount: databaseActiveTaskIds.length,
    schedulerActiveTaskIds: [],
    schedulerActiveTaskCount: 0,
    schedulerActiveRunSlotCount: 0,
    schedulerActiveQueueIds: [],
    schedulerProcessingQueueIds: [],
    schedulerOrphanedActiveTaskIds: databaseActiveTaskIds,
    schedulerOrphanedActiveTaskCount: databaseActiveTaskIds.length,
    activeHeartbeatTaskIds,
    activeHeartbeatCount: activeHeartbeatTaskIds.length,
    heartbeatFreshTaskIds,
    heartbeatExpiredTaskIds,
    heartbeatMissingTaskIds,
    staleRecoveryCandidateTaskIds,
    heartbeatRiskTaskIds,
    traceGapTaskIds,
    traceGapNotStaleTaskIds,
    schedulerHeartbeatStaleMs,
    now,
    lastSchedulerHeartbeatAt: maxTimestamp(heartbeatRows.map((row) => heartbeatTimestamp(row.heartbeat, "lastLocalHeartbeatAt"))),
    lastObservedAgentEventAt: maxTimestamp(heartbeatRows.map((row) => heartbeatTimestamp(row.heartbeat, "lastObservedAgentEventAt"))),
    lastPersistedTraceAt: maxTimestamp(heartbeatRows.map((row) => heartbeatTimestamp(row.heartbeat, "lastPersistedTraceAt"))),
    oaPublisher: null,
    reasons,
    guidance: [
      "Do not use master activeRunSlotCount=0 alone to declare a task stuck.",
      "Check PostgreSQL task status, scheduler active runs/slots, scheduler heartbeat, and trace/OA publisher progress together.",
      "Trace gap with a fresh scheduler heartbeat must not trigger stale retry.",
      "Stale recovery is allowed only when the scheduler has no local active run and the owner heartbeat is expired.",
    ],
  };
}

function normalizeOutput(value: unknown): LiveOutput[] {
  if (!Array.isArray(value)) return [];
  return value.flatMap((item): LiveOutput[] => {
    const record = asRecord(item);
    if (record === null) return [];
    const seq = numberField(record.seq, 0);
    if (seq <= 0) return [];
    const channel = String(record.channel || "system") as OutputChannel;
    return [{
      seq,
      at: timestampToIso(typeof record.at === "string" ? record.at : null) ?? nowIso(),
      channel,
      text: String(record.text ?? ""),
      ...(typeof record.method === "string" ? { method: record.method } : {}),
      ...(typeof record.itemId === "string" ? { itemId: record.itemId } : {}),
    }];
  }).sort((left, right) => left.seq - right.seq);
}

function stringList(value: unknown): string[] {
  if (Array.isArray(value)) return value.map((item) => String(item ?? "")).filter((item) => item.trim().length > 0);
  if (typeof value === "string" && value.trim().length > 0) return [value];
  return [];
}

function numberList(value: unknown, fallback: number): number[] {
  const values = Array.isArray(value) ? value : [];
  const numbers = values.map((item) => Number(item)).filter(Number.isFinite).map((item) => Math.floor(item));
  if (numbers.length === 0 && Number.isFinite(fallback)) numbers.push(Math.floor(fallback));
  return Array.from(new Set(numbers)).sort((left, right) => left - right);
}

function normalizeTask(value: unknown): QueueTask {
  const record = asRecord(value) ?? {};
  const status = String(record.status || "queued") as TaskStatus;
  const id = typeof record.id === "string" && record.id.length > 0 ? record.id : makeTaskId();
  const queueId = safeQueueId(record.queueId);
  const createdAt = timestampToIso(typeof record.createdAt === "string" ? record.createdAt : null) ?? nowIso();
  const updatedAt = timestampToIso(typeof record.updatedAt === "string" ? record.updatedAt : null) ?? createdAt;
  const model = normalizeCodeModel(typeof record.model === "string" && record.model.length > 0 ? record.model : config.defaultModel);
  const providerId = normalizeProviderId(record.providerId);
  const executionMode = normalizeExecutionMode(record.executionMode);
  const prompt = typeof record.prompt === "string" ? record.prompt : "";
  const basePrompt = typeof record.basePrompt === "string" && record.basePrompt.length > 0 ? record.basePrompt : userPromptForDisplay(prompt);
  const task: QueueTask = {
    id,
    queueId,
    queueEnteredAt: timestampToIso(typeof record.queueEnteredAt === "string" ? record.queueEnteredAt : null) ?? createdAt,
    prompt,
    basePrompt,
    referenceTaskIds: normalizeStringArray(record.referenceTaskIds),
    referenceInjection: toJsonValue(record.referenceInjection ?? null),
    providerId,
    cwd: normalizeCwd(providerId, record.cwd),
    model,
    reasoningEffort: typeof record.reasoningEffort === "string" ? record.reasoningEffort : resolveReasoningEffort(model),
    executionMode,
    maxAttempts: clampAttempts(numberField(record.maxAttempts, config.defaultMaxAttempts)),
    status,
    createdAt,
    updatedAt,
    startedAt: timestampToIso(typeof record.startedAt === "string" ? record.startedAt : null),
    finishedAt: terminalTask({ status } as QueueTask) ? timestampToIso(typeof record.finishedAt === "string" ? record.finishedAt : null) : null,
    readAt: terminalTask({ status } as QueueTask) ? timestampToIso(typeof record.readAt === "string" ? record.readAt : null) : null,
    currentAttempt: numberField(record.currentAttempt, 0),
    currentMode: record.currentMode === "initial" || record.currentMode === "retry" ? record.currentMode : null,
    codexThreadId: typeof record.codexThreadId === "string" ? record.codexThreadId : null,
    activeTurnId: typeof record.activeTurnId === "string" ? record.activeTurnId : null,
    schedulerHeartbeat: asRecord(record.schedulerHeartbeat) as JsonRecord | null,
    finalResponse: typeof record.finalResponse === "string" ? record.finalResponse : "",
    lastError: typeof record.lastError === "string" ? record.lastError : null,
    lastJudge: toJsonValue(record.lastJudge ?? null),
    judgeFailCount: numberField(record.judgeFailCount, 0),
    promptHistory: Array.isArray(record.promptHistory) ? record.promptHistory.map(toJsonValue) : [],
    output: normalizeOutput(record.output),
    events: Array.isArray(record.events) ? record.events.map(toJsonValue) : [],
    attempts: Array.isArray(record.attempts) ? record.attempts.map((item, index) => normalizeAttempt(item, index + 1)) : [],
    cancelRequested: record.cancelRequested === true,
    nextPrompt: typeof record.nextPrompt === "string" ? record.nextPrompt : null,
    nextMode: record.nextMode === "initial" || record.nextMode === "retry" ? record.nextMode : null,
  };
  if (task.status !== "running" && task.status !== "judging") task.activeTurnId = null;
  task.stepCount = numberField(record.stepCount ?? record.llmStepCount, task.output.length);
  task.llmStepCount = task.stepCount;
  task.outputMaxSeq = Math.max(outputMaxSeq(task), numberField(record.outputMaxSeq, 0));
  return task;
}

function normalizeAttempt(value: unknown, fallbackIndex: number): AttemptSummary {
  const record = asRecord(value) ?? {};
  const finalResponse = typeof record.finalResponse === "string" ? record.finalResponse : "";
  return {
    index: numberField(record.index, fallbackIndex),
    mode: record.mode === "retry" ? "retry" : "initial",
    startedAt: timestampToIso(typeof record.startedAt === "string" ? record.startedAt : null) ?? nowIso(),
    finishedAt: timestampToIso(typeof record.finishedAt === "string" ? record.finishedAt : null) ?? nowIso(),
    providerId: typeof record.providerId === "string" ? record.providerId : undefined,
    executionMode: normalizeExecutionMode(record.executionMode),
    terminalStatus: record.terminalStatus === "completed" || record.terminalStatus === "interrupted" || record.terminalStatus === "failed" ? record.terminalStatus : null,
    transportClosedBeforeTerminal: record.transportClosedBeforeTerminal === true,
    appServerExitCode: typeof record.appServerExitCode === "number" ? record.appServerExitCode : null,
    appServerSignal: typeof record.appServerSignal === "string" ? record.appServerSignal : null,
    error: typeof record.error === "string" ? record.error : null,
    events: Array.isArray(record.events) ? record.events.map(toJsonValue) : undefined,
    inputPrompt: typeof record.inputPrompt === "string" ? record.inputPrompt : undefined,
    inputPromptPreview: typeof record.inputPromptPreview === "string" ? record.inputPromptPreview : undefined,
    inputPromptChars: typeof record.inputPromptChars === "number" ? record.inputPromptChars : undefined,
    inputPromptLines: typeof record.inputPromptLines === "number" ? record.inputPromptLines : undefined,
    finalResponse,
    finalResponsePreview: typeof record.finalResponsePreview === "string" ? record.finalResponsePreview : safePreview(finalResponse, 1200),
    finalResponseChars: typeof record.finalResponseChars === "number" ? record.finalResponseChars : finalResponse.length,
    judge: toJsonValue(record.judge ?? null),
    judgeAt: typeof record.judgeAt === "string" ? record.judgeAt : null,
    judgeSeq: typeof record.judgeSeq === "number" ? record.judgeSeq : null,
    feedbackPrompt: typeof record.feedbackPrompt === "string" ? record.feedbackPrompt : undefined,
    feedbackPromptPreview: typeof record.feedbackPromptPreview === "string" ? record.feedbackPromptPreview : undefined,
    feedbackPromptChars: typeof record.feedbackPromptChars === "number" ? record.feedbackPromptChars : undefined,
    feedbackPromptLines: typeof record.feedbackPromptLines === "number" ? record.feedbackPromptLines : undefined,
    feedbackPromptSource: typeof record.feedbackPromptSource === "string" ? record.feedbackPromptSource : undefined,
    feedbackPromptForAttempt: typeof record.feedbackPromptForAttempt === "number" ? record.feedbackPromptForAttempt : null,
    stderrTail: typeof record.stderrTail === "string" ? record.stderrTail : "",
    outputStartSeq: typeof record.outputStartSeq === "number" ? record.outputStartSeq : null,
    outputEndSeq: typeof record.outputEndSeq === "number" ? record.outputEndSeq : null,
    errorCount: typeof record.errorCount === "number" ? record.errorCount : undefined,
  };
}

function taskReferenceIds(task: QueueTask): string[] {
  const ids: string[] = [];
  for (const id of task.referenceTaskIds ?? []) addUniqueTaskId(ids, id);
  for (const id of referenceTaskIdsFromPrompt(task.basePrompt || userPromptForDisplay(task.prompt))) addUniqueTaskId(ids, id);
  return ids;
}

function taskBasePrompt(task: QueueTask): string {
  return (task.basePrompt || userPromptForDisplay(task.prompt)).trimEnd();
}

function referenceSummaryItem(task: QueueTask, round: number, roundIndex: number, viaTaskId: string | null): ReferenceInjectionSummaryItem {
  const lastMessage = lastAssistantMessage(task);
  const lastText = typeof lastMessage.text === "string" ? lastMessage.text : "";
  return {
    round,
    roundIndex,
    taskId: task.id,
    viaTaskId,
    status: task.status,
    providerId: task.providerId,
    executionMode: task.executionMode,
    model: task.model,
    cwd: task.cwd,
    createdAt: task.createdAt,
    updatedAt: task.updatedAt,
    promptChars: taskBasePrompt(task).length,
    finalResponseChars: lastText.trimEnd().length,
    finalResponseAt: typeof lastMessage.at === "string" ? lastMessage.at : null,
    finalResponseSource: typeof lastMessage.source === "string" ? lastMessage.source : "unknown",
    referenceTaskIds: taskReferenceIds(task),
    cliHint: `bun scripts/cli.ts codex task ${task.id}`,
  };
}

function cachedTaskFinder(finder: (id: string) => Promise<QueueTask | null>): (id: string) => Promise<QueueTask | null> {
  const cache = new Map<string, Promise<QueueTask | null>>();
  return (id: string) => {
    const existing = cache.get(id);
    if (existing !== undefined) return existing;
    const next = finder(id);
    cache.set(id, next);
    return next;
  };
}

async function collectReferenceGraph(rootIds: string[], finder: (id: string) => Promise<QueueTask | null>): Promise<{ items: ReferenceInjectionSummaryItem[]; tasks: QueueTask[]; truncated: boolean }> {
  const seen = new Set<string>();
  let frontier = rootIds.map((id) => ({ id, viaTaskId: null as string | null }));
  const rawItems: Array<{ task: QueueTask; depth: number; viaTaskId: string | null; discoveryIndex: number }> = [];
  let truncated = false;
  let discoveryIndex = 0;
  for (let depth = 1; frontier.length > 0; depth += 1) {
    if (referenceInjectionMaxRounds !== null && depth > referenceInjectionMaxRounds) {
      truncated = frontier.some((entry) => !seen.has(entry.id));
      break;
    }
    const next: Array<{ id: string; viaTaskId: string | null }> = [];
    for (const entry of frontier) {
      if (seen.has(entry.id)) continue;
      const task = await finder(entry.id);
      if (task === null) continue;
      seen.add(entry.id);
      discoveryIndex += 1;
      rawItems.push({ task, depth, viaTaskId: entry.viaTaskId, discoveryIndex });
      for (const childId of taskReferenceIds(task)) {
        if (!seen.has(childId)) next.push({ id: childId, viaTaskId: task.id });
      }
    }
    frontier = next;
  }
  if (frontier.some((entry) => !seen.has(entry.id))) truncated = true;
  const sorted = rawItems.sort((left, right) => {
    if (left.depth !== right.depth) return right.depth - left.depth;
    const createdDelta = Date.parse(left.task.createdAt) - Date.parse(right.task.createdAt);
    if (Number.isFinite(createdDelta) && createdDelta !== 0) return createdDelta;
    return left.discoveryIndex - right.discoveryIndex;
  });
  const depthToRound = new Map<number, number>();
  Array.from(new Set(sorted.map((item) => item.depth))).forEach((depth, index) => depthToRound.set(depth, index + 1));
  const roundCounts = new Map<number, number>();
  const items = sorted.map((item) => {
    const round = depthToRound.get(item.depth) ?? item.depth;
    const roundIndex = (roundCounts.get(round) ?? 0) + 1;
    roundCounts.set(round, roundIndex);
    return referenceSummaryItem(item.task, round, roundIndex, item.viaTaskId);
  });
  return { items, tasks: sorted.map((item) => item.task), truncated };
}

function referenceRoundSeparator(round: number, totalRounds: number, items: ReferenceInjectionSummaryItem[]): string {
  const createdTimes = items.map((item) => item.createdAt).filter(Boolean).sort();
  const updatedTimes = items.map((item) => item.updatedAt).filter(Boolean).sort();
  return [
    `----- Reference Round ${round}/${totalRounds} -----`,
    "order: upstream/oldest context first; direct references appear in the last round",
    `tasks: ${items.length}`,
    `createdRange: ${createdTimes[0] ?? "--"} -> ${createdTimes.at(-1) ?? "--"}`,
    `updatedRange: ${updatedTimes[0] ?? "--"} -> ${updatedTimes.at(-1) ?? "--"}`,
    "--------------------------------",
  ].join("\n");
}

function referencedTaskContext(task: QueueTask, summary: ReferenceInjectionSummaryItem): string {
  const lastMessage = lastAssistantMessage(task);
  const lastMessageText = typeof lastMessage.text === "string" ? lastMessage.text : "";
  return [
    `## Round ${summary.round}.${summary.roundIndex} referenced task ${task.id}`,
    `- via: ${summary.viaTaskId ?? "direct"}`,
    `- status/provider/model/cwd: ${task.status} / ${task.providerId} / ${task.model} / ${task.cwd}`,
    `- created/updated: ${task.createdAt} / ${task.updatedAt}`,
    `- cli: bun scripts/cli.ts codex task ${task.id}`,
    "",
    "### Initial prompt",
    taskBasePrompt(task) || "(empty)",
    "",
    "### Final/last response",
    lastMessageText.trimEnd() || "(none yet)",
    "",
    "### Query more context",
    `Run: bun scripts/cli.ts codex task ${task.id}`,
  ].join("\n");
}

async function injectReferencedTaskContext(prompt: string, basePrompt: string, referenceTaskIds: string[]): Promise<PreparedPrompt> {
  if (referenceTaskIds.length === 0 || prompt.includes(resolvedReferenceContextTitle)) {
    return { prompt, basePrompt, referenceTaskIds, referenceInjection: null };
  }
  if (referenceTaskIds.length > 5) throw new Error(`referenceTaskIds supports at most 5 task ids, got ${referenceTaskIds.length}`);
  const finder = cachedTaskFinder(loadTask);
  const referencedTasks = await Promise.all(referenceTaskIds.map((id) => finder(id)));
  const missing = referenceTaskIds.filter((_id, index) => referencedTasks[index] === null);
  if (missing.length > 0) throw new Error(`referenced Code Queue task not found: ${missing.join(", ")}`);
  const injectedAt = nowIso();
  const graph = await collectReferenceGraph(referenceTaskIds, finder);
  const injection: ReferenceInjectionRecord = {
    version: 2,
    injectedAt,
    basePrompt,
    directReferenceTaskIds: referenceTaskIds,
    maxRounds: referenceInjectionMaxRounds,
    truncated: graph.truncated,
    itemCount: graph.items.length,
    items: graph.items,
  };
  const taskById = new Map(graph.tasks.map((task) => [task.id, task]));
  const groupedItems = Array.from(new Set(graph.items.map((item) => item.round))).map((round) => ({
    round,
    items: graph.items.filter((item) => item.round === round),
  }));
  const context = [
    resolvedReferenceContextTitle,
    `injectedAt: ${injectedAt}`,
    `directReferences: ${referenceTaskIds.join(", ")}`,
    `referenceGraphItems: ${graph.items.length}${graph.truncated ? " (truncated)" : ""}`,
    "说明：Code Queue 后端只读取每个被引用任务的结构化 basePrompt（注入前 prompt）和 final/last response；不会把历史引用注入块继续套入。多轮引用按上游/最早上下文在前、直接引用在后的顺序注入；中间执行过程不注入，只保留 CLI 查询提示。",
    "",
    ...(groupedItems.flatMap((group) => [
      referenceRoundSeparator(group.round, groupedItems.length, group.items),
      "",
      ...group.items.map((item) => {
        const task = taskById.get(item.taskId);
        return task === undefined ? "" : referencedTaskContext(task, item);
      }).filter((text) => text.length > 0),
      "",
    ])),
    "",
    "# 本次任务",
    basePrompt.trim(),
  ].join("\n");
  return { prompt: context, basePrompt, referenceTaskIds, referenceInjection: toJsonValue(injection) };
}

async function preparePromptFromRecord(record: Record<string, unknown>, fallbackReferenceTaskIds: string[] = []): Promise<PreparedPrompt> {
  const rawPrompt = normalizePromptText(record.prompt);
  const basePrompt = typeof record.basePrompt === "string" && record.basePrompt.length > 0 ? record.basePrompt : userPromptForDisplay(rawPrompt);
  const referenceTaskIds = collectReferenceTaskIds(record, rawPrompt, fallbackReferenceTaskIds);
  const injected = await injectReferencedTaskContext(rawPrompt, basePrompt, referenceTaskIds);
  return {
    prompt: promptWithCodeQueueEnvironmentHint(injected.prompt),
    basePrompt: injected.basePrompt,
    referenceTaskIds: injected.referenceTaskIds,
    referenceInjection: injected.referenceInjection ?? toJsonValue(record.referenceInjection ?? null),
  };
}

async function createTaskFromRequest(value: unknown): Promise<QueueTask> {
  const record = asRecord(value);
  if (record === null) throw new Error("request body must be an object");
  const at = nowIso();
  const prepared = await preparePromptFromRecord(record);
  const providerId = normalizeProviderId(record.providerId);
  const model = normalizeCodeModel(typeof record.model === "string" && record.model.length > 0 ? record.model : config.defaultModel);
  const executionMode = normalizeExecutionMode(record.executionMode);
  const output: LiveOutput[] = [{ seq: 1, at, channel: "user", text: `${prepared.prompt}\n`, method: "enqueue" }];
  return {
    id: makeTaskId(),
    queueId: normalizeQueueId(record.queueId),
    queueEnteredAt: at,
    prompt: prepared.prompt,
    basePrompt: prepared.basePrompt,
    referenceTaskIds: prepared.referenceTaskIds,
    referenceInjection: prepared.referenceInjection,
    providerId,
    cwd: normalizeCwd(providerId, record.cwd),
    model,
    reasoningEffort: typeof record.reasoningEffort === "string" ? record.reasoningEffort : resolveReasoningEffort(model),
    executionMode,
    maxAttempts: clampAttempts(numberField(record.maxAttempts, config.defaultMaxAttempts)),
    status: "queued",
    createdAt: at,
    updatedAt: at,
    startedAt: null,
    finishedAt: null,
    readAt: null,
    currentAttempt: 0,
    currentMode: null,
    codexThreadId: null,
    activeTurnId: null,
    schedulerHeartbeat: null,
    finalResponse: "",
    stepCount: 0,
    llmStepCount: 0,
    outputMaxSeq: 1,
    lastError: null,
    lastJudge: null,
    judgeFailCount: 0,
    promptHistory: [],
    output,
    events: [],
    attempts: [],
    cancelRequested: false,
    nextPrompt: null,
    nextMode: null,
  };
}

function taskJson(task: QueueTask): JsonValue {
  return toJsonValue(task);
}

function queueIdOf(task: QueueTask): string {
  return safeQueueId(task.queueId);
}

function rowToTask(row: TaskJsonRow): QueueTask {
  const task = normalizeTask(row.task_json);
  if (typeof row.id === "string" && row.id.length > 0) task.id = row.id;
  if (typeof row.queue_id === "string" && row.queue_id.length > 0) task.queueId = safeQueueId(row.queue_id);
  if (typeof row.status === "string" && row.status.length > 0) task.status = row.status as TaskStatus;
  if (row.current_attempt !== undefined) task.currentAttempt = Number(row.current_attempt ?? 0);
  if (row.current_mode !== undefined) task.currentMode = row.current_mode ?? null;
  if (row.codex_thread_id !== undefined) task.codexThreadId = row.codex_thread_id ?? null;
  if (row.active_turn_id !== undefined) task.activeTurnId = row.active_turn_id ?? null;
  if (row.updated_at !== undefined) task.updatedAt = timestampToIso(row.updated_at) ?? task.updatedAt;
  if (row.started_at !== undefined) task.startedAt = timestampToIso(row.started_at);
  if (row.finished_at !== undefined) task.finishedAt = timestampToIso(row.finished_at);
  if (row.read_at !== undefined) task.readAt = timestampToIso(row.read_at);
  return task;
}

function taskStatusRowJson(row: TaskStatusRow | null): JsonValue {
  if (row === null) return null;
  return {
    id: row.id,
    queueId: safeQueueId(row.queue_id),
    status: row.status,
    startedAt: timestampToIso(row.started_at),
    currentAttempt: Number(row.current_attempt ?? 0),
    codexThreadId: row.codex_thread_id,
    activeTurnId: row.active_turn_id,
  };
}

function taskIsUnclaimedMovable(task: QueueTask): boolean {
  return (task.status === "queued" || task.status === "retry_wait")
    && task.startedAt === null
    && task.currentAttempt === 0
    && task.codexThreadId === null
    && task.activeTurnId === null;
}

function taskStatusMoveBlocker(row: TaskStatusRow | null): string {
  if (row === null) return "task not found";
  if (row.status !== "queued" && row.status !== "retry_wait") return `status=${row.status}`;
  if (row.started_at !== null) return "task already has started_at";
  const currentAttempt = Number(row.current_attempt ?? 0);
  if (currentAttempt !== 0) return `task already has current_attempt=${currentAttempt}`;
  if (row.codex_thread_id !== null) return "task already has codex_thread_id";
  if (row.active_turn_id !== null) return "task already has active_turn_id";
  return "";
}

function queueMergeBlocker(row: TaskStatusRow): string {
  if (row.status === "running" || row.status === "judging") return `task ${row.id} is ${row.status}`;
  if ((row.status === "queued" || row.status === "retry_wait") && taskStatusMoveBlocker(row).length > 0) return `task ${row.id} has already been claimed`;
  return "";
}

function queueRowToRecord(row: QueueRow): QueueRecord {
  const id = safeQueueId(row.id);
  return {
    id,
    name: normalizeQueueName(row.name, id),
    createdAt: timestampToIso(row.created_at) ?? nowIso(),
    updatedAt: timestampToIso(row.updated_at) ?? nowIso(),
  };
}

async function ensureSchema(): Promise<void> {
  await mgrSql`
    CREATE TABLE IF NOT EXISTS unidesk_code_queue_tasks (
      id TEXT PRIMARY KEY,
      queue_id TEXT NOT NULL DEFAULT 'default',
      status TEXT NOT NULL,
      provider_id TEXT NOT NULL DEFAULT 'main-server',
      execution_mode TEXT NOT NULL DEFAULT 'default',
      model TEXT NOT NULL,
      cwd TEXT NOT NULL,
      prompt TEXT NOT NULL,
      base_prompt TEXT NOT NULL DEFAULT '',
      reference_task_ids JSONB NOT NULL DEFAULT '[]'::jsonb,
      reference_injection JSONB,
      reasoning_effort TEXT,
      max_attempts INTEGER NOT NULL,
      current_attempt INTEGER NOT NULL DEFAULT 0,
      current_mode TEXT,
      codex_thread_id TEXT,
      active_turn_id TEXT,
      created_at TIMESTAMPTZ NOT NULL,
      updated_at TIMESTAMPTZ NOT NULL,
      started_at TIMESTAMPTZ,
      finished_at TIMESTAMPTZ,
      read_at TIMESTAMPTZ,
      last_error TEXT,
      last_judge JSONB,
      output_count INTEGER NOT NULL DEFAULT 0,
      event_count INTEGER NOT NULL DEFAULT 0,
      attempt_count INTEGER NOT NULL DEFAULT 0,
      last_output_seq BIGINT NOT NULL DEFAULT 0,
      task_json JSONB NOT NULL
    )
  `;
  await mgrSql`
    CREATE TABLE IF NOT EXISTS unidesk_code_queue_queues (
      id TEXT PRIMARY KEY,
      name TEXT NOT NULL DEFAULT '',
      created_at TIMESTAMPTZ NOT NULL,
      updated_at TIMESTAMPTZ NOT NULL
    )
  `;
  await mgrSql`
    CREATE TABLE IF NOT EXISTS unidesk_code_queue_workdirs (
      provider_id TEXT NOT NULL,
      execution_mode TEXT NOT NULL DEFAULT 'default',
      path TEXT NOT NULL,
      created_at TIMESTAMPTZ NOT NULL,
      updated_at TIMESTAMPTZ NOT NULL,
      PRIMARY KEY (provider_id, execution_mode, path)
    )
  `;
  await mgrSql`ALTER TABLE unidesk_code_queue_tasks ADD COLUMN IF NOT EXISTS queue_id TEXT NOT NULL DEFAULT 'default'`;
  await mgrSql`ALTER TABLE unidesk_code_queue_tasks ADD COLUMN IF NOT EXISTS provider_id TEXT NOT NULL DEFAULT 'main-server'`;
  await mgrSql`ALTER TABLE unidesk_code_queue_tasks ADD COLUMN IF NOT EXISTS execution_mode TEXT NOT NULL DEFAULT 'default'`;
  await mgrSql`ALTER TABLE unidesk_code_queue_tasks ADD COLUMN IF NOT EXISTS base_prompt TEXT NOT NULL DEFAULT ''`;
  await mgrSql`ALTER TABLE unidesk_code_queue_tasks ADD COLUMN IF NOT EXISTS reference_task_ids JSONB NOT NULL DEFAULT '[]'::jsonb`;
  await mgrSql`ALTER TABLE unidesk_code_queue_tasks ADD COLUMN IF NOT EXISTS reference_injection JSONB`;
  await mgrSql`ALTER TABLE unidesk_code_queue_tasks ADD COLUMN IF NOT EXISTS read_at TIMESTAMPTZ`;
  await mgrSql`ALTER TABLE unidesk_code_queue_queues ADD COLUMN IF NOT EXISTS name TEXT NOT NULL DEFAULT ''`;
  await mgrSql`ALTER TABLE unidesk_code_queue_workdirs ADD COLUMN IF NOT EXISTS execution_mode TEXT NOT NULL DEFAULT 'default'`;
  const now = nowIso();
  await upsertQueue({ id: config.defaultQueueId, name: config.defaultQueueId, createdAt: now, updatedAt: now });
  await Promise.all(defaultWorkdirRecords(now).map((record) => upsertWorkdir(record)));
  schemaReady = true;
  schemaLastError = null;
}

async function ensureSchemaWithRetry(): Promise<void> {
  let attempt = 0;
  while (!schemaReady) {
    attempt += 1;
    try {
      await ensureSchema();
      log("info", "schema_ready", { attempt });
      return;
    } catch (error) {
      schemaLastError = errorToJson(error);
      lastDatabaseError = schemaLastError.message as string;
      log("warn", "schema_retry", { attempt, error: schemaLastError });
      await Bun.sleep(Math.min(1000 * attempt, 10_000));
    }
  }
}

async function upsertQueue(queue: QueueRecord): Promise<void> {
  await mgrSql`
    INSERT INTO unidesk_code_queue_queues (id, name, created_at, updated_at)
    VALUES (${queue.id}, ${queue.name}, ${queue.createdAt}, ${queue.updatedAt})
    ON CONFLICT (id) DO UPDATE SET
      name = EXCLUDED.name,
      updated_at = GREATEST(unidesk_code_queue_queues.updated_at, EXCLUDED.updated_at)
  `;
}

async function upsertTask(client: SqlExecutor, task: QueueTask): Promise<void> {
  const taskHasClaim = hasClaimMarkers(task);
  await client`
    INSERT INTO unidesk_code_queue_tasks (
      id, queue_id, status, provider_id, execution_mode, model, cwd, prompt, base_prompt,
      reference_task_ids, reference_injection, reasoning_effort, max_attempts,
      current_attempt, current_mode, codex_thread_id, active_turn_id,
      created_at, updated_at, started_at, finished_at, read_at, last_error,
      last_judge, output_count, event_count, attempt_count, last_output_seq, task_json
    ) VALUES (
      ${task.id}, ${queueIdOf(task)}, ${task.status}, ${task.providerId}, ${task.executionMode},
      ${task.model}, ${task.cwd}, ${task.prompt}, ${task.basePrompt},
      ${client.json(task.referenceTaskIds as unknown as postgres.JSONValue)}, ${task.referenceInjection === null ? null : client.json(task.referenceInjection as unknown as postgres.JSONValue)},
      ${task.reasoningEffort}, ${task.maxAttempts}, ${task.currentAttempt}, ${task.currentMode},
      ${task.codexThreadId}, ${task.activeTurnId}, ${task.createdAt}, ${task.updatedAt},
      ${task.startedAt}, ${task.finishedAt}, ${task.readAt}, ${task.lastError},
      ${task.lastJudge === null ? null : client.json(task.lastJudge as unknown as postgres.JSONValue)},
      ${task.output.length}, ${task.events.length}, ${task.attempts.length}, ${outputMaxSeq(task)}, ${client.json(taskJson(task) as unknown as postgres.JSONValue)}
    )
    ON CONFLICT (id) DO UPDATE SET
      queue_id = EXCLUDED.queue_id,
      status = EXCLUDED.status,
      provider_id = EXCLUDED.provider_id,
      execution_mode = EXCLUDED.execution_mode,
      model = EXCLUDED.model,
      cwd = EXCLUDED.cwd,
      prompt = EXCLUDED.prompt,
      base_prompt = EXCLUDED.base_prompt,
      reference_task_ids = EXCLUDED.reference_task_ids,
      reference_injection = EXCLUDED.reference_injection,
      reasoning_effort = EXCLUDED.reasoning_effort,
      max_attempts = EXCLUDED.max_attempts,
      current_attempt = EXCLUDED.current_attempt,
      current_mode = EXCLUDED.current_mode,
      codex_thread_id = EXCLUDED.codex_thread_id,
      active_turn_id = EXCLUDED.active_turn_id,
      updated_at = EXCLUDED.updated_at,
      started_at = EXCLUDED.started_at,
      finished_at = EXCLUDED.finished_at,
      read_at = EXCLUDED.read_at,
      last_error = EXCLUDED.last_error,
      last_judge = EXCLUDED.last_judge,
      output_count = EXCLUDED.output_count,
      event_count = EXCLUDED.event_count,
      attempt_count = EXCLUDED.attempt_count,
      last_output_seq = EXCLUDED.last_output_seq,
      task_json = EXCLUDED.task_json
    WHERE unidesk_code_queue_tasks.status NOT IN ('running', 'judging')
      AND (
        ${taskHasClaim}
        OR unidesk_code_queue_tasks.started_at IS NULL
        OR unidesk_code_queue_tasks.started_at = EXCLUDED.started_at
      )
      AND (
        ${taskHasClaim}
        OR unidesk_code_queue_tasks.current_attempt = 0
        OR unidesk_code_queue_tasks.current_attempt = EXCLUDED.current_attempt
      )
      AND (
        ${taskHasClaim}
        OR unidesk_code_queue_tasks.codex_thread_id IS NULL
        OR unidesk_code_queue_tasks.codex_thread_id = EXCLUDED.codex_thread_id
      )
      AND (
        ${taskHasClaim}
        OR unidesk_code_queue_tasks.active_turn_id IS NULL
        OR unidesk_code_queue_tasks.active_turn_id = EXCLUDED.active_turn_id
      )
  `;
}

async function loadQueues(): Promise<QueueRecord[]> {
  const rows = await traceSql<QueueRow[]>`
    SELECT id, name, created_at, updated_at
    FROM unidesk_code_queue_queues
    ORDER BY id ASC
  `;
  const queues = rows.map(queueRowToRecord);
  if (!queues.some((queue) => queue.id === config.defaultQueueId)) {
    const at = nowIso();
    queues.unshift({ id: config.defaultQueueId, name: config.defaultQueueId, createdAt: at, updatedAt: at });
  }
  return queues;
}

async function loadTask(taskId: string): Promise<QueueTask | null> {
  const rows = await traceSql<TaskJsonRow[]>`
    SELECT
      id,
      queue_id,
      status,
      current_attempt,
      current_mode,
      codex_thread_id,
      active_turn_id,
      updated_at,
      started_at,
      finished_at,
      read_at,
      task_json
    FROM unidesk_code_queue_tasks
    WHERE id = ${taskId}
    LIMIT 1
  `;
  return rows[0] === undefined ? null : rowToTask(rows[0]);
}

async function loadTasksForList(url: URL): Promise<{ tasks: QueueTask[]; total: number; limit: number; offset: number }> {
  const limit = parseLimit(url);
  const offset = Math.max(0, numberField(url.searchParams.get("offset"), 0));
  const status = url.searchParams.get("status");
  const queueIdRaw = url.searchParams.get("queueId");
  const queueId = queueIdRaw === null || queueIdRaw.length === 0 ? null : safeQueueId(queueIdRaw);
  const search = String(url.searchParams.get("q") ?? url.searchParams.get("search") ?? "").trim();
  const rows = await traceSql<Array<TaskJsonRow & { total_count: string | number }>>`
    SELECT
      id,
      queue_id,
      status,
      current_attempt,
      current_mode,
      codex_thread_id,
      active_turn_id,
      updated_at,
      started_at,
      finished_at,
      read_at,
      task_json,
      COUNT(*) OVER() AS total_count
    FROM unidesk_code_queue_tasks
    WHERE (${status === null} OR status = ${status})
      AND (${queueId === null} OR queue_id = ${queueId})
      AND (${search.length === 0} OR id ILIKE ${`%${search}%`} OR prompt ILIKE ${`%${search}%`} OR base_prompt ILIKE ${`%${search}%`} OR COALESCE(last_error, '') ILIKE ${`%${search}%`})
    ORDER BY
      CASE status WHEN 'running' THEN 0 WHEN 'judging' THEN 1 WHEN 'retry_wait' THEN 2 WHEN 'queued' THEN 3 ELSE 8 END ASC,
      updated_at DESC,
      id DESC
    LIMIT ${limit}
    OFFSET ${offset}
  `;
  return { tasks: rows.map(rowToTask), total: Number(rows[0]?.total_count ?? 0), limit, offset };
}

async function loadTasksByIds(ids: string[]): Promise<QueueTask[]> {
  const unique = Array.from(new Set(ids.map((id) => id.trim()).filter(Boolean)));
  if (unique.length === 0) return [];
  const rows = await traceSql<TaskJsonRow[]>`
    SELECT
      id,
      queue_id,
      status,
      current_attempt,
      current_mode,
      codex_thread_id,
      active_turn_id,
      updated_at,
      started_at,
      finished_at,
      read_at,
      task_json
    FROM unidesk_code_queue_tasks
    WHERE id IN ${traceSql(unique)}
  `;
  const byId = new Map(rows.map((row) => {
    const task = rowToTask(row);
    return [task.id, task] as const;
  }));
  const tasks: QueueTask[] = [];
  for (const id of unique) {
    const task = byId.get(id);
    if (task !== undefined) tasks.push(task);
  }
  return tasks;
}

function parseLimit(url: URL): number {
  const raw = Number(url.searchParams.get("limit") ?? 100);
  return Number.isInteger(raw) && raw > 0 ? Math.min(config.maxListLimit, raw) : 100;
}

async function queueSummary(tasks?: QueueTask[], queueRecords?: QueueRecord[]): Promise<JsonRecord> {
  const [taskRows, queues] = await Promise.all([
    tasks === undefined ? loadAllTasksLite() : Promise.resolve(tasks),
    queueRecords === undefined ? loadQueues() : Promise.resolve(queueRecords),
  ]);
  const summaries = new Map<string, JsonRecord>();
  for (const queue of queues) {
    summaries.set(queue.id, {
      id: queue.id,
      name: queue.name,
      total: 0,
      counts: {},
      unreadTerminal: 0,
      activeTaskId: null,
      runnableTaskId: null,
      processing: false,
      createdAt: queue.createdAt,
      updatedAt: queue.updatedAt,
    });
  }
  for (const task of taskRows) {
    const queueId = queueIdOf(task);
    if (!summaries.has(queueId)) {
      summaries.set(queueId, {
        id: queueId,
        name: queueId,
        total: 0,
        counts: {},
        unreadTerminal: 0,
        activeTaskId: null,
        runnableTaskId: null,
        processing: false,
        createdAt: null,
        updatedAt: null,
      });
    }
    const summary = summaries.get(queueId) as JsonRecord;
    summary.total = Number(summary.total) + 1;
    const counts = asJsonRecord(summary.counts);
    counts[task.status] = Number(counts[task.status] ?? 0) + 1;
    summary.counts = counts;
    if (terminalTaskUnread(task)) summary.unreadTerminal = Number(summary.unreadTerminal) + 1;
    if ((task.status === "queued" || task.status === "retry_wait") && summary.runnableTaskId === null) summary.runnableTaskId = task.id;
  }
  const counts = taskRows.reduce<Record<string, number>>((memo, task) => {
    memo[task.status] = (memo[task.status] ?? 0) + 1;
    return memo;
  }, {});
  const databaseActiveTaskIds = taskRows.filter((task) => task.status === "running" || task.status === "judging").map((task) => task.id).sort();
  const queueList = Array.from(summaries.values()).sort((left, right) => String(left.id).localeCompare(String(right.id)));
  return {
    total: taskRows.length,
    defaultQueueId: config.defaultQueueId,
    queueCount: queueList.length,
    queues: queueList,
    activeQueueIds: [],
    processingQueueIds: [],
    activeRunSlotCount: 0,
    activeRunSlotWaiters: [],
    activeTaskIds: [],
    activeTaskId: null,
    databaseActiveTaskIds,
    databaseActiveTaskCount: databaseActiveTaskIds.length,
    executionStateSource: "postgres-control-plane",
    executionDiagnostics: controlPlaneExecutionDiagnostics(taskRows),
    schedulerHeartbeatStaleMs,
    orphanedActiveTaskIds: [],
    orphanedActiveTaskCount: 0,
    processing: false,
    counts,
    unreadTerminal: taskRows.reduce((total, task) => total + (terminalTaskUnread(task) ? 1 : 0), 0),
    judgeConfigured: null,
    judgeFailRetryLimit: 3,
    defaultModel: config.defaultModel,
    codeModels: config.codeModels,
    codexModels: config.codeModels.filter((model) => codeAgentPortForModel(model) === "codex"),
    opencodeModels: config.codeModels.filter((model) => codeAgentPortForModel(model) === "opencode"),
    modelPorts: Object.fromEntries(config.codeModels.map((model) => [model, codeAgentPortForModel(model)])),
    executionModes: codeExecutionModes.map(executionModeInfo),
    executionModeInfo: Object.fromEntries(codeExecutionModes.map((mode) => [mode, executionModeInfo(mode)])),
    agentPorts: { codex: codeAgentPortInfo("codex"), opencode: codeAgentPortInfo("opencode") },
    defaultReasoningEffort: config.defaultReasoningEffort,
    modelReasoningEfforts: config.modelReasoningEfforts,
    defaultProviderId: config.defaultProviderId,
    mainProviderId: config.defaultProviderId,
    defaultWorkdir: config.defaultWorkdir,
    remoteDefaultWorkdir: config.remoteDefaultWorkdir,
    maxActiveQueues: null,
    storage: {
      primary: "postgres",
      postgresConfigured: true,
      postgresReady: schemaReady,
      lastError: lastDatabaseError,
      role: "master-control-plane",
      mgrPoolMax: config.mgrDatabasePoolMax,
      tracePoolMax: config.traceDatabasePoolMax,
    },
    controlPlane: "master-code-queue-mgr",
  };
}

async function loadAllTasksLite(): Promise<QueueTask[]> {
  const rows = await traceSql<TaskJsonRow[]>`
    SELECT
      id,
      queue_id,
      status,
      current_attempt,
      current_mode,
      codex_thread_id,
      active_turn_id,
      updated_at,
      started_at,
      finished_at,
      read_at,
      task_json - 'output' - 'events' AS task_json
    FROM unidesk_code_queue_tasks
    ORDER BY created_at ASC, id ASC
    LIMIT 2000
  `;
  return rows.map(rowToTask);
}

function taskTiming(task: QueueTask): JsonRecord {
  const created = timestampMs(task.createdAt);
  const started = timestampMs(task.startedAt);
  const finished = timestampMs(task.finishedAt);
  const updated = timestampMs(task.updatedAt);
  const now = Date.now();
  return {
    queuedMs: created === null ? null : (started ?? finished ?? updated ?? now) - created,
    runMs: started === null ? null : (finished ?? now) - started,
    totalElapsedMs: created === null ? null : (finished ?? now) - created,
    durationMs: started === null ? null : (finished ?? now) - started,
  };
}

function durationMsBetween(startAt: string | null | undefined, endAt: string | null | undefined): number | null {
  const start = typeof startAt === "string" ? Date.parse(startAt) : NaN;
  const end = typeof endAt === "string" ? Date.parse(endAt) : NaN;
  if (!Number.isFinite(start) || !Number.isFinite(end)) return null;
  return Math.max(0, end - start);
}

function codexStatsDateKey(value: string | Date | null | undefined): string | null {
  const date = value instanceof Date ? value : typeof value === "string" && value.length > 0 ? new Date(value) : null;
  if (date === null || Number.isNaN(date.getTime())) return null;
  const parts = codexStatsDateFormatter.formatToParts(date).reduce<Record<string, string>>((memo, part) => {
    if (part.type !== "literal") memo[part.type] = part.value;
    return memo;
  }, {});
  const year = parts.year;
  const month = parts.month;
  const day = parts.day;
  return year !== undefined && month !== undefined && day !== undefined ? `${year}-${month}-${day}` : null;
}

function shiftDateKey(dateKey: string, offsetDays: number): string {
  const [year = "1970", month = "01", day = "01"] = dateKey.split("-");
  const shifted = new Date(Date.UTC(Number(year), Number(month) - 1, Number(day) + offsetDays));
  return shifted.toISOString().slice(0, 10);
}

function statsDaysFromUrl(url: URL): number {
  const value = Number(url.searchParams.get("days") ?? 14);
  return Number.isInteger(value) && value > 0 ? Math.min(90, value) : 14;
}

function retryAttemptDates(task: QueueTask): Array<string | null> {
  const retryAttempts = task.attempts.filter((attempt, index) => attempt.mode === "retry" || Number(attempt.index || 0) > 1 || index > 0);
  const fallbackRetryCount = Math.max(0, Math.floor(Number(task.currentAttempt || 0)) - 1);
  const fallbackAt = timestampToIso(task.updatedAt) ?? timestampToIso(task.startedAt) ?? timestampToIso(task.createdAt);
  return [
    ...retryAttempts.map((attempt) => timestampToIso(attempt.startedAt) ?? timestampToIso(attempt.finishedAt) ?? fallbackAt),
    ...Array.from({ length: Math.max(0, fallbackRetryCount - retryAttempts.length) }, () => fallbackAt),
  ];
}

function completedTaskDurationMs(task: QueueTask, finishedAt: string): number | null {
  const startedAt = timestampToIso(task.startedAt) ?? timestampToIso(task.attempts[0]?.startedAt ?? null) ?? timestampToIso(task.createdAt);
  return durationMsBetween(startedAt, finishedAt);
}

function taskStatisticsSummary(tasks: QueueTask[], days = 14): JsonRecord {
  const generatedAt = nowIso();
  const endDate = codexStatsDateKey(new Date()) ?? generatedAt.slice(0, 10);
  const safeDays = Math.max(1, Math.min(90, Math.floor(days)));
  const startDate = shiftDateKey(endDate, 1 - safeDays);
  const buckets = new Map<string, DailyTaskStatsBucket>();
  for (let offset = 0; offset < safeDays; offset += 1) {
    const date = shiftDateKey(startDate, offset);
    buckets.set(date, {
      date,
      executedTasks: 0,
      completedTasks: 0,
      retryAttempts: 0,
      succeededTasks: 0,
      failedTasks: 0,
      canceledTasks: 0,
      totalDurationMs: 0,
      durationSamples: 0,
    });
  }
  const bucketFor = (value: string | null): DailyTaskStatsBucket | null => {
    const date = codexStatsDateKey(value);
    return date === null ? null : buckets.get(date) ?? null;
  };
  for (const task of tasks) {
    const executedBucket = bucketFor(timestampToIso(task.startedAt) ?? timestampToIso(task.attempts[0]?.startedAt ?? null));
    if (executedBucket !== null) executedBucket.executedTasks += 1;
    for (const retryAt of retryAttemptDates(task)) {
      const retryBucket = bucketFor(retryAt);
      if (retryBucket !== null) retryBucket.retryAttempts += 1;
    }
    if (!terminalTask(task)) continue;
    const finishedAt = timestampToIso(task.finishedAt) ?? timestampToIso(task.updatedAt);
    const completedBucket = bucketFor(finishedAt);
    if (finishedAt === null || completedBucket === null) continue;
    completedBucket.completedTasks += 1;
    if (task.status === "succeeded") completedBucket.succeededTasks += 1;
    if (task.status === "failed") completedBucket.failedTasks += 1;
    if (task.status === "canceled") completedBucket.canceledTasks += 1;
    const durationMs = completedTaskDurationMs(task, finishedAt);
    if (durationMs !== null) {
      completedBucket.totalDurationMs += durationMs;
      completedBucket.durationSamples += 1;
    }
  }
  const daily = Array.from(buckets.values()).map((bucket) => ({
    ...bucket,
    avgDurationMs: bucket.durationSamples > 0 ? Math.round(bucket.totalDurationMs / bucket.durationSamples) : null,
  }));
  const totals = daily.reduce((memo, day) => {
    memo.executedTasks += day.executedTasks;
    memo.completedTasks += day.completedTasks;
    memo.retryAttempts += day.retryAttempts;
    memo.succeededTasks += day.succeededTasks;
    memo.failedTasks += day.failedTasks;
    memo.canceledTasks += day.canceledTasks;
    memo.totalDurationMs += day.totalDurationMs;
    memo.durationSamples += day.durationSamples;
    return memo;
  }, {
    executedTasks: 0,
    completedTasks: 0,
    retryAttempts: 0,
    succeededTasks: 0,
    failedTasks: 0,
    canceledTasks: 0,
    totalDurationMs: 0,
    durationSamples: 0,
  });
  return {
    generatedAt,
    timezone: codexStatsTimeZone,
    days: safeDays,
    range: { startDate, endDate },
    totals: {
      ...totals,
      avgDurationMs: totals.durationSamples > 0 ? Math.round(totals.totalDurationMs / totals.durationSamples) : null,
    },
    daily: toJsonValue(daily),
  };
}

function taskListResponse(task: QueueTask, lite = true): JsonRecord {
  const promptFields = codeQueuePromptResponseFields(task, { lite, userPromptForDisplay });
  return {
    id: task.id,
    queueId: queueIdOf(task),
    queueEnteredAt: task.queueEnteredAt,
    ...promptFields,
    promptEditable: queuedTaskPromptEditable(task),
    finalResponseChars: task.finalResponse.length,
    ...traceStatsFallbackPatch(task),
    summaryOnly: true,
    referenceTaskIds: task.referenceTaskIds,
    referenceInjection: task.referenceInjection,
    providerId: task.providerId,
    executionMode: task.executionMode,
    executionModeInfo: executionModeInfo(task.executionMode),
    cwd: task.cwd,
    model: task.model,
    agentPort: codeAgentPortForModel(task.model),
    agentPortInfo: codeAgentPortInfo(codeAgentPortForModel(task.model)),
    reasoningEffort: task.reasoningEffort,
    maxAttempts: task.maxAttempts,
    status: task.status,
    queuedReason: (task.status === "queued" || task.status === "retry_wait") ? { code: "mgr-visible", label: task.status === "retry_wait" ? "RETRY" : "QUEUED", message: "Task is visible from master code-queue-mgr; D601 scheduler will pick it up from PostgreSQL." } : null,
    queuedReasonLabel: task.status === "retry_wait" ? "RETRY" : task.status === "queued" ? "QUEUED" : null,
    createdAt: task.createdAt,
    updatedAt: task.updatedAt,
    startedAt: task.startedAt,
    finishedAt: task.finishedAt,
    readAt: task.readAt,
    currentAttempt: task.currentAttempt,
    currentMode: task.currentMode,
    judgeFailCount: task.judgeFailCount,
    judgeFailRetryLimit: 3,
    schedulerHeartbeat: task.schedulerHeartbeat ?? null,
    codexThreadId: task.codexThreadId,
    activeTurnId: task.activeTurnId,
    lastError: task.lastError,
    lastJudge: task.lastJudge,
    cancelRequested: task.cancelRequested,
    terminalUnread: terminalTaskUnread(task),
    outputCount: task.output.length,
    eventCount: task.events.length,
    attemptCount: task.attempts.length,
    attempts: toJsonValue(lite ? task.attempts.slice(-3) : task.attempts),
    timing: taskTiming(task),
  };
}

function taskMetaResponse(task: QueueTask): JsonRecord {
  return {
    ...taskListResponse(task, false),
    prompt: task.prompt,
    basePrompt: task.basePrompt,
    displayPrompt: task.basePrompt || userPromptForDisplay(task.prompt),
    initialPrompt: task.prompt,
    finalResponse: task.finalResponse,
    promptHistory: toJsonValue(task.promptHistory),
    attempts: toJsonValue(task.attempts),
    nextMode: task.nextMode,
    outputCount: task.output.length,
    retainedOutputCount: task.output.length,
    eventCount: task.events.length,
    transcriptCount: task.output.length + task.promptHistory.length,
    transcriptMaxSeq: outputMaxSeq(task),
    transcript: [],
    output: [],
    events: [],
  };
}

function lastAssistantMessage(task: QueueTask): JsonRecord {
  const assistantOutput = task.output.slice().reverse().find((item) => item.channel === "assistant");
  const text = task.finalResponse.trim().length > 0 ? task.finalResponse.trim() : String(assistantOutput?.text ?? "").trim();
  return {
    text,
    at: assistantOutput?.at ?? task.finishedAt ?? task.updatedAt,
    seq: assistantOutput?.seq ?? null,
    source: task.finalResponse.trim().length > 0 ? "finalResponse" : assistantOutput !== undefined ? "output" : "none",
  };
}

function taskSummary(task: QueueTask, url: URL): JsonRecord {
  const toolLimit = Math.max(1, Math.min(500, numberField(url.searchParams.get("toolLimit"), 160)));
  const toolOutputs = task.output.filter((item) => item.channel === "command" || item.channel === "tool" || item.channel === "diff" || item.channel === "error");
  const start = Math.max(0, toolOutputs.length - toolLimit);
  return {
    id: task.id,
    queueId: queueIdOf(task),
    status: task.status,
    providerId: task.providerId,
    executionMode: task.executionMode,
    executionModeInfo: executionModeInfo(task.executionMode),
    model: task.model,
    agentPort: codeAgentPortForModel(task.model),
    agentPortInfo: codeAgentPortInfo(codeAgentPortForModel(task.model)),
    cwd: task.cwd,
    reasoningEffort: task.reasoningEffort,
    maxAttempts: task.maxAttempts,
    currentAttempt: task.currentAttempt,
    currentMode: task.currentMode,
    judgeFailCount: task.judgeFailCount,
    judgeFailRetryLimit: 3,
    codexThreadId: task.codexThreadId,
    activeTurnId: task.activeTurnId,
    createdAt: task.createdAt,
    startedAt: task.startedAt,
    finishedAt: task.finishedAt,
    updatedAt: task.updatedAt,
    timing: taskTiming(task),
    initialPrompt: task.prompt,
    basePrompt: task.basePrompt,
    prompt: task.prompt,
    promptEditable: queuedTaskPromptEditable(task),
    referenceTaskIds: task.referenceTaskIds,
    referenceInjection: task.referenceInjection,
    lastAssistantMessage: lastAssistantMessage(task),
    toolSummary: {
      count: toolOutputs.length,
      returned: toolOutputs.length - start,
      limit: toolLimit,
      truncated: start > 0,
      items: toolOutputs.slice(start).map((item) => ({
        seq: item.seq,
        at: item.at,
        kind: item.channel === "diff" ? "edited" : item.channel === "command" ? "ran" : item.channel === "tool" ? "explored" : "error",
        title: item.method ?? item.channel,
        status: item.method ?? null,
        commandPreview: item.channel === "command" ? safePreview(item.text, 700) : "",
        commandOmittedLines: 0,
        outputPreview: item.channel === "command" ? "" : safePreview(item.text, 1200),
        outputOmittedLines: 0,
        rawSeqs: [item.seq],
      })),
    },
    attempts: toJsonValue(task.attempts),
    lastJudge: task.lastJudge,
    lastError: task.lastError,
    cancelRequested: task.cancelRequested,
    outputCount: task.output.length,
    eventCount: task.events.length,
    transcriptCount: task.output.length + task.promptHistory.length,
    transcriptMaxSeq: outputMaxSeq(task),
  };
}

function outputPage(task: QueueTask, url: URL): JsonRecord {
  const limit = parseLimit(url);
  const afterSeq = numberField(url.searchParams.get("afterSeq"), 0);
  const beforeSeqRaw = url.searchParams.get("beforeSeq");
  const tail = url.searchParams.get("tail") === "1";
  const ordered = task.output.slice().sort((left, right) => left.seq - right.seq);
  let rows = ordered;
  if (beforeSeqRaw !== null) rows = ordered.filter((item) => item.seq < numberField(beforeSeqRaw, Number.MAX_SAFE_INTEGER)).slice(-limit);
  else if (tail) rows = ordered.slice(-limit);
  else rows = ordered.filter((item) => item.seq > afterSeq).slice(0, limit);
  const firstSeq = rows[0]?.seq ?? null;
  const lastSeq = rows.at(-1)?.seq ?? null;
  const fullText = url.searchParams.get("fullText") === "1";
  const maxTextChars = Math.max(200, Math.min(100_000, numberField(url.searchParams.get("maxTextChars"), 6000)));
  return {
    ok: true,
    taskId: task.id,
    queueId: queueIdOf(task),
    status: task.status,
    updatedAt: task.updatedAt,
    mode: beforeSeqRaw !== null ? "before" : tail ? "tail" : "after",
    limit,
    total: ordered.length,
    maxSeq: outputMaxSeq(task),
    afterSeq,
    nextAfterSeq: lastSeq,
    beforeSeq: beforeSeqRaw === null ? null : numberField(beforeSeqRaw),
    previousBeforeSeq: firstSeq,
    hasMore: lastSeq !== null && ordered.some((item) => item.seq > lastSeq),
    hasBefore: firstSeq !== null && ordered.some((item) => item.seq < firstSeq),
    output: rows.map((item) => ({ ...item, text: fullText ? item.text : safePreview(item.text, maxTextChars) })),
  };
}

function transcriptKind(item: LiveOutput): string {
  if (item.channel === "command") return "ran";
  if (item.channel === "diff") return "edited";
  if (item.channel === "tool") return "explored";
  if (item.channel === "error") return "error";
  if (item.channel === "system") return "system";
  return "message";
}

function outputToTranscriptLine(item: LiveOutput, fullText: boolean): JsonRecord {
  const body = fullText ? item.text : safePreview(item.text, 1600);
  return {
    seq: item.seq,
    at: item.at,
    kind: transcriptKind(item),
    title: item.method ?? item.channel,
    status: item.method ?? null,
    commandPreview: item.channel === "command" ? body : null,
    bodyPreview: item.channel === "command" ? "" : body,
    commandOmittedLines: 0,
    bodyOmittedLines: 0,
    rawSeqs: [item.seq],
  };
}

function outputToTraceStepLine(item: LiveOutput): TraceStepLine {
  const body = safePreview(item.text, 1600);
  return {
    seq: item.seq,
    at: item.at,
    kind: transcriptKind(item),
    label: item.channel,
    title: item.method ?? item.channel,
    status: item.method ?? null,
    summaryLines: body.length > 0 ? [body] : [],
    commandPreview: item.channel === "command" ? body : undefined,
    commandOmittedLines: 0,
    bodyPreview: item.channel === "command" ? "" : body,
    bodyOmittedLines: 0,
    rawSeqs: [item.seq],
    source: "code-queue-mgr-postgres",
  };
}

function traceStepJson(line: TraceStepLine): JsonRecord {
  return {
    seq: line.seq,
    at: line.at,
    kind: line.kind,
    label: line.label,
    title: line.title,
    status: line.status,
    summaryLines: line.summaryLines,
    commandPreview: line.commandPreview ?? null,
    commandOmittedLines: line.commandOmittedLines ?? 0,
    bodyPreview: line.bodyPreview ?? "",
    bodyOmittedLines: line.bodyOmittedLines ?? 0,
    rawSeqs: line.rawSeqs,
    source: line.source,
  };
}

function transcriptPage(task: QueueTask, url: URL): JsonRecord {
  const limit = parseLimit(url);
  const afterSeq = numberField(url.searchParams.get("afterSeq"), 0);
  const beforeSeqRaw = url.searchParams.get("beforeSeq");
  const tail = url.searchParams.get("tail") === "1";
  const fullText = url.searchParams.get("fullText") === "1" || url.searchParams.get("raw") === "1";
  const transcript = task.output
    .filter((item) => !(item.channel === "system" && item.method === "enqueue"))
    .sort((left, right) => left.seq - right.seq)
    .map((item) => outputToTranscriptLine(item, fullText));
  let rows = transcript;
  if (beforeSeqRaw !== null) rows = transcript.filter((item) => Number(item.seq) < numberField(beforeSeqRaw, Number.MAX_SAFE_INTEGER)).slice(-limit);
  else if (tail) rows = transcript.slice(-limit);
  else rows = transcript.filter((item) => Number(item.seq) > afterSeq).slice(0, limit);
  const firstSeq = Number(rows[0]?.seq ?? NaN);
  const lastSeq = Number(rows.at(-1)?.seq ?? NaN);
  return {
    ok: true,
    taskId: task.id,
    queueId: queueIdOf(task),
    status: task.status,
    updatedAt: task.updatedAt,
    agentPort: codeAgentPortForModel(task.model),
    agentPortInfo: codeAgentPortInfo(codeAgentPortForModel(task.model)),
    mode: beforeSeqRaw !== null ? "before" : tail ? "tail" : "after",
    transcript: rows,
    afterSeq,
    nextAfterSeq: Number.isFinite(lastSeq) ? lastSeq : afterSeq,
    beforeSeq: beforeSeqRaw === null ? null : numberField(beforeSeqRaw),
    previousBeforeSeq: Number.isFinite(firstSeq) ? firstSeq : null,
    hasMore: Number.isFinite(lastSeq) && transcript.some((item) => Number(item.seq) > lastSeq),
    hasBefore: Number.isFinite(firstSeq) && transcript.some((item) => Number(item.seq) < firstSeq),
    total: transcript.length,
    maxSeq: transcript.at(-1)?.seq ?? 0,
    fullText,
  };
}

function oaTraceStepKind(kind: string): string {
  const normalized = String(kind || "").trim().toLowerCase();
  if (normalized === "read" || normalized === "explored" || normalized === "explore") return "explored";
  if (normalized === "edit" || normalized === "edited") return "edited";
  if (normalized === "run" || normalized === "ran" || normalized === "command") return "ran";
  if (normalized === "error" || normalized === "failed") return "error";
  if (normalized === "message" || normalized === "assistant" || normalized === "user") return "message";
  return "system";
}

function traceStepLifecycleMethod(line: TraceStepLine): "item/started" | "item/completed" | null {
  const status = String(line.status || "").trim();
  if (status === "item/started" || status === "item/completed") return status;
  const text = [line.title, ...line.summaryLines, line.commandPreview, line.bodyPreview].map((item) => String(item || "")).join("\n");
  if (/\bitem\/started\b/u.test(text)) return "item/started";
  if (/\bitem\/completed\b/u.test(text)) return "item/completed";
  return null;
}

function traceStepLifecycleTool(line: TraceStepLine): string | null {
  const text = [line.title, ...line.summaryLines, line.commandPreview, line.bodyPreview].map((item) => String(item || "")).join("\n");
  const match = /\b(webSearch|mcpToolCall|dynamicToolCall)\b/u.exec(text);
  return match?.[1] ?? null;
}

function traceStepIsCodexToolLifecycle(line: TraceStepLine): boolean {
  return traceStepLifecycleMethod(line) !== null && traceStepLifecycleTool(line) !== null;
}

function pushUniqueNumber(values: number[], value: number): void {
  if (Number.isFinite(value) && !values.includes(Math.floor(value))) values.push(Math.floor(value));
}

function pushUniqueString(values: string[], value: string): void {
  const text = value.trim();
  if (text.length > 0 && !values.includes(text)) values.push(text);
}

function traceStepCommandKind(text: string, fallback: string): string {
  if (/\bwebSearch\b/u.test(text)) return "explored";
  if (/\b(?:mcpToolCall|dynamicToolCall)\b/u.test(text)) return "ran";
  return fallback;
}

function shortTraceStepTitle(text: string, fallback: string): string {
  const normalized = text.replace(/\s+/gu, " ").trim();
  return normalized.length > 0 ? safePreview(normalized, 140) : fallback;
}

function mergeCodexToolLifecycleTraceSteps(group: TraceStepLine[]): TraceStepLine {
  if (group.length <= 1) return group[0] as TraceStepLine;
  const first = group[0] as TraceStepLine;
  const last = group.at(-1) ?? first;
  const rawSeqs: number[] = [];
  const summaryLines: string[] = [];
  for (const line of group) {
    for (const seq of line.rawSeqs.length > 0 ? line.rawSeqs : [line.seq]) pushUniqueNumber(rawSeqs, seq);
    for (const summaryLine of line.summaryLines) pushUniqueString(summaryLines, summaryLine);
  }
  rawSeqs.sort((left, right) => left - right);
  const commandText = String(last.commandPreview || first.commandPreview || last.bodyPreview || first.bodyPreview || last.title || first.title || "");
  const kind = traceStepCommandKind(commandText, last.kind || first.kind);
  return {
    ...first,
    at: last.at || first.at,
    kind,
    title: shortTraceStepTitle(commandText, last.title || first.title || "Trace step"),
    status: last.status || first.status,
    summaryLines: summaryLines.length > 0 ? summaryLines : first.summaryLines,
    commandPreview: commandText.length > 0 ? commandText : first.commandPreview,
    commandOmittedLines: Number(first.commandOmittedLines || 0) + Number(last.commandOmittedLines || 0),
    bodyPreview: last.bodyPreview || first.bodyPreview,
    bodyOmittedLines: Number(first.bodyOmittedLines || 0) + Number(last.bodyOmittedLines || 0),
    rawSeqs,
  };
}

function coalesceCodexToolLifecycleTraceSteps(lines: TraceStepLine[]): TraceStepLine[] {
  const rows = [...lines].sort((left, right) => left.seq - right.seq);
  const merged: TraceStepLine[] = [];
  let group: TraceStepLine[] = [];
  const flush = (): void => {
    if (group.length > 0) merged.push(mergeCodexToolLifecycleTraceSteps(group));
    group = [];
  };
  for (const line of rows) {
    if (traceStepIsCodexToolLifecycle(line)) {
      const method = traceStepLifecycleMethod(line);
      const tool = traceStepLifecycleTool(line);
      const groupTool = group.length === 0 ? null : traceStepLifecycleTool(group[0] as TraceStepLine);
      if (method === "item/started" && group.length > 0) flush();
      if (group.length > 0 && groupTool !== null && tool !== groupTool) flush();
      group.push(line);
      if (method === "item/completed") flush();
      continue;
    }
    flush();
    merged.push(line);
  }
  flush();
  return merged;
}

function traceScopeId(taskId: string, url: URL): string {
  const attempt = numberField(url.searchParams.get("attempt"), 0);
  return attempt > 0 ? `task:${taskId}:attempt:${attempt}` : `task:${taskId}`;
}

function taskScopeId(taskId: string): string {
  return `task:${taskId}`;
}

function taskAttemptScopeId(taskId: string, attemptIndex: number): string {
  return `${taskScopeId(taskId)}:attempt:${Math.floor(attemptIndex)}`;
}

function traceStatsRowToRecord(row: OaTraceStatsRow): JsonRecord {
  const taskAttempt = row.scope_id.match(/^task:([^:]+):attempt:(\d+)$/u);
  const taskOnly = row.scope_id.match(/^task:([^:]+)$/u);
  const taskId = taskAttempt?.[1] ?? taskOnly?.[1] ?? "";
  const attemptIndex = taskAttempt === null ? null : Number(taskAttempt[2]);
  return {
    scopeId: row.scope_id,
    serviceId: row.service_id,
    subjectKind: row.subject_kind,
    subjectId: row.subject_id,
    statsRevision: numberField(row.stats_revision, 0),
    stepCount: numberField(row.step_count, 0),
    llmStepCount: numberField(row.llm_step_count, 0),
    readCount: numberField(row.read_count, 0),
    editCount: numberField(row.edit_count, 0),
    runCount: numberField(row.run_count, 0),
    errorCount: numberField(row.error_count, 0),
    traceLineCount: numberField(row.trace_line_count, 0),
    outputMaxSeq: numberField(row.output_max_seq, 0),
    attemptStats: toJsonValue(row.attempt_stats_json ?? null),
    lastEventSequence: row.last_event_sequence === null ? null : numberField(row.last_event_sequence, 0),
    updatedAt: timestampToIso(row.updated_at),
    taskId,
    attemptIndex,
    source: "oa-event-flow",
  };
}

async function loadOaTraceStats(scopeIds: string[]): Promise<Map<string, JsonRecord>> {
  const uniqueScopeIds = Array.from(new Set(scopeIds.map((scopeId) => scopeId.trim()).filter(Boolean)));
  const result = new Map<string, JsonRecord>();
  if (uniqueScopeIds.length === 0) return result;
  try {
    const rows = await traceSql<OaTraceStatsRow[]>`
      SELECT scope_id, service_id, subject_kind, subject_id, stats_revision,
        step_count, llm_step_count, read_count, edit_count, run_count, error_count,
        trace_line_count, output_max_seq, attempt_stats_json, last_event_sequence, updated_at
      FROM oa_trace_stats
      WHERE scope_id IN ${traceSql(uniqueScopeIds)}
      ORDER BY updated_at DESC
      LIMIT ${Math.max(100, uniqueScopeIds.length)}
    `;
    for (const row of rows) {
      const record = traceStatsRowToRecord(row);
      result.set(String(record.scopeId || row.scope_id), record);
    }
  } catch (error) {
    log("warn", "oa_trace_stats_load_failed", { scopeIds: uniqueScopeIds, error: errorToJson(error) });
  }
  return result;
}

function oaTraceStepToLine(row: OaTraceStepRow): TraceStepLine | null {
  const seq = numberField(row.step_seq, 0);
  if (seq <= 0) return null;
  const kind = oaTraceStepKind(row.kind);
  const title = String(row.title || (kind === "explored" ? "Read" : kind === "edited" ? "Edit" : kind === "ran" ? "Run" : "Trace step"));
  const summaryLines = stringList(row.summary_lines);
  const summaryText = summaryLines.join("\n").trimEnd();
  const commandText = summaryText.startsWith("item/") ? summaryText : "";
  return {
    seq,
    at: timestampToIso(row.updated_at) ?? timestampToIso(row.created_at) ?? nowIso(),
    kind,
    label: row.kind,
    title,
    status: row.status ?? null,
    summaryLines,
    commandPreview: commandText.length > 0 ? commandText : undefined,
    commandOmittedLines: 0,
    bodyPreview: commandText.length > 0 ? "" : summaryText,
    bodyOmittedLines: 0,
    rawSeqs: numberList(row.raw_seqs, seq),
    source: "code-queue-mgr-oa-trace-steps",
  };
}

async function loadOaTraceStepLines(taskId: string, url: URL): Promise<TraceStepLine[]> {
  const scopeId = traceScopeId(taskId, url);
  const rows = await traceSql<OaTraceStepRow[]>`
    SELECT scope_id, step_seq, kind, title, status, summary_lines, raw_seqs, created_at, updated_at
    FROM oa_trace_steps
    WHERE scope_id = ${scopeId}
    ORDER BY step_seq ASC
    LIMIT 5000
  `;
  return coalesceCodexToolLifecycleTraceSteps(rows.map(oaTraceStepToLine).filter((line): line is TraceStepLine => line !== null));
}

async function loadOaTraceStepLinesForScope(scopeId: string): Promise<TraceStepLine[]> {
  const rows = await traceSql<OaTraceStepRow[]>`
    SELECT scope_id, step_seq, kind, title, status, summary_lines, raw_seqs, created_at, updated_at
    FROM oa_trace_steps
    WHERE scope_id = ${scopeId}
    ORDER BY step_seq ASC
    LIMIT 5000
  `;
  return coalesceCodexToolLifecycleTraceSteps(rows.map(oaTraceStepToLine).filter((line): line is TraceStepLine => line !== null));
}

function fallbackTraceStepLines(task: QueueTask): TraceStepLine[] {
  return task.output
    .filter((item) => item.channel !== "system" || item.method !== "enqueue")
    .sort((left, right) => left.seq - right.seq)
    .map(outputToTraceStepLine);
}

async function traceStepLines(task: QueueTask, url: URL): Promise<{ source: string; steps: TraceStepLine[] }> {
  try {
    const oaSteps = await loadOaTraceStepLines(task.id, url);
    if (oaSteps.length > 0) return { source: "code-queue-mgr-oa-trace-steps", steps: oaSteps };
  } catch (error) {
    log("warn", "oa_trace_steps_load_failed", { taskId: task.id, scopeId: traceScopeId(task.id, url), error: errorToJson(error) });
  }
  return { source: "code-queue-mgr-postgres", steps: fallbackTraceStepLines(task) };
}

async function traceSteps(task: QueueTask, url: URL): Promise<JsonRecord> {
  const limit = parseLimit(url);
  const afterSeq = numberField(url.searchParams.get("afterSeq"), 0);
  const beforeSeqRaw = url.searchParams.get("beforeSeq");
  const tail = url.searchParams.get("tail") === "1";
  const { source, steps: visible } = await traceStepLines(task, url);
  let rows = visible;
  if (beforeSeqRaw !== null) rows = visible.filter((item) => item.seq < numberField(beforeSeqRaw, Number.MAX_SAFE_INTEGER)).slice(-limit);
  else if (tail) rows = visible.slice(-limit);
  else rows = visible.filter((item) => item.seq > afterSeq).slice(0, limit);
  const firstSeq = rows[0]?.seq ?? null;
  const lastSeq = rows.at(-1)?.seq ?? null;
  return {
    ok: true,
    taskId: task.id,
    source,
    total: visible.length,
    returned: rows.length,
    limit,
    afterSeq,
    nextAfterSeq: lastSeq,
    beforeSeq: beforeSeqRaw === null ? null : numberField(beforeSeqRaw),
    previousBeforeSeq: firstSeq,
    hasMore: lastSeq !== null && visible.some((item) => item.seq > lastSeq),
    hasBefore: firstSeq !== null && visible.some((item) => item.seq < firstSeq),
    steps: rows.map(traceStepJson),
  };
}

async function traceStepDetail(task: QueueTask, url: URL): Promise<JsonRecord | null> {
  const seq = numberField(url.searchParams.get("seq"), 0);
  const { source, steps } = await traceStepLines(task, url);
  const line = steps.find((item) => item.seq === seq || item.rawSeqs.includes(seq)) ?? null;
  if (line !== null) {
    const payload = traceStepJson(line);
    return { ok: true, taskId: task.id, source, step: payload, rawOutput: payload, line: payload };
  }
  const item = task.output.find((output) => output.seq === seq) ?? null;
  if (item === null) return null;
  const fallback = outputToTraceStepLine(item);
  const payload = traceStepJson(fallback);
  return { ok: true, taskId: task.id, source: "code-queue-mgr-postgres", step: item as unknown as JsonValue, rawOutput: item as unknown as JsonValue, line: payload };
}

function attemptIndexesForTrace(task: QueueTask): number[] {
  const indexes = new Set<number>();
  for (const attempt of task.attempts) {
    const index = Number(attempt.index);
    if (Number.isInteger(index) && index > 0) indexes.add(index);
  }
  const current = Number(task.currentAttempt || 0);
  const max = Math.max(current, ...Array.from(indexes), 0);
  for (let index = 1; index <= max; index += 1) indexes.add(index);
  return Array.from(indexes).sort((left, right) => left - right);
}

function traceStepExecutionStats(steps: TraceStepLine[]): JsonRecord {
  const countKind = (kind: string) => steps.filter((step) => step.kind === kind).length;
  return {
    stepCount: steps.length,
    llmStepCount: steps.length,
    traceLineCount: steps.length,
    outputMaxSeq: steps.at(-1)?.seq ?? 0,
    readCount: countKind("explored"),
    editCount: countKind("edited"),
    runCount: countKind("ran"),
    errorCount: countKind("error"),
  };
}

function fallbackTraceStatsRecord(scopeId: string, fallback: JsonRecord): JsonRecord {
  const stepCount = positiveNumber(fallback.stepCount) ?? 0;
  return {
    scopeId,
    source: "oa-event-flow",
    stepCount,
    llmStepCount: positiveNumber(fallback.llmStepCount) ?? stepCount,
    traceLineCount: positiveNumber(fallback.traceLineCount) ?? stepCount,
    outputMaxSeq: positiveNumber(fallback.outputMaxSeq) ?? 0,
    readCount: positiveNumber(fallback.readCount) ?? 0,
    editCount: positiveNumber(fallback.editCount) ?? 0,
    runCount: positiveNumber(fallback.runCount) ?? 0,
    errorCount: positiveNumber(fallback.errorCount) ?? 0,
    sourceHint: "raw-trace-fallback",
  };
}

function applyStatsOrFallback(fallback: JsonRecord, stats: JsonRecord | null, scopeId: string): JsonRecord {
  if (stats !== null) {
    return {
      ...fallback,
      ...stats,
      traceStats: stats,
      statsSource: "oa-event-flow",
      traceStatsState: "ready",
      traceStatsReason: null,
      statsUnavailable: false,
      statsSyncing: false,
    };
  }
  const rawTracePresent = Number(fallback.stepCount ?? 0) > 0 || Number(fallback.traceLineCount ?? 0) > 0 || Number(fallback.outputMaxSeq ?? 0) > 0;
  const traceStats = rawTracePresent ? fallbackTraceStatsRecord(scopeId, fallback) : null;
  return {
    ...fallback,
    traceStats,
    statsSource: rawTracePresent ? "raw-trace-fallback" : "raw-trace-empty",
    traceStatsState: rawTracePresent ? "degraded" : "empty",
    traceStatsReason: rawTracePresent ? "oa-event-flow-stats-unavailable-raw-trace-present" : "no-trace-steps-yet",
    statsUnavailable: true,
    statsSyncing: rawTracePresent,
    rawTraceStepCount: fallback.stepCount ?? 0,
  };
}

async function traceSummary(task: QueueTask): Promise<JsonRecord> {
  const fallbackSteps = fallbackTraceStepLines(task);
  const attempts = attemptIndexesForTrace(task);
  const scopeIds = [taskScopeId(task.id), ...attempts.map((index) => taskAttemptScopeId(task.id, index))];
  const [stats, allOaSteps, attemptStepEntries] = await Promise.all([
    loadOaTraceStats(scopeIds),
    loadOaTraceStepLinesForScope(taskScopeId(task.id)).catch((error) => {
      log("warn", "oa_trace_summary_steps_load_failed", { taskId: task.id, error: errorToJson(error) });
      return [] as TraceStepLine[];
    }),
    Promise.all(attempts.map(async (index) => {
      const steps = await loadOaTraceStepLinesForScope(taskAttemptScopeId(task.id, index)).catch((error) => {
        log("warn", "oa_trace_summary_attempt_steps_load_failed", { taskId: task.id, attemptIndex: index, error: errorToJson(error) });
        return [] as TraceStepLine[];
      });
      return [index, steps] as const;
    })),
  ]);
  const visibleSteps = allOaSteps.length > 0 ? allOaSteps : fallbackSteps;
  const taskStats = applyStatsOrFallback(traceStepExecutionStats(visibleSteps), stats.get(taskScopeId(task.id)) ?? null, taskScopeId(task.id));
  const attemptSteps = new Map(attemptStepEntries);
  const attemptRows = attempts.length > 0 ? attempts : task.attempts.map((attempt) => attempt.index);
  return {
    id: task.id,
    taskId: task.id,
    queueId: queueIdOf(task),
    status: task.status,
    providerId: task.providerId,
    executionMode: task.executionMode,
    executionModeInfo: executionModeInfo(task.executionMode),
    model: task.model,
    agentPort: codeAgentPortForModel(task.model),
    agentPortInfo: codeAgentPortInfo(codeAgentPortForModel(task.model)),
    cwd: task.cwd,
    reasoningEffort: task.reasoningEffort,
    createdAt: task.createdAt,
    startedAt: task.startedAt,
    finishedAt: task.finishedAt,
    updatedAt: task.updatedAt,
    currentAttempt: task.currentAttempt,
    currentMode: task.currentMode,
    maxAttempts: task.maxAttempts,
    ...taskStats,
    retainedStepCount: fallbackSteps.length,
    outputMaxSeq: outputMaxSeq(task),
    schedulerHeartbeat: task.schedulerHeartbeat ?? null,
    execution: taskStats,
    attempts: attemptRows.map((index) => {
      const attempt = task.attempts.find((item) => Number(item.index) === index) ?? task.attempts[index - 1] ?? null;
      const steps = attemptSteps.get(index) ?? fallbackSteps.filter((step) => step.seq >= Number(attempt?.outputStartSeq ?? -Infinity) && step.seq <= Number(attempt?.outputEndSeq ?? Infinity));
      const attemptFallback = traceStepExecutionStats(steps);
      const attemptStats = applyStatsOrFallback(attemptFallback, stats.get(taskAttemptScopeId(task.id, index)) ?? null, taskAttemptScopeId(task.id, index));
      return {
        ...(attempt ?? {}),
        index,
        mode: attempt?.mode ?? (index <= 1 ? "initial" : "retry"),
        startedAt: attempt?.startedAt ?? steps[0]?.at ?? task.startedAt,
        finishedAt: attempt?.finishedAt ?? null,
        terminalStatus: attempt?.terminalStatus ?? null,
        startSeq: steps[0]?.seq ?? attempt?.outputStartSeq ?? null,
        endSeq: steps.at(-1)?.seq ?? attempt?.outputEndSeq ?? null,
        finalResponsePreview: attempt?.finalResponsePreview ?? "",
        judge: attempt?.judge ?? (index === task.currentAttempt ? task.lastJudge : null),
        feedbackPromptPreview: attempt?.feedbackPromptPreview ?? null,
        feedbackPromptChars: attempt?.feedbackPromptChars ?? null,
        attemptScopeId: taskAttemptScopeId(task.id, index),
        ...attemptStats,
        execution: attemptStats,
      };
    }),
    storedAttempts: task.attempts.map((attempt) => ({
      index: attempt.index,
      mode: attempt.mode,
      startedAt: attempt.startedAt,
      finishedAt: attempt.finishedAt,
      terminalStatus: attempt.terminalStatus,
      finalResponsePreview: attempt.finalResponsePreview,
      judge: attempt.judge ?? null,
      feedbackPromptPreview: attempt.feedbackPromptPreview ?? null,
      feedbackPromptChars: attempt.feedbackPromptChars ?? null,
    })),
    prompt: {
      initialPreview: safePreview(task.prompt, 1600),
      basePreview: safePreview(task.basePrompt, 1600),
      chars: task.prompt.length,
      lines: task.prompt.split(/\r?\n/u).length,
    },
    lastAssistantMessage: lastAssistantMessage(task),
    finalResponse: task.finalResponse,
    finalResponseChars: task.finalResponse.length,
    lastJudge: task.lastJudge,
    lastError: task.lastError,
    errorCount: taskStats.errorCount,
    timing: taskTiming(task),
  };
}

async function createTasks(req: Request): Promise<Response> {
  if (!schemaReady) return jsonResponse({ ok: false, error: "code-queue-mgr database schema is not ready", schemaLastError }, 503);
  const body = await readJson(req);
  const record = asRecord(body) ?? {};
  const batchQueueId = typeof record.queueId === "string" && record.queueId.trim().length > 0 ? normalizeQueueId(record.queueId) : undefined;
  const rawTasks = Array.isArray(record.tasks) ? record.tasks : [body];
  const tasks = await Promise.all(rawTasks.map(async (item) => {
    const next = asRecord(item) === null ? item : { ...(asRecord(item) as Record<string, unknown>) };
    if (batchQueueId !== undefined && asRecord(next)?.queueId === undefined) (next as Record<string, unknown>).queueId = batchQueueId;
    return await createTaskFromRequest(next);
  }));
  await mgrSql.begin(async (client) => {
    const queues = new Map<string, QueueRecord>();
    for (const task of tasks) queues.set(queueIdOf(task), { id: queueIdOf(task), name: queueIdOf(task), createdAt: task.createdAt, updatedAt: task.updatedAt });
    for (const queue of queues.values()) await client`
      INSERT INTO unidesk_code_queue_queues (id, name, created_at, updated_at)
      VALUES (${queue.id}, ${queue.name}, ${queue.createdAt}, ${queue.updatedAt})
      ON CONFLICT (id) DO UPDATE SET updated_at = GREATEST(unidesk_code_queue_queues.updated_at, EXCLUDED.updated_at)
    `;
    for (const task of tasks) await upsertTask(client, task);
  });
  log("info", "tasks_enqueued", { ids: tasks.map((task) => task.id), queueIds: Array.from(new Set(tasks.map(queueIdOf))) });
  return jsonResponse({ ok: true, tasks: tasks.map((task) => taskListResponse(task, false)), queue: await queueSummary() }, 202);
}

async function createQueue(req: Request): Promise<Response> {
  const body = asRecord(await readJson(req)) ?? {};
  const now = nowIso();
  const queueId = normalizeQueueId(body.queueId ?? body.id);
  const queue = { id: queueId, name: normalizeQueueName(body.name, queueId), createdAt: now, updatedAt: now };
  await upsertQueue(queue);
  return jsonResponse({ ok: true, queue, queues: (await queueSummary()).queues }, 201);
}

async function updateQueue(queueIdValue: string, req: Request): Promise<Response> {
  const queueId = normalizeQueueId(queueIdValue);
  const body = asRecord(await readJson(req)) ?? {};
  const name = normalizeQueueName(body.name, queueId);
  const rows = await mgrSql<QueueRow[]>`
    UPDATE unidesk_code_queue_queues
    SET name = ${name}, updated_at = ${nowIso()}
    WHERE id = ${queueId}
    RETURNING id, name, created_at, updated_at
  `;
  if (rows[0] === undefined) return jsonResponse({ ok: false, error: "queue not found" }, 404);
  return jsonResponse({ ok: true, queue: queueRowToRecord(rows[0]), queues: (await queueSummary()).queues });
}

async function mergeQueues(targetQueueIdValue: string | null, req: Request): Promise<Response> {
  const body = asRecord(await readJson(req)) ?? {};
  const targetQueueId = normalizeQueueId(targetQueueIdValue ?? body.targetQueueId ?? body.intoQueueId ?? body.into);
  const rawSource = body.sourceQueueIds ?? body.sources ?? body.sourceQueueId ?? body.fromQueueId ?? body.from ?? body.queueId ?? body.id;
  const sourceQueueIds = (Array.isArray(rawSource) ? rawSource : typeof rawSource === "string" ? rawSource.split(/[,\s]+/u) : [])
    .map((item) => normalizeQueueId(item))
    .filter((id) => id !== targetQueueId);
  if (sourceQueueIds.length === 0) return jsonResponse({ ok: false, error: "sourceQueueId is required" }, 400);
  const updatedAt = nowIso();
  const result = await mgrSql.begin(async (client): Promise<{ movedTaskIds: string[]; blocker: TaskStatusRow | null }> => {
    const mergeQueueIds = Array.from(new Set([targetQueueId, ...sourceQueueIds]));
    const lockedRows = await client<TaskStatusRow[]>`
      SELECT id, queue_id, status, started_at, current_attempt, codex_thread_id, active_turn_id
      FROM unidesk_code_queue_tasks
      WHERE queue_id IN ${client(mergeQueueIds)}
      ORDER BY updated_at DESC, id DESC
      FOR UPDATE
    `;
    const blocker = lockedRows.find((row) => queueMergeBlocker(row).length > 0) ?? null;
    if (blocker !== null) return { movedTaskIds: [], blocker };
    await client`
      INSERT INTO unidesk_code_queue_queues (id, name, created_at, updated_at)
      VALUES (${targetQueueId}, ${targetQueueId}, ${updatedAt}, ${updatedAt})
      ON CONFLICT (id) DO UPDATE SET updated_at = EXCLUDED.updated_at
    `;
    const movedRows = await client<Array<{ id: string }>>`
      UPDATE unidesk_code_queue_tasks
      SET
        queue_id = ${targetQueueId},
        updated_at = ${updatedAt},
        task_json = jsonb_set(jsonb_set(task_json, '{queueId}', to_jsonb(${targetQueueId}::text), true), '{updatedAt}', to_jsonb(${updatedAt}::text), true)
      WHERE queue_id IN ${client(sourceQueueIds)}
        AND (
          status IN ('succeeded', 'failed', 'canceled')
          OR (
            status IN ('queued', 'retry_wait')
            AND started_at IS NULL
            AND current_attempt = 0
            AND codex_thread_id IS NULL
            AND active_turn_id IS NULL
          )
        )
      RETURNING id
    `;
    await client`DELETE FROM unidesk_code_queue_queues WHERE id IN ${client(sourceQueueIds)}`;
    return { movedTaskIds: movedRows.map((row) => row.id), blocker: null };
  });
  if (result.blocker !== null) {
    const blocker = result.blocker;
    const queueId = safeQueueId(blocker.queue_id);
    return jsonResponse({
      ok: false,
      error: `cannot merge queue ${queueId}: ${queueMergeBlocker(blocker)}`,
      blocker: taskStatusRowJson(blocker),
    }, 409);
  }
  return jsonResponse({ ok: true, targetQueueId, sourceQueueIds, movedTaskIds: result.movedTaskIds, queue: await queueSummary() }, 202);
}

async function markTaskRead(taskId: string): Promise<Response> {
  const readAt = nowIso();
  const rows = await mgrSql<Array<{ id: string; status: TaskStatus }>>`
    UPDATE unidesk_code_queue_tasks
    SET
      read_at = ${readAt},
      task_json = jsonb_set(task_json, '{readAt}', to_jsonb(${readAt}::text), true)
    WHERE id = ${taskId}
      AND status IN ('succeeded', 'failed', 'canceled')
      AND read_at IS NULL
    RETURNING id, status
  `;
  if (rows[0] === undefined) {
    const task = await loadTask(taskId);
    if (task === null) return jsonResponse({ ok: false, error: "task not found" }, 404);
    if (!terminalTask(task)) return jsonResponse({ ok: false, error: `task is not terminal: ${task.status}` }, 409);
    return jsonResponse({ ok: true, task: taskListResponse(task), queue: await queueSummary() });
  }
  return jsonResponse({ ok: true, task: { id: taskId, readAt, terminalUnread: false }, queue: await queueSummary() });
}

async function markAllRead(url: URL): Promise<Response> {
  const readAt = nowIso();
  const queueIdRaw = url.searchParams.get("queueId");
  const queueId = queueIdRaw === null || queueIdRaw.length === 0 ? null : safeQueueId(queueIdRaw);
  const rows = await mgrSql<Array<{ id: string }>>`
    UPDATE unidesk_code_queue_tasks
    SET
      read_at = ${readAt},
      task_json = jsonb_set(task_json, '{readAt}', to_jsonb(${readAt}::text), true)
    WHERE (${queueId === null} OR queue_id = ${queueId})
      AND status IN ('succeeded', 'failed', 'canceled')
      AND read_at IS NULL
    RETURNING id
  `;
  return jsonResponse({ ok: true, count: rows.length, readAt, queue: await queueSummary() });
}

async function moveTask(taskId: string, req: Request): Promise<Response> {
  const body = asRecord(await readJson(req)) ?? {};
  const queueId = normalizeQueueId(body.queueId ?? body.id);
  const movedAt = nowIso();
  const result = await mgrSql.begin(async (client): Promise<{ task: QueueTask | null; blocker: string; row: TaskStatusRow | null }> => {
    const rows = await client<Array<TaskJsonRow & TaskStatusRow>>`
      SELECT
        id,
        queue_id,
        status,
        current_attempt,
        current_mode,
        codex_thread_id,
        active_turn_id,
        updated_at,
        started_at,
        finished_at,
        read_at,
        task_json
      FROM unidesk_code_queue_tasks
      WHERE id = ${taskId}
      FOR UPDATE
    `;
    const row = rows[0] ?? null;
    const blocker = taskStatusMoveBlocker(row);
    if (row === null || blocker.length > 0) return { task: row === null ? null : rowToTask(row), blocker, row };
    await client`
      INSERT INTO unidesk_code_queue_queues (id, name, created_at, updated_at)
      VALUES (${queueId}, ${queueId}, ${movedAt}, ${movedAt})
      ON CONFLICT (id) DO UPDATE SET updated_at = EXCLUDED.updated_at
    `;
    const previousQueueId = safeQueueId(row.queue_id);
    const nextTask = rowToTask(row);
    nextTask.queueId = queueId;
    nextTask.queueEnteredAt = movedAt;
    nextTask.updatedAt = movedAt;
    nextTask.output.push({ seq: outputMaxSeq(nextTask) + 1, at: movedAt, channel: "system", text: `moved from queue=${previousQueueId} to queue=${queueId}\n`, method: "queue/move" });
    nextTask.outputMaxSeq = outputMaxSeq(nextTask);
    const updated = await client<TaskStatusRow[]>`
      UPDATE unidesk_code_queue_tasks
      SET
        queue_id = ${queueId},
        updated_at = ${movedAt},
        output_count = ${nextTask.output.length},
        event_count = ${nextTask.events.length},
        attempt_count = ${nextTask.attempts.length},
        last_output_seq = ${outputMaxSeq(nextTask)},
        task_json = ${client.json(taskJson(nextTask) as unknown as postgres.JSONValue)}
      WHERE id = ${taskId}
        AND status IN ('queued', 'retry_wait')
        AND started_at IS NULL
        AND current_attempt = 0
        AND codex_thread_id IS NULL
        AND active_turn_id IS NULL
      RETURNING id, queue_id, status, started_at, current_attempt, codex_thread_id, active_turn_id
    `;
    if (updated[0] === undefined) return { task: rowToTask(row), blocker: "conditional update matched no rows", row };
    return { task: nextTask, blocker: "", row: updated[0] };
  });
  if (result.task === null) return jsonResponse({ ok: false, error: "task not found" }, 404);
  if (result.blocker.length > 0) {
    return jsonResponse({
      ok: false,
      error: `cannot move task ${taskId}: ${result.blocker}`,
      blocker: taskStatusRowJson(result.row),
      task: taskListResponse(result.task),
    }, 409);
  }
  return jsonResponse({ ok: true, task: taskListResponse(result.task, false), blocker: taskStatusRowJson(result.row), queue: await queueSummary() }, 202);
}

async function runQueueClaimMoveSelfTest(): Promise<JsonRecord> {
  const suffix = String(Date.now());
  const taskId = `codex_mgr_claim_move_${suffix}`;
  const sourceQueueId = `mgr_claim_move_source_${suffix}`;
  const targetQueueId = `mgr_claim_move_target_${suffix}`;
  const queuedAt = nowIso();
  await mgrSql`DELETE FROM unidesk_code_queue_tasks WHERE id = ${taskId}`;
  try {
    const queuedTask = normalizeTask(await createTaskFromRequest({
      prompt: "Code Queue manager claim/move race self-test",
      queueId: sourceQueueId,
      maxAttempts: 1,
    }));
    queuedTask.id = taskId;
    queuedTask.queueId = sourceQueueId;
    queuedTask.queueEnteredAt = queuedAt;
    queuedTask.createdAt = queuedAt;
    queuedTask.updatedAt = queuedAt;
    await mgrSql.begin(async (client) => {
      await client`
        INSERT INTO unidesk_code_queue_queues (id, name, created_at, updated_at)
        VALUES (${sourceQueueId}, ${sourceQueueId}, ${queuedAt}, ${queuedAt})
        ON CONFLICT (id) DO UPDATE SET updated_at = EXCLUDED.updated_at
      `;
      await upsertTask(client, queuedTask);
    });

    const claimedAt = nowIso();
    const claimedTask = normalizeTask(JSON.parse(JSON.stringify(queuedTask)) as QueueTask);
    claimedTask.status = "running";
    claimedTask.startedAt = claimedAt;
    claimedTask.currentAttempt = 1;
    claimedTask.currentMode = "initial";
    claimedTask.codexThreadId = `thread_${suffix}`;
    claimedTask.activeTurnId = `turn_${suffix}`;
    claimedTask.updatedAt = claimedAt;
    await mgrSql.begin(async (client) => {
      await upsertTask(client, claimedTask);
    });

    const moveResponse = await moveTask(taskId, new Request(`http://code-queue-mgr.local/api/tasks/${taskId}/move`, {
      method: "POST",
      body: JSON.stringify({ queueId: targetQueueId }),
      headers: { "content-type": "application/json" },
    }));
    const moveBody = await moveResponse.json() as JsonRecord;
    const rows = await mgrSql<Array<TaskJsonRow & TaskStatusRow>>`
      SELECT id, queue_id, status, current_attempt, current_mode, codex_thread_id, active_turn_id, updated_at, started_at, finished_at, read_at, task_json
      FROM unidesk_code_queue_tasks
      WHERE id = ${taskId}
      LIMIT 1
    `;
    const after = rows[0] === undefined ? null : rowToTask(rows[0]);
    const ok = moveResponse.status === 409
      && after !== null
      && after.status === "running"
      && queueIdOf(after) === sourceQueueId
      && after.currentAttempt === 1
      && after.startedAt !== null
      && after.codexThreadId !== null
      && after.activeTurnId !== null;
    if (!ok) {
      throw new Error(`claim/move self-test failed: moveStatus=${moveResponse.status}, after=${JSON.stringify(after === null ? null : taskListResponse(after))}`);
    }
    return {
      ok: true,
      taskId,
      sourceQueueId,
      targetQueueId,
      moveStatus: moveResponse.status,
      databaseStatus: after.status,
      databaseQueueId: queueIdOf(after),
      currentAttempt: after.currentAttempt,
      startedAt: after.startedAt,
      codexThreadId: after.codexThreadId,
      activeTurnId: after.activeTurnId,
      moveResponse: moveBody,
    };
  } finally {
    await mgrSql`DELETE FROM unidesk_code_queue_tasks WHERE id = ${taskId}`;
    await mgrSql`DELETE FROM unidesk_code_queue_queues WHERE id IN ${mgrSql([sourceQueueId, targetQueueId])}`;
  }
}

async function retryTask(taskId: string, req: Request): Promise<Response> {
  const body = asRecord(await readJson(req)) ?? {};
  const explicitPrompt = typeof body.prompt === "string" ? body.prompt.trim() : typeof body.continuePrompt === "string" ? body.continuePrompt.trim() : "";
  const queuedAt = nowIso();
  const result = await mgrSql.begin(async (client): Promise<{ task: QueueTask | null; changed: boolean }> => {
    const rows = await client<Array<TaskJsonRow & TaskStatusRow>>`
      SELECT
        id,
        queue_id,
        status,
        current_attempt,
        current_mode,
        codex_thread_id,
        active_turn_id,
        updated_at,
        started_at,
        finished_at,
        read_at,
        task_json
      FROM unidesk_code_queue_tasks
      WHERE id = ${taskId}
      FOR UPDATE
    `;
    if (rows[0] === undefined) return { task: null, changed: false };
    const nextTask = rowToTask(rows[0]);
    const isTerminal = terminalTask(nextTask);
    const isClaimedQueueRetry = retryableClaimedQueueState(nextTask);
    if (!isTerminal && !isClaimedQueueRetry) return { task: nextTask, changed: false };
    nextTask.status = hasClaimMarkers(nextTask) ? "retry_wait" : "queued";
    nextTask.finishedAt = null;
    nextTask.readAt = null;
    nextTask.cancelRequested = false;
    nextTask.lastError = null;
    nextTask.maxAttempts = Math.max(nextTask.maxAttempts, nextTask.currentAttempt + 1, nextTask.attempts.length + 1);
    nextTask.nextMode = "retry";
    nextTask.nextPrompt = explicitPrompt.length > 0 ? explicitPrompt : "Manual retry requested from master code-queue-mgr. Continue the original task and produce a visible final response.";
    nextTask.updatedAt = queuedAt;
    nextTask.queueEnteredAt = queuedAt;
    const retryText = isClaimedQueueRetry
      ? "claimed queued task normalized to retry_wait by master code-queue-mgr\n"
      : "manual retry queued by master code-queue-mgr\n";
    nextTask.output.push({ seq: outputMaxSeq(nextTask) + 1, at: queuedAt, channel: "system", text: retryText, method: "manual-retry" });
    nextTask.outputMaxSeq = outputMaxSeq(nextTask);
    await upsertTask(client, nextTask);
    return { task: nextTask, changed: true };
  });
  const task = result.task;
  if (task === null) return jsonResponse({ ok: false, error: "task not found" }, 404);
  if (!result.changed) return jsonResponse({ ok: false, error: `task cannot be retried from status=${task.status}`, task: taskListResponse(task) }, 409);
  return jsonResponse({ ok: true, task: taskListResponse(task, false), queue: await queueSummary() }, 202);
}

async function editTask(taskId: string, req: Request): Promise<Response> {
  const body = asRecord(await readJson(req)) ?? {};
  const editedAt = nowIso();
  const result = await mgrSql.begin(async (client): Promise<{ task: QueueTask | null; changed: boolean }> => {
    const rows = await client<Array<TaskJsonRow & TaskStatusRow>>`
      SELECT
        id,
        queue_id,
        status,
        current_attempt,
        current_mode,
        codex_thread_id,
        active_turn_id,
        updated_at,
        started_at,
        finished_at,
        read_at,
        task_json
      FROM unidesk_code_queue_tasks
      WHERE id = ${taskId}
      FOR UPDATE
    `;
    if (rows[0] === undefined) return { task: null, changed: false };
    const nextTask = rowToTask(rows[0]);
    if (!queuedTaskPromptEditable(nextTask)) return { task: nextTask, changed: false };
    const prepared = await preparePromptFromRecord(body, nextTask.referenceTaskIds);
    if (prepared.referenceTaskIds.includes(nextTask.id)) throw new Error("a task cannot reference itself while editing prompt");
    nextTask.prompt = prepared.prompt;
    nextTask.basePrompt = prepared.basePrompt;
    nextTask.referenceTaskIds = prepared.referenceTaskIds;
    nextTask.referenceInjection = prepared.referenceInjection;
    nextTask.updatedAt = editedAt;
    const enqueue = nextTask.output.find((item) => item.method === "enqueue");
    if (enqueue !== undefined) {
      enqueue.at = editedAt;
      enqueue.text = `${prepared.prompt}\n`;
    }
    nextTask.output.push({ seq: outputMaxSeq(nextTask) + 1, at: editedAt, channel: "system", text: "queued prompt edited by master code-queue-mgr\n", method: "prompt/edit" });
    nextTask.outputMaxSeq = outputMaxSeq(nextTask);
    await upsertTask(client, nextTask);
    return { task: nextTask, changed: true };
  });
  const task = result.task;
  if (task === null) return jsonResponse({ ok: false, error: "task not found" }, 404);
  if (!result.changed) return jsonResponse({ ok: false, error: `task prompt can only be edited before first run while status=queued; current status=${task.status}`, task: taskListResponse(task) }, 409);
  return jsonResponse({ ok: true, changed: true, editable: true, task: taskListResponse(task, false), queue: await queueSummary() });
}

async function tasksOverview(url: URL): Promise<Response> {
  const { tasks, total, limit, offset } = await loadTasksForList(url);
  const queues = await loadQueues();
  const summary = await queueSummary(undefined, queues);
  const selectedId = url.searchParams.get("preferId") || tasks[0]?.id || "";
  const selectedTask = selectedId.length > 0 ? await loadTask(selectedId) : null;
  const transcriptLimit = Math.max(1, Math.min(500, numberField(url.searchParams.get("transcriptLimit"), 80)));
  const afterSeq = numberField(url.searchParams.get("afterSeq"), 0);
  const selected = selectedTask === null ? null : {
    task: taskMetaResponse(selectedTask),
    transcript: selectedTask.output.filter((item) => item.seq > afterSeq).slice(0, transcriptLimit).map((item) => ({
      seq: item.seq,
      at: item.at,
      kind: item.channel === "command" ? "ran" : item.channel === "diff" ? "edited" : item.channel === "tool" ? "explored" : item.channel === "error" ? "error" : "message",
      title: item.method ?? item.channel,
      status: item.method ?? null,
      bodyPreview: safePreview(item.text, 1200),
      rawSeqs: [item.seq],
    })),
    afterSeq,
    nextAfterSeq: selectedTask.output.at(-1)?.seq ?? afterSeq,
    hasMore: false,
    preview: true,
    total: selectedTask.output.length,
    maxSeq: outputMaxSeq(selectedTask),
  };
  return jsonResponse({
    ok: true,
    queue: summary,
    statistics: { days: 14, source: "code-queue-mgr", skipped: true },
    tasks: tasks.map((task) => taskListResponse(task, true)),
    selected,
    pagination: {
      limit,
      returned: tasks.length,
      total,
      hasMore: offset + tasks.length < total,
      nextBeforeId: null,
      beforeId: null,
      includeActive: false,
    },
  });
}

function httpError(status: number, error: string, detail: JsonRecord = {}): HttpErrorDetail {
  return { status, body: { ok: false, error, ...detail } };
}

function routeActiveControl(pathname: string, method: string): HttpErrorDetail | null {
  if (/^\/api\/tasks\/[^/]+\/(?:steer|interrupt)$/u.test(pathname)) {
    return httpError(409, "active run control remains on D601 scheduler", { method, schedulerPath: pathname });
  }
  if (/^\/api\/tasks\/[^/]+$/u.test(pathname) && method === "DELETE") {
    return httpError(409, "active run interrupt remains on D601 scheduler", { method, schedulerPath: pathname });
  }
  return null;
}

async function route(req: Request): Promise<Response> {
  const url = new URL(req.url);
  if (req.method === "OPTIONS") return new Response(null, { status: 204 });
  try {
    if (url.pathname === "/" || url.pathname === "/health" || url.pathname === "/live") {
      const taskCount = schemaReady ? Number((await traceSql<Array<{ count: string | number }>>`SELECT COUNT(*) AS count FROM unidesk_code_queue_tasks`)[0]?.count ?? 0) : null;
      const queue = schemaReady ? await queueSummary() : null;
      return jsonResponse({
        ok: schemaReady,
        service: "code-queue-mgr",
        role: "master-control-plane",
        deploy: {
          serviceId: config.deployServiceId,
          repo: config.deployRepo,
          commit: config.deployCommit,
          requestedCommit: config.deployRequestedCommit,
        },
        startedAt: serviceStartedAt,
        schemaReady,
        schemaLastError,
        taskCount,
        queue,
        resourceBudget: {
          targetMemoryMb: 100,
          mgrPoolMax: config.mgrDatabasePoolMax,
          tracePoolMax: config.traceDatabasePoolMax,
          noRunnerDependencies: true,
          noPlaywright: true,
          noCodexRuntime: true,
          noDockerSocket: true,
        },
        endpoints: {
          control: ["/api/queues", "/api/tasks", "/api/tasks/:id/(move|retry|read|edit)", "/api/workdirs", "/api/queue-claim-move/self-test"],
          traceRead: ["/api/tasks/overview", "/api/tasks/:id/summary", "/api/tasks/:id/trace-summary", "/api/tasks/:id/trace-steps", "/api/tasks/:id/output"],
        },
      }, schemaReady ? 200 : 503);
    }
    if (url.pathname === "/logs" && req.method === "GET") return jsonResponse({ ok: true, logs: recentLogs.slice(-100) });
    if (url.pathname === "/api/queue-claim-move/self-test" && (req.method === "POST" || req.method === "GET")) {
      if (!schemaReady) return jsonResponse({ ok: false, error: "code-queue-mgr database schema is not ready", schemaLastError }, 503);
      return jsonResponse(await runQueueClaimMoveSelfTest(), 200);
    }
    if (url.pathname === "/api/workdirs" && req.method === "GET") return await listWorkdirs(url);
    if (url.pathname === "/api/workdirs" && req.method === "POST") return await createWorkdir(req);
    const workdirMatch = url.pathname.match(/^\/api\/workdirs\/([^/]+)\/([^/]+)\/(.+)$/u);
    if (workdirMatch !== null && req.method === "DELETE") return await deleteWorkdir(decodeURIComponent(workdirMatch[1] ?? ""), decodeURIComponent(workdirMatch[2] ?? ""), decodeURIComponent(workdirMatch[3] ?? ""));
    const activeControl = routeActiveControl(url.pathname, req.method);
    if (activeControl !== null) return jsonResponse(activeControl.body, activeControl.status);
    if (url.pathname === "/api/queues" && req.method === "GET") {
      const tasks = await loadAllTasksLite();
      const queues = await loadQueues();
      return jsonResponse({ ok: true, queues: (await queueSummary(tasks, queues)).queues, queue: await queueSummary(tasks, queues) });
    }
    if (url.pathname === "/api/queues" && req.method === "POST") return await createQueue(req);
    if (url.pathname === "/api/queues/merge" && req.method === "POST") return await mergeQueues(null, req);
    const queueMergeMatch = url.pathname.match(/^\/api\/queues\/([^/]+)\/merge$/u);
    if (queueMergeMatch !== null && req.method === "POST") return await mergeQueues(decodeURIComponent(queueMergeMatch[1] ?? ""), req);
    const queueMatch = url.pathname.match(/^\/api\/queues\/([^/]+)$/u);
    if (queueMatch !== null && (req.method === "PATCH" || req.method === "PUT" || req.method === "POST")) return await updateQueue(decodeURIComponent(queueMatch[1] ?? ""), req);
    if (url.pathname === "/api/tasks/overview" && req.method === "GET") return await tasksOverview(url);
    if (url.pathname === "/api/tasks/stats" && req.method === "GET") {
      const queueIdRaw = url.searchParams.get("queueId");
      const queueId = queueIdRaw === null || queueIdRaw.length === 0 ? null : safeQueueId(queueIdRaw);
      const allTasks = await loadAllTasksLite();
      const statsTasks = queueId === null ? allTasks : allTasks.filter((task) => queueIdOf(task) === queueId);
      return jsonResponse({ ok: true, statistics: taskStatisticsSummary(statsTasks, statsDaysFromUrl(url)), queue: await queueSummary(allTasks) });
    }
    if (url.pathname === "/api/tasks" && req.method === "GET") {
      const page = await loadTasksForList(url);
      return jsonResponse({ ok: true, tasks: page.tasks.map((task) => taskListResponse(task, url.searchParams.get("lite") === "1")), queue: await queueSummary(), pagination: { limit: page.limit, offset: page.offset, total: page.total, returned: page.tasks.length, hasMore: page.offset + page.tasks.length < page.total } });
    }
    if ((url.pathname === "/api/tasks" || url.pathname === "/api/tasks/batch") && req.method === "POST") return await createTasks(req);
    if (url.pathname === "/api/tasks/read-all" && req.method === "POST") return await markAllRead(url);
    const outputMatch = url.pathname.match(/^\/api\/tasks\/([^/]+)\/output$/u);
    if (outputMatch !== null && req.method === "GET") {
      const task = await loadTask(decodeURIComponent(outputMatch[1] ?? ""));
      return task === null ? jsonResponse({ ok: false, error: "task not found" }, 404) : jsonResponse(outputPage(task, url));
    }
    const transcriptMatch = url.pathname.match(/^\/api\/tasks\/([^/]+)\/transcript$/u);
    if (transcriptMatch !== null && req.method === "GET") {
      const task = await loadTask(decodeURIComponent(transcriptMatch[1] ?? ""));
      return task === null ? jsonResponse({ ok: false, error: "task not found" }, 404) : jsonResponse(transcriptPage(task, url));
    }
    const traceSummaryMatch = url.pathname.match(/^\/api\/tasks\/([^/]+)\/trace-summary$/u);
    if (traceSummaryMatch !== null && req.method === "GET") {
      const task = await loadTask(decodeURIComponent(traceSummaryMatch[1] ?? ""));
      return task === null ? jsonResponse({ ok: false, error: "task not found" }, 404) : jsonResponse({ ok: true, summary: await traceSummary(task) });
    }
    const traceStepsMatch = url.pathname.match(/^\/api\/tasks\/([^/]+)\/trace-steps$/u);
    if (traceStepsMatch !== null && req.method === "GET") {
      const task = await loadTask(decodeURIComponent(traceStepsMatch[1] ?? ""));
      return task === null ? jsonResponse({ ok: false, error: "task not found" }, 404) : jsonResponse(await traceSteps(task, url));
    }
    const traceStepMatch = url.pathname.match(/^\/api\/tasks\/([^/]+)\/trace-step$/u);
    if (traceStepMatch !== null && req.method === "GET") {
      const task = await loadTask(decodeURIComponent(traceStepMatch[1] ?? ""));
      if (task === null) return jsonResponse({ ok: false, error: "trace step not found" }, 404);
      const detail = await traceStepDetail(task, url);
      return detail === null ? jsonResponse({ ok: false, error: "trace step not found" }, 404) : jsonResponse(detail);
    }
    const summaryMatch = url.pathname.match(/^\/api\/tasks\/([^/]+)\/summary$/u);
    if (summaryMatch !== null && req.method === "GET") {
      const task = await loadTask(decodeURIComponent(summaryMatch[1] ?? ""));
      return task === null ? jsonResponse({ ok: false, error: "task not found" }, 404) : jsonResponse({ ok: true, summary: taskSummary(task, url) });
    }
    const promptMatch = url.pathname.match(/^\/api\/tasks\/([^/]+)\/prompt$/u);
    if (promptMatch !== null && req.method === "GET") {
      const task = await loadTask(decodeURIComponent(promptMatch[1] ?? ""));
      if (task === null) return jsonResponse({ ok: false, error: "task not found" }, 404);
      const part = url.searchParams.get("part") ?? "initial";
      const text = part === "base" ? task.basePrompt : task.prompt;
      return jsonResponse({ ok: true, taskId: task.id, part, text, chars: text.length, lines: text.split(/\r?\n/u).length });
    }
    if (promptMatch !== null && req.method === "PATCH") return await editTask(decodeURIComponent(promptMatch[1] ?? ""), req);
    const taskActionMatch = url.pathname.match(/^\/api\/tasks\/([^/]+)(?:\/(retry|move|read|edit))?$/u);
    if (taskActionMatch !== null) {
      const taskId = decodeURIComponent(taskActionMatch[1] ?? "");
      const action = taskActionMatch[2] ?? "";
      if (action === "retry" && req.method === "POST") return await retryTask(taskId, req);
      if (action === "move" && req.method === "POST") return await moveTask(taskId, req);
      if (action === "read" && req.method === "POST") return await markTaskRead(taskId);
      if (action === "edit" && (req.method === "POST" || req.method === "PATCH")) return await editTask(taskId, req);
      if (action.length === 0 && req.method === "GET") {
        const task = await loadTask(taskId);
        if (task === null) return jsonResponse({ ok: false, error: "task not found" }, 404);
        if (url.searchParams.get("meta") === "1") return jsonResponse({ ok: true, task: taskMetaResponse(task) });
        return jsonResponse({ ok: true, task: { ...task, ...taskMetaResponse(task), output: task.output, events: task.events } });
      }
    }
    return jsonResponse({ ok: false, error: "not found", path: url.pathname }, 404);
  } catch (error) {
    lastDatabaseError = error instanceof Error ? error.message : String(error);
    log("error", "request_failed", { path: url.pathname, method: req.method, error: errorToJson(error) });
    if (error instanceof SyntaxError) return jsonResponse({ ok: false, error: "invalid JSON request body", detail: error.message }, 400);
    return jsonResponse({ ok: false, error: error instanceof Error ? error.message : String(error) }, 500);
  }
}

void ensureSchemaWithRetry();
Bun.serve({ hostname: config.host, port: config.port, fetch: route, idleTimeout: 60 });
log("info", "service_listening", { port: config.port, role: "master-control-plane", mgrPoolMax: config.mgrDatabasePoolMax, tracePoolMax: config.traceDatabasePoolMax });
