import Database from "better-sqlite3";
import { v4 as uuidv4 } from "uuid";
import {
  Task,
  TaskEvent,
  TaskEventDetailResult,
  TaskTimelinePageRequest,
  TaskTimelinePageResult,
  TaskTraceRunDetail,
  TaskTraceRunSummary,
  EventType,
  Artifact,
  Annotation,
  AnnotationCreateInput,
  AnnotationListQuery,
  AnnotationStatus,
  AnnotationUpdateInput,
  Workspace,
  ApprovalRequest,
  PersistedPermissionRule,
  InputRequest,
  Skill,
  WorkspacePermissions,
  isTempWorkspaceId,
  WorktreeInfo,
  WorktreeStatus,
  MergeResult,
  ComparisonSession,
  ComparisonSessionStatus,
  ComparisonResult,
  CuratedMemoryEntry,
  CuratedMemoryKind,
  CuratedMemoryTarget,
  ChannelSpecialization,
  CreateChannelSpecializationRequest,
  UpdateChannelSpecializationRequest,
} from "../../shared/types";
import { isActiveTaskStatus, normalizeTaskLifecycleState } from "../../shared/task-status";
import { isTimelineEventType, normalizeTaskEventToTimelineV2 } from "../../shared/timeline-v2";
import { normalizeTaskEvents } from "../agent/timeline/timeline-normalizer";
import {
  sanitizeTimelineEventForStorage,
  sanitizeTimelinePayloadForStorage,
} from "../agent/timeline-payload-sanitizer";
import {
  buildTaskTraceMetrics,
  buildTaskTraceRunSummaries,
  buildTaskTraceSiblingRuns,
  getTaskTraceSessionId,
} from "./task-trace-projection";
import { UsageInsightsProjector } from "../reports/UsageInsightsProjector";
import { enqueueTaskEventTelemetry } from "../telemetry/task-event-exporter";
import { getSafeStorage } from "../utils/safe-storage";
import { createLogger } from "../utils/logger";

/**
 * Safely parse JSON with error handling
 * Returns defaultValue if parsing fails
 */
function safeJsonParse<T>(jsonString: string, defaultValue: T, context?: string): T {
  try {
    return JSON.parse(jsonString);
  } catch (error) {
    console.error(
      `Failed to parse JSON${context ? ` in ${context}` : ""}:`,
      error,
      "Input:",
      jsonString?.slice(0, 100),
    );
    return defaultValue;
  }
}

const taskRepositoryLogger = createLogger("TaskRepository");
const memoryRepositoryLogger = createLogger("MemoryRepository");
const SAFE_SQL_IDENTIFIER = /^[A-Za-z_][A-Za-z0-9_]*$/;

const quoteSqlIdentifier = (identifier: string): string => {
  if (!SAFE_SQL_IDENTIFIER.test(identifier)) {
    throw new Error(`Unsafe SQL identifier: ${identifier}`);
  }
  return `"${identifier}"`;
};

interface SqliteForeignKeyRow {
  table?: string;
  from?: string;
  to?: string;
  on_delete?: string;
}

interface SqliteTableInfoRow {
  name?: string;
  notnull?: number;
  pk?: number;
}

export class WorkspaceRepository {
  constructor(private db: Database.Database) {}

  create(name: string, path: string, permissions: WorkspacePermissions): Workspace {
    const now = Date.now();
    const workspace: Workspace = {
      id: uuidv4(),
      name,
      path,
      createdAt: now,
      lastUsedAt: now,
      permissions,
    };

    const stmt = this.db.prepare(`
      INSERT INTO workspaces (id, name, path, created_at, last_used_at, permissions)
      VALUES (?, ?, ?, ?, ?, ?)
    `);

    stmt.run(
      workspace.id,
      workspace.name,
      workspace.path,
      workspace.createdAt,
      workspace.lastUsedAt,
      JSON.stringify(workspace.permissions),
    );

    return workspace;
  }

  findById(id: string): Workspace | undefined {
    const stmt = this.db.prepare("SELECT * FROM workspaces WHERE id = ?");
    const row = stmt.get(id) as Any;
    return row ? this.mapRowToWorkspace(row) : undefined;
  }

  findAll(): Workspace[] {
    const stmt = this.db.prepare(`
      SELECT *
      FROM workspaces
      ORDER BY COALESCE(last_used_at, created_at) DESC
    `);
    const rows = stmt.all() as Any[];
    return rows.map((row) => this.mapRowToWorkspace(row));
  }

  /**
   * Check if a workspace with the given path already exists
   */
  existsByPath(path: string): boolean {
    const stmt = this.db.prepare("SELECT 1 FROM workspaces WHERE path = ?");
    const row = stmt.get(path);
    return !!row;
  }

  /**
   * Find a workspace by its path
   */
  findByPath(path: string): Workspace | undefined {
    const stmt = this.db.prepare("SELECT * FROM workspaces WHERE path = ?");
    const row = stmt.get(path) as Any;
    return row ? this.mapRowToWorkspace(row) : undefined;
  }

  /**
   * Update workspace permissions
   */
  updatePermissions(id: string, permissions: WorkspacePermissions): void {
    const stmt = this.db.prepare("UPDATE workspaces SET permissions = ? WHERE id = ?");
    stmt.run(JSON.stringify(permissions), id);
  }

  /**
   * Update last used timestamp for recency ordering
   */
  updateLastUsedAt(id: string, lastUsedAt: number = Date.now()): void {
    const stmt = this.db.prepare("UPDATE workspaces SET last_used_at = ? WHERE id = ?");
    stmt.run(lastUsedAt, id);
  }

  /**
   * Update workspace path after the folder is moved.
   */
  updatePath(id: string, nextPath: string): void {
    const stmt = this.db.prepare("UPDATE workspaces SET path = ? WHERE id = ?");
    stmt.run(nextPath, id);
  }

  /**
   * Delete a workspace by ID
   */
  delete(id: string): void {
    const stmt = this.db.prepare("DELETE FROM workspaces WHERE id = ?");
    stmt.run(id);
  }

  private mapRowToWorkspace(row: Any): Workspace {
    // Note: network is true by default for browser tools (web access)
    const defaultPermissions: WorkspacePermissions = {
      read: true,
      write: true,
      delete: false,
      network: true,
      shell: false,
    };
    const storedPermissions = safeJsonParse(
      row.permissions,
      defaultPermissions,
      "workspace.permissions",
    );

    // Merge with defaults to ensure new fields (like network) get proper defaults
    // for workspaces created before those fields existed
    const mergedPermissions: WorkspacePermissions = {
      ...defaultPermissions,
      ...storedPermissions,
    };

    // Migration: if network was explicitly false (old default), upgrade it to true
    // This ensures existing workspaces get browser tool access
    if (storedPermissions.network === false) {
      mergedPermissions.network = true;
    }

    return {
      id: row.id,
      name: row.name,
      path: row.path,
      createdAt: row.created_at,
      lastUsedAt: row.last_used_at ?? undefined,
      permissions: mergedPermissions,
      isTemp: isTempWorkspaceId(typeof row.id === "string" ? row.id : undefined),
    };
  }
}

export class TaskRepository {
  private static readonly UPDATE_FIELD_TO_COLUMN: Partial<Record<keyof Task, string>> = {
    prompt: "prompt",
    rawPrompt: "raw_prompt",
    userPrompt: "user_prompt",
    title: "title",
    status: "status",
    workspaceId: "workspace_id",
    budgetTokens: "budget_tokens",
    budgetCost: "budget_cost",
    successCriteria: "success_criteria",
    maxAttempts: "max_attempts",
    currentAttempt: "current_attempt",
    parentTaskId: "parent_task_id",
    agentType: "agent_type",
    agentConfig: "agent_config",
    depth: "depth",
    resultSummary: "result_summary",
    completedAt: "completed_at",
    lastRunDurationMs: "last_run_duration_ms",
    error: "error",
    pinned: "is_pinned",
    labels: "labels",
    mentionedAgentRoleIds: "mentioned_agent_role_ids",
    strategyLock: "strategy_lock",
    budgetProfile: "budget_profile",
    terminalStatus: "terminal_status",
    failureClass: "failure_class",
    verificationVerdict: "verification_verdict",
    verificationReport: "verification_report",
    bestKnownOutcome: "best_known_outcome",
    budgetUsage: "budget_usage",
    continuationCount: "continuation_count",
    continuationWindow: "continuation_window",
    lifetimeTurnsUsed: "lifetime_turns_used",
    lastProgressScore: "last_progress_score",
    autoContinueBlockReason: "auto_continue_block_reason",
    awaitingUserInputReasonCode: "awaiting_user_input_reason_code",
    compactionCount: "compaction_count",
    lastCompactionAt: "last_compaction_at",
    lastCompactionTokensBefore: "last_compaction_tokens_before",
    lastCompactionTokensAfter: "last_compaction_tokens_after",
    noProgressStreak: "no_progress_streak",
    lastLoopFingerprint: "last_loop_fingerprint",
    riskLevel: "risk_level",
    evalCaseId: "eval_case_id",
    evalRunId: "eval_run_id",
    issueId: "issue_id",
    heartbeatRunId: "heartbeat_run_id",
    companyId: "company_id",
    goalId: "goal_id",
    projectId: "project_id",
    requestDepth: "request_depth",
    billingCode: "billing_code",
    assignedAgentRoleId: "assigned_agent_role_id",
    workerRole: "worker_role",
    boardColumn: "board_column",
    priority: "priority",
    dueDate: "due_date",
    estimatedMinutes: "estimated_minutes",
    actualMinutes: "actual_minutes",
    semanticSummary: "semantic_summary",
    targetNodeId: "target_node_id",
    worktreePath: "worktree_path",
    worktreeBranch: "worktree_branch",
    worktreeStatus: "worktree_status",
    comparisonSessionId: "comparison_session_id",
    sessionId: "session_id",
    branchFromTaskId: "branch_from_task_id",
    branchFromEventId: "branch_from_event_id",
    branchLabel: "branch_label",
    resumeStrategy: "resume_strategy",
    source: "source",
  };

  constructor(private db: Database.Database) {}

  private static readonly SIDEBAR_ACTIVE_STATUSES = new Set([
    "executing",
    "planning",
    "interrupted",
    "paused",
    "blocked",
  ]);

  private static buildSidebarCursorPredicate(cursor?: {
    id?: string;
    pinned?: boolean;
    status?: string;
    updatedAt?: number;
    createdAt?: number;
  }): { sql: string; args: Any[] } {
    if (!cursor?.id) return { sql: "", args: [] };
    const pinnedRank = cursor.pinned ? 0 : 1;
    const activeRank = TaskRepository.SIDEBAR_ACTIVE_STATUSES.has(String(cursor.status || ""))
      ? 0
      : 1;
    const updatedAt =
      typeof cursor.updatedAt === "number" && Number.isFinite(cursor.updatedAt)
        ? Math.floor(cursor.updatedAt)
        : typeof cursor.createdAt === "number" && Number.isFinite(cursor.createdAt)
          ? Math.floor(cursor.createdAt)
          : 0;
    const createdAt =
      typeof cursor.createdAt === "number" && Number.isFinite(cursor.createdAt)
        ? Math.floor(cursor.createdAt)
        : 0;
    return {
      sql: `
        (
          CASE WHEN COALESCE(is_pinned, 0) = 1 THEN 0 ELSE 1 END > ?
          OR (
            CASE WHEN COALESCE(is_pinned, 0) = 1 THEN 0 ELSE 1 END = ?
            AND CASE WHEN status IN ('executing', 'planning', 'interrupted', 'paused', 'blocked') THEN 0 ELSE 1 END > ?
          )
          OR (
            CASE WHEN COALESCE(is_pinned, 0) = 1 THEN 0 ELSE 1 END = ?
            AND CASE WHEN status IN ('executing', 'planning', 'interrupted', 'paused', 'blocked') THEN 0 ELSE 1 END = ?
            AND COALESCE(updated_at, created_at) < ?
          )
          OR (
            CASE WHEN COALESCE(is_pinned, 0) = 1 THEN 0 ELSE 1 END = ?
            AND CASE WHEN status IN ('executing', 'planning', 'interrupted', 'paused', 'blocked') THEN 0 ELSE 1 END = ?
            AND COALESCE(updated_at, created_at) = ?
            AND created_at < ?
          )
          OR (
            CASE WHEN COALESCE(is_pinned, 0) = 1 THEN 0 ELSE 1 END = ?
            AND CASE WHEN status IN ('executing', 'planning', 'interrupted', 'paused', 'blocked') THEN 0 ELSE 1 END = ?
            AND COALESCE(updated_at, created_at) = ?
            AND created_at = ?
            AND id < ?
          )
        )
      `,
      args: [
        pinnedRank,
        pinnedRank,
        activeRank,
        pinnedRank,
        activeRank,
        updatedAt,
        pinnedRank,
        activeRank,
        updatedAt,
        createdAt,
        pinnedRank,
        activeRank,
        updatedAt,
        createdAt,
        cursor.id,
      ],
    };
  }

  private static normalizePromptFields(
    task: Omit<Task, "id" | "createdAt" | "updatedAt">,
  ): Omit<Task, "id" | "createdAt" | "updatedAt"> {
    const prompt = String(task.prompt || "");
    const rawPrompt = String(task.rawPrompt || "").trim() || prompt;
    const userPrompt = String(task.userPrompt || "").trim() || rawPrompt;
    return {
      ...task,
      prompt,
      rawPrompt,
      userPrompt,
    };
  }

  create(task: Omit<Task, "id" | "createdAt" | "updatedAt">): Task {
    const normalizedTask = TaskRepository.normalizePromptFields(task);
    const newTask: Task = {
      ...normalizedTask,
      id: uuidv4(),
      createdAt: Date.now(),
      updatedAt: Date.now(),
    };

    const stmt = this.db.prepare(`
      INSERT INTO tasks (id, title, prompt, raw_prompt, user_prompt, status, workspace_id, created_at, updated_at, budget_tokens, budget_cost, success_criteria, max_attempts, current_attempt, parent_task_id, agent_type, agent_config, depth, result_summary, source, strategy_lock, budget_profile, terminal_status, failure_class, verification_verdict, verification_report, best_known_outcome, budget_usage, continuation_count, continuation_window, lifetime_turns_used, last_progress_score, auto_continue_block_reason, compaction_count, last_compaction_at, last_compaction_tokens_before, last_compaction_tokens_after, no_progress_streak, last_loop_fingerprint, risk_level, eval_case_id, eval_run_id, issue_id, heartbeat_run_id, company_id, goal_id, project_id, request_depth, billing_code, assigned_agent_role_id, worker_role, semantic_summary)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `);

    stmt.run(
      newTask.id,
      newTask.title,
      newTask.prompt,
      newTask.rawPrompt || null,
      newTask.userPrompt || null,
      newTask.status,
      newTask.workspaceId,
      newTask.createdAt,
      newTask.updatedAt,
      newTask.budgetTokens || null,
      newTask.budgetCost || null,
      newTask.successCriteria ? JSON.stringify(newTask.successCriteria) : null,
      newTask.maxAttempts || null,
      newTask.currentAttempt || 1,
      newTask.parentTaskId || null,
      newTask.agentType || "main",
      newTask.agentConfig ? JSON.stringify(newTask.agentConfig) : null,
      newTask.depth ?? 0,
      newTask.resultSummary || null,
      newTask.source || "manual",
      newTask.strategyLock ? 1 : 0,
      newTask.budgetProfile || null,
      newTask.terminalStatus || null,
      newTask.failureClass || null,
      newTask.verificationVerdict || null,
      newTask.verificationReport || null,
      newTask.bestKnownOutcome ? JSON.stringify(newTask.bestKnownOutcome) : null,
      newTask.budgetUsage ? JSON.stringify(newTask.budgetUsage) : null,
      newTask.continuationCount ?? 0,
      newTask.continuationWindow ?? 1,
      newTask.lifetimeTurnsUsed ?? 0,
      typeof newTask.lastProgressScore === "number" ? newTask.lastProgressScore : null,
      newTask.autoContinueBlockReason || null,
      newTask.compactionCount ?? 0,
      typeof newTask.lastCompactionAt === "number" ? newTask.lastCompactionAt : null,
      typeof newTask.lastCompactionTokensBefore === "number"
        ? Math.floor(newTask.lastCompactionTokensBefore)
        : null,
      typeof newTask.lastCompactionTokensAfter === "number"
        ? Math.floor(newTask.lastCompactionTokensAfter)
        : null,
      newTask.noProgressStreak ?? 0,
      newTask.lastLoopFingerprint || null,
      newTask.riskLevel || null,
      newTask.evalCaseId || null,
      newTask.evalRunId || null,
      newTask.issueId || null,
      newTask.heartbeatRunId || null,
      newTask.companyId || null,
      newTask.goalId || null,
      newTask.projectId || null,
      newTask.requestDepth ?? null,
      newTask.billingCode || null,
      newTask.assignedAgentRoleId || null,
      newTask.workerRole || null,
      newTask.semanticSummary || null,
    );

    UsageInsightsProjector.getIfInitialized()?.enqueueTaskCreate(newTask);

    return newTask;
  }

  // Whitelist of allowed update fields to prevent SQL injection
  private static readonly ALLOWED_UPDATE_FIELDS = new Set([
    "title",
    "status",
    "error",
    "result",
    "budgetTokens",
    "budgetCost",
    "successCriteria",
    "maxAttempts",
    "currentAttempt",
    "completedAt",
    "lastRunDurationMs",
    "workspaceId",
    "parentTaskId",
    "agentType",
    "agentConfig",
    "depth",
    "resultSummary",
    // Agent Squad fields
    "assignedAgentRoleId",
    "workerRole",
    "boardColumn",
    "priority",
    // Task Board fields
    "labels",
    "dueDate",
    "estimatedMinutes",
    "actualMinutes",
    "mentionedAgentRoleIds",
    "userPrompt",
    "pinned",
    "rawPrompt",
    "strategyLock",
    "budgetProfile",
    "terminalStatus",
    "failureClass",
    "verificationVerdict",
    "verificationReport",
    "bestKnownOutcome",
    "budgetUsage",
    "continuationCount",
    "continuationWindow",
    "lifetimeTurnsUsed",
    "lastProgressScore",
    "autoContinueBlockReason",
    "awaitingUserInputReasonCode",
    "compactionCount",
    "lastCompactionAt",
    "lastCompactionTokensBefore",
    "lastCompactionTokensAfter",
    "noProgressStreak",
    "lastLoopFingerprint",
    "riskLevel",
    "evalCaseId",
    "evalRunId",
    // Control plane linkage fields
    "issueId",
    "heartbeatRunId",
    "companyId",
    "goalId",
    "projectId",
    "requestDepth",
    "billingCode",
    "semanticSummary",
    "targetNodeId",
    // Git Worktree fields
    "worktreePath",
    "worktreeBranch",
    "worktreeStatus",
    "comparisonSessionId",
    "sessionId",
    "branchFromTaskId",
    "branchFromEventId",
    "branchLabel",
    "resumeStrategy",
    "source",
  ]);

  update(id: string, updates: Partial<Task>): void {
    const before = this.findById(id);
    const normalizedUpdates: Partial<Task> = { ...updates };
    if (isActiveTaskStatus(normalizedUpdates.status)) {
      if (!Object.prototype.hasOwnProperty.call(normalizedUpdates, "completedAt")) {
        normalizedUpdates.completedAt = undefined;
      }
      if (!Object.prototype.hasOwnProperty.call(normalizedUpdates, "terminalStatus")) {
        normalizedUpdates.terminalStatus = undefined;
      }
      if (!Object.prototype.hasOwnProperty.call(normalizedUpdates, "failureClass")) {
        normalizedUpdates.failureClass = undefined;
      }
      if (!Object.prototype.hasOwnProperty.call(normalizedUpdates, "lastRunDurationMs")) {
        normalizedUpdates.lastRunDurationMs = undefined;
      }
    }

    const fields: string[] = [];
    const values: Any[] = [];

    Object.entries(normalizedUpdates).forEach(([key, value]) => {
      // Validate field name against whitelist
      if (!TaskRepository.ALLOWED_UPDATE_FIELDS.has(key)) {
        taskRepositoryLogger.warn(`Ignoring unknown field in task update: ${key}`);
        return;
      }
      const dbKey = TaskRepository.UPDATE_FIELD_TO_COLUMN[key as keyof Task];
      if (!dbKey) {
        taskRepositoryLogger.warn(`No database column mapping found for task field: ${key}`);
        return;
      }
      fields.push(`${dbKey} = ?`);

      // JSON serialize object/array fields
      if (
        (key === "successCriteria" ||
          key === "agentConfig" ||
          key === "labels" ||
          key === "mentionedAgentRoleIds" ||
          key === "bestKnownOutcome" ||
          key === "budgetUsage") &&
        value != null
      ) {
        values.push(JSON.stringify(value));
      } else if (key === "pinned") {
        values.push(Number(Boolean(value)));
      } else if (key === "strategyLock") {
        values.push(Number(Boolean(value)));
      } else {
        values.push(value);
      }
    });

    if (fields.length === 0) {
      return; // No valid fields to update
    }

    fields.push("updated_at = ?");
    values.push(Date.now());
    values.push(id);

    const stmt = this.db.prepare(`UPDATE tasks SET ${fields.join(", ")} WHERE id = ?`);
    stmt.run(...values);
    const after = this.findById(id);
    UsageInsightsProjector.getIfInitialized()?.enqueueTaskUpdate(before, after);
  }

  togglePin(id: string): Task | undefined {
    const result = this.db
      .prepare(`
      UPDATE tasks
      SET is_pinned = CASE
          WHEN CAST(is_pinned AS INTEGER) = 1 THEN 0
          ELSE 1
        END,
        updated_at = ?
      WHERE id = ?
    `)
      .run(Date.now(), id);

    if (result.changes === 0) {
      return undefined;
    }

    return this.findById(id);
  }

  touch(id: string, timestamp = Date.now()): Task | undefined {
    const before = this.findById(id);
    const result = this.db
      .prepare("UPDATE tasks SET updated_at = ? WHERE id = ?")
      .run(timestamp, id);

    if (result.changes === 0) {
      return undefined;
    }

    const after = this.findById(id);
    UsageInsightsProjector.getIfInitialized()?.enqueueTaskUpdate(before, after);
    return after;
  }

  findById(id: string): Task | undefined {
    const stmt = this.db.prepare("SELECT * FROM tasks WHERE id = ?");
    const row = stmt.get(id) as Any;
    return row ? this.mapRowToTask(row) : undefined;
  }

  findAll(
    limit = 100,
    offset = 0,
    options?: {
      prioritizeSidebar?: boolean;
      excludeSources?: Array<NonNullable<Task["source"]>>;
      cursor?: {
        id?: string;
        pinned?: boolean;
        status?: string;
        updatedAt?: number;
        createdAt?: number;
      };
    },
  ): Task[] {
    const orderBy = options?.prioritizeSidebar
      ? `
      ORDER BY
        CASE WHEN COALESCE(is_pinned, 0) = 1 THEN 0 ELSE 1 END,
        CASE WHEN status IN ('executing', 'planning', 'interrupted', 'paused', 'blocked') THEN 0 ELSE 1 END,
        COALESCE(updated_at, created_at) DESC,
        created_at DESC,
        id DESC
      `
      : "ORDER BY COALESCE(updated_at, created_at) DESC, created_at DESC, id DESC";
    const excludedSources = Array.isArray(options?.excludeSources)
      ? options.excludeSources.filter((source): source is NonNullable<Task["source"]> => Boolean(source))
      : [];
    const cursor = options?.prioritizeSidebar
      ? TaskRepository.buildSidebarCursorPredicate(options.cursor)
      : { sql: "", args: [] };
    const whereClauses = [
      ...(excludedSources.length > 0
        ? [`COALESCE(source, 'manual') NOT IN (${excludedSources.map(() => "?").join(", ")})`]
        : []),
      ...(cursor.sql ? [cursor.sql] : []),
    ];
    const where = whereClauses.length > 0 ? `WHERE ${whereClauses.join(" AND ")}` : "";
    const stmt = this.db.prepare(`
      SELECT * FROM tasks
      ${where}
      ${orderBy}
      LIMIT ? OFFSET ?
    `);
    const rows = stmt.all(...excludedSources, ...cursor.args, limit, offset) as Any[];
    return rows.map((row) => this.mapRowToTask(row));
  }

  findSidebarSummaries(
    limit = 100,
    offset = 0,
    options?: {
      prioritizeSidebar?: boolean;
      excludeSources?: Array<NonNullable<Task["source"]>>;
      cursor?: {
        id?: string;
        pinned?: boolean;
        status?: string;
        updatedAt?: number;
        createdAt?: number;
      };
    },
  ): Task[] {
    const orderBy = options?.prioritizeSidebar
      ? `
      ORDER BY
        CASE WHEN COALESCE(is_pinned, 0) = 1 THEN 0 ELSE 1 END,
        CASE WHEN status IN ('executing', 'planning', 'interrupted', 'paused', 'blocked') THEN 0 ELSE 1 END,
        COALESCE(updated_at, created_at) DESC,
        created_at DESC,
        id DESC
      `
      : "ORDER BY COALESCE(updated_at, created_at) DESC, created_at DESC, id DESC";
    const excludedSources = Array.isArray(options?.excludeSources)
      ? options.excludeSources.filter((source): source is NonNullable<Task["source"]> => Boolean(source))
      : [];
    const cursor = options?.prioritizeSidebar
      ? TaskRepository.buildSidebarCursorPredicate(options.cursor)
      : { sql: "", args: [] };
    const whereClauses = [
      ...(excludedSources.length > 0
        ? [`COALESCE(source, 'manual') NOT IN (${excludedSources.map(() => "?").join(", ")})`]
        : []),
      ...(cursor.sql ? [cursor.sql] : []),
    ];
    const where = whereClauses.length > 0 ? `WHERE ${whereClauses.join(" AND ")}` : "";
    const stmt = this.db.prepare(`
      SELECT
        id,
        title,
        status,
        workspace_id,
        created_at,
        updated_at,
        completed_at,
        last_run_duration_ms,
        is_pinned,
        parent_task_id,
        agent_type,
        assigned_agent_role_id,
        worker_role,
        board_column,
        priority,
        comparison_session_id,
        session_id,
        branch_from_task_id,
        branch_from_event_id,
        branch_label,
        resume_strategy,
        source,
        strategy_lock,
        budget_profile,
        terminal_status,
        failure_class,
        verification_verdict,
        continuation_count,
        awaiting_user_input_reason_code,
        worktree_path,
        target_node_id,
        company_id,
        goal_id,
        project_id,
        issue_id,
        heartbeat_run_id,
        request_depth,
        billing_code,
        SUBSTR(
          COALESCE(NULLIF(user_prompt, ''), NULLIF(raw_prompt, ''), NULLIF(prompt, ''), ''),
          1,
          1024
        ) AS sidebar_prompt_preview,
        SUBSTR(COALESCE(result_summary, ''), 1, 512) AS result_summary,
        SUBSTR(COALESCE(semantic_summary, ''), 1, 512) AS semantic_summary,
        CASE
          WHEN agent_config IS NOT NULL AND json_valid(agent_config)
          THEN json_extract(agent_config, '$.videoGenerationMode')
          ELSE NULL
        END AS agent_config_video_generation_mode,
        CASE
          WHEN agent_config IS NOT NULL AND json_valid(agent_config)
          THEN json_extract(agent_config, '$.taskDomain')
          ELSE NULL
        END AS agent_config_task_domain,
        CASE
          WHEN agent_config IS NOT NULL AND json_valid(agent_config)
          THEN json_extract(agent_config, '$.multitaskMode')
          ELSE NULL
        END AS agent_config_multitask_mode,
        CASE
          WHEN agent_config IS NOT NULL AND json_valid(agent_config)
          THEN json_extract(agent_config, '$.collaborativeMode')
          ELSE NULL
        END AS agent_config_collaborative_mode,
        CASE
          WHEN agent_config IS NOT NULL AND json_valid(agent_config)
          THEN json_extract(agent_config, '$.multiLlmMode')
          ELSE NULL
        END AS agent_config_multi_llm_mode,
        CASE
          WHEN agent_config IS NOT NULL AND json_valid(agent_config)
          THEN json_extract(agent_config, '$.autonomousMode')
          ELSE NULL
        END AS agent_config_autonomous_mode,
        CASE
          WHEN agent_config IS NOT NULL AND json_valid(agent_config)
          THEN json_extract(agent_config, '$.conversationMode')
          ELSE NULL
        END AS agent_config_conversation_mode,
        CASE
          WHEN agent_config IS NOT NULL AND json_valid(agent_config)
          THEN json_extract(agent_config, '$.executionMode')
          ELSE NULL
        END AS agent_config_execution_mode,
        CASE
          WHEN agent_config IS NOT NULL AND json_valid(agent_config)
          THEN json_extract(agent_config, '$.executionModeSource')
          ELSE NULL
        END AS agent_config_execution_mode_source
      FROM tasks
      ${where}
      ${orderBy}
      LIMIT ? OFFSET ?
    `);
    const rows = stmt.all(...excludedSources, ...cursor.args, limit, offset) as Any[];
    return rows.map((row) => this.mapRowToSidebarTask(row));
  }

