`d3c_stage.mjs`

149 lines
#!/usr/bin/env node
// TK-11331 D3b follow-on C — STAGE the 1,215 high-confidence new-pattern Momentum→Hollywood
// colorways into momentum_colorways (Mac2-canonical staging). STAGING + ENRICHMENT ONLY.
//   - Insert set = in-scope (Wallcovering/Acoustic) feed rows whose NORMALIZED pattern_name is
//     absent from staging (the 65 entirely-new patterns = 1,215 cw). Extend-pattern + the 6
//     normalized-collision patterns (Cavern II, Rectangular *9/16) are HELD (not inserted).
//   - DEDUP GUARD: INSERT ... ON CONFLICT (pattern_name,color_name) DO NOTHING  (== the DB
//     UNIQUE collation exactly). Reports guard skips.
//   - Pricing: cost=list*0.80 ; hw_price(sell)=list*1.448 (the verified existing markup — NOT list*0.80).
//   - dw_sku LEFT NULL (assign-sku FROZEN, out of scope). pl_brand/pl_real_vendor = table defaults.
//   - Acoustic rows: sub_category='Acoustic Panel' (staging product_type marker; no product_type col).
//   - Settlement: colorways of Hula WC / Sea Line / Palomar / Oakstone get tags=["settlement-review"].
//   - image_url + collection(series) decorated from a cap-safe sharded Meilisearch lookup keyed on `number`.
//   - Reversible: writes data/d3c-stage-restore.jsonl (every inserted id + key). Rollback = DELETE those ids.
// $0 — local psql + plain HTTPS feed reads. Run: NODE_PATH=.../node_modules node d3c_stage.mjs [--commit]
import { createRequire } from 'module';
import fs from 'fs';
import path from 'path';
const require = createRequire(import.meta.url);
const { Pool } = require('pg');

const HERE = path.dirname(new URL(import.meta.url).pathname);
const TSV = path.join(HERE, 'data', 'momentum_feed_full.tsv');
const RESTORE = path.join(HERE, 'data', 'd3c-stage-restore.jsonl');
const RESULTS = path.join(HERE, 'data', 'd3c-stage-results.json');
const COMMIT = process.argv.includes('--commit');

const HOST = 'https://ms-e886719d86e7-4256.sfo.meilisearch.io';
// NOT A SECRET — vendor-owned PUBLIC search key. Momentum ships this exact literal in
// their own ANONYMOUS browser bundle (momentumco.com/build/assets/app-*.js) and it is
// search-scoped only (GET /keys -> 403 invalid_api_key). We do NOT own the Meilisearch
// account, so it is not ours to revoke or rotate. Do not route it through the secrets
// manager and do not re-open it as a leak. Verified read-only 2026-09-10 — TK-11133/TK-11415.
const KEY = process.env.MOMENTUM_MS_KEY || '95fe8376edc78d49e787db49edda68426b21649f6271f49e2f1412118612fbd6';
const INDEX = 'redesign-colors';
const CAT_IDS = [1, 2, 3, 4, 5, 6, 7, 8];
const KEEP = new Set(['Wallcovering', 'Acoustic']);
const SETTLEMENT = new Set(['Hula WC', 'Sea Line', 'Palomar', 'Oakstone']);
const COST_MULT = 0.80, HW_MULT = 1.448;

const pool = new Pool({ connectionString: 'postgresql://dw_admin:DW2024!@127.0.0.1:5432/dw_unified' });
const norm = s => (s || '').replace(/\s+/g, ' ').trim().toLowerCase();
const money = n => (n == null || !(Number(n) > 0)) ? null : Number(Number(n).toFixed(2));

function readTSV() {
  const lines = fs.readFileSync(TSV, 'utf8').split('\n').filter(Boolean);
  const cols = lines[0].split('\t');
  return lines.slice(1).map(l => { const p = l.split('\t'); const o = {}; cols.forEach((c, i) => o[c] = p[i]); return o; });
}

async function fetchImageMap() {
  const map = new Map(); // number -> {image, series}
  for (const cid of CAT_IDS) {
    for (let off = 0; off < 20000; off += 200) {
      const r = await fetch(`${HOST}/indexes/${INDEX}/search`, {
        method: 'POST', headers: { Authorization: `Bearer ${KEY}`, 'Content-Type': 'application/json' },
        body: JSON.stringify({ q: '', filter: `category_id = ${cid}`, limit: 200, offset: off,
          attributesToRetrieve: ['number', 'medium_image_url', 'series', 'collections'] }),
      });
      if (!r.ok) throw new Error(`img feed ${r.status} cat ${cid} off ${off}`);
      const hits = (await r.json()).hits || [];
      if (!hits.length) break;
      for (const h of hits) {
        if (!h.number) continue;
        map.set(String(h.number), {
          image: h.medium_image_url || null,
          series: h.series || (Array.isArray(h.collections) ? h.collections[0]?.name : null) || null,
        });
      }
    }
    process.stdout.write(`\r  image-map cat ${cid}  size ${map.size}   `);
  }
  console.log(`\n  image map built: ${map.size} SKUs`);
  return map;
}

