`scripts/draft-reactivation-plan/build-draft-reactivation-plan.mjs`

78 lines
// build-draft-reactivation-plan — REPORT-ONLY staging plan to reactivate sellable
// products stuck in DRAFT (fully built, never published). Same tiered+batched+
// stage-time-revalidation pattern as the April restore plan. NO Shopify writes.
//
// Cohort: status=DRAFT, image present, NOT Nicolette, NOT discontinued-tagged.
// Tiers: T1 CLEAN (batch) / T2 MAP Kravet-family (batch, price at MAP) /
//        T3 HOLD Phillip Jeffries(display-excluded)+gated-uncosted (review, no batch).
// price is NOT a pre-filter (product price col = $4.25 sample variant for many);
// real price re-validated at stage time against the live variant.
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/draft-reactivation-plan-2026-06-16.json`;
const OUT_MD = `${process.env.HOME}/.claude/yolo-queue/draft-reactivation-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: 128 * 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 upper(status)='DRAFT' and image_url is not null and image_url<>''
    and vendor !~* 'nicolette' and (tags is null or tags !~* 'discontinu')
  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'), t2 = rows.filter(r => r.tier === 'T2_MAP'), t3 = rows.filter(r => r.tier === 'T3_HOLD');

const batchable = [...t1, ...t2];
const batches = [];
for (let i = 0; i < batchable.length; i += BATCH) {
  const s = batchable.slice(i, i + BATCH);
  batches.push({ day: batches.length + 1, count: s.length,
    tiers: { T1_CLEAN: s.filter(r=>r.tier==='T1_CLEAN').length, T2_MAP: s.filter(r=>r.tier==='T2_MAP').length },
    ids: s.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 mirror (READ-ONLY)',
  cohort: 'status=DRAFT, 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 DRAFT (not already active/changed)', 'image_url still present',
    'live MIN non-sample variant price > $5 (skip $4.25-sample-only → would re-feed GMC)',
    'vendor not in HOLD list', 'T2_MAP: floored at MAP before publish',
  ],
  apply_method: 'shopify_api_queue: {id, status:ACTIVE} + set inventory=2026 on BOTH variants (per new-products rule), ≤1000/day — GATED, Steve fires',
  batches, t3_hold_for_review: { count: t3.length, by_vendor: byVendor(t3) },
  t1_by_vendor: byVendor(t1), t2_by_vendor: byVendor(t2),
};
fs.writeFileSync(OUT_JSON, JSON.stringify(plan, null, 2));

let md = `# DRAFT reactivation — STAGING PLAN (report-only)\n\n`;
md += `**${plan.generated_at}** · dw_unified mirror (READ-ONLY) · **$0** · standing veto respected (PLAN only, Steve fires).\n\n`;
md += `## Cohort\n${plan.cohort}\n\n## Totals\n`;
md += `- **Restorable DRAFTs: ${rows.length.toLocaleString()}**\n  - T1 CLEAN: **${t1.length}**\n  - T2 MAP (Kravet-family, price at MAP): **${t2.length}**\n  - T3 HOLD (PJ + gated/uncosted — review): **${t3.length}**\n`;
md += `- Auto-batchable (T1+T2): **${batchable.length}** → **${batches.length}** daily batch(es) of ≤${BATCH}\n\n`;
md += `> Note: product price col = $4.25 sample for many rows → price re-validated at STAGE TIME, not pre-filtered. New-product rule: set inventory=2026 on BOTH variants on activation.\n\n`;
md += `## Stage-time re-validation gate\n${plan.stage_time_revalidation_gate.map(g=>`- ${g}`).join('\n')}\n\n`;
md += `## Apply method (GATED)\n${plan.apply_method}\n\n`;
md += `## T1 CLEAN by vendor\n| Vendor | Count |\n|---|---:|\n${plan.t1_by_vendor.map(([v,c])=>`| ${v} | ${c} |`).join('\n')}\n\n`;
if (t2.length) md += `## T2 MAP by vendor\n| Vendor | Count |\n|---|---:|\n${plan.t2_by_vendor.map(([v,c])=>`| ${v} | ${c} |`).join('\n')}\n\n`;
md += `## T3 HOLD-for-review (NOT batched)\n| Vendor | Count |\n|---|---:|\n${plan.t3_hold_for_review.by_vendor.map(([v,c])=>`| ${v} | ${c} |`).join('\n')}\n`;
fs.writeFileSync(OUT_MD, md);

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