// provider_sync.js —— 一键把历史会话同步到「当前账号」的 provider，使其在 Codex 侧栏重新显示。
// 复刻 CodexPlusPlus 的 provider-sync：改 rollout 首行 provider + state DB(provider/has_user_event/cwd)。
// 必须在 Codex 已退出时运行。数据库用纯 JS 的 sql.js（免原生编译，跨平台）。
const fs = require("fs");
const path = require("path");
const os = require("os");
const { spawnSync } = require("child_process");
const initSqlJs = require("sql.js");
const { cleanThreadTitle, cleanUserVisibleText } = require("./title_clean");
const codexDesktop = require("./codex_desktop");

let CODEX_HOME = path.join(os.homedir(), ".codex");
let SESSION_INDEX = path.join(CODEX_HOME, "session_index.jsonl");
let GLOBAL_STATE = path.join(CODEX_HOME, ".codex-global-state.json");
// Codex 桌面端侧栏现在优先读这个本地目录库，而不是直接扫 state_*.sqlite。
// 只改 threads / rollout / session_index 而不同步这里，重启后侧栏仍会空。
let LOCAL_CATALOG_DB = path.join(CODEX_HOME, "sqlite", "codex-dev.db");
const STATE_DB_UPSERT_BATCH_SIZE = 200;

function setCodexDataDir(dir) {
  if (typeof dir !== "string" || !path.isAbsolute(dir)) throw new Error("Codex 数据目录必须是绝对路径");
  CODEX_HOME = path.resolve(dir);
  SESSION_INDEX = path.join(CODEX_HOME, "session_index.jsonl");
  GLOBAL_STATE = path.join(CODEX_HOME, ".codex-global-state.json");
  LOCAL_CATALOG_DB = path.join(CODEX_HOME, "sqlite", "codex-dev.db");
}

// 检测 Codex 是否在运行（尽力而为，跨平台）
function codexRunning() {
  return codexDesktop.codexRunning();
}

