formulation/scripts/db-sync.mjs

#!/usr/bin/env node
import fs from "node:fs";
import path from "node:path";
import process from "node:process";
import { DatabaseSync } from "node:sqlite";
import YAML from "yaml";
import { writeSiteProjection } from "./lib/site-projection.mjs";

const root = path.resolve(import.meta.dirname, "..");
const databasePath = path.join(root, "var", "recipe-book.sqlite");

console.error(
  "❌ ERROR: Direct SQLite database resets from culinary YAML files are disabled.\n" +
  "The SQLite database (var/recipe-book.sqlite) and its JSON backup snapshots (scripts/backup.mjs) are the canonical source of truth.\n" +
  "To backup the database:  npm run db:backup -- [backup.json]\n" +
  "To restore from backup:  npm run db:restore -- <backup.json>\n"
);
process.exit(1);
fs.mkdirSync(path.dirname(databasePath), { recursive: true });
const db = new DatabaseSync(databasePath);
db.exec("PRAGMA foreign_keys = ON; PRAGMA journal_mode = WAL;");

const records = (directory) => {
  const location = path.join(root, "culinary", directory);
  if (!fs.existsSync(location)) return [];
  return fs.readdirSync(location).filter((name) => name.endsWith(".yaml")).sort().map((name) => YAML.parse(fs.readFileSync(path.join(location, name), "utf8")));
};
const run = (sql, values) => db.prepare(sql).run(...values);
const json = (value) => JSON.stringify(value ?? []);

const migrationsDir = path.join(root, "migrations");
for (const file of fs.readdirSync(migrationsDir).filter((f) => f.endsWith(".sql")).sort()) {
  try {
    db.exec(fs.readFileSync(path.join(migrationsDir, file), "utf8"));
  } catch {}
}

