← back to Watches

thibaut-room-settings-scraper.js

445 lines

#!/usr/bin/env node

const puppeteer = require('/root/Projects/watches/node_modules/puppeteer');
const { Pool } = require('pg');

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

// Thibaut login credentials
const THIBAUT_EMAIL = 'info@designerwallcoverings.com';
const THIBAUT_PASSWORD = '*Thibautaccess911*';

// Puppeteer settings
const CHROME_PATH = '/root/.cache/puppeteer/chrome/linux-145.0.7632.46/chrome-linux64/chrome';
const RATE_LIMIT_MS = 2000;

// Stats tracking
const stats = {
  collectionsFound: 0,
  collectionsScraped: 0,
  collectionsFailed: 0,
  roomSettingsFound: 0,
  roomSettingsInserted: 0,
  roomSettingsDuplicate: 0,
  productsMatched: 0
};

/**
 * Sleep helper function
 */
function sleep(ms) {
  return new Promise(resolve => setTimeout(resolve, ms));
}

/**
 * Initialize database table
 */
async function initDatabase() {
  console.log('\n🗄️  Initializing database...');

  const createTableSQL = `
    CREATE TABLE IF NOT EXISTS thibaut_room_settings (
      id SERIAL PRIMARY KEY,
      collection_name VARCHAR(255),
      collection_url TEXT,
      image_url TEXT NOT NULL,
      image_filename VARCHAR(500),
      pattern_names TEXT[],
      mfr_skus TEXT[],
      description TEXT,
      sort_order INTEGER,
      crawled_at TIMESTAMP DEFAULT NOW(),
      UNIQUE(image_url)
    );

    CREATE INDEX IF NOT EXISTS idx_trs_collection ON thibaut_room_settings(collection_name);
    CREATE INDEX IF NOT EXISTS idx_trs_skus ON thibaut_room_settings USING GIN(mfr_skus);
  `;

  await pool.query(createTableSQL);
  console.log('✅ Database table ready');
}

/**
 * Parse pattern names from filename
 * Example: "Grand-Palace-JardinBloom-white-ArchitectEmbroidery-HarvestGold1.jpg"
 * Returns: ["Grand Palace", "Jardin Bloom", "Architect Embroidery", "Harvest Gold"]
 */
function parsePatternNames(filename) {
  if (!filename) return [];

  // Remove extension and split on hyphens
  const base = filename.replace(/\.(jpg|jpeg|png|webp)$/i, '');
  const parts = base.split('-');

  const patterns = [];
  let currentPattern = '';

  for (const part of parts) {
    // Skip color words and numbers
    if (/^(white|black|blue|red|green|yellow|gold|silver|grey|gray|beige|cream|ivory|brown|tan)$/i.test(part)) {
      if (currentPattern) {
        patterns.push(currentPattern.trim());
        currentPattern = '';
      }
      continue;
    }
    if (/^\d+$/.test(part)) {
      if (currentPattern) {
        patterns.push(currentPattern.trim());
        currentPattern = '';
      }
      continue;
    }

    // If starts with capital, might be new pattern
    if (/^[A-Z]/.test(part)) {
      if (currentPattern) {
        patterns.push(currentPattern.trim());
      }
      currentPattern = part;
    } else {
      currentPattern += ' ' + part;
    }
  }

  if (currentPattern) {
    patterns.push(currentPattern.trim());
  }

  return patterns.filter(p => p.length > 2); // Remove very short patterns
}

/**
 * Login to Thibaut
 */
async function loginToThibaut(page) {
  console.log('\n🔐 Logging into Thibaut...');

  await page.goto('https://www.thibautdesign.com/account/login', {
    waitUntil: 'domcontentloaded',
    timeout: 60000
  });

  // Wait for login form
  await page.waitForSelector('#email', { timeout: 10000 });

  // Fill in credentials
  await page.type('#email', THIBAUT_EMAIL);
  await page.type('#password', THIBAUT_PASSWORD);

  // Submit form and wait for navigation with Promise.race for timeout
  try {
    await Promise.all([
      page.waitForNavigation({ waitUntil: 'domcontentloaded', timeout: 60000 }),
      page.evaluate(() => {
        const forms = document.querySelectorAll('form');
        for (const f of forms) {
          if (f.action.includes('/account/login')) {
            f.submit();
            break;
          }
        }
      })
    ]);
  } catch (err) {
    console.log('⚠️  Navigation timeout, checking if login succeeded...');

    // Check if we're logged in by looking for account elements
    const currentUrl = page.url();
    if (!currentUrl.includes('/account/login')) {
      console.log('✅ Login appears successful (redirected from login page)');
    } else {
      throw new Error('Login failed - still on login page');
    }
  }

  // Wait a moment for any redirects
  await sleep(3000);

  console.log('✅ Logged in successfully');
}

