宜博 / lemonboss

/**
 * User Database — SQLite 用户存储
 *
 * 复用 better-sqlite3（与 benchmark-db 共享依赖）。
 * 存储位置: data/users.sqlite
 */

const Database = require('better-sqlite3');
const path = require('path');
const fs = require('fs');
const crypto = require('crypto');

// 注意这里是**三级**上跳：从 server/services/ 落到仓库外的 ~lemonAI/data，
// 与 skill-db / benchmark-db 一致 —— 用户、skill、benchmark 是跨兄弟仓库共享的平台数据。
// 仓库内的 <repo>/data 放的是本仓库自己的租户数据（graph / companies / devloop）。
const DATA_DIR = path.resolve(__dirname, '../../../data');
// USERS_DB_PATH 覆盖口（口径同 graph-db.js 的 GRAPH_DB_PATH）。
// 没有它之前，user-db.test.js 无法隔离，每跑一次就往**共享的生产用户库**里插两行；
// 那个库里 357 个用户有 356 个是这么攒出来的测试夹具（178 次运行），真人只有 1 个。
const DB_PATH = process.env.USERS_DB_PATH || path.join(DATA_DIR, 'users.sqlite');

let db = null;

function getDb() {
  if (db) return db;

  if (!fs.existsSync(DATA_DIR)) {
    fs.mkdirSync(DATA_DIR, { recursive: true });
  }

  db = new Database(DB_PATH);
  db.pragma('journal_mode = WAL');
  db.pragma('foreign_keys = ON');

  // Create tables
  db.exec(`
    CREATE TABLE IF NOT EXISTS users (
      id            INTEGER PRIMARY KEY AUTOINCREMENT,
      email         TEXT UNIQUE,
      password_hash TEXT,
      display_name  TEXT NOT NULL,
      avatar_url    TEXT,
      provider      TEXT DEFAULT 'local',
      provider_id   TEXT,
      github_login  TEXT,
      role          TEXT DEFAULT 'user',
      api_key       TEXT UNIQUE,
      created_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
      last_login    DATETIME
    );

    CREATE UNIQUE INDEX IF NOT EXISTS idx_provider
      ON users(provider, provider_id)
      WHERE provider_id IS NOT NULL;

    CREATE INDEX IF NOT EXISTS idx_email ON users(email);
    CREATE INDEX IF NOT EXISTS idx_api_key ON users(api_key);
  `);

  return db;
}

// ─── CRUD ───

function createUser({ email, passwordHash, displayName, avatarUrl, provider = 'local', providerId = null, githubLogin = null }) {
  const d = getDb();
  const apiKey = `sk-user-${crypto.randomBytes(24).toString('hex')}`;

  const stmt = d.prepare(`
    INSERT INTO users (email, password_hash, display_name, avatar_url, provider, provider_id, github_login, api_key, last_login)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP)
  `);

  const result = stmt.run(email, passwordHash, displayName, avatarUrl, provider, providerId, githubLogin, apiKey);
  return getUserById(result.lastInsertRowid);
}

function getUserById(id) {
  const d = getDb();
  return d.prepare('SELECT * FROM users WHERE id = ?').get(id);
}

function findByEmail(email) {
  const d = getDb();
  // Match local users, or OAuth users that have set a password
  return d.prepare(
    'SELECT * FROM users WHERE email = ? AND (provider = ? OR password_hash IS NOT NULL)'
  ).get(email, 'local');
}

function findByProviderId(provider, providerId) {
  const d = getDb();
  return d.prepare('SELECT * FROM users WHERE provider = ? AND provider_id = ?').get(provider, String(providerId));
}

function findByApiKey(apiKey) {
  const d = getDb();
  return d.prepare('SELECT * FROM users WHERE api_key = ?').get(apiKey);
}

function findOrCreateOAuthUser({ provider, providerId, email, displayName, avatarUrl, githubLogin }) {
  let user = findByProviderId(provider, providerId);
  if (user) {
    // Update last login & avatar
    const d = getDb();
    d.prepare('UPDATE users SET last_login = CURRENT_TIMESTAMP, avatar_url = ?, display_name = ? WHERE id = ?')
      .run(avatarUrl || user.avatar_url, displayName || user.display_name, user.id);
    return getUserById(user.id);
  }

  // Create new user
  return createUser({
    email: email || `${provider}_${providerId}@oauth.local`,
    passwordHash: null,
    displayName: displayName || `${provider} user`,
    avatarUrl,
    provider,
    providerId: String(providerId),
    githubLogin,
  });
}

function updateLastLogin(id) {
  const d = getDb();
  d.prepare('UPDATE users SET last_login = CURRENT_TIMESTAMP WHERE id = ?').run(id);
}

function toPublicUser(user) {
  if (!user) return null;
  const { password_hash, ...publicFields } = user;
  return publicFields;
}

function getStats() {
  const d = getDb();
  const total = d.prepare('SELECT COUNT(*) as count FROM users').get().count;
  const byProvider = d.prepare('SELECT provider, COUNT(*) as count FROM users GROUP BY provider').all();
  return { total, byProvider };
}

module.exports = {
  getDb,
  createUser,
  getUserById,
  findByEmail,
  findByProviderId,
  findByApiKey,
  findOrCreateOAuthUser,
  updateLastLogin,
  toPublicUser,
  getStats,
};