mega-plant/scripts/seed.mjs

// Seed PocketBase with data extracted from data/source/*.json
// Regenerate those files with `node scripts/extract.mjs`
//
// Usage: PB_ADMIN=... PB_PASS=... node scripts/seed.mjs
import PocketBase from "pocketbase";
import { readFileSync, existsSync, readdirSync } from "node:fs";
import { fileURLToPath } from "node:url";
import { dirname, resolve } from "node:path";
// The app's own insurance resolver, imported directly so the CLI and the page cost
// a rate period the same way. Node strips the TypeScript types natively — this
// import only works because lib/insurance.ts uses erasable syntax and type-only
// imports, so nothing has to resolve `pocketbase` at runtime.
import { monthlyCost, uncoveredMonths, validatePeriods } from "../lib/insurance.ts";

const __dirname = dirname(fileURLToPath(import.meta.url));
const DATA_DIR = resolve(__dirname, "..", "data", "source");

// Load .env.local so `npm run seed` works without exporting vars by hand.
// Explicit process.env values still win — CI/deploys can override.
const envPath = resolve(__dirname, "..", ".env.local");
if (existsSync(envPath)) {
  for (const line of readFileSync(envPath, "utf8").split("\n")) {
    const t = line.trim();
    if (!t || t.startsWith("#") || !t.includes("=")) continue;
    const i = t.indexOf("=");
    const k = t.slice(0, i).trim();
    if (!process.env[k]) process.env[k] = t.slice(i + 1).trim();
  }
}

// Read required env — fail loudly if missing (per Part 5.2)
const PB = process.env.NEXT_PUBLIC_PB_URL;
const EMAIL = process.env.PB_ADMIN;
const PASS = process.env.PB_PASS;
const errors = [];
if (!PB) errors.push("NEXT_PUBLIC_PB_URL");
if (!EMAIL) errors.push("PB_ADMIN");
if (!PASS) errors.push("PB_PASS");
if (errors.length) {
  console.error(`✗ Missing required env: ${errors.join(", ")}`);
  console.error("  Copy .env.example to .env.local and fill in.");
  process.exit(1);
}

// Placeholder pin for every machine that has no coordinate of its own, so the
// Plant Map is never blank. Every asset therefore shares one point until the
// on-site scan sheet supplies real per-machine coordinates — any explicit
// `latitude`/`longitude` in assets.json wins over this.
// When the sheet lands, delete these two lines and the `?? DEFAULT_GEO` fallbacks
// below; the JSON values will carry every pin from then on.
const DEFAULT_GEO = JSON.parse(readFileSync(resolve(__dirname, "..", "lib", "plant-location-policy.json"), "utf8"));

const ASSETS_COLL = "assets";
const INCOME_COLL = "monthly_income";
const RUN_COLL = "monthly_running_costs";
const OVERHEADS_COLL = "overheads";
const COST_LINES_COLL = "cost_lines";
const INSURANCE_RATES_COLL = "insurance_rates";
const FINANCING_COLL = "financing_terms";
const OPERATORS_COLL = "operators";
const MECHANICS_COLL = "mechanics";
const OIL_COLL = "oil_items";
const INVOICES_COLL = "invoices";
const INVOICE_LINES_COLL = "invoice_lines";

const assets = JSON.parse(readFileSync(resolve(DATA_DIR, "assets.json"), "utf8"));
const machines = JSON.parse(readFileSync(resolve(DATA_DIR, "machines.json"), "utf8"));
const mrc = JSON.parse(readFileSync(resolve(DATA_DIR, "mrc.json"), "utf8"));
const overheads = JSON.parse(readFileSync(resolve(DATA_DIR, "overheads.json"), "utf8"));
// `insurance` rows are NOT in this file any more — they are derived from the rate
// periods below, so a premium has exactly one home. See the derive step.
const costLines = JSON.parse(readFileSync(resolve(DATA_DIR, "cost_lines.json"), "utf8"));
// One row per premium PERIOD, not per month. A premium moves every month or three.
const insuranceRates = JSON.parse(readFileSync(resolve(DATA_DIR, "insurance-rates.json"), "utf8"));
// One row per FINANCE AGREEMENT — the instalment only. Start/end dates and the
// settled flag are typed in by a human in the app and are preserved across re-seeds
// (see the capture below), because no sheet records them and losing them would make
// the Financing page answer nothing.
const financingTerms = JSON.parse(readFileSync(resolve(DATA_DIR, "financing-terms.json"), "utf8"));
// Operator register — TERM 3 of the fixed-expense formula. The NAME and RATE come from
// the sheet; which MACHINE a person is allocated to is changed in the app and is
// preserved across re-seeds (see the capture below), because re-running the seed
// would otherwise silently put everyone back where the sheet last said.
const operators = JSON.parse(readFileSync(resolve(DATA_DIR, "operators.json"), "utf8"));
// Mechanic register — the people whose badge carries a QR. Separate from operators:
// an operator is allocated TO a machine, a mechanic is not.
const mechanics = JSON.parse(readFileSync(resolve(DATA_DIR, "mechanics.json"), "utf8"));
// Tracked oils: name, drum size and the store cost code a PO prints for it. Reconciled
// by `code`, never cleared, so an oil added in the app is not deleted on a re-seed.
const oilItems = JSON.parse(readFileSync(resolve(DATA_DIR, "oil-items.json"), "utf8"));
// Which machines the operator sheet covers. The sheet lists twenty vehicles; the plant
// register holds forty-one coded machines and the difference (containers, generators,
// truck trailers) can never have a person allocated to them. Stored as a flag on each
// asset so the page can list the sheet's vehicles while STILL reading their name and
// code from the register. Three of the twenty have no operator and would be dropped by
// any rule derived from the operators collection.
const operatorSheetCodes = new Set(operators.map((o) => o.asset_code).filter(Boolean));

// Invoices: one JSON per source PDF, all loaded automatically. Dropping a new
// invoice-*.json into data/source is the whole procedure for adding another invoice.
const invoiceFiles = readdirSync(DATA_DIR).filter((f) => /^invoice-.*\.json$/i.test(f)).sort();
const invoices = invoiceFiles.map((f) => ({
  ...JSON.parse(readFileSync(resolve(DATA_DIR, f), "utf8")),
  _file: f,
}));

const pb = new PocketBase(PB);
pb.autoCancellation(false);

