// ===========================
// Klaviyo.gs — Klaviyo → Google Sheets reporting template
//
// You should never need to edit this file. All account-specific values live
// in the CONFIG object in Config.gs — see SETUP.md for the walkthrough.
//
// CONFIG is always read inside functions (at run time), never at the top
// level of this file, so the two files work regardless of the order Apps
// Script loads them in.
// ===========================

// Logging
const LOG_DEBUG = false;

// Timezone accessor — all day-bucketing goes through this.
function tz_() {
  return CONFIG.TIMEZONE;
}

// Fail fast with a clear message if setup isn't finished, rather than a
// confusing API error deep in a report call.
function validateConfig_() {
  const problems = [];
  if (!CONFIG.BASE_METRIC_ID || CONFIG.BASE_METRIC_ID === "REPLACE_ME") {
    problems.push("CONFIG.BASE_METRIC_ID is not set (see Config.gs).");
  }
  (CONFIG.CONVERSION_METRICS || []).forEach((m, i) => {
    if (!m.label || !m.metricId || m.metricId === "REPLACE_ME") {
      problems.push(`CONFIG.CONVERSION_METRICS[${i}] is missing a label or metricId.`);
    }
  });
  if (problems.length) {
    throw new Error("Config not finished — fix these in Config.gs before running:\n" + problems.join("\n"));
  }
}

// ----------
// Entry points
// ----------

// Setup helper — run this once after adding your API key. Logs every metric
// and list in the account with its ID, for filling in CONFIG (Config.gs).
function klaviyoLogAccountInfo() {
  const metrics = fetchAllPages_("metrics");
  Logger.log(`--- Metrics (${metrics.length}) ---`);
  metrics.sort((a, b) => a.name.localeCompare(b.name))
    .forEach(m => Logger.log(`${m.id}  ${m.name}`));

  const lists = fetchAllPages_("lists");
  Logger.log(`--- Lists (${lists.length}) ---`);
  lists.sort((a, b) => a.name.localeCompare(b.name))
    .forEach(l => Logger.log(`${l.id}  ${l.name}`));
}

// Regular scheduled pull — rolling window (CONFIG.LOOKBACK_DAYS), upsert only.
// Point your time-based trigger at this function.
function klaviyoWeeklyPull() {
  validateConfig_();
  runPull_(CONFIG.LOOKBACK_DAYS);
}

// One-time email growth backfill — clears Raw_Email_Growth and re-populates from all-time
// profile history. Unsubscribes are sourced from whatever is currently in Raw_Campaign /
// Raw_Flow (max 365 days), so unsub counts before that window will show 0.
function klaviyoEmailGrowthBackfill() {
  clearSheetKeepHeader_(CONFIG.SHEET_EMAIL_GROWTH);
  const tf = timeframeWindow_(1825, 0); // 5-year lookback; extend if account is older
  writeEmailGrowthDaily_(tf);
  Logger.log("klaviyoEmailGrowthBackfill finished at " + new Date().toISOString());
}

// One-time full refresh — campaigns/flows backfilled 365 days in 60-day chunks
// (Klaviyo flow series API enforces a 60-day max for daily interval).
//
// Execution-time-safe: each 60-day chunk runs in its own trigger invocation so the
// 6-minute Apps Script limit is never hit.  Progress is stored in ScriptProperties.
//
// Usage: run klaviyoFullRefresh() once from the editor.  It clears the sheets,
// schedules klaviyoFullRefreshStep_ every 5 minutes, and fires the first step
// immediately.  Subsequent steps run automatically until done, then the trigger
// is deleted.  Check Executions log or the Logger output to track progress.
const REFRESH_KEY   = "KLAVIYO_REFRESH_STATE";
const REFRESH_CHUNK = 60;
const REFRESH_TOTAL = 365;

function klaviyoFullRefresh() {
  validateConfig_();

  // 1) Clear all raw sheets up-front (fast — do this in the main execution).
  clearSheetKeepHeader_(CONFIG.SHEET_CAMPAIGN);
  clearSheetKeepHeader_(CONFIG.SHEET_FLOW);
  clearSheetKeepHeader_(CONFIG.SHEET_EMAIL_GROWTH);
  clearSheetKeepHeader_(CONFIG.SHEET_SMS_GROWTH);

  // 2) Remove any leftover step triggers from a previous run.
  _deleteRefreshTriggers_();

  // 3) Persist initial state.
  PropertiesService.getScriptProperties()
    .setProperty(REFRESH_KEY, JSON.stringify({ offset: 0, growthDone: false }));

  // 4) Schedule a 5-minute recurring trigger for the step function.
  // (Valid values: 1, 5, 10, 15, 30 — Apps Script rejects anything else.)
  ScriptApp.newTrigger("klaviyoFullRefreshStep_")
    .timeBased().everyMinutes(5).create();

  // 5) Run the first step immediately so chunk 0 starts right now.
  klaviyoFullRefreshStep_();
  Logger.log("klaviyoFullRefresh: first step done — remaining chunks will run via trigger.");
}

// Called by the 5-minute trigger (and directly for the first chunk).
// Processes exactly one 60-day window per invocation, then saves updated offset.
// When all chunks + growth data are done, deletes the trigger.
function klaviyoFullRefreshStep_() {
  // Prevent concurrent runs in case the trigger fires before the previous step finishes.
  const lock = LockService.getScriptLock();
  if (!lock.tryLock(10000)) {
    Logger.log("[FullRefresh] step skipped — another instance is still running.");
    return;
  }

  try {
    const props = PropertiesService.getScriptProperties();
    const raw   = props.getProperty(REFRESH_KEY);
    if (!raw) {
      // No state → nothing to do (trigger may be stale).
      _deleteRefreshTriggers_();
      return;
    }

    let state;
    try { state = JSON.parse(raw); }
    catch (e) { props.deleteProperty(REFRESH_KEY); _deleteRefreshTriggers_(); return; }

    // ── Phase 1: campaign / flow chunks ──────────────────────────────────────
    if (state.offset < REFRESH_TOTAL) {
      const days = Math.min(REFRESH_CHUNK, REFRESH_TOTAL - state.offset);
      Logger.log(`[FullRefresh] chunk offset=${state.offset}d  days=${days}  (${new Date().toISOString()})`);
      runCampaignFlowPull_(days, state.offset);
      state.offset += REFRESH_CHUNK;
      props.setProperty(REFRESH_KEY, JSON.stringify(state));
      return; // next step fires in ~5 min
    }

    // ── Phase 2: email + SMS growth (runs once after all chunks) ─────────────
    if (!state.growthDone) {
      Logger.log("[FullRefresh] writing growth data…");
      const tfGrowth = timeframeLastNDays_(CONFIG.LOOKBACK_DAYS);
      writeEmailGrowthDaily_(tfGrowth);
      writeSMSGrowthDaily_(tfGrowth);
      state.growthDone = true;
      props.setProperty(REFRESH_KEY, JSON.stringify(state));
      return; // trigger fires once more → hits the "all done" block below
    }

    // ── All done ─────────────────────────────────────────────────────────────
    _deleteRefreshTriggers_();
    props.deleteProperty(REFRESH_KEY);
    Logger.log("[FullRefresh] completed at " + new Date().toISOString());

  } finally {
    lock.releaseLock();
  }
}

