`scripts/audit-dup-invoices.mjs`

134 lines
// READ-ONLY audit: find Shopify orders that got MORE THAN ONE FileMaker invoice
// (the mac2 split-brain / old-code per-SKU "second invoice" duplication).
// Groups invoices by `Cust PO` (= Shopify order_number). No writes, no deletes.
//
//   node scripts/audit-dup-invoices.mjs           # last 21 days
//   node scripts/audit-dup-invoices.mjs 60        # last 60 days
import { writeFileSync } from 'node:fs';
import { findRecords } from '../src/fm-client.js';
import { loadEnv, num, money } from '../lib/fm-script-helpers.js';

loadEnv(import.meta.url);

const HDR = 'Invoice to Client Copy';
const args = process.argv.slice(2);
const DAYS = parseInt(args.find((a) => /^\d+$/.test(a)) || '21', 10);
const csvIdx = args.indexOf('--csv');
const CSV_PATH = csvIdx !== -1 ? (args[csvIdx + 1] || `${process.env.HOME}/Desktop/dup-invoices-audit.csv`) : null;

// Build a MM/DD/YYYY...MM/DD/YYYY FileMaker date range for the window.
const d = new Date();
const end = new Date(d);
const start = new Date(d); start.setDate(start.getDate() - DAYS);
const fmDate = (x) => `${String(x.getMonth() + 1).padStart(2, '0')}/${String(x.getDate()).padStart(2, '0')}/${x.getFullYear()}`;
const range = `${fmDate(start)}...${fmDate(end)}`;

