Code.gs

/**
 * GitHub Repository Analytics Collector — v2
 * ---------------------------------------
 * Single-file Google Apps Script project. Collects metadata, languages,
 * contributors, traffic, referrers/paths, branches, and PR/issue activity
 * for every accessible GitHub repository, storing results in Google Sheets
 * for long-term trend analysis (GitHub's traffic API only exposes 14 days).
 *
 * WHAT CHANGED FROM v1 (read this if you're diffing):
 *
 *  PERFORMANCE (this is why v1 got slower every day):
 *   - v1 called upsertRows_()/appendRows_() once PER REPO PER SHEET. Each
 *     upsert first reads the ENTIRE existing sheet to build a key index
 *     (buildRowIndex_). So cost was O(repos × history-size), and history
 *     size grows every day forever. That's exactly the "gets slower and
 *     slower" pattern in the logs.
 *   - Fix: repo processing now only fetches data from GitHub and stages
 *     rows in memory (a "batch"). Sheet writes happen in one flush pass
 *     per sheet, so each sheet is read/indexed ONCE per run, not once per
 *     repo. Cost is now O(repos + history-size) instead of O(repos × history).
 *   - We still flush every BATCH_FLUSH_EVERY_N_REPOS repos (default 25),
 *     not only at the very end — so if Apps Script's hard 6-min execution
 *     ceiling kills the run mid-way, you don't lose an entire run's data,
 *     only the repos processed since the last flush.
 *   - getOrCreateSheet_() used to reformat the ENTIRE date column on every
 *     single call (there could be ~9 sheets × 11 repos = ~99 of these per
 *     run in v1). Now it only formats once, at sheet creation time.
 *
 *  DATA CORRECTNESS / CLARITY:
 *   - v1 wrote the same number into both "Watcher Count" and "Subscriber
 *     Count" (GraphQL's `watchers` connection actually corresponds to
 *     REST's true subscriber count, not a separate watcher metric). v2
 *     keeps a single, correctly-labeled "Subscriber Count (true watchers)"
 *     column instead of two identical, confusingly-named ones.
 *   - "Network Count" is only an approximation (≈ fork count; GraphQL has
 *     no true network-size field). The column header now says so directly
 *     instead of relying on a code comment nobody reading the sheet sees.
 *   - PopularReferrers/PopularPaths are GitHub's rolling 14-day snapshot,
 *     not a daily time series — collecting them every day mostly just
 *     duplicates the same 14-day window. Default frequency is now weekly
 *     (configurable), and old rows get pruned periodically.
 *   - Contributors/Branches collection frequency is also configurable
 *     (still daily by default, but you can slow it down as history grows).
 *
 *  CHARTS:
 *   - New "Public Charts" / "PublicChartData" tabs contain ONLY public
 *     repositories, so you can share/publish that tab without exposing
 *     anything about your private repos. "Charts"/"ChartData" continue
 *     to include everything.
 *   - New: org-wide totals chart (sum across all repos, not just per-repo
 *     lines), a language-mix snapshot chart, and a PR merge-rate snapshot
 *     chart, all previously-collected-but-never-charted data.
 *   - Per-repo breakdown charts now collapse to "Top N + Other" once you
 *     have more repos than fit legibly in one legend.
 *
 * Setup: see README.md. In short —
 *   1. Set Script Property GITHUB_PAT with your GitHub token.
 *   2. Run initializeSpreadsheet() once.
 *   3. Run main() once to do a first collection pass.
 *   4. Run setupTriggers() once to install the recurring schedule.
 *
 * Sections in this file (search for these headers):
 *   1. CONFIGURATION & CREDENTIALS
 *   2. GITHUB API CLIENT (REST + GraphQL, retry/backoff, rate limiting)
 *   3. SPREADSHEET HELPERS (sheet bootstrapping, batched upsert-by-key)
 *   4. LOGGING (Errors / ExecutionLog sheets)
 *   5. REPOSITORY DISCOVERY (GraphQL bulk query)
 *   6. ACTIVITY SUMMARY (per-repo GraphQL: PR/issue states, latest commit)
 *   7. TRAFFIC COLLECTOR (REST: views/clones/referrers/paths)
 *   8. CONTRIBUTORS COLLECTOR (REST)
 *   9. BRANCHES COLLECTOR (REST)
 *   10. STAGING / SHEET WRITERS (batch accumulation + flush)
 *   11. MAIN ORCHESTRATOR + TRIGGERS
 *   12. CHARTS / TREND VISUALIZATION (public + all-repo variants)
 *   13. RAW API DEBUG EXPORT (manual diagnostic — dumps untouched JSON per repo/endpoint)
 *   14. MAINTENANCE: DEDUPLICATE + REPAIR KEYED SHEETS (manual, on-demand)
 *   15. CUSTOM MENU (onOpen())
 */


// ============================================================
// 1. CONFIGURATION & CREDENTIALS  (FROM: Config.gs)
// ============================================================
/**
 * Handles credentials (Script Properties) and the "Configuration" sheet.
 * NOTHING in this file ever hardcodes a token.
 */

var PROP_KEY_PAT = 'GITHUB_PAT';

/**
 * One-time setup helper. We intentionally do NOT hardcode a real token
 * here — set it via Project Settings > Script Properties > GITHUB_PAT.
 */
function setGitHubToken() {
  throw new Error(
    'Do not hardcode your PAT in code. Instead go to ' +
    'Project Settings > Script Properties and add a property named ' +
    'GITHUB_PAT with your GitHub Personal Access Token as the value.'
  );
}

function getGitHubToken_() {
  var token = PropertiesService.getScriptProperties().getProperty(PROP_KEY_PAT);
  if (!token) {
    throw new Error(
      'GitHub PAT not found. Set a Script Property named "GITHUB_PAT" ' +
      '(Project Settings > Script Properties) before running this script.'
    );
  }
  return token;
}

/**
 * Default configuration. Overridden by rows in the "Configuration" sheet
 * if present (Key / Value columns).
 */
var DEFAULT_CONFIG = {
  RUN_FREQUENCY: 'daily',            // 'daily' | 'every6hours'
  INCLUDE_ARCHIVED: 'yes',
  INCLUDE_FORKS: 'yes',
  COLLECT_CONTRIBUTORS: 'yes',
  COLLECT_RELEASES: 'yes',
  COLLECT_BRANCHES: 'yes',
  COLLECT_TRAFFIC: 'yes',
  COLLECT_REFERRERS_PATHS: 'yes',
  MAX_RUNTIME_MS: '300000',          // stop gracefully before Apps Script's 6-min hard limit
  API_PAUSE_THRESHOLD: '50',         // pause/back off when remaining rate limit falls below this
  ACTIVITY_QUERY_PAUSE_MS: '150',    // small delay before each per-repo GraphQL call that includes
                                     // a `search(...)` field (draft PR count) — GitHub applies
                                     // extra abuse-detection scrutiny to search-heavy traffic, so a
                                     // small stagger is cheap insurance against secondary rate limits.
  CONTRIBUTORS_FREQUENCY_DAYS: '1',       // collect contributors every N days (1 = every run)
  BRANCHES_FREQUENCY_DAYS: '1',           // collect branches every N days (1 = every run)
  REFERRERS_PATHS_FREQUENCY_DAYS: '7',    // referrers/paths are a rolling 14-day snapshot, so
                                          // collecting daily mostly duplicates the same window —
                                          // default to weekly. Set to 1 to restore daily collection.
  PRUNE_ROLLING_SNAPSHOTS_DAYS: '60',     // delete PopularReferrers/PopularPaths rows older than
                                          // this many days (keeps that sheet from growing forever)
  BATCH_FLUSH_EVERY_N_REPOS: '25',        // flush staged rows to sheets after this many repos, in
                                          // addition to a final flush — bounds data loss if Apps
                                          // Script's hard execution ceiling kills the run mid-pass
  CHART_TOP_N_REPOS: '15'                 // per-repo breakdown charts collapse to "Top N + Other"
                                          // once there are more repos than this
};

/**
 * Reads the Configuration sheet (Key | Value | Description) and merges
 * it on top of DEFAULT_CONFIG. Creates the sheet with defaults if missing.
 */
function getConfig() {
  var ss = getSpreadsheet_();
  var sheet = ss.getSheetByName('Configuration');
  var config = {};
  for (var k in DEFAULT_CONFIG) config[k] = DEFAULT_CONFIG[k];

  if (!sheet) {
    sheet = ss.insertSheet('Configuration');
    sheet.appendRow(['Key', 'Value', 'Description']);
    sheet.getRange(1, 1, 1, 3).setFontWeight('bold');
    var descriptions = {
      RUN_FREQUENCY: 'daily or every6hours',
      INCLUDE_ARCHIVED: 'yes/no - include archived repositories',
      INCLUDE_FORKS: 'yes/no - include forked repositories',
      COLLECT_CONTRIBUTORS: 'yes/no',
      COLLECT_RELEASES: 'yes/no',
      COLLECT_BRANCHES: 'yes/no',
      COLLECT_TRAFFIC: 'yes/no',
      COLLECT_REFERRERS_PATHS: 'yes/no - popular referrers/paths (requires push access)',
      MAX_RUNTIME_MS: 'safety cutoff so the script exits before the 6-min Apps Script limit',
      API_PAUSE_THRESHOLD: 'remaining GitHub API calls at which the script starts throttling',
      ACTIVITY_QUERY_PAUSE_MS: 'ms to pause before each per-repo GraphQL search-containing call',
      CONTRIBUTORS_FREQUENCY_DAYS: 'collect contributors every N days (1 = every run)',
      BRANCHES_FREQUENCY_DAYS: 'collect branches every N days (1 = every run)',
      REFERRERS_PATHS_FREQUENCY_DAYS: 'collect referrers/paths every N days (data is a rolling 14-day snapshot, so daily is mostly redundant)',
      PRUNE_ROLLING_SNAPSHOTS_DAYS: 'delete PopularReferrers/PopularPaths rows older than N days',
      BATCH_FLUSH_EVERY_N_REPOS: 'write staged data to sheets after every N repos processed',
      CHART_TOP_N_REPOS: 'per-repo charts collapse to Top N + "Other" beyond this many repos'
    };
    var rows = [];
    for (var key in DEFAULT_CONFIG) {
      rows.push([key, DEFAULT_CONFIG[key], descriptions[key] || '']);
    }
    sheet.getRange(2, 1, rows.length, 3).setValues(rows);
    sheet.autoResizeColumns(1, 3);
    trimUnusedColumns_(sheet, 3);
    return config;
  }

  var data = sheet.getDataRange().getValues();
  for (var i = 1; i < data.length; i++) {
    var key = String(data[i][0] || '').trim();
    var value = data[i][1];
    if (key) config[key] = String(value).trim();
  }
  trimUnusedColumns_(sheet, 3);
  return config;
}

function configBool_(config, key) {
  return String(config[key]).toLowerCase() === 'yes' || String(config[key]).toLowerCase() === 'true';
}

/**
 * Deterministic "should this run today" check for frequency-gated
 * collectors (contributors/branches/referrers-paths). Using day-of-year
 * mod N means all repos in a run agree on the same decision without
 * needing to persist any extra state.
 */
function shouldRunOnFrequency_(config, key, defaultDays) {
  var days = Math.max(1, Number(config[key] || defaultDays));
  if (days <= 1) return true;
  var now = new Date();
  var startOfYear = new Date(now.getFullYear(), 0, 0);
  var dayOfYear = Math.floor((now.getTime() - startOfYear.getTime()) / 86400000);
  return (dayOfYear % days) === 0;
}


// ============================================================
// 2. GITHUB API CLIENT  (FROM: GitHubClient.gs)
// ============================================================
/**
 * Thin wrapper around UrlFetchApp for talking to the GitHub REST and
 * GraphQL APIs. Centralizes: auth headers, retry/backoff, rate-limit
 * awareness, pagination (Link header), and error logging.
 */

var GITHUB_API_ROOT = 'https://api.github.com';
var GITHUB_GRAPHQL_URL = 'https://api.github.com/graphql';
var MAX_RETRIES = 4;

/** Tracks the last known rate limit state for this execution. */
var _rateLimitState = { remaining: null, resetEpochSeconds: null };

function ghHeaders_() {
  return {
    'Authorization': 'Bearer ' + getGitHubToken_(),
    'Accept': 'application/vnd.github+json',
    'X-GitHub-Api-Version': '2022-11-28'
  };
}