  /**
   * Find tasks by status (single status or array of statuses)
   */
  findByStatus(status: string | string[]): Task[] {
    const statuses = Array.isArray(status) ? status : [status];
    const placeholders = statuses.map(() => "?").join(", ");
    const stmt = this.db.prepare(`
      SELECT * FROM tasks
      WHERE status IN (${placeholders})
      ORDER BY created_at ASC
    `);
    const rows = stmt.all(...statuses) as Any[];
    return rows.map((row) => this.mapRowToTask(row));
  }

  /**
   * Find tasks by workspace ID
   */
  findByWorkspace(workspaceId: string, limit?: number, offset?: number): Task[] {
    if (typeof limit === "number" && Number.isFinite(limit)) {
      const safeLimit = Math.max(1, Math.floor(limit));
      const safeOffset =
        typeof offset === "number" && Number.isFinite(offset) ? Math.max(0, Math.floor(offset)) : 0;
      const stmt = this.db.prepare(`
        SELECT * FROM tasks
        WHERE workspace_id = ?
        ORDER BY created_at DESC
        LIMIT ? OFFSET ?
      `);
      const rows = stmt.all(workspaceId, safeLimit, safeOffset) as Any[];
      return rows.map((row) => this.mapRowToTask(row));
    }

    const stmt = this.db.prepare(`
      SELECT * FROM tasks
      WHERE workspace_id = ?
      ORDER BY created_at DESC
    `);
    const rows = stmt.all(workspaceId) as Any[];
    return rows.map((row) => this.mapRowToTask(row));
  }

  findBySessionId(sessionId: string, limit?: number, offset?: number): Task[] {
    const normalizedSessionId = String(sessionId || "").trim();
    if (!normalizedSessionId) return [];

    if (typeof limit === "number" && Number.isFinite(limit)) {
      const safeLimit = Math.max(1, Math.floor(limit));
      const safeOffset =
        typeof offset === "number" && Number.isFinite(offset) ? Math.max(0, Math.floor(offset)) : 0;
      const rows = this.db.prepare(`
        SELECT * FROM tasks
        WHERE session_id = ?
        ORDER BY created_at ASC
        LIMIT ? OFFSET ?
      `).all(normalizedSessionId, safeLimit, safeOffset) as Any[];
      return rows.map((row) => this.mapRowToTask(row));
    }

    const rows = this.db.prepare(`
      SELECT * FROM tasks
      WHERE session_id = ?
      ORDER BY created_at ASC
    `).all(normalizedSessionId) as Any[];
    return rows.map((row) => this.mapRowToTask(row));
  }

  countByWorkspace(workspaceId: string): number {
    const stmt = this.db.prepare("SELECT COUNT(1) as count FROM tasks WHERE workspace_id = ?");
    const row = stmt.get(workspaceId) as Any;
    const count = row?.count;
    if (typeof count === "number") return count;
    const parsed = Number(count);
    return Number.isFinite(parsed) ? parsed : 0;
  }

  /**
   * Find tasks within a created_at time range (inclusive start, exclusive end).
   * Optionally filter by workspace and a simple substring query over title/prompt.
   */
  findByCreatedAtRange(params: {
    startMs: number;
    endMs: number;
    limit?: number;
    workspaceId?: string;
    query?: string;
  }): Task[] {
    const startMs = Number(params.startMs);
    const endMs = Number(params.endMs);
    if (!Number.isFinite(startMs) || !Number.isFinite(endMs)) return [];
    if (endMs <= startMs) return [];

    const limit =
      typeof params.limit === "number" && Number.isFinite(params.limit)
        ? Math.min(Math.max(Math.floor(params.limit), 1), 200)
        : 50;

    const where: string[] = ["created_at >= ?", "created_at < ?"];
    const args: Any[] = [startMs, endMs];

    const workspaceId = typeof params.workspaceId === "string" ? params.workspaceId.trim() : "";
    if (workspaceId) {
      where.push("workspace_id = ?");
      args.push(workspaceId);
    }

    const query = typeof params.query === "string" ? params.query.trim() : "";
    if (query) {
      // Simple LIKE match (SQLite default collation is case-insensitive for ASCII).
      where.push("(title LIKE ? OR prompt LIKE ?)");
      args.push(`%${query}%`, `%${query}%`);
    }

    args.push(limit);

    const stmt = this.db.prepare(`
      SELECT * FROM tasks
      WHERE ${where.join(" AND ")}
      ORDER BY created_at DESC
      LIMIT ?
    `);

    const rows = stmt.all(...args) as Any[];
    return rows.map((row) => this.mapRowToTask(row));
  }

  delete(id: string): void {
    // Use transaction to ensure atomic deletion
    const deleteTransaction = this.db.transaction((taskId: string) => {
      // Delete related records from all tables with foreign keys to tasks
      const deleteEvents = this.db.prepare("DELETE FROM task_events WHERE task_id = ?");
      deleteEvents.run(taskId);

      const deleteArtifacts = this.db.prepare("DELETE FROM artifacts WHERE task_id = ?");
      deleteArtifacts.run(taskId);

      const deleteApprovals = this.db.prepare("DELETE FROM approvals WHERE task_id = ?");
      deleteApprovals.run(taskId);

      const deleteInputRequests = this.db.prepare("DELETE FROM input_requests WHERE task_id = ?");
      deleteInputRequests.run(taskId);

      // Delete activity feed entries for this task
      const deleteActivities = this.db.prepare("DELETE FROM activity_feed WHERE task_id = ?");
      deleteActivities.run(taskId);

      // Delete agent mentions for this task
      const deleteMentions = this.db.prepare("DELETE FROM agent_mentions WHERE task_id = ?");
      deleteMentions.run(taskId);

      // Delete working state entries for this task
      const deleteWorkingState = this.db.prepare(
        "DELETE FROM agent_working_state WHERE task_id = ?",
      );
      deleteWorkingState.run(taskId);

      // Nullify task_id in memories rather than deleting them
      const clearMemoryTaskId = this.db.prepare(
        "UPDATE memories SET task_id = NULL WHERE task_id = ?",
      );
      clearMemoryTaskId.run(taskId);

      const clearCuratedMemoryTaskId = this.db.prepare(
        "UPDATE curated_memory_entries SET task_id = NULL WHERE task_id = ?",
      );
      clearCuratedMemoryTaskId.run(taskId);

      const clearMemoryObservationTaskId = this.db.prepare(
        "UPDATE memory_observation_metadata SET task_id = NULL WHERE task_id = ?",
      );
      clearMemoryObservationTaskId.run(taskId);

      // Nullify task_id in channel_sessions rather than deleting the session
      const clearSessionTaskId = this.db.prepare(
        "UPDATE channel_sessions SET task_id = NULL WHERE task_id = ?",
      );
      clearSessionTaskId.run(taskId);

      // Delete worktree_info record if it exists
      const deleteWorktreeInfo = this.db.prepare("DELETE FROM worktree_info WHERE task_id = ?");
      deleteWorktreeInfo.run(taskId);

      // Delete hook_sessions (task_id NOT NULL)
      const deleteHookSessions = this.db.prepare("DELETE FROM hook_sessions WHERE task_id = ?");
      deleteHookSessions.run(taskId);

      // Nullify source_task_id in eval_cases
      const clearEvalCaseSource = this.db.prepare(
        "UPDATE eval_cases SET source_task_id = NULL WHERE source_task_id = ?",
      );
      clearEvalCaseSource.run(taskId);

      const clearManagedSessionBackingTask = this.db.prepare(
        "UPDATE managed_sessions SET backing_task_id = NULL WHERE backing_task_id = ?",
      );
      clearManagedSessionBackingTask.run(taskId);

      const clearManagedSessionEventSourceTask = this.db.prepare(
        "UPDATE managed_session_events SET source_task_id = NULL WHERE source_task_id = ?",
      );
      clearManagedSessionEventSourceTask.run(taskId);

      const clearManagedSessionBackingTeamRun = this.db.prepare(`
        UPDATE managed_sessions
        SET backing_team_run_id = NULL
        WHERE backing_team_run_id IN (
          SELECT id FROM agent_team_runs WHERE root_task_id = ?
        )
      `);
      clearManagedSessionBackingTeamRun.run(taskId);

      // Delete agent_team_runs where this task is the root (cascades to items/thoughts)
      const deleteTeamRuns = this.db.prepare(
        "DELETE FROM agent_team_runs WHERE root_task_id = ?",
      );
      deleteTeamRuns.run(taskId);

      // Nullify source_task_id in agent_team_items (for runs we did not delete)
      const clearTeamItemSource = this.db.prepare(
        "UPDATE agent_team_items SET source_task_id = NULL WHERE source_task_id = ?",
      );
      clearTeamItemSource.run(taskId);

      // Nullify source_task_id in agent_team_thoughts
      const clearTeamThoughtSource = this.db.prepare(
        "UPDATE agent_team_thoughts SET source_task_id = NULL WHERE source_task_id = ?",
      );
      clearTeamThoughtSource.run(taskId);

      // Preserve cross-system/task analytics history while dropping the task row.
      const clearIssueTaskId = this.db.prepare("UPDATE issues SET task_id = NULL WHERE task_id = ?");
      clearIssueTaskId.run(taskId);

      const clearHeartbeatRunTaskId = this.db.prepare(
        "UPDATE heartbeat_runs SET task_id = NULL WHERE task_id = ?",
      );
      clearHeartbeatRunTaskId.run(taskId);

      const clearSupervisorExchangeTaskId = this.db.prepare(
        "UPDATE supervisor_exchanges SET linked_task_id = NULL WHERE linked_task_id = ?",
      );
      clearSupervisorExchangeTaskId.run(taskId);

      const clearCouncilRunTaskId = this.db.prepare(
        "UPDATE council_runs SET task_id = NULL WHERE task_id = ?",
      );
      clearCouncilRunTaskId.run(taskId);

      const clearCouncilMemoTaskId = this.db.prepare(
        "UPDATE council_memos SET task_id = NULL WHERE task_id = ?",
      );
      clearCouncilMemoTaskId.run(taskId);

      const clearLlmCallEventTaskId = this.db.prepare(
        "UPDATE llm_call_events SET task_id = NULL WHERE task_id = ?",
      );
      clearLlmCallEventTaskId.run(taskId);

      // Orphan child tasks so we can delete this parent
      const clearChildParent = this.db.prepare(
        "UPDATE tasks SET parent_task_id = NULL WHERE parent_task_id = ?",
      );
      clearChildParent.run(taskId);

      const clearBranchFromTask = this.db.prepare(
        "UPDATE tasks SET branch_from_task_id = NULL WHERE branch_from_task_id = ?",
      );
      clearBranchFromTask.run(taskId);

      // Delete task_subscriptions (ON DELETE CASCADE may not run before FK check in some SQLite configs)
      const deleteSubscriptions = this.db.prepare(
        "DELETE FROM task_subscriptions WHERE task_id = ?",
      );
      deleteSubscriptions.run(taskId);

      this.cleanupTaskForeignKeyReferences(taskId);

      // Finally delete the task
      const deleteTask = this.db.prepare("DELETE FROM tasks WHERE id = ?");
      deleteTask.run(taskId);
    });

    deleteTransaction(id);
  }

  private cleanupTaskForeignKeyReferences(taskId: string): void {
    const tableRows = this.db
      .prepare("SELECT name FROM sqlite_master WHERE type = 'table'")
      .all() as Array<{ name?: string }>;

    for (const tableRow of tableRows) {
      const tableName = String(tableRow.name || "");
      if (!tableName || tableName.startsWith("sqlite_") || tableName === "tasks") {
        continue;
      }
      if (!SAFE_SQL_IDENTIFIER.test(tableName)) {
        taskRepositoryLogger.warn(
          `Skipping task delete FK cleanup for unsafe table name: ${tableName}`,
        );
        continue;
      }

      const tableInfo = this.db
        .prepare(`PRAGMA table_info(${quoteSqlIdentifier(tableName)})`)
        .all() as SqliteTableInfoRow[];
      const columns = new Map(
        tableInfo
          .map(
            (column) =>
              [
                String(column.name || ""),
                Number(column.notnull || 0) || Number(column.pk || 0),
              ] as const,
          )
          .filter(([column]) => SAFE_SQL_IDENTIFIER.test(column)),
      );

      const foreignKeys = this.db
        .prepare(`PRAGMA foreign_key_list(${quoteSqlIdentifier(tableName)})`)
        .all() as SqliteForeignKeyRow[];

      for (const foreignKey of foreignKeys) {
        const referencedTable = String(foreignKey.table || "");
        const referencedColumn = String(foreignKey.to || "id");
        const columnName = String(foreignKey.from || "");
        if (referencedTable !== "tasks" || referencedColumn !== "id") {
          continue;
        }
        if (!SAFE_SQL_IDENTIFIER.test(columnName) || !columns.has(columnName)) {
          taskRepositoryLogger.warn(
            `Skipping task delete FK cleanup for unsafe column ${tableName}.${columnName}`,
          );
          continue;
        }

        const quotedTable = quoteSqlIdentifier(tableName);
        const quotedColumn = quoteSqlIdentifier(columnName);
        const onDelete = String(foreignKey.on_delete || "").toUpperCase();
        const columnIsNullable = columns.get(columnName) === 0;

        if (columnIsNullable && onDelete !== "CASCADE") {
          this.db
            .prepare(`UPDATE ${quotedTable} SET ${quotedColumn} = NULL WHERE ${quotedColumn} = ?`)
            .run(taskId);
        } else {
          this.db.prepare(`DELETE FROM ${quotedTable} WHERE ${quotedColumn} = ?`).run(taskId);
        }
      }
    }

    const quotedTasksTable = quoteSqlIdentifier("tasks");
    for (const columnName of ["parent_task_id", "branch_from_task_id"]) {
      try {
        const quotedColumn = quoteSqlIdentifier(columnName);
        this.db
          .prepare(`UPDATE ${quotedTasksTable} SET ${quotedColumn} = NULL WHERE ${quotedColumn} = ?`)
          .run(taskId);
      } catch {
        // Older databases may not have every self-reference column.
      }
    }
  }

  private mapRowToTask(row: Any): Task {
    return normalizeTaskLifecycleState({
      id: row.id,
      title: row.title,
      prompt: row.prompt,
      rawPrompt: row.raw_prompt || undefined,
      userPrompt: row.user_prompt || undefined,
      status: row.status,
      workspaceId: row.workspace_id,
      createdAt: row.created_at,
      updatedAt: row.updated_at,
      completedAt: row.completed_at || undefined,
      lastRunDurationMs:
        typeof row.last_run_duration_ms === "number" && Number.isFinite(row.last_run_duration_ms)
          ? Math.max(0, Math.floor(row.last_run_duration_ms))
          : undefined,
      pinned: Number(row.is_pinned) === 1,
      budgetTokens: row.budget_tokens || undefined,
      budgetCost: row.budget_cost || undefined,
      error: row.error || undefined,
      // Verification/retry metadata
      successCriteria: row.success_criteria
        ? safeJsonParse(row.success_criteria, undefined, "task.successCriteria")
        : undefined,
      maxAttempts: row.max_attempts || undefined,
      currentAttempt: row.current_attempt || undefined,
      // Sub-Agent / Parallel Agent fields
      parentTaskId: row.parent_task_id || undefined,
      agentType: row.agent_type || undefined,
      agentConfig: row.agent_config
        ? safeJsonParse(row.agent_config, undefined, "task.agentConfig")
        : undefined,
      depth: row.depth ?? undefined,
      resultSummary: row.result_summary || undefined,
      // Agent Squad fields
      assignedAgentRoleId: row.assigned_agent_role_id || undefined,
      workerRole: row.worker_role || undefined,
      boardColumn: row.board_column || undefined,
      priority: row.priority ?? undefined,
      // Task Board fields
      labels: row.labels ? safeJsonParse<string[]>(row.labels, [], "task.labels") : undefined,
      dueDate: row.due_date || undefined,
      estimatedMinutes: row.estimated_minutes || undefined,
      actualMinutes: row.actual_minutes || undefined,
      mentionedAgentRoleIds: row.mentioned_agent_role_ids
        ? safeJsonParse<string[]>(row.mentioned_agent_role_ids, [], "task.mentionedAgentRoleIds")
        : undefined,
      // Git Worktree fields
      worktreePath: row.worktree_path || undefined,
      worktreeBranch: row.worktree_branch || undefined,
      worktreeStatus: (row.worktree_status as Task["worktreeStatus"]) || undefined,
      comparisonSessionId: row.comparison_session_id || undefined,
      sessionId: row.session_id || undefined,
      branchFromTaskId: row.branch_from_task_id || undefined,
      branchFromEventId: row.branch_from_event_id || undefined,
      branchLabel: row.branch_label || undefined,
      resumeStrategy: row.resume_strategy || undefined,
      source: (row.source as Task["source"]) || undefined,
      strategyLock: Number(row.strategy_lock) === 1,
      budgetProfile: row.budget_profile || undefined,
      terminalStatus: row.terminal_status || undefined,
      failureClass: row.failure_class || undefined,
      verificationVerdict: row.verification_verdict || undefined,
      verificationReport: row.verification_report || undefined,
      bestKnownOutcome: row.best_known_outcome
        ? safeJsonParse(row.best_known_outcome, undefined, "task.bestKnownOutcome")
        : undefined,
      continuationCount:
        typeof row.continuation_count === "number" ? row.continuation_count : undefined,
      continuationWindow:
        typeof row.continuation_window === "number" ? row.continuation_window : undefined,
      lifetimeTurnsUsed:
        typeof row.lifetime_turns_used === "number" ? row.lifetime_turns_used : undefined,
      lastProgressScore:
        typeof row.last_progress_score === "number" ? row.last_progress_score : undefined,
      autoContinueBlockReason: row.auto_continue_block_reason || undefined,
      awaitingUserInputReasonCode: row.awaiting_user_input_reason_code || undefined,
      compactionCount: typeof row.compaction_count === "number" ? row.compaction_count : undefined,
      lastCompactionAt:
        typeof row.last_compaction_at === "number" ? row.last_compaction_at : undefined,
      lastCompactionTokensBefore:
        typeof row.last_compaction_tokens_before === "number"
          ? row.last_compaction_tokens_before
          : undefined,
      lastCompactionTokensAfter:
        typeof row.last_compaction_tokens_after === "number"
          ? row.last_compaction_tokens_after
          : undefined,
      noProgressStreak:
        typeof row.no_progress_streak === "number" ? row.no_progress_streak : undefined,
      lastLoopFingerprint: row.last_loop_fingerprint || undefined,
      riskLevel: row.risk_level || undefined,
      evalCaseId: row.eval_case_id || undefined,
      evalRunId: row.eval_run_id || undefined,
      budgetUsage: row.budget_usage
        ? safeJsonParse(row.budget_usage, undefined, "task.budgetUsage")
        : undefined,
      companyId: row.company_id || undefined,
      goalId: row.goal_id || undefined,
      projectId: row.project_id || undefined,
      issueId: row.issue_id || undefined,
      heartbeatRunId: row.heartbeat_run_id || undefined,
      requestDepth:
        typeof row.request_depth === "number" ? row.request_depth : undefined,
      billingCode: row.billing_code || undefined,
      semanticSummary: row.semantic_summary || undefined,
      targetNodeId: row.target_node_id || undefined,
    });
  }

  private mapRowToSidebarTask(row: Any): Task {
    type SidebarAgentConfig = NonNullable<Task["agentConfig"]>;
    const agentConfig: SidebarAgentConfig = {};
    const setBooleanAgentConfig = (
      key: "videoGenerationMode" | "multitaskMode" | "collaborativeMode" | "multiLlmMode" | "autonomousMode",
      value: unknown,
    ): void => {
      if (value === null || value === undefined) return;
      agentConfig[key] = value === true || value === 1 || value === "true";
    };

    setBooleanAgentConfig("videoGenerationMode", row.agent_config_video_generation_mode);
    setBooleanAgentConfig("multitaskMode", row.agent_config_multitask_mode);
    setBooleanAgentConfig("collaborativeMode", row.agent_config_collaborative_mode);
    setBooleanAgentConfig("multiLlmMode", row.agent_config_multi_llm_mode);
    setBooleanAgentConfig("autonomousMode", row.agent_config_autonomous_mode);

    if (typeof row.agent_config_task_domain === "string") {
      agentConfig.taskDomain = row.agent_config_task_domain as SidebarAgentConfig["taskDomain"];
    }
    if (typeof row.agent_config_conversation_mode === "string") {
      agentConfig.conversationMode =
        row.agent_config_conversation_mode as SidebarAgentConfig["conversationMode"];
    }
    if (typeof row.agent_config_execution_mode === "string") {
      agentConfig.executionMode =
        row.agent_config_execution_mode as SidebarAgentConfig["executionMode"];
    }
    if (typeof row.agent_config_execution_mode_source === "string") {
      agentConfig.executionModeSource =
        row.agent_config_execution_mode_source as SidebarAgentConfig["executionModeSource"];
    }

    const hasAgentConfig = Object.keys(agentConfig).length > 0;

    return normalizeTaskLifecycleState({
      id: row.id,
      title: row.title,
      prompt: "",
      sidebarPromptPreview: row.sidebar_prompt_preview || undefined,
      status: row.status,
      workspaceId: row.workspace_id,
      createdAt: row.created_at,
      updatedAt: row.updated_at,
      completedAt: row.completed_at || undefined,
      lastRunDurationMs:
        typeof row.last_run_duration_ms === "number" && Number.isFinite(row.last_run_duration_ms)
          ? Math.max(0, Math.floor(row.last_run_duration_ms))
          : undefined,
      pinned: Number(row.is_pinned) === 1,
      parentTaskId: row.parent_task_id || undefined,
      agentType: row.agent_type || undefined,
      agentConfig: hasAgentConfig ? agentConfig : undefined,
      resultSummary: row.result_summary || undefined,
      assignedAgentRoleId: row.assigned_agent_role_id || undefined,
      workerRole: row.worker_role || undefined,
      boardColumn: row.board_column || undefined,
      priority: row.priority ?? undefined,
      worktreePath: row.worktree_path || undefined,
      comparisonSessionId: row.comparison_session_id || undefined,
      sessionId: row.session_id || undefined,
      branchFromTaskId: row.branch_from_task_id || undefined,
      branchFromEventId: row.branch_from_event_id || undefined,
      branchLabel: row.branch_label || undefined,
      resumeStrategy: row.resume_strategy || undefined,
      source: (row.source as Task["source"]) || undefined,
      strategyLock: Number(row.strategy_lock) === 1,
      budgetProfile: row.budget_profile || undefined,
      terminalStatus: row.terminal_status || undefined,
      failureClass: row.failure_class || undefined,
      verificationVerdict: row.verification_verdict || undefined,
      continuationCount:
        typeof row.continuation_count === "number" ? row.continuation_count : undefined,
      awaitingUserInputReasonCode: row.awaiting_user_input_reason_code || undefined,
      companyId: row.company_id || undefined,
      goalId: row.goal_id || undefined,
      projectId: row.project_id || undefined,
      issueId: row.issue_id || undefined,
      heartbeatRunId: row.heartbeat_run_id || undefined,
      targetNodeId: row.target_node_id || undefined,
      requestDepth:
        typeof row.request_depth === "number" ? row.request_depth : undefined,
      billingCode: row.billing_code || undefined,
      semanticSummary: row.semantic_summary || undefined,
    });
  }

  findByTargetNodeId(nodeId: string, limit = 50): Task[] {
    const stmt = this.db.prepare(`
      SELECT * FROM tasks
      WHERE target_node_id = ?
      ORDER BY updated_at DESC
      LIMIT ?
    `);
    const rows = stmt.all(nodeId, limit) as Any[];
    return rows.map((row) => this.mapRowToTask(row));
  }

  findByTargetNodeIds(nodeIds: string[], limit = 50): Task[] {
    const normalized = Array.from(
      new Set(
        nodeIds
          .map((value) => (typeof value === "string" ? value.trim() : ""))
          .filter((value) => value.length > 0),
      ),
    );
    if (normalized.length === 0) return [];

    const placeholders = normalized.map(() => "?").join(", ");
    const stmt = this.db.prepare(`
      SELECT * FROM tasks
      WHERE target_node_id IN (${placeholders})
      ORDER BY updated_at DESC
      LIMIT ?
    `);
    const rows = stmt.all(...normalized, limit) as Any[];
    return rows.map((row) => this.mapRowToTask(row));
  }

  pruneByTargetNodeIds(nodeIds: string[], keepTaskIds: string[], createdAtGte?: number): number {
    const normalizedNodeIds = Array.from(
      new Set(
        nodeIds
          .map((value) => (typeof value === "string" ? value.trim() : ""))
          .filter((value) => value.length > 0),
      ),
    );
    if (normalizedNodeIds.length === 0) return 0;

    const normalizedKeepTaskIds = Array.from(
      new Set(
        keepTaskIds
          .map((value) => (typeof value === "string" ? value.trim() : ""))
          .filter((value) => value.length > 0),
      ),
    );

    const where: string[] = [
      `target_node_id IN (${normalizedNodeIds.map(() => "?").join(", ")})`,
    ];
    const args: Any[] = [...normalizedNodeIds];

    if (typeof createdAtGte === "number" && Number.isFinite(createdAtGte)) {
      where.push("created_at >= ?");
      args.push(createdAtGte);
    }

    if (normalizedKeepTaskIds.length > 0) {
      where.push(`id NOT IN (${normalizedKeepTaskIds.map(() => "?").join(", ")})`);
      args.push(...normalizedKeepTaskIds);
    }

    const rows = this.db
      .prepare(`SELECT id FROM tasks WHERE ${where.join(" AND ")}`)
      .all(...args) as Array<{ id?: string }>;

    for (const row of rows) {
      if (typeof row?.id === "string" && row.id.trim()) {
        this.delete(row.id);
      }
    }

    return rows.length;
  }

  /**
   * Find tasks by parent task ID
   */
  findByParent(parentTaskId: string): Task[] {
    const stmt = this.db.prepare(`
      SELECT * FROM tasks
      WHERE parent_task_id = ?
      ORDER BY created_at ASC
    `);
    const rows = stmt.all(parentTaskId) as Any[];
    return rows.map((row) => this.mapRowToTask(row));
  }

  // ============ Task Board Methods ============

  /**
   * Find tasks by workspace and board column
   */
  findByBoardColumn(workspaceId: string, boardColumn: string): Task[] {
    const stmt = this.db.prepare(`
      SELECT * FROM tasks
      WHERE workspace_id = ? AND board_column = ?
      ORDER BY priority DESC, created_at ASC
    `);
    const rows = stmt.all(workspaceId, boardColumn) as Any[];
    return rows.map((row) => this.mapRowToTask(row));
  }

  /**
   * Get tasks grouped by board column for a workspace
   */
  getTaskBoard(workspaceId: string): Record<string, Task[]> {
    const stmt = this.db.prepare(`
      SELECT * FROM tasks
      WHERE workspace_id = ? AND parent_task_id IS NULL
      ORDER BY board_column, priority DESC, created_at ASC
    `);
    const rows = stmt.all(workspaceId) as Any[];
    const tasks = rows.map((row) => this.mapRowToTask(row));

    // Group tasks by board column
    const board: Record<string, Task[]> = {
      backlog: [],
      todo: [],
      in_progress: [],
      review: [],
      done: [],
    };

    for (const task of tasks) {
      const column = task.boardColumn || "backlog";
      if (board[column]) {
        board[column].push(task);
      } else {
        board.backlog.push(task);
      }
    }

    return board;
  }

  /**
   * Move a task to a different board column
   */
  moveToColumn(id: string, boardColumn: string): Task | undefined {
    this.update(id, { boardColumn: boardColumn as Any });
    return this.findById(id);
  }

