zynk/db.h

#ifndef DB_H
#define DB_H

#include "zynk.h"

void tell_deliver(Session *s, const char *nick, const char *channel);
int asprintf(char **, const char *, ...);

/* ---- SQLite3 dynamic loading ---- */

void    *sqlite_lib;
int    (*db_open)(const char*,void**);
int    (*db_exec)(void*,const char*,int(*)(void*,int,char**,char**),void*,char**);
int    (*db_close)(void*);
int    (*db_prepare)(void*,const char*,int,void**,const char**);
int    (*db_bind_text)(void*,int,const char*,int,void(*)(void*));
int    (*db_step)(void*);
int    (*db_finalize)(void*);
const unsigned char *(*db_column_text)(void*,int);
void   (*db_free)(void*);
int    (*db_bind_int64)(void*,int,long long);
long long (*db_last_rowid)(void*);
void *db_handle;

#define LOAD_FUNC(var, name) do { \
  union { void *p; typeof(var) f; } _u; \
  _u.p = dlsym(sqlite_lib, name); \
  var = _u.f; \
} while(0)

/*
 * sqlite_init_lib - Dynamically load libsqlite3 and resolve function symbols.
 *
 * Iterates through a list of well-known library paths (platform-dependent)
 * attempting to dlopen() the SQLite3 shared object. Falls back to a generic
 * dlopen() of "libsqlite3.so.0" and "libsqlite3.so" if none of the hardcoded
 * paths succeed. On success, resolves all required SQLite3 function pointers
 * (sqlite3_open, sqlite3_exec, sqlite3_close, sqlite3_prepare_v2,
 * sqlite3_bind_text, sqlite3_step, sqlite3_finalize, sqlite3_column_text,
 * sqlite3_free, sqlite3_bind_int64, sqlite3_last_insert_rowid) via dlsym
 * using the LOAD_FUNC union trick for type-safe function pointer casts.
 * Validates that all symbols were resolved; on failure, closes the handle.
 *
 * Returns: 0 on success, -1 on failure (library not found or missing symbols).
 */
static int sqlite_init_lib(void) {
  static const char *paths[] = {
    "/usr/lib/x86_64-linux-gnu/libsqlite3.so.0",
    "/usr/lib/aarch64-linux-gnu/libsqlite3.so.0",
    "/usr/lib/libsqlite3.so.0",
    "/usr/lib64/libsqlite3.so.0",
    "/lib/x86_64-linux-gnu/libsqlite3.so.0",
    "/lib/aarch64-linux-gnu/libsqlite3.so.0",
    "/lib/libsqlite3.so.0",
  };
  for (size_t i = 0; i < sizeof(paths)/sizeof(paths[0]); i++) {
    sqlite_lib = dlopen(paths[i], RTLD_LAZY | RTLD_LOCAL);
    if (sqlite_lib) break;
  }
  if (!sqlite_lib) {
    sqlite_lib = dlopen("libsqlite3.so.0", RTLD_LAZY | RTLD_LOCAL);
    if (!sqlite_lib)
      sqlite_lib = dlopen("libsqlite3.so", RTLD_LAZY | RTLD_LOCAL);
  }
  if (!sqlite_lib) return -1;
  LOAD_FUNC(db_open,       "sqlite3_open");
  LOAD_FUNC(db_exec,       "sqlite3_exec");
  LOAD_FUNC(db_close,      "sqlite3_close");
  LOAD_FUNC(db_prepare,    "sqlite3_prepare_v2");
  LOAD_FUNC(db_bind_text,  "sqlite3_bind_text");
  LOAD_FUNC(db_step,       "sqlite3_step");
  LOAD_FUNC(db_finalize,   "sqlite3_finalize");
  LOAD_FUNC(db_column_text,"sqlite3_column_text");
  LOAD_FUNC(db_free,       "sqlite3_free");
  LOAD_FUNC(db_bind_int64, "sqlite3_bind_int64");
  LOAD_FUNC(db_last_rowid, "sqlite3_last_insert_rowid");
  if (!db_open || !db_exec || !db_close || !db_prepare ||
      !db_bind_text || !db_step || !db_finalize || !db_column_text ||
      !db_free || !db_bind_int64 || !db_last_rowid) {
    dlclose(sqlite_lib);
    sqlite_lib = NULL;
    return -1;
  }
  return 0;
}