/**
 * Low level GET/POST with retry/backoff and rate-limit handling.
 * Returns { status, headers, body (parsed JSON or null), rawText }.
 */
function ghFetch_(url, options) {
  options = options || {};
  var config = options.config || getConfig();
  var pauseThreshold = Number(config.API_PAUSE_THRESHOLD || 50);

  var attempt = 0;
  while (true) {
    attempt++;
    maybeThrottleForRateLimit_(pauseThreshold);

    var fetchOptions = {
      method: options.method || 'get',
      headers: ghHeaders_(),
      muteHttpExceptions: true,
      followRedirects: true
    };
    if (options.payload) {
      fetchOptions.contentType = 'application/json';
      fetchOptions.payload = JSON.stringify(options.payload);
    }

    var response;
    try {
      response = UrlFetchApp.fetch(url, fetchOptions);
    } catch (e) {
      logError_(options.repoFullName || '', url, 0, 'Network error: ' + e.message, attempt < MAX_RETRIES);
      if (attempt >= MAX_RETRIES) throw e;
      Utilities.sleep(backoffMs_(attempt));
      continue;
    }

    var status = response.getResponseCode();
    var headers = response.getAllHeaders();
    updateRateLimitState_(headers);

    if (status >= 200 && status < 300) {
      var rawText = response.getContentText();
      var parsed = null;
      try { parsed = rawText ? JSON.parse(rawText) : null; } catch (e) { parsed = null; }
      return { status: status, headers: headers, body: parsed, rawText: rawText };
    }

    if (status === 403 && (headers['retry-after'] || headers['Retry-After'])) {
      var retryAfterSec = Number(headers['retry-after'] || headers['Retry-After']) || 5;
      logError_(options.repoFullName || '', url, status, 'Secondary rate limit hit, retry-after=' + retryAfterSec + 's', true);
      Utilities.sleep(retryAfterSec * 1000);
      continue;
    }

    if (status === 403 && headers['x-ratelimit-remaining'] === '0') {
      var resetHeader = Number(headers['x-ratelimit-reset']);
      var waitMs = Math.max(0, resetHeader * 1000 - Date.now()) + 1000;
      logError_(options.repoFullName || '', url, status, 'Rate limit exhausted, waiting ' + Math.round(waitMs / 1000) + 's', true);
      Utilities.sleep(Math.min(waitMs, 5 * 60 * 1000));
      continue;
    }

    // Traffic endpoints / permissions issue: not retryable, caller should
    // treat this as "unavailable" rather than a hard failure. We still log
    // it (as non-fatal / no-retry) so it's visible in the Errors sheet —
    // this is the one case that used to be completely silent: a repo could
    // be missing traffic/contributors/branches data indefinitely with
    // nothing in Errors to explain why (insufficient PAT scope, missing
    // Administration:Read on a fine-grained PAT, org SSO not authorized,
    // or genuinely read-only access to that repo).
    if (status === 403) {
      logError_(options.repoFullName || '', url, status,
        'Request forbidden (403) - likely insufficient PAT permissions for this endpoint (e.g. missing push access / Administration:Read, or org SSO not authorized for this token). Treated as "unavailable", not retried.',
        false);
      return { status: status, headers: headers, body: safeParse_(response.getContentText()), rawText: response.getContentText() };
    }

    if (status === 404) {
      return { status: status, headers: headers, body: null, rawText: response.getContentText() };
    }

    if (status >= 500 || status === 502 || status === 503) {
      logError_(options.repoFullName || '', url, status, 'Transient server error', attempt < MAX_RETRIES);
      if (attempt >= MAX_RETRIES) {
        return { status: status, headers: headers, body: null, rawText: response.getContentText() };
      }
      Utilities.sleep(backoffMs_(attempt));
      continue;
    }

    logError_(options.repoFullName || '', url, status, safeErrorMessage_(response.getContentText()), false);
    return { status: status, headers: headers, body: safeParse_(response.getContentText()), rawText: response.getContentText() };
  }
}

function backoffMs_(attempt) {
  var base = Math.pow(2, attempt - 1) * 1000;
  return base + Math.floor(Math.random() * 250);
}

function safeParse_(text) {
  try { return JSON.parse(text); } catch (e) { return null; }
}

function safeErrorMessage_(text) {
  var parsed = safeParse_(text);
  if (parsed && parsed.message) return parsed.message;
  return (text || '').substring(0, 300);
}

function updateRateLimitState_(headers) {
  var remaining = headers['x-ratelimit-remaining'];
  var reset = headers['x-ratelimit-reset'];
  if (remaining !== undefined) _rateLimitState.remaining = Number(remaining);
  if (reset !== undefined) _rateLimitState.resetEpochSeconds = Number(reset);
}

function getRateLimitState() {
  return _rateLimitState;
}

function maybeThrottleForRateLimit_(pauseThreshold) {
  if (_rateLimitState.remaining !== null && _rateLimitState.remaining <= pauseThreshold) {
    var waitMs = Math.max(0, (_rateLimitState.resetEpochSeconds || 0) * 1000 - Date.now()) + 1000;
    if (waitMs > 0) {
      Utilities.sleep(Math.min(waitMs, 5 * 60 * 1000));
    }
  }
}

/**
 * GET a REST endpoint, following pagination via the Link header until
 * exhausted or `maxPages` reached. Returns an array of all `body` items
 * (assumes each page's body is a JSON array).
 */
function ghGetPaginated_(path, params, options) {
  options = options || {};
  var maxPages = options.maxPages || 50;
  var perPage = (params && params.per_page) || 100;
  var url = GITHUB_API_ROOT + path + '?' + buildQuery_(Object.assign({ per_page: perPage }, params || {}));

  var results = [];
  var page = 0;
  while (url && page < maxPages) {
    page++;
    var res = ghFetch_(url, options);
    if (res.status >= 200 && res.status < 300 && Array.isArray(res.body)) {
      results = results.concat(res.body);
    } else if (res.status !== 200) {
      break;
    }
    url = extractNextLink_(res.headers['link'] || res.headers['Link']);
  }
  return results;
}

function extractNextLink_(linkHeader) {
  if (!linkHeader) return null;
  var parts = linkHeader.split(',');
  for (var i = 0; i < parts.length; i++) {
    var m = parts[i].match(/<([^>]+)>;\s*rel="next"/);
    if (m) return m[1];
  }
  return null;
}

function buildQuery_(params) {
  return Object.keys(params)
    .filter(function (k) { return params[k] !== undefined && params[k] !== null; })
    .map(function (k) { return encodeURIComponent(k) + '=' + encodeURIComponent(params[k]); })
    .join('&');
}

/** GraphQL POST helper. */
function ghGraphQL_(query, variables, options) {
  options = options || {};
  var res = ghFetch_(GITHUB_GRAPHQL_URL, Object.assign({
    method: 'post',
    payload: { query: query, variables: variables || {} }
  }, options));

  if (res.body && res.body.errors && res.body.errors.length) {
    logError_(options.repoFullName || '', 'graphql', res.status, 'GraphQL errors: ' + JSON.stringify(res.body.errors).substring(0, 500), false);
  }
  return res.body;
}


// ============================================================
// 3. SPREADSHEET HELPERS  (FROM: SheetService.gs)
// ============================================================
/**
 * Helpers for opening the workbook, creating sheets with headers, and
 * doing batched "upsert" writes (update existing row if a key matches,
 * otherwise append). Callers should batch ALL rows for a sheet across
 * every repo into one call — see section 10 for the staging pattern that
 * makes this possible. That's the fix for the "gets slower every day" bug:
 * each of these functions reads the target sheet's existing data ONCE,
 * not once per repo.
 */

var WORKBOOK_NAME = 'GitHub Analytics';

function getSpreadsheet_() {
  var active = SpreadsheetApp.getActiveSpreadsheet ? SpreadsheetApp.getActiveSpreadsheet() : null;
  if (active) return active;

  var props = PropertiesService.getScriptProperties();
  var id = props.getProperty('WORKBOOK_ID');
  if (id) {
    try {
      return SpreadsheetApp.openById(id);
    } catch (e) {
      // fall through and recreate if it was deleted
    }
  }
  var ss = SpreadsheetApp.create(WORKBOOK_NAME);
  props.setProperty('WORKBOOK_ID', ss.getId());
  return ss;
}

/** Parses an ISO 'yyyy-MM-dd' string into a real Date object (midnight UTC). */
function toIsoDate_(isoDateStr) {
  return Utilities.parseDate(isoDateStr, 'Etc/UTC', 'yyyy-MM-dd');
}

/**
 * Normalizes a cell/row value for use as part of an upsert key, so both a
 * real Date object and an equivalent string produce the same key.
 *
 * This handles more than the obvious "Date object" case: manually pasted
 * data (e.g. a spreadsheet migration) can leave a date-shaped cell as
 * plain TEXT rather than a real Date — which looks identical on screen
 * (both display "2026-07-03") but compares unequal unless we specifically
 * detect and re-normalize it. We trim whitespace and, if the string looks
 * date-like, parse and reformat it the same way a real Date object would
 * be — so a text-typed "2026-07-03" (or one with a stray time component)
 * matches a properly-typed Date cell for the same calendar day.
 */
function normalizeKeyValue_(v) {
  if (v instanceof Date) {
    return Utilities.formatDate(v, 'Etc/UTC', 'yyyy-MM-dd');
  }
  var s = String(v).trim();
  if (/^\d{4}-\d{1,2}-\d{1,2}([ T].*)?$/.test(s)) {
    try {
      var parsed = new Date(s.length <= 10 ? s + 'T00:00:00Z' : s.replace(' ', 'T') + (s.indexOf('Z') === -1 && s.indexOf('+') === -1 ? 'Z' : ''));
      if (!isNaN(parsed.getTime())) {
        return Utilities.formatDate(parsed, 'Etc/UTC', 'yyyy-MM-dd');
      }
    } catch (e) {
      // fall through to raw trimmed string below
    }
  }
  return s;
}

/** Deletes all unused columns to the right of the header row. Cheap no-op once trimmed. */
function trimUnusedColumns_(sheet, usedColumnCount) {
  var maxCols = sheet.getMaxColumns();
  if (maxCols > usedColumnCount) {
    sheet.deleteColumns(usedColumnCount + 1, maxCols - usedColumnCount);
  }
}

/**
 * Applies a yyyy-mm-dd number format to any header column whose name ends
 * in "Date". IMPORTANT: unlike v1, this is now only called once at sheet
 * creation time (from getOrCreateSheet_), not on every write — reformatting
 * the whole column on every single call was a meaningful chunk of the
 * per-run slowdown as sheets grew.
 */
function formatDateColumns_(sheet, headers) {
  headers.forEach(function (h, i) {
    if (/Date$/.test(h)) {
      sheet.getRange(2, i + 1, Math.max(sheet.getMaxRows() - 1, 1), 1).setNumberFormat('yyyy-mm-dd');
    }
  });
}

/**
 * Gets a sheet by name, creating it (with header row + date formatting)
 * if it doesn't exist. Trims unused columns every call (cheap), but only
 * formats date columns at creation time.
 */
function getOrCreateSheet_(name, headers) {
  var ss = getSpreadsheet_();
  var sheet = ss.getSheetByName(name);
  var isNew = false;
  if (!sheet) {
    sheet = ss.insertSheet(name);
    isNew = true;
  } else if (sheet.getLastRow() === 0) {
    isNew = true;
  }
  if (isNew) {
    sheet.appendRow(headers);
    sheet.getRange(1, 1, 1, headers.length).setFontWeight('bold');
    sheet.setFrozenRows(1);
    formatDateColumns_(sheet, headers);
  }
  trimUnusedColumns_(sheet, headers.length);
  return sheet;
}

/**
 * Builds a map of "key -> row number" for a sheet, based on one or more
 * key columns (1-indexed). This is the expensive full-sheet read — call
 * it ONCE per sheet per run (via upsertRows_/flush, never per repo).
 */
function buildRowIndex_(sheet, keyColumns) {
  var index = {};
  var lastRow = sheet.getLastRow();
  if (lastRow < 2) return index;
  var lastCol = Math.max.apply(null, keyColumns);
  var values = sheet.getRange(2, 1, lastRow - 1, lastCol).getValues();
  for (var i = 0; i < values.length; i++) {
    var keyParts = keyColumns.map(function (c) { return normalizeKeyValue_(values[i][c - 1]); });
    index[keyParts.join('||')] = i + 2;
  }
  return index;
}

