`scripts/fmpro.mjs`

278 lines
#!/usr/bin/env node
// FMPro write-back engine for the sample follow-up system.
//
// Two writes to the WALLPAPER (WALLPAPER2) memo records, keyed by the vendor's
// `vid` + the 10–60 day outstanding window:
//   • "Date Email Sent to Vendor after 10 Days" — stamped the day the follow-up email goes out.
//   • "Priority Memo Notes"                     — a plain-language note when the vendor replies.
//
// Reuses the filemaker-mcp client (FileMaker Cloud / Claris ID auth) so we never
// re-implement the Cognito handshake. Creds are read from ~/.claude.json's
// mcpServers.filemaker.env block (single source of truth) at runtime.
//
// CLI:
//   node scripts/fmpro.mjs backfill                       # stamp every already-sent vendor's outstanding SKUs
//   node scripts/fmpro.mjs stamp --vid DGD --date 08/14/2026
//   node scripts/fmpro.mjs note  --record 536324 --text "Discontinued"
//   node scripts/fmpro.mjs note  --vid DGD --sku DWKK123171 --text "Sending now"
//
// server.js spawns `stamp --vid <vid> --date <today>` after a successful send so
// the FileMaker field is recorded automatically (the gap this whole build closes).

import { readFileSync, writeFileSync, mkdirSync } from 'node:fs';
import { fileURLToPath } from 'node:url';
import { dirname, join } from 'node:path';
import { homedir } from 'node:os';

const __dir = dirname(fileURLToPath(import.meta.url));
const ROOT = join(__dir, '..');

const DB = 'WALLPAPER';
const LAYOUT = 'Report for old memo samples';     // exposes vid, combo sku, today for client, Date WP Sample Sent
const FIELD_SENT = 'Date Email Sent to Vendor after 10 Days';
// Steve 8/25: on a real send, ALSO stamp the 2nd-request date. No dedicated "2nd request date"
// field is exposed to the Data API; this real date field on the same layout is the chosen target.
const FIELD_2ND_DATE = 'Date Sample Request Letter Sent';
const FIELD_NOTES = 'Priority Memo Notes';
const MIN_AGE_DAYS = 10;   // don't chase newer than 10 days
const MAX_AGE_DAYS = 60;   // >60 = dead lead, don't chase (Steve's rule)

// --- load FileMaker Cloud creds from the canonical MCP config, then the client ---
function loadFmEnv() {
  const cfg = JSON.parse(readFileSync(join(homedir(), '.claude.json'), 'utf8'));
  const env = cfg?.mcpServers?.filemaker?.env || {};
  for (const k of ['FM_CLOUD_HOST', 'FM_CLARIS_EMAIL', 'FM_CLARIS_PASSWORD']) {
    if (!env[k]) throw new Error(`~/.claude.json mcpServers.filemaker.env.${k} missing`);
    process.env[k] = env[k];
  }
  process.env.FM_READONLY = '0'; // this engine writes
}
loadFmEnv();
const fm = await import('/Users/macstudio3/Projects/filemaker-mcp/src/fm-client.js');

// --- date helpers (FileMaker wants MM/DD/YYYY; dates are judged in Pacific time) ---
const pad = (n) => String(n).padStart(2, '0');
function fmtDate(d) { return `${pad(d.getMonth() + 1)}/${pad(d.getDate())}/${d.getFullYear()}`; }
function ptDateFromISO(iso) {
  // Convert an ISO instant to a MM/DD/YYYY calendar date in America/Los_Angeles.
  const s = new Date(iso).toLocaleDateString('en-US', { timeZone: 'America/Los_Angeles' }); // M/D/YYYY
  const [m, day, y] = s.split('/');
  return `${pad(m)}/${pad(day)}/${y}`;
}
function windowRange(today = new Date()) {
  const lo = new Date(today); lo.setDate(lo.getDate() - MAX_AGE_DAYS); // oldest still chased
  const hi = new Date(today); hi.setDate(hi.getDate() - MIN_AGE_DAYS); // newest chased (>10d)
  return `${fmtDate(lo)}...${fmtDate(hi)}`;
}

