`scripts/generate-schedule.js`

166 lines
'use strict';
/*
 * generate-schedule.js — assign a DURABLE date + time (go-live) to EVERY staged
 * SKU waiting to go active, and write it to data/activation-schedule.json.
 *
 * WHY a JSON file (not a PG table): dw_unified's publication `dw_unified_pub` is
 * FOR ALL TABLES, so ANY new table auto-joins logical replication to Kamatera and
 * could stall the subscriber's apply worker. A JSON file keeps the schedule
 * local-only, reversible, and non-canonical — exactly the DTD-AX intent — with
 * zero replication blast-radius. (dw_unified is still READ, never written.)
 *
 * FAITHFUL TO REALITY: the order and cadence now mirror the UNIFIED rotation
 * (2026-07-21 redesign — textures-first + vendor round-robin across ALL 15,863
 * staged DRAFTs, not just the ~5k field-fix worklist). The order is imported from
 * lib/rotation-order.js (ROTATION_ORDER_SQL) — the SAME canonical query the
 * rotation activator (dw-rotation-activator/rotate-activate.js) uses to actually
 * flip DRAFT→ACTIVE — so the projected calendar and the live activation order are
 * identical:
 *   - order  = mat_tier 0 (textures/naturals) FIRST, then mat_tier 1 (rest);
 *              within each tier a breadth-first vendor round-robin (rr) so each
 *              increment spreads one-per-vendor (never a single-vendor block).
 *   - cadence = HARD 500/day, spread across the 24 hourly slots that fire at :10,
 *               ~30/slot (150 in the 21-23 reclaim slots).
 * So each SKU's go_live_at is the concrete date + HH:10 slot the rotation will
 * actually activate it in — not an invented time.
 *
 * COVERAGE: ALL staged DRAFTs (Schumacher excluded — internal-only; help-doc
 * noise rows excluded). Two lanes drain the same ordered queue — the field-fix
 * drain builds missing variants for the ~5k incomplete drafts, and the rotation
 * activator flips the already-complete drafts live — but the EFFECTIVE order is
 * this one, unified.
 *
 * Re-run any time to re-project against the current DRAFT set (100% coverage,
 * deterministic). Every staged DRAFT gets exactly one (date, time).
 */
const fs = require('fs');
const path = require('path');
const { Pool } = require('pg');
const { ROTATION_ORDER_SQL } = require('../lib/rotation-order.js');

const PER_DAY = parseInt(process.env.SCHED_PER_DAY, 10) || 500;   // budget.cjs backlog hard cap
const SLOT_MIN = 10;                                              // drain fires at HH:10
const hourCap = (h) => (h >= 21 ? 150 : 30);                      // drain.sh SLOT_MAX (reclaim 150 in 21-23)

const pool = process.env.DATABASE_URL
  ? new Pool({ connectionString: process.env.DATABASE_URL, max: 4 })
  : new Pool({ host: process.env.PGHOST || '/tmp', database: process.env.PGDATABASE || 'dw_unified',
      user: process.env.PGUSER || process.env.USER || 'stevestudio2', max: 4 });

// UNIFIED rotation order (2026-07-21) — imported from lib/rotation-order.js so the
// calendar projects the EXACT order the rotation activator activates in:
//   textures-first (mat_tier 0) then rest (mat_tier 1), breadth-first vendor
//   round-robin (rr) within each tier. Schumacher + help-doc noise excluded there.
// This covers ALL staged DRAFTs (status='DRAFT' in shopify_products), not just the
// ~5k field-fix worklist. Already-ACTIVE products fall out naturally (the query
// selects status='DRAFT' only), satisfying Steve's "don't re-stage live products"
// rule. shopify_id is the durable per-item identity + image/title join key.
// (dw_unified still READ-only; nothing is written back to it.)
const RR_SQL = ROTATION_ORDER_SQL;

// which hourly :10 slot a within-day position lands in (0-based pos within the day)
function slotHourForPos(pos) {
  let cum = 0;
  for (let h = 0; h < 24; h++) {
    const cap = hourCap(h);
    if (pos < cum + cap) return h;
    cum += cap;
  }
  return 23; // unreachable under a 500/day cap, but safe
}

