← back to Consulting Designerwallcoverings Com

scripts/collect-dw.mjs

383 lines

#!/usr/bin/env node
// DW analysis collector — DTD verdict C (2026-07-26, 5/5): snapshot JSON is the
// render source; this script is the re-runnable collector. Reads ONLY: the local
// dw_unified mirror (psql over the /tmp socket), the MCC status API, the all.dw
// microsite directory, and the public storefront feed. Never writes to any DW
// system. Output: data/dw-analysis.json with a collected_at stamp.
import { execFileSync } from 'node:child_process';
import { writeFileSync, mkdirSync, readFileSync, statSync } from 'node:fs';
import { dirname, join } from 'node:path';
import { fileURLToPath } from 'node:url';
import { homedir } from 'node:os';

const HERE = dirname(fileURLToPath(import.meta.url));
const OUT = join(HERE, '..', 'data');
mkdirSync(OUT, { recursive: true });

const AUTH = 'Basic ' + Buffer.from('admin:DW2024!').toString('base64');
// One canonical forbidden-name list. Every downstream filter (product rows, fleet
// hostnames, competitor note scrubbing) derives its regex from this single source
// so additions here propagate everywhere automatically.
const FORBIDDEN_TERMS = ['schumacher', 'momentum', 'versa', 'wallquest', 'chesapeake',
  'seabrook', 'nextwall', 'command54', 'greenland', 'rigo', 'tokiwa', 'justin.?david'];
// General name match — for product vendor/title strings (allows "justin david" with space).
const FORBIDDEN_NAMES = new RegExp(FORBIDDEN_TERMS.join('|'), 'i');
// Word-boundary form — for scrubbing forbidden terms out of competitor note text.
const SCRUB = new RegExp('\\b(' + FORBIDDEN_TERMS.join('|') + ')\\b,?\\s*', 'gi');
// Hostname form — no spaces in URLs; collapse justin.?david to the no-space variants.
const FORBIDDEN = new RegExp(FORBIDDEN_TERMS.map(t => t.replace('justin.?david', 'justindavid|justin-david')).join('|'), 'i');

function sql(q) {
  try {
    const out = execFileSync('psql', ['host=/tmp dbname=dw_unified', '-tA', '-F', '\t', '-c', q],
      { encoding: 'utf8', timeout: 30000 });
    return out.trim().split('\n').filter(Boolean).map(r => r.split('\t'));
  } catch (e) { console.error('sql fail:', q.slice(0, 60), e.message.slice(0, 120)); return []; }
}
async function getJson(url, { auth = false, timeout = 12000 } = {}) {
  try {
    const r = await fetch(url, { headers: auth ? { Authorization: AUTH } : {}, signal: AbortSignal.timeout(timeout) });
    if (!r.ok) return { _status: r.status };
    return await r.json();
  } catch (e) { return { _error: String(e.message || e).slice(0, 120) }; }
}

/* ---------------------------------------------------- mirror: catalog KPIs --- */
const statusRows = sql(`select upper(status), count(*) from shopify_products where status is not null group by 1 order by 2 desc`);
const statuses = Object.fromEntries(statusRows.map(([s, c]) => [s, +c]));

const vendors = sql(`select count(distinct vendor) from shopify_products where status='ACTIVE'`)[0]?.[0];
const topVendors = sql(`select vendor, count(*) from shopify_products where status='ACTIVE' and vendor is not null group by 1 order by 2 desc limit 14`)
  .map(([v, c]) => ({ vendor: v, active: +c }));

const types = sql(`select coalesce(nullif(trim(product_type),''),'(untyped)'), count(*) from shopify_products where status='ACTIVE' group by 1 order by 2 desc limit 10`)
  .map(([t, c]) => ({ type: t, active: +c }));

