← back to Dw Activation Calendar
server.js
360 lines
'use strict';
/*
* DW Calendar — TWO surfaces in one app (DTD verdict AX, 2026-06-24):
*
* 1) Google Calendar tab (EDITABLE): live read + CRUD against a real shared
* DW Google Calendar via google-cal.js (OAuth + events.list/insert/patch/
* delete). The modal creates/edits/deletes real events. Degrades gracefully
* to a "not connected" state when no OAuth credential is present (never 500s).
* Credential + consent are Steve-gated (see README + pending-approval memo).
*
* 2) SKU Activation tab (read-only): projects the go-active rollout of every
* SKU staged for activation, at the publish cadence, in date order, until
* the staged list is exhausted. Each SKU is a chip with vendor + image.
* Source = dw_unified.bulk_fivefield_worklist joined to shopify_products.
*/
const express = require('express');
const path = require('path');
const { Pool } = require('pg');
const { execFile } = require('child_process');
const gcal = require('./google-cal');
const app = express();
app.use(express.json({ limit: '256kb' }));
const PORT = process.env.PORT || 9765;
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 });
app.use(express.static(path.join(__dirname, 'public')));
const fs = require('fs');
const SCHEDULE_FILE = path.join(__dirname, 'data', 'activation-schedule.json');
// --- PRIMARY source: the persisted per-SKU date+time schedule ----------------
// data/activation-schedule.json is generated by scripts/generate-schedule.js —
// every staged item carries a DURABLE go_live_at (date + HH:10 drain slot). We
// read it (re-reading when the file changes), attach live image/title from
// shopify_products (joined on shopify_id, so blank-dw_sku build-roll items still
// get images), and group by go_live_date into the calendar's day cells.
let _schedCache = null, _schedMtime = 0;
function loadScheduleFile() {
try {
const st = fs.statSync(SCHEDULE_FILE);
if (_schedCache && st.mtimeMs === _schedMtime) return _schedCache;
const parsed = JSON.parse(fs.readFileSync(SCHEDULE_FILE, 'utf8'));
_schedCache = parsed; _schedMtime = st.mtimeMs;
return parsed;
} catch { return null; } // missing/corrupt → caller falls back to compute
}
// image/title lookup for a set of shopify_ids (chunked; never throws)
async function imagesFor(shopifyIds) {
const map = new Map();
if (!shopifyIds.length) return map;
const c = await pool.connect();
try {
for (let i = 0; i < shopifyIds.length; i += 5000) {
const chunk = shopifyIds.slice(i, i + 5000);
const r = await c.query(
`select shopify_id, image_url, title, handle, dw_sku
from shopify_products where shopify_id = any($1::text[])`, [chunk]);
for (const row of r.rows) map.set(row.shopify_id, row);
}
} catch (e) { /* degrade gracefully — chips render without images */ }
finally { c.release(); }
return map;
}
// Build the calendar payload from the persisted schedule. Returns null if the
// file is absent so the caller can fall back to the legacy on-the-fly compute.
async function scheduleFromFile() {
const f = loadScheduleFile();
if (!f || !Array.isArray(f.skus) || !f.skus.length || !f.meta) return null;
const imgs = await imagesFor([...new Set(f.skus.map(s => s.shopify_id).filter(Boolean))]);
const byDate = new Map(); const vendorTotals = {};
for (const s of f.skus) {
const sp = imgs.get(s.shopify_id) || {};
vendorTotals[s.vendor] = (vendorTotals[s.vendor] || 0) + 1;
if (!byDate.has(s.go_live_date)) byDate.set(s.go_live_date, []);
byDate.get(s.go_live_date).push({
dw_sku: s.dw_sku || sp.dw_sku || '', mfr_sku: s.mfr_sku || '',
vendor: s.vendor,
// fix_type is legacy (worklist-only); the unified schedule uses mat_tier
// (0 = textures/naturals, activated first). Keep fix_type for back-compat
// (undefined on unified rows) and expose mat_tier for the UI's texture pill.
fix_type: s.fix_type, mat_tier: (s.mat_tier != null ? s.mat_tier : null),
slot_hour: s.slot_hour, go_live_at: s.go_live_at,
image_url: sp.image_url || null, title: sp.title || s.title || null,
shopify_id: s.shopify_id || null, handle: sp.handle || null,
});
}
const days = [...byDate.entries()].sort((a, b) => a[0] < b[0] ? -1 : 1)
.map(([date, skus]) => {
const perVendor = {}; skus.forEach(s => { perVendor[s.vendor] = (perVendor[s.vendor] || 0) + 1; });
const hrs = skus.map(s => s.slot_hour);
return { date, count: skus.length, perVendor, skus,
firstSlot: Math.min(...hrs), lastSlot: Math.max(...hrs) };
});
return {
days, vendorTotals,
totalSkus: f.meta.total_items, totalDays: days.length,
endDate: f.meta.end_date, perDay: f.meta.per_day, perVendorDay: null,
startDate: f.meta.start_date, coverage: f.meta.coverage,
blankSkuItems: f.meta.blank_sku_items, distinctProducts: f.meta.distinct_products,
persisted: true, scheduleGeneratedAt: f.meta.generated_at,
generatedAt: new Date().toISOString(),
};
}
// --- FALLBACK: legacy on-the-fly compute (used only if the schedule file is
// missing). Kept so the tab never goes blank before the first generator run. --
let _cache = null, _cacheAt = 0;
async function loadStaged() {
if (_cache && Date.now() - _cacheAt < 5 * 60e3) return _cache;
const c = await pool.connect();
try {
const r = await c.query(`
select w.dw_sku, w.vendor, w.fix_type, w.order_rank, w.shopify_id,
sp.image_url, sp.handle, sp.title
from bulk_fivefield_worklist w
left join shopify_products sp on sp.shopify_id = w.shopify_id
where w.fix_type <> 'reprice-zero'
and coalesce(upper(sp.status), '') <> 'ACTIVE' -- already-live products are not re-staged (Steve 2026-07-15)
order by w.order_rank, w.vendor, w.dw_sku`);
_cache = r.rows; _cacheAt = Date.now();
return _cache;
} finally { c.release(); }
}
// --- the legacy scheduler (fallback only) -----------------------------------
function schedule(rows, { perDay = 432, perVendorDay = 10, startISO } = {}) {
// group by vendor, preserving rank order within each vendor
const byVendor = new Map();
for (const r of rows) {
if (!byVendor.has(r.vendor)) byVendor.set(r.vendor, []);
byVendor.get(r.vendor).push(r);
}
// queues sorted so the biggest backlogs lead each round (keeps days full)
let queues = [...byVendor.entries()].map(([vendor, items]) => ({ vendor, items, i: 0 }));
const start = startISO ? new Date(startISO + 'T00:00:00') : (() => { const d = new Date(); d.setHours(0,0,0,0); d.setDate(d.getDate()+1); return d; })();
const days = [];
let dayIdx = 0;
const remaining = () => queues.reduce((a, q) => a + (q.items.length - q.i), 0);
while (remaining() > 0 && dayIdx < 400) { // 400-day safety cap
const d = new Date(start); d.setDate(d.getDate() + dayIdx);
const iso = d.toISOString().slice(0, 10);
const skus = []; const perVendorUsed = {};
// round-robin: keep looping vendors until the day is full or no vendor can add
let progressed = true;
queues.sort((a, b) => (b.items.length - b.i) - (a.items.length - a.i)); // biggest first
while (skus.length < perDay && progressed) {
progressed = false;
for (const q of queues) {
if (skus.length >= perDay) break;
if (q.i >= q.items.length) continue;
if ((perVendorUsed[q.vendor] || 0) >= perVendorDay) continue;
const it = q.items[q.i++];
perVendorUsed[q.vendor] = (perVendorUsed[q.vendor] || 0) + 1;
skus.push(it);
progressed = true;
}
}
days.push({ date: iso, count: skus.length, perVendor: perVendorUsed, skus });
dayIdx++;
}
return { days, totalSkus: rows.length, totalDays: days.length,
endDate: days.length ? days[days.length - 1].date : null,
perDay, perVendorDay };
}
// --- API --------------------------------------------------------------------
// --- LIVE data: regenerate the schedule from the CURRENT dw_unified worklist when
// the persisted file is stale (or a caller forces it). One in-flight regen shared
// across concurrent requests so a burst of loads never stampedes the generator.
const STALE_MS = Number(process.env.SCHED_STALE_MS || 10 * 60 * 1000); // 10 min
let _regenInFlight = null;
function regenerateSchedule() {
if (_regenInFlight) return _regenInFlight;
const { execFile } = require('child_process');
_regenInFlight = new Promise((resolve) => {
execFile('node', [path.join(__dirname, 'scripts', 'generate-schedule.js')],
{ cwd: __dirname, timeout: 120000 }, (err) => {
_regenInFlight = null; _schedCache = null; _schedMtime = 0;
resolve(!err);
});
});
return _regenInFlight;
}
async function ensureFresh(force) {
let stale = !!force;
try { const st = fs.statSync(SCHEDULE_FILE); if (Date.now() - st.mtimeMs > STALE_MS) stale = true; }
catch { stale = true; } // missing file → must generate
if (stale) await regenerateSchedule();
}
app.get('/api/schedule', async (req, res) => {
try {
// LIVE: refresh from the current worklist if the snapshot is stale (or ?fresh=1).
await ensureFresh(req.query.fresh === '1');
// PRIMARY: the persisted per-SKU date+time schedule (every item stamped).
const persisted = await scheduleFromFile();
if (persisted) return res.json(persisted);
// FALLBACK: legacy on-the-fly compute (only until the first generator run).
const rows = await loadStaged();
const perDay = Math.max(1, parseInt(req.query.perDay, 10) || 500); // EVEN 50/50: backlog gets 500 variants/day
// No hard per-vendor cap by default — matches the executor (pure round-robin: breadth-first
// while many vendors have stock, then the full 500/day flows to whoever remains, so the big
// vendors' tail clears instead of dragging for months). Set a number to impose a cap.
const perVendorDay = Math.max(1, parseInt(req.query.perVendorDay, 10) || 500);
const startISO = req.query.start && /^\d{4}-\d{2}-\d{2}$/.test(req.query.start) ? req.query.start : null;
const out = schedule(rows, { perDay, perVendorDay, startISO });
// vendor totals for the legend
const vt = {}; rows.forEach(r => { vt[r.vendor] = (vt[r.vendor] || 0) + 1; });
res.json({ ...out, vendorTotals: vt, generatedAt: new Date().toISOString() });
} catch (e) { res.status(500).json({ error: e.message }); }
});
app.get('/api/health', (_q, res) => res.json({ ok: true, port: PORT }));
// Re-project the schedule against the CURRENT worklist (re-stamp every item's
// date+time). Cheap, local, read-only against dw_unified; writes only the JSON.
app.post('/api/schedule/regenerate', (_q, res) => {
execFile('node', [path.join(__dirname, 'scripts', 'generate-schedule.js')],
{ cwd: __dirname, timeout: 120000 }, (err, stdout, stderr) => {
if (err) return res.status(500).json({ ok: false, error: (stderr || err.message).slice(0, 500) });
_schedCache = null; _schedMtime = 0; // force re-read on next /api/schedule
const f = loadScheduleFile();
res.json({ ok: true, meta: f && f.meta, out: String(stdout).trim() });
});
});
// Manual reschedule — drag a SKU chip to a different day. Updates the item's
// go_live_date/at in the persisted schedule AND records a durable override in
// data/schedule-overrides.json so a later regenerate keeps the manual move.
const OVERRIDES_FILE = path.join(__dirname, 'data', 'schedule-overrides.json');
app.post('/api/schedule/move', (req, res) => {
const { key, to_date } = req.body || {};
if (!key || !/^\d{4}-\d{2}-\d{2}$/.test(String(to_date || ''))) {
return res.status(400).json({ ok: false, error: 'key + valid to_date (YYYY-MM-DD) required' });
}
const f = loadScheduleFile();
if (!f || !Array.isArray(f.skus)) return res.status(409).json({ ok: false, error: 'no schedule file' });
const sku = f.skus.find(s => (s.shopify_id || s.dw_sku) === key);
if (!sku) return res.status(404).json({ ok: false, error: 'sku not found for key' });
const [Y, M, D] = to_date.split('-').map(Number);
const hour = sku.slot_hour != null ? sku.slot_hour : 0;
const dt = new Date(Y, M - 1, D, hour, 10, 0, 0); // keep the item's slot, on the new day
sku.go_live_date = to_date; sku.go_live_at = dt.toISOString(); sku.moved = true;
try {
fs.writeFileSync(SCHEDULE_FILE, JSON.stringify(f)); // reflect immediately
let ov = {}; try { ov = JSON.parse(fs.readFileSync(OVERRIDES_FILE, 'utf8')); } catch {}
ov[key] = { go_live_date: to_date, slot_hour: hour, at: new Date().toISOString() };
fs.writeFileSync(OVERRIDES_FILE, JSON.stringify(ov));
_schedCache = null; _schedMtime = 0;
res.json({ ok: true, key, go_live_date: to_date, go_live_at: sku.go_live_at });
} catch (e) { res.status(500).json({ ok: false, error: e.message }); }
});
// ============================================================================
// Google Calendar layer — read + editable CRUD.
// Every route degrades gracefully when no OAuth credential is present:
// it returns a clean { connected:false } / 409 'not_connected' signal,
// NEVER a 500. The OAuth credential + the consent click are Steve-gated.
// ============================================================================
// Connection status — drives the UI banner ("Calendar not connected — Steve
// must authorize" vs live).
app.get('/api/calendar/status', (_q, res) => res.json(gcal.status()));
// Begin the OAuth grant (302 → Google consent). 503 if no client-id/secret yet.
app.get('/api/oauth/start', (req, res) => {
if (!gcal.isOAuthConfigured()) {
return res.status(503).json({ error: 'oauth_not_configured',
message: 'Calendar not connected — Steve must add the Google OAuth credential.' });
}
try { res.redirect(gcal.buildAuthUrl()); }
catch (e) { res.status(500).json({ error: e.message }); }
});
// OAuth callback — exchanges ?code for tokens, persists, then bounces to the UI.
app.get('/api/oauth/callback', async (req, res) => {
const { code, state, error } = req.query;
if (error) return res.status(400).send(`OAuth denied: ${error}`);
if (!code || !state) return res.status(400).send('Missing code/state');
try {
await gcal.exchangeCodeAndStore(String(code), String(state));
res.redirect('/?connected=1');
} catch (e) {
res.status(400).send(`OAuth exchange failed: ${e.message}`);
}
});
// Disconnect (revoke + drop tokens).
app.post('/api/oauth/revoke', async (_q, res) => {
try { await gcal.revoke(); res.json({ ok: true }); }
catch (e) { res.status(500).json({ ok: false, error: e.message }); }
});
// --- events read (events.list for a window) ---------------------------------
app.get('/api/events', async (req, res) => {
try {
// Default window = the visible month ±1 week; client passes ISO bounds.
const now = new Date();
const timeMin = req.query.timeMin || new Date(now.getFullYear(), now.getMonth() - 1, 1).toISOString();
const timeMax = req.query.timeMax || new Date(now.getFullYear(), now.getMonth() + 2, 0).toISOString();
const out = await gcal.listEvents(String(timeMin), String(timeMax));
res.json(out);
} catch (e) { res.status(500).json({ error: e.message }); }
});
function validateEventBody(b) {
if (!b || typeof b !== 'object') return 'missing body';
if (!b.summary || !String(b.summary).trim()) return 'summary required';
if (!b.start) return 'start required';
if (!b.allDay && !b.end) return 'end required for timed events';
return null;
}
// --- events create (events.insert) ------------------------------------------
app.post('/api/events', async (req, res) => {
const err = validateEventBody(req.body);
if (err) return res.status(400).json({ ok: false, error: err });
try {
const r = await gcal.insertEvent(req.body);
if (!r.ok && r.reason === 'not_connected')
return res.status(409).json({ ok: false, error: 'not_connected',
message: 'Calendar not connected — Steve must authorize.' });
if (!r.ok) return res.status(502).json({ ok: false, error: r.reason, status: r.status, detail: r.detail });
res.status(201).json(r);
} catch (e) { res.status(500).json({ ok: false, error: e.message }); }
});
// --- events update (events.patch) -------------------------------------------
app.patch('/api/events/:id', async (req, res) => {
const err = validateEventBody(req.body);
if (err) return res.status(400).json({ ok: false, error: err });
try {
const r = await gcal.updateEvent(req.params.id, req.body);
if (!r.ok && r.reason === 'not_connected')
return res.status(409).json({ ok: false, error: 'not_connected',
message: 'Calendar not connected — Steve must authorize.' });
if (!r.ok) return res.status(502).json({ ok: false, error: r.reason, status: r.status, detail: r.detail });
res.json(r);
} catch (e) { res.status(500).json({ ok: false, error: e.message }); }
});
// --- events delete (events.delete) ------------------------------------------
app.delete('/api/events/:id', async (req, res) => {
try {
const r = await gcal.deleteEvent(req.params.id);
if (!r.ok && r.reason === 'not_connected')
return res.status(409).json({ ok: false, error: 'not_connected' });
res.json(r);
} catch (e) { res.status(500).json({ ok: false, error: e.message }); }
});
app.listen(PORT, () => console.log(`DW Activation Calendar on http://127.0.0.1:${PORT}`));