/**
 * Upserts many rows at once against a sheet, keyed by keyColumns
 * (1-indexed column numbers whose values form the unique key).
 * Pass ALL rows for ALL repos in one call — see section 10.
 */
function upsertRows_(sheet, rows, keyColumns) {
  if (!rows.length) return;
  var index = buildRowIndex_(sheet, keyColumns);
  var toAppend = [];

  rows.forEach(function (row) {
    var keyParts = keyColumns.map(function (c) { return normalizeKeyValue_(row[c - 1]); });
    var key = keyParts.join('||');
    if (index[key]) {
      sheet.getRange(index[key], 1, 1, row.length).setValues([row]);
    } else {
      toAppend.push(row);
      index[key] = -1; // guard against duplicate keys within the same batch
    }
  });

  if (toAppend.length) {
    sheet.getRange(sheet.getLastRow() + 1, 1, toAppend.length, toAppend[0].length).setValues(toAppend);
  }
}

/** Pure append (used for tables where we intentionally keep full history). */
function appendRows_(sheet, rows) {
  if (!rows.length) return;
  sheet.getRange(sheet.getLastRow() + 1, 1, rows.length, rows[0].length).setValues(rows);
}


// ============================================================
// 4. LOGGING  (FROM: Logging.gs)
// ============================================================
/**
 * Writes to the "Errors" and "ExecutionLog" sheets. Errors are buffered
 * in memory and flushed in one batch write.
 */

var _errorBuffer = [];

function logError_(repoFullName, endpoint, httpStatus, message, retryAttempted) {
  _errorBuffer.push([
    new Date(),
    repoFullName || '',
    endpoint || '',
    httpStatus || '',
    message || '',
    retryAttempted ? 'yes' : 'no'
  ]);
  Logger.log('[ERROR] repo=%s endpoint=%s status=%s msg=%s', repoFullName, endpoint, httpStatus, message);
}

function flushErrorLog_() {
  if (!_errorBuffer.length) return;
  var sheet = getOrCreateSheet_('Errors', [
    'Timestamp', 'Repository', 'Endpoint', 'HTTP Status', 'Message', 'Retry Attempted'
  ]);
  sheet.getRange(sheet.getLastRow() + 1, 1, _errorBuffer.length, 6).setValues(_errorBuffer);
  _errorBuffer = [];
}

function logExecution_(summary) {
  var sheet = getOrCreateSheet_('ExecutionLog', [
    'Start Time', 'End Time', 'Duration (s)', 'Repositories Processed',
    'Repositories Failed', 'API Calls Made', 'Rate Limit Remaining', 'Notes'
  ]);
  sheet.appendRow([
    summary.startTime,
    summary.endTime,
    Math.round((summary.endTime - summary.startTime) / 1000),
    summary.repositoriesProcessed,
    summary.repositoriesFailed,
    summary.apiCallsMade,
    summary.rateLimitRemaining,
    summary.notes || ''
  ]);
}


// ============================================================
// 5. REPOSITORY DISCOVERY  (FROM: RepoDiscovery.gs)
// ============================================================
/**
 * Uses GraphQL to discover every repository accessible to the
 * authenticated user and pull metadata + language breakdown in as few
 * API calls as possible. REST is reserved for traffic, contributors,
 * referrers/paths, releases, and branches — endpoints GraphQL doesn't expose.
 */

var REPO_DISCOVERY_QUERY =
  'query($cursor: String) {\n' +
  '  viewer {\n' +
  '    repositories(first: 50, after: $cursor, affiliations: [OWNER, ORGANIZATION_MEMBER, COLLABORATOR], ownerAffiliations: [OWNER, ORGANIZATION_MEMBER, COLLABORATOR], orderBy: {field: UPDATED_AT, direction: DESC}) {\n' +
  '      pageInfo { hasNextPage endCursor }\n' +
  '      nodes {\n' +
  '        databaseId\n' +
  '        name\n' +
  '        nameWithOwner\n' +
  '        owner { login }\n' +
  '        isPrivate\n' +
  '        isArchived\n' +
  '        isDisabled\n' +
  '        isFork\n' +
  '        parent { nameWithOwner }\n' +
  '        defaultBranchRef { name }\n' +
  '        createdAt\n' +
  '        updatedAt\n' +
  '        pushedAt\n' +
  '        diskUsage\n' +
  '        primaryLanguage { name }\n' +
  '        description\n' +
  '        homepageUrl\n' +
  '        licenseInfo { spdxId }\n' +
  '        repositoryTopics(first: 20) { nodes { topic { name } } }\n' +
  '        issues(states: OPEN) { totalCount }\n' +
  '        pullRequests(states: OPEN) { totalCount }\n' +
  '        forkCount\n' +
  '        stargazerCount\n' +
  '        watchers { totalCount }\n' +
  '        languages(first: 20, orderBy: {field: SIZE, direction: DESC}) {\n' +
  '          edges { size node { name } }\n' +
  '        }\n' +
  '        latestRelease { tagName publishedAt }\n' +
  '        releases { totalCount }\n' +
  '      }\n' +
  '    }\n' +
  '  }\n' +
  '  rateLimit { remaining resetAt }\n' +
  '}';

function discoverRepositories(config) {
  var all = [];
  var cursor = null;
  var hasNextPage = true;
  var guard = 0;

  while (hasNextPage && guard < 40) { // 40*50 = 2000 repos ceiling per run, safety guard
    guard++;
    var data = ghGraphQL_(REPO_DISCOVERY_QUERY, { cursor: cursor });
    if (!data || !data.data || !data.data.viewer) {
      logError_('', 'graphql:repositories', 0, 'No data returned from repository discovery query', false);
      break;
    }
    var conn = data.data.viewer.repositories;
    conn.nodes.forEach(function (n) { all.push(normalizeRepoNode_(n)); });
    hasNextPage = conn.pageInfo.hasNextPage;
    cursor = conn.pageInfo.endCursor;

    // GraphQL's own rateLimit block is available here (data.data.rateLimit)
    // but we rely on HTTP response headers (updateRateLimitState_) for
    // throttling decisions instead, since that's populated for every call
    // including REST ones — this field is fetched mainly for visibility
    // if you want to inspect it in Stackdriver logs.
    if (data.data.rateLimit) {
      Logger.log('GraphQL rateLimit: remaining=%s resetAt=%s', data.data.rateLimit.remaining, data.data.rateLimit.resetAt);
    }
  }

  var filtered = all.filter(function (r) {
    if (!configBool_(config, 'INCLUDE_ARCHIVED') && r.archived) return false;
    if (!configBool_(config, 'INCLUDE_FORKS') && r.fork) return false;
    return true;
  });

  return filtered;
}

function normalizeRepoNode_(n) {
  var topics = (n.repositoryTopics && n.repositoryTopics.nodes || [])
    .map(function (t) { return t.topic.name; }).join(', ');
  var languages = (n.languages && n.languages.edges || []).map(function (e) {
    return { name: e.node.name, bytes: e.size };
  });

  return {
    repoId: n.databaseId,
    owner: n.owner.login,
    name: n.name,
    fullName: n.nameWithOwner,
    visibility: n.isPrivate ? 'private' : 'public',
    isPrivate: n.isPrivate,
    defaultBranch: n.defaultBranchRef ? n.defaultBranchRef.name : '',
    archived: n.isArchived,
    disabled: n.isDisabled,
    fork: n.isFork,
    parentFullName: n.parent ? n.parent.nameWithOwner : '',
    createdAt: n.createdAt,
    updatedAt: n.updatedAt,
    pushedAt: n.pushedAt,
    sizeKb: n.diskUsage,
    primaryLanguage: n.primaryLanguage ? n.primaryLanguage.name : '',
    topics: topics,
    license: n.licenseInfo ? n.licenseInfo.spdxId : '',
    homepage: n.homepageUrl || '',
    description: n.description || '',
    openIssues: n.issues ? n.issues.totalCount : 0,
    openPullRequests: n.pullRequests ? n.pullRequests.totalCount : 0,
    forkCount: n.forkCount,
    starCount: n.stargazerCount,
    // GraphQL's `watchers` connection = REST's true subscriber count (people
    // who clicked "Watch"), NOT the legacy REST `watchers_count` field
    // (which is actually a star-count alias, and NOT the same thing as
    // GitHub's "network" of forks-of-forks). We only keep ONE clearly
    // labeled field for this to avoid the v1 bug of writing the same
    // number into two differently-named, confusing columns.
    subscriberCount: n.watchers ? n.watchers.totalCount : 0,
    // GraphQL has no true "network size" field (forks-of-forks etc.) — this
    // is an approximation using direct fork count only. Treat as indicative,
    // not exact; the sheet column header says so explicitly.
    networkCountApprox: n.forkCount,
    languages: languages,
    latestReleaseTag: n.latestRelease ? n.latestRelease.tagName : '',
    latestReleaseDate: n.latestRelease ? n.latestRelease.publishedAt : '',
    releaseCount: n.releases ? n.releases.totalCount : 0
  };
}


// ============================================================
// 6. ACTIVITY SUMMARY  (FROM: ActivitySummary.gs)
// ============================================================
/**
 * Per-repository GraphQL query for closed/merged PR counts, draft PR
 * count, closed issue count, and the latest commit timestamp.
 */

var ACTIVITY_QUERY =
  'query($owner: String!, $name: String!, $draftQuery: String!) {\n' +
  '  repository(owner: $owner, name: $name) {\n' +
  '    openIssues: issues(states: OPEN) { totalCount }\n' +
  '    closedIssues: issues(states: CLOSED) { totalCount }\n' +
  '    openPRs: pullRequests(states: OPEN) { totalCount }\n' +
  '    closedPRs: pullRequests(states: CLOSED) { totalCount }\n' +
  '    mergedPRs: pullRequests(states: MERGED) { totalCount }\n' +
  '    defaultBranchRef {\n' +
  '      target {\n' +
  '        ... on Commit { committedDate }\n' +
  '      }\n' +
  '    }\n' +
  '  }\n' +
  '  draftSearch: search(query: $draftQuery, type: ISSUE) { issueCount }\n' +
  '}';

function buildActivityVariables_(repo) {
  return {
    owner: repo.owner,
    name: repo.name,
    draftQuery: 'repo:' + repo.fullName + ' is:pr is:draft'
  };
}

function collectActivitySummary(repo, config) {
  var pauseMs = Number(config.ACTIVITY_QUERY_PAUSE_MS || 150);
  if (pauseMs > 0) Utilities.sleep(pauseMs);

  var data = ghGraphQL_(ACTIVITY_QUERY, buildActivityVariables_(repo), { repoFullName: repo.fullName, config: config });
  if (!data || !data.data || !data.data.repository) {
    return {
      openIssues: repo.openIssues || 0, closedIssues: 0,
      openPRs: repo.openPullRequests || 0, closedPRs: 0, mergedPRs: 0, draftPRs: 0,
      latestCommitDate: ''
    };
  }
  var r = data.data.repository;
  return {
    openIssues: r.openIssues.totalCount,
    closedIssues: r.closedIssues.totalCount,
    openPRs: r.openPRs.totalCount,
    closedPRs: r.closedPRs.totalCount,
    mergedPRs: r.mergedPRs.totalCount,
    draftPRs: data.data.draftSearch ? data.data.draftSearch.issueCount : 0,
    latestCommitDate: r.defaultBranchRef && r.defaultBranchRef.target ? r.defaultBranchRef.target.committedDate : ''
  };
}


// ============================================================
// 7. TRAFFIC COLLECTOR  (FROM: TrafficCollector.gs)
// ============================================================
/**
 * REST-only endpoints: traffic views, clones, popular referrers/paths.
 * GitHub only returns the last 14 days for all of these, so views/clones
 * are UPSERTed by (repo, date). Referrers/paths are GitHub's own rolling
 * top-10-of-last-14-days snapshot (not a daily-delta time series) — see
 * REFERRERS_PATHS_FREQUENCY_DAYS in Config for how often we bother
 * re-collecting that, and pruneOldRollingSnapshots_ (section 10) for how
 * old rows get cleared out so that sheet doesn't grow forever.
 */

