← back to Watches

thibaut-description-backfill.js

379 lines

#!/usr/bin/env node

/**
 * Thibaut Description Backfill Script
 *
 * Scrapes missing product descriptions from Thibaut website and updates PostgreSQL.
 * Handles ~2,749 products missing descriptions.
 */

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

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

// Thibaut login credentials
const THIBAUT_LOGIN = {
  url: 'https://www.thibautdesign.com/account/login',
  email: 'info@designerwallcoverings.com',
  password: '*Thibautaccess911*'
};

// Rate limiting configuration
const RATE_LIMIT = {
  requestDelay: 1500,      // 1.5 seconds between requests
  batchSize: 10,           // Process 10 products
  batchPause: 2000,        // 2 second pause after each batch
  logInterval: 25          // Log progress every 25 products
};

// Statistics
const stats = {
  total: 0,
  processed: 0,
  updated: 0,
  skipped: 0,
  failed: 0,
  startTime: Date.now()
};

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

/**
 * Initialize Puppeteer browser
 */
async function initBrowser() {
  console.log('🚀 Launching browser...');
  const browser = await puppeteer.launch({
    executablePath: '/root/.cache/puppeteer/chrome/linux-145.0.7632.46/chrome-linux64/chrome',
    headless: 'new',
    args: [
      '--no-sandbox',
      '--disable-setuid-sandbox',
      '--disable-dev-shm-usage'
    ]
  });
  return browser;
}

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

  try {
    await page.goto(THIBAUT_LOGIN.url, { waitUntil: 'networkidle2', timeout: 30000 });

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

    // Setup navigation promise that catches errors
    const navP = page.waitForNavigation({ waitUntil: 'networkidle2', timeout: 15000 }).catch(() => {});

    // Submit form
    await page.evaluate(() => {
      const forms = document.querySelectorAll('form');
      for (const f of forms) {
        if (f.action.includes('/account/login')) {
          f.submit();
          break;
        }
      }
    });

    // Wait for navigation
    await navP;

    // Check if login was successful (not on login page)
    const loggedIn = !page.url().includes('/login');
    console.log(loggedIn ? '✅ Login successful' : '❌ Login failed');
    return loggedIn;
  } catch (error) {
    console.error('❌ Login failed:', error.message);
    return false;
  }
}

/**
 * Check if we need to re-login (session expired)
 */
async function checkSession(page) {
  const url = page.url();
  if (url.includes('/account/login') || url.includes('/challenge')) {
    console.log('⚠️  Session expired, re-logging in...');
    return await loginToThibaut(page);
  }
  return true;
}

/**
 * Scrape product description and collection from URL
 */
async function scrapeProductDetails(page, productUrl, mfrSku) {
  try {
    // Navigate to product page
    await page.goto(productUrl, { waitUntil: 'networkidle2', timeout: 30000 });

    // Check if we got redirected to login
    if (!(await checkSession(page))) {
      throw new Error('Failed to maintain session');
    }

    // Wait a moment for page to fully load
    await sleep(1000);

    // Extract description and collection
    const details = await page.evaluate(() => {
      // Blacklist of common non-description text to ignore
      const blacklist = [
        'looks like you haven\'t added anything',
        'let\'s get you started',
        'add to cart',
        'add to wishlist',
        'sign in',
        'create account',
        'continue shopping',
        'your cart is empty',
        'transform any space with statement-making walls',  // Generic marketing blurb
        'browse thousands of exclusive wallcoverings'       // Part of generic blurb
      ];

      function isBlacklisted(text) {
        const lower = text.toLowerCase();
        return blacklist.some(phrase => lower.includes(phrase));
      }

      // Find description - Thibaut uses span.markup for product descriptions
      // ONLY accept span.markup - other text on page is generic marketing
      let description = '';

      const markupElem = document.querySelector('span.markup');
      if (markupElem) {
        const text = markupElem.textContent.trim();
        // Actual descriptions are >50 chars. Shorter text is usually just product name/SKU
        if (text.length > 50 && !isBlacklisted(text)) {
          description = text;
        }
      }

      // If no span.markup found or text too short, product doesn't have a description - skip it
      // (Don't fall back to other selectors - they ALL pick up generic marketing text)

      // Find collection name
      const collectionSelectors = [
        '.product-collection',
        '.collection-name',
        '.breadcrumb a:nth-last-child(2)',
        '[itemprop="collection"]',
        '.product__meta .collection',
        '.product-single__vendor a',
        '.product-vendor a'
      ];

      let collection = '';
      for (const selector of collectionSelectors) {
        const elem = document.querySelector(selector);
        if (elem && elem.textContent.trim()) {
          collection = elem.textContent.trim();
          break;
        }
      }

      // Try breadcrumb navigation
      if (!collection) {
        const breadcrumbs = Array.from(document.querySelectorAll('.breadcrumb a, .breadcrumbs a'));
        if (breadcrumbs.length > 1) {
          collection = breadcrumbs[breadcrumbs.length - 2].textContent.trim();
        }
      }

      return { description, collection };
    });

    return details;

  } catch (error) {
    console.error(`   ❌ Error scraping ${mfrSku}: ${error.message}`);
    return null;
  }
}

