`plan-grs-dedup.mjs`

43 lines
// Build a GRS duplicate-master DEDUP PLAN from the FM dump (read-only, no writes).
// For each combo sku with >1 FileMaker master: pick a KEEPER, list the EXTRAS to delete
// with full field data saved for restore. Deletion itself is GATED (drafted, not run).
import fs from 'fs';
const Q = String.fromCharCode(34);
function P(t) { const R = []; let i = 0, f = '', r = [], q = false; while (i < t.length) { const c = t[i]; if (q) { if (c === Q) { if (t[i + 1] === Q) { f += Q; i++; } else q = false; } else f += c; } else { if (c === Q) q = true; else if (c === ',') { r.push(f); f = ''; } else if (c === '\n') { r.push(f); R.push(r); r = []; f = ''; } else if (c !== '\r') f += c; } i++; } if (f.length || r.length) { r.push(f); R.push(r); } return R; }
const norm = s => (s || '').toUpperCase().replace(/[^A-Z0-9]/g, '');
const raw = P(fs.readFileSync(process.env.HOME + '/Desktop/GRS_fm_all_records_20260819.csv', 'utf8'));
const h = raw.shift(); const ix = Object.fromEntries(h.map((x, i) => [x, i]));

function realCode(mfr, js) { const up = (mfr || '').toUpperCase().trim(); if (!up || /DO NOT SELL|CONFUSING|TEST|DELETE|DISCONTINUE|NEED SAMPLE/.test(up)) return null; const m = up.match(/\b([A-Z]{1,4}\d{1,6}[A-Z]?)\b/); if (m && m[1] !== norm(js)) return m[1]; return null; }
// richness score: has real code + populated fields
function score(r) { let s = 0; if (realCode(r[ix.mfr_pattern], r[ix.js_pattern])) s += 100; if (r[ix.jpg_name]) s += 10; if (r[ix.supplier]) s += 5; if (r[ix.net_price]) s += 3; s += Object.values(r).filter(Boolean).length; return s; }

const g = new Map();
for (const r of raw) { if (!r.length || !r[ix.combo_sku]) continue; const c = norm(r[ix.combo_sku]); (g.get(c) || g.set(c, []).get(c)).push(r); }

let dupGroups = 0, extras = 0, conflictGroups = 0;
const keepRows = [], delRows = [];
for (const [combo, recs] of g) {
  if (recs.length < 2) continue;
  dupGroups++;
  const codes = new Set(recs.map(r => realCode(r[ix.mfr_pattern], r[ix.js_pattern])).filter(Boolean));
  const conflict = codes.size > 1;
  if (conflict) conflictGroups++;
  recs.sort((a, b) => score(b) - score(a) || (+a[ix.fm_record_id]) - (+b[ix.fm_record_id]));
  const keeper = recs[0];
  keepRows.push([combo, keeper[ix.fm_record_id], keeper[ix.mfr_pattern], recs.length, conflict ? 'CONFLICT' : 'agree']);
  for (const r of recs.slice(1)) { extras++; delRows.push([combo, r[ix.fm_record_id], r[ix.mfr_pattern], r[ix.js_pattern], r[ix.series], r[ix.supplier], r[ix.net_price], r[ix.jpg_name], keeper[ix.fm_record_id], conflict ? 'CONFLICT-HOLD' : 'safe-delete']); }
}
const qq = x => Q + String(x ?? '').replace(/"/g, Q + Q) + Q;
const delFile = process.env.HOME + '/Desktop/GRS_dedup_DELETE_plan_20260819.csv';
fs.writeFileSync(delFile, 'combo,delete_record_id,mfr_pattern,js_pattern,series,supplier,net,jpg,keeper_record_id,disposition\n' + delRows.map(r => r.map(qq).join(',')).join('\n') + '\n');
const safeDel = delRows.filter(r => r[9] === 'safe-delete').length;
console.log('=== GRS duplicate-master DEDUP PLAN ===');
console.log('  duplicate combo groups:', dupGroups);
console.log('  of those, code-CONFLICT groups (masters disagree — HOLD, manual):', conflictGroups);
console.log('  extra records (keeper picked, these are the dupes):', extras);
console.log('  -> SAFE to delete (dupes where masters agree/keeper clear):', safeDel);
console.log('  -> HOLD (conflict groups, need human pick):', extras - safeDel);
console.log('  plan CSV ->', delFile);
console.log('\n  keeper rule: highest richness (real code > jpg > supplier > populated fields), tie -> lowest record_id');