#include "auth/auth_store.h"

#include "deita/deita.h"

#include <openssl/crypto.h>

#include <fcntl.h>
#include <limits.h>
#include <pthread.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <time.h>
#include <unistd.h>

struct Auth_Store {
  Deita_Connection *p_connection;
  pthread_mutex_t   mutex;
};

/* ------------------------------------------------------------------ */
/* Migration SQL                                                        */
/* ------------------------------------------------------------------ */

static const char *k_migration_v1 =
    "CREATE TABLE IF NOT EXISTS users ("
    "  id TEXT PRIMARY KEY,"
    "  username TEXT NOT NULL,"
    "  normalized_username TEXT NOT NULL UNIQUE,"
    "  password_hash TEXT NOT NULL,"
    "  role TEXT NOT NULL CHECK(role IN ('admin','member')),"
    "  status TEXT NOT NULL DEFAULT 'active'"
    "    CHECK(status IN ('active','disabled')),"
    "  must_change_password INTEGER NOT NULL DEFAULT 0,"
    "  password_changed_at INTEGER NOT NULL DEFAULT 0,"
    "  created_at INTEGER NOT NULL DEFAULT (strftime('%s','now')),"
    "  updated_at INTEGER NOT NULL DEFAULT (strftime('%s','now'))"
    ");"
    "CREATE TABLE IF NOT EXISTS auth_sessions ("
    "  token_digest TEXT PRIMARY KEY,"
    "  csrf_digest TEXT NOT NULL,"
    "  user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,"
    "  created_at INTEGER NOT NULL DEFAULT (strftime('%s','now')),"
    "  last_seen_at INTEGER NOT NULL DEFAULT (strftime('%s','now')),"
    "  idle_expires_at INTEGER NOT NULL,"
    "  absolute_expires_at INTEGER NOT NULL,"
    "  password_changed_at_snapshot INTEGER NOT NULL DEFAULT 0,"
    "  revoked_at INTEGER"
    ");"
    "CREATE TABLE IF NOT EXISTS guest_identities ("
    "  id TEXT PRIMARY KEY,"
    "  ip_binding_digest TEXT NOT NULL,"
    "  created_at INTEGER NOT NULL DEFAULT (strftime('%s','now')),"
    "  last_seen_at INTEGER NOT NULL DEFAULT (strftime('%s','now')),"
    "  expires_at INTEGER NOT NULL"
    ");"
    "CREATE TABLE IF NOT EXISTS guest_usage ("
    "  guest_id TEXT NOT NULL"
    "    REFERENCES guest_identities(id) ON DELETE CASCADE,"
    "  window_start INTEGER NOT NULL,"
    "  count INTEGER NOT NULL DEFAULT 0,"
    "  PRIMARY KEY (guest_id, window_start)"
    ");"
    "CREATE TABLE IF NOT EXISTS guest_usage_reservations ("
    "  id INTEGER PRIMARY KEY AUTOINCREMENT,"
    "  guest_id TEXT NOT NULL"
    "    REFERENCES guest_identities(id) ON DELETE CASCADE,"
    "  reserved_at INTEGER NOT NULL DEFAULT (strftime('%s','now')),"
    "  expires_at INTEGER NOT NULL"
    ");"
    "CREATE TABLE IF NOT EXISTS admin_audit_log ("
    "  id INTEGER PRIMARY KEY AUTOINCREMENT,"
    "  actor_user_id TEXT,"
    "  action TEXT NOT NULL,"
    "  target_user_id TEXT,"
    "  detail TEXT,"
    "  created_at INTEGER NOT NULL DEFAULT (strftime('%s','now'))"
    ");"
    "CREATE INDEX IF NOT EXISTS idx_users_normalized"
    "  ON users(normalized_username);"
    "CREATE INDEX IF NOT EXISTS idx_sessions_user"
    "  ON auth_sessions(user_id);"
    "CREATE INDEX IF NOT EXISTS idx_sessions_expiry"
    "  ON auth_sessions(absolute_expires_at) WHERE revoked_at IS NULL;"
    "CREATE INDEX IF NOT EXISTS idx_guest_expiry"
    "  ON guest_identities(expires_at);"
    "CREATE INDEX IF NOT EXISTS idx_audit_created"
    "  ON admin_audit_log(created_at DESC);";

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

static void auth__copy_text_fixed(char *dest, size_t size, const char *text)
{
  const char *src = text ? text : "";
  size_t      n   = strlen(src);
  if (n >= size)
    n = size - 1;
  memcpy(dest, src, n);
  dest[n] = '\0';
}

static boolean auth__generate_uuid(char output[37])
{
  uint8   bytes[16];
  int     fd     = open("/dev/urandom", O_RDONLY);
  size_t  offset = 0;
  ssize_t amount;

  if (fd < 0)
    return FALSE;
  while (offset < sizeof(bytes))
  {
    amount = read(fd, bytes + offset, sizeof(bytes) - offset);
    if (amount <= 0)
    {
      close(fd);
      return FALSE;
    }
    offset += (size_t)amount;
  }
  close(fd);

  bytes[6] = (uint8)((bytes[6] & 0x0f) | 0x40);
  bytes[8] = (uint8)((bytes[8] & 0x3f) | 0x80);
  snprintf(
      output, 37,
      "%02x%02x%02x%02x-%02x%02x-%02x%02x-%02x%02x-%02x%02x%02x%02x%02x%02x",
      bytes[0],  bytes[1],  bytes[2],  bytes[3],
      bytes[4],  bytes[5],  bytes[6],  bytes[7],
      bytes[8],  bytes[9],  bytes[10], bytes[11],
      bytes[12], bytes[13], bytes[14], bytes[15]);
  return TRUE;
}

static Auth_Store_Result auth__rollback(Auth_Store *p_store,
                                        Auth_Store_Result result)
{
  Deita_Query_Execute_Update(p_store->p_connection, "ROLLBACK");
  return result;
}

/* Forward declaration — implementation is in the guest quota section. */
static void auth__i64_str(char *buf, size_t size, int64 value);

static boolean auth__insert_audit_log_locked(
    Auth_Store *p_store,
    const char *actor_user_id,
    const char *action,
    const char *target_user_id,
    const char *detail)
{
  /* actor_user_id and detail may be NULL — represented as empty string */
  const char *actor  = actor_user_id  ? actor_user_id  : "";
  const char *tgt    = target_user_id ? target_user_id : "";
  const char *det    = detail         ? detail         : "";
  const char *params[] = {actor, action, tgt, det};
  if (Deita_Query_Execute_Update_Prepared(
          p_store->p_connection,
          "INSERT INTO admin_audit_log"
          "  (actor_user_id, action, target_user_id, detail)"
          "  VALUES (?, ?, ?, ?)",
          4, params) < 0)
    return FALSE;
  /* Trim to the most recent 10 000 entries. */
  Deita_Query_Execute_Update(
      p_store->p_connection,
      "DELETE FROM admin_audit_log"
      "  WHERE id <= (SELECT MAX(id) FROM admin_audit_log) - 10000");
  return TRUE;
}

static boolean auth__digest_is_valid(const char *digest)
{
  if (!digest)
    return FALSE;
  for (size_t i = 0; i < AUTH_CRYPTO_TOKEN_DIGEST_SIZE - 1; i++)
  {
    uint8 c = (uint8)digest[i];
    if (c == '\0' ||
        !((c >= '0' && c <= '9') ||
          (c >= 'a' && c <= 'f') ||
          (c >= 'A' && c <= 'F')))
      return FALSE;
  }
  return digest[AUTH_CRYPTO_TOKEN_DIGEST_SIZE - 1] == '\0';
}

static boolean auth__session_arguments_are_valid(
    const char *token_digest,
    const char *csrf_digest,
    int64       idle_ttl_secs,
    int64       absolute_ttl_secs,
    int64       current_unix)
{
  if (!auth__digest_is_valid(token_digest) ||
      !auth__digest_is_valid(csrf_digest) ||
      current_unix < 0 || idle_ttl_secs <= 0 || absolute_ttl_secs <= 0 ||
      idle_ttl_secs > absolute_ttl_secs)
    return FALSE;
  if (current_unix > (int64)LLONG_MAX - idle_ttl_secs ||
      current_unix > (int64)LLONG_MAX - absolute_ttl_secs)
    return FALSE;
  return TRUE;
}

static void auth__fill_session_record(
    Auth_Session_Record *p_record,
    const char          *user_id,
    int64                idle_ttl_secs,
    int64                absolute_ttl_secs,
    int64                current_unix,
    int64                password_changed_at)
{
  memset(p_record, 0, sizeof(*p_record));
  auth__copy_text_fixed(p_record->user_id, sizeof(p_record->user_id), user_id);
  p_record->created_at = current_unix;
  p_record->last_seen_at = current_unix;
  p_record->idle_expires_at = current_unix + idle_ttl_secs;
  p_record->absolute_expires_at = current_unix + absolute_ttl_secs;
  p_record->password_changed_at_snapshot = password_changed_at;
}

static Auth_Store_Result auth__insert_session_locked(
    Auth_Store          *p_store,
    const char          *user_id,
    const char          *token_digest,
    const char          *csrf_digest,
    int64                idle_ttl_secs,
    int64                absolute_ttl_secs,
    int64                current_unix,
    int64                password_changed_at)
{
  char created_str[32], idle_exp_str[32], abs_exp_str[32], snap_str[32];
  snprintf(created_str, sizeof(created_str), "%lld", (long long)current_unix);
  snprintf(idle_exp_str, sizeof(idle_exp_str), "%lld",
           (long long)(current_unix + idle_ttl_secs));
  snprintf(abs_exp_str, sizeof(abs_exp_str), "%lld",
           (long long)(current_unix + absolute_ttl_secs));
  snprintf(snap_str, sizeof(snap_str), "%lld",
           (long long)password_changed_at);

  const char *params[] = {
    token_digest, csrf_digest, user_id,
    created_str, created_str, idle_exp_str, abs_exp_str, snap_str
  };
  int32 result = Deita_Query_Execute_Update_Prepared(
      p_store->p_connection,
      "INSERT INTO auth_sessions"
      "  (token_digest, csrf_digest, user_id,"
      "   created_at, last_seen_at, idle_expires_at,"
      "   absolute_expires_at, password_changed_at_snapshot)"
      "  VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
      8, params);
  return result < 0 ? AUTH_STORE_CONFLICT : AUTH_STORE_OK;
}

/* ------------------------------------------------------------------ */
/* Migration system                                                     */
/* ------------------------------------------------------------------ */

static boolean auth__apply_migration(Auth_Store *p_store,
                                     int32       version,
                                     const char *sql)
{
  char version_str[32];
  snprintf(version_str, sizeof(version_str), "%d", (int)version);

  if (Deita_Query_Execute_Update(
          p_store->p_connection, "BEGIN EXCLUSIVE") < 0)
    return FALSE;

  /* Check if already applied. */
  Dowa_Arena       *p_arena = Dowa_Arena_Create(1024);
  if (!p_arena)
  {
    auth__rollback(p_store, AUTH_STORE_ERROR);
    return FALSE;
  }
  const char       *check_params[] = {version_str};
  Deita_Result_Set *p_result = Deita_Query_Execute_Prepared(
      p_store->p_connection,
      "SELECT version FROM auth_schema_migrations WHERE version = ?",
      1, check_params, p_arena);
  boolean already = p_result && Deita_Result_Set_Next(p_result);
  if (p_result)
    Deita_Result_Set_Free(p_result);
  Dowa_Arena_Free(p_arena);

  if (already)
  {
    if (Deita_Query_Execute_Update(p_store->p_connection, "COMMIT") < 0)
    {
      auth__rollback(p_store, AUTH_STORE_ERROR);
      return FALSE;
    }
    return TRUE;
  }

  /* Apply the migration. */
  if (Deita_Query_Execute_Update(p_store->p_connection, sql) < 0)
  {
    auth__rollback(p_store, AUTH_STORE_ERROR);
    return FALSE;
  }

  const char *ins_params[] = {version_str};
  if (Deita_Query_Execute_Update_Prepared(
          p_store->p_connection,
          "INSERT INTO auth_schema_migrations (version) VALUES (?)",
          1, ins_params) < 0)
  {
    auth__rollback(p_store, AUTH_STORE_ERROR);
    return FALSE;
  }

  if (Deita_Query_Execute_Update(p_store->p_connection, "COMMIT") < 0)
  {
    auth__rollback(p_store, AUTH_STORE_ERROR);
    return FALSE;
  }
  return TRUE;
}

/*
 * Migration v2: extend guest quota tables.
 * - Backfills turns_used from the legacy count column before any new schema
 *   is applied, so existing usage is preserved (req 1).
 * - Adds turns_used, output_tokens_used, output_tokens_reserved to guest_usage.
 * - Replaces guest_usage_reservations with a richer schema keyed by request_id.
 * The old reservations table is ephemeral (in-flight requests only), so
 * dropping and recreating it is safe; legacy reservations had no token-amount
 * column so output_tokens_reserved stays 0 after the replace.
 */