// Deletes every time-based trigger whose handler is klaviyoFullRefreshStep_.
function _deleteRefreshTriggers_() {
  ScriptApp.getProjectTriggers()
    .filter(t => t.getHandlerFunction() === "klaviyoFullRefreshStep_")
    .forEach(t => ScriptApp.deleteTrigger(t));
}

function clearSheetKeepHeader_(sheetName) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sh = ss.getSheetByName(sheetName);
  if (!sh || sh.getLastRow() < 2) return;
  sh.deleteRows(2, sh.getLastRow() - 1);
  Logger.log(`Cleared data rows from ${sheetName}`);
}

// Campaigns + flows only (no profile pagination).
// offsetDays lets klaviyoFullRefresh pull historical windows (e.g. 60d starting 120d ago).
function runCampaignFlowPull_(days, offsetDays) {
  offsetDays = offsetDays || 0;
  Logger.log(`runCampaignFlowPull_(${days}d, offset=${offsetDays}d) started at ` + new Date().toISOString());

  const timeframe = timeframeWindow_(days, offsetDays);
  const interval  = "daily";
  const convMetrics = CONFIG.CONVERSION_METRICS || [];

  const engagementStats = [
    "recipients",
    "opens", "opens_unique", "open_rate",
    "clicks", "clicks_unique", "click_rate",
    "unsubscribes", "unsubscribe_uniques", "unsubscribe_rate",
    "spam_complaints", "spam_complaint_rate",
    "bounced", "bounce_rate"
  ];

  // 1) Campaigns: base engagement stats + one report per configured conversion metric
  const campaignBase = runCampaignValuesReport_(timeframe, CONFIG.BASE_METRIC_ID, engagementStats);
  Utilities.sleep(1200);

  const campaignConversions = convMetrics.map(m => {
    const json = runCampaignValuesReport_(timeframe, m.metricId, ["conversion_uniques"]);
    Utilities.sleep(1200);
    return { label: m.label, json };
  });

  const mergedCampaign = mergeConversions_(campaignBase, campaignConversions, "campaign_message_id");

  // 2) Flows: daily series base stats + one report per configured conversion metric
  const flowSeries = runFlowSeriesReport_(timeframe, CONFIG.BASE_METRIC_ID, engagementStats, interval);
  Utilities.sleep(1200);

  const flowConversions = convMetrics.map(m => {
    const json = runFlowSeriesReport_(timeframe, m.metricId, ["conversion_uniques"], interval);
    Utilities.sleep(1200);
    return { label: m.label, json };
  });

  // 3) Metadata: campaigns + flow messages
  const campaignIds = Array.from(new Set((mergedCampaign.results || [])
    .map(r => r.groupings?.campaign_id)
    .filter(Boolean)));

  const campaignMessageMetaIndex = fetchCampaignMessageMetaIndexByCampaign_(campaignIds);
  const campaignParentMetaMap    = fetchCampaignMetaMap_(campaignIds);

  const flowMessageIds = Array.from(new Set((flowSeries?.data?.attributes?.results || [])
    .map(r => r.groupings?.flow_message_id)
    .filter(Boolean)));

  const flowMetaMap = fetchFlowMessageMetaMap_(flowMessageIds);

  // 4) Write campaign + flow sheets
  writeCampaignSheet_(mergedCampaign, campaignMessageMetaIndex, campaignParentMetaMap);
  const cutoffYmd = Utilities.formatDate(new Date(timeframe.start), tz_(), "yyyy-MM-dd");
  writeFlowSheetDaily_(flowSeries, flowConversions, flowMetaMap, cutoffYmd);

  Logger.log(`runCampaignFlowPull_(${days}d) finished at ` + new Date().toISOString());
}

// Full daily run: campaigns + flows + email growth + SMS growth. Used by klaviyoWeeklyPull.
function runPull_(days) {
  Logger.log(`runPull_(${days}d) started at ` + new Date().toISOString());
  runCampaignFlowPull_(days);
  // Growth functions use a 2-day window (yesterday + today) instead of the full campaign/flow
  // lookback. Profile created timestamps are immutable so there is nothing to retroactively
  // update, and a short window avoids paginating thousands of profiles mid-run and timing out.
  const growthTimeframe = timeframeLastNDays_(2);
  writeEmailGrowthDaily_(growthTimeframe);
  writeSMSGrowthDaily_(growthTimeframe);
  Logger.log(`runPull_(${days}d) finished at ` + new Date().toISOString());
}

// ===========================
// Timeframe helpers
// ===========================
function timeframeLastNDays_(days) {
  return timeframeWindow_(days, 0);
}
function timeframeWindow_(days, offsetDays) {
  const end   = new Date(Date.now() - offsetDays * 86400000);
  const start = new Date(end.getTime() - days * 86400000);
  return { start: start.toISOString(), end: end.toISOString() };
}
function ymd_(d) {
  return Utilities.formatDate(d, tz_(), "yyyy-MM-dd");
}

// ===========================
// Logging helpers
// ===========================
function logDebug_(msg) {
  if (LOG_DEBUG) Logger.log(msg);
}
function isNotFoundErr_(e) {
  const msg = String(e && e.message ? e.message : e);
  return msg.includes("404") || msg.includes("not_found") || msg.includes("does not exist");
}
function logErr_(msg, e, { quiet404 = true } = {}) {
  if (quiet404 && isNotFoundErr_(e)) return;
  Logger.log(`${msg}: ${String(e && e.message ? e.message : e)}`);
}

// ===========================
// Reports
// ===========================
function runCampaignValuesReport_(timeframe, conversionMetricId, statistics) {
  const payload = {
    data: {
      type: "campaign-values-report",
      attributes: {
        timeframe,
        statistics,
        group_by: ["campaign_message_id", "campaign_id", "campaign_message_name", "send_channel", "variation_name"],
        conversion_metric_id: conversionMetricId
      }
    }
  };

  const url = `${KLAVIYO_BASE}/campaign-values-reports/`;
  const res = klaviyoFetch_(url, {
    method: "post",
    contentType: "application/json",
    payload: JSON.stringify(payload)
  });

  return res.json;
}

function runFlowSeriesReport_(timeframe, conversionMetricId, statistics, interval) {
  const payload = {
    data: {
      type: "flow-series-report",
      attributes: {
        timeframe,
        interval,
        statistics,
        group_by: ["flow_message_id", "flow_id", "flow_message_name", "flow_name", "send_channel", "variation_name"],
        conversion_metric_id: conversionMetricId
      }
    }
  };

  const url = `${KLAVIYO_BASE}/flow-series-reports/`;
  const res = klaviyoFetch_(url, {
    method: "post",
    contentType: "application/json",
    payload: JSON.stringify(payload)
  });

  return res.json;
}