/*
 * db_init - Initialize the SQLite3 database and create required tables.
 *
 * Calls sqlite_init_lib() to load the shared library, then opens (or creates)
 * the "zynk.db" database file. Creates the "zynk" key-value table with columns
 * (key TEXT PRIMARY KEY, value TEXT, nick TEXT, updated_at TEXT) and the
 * "ai_pending" table for tracking in-flight AI queries with columns
 * (id INTEGER PRIMARY KEY, question, answer, target, nick, status).
 * Clears stale ai_pending rows and any lastq/lasta history entries from the
 * previous session. On any error, logs to stderr and performs cleanup.
 *
 * Returns: 0 on success, -1 on failure.
 */
int db_init(void) {
  if (sqlite_init_lib() < 0) { fprintf(stderr, CLR_RED "Cannot load libsqlite3" CLR_RESET "\n"); return -1; }
  if (db_open("zynk.db", &db_handle) != SQLITE_OK) {
    fprintf(stderr, CLR_RED "DB open failed" CLR_RESET "\n");
    if (db_handle) { db_close(DB); db_handle = NULL; }
    return -1;
  }
  const char *sql_zynk = "CREATE TABLE IF NOT EXISTS zynk ("
    "key TEXT PRIMARY KEY, value TEXT NOT NULL, "
    "nick TEXT NOT NULL, updated_at TEXT DEFAULT (datetime('now','localtime')))";
  char *err = NULL;
  if (db_exec(DB, sql_zynk, NULL, NULL, &err) != SQLITE_OK) {
    fprintf(stderr, CLR_RED "SQL: %s" CLR_RESET "\n", err ? err : "unknown error");
    if (err) db_free(err);
    db_close_cleanup();
    return -1;
  }
  const char *sql_ai = "CREATE TABLE IF NOT EXISTS ai_pending ("
    "id INTEGER PRIMARY KEY, question TEXT, "
    "answer TEXT, target TEXT, nick TEXT, status TEXT DEFAULT 'idle')";
  if (db_exec(DB, sql_ai, NULL, NULL, &err) != SQLITE_OK) {
    fprintf(stderr, CLR_RED "SQL: %s" CLR_RESET "\n", err ? err : "unknown error");
    if (err) db_free(err);
    db_close_cleanup();
    return -1;
  }
  const char *sql_seen = "CREATE TABLE IF NOT EXISTS seen ("
    "nick TEXT PRIMARY KEY, last_seen TEXT, last_channel TEXT, last_action TEXT)";
  db_exec(DB, sql_seen, NULL, NULL, NULL);
  const char *sql_tell = "CREATE TABLE IF NOT EXISTS tell ("
    "id INTEGER PRIMARY KEY, from_nick TEXT, to_nick TEXT, "
    "message TEXT, created_at TEXT DEFAULT (datetime('now','localtime')))";
  db_exec(DB, sql_tell, NULL, NULL, NULL);
  const char *sql_clear_ai = "DELETE FROM ai_pending";
  db_exec(DB, sql_clear_ai, NULL, NULL, NULL);
  const char *sql_clear = "DELETE FROM zynk WHERE key LIKE 'lastq%' OR key LIKE 'lasta%'";
  db_exec(DB, sql_clear, NULL, NULL, NULL);
  return 0;
}

/*
 * db_close_cleanup - Close the SQLite3 database handle if it is open.
 *
 * Checks if the global db_handle is non-NULL, and if so, calls sqlite3_close
 * on it and resets the handle to NULL. Safe to call multiple times.
 *
 * Returns: void.
 */
void db_close_cleanup(void) { if (db_handle) { db_close(DB); db_handle = NULL; } }