console.log(`→ Connecting to ${PB} as ${EMAIL}`);
await pb.collection("_superusers").authWithPassword(EMAIL, PASS); // Part 5.3: _superusers, not admins
console.log("  ✓ authenticated");

// Names a human typed into the app are NOT in data/source, so a re-seed would otherwise
// silently erase them — the same trap as an uploaded PDF. Captured BEFORE the wipe below,
// keyed by invoice_no, and re-applied only where the JSON itself specifies no name (an
// external invoice's supplier is typed in; an internal site's name comes from the JSON and
// always wins).
const preservedNames = new Map();
try {
  for (const r of await pb.collection(INVOICES_COLL).getFullList()) {
    const name = (r.site || "").trim();
    if (r.invoice_no && name) preservedNames.set(r.invoice_no, name);
  }
} catch {
  // No invoices collection yet — nothing to preserve.
}
console.log(`  ✓ captured ${preservedNames.size} existing invoice name(s) to preserve`);

// Same trap, worse consequence: a finance agreement's start date, end date and
// "settled" flag are typed in by a human on the Financing page. They exist nowhere in
// data/source, so a re-seed would silently wipe months of someone's bookkeeping.
// Captured BEFORE the wipe, keyed by ASSET CODE, and re-applied to the fresh row.
// A machine that leaves the sheet loses its row entirely (it is no longer financed),
// which is the correct outcome — the sheet, not the database, decides membership.
const preservedFinance = new Map();
try {
  const assets = await pb.collection(ASSETS_COLL).getFullList();
  const codeOf = new Map(assets.map((a) => [a.id, a.code]));
  for (const t of await pb.collection(FINANCING_COLL).getFullList()) {
    const code = codeOf.get(t.asset);
    if (!code) continue;
    preservedFinance.set(code, {
      start_year: t.start_year || 0,
      start_month: t.start_month || 0,
      end_year: t.end_year || 0,
      end_month: t.end_month || 0,
      is_settled: Boolean(t.is_settled),
    });
  }
} catch {
  // No financing_terms collection yet — nothing to preserve.
}
console.log(`  ✓ captured ${preservedFinance.size} finance agreement(s) with client-entered dates to preserve`);

// Same trap for operators, keyed by EMPLOYEE CODE — the one identifier on this register
// that is unique per person (names are not). Which machine someone is allocated to is
// moved by hand in the app and exists nowhere on disk, so a re-seed that ignored this
// would quietly put every operator back where the sheet last said.
const preservedOperatorAssets = new Map();
const existingOperators = new Map();
try {
  const assets = await pb.collection(ASSETS_COLL).getFullList();
  const codeOf = new Map(assets.map((a) => [a.id, a.code]));
  for (const o of await pb.collection(OPERATORS_COLL).getFullList()) {
    if (!o.employee_code) continue;
    preservedOperatorAssets.set(o.employee_code, codeOf.get(o.asset) || null);
    existingOperators.set(o.employee_code, o);
  }
} catch {
  // No operators collection yet — nothing to preserve.
}
console.log(`  ✓ captured ${preservedOperatorAssets.size} operator allocation(s) to preserve`);

// Same trap, same fix, for purchase lines. `assets` is re-created by this seed with NEW
// record ids, and PocketBase CLEARS a relation whose target was deleted — so without
// this, every purchase-to-machine allocation silently empties on any re-seed. Keyed by
// PO number + line number because BOTH ids change when the rows are rebuilt.
const preservedPurchaseLines = [];
try {
  const assets = await pb.collection(ASSETS_COLL).getFullList();
  const codeOf = new Map(assets.map((a) => [a.id, a.code]));
  const pos = await pb.collection("purchases").getFullList();
  // Maps purchase id -> po_no STRING, so the value is used directly. Reading
  // `.po_no` off it yields undefined and every restore then misses.
  const poNoOf = new Map(pos.map((p) => [p.id, p.po_no]));
  for (const l of await pb.collection("purchase_lines").getFullList()) {
    const poNo = poNoOf.get(l.purchase);
    if (poNo && l.asset) preservedPurchaseLines.push({ po: poNo, line: l.line_no, code: codeOf.get(l.asset) });
  }
} catch {
  // No purchases yet — nothing to preserve.
}
console.log(`  ✓ captured ${preservedPurchaseLines.length} purchase-line allocation(s) to preserve`);

// Wipe existing rows so we can re-seed cleanly.
//
// `operators` and `mechanics` are DELIBERATELY NOT in this list either. A person can be
// added to either register from the app, and those rows exist in no file — clearing them
// would delete the client's own staff on the next seed. Both are reconciled by key
// against the sheet further down: refresh what the sheet defines, keep what it does not.
//
// `invoices` and `invoice_lines` are DELIBERATELY NOT in this list. They used to be, and
// that meant a re-seed deleted every invoice a person had uploaded through the app and
// re-created only the handful that happen to have a JSON file in data/source — silently
// destroying the register, twice in one afternoon. The app now has an upload pipeline, so
// most invoices have no JSON file and the collection is no longer owned by this script.
//
// Instead only the invoices this run will actually rebuild are replaced, just below. An
// uploaded invoice survives any number of re-seeds; a seeded one is still refreshed from
// its JSON so edits there keep working.
for (const c of [ASSETS_COLL, INCOME_COLL, RUN_COLL, OVERHEADS_COLL, COST_LINES_COLL, INSURANCE_RATES_COLL, FINANCING_COLL]) {
  const items = await pb.collection(c).getFullList();
  for (const r of items) await pb.collection(c).delete(r.id);
}
console.log("  ✓ wiped existing rows");

// Replace ONLY the invoices this run will re-create, so editing an invoice-*.json still
// takes effect without touching anything uploaded through the app. Lines go first — they
// reference their invoice.
const seededInvoiceNos = new Set(invoices.map((i) => (i.invoice_no || "").trim()).filter(Boolean));
try {
  const existing = await pb.collection(INVOICES_COLL).getFullList();
  let removedLines = 0;
  let removedInvoices = 0;
  for (const inv of existing) {
    if (!seededInvoiceNos.has((inv.invoice_no || "").trim())) continue; // uploaded — keep it
    for (const l of await pb.collection(INVOICE_LINES_COLL).getFullList({ filter: `invoice = "${inv.id}"` })) {
      await pb.collection(INVOICE_LINES_COLL).delete(l.id);
      removedLines++;
    }
    await pb.collection(INVOICES_COLL).delete(inv.id);
    removedInvoices++;
  }
  const kept = existing.length - removedInvoices;
  console.log(
    `  ✓ invoices: replacing ${removedInvoices} from data/source (${removedLines} lines), ` +
      `keeping ${kept} uploaded through the app`,
  );
} catch {
  // No invoices collection yet — nothing to replace.
}

