MEGAINFRA-WEBAPP/scripts/migrations/2026-09-30-add-supplier-reconciliation-rules.mjs

/** Add draft knowledge to suppliers, without creating another collection.
 * node --env-file=.env.local scripts/migrations/2026-09-30-add-supplier-reconciliation-rules.mjs [--apply]
 * Default is a live read-only dry run. Backups contain no authentication secrets.
 */
import PocketBase from 'pocketbase';
import { readFile, writeFile, mkdtemp } from 'node:fs/promises';
import { tmpdir } from 'node:os';
import { resolve, join } from 'node:path';
import { fileURLToPath } from 'node:url';

const fieldName = 'reconciliation_rules';
const packPath = new URL('../../docs/audits/2026-09-30-supplier-reconciliation-rules.json', import.meta.url);
const canonical = value => Array.isArray(value) ? value.map(canonical)
  : value && typeof value === 'object' ? Object.fromEntries(Object.keys(value).sort().map(k => [k, canonical(value[k])])) : value;
export const equalJson = (a, b) => JSON.stringify(canonical(a)) === JSON.stringify(canonical(b));

export function buildKnowledge(existing, pack, supplierId) {
  if (existing != null && (typeof existing !== 'object' || Array.isArray(existing)
    || existing.schema_version !== 1 || !Array.isArray(existing.rules))) {
    throw new Error(`Invalid existing supplier knowledge: ${supplierId}`);
  }
  const result = structuredClone(existing ?? {
    schema_version: 1, prepared_on: pack.prepared_on, status: 'draft', financially_active: false,
    rules: [],
  });
  for (const rule of pack.rules.filter(r => !r.supplier_ids.length || r.supplier_ids.includes(supplierId))) {
    const matches = result.rules.filter(r => r.rule_key === rule.rule_key);
    if (matches.length > 1 || (matches.length && !equalJson(matches[0], rule))) {
      throw new Error(`Rule conflict on ${supplierId}: ${rule.rule_key}; preserve existing knowledge for review`);
    }
    if (!matches.length) result.rules.push(structuredClone(rule));
  }
  return result;
}

function preservedRecord(row) {
  return Object.fromEntries(Object.entries(row).filter(([key]) => ![fieldName, 'updated', 'expand'].includes(key)));
}

