`scripts/execute-sweep.mjs`

368 lines
#!/usr/bin/env node
// execute-sweep.mjs — GATED EXECUTOR for the "DW# == Mfr SKU" master-cleanup sweep.
//
// ============================ HARD SAFETY RAILS ============================
//  1. DRY-RUN by default. Nothing is written unless --apply is passed.
//  2. Every DELETE re-snapshots the FULL record to data/restore/<rid>-<ISO>.json
//     FIRST and REFUSES to delete without a saved snapshot on disk.
//  3. RE-VERIFY at execute time (re-read each record via FileMaker): a record is
//     deleted ONLY if it STILL has no sample history AND its mfr is STILL a
//     placeholder (== the dw_sku numeric tail). Any record that no longer matches
//     is SKIPPED + reported (skipped-mismatch).
//  4. NEVER delete a SKU to zero — at least one KEEP record must survive per SKU.
//  5. Each KEEP: write the real mfr onto `Mfr Pattern` ONLY if the current value
//     is a placeholder; the OLD value is captured first (reversible).
//  6. Blast cap (default 500 deletes) — refuses to exceed without --max.
//  7. Every executed action is ledgered to
//     ~/.claude/yolo-queue/executed-reversible/ledger.jsonl via log-exec.mjs.
//
// This script performs DESTRUCTIVE canonical FileMaker writes and must only be
// run on an explicit approved plan + --apply. Default is dry-run.
//
// Usage:
//   node scripts/execute-sweep.mjs --plan data/plans/<vendor>-<ISO>.json          # DRY-RUN
//   node scripts/execute-sweep.mjs --plan data/plans/<vendor>-<ISO>.json --apply   # EXECUTE
//   node scripts/execute-sweep.mjs --memo ~/.claude/yolo-queue/pending-approval/<memo>.md --apply
//   node scripts/execute-sweep.mjs --plan <file> --apply --max 800 --ticket TK-10909
//
// Plan JSON shape (array of per-SKU entries):
//   [ { "dw_sku": "HSW-51526", "keepRecordId": "446079", "realMfr": "gz127",
//       "deleteRecordIds": ["391954","446080"] }, ... ]

import { readFileSync, writeFileSync, existsSync, mkdirSync } from 'node:fs';
import { execFileSync } from 'node:child_process';
import path from 'node:path';
import os from 'node:os';
import { fileURLToPath } from 'node:url';

const __dir = path.dirname(fileURLToPath(import.meta.url));
const ROOT = path.join(__dir, '..');
const RESTORE_DIR = path.join(ROOT, 'data', 'restore');
const PENDING_DIR = path.join(os.homedir(), '.claude', 'yolo-queue', 'pending-approval');
const LOG_EXEC = path.join(os.homedir(), '.claude', 'yolo-queue', 'executed-reversible', 'log-exec.mjs');

const FM_PROJECT = path.join(os.homedir(), 'Projects', 'filemaker-mcp');
const FM_ENV = path.join(FM_PROJECT, '.env');
const FM_CLIENT = path.join(FM_PROJECT, 'src', 'fm-client.js');
const FM_DB = 'WALLPAPER';
const FM_LAYOUT = '*List Wallpapers - Full View';
// WRITE/DELETE layout (fix for FileMaker error [110] "Related tables are missing", 2026-08-28):
// The full-view layout carries a portal ("WALLPAPER2 by mfr#") + related fields (Clients::,
// "WALLPAPER2 by combo sku::"). The Data API validates ALL related table occurrences on a
// DELETE / PATCH (but not on a plain GET), and one of those relationships is broken/missing,
// so every delete aborts with [110] even though the record itself is fine and reads succeed.
// "Basic List of Fields" (21 fields, 0 portals, 0 related fields) reads the SAME record clean
// (verified: same recordId = same base-table occurrence, identical combo sku + Mfr Pattern) and
// deletes/patches without the related-table validation. Cascade behavior is defined in the
// Relationship Graph, not the layout, so it is identical regardless of layout (Kimi-confirmed).
// READS still use FM_LAYOUT (full field set); WRITES + DELETES use FM_WRITE_LAYOUT.
const FM_WRITE_LAYOUT = process.env.FM_WRITE_LAYOUT || 'Basic List of Fields';