static const char *k_migration_v2 =
    "ALTER TABLE guest_usage"
    "  ADD COLUMN turns_used INTEGER NOT NULL DEFAULT 0;"
    "ALTER TABLE guest_usage"
    "  ADD COLUMN output_tokens_used INTEGER NOT NULL DEFAULT 0;"
    "ALTER TABLE guest_usage"
    "  ADD COLUMN output_tokens_reserved INTEGER NOT NULL DEFAULT 0;"
    /* Backfill turns_used from the legacy request-count column. */
    "UPDATE guest_usage SET turns_used = count WHERE count > 0;"
    "DROP TABLE IF EXISTS guest_usage_reservations;"
    "CREATE TABLE IF NOT EXISTS guest_usage_reservations ("
    "  request_id TEXT PRIMARY KEY,"
    "  guest_id TEXT NOT NULL"
    "    REFERENCES guest_identities(id) ON DELETE CASCADE,"
    "  window_start INTEGER NOT NULL,"
    "  output_tokens_reserved INTEGER NOT NULL DEFAULT 0,"
    "  reserved_at INTEGER NOT NULL DEFAULT (strftime('%s','now')),"
    "  expires_at INTEGER NOT NULL"
    ");"
    "CREATE INDEX IF NOT EXISTS idx_reservations_guest"
    "  ON guest_usage_reservations(guest_id);"
    "CREATE INDEX IF NOT EXISTS idx_reservations_expiry"
    "  ON guest_usage_reservations(expires_at);"
    "CREATE INDEX IF NOT EXISTS idx_guest_usage_guest"
    "  ON guest_usage(guest_id);";

static boolean auth__run_migrations(Auth_Store *p_store)
{
  if (Deita_Query_Execute_Update(
          p_store->p_connection,
          "CREATE TABLE IF NOT EXISTS auth_schema_migrations ("
          "  version INTEGER PRIMARY KEY,"
          "  applied_at INTEGER NOT NULL DEFAULT (strftime('%s','now'))"
          ")") < 0)
    return FALSE;

  if (!auth__apply_migration(p_store, 1, k_migration_v1))
    return FALSE;
  return auth__apply_migration(p_store, 2, k_migration_v2);
}

/* ------------------------------------------------------------------ */
/* Store lifecycle                                                      */
/* ------------------------------------------------------------------ */

/*
 * Reap expired reservations with the mutex already held.
 * Run atomically in a nested SAVEPOINT to avoid interfering with any outer
 * transaction (callers sometimes hold BEGIN IMMEDIATE).
 */
static void auth__reap_expired_reservations_locked(
    Auth_Store *p_store,
    int64       current_unix)
{
  char now_str[32];
  auth__i64_str(now_str, sizeof(now_str), current_unix);

  /* Use a savepoint so we can run inside or outside a transaction. */
  if (Deita_Query_Execute_Update(
          p_store->p_connection,
          "SAVEPOINT reap_expired") < 0)
    return;

  const char *upd_params[] = {now_str, now_str};
  if (Deita_Query_Execute_Update_Prepared(
          p_store->p_connection,
          "UPDATE guest_usage"
          "  SET output_tokens_reserved = MAX(0, output_tokens_reserved - ("
          "    SELECT COALESCE(SUM(r.output_tokens_reserved), 0)"
          "    FROM guest_usage_reservations r"
          "    WHERE r.guest_id = guest_usage.guest_id"
          "      AND r.window_start = guest_usage.window_start"
          "      AND r.expires_at < ?"
          "  ))"
          "  WHERE output_tokens_reserved > 0"
          "    AND EXISTS ("
          "      SELECT 1 FROM guest_usage_reservations r2"
          "      WHERE r2.guest_id = guest_usage.guest_id"
          "        AND r2.expires_at < ?)",
          2, upd_params) < 0)
  {
    Deita_Query_Execute_Update(p_store->p_connection,
                               "ROLLBACK TO reap_expired");
    Deita_Query_Execute_Update(p_store->p_connection, "RELEASE reap_expired");
    return;
  }

  const char *del_params[] = {now_str};
  if (Deita_Query_Execute_Update_Prepared(
          p_store->p_connection,
          "DELETE FROM guest_usage_reservations WHERE expires_at < ?",
          1, del_params) < 0)
  {
    Deita_Query_Execute_Update(p_store->p_connection,
                               "ROLLBACK TO reap_expired");
    Deita_Query_Execute_Update(p_store->p_connection, "RELEASE reap_expired");
    return;
  }

  Deita_Query_Execute_Update(p_store->p_connection, "RELEASE reap_expired");
}

Auth_Store *Auth_Store_Create(const char *database_path)
{
  if (!database_path)
    return NULL;

  Auth_Store *p_store = calloc(1, sizeof(*p_store));
  if (!p_store)
    return NULL;

  p_store->p_connection = Deita_Connection_Create(
      DEITA_DATABASE_TYPE_SQLITE3, database_path);
  if (!p_store->p_connection ||
      !Deita_Connection_Is_Open(p_store->p_connection))
  {
    if (p_store->p_connection)
      Deita_Connection_Close(p_store->p_connection);
    free(p_store);
    return NULL;
  }

  if (pthread_mutex_init(&p_store->mutex, NULL) != 0)
  {
    Deita_Connection_Close(p_store->p_connection);
    free(p_store);
    return NULL;
  }

  /* Connection-level settings — must be re-applied on every open. */
  if (Deita_Query_Execute_Update(p_store->p_connection,
          "PRAGMA foreign_keys = ON;"
          "PRAGMA journal_mode = WAL;") < 0)
  {
    Auth_Store_Destroy(p_store);
    return NULL;
  }

  if (!auth__run_migrations(p_store))
  {
    Auth_Store_Destroy(p_store);
    return NULL;
  }

  /* Reap any expired reservations left over from a previous run. */
  pthread_mutex_lock(&p_store->mutex);
  auth__reap_expired_reservations_locked(p_store, (int64)time(NULL));
  pthread_mutex_unlock(&p_store->mutex);

  return p_store;
}

void Auth_Store_Destroy(Auth_Store *p_store)
{
  if (!p_store)
    return;
  if (p_store->p_connection)
    Deita_Connection_Close(p_store->p_connection);
  pthread_mutex_destroy(&p_store->mutex);
  free(p_store);
}

/* ------------------------------------------------------------------ */
/* Username utilities                                                   */
/* ------------------------------------------------------------------ */

boolean Auth_Store_Normalize_Username(
    const char *username,
    char       *normalized,
    size_t      capacity)
{
  if (!username || !normalized || capacity == 0)
    return FALSE;

  size_t len   = strlen(username);
  size_t start = 0;
  size_t end   = len;

  while (start < len && username[start] == ' ')
    start++;
  while (end > start && username[end - 1] == ' ')
    end--;

  size_t norm_len = end - start;
  if (norm_len < AUTH_STORE_USERNAME_MIN ||
      norm_len > AUTH_STORE_USERNAME_MAX)
    return FALSE;
  if (capacity <= norm_len)
    return FALSE;

  for (size_t i = 0; i < norm_len; i++)
  {
    uint8 c = (uint8)username[start + i];
    if (c >= 'A' && c <= 'Z')
      c = (uint8)(c - 'A' + 'a');
    else if ((c >= 'a' && c <= 'z') ||
             (c >= '0' && c <= '9') ||
             c == '_' || c == '-' || c == '.')
      ; /* valid as-is */
    else
      return FALSE;
    normalized[i] = (char)c;
  }
  normalized[norm_len] = '\0';
  return TRUE;
}

boolean Auth_Store_Validate_Username(const char *normalized_username)
{
  if (!normalized_username)
    return FALSE;
  size_t i = 0;
  while (normalized_username[i] != '\0')
  {
    if (i >= AUTH_STORE_USERNAME_MAX)
      return FALSE;
    uint8 c = (uint8)normalized_username[i];
    if (!((c >= 'a' && c <= 'z') ||
          (c >= '0' && c <= '9') ||
          c == '_' || c == '-' || c == '.'))
      return FALSE;
    i++;
  }
  return i >= AUTH_STORE_USERNAME_MIN;
}

/* ------------------------------------------------------------------ */
/* Internal: populate Auth_User_Record from an open result set         */
/* ------------------------------------------------------------------ */

/*
 * Columns expected (0-based):
 *   0 id, 1 username, 2 normalized_username, 3 role, 4 status,
 *   5 must_change_password, 6 password_changed_at, 7 created_at, 8 updated_at
 */
static void auth__read_user_record(Auth_User_Record *r,
                                   Deita_Result_Set *p)
{
  auth__copy_text_fixed(r->id,                 sizeof(r->id),
                        Deita_Result_Set_Get_Text(p, 0));
  auth__copy_text_fixed(r->username,           sizeof(r->username),
                        Deita_Result_Set_Get_Text(p, 1));
  auth__copy_text_fixed(r->normalized_username,sizeof(r->normalized_username),
                        Deita_Result_Set_Get_Text(p, 2));
  auth__copy_text_fixed(r->role,               sizeof(r->role),
                        Deita_Result_Set_Get_Text(p, 3));
  auth__copy_text_fixed(r->status,             sizeof(r->status),
                        Deita_Result_Set_Get_Text(p, 4));
  r->must_change_password = Deita_Result_Set_Get_Integer(p, 5) ? TRUE : FALSE;
  r->password_changed_at  = Deita_Result_Set_Get_Integer(p, 6);
  r->created_at           = Deita_Result_Set_Get_Integer(p, 7);
  r->updated_at           = Deita_Result_Set_Get_Integer(p, 8);
}

/* ------------------------------------------------------------------ */
/* User management                                                      */
/* ------------------------------------------------------------------ */

Auth_Store_Result Auth_Store_Create_User(
    Auth_Store *p_store,
    const char *username,
    const char *encoded_hash,
    const char *role,
    boolean     must_change_password,
    char        output_id[37])
{
  if (!p_store || !username || !encoded_hash || !role || !output_id)
    return AUTH_STORE_INVALID_ARG;
  if (encoded_hash[0] == '\0')
    return AUTH_STORE_INVALID_ARG;
  if (strcmp(role, "admin") != 0 && strcmp(role, "member") != 0)
    return AUTH_STORE_INVALID_ARG;

  char normalized[AUTH_STORE_USERNAME_MAX + 1];
  if (!Auth_Store_Normalize_Username(
          username, normalized, sizeof(normalized)))
    return AUTH_STORE_INVALID_ARG;

  if (!auth__generate_uuid(output_id))
    return AUTH_STORE_ERROR;

  const char *mcp_str = must_change_password ? "1" : "0";
  const char *params[] = {
    output_id, username, normalized, encoded_hash, role, mcp_str
  };

  pthread_mutex_lock(&p_store->mutex);
  int32 result = Deita_Query_Execute_Update_Prepared(
      p_store->p_connection,
      "INSERT INTO users"
      "  (id, username, normalized_username, password_hash, role,"
      "   must_change_password)"
      "  VALUES (?, ?, ?, ?, ?, ?)",
      6, params);
  pthread_mutex_unlock(&p_store->mutex);

  if (result < 0)
    return AUTH_STORE_CONFLICT; /* most likely UNIQUE constraint on norm_name */
  return AUTH_STORE_OK;
}

Auth_Store_Result Auth_Store_Create_User_Audited(
    Auth_Store *p_store,
    const char *username,
    const char *encoded_hash,
    const char *role,
    boolean     must_change_password,
    const char *actor_user_id,
    char        output_id[37])
{
  if (!p_store || !username || !encoded_hash || !role || !output_id)
    return AUTH_STORE_INVALID_ARG;
  if (encoded_hash[0] == '\0')
    return AUTH_STORE_INVALID_ARG;
  if (strcmp(role, "admin") != 0 && strcmp(role, "member") != 0)
    return AUTH_STORE_INVALID_ARG;

  char normalized[AUTH_STORE_USERNAME_MAX + 1];
  if (!Auth_Store_Normalize_Username(
          username, normalized, sizeof(normalized)))
    return AUTH_STORE_INVALID_ARG;
  if (!auth__generate_uuid(output_id))
    return AUTH_STORE_ERROR;

  const char *mcp_str = must_change_password ? "1" : "0";
  const char *params[] = {
    output_id, username, normalized, encoded_hash, role, mcp_str
  };

  pthread_mutex_lock(&p_store->mutex);
  if (Deita_Query_Execute_Update(
          p_store->p_connection, "BEGIN IMMEDIATE") < 0)
  {
    pthread_mutex_unlock(&p_store->mutex);
    return AUTH_STORE_ERROR;
  }

  int32 result = Deita_Query_Execute_Update_Prepared(
      p_store->p_connection,
      "INSERT INTO users"
      "  (id, username, normalized_username, password_hash, role,"
      "   must_change_password)"
      "  VALUES (?, ?, ?, ?, ?, ?)",
      6, params);
  if (result < 0)
  {
    auth__rollback(p_store, AUTH_STORE_ERROR);
    pthread_mutex_unlock(&p_store->mutex);
    return AUTH_STORE_CONFLICT;
  }

  if (!auth__insert_audit_log_locked(
          p_store, actor_user_id, "admin_user_created", output_id, role))
  {
    auth__rollback(p_store, AUTH_STORE_ERROR);
    pthread_mutex_unlock(&p_store->mutex);
    return AUTH_STORE_ERROR;
  }

  if (Deita_Query_Execute_Update(p_store->p_connection, "COMMIT") < 0)
  {
    auth__rollback(p_store, AUTH_STORE_ERROR);
    pthread_mutex_unlock(&p_store->mutex);
    return AUTH_STORE_ERROR;
  }

  pthread_mutex_unlock(&p_store->mutex);
  return AUTH_STORE_OK;
}