// === Build alias map for code mismatches between graph sheet and asset register ===
// Per Part 1.4 / Part B.3: derived from the workbook, not invented. Unmatched rows
// must produce a loud failure, not a silent ghost.
const codeAliases = {
  "428E": "TLB02",   // CAT 425F (428E) → TLB02
  "430":  "SS01",    // CASE420 (430) → first SS01 (handled below)
};
function resolveCode(graphCode, graphName) {
  if (!graphCode) return null;
  if (codeAliases[graphCode]) {
    // Special-case 430 — first match wins unless name indicates second
    if (graphCode === "430" && /SS02/i.test(graphName)) return "SS02";
    return codeAliases[graphCode];
  }
  return graphCode;
}

// Name normalisation for fallback matching
function normalizeName(n) {
  return String(n).toLowerCase().replace(/[^a-z0-9]+/g, "").slice(0, 12);
}

// === Insert assets ===
// Site assignments are preserved across a re-seed. Before the assets are wiped
// we snapshot the current code→site mapping, because the new rows get fresh
// ids and a name/code lookup is the only stable key across the wipe.
const SITES_COLL = "sites";
let existingSiteByCode = new Map();
let existingSiteByName = new Map();
// QR text typed into the Plant Register is entered by hand and exists nowhere on disk.
const existingQrByCode = new Map();
// Vehicle sticker numbers from the client's sheet. A value typed in the app wins; this fills the rest.
const sheetQrByCode = JSON.parse(readFileSync(resolve(DATA_DIR, "asset-qr.json"), "utf8"));
try {
  const siteIdToName = new Map();
  for (const s of await pb.collection(SITES_COLL).getFullList()) {
    siteIdToName.set(s.id, s.name);
  }
  for (const a of await pb.collection(ASSETS_COLL).getFullList()) {
    if (a.code && a.qr_code) existingQrByCode.set(a.code, a.qr_code);
    const siteName = a.site ? siteIdToName.get(a.site) : null;
    if (!siteName) continue;
    if (a.code) existingSiteByCode.set(a.code, siteName);
    existingSiteByName.set(normalizeName(a.name), siteName);
  }
  if (existingSiteByCode.size || existingSiteByName.size) {
    console.log(`  · preserving site assignments for ${existingSiteByCode.size + existingSiteByName.size} asset lookups`);
  }
} catch {
  // `sites` is optional — a fresh install may not have run the setup script.
}

let siteIdByName = new Map();
try {
  for (const s of await pb.collection(SITES_COLL).getFullList()) {
    siteIdByName.set(normalizeName(s.name), s.id);
  }
} catch {
  console.log("  · no `sites` collection — every machine will seed unassigned");
}

console.log(`\n→ Seeding ${assets.length} assets`);
const codeToId = {};
const nameToId = {};
let ok = 0;
const seedErrors = [];
for (const a of assets) {
  try {
    // `site` in assets.json wins; otherwise fall back to whatever was assigned
    // before the wipe. An unrecognised name is a loud failure, not a silent
    // unassignment — a dropped site link is exactly the kind of quiet data
    // loss this seed is meant to surface.
    const wantedSite = a.site || existingSiteByCode.get(a.code) || existingSiteByName.get(normalizeName(a.name)) || null;
    let siteId = "";
    if (wantedSite) {
      siteId = siteIdByName.get(normalizeName(wantedSite)) || "";
      if (!siteId) seedErrors.push(`asset "${a.name}": unknown site "${wantedSite}" — add it to the sites collection or clear the assignment`);
    }
    const rec = await pb.collection(ASSETS_COLL).create({
      code: a.code,
      name: a.name,
      category: a.category || "Plant",
      type: a.type || "",         // Part C.2
      gl_account: a.gl_account || "",
      supplier: a.supplier || "",
      monthly_excl: a.monthly_excl || 0,
      monthly_incl: a.monthly_incl || 0,
      purchase_value: a.purchase_value || 0,
      is_provisional: a.is_provisional || false,  // Part C.3
      // Scope flag: this machine appears on the operator sheet. Derived from the sheet,
      // so a re-seed always refreshes it — never a person-entered value.
      on_operator_sheet: operatorSheetCodes.has(a.code),
      is_active: a.is_active !== false, // default true; only "AEB Plant Versekering" is false
      site: siteId,
      qr_code: existingQrByCode.get(a.code) || sheetQrByCode[a.code] || "",
      // Plant Map — falls back to the shared placeholder pin until the
      // on-site scan sheet supplies a real coordinate for this machine.
      latitude: a.latitude ?? DEFAULT_GEO.latitude,
      longitude: a.longitude ?? DEFAULT_GEO.longitude,
    });
    if (a.code) codeToId[a.code] = rec.id;
    nameToId[normalizeName(a.name)] = rec.id;
    ok++;
  } catch (e) {
    seedErrors.push(`asset "${a.name}": ${e.response?.data?.message || e.message}`);
  }
}
console.log(`  ✓ ${ok}/${assets.length} assets inserted`);

// === Insert insurance_rates (the premium itself) ===
// Insurance is stored as RATE PERIODS because a premium moves every month or three
// (client ruling, 2026-10-08). A machine's monthly cost is then DERIVED from the
// period covering that month, so a premium is keyed in exactly once and the month a
// rate changed is readable.
//
// This imports the app's own resolver rather than reimplementing it. If the CLI and
// the page each had their own copy, a fix to one would silently leave the other
// costing a year wrongly — the same reason `invoice-booking-policy.json` is shared.
console.log(`\n→ Seeding ${insuranceRates.length} insurance rate periods`);
const rateErrors = [];
const periodsByCode = new Map();
let irRows = 0;
for (const r of insuranceRates) {
  const assetId = codeToId[r.asset_code];
  if (!assetId) { rateErrors.push(`insurance rate asset_code=${r.asset_code}: asset not found`); continue; }
  try {
    await pb.collection(INSURANCE_RATES_COLL).create({
      asset: assetId,
      year: r.year,
      month_from: r.month_from,
      // Normalise a missing month_to to 0 (open) rather than writing null: null
      // would read as "unknown", and an unknown end date must not be mistaken for
      // a premium that never expires.
      month_to: r.month_to || 0,
      rate: r.rate,
      plant: r.plant || "",
      source: r.source || "",
    });
    irRows++;
    const list = periodsByCode.get(r.asset_code) || [];
    list.push({ from: r.month_from, to: r.month_to || 0, rate: r.rate, source: r.source || "" });
    periodsByCode.set(r.asset_code, list);
  } catch (e) {
    rateErrors.push(`insurance rate ${r.asset_code} ${r.month_from}→${r.month_to}: ${e.response?.data?.message || e.message}`);
  }
}
console.log(`  ✓ ${irRows} insurance rate periods inserted`);

