宜博 / h3livestream

// SQLite-backed store for users/sessions (dev-plan-3.md T18; schema:
// dressup-dance-room-mvcp.md M.1/M.10). Separate from store.js's JSON
// files on purpose — architecture.md 1.4 flags credential storage as the
// trigger to move off JSON, other stores (characters/garments/dance-refs/
// library) are unaffected and stay exactly as they are.
//
// node:sqlite (DatabaseSync) is a Node 24 built-in (experimental but
// functional — confirmed working here) and node:crypto's scryptSync
// covers password hashing, so this needs zero new npm dependencies and no
// package.json.
const path = require("path");
const crypto = require("crypto");
const { DatabaseSync } = require("node:sqlite");

function openDb(filePath) {
  const db = new DatabaseSync(filePath);
  // WAL lets readers and a writer coexist instead of the default
  // rollback-journal mode's "any write blocks everyone" — this app now
  // legitimately has multiple processes touching data.sqlite at once (the
  // long-running server plus test runs against the same file in dev), and
  // credit_ledger's writes only get more frequent from here (T24 webhook,
  // T27 spend/refund) — worth having before contention actually bites in
  // production too, not just to unblock local test runs.
  db.exec(`PRAGMA journal_mode = WAL`);
  db.exec(`
    CREATE TABLE IF NOT EXISTS users (
      id TEXT PRIMARY KEY,
      email TEXT NOT NULL UNIQUE,
      passwordHash TEXT NOT NULL,
      emailVerified INTEGER NOT NULL DEFAULT 0,
      role TEXT NOT NULL DEFAULT 'viewer',
      oauthProviders TEXT NOT NULL DEFAULT '[]',
      nickname TEXT,
      legacyUid TEXT,
      ageConfirmed INTEGER NOT NULL DEFAULT 0,
      ageConfirmedAt INTEGER,
      creditBalance INTEGER NOT NULL DEFAULT 0,
      createdAt INTEGER NOT NULL
    );
    CREATE TABLE IF NOT EXISTS sessions (
      id TEXT PRIMARY KEY,
      userId TEXT NOT NULL REFERENCES users(id),
      kind TEXT NOT NULL,
      token TEXT NOT NULL UNIQUE,
      expiresAt INTEGER NOT NULL,
      createdAt INTEGER NOT NULL
    );
    CREATE INDEX IF NOT EXISTS idx_sessions_token ON sessions(token);
    CREATE INDEX IF NOT EXISTS idx_sessions_userId ON sessions(userId);
    CREATE TABLE IF NOT EXISTS credit_ledger (
      id TEXT PRIMARY KEY,
      userId TEXT NOT NULL REFERENCES users(id),
      delta INTEGER NOT NULL,
      reason TEXT NOT NULL,
      relatedAssetId TEXT,
      balanceAfter INTEGER NOT NULL,
      paymentRef TEXT,
      actorUserId TEXT,
      createdAt INTEGER NOT NULL
    );
    CREATE INDEX IF NOT EXISTS idx_credit_ledger_userId ON credit_ledger(userId);
    CREATE INDEX IF NOT EXISTS idx_credit_ledger_paymentRef ON credit_ledger(paymentRef);
    CREATE INDEX IF NOT EXISTS idx_credit_ledger_relatedAssetId_reason ON credit_ledger(relatedAssetId, reason);
  `);
  // actorUserId was added after credit_ledger's initial CREATE TABLE
  // (T30) — SQLite's ALTER TABLE has no "ADD COLUMN IF NOT EXISTS", and
  // CREATE TABLE IF NOT EXISTS above is a no-op against a table that
  // already exists from before this column existed. No migration
  // framework exists anywhere in this project (JSON-file stores don't
  // need one), so this is the same "idempotent guard, not a migration
  // system" spirit as the rest of the codebase.
  try {
    db.exec(`ALTER TABLE credit_ledger ADD COLUMN actorUserId TEXT`);
  } catch (err) {
    if (!/duplicate column name/.test(err.message)) throw err;
  }
  return db;
}

function rowToUser(row) {
  if (!row) return null;
  return {
    ...row,
    emailVerified: !!row.emailVerified,
    ageConfirmed: !!row.ageConfirmed,
    oauthProviders: JSON.parse(row.oauthProviders),
  };
}

// Fields a caller is allowed to patch via users.update() — deliberately a
// fixed list rather than trusting arbitrary keys, since they get spliced
// straight into a SQL UPDATE's column list.
const USER_PATCHABLE_FIELDS = [
  "passwordHash",
  "emailVerified",
  "role",
  "oauthProviders",
  "nickname",
  "legacyUid",
  "ageConfirmed",
  "ageConfirmedAt",
  "creditBalance",
];

