`scripts/shopify-images-sync.mjs`

163 lines
#!/usr/bin/env node
// Shopify product-images sync  →  dw_unified.product_images
// ─────────────────────────────────────────────────────────────────────────────
// The dw_unified.shopify_products mirror keeps only the FEATURED image_url; the full
// per-product galleries live in Shopify. Steve: "need all images for unified except
// archived or disco in all. / substitutes / new." This pulls every non-archived
// product's COMPLETE image set via Shopify's Bulk Operations API (one server-side
// export → a single JSONL, minutes not hours) and loads it into product_images, keyed
// by the numeric Shopify product_id so the all-dw feed can join galleries in at serve time.
//
// COST: Shopify Admin API = free (rate-limited). SAFE: writes ONLY to product_images.
// Full-refresh: rebuilds the table each run (TRUNCATE + reload) so removed images drop out.
//
// Usage: node scripts/shopify-images-sync.mjs

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

const DSN = process.env.DATABASE_URL || 'postgresql:///dw_unified?host=/tmp';
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));
if (!SHOPIFY_LIB) { console.error('Shopify client lib not found (set SHOPIFY_LIB)'); process.exit(1); }
const { gql, ENDPOINT, TOKEN } = await import(pathToFileURL(SHOPIFY_LIB).href);

const sleep = (ms) => new Promise((r) => setTimeout(r, ms));
const pidOf = (gid) => (String(gid || '').match(/(\d+)\s*$/) || [])[1] || null;

const DDL = `
CREATE TABLE IF NOT EXISTS product_images (
  id         bigserial PRIMARY KEY,
  product_id text NOT NULL,
  handle     text,
  status     text,
  position   int,
  image_url  text NOT NULL,
  alt        text,
  width      int,
  height     int,
  synced_at  timestamptz DEFAULT now()
);
CREATE INDEX IF NOT EXISTS product_images_pid_idx ON product_images (product_id);
`;

// One bulk query exports every non-archived product with its full image list. Shopify runs
// it server-side and returns a JSONL where each product node and each nested image node is a
// line; image lines carry __parentId = the product's gid.
const BULK_QUERY = `
{
  products(query: "status:active OR status:draft") {
    edges { node {
      id handle status
      images { edges { node { url altText width height } } }
    } }
  }
}`;

async function startBulk() {
  const m = `mutation {
    bulkOperationRunQuery(query: ${JSON.stringify(BULK_QUERY)}) {
      bulkOperation { id status }
      userErrors { field message }
    }
  }`;
  const d = await gql(m);
  const ue = d?.bulkOperationRunQuery?.userErrors;
  if (ue?.length) throw new Error('bulk start userErrors: ' + JSON.stringify(ue));
  const op = d?.bulkOperationRunQuery?.bulkOperation;
  if (!op) throw new Error('bulk start failed: ' + JSON.stringify(d));
  return op.id;
}

async function pollBulk() {
  for (let i = 0; i < 600; i++) {            // up to ~50 min (5s poll)
    const d = await gql(`{ currentBulkOperation { id status errorCode objectCount url } }`);
    const op = d?.currentBulkOperation;
    if (!op) throw new Error('no currentBulkOperation');
    if (op.status === 'COMPLETED') return op;
    if (['FAILED', 'CANCELED', 'EXPIRED'].includes(op.status)) throw new Error('bulk ' + op.status + ' ' + (op.errorCode || ''));
    if (i % 6 === 0) console.log(`  bulk ${op.status}… objects=${op.objectCount || 0}`);
    await sleep(5000);
  }
  throw new Error('bulk poll timed out');
}

async function downloadJsonl(url, dest) {
  const r = await fetch(url);
  if (!r.ok) throw new Error('download failed ' + r.status);
  const buf = Buffer.from(await r.arrayBuffer());
  fs.writeFileSync(dest, buf);
  return buf.length;
}

async function main() {
  const t0 = Date.now();
  const pool = new pg.Pool({ connectionString: DSN, max: 3 });
  await pool.query(DDL);

  console.log('starting Shopify bulk export of product images…');
  await startBulk();
  const op = await pollBulk();
  console.log(`bulk COMPLETED: ${op.objectCount} objects`);
  if (!op.url) { console.log('no url (0 products?) — nothing to load'); await pool.end(); return; }

  const jsonl = '/tmp/shopify-images-bulk.jsonl';
  const bytes = await downloadJsonl(op.url, jsonl);
  console.log(`downloaded ${(bytes / 1e6).toFixed(1)} MB → ${jsonl}`);

  // Parse: product lines have id+handle+status; image lines have url+__parentId (the product gid).
  // Assign position per product in the order images appear.
  const prodMeta = new Map();               // gid → { handle, status }
  const posByProd = new Map();              // gid → running position counter
  const lines = fs.readFileSync(jsonl, 'utf8').split('\n');
  let images = [], loaded = 0;

  await pool.query('TRUNCATE product_images');
  const flush = async () => {
    if (!images.length) return;
    // multi-row insert
    const vals = [], params = [];
    images.forEach((im, i) => {
      const b = i * 8;
      vals.push(`($${b + 1},$${b + 2},$${b + 3},$${b + 4},$${b + 5},$${b + 6},$${b + 7},$${b + 8})`);
      params.push(im.product_id, im.handle, im.status, im.position, im.image_url, im.alt, im.width, im.height);
    });
    await pool.query(
      `INSERT INTO product_images (product_id, handle, status, position, image_url, alt, width, height) VALUES ${vals.join(',')}`,
      params);
    loaded += images.length; images = [];
  };

  for (const line of lines) {
    if (!line.trim()) continue;
    let o; try { o = JSON.parse(line); } catch { continue; }
    if (o.id && o.id.includes('/Product/')) {         // product line
      prodMeta.set(o.id, { handle: o.handle || null, status: o.status || null });
      continue;
    }
    if (o.url && o.__parentId) {                       // image line
      const meta = prodMeta.get(o.__parentId) || {};
      const pid = pidOf(o.__parentId);
      const pos = (posByProd.get(o.__parentId) || 0) + 1;
      posByProd.set(o.__parentId, pos);
      images.push({ product_id: pid, handle: meta.handle, status: meta.status,
        position: pos, image_url: o.url, alt: o.altText || null, width: o.width || null, height: o.height || null });
      if (images.length >= 500) await flush();
    }
  }
  await flush();

  const { rows: [c] } = await pool.query(
    `SELECT count(*) imgs, count(distinct product_id) prods, round(avg(n),2) avg_per FROM
      (SELECT product_id, count(*) n FROM product_images GROUP BY product_id) t`);
  console.log(`\nimages sync DONE in ${((Date.now() - t0) / 1000).toFixed(1)}s`);
  console.log(`  ${(+c.imgs).toLocaleString()} images across ${(+c.prods).toLocaleString()} products (avg ${c.avg_per}/product)`);
  await pool.end();
}

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