// A premium period that overlaps another, or that leaves a hole, would cost a year
// wrongly and the page would only show it as a blank cell. Refuse the whole seed
// rather than write numbers nobody can trust.
for (const [code, periods] of periodsByCode) {
  for (const problem of validatePeriods(periods)) {
    rateErrors.push(`${code}: ${problem}`);
  }
}

// === Derive each month's insurance cost from the rate in force that month ===
const derivedInsurance = [];
let uncoveredTotal = 0;
for (const [code, periods] of periodsByCode) {
  const cost = monthlyCost(periods);
  uncoveredTotal += uncoveredMonths(periods).length;
  cost.forEach((amount, i) => {
    // `null` means no premium was recorded for that month. Skip the row entirely —
    // never book a 0, which would read as "insured for free" and drag the total down.
    if (amount === null) return;
    derivedInsurance.push({ asset_code: code, year: 2026, month: i + 1, kind: "insurance", amount });
  });
}
console.log(`  ✓ ${derivedInsurance.length} monthly insurance costs derived from rate history`);
if (uncoveredTotal) {
  console.log(`  ! ${uncoveredTotal} month(s) have no premium on record — left unbooked, not booked as R0`);
}
if (rateErrors.length) {
  console.error("\n✗ Insurance rate problems — refusing to seed:");
  for (const e of rateErrors) console.error(`    ${e}`);
  process.exit(1);
}

// === Insert financing_terms (finance agreements) ===
// Only the INSTALMENT comes from the sheet. Dates and the settled flag are re-applied
// from the pre-wipe capture above, because those are typed in by a human and exist in
// no file. A machine absent from the sheet gets no row — it is not financed.
console.log(`\n→ Seeding ${financingTerms.length} finance agreements`);
let ftRows = 0;
let ftRestored = 0;
const ftErrors = [];
for (const t of financingTerms) {
  const assetId = codeToId[t.asset_code];
  if (!assetId) { ftErrors.push(`financing asset_code=${t.asset_code}: asset not found`); continue; }
  const kept = preservedFinance.get(t.asset_code);
  if (kept) ftRestored++;
  // An ACTIVE agreement must carry an instalment. A paid-off one legitimately has
  // none (the sheet leaves the cell blank), so the check is on live agreements only.
  // The schema cannot enforce this: `installment` is optional precisely so a
  // settled row can be written, and the business rule belongs here.
  const willBeSettled = kept ? kept.is_settled : Boolean(t.settled);
  if (!willBeSettled && !(t.installment > 0)) {
    ftErrors.push(`financing ${t.asset_code}: active agreement with no instalment on the sheet`);
    continue;
  }
  try {
    await pb.collection(FINANCING_COLL).create({
      asset: assetId,
      installment: t.installment,
      start_year: kept?.start_year ?? 0,
      start_month: kept?.start_month ?? 0,
      end_year: kept?.end_year ?? 0,
      end_month: kept?.end_month ?? 0,
      // The client's own toggle WINS over the sheet. The JSON `settled` flag is only a
      // first-run default for an agreement that has no preserved row yet — otherwise
      // re-seeding would silently reopen an agreement the client has since settled.
      is_settled: willBeSettled,
      plant: t.plant || "",
      source: t.source || "",
    });
    ftRows++;
  } catch (e) {
    ftErrors.push(`financing ${t.asset_code}: ${e.response?.data?.message || e.message}`);
  }
}
console.log(`  ✓ ${ftRows} finance agreements inserted`);
if (ftRestored) console.log(`  ✓ ${ftRestored} restored with their client-entered dates and settled flag`);

if (ftErrors.length) {
  console.error("\n✗ Financing problems — refusing to seed:");
  for (const e of ftErrors) console.error(`    ${e}`);
  process.exit(1);
}