function createUsersStore(db) {
  const insert = db.prepare(`
    INSERT INTO users (id, email, passwordHash, emailVerified, role, oauthProviders, nickname, legacyUid, ageConfirmed, ageConfirmedAt, creditBalance, createdAt)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
  `);
  const byId = db.prepare(`SELECT * FROM users WHERE id = ?`);
  const byEmail = db.prepare(`SELECT * FROM users WHERE email = ?`);

  return {
    // Only email/passwordHash are required — everything else (nickname/
    // legacyUid/ageConfirmed/ageConfirmedAt) is how T21 carries an existing
    // anonymous viewer_uid identity onto the new account at registration
    // time, so callers pass what they already have off the cookie.
    create({ email, passwordHash, nickname = null, legacyUid = null, ageConfirmed = false, ageConfirmedAt = null }) {
      const user = {
        id: crypto.randomUUID(),
        email,
        passwordHash,
        emailVerified: false,
        role: "viewer",
        oauthProviders: [],
        nickname,
        legacyUid,
        ageConfirmed,
        ageConfirmedAt,
        creditBalance: 0,
        createdAt: Date.now(),
      };
      try {
        insert.run(
          user.id,
          user.email,
          user.passwordHash,
          user.emailVerified ? 1 : 0,
          user.role,
          JSON.stringify(user.oauthProviders),
          user.nickname,
          user.legacyUid,
          user.ageConfirmed ? 1 : 0,
          user.ageConfirmedAt,
          user.creditBalance,
          user.createdAt
        );
      } catch (err) {
        if (err.code === "ERR_SQLITE_ERROR" && /UNIQUE constraint failed: users\.email/.test(err.message)) {
          const conflict = new Error("email already registered");
          conflict.code = "EMAIL_TAKEN";
          throw conflict;
        }
        throw err;
      }
      return user;
    },
    get(id) {
      return rowToUser(byId.get(id));
    },
    findByEmail(email) {
      return rowToUser(byEmail.get(email));
    },
    update(id, patch) {
      const fields = Object.keys(patch).filter((k) => USER_PATCHABLE_FIELDS.includes(k));
      if (fields.length === 0) return this.get(id);
      const setClause = fields.map((f) => `${f} = ?`).join(", ");
      const values = fields.map((f) => {
        const v = patch[f];
        if (f === "emailVerified" || f === "ageConfirmed") return v ? 1 : 0;
        if (f === "oauthProviders") return JSON.stringify(v);
        return v;
      });
      db.prepare(`UPDATE users SET ${setClause} WHERE id = ?`).run(...values, id);
      return this.get(id);
    },
  };
}

function createSessionsStore(db) {
  const insert = db.prepare(`INSERT INTO sessions (id, userId, kind, token, expiresAt, createdAt) VALUES (?, ?, ?, ?, ?, ?)`);
  const byToken = db.prepare(`SELECT * FROM sessions WHERE token = ?`);
  const del = db.prepare(`DELETE FROM sessions WHERE token = ?`);
  const delExpired = db.prepare(`DELETE FROM sessions WHERE expiresAt < ?`);

  return {
    create({ userId, kind, expiresAt, token = crypto.randomBytes(32).toString("hex") }) {
      const session = { id: crypto.randomUUID(), userId, kind, token, expiresAt, createdAt: Date.now() };
      insert.run(session.id, session.userId, session.kind, session.token, session.expiresAt, session.createdAt);
      return session;
    },
    // Lazily reaps the row if it's expired, same "no separate cron" spirit
    // as the rest of this codebase (e.g. store.js has no TTL sweep either).
    findByToken(token) {
      const row = byToken.get(token);
      if (!row) return null;
      if (row.expiresAt < Date.now()) {
        del.run(token);
        return null;
      }
      return row;
    },
    revoke(token) {
      del.run(token);
    },
    sweepExpired() {
      delExpired.run(Date.now());
    },
  };
}

function rowToLedgerEntry(row) {
  return row || null;
}

