`scripts/april-restore-plan/build-april-restore-plan.mjs`

116 lines
// build-april-restore-plan — REPORT-ONLY staging plan for the April 2026 wrongful
// mass-archive. Produces a tiered, batched JSON Steve green-lights; writes NOTHING
// to Shopify. The standing veto forbids autonomous restore — this only PLANS it.
//
// Cohort: products archived 2026-04-17/18 (the wrongful sweep) that are restorable:
//   image present + NOT Nicolette Mayer + NOT discontinued-tagged.
// (price is NOT a gate: the product-level price column reflects the $4.25 sample
//  variant, not the real roll price — real price must be re-validated at stage time
//  against the live variant, same root cause as the GMC $4.25 issue.)
//
// Tiers:
//   T1 CLEAN — house/standard lines, restore freely.            (batched)
//   T2 MAP   — Kravet-family; restore BUT must price at MAP.    (batched, flagged)
//   T3 HOLD  — Phillip Jeffries (display-excluded) + gated/uncosted (Scalamandre/
//              Koroseal/Newmor/Desima/Cowtan/Innovations/Wolf Gordon). NOT batched —
//              listed for explicit per-vendor review.
//
// Batches: 1,000/day (Shopify ~1k/day variant cap), T1 first then T2. Each batch
// carries a stage-time re-validation gate spec.
import { execFileSync } from 'node:child_process';
import fs from 'node:fs';

const PSQL = [ '/opt/homebrew/opt/postgresql@14/bin/psql', '/usr/local/opt/postgresql@14/bin/psql', 'psql' ]
  .find(p => { try { execFileSync(p, ['--version'], { stdio: 'ignore' }); return true; } catch { return false; } }) || 'psql';
const DB = process.env.DW_UNIFIED_URL || 'postgresql:///dw_unified?host=/tmp';
const OUT_JSON = `${process.env.HOME}/.claude/yolo-queue/april-restore-plan-2026-06-16.json`;
const OUT_MD = `${process.env.HOME}/.claude/yolo-queue/april-restore-plan-2026-06-16.md`;
const BATCH = 1000;

const HOLD = /phillip jeffries|jeffries|scalamandre|koroseal|newmor|desima|cowtan|innovations|wolf *gordon/i;
const KF = /kravet|lee jofa|groundworks|brunschwig|cole *& *son|gp *& *j *baker|colefax|clarke *& *clarke|mulberry|threads|baker lifestyle|andrew martin|aerin|barclay butera|thom filicia/i;

function q(sql) {
  const out = execFileSync(PSQL, [DB, '-At', '-F', '|', '-c', sql], { encoding: 'utf8', maxBuffer: 256 * 1024 * 1024 });
  return out.trim() ? out.trim().split('\n').map(r => r.split('|')) : [];
}

const rows = q(`
  select shopify_id, vendor, mfr_sku, handle from shopify_products
  where status='ARCHIVED' and updated_at_shopify::date in ('2026-04-17','2026-04-18')
    and vendor !~* 'nicolette' and (tags is null or tags !~* 'discontinu')
    and image_url is not null and image_url<>''
  order by vendor, mfr_sku`).map(r => ({ shopify_id: r[0], vendor: r[1], sku: r[2], handle: r[3] }));

const tier = (v) => HOLD.test(v) ? 'T3_HOLD' : (KF.test(v) && !/scalamandre/i.test(v)) ? 'T2_MAP' : 'T1_CLEAN';
for (const r of rows) r.tier = tier(r.vendor);

const t1 = rows.filter(r => r.tier === 'T1_CLEAN');
const t2 = rows.filter(r => r.tier === 'T2_MAP');
const t3 = rows.filter(r => r.tier === 'T3_HOLD');

// batch T1 then T2 into 1k/day
const batchable = [...t1, ...t2];
const batches = [];
for (let i = 0; i < batchable.length; i += BATCH) {
  const slice = batchable.slice(i, i + BATCH);
  batches.push({
    day: batches.length + 1,
    count: slice.length,
    tiers: { T1_CLEAN: slice.filter(r => r.tier === 'T1_CLEAN').length, T2_MAP: slice.filter(r => r.tier === 'T2_MAP').length },
    ids: slice.map(r => r.shopify_id),
  });
}

const byVendor = (arr) => Object.entries(arr.reduce((m, r) => (m[r.vendor] = (m[r.vendor]||0)+1, m), {})).sort((a,b)=>b[1]-a[1]);

