// Thin wrapper over `mizchi/sqlite` that preserves the column-name-based
// row API the rest of mnemo (session_db.mbt etc.) was built against.
//
// Design: `mizchi/sqlite` is backend-agnostic (node:sqlite on JS,
// libsqlite3 on native). Its Statement API takes column *indices* for
// reads; mnemo's older FFI returned rows as dicts keyed by column name.
// To keep session_db.mbt unchanged we:
//   - pool pending bind values in an Array, flushed on run/all via bind_all;
//   - on stmt_all, pre-compute a name→index map via Statement::column_name
//     (added in mizchi/sqlite 0.3.0), materialize every row into an
//     Array[SqlValue], and expose stmt_get_* as map lookups.
//
// The wrapper is cross-target: no `extern "js"` here, so this file
// compiles on both --target js and --target native.

///|
pub struct SqliteDb {
  inner : @sqlite.Database
}

///|
pub fn open_db(path : String) -> SqliteDb {
  match @sqlite.Database::open(path) {
    Some(d) => { inner: d }
    None => abort("failed to open sqlite database: " + path)
  }
}

///|
pub fn db_exec(db : SqliteDb, sql : String) -> Unit {
  let _ = db.inner.exec(sql)

}

// --- Prepared statements ---

///|
pub struct SqliteStmt {
  inner : @sqlite.Statement
  mut pending : Array[@sqlite.SqlValue]
}

///|
pub fn prepare(db : SqliteDb, sql : String) -> SqliteStmt {
  match db.inner.prepare(sql) {
    Some(s) => { inner: s, pending: [] }
    None => abort("failed to prepare statement: " + sql)
  }
}

///|
/// No-op kept for source compatibility with the previous FFI. The old JS
/// implementation stashed pending bindings in `globalThis.__mnemo_stmt_buf`
/// and needed this one-time init; the new wrapper carries pending values
/// inside the `SqliteStmt` value itself, so initialization is implicit.
pub fn _stmt_buffer_init() -> Unit {
  ()
}

// --- Bind helpers ---
//
// Bindings are buffered until stmt_run / stmt_all. We pad with Null for
// any skipped positional slots so out-of-order binding (e.g. bind 1, 3)
// produces a well-formed array for bind_all.

///|
fn ensure_bind_slot(stmt : SqliteStmt, i : Int) -> Unit {
  while stmt.pending.length() < i {
    stmt.pending.push(Null)
  }
}

///|
pub fn stmt_bind_text(stmt : SqliteStmt, i : Int, val : String) -> Unit {
  ensure_bind_slot(stmt, i)
  stmt.pending[i - 1] = Text(@utf8.encode(val))
}

///|
pub fn stmt_bind_int(stmt : SqliteStmt, i : Int, val : Int) -> Unit {
  ensure_bind_slot(stmt, i)
  stmt.pending[i - 1] = Int(val)
}

///|
pub fn stmt_bind_int64(stmt : SqliteStmt, i : Int, val : Int64) -> Unit {
  ensure_bind_slot(stmt, i)
  stmt.pending[i - 1] = Int64(val)
}

///|
pub fn stmt_bind_null(stmt : SqliteStmt, i : Int) -> Unit {
  ensure_bind_slot(stmt, i)
  stmt.pending[i - 1] = Null
}

// --- Aliases kept for source-compat with the previous FFI ---
// session_db.mbt and friends historically called the underscore-prefixed
// extern helpers directly; expose thin forwarders so the callers don't
// need to change.

///|
pub fn _stmt_bind_text(stmt : SqliteStmt, i : Int, val : String) -> Unit {
  stmt_bind_text(stmt, i, val)
}

///|
pub fn _stmt_bind_int(stmt : SqliteStmt, i : Int, val : Int) -> Unit {
  stmt_bind_int(stmt, i, val)
}

///|
pub fn _stmt_bind_int64(stmt : SqliteStmt, i : Int, val : Int64) -> Unit {
  stmt_bind_int64(stmt, i, val)
}

///|
pub fn _stmt_bind_null(stmt : SqliteStmt, i : Int) -> Unit {
  stmt_bind_null(stmt, i)
}

///|
pub fn _stmt_run(stmt : SqliteStmt) -> Unit {
  stmt_run(stmt)
}

///|
pub fn _stmt_all(stmt : SqliteStmt) -> JsRows {
  stmt_all(stmt)
}

///|
pub fn _rows_length(rows : JsRows) -> Int {
  rows_length(rows)
}

// Single-row view. The old FFI exposed `_row_at(rows, i)` that returned
// an opaque "row" and separate `_row_get_*(row, col)` getters. Keep the
// same shape so mnemo_api.mbt's hand-rolled JSON builders don't change.

///|
pub struct JsRow {
  column_index : Map[String, Int]
  cells : Array[@sqlite.SqlValue]
}

///|
pub fn _row_at(rows : JsRows, idx : Int) -> JsRow {
  if idx < 0 || idx >= rows.rows.length() {
    return { column_index: rows.column_index, cells: [] }
  }
  { column_index: rows.column_index, cells: rows.rows[idx] }
}

