///|
pub let wal_checkpoint_every_n_writes : Int = 50

///|
pub struct SessionDb {
  db : SqliteDb
  db_path : String
  mut write_count : Int
  checkpoint_interval : Int
}

///| Process-wide RNG seeded at first use from the wall clock, so session
///| IDs differ between runs without requiring a cryptographically secure
///| source. Sufficient for local DB IDs (the DB also enforces uniqueness
///| via PRIMARY KEY + INSERT OR IGNORE).
let _rng_state : Array[@random.Rand] = []

///|
fn _rng() -> @random.Rand {
  if _rng_state.length() == 0 {
    // Spread the millisecond clock into 8 bytes of the 32-byte seed;
    // the remaining 24 stay zero. Not cryptographic, but enough to
    // decorrelate session IDs between process runs.
    let s = @env.now().reinterpret_as_int64()
    let seed = Bytes::makei(32, fn(i) {
      if i < 8 {
        let shift : Int = i * 8
        (s >> shift).to_byte()
      } else {
        b'\x00'
      }
    })
    _rng_state.push(@random.Rand::chacha8(seed~))
  }
  _rng_state[0]
}

///| UUID v4 string via moonbitlang/x/uuid. Generates 16 random bytes,
///| flips version/variant bits, and formats as 8-4-4-4-12 hex.
fn _uuid_v4() -> String {
  let r = _rng()
  let bytes = Bytes::makei(16, fn(_) { r.int(limit=256).to_byte() })
  let uuid = try {
    @uuid.from_bytes(bytes).as_version(V4)
  } catch {
    _ => abort("uuid generation failed")
  }
  uuid.to_string()
}

///| Current time in ms since epoch (cross-target via moonbitlang/core/env).
fn _now_ms() -> Int64 {
  @env.now().reinterpret_as_int64()
}

///|
fn _sess_apply_pragmas(db : SqliteDb) -> Unit {
  db_exec(db, "PRAGMA journal_mode=WAL")
  db_exec(db, "PRAGMA synchronous=NORMAL")
  db_exec(db, "PRAGMA busy_timeout=5000")
  db_exec(db, "PRAGMA foreign_keys=ON")
}

///|
fn _sess_apply_schema(db : SqliteDb, schema_sql : String) -> Unit {
  db_exec(db, schema_sql)
}

///| Load contract/sql/schema.sql. Respects MNEMO_CONTRACT_DIR for
///| out-of-repo deployments (e.g. inside .mooncakes); defaults to
///| "./contract" relative to the current working directory.
fn _sess_read_schema_sql() -> String {
  let dir = (@env.get_env_var("MNEMO_CONTRACT_DIR")).unwrap_or("./contract")
  let path = dir + "/sql/schema.sql"
  (try? @fs.read_file_to_string(path)).unwrap_or("")
}

///|
pub fn open_session_db(db_path : String) -> SessionDb {
  open_session_db_opts(db_path, checkpoint_interval=wal_checkpoint_every_n_writes)
}

///|
pub fn open_session_db_opts(
  db_path : String,
  checkpoint_interval~ : Int = wal_checkpoint_every_n_writes
) -> SessionDb {
  _stmt_buffer_init()
  let db = open_db(db_path)
  _sess_apply_pragmas(db)
  let schema = _sess_read_schema_sql()
  _sess_apply_schema(db, schema)
  _sess_apply_migrations(db)
  { db, db_path, write_count: 0, checkpoint_interval }
}

///|
/// Run SQL swallowing any error. Used for ALTER TABLE IF NOT EXISTS-style
/// migration steps where the statement may fail on an already-migrated
/// schema. mizchi/sqlite's `exec` already returns a Bool rather than
/// throwing, so we simply ignore its return value.
fn _sess_exec_ignore(db : SqliteDb, sql : String) -> Unit {
  let _ = db.inner.exec(sql)

}