// ---- CLI args --------------------------------------------------------------
function arg(k) { const i = process.argv.indexOf('--' + k); return i >= 0 ? process.argv[i + 1] : undefined; }
const HAS = (k) => process.argv.includes('--' + k);
const APPLY = HAS('apply');
const PLAN_FILE = arg('plan');
const MEMO_FILE = arg('memo');
const MAX = arg('max') ? Number(arg('max')) : 500;
const TICKET = arg('ticket') || process.env.TK_TICKET || 'TK-10909';
const AGENT = arg('agent') || process.env.TK_AGENT || 'vp-dw-commerce';

// ---- load filemaker-mcp .env (read-only) so FM Cloud creds are present -----
function loadFmEnv() {
  if (!existsSync(FM_ENV)) return;
  for (const line of readFileSync(FM_ENV, 'utf8').split('\n')) {
    const m = line.match(/^([A-Z_]+)=(.*)$/);
    if (m && !process.env[m[1]]) process.env[m[1]] = m[2];
  }
}
loadFmEnv();

let _fm = null;
async function fm() {
  if (_fm) return _fm;
  _fm = await import('file://' + FM_CLIENT);
  return _fm;
}

// ---- placeholder / sample predicate helpers (mirror candidates.mjs) --------
function numericTail(s) { const m = String(s || '').match(/(\d[\d]*)\s*$/); return m ? m[1] : ''; }

