`modules/calendars/lib.js`

341 lines
// lib.js — the data engine for the unified "All Calendars" view.
//
// THREE layers, one array:
//   • activation — every staged SKU on its DURABLE go-live date+time, projected
//     faithfully from the real drain (rr order over bulk_fivefield_worklist,
//     500/day across hourly :10 slots — ported from dw-activation-calendar/
//     scripts/generate-schedule.js), then ENRICHED from shopify_products so each
//     SKU carries material/price/color/style/book/hue for the left-panel toggles.
//   • marketing + campaign — the MCC's own curated dates + campaign schedule
//     (read via ../calendar/store.js — one source of truth, no duplication).
//   • google — optional/fail-soft overlay (returns [] until OAuth is wired).
//
// Safe by design: dw_unified is READ-only here; the enriched projection is cached
// to data/calendars-activation.json (never enrich 20k rows per request); drag
// reschedules persist to data/calendars-overrides.json and survive regeneration.
const fs = require('fs');
const path = require('path');
const store = require('../calendar/store');

const DATA_DIR = path.join(__dirname, '..', '..', 'data');
const CACHE_FILE = path.join(DATA_DIR, 'calendars-activation.json');
const OVERRIDES_FILE = path.join(DATA_DIR, 'calendars-overrides.json');

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 STALE_MS = parseInt(process.env.SCHED_STALE_MS, 10) || 10 * 60 * 1000;
const hourCap = (h) => (h >= 21 ? 150 : 30);                      // drain.sh SLOT_MAX (reclaim 150 in 21-23)

// ── Postgres (lazy; dw_unified is canonical on Kamatera, /tmp mirror locally) ──
let _pool = null;
function pool() {
  if (_pool) return _pool;
  const { Pool } = require('pg');
  _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 });
  _pool.on('error', (e) => console.error('[calendars pool]', e.message));   // log, but never crash the MCC
  return _pool;
}

// The executor's EXACT rr ordering (bulk-fivefield-exec.py), minus reprice-zero.
// dw_sku is NOT unique/present on ~3.3k build-roll rows → shopify_id is identity.
const RR_SQL = `
  SELECT rr, shopify_id, dw_sku, mfr_sku, vendor, fix_type FROM (
    SELECT (row_number() OVER (PARTITION BY vendor ORDER BY (fix_type='add-sample') DESC, dw_sku))*100000
           + dense_rank() OVER (ORDER BY vendor) AS rr,
           shopify_id, dw_sku, mfr_sku, vendor, fix_type
      FROM bulk_fivefield_worklist
     WHERE status='pending' AND fix_type <> 'reprice-zero'
  ) t
  ORDER BY rr, vendor, dw_sku`;

// Products that already LAUNCHED (live on the storefront) in the last 30 days,
// placed on the calendar on their real launch date — the past-side companion to
// the future-side activation layer. Read-only, bounded by created_at_shopify.
const LAUNCHED_DAYS = 30;
const LAUNCHED_SQL = `
  SELECT shopify_id, vendor, product_type, retail_price, min_variant_price, price,
         tags, metafields, pattern_name, title, image_url, handle, dw_sku, mfr_sku,
         created_at_shopify
    FROM shopify_products
   WHERE status = 'ACTIVE' AND online_store_published = true
     AND created_at_shopify IS NOT NULL
     AND created_at_shopify >= now() - ($1 || ' days')::interval
   ORDER BY created_at_shopify DESC`;

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

// ── enrichment field normalizers ────────────────────────────────────────────
const pad = (n) => String(n).padStart(2, '0');
function parseTags(raw) {                        // handles PG-array text ({"a","b"}) and CSV
  if (!raw) return [];
  let s = String(raw).trim();
  if (s.startsWith('{') && s.endsWith('}')) s = s.slice(1, -1);
  const out = []; let cur = '', inq = false;
  for (const ch of s) {
    if (ch === '"') { inq = !inq; continue; }
    if (ch === ',' && !inq) { out.push(cur); cur = ''; continue; }
    cur += ch;
  }
  if (cur) out.push(cur);
  return out.map(t => t.trim()).filter(Boolean);
}
function tagVal(tags, ...prefixes) {             // "Color: Rattan" / "color:White" → value
  for (const t of tags) {
    const i = t.indexOf(':'); if (i < 0) continue;
    if (prefixes.includes(t.slice(0, i).trim().toLowerCase())) return t.slice(i + 1).trim();
  }
  return '';
}
function mfVal(metafields, key) {                // metafields.global.<key>.value
  try { return String(metafields?.global?.[key]?.value ?? '').trim(); } catch { return ''; }
}
function priceBand(p) {
  if (p == null || isNaN(p)) return 'Unpriced';
  if (p < 50) return '$0–50'; if (p < 100) return '$50–100'; if (p < 200) return '$100–200';
  if (p < 500) return '$200–500'; if (p < 1000) return '$500–1,000'; return '$1,000+';
}