// 5-field compliance over ACTIVE (Steve's hard rule + the image 6th check)
const ff = sql(`select
    count(*) filter (where not coalesce(has_sample_variant,false)),
    count(*) filter (where not coalesce(has_product_variant,false)),
    count(*) filter (where not coalesce(has_description,false)),
    count(*) filter (where image_url is null),
    count(*) filter (where coalesce(min_variant_price,0) <= 0),
    count(*)
  from shopify_products where status='ACTIVE'`)[0] || [];
const fiveField = {
  missing_sample: +ff[0] || 0, missing_sellable: +ff[1] || 0, missing_description: +ff[2] || 0,
  missing_image: +ff[3] || 0, zero_price: +ff[4] || 0, active_total: +ff[5] || 0,
};

// Sample-only split: quote-only lines (tagged 'quotes', by design) vs true backlog
const so = sql(`select count(*) filter (where tags ilike '%quotes%'), count(*) from shopify_products where status='ACTIVE' and not coalesce(has_product_variant,false)`)[0] || [];
const sampleOnly = { quote_only_by_design: +so[0] || 0, total: +so[1] || 0, backlog: (+so[1] || 0) - (+so[0] || 0) };
const sampleOnlyVendors = sql(`select vendor, count(*) from shopify_products where status='ACTIVE' and not coalesce(has_product_variant,false) group by 1 order by 2 desc limit 8`)
  .map(([v, c]) => ({ vendor: v, count: +c }));

/* --------------------------------------------- sample → roll funnel (Pool 2) ---
 * The $4.25 memo program is the top-of-funnel purchase-intent signal. Real
 * memo-order-vs-roll-order data is NOT in this mirror (it's a CATALOG mirror —
 * no shopify_orders table; mp_orders is 6 seed rows, digital_sample_orders 25
 * thin digital-only rows — see order_data below). So we quantify the STRUCTURAL
 * proxy the funnel demands: how many products a customer can memo-sample vs how
 * many of those they can then actually buy as a roll. The gap is split honestly
 * into quote-only-by-design (tagged 'quotes') and the recoverable backlog. */
const srf = sql(`select
    count(*) filter (where s)                          as sampleable,
    count(*) filter (where s and b)                    as converted,
    count(*) filter (where s and not b)                as gap,
    count(*) filter (where s and not b and q)          as gap_quote_only,
    count(*) filter (where s and not b and not q)      as gap_backlog
  from (select coalesce(has_sample_variant,false) s, coalesce(has_product_variant,false) b, tags ilike '%quotes%' q
        from shopify_products where status='ACTIVE') x`)[0] || [];
const sampleRollFunnel = {
  sampleable: +srf[0] || 0, converted: +srf[1] || 0, gap: +srf[2] || 0,
  gap_quote_only: +srf[3] || 0, gap_backlog: +srf[4] || 0,
  conversion_pct: (+srf[0]) ? Math.round(1000 * (+srf[1]) / (+srf[0])) / 10 : 0,
};
// Per-vendor recoverable leak: sampleable, not buyable, NOT quote-tagged — the
// lines where an existing memo customer hits a dead end. Forbidden upstream
// names filtered at the data layer (customer-facing private labels pass through).
const sampleRollVendors = sql(`select vendor,
    count(*) filter (where coalesce(has_sample_variant,false)) sampleable,
    count(*) filter (where coalesce(has_sample_variant,false) and coalesce(has_product_variant,false)) converted,
    count(*) filter (where coalesce(has_sample_variant,false) and not coalesce(has_product_variant,false) and tags not ilike '%quotes%') leak
  from shopify_products where status='ACTIVE' and vendor is not null
  group by vendor
  having count(*) filter (where coalesce(has_sample_variant,false) and not coalesce(has_product_variant,false) and tags not ilike '%quotes%') > 0
  order by leak desc limit 20`)
  .map(([vendor, sampleable, converted, leak]) => ({ vendor, sampleable: +sampleable, converted: +converted, leak: +leak }))
  .filter(v => !FORBIDDEN_NAMES.test(v.vendor)).slice(0, 10);