// --- match ONE page SKU to its live memo record, exactly ---
// Keyed by the exact DW# (`combo sku`) shown on the console — NOT vid (FileMaker text
// find is begins-with-word, so vid over-matches: "SAN"→SANDB, "GREEN"→unrelated rows).
// `==` forces an exact match; `"="` matches an EMPTY field, so already-stamped/fulfilled
// rows are excluded (idempotent). Disambiguated by request date when a SKU repeats.
async function matchPageSku(dw, requested, pass = 1) {
  // Require BOTH the exact DW# and the exact request date. A DW# alone maps to many
  // records (the live memo + blank pattern-masters + year-old orders); the request
  // date pins it to the one current memo. No request date = don't guess, skip.
  if (!dw || !String(dw).trim() || !requested) return [];
  // TK-11409 (Steve, 2026-09-10): the 2nd request must also update the WALLPAPER file.
  // pass 1 (first chase)  -> rows whose FIELD_SENT is still EMPTY.
  // pass 2 (the resend)   -> rows already stamped by pass 1 whose 2nd-request field is
  //                          still EMPTY. Keying pass 2 off `FIELD_SENT: '*'` (non-empty)
  //                          is what makes the resend visible at all: the old query hard-
  //                          coded `FIELD_SENT: '='` for every send, so on a resend it
  //                          matched ZERO records and silently wrote nothing.
  const query = {
    'combo sku': `==${dw}`,
    'today for client': `==${requested}`,   // exact page-row request date
    'Date WP Sample Sent': '=',             // still outstanding
    ...(pass === 2
      ? { [FIELD_SENT]: '*', [FIELD_2ND_DATE]: '=' }   // chased once, not yet 2nd-requested
      : { [FIELD_SENT]: '=' }),                        // not yet stamped
  };
  try {
    const { records } = await fm.findRecords(DB, LAYOUT, query, { limit: 50 });
    return records.map((r) => ({
      recordId: r.recordId,
      sku: r.fieldData['combo sku'] || '',
      client: (r.fieldData['company for client fileS'] || r.fieldData['Clients::Company'] || '').trim(),
      requested: r.fieldData['today for client'] || '',
    }));
  } catch (e) {
    if (e.fmCode === '401') return []; // no records found
    throw e;
  }
}

// --- plan/stamp one vendor from its exact page SKUs (fleet.json items) ---
async function planVendor(v, pass = 1) {
  // TK-11409 gate: pass 2 asserts "a 2nd request was sent", so it requires positive
  // evidence that one actually was. Returns a REASON, never a bare empty list — a silent
  // zero-row result is the exact failure this ticket exists to kill.
  if (pass === 2) {
    const ev = sendEvidence(v.slug);
    if (!ev.known) { v._skip = `NO SEND RECORD for ${v.slug} (${ev.addrs.join(', ') || 'no address'}) — cannot assert a 2nd request; refusing`; return []; }
    if (ev.count < 2) { v._skip = `only ${ev.count} recorded send(s) for ${v.slug} — no 2nd request happened; refusing`; return []; }
  }
  const seen = new Set();
  const rows = [];
  for (const it of (v.items || [])) {
    const matches = await matchPageSku(it.dw, it.initialReq || it.req, pass);
    for (const m of matches) {
      if (seen.has(m.recordId)) continue;
      seen.add(m.recordId);
      rows.push({ ...m, date: v.date, vid: v.vid, vendor: v.name });
    }
  }
  return rows;
}
async function stampVendor(v, pass = 1) {
  const rows = await planVendor(v, pass);
  const stamped = [];
  for (const r of rows) {
    // Pass 1 writes ONLY the 10-day chase date, so the 2nd-request field stays free as a
    // real slot. (It used to write both on the first send, which left request #2 nowhere
    // to record itself.) Pass 2 writes ONLY the 2nd-request field, preserving the 1st date.
    const fields = pass === 2 ? { [FIELD_2ND_DATE]: r.date } : { [FIELD_SENT]: r.date };
    const res = await fm.updateRecord(DB, LAYOUT, r.recordId, fields, { dryRun: false });
    if (res.committed) stamped.push(r);
  }
  // `skip` distinguishes "refused for lack of evidence" from "nothing left to do" —
  // both yield 0 rows, and conflating them is what made the original bug invisible.
  return { vid: v.vid, date: v.date, pass, count: stamped.length, found: rows.length, stamped, skip: v._skip || null };
}