function collectTraffic(repo, config) {
  var result = { views: [], clones: [], referrers: [], paths: [], unavailable: false };
  var owner = repo.owner, name = repo.name;

  var viewsRes = ghFetch_(GITHUB_API_ROOT + '/repos/' + owner + '/' + name + '/traffic/views', { repoFullName: repo.fullName, config: config });
  if (viewsRes.status === 403) {
    result.unavailable = true;
    return result;
  }
  if (viewsRes.status === 200 && viewsRes.body) {
    (viewsRes.body.views || []).forEach(function (v) {
      result.views.push({ date: v.timestamp.substring(0, 10), count: v.count, uniques: v.uniques });
    });
  }

  var clonesRes = ghFetch_(GITHUB_API_ROOT + '/repos/' + owner + '/' + name + '/traffic/clones', { repoFullName: repo.fullName, config: config });
  if (clonesRes.status === 200 && clonesRes.body) {
    (clonesRes.body.clones || []).forEach(function (c) {
      result.clones.push({ date: c.timestamp.substring(0, 10), count: c.count, uniques: c.uniques });
    });
  }

  var collectReferrersPaths = configBool_(config, 'COLLECT_REFERRERS_PATHS') &&
    shouldRunOnFrequency_(config, 'REFERRERS_PATHS_FREQUENCY_DAYS', 7);

  if (collectReferrersPaths) {
    var refRes = ghFetch_(GITHUB_API_ROOT + '/repos/' + owner + '/' + name + '/traffic/popular/referrers', { repoFullName: repo.fullName, config: config });
    if (refRes.status === 200 && Array.isArray(refRes.body)) {
      refRes.body.forEach(function (r) {
        result.referrers.push({ referrer: r.referrer, uniques: r.uniques, count: r.count });
      });
    }

    var pathsRes = ghFetch_(GITHUB_API_ROOT + '/repos/' + owner + '/' + name + '/traffic/popular/paths', { repoFullName: repo.fullName, config: config });
    if (pathsRes.status === 200 && Array.isArray(pathsRes.body)) {
      pathsRes.body.forEach(function (p) {
        result.paths.push({ path: p.path, title: p.title, count: p.count, uniques: p.uniques });
      });
    }
  }

  return result;
}


// ============================================================
// 8. CONTRIBUTORS COLLECTOR  (FROM: ContributorsCollector.gs)
// ============================================================
/**
 * GET /repos/{owner}/{repo}/contributors — paginated REST endpoint.
 * NOTE: GitHub caches this and it can lag real activity. It's also
 * sorted by commits on the default branch only, and caps around 500
 * contributors — contributors who only appear via squash-merged PRs
 * under a different default-branch author, or on non-default branches,
 * may not show up here. Treat as indicative, not exhaustive.
 */

function collectContributors(repo, config) {
  if (!configBool_(config, 'COLLECT_CONTRIBUTORS')) return [];
  if (!shouldRunOnFrequency_(config, 'CONTRIBUTORS_FREQUENCY_DAYS', 1)) return [];

  var owner = repo.owner, name = repo.name;
  var items = ghGetPaginated_('/repos/' + owner + '/' + name + '/contributors', { anon: 0 }, { repoFullName: repo.fullName, config: config, maxPages: 10 });

  return items.map(function (c) {
    return { login: c.login || '(deleted user)', contributions: c.contributions };
  });
}


// ============================================================
// 9. BRANCHES COLLECTOR  (FROM: BranchesAndReleases.gs)
// ============================================================
/**
 * Branches: GET /repos/{owner}/{repo}/branches (paginated), including
 * protection status (REST v2022-11-28 list endpoint includes `protected`
 * directly, no extra per-branch call needed).
 *
 * Releases: already captured cheaply via GraphQL in RepoDiscovery — no
 * extra API calls needed here.
 */

function collectBranches(repo, config) {
  if (!configBool_(config, 'COLLECT_BRANCHES')) return [];
  if (!shouldRunOnFrequency_(config, 'BRANCHES_FREQUENCY_DAYS', 1)) return [];

  var owner = repo.owner, name = repo.name;
  var items = ghGetPaginated_('/repos/' + owner + '/' + name + '/branches', {}, { repoFullName: repo.fullName, config: config, maxPages: 10 });
  return items.map(function (b) {
    return {
      name: b.name,
      protected: !!b.protected,
      lastCommitSha: b.commit ? b.commit.sha : ''
    };
  });
}


// ============================================================
// 10. STAGING / SHEET WRITERS  (batch accumulation + flush)
// ============================================================
/**
 * This is the core of the performance fix. In v1, every write*ToSheet_()
 * function ran an upsert immediately, per repo — which meant re-reading
 * the entire, ever-growing sheet history once per repo per run. Here,
 * stage*_() functions just push plain row-arrays into an in-memory batch.
 * flushBatch_() then does exactly ONE upsert/append pass per sheet,
 * covering every repo processed so far.
 *
 * main() calls flushBatch_() periodically (every BATCH_FLUSH_EVERY_N_REPOS
 * repos) as well as at the end, so a mid-run script kill doesn't lose
 * everything staged since the run started — only what's staged since the
 * last periodic flush.
 */

var REPOSITORIES_HEADERS = [
  'Repository ID', 'Owner', 'Repository Name', 'Full Name', 'Visibility',
  'Default Branch', 'Archived', 'Disabled', 'Fork', 'Parent Repository',
  'Created Date', 'Updated Date', 'Last Push Date', 'Size (KB)', 'Primary Language',
  'Topics', 'License', 'Homepage', 'Description',
  'Open Issues', 'Closed Issues', 'Open Pull Requests', 'Closed Pull Requests',
  'Merged Pull Requests', 'Draft Pull Requests',
  'Fork Count', 'Star Count', 'Subscriber Count (true watchers)', 'Network Count (approx., = fork count)',
  'Latest Release Tag', 'Latest Release Date', 'Release Count',
  'Latest Commit Date', 'Last Collected At'
];

function createBatch_() {
  return {
    repositories: [],
    languages: [],
    snapshots: [],
    trafficViews: [],
    trafficClones: [],
    referrers: [],
    paths: [],
    contributors: [],
    branches: []
  };
}

function stageRepositoryMetadata_(batch, repo, activity, now) {
  batch.repositories.push([
    repo.repoId, repo.owner, repo.name, repo.fullName, repo.visibility,
    repo.defaultBranch, repo.archived ? 'yes' : 'no', repo.disabled ? 'yes' : 'no',
    repo.fork ? 'yes' : 'no', repo.parentFullName,
    repo.createdAt, repo.updatedAt, repo.pushedAt, repo.sizeKb, repo.primaryLanguage,
    repo.topics, repo.license, repo.homepage, repo.description,
    activity.openIssues, activity.closedIssues, activity.openPRs, activity.closedPRs,
    activity.mergedPRs, activity.draftPRs,
    repo.forkCount, repo.starCount, repo.subscriberCount, repo.networkCountApprox,
    repo.latestReleaseTag, repo.latestReleaseDate, repo.releaseCount,
    activity.latestCommitDate, now
  ]);
}

function stageLanguages_(batch, repo, collectionDate) {
  if (!repo.languages.length) return;
  repo.languages.forEach(function (l) {
    batch.languages.push([repo.repoId, repo.fullName, toIsoDate_(collectionDate), l.name, l.bytes]);
  });
}

function stageSnapshot_(batch, repo, activity, collectionDate) {
  batch.snapshots.push([
    toIsoDate_(collectionDate), repo.repoId, repo.fullName, repo.starCount, repo.forkCount,
    repo.subscriberCount, activity.openIssues, activity.openPRs, repo.sizeKb
  ]);
}

function stageTraffic_(batch, repo, traffic, collectionDate) {
  if (traffic.unavailable) return;

  traffic.views.forEach(function (v) {
    batch.trafficViews.push([repo.repoId, repo.fullName, toIsoDate_(v.date), v.count, v.uniques]);
  });
  traffic.clones.forEach(function (c) {
    batch.trafficClones.push([repo.repoId, repo.fullName, toIsoDate_(c.date), c.count, c.uniques]);
  });
  traffic.referrers.forEach(function (r) {
    batch.referrers.push([repo.repoId, repo.fullName, toIsoDate_(collectionDate), r.referrer, r.uniques, r.count]);
  });
  traffic.paths.forEach(function (p) {
    batch.paths.push([repo.repoId, repo.fullName, toIsoDate_(collectionDate), p.path, p.title, p.count, p.uniques]);
  });
}

function stageContributors_(batch, repo, contributors, collectionDate) {
  contributors.forEach(function (c) {
    batch.contributors.push([repo.repoId, repo.fullName, c.login, c.contributions, toIsoDate_(collectionDate)]);
  });
}

function stageBranches_(batch, repo, branches, collectionDate) {
  branches.forEach(function (b) {
    batch.branches.push([repo.repoId, repo.fullName, b.name, b.protected ? 'yes' : 'no', b.lastCommitSha, toIsoDate_(collectionDate)]);
  });
}

/**
 * Flushes everything staged so far to sheets — one upsert/append call
 * per sheet, covering all repos staged since the last flush. Clears the
 * batch's arrays afterward (in place) so a subsequent flush doesn't
 * re-write the same rows.
 */
function flushBatch_(batch) {
  if (batch.repositories.length) {
    upsertRows_(getOrCreateSheet_('Repositories', REPOSITORIES_HEADERS), batch.repositories, [1]);
  }
  if (batch.languages.length) {
    upsertRows_(getOrCreateSheet_('Languages', ['Repository ID', 'Repository', 'Collection Date', 'Language', 'Bytes']), batch.languages, [1, 3, 4]);
  }
  if (batch.snapshots.length) {
    upsertRows_(getOrCreateSheet_('RepositorySnapshots', ['Date', 'Repository ID', 'Repository', 'Stars', 'Forks', 'Watchers', 'Open Issues', 'Open Pull Requests', 'Size (KB)']), batch.snapshots, [1, 2]);
  }
  if (batch.trafficViews.length) {
    upsertRows_(getOrCreateSheet_('TrafficViews', ['Repository ID', 'Repository', 'Date', 'Views', 'Unique Views']), batch.trafficViews, [1, 3]);
  }
  if (batch.trafficClones.length) {
    upsertRows_(getOrCreateSheet_('TrafficClones', ['Repository ID', 'Repository', 'Date', 'Clones', 'Unique Clones']), batch.trafficClones, [1, 3]);
  }
  if (batch.referrers.length) {
    upsertRows_(getOrCreateSheet_('PopularReferrers', ['Repository ID', 'Repository', 'Collection Date', 'Referrer', 'Unique Visitors', 'Total Count']), batch.referrers, [1, 3, 4]);
  }
  if (batch.paths.length) {
    upsertRows_(getOrCreateSheet_('PopularPaths', ['Repository ID', 'Repository', 'Collection Date', 'Path', 'Title', 'Count', 'Unique']), batch.paths, [1, 3, 4]);
  }
  if (batch.contributors.length) {
    upsertRows_(getOrCreateSheet_('Contributors', ['Repository ID', 'Repository', 'GitHub Login', 'Contributions', 'Collection Date']), batch.contributors, [1, 3, 5]);
  }
  if (batch.branches.length) {
    upsertRows_(getOrCreateSheet_('Branches', ['Repository ID', 'Repository', 'Branch Name', 'Protected', 'Last Commit SHA', 'Collection Date']), batch.branches, [1, 3]);
  }

  batch.repositories.length = 0;
  batch.languages.length = 0;
  batch.snapshots.length = 0;
  batch.trafficViews.length = 0;
  batch.trafficClones.length = 0;
  batch.referrers.length = 0;
  batch.paths.length = 0;
  batch.contributors.length = 0;
  batch.branches.length = 0;
}

/**
 * Deletes PopularReferrers/PopularPaths rows older than
 * PRUNE_ROLLING_SNAPSHOTS_DAYS. Only worth running occasionally (this does
 * one full read+rewrite of each sheet), so it's gated to once a week —
 * called from main() regardless of RUN_FREQUENCY.
 */