// ===========================
// Merge conversions into base report results
// conversionEntries: array of { label, json } — one per CONFIG.CONVERSION_METRICS entry.
// Each row's merged statistics gets a `conversions` object keyed by label,
// e.g. r.statistics.conversions["Order Completed"] = 12
// ===========================
function mergeConversions_(baseJson, conversionEntries, idKey) {
  const base = (baseJson?.data?.attributes?.results || []);

  // Composite key: message_id + "|" + variation_name (for A/B variants).
  const makeKey_   = r => {
    const id = String(r.groupings?.[idKey] || "").trim();
    const v  = String(r.groupings?.variation_name || "").trim();
    return v ? `${id}|${v}` : id;
  };
  // ID-only key: used as a fallback when the conversion API doesn't return
  // per-variant breakdowns (variation_name absent or differs from the base report).
  const makeIdKey_ = r => String(r.groupings?.[idKey] || "").trim();

  // For each configured conversion metric, build both a composite-key map and
  // an ID-only fallback map.
  const perMetric = (conversionEntries || []).map(entry => {
    const rows = (entry.json?.data?.attributes?.results || []);
    const map   = new Map(rows.map(r => [makeKey_(r),   r.statistics?.conversion_uniques ?? 0]));
    const idMap = new Map(rows.map(r => [makeIdKey_(r), r.statistics?.conversion_uniques ?? 0]));
    return { label: entry.label, rows, map, idMap };
  });

  // Diagnostic — check logs if conversion columns show 0 for all rows.
  Logger.log(
    `mergeConversions_(${idKey}): base=${base.length} ` +
    perMetric.map(m => `${m.label}=${m.rows.length}`).join(" ")
  );
  perMetric.forEach(m => {
    if (m.rows.length === 0) {
      Logger.log(`  WARNING: "${m.label}" conversion report returned 0 rows`);
    } else {
      Logger.log(`  "${m.label}" sample key="${makeKey_(m.rows[0])}"  idKey="${makeIdKey_(m.rows[0])}"`);
    }
  });

  // Lookup: composite key first; fall back to ID-only if the conversion API
  // didn't return a variant-level row for this message.
  const lookup_ = (map, idMap, key, idOnlyKey) => {
    const v = map.get(key);
    return v !== undefined ? v : (idMap.get(idOnlyKey) ?? 0);
  };

  const merged = base.map(r => {
    const key       = makeKey_(r);
    const idOnlyKey = makeIdKey_(r);

    const conversions = {};
    perMetric.forEach(m => {
      conversions[m.label] = lookup_(m.map, m.idMap, key, idOnlyKey);
    });

    return {
      groupings: r.groupings || {},
      statistics: Object.assign({}, r.statistics || {}, { conversions })
    };
  });

  return { results: merged };
}

// ===========================
// Sheet writers: Campaign
// ===========================

// Canonical ordered column list for Raw_Campaign.
// Any header not already present in row 1 will be appended automatically.
// Computed as a function (not a top-level const) so it always reflects the
// current CONFIG.CONVERSION_METRICS, including however many metrics you've configured.
function campaignHeaders_() {
  const convHeaders = (CONFIG.CONVERSION_METRICS || []).map(m => `Unique ${m.label}`);
  return [
    "Send Date", "Send Time", "Campaign Message ID", "Campaign Message Name",
    "Variant Name", "Campaign Message Channel",
    "Total Recipients", "Total Opens", "Unique Opens", "Open Rate",
    "Total Clicks", "Unique Clicks", "Click Rate",
    "Total Unsubscribes", "Unique Unsubscribes", "Unsubscribe Rate",
    "Spam Complaints", "Spam Complaints Rate", "Bounces", "Bounce Rate",
    ...convHeaders,
    "Subject", "Preview Text", "Tags", "List", "Excluded List"
  ];
}

function writeCampaignSheet_(merged, campaignMessageMetaIndex, campaignMetaMap) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sh = ss.getSheetByName(CONFIG.SHEET_CAMPAIGN) || ss.insertSheet(CONFIG.SHEET_CAMPAIGN);

  // ── Ensure all expected headers exist in row 1 ──────────────────────────
  // Re-read after potential appends so the array is always fresh.
  let lastCol = sh.getLastColumn();
  if (lastCol < 1) throw new Error(`${CONFIG.SHEET_CAMPAIGN} has no columns. Add header row first.`);

  let headers = sh.getRange(1, 1, 1, lastCol).getValues()[0].map(h => String(h || "").trim());

  const missing = campaignHeaders_().filter(h => !headers.includes(h));
  if (missing.length) {
    sh.getRange(1, lastCol + 1, 1, missing.length).setValues([missing]);
    headers = headers.concat(missing);
    Logger.log(`${CONFIG.SHEET_CAMPAIGN}: auto-added headers: ${missing.join(", ")}`);
  }
  // ────────────────────────────────────────────────────────────────────────

  const idCol      = headers.indexOf("Campaign Message ID") + 1;
  if (idCol < 1) throw new Error(`${CONFIG.SHEET_CAMPAIGN} missing required header: Campaign Message ID`);
  const variantCol = headers.indexOf("Variant Name") + 1; // 0 if absent

  const idRowMap = buildExistingIdRowMap_(sh, idCol, variantCol || undefined);

  const rowsAll = (merged?.results || []).map(r => {
    const g = r.groupings || {};
    const s = r.statistics || {};

    const msgId      = g.campaign_message_id || "";
    const campaignId = g.campaign_id || "";

    const msgMeta = resolveCampaignMessageMeta_(
      campaignMessageMetaIndex,
      campaignId,
      g.campaign_message_name,
      g.variation_name,
      g.send_channel
    );

    const campMeta    = campaignMetaMap?.[campaignId] || {};
    const sendUse     = parseToDate_(campMeta.sendTs);
    const sendDateKey = sendUse ? Utilities.formatDate(sendUse, tz_(), "yyyy-MM-dd") : "";
    const sendTimeStr = sendUse ? Utilities.formatDate(sendUse, tz_(), "HH:mm:ss") : "";

    const obj = {
      "Send Date":               sendDateKey,
      "Send Time":               sendTimeStr,
      "Campaign Message ID":     msgId,
      "Campaign Message Name":   g.campaign_message_name || "",
      "Variant Name":            g.variation_name || "",
      "Campaign Message Channel": g.send_channel || "",

      "Total Recipients":   s.recipients ?? 0,
      "Total Opens":        s.opens ?? 0,
      "Unique Opens":       s.opens_unique ?? 0,
      "Open Rate":          s.open_rate ?? 0,
      "Total Clicks":       s.clicks ?? 0,
      "Unique Clicks":      s.clicks_unique ?? 0,
      "Click Rate":         s.click_rate ?? 0,
      "Total Unsubscribes": s.unsubscribes ?? 0,
      "Unique Unsubscribes": s.unsubscribe_uniques ?? 0,
      "Unsubscribe Rate":   s.unsubscribe_rate ?? 0,
      "Spam Complaints":    s.spam_complaints ?? 0,
      "Spam Complaints Rate": s.spam_complaint_rate ?? 0,
      "Bounces":            s.bounced ?? 0,
      "Bounce Rate":        s.bounce_rate ?? 0,

      "Subject":        msgMeta.subject || "",
      "Preview Text":   msgMeta.previewText || "",
      "Tags":           campMeta.tags || "",
      "List":           campMeta.list || "",
      "Excluded List":  campMeta.excludedList || ""
    };

    (CONFIG.CONVERSION_METRICS || []).forEach(m => {
      obj[`Unique ${m.label}`] = s.conversions?.[m.label] ?? 0;
    });

    // Composite upsert key so A/B variants don't clobber each other
    const variantName = g.variation_name || "";
    const upsertKey   = variantName ? `${msgId}|${variantName}` : msgId;
    return { key: upsertKey, row: headers.map(h => (h in obj ? obj[h] : "")) };
  });

  let updated = 0;
  const toAppend = [];

  rowsAll.forEach(x => {
    const id = String(x.key || "").trim();
    if (!id) return;

    const existingRow = idRowMap.get(id);
    if (existingRow) {
      sh.getRange(existingRow, 1, 1, headers.length).setValues([x.row]);
      updated++;
    } else {
      toAppend.push(x.row);
    }
  });

  if (toAppend.length) {
    sh.getRange(sh.getLastRow() + 1, 1, toAppend.length, headers.length).setValues(toAppend);
  }

  Logger.log(`Campaign upsert: incoming ${rowsAll.length}, updated ${updated}, appended ${toAppend.length}`);
}

