import { Database } from "bun:sqlite";
import { existsSync, statSync } from "node:fs";
import { homedir } from "node:os";
import { basename, join, resolve } from "node:path";
import type { CollectorConfig } from "../config";
import type { Adapter } from "../adapter";
import type { ActionRef, AdapterResult, Item, Panel } from "../schema";
import type { RequestsStore } from "../requests/requests-store";
import { notifyOwnerBlock } from "../requests/requests-announce";

/** Matches the visualizer's default session list cap (see SssfDb.sessions limit=200). */
export const COMPLETED_SESSION_LIMIT = 200;

const DEFAULT_INTERVAL_MS = 5_000;
export const DEFAULT_FACTORY_DB_PATH = join(homedir(), ".local", "state", "overdeck", "factory", "sssf.db");
const EVENT_PAGE_LIMIT = 500;
const FACTORY_DETAIL_INPUT_LIMIT = 7 * 1024 * 1024;
const FACTORY_DETAIL_ROW_OVERHEAD = 512;
const FACTORY_SUMMARY_TEXT_LIMIT = 2_000;
const FACTORY_SUMMARY_DECISION_LIMIT = 20;
const REQUIRED_TABLES = [
  "sessions",
  "phases",
  "events",
] as const;
const OPTIONAL_TRACE_TABLES = ["agent_attempts", "gate_results", "phase_diffs", "processes"] as const;

export const FACTORY_PANEL_ID = "factory-runs";

export interface FactoryEventView {
  rowid: number;
  eventId: string;
  phaseId: string | null;
  parentId: string | null;
  type: string | null;
  name: string | null;
  payloadJson: string | null;
  tokens: number | null;
  startedAt: string | null;
  endedAt: string | null;
}

export interface FactoryPhaseView {
  phaseId: string;
  /** Authoritative orchestration task identity; null for legacy traces. */
  taskId?: string | null;
  seq: number | null;
  name: string | null;
  kind: string | null;
  owner: string | null;
  description: string | null;
  status: string | null;
  attempt: number | null;
  retries: number | null;
  error: string | null;
  startedAt: string | null;
  endedAt: string | null;
  durationMs: number | null;
  /** Summed from `agent_end` events.tokens in this phase. */
  tokens: number;
  /** Summed from `agent_end` payload_json.cost in this phase. */
  spend: number;
}

/** One agent attempt within a phase. */
export interface FactoryAgentAttemptView {
  attemptId: string;
  phaseId: string | null;
  agent: string | null;
  sessionId: string | null;
  command: string | null;
  systemPrompt: string | null;
  userPrompt: string | null;
  returncode: number | null;
  signal: number | null;
  timedOut: boolean;
  timeoutKind: string | null;
  tokens: number | null;
  error: string | null;
  stderrPath: string | null;
  host: string | null;
  account: string | null;
  model: string | null;
  usage: FactoryUsageView | null;
  providerFailure: FactoryProviderFailureView | null;
  startedAt: string | null;
  lastOutputAt: string | null;
  endedAt: string | null;
  durationMs: number | null;
  idleMs: number | null;
  toolCalls: FactoryToolCallView[];
}

export interface FactoryProviderFailureView {
  kind: "capped" | "unavailable" | "protocol" | "context_overflow" | "timeout" | "cancelled";
  detail: string;
  retryAfterSeconds?: number | null;
  resumeAt?: string | null;
}

export interface FactoryUsageView {
  inputTokens: number | null;
  outputTokens: number | null;
  cacheReadTokens: number | null;
  cacheWriteTokens: number | null;
  reasoningTokens: number | null;
  totalTokens: number | null;
  inputCost: number | null;
  outputCost: number | null;
  cacheReadCost: number | null;
  cacheWriteCost: number | null;
  totalCost: number | null;
  usageEstimated: boolean;
  billingStatus: string | null;
  maxTokens: number | null;
  contextWindow: number | null;
  providerFailure?: FactoryProviderFailureView | null;
}

/** One tool invocation made by an agent during an attempt. */
export interface FactoryToolCallView {
  toolCallId: string;
  seq: number;
  toolName: string | null;
  args: Record<string, unknown> | null;
  startedAt: string | null;
  endedAt: string | null;
  durationMs: number | null;
  ok: boolean | null;
  resultExcerpt: string | null;
}

/** One gate evaluation for a phase attempt. */
export interface FactoryGateResultView {
  adwId: string;
  phaseId: string | null;
  attempt: number | null;
  gate: string | null;
  passed: boolean | null;
  violations: unknown;
  checks: unknown;
}

/** One file touched by a phase diff. */
export interface FactoryDiffFileView {
  path: string;
  status: string | null;
  insertions: number | null;
  deletions: number | null;
}

/** The working-tree diff a phase attempt produced. */
export interface FactoryPhaseDiffView {
  /** Explicit external linkage; legacy phase diffs are deliberately unavailable. */
  adwId?: string;
  taskId?: string | null;
  attemptId?: string | null;
  linkage?: "linked" | "unavailable" | "conflicting";
  phaseId: string;
  attempt: number | null;
  files: FactoryDiffFileView[];
  insertions: number | null;
  deletions: number | null;
  diffText: string | null;
  truncated: boolean;
  createdAt: string | null;
}

/** One OS process the run owned — the workflow itself or a coding-agent child. */
export interface FactoryProcessView {
  kind: string | null;
  name: string | null;
  pid: number | null;
  command: string | null;
  startedAt: string | null;
  endedAt: string | null;
  durationMs: number | null;
}

export interface FactoryDecisionOptionView {
  value: string;
  label: string;
  recommended?: boolean;
}

export interface FactoryDecisionView {
  decisionId: string;
  phase: string | null;
  question: string;
  options: FactoryDecisionOptionView[];
  freeText: boolean;
  context: string;
  status: string;
  answerValue: string | null;
  answerText: string | null;
  answeredBy: string | null;
  createdAt: string;
  answeredAt: string | null;
}

export interface FactoryRunView {
  adwId: string;
  /** Canonical target repository root for the run (sessions.repo). */
  repo: string | null;
  /** Canonical git repository name (sessions.repo_name); null on legacy schemas. */
  repoName: string | null;
  adwName: string | null;
  /** Human-readable run slug (sessions.run_slug); null on legacy schemas. */
  runSlug: string | null;
  /** Named seat/model arrangement (sessions.preset); null when no preset was selected. */
  preset: string | null;
  request: string | null;
  status: string | null;
  engineer: string | null;
  startedAt: string | null;
  endedAt: string | null;
  totalTokens: number | null;
  totalCost: number | null;
  /** Heavy trace arrays are available from the per-run detail endpoint. */
  detailAvailable?: boolean;
  phases: FactoryPhaseView[];
  events: FactoryEventView[];
  attempts: FactoryAgentAttemptView[];
  gates: FactoryGateResultView[];
  diffs: FactoryPhaseDiffView[];
  processes: FactoryProcessView[];
  unavailableTables: string[];
  /** Full decision history for the run — pending, answered, and canceled. */
  decisions: FactoryDecisionView[];
}

