`scrape_dg_specs.js`

243 lines
#!/usr/bin/env node
/**
 * Designers Guild Phase 2 Enrichment Scraper
 * Scrapes individual product pages for full specs:
 * fire_rating, repeat_v, repeat_h, match_type, roll length, weight, care, application, collection
 * Saves to PostgreSQL designers_guild_catalog
 */

const https = require('https');
const { Pool } = require('pg');

const pool = new Pool({ connectionString: (process.env.DATABASE_URL || 'postgresql://dw_admin@127.0.0.1:5432/dw_unified') });

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

function fetchPage(url) {
  return new Promise((resolve, reject) => {
    const parsed = new URL(url);
    const req = https.get({
      hostname: parsed.hostname,
      path: parsed.pathname + parsed.search,
      headers: { 'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36', 'Accept': 'text/html' }
    }, res => {
      if (res.statusCode >= 300 && res.statusCode < 400 && res.headers.location) {
        return fetchPage(res.headers.location).then(resolve).catch(reject);
      }
      let data = '';
      res.on('data', c => data += c);
      res.on('end', () => resolve({ status: res.statusCode, body: data }));
    });
    req.on('error', reject);
    req.setTimeout(20000, () => { req.destroy(); reject(new Error('timeout')); });
  });
}

function extractSpec(html, label) {
  // Look for patterns like: <dt>Width</dt><dd>27.5 in</dd>
  // or: "Width":"27.5 in"
  // or: label followed by value in various HTML patterns

  const patterns = [
    new RegExp(`"${label}"\\s*:\\s*"([^"]*)"`, 'i'),
    new RegExp(`>${label}<[^>]*>[^<]*<[^>]*>([^<]+)<`, 'i'),
    new RegExp(`${label}[:\\s]*</[^>]+>\\s*<[^>]+>([^<]+)<`, 'i'),
    new RegExp(`${label}[:\\s]+([^<\\n]+)`, 'i'),
  ];

  for (const pat of patterns) {
    const m = html.match(pat);
    if (m && m[1]?.trim()) return m[1].trim();
  }
  return null;
}

function extractAllSpecs(html) {
  const specs = {};

  // Width
  specs.width = extractSpec(html, 'Width') || extractSpec(html, 'width');

  // Repeat
  specs.repeat_v = extractSpec(html, 'Vertical Pattern Repeat') || extractSpec(html, 'Vertical Repeat') || extractSpec(html, 'verticalRepeat');
  specs.repeat_h = extractSpec(html, 'Horizontal Pattern Repeat') || extractSpec(html, 'Horizontal Repeat') || extractSpec(html, 'horizontalRepeat');

  // Match type
  specs.match_type = extractSpec(html, 'Pattern Match') || extractSpec(html, 'Match') || extractSpec(html, 'patternMatch');

  // Fire rating
  specs.fire_rating = extractSpec(html, 'Fire') || extractSpec(html, 'Flame') || extractSpec(html, 'fireRating');
  // Also check for EN13501 pattern
  const fireMatch = html.match(/EN13501[^<"]{0,50}Class\s+([A-E]-s\d-d\d)/i) || html.match(/(EN13501[^<"]{0,30})/i);
  if (fireMatch && !specs.fire_rating) specs.fire_rating = fireMatch[1]?.trim();

  // Collection
  specs.collection = extractSpec(html, 'Collection') || extractSpec(html, 'collection');

  // Roll length
  specs.roll_length = extractSpec(html, 'Roll Length') || extractSpec(html, 'rollLength');

  // Weight
  specs.weight = extractSpec(html, 'Weight') || extractSpec(html, 'weight');

  // Care/cleaning
  specs.care = extractSpec(html, 'Care') || extractSpec(html, 'Cleaning') || extractSpec(html, 'careInstructions');

  // Application/installation
  specs.application = extractSpec(html, 'Installation') || extractSpec(html, 'Hanging') || extractSpec(html, 'Paste');

  // Coverage
  specs.coverage = extractSpec(html, 'Coverage') || extractSpec(html, 'coverage');

  // Try JSON-LD structured data
  const jsonLdMatch = html.match(/<script type="application\/ld\+json">([\s\S]*?)<\/script>/g);
  if (jsonLdMatch) {
    for (const block of jsonLdMatch) {
      try {
        const json = JSON.parse(block.replace(/<script[^>]*>/, '').replace(/<\/script>/, ''));
        if (json.additionalProperty) {
          for (const prop of json.additionalProperty) {
            const name = (prop.name || '').toLowerCase();
            const val = prop.value;
            if (name.includes('vertical') && name.includes('repeat') && !specs.repeat_v) specs.repeat_v = val;
            if (name.includes('horizontal') && name.includes('repeat') && !specs.repeat_h) specs.repeat_h = val;
            if (name.includes('match') && !specs.match_type) specs.match_type = val;
            if (name.includes('fire') && !specs.fire_rating) specs.fire_rating = val;
            if (name.includes('weight') && !specs.weight) specs.weight = val;
            if (name.includes('collection') && !specs.collection) specs.collection = val;
            if (name.includes('length') && !specs.roll_length) specs.roll_length = val;
            if (name.includes('care') && !specs.care) specs.care = val;
            if (name.includes('paste') || name.includes('install') && !specs.application) specs.application = val;
          }
        }
      } catch {}
    }
  }

  // Room setting images - look for lifestyle/room images
  const roomImages = [];
  const imgMatches = html.matchAll(/src=["']([^"']*(?:room|lifestyle|interior|setting|scene|styled)[^"']*\.(?:jpg|jpeg|png|webp))["']/gi);
  for (const m of imgMatches) {
    roomImages.push(m[1]);
  }
  // Also check for large images in the gallery
  const galleryImgs = html.matchAll(/image-nonwebp\/\d+\/(\d+)/g);
  const imageIds = new Set();
  for (const m of galleryImgs) {
    imageIds.add(m[1]);
  }
  specs.image_ids = [...imageIds];
  specs.room_images = roomImages;

  return specs;
}