// === Insert operators (term 3 — the operator wage) ===
// A row per PERSON on the sheet. A machine with no operator gets NO row here — that
// is expressed by the absence of a row, not by a blank one. The machine's "no
// operator" state is derived by the page from the plant register.
//
// The machine allocation is re-applied from the pre-wipe capture, so a move made in
// the app survives a re-seed. An operator with no machine on the sheet is legitimate
// (two were) and stays unassigned until someone allocates them.
console.log(`\n→ Seeding ${operators.length} operator rows`);
let opRows = 0;
let opAllocated = 0;
const opErrors = [];
for (const o of operators) {
  // No operator name means the sheet's machine simply has nobody on it.
  if (!o.operator) continue;
  if (!(o.rate > 0)) {
    opErrors.push(`operator ${o.operator} (${o.employee_code}): no rate on the sheet`);
    continue;
  }
  if (!o.employee_code) {
    // The capture is keyed by employee code, so an operator without one could never
    // have their allocation preserved. Refuse rather than write a row that a later
    // re-seed would silently reset.
    opErrors.push(`operator ${o.operator}: no employee code — cannot track their allocation across a re-seed`);
    continue;
  }

  // The client's own allocation WINS over the sheet when it survives. It is captured
  // above while the OLD asset rows still exist, so it reads as a real code rather than a
  // dangling id.
  //
  // When the stored relation is EMPTY the sheet decides instead, and that is deliberate:
  // PocketBase CLEARS a relation whose target record was deleted, and `assets` is
  // re-created by this very seed with new ids. So an empty relation is AMBIGUOUS — it
  // can mean "deliberately unassigned" or "the machine row was rebuilt and the link
  // broke". Losing a real allocation is far worse than restoring the client's own sheet
  // value, so the sheet is the tie-breaker. (Verified: this exact ambiguity once emptied
  // all 19 allocations in one seed.)
  let assetCode = o.asset_code || null;
  if (preservedOperatorAssets.has(o.employee_code) && preservedOperatorAssets.get(o.employee_code)) {
    assetCode = preservedOperatorAssets.get(o.employee_code);
  }
  let assetId = assetCode ? codeToId[assetCode] : null;
  if (assetCode && !assetId) {
    opErrors.push(`operator ${o.operator}: machine "${assetCode}" is not in the plant register`);
    continue;
  }
  if (assetId) opAllocated++;

  // Refresh in place when the register already knows this person, so a corrected rate
  // or name on the sheet lands without creating a duplicate row. The sheet does NOT
  // overwrite the machine allocation — that is a person-entered fact the client owns.
  const existing = existingOperators.get(o.employee_code);
  try {
    if (existing) {
      // `asset` MUST be included here. The asset rows were just rebuilt with new ids,
      // so the stored relation is dangling — and PocketBase reads a dangling relation as
      // EMPTY. Omitting it left every existing operator unassigned while the seed still
      // reported them as allocated. Verified: that mistake emptied all 19 in one seed.
      await pb.collection(OPERATORS_COLL).update(existing.id, {
        name: o.operator,
        // A rate changed on the employee's dashboard WINS over the sheet. Writing the
        // sheet value here would silently undo the client's own correction every seed.
        ...(existing.rate_set_in_app ? {} : { rate: o.rate }),
        // Empty string, not null: a PocketBase relation clears properly with "".
        asset: assetId || "",
      });
    } else {
      await pb.collection(OPERATORS_COLL).create({
        name: o.operator,
        employee_code: o.employee_code,
        rate: o.rate,
        // Empty string, not null: a PocketBase relation clears properly with "".
        asset: assetId || "",
        source: o.source || "Operator sheet",
      });
    }
    opRows++;
  } catch (e) {
    opErrors.push(`operator ${o.operator} (${o.employee_code}): ${e.response?.data?.message || e.message}`);
  }
}
console.log(`  ✓ ${opRows} operators reconciled (${opAllocated} allocated to a machine, ${opRows - opAllocated} unassigned)`);

const opKept = [...existingOperators.keys()].filter((c) => !operators.some((o) => o.employee_code === c));
if (opKept.length) {
  console.log(`  ✓ ${opKept.length} operator(s) kept that are NOT on the sheet — they were added in the app:`);
  for (const c of opKept) console.log(`      ${c}`);
}

// ⚠️ The loop above only re-applies a machine to operators the SHEET lists. An operator
// added in the app has no sheet row, so their `asset` relation is never refreshed — and
// `assets` is re-created by this very seed with NEW record ids, so the stored relation
// now points at a row that no longer exists and reads as "unassigned". Without this the
// client silently loses the machine of anyone they added in the app, every single seed.
let opRestored = 0;
for (const [employeeCode, assetCode] of preservedOperatorAssets) {
  if (operators.some((o) => o.employee_code === employeeCode)) continue; // handled above
  const op = existingOperators.get(employeeCode);
  if (!op) continue;
  const newAssetId = assetCode ? codeToId[assetCode] : null;
  if (assetCode && !newAssetId) {
    opErrors.push(`operator ${op.name} (${employeeCode}): machine "${assetCode}" is no longer in the plant register`);
    continue;
  }
  try {
    await pb.collection(OPERATORS_COLL).update(op.id, { asset: newAssetId || "" });
    opRestored++;
  } catch (e) {
    opErrors.push(`operator ${op.name} (${employeeCode}): could not restore machine — ${e.response?.data?.message || e.message}`);
  }
}
if (opRestored) console.log(`  ✓ ${opRestored} app-added operator allocation(s) re-pointed at the rebuilt asset rows`);

if (opErrors.length) {
  console.error("\n✗ Operator problems — refusing to seed:");
  for (const e of opErrors) console.error(`    ${e}`);
  process.exit(1);
}

// === Reconcile mechanics (badge register) ===
// The employee code is what the QR encodes, so a duplicate would put two
// indistinguishable badges in circulation. Refuse rather than print them.
//
// Refreshed in place by employee code rather than cleared and re-created: a mechanic
// added from the app has no row in the sheet and must survive a re-seed, and a badge
// holding a photo must not lose it just because the name was retyped.
console.log(`\n→ Reconciling ${mechanics.length} mechanics against the sheet`);
let mechRows = 0;
const mechErrors = [];
const seenCodes = new Set();
let existingMechanics = [];
try {
  existingMechanics = await pb.collection(MECHANICS_COLL).getFullList();
} catch {
  // No mechanics collection yet.
}
const mechanicsByCode = new Map(existingMechanics.filter((m) => m.employee_code).map((m) => [m.employee_code, m]));
for (const m of mechanics) {
  const code = (m.employee_code || "").trim();
  if (!m.name) { mechErrors.push(`mechanic "${code || "?"}": no name`); continue; }
  if (!code) { mechErrors.push(`mechanic ${m.name}: no employee code — there would be nothing to encode in the QR`); continue; }
  if (seenCodes.has(code)) { mechErrors.push(`mechanic ${m.name}: employee code ${code} is already used by another badge`); continue; }
  seenCodes.add(code);
  try {
    const existing = mechanicsByCode.get(code);
    if (existing) {
      await pb.collection(MECHANICS_COLL).update(existing.id, { name: m.name, role: m.role || "Mechanic" });
    } else {
      await pb.collection(MECHANICS_COLL).create({
        name: m.name,
        employee_code: code,
        role: m.role || "Mechanic",
        source: m.source || "Mechanics sheet",
      });
    }
    mechRows++;
  } catch (e) {
    mechErrors.push(`mechanic ${m.name} (${code}): ${e.response?.data?.message || e.message}`);
  }
}
console.log(`  ✓ ${mechRows} mechanics reconciled`);
const mechKept = [...mechanicsByCode.keys()].filter((c) => !mechanics.some((m) => (m.employee_code || "").trim() === c));
if (mechKept.length) {
  console.log(`  ✓ ${mechKept.length} mechanic(s) kept that are NOT on the sheet — they were added in the app:`);
  for (const c of mechKept) console.log(`      ${c}`);
}
if (mechErrors.length) {
  console.error("\n✗ Mechanic problems — refusing to seed:");
  for (const e of mechErrors) console.error(`    ${e}`);
  process.exit(1);
}