// ===========================
// Sheet writers: Flow (Daily)
// ===========================
// Canonical ordered column list for Raw_Flow. Same auto-add-missing-headers
// pattern as Raw_Campaign — see campaignHeaders_().
function flowHeaders_() {
  const convHeaders = (CONFIG.CONVERSION_METRICS || []).map(m => `Unique ${m.label}`);
  return [
    "Day", "Flow ID", "Flow Name", "Message ID", "Message Name",
    "Message Channel", "Variant Name",
    "Total Recipients", "Total Opens", "Unique Opens", "Open Rate",
    "Total Clicks", "Unique Clicks", "Click Rate",
    "Total Unsubscribes", "Unique Unsubscribes", "Unsubscribe Rate",
    "Spam Complaints", "Spam Complaints Rate", "Bounces", "Bounce Rate",
    ...convHeaders,
    "Tags", "Message Status", "A/B Test Name", "Winning Variant"
  ];
}

function writeFlowSheetDaily_(flowSeriesJson, flowConversions, flowMetaMap, cutoffYmd) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sh = ss.getSheetByName(CONFIG.SHEET_FLOW) || ss.insertSheet(CONFIG.SHEET_FLOW);

  let lastCol = sh.getLastColumn();
  if (lastCol < 1) throw new Error(`${CONFIG.SHEET_FLOW} has no columns. Add header row first.`);

  let headers = sh.getRange(1, 1, 1, lastCol).getValues()[0].map(h => String(h || "").trim());

  // Auto-add any missing headers (e.g. a newly-added conversion metric) —
  // mirrors the same pattern used in writeCampaignSheet_ so nothing silently
  // fails to write just because a column wasn't set up ahead of time.
  const missing = flowHeaders_().filter(h => !headers.includes(h));
  if (missing.length) {
    sh.getRange(1, lastCol + 1, 1, missing.length).setValues([missing]);
    headers = headers.concat(missing);
    lastCol = headers.length;
    Logger.log(`${CONFIG.SHEET_FLOW}: auto-added headers: ${missing.join(", ")}`);
  }

  const dayCol      = headers.indexOf("Day") + 1;
  const idCol       = headers.indexOf("Message ID") + 1;
  const variantCol  = headers.indexOf("Variant Name") + 1; // 0 if absent
  if (dayCol < 1 || idCol < 1) throw new Error(`${CONFIG.SHEET_FLOW} missing required headers: Day, Message ID`);
  const existingMap = buildExistingFlowKeyRowMap_(sh, dayCol, idCol, variantCol || 0, cutoffYmd);

  const baseDates   = flowSeriesJson?.data?.attributes?.date_times || [];
  const baseResults = flowSeriesJson?.data?.attributes?.results    || [];

  // Composite key: flow_message_id + "|" + variation_name so A/B variants get their own metrics
  const flowConvKey_ = r => {
    const id = String(r?.groupings?.flow_message_id || "").trim();
    const v  = String(r?.groupings?.variation_name  || "").trim();
    return v ? `${id}|${v}` : id;
  };

  // One lookup Map per configured conversion metric, keyed by label.
  const convMapsByLabel = {};
  (flowConversions || []).forEach(entry => {
    const rows = entry.json?.data?.attributes?.results || [];
    convMapsByLabel[entry.label] = new Map(rows.map(r => [flowConvKey_(r), r]));
  });

  const updates = [];
  const appends = [];

  baseResults.forEach(r => {
    const g  = r.groupings || {};
    const id = String(g.flow_message_id || "").trim();
    if (!id) return;

    const meta      = flowMetaMap?.[id] || {};
    const s         = r.statistics || {};
    const variant   = String(g.variation_name || "").trim();
    const lookupKey = variant ? `${id}|${variant}` : id;

    // Grab each configured metric's matching row (if any) up front.
    const convRowByLabel = {};
    Object.keys(convMapsByLabel).forEach(label => {
      convRowByLabel[label] = convMapsByLabel[label].get(lookupKey);
    });

    for (let idx = 0; idx < baseDates.length; idx++) {
      const day = String(baseDates[idx]).slice(0, 10);
      if (day < cutoffYmd) continue;

      const key = variant ? `${day}|${id}|${variant}` : `${day}|${id}`;

      const obj = {
        "Day":             day,
        "Flow ID":         g.flow_id || "",
        "Flow Name":       g.flow_name || "",
        "Message ID":      id,
        "Message Name":    g.flow_message_name || "",
        "Message Channel": g.send_channel || "",
        "Variant Name":    g.variation_name || "",

        "Total Recipients":   (s.recipients || [])[idx] ?? 0,
        "Total Opens":        (s.opens || [])[idx] ?? 0,
        "Unique Opens":       (s.opens_unique || [])[idx] ?? 0,
        "Open Rate":          (s.open_rate || [])[idx] ?? 0,
        "Total Clicks":       (s.clicks || [])[idx] ?? 0,
        "Unique Clicks":      (s.clicks_unique || [])[idx] ?? 0,
        "Click Rate":         (s.click_rate || [])[idx] ?? 0,
        "Total Unsubscribes": (s.unsubscribes || [])[idx] ?? 0,
        "Unique Unsubscribes": (s.unsubscribe_uniques || [])[idx] ?? 0,
        "Unsubscribe Rate":   (s.unsubscribe_rate || [])[idx] ?? 0,
        "Spam Complaints":    (s.spam_complaints || [])[idx] ?? 0,
        "Spam Complaints Rate": (s.spam_complaint_rate || [])[idx] ?? 0,
        "Bounces":            (s.bounced || [])[idx] ?? 0,
        "Bounce Rate":        (s.bounce_rate || [])[idx] ?? 0,

        "Tags":             meta.tags || "",
        "Message Status":   meta.status || "",
        "A/B Test Name":    meta.abTestName || "",
        "Winning Variant":  meta.winningVariant || ""
      };

      (CONFIG.CONVERSION_METRICS || []).forEach(m => {
        const convRow = convRowByLabel[m.label];
        obj[`Unique ${m.label}`] = (convRow?.statistics?.conversion_uniques || [])[idx] ?? 0;
      });

      const rowValues      = headers.map(h => (h in obj ? obj[h] : ""));
      const existingRowNum = existingMap.get(key);

      if (existingRowNum) updates.push({ rowNumber: existingRowNum, rowValues });
      else appends.push(rowValues);
    }
  });

  updates.forEach(u => sh.getRange(u.rowNumber, 1, 1, headers.length).setValues([u.rowValues]));
  if (appends.length) sh.getRange(sh.getLastRow() + 1, 1, appends.length, headers.length).setValues(appends);

  Logger.log(`Flow upsert: updated ${updates.length}, appended ${appends.length}, cutoff >= ${cutoffYmd}`);
}