Auth_Store_Result Auth_Store_Bootstrap_Admin(
    Auth_Store               *p_store,
    const char               *username,
    const char               *encoded_hash,
    Auth_Store_Bootstrap_Result *p_bootstrap_result,
    char                      output_id[37])
{
  if (!p_store || !username || !encoded_hash || !p_bootstrap_result)
    return AUTH_STORE_INVALID_ARG;
  if (encoded_hash[0] == '\0')
    return AUTH_STORE_INVALID_ARG;

  char normalized[AUTH_STORE_USERNAME_MAX + 1];
  if (!Auth_Store_Normalize_Username(
          username, normalized, sizeof(normalized)))
    return AUTH_STORE_INVALID_ARG;

  char id_buf[37];
  if (!auth__generate_uuid(id_buf))
    return AUTH_STORE_ERROR;

  pthread_mutex_lock(&p_store->mutex);

  if (Deita_Query_Execute_Update(
          p_store->p_connection, "BEGIN IMMEDIATE") < 0)
  {
    pthread_mutex_unlock(&p_store->mutex);
    return AUTH_STORE_ERROR;
  }

  /* Check for any existing admin (active or disabled). */
  Dowa_Arena       *p_arena = Dowa_Arena_Create(2048);
  if (!p_arena)
  {
    auth__rollback(p_store, AUTH_STORE_ERROR);
    pthread_mutex_unlock(&p_store->mutex);
    return AUTH_STORE_ERROR;
  }

  Deita_Result_Set *p_result = Deita_Query_Execute(
      p_store->p_connection,
      "SELECT id FROM users WHERE role = 'admin' LIMIT 1",
      p_arena);
  boolean admin_exists = p_result && Deita_Result_Set_Next(p_result);
  char existing_id[37] = {0};
  if (admin_exists && p_result)
    auth__copy_text_fixed(existing_id, sizeof(existing_id),
                          Deita_Result_Set_Get_Text(p_result, 0));
  if (p_result)
    Deita_Result_Set_Free(p_result);
  Dowa_Arena_Free(p_arena);

  if (admin_exists)
  {
    if (Deita_Query_Execute_Update(p_store->p_connection, "COMMIT") < 0)
    {
      auth__rollback(p_store, AUTH_STORE_ERROR);
      pthread_mutex_unlock(&p_store->mutex);
      return AUTH_STORE_ERROR;
    }
    pthread_mutex_unlock(&p_store->mutex);
    *p_bootstrap_result = AUTH_STORE_BOOTSTRAP_ALREADY_PRESENT;
    if (output_id)
      auth__copy_text_fixed(output_id, 37, existing_id);
    return AUTH_STORE_OK;
  }

  const char *params[] = {
    id_buf, username, normalized, encoded_hash, "admin"
  };
  int32 ins = Deita_Query_Execute_Update_Prepared(
      p_store->p_connection,
      "INSERT INTO users"
      "  (id, username, normalized_username, password_hash, role)"
      "  VALUES (?, ?, ?, ?, ?)",
      5, params);
  if (ins < 0)
  {
    auth__rollback(p_store, AUTH_STORE_ERROR);
    pthread_mutex_unlock(&p_store->mutex);
    return AUTH_STORE_ERROR;
  }

  if (!auth__insert_audit_log_locked(
          p_store, NULL, "bootstrap_admin_created", id_buf, NULL))
  {
    auth__rollback(p_store, AUTH_STORE_ERROR);
    pthread_mutex_unlock(&p_store->mutex);
    return AUTH_STORE_ERROR;
  }

  if (Deita_Query_Execute_Update(p_store->p_connection, "COMMIT") < 0)
  {
    auth__rollback(p_store, AUTH_STORE_ERROR);
    pthread_mutex_unlock(&p_store->mutex);
    return AUTH_STORE_ERROR;
  }

  pthread_mutex_unlock(&p_store->mutex);
  *p_bootstrap_result = AUTH_STORE_BOOTSTRAP_CREATED;
  if (output_id)
    auth__copy_text_fixed(output_id, 37, id_buf);
  return AUTH_STORE_OK;
}

Auth_Store_Result Auth_Store_Find_User_By_Username(
    Auth_Store            *p_store,
    const char            *username,
    Auth_User_Auth_Record *p_record)
{
  if (!p_store || !username || !p_record)
    return AUTH_STORE_INVALID_ARG;

  char normalized[AUTH_STORE_USERNAME_MAX + 1];
  if (!Auth_Store_Normalize_Username(
          username, normalized, sizeof(normalized)))
    return AUTH_STORE_NOT_FOUND;

  memset(p_record, 0, sizeof(*p_record));

  Dowa_Arena       *p_arena = Dowa_Arena_Create(2048);
  if (!p_arena)
    return AUTH_STORE_ERROR;

  const char       *params[] = {normalized};
  pthread_mutex_lock(&p_store->mutex);
  Deita_Result_Set *p_result = Deita_Query_Execute_Prepared(
      p_store->p_connection,
      "SELECT id, username, normalized_username, role, status,"
      "       must_change_password, password_changed_at,"
      "       created_at, updated_at, password_hash"
      "  FROM users WHERE normalized_username = ?",
      1, params, p_arena);
  if (!p_result || !Deita_Result_Set_Next(p_result))
  {
    if (p_result)
      Deita_Result_Set_Free(p_result);
    pthread_mutex_unlock(&p_store->mutex);
    Dowa_Arena_Free(p_arena);
    return AUTH_STORE_NOT_FOUND;
  }
  auth__read_user_record(&p_record->user, p_result);
  auth__copy_text_fixed(p_record->password_hash,
                        sizeof(p_record->password_hash),
                        Deita_Result_Set_Get_Text(p_result, 9));
  Deita_Result_Set_Free(p_result);
  pthread_mutex_unlock(&p_store->mutex);
  Dowa_Arena_Free(p_arena);
  return AUTH_STORE_OK;
}

Auth_Store_Result Auth_Store_Get_User(
    Auth_Store       *p_store,
    const char       *user_id,
    Auth_User_Record *p_record)
{
  if (!p_store || !user_id || !p_record)
    return AUTH_STORE_INVALID_ARG;

  memset(p_record, 0, sizeof(*p_record));

  Dowa_Arena       *p_arena = Dowa_Arena_Create(2048);
  if (!p_arena)
    return AUTH_STORE_ERROR;

  const char       *params[] = {user_id};
  pthread_mutex_lock(&p_store->mutex);
  Deita_Result_Set *p_result = Deita_Query_Execute_Prepared(
      p_store->p_connection,
      "SELECT id, username, normalized_username, role, status,"
      "       must_change_password, password_changed_at,"
      "       created_at, updated_at"
      "  FROM users WHERE id = ?",
      1, params, p_arena);
  if (!p_result || !Deita_Result_Set_Next(p_result))
  {
    if (p_result)
      Deita_Result_Set_Free(p_result);
    pthread_mutex_unlock(&p_store->mutex);
    Dowa_Arena_Free(p_arena);
    return AUTH_STORE_NOT_FOUND;
  }
  auth__read_user_record(p_record, p_result);
  Deita_Result_Set_Free(p_result);
  pthread_mutex_unlock(&p_store->mutex);
  Dowa_Arena_Free(p_arena);
  return AUTH_STORE_OK;
}

Auth_Store_Result Auth_Store_List_Users(
    Auth_Store        *p_store,
    Auth_User_Record **pp_records,
    Dowa_Arena        *p_arena)
{
  if (!p_store || !pp_records || !p_arena)
    return AUTH_STORE_INVALID_ARG;

  *pp_records = NULL;

  Dowa_Arena *p_local = Dowa_Arena_Create(2048);
  if (!p_local)
    return AUTH_STORE_ERROR;

  pthread_mutex_lock(&p_store->mutex);
  Deita_Result_Set *p_result = Deita_Query_Execute(
      p_store->p_connection,
      "SELECT id, username, normalized_username, role, status,"
      "       must_change_password, password_changed_at,"
      "       created_at, updated_at"
      "  FROM users ORDER BY created_at",
      p_local);
  if (!p_result)
  {
    pthread_mutex_unlock(&p_store->mutex);
    Dowa_Arena_Free(p_local);
    return AUTH_STORE_ERROR;
  }

  Auth_User_Record *records = NULL;
  while (Deita_Result_Set_Next(p_result))
  {
    Auth_User_Record record;
    memset(&record, 0, sizeof(record));
    auth__read_user_record(&record, p_result);
    Dowa_Array_Push_Arena(records, record, p_arena);
  }
  boolean err = Deita_Result_Set_Has_Error(p_result);
  Deita_Result_Set_Free(p_result);
  pthread_mutex_unlock(&p_store->mutex);
  Dowa_Arena_Free(p_local);

  if (err)
    return AUTH_STORE_ERROR;

  *pp_records = records;
  return AUTH_STORE_OK;
}

static Auth_Store_Result auth__update_user_status(
    Auth_Store *p_store,
    const char *user_id,
    const char *new_status,
    const char *actor_user_id,
    boolean     revoke_sessions)
{
  if (!p_store || !user_id || !new_status)
    return AUTH_STORE_INVALID_ARG;
  if (strcmp(new_status, "active") != 0 &&
      strcmp(new_status, "disabled") != 0)
    return AUTH_STORE_INVALID_ARG;

  pthread_mutex_lock(&p_store->mutex);

  if (Deita_Query_Execute_Update(
          p_store->p_connection, "BEGIN IMMEDIATE") < 0)
  {
    pthread_mutex_unlock(&p_store->mutex);
    return AUTH_STORE_ERROR;
  }

  /* Fetch current role and status. */
  Dowa_Arena       *p_arena = Dowa_Arena_Create(2048);
  if (!p_arena)
  {
    auth__rollback(p_store, AUTH_STORE_ERROR);
    pthread_mutex_unlock(&p_store->mutex);
    return AUTH_STORE_ERROR;
  }
  const char       *sel_params[] = {user_id};
  Deita_Result_Set *p_result = Deita_Query_Execute_Prepared(
      p_store->p_connection,
      "SELECT role, status FROM users WHERE id = ?",
      1, sel_params, p_arena);
  if (!p_result || !Deita_Result_Set_Next(p_result))
  {
    if (p_result)
      Deita_Result_Set_Free(p_result);
    Dowa_Arena_Free(p_arena);
    auth__rollback(p_store, AUTH_STORE_ERROR);
    pthread_mutex_unlock(&p_store->mutex);
    return AUTH_STORE_NOT_FOUND;
  }
  char cur_role[8], cur_status[9];
  auth__copy_text_fixed(cur_role,   sizeof(cur_role),
                        Deita_Result_Set_Get_Text(p_result, 0));
  auth__copy_text_fixed(cur_status, sizeof(cur_status),
                        Deita_Result_Set_Get_Text(p_result, 1));
  Deita_Result_Set_Free(p_result);
  Dowa_Arena_Free(p_arena);

  /* Last-admin protection: prevent disabling the final active admin. */
  if (strcmp(new_status, "disabled") == 0 &&
      strcmp(cur_role, "admin")      == 0 &&
      strcmp(cur_status, "active")   == 0)
  {
    Dowa_Arena *p_count_arena = Dowa_Arena_Create(1024);
    if (!p_count_arena)
    {
      auth__rollback(p_store, AUTH_STORE_ERROR);
      pthread_mutex_unlock(&p_store->mutex);
      return AUTH_STORE_ERROR;
    }
    p_result = Deita_Query_Execute(
        p_store->p_connection,
        "SELECT COUNT(*) FROM users WHERE role = 'admin' AND status = 'active'",
        p_count_arena);
    int64 count = 0;
    if (p_result && Deita_Result_Set_Next(p_result))
      count = Deita_Result_Set_Get_Integer(p_result, 0);
    if (p_result)
      Deita_Result_Set_Free(p_result);
    Dowa_Arena_Free(p_count_arena);

    if (count <= 1)
    {
      auth__rollback(p_store, AUTH_STORE_ERROR);
      pthread_mutex_unlock(&p_store->mutex);
      return AUTH_STORE_LAST_ADMIN;
    }
  }

  const char *upd_params[] = {new_status, user_id};
  int32 upd = Deita_Query_Execute_Update_Prepared(
      p_store->p_connection,
      "UPDATE users SET status = ?, updated_at = strftime('%s','now')"
      "  WHERE id = ?",
      2, upd_params);
  if (upd <= 0)
  {
    auth__rollback(p_store, AUTH_STORE_ERROR);
    pthread_mutex_unlock(&p_store->mutex);
    return upd < 0 ? AUTH_STORE_ERROR : AUTH_STORE_NOT_FOUND;
  }

  if (revoke_sessions)
  {
    const char *rev_params[] = {user_id};
    if (Deita_Query_Execute_Update_Prepared(
            p_store->p_connection,
            "UPDATE auth_sessions SET revoked_at = strftime('%s','now')"
            "  WHERE user_id = ? AND revoked_at IS NULL",
            1, rev_params) < 0)
    {
      auth__rollback(p_store, AUTH_STORE_ERROR);
      pthread_mutex_unlock(&p_store->mutex);
      return AUTH_STORE_ERROR;
    }
  }

  char detail[64];
  snprintf(detail, sizeof(detail), "status->%s", new_status);
  if (!auth__insert_audit_log_locked(
          p_store, actor_user_id, "user_status_updated", user_id, detail))
  {
    auth__rollback(p_store, AUTH_STORE_ERROR);
    pthread_mutex_unlock(&p_store->mutex);
    return AUTH_STORE_ERROR;
  }

  if (Deita_Query_Execute_Update(p_store->p_connection, "COMMIT") < 0)
  {
    auth__rollback(p_store, AUTH_STORE_ERROR);
    pthread_mutex_unlock(&p_store->mutex);
    return AUTH_STORE_ERROR;
  }

  pthread_mutex_unlock(&p_store->mutex);
  return AUTH_STORE_OK;
}

Auth_Store_Result Auth_Store_Update_User_Status(
    Auth_Store *p_store,
    const char *user_id,
    const char *new_status,
    const char *actor_user_id)
{
  return auth__update_user_status(
      p_store, user_id, new_status, actor_user_id, FALSE);
}

