`reference/recrawl-graham_brown.js`

196 lines
#!/usr/bin/env node
/*
 * recrawl-graham_brown.js — additive full-page all_images re-crawl for Graham & Brown.
 *
 * WHY: 3117/3125 graham_brown_catalog rows carry only a primary image_url and an
 * empty all_images, which fails Steve's "always pull all data and images"
 * completeness gate (a row is import-ready only when the crawl captured the FULL
 * product page incl. ALL images). This driver re-crawls each product page and
 * fills all_images + room_setting_url WITHOUT touching anything else (additive).
 *
 * SOURCE: grahambrown.com is a BigCommerce Stencil store (store hash s-7psb023nit).
 * Each PDP renders this product's gallery AND a "related products" carousel, both
 * on the same cdn11.bigcommerce.com host — so a naive "all CDN images" grab would
 * pollute all_images with other SKUs' thumbnails. The reliable discriminator: the
 * THIS-product gallery images embed the product's mfr_sku in the filename
 * (e.g. 120628_TILE_..., 120628_ROOMSET_...). We filter on that.
 *
 * SAFETY: additive only. We UPDATE all_images / room_setting_url ONLY on rows where
 * all_images is currently empty. No inserts, no deletes, no Shopify writes, no
 * column other than all_images / room_setting_url / updated_at touched.
 *
 * USAGE
 *   # local dry-run (no DB write needed; prints extracted images per URL):
 *   node recrawl-graham_brown.js --dry-run --limit 5
 *
 *   # prod (Kamatera) — fills empty all_images for every missing row:
 *   DATABASE_URL=postgres://... node recrawl-graham_brown.js --all-images
 *
 *   # prod, net-net-new only (the 240 rows not yet on Shopify) — fastest path to
 *   # making the genuinely new SKUs import-ready:
 *   DATABASE_URL=postgres://... node recrawl-graham_brown.js --net-new --all-images
 *
 * FLAGS
 *   --dry-run      fetch + extract + print, never write DB (DATABASE_URL optional)
 *   --net-new      restrict to rows with no shopify_product_id AND no shopify sku match
 *   --all-images   (default behaviour) capture all_images + room images; kept explicit
 *   --limit N      cap number of product pages processed
 */
'use strict';

const https = require('https');
// pg is required lazily inside main() so --dry-run works without the module installed.

const ARGS = process.argv.slice(2);
const DRY_RUN   = ARGS.includes('--dry-run');
const NET_NEW   = ARGS.includes('--net-new');
const LIMIT     = (() => { const i = ARGS.indexOf('--limit'); return i >= 0 ? parseInt(ARGS[i + 1], 10) || 0 : 0; })();

const TABLE     = 'graham_brown_catalog';
const STORE_HASH = 's-7psb023nit';
const SITE_BASE = 'https://www.grahambrown.com';
const UA = 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/120.0.0.0 Safari/537.36';
const CONCURRENCY = 3;
const sleep = (ms) => new Promise((r) => setTimeout(r, ms + Math.floor(Math.random() * 250)));

function httpGet(url, maxRedirects = 4) {
  return new Promise((resolve, reject) => {
    const req = https.get(url, { timeout: 25000, headers: { 'User-Agent': UA, Accept: 'text/html,application/xhtml+xml', 'Accept-Language': 'en-US,en;q=0.9' } }, (res) => {
      if ((res.statusCode === 301 || res.statusCode === 302) && res.headers.location && maxRedirects > 0) {
        const loc = res.headers.location.startsWith('http') ? res.headers.location : SITE_BASE + res.headers.location;
        res.resume();
        return httpGet(loc, maxRedirects - 1).then(resolve).catch(reject);
      }
      if (res.statusCode !== 200) { res.resume(); return reject(new Error('HTTP ' + res.statusCode)); }
      let d = ''; res.on('data', (c) => (d += c)); res.on('end', () => resolve(d)); res.on('error', reject);
    });
    req.on('error', reject);
    req.on('timeout', () => { req.destroy(); reject(new Error('timeout')); });
  });
}

/*
 * Extract THIS product's gallery from a Graham & Brown BigCommerce PDP.
 * Returns { all_images: string[], room_setting_url: string|null }.
 * Strategy: collect every cdn11/<STORE_HASH>/.../products/.../<...>.jpg whose
 * filename contains the product's mfr_sku, normalise each to the "original" size
 * variant, dedupe by image path, and order TILE first (the swatch shoppers expect
 * as primary) then the rest. room_setting_url = first ROOMSET/ROLLSHOT frame.
 */