// ===========================
// Email Growth (New Profiles w/ Email + Unsubs from Campaign/Flow sheets)
// ===========================
function writeEmailGrowthDaily_(timeframe) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sh = ss.getSheetByName(CONFIG.SHEET_EMAIL_GROWTH) || ss.insertSheet(CONFIG.SHEET_EMAIL_GROWTH);

  const headers = ["Day", "New Profiles (Email)", "Email Unsubscribes", "Net New"];

  if (sh.getLastRow() === 0) {
    sh.getRange(1, 1, 1, headers.length).setValues([headers]);
  } else {
    const existing = sh.getRange(1, 1, 1, headers.length).getValues()[0];
    const mismatch = headers.some((h, i) => String(existing[i] || "").trim() !== h);
    if (mismatch) sh.getRange(1, 1, 1, headers.length).setValues([headers]);
  }

  const cutoffYmd = Utilities.formatDate(new Date(timeframe.start), tz_(), "yyyy-MM-dd");

  // 1) New profiles created with an email address
  const newProfilesByDay = queryNewEmailProfilesCountByDay_(timeframe);
  Logger.log("New email profiles total = " + Object.values(newProfilesByDay).reduce((a, b) => a + b, 0));
  Logger.log("Days w/ counts = " + Object.keys(newProfilesByDay).length);
  Logger.log("Sample days = " + Object.keys(newProfilesByDay).slice(0, 10).join(", "));

  // 2) Unsubscribes aggregated from already-written Raw_Campaign + Raw_Flow sheets
  const unsubsByDay = buildUnsubsByDayFromSheets_(cutoffYmd);

  const days        = listDays_(timeframe.start, timeframe.end).filter(d => d >= cutoffYmd);
  const existingMap = buildExistingDayRowMap_(sh, 1, cutoffYmd);

  const toAppend = [];
  let updated = 0;

  days.forEach(day => {
    const newP = Number(newProfilesByDay?.[day] || 0);
    const un   = Number(unsubsByDay?.[day]       || 0);
    const net  = newP - un;

    const row         = [day, newP, un, net];
    const existingRow = existingMap.get(day);

    if (existingRow) {
      sh.getRange(existingRow, 1, 1, headers.length).setValues([row]);
      updated++;
    } else {
      toAppend.push(row);
    }
  });

  if (toAppend.length) {
    sh.getRange(sh.getLastRow() + 1, 1, toAppend.length, headers.length).setValues(toAppend);
  }

  Logger.log(`Email growth upsert: updated ${updated}, appended ${toAppend.length}`);
}

// ===========================
// Aggregate unsubscribes from Raw_Campaign + Raw_Flow sheets by day
// Called after both sheets have been written in the same run.
// ===========================
function buildUnsubsByDayFromSheets_(cutoffYmd) {
  const ss  = SpreadsheetApp.getActiveSpreadsheet();
  const out = {};

  // Flow: sum "Unique Unsubscribes" by "Day"
  const shFlow = ss.getSheetByName(CONFIG.SHEET_FLOW);
  if (shFlow && shFlow.getLastRow() > 1) {
    const flowData = shFlow.getDataRange().getValues();
    const fh       = flowData[0].map(h => String(h || "").trim());
    const idxDay   = fh.indexOf("Day");
    const idxU     = fh.indexOf("Unique Unsubscribes");
    if (idxDay >= 0 && idxU >= 0) {
      for (let r = 1; r < flowData.length; r++) {
        const dayVal = flowData[r][idxDay];
        if (!dayVal) continue;
        const day = (dayVal instanceof Date) ? ymd_(dayVal) : String(dayVal).slice(0, 10);
        if (day < cutoffYmd) continue;
        const v = Number(flowData[r][idxU] || 0);
        if (v) out[day] = (out[day] || 0) + v;
      }
    }
  }

  // Campaign: sum "Unique Unsubscribes" by "Send Date"
  const shCamp = ss.getSheetByName(CONFIG.SHEET_CAMPAIGN);
  if (shCamp && shCamp.getLastRow() > 1) {
    const campData    = shCamp.getDataRange().getValues();
    const ch          = campData[0].map(h => String(h || "").trim());
    const idxSendDate = ch.indexOf("Send Date");
    const idxU        = ch.indexOf("Unique Unsubscribes");
    if (idxSendDate >= 0 && idxU >= 0) {
      for (let r = 1; r < campData.length; r++) {
        const dayVal = campData[r][idxSendDate];
        if (!dayVal) continue;
        const day = (dayVal instanceof Date) ? ymd_(dayVal) : String(dayVal).slice(0, 10);
        if (day < cutoffYmd) continue;
        const v = Number(campData[r][idxU] || 0);
        if (v) out[day] = (out[day] || 0) + v;
      }
    }
  }

  return out;
}

