import { createHash, randomUUID } from "node:crypto";
import postgres from "postgres";
import { createHourlyJsonlWriter, logRetentionBytesForService } from "../../../shared/src/rotating-jsonl";
import {
  DocumentContractError,
  buildDocumentNumber,
  decisionDocumentPriorities,
  decisionDocumentTypes,
  extractDocumentNumberFromLegacy,
  normalizeDocumentNumberList,
  parseDocumentNumber,
  parseDocumentPriority,
  parseDocumentSequence,
  parseDocumentType,
  parseDocumentYear,
  parseIssuedAt,
  tryParseDocumentNumber,
  type DecisionDocumentNumber,
  type DecisionDocumentPriority,
  type DecisionDocumentType,
} from "./document-contract";

type JsonValue = string | number | boolean | null | JsonValue[] | { [key: string]: JsonValue };
type JsonRecord = Record<string, JsonValue>;

type DecisionRecordType = "meeting" | "decision" | "goal" | "external_goal" | "internal_goal" | "blocker" | "debt" | "experiment";
type RequirementRecordType = Exclude<DecisionRecordType, "meeting">;
type DecisionRecordLevel = "G0" | "G1" | "G2" | "G3" | "P0" | "P1" | "P2" | "P3" | "none";
type DecisionRecordStatus = "active" | "blocked" | "parked" | "done";
type SqlExecutor = postgres.Sql | postgres.TransactionSql;

interface RuntimeConfig {
  host: string;
  port: number;
  databaseUrl: string;
  logFile: string;
  databasePoolMax: number;
}

interface DecisionRecordRow {
  id: string;
  type: DecisionRecordType;
  level: DecisionRecordLevel;
  status: DecisionRecordStatus;
  title: string;
  body: string;
  linked_goal_id: string | null;
  tags: JsonValue;
  evidence_links: JsonValue;
  doc_no: string;
  doc_type: string;
  doc_priority: string;
  doc_year: number | null;
  doc_seq: number | null;
  signer: string;
  issued_at: Date | string | null;
  effective_scope: string;
  supersedes: JsonValue;
  superseded_by: JsonValue;
  source: string;
  source_session: string;
  issue_id: string;
  task_id: string;
  commit_id: string;
  created_at: Date | string;
  updated_at: Date | string;
}

interface DecisionRecord extends JsonRecord {
  id: string;
  type: DecisionRecordType;
  level: DecisionRecordLevel;
  status: DecisionRecordStatus;
  title: string;
  summary: string;
  body: string;
  priority: DecisionRecordLevel;
  linkedGoalId: string | null;
  tags: string[];
  evidenceLinks: string[];
  docNo: string;
  docType: string;
  docPriority: string;
  docYear: number | null;
  docSeq: number | null;
  signer: string;
  issuedAt: string;
  effectiveScope: string;
  supersedes: string[];
  supersededBy: string[];
  source: string;
  sourceSession: string;
  issueId: string;
  taskId: string;
  commitId: string;
  createdAt: string;
  updatedAt: string;
}

interface DiaryEntryRow {
  id: string;
  entry_date: Date | string;
  month: string;
  title: string;
  summary: string;
  body: string;
  source_file: string;
  markdown_path: string;
  tags: JsonValue;
  content_hash: string;
  created_at: Date | string;
  updated_at: Date | string;
  imported_at: Date | string;
}

interface DiaryEntry extends JsonRecord {
  id: string;
  date: string;
  month: string;
  title: string;
  summary: string;
  body: string;
  sourceFile: string;
  markdownPath: string;
  tags: string[];
  contentHash: string;
  createdAt: string;
  updatedAt: string;
  importedAt: string;
}

interface DiaryDraft {
  date: string;
  month: string;
  title: string;
  body: string;
  markdownPath: string;
}

interface DiaryUpsertRow extends DiaryEntryRow {
  inserted: boolean;
  changed: boolean;
}

class HttpError extends Error {
  readonly status: number;
  readonly detail: JsonRecord;

  constructor(status: number, message: string, detail: JsonRecord = {}) {
    super(message);
    this.name = "HttpError";
    this.status = status;
    this.detail = detail;
  }
}

const recordTypes = new Set<DecisionRecordType>(["meeting", "decision", "goal", "external_goal", "internal_goal", "blocker", "debt", "experiment"]);
const requirementRecordTypes = new Set<RequirementRecordType>(["decision", "goal", "external_goal", "internal_goal", "blocker", "debt", "experiment"]);
const recordLevels = new Set<DecisionRecordLevel>(["G0", "G1", "G2", "G3", "P0", "P1", "P2", "P3", "none"]);
const recordStatuses = new Set<DecisionRecordStatus>(["active", "blocked", "parked", "done"]);
const serviceStartedAt = new Date().toISOString();
const recentLogs: JsonRecord[] = [];
let schemaReady = false;
let schemaLastError: JsonRecord | null = null;
let databaseHealth: JsonRecord = { ok: false, recordCount: 0, checkedAt: "", lastOkAt: "", degraded: false, error: null };
let databaseHealthRefreshInFlight = false;

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 configFromEnv(): RuntimeConfig {
  const databaseUrl = process.env.DATABASE_URL || "";
  if (!databaseUrl) throw new Error("DATABASE_URL is required");
  return {
    host: envString("HOST", "0.0.0.0"),
    port: envNumber("PORT", 4277),
    databaseUrl,
    logFile: envString("LOG_FILE", "/var/log/unidesk/decision-center.jsonl"),
    databasePoolMax: Math.max(1, Math.min(8, envNumber("DATABASE_POOL_MAX", 2))),
  };
}

const config = configFromEnv();
type SqlClient = ReturnType<typeof postgres>;

function createSqlClient(): SqlClient {
  return postgres(config.databaseUrl, {
    max: config.databasePoolMax,
    idle_timeout: 20,
    connect_timeout: 5,
    connection: { application_name: "unidesk-decision-center" },
  });
}

let sql = createSqlClient();
const logWriter = config.logFile
  ? createHourlyJsonlWriter({
      baseLogFile: config.logFile,
      service: "decision-center",
      maxBytes: logRetentionBytesForService("decision-center"),
    })
  : null;
logWriter?.prune();

function log(level: "info" | "warn" | "error", event: string, detail: JsonRecord = {}): void {
  const record: JsonRecord = { at: new Date().toISOString(), service: "decision-center", level, event, ...detail };
  recentLogs.push(record);
  while (recentLogs.length > 300) recentLogs.shift();
  try {
    logWriter?.appendJson(record, new Date(String(record.at)));
  } catch {
    // Logging must not break decision writes.
  }
  const line = JSON.stringify(record);
  const writer = level === "error" ? console.error : level === "warn" ? console.warn : console.log;
  writer(line);
}

function jsonResponse(body: JsonValue, status = 200): Response {
  return new Response(JSON.stringify(body), {
    status,
    headers: { "content-type": "application/json; charset=utf-8" },
  });
}

function errorToJson(error: unknown): JsonRecord {
  if (error instanceof HttpError) return { name: error.name, message: error.message, status: error.status, detail: error.detail };
  if (error instanceof DocumentContractError) return { name: error.name, message: error.message, status: 400, detail: error.detail };
  if (error instanceof Error) return { name: error.name, message: error.message, stack: error.stack || "" };
  return { message: String(error) };
}

function databaseErrorToHttp(error: unknown): HttpError | null {
  const record = typeof error === "object" && error !== null ? error as Record<string, unknown> : {};
  const code = typeof record.code === "string" ? record.code : "";
  const constraint = typeof record.constraint_name === "string"
    ? record.constraint_name
    : typeof record.constraint === "string"
      ? record.constraint
      : "";
  const message = error instanceof Error ? error.message : String(error);
  if (code === "23505" && /doc_no|document/iu.test(`${constraint} ${message}`)) {
    return new HttpError(409, "document number already exists", {
      code: "doc_no_conflict",
      constraint,
    });
  }
  return null;
}

function responseError(error: unknown): HttpError | DocumentContractError | unknown {
  return databaseErrorToHttp(error) ?? error;
}

function errorResponse(error: unknown): Response {
  const normalized = responseError(error);
  const status = normalized instanceof HttpError ? normalized.status : normalized instanceof DocumentContractError ? 400 : 500;
  const detail = normalized instanceof HttpError || normalized instanceof DocumentContractError ? normalized.detail : {};
  const message = normalized instanceof Error ? normalized.message : String(normalized);
  const body = {
    ok: false,
    error: message,
    ...detail,
    errorInfo: {
      name: normalized instanceof Error ? normalized.name : "Error",
      message,
      status,
      detail,
    },
  };
  log(status >= 500 ? "error" : "warn", "request_failed", { status, error: errorToJson(normalized) });
  return jsonResponse(body, status);
}

function shouldResetSqlClient(error: unknown): boolean {
  if (error instanceof HttpError) return false;
  const message = error instanceof Error ? `${error.name} ${error.message}` : String(error);
  return /CONNECT_TIMEOUT|ECONN|ETIMEDOUT|EPIPE|connection|terminated|closed|offset is out of range/iu.test(message);
}

async function resetSqlClient(operation: string, error: unknown, failedClient: SqlClient): Promise<void> {
  if (!shouldResetSqlClient(error) || failedClient !== sql) return;
  const previous = sql;
  sql = createSqlClient();
  log("warn", "postgres_client_reset", { operation, error: errorToJson(error) });
  try {
    await previous.end({ timeout: 1 });
  } catch (closeError) {
    log("warn", "postgres_client_close_failed", { operation, error: errorToJson(closeError) });
  }
}

async function withDatabaseRecovery<T>(operation: string, run: () => Promise<T>, options: { retryRead?: boolean } = {}): Promise<T> {
  const failedClient = sql;
  try {
    return await run();
  } catch (error) {
    await resetSqlClient(operation, error, failedClient);
    if (options.retryRead === true && shouldResetSqlClient(error)) return run();
    throw error;
  }
}

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

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

function asString(value: unknown): string {
  if (value === null || value === undefined) return "";
  if (typeof value === "string") return value.trim();
  if (typeof value === "number" || typeof value === "boolean" || typeof value === "bigint") return String(value).trim();
  return "";
}

