`scripts/shopify-push.mjs`

194 lines
#!/usr/bin/env node
// FileMaker → Shopify spec push worker (per-SKU).  DRY-RUN by default.
// ─────────────────────────────────────────────────────────────────────────────
// Steve: "Create button to update shopify with name, mfr, width, all specs" — the
// data flowing FROM FileMaker's WALLPAPER master INTO the live Shopify product. This
// worker owns one SKU: read the FileMaker mirror row + the live Shopify product, compute
// the diff, and (only when explicitly gated) write the non-empty, changed fields.
//
// Spawned by all-designerwallcoverings/server.js (the /api/shopify-push endpoint), same
// pattern as scripts/live-scrape.js. Emits ONE JSON object on stdout.
//
// HARD SAFETY RAILS:
//   • NEVER writes an empty FileMaker value (a blank FM cell must not blank live Shopify data).
//   • NEVER touches custom.material (the double-encoding-bug field) or any color_* / color_details
//     metafield (owned by the local PIL color-enrichment pipeline).
//   • Only writes metafield keys that ALREADY EXIST on the product, except the core mfr + width
//     keys which are allowed to be created — so the push can't sprawl new metafields.
//   • --commit is a NO-OP unless SHOPIFY_PUSH_ENABLED=1 is ALSO set in the environment. The
//     dry-run diff is always free and side-effect-free. Live writes stay Steve-gated.
//
// Usage:
//   node scripts/shopify-push.mjs <dw_sku>            # dry-run: prints the diff, writes nothing
//   node scripts/shopify-push.mjs <dw_sku> --commit   # write (requires SHOPIFY_PUSH_ENABLED=1)

import pg from 'pg';
import fs from 'node:fs';
import { pathToFileURL } from 'node:url';
// Portable resolve of the shared Shopify Admin client — env override first, then the mac3
// designerwallcoverings repo, then a co-located copy if one is vendored. Keeps the worker from
// hard-crashing on a missing absolute path when it runs somewhere other than mac3.
const SHOPIFY_LIB = [
  process.env.SHOPIFY_LIB,
  '/Users/macstudio3/Projects/designerwallcoverings/scripts/lib/shopify.mjs',
  new URL('./lib/shopify.mjs', import.meta.url).pathname,
].find((p) => p && fs.existsSync(p));
const gql = SHOPIFY_LIB
  ? (await import(pathToFileURL(SHOPIFY_LIB).href)).gql
  : () => { throw new Error('Shopify client not found (set SHOPIFY_LIB or install the shared lib)'); };

const DSN = process.env.DATABASE_URL || 'postgresql:///dw_unified?host=/tmp';
const dwSkuArg = process.argv[2];
const COMMIT = process.argv.includes('--commit');
const PUSH_ENABLED = process.env.SHOPIFY_PUSH_ENABLED === '1';
const out = (o) => { process.stdout.write(JSON.stringify(o)); process.exit(o.ok === false ? 1 : 0); };

if (!dwSkuArg) out({ ok: false, reason: 'dw_sku required' });

// ── FileMaker-field → Shopify-metafield target map ───────────────────────────────
// Each FM value fans out to the DW metafield keys that mirror it (discovered from a real
// product). `create:true` = allowed to be created if absent (the core identity fields);
// everything else is written ONLY if the key already exists on the product (no sprawl).
// Deliberately EXCLUDED: custom.material (double-encode bug), custom.color* / color_details
// (local color pipeline owns those).
const T = (namespace, key, create = false) => ({ namespace, key, create });
const FIELD_TARGETS = {
  // key = FileMaker mirror field OR specs.<key>;  value = metafield targets
  mfr_number: [T('custom', 'manufacturer_sku', true), T('dwc', 'manufacturer_sku', true)],
  width:      [T('specifications', 'width', true), T('global', 'width', true), T('dwc', 'width'), T('custom', 'width')],
  content:    [T('global', 'Contents'), T('dwc', 'contents'), T('specifications', 'material'), T('global', 'material')],
  collection: [T('global', 'Collection'), T('custom', 'collection_name')],
  length:     [T('global', 'length'), T('custom', 'length'), T('specifications', 'roll_length')],
  brand:      [T('global', 'Brand'), T('custom', 'brand')],
  type:       [T('global', 'type')],
  style:      [T('specs', 'style')],
  sold_per:   [T('global', 'sold_by')],
  product_category: [T('specs', 'pattern')],
};