  /**
   * Set task priority
   */
  setPriority(id: string, priority: number): Task | undefined {
    this.update(id, { priority });
    return this.findById(id);
  }

  /**
   * Set task due date
   */
  setDueDate(id: string, dueDate: number | null): Task | undefined {
    this.update(id, { dueDate: dueDate || undefined } as Any);
    return this.findById(id);
  }

  /**
   * Set task time estimate
   */
  setEstimate(id: string, estimatedMinutes: number | null): Task | undefined {
    this.update(id, { estimatedMinutes: estimatedMinutes || undefined } as Any);
    return this.findById(id);
  }

  /**
   * Add a label to a task
   */
  addLabel(id: string, labelId: string): Task | undefined {
    const task = this.findById(id);
    if (!task) return undefined;

    const labels = task.labels || [];
    if (!labels.includes(labelId)) {
      labels.push(labelId);
      this.update(id, { labels } as Any);
    }
    return this.findById(id);
  }

  /**
   * Remove a label from a task
   */
  removeLabel(id: string, labelId: string): Task | undefined {
    const task = this.findById(id);
    if (!task) return undefined;

    const labels = task.labels || [];
    const newLabels = labels.filter((l) => l !== labelId);
    this.update(id, { labels: newLabels } as Any);
    return this.findById(id);
  }

  /**
   * Assign an agent role to a task
   */
  assignAgentRole(id: string, agentRoleId: string | null): Task | undefined {
    this.update(id, { assignedAgentRoleId: agentRoleId || undefined } as Any);
    return this.findById(id);
  }
}

export class TaskEventRepository {
  private static readonly RENDERER_NOISE_EVENT_TYPES = [
    "log",
    "llm_usage",
    "llm_streaming",
    "progress_update",
    "task_analysis",
    "executing",
  ] as const;
  private static readonly DEFAULT_TIMELINE_PAGE_LIMIT = 160;
  private static readonly MAX_TIMELINE_PAGE_LIMIT = 600;
  private static readonly DEFAULT_TIMELINE_PAGE_BYTE_LIMIT = 512 * 1024;
  private static readonly MAX_TIMELINE_PAGE_BYTE_LIMIT = 2 * 1024 * 1024;
  private static readonly DEFAULT_TIMELINE_SINGLE_EVENT_BYTE_LIMIT = 64 * 1024;
  private static readonly MAX_TIMELINE_SINGLE_EVENT_BYTE_LIMIT = 256 * 1024;
  private static readonly TRUNCATED_PAYLOAD_PREVIEW_CHARS = 4096;
  private static readonly TIMELINE_ADDITIONAL_TASK_ID_CHUNK_SIZE = 500;

  constructor(private db: Database.Database) {}

  private static normalizePositiveInteger(
    value: unknown,
    fallback: number,
    min: number,
    max: number,
  ): number {
    const numeric = typeof value === "number" && Number.isFinite(value) ? Math.floor(value) : fallback;
    return Math.min(max, Math.max(min, numeric));
  }

  create(event: Omit<TaskEvent, "id"> & { id?: string }): TaskEvent {
    const newEvent: TaskEvent = {
      ...event,
      id: event.id || uuidv4(),
      schemaVersion: 2,
      eventId:
        typeof event.eventId === "string" && event.eventId.trim().length > 0
          ? event.eventId.trim()
          : event.id || "",
      ts: typeof event.ts === "number" && Number.isFinite(event.ts) ? event.ts : event.timestamp,
      seq:
        typeof event.seq === "number" && Number.isFinite(event.seq) && event.seq > 0
          ? Math.floor(event.seq)
          : undefined,
    };
    if (!newEvent.eventId) {
      newEvent.eventId = newEvent.id;
    }

    const storedEvent = sanitizeTimelineEventForStorage(newEvent);

    const stmt = this.db.prepare(`
      INSERT INTO task_events (
        id,
        task_id,
        timestamp,
        type,
        payload,
        schema_version,
        event_id,
        seq,
        ts,
        status,
        step_id,
        group_id,
        actor,
        legacy_type
      )
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `);

    stmt.run(
      storedEvent.id,
      storedEvent.taskId,
      storedEvent.timestamp,
      storedEvent.type,
      JSON.stringify(storedEvent.payload),
      2,
      storedEvent.eventId || storedEvent.id,
      typeof storedEvent.seq === "number" ? storedEvent.seq : null,
      typeof storedEvent.ts === "number" ? storedEvent.ts : storedEvent.timestamp,
      typeof storedEvent.status === "string" ? storedEvent.status : null,
      typeof storedEvent.stepId === "string" ? storedEvent.stepId : null,
      typeof storedEvent.groupId === "string" ? storedEvent.groupId : null,
      typeof storedEvent.actor === "string" ? storedEvent.actor : null,
      typeof storedEvent.legacyType === "string" ? storedEvent.legacyType : null,
    );

    const effectiveType = String(
      (typeof storedEvent.legacyType === "string" && storedEvent.legacyType) ||
        storedEvent.type ||
        "",
    );
    if (
      effectiveType === "skill_used" ||
      effectiveType === "tool_call" ||
      effectiveType === "tool_result" ||
      effectiveType === "tool_error" ||
      effectiveType === "tool_blocked" ||
      effectiveType === "tool_warning" ||
      effectiveType === "user_feedback"
    ) {
      try {
        const row = this.db
          .prepare("SELECT workspace_id FROM tasks WHERE id = ?")
          .get(storedEvent.taskId) as { workspace_id: string } | undefined;
        UsageInsightsProjector.getIfInitialized()?.enqueueTaskEvent(row?.workspace_id, storedEvent);
      } catch {
        // Best-effort cache invalidation only.
      }
    }

    try {
      enqueueTaskEventTelemetry(storedEvent);
    } catch {
      // Best-effort telemetry only.
    }

    return storedEvent;
  }

  findByTaskId(taskId: string): TaskEvent[] {
    const stmt = this.db.prepare(`
      SELECT * FROM task_events
      WHERE task_id = ?
      ORDER BY COALESCE(seq, timestamp) ASC, timestamp ASC
    `);
    const rows = stmt.all(taskId) as Any[];
    return this.mapRowsToEvents(rows).events;
  }

  findRecentByTaskId(taskId: string, maxEvents: number): TaskEvent[] {
    const safeLimit =
      typeof maxEvents === "number" && Number.isFinite(maxEvents) && maxEvents > 0
        ? Math.floor(maxEvents)
        : 0;
    if (!taskId || safeLimit <= 0) return [];

    const noiseTypes = TaskEventRepository.RENDERER_NOISE_EVENT_TYPES;
    const noisePlaceholders = noiseTypes.map(() => "?").join(", ");

    const structuralRowsStmt = this.db.prepare(`
      SELECT * FROM task_events
      WHERE task_id = ?
        AND COALESCE(legacy_type, type) NOT IN (${noisePlaceholders})
      ORDER BY COALESCE(seq, timestamp) DESC, timestamp DESC
      LIMIT ?
    `);

    const structuralRows = structuralRowsStmt.all(taskId, ...noiseTypes, safeLimit) as Any[];
    let rows = structuralRows;

    if (structuralRows.length < safeLimit) {
      const noiseBudget = safeLimit - structuralRows.length;
      const noiseRowsStmt = this.db.prepare(`
        SELECT * FROM task_events
        WHERE task_id = ?
          AND COALESCE(legacy_type, type) IN (${noisePlaceholders})
        ORDER BY COALESCE(seq, timestamp) DESC, timestamp DESC
        LIMIT ?
      `);
      const noiseRows = noiseRowsStmt.all(taskId, ...noiseTypes, noiseBudget) as Any[];
      rows = [...structuralRows, ...noiseRows];
    }

    rows.sort((a, b) => {
      const aOrder =
        typeof a.seq === "number" && Number.isFinite(a.seq) ? a.seq : Number(a.timestamp) || 0;
      const bOrder =
        typeof b.seq === "number" && Number.isFinite(b.seq) ? b.seq : Number(b.timestamp) || 0;
      if (aOrder !== bOrder) return aOrder - bOrder;
      return (Number(a.timestamp) || 0) - (Number(b.timestamp) || 0);
    });

    return this.mapRowsToEvents(rows).events;
  }

  findTimelinePage(request: TaskTimelinePageRequest): TaskTimelinePageResult {
    const taskId = typeof request.taskId === "string" ? request.taskId.trim() : "";
    if (!taskId) {
      return {
        taskId: "",
        events: [],
        hasMoreHistory: false,
        nextCursor: null,
        summary: {
          eventCount: 0,
          payloadBytes: 0,
          truncatedEventCount: 0,
          largestEventPayloadBytes: 0,
        },
      };
    }

    const safeLimit = TaskEventRepository.normalizePositiveInteger(
      request.limit,
      TaskEventRepository.DEFAULT_TIMELINE_PAGE_LIMIT,
      1,
      TaskEventRepository.MAX_TIMELINE_PAGE_LIMIT,
    );
    const byteLimit = TaskEventRepository.normalizePositiveInteger(
      request.byteLimit,
      TaskEventRepository.DEFAULT_TIMELINE_PAGE_BYTE_LIMIT,
      32 * 1024,
      TaskEventRepository.MAX_TIMELINE_PAGE_BYTE_LIMIT,
    );
    const singleEventByteLimit = TaskEventRepository.normalizePositiveInteger(
      request.singleEventByteLimit,
      TaskEventRepository.DEFAULT_TIMELINE_SINGLE_EVENT_BYTE_LIMIT,
      8 * 1024,
      TaskEventRepository.MAX_TIMELINE_SINGLE_EVENT_BYTE_LIMIT,
    );
    const additionalTaskIds = Array.from(
      new Set(
        (Array.isArray(request.additionalTaskIds) ? request.additionalTaskIds : [])
          .map((value) => (typeof value === "string" ? value.trim() : ""))
          .filter((value) => value.length > 0 && value !== taskId),
      ),
    );
    const additionalTaskEventTypes = Array.from(
      new Set(
        (Array.isArray(request.additionalTaskEventTypes)
          ? request.additionalTaskEventTypes
          : []
        )
          .map((value) => (typeof value === "string" ? value.trim() : ""))
          .filter((value) => value.length > 0),
      ),
    );

    const cursor = request.cursor;
    const cursorOrder =
      cursor && typeof cursor.order === "number" && Number.isFinite(cursor.order)
        ? Math.floor(cursor.order)
        : null;
    const cursorTimestamp =
      cursor && typeof cursor.timestamp === "number" && Number.isFinite(cursor.timestamp)
        ? Math.floor(cursor.timestamp)
        : null;
    const cursorId =
      cursor && typeof cursor.id === "string" && cursor.id.trim().length > 0
        ? cursor.id.trim()
        : null;
    const cursorWhere =
      cursorOrder !== null && cursorTimestamp !== null && cursorId
        ? "AND (COALESCE(seq, timestamp) < ? OR (COALESCE(seq, timestamp) = ? AND (timestamp < ? OR (timestamp = ? AND id < ?))))"
        : cursorOrder !== null && cursorTimestamp !== null
          ? "AND (COALESCE(seq, timestamp) < ? OR (COALESCE(seq, timestamp) = ? AND timestamp < ?))"
          : "";
    const cursorArgs: Any[] =
      cursorOrder !== null && cursorTimestamp !== null && cursorId
        ? [cursorOrder, cursorOrder, cursorTimestamp, cursorTimestamp, cursorId]
        : cursorOrder !== null && cursorTimestamp !== null
          ? [cursorOrder, cursorOrder, cursorTimestamp]
          : [];
    const selectRows = (scopeWhere: string, scopeArgs: Any[]): Any[] =>
      this.db
        .prepare(`
        SELECT
          *,
          COALESCE(seq, timestamp) AS timeline_order,
          LENGTH(COALESCE(payload, '')) AS payload_bytes
        FROM task_events
        WHERE ${scopeWhere}
          ${cursorWhere}
        ORDER BY COALESCE(seq, timestamp) DESC, timestamp DESC, id DESC
        LIMIT ?
      `)
        .all(...scopeArgs, ...cursorArgs, safeLimit + 1) as Any[];

    let rows: Any[];
    if (additionalTaskIds.length > 0 && additionalTaskEventTypes.length > 0) {
      rows = selectRows("task_id = ?", [taskId]);
      const typePlaceholders = additionalTaskEventTypes.map(() => "?").join(", ");
      for (
        let index = 0;
        index < additionalTaskIds.length;
        index += TaskEventRepository.TIMELINE_ADDITIONAL_TASK_ID_CHUNK_SIZE
      ) {
        const chunk = additionalTaskIds.slice(
          index,
          index + TaskEventRepository.TIMELINE_ADDITIONAL_TASK_ID_CHUNK_SIZE,
        );
        const taskPlaceholders = chunk.map(() => "?").join(", ");
        rows.push(
          ...selectRows(
            `task_id IN (${taskPlaceholders}) AND COALESCE(legacy_type, type) IN (${typePlaceholders})`,
            [...chunk, ...additionalTaskEventTypes],
          ),
        );
      }
      const seen = new Set<string>();
      rows = rows
        .sort((a, b) => {
          const orderDelta =
            (Number(b.timeline_order ?? b.seq ?? b.timestamp) || 0) -
            (Number(a.timeline_order ?? a.seq ?? a.timestamp) || 0);
          if (orderDelta !== 0) return orderDelta;
          const timestampDelta = (Number(b.timestamp) || 0) - (Number(a.timestamp) || 0);
          if (timestampDelta !== 0) return timestampDelta;
          return String(b.id ?? "").localeCompare(String(a.id ?? ""));
        })
        .filter((row) => {
          const id = String(row.id ?? "");
          if (!id) return true;
          if (seen.has(id)) return false;
          seen.add(id);
          return true;
        })
        .slice(0, safeLimit + 1);
    } else {
      rows = selectRows("task_id = ?", [taskId]);
    }

    const selectedRows: Any[] = [];
    let payloadBytes = 0;
    let largestEventPayloadBytes = 0;
    let truncatedEventCount = 0;
    let stoppedByByteLimit = false;

    for (const row of rows.slice(0, safeLimit)) {
      const rowPayloadBytes =
        typeof row.payload_bytes === "number" && Number.isFinite(row.payload_bytes)
          ? row.payload_bytes
          : Buffer.byteLength(String(row.payload ?? ""), "utf8");
      largestEventPayloadBytes = Math.max(largestEventPayloadBytes, rowPayloadBytes);
      const nextPayloadBytes = payloadBytes + Math.min(rowPayloadBytes, singleEventByteLimit);
      if (selectedRows.length > 0 && nextPayloadBytes > byteLimit) {
        stoppedByByteLimit = true;
        break;
      }

      payloadBytes = nextPayloadBytes;
      if (rowPayloadBytes > singleEventByteLimit) {
        truncatedEventCount += 1;
        selectedRows.push(this.buildTimelineTruncatedPayloadRow(row, rowPayloadBytes));
      } else {
        selectedRows.push(row);
      }
    }

    if (selectedRows.length === 0 && rows.length > 0) {
      const row = rows[0];
      const rowPayloadBytes =
        typeof row.payload_bytes === "number" && Number.isFinite(row.payload_bytes)
          ? row.payload_bytes
          : Buffer.byteLength(String(row.payload ?? ""), "utf8");
      largestEventPayloadBytes = Math.max(largestEventPayloadBytes, rowPayloadBytes);
      payloadBytes = Math.min(rowPayloadBytes, singleEventByteLimit);
      truncatedEventCount = rowPayloadBytes > singleEventByteLimit ? 1 : 0;
      selectedRows.push(
        rowPayloadBytes > singleEventByteLimit
          ? this.buildTimelineTruncatedPayloadRow(row, rowPayloadBytes)
          : row,
      );
    }

    const oldestSelected = selectedRows[selectedRows.length - 1];
    const planContextRow =
      cursorWhere.length === 0
        ? this.findLatestTimelineContextRow(taskId, "plan_created", selectedRows)
        : null;
    const selectedRowsForEvents = planContextRow ? [...selectedRows, planContextRow] : selectedRows;
    if (planContextRow) {
      const planPayloadBytes =
        typeof planContextRow.payload_bytes === "number" && Number.isFinite(planContextRow.payload_bytes)
          ? planContextRow.payload_bytes
          : Buffer.byteLength(String(planContextRow.payload ?? ""), "utf8");
      payloadBytes += planPayloadBytes;
      largestEventPayloadBytes = Math.max(largestEventPayloadBytes, planPayloadBytes);
    }

    const selectedRowsAscending = [...selectedRowsForEvents].sort((a, b) => {
      const aOrder = Number(a.timeline_order ?? a.seq ?? a.timestamp) || 0;
      const bOrder = Number(b.timeline_order ?? b.seq ?? b.timestamp) || 0;
      if (aOrder !== bOrder) return aOrder - bOrder;
      const timestampDelta = (Number(a.timestamp) || 0) - (Number(b.timestamp) || 0);
      if (timestampDelta !== 0) return timestampDelta;
      return String(a.id ?? "").localeCompare(String(b.id ?? ""));
    });
    const events = this.mapRowsToEvents(selectedRowsAscending, {
      persistMigrations: false,
    }).events;
    const hasMoreHistory =
      stoppedByByteLimit || rows.length > safeLimit || selectedRows.length < Math.min(rows.length, safeLimit);
    const nextCursor =
      hasMoreHistory && oldestSelected
        ? {
            order: Number(oldestSelected.timeline_order ?? oldestSelected.seq ?? oldestSelected.timestamp) || 0,
            timestamp: Number(oldestSelected.timestamp) || 0,
            id: typeof oldestSelected.id === "string" ? oldestSelected.id : undefined,
          }
        : null;

    return {
      taskId,
      events,
      hasMoreHistory,
      nextCursor,
      summary: {
        eventCount: events.length,
        payloadBytes,
        truncatedEventCount,
        largestEventPayloadBytes,
        ...this.deriveTimelinePageSummary(events),
      },
      warnings: this.buildTimelinePageWarnings(taskId, payloadBytes, largestEventPayloadBytes, truncatedEventCount),
    };
  }

  private findLatestTimelineContextRow(
    taskId: string,
    effectiveType: string,
    selectedRows: Any[],
  ): Any | null {
    if (!taskId || !effectiveType) return null;
    const selectedIds = new Set(
      selectedRows
        .map((row) => (typeof row.id === "string" ? row.id : ""))
        .filter((id) => id.length > 0),
    );
    const row = this.db
      .prepare(`
        SELECT
          *,
          COALESCE(seq, timestamp) AS timeline_order,
          LENGTH(COALESCE(payload, '')) AS payload_bytes
        FROM task_events
        WHERE task_id = ?
          AND COALESCE(legacy_type, type) IN (?)
        ORDER BY COALESCE(seq, timestamp) DESC, timestamp DESC, id DESC
        LIMIT 1
      `)
      .get(taskId, effectiveType) as Any;
    if (!row || selectedIds.has(String(row.id ?? ""))) return null;
    return row;
  }

  findEventDetailById(
    eventId: string,
    scope?: {
      taskId?: string;
      additionalTaskIds?: string[];
      additionalTaskEventTypes?: string[];
    },
  ): TaskEventDetailResult {
    const normalizedEventId = typeof eventId === "string" ? eventId.trim() : "";
    if (!normalizedEventId) return { event: null, payloadBytes: 0 };
    const normalizedTaskId = typeof scope?.taskId === "string" ? scope.taskId.trim() : "";
    const additionalTaskIds = Array.from(
      new Set(
        (Array.isArray(scope?.additionalTaskIds) ? scope.additionalTaskIds : [])
          .map((id) => (typeof id === "string" ? id.trim() : ""))
          .filter((id) => id.length > 0 && id !== normalizedTaskId),
      ),
    );
    const additionalTaskEventTypes = Array.from(
      new Set(
        (Array.isArray(scope?.additionalTaskEventTypes) ? scope.additionalTaskEventTypes : [])
          .map((type) => (typeof type === "string" ? type.trim() : ""))
          .filter(Boolean),
      ),
    );
    const selectScopedRow = (scopeWhere: string, scopeArgs: Any[]): Any =>
      this.db
        .prepare(`
          SELECT *, LENGTH(COALESCE(payload, '')) AS payload_bytes
          FROM task_events
          WHERE (id = ? OR event_id = ?)
            AND ${scopeWhere}
          LIMIT 1
        `)
        .get(normalizedEventId, normalizedEventId, ...scopeArgs) as Any;

    let row: Any;
    if (normalizedTaskId) {
      row = selectScopedRow("task_id = ?", [normalizedTaskId]);
      if (!row && additionalTaskIds.length > 0 && additionalTaskEventTypes.length > 0) {
        const typePlaceholders = additionalTaskEventTypes.map(() => "?").join(", ");
        for (
          let index = 0;
          index < additionalTaskIds.length && !row;
          index += TaskEventRepository.TIMELINE_ADDITIONAL_TASK_ID_CHUNK_SIZE
        ) {
          const chunk = additionalTaskIds.slice(
            index,
            index + TaskEventRepository.TIMELINE_ADDITIONAL_TASK_ID_CHUNK_SIZE,
          );
          const taskPlaceholders = chunk.map(() => "?").join(", ");
          row = selectScopedRow(
            `task_id IN (${taskPlaceholders}) AND COALESCE(legacy_type, type) IN (${typePlaceholders})`,
            [...chunk, ...additionalTaskEventTypes],
          );
        }
      }
    } else {
      row = this.db
        .prepare("SELECT *, LENGTH(COALESCE(payload, '')) AS payload_bytes FROM task_events WHERE id = ? OR event_id = ? LIMIT 1")
        .get(normalizedEventId, normalizedEventId) as Any;
    }
    if (!row) return { event: null, payloadBytes: 0 };
    return {
      event: this.mapRowsToEvents([row]).events[0] || null,
      payloadBytes:
        typeof row.payload_bytes === "number" && Number.isFinite(row.payload_bytes)
          ? row.payload_bytes
          : Buffer.byteLength(String(row.payload ?? ""), "utf8"),
    };
  }

  findByTaskIds(taskIds: string[], types?: string[]): TaskEvent[] {
    if (!Array.isArray(taskIds) || taskIds.length === 0) {
      return [];
    }

    const normalizedTaskIds = taskIds
      .map((id) => (typeof id === "string" ? id.trim() : ""))
      .filter(Boolean);
    if (normalizedTaskIds.length === 0) {
      return [];
    }

    const normalizedTypes = (types || [])
      .map((t) => (typeof t === "string" ? t.trim() : ""))
      .filter(Boolean);

    // Chunk task IDs to stay under SQLite's SQLITE_MAX_VARIABLE_NUMBER (999).
    const CHUNK_SIZE = 500;
    const allRows: Any[] = [];

    for (let i = 0; i < normalizedTaskIds.length; i += CHUNK_SIZE) {
      const chunk = normalizedTaskIds.slice(i, i + CHUNK_SIZE);
      const taskPlaceholders = chunk.map(() => "?").join(", ");
      const args: Any[] = [...chunk];

      let sql = `
        SELECT * FROM task_events
        WHERE task_id IN (${taskPlaceholders})
      `;

      if (normalizedTypes.length > 0) {
        const typePlaceholders = normalizedTypes.map(() => "?").join(", ");
        sql += ` AND (type IN (${typePlaceholders}) OR legacy_type IN (${typePlaceholders}))`;
        args.push(...normalizedTypes, ...normalizedTypes);
      }

      sql += " ORDER BY task_id ASC, COALESCE(seq, timestamp) ASC, timestamp ASC";

      const stmt = this.db.prepare(sql);
      allRows.push(...(stmt.all(...args) as Any[]));
    }

    return this.mapRowsToEvents(allRows).events;
  }

  updatePayloadById(eventId: string, payload: Record<string, unknown>): void {
    const normalizedEventId = typeof eventId === "string" ? eventId.trim() : "";
    if (!normalizedEventId) return;
    const stmt = this.db.prepare(`
      UPDATE task_events
      SET payload = ?
      WHERE id = ?
    `);
    stmt.run(JSON.stringify(sanitizeTimelinePayloadForStorage(payload ?? {})), normalizedEventId);
  }

  private buildTimelineTruncatedPayloadRow(row: Any, payloadBytes: number): Any {
    const preview = String(row.payload ?? "").slice(
      0,
      TaskEventRepository.TRUNCATED_PAYLOAD_PREVIEW_CHARS,
    );
    return {
      ...row,
      payload: JSON.stringify({
        __coworkPayloadTruncated: true,
        originalPayloadBytes: payloadBytes,
        preview,
        eventId: row.id,
        eventDetailId:
          typeof row.event_id === "string" && row.event_id.trim().length > 0
            ? row.event_id
            : row.id,
      }),
    };
  }

  private deriveTimelinePageSummary(events: TaskEvent[]): Partial<TaskTimelinePageResult["summary"]> {
    let planStepCount: number | undefined;
    let hasChecklist = false;
    let outputEventCount = 0;
    let commandSessionCount = 0;
    const commandSessionIds = new Set<string>();

    for (const event of events) {
      const payload = event.payload && typeof event.payload === "object" ? event.payload : {};
      const effectiveType = event.legacyType || event.type;
      if (effectiveType === "plan_created" && Array.isArray((payload as Any).plan?.steps)) {
        planStepCount = (payload as Any).plan.steps.length;
      }
      if ((payload as Any).checklist && Array.isArray((payload as Any).checklist.items)) {
        hasChecklist = true;
      }
      if (
        effectiveType === "file_created" ||
        effectiveType === "file_modified" ||
        effectiveType === "artifact_created"
      ) {
        outputEventCount += 1;
      }
      if (effectiveType === "command_output" || effectiveType === "timeline_command_output") {
        const sessionId =
          typeof (payload as Any).sessionId === "string"
            ? (payload as Any).sessionId
            : typeof event.stepId === "string"
              ? event.stepId
              : event.id;
        commandSessionIds.add(sessionId);
      }
    }

    commandSessionCount = commandSessionIds.size;
    return {
      ...(typeof planStepCount === "number" ? { planStepCount } : {}),
      hasChecklist,
      outputEventCount,
      commandSessionCount,
    };
  }

  private buildTimelinePageWarnings(
    taskId: string,
    payloadBytes: number,
    largestEventPayloadBytes: number,
    truncatedEventCount: number,
  ): string[] | undefined {
    const warnings: string[] = [];
    if (payloadBytes > TaskEventRepository.DEFAULT_TIMELINE_PAGE_BYTE_LIMIT) {
      warnings.push(
        `task ${taskId} timeline page payload is ${payloadBytes} bytes, above ${TaskEventRepository.DEFAULT_TIMELINE_PAGE_BYTE_LIMIT}`,
      );
    }
    if (largestEventPayloadBytes > 1024 * 1024) {
      warnings.push(
        `task ${taskId} has a ${largestEventPayloadBytes} byte timeline event payload`,
      );
    }
    if (truncatedEventCount > 0) {
      warnings.push(`task ${taskId} returned ${truncatedEventCount} truncated timeline events`);
    }
    return warnings.length > 0 ? warnings : undefined;
  }