// Order-data provenance — proves WHY we use the proxy, not order history. These
// are the only order-shaped tables in the mirror and both are seed/thin.
const mpOrderTypes = sql(`select order_type, count(*) from mp_orders group by 1 order by 2 desc`).map(([t, c]) => ({ type: t, count: +c }));
const dso = sql(`select count(*), min(detected_at)::date, max(detected_at)::date from digital_sample_orders`)[0] || [];
const sampleRollOrderData = {
  mp_orders: { rows: mpOrderTypes.reduce((a, r) => a + r.count, 0), types: mpOrderTypes,
    note: 'Marketplace seed data — 6 identical $496 wallpaper rows, one date, no sample-type rows. Not real customer history.' },
  digital_sample_orders: { rows: +dso[0] || 0, span_start: dso[1] || null, span_end: dso[2] || null,
    note: 'Real but thin — DIGITAL sample orders only (not the $4.25 physical memo program); too few to compute a behavioral conversion rate.' },
  shopify_orders_present: false,
  note: 'dw_unified is a CATALOG mirror: no shopify_orders table. Real memo→roll order conversion is not measurable here — instrumenting it is the recommendation, not the finding.',
};

// Monthly activation ramp — 7 calendar months (the growth-story curve)
const monthly = sql(`select to_char(date_trunc('month',created_at_shopify),'Mon'), count(*)
  from shopify_products where status='ACTIVE' and created_at_shopify > now() - interval '7 months'
  group by date_trunc('month',created_at_shopify) order by date_trunc('month',created_at_shopify)`)
  .map(([m, c]) => ({ month: m, added: +c }));

// Workflow-tag backlog — the ops queue measured from the catalog's own tags
const needsTags = ['Needs-Price', 'Needs-Image', 'Needs-Width', 'Needs-Description'].map(t => ({
  tag: t, count: +(sql(`select count(*) from shopify_products where status='ACTIVE' and tags ilike '%${t}%'`)[0]?.[0] || 0),
})).filter(x => x.count > 0);

// Style + color mix over ACTIVE (tag-match — same buckets the storefront sorts use)
const STYLES = ['Traditional', 'Contemporary', 'Geometric', 'Floral', 'Damask', 'Stripe', 'Grasscloth', 'Textured', 'Metallic', 'Mural'];
const styleMix = STYLES.map(s => ({ style: s, count: +(sql(`select count(*) from shopify_products where status='ACTIVE' and tags ilike '%${s}%'`)[0]?.[0] || 0) }))
  .filter(x => x.count > 0).sort((a, b) => b.count - a.count);
const COLORS = [['White', '#f2efe8'], ['Beige', '#d9c9a8'], ['Gray', '#9a9a99'], ['Blue', '#5a7fa8'], ['Green', '#6b8f6d'], ['Gold', '#c9a24b'], ['Brown', '#8a6a4c'], ['Black', '#2b2b2b'], ['Pink', '#d3a0a6'], ['Red', '#a84a44']];
const colorMix = COLORS.map(([c, hex]) => ({ color: c, hex, count: +(sql(`select count(*) from shopify_products where status='ACTIVE' and (tags ilike '%color:${c}%' or tags ilike '%"${c}"%')`)[0]?.[0] || 0) }))
  .filter(x => x.count > 0).sort((a, b) => b.count - a.count);

// Real product gallery for the concept grids — ACTIVE, already customer-facing
// on the live store (cdn.shopify.com), forbidden-vendor-filtered, one per vendor.
const gallery = sql(`select distinct on (vendor) title, vendor, coalesce(nullif(trim(product_type),''),'Wallcovering'), image_url
  from shopify_products
  where status='ACTIVE' and image_url like 'https://cdn.shopify.com%' and title is not null
    and vendor is not null and length(title) between 8 and 60
  order by vendor, created_at_shopify desc limit 90`)
  .map(([title, vendor, type, image]) => ({ title, vendor, type, image }))
  .filter(g => !FORBIDDEN_NAMES.test(g.vendor + ' ' + g.title)).slice(0, 60);