function pruneOldRollingSnapshots_(config) {
  if (!shouldRunOnFrequency_(config, 'PRUNE_ROLLING_SNAPSHOTS_DAYS_TRIGGER_UNUSED', 7)) {
    // Intentionally always gated weekly regardless of the retention window
    // itself, to bound the cost of the rewrite. See PRUNE_ROLLING_SNAPSHOTS_DAYS
    // for how far back we keep.
  }
  var now = new Date();
  if (now.getDay() !== 0) return; // only run on Sundays

  var retentionDays = Math.max(7, Number(config.PRUNE_ROLLING_SNAPSHOTS_DAYS || 60));
  var cutoff = new Date(now.getTime() - retentionDays * 86400000);
  var cutoffIso = Utilities.formatDate(cutoff, 'Etc/UTC', 'yyyy-MM-dd');

  ['PopularReferrers', 'PopularPaths'].forEach(function (sheetName) {
    var sheet = getSpreadsheet_().getSheetByName(sheetName);
    if (!sheet || sheet.getLastRow() < 2) return;
    var data = sheet.getDataRange().getValues();
    var headers = data[0];
    var dateCol = headers.indexOf('Collection Date');
    if (dateCol === -1) return;

    var kept = [headers];
    for (var i = 1; i < data.length; i++) {
      var raw = data[i][dateCol];
      var iso = (raw instanceof Date) ? Utilities.formatDate(raw, 'Etc/UTC', 'yyyy-MM-dd') : String(raw);
      if (iso >= cutoffIso) kept.push(data[i]);
    }

    if (kept.length < data.length) {
      sheet.clearContents();
      sheet.getRange(1, 1, kept.length, headers.length).setValues(kept);
      sheet.getRange(1, 1, 1, headers.length).setFontWeight('bold');
      formatDateColumns_(sheet, headers);
      Logger.log('Pruned %s: removed %s rows older than %s', sheetName, data.length - kept.length, cutoffIso);
    }
  });
}


// ============================================================
// 11. MAIN ORCHESTRATOR + TRIGGERS  (FROM: Main.gs)
// ============================================================

function main() {
  var startTime = new Date();
  var config = getConfig();
  var maxRuntimeMs = Number(config.MAX_RUNTIME_MS || 300000);
  var flushEvery = Math.max(1, Number(config.BATCH_FLUSH_EVERY_N_REPOS || 25));

  var processed = 0;
  var failed = 0;
  var todayStr = Utilities.formatDate(startTime, 'Etc/UTC', 'yyyy-MM-dd');
  var batch = createBatch_();

  var repos;
  try {
    repos = discoverRepositories(config);
  } catch (e) {
    logError_('', 'discoverRepositories', 0, 'Fatal error during repo discovery: ' + e.message, false);
    flushErrorLog_();
    logExecution_({
      startTime: startTime, endTime: new Date(), repositoriesProcessed: 0, repositoriesFailed: 0,
      apiCallsMade: 'n/a', rateLimitRemaining: getRateLimitState().remaining,
      notes: 'ABORTED: repository discovery failed'
    });
    return;
  }

  Logger.log('Discovered %s repositories to process.', repos.length);

  for (var i = 0; i < repos.length; i++) {
    if (Date.now() - startTime.getTime() > maxRuntimeMs) {
      Logger.log('Time budget exceeded (%s ms); stopping early. %s/%s repos processed this run.', maxRuntimeMs, i, repos.length);
      break;
    }

    var repo = repos[i];
    try {
      processRepository_(repo, config, todayStr, startTime, batch);
      processed++;
    } catch (e) {
      failed++;
      logError_(repo.fullName, 'processRepository_', 0, 'Unhandled error: ' + e.message, false);
    }

    if ((i + 1) % flushEvery === 0) {
      flushBatch_(batch);
    }
  }

  // Final flush covers anything staged since the last periodic flush
  // (including a partial batch if we broke out early on time budget).
  flushBatch_(batch);

  try {
    pruneOldRollingSnapshots_(config);
  } catch (e) {
    logError_('', 'pruneOldRollingSnapshots_', 0, e.message, false);
  }

  var chartsRefreshed = false;
  var chartsError = '';
  try {
    initializeCharts();
    chartsRefreshed = true;
  } catch (e) {
    chartsError = e.message;
    logError_('', 'initializeCharts', 0, 'Automatic chart refresh failed: ' + e.message, false);
  }

  flushErrorLog_();
  logExecution_({
    startTime: startTime,
    endTime: new Date(),
    repositoriesProcessed: processed,
    repositoriesFailed: failed,
    apiCallsMade: 'see Stackdriver logs',
    rateLimitRemaining: getRateLimitState().remaining,
    notes: (repos.length > processed + failed ? 'Stopped early on time budget. ' : 'Completed full pass. ') +
      (chartsRefreshed ? 'Charts refreshed automatically.' : 'Chart refresh FAILED: ' + chartsError)
  });

  Logger.log('Run complete. Processed=%s Failed=%s ChartsRefreshed=%s RateLimitRemaining=%s',
    processed, failed, chartsRefreshed, getRateLimitState().remaining);
}

/**
 * Full collection pipeline for a single repository: fetches from GitHub
 * and stages rows into `batch`. Does NOT touch the spreadsheet directly
 * (see section 10) — that's what makes batched flushing possible.
 */
function processRepository_(repo, config, todayStr, runStartTime, batch) {
  var activity = { openIssues: repo.openIssues, closedIssues: 0, openPRs: repo.openPullRequests, closedPRs: 0, mergedPRs: 0, draftPRs: 0, latestCommitDate: '' };
  try {
    activity = collectActivitySummary(repo, config);
  } catch (e) {
    logError_(repo.fullName, 'collectActivitySummary', 0, e.message, false);
  }

  try {
    stageRepositoryMetadata_(batch, repo, activity, runStartTime);
  } catch (e) {
    logError_(repo.fullName, 'stageRepositoryMetadata_', 0, e.message, false);
  }

  try {
    stageLanguages_(batch, repo, todayStr);
  } catch (e) {
    logError_(repo.fullName, 'stageLanguages_', 0, e.message, false);
  }

  try {
    stageSnapshot_(batch, repo, activity, todayStr);
  } catch (e) {
    logError_(repo.fullName, 'stageSnapshot_', 0, e.message, false);
  }

  if (configBool_(config, 'COLLECT_TRAFFIC')) {
    try {
      var traffic = collectTraffic(repo, config);
      stageTraffic_(batch, repo, traffic, todayStr);
    } catch (e) {
      logError_(repo.fullName, 'collectTraffic', 0, e.message, false);
    }
  }

  if (configBool_(config, 'COLLECT_CONTRIBUTORS')) {
    try {
      var contributors = collectContributors(repo, config);
      stageContributors_(batch, repo, contributors, todayStr);
    } catch (e) {
      logError_(repo.fullName, 'collectContributors', 0, e.message, false);
    }
  }

  if (configBool_(config, 'COLLECT_BRANCHES')) {
    try {
      var branches = collectBranches(repo, config);
      stageBranches_(batch, repo, branches, todayStr);
    } catch (e) {
      logError_(repo.fullName, 'collectBranches', 0, e.message, false);
    }
  }
}

/** Installs the recurring trigger. Safe to re-run. */
function setupTriggers() {
  var triggers = ScriptApp.getProjectTriggers();
  triggers.forEach(function (t) {
    if (t.getHandlerFunction() === 'main') ScriptApp.deleteTrigger(t);
  });

  var config = getConfig();
  if (String(config.RUN_FREQUENCY).toLowerCase() === 'every6hours') {
    ScriptApp.newTrigger('main').timeBased().everyHours(6).create();
    Logger.log('Installed trigger: every 6 hours.');
  } else {
    ScriptApp.newTrigger('main').timeBased().atHour(2).everyDays(1).create();
    Logger.log('Installed trigger: daily at 02:00 UTC.');
  }
}

/**
 * One-time helper to initialize the spreadsheet + all sheets with headers
 * without doing a full collection run.
 */
function initializeSpreadsheet() {
  getConfig();
  getOrCreateSheet_('Repositories', REPOSITORIES_HEADERS);
  getOrCreateSheet_('RepositorySnapshots', ['Date', 'Repository ID', 'Repository', 'Stars', 'Forks', 'Watchers', 'Open Issues', 'Open Pull Requests', 'Size (KB)']);
  getOrCreateSheet_('TrafficViews', ['Repository ID', 'Repository', 'Date', 'Views', 'Unique Views']);
  getOrCreateSheet_('TrafficClones', ['Repository ID', 'Repository', 'Date', 'Clones', 'Unique Clones']);
  getOrCreateSheet_('PopularReferrers', ['Repository ID', 'Repository', 'Collection Date', 'Referrer', 'Unique Visitors', 'Total Count']);
  getOrCreateSheet_('PopularPaths', ['Repository ID', 'Repository', 'Collection Date', 'Path', 'Title', 'Count', 'Unique']);
  getOrCreateSheet_('Languages', ['Repository ID', 'Repository', 'Collection Date', 'Language', 'Bytes']);
  getOrCreateSheet_('Contributors', ['Repository ID', 'Repository', 'GitHub Login', 'Contributions', 'Collection Date']);
  getOrCreateSheet_('Branches', ['Repository ID', 'Repository', 'Branch Name', 'Protected', 'Last Commit SHA', 'Collection Date']);
  getOrCreateSheet_('ExecutionLog', ['Start Time', 'End Time', 'Duration (s)', 'Repositories Processed', 'Repositories Failed', 'API Calls Made', 'Rate Limit Remaining', 'Notes']);
  getOrCreateSheet_('Errors', ['Timestamp', 'Repository', 'Endpoint', 'HTTP Status', 'Message', 'Retry Attempted']);
  Logger.log('Spreadsheet initialized: ' + getSpreadsheet_().getUrl());
}


// ============================================================
// 12. CHARTS / TREND VISUALIZATION
// ============================================================
//
// initializeCharts() builds TWO parallel sets of tabs:
//   - "ChartData" (hidden) + "Charts"        -> every repo, public + private
//   - "PublicChartData" (hidden) + "Public Charts" -> public repos only
//
// This lets you safely share/publish just the "Public Charts" tab (Google
// Sheets supports publishing individual sheets) without exposing anything
// about your private repositories, while still keeping the full picture
// on "Charts" for yourself.
//
// Pivot tables are computed in Apps Script (reading the raw history
// sheets) rather than via live QUERY() formulas, so header rows are
// exactly the repo/category name every time, guaranteed — QUERY()'s
// auto-generated headers were unreliable to control.
//
// main() calls initializeCharts() automatically at the end of every
// collection run, so in normal use you shouldn't need to refresh by hand.
// The menu item still exists for on-demand use.

var CHART_DATE_FORMAT = 'yyyy-mm-dd';
var CHART_SECTION_GAP_ROWS = 2;

// --- Per-repository breakdown charts (one series per repo) ---
var CHART_BREAKDOWN_SECTIONS = [
  {
    title: 'Stars Over Time (by repository)',
    sourceSheet: 'RepositorySnapshots', dateHeader: 'Date', categoryHeader: 'Repository', valueHeader: 'Stars',
    chartTitle: 'Stars Over Time (by repository)', vAxisTitle: 'Stars',
    chartType: 'LINE', stacked: false, cumulative: false
  },
  {
    title: 'Forks Over Time (by repository)',
    sourceSheet: 'RepositorySnapshots', dateHeader: 'Date', categoryHeader: 'Repository', valueHeader: 'Forks',
    chartTitle: 'Forks Over Time (by repository)', vAxisTitle: 'Forks',
    chartType: 'LINE', stacked: false, cumulative: false
  },
  {
    title: 'Open Issues Over Time (by repository)',
    sourceSheet: 'RepositorySnapshots', dateHeader: 'Date', categoryHeader: 'Repository', valueHeader: 'Open Issues',
    chartTitle: 'Open Issues Over Time (by repository)', vAxisTitle: 'Open Issues',
    chartType: 'LINE', stacked: false, cumulative: false
  },
  {
    title: 'Traffic Views Per Day (by repository, stacked)',
    sourceSheet: 'TrafficViews', dateHeader: 'Date', categoryHeader: 'Repository', valueHeader: 'Views',
    chartTitle: 'Traffic Views Per Day (by repository, stacked)', vAxisTitle: 'Views',
    chartType: 'COLUMN', stacked: true, cumulative: false
  },
  {
    title: 'Traffic Clones Per Day (by repository, stacked)',
    sourceSheet: 'TrafficClones', dateHeader: 'Date', categoryHeader: 'Repository', valueHeader: 'Clones',
    chartTitle: 'Traffic Clones Per Day (by repository, stacked)', vAxisTitle: 'Clones',
    chartType: 'COLUMN', stacked: true, cumulative: false
  }
];