// Parse the real mfr code out of the Mfr Pattern / note text.
// "#gz127 - $44.10 Net - 1/14" -> "gz127"; "AMW10042-6; ..." -> "AMW10042-6"; "15712" -> "15712".
function parseNoteMfr(note) {
  let t = String(note || '').trim();
  if (!t) return '';
  t = t.split(/[\r\n]/)[0].trim();
  const hash = t.match(/#\s*([^\s;,-][^\s;,]*)/);
  if (hash) return hash[1].replace(/[.,]$/, '').trim();
  const m = t.match(/^([^\s;]+?)(?:\s*;|\s+-\s+|\s+\$|\s+net\b|$)/i);
  if (m) return m[1].replace(/[.,]$/, '').trim();
  return t;
}

// Given a live fieldData object + the dw_sku, compute (sampleOrdered, mfrPlaceholder).
// This IS the execute-time re-verification predicate.
function classifyRecord(fd, dwSku) {
  const clean = String(dwSku || '').trim().replace(/[-_ ]?sample$/i, '');
  const tail = numericTail(clean);
  const mfrPattern = String(fd['Mfr Pattern'] || '').trim();
  const chaseMemo = String(fd['Vendor Sample - Where is Memo Send 2nd day'] || '').trim();
  const mfrNote = mfrPattern || (/#/.test(chaseMemo) ? chaseMemo : '');
  const noteMfr = parseNoteMfr(mfrNote) || parseNoteMfr(chaseMemo);

  const sampleReq = String(fd['today for client'] || '').trim();
  const sampleSent = String(fd['Date WP Sample Sent'] || '').trim();
  const sampleOrdered = !!(sampleReq || sampleSent);

  const codeHasAlpha = /[A-Za-z]/.test(noteMfr || '');
  const codeDigits = String(noteMfr || mfrPattern || '').replace(/[^0-9]/g, '');
  const mfrPlaceholder = !codeHasAlpha && !!tail && codeDigits === tail;

  // BELONGS-TO-SKU guard (2026-08-27, critical): a record's Series+JS Pattern must
  // normalize to THIS dw_sku. A foreign record (e.g. Schumacher SCH|51526 that merely
  // shares the number 51526 with HSW-51526) is NOT this SKU and must NEVER be deleted,
  // even though it reads no-sample + placeholder. Mirrors candidates.mjs skuMatch.
  const normSku = (s) => String(s || '').toUpperCase().replace(/[^A-Z0-9]/g, '');
  const target = normSku(clean);
  const storedKey = normSku(String(fd.Series || '') + String(fd['JS Pattern'] || ''));
  const comboKey = normSku(fd['combo sku']);
  const skuMatch = !!target && (storedKey === target || comboKey === target);

  return { sampleOrdered, mfrPlaceholder, mfrPattern, noteMfr, skuMatch };
}

// ---- plan loading ----------------------------------------------------------
// Accepts either a plan JSON file, or an approved memo (parses the KEEP/DELETE
// blocks that vendor-sweep-analyze / the viewer wrote into the pending-approval memo).
function loadPlanFromJson(file) {
  const raw = JSON.parse(readFileSync(file, 'utf8'));
  // Support both a bare array and { plan: [...] } / { skus: [...] }.
  const arr = Array.isArray(raw) ? raw : (raw.plan || raw.skus || raw.deletions || []);
  return arr.map((e) => ({
    dw_sku: String(e.dw_sku || e.sku || '').trim(),
    keepRecordId: String(e.keepRecordId || e.keepRid || '').trim(),
    realMfr: String(e.realMfr || e.keepMfr || e.confirmed_real_mfr || '').trim(),
    deleteRecordIds: (e.deleteRecordIds || e.deleteRids || []).map(String),
  })).filter((e) => e.dw_sku);
}

// Parse an approved memo's "### <dw_sku>" / "KEEP ... master <id> ... confirmed mfr: <x>"
// / "DELETE masters: a, b" blocks (the format server.js /api/stage writes).
function loadPlanFromMemo(file) {
  const text = readFileSync(file, 'utf8');
  const out = [];
  const lines = text.split('\n');
  let cur = null;
  for (const ln of lines) {
    const h = ln.match(/^###\s+(.+?)\s*$/);
    if (h) { if (cur) out.push(cur); cur = { dw_sku: h[1].trim(), keepRecordId: '', realMfr: '', deleteRecordIds: [] }; continue; }
    if (!cur) continue;
    const keep = ln.match(/master\s+(\d+)/i); if (keep && !cur.keepRecordId) cur.keepRecordId = keep[1];
    const mfr = ln.match(/confirmed mfr:\s*([^\s]+)/i); if (mfr && !cur.realMfr) cur.realMfr = mfr[1];
    const del = ln.match(/DELETE masters?:\s*(.+?)\s*(?:\(|$)/i);
    if (del) { for (const m of del[1].split(/[,\s]+/)) { const d = m.replace(/[^0-9]/g, ''); if (d) cur.deleteRecordIds.push(d); } }
  }
  if (cur) out.push(cur);
  return out.filter((e) => e.dw_sku && e.deleteRecordIds.length);
}

// ---- snapshot before delete (REFUSE without a saved snapshot) --------------
async function snapshotRecord(rid, iso) {
  const { getRecord } = await fm();
  const rec = await getRecord(FM_DB, FM_LAYOUT, rid);
  if (!rec) throw new Error(`record ${rid} not found`);
  if (!existsSync(RESTORE_DIR)) mkdirSync(RESTORE_DIR, { recursive: true });
  const file = path.join(RESTORE_DIR, `${rid}-${iso}.json`);
  const snap = {
    recordId: rec.recordId || rid, db: FM_DB, layout: FM_LAYOUT,
    modId: rec.modId || null, captured_at: new Date().toISOString(),
    fieldData: rec.fieldData || {},
  };
  writeFileSync(file, JSON.stringify(snap, null, 2));
  if (!existsSync(file)) throw new Error(`snapshot write failed for ${rid}`);
  return { file, fieldData: rec.fieldData || {} };
}

function ledger(action, extra) {
  try {
    execFileSync('node', [LOG_EXEC,
      '--agent', AGENT, '--ticket', TICKET, '--action', action,
      '--blast', String(extra.blast || 1),
      '--undo', extra.undo || 'recreate WALLPAPER record from data/restore snapshot',
      '--verify', extra.verify || 'candidatesForSku(dw_sku) no longer lists the deleted rid',
    ], { stdio: 'pipe' });
  } catch (e) { console.error('  ! ledger failed:', e.message); }
}

// ---------------------------------------------------------------------------
async function main() {
  if (!PLAN_FILE && !MEMO_FILE) {
    console.error('ERROR: pass --plan <file.json> or --memo <memo.md>');
    process.exit(2);
  }
  const plan = PLAN_FILE ? loadPlanFromJson(PLAN_FILE) : loadPlanFromMemo(MEMO_FILE);
  if (!plan.length) { console.error('ERROR: plan is empty — nothing to do.'); process.exit(2); }

  const totalDeletes = plan.reduce((n, e) => n + e.deleteRecordIds.length, 0);
  console.log(`\n=== execute-sweep ${APPLY ? 'APPLY (LIVE DESTRUCTIVE)' : 'DRY-RUN (no writes)'} ===`);
  console.log(`plan source : ${PLAN_FILE || MEMO_FILE}`);
  console.log(`SKUs        : ${plan.length}`);
  console.log(`deletes     : ${totalDeletes}   (blast cap: ${MAX})`);
  console.log(`mfr-fixes   : ${plan.filter((e) => e.keepRecordId && e.realMfr).length}`);
  console.log(`ticket      : ${TICKET}   agent: ${AGENT}\n`);

  if (totalDeletes > MAX) {
    console.error(`>>> BLAST CAP: ${totalDeletes} deletes exceeds --max ${MAX}. Re-run with --max ${totalDeletes} to proceed.`);
    process.exit(3);
  }
  if (totalDeletes > 500) {
    console.error(`>>> HEADS-UP: ${totalDeletes} deletes exceeds the standing 500-item bound. Proceeding because --max=${MAX} was set explicitly.`);
  }

  const iso = new Date().toISOString().replace(/[:.]/g, '-');
  let deleted = 0, mfrFixed = 0, skippedGuard = 0, skippedMismatch = 0, snapFail = 0;

  for (let e of plan) {
    console.log(`--- ${e.dw_sku} ---`);
    const { getRecord } = await fm();

    // ---- SANITY GUARD (Cody hole #1): the keepRecordId must NEVER also be in the
    // delete set. A malformed/stale plan that lists the same rid as keep AND delete
    // would otherwise write mfr onto it then delete it. Drop the keep-rid from the
    // delete list AND refuse the whole SKU (a plan this broken is not trustworthy). ----
    const keepRid = String(e.keepRecordId || '');
    const dedupedDeletes = [...new Set(e.deleteRecordIds.map(String))].filter((r) => r && r !== keepRid);
    if (dedupedDeletes.length !== e.deleteRecordIds.length) {
      console.log(`  GUARD: plan listed keep=${keepRid} inside its own delete set (or dup rids) — DROPPING keep-rid + dups from deletes.`);
    }
    e = { ...e, deleteRecordIds: dedupedDeletes };
    if (!e.deleteRecordIds.length) {
      console.log(`  (no deletes remain after keep/dup sanitation)`);
      // still allow the KEEP mfr-write below to run; fall through
    }

    // Re-read every record in the plan (keep + deletes) to re-verify at execute time.
    const allRids = [...new Set([e.keepRecordId, ...e.deleteRecordIds].filter(Boolean))];
    const live = {};
    for (const rid of allRids) {
      try { live[rid] = await getRecord(FM_DB, FM_LAYOUT, rid); }
      catch { live[rid] = null; }
    }

    // ---- KEEP-worthiness guard (Cody hole #3): don't trust the plan blindly.
    // The named keep record must ACTUALLY be keep-worthy (sample-ordered OR a real
    // alpha mfr). If it's a placeholder-no-sample record, the plan is inverted — the
    // "keep" is itself a broken master — so refuse ALL deletes for this SKU. ----
    if (e.deleteRecordIds.length && live[keepRid]) {
      const kc = classifyRecord(live[keepRid].fieldData || {}, e.dw_sku);
      const keepWorthy = kc.sampleOrdered || (!kc.mfrPlaceholder && /[A-Za-z]/.test(kc.noteMfr || kc.mfrPattern || ''));
      if (!keepWorthy) {
        console.log(`  GUARD: named keep ${keepRid} is NOT keep-worthy (no sample + placeholder mfr) — REFUSING all deletes AND the mfr-write for ${e.dw_sku} (inverted plan, untrusted).`);
        skippedGuard += e.deleteRecordIds.length;
        e = { ...e, deleteRecordIds: [], realMfr: '' }; // also don't trust the plan's realMfr
      }
    } else if (e.deleteRecordIds.length && !live[keepRid]) {
      // keep record missing at execute time — cannot prove a survivor; refuse deletes.
      console.log(`  GUARD: named keep ${keepRid} not found live — cannot guarantee a survivor, REFUSING all deletes for ${e.dw_sku}.`);
      skippedGuard += e.deleteRecordIds.length;
      e = { ...e, deleteRecordIds: [] };
    }

    // never-delete-to-zero: how many records for this SKU will REMAIN?
    // A record survives if it is NOT in the confirmed delete set (after re-verify).
    const confirmedDeletes = [];
    for (const rid of e.deleteRecordIds) {
      const rec = live[rid];
      if (!rec) { console.log(`  SKIP delete ${rid}: record no longer exists`); skippedMismatch++; continue; }
      const c = classifyRecord(rec.fieldData || {}, e.dw_sku);
      if (!c.skuMatch) { console.log(`  SKIP delete ${rid}: does NOT belong to ${e.dw_sku} (foreign record ${rec.fieldData?.Series || ''}|${rec.fieldData?.['JS Pattern'] || ''}) — NEVER delete a different SKU's record`); skippedMismatch++; continue; }
      if (c.sampleOrdered) { console.log(`  SKIP delete ${rid}: now has SAMPLE history (sampleOrdered) — KEEP`); skippedMismatch++; continue; }
      if (!c.mfrPlaceholder) { console.log(`  SKIP delete ${rid}: mfr no longer a placeholder ("${c.mfrPattern}") — KEEP`); skippedMismatch++; continue; }
      confirmedDeletes.push(rid);
    }

    // never-to-zero guard: the keep record must exist & survive, OR at least one
    // non-deleted record for this SKU must remain.
    const keepAlive = e.keepRecordId && live[e.keepRecordId];
    if (!confirmedDeletes.length) {
      console.log(`  (no confirmed deletes after re-verify)`);
    }
    if (confirmedDeletes.length && !keepAlive) {
      // Would we be deleting every re-read record? Guard: refuse if nothing survives.
      const survivors = allRids.filter((r) => live[r] && !confirmedDeletes.includes(r));
      if (!survivors.length) {
        console.log(`  GUARD: would delete SKU to zero (no keep survives) — SKIPPING all deletes for ${e.dw_sku}`);
        skippedGuard += confirmedDeletes.length;
        confirmedDeletes.length = 0;
      }
    }

    // ---- KEEP: write real mfr onto Mfr Pattern iff current is placeholder ----
    if (e.keepRecordId && e.realMfr && keepAlive) {
      const c = classifyRecord(live[e.keepRecordId].fieldData || {}, e.dw_sku);
      const oldVal = String(live[e.keepRecordId].fieldData['Mfr Pattern'] || '');
      if (c.mfrPlaceholder) {
        if (APPLY) {
          try {
            const { updateRecord } = await fm();
            const r = await updateRecord(FM_DB, FM_WRITE_LAYOUT, e.keepRecordId, { 'Mfr Pattern': e.realMfr }, { dryRun: false });
            if (r.committed) {
              console.log(`  KEEP ${e.keepRecordId}: Mfr Pattern "${oldVal}" -> "${e.realMfr}" [committed]`);
              mfrFixed++;
              ledger(`mfr-master-sweep set Mfr Pattern on kept master ${e.keepRecordId} (${e.dw_sku}): "${oldVal}" -> "${e.realMfr}"`, {
                blast: 1,
                undo: `fm updateRecord WALLPAPER '${FM_LAYOUT}' ${e.keepRecordId} {"Mfr Pattern":"${oldVal}"}`,
                verify: `getRecord ${e.keepRecordId}.Mfr Pattern == "${e.realMfr}"`,
              });
            } else {
              console.log(`  KEEP ${e.keepRecordId}: no change (${r.note || 'values matched'})`);
            }
          } catch (err) { console.log(`  ! KEEP mfr-write failed for ${e.keepRecordId}: ${err.message}`); }
        } else {
          console.log(`  KEEP ${e.keepRecordId}: WOULD set Mfr Pattern "${oldVal}" -> "${e.realMfr}" (dry-run)`);
        }
      } else {
        console.log(`  KEEP ${e.keepRecordId}: Mfr Pattern "${oldVal}" is not a placeholder — leaving as-is`);
      }
    } else if (e.keepRecordId && e.realMfr && !keepAlive) {
      console.log(`  ! keep record ${e.keepRecordId} not found live — mfr-write skipped`);
    }

    // ---- DELETE confirmed records (snapshot-first, refuse w/o snapshot) ------
    for (const rid of confirmedDeletes) {
      let snap;
      try { snap = await snapshotRecord(rid, iso); }
      catch (err) { console.log(`  ! SNAPSHOT FAILED for ${rid} (${err.message}) — REFUSING to delete`); snapFail++; continue; }

      if (!APPLY) {
        console.log(`  DELETE ${rid}: WOULD delete (snapshot saved: ${path.relative(ROOT, snap.file)}) (dry-run)`);
        continue;
      }
      // snapshot exists on disk — safe to delete.
      try {
        const { deleteRecord } = await fm();
        await deleteRecord(FM_DB, FM_WRITE_LAYOUT, rid);
        console.log(`  DELETE ${rid}: deleted [committed]  (restore: ${path.relative(ROOT, snap.file)})`);
        deleted++;
        ledger(`mfr-master-sweep DELETE broken no-sample master ${rid} (${e.dw_sku})`, {
          blast: 1,
          undo: `cd ${ROOT} && node scripts/restore-record.mjs ${path.relative(ROOT, snap.file)} --apply`,
          verify: `candidatesForSku('${e.dw_sku}') no longer lists rid ${rid}`,
        });
      } catch (err) { console.log(`  ! DELETE failed for ${rid}: ${err.message}`); }
    }
    console.log('');
  }

  console.log('=== SUMMARY ===');
  console.log(`  deleted          : ${deleted}${APPLY ? '' : ' (dry-run: 0 actually deleted)'}`);
  console.log(`  mfr-fixed        : ${mfrFixed}${APPLY ? '' : ' (dry-run: 0 actually written)'}`);
  console.log(`  skipped-guard    : ${skippedGuard}   (would delete-to-zero)`);
  console.log(`  skipped-mismatch : ${skippedMismatch}   (predicate no longer holds at execute time)`);
  console.log(`  snapshot-failed  : ${snapFail}   (delete refused — no reversibility record)`);
  console.log(APPLY ? '\n[APPLIED — LIVE writes executed + ledgered]' : '\n[DRY-RUN — nothing was written. Add --apply to execute.]');
}

main().catch((e) => { console.error('FATAL:', e.message); process.exit(1); });