export interface FactoryRequestRunLink {
  requestId: string;
  adwId: string;
  linkedAt: string;
  startedAt: string | null;
  status: string | null;
  repo: string | null;
}

export interface FactoryRequestRunLinksResult {
  available: boolean;
  links: FactoryRequestRunLink[];
}

export interface FactoryPanelData {
  dbPath: string | null;
  dbPresent: boolean;
  runs: FactoryRunView[];
}

export interface FactoryAdapterOptions {
  id?: string;
  interval?: number;
  /** Global sssf.db path — `dbPath` or `snapshotPath` from collector config. */
  dbPath?: string;
  now?: () => number;
  /** Injectable for tests; defaults to Bun built-in SQLite readonly open. */
  openDbImpl?: (path: string) => Database;
  existsImpl?: (path: string) => boolean;
  statImpl?: (path: string) => { dev: number; ino: number };
  /** Durable owner-decision projection; omitted for a read-only factory panel. */
  requests?: RequestsStore;
  /** Injectable fail-open notification seam for a newly blocked projected row. */
  notifyOwnerBlock?: (row: import("../requests/requests-store").RequestRow) => Promise<void>;
}

interface SessionRow {
  adw_id: string;
  adw_name: string | null;
  repo: string | null;
  repo_name: string | null;
  run_slug: string | null;
  preset: string | null;
  request: string | null;
  status: string | null;
  engineer: string | null;
  started_at: string | null;
  ended_at: string | null;
  total_tokens: number | null;
  total_cost: number | null;
}

interface PhaseRow {
  phase_id: string;
  adw_id: string;
  task_id: string | null;
  seq: number | null;
  name: string | null;
  kind: string | null;
  owner: string | null;
  description: string | null;
  status: string | null;
  attempt: number | null;
  retries: number | null;
  error: string | null;
  started_at: string | null;
  ended_at: string | null;
}

interface FactoryDecisionOption {
  value: string;
  label?: string;
  recommended?: boolean;
}

interface DecisionRow {
  decision_id: string;
  adw_id: string;
  phase: string | null;
  question: string;
  options: string;
  free_text: number;
  context: string;
  status: string;
  answer_value: string | null;
  answer_text: string | null;
  answered_by: string | null;
  created_at: string;
  answered_at: string | null;
}

interface PendingDecisionRow {
  decision_id: string;
  adw_id: string;
  phase: string | null;
  question: string;
  options: string;
  free_text: number;
  context: string;
  created_at: string;
  repo: string | null;
  adw_name: string | null;
}

interface ProjectedDecisionRow extends PendingDecisionRow {
  status: string;
}

interface EventRow {
  rowid: number;
  event_id: string;
  adw_id: string;
  phase_id: string | null;
  parent_id: string | null;
  type: string | null;
  name: string | null;
  payload_json: string | null;
  tokens: number | null;
  started_at: string | null;
  ended_at: string | null;
}

interface AgentAttemptRow {
  attempt_id: string;
  adw_id: string;
  phase_id: string | null;
  agent: string | null;
  session_id: string | null;
  command: string | null;
  system_prompt: string | null;
  user_prompt: string | null;
  returncode: number | null;
  signal: number | null;
  timed_out: number | null;
  timeout_kind: string | null;
  tokens: number | null;
  usage_json: string | null;
  error: string | null;
  stderr_path: string | null;
  host: string | null;
  account: string | null;
  model: string | null;
  started_at: string | null;
  last_output_at: string | null;
  ended_at: string | null;
}

interface ToolCallRow {
  tool_call_id: string;
  attempt_id: string;
  seq: number;
  tool_name: string | null;
  args_json: string | null;
  started_at: string | null;
  ended_at: string | null;
  duration_ms: number | null;
  ok: number | null;
  result_excerpt: string | null;
}

interface GateResultRow {
  adw_id: string;
  phase_id: string | null;
  attempt: number | null;
  gate: string | null;
  passed: number | null;
  violations_json: string | null;
  checks_json: string | null;
}

interface PhaseDiffRow {
  adw_id: string;
  task_id: string | null;
  phase_id: string;
  attempt_id: string | null;
  attempt: number | null;
  files_json: string | null;
  insertions: number | null;
  deletions: number | null;
  diff_text: string | null;
  truncated: number | null;
  created_at: string | null;
  linkage_valid: number;
}

interface ProcessRow {
  adw_id: string;
  kind: string | null;
  name: string | null;
  pid: number | null;
  command: string | null;
  started_at: string | null;
  ended_at: string | null;
}

interface DbFingerprint {
  path: string;
  dev: number;
  ino: number;
}

interface AdapterState {
  db: Database | null;
  fingerprint: DbFingerprint | null;
  lastEventRowid: number;
  eventsByAdw: Map<string, EventRow[]>;
  columnCache: Map<string, boolean>;
  tableCache: Map<string, boolean>;
}

function defaultOpenDb(path: string): Database {
  const db = new Database(path, { readonly: true });
  db.exec("PRAGMA busy_timeout = 5000");
  db.exec("PRAGMA synchronous = NORMAL");
  return db;
}

export function resolveFactoryDbPath(dbPath?: string): string {
  return resolve(dbPath ?? DEFAULT_FACTORY_DB_PATH);
}

export function configuredFactoryDbPath(config: CollectorConfig): string {
  const settings = config.adapters.factory;
  return resolveFactoryDbPath(settings?.dbPath ?? settings?.snapshotPath);
}

function resolveDbPath(opts: FactoryAdapterOptions): string {
  return resolveFactoryDbPath(opts.dbPath);
}

function hasRequiredSchema(db: Database): boolean {
  const rows = db.query<{ name: string }, []>(
    "SELECT name FROM sqlite_master WHERE type = 'table'",
  ).all();
  const names = new Set(rows.map((row) => row.name));
  return REQUIRED_TABLES.every((table) => names.has(table));
}

function hasTable(state: AdapterState, table: string): boolean {
  if (state.tableCache.get(table)) return true;
  const row = state.db!.query<{ present: number }, [string]>(
    "SELECT 1 AS present FROM sqlite_master WHERE type = 'table' AND name = ? LIMIT 1",
  ).get(table);
  const present = row !== null && row !== undefined;
  if (present) state.tableCache.set(table, true);
  return present;
}