// --- write a plain-language reply note to a specific memo record ---
async function noteRecord(recordId, text) {
  const res = await fm.updateRecord(DB, LAYOUT, recordId, { [FIELD_NOTES]: text }, { dryRun: false });
  return { recordId, text, committed: !!res.committed };
}
async function findRecordIdByVidSku(vid, sku) {
  const { records } = await fm.findRecords(DB, LAYOUT, { vid, 'combo sku': `==${sku}`, 'Date WP Sample Sent': '=' }, { limit: 10 });
  return records[0]?.recordId || null;
}

// --- fmpro-posted state (drives the console chip) ---
const POSTED = join(ROOT, 'data', 'fmpro-posted.json');
function readPosted() { try { return JSON.parse(readFileSync(POSTED, 'utf8')); } catch { return { byVid: {} }; } }
function writePosted(p) { mkdirSync(join(ROOT, 'data'), { recursive: true }); writeFileSync(POSTED, JSON.stringify(p, null, 2)); }
function recordPosted(vid, r) {
  const p = readPosted();
  const prev = p.byVid[vid] || { count: 0 };
  p.byVid[vid] = { date: r.date, count: (prev.count || 0) + r.count, lastPostedAt: new Date().toISOString() };
  writePosted(p);
}

// --- how many times has this vendor actually been emailed? (TK-11409) ---
// The evidence oracle for pass 2. DB field state is NOT contact history: rows stamped
// before 2026-08-25 (commit 32145b7, when the 2nd-request field started being written)
// carry a chase date with an EMPTY 2nd-request field purely because that field did not
// exist yet — 8 such legacy rows are live right now. Matching on field-emptiness alone
// would read those as "chased once, awaiting a 2nd request" and fabricate a 2nd-request
// date for a resend that never happened. So pass 2 additionally demands positive evidence
// from sent.json, the record of what was actually sent.
// Returns { count, addrs, known } — `known:false` means we have NO send record for this
// vendor at all, which is a DIFFERENT condition from "sent once" and must never be
// silently treated as "no work to do".
function sendEvidence(slug) {
  let contacts = {}, sent = {};
  try { contacts = JSON.parse(readFileSync(join(ROOT, 'data', 'contacts.json'), 'utf8')); } catch (e) {}
  try { sent = JSON.parse(readFileSync(join(ROOT, 'data', 'sent.json'), 'utf8')).byEmail || {}; } catch (e) {}
  const norm = (s) => String(s || '').toLowerCase().trim();
  const sentMap = {}; for (const [k, v] of Object.entries(sent)) sentMap[norm(k)] = v;
  const addrs = norm((contacts[slug] || {}).sample_email).split(',').map((a) => a.trim()).filter((a) => a.includes('@'));
  const hits = addrs.map((a) => sentMap[a]).filter(Boolean);
  if (!hits.length) return { count: 0, addrs, known: false };
  // The vendor was chased N times only if EVERY recipient received N — a resend goes to
  // the whole address list, so the minimum is the honest count.
  return { count: Math.min(...hits.map((h) => h.count || 1)), addrs, known: true };
}

// --- sent-vendor roster: fleet vendors whose contacts email is in the Sent snapshot ---
function sentVendors() {
  const fleet = JSON.parse(readFileSync(join(ROOT, 'data', 'fleet.json'), 'utf8')).vendors;
  const contacts = JSON.parse(readFileSync(join(ROOT, 'data', 'contacts.json'), 'utf8'));
  const sent = JSON.parse(readFileSync(join(ROOT, 'data', 'sent.json'), 'utf8')).byEmail || {};
  const norm = (s) => String(s || '').toLowerCase().trim();
  const sentMap = {}; for (const [k, v] of Object.entries(sent)) sentMap[norm(k)] = v;
  const out = [];
  for (const v of fleet) {
    const c = contacts[v.slug] || {};
    if (c.disposition === 'no-chase') continue;
    const addrs = norm(c.sample_email).split(',').map((a) => a.trim()).filter((a) => a.includes('@'));
    const hits = addrs.map((a) => sentMap[a]).filter(Boolean);
    if (!hits.length) continue;
    const lastISO = hits.map((h) => h.lastSent).sort().pop();
    out.push({ slug: v.slug, vid: v.vid, name: v.name, items: v.items || [], date: ptDateFromISO(lastISO) });
  }
  return out;
}

// ---------------- CLI ----------------
function arg(name) { const i = process.argv.indexOf(`--${name}`); return i >= 0 ? process.argv[i + 1] : undefined; }
const cmd = process.argv[2];

