`scripts/audit-clients.mjs`

84 lines
#!/usr/bin/env node
// READ-ONLY audit of the FileMaker Clients file. Detects (a) records whose
// Company/Name fields are swapped (business name sitting in Name, a person in
// Company — the signature of the fixed bug) and (b) duplicate clients (same
// email, same phone, or same company). Writes NOTHING. $0 local.
import { readFileSync } from 'node:fs';
import { fileURLToPath } from 'node:url';
import { dirname, join } from 'node:path';
import * as fm from '../src/fm-client.js';

const ROOT = join(dirname(fileURLToPath(import.meta.url)), '..');
for (const l of readFileSync(join(ROOT, '.env'), 'utf8').split('\n')) {
  const m = l.match(/^([A-Z0-9_]+)=(.*)$/); if (m && !process.env[m[1]]) process.env[m[1]] = m[2];
}

const LAYOUT = 'wALLPAPER oPTIONS';
const norm = (s) => (s || '').toString().trim().toLowerCase().replace(/\s+/g, ' ');
const last10 = (s) => (s || '').toString().replace(/\D/g, '').slice(-10);

// business-name signal — word-boundary tokens that a PERSON name would not carry
const BIZ = /\b(construction|inc|inc\.|llc|l\.l\.c|corp|corporation|company|co\.|design|designs|interiors|interior|studio|studios|group|electric|electrical|kitchen|kitchens|cabinet|cabinets|builders|building|homes|realty|architect|architects|architecture|associates|assoc|ltd|enterprises|contracting|contractors|remodel|remodeling|properties|property|development|developers|furnishings|decor|décor|flooring|floors|paint|painting|hospitality|hotel|hotels|restaurant|salon|spa|boutique|gallery|works|supply|services|solutions|systems|international|motors|ford|chevrolet|toyota|group|partners|lighting|millwork|woodworks|granite|stone|tile|drywall|plumbing|hvac|roofing|landscape|landscaping|nursery|florist|bakery|cafe|grill|realtors|residential|commercial|custom)\b/i;
const looksBiz = (s) => BIZ.test(s || '');
// a person-ish string: 1–4 alphabetic words, no business token, no digits
const looksPerson = (s) => { const t = (s || '').trim(); return t && !/\d/.test(t) && !looksBiz(t) && t.split(/\s+/).length <= 4; };

async function main() {
  const PAGE = 500, CAP_PAGES = 200; // safety cap = 100k records
  let offset = 1, page = 0, total = null;
  const recs = [];
  while (page < CAP_PAGES) {
    const { records, dataInfo } = await fm.listRecords('Clients', LAYOUT, { limit: PAGE, offset });
    if (total === null) total = dataInfo?.foundCount ?? dataInfo?.totalRecordCount ?? null;
    if (!records.length) break;
    for (const r of records) {
      const f = r.fieldData || {};
      recs.push({ id: r.recordId, acct: f['Account Number'], company: f['Company'] || '', name: f['Name'] || '', email: f['Email address'] || '', phone: f['Phone'] || '' });
    }
    process.stderr.write(`\r  scanned ${recs.length}${total ? '/' + total : ''} …`);
    if (records.length < PAGE) break;
    offset += PAGE; page++;
  }
  process.stderr.write('\n');
  const hitCap = page >= CAP_PAGES;

  // (a) swapped fields: business in Name, person in Company
  const swapped = recs.filter((r) => r.company && r.name && looksBiz(r.name) && looksPerson(r.company));

  // (b) duplicates by email / phone / company
  const groupBy = (key) => {
    const g = new Map();
    for (const r of recs) { const k = key(r); if (!k) continue; (g.get(k) || g.set(k, []).get(k)).push(r); }
    return [...g.values()].filter((v) => v.length > 1);
  };
  const dupEmail = groupBy((r) => norm(r.email));
  const dupPhone = groupBy((r) => last10(r.phone));
  const dupCompany = groupBy((r) => norm(r.company));

  const uniqRecordsInDupes = new Set([...dupEmail, ...dupPhone, ...dupCompany].flat().map((r) => r.id)).size;

  console.log('\n════════ FileMaker Clients audit (READ-ONLY) ════════');
  console.log(`Records scanned      : ${recs.length}${hitCap ? '  ⚠️  HIT PAGE CAP — not all records scanned' : ''}`);
  console.log(`\n── (a) Swapped Company/Name (business in Name, person in Company) ──`);
  console.log(`Flagged: ${swapped.length}`);
  for (const r of swapped.slice(0, 20)) console.log(`   acct ${r.acct||'?'} | Company="${r.company}"  Name="${r.name}"`);
  if (swapped.length > 20) console.log(`   … and ${swapped.length - 20} more`);

  console.log(`\n── (b) Duplicate clients ──`);
  console.log(`Same EMAIL   : ${dupEmail.length} group(s)`);
  for (const g of dupEmail.slice(0, 10)) console.log(`   "${g[0].email}" ×${g.length}  → accts ${g.map((r) => r.acct).join(', ')}`);
  if (dupEmail.length > 10) console.log(`   … and ${dupEmail.length - 10} more group(s)`);
  console.log(`Same PHONE   : ${dupPhone.length} group(s)`);
  for (const g of dupPhone.slice(0, 10)) console.log(`   "${last10(g[0].phone)}" ×${g.length}  → accts ${g.map((r) => r.acct).join(', ')}  [${g.map((r)=>r.company||r.name).slice(0,3).join(' / ')}]`);
  if (dupPhone.length > 10) console.log(`   … and ${dupPhone.length - 10} more group(s)`);
  console.log(`Same COMPANY : ${dupCompany.length} group(s)`);
  for (const g of dupCompany.slice(0, 10)) console.log(`   "${g[0].company}" ×${g.length}  → accts ${g.map((r) => r.acct).join(', ')}`);
  if (dupCompany.length > 10) console.log(`   … and ${dupCompany.length - 10} more group(s)`);

  console.log(`\n── Summary ──`);
  console.log(`Swapped-field records : ${swapped.length}`);
  console.log(`Records in a dup group: ${uniqRecordsInDupes}  (email ${dupEmail.length} / phone ${dupPhone.length} / company ${dupCompany.length} groups)`);
  console.log('No records were modified.\n');
}
main().catch((e) => { console.error('AUDIT ERROR:', e.message); process.exit(1); });