sessions.js

// SQLite 会话持久化（bun:sqlite，零外部依赖）
// 支持多后端：backend 字段区分 claude / codex
import { Database } from "bun:sqlite";
import { join, isAbsolute } from "path";

const DB_PATH = process.env.SESSIONS_DB
  ? (isAbsolute(process.env.SESSIONS_DB) ? process.env.SESSIONS_DB : join(import.meta.dir, process.env.SESSIONS_DB))
  : join(import.meta.dir, "sessions.db");

export const SESSION_TYPES = ["normal", "discuss"];

function normalizeSessionType(value) {
  const normalized = String(value || "normal").trim().toLowerCase();
  return SESSION_TYPES.includes(normalized) ? normalized : "normal";
}

const db = new Database(DB_PATH);
db.exec("PRAGMA journal_mode = WAL");
db.exec("PRAGMA busy_timeout = 5000"); // 让 SQLite 自己等锁释放，缓解 launchd 快速重启时的 WAL 锁竞争崩溃（对齐 shared-context/sqlite.js）
db.exec(`
  CREATE TABLE IF NOT EXISTS sessions (
    chat_id INTEGER PRIMARY KEY,
    session_id TEXT NOT NULL,
    created_at INTEGER NOT NULL,
    last_active INTEGER NOT NULL,
    display_name TEXT DEFAULT '',
    backend TEXT DEFAULT 'claude',
    ownership TEXT DEFAULT 'owned',
    session_type TEXT DEFAULT 'normal'
  )
`);

// 迁移：旧表没有 backend 列时自动加（只忽略"列已存在"，其他错误抛出）
try {
  db.exec("ALTER TABLE sessions ADD COLUMN backend TEXT DEFAULT 'claude'");
} catch (e) {
  if (!/duplicate column|already exists/i.test(e.message)) {
    console.error("[DB] Migration error (backend column):", e.message);
    throw e;
  }
}
try {
  db.exec("ALTER TABLE sessions ADD COLUMN ownership TEXT DEFAULT 'owned'");
} catch (e) {
  if (!/duplicate column|already exists/i.test(e.message)) {
    console.error("[DB] Migration error (ownership column):", e.message);
    throw e;
  }
}
try {
  db.exec("ALTER TABLE sessions ADD COLUMN session_type TEXT DEFAULT 'normal'");
} catch (e) {
  if (!/duplicate column|already exists/i.test(e.message)) {
    console.error("[DB] Migration error (session_type column):", e.message);
    throw e;
  }
}

// 会话历史表（保留所有历史会话，不被 /new 或 upsert 覆盖）
db.exec(`
  CREATE TABLE IF NOT EXISTS session_history (
    session_id TEXT PRIMARY KEY,
    chat_id INTEGER NOT NULL,
    created_at INTEGER NOT NULL,
    last_active INTEGER NOT NULL,
    display_name TEXT DEFAULT '',
    backend TEXT DEFAULT 'claude',
    ownership TEXT DEFAULT 'owned',
    session_type TEXT DEFAULT 'normal'
  )
`);
try {
  db.exec("ALTER TABLE session_history ADD COLUMN ownership TEXT DEFAULT 'owned'");
} catch (e) {
  if (!/duplicate column|already exists/i.test(e.message)) {
    console.error("[DB] Migration error (session_history.ownership):", e.message);
    throw e;
  }
}
try {
  db.exec("ALTER TABLE session_history ADD COLUMN session_type TEXT DEFAULT 'normal'");
} catch (e) {
  if (!/duplicate column|already exists/i.test(e.message)) {
    console.error("[DB] Migration error (session_history.session_type):", e.message);
    throw e;
  }
}

// 后端偏好表（每个 chat 独立选后端）
db.exec(`
  CREATE TABLE IF NOT EXISTS chat_backend (
    chat_id INTEGER PRIMARY KEY,
    backend TEXT NOT NULL DEFAULT 'claude'
  )
`);

db.exec(`
  CREATE TABLE IF NOT EXISTS chat_session_type (
    chat_id INTEGER PRIMARY KEY,
    session_type TEXT NOT NULL DEFAULT 'normal',
    explicit INTEGER NOT NULL DEFAULT 0,
    updated_at INTEGER NOT NULL
  )
`);
try {
  db.exec("ALTER TABLE chat_session_type ADD COLUMN explicit INTEGER NOT NULL DEFAULT 0");
} catch (e) {
  if (!/duplicate column|already exists/i.test(e.message)) {
    console.error("[DB] Migration error (chat_session_type.explicit):", e.message);
    throw e;
  }
}

