`lib/invoice.js`

265 lines
// Build a FileMaker invoice from a Shopify order — encodes all of Steve's field
// rules (learned 2026-06-29 building invoice 102262 from order #32474):
//  - header on "Invoice to Client Copy"; line fields are REPEATING -> "(n)" syntax
//  - SALES TAX / TOTALS / GRAND TOTAL are CALC fields (never written)
//  - Unit = unit of measure (yards/rolls/bolts), NOT price; price -> RETAIL/NET
//  - tax zeroed (SALES TAX YN = N) when the order carried no tax (out-of-state)
//  - PAID ON ACCOUNT = the computed GRAND TOTAL (paid in full -> Net Due 0)
//  - Resale # falls back to Out of State / Out of Country when none on file
//  - Sales Person = Otto; Buyer = customer; Sidemark = project (company)
//  - JS Easy SKU # (middle SKU field, Layout #47) = base SKU (no variant suffix)
//  - WC PATTERN (1-order detail 1 Copy3) = SKU minus -Sample/Sample
//  - SOLD/SHIP address written explicitly (Data API create does NOT fire lookups)
//  - Notes stamped "Imported by Otto — <date time>"
import * as fm from '../src/fm-client.js';
import { ensureWallpaper } from './wallpaper.js';
import { flagSku } from './notify.js';

const HDR = 'Invoice to Client Copy';
const SHIP_LAYOUT = 'Order Detail - Main - Shipping'; // exposes VID + Detail 1 Mfr Number
const SKU_LAYOUT = 'Layout #47';        // exposes "JS Easy SKU #"
const WCPAT_LAYOUT = '1-order detail 1 Copy3'; // exposes "wc pattern"
// Steve's writable Text[4] field (repeating, maxRepeat 4 -> rep 1 written via the
// bare field name). Shares the header base table via "1-order detail 1 Copy3".
// NOT the calc fields "wc pattern no" / "wc pattern no2" / "wc pattern".
const DWSKU_LAYOUT = '1-order detail 1 Copy3';
const DWSKU_FIELD = 'WC Pattern Number AKA DW SKU';
const RESALE_LAYOUT = 'PROFORMA'; // exposes the writable 'Resale card lookup' (the field shown on the invoice)
// DW SKU value for the WC Pattern Number AKA DW SKU field: base pattern, sample-stripped, DASHES REMOVED (always).
const dwSku = (sku) => baseSku((sku || '').replace(/-?\s*sample\s*/ig, '').trim()).replace(/-/g, '');

const today = () => { const d = new Date(); return `${String(d.getMonth()+1).padStart(2,'0')}/${String(d.getDate()).padStart(2,'0')}/${d.getFullYear()}`; };
const stamp = () => new Date().toLocaleString('en-US', { month:'short', day:'numeric', year:'numeric', hour:'numeric', minute:'2-digit' });
const baseSku = (sku) => { const m = (sku||'').match(/^([A-Za-z]+-?\d+)/); return m ? m[1] : (sku||''); };
// WC PATTERN / SKU = the pattern's Shopify SKU with the -Sample/Sample variant removed.
const wcPattern = (sku) => baseSku((sku||'').replace(/-?\s*sample\s*/ig, '').trim());
const r2 = (n) => Math.round((Number(n)||0) * 100) / 100;
function uom(li) {
  const t = `${li.variant_title||''} ${li.name||''} ${li.sku||''}`.toLowerCase();
  if (/per yard|\/ ?yard|\byard/.test(t)) return 'yards';
  if (/double roll|per roll|\/ ?roll|\broll/.test(t)) return 'rolls';
  if (/\bbolt/.test(t)) return 'bolts';
  if (/\bpanel/.test(t)) return 'panels';
  if (/sample|memo|swatch/.test(t)) return 'each';
  return 'each';
}
const isSample = (li) => /sample|memo|swatch/i.test(`${li.variant_title||''} ${li.name||''} ${li.sku||''}`);
// The money record carries ALL the order's dollars on ONE line (Steve's rule
// 2026-07-09): total units × per-unit price, so GRAND TOTAL = the whole
// merchandise subtotal. Q = the order's total quantity, NET/RETAIL = merch ÷
// total-qty (the real per-unit price — exact for a uniform-price order like a
// batch of $4.25 samples; a blended per-unit for the rare mixed-price order).
// DETAIL = "Samples" for an all-samples order (so Q=2 reads "2 Samples @ $4.25"),
// else headlines the first SKU. Every OTHER SKU goes on its own $0 record (just
// the SKU), so all the money stays on the first invoice.
function moneyLine(items, merch) {
  const totalQty = items.reduce((a, li) => a + (li.quantity || 1), 0) || 1;
  const allSamples = items.every(isSample);
  const detail = allSamples ? 'Samples' : `${baseSku(items[0].sku)} — ${items[0].name || ''}`.trim().slice(0, 250);
  return { detail, qty: totalQty, unit: uom(items[0]), price: r2(merch / totalQty) };
}
function resale(s) {
  const country = (s.country_code || 'US').toUpperCase();
  const state = (s.province_code || '').toUpperCase();
  if (country !== 'US') return 'Out of Country';
  if (state && state !== 'CA') return 'Out of State';
  return 'On File';
}

