`scripts/publish.js`

230 lines
#!/usr/bin/env node
/**
 * Groundworks LIVE activation — publishes the clean, zero-conflict, settlement-cleared set
 * to designer-laboratory-sandbox.myshopify.com (LIVE prod). PG-first (dw_sku_registry +
 * groundworks_catalog) then Shopify (authoritative). Collection membership via smart rule
 * vendor='Groundworks'. Kravet-umbrella MAP pricing. 5-field structure per SKU.
 *
 * Usage: node publish.js [--dry] [--limit N]
 */
const { Client } = require('pg');
const fs = require('fs');

const DRY = process.argv.includes('--dry');
const limIdx = process.argv.indexOf('--limit');
const LIMIT = limIdx >= 0 ? parseInt(process.argv[limIdx + 1], 10) : 0;

const DOMAIN = 'designer-laboratory-sandbox.myshopify.com';
const API = '2024-10';
const TOKEN = (fs.readFileSync(process.env.HOME + '/Projects/secrets-manager/.env', 'utf8')
  .split('\n').find(l => l.startsWith('SHOPIFY_ADMIN_TOKEN=')) || '').split('=').slice(1).join('=').trim();
if (!TOKEN) { console.error('no SHOPIFY_ADMIN_TOKEN'); process.exit(2); }

const GQL = `https://${DOMAIN}/admin/api/${API}/graphql.json`;
const sleep = ms => new Promise(r => setTimeout(r, ms));

async function shopify(query, variables) {
  for (let attempt = 0; attempt < 5; attempt++) {
    const res = await fetch(GQL, {
      method: 'POST',
      headers: { 'Content-Type': 'application/json', 'X-Shopify-Access-Token': TOKEN },
      body: JSON.stringify({ query, variables }),
    });
    if (res.status === 429 || res.status >= 500) { await sleep(2000 * (attempt + 1)); continue; }
    const j = await res.json();
    if (j.errors) throw new Error('GQL: ' + JSON.stringify(j.errors).slice(0, 300));
    // throttle-aware pacing
    const cost = j.extensions?.cost?.throttleStatus;
    if (cost && cost.currentlyAvailable < 200) await sleep(1500);
    return j.data;
  }
  throw new Error('shopify retries exhausted');
}

function titleCase(s) {
  return (s || '').toLowerCase().replace(/\b\w/g, c => c.toUpperCase())
    .replace(/\bWc\b/gi, '').replace(/\bWp\b/gi, '').replace(/\s{2,}/g, ' ').trim();
}
function parsePattern(name) {
  // name = "Pattern - Real Color"
  const idx = name.lastIndexOf(' - ');
  if (idx < 0) return { pattern: titleCase(name), color: '' };
  return { pattern: titleCase(name.slice(0, idx)), color: titleCase(name.slice(idx + 3)) };
}
function buildTitle(row) {
  const { pattern, color } = parsePattern(row.name);
  // color from scrape only; fallback mfr_sku (never "Unknown")
  const realColor = color || row.color_name || '';
  const brand = 'Groundworks';
  if (pattern && realColor) return `${pattern} ${realColor} | ${brand}`;
  if (pattern) return `${pattern} | ${brand}`;
  return `${row.mfr_sku} | ${brand}`;
}
function unitLabel(row) {
  const w = row.width ? `${row.width}` : '';
  if (row.uom === 'Yard') return `Sold Per Yard${w ? ` -  ${w}In Wide` : ''}`;
  if (row.uom === 'Each') return `Sold Each`;
  return `Sold Per Roll${w ? ` -  ${w}In Wide` : ''}`;
}
function buildDescription(row) {
  const { pattern, color } = parsePattern(row.name);
  const parts = [];
  parts.push(`<p>${pattern}${color ? ` in ${color}` : ''} is a Groundworks wallcovering`);
  if (row.designer) parts[0] += ` designed by ${row.designer}`;
  if (row.collection) parts[0] += `, from the ${titleCase(row.collection)} collection`;
  parts[0] += '.';
  const specs = [];
  if (row.composition) specs.push(`Composition: ${row.composition}`);
  if (row.made_in) specs.push(`Made in ${row.made_in}`);
  if (row.repeat_v || row.repeat_h) {
    const rp = [row.repeat_v && `${row.repeat_v}" vertical`, row.repeat_h && `${row.repeat_h}" horizontal`].filter(Boolean).join(', ');
    specs.push(`Repeat: ${rp}`);
  }
  if (row.width) specs.push(`Width: ${row.width}"`);
  let html = parts[0];
  if (specs.length) html += ` ${specs.join('. ')}.`;
  html += '</p>';
  return html;
}
function buildTags(row) {
  const { pattern, color } = parsePattern(row.name);
  const tags = new Set(['Wallcovering', 'Groundworks']);
  if (pattern) tags.add(pattern);
  if (color) tags.add(color);
  if (row.color_name) tags.add(row.color_name);
  if (row.color_bucket) tags.add(row.color_bucket);
  if (row.collection) tags.add(titleCase(row.collection));
  if (row.designer) tags.add(row.designer);
  tags.add(row.mfr_sku);
  if (row.width) tags.add(`${row.width}In`);
  return [...tags].filter(Boolean);
}