// Prepared statements — sessions
const stmtGet = db.prepare("SELECT session_id, last_active, backend, ownership, session_type FROM sessions WHERE chat_id = ?");
const stmtUpsert = db.prepare(`
  INSERT INTO sessions (chat_id, session_id, created_at, last_active, display_name, backend, ownership, session_type)
  VALUES (?, ?, ?, ?, ?, ?, ?, ?)
  ON CONFLICT(chat_id) DO UPDATE SET
    session_id = excluded.session_id,
    last_active = excluded.last_active,
    display_name = excluded.display_name,
    backend = excluded.backend,
    ownership = excluded.ownership,
    session_type = excluded.session_type
`);
const stmtDelete = db.prepare("DELETE FROM sessions WHERE chat_id = ?");
const stmtRecentAll = db.prepare(`
  SELECT chat_id, session_id, created_at, last_active, display_name, backend, ownership, session_type FROM (
    SELECT chat_id, session_id, created_at, last_active, display_name, backend, ownership, session_type FROM sessions
    UNION ALL
    SELECT chat_id, session_id, created_at, last_active, display_name, backend, ownership, session_type FROM session_history
  ) ORDER BY last_active DESC LIMIT ?
`);
const stmtRecentByChat = db.prepare(`
  SELECT chat_id, session_id, created_at, last_active, display_name, backend, ownership, session_type FROM (
    SELECT chat_id, session_id, created_at, last_active, display_name, backend, ownership, session_type FROM sessions
    UNION ALL
    SELECT chat_id, session_id, created_at, last_active, display_name, backend, ownership, session_type FROM session_history
  ) WHERE chat_id = ?
  ORDER BY last_active DESC LIMIT ?
`);
const stmtRecentByChatAndBackend = db.prepare(`
  SELECT chat_id, session_id, created_at, last_active, display_name, backend, ownership, session_type FROM (
    SELECT chat_id, session_id, created_at, last_active, display_name, backend, ownership, session_type FROM sessions
    UNION ALL
    SELECT chat_id, session_id, created_at, last_active, display_name, backend, ownership, session_type FROM session_history
  ) WHERE chat_id = ? AND backend = ?
  ORDER BY last_active DESC LIMIT ?
`);
const stmtRecentByChatBackendAndOwnership = db.prepare(`
  SELECT chat_id, session_id, created_at, last_active, display_name, backend, ownership, session_type FROM (
    SELECT chat_id, session_id, created_at, last_active, display_name, backend, ownership, session_type FROM sessions
    UNION ALL
    SELECT chat_id, session_id, created_at, last_active, display_name, backend, ownership, session_type FROM session_history
  ) WHERE chat_id = ? AND backend = ? AND ownership = ?
  ORDER BY last_active DESC LIMIT ?
`);
const stmtCleanup = db.prepare("DELETE FROM sessions WHERE last_active < ?");
const stmtCleanupHistory = db.prepare("DELETE FROM session_history WHERE last_active < ?");
const stmtTouch = db.prepare("UPDATE sessions SET last_active = ? WHERE chat_id = ?");
const stmtGetSessionType = db.prepare("SELECT session_type FROM sessions WHERE chat_id = ?");
const stmtSetSessionType = db.prepare("UPDATE sessions SET session_type = ?, last_active = ? WHERE chat_id = ?");
const stmtGetChatSessionType = db.prepare("SELECT session_type, explicit FROM chat_session_type WHERE chat_id = ?");
const stmtSetChatSessionType = db.prepare(`
  INSERT INTO chat_session_type (chat_id, session_type, explicit, updated_at)
  VALUES (?, ?, 1, ?)
  ON CONFLICT(chat_id) DO UPDATE SET
    session_type = excluded.session_type,
    explicit = excluded.explicit,
    updated_at = excluded.updated_at
`);

