`lib/sync-core.js`

159 lines
// Shopify order -> FileMaker Clients sync core.
// Maps a Shopify order's customer to a FileMaker Clients record, but only
// creates one when NO similar client already exists. Dedup is checked in strict
// priority order — EMAIL first, then COMPANY name, then CLIENT (contact) name —
// per Steve's rule (2026-07-16). Company = business name, Name = contact person.
import * as fm from '../src/fm-client.js';

const LAYOUT = 'wALLPAPER oPTIONS'; // Clients layout exposing the client header fields
const digits = (s) => (s || '').toString().replace(/\D/g, '');
const last10 = (s) => digits(s).slice(-10);

export function mapOrder(o) {
  const c = o.customer || {};
  const s = o.shipping_address || o.billing_address || {};
  const fullName = ((c.first_name || '') + ' ' + (c.last_name || '')).trim();
  return {
    orderNumber: o.order_number,
    orderId: o.id,
    fields: {
      // FileMaker CLIENT record: Company = the BUSINESS name, Name = the CONTACT
      // person. Previously these were reversed (fullName went into Company,
      // company into Name), which put e.g. "Denny Wilson" in Company and
      // "Wilson Construction" in Name. Fixed 2026-07-16 (Steve flagged).
      // Fall back to the person's name for Company only when there is no
      // business name (individual buyer), preserving the old behavior there.
      'Company': s.company || fullName || '',
      'Name': fullName || '',
      'Address': s.address1 || '',
      'City': s.city || '',
      'State': s.province_code || '',
      'Zip': s.zip || '',
      'Phone': s.phone || o.phone || c.phone || '',
      'Email address': o.email || c.email || '',
    },
  };
}

// Returns an array of matching existing clients (empty = safe to create).
//
// Steve's dedup rule (2026-07-16): ALWAYS check in priority order —
//   1) Email address   2) Company name   3) Client (contact) name
// We check tier by tier and return the FIRST tier that produces a match, so a
// client already on file is never entered twice. Phone rides with the email
// tier as an equally-strong identity signal. Matching the CORRECT fields is
// essential: the old code searched Company against the person's name (because
// the field mapping was reversed), which silently missed real dupes and let
// second records get created.
export async function findDuplicates({ fields }) {
  const email = fields['Email address'];
  const phone10 = last10(fields['Phone']);
  const company = fields['Company']; // business name
  const person = fields['Name'];     // contact person

  const tiers = [
    { queries: [
        ...(email ? [{ 'Email address': '==' + email }] : []),
        ...(phone10 ? [{ 'Phone': '*' + phone10 + '*' }] : []), // contains last-10
      ] },
    { queries: company ? [{ 'Company': '==' + company }] : [] }, // exact company
    { queries: person ? [{ 'Name': '==' + person }] : [] },      // exact contact name
  ];

  for (const tier of tiers) {
    const hits = new Map(); // recordId -> {acct, company, why[]}
    for (const q of tier.queries) {
      let recs = [];
      try { recs = (await fm.findRecords('Clients', LAYOUT, q, { limit: 10 })).records; }
      catch (e) {
        // 401 = "no records match" (a genuine empty result) → keep looking.
        // ANY other error means the dedup could not run reliably; surface it
        // rather than silently returning no-match (which would double-enter).
        if (String(e.fmCode) === '401') continue;
        throw new Error(`dedup find failed (${Object.keys(q)[0]}): ${e.message}`);
      }
      const why = Object.keys(q)[0];
      for (const r of recs) {
        // confirm phone match by normalized last-10 to avoid wildcard false positives
        if (why === 'Phone' && last10(r.fieldData['Phone']) !== phone10) continue;
        const entry = hits.get(r.recordId) || { recordId: r.recordId, acct: r.fieldData['Account Number'], company: r.fieldData['Company'], why: [] };
        if (!entry.why.includes(why)) entry.why.push(why);
        hits.set(r.recordId, entry);
      }
    }
    if (hits.size) return [...hits.values()]; // first tier with a match wins
  }
  return [];
}

// Process one order end-to-end: ensure the client exists (create if new, dedup
// by email/phone/name otherwise), create the FileMaker invoice, then post a
// new-order alert to Slack. Every order gets an invoice + Slack ping regardless
// of whether the client was new or already on file.
export async function syncOrder(order, { commit = true } = {}) {
  const mapped = mapOrder(order);
  if (!mapped.fields['Email address'] && !mapped.fields['Phone'] && !mapped.fields['Company']) {
    return { orderNumber: mapped.orderNumber, action: 'skipped', reason: 'no identifying fields' };
  }

  // 1) ensure client + resolve account number
  let clientAction, accountNumber;
  const dupes = await findDuplicates(mapped);
  if (dupes.length) {
    accountNumber = dupes[0].acct;
    clientAction = 'existing';
    // refresh the existing client's contact info from this order BEFORE invoicing
    if (commit && dupes[0].recordId) {
      const refresh = {};
      for (const k of ['Address', 'City', 'State', 'Zip', 'Phone', 'Email address']) {
        if (mapped.fields[k]) refresh[k] = mapped.fields[k];
      }
      try { await fm.updateRecord('Clients', LAYOUT, dupes[0].recordId, refresh, { dryRun: false }); clientAction = 'updated'; } catch { /* best-effort refresh — do not block invoicing */ }
    }
  } else if (commit) {
    const res = await fm.createRecord('Clients', LAYOUT, mapped.fields, { dryRun: false });
    try { accountNumber = (await fm.getRecord('Clients', LAYOUT, res.recordId))?.fieldData?.['Account Number']; } catch {}
    clientAction = 'created';
  } else {
    clientAction = 'would-create';
  }

  // 2) invoice — HARD RULES (Steve 2026-07-03):
  //   (a) CLIENT-ACCOUNT-FIRST: no invoice may be created without a client account
  //       first. Only a client account number can BEGIN an invoice. If we could not
  //       resolve/create an account number above, DO NOT create the invoice.
  //   (b) NO SECOND INVOICE: the per-SKU tracking records (createSkuRecords) that
  //       duplicated the SKU number are removed — not needed.
  if (commit && !String(accountNumber || '').trim()) {
    return {
      orderNumber: mapped.orderNumber,
      action: 'skipped',
      reason: 'no client account number — invoice not created (client-account-first rule)',
      client: clientAction,
      company: mapped.fields['Company'],
      email: mapped.fields['Email address'],
    };
  }
  const { createInvoiceForOrder } = await import('./invoice.js');
  const inv = await createInvoiceForOrder(order, accountNumber, { commit });
  const skuRecs = null; // second invoice (per-SKU tracking records) intentionally removed

  // 3) Slack alert (only on real commit)
  let slack = null;
  if (commit) {
    const { postOrderToSlack } = await import('./notify.js');
    try { slack = await postOrderToSlack({ order, invoiceNumber: inv.invoiceNumber, total: inv.grandTotal, clientName: mapped.fields['Company'] }); }
    catch (e) { slack = { ok: false, error: e.message }; }
  }

  return {
    orderNumber: mapped.orderNumber,
    action: commit ? 'processed' : 'would-process',
    client: clientAction, accountNumber, company: mapped.fields['Company'], email: mapped.fields['Email address'],
    invoiceNumber: inv.invoiceNumber, invoiceTotal: inv.grandTotal,
    skuRecords: skuRecs?.created?.length ?? 0,
    slack: slack?.ok ? 'posted' : (slack?.error || 'n/a'),
    matchedOn: dupes.length ? [...new Set(dupes.flatMap((d) => d.why))] : undefined,
  };
}