// Vendors that must NEVER surface on any front-end surface. Schumacher is
// internal-only (see the schumacher-internal-only standing rule) — it is filtered
// out of BOTH calendar layers at the source so a cache regeneration can't
// reintroduce it. Matched case-insensitively against the resolved vendor.
const HIDDEN_VENDORS = new Set(['schumacher']);
const isHiddenVendor = (v) => HIDDEN_VENDORS.has(String(v || '').trim().toLowerCase());

// Map a shopify_products row → the common filterable fields (shared by the
// activation layer's enrichment join AND the launched layer's direct rows).
function fieldsFrom(e) {
  const tags = parseTags(e.tags);
  const price = e.retail_price ?? e.min_variant_price ?? e.price ?? null;
  return {
    material: e.product_type || mfVal(e.metafields, 'Type1') || '',
    price: price != null ? Number(price) : null,
    priceBand: priceBand(price != null ? Number(price) : null),
    color: tagVal(tags, 'color', 'colour') || '',
    style: tagVal(tags, 'style') || mfVal(e.metafields, 'Style2') || '',
    book: tagVal(tags, 'collection') || e.pattern_name || mfVal(e.metafields, 'Series') || '',
    hue: tagVal(tags, 'hue') || '',
    title: e.title || '', image_url: e.image_url || '', handle: e.handle || '',
  };
}