/*
 * sqlite_cleanup - Unload the dynamically loaded SQLite3 shared library.
 *
 * Calls dlclose() on the sqlite_lib handle if non-NULL and resets it to NULL.
 * Should be called after db_close_cleanup() during shutdown.
 *
 * Returns: void.
 */
void sqlite_cleanup(void) { if (sqlite_lib) { dlclose(sqlite_lib); sqlite_lib = NULL; } }

/* ---- DB key-value operations ---- */

/*
 * db_set - Insert or update a key-value pair in the zynk database.
 *
 * Uses an INSERT with ON CONFLICT upsert: if the key already exists, the
 * value, nick (who set it), and updated_at timestamp are all replaced.
 * The nick parameter records which IRC user created or last updated the entry.
 *
 * Parameters:
 *   key   - The lookup key (stored lowercase by callers).
 *   value - The value string to store.
 *   nick  - The IRC nick of the user who is setting this value.
 *
 * Returns: 0 on success, -1 on SQLite error.
 */
int db_set(const char *key, const char *value, const char *nick) {
  void *stmt;
  const char *sql = "INSERT INTO zynk(key,value,nick,updated_at)"
    " VALUES(?,?,?,datetime('now','localtime'))"
    " ON CONFLICT(key) DO UPDATE SET value=excluded.value,"
    " nick=excluded.nick, updated_at=datetime('now','localtime')";
  if (db_prepare(DB, sql, -1, &stmt, NULL) != SQLITE_OK) return -1;
  db_bind_text(stmt, 1, key, -1, SQLITE_STATIC);
  db_bind_text(stmt, 2, value, -1, SQLITE_STATIC);
  db_bind_text(stmt, 3, nick, -1, SQLITE_STATIC);
  int rc = db_step(stmt);
  db_finalize(stmt);
  return rc == SQLITE_DONE ? 0 : -1;
}

/*
 * db_get_raw - Retrieve the raw value for a key without metadata.
 *
 * Queries the zynk table for the value associated with the given key.
 * Unlike db_get(), does NOT append attribution info (nick, timestamp).
 * Used internally for history rotation and existence checks.
 *
 * Parameters:
 *   key - The key to look up.
 *
 * Returns: A newly allocated string containing the value, or NULL if the
 *          key is not found. Caller must free() the result.
 */
char *db_get_raw(const char *key) {
  void *stmt;
  const char *sql = "SELECT value FROM zynk WHERE key=?";
  if (db_prepare(DB, sql, -1, &stmt, NULL) != SQLITE_OK) return NULL;
  db_bind_text(stmt, 1, key, -1, SQLITE_STATIC);
  if (db_step(stmt) != SQLITE_ROW) { db_finalize(stmt); return NULL; }
  const char *v = (const char*)db_column_text(stmt, 0);
  char *r = v ? strdup(v) : NULL;
  db_finalize(stmt);
  return r;
}

/*
 * db_get - Retrieve a key's value with attribution metadata.
 *
 * Queries the zynk table for the value, nick, and updated_at timestamp
 * for the given key. Returns a formatted string in the form
 * "value (set by nick on timestamp)".
 *
 * Parameters:
 *   key - The key to look up.
 *
 * Returns: A newly allocated formatted string, or NULL if the key is not
 *          found. Caller must free() the result.
 */
char *db_get(const char *key) {
  void *stmt;
  const char *sql = "SELECT value,nick,updated_at FROM zynk WHERE key=?";
  if (db_prepare(DB, sql, -1, &stmt, NULL) != SQLITE_OK) return NULL;
  db_bind_text(stmt, 1, key, -1, SQLITE_STATIC);
  if (db_step(stmt) != SQLITE_ROW) { db_finalize(stmt); return NULL; }
  const char *v = (const char*)db_column_text(stmt, 0);
  const char *n = (const char*)db_column_text(stmt, 1);
  const char *t = (const char*)db_column_text(stmt, 2);
  char *r = NULL;
  if (asprintf(&r, "%s (set by %s on %s)", v, n, t) < 0) r = NULL;
  db_finalize(stmt);
  return r;
}

