`halve-preview.js`

166 lines
#!/usr/bin/env node
/*
 * halve-preview.js  —  READ-ONLY dry-run preview for the "cut retail prices in half" request.
 *
 * Reads the LOCAL dw_unified mirror (shopify_products) via psql, NEVER writes anything,
 * NEVER touches Shopify, NEVER touches the Kamatera-canonical DB. Emits a CSV with:
 *   dw_sku, vendor, current_retail, halved_retail, sample_price, excluded_reason
 *
 * Rules baked in:
 *   - Kravet-umbrella brands are MAP-priced -> halving breaches MAP -> excluded_reason=MAP
 *   - Any row whose halved price would be 0/null -> excluded_reason=zero_price_guard
 *   - Eligible rows: halved_retail = round(current_retail / 2, 2)
 *
 * Usage:
 *   node halve-preview.js [--status ACTIVE] [--vendor "Thibaut"]... [--limit 50]
 *                         [--price-col price|retail_price|min_variant_price]
 *                         [--out /abs/path.csv]
 *
 * Cost: $0 (local psql + node, no paid API).
 */

const { execFileSync } = require('child_process');
const fs = require('fs');

// ---- Kravet-umbrella MAP exclusion (case-insensitive, word-anchored) -------
// Matches the actual vendor strings seen in dw_unified. Deliberately precise so
// "Home Couture" (NOT Kravet) does not get swept in by a bare /couture/.
const MAP_PATTERNS = [
  /\bkravet\b/i,                    // Kravet, Kravet Couture, Kravet Design, Kravet Contract, Kravet Basics
  /\blee\s+jofa\b/i,                // Lee Jofa, Lee Jofa Modern
  /\bgroundworks?\b/i,              // Groundworks
  /\bbrunschwig\b/i,                // Brunschwig & Fils
  /\bcole\s*(?:&|and)\s*son\b/i,    // Cole & Son
  /\bgp\s*(?:&|and)\s*j\s*baker\b/i,// GP & J Baker
  /\bcolefax\b/i,                   // Colefax and Fowler
  /\bclarke\s*(?:&|and)\s*clarke\b/i,// Clarke & Clarke / Clarke And Clarke
  /\bmulberry\b/i,                  // Mulberry (Home)
  /\bthreads\b/i,                   // Threads
  /\bbaker\s+lifestyle\b/i,         // Baker Lifestyle
  /\bandrew\s+martin\b/i,           // Andrew Martin
  /\bnicolette\s+mayer\b/i,         // Nicolette Mayer (also hard-archived)
  /\baerin\b/i,                     // Aerin
  /\bbarclay\s+butera\b/i,          // Barclay Butera
  /\bthom\s+filicia\b/i,            // Thom Filicia
];

function isMapVendor(vendor) {
  if (!vendor) return false;
  return MAP_PATTERNS.some((re) => re.test(vendor));
}

const SAMPLE_PRICE = '4.25'; // DW standard sample price (the {DW_SKU}-Sample variant)

// ---- arg parsing -----------------------------------------------------------
const args = process.argv.slice(2);
const opt = { status: 'ACTIVE', vendors: [], limit: null, priceCol: 'price', out: null, order: 'vendor' };
for (let i = 0; i < args.length; i++) {
  const a = args[i];
  if (a === '--status') opt.status = args[++i];
  else if (a === '--vendor') opt.vendors.push(args[++i]);
  else if (a === '--limit') opt.limit = parseInt(args[++i], 10);
  else if (a === '--price-col') opt.priceCol = args[++i];
  else if (a === '--out') opt.out = args[++i];
  else if (a === '--order') opt.order = args[++i];
  else if (a === '--help' || a === '-h') {
    console.log('Usage: node halve-preview.js [--status ACTIVE] [--vendor X]... [--limit N] [--price-col price|retail_price|min_variant_price] [--out file.csv]');
    process.exit(0);
  }
}
const allowedCols = new Set(['price', 'retail_price', 'min_variant_price']);
if (!allowedCols.has(opt.priceCol)) {
  console.error(`ERROR: --price-col must be one of ${[...allowedCols].join(', ')}`);
  process.exit(2);
}