Auth_Store_Result Auth_Store_Enable_User(
    Auth_Store *p_store,
    const char *user_id,
    const char *actor_user_id)
{
  return auth__update_user_status(
      p_store, user_id, "active", actor_user_id, FALSE);
}

Auth_Store_Result Auth_Store_Disable_User_And_Revoke_Sessions(
    Auth_Store *p_store,
    const char *user_id,
    const char *actor_user_id)
{
  return auth__update_user_status(
      p_store, user_id, "disabled", actor_user_id, TRUE);
}

static Auth_Store_Result auth__update_user_role(
    Auth_Store *p_store,
    const char *user_id,
    const char *new_role,
    const char *actor_user_id,
    boolean     revoke_sessions)
{
  if (!p_store || !user_id || !new_role)
    return AUTH_STORE_INVALID_ARG;
  if (strcmp(new_role, "admin") != 0 && strcmp(new_role, "member") != 0)
    return AUTH_STORE_INVALID_ARG;

  pthread_mutex_lock(&p_store->mutex);

  if (Deita_Query_Execute_Update(
          p_store->p_connection, "BEGIN IMMEDIATE") < 0)
  {
    pthread_mutex_unlock(&p_store->mutex);
    return AUTH_STORE_ERROR;
  }

  Dowa_Arena       *p_arena = Dowa_Arena_Create(2048);
  if (!p_arena)
  {
    auth__rollback(p_store, AUTH_STORE_ERROR);
    pthread_mutex_unlock(&p_store->mutex);
    return AUTH_STORE_ERROR;
  }
  const char       *sel_params[] = {user_id};
  Deita_Result_Set *p_result = Deita_Query_Execute_Prepared(
      p_store->p_connection,
      "SELECT role, status FROM users WHERE id = ?",
      1, sel_params, p_arena);
  if (!p_result || !Deita_Result_Set_Next(p_result))
  {
    if (p_result)
      Deita_Result_Set_Free(p_result);
    Dowa_Arena_Free(p_arena);
    auth__rollback(p_store, AUTH_STORE_ERROR);
    pthread_mutex_unlock(&p_store->mutex);
    return AUTH_STORE_NOT_FOUND;
  }
  char cur_role[8], cur_status[9];
  auth__copy_text_fixed(cur_role,   sizeof(cur_role),
                        Deita_Result_Set_Get_Text(p_result, 0));
  auth__copy_text_fixed(cur_status, sizeof(cur_status),
                        Deita_Result_Set_Get_Text(p_result, 1));
  Deita_Result_Set_Free(p_result);
  Dowa_Arena_Free(p_arena);

  /* Last-admin protection: prevent demoting the final active admin. */
  if (strcmp(new_role,    "member") == 0 &&
      strcmp(cur_role,    "admin")  == 0 &&
      strcmp(cur_status,  "active") == 0)
  {
    Dowa_Arena *p_count_arena = Dowa_Arena_Create(1024);
    if (!p_count_arena)
    {
      auth__rollback(p_store, AUTH_STORE_ERROR);
      pthread_mutex_unlock(&p_store->mutex);
      return AUTH_STORE_ERROR;
    }
    p_result = Deita_Query_Execute(
        p_store->p_connection,
        "SELECT COUNT(*) FROM users WHERE role = 'admin' AND status = 'active'",
        p_count_arena);
    int64 count = 0;
    if (p_result && Deita_Result_Set_Next(p_result))
      count = Deita_Result_Set_Get_Integer(p_result, 0);
    if (p_result)
      Deita_Result_Set_Free(p_result);
    Dowa_Arena_Free(p_count_arena);

    if (count <= 1)
    {
      auth__rollback(p_store, AUTH_STORE_ERROR);
      pthread_mutex_unlock(&p_store->mutex);
      return AUTH_STORE_LAST_ADMIN;
    }
  }

  const char *upd_params[] = {new_role, user_id};
  int32 upd = Deita_Query_Execute_Update_Prepared(
      p_store->p_connection,
      "UPDATE users SET role = ?, updated_at = strftime('%s','now')"
      "  WHERE id = ?",
      2, upd_params);
  if (upd <= 0)
  {
    auth__rollback(p_store, AUTH_STORE_ERROR);
    pthread_mutex_unlock(&p_store->mutex);
    return upd < 0 ? AUTH_STORE_ERROR : AUTH_STORE_NOT_FOUND;
  }

  if (revoke_sessions)
  {
    const char *rev_params[] = {user_id};
    if (Deita_Query_Execute_Update_Prepared(
            p_store->p_connection,
            "UPDATE auth_sessions SET revoked_at = strftime('%s','now')"
            "  WHERE user_id = ? AND revoked_at IS NULL",
            1, rev_params) < 0)
    {
      auth__rollback(p_store, AUTH_STORE_ERROR);
      pthread_mutex_unlock(&p_store->mutex);
      return AUTH_STORE_ERROR;
    }
  }

  char detail[64];
  snprintf(detail, sizeof(detail), "role->%s", new_role);
  if (!auth__insert_audit_log_locked(
          p_store, actor_user_id, "user_role_updated", user_id, detail))
  {
    auth__rollback(p_store, AUTH_STORE_ERROR);
    pthread_mutex_unlock(&p_store->mutex);
    return AUTH_STORE_ERROR;
  }

  if (Deita_Query_Execute_Update(p_store->p_connection, "COMMIT") < 0)
  {
    auth__rollback(p_store, AUTH_STORE_ERROR);
    pthread_mutex_unlock(&p_store->mutex);
    return AUTH_STORE_ERROR;
  }

  pthread_mutex_unlock(&p_store->mutex);
  return AUTH_STORE_OK;
}

Auth_Store_Result Auth_Store_Update_User_Role(
    Auth_Store *p_store,
    const char *user_id,
    const char *new_role,
    const char *actor_user_id)
{
  return auth__update_user_role(
      p_store, user_id, new_role, actor_user_id, FALSE);
}

Auth_Store_Result Auth_Store_Update_Role_And_Revoke_Sessions(
    Auth_Store *p_store,
    const char *user_id,
    const char *new_role,
    const char *actor_user_id)
{
  return auth__update_user_role(
      p_store, user_id, new_role, actor_user_id, TRUE);
}

Auth_Store_Result Auth_Store_Set_Must_Change_Password(
    Auth_Store *p_store,
    const char *user_id,
    boolean     value,
    const char *actor_user_id)
{
  if (!p_store || !user_id)
    return AUTH_STORE_INVALID_ARG;

  const char *val_str    = value ? "1" : "0";
  const char *params[]   = {val_str, user_id};

  pthread_mutex_lock(&p_store->mutex);

  if (Deita_Query_Execute_Update(
          p_store->p_connection, "BEGIN IMMEDIATE") < 0)
  {
    pthread_mutex_unlock(&p_store->mutex);
    return AUTH_STORE_ERROR;
  }

  int32 upd = Deita_Query_Execute_Update_Prepared(
      p_store->p_connection,
      "UPDATE users"
      "  SET must_change_password = ?, updated_at = strftime('%s','now')"
      "  WHERE id = ?",
      2, params);
  if (upd <= 0)
  {
    auth__rollback(p_store, AUTH_STORE_ERROR);
    pthread_mutex_unlock(&p_store->mutex);
    return upd < 0 ? AUTH_STORE_ERROR : AUTH_STORE_NOT_FOUND;
  }

  if (!auth__insert_audit_log_locked(
          p_store, actor_user_id,
          value ? "must_change_password_set" : "must_change_password_cleared",
          user_id, NULL))
  {
    auth__rollback(p_store, AUTH_STORE_ERROR);
    pthread_mutex_unlock(&p_store->mutex);
    return AUTH_STORE_ERROR;
  }

  if (Deita_Query_Execute_Update(p_store->p_connection, "COMMIT") < 0)
  {
    auth__rollback(p_store, AUTH_STORE_ERROR);
    pthread_mutex_unlock(&p_store->mutex);
    return AUTH_STORE_ERROR;
  }

  pthread_mutex_unlock(&p_store->mutex);
  return AUTH_STORE_OK;
}

Auth_Store_Result Auth_Store_Update_Password(
    Auth_Store *p_store,
    const char *user_id,
    const char *new_encoded_hash,
    boolean     revoke_other_sessions,
    const char *keep_token_digest)
{
  if (!p_store || !user_id || !new_encoded_hash)
    return AUTH_STORE_INVALID_ARG;
  if (new_encoded_hash[0] == '\0')
    return AUTH_STORE_INVALID_ARG;

  pthread_mutex_lock(&p_store->mutex);

  if (Deita_Query_Execute_Update(
          p_store->p_connection, "BEGIN IMMEDIATE") < 0)
  {
    pthread_mutex_unlock(&p_store->mutex);
    return AUTH_STORE_ERROR;
  }

  const char *upd_params[] = {new_encoded_hash, user_id};
  int32 upd = Deita_Query_Execute_Update_Prepared(
      p_store->p_connection,
      "UPDATE users"
      "  SET password_hash = ?,"
      "      password_changed_at = strftime('%s','now'),"
      "      must_change_password = 0,"
      "      updated_at = strftime('%s','now')"
      "  WHERE id = ?",
      2, upd_params);
  if (upd <= 0)
  {
    auth__rollback(p_store, AUTH_STORE_ERROR);
    pthread_mutex_unlock(&p_store->mutex);
    return upd < 0 ? AUTH_STORE_ERROR : AUTH_STORE_NOT_FOUND;
  }

  if (revoke_other_sessions)
  {
    int32 rev;
    if (keep_token_digest && keep_token_digest[0] != '\0')
    {
      const char *rev_params[] = {user_id, keep_token_digest};
      rev = Deita_Query_Execute_Update_Prepared(
          p_store->p_connection,
          "UPDATE auth_sessions"
          "  SET revoked_at = strftime('%s','now')"
          "  WHERE user_id = ? AND token_digest != ? AND revoked_at IS NULL",
          2, rev_params);
    }
    else
    {
      const char *rev_params[] = {user_id};
      rev = Deita_Query_Execute_Update_Prepared(
          p_store->p_connection,
          "UPDATE auth_sessions"
          "  SET revoked_at = strftime('%s','now')"
          "  WHERE user_id = ? AND revoked_at IS NULL",
          1, rev_params);
    }
    if (rev < 0)
    {
      auth__rollback(p_store, AUTH_STORE_ERROR);
      pthread_mutex_unlock(&p_store->mutex);
      return AUTH_STORE_ERROR;
    }
  }

  if (Deita_Query_Execute_Update(p_store->p_connection, "COMMIT") < 0)
  {
    auth__rollback(p_store, AUTH_STORE_ERROR);
    pthread_mutex_unlock(&p_store->mutex);
    return AUTH_STORE_ERROR;
  }

  pthread_mutex_unlock(&p_store->mutex);
  return AUTH_STORE_OK;
}

/* ------------------------------------------------------------------ */
/* Session management                                                   */
/* ------------------------------------------------------------------ */

Auth_Store_Result Auth_Store_Create_Session(
    Auth_Store          *p_store,
    const char          *user_id,
    const char          *token_digest,
    const char          *csrf_digest,
    int64                idle_ttl_secs,
    int64                absolute_ttl_secs,
    int64                current_unix,
    Auth_Session_Record *p_record)
{
  if (!p_store || !user_id || !token_digest || !csrf_digest || !p_record)
    return AUTH_STORE_INVALID_ARG;
  if (!auth__session_arguments_are_valid(
          token_digest, csrf_digest, idle_ttl_secs,
          absolute_ttl_secs, current_unix))
    return AUTH_STORE_INVALID_ARG;

  pthread_mutex_lock(&p_store->mutex);

  if (Deita_Query_Execute_Update(
          p_store->p_connection, "BEGIN IMMEDIATE") < 0)
  {
    pthread_mutex_unlock(&p_store->mutex);
    return AUTH_STORE_ERROR;
  }

  /* Verify user is active and read password_changed_at snapshot. */
  Dowa_Arena       *p_arena = Dowa_Arena_Create(2048);
  if (!p_arena)
  {
    auth__rollback(p_store, AUTH_STORE_ERROR);
    pthread_mutex_unlock(&p_store->mutex);
    return AUTH_STORE_ERROR;
  }
  const char       *sel_params[] = {user_id};
  Deita_Result_Set *p_result = Deita_Query_Execute_Prepared(
      p_store->p_connection,
      "SELECT status, password_changed_at FROM users WHERE id = ?",
      1, sel_params, p_arena);
  if (!p_result || !Deita_Result_Set_Next(p_result))
  {
    if (p_result)
      Deita_Result_Set_Free(p_result);
    Dowa_Arena_Free(p_arena);
    auth__rollback(p_store, AUTH_STORE_ERROR);
    pthread_mutex_unlock(&p_store->mutex);
    return AUTH_STORE_NOT_FOUND;
  }
  char  cur_status[9];
  int64 pca;
  auth__copy_text_fixed(cur_status, sizeof(cur_status),
                        Deita_Result_Set_Get_Text(p_result, 0));
  pca = Deita_Result_Set_Get_Integer(p_result, 1);
  Deita_Result_Set_Free(p_result);
  Dowa_Arena_Free(p_arena);

  if (strcmp(cur_status, "disabled") == 0)
  {
    auth__rollback(p_store, AUTH_STORE_ERROR);
    pthread_mutex_unlock(&p_store->mutex);
    return AUTH_STORE_USER_DISABLED;
  }

  Auth_Store_Result insert_result = auth__insert_session_locked(
      p_store, user_id, token_digest, csrf_digest,
      idle_ttl_secs, absolute_ttl_secs, current_unix, pca);
  if (insert_result != AUTH_STORE_OK)
  {
    auth__rollback(p_store, AUTH_STORE_ERROR);
    pthread_mutex_unlock(&p_store->mutex);
    return insert_result;
  }

  if (Deita_Query_Execute_Update(p_store->p_connection, "COMMIT") < 0)
  {
    auth__rollback(p_store, AUTH_STORE_ERROR);
    pthread_mutex_unlock(&p_store->mutex);
    return AUTH_STORE_ERROR;
  }

  pthread_mutex_unlock(&p_store->mutex);

  auth__fill_session_record(
      p_record, user_id, idle_ttl_secs, absolute_ttl_secs, current_unix, pca);
  return AUTH_STORE_OK;
}