function hasColumn(state: AdapterState, table: string, column: string): boolean {
  const key = `${table}.${column}`;
  if (state.columnCache.get(key)) return true;
  const cols = state.db!
    .query<{ name: string }, []>(`PRAGMA table_info(${table})`)
    .all();
  const present = cols.some((col) => col.name === column);
  if (present) state.columnCache.set(key, true);
  return present;
}

function optionalColumn(state: AdapterState, table: string, column: string): string {
  return hasColumn(state, table, column) ? column : `NULL AS ${column}`;
}

function resetState(state: AdapterState): void {
  state.db?.close();
  state.db = null;
  state.fingerprint = null;
  state.lastEventRowid = 0;
  state.eventsByAdw.clear();
  state.columnCache.clear();
  state.tableCache.clear();
}

function fingerprintOf(
  path: string,
  statImpl: (path: string) => { dev: number; ino: number },
): DbFingerprint {
  const stat = statImpl(path);
  return { path, dev: stat.dev, ino: stat.ino };
}

function fingerprintsDiffer(left: DbFingerprint | null, right: DbFingerprint | null): boolean {
  if (!left || !right) return true;
  return left.path !== right.path || left.dev !== right.dev || left.ino !== right.ino;
}

function parseAgentEndCost(payloadJson: string | null): number {
  if (!payloadJson) return 0;
  try {
    const payload = JSON.parse(payloadJson) as { cost?: unknown };
    return typeof payload.cost === "number" && Number.isFinite(payload.cost) ? payload.cost : 0;
  } catch {
    return 0;
  }
}

function derivePhaseMetrics(events: EventRow[]): Map<string, { tokens: number; spend: number }> {
  const byPhase = new Map<string, { tokens: number; spend: number }>();
  for (const event of events) {
    if (event.type !== "agent_end" || !event.phase_id) continue;
    const bucket = byPhase.get(event.phase_id) ?? { tokens: 0, spend: 0 };
    bucket.tokens += event.tokens ?? 0;
    bucket.spend += parseAgentEndCost(event.payload_json);
    byPhase.set(event.phase_id, bucket);
  }
  return byPhase;
}

function durationMs(startedAt: string | null, endedAt: string | null): number | null {
  if (!startedAt || !endedAt) return null;
  const started = Date.parse(startedAt);
  const ended = Date.parse(endedAt);
  return Number.isFinite(started) && Number.isFinite(ended) && ended >= started ? ended - started : null;
}

function parseChecks(raw: string | null): unknown {
  if (raw === null) return null;
  try {
    return JSON.parse(raw);
  } catch {
    return raw;
  }
}

function parseViolations(raw: string | null): unknown {
  if (!raw) return [];
  try {
    const parsed = JSON.parse(raw) as unknown;
    return Array.isArray(parsed) ? parsed : [];
  } catch {
    return [];
  }
}

function parseToolArgs(raw: string | null): Record<string, unknown> | null {
  if (raw === null) return null;
  try {
    const parsed = JSON.parse(raw) as unknown;
    return parsed !== null && typeof parsed === "object" && !Array.isArray(parsed)
      ? parsed as Record<string, unknown>
      : null;
  } catch {
    return null;
  }
}

function parseProviderFailure(raw: unknown): FactoryProviderFailureView | null {
  if (!raw || typeof raw !== "object" || Array.isArray(raw)) return null;
  const item = raw as Record<string, unknown>;
  const kind = item.kind;
  const detail = item.detail;
  if (kind !== "capped" && kind !== "unavailable" && kind !== "protocol"
    && kind !== "context_overflow" && kind !== "timeout" && kind !== "cancelled") return null;
  if (typeof detail !== "string" || detail.trim() === "") return null;
  return {
    kind,
    detail,
    retryAfterSeconds: typeof item.retry_after_seconds === "number" ? item.retry_after_seconds : null,
    resumeAt: typeof item.resume_at === "string" ? item.resume_at : null,
  };
}

function parseAttemptUsage(raw: string | null): FactoryUsageView | null {
  if (raw === null) return null;
  try {
    const parsed = JSON.parse(raw) as Record<string, unknown>;
    return {
      inputTokens: typeof parsed.input_tokens === "number" ? parsed.input_tokens : null,
      outputTokens: typeof parsed.output_tokens === "number" ? parsed.output_tokens : null,
      cacheReadTokens: typeof parsed.cache_read_tokens === "number" ? parsed.cache_read_tokens : null,
      cacheWriteTokens: typeof parsed.cache_write_tokens === "number" ? parsed.cache_write_tokens : null,
      reasoningTokens: typeof parsed.reasoning_tokens === "number" ? parsed.reasoning_tokens : null,
      totalTokens: typeof parsed.total_tokens === "number" ? parsed.total_tokens : null,
      inputCost: typeof parsed.input_cost === "number" ? parsed.input_cost : null,
      outputCost: typeof parsed.output_cost === "number" ? parsed.output_cost : null,
      cacheReadCost: typeof parsed.cache_read_cost === "number" ? parsed.cache_read_cost : null,
      cacheWriteCost: typeof parsed.cache_write_cost === "number" ? parsed.cache_write_cost : null,
      totalCost: typeof parsed.total_cost === "number" ? parsed.total_cost : null,
      usageEstimated: parsed.usage_estimated === true,
      billingStatus: typeof parsed.billing_status === "string" ? parsed.billing_status : null,
      maxTokens: typeof parsed.max_tokens === "number" ? parsed.max_tokens : null,
      contextWindow: typeof parsed.context_window === "number" ? parsed.context_window : null,
      providerFailure: parseProviderFailure(parsed.provider_failure),
    };
  } catch {
    return null;
  }
}

function toEventView(row: EventRow): FactoryEventView {
  return {
    rowid: row.rowid,
    eventId: row.event_id,
    phaseId: row.phase_id,
    parentId: row.parent_id,
    type: row.type,
    name: row.name,
    payloadJson: row.payload_json,
    tokens: row.tokens,
    startedAt: row.started_at,
    endedAt: row.ended_at,
  };
}

export class FactoryRunDetailTooLargeError extends Error {
  constructor() {
    super("factory-run-detail-too-large");
    this.name = "FactoryRunDetailTooLargeError";
  }
}

function eventInputBytes(row: EventRow): number {
  return FACTORY_DETAIL_ROW_OVERHEAD + [
    row.event_id, row.adw_id, row.phase_id, row.parent_id, row.type,
    row.name, row.payload_json, row.started_at, row.ended_at,
  ].reduce((total, value) => total + (value?.length ?? 0), 0);
}