// ===========================
// SMS Growth (List Subscribes + Unsubscribes)
// Optional — skipped entirely if CONFIG.SMS_LIST_ID is blank. If only
// SMS_UNSUBSCRIBE_METRIC_ID is blank, subscribes are still tracked and the
// unsubscribe column reports 0.
// ===========================
function writeSMSGrowthDaily_(timeframe) {
  if (!CONFIG.SMS_LIST_ID) {
    Logger.log("writeSMSGrowthDaily_: skipped — CONFIG.SMS_LIST_ID not set.");
    return;
  }

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sh = ss.getSheetByName(CONFIG.SHEET_SMS_GROWTH) || ss.insertSheet(CONFIG.SHEET_SMS_GROWTH);

  const headers = ["Day", "SMS Subscribes", "SMS Unsubscribes", "Net New"];

  if (sh.getLastRow() === 0) {
    sh.getRange(1, 1, 1, headers.length).setValues([headers]);
  } else {
    const existing = sh.getRange(1, 1, 1, headers.length).getValues()[0];
    const mismatch = headers.some((h, i) => String(existing[i] || "").trim() !== h);
    if (mismatch) sh.getRange(1, 1, 1, headers.length).setValues([headers]);
  }

  const cutoffYmd = Utilities.formatDate(new Date(timeframe.start), tz_(), "yyyy-MM-dd");

  // 1) SMS subscribes: profiles added to the SMS list, counted by consent date
  const subscribesByDay = querySMSSubscribesByDay_(timeframe);
  Logger.log("SMS subscribes total = " + Object.values(subscribesByDay).reduce((a, b) => a + b, 0));

  // 2) SMS unsubscribes from metric-aggregates (optional)
  let unsubsByDay = {};
  if (CONFIG.SMS_UNSUBSCRIBE_METRIC_ID) {
    try {
      unsubsByDay = queryMetricCountByDay_(CONFIG.SMS_UNSUBSCRIBE_METRIC_ID, timeframe, "day");
    } catch (e) {
      logErr_("SMS unsub metric-aggregates interval=day failed; retrying interval=daily", e, { quiet404: false });
      unsubsByDay = queryMetricCountByDay_(CONFIG.SMS_UNSUBSCRIBE_METRIC_ID, timeframe, "daily");
    }
  } else {
    Logger.log("SMS unsubscribes reported as 0 — set CONFIG.SMS_UNSUBSCRIBE_METRIC_ID to track them.");
  }

  const days        = listDays_(timeframe.start, timeframe.end).filter(d => d >= cutoffYmd);
  const existingMap = buildExistingDayRowMap_(sh, 1, cutoffYmd);

  const toAppend = [];
  let updated = 0;

  days.forEach(day => {
    const subs   = Number(subscribesByDay?.[day] || 0);
    const unsubs = Number(unsubsByDay?.[day]      || 0);
    const net    = subs - unsubs;

    const row         = [day, subs, unsubs, net];
    const existingRow = existingMap.get(day);

    if (existingRow) {
      sh.getRange(existingRow, 1, 1, headers.length).setValues([row]);
      updated++;
    } else {
      toAppend.push(row);
    }
  });

  if (toAppend.length) {
    sh.getRange(sh.getLastRow() + 1, 1, toAppend.length, headers.length).setValues(toAppend);
  }

  Logger.log(`SMS growth upsert: updated ${updated}, appended ${toAppend.length}`);
}

// ===========================
// SMS: page through list members and count by SMS consent date
// ===========================
function querySMSSubscribesByDay_(timeframe) {
  const out       = {};
  const startDate = new Date(timeframe.start);
  const endDate   = new Date(timeframe.end);

  let url = `${KLAVIYO_BASE}/lists/${CONFIG.SMS_LIST_ID}/profiles/?` +
    `additional-fields[profile]=subscriptions&` +
    `fields[profile]=phone_number,subscriptions&` +
    `page[size]=100`;

  while (url) {
    const res  = klaviyoFetch_(url, { method: "get" });
    const data = res.json?.data || [];

    for (const p of data) {
      const consentTs = p?.attributes?.subscriptions?.sms?.marketing?.consent_timestamp;
      if (!consentTs) continue;

      const consentDate = new Date(consentTs);
      if (isNaN(consentDate.getTime())) continue;
      if (consentDate < startDate || consentDate >= endDate) continue;

      const day  = Utilities.formatDate(consentDate, tz_(), "yyyy-MM-dd");
      out[day] = (out[day] || 0) + 1;
    }

    url = res.json?.links?.next || "";
    Logger.log(`SMS list profiles page: ${data.length} records; next=${Boolean(res.json?.links?.next)}`);
  }

  return out;
}

// ===========================
// Profile count by day (Email)
// ===========================
function queryNewEmailProfilesCountByDay_(timeframe) {
  const out      = {};
  const startIso = timeframe.start;
  const endIso   = timeframe.end;

  let url = `${KLAVIYO_BASE}/profiles/?` +
    `sort=created&` +
    `filter=and(greater-than(created,${startIso}),less-than(created,${endIso}))&` +
    `page[size]=100`;

  while (url) {
    const res  = klaviyoFetch_(url, { method: "get" });
    const data = res.json?.data || [];

    for (const p of data) {
      const a       = p?.attributes || {};
      const created = a?.created;
      const email   = a?.email;

      if (!email || !created) continue;

      const day = Utilities.formatDate(new Date(created), tz_(), "yyyy-MM-dd");
      out[day] = (out[day] || 0) + 1;
    }

    url = res.json?.links?.next || "";
    Logger.log(`profiles page fetched: ${data.length} records; next=${Boolean(res.json?.links?.next)}`);
  }

  return out;
}

function listDays_(startIso, endIso) {
  const out   = [];
  const start = new Date(startIso);
  const end   = new Date(endIso);
  if (isNaN(start.getTime()) || isNaN(end.getTime())) return out;

  let cursor = new Date(Utilities.formatDate(start, tz_(), "yyyy-MM-dd") + "T00:00:00");
  const endYmd = Utilities.formatDate(end, tz_(), "yyyy-MM-dd");

  while (true) {
    const ymd = Utilities.formatDate(cursor, tz_(), "yyyy-MM-dd");
    if (ymd > endYmd) break;
    out.push(ymd);
    cursor.setDate(cursor.getDate() + 1);
    if (out.length > 2000) break;
  }
  return out;
}

// ===========================
// Metric Aggregates: counts per day
// ===========================
function queryMetricCountByDay_(metricId, timeframe, interval) {
  const startIso = timeframe?.start;
  const endIso   = timeframe?.end;

  const payload = {
    data: {
      type: "metric-aggregate",
      attributes: {
        metric_id: metricId,
        measurements: ["count"],
        filter: [
          `greater-or-equal(datetime,${startIso})`,
          `less-than(datetime,${endIso})`
        ],
        interval: interval,
        timezone: tz_()
      }
    }
  };

  const url = `${KLAVIYO_BASE}/metric-aggregates/`;
  const res = klaviyoFetch_(url, {
    method: "post",
    contentType: "application/json",
    payload: JSON.stringify(payload)
  });

  return normalizeMetricAggToDayMap_(res?.json || {});
}

function normalizeMetricAggToDayMap_(json) {
  const out = {};

  // Shape A: data.attributes.results[]
  const results = json?.data?.attributes?.results;
  if (Array.isArray(results)) {
    for (const r of results) {
      const dt = r?.date_time || r?.datetime || r?.date;
      const c  = r?.measurements?.count ?? r?.measurements?.[0] ?? r?.count ?? 0;
      if (dt) out[String(dt).slice(0, 10)] = Number(c) || 0;
    }
    return out;
  }

  // Shape B: data.attributes.data[]
  const data = json?.data?.attributes?.data;
  if (Array.isArray(data)) {
    for (const r of data) {
      const dt = r?.date_time || r?.datetime || r?.date;
      const c  = r?.measurements?.count ?? r?.measurements?.[0] ?? r?.count ?? 0;
      if (dt) out[String(dt).slice(0, 10)] = Number(c) || 0;
    }
    return out;
  }

  // Shape C: series arrays
  const dts  = json?.data?.attributes?.date_times;
  const vals = json?.data?.attributes?.values;
  if (Array.isArray(dts) && Array.isArray(vals) && dts.length === vals.length) {
    for (let i = 0; i < dts.length; i++) out[String(dts[i]).slice(0, 10)] = Number(vals[i]) || 0;
    return out;
  }

  return out;
}