function asText(value: unknown): string {
  if (value === null || value === undefined) return "";
  if (typeof value === "string") return value;
  if (typeof value === "number" || typeof value === "boolean" || typeof value === "bigint") return String(value);
  return "";
}

function asStringArray(value: unknown, field: string): string[] {
  if (value === null || value === undefined || value === "") return [];
  if (typeof value === "string") {
    return value.split(",").map((item) => item.trim()).filter(Boolean).slice(0, 50);
  }
  if (!Array.isArray(value)) throw new HttpError(400, `${field} must be an array of strings`);
  const items = value.map((item) => asString(item)).filter(Boolean);
  if (items.length > 50) throw new HttpError(400, `${field} must contain at most 50 items`);
  return [...new Set(items)];
}

function jsonStringArray(value: JsonValue): string[] {
  return Array.isArray(value) ? value.map(String).filter(Boolean) : [];
}

function hasAny(input: Record<string, unknown>, fields: string[]): boolean {
  return fields.some((field) => field in input);
}

function currentUtcYear(): number {
  return new Date().getUTCFullYear();
}

function priorityFromLevel(level: DecisionRecordLevel): DecisionDocumentPriority | "" {
  return level === "P0" || level === "P1" || level === "P2" || level === "P3" ? level : "";
}

function documentTypeFromRecordType(type: DecisionRecordType): DecisionDocumentType {
  if (type === "decision") return "DCSN";
  if (type === "goal" || type === "external_goal" || type === "internal_goal") return "GOAL";
  if (type === "meeting") return "MINS";
  if (type === "blocker" || type === "debt") return "ISSU";
  return "RPRT";
}

function existingDocumentNumber(record: DecisionRecord): DecisionDocumentNumber | null {
  return record.docNo ? tryParseDocumentNumber(record.docNo) : null;
}

function inputDocumentNumber(input: Record<string, unknown>): DecisionDocumentNumber | null {
  const raw = asString(input.docNo ?? input.docNumber ?? input.documentNo ?? input.documentNumber);
  return raw ? parseDocumentNumber(raw, "docNo") : null;
}

function assertDocumentComponentsMatch(input: Record<string, unknown>, doc: DecisionDocumentNumber): void {
  if ("docType" in input && parseDocumentType(input.docType) !== doc.docType) throw new HttpError(400, "docType does not match docNo", { docNo: doc.docNo, docType: asString(input.docType), expected: doc.docType });
  if ("docPriority" in input && parseDocumentPriority(input.docPriority) !== doc.docPriority) throw new HttpError(400, "docPriority does not match docNo", { docNo: doc.docNo, docPriority: asString(input.docPriority), expected: doc.docPriority });
  if ("docYear" in input && parseDocumentYear(input.docYear) !== doc.docYear) throw new HttpError(400, "docYear does not match docNo", { docNo: doc.docNo, docYear: asString(input.docYear), expected: doc.docYear });
  if ("docSeq" in input && parseDocumentSequence(input.docSeq) !== doc.docSeq) throw new HttpError(400, "docSeq does not match docNo", { docNo: doc.docNo, docSeq: asString(input.docSeq), expected: doc.docSeq });
}

async function nextDocumentSequence(client: SqlExecutor, docType: DecisionDocumentType, docPriority: DecisionDocumentPriority, docYear: number): Promise<number> {
  await client`LOCK TABLE decision_center_records IN SHARE ROW EXCLUSIVE MODE`;
  const rows = await client<{ next_seq: number }[]>`
    SELECT COALESCE(max(doc_seq), 0)::int + 1 AS next_seq
    FROM decision_center_records
    WHERE doc_type = ${docType}
      AND doc_priority = ${docPriority}
      AND doc_year = ${docYear}
  `;
  return Number(rows[0]?.next_seq ?? 1);
}

function documentInputPresent(input: Record<string, unknown>): boolean {
  return hasAny(input, ["docNo", "docNumber", "documentNo", "documentNumber", "docType", "docPriority", "docYear", "docSeq"]);
}

async function documentNumberForCreate(
  client: SqlExecutor,
  input: Record<string, unknown>,
  draft: { id: string; type: DecisionRecordType; level: DecisionRecordLevel; title: string; body: string; tags: string[] },
): Promise<DecisionDocumentNumber | null> {
  const explicitDocNo = inputDocumentNumber(input);
  if (explicitDocNo !== null) {
    assertDocumentComponentsMatch(input, explicitDocNo);
    return explicitDocNo;
  }

  const legacyDocNo = extractDocumentNumberFromLegacy(draft);
  if (legacyDocNo !== null && !documentInputPresent(input)) return legacyDocNo;

  if (!documentInputPresent(input)) return null;

  const docType = "docType" in input ? parseDocumentType(input.docType) : documentTypeFromRecordType(draft.type);
  const fallbackPriority = priorityFromLevel(draft.level);
  if (!("docPriority" in input) && !fallbackPriority) {
    throw new HttpError(400, "docPriority is required when assigning a document number without docNo", { allowed: [...decisionDocumentPriorities] });
  }
  const docPriority = "docPriority" in input ? parseDocumentPriority(input.docPriority) : fallbackPriority as DecisionDocumentPriority;
  const docYear = "docYear" in input ? parseDocumentYear(input.docYear) : currentUtcYear();
  const docSeq = "docSeq" in input ? parseDocumentSequence(input.docSeq) : await nextDocumentSequence(client, docType, docPriority, docYear);
  return parseDocumentNumber(buildDocumentNumber(docType, docPriority, docYear, docSeq));
}

async function documentNumberForUpdate(
  client: SqlExecutor,
  input: Record<string, unknown>,
  existing: DecisionRecord,
  nextType: DecisionRecordType,
  nextLevel: DecisionRecordLevel,
): Promise<DecisionDocumentNumber | null> {
  const explicitDocNo = inputDocumentNumber(input);
  if (explicitDocNo !== null) {
    assertDocumentComponentsMatch(input, explicitDocNo);
    return explicitDocNo;
  }

  const existingDoc = existingDocumentNumber(existing);
  if (!documentInputPresent(input)) return existingDoc;

  const docType = "docType" in input
    ? parseDocumentType(input.docType)
    : existingDoc?.docType ?? documentTypeFromRecordType(nextType);
  const fallbackPriority = existingDoc?.docPriority ?? priorityFromLevel(nextLevel);
  if (!("docPriority" in input) && !fallbackPriority) {
    throw new HttpError(400, "docPriority is required when assigning a document number without docNo", { allowed: [...decisionDocumentPriorities] });
  }
  const docPriority = "docPriority" in input ? parseDocumentPriority(input.docPriority) : fallbackPriority as DecisionDocumentPriority;
  const docYear = "docYear" in input ? parseDocumentYear(input.docYear) : existingDoc?.docYear ?? currentUtcYear();
  const keepsExistingSeq = existingDoc !== null
    && existingDoc.docType === docType
    && existingDoc.docPriority === docPriority
    && existingDoc.docYear === docYear;
  const docSeq = "docSeq" in input
    ? parseDocumentSequence(input.docSeq)
    : keepsExistingSeq
      ? existingDoc.docSeq
      : await nextDocumentSequence(client, docType, docPriority, docYear);
  return parseDocumentNumber(buildDocumentNumber(docType, docPriority, docYear, docSeq));
}

function parseDocumentDateColumn(value: string): string | null {
  return value ? parseIssuedAt(value) : null;
}

function documentColumnsForInsert(doc: DecisionDocumentNumber | null, input: Record<string, unknown>): {
  docNo: string;
  docType: string;
  docPriority: string;
  docYear: number | null;
  docSeq: number | null;
  signer: string;
  issuedAt: string | null;
  effectiveScope: string;
  supersedes: string[];
  supersededBy: string[];
} {
  return {
    docNo: doc?.docNo ?? "",
    docType: doc?.docType ?? "",
    docPriority: doc?.docPriority ?? "",
    docYear: doc?.docYear ?? null,
    docSeq: doc?.docSeq ?? null,
    signer: asString(input.signer),
    issuedAt: parseDocumentDateColumn(asString(input.issuedAt)),
    effectiveScope: asString(input.effectiveScope),
    supersedes: normalizeDocumentNumberList(input.supersedes, "supersedes"),
    supersededBy: normalizeDocumentNumberList(input.supersededBy, "supersededBy"),
  };
}

function documentColumnsForUpdate(doc: DecisionDocumentNumber | null, input: Record<string, unknown>, existing: DecisionRecord): {
  docNo: string;
  docType: string;
  docPriority: string;
  docYear: number | null;
  docSeq: number | null;
  signer: string;
  issuedAt: string | null;
  effectiveScope: string;
  supersedes: string[];
  supersededBy: string[];
} {
  return {
    docNo: doc?.docNo ?? "",
    docType: doc?.docType ?? "",
    docPriority: doc?.docPriority ?? "",
    docYear: doc?.docYear ?? null,
    docSeq: doc?.docSeq ?? null,
    signer: "signer" in input ? asString(input.signer) : existing.signer,
    issuedAt: "issuedAt" in input ? parseDocumentDateColumn(asString(input.issuedAt)) : existing.issuedAt || null,
    effectiveScope: "effectiveScope" in input ? asString(input.effectiveScope) : existing.effectiveScope,
    supersedes: "supersedes" in input ? normalizeDocumentNumberList(input.supersedes, "supersedes") : existing.supersedes,
    supersededBy: "supersededBy" in input ? normalizeDocumentNumberList(input.supersededBy, "supersededBy") : existing.supersededBy,
  };
}

