`price-backfill.js`

76 lines
#!/usr/bin/env node
// ============================================================================
// Wolf Gordon price backfill.
// WG publishes a per-linear-yard price on each public product page
// ("$31.25 per linear yard"). That price is the COST input to the DW formula:
//   price_trade  = cost (scraped per-yard price)
//   price_retail = round(cost / 0.65 / 0.85, 2)      [Steve, 2026-06-19]
// Price is per-PATTERN (identical across colorways), so we fetch one
// representative page per pattern and apply to every colorway of that pattern.
// Local dw_unified only. $0 (public HTTP + local DB).
// ============================================================================
const https = require('https');
const { pool, wait, closePool } = require('./scraper-utils');

const UA = 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/120.0.0.0 Safari/537.36';

function fetchPage(url, redirects = 5) {
  return new Promise((resolve, reject) => {
    https.get(url, { timeout: 25000, headers: { 'User-Agent': UA, 'Accept': 'text/html' } }, (res) => {
      if ((res.statusCode === 301 || res.statusCode === 302) && res.headers.location && redirects > 0) {
        let loc = res.headers.location;
        if (loc.startsWith('/')) loc = 'https://www.wolfgordon.com' + loc;
        return fetchPage(loc, redirects - 1).then(resolve).catch(reject);
      }
      let d = ''; res.on('data', c => d += c); res.on('end', () => resolve({ html: d, status: res.statusCode }));
    }).on('error', reject).on('timeout', function () { this.destroy(); reject(new Error('timeout')); });
  });
}

function parseCost(html) {
  const m = html.match(/\$\s*([0-9]+(?:\.[0-9]{2})?)\s*per\s*linear\s*yard/i);
  return m ? parseFloat(m[1]) : null;
}
const retailFrom = cost => Math.round((cost / 0.65 / 0.85) * 100) / 100;

async function main() {
  // one representative product_url per pattern
  const { rows } = await pool.query(`
    SELECT pattern_name, MIN(product_url) AS url, COUNT(*) AS colorways
    FROM wolf_gordon_catalog
    WHERE pattern_name IS NOT NULL AND product_url IS NOT NULL
    GROUP BY pattern_name ORDER BY pattern_name`);
  console.log(`Patterns to price: ${rows.length}`);

  let priced = 0, rowsUpdated = 0, noPrice = 0, errors = 0, i = 0;
  const concurrency = 4;

  async function worker() {
    while (i < rows.length) {
      const r = rows[i++];
      try {
        const { html, status } = await fetchPage(r.url);
        if (status !== 200) { noPrice++; continue; }
        const cost = parseCost(html);
        if (!cost) { noPrice++; continue; }
        const retail = retailFrom(cost);
        const upd = await pool.query(
          `UPDATE wolf_gordon_catalog SET price_trade=$1, price_retail=$2, updated_at=NOW()
           WHERE pattern_name=$3`, [cost, retail, r.pattern_name]);
        priced++; rowsUpdated += upd.rowCount;
        if (priced <= 10) console.log(`  ${r.pattern_name.padEnd(24)} cost $${cost} -> retail $${retail}  (${upd.rowCount} colorways)`);
        if (priced % 50 === 0) console.log(`  ...${priced}/${rows.length} patterns priced (${rowsUpdated} rows)`);
        await wait(250, 150);
      } catch (e) { errors++; if (errors <= 5) console.error(`  err ${r.pattern_name}: ${e.message}`); await wait(500, 300); }
    }
  }
  await Promise.all(Array.from({ length: concurrency }, worker));

  const cov = await pool.query(`SELECT count(*) total, count(*) FILTER(WHERE price_retail IS NOT NULL) withprice FROM wolf_gordon_catalog`);
  console.log('\n=== PRICE BACKFILL DONE ===');
  console.log(`  patterns priced: ${priced} | no-price: ${noPrice} | errors: ${errors}`);
  console.log(`  catalog rows with price: ${cov.rows[0].withprice}/${cov.rows[0].total}`);
  await closePool();
}
main().catch(e => { console.error('FATAL', e); closePool().then(() => process.exit(1)); });