`scripts/fm-wallpaper-sync.mjs`

232 lines
#!/usr/bin/env node
// FileMaker WALLPAPER  →  dw_unified.filemaker_wallpaper  mirror.
// ─────────────────────────────────────────────────────────────────────────────
// WHY: all.designerwallcoverings.com / substitutefinder / dw-photo-capture all read
// the /api/catalog-full feed, which is Shopify-driven. Steve's rule: FileMaker Pro's
// WALLPAPER file is the SOURCE OF TRUTH — the catalog should show what FMPro says is
// live (no `Date Discontinued`), display what FMPro says the mfr number is, and be
// the source for a FileMaker→Shopify spec push. FileMaker's Data API is slow + rate-
// limited over ~160k master rows, so we mirror it into Postgres once (nightly) and the
// feed LEFT-JOINs the mirror by dw_sku. This script owns that mirror.
//
// READ LAYOUT: "*List Wallpapers - Shopify Detail Database" — the FileMaker layout
// purpose-built to stage every field for Shopify (Shopify Handle, comboskuwithdash,
// Mfr Pattern, mfr pattern number, Width, and the whole `… for Shopify MetaData`
// family). It reads cleanly via the Data API PROVIDED we obey the clean-query rule.
//
// CLEAN-QUERY RULE (hard gotcha, verified): a `Date Discontinued` clause in the find
// query silently breaks the Data API — foundCount>0 but zero rows returned. So we
// query on `Record Type` + `comboskuwithdash` (both safe) and evaluate Date
// Discontinued from the returned fieldData in code.
//
// COST: FileMaker Cloud Data API = already-paid, $0/call. Postgres = $0 (local).
// SAFETY: writes ONLY to the new `filemaker_wallpaper` table. Touches nothing else.
//
// Usage:
//   node scripts/fm-wallpaper-sync.mjs            # full sync (all Master rows)
//   FM_LIMIT=1000 node scripts/fm-wallpaper-sync.mjs   # cap total rows (smoke test)
//   FM_BATCH=300  node scripts/fm-wallpaper-sync.mjs   # page size (default 500)

import { readFileSync } from 'node:fs';
import { join } from 'node:path';
import { pathToFileURL } from 'node:url';
import pg from 'pg';

const FM_MCP_DIR = process.env.FM_MCP_DIR || '/Users/macstudio3/Projects/filemaker-mcp';
const FM_DATABASE = 'WALLPAPER';
const FM_LAYOUT = process.env.FM_LAYOUT || '*List Wallpapers - Shopify Detail Database';
const DSN = process.env.DATABASE_URL || 'postgresql:///dw_unified?host=/tmp';
const BATCH = Math.max(50, +(process.env.FM_BATCH || 500) || 500);
const CAP = process.env.FM_LIMIT ? Math.max(1, +process.env.FM_LIMIT) : Infinity;

// ── FileMaker field names on the Shopify Detail layout (verified via field metadata) ──
const F = {
  dwSku: 'comboskuwithdash',            // "DWT-55001" — the dw_sku join key
  comboSku: 'combo sku',                // "DWT55001"
  handle: 'Shopify Handle',
  mfrPattern: 'Mfr Pattern',            // primary, usually-populated mfr number ("T35100")
  mfrPatternNumber: 'mfr pattern number', // secondary/alt mfr number (often blank)
  name: 'Name of Pattern',              // "SAROKA WALLPAPER - Pearl"
  color: 'Color of Pattern',
  title: 'Title',
  series: 'Series',
  vid: 'vid',                           // vendor id ("thib", "WQ", …)
  recordType: 'Record Type',            // "Master" == a real product record
  dateDiscontinued: 'Date Discontinued',
  bodyHtml: 'Descripton for Shopify Body',
  tags: 'Tags',
  dwRetail: 'Dw Retail Price', netPrice: 'Net Price', variantPrice: 'Variant Price', mfrMap: 'MFR MAP PRICE',
};
// Everything that counts as a "spec" for display + the FileMaker→Shopify push, stored
// as JSONB (non-empty only). Key = our snake_case; value = the FileMaker field name.
const SPEC_FIELDS = {
  width: 'Width', repeat: 'Repeat', content: 'Content',
  sold_per: 'Sold Per', minimum: 'Minimum', collection: 'Collection',
  durability: 'Durability for Shopify MetaData', lead_time: 'Lead Time for Shopify MetaData',
  length: 'Length for Shopify Meta Data', packaged: 'Packaged for Shopify Meta Data',
  qty_order_unit: 'Qty Order Unit for Shopify MetaData', qty_min_unit: 'Qty Minimum Unit for Shopify MetaData',
  unit_of_measure: 'Unit of Measure for Shopify Meta Data', style: 'Style for Shopify MetaData',
  type: 'Type for Shopify MetaData', brand: 'Brand for Shopify MetaData', color: 'Color for Shopify MetaData',
  product_category: 'Product Category for Shopify Sample Data', variant_grams: 'Variant Grams',
  keyword_tags: 'KEYWORD TAGS', collection_tag: 'COLLECTION TAG', mfr_name: 'Mfr Name of Pattern',
};

