#include <sqlite3.h>

// TODO all the sql statements are prepared every time, prepare them once (and reset correctly)

/* ------------------------------------------------------------------------- */
/*  Internal helpers                                                         */
/* ------------------------------------------------------------------------- */

static sqlite3 *g_db = NULL;

/* Convert milliseconds timestamp to ISO 8601 string (UTC) */
static size_t ms_to_iso(uint64_t ms, char *out, size_t out_len) {
  time_t sec = ms / 1000;
  struct tm tm;
  gmtime_r(&sec, &tm);
  return strftime(out, out_len, "%Y-%m-%dT%H:%M:%SZ", &tm);
}

/* Execute a SQL statement and ignore result (for pragmas, etc.) */
static void exec_sql(const char *sql) {
  char *errmsg = NULL;
  sqlite3_exec(g_db, sql, NULL, NULL, &errmsg);
  if (errmsg) {
    warn("SQL exec error: %s\n", errmsg);
    sqlite3_free(errmsg);
  }
}

/* ------------------------------------------------------------------------- */
/*  Public API                                                               */
/* ------------------------------------------------------------------------- */

static char const *db_errmsg(void){
  return sqlite3_errmsg(g_db);
}

static void db_seed_default_rooms_(void) {
  exec_sql(
    "INSERT OR IGNORE INTO rooms (id, name, banner) VALUES "
    "('general', 'General', 'General discussions');"
  );
}

static bool db_init(const char *db_path) {
  int rc = sqlite3_open(db_path, &g_db);
  if (rc != SQLITE_OK) {
    warn("Cannot open database: %s\n", sqlite3_errmsg(g_db));
    sqlite3_close(g_db);
    g_db = NULL;
    return false;
  }

  /* Enable WAL and other pragmas */
  exec_sql("PRAGMA journal_mode=WAL;");
  exec_sql("PRAGMA mmap_size=268435456;");
  exec_sql("PRAGMA foreign_keys=ON;");
  exec_sql("PRAGMA synchronous=NORMAL;");

  /* Create tables if they don't exist (full schema from earlier) */
  const char *create_sql =
    "CREATE TABLE IF NOT EXISTS users ("
    "  id TEXT PRIMARY KEY, username TEXT UNIQUE NOT NULL, password TEXT NOT NULL,"
    "  created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')), last_seen_at INTEGER);"
    "CREATE TABLE IF NOT EXISTS avatars ("
    "  id TEXT PRIMARY KEY,"
    "  user_id TEXT NOT NULL UNIQUE,"
    "  data BLOB NOT NULL,"
    "  mime_type TEXT NOT NULL,"
    "  url TEXT NOT NULL,"
    "  created_at INTEGER NOT NULL DEFAULT (strftime('%s','now')),"
    "  FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE"
    ");"
    "CREATE INDEX IF NOT EXISTS idx_avatars_url ON avatars(url);"
    "CREATE TABLE IF NOT EXISTS rooms ("
    "  id TEXT PRIMARY KEY, name TEXT NOT NULL, banner TEXT,"
    "  last_message_at INTEGER, created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')));"
    "CREATE TABLE IF NOT EXISTS room_members ("
    "  room_id TEXT NOT NULL, user_id TEXT NOT NULL, joined_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),"
    "  last_read_at INTEGER, PRIMARY KEY (room_id, user_id),"
    "  FOREIGN KEY(room_id) REFERENCES rooms(id) ON DELETE CASCADE,"
    "  FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE);"
    "CREATE TABLE IF NOT EXISTS messages ("
    "  id TEXT PRIMARY KEY, room_id TEXT NOT NULL, sender_id TEXT NOT NULL,"
    "  text TEXT, created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),"
    "  edited_at INTEGER,"
    "  FOREIGN KEY(room_id) REFERENCES rooms(id) ON DELETE CASCADE,"
    "  FOREIGN KEY(sender_id) REFERENCES users(id) ON DELETE RESTRICT);"
    "CREATE TABLE IF NOT EXISTS attachments ("
    "  id TEXT PRIMARY KEY,"
    "  message_id TEXT,"
    "  mime_type TEXT NOT NULL,"
    "  name TEXT NOT NULL,"
    "  url TEXT NOT NULL,"
    "  data BLOB,"
    "  created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),"
    "  FOREIGN KEY(message_id) REFERENCES messages(id) ON DELETE CASCADE);"
    "CREATE TABLE IF NOT EXISTS message_deliveries ("
    "  message_id TEXT NOT NULL, user_id TEXT NOT NULL,"
    "  delivered_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')), read_at INTEGER,"
    "  PRIMARY KEY (message_id, user_id),"
    "  FOREIGN KEY(message_id) REFERENCES messages(id) ON DELETE CASCADE,"
    "  FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE);"
    "CREATE INDEX IF NOT EXISTS idx_messages_room_created_at ON messages(room_id, created_at DESC);"
    "CREATE INDEX IF NOT EXISTS idx_room_members_user ON room_members(user_id);"
    "CREATE INDEX IF NOT EXISTS idx_deliveries_user_unread ON message_deliveries(user_id, read_at) WHERE read_at IS NULL;";

  rc = sqlite3_exec(g_db, create_sql, NULL, NULL, NULL);
  if (rc != SQLITE_OK) {
    warn("Failed to create tables: %s\n", sqlite3_errmsg(g_db));
    sqlite3_close(g_db);
    g_db = NULL;
    return false;
  }

  /* Create trigger for last_message_at */
  exec_sql(
    "CREATE TRIGGER IF NOT EXISTS update_room_last_message "
    "AFTER INSERT ON messages BEGIN "
    "  UPDATE rooms SET last_message_at = NEW.created_at "
    "  WHERE id = NEW.room_id AND (last_message_at IS NULL OR NEW.created_at > last_message_at); "
    "END;"
  );
  /* Create trigger for automatic delivery receipts */
  exec_sql(
    "CREATE TRIGGER IF NOT EXISTS create_deliveries "
    "AFTER INSERT ON messages BEGIN "
    "  INSERT INTO message_deliveries (message_id, user_id, delivered_at) "
    "  SELECT NEW.id, rm.user_id, strftime('%s','now') "
    "  FROM room_members rm WHERE rm.room_id = NEW.room_id AND rm.user_id != NEW.sender_id; "
    "END;"
  );

  db_seed_default_rooms_();

  return true;
}