///|
/// Apply auto-migrations to detect and add missing columns.
fn _sess_apply_migrations(db : SqliteDb) -> Unit {
  // Add reasoning_details and codex_reasoning_items if not present
  // (already in schema.sql for fresh DBs; this handles existing DBs)
  let cols_stmt = prepare(db, "PRAGMA table_info(messages)")
  let cols = _stmt_all(cols_stmt)
  let n = _rows_length(cols)
  let mut has_reasoning_details = false
  let mut has_codex_reasoning_items = false
  let mut i = 0
  while i < n {
    let row = _row_at(cols, i)
    let col_name = _row_get_text(row, "name")
    if col_name == "reasoning_details" {
      has_reasoning_details = true
    }
    if col_name == "codex_reasoning_items" {
      has_codex_reasoning_items = true
    }
    i = i + 1
  }
  if !has_reasoning_details {
    _sess_exec_ignore(db, "ALTER TABLE messages ADD COLUMN reasoning_details TEXT")
  }
  if !has_codex_reasoning_items {
    _sess_exec_ignore(db,
      "ALTER TABLE messages ADD COLUMN codex_reasoning_items TEXT",
    )
  }
}

///|
pub fn close_session_db(sdb : SessionDb) -> Unit {
  _sess_exec_ignore(sdb.db, "PRAGMA wal_checkpoint(PASSIVE)")
  _sess_close_db(sdb.db)
}

///|
fn _sess_close_db(db : SqliteDb) -> Unit {
  db.inner.close()
}

///|
fn _maybe_checkpoint(sdb : SessionDb) -> Unit {
  sdb.write_count = sdb.write_count + 1
  if sdb.write_count % sdb.checkpoint_interval == 0 {
    _sess_exec_ignore(sdb.db, "PRAGMA wal_checkpoint(PASSIVE)")
  }
}

// ── write helper ──

///| Wraps a callback in BEGIN IMMEDIATE / COMMIT. Returns false on any
///| failure so the caller can retry on SQLITE_BUSY. All three operations
///| delegate to mizchi/sqlite which returns Bool for exec. If BEGIN
///| IMMEDIATE returns false (e.g. a stale open transaction left over
///| from a prior failed attempt), we roll back first so the next retry
///| starts from a clean state — this mirrors what the old node:sqlite
///| try/catch path did implicitly via exec("ROLLBACK") in the catch.
fn _sess_try_write_tx(db : SqliteDb, f : () -> Unit) -> Bool {
  if !db.inner.begin_immediate() {
    let _ = db.inner.rollback()

    return false
  }
  f()
  if db.inner.commit() {
    return true
  }
  let _ = db.inner.rollback()

  false
}

///| Cooperative async write transaction with retry.
async fn _sess_with_write_tx_async(db : SqliteDb, f : () -> Unit) -> Unit {
  let max = 15
  let mut attempt = 0
  while attempt < max {
    if _sess_try_write_tx(db, f) {
      return
    }
    attempt = attempt + 1
    if attempt >= max {
      abort("session_db: locked after max retries")
    }
    let ms = 5 + _rand_int(45)
    sleep_ms(ms)
  }
}

///| Returns a random integer in [0, max). Cross-target.
fn _rand_int(max : Int) -> Int {
  _rng().int(limit=max)
}

///|
pub async fn with_write_tx(sdb : SessionDb, f : () -> Unit) -> Unit {
  _sess_with_write_tx_async(sdb.db, f)
}

// ── create_session ──

///|
pub async fn create_session(
  sdb : SessionDb,
  source : String,
  system_prompt : String,
  model~ : String? = None,
  user_id~ : String? = None,
  parent_session_id~ : String? = None
) -> String {
  let sid = _uuid_v4()
  let now = _now_ms()
  with_write_tx(sdb, fn() {
    let stmt = prepare(sdb.db,
      "INSERT OR IGNORE INTO sessions (id, source, user_id, model, system_prompt, parent_session_id, started_at) VALUES (?, ?, ?, ?, ?, ?, ?)"
    )
    _stmt_bind_text(stmt, 1, sid)
    _stmt_bind_text(stmt, 2, source)
    match user_id {
      None => _stmt_bind_null(stmt, 3)
      Some(v) => _stmt_bind_text(stmt, 3, v)
    }
    match model {
      None => _stmt_bind_null(stmt, 4)
      Some(v) => _stmt_bind_text(stmt, 4, v)
    }
    _stmt_bind_text(stmt, 5, system_prompt)
    match parent_session_id {
      None => _stmt_bind_null(stmt, 6)
      Some(v) => _stmt_bind_text(stmt, 6, v)
    }
    _stmt_bind_int64(stmt, 7, now)
    _stmt_run(stmt)
  })
  _maybe_checkpoint(sdb)
  sid
}