// dev-plan-4.md T23: credit_ledger (schema: dressup-dance-room-mvcp.md M.2).
// architecture.md 1.4 flags "扣积分 + 创建生成任务 + 扣减片段配额" as
// needing real atomicity — spend/refund/topup below each read-modify-write
// users.creditBalance inside a SQLite transaction so a crash mid-operation
// can't leave the ledger and the balance disagreeing. node:sqlite's
// DatabaseSync is synchronous and single-connection, so within one process
// there's no interleaving between these calls either way (no `await`
// anywhere in the critical section) — the transaction is defense against a
// mid-function throw leaving a half-applied row, not against concurrent
// callers (there are none, by construction).
function createCreditLedgerStore(db, users) {
  const insert = db.prepare(`
    INSERT INTO credit_ledger (id, userId, delta, reason, relatedAssetId, balanceAfter, paymentRef, actorUserId, createdAt)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
  `);
  const byPaymentRef = db.prepare(`SELECT * FROM credit_ledger WHERE paymentRef = ?`);
  // createdAt alone can tie (millisecond resolution, several writes in one
  // tick) — rowid as a tiebreaker keeps ties in insertion order instead of
  // an arbitrary one.
  const byUser = db.prepare(`SELECT * FROM credit_ledger WHERE userId = ? ORDER BY createdAt DESC, rowid DESC`);
  const byRelatedAssetAndReason = db.prepare(`SELECT * FROM credit_ledger WHERE relatedAssetId = ? AND reason = ?`);

  // Shared by spend/refund/topup/adjust: apply `delta` to users.creditBalance
  // and insert the matching ledger row, both inside the caller's transaction.
  function applyDelta({ userId, delta, reason, relatedAssetId = null, paymentRef = null, actorUserId = null }) {
    const user = users.get(userId);
    if (!user) throw Object.assign(new Error("no such user"), { code: "NO_SUCH_USER" });
    const balanceAfter = user.creditBalance + delta;
    if (balanceAfter < 0) throw Object.assign(new Error("insufficient credit balance"), { code: "INSUFFICIENT_BALANCE" });
    const row = {
      id: crypto.randomUUID(),
      userId,
      delta,
      reason,
      relatedAssetId,
      balanceAfter,
      paymentRef,
      actorUserId,
      createdAt: Date.now(),
    };
    insert.run(row.id, row.userId, row.delta, row.reason, row.relatedAssetId, row.balanceAfter, row.paymentRef, row.actorUserId, row.createdAt);
    db.prepare(`UPDATE users SET creditBalance = ? WHERE id = ?`).run(balanceAfter, userId);
    return row;
  }

  function inTransaction(fn) {
    db.exec("BEGIN");
    try {
      const result = fn();
      db.exec("COMMIT");
      return result;
    } catch (err) {
      db.exec("ROLLBACK");
      throw err;
    }
  }

  return {
    // Throws INSUFFICIENT_BALANCE (not a negative balance) when the user
    // can't afford `amount`.
    spend({ userId, amount, reason, relatedAssetId = null }) {
      if (amount <= 0) throw new Error("spend amount must be positive");
      return inTransaction(() => rowToLedgerEntry(applyDelta({ userId, delta: -amount, reason, relatedAssetId })));
    },
    refund({ userId, amount, reason, relatedAssetId = null }) {
      if (amount <= 0) throw new Error("refund amount must be positive");
      return inTransaction(() => rowToLedgerEntry(applyDelta({ userId, delta: amount, reason, relatedAssetId })));
    },
    // paymentRef dedup lives here (not spend/refund) because it's the only
    // reason it's ever set (Stripe's event id, T24) — Stripe guarantees
    // at-least-once webhook delivery, so a second delivery of the same
    // event must be a no-op, not a second credit. Returns the *existing*
    // row on a duplicate rather than throwing, since "already applied" is
    // success from the caller's (webhook handler's) point of view.
    topup({ userId, amount, reason = "topup", paymentRef = null }) {
      if (amount <= 0) throw new Error("topup amount must be positive");
      return inTransaction(() => {
        if (paymentRef) {
          const existing = byPaymentRef.get(paymentRef);
          if (existing) return rowToLedgerEntry(existing);
        }
        return rowToLedgerEntry(applyDelta({ userId, delta: amount, reason, paymentRef }));
      });
    },
    // T30: admin-initiated balance correction — delta can be either sign
    // (unlike spend/refund, which each only move money one direction).
    // actorUserId (the admin's own users.id) is what makes this row
    // attributable ("credit_ledger 里能查到是哪个管理员做的"); it's null
    // only when the request came in over the legacy shared ADMIN_PASSWORD
    // channel rather than a real admin account (server.js's
    // adminActorUserId()).
    adjust({ userId, delta, reason = "admin_adjust", actorUserId = null }) {
      if (!delta) throw new Error("adjust delta must be non-zero");
      return inTransaction(() => rowToLedgerEntry(applyDelta({ userId, delta, reason, actorUserId })));
    },
    listByUser(userId) {
      return byUser.all(userId);
    },
    // T27's async refund-on-generation-failure guard: ensureDanceSchedule's
    // tick can in principle observe the same "generating" -> "failed"
    // transition twice across a crash/restart (see server.js's comment at
    // the call site) — check here rather than trusting the caller to only
    // call refund() once.
    findByRelatedAssetAndReason(relatedAssetId, reason) {
      return byRelatedAssetAndReason.all(relatedAssetId, reason);
    },
  };
}

const DB_PATH = process.env.DB_PATH || path.join(__dirname, "data.sqlite");
const db = openDb(DB_PATH);
const users = createUsersStore(db);
const sessions = createSessionsStore(db);
const creditLedger = createCreditLedgerStore(db, users);

module.exports = { db, users, sessions, creditLedger, openDb, createUsersStore, createSessionsStore, createCreditLedgerStore };