// ── build the enriched activation layer (+ live launched layer) ──────────────
// The two layers have DECOUPLED failure domains. The launched layer reads only
// shopify_products (present on every host), so it stays LIVE even where the
// drain worklist bulk_fivefield_worklist is absent (e.g. Kamatera, where the
// worklist is Mac-local). In that case the activation layer is served from the
// last good snapshot while launched is recomputed live — a missing worklist no
// longer sinks the launched layer (its own inner try/catch contains it).
async function computeActivation() {
  const c = await pool().connect();
  let rows = null, enrich = {}, launchedRows = [];
  let activationLive = false, activationErr = null;
  try {
    try {
      rows = (await c.query(RR_SQL)).rows;
      // enrich in chunks — one round-trip per 5k shopify_ids
      const ids = [...new Set(rows.map(r => r.shopify_id).filter(Boolean))];
      for (let i = 0; i < ids.length; i += 5000) {
        const chunk = ids.slice(i, i + 5000);
        const q = await c.query(
          `SELECT shopify_id, vendor, product_type, retail_price, min_variant_price, price,
                  tags, metafields, pattern_name, title, image_url, handle
             FROM shopify_products WHERE shopify_id = ANY($1::text[])`, [chunk]);
        for (const r of q.rows) enrich[r.shopify_id] = r;
      }
      activationLive = true;
    } catch (e) {
      activationErr = e.message;   // worklist absent on this host → keep snapshot activation
      rows = null;
    }
    // launched layer is INDEPENDENT of the worklist — always recompute live
    launchedRows = (await c.query(LAUNCHED_SQL, [String(LAUNCHED_DAYS)])).rows;
  } finally { c.release(); }

  // launched layer — one item per already-live product, on its launch date (LIVE)
  const launched = launchedRows.filter(r => !isHiddenVendor(r.vendor)).map(r => {
    const d = new Date(r.created_at_shopify);
    return {
      key: r.shopify_id, layer: 'launched',
      date: `${d.getFullYear()}-${pad(d.getMonth() + 1)}-${pad(d.getDate())}`,
      launched_at: (r.created_at_shopify instanceof Date ? r.created_at_shopify.toISOString() : String(r.created_at_shopify)),
      vendor: r.vendor || '', dw_sku: r.dw_sku || '', mfr_sku: r.mfr_sku || '',
      ...fieldsFrom(r),
      shopify_id: r.shopify_id,
    };
  });

  // activation layer — project LIVE if the worklist was readable, else reuse the
  // last good snapshot's activation items (the intended source where it's absent)
  let items, total, start_date, end_date;
  if (activationLive && rows) {
    const overrides = store.readJson(OVERRIDES_FILE, {}) || {};
    const start = new Date(); start.setHours(0, 0, 0, 0); start.setDate(start.getDate() + 1);
    total = rows.length;
    items = new Array(total);
    for (let seq = 0; seq < total; seq++) {
      const r = rows[seq];
      const day = Math.floor(seq / PER_DAY), pos = seq % PER_DAY, hour = slotHourForPos(pos);
      let dt = new Date(start.getFullYear(), start.getMonth(), start.getDate() + day, hour, SLOT_MIN, 0, 0);
      let slot = hour;
      // manual drag override wins (keyed by shopify_id or dw_sku)
      const ov = overrides[r.shopify_id] || (r.dw_sku && overrides[r.dw_sku]);
      if (ov && ov.go_live_date) {
        if (ov.slot_hour != null) slot = ov.slot_hour;
        const [Y, M, D] = ov.go_live_date.split('-').map(Number);
        dt = new Date(Y, M - 1, D, slot, SLOT_MIN, 0, 0);
      }
      const e = enrich[r.shopify_id] || {};
      items[seq] = {
        key: r.shopify_id || r.dw_sku,
        layer: 'activation',
        date: `${dt.getFullYear()}-${pad(dt.getMonth() + 1)}-${pad(dt.getDate())}`,
        go_live_at: dt.toISOString(),
        slot_hour: slot,
        vendor: e.vendor || r.vendor || '',          // prefer sanitized storefront vendor
        dw_sku: r.dw_sku || '', mfr_sku: r.mfr_sku || '', fix_type: r.fix_type,
        ...fieldsFrom(e),
        shopify_id: r.shopify_id || '',
      };
    }
    // drop internal-only vendors (e.g. Schumacher) AFTER date projection so the
    // remaining SKUs keep their real go-live slots (never resequenced by a regen)
    items = items.filter(it => !isHiddenVendor(it.vendor));
    total = items.length;
    start_date = items[0]?.date || null;
    end_date = items[total - 1]?.date || null;
  } else {
    const disk = store.readJson(CACHE_FILE, null) || {};
    items = (disk.items || []).filter(it => !isHiddenVendor(it.vendor));
    total = items.length;
    start_date = items[0]?.date || disk.start_date || null;
    end_date = items[total - 1]?.date || disk.end_date || null;
  }

  const out = {
    generated_at: new Date().toISOString(),
    total, per_day: PER_DAY, start_date, end_date,
    items,
    launched,                          // already-live products (last 30 days) — LIVE
    launched_count: launched.length,
    activation_live: activationLive,   // false where worklist absent (activation from snapshot)
    launched_live: true,               // launched always recomputes live from shopify_products
    ...(activationErr ? { activation_error: activationErr } : {}),
  };
  store.writeJson(CACHE_FILE, out);
  return out;
}

let _mem = null;   // { t, data }
async function getActivation(force) {
  if (!force && _mem && Date.now() - _mem.t < STALE_MS) return _mem.data;
  if (!force) {
    const disk = store.readJson(CACHE_FILE, null);
    if (disk && disk.generated_at && Date.now() - new Date(disk.generated_at).getTime() < STALE_MS) {
      _mem = { t: Date.now(), data: disk }; return disk;
    }
  }
  try {
    const data = await computeActivation();
    _mem = { t: Date.now(), data };
    return data;
  } catch (e) {
    // fail-soft: keep the MCC alive, surface the reason to the panel banner
    const disk = store.readJson(CACHE_FILE, null);
    if (disk) return { ...disk, stale: true, error: e.message };
    return { generated_at: new Date().toISOString(), total: 0, items: [], mock: true, error: e.message };
  }
}

