`d3b_heldreview_stage.mjs`

183 lines
#!/usr/bin/env node
// TK-11331 D3b-heldreview — classify the two HELD buckets + STAGE the STAGE-NEW/DISAMBIGUATE rows.
// Mirrors d3c_stage.mjs: hw_price=list*1.448, cost=list*0.80, sub_category='Acoustic Panel' for acoustic,
// settlement-review tag where flagged, image_url via per-number Meilisearch lookup, dw_sku LEFT NULL
// (assign-sku FROZEN), ON CONFLICT (pattern_name,color_name) DO NOTHING guard, id-scoped restore file.
// DISAMBIGUATE: stage new colorways of a normalized-collision pattern UNDER the existing staged raw
// spelling (canonical), so they join the existing pattern group instead of forking a whitespace variant.
// DROP-DUP: skip (exact or fuzzy Gray/Grey color match already staged).
// DRY-RUN by default; --commit writes.
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', 'd3b-heldreview-stage-restore.jsonl');
const RESULTS = path.join(HERE, 'data', 'd3b-heldreview-results.json');
const TABLE = path.join(HERE, 'data', 'd3b-heldreview-table.json');
const COMMIT = process.argv.includes('--commit');

const HOST = 'https://ms-e886719d86e7-4256.sfo.meilisearch.io';
const KEY = process.env.MOMENTUM_MS_KEY || '';
const INDEX = 'redesign-colors';
const SETTLEMENT = new Set(['Hula WC', 'Sea Line', 'Palomar', 'Oakstone']);
const COST_MULT = 0.80, HW_MULT = 1.448;
const KEEP = new Set(['Wallcovering', 'Acoustic']);

