record.js

// Save what was sent (a send counts only with a Gmail message id) and build the A/R summary.
const s = $('Settings (edit me)').first().json;
const src = $('Normalize Invoices').first().json;
const plan = $('Plan Reminders').first().json._plan ?? [];
const db = $getWorkflowStaticData('global');
db.ar ??= {};

const results = [];
$input.all().forEach((item, i) => {
  // Gmail's response ({ id, threadId } or { error }) has no invoice_id. Without a credential the node passes
  // the plan item through unchanged, so that counts as not sent.
  const fromGmail = !item.json.invoice_id && !item.json.no_reminders;
  const x = fromGmail ? $('Plan Reminders').itemMatching(i).json : item.json;
  if (!x.invoice_id) return; // the "no reminders" marker
  const willSend = !x.dry_run && !x.record_only;
  const ok = x.record_only ? !x.dry_run : fromGmail && Boolean(item.json.id);
  const error = willSend && !ok ? String(item.json.error?.message ?? item.json.error ?? 'no message id returned (check the Gmail credential)').slice(0, 300) : null;
  if (willSend || (x.record_only && !x.dry_run)) {
    const led = (db.ar[x.invoice_id] ??= { sent: {} });
    if (ok) { led.sent[x.stage] = src.now; led.last_error = null; }
    else led.last_error = { stage: x.stage, at: src.now, error }; // not marked sent → retried next run
  }
  results.push({ invoice_id: x.invoice_id, number: x.number, customer: x.customer, stage: x.stage, to: x.to, cc: x.cc || null,
    internal: x.internal, subject: x.subject, body: x.dry_run ? x.body : undefined, skipped_stages: x.skipped_stages, sent: ok && willSend, recorded_only: Boolean(x.record_only), error });
});

// Aging buckets (by days past due) per currency
const buckets = {};
const today = DateTime.fromISO(src.now).setZone(s.timezone).startOf('day');
for (const inv of src.invoices.filter((v) => v.status === 'open' && v.amount_remaining > 0)) {
  const dpd = inv.due_date ? Math.round(today.diff(DateTime.fromISO(inv.due_date, { zone: s.timezone }).startOf('day'), 'days').days) : 0;
  const b = dpd <= 0 ? 'current' : dpd <= 30 ? '1-30' : dpd <= 60 ? '31-60' : dpd <= 90 ? '61-90' : '90+';
  const cur = (buckets[inv.currency] ??= { current: 0, '1-30': 0, '31-60': 0, '61-90': 0, '90+': 0, total: 0, overdue: 0 });
  cur[b] = Math.round((cur[b] + inv.amount_remaining) * 100) / 100;
  cur.total = Math.round((cur.total + inv.amount_remaining) * 100) / 100;
  if (dpd > 0) cur.overdue = Math.round((cur.overdue + inv.amount_remaining) * 100) / 100;
}
const money = (n, c) => new Intl.NumberFormat('en-US', { style: 'currency', currency: c.toUpperCase() }).format(n);
const topOverdue = plan.filter((p) => p.days_past_due > 0 && p.amount_remaining > 0 && !(p.reason ?? '').startsWith('status')).sort((a, b) => b.amount_remaining - a.amount_remaining).slice(0, 5);
const summaryText = [
  `A/R summary for ${s.business_name}, ${today.toFormat('ccc LLL d, yyyy')}`, '',
  ...Object.entries(buckets).map(([c, b]) => `${c.toUpperCase()}: ${money(b.total, c)} open, ${money(b.overdue, c)} overdue (1-30: ${money(b['1-30'], c)} · 31-60: ${money(b['31-60'], c)} · 61-90: ${money(b['61-90'], c)} · 90+: ${money(b['90+'], c)})`),
  '', 'Largest overdue:', ...(topOverdue.length ? topOverdue.map((p) => `- ${p.customer}: ${money(p.amount_remaining, p.currency)}, ${p.number}, ${p.days_past_due} days`) : ['- none']),
  '', `Reminders this run: ${results.filter((r) => r.sent).length} sent, ${results.filter((r) => r.recorded_only && !src.dry_run).length} recorded without email, ${results.filter((r) => r.error).length} failed${src.dry_run ? ' (dry run)' : ''}.`,
  ...plan.filter((p) => (p.reason ?? '').startsWith('paused') || p.reason === 'no customer email').map((p) => `- ${p.number} (${p.customer}): ${p.reason}`),
].join('\n');

return [{
  json: {
    run_at: src.now, source: src.source, source_error: src.source_error, has_more: src.has_more, dry_run: src.dry_run,
    open_invoices: src.invoices.length, reminders: results, plan, aging: buckets,
    summary_due: src.force_summary || today.toFormat('ccc') === s.summary_day,
    summary_subject: `A/R summary: ${Object.entries(buckets).map(([c, b]) => money(b.overdue, c) + ' overdue').join(', ') || 'nothing open'}`,
    summary_text: summaryText, via_api: src.via_api,
  },
}];