// ---- build the READ-ONLY query --------------------------------------------
// Parameterized via psql -v to avoid injection; status + vendor list are the
// only dynamic inputs. We pull one extra row past --limit only conceptually;
// here we just LIMIT in SQL.
// Single-quote-escape a SQL string literal. Inputs are trusted internal
// vendor/status strings (CLI args from Steve/officer), never web input.
const q = (s) => "'" + String(s).replace(/'/g, "''") + "'";

const conds = [`status = ${q(opt.status)}`];
if (opt.vendors.length) {
  conds.push(`vendor IN (${opt.vendors.map(q).join(', ')})`);
}
const limitClause = opt.limit ? `LIMIT ${parseInt(opt.limit, 10)}` : '';

const sql = `
  SELECT
    coalesce(dw_sku, sku, variant_sku, '') AS dw_sku,
    coalesce(vendor, '')                   AS vendor,
    ${opt.priceCol}                        AS current_retail
  FROM shopify_products
  WHERE ${conds.join(' AND ')}
  ORDER BY ${opt.order === 'random' ? 'random()' : 'vendor, dw_sku'}
  ${limitClause};
`;

const psqlArgs = ['-d', 'dw_unified', '-F', '\t', '-A', '-t', '--no-align', '-c', sql];

let raw;
try {
  raw = execFileSync('psql', psqlArgs, { encoding: 'utf8', maxBuffer: 1024 * 1024 * 512 });
} catch (e) {
  console.error('psql read failed:', e.message);
  process.exit(1);
}

// ---- transform -------------------------------------------------------------
function round2(n) { return Math.round((n + Number.EPSILON) * 100) / 100; }
function csvField(s) {
  s = (s === null || s === undefined) ? '' : String(s);
  return /[",\n]/.test(s) ? `"${s.replace(/"/g, '""')}"` : s;
}

const rows = raw.split('\n').filter((l) => l.length > 0);
const out = [['dw_sku', 'vendor', 'current_retail', 'halved_retail', 'sample_price', 'excluded_reason']];
let nEligible = 0, nMap = 0, nZero = 0;

for (const line of rows) {
  const [dw_sku, vendor, currentRaw] = line.split('\t');
  const current = currentRaw === '' || currentRaw === undefined ? null : Number(currentRaw);
  let halved = '';
  let reason = '';

  if (isMapVendor(vendor)) {
    reason = 'MAP';
    nMap++;
  } else if (current === null || !isFinite(current) || current <= 0 || round2(current / 2) <= 0) {
    reason = 'zero_price_guard';
    nZero++;
  } else {
    halved = round2(current / 2).toFixed(2);
    nEligible++;
  }

  out.push([
    dw_sku,
    vendor,
    current === null || !isFinite(current) ? '' : current.toFixed(2),
    halved,
    SAMPLE_PRICE,
    reason,
  ]);
}

const csv = out.map((r) => r.map(csvField).join(',')).join('\n') + '\n';
if (opt.out) {
  fs.writeFileSync(opt.out, csv);
  console.error(`Wrote ${rows.length} rows -> ${opt.out}`);
} else {
  process.stdout.write(csv);
}

// ---- summary to stderr (so stdout stays a clean CSV when piped) -----------
console.error('--- summary ---');
console.error(`price-col (current_retail source): ${opt.priceCol}`);
console.error(`status scope: ${opt.status}` + (opt.vendors.length ? ` | vendors: ${opt.vendors.join(', ')}` : ' | all vendors') + (opt.limit ? ` | limit ${opt.limit}` : ''));
console.error(`total rows:        ${rows.length}`);
console.error(`eligible:          ${nEligible}`);
console.error(`excluded MAP:      ${nMap}`);
console.error(`zero_price_guard:  ${nZero}`);
console.error('cost: $0 (local)');