async function nextInvoiceNumber() {
  const r = await fm.findRecords('invoice', HDR, { 'Invoice': '10*' }, { limit: 25, sort: [{ fieldName: 'Invoice', sortOrder: 'descend' }] });
  const nums = r.records.map((x) => parseInt((x.fieldData['Invoice']||'').replace(/\D/g,''))).filter((n) => n > 100000 && n < 200000);
  return (nums.length ? Math.max(...nums) : 102261) + 1;
}

// safe single-field patch (skips calc / non-modifiable fields without aborting)
async function patch(id, field, value) {
  try { await fm.updateRecord('invoice', HDR, id, { [field]: value }, { dryRun: false }); }
  catch (e) { if (!['102','201','509','111'].includes(e.fmCode)) throw e; }
}
// Line-field write note: only repetition 1 of the "… 1" family is placed on the
// layout, so all money rides `DETAIL 1(1)`/`Q1(1)`/`NET 1(1)` on the first record
// (verified 2026-07-09). The "… 2" fields are intentionally NOT used — every SKU
// beyond the first gets its own $0 record instead.

// Create one no-dollar tracking record per SKU in the order, with the DW SKU in
// the writable "WC Pattern Number AKA DW SKU" field (FileMaker's pattern Name/Color
// lookup keys off it when opened/imported in the client). Separate from the
// billing invoice. Returns the created records.
export async function createSkuRecords(order, accountNumber, clientName, { commit = true } = {}) {
  const items = order.line_items || [];
  const skus = items.map((li) => dwSku(li.sku)); // base DW SKU, sample-stripped, dashes removed
  if (!commit) return { wouldCreate: skus.length, skus };
  const created = [];
  for (const sku of skus) {
    const res = await fm.createRecord('invoice', HDR, {
      'Cust PO': String(order.order_number), 'Account #': String(accountNumber || ''),
      'SOLD NAME': clientName || '', 'Date': today(),
    }, { dryRun: false }).catch(() => null);
    if (!res) continue;
    try { await fm.updateRecord('invoice', DWSKU_LAYOUT, res.recordId, { [DWSKU_FIELD]: sku }, { dryRun: false }); } catch {}
    created.push({ recordId: res.recordId, sku });
  }
  return { created, skus };
}

