← back to Harlequin Sample Price Analysis

scripts/analyze-harlequin.mjs

121 lines

#!/usr/bin/env node
// analyze-harlequin.mjs — READ-ONLY Harlequin missing-sample + price analysis (TK-10870).
//
// Reads the on-Shopify Harlequin cohort from the dw_unified mirror via `psql`,
// applies the pure pricing logic in ./pricing.mjs, and writes:
//   artifacts/harlequin-analysis.json   (machine-readable)
//   artifacts/harlequin-analysis.md     (human report)
//
// SAFETY: this NEVER writes to dw_unified or Shopify.
//   * the SQL is asserted SELECT-only before execution (regex guard);
//   * the psql session is forced read-only via PGOPTIONS=default_transaction_read_only=on,
//     so any accidental write statement would be rejected by the server.
//
// Usage:
//   node scripts/analyze-harlequin.mjs                 # live read from the mirror
//   node scripts/analyze-harlequin.mjs --from-json f   # offline, from a saved export
//
import { execFileSync } from 'node:child_process';
import { writeFileSync, readFileSync, mkdirSync } from 'node:fs';
import { fileURLToPath } from 'node:url';
import { dirname, join } from 'node:path';
import { summarize, SAMPLE_PRICE } from './pricing.mjs';

const __dir = dirname(fileURLToPath(import.meta.url));
const ROOT = join(__dir, '..');
const ART = join(ROOT, 'artifacts');

// The one and only query. Kept as a constant so the SELECT-only guard is meaningful.
const QUERY = `
  SELECT dw_sku, mfr_sku, pattern_name, color_name, collection,
         price_retail, price_trade, tariff_amount_per_roll,
         on_shopify, discontinued, shopify_product_id
  FROM harlequin_catalog
  WHERE on_shopify = true
  ORDER BY mfr_sku`;

function assertSelectOnly(sql) {
  const stripped = sql.replace(/\s+/g, ' ').trim().toLowerCase();
  if (!stripped.startsWith('select')) throw new Error('refusing: query is not a SELECT');
  if (/;\s*\S/.test(sql)) throw new Error('refusing: multiple statements');
  if (/\b(insert|update|delete|drop|alter|truncate|create|grant|copy)\b/.test(stripped))
    throw new Error('refusing: write keyword detected');
}

function readLive() {
  assertSelectOnly(QUERY);
  const wrapped = `SELECT coalesce(json_agg(t), '[]') FROM (${QUERY}) t`;
  const out = execFileSync('psql', ['-tAc', wrapped], {
    env: {
      ...process.env,
      PGHOST: process.env.PGHOST || '/tmp',
      PGDATABASE: process.env.PGDATABASE || 'dw_unified',
      PGOPTIONS: '-c default_transaction_read_only=on', // server rejects any write
    },
    encoding: 'utf8',
    maxBuffer: 32 * 1024 * 1024,
  });
  return JSON.parse(out.trim() || '[]');
}

function main() {
  const fromJsonIdx = process.argv.indexOf('--from-json');
  const rows = fromJsonIdx >= 0
    ? JSON.parse(readFileSync(process.argv[fromJsonIdx + 1], 'utf8'))
    : readLive();

  const s = summarize(rows);
  const costGaps = s.classified.filter((r) => r.costGap);
  const dwskuGaps = s.classified.filter((r) => r.dwskuGap);

  const report = {
    ticket: 'TK-10870',
    generated_utc: new Date().toISOString(),
    source: 'dw_unified.harlequin_catalog (read-only mirror)',
    cohort: 'on_shopify = true',
    headline: {
      on_shopify_total: s.total,
      sell_price_computable: s.computable,
      cost_gap: s.costGap,
      dw_sku_gap: s.dwskuGap,
      retail_placeholder_150: s.retailPlaceholder,
      retail_matches_formula: s.formulaMatch,
      below_map_floor_2x: s.belowMapFloor,
      sample_price: SAMPLE_PRICE,
    },
    cost_gap_rows: costGaps.map((r) => ({ dw_sku: r.dw_sku, mfr_sku: r.mfr_sku })),
    dw_sku_gap_rows: dwskuGaps.map((r) => ({ mfr_sku: r.mfr_sku, computedRetail: r.computedRetail })),
    rows: s.classified,
  };

  mkdirSync(ART, { recursive: true });
  writeFileSync(join(ART, 'harlequin-analysis.json'), JSON.stringify(report, null, 2));

  const md = [
    `# Harlequin missing-sample + price analysis (TK-10870)`,
    ``,
    `Generated: ${report.generated_utc}  ·  Source: ${report.source}  ·  Cohort: \`${report.cohort}\``,
    ``,
    `| metric | value |`,
    `| --- | ---: |`,
    ...Object.entries(report.headline).map(([k, v]) => `| ${k} | ${v} |`),
    ``,
    `## Cost-gap rows (sell price NOT computable — cost follow-up needed)`,
    costGaps.length
      ? costGaps.map((r) => `- \`${r.mfr_sku}\` (dw_sku ${r.dw_sku ?? 'NULL'})`).join('\n')
      : '- none',
    ``,
    `## Null dw_sku rows (priced, but missing the DW SKU)`,
    dwskuGaps.length
      ? dwskuGaps.map((r) => `- \`${r.mfr_sku}\` → computed retail $${r.computedRetail}`).join('\n')
      : '- none',
    ``,
  ].join('\n');
  writeFileSync(join(ART, 'harlequin-analysis.md'), md);

  console.log(JSON.stringify(report.headline, null, 2));
  console.error(`\nwrote artifacts/harlequin-analysis.json + .md  (${s.total} rows)`);
}

main();