`scripts/dw-stale-urls-push.js`

192 lines
// Push image swaps to Shopify for the verified-200 set.
// Strategy per row:
//   1. GET product images.json
//   2. If product 404 → mark skipped (deleted-on-shopify)
//   3. If primary image src already == new_url → MATCH, just sync local mirror
//   4. Else POST new image with src=new_url, then PUT product { image: { id: new_image_id } } to set as primary
//      Then DELETE any image whose src equals the original old_url (if found).
//
// Batch=50, pause 5s between. Abort on 3 consecutive failures (not counting 404-skipped or MATCH).
// Rate: 2 calls/sec safe; we add 500ms between Shopify calls.
// Retries on 429: exponential backoff (2s, 4s, 8s) up to 3x.

const { Client } = require('pg');
const fs = require('fs');
const path = require('path');

const SHOP = 'designer-laboratory-sandbox.myshopify.com';
const API = '2026-01';
const TOKEN = (() => {
  const envPath = '/Users/macstudio3/Projects/secrets-manager/.env';
  const txt = fs.readFileSync(envPath, 'utf8');
  const m = txt.match(/^SHOPIFY_ADMIN_TOKEN=(.+)$/m);
  if (!m) throw new Error('SHOPIFY_ADMIN_TOKEN missing');
  return m[1].trim();
})();

const BATCH = 50;
const BATCH_PAUSE_MS = 5000;
const PER_CALL_DELAY_MS = 550; // ~1.8/sec
const ABORT_AFTER_CONSEC = 3;

const DRY_RUN = process.argv.includes('--dry-run');
const LIMIT = (() => { const i = process.argv.indexOf('--limit'); return i > -1 ? parseInt(process.argv[i+1], 10) : null; })();

const sleep = (ms) => new Promise(r => setTimeout(r, ms));

async function shopify(method, urlPath, body) {
  for (let attempt = 0; attempt < 4; attempt++) {
    const res = await fetch(`https://${SHOP}/admin/api/${API}${urlPath}`, {
      method,
      headers: { 'X-Shopify-Access-Token': TOKEN, 'Content-Type': 'application/json' },
      body: body ? JSON.stringify(body) : undefined,
    });
    if (res.status === 429) {
      const wait = (2 ** attempt) * 2000;
      console.log(`  429 — backing off ${wait}ms`);
      await sleep(wait);
      continue;
    }
    let json = null;
    try { json = await res.json(); } catch (e) {}
    return { status: res.status, json };
  }
  return { status: 429, json: null };
}

function gidToNumeric(gid) {
  const m = String(gid).match(/(\d+)$/);
  return m ? m[1] : null;
}

async function processOne(row, results) {
  const numericId = gidToNumeric(row.shopify_id);
  if (!numericId) {
    results.failed.push({ id: row.id, reason: 'no-numeric-id', product_id: row.product_id });
    return 'fail';
  }
  // 1. GET images
  const got = await shopify('GET', `/products/${numericId}/images.json`);
  await sleep(PER_CALL_DELAY_MS);
  if (got.status === 404) {
    results.skipped_404.push({ id: row.id, product_id: row.product_id, handle: row.handle });
    return 'skip';
  }
  if (got.status !== 200) {
    results.failed.push({ id: row.id, product_id: row.product_id, reason: `GET-images-${got.status}` });
    return 'fail';
  }
  const images = got.json?.images || [];
  images.sort((a, b) => a.position - b.position);
  const primary = images[0];

  // 2. MATCH check
  if (primary && primary.src === row.new_url) {
    results.matched.push({ id: row.id, product_id: row.product_id });
    return 'match';
  }

  if (DRY_RUN) {
    results.would_push.push({ id: row.id, product_id: row.product_id, old: row.old_url, new: row.new_url });
    return 'dry';
  }

  // 3. POST new image
  const posted = await shopify('POST', `/products/${numericId}/images.json`, { image: { src: row.new_url, position: 1 } });
  await sleep(PER_CALL_DELAY_MS);
  if (posted.status !== 200 && posted.status !== 201) {
    results.failed.push({ id: row.id, product_id: row.product_id, reason: `POST-image-${posted.status}`, detail: JSON.stringify(posted.json).slice(0, 200) });
    return 'fail';
  }
  const newImageId = posted.json?.image?.id;
  if (!newImageId) {
    results.failed.push({ id: row.id, product_id: row.product_id, reason: 'no-new-image-id' });
    return 'fail';
  }

  // 4. Set as primary via product.image = { id: newImageId }
  const putRes = await shopify('PUT', `/products/${numericId}.json`, { product: { id: parseInt(numericId, 10), image: { id: newImageId } } });
  await sleep(PER_CALL_DELAY_MS);
  if (putRes.status !== 200) {
    results.failed.push({ id: row.id, product_id: row.product_id, reason: `PUT-primary-${putRes.status}`, detail: JSON.stringify(putRes.json).slice(0, 200) });
    return 'fail';
  }

  // 5. Optional: delete any image whose src equals old_url
  for (const img of images) {
    if (img.src === row.old_url && img.id !== newImageId) {
      const del = await shopify('DELETE', `/products/${numericId}/images/${img.id}.json`);
      await sleep(PER_CALL_DELAY_MS);
      if (del.status !== 200) {
        results.delete_warnings.push({ id: row.id, product_id: row.product_id, image_id: img.id, status: del.status });
      }
    }
  }

  results.succeeded.push({ id: row.id, product_id: row.product_id, new_image_id: newImageId, old: row.old_url, new: row.new_url });
  return 'ok';
}

