`scripts/sales-summary.mjs`

139 lines
// End-of-day sales recap → Slack #new-order, sourced from the FileMaker invoice DB.
// Reports a TRAILING 24-HOUR window: everything new since the previous run (Steve,
// 2026-07-03 "Yes. Everything in last 24 hours at 4:30."). The invoice layout has no
// timestamp fields — only dates — so the window is implemented as a diff against a
// ledger of invoice numbers already reported (data/sales-summary-ledger.json). That
// catches late-day orders, backdated entries, and quote→booked conversions exactly.
// MONTH-TO-DATE stays bucketed by invoice Date (Pacific Time).
//
//   node scripts/sales-summary.mjs            # compute + POST to Slack + update ledger
//   node scripts/sales-summary.mjs --dry-run  # compute + print only (no post, no ledger write)

import { readFileSync, writeFileSync, renameSync, existsSync } from 'node:fs';
import { join, dirname } from 'node:path';
import { fileURLToPath } from 'node:url';
import { findRecords } from '../src/fm-client.js';
import { loadEnv, TZ, ptYMD, num, money } from '../lib/fm-script-helpers.js';

loadEnv(import.meta.url);

const DB = 'invoice';
const LAYOUT = 'GRAND TOTAL SALES';
const TOTAL_FIELD = 'GRAND TOTAL 2';       // per-invoice grand total (merch + shipping + tax)
const DATE_FIELD = 'Date';                 // invoice date (a DATE — the layout has no timestamps)
const CHANNEL = process.env.SALES_SUMMARY_CHANNEL || 'C06D9C2PG1K'; // #new-order
const DRY_RUN = process.argv.includes('--dry-run');
const EXCLUDE_INVOICES = new Set(['999999']); // test/demo records
const LEDGER_PATH = join(dirname(fileURLToPath(import.meta.url)), '..', 'data', 'sales-summary-ledger.json');
const LOOKBACK_DAYS = 10;   // how far back we pull to catch backdated entries
const PRUNE_DAYS = 60;      // drop ledger entries not seen in this many days

const pad = (n) => String(n).padStart(2, '0');
const fmDate = ({ y, m, d }) => `${pad(m)}/${pad(d)}/${y}`; // FM returns zero-padded

function loadLedger() {
  if (!existsSync(LEDGER_PATH)) return null;
  try { return JSON.parse(readFileSync(LEDGER_PATH, 'utf8')); } catch { return null; }
}

function saveLedger(ledger) {
  const tmp = LEDGER_PATH + '.tmp';
  writeFileSync(tmp, JSON.stringify(ledger, null, 1));
  renameSync(tmp, LEDGER_PATH);
}