// ── append_message ──

///|
pub async fn append_message(
  sdb : SessionDb,
  session_id : String,
  role : String,
  timestamp : Int64,
  content~ : String? = None,
  tool_name~ : String? = None,
  token_count~ : Int? = None,
  reasoning_details~ : String? = None,
  codex_reasoning_items~ : String? = None
) -> Int {
  let mut mid = 0
  with_write_tx(sdb, fn() {
    let stmt = prepare(sdb.db,
      "INSERT INTO messages (session_id, role, content, tool_name, timestamp, token_count, reasoning_details, codex_reasoning_items) VALUES (?, ?, ?, ?, ?, ?, ?, ?)"
    )
    _stmt_bind_text(stmt, 1, session_id)
    _stmt_bind_text(stmt, 2, role)
    match content {
      None => _stmt_bind_null(stmt, 3)
      Some(v) => _stmt_bind_text(stmt, 3, v)
    }
    match tool_name {
      None => _stmt_bind_null(stmt, 4)
      Some(v) => _stmt_bind_text(stmt, 4, v)
    }
    _stmt_bind_int64(stmt, 5, timestamp)
    match token_count {
      None => _stmt_bind_null(stmt, 6)
      Some(v) => _stmt_bind_int(stmt, 6, v)
    }
    match reasoning_details {
      None => _stmt_bind_null(stmt, 7)
      Some(v) => _stmt_bind_text(stmt, 7, v)
    }
    match codex_reasoning_items {
      None => _stmt_bind_null(stmt, 8)
      Some(v) => _stmt_bind_text(stmt, 8, v)
    }
    _stmt_run(stmt)
    mid = last_insert_rowid(sdb.db)
    let upd = prepare(sdb.db,
      "UPDATE sessions SET message_count = message_count + 1 WHERE id = ?"
    )
    _stmt_bind_text(upd, 1, session_id)
    _stmt_run(upd)
  })
  _maybe_checkpoint(sdb)
  mid
}

// ── get_messages ──

///|
pub struct MsgRow {
  id : Int
  role : String
  content : String
  tool_name : String
  reasoning_details : String?
  codex_reasoning_items : String?
}

///|
pub fn get_messages(sdb : SessionDb, session_id : String) -> Array[MsgRow] {
  let stmt = prepare(sdb.db,
    "SELECT id, role, content, tool_name, reasoning_details, codex_reasoning_items FROM messages WHERE session_id = ? ORDER BY id ASC"
  )
  _stmt_bind_text(stmt, 1, session_id)
  let rows = _stmt_all(stmt)
  let n = _rows_length(rows)
  let result : Array[MsgRow] = []
  let mut i = 0
  while i < n {
    let row = _row_at(rows, i)
    let rd = if _row_is_null(row, "reasoning_details") {
      None
    } else {
      Some(_row_get_text(row, "reasoning_details"))
    }
    let cri = if _row_is_null(row, "codex_reasoning_items") {
      None
    } else {
      Some(_row_get_text(row, "codex_reasoning_items"))
    }
    result.push(
      {
        id: _row_get_int(row, "id"),
        role: _row_get_text(row, "role"),
        content: _row_get_text(row, "content"),
        tool_name: _row_get_text(row, "tool_name"),
        reasoning_details: rd,
        codex_reasoning_items: cri,
      },
    )
    i = i + 1
  }
  result
}

// ── search_messages (FTS5) ──