/**
 * Update product in database
 */
async function updateProduct(mfrSku, description, collection) {
  try {
    const query = `
      UPDATE thibaut_catalog
      SET mfr_description = $1,
          collection = COALESCE($2, collection),
          updated_at = NOW()
      WHERE mfr_sku = $3
      RETURNING mfr_sku
    `;

    const result = await pool.query(query, [description, collection || null, mfrSku]);
    return result.rowCount > 0;
  } catch (error) {
    console.error(`   ❌ Database error for ${mfrSku}:`, error.message);
    return false;
  }
}

/**
 * Get products needing descriptions
 */
async function getProductsToProcess() {
  const query = `
    SELECT mfr_sku, product_url, collection
    FROM thibaut_catalog
    WHERE (mfr_description IS NULL OR mfr_description = '')
    AND product_url IS NOT NULL
    AND product_url != ''
    ORDER BY mfr_sku
  `;

  const result = await pool.query(query);
  return result.rows;
}

/**
 * Log progress
 */
function logProgress() {
  const elapsed = Math.floor((Date.now() - stats.startTime) / 1000);
  const rate = stats.processed / (elapsed / 60);
  const remaining = stats.total - stats.processed;
  const eta = remaining > 0 ? Math.floor(remaining / rate) : 0;

  console.log(`
📊 Progress Report (${stats.processed}/${stats.total})
   ✅ Updated: ${stats.updated}
   ⏭️  Skipped: ${stats.skipped}
   ❌ Failed: ${stats.failed}
   ⏱️  Elapsed: ${elapsed}s
   📈 Rate: ${rate.toFixed(1)} products/min
   ⏳ ETA: ${eta} minutes
  `);
}

/**
 * Main processing function
 */
async function processProducts() {
  let browser = null;
  let page = null;

  try {
    // Get products to process
    console.log('📋 Fetching products needing descriptions...');
    const products = await getProductsToProcess();
    stats.total = products.length;

    console.log(`Found ${stats.total} products to process\n`);

    if (stats.total === 0) {
      console.log('✅ No products need description backfill!');
      return;
    }

    // Initialize browser
    browser = await initBrowser();
    page = await browser.newPage();

    // Set user agent
    await page.setUserAgent('Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/120.0.0.0 Safari/537.36');

    // Login
    if (!(await loginToThibaut(page))) {
      throw new Error('Initial login failed');
    }

    console.log('\n🏃 Starting description backfill...\n');

    // Process each product
    for (let i = 0; i < products.length; i++) {
      const product = products[i];
      const { mfr_sku, product_url, collection } = product;

      stats.processed++;

      console.log(`[${stats.processed}/${stats.total}] Processing ${mfr_sku}...`);

      // Scrape product details
      const details = await scrapeProductDetails(page, product_url, mfr_sku);

      if (!details || !details.description) {
        console.log(`   ⏭️  No description found, skipping`);
        stats.skipped++;
      } else {
        // Update database
        const updated = await updateProduct(
          mfr_sku,
          details.description,
          details.collection
        );

        if (updated) {
          console.log(`   ✅ Updated with description (${details.description.length} chars)`);
          if (details.collection && details.collection !== collection) {
            console.log(`   📁 Collection updated: "${collection}" → "${details.collection}"`);
          }
          stats.updated++;
        } else {
          console.log(`   ❌ Database update failed`);
          stats.failed++;
        }
      }

      // Rate limiting
      await sleep(RATE_LIMIT.requestDelay);

      // Batch pause
      if (stats.processed % RATE_LIMIT.batchSize === 0) {
        console.log(`   ⏸️  Batch complete, pausing for ${RATE_LIMIT.batchPause}ms...\n`);
        await sleep(RATE_LIMIT.batchPause);
      }

      // Progress logging
      if (stats.processed % RATE_LIMIT.logInterval === 0) {
        logProgress();
      }
    }

    // Final report
    console.log('\n' + '='.repeat(60));
    console.log('🎉 BACKFILL COMPLETE!');
    logProgress();
    console.log('='.repeat(60) + '\n');

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

// Run the script
console.log('🔍 Thibaut Description Backfill Script');
console.log('=' .repeat(60) + '\n');

processProducts().catch(error => {
  console.error('💥 Unhandled error:', error);
  process.exit(1);
});