Auth_Store_Result Auth_Store_Create_Session_CAS(
    Auth_Store          *p_store,
    const char          *user_id,
    const char          *expected_password_hash,
    const char          *token_digest,
    const char          *csrf_digest,
    int64                idle_ttl_secs,
    int64                absolute_ttl_secs,
    int64                current_unix,
    Auth_Session_Record *p_record)
{
  if (!p_store || !user_id || !expected_password_hash ||
      expected_password_hash[0] == '\0' || !p_record)
    return AUTH_STORE_INVALID_ARG;
  if (!auth__session_arguments_are_valid(
          token_digest, csrf_digest, idle_ttl_secs,
          absolute_ttl_secs, current_unix))
    return AUTH_STORE_INVALID_ARG;

  char              current_hash[AUTH_CRYPTO_PASSWORD_HASH_ENCODED_SIZE] = {0};
  char              current_status[9] = {0};
  int64             password_changed_at = 0;
  Auth_Store_Result result = AUTH_STORE_ERROR;

  pthread_mutex_lock(&p_store->mutex);
  if (Deita_Query_Execute_Update(
          p_store->p_connection, "BEGIN IMMEDIATE") < 0)
  {
    pthread_mutex_unlock(&p_store->mutex);
    OPENSSL_cleanse(current_hash, sizeof(current_hash));
    return AUTH_STORE_ERROR;
  }

  Dowa_Arena *p_arena = Dowa_Arena_Create(2048);
  if (!p_arena)
  {
    result = auth__rollback(p_store, AUTH_STORE_ERROR);
    goto create_session_cas_done;
  }

  const char *select_params[] = {user_id};
  Deita_Result_Set *p_result = Deita_Query_Execute_Prepared(
      p_store->p_connection,
      "SELECT status, password_changed_at, password_hash"
      "  FROM users WHERE id = ?",
      1, select_params, p_arena);
  if (!p_result)
  {
    Dowa_Arena_Free(p_arena);
    result = auth__rollback(p_store, AUTH_STORE_ERROR);
    goto create_session_cas_done;
  }
  if (!Deita_Result_Set_Next(p_result))
  {
    boolean query_error = Deita_Result_Set_Has_Error(p_result);
    Deita_Result_Set_Free(p_result);
    Dowa_Arena_Free(p_arena);
    result = auth__rollback(
        p_store, query_error ? AUTH_STORE_ERROR : AUTH_STORE_NOT_FOUND);
    goto create_session_cas_done;
  }

  auth__copy_text_fixed(
      current_status, sizeof(current_status),
      Deita_Result_Set_Get_Text(p_result, 0));
  password_changed_at = Deita_Result_Set_Get_Integer(p_result, 1);
  auth__copy_text_fixed(
      current_hash, sizeof(current_hash),
      Deita_Result_Set_Get_Text(p_result, 2));
  Deita_Result_Set_Free(p_result);
  Dowa_Arena_Free(p_arena);

  if (strcmp(current_status, "disabled") == 0)
  {
    result = auth__rollback(p_store, AUTH_STORE_USER_DISABLED);
    goto create_session_cas_done;
  }
  if (strcmp(current_hash, expected_password_hash) != 0)
  {
    result = auth__rollback(p_store, AUTH_STORE_STALE_PASSWORD);
    goto create_session_cas_done;
  }

  result = auth__insert_session_locked(
      p_store, user_id, token_digest, csrf_digest,
      idle_ttl_secs, absolute_ttl_secs, current_unix, password_changed_at);
  if (result != AUTH_STORE_OK)
  {
    auth__rollback(p_store, AUTH_STORE_ERROR);
    goto create_session_cas_done;
  }

  if (Deita_Query_Execute_Update(p_store->p_connection, "COMMIT") < 0)
  {
    result = auth__rollback(p_store, AUTH_STORE_ERROR);
    goto create_session_cas_done;
  }

  auth__fill_session_record(
      p_record, user_id, idle_ttl_secs, absolute_ttl_secs,
      current_unix, password_changed_at);
  result = AUTH_STORE_OK;

create_session_cas_done:
  OPENSSL_cleanse(current_hash, sizeof(current_hash));
  pthread_mutex_unlock(&p_store->mutex);
  return result;
}

Auth_Store_Result Auth_Store_Find_Session(
    Auth_Store          *p_store,
    const char          *token_digest,
    int64                current_unix,
    Auth_Session_Record *p_session,
    Auth_User_Record    *p_user)
{
  if (!p_store || !token_digest || !p_session || !p_user)
    return AUTH_STORE_INVALID_ARG;

  memset(p_session, 0, sizeof(*p_session));
  memset(p_user,    0, sizeof(*p_user));

  Dowa_Arena       *p_arena = Dowa_Arena_Create(4096);
  if (!p_arena)
    return AUTH_STORE_ERROR;

  const char       *params[] = {token_digest};
  pthread_mutex_lock(&p_store->mutex);
  Deita_Result_Set *p_result = Deita_Query_Execute_Prepared(
      p_store->p_connection,
      "SELECT"
      "  s.user_id, s.created_at, s.last_seen_at,"
      "  s.idle_expires_at, s.absolute_expires_at,"
      "  s.password_changed_at_snapshot, s.revoked_at,"
      "  u.id, u.username, u.normalized_username, u.role, u.status,"
      "  u.must_change_password, u.password_changed_at,"
      "  u.created_at, u.updated_at"
      "  FROM auth_sessions s"
      "  JOIN users u ON s.user_id = u.id"
      "  WHERE s.token_digest = ?",
      1, params, p_arena);

  if (!p_result || !Deita_Result_Set_Next(p_result))
  {
    if (p_result)
      Deita_Result_Set_Free(p_result);
    pthread_mutex_unlock(&p_store->mutex);
    Dowa_Arena_Free(p_arena);
    return AUTH_STORE_NOT_FOUND;
  }

  /* Read session fields. */
  auth__copy_text_fixed(p_session->user_id, sizeof(p_session->user_id),
                        Deita_Result_Set_Get_Text(p_result, 0));
  p_session->created_at    = Deita_Result_Set_Get_Integer(p_result, 1);
  p_session->last_seen_at  = Deita_Result_Set_Get_Integer(p_result, 2);
  p_session->idle_expires_at       = Deita_Result_Set_Get_Integer(p_result, 3);
  p_session->absolute_expires_at   = Deita_Result_Set_Get_Integer(p_result, 4);
  p_session->password_changed_at_snapshot =
      Deita_Result_Set_Get_Integer(p_result, 5);
  boolean is_revoked =
      (Deita_Result_Set_Get_Column_Type(p_result, 6) != DEITA_COLUMN_TYPE_NULL);

  /* Read user fields (columns 7-15). */
  /* Reuse auth__read_user_record after shifting: pass a pointer with offset */
  Auth_User_Record tmp_user;
  memset(&tmp_user, 0, sizeof(tmp_user));
  auth__copy_text_fixed(tmp_user.id,                 sizeof(tmp_user.id),
                        Deita_Result_Set_Get_Text(p_result, 7));
  auth__copy_text_fixed(tmp_user.username,           sizeof(tmp_user.username),
                        Deita_Result_Set_Get_Text(p_result, 8));
  auth__copy_text_fixed(tmp_user.normalized_username,
                        sizeof(tmp_user.normalized_username),
                        Deita_Result_Set_Get_Text(p_result, 9));
  auth__copy_text_fixed(tmp_user.role,   sizeof(tmp_user.role),
                        Deita_Result_Set_Get_Text(p_result, 10));
  auth__copy_text_fixed(tmp_user.status, sizeof(tmp_user.status),
                        Deita_Result_Set_Get_Text(p_result, 11));
  tmp_user.must_change_password  = Deita_Result_Set_Get_Integer(p_result, 12) ?
                                    TRUE : FALSE;
  tmp_user.password_changed_at   = Deita_Result_Set_Get_Integer(p_result, 13);
  tmp_user.created_at            = Deita_Result_Set_Get_Integer(p_result, 14);
  tmp_user.updated_at            = Deita_Result_Set_Get_Integer(p_result, 15);

  Deita_Result_Set_Free(p_result);
  pthread_mutex_unlock(&p_store->mutex);
  Dowa_Arena_Free(p_arena);

  /* Apply validity checks in order of specificity. */
  if (is_revoked)
    return AUTH_STORE_REVOKED;

  if (current_unix >= p_session->idle_expires_at ||
      current_unix >= p_session->absolute_expires_at)
    return AUTH_STORE_EXPIRED;

  if (strcmp(tmp_user.status, "disabled") == 0)
    return AUTH_STORE_USER_DISABLED;

  if (tmp_user.password_changed_at != p_session->password_changed_at_snapshot)
    return AUTH_STORE_STALE_PASSWORD;

  *p_user = tmp_user;
  return AUTH_STORE_OK;
}

Auth_Store_Result Auth_Store_Touch_Session(
    Auth_Store *p_store,
    const char *token_digest,
    int64       current_unix,
    int64       idle_ttl_secs)
{
  if (!p_store || !token_digest)
    return AUTH_STORE_INVALID_ARG;

  char last_seen_str[32], idle_exp_str[32];
  snprintf(last_seen_str, sizeof(last_seen_str), "%lld", (long long)current_unix);
  snprintf(idle_exp_str,  sizeof(idle_exp_str),  "%lld",
           (long long)(current_unix + idle_ttl_secs));

  const char *params[] = {last_seen_str, idle_exp_str, token_digest};

  pthread_mutex_lock(&p_store->mutex);
  int32 result = Deita_Query_Execute_Update_Prepared(
      p_store->p_connection,
      "UPDATE auth_sessions"
      "  SET last_seen_at = ?, idle_expires_at = ?"
      "  WHERE token_digest = ? AND revoked_at IS NULL",
      3, params);
  pthread_mutex_unlock(&p_store->mutex);

  if (result < 0)
    return AUTH_STORE_ERROR;
  return result == 0 ? AUTH_STORE_NOT_FOUND : AUTH_STORE_OK;
}

Auth_Store_Result Auth_Store_Revoke_Session(
    Auth_Store *p_store,
    const char *token_digest)
{
  if (!p_store || !token_digest)
    return AUTH_STORE_INVALID_ARG;

  const char *params[] = {token_digest};

  pthread_mutex_lock(&p_store->mutex);
  int32 result = Deita_Query_Execute_Update_Prepared(
      p_store->p_connection,
      "UPDATE auth_sessions SET revoked_at = strftime('%s','now')"
      "  WHERE token_digest = ?",
      1, params);
  pthread_mutex_unlock(&p_store->mutex);

  if (result < 0)
    return AUTH_STORE_ERROR;
  return result == 0 ? AUTH_STORE_NOT_FOUND : AUTH_STORE_OK;
}

Auth_Store_Result Auth_Store_Revoke_All_Sessions(
    Auth_Store *p_store,
    const char *user_id,
    const char *except_token_digest)
{
  if (!p_store || !user_id)
    return AUTH_STORE_INVALID_ARG;

  pthread_mutex_lock(&p_store->mutex);
  int32 result;
  if (except_token_digest && except_token_digest[0] != '\0')
  {
    const char *params[] = {user_id, except_token_digest};
    result = Deita_Query_Execute_Update_Prepared(
        p_store->p_connection,
        "UPDATE auth_sessions SET revoked_at = strftime('%s','now')"
        "  WHERE user_id = ? AND token_digest != ? AND revoked_at IS NULL",
        2, params);
  }
  else
  {
    const char *params[] = {user_id};
    result = Deita_Query_Execute_Update_Prepared(
        p_store->p_connection,
        "UPDATE auth_sessions SET revoked_at = strftime('%s','now')"
        "  WHERE user_id = ? AND revoked_at IS NULL",
        1, params);
  }
  pthread_mutex_unlock(&p_store->mutex);

  return result < 0 ? AUTH_STORE_ERROR : AUTH_STORE_OK;
}