///|
fn row_cell(row : JsRow, col : String) -> @sqlite.SqlValue {
  match row.column_index.get(col) {
    None => Null
    Some(i) =>
      if i < 0 || i >= row.cells.length() {
        Null
      } else {
        row.cells[i]
      }
  }
}

///|
pub fn _row_is_null(row : JsRow, col : String) -> Bool {
  match row_cell(row, col) {
    Null => true
    _ => false
  }
}

///|
pub fn _row_get_text(row : JsRow, col : String) -> String {
  match row_cell(row, col) {
    Text(b) => @utf8.decode_lossy(b[:])
    Blob(b) => @utf8.decode_lossy(b[:])
    Int(n) => n.to_string()
    Int64(n) => n.to_string()
    Double(n) => n.to_string()
    Null => ""
  }
}

///|
pub fn _row_get_int(row : JsRow, col : String) -> Int {
  match row_cell(row, col) {
    Int(n) => n
    Int64(n) => n.to_int()
    Double(n) => n.to_int()
    Text(b) => {
      let s = @utf8.decode_lossy(b[:])
      (try? @string.parse_int(s.view())).or(0)
    }
    _ => 0
  }
}

///|
pub fn _row_get_int64(row : JsRow, col : String) -> Int64 {
  match row_cell(row, col) {
    Int64(n) => n
    Int(n) => n.to_int64()
    Double(n) => n.to_int64()
    Text(b) => {
      let s = @utf8.decode_lossy(b[:])
      (try? @string.parse_int64(s.view())).or(0L)
    }
    _ => 0L
  }
}

// --- Execute / query ---

///|
fn flush_bindings(stmt : SqliteStmt) -> Unit {
  let _ = stmt.inner.bind_all(stmt.pending)

}

///|
pub fn stmt_run(stmt : SqliteStmt) -> Unit {
  flush_bindings(stmt)
  let _ = stmt.inner.execute()
  stmt.inner.reset()
  stmt.pending = []
}

///|
/// A fully-materialized result set. Each row is an `Array[SqlValue]`
/// indexed by the order columns appear in the SELECT. The name→index
/// map lets callers continue to write `stmt_get_text(rows, row_idx, "col")`.
pub struct JsRows {
  column_index : Map[String, Int]
  rows : Array[Array[@sqlite.SqlValue]]
}

