// @pdsjs/storage-sqlite - SQLite storage adapter for Node.js

import { APP_PASSWORD_SCHEMA_SQL } from '@pdsjs/core/app-password';
import { SESSION_SCHEMA_SQL } from '@pdsjs/core/auth';
import {
  BLOB_FACETS_APPS_SQL,
  BLOB_FACETS_KINDS_SQL,
  BLOB_FACETS_TOTAL_SQL,
  BLOB_FACETS_UNUSED_SQL,
  blobListCursor,
  buildBlobListQuery,
} from '@pdsjs/core/blob-sql';
import { PASSKEY_SCHEMA_SQL } from '@pdsjs/core/webauthn';

export { createSpaceStorage } from './space.js';

/** @typedef {{name: string, privileged: number, created_at: string}} AppPasswordRow */

/**
 * @param {AppPasswordRow} row
 * @returns {import('@pdsjs/core/ports').AppPasswordRecord}
 */
function toAppPassword(row) {
  return {
    name: row.name,
    privileged: row.privileged === 1,
    createdAt: row.created_at,
  };
}

// ============================================================================
// Row type definitions for SQLite queries
// ============================================================================

/** @typedef {{cid: string, value: Uint8Array}} RecordResult */
/** @typedef {{uri: string, cid: string, rkey: string, value: Uint8Array|Buffer}} RecordRow */
/** @typedef {{collection: string, rkey: string, cid: string}} RecordKeyRow */
/** @typedef {{data: Uint8Array}} BlockRow */
/** @typedef {{seq: number, cid: string, rev: string}} CommitRow */
/** @typedef {{seq: number, did: string, commit_cid: string, evt: Uint8Array}} EventRow */
/** @typedef {{mime_type: string, size: number, created_at: number}} BlobMetaRow */
/** @typedef {{cid: string, mime_type: string, size: number, created_at: number, uris: string|null, time_ms: number, time_known: number}} BlobDetailRow */
/** @typedef {{count: number, bytes: number}} BlobBucketRow */
/** @typedef {{kind: import('@pdsjs/core/ports').BlobKind, count: number, bytes: number}} BlobKindRow */
/** @typedef {{authority: string, count: number, bytes: number}} BlobAppRow */
/** @typedef {{cid: string}} CidRow */
/** @typedef {{value: string}} MetadataStringRow */
/** @typedef {{value: Buffer}} MetadataBlobRow */
/** @typedef {{data: string, expires_at: number}} OAuthRequestRow */
/** @typedef {{data: string}} OAuthTokenRow */
/** @typedef {{credential_id: string, did: string, name: string, public_key: string, algorithm: number, sign_count: number, created_at: string, last_used_at: string|null}} PasskeyRow */

/** @typedef {{jti: string, did: string, label: string, scope: string, user_agent: string|null, created_at: string, refreshed_at: string|null, expires_at: number|null, next_jti: string|null}} SessionRow */

/**
 * @param {SessionRow} row
 * @returns {import('@pdsjs/core/ports').SessionRecord}
 */
function toSessionRecord(row) {
  return {
    jti: row.jti,
    did: row.did,
    label: row.label,
    scope: row.scope,
    userAgent: row.user_agent,
    createdAt: row.created_at,
    refreshedAt: row.refreshed_at,
    expiresAt: row.expires_at ?? undefined,
    nextJti: row.next_jti ?? null,
  };
}

/**
 * @param {PasskeyRow} row
 * @returns {import('@pdsjs/core/ports').PasskeyRecord}
 */
function toPasskeyRecord(row) {
  return {
    credentialId: row.credential_id,
    did: row.did,
    name: row.name,
    publicKey: row.public_key,
    algorithm: row.algorithm,
    signCount: row.sign_count,
    createdAt: row.created_at,
    lastUsedAt: row.last_used_at,
  };
}
/** @typedef {{token_id: string, data: string}} OAuthTokenListRow */

// ============================================================================
// Storage adapter
// ============================================================================

/**
 * Create storage adapter for better-sqlite3 (Node.js)
 * @param {import('./driver.js').SQLiteDatabase} db
 * @returns {import('@pdsjs/core/ports').ActorStoragePort}
 */