// === Re-point purchase-line allocations at the rebuilt asset rows ===
// Indexed in memory from ONE query rather than filtered per line: a per-line filter
// silently returning nothing is how this block used to run through five saved
// allocations and restore none of them, with no error to show for it.
{
  const assetsNow = await pb.collection(ASSETS_COLL).getFullList();
  const codeToIdNow = new Map(assetsNow.filter((a) => a.code).map((a) => [a.code, a.id]));
  const posNow = await pb.collection("purchases").getFullList();
  const poIdByNo = new Map(posNow.map((p) => [p.po_no, p.id]));
  const lineByKey = new Map();
  for (const l of await pb.collection("purchase_lines").getFullList()) {
    lineByKey.set(`${l.purchase}:${l.line_no}`, l);
  }

  let restored = 0;
  const missed = [];
  for (const saved of preservedPurchaseLines) {
    const poId = poIdByNo.get(saved.po);
    const target = saved.code ? codeToIdNow.get(saved.code) : "";
    const line = poId ? lineByKey.get(`${poId}:${saved.line}`) : null;
    if (!line) { missed.push(`${saved.po} #${saved.line} (line not found)`); continue; }
    if (!target) { missed.push(`${saved.po} #${saved.line} (machine "${saved.code}" gone)`); continue; }
    if (line.asset === target) continue;
    await pb.collection("purchase_lines").update(line.id, { asset: target });
    restored++;
  }
  if (restored) console.log(`  ✓ ${restored} purchase-line allocation(s) re-pointed at the rebuilt asset rows`);
  for (const m of missed) console.log(`  ! could not restore a purchase allocation: ${m}`);
}

// === Reconcile oil items ===
// NOT deleted: people add oils from the app. The sheet refreshes name/drum/store code
// for the ones it lists, and anything it does not list is kept.
console.log(`\n→ Reconciling ${oilItems.length} oil items against the sheet`);
let oilRows = 0;
const oilErrors = [];
const existingOils = new Map();
try {
  for (const o of await pb.collection(OIL_COLL).getFullList()) {
    if (o.code) existingOils.set(o.code, o);
  }
} catch {
  // No oils collection yet.
}
for (const o of oilItems) {
  if (!o.code || !o.name) { oilErrors.push(`oil ${o.code || "?"}: needs a code and a name`); continue; }
  try {
    const existing = existingOils.get(o.code);
    if (existing) {
      await pb.collection(OIL_COLL).update(existing.id, {
        name: o.name, detail: o.detail || "", drum_litres: o.drum_litres || 0,
        colour: o.colour || "", store_cost_code: o.store_cost_code || "", qr_payload: o.qr || "",
      });
    } else {
      await pb.collection(OIL_COLL).create({
        code: o.code, name: o.name, detail: o.detail || "", supplier: o.supplier || "",
        material_code: o.material_code || "", store_cost_code: o.store_cost_code || "",
        drum_litres: o.drum_litres || 0, colour: o.colour || "", qr_payload: o.qr || "",
        source: "Oil sheet",
      });
    }
    // Opening stock (diesel starts at 589 L): written ONCE, only while the item has no
    // movements at all, so a re-seed never re-adds it on top of later deliveries/overwrites.
    if (o.opening_litres) {
      const item = existingOils.get(o.code) ?? (await pb.collection(OIL_COLL).getFirstListItem(`code = "${o.code}"`));
      const any = await pb.collection("oil_stock_movements").getList(1, 1, { filter: `item = "${item.id}"` });
      if (!any.totalItems) {
        await pb.collection("oil_stock_movements").create({
          item: item.id, kind: "adjusted", litres: o.opening_litres, note: "Opening stock",
        });
      }
    }
    oilRows++;
  } catch (e) {
    oilErrors.push(`oil ${o.code}: ${e.response?.data?.message || e.message}`);
  }
}
console.log(`  ✓ ${oilRows} oil items reconciled`);
const oilKept = [...existingOils.keys()].filter((c) => !oilItems.some((o) => o.code === c));
if (oilKept.length) {
  console.log(`  ✓ ${oilKept.length} oil(s) kept that are NOT on the sheet — they were added in the app:`);
  for (const c of oilKept) console.log(`      ${c}`);
}
if (oilErrors.length) {
  console.error("\n✗ Oil problems — refusing to seed:");
  for (const e of oilErrors) console.error(`    ${e}`);
  process.exit(1);
}

// === Insert cost_lines (Part C.1 — finance/tracking per machine, + derived insurance) ===
const allCostLines = [...costLines, ...derivedInsurance];
console.log(`\n→ Seeding ${allCostLines.length} cost_lines`);
let clRows = 0;
const clErrors = [];
for (const cl of allCostLines) {
  const assetId = codeToId[cl.asset_code];
  if (!assetId) { clErrors.push(`cost_line asset_code=${cl.asset_code}: asset not found`); continue; }
  try {
    await pb.collection(COST_LINES_COLL).create({
      asset: assetId,
      year: cl.year,
      month: cl.month,
      kind: cl.kind,
      amount: cl.amount,
    });
    clRows++;
  } catch (e) {
    clErrors.push(`cost_line ${cl.asset_code} ${cl.kind} m=${cl.month}: ${e.response?.data?.message || e.message}`);
  }
}
console.log(`  ✓ ${clRows} cost_lines inserted`);

// === Insert monthly_income from Machine Graph Data ===
// Part 1.5: monthly_income no longer carries running_cost — we derive it from monthly_running_costs on read.
// Part 1.4: alias map + synthetic codes for uncoded items, fail loudly on unmatched rows.
console.log("\n→ Seeding monthly_income from Machine Graph Data");
let incomeRows = 0;
const incomeErrors = [];
const lastActualMonth = (() => {
  // Last month with any recorded income across the whole workbook
  let m = 0;
  for (const x of machines) for (const d of x.data) if (d.income > 0 && d.month > m) m = d.month;
  return m;
})();
for (const m of machines) {
  const resolvedCode = resolveCode(m.code, m.machine);
  let assetId = resolvedCode ? codeToId[resolvedCode] : null;
  if (!assetId && m.machine) assetId = nameToId[normalizeName(m.machine)];
  // Part B.3: NO synthetic code, NO auto-create. Unmatched → loud failure.
  if (!assetId) {
    incomeErrors.push(`machine "${m.machine}" (code=${m.code || "—"}) — no asset matched. Add a code or sequential alias to assets.json and re-seed.`);
    continue;
  }
  for (const d of m.data) {
    const isForecast = d.month > lastActualMonth;
    try {
      await pb.collection(INCOME_COLL).create({
        asset: assetId,
        year: 2026,
        month: d.month,
        income: d.income,
        fixed_expenses: d.fixed_expenses,
        is_forecast: isForecast, // Part 1.2
        // running_cost removed (Part 1.5) — derived from monthly_running_costs at read time
      });
      incomeRows++;
    } catch (e) {
      incomeErrors.push(`income ${m.code || m.machine} m=${d.month}: ${e.response?.data?.message || e.message}`);
    }
  }
}
console.log(`  ✓ ${incomeRows} income rows inserted`);