// ===========================
// De-dupe maps
// ===========================
function buildExistingIdRowMap_(sh, idColIndex1, variantColIndex1) {
  const lastRow = sh.getLastRow();
  const map     = new Map();
  if (lastRow < 2) return map;

  if (variantColIndex1) {
    // Read enough columns to cover both id and variant
    const maxCol = Math.max(idColIndex1, variantColIndex1);
    const values = sh.getRange(2, 1, lastRow - 1, maxCol).getValues();
    for (let i = 0; i < values.length; i++) {
      const id = values[i][idColIndex1 - 1];
      if (!id) continue;
      const variant = String(values[i][variantColIndex1 - 1] || "").trim();
      const key     = variant ? `${String(id).trim()}|${variant}` : String(id).trim();
      map.set(key, i + 2);
    }
  } else {
    const values = sh.getRange(2, idColIndex1, lastRow - 1, 1).getValues();
    for (let i = 0; i < values.length; i++) {
      const id = values[i][0];
      if (!id) continue;
      map.set(String(id).trim(), i + 2);
    }
  }
  return map;
}

function buildExistingDayRowMap_(sh, dayCol1, cutoffYmd) {
  const lastRow = sh.getLastRow();
  const map     = new Map();
  if (lastRow < 2) return map;

  const lastCol = sh.getLastColumn();
  const values  = sh.getRange(2, 1, lastRow - 1, lastCol).getValues();

  for (let i = 0; i < values.length; i++) {
    const dayVal = values[i][dayCol1 - 1];
    if (!dayVal) continue;

    const day = (dayVal instanceof Date) ? ymd_(dayVal) : String(dayVal).slice(0, 10);
    if (day < cutoffYmd) continue;

    map.set(day, i + 2);
  }
  return map;
}

function buildExistingFlowKeyRowMap_(sh, dayCol1, idCol1, variantCol1, cutoffYmd) {
  const lastRow = sh.getLastRow();
  const map     = new Map();
  if (lastRow < 2) return map;

  const lastCol = sh.getLastColumn();
  const values  = sh.getRange(2, 1, lastRow - 1, lastCol).getValues();

  for (let i = 0; i < values.length; i++) {
    const row    = values[i];
    const dayVal = row[dayCol1 - 1];
    const idVal  = row[idCol1 - 1];
    if (!dayVal || !idVal) continue;

    const day = (dayVal instanceof Date) ? ymd_(dayVal) : String(dayVal).slice(0, 10);
    if (day < cutoffYmd) continue;

    const variant = variantCol1 ? String(row[variantCol1 - 1] || "").trim() : "";
    const key     = variant ? `${day}|${String(idVal).trim()}|${variant}` : `${day}|${String(idVal).trim()}`;
    map.set(key, i + 2);
  }
  return map;
}

// ===========================
// Metadata fetchers
// ===========================
function fetchCampaignMessageMetaIndexByCampaign_(campaignIds) {
  const out              = {};
  const allMsgIds        = new Set();
  const campaignToMsgIds = {};

  campaignIds.forEach(campaignId => {
    try {
      const url  = `${KLAVIYO_BASE}/campaigns/${campaignId}/campaign-messages/`;
      const res  = klaviyoFetch_(url, { method: "get" });
      const data = res.json?.data || [];
      const ids  = data.map(x => x.id).filter(Boolean);
      campaignToMsgIds[campaignId] = ids;
      ids.forEach(id => allMsgIds.add(id));
    } catch (e) {
      logErr_(`Campaign-messages list fetch failed campaignId=${campaignId}`, e, { quiet404: true });
      campaignToMsgIds[campaignId] = [];
    }
  });

  const msgMetaMap = fetchMetaMapGeneric_(Array.from(allMsgIds), "campaign-messages");

  Object.keys(campaignToMsgIds).forEach(campaignId => {
    out[campaignId] = (campaignToMsgIds[campaignId] || []).map(msgId => {
      const m = msgMetaMap[msgId] || {};
      return Object.assign({ id: msgId }, m);
    });
  });

  return out;
}

function resolveCampaignMessageMeta_(campaignMessageMetaIndex, campaignId, messageName, variationName, sendChannel) {
  const candidates = campaignMessageMetaIndex?.[campaignId] || [];
  if (!candidates.length) return {};

  const norm = v => String(v || "").trim().toLowerCase();

  const msgNameN = norm(messageName);
  const varN     = norm(variationName);
  const chanN    = norm(sendChannel);

  let hit = candidates.find(c =>
    norm(c.messageName) === msgNameN &&
    norm(c.variationName) === varN &&
    norm(c.sendChannel) === chanN
  );
  if (hit) return hit;

  hit = candidates.find(c =>
    norm(c.messageName) === msgNameN &&
    norm(c.sendChannel) === chanN
  );
  if (hit) return hit;

  hit = candidates.find(c => norm(c.messageName) === msgNameN);
  if (hit) return hit;

  return candidates[0] || {};
}

function fetchFlowMessageMetaMap_(ids) {
  return fetchMetaMapGeneric_(ids, "flow-messages");
}

function fetchMetaMapGeneric_(ids, resource) {
  const cache   = CacheService.getScriptCache();
  const out     = {};
  const toFetch = [];

  ids.forEach(id => {
    const key    = `meta:${resource}:${id}`;
    const cached = cache.get(key);
    if (cached) out[id] = JSON.parse(cached);
    else toFetch.push(id);
  });

  const batchSize = 20;

  for (let i = 0; i < toFetch.length; i += batchSize) {
    const batch = toFetch.slice(i, i + batchSize);

    batch.forEach(id => {
      const url = `${KLAVIYO_BASE}/${resource}/${id}`;

      let j = {};
      try {
        const res = klaviyoFetch_(url, { method: "get" });
        j = res.json || {};
      } catch (e) {
        logErr_(`Meta fetch failed for ${resource} id=${id}`, e, { quiet404: true });
        out[id] = {
          subject: "", previewText: "", sendTs: "", tags: "",
          status: "", abTestName: "", winningVariant: "",
          messageName: "", variationName: "", sendChannel: ""
        };
        return;
      }

      const a = j?.data?.attributes || {};

      const messageName    = a?.name || a?.message_name || a?.label || "";
      const variationName  = a?.variation_name || a?.variation?.name || a?.variation_label || "";
      const sendChannel    = a?.send_channel || a?.channel || "";

      const subject =
        a?.subject || a?.subject_line || a?.message_subject || a?.email_subject ||
        a?.content?.subject || a?.content?.email_subject || a?.content?.subject_line || "";

      const previewText =
        a?.preview_text || a?.previewText || a?.email_preview_text ||
        a?.content?.preview_text || a?.content?.preview ||
        a?.content?.email_preview_text || a?.content?.previewText || "";

      const sendTs =
        a?.send_time || a?.sent_at || a?.scheduled_at ||
        a?.created_at || a?.created || "";

      const tags           = a?.tags || a?.tag_names || "";
      const status         = a?.status || a?.message_status || a?.state || "";
      const abTestName     = a?.ab_test_name || a?.ab_test?.name || "";
      const winningVariant = a?.winning_variant || a?.ab_test?.winning_variant || "";

      const meta = { subject, previewText, sendTs, tags, status, abTestName, winningVariant, messageName, variationName, sendChannel };

      out[id] = meta;
      cache.put(`meta:${resource}:${id}`, JSON.stringify(meta), 21600);
      Utilities.sleep(150);
    });

    Utilities.sleep(600);
  }

  return out;
}