async function main() {
  if (process.argv.slice(2).some(arg => arg !== '--apply')) throw new Error('Only --apply is supported');
  const apply = process.argv.includes('--apply');
  const pack = JSON.parse(await readFile(packPath, 'utf8'));
  if (pack.schema_version !== 1 || !pack.suppliers.length || !pack.rules.length
    || new Set(pack.suppliers.map(s => s.id)).size !== pack.suppliers.length
    || new Set(pack.rules.map(r => r.rule_key)).size !== pack.rules.length
    || pack.rules.some(r => r.status !== 'draft' || r.financially_active !== false
      || !Array.isArray(r.supplier_ids) || r.supplier_ids.some(id => !pack.suppliers.some(s => s.id === id)))) {
    throw new Error('Invalid draft pack; no writes made');
  }
  const url = process.env.NEXT_PUBLIC_POCKETBASE_URL || process.env.PB_URL;
  if (!url) throw new Error('PocketBase URL missing');
  const pb = new PocketBase(url);
  pb.autoCancellation(false);
  if (process.env.POCKETBASE_ADMIN_TOKEN) pb.authStore.save(process.env.POCKETBASE_ADMIN_TOKEN);
  else await pb.collection('_superusers').authWithPassword(
    process.env.POCKETBASE_ADMIN_EMAIL || process.env.PB_ADMIN_EMAIL,
    process.env.POCKETBASE_ADMIN_PASSWORD || process.env.PB_ADMIN_PASSWORD);
  const schema = await pb.collections.getOne('suppliers');
  const field = schema.fields.find(f => f.name === fieldName);
  if (field && (field.type !== 'json' || field.required || field.maxSize < 1048576)) {
    throw new Error('Existing reconciliation_rules field is incompatible; no writes made');
  }
  const allBefore = await pb.collection(schema.id).getFullList();
  const plans = pack.suppliers.map(supplier => {
    const original = allBefore.find(s => s.id === supplier.id);
    if (!original || original.name !== supplier.name) throw new Error(`Supplier identity mismatch: ${supplier.id}`);
    const knowledge = buildKnowledge(original[fieldName], pack, supplier.id);
    return { original, knowledge, changed: !equalJson(original[fieldName], knowledge) };
  });
  console.log(JSON.stringify({ mode: apply ? 'apply' : 'dry-run', addField: !field,
    changedSuppliers: plans.filter(p => p.changed).length,
    suppliers: plans.map(p => ({ id: p.original.id, name: p.original.name, rules: p.knowledge.rules.length, changed: p.changed })) }, null, 2));
  if (!apply) return;
  if (!field || plans.some(p => p.changed)) {
    const backupDir = await mkdtemp(join(tmpdir(), 'mega-supplier-rules-backup-'));
    await writeFile(join(backupDir, 'before.json'), JSON.stringify({ schema, suppliers: allBefore }, null, 2), { mode: 0o600 });
    console.log(`Backup: ${backupDir}/before.json`);
    // Re-check the collection before appending; never replace access rules or indexes.
    if (!equalJson(schema, await pb.collections.getOne(schema.id))) throw new Error('Schema changed during migration');
    if (!field) await pb.collections.update(schema.id, {
      fields: [...schema.fields, { name: fieldName, type: 'json', required: false, maxSize: 1048576 }],
    });
    for (const plan of plans.filter(p => p.changed)) {
      const current = await pb.collection(schema.id).getOne(plan.original.id);
      if (!equalJson(preservedRecord(current), preservedRecord(plan.original))
        || !equalJson(current[fieldName] ?? null, plan.original[fieldName] ?? null)) {
        throw new Error(`Supplier changed during migration: ${plan.original.id}; backup retained`);
      }
      await pb.collection(schema.id).update(plan.original.id, { [fieldName]: plan.knowledge });
    }
  }
  const afterSchema = await pb.collections.getOne(schema.id);
  const actualField = afterSchema.fields.find(f => f.name === fieldName);
  if (!actualField || actualField.type !== 'json' || actualField.required) throw new Error('Field verification failed');
  for (const key of ['listRule', 'viewRule', 'createRule', 'updateRule', 'deleteRule', 'indexes']) {
    if (!equalJson(schema[key], afterSchema[key])) throw new Error(`Collection ${key} changed`);
  }
  if (!equalJson(schema.fields.filter(f => f.name !== fieldName), afterSchema.fields.filter(f => f.name !== fieldName))) {
    throw new Error('Existing schema fields changed');
  }
  const allAfter = await pb.collection(schema.id).getFullList();
  if (allAfter.length !== allBefore.length) throw new Error('Supplier count changed');
  for (const before of allBefore) {
    const after = allAfter.find(r => r.id === before.id);
    const plan = plans.find(p => p.original.id === before.id);
    if (!after || !equalJson(preservedRecord(before), preservedRecord(after))
      || !equalJson(after[fieldName] ?? null, plan ? plan.knowledge : before[fieldName] ?? null)) {
      throw new Error(`Supplier read-back verification failed: ${before.id}`);
    }
  }
  console.log(`Verified ${plans.length} supplier knowledge records; ${allBefore.length} supplier identities and existing schema preserved. Rules remain draft.`);
}

if (process.argv[1] && resolve(process.argv[1]) === fileURLToPath(import.meta.url)) {
  main().catch(error => {
    // SDK errors can contain authentication payloads; report only the safe status.
    console.error(error?.status ? `PocketBase request failed (HTTP ${error.status}); inspect retained backup before retrying.` : error.message);
    process.exitCode = 1;
  });
}