`scripts/vendor-sweep-analyze.mjs`

146 lines
#!/usr/bin/env node
// vendor-sweep-analyze.mjs — READ-ONLY batch analyzer for the "DW# == Mfr SKU" sweep.
//
// For ONE vendor, enumerate the DW#==mfr placeholder SKUs from dw_unified
// (mfr_sku == numeric tail of dw_sku, numeric-only i.e. no real alpha code), then
// per SKU reuse lib/candidates.mjs candidatesForSku() to resolve the FileMaker
// masters + the delete/keep suggestion, and aggregate a plan JSON.
//
// PURELY READ-ONLY: psql SELECTs + FileMaker _find reads via candidatesForSku.
// Writes ONLY its own plan JSON to data/plans/<vendor>-<ISO>.json. Never touches
// dw_unified, Shopify, or FileMaker records. Paces FM reads gently.
//
// Usage:
//   node scripts/vendor-sweep-analyze.mjs "Glitter Walls"
//   node scripts/vendor-sweep-analyze.mjs "Glitter Walls" --limit 20   # sample run

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

const __dir = path.dirname(fileURLToPath(import.meta.url));
const ROOT = path.join(__dir, '..');
const PLANS_DIR = path.join(ROOT, 'data', 'plans');
const PSQL = process.env.PSQL_BIN || '/opt/homebrew/opt/postgresql@14/bin/psql';

function arg(k) { const i = process.argv.indexOf('--' + k); return i >= 0 ? process.argv[i + 1] : undefined; }
const VENDOR = process.argv.slice(2).find((a) => !a.startsWith('--'));
const LIMIT = arg('limit') ? Number(arg('limit')) : null;
const PACE_MS = arg('pace') ? Number(arg('pace')) : 250; // gentle FM pacing

if (!VENDOR) { console.error('Usage: vendor-sweep-analyze.mjs "<vendor>" [--limit N]'); process.exit(2); }

function psql(sql) {
  return new Promise((resolve) => {
    execFile(PSQL, ['dw_unified', '-tAc', sql], { maxBuffer: 1024 * 1024 * 64 }, (err, stdout) => {
      if (err) return resolve([]);
      resolve((stdout || '').trim().split('\n').filter(Boolean));
    });
  });
}

