`tk10873/build_zoffany_optionA_draft.mjs`

140 lines
#!/usr/bin/env node
// build_zoffany_optionA_draft.mjs — TK-10873 Stage S3, EPIC TK-10874 (SDG family onboard).
// READ-ONLY; NO SHOPIFY/DB WRITES. Only SELECTs against the local dw_unified mirror
// (host=/tmp socket) + writes LOCAL artifact files under tk10873/. It NEVER issues an
// UPDATE/INSERT/DELETE or any Shopify call. The live-publish is a SEPARATE, Steve-gated step.
//
// Emits the Option-A draft plan for all 331 ACTIVE Zoffany products:
//   REPRICE          — has sellable variant + staged price_retail>0 -> proposed = price_retail
//   SELLABLE_ADD     — missing sellable variant (DWWC-502880 outlier) + usable staged price
//   HELD_NEEDS_PRICE — joins but price null/0, OR no staging join, OR outlier w/o usable price
// Sample variant stays 4.25 on every product (Option-A contract).
import fs from 'node:fs';
import { execFileSync } from 'node:child_process';
import { buildPlanRow, SAMPLE_PRICE } from './lib.mjs';

const DIR = new URL('.', import.meta.url).pathname;
const JSON_OUT = `${DIR}zoffany_optionA_draft.json`;
const CSV_OUT = `${DIR}zoffany_optionA_draft.csv`;
const SUMMARY_OUT = `${DIR}zoffany_optionA_summary.json`;

// READ-ONLY select. Same psql idiom as scripts/load_feed_pricing.mjs.
// LEFT JOIN keeps all 331 active rows even when staging has no mfr_sku match (join=0).
const SQL = `
COPY (
  SELECT
    l.mfr_sku,
    l.dw_sku            AS live_dw_sku,
    (z.mfr_sku IS NOT NULL) AS joined,
    l.has_product_variant,
    l.has_sample_variant,
    l.variant_count,
    z.dw_sku            AS staged_dw_sku,
    z.price_trade,
    z.price_retail,
    z.our_price,
    z.tariff_amount_per_roll AS tariff
  FROM shopify_products l
  LEFT JOIN zoffany_catalog z ON z.mfr_sku = l.mfr_sku
  WHERE l.vendor ILIKE 'zoffany%' AND l.status = 'ACTIVE'
  ORDER BY l.mfr_sku
) TO STDOUT WITH (FORMAT csv, HEADER true);
`;

function query() {
  const out = execFileSync('psql', ['-h', '/tmp', '-d', 'dw_unified', '-tAc', SQL], { encoding: 'utf8' });
  const lines = out.split('\n').filter(Boolean);
  const header = lines.shift().split(',');
  return lines.map(line => {
    // simple CSV parse — SAFE ONLY because every SELECTed column here is a sku /
    // boolean / numeric with ZERO embedded commas or quotes (verified against the
    // live data). If you ever add a text column (pattern/color/note) to the SELECT,
    // replace this with a real CSV parser — psql COPY quotes such fields and the
    // naive split would misalign.
    const cells = line.split(',');
    const rec = {};
    header.forEach((h, i) => { rec[h] = cells[i] === '' ? null : cells[i]; });
    return {
      mfr_sku: rec.mfr_sku,
      live_dw_sku: rec.live_dw_sku,
      joined: rec.joined === 't' || rec.joined === 'true',
      has_product_variant: rec.has_product_variant === 't' || rec.has_product_variant === 'true',
      has_sample_variant: rec.has_sample_variant === 't' || rec.has_sample_variant === 'true',
      variant_count: rec.variant_count == null ? null : Number(rec.variant_count),
      staged_dw_sku: rec.staged_dw_sku,
      price_trade: rec.price_trade,
      price_retail: rec.price_retail,
      our_price: rec.our_price,
      tariff: rec.tariff,
    };
  });
}

function csvField(v) {
  if (v == null) return '';
  const s = String(v);
  return /[",\n]/.test(s) ? `"${s.replace(/"/g, '""')}"` : s;
}

function main() {
  const recs = query();
  const plan = recs.map(buildPlanRow);

  // JSON (full plan)
  fs.writeFileSync(JSON_OUT, JSON.stringify(plan, null, 2));

  // CSV
  const cols = ['mfr_sku', 'dw_sku', 'action', 'live_has_sellable', 'live_has_sample',
    'staged_trade', 'proposed_sellable_price', 'sample_price', 'note'];
  const csvLines = [cols.join(',')];
  for (const p of plan) {
    csvLines.push([
      csvField(p.mfr_sku),
      csvField(p.live_dw_sku || p.staged_dw_sku),
      csvField(p.action),
      csvField(p.has_product_variant),
      csvField(p.has_sample_variant),
      csvField(p.price_trade),
      csvField(p.proposed_sellable_price),
      csvField(p.sample_price),
      csvField(p.note),
    ].join(','));
  }
  fs.writeFileSync(CSV_OUT, csvLines.join('\n') + '\n');

  // Summary
  const counts = { REPRICE: 0, SELLABLE_ADD: 0, HELD_NEEDS_PRICE: 0 };
  let joined = 0, priced = 0, missingSellable = 0;
  for (const p of plan) {
    counts[p.action] = (counts[p.action] || 0) + 1;
    if (p.joined) joined++;
    if (Number(p.price_retail) > 0) priced++;
    if (!p.has_product_variant) missingSellable++;
  }
  const summary = {
    ticket: 'TK-10873',
    epic: 'TK-10874',
    generated_at: new Date().toISOString(),
    read_only: true,
    vendor: 'Zoffany',
    active_total: plan.length,
    joined_staging: joined,
    priced_from_staging: priced,
    missing_sellable_variant: missingSellable,
    sample_price: SAMPLE_PRICE,
    counts,
    expected: { active: 331, joined: 330, priced: 328, missing_sellable: 1 },
  };
  fs.writeFileSync(SUMMARY_OUT, JSON.stringify(summary, null, 2));

  // stdout table
  console.log('=== Zoffany Option-A Draft (READ-ONLY, no writes) ===');
  console.log(`active_total=${plan.length}  joined=${joined}  priced=${priced}  missing_sellable=${missingSellable}`);
  console.log('action              count');
  console.log('------------------  -----');
  for (const [k, v] of Object.entries(counts)) console.log(`${k.padEnd(18)}  ${v}`);
  console.log(`\nartifacts:\n  ${JSON_OUT}\n  ${CSV_OUT}\n  ${SUMMARY_OUT}`);
}

main();