async function main() {
  const pool = new pg.Pool({ connectionString: DSN, max: 2 });
  // FileMaker's comboskuwithdash is bare (no -SAMPLE); a card may pass the Shopify variant sku
  // WITH the suffix. Normalize once so BOTH lookups key on the same bare dw_sku.
  const dwBare = dwSkuArg.replace(/-sample$/i, '');
  // 1. FileMaker source of truth
  const { rows: fmRows } = await pool.query(
    `SELECT * FROM filemaker_wallpaper WHERE upper(dw_sku)=upper($1) LIMIT 1`, [dwBare]);
  const fm = fmRows[0];
  if (!fm) { await pool.end(); return out({ ok: false, dw_sku: dwSkuArg, reason: 'not in FileMaker WALLPAPER file' }); }
  if (fm.is_discontinued) { await pool.end(); return out({ ok: false, dw_sku: dwSkuArg, reason: 'FileMaker marks this DISCONTINUED — refusing to push' }); }

  // 2. the live Shopify product (GID via the mirror). The SKU may live in dw_sku, variant_sku,
  // OR sku (dw_sku is often null on the sample-variant rows) — match any, -SAMPLE-stripped.
  const { rows: spRows } = await pool.query(
    `SELECT shopify_id, handle, title, status FROM shopify_products
      WHERE upper($1) IN (
        upper(regexp_replace(coalesce(dw_sku,''),'-SAMPLE$','','i')),
        upper(regexp_replace(coalesce(variant_sku,''),'-SAMPLE$','','i')),
        upper(regexp_replace(coalesce(sku,''),'-SAMPLE$','','i')) )
        AND shopify_id IS NOT NULL
      ORDER BY (upper(status)='ACTIVE') DESC LIMIT 1`, [dwBare]);
  await pool.end();
  const sp = spRows[0];
  if (!sp) return out({ ok: false, dw_sku: dwSkuArg, reason: 'no Shopify product for this SKU (FileMaker-only — would need CREATE, not update)', fm_only: true });
  const gid = /^gid:/.test(sp.shopify_id) ? sp.shopify_id : `gid://shopify/Product/${String(sp.shopify_id).match(/\d+$/)?.[0]}`;

  // 3. read the product's current title + existing metafields
  const pd = await gql(`{ product(id:${JSON.stringify(gid)}) { id title status
     metafields(first:80){ nodes { namespace key type value } } } }`);
  if (pd?.__err || !pd?.product) return out({ ok: false, dw_sku: dwSkuArg, reason: 'Shopify product read failed', detail: pd?.__err });
  const prod = pd.product;
  const have = new Map();                                 // "ns.key" → {type,value}
  for (const m of prod.metafields.nodes) have.set(`${m.namespace}.${m.key}`, { type: m.type, value: m.value });

  // 4. build the FileMaker source values (non-empty only)
  const specs = fm.specs || {};
  const srcVal = (fieldKey) => {
    if (fieldKey === 'mfr_number') return (fm.mfr_number || '').trim();
    if (fieldKey === 'width') return (fm.width || specs.width || '').trim();
    return (specs[fieldKey] || '').trim();
  };

  // Don't-downgrade guard: if Shopify's current value already CONTAINS the FileMaker value
  // (normalized to alphanumerics), Shopify is a richer superset ("36\" (91CM)" ⊇ "36\"",
  // "Linen Gesso - Ivory … | Schumacher" ⊇ "Linen Gesso") — pushing would strip detail, so we
  // record it as an info-only skip rather than a proposed change. FileMaker is authoritative
  // for the mfr NUMBER (identity), not for how richly a spec/name is merchandised.
  const norm = (s) => String(s || '').toLowerCase().replace(/[^a-z0-9]/g, '');
  const shopifyRicher = (before, val) => { const b = norm(before), v = norm(val); return !!b && !!v && b.includes(v) && b !== v; };

  // 5. diff
  const changes = [];                                     // proposed metafield changes
  const skipped = [];                                     // info-only: differs but Shopify is richer
  const metafieldsToSet = [];
  for (const [fieldKey, targets] of Object.entries(FIELD_TARGETS)) {
    const val = srcVal(fieldKey);
    if (!val) continue;                                   // RULE: never push an empty FM value
    // Guard: an mfr NUMBER never contains whitespace. When FileMaker's mfr value looks like a
    // name (e.g. "Cabochon Verdigris") but Shopify already has a compact code, don't propose
    // overwriting — surface it for human verification instead. FMPro is authoritative for the
    // mfr, but only when it actually holds a number.
    if (fieldKey === 'mfr_number' && /\s/.test(val)) { skipped.push({ field: fieldKey, to: val, reason: 'FileMaker mfr looks like a name, not a number — verify in FileMaker' }); break; }
    for (const t of targets) {
      const id = `${t.namespace}.${t.key}`;
      const cur = have.get(id);
      if (!cur && !t.create) continue;                    // no sprawl: skip keys not already present
      const before = cur ? (cur.value || '') : null;
      if (String(before ?? '') === val) continue;         // already matches
      if (shopifyRicher(before, val)) { skipped.push({ field: fieldKey, metafield: id, from: before, to: val, reason: 'shopify value is richer' }); continue; }
      changes.push({ field: fieldKey, metafield: id, from: before, to: val });
      metafieldsToSet.push({ ownerId: gid, namespace: t.namespace, key: t.key,
        type: cur?.type || 'single_line_text_field', value: val });
    }
  }
  // title (name) — only when FileMaker has a name, it differs, AND Shopify isn't already richer
  const fmName = (fm.name_of_pattern || fm.title || '').trim();
  let titleChange = null;
  if (fmName && fmName !== (prod.title || '').trim()) {
    if (shopifyRicher(prod.title, fmName)) skipped.push({ field: 'title', from: prod.title, to: fmName, reason: 'shopify title is richer' });
    else titleChange = { from: prod.title, to: fmName };
  }

  const plan = {
    ok: true, dw_sku: fm.dw_sku, product_id: gid, handle: sp.handle,
    shopify_title: prod.title, fm_name: fmName || null,
    fm_mfr_number: fm.mfr_number || null,
    title_change: titleChange,
    metafield_changes: changes,
    skipped_richer: skipped,
    change_count: changes.length + (titleChange ? 1 : 0),
    committed: false,
  };

  if (!COMMIT || plan.change_count === 0) {
    plan.note = plan.change_count === 0 ? 'No changes — Shopify already matches FileMaker.'
      : 'DRY RUN — call with --commit (and SHOPIFY_PUSH_ENABLED=1) to write.';
    return out(plan);
  }

  // ── COMMIT (gated) ──────────────────────────────────────────────────────────────
  if (!PUSH_ENABLED) {
    plan.note = 'COMMIT BLOCKED: SHOPIFY_PUSH_ENABLED is not set. This is the Steve-gated live-write switch.';
    plan.blocked = true;
    return out(plan);
  }
  const errors = [];
  if (titleChange) {
    const r = await gql(`mutation($input:ProductInput!){ productUpdate(input:$input){ userErrors{ field message } } }`,
      { input: { id: gid, title: fmName } });
    if (r?.__err || r?.productUpdate?.userErrors?.length) errors.push({ title: r?.productUpdate?.userErrors || r?.__err });
  }
  for (let i = 0; i < metafieldsToSet.length; i += 25) {
    const batch = metafieldsToSet.slice(i, i + 25);
    const r = await gql(`mutation($m:[MetafieldsSetInput!]!){ metafieldsSet(metafields:$m){ userErrors{ field message } } }`,
      { m: batch });
    if (r?.__err || r?.metafieldsSet?.userErrors?.length) errors.push({ metafields: r?.metafieldsSet?.userErrors || r?.__err });
  }
  plan.committed = errors.length === 0;
  plan.errors = errors;
  plan.note = errors.length ? 'Committed with errors — see errors[].' : 'Committed to live Shopify.';
  return out(plan);
}

main().catch((e) => out({ ok: false, dw_sku: dwSkuArg, reason: 'push worker error: ' + e.message }));