// Enumerate the vendor's DW#==mfr placeholder base SKUs (strip -Sample, dedupe).
async function enumerateSkus(vendor) {
  const esc = vendor.replace(/'/g, "''");
  // SCOPE (Steve 2026-08-27, "only look at active patterns"): ACTIVE-only.
  const statusFilter = (process.env.STATUS_FILTER || 'ACTIVE').trim().toUpperCase();
  const statusClause = statusFilter === 'ALL' ? '' : `\n      AND status='${statusFilter.replace(/'/g, "''")}'`;
  const sql = `
    SELECT DISTINCT regexp_replace(dw_sku, '[-_ ]?[Ss]ample$', '') AS base_sku
    FROM shopify_products
    WHERE vendor ILIKE '%${esc}%'${statusClause}
      AND mfr_sku IS NOT NULL AND mfr_sku <> ''
      AND mfr_sku !~ '[A-Za-z]'
      AND regexp_replace(coalesce(mfr_sku,''),'[^0-9]','','g') <> ''
      AND regexp_replace(coalesce(mfr_sku,''),'[^0-9]','','g')
        = regexp_replace(coalesce(dw_sku,''),'[^0-9]','','g')
    ORDER BY base_sku`;
  const rows = await psql(sql);
  return rows.map((r) => r.trim()).filter(Boolean);
}

async function main() {
  console.log(`\n=== vendor-sweep-analyze (READ-ONLY): "${VENDOR}" ===`);
  let skus = await enumerateSkus(VENDOR);
  const totalFound = skus.length;
  if (LIMIT) skus = skus.slice(0, LIMIT);
  console.log(`DW#==mfr placeholder SKUs found: ${totalFound}${LIMIT ? ` (analyzing first ${skus.length})` : ''}\n`);

  const { candidatesForSku } = await import('file://' + path.join(ROOT, 'lib', 'candidates.mjs'));

  const plan = [];
  let recordsToDelete = 0, mastersToFix = 0, skippedGuard = 0, noFmMatch = 0;
  const sampleReport = [];

  for (let i = 0; i < skus.length; i++) {
    const sku = skus[i];
    let out;
    try { out = await candidatesForSku(sku); }
    catch (e) { console.log(`  ${sku}: candidatesForSku error — ${e.message}`); noFmMatch++; continue; }

    const fmRecs = out.filemaker || [];
    const sug = out.suggestion || { realMfr: '', keepRid: '', deleteRids: [] };

    if (!fmRecs.length) { noFmMatch++; if (i < skus.length) process.stdout.write(`.`); continue; }

    const deleteRids = (sug.deleteRids || []).map(String);
    if (!deleteRids.length) {
      // no delete suggested — either nothing to prune or the guard blocked it
      // (guard = no keeper survives). Detect the guard case: placeholders exist but canPrune=false.
      const placeholders = fmRecs.filter((r) => r.mfrPlaceholder && !r.sampleOrdered);
      const keepers = fmRecs.filter((r) => r.sampleOrdered || (!r.mfrPlaceholder && /[A-Za-z]/.test(r.noteMfr || r.mfrPattern || '')));
      if (placeholders.length && !keepers.length) { skippedGuard++; }
      if (i < skus.length) process.stdout.write(`.`);
      await new Promise((r) => setTimeout(r, PACE_MS));
      continue;
    }

    plan.push({
      dw_sku: sku,
      keepRecordId: String(sug.keepRid || ''),
      realMfr: String(sug.realMfr || ''),
      deleteRecordIds: deleteRids,
    });
    recordsToDelete += deleteRids.length;
    if (sug.keepRid && sug.realMfr) mastersToFix++;
    if (sampleReport.length < 6) {
      sampleReport.push({ dw_sku: sku, keep: sug.keepRid, realMfr: sug.realMfr, delete: deleteRids });
    }
    process.stdout.write(`+`);
    await new Promise((r) => setTimeout(r, PACE_MS));
  }
  console.log('\n');

  // ---- write the plan JSON ----
  if (!existsSync(PLANS_DIR)) mkdirSync(PLANS_DIR, { recursive: true });
  const iso = new Date().toISOString().replace(/[:.]/g, '-');
  const slug = VENDOR.replace(/[^A-Za-z0-9]+/g, '-').replace(/(^-|-$)/g, '');
  const planFile = path.join(PLANS_DIR, `${slug}-${iso}.json`);
  const counts = {
    vendor: VENDOR,
    generated_at: new Date().toISOString(),
    skus_analyzed: skus.length,
    skus_found_total: totalFound,
    skus_with_deletes: plan.length,
    records_to_delete: recordsToDelete,
    masters_to_fix: mastersToFix,
    skus_skipped_by_guard: skippedGuard,
    skus_no_fm_match: noFmMatch,
  };
  writeFileSync(planFile, JSON.stringify({ counts, plan }, null, 2));

  console.log('=== COUNTS ===');
  for (const [k, v] of Object.entries(counts)) console.log(`  ${k.padEnd(22)}: ${v}`);
  console.log(`\nplan written: ${path.relative(ROOT, planFile)}`);
  if (sampleReport.length) {
    console.log('\n=== sample SKUs ===');
    for (const s of sampleReport) {
      console.log(`  ${s.dw_sku}  keep=${s.keep || '(none)'}  realMfr=${s.realMfr || '(none)'}  delete=[${s.delete.join(', ')}]`);
    }
  }
  console.log('\n[READ-ONLY — no records written. Execute with scripts/execute-sweep.mjs --plan <file> (dry-run) then --apply]');
}

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