/*
 * db_get_random - Fetch a random key-value pair from the zynk database.
 *
 * Selects one random row from the zynk table, excluding internal history
 * keys (those with prefix "lastq" or "lasta"). Returns a formatted string
 * "key = value (by nick on timestamp)". Used when the user sends bare "zynk"
 * without a key.
 *
 * Returns: A newly allocated formatted string, or NULL if the database is
 *          empty. Caller must free() the result.
 */
char *db_get_random(void) {
  void *stmt;
  const char *sql = "SELECT key,value,nick,updated_at FROM zynk"
    " WHERE key NOT LIKE 'lastq%' AND key NOT LIKE 'lasta%'"
    " ORDER BY RANDOM() LIMIT 1";
  if (db_prepare(DB, sql, -1, &stmt, NULL) != SQLITE_OK) return NULL;
  if (db_step(stmt) != SQLITE_ROW) { db_finalize(stmt); return NULL; }
  const char *k = (const char*)db_column_text(stmt, 0);
  const char *v = (const char*)db_column_text(stmt, 1);
  const char *n = (const char*)db_column_text(stmt, 2);
  const char *t = (const char*)db_column_text(stmt, 3);
  char *r = NULL;
  if (asprintf(&r, "%s = %s (by %s on %s)", k, v, n, t) < 0) r = NULL;
  db_finalize(stmt);
  return r;
}

/*
 * db_rotate_history - Rotate entries in the recent history ring buffer.
 *
 * Maintains a fixed-size (MAX_HISTORY) ring buffer of recent entries stored
 * under keys "lastq01".."lastqN" or "lasta01".."lastaN". Shifts all existing
 * entries by one position (N-1 <- N-2 <- ... <- 1 <- 0) and inserts the new
 * value at position 0. Used to keep context for AI queries (lastq = questions,
 * lasta = answers). Each entry is stored as-is without attribution metadata.
 *
 * Parameters:
 *   prefix - "lastq" for question history or "lasta" for answer history.
 *   value  - The new entry to insert at position 0.
 *
 * Returns: void.
 */
void db_rotate_history(const char *prefix, const char *value) {
  char *old[MAX_HISTORY];
  char kbuf[MAX_HISTORY][12];
  for (int i = 0; i < MAX_HISTORY; i++) {
    snprintf(kbuf[i], sizeof kbuf[i], "%s%02d", prefix, i + 1);
    old[i] = db_get_raw(kbuf[i]);
  }
  void *stmt;
  const char *sql = "INSERT INTO zynk(key,value,nick,updated_at)"
    " VALUES(?,?,?,datetime('now','localtime'))"
    " ON CONFLICT(key) DO UPDATE SET value=excluded.value,"
    " nick=excluded.nick, updated_at=datetime('now','localtime')";
  if (db_prepare(DB, sql, -1, &stmt, NULL) == SQLITE_OK) {
    db_bind_text(stmt, 1, kbuf[0], -1, SQLITE_STATIC);
    db_bind_text(stmt, 2, value, -1, SQLITE_STATIC);
    db_bind_text(stmt, 3, "", -1, SQLITE_STATIC);
    db_step(stmt);
    db_finalize(stmt);
  }
  for (int i = 0; i < MAX_HISTORY - 1; i++) {
    if (old[i] && db_prepare(DB, sql, -1, &stmt, NULL) == SQLITE_OK) {
      db_bind_text(stmt, 1, kbuf[i + 1], -1, SQLITE_STATIC);
      db_bind_text(stmt, 2, old[i], -1, SQLITE_STATIC);
      db_bind_text(stmt, 3, "", -1, SQLITE_STATIC);
      db_step(stmt);
      db_finalize(stmt);
    }
  }
  for (int i = 0; i < MAX_HISTORY; i++) free(old[i]);
}

#define db_update_lastq(v) db_rotate_history("lastq", v)
#define db_update_lasta(v) db_rotate_history("lasta", v)

/* ---- DB AI pending operations ---- */

