`recrawl-versa_ds.js`

243 lines
#!/usr/bin/env node
// ============================================================================
// Versa Designed Surfaces ADDITIVE all_images re-crawl — full-page pass
// ============================================================================
// WHY: the bulk versa_ds catalog (1,745 rows in versa_catalog / vendor_catalog
// vendor_code='versa_ds') was listing-scraped and captured only a single
// /Thumbnails/ hero per SKU — NO gallery, NO per-colorway swatches, NO specs.
// 1,619 rows have empty all_images. Many color_name values are a HEX string
// (e.g. "#d1cfcb") instead of the real colour, which is actually encoded in the
// thumbnail filename (…-038-Earl_Grey.jpg → "Earl Grey"). Steve's standing rule
// "always pull all data and images" requires every product to carry its FULL
// image set + real colour name + specs before it is import-ready.
//
// PRIVATE LABEL: versa_ds is the DW private label "DW Commercial Surfaces"
// (NOT Hollywood). The source brand "Versa" / "Versa Designed Surfaces" must be
// scrubbed from every customer-facing field at PUSH time via the PL scrub
// (hollywood-import/scrub-momentum.js leak-check passes 0 on versa, but its
// vendor default of "Hollywood Wallcoverings" is WRONG for versa — see the
// pending-approval note before any Shopify push).
//
// SAFETY / ADDITIVE-ONLY:
//   * NEVER inserts new rows. Only UPDATEs all_images / specs / color_name on
//     rows that already exist (matched by mfr_sku carried out of the DB).
//   * Only touches rows whose all_images is empty.
//   * Does NOT push to Shopify, does NOT publish, does NOT change SKUs.
//
// TWO image sources, additive:
//   1. PAGE CRAWL (Playwright) of the product detail page when product_url is
//      present — yields the full gallery + sibling colorways + specs. Versa is
//      a JS-rendered TYPO3 site, hence Playwright (matches the proven
//      DW-Programming/versa-evo-pvcfree-scraper.js approach).
//   2. URL-DERIVED FALLBACK (zero-risk, no crawl) from the existing thumbnail
//      image_url: the full-size image (strip /Thumbnails/) + the REAL colour
//      name parsed from the filename. Applied to EVERY eligible row so even
//      product_url-less rows get a real colour name + full-res hero.
//
// MODES:
//   --canary N    process only the first N rows (default 5) — run FIRST to
//                 confirm the gallery selector matches live HTML.
//   --all         process every eligible row (full ~1,619-row pass).
//   --net-new     restrict to rows whose mfr_sku is NOT already in
//                 shopify_products (skip the 827 already-live cohort; ~792 rows).
//   --no-crawl    URL-derive only (color_name + full-size hero), skip Playwright.
//
// Mirrors the harness of recrawl-coordonne.js / recrawl-osborne.js.
// Deploy target: Kamatera /root/DW-Agents/vendor-scrapers/.  Authored from the
// local mirror; FIRST run on Kamatera MUST be --canary to validate selectors.
// ============================================================================
require('dotenv').config({ path: '/root/DW-Agents/dw-price-stock/.env' });
const { pool } = require('/root/DW-Agents/vendor-scrapers/scraper-utils');

const TABLE = 'versa_catalog';
const SITE_BASE = 'https://www.versadesignedsurfaces.com';

const argv = process.argv.slice(2);
const FULL = argv.includes('--all');
const NET_NEW_ONLY = argv.includes('--net-new');
const NO_CRAWL = argv.includes('--no-crawl');
const canaryIdx = argv.indexOf('--canary');
const CANARY_N = canaryIdx >= 0 ? (parseInt(argv[canaryIdx + 1], 10) || 5) : (FULL ? 0 : 5);

const sleep = (ms) => new Promise(r => setTimeout(r, ms + Math.floor(Math.random() * 250)));

// Junk filters — never store logos, icons, sprites, payment badges as images.
const JUNK = /(logo|icon|favicon|placeholder|sprite|payment|visa|mastercard|paypal|flag|avatar|loader|spinner)/i;