function fetchCampaignMetaMap_(campaignIds) {
  const cache   = CacheService.getScriptCache();
  const out     = {};
  const toFetch = [];

  campaignIds.forEach(id => {
    const key    = `meta:campaigns:${id}`;
    const cached = cache.get(key);
    if (cached) out[id] = JSON.parse(cached);
    else toFetch.push(id);
  });

  const campaignAudienceIds = {};
  const allAudienceIds      = new Set();

  const batchSize = 20;
  for (let i = 0; i < toFetch.length; i += batchSize) {
    const batch = toFetch.slice(i, i + batchSize);

    batch.forEach(campaignId => {
      const url = `${KLAVIYO_BASE}/campaigns/${campaignId}/`;
      try {
        const res = klaviyoFetch_(url, { method: "get" });
        const a   = res?.json?.data?.attributes || {};

        const audiences = a?.audiences || {};
        const included  = Array.isArray(audiences?.included) ? audiences.included : [];
        const excluded  = Array.isArray(audiences?.excluded) ? audiences.excluded : [];

        included.forEach(x => allAudienceIds.add(x));
        excluded.forEach(x => allAudienceIds.add(x));

        campaignAudienceIds[campaignId] = { included, excluded };

        out[campaignId] = {
          sendTs: a?.send_time || a?.scheduled_at || a?.updated_at || a?.created_at || "",
          tags: "", list: "", excludedList: ""
        };
      } catch (e) {
        logErr_(`Campaign meta fetch failed id=${campaignId}`, e, { quiet404: true });
        out[campaignId] = { sendTs: "", tags: "", list: "", excludedList: "" };
      }
      Utilities.sleep(150);
    });

    Utilities.sleep(600);
  }

  // Resolve audience names
  const audienceNameMap = fetchAudienceNameMap_(Array.from(allAudienceIds));

  Object.keys(campaignAudienceIds).forEach(campaignId => {
    const inc = campaignAudienceIds[campaignId]?.included || [];
    const exc = campaignAudienceIds[campaignId]?.excluded || [];
    out[campaignId].list         = inc.map(id => audienceNameMap[id] || id).filter(Boolean).join(", ");
    out[campaignId].excludedList = exc.map(id => audienceNameMap[id] || id).filter(Boolean).join(", ");
  });

  // Tags
  const tagsMap = fetchCampaignTagsMap_(Object.keys(campaignAudienceIds));
  Object.keys(tagsMap).forEach(campaignId => {
    out[campaignId].tags = tagsMap[campaignId] || "";
  });

  Object.keys(out).forEach(campaignId => {
    cache.put(`meta:campaigns:${campaignId}`, JSON.stringify(out[campaignId]), 21600);
  });

  return out;
}

function fetchAudienceNameMap_(audienceIds) {
  const cache   = CacheService.getScriptCache();
  const out     = {};
  const toFetch = [];

  (audienceIds || []).forEach(id => {
    const key    = `meta:audience:${id}`;
    const cached = cache.get(key);
    if (cached !== null && cached !== undefined) out[id] = cached;
    else toFetch.push(id);
  });

  const batchSize = 30;
  for (let i = 0; i < toFetch.length; i += batchSize) {
    const batch = toFetch.slice(i, i + batchSize);

    batch.forEach(audienceId => {
      let name = "";

      try {
        const segRes = klaviyoFetch_(`${KLAVIYO_BASE}/segments/${audienceId}/`, { method: "get" });
        name = segRes?.json?.data?.attributes?.name || "";
      } catch (e) {}

      if (!name) {
        try {
          const listRes = klaviyoFetch_(`${KLAVIYO_BASE}/lists/${audienceId}/`, { method: "get" });
          name = listRes?.json?.data?.attributes?.name || "";
        } catch (e) { name = ""; }
      }

      out[audienceId] = name;
      cache.put(`meta:audience:${audienceId}`, name, 21600);
      Utilities.sleep(100);
    });

    Utilities.sleep(400);
  }

  return out;
}

function fetchCampaignTagsMap_(campaignIds) {
  const cache   = CacheService.getScriptCache();
  const out     = {};
  const toFetch = [];

  campaignIds.forEach(id => {
    const key    = `meta:campaignTags:${id}`;
    const cached = cache.get(key);
    if (cached !== null && cached !== undefined) out[id] = cached;
    else toFetch.push(id);
  });

  const batchSize = 20;
  for (let i = 0; i < toFetch.length; i += batchSize) {
    const batch = toFetch.slice(i, i + batchSize);

    batch.forEach(campaignId => {
      const url = `${KLAVIYO_BASE}/campaigns/${campaignId}/tags/`;
      try {
        const res    = klaviyoFetch_(url, { method: "get" });
        const data   = res.json?.data || [];
        const names  = Array.isArray(data) ? data.map(t => t?.attributes?.name).filter(Boolean) : [];
        const joined = names.join(", ");
        out[campaignId] = joined;
        cache.put(`meta:campaignTags:${campaignId}`, joined, 21600);
      } catch (e) {
        logErr_(`Campaign tags fetch failed campaignId=${campaignId}`, e, { quiet404: true });
        out[campaignId] = "";
        cache.put(`meta:campaignTags:${campaignId}`, "", 21600);
      }
      Utilities.sleep(100);
    });

    Utilities.sleep(400);
  }

  return out;
}

// ===========================
// Small utils
// ===========================
function parseToDate_(ts) {
  if (!ts) return null;
  const d = new Date(ts);
  if (isNaN(d.getTime())) return null;
  return d;
}

// Pages through a Klaviyo collection endpoint (e.g. "metrics", "lists") and
// returns [{id, name}]. Used by klaviyoLogAccountInfo().
function fetchAllPages_(resource) {
  const out = [];
  let url = `${KLAVIYO_BASE}/${resource}/`;
  while (url) {
    const res = klaviyoFetch_(url, { method: "get" });
    (res.json?.data || []).forEach(x => out.push({ id: x.id, name: x?.attributes?.name || "" }));
    url = res.json?.links?.next || "";
  }
  return out;
}