/*
 * ai_start - Create a new pending AI query record in the database.
 *
 * Inserts a new row into the ai_pending table with status 'pending',
 * the question text, an empty answer, the target (channel or nick to
 * reply to), and the requesting user's nick. Returns the auto-incremented
 * row ID via the out_id parameter for later retrieval of the answer.
 *
 * Parameters:
 *   question - The user's question or code change request.
 *   target   - The IRC target to send the reply to (channel or nick).
 *   nick     - The IRC nick of the user who asked.
 *   out_id   - Pointer to receive the new row's ID.
 *
 * Returns: 0 on success, -1 on error.
 */
int ai_start(const char *question, const char *target, const char *nick, long long *out_id) {
  const char *sql = "INSERT INTO ai_pending(question,answer,target,nick,status)"
    " VALUES(?,?,?,?,'pending')";
  void *stmt;
  if (db_prepare(DB, sql, -1, &stmt, NULL) != SQLITE_OK) return -1;
  db_bind_text(stmt, 1, question, -1, SQLITE_STATIC);
  db_bind_text(stmt, 2, "", -1, SQLITE_STATIC);
  db_bind_text(stmt, 3, target, -1, SQLITE_STATIC);
  db_bind_text(stmt, 4, nick, -1, SQLITE_STATIC);
  int rc = db_step(stmt);
  db_finalize(stmt);
  if (rc == SQLITE_DONE) {
    *out_id = db_last_rowid(db_handle);
    return 0;
  }
  return -1;
}

/*
 * ai_get_question - Retrieve the question text for a pending AI query.
 *
 * Queries ai_pending by row ID and returns the stored question string.
 * Used by the child process to know what to ask opencode.
 *
 * Parameters:
 *   id - The row ID returned by ai_start().
 *
 * Returns: A newly allocated string with the question, or NULL on error.
 *          Caller must free() the result.
 */
char *ai_get_question(long long id) {
  void *stmt;
  const char *sql = "SELECT question FROM ai_pending WHERE id=?";
  if (db_prepare(DB, sql, -1, &stmt, NULL) != SQLITE_OK) return NULL;
  db_bind_int64(stmt, 1, id);
  if (db_step(stmt) != SQLITE_ROW) { db_finalize(stmt); return NULL; }
  const char *q = (const char*)db_column_text(stmt, 0);
  char *r = q ? strdup(q) : NULL;
  db_finalize(stmt);
  return r;
}

/*
 * ai_set_answer - Store the AI response and mark the query as done.
 *
 * Updates the ai_pending row with the given ID, setting the answer text
 * and changing status from 'pending' to 'done'. Called by the child process
 * after opencode produces output (or an error message).
 *
 * Parameters:
 *   id     - The row ID returned by ai_start().
 *   answer - The AI response text to store.
 *
 * Returns: 0 on success, -1 on error.
 */
int ai_set_answer(long long id, const char *answer) {
  const char *sql = "UPDATE ai_pending SET answer=?, status='done' WHERE id=?";
  void *stmt;
  if (db_prepare(DB, sql, -1, &stmt, NULL) != SQLITE_OK) return -1;
  db_bind_text(stmt, 1, answer, -1, SQLITE_STATIC);
  db_bind_int64(stmt, 2, id);
  int rc = db_step(stmt);
  db_finalize(stmt);
  return rc == SQLITE_DONE ? 0 : -1;
}

/*
 * ai_get_answer - Retrieve the completed AI answer and its target.
 *
 * Queries ai_pending for a row with status='done' matching the given ID.
 * Returns the answer text and the target channel/nick concatenated with
 * a \x1f (unit separator) delimiter. The caller splits on this character
 * to extract the answer and delivery target separately.
 *
 * Parameters:
 *   id - The row ID of the completed query.
 *
 * Returns: A newly allocated string "answer\x1ftarget", or NULL if the
 *          query is not yet done or not found. Caller must free() the result.
 */