// ── overlay layers (marketing dates + campaigns + google stub) ───────────────
function overlays(startISO, endISO) {
  const out = [];
  // marketing curated dates — walk each month in the window
  const s = new Date(startISO + 'T00:00:00'), e = new Date(endISO + 'T00:00:00');
  let y = s.getFullYear(), m = s.getMonth() + 1;
  while (y < e.getFullYear() || (y === e.getFullYear() && m <= e.getMonth() + 1)) {
    for (const d of store.datesForMonth(y, m)) {
      if (d.date >= startISO && d.date <= endISO) {
        out.push({ key: 'mk_' + d.date + '_' + d.title, layer: 'marketing', date: d.date,
          title: d.title, style: d.type, suggestion: d.suggestion, vendor: '', material: '' });
      }
    }
    if (++m > 12) { m = 1; y++; }
  }
  // campaigns
  for (const c of store.loadSchedule()) {
    if (c.date >= startISO && c.date <= endISO) {
      out.push({ key: c.id, layer: 'campaign', date: c.date, title: c.title,
        channel: c.channel, status: c.status, notes: c.notes || '', vendor: '', material: '' });
    }
  }
  // google — fail-soft stub until OAuth is wired (Steve-gated)
  out.push(...googleEvents(startISO, endISO));
  return out;
}
function googleEvents(startISO, endISO) { return []; }   // stub until OAuth is wired (Steve-gated)

// ── the unified feed the panel consumes ──────────────────────────────────────
async function buildEvents(force) {
  const act = await getActivation(force);
  const today = new Date();
  const todayISO = `${today.getFullYear()}-${pad(today.getMonth() + 1)}-${pad(today.getDate())}`;
  const winStart = (() => { const d = new Date(today); d.setMonth(d.getMonth() - 1); return `${d.getFullYear()}-${pad(d.getMonth() + 1)}-01`; })();
  const winEnd = (() => {
    const far = new Date(today); far.setMonth(far.getMonth() + 13);
    const cand = `${far.getFullYear()}-${pad(far.getMonth() + 1)}-${pad(new Date(far.getFullYear(), far.getMonth() + 1, 0).getDate())}`;
    return act.end_date && act.end_date > cand ? act.end_date : cand;
  })();
  const over = overlays(winStart, winEnd);
  const items = [...(act.items || []), ...(act.launched || []), ...over];
  const counts = items.reduce((a, it) => { a[it.layer] = (a[it.layer] || 0) + 1; return a; }, {});
  return {
    generated_at: act.generated_at,
    stale: act.stale || false, mock: act.mock || false, error: act.error || null,
    activation_live: act.activation_live !== false,   // false only where the worklist is absent (activation from snapshot)
    launched_live: act.launched_live || false,        // launched recomputed live from shopify_products
    activation_error: act.activation_error || null,   // diagnostic only — NOT surfaced as the top-level error banner
    activation: { total: act.total || 0, start: act.start_date, end: act.end_date, per_day: act.per_day || PER_DAY },
    window: { start: winStart, end: winEnd, today: todayISO },
    counts, items,
  };
}

// ── drag reschedule — persist + patch in-place (no 20k recompute per drag) ────
function applyMove({ key, to_date, layer }) {
  if (!key || !to_date || !/^\d{4}-\d{2}-\d{2}$/.test(to_date)) return { ok: false, error: 'key and valid to_date required' };
  if (layer === 'campaign') {
    const list = store.loadSchedule();
    const c = list.find(x => x.id === key);
    if (!c) return { ok: false, error: 'campaign not found' };
    const r = store.upsertCampaign({ ...c, date: to_date });
    return r.ok ? { ok: true, key, to_date, layer } : r;
  }
  // activation (default): record override + patch the cached item so the move shows immediately
  const ov = store.readJson(OVERRIDES_FILE, {}) || {};
  ov[key] = { go_live_date: to_date };
  store.writeJson(OVERRIDES_FILE, ov);
  const cache = store.readJson(CACHE_FILE, null);
  if (cache && cache.items) {
    const it = cache.items.find(x => x.key === key || x.shopify_id === key || x.dw_sku === key);
    if (it) {
      const [Y, M, D] = to_date.split('-').map(Number);
      const dt = new Date(Y, M - 1, D, it.slot_hour ?? 10, SLOT_MIN, 0, 0);
      it.date = to_date; it.go_live_at = dt.toISOString();
      store.writeJson(CACHE_FILE, cache);
      _mem = { t: Date.now(), data: cache };
    }
  }
  return { ok: true, key, to_date, layer: layer || 'activation' };
}

module.exports = { buildEvents, applyMove, getActivation };