  private mapRowsToEvents(
    rows: Any[],
    options: { persistMigrations?: boolean } = {},
  ): { events: TaskEvent[]; migratedCount: number } {
    const events: TaskEvent[] = [];
    const migratedRows: TaskEvent[] = [];
    const perTaskSeq = new Map<string, number>();

    for (const row of rows) {
      const taskId = typeof row.task_id === "string" ? row.task_id : "";
      if (!taskId) continue;

      const payload = safeJsonParse(row.payload, {}, "taskEvent.payload");
      const seqFromRow =
        typeof row.seq === "number" && Number.isFinite(row.seq) && row.seq > 0
          ? Math.floor(row.seq)
          : undefined;
      const seq = seqFromRow ?? (perTaskSeq.get(taskId) || 0) + 1;
      perTaskSeq.set(taskId, Math.max(seq, perTaskSeq.get(taskId) || 0));

      const rowEventId =
        typeof row.event_id === "string" && row.event_id.trim().length > 0 ? row.event_id : row.id;
      const rowTs =
        typeof row.ts === "number" && Number.isFinite(row.ts) ? row.ts : Number(row.timestamp) || 0;

      const isV2 = Number(row.schema_version) === 2 && isTimelineEventType(row.type);
      if (isV2) {
        events.push({
          id: row.id,
          taskId,
          timestamp: Number(row.timestamp) || rowTs || Date.now(),
          type: row.type as EventType,
          payload,
          schemaVersion: 2,
          eventId: rowEventId,
          seq,
          ts: rowTs,
          status: typeof row.status === "string" ? row.status : undefined,
          stepId: typeof row.step_id === "string" ? row.step_id : undefined,
          groupId: typeof row.group_id === "string" ? row.group_id : undefined,
          actor: typeof row.actor === "string" ? row.actor : undefined,
          legacyType: typeof row.legacy_type === "string" ? row.legacy_type : undefined,
        });
        continue;
      }

      try {
        const normalized = normalizeTaskEventToTimelineV2({
          taskId,
          type: String(row.type || "error"),
          payload,
          timestamp: Number(row.timestamp) || Date.now(),
          eventId: rowEventId,
          seq,
        });
        const migratedEvent: TaskEvent = {
          ...normalized,
          id: row.id,
        };
        events.push(migratedEvent);
        migratedRows.push(migratedEvent);
      } catch (error) {
        const fallback: TaskEvent = {
          id: row.id,
          taskId,
          timestamp: Number(row.timestamp) || Date.now(),
          type: "timeline_error",
          payload: {
            message: "Legacy event migration failed",
            migrationError: error instanceof Error ? error.message : String(error),
            rawType: row.type,
            rawPayload: payload,
            legacyType: "error",
          },
          schemaVersion: 2,
          eventId: rowEventId,
          seq,
          ts: Number(row.timestamp) || Date.now(),
          status: "failed",
          stepId: `migration:${taskId}`,
          actor: "system",
          legacyType: "error",
        };
        events.push(fallback);
        migratedRows.push(fallback);
      }
    }

    if (migratedRows.length > 0 && options.persistMigrations !== false) {
      this.persistMigratedRows(migratedRows);
    }

    return { events, migratedCount: migratedRows.length };
  }

  private persistMigratedRows(rows: TaskEvent[]): void {
    if (rows.length === 0) return;
    const stmt = this.db.prepare(`
      UPDATE task_events
      SET
        type = ?,
        payload = ?,
        schema_version = 2,
        event_id = ?,
        seq = ?,
        ts = ?,
        status = ?,
        step_id = ?,
        group_id = ?,
        actor = ?,
        legacy_type = ?
      WHERE id = ?
    `);
    const tx = this.db.transaction((items: TaskEvent[]) => {
      for (const event of items) {
        const storedEvent = sanitizeTimelineEventForStorage(event);
        stmt.run(
          storedEvent.type,
          JSON.stringify(storedEvent.payload ?? {}),
          storedEvent.eventId || storedEvent.id,
          typeof storedEvent.seq === "number" ? storedEvent.seq : null,
          typeof storedEvent.ts === "number" ? storedEvent.ts : storedEvent.timestamp,
          typeof storedEvent.status === "string" ? storedEvent.status : null,
          typeof storedEvent.stepId === "string" ? storedEvent.stepId : null,
          typeof storedEvent.groupId === "string" ? storedEvent.groupId : null,
          typeof storedEvent.actor === "string" ? storedEvent.actor : null,
          typeof storedEvent.legacyType === "string" ? storedEvent.legacyType : null,
          storedEvent.id,
        );
      }
    });
    tx(rows);
  }

  getLatestSeq(taskId: string): number {
    const row = this.db
      .prepare("SELECT MAX(COALESCE(seq, 0)) as max_seq FROM task_events WHERE task_id = ?")
      .get(taskId) as { max_seq?: number } | undefined;
    const maxSeq = row?.max_seq;
    return typeof maxSeq === "number" && Number.isFinite(maxSeq) ? Math.floor(maxSeq) : 0;
  }

  migrateLegacyEventsForTask(taskId: string): number {
    const legacyCountRow = this.db
      .prepare(
        `
        SELECT COUNT(1) as count
        FROM task_events
        WHERE task_id = ?
          AND (COALESCE(schema_version, 0) <> 2 OR type NOT LIKE 'timeline_%')
      `,
      )
      .get(taskId) as { count?: number } | undefined;
    const legacyCount =
      typeof legacyCountRow?.count === "number" && Number.isFinite(legacyCountRow.count)
        ? legacyCountRow.count
        : 0;
    if (legacyCount <= 0) return 0;

    const rows = this.db
      .prepare(
        `
        SELECT *
        FROM task_events
        WHERE task_id = ?
        ORDER BY COALESCE(seq, timestamp) ASC, timestamp ASC
      `,
      )
      .all(taskId) as Any[];

    return this.mapRowsToEvents(rows).migratedCount;
  }

  migrateLegacyEventsForTasks(taskIds: string[]): number {
    let migrated = 0;
    for (const taskId of taskIds) {
      if (typeof taskId !== "string" || taskId.trim().length === 0) continue;
      migrated += this.migrateLegacyEventsForTask(taskId.trim());
    }
    return migrated;
  }

  /**
   * Prune old conversation snapshots for a task, keeping only the most recent one.
   * This prevents database bloat from accumulating snapshots over time.
   */
  pruneOldSnapshots(taskId: string): void {
    // Find all conversation_snapshot events for this task, ordered by timestamp descending
    const findStmt = this.db.prepare(`
      SELECT id, timestamp FROM task_events
      WHERE task_id = ?
        AND (
          type = 'conversation_snapshot'
          OR (type LIKE 'timeline_%' AND legacy_type = 'conversation_snapshot')
        )
      ORDER BY timestamp DESC
    `);
    const snapshots = findStmt.all(taskId) as { id: string; timestamp: number }[];

    // Keep only the most recent one, delete the rest
    if (snapshots.length > 1) {
      const idsToDelete = snapshots.slice(1).map((s) => s.id);
      const deleteStmt = this.db.prepare(`
        DELETE FROM task_events WHERE id = ?
      `);

      for (const id of idsToDelete) {
        deleteStmt.run(id);
      }

      console.log(
        `[TaskEventRepository] Pruned ${idsToDelete.length} old snapshot(s) for task ${taskId}`,
      );
    }
  }

  /**
   * Delete events belonging to terminal tasks older than `retentionDays`.
   * Returns the number of deleted rows.
   */
  pruneOldEvents(retentionDays: number = 90): number {
    const cutoff = Date.now() - retentionDays * 24 * 60 * 60 * 1000;
    const stmt = this.db.prepare(`
      DELETE FROM task_events
      WHERE task_id IN (
        SELECT id FROM tasks WHERE status IN ('completed', 'failed', 'cancelled') AND created_at < ?
      )
    `);
    const result = stmt.run(cutoff);
    return result.changes;
  }

  /**
   * Run VACUUM if the SQLite freelist exceeds `thresholdMB` megabytes.
   * Returns true if a vacuum was performed.
   */
  vacuumIfNeeded(thresholdMB: number = 500): boolean {
    const freelistCount = (this.db.pragma("freelist_count") as { freelist_count: number }[])[0]?.freelist_count ?? 0;
    const pageSize = (this.db.pragma("page_size") as { page_size: number }[])[0]?.page_size ?? 4096;
    const freelistMB = (freelistCount * pageSize) / (1024 * 1024);
    if (freelistMB < thresholdMB) return false;
    this.db.exec("VACUUM");
    return true;
  }
}

export class TaskTraceRepository {
  constructor(
    private readonly taskRepo: TaskRepository,
    private readonly taskEventRepo: TaskEventRepository,
  ) {}

  listTaskTraceRuns(
    request: import("../../shared/types").ListTaskTraceRunsRequest = {},
  ): TaskTraceRunSummary[] {
    const limit =
      typeof request.limit === "number" && Number.isFinite(request.limit)
        ? Math.max(1, Math.min(200, Math.floor(request.limit)))
        : 50;
    const scanLimit = Math.max(limit * 20, 500);
    const workspaceId =
      typeof request.workspaceId === "string" && request.workspaceId.trim().length > 0
        ? request.workspaceId.trim()
        : "";

    const tasks = workspaceId
      ? this.taskRepo.findByWorkspace(workspaceId, scanLimit, 0)
      : this.taskRepo.findAll(scanLimit, 0);

    return buildTaskTraceRunSummaries(tasks, { ...request, limit });
  }

  getTaskTraceRun(taskId: string): TaskTraceRunDetail | undefined {
    const task = this.taskRepo.findById(taskId);
    if (!task) return undefined;

    const sessionId = getTaskTraceSessionId(task);
    const siblingTasks = this.listSessionTasks(task);
    const rawEvents = this.taskEventRepo.findByTaskId(taskId);

    return {
      sessionId,
      task,
      siblingRuns: buildTaskTraceSiblingRuns(siblingTasks),
      metrics: buildTaskTraceMetrics(task, rawEvents),
      rawEvents,
      semanticTimeline: normalizeTaskEvents([...rawEvents].sort((a, b) => a.timestamp - b.timestamp)),
    };
  }

  private listSessionTasks(task: Task): Task[] {
    if (!(typeof task.sessionId === "string" && task.sessionId.trim().length > 0)) {
      return [task];
    }

    const sessionId = task.sessionId.trim();
    return this.taskRepo.findBySessionId(sessionId, 5000, 0);
  }
}

export class ArtifactRepository {
  constructor(private db: Database.Database) {}

  create(artifact: Omit<Artifact, "id">): Artifact {
    const newArtifact: Artifact = {
      ...artifact,
      id: uuidv4(),
    };

    const stmt = this.db.prepare(`
      INSERT INTO artifacts (id, task_id, path, mime_type, sha256, size, created_at)
      VALUES (?, ?, ?, ?, ?, ?, ?)
    `);

    stmt.run(
      newArtifact.id,
      newArtifact.taskId,
      newArtifact.path,
      newArtifact.mimeType,
      newArtifact.sha256,
      newArtifact.size,
      newArtifact.createdAt,
    );

    return newArtifact;
  }

  findByTaskId(taskId: string): Artifact[] {
    const stmt = this.db.prepare(
      "SELECT * FROM artifacts WHERE task_id = ? ORDER BY created_at DESC",
    );
    const rows = stmt.all(taskId) as Any[];
    return rows.map((row) => this.mapRowToArtifact(row));
  }

  findById(id: string): Artifact | undefined {
    const stmt = this.db.prepare("SELECT * FROM artifacts WHERE id = ?");
    const row = stmt.get(id) as Any;
    return row ? this.mapRowToArtifact(row) : undefined;
  }

  findLatestByPath(artifactPath: string): Artifact | undefined {
    const stmt = this.db.prepare(
      "SELECT * FROM artifacts WHERE path = ? ORDER BY created_at DESC LIMIT 1",
    );
    const row = stmt.get(artifactPath) as Any;
    return row ? this.mapRowToArtifact(row) : undefined;
  }

  private mapRowToArtifact(row: Any): Artifact {
    return {
      id: row.id,
      taskId: row.task_id,
      path: row.path,
      mimeType: row.mime_type,
      sha256: row.sha256,
      size: row.size,
      createdAt: row.created_at,
    };
  }
}

const ANNOTATION_STATUSES: AnnotationStatus[] = [
  "open",
  "addressing",
  "addressed",
  "resolved",
  "dismissed",
];

function normalizeAnnotationStatus(value: unknown, fallback: AnnotationStatus = "open"): AnnotationStatus {
  return ANNOTATION_STATUSES.includes(value as AnnotationStatus)
    ? (value as AnnotationStatus)
    : fallback;
}

export class AnnotationRepository {
  constructor(private db: Database.Database) {}

  create(input: AnnotationCreateInput): Annotation {
    const now = Date.now();
    const annotation: Annotation = {
      id: uuidv4(),
      taskId: input.taskId,
      workspaceId: input.workspaceId,
      surfaceType: input.surfaceType,
      surfaceId: input.surfaceId,
      body: input.body.trim(),
      status: "open",
      targetRef: input.targetRef,
      stylePatch: input.stylePatch,
      artifactId: input.artifactId,
      screenshotPath: input.screenshotPath,
      createdBy: input.createdBy || "user",
      createdAt: now,
      updatedAt: now,
    };

    this.db
      .prepare(`
        INSERT INTO annotations (
          id, task_id, workspace_id, surface_type, surface_id, body, status,
          target_ref_json, style_patch_json, artifact_id, screenshot_path,
          created_by, created_at, updated_at, resolved_at, resolved_by_event_id
        )
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      `)
      .run(
        annotation.id,
        annotation.taskId,
        annotation.workspaceId || null,
        annotation.surfaceType,
        annotation.surfaceId || null,
        annotation.body,
        annotation.status,
        JSON.stringify(annotation.targetRef),
        annotation.stylePatch ? JSON.stringify(annotation.stylePatch) : null,
        annotation.artifactId || null,
        annotation.screenshotPath || null,
        annotation.createdBy,
        annotation.createdAt,
        annotation.updatedAt,
        null,
        null,
      );

    return annotation;
  }

  update(id: string, patch: AnnotationUpdateInput): Annotation | undefined {
    const current = this.findById(id);
    if (!current) return undefined;

    const nextStatus = patch.status ? normalizeAnnotationStatus(patch.status, current.status) : current.status;
    const now = Date.now();
    const nextStylePatchJson =
      patch.stylePatch === null
        ? null
        : patch.stylePatch !== undefined
          ? JSON.stringify(patch.stylePatch)
          : current.stylePatch
            ? JSON.stringify(current.stylePatch)
            : null;
    const resolvedAt =
      nextStatus === "resolved" || nextStatus === "dismissed"
        ? current.resolvedAt || now
        : patch.status && patch.status !== current.status
          ? null
          : current.resolvedAt || null;

    this.db
      .prepare(`
        UPDATE annotations
        SET body = ?,
            status = ?,
            target_ref_json = ?,
            style_patch_json = ?,
            artifact_id = ?,
            screenshot_path = ?,
            updated_at = ?,
            resolved_at = ?,
            resolved_by_event_id = ?
        WHERE id = ?
      `)
      .run(
        patch.body !== undefined ? patch.body.trim() : current.body,
        nextStatus,
        JSON.stringify(patch.targetRef || current.targetRef),
        nextStylePatchJson,
        patch.artifactId === null ? null : patch.artifactId || current.artifactId || null,
        patch.screenshotPath === null ? null : patch.screenshotPath || current.screenshotPath || null,
        now,
        resolvedAt,
        patch.resolvedByEventId === null
          ? null
          : patch.resolvedByEventId || current.resolvedByEventId || null,
        id,
      );

    return this.findById(id);
  }

  markAddressing(taskId: string, annotationIds?: string[]): number {
    const ids = (annotationIds || []).map((id) => id.trim()).filter(Boolean);
    const now = Date.now();
    if (ids.length > 0) {
      const placeholders = ids.map(() => "?").join(", ");
      const result = this.db
        .prepare(`
          UPDATE annotations
          SET status = 'addressing', updated_at = ?
          WHERE task_id = ?
            AND status = 'open'
            AND id IN (${placeholders})
        `)
        .run(now, taskId, ...ids);
      return Number(result.changes || 0);
    }
    const result = this.db
      .prepare(`
        UPDATE annotations
        SET status = 'addressing', updated_at = ?
        WHERE task_id = ?
          AND status = 'open'
      `)
      .run(now, taskId);
    return Number(result.changes || 0);
  }

  markAddressed(taskId: string): number {
    const result = this.db
      .prepare(`
        UPDATE annotations
        SET status = 'addressed', updated_at = ?
        WHERE task_id = ?
          AND status = 'addressing'
      `)
      .run(Date.now(), taskId);
    return Number(result.changes || 0);
  }

  findById(id: string): Annotation | undefined {
    const row = this.db.prepare("SELECT * FROM annotations WHERE id = ?").get(id) as Any;
    return row ? this.mapRowToAnnotation(row) : undefined;
  }

  list(query: AnnotationListQuery = {}): Annotation[] {
    const clauses: string[] = [];
    const params: unknown[] = [];
    if (query.taskId) {
      clauses.push("task_id = ?");
      params.push(query.taskId);
    }
    if (query.workspaceId) {
      clauses.push("workspace_id = ?");
      params.push(query.workspaceId);
    }
    if (query.surfaceType) {
      clauses.push("surface_type = ?");
      params.push(query.surfaceType);
    }
    if (query.surfaceId) {
      clauses.push("surface_id = ?");
      params.push(query.surfaceId);
    }
    const statuses = (query.statuses || []).filter((status) =>
      ANNOTATION_STATUSES.includes(status),
    );
    if (statuses.length > 0) {
      clauses.push(`status IN (${statuses.map(() => "?").join(", ")})`);
      params.push(...statuses);
    }
    const limit =
      typeof query.limit === "number" && Number.isFinite(query.limit)
        ? Math.min(Math.max(Math.floor(query.limit), 1), 500)
        : 200;
    const where = clauses.length > 0 ? `WHERE ${clauses.join(" AND ")}` : "";
    const rows = this.db
      .prepare(`
        SELECT *
        FROM annotations
        ${where}
        ORDER BY created_at DESC
        LIMIT ?
      `)
      .all(...params, limit) as Any[];
    return rows.map((row) => this.mapRowToAnnotation(row));
  }

  listOpenByTask(taskId: string): Annotation[] {
    return this.list({
      taskId,
      statuses: ["open", "addressing"],
      limit: 100,
    });
  }

  private mapRowToAnnotation(row: Any): Annotation {
    const status = normalizeAnnotationStatus(row.status);
    return {
      id: row.id,
      taskId: row.task_id,
      workspaceId: row.workspace_id || undefined,
      surfaceType: row.surface_type as Annotation["surfaceType"],
      surfaceId: row.surface_id || undefined,
      body: row.body || "",
      status,
      targetRef: safeJsonParse(row.target_ref_json, {
        surfaceType: row.surface_type || "browser",
      } as Annotation["targetRef"], "annotation.target_ref_json"),
      stylePatch: row.style_patch_json
        ? safeJsonParse(row.style_patch_json, undefined, "annotation.style_patch_json")
        : undefined,
      artifactId: row.artifact_id || undefined,
      screenshotPath: row.screenshot_path || undefined,
      createdBy: (row.created_by || "user") as Annotation["createdBy"],
      createdAt: row.created_at,
      updatedAt: row.updated_at,
      resolvedAt: row.resolved_at || undefined,
      resolvedByEventId: row.resolved_by_event_id || undefined,
    };
  }
}

export class ApprovalRepository {
  constructor(private db: Database.Database) {}

  create(approval: Omit<ApprovalRequest, "id">): ApprovalRequest {
    const newApproval: ApprovalRequest = {
      ...approval,
      id: uuidv4(),
    };

    const stmt = this.db.prepare(`
      INSERT INTO approvals (id, task_id, type, description, details, status, requested_at)
      VALUES (?, ?, ?, ?, ?, ?, ?)
    `);

    stmt.run(
      newApproval.id,
      newApproval.taskId,
      newApproval.type,
      newApproval.description,
      JSON.stringify(newApproval.details),
      newApproval.status,
      newApproval.requestedAt,
    );

    return newApproval;
  }

  update(id: string, status: "approved" | "denied"): void {
    const stmt = this.db.prepare(`
      UPDATE approvals
      SET status = ?, resolved_at = ?
      WHERE id = ?
    `);
    stmt.run(status, Date.now(), id);
  }

  findPendingByTaskId(taskId: string): ApprovalRequest[] {
    const stmt = this.db.prepare(`
      SELECT * FROM approvals
      WHERE task_id = ? AND status = 'pending'
      ORDER BY requested_at ASC
    `);
    const rows = stmt.all(taskId) as Any[];
    return rows.map((row) => this.mapRowToApproval(row));
  }

  findPending(limit = 100): ApprovalRequest[] {
    const safeLimit =
      typeof limit === "number" && Number.isFinite(limit) && limit > 0
        ? Math.min(1000, Math.floor(limit))
        : 100;
    const stmt = this.db.prepare(`
      SELECT * FROM approvals
      WHERE status = 'pending'
      ORDER BY requested_at ASC
      LIMIT ?
    `);
    const rows = stmt.all(safeLimit) as Any[];
    return rows.map((row) => this.mapRowToApproval(row));
  }

  private mapRowToApproval(row: Any): ApprovalRequest {
    return {
      id: row.id,
      taskId: row.task_id,
      type: row.type,
      description: row.description,
      details: safeJsonParse(row.details, {}, "approval.details"),
      status: row.status,
      requestedAt: row.requested_at,
      resolvedAt: row.resolved_at || undefined,
    };
  }
}

export class WorkspacePermissionRuleRepository {
  constructor(private db: Database.Database) {}

  listByWorkspaceId(workspaceId: string): PersistedPermissionRule[] {
    const stmt = this.db.prepare(`
      SELECT *
      FROM workspace_permission_rules
      WHERE workspace_id = ?
      ORDER BY updated_at DESC, created_at DESC
    `);
    const rows = stmt.all(workspaceId) as Any[];
    return rows.map((row) => this.mapRowToRule(row));
  }

  findById(id: string): PersistedPermissionRule | null {
    const row = this.db
      .prepare(`SELECT * FROM workspace_permission_rules WHERE id = ?`)
      .get(id) as Any | undefined;
    return row ? this.mapRowToRule(row) : null;
  }

  create(rule: {
    workspaceId: string;
    effect: PersistedPermissionRule["effect"];
    scope: PersistedPermissionRule["scope"];
    metadata?: Record<string, unknown>;
  }): PersistedPermissionRule {
    const now = Date.now();
    const id = uuidv4();
    const stmt = this.db.prepare(`
      INSERT INTO workspace_permission_rules (
        id,
        workspace_id,
        effect,
        scope_kind,
        scope_tool_name,
        scope_path,
        scope_prefix,
        scope_server_name,
        metadata_json,
        created_at,
        updated_at
      )
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `);
    stmt.run(
      id,
      rule.workspaceId,
      rule.effect,
      rule.scope.kind,
      "toolName" in rule.scope ? rule.scope.toolName || null : null,
      "path" in rule.scope ? rule.scope.path || null : null,
      "prefix" in rule.scope ? rule.scope.prefix || null : null,
      "serverName" in rule.scope
        ? rule.scope.serverName || null
        : "domain" in rule.scope
          ? rule.scope.domain || null
          : null,
      JSON.stringify(rule.metadata ?? {}),
      now,
      now,
    );

    return {
      id,
      workspaceId: rule.workspaceId,
      source: "workspace_db",
      effect: rule.effect,
      scope: rule.scope,
      metadata: rule.metadata,
      createdAt: now,
    };
  }

  deleteById(id: string): PersistedPermissionRule | null {
    const existing = this.findById(id);
    if (!existing) {
      return null;
    }
    this.db.prepare(`DELETE FROM workspace_permission_rules WHERE id = ?`).run(id);
    return existing;
  }

  deleteByWorkspaceAndId(workspaceId: string, id: string): PersistedPermissionRule | null {
    const existing = this.findById(id);
    if (!existing || existing.workspaceId !== workspaceId) {
      return null;
    }
    this.db.prepare(`DELETE FROM workspace_permission_rules WHERE id = ?`).run(id);
    return existing;
  }

  private mapRowToRule(row: Any): PersistedPermissionRule {
    const scopeKind = String(row.scope_kind || "");
    let scope: PersistedPermissionRule["scope"];
    switch (scopeKind) {
      case "domain":
        scope = {
          kind: "domain",
          domain: String(row.scope_server_name || ""),
          ...(typeof row.scope_tool_name === "string" && row.scope_tool_name
            ? { toolName: row.scope_tool_name }
            : {}),
        };
        break;
      case "path":
        scope = {
          kind: "path",
          path: String(row.scope_path || ""),
          ...(typeof row.scope_tool_name === "string" && row.scope_tool_name
            ? { toolName: row.scope_tool_name }
            : {}),
        };
        break;
      case "command_prefix":
        scope = {
          kind: "command_prefix",
          prefix: String(row.scope_prefix || ""),
        };
        break;
      case "mcp_server":
        scope = {
          kind: "mcp_server",
          serverName: String(row.scope_server_name || ""),
        };
        break;
      case "tool":
      default:
        scope = {
          kind: "tool",
          toolName: String(row.scope_tool_name || ""),
        };
        break;
    }

    return {
      id: row.id,
      workspaceId: row.workspace_id,
      source: "workspace_db",
      effect: row.effect,
      scope,
      metadata: safeJsonParse(row.metadata_json, {}, "workspacePermissionRule.metadata"),
      createdAt: row.created_at,
    };
  }
}

export class InputRequestRepository {
  constructor(private db: Database.Database) {}

  create(request: {
    taskId: string;
    questions: InputRequest["questions"];
    requestedAt: number;
    status?: InputRequest["status"];
  }): InputRequest {
    const newRequest: InputRequest = {
      id: uuidv4(),
      taskId: request.taskId,
      questions: request.questions,
      status: request.status || "pending",
      requestedAt: request.requestedAt,
    };

    const stmt = this.db.prepare(`
      INSERT INTO input_requests (id, task_id, questions, status, answers, requested_at, resolved_at)
      VALUES (?, ?, ?, ?, ?, ?, ?)
    `);

    stmt.run(
      newRequest.id,
      newRequest.taskId,
      JSON.stringify(newRequest.questions),
      newRequest.status,
      null,
      newRequest.requestedAt,
      null,
    );

    return newRequest;
  }

  resolve(
    id: string,
    status: Extract<InputRequest["status"], "submitted" | "dismissed">,
    answers?: InputRequest["answers"],
  ): void {
    const stmt = this.db.prepare(`
      UPDATE input_requests
      SET status = ?, answers = ?, resolved_at = ?
      WHERE id = ? AND status = 'pending'
    `);
    stmt.run(status, answers ? JSON.stringify(answers) : null, Date.now(), id);
  }

  findById(id: string): InputRequest | undefined {
    const stmt = this.db.prepare("SELECT * FROM input_requests WHERE id = ?");
    const row = stmt.get(id) as Any;
    return row ? this.mapRowToInputRequest(row) : undefined;
  }

  findPendingByTaskId(taskId: string): InputRequest[] {
    const stmt = this.db.prepare(`
      SELECT * FROM input_requests
      WHERE task_id = ? AND status = 'pending'
      ORDER BY requested_at ASC
    `);
    const rows = stmt.all(taskId) as Any[];
    return rows.map((row) => this.mapRowToInputRequest(row));
  }

  list(params: {
    limit: number;
    offset: number;
    taskId?: string;
    status?: InputRequest["status"];
  }): InputRequest[] {
    if (params.taskId && params.status) {
      const stmt = this.db.prepare(`
        SELECT * FROM input_requests
        WHERE task_id = ? AND status = ?
        ORDER BY requested_at DESC
        LIMIT ? OFFSET ?
      `);
      const rows = stmt.all(params.taskId, params.status, params.limit, params.offset) as Any[];
      return rows.map((row) => this.mapRowToInputRequest(row));
    }

    if (params.taskId) {
      const stmt = this.db.prepare(`
        SELECT * FROM input_requests
        WHERE task_id = ?
        ORDER BY requested_at DESC
        LIMIT ? OFFSET ?
      `);
      const rows = stmt.all(params.taskId, params.limit, params.offset) as Any[];
      return rows.map((row) => this.mapRowToInputRequest(row));
    }

    if (params.status) {
      const stmt = this.db.prepare(`
        SELECT * FROM input_requests
        WHERE status = ?
        ORDER BY requested_at DESC
        LIMIT ? OFFSET ?
      `);
      const rows = stmt.all(params.status, params.limit, params.offset) as Any[];
      return rows.map((row) => this.mapRowToInputRequest(row));
    }

    const stmt = this.db.prepare(`
      SELECT * FROM input_requests
      ORDER BY requested_at DESC
      LIMIT ? OFFSET ?
    `);
    const rows = stmt.all(params.limit, params.offset) as Any[];
    return rows.map((row) => this.mapRowToInputRequest(row));
  }

  private mapRowToInputRequest(row: Any): InputRequest {
    return {
      id: String(row.id ?? ""),
      taskId: String(row.task_id ?? ""),
      questions: safeJsonParse<InputRequest["questions"]>(
        row.questions,
        [],
        "inputRequest.questions",
      ),
      status: String(row.status ?? "pending") as InputRequest["status"],
      answers: row.answers
        ? safeJsonParse<InputRequest["answers"]>(row.answers, undefined, "inputRequest.answers")
        : undefined,
      requestedAt: Number(row.requested_at ?? 0),
      resolvedAt: row.resolved_at ? Number(row.resolved_at) : undefined,
    };
  }
}

export class SkillRepository {
  constructor(private db: Database.Database) {}

