← back to Harlequin Sample Price Analysis

scripts/pricing.mjs

165 lines

// pricing.mjs — PURE, dependency-free Harlequin pricing + row-classification logic.
// No DB, no network, no side effects. Unit-tested by tests/pricing.test.mjs.
//
// Context (TK-10870): the Harlequin per-roll DW sell price is computed with the
// DW-standard markup  retail = cost / 0.65 / 0.85  (== cost * 1.8100...).
// Verified against the live dw_unified mirror on 2026-08-30: 732/842 catalog rows
// and 31/32 on-Shopify rows have  round(price_retail / price_trade, 3) == 1.810.
//
// This module is READ-ONLY analysis support. It NEVER writes to Shopify or dw_unified.

/** DW standard markup divisors applied to net cost (wholesale). */
export const DW_MARKUP_DIVISORS = [0.65, 0.85];

/** Fixed memo-sample price on the DW store. */
export const SAMPLE_PRICE = 4.25;

/** The flat placeholder retail a row carries before a real cost is harvested. */
export const RETAIL_PLACEHOLDER = 150.0;

/**
 * Hypothesised vendor MAP / minimum-sell floor multiple, from the epic's S1
 * coordinator note "SSP=2xTRADE". Used only to FLAG rows whose computed retail
 * falls below 2x cost — surfaced for Steve, never auto-applied.
 */
export const MAP_FLOOR_MULTIPLE = 2.0;

/** Round to cents the way the pipeline does (half-up on 2 decimals). */
export function roundCents(n) {
  return Math.round((n + Number.EPSILON) * 100) / 100;
}

/**
 * Compute the DW sell price from a net cost.
 * @param {number} cost net/wholesale cost per single roll
 * @returns {number|null} rounded retail, or null when cost is missing/non-positive
 */
export function computeRetail(cost) {
  if (cost == null || !(cost > 0)) return null;
  const retail = DW_MARKUP_DIVISORS.reduce((acc, d) => acc / d, cost);
  return roundCents(retail);
}

/** The MAP floor (2x cost) hypothesis, or null when cost is missing. */
export function computeMapFloor(cost) {
  if (cost == null || !(cost > 0)) return null;
  return roundCents(cost * MAP_FLOOR_MULTIPLE);
}

/**
 * True when the stored retail equals the formula-derived retail (to the cent).
 * Tolerance of half a cent absorbs rounding-order differences.
 */
export function retailMatchesFormula(cost, storedRetail) {
  const derived = computeRetail(cost);
  if (derived == null || storedRetail == null) return false;
  return Math.abs(derived - Number(storedRetail)) <= 0.005;
}

/**
 * Classify one catalog row for the missing-sample + price analysis.
 * Input row keys: dw_sku, mfr_sku, price_trade (cost), price_retail, on_shopify.
 * Returns a plain object of booleans + derived numbers. Pure — no I/O.
 */
export function classifyRow(row) {
  const cost = row.price_trade == null ? null : Number(row.price_trade);
  const storedRetail = row.price_retail == null ? null : Number(row.price_retail);

  const costGap = cost == null || !(cost > 0);
  const dwskuGap = row.dw_sku == null || String(row.dw_sku).trim() === '';
  const retailPlaceholder = storedRetail != null && Math.abs(storedRetail - RETAIL_PLACEHOLDER) <= 0.005;

  const computedRetail = computeRetail(cost);
  const mapFloor = computeMapFloor(cost);
  const formulaMatch = retailMatchesFormula(cost, storedRetail);
  const belowMapFloor = computedRetail != null && mapFloor != null && computedRetail < mapFloor;

  return {
    dw_sku: row.dw_sku ?? null,
    mfr_sku: row.mfr_sku ?? null,
    cost,
    storedRetail,
    computedRetail,       // null when costGap
    mapFloor,             // null when costGap
    sellPriceComputable: !costGap,
    costGap,
    dwskuGap,
    retailPlaceholder,
    formulaMatch,
    belowMapFloor,
  };
}

/**
 * Extract the Harlequin PATTERN code from an mfr_sku. Harlequin codes are
 * "...-hawNNNN-CC" where hawNNNN identifies the PATTERN and -CC the colorway
 * (e.g. cranes-in-flight-marine-haw0065-05 → "haw0065", shared by every Cranes In
 * Flight colorway). This is the robust grouping key for sibling-cost inference —
 * colorword slugs differ per colorway and cannot be used to group.
 * Falls back to the full lowercase slug when no HAW code is present.
 */
export function patternKeyFromMfrSku(mfrSku) {
  if (!mfrSku) return null;
  const m = String(mfrSku).toLowerCase().match(/(haw\d+)-\d+$/);
  return m ? m[1] : String(mfrSku).toLowerCase();
}

/**
 * Detect an obviously-junk mfr_sku value (a boolean literal or a null-ish token leaked
 * into the manufacturer-SKU field). Used to characterise the fleet-wide shared-mfr_sku
 * class honestly — a shared 'TRUE'/'York' value is junk-leakage, NOT a Cranes-style
 * product-identity collision. Pure; case-insensitive.
 */
export function isJunkMfrSku(v) {
  if (v == null) return true;
  const s = String(v).trim().toLowerCase();
  return s === '' || ['true', 'false', 'null', 'none', 'n/a', 'na', '-', '#ref!', '0'].includes(s);
}

/**
 * Read-only sibling-cost CANDIDATE inference.
 * Given all rows of a pattern's colorways, when the target row's cost is missing,
 * return the modal (most common) sibling cost as a CANDIDATE — never a confirmed cost.
 * Wallcovering colorways of one pattern normally share a wholesale cost, so this is a
 * strong hint to CONFIRM against a vendor list, not a value to write blindly.
 * @returns {{candidateCost:number|null, agreeCount:number, siblingCount:number, confident:boolean}}
 */
export function inferSiblingCostCandidate(rows, targetMfrSku) {
  const key = patternKeyFromMfrSku(targetMfrSku);
  const siblings = rows.filter(
    (r) => patternKeyFromMfrSku(r.mfr_sku) === key &&
           r.mfr_sku !== targetMfrSku &&
           r.price_trade != null && Number(r.price_trade) > 0
  );
  if (!siblings.length) return { candidateCost: null, agreeCount: 0, siblingCount: 0, confident: false };
  const tally = new Map();
  for (const s of siblings) {
    const c = Number(s.price_trade);
    tally.set(c, (tally.get(c) || 0) + 1);
  }
  let candidateCost = null, agreeCount = 0;
  for (const [c, n] of tally) if (n > agreeCount) { candidateCost = c; agreeCount = n; }
  // Confident only when ALL priced siblings agree and there are >= 2 of them.
  const confident = agreeCount === siblings.length && siblings.length >= 2;
  return { candidateCost, agreeCount, siblingCount: siblings.length, confident };
}

/**
 * Summarise a set of classified rows into headline counts.
 * @param {Array} rows raw catalog rows
 */
export function summarize(rows) {
  const classified = rows.map(classifyRow);
  const count = (pred) => classified.filter(pred).length;
  return {
    total: classified.length,
    computable: count((r) => r.sellPriceComputable),
    costGap: count((r) => r.costGap),
    dwskuGap: count((r) => r.dwskuGap),
    retailPlaceholder: count((r) => r.retailPlaceholder),
    formulaMatch: count((r) => r.formulaMatch),
    belowMapFloor: count((r) => r.belowMapFloor),
    classified,
  };
}