`scripts/backfill_shopify_ids.js`

93 lines
#!/usr/bin/env node
/**
 * After Steve uploads the CSV, every variant in DW Shopify has SKU = `DWSP-<orig sku>`.
 * Walk all Sister Parish products in DW Shopify, match each variant's SKU back to
 * its `sp_product_id` in dw_unified.sisterparish_catalog, and write
 * shopify_product_id + shopify_handle into that table.
 *
 * Idempotent — safe to re-run.
 */
require('dotenv').config({ path: require('path').join(__dirname,'..','.env') });
const { execSync } = require('child_process');

const SHOP  = process.env.SHOPIFY_STORE;
const TOKEN = process.env.SHOPIFY_ADMIN_TOKEN;
if (!SHOP || !TOKEN) { console.error('Need SHOPIFY_STORE + SHOPIFY_ADMIN_TOKEN'); process.exit(1); }

const BASE = `https://${SHOP}/admin/api/2026-01`;

async function shopify(path) {
  const res = await fetch(`${BASE}${path}`, { headers: { 'X-Shopify-Access-Token': TOKEN } });
  const text = await res.text();
  if (!res.ok) throw new Error(`${res.status}: ${text.slice(0,300)}`);
  return { json: JSON.parse(text), link: res.headers.get('link') || '' };
}

function nextPageInfo(link) {
  // Shopify cursor pagination — extract rel="next" page_info
  const m = link.match(/<[^>]*[?&]page_info=([^&>]+)[^>]*>;\s*rel="next"/);
  return m ? m[1] : null;
}

(async () => {
  // Walk all Sister Parish products (paginated)
  const all = [];
  let path = '/products.json?vendor=Sister+Parish&limit=250&fields=id,handle,title,variants';
  for (;;) {
    const r = await shopify(path);
    all.push(...r.json.products);
    const next = nextPageInfo(r.link);
    if (!next) break;
    path = `/products.json?limit=250&fields=id,handle,title,variants&page_info=${next}`;
  }
  console.log(`Sister Parish products in DW Shopify: ${all.length}`);

  if (all.length === 0) {
    console.log('Nothing to backfill yet — re-run after CSV import finishes.');
    return;
  }

  // Map: source_product_id (parsed from DWSP-<orig sku> back to sp_product_id) — we need a join.
  // We'll use the PG catalog and look up sp_product_id by variant.sku (stripped of DWSP-).
  // First, write all (shopify_product_id, shopify_handle, variant_sku_list) to /tmp, then JOIN in SQL.
  const fs = require('fs');
  const lines = ['shopify_product_id\tshopify_handle\torig_sku'];
  for (const p of all) {
    for (const v of p.variants) {
      const orig = (v.sku || '').replace(/^DWSP-/, '');
      lines.push(`${p.id}\t${p.handle}\t${orig}`);
    }
  }
  fs.writeFileSync('/tmp/sp_shopify_map.tsv', lines.join('\n'));

  const sql = `
ALTER TABLE sisterparish_catalog ADD COLUMN IF NOT EXISTS shopify_product_id BIGINT;
ALTER TABLE sisterparish_catalog ADD COLUMN IF NOT EXISTS shopify_handle TEXT;
ALTER TABLE sisterparish_catalog ADD COLUMN IF NOT EXISTS shopify_synced_at TIMESTAMPTZ;

CREATE TEMP TABLE sp_map (shopify_product_id BIGINT, shopify_handle TEXT, orig_sku TEXT);
\\copy sp_map FROM '/tmp/sp_shopify_map.tsv' WITH (FORMAT csv, DELIMITER E'\\t', HEADER true);

-- Match: for each catalog row, find a variant in sp_map whose orig_sku matches any variant in JSONB.
UPDATE sisterparish_catalog c
SET shopify_product_id = m.shopify_product_id,
    shopify_handle     = m.shopify_handle,
    shopify_synced_at  = NOW()
FROM (
  SELECT DISTINCT ON (c2.sp_product_id) c2.sp_product_id, m.shopify_product_id, m.shopify_handle
  FROM sisterparish_catalog c2
  JOIN jsonb_array_elements(c2.variants) AS v ON true
  JOIN sp_map m ON m.orig_sku = v->>'sku'
) m
WHERE m.sp_product_id = c.sp_product_id;

SELECT
  COUNT(*) FILTER (WHERE shopify_product_id IS NOT NULL) AS matched,
  COUNT(*) FILTER (WHERE shopify_product_id IS NULL)     AS unmatched,
  COUNT(*) AS total
FROM sisterparish_catalog;
`;
  fs.writeFileSync('/tmp/sp_backfill.sql', sql);
  execSync('psql dw_unified -f /tmp/sp_backfill.sql', { stdio: 'inherit' });
})().catch(e => { console.error('FATAL', e); process.exit(1); });