function bootstrapSessionEvents(state: AdapterState, adwId: string): void {
  const db = state.db!;
  let cursor = 0;
  const collected: EventRow[] = [];
  let inputBytes = 0;
  while (true) {
    const page = db.query<EventRow, [string, number, number]>(
      `SELECT rowid, event_id, adw_id, phase_id, parent_id, type, name,
              payload_json, tokens, started_at, ended_at
         FROM events
        WHERE adw_id = ? AND rowid > ?
        ORDER BY rowid
        LIMIT ?`,
    ).all(adwId, cursor, EVENT_PAGE_LIMIT);
    if (page.length === 0) break;
    for (const row of page) {
      inputBytes += eventInputBytes(row);
      if (inputBytes > FACTORY_DETAIL_INPUT_LIMIT) throw new FactoryRunDetailTooLargeError();
      collected.push(row);
    }
    cursor = page[page.length - 1]!.rowid;
    if (page.length < EVENT_PAGE_LIMIT) break;
  }
  state.eventsByAdw.set(adwId, collected);
}

function loadSessions(state: AdapterState): SessionRow[] {
  const db = state.db!;
  const archivedFilter = hasColumn(state, "sessions", "archived")
    ? "COALESCE(archived, 0) = 0"
    : "1 = 1";
  const adwName = optionalColumn(state, "sessions", "adw_name");
  const repo = optionalColumn(state, "sessions", "repo");
  const repoName = optionalColumn(state, "sessions", "repo_name");
  const runSlug = optionalColumn(state, "sessions", "run_slug");
  const preset = optionalColumn(state, "sessions", "preset");

  const running = db.query<SessionRow, []>(
    `SELECT adw_id, ${adwName}, ${repo}, ${repoName}, ${runSlug}, ${preset}, request, status, engineer,
            started_at, ended_at, total_tokens, total_cost
       FROM sessions
      WHERE status = 'running' AND ${archivedFilter}
      ORDER BY started_at DESC, rowid DESC`,
  ).all();

  const completed = db.query<SessionRow, [number]>(
    `SELECT adw_id, ${adwName}, ${repo}, ${repoName}, ${runSlug}, ${preset}, request, status, engineer,
            started_at, ended_at, total_tokens, total_cost
       FROM sessions
      WHERE (status IS NULL OR status != 'running') AND ${archivedFilter}
      ORDER BY started_at DESC, rowid DESC
      LIMIT ?`,
  ).all(COMPLETED_SESSION_LIMIT);

  const seen = new Set<string>();
  const merged: SessionRow[] = [];
  for (const row of [...running, ...completed]) {
    if (seen.has(row.adw_id)) continue;
    seen.add(row.adw_id);
    merged.push(row);
  }
  return merged;
}

function loadPhases(state: AdapterState, adwIds: string[]): Map<string, PhaseRow[]> {
  const byAdw = new Map<string, PhaseRow[]>();
  if (adwIds.length === 0) return byAdw;
  const db = state.db!;
  const placeholders = adwIds.map(() => "?").join(", ");
  const taskId = hasColumn(state, "phases", "task_id") ? "task_id" : "NULL AS task_id";
  const rows = db.query<PhaseRow, string[]>(
    `SELECT phase_id, adw_id, ${taskId}, seq, name, kind, owner, description, status,
            attempt, retries, error, started_at, ended_at
       FROM phases
      WHERE adw_id IN (${placeholders})
      ORDER BY seq, rowid`,
  ).all(...adwIds);
  for (const row of rows) {
    const list = byAdw.get(row.adw_id);
    if (list) list.push(row);
    else byAdw.set(row.adw_id, [row]);
  }
  return byAdw;
}

function loadToolCalls(state: AdapterState, adwIds: string[]): Map<string, FactoryToolCallView[]> {
  const byAttempt = new Map<string, FactoryToolCallView[]>();
  if (adwIds.length === 0 || !hasTable(state, "tool_calls")) return byAttempt;
  const placeholders = adwIds.map(() => "?").join(", ");
  const rows = state.db!.query<ToolCallRow, string[]>(
    `SELECT tc.tool_call_id, tc.attempt_id, tc.seq, tc.tool_name, tc.args_json,
            tc.started_at, tc.ended_at, tc.duration_ms, tc.ok, tc.result_excerpt
       FROM tool_calls tc
       INNER JOIN agent_attempts aa ON aa.attempt_id = tc.attempt_id
      WHERE aa.adw_id IN (${placeholders})
      ORDER BY tc.attempt_id, tc.seq`,
  ).all(...adwIds);
  for (const row of rows) {
    const view: FactoryToolCallView = {
      toolCallId: row.tool_call_id,
      seq: row.seq,
      toolName: row.tool_name,
      args: parseToolArgs(row.args_json),
      startedAt: row.started_at,
      endedAt: row.ended_at,
      durationMs: row.duration_ms,
      ok: row.ok === null ? null : row.ok === 1,
      resultExcerpt: row.result_excerpt,
    };
    const list = byAttempt.get(row.attempt_id) ?? [];
    list.push(view);
    byAttempt.set(row.attempt_id, list);
  }
  return byAttempt;
}

function loadAttempts(state: AdapterState, adwIds: string[], polledAt: number): Map<string, FactoryAgentAttemptView[]> {
  const byAdw = new Map<string, FactoryAgentAttemptView[]>();
  if (adwIds.length === 0 || !hasTable(state, "agent_attempts")) return byAdw;
  const placeholders = adwIds.map(() => "?").join(", ");
  const host = hasColumn(state, "agent_attempts", "host") ? "aa.host" : "NULL AS host";
  const account = hasColumn(state, "agent_attempts", "account") ? "aa.account" : "NULL AS account";
  const systemPrompt = hasColumn(state, "agent_attempts", "system_prompt") ? "aa.system_prompt" : "NULL AS system_prompt";
  const userPrompt = hasColumn(state, "agent_attempts", "user_prompt") ? "aa.user_prompt" : "NULL AS user_prompt";
  const lastOutputAt = hasColumn(state, "agent_attempts", "last_output_at") ? "aa.last_output_at" : "NULL AS last_output_at";
  const timeoutKind = hasColumn(state, "agent_attempts", "timeout_kind") ? "aa.timeout_kind" : "NULL AS timeout_kind";
  const attemptModel = hasColumn(state, "agent_attempts", "model") ? "aa.model" : "NULL";
  const model = hasTable(state, "agent_sessions")
    ? `COALESCE(${attemptModel}, s.model) AS model`
    : `${attemptModel} AS model`;
  const join = hasTable(state, "agent_sessions")
    ? "LEFT JOIN agent_sessions s ON s.adw_id = aa.adw_id AND s.agent = aa.agent"
    : "";
  const toolCallsByAttempt = loadToolCalls(state, adwIds);
  const rows = state.db!.query<AgentAttemptRow, string[]>(
    `SELECT aa.attempt_id, aa.adw_id, aa.phase_id, aa.agent, aa.session_id, aa.command, ${systemPrompt}, ${userPrompt}, aa.returncode, aa.signal,
            aa.timed_out, ${timeoutKind}, aa.tokens, aa.usage_json, aa.error, aa.stderr_path, ${host}, ${account}, ${model},
            aa.started_at, ${lastOutputAt}, aa.ended_at
       FROM agent_attempts aa ${join}
      WHERE aa.adw_id IN (${placeholders}) ORDER BY aa.started_at, aa.rowid`,
  ).all(...adwIds);
  for (const row of rows) {
    const usage = parseAttemptUsage(row.usage_json);
    const view: FactoryAgentAttemptView = {
      attemptId: row.attempt_id, phaseId: row.phase_id, agent: row.agent, sessionId: row.session_id,
      command: row.command, systemPrompt: row.system_prompt, userPrompt: row.user_prompt,
      returncode: row.returncode, signal: row.signal, timedOut: row.timed_out === 1,
      timeoutKind: row.timeout_kind,
      tokens: row.tokens, usage, providerFailure: usage?.providerFailure ?? null,
      error: row.error, stderrPath: row.stderr_path, host: row.host, account: row.account, model: row.model,
      startedAt: row.started_at, lastOutputAt: row.last_output_at, endedAt: row.ended_at,
      durationMs: durationMs(row.started_at, row.ended_at),
      idleMs: row.ended_at === null ? durationMs(row.last_output_at, new Date(polledAt).toISOString()) : null,
      toolCalls: toolCallsByAttempt.get(row.attempt_id) ?? [],
    };
    const list = byAdw.get(row.adw_id) ?? [];
    list.push(view);
    byAdw.set(row.adw_id, list);
  }
  return byAdw;
}