  create(skill: Omit<Skill, "id">): Skill {
    const newSkill: Skill = {
      ...skill,
      id: uuidv4(),
    };

    const stmt = this.db.prepare(`
      INSERT INTO skills (id, name, description, category, prompt, script_path, parameters)
      VALUES (?, ?, ?, ?, ?, ?, ?)
    `);

    stmt.run(
      newSkill.id,
      newSkill.name,
      newSkill.description,
      newSkill.category,
      newSkill.prompt,
      newSkill.scriptPath || null,
      newSkill.parameters ? JSON.stringify(newSkill.parameters) : null,
    );

    return newSkill;
  }

  findAll(): Skill[] {
    const stmt = this.db.prepare("SELECT * FROM skills ORDER BY name ASC");
    const rows = stmt.all() as Any[];
    return rows.map((row) => this.mapRowToSkill(row));
  }

  findById(id: string): Skill | undefined {
    const stmt = this.db.prepare("SELECT * FROM skills WHERE id = ?");
    const row = stmt.get(id) as Any;
    return row ? this.mapRowToSkill(row) : undefined;
  }

  private mapRowToSkill(row: Any): Skill {
    return {
      id: row.id,
      name: row.name,
      description: row.description,
      category: row.category,
      prompt: row.prompt,
      scriptPath: row.script_path || undefined,
      parameters: row.parameters
        ? safeJsonParse(row.parameters, undefined, "skill.parameters")
        : undefined,
    };
  }
}

export interface LLMModel {
  id: string;
  key: string;
  displayName: string;
  description: string;
  anthropicModelId: string;
  bedrockModelId: string;
  sortOrder: number;
  isActive: boolean;
  createdAt: number;
  updatedAt: number;
}

export class LLMModelRepository {
  constructor(private db: Database.Database) {}

  findAll(): LLMModel[] {
    const stmt = this.db.prepare(`
      SELECT * FROM llm_models
      WHERE is_active = 1
      ORDER BY sort_order ASC
    `);
    const rows = stmt.all() as Any[];
    return rows.map((row) => this.mapRowToModel(row));
  }

  findByKey(key: string): LLMModel | undefined {
    const stmt = this.db.prepare("SELECT * FROM llm_models WHERE key = ?");
    const row = stmt.get(key) as Any;
    return row ? this.mapRowToModel(row) : undefined;
  }

  findById(id: string): LLMModel | undefined {
    const stmt = this.db.prepare("SELECT * FROM llm_models WHERE id = ?");
    const row = stmt.get(id) as Any;
    return row ? this.mapRowToModel(row) : undefined;
  }

  private mapRowToModel(row: Any): LLMModel {
    return {
      id: row.id,
      key: row.key,
      displayName: row.display_name,
      description: row.description,
      anthropicModelId: row.anthropic_model_id,
      bedrockModelId: row.bedrock_model_id,
      sortOrder: row.sort_order,
      isActive: row.is_active === 1,
      createdAt: row.created_at,
      updatedAt: row.updated_at,
    };
  }
}

// ============================================================
// Channel Gateway Repositories
// ============================================================

const channelRepoLogger = createLogger("ChannelRepository");
const CHANNEL_CONFIG_ENCRYPTED_PREFIX = "enc:";

interface ChannelConfigReadResult {
  json: string;
  encrypted: boolean;
  readError?: string;
}

/**
 * Encrypt a channel config JSON string using OS keychain via safeStorage.
 * Refuses to persist secrets when secure storage is unavailable.
 */
function encryptChannelConfig(json: string): string {
  try {
    const safeStorage = getSafeStorage();
    if (safeStorage?.isEncryptionAvailable()) {
      return CHANNEL_CONFIG_ENCRYPTED_PREFIX + safeStorage.encryptString(json).toString("base64");
    }
    throw new Error(
      "Secure storage is unavailable. Refusing to store channel credentials in plaintext.",
    );
  } catch (error) {
    channelRepoLogger.error("Failed to encrypt channel config:", error);
    throw error instanceof Error
      ? error
      : new Error("Failed to encrypt channel config with secure storage.");
  }
}

/**
 * Decrypt a channel config value that was encrypted with encryptChannelConfig.
 * Handles both encrypted and legacy plaintext values transparently.
 */
function decryptChannelConfig(value: string): ChannelConfigReadResult {
  if (!value.startsWith(CHANNEL_CONFIG_ENCRYPTED_PREFIX)) {
    return {
      json: value,
      encrypted: false,
    };
  }
  try {
    const safeStorage = getSafeStorage();
    if (safeStorage?.isEncryptionAvailable()) {
      const buf = Buffer.from(value.slice(CHANNEL_CONFIG_ENCRYPTED_PREFIX.length), "base64");
      return {
        json: safeStorage.decryptString(buf),
        encrypted: true,
      };
    }
    const readError =
      "Channel configuration is encrypted with OS secure storage and cannot be decrypted in this environment.";
    channelRepoLogger.error(readError);
    return {
      json: "{}",
      encrypted: true,
      readError,
    };
  } catch (error) {
    channelRepoLogger.error("Failed to decrypt channel config:", error);
    return {
      json: "{}",
      encrypted: true,
      readError:
        "Channel configuration is encrypted but could not be decrypted. Refusing to overwrite it until secure storage is available again.",
    };
  }
}

export interface Channel {
  id: string;
  type: string;
  name: string;
  enabled: boolean;
  config: Record<string, unknown>;
  configEncrypted?: boolean;
  configReadError?: string;
  securityConfig: {
    mode: "open" | "allowlist" | "pairing";
    allowedUsers?: string[];
    pairingCodeTTL?: number;
    maxPairingAttempts?: number;
    rateLimitPerMinute?: number;
  };
  status: string;
  botUsername?: string;
  createdAt: number;
  updatedAt: number;
}

export interface ChannelUser {
  id: string;
  channelId: string;
  channelUserId: string;
  displayName: string;
  username?: string;
  allowed: boolean;
  pairingCode?: string;
  pairingAttempts: number;
  pairingExpiresAt?: number;
  /** Separate field for brute-force lockout timestamp (distinct from pairing code expiration) */
  lockoutUntil?: number;
  createdAt: number;
  lastSeenAt: number;
}

export interface ChannelSession {
  id: string;
  channelId: string;
  chatId: string;
  userId?: string;
  taskId?: string;
  workspaceId?: string;
  state: "idle" | "active" | "waiting_approval";
  context?: Record<string, unknown>;
  shellEnabled?: boolean;
  debugMode?: boolean;
  createdAt: number;
  lastActivityAt: number;
}

export interface ChannelMessage {
  id: string;
  channelId: string;
  sessionId?: string;
  channelMessageId: string;
  chatId: string;
  userId?: string;
  /**
   * Message direction as recorded by the gateway.
   * - incoming: message received from another user/device
   * - outgoing: message sent by CoWork OS back into the chat
   * - outgoing_user: message sent by the local user (captured from some channels when enabled)
   */
  direction: "incoming" | "outgoing" | "outgoing_user";
  content: string;
  attachments?: Array<{ type: string; url?: string; fileName?: string }>;
  timestamp: number;
}

export class ChannelRepository {
  constructor(private db: Database.Database) {}

  create(channel: Omit<Channel, "id" | "createdAt" | "updatedAt">): Channel {
    const now = Date.now();
    const newChannel: Channel = {
      ...channel,
      id: uuidv4(),
      createdAt: now,
      updatedAt: now,
    };

    const stmt = this.db.prepare(`
      INSERT INTO channels (id, type, name, enabled, config, security_config, status, bot_username, created_at, updated_at)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `);

    stmt.run(
      newChannel.id,
      newChannel.type,
      newChannel.name,
      newChannel.enabled ? 1 : 0,
      encryptChannelConfig(JSON.stringify(newChannel.config)),
      JSON.stringify(newChannel.securityConfig),
      newChannel.status,
      newChannel.botUsername || null,
      newChannel.createdAt,
      newChannel.updatedAt,
    );

    return newChannel;
  }

  update(id: string, updates: Partial<Channel>): void {
    const existingChannel = updates.config !== undefined ? this.findById(id) : undefined;
    if (updates.config !== undefined && existingChannel?.configReadError) {
      throw new Error(existingChannel.configReadError);
    }

    const fields: string[] = [];
    const values: unknown[] = [];

    if (updates.name !== undefined) {
      fields.push("name = ?");
      values.push(updates.name);
    }
    if (updates.enabled !== undefined) {
      fields.push("enabled = ?");
      values.push(updates.enabled ? 1 : 0);
    }
    if (updates.config !== undefined) {
      fields.push("config = ?");
      values.push(encryptChannelConfig(JSON.stringify(updates.config)));
    }
    if (updates.securityConfig !== undefined) {
      fields.push("security_config = ?");
      values.push(JSON.stringify(updates.securityConfig));
    }
    if (updates.status !== undefined) {
      fields.push("status = ?");
      values.push(updates.status);
    }
    if (updates.botUsername !== undefined) {
      fields.push("bot_username = ?");
      values.push(updates.botUsername);
    }

    if (fields.length === 0) return;

    fields.push("updated_at = ?");
    values.push(Date.now());
    values.push(id);

    const stmt = this.db.prepare(`UPDATE channels SET ${fields.join(", ")} WHERE id = ?`);
    stmt.run(...values);
  }

  findById(id: string): Channel | undefined {
    const stmt = this.db.prepare("SELECT * FROM channels WHERE id = ?");
    const row = stmt.get(id) as Record<string, unknown> | undefined;
    return row ? this.mapRowToChannel(row) : undefined;
  }

  findByType(type: string): Channel | undefined {
    const stmt = this.db.prepare("SELECT * FROM channels WHERE type = ?");
    const row = stmt.get(type) as Record<string, unknown> | undefined;
    return row ? this.mapRowToChannel(row) : undefined;
  }

  findAllByType(type: string): Channel[] {
    const stmt = this.db.prepare("SELECT * FROM channels WHERE type = ? ORDER BY created_at ASC");
    const rows = stmt.all(type) as Record<string, unknown>[];
    return rows.map((row) => this.mapRowToChannel(row));
  }

  findAll(): Channel[] {
    const stmt = this.db.prepare("SELECT * FROM channels ORDER BY created_at ASC");
    const rows = stmt.all() as Record<string, unknown>[];
    return rows.map((row) => this.mapRowToChannel(row));
  }

  findEnabled(): Channel[] {
    const stmt = this.db.prepare(
      "SELECT * FROM channels WHERE enabled = 1 ORDER BY created_at ASC",
    );
    const rows = stmt.all() as Record<string, unknown>[];
    return rows.map((row) => this.mapRowToChannel(row));
  }

  delete(id: string): void {
    const stmt = this.db.prepare("DELETE FROM channels WHERE id = ?");
    stmt.run(id);
  }

  private mapRowToChannel(row: Record<string, unknown>): Channel {
    const defaultSecurityConfig = { mode: "pairing" as const };
    const configState = decryptChannelConfig(row.config as string);
    return {
      id: row.id as string,
      type: row.type as string,
      name: row.name as string,
      enabled: row.enabled === 1,
      config: safeJsonParse(configState.json, {}, "channel.config"),
      configEncrypted: configState.encrypted,
      configReadError: configState.readError,
      securityConfig: safeJsonParse(
        row.security_config as string,
        defaultSecurityConfig,
        "channel.securityConfig",
      ),
      status: row.status as string,
      botUsername: (row.bot_username as string) || undefined,
      createdAt: row.created_at as number,
      updatedAt: row.updated_at as number,
    };
  }
}

export class ChannelUserRepository {
  constructor(private db: Database.Database) {}

  create(
    user: Omit<ChannelUser, "id" | "createdAt" | "lastSeenAt" | "pairingAttempts">,
  ): ChannelUser {
    const now = Date.now();
    const newUser: ChannelUser = {
      ...user,
      id: uuidv4(),
      pairingAttempts: 0,
      createdAt: now,
      lastSeenAt: now,
    };

    const stmt = this.db.prepare(`
      INSERT INTO channel_users (id, channel_id, channel_user_id, display_name, username, allowed, pairing_code, pairing_attempts, pairing_expires_at, created_at, last_seen_at)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `);

    stmt.run(
      newUser.id,
      newUser.channelId,
      newUser.channelUserId,
      newUser.displayName,
      newUser.username || null,
      newUser.allowed ? 1 : 0,
      newUser.pairingCode || null,
      newUser.pairingAttempts,
      newUser.pairingExpiresAt || null,
      newUser.createdAt,
      newUser.lastSeenAt,
    );

    return newUser;
  }

  update(id: string, updates: Partial<ChannelUser>): void {
    const fields: string[] = [];
    const values: unknown[] = [];

    if (updates.displayName !== undefined) {
      fields.push("display_name = ?");
      values.push(updates.displayName);
    }
    if (updates.username !== undefined) {
      fields.push("username = ?");
      values.push(updates.username);
    }
    if (updates.allowed !== undefined) {
      fields.push("allowed = ?");
      values.push(updates.allowed ? 1 : 0);
    }
    if (updates.pairingCode !== undefined) {
      fields.push("pairing_code = ?");
      values.push(updates.pairingCode);
    }
    if (updates.pairingAttempts !== undefined) {
      fields.push("pairing_attempts = ?");
      values.push(updates.pairingAttempts);
    }
    if (updates.pairingExpiresAt !== undefined) {
      fields.push("pairing_expires_at = ?");
      values.push(updates.pairingExpiresAt);
    }
    if (updates.lockoutUntil !== undefined) {
      fields.push("lockout_until = ?");
      values.push(updates.lockoutUntil);
    }
    if (updates.lastSeenAt !== undefined) {
      fields.push("last_seen_at = ?");
      values.push(updates.lastSeenAt);
    }

    if (fields.length === 0) return;

    values.push(id);
    const stmt = this.db.prepare(`UPDATE channel_users SET ${fields.join(", ")} WHERE id = ?`);
    stmt.run(...values);
  }

  findById(id: string): ChannelUser | undefined {
    const stmt = this.db.prepare("SELECT * FROM channel_users WHERE id = ?");
    const row = stmt.get(id) as Record<string, unknown> | undefined;
    return row ? this.mapRowToUser(row) : undefined;
  }

  findByChannelUserId(channelId: string, channelUserId: string): ChannelUser | undefined {
    const stmt = this.db.prepare(
      "SELECT * FROM channel_users WHERE channel_id = ? AND channel_user_id = ?",
    );
    const row = stmt.get(channelId, channelUserId) as Record<string, unknown> | undefined;
    return row ? this.mapRowToUser(row) : undefined;
  }

  findByChannelId(channelId: string): ChannelUser[] {
    const stmt = this.db.prepare(
      "SELECT * FROM channel_users WHERE channel_id = ? ORDER BY last_seen_at DESC",
    );
    const rows = stmt.all(channelId) as Record<string, unknown>[];
    return rows.map((row) => this.mapRowToUser(row));
  }

  findAllowedByChannelId(channelId: string): ChannelUser[] {
    const stmt = this.db.prepare(
      "SELECT * FROM channel_users WHERE channel_id = ? AND allowed = 1 ORDER BY last_seen_at DESC",
    );
    const rows = stmt.all(channelId) as Record<string, unknown>[];
    return rows.map((row) => this.mapRowToUser(row));
  }

  deleteByChannelId(channelId: string): void {
    const stmt = this.db.prepare("DELETE FROM channel_users WHERE channel_id = ?");
    stmt.run(channelId);
  }

  delete(id: string): void {
    const stmt = this.db.prepare("DELETE FROM channel_users WHERE id = ?");
    stmt.run(id);
  }

  /**
   * Delete expired pending pairing entries
   * These are placeholder entries created when generating pairing codes that have expired
   * Returns the number of deleted entries
   */
  deleteExpiredPending(channelId: string): number {
    const now = Date.now();
    const stmt = this.db.prepare(`
      DELETE FROM channel_users
      WHERE channel_id = ?
        AND allowed = 0
        AND channel_user_id LIKE 'pending_%'
        AND (
          pairing_expires_at IS NULL
          OR pairing_code IS NULL
          OR pairing_expires_at < ?
        )
    `);
    const result = stmt.run(channelId, now);
    return result.changes;
  }

  /**
   * Delete all pending pairing entries for a channel (valid or expired).
   */
  deletePendingByChannel(channelId: string): number {
    const stmt = this.db.prepare(`
      DELETE FROM channel_users
      WHERE channel_id = ?
        AND allowed = 0
        AND channel_user_id LIKE 'pending_%'
    `);
    const result = stmt.run(channelId);
    return result.changes;
  }

  /**
   * Delete expired or empty pending pairing entries across all channels.
   */
  deleteExpiredPendingAll(): number {
    const now = Date.now();
    const stmt = this.db.prepare(`
      DELETE FROM channel_users
      WHERE allowed = 0
        AND channel_user_id LIKE 'pending_%'
        AND (
          pairing_expires_at IS NULL
          OR pairing_code IS NULL
          OR pairing_expires_at < ?
        )
    `);
    const result = stmt.run(now);
    return result.changes;
  }

  findByPairingCode(channelId: string, pairingCode: string): ChannelUser | undefined {
    const stmt = this.db.prepare(
      "SELECT * FROM channel_users WHERE channel_id = ? AND UPPER(pairing_code) = UPPER(?)",
    );
    const row = stmt.get(channelId, pairingCode) as Record<string, unknown> | undefined;
    return row ? this.mapRowToUser(row) : undefined;
  }

  private mapRowToUser(row: Record<string, unknown>): ChannelUser {
    return {
      id: row.id as string,
      channelId: row.channel_id as string,
      channelUserId: row.channel_user_id as string,
      displayName: row.display_name as string,
      username: (row.username as string) || undefined,
      allowed: row.allowed === 1,
      pairingCode: (row.pairing_code as string) || undefined,
      pairingAttempts: row.pairing_attempts as number,
      pairingExpiresAt: (row.pairing_expires_at as number) || undefined,
      lockoutUntil: (row.lockout_until as number) || undefined,
      createdAt: row.created_at as number,
      lastSeenAt: row.last_seen_at as number,
    };
  }
}

export class ChannelSessionRepository {
  constructor(private db: Database.Database) {}

  create(session: Omit<ChannelSession, "id" | "createdAt" | "lastActivityAt">): ChannelSession {
    const now = Date.now();
    const newSession: ChannelSession = {
      ...session,
      id: uuidv4(),
      createdAt: now,
      lastActivityAt: now,
    };

    const stmt = this.db.prepare(`
      INSERT INTO channel_sessions (id, channel_id, chat_id, user_id, task_id, workspace_id, state, context, created_at, last_activity_at)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `);

    stmt.run(
      newSession.id,
      newSession.channelId,
      newSession.chatId,
      newSession.userId || null,
      newSession.taskId || null,
      newSession.workspaceId || null,
      newSession.state,
      newSession.context ? JSON.stringify(newSession.context) : null,
      newSession.createdAt,
      newSession.lastActivityAt,
    );

    return newSession;
  }

  update(id: string, updates: Partial<ChannelSession>): void {
    const fields: string[] = [];
    const values: unknown[] = [];

    // Use 'in' check to allow setting fields to null/undefined (clearing them)
    if ("taskId" in updates) {
      fields.push("task_id = ?");
      values.push(updates.taskId ?? null); // Convert undefined to null for SQLite
    }
    if ("workspaceId" in updates) {
      fields.push("workspace_id = ?");
      values.push(updates.workspaceId ?? null);
    }
    if ("state" in updates) {
      fields.push("state = ?");
      values.push(updates.state);
    }
    if ("lastActivityAt" in updates) {
      fields.push("last_activity_at = ?");
      values.push(updates.lastActivityAt);
    }

    // Handle shellEnabled and debugMode by merging into context
    const hasContextUpdate =
      "context" in updates || "shellEnabled" in updates || "debugMode" in updates;
    if (hasContextUpdate) {
      // Load existing session to merge context
      const existing = this.findById(id);
      const existingContext = existing?.context || {};
      const newContext = {
        ...existingContext,
        ...("context" in updates ? updates.context : {}),
        ...("shellEnabled" in updates ? { shellEnabled: updates.shellEnabled } : {}),
        ...("debugMode" in updates ? { debugMode: updates.debugMode } : {}),
      };
      fields.push("context = ?");
      values.push(JSON.stringify(newContext));
    }

    if (fields.length === 0) return;

    values.push(id);
    const stmt = this.db.prepare(`UPDATE channel_sessions SET ${fields.join(", ")} WHERE id = ?`);
    stmt.run(...values);
  }

  findById(id: string): ChannelSession | undefined {
    const stmt = this.db.prepare("SELECT * FROM channel_sessions WHERE id = ?");
    const row = stmt.get(id) as Record<string, unknown> | undefined;
    return row ? this.mapRowToSession(row) : undefined;
  }

  findByChatId(channelId: string, chatId: string): ChannelSession | undefined {
    const stmt = this.db.prepare(
      "SELECT * FROM channel_sessions WHERE channel_id = ? AND chat_id = ? ORDER BY last_activity_at DESC LIMIT 1",
    );
    const row = stmt.get(channelId, chatId) as Record<string, unknown> | undefined;
    return row ? this.mapRowToSession(row) : undefined;
  }

  findByTaskId(taskId: string): ChannelSession | undefined {
    const stmt = this.db.prepare("SELECT * FROM channel_sessions WHERE task_id = ?");
    const row = stmt.get(taskId) as Record<string, unknown> | undefined;
    return row ? this.mapRowToSession(row) : undefined;
  }

  findActiveByChannelId(channelId: string): ChannelSession[] {
    const stmt = this.db.prepare(
      "SELECT * FROM channel_sessions WHERE channel_id = ? AND state != 'idle' ORDER BY last_activity_at DESC",
    );
    const rows = stmt.all(channelId) as Record<string, unknown>[];
    return rows.map((row) => this.mapRowToSession(row));
  }

  deleteIdleOlderThan(cutoffMs: number): number {
    const stmt = this.db.prepare(
      "DELETE FROM channel_sessions WHERE state = 'idle' AND COALESCE(last_activity_at, created_at) < ?",
    );
    const result = stmt.run(cutoffMs);
    return Number(result.changes || 0);
  }

  deleteByChannelId(channelId: string): void {
    const stmt = this.db.prepare("DELETE FROM channel_sessions WHERE channel_id = ?");
    stmt.run(channelId);
  }

  private mapRowToSession(row: Record<string, unknown>): ChannelSession {
    const context = row.context
      ? safeJsonParse(row.context as string, {} as Record<string, unknown>, "session.context")
      : undefined;
    // Extract shellEnabled and debugMode from context
    const shellEnabled = context?.shellEnabled as boolean | undefined;
    const debugMode = context?.debugMode as boolean | undefined;
    return {
      id: row.id as string,
      channelId: row.channel_id as string,
      chatId: row.chat_id as string,
      userId: (row.user_id as string) || undefined,
      taskId: (row.task_id as string) || undefined,
      workspaceId: (row.workspace_id as string) || undefined,
      state: row.state as "idle" | "active" | "waiting_approval",
      context,
      shellEnabled,
      debugMode,
      createdAt: row.created_at as number,
      lastActivityAt: row.last_activity_at as number,
    };
  }
}

export class ChannelSpecializationRepository {
  constructor(private db: Database.Database) {}

  upsert(request: CreateChannelSpecializationRequest): ChannelSpecialization {
    const existing = this.findByScope({
      channelId: request.channelId,
      chatId: request.chatId,
      threadId: request.threadId,
    });
    if (!existing) return this.create(request);
    const update: UpdateChannelSpecializationRequest = {
      id: existing.id,
      chatId: request.chatId ?? null,
      threadId: request.threadId ?? null,
      name: request.name ?? null,
      workspaceId: request.workspaceId ?? null,
      agentRoleId: request.agentRoleId ?? null,
      systemGuidance: request.systemGuidance ?? null,
      toolRestrictions: request.toolRestrictions,
    };
    if (request.allowSharedContextMemory !== undefined) {
      update.allowSharedContextMemory = request.allowSharedContextMemory;
    }
    if (request.enabled !== undefined) {
      update.enabled = request.enabled;
    }
    return this.update(update) || existing;
  }