if (cmd === 'plan') {
  // READ-ONLY: show exactly which sent-vendor records would be stamped, and with what date.
  // --pass 2 previews the RESEND (2nd-request) match set without writing anything.
  const planPass = String(arg('pass') || '1') === '2' ? 2 : 1;
  const vendors = sentVendors();
  const plan = [];
  const refused = [];
  for (const v of vendors) {
    const rows = await planVendor(v, planPass);
    if (v._skip) refused.push(v._skip);
    rows.forEach((r) => plan.push(r));
  }
  console.log(`PLAN (read-only, pass ${planPass}${planPass === 2 ? ' = 2nd request' : ' = first chase'}) — ${plan.length} record(s) would be stamped:\n`);
  if (refused.length) {
    console.log(`  REFUSED (${refused.length} vendor(s)) — no evidence a 2nd request was sent:`);
    for (const r of refused) console.log(`    · ${r}`);
    console.log('');
  }
  for (const p of plan) console.log(`  ${p.recordId}  ${p.date}  [${p.vid}] ${p.sku}  ${p.client}  (req ${p.requested})  — ${p.vendor}`);
  console.log(`\nJSON:`); console.log(JSON.stringify(plan));
} else if (cmd === 'backfill') {
  const vendors = sentVendors();
  console.log(`FMPro backfill — ${vendors.length} already-sent vendors · window ${windowRange()} (${MIN_AGE_DAYS}–${MAX_AGE_DAYS}d)\n`);
  let total = 0;
  for (const v of vendors) {
    try {
      const r = await stampVendor(v);
      if (r.count) recordPosted(v.vid, r);
      total += r.count;
      const note = r.found === 0 ? 'already clean / none outstanding' : `stamped ${r.count}/${r.found} with ${v.date}`;
      console.log(`  ${r.count ? '✓' : '·'} ${v.name.padEnd(34)} [${v.vid}]  ${note}`);
      r.stamped.forEach((s) => console.log(`       - ${s.sku}  ${s.client}  (req ${s.requested})`));
    } catch (e) {
      console.log(`  ✗ ${v.name} [${v.vid}]  ERROR: ${e.message}`);
    }
  }
  console.log(`\nDone. ${total} record(s) newly stamped. State → data/fmpro-posted.json`);
} else if (cmd === 'stamp') {
  // Single vendor, keyed by --slug (server.js passes this at send time). Matches
  // that vendor's exact page SKUs — never vid — so it can't over-match other vendors.
  const slug = arg('slug'); const date = arg('date') || fmtDate(new Date());
  // --pass 2 = this send was a RESEND (the 2nd request). Default 1 keeps the original
  // first-chase behaviour, so nothing that calls `stamp` without --pass changes.
  const pass = String(arg('pass') || '1') === '2' ? 2 : 1;
  if (!slug) { console.error('need --slug'); process.exit(1); }
  // TK-11255: slug is legitimately non-unique (declared aliases ship as items:0
  // stubs on another vendor's slug); prefer the row that actually has items
  // instead of depending on array order.
  const _fv = JSON.parse(readFileSync(join(ROOT, 'data', 'fleet.json'), 'utf8')).vendors.filter((x) => x.slug === slug);
  const fleetV = _fv.find((x) => x.items && x.items.length) || _fv[0];
  if (!fleetV) { console.error(`unknown --slug ${slug}`); process.exit(1); }
  const r = await stampVendor({ slug: fleetV.slug, vid: fleetV.vid, name: fleetV.name, items: fleetV.items, date }, pass);
  if (r.count) recordPosted(fleetV.vid, r);
  console.log(JSON.stringify(r));
} else if (cmd === 'note') {
  const text = arg('text'); if (!text) { console.error('need --text'); process.exit(1); }
  let recordId = arg('record');
  if (!recordId) { const vid = arg('vid'), sku = arg('sku'); if (!vid || !sku) { console.error('need --record OR --vid + --sku'); process.exit(1); } recordId = await findRecordIdByVidSku(vid, sku); }
  if (!recordId) { console.error('record not found'); process.exit(1); }
  console.log(JSON.stringify(await noteRecord(recordId, text)));
} else {
  console.log('usage: fmpro.mjs backfill | stamp --vid V --date MM/DD/YYYY | note (--record R | --vid V --sku S) --text "..."');
}