char *ai_get_answer(long long id) {
  void *stmt;
  const char *sql = "SELECT answer,target FROM ai_pending WHERE id=? AND status='done'";
  if (db_prepare(DB, sql, -1, &stmt, NULL) != SQLITE_OK) return NULL;
  db_bind_int64(stmt, 1, id);
  if (db_step(stmt) != SQLITE_ROW) { db_finalize(stmt); return NULL; }
  const char *a = (const char*)db_column_text(stmt, 0);
  const char *t = (const char*)db_column_text(stmt, 1);
  char *r = NULL;
  if (a && t) { if (asprintf(&r, "%s\037%s", a, t) < 0) r = NULL; }
  db_finalize(stmt);
  return r;
}

/*
 * ai_get_nick - Retrieve the requesting user's nick for a pending AI query.
 *
 * Queries the ai_pending table for the nick associated with the given ID.
 * Used for logging when the AI child process completes.
 *
 * Parameters:
 *   id - The row ID returned by ai_start().
 *
 * Returns: A newly allocated string with the nick, or NULL on error.
 *          Caller must free() the result.
 */
char *ai_get_nick(long long id) {
  void *stmt;
  const char *sql = "SELECT nick FROM ai_pending WHERE id=?";
  if (db_prepare(DB, sql, -1, &stmt, NULL) != SQLITE_OK) return NULL;
  db_bind_int64(stmt, 1, id);
  if (db_step(stmt) != SQLITE_ROW) { db_finalize(stmt); return NULL; }
  const char *n = (const char*)db_column_text(stmt, 0);
  char *r = n ? strdup(n) : NULL;
  db_finalize(stmt);
  return r;
}

/*
 * ai_reset - Delete a completed or stale AI query record from the database.
 *
 * Removes the ai_pending row matching the given ID. Called after the answer
 * has been delivered to IRC, or after a timeout/failure to clean up.
 *
 * Parameters:
 *   id - The row ID of the query to remove.
 *
 * Returns: void.
 */
void ai_reset(long long id) {
  const char *sql = "DELETE FROM ai_pending WHERE id=?";
  void *stmt;
  if (db_prepare(DB, sql, -1, &stmt, NULL) != SQLITE_OK) return;
  db_bind_int64(stmt, 1, id);
  db_step(stmt);
  db_finalize(stmt);
}

/* ---- DB seen/tell operations ---- */

/*
 * seen_update - Record a nick's last-seen timestamp and context.
 *
 * Upserts a row in the seen table: inserts a new record if the nick has
 * never been seen, or updates the existing record with the current UTC
 * timestamp, channel, and action. Uses INSERT ... ON CONFLICT DO UPDATE.
 *
 * Parameters:
 *   nick    - The IRC nick to record.
 *   channel - The channel where the nick was seen (or "" for DMs/QUIT).
 *   action  - A short description of what the nick did (e.g. "spoke",
 *             "joined", "left"). Defaults to "spoke" if NULL.
 *
 * Returns: void.
 */
void seen_update(const char *nick, const char *channel, const char *action) {
  void *stmt;
  const char *sql = "INSERT INTO seen(nick,last_seen,last_channel,last_action)"
    " VALUES(?,datetime('now','localtime'),?,?)"
    " ON CONFLICT(nick) DO UPDATE SET last_seen=datetime('now','localtime'),"
    " last_channel=excluded.last_channel, last_action=excluded.last_action";
  if (db_prepare(DB, sql, -1, &stmt, NULL) != SQLITE_OK) return;
  db_bind_text(stmt, 1, nick, -1, SQLITE_STATIC);
  db_bind_text(stmt, 2, channel ? channel : "", -1, SQLITE_STATIC);
  db_bind_text(stmt, 3, action ? action : "spoke", -1, SQLITE_STATIC);
  db_step(stmt);
  db_finalize(stmt);
}

/*
 * seen_lookup - Retrieve last-seen info for a given nick.
 *
 * Queries the seen table for the most recent timestamp, channel, and
 * action recorded for the given nick. Returns a human-readable string
 * suitable for sending directly to IRC.
 *
 * Parameters:
 *   nick - The IRC nick to look up.
 *
 * Returns: A newly allocated string like "<nick> was last seen <action>
 *          <channel> <timestamp>", or NULL if the nick was never seen.
 *          Caller must free() the result.
 */