const pool = new Pool({ host: '/tmp', database: '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));
// fuzzy color key: lowercase, gray->grey, drop all non-alnum → catches Gray/Grey + spacing/punct variants
const fcolor = s => norm(s).replace(/gray/g, 'grey').replace(/[^a-z0-9]/g, '');

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 imageFor(number) {
  try {
    const r = await fetch(`${HOST}/indexes/${INDEX}/search`, {
      method: 'POST', headers: { Authorization: `Bearer ${KEY}`, 'Content-Type': 'application/json' },
      body: JSON.stringify({ q: String(number), limit: 5, attributesToRetrieve: ['number', 'medium_image_url', 'series', 'collections'] }),
    });
    if (!r.ok) return {};
    const hits = (await r.json()).hits || [];
    const h = hits.find(x => String(x.number) === String(number)) || hits[0];
    if (!h) return {};
    return { image: h.medium_image_url || null, series: h.series || (Array.isArray(h.collections) ? h.collections[0]?.name : null) || null };
  } catch { return {}; }
}

async function main() {
  const { rows: pr } = await pool.query('SELECT pattern_name, color_name FROM momentum_colorways');
  const stagedPatternNorm = new Set(pr.map(r => norm(r.pattern_name)));
  const stagedPairNorm = new Set(pr.map(r => norm(r.pattern_name) + '' + norm(r.color_name)));
  // per norm(pattern): canonical raw spelling (longest/first existing) + fuzzy color set
  const patRaw = new Map();      // normPattern -> Map(rawSpelling -> count)
  const patFcolors = new Map();  // normPattern -> Set(fuzzyColor)
  for (const r of pr) {
    const n = norm(r.pattern_name);
    if (!patRaw.has(n)) patRaw.set(n, new Map());
    patRaw.get(n).set(r.pattern_name, (patRaw.get(n).get(r.pattern_name) || 0) + 1);
    if (!patFcolors.has(n)) patFcolors.set(n, new Set());
    patFcolors.get(n).add(fcolor(r.color_name));
  }
  const canonicalRaw = n => {
    const m = patRaw.get(n); if (!m) return null;
    return [...m.entries()].sort((a, b) => b[1] - a[1] || a[0].length - b[0].length)[0][0];
  };

  const feed = readTSV();
  const inscope = feed.filter(r => KEEP.has(r.category_name) && (r.pattern_name || '').trim() && (r.preferred_color_name || '').trim());
  const seen = new Set(); const dedup = [];
  for (const r of inscope) { const k = r.pattern_name + '' + r.preferred_color_name; if (seen.has(k)) continue; seen.add(k); dedup.push(r); }

  // HELD extend-pattern GENUINELY-NEW set = norm(pattern) staged AND norm(pair) NOT staged
  const held = dedup.filter(r => stagedPatternNorm.has(norm(r.pattern_name)) && !stagedPairNorm.has(norm(r.pattern_name) + '' + norm(r.preferred_color_name)));

  const table = [];
  const toStage = [];
  for (const r of held) {
    const np = norm(r.pattern_name);
    const rawFeed = r.pattern_name;
    const canon = canonicalRaw(np);
    const disambiguate = canon !== rawFeed; // feed spelling differs from staged spelling (whitespace/case)
    const fc = fcolor(r.preferred_color_name);
    const fuzzyDup = patFcolors.get(np)?.has(fc);
    let verdict, reason, stagePattern = canon || rawFeed;
    if (fuzzyDup) {
      verdict = 'DROP-DUP';
      reason = `fuzzy-color match to an already-staged colorway of "${canon}" (spelling variant, e.g. Gray/Grey); fcolor="${fc}"`;
    } else {
      verdict = disambiguate ? 'DISAMBIGUATE' : 'STAGE-NEW';
      reason = disambiguate
        ? `genuinely-new colorway; feed pattern "${rawFeed}" collides on normalization with staged "${canon}" — stage under canonical staged spelling to avoid whitespace fork`
        : `genuinely-new colorway of already-staged pattern "${canon}"; no staged equivalent (norm pair + fuzzy color both absent)`;
      toStage.push({ r, stagePattern });
    }
    table.push({
      item: `${rawFeed.trim()} / ${r.preferred_color_name}`,
      number: r.number, category: r.category_name, bucket: 'extend-pattern',
      verdict, stage_pattern: verdict === 'DROP-DUP' ? null : stagePattern, reason,
    });
  }

  // Cavern II bucket (all exact-staged) + Rectangular Hatch/Linear/Louvre/Vault (0 feed) — collision-pattern rows
  // Cavern II rows are already TRUE-DUP (norm pair staged) so they never enter `held`; record them explicitly.
  const COLLISION = ['Cavern II', 'Rectangular Hatch', 'Rectangular Linear', 'Rectangular Louvre', 'Rectangular Vault', 'Rectangular Vertex 9/16'];
  const collisionSummary = [];
  for (const cp of COLLISION) {
    const cn = norm(cp);
    const feedRows = dedup.filter(r => norm(r.pattern_name) === cn);
    const staged = stagedPatternNorm.has(cn);
    const canon = canonicalRaw(cn);
    const newOnes = feedRows.filter(r => !stagedPairNorm.has(cn + '' + norm(r.preferred_color_name)) && !patFcolors.get(cn)?.has(fcolor(r.preferred_color_name)));
    let verdict;
    if (feedRows.length === 0) verdict = 'NO-OP (absent from current feed — nothing to onboard)';
    else if (newOnes.length === 0) verdict = 'DROP-DUP (all feed colorways already staged under canonical spelling)';
    else verdict = `DISAMBIGUATE (${newOnes.length} new colorways staged under canonical "${canon}")`;
    collisionSummary.push({ pattern: cp, norm: cn, feed_colorways: feedRows.length, pattern_staged: staged, canonical_staged_spelling: canon, new_colorways: newOnes.length, verdict });
  }

  const counts = {
    held_total: held.length,
    stage_new: table.filter(t => t.verdict === 'STAGE-NEW').length,
    disambiguate: table.filter(t => t.verdict === 'DISAMBIGUATE').length,
    drop_dup: table.filter(t => t.verdict === 'DROP-DUP').length,
  };
  console.log('== CLASSIFICATION ==');
  console.log(JSON.stringify(counts, null, 2));
  console.log('== COLLISION PATTERNS ==');
  console.table(collisionSummary);
  fs.writeFileSync(TABLE, JSON.stringify({ counts, table, collisionSummary }, null, 2));

  if (!COMMIT) { console.log(`\nDRY-RUN. toStage=${toStage.length}. Re-run with --commit.`); await pool.end(); return; }

  // enrich images (per-number, ≤79 lookups)
  for (const x of toStage) { const im = await imageFor(x.r.number); x.image = im.image || null; x.series = im.series || null; }
  const withImg = toStage.filter(x => x.image).length;
  console.log(`\nimage lookups: ${withImg}/${toStage.length} got image_url`);

  const c = await pool.connect();
  let inserted = 0, skipped = 0; const restore = [];
  try {
    await c.query('BEGIN');
    for (const x of toStage) {
      const r = x.r; const list = money(r.list_price);
      const tags = SETTLEMENT.has(r.pattern_name.trim()) ? 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`,
        [x.stagePattern, r.preferred_color_name, r.preferred_color_number || null, r.number || null,
         x.image || null, list, (r.base_width || '').trim() || null, r.category_name,
         (r.product_line_code || '').trim() || null, x.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: x.stagePattern, 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(), counts, attempted: toStage.length, inserted, guard_skips: skipped,
    final_rows: finalCount, restore_file: RESTORE, inserted_ids: restore.map(x => x.id) };
  fs.writeFileSync(RESULTS, JSON.stringify(results, null, 2));
  console.log(`COMMITTED — inserted ${inserted}, guard-skipped ${skipped}. final_rows=${finalCount}`);
  console.log(`Restore: ${RESTORE}`);
  await pool.end();
}
main().catch(e => { console.error(e); process.exit(1); });