///|
pub struct FtsResult {
  session_id : String
  message_id : Int
  content : String
}

///|
pub fn search_messages(
  sdb : SessionDb,
  query : String,
  limit~ : Int = 50,
  role_filter~ : String? = None
) -> Array[FtsResult] {
  let sanitized = sanitize_fts5_query(query)
  if sanitized == "" {
    return []
  }
  let stmt = match role_filter {
    None =>
      prepare(sdb.db,
        "SELECT m.session_id, m.id AS message_id, m.content FROM messages_fts fts JOIN messages m ON m.id = fts.rowid WHERE messages_fts MATCH ? ORDER BY rank LIMIT ?"
      )
    Some(role) => {
      let s = prepare(sdb.db,
        "SELECT m.session_id, m.id AS message_id, m.content FROM messages_fts fts JOIN messages m ON m.id = fts.rowid WHERE messages_fts MATCH ? AND fts.role = ? ORDER BY rank LIMIT ?"
      )
      _stmt_bind_text(s, 1, sanitized)
      _stmt_bind_text(s, 2, role)
      _stmt_bind_int(s, 3, limit)
      let rows = _stmt_all(s)
      let n = _rows_length(rows)
      let result : Array[FtsResult] = []
      let mut i = 0
      while i < n {
        let row = _row_at(rows, i)
        result.push(
          {
            session_id: _row_get_text(row, "session_id"),
            message_id: _row_get_int(row, "message_id"),
            content: _row_get_text(row, "content"),
          },
        )
        i = i + 1
      }
      return result
    }
  }
  _stmt_bind_text(stmt, 1, sanitized)
  _stmt_bind_int(stmt, 2, limit)
  let rows = _stmt_all(stmt)
  let n = _rows_length(rows)
  let result : Array[FtsResult] = []
  let mut i = 0
  while i < n {
    let row = _row_at(rows, i)
    result.push(
      {
        session_id: _row_get_text(row, "session_id"),
        message_id: _row_get_int(row, "message_id"),
        content: _row_get_text(row, "content"),
      },
    )
    i = i + 1
  }
  result
}

// ── end_session ──

///|
pub async fn end_session(
  sdb : SessionDb,
  session_id : String,
  end_reason : String
) -> Unit {
  let now = _now_ms()
  with_write_tx(sdb, fn() {
    let stmt = prepare(sdb.db,
      "UPDATE sessions SET ended_at = ?, end_reason = ? WHERE id = ? AND ended_at IS NULL"
    )
    _stmt_bind_int64(stmt, 1, now)
    _stmt_bind_text(stmt, 2, end_reason)
    _stmt_bind_text(stmt, 3, session_id)
    _stmt_run(stmt)
  })
  _maybe_checkpoint(sdb)
}

// ── get_compression_tip ──

///|
pub fn get_compression_tip(sdb : SessionDb, session_id : String) -> String {
  let mut current = session_id
  let mut i = 0
  let mut found = true
  while i < 100 && found {
    let stmt = prepare(sdb.db,
      "SELECT id FROM sessions WHERE parent_session_id = ? AND started_at >= (SELECT ended_at FROM sessions WHERE id = ? AND end_reason = 'compression') ORDER BY started_at DESC LIMIT 1"
    )
    _stmt_bind_text(stmt, 1, current)
    _stmt_bind_text(stmt, 2, current)
    let rows = _stmt_all(stmt)
    if _rows_length(rows) == 0 {
      found = false
    } else {
      current = _row_get_text(_row_at(rows, 0), "id")
    }
    i = i + 1
  }
  current
}

// ── SessionRow + get_session / list_sessions ──

///|
pub struct SessionRow {
  id : String
  source : String
  user_id : String?
  model : String?
  system_prompt : String
  parent_session_id : String?
  started_at : Int64
  ended_at : Int64?
  end_reason : String?
  message_count : Int
  input_tokens : Int
  output_tokens : Int
  cache_read_tokens : Int
  cache_write_tokens : Int
  title : String?
}

