apex/server/db/index.js

import initSqlJs from 'sql.js';
import path from 'path';
import fs from 'fs';
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 schemaPath = path.join(__dirname, 'schema.sql');

let db = null;
let sqlJs = null;

/** Création idempotente — bases anciennes sans ces tables (avant migration 014). */
const HARDWARE_CATALOG_DDL = `
CREATE TABLE IF NOT EXISTS blum_hardware (
  id              INTEGER PRIMARY KEY AUTOINCREMENT,
  code            TEXT UNIQUE NOT NULL,
  name            TEXT NOT NULL,
  type            TEXT NOT NULL,
  variant         TEXT,
  price_chf       REAL NOT NULL,
  specs_json      TEXT,
  image_url       TEXT,
  active          INTEGER DEFAULT 1
);
CREATE TABLE IF NOT EXISTS handle_variants (
  id              INTEGER PRIMARY KEY AUTOINCREMENT,
  code            TEXT UNIQUE NOT NULL,
  name            TEXT NOT NULL,
  style           TEXT,
  length_mm       INTEGER,
  price_chf       REAL NOT NULL,
  image_url       TEXT,
  active          INTEGER DEFAULT 1
);
CREATE TABLE IF NOT EXISTS hinge_variants (
  id              INTEGER PRIMARY KEY AUTOINCREMENT,
  code            TEXT UNIQUE NOT NULL,
  name            TEXT NOT NULL,
  type            TEXT NOT NULL,
  brand           TEXT DEFAULT 'Blum',
  price_chf       REAL NOT NULL,
  active          INTEGER DEFAULT 1
);
CREATE INDEX IF NOT EXISTS idx_blum_hardware_type ON blum_hardware(type);
CREATE INDEX IF NOT EXISTS idx_handle_variants_style ON handle_variants(style);
CREATE INDEX IF NOT EXISTS idx_hinge_variants_type ON hinge_variants(type);
`;

function toSqlitePlaceholders(sql) {
  return sql.replace(/\$(\d+)/g, '?');
}

const BACKUP_DIR = path.join(dataDir, 'backups');
const MAX_BACKUP_DAYS = 14;

/**
 * Count formex_materials in an open sql.js Database instance.
 */
function countMaterialsIn(database) {
  if (!database) return 0;
  try {
    const result = database.exec('SELECT COUNT(*) AS c FROM formex_materials');
    return Number(result[0]?.values?.[0]?.[0] ?? 0);
  } catch {
    return 0;
  }
}

/**
 * Peek material count on a DB file without touching the live in-memory DB.
 */
function countMaterialsOnDisk(filePath = dbPath) {
  if (!filePath || !fs.existsSync(filePath) || !sqlJs) return 0;
  let probe = null;
  try {
    const buffer = fs.readFileSync(filePath);
    if (!buffer.length) return 0;
    probe = new sqlJs.Database(buffer);
    return countMaterialsIn(probe);
  } catch {
    return 0;
  } finally {
    try { probe?.close(); } catch { /* ignore */ }
  }
}

/**
 * Snapshot the on-disk DB before this process touches it, so scraped data
 * (Häfele, Leuba Hiag, Formex) survives accidental wipes. One backup per
 * day (first start of the day), kept for MAX_BACKUP_DAYS days.
 * Never replaces a richer day's backup with an emptier DB.
 */
function backupExistingDb() {
  try {
    if (!fs.existsSync(dbPath)) return;
    if (!fs.existsSync(BACKUP_DIR)) fs.mkdirSync(BACKUP_DIR, { recursive: true });

    const diskCount = countMaterialsOnDisk(dbPath);
    const today = new Date().toISOString().slice(0, 10);
    const backupPath = path.join(BACKUP_DIR, `configurateur-${today}.db`);

    if (!fs.existsSync(backupPath)) {
      if (diskCount === 0) {
        console.warn('[db] skip daily backup — on-disk DB has 0 materials (protects older non-empty backups)');
      } else {
        fs.copyFileSync(dbPath, backupPath);
        console.log(`[db] daily backup created: ${backupPath} (${diskCount} materials)`);
      }
    } else {
      const backupCount = countMaterialsOnDisk(backupPath);
      if (diskCount > backupCount) {
        fs.copyFileSync(dbPath, backupPath);
        console.log(`[db] daily backup upgraded: ${backupPath} (${backupCount} → ${diskCount} materials)`);
      }
    }

    const backups = fs.readdirSync(BACKUP_DIR)
      .filter((f) => /^configurateur-\d{4}-\d{2}-\d{2}\.db$/.test(f))
      .sort();

    while (backups.length > MAX_BACKUP_DAYS) {
      // Prefer deleting empty backups so a sole non-empty snapshot is kept.
      let removeIdx = backups.findIndex((f) => countMaterialsOnDisk(path.join(BACKUP_DIR, f)) === 0);
      if (removeIdx < 0) removeIdx = 0;
      const removed = backups.splice(removeIdx, 1)[0];
      fs.unlinkSync(path.join(BACKUP_DIR, removed));
      console.warn(`[db] rotated backup removed: ${removed}`);
    }
  } catch (e) {
    console.error('[db] backup failed:', e.message);
  }
}