function loadGates(state: AdapterState, adwIds: string[]): Map<string, FactoryGateResultView[]> {
  const byAdw = new Map<string, FactoryGateResultView[]>();
  if (adwIds.length === 0 || !hasTable(state, "gate_results")) return byAdw;
  const placeholders = adwIds.map(() => "?").join(", ");
  const checks = optionalColumn(state, "gate_results", "checks_json");
  const violations = optionalColumn(state, "gate_results", "violations_json");
  const rows = state.db!.query<GateResultRow, string[]>(
    `SELECT adw_id, phase_id, attempt, gate, passed, ${violations}, ${checks} FROM gate_results
      WHERE adw_id IN (${placeholders}) ORDER BY rowid`,
  ).all(...adwIds);
  for (const row of rows) {
    const view: FactoryGateResultView = {
      adwId: row.adw_id, phaseId: row.phase_id, attempt: row.attempt, gate: row.gate,
      passed: row.passed === null ? null : row.passed === 1,
      violations: parseViolations(row.violations_json), checks: parseChecks(row.checks_json),
    };
    const list = byAdw.get(row.adw_id) ?? [];
    list.push(view);
    byAdw.set(row.adw_id, list);
  }
  return byAdw;
}

function numberField(value: unknown): number | null {
  return typeof value === "number" && Number.isFinite(value) ? value : null;
}

function parseFiles(json: string | null): FactoryDiffFileView[] {
  if (!json) return [];
  let parsed: unknown;
  try {
    parsed = JSON.parse(json);
  } catch {
    return [];
  }
  if (!Array.isArray(parsed)) return [];
  const files: FactoryDiffFileView[] = [];
  for (const entry of parsed) {
    if (typeof entry === "string") {
      files.push({ path: entry, status: null, insertions: null, deletions: null });
      continue;
    }
    if (entry === null || typeof entry !== "object") continue;
    const row = entry as Record<string, unknown>;
    if (typeof row.path !== "string") continue;
    files.push({
      path: row.path,
      status: typeof row.status === "string" ? row.status : null,
      insertions: numberField(row.insertions),
      deletions: numberField(row.deletions),
    });
  }
  return files;
}

function loadDiffs(state: AdapterState, adwIds: string[]): Map<string, FactoryPhaseDiffView[]> {
  const byAdw = new Map<string, FactoryPhaseDiffView[]>();
  if (adwIds.length === 0 || !hasTable(state, "phase_diffs")) return byAdw;
  const placeholders = adwIds.map(() => "?").join(", ");
  const hasTaskId = hasColumn(state, "phase_diffs", "task_id");
  const hasAttemptId = hasColumn(state, "phase_diffs", "attempt_id");
  const hasPhaseTaskId = hasColumn(state, "phases", "task_id");
  const canValidateLinkage = hasTaskId && hasAttemptId && hasPhaseTaskId && hasTable(state, "agent_attempts");
  const linkageValidity = canValidateLinkage
    ? `CASE WHEN EXISTS (
         SELECT 1 FROM phases p
         INNER JOIN agent_attempts aa
           ON aa.attempt_id = phase_diffs.attempt_id
          AND aa.adw_id = phase_diffs.adw_id
          AND aa.phase_id = phase_diffs.phase_id
        WHERE p.adw_id = phase_diffs.adw_id
          AND p.phase_id = phase_diffs.phase_id
          AND phase_diffs.task_id = p.task_id
       ) THEN 1 ELSE 0 END`
    : "0";
  const rows = state.db!.query<PhaseDiffRow, string[]>(
    `SELECT adw_id, ${hasTaskId ? "task_id" : "NULL AS task_id"}, phase_id, ${hasAttemptId ? "attempt_id" : "NULL AS attempt_id"}, attempt, files_json, insertions, deletions, diff_text, truncated, created_at, ${linkageValidity} AS linkage_valid
       FROM phase_diffs WHERE adw_id IN (${placeholders}) ORDER BY rowid`,
  ).all(...adwIds);
  for (const row of rows) {
    const view: FactoryPhaseDiffView = {
      adwId: row.adw_id,
      taskId: row.task_id,
      attemptId: row.attempt_id,
      linkage: row.linkage_valid === 1 ? "linked" : "unavailable",
      phaseId: row.phase_id, attempt: row.attempt, files: parseFiles(row.files_json),
      insertions: row.insertions, deletions: row.deletions, diffText: row.diff_text,
      truncated: row.truncated === 1, createdAt: row.created_at,
    };
    const list = byAdw.get(row.adw_id) ?? [];
    list.push(view);
    byAdw.set(row.adw_id, list);
  }
  return byAdw;
}

function loadProcesses(state: AdapterState, adwIds: string[]): Map<string, FactoryProcessView[]> {
  const byAdw = new Map<string, FactoryProcessView[]>();
  if (adwIds.length === 0 || !hasTable(state, "processes")) return byAdw;
  const placeholders = adwIds.map(() => "?").join(", ");
  const rows = state.db!.query<ProcessRow, string[]>(
    `SELECT adw_id, kind, name, pid, command, started_at, ended_at
       FROM processes WHERE adw_id IN (${placeholders}) ORDER BY rowid`,
  ).all(...adwIds);
  for (const row of rows) {
    const view: FactoryProcessView = {
      kind: row.kind, name: row.name, pid: row.pid, command: row.command,
      startedAt: row.started_at, endedAt: row.ended_at,
      durationMs: durationMs(row.started_at, row.ended_at),
    };
    const list = byAdw.get(row.adw_id) ?? [];
    list.push(view);
    byAdw.set(row.adw_id, list);
  }
  return byAdw;
}

