← back to Consulting Designerwallcoverings Com

scripts/build-work-orders.mjs

180 lines

#!/usr/bin/env node
// Sample-only backlog WORK ORDERS builder.
// READ-ONLY against the local dw_unified mirror (psql over the /tmp socket).
// Never writes to any DW system. Output: data/work-orders/<vendor>.csv (one per
// vendor line), data/work-orders/index.json (machine-readable counts), and
// data/work-orders/SUMMARY.md (the human deliverable, top-8 first). Also registers
// the deliverable in data/media.json so it surfaces in the /admin media bucket.
//
// Backlog definition (matches scripts/collect-dw.mjs exactly):
//   status='ACTIVE'  AND  NOT has_product_variant  AND  tags NOT ILIKE '%quotes%'
//   => 22,952 non-quote sample-only actives: browseable + sampleable, not buyable.
import { execFileSync } from 'node:child_process';
import { writeFileSync, mkdirSync, readFileSync } from 'node:fs';
import { dirname, join } from 'node:path';
import { fileURLToPath } from 'node:url';

const HERE = dirname(fileURLToPath(import.meta.url));
const DATA = join(HERE, '..', 'data');
const OUT = join(DATA, 'work-orders');
mkdirSync(OUT, { recursive: true });

// NOTE on leak-safety: the mirror's `vendor` column is already the CUSTOMER-FACING
// DW-facing name (e.g. "Phillipe Romano", "Hollywood Wallcoverings"), never the
// upstream private-label real name — so vendor-keyed filenames/labels are clean by
// construction. We deliberately do NOT drop rows on a broad upstream-name regex: it
// false-matches legitimate customer vendors ("versa" -> "Versace") and would silently
// omit real remediation work + diverge from the 22,952 headline. This deliverable is
// internal, admin-gated ops tooling covering the full backlog set.

function sql(q) {
  const out = execFileSync('psql', ['host=/tmp dbname=dw_unified', '-tA', '-F', '\t', '-c', q],
    { encoding: 'utf8', timeout: 60000, maxBuffer: 64 * 1024 * 1024 });
  return out.replace(/\n$/, '').split('\n').filter(Boolean).map(r => r.split('\t'));
}

const WHERE = `status='ACTIVE' and not coalesce(has_product_variant,false) and tags not ilike '%quotes%'`;

// One pass: every backlog row with the fields a remediation tech needs.
// scrub() collapses tabs/newlines inside text fields so the tab-delimited,
// one-record-per-line psql output never mis-splits.
const scrub = (c) => `regexp_replace(coalesce(${c},''),'[\\t\\n\\r]+',' ','g')`;
const rows = sql(`select
    ${scrub('dw_sku')}, ${scrub('mfr_sku')}, coalesce(shopify_id,''),
    ${scrub('handle')}, ${scrub('title')}, ${scrub("coalesce(vendor,'(unassigned)')")},
    ${scrub("nullif(trim(product_type),'')")},
    coalesce(has_sample_variant,false), coalesce(has_description,false),
    (image_url is not null and image_url <> ''),
    (coalesce(cost,cost_price) is not null),
    coalesce(min_variant_price::text,''),
    coalesce(to_char(created_at_shopify,'YYYY-MM-DD'),'')
  from shopify_products where ${WHERE}
  order by vendor, created_at_shopify desc`);