// Activation cadence — new ACTIVEs per week, last 12 weeks (the rotation-activator drip)
const cadence = sql(`select to_char(date_trunc('week', created_at_shopify),'MM-DD'), count(*)
  from shopify_products where status='ACTIVE' and created_at_shopify > now() - interval '12 weeks'
  group by date_trunc('week', created_at_shopify) order by date_trunc('week', created_at_shopify)`)
  .map(([w, c]) => ({ week: w, added: +c }));

// Price architecture over ACTIVE sellable
const priceBands = sql(`select band, count(*) from (
    select case when min_variant_price < 50 then 'Under $50'
                when min_variant_price < 150 then '$50–150'
                when min_variant_price < 300 then '$150–300'
                when min_variant_price < 600 then '$300–600'
                else '$600+' end as band
    from shopify_products where status='ACTIVE' and min_variant_price > 4.25) x
  group by band order by min(case band when 'Under $50' then 1 when '$50–150' then 2 when '$150–300' then 3 when '$300–600' then 4 else 5 end)`)
  .map(([b, c]) => ({ band: b, count: +c }));

const drafts = (statuses.DRAFT || 0);
const staged = sql(`select count(*) from shopify_products where upper(status)='DRAFT' and coalesce(has_product_variant,false) and image_url is not null`)[0]?.[0];

const competitors = sql(`select name, domain, enabled, coalesce(products_found,0), coalesce(notes,'') from connie_competitors order by products_found desc`)
  .map(([name, domain, enabled, found, notes]) => ({ name, domain, enabled: enabled === 't', products_found: +found, notes: String(notes).replace(SCRUB, '').replace(/,\s*$/, '').replace(/\s{2,}/g, ' ') }));

/* ------------------------------------------------------------ live probes --- */
const [mcc, microsites, storefront] = await Promise.all([
  getJson('https://marketing.designerwallcoverings.com/api/channels/status', { auth: true }),
  getJson('https://all.designerwallcoverings.com/api/microsites', { auth: true }),
  getJson('https://www.designerwallcoverings.com/products.json?limit=1'),
]);

const channels = mcc?.platforms
  ? Object.entries(mcc.platforms).map(([k, v]) => ({
      key: k, label: v.label || k, connected: !!v.connected,
      accounts: Array.isArray(v.accounts) ? v.accounts.length : (v.connected ? 1 : 0),
      canPost: v.can_post ?? v.canPost ?? null, note: v.note || v.status || '',
    }))
  : [];

const siteList = Array.isArray(microsites) ? microsites
  : (microsites?.microsites || microsites?.sites || []);
// Hard rule: private-label upstream / internal-only vendor names must never
// render on any front end, gated or not — filter at the DATA layer so no
// downstream surface (fleet chips, JSON export) can leak them.
// FORBIDDEN is derived from FORBIDDEN_TERMS (hostname form) at the top of this file.
// HONEST per-site counts (DTD verdict B, 5/5, 2026-07-28): use handleCount as the
// per-site depth, NOT productCount. Why: productCount is a rolled-up figure the
// aggregator back-fills — e.g. the type=internal directory site all.dw reports
// productCount=201504 (the whole fleet) while its OWN handleCount is 49; cf-zone
// mirrors likewise report inflated productCounts. handleCount is the depth the
// crawler actually enumerated per host, and summing non-internal handleCounts
// (~124k) reconstructs the server's own honest total_handles (125,624) — proof
// it's the faithful signal. We therefore (1) DROP type=internal aggregator/
// directory sites entirely (they double-count the fleet), and (2) rank + report
// by handleCount. Some sites cap handleCount at 200 (feed page size) — a small,
// transparent UNDERSTATEMENT, far safer for a client deliverable than
// productCount's gross overstatement. The raw products[] array (titles + upstream
// image URLs) still must NEVER enter the snapshot.
const asCount = v => (Number.isFinite(v) && v >= 0 ? v : null);
// Site types that are NOT authentic per-site depth: directory/aggregator sites
// that roll the whole fleet up into one figure. Dropped from ranking + any sum.
const isAggregator = s => String(s.type || '').toLowerCase() === 'internal';
const fleet = (Array.isArray(siteList) ? siteList : [])
  .filter(s => !isAggregator(s))
  .map(s => {
    const handleCount = asCount(s.handleCount ?? s.handle_count);
    const productCount = asCount(s.productCount ?? s.product_count);
    const dbCount = asCount(s.activeProductsDB ?? s.active_products_db);
    return {
      host: s.host || s.subdomain || s.domain || String(s.name || ''),
      type: s.type || null,
      // Honest per-site depth = handleCount (truly-crawled). Fall back to the
      // mirror-backed DB count, then honest-null. productCount is retained only
      // as context (rolled-up figure), NEVER used as the displayed depth.
      products: handleCount ?? dbCount,
      handles: handleCount,
      products_rollup: productCount,
      products_db: dbCount,
      capped: handleCount === 200,   // feed page-size cap → likely understated
      ok: s.up ?? s.ok ?? s.reachable ?? null,
      feed: s.feed ?? null,
    };
  }).filter(s => s.host && !FORBIDDEN.test(s.host));