///| Map a single row (from `_row_at`) to a `SessionRow`.
fn _row_to_session(row : JsRow) -> SessionRow {
  {
    id: _row_get_text(row, "id"),
    source: _row_get_text(row, "source"),
    user_id: if _row_is_null(row, "user_id") { None } else { Some(_row_get_text(row, "user_id")) },
    model: if _row_is_null(row, "model") { None } else { Some(_row_get_text(row, "model")) },
    system_prompt: _row_get_text(row, "system_prompt"),
    parent_session_id: if _row_is_null(row, "parent_session_id") {
      None
    } else {
      Some(_row_get_text(row, "parent_session_id"))
    },
    started_at: _row_get_int64(row, "started_at"),
    ended_at: if _row_is_null(row, "ended_at") { None } else { Some(_row_get_int64(row, "ended_at")) },
    end_reason: if _row_is_null(row, "end_reason") {
      None
    } else {
      Some(_row_get_text(row, "end_reason"))
    },
    message_count: _row_get_int(row, "message_count"),
    input_tokens: _row_get_int(row, "input_tokens"),
    output_tokens: _row_get_int(row, "output_tokens"),
    cache_read_tokens: _row_get_int(row, "cache_read_tokens"),
    cache_write_tokens: _row_get_int(row, "cache_write_tokens"),
    title: if _row_is_null(row, "title") { None } else { Some(_row_get_text(row, "title")) },
  }
}

///| Get a single session by ID. Returns None if not found.
pub fn get_session(sdb : SessionDb, session_id : String) -> SessionRow? {
  let stmt = prepare(sdb.db, "SELECT * FROM sessions WHERE id = ?")
  _stmt_bind_text(stmt, 1, session_id)
  let rows = _stmt_all(stmt)
  if _rows_length(rows) == 0 {
    None
  } else {
    Some(_row_to_session(_row_at(rows, 0)))
  }
}

///| List sessions with optional source filter, limit, and offset. Returns (rows, total).
pub fn list_sessions(
  sdb : SessionDb,
  limit~ : Int = 20,
  offset~ : Int = 0,
  source~ : String = ""
) -> (Array[SessionRow], Int) {
  let rows_raw = if source.length() > 0 {
    let stmt = prepare(sdb.db,
      "SELECT * FROM sessions WHERE source = ? ORDER BY started_at DESC, rowid DESC LIMIT ? OFFSET ?"
    )
    _stmt_bind_text(stmt, 1, source)
    _stmt_bind_int(stmt, 2, limit)
    _stmt_bind_int(stmt, 3, offset)
    _stmt_all(stmt)
  } else {
    let stmt = prepare(sdb.db,
      "SELECT * FROM sessions ORDER BY started_at DESC, rowid DESC LIMIT ? OFFSET ?"
    )
    _stmt_bind_int(stmt, 1, limit)
    _stmt_bind_int(stmt, 2, offset)
    _stmt_all(stmt)
  }
  let n = _rows_length(rows_raw)
  let result : Array[SessionRow] = []
  let mut i = 0
  while i < n {
    result.push(_row_to_session(_row_at(rows_raw, i)))
    i = i + 1
  }
  // Get total count
  let total = if source.length() > 0 {
    let cnt_stmt = prepare(sdb.db, "SELECT COUNT(*) AS n FROM sessions WHERE source = ?")
    _stmt_bind_text(cnt_stmt, 1, source)
    let cnt_rows = _stmt_all(cnt_stmt)
    if _rows_length(cnt_rows) > 0 { _row_get_int(_row_at(cnt_rows, 0), "n") } else { 0 }
  } else {
    let cnt_stmt = prepare(sdb.db, "SELECT COUNT(*) AS n FROM sessions")
    let cnt_rows = _stmt_all(cnt_stmt)
    if _rows_length(cnt_rows) > 0 { _row_get_int(_row_at(cnt_rows, 0), "n") } else { 0 }
  }
  (result, total)
}

// ── resolve_session_id ──