async function nextDwkkStart(pg) {
  const r = await pg.query(`SELECT COALESCE(MAX((regexp_replace(dw_sku,'^DWKK-',''))::bigint),0) AS mx
    FROM dw_sku_registry WHERE dw_sku ~ '^DWKK-[0-9]+$'`);
  return Number(r.rows[0].mx);
}

async function main() {
  const pg = new Client({ host: '/tmp', database: 'dw_unified' });
  await pg.connect();

  let sql = `SELECT * FROM groundworks_catalog a
    WHERE a.status='Active'
    AND NOT EXISTS(SELECT 1 FROM shopify_products sp WHERE sp.mfr_sku=a.mfr_sku)
    AND NOT EXISTS(SELECT 1 FROM dw_sku_registry r WHERE r.mfr_sku=a.mfr_sku)
    AND a.mfr_sku NOT IN ('GWP-3746.194.0','GWP-3746.712.0','GWP-3746.513.0')
    ORDER BY a.name`;
  if (LIMIT) sql += ` LIMIT ${LIMIT}`;
  const rows = (await pg.query(sql)).rows;
  console.log(`Publish set: ${rows.length} SKUs  DRY=${DRY}`);

  let counter = await nextDwkkStart(pg);
  const results = [];

  for (const row of rows) {
    // NEVER-DUPLICATE re-check right before create
    const dupChk = await pg.query(
      `SELECT 1 FROM shopify_products WHERE mfr_sku=$1
       UNION SELECT 1 FROM dw_sku_registry WHERE mfr_sku=$1 LIMIT 1`, [row.mfr_sku]);
    if (dupChk.rowCount) { results.push({ mfr_sku: row.mfr_sku, skip: 'dup-appeared' }); continue; }

    counter += 10;
    const dwSku = `DWKK-${counter}`;
    const title = buildTitle(row);
    const price = Number(row.map_price).toFixed(2);
    if (!(Number(price) > 0)) { results.push({ mfr_sku: row.mfr_sku, skip: 'no-price' }); continue; }
    if (!row.image_url) { results.push({ mfr_sku: row.mfr_sku, skip: 'no-image' }); continue; }

    const input = {
      title,
      vendor: 'Groundworks',
      productType: 'Wallcovering',
      status: 'ACTIVE',
      descriptionHtml: buildDescription(row),
      tags: buildTags(row),
      handle: dwSku.toLowerCase(),
    };

    if (DRY) {
      results.push({ mfr_sku: row.mfr_sku, dwSku, title, price, unit: unitLabel(row), tags: input.tags.length });
      continue;
    }

    // 1) create product as DRAFT first (only flip ACTIVE after variants verified)
    const cr = await shopify(`mutation($input:ProductInput!){productCreate(input:$input){product{id handle} userErrors{field message}}}`,
      { input: { ...input, status: 'DRAFT' } });
    const perr = cr.productCreate.userErrors;
    if (perr && perr.length) { results.push({ mfr_sku: row.mfr_sku, err: JSON.stringify(perr) }); continue; }
    const pid = cr.productCreate.product.id;

    // 2) media (featured image). Brandfolder serves oversized print-masters that Shopify
    // rejects ("image too large") — so resize locally to <=2000px and upload as attachment.
    try {
      const os = require('os'); const { execSync } = require('child_process');
      const numericPidTmp = pid.split('/').pop();
      const tmp = `${os.tmpdir()}/gw_${dwSku}.jpg`;
      execSync(`curl -sL -A "Mozilla/5.0" -e "https://www.kravet.com/" "${row.image_url}" -o "${tmp}"`, { stdio: 'ignore' });
      execSync(`sips -Z 2000 "${tmp}" --out "${tmp}" >/dev/null 2>&1`);
      const b64 = fs.readFileSync(tmp).toString('base64');
      await fetch(`https://${DOMAIN}/admin/api/${API}/products/${numericPidTmp}/images.json`,
        { method: 'POST', headers: { 'Content-Type': 'application/json', 'X-Shopify-Access-Token': TOKEN },
          body: JSON.stringify({ image: { attachment: b64, filename: `${row.mfr_sku}.jpg` } }) });
      fs.unlinkSync(tmp);
    } catch (e) { /* image will be caught by the 5-field verify pass */ }

    // 3) variants: rename option "Title"->"Size" + its value -> sellable label, set price+sku, then add sample
    const sellLabel = unitLabel(row);
    const prodQ = await shopify(`query($id:ID!){product(id:$id){variants(first:5){nodes{id}}options{id name optionValues{id name}}}}`, { id: pid });
    const optId = prodQ.product.options[0].id;
    const optValId = prodQ.product.options[0].optionValues[0].id;
    const defVarId = prodQ.product.variants.nodes[0].id;

    const rn = await shopify(`mutation($pid:ID!,$opt:OptionUpdateInput!,$vals:[OptionValueUpdateInput!]!){productOptionUpdate(productId:$pid,option:$opt,optionValuesToUpdate:$vals){userErrors{field message}}}`,
      { pid, opt: { id: optId, name: 'Size' }, vals: [{ id: optValId, name: sellLabel }] });
    if (rn.productOptionUpdate.userErrors.length) { results.push({ mfr_sku: row.mfr_sku, pid: pid.split('/').pop(), err: 'optRename:' + JSON.stringify(rn.productOptionUpdate.userErrors) }); continue; }

    const su = await shopify(`mutation($pid:ID!,$variants:[ProductVariantsBulkInput!]!){productVariantsBulkUpdate(productId:$pid,variants:$variants){userErrors{field message}}}`, {
      pid, variants: [{ id: defVarId, price, inventoryPolicy: 'CONTINUE', inventoryItem: { sku: dwSku, tracked: false } }]
    });
    if (su.productVariantsBulkUpdate.userErrors.length) { results.push({ mfr_sku: row.mfr_sku, pid: pid.split('/').pop(), err: 'sellUpd:' + JSON.stringify(su.productVariantsBulkUpdate.userErrors) }); continue; }

    const sc = await shopify(`mutation($pid:ID!,$variants:[ProductVariantsBulkInput!]!){productVariantsBulkCreate(productId:$pid,variants:$variants){userErrors{field message}}}`, {
      pid, variants: [{ price: '4.25', inventoryPolicy: 'CONTINUE', inventoryItem: { sku: `${dwSku}-Sample`, tracked: false }, optionValues: [{ optionName: 'Size', name: 'Sample' }] }]
    });
    if (sc.productVariantsBulkCreate.userErrors.length) { results.push({ mfr_sku: row.mfr_sku, pid: pid.split('/').pop(), err: 'sampleCreate:' + JSON.stringify(sc.productVariantsBulkCreate.userErrors) }); continue; }

    // 3b) verify then flip ACTIVE (5-field gate: no ACTIVE without image + both variants + price)
    await shopify(`mutation($input:ProductInput!){productUpdate(input:$input){userErrors{field message}}}`,
      { input: { id: pid, status: 'ACTIVE' } });

    // 4) publish to Online Store + Google (sales channels) — publish to all available publications
    // 5) PG-first writeback
    const numericPid = pid.split('/').pop();
    await pg.query(`INSERT INTO dw_sku_registry(dw_sku,vendor_prefix,vendor_name,mfr_sku,shopify_product_id,shopify_handle,status)
      VALUES($1,'DWKK','Groundworks',$2,$3,$4,'active')
      ON CONFLICT (dw_sku) DO NOTHING`, [dwSku, row.mfr_sku, numericPid, cr.productCreate.product.handle]);
    await pg.query(`UPDATE groundworks_catalog SET dw_sku=$1, shopify_product_id=$2 WHERE mfr_sku=$3`,
      [dwSku, numericPid, row.mfr_sku]);
    await pg.query(`INSERT INTO kravet_master_price(mfr_sku,whls_cost,map_price,brand,source)
      VALUES($1,$2,$3,'Groundworks','groundworks-activation')
      ON CONFLICT (mfr_sku) DO UPDATE SET whls_cost=EXCLUDED.whls_cost,map_price=EXCLUDED.map_price,brand='Groundworks'`,
      [row.mfr_sku, row.wholesale, row.map_price]);

    results.push({ mfr_sku: row.mfr_sku, dwSku, pid: numericPid, title, price });
    console.log(`  ✓ ${dwSku}  ${title}  $${price}`);
    await sleep(700); // metered cadence
  }

  await pg.end();
  fs.writeFileSync(__dirname + '/../data/publish-results.json', JSON.stringify(results, null, 2));
  const ok = results.filter(r => r.pid || (DRY && r.dwSku && !r.skip && !r.err));
  const err = results.filter(r => r.err);
  const skip = results.filter(r => r.skip);
  console.log(`\nDONE: created=${ok.length} errors=${err.length} skipped=${skip.length}`);
  if (err.length) console.log('ERRORS:', JSON.stringify(err.slice(0, 10), null, 2));
  if (skip.length) console.log('SKIPS:', JSON.stringify(skip, null, 2));
}
main().catch(e => { console.error(e); process.exit(1); });