static void db_close(void) {
  if (g_db) {
    sqlite3_close(g_db);
    g_db = NULL;
  }
}

// get the username given the user_id
static str db_get_username(arena *A, str user_id) {
  if (user_id.len == 0) return (str){0};

  const char *sql = "SELECT username FROM users WHERE id = ?;";
  sqlite3_stmt *stmt = NULL;
  int rc = sqlite3_prepare_v2(g_db, sql, -1, &stmt, NULL);
  if (rc != SQLITE_OK) {
    return (str){0};
  }

  sqlite3_bind_text(stmt, 1, (const char*)user_id.data, user_id.len, SQLITE_STATIC);

  str result = (str){0};
  if (sqlite3_step(stmt) == SQLITE_ROW) {
    const unsigned char *username = sqlite3_column_text(stmt, 0);
    int username_len = sqlite3_column_bytes(stmt, 0);
    if (username && username_len > 0) {
      result = str_new_from_buf(A, username, (size_t)username_len);
    }
  }

  sqlite3_finalize(stmt);
  return result;
}

// db_authenticate_user: authenticates existing user.
// Returns a str containing BLISP like #(:id #26'06EQFVKN9FPXNY0Y2BK0GMR3C0' :username #5'frotz' :avatar_url #0''))
// If user does not exist or error, returns empty str (len==0) and out_user_id is also empty.
// On success, out_user_id is set to a str pointing to a copy of the user_id allocated from the scratch arena.
// The returned JSON string is allocated in the perm arena.
static str db_authenticate_user(arena *perm, arena *scratch, str username, str password, str *out_user_id) {
  unless(g_db) die("db_authenticate_user forgot to call db_init()\n");
  (void)scratch;
  if (out_user_id) *out_user_id = (str){0};

  if (username.len == 0 || password.len == 0) return (str){0};

  sqlite3_stmt *stmt = NULL;
  const char *sql = "SELECT u.id, u.username, a.url, u.password FROM users u LEFT JOIN avatars a ON u.id = a.user_id WHERE u.username = ?;";
  int rc = sqlite3_prepare_v2(g_db, sql, -1, &stmt, NULL);
  if (rc != SQLITE_OK) {
    return (str){0};
  }
  sqlite3_bind_text(stmt, 1, (const char*)username.data, username.len, SQLITE_STATIC);

  int exists = (sqlite3_step(stmt) == SQLITE_ROW);
  if (!exists) {
    sqlite3_finalize(stmt);
    return (str){0};
  }

  // Extract columns as str
  str id = str_from_buf(sqlite3_column_text(stmt, 0), sqlite3_column_bytes(stmt, 0));
  str db_username = str_from_buf(sqlite3_column_text(stmt, 1), sqlite3_column_bytes(stmt, 1));
  str avatar_url = str_from_buf(sqlite3_column_text(stmt, 2), sqlite3_column_bytes(stmt, 2));
  str stored_hash = str_from_buf(sqlite3_column_text(stmt, 3), sqlite3_column_bytes(stmt, 3));

  str user_blisp = {0};

  // Verify password using bcrypt
  if (!verify_password(password, stored_hash)) {
    goto Return;
  }

  // Copy user_id to output (using perm arena for persistence)
  if (out_user_id && id.len > 0) {
    *out_user_id = str_new_from_buf(perm, id.data, id.len);
  }

  // Build BLISP user object
  user_blisp = str_printf(perm,
      "#(:id #%d'%.*s' :username #%d'%.*s' :avatar_url #%d'%.*s')",
      (int)id.len, pstr(id),
      (int)db_username.len, pstr(db_username),
      (int)avatar_url.len, pstr(avatar_url));

Return:
  sqlite3_finalize(stmt);
  return user_blisp;
}