(async () => {
  const client = new Client();
  await client.connect();
  const limClause = LIMIT ? `LIMIT ${LIMIT}` : '';
  const { rows } = await client.query(`
    SELECT p.id, p.product_id, p.handle, p.old_url, p.new_url, p.source_table, sp.shopify_id
    FROM image_url_swap_proposals_2026_05_19 p
    JOIN shopify_products sp ON sp.id = p.product_id
    WHERE p.new_url IS NOT NULL AND p.head_check_status = 200
    ORDER BY p.id
    ${limClause}
  `);
  console.log(`Push set: ${rows.length}${DRY_RUN ? ' (DRY RUN)' : ''}`);

  const results = {
    succeeded: [], matched: [], skipped_404: [],
    failed: [], delete_warnings: [], would_push: [],
    started_at: new Date().toISOString(),
  };
  let consecFails = 0;
  let aborted = false;

  for (let i = 0; i < rows.length; i += BATCH) {
    const batch = rows.slice(i, i + BATCH);
    console.log(`\n=== Batch ${Math.floor(i / BATCH) + 1} (rows ${i + 1}-${Math.min(i + BATCH, rows.length)}) ===`);
    for (const row of batch) {
      const outcome = await processOne(row, results);
      if (outcome === 'fail') {
        consecFails++;
        if (consecFails >= ABORT_AFTER_CONSEC) {
          console.error(`!! ${ABORT_AFTER_CONSEC} consecutive failures — aborting`);
          aborted = true;
          break;
        }
      } else {
        consecFails = 0;
      }
      // Sync local mirror for ok/match/dry
      if ((outcome === 'ok' || outcome === 'match') && !DRY_RUN) {
        try {
          await client.query('UPDATE shopify_products SET image_url=$1 WHERE id=$2', [row.new_url, row.product_id]);
        } catch (e) {
          console.error(`  local mirror update failed for product_id=${row.product_id}: ${e.message}`);
        }
      }
    }
    if (aborted) break;
    console.log(`  Progress: ok=${results.succeeded.length} match=${results.matched.length} 404=${results.skipped_404.length} fail=${results.failed.length}`);
    if (i + BATCH < rows.length) {
      console.log(`  pausing ${BATCH_PAUSE_MS}ms...`);
      await sleep(BATCH_PAUSE_MS);
    }
  }

  results.ended_at = new Date().toISOString();
  results.aborted = aborted;
  const outPath = '/tmp/dw-stale-urls/push-results.json';
  fs.writeFileSync(outPath, JSON.stringify(results, null, 2));
  console.log(`\nFINAL: ok=${results.succeeded.length} match=${results.matched.length} skipped404=${results.skipped_404.length} fail=${results.failed.length} aborted=${aborted}`);
  console.log(`Results → ${outPath}`);
  await client.end();
})().catch(e => { console.error(e); process.exit(1); });