// --- Cumulative-over-time charts (stacked area, one band per repo) ---
var CHART_CUMULATIVE_SECTIONS = [
  {
    title: 'Cumulative Traffic Views Over Time (by repository, stacked)',
    sourceSheet: 'TrafficViews', dateHeader: 'Date', categoryHeader: 'Repository', valueHeader: 'Views',
    chartTitle: 'Cumulative Traffic Views Over Time (by repository, stacked)', vAxisTitle: 'Total Views To Date',
    chartType: 'AREA', stacked: true, cumulative: true
  },
  {
    title: 'Cumulative Traffic Clones Over Time (by repository, stacked)',
    sourceSheet: 'TrafficClones', dateHeader: 'Date', categoryHeader: 'Repository', valueHeader: 'Clones',
    chartTitle: 'Cumulative Traffic Clones Over Time (by repository, stacked)', vAxisTitle: 'Total Clones To Date',
    chartType: 'AREA', stacked: true, cumulative: true
  }
];

// --- Org-wide totals (sum across ALL repos, one line per metric) ---
// NEW in v2: v1 only ever showed per-repo breakdowns, which get cluttered
// past a handful of repos and never answer "what's our overall trend".
var CHART_TOTALS_SECTION = {
  title: 'Org-Wide Totals Over Time (summed across repositories)',
  sourceSheet: 'RepositorySnapshots', dateHeader: 'Date',
  metricHeaders: ['Stars', 'Forks', 'Open Issues', 'Open Pull Requests'],
  chartTitle: 'Org-Wide Totals Over Time', vAxisTitle: 'Total',
  chartType: 'LINE'
};

// --- Snapshot charts: most-recent-collection-date top N, across all repos ---
var CHART_SNAPSHOT_SECTIONS = [
  {
    title: 'Top Paths — Most Recent 14-Day Snapshot (all repos)',
    sourceSheet: 'PopularPaths', dateHeader: 'Collection Date', valueHeader: 'Count',
    labelFn: function (row, idx) { return row[idx.Repository] + ' — ' + row[idx.Path]; },
    repoColHeader: 'Repository',
    chartTitle: 'Top Paths — Most Recent 14-Day Snapshot (All Repos)',
    axisTitle: 'Views (trailing 14 days)',
    limit: 20
  },
  {
    title: 'Top Referrers — Most Recent 14-Day Snapshot (all repos)',
    sourceSheet: 'PopularReferrers', dateHeader: 'Collection Date', valueHeader: 'Total Count',
    labelFn: function (row, idx) { return row[idx.Repository] + ' — ' + row[idx.Referrer]; },
    repoColHeader: 'Repository',
    chartTitle: 'Top Referrers — Most Recent 14-Day Snapshot (All Repos)',
    axisTitle: 'Visits (trailing 14 days)',
    limit: 20
  }
];

// NEW in v2: languages were collected every run but never charted.
var CHART_LANGUAGE_SECTION = {
  title: 'Top Languages by Bytes — Most Recent Snapshot (all repos)',
  chartTitle: 'Top Languages by Bytes (All Repos)',
  axisTitle: 'Bytes',
  limit: 15
};

// NEW in v2: PR merge-rate snapshot, from data already collected but
// previously never visualized (Repositories is current-state, so no
// "most recent date" filtering needed — it's always current).
var CHART_MERGE_RATE_SECTION = {
  title: 'PR Merge Rate by Repository (current)',
  chartTitle: 'PR Merge Rate by Repository (% of resolved PRs merged)',
  axisTitle: 'Merge Rate (%)',
  limit: 25
};

/** Builds a Full Name -> 'public'/'private' lookup from the Repositories sheet. */
function getVisibilityMap_() {
  var sheet = getSpreadsheet_().getSheetByName('Repositories');
  if (!sheet || sheet.getLastRow() < 2) return {};
  var data = sheet.getDataRange().getValues();
  var headers = data[0];
  var nameCol = headers.indexOf('Full Name');
  var visCol = headers.indexOf('Visibility');
  var map = {};
  for (var i = 1; i < data.length; i++) map[data[i][nameCol]] = data[i][visCol];
  return map;
}

function repoAllowedByVisibility_(repoFullName, visibilityFilter, visibilityMap) {
  if (!visibilityFilter || visibilityFilter === 'all') return true;
  return visibilityMap[repoFullName] === visibilityFilter;
}

/**
 * Reads a raw history sheet, keeps only rows from the most recent
 * Collection Date, optionally filters by repo visibility, sorts by value
 * descending, and returns the top `limit` as
 * [ ['Item', valueHeader], [label, value], ... ] reversed for BAR charts.
 */
function buildLatestSnapshotTable_(sourceSheetName, dateHeader, valueHeader, labelFn, limit, opts) {
  opts = opts || {};
  var ss = getSpreadsheet_();
  var sheet = ss.getSheetByName(sourceSheetName);
  if (!sheet || sheet.getLastRow() < 2) return null;

  var data = sheet.getDataRange().getValues();
  var headers = data[0];
  var dateCol = headers.indexOf(dateHeader);
  var valCol = headers.indexOf(valueHeader);
  if (dateCol === -1 || valCol === -1) return null;

  var idx = {};
  headers.forEach(function (h, i) { idx[h] = i; });
  var repoCol = opts.repoColHeader ? idx[opts.repoColHeader] : -1;

  var isoOf = function (raw) {
    return (raw instanceof Date) ? Utilities.formatDate(raw, 'Etc/UTC', 'yyyy-MM-dd') : String(raw);
  };

  var latestIso = null;
  for (var i = 1; i < data.length; i++) {
    var iso = isoOf(data[i][dateCol]);
    if (!latestIso || iso > latestIso) latestIso = iso;
  }
  if (!latestIso) return null;

  var rows = [];
  for (var j = 1; j < data.length; j++) {
    if (isoOf(data[j][dateCol]) !== latestIso) continue;
    if (repoCol !== -1 && opts.visibilityFilter && opts.visibilityFilter !== 'all') {
      var repoName = data[j][repoCol];
      if (!repoAllowedByVisibility_(repoName, opts.visibilityFilter, opts.visibilityMap || {})) continue;
    }
    rows.push([labelFn(data[j], idx), Number(data[j][valCol]) || 0]);
  }
  if (!rows.length) return null;

  rows.sort(function (a, b) { return b[1] - a[1]; });
  rows = rows.slice(0, limit).reverse();
  return [['Item', valueHeader]].concat(rows);
}

/**
 * Reads a raw history sheet and pivots it into
 * [ ['Date', cat1, cat2, ...], [date, val, val, ...], ... ].
 * Optionally filters categories by repo visibility, and optionally
 * collapses to "Top N + Other" (by most recent value) once there are
 * more categories than topN.
 */
function buildPivotTable_(sourceSheetName, dateHeader, categoryHeader, valueHeader, cumulative, opts) {
  opts = opts || {};
  var ss = getSpreadsheet_();
  var sheet = ss.getSheetByName(sourceSheetName);
  if (!sheet || sheet.getLastRow() < 2) return null;

  var data = sheet.getDataRange().getValues();
  var headers = data[0];
  var dateCol = headers.indexOf(dateHeader);
  var catCol = headers.indexOf(categoryHeader);
  var valCol = headers.indexOf(valueHeader);
  if (dateCol === -1 || catCol === -1 || valCol === -1) return null;

  var categoriesSet = {};
  var byDate = {};

  for (var i = 1; i < data.length; i++) {
    var rawDate = data[i][dateCol];
    var category = String(data[i][catCol] || '').trim();
    var value = Number(data[i][valCol]) || 0;
    if (!category || !rawDate) continue;
    if (opts.visibilityFilter && opts.visibilityFilter !== 'all') {
      if (!repoAllowedByVisibility_(category, opts.visibilityFilter, opts.visibilityMap || {})) continue;
    }
    var isoKey = (rawDate instanceof Date)
      ? Utilities.formatDate(rawDate, 'Etc/UTC', 'yyyy-MM-dd')
      : String(rawDate);

    categoriesSet[category] = true;
    if (!byDate[isoKey]) byDate[isoKey] = {};
    byDate[isoKey][category] = (byDate[isoKey][category] || 0) + value;
  }

  var categories = Object.keys(categoriesSet).sort();
  var sortedDates = Object.keys(byDate).sort();
  if (!categories.length || !sortedDates.length) return null;

  // Top-N + Other: pick categories by their value on the most recent date.
  if (opts.topN && categories.length > opts.topN) {
    var latestDate = sortedDates[sortedDates.length - 1];
    var latestValues = byDate[latestDate] || {};
    var ranked = categories.slice().sort(function (a, b) {
      return (latestValues[b] || 0) - (latestValues[a] || 0);
    });
    var keepSet = {};
    ranked.slice(0, opts.topN).forEach(function (c) { keepSet[c] = true; });
    categories = ranked.slice(0, opts.topN).sort();
    categories.push('Other (' + (ranked.length - opts.topN) + ' repos)');
    var otherLabel = categories[categories.length - 1];

    var collapsedByDate = {};
    sortedDates.forEach(function (isoKey) {
      var dayValues = byDate[isoKey];
      var newDay = {};
      Object.keys(dayValues).forEach(function (c) {
        var target = keepSet[c] ? c : otherLabel;
        newDay[target] = (newDay[target] || 0) + dayValues[c];
      });
      collapsedByDate[isoKey] = newDay;
    });
    byDate = collapsedByDate;
  }

  var table = [['Date'].concat(categories)];
  var runningTotals = {};
  categories.forEach(function (c) { runningTotals[c] = 0; });

  sortedDates.forEach(function (isoKey) {
    var dayValues = byDate[isoKey];
    var row = [toIsoDate_(isoKey)];
    categories.forEach(function (c) {
      if (cumulative) {
        if (dayValues[c] !== undefined) runningTotals[c] += dayValues[c];
        row.push(runningTotals[c]);
      } else {
        row.push(dayValues[c] !== undefined ? dayValues[c] : null);
      }
    });
    table.push(row);
  });
  return table;
}

/**
 * Builds an org-wide totals table: sums each metric across ALL matching
 * repos per date, one column per metric (not per repo).
 */
function buildTotalsTable_(sourceSheetName, dateHeader, metricHeaders, opts) {
  opts = opts || {};
  var ss = getSpreadsheet_();
  var sheet = ss.getSheetByName(sourceSheetName);
  if (!sheet || sheet.getLastRow() < 2) return null;

  var data = sheet.getDataRange().getValues();
  var headers = data[0];
  var dateCol = headers.indexOf(dateHeader);
  var repoCol = headers.indexOf('Repository');
  var metricCols = metricHeaders.map(function (h) { return headers.indexOf(h); });
  if (dateCol === -1 || metricCols.indexOf(-1) !== -1) return null;

  var byDate = {};
  for (var i = 1; i < data.length; i++) {
    var rawDate = data[i][dateCol];
    if (!rawDate) continue;
    if (opts.visibilityFilter && opts.visibilityFilter !== 'all' && repoCol !== -1) {
      if (!repoAllowedByVisibility_(data[i][repoCol], opts.visibilityFilter, opts.visibilityMap || {})) continue;
    }
    var isoKey = (rawDate instanceof Date)
      ? Utilities.formatDate(rawDate, 'Etc/UTC', 'yyyy-MM-dd')
      : String(rawDate);
    if (!byDate[isoKey]) byDate[isoKey] = metricHeaders.map(function () { return 0; });
    metricCols.forEach(function (col, m) {
      byDate[isoKey][m] += Number(data[i][col]) || 0;
    });
  }

  var sortedDates = Object.keys(byDate).sort();
  if (!sortedDates.length) return null;

  var table = [['Date'].concat(metricHeaders)];
  sortedDates.forEach(function (isoKey) {
    table.push([toIsoDate_(isoKey)].concat(byDate[isoKey]));
  });
  return table;
}

/**
 * Builds a "top languages by total bytes, most recent snapshot" table,
 * summed across all (visibility-filtered) repos.
 */