///| Resolve a session ID prefix to a full session ID.
///| Returns Some(id) if exactly one session matches, None if none.
///| Aborts (via error string) if multiple sessions match — caller should
///| surface the ambiguous message.
pub fn resolve_session_id(sdb : SessionDb, prefix : String) -> Result[String, String] {
  if prefix == "" {
    return Err("prefix must be non-empty")
  }
  // Escape LIKE metacharacters: \ % _
  let escaped = _sess_escape_like(prefix)
  let stmt = prepare(sdb.db,
    "SELECT id FROM sessions WHERE id LIKE ? ESCAPE '\\' ORDER BY started_at DESC LIMIT 3"
  )
  _stmt_bind_text(stmt, 1, escaped + "%")
  let rows = _stmt_all(stmt)
  let n = _rows_length(rows)
  if n == 0 {
    Err("session_not_found: no session with id prefix '" + prefix + "'")
  } else if n > 1 {
    Err("session_ambiguous: prefix '" + prefix + "' matches " + n.to_string() + " sessions")
  } else {
    Ok(_row_get_text(_row_at(rows, 0), "id"))
  }
}

///| Escape `\`, `%`, `_` for use inside a SQL LIKE pattern. Pure MoonBit
///| (cross-target) — replaces the earlier JS regex version.
fn _sess_escape_like(s : String) -> String {
  let buf = StringBuilder::new()
  let view = s.view()
  let mut i = 0
  while i < view.length() {
    match view.get_char(i) {
      Some('\\') => buf.write_string("\\\\")
      Some('%') => buf.write_string("\\%")
      Some('_') => buf.write_string("\\_")
      Some(ch) => buf.write_char(ch)
      None => ()
    }
    i = i + 1
  }
  buf.to_string()
}

// ── inline tests ──

///| Monotonic counter bumped per scratch-dir call so test fixtures
///| never collide when many tests run within the same millisecond.
let _mkdtemp_counter : Array[Int] = [0]

///| Cross-target scratch dir for tests. Uses $TMPDIR (or /tmp fallback)
///| + a monotonic counter + the ms clock so repeated fast calls
///| always produce a unique path.
fn _mkdtemp_sess() -> String {
  let tmp = (@env.get_env_var("TMPDIR")).unwrap_or("/tmp")
  _mkdtemp_counter[0] = _mkdtemp_counter[0] + 1
  let suffix = _now_ms().to_string() + "-" + _mkdtemp_counter[0].to_string()
  let dir = tmp + "/mnemo-mbt-sess-" + suffix
  let _ = try? @fs.create_dir(dir)

  dir
}

///| Best-effort recursive rmdir. `@fs.remove_dir` takes a `recursive`
///| flag; we pass `true` so the call cleans up even if subdirs exist.
fn _rmrf_sess(dir : String) -> Unit {
  let _ = try? @fs.remove_dir(dir)

}

///| Concatenate two path segments with a single `/`. Pure MoonBit —
///| not full POSIX normalization, but sufficient for the in-repo test
///| paths this helper handles.
fn _path_join_2(a : String, b : String) -> String {
  if a.is_empty() {
    b
  } else if a.has_suffix("/") {
    a + b
  } else {
    a + "/" + b
  }
}

///|
async test "session_db: WAL checkpoint fires at interval" {
  let dir = _mkdtemp_sess()
  let sdb = open_session_db_opts(_path_join_2(dir, "s.db"), checkpoint_interval=3)
  let sid = create_session(sdb, "cli", "you are helpful")
  // write_count is now 1
  let _ = append_message(sdb, sid, "user", 1000L, content=Some("a"))
  // write_count is now 2
  let _ = append_message(sdb, sid, "assistant", 1001L, content=Some("b"))
  // write_count is now 3 — checkpoint should fire without error
  let msgs = get_messages(sdb, sid)
  assert_eq(msgs.length(), 2)
  _rmrf_sess(dir)
}