// db_get_user_rooms: returns BLISP array of rooms or empty string on error.
static str db_get_user_rooms(arena *perm, arena *scratch, str user_id) {
  (void)scratch;
  if (user_id.len == 0) return (str){0};

  const char *sql =
    "SELECT r.id, r.name, r.banner, r.last_message_at "
    "FROM rooms r JOIN room_members rm ON r.id = rm.room_id "
    "WHERE rm.user_id = ? ORDER BY r.last_message_at DESC;";
  sqlite3_stmt *stmt = NULL;
  int rc = sqlite3_prepare_v2(g_db, sql, -1, &stmt, NULL);
  if (rc != SQLITE_OK) {
    return (str){0};
  }
  sqlite3_bind_text(stmt, 1, (const char*)user_id.data, user_id.len, SQLITE_STATIC);

  // BLISP mode: build array (list)
  str result = str_printf(perm, "(");
  int row_count = 0;
  while (sqlite3_step(stmt) == SQLITE_ROW) {
    if (row_count > 0) {
      result = str_printf_append(perm, result, " ");
    }

    const unsigned char *id = sqlite3_column_text(stmt, 0);
    int id_len = sqlite3_column_bytes(stmt, 0);
    const unsigned char *name = sqlite3_column_text(stmt, 1);
    int name_len = sqlite3_column_bytes(stmt, 1);
    const unsigned char *banner = sqlite3_column_text(stmt, 2);
    int banner_len = sqlite3_column_bytes(stmt, 2);
    int64_t last_msg_ms = sqlite3_column_int64(stmt, 3);

    result = str_printf_append(perm, result,
        "#(:id #%d'%.*s' :name #%d'%.*s' :banner #%d'%.*s'",
        id_len, id_len, id,
        name_len, name_len, name,
        banner_len, banner_len, banner ? (const char*)banner : "");

    if (last_msg_ms) {
      char last_iso[32] = "";
      int const iso_len = ms_to_iso((uint64_t)last_msg_ms, last_iso, sizeof(last_iso));
      result = str_printf_append(perm, result, " :last_message_at #%d'%s'", iso_len, last_iso);
    }
    result = str_printf_append(perm, result, ")");
    row_count++;
  }
  sqlite3_finalize(stmt);

  result = str_printf_append(perm, result, ")\n");
  return result;
}

