scripts/seed-admin.mjs

/**
 * Seeds the admin user into the SQLite database.
 * Run once after first startup:
 *   node scripts/seed-admin.mjs
 *
 * Uses only node:sqlite (built-in) — no external dependencies.
 * Runs pending migrations before inserting so it is safe to call
 * before or after the server first starts.
 */
import { DatabaseSync } from "node:sqlite";
import fs from "fs";
import path from "path";
import { fileURLToPath } from "url";
import { createHash } from "crypto";

const __dirname = path.dirname(fileURLToPath(import.meta.url));

const DB_PATH =
  process.env.DB_PATH ||
  path.join(__dirname, "../artifacts/api-server/raqim.db");

const MIGRATIONS_DIR =
  process.env.MIGRATIONS_DIR ||
  path.join(__dirname, "../lib/db/drizzle");

const ADMIN_EMAIL = (process.env.ADMIN_EMAIL || "admin@raqim.app").toLowerCase();
const ADMIN_NAME  = process.env.ADMIN_NAME  || "Admin";

// bcrypt hash for "Admin1234!" cost=12
// Override via ADMIN_PASSWORD_HASH env var.
const ADMIN_PASSWORD_HASH =
  process.env.ADMIN_PASSWORD_HASH ||
  "$2b$12$6PwSkmxfTjxyQYCXBcKvA.CqVVHL.b.Kz29McI5txRPlwutiMp4ZS";

console.log("[seed] DB:", DB_PATH);
console.log("[seed] Email:", ADMIN_EMAIL);

const db = new DatabaseSync(DB_PATH);
db.exec("PRAGMA journal_mode = WAL");
db.exec("PRAGMA foreign_keys = ON");

// ── Run pending migrations so tables exist ────────────────────────────────
db.exec(`CREATE TABLE IF NOT EXISTS __drizzle_migrations (
  id        INTEGER PRIMARY KEY AUTOINCREMENT,
  hash      TEXT    NOT NULL UNIQUE,
  applied_at INTEGER NOT NULL DEFAULT (unixepoch() * 1000)
)`);

if (fs.existsSync(MIGRATIONS_DIR)) {
  const sqlFiles = fs
    .readdirSync(MIGRATIONS_DIR)
    .filter((f) => f.endsWith(".sql"))
    .sort();

  for (const file of sqlFiles) {
    const hash = file.replace(".sql", "");
    const already = db
      .prepare("SELECT 1 FROM __drizzle_migrations WHERE hash = ?")
      .get(hash);
    if (already) continue;

    const sql = fs.readFileSync(path.join(MIGRATIONS_DIR, file), "utf-8");
    const stmts = sql
      .split("--> statement-breakpoint")
      .map((s) => s.trim())
      .filter(Boolean);

    for (const stmt of stmts) db.exec(stmt);

    db.prepare("INSERT INTO __drizzle_migrations (hash) VALUES (?)").run(hash);
    console.log("[seed] Applied migration:", file);
  }
}

/**
 * Derives a stable UUID v4-shaped ID from the admin email.
 * Same email → same UUID every time, across container restarts.
 * This prevents FK violations when the DB is wiped and re-seeded.
 */
function emailToUUID(email) {
  const h = createHash("sha256").update("raqim:admin:" + email).digest("hex");
  return [
    h.slice(0, 8),
    h.slice(8, 12),
    "4" + h.slice(13, 16),
    (parseInt(h[16], 16) & 0x3 | 0x8).toString(16) + h.slice(17, 20),
    h.slice(20, 32),
  ].join("-");
}

const ADMIN_ID = process.env.ADMIN_ID || emailToUUID(ADMIN_EMAIL);
console.log("[seed] Admin UUID:", ADMIN_ID);

// ── Insert admin user ─────────────────────────────────────────────────────
const existing = db
  .prepare("SELECT id FROM users WHERE email = ?")
  .get(ADMIN_EMAIL);

if (existing) {
  // If the admin exists but with a DIFFERENT id (old random UUID),
  // update it to the deterministic one so future tokens work correctly.
  if (existing.id !== ADMIN_ID) {
    console.log("[seed] Updating admin UUID from", existing.id, "→", ADMIN_ID);
    db.prepare("UPDATE users SET id = ?, updated_at = unixepoch() * 1000 WHERE email = ?")
      .run(ADMIN_ID, ADMIN_EMAIL);
    console.log("[seed] ✓ Admin UUID updated.");
  } else {
    console.log("[seed] Admin already exists with correct UUID — skipping.");
  }
  process.exit(0);
}

db.prepare(`
  INSERT INTO users (id, email, password_hash, display_name, role, status, created_at, updated_at)
  VALUES (?, ?, ?, ?, 'admin', 'active', unixepoch() * 1000, unixepoch() * 1000)
`).run(ADMIN_ID, ADMIN_EMAIL, ADMIN_PASSWORD_HASH, ADMIN_NAME);

console.log("[seed] ✓ Admin user created:", ADMIN_EMAIL, "id:", ADMIN_ID);