// History statements
const stmtArchive = db.prepare(`
  INSERT OR REPLACE INTO session_history (session_id, chat_id, created_at, last_active, display_name, backend, ownership, session_type)
  SELECT session_id, chat_id, created_at, last_active, display_name, backend, ownership, session_type FROM sessions WHERE chat_id = ?
`);
const stmtGetHistory = db.prepare(
  "SELECT session_id, chat_id, created_at, last_active, display_name, backend, ownership, session_type FROM session_history WHERE session_id = ?"
);
const stmtDeleteFromHistory = db.prepare("DELETE FROM session_history WHERE session_id = ?");
const stmtHasSessionForChat = db.prepare(`
  SELECT 1 AS ok FROM (
    SELECT chat_id, session_id, backend, ownership FROM sessions
    UNION ALL
    SELECT chat_id, session_id, backend, ownership FROM session_history
  )
  WHERE chat_id = ?
    AND session_id = ?
    AND (? IS NULL OR backend = ?)
    AND (? IS NULL OR ownership = ?)
  LIMIT 1
`);

// Prepared statements — chat_backend
const stmtGetBackendPref = db.prepare("SELECT backend FROM chat_backend WHERE chat_id = ?");
const stmtSetBackendPref = db.prepare(`
  INSERT INTO chat_backend (chat_id, backend) VALUES (?, ?)
  ON CONFLICT(chat_id) DO UPDATE SET backend = excluded.backend
`);

// 模型偏好表（每个 chat 独立选模型，跨重启持久化）
db.exec(`
  CREATE TABLE IF NOT EXISTS chat_model (
    chat_id INTEGER PRIMARY KEY,
    model TEXT NOT NULL
  )
`);
const stmtGetModelPref = db.prepare("SELECT model FROM chat_model WHERE chat_id = ?");
const stmtSetModelPref = db.prepare(`
  INSERT INTO chat_model (chat_id, model) VALUES (?, ?)
  ON CONFLICT(chat_id) DO UPDATE SET model = excluded.model
`);
const stmtDeleteModelPref = db.prepare("DELETE FROM chat_model WHERE chat_id = ?");

// Effort 偏好表（每个 chat 独立选 effort，跨重启持久化）
db.exec(`
  CREATE TABLE IF NOT EXISTS chat_effort (
    chat_id INTEGER PRIMARY KEY,
    effort TEXT NOT NULL
  )
`);
const stmtGetEffortPref = db.prepare("SELECT effort FROM chat_effort WHERE chat_id = ?");
const stmtSetEffortPref = db.prepare(`
  INSERT INTO chat_effort (chat_id, effort) VALUES (?, ?)
  ON CONFLICT(chat_id) DO UPDATE SET effort = excluded.effort
`);
const stmtDeleteEffortPref = db.prepare("DELETE FROM chat_effort WHERE chat_id = ?");

export function getSession(chatId) {
  const row = stmtGet.get(chatId);
  if (!row) return null;
  // Touch last_active
  stmtTouch.run(Date.now(), chatId);
  return {
    session_id: row.session_id,
    backend: row.backend || "claude",
    ownership: row.ownership || "owned",
    session_type: normalizeSessionType(row.session_type),
  };
}

// 无 touch 副作用的只读版（getSession 会刷新 last_active；turn 收尾的写回防护不该改活跃时间）
export function peekSession(chatId) {
  const row = stmtGet.get(chatId);
  if (!row) return null;
  return {
    session_id: row.session_id,
    backend: row.backend || "claude",
    ownership: row.ownership || "owned",
    session_type: normalizeSessionType(row.session_type),
  };
}

// chatId → 最近一次 deleteSession 的时间（内存即可：bridge 重启时在途 turn 的收尾回调一并消失）
const sessionResetAt = new Map();

export function getSessionResetAt(chatId) {
  return sessionResetAt.get(chatId) || 0;
}

export function setSession(
  chatId,
  sessionId,
  displayName = "",
  backend = "claude",
  ownership = "owned",
  sessionType = "normal",
) {
  // 归档旧会话（如果有）
  stmtArchive.run(chatId);
  // 从历史中移除（避免恢复后重复出现）
  stmtDeleteFromHistory.run(sessionId);
  const now = Date.now();
  const normalizedSessionType = normalizeSessionType(sessionType);
  stmtUpsert.run(chatId, sessionId, now, now, displayName, backend, ownership, normalizedSessionType);
}