static str db_get_messages(arena *perm, arena *scratch, str room_id, str before_ulid, int limit) {
  (void)scratch;
  if (room_id.len == 0 || limit < 1) return (str){0};
  if (limit > 200) limit = 200;

  const char *sql;
  sqlite3_stmt *stmt = NULL;
  int rc;

  if (before_ulid.len > 0) {
    sql = "SELECT m.id, m.sender_id, u.username, m.text, m.created_at, "
          "a.id, a.mime_type, a.name, a.url "
          "FROM ("
          "  SELECT id, sender_id, text, created_at "
          "  FROM messages "
          "  WHERE room_id = ? AND created_at < (SELECT created_at FROM messages WHERE id = ?) "
          "  ORDER BY created_at DESC LIMIT ?"
          ") m "
          "JOIN users u ON m.sender_id = u.id "
          "LEFT JOIN attachments a ON m.id = a.message_id "
          "ORDER BY m.created_at DESC, a.id DESC;";
    rc = sqlite3_prepare_v2(g_db, sql, -1, &stmt, NULL);
    if (rc == SQLITE_OK) {
      sqlite3_bind_text(stmt, 1, (const char*)room_id.data, room_id.len, SQLITE_STATIC);
      sqlite3_bind_text(stmt, 2, (const char*)before_ulid.data, before_ulid.len, SQLITE_STATIC);
      sqlite3_bind_int(stmt, 3, limit);
    }
  } else {
    sql = "SELECT m.id, m.sender_id, u.username, m.text, m.created_at, "
          "a.id, a.mime_type, a.name, a.url "
          "FROM ("
          "  SELECT id, sender_id, text, created_at "
          "  FROM messages "
          "  WHERE room_id = ? "
          "  ORDER BY created_at DESC LIMIT ?"
          ") m "
          "JOIN users u ON m.sender_id = u.id "
          "LEFT JOIN attachments a ON m.id = a.message_id "
          "ORDER BY m.created_at DESC, a.id DESC;";
    rc = sqlite3_prepare_v2(g_db, sql, -1, &stmt, NULL);
    if (rc == SQLITE_OK) {
      sqlite3_bind_text(stmt, 1, (const char*)room_id.data, room_id.len, SQLITE_STATIC);
      sqlite3_bind_int(stmt, 2, limit);
    }
  }

  if (rc != SQLITE_OK) {
    return (str){0};
  }

  char prev_msg_id_[ulid_byte_len];
  str prev_msg_id = str_from_buf(prev_msg_id_, sizeof(prev_msg_id_));
  str result = str_printf(perm, "#(:messages (");
  int message_count = 0;
  bool first_attachment = true;

  while (sqlite3_step(stmt) == SQLITE_ROW) {
    // Create str views for each column
    str msg_id = str_from_buf(sqlite3_column_text(stmt, 0), sqlite3_column_bytes(stmt, 0));
    str sender_id = str_from_buf(sqlite3_column_text(stmt, 1), sqlite3_column_bytes(stmt, 1));
    str sender_name = str_from_buf(sqlite3_column_text(stmt, 2), sqlite3_column_bytes(stmt, 2));
    str text = str_from_buf(sqlite3_column_text(stmt, 3), sqlite3_column_bytes(stmt, 3));
    int64_t ts_ms = sqlite3_column_int64(stmt, 4);
    // Attachment columns (may be NULL)
    str att_id = str_from_buf(sqlite3_column_text(stmt, 5), sqlite3_column_bytes(stmt, 5));
    str mime_type = str_from_buf(sqlite3_column_text(stmt, 6), sqlite3_column_bytes(stmt, 6));
    str att_name = str_from_buf(sqlite3_column_text(stmt, 7), sqlite3_column_bytes(stmt, 7));
    str url = str_from_buf(sqlite3_column_text(stmt, 8), sqlite3_column_bytes(stmt, 8));

    bool const is_new_message = !(str_eq(prev_msg_id, msg_id));

    if(is_new_message){
      bool const in_open_message = message_count;
      if(in_open_message){
        result = str_printf_append(perm, result, ")) ");
      }
      char ts_iso[32];
      int const ts_len = ms_to_iso((uint64_t)ts_ms, ts_iso, sizeof(ts_iso));

      result = str_printf_append(perm, result,
          "#(:id #%d'%.*s' :sender_id #%d'%.*s' :sender_name #%d'%.*s' "
          ":text #%d'%.*s' :created_at #%d'%s' :attachments (",
          (int)msg_id.len, pstr(msg_id),
          (int)sender_id.len, pstr(sender_id),
          (int)sender_name.len, pstr(sender_name),
          (int)text.len, pstr(text),
          ts_len, ts_iso);

      str_update(&prev_msg_id, msg_id);
      first_attachment = true;
      message_count++;
    }

    bool const has_attachment = (att_id.len > 0);
    if(has_attachment){
      if(!first_attachment) result = str_printf_append(perm, result, " ");
      first_attachment = false;
      result = str_printf_append(perm, result,
          "#(:id #%d'%.*s' :mime_type #%d'%.*s' :name #%d'%.*s' :url #%d'%.*s')",
          (int)att_id.len, pstr(att_id),
          (int)mime_type.len, pstr(mime_type),
          (int)att_name.len, pstr(att_name),
          (int)url.len, pstr(url));
    }
  }

  if (message_count) result = str_printf_append(perm, result, "))");

  sqlite3_finalize(stmt);

  // Check for more messages (unchanged)
  int has_more = 0;
  if (before_ulid.len > 0) {
    sqlite3_stmt *more_stmt = NULL;
    char const *more_sql = "SELECT 1 FROM messages "
               "WHERE room_id = ? AND created_at < (SELECT created_at FROM messages WHERE id = ?) "
               "ORDER BY created_at DESC LIMIT 1 OFFSET ?;";
    rc = sqlite3_prepare_v2(g_db, more_sql, -1, &more_stmt, NULL);
    if (rc == SQLITE_OK) {
      sqlite3_bind_text(more_stmt, 1, (const char*)room_id.data, room_id.len, SQLITE_STATIC);
      sqlite3_bind_text(more_stmt, 2, (const char*)before_ulid.data, before_ulid.len, SQLITE_STATIC);
      sqlite3_bind_int(more_stmt, 3, limit);
      has_more = (sqlite3_step(more_stmt) == SQLITE_ROW);
      sqlite3_finalize(more_stmt);
    }
  } else {
    sqlite3_stmt *more_stmt = NULL;
    char const *more_sql = "SELECT 1 FROM messages WHERE room_id = ? ORDER BY created_at DESC LIMIT 1 OFFSET ?;";
    rc = sqlite3_prepare_v2(g_db, more_sql, -1, &more_stmt, NULL);
    if (rc == SQLITE_OK) {
      sqlite3_bind_text(more_stmt, 1, (const char*)room_id.data, room_id.len, SQLITE_STATIC);
      sqlite3_bind_int(more_stmt, 2, limit);
      has_more = (sqlite3_step(more_stmt) == SQLITE_ROW);
      sqlite3_finalize(more_stmt);
    }
  }

  result = str_printf_append(perm, result, ") :has_more %s)", has_more ? ":true" : ":false");
  return result;
}