function hasDecisionsTable(state: AdapterState): boolean {
  return hasTable(state, "decisions");
}

function repoProject(repo: string | null): string | undefined {
  if (!repo?.trim()) return undefined;
  return basename(repo);
}

function parseDecisionOptions(raw: string): FactoryDecisionOption[] {
  try {
    const parsed = JSON.parse(raw) as unknown;
    if (!Array.isArray(parsed)) return [];
    return parsed.flatMap((entry) => {
      if (!entry || typeof entry !== "object") return [];
      const value = (entry as { value?: unknown }).value;
      if (typeof value !== "string" || value.length === 0) return [];
      const label = (entry as { label?: unknown }).label;
      const recommended = (entry as { recommended?: unknown }).recommended;
      return [{
        value,
        label: typeof label === "string" && label.length > 0 ? label : value,
        recommended: recommended === true ? true : undefined,
      }];
    });
  } catch {
    return [];
  }
}

function decisionContext(row: PendingDecisionRow): string {
  if (row.context.trim()) return row.context.trim();
  const parts = [row.adw_name?.trim(), row.repo?.trim()].filter((part): part is string => Boolean(part));
  return parts.length > 0 ? parts.join(" — ") : row.adw_id;
}

function toDecisionView(row: DecisionRow): FactoryDecisionView {
  return {
    decisionId: row.decision_id,
    phase: row.phase,
    question: row.question,
    options: parseDecisionOptions(row.options).map((option) => ({
      value: option.value,
      label: option.label ?? option.value,
      recommended: option.recommended,
    })),
    freeText: row.free_text === 1,
    context: row.context,
    status: row.status,
    answerValue: row.answer_value,
    answerText: row.answer_text,
    answeredBy: row.answered_by,
    createdAt: row.created_at,
    answeredAt: row.answered_at,
  };
}

function loadSummaryDecisions(state: AdapterState, adwIds: string[]): Map<string, FactoryDecisionView[]> {
  const byAdw = new Map<string, FactoryDecisionView[]>();
  if (adwIds.length === 0 || !hasDecisionsTable(state)) return byAdw;
  const query = state.db!.query<DecisionRow, [string, number]>(
    `SELECT decision_id, adw_id, phase, question, options, free_text, context, status,
            answer_value, answer_text, answered_by, created_at, answered_at
       FROM decisions
      WHERE adw_id = ?
      ORDER BY created_at DESC, rowid DESC
      LIMIT ?`,
  );
  for (const adwId of adwIds) {
    byAdw.set(adwId, query.all(adwId, FACTORY_SUMMARY_DECISION_LIMIT).reverse().map(toDecisionView));
  }
  return byAdw;
}

function loadDecisions(state: AdapterState, adwIds: string[]): Map<string, FactoryDecisionView[]> {
  const byAdw = new Map<string, FactoryDecisionView[]>();
  if (adwIds.length === 0 || !hasDecisionsTable(state)) return byAdw;
  const placeholders = adwIds.map(() => "?").join(", ");
  const rows = state.db!.query<DecisionRow, string[]>(
    `SELECT decision_id, adw_id, phase, question, options, free_text, context, status,
            answer_value, answer_text, answered_by, created_at, answered_at
       FROM decisions
      WHERE adw_id IN (${placeholders})
      ORDER BY created_at, rowid`,
  ).all(...adwIds);
  for (const row of rows) {
    const view = toDecisionView(row);
    const list = byAdw.get(row.adw_id);
    if (list) list.push(view);
    else byAdw.set(row.adw_id, [view]);
  }
  return byAdw;
}

function loadPendingDecisions(state: AdapterState): PendingDecisionRow[] {
  if (!hasDecisionsTable(state)) return [];
  const db = state.db!;
  const archivedFilter = hasColumn(state, "sessions", "archived")
    ? "COALESCE(s.archived, 0) = 0"
    : "1 = 1";
  const adwName = optionalColumn(state, "sessions", "adw_name");
  const repo = optionalColumn(state, "sessions", "repo");
  return db.query<PendingDecisionRow, []>(
    `SELECT d.decision_id, d.adw_id, d.phase, d.question, d.options, d.free_text, d.context,
            d.created_at, ${adwName} AS adw_name, ${repo} AS repo
       FROM decisions d
       INNER JOIN sessions s ON s.adw_id = d.adw_id
      WHERE d.status = 'pending' AND ${archivedFilter}
      ORDER BY d.created_at`,
  ).all();
}

function loadProjectedDecisions(state: AdapterState): ProjectedDecisionRow[] {
  if (!hasDecisionsTable(state)) return [];
  const adwName = optionalColumn(state, "sessions", "adw_name");
  const repo = optionalColumn(state, "sessions", "repo");
  return state.db!.query<ProjectedDecisionRow, []>(
    `SELECT d.decision_id, d.adw_id, d.phase, d.question, d.options, d.free_text, d.context,
            d.created_at, ${adwName} AS adw_name, ${repo} AS repo, d.status
       FROM decisions d
       LEFT JOIN sessions s ON s.adw_id = d.adw_id
      ORDER BY d.created_at, d.rowid`,
  ).all();
}

async function projectFactoryDecisions(
  state: AdapterState,
  requests: RequestsStore,
  notify: (row: import("../requests/requests-store").RequestRow) => Promise<void>,
): Promise<void> {
  for (const decision of loadProjectedDecisions(state)) {
    const workKey = `factory-decision-${decision.decision_id}`;
    const existing = requests.resolve(workKey);
    if (decision.status === "pending") {
      const row = existing ?? requests.create({
        id: workKey,
        title: decision.question,
        project: repoProject(decision.repo) ?? "factory",
        state: "asked",
        priority: "HIGH",
        origin: "agent-judgement",
        asked_at: decision.created_at,
        updated_at: decision.created_at,
        plan_ref: workKey,
        factory_run_id: decision.adw_id,
      });
      if (row.state === "asked" || row.state === "in_flight") {
        const blocked = requests.transition(workKey, {
          state: "blocked_needs_owner",
          actor: "factory-adapter",
          reason: decision.question,
        });
        // Botmaster is optional; notifyOwnerBlock is fail-open by contract.
        await notify(blocked);
      }
      continue;
    }
    if (!existing || existing.state !== "blocked_needs_owner") continue;
    // Keep the reviewed request matrix intact: a closed factory decision
    // re-enters work before its decision-only row is shipped.
    const resumed = requests.transition(workKey, {
      state: "in_flight",
      actor: "factory-adapter",
    });
    if (decision.status !== "answered") {
      requests.transition(workKey, { state: "shipped", actor: "factory-adapter", worker: resumed.worker });
    }
  }
}