function buildLanguageSnapshotTable_(limit, opts) {
  opts = opts || {};
  var ss = getSpreadsheet_();
  var sheet = ss.getSheetByName('Languages');
  if (!sheet || sheet.getLastRow() < 2) return null;

  var data = sheet.getDataRange().getValues();
  var headers = data[0];
  var dateCol = headers.indexOf('Collection Date');
  var repoCol = headers.indexOf('Repository');
  var langCol = headers.indexOf('Language');
  var bytesCol = headers.indexOf('Bytes');
  if ([dateCol, repoCol, langCol, bytesCol].indexOf(-1) !== -1) return null;

  var isoOf = function (raw) {
    return (raw instanceof Date) ? Utilities.formatDate(raw, 'Etc/UTC', 'yyyy-MM-dd') : String(raw);
  };

  var latestIso = null;
  for (var i = 1; i < data.length; i++) {
    var iso = isoOf(data[i][dateCol]);
    if (!latestIso || iso > latestIso) latestIso = iso;
  }
  if (!latestIso) return null;

  var totals = {};
  for (var j = 1; j < data.length; j++) {
    if (isoOf(data[j][dateCol]) !== latestIso) continue;
    if (opts.visibilityFilter && opts.visibilityFilter !== 'all') {
      if (!repoAllowedByVisibility_(data[j][repoCol], opts.visibilityFilter, opts.visibilityMap || {})) continue;
    }
    var lang = data[j][langCol];
    totals[lang] = (totals[lang] || 0) + (Number(data[j][bytesCol]) || 0);
  }

  var rows = Object.keys(totals).map(function (lang) { return [lang, totals[lang]]; });
  if (!rows.length) return null;
  rows.sort(function (a, b) { return b[1] - a[1]; });
  rows = rows.slice(0, limit).reverse();
  return [['Language', 'Bytes']].concat(rows);
}

/**
 * Builds a PR merge-rate table from current Repositories data: for each
 * (visibility-filtered) repo, merge rate = merged / (merged + closed),
 * i.e. of all PRs that reached a final resolved state, what % were merged
 * rather than closed unmerged. Repos with zero resolved PRs are omitted
 * (rate is undefined, not zero).
 */
function buildMergeRateTable_(limit, opts) {
  opts = opts || {};
  var ss = getSpreadsheet_();
  var sheet = ss.getSheetByName('Repositories');
  if (!sheet || sheet.getLastRow() < 2) return null;

  var data = sheet.getDataRange().getValues();
  var headers = data[0];
  var nameCol = headers.indexOf('Full Name');
  var visCol = headers.indexOf('Visibility');
  var mergedCol = headers.indexOf('Merged Pull Requests');
  var closedCol = headers.indexOf('Closed Pull Requests');
  if ([nameCol, mergedCol, closedCol].indexOf(-1) !== -1) return null;

  var rows = [];
  for (var i = 1; i < data.length; i++) {
    if (opts.visibilityFilter && opts.visibilityFilter !== 'all' && data[i][visCol] !== opts.visibilityFilter) continue;
    var merged = Number(data[i][mergedCol]) || 0;
    var closed = Number(data[i][closedCol]) || 0;
    var resolved = merged + closed;
    if (resolved === 0) continue;
    rows.push([data[i][nameCol], Math.round((merged / resolved) * 1000) / 10]);
  }
  if (!rows.length) return null;

  rows.sort(function (a, b) { return b[1] - a[1]; });
  rows = rows.slice(0, limit).reverse();
  return [['Repository', 'Merge Rate (%)']].concat(rows);
}

function writePivotTableToDataSheet_(dataSheet, anchorRow, title, table) {
  dataSheet.getRange(anchorRow, 1).setValue(title).setFontWeight('bold');
  var headerRow = anchorRow + 1;

  if (!table) {
    dataSheet.getRange(headerRow, 1).setValue('No data yet — run main() first (traffic charts also need Administration:Read on the PAT).');
    return { headerRow: headerRow, numRows: 1, numCols: 1 };
  }

  dataSheet.getRange(headerRow, 1, table.length, table[0].length).setValues(table);
  if (table.length > 1) {
    dataSheet.getRange(headerRow + 1, 1, table.length - 1, 1).setNumberFormat(CHART_DATE_FORMAT);
  }
  return { headerRow: headerRow, numRows: table.length, numCols: table[0].length };
}

/**
 * Builds one full chart tab pair (dataSheetName + chartSheetName) filtered
 * to the given visibility ('all' | 'public' | 'private'). Called twice by
 * initializeCharts(): once for everything, once for public-only.
 */
function buildChartTabs_(dataSheetName, chartSheetName, visibilityFilter, visibilityMap, topN) {
  var ss = getSpreadsheet_();

  var dataSheet = ss.getSheetByName(dataSheetName);
  if (dataSheet) {
    dataSheet.showSheet();
    dataSheet.clear();
  } else {
    dataSheet = ss.insertSheet(dataSheetName);
  }

  var chartSheet = ss.getSheetByName(chartSheetName);
  if (!chartSheet) chartSheet = ss.insertSheet(chartSheetName);
  chartSheet.getCharts().forEach(function (c) { chartSheet.removeChart(c); });
  chartSheet.clear();

  var opts = { visibilityFilter: visibilityFilter, visibilityMap: visibilityMap, topN: topN };
  var currentRow = 2;
  var chartIndex = 0;
  var chartsBuilt = 0;
  var chartsTotal = 0;
  var maxColsUsed = 1;

  function placeSeriesChart_(table, chartType, chartTitle, vAxisTitle, stacked) {
    var placed = writePivotTableToDataSheet_(dataSheet, currentRow, chartTitle, table);
    maxColsUsed = Math.max(maxColsUsed, placed.numCols);
    if (table) {
      var range = dataSheet.getRange(placed.headerRow, 1, placed.numRows, placed.numCols);
      var builder = chartSheet.newChart()
        .setChartType(Charts.ChartType[chartType])
        .addRange(range)
        .setNumHeaders(1)
        .setOption('title', chartTitle)
        .setOption('vAxis', { title: vAxisTitle })
        .setOption('hAxis', { title: 'Date', format: CHART_DATE_FORMAT, slantedText: true, slantedTextAngle: 30 })
        .setOption('legend', { position: 'right', textStyle: { fontSize: 10 } })
        .setOption('width', 900)
        .setOption('height', 400)
        .setPosition(2 + chartIndex * 22, 1, 0, 0);
      if (stacked) builder.setOption('isStacked', true);
      chartSheet.insertChart(builder.build());
      chartIndex++;
      chartsBuilt++;
    }
    currentRow = placed.headerRow + placed.numRows + CHART_SECTION_GAP_ROWS;
  }

  function placeBarChart_(table, chartTitle, axisTitle, height) {
    var placed = writePivotTableToDataSheet_(dataSheet, currentRow, chartTitle, table);
    maxColsUsed = Math.max(maxColsUsed, placed.numCols);
    if (table) {
      var range = dataSheet.getRange(placed.headerRow, 1, placed.numRows, placed.numCols);
      var chart = chartSheet.newChart()
        .setChartType(Charts.ChartType.BAR)
        .addRange(range)
        .setNumHeaders(1)
        .setOption('title', chartTitle)
        .setOption('hAxis', { title: axisTitle })
        .setOption('legend', { position: 'none' })
        .setOption('width', 900)
        .setOption('height', height || 450)
        .setPosition(2 + chartIndex * 22, 1, 0, 0)
        .build();
      chartSheet.insertChart(chart);
      chartIndex++;
      chartsBuilt++;
    }
    currentRow = placed.headerRow + placed.numRows + CHART_SECTION_GAP_ROWS;
  }

  // Per-repo breakdown charts (Top N + Other applied via opts.topN)
  CHART_BREAKDOWN_SECTIONS.forEach(function (section) {
    chartsTotal++;
    var table = buildPivotTable_(section.sourceSheet, section.dateHeader, section.categoryHeader, section.valueHeader, section.cumulative, opts);
    placeSeriesChart_(table, section.chartType, section.chartTitle, section.vAxisTitle, section.stacked);
  });

  // Cumulative charts
  CHART_CUMULATIVE_SECTIONS.forEach(function (section) {
    chartsTotal++;
    var table = buildPivotTable_(section.sourceSheet, section.dateHeader, section.categoryHeader, section.valueHeader, section.cumulative, opts);
    placeSeriesChart_(table, section.chartType, section.chartTitle, section.vAxisTitle, section.stacked);
  });

  // Org-wide totals (NEW)
  chartsTotal++;
  var totalsTable = buildTotalsTable_(CHART_TOTALS_SECTION.sourceSheet, CHART_TOTALS_SECTION.dateHeader, CHART_TOTALS_SECTION.metricHeaders, opts);
  placeSeriesChart_(totalsTable, CHART_TOTALS_SECTION.chartType, CHART_TOTALS_SECTION.chartTitle, CHART_TOTALS_SECTION.vAxisTitle, false);

  // Snapshot bar charts (paths / referrers)
  CHART_SNAPSHOT_SECTIONS.forEach(function (section) {
    chartsTotal++;
    var table = buildLatestSnapshotTable_(section.sourceSheet, section.dateHeader, section.valueHeader, section.labelFn, section.limit,
      { visibilityFilter: visibilityFilter, visibilityMap: visibilityMap, repoColHeader: section.repoColHeader });
    placeBarChart_(table, section.chartTitle, section.axisTitle, 450);
  });

  // Language mix snapshot (NEW)
  chartsTotal++;
  var langTable = buildLanguageSnapshotTable_(CHART_LANGUAGE_SECTION.limit, opts);
  placeBarChart_(langTable, CHART_LANGUAGE_SECTION.chartTitle, CHART_LANGUAGE_SECTION.axisTitle, 400);

  // PR merge rate snapshot (NEW)
  chartsTotal++;
  var mergeTable = buildMergeRateTable_(CHART_MERGE_RATE_SECTION.limit, opts);
  placeBarChart_(mergeTable, CHART_MERGE_RATE_SECTION.chartTitle, CHART_MERGE_RATE_SECTION.axisTitle, 450);

  trimUnusedColumns_(dataSheet, maxColsUsed);
  dataSheet.hideSheet();

  return { chartsBuilt: chartsBuilt, chartsTotal: chartsTotal };
}

/**
 * Pins "Charts" and "Public Charts" as the first two visible tabs (in that
 * order), so they're always the leftmost tabs regardless of how many data
 * sheets accumulate. Safe to call every run — it's a no-op re-ordering if
 * they're already in place. Hidden data sheets (ChartData/PublicChartData)
 * are left wherever they are since they're not meant to be browsed.
 */
function moveChartTabsToFront_() {
  var ss = getSpreadsheet_();
  var chartsSheet = ss.getSheetByName('Charts');
  var publicChartsSheet = ss.getSheetByName('Public Charts');
  if (chartsSheet) {
    ss.setActiveSheet(chartsSheet);
    ss.moveActiveSheet(1);
  }
  if (publicChartsSheet) {
    ss.setActiveSheet(publicChartsSheet);
    ss.moveActiveSheet(2);
  }
}

/**
 * Run this any time to (re)build all trend charts. Builds both the
 * all-repo tabs ("ChartData"/"Charts") and the public-only tabs
 * ("PublicChartData"/"Public Charts"), then pins Charts/Public Charts as
 * the leftmost tabs.
 */
function initializeCharts() {
  var config = getConfig();
  var topN = Math.max(3, Number(config.CHART_TOP_N_REPOS || 15));
  var visibilityMap = getVisibilityMap_();

  var allResult = buildChartTabs_('ChartData', 'Charts', 'all', visibilityMap, topN);
  var publicResult = buildChartTabs_('PublicChartData', 'Public Charts', 'public', visibilityMap, topN);
  moveChartTabsToFront_();

  Logger.log('Charts initialized. All-repo tab: %s/%s charts had data. Public-only tab: %s/%s charts had data. URL: %s',
    allResult.chartsBuilt, allResult.chartsTotal, publicResult.chartsBuilt, publicResult.chartsTotal, getSpreadsheet_().getUrl());
}


// ============================================================
// 13. RAW API DEBUG EXPORT (manual diagnostic — not part of the daily run)
// ============================================================
/**
 * exportRawApiSnapshot() is meant to be run BY HAND when something in the
 * collected data looks off (e.g. "why don't I get a Views row for repo X
 * on date Y"). It re-hits every REST/GraphQL endpoint the normal collector
 * uses, one repo at a time, and writes the UNTOUCHED response body — no
 * normalization, no filtering, no interpretation — into a "RawApiDebug"
 * sheet. That gives you (or anyone helping you debug) ground truth on
 * exactly what GitHub sent back, separate from anything this script did
 * with it afterward.
 *
 * It reuses ghFetch_() for every call, so you still get the same
 * retry/backoff/rate-limit handling and 403 logging as the real
 * collector — this is meant to mirror production behavior, not bypass it.
 *
 * Each run clears previous debug rows first, so the sheet always reflects
 * only the most recent snapshot rather than accumulating forever (unlike
 * the main data sheets, this one is a diagnostic snapshot, not a history).
 *
 * NOTE ON COLL SIZE: Google Sheets caps a single cell at 50,000 characters.
 * Traffic/activity responses are small and will never hit this, but very
 * large contributors/branches lists could. Anything over ~45,000 chars is
 * truncated with a note (and the true length is recorded in its own
 * column) rather than causing the whole export to fail.
 */