const s = (f, k) => { const v = f[k]; return v == null ? '' : String(v).trim(); };
const numOrNull = (f, k) => {
  const raw = s(f, k); if (!raw) return null;
  const v = parseFloat(raw.replace(/[^0-9.\-]/g, '')); return Number.isFinite(v) ? v : null;
};
// mfr numbers carry human notes ("SBG3207 -- MUST USE QUOTE #G45"). Keep the raw for
// display + push-safety, and a normalized token for mismatch comparison.
const cleanMfr = (raw) => String(raw || '').split(/--|·|;|\bMUST\b|\bQUOTE\b/i)[0]
  .replace(/[^A-Za-z0-9./-]+/g, '').toUpperCase();

// Load filemaker-mcp/.env (host + Cognito creds) without clobbering our own env.
function loadFmEnv() {
  const raw = readFileSync(join(FM_MCP_DIR, '.env'), 'utf8');
  for (const line of raw.split('\n')) {
    const m = line.match(/^([A-Za-z_][A-Za-z0-9_]*)=(.*)$/); if (!m) continue;
    let v = m[2];
    if ((v.startsWith('"') && v.endsWith('"')) || (v.startsWith("'") && v.endsWith("'"))) v = v.slice(1, -1);
    if (process.env[m[1]] === undefined) process.env[m[1]] = v;
  }
}

const DDL = `
CREATE TABLE IF NOT EXISTS filemaker_wallpaper (
  dw_sku              text PRIMARY KEY,
  combo_sku           text,
  shopify_handle      text,
  mfr_pattern         text,
  mfr_pattern_number  text,
  mfr_number          text,          -- resolved: Mfr Pattern || mfr pattern number
  mfr_number_clean    text,          -- normalized token for mismatch comparison
  name_of_pattern     text,
  color_of_pattern    text,
  title               text,
  series              text,
  vid                 text,
  record_type         text,
  date_discontinued   text,
  is_discontinued     boolean,
  width               text,
  body_html           text,
  tags                text,
  dw_retail_price     numeric,
  net_price           numeric,
  variant_price       numeric,
  mfr_map_price       numeric,
  specs               jsonb,
  fm_record_id        text,
  synced_at           timestamptz DEFAULT now()
);
CREATE INDEX IF NOT EXISTS filemaker_wallpaper_disco_idx ON filemaker_wallpaper (is_discontinued);
CREATE INDEX IF NOT EXISTS filemaker_wallpaper_mfrclean_idx ON filemaker_wallpaper (mfr_number_clean);
`;

const UPSERT = `
INSERT INTO filemaker_wallpaper (
  dw_sku, combo_sku, shopify_handle, mfr_pattern, mfr_pattern_number, mfr_number, mfr_number_clean,
  name_of_pattern, color_of_pattern, title, series, vid, record_type,
  date_discontinued, is_discontinued, width, body_html, tags,
  dw_retail_price, net_price, variant_price, mfr_map_price, specs, fm_record_id, synced_at
) VALUES (
  $1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22,$23,$24, now()
)
ON CONFLICT (dw_sku) DO UPDATE SET
  combo_sku=EXCLUDED.combo_sku, shopify_handle=EXCLUDED.shopify_handle,
  mfr_pattern=EXCLUDED.mfr_pattern, mfr_pattern_number=EXCLUDED.mfr_pattern_number,
  mfr_number=EXCLUDED.mfr_number, mfr_number_clean=EXCLUDED.mfr_number_clean,
  name_of_pattern=EXCLUDED.name_of_pattern, color_of_pattern=EXCLUDED.color_of_pattern,
  title=EXCLUDED.title, series=EXCLUDED.series, vid=EXCLUDED.vid, record_type=EXCLUDED.record_type,
  date_discontinued=EXCLUDED.date_discontinued, is_discontinued=EXCLUDED.is_discontinued,
  width=EXCLUDED.width, body_html=EXCLUDED.body_html, tags=EXCLUDED.tags,
  dw_retail_price=EXCLUDED.dw_retail_price, net_price=EXCLUDED.net_price,
  variant_price=EXCLUDED.variant_price, mfr_map_price=EXCLUDED.mfr_map_price,
  specs=EXCLUDED.specs, fm_record_id=EXCLUDED.fm_record_id, synced_at=now()
`;