// Versa product imagery lives under /fileadmin/products/. Accept those + TYPO3
// processed images (csm_ / _processed_). Reject everything else.
function isProductImg(u) {
  if (!u) return false;
  if (!/\.(jpe?g|png|webp)(\?.*)?$/i.test(u)) return false;
  if (JUNK.test(u)) return false;
  return /\/fileadmin\/|csm_|_processed_/i.test(u);
}
function absUrl(u) {
  if (!u) return '';
  u = u.trim().replace(/&amp;/g, '&');
  if (/^data:/i.test(u)) return '';            // inline base64 placeholder, not a real image
  if (u.startsWith('//')) return 'https:' + u;
  if (/^https?:\/\//i.test(u)) return u;
  return SITE_BASE + (u.startsWith('/') ? '' : '/') + u;
}

// Turn a /Thumbnails/ thumbnail URL into its full-size sibling (TYPO3 keeps the
// full image in the parent product dir, same filename without /Thumbnails/).
function fullSizeFromThumb(thumbUrl) {
  if (!thumbUrl) return '';
  return thumbUrl.replace(/\/Thumbnails\//i, '/');
}

// The REAL colour name is the trailing token of the thumbnail filename:
//   Brio_A119-038-Earl_Grey.jpg  ->  "Earl Grey"
//   Bellagio_A122-505-Barley.jpg ->  "Barley"
// Parse it off the filename when color_name is missing or a hex string.
function colorFromImageUrl(imageUrl, mfrSku) {
  if (!imageUrl) return '';
  if (/^data:/i.test(imageUrl)) return '';   // base64 placeholder — no parseable filename (AVF/Evo rows)
  const file = imageUrl.split('/').pop().replace(/\.(jpe?g|png|webp)$/i, '');
  // Strip the leading <Pattern>_<family>-<num> and keep the trailing colour.
  // The SKU (e.g. A119-038) appears in the filename; split on it.
  const skuTail = (mfrSku || '').split('-').pop(); // "038"
  let tail = '';
  if (skuTail && file.includes('-' + skuTail + '-')) {
    tail = file.split('-' + skuTail + '-').pop();
  } else {
    // fallback: take everything after the last numeric-only segment
    const parts = file.split('-');
    const lastNumIdx = parts.map(p => /^\d+$/.test(p)).lastIndexOf(true);
    tail = lastNumIdx >= 0 ? parts.slice(lastNumIdx + 1).join('-') : parts.slice(-1)[0];
  }
  const color = tail.replace(/_/g, ' ').replace(/\s+/g, ' ').trim();
  // Sanity: a real colour name has letters, no '=' (base64), 3..40 chars.
  if (!/[a-z]/i.test(color) || /=/.test(color) || color.length < 3 || color.length > 40) return '';
  return color;
}

// Crawl one product detail page with Playwright → { images[], specs{} }.
async function crawlPage(page, url) {
  await page.goto(url, { waitUntil: 'domcontentloaded', timeout: 30000 });
  await page.waitForTimeout(2500); // TYPO3 JS render
  return await page.evaluate(() => {
    const out = { images: [], specs: {} };
    document.querySelectorAll('img').forEach(img => {
      const s = img.getAttribute('src') || img.getAttribute('data-src') || '';
      if (s) out.images.push(s);
    });
    document.querySelectorAll('a[href]').forEach(a => {
      const h = a.getAttribute('href') || '';
      if (/\.(jpe?g|png|webp)$/i.test(h)) out.images.push(h);
    });
    // specs: any <dl>/<table>/label:value rows on the product page
    document.querySelectorAll('table tr, dl').forEach(() => {});
    const rows = document.querySelectorAll('table tr');
    rows.forEach(tr => {
      const cells = tr.querySelectorAll('th,td');
      if (cells.length === 2) {
        const k = cells[0].textContent.trim().replace(/\s+/g, ' ');
        const v = cells[1].textContent.trim().replace(/\s+/g, ' ');
        if (k && v && k.length < 60) out.specs[k] = v;
      }
    });
    return out;
  });
}

(async () => {
  // Eligible rows: missing all_images.
  let sql = `
    SELECT mfr_sku,
           split_part(product_url,'?',1) AS url,
           image_url,
           color_name
    FROM ${TABLE}
    WHERE (all_images IS NULL OR all_images = '' OR all_images = '[]' OR all_images = '""')
  `;
  if (NET_NEW_ONLY) {
    sql += ` AND NOT EXISTS (SELECT 1 FROM shopify_products s WHERE upper(s.mfr_sku)=upper(${TABLE}.mfr_sku))`;
  }
  sql += ` ORDER BY mfr_sku`;
  if (CANARY_N > 0) sql += ` LIMIT ${CANARY_N}`;

  // Ensure target columns exist (idempotent — no-op if already present).
  await pool.query(`ALTER TABLE ${TABLE} ADD COLUMN IF NOT EXISTS all_images TEXT`);
  await pool.query(`ALTER TABLE ${TABLE} ADD COLUMN IF NOT EXISTS specs JSONB`);

  const { rows } = await pool.query(sql);
  console.log(`Versa additive all_images re-crawl — ${rows.length} rows${CANARY_N > 0 ? ` (CANARY/limit ${CANARY_N})` : ' (FULL)'}${NET_NEW_ONLY ? ' [net-new only]' : ''}${NO_CRAWL ? ' [url-derive only]' : ''}`);

  // Lazy Playwright launch (only if we're going to crawl).
  let browser = null, page = null;
  if (!NO_CRAWL) {
    const { chromium } = require('playwright');
    browser = await chromium.launch({ headless: true });
    page = await browser.newPage();
    await page.setExtraHTTPHeaders({ 'Accept-Language': 'en-US,en;q=0.9' });
  }

  let n = 0, updated = 0, withImgs = 0, withSpecs = 0, colorFixed = 0, crawlMiss = 0, errs = 0;
  for (const r of rows) {
    n++;
    try {
      // (2) URL-derived baseline — zero-risk, always available.
      const heroFull = fullSizeFromThumb(absUrl(r.image_url));
      const derivedColor = colorFromImageUrl(r.image_url, r.mfr_sku);
      const isHexColor = r.color_name && /^#?[0-9a-f]{3,8}$/i.test(String(r.color_name).trim());
      const newColor = (derivedColor && (!r.color_name || isHexColor)) ? derivedColor : null;

      let images = [];
      if (heroFull) images.push(heroFull);
      if (r.image_url) images.push(absUrl(r.image_url)); // keep thumbnail too
      let specs = {};

      // (1) Page crawl when we have a real product_url.
      if (!NO_CRAWL && r.url && /versadesignedsurfaces\.com/i.test(r.url)) {
        try {
          const pageData = await crawlPage(page, r.url);
          for (const raw of pageData.images) {
            const u = absUrl(raw);
            if (isProductImg(u) && !images.includes(u)) images.push(u);
          }
          specs = pageData.specs || {};
          await sleep(600);
        } catch (e) {
          crawlMiss++;
          if (CANARY_N > 0) console.error(`  crawl-miss ${r.mfr_sku}: ${e.message}`);
        }
      }

      // Dedupe + keep only real product images.
      images = [...new Set(images.filter(isProductImg))];
      if (!images.length) { if (CANARY_N > 0) console.log(`  ${r.mfr_sku}: no images`); continue; }

      await pool.query(
        `UPDATE ${TABLE}
            SET all_images = $1,
                image_url  = COALESCE(NULLIF(image_url,''), $2),
                color_name = COALESCE($3, color_name),
                specs      = COALESCE(specs, '{}'::jsonb) || $4::jsonb
          WHERE mfr_sku = $5`,
        [JSON.stringify(images), images[0], newColor, JSON.stringify(specs), r.mfr_sku]
      );
      updated++; withImgs++;
      if (Object.keys(specs).length) withSpecs++;
      if (newColor) colorFixed++;

      if (CANARY_N > 0) {
        console.log(`  ${r.mfr_sku}: ${images.length} imgs${newColor ? `, color "${newColor}"` : ''}, ${Object.keys(specs).length} specs`);
        console.log(`    ${images.slice(0, 3).join('\n    ')}${images.length > 3 ? '\n    …' : ''}`);
      } else if (n % 50 === 0) {
        console.log(`  ${n}/${rows.length} | updated ${updated} | w/imgs ${withImgs} | w/specs ${withSpecs} | colorFixed ${colorFixed} | crawlMiss ${crawlMiss} | errs ${errs}`);
      }
    } catch (e) {
      errs++;
      if (errs % 10 === 0 || CANARY_N > 0) console.error(`  err ${r.mfr_sku}: ${e.message}`);
      await sleep(500);
    }
  }

  if (browser) await browser.close();
  console.log(`\nDONE. rows=${n} updated=${updated} with_imgs=${withImgs} with_specs=${withSpecs} color_fixed=${colorFixed} crawl_miss=${crawlMiss} errors=${errs}`);
  await pool.end();
})().catch(e => { console.error('FATAL', e); pool.end().catch(() => {}); process.exit(1); });