// Insert an attachment record into the database (without message_id).
// Returns true on success, false on error.
static bool db_insert_attachment(str attachment_id, str mime_type, str name, str url, str data) {
  if (attachment_id.len == 0) return false;

  // message_id column is omitted – will be set later via db_set_attachment_message_id
  const char *sql = "INSERT INTO attachments (id, mime_type, name, url, data) VALUES (?, ?, ?, ?, ?);";
  sqlite3_stmt *stmt = NULL;
  int rc = sqlite3_prepare_v2(g_db, sql, -1, &stmt, NULL);
  if (rc != SQLITE_OK) {
    return false;
  }

  sqlite3_bind_text(stmt, 1, (const char*)attachment_id.data, attachment_id.len, SQLITE_STATIC);
  sqlite3_bind_text(stmt, 2, (const char*)mime_type.data, mime_type.len, SQLITE_STATIC);
  sqlite3_bind_text(stmt, 3, (const char*)name.data, name.len, SQLITE_STATIC);
  sqlite3_bind_text(stmt, 4, (const char*)url.data, url.len, SQLITE_STATIC);
  sqlite3_bind_blob(stmt, 5, data.data, data.len, SQLITE_STATIC);

  rc = sqlite3_step(stmt);
  sqlite3_finalize(stmt);

  return rc == SQLITE_DONE;
}

// Set the message_id for an existing attachment (after the message is inserted).
// Returns true on success, false on error.
static bool db_set_attachment_message_id(str attachment_id, str message_id) {
  if (attachment_id.len == 0 || message_id.len == 0) return false;

  const char *sql = "UPDATE attachments SET message_id = ? WHERE id = ?;";
  sqlite3_stmt *stmt = NULL;
  int rc = sqlite3_prepare_v2(g_db, sql, -1, &stmt, NULL);
  if (rc != SQLITE_OK) {
    return false;
  }

  sqlite3_bind_text(stmt, 1, (const char*)message_id.data, message_id.len, SQLITE_STATIC);
  sqlite3_bind_text(stmt, 2, (const char*)attachment_id.data, attachment_id.len, SQLITE_STATIC);

  rc = sqlite3_step(stmt);
  sqlite3_finalize(stmt);

  return rc == SQLITE_DONE;
}

static bool db_insert_message(str room_id, str sender_id, str text, str attachments, str msg_id, uint64_t created_at) {
  if (room_id.len == 0 || sender_id.len == 0) return false;

  const char *sql = "INSERT INTO messages (id, room_id, sender_id, text, created_at) VALUES (?, ?, ?, ?, ?);";
  sqlite3_stmt *stmt = NULL;
  int rc = sqlite3_prepare_v2(g_db, sql, -1, &stmt, NULL);
  if (rc != SQLITE_OK) {
    return false;
  }

  sqlite3_bind_text(stmt, 1, (char const*)msg_id.data, msg_id.len, SQLITE_STATIC);
  sqlite3_bind_text(stmt, 2, (const char*)room_id.data, room_id.len, SQLITE_STATIC);
  sqlite3_bind_text(stmt, 3, (const char*)sender_id.data, sender_id.len, SQLITE_STATIC);
  sqlite3_bind_text(stmt, 4, text.len ? (const char*)text.data : "", text.len, SQLITE_STATIC);
  sqlite3_bind_int64(stmt, 5, created_at);
  rc = sqlite3_step(stmt);
  sqlite3_finalize(stmt);

  if (rc != SQLITE_DONE) {
    return false;
  }

  // attachments is the start of an array of these
  // #(:name 'genulid.c' :mime_type 'text/x-csrc' :url '/store/06ERTR7BZ7527YG8QBFM8Y2BWR')
  unless(attachments.len && '('==attachments.data[0]){
    debug("bad attachments [%.*s]\n", pstr(attachments));
    return false;
  }
  str ats = str_drop(attachments, 1);
  while(ats.len && ')'!=ats.data[0]){
    str id={0};
    str map = ats;
    unless(str_starts_with(map, kstr("#("))){ debug("bad attachment element\n"); return false; }
    map = str_drop(map, KSTR_LEN("#("));
    while(map.len && ')'!=map.data[0]){
      str key, val;
      str x = bl_get_symbol(map, &key);
      if(bl_fail(map, x)){ debug("bad attachment non symbol key\n"); return false; }
      unless(' '==x.data[0]){ debug("bad attachment non space after key\n"); return false; }
      map = str_drop(x,1);
      x = bl_get_string(map, &val);
      if(bl_fail(map, x)){ debug("bad attachment bad value\n"); return false; }
      if(x.len && ' '==x.data[0]) x = str_drop(x,1);
      map=x;
      if(str_eq(key, kstr("id"))) id=val; // TODO I could probably exit early here I don't use the rest of the message
    }
    unless(map.len && ')'==map.data[0]){ debug("bad attachment map missing closing )\n"); return false; }
    ats = str_drop(map, 1);
    if(ats.len && ' '==ats.data[0]) ats = str_drop(ats,1);

    if(0==id.len){
      debug("attachment missing id\n");
      return false;
    }
    if(!db_set_attachment_message_id(id, msg_id)) {
      debug("db_set_attachment_message_id failed\n");
      return false;
    }
  }

  return true;
}