function parseInches(val) {
  if (!val) return null;
  // "27.5 in" → 27.5
  // "118 in" → 118
  // "55 cm" → convert
  const inMatch = val.match(/([\d.]+)\s*(?:in|inch)/i);
  if (inMatch) return parseFloat(inMatch[1]);
  const cmMatch = val.match(/([\d.]+)\s*cm/i);
  if (cmMatch) return parseFloat(cmMatch[1]) / 2.54;
  const numMatch = val.match(/([\d.]+)/);
  if (numMatch) return parseFloat(numMatch[1]);
  return null;
}

async function main() {
  console.log('=== Designers Guild Phase 2 Enrichment Scraper ===\n');

  // Ensure columns exist
  const newCols = ['care_instructions', 'weight', 'roll_length', 'coverage', 'room_setting_urls'];
  for (const col of newCols) {
    await pool.query(`ALTER TABLE designers_guild_catalog ADD COLUMN IF NOT EXISTS ${col} TEXT`).catch(() => {});
  }

  // Get all products that need enrichment (no fire_rating = not yet scraped at product level)
  const { rows } = await pool.query(`
    SELECT id, mfr_sku, product_url
    FROM designers_guild_catalog
    WHERE product_url IS NOT NULL AND product_url != ''
      AND (fire_rating IS NULL OR fire_rating = '')
    ORDER BY id
  `);

  console.log(`Found ${rows.length} products to scrape\n`);

  let success = 0, failed = 0, noData = 0;

  for (let i = 0; i < rows.length; i++) {
    const row = rows[i];

    try {
      const { status, body } = await fetchPage(row.product_url);

      if (status !== 200) {
        failed++;
        if (i < 5) console.log(`  FAIL [${row.mfr_sku}]: HTTP ${status}`);
        continue;
      }

      const specs = extractAllSpecs(body);

      // Parse numeric values
      const repeatV = parseInches(specs.repeat_v);
      const repeatH = parseInches(specs.repeat_h);

      // Update PostgreSQL
      const updates = [];
      const values = [];
      let paramIdx = 1;

      const addUpdate = (col, val) => {
        if (val && val.toString().trim()) {
          updates.push(`${col} = $${paramIdx}`);
          values.push(val.toString().trim());
          paramIdx++;
        }
      };

      addUpdate('fire_rating', specs.fire_rating || 'N/A'); // Always set to mark as scraped
      addUpdate('match_type', specs.match_type);
      addUpdate('collection', specs.collection);
      addUpdate('care_instructions', specs.care);
      addUpdate('weight', specs.weight);
      addUpdate('roll_length', specs.roll_length);
      addUpdate('coverage', specs.coverage);
      addUpdate('application', specs.application);
      if (repeatV) { addUpdate('repeat_v', repeatV); }
      if (repeatH) { addUpdate('repeat_h', repeatH); }
      if (specs.room_images?.length > 0) {
        addUpdate('room_setting_urls', JSON.stringify(specs.room_images));
      }

      if (updates.length > 0) {
        values.push(row.id);
        await pool.query(`UPDATE designers_guild_catalog SET ${updates.join(', ')} WHERE id = $${paramIdx}`, values);
        success++;
      } else {
        noData++;
      }

    } catch (e) {
      failed++;
      if (i < 5) console.log(`  ERROR [${row.mfr_sku}]: ${e.message}`);
    }

    await sleep(1200); // Be nice to the server

    if ((i + 1) % 50 === 0) {
      console.log(`[${i + 1}/${rows.length}] success:${success} failed:${failed} noData:${noData}`);
    }
  }

  console.log('\n=== COMPLETE ===');
  console.log(`Total: ${rows.length} | Success: ${success} | Failed: ${failed} | No data: ${noData}`);

  await pool.end();
}

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