var RAW_DEBUG_MAX_CELL_CHARS = 45000; // headroom under Sheets' 50,000-char cell limit

function exportRawApiSnapshot() {
  var config = getConfig();
  var timestamp = new Date();

  var sheet = getOrCreateSheet_('RawApiDebug', [
    'Timestamp', 'Repository', 'Endpoint', 'HTTP Status', 'URL', 'Response Length (chars)', 'Raw Response (JSON; truncated if very long)'
  ]);

  // Diagnostic snapshot, not a history table — clear prior rows so this
  // always shows only the latest run.
  if (sheet.getLastRow() > 1) {
    sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getMaxColumns()).clearContent();
  }

  var repos;
  try {
    repos = discoverRepositories(config);
  } catch (e) {
    logError_('', 'exportRawApiSnapshot:discoverRepositories', 0, 'Fatal error during repo discovery: ' + e.message, false);
    flushErrorLog_();
    throw e;
  }

  var rows = [];
  repos.forEach(function (repo) {
    var owner = repo.owner, name = repo.name;

    rows.push(rawDebugRow_(timestamp, repo.fullName, 'traffic/views',
      GITHUB_API_ROOT + '/repos/' + owner + '/' + name + '/traffic/views', config));
    rows.push(rawDebugRow_(timestamp, repo.fullName, 'traffic/clones',
      GITHUB_API_ROOT + '/repos/' + owner + '/' + name + '/traffic/clones', config));
    rows.push(rawDebugRow_(timestamp, repo.fullName, 'traffic/popular/referrers',
      GITHUB_API_ROOT + '/repos/' + owner + '/' + name + '/traffic/popular/referrers', config));
    rows.push(rawDebugRow_(timestamp, repo.fullName, 'traffic/popular/paths',
      GITHUB_API_ROOT + '/repos/' + owner + '/' + name + '/traffic/popular/paths', config));
    rows.push(rawDebugRow_(timestamp, repo.fullName, 'contributors (page 1, raw)',
      GITHUB_API_ROOT + '/repos/' + owner + '/' + name + '/contributors?per_page=100&anon=0', config));
    rows.push(rawDebugRow_(timestamp, repo.fullName, 'branches (page 1, raw)',
      GITHUB_API_ROOT + '/repos/' + owner + '/' + name + '/branches?per_page=100', config));

    // Per-repo GraphQL activity query (issue/PR states, draft PRs, latest
    // commit) — called directly here (rather than via collectActivitySummary)
    // so we capture the true raw response body, not the normalized object.
    var pauseMs = Number(config.ACTIVITY_QUERY_PAUSE_MS || 150);
    if (pauseMs > 0) Utilities.sleep(pauseMs);
    var activityRes = ghFetch_(GITHUB_GRAPHQL_URL, {
      method: 'post',
      payload: { query: ACTIVITY_QUERY, variables: buildActivityVariables_(repo) },
      repoFullName: repo.fullName,
      config: config
    });
    rows.push(buildRawDebugRowFromResponse_(timestamp, repo.fullName, 'graphql:activitySummary', GITHUB_GRAPHQL_URL, activityRes));
  });

  if (rows.length) {
    sheet.getRange(sheet.getLastRow() + 1, 1, rows.length, rows[0].length).setValues(rows);
  }

  flushErrorLog_();
  Logger.log('Raw API snapshot exported: %s repositories, %s rows, sheet "RawApiDebug". %s',
    repos.length, rows.length, getSpreadsheet_().getUrl());
}

function rawDebugRow_(timestamp, repoFullName, endpointLabel, url, config) {
  var res = ghFetch_(url, { repoFullName: repoFullName, config: config });
  return buildRawDebugRowFromResponse_(timestamp, repoFullName, endpointLabel, url, res);
}

function buildRawDebugRowFromResponse_(timestamp, repoFullName, endpointLabel, url, res) {
  var raw = res.rawText || '';
  var length = raw.length;
  var stored = raw;
  if (length > RAW_DEBUG_MAX_CELL_CHARS) {
    stored = raw.substring(0, RAW_DEBUG_MAX_CELL_CHARS) + '\n...TRUNCATED (' + length + ' total chars) — full response was too long for one cell...';
  }
  return [timestamp, repoFullName, endpointLabel, res.status, url, length, stored];
}


// ============================================================
// 14. MAINTENANCE: DEDUPLICATE + REPAIR KEYED SHEETS (manual, on-demand)
// ============================================================
/**
 * Removes duplicate rows from a sheet, keyed by one or more 1-indexed
 * column numbers. Rewrites the sheet in place. Not run automatically —
 * this is a manual cleanup tool for situations where rows got into a
 * sheet WITHOUT going through upsertRows_() (e.g. a manual copy/paste
 * migration from an old spreadsheet), since that's the one path that can
 * introduce duplicate keys the normal collection flow prevents.
 *
 * keepLast=true (default): when the same key appears more than once, the
 * LAST matching row in the sheet wins and earlier ones are discarded —
 * sensible default since a later row is more likely to reflect a more
 * recent write. Pass false to keep the first occurrence instead.
 */
function dedupeSheetByKeys_(sheetName, keyColumns, keepLast) {
  if (keepLast === undefined) keepLast = true;
  var sheet = getSpreadsheet_().getSheetByName(sheetName);
  if (!sheet || sheet.getLastRow() < 2) return { removed: 0, remaining: 0 };

  var data = sheet.getDataRange().getValues();
  var headers = data[0];
  var seenAt = {}; // key -> index into `kept`
  var kept = [];

  for (var i = 1; i < data.length; i++) {
    var row = data[i];
    var key = keyColumns.map(function (c) { return normalizeKeyValue_(row[c - 1]); }).join('||');
    if (seenAt.hasOwnProperty(key)) {
      if (keepLast) kept[seenAt[key]] = row; // overwrite with the later row
      // else: first occurrence already kept, skip this one
    } else {
      seenAt[key] = kept.length;
      kept.push(row);
    }
  }

  var removed = (data.length - 1) - kept.length;
  if (removed > 0) {
    sheet.clearContents();
    sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
    sheet.getRange(1, 1, 1, headers.length).setFontWeight('bold');
    if (kept.length) {
      sheet.getRange(2, 1, kept.length, headers.length).setValues(kept);
    }
    formatDateColumns_(sheet, headers);
  }
  return { removed: removed, remaining: kept.length };
}

/**
 * Menu-callable: deduplicates TrafficViews and TrafficClones by their true
 * key (Repository ID + Date — columns 1 and 3), and shows a summary.
 */
function dedupeTrafficSheets() {
  var viewsResult = dedupeSheetByKeys_('TrafficViews', [1, 3], true);
  var clonesResult = dedupeSheetByKeys_('TrafficClones', [1, 3], true);

  var message = 'TrafficViews: removed ' + viewsResult.removed + ', ' + viewsResult.remaining + ' rows remain.\n' +
    'TrafficClones: removed ' + clonesResult.removed + ', ' + clonesResult.remaining + ' rows remain.';
  Logger.log('dedupeTrafficSheets: %s', message.replace('\n', ' | '));

  var ui = SpreadsheetApp.getUi();
  if (ui) ui.alert('Deduplication complete', message, ui.ButtonSet.OK);
}


/**
 * Repairs Date-suffixed columns (any header ending in "Date") in the given
 * sheets where the underlying value is a real Date object but NOT exactly
 * midnight UTC. This specifically targets legacy rows — almost always from
 * an old, pre-fix version of the collector, or a manual spreadsheet
 * migration — where a plain date-like STRING got auto-converted by Sheets
 * using the spreadsheet's own local timezone instead of UTC (e.g. a
 * Europe/Stockholm spreadsheet turning "2026-06-25" into 2026-06-24T22:00Z).
 *
 * Such a cell renders identically to a clean UTC-midnight row for the
 * "next" day (both display as the same date locally), which is exactly
 * what makes them look like duplicates in the sheet while the UTC-based
 * key logic (correctly) treats them as different days — meaning dedupe
 * alone can't fix this; the stored value itself needs correcting.
 *
 * The fix: read off the calendar day the cell was clearly INTENDED to
 * represent (using the spreadsheet's own timezone, since that's how it
 * was mis-created), then rewrite it as clean midnight UTC for that day —
 * consistent with how every other date in these sheets is stored.
 */
function repairNonMidnightUtcDates_(sheetNames) {
  var ss = getSpreadsheet_();
  var tz = ss.getSpreadsheetTimeZone();
  var totalFixed = 0;

  sheetNames.forEach(function (sheetName) {
    var sheet = ss.getSheetByName(sheetName);
    if (!sheet || sheet.getLastRow() < 2) return;

    var lastCol = sheet.getLastColumn();
    var headers = sheet.getRange(1, 1, 1, lastCol).getValues()[0];
    var fixedThisSheet = 0;

    headers.forEach(function (header, colIdx) {
      if (!/Date$/.test(header)) return;
      var range = sheet.getRange(2, colIdx + 1, sheet.getLastRow() - 1, 1);
      var values = range.getValues();
      var changed = false;

      for (var i = 0; i < values.length; i++) {
        var v = values[i][0];
        if (v instanceof Date) {
          var utcTimeOfDay = Utilities.formatDate(v, 'Etc/UTC', 'HH:mm:ss');
          if (utcTimeOfDay !== '00:00:00') {
            var intendedLocalDay = Utilities.formatDate(v, tz, 'yyyy-MM-dd');
            values[i][0] = Utilities.parseDate(intendedLocalDay, 'Etc/UTC', 'yyyy-MM-dd');
            changed = true;
            fixedThisSheet++;
          }
        }
      }
      if (changed) range.setValues(values);
    });

    if (fixedThisSheet > 0) {
      Logger.log('%s: repaired %s non-UTC-midnight date cell(s)', sheetName, fixedThisSheet);
    }
    totalFixed += fixedThisSheet;
  });

  return totalFixed;
}

/**
 * Menu-callable: repairs timezone-drifted date cells in TrafficViews and
 * TrafficClones, THEN deduplicates them — repair has to run first, since
 * dedupe can only merge rows whose keys already match.
 */
function repairAndDedupeTrafficSheets() {
  var fixed = repairNonMidnightUtcDates_(['TrafficViews', 'TrafficClones']);
  var viewsResult = dedupeSheetByKeys_('TrafficViews', [1, 3], true);
  var clonesResult = dedupeSheetByKeys_('TrafficClones', [1, 3], true);

  var message = 'Repaired ' + fixed + ' timezone-drifted date cell(s).\n' +
    'TrafficViews: removed ' + viewsResult.removed + ', ' + viewsResult.remaining + ' rows remain.\n' +
    'TrafficClones: removed ' + clonesResult.removed + ', ' + clonesResult.remaining + ' rows remain.';
  Logger.log('repairAndDedupeTrafficSheets: %s', message.replace(/\n/g, ' | '));

  var ui = SpreadsheetApp.getUi();
  if (ui) ui.alert('Repair + deduplication complete', message, ui.ButtonSet.OK);
}


// ============================================================
// 15. CUSTOM MENU (onOpen)
// ============================================================

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('GitHub Analytics')
    .addItem('1. Initialize spreadsheet (first-time setup)', 'initializeSpreadsheet')
    .addItem('2. Run collection now (main)', 'main')
    .addItem('3. Install daily/6-hourly trigger', 'setupTriggers')
    .addSeparator()
    .addItem('Refresh charts now (builds Charts + Public Charts; also runs automatically after each collection)', 'initializeCharts')
    .addItem('Export raw API snapshot (debug — dumps untouched JSON per repo/endpoint to "RawApiDebug")', 'exportRawApiSnapshot')
    .addItem('Repair legacy date cells + dedupe Traffic sheets (fixes timezone-drifted rows, e.g. after a manual data migration)', 'repairAndDedupeTrafficSheets')
    .addToUi();
}