export function createActorStorage(db) {
  // Initialize schema
  db.exec(`
    CREATE TABLE IF NOT EXISTS metadata (
      key TEXT PRIMARY KEY,
      value BLOB
    );

    CREATE TABLE IF NOT EXISTS blocks (
      cid TEXT PRIMARY KEY,
      data BLOB
    );

    CREATE TABLE IF NOT EXISTS records (
      uri TEXT PRIMARY KEY,
      cid TEXT,
      collection TEXT,
      rkey TEXT,
      value BLOB
    );
    CREATE INDEX IF NOT EXISTS idx_records_collection ON records(collection, rkey);
    CREATE INDEX IF NOT EXISTS idx_records_cid ON records(cid);

    CREATE TABLE IF NOT EXISTS commits (
      seq INTEGER PRIMARY KEY,
      cid TEXT,
      rev TEXT,
      prev TEXT
    );

    CREATE TABLE IF NOT EXISTS seq_events (
      seq INTEGER PRIMARY KEY,
      did TEXT,
      commit_cid TEXT,
      evt BLOB
    );

    CREATE TABLE IF NOT EXISTS blobs (
      cid TEXT PRIMARY KEY,
      mime_type TEXT,
      size INTEGER,
      created_at INTEGER
    );

    CREATE TABLE IF NOT EXISTS record_blobs (
      blob_cid TEXT,
      record_uri TEXT,
      record_time INTEGER,
      PRIMARY KEY (blob_cid, record_uri)
    );
  `);

  // Databases created before record_time existed gain the column in place.
  try {
    db.exec('ALTER TABLE record_blobs ADD COLUMN record_time INTEGER');
  } catch {
    // Already present
  }

  const stmts = {
    getRecord: db.prepare('SELECT cid, value FROM records WHERE uri = ?'),
    listRecords: db.prepare(
      'SELECT uri, cid, rkey, value FROM records WHERE collection = ? ORDER BY rkey LIMIT ?',
    ),
    listRecordsAfter: db.prepare(
      'SELECT uri, cid, rkey, value FROM records WHERE collection = ? AND rkey > ? ORDER BY rkey LIMIT ?',
    ),
    listRecordsDesc: db.prepare(
      'SELECT uri, cid, rkey, value FROM records WHERE collection = ? ORDER BY rkey DESC LIMIT ?',
    ),
    listRecordsBefore: db.prepare(
      'SELECT uri, cid, rkey, value FROM records WHERE collection = ? AND rkey < ? ORDER BY rkey DESC LIMIT ?',
    ),
    listAllRecords: db.prepare(
      "SELECT collection, rkey, cid FROM records ORDER BY (collection || '/' || rkey)",
    ),
    putRecord: db.prepare(
      'INSERT OR REPLACE INTO records (uri, cid, collection, rkey, value) VALUES (?, ?, ?, ?, ?)',
    ),
    deleteRecord: db.prepare('DELETE FROM records WHERE uri = ?'),
    deleteRecordBlobs: db.prepare(
      'DELETE FROM record_blobs WHERE record_uri = ?',
    ),
    getBlock: db.prepare('SELECT data FROM blocks WHERE cid = ?'),
    putBlock: db.prepare(
      'INSERT OR IGNORE INTO blocks (cid, data) VALUES (?, ?)',
    ),
    deleteBlock: db.prepare('DELETE FROM blocks WHERE cid = ?'),
    listBlocks: db.prepare('SELECT cid, data FROM blocks ORDER BY cid LIMIT ?'),
    listBlocksAfter: db.prepare(
      'SELECT cid, data FROM blocks WHERE cid > ? ORDER BY cid LIMIT ?',
    ),
    recordWithCid: db.prepare('SELECT 1 FROM records WHERE cid = ? LIMIT 1'),
    getLatestCommit: db.prepare(
      'SELECT seq, cid, rev FROM commits ORDER BY seq DESC LIMIT 1',
    ),
    putCommit: db.prepare(
      'INSERT INTO commits (seq, cid, rev, prev) VALUES (?, ?, ?, ?)',
    ),
    getEvents: db.prepare(
      'SELECT seq, did, commit_cid, evt FROM seq_events ORDER BY seq LIMIT ?',
    ),
    getEventsAfter: db.prepare(
      'SELECT seq, did, commit_cid, evt FROM seq_events WHERE seq > ? ORDER BY seq LIMIT ?',
    ),
    getEventsBefore: db.prepare(
      'SELECT seq, evt FROM seq_events WHERE seq < ? ORDER BY seq DESC LIMIT ?',
    ),
    putEvent: db.prepare(
      'INSERT INTO seq_events (seq, did, commit_cid, evt) VALUES (?, ?, ?, ?)',
    ),
    getBlob: db.prepare(
      'SELECT mime_type, size, created_at FROM blobs WHERE cid = ?',
    ),
    putBlob: db.prepare(
      'INSERT OR REPLACE INTO blobs (cid, mime_type, size, created_at) VALUES (?, ?, ?, ?)',
    ),
    deleteBlob: db.prepare('DELETE FROM blobs WHERE cid = ?'),
    listBlobs: db.prepare('SELECT cid FROM blobs ORDER BY cid LIMIT ?'),
    listBlobsAfter: db.prepare(
      'SELECT cid FROM blobs WHERE cid > ? ORDER BY cid LIMIT ?',
    ),
    blobFacetsTotal: db.prepare(BLOB_FACETS_TOTAL_SQL),
    blobFacetsUnused: db.prepare(BLOB_FACETS_UNUSED_SQL),
    blobFacetsKinds: db.prepare(BLOB_FACETS_KINDS_SQL),
    blobFacetsApps: db.prepare(BLOB_FACETS_APPS_SQL),
    getOrphanedBlobs: db.prepare(
      'SELECT b.cid FROM blobs b LEFT JOIN record_blobs rb ON b.cid = rb.blob_cid WHERE rb.blob_cid IS NULL AND b.created_at < ?',
    ),
    linkBlobToRecord: db.prepare(
      'INSERT OR REPLACE INTO record_blobs (blob_cid, record_uri, record_time) VALUES (?, ?, ?)',
    ),
    unlinkBlobsFromRecord: db.prepare(
      'DELETE FROM record_blobs WHERE record_uri = ?',
    ),
    listBlobRecordUris: db.prepare(
      'SELECT record_uri FROM record_blobs WHERE blob_cid = ?',
    ),
    getMetadata: db.prepare('SELECT value FROM metadata WHERE key = ?'),
    setMetadata: db.prepare(
      'INSERT OR REPLACE INTO metadata (key, value) VALUES (?, ?)',
    ),
  };

  return {
    async getRecord(uri) {
      const row = /** @type {RecordResult|undefined} */ (
        stmts.getRecord.get(uri)
      );
      return row ? { cid: row.cid, value: row.value } : null;
    },

    async listRecords(collection, cursor, limit, reverse = false) {
      const stmt = reverse
        ? cursor
          ? stmts.listRecordsBefore
          : stmts.listRecordsDesc
        : cursor
          ? stmts.listRecordsAfter
          : stmts.listRecords;
      const rows = /** @type {RecordRow[]} */ (
        cursor
          ? stmt.all(collection, cursor, limit)
          : stmt.all(collection, limit)
      );
      return {
        records: rows.map((r) => ({ uri: r.uri, cid: r.cid, value: r.value })),
        cursor: rows.length === limit ? rows[rows.length - 1].rkey : null,
      };
    },

    async listAllRecords() {
      const rows = /** @type {RecordKeyRow[]} */ (stmts.listAllRecords.all());
      return rows.map((r) => ({
        key: `${r.collection}/${r.rkey}`,
        cid: r.cid,
      }));
    },

    async putRecord(uri, cid, collection, rkey, value) {
      stmts.putRecord.run(uri, cid, collection, rkey, value);
    },

    async deleteRecord(uri) {
      stmts.deleteRecordBlobs.run(uri);
      stmts.deleteRecord.run(uri);
    },

    async getBlock(cid) {
      const row = /** @type {BlockRow|undefined} */ (stmts.getBlock.get(cid));
      return row ? row.data : null;
    },

    async putBlock(cid, data) {
      stmts.putBlock.run(cid, data);
    },

    async deleteBlocks(cids) {
      for (const cid of cids) stmts.deleteBlock.run(cid);
    },

    async listBlocks(cursor, limit) {
      const rows = /** @type {Array<{cid: string, data: Uint8Array}>} */ (
        cursor
          ? stmts.listBlocksAfter.all(cursor, limit)
          : stmts.listBlocks.all(limit)
      );
      const last = rows.length === limit ? rows[limit - 1].cid : null;
      return { blocks: rows, cursor: last };
    },

    async recordCidsAmong(cids) {
      return cids.filter((cid) => stmts.recordWithCid.get(cid));
    },

    async getBlocksCollected() {
      const row = /** @type {MetadataStringRow|undefined} */ (
        stmts.getMetadata.get('blocksCollected')
      );
      return row?.value === '1';
    },

    async setBlocksCollected(collected) {
      stmts.setMetadata.run('blocksCollected', collected ? '1' : '0');
    },

    async getLatestCommit() {
      const row = /** @type {CommitRow|undefined} */ (
        stmts.getLatestCommit.get()
      );
      return row || null;
    },

    async putCommit(seq, cid, rev, prev) {
      stmts.putCommit.run(seq, cid, rev, prev);
    },

    async getEvents(cursor, limit) {
      const rows = /** @type {EventRow[]} */ (
        cursor
          ? stmts.getEventsAfter.all(cursor, limit)
          : stmts.getEvents.all(limit)
      );
      return {
        events: rows.map((r) => ({
          seq: r.seq,
          commit_cid: r.commit_cid,
          evt: r.evt,
        })),
        cursor: rows.length > 0 ? rows[rows.length - 1].seq : cursor,
      };
    },

    async putEvent(seq, did, commitCid, evt) {
      stmts.putEvent.run(seq, did, commitCid, evt);
    },

    async getEventsBefore(before, limit) {
      const rows = /** @type {{seq: number, evt: Uint8Array}[]} */ (
        stmts.getEventsBefore.all(before, limit)
      );
      return rows.map((r) => ({ seq: r.seq, evt: r.evt }));
    },

    async getLatestSeq() {
      // Max across both sequenced tables: a crash between putCommit and
      // putEvent leaves a commit whose seq the events table never saw, and
      // counting only events would hand that seq out again forever.
      const row = /** @type {{seq: number|null}|undefined} */ (
        db
          .prepare(
            'SELECT MAX(s) as seq FROM (SELECT MAX(seq) as s FROM seq_events UNION ALL SELECT MAX(seq) as s FROM commits)',
          )
          .get()
      );
      return row?.seq ?? 0;
    },

    async getBlob(cid) {
      const row = /** @type {BlobMetaRow|undefined} */ (stmts.getBlob.get(cid));
      return row
        ? { mimeType: row.mime_type, size: row.size, createdAt: row.created_at }
        : null;
    },

    async putBlob(cid, mimeType, size) {
      stmts.putBlob.run(cid, mimeType, size, Date.now());
    },

    async deleteBlob(cid) {
      stmts.deleteBlob.run(cid);
    },

    async listBlobs(cursor, limit) {
      const rows = /** @type {CidRow[]} */ (
        cursor
          ? stmts.listBlobsAfter.all(cursor, limit)
          : stmts.listBlobs.all(limit)
      );
      return {
        cids: rows.map((r) => r.cid),
        cursor: rows.length === limit ? rows[rows.length - 1].cid : null,
      };
    },

    async listBlobDetails(cursor, limit, options = {}) {
      const { sql, params } = buildBlobListQuery(cursor, limit, options);
      const rows = /** @type {BlobDetailRow[]} */ (
        db.prepare(sql).all(...params)
      );
      const blobs = rows.map((r) => ({
        cid: r.cid,
        mimeType: r.mime_type,
        size: r.size,
        createdAt: r.created_at,
        time: r.time_ms,
        timeFromRecord: r.time_known === 1,
        records: r.uris ? r.uris.split(',') : [],
      }));
      return {
        blobs,
        cursor:
          blobs.length === limit
            ? blobListCursor(blobs[blobs.length - 1], options)
            : null,
      };
    },

    async getBlobFacets() {
      const total = /** @type {BlobBucketRow} */ (stmts.blobFacetsTotal.get());
      const unused = /** @type {BlobBucketRow} */ (
        stmts.blobFacetsUnused.get()
      );
      const kinds = /** @type {BlobKindRow[]} */ (stmts.blobFacetsKinds.all());
      const apps = /** @type {BlobAppRow[]} */ (stmts.blobFacetsApps.all());
      return { total, unused, kinds, apps };
    },

    async getOrphanedBlobs(olderThan) {
      const rows = /** @type {CidRow[]} */ (
        stmts.getOrphanedBlobs.all(olderThan)
      );
      return rows.map((r) => r.cid);
    },

    async linkBlobToRecord(blobCid, recordUri, recordTime = null) {
      stmts.linkBlobToRecord.run(blobCid, recordUri, recordTime);
    },

    async unlinkBlobsFromRecord(recordUri) {
      stmts.unlinkBlobsFromRecord.run(recordUri);
    },

    async listBlobRecordUris(blobCid) {
      const rows = /** @type {{record_uri: string}[]} */ (
        stmts.listBlobRecordUris.all(blobCid)
      );
      return rows.map((row) => row.record_uri);
    },

    async getDid() {
      const row = /** @type {MetadataStringRow|undefined} */ (
        stmts.getMetadata.get('did')
      );
      return row ? row.value : null;
    },

    async setDid(did) {
      stmts.setMetadata.run('did', did);
    },

    async getPrivateKey() {
      const row = /** @type {MetadataBlobRow|undefined} */ (
        stmts.getMetadata.get('privateKey')
      );
      return row ? new Uint8Array(row.value) : null;
    },

    async setPrivateKey(key) {
      stmts.setMetadata.run('privateKey', key);
    },

    async getKemKey() {
      const row = /** @type {MetadataBlobRow|undefined} */ (
        stmts.getMetadata.get('kemKey')
      );
      return row ? new Uint8Array(row.value) : null;
    },

    async setKemKey(key) {
      stmts.setMetadata.run('kemKey', key);
    },

    async getHandle() {
      const row = /** @type {MetadataStringRow|undefined} */ (
        stmts.getMetadata.get('handle')
      );
      return row ? row.value : null;
    },

    async getEmail() {
      const row = /** @type {MetadataStringRow|undefined} */ (
        stmts.getMetadata.get('email')
      );
      if (!row) return null;
      try {
        return JSON.parse(row.value);
      } catch {
        return null;
      }
    },

    async setEmail(state) {
      stmts.setMetadata.run('email', JSON.stringify(state));
    },

    async getAccountPassword() {
      const row = /** @type {MetadataStringRow|undefined} */ (
        stmts.getMetadata.get('accountPassword')
      );
      if (!row) return null;
      try {
        return JSON.parse(row.value);
      } catch {
        return null;
      }
    },

    async setAccountPassword(state) {
      stmts.setMetadata.run('accountPassword', JSON.stringify(state));
    },

    async getPlcToken() {
      const row = /** @type {MetadataStringRow|undefined} */ (
        stmts.getMetadata.get('plcToken')
      );
      if (!row) return null;
      try {
        return JSON.parse(row.value);
      } catch {
        return null;
      }
    },

    async setPlcToken(token) {
      stmts.setMetadata.run('plcToken', JSON.stringify(token));
    },

    async getAccountStatus() {
      const row = /** @type {MetadataStringRow|undefined} */ (
        stmts.getMetadata.get('accountStatus')
      );
      return row ? row.value : null;
    },

    async setAccountStatus(status) {
      stmts.setMetadata.run('accountStatus', status);
    },

    async setHandle(handle) {
      stmts.setMetadata.run('handle', handle);
    },

    // Preferences storage
    async getPreferences() {
      const row = /** @type {MetadataStringRow|undefined} */ (
        stmts.getMetadata.get('preferences')
      );
      return row ? JSON.parse(row.value) : [];
    },

    async setPreferences(preferences) {
      stmts.setMetadata.run('preferences', JSON.stringify(preferences));
    },

    // Backup settings and run history, one JSON document
    async getBackupState() {
      const row = /** @type {MetadataStringRow|undefined} */ (
        stmts.getMetadata.get('backupState')
      );
      if (!row) return null;
      try {
        return JSON.parse(row.value);
      } catch {
        return null;
      }
    },

    async setBackupState(state) {
      stmts.setMetadata.run('backupState', JSON.stringify(state));
    },

    // The account app's own preferences, one JSON document
    async getAccountUiState() {
      const row = /** @type {MetadataStringRow|undefined} */ (
        stmts.getMetadata.get('accountUiState')
      );
      if (!row) return null;
      try {
        return JSON.parse(row.value);
      } catch {
        return null;
      }
    },

    async setAccountUiState(state) {
      stmts.setMetadata.run('accountUiState', JSON.stringify(state));
    },
  };
}