function buildDecisionItem(source: string, row: PendingDecisionRow): Item {
  const options = parseDecisionOptions(row.options);
  const context = decisionContext(row);
  const actions: ActionRef[] = options.map((option) => ({
    verb: "factory.decision.answer",
    args: { decisionId: row.decision_id, choice: option.value },
    label: option.label ?? option.value,
    recommended: option.recommended,
  }));
  return {
    id: `factory-decision-${row.decision_id}`,
    source,
    project: repoProject(row.repo),
    severity: "act",
    kind: "decision",
    title: row.question,
    detail: context,
    ts: row.created_at,
    actions,
    decision: {
      question: row.question,
      options: options.map((option) => ({ label: option.label ?? option.value, recommended: option.recommended })),
      freeText: row.free_text === 1,
      context,
      waitingSince: row.created_at,
    },
  };
}

function buildHaltItem(source: string, run: FactoryRunView, ts: string): Item {
  return {
    id: `factory-halt-${run.adwId}`,
    source,
    project: run.repo ?? undefined,
    severity: "act",
    kind: "halt",
    title: run.request?.trim() || `Factory run ${run.adwId} failed`,
    detail: run.adwName ? `${run.adwName} — status fail` : "status fail",
    ts: run.endedAt ?? run.startedAt ?? ts,
    actions: [],
  };
}

function buildRunningItem(source: string, run: FactoryRunView, ts: string): Item {
  return {
    id: `factory-running-${run.adwId}`,
    source,
    project: run.repo ?? undefined,
    severity: "info",
    kind: "progress",
    title: run.request?.trim() || `Factory run ${run.adwId} in progress`,
    detail: run.repo ? `repo ${run.repo}` : run.adwName ?? run.adwId,
    ts: run.startedAt ?? ts,
    actions: [],
  };
}

function emptyPanel(ts: string, dbPath: string, dbPresent: boolean): Panel & { data: FactoryPanelData } {
  return {
    id: FACTORY_PANEL_ID,
    ts,
    data: { dbPath, dbPresent, runs: [] },
  };
}

function detailTableBytes(
  state: AdapterState,
  table: string,
  adwId: string,
): number {
  if (!hasTable(state, table) || !hasColumn(state, table, "adw_id")) return 0;
  const columns = state.db!.query<{ name: string }, []>(`PRAGMA table_info(${table})`).all();
  const textColumns = columns
    .filter((column) => column.name !== "adw_id")
    .map((column) => `COALESCE(length(${column.name}), 0)`);
  if (textColumns.length === 0) return 0;
  const expression = textColumns.join(" + ");
  const row = state.db!.query<{ bytes: number }, [string]>(
    `SELECT COALESCE(SUM(${expression} + ${FACTORY_DETAIL_ROW_OVERHEAD}), 0) AS bytes FROM ${table} WHERE adw_id = ?`,
  ).get(adwId);
  return Number(row?.bytes ?? 0);
}

function toolCallBytes(state: AdapterState, adwId: string): number {
  if (!hasTable(state, "tool_calls") || !hasTable(state, "agent_attempts")) return 0;
  const row = state.db!.query<{ bytes: number }, [string]>(
    `SELECT COALESCE(SUM(
       COALESCE(length(t.tool_call_id), 0) + COALESCE(length(t.tool_name), 0) +
       COALESCE(length(t.args_json), 0) + COALESCE(length(t.result_excerpt), 0) +
       ${FACTORY_DETAIL_ROW_OVERHEAD}), 0) AS bytes
       FROM tool_calls t
       INNER JOIN agent_attempts a ON a.attempt_id = t.attempt_id
       WHERE a.adw_id = ?`,
  ).get(adwId);
  return Number(row?.bytes ?? 0);
}

function assertFactoryDetailBudget(state: AdapterState, adwId: string): void {
  const tables = ["phases", "events", "agent_attempts", "gate_results", "phase_diffs", "processes", "decisions"];
  let bytes = toolCallBytes(state, adwId);
  for (const table of tables) {
    bytes += detailTableBytes(state, table, adwId);
    if (bytes > FACTORY_DETAIL_INPUT_LIMIT) throw new FactoryRunDetailTooLargeError();
  }
}

export function loadFactoryRequestRunLinks(
  requestId: string,
  opts: FactoryAdapterOptions = {},
): FactoryRequestRunLinksResult {
  const dbPath = resolveDbPath(opts);
  const existsImpl = opts.existsImpl ?? existsSync;
  if (!existsImpl(dbPath)) return { available: false, links: [] };
  let db: Database;
  try {
    db = (opts.openDbImpl ?? defaultOpenDb)(dbPath);
  } catch {
    return { available: false, links: [] };
  }
  try {
    const table = db.query<{ name: string }, [string]>(
      "SELECT name FROM sqlite_master WHERE type='table' AND name=?",
    ).get("request_run_links");
    if (!table) return { available: false, links: [] };
    const rows = db.query<{
      request_id: string;
      adw_id: string;
      linked_at: string;
      started_at: string | null;
      status: string | null;
      repo: string | null;
    }, [string]>(`SELECT l.request_id, l.adw_id, l.linked_at, s.started_at, s.status, s.repo
      FROM request_run_links l
      INNER JOIN sessions s ON s.adw_id = l.adw_id
      WHERE l.request_id = ? ORDER BY l.linked_at, l.adw_id`).all(requestId);
    return {
      available: true,
      links: rows.map((row) => ({
        requestId: row.request_id,
        adwId: row.adw_id,
        linkedAt: row.linked_at,
        startedAt: row.started_at,
        status: row.status,
        repo: row.repo,
      })),
    };
  } catch {
    return { available: false, links: [] };
  } finally {
    db.close();
  }
}