static bool db_mark_message_read(const char *message_id, const char *user_id) {
  if (!message_id || !user_id) return false;
  const char *sql = "UPDATE message_deliveries SET read_at = strftime('%s','now') WHERE message_id = ? AND user_id = ?;";
  sqlite3_stmt *stmt = NULL;
  int rc = sqlite3_prepare_v2(g_db, sql, -1, &stmt, NULL);
  if (rc != SQLITE_OK) {
    return false;
  }
  sqlite3_bind_text(stmt, 1, message_id, -1, SQLITE_STATIC);
  sqlite3_bind_text(stmt, 2, user_id, -1, SQLITE_STATIC);
  sqlite3_step(stmt);
  sqlite3_finalize(stmt);
  return true;
}

static int db_get_unread_count(const char *user_id, const char *room_id) {
  if (!user_id) return -1;
  const char *sql;
  if (room_id) {
    sql = "SELECT COUNT(*) FROM message_deliveries d JOIN messages m ON d.message_id = m.id "
          "WHERE d.user_id = ? AND d.read_at IS NULL AND m.room_id = ?;";
  } else {
    sql = "SELECT COUNT(*) FROM message_deliveries WHERE user_id = ? AND read_at IS NULL;";
  }
  sqlite3_stmt *stmt = NULL;
  int rc = sqlite3_prepare_v2(g_db, sql, -1, &stmt, NULL);
  if (rc != SQLITE_OK) {
    return -1;
  }
  sqlite3_bind_text(stmt, 1, user_id, -1, SQLITE_STATIC);
  if (room_id)
    sqlite3_bind_text(stmt, 2, room_id, -1, SQLITE_STATIC);
  int count = 0;
  if (sqlite3_step(stmt) == SQLITE_ROW)
    count = sqlite3_column_int(stmt, 0);
  sqlite3_finalize(stmt);
  return count;
}

// Change user password after verifying old password
static bool db_change_user_password(arena *perm, arena *scratch, str user_id, str old, str new) {
  (void)scratch;
  if (user_id.len == 0 || old.len == 0 || new.len < 4) return false;

  // First, get the current password hash
  const char *sql_select = "SELECT password FROM users WHERE id = ?;";
  sqlite3_stmt *stmt = NULL;
  int rc = sqlite3_prepare_v2(g_db, sql_select, -1, &stmt, NULL);
  if (rc != SQLITE_OK) {
    return false;
  }

  sqlite3_bind_text(stmt, 1, (const char*)user_id.data, user_id.len, SQLITE_STATIC);

  if (sqlite3_step(stmt) != SQLITE_ROW) {
    sqlite3_finalize(stmt);
    return false;
  }

  str stored_hash = str_from_buf(sqlite3_column_text(stmt, 0), sqlite3_column_bytes(stmt, 0));

  // Verify old password
  if (!verify_password(old, stored_hash)) {
    debug("db_change_user_password: old password verification failed user_id: [%.*s]\n", pstr(user_id));
    sqlite3_finalize(stmt);
    return false;
  }
  sqlite3_finalize(stmt);

  // Create new password hash
  str new_hash = create_password_hash(perm, new, BCRYPT_DEFAULT_COST);
  if (new_hash.len == 0) {
    return false;
  }

  // Update the password
  const char *sql_update = "UPDATE users SET password = ? WHERE id = ?;";
  rc = sqlite3_prepare_v2(g_db, sql_update, -1, &stmt, NULL);
  if (rc != SQLITE_OK) {
    return false;
  }

  sqlite3_bind_text(stmt, 1, (const char*)new_hash.data, new_hash.len, SQLITE_STATIC);
  sqlite3_bind_text(stmt, 2, (const char*)user_id.data, user_id.len, SQLITE_STATIC);

  rc = sqlite3_step(stmt);
  sqlite3_finalize(stmt);

  unless(rc == SQLITE_DONE){
    debug("db_change_user_password: password update failed user_id: [%.*s]\n", pstr(user_id));
    return false;
  }
  return true;
}