/* ----------------------------------------------------- traffic & search --- */
// GSC "Traffic & Search Reality" (DTD verdict A, 5/5, 2026-07-26): try the LIVE
// OpenSEO MCP at localhost:3001 first (freshest first-party GSC for
// sc-domain:designerwallcoverings.com); on unreachable/empty FALL BACK to the
// REAL cached pull the sibling dw-seo-report project already holds for the SAME
// property. Additive/optional — NEVER blocks the snapshot (no sanity-gate exit).
// GA4 is honest-null: no live property is provisioned for the main store (the
// analytics rollout is still 'awaiting ID').
const GSC_PROJECT = '20a5b2e9-ea7b-4645-964a-46f04c9992e7';
const GSC_PROPERTY = 'sc-domain:designerwallcoverings.com';
const SEO_CACHE = join(homedir(), 'Projects', 'dw-seo-report', 'data');
const GA4_NULL = { available: false, note: 'No GA4 property provisioned for the main store (analytics rollout still awaiting ID).' };

async function mcpGsc(dimensions, dateRange, rowLimit) {
  const payload = { jsonrpc: '2.0', id: 1, method: 'tools/call', params: {
    name: 'get_search_console_performance',
    arguments: { projectId: GSC_PROJECT, dimensions, dateRange, rowLimit } } };
  const r = await fetch('http://localhost:3001/mcp', {
    method: 'POST',
    headers: { 'Content-Type': 'application/json', Accept: 'application/json, text/event-stream' },
    body: JSON.stringify(payload), signal: AbortSignal.timeout(4000),
  });
  if (!r.ok) throw new Error('http ' + r.status);
  const text = await r.text(); // MCP replies as SSE — the JSON line is 'data: {...}'
  for (const raw of text.split('\n')) {
    const ln = raw.replace(/^data:\s*/, '').trim();
    if (!ln || ln[0] !== '{') continue;
    try { const j = JSON.parse(ln); const rows = j?.result?.structuredContent?.rows; if (Array.isArray(rows) && rows.length) return rows; } catch { /* keep scanning */ }
  }
  throw new Error('no rows');
}