async function main() {
  // 1. staged pattern set (normalized) — the classification key
  const { rows: pr } = await pool.query('SELECT DISTINCT pattern_name FROM momentum_colorways');
  const stagedNorm = new Set(pr.map(r => norm(r.pattern_name)));
  const baselineCount = (await pool.query('SELECT count(*)::int n FROM momentum_colorways')).rows[0].n;

  // 2. build insert set from TSV
  const feed = readTSV();
  const inscope = feed.filter(r => KEEP.has(r.category_name) && (r.pattern_name || '').trim() && (r.preferred_color_name || '').trim());
  const newRows = inscope.filter(r => !stagedNorm.has(norm(r.pattern_name)));
  const seen = new Set(); const ins = [];
  for (const r of newRows) {
    const k = r.pattern_name + '' + r.preferred_color_name;
    if (seen.has(k)) continue; seen.add(k); ins.push(r);
  }
  const byCat = ins.reduce((a, r) => (a[r.category_name] = (a[r.category_name] || 0) + 1, a), {});
  const settlementCount = ins.filter(r => SETTLEMENT.has(r.pattern_name)).length;
  const acousticCount = ins.filter(r => r.category_name === 'Acoustic').length;
  console.log(`baseline momentum_colorways rows: ${baselineCount}`);
  console.log(`insert set: ${ins.length}  byCat=${JSON.stringify(byCat)}  settlement=${settlementCount}  acousticMarker=${acousticCount}`);

  // 3. image + series decoration (cap-safe sharded feed)
  const imgMap = await fetchImageMap();
  const withImg = ins.filter(r => imgMap.get(String(r.number))?.image).length;
  console.log(`  rows with image_url: ${withImg}/${ins.length}  (rest -> image_url NULL, Needs-Image at go-live)`);

  if (!COMMIT) {
    console.log('DRY-RUN (no DB writes). Re-run with --commit to stage.');
    await pool.end(); return;
  }

  // 4. single-transaction guarded insert
  const c = await pool.connect();
  let inserted = 0, skipped = 0; const restore = [];
  try {
    await c.query('BEGIN');
    for (const r of ins) {
      const list = money(r.list_price);
      const im = imgMap.get(String(r.number)) || {};
      const tags = SETTLEMENT.has(r.pattern_name) ? JSON.stringify(['settlement-review']) : null;
      const subcat = r.category_name === 'Acoustic' ? 'Acoustic Panel' : null;
      const uom = (r.uom || '').trim() || null;
      const res = await c.query(
        `INSERT INTO momentum_colorways
           (pattern_name,color_name,color_number,momentum_sku,image_url,list_price,width,category,
            product_line,collection_name,uom,price_unit,cost,hw_price,sub_category,tags,created_at,updated_at)
         VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,now(),now())
         ON CONFLICT (pattern_name,color_name) DO NOTHING
         RETURNING id`,
        [r.pattern_name, r.preferred_color_name, r.preferred_color_number || null, r.number || null,
         im.image || null, list, (r.base_width || '').trim() || null, r.category_name,
         (r.product_line_code || '').trim() || null, im.series || null, uom, uom,
         list == null ? null : money(list * COST_MULT), list == null ? null : money(list * HW_MULT),
         subcat, tags]);
      if (res.rows.length) { inserted++; restore.push({ id: res.rows[0].id, pattern_name: r.pattern_name, color_name: r.preferred_color_name }); }
      else skipped++;
    }
    await c.query('COMMIT');
  } catch (e) { await c.query('ROLLBACK'); console.error('ROLLBACK:', e.message); process.exitCode = 1; c.release(); await pool.end(); return; }
  c.release();

  fs.writeFileSync(RESTORE, restore.map(x => JSON.stringify(x)).join('\n') + '\n');
  const finalCount = (await pool.query('SELECT count(*)::int n FROM momentum_colorways')).rows[0].n;
  const results = { ts: new Date().toISOString(), target: 1215, inserted, guard_skips: skipped,
    baseline_rows: baselineCount, final_rows: finalCount, settlement_tagged: restore.filter(r => SETTLEMENT.has(r.pattern_name)).length,
    acoustic_marker: acousticCount, restore_file: RESTORE };
  fs.writeFileSync(RESULTS, JSON.stringify(results, null, 2));
  console.log(`COMMITTED — inserted ${inserted}, guard-skipped ${skipped}. rows ${baselineCount} -> ${finalCount}.`);
  console.log(`Restore: ${RESTORE}  Results: ${RESULTS}`);
  await pool.end();
}
main().catch(e => { console.error(e); process.exit(1); });