← back to Letsbegin
rw-catalog-recrawl.js
301 lines
#!/usr/bin/env node
/**
* rw-catalog-recrawl.js — feed-first re-crawl of Rebel Walls (rebelwalls.com)
* into the `vendor_catalog` table to recover the real R##### mfr_skus for the
* cohort of newer RW mural products that have no catalog match, then write the
* 94→sku mapping for the Shopify backfill stage.
*
* SCOPE: catalog crawl into vendor_catalog ONLY. No Shopify product PUSH, no
* cost/price writes. (The RW Shopify bulk-PUSH that Steve stopped 2026-06-11 is
* a different thing — this only ingests current designs into our catalog.)
*
* rebelwalls.com runs Gimmersta "Cronos": every product page carries a
* JSON-LD <script type=application/ld+json> @type:Product with the real
* name ("Abstract Arches, Neutral"), sku (R19536), image, offers.price.
* Product slugs are root-level kebab-case. We derive each of the 94 cohort
* products' slug from its Shopify title, fetch the PDP, parse JSON-LD, and also
* harvest the page's sibling colorways (broadens the crawl). Everything
* harvested is upserted into vendor_catalog on the (vendor_code, mfr_sku)
* unique key; new mfr_skus get a fresh DWRW-###### dw_sku from the existing
* series max (never reassigns an mfr_sku that already has a dw_sku).
*
* Usage:
* node rw-catalog-recrawl.js --crawl # crawl + upsert vendor_catalog + build match table
* node rw-catalog-recrawl.js --crawl --dry # crawl, report, NO db writes
*
* Env: PG* optional (defaults user stevestudio2, db dw_unified, host /tmp).
*/
const https = require('https');
const { Client } = require('pg');
const VENDOR_CODE = 'rebel_walls';
const BASE = 'https://rebelwalls.com';
const UA = 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/120.0.0.0 Safari/537.36';
const args = process.argv.slice(2);
const DRY = args.includes('--dry');
const DO_CRAWL = args.includes('--crawl');
if (!DO_CRAWL) { console.error('Usage: node rw-catalog-recrawl.js --crawl [--dry]'); process.exit(2); }
const sleep = (ms) => new Promise((r) => setTimeout(r, ms));
function pgConnect() {
const pg = new Client({
user: process.env.PGUSER || 'stevestudio2',
database: process.env.PGDATABASE || 'dw_unified',
host: process.env.PGHOST || '/tmp',
});
return pg.connect().then(() => pg);
}
function fetchHtml(url) {
return new Promise((resolve, reject) => {
const req = https.request(url, { method: 'GET', headers: { 'User-Agent': UA, Accept: 'text/html' } }, (res) => {
if (res.statusCode === 404) { res.resume(); return resolve(null); }
if (res.statusCode >= 300 && res.statusCode < 400 && res.headers.location) {
res.resume();
const next = res.headers.location.startsWith('http') ? res.headers.location : BASE + res.headers.location;
return fetchHtml(next).then(resolve, reject);
}
if (res.statusCode !== 200) { res.resume(); return reject(new Error(`HTTP ${res.statusCode} ${url}`)); }
let c = '';
res.on('data', (d) => (c += d));
res.on('end', () => resolve(c));
});
req.on('error', reject);
req.setTimeout(25000, () => { req.destroy(); reject(new Error('timeout ' + url)); });
req.end();
});
}
function decodeEntities(s) {
return String(s || '')
.replace(/"/g, '"').replace(/�?39;/g, "'").replace(/'/g, "'")
.replace(/&/g, '&').replace(/</g, '<').replace(/>/g, '>');
}
// Derive candidate product slugs from a Shopify title.
// "Abstract Fields, Green & Red Wallcoverings | Rebel Walls" -> abstract-fields-green-red
function slugVariants(title) {
let t = decodeEntities(title)
.replace(/\s*Wallcoverings\s*\|\s*Rebel Walls\s*$/i, '')
.replace(/\s*\|\s*Rebel Walls\s*$/i, '');
const base = (repAmp) => {
let s = t.replace(/&/g, repAmp).replace(/,/g, ' ').toLowerCase();
s = s.replace(/[^a-z0-9]+/g, '-').replace(/^-+|-+$/g, '');
return s;
};
const set = new Set();
// RW drops '&' entirely (green & red -> green-red); also try 'and' just in case
for (const amp of [' ', ' and ']) {
const s = base(amp);
set.add(s);
// gray -> grey fallback (RW uses British spelling)
if (/(^|-)gray(-|$)/.test(s)) set.add(s.replace(/(^|-)gray(-|$)/g, '$1grey$2'));
}
return [...set];
}
function parseProductLd(html) {
const re = /<script type="application\/ld\+json">([\s\S]*?)<\/script>/g;
let m;
while ((m = re.exec(html))) {
let d;
try { d = JSON.parse(m[1]); } catch { continue; }
const items = Array.isArray(d) ? d : (d['@graph'] || [d]);
for (const it of items) {
if (it && it['@type'] === 'Product') {
let img = it.image;
if (Array.isArray(img)) img = img[0];
let offers = it.offers;
if (Array.isArray(offers)) offers = offers[0];
return {
name: decodeEntities(it.name || '').trim(),
sku: (it.sku || '').trim(),
image: typeof img === 'string' ? img : '',
price: offers && offers.price != null ? Number(offers.price) : null,
};
}
}
}
return null;
}
// Harvest sibling colorways from the inline state blob (broadens the crawl).
// colorways[] entries look like {"no":"R12345","name":"Blue","link":"/slug",...}
function parseColorways(html, baseName) {
const out = [];
const re = /"no"\s*:\s*"(R\d+)"\s*,\s*"name"\s*:\s*"([^"]*)"[^}]*?"link"\s*:\s*"([^"]*)"/g;
let m;
const seen = new Set();
while ((m = re.exec(html))) {
const sku = m[1];
if (seen.has(sku)) continue;
seen.add(sku);
const color = decodeEntities(m[2]).trim();
let link = decodeEntities(m[3]).trim();
if (link && !link.startsWith('http')) link = BASE + (link.startsWith('/') ? '' : '/') + link;
out.push({ sku, pattern_name: baseName, color_name: color, product_url: link });
}
return out;
}
// "Abstract Arches, Neutral" -> {pattern:"Abstract Arches", color:"Neutral"}
function splitName(name) {
const ci = name.lastIndexOf(',');
if (ci > 0) return { pattern: name.slice(0, ci).trim(), color: name.slice(ci + 1).trim() };
return { pattern: name.trim(), color: '' };
}
async function nextDwSeq(pg) {
const r = await pg.query(
`SELECT coalesce(max((regexp_replace(dw_sku,'\\D','','g'))::bigint),0) AS mx
FROM vendor_catalog WHERE vendor_code=$1 AND dw_sku ~ '^DWRW-[0-9]+$'`, [VENDOR_CODE]);
return Number(r.rows[0].mx);
}
async function main() {
const pg = await pgConnect();
try {
// 1. Load the 94 cohort
const cohort = (await pg.query(`
SELECT shopify_id, title
FROM shopify_products
WHERE created_at_shopify >= now() - interval '20 days'
AND vendor='Rebel Walls'
AND coalesce(dw_sku,'')=''
AND NOT EXISTS (
SELECT 1 FROM vendor_catalog vc
WHERE vc.vendor_code IN ('rebel_walls','rebelwalls')
AND lower(trim(vc.pattern_name)) = lower(trim(split_part(regexp_replace(title,'\\s*Wallcoverings.*$','','i'),',',1)))
)
ORDER BY title`)).rows;
console.log(`[crawl] cohort to recover: ${cohort.length}`);
// 2. Crawl each cohort product page -> real JSON-LD sku, + harvest colorways
const harvested = new Map(); // mfr_sku(UPPER) -> design row
const cohortMatch = []; // {shopify_id, title, mfr_sku, image, product_url, pattern, color}
const unresolved = [];
let i = 0;
for (const row of cohort) {
i++;
const variants = slugVariants(row.title);
let pdp = null, usedUrl = null, html = null;
for (const slug of variants) {
const url = `${BASE}/${slug}`;
try { html = await fetchHtml(url); } catch (e) { html = null; }
if (html) {
const ld = parseProductLd(html);
if (ld && ld.sku) { pdp = ld; usedUrl = url; break; }
}
await sleep(200);
}
if (!pdp) {
unresolved.push({ shopify_id: row.shopify_id, title: row.title, tried: variants });
console.log(` [${i}/${cohort.length}] UNRESOLVED "${row.title}" (tried ${variants.join(', ')})`);
await sleep(900);
continue;
}
const { pattern, color } = splitName(pdp.name);
const mfrU = pdp.sku.toUpperCase();
harvested.set(mfrU, { mfr_sku: pdp.sku, pattern_name: pattern, color_name: color, image_url: pdp.image, product_url: usedUrl, price: pdp.price });
cohortMatch.push({ shopify_id: row.shopify_id, title: row.title, mfr_sku: pdp.sku, product_url: usedUrl, pattern, color });
// sibling colorways broaden the catalog
for (const cw of parseColorways(html, pattern)) {
const u = cw.sku.toUpperCase();
if (!harvested.has(u)) harvested.set(u, { mfr_sku: cw.sku, pattern_name: cw.pattern_name, color_name: cw.color_name, image_url: '', product_url: cw.product_url, price: null });
}
console.log(` [${i}/${cohort.length}] OK "${row.title}" -> ${pdp.sku} (${pattern}, ${color})`);
await sleep(900); // ~1 req/s, gentle on the small vendor
}
console.log(`\n[crawl] cohort resolved: ${cohortMatch.length}/${cohort.length}, unresolved: ${unresolved.length}`);
console.log(`[crawl] distinct designs harvested (cohort + sibling colorways): ${harvested.size}`);
if (DRY) {
console.log('[dry] no db writes. Unresolved:');
unresolved.forEach((u) => console.log(' ' + u.title));
return;
}
// 3. Upsert harvested designs into vendor_catalog, assigning DWRW dw_sku for NEW mfr_skus only.
// Dedup case-insensitively against existing rows; never reassign an existing dw_sku.
const existing = (await pg.query(
`SELECT upper(trim(mfr_sku)) AS u, mfr_sku, dw_sku FROM vendor_catalog WHERE vendor_code=$1`, [VENDOR_CODE])).rows;
const existingByUpper = new Map(existing.map((r) => [r.u, r]));
let seq = await nextDwSeq(pg);
let inserted = 0, updated = 0;
for (const [u, d] of harvested) {
const ex = existingByUpper.get(u);
if (ex) {
// already present — refresh descriptive fields only, keep its dw_sku
const r = await pg.query(`
UPDATE vendor_catalog
SET pattern_name = COALESCE(NULLIF($3,''), pattern_name),
color_name = COALESCE(NULLIF($4,''), color_name),
product_url = COALESCE(NULLIF($5,''), product_url),
image_url = COALESCE(NULLIF($6,''), image_url),
last_scraped_at = now(),
updated_at = now()
WHERE vendor_code=$1 AND upper(trim(mfr_sku))=$2`,
[VENDOR_CODE, u, d.pattern_name, d.color_name, d.product_url, d.image_url]);
updated += r.rowCount;
} else {
seq += 1;
const dw = `DWRW-${seq}`;
await pg.query(`
INSERT INTO vendor_catalog
(vendor_code, mfr_sku, dw_sku, pattern_name, color_name, product_type,
image_url, product_url, sync_status, first_seen_at, last_scraped_at, updated_at)
VALUES ($1,$2,$3,$4,$5,'Mural',$6,$7,'new',now(),now(),now())
ON CONFLICT (vendor_code, mfr_sku) DO NOTHING`,
[VENDOR_CODE, d.mfr_sku, dw, d.pattern_name, d.color_name, d.image_url, d.product_url]);
existingByUpper.set(u, { u, mfr_sku: d.mfr_sku, dw_sku: dw });
inserted += 1;
}
}
console.log(`[vendor_catalog] inserted (new designs): ${inserted}, refreshed (already present): ${updated}`);
// 4. Build the cohort match table for the Shopify backfill stage.
// Each cohort product -> its real dw_sku (looked up by mfr_sku, freshly inserted or pre-existing).
await pg.query(`
CREATE TABLE IF NOT EXISTS rw_recrawl_match_94 (
shopify_id text PRIMARY KEY,
title text,
mfr_sku text,
dw_sku text,
product_url text,
match_kind text DEFAULT 'recrawl-jsonld',
shopify_done boolean DEFAULT false,
shopify_error text,
created_at timestamptz DEFAULT now()
)`);
await pg.query('TRUNCATE rw_recrawl_match_94');
let mapped = 0, nodw = 0;
for (const c of cohortMatch) {
const ex = existingByUpper.get(c.mfr_sku.toUpperCase());
const dw = ex && ex.dw_sku;
if (!dw) { nodw++; continue; }
await pg.query(
`INSERT INTO rw_recrawl_match_94 (shopify_id,title,mfr_sku,dw_sku,product_url)
VALUES ($1,$2,$3,$4,$5)
ON CONFLICT (shopify_id) DO UPDATE SET mfr_sku=excluded.mfr_sku, dw_sku=excluded.dw_sku, product_url=excluded.product_url`,
[c.shopify_id, c.title, ex.mfr_sku, dw, c.product_url]);
mapped++;
}
console.log(`[match] rw_recrawl_match_94 rows: ${mapped} (cohort products with a resolved dw_sku; ${nodw} had no dw_sku)`);
if (unresolved.length) {
console.log('\n[UNRESOLVED — still absent from rebelwalls.com]:');
unresolved.forEach((u) => console.log(' ' + u.title));
}
} finally {
await pg.end();
}
}
main().catch((e) => { console.error('FATAL', e.message, '\n', e.stack); process.exit(1); });