`classify.mjs`

89 lines
#!/usr/bin/env node
// TK-10875 — unbuyable-catalog classification (READ-ONLY).
// Writes data/classification-summary.json. Makes ZERO writes to any DB.

import { writeFileSync } from 'node:fs';
import { fileURLToPath } from 'node:url';
import { dirname, join } from 'node:path';
import { queryOne, queryRows } from './lib/db.mjs';

const HERE = dirname(fileURLToPath(import.meta.url));

const UNBUYABLE = `lower(status)='active' AND (has_product_variant IS DISTINCT FROM true)`;

// "Single priced variant" signature: variant_count=1 with a non-sample SKU priced
// above the $4.25 floor. In the mirror this LOOKS like "already sellable", but a
// live-Shopify check (2026-08-30) proved the opposite for the Wolf Gordon cohort:
// these are the RR-defect (a lone variant labeled option1='Sample' but priced at
// roll retail). The mirror CANNOT distinguish the two — it carries no option1 —
// so this count is "needs live classification", not a recovery verdict.
const SINGLE_PRICED = `variant_count = 1 AND variant_sku IS NOT NULL AND variant_sku NOT ILIKE '%-sample' AND COALESCE(min_variant_price,0) > 4.50`;
const SAMPLE_ONLY   = `NOT (${SINGLE_PRICED})`;

// 1) Headline
const headline = queryOne(`
  SELECT
    count(*)                                        AS unbuyable_total,
    count(*) FILTER (WHERE ${SINGLE_PRICED})        AS single_priced_variant_needs_live_check,
    count(*) FILTER (WHERE ${SAMPLE_ONLY})          AS sample_only_mirror,
    count(*) FILTER (WHERE has_product_variant IS NULL) AS null_flag
  FROM shopify_products WHERE ${UNBUYABLE}`);

// 2) Vendor breakdown (top 25)
const vendors = queryRows(`
  SELECT vendor, count(*) AS unbuyable,
         round(100.0*count(*)/sum(count(*)) over (),1) AS pct
  FROM shopify_products WHERE ${UNBUYABLE}
  GROUP BY vendor ORDER BY count(*) DESC LIMIT 25`);

// 3) Join-validated staging-cost recoverability for the 3 cohorts the prior
//    memo flagged as "quick wins". Only Wolf Gordon survives the real join.
const cohorts = [
  { vendor: 'Wolf Gordon',     table: 'wolf_gordon_catalog', cost: 'price_trade' },
  { vendor: 'Coordonné',       table: 'coordonne_catalog',   cost: 'price_trade' },
  { vendor: 'Malibu Wallpaper',table: 'wallquest_catalog',   cost: 'COALESCE(net_cost,price_trade)' },
];
const recoverable = cohorts.map(({ vendor, table, cost }) => {
  const row = queryOne(`
    WITH ub AS (SELECT id, sku, variant_count, variant_sku, min_variant_price FROM shopify_products
                WHERE vendor='${vendor}' AND ${UNBUYABLE})
    SELECT (SELECT count(*) FROM ub) AS unbuyable,
           count(DISTINCT ub.id) FILTER (WHERE (${cost}) > 0) AS cost_joined
    FROM ub LEFT JOIN ${table} c ON c.dw_sku = ub.sku`);
  return { vendor, staging_table: table, cost_column: cost,
           unbuyable: Number(row.unbuyable),
           cost_joined: Number(row.cost_joined),
           // Wolf Gordon's cost_joined cohort was live-verified as RR-defect →
           // recoverable via variant restructure (GATED). Others unverified.
           recovery: vendor === 'Wolf Gordon'
             ? 'live-verified RR-defect → recoverable via restructure (GATED)'
             : (vendor === 'Coordonné' ? 'blocked: DWCO2↔DWDC prefix + empty trade cost'
                                        : 'no staging join (CDA line ≠ wallquest DWQW)') };
});

const summary = {
  ticket: 'TK-10875',
  generated_at: new Date().toISOString(),
  source: 'dw_unified mirror (local /tmp socket) — READ-ONLY',
  definition_unbuyable: "status='active' AND has_product_variant IS NOT TRUE",
  headline: {
    unbuyable_total: Number(headline.unbuyable_total),
    single_priced_variant_needs_live_check: Number(headline.single_priced_variant_needs_live_check),
    sample_only_mirror: Number(headline.sample_only_mirror),
    null_flag: Number(headline.null_flag),
  },
  top_vendors: vendors.map(v => ({ vendor: v.vendor, unbuyable: Number(v.unbuyable), pct: Number(v.pct) })),
  join_validated_recoverable: recoverable,
  finding: 'Three-layer result. (1) JOIN-KEY SWAP: working join is shopify.sku = <vendor>_catalog.dw_sku, not dw_sku↔dw_sku (yields 0) — why prior cycles used unreliable table-totals. (2) MIRROR CANNOT CLASSIFY: it has no variant option1 and is price-stale (dead dw-shopify-products-sync-hourly, 13d). (3) LIVE-VERIFIED (2026-08-30): the WG 224 cost-joined cohort is the RR-defect — a lone option1=Sample variant priced at ROLL RETAIL (not $4.25), no real sample, no roll. They ARE recoverable via the proven TK-10875-130 variant-restructure (relabel Sample→Sold Per Roll @ trade/0.65/0.85 + add $4.25 Sample), reversible+ledgered — but it is a GATED customer-facing write. Coordonné/Malibu do not join. Executor MUST read live Shopify + recompute retail from staging (never the stale mirror price).',
};

const out = join(HERE, 'data', 'classification-summary.json');
writeFileSync(out, JSON.stringify(summary, null, 2));
console.log(`[classify] unbuyable=${summary.headline.unbuyable_total}  ` +
  `single-priced(needs-live-check)=${summary.headline.single_priced_variant_needs_live_check}  ` +
  `sample-only(mirror)=${summary.headline.sample_only_mirror}`);
console.log(`[classify] cohorts (cost_joined / unbuyable): ` +
  recoverable.map(r=>`${r.vendor}:${r.cost_joined}/${r.unbuyable}`).join('  '));
console.log(`[classify] WG 224 = live-verified RR-defect → recoverable via restructure (GATED)`);
console.log(`[classify] wrote ${out}`);