`scripts/d3b_recovery_refresh.mjs`

95 lines
#!/usr/bin/env node
// TK-11331 D3b — Momentum staging RECOVERY refresh (Mac2-canonical *_catalog; reversible; $0).
// Reads the already-scraped full live feed (data/momentum_feed_full.tsv) and:
//   (A) INSERT genuinely-new (pattern_name,color_name) WC+Acoustic colorways not in the table,
//       with alt_sku(=alt_product_description), momentum_sku, color_number, list_price,
//       hw_price = list*1.448, width, category, uom, product_line.
//   (B) UPDATE existing rows NON-CLOBBERINGLY: fill alt_sku where blank (join by momentum_sku=number),
//       fill hw_price where null (= list*1.448), fill list_price where null.
//   NEVER touches dw_sku, ai_*, description, shopify_*, pl_*, color/pattern names (curation is sacred).
// list_price = pre_discount_price>0 ? pre_discount_price : price  (already applied in the TSV scrape).
// Writes a full restore map (data/d3b-refresh-restore.jsonl) so every change is one-command reversible.
// DRY-RUN by default; --commit to apply. Snapshots before/after counts.
import { createRequire } from 'module';
import fs from 'fs';
const require = createRequire(import.meta.url);
const { Pool } = require('pg');
const HERE = new URL('..', import.meta.url).pathname;
const TSV = HERE + 'data/momentum_feed_full.tsv';
const RESTORE = HERE + 'data/d3b-refresh-restore.jsonl';
const MARKUP = 1.448;                      // list*0.80/0.65/0.85 = *1.4479 (verified Hollywood/PR markup)
const KEEP = new Set(['Wallcovering', 'Acoustic']);
const COMMIT = process.argv.includes('--commit');
const ALT_ONLY = process.argv.includes('--alt-only');  // recovery-surgical: backfill alt_sku only, no inserts, no price fills
const pool = new Pool({ connectionString: 'postgresql://dw_admin:DW2024!@127.0.0.1:5432/dw_unified' });

function loadFeed() {
  const lines = fs.readFileSync(TSV, 'utf8').split('\n');
  const hdr = lines[0].split('\t');
  const rows = [];
  for (let i = 1; i < lines.length; i++) {
    if (!lines[i]) continue;
    const r = Object.fromEntries(lines[i].split('\t').map((v, k) => [hdr[k], v]));
    if (!KEEP.has(r.category_name)) continue;
    if (!r.pattern_name || !r.preferred_color_name) continue;
    rows.push(r);
  }
  return rows;
}
const hw = lp => (lp != null && lp !== '' && Number(lp) > 0) ? +(Number(lp) * MARKUP).toFixed(2) : null;

async function main() {
  const feed = loadFeed();
  const c = await pool.connect();
  const restore = [];
  let ins = 0, updAlt = 0, updHw = 0, updLp = 0;
  const before = (await c.query(`SELECT count(*) t, count(*) FILTER (WHERE alt_sku IS NOT NULL AND alt_sku<>'') a, count(*) FILTER (WHERE hw_price IS NOT NULL) h FROM momentum_colorways`)).rows[0];
  try {
    if (COMMIT) await c.query('BEGIN');
    // existing keys
    const ex = new Map();  // "pat||col" -> {id, alt_sku, hw_price, list_price, momentum_sku}
    for (const r of (await c.query(`SELECT id,pattern_name,color_name,alt_sku,hw_price,list_price,momentum_sku FROM momentum_colorways`)).rows)
      ex.set(r.pattern_name + '||' + r.color_name, r);
    for (const f of feed) {
      const key = f.pattern_name + '||' + f.preferred_color_name;
      const row = ex.get(key);
      const lp = (f.list_price === '' ? null : Number(f.list_price));
      if (!row) {                                   // (A) INSERT new colorway
        if (ALT_ONLY) { ins++; continue; }          // alt-only mode: count would-be inserts, do NOT write
        if (COMMIT) {
          const res = await c.query(
            `INSERT INTO momentum_colorways
               (pattern_name,color_name,color_number,momentum_sku,alt_sku,image_url,list_price,hw_price,width,category,uom,product_line,created_at,updated_at)
             VALUES ($1,$2,$3,$4,$5,NULL,$6,$7,$8,$9,'YD',$10,now(),now())
             ON CONFLICT (pattern_name,color_name) DO NOTHING RETURNING id`,
            [f.pattern_name, f.preferred_color_name, f.preferred_color_number || null, f.number || null,
             f.alt_product_description || null, lp, hw(lp), f.base_width || null, f.category_name, f.product_line_code || null]);
          if (res.rows[0]) restore.push({ op: 'insert', id: res.rows[0].id });
        }
        ins++;
      } else {                                       // (B) non-clobbering fills
        const sets = [], vals = [], old = {};
        if ((!row.alt_sku || row.alt_sku === '') && f.alt_product_description) {
          sets.push(`alt_sku=$${sets.length + 1}`); vals.push(f.alt_product_description); old.alt_sku = row.alt_sku; updAlt++; }
        if (!ALT_ONLY && row.hw_price == null && lp != null) {
          old.hw_price = row.hw_price; old.list_price = row.list_price;
          sets.push(`hw_price=$${sets.length + 1}`); vals.push(hw(lp));
          sets.push(`list_price=$${sets.length + 1}`); vals.push(lp); updHw++; }
        else if (!ALT_ONLY && row.list_price == null && lp != null) {
          sets.push(`list_price=$${sets.length + 1}`); vals.push(lp); old.list_price = row.list_price; updLp++; }
        if (sets.length) {
          restore.push({ op: 'update', id: row.id, old });
          if (COMMIT) { vals.push(row.id); await c.query(`UPDATE momentum_colorways SET ${sets.join(',')},updated_at=now() WHERE id=$${vals.length}`, vals); }
        }
      }
    }
    if (COMMIT) { await c.query('COMMIT'); fs.writeFileSync(RESTORE, restore.map(r => JSON.stringify(r)).join('\n') + '\n'); }
    const after = COMMIT ? (await c.query(`SELECT count(*) t, count(*) FILTER (WHERE alt_sku IS NOT NULL AND alt_sku<>'') a, count(*) FILTER (WHERE hw_price IS NOT NULL) h FROM momentum_colorways`)).rows[0] : null;
    console.log(JSON.stringify({ mode: COMMIT ? 'COMMIT' : 'DRY-RUN', feed_wc_acoustic: feed.length,
      before, after, would_insert: ins, would_fill_alt_sku: updAlt, would_fill_hw_price: updHw, would_fill_list_only: updLp,
      restore_map: COMMIT ? RESTORE : '(dry-run, not written)' }, null, 2));
  } catch (e) { if (COMMIT) await c.query('ROLLBACK'); console.error('ROLLBACK:', e.message); process.exitCode = 1; }
  finally { c.release(); await pool.end(); }
}
main().catch(e => { console.error(e); process.exit(1); });