///|
async test "session_db: close_session_db checkpoints on close" {
  let dir = _mkdtemp_sess()
  let sdb = open_session_db(_path_join_2(dir, "s.db"))
  let sid = create_session(sdb, "cli", "s")
  let _ = append_message(sdb, sid, "user", 1000L, content=Some("close-test"))
  // close_session_db runs PRAGMA wal_checkpoint(PASSIVE) without throwing
  close_session_db(sdb)
  _rmrf_sess(dir)
}

///|
async test "session_db: reasoning_details and codex_reasoning_items round-trip" {
  let dir = _mkdtemp_sess()
  let sdb = open_session_db(_path_join_2(dir, "s.db"))
  let sid = create_session(sdb, "cli", "s")
  let rd = "{\"thinking\":\"step by step...\"}"
  let cri = "[{\"type\":\"thinking\",\"thinking\":\"reasoning\"}]"
  let mid = append_message(sdb, sid, "assistant", 1000L,
    content=Some("result"),
    reasoning_details=Some(rd),
    codex_reasoning_items=Some(cri))
  assert_eq(mid > 0, true)
  let msgs = get_messages(sdb, sid)
  assert_eq(msgs.length(), 1)
  assert_eq(msgs[0].reasoning_details, Some(rd))
  assert_eq(msgs[0].codex_reasoning_items, Some(cri))
  _rmrf_sess(dir)
}

///|
async test "session_db: reasoning_details defaults to None" {
  let dir = _mkdtemp_sess()
  let sdb = open_session_db(_path_join_2(dir, "s.db"))
  let sid = create_session(sdb, "cli", "s")
  let _ = append_message(sdb, sid, "user", 1000L, content=Some("hello"))
  let msgs = get_messages(sdb, sid)
  assert_eq(msgs[0].reasoning_details, None)
  assert_eq(msgs[0].codex_reasoning_items, None)
  _rmrf_sess(dir)
}

///|
test "session_db: open_session_db creates schema" {
  let dir = _mkdtemp_sess()
  let sdb = open_session_db(_path_join_2(dir, "s.db"))
  // If schema creation succeeded, this won't panic.
  let _ = search_messages(sdb, "test")
  _rmrf_sess(dir)
}

///|
async test "session_db: create_session returns id" {
  let dir = _mkdtemp_sess()
  let sdb = open_session_db(_path_join_2(dir, "s.db"))
  let sid = create_session(sdb, "cli", "you are helpful")
  assert_eq(sid.length() > 0, true)
  _rmrf_sess(dir)
}

///|
async test "session_db: append_message via FTS search" {
  let dir = _mkdtemp_sess()
  let sdb = open_session_db(_path_join_2(dir, "s.db"))
  let sid = create_session(sdb, "cli", "s")
  let mid = append_message(sdb, sid, "user", 1700000000L,
    content=Some("mnemo_fts_unique_abc"))
  assert_eq(mid > 0, true)
  let results = search_messages(sdb, "mnemo_fts_unique_abc")
  assert_eq(results.length(), 1)
  _rmrf_sess(dir)
}

///|
async test "session_db: get_messages returns ordered messages" {
  let dir = _mkdtemp_sess()
  let sdb = open_session_db(_path_join_2(dir, "s.db"))
  let sid = create_session(sdb, "cli", "s")
  let _ = append_message(sdb, sid, "user", 1000L, content=Some("first"))
  let _ = append_message(sdb, sid, "assistant", 1001L, content=Some("second"))
  let msgs = get_messages(sdb, sid)
  assert_eq(msgs.length(), 2)
  assert_eq(msgs[0].role, "user")
  assert_eq(msgs[1].role, "assistant")
  _rmrf_sess(dir)
}

///|
async test "session_db: get_compression_tip returns self when no chain" {
  let dir = _mkdtemp_sess()
  let sdb = open_session_db(_path_join_2(dir, "s.db"))
  let parent = create_session(sdb, "cli", "p")
  let tip = get_compression_tip(sdb, parent)
  assert_eq(tip, parent)
  _rmrf_sess(dir)
}