export function deleteSession(chatId, source = "unknown") {
  // 归档到历史再删除
  const existing = stmtGet.get(chatId);
  console.log(`[Session Debug] deleteSession(${chatId}) source=${source} existing:`, existing ? `${existing.session_id.slice(0, 8)}...` : "none", new Error().stack?.split("\n").slice(1, 8).join(" ← "));
  stmtArchive.run(chatId);
  stmtDelete.run(chatId);
  sessionResetAt.set(chatId, Date.now());
}

export function getHistorySession(sessionId) {
  return stmtGetHistory.get(sessionId) || null;
}

export function getSessionType(chatId) {
  return getSessionTypeState(chatId).sessionType;
}

export function getSessionTypeState(chatId) {
  const chatRow = stmtGetChatSessionType.get(chatId);
  if (chatRow && Number(chatRow.explicit) === 1) {
    return {
      sessionType: normalizeSessionType(chatRow.session_type),
      explicit: true,
    };
  }

  const row = stmtGetSessionType.get(chatId);
  if (row?.session_type) {
    return {
      sessionType: normalizeSessionType(row.session_type),
      explicit: false,
    };
  }

  if (chatRow) {
    return {
      sessionType: normalizeSessionType(chatRow.session_type),
      explicit: false,
    };
  }

  return {
    sessionType: "normal",
    explicit: false,
  };
}

export function setSessionType(chatId, sessionType) {
  const normalizedSessionType = normalizeSessionType(sessionType);
  const now = Date.now();
  stmtSetChatSessionType.run(chatId, normalizedSessionType, now);
  const sessionChanges = stmtSetSessionType.run(normalizedSessionType, now, chatId).changes;
  return Math.max(1, sessionChanges);
}

export function recentSessions(limit = 8, options = {}) {
  const { chatId = null, backend = null, ownership = null } = options;
  if (chatId != null && backend && ownership) {
    return stmtRecentByChatBackendAndOwnership.all(chatId, backend, ownership, limit);
  }
  if (chatId != null && backend) {
    return stmtRecentByChatAndBackend.all(chatId, backend, limit);
  }
  if (chatId != null) {
    return stmtRecentByChat.all(chatId, limit);
  }
  return stmtRecentAll.all(limit);
}

export function cleanupExpired() {
  // 当前会话默认长期保留，直到用户显式 /new 或 /resume 切换。
  // 历史表仍然定期清理，避免只读预览列表无限增长。
  const historyCutoff = Date.now() - 7 * 24 * 60 * 60 * 1000;
  const result = stmtCleanupHistory.run(historyCutoff);
  // WAL 只在 checkpoint 或干净关闭时回收；长跑进程被 launchd 硬杀从不触发后者，
  // 实测主库 28K 时 -wal 可涨到 2.7M，借周期清理顺带截断（对齐 tasks.js 范式）
  db.exec("PRAGMA wal_checkpoint(TRUNCATE)");
  return result.changes;
}

export function closeSessionsDb() {
  try {
    db.exec("PRAGMA wal_checkpoint(TRUNCATE)");
    db.close();
  } catch {}
}

export function sessionBelongsToChat(chatId, sessionId, backend = null, ownership = null) {
  return Boolean(
    stmtHasSessionForChat.get(
      chatId,
      sessionId,
      backend,
      backend,
      ownership,
      ownership,
    ),
  );
}

export function getChatBackend(chatId) {
  const row = stmtGetBackendPref.get(chatId);
  return row?.backend || null;
}

export function setChatBackend(chatId, backend) {
  stmtSetBackendPref.run(chatId, backend);
}

export function getChatModel(chatId) {
  const row = stmtGetModelPref.get(chatId);
  return row?.model || null;
}

export function setChatModel(chatId, model) {
  stmtSetModelPref.run(chatId, model);
}

export function deleteChatModel(chatId) {
  stmtDeleteModelPref.run(chatId);
}

export function getChatEffort(chatId) {
  const row = stmtGetEffortPref.get(chatId);
  return row?.effort || null;
}

export function setChatEffort(chatId, effort) {
  stmtSetEffortPref.run(chatId, effort);
}

export function deleteChatEffort(chatId) {
  stmtDeleteEffortPref.run(chatId);
}

// 每 30 分钟自动清理过期会话
setInterval(cleanupExpired, 30 * 60 * 1000);