async function collectTraffic() {
  let q, qp, dt, source, pulledAt;
  try {
    [q, qp, dt] = await Promise.all([
      mcpGsc(['query'], 'last_28_days', 1000),
      mcpGsc(['query', 'page'], 'last_28_days', 1000),
      mcpGsc(['date'], 'last_6_months', 200),
    ]);
    source = 'live-openseo'; pulledAt = new Date().toISOString();
  } catch (liveErr) {
    try { // fall back to the real cached dw-seo-report pull for the same property
      const rd = f => JSON.parse(readFileSync(join(SEO_CACHE, f), 'utf8')).rows || [];
      q = rd('q.json'); qp = rd('qp.json'); dt = rd('date.json');
      if (!q.length) throw new Error('cache empty');
      source = 'cached-dw-seo-report';
      pulledAt = statSync(join(SEO_CACHE, 'q.json')).mtime.toISOString();
    } catch (cacheErr) {
      return { available: false, source: 'unavailable', property: GSC_PROPERTY,
        note: `GSC unavailable at collection time (live: ${String(liveErr.message).slice(0, 60)}; cache: ${String(cacheErr.message).slice(0, 60)})`,
        ga4: GA4_NULL };
    }
  }
  const sum = (a, f) => a.reduce((s, r) => s + f(r), 0);
  const clicks = sum(q, r => r.clicks), impressions = sum(q, r => r.impressions);
  const wpos = impressions ? sum(q, r => r.position * r.impressions) / impressions : 0;
  const BR = /designer\s*wallcovering|designerwallcovering/i; // brand-term match
  const brandedClicks = sum(q.filter(r => BR.test(r.keys[0])), r => r.clicks);
  const topQueries = [...q].sort((a, b) => b.clicks - a.clicks).slice(0, 12)
    .map(r => ({ query: r.keys[0], clicks: r.clicks, impressions: r.impressions, ctr: r.ctr, position: r.position }));
  // Striking distance = page-2 rank (pos 8–20) with real demand (≥100 impr): one push from page 1
  const striking = q.filter(r => r.position >= 8 && r.position <= 20 && r.impressions >= 100)
    .sort((a, b) => b.impressions - a.impressions).slice(0, 10)
    .map(r => ({ query: r.keys[0], impressions: r.impressions, clicks: r.clicks, position: r.position }));
  const pg = {};
  qp.forEach(r => { const p = r.keys[1] || ''; (pg[p] ??= { clicks: 0, impressions: 0 }); pg[p].clicks += r.clicks; pg[p].impressions += r.impressions; });
  const topPages = Object.entries(pg).sort((a, b) => b[1].clicks - a[1].clicks).slice(0, 10)
    .map(([url, v]) => ({ path: url.replace(/^https?:\/\/[^/]+/, '') || '/', clicks: v.clicks, impressions: v.impressions }));
  const mo = {};
  dt.forEach(r => { const m = r.keys[0].slice(0, 7); (mo[m] ??= { clicks: 0, impressions: 0 }); mo[m].clicks += r.clicks; mo[m].impressions += r.impressions; });
  const monthly = Object.entries(mo).sort((a, b) => a[0].localeCompare(b[0]))
    .map(([ym, v]) => ({ ym, month: new Date(ym + '-01T00:00:00Z').toLocaleString('en-US', { month: 'short', timeZone: 'UTC' }), clicks: v.clicks, impressions: v.impressions }));
  const lastDate = dt.length ? dt[dt.length - 1].keys[0] : null;
  const ageDays = (Date.now() - new Date(pulledAt).getTime()) / 86400000;
  return {
    available: true, source, property: GSC_PROPERTY, pulled_at: pulledAt,
    stale: ageDays > 7, age_days: Math.round(ageDays), window: 'last 28 days', last_data_date: lastDate,
    totals_28d: { clicks, impressions, ctr: impressions ? clicks / impressions : 0, avg_position: wpos },
    branded_split: { branded_clicks: brandedClicks, nonbranded_clicks: clicks - brandedClicks, branded_pct: clicks ? brandedClicks / clicks : 0 },
    top_queries: topQueries, striking_distance: striking, top_pages: topPages, monthly_trend: monthly,
    ga4: GA4_NULL,
    note: source === 'cached-dw-seo-report'
      ? 'Real first-party GSC — cached from the OpenSEO pull the dw-seo-report project holds for this property (OpenSEO not reachable live at collection time).'
      : 'Live first-party GSC via the OpenSEO MCP at collection time.',
  };
}
const traffic = await collectTraffic();