// db_get_user_info: receives a BLISP list of user IDs
// Returns a BLISP list of user info maps OR empty str (len==0) on error
static str db_get_user_info(arena *perm, arena *scratch, str user_list) {
  (void)scratch;
  if (user_list.len == 0) return (str){0};

  unless(str_starts_with(user_list, kstr("(")) && str_ends_with(user_list, kstr(")"))) {
    warn("db_get_user_info: user_list must be a BLISP list\n");
    return (str){0};
  }

  uint8_t sql_arena_buf[8*1024];
  arena sql_arena;
  arena_init(&sql_arena, sql_arena_buf, sizeof(sql_arena_buf));

  enum{max_user_count=256};
  str user_ids[max_user_count];
  int nusers=0;
  str sql = str_printf(&sql_arena, "SELECT u.id, u.username, a.url FROM users u LEFT JOIN avatars a ON u.id = a.user_id WHERE u.id IN (");
  user_list = str_drop(user_list, 1);
  for(; user_list.len && ')'!=user_list.data[0]; ++nusers){
    if(nusers>max_user_count){
      debug("db_get_user_info: too many users (max %d)\n", max_user_count);
      return (str){0};
    }
    str user_id;
    str const rest = bl_get_string(user_list, &user_id);
    if(bl_fail(user_list, rest)){
      debug("db_get_user_info: failed to parse user id\n");
      return (str){0};
    }
    user_list = rest;

    user_ids[nusers] = user_id;
    if(nusers>0) sql = str_printf_append(&sql_arena, sql, ",");
    sql = str_printf_append(&sql_arena, sql, "?");

    if(user_list.len && ' '==user_list.data[0]) user_list = str_drop(user_list, 1);
  }
  unless(user_list.len && ')'==user_list.data[0]){
    debug("db_get_user_info: user_list missing final close brace");
    return (str){0};
  }
  sql = str_printf_append(&sql_arena, sql, ");");
  if(0 == nusers) return (str){0};

  debug("db_get_user_info: sql [%.*s]\n", pstr(sql));

  sqlite3_stmt *stmt = NULL;
  int rc = sqlite3_prepare_v2(g_db, (const char*)sql.data, sql.len, &stmt, NULL);
  if(rc != SQLITE_OK) {
    debug("db_get_user_info: prepare failed: %s\n", sqlite3_errmsg(g_db));
    return (str){0};
  }

  for(int i = 0; i < nusers; i++) {
    debug("db_get_user_info binding user_id[%d] = [%.*s]\n", i, pstr(user_ids[i]));
    sqlite3_bind_text(stmt, i + 1, (const char*)user_ids[i].data, user_ids[i].len, SQLITE_STATIC);
  }

  str result = str_printf(perm, "(");
  bool first = true;
  while(sqlite3_step(stmt) == SQLITE_ROW) {
    str user_id = str_from_buf(sqlite3_column_text(stmt, 0), sqlite3_column_bytes(stmt, 0));
    str username = str_from_buf(sqlite3_column_text(stmt, 1), sqlite3_column_bytes(stmt, 1));
    str avatar_url = str_from_buf(sqlite3_column_text(stmt, 2), sqlite3_column_bytes(stmt, 2));

    unless(first) result = str_printf_append(perm, result, " ");
    first = false;

    result = str_printf_append(perm, result,
      "#(:id #%d'%.*s' :username #%d'%.*s' :avatar_url #%d'%.*s')",
      (int)user_id.len, pstr(user_id),
      (int)username.len, pstr(username),
      (int)avatar_url.len, pstr(avatar_url));
  }
  result = str_printf_append(perm, result, ")");

  sqlite3_finalize(stmt);
  return result;
}

// db_update_avatar: add or replace user's avatar
// Returns true on success, false on error
static bool db_update_avatar(str avatar_id, str user_id, str mime_type, str url, str data) {
  debug("db_update_avatar avatar_id: [%.*s] user_id: [%.*s] mime_type: [%.*s] url: [%.*s] data.len: %ld\n" , pstr(avatar_id), pstr(user_id), pstr(mime_type), pstr(url), data.len);

  if (avatar_id.len == 0 || user_id.len == 0 || data.len == 0 || mime_type.len == 0){
    warn("db_update_avatar one is empty! avatar_id: [%.*s] user_id: [%.*s] mime_type: [%.*s] url: [%.*s] data.len: %ld\n" , pstr(avatar_id), pstr(user_id), pstr(mime_type), pstr(url), data.len);
    return false;
  }

  // Insert new avatar
  const char *sql = "INSERT OR REPLACE INTO avatars (id, user_id, data, mime_type, url, created_at) VALUES (?, ?, ?, ?, ?, strftime('%s','now'));";
  sqlite3_stmt *stmt = NULL;
  int rc = sqlite3_prepare_v2(g_db, sql, -1, &stmt, NULL);
  if (rc != SQLITE_OK) {
    debug("db_update_avatar: insert prepare failed: %s\n", sqlite3_errmsg(g_db));
    return false;
  }

  sqlite3_bind_text(stmt, 1, (const char*)avatar_id.data, avatar_id.len, SQLITE_STATIC);
  sqlite3_bind_text(stmt, 2, (const char*)user_id.data, user_id.len, SQLITE_STATIC);
  sqlite3_bind_blob(stmt, 3, data.data, data.len, SQLITE_STATIC);
  sqlite3_bind_text(stmt, 4, (const char*)mime_type.data, mime_type.len, SQLITE_STATIC);
  sqlite3_bind_text(stmt, 5, (const char*)url.data, url.len, SQLITE_STATIC);

  rc = sqlite3_step(stmt);
  sqlite3_finalize(stmt);

  if (rc != SQLITE_DONE) {
    debug("db_update_avatar: insert failed: %s\n", sqlite3_errmsg(g_db));
    return false;
  }

  return true;
}