// ============================================================================
// Shared storage adapter for Node.js
// ============================================================================

/** @typedef {{did: string, handle: string|null, created_at: number}} ActorRow */

/**
 * Create shared storage adapter for better-sqlite3 (Node.js)
 * @param {import('./driver.js').SQLiteDatabase} db
 * @returns {import('@pdsjs/core/ports').SharedStoragePort}
 */
export function createSharedStorage(db) {
  // Initialize schema
  db.exec(`
    CREATE TABLE IF NOT EXISTS actors (
      did TEXT PRIMARY KEY,
      handle TEXT,
      created_at INTEGER NOT NULL
    );
    CREATE UNIQUE INDEX IF NOT EXISTS actors_handle_lower_idx ON actors(lower(handle));

    CREATE TABLE IF NOT EXISTS oauth_requests (
      request_uri TEXT PRIMARY KEY,
      data TEXT,
      expires_at INTEGER
    );
    CREATE INDEX IF NOT EXISTS oauth_requests_expires_idx ON oauth_requests(expires_at);

    CREATE TABLE IF NOT EXISTS oauth_tokens (
      token_id TEXT PRIMARY KEY,
      did TEXT NOT NULL,
      data TEXT
    );
    CREATE INDEX IF NOT EXISTS oauth_tokens_did_idx ON oauth_tokens(did);

    CREATE TABLE IF NOT EXISTS dpop_jtis (
      jti TEXT PRIMARY KEY,
      expires_at INTEGER
    );
    CREATE INDEX IF NOT EXISTS dpop_jtis_expires_idx ON dpop_jtis(expires_at);
  `);
  db.exec(APP_PASSWORD_SCHEMA_SQL);
  db.exec(PASSKEY_SCHEMA_SQL);
  db.exec(SESSION_SCHEMA_SQL);
  // Databases created before sessions carried an expiry lack the column; add
  // it once. SQLite has no ADD COLUMN IF NOT EXISTS, so a re-run just throws.
  try {
    db.exec('ALTER TABLE sessions ADD COLUMN expires_at INTEGER');
  } catch {
    // Column already present.
  }
  // Likewise for the successor a rotation names when it spends a token.
  try {
    db.exec('ALTER TABLE sessions ADD COLUMN next_jti TEXT');
  } catch {
    // Column already present.
  }

  const stmts = {
    getActor: db.prepare(
      'SELECT did, handle, created_at FROM actors WHERE did = ?',
    ),
    resolveHandle: db.prepare(
      'SELECT did FROM actors WHERE lower(handle) = lower(?)',
    ),
    putActor: db.prepare(
      'INSERT OR REPLACE INTO actors (did, handle, created_at) VALUES (?, ?, ?)',
    ),
    deleteActor: db.prepare('DELETE FROM actors WHERE did = ?'),

    getOAuthRequest: db.prepare(
      'SELECT data, expires_at FROM oauth_requests WHERE request_uri = ?',
    ),
    putOAuthRequest: db.prepare(
      'INSERT OR REPLACE INTO oauth_requests (request_uri, data, expires_at) VALUES (?, ?, ?)',
    ),
    deleteOAuthRequest: db.prepare(
      'DELETE FROM oauth_requests WHERE request_uri = ?',
    ),

    getOAuthToken: db.prepare(
      'SELECT data FROM oauth_tokens WHERE token_id = ?',
    ),
    putOAuthToken: db.prepare(
      'INSERT OR REPLACE INTO oauth_tokens (token_id, did, data) VALUES (?, ?, ?)',
    ),
    deleteOAuthToken: db.prepare('DELETE FROM oauth_tokens WHERE token_id = ?'),
    listOAuthTokensByDid: db.prepare(
      'SELECT token_id, data FROM oauth_tokens WHERE did = ?',
    ),

    storeDpopJti: db.prepare(
      'INSERT OR IGNORE INTO dpop_jtis (jti, expires_at) VALUES (?, ?)',
    ),
    cleanupExpiredDpopJtis: db.prepare(
      'DELETE FROM dpop_jtis WHERE expires_at < ?',
    ),

    putAppPassword: db.prepare(
      'INSERT OR IGNORE INTO app_passwords (did, name, hash, privileged, created_at) VALUES (?, ?, ?, ?, ?)',
    ),
    getAppPasswordByHash: db.prepare(
      'SELECT name, privileged, created_at FROM app_passwords WHERE did = ? AND hash = ?',
    ),
    listAppPasswords: db.prepare(
      'SELECT name, privileged, created_at FROM app_passwords WHERE did = ? ORDER BY created_at',
    ),
    putSession: db.prepare(
      'INSERT OR REPLACE INTO sessions (jti, did, label, scope, user_agent, created_at, refreshed_at, expires_at, next_jti) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)',
    ),
    getSession: db.prepare('SELECT * FROM sessions WHERE jti = ?'),
    listSessions: db.prepare(
      'SELECT * FROM sessions WHERE did = ? ORDER BY created_at',
    ),
    deleteSession: db.prepare('DELETE FROM sessions WHERE jti = ?'),
    // Conditional on the successor, so that of two rotations racing the same
    // token only the first takes effect. Statements run to completion here, so
    // the test and the write cannot be separated.
    spendSession: db.prepare(
      'UPDATE sessions SET expires_at = ?, next_jti = ? WHERE jti = ? AND (next_jti IS NULL OR next_jti = ?)',
    ),

    putPasskey: db.prepare(
      'INSERT OR IGNORE INTO passkeys (credential_id, did, name, public_key, algorithm, sign_count, created_at, last_used_at) VALUES (?, ?, ?, ?, ?, 0, ?, NULL)',
    ),
    getPasskey: db.prepare('SELECT * FROM passkeys WHERE credential_id = ?'),
    listPasskeys: db.prepare(
      'SELECT * FROM passkeys WHERE did = ? ORDER BY created_at',
    ),
    deletePasskey: db.prepare(
      'DELETE FROM passkeys WHERE did = ? AND credential_id = ?',
    ),
    touchPasskey: db.prepare(
      'UPDATE passkeys SET sign_count = ?, last_used_at = ? WHERE credential_id = ?',
    ),

    deleteAppPassword: db.prepare(
      'DELETE FROM app_passwords WHERE did = ? AND name = ?',
    ),
  };

  return {
    async getActor(did) {
      const row = /** @type {ActorRow|undefined} */ (stmts.getActor.get(did));
      return row
        ? { did: row.did, handle: row.handle, createdAt: row.created_at }
        : null;
    },

    async resolveHandle(handle) {
      const row = /** @type {{did: string}|undefined} */ (
        stmts.resolveHandle.get(handle)
      );
      return row ? row.did : null;
    },

    async putActor(did, handle, createdAt) {
      stmts.putActor.run(did, handle, createdAt);
    },

    async deleteActor(did) {
      stmts.deleteActor.run(did);
    },

    async getOAuthRequest(requestUri) {
      const row = /** @type {OAuthRequestRow|undefined} */ (
        stmts.getOAuthRequest.get(requestUri)
      );
      if (!row) return null;
      if (row.expires_at < Date.now()) {
        stmts.deleteOAuthRequest.run(requestUri);
        return null;
      }
      try {
        return JSON.parse(row.data);
      } catch {
        stmts.deleteOAuthRequest.run(requestUri);
        return null;
      }
    },

    async putOAuthRequest(requestUri, data, expiresAt) {
      stmts.putOAuthRequest.run(requestUri, JSON.stringify(data), expiresAt);
    },

    async deleteOAuthRequest(requestUri) {
      stmts.deleteOAuthRequest.run(requestUri);
    },

    async getOAuthToken(tokenId) {
      const row = /** @type {OAuthTokenRow|undefined} */ (
        stmts.getOAuthToken.get(tokenId)
      );
      if (!row) return null;
      try {
        return JSON.parse(row.data);
      } catch {
        stmts.deleteOAuthToken.run(tokenId);
        return null;
      }
    },

    async putOAuthToken(tokenId, data) {
      const tokenData = /** @type {{did: string}} */ (data);
      stmts.putOAuthToken.run(tokenId, tokenData.did, JSON.stringify(data));
    },

    async deleteOAuthToken(tokenId) {
      stmts.deleteOAuthToken.run(tokenId);
    },

    async putSession(session) {
      stmts.putSession.run(
        session.jti,
        session.did,
        session.label,
        session.scope,
        session.userAgent,
        session.createdAt,
        session.refreshedAt,
        session.expiresAt ?? null,
        session.nextJti ?? null,
      );
    },

    async getSession(jti) {
      const row = /** @type {SessionRow|undefined} */ (
        stmts.getSession.get(jti)
      );
      return row ? toSessionRecord(row) : null;
    },

    async listSessions(did) {
      const rows = /** @type {SessionRow[]} */ (stmts.listSessions.all(did));
      return rows.map(toSessionRecord);
    },

    async deleteSession(jti) {
      return stmts.deleteSession.run(jti).changes > 0;
    },

    async spendSession(jti, expiresAt, nextJti) {
      return (
        stmts.spendSession.run(expiresAt, nextJti, jti, nextJti).changes > 0
      );
    },

    async putPasskey(record) {
      const result = stmts.putPasskey.run(
        record.credentialId,
        record.did,
        record.name,
        record.publicKey,
        record.algorithm,
        record.createdAt,
      );
      return result.changes > 0;
    },

    async getPasskey(credentialId) {
      const row = /** @type {PasskeyRow|undefined} */ (
        stmts.getPasskey.get(credentialId)
      );
      return row ? toPasskeyRecord(row) : null;
    },

    async listPasskeys(did) {
      const rows = /** @type {PasskeyRow[]} */ (stmts.listPasskeys.all(did));
      return rows.map(toPasskeyRecord);
    },

    async deletePasskey(did, credentialId) {
      const result = stmts.deletePasskey.run(did, credentialId);
      return result.changes > 0;
    },

    async touchPasskey(credentialId, signCount, lastUsedAt) {
      stmts.touchPasskey.run(signCount, lastUsedAt, credentialId);
    },

    async listOAuthTokensByDid(did) {
      const rows = /** @type {OAuthTokenListRow[]} */ (
        stmts.listOAuthTokensByDid.all(did)
      );
      return rows
        .map((r) => {
          try {
            // The row key travels with the data so a caller can revoke a
            // single session without a second lookup.
            return { ...JSON.parse(r.data), tokenId: r.token_id };
          } catch {
            return null;
          }
        })
        .filter(Boolean);
    },

    async checkAndStoreDpopJti(jti, expiresAt) {
      const result = stmts.storeDpopJti.run(jti, expiresAt);
      return result.changes > 0;
    },

    async cleanupExpiredDpopJtis() {
      stmts.cleanupExpiredDpopJtis.run(Date.now());
    },

    async putAppPassword(did, name, hash, privileged, createdAt) {
      // INSERT OR IGNORE rather than REPLACE: reusing a name must not
      // silently invalidate the password already issued under it.
      const result = stmts.putAppPassword.run(
        did,
        name,
        hash,
        privileged ? 1 : 0,
        createdAt,
      );
      return Number(result.changes) > 0;
    },

    async getAppPasswordByHash(did, hash) {
      const row = /** @type {AppPasswordRow|undefined} */ (
        stmts.getAppPasswordByHash.get(did, hash)
      );
      return row ? toAppPassword(row) : null;
    },

    async listAppPasswords(did) {
      const rows = /** @type {AppPasswordRow[]} */ (
        stmts.listAppPasswords.all(did)
      );
      return rows.map(toAppPassword);
    },

    async deleteAppPassword(did, name) {
      const result = stmts.deleteAppPassword.run(did, name);
      return Number(result.changes) > 0;
    },
  };
}