// === Insert monthly_running_costs ===
console.log("\n→ Seeding monthly_running_costs");
let runRows = 0;
const runErrors = [];
for (const r of mrc) {
  let assetId = codeToId[r.code];
  if (!assetId) assetId = nameToId[normalizeName(r.name)];
  if (!assetId) { runErrors.push(`running ${r.code || r.name} m=${r.month}: no asset matched`); continue; }
  if (!r.amount) continue;
  try {
    await pb.collection(RUN_COLL).create({
      asset: assetId,
      year: 2026,
      month: r.month,
      amount: r.amount,
    });
    runRows++;
  } catch (e) {
    runErrors.push(`running ${r.code} m=${r.month}: ${e.response?.data?.message || e.message}`);
  }
}
console.log(`  ✓ ${runRows} running-cost rows inserted`);

// === Insert overheads ===
// Part 1.3: skip "Plant Overheads" — those rows are machines already in assets.
// Part B.4: skip "Santam versekering" — double-counted in the workbook's own subtotal.
console.log("\n→ Seeding overheads (excluding Plant Overheads + Santam)");
let ohRows = 0;
const ohErrors = [];
const VAT_WARN = []; // Part B.3: collect VAT anomalies — DO NOT silent-correct, report loudly
for (const o of overheads) {
  if (o.section === "Plant Overheads") continue; // Part 1.3
  // Note: Santam is kept (Round 4 Part A.2 — round 3 was wrong to drop it).

  // Part B.3 / Round 4 Part A.3: VAT validation — collect warnings, then FAIL the seed
  // if any row is arithmetically broken (incl < excl or VAT math doesn't add up).
  // Per A.3: do not silently correct. Trip loudly so the client confirms the right number.
  const vatErrors = [];
  if (o.amount_incl > 0 && o.amount_excl > 0) {
    const computedIncl = o.amount_excl + o.vat;
    if (Math.abs(computedIncl - o.amount_incl) > 0.5) {
      vatErrors.push(`VAT math broken: "${o.description}" (${o.section}) — excl ${o.amount_excl} + vat ${o.vat} = ${computedIncl}, but incl is ${o.amount_incl}`);
    }
    if (o.amount_incl < o.amount_excl) {
      vatErrors.push(`incl < excl: "${o.description}" (${o.section}) — excl ${o.amount_excl}, incl ${o.amount_incl}`);
    }
    if (o.amount_excl > 0 && (o.vat / o.amount_excl) > 0.20) {
      vatErrors.push(`VAT > 20%: "${o.description}" (${o.section}) — ${((o.vat / o.amount_excl) * 100).toFixed(1)}%`);
    }
  }
  // A.3: hard fail on VAT errors. Move them to FATAL so seed exits non-zero.
  if (vatErrors.length > 0) {
    ohErrors.push(...vatErrors.map((v) => `FATAL-VAT: ${v}`));
    VAT_WARN.push(...vatErrors);
  }

  try {
    await pb.collection(OVERHEADS_COLL).create({
      section: o.section,
      description: o.description,
      gl_account: o.gl_account || "",
      supplier: o.supplier || "",
      qty: o.qty || 0,
      unit_price: o.unit_price || 0,
      amount_excl: o.amount_excl || 0,
      vat: o.vat || 0,
      amount_incl: o.amount_incl || 0,
    });
    ohRows++;
  } catch (e) {
    ohErrors.push(`overhead "${o.description}": ${e.response?.data?.message || e.message}`);
  }
}
console.log(`  ✓ ${ohRows} overhead rows inserted`);

if (VAT_WARN.length > 0) {
  console.log(`\n  ⚠ ${VAT_WARN.length} VAT anomalies (Part B.3 — flagged, NOT corrected):`);
  for (const w of VAT_WARN) console.log(`    - ${w}`);
}

// === Insert invoices + invoice lines ===
// Every data/source/invoice-*.json is picked up automatically, so dropping a new
// invoice PDF's transcription in that folder is all it takes to add another one.
//
// The line items are transcribed from the source PDF, so the arithmetic is re-checked
// here rather than trusted. Per the project's standing rule: never silently correct a
// figure to make it balance — trip loudly and let the client confirm.
console.log(`\n→ Seeding ${invoices.length} invoice(s)`);
const invErrors = [];
const round2 = (n) => Math.round(n * 100) / 100;
let invoicesSeeded = 0;
let lineRows = 0;