const snapshot = {
  collected_at: new Date().toISOString(),
  sources: {
    mirror: 'dw_unified local mirror (host=/tmp) — READ ONLY',
    mcc: mcc?._error || mcc?._status ? `unreachable (${mcc._error || mcc._status})` : 'marketing.designerwallcoverings.com/api/channels/status',
    fleet: microsites?._error || microsites?._status ? `unreachable (${microsites._error || microsites._status})` : 'all.designerwallcoverings.com/api/microsites',
    storefront: storefront?._error || storefront?._status ? `probe ${storefront._error || storefront._status}` : 'live products.json OK',
    traffic: traffic.available ? `${traffic.source} (GSC ${GSC_PROPERTY})` : (traffic.source || 'unavailable'),
  },
  catalog: {
    statuses, vendors_active: +vendors || 0, top_vendors: topVendors, types,
    five_field: fiveField, activation_cadence: cadence, price_bands: priceBands,
    price_bands_basis: 'products whose minimum variant exceeds the $4.25 memo sample — the priced subset of the mirror (~3.8k); most actives resolve min-variant to the sample, and full variant-level pricing lives Shopify-side',
    drafts_total: drafts, drafts_stageable: +staged || 0,
    sample_only: sampleOnly, sample_only_vendors: sampleOnlyVendors,
    sample_roll: { funnel: sampleRollFunnel, vendors: sampleRollVendors, order_data: sampleRollOrderData },
    monthly_activation: monthly, needs_tags: needsTags, style_mix: styleMix, color_mix: colorMix,
  },
  channels,
  fleet: {
    count: fleet.length,   // authentic per-site fleet (aggregators + forbidden hosts dropped)
    // Fleet-level truth straight from the aggregator crawl (honest-null when absent):
    up: asCount(microsites?.up),                 // sites reachable at crawl time
    with_feed: asCount(microsites?.with_feed),   // sites exposing a product feed
    total_handles: asCount(microsites?.total_handles), // total product handles across the fleet
    shopify_products: asCount(microsites?.products),   // flagship Shopify products mirrored
    // The ONLY honest fleet-depth aggregate is the server's own total_handles
    // (above). We deliberately do NOT publish a naive sum of per-site depth: the
    // old products_total summed rolled-up productCounts and inflated the fleet
    // ~123x (DTD verdict B, 2026-07-28). For transparency we expose the sum of
    // the honest per-site handleCounts, which reconstructs total_handles closely.
    handles_sum: fleet.reduce((a, s) => a + (s.handles || 0), 0) || null,
    sites: fleet.slice(0, 60),
  },
  competitors,
  gallery,
  traffic,
  storefront_live: !(storefront?._error) && storefront?._status !== 404,
};

// Sanity gate (contrarian fix #2): never overwrite a good snapshot with a
// degraded one. A dead mirror or an empty channel read exits non-zero so
// /api/refresh returns 500 instead of silently serving hollow numbers.
if ((fiveField.active_total || 0) < 1000) {
  console.error(JSON.stringify({ ok: false, error: 'sanity: active_total < 1000 — mirror read failed; snapshot NOT written' }));
  process.exit(1);
}
if (channels.length === 0) {
  console.error(JSON.stringify({ ok: false, error: 'sanity: 0 channels — MCC unreachable; snapshot NOT written (previous snapshot preserved)' }));
  process.exit(1);
}
writeFileSync(join(OUT, 'dw-analysis.json'), JSON.stringify(snapshot, null, 2));
console.log(JSON.stringify({
  ok: true, collected_at: snapshot.collected_at,
  active: fiveField.active_total, vendors: snapshot.catalog.vendors_active,
  channels: channels.length, fleet_sites: fleet.length, competitors: competitors.length,
  mcc_ok: !mcc?._error && !mcc?._status, fleet_ok: !microsites?._error && !microsites?._status,
  traffic: traffic.available ? `${traffic.source}: ${traffic.totals_28d.clicks} clicks/28d (${traffic.stale ? 'stale ' + traffic.age_days + 'd' : 'fresh'})` : 'unavailable',
}, null, 2));