apex/server/db/init.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 dataDir = path.join(__dirname, '..', 'data');
if (!fs.existsSync(dataDir)) fs.mkdirSync(dataDir, { recursive: true });
const dbPath = process.env.DATABASE_PATH || path.join(dataDir, 'configurateur.db');

const force = process.argv.includes('--force');

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 dbExists = fs.existsSync(dbPath);

if (dbExists && force) {
  // Backup before destroying anything.
  const backupDir = path.join(dataDir, 'backups');
  if (!fs.existsSync(backupDir)) fs.mkdirSync(backupDir, { recursive: true });
  const stamp = new Date().toISOString().replace(/[:.]/g, '-').slice(0, 19);
  const backupPath = path.join(backupDir, `configurateur-pre-init-${stamp}.db`);
  fs.copyFileSync(dbPath, backupPath);
  console.log(`Existing database backed up to ${backupPath}`);
}

let db;
if (dbExists && !force) {
  // Non-destructive: apply schema (CREATE TABLE IF NOT EXISTS) to the existing DB,
  // preserving all data (scraped materials, orders, etc.).
  db = new SQL.Database(fs.readFileSync(dbPath));
  const before = countMaterials(db);
  console.log(
    `Existing database found (${before} materials) — applying schema without deleting data (use --force to recreate from scratch).`
  );
} else {
  db = new SQL.Database();
}

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

if (dbExists && !force) {
  const after = countMaterials(db);
  const diskBefore = (() => {
    try {
      const probe = new SQL.Database(fs.readFileSync(dbPath));
      const n = countMaterials(probe);
      probe.close();
      return n;
    } catch {
      return 0;
    }
  })();
  if (diskBefore > 0 && after < diskBefore) {
    console.error(
      `REFUSING to write: schema apply would shrink materials ${diskBefore} → ${after}. Aborting.`
    );
    db.close();
    process.exit(1);
  }
}

atomicWrite(dbPath, db);
console.log(`Schema applied. Materials on disk: ${countMaterials(db)}`);
db.close();