  create(request: CreateChannelSpecializationRequest): ChannelSpecialization {
    const now = Date.now();
    const specialization: ChannelSpecialization = {
      id: uuidv4(),
      channelId: request.channelId,
      chatId: this.cleanOptionalString(request.chatId),
      threadId: this.cleanOptionalString(request.threadId),
      name: this.cleanOptionalString(request.name),
      workspaceId: this.cleanOptionalString(request.workspaceId),
      agentRoleId: this.cleanOptionalString(request.agentRoleId),
      systemGuidance: this.cleanOptionalString(request.systemGuidance),
      toolRestrictions: this.cleanToolRestrictions(request.toolRestrictions),
      allowSharedContextMemory: request.allowSharedContextMemory === true,
      enabled: request.enabled !== false,
      createdAt: now,
      updatedAt: now,
    };

    this.db
      .prepare(
        `INSERT INTO channel_specializations (
          id, channel_id, chat_id, thread_id, name, workspace_id, agent_role_id,
          system_guidance, tool_restrictions, allow_shared_context_memory,
          enabled, created_at, updated_at
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      )
      .run(
        specialization.id,
        specialization.channelId,
        specialization.chatId ?? null,
        specialization.threadId ?? null,
        specialization.name ?? null,
        specialization.workspaceId ?? null,
        specialization.agentRoleId ?? null,
        specialization.systemGuidance ?? null,
        JSON.stringify(specialization.toolRestrictions || []),
        specialization.allowSharedContextMemory ? 1 : 0,
        specialization.enabled ? 1 : 0,
        specialization.createdAt,
        specialization.updatedAt,
      );

    return specialization;
  }

  update(request: UpdateChannelSpecializationRequest): ChannelSpecialization | undefined {
    const existing = this.findById(request.id);
    if (!existing) return undefined;

    const fields: string[] = [];
    const values: unknown[] = [];
    const push = (column: string, value: unknown) => {
      fields.push(`${column} = ?`);
      values.push(value);
    };

    if ("chatId" in request) push("chat_id", this.cleanOptionalString(request.chatId) ?? null);
    if ("threadId" in request) push("thread_id", this.cleanOptionalString(request.threadId) ?? null);
    if ("name" in request) push("name", this.cleanOptionalString(request.name) ?? null);
    if ("workspaceId" in request)
      push("workspace_id", this.cleanOptionalString(request.workspaceId) ?? null);
    if ("agentRoleId" in request)
      push("agent_role_id", this.cleanOptionalString(request.agentRoleId) ?? null);
    if ("systemGuidance" in request)
      push("system_guidance", this.cleanOptionalString(request.systemGuidance) ?? null);
    if ("toolRestrictions" in request)
      push("tool_restrictions", JSON.stringify(this.cleanToolRestrictions(request.toolRestrictions)));
    if ("allowSharedContextMemory" in request)
      push("allow_shared_context_memory", request.allowSharedContextMemory ? 1 : 0);
    if ("enabled" in request) push("enabled", request.enabled ? 1 : 0);

    if (fields.length === 0) return existing;

    push("updated_at", Date.now());
    values.push(request.id);
    this.db
      .prepare(`UPDATE channel_specializations SET ${fields.join(", ")} WHERE id = ?`)
      .run(...values);
    return this.findById(request.id);
  }

  delete(id: string): boolean {
    const result = this.db.prepare("DELETE FROM channel_specializations WHERE id = ?").run(id);
    return Number(result.changes || 0) > 0;
  }

  findById(id: string): ChannelSpecialization | undefined {
    const row = this.db
      .prepare("SELECT * FROM channel_specializations WHERE id = ?")
      .get(id) as Record<string, unknown> | undefined;
    return row ? this.mapRow(row) : undefined;
  }

  findByScope(input: {
    channelId: string;
    chatId?: string | null;
    threadId?: string | null;
  }): ChannelSpecialization | undefined {
    const row = this.db
      .prepare(
        `SELECT * FROM channel_specializations
         WHERE channel_id = ?
           AND COALESCE(chat_id, '') = ?
           AND COALESCE(thread_id, '') = ?
         LIMIT 1`,
      )
      .get(
        input.channelId,
        this.cleanOptionalString(input.chatId) ?? "",
        this.cleanOptionalString(input.threadId) ?? "",
      ) as Record<string, unknown> | undefined;
    return row ? this.mapRow(row) : undefined;
  }

  listByChannel(channelId: string): ChannelSpecialization[] {
    const rows = this.db
      .prepare(
        `SELECT * FROM channel_specializations
         WHERE channel_id = ?
         ORDER BY COALESCE(chat_id, '') ASC, COALESCE(thread_id, '') ASC, updated_at DESC`,
      )
      .all(channelId) as Record<string, unknown>[];
    return rows.map((row) => this.mapRow(row));
  }

  resolve(input: {
    channelId: string;
    chatId?: string | null;
    threadId?: string | null;
  }): ChannelSpecialization | undefined {
    const chatId = this.cleanOptionalString(input.chatId);
    const threadId = this.cleanOptionalString(input.threadId);
    const rows = this.db
      .prepare(
        `SELECT * FROM channel_specializations
         WHERE channel_id = ?
           AND enabled = 1
           AND (
             (chat_id IS NULL AND thread_id IS NULL)
             OR (? IS NOT NULL AND chat_id = ? AND thread_id IS NULL)
             OR (? IS NOT NULL AND ? IS NOT NULL AND chat_id = ? AND thread_id = ?)
           )
         ORDER BY
           CASE
             WHEN chat_id = ? AND thread_id = ? THEN 3
             WHEN chat_id = ? AND thread_id IS NULL THEN 2
             WHEN chat_id IS NULL AND thread_id IS NULL THEN 1
             ELSE 0
           END DESC,
           updated_at DESC
         LIMIT 1`,
      )
      .all(
        input.channelId,
        chatId ?? null,
        chatId ?? null,
        chatId ?? null,
        threadId ?? null,
        chatId ?? null,
        threadId ?? null,
        chatId ?? null,
        threadId ?? null,
        chatId ?? null,
      ) as Record<string, unknown>[];
    return rows[0] ? this.mapRow(rows[0]) : undefined;
  }

  private cleanOptionalString(value: unknown): string | undefined {
    return typeof value === "string" && value.trim().length > 0 ? value.trim() : undefined;
  }

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

  private mapRow(row: Record<string, unknown>): ChannelSpecialization {
    return {
      id: row.id as string,
      channelId: row.channel_id as string,
      chatId: (row.chat_id as string) || undefined,
      threadId: (row.thread_id as string) || undefined,
      name: (row.name as string) || undefined,
      workspaceId: (row.workspace_id as string) || undefined,
      agentRoleId: (row.agent_role_id as string) || undefined,
      systemGuidance: (row.system_guidance as string) || undefined,
      toolRestrictions: safeJsonParse(
        (row.tool_restrictions as string) || "[]",
        [] as string[],
        "channelSpecialization.toolRestrictions",
      ).filter((item): item is string => typeof item === "string"),
      allowSharedContextMemory: row.allow_shared_context_memory === 1,
      enabled: row.enabled === 1,
      createdAt: row.created_at as number,
      updatedAt: row.updated_at as number,
    };
  }
}

export class ChannelMessageRepository {
  constructor(private db: Database.Database) {}

  create(message: Omit<ChannelMessage, "id">): ChannelMessage {
    const newMessage: ChannelMessage = {
      ...message,
      id: uuidv4(),
    };

    const stmt = this.db.prepare(`
      INSERT INTO channel_messages (id, channel_id, session_id, channel_message_id, chat_id, user_id, direction, content, attachments, timestamp)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `);

    stmt.run(
      newMessage.id,
      newMessage.channelId,
      newMessage.sessionId || null,
      newMessage.channelMessageId,
      newMessage.chatId,
      newMessage.userId || null,
      newMessage.direction,
      newMessage.content,
      newMessage.attachments ? JSON.stringify(newMessage.attachments) : null,
      newMessage.timestamp,
    );

    return newMessage;
  }

  findBySessionId(sessionId: string, limit = 50): ChannelMessage[] {
    const stmt = this.db.prepare(
      "SELECT * FROM channel_messages WHERE session_id = ? ORDER BY timestamp DESC LIMIT ?",
    );
    const rows = stmt.all(sessionId, limit) as Record<string, unknown>[];
    return rows.map((row) => this.mapRowToMessage(row)).reverse();
  }

  findByChatId(channelId: string, chatId: string, limit = 50): ChannelMessage[] {
    const stmt = this.db.prepare(
      "SELECT * FROM channel_messages WHERE channel_id = ? AND chat_id = ? ORDER BY timestamp DESC LIMIT ?",
    );
    const rows = stmt.all(channelId, chatId, limit) as Record<string, unknown>[];
    return rows.map((row) => this.mapRowToMessage(row)).reverse();
  }

  deleteByChannelId(channelId: string): void {
    const stmt = this.db.prepare("DELETE FROM channel_messages WHERE channel_id = ?");
    stmt.run(channelId);
  }

  /**
   * Get distinct chat IDs for a channel, ordered by most recent message.
   */
  getDistinctChatIds(
    channelId: string,
    limit = 50,
  ): Array<{ chatId: string; lastTimestamp: number }> {
    const stmt = this.db.prepare(`
      SELECT chat_id, MAX(timestamp) as last_ts
      FROM channel_messages
      WHERE channel_id = ?
      GROUP BY chat_id
      ORDER BY last_ts DESC
      LIMIT ?
    `);
    const rows = stmt.all(channelId, limit) as Array<{ chat_id: string; last_ts: number }>;
    return rows.map((row) => ({ chatId: row.chat_id, lastTimestamp: row.last_ts }));
  }

  private mapRowToMessage(row: Record<string, unknown>): ChannelMessage {
    const directionRaw = String(row.direction ?? "").trim();
    const direction: ChannelMessage["direction"] =
      directionRaw === "outgoing" || directionRaw === "outgoing_user" ? directionRaw : "incoming";

    return {
      id: row.id as string,
      channelId: row.channel_id as string,
      sessionId: (row.session_id as string) || undefined,
      channelMessageId: row.channel_message_id as string,
      chatId: row.chat_id as string,
      userId: (row.user_id as string) || undefined,
      direction,
      content: row.content as string,
      attachments: row.attachments
        ? safeJsonParse(row.attachments as string, undefined, "message.attachments")
        : undefined,
      timestamp: row.timestamp as number,
    };
  }
}

// ============================================================
// Gateway Infrastructure Repositories
// ============================================================

export interface QueuedMessage {
  id: string;
  channelType: string;
  chatId: string;
  message: Record<string, unknown>;
  priority: number;
  status: "pending" | "processing" | "sent" | "failed";
  attempts: number;
  maxAttempts: number;
  lastAttemptAt?: number;
  error?: string;
  createdAt: number;
  scheduledAt?: number;
}

export interface ScheduledMessage {
  id: string;
  channelType: string;
  chatId: string;
  message: Record<string, unknown>;
  scheduledAt: number;
  status: "pending" | "sent" | "failed" | "cancelled";
  sentMessageId?: string;
  error?: string;
  createdAt: number;
}

export interface DeliveryRecord {
  id: string;
  channelType: string;
  chatId: string;
  messageId: string;
  status: "pending" | "sent" | "delivered" | "read" | "failed";
  sentAt?: number;
  deliveredAt?: number;
  readAt?: number;
  error?: string;
  createdAt: number;
}

export interface RateLimitRecord {
  id: string;
  channelType: string;
  userId: string;
  messageCount: number;
  windowStart: number;
  isLimited: boolean;
  limitExpiresAt?: number;
}

export interface AuditLogEntry {
  id: string;
  timestamp: number;
  action: string;
  channelType?: string;
  userId?: string;
  chatId?: string;
  details?: Record<string, unknown>;
  severity: "debug" | "info" | "warn" | "error";
}

export class MessageQueueRepository {
  constructor(private db: Database.Database) {}

  enqueue(item: Omit<QueuedMessage, "id" | "createdAt" | "attempts" | "status">): QueuedMessage {
    const newItem: QueuedMessage = {
      ...item,
      id: uuidv4(),
      status: "pending",
      attempts: 0,
      createdAt: Date.now(),
    };

    const stmt = this.db.prepare(`
      INSERT INTO message_queue (id, channel_type, chat_id, message, priority, status, attempts, max_attempts, last_attempt_at, error, created_at, scheduled_at)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `);

    stmt.run(
      newItem.id,
      newItem.channelType,
      newItem.chatId,
      JSON.stringify(newItem.message),
      newItem.priority,
      newItem.status,
      newItem.attempts,
      newItem.maxAttempts,
      newItem.lastAttemptAt || null,
      newItem.error || null,
      newItem.createdAt,
      newItem.scheduledAt || null,
    );

    return newItem;
  }

  update(id: string, updates: Partial<QueuedMessage>): void {
    const fields: string[] = [];
    const values: unknown[] = [];

    if (updates.status !== undefined) {
      fields.push("status = ?");
      values.push(updates.status);
    }
    if (updates.attempts !== undefined) {
      fields.push("attempts = ?");
      values.push(updates.attempts);
    }
    if (updates.lastAttemptAt !== undefined) {
      fields.push("last_attempt_at = ?");
      values.push(updates.lastAttemptAt);
    }
    if (updates.error !== undefined) {
      fields.push("error = ?");
      values.push(updates.error);
    }

    if (fields.length === 0) return;

    values.push(id);
    const stmt = this.db.prepare(`UPDATE message_queue SET ${fields.join(", ")} WHERE id = ?`);
    stmt.run(...values);
  }

  findPending(limit = 50): QueuedMessage[] {
    const now = Date.now();
    const stmt = this.db.prepare(`
      SELECT * FROM message_queue
      WHERE status = 'pending' AND (scheduled_at IS NULL OR scheduled_at <= ?)
      ORDER BY priority DESC, created_at ASC
      LIMIT ?
    `);
    const rows = stmt.all(now, limit) as Record<string, unknown>[];
    return rows.map((row) => this.mapRowToItem(row));
  }

  findById(id: string): QueuedMessage | undefined {
    const stmt = this.db.prepare("SELECT * FROM message_queue WHERE id = ?");
    const row = stmt.get(id) as Record<string, unknown> | undefined;
    return row ? this.mapRowToItem(row) : undefined;
  }

  delete(id: string): void {
    const stmt = this.db.prepare("DELETE FROM message_queue WHERE id = ?");
    stmt.run(id);
  }

  deleteOld(olderThanMs: number): number {
    const cutoff = Date.now() - olderThanMs;
    const stmt = this.db.prepare(
      "DELETE FROM message_queue WHERE status IN ('sent', 'failed') AND created_at < ?",
    );
    const result = stmt.run(cutoff);
    return result.changes;
  }

  private mapRowToItem(row: Record<string, unknown>): QueuedMessage {
    return {
      id: row.id as string,
      channelType: row.channel_type as string,
      chatId: row.chat_id as string,
      message: safeJsonParse(row.message as string, {}, "queue.message"),
      priority: row.priority as number,
      status: row.status as QueuedMessage["status"],
      attempts: row.attempts as number,
      maxAttempts: row.max_attempts as number,
      lastAttemptAt: (row.last_attempt_at as number) || undefined,
      error: (row.error as string) || undefined,
      createdAt: row.created_at as number,
      scheduledAt: (row.scheduled_at as number) || undefined,
    };
  }
}

export class ScheduledMessageRepository {
  constructor(private db: Database.Database) {}

  create(item: Omit<ScheduledMessage, "id" | "createdAt" | "status">): ScheduledMessage {
    const newItem: ScheduledMessage = {
      ...item,
      id: uuidv4(),
      status: "pending",
      createdAt: Date.now(),
    };

    const stmt = this.db.prepare(`
      INSERT INTO scheduled_messages (id, channel_type, chat_id, message, scheduled_at, status, sent_message_id, error, created_at)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
    `);

    stmt.run(
      newItem.id,
      newItem.channelType,
      newItem.chatId,
      JSON.stringify(newItem.message),
      newItem.scheduledAt,
      newItem.status,
      newItem.sentMessageId || null,
      newItem.error || null,
      newItem.createdAt,
    );

    return newItem;
  }

  update(id: string, updates: Partial<ScheduledMessage>): void {
    const fields: string[] = [];
    const values: unknown[] = [];

    if (updates.status !== undefined) {
      fields.push("status = ?");
      values.push(updates.status);
    }
    if (updates.sentMessageId !== undefined) {
      fields.push("sent_message_id = ?");
      values.push(updates.sentMessageId);
    }
    if (updates.error !== undefined) {
      fields.push("error = ?");
      values.push(updates.error);
    }
    if (updates.scheduledAt !== undefined) {
      fields.push("scheduled_at = ?");
      values.push(updates.scheduledAt);
    }

    if (fields.length === 0) return;

    values.push(id);
    const stmt = this.db.prepare(`UPDATE scheduled_messages SET ${fields.join(", ")} WHERE id = ?`);
    stmt.run(...values);
  }

  findDue(limit = 50): ScheduledMessage[] {
    const now = Date.now();
    const stmt = this.db.prepare(`
      SELECT * FROM scheduled_messages
      WHERE status = 'pending' AND scheduled_at <= ?
      ORDER BY scheduled_at ASC
      LIMIT ?
    `);
    const rows = stmt.all(now, limit) as Record<string, unknown>[];
    return rows.map((row) => this.mapRowToItem(row));
  }

  findById(id: string): ScheduledMessage | undefined {
    const stmt = this.db.prepare("SELECT * FROM scheduled_messages WHERE id = ?");
    const row = stmt.get(id) as Record<string, unknown> | undefined;
    return row ? this.mapRowToItem(row) : undefined;
  }

  findByChatId(channelType: string, chatId: string): ScheduledMessage[] {
    const stmt = this.db.prepare(`
      SELECT * FROM scheduled_messages
      WHERE channel_type = ? AND chat_id = ? AND status = 'pending'
      ORDER BY scheduled_at ASC
    `);
    const rows = stmt.all(channelType, chatId) as Record<string, unknown>[];
    return rows.map((row) => this.mapRowToItem(row));
  }

  cancel(id: string): void {
    const stmt = this.db.prepare(
      "UPDATE scheduled_messages SET status = 'cancelled' WHERE id = ? AND status = 'pending'",
    );
    stmt.run(id);
  }

  delete(id: string): void {
    const stmt = this.db.prepare("DELETE FROM scheduled_messages WHERE id = ?");
    stmt.run(id);
  }

  private mapRowToItem(row: Record<string, unknown>): ScheduledMessage {
    return {
      id: row.id as string,
      channelType: row.channel_type as string,
      chatId: row.chat_id as string,
      message: safeJsonParse(row.message as string, {}, "scheduled.message"),
      scheduledAt: row.scheduled_at as number,
      status: row.status as ScheduledMessage["status"],
      sentMessageId: (row.sent_message_id as string) || undefined,
      error: (row.error as string) || undefined,
      createdAt: row.created_at as number,
    };
  }
}

export class DeliveryTrackingRepository {
  constructor(private db: Database.Database) {}

  create(item: Omit<DeliveryRecord, "id" | "createdAt">): DeliveryRecord {
    const newItem: DeliveryRecord = {
      ...item,
      id: uuidv4(),
      createdAt: Date.now(),
    };

    const stmt = this.db.prepare(`
      INSERT INTO delivery_tracking (id, channel_type, chat_id, message_id, status, sent_at, delivered_at, read_at, error, created_at)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `);

    stmt.run(
      newItem.id,
      newItem.channelType,
      newItem.chatId,
      newItem.messageId,
      newItem.status,
      newItem.sentAt || null,
      newItem.deliveredAt || null,
      newItem.readAt || null,
      newItem.error || null,
      newItem.createdAt,
    );

    return newItem;
  }

  update(id: string, updates: Partial<DeliveryRecord>): void {
    const fields: string[] = [];
    const values: unknown[] = [];

    if (updates.status !== undefined) {
      fields.push("status = ?");
      values.push(updates.status);
    }
    if (updates.sentAt !== undefined) {
      fields.push("sent_at = ?");
      values.push(updates.sentAt);
    }
    if (updates.deliveredAt !== undefined) {
      fields.push("delivered_at = ?");
      values.push(updates.deliveredAt);
    }
    if (updates.readAt !== undefined) {
      fields.push("read_at = ?");
      values.push(updates.readAt);
    }
    if (updates.error !== undefined) {
      fields.push("error = ?");
      values.push(updates.error);
    }

    if (fields.length === 0) return;

    values.push(id);
    const stmt = this.db.prepare(`UPDATE delivery_tracking SET ${fields.join(", ")} WHERE id = ?`);
    stmt.run(...values);
  }

  findByMessageId(messageId: string): DeliveryRecord | undefined {
    const stmt = this.db.prepare("SELECT * FROM delivery_tracking WHERE message_id = ?");
    const row = stmt.get(messageId) as Record<string, unknown> | undefined;
    return row ? this.mapRowToItem(row) : undefined;
  }

  findByChatId(channelType: string, chatId: string, limit = 50): DeliveryRecord[] {
    const stmt = this.db.prepare(`
      SELECT * FROM delivery_tracking
      WHERE channel_type = ? AND chat_id = ?
      ORDER BY created_at DESC
      LIMIT ?
    `);
    const rows = stmt.all(channelType, chatId, limit) as Record<string, unknown>[];
    return rows.map((row) => this.mapRowToItem(row));
  }

  deleteOld(olderThanMs: number): number {
    const cutoff = Date.now() - olderThanMs;
    const stmt = this.db.prepare("DELETE FROM delivery_tracking WHERE created_at < ?");
    const result = stmt.run(cutoff);
    return result.changes;
  }

  private mapRowToItem(row: Record<string, unknown>): DeliveryRecord {
    return {
      id: row.id as string,
      channelType: row.channel_type as string,
      chatId: row.chat_id as string,
      messageId: row.message_id as string,
      status: row.status as DeliveryRecord["status"],
      sentAt: (row.sent_at as number) || undefined,
      deliveredAt: (row.delivered_at as number) || undefined,
      readAt: (row.read_at as number) || undefined,
      error: (row.error as string) || undefined,
      createdAt: row.created_at as number,
    };
  }
}

export class RateLimitRepository {
  constructor(private db: Database.Database) {}

  getOrCreate(channelType: string, userId: string): RateLimitRecord {
    const stmt = this.db.prepare(
      "SELECT * FROM rate_limits WHERE channel_type = ? AND user_id = ?",
    );
    const row = stmt.get(channelType, userId) as Record<string, unknown> | undefined;

    if (row) {
      return this.mapRowToItem(row);
    }

    // Create new record
    const newItem: RateLimitRecord = {
      id: uuidv4(),
      channelType,
      userId,
      messageCount: 0,
      windowStart: Date.now(),
      isLimited: false,
    };

    const insertStmt = this.db.prepare(`
      INSERT INTO rate_limits (id, channel_type, user_id, message_count, window_start, is_limited, limit_expires_at)
      VALUES (?, ?, ?, ?, ?, ?, ?)
    `);

    insertStmt.run(
      newItem.id,
      newItem.channelType,
      newItem.userId,
      newItem.messageCount,
      newItem.windowStart,
      newItem.isLimited ? 1 : 0,
      newItem.limitExpiresAt || null,
    );

    return newItem;
  }

  update(channelType: string, userId: string, updates: Partial<RateLimitRecord>): void {
    const fields: string[] = [];
    const values: unknown[] = [];

    if (updates.messageCount !== undefined) {
      fields.push("message_count = ?");
      values.push(updates.messageCount);
    }
    if (updates.windowStart !== undefined) {
      fields.push("window_start = ?");
      values.push(updates.windowStart);
    }
    if (updates.isLimited !== undefined) {
      fields.push("is_limited = ?");
      values.push(updates.isLimited ? 1 : 0);
    }
    if (updates.limitExpiresAt !== undefined) {
      fields.push("limit_expires_at = ?");
      values.push(updates.limitExpiresAt);
    }

    if (fields.length === 0) return;

    values.push(channelType, userId);
    const stmt = this.db.prepare(
      `UPDATE rate_limits SET ${fields.join(", ")} WHERE channel_type = ? AND user_id = ?`,
    );
    stmt.run(...values);
  }

  resetWindow(channelType: string, userId: string): void {
    const stmt = this.db.prepare(`
      UPDATE rate_limits
      SET message_count = 0, window_start = ?, is_limited = 0, limit_expires_at = NULL
      WHERE channel_type = ? AND user_id = ?
    `);
    stmt.run(Date.now(), channelType, userId);
  }

  private mapRowToItem(row: Record<string, unknown>): RateLimitRecord {
    return {
      id: row.id as string,
      channelType: row.channel_type as string,
      userId: row.user_id as string,
      messageCount: row.message_count as number,
      windowStart: row.window_start as number,
      isLimited: row.is_limited === 1,
      limitExpiresAt: (row.limit_expires_at as number) || undefined,
    };
  }
}

export class AuditLogRepository {
  constructor(private db: Database.Database) {}

  log(entry: Omit<AuditLogEntry, "id" | "timestamp">): AuditLogEntry {
    const newEntry: AuditLogEntry = {
      ...entry,
      id: uuidv4(),
      timestamp: Date.now(),
    };

    const stmt = this.db.prepare(`
      INSERT INTO audit_log (id, timestamp, action, channel_type, user_id, chat_id, details, severity)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?)
    `);

    stmt.run(
      newEntry.id,
      newEntry.timestamp,
      newEntry.action,
      newEntry.channelType || null,
      newEntry.userId || null,
      newEntry.chatId || null,
      newEntry.details ? JSON.stringify(newEntry.details) : null,
      newEntry.severity,
    );

    return newEntry;
  }

  find(options: {
    action?: string;
    channelType?: string;
    userId?: string;
    chatId?: string;
    fromTimestamp?: number;
    toTimestamp?: number;
    severity?: AuditLogEntry["severity"];
    limit?: number;
    offset?: number;
  }): AuditLogEntry[] {
    const conditions: string[] = [];
    const values: unknown[] = [];

    if (options.action) {
      conditions.push("action = ?");
      values.push(options.action);
    }
    if (options.channelType) {
      conditions.push("channel_type = ?");
      values.push(options.channelType);
    }
    if (options.userId) {
      conditions.push("user_id = ?");
      values.push(options.userId);
    }
    if (options.chatId) {
      conditions.push("chat_id = ?");
      values.push(options.chatId);
    }
    if (options.fromTimestamp) {
      conditions.push("timestamp >= ?");
      values.push(options.fromTimestamp);
    }
    if (options.toTimestamp) {
      conditions.push("timestamp <= ?");
      values.push(options.toTimestamp);
    }
    if (options.severity) {
      conditions.push("severity = ?");
      values.push(options.severity);
    }

    const whereClause = conditions.length > 0 ? `WHERE ${conditions.join(" AND ")}` : "";
    const limit = options.limit || 100;
    const offset = options.offset || 0;

    const stmt = this.db.prepare(`
      SELECT * FROM audit_log
      ${whereClause}
      ORDER BY timestamp DESC
      LIMIT ? OFFSET ?
    `);

    values.push(limit, offset);
    const rows = stmt.all(...values) as Record<string, unknown>[];
    return rows.map((row) => this.mapRowToEntry(row));
  }

  deleteOld(olderThanMs: number): number {
    const cutoff = Date.now() - olderThanMs;
    const stmt = this.db.prepare("DELETE FROM audit_log WHERE timestamp < ?");
    const result = stmt.run(cutoff);
    return result.changes;
  }

  private mapRowToEntry(row: Record<string, unknown>): AuditLogEntry {
    return {
      id: row.id as string,
      timestamp: row.timestamp as number,
      action: row.action as string,
      channelType: (row.channel_type as string) || undefined,
      userId: (row.user_id as string) || undefined,
      chatId: (row.chat_id as string) || undefined,
      details: row.details
        ? safeJsonParse(row.details as string, undefined, "audit.details")
        : undefined,
      severity: row.severity as AuditLogEntry["severity"],
    };
  }
}

// ============================================================
// Memory System Repositories
// ============================================================

export type MemoryType =
  | "observation"
  | "decision"
  | "error"
  | "insight"
  | "screen_context"
  | "summary"
  | "preference"
  | "constraint"
  | "timing_preference"
  | "workflow_pattern"
  | "correction_rule";
export type PrivacyMode = "normal" | "strict" | "disabled";
export type TimePeriod = "hourly" | "daily" | "weekly";

export interface Memory {
  id: string;
  workspaceId: string;
  taskId?: string;
  type: MemoryType;
  content: string;
  summary?: string;
  tokens: number;
  isCompressed: boolean;
  isPrivate: boolean;
  createdAt: number;
  updatedAt: number;
}

export interface CuratedMemoryEntryRecord extends CuratedMemoryEntry {}

export interface MemorySummary {
  id: string;
  workspaceId: string;
  timePeriod: TimePeriod;
  periodStart: number;
  periodEnd: number;
  summary: string;
  memoryIds: string[];
  tokens: number;
  createdAt: number;
}

export interface MemorySettings {
  workspaceId: string;
  enabled: boolean;
  autoCapture: boolean;
  compressionEnabled: boolean;
  retentionDays: number;
  maxStorageMb: number;
  privacyMode: PrivacyMode;
  excludedPatterns?: string[];
}

export type PendingMemoryWriteStatus = "pending" | "applying" | "applied" | "rejected" | "failed";

export interface PendingMemoryWrite {
  id: string;
  workspaceId: string;
  taskId?: string;
  target: string;
  action: string;
  origin: string;
  summary: string;
  payload: Record<string, unknown>;
  oldValue?: string;
  proposedValue?: string;
  reason?: string;
  evidence: Array<Record<string, unknown>>;
  riskScore: number;
  status: PendingMemoryWriteStatus;
  createdAt: number;
  reviewedAt?: number;
  reviewedBy?: string;
  resolution?: string;
}

export type MemorySearchResult =
  | {
      id: string;
      snippet: string;
      type: MemoryType;
      relevanceScore: number;
      createdAt: number;
      taskId?: string;
      /** Origin of this search result (database memory vs markdown kit index). */
      source: "db";
    }
  | {
      id: string;
      snippet: string;
      type: MemoryType;
      relevanceScore: number;
      createdAt: number;
      taskId?: string;
      /** Origin of this search result (database memory vs markdown kit index). */
      source: "markdown";
      /** File path for markdown-backed results (workspace-relative). */
      path: string;
      /** Start line (1-based) for markdown-backed results. */
      startLine: number;
      /** End line (1-based) for markdown-backed results. */
      endLine: number;
    };

export interface MemoryEmbedding {
  memoryId: string;
  workspaceId: string;
  embedding: number[];
  updatedAt: number;
}

export interface MemoryTimelineEntry {
  id: string;
  content: string;
  type: MemoryType;
  createdAt: number;
  taskId?: string;
}

export interface MemoryStats {
  count: number;
  totalTokens: number;
  compressedCount: number;
  compressionRatio: number;
}

// Imported memories can optionally carry a lightweight control header on the first line.
const IMPORTED_PROMPT_RECALL_IGNORE_MARKER = "[cowork:prompt_recall=ignore]";
const buildImportedMemoryFilterSql = (contentExpr: string): string =>
  `(${contentExpr} LIKE '[Imported from %' OR ${contentExpr} LIKE '${IMPORTED_PROMPT_RECALL_IGNORE_MARKER}%[Imported from %')`;

export class MemoryRepository {
  constructor(private db: Database.Database) {}

  private static readonly MEMORY_FTS_RAW_MAX_CHARS = 160;
  private static readonly MEMORY_FTS_RAW_MAX_TOKENS = 12;
  private static readonly MEMORY_FTS_SLOW_QUERY_MS = 250;
  private static readonly PROMPT_RECALL_FTS_MAX_TOKENS = 5;

  // Keep this small and local: we want memory search to be robust against
  // natural-language queries (punctuation, filler words) without pulling in
  // other modules and risking circular deps.
  private static readonly MEMORY_SEARCH_STOP_WORDS = new Set([
    "a",
    "an",
    "the",
    "and",
    "or",
    "but",
    "if",
    "then",
    "else",
    "is",
    "are",
    "was",
    "were",
    "be",
    "been",
    "being",
    "to",
    "of",
    "in",
    "on",
    "for",
    "with",
    "by",
    "as",
    "at",
    "from",
    "into",
    "about",
    "that",
    "this",
    "it",
    "its",
    "we",
    "you",
    "they",
    "i",
    "he",
    "she",
    "them",
    "our",
    "your",
    "my",
    "me",
    "us",
    "do",
    "does",
    "did",
    "done",
    "can",
    "could",
    "should",
    "would",
    "will",
    "shall",
    "may",
    "might",
    "not",
    "no",
    "yes",
    "please",
    "help",
  ]);

  create(memory: Omit<Memory, "id" | "createdAt" | "updatedAt">): Memory {
    const now = Date.now();
    const newMemory: Memory = {
      ...memory,
      id: uuidv4(),
      createdAt: now,
      updatedAt: now,
    };

    const stmt = this.db.prepare(`
      INSERT INTO memories (id, workspace_id, task_id, type, content, summary, tokens, is_compressed, is_private, created_at, updated_at)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `);

    stmt.run(
      newMemory.id,
      newMemory.workspaceId,
      newMemory.taskId || null,
      newMemory.type,
      newMemory.content,
      newMemory.summary || null,
      newMemory.tokens,
      newMemory.isCompressed ? 1 : 0,
      newMemory.isPrivate ? 1 : 0,
      newMemory.createdAt,
      newMemory.updatedAt,
    );

    return newMemory;
  }

  update(
    id: string,
    updates: Partial<Pick<Memory, "summary" | "tokens" | "isCompressed" | "content">>,
  ): void {
    const fields: string[] = [];
    const values: unknown[] = [];

    if (updates.summary !== undefined) {
      fields.push("summary = ?");
      values.push(updates.summary);
    }
    if (updates.tokens !== undefined) {
      fields.push("tokens = ?");
      values.push(updates.tokens);
    }
    if (updates.isCompressed !== undefined) {
      fields.push("is_compressed = ?");
      values.push(updates.isCompressed ? 1 : 0);
    }
    if (updates.content !== undefined) {
      fields.push("content = ?");
      values.push(updates.content);
    }

    if (fields.length === 0) return;

    fields.push("updated_at = ?");
    values.push(Date.now());
    values.push(id);

    const stmt = this.db.prepare(`UPDATE memories SET ${fields.join(", ")} WHERE id = ?`);
    stmt.run(...values);
  }

  findById(id: string): Memory | undefined {
    const stmt = this.db.prepare("SELECT * FROM memories WHERE id = ?");
    const row = stmt.get(id) as Record<string, unknown> | undefined;
    return row ? this.mapRowToMemory(row) : undefined;
  }

  findByIds(ids: string[]): Memory[] {
    if (ids.length === 0) return [];
    const placeholders = ids.map(() => "?").join(", ");
    const stmt = this.db.prepare(`SELECT * FROM memories WHERE id IN (${placeholders})`);
    const rows = stmt.all(...ids) as Record<string, unknown>[];
    return rows.map((row) => this.mapRowToMemory(row));
  }

  /**
   * Layer 1: Search returns IDs + brief snippets (~50 tokens each)
   * Uses FTS5 for full-text search with relevance ranking
   */
  search(
    workspaceId: string,
    query: string,
    limit = 20,
    includePrivate = false,
  ): MemorySearchResult[] {
    const privacyFilter = includePrivate ? "" : "AND m.is_private = 0";
    try {
      // Try FTS5 search first.
      //
      // FTS5 uses a query language where whitespace implies AND. That is often
      // too strict for natural language prompts (lots of filler words), and
      // punctuation can also produce syntax errors. We therefore:
      // 1) try raw query
      // 2) if empty or error, retry with a relaxed OR query over key tokens
      const stmt = this.db.prepare(`
        SELECT m.id, m.summary, m.content, m.type, m.created_at, m.task_id,
               bm25(memories_fts) as score
        FROM memories_fts f
        JOIN memories m ON f.rowid = m.rowid
        WHERE memories_fts MATCH ? AND m.workspace_id = ? ${privacyFilter}
        ORDER BY score
        LIMIT ?
      `);

      const raw = (query || "").trim();
      if (!raw) return [];
      const tokenized = this.buildRelaxedFtsQuery(raw);
      const tryRaw = this.shouldTryRawFtsQuery(raw);

      const mapRows = (rows: Record<string, unknown>[]) =>
        rows.map((row) => ({
          id: row.id as string,
          snippet: (row.summary as string) || this.truncateToSnippet(row.content as string, 200),
          type: row.type as MemoryType,
          relevanceScore: Math.abs(row.score as number),
          createdAt: row.created_at as number,
          taskId: (row.task_id as string) || undefined,
          source: "db" as const,
        }));

      const ftsM = { workspaceId, limit };
      let rows: Record<string, unknown>[] = [];
      if (tryRaw) {
        try {
          rows = this.runMemoryFtsQuery("local-raw", raw, () =>
            stmt.all(raw, workspaceId, limit),
            ftsM,
          ) as Record<string, unknown>[];
        } catch {
          // Raw query may be invalid FTS syntax; retry below with tokenized query.
          rows = [];
        }
      }

      // If raw query was too strict (common) or failed, retry with relaxed query.
      if (rows.length === 0 && tokenized) {
        try {
          rows = this.runMemoryFtsQuery("local-relaxed", tokenized, () =>
            stmt.all(tokenized, workspaceId, limit),
            ftsM,
          ) as Record<string, unknown>[];
        } catch {
          // Ignore; we'll fall back to LIKE below.
          rows = [];
        }
      }

      if (rows.length > 0) {
        return mapRows(rows);
      }
    } catch {
      // Fall back to LIKE search if FTS5 is not available
      const fallbackPrivacyFilter = includePrivate ? "" : "AND is_private = 0";
      const raw = (query || "").trim();
      const tokens = this.tokenizeSearchQuery(raw);
      const likeTokens = (tokens.length > 0 ? tokens : [raw]).slice(0, 8).filter(Boolean);

      // Build an OR LIKE query over a small token set for recall.
      const clauses: string[] = [];
      const params: unknown[] = [workspaceId];
      for (const token of likeTokens) {
        clauses.push("(content LIKE ? OR summary LIKE ?)");
        const like = `%${token}%`;
        params.push(like, like);
      }

      const where = clauses.length > 0 ? `AND (${clauses.join(" OR ")})` : "";
      const stmt = this.db.prepare(`
        SELECT id, summary, content, type, created_at, task_id
        FROM memories
        WHERE workspace_id = ? ${fallbackPrivacyFilter}
          ${where}
        ORDER BY created_at DESC
        LIMIT ?
      `);

      params.push(limit);
      const rows = stmt.all(...params) as Record<string, unknown>[];
      return rows.map((row) => ({
        id: row.id as string,
        snippet: (row.summary as string) || this.truncateToSnippet(row.content as string, 200),
        type: row.type as MemoryType,
        relevanceScore: 1,
        createdAt: row.created_at as number,
        taskId: (row.task_id as string) || undefined,
        source: "db" as const,
      }));
    }

    return [];
  }

  /**
   * Search imported memories across ALL workspaces.
   * This is intentionally global so sessions from any workspace can retrieve imported history.
   */
  searchImportedGlobal(query: string, limit = 20, includePrivate = false): MemorySearchResult[] {
    const privacyFilter = includePrivate ? "" : "AND m.is_private = 0";
    try {
      const stmt = this.db.prepare(`
        SELECT m.id, m.summary, m.content, m.type, m.created_at, m.task_id,
               bm25(memories_fts) as score
        FROM memories_fts f
        JOIN memories m ON f.rowid = m.rowid
        WHERE memories_fts MATCH ?
          AND ${buildImportedMemoryFilterSql("m.content")}
          ${privacyFilter}
        ORDER BY score
        LIMIT ?
      `);

      const raw = (query || "").trim();
      if (!raw) return [];
      const tokenized = this.buildRelaxedFtsQuery(raw);
      const tryRaw = this.shouldTryRawFtsQuery(raw);

      const ftsM = { limit };
      let rows: Record<string, unknown>[] = [];
      if (tryRaw) {
        try {
          rows = this.runMemoryFtsQuery("imported-raw", raw, () =>
            stmt.all(raw, limit),
            ftsM,
          ) as Record<string, unknown>[];
        } catch {
          rows = [];
        }
      }

      if (rows.length === 0 && tokenized) {
        try {
          rows = this.runMemoryFtsQuery("imported-relaxed", tokenized, () =>
            stmt.all(tokenized, limit),
            ftsM,
          ) as Record<string, unknown>[];
        } catch {
          rows = [];
        }
      }

      if (rows.length > 0) {
        return rows.map((row) => ({
          id: row.id as string,
          snippet: (row.summary as string) || this.truncateToSnippet(row.content as string, 200),
          type: row.type as MemoryType,
          relevanceScore: Math.abs(row.score as number),
          createdAt: row.created_at as number,
          taskId: (row.task_id as string) || undefined,
          source: "db" as const,
        }));
      }
    } catch {
      // ignore and fall back below
    }

    // LIKE fallback (global)
    const raw = (query || "").trim();
    if (!raw) return [];
    const tokens = this.tokenizeSearchQuery(raw);
    const likeTokens = (tokens.length > 0 ? tokens : [raw]).slice(0, 8).filter(Boolean);

    const clauses: string[] = [];
    const params: unknown[] = [];
    for (const token of likeTokens) {
      clauses.push("(m.content LIKE ? OR m.summary LIKE ?)");
      const like = `%${token}%`;
      params.push(like, like);
    }

    const where = clauses.length > 0 ? `AND (${clauses.join(" OR ")})` : "";
    const stmt = this.db.prepare(`
      SELECT m.id, m.summary, m.content, m.type, m.created_at, m.task_id
      FROM memories m
      WHERE ${buildImportedMemoryFilterSql("m.content")}
        ${includePrivate ? "" : "AND m.is_private = 0"}
        ${where}
      ORDER BY m.created_at DESC
      LIMIT ?
    `);

    params.push(limit);
    const rows = stmt.all(...params) as Record<string, unknown>[];
    return rows.map((row) => ({
      id: row.id as string,
      snippet: (row.summary as string) || this.truncateToSnippet(row.content as string, 200),
      type: row.type as MemoryType,
      relevanceScore: 1,
      createdAt: row.created_at as number,
      taskId: (row.task_id as string) || undefined,
      source: "db" as const,
    }));
  }

  /**
   * Local-only BM25 search for prompt recall. Skips imported-global and uses
   * a tighter token cap to keep FTS fast. Returns content alongside snippets
   * so callers can filter without a second getFullDetails round-trip.
   */
  searchLocalForPromptRecall(
    workspaceId: string,
    query: string,
    limit = 5,
  ): Array<MemorySearchResult & { source: "db"; content: string }> {
    const raw = (query || "").trim();
    if (!raw) return [];

    try {
      const stmt = this.db.prepare(`
        SELECT m.id, m.summary, m.content, m.type, m.created_at, m.task_id,
               bm25(memories_fts) as score
        FROM memories_fts f
        JOIN memories m ON f.rowid = m.rowid
        WHERE memories_fts MATCH ? AND m.workspace_id = ? AND m.is_private = 0
        ORDER BY score
        LIMIT ?
      `);

      const tokenized = this.buildRelaxedFtsQuery(
        raw,
        MemoryRepository.PROMPT_RECALL_FTS_MAX_TOKENS,
      );
      const tryRaw = this.shouldTryRawFtsQuery(raw);

      const mapRows = (rows: Record<string, unknown>[]) =>
        rows.map((row) => ({
          id: row.id as string,
          snippet:
            (row.summary as string) ||
            this.truncateToSnippet(row.content as string, 200),
          content: row.content as string,
          type: row.type as MemoryType,
          relevanceScore: Math.abs(row.score as number),
          createdAt: row.created_at as number,
          taskId: (row.task_id as string) || undefined,
          source: "db" as const,
        }));

      const ftsM = { workspaceId, limit };
      let rows: Record<string, unknown>[] = [];
      if (tryRaw) {
        try {
          rows = this.runMemoryFtsQuery("prompt-recall-raw", raw, () =>
            stmt.all(raw, workspaceId, limit),
            ftsM,
          ) as Record<string, unknown>[];
        } catch {
          rows = [];
        }
      }

      if (rows.length === 0 && tokenized) {
        try {
          rows = this.runMemoryFtsQuery("prompt-recall-relaxed", tokenized, () =>
            stmt.all(tokenized, workspaceId, limit),
            ftsM,
          ) as Record<string, unknown>[];
        } catch {
          rows = [];
        }
      }

      if (rows.length > 0) return mapRows(rows);
    } catch {
      // Fall through to LIKE fallback
    }

    const tokens = this.tokenizeSearchQuery(raw);
    const likeTokens = (tokens.length > 0 ? tokens : [raw])
      .slice(0, MemoryRepository.PROMPT_RECALL_FTS_MAX_TOKENS)
      .filter(Boolean);

    const clauses: string[] = [];
    const params: unknown[] = [workspaceId];
    for (const token of likeTokens) {
      clauses.push("(content LIKE ? OR summary LIKE ?)");
      const like = `%${token}%`;
      params.push(like, like);
    }

    const where = clauses.length > 0 ? `AND (${clauses.join(" OR ")})` : "";
    const stmt = this.db.prepare(`
      SELECT id, summary, content, type, created_at, task_id
      FROM memories
      WHERE workspace_id = ? AND is_private = 0
        ${where}
      ORDER BY created_at DESC
      LIMIT ?
    `);
    params.push(limit);
    const rows = stmt.all(...params) as Record<string, unknown>[];
    return rows.map((row) => ({
      id: row.id as string,
      snippet:
        (row.summary as string) ||
        this.truncateToSnippet(row.content as string, 200),
      content: row.content as string,
      type: row.type as MemoryType,
      relevanceScore: 1,
      createdAt: row.created_at as number,
      taskId: (row.task_id as string) || undefined,
      source: "db" as const,
    }));
  }

  /**
   * Fast marker-based lookup using LIKE instead of FTS.
   * For background callers that search for known content prefixes/markers
   * (e.g. "[SUGGESTION]", "[PLAYBOOK] Task succeeded").
   */
  searchByContentMarker(
    workspaceId: string,
    marker: string,
    limit = 50,
  ): MemorySearchResult[] {
    const mapRows = (rows: Record<string, unknown>[]) =>
      rows.map((row) => ({
        id: row.id as string,
        snippet:
          (row.summary as string) ||
          this.truncateToSnippet(row.content as string, 200),
        type: row.type as MemoryType,
        relevanceScore: 1,
        createdAt: row.created_at as number,
        taskId: (row.task_id as string) || undefined,
        source: "db" as const,
      }));

    const likeStmt = this.db.prepare(`
      SELECT id, summary, content, type, created_at, task_id
      FROM memories
      WHERE workspace_id = ? AND is_private = 0 AND (content LIKE ? OR summary LIKE ?)
      ORDER BY created_at DESC
      LIMIT ?
    `);
    const like = `%${marker}%`;
    return mapRows(likeStmt.all(workspaceId, like, like, limit) as Record<string, unknown>[]);
  }

  /**
   * Layer 2: Get timeline context around a specific memory
   * Returns surrounding memories within a time window
   */
  getTimelineContext(memoryId: string, windowSize = 5): MemoryTimelineEntry[] {
    const memory = this.findById(memoryId);
    if (!memory) return [];

    const stmt = this.db.prepare(`
      SELECT id, content, type, created_at, task_id
      FROM memories
      WHERE workspace_id = ?
        AND created_at BETWEEN ? AND ?
      ORDER BY created_at ASC
      LIMIT ?
    `);

    const timeWindow = 30 * 60 * 1000; // 30 minutes
    const rows = stmt.all(
      memory.workspaceId,
      memory.createdAt - timeWindow,
      memory.createdAt + timeWindow,
      windowSize * 2 + 1,
    ) as Record<string, unknown>[];

    return rows.map((row) => ({
      id: row.id as string,
      content: row.content as string,
      type: row.type as MemoryType,
      createdAt: row.created_at as number,
      taskId: (row.task_id as string) || undefined,
    }));
  }

  /**
   * Layer 3: Get full details for selected IDs
   * Only called for specific memories when full content is needed
   */
  getFullDetails(ids: string[]): Memory[] {
    return this.findByIds(ids);
  }

  /**
   * Get recent memories for context injection
   */
  getRecentForWorkspace(workspaceId: string, limit = 10, includePrivate = false): Memory[] {
    const privacyFilter = includePrivate ? "" : "AND is_private = 0";
    const stmt = this.db.prepare(`
      SELECT * FROM memories
      WHERE workspace_id = ? ${privacyFilter}
      ORDER BY created_at DESC
      LIMIT ?
    `);
    const rows = stmt.all(workspaceId, limit) as Record<string, unknown>[];
    return rows.map((row) => this.mapRowToMemory(row));
  }

  getRecentImportedGlobal(limit = 20, includePrivate = false): Memory[] {
    const privacyFilter = includePrivate ? "" : "AND is_private = 0";
    const stmt = this.db.prepare(`
      SELECT * FROM memories
      WHERE ${buildImportedMemoryFilterSql("content")} ${privacyFilter}
      ORDER BY created_at DESC
      LIMIT ?
    `);
    const rows = stmt.all(limit) as Record<string, unknown>[];
    return rows.map((row) => this.mapRowToMemory(row));
  }

  /**
   * Get uncompressed memories for batch compression
   */
  getUncompressed(limit = 50): Memory[] {
    const stmt = this.db.prepare(`
      SELECT * FROM memories
      WHERE is_compressed = 0 AND summary IS NULL
      ORDER BY created_at ASC
      LIMIT ?
    `);
    const rows = stmt.all(limit) as Record<string, unknown>[];
    return rows.map((row) => this.mapRowToMemory(row));
  }

  /**
   * List workspace IDs that currently have at least one memory.
   */
  listWorkspaceIds(limit = 5000): string[] {
    const stmt = this.db.prepare(`
      SELECT DISTINCT workspace_id
      FROM memories
      ORDER BY workspace_id ASC
      LIMIT ?
    `);
    const rows = stmt.all(limit) as Array<{ workspace_id: string }>;
    return rows
      .map((row) => row.workspace_id)
      .filter((id) => typeof id === "string" && id.length > 0);
  }

  /**
   * Approximate storage in bytes (UTF-8 length proxy via SQLite length()).
   */
  getApproxStorageBytes(workspaceId: string): number {
    const stmt = this.db.prepare(`
      SELECT COALESCE(SUM(length(content) + COALESCE(length(summary), 0)), 0) as total_bytes
      FROM memories
      WHERE workspace_id = ?
    `);
    const row = stmt.get(workspaceId) as { total_bytes?: number } | undefined;
    const total = Number(row?.total_bytes || 0);
    return Number.isFinite(total) ? total : 0;
  }

  /**
   * Get oldest memories first, including approximate row bytes for cleanup decisions.
   */
  getOldestForWorkspace(
    workspaceId: string,
    limit = 200,
  ): Array<{ id: string; createdAt: number; approxBytes: number }> {
    const stmt = this.db.prepare(`
      SELECT id, created_at, (length(content) + COALESCE(length(summary), 0)) as approx_bytes
      FROM memories
      WHERE workspace_id = ?
      ORDER BY created_at ASC
      LIMIT ?
    `);
    const rows = stmt.all(workspaceId, limit) as Array<{
      id: string;
      created_at: number;
      approx_bytes: number;
    }>;
    return rows.map((row) => ({
      id: row.id,
      createdAt: row.created_at,
      approxBytes: Number.isFinite(row.approx_bytes) ? row.approx_bytes : 0,
    }));
  }

  /**
   * Delete a specific set of memory IDs from a workspace.
   */
  deleteByIds(workspaceId: string, ids: string[]): number {
    if (!ids.length) return 0;
    const placeholders = ids.map(() => "?").join(", ");
    const stmt = this.db.prepare(`
      DELETE FROM memories
      WHERE workspace_id = ? AND id IN (${placeholders})
    `);
    const result = stmt.run(workspaceId, ...ids);
    return result.changes;
  }

  /**
   * Find memories by workspace
   */
  findByWorkspace(workspaceId: string, limit = 100, offset = 0): Memory[] {
    const stmt = this.db.prepare(`
      SELECT * FROM memories
      WHERE workspace_id = ?
      ORDER BY created_at DESC
      LIMIT ? OFFSET ?
    `);
    const rows = stmt.all(workspaceId, limit, offset) as Record<string, unknown>[];
    return rows.map((row) => this.mapRowToMemory(row));
  }

  /**
   * Find memories by task
   */
  findByTask(taskId: string): Memory[] {
    const stmt = this.db.prepare(`
      SELECT * FROM memories
      WHERE task_id = ?
      ORDER BY created_at ASC
    `);
    const rows = stmt.all(taskId) as Record<string, unknown>[];
    return rows.map((row) => this.mapRowToMemory(row));
  }

  /**
   * Cleanup old memories based on retention policy
   */
  deleteOlderThan(workspaceId: string, cutoffTimestamp: number): number {
    const stmt = this.db.prepare(`
      DELETE FROM memories
      WHERE workspace_id = ? AND created_at < ?
    `);
    const result = stmt.run(workspaceId, cutoffTimestamp);
    return result.changes;
  }

  /**
   * Delete all memories for a workspace
   */
  deleteByWorkspace(workspaceId: string): number {
    const stmt = this.db.prepare("DELETE FROM memories WHERE workspace_id = ?");
    const result = stmt.run(workspaceId);
    return result.changes;
  }

  deleteByWorkspaceAndId(workspaceId: string, memoryId: string): number {
    const stmt = this.db.prepare("DELETE FROM memories WHERE workspace_id = ? AND id = ?");
    const result = stmt.run(workspaceId, memoryId);
    return result.changes;
  }

  /**
   * Get storage statistics for a workspace
   */
  getStats(workspaceId: string): MemoryStats {
    const stmt = this.db.prepare(`
      SELECT COUNT(*) as count,
             COALESCE(SUM(tokens), 0) as total_tokens,
             SUM(CASE WHEN is_compressed = 1 THEN 1 ELSE 0 END) as compressed_count
      FROM memories
      WHERE workspace_id = ?
    `);
    const row = stmt.get(workspaceId) as Record<string, unknown>;
    const count = row.count as number;
    const compressedCount = row.compressed_count as number;
    return {
      count,
      totalTokens: row.total_tokens as number,
      compressedCount,
      compressionRatio: count > 0 ? compressedCount / count : 0,
    };
  }

  /**
   * Get statistics for imported memories
   */
  getImportedStats(workspaceId: string): { count: number; totalTokens: number } {
    const stmt = this.db.prepare(`
      SELECT COUNT(*) as count, COALESCE(SUM(tokens), 0) as total_tokens
      FROM memories
      WHERE workspace_id = ? AND ${buildImportedMemoryFilterSql("content")}
    `);
    const row = stmt.get(workspaceId) as Record<string, unknown>;
    return {
      count: row.count as number,
      totalTokens: row.total_tokens as number,
    };
  }

  /**
   * Find imported memories with pagination
   */
  findImported(workspaceId: string, limit = 50, offset = 0): Memory[] {
    const stmt = this.db.prepare(`
      SELECT * FROM memories
      WHERE workspace_id = ? AND ${buildImportedMemoryFilterSql("content")}
      ORDER BY created_at DESC
      LIMIT ? OFFSET ?
    `);
    const rows = stmt.all(workspaceId, limit, offset) as Record<string, unknown>[];
    return rows.map((row) => this.mapRowToMemory(row));
  }

  /**
   * Delete all imported memories for a workspace
   */
  deleteImported(workspaceId: string): number {
    const stmt = this.db.prepare(
      `DELETE FROM memories WHERE workspace_id = ? AND ${buildImportedMemoryFilterSql("content")}`,
    );
    const result = stmt.run(workspaceId);
    return result.changes;
  }

  private truncateToSnippet(content: string, maxChars: number): string {
    if (content.length <= maxChars) return content;
    return content.slice(0, maxChars - 3) + "...";
  }

  private tokenizeSearchQuery(raw: string): string[] {
    return (raw || "")
      .toLowerCase()
      .replace(/[^a-z0-9_\s-]/g, " ")
      .split(/\s+/)
      .map((t) => t.trim())
      .filter((t) => t.length > 1 && !MemoryRepository.MEMORY_SEARCH_STOP_WORDS.has(t));
  }

  private buildRelaxedFtsQuery(raw: string, maxTokens = 8): string | null {
    const tokens = this.tokenizeSearchQuery(raw).slice(0, maxTokens);
    if (tokens.length === 0) return null;

    // Quote tokens to avoid them being interpreted as query operators.
    // Use OR to improve recall for long natural-language prompts.
    const parts = tokens.map((t) => `"${t.replace(/"/g, "")}"`);
    return parts.join(" OR ");
  }

  private shouldTryRawFtsQuery(raw: string): boolean {
    if (raw.length > MemoryRepository.MEMORY_FTS_RAW_MAX_CHARS) return false;
    return this.tokenizeSearchQuery(raw).length <= MemoryRepository.MEMORY_FTS_RAW_MAX_TOKENS;
  }

  private runMemoryFtsQuery<T>(
    label: string,
    query: string,
    run: () => T,
    meta?: { workspaceId?: string; limit?: number },
  ): T {
    const startedAt = Date.now();
    let result: T;
    try {
      result = run();
      return result;
    } finally {
      const elapsedMs = Date.now() - startedAt;
      if (elapsedMs >= MemoryRepository.MEMORY_FTS_SLOW_QUERY_MS) {
        const tokenCount = this.tokenizeSearchQuery(query).length;
        const rowCount = Array.isArray(result!) ? result!.length : -1;
        memoryRepositoryLogger.warn(
          `[MemoryRepository] Slow memory FTS query` +
            ` label=${label} elapsedMs=${elapsedMs}` +
            ` queryChars=${query.length} tokens=${tokenCount}` +
            ` rows=${rowCount} limit=${meta?.limit ?? "?"}` +
            ` workspace=${meta?.workspaceId ?? "global"}`,
        );
      }
    }
  }

  private mapRowToMemory(row: Record<string, unknown>): Memory {
    return {
      id: row.id as string,
      workspaceId: row.workspace_id as string,
      taskId: (row.task_id as string) || undefined,
      type: row.type as MemoryType,
      content: row.content as string,
      summary: (row.summary as string) || undefined,
      tokens: row.tokens as number,
      isCompressed: row.is_compressed === 1,
      isPrivate: row.is_private === 1,
      createdAt: row.created_at as number,
      updatedAt: row.updated_at as number,
    };
  }
}

