`lib/generic-url-refresh.js`

132 lines
'use strict';
// GENERIC product_url refresher (DTD Option C). For each existing SKU with a
// product_url, fetch the page (free), extract price/specs via the LOCAL enrich-router
// (exo -> Ollama, $0, no paid API), and UPDATE dw_unified staging. NO publish.
//
// Covers any vendor catalog that has a product_url column populated — hundreds of
// vendors — instead of hand-wiring each. Hand-wired <code>-refresh scripts remain the
// fallback for vendors with no product_url / anti-bot walls / JS-rendered pages.
//
// Safe: --dry-run fetches + extracts but writes NOTHING. Live writes only price/spec
// columns that exist on the table, only when extraction is confident.
const https = require('https');
const { pool } = require('./db');
const enrich = require('./enrich-router');
const cfg = require('./config');

function fetchHtml(url, timeoutMs = 15000, hops = 0) {
  return new Promise((resolve) => {
    if (hops > 4) return resolve({ ok: false, status: 'too-many-redirects' });
    let done = false;
    const finish = (v) => { if (done) return; done = true; try { req.destroy(); } catch {} resolve(v); };
    // GUARANTEED hard deadline — one tarpitting/anti-bot URL can never stall the batch.
    const hard = setTimeout(() => finish({ ok: false, status: 'timeout' }), timeoutMs);
    let req;
    try {
      req = https.get(url, { headers: { 'User-Agent': 'Mozilla/5.0 (Macintosh) DW-recrawl/1.0' } }, (res) => {
        if (res.statusCode >= 300 && res.statusCode < 400 && res.headers.location) {
          res.resume(); clearTimeout(hard); if (done) return; done = true;
          return resolve(fetchHtml(new URL(res.headers.location, url).href, timeoutMs, hops + 1));
        }
        if (res.statusCode !== 200) { res.resume(); clearTimeout(hard); return finish({ ok: false, status: res.statusCode }); }
        let body = ''; res.setEncoding('utf8');
        res.on('data', d => { body += d; if (body.length > 800_000) { res.destroy(); } });
        res.on('end', () => { clearTimeout(hard); finish({ ok: true, html: body }); });
      });
      req.on('error', () => { clearTimeout(hard); finish({ ok: false, status: 'error' }); });
    } catch { clearTimeout(hard); finish({ ok: false, status: 'bad-url' }); }
  });
}

// Strip HTML to visible-ish text, trimmed to a model-friendly size.
function htmlToText(html) {
  return html
    .replace(/<script[\s\S]*?<\/script>/gi, ' ')
    .replace(/<style[\s\S]*?<\/style>/gi, ' ')
    .replace(/<[^>]+>/g, ' ')
    .replace(/&nbsp;/g, ' ').replace(/&amp;/g, '&')
    .replace(/\s+/g, ' ')
    .trim()
    .slice(0, 6000);
}

// Ask the LOCAL model to extract structured fields from the page text. Returns object or null.
async function extractFields(vendorName, sku, text) {
  const prompt =
    `You are extracting wallcovering product data from a vendor product page.\n` +
    `Vendor: ${vendorName}. SKU: ${sku}.\n` +
    `From the PAGE TEXT below, return ONLY a compact JSON object with any of these keys you can find ` +
    `(omit keys you can't find, no prose): {"price": number, "width_in": number, "repeat_in": number, ` +
    `"material": string, "pattern_name": string, "discontinued": boolean}.\n\nPAGE TEXT:\n${text}`;
  const res = await enrich.chat([{ role: 'user', content: prompt }]);
  if (!res || res.skipped || !res.text) return null;
  const m = res.text.match(/\{[\s\S]*\}/);
  if (!m) return null;
  try { return { provider: res.provider, fields: JSON.parse(m[0]) }; } catch { return null; }
}

// Which price/spec columns actually exist on this table (only update real columns).
async function tableColumns(table) {
  const { rows } = await pool.query(
    `SELECT column_name FROM information_schema.columns WHERE table_name = $1`, [table]);
  return new Set(rows.map(r => r.column_name));
}

// Refresh up to `limit` stale SKUs (with product_url) for one vendor catalog.
// opts: { table, vendorName, skuCol, limit, dryRun }
async function refreshVendor({ table, vendorName, skuCol = 'mfr_sku', limit = 25, dryRun = true }) {
  const cols = await tableColumns(table);
  if (!cols.has('product_url')) return { table, skipped: 'no product_url column' };
  const orderStale = cols.has('updated_at') ? 'ORDER BY updated_at ASC NULLS FIRST' : '';
  const { rows } = await pool.query(
    `SELECT ${skuCol} AS sku, product_url FROM ${table}
      WHERE product_url IS NOT NULL AND product_url <> '' ${orderStale} LIMIT $1`, [limit]);

  let fetched = 0, extracted = 0, updated = 0, failed = 0;
  const sample = [];
  for (const r of rows) {
    const page = await fetchHtml(r.product_url);
    if (!page.ok) { failed++; continue; }
    fetched++;
    const ex = await extractFields(vendorName, r.sku, htmlToText(page.html));
    if (!ex || !ex.fields || !Object.keys(ex.fields).length) { continue; }
    extracted++;
    // build an UPDATE over only real, price/spec columns the model returned
    const set = [], vals = [];
    const map = { price: 'price', width_in: 'width_in', repeat_in: 'repeat_in',
                  material: 'material', pattern_name: 'pattern_name', discontinued: 'discontinued' };
    for (const [k, col] of Object.entries(map)) {
      if (ex.fields[k] != null && cols.has(col)) { vals.push(ex.fields[k]); set.push(`${col} = $${vals.length}`); }
    }
    if (!set.length) continue;
    if (sample.length < 5) sample.push({ sku: r.sku, via: ex.provider, fields: ex.fields });
    if (!dryRun) {
      if (cols.has('updated_at')) set.push(`updated_at = now()`);
      vals.push(r.sku);
      await pool.query(`UPDATE ${table} SET ${set.join(', ')} WHERE ${skuCol} = $${vals.length}`, vals);
      updated++;
    }
    await new Promise(s => setTimeout(s, 1200 + Math.floor((Date.now() % 800)))); // politeness
  }
  return { table, vendorName, rows: rows.length, fetched, extracted, updated, failed, dryRun, sample };
}

module.exports = { refreshVendor, fetchHtml, htmlToText, extractFields };

// CLI: node lib/generic-url-refresh.js <table> <vendorName> [limit] [--run]
//   default = DRY-RUN (writes nothing). --run performs live UPDATEs to local staging (no publish).
if (require.main === module) {
  (async () => {
    const argv = process.argv.slice(2);
    const flags = argv.filter(a => a.startsWith('--'));
    const pos = argv.filter(a => !a.startsWith('--'));
    const [table = 'thibaut_catalog', vendorName = 'Thibaut', limit = '3'] = pos;
    const dryRun = !flags.includes('--run');
    const backend = await enrich.resolveProvider();
    console.log(`  local backend: ${backend} | table=${table} | ${dryRun ? 'DRY-RUN (writes nothing)' : 'LIVE (updates local staging, no publish)'} | $0 local`);
    const r = await refreshVendor({ table, vendorName, limit: parseInt(limit, 10), dryRun });
    console.log(JSON.stringify(r, null, 2));
    await pool.end().catch(() => {});
  })();
}