`scripts/crawl_pdps.js`

107 lines
#!/usr/bin/env node
/**
 * Crawl Sister Parish PDPs to extract DW-spec metafields the /products.json
 * endpoint doesn't expose: item width, roll length (sold by), repeat (H+V),
 * country of origin, paint pairings, plus anything else in the two <dl> blocks.
 *
 * Writes results to dw_unified.sisterparish_catalog.specs JSONB.
 * Rate-limited: ~1 req/sec to be polite.
 */
require('dotenv').config({ path: require('path').join(__dirname,'..','.env') });
const fs = require('fs');
const path = require('path');
const { execSync } = require('child_process');

const DELAY_MS = 1100;
const OUT = path.join(__dirname,'..','output','pdp_specs.json');

async function sleep(ms){return new Promise(r=>setTimeout(r,ms));}

function parseSpecs(html) {
  const specs = {};
  // Both <dl> blocks
  const dls = [...html.matchAll(/<dl[^>]*>([\s\S]*?)<\/dl>/g)];
  for (const dl of dls) {
    const pairs = [...dl[1].matchAll(/<dt[^>]*>([\s\S]*?)<\/dt>\s*<dd[^>]*>([\s\S]*?)<\/dd>/g)];
    for (const p of pairs) {
      const k = p[1].replace(/<[^>]+>/g,' ').replace(/[:\s]+$/,'').replace(/\s+/g,' ').trim();
      const v = p[2].replace(/<[^>]+>/g,' ').replace(/\s+/g,' ').trim();
      if (k && v) specs[k] = v;
    }
  }
  // Pattern title's body sometimes mentions a Sister Parish-told story — capture description block too
  const descMatch = html.match(/<div[^>]*product__description[^>]*>([\s\S]*?)<\/div>\s*<\/div>/);
  if (descMatch) {
    const txt = descMatch[1].replace(/<[^>]+>/g,' ').replace(/\s+/g,' ').trim();
    if (txt && txt.length > 30) specs.__description_long = txt.slice(0, 2000);
  }
  return specs;
}

// Normalize loose keys to canonical DW spec keys for downstream metafields
function canonicalize(raw) {
  const norm = {};
  for (const [k, v] of Object.entries(raw)) {
    const lk = k.toLowerCase();
    if (lk.includes('item width') || lk === 'width') norm.width = v;
    else if (lk.includes('sold by')) norm.sold_by = v;
    else if (lk.includes('horizontal repeat')) norm.horizontal_repeat = v;
    else if (lk.includes('vertical repeat')) norm.vertical_repeat = v;
    else if (lk.includes('repeat') && !norm.repeat) norm.repeat = v;
    else if (lk.includes('country of origin') || lk === 'origin') norm.country_of_origin = v;
    else if (lk.includes('match')) norm.match = v;
    else if (lk.includes('fire')) norm.fire_rating = v;
    else if (lk.includes('paste') || lk.includes('hang')) norm.paste = v;
    else if (lk.includes('paint pair')) norm.paint_pairings = v;
    else if (lk === 'sku') {} // skip; already have
    else norm[`extra__${k.replace(/[^a-z0-9]+/gi,'_').toLowerCase()}`] = v;
  }
  return norm;
}

(async () => {
  const data = JSON.parse(fs.readFileSync(path.join(__dirname,'..','output','normalized.json'),'utf8'));
  const results = [];
  for (let i = 0; i < data.length; i++) {
    const p = data[i];
    const url = p.url;
    const stamp = `[${String(i+1).padStart(3,'0')}/${data.length}]`;
    try {
      const res = await fetch(url, { headers: { 'User-Agent': 'Mozilla/5.0 (DW-onboarding/1.0)' } });
      if (!res.ok) throw new Error(`HTTP ${res.status}`);
      const html = await res.text();
      const raw = parseSpecs(html);
      const canon = canonicalize(raw);
      const filled = Object.keys(canon).filter(k => !k.startsWith('extra__') && !k.startsWith('__')).length;
      results.push({ sp_product_id: p.sp_product_id, handle: p.handle, raw, canon });
      console.log(`${stamp} ✓ ${p.title.slice(0,50).padEnd(50)} keys=${Object.keys(raw).length} canon=${filled}`);
    } catch (e) {
      results.push({ sp_product_id: p.sp_product_id, handle: p.handle, error: e.message });
      console.log(`${stamp} ✗ ${p.title}: ${e.message}`);
    }
    await sleep(DELAY_MS);
  }
  fs.writeFileSync(OUT, JSON.stringify(results, null, 2));
  console.log(`\nWrote ${results.length} PDP specs to ${OUT}`);

  // Push specs into PG
  const sql = `
ALTER TABLE sisterparish_catalog ADD COLUMN IF NOT EXISTS specs JSONB;
ALTER TABLE sisterparish_catalog ADD COLUMN IF NOT EXISTS specs_raw JSONB;
ALTER TABLE sisterparish_catalog ADD COLUMN IF NOT EXISTS specs_synced_at TIMESTAMPTZ;
`;
  execSync(`psql dw_unified -c ${JSON.stringify(sql)}`, { stdio: 'inherit' });
  for (const r of results) {
    if (r.error) continue;
    const canonStr = JSON.stringify(r.canon).replace(/'/g, "''");
    const rawStr = JSON.stringify(r.raw).replace(/'/g, "''");
    const stmt = `UPDATE sisterparish_catalog SET specs='${canonStr}'::jsonb, specs_raw='${rawStr}'::jsonb, specs_synced_at=NOW() WHERE sp_product_id=${r.sp_product_id};`;
    fs.appendFileSync('/tmp/sp_specs_update.sql', stmt + '\n');
  }
  execSync(`psql dw_unified -f /tmp/sp_specs_update.sql`, { stdio: 'inherit' });
  fs.unlinkSync('/tmp/sp_specs_update.sql');

  // Spot-check
  execSync(`psql dw_unified -c "SELECT title, specs->>'width' AS width, specs->>'sold_by' AS sold_by, specs->>'horizontal_repeat' AS h_repeat, specs->>'vertical_repeat' AS v_repeat, specs->>'country_of_origin' AS coo FROM sisterparish_catalog WHERE specs IS NOT NULL ORDER BY random() LIMIT 8;"`, { stdio: 'inherit' });
})();