async function main() {
  const c = await pool.connect();
  let rows;
  try {
    rows = (await c.query(RR_SQL)).rows;
  } finally { c.release(); await pool.end(); }

  const total = rows.length;
  if (total === 0) { console.error('No pending schedulable SKUs — nothing to schedule.'); process.exit(2); }

  // Start at the next full drain day (tomorrow 00:00 local) — matches the calendar's
  // existing convention and avoids over-fitting today's partially-spent budget.
  const start = new Date(); start.setHours(0, 0, 0, 0); start.setDate(start.getDate() + 1);

  const pad = (n) => String(n).padStart(2, '0');
  const out = new Array(total);
  for (let seq = 0; seq < total; seq++) {
    const r = rows[seq];
    const day = Math.floor(seq / PER_DAY);
    const pos = seq % PER_DAY;
    const hour = slotHourForPos(pos);
    // concrete local wall-clock instant: (start + day) at HH:10
    const dt = new Date(start.getFullYear(), start.getMonth(), start.getDate() + day, hour, SLOT_MIN, 0, 0);
    const go_live_date = `${dt.getFullYear()}-${pad(dt.getMonth() + 1)}-${pad(dt.getDate())}`;
    out[seq] = {
      shopify_id: r.shopify_id,          // durable per-item identity + image/title join key
      dw_sku: r.dw_sku || '',            // may be blank on some rows
      title: r.title || '',              // label (shared query returns the live title)
      product_type: r.product_type || '',
      mat_tier: Number(r.mat_tier),      // 0 = textures/naturals (activated first), 1 = rest
      rr: Number(r.rr),
      seq,
      vendor: r.vendor,
      slot_hour: hour,
      go_live_date,
      go_live_at: dt.toISOString(),      // exact instant (UTC); UI renders back to local
    };
  }

  // Apply MANUAL MOVES (drag-to-reschedule) so a regenerate keeps the user's
  // hand-placed dates instead of overwriting them with the computed cadence.
  try {
    const ov = JSON.parse(fs.readFileSync(path.join(__dirname, '..', 'data', 'schedule-overrides.json'), 'utf8'));
    let applied = 0;
    for (const s of out) {
      const o = ov[s.shopify_id] || ov[s.dw_sku];
      if (o && o.go_live_date) {
        s.go_live_date = o.go_live_date;
        if (o.slot_hour != null) s.slot_hour = o.slot_hour;
        const [Y, M, D] = o.go_live_date.split('-').map(Number);
        s.go_live_at = new Date(Y, M - 1, D, s.slot_hour, 10, 0, 0).toISOString();
        s.moved = true; applied++;
      }
    }
    if (applied) console.log(`  applied ${applied} manual move override(s)`);
  } catch { /* no overrides file — nothing to apply */ }

  // Coverage assertion — EVERY staged item MUST have a date+time (Steve's requirement).
  // Keyed on the row (seq), not dw_sku: dw_sku is not unique and can be blank.
  const withDT = out.filter(s => s.go_live_at && s.go_live_date).length;
  const uniqueSeq = new Set(out.map(s => s.seq)).size;
  if (withDT !== total || uniqueSeq !== total) {
    console.error(`COVERAGE FAIL: total=${total} withDateTime=${withDT} uniqueSeq=${uniqueSeq}`);
    process.exit(3);
  }

  const distinctProducts = new Set(out.map(s => s.shopify_id)).size;
  const blankSkuItems = out.filter(s => !s.dw_sku).length;
  const tier0Items = out.filter(s => s.mat_tier === 0).length;
  const meta = {
    generated_at: new Date().toISOString(),
    total_items: total,               // every staged DRAFT = one drain unit (one slot)
    distinct_products: distinctProducts,
    tier0_textures: tier0Items,       // textures/naturals activated FIRST
    tier1_rest: total - tier0Items,
    blank_sku_items: blankSkuItems,   // rows without a dw_sku (keyed by shopify_id)
    per_day: PER_DAY,
    slot_minute: SLOT_MIN,
    start_date: `${start.getFullYear()}-${pad(start.getMonth() + 1)}-${pad(start.getDate())}`,
    end_date: out[total - 1].go_live_date,
    days_to_clear: Math.ceil(total / PER_DAY),
    coverage: `${withDT}/${total}`,
    source: 'ALL staged DRAFTs via lib/rotation-order.js (textures-first + vendor round-robin, Schumacher+noise excluded) @ 500/day hourly :10 slots',
  };

  const dataDir = path.join(__dirname, '..', 'data');
  fs.mkdirSync(dataDir, { recursive: true });
  const file = path.join(dataDir, 'activation-schedule.json');
  fs.writeFileSync(file, JSON.stringify({ meta, skus: out }, null, 0));
  console.log(`✓ wrote ${file}`);
  console.log(`  ${meta.coverage} SKUs stamped with date+time · ${meta.days_to_clear} days · ${meta.start_date} → ${meta.end_date}`);
}

main().catch(e => { console.error('generate-schedule failed:', e.message); process.exit(1); });