Auth_Store_Result Auth_Store_Revoke_All_Sessions_Audited(
    Auth_Store *p_store,
    const char *user_id,
    const char *except_token_digest,
    const char *actor_user_id)
{
  if (!p_store || !user_id)
    return AUTH_STORE_INVALID_ARG;
  if (except_token_digest && except_token_digest[0] != '\0' &&
      !auth__digest_is_valid(except_token_digest))
    return AUTH_STORE_INVALID_ARG;

  pthread_mutex_lock(&p_store->mutex);
  if (Deita_Query_Execute_Update(
          p_store->p_connection, "BEGIN IMMEDIATE") < 0)
  {
    pthread_mutex_unlock(&p_store->mutex);
    return AUTH_STORE_ERROR;
  }

  Dowa_Arena *p_arena = Dowa_Arena_Create(1024);
  if (!p_arena)
  {
    auth__rollback(p_store, AUTH_STORE_ERROR);
    pthread_mutex_unlock(&p_store->mutex);
    return AUTH_STORE_ERROR;
  }

  const char *find_params[] = {user_id};
  Deita_Result_Set *p_result = Deita_Query_Execute_Prepared(
      p_store->p_connection,
      "SELECT 1 FROM users WHERE id = ?",
      1, find_params, p_arena);
  if (!p_result)
  {
    Dowa_Arena_Free(p_arena);
    auth__rollback(p_store, AUTH_STORE_ERROR);
    pthread_mutex_unlock(&p_store->mutex);
    return AUTH_STORE_ERROR;
  }
  if (!Deita_Result_Set_Next(p_result))
  {
    boolean query_error = Deita_Result_Set_Has_Error(p_result);
    Deita_Result_Set_Free(p_result);
    Dowa_Arena_Free(p_arena);
    auth__rollback(p_store, AUTH_STORE_ERROR);
    pthread_mutex_unlock(&p_store->mutex);
    return query_error ? AUTH_STORE_ERROR : AUTH_STORE_NOT_FOUND;
  }
  Deita_Result_Set_Free(p_result);
  Dowa_Arena_Free(p_arena);

  int32 revoke_result;
  if (except_token_digest && except_token_digest[0] != '\0')
  {
    const char *params[] = {user_id, except_token_digest};
    revoke_result = Deita_Query_Execute_Update_Prepared(
        p_store->p_connection,
        "UPDATE auth_sessions SET revoked_at = strftime('%s','now')"
        "  WHERE user_id = ? AND token_digest != ? AND revoked_at IS NULL",
        2, params);
  }
  else
  {
    const char *params[] = {user_id};
    revoke_result = Deita_Query_Execute_Update_Prepared(
        p_store->p_connection,
        "UPDATE auth_sessions SET revoked_at = strftime('%s','now')"
        "  WHERE user_id = ? AND revoked_at IS NULL",
        1, params);
  }
  if (revoke_result < 0)
  {
    auth__rollback(p_store, AUTH_STORE_ERROR);
    pthread_mutex_unlock(&p_store->mutex);
    return AUTH_STORE_ERROR;
  }

  if (!auth__insert_audit_log_locked(
          p_store, actor_user_id, "admin_sessions_revoked", user_id, NULL))
  {
    auth__rollback(p_store, AUTH_STORE_ERROR);
    pthread_mutex_unlock(&p_store->mutex);
    return AUTH_STORE_ERROR;
  }

  if (Deita_Query_Execute_Update(p_store->p_connection, "COMMIT") < 0)
  {
    auth__rollback(p_store, AUTH_STORE_ERROR);
    pthread_mutex_unlock(&p_store->mutex);
    return AUTH_STORE_ERROR;
  }

  pthread_mutex_unlock(&p_store->mutex);
  return AUTH_STORE_OK;
}

Auth_Store_Result Auth_Store_Rotate_Session(
    Auth_Store          *p_store,
    const char          *user_id,
    const char          *old_token_digest,
    const char          *new_token_digest,
    const char          *new_csrf_digest,
    int64                idle_ttl_secs,
    int64                absolute_ttl_secs,
    int64                current_unix,
    Auth_Session_Record *p_record)
{
  if (!p_store || !user_id || !old_token_digest || !new_token_digest ||
      !new_csrf_digest || !p_record || !auth__digest_is_valid(old_token_digest))
    return AUTH_STORE_INVALID_ARG;
  if (!auth__session_arguments_are_valid(
          new_token_digest, new_csrf_digest, idle_ttl_secs,
          absolute_ttl_secs, current_unix))
    return AUTH_STORE_INVALID_ARG;

  pthread_mutex_lock(&p_store->mutex);

  if (Deita_Query_Execute_Update(
          p_store->p_connection, "BEGIN IMMEDIATE") < 0)
  {
    pthread_mutex_unlock(&p_store->mutex);
    return AUTH_STORE_ERROR;
  }

  /* Verify user is active and read password_changed_at snapshot. */
  Dowa_Arena       *p_arena = Dowa_Arena_Create(2048);
  if (!p_arena)
  {
    auth__rollback(p_store, AUTH_STORE_ERROR);
    pthread_mutex_unlock(&p_store->mutex);
    return AUTH_STORE_ERROR;
  }
  const char       *sel_params[] = {user_id};
  Deita_Result_Set *p_result = Deita_Query_Execute_Prepared(
      p_store->p_connection,
      "SELECT status, password_changed_at FROM users WHERE id = ?",
      1, sel_params, p_arena);
  if (!p_result || !Deita_Result_Set_Next(p_result))
  {
    if (p_result)
      Deita_Result_Set_Free(p_result);
    Dowa_Arena_Free(p_arena);
    auth__rollback(p_store, AUTH_STORE_ERROR);
    pthread_mutex_unlock(&p_store->mutex);
    return AUTH_STORE_NOT_FOUND;
  }
  char  cur_status[9];
  int64 pca;
  auth__copy_text_fixed(cur_status, sizeof(cur_status),
                        Deita_Result_Set_Get_Text(p_result, 0));
  pca = Deita_Result_Set_Get_Integer(p_result, 1);
  Deita_Result_Set_Free(p_result);
  Dowa_Arena_Free(p_arena);

  if (strcmp(cur_status, "disabled") == 0)
  {
    auth__rollback(p_store, AUTH_STORE_ERROR);
    pthread_mutex_unlock(&p_store->mutex);
    return AUTH_STORE_USER_DISABLED;
  }

  /* Insert new session. */
  Auth_Store_Result insert_result = auth__insert_session_locked(
      p_store, user_id, new_token_digest, new_csrf_digest,
      idle_ttl_secs, absolute_ttl_secs, current_unix, pca);
  if (insert_result != AUTH_STORE_OK)
  {
    auth__rollback(p_store, AUTH_STORE_ERROR);
    pthread_mutex_unlock(&p_store->mutex);
    return insert_result;
  }

  /* Revoke old session (idempotent: ignore if already revoked). */
  const char *rev_params[] = {old_token_digest};
  int32 revoke_result = Deita_Query_Execute_Update_Prepared(
      p_store->p_connection,
      "UPDATE auth_sessions SET revoked_at = strftime('%s','now')"
      "  WHERE token_digest = ? AND revoked_at IS NULL",
      1, rev_params);
  if (revoke_result < 0)
  {
    auth__rollback(p_store, AUTH_STORE_ERROR);
    pthread_mutex_unlock(&p_store->mutex);
    return AUTH_STORE_ERROR;
  }

  if (Deita_Query_Execute_Update(p_store->p_connection, "COMMIT") < 0)
  {
    auth__rollback(p_store, AUTH_STORE_ERROR);
    pthread_mutex_unlock(&p_store->mutex);
    return AUTH_STORE_ERROR;
  }

  pthread_mutex_unlock(&p_store->mutex);

  auth__fill_session_record(
      p_record, user_id, idle_ttl_secs, absolute_ttl_secs, current_unix, pca);
  return AUTH_STORE_OK;
}

Auth_Store_Result Auth_Store_Self_Change_Password(
    Auth_Store          *p_store,
    const char          *user_id,
    const char          *old_encoded_hash,
    const char          *new_encoded_hash,
    const char          *new_token_digest,
    const char          *new_csrf_digest,
    int64                idle_ttl_secs,
    int64                absolute_ttl_secs,
    int64                current_unix,
    Auth_Session_Record *p_record)
{
  if (!p_store || !user_id || !old_encoded_hash || !new_encoded_hash ||
      old_encoded_hash[0] == '\0' || new_encoded_hash[0] == '\0' || !p_record)
    return AUTH_STORE_INVALID_ARG;
  if (!auth__session_arguments_are_valid(
          new_token_digest, new_csrf_digest, idle_ttl_secs,
          absolute_ttl_secs, current_unix))
    return AUTH_STORE_INVALID_ARG;

  char current_hash[AUTH_CRYPTO_PASSWORD_HASH_ENCODED_SIZE] = {0};
  char reread_hash[AUTH_CRYPTO_PASSWORD_HASH_ENCODED_SIZE] = {0};
  char status[9] = {0};
  char timestamp[32];
  snprintf(timestamp, sizeof(timestamp), "%lld", (long long)current_unix);
  Auth_Store_Result result = AUTH_STORE_ERROR;

  pthread_mutex_lock(&p_store->mutex);
  if (Deita_Query_Execute_Update(
          p_store->p_connection, "BEGIN IMMEDIATE") < 0)
  {
    pthread_mutex_unlock(&p_store->mutex);
    OPENSSL_cleanse(current_hash, sizeof(current_hash));
    OPENSSL_cleanse(reread_hash, sizeof(reread_hash));
    return AUTH_STORE_ERROR;
  }

  Dowa_Arena *p_arena = Dowa_Arena_Create(2048);
  if (!p_arena)
  {
    result = auth__rollback(p_store, AUTH_STORE_ERROR);
    goto self_change_done;
  }

  const char *select_params[] = {user_id};
  Deita_Result_Set *p_result = Deita_Query_Execute_Prepared(
      p_store->p_connection,
      "SELECT status, password_hash FROM users WHERE id = ?",
      1, select_params, p_arena);
  if (!p_result)
  {
    Dowa_Arena_Free(p_arena);
    result = auth__rollback(p_store, AUTH_STORE_ERROR);
    goto self_change_done;
  }
  if (!Deita_Result_Set_Next(p_result))
  {
    boolean query_error = Deita_Result_Set_Has_Error(p_result);
    Deita_Result_Set_Free(p_result);
    Dowa_Arena_Free(p_arena);
    result = auth__rollback(
        p_store, query_error ? AUTH_STORE_ERROR : AUTH_STORE_NOT_FOUND);
    goto self_change_done;
  }
  auth__copy_text_fixed(
      status, sizeof(status), Deita_Result_Set_Get_Text(p_result, 0));
  auth__copy_text_fixed(
      current_hash, sizeof(current_hash),
      Deita_Result_Set_Get_Text(p_result, 1));
  Deita_Result_Set_Free(p_result);
  Dowa_Arena_Free(p_arena);

  if (strcmp(status, "disabled") == 0)
  {
    result = auth__rollback(p_store, AUTH_STORE_USER_DISABLED);
    goto self_change_done;
  }
  if (strcmp(current_hash, old_encoded_hash) != 0)
  {
    result = auth__rollback(p_store, AUTH_STORE_STALE_PASSWORD);
    goto self_change_done;
  }

  const char *update_params[] = {
    new_encoded_hash, timestamp, timestamp, user_id, old_encoded_hash
  };
  int32 update_result = Deita_Query_Execute_Update_Prepared(
      p_store->p_connection,
      "UPDATE users"
      "  SET password_hash = ?, password_changed_at = ?,"
      "      must_change_password = 0, updated_at = ?"
      "  WHERE id = ? AND password_hash = ?",
      5, update_params);
  if (update_result < 0)
  {
    result = auth__rollback(p_store, AUTH_STORE_ERROR);
    goto self_change_done;
  }
  if (update_result == 0)
  {
    result = auth__rollback(p_store, AUTH_STORE_STALE_PASSWORD);
    goto self_change_done;
  }

  p_arena = Dowa_Arena_Create(2048);
  if (!p_arena)
  {
    result = auth__rollback(p_store, AUTH_STORE_ERROR);
    goto self_change_done;
  }
  p_result = Deita_Query_Execute_Prepared(
      p_store->p_connection,
      "SELECT status, password_changed_at, password_hash"
      "  FROM users WHERE id = ?",
      1, select_params, p_arena);
  if (!p_result)
  {
    Dowa_Arena_Free(p_arena);
    result = auth__rollback(p_store, AUTH_STORE_ERROR);
    goto self_change_done;
  }
  if (!Deita_Result_Set_Next(p_result))
  {
    Deita_Result_Set_Free(p_result);
    Dowa_Arena_Free(p_arena);
    result = auth__rollback(p_store, AUTH_STORE_ERROR);
    goto self_change_done;
  }
  auth__copy_text_fixed(
      status, sizeof(status), Deita_Result_Set_Get_Text(p_result, 0));
  int64 password_changed_at = Deita_Result_Set_Get_Integer(p_result, 1);
  auth__copy_text_fixed(
      reread_hash, sizeof(reread_hash),
      Deita_Result_Set_Get_Text(p_result, 2));
  Deita_Result_Set_Free(p_result);
  Dowa_Arena_Free(p_arena);
  if (strcmp(status, "active") != 0 ||
      password_changed_at != current_unix ||
      strcmp(reread_hash, new_encoded_hash) != 0)
  {
    result = auth__rollback(p_store, AUTH_STORE_ERROR);
    goto self_change_done;
  }

  const char *revoke_params[] = {timestamp, user_id};
  if (Deita_Query_Execute_Update_Prepared(
          p_store->p_connection,
          "UPDATE auth_sessions SET revoked_at = ?"
          "  WHERE user_id = ? AND revoked_at IS NULL",
          2, revoke_params) < 0)
  {
    result = auth__rollback(p_store, AUTH_STORE_ERROR);
    goto self_change_done;
  }

  result = auth__insert_session_locked(
      p_store, user_id, new_token_digest, new_csrf_digest,
      idle_ttl_secs, absolute_ttl_secs, current_unix, password_changed_at);
  if (result != AUTH_STORE_OK)
  {
    auth__rollback(p_store, AUTH_STORE_ERROR);
    goto self_change_done;
  }

  if (Deita_Query_Execute_Update(p_store->p_connection, "COMMIT") < 0)
  {
    result = auth__rollback(p_store, AUTH_STORE_ERROR);
    goto self_change_done;
  }

  auth__fill_session_record(
      p_record, user_id, idle_ttl_secs, absolute_ttl_secs,
      current_unix, password_changed_at);
  result = AUTH_STORE_OK;

self_change_done:
  OPENSSL_cleanse(current_hash, sizeof(current_hash));
  OPENSSL_cleanse(reread_hash, sizeof(reread_hash));
  pthread_mutex_unlock(&p_store->mutex);
  return result;
}