// Check if a user is a member of a room
// Returns true if user is a member of the room, false otherwise
static bool db_is_user_member_of_room(str user_id, str room_id) {
  if (user_id.len == 0 || room_id.len == 0) return false;

  const char *sql = "SELECT 1 FROM room_members WHERE user_id = ? AND room_id = ? LIMIT 1;";
  sqlite3_stmt *stmt = NULL;
  int rc = sqlite3_prepare_v2(g_db, sql, -1, &stmt, NULL);
  if (rc != SQLITE_OK) {
    warn("db_is_user_member_of_room: prepare failed: %s\n", sqlite3_errmsg(g_db));
    return false;
  }

  sqlite3_bind_text(stmt, 1, (const char*)user_id.data, user_id.len, SQLITE_STATIC);
  sqlite3_bind_text(stmt, 2, (const char*)room_id.data, room_id.len, SQLITE_STATIC);

  bool is_member = (sqlite3_step(stmt) == SQLITE_ROW);
  sqlite3_finalize(stmt);

  debug("db_is_user_member_of_room: user_id=%.*s room_id=%.*s result=%d\n",
        pstr(user_id), pstr(room_id), is_member);

  return is_member;
}

// Get message by ID, returns text, sender_id, and created_at
// Returns true if found, false otherwise
static bool db_get_message_by_id(arena *A, str message_id, str *out_text, str *out_sender_id, uint64_t *out_created_at) {
  if (message_id.len == 0) return false;

  const char *sql = "SELECT text, sender_id, created_at FROM messages WHERE id = ?;";
  sqlite3_stmt *stmt = NULL;
  int rc = sqlite3_prepare_v2(g_db, sql, -1, &stmt, NULL);
  if (rc != SQLITE_OK) {
    warn("db_get_message_by_id: prepare failed: %s\n", sqlite3_errmsg(g_db));
    return false;
  }

  sqlite3_bind_text(stmt, 1, (const char*)message_id.data, message_id.len, SQLITE_STATIC);

  bool found = false;
  if (sqlite3_step(stmt) == SQLITE_ROW) {
    if (out_text) {
      const unsigned char *text = sqlite3_column_text(stmt, 0);
      int text_len = sqlite3_column_bytes(stmt, 0);
      if (text && text_len > 0) {
        *out_text = str_new_from_buf(A, text, (size_t)text_len);
      } else {
        *out_text = (str){0};
      }
    }
    if (out_sender_id) {
      const unsigned char *sender_id = sqlite3_column_text(stmt, 1);
      int sender_id_len = sqlite3_column_bytes(stmt, 1);
      if (sender_id && sender_id_len > 0) {
        *out_sender_id = str_new_from_buf(A, sender_id, (size_t)sender_id_len);
      } else {
        *out_sender_id = (str){0};
      }
    }
    if (out_created_at) {
      *out_created_at = (uint64_t)sqlite3_column_int64(stmt, 2);
    }
    found = true;
  }

  sqlite3_finalize(stmt);
  return found;
}

// Update message text and set edited_at timestamp
// Returns true on success, false on error
static bool db_update_message_text(str message_id, str new_text, uint64_t edited_at_ms) {
  if (message_id.len == 0) return false;

  const char *sql = "UPDATE messages SET text = ?, edited_at = ? WHERE id = ?;";
  sqlite3_stmt *stmt = NULL;
  int rc = sqlite3_prepare_v2(g_db, sql, -1, &stmt, NULL);
  if (rc != SQLITE_OK) {
    warn("db_update_message_text: prepare failed: %s\n", sqlite3_errmsg(g_db));
    return false;
  }

  sqlite3_bind_text(stmt, 1, (const char*)new_text.data, new_text.len, SQLITE_STATIC);
  sqlite3_bind_int64(stmt, 2, edited_at_ms);
  sqlite3_bind_text(stmt, 3, (const char*)message_id.data, message_id.len, SQLITE_STATIC);

  rc = sqlite3_step(stmt);
  sqlite3_finalize(stmt);

  if (rc != SQLITE_DONE) {
    debug("db_update_message_text: update failed: %s\n", sqlite3_errmsg(g_db));
    return false;
  }

  return true;
}

// Delete a message by ID (cascade will handle attachments and deliveries)
// Returns true on success, false on error
static bool db_delete_message(str message_id) {
  if (message_id.len == 0) return false;

  const char *sql = "DELETE FROM messages WHERE id = ?;";
  sqlite3_stmt *stmt = NULL;
  int rc = sqlite3_prepare_v2(g_db, sql, -1, &stmt, NULL);
  if (rc != SQLITE_OK) {
    warn("db_delete_message: prepare failed: %s\n", sqlite3_errmsg(g_db));
    return false;
  }

  sqlite3_bind_text(stmt, 1, (const char*)message_id.data, message_id.len, SQLITE_STATIC);

  rc = sqlite3_step(stmt);
  sqlite3_finalize(stmt);

  if (rc != SQLITE_DONE) {
    debug("db_delete_message: delete failed: %s\n", sqlite3_errmsg(g_db));
    return false;
  }

  return true;
}