// Steve's rule (2026-07-07, refined 2026-07-09): ONE invoice record per SKU, but
// ALL the money rides the FIRST invoice and every subsequent invoice shows JUST
// its SKU. The first (money) record has ONE line = the order's TOTAL units × the
// per-unit price, so GRAND TOTAL (= Q×NET, a calc) equals the whole merchandise
// subtotal — real units, real per-unit price (exact for a uniform-price order,
// e.g. a batch of $4.25 samples; a blended per-unit for a mixed-price order).
// PAID ON ACCOUNT = that grand total (+ tax + shipping, which ride the money
// record only). Every subsequent per-SKU record is a $0 stub — just its SKU, with
// NET/RETAIL/PAID = 0 — so the order's money is counted ONCE while each pattern
// still gets its own record for FileMaker's Name/Color lookup. Returns the first
// invoice number + its grand total (what the Slack alert references); the $0
// stubs have GRAND TOTAL 2 = 0 so the monitor skips them.
export async function createInvoiceForOrder(order, accountNumber, { commit = true } = {}) {
  const c = order.customer || {}, s = order.shipping_address || order.billing_address || {};
  const name = ((c.first_name||'') + ' ' + (c.last_name||'')).trim();
  const ship = order.shipping_lines?.[0];
  const shipVia = ship?.title || '';
  const shipping = Number(ship?.price || 0);
  const orderTax = Number(order.total_tax || 0);
  const items = order.line_items || [];
  if (!items.length) throw new Error('order has no line items');

  // whole-order merchandise subtotal — lands on the FIRST record only
  const merch = r2(items.reduce((a, li) => a + (Number(li.price) || 0) * (li.quantity || 1), 0));

  if (!commit) {
    return { wouldCreate: true, invoiceNumber: '(next)', invoiceCount: items.length,
      cust_po: order.order_number, account: accountNumber, shipVia, shipping, orderTax, resale: resale(s),
      merch,
      moneyLine: (() => { const L = moneyLine(items, merch); return { detail: L.detail, qty: L.qty, unit: L.unit, price: L.price, ext: r2(L.qty * L.price) }; })(),
      stubRecords: items.slice(1).map((li) => ({ sku: li.sku, qty: li.quantity || 1, dollars: 0 })) };
  }

  // ── Idempotency guard (2026-07-14): never create a second invoice for an
  // order FileMaker already has one for. Dedup was previously local-only
  // (state.processed[] in shopify-sync.js), so any re-process — a state reset,
  // manual keying, a second machine, or a run overlapping the daemon — minted a
  // duplicate invoice (e.g. #32609 → #101633 + #102735). Check FileMaker truth
  // by Cust PO (= unique Shopify order number) and return the existing invoice.
  const existing = await fm
    .findRecords('invoice', HDR, { 'Cust PO': '==' + order.order_number }, { limit: 10 })
    .catch(() => ({ records: [] }));
  const prior = (existing.records || [])
    .map((r) => r.fieldData || {})
    .filter((f) => /^\d+$/.test(String(f['Invoice'] || '').trim()))
    .map((f) => parseInt(f['Invoice'], 10))
    .sort((a, b) => a - b);
  if (prior.length) {
    return {
      invoiceNumber: String(prior[0]), recordId: null, grandTotal: 0,
      cust_po: order.order_number, account: accountNumber, invoiceCount: prior.length,
      invoices: prior.map((n) => ({ invoiceNumber: String(n) })), skipped: 'already-invoiced',
    };
  }

  const sharedHeader = {
    'Cust PO': String(order.order_number), 'Account #': String(accountNumber || ''),
    'SOLD NAME': name, 'SOLD COMPANY': s.company || '', 'SOLD ADDRESS': s.address1 || '', 'SOLD CITY': s.city || '',
    'SOLD STATE': s.province_code || '', 'SOLD ZIP': s.zip || '', 'TELEPHONE': s.phone || order.phone || '',
    'ship name': name, 'SHIP COMAPNY': s.company || '', 'SHIP ADDRESS': s.address1 || '', 'SHIP CITY': s.city || '',
    'SHIP STATE': s.province_code || '', 'SHIP ZIP': s.zip || '', 'Date': today(),
  };

  let nextNum = await nextInvoiceNumber();
  let firstInvoiceNumber = null, firstGrand = 0, firstId = null;
  const created = [];

  for (let i = 0; i < items.length; i++) {
    const li = items[i];
    const isFirst = i === 0;

    // allocate a free invoice number + create the header record
    let id = null, createdInv = null;
    for (let tries = 0; tries < 8 && !id; tries++, nextNum++) {
      const dup = await fm.findRecords('invoice', HDR, { 'Invoice': '==' + nextNum }, { limit: 1 }).catch(() => ({ records: [] }));
      if (dup.records.length) continue;
      const res = await fm.createRecord('invoice', HDR, { 'Invoice': String(nextNum), ...sharedHeader }, { dryRun: false }).catch(() => null);
      if (res) { id = res.recordId; createdInv = nextNum; } // for-loop's single nextNum++ then advances to the next free number (no gaps)
    }
    if (!id) throw new Error('could not allocate an invoice number');

    // header extras (same on every record)
    await patch(id, 'SALES PERSON', 'Otto');
    await patch(id, 'BUYER', name);
    await patch(id, 'Resale #', resale(s));
    try { await fm.updateRecord('invoice', RESALE_LAYOUT, id, { 'Resale card lookup': resale(s) }, { dryRun: false }); } catch {}
    await patch(id, 'check Number', today());
    // tax + shipping ride the money record only
    await patch(id, 'SHIP VIA', isFirst ? shipVia : '');
    await patch(id, 'SHIPPING  HANDLING', isFirst ? shipping : 0);
    await patch(id, 'SALES TAX YN', (isFirst && orderTax > 0) ? 'Y' : 'N');

    // The FIRST record carries ALL the order's money on ONE line: total units ×
    // per-unit price (GRAND TOTAL = the whole merch subtotal), with real units +
    // a real per-unit price. Every OTHER record shows JUST its SKU at $0, so all
    // the dollars stay on the first invoice and each pattern still gets a record.
    if (isFirst) {
      const L = moneyLine(items, merch);
      await patch(id, 'DETAIL 1(1)', L.detail);
      await patch(id, 'Q1(1)', L.qty);
      await patch(id, 'Unit 1(1)', L.unit);
      await patch(id, 'RETAIL 1(1)', L.price);
      await patch(id, 'NET 1(1)', L.price);
      await patch(id, 'Sidemark 1(1)', s.company || '');
    } else {
      const desc = `${baseSku(li.sku)} — ${li.name || ''}`.trim();
      await patch(id, 'DETAIL 1(1)', desc.slice(0, 250));
      await patch(id, 'Q1(1)', li.quantity || 1);
      await patch(id, 'Unit 1(1)', uom(li));
      await patch(id, 'RETAIL 1(1)', 0);
      await patch(id, 'NET 1(1)', 0);
      await patch(id, 'Sidemark 1(1)', s.company || '');
    }

    // cross-table SKU fields for THIS sku (FileMaker pattern lookup keys off these)
    try { await fm.updateRecord('invoice', SKU_LAYOUT, id, { 'JS Easy SKU #': baseSku(li.sku) }, { dryRun: false }); } catch {}
    try { await fm.updateRecord('invoice', WCPAT_LAYOUT, id, { 'wc pattern': wcPattern(li.sku) }, { dryRun: false }); } catch {}
    try { await fm.updateRecord('invoice', DWSKU_LAYOUT, id, { [DWSKU_FIELD]: dwSku(li.sku) }, { dryRun: false }); } catch {}

    // Auto-complete the FileMaker WALLPAPER master record for this sku from
    // dw_unified so the invoice's width/name/vid/supplier lookups resolve, then
    // write the copy-lookup fields (VID + Detail 1 Mfr Number) onto this line.
    // Never let this break invoicing — the WALLPAPER completion is best-effort.
    // If the SKU can't be resolved (no findable mfr number) OR ensureWallpaper
    // throws, the invoice line STILL stands (money must never be blocked by a
    // catalog gap) but we LOUDLY flag it instead of silently writing blank
    // vid/mfr — console.warn + idempotent data/flagged-skus.jsonl queue + an
    // internal Slack + George alert to Steve (drainable via drain-flagged-skus).
    try {
      const wp = await ensureWallpaper(dwSku(li.sku));
      if (wp?.ok) {
        try { await fm.updateRecord('invoice', SHIP_LAYOUT, id, { 'VID': wp.vid || '', 'Detail 1 Mfr Number': wp.mfr }, { dryRun: false }); } catch {}
      } else {
        await flagSku({ orderId: order.id, orderName: order.name || `#${order.order_number}`,
          invoiceNumber: createdInv, sku: li.sku, dwSku: dwSku(li.sku),
          reason: wp?.reason || 'ensureWallpaper returned not-ok' }).catch(() => {});
      }
    } catch (e) {
      await flagSku({ orderId: order.id, orderName: order.name || `#${order.order_number}`,
        invoiceNumber: createdInv, sku: li.sku, dwSku: dwSku(li.sku),
        reason: `ensureWallpaper threw: ${e.message}` }).catch(() => {});
    }

    // paid-in-full = the EXACT order total (merch + tax + shipping) on the first
    // record, 0 on the SKU records. Computed directly (not read from GRAND TOTAL)
    // so the money is exact even when a summarized per-unit line rounds by a cent.
    const grand = isFirst ? r2(merch + orderTax + shipping) : 0;
    await patch(id, 'PAID ON ACCOUNT', grand);
    await patch(id, 'Notes', isFirst
      ? `Imported by Otto — ${stamp()}`
      : `Imported by Otto — ${stamp()} · $0 SKU record; order total billed on invoice ${firstInvoiceNumber}`);

    if (isFirst) { firstInvoiceNumber = createdInv; firstGrand = grand; firstId = id; }
    created.push({ invoiceNumber: String(createdInv), sku: li.sku, amount: isFirst ? merch : 0 });
  }

  return { invoiceNumber: String(firstInvoiceNumber), recordId: firstId, grandTotal: firstGrand,
    cust_po: order.order_number, account: accountNumber, invoiceCount: created.length, invoices: created };
}