function extractGrahamImages(html, mfrSku, productUrl) {
  const sku = String(mfrSku || '').trim();
  if (!sku) return { all_images: [], room_setting_url: null };
  // Paint / multi-variant "master" pages live at .../<masterNum>-master/ where the
  // URL number can differ from this row's mfr_sku. Accept either as the filename key.
  const masterNum = (String(productUrl || '').match(/\/(\d+)-master\//) || [])[1] || '';
  const keys = [sku, masterNum].filter(Boolean);
  const matchesKey = (fname) => keys.some((k) => fname.includes(k));
  const re = new RegExp(`https://cdn11\\.bigcommerce\\.com/${STORE_HASH}/images/stencil/[^"'\\\\ ]+`, 'g');
  const seen = new Set();
  const imgs = [];
  let m;
  while ((m = re.exec(html)) !== null) {
    let url = m[0]
      .replace(/\\\//g, '/')   // un-escape JSON slashes
      .replace(/\\/g, '')      // drop any stray backslashes
      .replace(/&amp;/g, '&')
      .replace(/[?].*$/, '');
    if (!/\.(jpg|jpeg|png|webp)$/i.test(url)) continue; // skip truncated / non-image hits
    // only THIS product's frames — filename carries the mfr_sku (or master number)
    const fname = url.split('/').pop();
    if (!matchesKey(fname)) continue;
    // normalise size token (100w / 340w / 1200w / 1280w / original) -> original
    url = url.replace(/\/stencil\/(?:\d+w|\d+x\d+|original)\//, '/stencil/original/');
    const pathKey = url.replace(/__\d+\.\d+\.(jpg|jpeg|png|webp)$/i, ''); // strip BC cache-bust suffix for dedupe
    if (seen.has(pathKey)) continue;
    seen.add(pathKey);
    imgs.push(url);
  }
  // order: TILE first, then ROLLSHOT/DIGITAL ROLL, then ROOMSET/FLATLAY
  const rank = (u) => {
    const f = u.toUpperCase();
    if (f.includes('_TILE')) return 0;
    if (f.includes('ROLLSHOT') || f.includes('DIGITAL')) return 1;
    if (f.includes('FLATLAY')) return 2;
    if (f.includes('ROOMSET') || f.includes('ROOM')) return 3;
    return 4;
  };
  imgs.sort((a, b) => rank(a) - rank(b));
  const room = imgs.find((u) => /ROOMSET|ROOM|ROLLSHOT/i.test(u)) || null;
  return { all_images: imgs, room_setting_url: room };
}

async function main() {
  const pool = (DATABASE_URL_PRESENT()) ? new (require('pg').Pool)({ connectionString: process.env.DATABASE_URL }) : null;
  if (!pool && !DRY_RUN) {
    console.error('FATAL: DATABASE_URL not set and not --dry-run. Refusing to run.');
    process.exit(2);
  }

  // Build the worklist. Rows missing all_images that have a product_url to fetch.
  let where = `(all_images IS NULL OR all_images = '' OR all_images = '[]') AND product_url IS NOT NULL AND product_url <> ''`;
  if (NET_NEW) {
    where += ` AND shopify_product_id IS NULL
      AND NOT EXISTS (SELECT 1 FROM shopify_products s
        WHERE s.sku = g.dw_sku OR s.sku = g.dw_sku || '-Sample' OR s.sku ILIKE g.dw_sku || '-%')`;
  }
  let rows;
  if (pool) {
    const q = `SELECT g.id, g.dw_sku, g.mfr_sku, split_part(g.product_url,'?',1) AS url
               FROM ${TABLE} g WHERE ${where} ORDER BY g.dw_sku ${LIMIT ? 'LIMIT ' + LIMIT : ''}`;
    ({ rows } = await pool.query(q));
  } else {
    // dry-run with no DB: hit a couple of known sample URLs so the extractor is exercised.
    rows = [
      // real net-net-new worklist rows: one wallpaper, one paint "master" (exercises the URL master-number fallback)
      { id: 0, dw_sku: 'DWGB-200116', mfr_sku: '120628', url: 'https://www.grahambrown.com/us/product/wallflower-night-garden-wallpaper/120628-master/' },
      { id: 0, dw_sku: 'DWGB-200555', mfr_sku: '109732', url: 'https://www.grahambrown.com/us/product/g-b-white-paint/109729-master/' },
    ].slice(0, LIMIT || 2);
  }

  console.log(`Re-crawling ${rows.length} graham_brown product page(s)${NET_NEW ? ' (net-net-new only)' : ''}${DRY_RUN ? ' [DRY RUN]' : ''}...`);
  let pages = 0, updated = 0, withImg = 0, multiImg = 0, errs = 0;
  let idx = 0;
  async function worker() {
    while (idx < rows.length) {
      const i = idx++;
      const r = rows[i];
      try {
        const html = await httpGet(r.url);
        const { all_images, room_setting_url } = extractGrahamImages(html, r.mfr_sku, r.url);
        pages++;
        if (all_images.length) withImg++;
        if (all_images.length > 1) multiImg++;
        if (DRY_RUN) {
          console.log(`  ${r.dw_sku} (${r.mfr_sku}) -> ${all_images.length} img${room_setting_url ? ' +room' : ''}`);
          all_images.forEach((u) => console.log(`      ${u}`));
        } else if (all_images.length) {
          // additive: only set all_images where it is still empty (guards concurrent fills)
          const res = await pool.query(
            `UPDATE ${TABLE}
               SET all_images = $1,
                   room_setting_url = COALESCE(NULLIF(room_setting_url,''), $2),
                   updated_at = now()
             WHERE id = $3 AND (all_images IS NULL OR all_images = '' OR all_images = '[]')`,
            [JSON.stringify(all_images), room_setting_url, r.id]
          );
          if (res.rowCount) updated++;
        }
        if (pages % 25 === 0) console.log(`  ${pages}/${rows.length} pages | ${updated} updated | ${withImg} w/img | ${multiImg} multi | ${errs} errs`);
        await sleep(350);
      } catch (e) {
        errs++;
        if (errs % 10 === 0) console.error(`  err at ${r.url}: ${e.message}`);
        await sleep(800);
      }
    }
  }
  await Promise.all(Array.from({ length: CONCURRENCY }, worker));
  console.log(`\nDONE. pages=${pages} updated=${updated} with_img=${withImg} multi_img=${multiImg} errors=${errs}`);
  if (pool) await pool.end();
}

function DATABASE_URL_PRESENT() { return !!process.env.DATABASE_URL; }

main().catch((e) => { console.error('FATAL', e); process.exit(1); });