async function main() {
  const now = new Date();
  const { y, m, d } = ptYMD(now);
  const todayFM = fmDate({ y, m, d });
  const yesterdayFM = fmDate(ptYMD(new Date(now.getTime() - 864e5)));
  const todayISO = `${y}-${pad(m)}-${pad(d)}`;

  // Pull window: month start (for MTD) or LOOKBACK_DAYS ago, whichever is earlier.
  const lookback = ptYMD(new Date(now.getTime() - LOOKBACK_DAYS * 864e5));
  const monthIsEarlier = y < lookback.y || (y === lookback.y && m < lookback.m);
  const from = monthIsEarlier ? `${m}/1/${y}` : `${lookback.m}/${lookback.d}/${lookback.y}`;

  const { records } = await findRecords(
    DB, LAYOUT,
    [{ [DATE_FIELD]: `${from}...${m}/${d}/${y}` }],
    { limit: 2000, sort: [{ fieldName: DATE_FIELD, sortOrder: 'ascend' }] },
  );

  // A BOOKED order (= a real sale) has money received (PAID ON ACCOUNT ≠ 0).
  // No payment yet = an open QUOTE, which must NOT count as sales.
  const isBooked = (f) => {
    const p = String(f['PAID ON ACCOUNT'] ?? '').trim();
    return p !== '' && num(p) !== 0;
  };
  const inCurrentMonth = (dateStr) => {
    const [mm, , yy] = String(dateStr).split('/');
    return Number(mm) === m && Number(yy) === y;
  };

  const ledger = loadLedger();
  const firstRun = !ledger || !ledger.invoices;
  const seen = firstRun ? {} : ledger.invoices;
  const nextInvoices = { ...seen };

  let dayCount = 0, dayTotal = 0, mtdCount = 0, mtdTotal = 0, qCount = 0, qTotal = 0;
  for (const r of records) {
    const f = r.fieldData || {};
    const inv = String(f['Invoice'] || '').trim();
    if (EXCLUDE_INVOICES.has(inv)) continue;
    const key = inv || `rec:${r.recordId}`;
    const amt = num(f[TOTAL_FIELD]);
    const dateStr = String(f[DATE_FIELD] || '').trim();
    const booked = isBooked(f);

    if (booked && inCurrentMonth(dateStr)) { mtdCount += 1; mtdTotal += amt; }

    const prev = seen[key];
    // First run has no ledger to diff against — approximate the 24h window as
    // "dated yesterday or today". Every later run diffs against the ledger.
    const inWindow = firstRun
      ? (dateStr === todayFM || dateStr === yesterdayFM)
      : (booked ? (!prev || prev.status !== 'booked') : !prev);
    if (inWindow) {
      if (booked) { dayCount += 1; dayTotal += amt; }
      else { qCount += 1; qTotal += amt; }
    }
    nextInvoices[key] = { status: booked ? 'booked' : 'quote', lastSeen: todayISO };
  }

  // Prune ledger entries not seen recently so the file stays small.
  const cutoff = new Date(now.getTime() - PRUNE_DAYS * 864e5).toISOString().slice(0, 10);
  for (const [key, v] of Object.entries(nextInvoices)) {
    if ((v.lastSeen || '') < cutoff) delete nextInvoices[key];
  }

  const dayLabel = new Intl.DateTimeFormat('en-US', { timeZone: TZ, weekday: 'short', month: 'short', day: 'numeric', year: 'numeric' }).format(now);
  const monthLabel = new Intl.DateTimeFormat('en-US', { timeZone: TZ, month: 'long', year: 'numeric' }).format(now);

  const text =
    `*📊 Daily Sales Summary — ${dayLabel}*\n\n` +
    `🕓 *Last 24 hours — booked:* ${dayCount} order${dayCount === 1 ? '' : 's'} · *${money(dayTotal)}*\n` +
    `📈 *Month-to-date — booked (${monthLabel}):* ${mtdCount} order${mtdCount === 1 ? '' : 's'} · *${money(mtdTotal)}*\n` +
    `📝 *New quotes (last 24h):* ${qCount} · ${money(qTotal)}\n\n` +
    `_Last 24h = everything new since the previous 4:30 report, incl. late-day + backdated orders and quotes that converted to booked. Booked = payment received (PAID ON ACCOUNT). Excludes test #999999; refunds net out._`;

  if (DRY_RUN) {
    console.log(`--- DRY RUN (not posted, ledger not written${firstRun ? '; first-run window = yesterday+today' : ''}) ---\n` + text);
    return;
  }

  const token = process.env.SLACK_BOT_TOKEN;
  if (!token) throw new Error('SLACK_BOT_TOKEN not set.');
  const res = await fetch('https://slack.com/api/chat.postMessage', {
    method: 'POST',
    headers: { Authorization: `Bearer ${token}`, 'Content-Type': 'application/json; charset=utf-8' },
    body: JSON.stringify({ channel: CHANNEL, text, unfurl_links: false }),
  });
  const j = await res.json();
  if (!j.ok) throw new Error(`Slack post failed: ${j.error}`);
  saveLedger({ lastRun: now.toISOString(), invoices: nextInvoices });
  console.log(`✓ Posted to ${CHANNEL} (ts ${j.ts}) — 24h ${dayCount}/${money(dayTotal)} · MTD ${mtdCount}/${money(mtdTotal)}`);
}

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