/* ------------------------------------------------------------------ */
/* Guest identity                                                       */
/* ------------------------------------------------------------------ */

Auth_Store_Result Auth_Store_Upsert_Guest_Identity(
    Auth_Store                *p_store,
    const char                *guest_id,
    const char                *ip_binding_digest,
    int64                      expires_at,
    Auth_Guest_Identity_Record *p_record)
{
  if (!p_store || !guest_id || !ip_binding_digest || !p_record)
    return AUTH_STORE_INVALID_ARG;

  char exp_str[32];
  snprintf(exp_str, sizeof(exp_str), "%lld", (long long)expires_at);

  pthread_mutex_lock(&p_store->mutex);

  if (Deita_Query_Execute_Update(
          p_store->p_connection, "BEGIN IMMEDIATE") < 0)
  {
    pthread_mutex_unlock(&p_store->mutex);
    return AUTH_STORE_ERROR;
  }

  /* INSERT OR IGNORE so usage data is not cascade-deleted on upsert. */
  const char *ins_params[] = {guest_id, ip_binding_digest, exp_str};
  Deita_Query_Execute_Update_Prepared(
      p_store->p_connection,
      "INSERT OR IGNORE INTO guest_identities (id, ip_binding_digest, expires_at)"
      "  VALUES (?, ?, ?)",
      3, ins_params);

  /* Always refresh last_seen_at and expires_at. */
  const char *upd_params[] = {exp_str, guest_id};
  if (Deita_Query_Execute_Update_Prepared(
          p_store->p_connection,
          "UPDATE guest_identities"
          "  SET last_seen_at = strftime('%s','now'), expires_at = ?"
          "  WHERE id = ?",
          2, upd_params) < 0)
  {
    auth__rollback(p_store, AUTH_STORE_ERROR);
    pthread_mutex_unlock(&p_store->mutex);
    return AUTH_STORE_ERROR;
  }

  /* Read back the current row. */
  Dowa_Arena       *p_arena = Dowa_Arena_Create(2048);
  if (!p_arena)
  {
    auth__rollback(p_store, AUTH_STORE_ERROR);
    pthread_mutex_unlock(&p_store->mutex);
    return AUTH_STORE_ERROR;
  }
  const char       *sel_params[] = {guest_id};
  Deita_Result_Set *p_result = Deita_Query_Execute_Prepared(
      p_store->p_connection,
      "SELECT id, created_at, last_seen_at, expires_at"
      "  FROM guest_identities WHERE id = ?",
      1, sel_params, p_arena);
  if (!p_result || !Deita_Result_Set_Next(p_result))
  {
    if (p_result)
      Deita_Result_Set_Free(p_result);
    Dowa_Arena_Free(p_arena);
    auth__rollback(p_store, AUTH_STORE_ERROR);
    pthread_mutex_unlock(&p_store->mutex);
    return AUTH_STORE_ERROR;
  }
  auth__copy_text_fixed(p_record->id, sizeof(p_record->id),
                        Deita_Result_Set_Get_Text(p_result, 0));
  p_record->created_at   = Deita_Result_Set_Get_Integer(p_result, 1);
  p_record->last_seen_at = Deita_Result_Set_Get_Integer(p_result, 2);
  p_record->expires_at   = Deita_Result_Set_Get_Integer(p_result, 3);
  Deita_Result_Set_Free(p_result);
  Dowa_Arena_Free(p_arena);

  if (Deita_Query_Execute_Update(p_store->p_connection, "COMMIT") < 0)
  {
    auth__rollback(p_store, AUTH_STORE_ERROR);
    pthread_mutex_unlock(&p_store->mutex);
    return AUTH_STORE_ERROR;
  }

  pthread_mutex_unlock(&p_store->mutex);
  return AUTH_STORE_OK;
}

Auth_Store_Result Auth_Store_Find_Guest_Identity(
    Auth_Store                *p_store,
    const char                *guest_id,
    int64                      current_unix,
    Auth_Guest_Identity_Record *p_record)
{
  if (!p_store || !guest_id || !p_record)
    return AUTH_STORE_INVALID_ARG;

  memset(p_record, 0, sizeof(*p_record));

  Dowa_Arena       *p_arena = Dowa_Arena_Create(2048);
  if (!p_arena)
    return AUTH_STORE_ERROR;

  const char       *params[] = {guest_id};
  pthread_mutex_lock(&p_store->mutex);
  Deita_Result_Set *p_result = Deita_Query_Execute_Prepared(
      p_store->p_connection,
      "SELECT id, created_at, last_seen_at, expires_at"
      "  FROM guest_identities WHERE id = ?",
      1, params, p_arena);
  if (!p_result || !Deita_Result_Set_Next(p_result))
  {
    if (p_result)
      Deita_Result_Set_Free(p_result);
    pthread_mutex_unlock(&p_store->mutex);
    Dowa_Arena_Free(p_arena);
    return AUTH_STORE_NOT_FOUND;
  }
  auth__copy_text_fixed(p_record->id, sizeof(p_record->id),
                        Deita_Result_Set_Get_Text(p_result, 0));
  p_record->created_at   = Deita_Result_Set_Get_Integer(p_result, 1);
  p_record->last_seen_at = Deita_Result_Set_Get_Integer(p_result, 2);
  p_record->expires_at   = Deita_Result_Set_Get_Integer(p_result, 3);
  Deita_Result_Set_Free(p_result);
  pthread_mutex_unlock(&p_store->mutex);
  Dowa_Arena_Free(p_arena);

  if (current_unix >= p_record->expires_at)
    return AUTH_STORE_EXPIRED;

  return AUTH_STORE_OK;
}

/* ------------------------------------------------------------------ */
/* Guest quota                                                          */
/* ------------------------------------------------------------------ */

static void auth__i64_str(char *buf, size_t size, int64 value)
{
  snprintf(buf, size, "%lld", (long long)value);
}

Auth_Store_Result Auth_Store_Guest_Get_Usage(
    Auth_Store             *p_store,
    const char             *guest_id,
    int64                   window_start,
    Auth_Store_Guest_Usage *p_usage)
{
  if (!p_store || !guest_id || !p_usage)
    return AUTH_STORE_INVALID_ARG;

  memset(p_usage, 0, sizeof(*p_usage));

  char ws_str[32];
  auth__i64_str(ws_str, sizeof(ws_str), window_start);

  Dowa_Arena *p_arena = Dowa_Arena_Create(2048);
  if (!p_arena)
    return AUTH_STORE_ERROR;

  const char       *params[] = {guest_id, ws_str};
  pthread_mutex_lock(&p_store->mutex);
  auth__reap_expired_reservations_locked(p_store, (int64)time(NULL));
  Deita_Result_Set *p_result = Deita_Query_Execute_Prepared(
      p_store->p_connection,
      "SELECT turns_used, output_tokens_used, output_tokens_reserved"
      "  FROM guest_usage WHERE guest_id = ? AND window_start = ?",
      2, params, p_arena);
  if (p_result && Deita_Result_Set_Next(p_result))
  {
    p_usage->turns_used             = Deita_Result_Set_Get_Integer(p_result, 0);
    p_usage->output_tokens_used     = Deita_Result_Set_Get_Integer(p_result, 1);
    p_usage->output_tokens_reserved = Deita_Result_Set_Get_Integer(p_result, 2);
  }
  if (p_result)
    Deita_Result_Set_Free(p_result);
  pthread_mutex_unlock(&p_store->mutex);
  Dowa_Arena_Free(p_arena);
  return AUTH_STORE_OK;
}

Auth_Store_Guest_Quota_Result Auth_Store_Guest_Reserve(
    Auth_Store *p_store,
    const char *guest_id,
    const char *request_id,
    int64       window_start,
    int64       max_output_tokens,
    int64       turns_limit,
    int64       tokens_limit,
    int64       reservation_expires)
{
  if (!p_store || !guest_id || !request_id ||
      max_output_tokens <= 0 || turns_limit <= 0 || tokens_limit <= 0)
    return AUTH_STORE_GUEST_QUOTA_ERROR;

  char ws_str[32], exp_str[32], tok_str[32];
  auth__i64_str(ws_str,  sizeof(ws_str),  window_start);
  auth__i64_str(exp_str, sizeof(exp_str), reservation_expires);
  auth__i64_str(tok_str, sizeof(tok_str), max_output_tokens);

  Dowa_Arena *p_arena = Dowa_Arena_Create(4096);
  if (!p_arena)
    return AUTH_STORE_GUEST_QUOTA_ERROR;

  pthread_mutex_lock(&p_store->mutex);

  if (Deita_Query_Execute_Update(p_store->p_connection, "BEGIN IMMEDIATE") < 0)
  {
    pthread_mutex_unlock(&p_store->mutex);
    Dowa_Arena_Free(p_arena);
    return AUTH_STORE_GUEST_QUOTA_ERROR;
  }
  auth__reap_expired_reservations_locked(p_store, (int64)time(NULL));

  /* Ensure usage row exists for this window. */
  const char *ins_params[] = {guest_id, ws_str};
  if (Deita_Query_Execute_Update_Prepared(
          p_store->p_connection,
          "INSERT OR IGNORE INTO guest_usage"
          "  (guest_id, window_start, count)"
          "  VALUES (?, ?, 0)",
          2, ins_params) < 0)
  {
    auth__rollback(p_store, AUTH_STORE_ERROR);
    pthread_mutex_unlock(&p_store->mutex);
    Dowa_Arena_Free(p_arena);
    return AUTH_STORE_GUEST_QUOTA_ERROR;
  }

  /* Read current usage. */
  const char       *sel_params[] = {guest_id, ws_str};
  Deita_Result_Set *p_result = Deita_Query_Execute_Prepared(
      p_store->p_connection,
      "SELECT turns_used, output_tokens_used, output_tokens_reserved"
      "  FROM guest_usage WHERE guest_id = ? AND window_start = ?",
      2, sel_params, p_arena);
  if (!p_result || !Deita_Result_Set_Next(p_result))
  {
    if (p_result) Deita_Result_Set_Free(p_result);
    auth__rollback(p_store, AUTH_STORE_ERROR);
    pthread_mutex_unlock(&p_store->mutex);
    Dowa_Arena_Free(p_arena);
    return AUTH_STORE_GUEST_QUOTA_ERROR;
  }
  int64 turns_used    = Deita_Result_Set_Get_Integer(p_result, 0);
  int64 tokens_used   = Deita_Result_Set_Get_Integer(p_result, 1);
  int64 tokens_resvd  = Deita_Result_Set_Get_Integer(p_result, 2);
  Deita_Result_Set_Free(p_result);

  /* Check turn limit. */
  if (turns_used >= turns_limit)
  {
    auth__rollback(p_store, AUTH_STORE_ERROR);
    pthread_mutex_unlock(&p_store->mutex);
    Dowa_Arena_Free(p_arena);
    return AUTH_STORE_GUEST_QUOTA_TURNS_EXHAUSTED;
  }

  /* Check token limit: used + reserved + new_reservation <= limit. */
  if (tokens_used + tokens_resvd + max_output_tokens > tokens_limit)
  {
    auth__rollback(p_store, AUTH_STORE_ERROR);
    pthread_mutex_unlock(&p_store->mutex);
    Dowa_Arena_Free(p_arena);
    return AUTH_STORE_GUEST_QUOTA_TOKENS_EXHAUSTED;
  }

  /* Update usage: charge turn, add token reservation. */
  const char *upd_params[] = {tok_str, guest_id, ws_str};
  if (Deita_Query_Execute_Update_Prepared(
          p_store->p_connection,
          "UPDATE guest_usage"
          "  SET turns_used = turns_used + 1,"
          "      output_tokens_reserved = output_tokens_reserved + ?,"
          "      count = count + 1"
          "  WHERE guest_id = ? AND window_start = ?",
          3, upd_params) <= 0)
  {
    auth__rollback(p_store, AUTH_STORE_ERROR);
    pthread_mutex_unlock(&p_store->mutex);
    Dowa_Arena_Free(p_arena);
    return AUTH_STORE_GUEST_QUOTA_ERROR;
  }

  /* Insert reservation row. */
  const char *res_params[] = {request_id, guest_id, ws_str, tok_str, exp_str};
  if (Deita_Query_Execute_Update_Prepared(
          p_store->p_connection,
          "INSERT INTO guest_usage_reservations"
          "  (request_id, guest_id, window_start, output_tokens_reserved, expires_at)"
          "  VALUES (?, ?, ?, ?, ?)",
          5, res_params) < 0)
  {
    auth__rollback(p_store, AUTH_STORE_ERROR);
    pthread_mutex_unlock(&p_store->mutex);
    Dowa_Arena_Free(p_arena);
    return AUTH_STORE_GUEST_QUOTA_ERROR;
  }

  if (Deita_Query_Execute_Update(p_store->p_connection, "COMMIT") < 0)
  {
    auth__rollback(p_store, AUTH_STORE_ERROR);
    pthread_mutex_unlock(&p_store->mutex);
    Dowa_Arena_Free(p_arena);
    return AUTH_STORE_GUEST_QUOTA_ERROR;
  }

  pthread_mutex_unlock(&p_store->mutex);
  Dowa_Arena_Free(p_arena);
  return AUTH_STORE_GUEST_QUOTA_OK;
}