///|
pub fn stmt_all(stmt : SqliteStmt) -> JsRows {
  flush_bindings(stmt)
  let cols = stmt.inner.column_count()
  let column_index : Map[String, Int] = {}
  for c in 0.. Int {
  rows.rows.length()
}

///|
fn col_idx(rows : JsRows, col : String) -> Int {
  match rows.column_index.get(col) {
    Some(i) => i
    // -1 is treated by all getters as "missing" → default value
    None => -1
  }
}

///|
fn cell_at(rows : JsRows, idx : Int, col : String) -> @sqlite.SqlValue {
  let ci = col_idx(rows, col)
  if ci < 0 || idx < 0 || idx >= rows.rows.length() {
    return Null
  }
  let row = rows.rows[idx]
  if ci >= row.length() {
    return Null
  }
  row[ci]
}

///|
pub fn stmt_get_text(rows : JsRows, idx : Int, col : String) -> String {
  match cell_at(rows, idx, col) {
    Text(b) => @utf8.decode_lossy(b[:])
    Null => ""
    Int(n) => n.to_string()
    Int64(n) => n.to_string()
    Double(n) => n.to_string()
    Blob(b) => @utf8.decode_lossy(b[:])
  }
}

///|
pub fn stmt_get_int(rows : JsRows, idx : Int, col : String) -> Int {
  match cell_at(rows, idx, col) {
    Int(n) => n
    Int64(n) => n.to_int()
    Double(n) => n.to_int()
    Text(b) => {
      let s = @utf8.decode_lossy(b[:])
      (try? @string.parse_int(s.view())).or(0)
    }
    _ => 0
  }
}

///|
pub fn stmt_get_int64(rows : JsRows, idx : Int, col : String) -> Int64 {
  match cell_at(rows, idx, col) {
    Int64(n) => n
    Int(n) => n.to_int64()
    Double(n) => n.to_int64()
    Text(b) => {
      let s = @utf8.decode_lossy(b[:])
      (try? @string.parse_int64(s.view())).or(0L)
    }
    _ => 0L
  }
}

///|
pub fn stmt_get_text_opt(rows : JsRows, idx : Int, col : String) -> String? {
  match cell_at(rows, idx, col) {
    Null => None
    other =>
      Some(
        match other {
          Text(b) => @utf8.decode_lossy(b[:])
          Int(n) => n.to_string()
          Int64(n) => n.to_string()
          Double(n) => n.to_string()
          Blob(b) => @utf8.decode_lossy(b[:])
          Null => ""
        },
      )
  }
}

///|
pub fn stmt_get_int_opt(rows : JsRows, idx : Int, col : String) -> Int? {
  match cell_at(rows, idx, col) {
    Null => None
    Int(n) => Some(n)
    Int64(n) => Some(n.to_int())
    Double(n) => Some(n.to_int())
    _ => None
  }
}

///|
pub fn stmt_get_int64_opt(rows : JsRows, idx : Int, col : String) -> Int64? {
  match cell_at(rows, idx, col) {
    Null => None
    Int64(n) => Some(n)
    Int(n) => Some(n.to_int64())
    Double(n) => Some(n.to_int64())
    _ => None
  }
}

// --- Finalize ---

///|
pub fn stmt_finalize(stmt : SqliteStmt) -> Unit {
  stmt.inner.finalize()
}

// --- Transaction control ---

///|
pub fn tx_begin_immediate(db : SqliteDb) -> Unit {
  let _ = db.inner.begin_immediate()

}

///|
pub fn tx_commit(db : SqliteDb) -> Unit {
  let _ = db.inner.commit()

}

///|
pub fn tx_rollback(db : SqliteDb) -> Unit {
  let _ = db.inner.rollback()

}

///|
pub fn last_insert_rowid(db : SqliteDb) -> Int {
  db.inner.last_insert_rowid().to_int()
}

// --- Phase 0 spike kept for `just spike` ---

///|
pub fn spike_sqlite() -> Unit {
  let db = open_db(":memory:")
  db_exec(db, "CREATE TABLE t (id INTEGER PRIMARY KEY, v TEXT)")
  let ins = prepare(db, "INSERT INTO t (v) VALUES (?)")
  stmt_bind_text(ins, 1, "hello")
  stmt_run(ins)
  let sel = prepare(db, "SELECT v FROM t WHERE id = ?")
  stmt_bind_int(sel, 1, 1)
  let rows = stmt_all(sel)
  let result = stmt_get_text(rows, 0, "v")
  println("moonbit sqlite ok: \{result}")
}

// --- Inline tests ---

///|
test "sqlite_ffi: prepare + bind text + run + all" {
  let db = open_db(":memory:")
  db_exec(
    db,
    "CREATE TABLE items (id INTEGER PRIMARY KEY, name TEXT, score INTEGER, note TEXT)",
  )
  let ins = prepare(db, "INSERT INTO items (name, score) VALUES (?, ?)")
  stmt_bind_text(ins, 1, "alpha")
  stmt_bind_int(ins, 2, 42)
  stmt_run(ins)
  stmt_bind_text(ins, 1, "beta")
  stmt_bind_null(ins, 2)
  stmt_run(ins)
  let sel = prepare(db, "SELECT name, score, note FROM items ORDER BY id")
  let rows = stmt_all(sel)
  assert_eq(rows_length(rows), 2)
  assert_eq(stmt_get_text(rows, 0, "name"), "alpha")
  assert_eq(stmt_get_int(rows, 0, "score"), 42)
  assert_eq(stmt_get_int_opt(rows, 0, "score"), Some(42))
  assert_eq(stmt_get_text(rows, 1, "name"), "beta")
  assert_eq(stmt_get_int_opt(rows, 1, "score"), None)
  assert_eq(stmt_get_text_opt(rows, 0, "note"), None)
}

///|
test "sqlite_ffi: transaction commit" {
  let db = open_db(":memory:")
  db_exec(db, "CREATE TABLE items (id INTEGER PRIMARY KEY, name TEXT)")
  tx_begin_immediate(db)
  let ins = prepare(db, "INSERT INTO items (name) VALUES (?)")
  stmt_bind_text(ins, 1, "committed")
  stmt_run(ins)
  tx_commit(db)
  let sel = prepare(db, "SELECT name FROM items")
  let rows = stmt_all(sel)
  assert_eq(rows_length(rows), 1)
  assert_eq(stmt_get_text(rows, 0, "name"), "committed")
}

///|
test "sqlite_ffi: transaction rollback" {
  let db = open_db(":memory:")
  db_exec(db, "CREATE TABLE items (id INTEGER PRIMARY KEY, name TEXT)")
  tx_begin_immediate(db)
  let ins = prepare(db, "INSERT INTO items (name) VALUES (?)")
  stmt_bind_text(ins, 1, "rolled_back")
  stmt_run(ins)
  tx_rollback(db)
  let sel = prepare(db, "SELECT name FROM items")
  let rows = stmt_all(sel)
  assert_eq(rows_length(rows), 0)
}

///|
test "sqlite_ffi: last_insert_rowid" {
  let db = open_db(":memory:")
  db_exec(db, "CREATE TABLE items (id INTEGER PRIMARY KEY, name TEXT)")
  let ins = prepare(db, "INSERT INTO items (name) VALUES (?)")
  stmt_bind_text(ins, 1, "row1")
  stmt_run(ins)
  let rowid = last_insert_rowid(db)
  assert_eq(rowid, 1)
}