export function loadFactoryRunDetail(
  adwId: string,
  opts: FactoryAdapterOptions = {},
): FactoryRunView | undefined {
  const dbPath = resolveDbPath(opts);
  const existsImpl = opts.existsImpl ?? existsSync;
  if (!existsImpl(dbPath)) return undefined;

  const db = (opts.openDbImpl ?? defaultOpenDb)(dbPath);
  const state: AdapterState = {
    db,
    fingerprint: null,
    lastEventRowid: 0,
    eventsByAdw: new Map(),
    columnCache: new Map(),
    tableCache: new Map(),
  };

  try {
    if (!hasRequiredSchema(db)) return undefined;
    assertFactoryDetailBudget(state, adwId);
    const session = loadSessions(state).find((entry) => entry.adw_id === adwId);
    if (!session) return undefined;

    bootstrapSessionEvents(state, adwId);
    const eventRows = state.eventsByAdw.get(adwId) ?? [];
    const phaseMetrics = derivePhaseMetrics(eventRows);
    const phases = (loadPhases(state, [adwId]).get(adwId) ?? []).map((phase) => {
      const metrics = phaseMetrics.get(phase.phase_id) ?? { tokens: 0, spend: 0 };
      return {
        phaseId: phase.phase_id,
        taskId: phase.task_id,
        seq: phase.seq,
        name: phase.name,
        kind: phase.kind,
        owner: phase.owner,
        description: phase.description,
        status: phase.status,
        attempt: phase.attempt,
        retries: phase.retries,
        error: phase.error,
        startedAt: phase.started_at,
        endedAt: phase.ended_at,
        durationMs: durationMs(phase.started_at, phase.ended_at),
        tokens: metrics.tokens,
        spend: metrics.spend,
      } satisfies FactoryPhaseView;
    });
    const unavailableTables = OPTIONAL_TRACE_TABLES.filter((table) => !hasTable(state, table));

    return {
      adwId: session.adw_id,
      repo: session.repo,
      repoName: session.repo_name,
      adwName: session.adw_name,
      runSlug: session.run_slug,
      preset: session.preset,
      request: session.request,
      status: session.status,
      engineer: session.engineer,
      startedAt: session.started_at,
      endedAt: session.ended_at,
      totalTokens: session.total_tokens,
      totalCost: session.total_cost,
      phases,
      events: eventRows.map(toEventView),
      attempts: loadAttempts(state, [adwId], opts.now?.() ?? Date.now()).get(adwId) ?? [],
      gates: loadGates(state, [adwId]).get(adwId) ?? [],
      diffs: loadDiffs(state, [adwId]).get(adwId) ?? [],
      processes: loadProcesses(state, [adwId]).get(adwId) ?? [],
      unavailableTables,
      decisions: loadDecisions(state, [adwId]).get(adwId) ?? [],
    };
  } finally {
    db.close();
  }
}

function boundedSummaryText(value: string | null): string | null {
  if (value === null || value.length <= FACTORY_SUMMARY_TEXT_LIMIT) return value;
  return value.slice(0, FACTORY_SUMMARY_TEXT_LIMIT);
}

function boundedSummaryDecision(decision: FactoryDecisionView): FactoryDecisionView {
  return {
    ...decision,
    question: boundedSummaryText(decision.question) ?? "",
    context: boundedSummaryText(decision.context) ?? "",
    answerText: boundedSummaryText(decision.answerText),
    options: decision.options.slice(0, FACTORY_SUMMARY_DECISION_LIMIT).map((option) => ({
      ...option,
      value: boundedSummaryText(option.value) ?? "",
      label: boundedSummaryText(option.label) ?? "",
    })),
  };
}

export function createFactoryAdapter(opts: FactoryAdapterOptions = {}): Adapter {
  const id = opts.id ?? "factory";
  const interval = opts.interval ?? DEFAULT_INTERVAL_MS;
  const dbPath = resolveDbPath(opts);
  const now = opts.now ?? Date.now;
  const openDbImpl = opts.openDbImpl ?? defaultOpenDb;
  const existsImpl = opts.existsImpl ?? existsSync;
  const statImpl = opts.statImpl ?? ((path: string) => {
    const stat = statSync(path);
    return { dev: Number(stat.dev), ino: Number(stat.ino) };
  });
  const requests = opts.requests;
  const notify = opts.notifyOwnerBlock ?? notifyOwnerBlock;

  const state: AdapterState = {
    db: null,
    fingerprint: null,
    lastEventRowid: 0,
    eventsByAdw: new Map(),
    columnCache: new Map(),
    tableCache: new Map(),
  };

  function ensureDb(): boolean {
    if (!existsImpl(dbPath)) {
      resetState(state);
      return false;
    }

    const nextFingerprint = fingerprintOf(dbPath, statImpl);
    if (fingerprintsDiffer(state.fingerprint, nextFingerprint)) {
      resetState(state);
    }

    if (!state.db) {
      try {
        state.db = openDbImpl(dbPath);
      } catch {
        resetState(state);
        return false;
      }
      if (!hasRequiredSchema(state.db)) {
        resetState(state);
        return false;
      }
      state.fingerprint = nextFingerprint;
      const maxRow = state.db
        .query<{ m: number }, []>("SELECT COALESCE(MAX(rowid), 0) AS m FROM events")
        .get()?.m ?? 0;
      if (maxRow < state.lastEventRowid) {
        state.lastEventRowid = 0;
        state.eventsByAdw.clear();
      }
    }

    return true;
  }

  return {
    id,
    interval,
    async poll(): Promise<AdapterResult> {
      const polledAt = now();
      const ts = new Date(polledAt).toISOString();
      if (!ensureDb()) {
        return { items: [], panels: [emptyPanel(ts, dbPath, false)] };
      }

      if (requests) await projectFactoryDecisions(state, requests, notify);

      const sessions = loadSessions(state);
      const activeAdwIds = sessions.map((session) => session.adw_id);
      const decisionsByAdw = loadSummaryDecisions(state, activeAdwIds);
      const unavailableTables = OPTIONAL_TRACE_TABLES.filter((table) => !hasTable(state, table));
      const runs: FactoryRunView[] = sessions.map((session) => ({
        adwId: session.adw_id,
        repo: session.repo,
        repoName: session.repo_name,
        adwName: session.adw_name,
        runSlug: session.run_slug,
        preset: session.preset,
        request: boundedSummaryText(session.request),
        status: session.status,
        engineer: session.engineer,
        startedAt: session.started_at,
        endedAt: session.ended_at,
        totalTokens: session.total_tokens,
        totalCost: session.total_cost,
        detailAvailable: true,
        phases: [],
        events: [],
        attempts: [],
        gates: [],
        diffs: [],
        processes: [],
        unavailableTables: [...unavailableTables],
        decisions: (decisionsByAdw.get(session.adw_id) ?? [])
          .slice(0, FACTORY_SUMMARY_DECISION_LIMIT)
          .map(boundedSummaryDecision),
      }));

      const items: Item[] = [];
      for (const run of runs) {
        if (run.status === "fail") items.push(buildHaltItem(id, run, ts));
        else if (run.status === "running") items.push(buildRunningItem(id, run, ts));
      }
      for (const decision of loadPendingDecisions(state)) {
        items.push(buildDecisionItem(id, decision));
      }

      const panel: Panel & { data: FactoryPanelData } = {
        id: FACTORY_PANEL_ID,
        ts,
        data: { dbPath, dbPresent: true, runs },
      };

      return { items, panels: [panel] };
    },
  };
}