// ---- CSV helpers ------------------------------------------------------------
const q = (v) => { const s = String(v ?? ''); return /[",\n]/.test(s) ? '"' + s.replace(/"/g, '""') + '"' : s; };
const slug = (v) => v.toLowerCase().replace(/&/g, 'and').replace(/[^a-z0-9]+/g, '-').replace(/^-+|-+$/g, '') || 'unassigned';
const HEADER = ['dw_sku','mfr_sku','shopify_id','handle','title','vendor','product_type',
  'has_sample_variant','has_description','has_image','has_cost','min_variant_price','created_at','remediation'];

function remediation(hasSample, hasDesc, hasImg, hasCost) {
  const parts = ['CREATE_SELLABLE_VARIANT'];       // every row: no buyable roll -> add one
  if (!hasCost)   parts.push('NEEDS_COST');         // no wholesale cost -> can't price the roll
  if (!hasImg)    parts.push('NEEDS_IMAGE');
  if (!hasDesc)   parts.push('NEEDS_DESCRIPTION');
  if (!hasSample) parts.push('NEEDS_SAMPLE_VARIANT');
  return parts.join(' | ');
}

// ---- group by vendor --------------------------------------------------------
const byVendor = new Map();
let malformed = 0;
for (const r of rows) {
  if (r.length < 13) { malformed++; continue; }
  const [dw_sku, mfr_sku, sid, handle, title, vendor, ptype, hs, hd, hi, hc, mvp, created] = r;
  const hasSample = hs === 't', hasDesc = hd === 't', hasImg = hi === 't', hasCost = hc === 't';
  if (!byVendor.has(vendor)) byVendor.set(vendor, []);
  byVendor.get(vendor).push([dw_sku, mfr_sku, sid, handle, title, vendor, ptype,
    hasSample, hasDesc, hasImg, hasCost, mvp, created,
    remediation(hasSample, hasDesc, hasImg, hasCost)]);
}

// ---- write one CSV per vendor + build the index -----------------------------
const index = [];
for (const [vendor, vrows] of byVendor) {
  const file = slug(vendor) + '.csv';
  const csv = [HEADER.join(',')].concat(vrows.map(vr => vr.map(q).join(','))).join('\n') + '\n';
  writeFileSync(join(OUT, file), csv);
  const missing = { cost: 0, image: 0, description: 0, sample: 0 };
  for (const vr of vrows) { if (!vr[10]) missing.cost++; if (!vr[9]) missing.image++; if (!vr[8]) missing.description++; if (!vr[7]) missing.sample++; }
  index.push({ vendor, file: 'work-orders/' + file, count: vrows.length, missing });
}
index.sort((a, b) => b.count - a.count);
const total = index.reduce((a, v) => a + v.count, 0);
const generated_at = new Date().toISOString();
writeFileSync(join(OUT, 'index.json'),
  JSON.stringify({ generated_at, definition: WHERE, total, vendor_count: index.length, malformed_rows: malformed, vendors: index }, null, 2));

// ---- SUMMARY.md (deliverable, top-8 first) ----------------------------------
const top8 = index.slice(0, 8);
const top8sum = top8.reduce((a, v) => a + v.count, 0);
const fmt = (n) => n.toLocaleString('en-US');
const pct = (n) => ((n / total) * 100).toFixed(1);
const md = `# Sample-only backlog — Work Orders

**Generated:** ${generated_at}
**Source:** read-only \`dw_unified\` mirror (\`psql host=/tmp dbname=dw_unified\`) · no Shopify writes
**Backlog definition:** \`${WHERE}\`

## The finding

**${fmt(total)}** ACTIVE products are **sample-only without a quote-only designation** — a
customer can browse them and order a $4.25 memo sample, but there is **no sellable
roll variant to buy**. Every one is paid-for merchandising with the register removed.
This is the single largest dormant-revenue pool in the catalog.

Spread across **${index.length}** vendor lines. The **top 8 lines carry ${fmt(top8sum)}
(${pct(top8sum)}%)** of the backlog — remediation should start there.

## Primary remediation

Each product needs a **sellable roll/product variant created** (the \`CREATE_SELLABLE_VARIANT\`
action in every row). A large majority already have a sample variant, a description, and a
featured image — so the dominant blocker is the **missing sellable variant**, and secondarily
the **missing wholesale cost** needed to price that variant. Per-row flags in each CSV:

| flag | meaning |
|---|---|
| \`CREATE_SELLABLE_VARIANT\` | no buyable roll variant exists — add one (all rows) |
| \`NEEDS_COST\` | no wholesale cost in the mirror — required before the roll can be priced |
| \`NEEDS_IMAGE\` | no featured image |
| \`NEEDS_DESCRIPTION\` | no product description |
| \`NEEDS_SAMPLE_VARIANT\` | not even the $4.25 memo sample exists |

## Top 8 lines (start here)

| # | Vendor line | Backlog | Needs cost | Needs image | Needs desc | Work order |
|--:|---|--:|--:|--:|--:|---|
${top8.map((v, i) => `| ${i + 1} | ${v.vendor} | ${fmt(v.count)} | ${fmt(v.missing.cost)} | ${fmt(v.missing.image)} | ${fmt(v.missing.description)} | \`${v.file}\` |`).join('\n')}

## All ${index.length} lines

| Vendor line | Backlog | Needs cost | Needs image | Needs desc | Work order |
|---|--:|--:|--:|--:|---|
${index.map(v => `| ${v.vendor} | ${fmt(v.count)} | ${fmt(v.missing.cost)} | ${fmt(v.missing.image)} | ${fmt(v.missing.description)} | \`${v.file}\` |`).join('\n')}

## Provenance — tiered honestly

- **Tier A (measured, catalog structure):** the counts above come straight from the read-only
  \`dw_unified\` mirror — every ACTIVE product's \`has_product_variant\` / \`has_sample_variant\` /
  \`has_description\` / \`image_url\` / cost flags and the \`quotes\` tag. Solid.
- **Tier B (proxy, labeled):** "backlog" excludes lines tagged \`quotes\`. That tag under-captures
  reality — some contract lines (e.g. Koroseal, Phillip Jeffries) are quote-only *in practice* but
  untagged — so **${fmt(total)} is an upper bound**; the truly variant-recoverable share is a
  subset weighted toward the merchant private-label and residential lines.
- **Tier C (absent):** whether a given product *should* get a priced roll variant vs. stay
  sample/quote-only is a **merchandising decision per line**, not derivable from the mirror.
  Execution is gated for that reason (see the pending-approval memo).

## Execution is gated

Actually creating the ${fmt(total)} sellable variants is a **customer-facing Shopify write** and is
**NOT executed here**. The execution memo with per-vendor counts is drafted to
\`~/.claude/yolo-queue/pending-approval/\` for Steve's review + vp-dw-commerce sign-off.
`;
writeFileSync(join(OUT, 'SUMMARY.md'), md);

// ---- register in the /admin media bucket ------------------------------------
const mediaPath = join(DATA, 'media.json');
let media = []; try { media = JSON.parse(readFileSync(mediaPath, 'utf8')); } catch { media = []; }
media = media.filter(m => m.kind !== 'work-order-backlog'); // idempotent
media.push({
  kind: 'work-order-backlog',
  label: `Sample-only backlog — work orders (${fmt(total)} products, ${index.length} lines)`,
  url: '/work-orders/SUMMARY.md',
  note: `Per-vendor remediation CSVs for the ${fmt(total)} non-quote sample-only actives. Top 8 lines = ${fmt(top8sum)}. Generated ${generated_at.slice(0, 10)} read-only from the dw_unified mirror. Execution gated.`,
});
writeFileSync(mediaPath, JSON.stringify(media, null, 2));

console.log(JSON.stringify({ ok: true, total, vendor_count: index.length, top8: top8.map(v => ({ vendor: v.vendor, count: v.count })), top8sum, malformed }, null, 2));