`scripts/generate-schedule.js.bak`

164 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 mirror the real executor
 * (dw-five-field-step0/bulk-fivefield-exec.py + drain.sh):
 *   - order  = the executor's live vendor round-robin `rr`
 *              (samples-first within each vendor, breadth-first across vendors),
 *              computed over the SAME pending set, excluding reprice-zero (held).
 *   - cadence = HARD 500/day (budget.cjs backlog cap), spread across the 24 hourly
 *               drain slots that fire at :10, ~30/slot (150 in the 21-23 reclaim
 *               slots). Under a 500/day cap a day fills hours 00:10..~16:10.
 * So each SKU's go_live_at is the concrete date + HH:10 slot the drain will
 * actually activate it in — not an invented time.
 *
 * Re-run any time to re-project against the current worklist (100% coverage,
 * deterministic). Every staged SKU gets exactly one (date, time).
 */
const fs = require('fs');
const path = require('path');
const { Pool } = require('pg');

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 });

// The executor's EXACT rr ordering (bulk-fivefield-exec.py), minus reprice-zero
// (held-no-cost — the drain's TOTAL and the calendar both exclude it).
// NOTE: dw_sku is NOT the row key — ~3.3k build-roll rows carry a blank dw_sku but
// always have a shopify_id, so shopify_id is the durable per-item identity (and the
// right join key for images/titles). Each ROW is one drain unit → one slot.
// EXCLUDE already-live products (Steve's rule, 2026-07-15): if a product is
// already ACTIVE on Shopify, it has already "gone active" — it must NOT be staged
// for redeployment on the activation calendar. We gate on the shopify_products
// mirror's status. LEFT JOIN so a worklist row with no mirror row is KEPT (can't
// prove it's live). The window functions compute over the FILTERED set so rr stays
// contiguous. (dw_unified still READ-only; nothing is written back to it.)
const RR_SQL = `
  SELECT rr, shopify_id, dw_sku, mfr_sku, vendor, fix_type FROM (
    SELECT (row_number() OVER (PARTITION BY w.vendor ORDER BY (w.fix_type='add-sample') DESC, w.dw_sku))*100000
           + dense_rank() OVER (ORDER BY w.vendor) AS rr,
           w.shopify_id, w.dw_sku, w.mfr_sku, w.vendor, w.fix_type
      FROM bulk_fivefield_worklist w
      LEFT JOIN shopify_products p ON p.shopify_id = w.shopify_id
     WHERE w.status='pending' AND w.fix_type <> 'reprice-zero'
       AND COALESCE(upper(p.status), '') <> 'ACTIVE'   -- already-live products are not re-staged
       AND w.vendor NOT ILIKE '%schumacher%'           -- Schumacher is INTERNAL-ONLY (Steve HARD RULE 2026-07-16): never drain to the live store; internal.schumacher.designerwallcoverings.com only
  ) t
  ORDER BY rr, vendor, dw_sku`;

// 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 build-roll rows
      mfr_sku: r.mfr_sku || '',          // label fallback when dw_sku is blank
      rr: Number(r.rr),
      seq,
      vendor: r.vendor,
      fix_type: r.fix_type,
      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 meta = {
    generated_at: new Date().toISOString(),
    total_items: total,               // worklist rows = drain units (each gets one slot)
    distinct_products: distinctProducts,
    blank_sku_items: blankSkuItems,   // build-roll 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: 'bulk_fivefield_worklist (rr order, reprice-zero 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); });