export class CuratedMemoryRepository {
  constructor(private db: Database.Database) {}

  create(
    input: Omit<CuratedMemoryEntryRecord, "id" | "createdAt" | "updatedAt"> & {
      id?: string;
      createdAt?: number;
      updatedAt?: number;
    },
  ): CuratedMemoryEntryRecord {
    const now = Date.now();
    const entry: CuratedMemoryEntryRecord = {
      ...input,
      id: input.id || uuidv4(),
      createdAt: input.createdAt ?? now,
      updatedAt: input.updatedAt ?? now,
    };

    this.db
      .prepare(
        `INSERT INTO curated_memory_entries (
          id, workspace_id, task_id, target, kind, content, normalized_key, source,
          confidence, status, created_at, updated_at, last_confirmed_at
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      )
      .run(
        entry.id,
        entry.workspaceId,
        entry.taskId || null,
        entry.target,
        entry.kind,
        entry.content,
        entry.normalizedKey,
        entry.source,
        entry.confidence,
        entry.status,
        entry.createdAt,
        entry.updatedAt,
        entry.lastConfirmedAt ?? null,
      );

    return entry;
  }

  update(
    id: string,
    updates: Partial<
      Pick<
        CuratedMemoryEntryRecord,
        "kind" | "content" | "normalizedKey" | "confidence" | "status" | "lastConfirmedAt"
      >
    >,
  ): CuratedMemoryEntryRecord | undefined {
    const fields: string[] = [];
    const values: unknown[] = [];

    if (updates.kind !== undefined) {
      fields.push("kind = ?");
      values.push(updates.kind);
    }
    if (updates.content !== undefined) {
      fields.push("content = ?");
      values.push(updates.content);
    }
    if (updates.normalizedKey !== undefined) {
      fields.push("normalized_key = ?");
      values.push(updates.normalizedKey);
    }
    if (updates.confidence !== undefined) {
      fields.push("confidence = ?");
      values.push(updates.confidence);
    }
    if (updates.status !== undefined) {
      fields.push("status = ?");
      values.push(updates.status);
    }
    if (updates.lastConfirmedAt !== undefined) {
      fields.push("last_confirmed_at = ?");
      values.push(updates.lastConfirmedAt ?? null);
    }
    if (fields.length === 0) return this.findById(id);

    fields.push("updated_at = ?");
    values.push(Date.now(), id);

    this.db
      .prepare(`UPDATE curated_memory_entries SET ${fields.join(", ")} WHERE id = ?`)
      .run(...values);

    return this.findById(id);
  }

  findById(id: string): CuratedMemoryEntryRecord | undefined {
    const row = this.db
      .prepare("SELECT * FROM curated_memory_entries WHERE id = ?")
      .get(id) as Record<string, unknown> | undefined;
    return row ? this.mapRow(row) : undefined;
  }

  findByNormalizedKey(
    workspaceId: string,
    target: CuratedMemoryTarget,
    kind: CuratedMemoryKind,
    normalizedKey: string,
  ): CuratedMemoryEntryRecord | undefined {
    const row = this.db
      .prepare(
        `SELECT * FROM curated_memory_entries
         WHERE workspace_id = ?
           AND target = ?
           AND kind = ?
           AND normalized_key = ?
           AND status = 'active'
         ORDER BY updated_at DESC
         LIMIT 1`,
      )
      .get(workspaceId, target, kind, normalizedKey) as Record<string, unknown> | undefined;
    return row ? this.mapRow(row) : undefined;
  }

  findFirstMatching(workspaceId: string, target: CuratedMemoryTarget, match: string) {
    const token = `%${match}%`;
    const row = this.db
      .prepare(
        `SELECT * FROM curated_memory_entries
         WHERE workspace_id = ?
           AND target = ?
           AND status = 'active'
           AND content LIKE ?
         ORDER BY updated_at DESC
         LIMIT 1`,
      )
      .get(workspaceId, target, token) as Record<string, unknown> | undefined;
    return row ? this.mapRow(row) : undefined;
  }

  list(params: {
    workspaceId: string;
    target?: CuratedMemoryTarget;
    kind?: CuratedMemoryKind;
    status?: "active" | "archived";
    limit?: number;
  }): CuratedMemoryEntryRecord[] {
    const clauses = ["workspace_id = ?"];
    const values: unknown[] = [params.workspaceId];
    if (params.target) {
      clauses.push("target = ?");
      values.push(params.target);
    }
    if (params.kind) {
      clauses.push("kind = ?");
      values.push(params.kind);
    }
    if (params.status) {
      clauses.push("status = ?");
      values.push(params.status);
    }
    values.push(Math.max(1, params.limit ?? 100));
    const rows = this.db
      .prepare(
        `SELECT * FROM curated_memory_entries
         WHERE ${clauses.join(" AND ")}
         ORDER BY
           CASE target WHEN 'user' THEN 0 ELSE 1 END,
           confidence DESC,
           updated_at DESC
         LIMIT ?`,
      )
      .all(...values) as Record<string, unknown>[];
    return rows.map((row) => this.mapRow(row));
  }

  archive(id: string): CuratedMemoryEntryRecord | undefined {
    return this.update(id, { status: "archived" });
  }

  private mapRow(row: Record<string, unknown>): CuratedMemoryEntryRecord {
    return {
      id: row.id as string,
      workspaceId: row.workspace_id as string,
      taskId: (row.task_id as string) || undefined,
      target: row.target as CuratedMemoryTarget,
      kind: row.kind as CuratedMemoryKind,
      content: row.content as string,
      normalizedKey: row.normalized_key as string,
      source: row.source as CuratedMemoryEntryRecord["source"],
      confidence: Number(row.confidence || 0),
      status: row.status as CuratedMemoryEntryRecord["status"],
      createdAt: Number(row.created_at || 0),
      updatedAt: Number(row.updated_at || 0),
      lastConfirmedAt: row.last_confirmed_at ? Number(row.last_confirmed_at) : undefined,
    };
  }
}

export class MemoryEmbeddingRepository {
  constructor(private db: Database.Database) {}

  upsert(workspaceId: string, memoryId: string, embedding: number[], updatedAt = Date.now()): void {
    const stmt = this.db.prepare(`
      INSERT INTO memory_embeddings (memory_id, workspace_id, embedding, updated_at)
      VALUES (?, ?, ?, ?)
      ON CONFLICT(memory_id) DO UPDATE SET
        workspace_id = excluded.workspace_id,
        embedding = excluded.embedding,
        updated_at = excluded.updated_at
    `);
    stmt.run(memoryId, workspaceId, JSON.stringify(embedding), updatedAt);
  }

  getByWorkspace(workspaceId: string): MemoryEmbedding[] {
    const stmt = this.db.prepare(`
      SELECT memory_id, workspace_id, embedding, updated_at
      FROM memory_embeddings
      WHERE workspace_id = ?
    `);
    const rows = stmt.all(workspaceId) as Array<{
      memory_id: string;
      workspace_id: string;
      embedding: string;
      updated_at: number;
    }>;

    const results: MemoryEmbedding[] = [];
    for (const row of rows) {
      try {
        const parsed = JSON.parse(row.embedding) as number[];
        if (!Array.isArray(parsed)) continue;
        results.push({
          memoryId: row.memory_id,
          workspaceId: row.workspace_id,
          embedding: parsed,
          updatedAt: row.updated_at,
        });
      } catch {
        // ignore malformed row
      }
    }
    return results;
  }

  getStats(workspaceId: string): { count: number } {
    const stmt = this.db.prepare(`
      SELECT COUNT(*) as count
      FROM memory_embeddings
      WHERE workspace_id = ?
    `);
    const row = stmt.get(workspaceId) as Record<string, unknown>;
    return { count: row.count as number };
  }

  /**
   * Find memories that are missing embeddings or have stale embeddings.
   * Ordered by most-recently-updated first so results improve quickly.
   */
  findMissingOrStale(
    workspaceId: string,
    limit = 500,
  ): Array<{ memoryId: string; updatedAt: number; content: string; summary?: string }> {
    const stmt = this.db.prepare(`
      SELECT m.id as memory_id, m.updated_at, m.content, m.summary, e.updated_at as emb_updated_at
      FROM memories m
      LEFT JOIN memory_embeddings e ON e.memory_id = m.id
      WHERE m.workspace_id = ?
        AND (e.memory_id IS NULL OR e.updated_at < m.updated_at)
      ORDER BY m.updated_at DESC
      LIMIT ?
    `);
    const rows = stmt.all(workspaceId, limit) as Array<{
      memory_id: string;
      updated_at: number;
      content: string;
      summary: string | null;
      emb_updated_at: number | null;
    }>;
    return rows.map((r) => ({
      memoryId: r.memory_id,
      updatedAt: r.updated_at,
      content: r.content,
      summary: r.summary || undefined,
    }));
  }

  getImportedGlobal(limit = 5000, offset = 0): Array<MemoryEmbedding & { workspaceId: string }> {
    const stmt = this.db.prepare(`
      SELECT e.memory_id, e.workspace_id, e.embedding, e.updated_at
      FROM memory_embeddings e
      JOIN memories m ON m.id = e.memory_id
      WHERE ${buildImportedMemoryFilterSql("m.content")}
      ORDER BY e.updated_at DESC
      LIMIT ? OFFSET ?
    `);
    const rows = stmt.all(limit, offset) as Array<{
      memory_id: string;
      workspace_id: string;
      embedding: string;
      updated_at: number;
    }>;

    const results: Array<MemoryEmbedding & { workspaceId: string }> = [];
    for (const row of rows) {
      try {
        const parsed = JSON.parse(row.embedding) as number[];
        if (!Array.isArray(parsed)) continue;
        results.push({
          memoryId: row.memory_id,
          workspaceId: row.workspace_id,
          embedding: parsed,
          updatedAt: row.updated_at,
        });
      } catch {
        // ignore
      }
    }
    return results;
  }

  findMissingOrStaleImportedGlobal(limit = 500): Array<{
    memoryId: string;
    workspaceId: string;
    updatedAt: number;
    content: string;
    summary?: string;
  }> {
    const stmt = this.db.prepare(`
      SELECT m.id as memory_id, m.workspace_id, m.updated_at, m.content, m.summary, e.updated_at as emb_updated_at
      FROM memories m
      LEFT JOIN memory_embeddings e ON e.memory_id = m.id
      WHERE ${buildImportedMemoryFilterSql("m.content")}
        AND (e.memory_id IS NULL OR e.updated_at < m.updated_at)
      ORDER BY m.updated_at DESC
      LIMIT ?
    `);
    const rows = stmt.all(limit) as Array<{
      memory_id: string;
      workspace_id: string;
      updated_at: number;
      content: string;
      summary: string | null;
      emb_updated_at: number | null;
    }>;
    return rows.map((r) => ({
      memoryId: r.memory_id,
      workspaceId: r.workspace_id,
      updatedAt: r.updated_at,
      content: r.content,
      summary: r.summary || undefined,
    }));
  }

  deleteByWorkspace(workspaceId: string): number {
    const stmt = this.db.prepare("DELETE FROM memory_embeddings WHERE workspace_id = ?");
    const result = stmt.run(workspaceId);
    return result.changes;
  }

  deleteByMemoryIds(ids: string[]): number {
    if (!ids.length) return 0;
    const placeholders = ids.map(() => "?").join(", ");
    const stmt = this.db.prepare(`
      DELETE FROM memory_embeddings
      WHERE memory_id IN (${placeholders})
    `);
    const result = stmt.run(...ids);
    return result.changes;
  }

  deleteImported(workspaceId: string): number {
    // Must be called before deleting imported memories from the memories table.
    const stmt = this.db.prepare(`
      DELETE FROM memory_embeddings
      WHERE workspace_id = ?
        AND memory_id IN (
          SELECT id FROM memories
          WHERE workspace_id = ? AND ${buildImportedMemoryFilterSql("content")}
        )
    `);
    const result = stmt.run(workspaceId, workspaceId);
    return result.changes;
  }
}

export class MemorySummaryRepository {
  constructor(private db: Database.Database) {}

  create(summary: Omit<MemorySummary, "id" | "createdAt">): MemorySummary {
    const newSummary: MemorySummary = {
      ...summary,
      id: uuidv4(),
      createdAt: Date.now(),
    };

    const stmt = this.db.prepare(`
      INSERT INTO memory_summaries (id, workspace_id, time_period, period_start, period_end, summary, memory_ids, tokens, created_at)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
    `);

    stmt.run(
      newSummary.id,
      newSummary.workspaceId,
      newSummary.timePeriod,
      newSummary.periodStart,
      newSummary.periodEnd,
      newSummary.summary,
      JSON.stringify(newSummary.memoryIds),
      newSummary.tokens,
      newSummary.createdAt,
    );

    return newSummary;
  }

  findByWorkspaceAndPeriod(
    workspaceId: string,
    timePeriod: TimePeriod,
    limit = 10,
  ): MemorySummary[] {
    const stmt = this.db.prepare(`
      SELECT * FROM memory_summaries
      WHERE workspace_id = ? AND time_period = ?
      ORDER BY period_start DESC
      LIMIT ?
    `);
    const rows = stmt.all(workspaceId, timePeriod, limit) as Record<string, unknown>[];
    return rows.map((row) => this.mapRowToSummary(row));
  }

  findByWorkspace(workspaceId: string, limit = 50): MemorySummary[] {
    const stmt = this.db.prepare(`
      SELECT * FROM memory_summaries
      WHERE workspace_id = ?
      ORDER BY period_start DESC
      LIMIT ?
    `);
    const rows = stmt.all(workspaceId, limit) as Record<string, unknown>[];
    return rows.map((row) => this.mapRowToSummary(row));
  }

  deleteByWorkspace(workspaceId: string): number {
    const stmt = this.db.prepare("DELETE FROM memory_summaries WHERE workspace_id = ?");
    const result = stmt.run(workspaceId);
    return result.changes;
  }

  private mapRowToSummary(row: Record<string, unknown>): MemorySummary {
    return {
      id: row.id as string,
      workspaceId: row.workspace_id as string,
      timePeriod: row.time_period as TimePeriod,
      periodStart: row.period_start as number,
      periodEnd: row.period_end as number,
      summary: row.summary as string,
      memoryIds: safeJsonParse(row.memory_ids as string, [] as string[], "memorySummary.memoryIds"),
      tokens: row.tokens as number,
      createdAt: row.created_at as number,
    };
  }
}

export class MemorySettingsRepository {
  constructor(private db: Database.Database) {}

  getOrCreate(workspaceId: string): MemorySettings {
    const stmt = this.db.prepare("SELECT * FROM memory_settings WHERE workspace_id = ?");
    const row = stmt.get(workspaceId) as Record<string, unknown> | undefined;

    if (row) {
      return this.mapRowToSettings(row);
    }

    // Create default settings
    const defaults: MemorySettings = {
      workspaceId,
      enabled: true,
      autoCapture: true,
      compressionEnabled: true,
      retentionDays: 90,
      maxStorageMb: 100,
      privacyMode: "normal",
      excludedPatterns: [],
    };

    const insertStmt = this.db.prepare(`
      INSERT INTO memory_settings (workspace_id, enabled, auto_capture, compression_enabled, retention_days, max_storage_mb, privacy_mode, excluded_patterns)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?)
    `);

    insertStmt.run(
      defaults.workspaceId,
      defaults.enabled ? 1 : 0,
      defaults.autoCapture ? 1 : 0,
      defaults.compressionEnabled ? 1 : 0,
      defaults.retentionDays,
      defaults.maxStorageMb,
      defaults.privacyMode,
      JSON.stringify(defaults.excludedPatterns),
    );

    return defaults;
  }

  update(workspaceId: string, updates: Partial<Omit<MemorySettings, "workspaceId">>): void {
    const fields: string[] = [];
    const values: unknown[] = [];

    if (updates.enabled !== undefined) {
      fields.push("enabled = ?");
      values.push(updates.enabled ? 1 : 0);
    }
    if (updates.autoCapture !== undefined) {
      fields.push("auto_capture = ?");
      values.push(updates.autoCapture ? 1 : 0);
    }
    if (updates.compressionEnabled !== undefined) {
      fields.push("compression_enabled = ?");
      values.push(updates.compressionEnabled ? 1 : 0);
    }
    if (updates.retentionDays !== undefined) {
      fields.push("retention_days = ?");
      values.push(updates.retentionDays);
    }
    if (updates.maxStorageMb !== undefined) {
      fields.push("max_storage_mb = ?");
      values.push(updates.maxStorageMb);
    }
    if (updates.privacyMode !== undefined) {
      fields.push("privacy_mode = ?");
      values.push(updates.privacyMode);
    }
    if (updates.excludedPatterns !== undefined) {
      fields.push("excluded_patterns = ?");
      values.push(JSON.stringify(updates.excludedPatterns));
    }

    if (fields.length === 0) return;

    values.push(workspaceId);
    const stmt = this.db.prepare(
      `UPDATE memory_settings SET ${fields.join(", ")} WHERE workspace_id = ?`,
    );
    stmt.run(...values);
  }

  delete(workspaceId: string): void {
    const stmt = this.db.prepare("DELETE FROM memory_settings WHERE workspace_id = ?");
    stmt.run(workspaceId);
  }

  private mapRowToSettings(row: Record<string, unknown>): MemorySettings {
    return {
      workspaceId: row.workspace_id as string,
      enabled: row.enabled === 1,
      autoCapture: row.auto_capture === 1,
      compressionEnabled: row.compression_enabled === 1,
      retentionDays: row.retention_days as number,
      maxStorageMb: row.max_storage_mb as number,
      privacyMode: row.privacy_mode as PrivacyMode,
      excludedPatterns: safeJsonParse(
        row.excluded_patterns as string,
        [] as string[],
        "memorySettings.excludedPatterns",
      ),
    };
  }
}

export class PendingMemoryWriteRepository {
  constructor(private db: Database.Database) {}

  create(input: {
    workspaceId: string;
    taskId?: string;
    target: string;
    action: string;
    origin: string;
    summary: string;
    payload: Record<string, unknown>;
    oldValue?: string;
    proposedValue?: string;
    reason?: string;
    evidence?: Array<Record<string, unknown>>;
    riskScore?: number;
  }): PendingMemoryWrite {
    const now = Date.now();
    const record: PendingMemoryWrite = {
      id: uuidv4(),
      workspaceId: input.workspaceId,
      taskId: input.taskId,
      target: input.target,
      action: input.action,
      origin: input.origin,
      summary: input.summary,
      payload: input.payload,
      oldValue: input.oldValue,
      proposedValue: input.proposedValue,
      reason: input.reason,
      evidence: input.evidence || [],
      riskScore: input.riskScore ?? 0,
      status: "pending",
      createdAt: now,
    };

    this.db
      .prepare(
        `INSERT INTO pending_memory_writes (
          id, workspace_id, task_id, target, action, origin, summary, payload_json,
          old_value, proposed_value, reason, evidence_json, risk_score, status,
          created_at, reviewed_at, reviewed_by, resolution
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      )
      .run(
        record.id,
        record.workspaceId,
        record.taskId || null,
        record.target,
        record.action,
        record.origin,
        record.summary,
        JSON.stringify(record.payload),
        record.oldValue || null,
        record.proposedValue || null,
        record.reason || null,
        JSON.stringify(record.evidence),
        record.riskScore,
        record.status,
        record.createdAt,
        null,
        null,
        null,
      );

    return record;
  }

