← back to Watches

thibaut-price-scraper.js

205 lines

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

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

const BATCH_SIZE = 10; // Products per batch
const DELAY_BETWEEN = 1500; // ms between requests
const MAX_RETRIES = 2;

async function login(page) {
  console.log('Logging into Thibaut...');
  await page.goto('https://www.thibautdesign.com/account/login', { waitUntil: 'networkidle2', timeout: 30000 });
  await page.type('#email', 'info@designerwallcoverings.com');
  await page.type('#password', '*Thibautaccess911*');
  const navP = page.waitForNavigation({ waitUntil: 'networkidle2', timeout: 15000 }).catch(() => {});
  await page.evaluate(() => {
    const forms = document.querySelectorAll('form');
    for (const f of forms) {
      if (f.action.includes('/account/login')) { f.submit(); break; }
    }
  });
  await navP;
  const loggedIn = !page.url().includes('/login');
  console.log(loggedIn ? 'Login successful' : 'Login FAILED');
  return loggedIn;
}

async function scrapePrices(page, url) {
  await page.goto(url, { waitUntil: 'networkidle2', timeout: 20000 });
  await new Promise(r => setTimeout(r, 1000));

  const prices = await page.evaluate(() => {
    const text = document.body.innerText;
    const result = { retailPrice: null, yourCost: null, unit: '' };

    // Extract Retail Price
    const retailMatch = text.match(/Retail\s*Price:\s*\$?([\d,.]+)(?:\/([\w]+))?/i);
    if (retailMatch) {
      const val = parseFloat(retailMatch[1].replace(',', ''));
      if (val > 0) {
        result.retailPrice = val;
        result.unit = retailMatch[2] || '';
      }
    }

    // Extract Your Cost
    const costMatch = text.match(/Your\s*Cost:\s*\$?([\d,.]+)(?:\/([\w]+))?/i);
    if (costMatch) {
      const val = parseFloat(costMatch[1].replace(',', ''));
      if (val > 0) {
        result.yourCost = val;
        if (!result.unit) result.unit = costMatch[2] || '';
      }
    }

    return result;
  });

  return prices;
}

async function main() {
  const startTime = Date.now();

  // Get all products that need pricing
  const { rows: products } = await pool.query(`
    SELECT mfr_sku, product_url, retail_price, your_cost
    FROM thibaut_catalog
    WHERE product_url IS NOT NULL AND product_url != ''
    ORDER BY mfr_sku
  `);
  console.log(`Total products: ${products.length}`);

  // Filter to only those missing prices
  const needsPricing = products.filter(p => !p.retail_price && !p.your_cost);
  console.log(`Need pricing: ${needsPricing.length}`);
  console.log(`Already have pricing: ${products.length - needsPricing.length}`);

  if (needsPricing.length === 0) {
    console.log('All products already have pricing!');
    await pool.end();
    return;
  }

  const browser = await puppeteer.launch({
    headless: 'new',
    executablePath: '/root/.cache/puppeteer/chrome/linux-145.0.7632.46/chrome-linux64/chrome',
    args: ['--no-sandbox', '--disable-setuid-sandbox', '--disable-dev-shm-usage']
  });
  const page = await browser.newPage();
  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
  const loggedIn = await login(page);
  if (!loggedIn) {
    console.error('Failed to login. Exiting.');
    await browser.close();
    await pool.end();
    return;
  }

  let success = 0;
  let failed = 0;
  let skipped = 0;
  let noPricing = 0;

  for (let i = 0; i < needsPricing.length; i++) {
    const product = needsPricing[i];
    const sku = product.mfr_sku;
    const url = product.product_url;

    try {
      const prices = await scrapePrices(page, url);

      if (prices.retailPrice || prices.yourCost) {
        // Calculate DW Retail Price = Cost / 0.6 / 0.85
        let dwRetailPrice = null;
        if (prices.yourCost) {
          dwRetailPrice = Math.round((prices.yourCost / 0.6 / 0.85) * 100) / 100;
        }

        await pool.query(`
          UPDATE thibaut_catalog
          SET retail_price = $1, your_cost = $2, dw_retail_price = $3, updated_at = NOW()
          WHERE mfr_sku = $4
        `, [prices.retailPrice, prices.yourCost, dwRetailPrice, sku]);

        success++;
        if ((i + 1) % 25 === 0 || i < 5) {
          console.log(`[${i + 1}/${needsPricing.length}] ${sku}: Retail=$${prices.retailPrice || 'N/A'}, Cost=$${prices.yourCost || 'N/A'}, DW=$${dwRetailPrice || 'N/A'} (${prices.unit})`);
        }
      } else {
        noPricing++;
        if (noPricing <= 10) {
          console.log(`[${i + 1}/${needsPricing.length}] ${sku}: No pricing found`);
        }
      }
    } catch (err) {
      failed++;
      if (failed <= 10) {
        console.log(`[${i + 1}/${needsPricing.length}] ${sku}: ERROR - ${err.message}`);
      }

      // Re-login if we got logged out
      if (err.message.includes('timeout') || err.message.includes('Navigation')) {
        console.log('Possible session issue, re-logging in...');
        try { await login(page); } catch {}
      }
    }

    // Rate limiting
    if (i > 0 && i % BATCH_SIZE === 0) {
      await new Promise(r => setTimeout(r, DELAY_BETWEEN));
    }

    // Progress report every 100
    if ((i + 1) % 100 === 0) {
      const elapsed = Math.round((Date.now() - startTime) / 1000);
      const rate = Math.round(i / elapsed * 60);
      const remaining = Math.round((needsPricing.length - i) / rate);
      console.log(`--- Progress: ${i + 1}/${needsPricing.length} | Success: ${success} | NoPricing: ${noPricing} | Failed: ${failed} | ${rate}/min | ~${remaining}min remaining ---`);
    }
  }

  const elapsed = Math.round((Date.now() - startTime) / 1000);
  console.log(`\n=== COMPLETE ===`);
  console.log(`Total: ${needsPricing.length}`);
  console.log(`Success: ${success}`);
  console.log(`No Pricing: ${noPricing}`);
  console.log(`Failed: ${failed}`);
  console.log(`Time: ${Math.round(elapsed / 60)}min`);

  // Show summary of pricing data
  const { rows: summary } = await pool.query(`
    SELECT
      COUNT(*) as total,
      COUNT(retail_price) as has_retail,
      COUNT(your_cost) as has_cost,
      COUNT(dw_retail_price) as has_dw_price,
      ROUND(AVG(retail_price)::numeric, 2) as avg_retail,
      ROUND(AVG(your_cost)::numeric, 2) as avg_cost,
      ROUND(AVG(dw_retail_price)::numeric, 2) as avg_dw_price
    FROM thibaut_catalog
  `);
  console.log('\nDatabase summary:', JSON.stringify(summary[0], null, 2));

  await browser.close();
  await pool.end();

  // Send Slack notification
  try {
    await fetch('https://hooks.slack.com/services/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', {
      method: 'POST',
      headers: { 'Content-Type': 'application/json' },
      body: JSON.stringify({
        text: `✅ *Thibaut Price Scraper Complete*\n• Total: ${needsPricing.length}\n• Success: ${success}\n• No Pricing: ${noPricing}\n• Failed: ${failed}\n• Duration: ${Math.round(elapsed / 60)}min\n• DW Retail Price formula: Cost / 0.6 / 0.85`
      })
    });
  } catch {}
}

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