// 目标 provider = config.toml 根级 model_provider，缺省 openai
function targetProvider() {
  try {
    const head = fs.readFileSync(path.join(CODEX_HOME, "config.toml"), "utf-8").split("\n[")[0];
    const m = head.match(/^\s*model_provider\s*=\s*["']([^"']+)/m);
    if (m) return m[1];
  } catch {}
  return "openai";
}

function findRollouts() {
  const out = [];
  for (const root of [path.join(CODEX_HOME, "sessions"), path.join(CODEX_HOME, "archived_sessions")]) {
    const walk = (dir) => {
      let ents;
      try {
        ents = fs.readdirSync(dir, { withFileTypes: true });
      } catch {
        return;
      }
      for (const e of ents) {
        const full = path.join(dir, e.name);
        if (e.isDirectory()) walk(full);
        else if (e.isFile() && e.name.startsWith("rollout-") && e.name.endsWith(".jsonl")) out.push(full);
      }
    };
    walk(root);
  }
  return out;
}

function isArchivedRollout(file) {
  return file.startsWith(path.join(CODEX_HOME, "archived_sessions") + path.sep);
}

function loadIndexTitles() {
  const map = {};
  try {
    for (const line of fs.readFileSync(SESSION_INDEX, "utf-8").split("\n")) {
      if (!line.trim()) continue;
      try {
        const d = JSON.parse(line);
        if (d.id && d.thread_name) map[d.id] = cleanThreadTitle(d.thread_name, "", 80);
      } catch {}
    }
  } catch {}
  return map;
}

function parseTimeMs(tsText, fallbackMs = Date.now()) {
  const n = Date.parse(tsText || "");
  return Number.isFinite(n) ? n : fallbackMs;
}

function firstText(value) {
  if (typeof value === "string") return value;
  if (Array.isArray(value)) {
    return value
      .map((x) => (typeof x === "string" ? x : x && typeof x.text === "string" ? x.text : ""))
      .filter(Boolean)
      .join("\n");
  }
  return "";
}

// rollout payload 里的字段不一定是字符串：subagent 会话的 source 就是对象
// {subagent:{thread_spawn:{...}}}。Codex 自身在 state 库里存的是 JSON 字符串；
// 直接把对象绑给 sql.js 会抛 "unknown type" 并让整批 upsert 回滚（Windows 无
// sqlite3 CLI 时走 sql.js 路径，正是这个原因导致恢复后 state 库 0 线程）。
function payloadText(value, fallback) {
  if (typeof value === "string") return value;
  if (value === null || value === undefined) return fallback;
  if (typeof value === "number" || typeof value === "boolean") return String(value);
  try {
    const text = JSON.stringify(value);
    if (typeof text === "string") return text;
  } catch {}
  return fallback;
}

function sql(v) {
  if (v === null || v === undefined) return "NULL";
  if (typeof v === "number") return Number.isFinite(v) ? String(v) : "NULL";
  if (typeof v !== "string") {
    // 对象（如 subagent 的 source）序列化成 JSON 文本，与 Codex 自身存储一致，
    // 避免落入 String(v) 产生无意义的 '[object Object]' 脏行。
    try { v = JSON.stringify(v); } catch { v = String(v); }
  }
  return "'" + String(v).replace(/'/g, "''") + "'";
}

const THREAD_COLUMNS = [
  "id", "rollout_path", "created_at", "updated_at", "source", "model_provider", "cwd", "title",
  "sandbox_policy", "approval_mode", "tokens_used", "has_user_event", "archived", "archived_at",
  "git_sha", "git_branch", "git_origin_url", "cli_version", "first_user_message", "memory_mode",
  "model", "reasoning_effort", "created_at_ms", "updated_at_ms", "thread_source", "preview",
  "recency_at", "recency_at_ms", "history_mode",
];

// Codex 当前 state_5.sqlite 的完整基础 schema。首次安装工具箱且目标目录还没有任何
// state_*.sqlite 时，不能只创建一个 threads 简表：Codex 启动时还会依赖这些索引、触发器
// 和关联表。migration 记录使用当前 Codex 版本的基线，后续版本可继续追加自己的迁移。
const STATE_DB_SCHEMA_SQL = `
CREATE TABLE IF NOT EXISTS _sqlx_migrations (
  version BIGINT PRIMARY KEY,
  description TEXT NOT NULL,
  installed_on TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  success BOOLEAN NOT NULL,
  checksum BLOB NOT NULL,
  execution_time BIGINT NOT NULL
);
CREATE TABLE IF NOT EXISTS thread_sections (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS backfill_state (
  id INTEGER PRIMARY KEY CHECK (id = 1),
  status TEXT NOT NULL,
  last_watermark TEXT,
  last_success_at INTEGER,
  updated_at INTEGER NOT NULL
);
CREATE TABLE IF NOT EXISTS external_agent_config_imports (
  import_id TEXT PRIMARY KEY,
  completed_at_ms INTEGER NOT NULL,
  successes TEXT NOT NULL,
  failures TEXT NOT NULL,
  provider_id TEXT
);
CREATE TABLE IF NOT EXISTS remote_control_enrollments (
  websocket_url TEXT NOT NULL,
  account_id TEXT NOT NULL,
  app_server_client_name TEXT NOT NULL,
  server_id TEXT NOT NULL,
  environment_id TEXT NOT NULL,
  server_name TEXT NOT NULL,
  updated_at INTEGER NOT NULL,
  remote_control_enabled INTEGER,
  PRIMARY KEY (websocket_url, account_id, app_server_client_name)
);
CREATE TABLE IF NOT EXISTS threads (
  id TEXT PRIMARY KEY,
  rollout_path TEXT NOT NULL,
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL,
  source TEXT NOT NULL,
  model_provider TEXT NOT NULL,
  cwd TEXT NOT NULL,
  title TEXT NOT NULL,
  sandbox_policy TEXT NOT NULL,
  approval_mode TEXT NOT NULL,
  tokens_used INTEGER NOT NULL DEFAULT 0,
  has_user_event INTEGER NOT NULL DEFAULT 0,
  archived INTEGER NOT NULL DEFAULT 0,
  archived_at INTEGER,
  git_sha TEXT,
  git_branch TEXT,
  git_origin_url TEXT,
  cli_version TEXT NOT NULL DEFAULT '',
  first_user_message TEXT NOT NULL DEFAULT '',
  agent_nickname TEXT,
  agent_role TEXT,
  memory_mode TEXT NOT NULL DEFAULT 'enabled',
  model TEXT,
  reasoning_effort TEXT,
  agent_path TEXT,
  created_at_ms INTEGER,
  updated_at_ms INTEGER,
  thread_source TEXT,
  preview TEXT NOT NULL DEFAULT '',
  recency_at INTEGER NOT NULL DEFAULT 0,
  recency_at_ms INTEGER NOT NULL DEFAULT 0,
  history_mode TEXT NOT NULL DEFAULT 'legacy',
  name TEXT,
  is_pinned INTEGER NOT NULL DEFAULT 0,
  thread_section_id TEXT REFERENCES thread_sections(id) ON DELETE SET NULL
);
CREATE TABLE IF NOT EXISTS thread_dynamic_tools (
  thread_id TEXT NOT NULL,
  position INTEGER NOT NULL,
  name TEXT NOT NULL,
  description TEXT NOT NULL,
  input_schema TEXT NOT NULL,
  defer_loading INTEGER NOT NULL DEFAULT 0,
  namespace TEXT,
  PRIMARY KEY(thread_id, position),
  FOREIGN KEY(thread_id) REFERENCES threads(id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS thread_spawn_edges (
  parent_thread_id TEXT NOT NULL,
  child_thread_id TEXT NOT NULL PRIMARY KEY,
  status TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_thread_dynamic_tools_thread ON thread_dynamic_tools(thread_id);
CREATE INDEX IF NOT EXISTS idx_thread_spawn_edges_parent_status ON thread_spawn_edges(parent_thread_id, status);
CREATE INDEX IF NOT EXISTS idx_threads_archived ON threads(archived);
CREATE INDEX IF NOT EXISTS idx_threads_archived_cwd_created_at_ms ON threads(archived, cwd, created_at_ms DESC, id DESC);
CREATE INDEX IF NOT EXISTS idx_threads_archived_cwd_recency_at_ms ON threads(archived, cwd, recency_at_ms DESC, id DESC);
CREATE INDEX IF NOT EXISTS idx_threads_archived_cwd_updated_at_ms ON threads(archived, cwd, updated_at_ms DESC, id DESC);
CREATE INDEX IF NOT EXISTS idx_threads_created_at ON threads(created_at DESC, id DESC);
CREATE INDEX IF NOT EXISTS idx_threads_created_at_ms ON threads(created_at_ms DESC, id DESC);
CREATE INDEX IF NOT EXISTS idx_threads_pinned_recency_at_ms ON threads(archived, recency_at_ms DESC, id DESC) WHERE is_pinned = 1 AND preview <> '';
CREATE INDEX IF NOT EXISTS idx_threads_provider ON threads(model_provider);
CREATE INDEX IF NOT EXISTS idx_threads_recency_at_ms ON threads(recency_at_ms DESC, id DESC);
CREATE INDEX IF NOT EXISTS idx_threads_section_recency_at_ms ON threads(archived, thread_section_id, recency_at_ms DESC, id DESC) WHERE thread_section_id IS NOT NULL AND preview <> '';
CREATE INDEX IF NOT EXISTS idx_threads_source ON threads(source);
CREATE INDEX IF NOT EXISTS idx_threads_updated_at ON threads(updated_at DESC, id DESC);
CREATE INDEX IF NOT EXISTS idx_threads_updated_at_ms ON threads(updated_at_ms DESC, id DESC);
CREATE INDEX IF NOT EXISTS idx_threads_visible_created_at_ms ON threads(archived, created_at_ms DESC) WHERE preview <> '';
CREATE INDEX IF NOT EXISTS idx_threads_visible_recency_at_ms ON threads(archived, recency_at_ms DESC, id DESC) WHERE preview <> '';
CREATE INDEX IF NOT EXISTS idx_threads_visible_updated_at_ms ON threads(archived, updated_at_ms DESC) WHERE preview <> '';
CREATE TRIGGER IF NOT EXISTS threads_created_at_ms_after_insert
AFTER INSERT ON threads WHEN NEW.created_at_ms IS NULL
BEGIN UPDATE threads SET created_at_ms = NEW.created_at * 1000 WHERE id = NEW.id; END;
CREATE TRIGGER IF NOT EXISTS threads_created_at_ms_after_update
AFTER UPDATE OF created_at ON threads
WHEN NEW.created_at != OLD.created_at AND NEW.created_at_ms IS OLD.created_at_ms
BEGIN UPDATE threads SET created_at_ms = NEW.created_at * 1000 WHERE id = NEW.id; END;
CREATE TRIGGER IF NOT EXISTS threads_recency_at_after_insert
AFTER INSERT ON threads WHEN NEW.recency_at_ms = 0
BEGIN UPDATE threads SET recency_at = NEW.updated_at, recency_at_ms = COALESCE(NEW.updated_at_ms, NEW.updated_at * 1000) WHERE id = NEW.id; END;
CREATE TRIGGER IF NOT EXISTS threads_updated_at_ms_after_insert
AFTER INSERT ON threads WHEN NEW.updated_at_ms IS NULL
BEGIN UPDATE threads SET updated_at_ms = NEW.updated_at * 1000 WHERE id = NEW.id; END;
CREATE TRIGGER IF NOT EXISTS threads_updated_at_ms_after_update
AFTER UPDATE OF updated_at ON threads
WHEN NEW.updated_at != OLD.updated_at AND NEW.updated_at_ms IS OLD.updated_at_ms
BEGIN UPDATE threads SET updated_at_ms = NEW.updated_at * 1000 WHERE id = NEW.id; END;
`;

const STATE_DB_MIGRATIONS = [
  [1, "threads", "627EF19164C9BB298A0CD99945981C9B7BDA3D9E6CF12EB35145E3B1D3BF7CF8740F0DBAA0B475185FC2993397078049"],
  [2, "logs", "521B72CBC04C7D03B1E4AEF8DC0FDEE672F1F7F5881385A55A0E937E4EBE87A3F67BCA4D3F6B59AFBC9A7CA723C82856"],
  [3, "logs thread id", "E58A2862BDB66D3274E60144A26DC9D68BBEFFF834CD29B82B740F38FCF95862934F412665D985715167839E8F0EB377"],
  [4, "thread dynamic tools", "8ADC251366E4C7E5EA78B620AC71F4C4BDFB8F048BF9F2FD7204226CFBA54C8E83FC9D83FC1EC334BC683BB7F6D0D8A8"],
  [5, "threads cli version", "1AC64F85E083E5B49B974BF498FC39C630F0032057FB4BF4CB31708619FAC5A80189F261CF3E38DAFD92DC406E73C220"],
  [6, "memories", "A203FC4B597E1420BFFDC4E00E214798B21CF7FC921D8BD6F089C75248FB6A4B4942C97A1F8E0271ECA8CF3B3E5E7A80"],
  [7, "threads first user message", "7E35A464DF6D4C82694DE50865BA3FE9104F9E1385A33BE7D633AB4B8D6CF60B7F369FE083D31251FD77B75FA2D61F82"],
  [8, "backfill state", "AA3F287F7A448554E7AF01F259F0871CE90ACE1A7D4B8EBBD74C87AE57C3E21119F7EEC8B9C2E9F4A9E76E63A0F0B60A"],
  [9, "stage1 outputs rollout slug", "8B469360777E6DD6C21D3F0EB93481251846B0A50E0A44EB0DBEE4A0A32B7206768A5D83A5FCEC30C6FD2E43D755FF1B"],
  [10, "logs process id", "8ADAF1365F8E5DEEBA68BDEEE9FD83B094A87BDAB364D95AB5C714F7B8BD95DC3B38576871470312154C9D41E8F10F93"],
  [11, "logs partition prune indexes", "31ECECBD07F04452D0DC5127D824ED183056B9900A31AA739387A4AEF232016D975110829814841FB6BF3FD23634B799"],
  [12, "logs estimated bytes", "0012A213ED25F2CF806C2530C487EBB00B4F102F5960D1A852154A0F77D25C9E9F7DF29550FAEB30EBAFF15A26C7D604"],
  [13, "threads agent nickname", "05526A0900A8430523A9A05932EA61302FCE20782B245498565C1B9A2DFD10CF5C49932089B496AE23AF44F6C5F76AF4"],
  [14, "agent jobs", "4D2FBB2442C5C5C7C47B4254B5B8DC2C52129F65C4A952A79F1EC5C40FB07483326429CE5ACDBD372BC9262352CB6277"],
  [15, "agent jobs max runtime seconds", "0EECE6DB59C948FAA6608526AF440A9F53F5A982D27F3E866CD9F1401C2E59BC61F8381B5A1C9744ACB232F50DB493E5"],
  [16, "memory usage", "4768F743E981293735C6E07B0282EC8ACE98AF720059D68555B17979D28C94B0C893A97A950D5E32758BC6D92D8FE6FF"],
  [17, "phase2 selection flag", "4CBDB2EDFEBF6040858FFC193CFFA36E6B3FBF508F8418AD073081BBFCF59D53D423B6EB4C0DED206AB4D4C1A0EFED94"],
  [18, "phase2 selection snapshot", "A4363003BC44F0A4AE1EED53B536B7175F97863EA6881A059DC6642E70E2921576204697CEC2345F560E65FF7FBF4455"],
  [19, "thread dynamic tools defer loading", "3884C02F080F0184E92B620F870DFBB840A8EC5DFCE2FE6455DD8197FCD27D7DD86E63AD6EC7C4091A1161D9ED199F8D"],
  [20, "threads model reasoning effort", "07673F4859F740134569AB9606760D8173ED6DF92DA47C86FBD0CEEB333E18AB7AC91D525AB8C11503C42164FD4167AE"],
  [21, "thread spawn edges", "EA1AEBE4F5A1D56C8EFFCC15F705C272275DD8ADFEB01BC97D3F7B283474A3B8C2B0C8C69DE55786E8BDA17CAE6F4DE7"],
  [22, "threads agent path", "160FE01B757DD06F9573B9030952D021F2B148732BEA5FC5F5AE09E9227F3F83CA676695EDAF8502669FCB546119DC94"],
  [23, "drop logs", "E6C748A9A18286E4C773FFE0DAD659F16FB7E5B7B032235815991C405F2D32C068F3EDDE920D0FCF589B687BFFCFEE2E"],
  [24, "remote control enrollments", "E92AA526B1F36E26F6221C04A671F07B27DE2E7A5112D93A8C3AF816A87846E7804D5FBAD689E61CF4EC91FD79D3BD8A"],
  [25, "thread timestamps millis", "D6FB51D79941B93C3278FE2A8A0C6FA882407B7B716E32D72329F0B7B60C181A42F49E56CD552315EFB0402A111EDE12"],
  [26, "thread dynamic tools namespace", "6C62A325D3B9CA8E424EACB9E3890D8D9DE57C3725E2D1FA30C3743AE779FCA2969387174359DFB26E4920791AE7E831"],
  [27, "threads cwd sort indexes", "D84DCA6A039E004C0C7D06C5A16C2CCC363137537F3DD9898F50BB013CE9067140761B87D58CA742D769D35976863FC3"],
  [28, "device key bindings", "EF44ADAA291E40DFE6B74E3CD259711ADAE13F01C5594B177D08E2D2041A67ED3813FEECF049662AAED0F636BD3F4AC3"],
  [29, "thread goals", "E9EC76F97DBF41CE4949DE324A2A26538C2025C0AC4F272B77A852970FB14B7EC970D6978A765F91AE171DCE9C0E4149"],
  [30, "threads thread source", "80C1CDACD4520B42B18B960164912685591A7EE041645F35609A2EE2792EB474B7B0B758366556982D8CDE2ACB583672"],
  [31, "drop device key bindings", "943675A9B3DC92D0B2731AE7D2FE324FF1A40B6366B9D5D8B4CB47CA885C08BEB6EBB1D776A5A47C878B4094DDE70EA0"],
  [32, "threads preview", "3C01D615C5E3ABA0FD858B7168E73881598D7D33A0ECC41527BDF6595A199D1AEC2FF9F3B3000A57241C500D81B34259"],
  [33, "thread goal stopped statuses", "4904AAE354E3D8CE32AC21D2AE579FD708446AE1DFB2649FC9DD22DC9279B91A2BBB02E6F01B3E0ABD1088177BF2F030"],
  [34, "drop thread goals", "D6FEA739C76EBDEF35A23F06E54EFB5904D7987FB6B663DB1EBC98EB9B192305F454932BFBAA2231AEDCA6209903A5F3"],
  [35, "drop memory tables", "D7B56ACB07C96858BDC7CA4C53DB51EB71C6A19EF2C524FF68C2660A6FE0D23211A6D39CB905F01C42FFB7C8EE913BBC"],
  [36, "threads visible sort indexes", "6381F7BB4C36BE41C6106B8523EA6C68F5A00273A5522155B7B84BAD26D152436F3375DF7097CE3ABF2421AEC32641ED"],
  [37, "remote control enrollments enabled", "F6A5AB0DB36BB9E45AB5285B56090FB453DF0A4D4D8D62EACE234F0B557F4203C6C455FC67BD7D6E72623C3979EC9405"],
  [38, "external agent config imports", "E7778522CBE529F5E2BA179B502AEE8E5C21272AD820E672AB0A565F7EDC9BD0F29171CCF9815C0ACC0F89EAE0A2B1FC"],
  [39, "threads recency at", "C217B3D14C08C23603F485AF11452EE2B68F65FA8DBAF215F33F53B608C092BE8738DE3F63D6A38F120EDEA5AF247C17"],
  [40, "threads history mode", "A7A99674F90A43184E43D66595C4F1DA50AE8715A5BFCB1579A2B7B10668335D7F7AB6B6F2D7A7A379272773D803A914"],
  [41, "threads name", "0DC734DBE4CBCC5AD5A8BBC2FDC1CF770DDEA3CB53EB36C55A4DA6FA2E8BE4CC8E68672F43AC953E727E415E45C50A78"],
  [42, "drop agent jobs", "CFDC4FD47328D1B67C0C7555125DE4FB4ADD5B3B57FD31AC1987E4D8B0C99B1F5418DB9EE7EC2158AED8A2E3DFCFD636"],
  [43, "threads is pinned", "605128E722BD2521C7E979E9961C16706435E442B3E3A8EFA36F6B16C42E3833090906D9A62FDE097A109B78E29DA830"],
  [44, "external agent config imports provider id", "5D29223BD1CAFE456A4AF992A545E8EC420B71331EB12A7427E26512B8A44117D9445B83562F095B3A5A0F11C4F091D2"],
  [45, "threads section", "A7FADC8CAEA8B6ABEB108F588A91B8E5D1C9E2645CE990F2251B9F0234E6E53D78FC854F1C88C7217CC143016539D691"],
];

function threadColumns(cols) {
  return THREAD_COLUMNS.filter((c) => cols.has(c));
}

function threadValue(record, target, column) {
  const created = Math.floor(record.created_at_ms / 1000);
  const updated = Math.floor(record.updated_at_ms / 1000);
  const values = {
    id: record.id,
    rollout_path: record.rollout_path,
    created_at: created,
    updated_at: updated,
    source: record.source,
    model_provider: target,
    cwd: record.cwd,
    title: record.title,
    sandbox_policy: record.sandbox_policy,
    approval_mode: record.approval_mode,
    tokens_used: record.tokens_used,
    has_user_event: record.has_user_event,
    archived: record.archived,
    archived_at: record.archived ? updated : null,
    git_sha: record.git_sha,
    git_branch: record.git_branch,
    git_origin_url: record.git_origin_url,
    cli_version: record.cli_version,
    first_user_message: record.first_user_message,
    memory_mode: "enabled",
    model: record.model,
    reasoning_effort: record.reasoning_effort,
    created_at_ms: Math.round(record.created_at_ms),
    updated_at_ms: Math.round(record.updated_at_ms),
    thread_source: record.thread_source,
    preview: record.preview,
    recency_at: updated,
    recency_at_ms: Math.round(record.updated_at_ms),
    history_mode: record.history_mode,
  };
  return values[column];
}

function threadUpdateSet(columns, target, parameterized = false) {
  return columns.filter((c) => c !== "id").map((c) => {
    if (c === "model_provider") return `${c}=${parameterized ? "?" : sql(target)}`;
    if (c === "has_user_event") {
      const subagent = columns.includes("thread_source")
        ? "LOWER(COALESCE(excluded.thread_source,''))='subagent'"
        : "0";
      return `${c}=CASE WHEN ${subagent} THEN 0 WHEN excluded.${c}=1 THEN 1 ELSE threads.${c} END`;
    }
    if (["cwd", "first_user_message", "preview", "thread_source"].includes(c)) {
      return `${c}=CASE WHEN COALESCE(excluded.${c},'')<>'' THEN excluded.${c} ELSE threads.${c} END`;
    }
    if (c === "title") {
      return `${c}=CASE WHEN COALESCE(threads.${c},'')='' OR threads.${c}='(无标题对话)' THEN excluded.${c} ELSE threads.${c} END`;
    }
    if (["updated_at", "updated_at_ms", "recency_at", "recency_at_ms"].includes(c)) {
      return `${c}=MAX(threads.${c}, excluded.${c})`;
    }
    return `${c}=excluded.${c}`;
  });
}

function sqlite3Available() {
  const r = spawnSync("sqlite3", ["--version"], { encoding: "utf-8" });
  return !r.error && r.status === 0;
}

function runSqlite3(dbPath, input) {
  const r = spawnSync("sqlite3", [dbPath], {
    input,
    encoding: "utf-8",
    maxBuffer: 20 * 1024 * 1024,
  });
  if (r.error || r.status !== 0) {
    throw new Error((r.stderr || r.error && r.error.message || "sqlite3 执行失败").trim());
  }
  return r.stdout || "";
}

function sqliteCols(dbPath) {
  const out = runSqlite3(dbPath, "PRAGMA table_info(threads);\n");
  return new Set(out.split("\n").map((line) => line.split("|")[1]).filter(Boolean));
}

function readRolloutRecord(file, titles) {
  let content;
  try {
    content = fs.readFileSync(file, "utf-8");
  } catch {
    return null;
  }
  const stat = fs.statSync(file);
  const rec = {
    id: "",
    rollout_path: file,
    cwd: "",
    source: "vscode",
    model_provider: "",
    thread_source: "user",
    title: "",
    first_user_message: "",
    preview: "",
    has_user_event: content.includes('"user_message"') || content.includes('"user_input"') ? 1 : 0,
    archived: isArchivedRollout(file) ? 1 : 0,
    archived_at: null,
    created_at_ms: stat.birthtimeMs || stat.mtimeMs || Date.now(),
    updated_at_ms: stat.mtimeMs || Date.now(),
    sandbox_policy: JSON.stringify({ type: "disabled" }),
    approval_mode: "never",
    tokens_used: 0,
    cli_version: "",
    git_sha: null,
    git_branch: null,
    git_origin_url: null,
    model: null,
    reasoning_effort: null,
    history_mode: "legacy",
  };
  for (const line of content.split("\n")) {
    if (!line.trim()) continue;
    let d;
    try {
      d = JSON.parse(line);
    } catch {
      continue;
    }
    const p = d.payload || {};
    if (d.type === "session_meta" && !rec.id) {
      rec.id = p.id || p.session_id || rec.id;
      rec.cwd = payloadText(p.cwd, rec.cwd);
      rec.source = payloadText(p.source, rec.source);
      rec.model_provider = payloadText(p.model_provider, rec.model_provider);
      rec.thread_source = payloadText(p.thread_source, rec.thread_source);
      rec.cli_version = payloadText(p.cli_version, rec.cli_version);
      rec.history_mode = payloadText(p.history_mode, rec.history_mode);
      rec.created_at_ms = parseTimeMs(p.timestamp || d.timestamp, rec.created_at_ms);
      if (p.git && typeof p.git === "object") {
        rec.git_sha = p.git.commit_hash || null;
        rec.git_branch = p.git.branch || null;
        rec.git_origin_url = p.git.repository_url || null;
      }
    } else if (d.type === "event_msg" && (p.type === "user_message" || p.type === "user_input")) {
      rec.has_user_event = 1;
      if (!rec.first_user_message) rec.first_user_message = firstText(p.message || p.input).trim();
    }
  }
  if (
    String(rec.thread_source || "").toLowerCase() === "subagent"
    || (rec.source && typeof rec.source === "object" && rec.source.subagent)
    || (typeof rec.source === "string" && /"subagent"\s*:/.test(rec.source))
  ) {
    rec.thread_source = "subagent";
    rec.has_user_event = 0;
  }
  rec.updated_at_ms = Math.max(rec.updated_at_ms, rec.created_at_ms);
  rec.title = cleanThreadTitle(titles[rec.id] || "", rec.first_user_message, 80) || "(无标题对话)";
  rec.preview = cleanUserVisibleText(rec.first_user_message).replace(/\s+/g, " ").trim().slice(0, 200);
  return rec.id ? rec : null;
}

function buildThreadUpsertSql(records, target, cols, opts = {}) {
  const desired = threadColumns(cols);
  const updateSet = threadUpdateSet(desired, target);
  const lines = ["BEGIN;"];
  for (const r of records) {
    lines.push(
      `INSERT INTO threads (${desired.join(",")}) VALUES (${desired.map((c) => sql(threadValue(r, target, c))).join(",")}) ` +
      `ON CONFLICT(id) DO UPDATE SET ${updateSet.join(",")};`
    );
  }
  if (opts.updateAllProviders !== false) {
    // 【增强】确保数据库中所有会话的 provider 都更新为目标（兜底逻辑）
    lines.push(`UPDATE threads SET model_provider=${sql(target)} WHERE COALESCE(model_provider,'')<>${sql(target)};`);
  }
  lines.push("COMMIT;");
  return lines.join("\n") + "\n";
}

function chunksOf(items, size) {
  const chunks = [];
  for (let i = 0; i < items.length; i += size) chunks.push(items.slice(i, i + size));
  return chunks;
}

function updateProjectState(records, log = () => {}) {
  if (!fs.existsSync(GLOBAL_STATE)) return { addedProjects: 0, removedProjectless: 0 };
  let state;
  try {
    state = JSON.parse(fs.readFileSync(GLOBAL_STATE, "utf-8"));
  } catch (e) {
    log(`全局项目索引跳过：${e.message || e}`);
    return { addedProjects: 0, removedProjectless: 0 };
  }
  const projects = [...new Set(records.map((r) => r.cwd).filter(Boolean))];
  const idsWithProject = new Set(records.filter((r) => r.cwd).map((r) => r.id));
  const before = Array.isArray(state["project-order"]) ? state["project-order"] : [];
  const seen = new Set(before);
  state["project-order"] = before.concat(projects.filter((p) => !seen.has(p)));
  const oldProjectless = Array.isArray(state["projectless-thread-ids"]) ? state["projectless-thread-ids"] : [];
  state["projectless-thread-ids"] = oldProjectless.filter((id) => !idsWithProject.has(id));
  const addedProjects = state["project-order"].length - before.length;
  const removedProjectless = oldProjectless.length - state["projectless-thread-ids"].length;
  if (addedProjects || removedProjectless) {
    fs.writeFileSync(GLOBAL_STATE, JSON.stringify(state, null, 2) + "\n", "utf-8");
    log(`项目索引更新：新增项目 ${addedProjects} 个，移出无项目会话 ${removedProjectless} 条`);
  }
  return { addedProjects, removedProjectless };
}

function ts() {
  const d = new Date();
  const p = (n) => String(n).padStart(2, "0");
  return `${d.getFullYear()}${p(d.getMonth() + 1)}${p(d.getDate())}-${p(d.getHours())}${p(d.getMinutes())}${p(d.getSeconds())}`;
}

// 【增强】重建 session_index.jsonl：确保所有会话都在索引中（切账号后必要）
function rebuildSessionIndex(records, log = () => {}) {
  if (!records || !records.length) {
    log("索引重建：无会话记录，跳过");
    return { rebuilt: 0 };
  }
  // 备份旧索引
  if (fs.existsSync(SESSION_INDEX)) {
    const backup = SESSION_INDEX + ".bak-" + ts();
    try {
      fs.copyFileSync(SESSION_INDEX, backup);
      log(`已备份旧索引 → ${path.basename(backup)}`);
    } catch (e) {
      log(`索引备份失败：${e.message || e}`);
    }
  }
  // 读取现有索引，保留用户重命名的标题
  const existingTitles = {};
  try {
    const lines = fs.readFileSync(SESSION_INDEX, "utf-8").split("\n");
    for (const line of lines) {
      if (!line.trim()) continue;
      try {
        const d = JSON.parse(line);
        if (d.id && d.thread_name) existingTitles[d.id] = d.thread_name;
      } catch {}
    }
  } catch {}
  // 重建索引：每个会话一行 JSON
  const lines = [];
  for (const r of records) {
    if (!r.id) continue;
    const indexEntry = {
      id: r.id,
      thread_name: cleanThreadTitle(existingTitles[r.id] || "", r.first_user_message || r.title, 80) || r.title || "(无标题对话)",
      project_path: r.cwd || null,
      created_at: new Date(r.created_at_ms).toISOString(),
      source: r.source || "vscode",
      archived: r.archived ? true : false,
    };
    lines.push(JSON.stringify(indexEntry));
  }
  // 写入新索引
  try {
    fs.writeFileSync(SESSION_INDEX, lines.join("\n") + "\n", "utf-8");
    log(`索引重建完成：${lines.length} 条会话`);
    return { rebuilt: lines.length };
  } catch (e) {
    log(`索引重建失败：${e.message || e}`);
    return { rebuilt: 0, error: e.message || String(e) };
  }
}

function secTs(ms) {
  const n = Number(ms);
  if (!Number.isFinite(n) || n <= 0) return Date.now() / 1000;
  // 兼容秒 / 毫秒两种时间戳
  return n > 1e12 ? n / 1000 : n;
}

// 重建 Codex 桌面端侧栏目录（~/.codex/sqlite/codex-dev.db 的 local_thread_catalog）。
// 新版 ChatGPT/Codex 启动后侧栏主要读这里；只同步 state_5.sqlite 不会让历史会话重新出现。
function rebuildLocalThreadCatalog(records, target, log = () => {}) {
  if (!records || !records.length) {
    log("侧栏目录重建：无会话记录，跳过");
    return { rebuilt: 0, skipped: true };
  }
  if (!fs.existsSync(LOCAL_CATALOG_DB)) {
    log(`侧栏目录重建跳过：未找到 ${LOCAL_CATALOG_DB}`);
    return { rebuilt: 0, skipped: true, missing: true };
  }

  // 只回填未归档、有标题/预览/用户消息的主会话。
  // 注意：新版 Codex 从某版本起大量线程 has_user_event 默认写成 0，不能再只靠这个字段过滤。
  const visible = [];
  const seen = new Set();
  for (const r of records) {
    if (!r || !r.id || seen.has(r.id) || r.archived) continue;
    if (String(r.thread_source || "").toLowerCase() === "subagent") continue;
    const hasText = !!(r.first_user_message || r.preview || (r.title && r.title !== "(无标题对话)"));
    const hasUser = !!r.has_user_event || hasText;
    if (!hasUser) continue;
    seen.add(r.id);
    visible.push(r);
  }
  if (!visible.length) {
    log("侧栏目录重建：没有可展示会话，跳过");
    return { rebuilt: 0, skipped: true };
  }

  // 备份目录库
  try {
    const bak = LOCAL_CATALOG_DB + ".bak-" + ts();
    fs.copyFileSync(LOCAL_CATALOG_DB, bak);
    log(`已备份侧栏目录库 → ${path.basename(bak)}`);
  } catch (e) {
    log(`侧栏目录库备份失败：${e.message || e}`);
  }

  const nowSec = Date.now() / 1000;
  let maxUpdated = 0;
  const valuesSql = [];
  visible.forEach((r, idx) => {
    const created = secTs(r.created_at_ms);
    const updated = Math.max(secTs(r.updated_at_ms), created);
    if (updated > maxUpdated) maxUpdated = updated;
    const title = cleanThreadTitle(r.title || "", r.first_user_message || r.preview || "", 80) || "(无标题对话)";
    const sourceKind = String(r.source || "vscode").slice(0, 64) || "vscode";
    const provider = target || r.model_provider || "";
    const cwd = r.cwd || "";
    const gitBranch = r.git_branch || null;
    // observation_sequence 用递增序号，避免全 0 被后续 reconcile 误判
    const obs = idx + 1;
    valuesSql.push(
      `(${sql("local")},${sql(r.id)},${sql(title)},${sql(created)},${sql(updated)},${sql(cwd)},` +
        `${sql(sourceKind)},NULL,${sql(provider)},${sql(gitBranch)},${sql(obs)},0)`
    );
  });

  const sqlText = [
    "BEGIN;",
    "DELETE FROM local_thread_catalog WHERE host_id='local';",
    "INSERT INTO local_thread_catalog (" +
      "host_id, thread_id, display_title, source_created_at, source_updated_at, cwd, " +
      "source_kind, source_detail, model_provider, git_branch, observation_sequence, missing_candidate" +
      ") VALUES " +
      valuesSql.join(",\n") +
      ";",
    // 标记冷构建完成，避免启动后又被半残状态卡住
    "INSERT OR IGNORE INTO local_thread_catalog_sync_state (host_id) VALUES ('local');",
    "UPDATE local_thread_catalog_sync_state SET " +
      `watermark_updated_at=${sql(maxUpdated || nowSec)}, ` +
      "initial_build_complete=1, " +
      `observation_sequence=${sql(visible.length)}, ` +
      `last_full_reconciled_at=${sql(Math.floor(nowSec))} ` +
      "WHERE host_id='local';",
    "INSERT OR IGNORE INTO local_thread_catalog_hosts (host_id, host_kind) VALUES ('local','local');",
    "INSERT OR IGNORE INTO local_thread_catalog_metadata (id, catalog_revision) VALUES (1, 0);",
    "UPDATE local_thread_catalog_metadata SET catalog_revision=COALESCE(catalog_revision,0)+1 WHERE id=1;",
    "COMMIT;",
    "PRAGMA wal_checkpoint(TRUNCATE);",
  ].join("\n") + "\n";

  try {
    if (sqlite3Available()) {
      runSqlite3(LOCAL_CATALOG_DB, sqlText);
    } else {
      // 无 sqlite3 CLI 时走 sql.js（同步路径里已有依赖）
      throw new Error("sqlite3 CLI 不可用，改走 sql.js");
    }
    log(`侧栏目录重建完成：${visible.length} 条（provider=${target}）`);
    return { rebuilt: visible.length };
  } catch (e) {
    // sql.js 兜底
    try {
      // 这里用同步 require 的 initSqlJs 需要 await，调用方是 async，所以抛回给上层处理
      return { rebuilt: 0, error: e.message || String(e), needSqlJs: true, sqlText, visibleCount: visible.length };
    } catch (e2) {
      log(`侧栏目录重建失败：${e.message || e}`);
      return { rebuilt: 0, error: e.message || String(e) };
    }
  }
}

// Codex 桌面端对 codex-dev.db 有一套版本化迁移（PRAGMA user_version，当前迁移链到 v30，
// 依据 ChatGPT.app 0.147.x 的 app.asar 反编译核对）。工具箱自建该库时必须把 user_version
// 对齐到链尾：否则 Codex 会从 v0 重跑全部迁移，其中 v27/v28/v29 会把
// local_thread_catalog_sync_state.last_full_reconciled_at 置 NULL，首次启动即触发
// full reconcile——若 state 库线程没写成功，侧栏会话会"闪现后全部消失"。
// Codex 升级新增 v31+ 迁移时会自然在此基础上继续，不受影响。
const CATALOG_DB_USER_VERSION = 30;

function createLocalCatalogSchema(db) {
  db.run(`CREATE TABLE IF NOT EXISTS local_thread_catalog (
    host_id TEXT NOT NULL,
    thread_id TEXT NOT NULL,
    display_title TEXT NOT NULL,
    source_created_at REAL NOT NULL,
    source_updated_at REAL NOT NULL,
    cwd TEXT NOT NULL,
    source_kind TEXT NOT NULL,
    source_detail TEXT,
    model_provider TEXT NOT NULL,
    git_branch TEXT,
    observation_sequence INTEGER NOT NULL,
    missing_candidate INTEGER NOT NULL DEFAULT 0 CHECK (missing_candidate IN (0, 1)),
    thread_source TEXT,
    source_recency_at REAL NOT NULL DEFAULT 0,
    PRIMARY KEY (host_id, thread_id)
  )`);
  db.run("CREATE INDEX IF NOT EXISTS local_thread_catalog_created_idx ON local_thread_catalog (host_id, source_created_at DESC, source_updated_at DESC, thread_id) WHERE missing_candidate=0");
  db.run("CREATE INDEX IF NOT EXISTS local_thread_catalog_cwd_created_idx ON local_thread_catalog (host_id, cwd, source_created_at DESC, source_updated_at DESC, thread_id) WHERE missing_candidate=0");
  db.run("CREATE INDEX IF NOT EXISTS local_thread_catalog_updated_idx ON local_thread_catalog (host_id, source_recency_at DESC, source_created_at DESC, thread_id) WHERE missing_candidate=0");
  db.run("CREATE INDEX IF NOT EXISTS local_thread_catalog_cwd_updated_idx ON local_thread_catalog (host_id, cwd, source_recency_at DESC, source_created_at DESC, thread_id) WHERE missing_candidate=0");
  db.run(`CREATE TABLE IF NOT EXISTS local_thread_catalog_sync_state (
    host_id TEXT PRIMARY KEY,
    watermark_updated_at REAL,
    initial_build_complete INTEGER NOT NULL DEFAULT 0,
    observation_sequence INTEGER NOT NULL DEFAULT 0,
    last_full_reconciled_at INTEGER
  )`);
  db.run("CREATE TABLE IF NOT EXISTS local_thread_catalog_hosts (host_id TEXT PRIMARY KEY, host_kind TEXT NOT NULL CHECK (host_kind IN ('local','ssh','wsl','remote-control')))");
  db.run("CREATE TABLE IF NOT EXISTS local_thread_catalog_metadata (id INTEGER PRIMARY KEY CHECK (id=1), catalog_revision INTEGER NOT NULL DEFAULT 0)");
}

async function rebuildLocalThreadCatalogAsync(records, target, log = () => {}) {
  if (!records || !records.length) {
    log("侧栏目录重建：无会话记录，跳过");
    return { rebuilt: 0, skipped: true };
  }
  const visible = [];
  const seen = new Set();
  for (const r of records) {
    if (!r || !r.id || seen.has(r.id) || r.archived) continue;
    if (String(r.thread_source || "").toLowerCase() === "subagent") continue;
    const hasText = !!(r.first_user_message || r.preview || (r.title && r.title !== "(无标题对话)"));
    if (!(r.has_user_event || hasText)) continue;
    seen.add(r.id);
    visible.push(r);
  }
  if (!visible.length) {
    log("侧栏目录重建：没有可展示会话，跳过");
    return { rebuilt: 0, skipped: true };
  }

  let db;
  const existed = fs.existsSync(LOCAL_CATALOG_DB);
  try {
    const SQL = await initSqlJs({
      locateFile: (f) =>
        path.join(global.__BUNDLE_ROOT__ || path.join(__dirname, "..", ".."), "node_modules", "sql.js", "dist", f),
    });
    fs.mkdirSync(path.dirname(LOCAL_CATALOG_DB), { recursive: true });
    db = existed ? new SQL.Database(fs.readFileSync(LOCAL_CATALOG_DB)) : new SQL.Database();
    createLocalCatalogSchema(db);
    if (!existed) db.run(`PRAGMA user_version = ${CATALOG_DB_USER_VERSION}`);

    if (existed) {
      try {
        const bak = LOCAL_CATALOG_DB + ".bak-" + ts();
        fs.copyFileSync(LOCAL_CATALOG_DB, bak);
        log(`已备份侧栏目录库 → ${path.basename(bak)}`);
      } catch (e) {
        log(`侧栏目录库备份失败：${e.message || e}`);
      }
    }

    const cols = sqlJsTableColumns(db, "local_thread_catalog");
    const insertCols = [
      "host_id", "thread_id", "display_title", "source_created_at", "source_updated_at", "cwd",
      "source_kind", "source_detail", "model_provider", "git_branch", "observation_sequence",
      "missing_candidate", "thread_source", "source_recency_at",
    ].filter((c) => cols.has(c));
    const nowSec = Date.now() / 1000;
    let maxUpdated = 0;
    const valuesFor = (r, idx) => {
      const created = secTs(r.created_at_ms);
      const updated = Math.max(secTs(r.updated_at_ms), created);
      maxUpdated = Math.max(maxUpdated, updated);
      const title = cleanThreadTitle(r.title || "", r.first_user_message || r.preview || "", 80) || "(无标题对话)";
      const values = {
        host_id: "local",
        thread_id: r.id,
        display_title: title,
        source_created_at: created,
        source_updated_at: updated,
        cwd: r.cwd || "",
        source_kind: String(r.source || "vscode").slice(0, 64) || "vscode",
        source_detail: null,
        model_provider: target || r.model_provider || "",
        git_branch: r.git_branch || null,
        observation_sequence: idx + 1,
        missing_candidate: 0,
        thread_source: r.thread_source || "user",
        source_recency_at: updated,
      };
      return insertCols.map((c) => values[c]);
    };
    const insert = db.prepare(
      `INSERT INTO local_thread_catalog (${insertCols.join(",")}) VALUES (${insertCols.map(() => "?").join(",")})`
    );
    db.run("BEGIN");
    db.run("DELETE FROM local_thread_catalog WHERE host_id='local'");
    visible.forEach((r, idx) => insert.run(valuesFor(r, idx)));
    insert.free();
    db.run("INSERT OR IGNORE INTO local_thread_catalog_sync_state (host_id) VALUES ('local')");
    db.run(
      "UPDATE local_thread_catalog_sync_state SET watermark_updated_at=?, initial_build_complete=1, observation_sequence=?, last_full_reconciled_at=? WHERE host_id='local'",
      [maxUpdated || nowSec, visible.length, Math.floor(nowSec)]
    );
    db.run("INSERT OR IGNORE INTO local_thread_catalog_hosts (host_id, host_kind) VALUES ('local','local')");
    db.run("INSERT OR IGNORE INTO local_thread_catalog_metadata (id, catalog_revision) VALUES (1,0)");
    db.run("UPDATE local_thread_catalog_metadata SET catalog_revision=COALESCE(catalog_revision,0)+1 WHERE id=1");
    db.run("COMMIT");
    const exported = Buffer.from(db.export());
    db.close();
    db = null;
    fs.writeFileSync(LOCAL_CATALOG_DB, exported);
    try { fs.rmSync(LOCAL_CATALOG_DB + "-wal", { force: true }); } catch {}
    try { fs.rmSync(LOCAL_CATALOG_DB + "-shm", { force: true }); } catch {}
    log(`侧栏目录重建完成（sql.js）：${visible.length} 条（provider=${target}）`);
    return { rebuilt: visible.length, created: !existed };
  } catch (e) {
    if (db) try { db.run("ROLLBACK"); } catch {}
    if (db) try { db.close(); } catch {}
    log(`侧栏目录重建失败：${e.message || e}`);
    return { rebuilt: 0, error: e.message || String(e) };
  }
}

// 兜底：把 rollout 已确认有用户消息、但 state DB 仍为 0 的 has_user_event 强制拉起来。
// 新版 Codex 从 7/17 起大量线程默认写成 0，仅靠 upsert 的 CASE 也可能被后续状态覆盖。
function forceHasUserEventSql(userEventIds) {
  if (!userEventIds || !userEventIds.size) return "";
  const ids = [...userEventIds].map((id) => sql(id));
  // 分批，避免单条 SQL 过长
  const chunks = [];
  for (let i = 0; i < ids.length; i += 200) chunks.push(ids.slice(i, i + 200));
  return chunks
    .map((part) => `UPDATE threads SET has_user_event=1 WHERE id IN (${part.join(",")}) AND COALESCE(has_user_event,0)<>1;`)
    .join("\n");
}

function forceSubagentNoUserEventSql(columns) {
  if (!columns || !columns.has("has_user_event")) return "";
  const checks = [];
  if (columns.has("thread_source")) checks.push("LOWER(COALESCE(thread_source,''))='subagent'");
  if (columns.has("source")) checks.push("LOWER(COALESCE(source,'')) LIKE '%\"subagent\"%'");
  if (!checks.length) return "";
  return `UPDATE threads SET has_user_event=0 WHERE COALESCE(has_user_event,0)<>0 AND (${checks.join(" OR ")});`;
}

function backupSqlite3(dbPath, suffix) {
  const backupDir = path.join(CODEX_HOME, "backups_state", "visibility-repair");
  fs.mkdirSync(backupDir, { recursive: true });
  const backupPath = path.join(backupDir, `${path.basename(dbPath)}.${suffix}.bak`);
  const escaped = backupPath.replace(/'/g, "''");
  runSqlite3(dbPath, `.timeout 5000\n.backup '${escaped}'\n`);
  return backupPath;
}

// 旧版本曾把 subagent 当成顶层用户会话写入 state/catalog。这里做轻量幂等修复，
// 不扫描或改写大体积 rollout；sqlite3 可用时允许与 Codex 的 WAL 连接并发事务。
// Windows 无 sqlite3 CLI 且 Codex 正在运行时不冒险整库覆盖，等下次 Codex 退出后再修。
async function repairLegacySessionVisibility(log = () => {}) {
  const stateDbNames = (() => {
    try { return fs.readdirSync(CODEX_HOME).filter((name) => /^state_.*\.sqlite$/.test(name)); } catch { return []; }
  })();
  const suffix = ts();
  const result = { stateRows: 0, catalogRows: 0, backups: [], skipped: false };

  if (sqlite3Available()) {
    for (const name of stateDbNames) {
      const dbPath = path.join(CODEX_HOME, name);
      const cols = sqliteCols(dbPath);
      const clearSql = forceSubagentNoUserEventSql(cols);
      if (!clearSql) continue;
      const before = Number(runSqlite3(
        dbPath,
        `SELECT COUNT(*) FROM threads WHERE COALESCE(has_user_event,0)<>0 AND (` +
          `${cols.has("thread_source") ? "LOWER(COALESCE(thread_source,''))='subagent'" : "0"}` +
          `${cols.has("source") ? `${cols.has("thread_source") ? " OR " : ""}LOWER(COALESCE(source,'')) LIKE '%\"subagent\"%'` : ""}` +
        ");\n"
      ).trim() || 0);
      if (!before) continue;
      result.backups.push(backupSqlite3(dbPath, suffix));
      runSqlite3(dbPath, `.timeout 5000\nBEGIN IMMEDIATE;\n${clearSql}\nCOMMIT;\n`);
      result.stateRows += before;
    }
    if (fs.existsSync(LOCAL_CATALOG_DB)) {
      const catalogCols = new Set(
        runSqlite3(LOCAL_CATALOG_DB, "PRAGMA table_info(local_thread_catalog);\n")
          .split("\n").map((line) => line.split("|")[1]).filter(Boolean)
      );
      if (catalogCols.has("thread_source")) {
        const before = Number(runSqlite3(
          LOCAL_CATALOG_DB,
          "SELECT COUNT(*) FROM local_thread_catalog WHERE LOWER(COALESCE(thread_source,''))='subagent';\n"
        ).trim() || 0);
        if (before) {
          result.backups.push(backupSqlite3(LOCAL_CATALOG_DB, suffix));
          runSqlite3(
            LOCAL_CATALOG_DB,
            ".timeout 5000\nBEGIN IMMEDIATE;\n" +
              "DELETE FROM local_thread_catalog WHERE LOWER(COALESCE(thread_source,''))='subagent';\n" +
              "UPDATE local_thread_catalog_metadata SET catalog_revision=COALESCE(catalog_revision,0)+1 WHERE id=1;\n" +
              "COMMIT;\n"
          );
          result.catalogRows = before;
        }
      }
    }
    if (result.stateRows || result.catalogRows) {
      log(`启动修复会话目录：state ${result.stateRows} 条，侧栏移除 subagent ${result.catalogRows} 条`);
    }
    return result;
  }

  if (codexRunning()) {
    result.skipped = true;
    result.reason = "Codex 正在运行且系统没有 sqlite3，已延后到下次启动";
    log(`启动修复会话目录跳过：${result.reason}`);
    return result;
  }

  const SQL = await loadSqlJs();
  for (const name of stateDbNames) {
    const dbPath = path.join(CODEX_HOME, name);
    const db = new SQL.Database(fs.readFileSync(dbPath));
    try {
      const cols = sqlJsTableColumns(db, "threads");
      const clearSql = forceSubagentNoUserEventSql(cols);
      if (!clearSql) continue;
      db.run(clearSql);
      const changed = db.getRowsModified();
      if (!changed) continue;
      const backupPath = `${dbPath}.${suffix}.bak`;
      fs.copyFileSync(dbPath, backupPath);
      result.backups.push(backupPath);
      fs.writeFileSync(dbPath, Buffer.from(db.export()));
      result.stateRows += changed;
    } finally {
      db.close();
    }
  }
  if (fs.existsSync(LOCAL_CATALOG_DB)) {
    const db = new SQL.Database(fs.readFileSync(LOCAL_CATALOG_DB));
    try {
      const cols = sqlJsTableColumns(db, "local_thread_catalog");
      if (cols.has("thread_source")) {
        db.run("DELETE FROM local_thread_catalog WHERE LOWER(COALESCE(thread_source,''))='subagent'");
        const changed = db.getRowsModified();
        if (changed) {
          try { db.run("UPDATE local_thread_catalog_metadata SET catalog_revision=COALESCE(catalog_revision,0)+1 WHERE id=1"); } catch {}
          const backupPath = `${LOCAL_CATALOG_DB}.${suffix}.bak`;
          fs.copyFileSync(LOCAL_CATALOG_DB, backupPath);
          result.backups.push(backupPath);
          fs.writeFileSync(LOCAL_CATALOG_DB, Buffer.from(db.export()));
          result.catalogRows = changed;
        }
      }
    } finally {
      db.close();
    }
  }
  if (result.stateRows || result.catalogRows) {
    log(`启动修复会话目录：state ${result.stateRows} 条，侧栏移除 subagent ${result.catalogRows} 条`);
  }
  return result;
}

function sqlJsTableColumns(db, table) {
  const result = db.exec(`PRAGMA table_info("${String(table).replace(/"/g, '""')}")`);
  if (!result.length) return new Set();
  return new Set(result[0].values.map((row) => String(row[1] || "")).filter(Boolean));
}

function sqlJsCount(db, table) {
  const result = db.exec(`SELECT COUNT(*) FROM "${String(table).replace(/"/g, '""')}"`);
  return result.length && result[0].values.length ? Number(result[0].values[0][0] || 0) : 0;
}

function sqlJsLocateFile(file) {
  return path.join(
    global.__BUNDLE_ROOT__ || path.join(__dirname, "..", ".."),
    "node_modules", "sql.js", "dist", file
  );
}

async function loadSqlJs() {
  return initSqlJs({ locateFile: sqlJsLocateFile });
}

// 创建一个可被当前 Codex 直接打开的完整 state_5.sqlite，而不是只造 threads 表。
// 通过临时文件安装，避免 Windows 下进程中断时留下半个数据库。
function createStateDbSqlJs(SQL, dbPath) {
  const db = new SQL.Database();
  try {
    db.exec(STATE_DB_SCHEMA_SQL);
    for (const [version, description, checksum] of STATE_DB_MIGRATIONS) {
      db.run(
        `INSERT INTO _sqlx_migrations (version, description, success, checksum, execution_time) ` +
        `VALUES (${version}, ${sql(description)}, 1, X'${checksum}', 0)`
      );
    }
    const tmp = `${dbPath}.td-create-${process.pid}-${Date.now()}.tmp`;
    fs.mkdirSync(path.dirname(dbPath), { recursive: true });
    try {
      fs.writeFileSync(tmp, Buffer.from(db.export()));
      fs.renameSync(tmp, dbPath);
    } finally {
      try { fs.rmSync(tmp, { force: true }); } catch {}
    }
  } finally {
    db.close();
  }
}

// sql.js 只接受 null/string/number/bigint 绑定值，其余类型直接抛错。
function bindValue(v) {
  if (v === null || v === undefined) return null;
  if (typeof v === "string" || typeof v === "number") return v;
  try {
    const text = JSON.stringify(v);
    if (typeof text === "string") return text;
  } catch {}
  return String(v);
}

// Windows 通常没有 sqlite3.exe。这里必须和 sqlite3 分支一样做 UPSERT，不能只 UPDATE 已有行，
// 否则新机器上的 state_*.sqlite 没有 threads 时，rollout 虽然存在，Codex 仍然看不到会话。
// 单条记录失败只跳过该条（记日志），不能让一条异常记录回滚整批——否则 state 库一条都进不去，
// 侧栏 catalog 却重建成功，Codex 启动 full reconcile 后会话会"闪现后全部消失"。
function upsertStateDbSqlJs(db, records, target, userEventIds, cwdById, log = () => {}) {
  const tables = db.exec("SELECT name FROM sqlite_master WHERE type='table' AND name='threads'");
  if (!tables.length) return { insertedRows: 0, provRows: 0, ueRows: 0, cwdRows: 0, failedRows: 0, skipped: true };
  const cols = sqlJsTableColumns(db, "threads");
  if (!cols.has("id") || !cols.has("model_provider")) {
    return { insertedRows: 0, provRows: 0, ueRows: 0, cwdRows: 0, failedRows: 0, skipped: true };
  }
  const desired = threadColumns(cols);
  const updateSet = threadUpdateSet(desired, target, true);
  const statement = db.prepare(
    `INSERT INTO threads (${desired.join(",")}) VALUES (${desired.map(() => "?").join(",")}) ` +
    `ON CONFLICT(id) DO UPDATE SET ${updateSet.join(",")}`
  );
  const before = sqlJsCount(db, "threads");
  let provRows = 0;
  let ueRows = 0;
  let cwdRows = 0;
  let failedRows = 0;
  try {
    db.run("BEGIN");
    for (const record of records) {
      try {
        statement.run(desired.map((column) => bindValue(threadValue(record, target, column))).concat([target]));
      } catch (e) {
        failedRows++;
        if (failedRows <= 3) log(`  线程回填跳过 ${record.id || "(无id)"}：${e.message || e}`);
      }
    }
    statement.free();
    db.run(`UPDATE threads SET model_provider=? WHERE COALESCE(model_provider,'')<>?`, [target, target]);
    provRows = db.getRowsModified();
    if (cols.has("has_user_event")) {
      for (const id of userEventIds || []) {
        db.run("UPDATE threads SET has_user_event=1 WHERE id=? AND COALESCE(has_user_event,0)<>1", [id]);
        ueRows += db.getRowsModified();
      }
      const checks = [];
      if (cols.has("thread_source")) checks.push("LOWER(COALESCE(thread_source,''))='subagent'");
      if (cols.has("source")) checks.push("LOWER(COALESCE(source,'')) LIKE '%\"subagent\"%'");
      if (checks.length) db.run(`UPDATE threads SET has_user_event=0 WHERE COALESCE(has_user_event,0)<>0 AND (${checks.join(" OR ")})`);
    }
    if (cols.has("cwd")) {
      for (const [id, cwd] of Object.entries(cwdById || {})) {
        db.run("UPDATE threads SET cwd=? WHERE id=? AND COALESCE(cwd,'')<>?", [cwd, id, cwd]);
        cwdRows += db.getRowsModified();
      }
    }
    db.run("COMMIT");
  } catch (e) {
    try { statement.free(); } catch {}
    try { db.run("ROLLBACK"); } catch {}
    throw e;
  }
  return {
    insertedRows: Math.max(0, sqlJsCount(db, "threads") - before),
    provRows,
    ueRows,
    cwdRows,
    failedRows,
  };
}

async function syncProviderToCurrent(log = () => {}, onProgress = () => {}) {
  if (codexRunning()) throw new Error("检测到 Codex 正在运行，请先完全退出 Codex 再同步。");
  const target = targetProvider();
  log(`目标 provider：${target}`);
  onProgress({ phase: "备份中", done: 0, total: 0 });

  // 1) 备份
  const backup = path.join(CODEX_HOME, "backups_state", "provider-sync", ts());
  fs.mkdirSync(backup, { recursive: true });
  for (const name of ["config.toml", ".codex-global-state.json", ".codex-global-state.json.bak"]) {
    const src = path.join(CODEX_HOME, name);
    if (fs.existsSync(src)) fs.copyFileSync(src, path.join(backup, name));
  }
  for (const db of fs.readdirSync(CODEX_HOME).filter((f) => /^state_.*\.sqlite(?:-(?:wal|shm))?$/.test(f))) {
    fs.copyFileSync(path.join(CODEX_HOME, db), path.join(backup, db));
  }
  // 同时备份侧栏目录库
  if (fs.existsSync(LOCAL_CATALOG_DB)) {
    try {
      fs.copyFileSync(LOCAL_CATALOG_DB, path.join(backup, "codex-dev.db"));
    } catch {}
  }
  log(`已备份到 ${backup}`);

  // 2) 遍历 rollout：改首行 provider，收集完整线程元信息
  const userEventIds = new Set();
  const cwdById = {};
  const titles = loadIndexTitles();
  const records = [];
  let changedFiles = 0;
  const rolloutFiles = findRollouts();
  const rtotal = rolloutFiles.length;
  let ri = 0;
  for (const file of rolloutFiles) {
    ri++;
    if (ri % 5 === 0 || ri === rtotal) onProgress({ phase: "改写会话文件", done: ri, total: rtotal });
    let lines;
    try {
      lines = fs.readFileSync(file, "utf-8").split(/(?<=\n)/); // 保留换行
    } catch {
      continue;
    }
    if (!lines.length) continue;
    let tid = null;
    let rewrote = false;
    for (let i = 0; i < lines.length; i++) {
      const line = lines[i];
      if (!line.trim()) continue;
      let rec;
      try {
        rec = JSON.parse(line);
      } catch {
        continue;
      }
      if (rec.type === "session_meta" && rec.payload && typeof rec.payload === "object") {
        tid = rec.payload.id || rec.payload.session_id || null;
        const cwd = rec.payload.cwd;
        if (tid && cwd) cwdById[tid] = cwd;
        if (rec.payload.model_provider !== target) {
          rec.payload.model_provider = target;
          lines[i] = JSON.stringify(rec) + "\n";
          rewrote = true;
        }
        break;
      }
    }
    if (rewrote) {
      fs.writeFileSync(file, lines.join(""), "utf-8");
      changedFiles++;
    }
    const rec = readRolloutRecord(file, titles);
    if (rec) {
      rec.model_provider = target;
      if (rec.has_user_event) userEventIds.add(rec.id);
      records.push(rec);
    }
  }
  log(`改写 rollout 文件：${changedFiles} 个；含用户消息的会话：${userEventIds.size} 条；可回填线程：${records.length} 条`);

  // 2.5) Codex 项目分组来自全局 state，历史 cwd 需要合并到 project-order。
  const projectState = updateProjectState(records, log);

  // 3) 首次安装场景：目标目录可能连 state_*.sqlite 都没有。先创建完整的
  // state_5.sqlite，再走下面统一的 sqlite3 / sql.js UPSERT 流程。
  let SQL = null;
  let stateDbCreated = 0;
  let stateDbCreateError = "";
  let stateDbNames = fs.readdirSync(CODEX_HOME).filter((f) => /^state_.*\.sqlite$/.test(f));
  if (!stateDbNames.length) {
    const stateDbName = "state_5.sqlite";
    try {
      SQL = await loadSqlJs();
      createStateDbSqlJs(SQL, path.join(CODEX_HOME, stateDbName));
      stateDbNames = [stateDbName];
      stateDbCreated = 1;
      log(`未找到 state_*.sqlite，已创建完整状态库：${stateDbName}`);
    } catch (e) {
      stateDbCreateError = e.message || String(e);
      log(`创建 state_5.sqlite 失败：${stateDbCreateError}`);
    }
  }

  // 4) 更新所有 state_*.sqlite
  onProgress({ phase: "更新数据库", done: 0, total: 0 });
  if (sqlite3Available()) {
    let provRows = 0;
    let ueRows = 0;
    let cwdRows = 0;
    let insertedRows = 0;
    const forceUeSql = forceHasUserEventSql(userEventIds);
    for (const name of stateDbNames) {
      const dbPath = path.join(CODEX_HOME, name);
      try {
        const cols = sqliteCols(dbPath);
        if (!cols.has("model_provider") || !cols.has("has_user_event")) continue;
        const beforeProvider = Number(runSqlite3(dbPath, `SELECT COUNT(*) FROM threads WHERE COALESCE(model_provider,'')<>${sql(target)};\n`).trim() || 0);
        const beforeUserEvent = Number(runSqlite3(dbPath, "SELECT COUNT(*) FROM threads WHERE COALESCE(has_user_event,0)<>1;\n").trim() || 0);
        const beforeRows = Number(runSqlite3(dbPath, "SELECT COUNT(*) FROM threads;\n").trim() || 0);
        let batchDone = 0;
        for (const batch of chunksOf(records, STATE_DB_UPSERT_BATCH_SIZE)) {
          batchDone += batch.length;
          onProgress({ phase: `更新数据库 ${name}`, done: batchDone, total: records.length });
          runSqlite3(dbPath, buildThreadUpsertSql(batch, target, cols, { updateAllProviders: false }));
        }
        runSqlite3(dbPath, `BEGIN;\nUPDATE threads SET model_provider=${sql(target)} WHERE COALESCE(model_provider,'')<>${sql(target)};\nCOMMIT;\n`);
        // 兜底：强制把 rollout 已确认有用户消息的线程 has_user_event 拉起来
        if (forceUeSql) runSqlite3(dbPath, "BEGIN;\n" + forceUeSql + "\nCOMMIT;\n");
        const clearSubagentSql = forceSubagentNoUserEventSql(cols);
        if (clearSubagentSql) runSqlite3(dbPath, "BEGIN;\n" + clearSubagentSql + "\nCOMMIT;\n");
        runSqlite3(dbPath, "PRAGMA wal_checkpoint(TRUNCATE);\n");
        const afterUserEvent = Number(runSqlite3(dbPath, "SELECT COUNT(*) FROM threads WHERE COALESCE(has_user_event,0)<>1;\n").trim() || 0);
        const afterRows = Number(runSqlite3(dbPath, "SELECT COUNT(*) FROM threads;\n").trim() || 0);
        provRows += beforeProvider;
        ueRows += Math.max(0, beforeUserEvent - afterUserEvent);
        insertedRows += Math.max(0, afterRows - beforeRows);
        cwdRows += records.filter((r) => r.cwd).length;
      } catch (e) {
        log(`  sqlite3 数据库跳过 ${name}：${e.message || e}`);
      }
    }
    log(`数据库更新：provider ${provRows} 行、has_user_event ${ueRows} 行、新增线程 ${insertedRows} 行、项目索引 +${projectState.addedProjects}`);

    // 【增强】4) 重建 session_index.jsonl，确保所有会话都在索引中
    onProgress({ phase: "重建索引", done: 0, total: 0 });
    const indexResult = rebuildSessionIndex(records, log);

    // 【关键修复】5) 重建 Codex 桌面侧栏 local_thread_catalog
    // 新版 ChatGPT/Codex 侧栏读的是 ~/.codex/sqlite/codex-dev.db，不是 state_5.sqlite。
    onProgress({ phase: "重建侧栏目录", done: 0, total: 0 });
    const catalogResult = await rebuildLocalThreadCatalogAsync(records, target, log);

    onProgress({ phase: "完成", done: 0, total: 0 });

    return {
      target,
      totalConversations: rtotal,
      changedFiles,
      userEvents: userEventIds.size,
      provRows,
      ueRows,
      cwdRows,
      insertedRows,
      projectRows: projectState.addedProjects,
      projectlessRemoved: projectState.removedProjectless,
      indexRebuilt: indexResult.rebuilt || 0,
      catalogRebuilt: catalogResult.rebuilt || 0,
      stateDbCreated,
      stateDbCreateError,
      backup,
    };
  }

  if (!SQL) SQL = await loadSqlJs();
  let provRows = 0;
  let ueRows = 0;
  let cwdRows = 0;
  let insertedRows = 0;
  let failedRows = 0;
  for (const name of stateDbNames) {
    const dbPath = path.join(CODEX_HOME, name);
    let db;
    try {
      db = new SQL.Database(fs.readFileSync(dbPath));
      const result = upsertStateDbSqlJs(db, records, target, userEventIds, cwdById, log);
      if (result.skipped) {
        db.close();
        db = null;
        continue;
      }
      provRows += result.provRows;
      ueRows += result.ueRows;
      cwdRows += result.cwdRows;
      insertedRows += result.insertedRows;
      failedRows += result.failedRows || 0;
      fs.writeFileSync(dbPath, Buffer.from(db.export()));
      db.close();
      db = null;
      try { fs.rmSync(dbPath + "-wal", { force: true }); } catch {}
      try { fs.rmSync(dbPath + "-shm", { force: true }); } catch {}
    } catch (e) {
      if (db) try { db.close(); } catch {}
      log(`  数据库跳过 ${name}：${e.message || e}`);
    }
  }
  log(`数据库更新：provider ${provRows} 行、has_user_event ${ueRows} 行、cwd ${cwdRows} 行、新增线程 ${insertedRows} 行${failedRows ? `、失败跳过 ${failedRows} 行` : ""}`);

  // 【增强】4) 重建 session_index.jsonl，确保所有会话都在索引中
  onProgress({ phase: "重建索引", done: 0, total: 0 });
  const indexResult = rebuildSessionIndex(records, log);

  // 【关键修复】5) 重建 Codex 桌面侧栏 local_thread_catalog
  onProgress({ phase: "重建侧栏目录", done: 0, total: 0 });
  const catalogResult = await rebuildLocalThreadCatalogAsync(records, target, log);

  onProgress({ phase: "完成", done: 0, total: 0 });

  return {
    target,
    totalConversations: rtotal,
    changedFiles,
    userEvents: userEventIds.size,
    provRows,
    ueRows,
    cwdRows,
    insertedRows,
    projectRows: projectState.addedProjects,
    projectlessRemoved: projectState.removedProjectless,
    indexRebuilt: indexResult.rebuilt || 0,
    catalogRebuilt: catalogResult.rebuilt || 0,
    stateDbCreated,
    stateDbCreateError,
    backup,
  };
}

module.exports = {
  syncProviderToCurrent,
  repairLegacySessionVisibility,
  codexRunning,
  targetProvider,
  setCodexDataDir,
};