const plan = {
  generated_at: new Date().toISOString(),
  source: 'dw_unified shopify_products mirror (Mac2), READ-ONLY',
  cohort: 'status=ARCHIVED, updated_at_shopify 2026-04-17/18, image present, not Nicolette, not discontinued-tagged',
  totals: { restorable: rows.length, t1_clean: t1.length, t2_map: t2.length, t3_hold: t3.length, batchable: batchable.length, batches: batches.length },
  stage_time_revalidation_gate: [
    'product still ARCHIVED (not already restored/changed)',
    'image_url still present',
    'live MIN non-sample variant price > $5 (skip if it only has the $4.25 sample → would re-trigger GMC $4.25)',
    'vendor not in HOLD list (re-check)',
    'for T2_MAP: price floored at MAP (wholesale×1.5) before publish',
  ],
  apply_method: 'shopify_api_queue: {id, status:ACTIVE} per product, ≤1000/day, ≥90s between bursts (gated — Steve fires)',
  batches,
  t3_hold_for_review: { count: t3.length, by_vendor: byVendor(t3) },
  t1_by_vendor_top: byVendor(t1).slice(0, 25),
  t2_by_vendor: byVendor(t2),
};

fs.writeFileSync(OUT_JSON, JSON.stringify(plan, null, 2));

let md = `# April mass-archive — RESTORE STAGING PLAN (report-only)\n\n`;
md += `**Generated:** ${plan.generated_at} · source: dw_unified mirror (READ-ONLY) · **$0**\n`;
md += `**Standing veto respected:** this is a PLAN only — no Shopify writes. Steve fires the publish.\n\n`;
md += `## Cohort\n${plan.cohort}\n\n`;
md += `## Totals\n`;
md += `- **Restorable: ${plan.totals.restorable.toLocaleString()}**\n`;
md += `  - T1 CLEAN (restore freely): **${t1.length.toLocaleString()}**\n`;
md += `  - T2 MAP (Kravet-family — price at MAP): **${t2.length.toLocaleString()}**\n`;
md += `  - T3 HOLD (PJ display-excluded + gated/uncosted — review): **${t3.length.toLocaleString()}**\n`;
md += `- **Auto-batchable (T1+T2): ${batchable.length.toLocaleString()}** → **${batches.length} daily batches** of ≤${BATCH}\n\n`;
md += `> Note: the product-level price column = the $4.25 sample variant for most rows, so price is re-validated at STAGE TIME against the live variant (not used as a pre-filter). This prevents restoring a product that only has the $4.25 sample (which would re-feed the GMC $4.25 problem).\n\n`;
md += `## Stage-time re-validation gate (re-checked per product at publish)\n`;
for (const g of plan.stage_time_revalidation_gate) md += `- ${g}\n`;
md += `\n## Apply method (GATED)\n${plan.apply_method}\n\n`;
md += `## T1 CLEAN — top vendors\n| Vendor | Count |\n|---|---:|\n`;
for (const [v, c] of plan.t1_by_vendor_top) md += `| ${v} | ${c} |\n`;
md += `\n## T2 MAP — Kravet-family (restore at MAP)\n| Vendor | Count |\n|---|---:|\n`;
for (const [v, c] of plan.t2_by_vendor) md += `| ${v} | ${c} |\n`;
md += `\n## T3 HOLD-for-review (NOT batched)\n| Vendor | Count |\n|---|---:|\n`;
for (const [v, c] of plan.t3_hold_for_review.by_vendor) md += `| ${v} | ${c} |\n`;
md += `\n## Batch schedule (first 10 days)\n| Day | Count | T1 | T2 |\n|---:|---:|---:|---:|\n`;
for (const b of batches.slice(0, 10)) md += `| ${b.day} | ${b.count} | ${b.tiers.T1_CLEAN} | ${b.tiers.T2_MAP} |\n`;
if (batches.length > 10) md += `| … | | | (${batches.length} batches total) |\n`;
md += `\n_Full per-batch ID lists in the JSON: ${OUT_JSON.replace(process.env.HOME, '~')}_\n`;
fs.writeFileSync(OUT_MD, md);

console.log(`[april-restore-plan] restorable=${rows.length} (T1=${t1.length} T2=${t2.length} T3-hold=${t3.length}) → ${batches.length} batches`);
console.log(`JSON: ${OUT_JSON}\nMD:   ${OUT_MD}`);