/**
 * Extract all collection links from collections page
 */
async function extractCollectionLinks(page) {
  console.log('\n📚 Extracting collection links...');

  await page.goto('https://www.thibautdesign.com/collections', {
    waitUntil: 'networkidle2',
    timeout: 60000
  });

  const collections = await page.evaluate(() => {
    const links = [];
    const anchors = document.querySelectorAll('a[href*="/collections/"]');

    anchors.forEach(a => {
      const href = a.href;
      const text = a.textContent.trim();

      // Skip duplicates and main collections page
      if (href && href !== 'https://www.thibautdesign.com/collections' &&
          !links.find(l => l.url === href)) {
        links.push({
          name: text || 'Untitled Collection',
          url: href
        });
      }
    });

    return links;
  });

  stats.collectionsFound = collections.length;
  console.log(`✅ Found ${collections.length} collections`);

  return collections;
}

/**
 * Scrape room settings from a collection page
 */
async function scrapeCollectionRoomSettings(browser, collection) {
  console.log(`\n🖼️  Scraping collection: ${collection.name}`);
  console.log(`   URL: ${collection.url}`);

  // Create a new page for each collection to avoid frame detachment issues
  const page = await browser.newPage();
  await page.setViewport({ width: 1920, height: 1080 });
  await page.setUserAgent('Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/120.0.0.0 Safari/537.36');

  try {
    await page.goto(collection.url, {
      waitUntil: 'domcontentloaded',
      timeout: 60000
    });

    // Wait a moment for dynamic content
    await sleep(5000);

    // Extract room setting images
    const roomSettings = await page.evaluate((collectionUrl) => {
      const images = [];

      // Look for various possible selectors for room galleries
      const selectors = [
        '.swiper.room-gallery img',
        '.room-gallery img',
        '.roomset img',
        '.room-setting img',
        'img[src*="Roomset"]',
        'img[src*="roomset"]',
        'img[src*="Room-"]',
        'img[src*="d2g31xe1lftxud.cloudfront.net"]'
      ];

      const foundImages = new Set();

      selectors.forEach(selector => {
        const imgs = document.querySelectorAll(selector);
        imgs.forEach((img, index) => {
          const src = img.src || img.dataset.src || img.getAttribute('data-src');
          if (src && !foundImages.has(src)) {
            foundImages.add(src);

            // Extract filename
            const filename = src.split('/').pop().split('?')[0];

            images.push({
              url: src,
              filename: filename,
              sortOrder: images.length + 1
            });
          }
        });
      });

      return images;
    }, collection.url);

    console.log(`   Found ${roomSettings.length} room setting images`);
    stats.roomSettingsFound += roomSettings.length;

    // Insert into database
    for (const rs of roomSettings) {
      const patternNames = parsePatternNames(rs.filename);

      try {
        await pool.query(`
          INSERT INTO thibaut_room_settings
          (collection_name, collection_url, image_url, image_filename, pattern_names, sort_order)
          VALUES ($1, $2, $3, $4, $5, $6)
          ON CONFLICT (image_url) DO NOTHING
        `, [
          collection.name,
          collection.url,
          rs.url,
          rs.filename,
          patternNames,
          rs.sortOrder
        ]);

        stats.roomSettingsInserted++;
        console.log(`   ✅ Inserted: ${rs.filename}`);
        console.log(`      Patterns: ${patternNames.join(', ')}`);
      } catch (err) {
        if (err.code === '23505') { // Duplicate
          stats.roomSettingsDuplicate++;
        } else {
          console.error(`   ❌ Error inserting ${rs.filename}:`, err.message);
        }
      }
    }

    stats.collectionsScraped++;

  } catch (err) {
    console.error(`   ❌ Failed to scrape collection: ${err.message}`);
    stats.collectionsFailed++;
  } finally {
    // Always close the page
    await page.close();
  }
}