///|
async test "session_db: end_session sets ended_at" {
  let dir = _mkdtemp_sess()
  let sdb = open_session_db(_path_join_2(dir, "s.db"))
  let sid = create_session(sdb, "cli", "p")
  end_session(sdb, sid, "normal")
  // Verify ended_at is set by querying directly
  let stmt = prepare(sdb.db,
    "SELECT ended_at, end_reason FROM sessions WHERE id = ?"
  )
  _stmt_bind_text(stmt, 1, sid)
  let rows = _stmt_all(stmt)
  assert_eq(_rows_length(rows), 1)
  let row = _row_at(rows, 0)
  assert_eq(_row_get_text(row, "end_reason"), "normal")
  // ended_at is stored as Int64 (BigInt in SQLite); verify it's non-zero (was set)
  let ended = _row_get_int64(row, "ended_at")
  assert_eq(ended != 0L, true)
  _rmrf_sess(dir)
}

///|
// Year 2033 timestamp (2033-05-18 ~03:33:20 UTC) = 2_000_000_000_000 ms
// Well past Int32 max (2_147_483_647) — verifies no 32-bit truncation.
async test "session_db: Int64 timestamp roundtrip at year 2033" {
  let ts2033 : Int64 = 2_000_000_000_000L
  let dir = _mkdtemp_sess()
  let sdb = open_session_db(_path_join_2(dir, "s.db"))
  let sid = create_session(sdb, "cli", "system")
  let mid = append_message(sdb, sid, "user", ts2033, content=Some("year2033"))
  assert_eq(mid > 0, true)
  // Read back the timestamp directly from the DB to verify no truncation
  let stmt = prepare(sdb.db,
    "SELECT timestamp FROM messages WHERE id = ?"
  )
  _stmt_bind_int(stmt, 1, mid)
  let rows = _stmt_all(stmt)
  assert_eq(_rows_length(rows), 1)
  let ts_back = _row_get_int64(_row_at(rows, 0), "timestamp")
  assert_eq(ts_back, ts2033)
  _rmrf_sess(dir)
}

///|
async test "session_db: resolve_session_id returns full id for unique prefix" {
  let dir = _mkdtemp_sess()
  let sdb = open_session_db(_path_join_2(dir, "s.db"))
  let sid = create_session(sdb, "cli", "system")
  let prefix = sid[0:8].to_string()
  match resolve_session_id(sdb, prefix) {
    Ok(full_id) => assert_eq(full_id, sid)
    Err(e) => assert_eq("expected Ok but got Err: " + e, "Ok")
  }
  _rmrf_sess(dir)
}

///|
async test "session_db: resolve_session_id returns not_found for unknown prefix" {
  let dir = _mkdtemp_sess()
  let sdb = open_session_db(_path_join_2(dir, "s.db"))
  match resolve_session_id(sdb, "00000000") {
    Ok(_) => assert_eq("expected Err but got Ok", "Err")
    Err(e) => assert_eq(e.contains("session_not_found"), true)
  }
  _rmrf_sess(dir)
}

///|
async test "session_db: resolve_session_id returns ambiguous for colliding prefix" {
  let dir = _mkdtemp_sess()
  let sdb = open_session_db(_path_join_2(dir, "s.db"))
  // Insert two sessions with known overlapping IDs directly
  let base = "bbbb0000-0000-4000-8000-"
  let sid1 = base + "000000000001"
  let sid2 = base + "000000000002"
  _sess_insert_raw(sdb.db, sid1)
  _sess_insert_raw(sdb.db, sid2)
  match resolve_session_id(sdb, "bbbb0000") {
    Ok(_) => assert_eq("expected Err but got Ok", "Err")
    Err(e) => assert_eq(e.contains("session_ambiguous"), true)
  }
  _rmrf_sess(dir)
}

///|
fn _sess_insert_raw(db : SqliteDb, id : String) -> Unit {
  let stmt = prepare(
    db,
    "INSERT INTO sessions (id, source, system_prompt, started_at) VALUES (?, 'cli', 's', ?)",
  )
  _stmt_bind_text(stmt, 1, id)
  _stmt_bind_int64(stmt, 2, _now_ms())
  _stmt_run(stmt)
}