function summaryFromBody(body: string): string {
  return body
    .split("\n")
    .map((line) => line.replace(/^#{1,6}\s+/u, "").trim())
    .filter(Boolean)
    .join(" ")
    .replace(/\s+/gu, " ")
    .slice(0, 280);
}

function recordFromRow(row: DecisionRecordRow, options: { includeBody?: boolean } = {}): DecisionRecord {
  const includeBody = options.includeBody !== false;
  const body = row.body || "";
  return {
    id: row.id,
    type: row.type,
    level: row.level,
    status: row.status,
    title: row.title,
    summary: summaryFromBody(body),
    body: includeBody ? body : "",
    priority: row.level,
    linkedGoalId: row.linked_goal_id,
    tags: jsonStringArray(row.tags),
    evidenceLinks: jsonStringArray(row.evidence_links),
    docNo: row.doc_no || "",
    docType: row.doc_type || "",
    docPriority: row.doc_priority || "",
    docYear: row.doc_year === null ? null : Number(row.doc_year),
    docSeq: row.doc_seq === null ? null : Number(row.doc_seq),
    signer: row.signer || "",
    issuedAt: iso(row.issued_at),
    effectiveScope: row.effective_scope || "",
    supersedes: jsonStringArray(row.supersedes),
    supersededBy: jsonStringArray(row.superseded_by),
    source: row.source,
    sourceSession: row.source_session,
    issueId: row.issue_id,
    taskId: row.task_id,
    commitId: row.commit_id,
    createdAt: iso(row.created_at),
    updatedAt: iso(row.updated_at),
  };
}

function parseRecordType(value: unknown, fallback: DecisionRecordType): DecisionRecordType {
  const raw = asString(value) || fallback;
  if (!recordTypes.has(raw as DecisionRecordType)) throw new HttpError(400, "unsupported record type", { value: raw, allowed: [...recordTypes] });
  return raw as DecisionRecordType;
}

function parseRequirementRecordType(value: unknown, fallback: RequirementRecordType): RequirementRecordType {
  const raw = asString(value) || fallback;
  if (!requirementRecordTypes.has(raw as RequirementRecordType)) {
    throw new HttpError(400, "unsupported requirement record type", { value: raw, allowed: [...requirementRecordTypes] });
  }
  return raw as RequirementRecordType;
}

function parseLevel(value: unknown, fallback: DecisionRecordLevel): DecisionRecordLevel {
  const raw = asString(value) || fallback;
  if (!recordLevels.has(raw as DecisionRecordLevel)) throw new HttpError(400, "unsupported record level", { value: raw, allowed: [...recordLevels] });
  return raw as DecisionRecordLevel;
}

function parsePriority(input: Record<string, unknown>, fallback: DecisionRecordLevel): DecisionRecordLevel {
  return parseLevel(input.priority ?? input.level, fallback);
}

function parseStatus(value: unknown, fallback: DecisionRecordStatus): DecisionRecordStatus {
  const raw = asString(value) || fallback;
  if (!recordStatuses.has(raw as DecisionRecordStatus)) throw new HttpError(400, "unsupported record status", { value: raw, allowed: [...recordStatuses] });
  return raw as DecisionRecordStatus;
}

function titleFromMarkdown(markdown: string, fallback: string): string {
  const heading = markdown.split("\n").map((line) => line.trim()).find((line) => /^#{1,3}\s+\S/u.test(line));
  if (heading !== undefined) return heading.replace(/^#{1,3}\s+/u, "").trim().slice(0, 220);
  const firstLine = markdown.split("\n").map((line) => line.trim()).find(Boolean);
  return (firstLine || fallback).slice(0, 220);
}

function validateRecordDraft(title: string, body: string): void {
  if (!title) throw new HttpError(400, "title is required");
  if (title.length > 240) throw new HttpError(400, "title must be at most 240 characters");
  if (body.length > 300_000) throw new HttpError(400, "body must be at most 300000 characters");
}

async function readJsonBody(req: Request): Promise<Record<string, unknown>> {
  const text = await req.text();
  if (text.length > 5_000_000) throw new HttpError(413, "request body is too large", { maxBytes: 5_000_000 });
  if (!text.trim()) return {};
  try {
    const parsed = JSON.parse(text) as unknown;
    if (typeof parsed !== "object" || parsed === null || Array.isArray(parsed)) {
      throw new Error("JSON body must be an object");
    }
    return parsed as Record<string, unknown>;
  } catch (error) {
    throw new HttpError(400, "invalid JSON body", { detail: error instanceof Error ? error.message : String(error) });
  }
}

async function ensureSchema(): Promise<void> {
  await withDatabaseRecovery("ensure_schema", async () => {
    await sql`
      CREATE TABLE IF NOT EXISTS decision_center_records (
        id TEXT PRIMARY KEY,
        type TEXT NOT NULL,
        level TEXT NOT NULL DEFAULT 'none',
        status TEXT NOT NULL DEFAULT 'active',
        title TEXT NOT NULL,
        body TEXT NOT NULL DEFAULT '',
        linked_goal_id TEXT,
        tags JSONB NOT NULL DEFAULT '[]'::jsonb,
        evidence_links JSONB NOT NULL DEFAULT '[]'::jsonb,
        doc_no TEXT NOT NULL DEFAULT '',
        doc_type TEXT NOT NULL DEFAULT '',
        doc_priority TEXT NOT NULL DEFAULT '',
        doc_year INTEGER,
        doc_seq INTEGER,
        signer TEXT NOT NULL DEFAULT '',
        issued_at TIMESTAMPTZ,
        effective_scope TEXT NOT NULL DEFAULT '',
        supersedes JSONB NOT NULL DEFAULT '[]'::jsonb,
        superseded_by JSONB NOT NULL DEFAULT '[]'::jsonb,
        source TEXT NOT NULL DEFAULT '',
        source_session TEXT NOT NULL DEFAULT '',
        issue_id TEXT NOT NULL DEFAULT '',
        task_id TEXT NOT NULL DEFAULT '',
        commit_id TEXT NOT NULL DEFAULT '',
        created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
        updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
        CONSTRAINT decision_center_records_type_check CHECK (type IN ('meeting', 'decision', 'goal', 'external_goal', 'internal_goal', 'blocker', 'debt', 'experiment')),
        CONSTRAINT decision_center_records_level_check CHECK (level IN ('G0', 'G1', 'G2', 'G3', 'P0', 'P1', 'P2', 'P3', 'none')),
        CONSTRAINT decision_center_records_status_check CHECK (status IN ('active', 'blocked', 'parked', 'done'))
      )
    `;
    await sql`ALTER TABLE decision_center_records ADD COLUMN IF NOT EXISTS source TEXT NOT NULL DEFAULT ''`;
    await sql`ALTER TABLE decision_center_records ADD COLUMN IF NOT EXISTS issue_id TEXT NOT NULL DEFAULT ''`;
    await sql`ALTER TABLE decision_center_records ADD COLUMN IF NOT EXISTS doc_no TEXT NOT NULL DEFAULT ''`;
    await sql`ALTER TABLE decision_center_records ADD COLUMN IF NOT EXISTS doc_type TEXT NOT NULL DEFAULT ''`;
    await sql`ALTER TABLE decision_center_records ADD COLUMN IF NOT EXISTS doc_priority TEXT NOT NULL DEFAULT ''`;
    await sql`ALTER TABLE decision_center_records ADD COLUMN IF NOT EXISTS doc_year INTEGER`;
    await sql`ALTER TABLE decision_center_records ADD COLUMN IF NOT EXISTS doc_seq INTEGER`;
    await sql`ALTER TABLE decision_center_records ADD COLUMN IF NOT EXISTS signer TEXT NOT NULL DEFAULT ''`;
    await sql`ALTER TABLE decision_center_records ADD COLUMN IF NOT EXISTS issued_at TIMESTAMPTZ`;
    await sql`ALTER TABLE decision_center_records ADD COLUMN IF NOT EXISTS effective_scope TEXT NOT NULL DEFAULT ''`;
    await sql`ALTER TABLE decision_center_records ADD COLUMN IF NOT EXISTS supersedes JSONB NOT NULL DEFAULT '[]'::jsonb`;
    await sql`ALTER TABLE decision_center_records ADD COLUMN IF NOT EXISTS superseded_by JSONB NOT NULL DEFAULT '[]'::jsonb`;
    await sql`ALTER TABLE decision_center_records DROP CONSTRAINT IF EXISTS decision_center_records_type_check`;
    await sql`ALTER TABLE decision_center_records DROP CONSTRAINT IF EXISTS decision_center_records_doc_shape_check`;
    await clearInvalidDocumentNumbers();
    await sql`
      ALTER TABLE decision_center_records
      ADD CONSTRAINT decision_center_records_type_check
      CHECK (type IN ('meeting', 'decision', 'goal', 'external_goal', 'internal_goal', 'blocker', 'debt', 'experiment'))
    `;
    await sql`
      ALTER TABLE decision_center_records
      ADD CONSTRAINT decision_center_records_doc_shape_check
      CHECK (
        (
          doc_no = ''
          AND doc_type = ''
          AND doc_priority = ''
          AND doc_year IS NULL
          AND doc_seq IS NULL
        )
        OR (
          doc_no ~ '^DC-(DCSN|GOAL|PLAN|RPRT|ACTN|ISSU|RETR|RQST|RESP|MINS)-P[0-3]-[0-9]{4}-[0-9]{3,9}$'
          AND doc_type IN ('DCSN', 'GOAL', 'PLAN', 'RPRT', 'ACTN', 'ISSU', 'RETR', 'RQST', 'RESP', 'MINS')
          AND doc_priority IN ('P0', 'P1', 'P2', 'P3')
          AND doc_year BETWEEN 1970 AND 2100
          AND doc_seq > 0
          AND doc_no = 'DC-' || doc_type || '-' || doc_priority || '-' || doc_year::text || '-' || lpad(doc_seq::text, 3, '0')
        )
      )
    `;
    await sql`CREATE INDEX IF NOT EXISTS idx_decision_center_records_type_status_level ON decision_center_records(type, status, level)`;
    await sql`CREATE INDEX IF NOT EXISTS idx_decision_center_records_linked_goal ON decision_center_records(linked_goal_id)`;
    await sql`CREATE INDEX IF NOT EXISTS idx_decision_center_records_updated ON decision_center_records(updated_at DESC)`;
    await sql`CREATE INDEX IF NOT EXISTS idx_decision_center_records_issue_id ON decision_center_records(issue_id)`;
    await clearDuplicateDocumentNumbers();
    await backfillLegacyDocumentNumbers();
    await clearDuplicateDocumentNumbers();
    await sql`CREATE UNIQUE INDEX IF NOT EXISTS idx_decision_center_records_doc_no_unique ON decision_center_records(doc_no) WHERE doc_no <> ''`;
    await sql`CREATE INDEX IF NOT EXISTS idx_decision_center_records_doc_filter ON decision_center_records(doc_type, doc_priority, doc_year, doc_seq) WHERE doc_no <> ''`;
    await sql`
      CREATE TABLE IF NOT EXISTS decision_center_diary_entries (
        id TEXT PRIMARY KEY,
        entry_date DATE NOT NULL,
        month TEXT NOT NULL,
        title TEXT NOT NULL,
        summary TEXT NOT NULL DEFAULT '',
        body TEXT NOT NULL DEFAULT '',
        source_file TEXT NOT NULL DEFAULT '',
        markdown_path TEXT NOT NULL,
        tags JSONB NOT NULL DEFAULT '[]'::jsonb,
        content_hash TEXT NOT NULL,
        created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
        updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
        imported_at TIMESTAMPTZ NOT NULL DEFAULT now(),
        CONSTRAINT decision_center_diary_entries_month_check CHECK (month ~ '^[0-9]{4}-[0-9]{2}$'),
        CONSTRAINT decision_center_diary_entries_path_check CHECK (markdown_path ~ '^[0-9]{4}-[0-9]{2}/[0-9]{4}-[0-9]{2}-[0-9]{2}[.]md$'),
        CONSTRAINT decision_center_diary_entries_source_date_unique UNIQUE (source_file, entry_date)
      )
    `;
    await sql`ALTER TABLE decision_center_diary_entries ADD COLUMN IF NOT EXISTS summary TEXT NOT NULL DEFAULT ''`;
    await sql`CREATE INDEX IF NOT EXISTS idx_decision_center_diary_entries_month_date ON decision_center_diary_entries(month DESC, entry_date DESC)`;
    await sql`CREATE INDEX IF NOT EXISTS idx_decision_center_diary_entries_updated ON decision_center_diary_entries(updated_at DESC)`;
  }, { retryRead: true });
}

async function waitForSchema(): Promise<void> {
  for (let attempt = 1; attempt <= 30; attempt += 1) {
    try {
      await ensureSchema();
      schemaReady = true;
      schemaLastError = null;
      log("info", "schema_ready", { attempt });
      return;
    } catch (error) {
      schemaReady = false;
      schemaLastError = errorToJson(error);
      log("warn", "schema_wait", { attempt, error: schemaLastError });
      await Bun.sleep(Math.min(1000 + attempt * 250, 5000));
    }
  }
  throw new Error(`Decision Center schema initialization failed: ${JSON.stringify(schemaLastError)}`);
}

async function refreshDatabaseHealth(): Promise<void> {
  if (databaseHealthRefreshInFlight) return;
  databaseHealthRefreshInFlight = true;
  try {
    const rows = await withDatabaseRecovery("refresh_database_health", () => sql<{ record_count: string | number; diary_entry_count: string | number }[]>`
      SELECT
        (SELECT count(*) FROM decision_center_records) AS record_count,
        (SELECT count(*) FROM decision_center_diary_entries) AS diary_entry_count
    `, { retryRead: true });
    const checkedAt = new Date().toISOString();
    databaseHealth = {
      ok: true,
      recordCount: Number(rows[0]?.record_count ?? 0),
      diaryEntryCount: Number(rows[0]?.diary_entry_count ?? 0),
      checkedAt,
      lastOkAt: checkedAt,
      degraded: false,
      error: null,
    };
  } catch (error) {
    const checkedAt = new Date().toISOString();
    const lastOkAt = typeof databaseHealth.lastOkAt === "string" ? databaseHealth.lastOkAt : "";
    const lastOkMs = Date.parse(lastOkAt);
    const withinGrace = Number.isFinite(lastOkMs) && Date.now() - lastOkMs < 5 * 60_000;
    databaseHealth = {
      ok: withinGrace,
      recordCount: Number(databaseHealth.recordCount ?? 0),
      diaryEntryCount: Number(databaseHealth.diaryEntryCount ?? 0),
      checkedAt,
      lastOkAt,
      degraded: withinGrace,
      error: errorToJson(error),
    };
  } finally {
    databaseHealthRefreshInFlight = false;
  }
}

function deployInfo(): JsonRecord {
  return {
    serviceId: envString("UNIDESK_DEPLOY_SERVICE_ID", "decision-center"),
    repo: envString("UNIDESK_DEPLOY_REPO", ""),
    commit: envString("UNIDESK_DEPLOY_COMMIT", ""),
    requestedCommit: envString("UNIDESK_DEPLOY_REQUESTED_COMMIT", ""),
  };
}

function health(): JsonRecord {
  const dbOk = databaseHealth.ok === true;
  return {
    ok: schemaReady && dbOk,
    service: "decision-center",
    status: schemaReady && dbOk ? "ready" : "not-ready",
    startedAt: serviceStartedAt,
    storage: "postgres",
    schemaReady,
    recordCount: Number(databaseHealth.recordCount ?? 0),
    diaryEntryCount: Number(databaseHealth.diaryEntryCount ?? 0),
    database: databaseHealth,
    deploy: deployInfo(),
  };
}

async function clearInvalidDocumentNumbers(): Promise<void> {
  const rows = await sql<{ id: string; doc_no: string }[]>`
    UPDATE decision_center_records
    SET
      doc_no = '',
      doc_type = '',
      doc_priority = '',
      doc_year = NULL,
      doc_seq = NULL,
      updated_at = updated_at
    WHERE NOT (
      (
        doc_no = ''
        AND doc_type = ''
        AND doc_priority = ''
        AND doc_year IS NULL
        AND doc_seq IS NULL
      )
      OR (
        doc_no ~ '^DC-(DCSN|GOAL|PLAN|RPRT|ACTN|ISSU|RETR|RQST|RESP|MINS)-P[0-3]-[0-9]{4}-[0-9]{3,9}$'
        AND doc_type IN ('DCSN', 'GOAL', 'PLAN', 'RPRT', 'ACTN', 'ISSU', 'RETR', 'RQST', 'RESP', 'MINS')
        AND doc_priority IN ('P0', 'P1', 'P2', 'P3')
        AND doc_year BETWEEN 1970 AND 2100
        AND doc_seq > 0
        AND doc_no = 'DC-' || doc_type || '-' || doc_priority || '-' || doc_year::text || '-' || lpad(doc_seq::text, 3, '0')
      )
    )
    RETURNING id, doc_no
  `;
  if (rows.length > 0) {
    log("warn", "invalid_document_numbers_cleared_for_schema_check", {
      clearedCount: rows.length,
      examples: rows.slice(0, 20).map((row) => ({ id: row.id, docNo: row.doc_no })),
    });
  }
}

async function clearDuplicateDocumentNumbers(): Promise<void> {
  const rows = await sql<{ id: string; doc_no: string }[]>`
    WITH ranked AS (
      SELECT
        id,
        doc_no,
        row_number() OVER (PARTITION BY doc_no ORDER BY updated_at DESC, created_at DESC, id ASC) AS rank
      FROM decision_center_records
      WHERE doc_no <> ''
    )
    UPDATE decision_center_records record
    SET
      doc_no = '',
      doc_type = '',
      doc_priority = '',
      doc_year = NULL,
      doc_seq = NULL,
      updated_at = updated_at
    FROM ranked
    WHERE record.id = ranked.id
      AND ranked.rank > 1
    RETURNING record.id, ranked.doc_no
  `;
  if (rows.length > 0) {
    log("warn", "duplicate_document_numbers_cleared_for_unique_index", {
      clearedCount: rows.length,
      docNos: [...new Set(rows.map((row) => row.doc_no))].slice(0, 20),
    });
  }
}

async function backfillLegacyDocumentNumbers(): Promise<void> {
  const rows = await sql<DecisionRecordRow[]>`
    SELECT *
    FROM decision_center_records
    WHERE doc_no = ''
    ORDER BY created_at ASC
    LIMIT 5000
  `;
  let updatedCount = 0;
  let skippedCount = 0;
  for (const row of rows) {
    const doc = extractDocumentNumberFromLegacy({
      id: row.id,
      title: row.title,
      body: row.body,
      tags: row.tags,
    });
    if (doc === null) continue;
    const updated = await sql<{ id: string }[]>`
      UPDATE decision_center_records
      SET
        doc_no = ${doc.docNo},
        doc_type = ${doc.docType},
        doc_priority = ${doc.docPriority},
        doc_year = ${doc.docYear},
        doc_seq = ${doc.docSeq},
        updated_at = updated_at
      WHERE id = ${row.id}
        AND doc_no = ''
        AND NOT EXISTS (
          SELECT 1
          FROM decision_center_records existing
          WHERE existing.doc_no = ${doc.docNo}
            AND existing.id <> ${row.id}
        )
      RETURNING id
    `;
    if (updated.length > 0) updatedCount += 1;
    else skippedCount += 1;
  }
  if (updatedCount > 0 || skippedCount > 0) {
    log("info", "legacy_document_numbers_backfilled", { updatedCount, skippedCount });
  }
}

async function createRecord(input: Record<string, unknown>): Promise<DecisionRecord> {
  const body = asText(input.body ?? input.summary ?? input.markdown);
  const title = asString(input.title) || titleFromMarkdown(body, "Untitled decision record");
  validateRecordDraft(title, body);
  const id = asString(input.id) || `dc_${randomUUID()}`;
  const type = parseRecordType(input.type, "meeting");
  const level = parsePriority(input, "none");
  const status = parseStatus(input.status, "active");
  const tags = asStringArray(input.tags, "tags");
  const evidenceLinks = asStringArray(input.evidenceLinks ?? input.evidence, "evidenceLinks");
  const rows = await withDatabaseRecovery("create_record", () => sql.begin(async (client) => {
    const doc = await documentNumberForCreate(client, input, { id, type, level, title, body, tags });
    const documentColumns = documentColumnsForInsert(doc, input);
    return await client<DecisionRecordRow[]>`
      INSERT INTO decision_center_records (
        id, type, level, status, title, body, linked_goal_id, tags, evidence_links,
        doc_no, doc_type, doc_priority, doc_year, doc_seq, signer, issued_at, effective_scope, supersedes, superseded_by,
        source, source_session, issue_id, task_id, commit_id
      ) VALUES (
        ${id},
        ${type},
        ${level},
        ${status},
        ${title},
        ${body},
        ${asString(input.linkedGoalId) || null},
        ${client.json(tags)},
        ${client.json(evidenceLinks)},
        ${documentColumns.docNo},
        ${documentColumns.docType},
        ${documentColumns.docPriority},
        ${documentColumns.docYear},
        ${documentColumns.docSeq},
        ${documentColumns.signer},
        ${documentColumns.issuedAt},
        ${documentColumns.effectiveScope},
        ${client.json(documentColumns.supersedes)},
        ${client.json(documentColumns.supersededBy)},
        ${asString(input.source)},
        ${asString(input.sourceSession)},
        ${asString(input.issueId ?? input.issue ?? input.linkedIssue ?? input.linkedTask ?? input.taskId)},
        ${asString(input.taskId)},
        ${asString(input.commitId)}
      )
      RETURNING *
    `;
  }));
  log("info", "record_created", { id: rows[0]?.id ?? id, type: rows[0]?.type ?? "", level: rows[0]?.level ?? "", docNo: rows[0]?.doc_no ?? "" });
  return recordFromRow(rows[0]!);
}

async function updateRecord(id: string, input: Record<string, unknown>): Promise<DecisionRecord> {
  const existing = await getRecord(id);
  const title = "title" in input ? asString(input.title) : existing.title;
  const body = "body" in input || "summary" in input || "markdown" in input ? asText(input.body ?? input.summary ?? input.markdown) : existing.body;
  const nextType = "type" in input ? parseRecordType(input.type, existing.type) : existing.type;
  const nextLevel = "level" in input || "priority" in input ? parsePriority(input, existing.level) : existing.level;
  const nextStatus = "status" in input ? parseStatus(input.status, existing.status) : existing.status;
  validateRecordDraft(title, body);
  const rows = await withDatabaseRecovery("update_record", () => sql.begin(async (client) => {
    const doc = await documentNumberForUpdate(client, input, existing, nextType, nextLevel);
    const documentColumns = documentColumnsForUpdate(doc, input, existing);
    return await client<DecisionRecordRow[]>`
      UPDATE decision_center_records
      SET
        type = ${nextType},
        level = ${nextLevel},
        status = ${nextStatus},
        title = ${title},
        body = ${body},
        linked_goal_id = ${"linkedGoalId" in input ? asString(input.linkedGoalId) || null : existing.linkedGoalId},
        tags = ${"tags" in input ? client.json(asStringArray(input.tags, "tags")) : client.json(existing.tags)},
        evidence_links = ${"evidenceLinks" in input || "evidence" in input ? client.json(asStringArray(input.evidenceLinks ?? input.evidence, "evidenceLinks")) : client.json(existing.evidenceLinks)},
        doc_no = ${documentColumns.docNo},
        doc_type = ${documentColumns.docType},
        doc_priority = ${documentColumns.docPriority},
        doc_year = ${documentColumns.docYear},
        doc_seq = ${documentColumns.docSeq},
        signer = ${documentColumns.signer},
        issued_at = ${documentColumns.issuedAt},
        effective_scope = ${documentColumns.effectiveScope},
        supersedes = ${client.json(documentColumns.supersedes)},
        superseded_by = ${client.json(documentColumns.supersededBy)},
        source = ${"source" in input ? asString(input.source) : existing.source},
        source_session = ${"sourceSession" in input ? asString(input.sourceSession) : existing.sourceSession},
        issue_id = ${"issueId" in input || "issue" in input || "linkedIssue" in input || "linkedTask" in input ? asString(input.issueId ?? input.issue ?? input.linkedIssue ?? input.linkedTask) : existing.issueId},
        task_id = ${"taskId" in input ? asString(input.taskId) : existing.taskId},
        commit_id = ${"commitId" in input ? asString(input.commitId) : existing.commitId},
        updated_at = now()
      WHERE id = ${existing.id}
      RETURNING *
    `;
  }));
  return recordFromRow(rows[0]!);
}

async function upsertRequirementRecord(input: Record<string, unknown>): Promise<JsonRecord> {
  const id = asString(input.id);
  const docNo = inputDocumentNumber(input)?.docNo ?? "";
  if (id || docNo) {
    try {
      const existing = await getRecordByIdOrDocNo(id, docNo);
      if (!requirementRecordTypes.has(existing.type as RequirementRecordType)) {
        throw new HttpError(409, "existing record is not a requirement-management record", { id: existing.id, docNo: existing.docNo, type: existing.type });
      }
      const record = await updateRecord(existing.id, {
        ...input,
        type: "type" in input ? parseRequirementRecordType(input.type, existing.type as RequirementRecordType) : existing.type,
      });
      log("info", "requirement_updated", { id: record.id, type: record.type, level: record.level, status: record.status });
      return { ok: true, action: "updated", record };
    } catch (error) {
      if (!(error instanceof HttpError) || error.status !== 404) throw error;
    }
  }
  const record = await createRecord({
    ...input,
    id: id || undefined,
    type: parseRequirementRecordType(input.type, "external_goal"),
  });
  log("info", "requirement_created", { id: record.id, type: record.type, level: record.level, status: record.status });
  return { ok: true, action: "created", record };
}

async function getRecordByIdOrDocNo(id: string, docNo: string): Promise<DecisionRecord> {
  const rows = await withDatabaseRecovery("get_record_by_id_or_doc_no", () => sql<DecisionRecordRow[]>`
    SELECT *
    FROM decision_center_records
    WHERE (${id || null}::text IS NOT NULL AND id = ${id || null})
      OR (${docNo || null}::text IS NOT NULL AND doc_no = ${docNo || null})
    ORDER BY
      CASE WHEN id = ${id || ""} THEN 0 ELSE 1 END,
      updated_at DESC
    LIMIT 1
  `, { retryRead: true });
  if (rows.length === 0) throw new HttpError(404, "decision record not found", { id, docNo });
  return recordFromRow(rows[0]!);
}

async function getRecord(id: string): Promise<DecisionRecord> {
  const docNo = tryParseDocumentNumber(id)?.docNo ?? "";
  const rows = await withDatabaseRecovery("get_record", () => sql<DecisionRecordRow[]>`
    SELECT *
    FROM decision_center_records
    WHERE id = ${id}
      OR (${docNo || null}::text IS NOT NULL AND doc_no = ${docNo || null})
    ORDER BY CASE WHEN id = ${id} THEN 0 ELSE 1 END
    LIMIT 1
  `, { retryRead: true });
  if (rows.length === 0) throw new HttpError(404, "decision record not found", { id });
  return recordFromRow(rows[0]!);
}

async function listRecords(url: URL, options: { requirementOnly?: boolean } = {}): Promise<DecisionRecord[]> {
  const docNoRaw = asString(url.searchParams.get("docNo") ?? url.searchParams.get("doc-no") ?? url.searchParams.get("documentNo") ?? url.searchParams.get("documentNumber"));
  const docNo = docNoRaw ? parseDocumentNumber(docNoRaw, "docNo").docNo : "";
  const docType = asString(url.searchParams.get("docType") ?? url.searchParams.get("doc-type"));
  const docPriority = asString(url.searchParams.get("docPriority") ?? url.searchParams.get("doc-priority"));
  const docYearRaw = asString(url.searchParams.get("docYear") ?? url.searchParams.get("doc-year") ?? url.searchParams.get("year"));
  const type = asString(url.searchParams.get("type"));
  const status = asString(url.searchParams.get("status"));
  const level = asString(url.searchParams.get("level"));
  const linkedGoalId = asString(url.searchParams.get("linkedGoalId"));
  const source = asString(url.searchParams.get("source"));
  const issueId = asString(url.searchParams.get("issueId") ?? url.searchParams.get("issue"));
  const tag = asString(url.searchParams.get("tag"));
  const query = asString(url.searchParams.get("q") ?? url.searchParams.get("query"));
  const requirementOnly = options.requirementOnly === true || url.searchParams.get("requirementOnly") === "true";
  const includeBody = url.searchParams.get("includeBody") === "true";
  const limit = Math.max(1, Math.min(500, Number(url.searchParams.get("limit") || 200) || 200));
  if (docType && !decisionDocumentTypes.includes(docType.toUpperCase() as DecisionDocumentType)) throw new HttpError(400, "unsupported docType filter", { docType, allowed: [...decisionDocumentTypes] });
  if (docPriority && !decisionDocumentPriorities.includes(docPriority.toUpperCase() as DecisionDocumentPriority)) throw new HttpError(400, "unsupported docPriority filter", { docPriority, allowed: [...decisionDocumentPriorities] });
  const normalizedDocType = docType ? parseDocumentType(docType, "docType") : "";
  const normalizedDocPriority = docPriority ? parseDocumentPriority(docPriority, "docPriority") : "";
  const docYear = docYearRaw ? parseDocumentYear(docYearRaw, "docYear") : null;
  if (type && !recordTypes.has(type as DecisionRecordType)) throw new HttpError(400, "unsupported type filter", { type });
  if (requirementOnly && type && !requirementRecordTypes.has(type as RequirementRecordType)) throw new HttpError(400, "unsupported requirement type filter", { type });
  if (status && !recordStatuses.has(status as DecisionRecordStatus)) throw new HttpError(400, "unsupported status filter", { status });
  if (level && !recordLevels.has(level as DecisionRecordLevel)) throw new HttpError(400, "unsupported level filter", { level });
  if (source.length > 240) throw new HttpError(400, "source filter must be at most 240 characters");
  if (issueId.length > 240) throw new HttpError(400, "issueId filter must be at most 240 characters");
  if (tag.length > 120) throw new HttpError(400, "tag filter must be at most 120 characters");
  if (query.length > 240) throw new HttpError(400, "query filter must be at most 240 characters");
  const rows = await withDatabaseRecovery("list_records", () => sql<DecisionRecordRow[]>`
    SELECT
      id,
      type,
      level,
      status,
      title,
      CASE WHEN ${includeBody}::boolean THEN body ELSE left(body, 4000) END AS body,
      linked_goal_id,
      tags,
      evidence_links,
      doc_no,
      doc_type,
      doc_priority,
      doc_year,
      doc_seq,
      signer,
      issued_at,
      effective_scope,
      supersedes,
      superseded_by,
      source,
      source_session,
      issue_id,
      task_id,
      commit_id,
      created_at,
      updated_at
    FROM decision_center_records
    WHERE (${docNo || null}::text IS NULL OR doc_no = ${docNo || null})
      AND (${normalizedDocType || null}::text IS NULL OR doc_type = ${normalizedDocType || null})
      AND (${normalizedDocPriority || null}::text IS NULL OR doc_priority = ${normalizedDocPriority || null})
      AND (${docYear}::int IS NULL OR doc_year = ${docYear})
      AND (${type || null}::text IS NULL OR type = ${type || null})
      AND (${requirementOnly}::boolean IS FALSE OR type IN ('decision', 'goal', 'external_goal', 'internal_goal', 'blocker', 'debt', 'experiment'))
      AND (${status || null}::text IS NULL OR status = ${status || null})
      AND (${level || null}::text IS NULL OR level = ${level || null})
      AND (${linkedGoalId || null}::text IS NULL OR linked_goal_id = ${linkedGoalId || null})
      AND (${source || null}::text IS NULL OR source = ${source || null})
      AND (${issueId || null}::text IS NULL OR issue_id = ${issueId || null})
      AND (${tag || null}::text IS NULL OR tags ? ${tag || ""})
      AND (${query || null}::text IS NULL OR title ILIKE ${query ? `%${query}%` : ""} OR body ILIKE ${query ? `%${query}%` : ""})
    ORDER BY
      CASE WHEN doc_no = '' THEN 1 ELSE 0 END ASC,
      doc_type ASC NULLS LAST,
      CASE doc_priority
        WHEN 'P0' THEN 0
        WHEN 'P1' THEN 1
        WHEN 'P2' THEN 2
        WHEN 'P3' THEN 3
        ELSE 4
      END ASC,
      doc_year ASC NULLS LAST,
      doc_seq ASC NULLS LAST,
      CASE level
        WHEN 'G0' THEN 0
        WHEN 'P0' THEN 1
        WHEN 'G1' THEN 2
        WHEN 'P1' THEN 3
        WHEN 'G2' THEN 4
        WHEN 'P2' THEN 5
        WHEN 'G3' THEN 6
        WHEN 'P3' THEN 7
        ELSE 8
      END ASC,
      updated_at DESC
    LIMIT ${limit}
  `, { retryRead: true });
  return rows.map((row) => recordFromRow(row, { includeBody }));
}

async function getRequirementRecord(id: string): Promise<DecisionRecord> {
  const record = await getRecord(id);
  if (!requirementRecordTypes.has(record.type as RequirementRecordType)) {
    throw new HttpError(404, "requirement record not found", { id, type: record.type });
  }
  return record;
}

async function createRequirementRecord(input: Record<string, unknown>): Promise<DecisionRecord> {
  const record = await createRecord({
    ...input,
    type: parseRequirementRecordType(input.type, "external_goal"),
  });
  log("info", "requirement_created", { id: record.id, type: record.type, level: record.level, status: record.status });
  return record;
}

async function updateRequirementRecord(id: string, input: Record<string, unknown>): Promise<DecisionRecord> {
  const existing = await getRequirementRecord(id);
  const record = await updateRecord(id, {
    ...input,
    type: "type" in input ? parseRequirementRecordType(input.type, existing.type as RequirementRecordType) : existing.type,
  });
  log("info", "requirement_updated", { id: record.id, type: record.type, level: record.level, status: record.status });
  return record;
}

function normalizeDecisionDrafts(value: unknown): Array<Record<string, unknown>> {
  if (value === undefined || value === null) return [];
  if (!Array.isArray(value)) throw new HttpError(400, "decisions must be an array");
  return value.map((item, index) => {
    const record = asRecord(item);
    if (Object.keys(record).length === 0) throw new HttpError(400, "decision item must be an object", { index });
    return record;
  });
}

async function importMeeting(input: Record<string, unknown>): Promise<JsonRecord> {
  const markdown = asString(input.markdown ?? input.body ?? input.summary);
  if (!markdown) throw new HttpError(400, "markdown is required");
  const base: Record<string, unknown> = {
    type: "meeting",
    level: parsePriority(input, "none"),
    status: parseStatus(input.status, "active"),
    title: asString(input.title) || titleFromMarkdown(markdown, "Imported meeting"),
    body: markdown,
    linkedGoalId: asString(input.linkedGoalId) || null,
    tags: asStringArray(input.tags, "tags"),
    evidenceLinks: asStringArray(input.evidenceLinks ?? input.evidence, "evidenceLinks"),
    source: asString(input.source),
    sourceSession: asString(input.sourceSession),
    issueId: asString(input.issueId ?? input.issue ?? input.linkedIssue ?? input.linkedTask ?? input.taskId),
    taskId: asString(input.taskId),
    commitId: asString(input.commitId),
  };
  const documentFields = ["docNo", "docNumber", "documentNo", "documentNumber", "docType", "docPriority", "docYear", "docSeq", "signer", "issuedAt", "effectiveScope", "supersedes", "supersededBy"];
  for (const field of documentFields) {
    if (field in input) base[field] = input[field];
  }
  const meeting = await createRecord(base);
  const decisionInputs = normalizeDecisionDrafts(input.decisions);
  const decisions: DecisionRecord[] = [];
  for (const decision of decisionInputs) {
    const inheritedBase = { ...base };
    for (const field of documentFields) {
      if (!(field in decision)) delete inheritedBase[field];
    }
    decisions.push(await createRecord({
      ...inheritedBase,
      ...decision,
      type: "decision",
      linkedGoalId: asString(decision.linkedGoalId) || meeting.linkedGoalId,
      body: asString(decision.body ?? decision.summary ?? decision.markdown) || asString(decision.title),
    }));
  }
  return { ok: true, meeting, decisions, createdCount: 1 + decisions.length };
}

async function deleteRecord(id: string): Promise<JsonRecord> {
  const existing = await getRecord(id);
  const rows = await withDatabaseRecovery("delete_record", () => sql<DecisionRecordRow[]>`DELETE FROM decision_center_records WHERE id = ${existing.id} RETURNING *`);
  if (rows.length === 0) throw new HttpError(404, "decision record not found", { id });
  log("info", "record_deleted", { id });
  return { ok: true, deleted: recordFromRow(rows[0]!) };
}

function dateOnly(value: Date | string | null | undefined): string {
  if (value === null || value === undefined) return "";
  if (typeof value === "string") {
    const match = value.match(/^(\d{4}-\d{2}-\d{2})/u);
    if (match !== null) return match[1] ?? "";
  }
  return iso(value).slice(0, 10);
}

function pad2(value: number): string {
  return String(value).padStart(2, "0");
}

function normalizedDate(year: number, month: number, day: number): string | null {
  if (!Number.isInteger(year) || !Number.isInteger(month) || !Number.isInteger(day)) return null;
  if (year < 1970 || year > 2100 || month < 1 || month > 12 || day < 1 || day > 31) return null;
  const date = new Date(Date.UTC(year, month - 1, day));
  if (date.getUTCFullYear() !== year || date.getUTCMonth() !== month - 1 || date.getUTCDate() !== day) return null;
  return `${year}-${pad2(month)}-${pad2(day)}`;
}

function parseDiaryHeadingDate(line: string): string | null {
  const trimmed = line.trim();
  if (!trimmed) return null;
  const heading = trimmed.match(/^#{1,6}\s+(.+)$/u);
  const candidate = heading === null ? trimmed : heading[1]?.trim() ?? "";
  if (heading === null && !/^\d{4}/u.test(candidate)) return null;
  const match = candidate.match(/^(\d{4})\s*(?:年|[-/.])\s*(\d{1,2})\s*(?:月|[-/.])\s*(\d{1,2})\s*(?:日)?(?:\s*(?:[(（].*[)）]|星期[一二三四五六日天]|周[一二三四五六日天]|[-–—:：].*))?$/u);
  if (match === null) return null;
  return normalizedDate(Number(match[1]), Number(match[2]), Number(match[3]));
}

function contentHash(value: string): string {
  return createHash("sha256").update(value).digest("hex");
}

function diaryEntryFromRow(row: DiaryEntryRow, options: { includeBody?: boolean } = {}): DiaryEntry {
  const includeBody = options.includeBody !== false;
  const body = row.body || "";
  return {
    id: row.id,
    date: dateOnly(row.entry_date),
    month: row.month,
    title: row.title,
    summary: row.summary || summaryFromBody(body),
    body: includeBody ? body : "",
    sourceFile: row.source_file,
    markdownPath: row.markdown_path,
    tags: Array.isArray(row.tags) ? row.tags.map(String) : [],
    contentHash: row.content_hash,
    createdAt: iso(row.created_at),
    updatedAt: iso(row.updated_at),
    importedAt: iso(row.imported_at),
  };
}

function normalizeDiarySummary(value: unknown, body: string): string {
  const explicit = asText(value).replace(/\s+/gu, " ").trim();
  return (explicit || summaryFromBody(body)).slice(0, 280);
}

function splitDiaryMarkdown(markdown: string): DiaryDraft[] {
  const normalized = markdown.replace(/\r\n?/gu, "\n");
  const lines = normalized.split("\n");
  const entries = new Map<string, DiaryDraft>();
  let currentDate = "";
  let currentLines: string[] = [];

  const flush = (): void => {
    if (!currentDate) return;
    const body = currentLines.join("\n").trim();
    if (!body) return;
    const month = currentDate.slice(0, 7);
    const existing = entries.get(currentDate);
    const title = titleFromMarkdown(body, `${currentDate} 工作日志`);
    const draft: DiaryDraft = {
      date: currentDate,
      month,
      title,
      body: existing === undefined ? body : `${existing.body}\n\n---\n\n${body}`,
      markdownPath: `${month}/${currentDate}.md`,
    };
    entries.set(currentDate, draft);
  };

  for (const line of lines) {
    const date = parseDiaryHeadingDate(line);
    if (date !== null) {
      flush();
      currentDate = date;
      currentLines = [line];
      continue;
    }
    if (currentDate) currentLines.push(line);
  }
  flush();

  return [...entries.values()].sort((left, right) => left.date.localeCompare(right.date));
}

function validDateFilter(value: string, field: string): string {
  if (!/^\d{4}-\d{2}-\d{2}$/u.test(value)) throw new HttpError(400, `${field} must use YYYY-MM-DD`, { value });
  const [year, month, day] = value.split("-").map(Number);
  if (normalizedDate(year ?? 0, month ?? 0, day ?? 0) !== value) throw new HttpError(400, `${field} is not a valid date`, { value });
  return value;
}

function validMonthFilter(value: string): string {
  if (!/^\d{4}-\d{2}$/u.test(value)) throw new HttpError(400, "month must use YYYY-MM", { value });
  const [year, month] = value.split("-").map(Number);
  if (normalizedDate(year ?? 0, month ?? 0, 1)?.slice(0, 7) !== value) throw new HttpError(400, "month is not valid", { value });
  return value;
}

function realTodayUtc(): string {
  const now = new Date();
  return `${now.getUTCFullYear()}-${pad2(now.getUTCMonth() + 1)}-${pad2(now.getUTCDate())}`;
}

function defaultDiaryBody(date: string): string {
  return `# ${date}\n\n`;
}

function markdownPathForDate(date: string): string {
  const month = date.slice(0, 7);
  return `${month}/${date}.md`;
}

function validateDiarySourceFile(value: string): string {
  if (value.length > 1000) throw new HttpError(400, "sourceFile must be at most 1000 characters");
  if (/[\u0000-\u001f\u007f]/u.test(value)) throw new HttpError(400, "sourceFile contains control characters");
  return value;
}

function sourceFileFilterFromUrl(url: URL): string {
  const sourceFile = asString(url.searchParams.get("sourceFile") ?? url.searchParams.get("sourcePath") ?? url.searchParams.get("source"));
  return sourceFile ? validateDiarySourceFile(sourceFile) : "";
}

function diaryTitleFor(date: string, body: string, rawTitle: unknown): string {
  const title = asString(rawTitle) || titleFromMarkdown(body, `${date} 工作日志`);
  if (!title) throw new HttpError(400, "title is required");
  if (title.length > 240) throw new HttpError(400, "title must be at most 240 characters");
  return title;
}

async function upsertDiaryEntry(draft: DiaryDraft, sourceFile: string, tags: string[]): Promise<{ entry: DiaryEntry; action: "created" | "updated" | "unchanged" }> {
  if (draft.body.length > 300_000) throw new HttpError(400, "diary day body must be at most 300000 characters", { date: draft.date, length: draft.body.length });
  const hash = contentHash(draft.body);
  const summary = summaryFromBody(draft.body);
  const rows = await withDatabaseRecovery("upsert_diary_entry", () => sql<DiaryUpsertRow[]>`
    WITH existing AS (
      SELECT content_hash, summary
      FROM decision_center_diary_entries
      WHERE source_file = ${sourceFile} AND entry_date = ${draft.date}::date
    ),
    upsert AS (
      INSERT INTO decision_center_diary_entries (
        id, entry_date, month, title, summary, body, source_file, markdown_path, tags, content_hash
      ) VALUES (
        ${`diary_${randomUUID()}`},
        ${draft.date}::date,
        ${draft.month},
        ${draft.title},
        ${summary},
        ${draft.body},
        ${sourceFile},
        ${draft.markdownPath},
        ${sql.json(tags)},
        ${hash}
      )
      ON CONFLICT (source_file, entry_date) DO UPDATE
      SET
        month = EXCLUDED.month,
        title = EXCLUDED.title,
        summary = EXCLUDED.summary,
        body = EXCLUDED.body,
        markdown_path = EXCLUDED.markdown_path,
        tags = EXCLUDED.tags,
        content_hash = EXCLUDED.content_hash,
        imported_at = now(),
        updated_at = CASE
          WHEN decision_center_diary_entries.content_hash IS DISTINCT FROM EXCLUDED.content_hash
            OR decision_center_diary_entries.summary IS DISTINCT FROM EXCLUDED.summary
          THEN now()
          ELSE decision_center_diary_entries.updated_at
        END
      RETURNING *
    )
    SELECT
      upsert.*,
      NOT EXISTS(SELECT 1 FROM existing) AS inserted,
      EXISTS(SELECT 1 FROM existing WHERE existing.content_hash IS DISTINCT FROM upsert.content_hash OR existing.summary IS DISTINCT FROM upsert.summary) AS changed
    FROM upsert
  `);
  const row = rows[0];
  if (row === undefined) throw new HttpError(500, "diary upsert returned no row", { date: draft.date });
  return {
    entry: diaryEntryFromRow(row, { includeBody: false }),
    action: row.inserted ? "created" : row.changed ? "updated" : "unchanged",
  };
}

async function importDiary(input: Record<string, unknown>): Promise<JsonRecord> {
  const markdown = asString(input.markdown ?? input.body);
  if (!markdown) throw new HttpError(400, "markdown is required");
  if (markdown.length > 4_500_000) throw new HttpError(400, "markdown must be at most 4500000 characters");
  const sourceFile = asString(input.sourceFile ?? input.sourcePath ?? input.source) || "inline";
  const tags = asStringArray(input.tags, "tags");
  const drafts = splitDiaryMarkdown(markdown);
  if (drafts.length === 0) {
    throw new HttpError(400, "no dated diary sections found", {
      expectedHeading: "# 2025年2月12日 or # 2025-02-12",
    });
  }
  const entries: DiaryEntry[] = [];
  let createdCount = 0;
  let updatedCount = 0;
  let unchangedCount = 0;
  for (const draft of drafts) {
    const result = await upsertDiaryEntry(draft, sourceFile, tags);
    entries.push(result.entry);
    if (result.action === "created") createdCount += 1;
    else if (result.action === "updated") updatedCount += 1;
    else unchangedCount += 1;
  }
  log("info", "diary_imported", { sourceFile, days: drafts.length, createdCount, updatedCount, unchangedCount });
  return {
    ok: true,
    sourceFile,
    totalDays: drafts.length,
    createdCount,
    updatedCount,
    unchangedCount,
    firstDate: entries[0]?.date ?? "",
    lastDate: entries[entries.length - 1]?.date ?? "",
    pathPattern: "YYYY-MM/YYYY-MM-DD.md",
    entries,
  };
}

async function listDiaryEntries(url: URL): Promise<DiaryEntry[]> {
  const month = asString(url.searchParams.get("month"));
  const from = asString(url.searchParams.get("from"));
  const to = asString(url.searchParams.get("to"));
  const sourceFile = sourceFileFilterFromUrl(url);
  const includeBody = url.searchParams.get("includeBody") === "true";
  const limit = Math.max(1, Math.min(1000, Number(url.searchParams.get("limit") || 240) || 240));
  if (month) validMonthFilter(month);
  if (from) validDateFilter(from, "from");
  if (to) validDateFilter(to, "to");
  const rows = await withDatabaseRecovery("list_diary_entries", () => sql<DiaryEntryRow[]>`
    SELECT
      id,
      entry_date,
      month,
      title,
      summary,
      CASE WHEN ${includeBody}::boolean THEN body ELSE left(body, 4000) END AS body,
      source_file,
      markdown_path,
      tags,
      content_hash,
      created_at,
      updated_at,
      imported_at
    FROM decision_center_diary_entries
    WHERE (${month || null}::text IS NULL OR month = ${month || null})
      AND (${from || null}::date IS NULL OR entry_date >= ${from || null}::date)
      AND (${to || null}::date IS NULL OR entry_date <= ${to || null}::date)
      AND (${sourceFile || null}::text IS NULL OR source_file = ${sourceFile || null})
    ORDER BY entry_date DESC, updated_at DESC
    LIMIT ${limit}
  `, { retryRead: true });
  return rows.map((row) => diaryEntryFromRow(row, { includeBody }));
}

async function listDiaryMonths(): Promise<JsonRecord[]> {
  const rows = await withDatabaseRecovery("list_diary_months", () => sql<Array<{ month: string; count: string | number; first_date: Date | string; last_date: Date | string; updated_at: Date | string }>>`
    SELECT month, count(*) AS count, min(entry_date) AS first_date, max(entry_date) AS last_date, max(updated_at) AS updated_at
    FROM decision_center_diary_entries
    GROUP BY month
    ORDER BY month DESC
  `, { retryRead: true });
  return rows.map((row) => ({
    month: row.month,
    count: Number(row.count),
    firstDate: dateOnly(row.first_date),
    lastDate: dateOnly(row.last_date),
    updatedAt: iso(row.updated_at),
  }));
}

async function getDiaryEntry(key: string, options: { sourceFile?: string } = {}): Promise<DiaryEntry> {
  const date = /^\d{4}-\d{2}-\d{2}$/u.test(key) ? validDateFilter(key, "date") : "";
  const sourceFile = options.sourceFile ? validateDiarySourceFile(options.sourceFile) : "";
  const rows = await withDatabaseRecovery("get_diary_entry", () => sql<DiaryEntryRow[]>`
    SELECT *
    FROM decision_center_diary_entries
    WHERE (
      (${date || null}::date IS NOT NULL AND entry_date = ${date || null}::date)
      OR (${date || null}::date IS NULL AND id = ${key})
    )
      AND (${sourceFile || null}::text IS NULL OR source_file = ${sourceFile || null})
    ORDER BY imported_at DESC
    LIMIT 1
  `, { retryRead: true });
  if (rows.length === 0) throw new HttpError(404, "diary entry not found", { key, ...(sourceFile ? { sourceFile } : {}) });
  return diaryEntryFromRow(rows[0]!);
}

async function upsertDiaryEntryByKey(key: string, input: Record<string, unknown>): Promise<JsonRecord> {
  const keyDate = /^\d{4}-\d{2}-\d{2}$/u.test(key) ? validDateFilter(key, "date") : "";
  const bodyProvided = "body" in input || "markdown" in input;
  const summaryProvided = "summary" in input;
  const titleProvided = "title" in input;
  const tagsProvided = "tags" in input;
  const sourceFileProvided = "sourceFile" in input || "sourcePath" in input || "source" in input;
  const sourceFile = sourceFileProvided ? validateDiarySourceFile(asString(input.sourceFile ?? input.sourcePath ?? input.source)) : "";

  const existingRows = await withDatabaseRecovery("get_diary_entry_for_upsert", () => sql<DiaryEntryRow[]>`
    SELECT *
    FROM decision_center_diary_entries
    WHERE (
      (${keyDate || null}::date IS NOT NULL AND entry_date = ${keyDate || null}::date)
      OR (${keyDate || null}::date IS NULL AND id = ${key})
    )
      AND (${sourceFile || null}::text IS NULL OR source_file = ${sourceFile || null})
    ORDER BY imported_at DESC
    LIMIT 1
  `, { retryRead: true });

  const existing = existingRows[0];
  if (existing === undefined && !keyDate) throw new HttpError(404, "diary entry not found", { key });

  const date = existing === undefined ? keyDate : dateOnly(existing.entry_date);
  const body = bodyProvided ? asText(input.body ?? input.markdown) : existing?.body ?? defaultDiaryBody(date);
  if (body.length > 300_000) throw new HttpError(400, "diary day body must be at most 300000 characters", { date, length: body.length });
  const summary = summaryProvided
    ? normalizeDiarySummary(input.summary, body)
    : bodyProvided || existing === undefined
      ? summaryFromBody(body)
      : existing.summary || summaryFromBody(body);
  const title = titleProvided || existing === undefined ? diaryTitleFor(date, body, input.title) : existing.title;
  const tags = tagsProvided ? asStringArray(input.tags, "tags") : Array.isArray(existing?.tags) ? existing.tags.map(String) : [];
  const finalSourceFile = sourceFileProvided ? sourceFile : validateDiarySourceFile(existing?.source_file ?? "manual");
  const month = date.slice(0, 7);
  const markdownPath = markdownPathForDate(date);
  const hash = contentHash(body);

  const rows = existing === undefined
    ? await withDatabaseRecovery("create_diary_entry_by_date", () => sql<DiaryEntryRow[]>`
      INSERT INTO decision_center_diary_entries (
        id, entry_date, month, title, summary, body, source_file, markdown_path, tags, content_hash, imported_at, updated_at
      ) VALUES (
        ${`diary_${randomUUID()}`},
        ${date}::date,
        ${month},
        ${title},
        ${summary},
        ${body},
        ${finalSourceFile},
        ${markdownPath},
        ${sql.json(tags)},
        ${hash},
        now(),
        now()
      )
      RETURNING *
    `)
    : await withDatabaseRecovery("update_diary_entry", () => sql<DiaryEntryRow[]>`
      UPDATE decision_center_diary_entries
      SET
        title = ${title},
        summary = ${summary},
        body = ${body},
        source_file = ${finalSourceFile},
        markdown_path = ${markdownPath},
        month = ${month},
        tags = ${sql.json(tags)},
        content_hash = ${hash},
        updated_at = CASE
          WHEN content_hash IS DISTINCT FROM ${hash}
            OR title IS DISTINCT FROM ${title}
            OR summary IS DISTINCT FROM ${summary}
            OR source_file IS DISTINCT FROM ${finalSourceFile}
            OR tags IS DISTINCT FROM ${sql.json(tags)}::jsonb
          THEN now()
          ELSE updated_at
        END
      WHERE id = ${existing.id}
      RETURNING *
    `);
  const row = rows[0];
  if (row === undefined) throw new HttpError(500, "diary edit returned no row", { key });
  const action = existing === undefined ? "created" : "updated";
  log("info", "diary_entry_upserted", { id: row.id, date, action, sourceFile: row.source_file });
  return { ok: true, action, entry: diaryEntryFromRow(row) };
}

async function getOrCreateTodayDiaryEntry(input: Record<string, unknown> = {}): Promise<JsonRecord> {
  const today = realTodayUtc();
  try {
    const entry = await getDiaryEntry(today);
    return { ok: true, action: "existing", today, entry };
  } catch (error) {
    if (!(error instanceof HttpError) || error.status !== 404) throw error;
  }
  const body = "body" in input || "markdown" in input ? asText(input.body ?? input.markdown) : defaultDiaryBody(today);
  const result = await upsertDiaryEntryByKey(today, {
    ...input,
    body,
    sourceFile: input.sourceFile ?? input.sourcePath ?? input.source ?? "today",
  });
  const entry = asRecord(result.entry) as DiaryEntry;
  return { ok: true, action: "created", today, entry };
}

async function route(req: Request): Promise<Response> {
  const url = new URL(req.url);
  const method = req.method.toUpperCase();
  if (url.pathname === "/live") {
    return jsonResponse({ ok: true, service: "decision-center", status: "alive", startedAt: serviceStartedAt, deploy: deployInfo() });
  }
  if (url.pathname === "/health") {
    const body = health();
    return jsonResponse(body, body.ok === true ? 200 : 503);
  }
  if (url.pathname === "/logs" && method === "GET") return jsonResponse({ ok: true, logs: recentLogs.slice(-200) });
  if (url.pathname === "/api/records" && method === "GET") return jsonResponse({ ok: true, records: await listRecords(url) });
  if (url.pathname === "/api/records" && method === "POST") return jsonResponse({ ok: true, record: await createRecord(await readJsonBody(req)) }, 201);
  if (url.pathname === "/api/requirements" && method === "GET") return jsonResponse({ ok: true, requirements: await listRecords(url, { requirementOnly: true }) });
  if (url.pathname === "/api/requirements" && method === "POST") {
    const input = await readJsonBody(req);
    if (asString(input.id)) {
      const result = await upsertRequirementRecord(input);
      return jsonResponse(result, result.action === "created" ? 201 : 200);
    }
    return jsonResponse({ ok: true, record: await createRequirementRecord(input) }, 201);
  }
  if (url.pathname === "/api/requirements" && method === "PUT") {
    const result = await upsertRequirementRecord(await readJsonBody(req));
    return jsonResponse(result, result.action === "created" ? 201 : 200);
  }
  if (url.pathname === "/api/meetings/import" && method === "POST") return jsonResponse(await importMeeting(await readJsonBody(req)), 201);
  if (url.pathname === "/api/diary/months" && method === "GET") return jsonResponse({ ok: true, months: await listDiaryMonths() });
  if (url.pathname === "/api/diary/history" && method === "GET") return jsonResponse({ ok: true, entries: await listDiaryEntries(url) });
  if (url.pathname === "/api/diary/today") {
    if (method === "GET") return jsonResponse(await getOrCreateTodayDiaryEntry());
    if (method === "PUT" || method === "POST") {
      const result = await upsertDiaryEntryByKey(realTodayUtc(), await readJsonBody(req));
      return jsonResponse(result, result.action === "created" ? 201 : 200);
    }
    throw new HttpError(405, "diary today route supports GET, POST, and PUT", { method });
  }
  if (url.pathname === "/api/diary/entries" && method === "GET") return jsonResponse({ ok: true, entries: await listDiaryEntries(url) });
  if (url.pathname === "/api/diary/import" && method === "POST") return jsonResponse(await importDiary(await readJsonBody(req)), 201);
  const requirementMatch = url.pathname.match(/^\/api\/requirements\/([^/]+)$/u);
  if (requirementMatch !== null) {
    const id = decodeURIComponent(requirementMatch[1] ?? "");
    if (!id) throw new HttpError(400, "requirement id is required");
    if (method === "GET") return jsonResponse({ ok: true, record: await getRequirementRecord(id) });
    if (method === "PUT") return jsonResponse({ ok: true, record: await updateRequirementRecord(id, await readJsonBody(req)) });
    throw new HttpError(405, "requirement route supports GET and PUT", { method });
  }
  const diaryMatch = url.pathname.match(/^\/api\/diary\/entries\/([^/]+)$/u);
  if (diaryMatch !== null) {
    const key = decodeURIComponent(diaryMatch[1] ?? "");
    if (!key) throw new HttpError(400, "diary entry id or date is required");
    if (method === "GET") return jsonResponse({ ok: true, entry: await getDiaryEntry(key, { sourceFile: sourceFileFilterFromUrl(url) }) });
    if (method === "PUT") {
      const result = await upsertDiaryEntryByKey(key, await readJsonBody(req));
      return jsonResponse(result, result.action === "created" ? 201 : 200);
    }
    throw new HttpError(405, "diary entry route supports GET and PUT", { method });
  }
  const recordMatch = url.pathname.match(/^\/api\/records\/([^/]+)$/u);
  if (recordMatch !== null) {
    const id = decodeURIComponent(recordMatch[1] ?? "");
    if (!id) throw new HttpError(400, "record id is required");
    if (method === "GET") return jsonResponse({ ok: true, record: await getRecord(id) });
    if (method === "PUT") return jsonResponse({ ok: true, record: await updateRecord(id, await readJsonBody(req)) });
    if (method === "DELETE") return jsonResponse(await deleteRecord(id));
    throw new HttpError(405, "record route supports GET, PUT, DELETE", { method });
  }
  throw new HttpError(404, "route not found", { path: url.pathname });
}

Bun.serve({
  hostname: config.host,
  port: config.port,
  async fetch(req) {
    try {
      return await route(req);
    } catch (error) {
      return errorResponse(error);
    }
  },
});

void waitForSchema()
  .then(() => refreshDatabaseHealth())
  .catch((error) => {
    log("error", "schema_init_failed", { error: errorToJson(error) });
  });
setInterval(() => {
  if (schemaReady) void refreshDatabaseHealth();
}, 15_000).unref();
log("info", "service_started", { host: config.host, port: config.port, storage: "postgres" });