/**
 * Match room settings to products in thibaut_catalog
 */
async function matchRoomSettingsToProducts() {
  console.log('\n🔗 Matching room settings to products...');

  const matchSQL = `
    UPDATE thibaut_room_settings rs
    SET mfr_skus = (
      SELECT array_agg(DISTINCT tc.mfr_sku)
      FROM thibaut_catalog tc
      WHERE tc.pattern_name ILIKE ANY(
        SELECT '%' || unnest(rs.pattern_names) || '%'
      )
    )
    WHERE (mfr_skus IS NULL OR array_length(mfr_skus, 1) IS NULL)
    AND pattern_names IS NOT NULL
    AND array_length(pattern_names, 1) > 0
  `;

  const result = await pool.query(matchSQL);
  stats.productsMatched = result.rowCount;

  console.log(`✅ Matched ${result.rowCount} room settings to products`);

  // Show some examples
  const examples = await pool.query(`
    SELECT collection_name, image_filename, pattern_names, mfr_skus
    FROM thibaut_room_settings
    WHERE mfr_skus IS NOT NULL
    LIMIT 5
  `);

  if (examples.rows.length > 0) {
    console.log('\n📋 Example matches:');
    examples.rows.forEach(row => {
      console.log(`   ${row.image_filename}`);
      console.log(`   Patterns: ${row.pattern_names.join(', ')}`);
      console.log(`   SKUs: ${row.mfr_skus.join(', ')}`);
      console.log('');
    });
  }
}

/**
 * Print summary statistics
 */
function printSummary() {
  console.log('\n' + '='.repeat(60));
  console.log('📊 SCRAPING SUMMARY');
  console.log('='.repeat(60));
  console.log(`Collections found:       ${stats.collectionsFound}`);
  console.log(`Collections scraped:     ${stats.collectionsScraped}`);
  console.log(`Collections failed:      ${stats.collectionsFailed}`);
  console.log(`Room settings found:     ${stats.roomSettingsFound}`);
  console.log(`Room settings inserted:  ${stats.roomSettingsInserted}`);
  console.log(`Room settings duplicate: ${stats.roomSettingsDuplicate}`);
  console.log(`Products matched:        ${stats.productsMatched}`);
  console.log('='.repeat(60) + '\n');
}

/**
 * Main execution
 */
async function main() {
  console.log('🚀 Starting Thibaut Room Settings Scraper\n');

  let browser;

  try {
    // Initialize database
    await initDatabase();

    // Launch browser
    console.log('🌐 Launching browser...');
    browser = await puppeteer.launch({
      executablePath: CHROME_PATH,
      headless: true,
      args: [
        '--no-sandbox',
        '--disable-setuid-sandbox',
        '--disable-dev-shm-usage'
      ]
    });

    const page = await browser.newPage();
    await page.setViewport({ width: 1920, height: 1080 });

    // Set user agent
    await page.setUserAgent('Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/120.0.0.0 Safari/537.36');

    // Login
    await loginToThibaut(page);

    // Get all collection links
    const collections = await extractCollectionLinks(page);

    // Scrape each collection (pass browser instead of page)
    for (let i = 0; i < collections.length; i++) {
      const collection = collections[i];
      console.log(`\n[${i + 1}/${collections.length}]`);

      await scrapeCollectionRoomSettings(browser, collection);

      // Rate limit
      if (i < collections.length - 1) {
        console.log(`   ⏳ Waiting ${RATE_LIMIT_MS}ms...`);
        await sleep(RATE_LIMIT_MS);
      }
    }

    // Match room settings to products
    await matchRoomSettingsToProducts();

    // Print summary
    printSummary();

  } catch (err) {
    console.error('\n❌ Fatal error:', err);
    throw err;
  } finally {
    if (browser) {
      await browser.close();
    }
    await pool.end();
  }

  console.log('✅ Scraper completed successfully\n');
}

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