char *seen_lookup(const char *nick) {
  void *stmt;
  const char *sql = "SELECT last_seen, last_channel, last_action FROM seen WHERE nick=?";
  if (db_prepare(DB, sql, -1, &stmt, NULL) != SQLITE_OK) return NULL;
  db_bind_text(stmt, 1, nick, -1, SQLITE_STATIC);
  if (db_step(stmt) != SQLITE_ROW) { db_finalize(stmt); return NULL; }
  const char *t = (const char*)db_column_text(stmt, 0);
  const char *c = (const char*)db_column_text(stmt, 1);
  const char *a = (const char*)db_column_text(stmt, 2);
  char *r = NULL;
  if (asprintf(&r, "%s was last seen %s %s %s", nick, a ? a : "in", c ? c : "?", t ? t : "unknown") < 0) r = NULL;
  db_finalize(stmt);
  return r;
}

/*
 * tell_add - Queue a message for later delivery to a nick.
 *
 * Inserts a pending message into the tell table, recording the sender,
 * recipient, and message text. The message will be delivered the next
 * time the recipient speaks (via tell_deliver).
 *
 * Parameters:
 *   from - The IRC nick of the sender.
 *   to   - The IRC nick of the intended recipient.
 *   msg  - The message text to deliver.
 *
 * Returns: void.
 */
void tell_add(const char *from, const char *to, const char *msg) {
  void *stmt;
  const char *sql = "INSERT INTO tell(from_nick,to_nick,message) VALUES(?,?,?)";
  if (db_prepare(DB, sql, -1, &stmt, NULL) != SQLITE_OK) return;
  db_bind_text(stmt, 1, from, -1, SQLITE_STATIC);
  db_bind_text(stmt, 2, to, -1, SQLITE_STATIC);
  db_bind_text(stmt, 3, msg, -1, SQLITE_STATIC);
  db_step(stmt);
  db_finalize(stmt);
}

/*
 * tell_deliver - Deliver all pending tell messages for a nick.
 *
 * Queries the tell table for all messages addressed to the given nick,
 * sends each one to the specified channel via irc_msg in the format
 * "<nick>: message from <sender>: <text>", and deletes each row after
 * delivery. Messages are delivered in insertion order (lowest ID first).
 *
 * Parameters:
 *   s       - The IRC session.
 *   nick    - The nick whose pending messages should be delivered.
 *   channel - The channel to send the delivery messages to.
 *
 * Returns: void.
 */
void tell_deliver(Session *s, const char *nick, const char *channel) {
  void *stmt;
  const char *sql = "SELECT id, from_nick, message FROM tell WHERE to_nick=? ORDER BY id ASC";
  if (db_prepare(DB, sql, -1, &stmt, NULL) != SQLITE_OK) return;
  db_bind_text(stmt, 1, nick, -1, SQLITE_STATIC);
  int delivered = 0;
  while (db_step(stmt) == SQLITE_ROW) {
    if (delivered >= TELL_DELIVER_MAX) break;
    long long id = 0;
    const void *idcol = db_column_text(stmt, 0);
    if (idcol) id = atoll((const char*)idcol);
    const char *from = (const char*)db_column_text(stmt, 1);
    const char *msg = (const char*)db_column_text(stmt, 2);
    char buf[1024];
    snprintf(buf, sizeof buf, "%s: message from %s: %s", nick, from ? from : "?", msg ? msg : "");
    irc_msg(s, channel, buf);
    char dsql[128];
    snprintf(dsql, sizeof dsql, "DELETE FROM tell WHERE id=%lld", id);
    void *dstmt;
    if (db_prepare(DB, dsql, -1, &dstmt, NULL) == SQLITE_OK) { db_step(dstmt); db_finalize(dstmt); }
    delivered++;
  }
  db_finalize(stmt);
}

#endif