`scripts/enrich-disco.js`

72 lines
// enrich-disco.js — stamp `disco` (Date Discontinued) onto samples already in data/lists.json,
// sourced from the LOCAL dw_unified.disco_items mirror (combo_sku → date_discontinued), which is
// itself loaded from filemaker:WALLPAPER.Date Discontinued. Fast + $0 local: lets us refresh the
// "sampled item is discontinued" flag WITHOUT a full FileMaker rebuild. (Steve 2026-07-13)
//
// Usage: node scripts/enrich-disco.js
import fs from 'node:fs';
import path from 'node:path';
import { fileURLToPath } from 'node:url';
import { execFileSync } from 'node:child_process';

const __dirname = path.dirname(fileURLToPath(import.meta.url));
const LISTS = path.join(__dirname, '..', 'data', 'lists.json');
const PSQL = process.env.PSQL_BIN || '/opt/homebrew/opt/postgresql@14/bin/psql';
const CONN = process.env.DW_UNIFIED_URL || 'postgresql://localhost/dw_unified';

// MM/DD/YYYY → ISO YYYY-MM-DD ('' on parse fail).
function toISO(s) {
  const m = String(s || '').trim().match(/^(\d{1,2})\/(\d{1,2})\/(\d{2,4})$/);
  if (!m) return '';
  let [, mo, d, y] = m; if (y.length === 2) y = (Number(y) > 50 ? '19' : '20') + y;
  return `${y}-${mo.padStart(2, '0')}-${d.padStart(2, '0')}`;
}
const key = (s) => String(s || '').trim().toUpperCase();

const payload = JSON.parse(fs.readFileSync(LISTS, 'utf8'));
const lists = [payload.list1 || [], payload.list2 || []];

// Collect every sample combo-sku referenced across list1/list2.
const skus = new Set();
for (const list of lists) for (const r of list)
  for (const it of [...(r.samples_sent || []), ...(r.samples_pending || [])])
    { const k = it.sku || it.label; if (k) skus.add(key(k)); }

console.log(`[enrich-disco] ${skus.size} distinct sample sku(s) to check against disco_items`);
if (!skus.size) { console.log('[enrich-disco] nothing to do'); process.exit(0); }

// One bulk query: combo_sku → most-recent date_discontinued.
const arr = '{' + [...skus].map((s) => '"' + s.replace(/[^A-Za-z0-9._-]/g, '') + '"').join(',') + '}';
const q = `SELECT upper(combo_sku), max(date_discontinued) FROM disco_items
           WHERE upper(combo_sku) = ANY('${arr}') AND coalesce(date_discontinued,'') <> ''
           GROUP BY 1`;
let out = '';
try {
  out = execFileSync(PSQL, [CONN, '-tAF', '\t', '-c', q], { maxBuffer: 256 * 1024 * 1024 }).toString();
} catch (e) {
  // psql absent / DB down / query error → leave lists.json untouched (safe) and exit non-zero.
  console.error('[enrich-disco] psql failed:', e.message);
  process.exit(1);
}
const discoMap = new Map();
for (const line of out.split('\n')) {
  if (!line.trim()) continue;
  const [sku, date] = line.split('\t');
  const iso = toISO(date); if (iso) discoMap.set(sku, iso);
}
console.log(`[enrich-disco] ${discoMap.size} sku(s) matched a discontinued date`);

// Stamp `disco` onto each sample item; tally affected rows.
let sentDead = 0, rowsAllDead = 0, rowsSomeDead = 0;
for (const list of lists) for (const r of list) {
  const sent = Array.isArray(r.samples_sent) ? r.samples_sent : [];
  for (const it of [...sent, ...(r.samples_pending || [])])
    it.disco = discoMap.get(key(it.sku || it.label)) || '';
  const dead = sent.filter((s) => s.disco).length;
  if (sent.length && dead) { sentDead += dead; if (dead === sent.length) rowsAllDead++; else rowsSomeDead++; }
}
console.log(`[enrich-disco] stamped ${sentDead} discontinued sent-sample line(s); ${rowsAllDead} row(s) ALL-dead, ${rowsSomeDead} row(s) partial`);

fs.writeFileSync(LISTS, JSON.stringify(payload, null, 2));
console.log('[enrich-disco] wrote', LISTS, '($0 — local dw_unified read)');