async function main() {
  // Pull every invoice in the window that carries a Shopify order number (Cust PO).
  let records = [];
  try {
    ({ records } = await findRecords('invoice', HDR, [{ 'Date': range, 'Cust PO': '*' }],
      { limit: 2000, sort: [{ fieldName: 'Invoice', sortOrder: 'ascend' }] }));
  } catch (e) {
    if (e.fmCode === '401') { console.log(`No invoices with a Cust PO in the last ${DAYS} days.`); return; }
    throw e;
  }

  // Group by Cust PO (Shopify order number). Only REAL order numbers — a Shopify
  // order_number is a 4-6 digit int; skip blanks, "NA", "0", freight refs, etc.,
  // which otherwise falsely lump unrelated invoices together.
  const byOrder = new Map();
  for (const r of records) {
    const f = r.fieldData || {};
    const po = String(f['Cust PO'] || '').trim().replace(/^#+/, '');
    if (!/^\d{4,6}$/.test(po)) continue;
    const inv = String(f['Invoice'] || '').trim();
    if (!/^\d+$/.test(inv) || inv === '999999') continue;
    const row = {
      inv: parseInt(inv, 10),
      total: num(f['GRAND TOTAL 2'] ?? f['PAID ON ACCOUNT']),
      name: String(f['SOLD NAME'] || f['Sold Company Name Lookup'] || '').trim(),
      date: String(f['Date'] || '').trim(),
      item: String(f['DETAIL 1(1)'] || f['DETAIL 1'] || '').trim().slice(0, 60),
      dwsku: String(f['WC Pattern Number AKA DW SKU'] || '').trim(),
    };
    if (!byOrder.has(po)) byOrder.set(po, []);
    byOrder.get(po).push(row);
  }

  // Keep only orders with >1 invoice, then classify each order.
  //   keeper       = the single largest positive-total invoice (the real bill)
  //   zeroOrphans  = $0.00 per-SKU tracking records (createSkuRecords clutter; no $ impact)
  //   doubleBill   = a SECOND positive-nonzero, non-refund invoice (real double-count)
  //   refunds/legit= negative totals (shipping/return) — left alone
  const isRefund = (r) => r.total < 0 || /refund/i.test(r.item);
  const orders = [...byOrder.entries()]
    .filter(([, rows]) => rows.length > 1)
    .map(([po, rows]) => {
      rows.sort((a, b) => a.inv - b.inv);
      const positives = rows.filter((r) => r.total > 0 && !isRefund(r));
      const maxTotal = positives.length ? Math.max(...positives.map((r) => r.total)) : 0;
      const keeper = positives.filter((r) => r.total === maxTotal).sort((a, b) => a.inv - b.inv)[0];
      for (const r of rows) {
        if (isRefund(r)) r.role = 'legit(refund)';
        else if (keeper && r.inv === keeper.inv) r.role = 'KEEP';
        else if (r.total === 0) r.role = 'orphan($0)';
        else r.role = 'DOUBLE-BILL';
      }
      return { po, name: rows[0].name, rows, hasDoubleBill: rows.some((r) => r.role === 'DOUBLE-BILL') };
    })
    .sort((a, b) => Math.min(...a.rows.map((r) => r.inv)) - Math.min(...b.rows.map((r) => r.inv)));

  if (!orders.length) {
    console.log(`✓ No duplicated orders found in the last ${DAYS} days (${records.length} invoices scanned, range ${range}).`);
    return;
  }

  const money9 = (n) => money(n).padStart(9);
  let zeroCount = 0, dblCount = 0, dblDollars = 0;
  const dblOrders = orders.filter((o) => o.hasDoubleBill);
  const zeroOnly = orders.filter((o) => !o.hasDoubleBill && o.rows.some((r) => r.role === 'orphan($0)'));

  console.log(`\n============ DUPLICATE-INVOICE AUDIT (READ-ONLY) ============`);
  console.log(`window: last ${DAYS} days (${range}) · ${records.length} invoices scanned · ${orders.length} multi-invoice orders\n`);

  console.log(`### CATEGORY A — REAL DOUBLE-BILLING (positive $ counted twice) — ${dblOrders.length} order(s)`);
  for (const o of dblOrders) {
    console.log(`Order #${o.po}  —  ${o.name}`);
    for (const r of o.rows) {
      if (r.role === 'DOUBLE-BILL') { dblCount++; dblDollars += r.total; }
      console.log(`   ${r.role.padEnd(13)} #${r.inv}  ${money9(r.total)}  ${r.date.padEnd(10)}  ${r.dwsku || r.item}`);
    }
    console.log('');
  }

  console.log(`### CATEGORY B — $0 PER-SKU TRACKING CLUTTER (no revenue impact) — ${zeroOnly.length} order(s)`);
  for (const o of zeroOnly) {
    const zeros = o.rows.filter((r) => r.role === 'orphan($0)');
    zeroCount += zeros.length;
    const keeper = o.rows.find((r) => r.role === 'KEEP');
    console.log(`   Order #${o.po}  ${o.name.slice(0, 24).padEnd(24)}  keep #${keeper?.inv ?? '—'} (${money(keeper?.total || 0)})  +${zeros.length} × $0 records: #${zeros.map((r) => r.inv).join(', #')}`);
  }

  console.log(`\n============ SUMMARY ============`);
  console.log(`A) Real double-billed orders:   ${dblOrders.length}   (${dblCount} extra invoices,  up to ${money(dblDollars)} double-counted [upper bound — incl. fragmented orders])`);
  console.log(`B) $0 tracking-record clutter:  ${zeroOnly.length} orders   (${zeroCount} zero-dollar records)`);
  console.log(`\nRead-only. Nothing deleted. Category A is the money/reporting problem; Category B is cosmetic clutter.`);

  // Optional CSV export — every row of every multi-invoice order, both categories.
  if (CSV_PATH) {
    const esc = (v) => `"${String(v ?? '').replace(/"/g, '""')}"`;
    const cat = (o) => (o.hasDoubleBill ? 'A_double_billing' : 'B_zero_clutter');
    const lines = ['category,order_po,customer,invoice,role,total,date,detail'];
    for (const o of orders) {
      for (const r of o.rows) {
        lines.push([cat(o), o.po, esc(o.name), r.inv, r.role, r.total.toFixed(2), r.date, esc(r.dwsku || r.item)].join(','));
      }
    }
    writeFileSync(CSV_PATH, lines.join('\n') + '\n');
    console.log(`\nCSV written: ${CSV_PATH}  (${lines.length - 1} rows across ${orders.length} orders)`);
  }
}

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