  findById(id: string): PendingMemoryWrite | undefined {
    const row = this.db
      .prepare("SELECT * FROM pending_memory_writes WHERE id = ?")
      .get(id) as Record<string, unknown> | undefined;
    return row ? this.mapRow(row) : undefined;
  }

  list(params: {
    workspaceId?: string;
    status?: PendingMemoryWriteStatus;
    limit?: number;
  } = {}): PendingMemoryWrite[] {
    const clauses: string[] = [];
    const values: unknown[] = [];
    if (params.workspaceId) {
      clauses.push("workspace_id = ?");
      values.push(params.workspaceId);
    }
    if (params.status) {
      clauses.push("status = ?");
      values.push(params.status);
    }
    values.push(Math.max(1, params.limit ?? 100));
    const where = clauses.length > 0 ? `WHERE ${clauses.join(" AND ")}` : "";
    const rows = this.db
      .prepare(
        `SELECT * FROM pending_memory_writes
         ${where}
         ORDER BY created_at DESC
         LIMIT ?`,
      )
      .all(...values) as Record<string, unknown>[];
    return rows.map((row) => this.mapRow(row));
  }

  countPending(workspaceId?: string): number {
    if (workspaceId) {
      const row = this.db
        .prepare("SELECT COUNT(*) AS count FROM pending_memory_writes WHERE workspace_id = ? AND status = 'pending'")
        .get(workspaceId) as { count?: number } | undefined;
      return Number(row?.count || 0);
    }
    const row = this.db
      .prepare("SELECT COUNT(*) AS count FROM pending_memory_writes WHERE status = 'pending'")
      .get() as { count?: number } | undefined;
    return Number(row?.count || 0);
  }

  updateStatus(
    id: string,
    status: PendingMemoryWriteStatus,
    details: { reviewedBy?: string; resolution?: string } = {},
  ): PendingMemoryWrite | undefined {
    this.db
      .prepare(
        `UPDATE pending_memory_writes
         SET status = ?, reviewed_at = ?, reviewed_by = ?, resolution = ?
         WHERE id = ?`,
      )
      .run(status, Date.now(), details.reviewedBy || null, details.resolution || null, id);
    return this.findById(id);
  }

  updateStatusIfCurrent(
    id: string,
    expectedStatus: PendingMemoryWriteStatus,
    status: PendingMemoryWriteStatus,
    details: { reviewedBy?: string; resolution?: string } = {},
  ): PendingMemoryWrite | undefined {
    const result = this.db
      .prepare(
        `UPDATE pending_memory_writes
         SET status = ?, reviewed_at = ?, reviewed_by = ?, resolution = ?
         WHERE id = ? AND status = ?`,
      )
      .run(status, Date.now(), details.reviewedBy || null, details.resolution || null, id, expectedStatus);
    return result.changes > 0 ? this.findById(id) : undefined;
  }

  private mapRow(row: Record<string, unknown>): PendingMemoryWrite {
    return {
      id: row.id as string,
      workspaceId: row.workspace_id as string,
      taskId: (row.task_id as string) || undefined,
      target: row.target as string,
      action: row.action as string,
      origin: row.origin as string,
      summary: row.summary as string,
      payload: safeJsonParse(
        row.payload_json as string,
        {} as Record<string, unknown>,
        "pendingMemoryWrite.payload",
      ),
      oldValue: (row.old_value as string) || undefined,
      proposedValue: (row.proposed_value as string) || undefined,
      reason: (row.reason as string) || undefined,
      evidence: safeJsonParse(
        row.evidence_json as string,
        [] as Array<Record<string, unknown>>,
        "pendingMemoryWrite.evidence",
      ),
      riskScore: Number(row.risk_score || 0),
      status: row.status as PendingMemoryWriteStatus,
      createdAt: Number(row.created_at || 0),
      reviewedAt: row.reviewed_at ? Number(row.reviewed_at) : undefined,
      reviewedBy: (row.reviewed_by as string) || undefined,
      resolution: (row.resolution as string) || undefined,
    };
  }
}

// ============ Git Worktree Repository ============

export class WorktreeInfoRepository {
  constructor(private db: Database.Database) {}

  create(info: WorktreeInfo): WorktreeInfo {
    const stmt = this.db.prepare(`
      INSERT INTO worktree_info (task_id, workspace_id, repo_path, worktree_path, branch_name, base_branch, base_commit, status, created_at, last_commit_sha, last_commit_message, merge_result)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
    `);
    stmt.run(
      info.taskId,
      info.workspaceId,
      info.repoPath ?? null,
      info.worktreePath,
      info.branchName,
      info.baseBranch,
      info.baseCommit,
      info.status,
      info.createdAt,
      info.lastCommitSha ?? null,
      info.lastCommitMessage ?? null,
      info.mergeResult ? JSON.stringify(info.mergeResult) : null,
    );
    return info;
  }

  findByTaskId(taskId: string): WorktreeInfo | undefined {
    const stmt = this.db.prepare("SELECT * FROM worktree_info WHERE task_id = ?");
    const row = stmt.get(taskId) as Record<string, unknown> | undefined;
    return row ? this.mapRow(row) : undefined;
  }

  findByWorkspaceId(workspaceId: string): WorktreeInfo[] {
    const stmt = this.db.prepare(
      "SELECT * FROM worktree_info WHERE workspace_id = ? ORDER BY created_at DESC",
    );
    const rows = stmt.all(workspaceId) as Record<string, unknown>[];
    return rows.map((row) => this.mapRow(row));
  }

  update(taskId: string, updates: Partial<WorktreeInfo>): void {
    const fields: string[] = [];
    const values: unknown[] = [];

    if (updates.repoPath !== undefined) {
      fields.push("repo_path = ?");
      values.push(updates.repoPath);
    }
    if (updates.status !== undefined) {
      fields.push("status = ?");
      values.push(updates.status);
    }
    if (updates.lastCommitSha !== undefined) {
      fields.push("last_commit_sha = ?");
      values.push(updates.lastCommitSha);
    }
    if (updates.lastCommitMessage !== undefined) {
      fields.push("last_commit_message = ?");
      values.push(updates.lastCommitMessage);
    }
    if (updates.mergeResult !== undefined) {
      fields.push("merge_result = ?");
      values.push(JSON.stringify(updates.mergeResult));
    }

    if (fields.length === 0) return;

    values.push(taskId);
    const stmt = this.db.prepare(`UPDATE worktree_info SET ${fields.join(", ")} WHERE task_id = ?`);
    stmt.run(...values);
  }

  delete(taskId: string): void {
    const stmt = this.db.prepare("DELETE FROM worktree_info WHERE task_id = ?");
    stmt.run(taskId);
  }

  private mapRow(row: Record<string, unknown>): WorktreeInfo {
    return {
      taskId: row.task_id as string,
      workspaceId: row.workspace_id as string,
      repoPath: (row.repo_path as string) || undefined,
      worktreePath: row.worktree_path as string,
      branchName: row.branch_name as string,
      baseBranch: row.base_branch as string,
      baseCommit: row.base_commit as string,
      status: row.status as WorktreeStatus,
      createdAt: row.created_at as number,
      lastCommitSha: (row.last_commit_sha as string) || undefined,
      lastCommitMessage: (row.last_commit_message as string) || undefined,
      mergeResult: row.merge_result
        ? safeJsonParse<MergeResult>(
            row.merge_result as string,
            { success: false },
            "worktreeInfo.mergeResult",
          )
        : undefined,
    };
  }
}

// ============ Comparison Session Repository ============

export class ComparisonSessionRepository {
  constructor(private db: Database.Database) {}

  create(params: Omit<ComparisonSession, "id" | "createdAt">): ComparisonSession {
    const session: ComparisonSession = {
      id: uuidv4(),
      ...params,
      createdAt: Date.now(),
    };

    const stmt = this.db.prepare(`
      INSERT INTO comparison_sessions (id, title, prompt, workspace_id, status, task_ids, created_at, completed_at, comparison_result)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
    `);
    stmt.run(
      session.id,
      session.title,
      session.prompt,
      session.workspaceId,
      session.status,
      JSON.stringify(session.taskIds),
      session.createdAt,
      session.completedAt ?? null,
      session.comparisonResult ? JSON.stringify(session.comparisonResult) : null,
    );
    return session;
  }

  findById(id: string): ComparisonSession | undefined {
    const stmt = this.db.prepare("SELECT * FROM comparison_sessions WHERE id = ?");
    const row = stmt.get(id) as Record<string, unknown> | undefined;
    if (!row) return undefined;
    return this.reconcileTaskIds(this.mapRow(row));
  }

  findByWorkspaceId(workspaceId: string): ComparisonSession[] {
    const stmt = this.db.prepare(
      "SELECT * FROM comparison_sessions WHERE workspace_id = ? ORDER BY created_at DESC",
    );
    const rows = stmt.all(workspaceId) as Record<string, unknown>[];
    return rows.map((row) => this.reconcileTaskIds(this.mapRow(row)));
  }

  update(id: string, updates: Partial<ComparisonSession>): void {
    const fields: string[] = [];
    const values: unknown[] = [];

    if (updates.status !== undefined) {
      fields.push("status = ?");
      values.push(updates.status);
    }
    if (updates.taskIds !== undefined) {
      // Keep materialized task_ids aligned with the canonical task linkage source.
      const canonicalTaskIds = this.getTaskIdsForSession(id);
      fields.push("task_ids = ?");
      values.push(JSON.stringify(canonicalTaskIds));
    }
    if (updates.completedAt !== undefined) {
      fields.push("completed_at = ?");
      values.push(updates.completedAt);
    }
    if (updates.comparisonResult !== undefined) {
      fields.push("comparison_result = ?");
      values.push(JSON.stringify(updates.comparisonResult));
    }

    if (fields.length === 0) return;

    values.push(id);
    const stmt = this.db.prepare(
      `UPDATE comparison_sessions SET ${fields.join(", ")} WHERE id = ?`,
    );
    stmt.run(...values);
  }

  delete(id: string): void {
    const stmt = this.db.prepare("DELETE FROM comparison_sessions WHERE id = ?");
    stmt.run(id);
  }

  syncTaskIdsFromTasks(sessionId: string): string[] {
    const taskIds = this.getTaskIdsForSession(sessionId);
    const stmt = this.db.prepare("UPDATE comparison_sessions SET task_ids = ? WHERE id = ?");
    stmt.run(JSON.stringify(taskIds), sessionId);
    return taskIds;
  }

  private mapRow(row: Record<string, unknown>): ComparisonSession {
    return {
      id: row.id as string,
      title: row.title as string,
      prompt: row.prompt as string,
      workspaceId: row.workspace_id as string,
      status: row.status as ComparisonSessionStatus,
      taskIds: safeJsonParse<string[]>(row.task_ids as string, [], "comparisonSession.taskIds"),
      createdAt: row.created_at as number,
      completedAt: (row.completed_at as number) || undefined,
      comparisonResult: row.comparison_result
        ? safeJsonParse<ComparisonResult>(
            row.comparison_result as string,
            { taskResults: [] },
            "comparisonSession.comparisonResult",
          )
        : undefined,
    };
  }

  private reconcileTaskIds(session: ComparisonSession): ComparisonSession {
    const canonicalTaskIds = this.getTaskIdsForSession(session.id);
    if (this.arraysEqual(session.taskIds, canonicalTaskIds)) {
      return session;
    }
    const stmt = this.db.prepare("UPDATE comparison_sessions SET task_ids = ? WHERE id = ?");
    stmt.run(JSON.stringify(canonicalTaskIds), session.id);
    return { ...session, taskIds: canonicalTaskIds };
  }

  private getTaskIdsForSession(sessionId: string): string[] {
    const stmt = this.db.prepare(
      "SELECT id FROM tasks WHERE comparison_session_id = ? ORDER BY created_at ASC",
    );
    const rows = stmt.all(sessionId) as Array<{ id: string }>;
    return rows
      .map((row) => row.id)
      .filter((id): id is string => typeof id === "string" && id.length > 0);
  }

  private arraysEqual(a: string[], b: string[]): boolean {
    if (a.length !== b.length) return false;
    for (let i = 0; i < a.length; i++) {
      if (a[i] !== b[i]) return false;
    }
    return true;
  }
}
