apex/server/db/seed.js

import initSqlJs from 'sql.js';
import fs from 'fs';
import path from 'path';
import { fileURLToPath } from 'url';

const __dirname = path.dirname(fileURLToPath(import.meta.url));
const dbPath = process.env.DATABASE_PATH || path.join(__dirname, '..', 'data', 'configurateur.db');

if (!fs.existsSync(dbPath)) {
  console.error('Database file not found. Run npm run db:init first.');
  process.exit(1);
}

const SQL = await initSqlJs({
  locateFile: (file) => path.join(__dirname, '..', 'node_modules', 'sql.js', 'dist', file)
});

function countMaterials(database) {
  try {
    const result = database.exec('SELECT COUNT(*) AS c FROM formex_materials');
    return Number(result[0]?.values?.[0]?.[0] ?? 0);
  } catch {
    return 0;
  }
}

function atomicWrite(filePath, database) {
  const tmpPath = `${filePath}.tmp`;
  fs.writeFileSync(tmpPath, Buffer.from(database.export()));
  try {
    fs.renameSync(tmpPath, filePath);
  } catch {
    fs.copyFileSync(tmpPath, filePath);
    try { fs.unlinkSync(tmpPath); } catch { /* ignore */ }
  }
}

const buffer = fs.readFileSync(dbPath);
const db = new SQL.Database(buffer);
const before = countMaterials(db);

const seedSql = fs.readFileSync(path.join(__dirname, 'seeds.sql'), 'utf8');
db.exec(seedSql);

const after = countMaterials(db);
if (before > 0 && after < before) {
  console.error(
    `REFUSING to write seeds: materials would shrink ${before} → ${after}. Aborting.`
  );
  db.close();
  process.exit(1);
}

atomicWrite(dbPath, db);
db.close();
console.log(`Seeds applied. Materials preserved: ${after} (was ${before}).`);