/**
 * Atomically persist the in-memory DB.
 *
 * Blocks only *catastrophic* overwrites — the failure mode that kept wiping
 * scrapes: a stale/empty in-memory process saving over a full on-disk catalog.
 * Normal scrapes (count grows) and single-row admin deletes still save.
 */
function save({ allowShrink = false } = {}) {
  if (!db || !dbPath) return false;

  const memCount = countMaterialsIn(db);
  if (!allowShrink && fs.existsSync(dbPath)) {
    const diskCount = countMaterialsOnDisk(dbPath);
    const emptyClobber = memCount === 0 && diskCount > 0;
    const massShrink = diskCount >= 20 && memCount < Math.floor(diskCount * 0.1);
    if (emptyClobber || massShrink) {
      console.error(
        `[db] REFUSING save: in-memory has ${memCount} materials but disk has ${diskCount}. ` +
          'Scraped data protected. Restart the server to reload from disk.'
      );
      try {
        if (!fs.existsSync(BACKUP_DIR)) fs.mkdirSync(BACKUP_DIR, { recursive: true });
        const stamp = new Date().toISOString().replace(/[:.]/g, '-').slice(0, 19);
        fs.writeFileSync(
          path.join(BACKUP_DIR, `configurateur-refused-${stamp}.db`),
          Buffer.from(db.export())
        );
      } catch { /* best-effort forensic copy */ }
      return false;
    }
  }

  const tmpPath = `${dbPath}.tmp`;
  const data = Buffer.from(db.export());
  fs.writeFileSync(tmpPath, data);
  try {
    fs.renameSync(tmpPath, dbPath);
  } catch {
    // Windows may not allow rename over an existing file while another handle is open.
    fs.copyFileSync(tmpPath, dbPath);
    try { fs.unlinkSync(tmpPath); } catch { /* ignore */ }
  }
  return true;
}

async function getDb() {
  if (db) return db;
  sqlJs = await initSqlJs({
    locateFile: (file) => path.join(__dirname, '..', 'node_modules', 'sql.js', 'dist', file)
  });
  let buffer;
  if (fs.existsSync(dbPath)) {
    // sqlJs must be ready before peek-based backup logic.
    backupExistingDb();
    buffer = fs.readFileSync(dbPath);
  }
  db = new sqlJs.Database(buffer || null);
  try {
    // Ensure full schema exists for legacy/partial DB files.
    if (fs.existsSync(schemaPath)) {
      const schemaSql = fs.readFileSync(schemaPath, 'utf8');
      db.exec(schemaSql);
    }
    db.exec(HARDWARE_CATALOG_DDL);
    // Persist schema ensures through the guarded saver (never clobber richer disk).
    save();
  } catch (e) {
    console.error('[db] ensureHardwareCatalogTables:', e.message);
  }
  const loaded = countMaterialsIn(db);
  console.log(`[db] loaded ${loaded} materials from ${dbPath}`);
  return db;
}

export const dbReady = getDb();

export const query = async (sql, params = []) => {
  const database = await getDb();
  const sqliteSql = toSqlitePlaceholders(sql);
  const stmt = database.prepare(sqliteSql);
  stmt.bind(Array.isArray(params) ? params : []);
  const rows = [];
  while (stmt.step()) rows.push(stmt.getAsObject());
  stmt.free();
  return { rows };
};

export const run = async (sql, params = []) => {
  const database = await getDb();
  const sqliteSql = toSqlitePlaceholders(sql);
  database.run(sqliteSql, Array.isArray(params) ? params : []);
  save();
  return {};
};

/** Force-persist even if material count shrinks (admin bulk delete, etc.). */
export const saveAllowShrink = async () => {
  await getDb();
  return save({ allowShrink: true });
};

const pool = {
  query: (sql, params) => query(sql, params)
};

export default pool;