db.exec("BEGIN IMMEDIATE");
try {
  db.exec("PRAGMA defer_foreign_keys = ON");
  for (const table of ["collection_recipes", "collections", "recipe_measure_conversions", "ingredient_density_measurements", "ingredient_measure_conversions", "ingredient_prep_actions", "step_equipment", "recipe_equipment", "item_prep_actions", "recipe_steps", "recipe_items", "recipe_components", "price_observations", "purchase_items", "source_mappings", "ingredient_aliases", "recipes", "prep_actions", "equipment", "ingredients", "units"]) db.exec(`DELETE FROM ${table}`);
  for (const unit of records("units")) run("INSERT INTO units VALUES (?, ?, ?, ?, ?, ?, ?, ?)", [unit.id, unit.name, unit.symbol, unit.dimension, unit.system, unit.base_conversion?.base_unit_id ?? null, unit.base_conversion?.factor ?? null, unit.base_conversion?.offset ?? null]);
  for (const ingredient of records("ingredients")) {
    run("INSERT INTO ingredients(id, schema_version, name, status, categories_json, source_json, description, tags_json) VALUES (?, ?, ?, ?, ?, ?, ?, ?)", [ingredient.id, ingredient.schema_version, ingredient.name, ingredient.status, json(ingredient.categories), json(ingredient), ingredient.description ?? null, json(ingredient.tags ?? [])]);
    for (const alias of ingredient.aliases ?? []) run("INSERT INTO ingredient_aliases VALUES (?, ?, ?)", [ingredient.id, alias.name, alias.kind ?? null]);
    for (const conversion of ingredient.measure_conversions ?? []) run("INSERT INTO ingredient_measure_conversions VALUES (?, ?, ?, ?, ?, ?, ?, ?)", [ingredient.id, conversion.id, conversion.from.quantity, conversion.from.unit_id, conversion.to.quantity, conversion.to.unit_id, conversion.state ?? null, json(conversion.source)]);
    for (const density of ingredient.density_measurements ?? []) run("INSERT INTO ingredient_density_measurements VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)", [ingredient.id, density.id, density.mass.quantity, density.mass.unit_id, density.volume.quantity, density.volume.unit_id, density.temperature_c ?? null, density.state ?? null, json(density.source)]);
  }
  for (const item of records("equipment")) run("INSERT INTO equipment VALUES (?, ?, ?, ?)", [item.id, item.name, item.category ?? null, item.notes ?? null]);
  for (const action of records("prep_actions")) run("INSERT INTO prep_actions VALUES (?, ?, ?, ?, ?)", [action.id, action.name, action.action_type, action.default_yield_factor ?? null, action.notes ?? null]);
  const recipes = records("recipes");
  for (const recipe of recipes) run("INSERT INTO recipes(id, schema_version, title, summary, categories_json, tags_json, yield_quantity, yield_unit_id, yield_servings, yield_basis, scaling_mode, scaling_basis_id, scaling_basis_quantity, scaling_basis_unit_id, notes_json, source_json) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", [recipe.id, recipe.schema_version, recipe.title, recipe.summary ?? null, json(recipe.categories), json(recipe.tags), recipe.yield.amount.quantity, recipe.yield.amount.unit_id, recipe.yield.servings ?? null, recipe.yield.basis ?? null, recipe.scaling?.mode ?? null, recipe.scaling?.basis_id ?? null, recipe.scaling?.basis_amount?.quantity ?? null, recipe.scaling?.basis_amount?.unit_id ?? null, json(recipe.notes), json(recipe)]);
  for (const recipe of recipes) {
    for (const conversion of recipe.measure_conversions ?? []) run("INSERT INTO recipe_measure_conversions VALUES (?, ?, ?, ?, ?, ?, ?, ?)", [recipe.id, conversion.id, conversion.from.quantity, conversion.from.unit_id, conversion.to.quantity, conversion.to.unit_id, conversion.notes ?? null, json(conversion.source ?? {})]);
    for (const [componentPosition, component] of recipe.components.entries()) {
      run("INSERT INTO recipe_components VALUES (?, ?, ?, ?, ?)", [recipe.id, component.id, componentPosition + 1, component.name, json(component.notes)]);
      for (const [itemPosition, item] of component.items.entries()) {
        run("INSERT INTO recipe_items(recipe_id, component_id, id, position, ingredient_id, subrecipe_id, quantity, unit_id, percentage, basis_member, optional, notes, nutrition_retention_factor) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", [recipe.id, component.id, item.id, itemPosition + 1, item.reference.ingredient_id ?? null, item.reference.recipe_id ?? null, item.amount.quantity, item.amount.unit_id, item.percentage ?? null, item.basis_member ? 1 : 0, item.optional ? 1 : 0, item.notes ?? null, item.nutrition_retention_factor ?? 1]);
        for (const [prepPosition, prep] of (item.prep ?? []).entries()) run("INSERT INTO item_prep_actions VALUES (?, ?, ?, ?, ?, ?)", [recipe.id, item.id, prepPosition + 1, prep.action_id, prep.yield_factor ?? null, prep.notes ?? null]);
      }
    }
    for (const step of recipe.steps) {
      run("INSERT INTO recipe_steps VALUES (?, ?, ?, ?, ?)", [recipe.id, step.id, step.order, step.instruction, step.critical_control_point ? 1 : 0]);
      for (const equipmentId of step.equipment_ids ?? []) run("INSERT INTO step_equipment VALUES (?, ?, ?)", [recipe.id, step.id, equipmentId]);
    }
    for (const equipmentId of recipe.equipment_ids ?? []) run("INSERT INTO recipe_equipment VALUES (?, ?)", [recipe.id, equipmentId]);
  }
  for (const ingredient of records("ingredients")) for (const prep of ingredient.prep_actions ?? []) run("INSERT INTO ingredient_prep_actions VALUES (?, ?, ?, ?)", [ingredient.id, prep.action_id, prep.yield_factor, prep.notes ?? null]);
  db.exec(`INSERT INTO ingredient_prep_actions(ingredient_id, action_id, yield_factor, notes)
    SELECT DISTINCT i.ingredient_id, p.action_id, COALESCE(p.yield_factor, a.default_yield_factor, 1), p.notes
    FROM item_prep_actions p JOIN recipe_items i ON i.recipe_id = p.recipe_id AND i.id = p.item_id
    JOIN prep_actions a ON a.id = p.action_id WHERE i.ingredient_id IS NOT NULL
    ON CONFLICT(ingredient_id, action_id) DO NOTHING`);
  for (const item of records("purchase_items")) {
    run("INSERT INTO purchase_items(id, ingredient_id, name, brand, supplier_id, supplier_sku, status, package_quantity, package_unit_id, units_per_case, usable_yield_factor, deleted_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NULL)", [item.id, item.ingredient_id, item.name, item.brand ?? null, item.supplier_id ?? null, item.supplier_sku ?? null, item.status, item.package.quantity, item.package.unit_id, item.package.units_per_case ?? 1, item.package.usable_yield_factor ?? 1]);
    for (const price of item.prices) run("INSERT INTO price_observations VALUES (?, ?, ?, ?, ?)", [item.id, price.effective_at, price.currency, price.amount, json(price.source)]);
  }
  for (const mapping of records("source_mappings")) run("INSERT INTO source_mappings VALUES (?, ?, ?, ?, ?, ?, ?)", [mapping.id, mapping.subject.type, mapping.subject.id, mapping.mapping_type, mapping.status, json(mapping.source), mapping.nutrition_per_100g ? json(mapping.nutrition_per_100g) : null]);
  for (const collection of records("collections")) {
    run("INSERT INTO collections(id, name, description, source_json) VALUES (?, ?, ?, ?)", [collection.id, collection.name, collection.description ?? null, json(collection)]);
    for (const [position, entry] of (collection.entries ?? []).entries()) run("INSERT INTO collection_recipes VALUES (?, ?, ?)", [collection.id, entry.recipe_id, position + 1]);
  }
  db.exec("COMMIT");
} catch (error) {
  db.exec("ROLLBACK");
  throw error;
}

const counts = Object.fromEntries(["recipes", "ingredients", "recipe_items", "purchase_items", "equipment"].map((table) => [table, db.prepare(`SELECT count(*) AS count FROM ${table}`).get().count]));
console.log(`Synchronized ${databasePath}: ${Object.entries(counts).map(([name, count]) => `${count} ${name}`).join(", ")}`);
writeSiteProjection(db);
db.close();