function rowFromRecord(rec) {
  const f = rec.fieldData || {};
  const dwSku = s(f, F.dwSku);
  if (!dwSku) return null;                              // skip blank / phantom rows
  const mfrPattern = s(f, F.mfrPattern);
  const mfrPatternNumber = s(f, F.mfrPatternNumber);
  const mfrNumber = mfrPattern || mfrPatternNumber || '';
  const disco = s(f, F.dateDiscontinued);
  const specs = {};
  for (const [key, fmField] of Object.entries(SPEC_FIELDS)) { const v = s(f, fmField); if (v) specs[key] = v; }
  return [
    dwSku, s(f, F.comboSku), s(f, F.handle),
    mfrPattern, mfrPatternNumber, mfrNumber, cleanMfr(mfrNumber),
    s(f, F.name), s(f, F.color), s(f, F.title), s(f, F.series), s(f, F.vid), s(f, F.recordType),
    disco, !!disco, s(f, F.width), s(f, F.bodyHtml), s(f, F.tags),
    numOrNull(f, F.dwRetail), numOrNull(f, F.netPrice), numOrNull(f, F.variantPrice), numOrNull(f, F.mfrMap),
    JSON.stringify(specs), rec.recordId,
  ];
}

async function main() {
  const t0 = Date.now();
  loadFmEnv();
  const fm = await import(pathToFileURL(join(FM_MCP_DIR, 'src', 'fm-client.js')).href);
  const pool = new pg.Pool({ connectionString: DSN, max: 3 });
  await pool.query(DDL);

  // Clean-query rule: Record Type=Master + comboskuwithdash present. NEVER Date Discontinued.
  const query = [{ [F.recordType]: 'Master', [F.dwSku]: '*' }];
  let offset = 1, total = 0, upserted = 0, skipped = 0, disco = 0, found = null;
  console.log(`FM sync: layout="${FM_LAYOUT}", batch=${BATCH}, cap=${CAP === Infinity ? 'all' : CAP}`);

  for (;;) {
    const want = Math.min(BATCH, CAP - total);
    if (want <= 0) break;
    let res;
    try {
      res = await fm.findRecords(FM_DATABASE, FM_LAYOUT, query, { limit: want, offset });
    } catch (e) {
      if (String(e.fmCode) === '401') break;             // "no more records"
      throw e;
    }
    const recs = res.records || [];
    if (found == null) found = res.dataInfo?.foundCount ?? null;
    if (!recs.length) break;

    const values = [];
    for (const rec of recs) {
      const row = rowFromRecord(rec);
      if (!row) { skipped++; continue; }
      if (row[14]) disco++;                              // is_discontinued
      values.push(row);
    }
    // upsert this batch inside one transaction
    if (values.length) {
      const client = await pool.connect();
      try {
        await client.query('BEGIN');
        for (const v of values) await client.query(UPSERT, v);
        await client.query('COMMIT');
        upserted += values.length;
      } catch (e) { await client.query('ROLLBACK'); throw e; }
      finally { client.release(); }
    }
    total += recs.length;
    offset += recs.length;
    if (total % 5000 < BATCH) {
      console.log(`  …${total.toLocaleString()}${found ? '/' + found.toLocaleString() : ''} read · ${upserted.toLocaleString()} upserted · ${disco.toLocaleString()} disco · ${skipped} skipped`);
    }
    if (recs.length < want) break;                       // last page
  }

  const { rows: [c] } = await pool.query(
    `SELECT count(*) total, count(*) FILTER (WHERE is_discontinued) disco,
            count(*) FILTER (WHERE NOT is_discontinued AND mfr_number<>'') live_with_mfr
       FROM filemaker_wallpaper`);
  console.log(`\nFM sync DONE in ${((Date.now() - t0) / 1000).toFixed(1)}s`);
  console.log(`  read ${total.toLocaleString()} · upserted ${upserted.toLocaleString()} · skipped ${skipped}`);
  console.log(`  table now: ${(+c.total).toLocaleString()} rows · ${(+c.disco).toLocaleString()} discontinued · ${(+c.live_with_mfr).toLocaleString()} live-with-mfr`);
  await pool.end();
}

main().catch((e) => { console.error('FM sync FAILED:', e.message); process.exit(1); });