`scripts/reconcile-from-shopify.js`

61 lines
'use strict';
/* READ Shopify live Rebel Walls products -> stamp local PG shopify_product_id by dw_sku.
 * Local-PG write ONLY. No Shopify writes. Fixes the PG<->Shopify drift before any resume. */
const { gqlRetry, psql } = require('./_shop');
const DRY = process.argv.includes('--dry');

const Q = `query($cursor:String){
  products(first:250, query:"vendor:'Rebel Walls'", after:$cursor){
    pageInfo{ hasNextPage endCursor }
    edges{ node{ id status variants(first:5){ edges{ node{ sku } } } } }
  }
}`;

(async () => {
  let cursor = null, page = 0, seen = 0;
  const map = []; // {sku, id}
  do {
    const r = await gqlRetry(Q, { cursor }, 'recon');
    if (r.json.errors) throw new Error(JSON.stringify(r.json.errors).slice(0,300));
    const d = r.json.data.products;
    for (const e of d.edges) {
      const numId = e.node.id.split('/').pop();
      // main variant SKU = the DWRW-xxxxxx that does NOT end in -Sample
      let sku = null;
      for (const v of e.node.variants.edges) {
        const s = (v.node.sku||'').trim();
        if (/^DWRW-\d+$/.test(s)) { sku = s; break; }
      }
      if (sku) { map.push({ sku, id: numId }); }
      seen++;
    }
    page++;
    process.stderr.write(`\r  page ${page} | products seen ${seen} | matched DWRW ${map.length}`);
    cursor = d.pageInfo.hasNextPage ? d.pageInfo.endCursor : null;
  } while (cursor);
  process.stderr.write('\n');

  console.log(`Shopify Rebel Walls products: ${seen}, with DWRW main SKU: ${map.length}`);
  if (DRY) { console.log('DRY — no PG writes. sample:', JSON.stringify(map.slice(0,3))); return; }

  // Bulk update via a single VALUES join (chunked).
  let updated = 0;
  for (let i = 0; i < map.length; i += 500) {
    const chunk = map.slice(i, i + 500);
    const values = chunk.map(m => `('${m.sku.replace(/'/g,"''")}','${m.id}')`).join(',');
    const sql = `UPDATE rebelwalls_catalog t SET shopify_product_id=v.id, updated_at=NOW()
                 FROM (VALUES ${values}) AS v(sku,id)
                 WHERE t.dw_sku = v.sku
                   AND (t.shopify_product_id IS NULL OR t.shopify_product_id='' OR t.shopify_product_id <> v.id);`;
    const out = psql(sql + " \\echo done"); // psql prints UPDATE n? use RETURNING count instead
    updated += chunk.length;
    process.stderr.write(`\r  stamping ${Math.min(i+500,map.length)}/${map.length}`);
  }
  process.stderr.write('\n');

  const after = psql(`SELECT count(*) FILTER (WHERE shopify_product_id IS NOT NULL AND shopify_product_id<>'') ,
                             count(*) FILTER (WHERE shopify_product_id IS NULL OR shopify_product_id='')
                      FROM rebelwalls_catalog;`).trim();
  console.log('PG after reconcile (stamped \\t unstamped):', after);
})().catch(e => { console.error('\nERR', e.message); process.exit(1); });