Auth_Store_Result Auth_Store_Guest_Reconcile(
    Auth_Store *p_store,
    const char *request_id,
    int64       actual_output_tokens)
{
  if (!p_store || !request_id)
    return AUTH_STORE_INVALID_ARG;
  if (actual_output_tokens < 0)
    actual_output_tokens = 0;

  Dowa_Arena *p_arena = Dowa_Arena_Create(4096);
  if (!p_arena)
    return AUTH_STORE_ERROR;

  pthread_mutex_lock(&p_store->mutex);

  if (Deita_Query_Execute_Update(p_store->p_connection, "BEGIN IMMEDIATE") < 0)
  {
    pthread_mutex_unlock(&p_store->mutex);
    Dowa_Arena_Free(p_arena);
    return AUTH_STORE_ERROR;
  }

  /* Fetch reservation. */
  const char       *sel_params[] = {request_id};
  Deita_Result_Set *p_result = Deita_Query_Execute_Prepared(
      p_store->p_connection,
      "SELECT guest_id, window_start, output_tokens_reserved"
      "  FROM guest_usage_reservations WHERE request_id = ?",
      1, sel_params, p_arena);
  if (!p_result || !Deita_Result_Set_Next(p_result))
  {
    /* Idempotent: reservation already gone. */
    if (p_result) Deita_Result_Set_Free(p_result);
    Deita_Query_Execute_Update(p_store->p_connection, "COMMIT");
    pthread_mutex_unlock(&p_store->mutex);
    Dowa_Arena_Free(p_arena);
    return AUTH_STORE_OK;
  }

  char guest_id[37];
  auth__copy_text_fixed(guest_id, sizeof(guest_id),
                        Deita_Result_Set_Get_Text(p_result, 0));
  int64 window_start       = Deita_Result_Set_Get_Integer(p_result, 1);
  int64 tokens_reserved    = Deita_Result_Set_Get_Integer(p_result, 2);
  Deita_Result_Set_Free(p_result);

  /* Charge provider-reported usage even when it exceeds the reservation. */
  int64 to_charge = actual_output_tokens;

  char ws_str[32], charge_str[32], res_str[32];
  auth__i64_str(ws_str,     sizeof(ws_str),     window_start);
  auth__i64_str(charge_str, sizeof(charge_str), to_charge);
  auth__i64_str(res_str,    sizeof(res_str),    tokens_reserved);

  /* Update usage: add actual tokens, subtract reservation. */
  const char *upd_params[] = {charge_str, res_str, guest_id, ws_str};
  if (Deita_Query_Execute_Update_Prepared(
          p_store->p_connection,
          "UPDATE guest_usage"
          "  SET output_tokens_used = output_tokens_used + ?,"
          "      output_tokens_reserved = MAX(0, output_tokens_reserved - ?)"
          "  WHERE guest_id = ? AND window_start = ?",
          4, upd_params) < 0)
  {
    auth__rollback(p_store, AUTH_STORE_ERROR);
    pthread_mutex_unlock(&p_store->mutex);
    Dowa_Arena_Free(p_arena);
    return AUTH_STORE_ERROR;
  }

  /* Delete reservation. */
  const char *del_params[] = {request_id};
  if (Deita_Query_Execute_Update_Prepared(
          p_store->p_connection,
          "DELETE FROM guest_usage_reservations WHERE request_id = ?",
          1, del_params) < 0)
  {
    auth__rollback(p_store, AUTH_STORE_ERROR);
    pthread_mutex_unlock(&p_store->mutex);
    Dowa_Arena_Free(p_arena);
    return AUTH_STORE_ERROR;
  }

  if (Deita_Query_Execute_Update(p_store->p_connection, "COMMIT") < 0)
  {
    auth__rollback(p_store, AUTH_STORE_ERROR);
    pthread_mutex_unlock(&p_store->mutex);
    Dowa_Arena_Free(p_arena);
    return AUTH_STORE_ERROR;
  }

  pthread_mutex_unlock(&p_store->mutex);
  Dowa_Arena_Free(p_arena);
  return AUTH_STORE_OK;
}

Auth_Store_Result Auth_Store_Guest_Release(
    Auth_Store *p_store,
    const char *request_id)
{
  if (!p_store || !request_id)
    return AUTH_STORE_INVALID_ARG;

  Dowa_Arena *p_arena = Dowa_Arena_Create(4096);
  if (!p_arena)
    return AUTH_STORE_ERROR;

  pthread_mutex_lock(&p_store->mutex);

  if (Deita_Query_Execute_Update(p_store->p_connection, "BEGIN IMMEDIATE") < 0)
  {
    pthread_mutex_unlock(&p_store->mutex);
    Dowa_Arena_Free(p_arena);
    return AUTH_STORE_ERROR;
  }

  /* Fetch reservation. */
  const char       *sel_params[] = {request_id};
  Deita_Result_Set *p_result = Deita_Query_Execute_Prepared(
      p_store->p_connection,
      "SELECT guest_id, window_start, output_tokens_reserved"
      "  FROM guest_usage_reservations WHERE request_id = ?",
      1, sel_params, p_arena);
  if (!p_result || !Deita_Result_Set_Next(p_result))
  {
    /* Idempotent: reservation already gone. */
    if (p_result) Deita_Result_Set_Free(p_result);
    Deita_Query_Execute_Update(p_store->p_connection, "COMMIT");
    pthread_mutex_unlock(&p_store->mutex);
    Dowa_Arena_Free(p_arena);
    return AUTH_STORE_OK;
  }

  char guest_id[37];
  auth__copy_text_fixed(guest_id, sizeof(guest_id),
                        Deita_Result_Set_Get_Text(p_result, 0));
  int64 window_start    = Deita_Result_Set_Get_Integer(p_result, 1);
  int64 tokens_reserved = Deita_Result_Set_Get_Integer(p_result, 2);
  Deita_Result_Set_Free(p_result);

  char ws_str[32], res_str[32];
  auth__i64_str(ws_str,  sizeof(ws_str),  window_start);
  auth__i64_str(res_str, sizeof(res_str), tokens_reserved);

  /* Release token reservation; turns_used is unchanged (turn already charged). */
  const char *upd_params[] = {res_str, guest_id, ws_str};
  if (Deita_Query_Execute_Update_Prepared(
          p_store->p_connection,
          "UPDATE guest_usage"
          "  SET output_tokens_reserved = MAX(0, output_tokens_reserved - ?)"
          "  WHERE guest_id = ? AND window_start = ?",
          3, upd_params) < 0)
  {
    auth__rollback(p_store, AUTH_STORE_ERROR);
    pthread_mutex_unlock(&p_store->mutex);
    Dowa_Arena_Free(p_arena);
    return AUTH_STORE_ERROR;
  }

  /* Delete reservation. */
  const char *del_params[] = {request_id};
  if (Deita_Query_Execute_Update_Prepared(
          p_store->p_connection,
          "DELETE FROM guest_usage_reservations WHERE request_id = ?",
          1, del_params) < 0)
  {
    auth__rollback(p_store, AUTH_STORE_ERROR);
    pthread_mutex_unlock(&p_store->mutex);
    Dowa_Arena_Free(p_arena);
    return AUTH_STORE_ERROR;
  }

  if (Deita_Query_Execute_Update(p_store->p_connection, "COMMIT") < 0)
  {
    auth__rollback(p_store, AUTH_STORE_ERROR);
    pthread_mutex_unlock(&p_store->mutex);
    Dowa_Arena_Free(p_arena);
    return AUTH_STORE_ERROR;
  }

  pthread_mutex_unlock(&p_store->mutex);
  Dowa_Arena_Free(p_arena);
  return AUTH_STORE_OK;
}

Auth_Store_Result Auth_Store_Guest_Clear_Reservations(
    Auth_Store *p_store,
    const char *guest_id)
{
  if (!p_store || !guest_id)
    return AUTH_STORE_INVALID_ARG;

  pthread_mutex_lock(&p_store->mutex);

  if (Deita_Query_Execute_Update(p_store->p_connection, "BEGIN IMMEDIATE") < 0)
  {
    pthread_mutex_unlock(&p_store->mutex);
    return AUTH_STORE_ERROR;
  }

  /*
   * Subtract each window's reserved tokens from the usage row.
   * The correlated subquery aggregates reservations per window.
   */
  const char *upd_params[] = {guest_id, guest_id};
  if (Deita_Query_Execute_Update_Prepared(
          p_store->p_connection,
          "UPDATE guest_usage"
          "  SET output_tokens_reserved = MAX(0, output_tokens_reserved - ("
          "    SELECT COALESCE(SUM(r.output_tokens_reserved), 0)"
          "    FROM guest_usage_reservations r"
          "    WHERE r.guest_id = ? AND r.window_start = guest_usage.window_start"
          "  ))"
          "  WHERE guest_id = ?",
          2, upd_params) < 0)
  {
    auth__rollback(p_store, AUTH_STORE_ERROR);
    pthread_mutex_unlock(&p_store->mutex);
    return AUTH_STORE_ERROR;
  }

  /* Delete all reservations for this guest. */
  const char *del_params[] = {guest_id};
  if (Deita_Query_Execute_Update_Prepared(
          p_store->p_connection,
          "DELETE FROM guest_usage_reservations WHERE guest_id = ?",
          1, del_params) < 0)
  {
    auth__rollback(p_store, AUTH_STORE_ERROR);
    pthread_mutex_unlock(&p_store->mutex);
    return AUTH_STORE_ERROR;
  }

  if (Deita_Query_Execute_Update(p_store->p_connection, "COMMIT") < 0)
  {
    auth__rollback(p_store, AUTH_STORE_ERROR);
    pthread_mutex_unlock(&p_store->mutex);
    return AUTH_STORE_ERROR;
  }

  pthread_mutex_unlock(&p_store->mutex);
  return AUTH_STORE_OK;
}

Auth_Store_Result Auth_Store_Guest_Reap_Expired(
    Auth_Store *p_store,
    int64       current_unix)
{
  if (!p_store)
    return AUTH_STORE_INVALID_ARG;
  pthread_mutex_lock(&p_store->mutex);
  auth__reap_expired_reservations_locked(p_store, current_unix);
  pthread_mutex_unlock(&p_store->mutex);
  return AUTH_STORE_OK;
}

/* ------------------------------------------------------------------ */
/* Audit log (public)                                                   */
/* ------------------------------------------------------------------ */

Auth_Store_Result Auth_Store_Insert_Audit_Log(
    Auth_Store *p_store,
    const char *actor_user_id,
    const char *action,
    const char *target_user_id,
    const char *detail)
{
  if (!p_store || !action || action[0] == '\0')
    return AUTH_STORE_INVALID_ARG;

  pthread_mutex_lock(&p_store->mutex);
  boolean inserted = auth__insert_audit_log_locked(
      p_store, actor_user_id, action, target_user_id, detail);
  pthread_mutex_unlock(&p_store->mutex);
  return inserted ? AUTH_STORE_OK : AUTH_STORE_ERROR;
}

/* ------------------------------------------------------------------ */
/* Admin password reset                                                 */
/* ------------------------------------------------------------------ */

Auth_Store_Result Auth_Store_Admin_Reset_Password(
    Auth_Store *p_store,
    const char *user_id,
    const char *new_encoded_hash,
    const char *actor_user_id)
{
  if (!p_store || !user_id || !new_encoded_hash)
    return AUTH_STORE_INVALID_ARG;
  if (new_encoded_hash[0] == '\0')
    return AUTH_STORE_INVALID_ARG;

  pthread_mutex_lock(&p_store->mutex);

  if (Deita_Query_Execute_Update(
          p_store->p_connection, "BEGIN IMMEDIATE") < 0)
  {
    pthread_mutex_unlock(&p_store->mutex);
    return AUTH_STORE_ERROR;
  }

  const char *upd_params[] = {new_encoded_hash, user_id};
  int32 upd = Deita_Query_Execute_Update_Prepared(
      p_store->p_connection,
      "UPDATE users"
      "  SET password_hash = ?,"
      "      password_changed_at = strftime('%s','now'),"
      "      must_change_password = 1,"
      "      updated_at = strftime('%s','now')"
      "  WHERE id = ?",
      2, upd_params);
  if (upd <= 0)
  {
    auth__rollback(p_store, AUTH_STORE_ERROR);
    pthread_mutex_unlock(&p_store->mutex);
    return upd < 0 ? AUTH_STORE_ERROR : AUTH_STORE_NOT_FOUND;
  }

  const char *rev_params[] = {user_id};
  int32 rev = Deita_Query_Execute_Update_Prepared(
      p_store->p_connection,
      "UPDATE auth_sessions"
      "  SET revoked_at = strftime('%s','now')"
      "  WHERE user_id = ? AND revoked_at IS NULL",
      1, rev_params);
  if (rev < 0)
  {
    auth__rollback(p_store, AUTH_STORE_ERROR);
    pthread_mutex_unlock(&p_store->mutex);
    return AUTH_STORE_ERROR;
  }

  if (!auth__insert_audit_log_locked(
          p_store, actor_user_id, "admin_temp_password_reset", user_id, NULL))
  {
    auth__rollback(p_store, AUTH_STORE_ERROR);
    pthread_mutex_unlock(&p_store->mutex);
    return AUTH_STORE_ERROR;
  }

  if (Deita_Query_Execute_Update(p_store->p_connection, "COMMIT") < 0)
  {
    auth__rollback(p_store, AUTH_STORE_ERROR);
    pthread_mutex_unlock(&p_store->mutex);
    return AUTH_STORE_ERROR;
  }

  pthread_mutex_unlock(&p_store->mutex);
  return AUTH_STORE_OK;
}