for (const inv of invoices) {
  const where = inv.source_file || inv.invoice_no;
  // Scoped per invoice: a warning on one invoice must never block the next one.
  // (Using the cumulative array here silently skipped every invoice after the first
  // one that had an unbooked line.)
  const errs = [];

  // Header arithmetic. `subtotal` is the ex-VAT hire, `vat` is the "PLUS VAT" column,
  // and `total_incl` is the VAT-inclusive figure the debtor actually pays.
  const sumHire = round2(inv.lines.reduce((s, l) => s + l.amount_excl, 0));
  const sumVat = round2(inv.lines.reduce((s, l) => s + l.vat, 0));
  if (sumHire !== round2(inv.subtotal)) {
    errs.push(`${where}: hire lines total ${sumHire} but invoice subtotal is ${inv.subtotal}`);
  }
  if (sumVat !== round2(inv.vat)) {
    errs.push(`${where}: VAT lines total ${sumVat} but invoice PLUS VAT is ${inv.vat}`);
  }
  if (round2(inv.subtotal + inv.escalation) !== round2(inv.total_excl)) {
    errs.push(`${where}: subtotal ${inv.subtotal} + escalation ${inv.escalation} != total_excl ${inv.total_excl}`);
  }
  if (round2(inv.total_excl + inv.vat) !== round2(inv.total_incl)) {
    errs.push(`${where}: total_excl ${inv.total_excl} + vat ${inv.vat} != total_incl ${inv.total_incl}`);
  }
  if (inv.vat > 0 && inv.vat_pct && inv.vat / inv.total_excl > inv.vat_pct / 100 + 0.001) {
    errs.push(`${where}: VAT ${inv.vat} on excl ${inv.total_excl} exceeds the stated ${inv.vat_pct}% rate`);
  }

  // Per-line arithmetic
  for (const l of inv.lines) {
    if (round2(l.amount_excl + l.vat) !== round2(l.total_incl)) {
      errs.push(`${where} line ${l.line_no} (${l.machine_code}) excl ${l.amount_excl} + vat ${l.vat} != incl ${l.total_incl}`);
    }
    if (inv.vat_pct) {
      const expected = round2(l.amount_excl * inv.vat_pct / 100);
      if (Math.abs(expected - l.vat) > 0.02) {
        errs.push(`${where} line ${l.line_no} (${l.machine_code}) VAT is ${l.vat}, but ${inv.vat_pct}% of ${l.amount_excl} is ${expected}`);
      }
    }
    if (l.qty && l.rate && round2(l.qty * l.rate) !== round2(l.amount_excl)) {
      errs.push(`${where} line ${l.line_no} (${l.machine_code}) qty ${l.qty} x rate ${l.rate} = ${round2(l.qty * l.rate)}, but amount is ${l.amount_excl}`);
    }
  }

  if (errs.length) {
    // One broken invoice must not block the others — report it and move on.
    invErrors.push(...errs);
    console.error(`  ✗ ${where}: NOT seeded —`);
    for (const e of errs) console.error(`      - ${e}`);
    continue;
  }

  const periodEnd = new Date(inv.period_to);
  let invoiceRec = null;
  try {
    invoiceRec = await pb.collection(INVOICES_COLL).create({
      invoice_no: inv.invoice_no,
      invoice_date: inv.invoice_date,
      order_no: inv.order_no,
      debtor: inv.debtor,
      debtor_no: inv.debtor_no,
      period_from: inv.period_from,
      period_to: inv.period_to,
      description: inv.description,
      // The JSON name is authoritative when it has one. Otherwise carry over whatever
      // was typed into the app before this seed ran.
      site: (inv.site || preservedNames.get(inv.invoice_no) || "").trim(),
      section: inv.section,
      category: inv.category,
      currency: inv.currency,
      vat_reg_no: inv.vat_reg_no,
      source_file: inv.source_file,
      subtotal: inv.subtotal,
      escalation: inv.escalation,
      vat: inv.vat,
      vat_pct: inv.vat_pct,
      total_excl: inv.total_excl,
      total_incl: inv.total_incl,
      booked_month: periodEnd.getUTCMonth() + 1,
      booked_year: periodEnd.getUTCFullYear(),
      is_booked: false,
    });
  } catch (e) {
    invErrors.push(`${where}: header failed — ${e.response?.data?.message || e.message}`);
    continue;
  }

  invoicesSeeded++;
  let okLines = 0;
  for (const l of inv.lines) {
    // An unmatched line is seeded with booked_asset EMPTY rather than guessed, so it
    // shows on the page as "unbooked" instead of silently charging the wrong machine.
    let assetId = l.asset_code ? codeToId[l.asset_code] : null;
    if (!assetId) {
      const why = l.asset_code
        ? `no asset matched code "${l.asset_code}"`
        : "no target asset assigned";
      invErrors.push(`UNBOOKED: ${where} line ${l.line_no} "${l.machine_code}" (${l.description}) — ${why}. ${l._unmatched || ""}`);
    }
    try {
      await pb.collection(INVOICE_LINES_COLL).create({
        invoice: invoiceRec.id,
        line_no: l.line_no,
        machine_code: l.machine_code,
        asset_code: l.asset_code || "",
        description: l.description,
        qty: l.qty,
        unit: l.unit,
        rate: l.rate,
        rate_type: l.rate_type,
        amount_excl: l.amount_excl,
        vat: l.vat,
        total_incl: l.total_incl,
        booked_asset: assetId || "",
        booked_month: invoiceRec.booked_month,
        booked_year: invoiceRec.booked_year,
      });
      okLines++;
      lineRows++;
    } catch (e) {
      invErrors.push(`${where} line ${l.line_no} ${l.machine_code}: ${e.response?.data?.message || e.message}`);
    }
  }
  console.log(`  ✓ ${inv.invoice_no}${inv.site ? ` (${inv.site})` : ""} — ${okLines}/${inv.lines.length} lines, total R${inv.total_incl.toFixed(2)} incl VAT`);
}

console.log("\n=== Summary ===");
console.log(`Assets: ${ok}/${assets.length}`);
console.log(`Cost lines: ${clRows}`);
console.log(`Income: ${incomeRows}`);
console.log(`Running: ${runRows}`);
console.log(`Overheads: ${ohRows}`);
console.log(`Invoices: ${invoicesSeeded}/${invoices.length} (+ ${lineRows} lines)`);

const totalErrors = seedErrors.length + clErrors.length + incomeErrors.length + runErrors.length + ohErrors.length + invErrors.length;
if (totalErrors > 0) {
  console.error(`\n✗ ${totalErrors} errors — seed FAILED loudly:`);
  for (const e of [...seedErrors, ...clErrors, ...incomeErrors, ...runErrors, ...ohErrors, ...invErrors]) console.error(`  - ${e}`);
  if (invErrors.length) {
    console.error("\n  Fix the target asset in the matching data/source/invoice-*.json, or assign it");
    console.error("  in /income/invoices (Booked to), then re-run: node scripts/book-invoice.mjs");
  }
  console.error("\n  ⚠ monthly_income was wiped and re-imported from the workbook only.");
  console.error("    Invoice-derived income is NOT in it — re-run `node scripts/book-invoice.mjs` after fixing the above.");
  process.exit(2);
}
console.log("\n✅ Seed complete");
console.log("   Invoice lines are seeded but NOT yet in monthly_income.");
console.log("   Run `node scripts/book-invoice.mjs` to post them.");