Portfolio X-Ray

// Google Apps Script source for cashflowengine's per-user Live Pricing
// Sheet -- see README.md's "Live Pricing" section for the full setup.
//
// THIS FILE IS NOT PART OF THE NODE/VITE BUILD. It never runs in this
// repo, isn't imported by anything here, and doesn't ship in the deployed
// app. It's a version-controlled reference copy of the script every user
// pastes into their own Google Sheet's Extensions > Apps Script editor,
// so there's one canonical, reviewable source of truth for it instead of
// it living only as a chat transcript or a hand-me-down copy/paste.
//
// Architecture recap (see INTEGRATION_STRATEGY.md Section 1 for the full
// discussion of why this replaced the earlier api/quote.js Vercel proxy):
// each user makes their own copy of the template Sheet and pastes this
// script into it. doGet() is published as a Web App and serves these requests:
//   ?action=quotes        -> the whole Securities table as JSON, read LIVE
//                            from the Sheet (no publish-to-web lag)
//   ?tickers=A,B,C        -> batch-register several tickers in one call
//   ?ticker=A             -> legacy single-ticker registration (kept so an
//                            older app build still works against this script)
//   ?action=pullHoldings&tickers=A,B
//                         -> v4: fetch each fund's latest SEC N-PORT filing (via retire.cash);
//                            v4.3: also classifies it and pulls funds inside it
//                            into the Holdings/Snapshots tabs (new filings only)
//   ?action=holdings[&tickers=A,B][&compact=1]
//                         -> v4: read the stored holdings back as JSON, with
//                            v4.2 classifications (industry / fund / cash),
//                            funds held inside the requested funds, and why
//                            a ticker has no holdings (money market etc.)
//                            (compact=1: equities individually, everything
//                            else summed into buckets; each series once)
// Publishing the Sheet as CSV is now an optional FALLBACK read path -- the
// app prefers ?action=quotes when a Web App URL is connected.
// Everything runs on the user's own free Google account -- no shared API
// key, no per-app-owner cost, no shared rate limit across every user, and
// every cache below (CacheService) is private to this script project on
// the user's own account. Only bare ticker symbols are ever sent here.
//
// Ticker validation and the named-sheet lookup below close two gaps an
// earlier draft of this script had: doGet() used to splice the raw ticker
// parameter directly into formula strings with no character whitelist
// (a crafted ticker could have broken out of the quoted string and
// injected an arbitrary formula), and it wrote to getActiveSheet() --
// whatever tab happened to be open in the UI -- rather than a specific
// named tab.
//
// ── v3 freshness model (READ THIS before touching formulas) ─────────────
// Google Sheets recalculates CUSTOM functions (SMART_DIVIDEND, YAHOO_PRICE,
// etc.) only when their arguments change or the Sheet is opened -- the
// File > Settings > Calculation timed setting only drives NOW/TODAY/RAND,
// and GOOGLEFINANCE refreshes on its own (~20 min). So a mutual fund whose
// price comes from the YAHOO_PRICE fallback went stale until the user
// happened to open the Sheet (observed live with FSELX). v3 fixes this
// with a "refresh salt": every custom-function formula takes $L$1 as a
// dummy extra argument, and the REFRESH_QUOTES trigger below bumps $L$1 on
// a schedule -- changing an argument, which forces every salted cell to
// recalculate. Mutual fund NAVs post once daily after the close, so the
// default every-6-hours trigger is already more than the data needs;
// don't be tempted to run it every minute, that just burns Yahoo calls.

// ── Configuration ──────────────────────────────────────────────────
// Name of the tab that stores ticker data. Must match your Sheet's tab
// name exactly (case-sensitive). If the tab doesn't exist yet, doGet()
// creates it automatically with a header row on first use.
var SHEET_NAME = "Securities";

// v4.6: reported to the app (?action=quotes) so it can tell people when a
// newer script is available. Bump this with every release.
var SCRIPT_VERSION = "4.6";

// The refresh-salt cell (see the freshness model note above). Lives on the
// header row past the data columns so the CSV parser (which reads columns
// A-I and skips row 1) never sees it. K1 is a human-readable label; L1 is
// the timestamp the trigger bumps.
var SALT_LABEL_CELL = "K1";
var SALT_CELL = "L1";
// How the formulas reference the salt. $-anchored so a row copy/paste
// can't drift it.
var SALT_REF = "$L$1";

// How many tickers one batch registration call will accept. Generous --
// the app sends one batch per Refresh with only the symbols missing from
// the Sheet -- while still bounding a single Web App execution.
var MAX_BATCH_TICKERS = 50;

// Cache TTLs (seconds). CacheService is scoped to this script project on
// YOUR Google account -- there is no shared or third-party cache server.
var TTL_PRICE = 1800;      // 30 min -- prices move; mutual fund NAV is daily anyway
var TTL_MODULE = 14400;    // 4 h  -- dividend rate/yield change rarely
var TTL_FREQUENCY = 86400; // 24 h -- payout cadence changes ~never
var TTL_TYPE = 86400;      // 24 h -- security type changes ~never
var TTL_AUTH = 21600;      // 6 h  -- Yahoo cookie+crumb session

// Allowed ticker shapes: plain tickers (AAPL, FXAIX), share classes
// (BRK-B, BF.B), and index tickers (^GSPC). 1-10 core chars, optional
// leading "^", optional "." or "-" suffix of 1-5 chars. Deliberately
// excludes quotes, parens, "=", ";", spaces, etc. so a crafted ticker
// value can't break out of the quoted string inside a formula.
var TICKER_PATTERN = /^\^?[A-Z0-9]{1,10}([.\-][A-Z0-9]{1,5})?$/;

function isValidTicker_(ticker) {
  return TICKER_PATTERN.test(ticker);
}

// ── One-time setup helpers (run these from the Apps Script editor) ─────

/**
 * Run ONCE from the editor (Run > setupRefreshTrigger) after pasting this
 * script: installs the time-driven trigger that keeps custom-function
 * cells fresh without the Sheet being open. Safe to re-run -- it removes
 * any previous trigger for REFRESH_QUOTES before adding the new one.
 * Every 6 hours by default; NAVs post once daily after market close, so
 * this cadence already over-covers the data's real update rate.
 */
function setupRefreshTrigger() {
  removeRefreshTrigger();
  ScriptApp.newTrigger("REFRESH_QUOTES").timeBased().everyHours(6).create();
}

/** Removes the REFRESH_QUOTES trigger(s). Run from the editor if you want
 *  to stop scheduled refreshes entirely. */
function removeRefreshTrigger() {
  var triggers = ScriptApp.getProjectTriggers();
  for (var i = 0; i < triggers.length; i++) {
    if (triggers[i].getHandlerFunction() === "REFRESH_QUOTES") {
      ScriptApp.deleteTrigger(triggers[i]);
    }
  }
}

/**
 * The scheduled refresh (also safe to run by hand from the editor).
 * 1. Migrates any v2-era rows to salted formulas (one-time per row --
 *    only cells that currently CONTAIN a formula are rewritten, so a cell
 *    you've hand-edited to a static value is left alone).
 * 2. Bumps the salt cell, which forces every salted custom-function cell
 *    to recalculate. GOOGLEFINANCE cells keep their own ~20-min refresh
 *    cycle and are untouched.
 */
function REFRESH_QUOTES() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(SHEET_NAME);
  if (!sheet) return;
  ensureSaltedFormulas_(sheet);
  sheet.getRange(SALT_LABEL_CELL).setValue("Refreshed");
  sheet.getRange(SALT_CELL).setValue(new Date());
  SpreadsheetApp.flush();
}

// Canonical formula set for one ticker's row, columns B..I. The single
// source of truth used by registration AND the v2->v3 migration, so the
// two can never drift apart.
function rowFormulas_(ticker) {
  return [
    '=SECURITY_TYPE("' + ticker + '", ' + SALT_REF + ')',                                            // B: Type (live formula, self-heals from a transient lookup failure)
    '=IFERROR(GOOGLEFINANCE("' + ticker + '", "price"), YAHOO_PRICE("' + ticker + '", ' + SALT_REF + '))', // C: Price (Yahoo fallback -- GOOGLEFINANCE has known gaps for open-end mutual funds)
    '=IFERROR(GOOGLEFINANCE("' + ticker + '", "change"), "")',                                       // D: Change
    '=IFERROR(GOOGLEFINANCE("' + ticker + '", "changepct"), "")',                                    // E: ChangePct
    '=SMART_FREQUENCY("' + ticker + '", ' + SALT_REF + ')',                                          // F: Payout Freq (calculated from real payout history)
    '=SMART_DIVIDEND("' + ticker + '", "dollar", ' + SALT_REF + ')',                                 // G: Dividend ($)
    '=SMART_DIVIDEND("' + ticker + '", "yield", ' + SALT_REF + ')',                                  // H: Yield (%)
    '="https://finance.yahoo.com/quote/"&"' + ticker + '"',                                          // I: URL
  ];
}

// v2 rows have formulas without the salt argument, which never recalculate
// on a schedule. Rewrite each formula-bearing cell in columns B..H to the
// salted canonical version (I has no custom function; D/E are pure
// GOOGLEFINANCE and get rewritten harmlessly to the same text). Cells with
// NO formula (hand-entered static values) are deliberately skipped.
function ensureSaltedFormulas_(sheet) {
  var lastRow = sheet.getLastRow();
  if (lastRow < 2) return;
  var dataRange = sheet.getRange(2, 1, lastRow - 1, 9); // A..I
  var values = dataRange.getValues();
  var formulas = dataRange.getFormulas();
  for (var r = 0; r < formulas.length; r++) {
    var ticker = (values[r][0] || "").toString().toUpperCase().trim();
    if (!ticker || !isValidTicker_(ticker)) continue;
    var canonical = rowFormulas_(ticker);
    for (var c = 1; c <= 8; c++) { // sheet columns B..I
      var existing = formulas[r][c];
      if (!existing) continue;                      // static value -- leave the user's edit alone
      if (existing.indexOf(SALT_REF) !== -1) continue; // already salted
      var target = canonical[c - 1];
      if (existing === target) continue;            // already canonical (e.g. URL/GOOGLEFINANCE-only columns)
      sheet.getRange(r + 2, c + 1).setFormula(target);
    }
  }
}

// ── Web App entry point ────────────────────────────────────────────

function doGet(e) {
  var p = (e && e.parameter) || {};

  if (p.action === "quotes") return serveQuotes_();
  if (p.action === "holdings") return serveHoldings_(p.tickers, p.compact === "1"); // v4: read stored fund holdings
  // v4.6: the public demo Sheet only serves reads -- visitors can't add
  // tickers to it or make it pull filings.
  if (isDemo_()) return jsonOut_({ ok: false, version: 4, demo: true,
    error: "This is the read-only demo Sheet. Set up your own Sheet to add other tickers." });
  if (p.action === "pullHoldings") return servePullHoldings_(p.tickers);  // v4: fetch new N-PORT filings from SEC
  if (p.tickers) return registerBatch_(p.tickers);

  // Legacy single-ticker path -- same plain-text protocol v2 spoke, so an
  // older app build pointed at this script keeps working unchanged.
  var ticker = p.ticker;
  if (!ticker) return ContentService.createTextOutput("No ticker provided.");

  ticker = ticker.toUpperCase().replace("MUTF:", "").trim();
  if (!isValidTicker_(ticker)) {
    return ContentService.createTextOutput("Invalid ticker format: " + ticker);
  }

  var lock = LockService.getScriptLock();
  if (!lock.tryLock(30000)) {
    return ContentService.createTextOutput("Busy, try again: " + ticker);
  }
  try {
    var status = registerOne_(getOrCreateSheet_(), ticker);
    return ContentService.createTextOutput(status).setMimeType(ContentService.MimeType.TEXT);
  } finally {
    lock.releaseLock();
  }
}

// The whole Securities table, read live from the Sheet and returned as
// JSON -- the app's preferred read path. Published-CSV reads lag behind
// the live Sheet by Google's ~5-minute republish cadence; this endpoint
// doesn't, which is what lets a Refresh see a just-registered ticker's
// values as soon as its formulas resolve.
function serveQuotes_() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(SHEET_NAME);
  var quotes = {};
  if (sheet) {
    var lastRow = sheet.getLastRow();
    if (lastRow >= 2) {
      var values = sheet.getRange(2, 1, lastRow - 1, 9).getValues(); // A..I
      for (var i = 0; i < values.length; i++) {
        var symbol = (values[i][0] || "").toString().toUpperCase().trim();
        if (!symbol) continue;
        quotes[symbol] = {
          type: values[i][1],
          price: values[i][2],
          change: values[i][3],
          changePct: values[i][4],
          payoutFreq: values[i][5],
          dividend: values[i][6],
          yield: values[i][7],
          url: values[i][8],
        };
      }
    }
  }
  return ContentService
    .createTextOutput(JSON.stringify({ ok: true, version: 3, scriptVersion: SCRIPT_VERSION,
                                       demo: isDemo_(), quotes: quotes }))
    .setMimeType(ContentService.MimeType.JSON);
}

// Batch registration: one Web App call, one lock, one sheet read for the
// whole list -- replaces the app calling the single-ticker path once per
// symbol (each call is a fresh Apps Script cold start, so ten new symbols
// used to cost ten cold starts plus stagger delays). Returns per-ticker
// statuses as JSON so the app can tell "registered", "already there",
// and "rejected" apart instead of assuming every HTTP 200 succeeded.
function registerBatch_(tickersParam) {
  var results = {};
  var raw = tickersParam.split(",");
  if (raw.length > MAX_BATCH_TICKERS) {
    return ContentService
      .createTextOutput(JSON.stringify({ ok: false, version: 3, error: "Too many tickers in one call (max " + MAX_BATCH_TICKERS + ")" }))
      .setMimeType(ContentService.MimeType.JSON);
  }

  var lock = LockService.getScriptLock();
  if (!lock.tryLock(30000)) {
    return ContentService
      .createTextOutput(JSON.stringify({ ok: false, version: 3, error: "Busy, try again" }))
      .setMimeType(ContentService.MimeType.JSON);
  }
  try {
    var sheet = getOrCreateSheet_();
    for (var i = 0; i < raw.length; i++) {
      var ticker = raw[i].toUpperCase().replace("MUTF:", "").trim();
      if (!ticker) continue;
      if (!isValidTicker_(ticker)) {
        results[ticker] = "Invalid";
        continue;
      }
      results[ticker] = registerOne_(sheet, ticker);
    }
    return ContentService
      .createTextOutput(JSON.stringify({ ok: true, version: 3, results: results }))
      .setMimeType(ContentService.MimeType.JSON);
  } finally {
    lock.releaseLock();
  }
}

function getOrCreateSheet_() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(SHEET_NAME);
  if (!sheet) {
    // First run against a fresh copy of the template: create the tab with
    // headers instead of silently writing to whatever tab happens to be
    // open.
    sheet = ss.insertSheet(SHEET_NAME);
    sheet.appendRow(["Ticker", "Type", "Price", "Change", "ChangePct",
                      "PayoutFreq", "Dividend", "Yield", "URL"]);
    sheet.getRange(SALT_LABEL_CELL).setValue("Refreshed");
    sheet.getRange(SALT_CELL).setValue(new Date());
  }
  return sheet;
}

// Appends one ticker's formula row if it isn't present. Caller must hold
// the script lock. Returns "Success" or "Exists".
function registerOne_(sheet, ticker) {
  var data = sheet.getDataRange().getValues();
  for (var i = 0; i < data.length; i++) {
    if (data[i][0] && data[i][0].toString().toUpperCase() === ticker) {
      return "Exists";
    }
  }
  // We split the Dividend and Yield into two separate columns!
  sheet.appendRow([ticker].concat(rowFormulas_(ticker)));
  return "Success";
}

// ── Yahoo Finance lookups (cached) ─────────────────────────────────
//
// All caches below are CacheService.getScriptCache() -- storage private to
// this script project on the user's own Google account, NOT any shared or
// third-party server. Caching matters because Sheets recalculates BOTH
// SMART_DIVIDEND cells (dollar + yield) independently, and each used to
// re-fetch the same Yahoo module -- with the scheduled REFRESH_QUOTES
// bumping recalculation several times a day, uncached lookups would
// multiply Yahoo traffic for data (dividend rates, payout cadence) that
// barely changes week to week. Only SUCCESSFUL lookups are cached; error
// responses always retry live.

/**
 * Deduces dividend payout frequency by counting actual payouts over the last 12 months.
 * The trailing _salt argument is unused -- it exists so the REFRESH_QUOTES
 * trigger can force recalculation by changing it (see the freshness model
 * note at the top).
 *
 * @customfunction
 */
function SMART_FREQUENCY(ticker, _salt) {
  if (!ticker) return "N/A";
  var cleanTicker = ticker.toString().replace("MUTF:", "").trim().toUpperCase();

  var cache = CacheService.getScriptCache();
  var cached = cache.get("freq_" + cleanTicker);
  if (cached) return cached;

  // Look at the chart API for the last 1 year of dividend events (interval=1d prevents Yahoo from aggregating weekly dividends)
  var url = "https://query1.finance.yahoo.com/v8/finance/chart/" + encodeURIComponent(cleanTicker) + "?events=div&interval=1d&range=1y";
  var options = {
    "muteHttpExceptions": true,
    "headers": { "User-Agent": "Mozilla/5.0" }
  };

  try {
    var response = UrlFetchApp.fetch(url, options);
    if (response.getResponseCode() !== 200) return "N/A";

    var json = JSON.parse(response.getContentText());
    var label;
    if (!json.chart || !json.chart.result || !json.chart.result[0].events || !json.chart.result[0].events.dividends) {
      // If Yahoo explicitly has no dividend history, check if it's yielding anything
      var yld = YAHOO_FINANCE(cleanTicker, "summaryDetail", "yield") || YAHOO_FINANCE(cleanTicker, "summaryDetail", "dividendYield");
      label = (yld && typeof yld !== "object" && yld !== "N/A") ? "Unknown" : "None";
      cache.put("freq_" + cleanTicker, label, TTL_FREQUENCY);
      return label;
    }

    // Sort historical dividends chronologically
    var divs = json.chart.result[0].events.dividends;
    var keys = Object.keys(divs).sort(function(a, b) { return a - b; });
    var count = keys.length;
    if (count === 0) return "None";

    var dates = [];
    for (var i = 0; i < keys.length; i++) dates.push(new Date(keys[i] * 1000));
    var lastDate = dates[dates.length - 1];

    var monthNames = ["Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"];
    var dayNames = ["Sundays", "Mondays", "Tuesdays", "Wednesdays", "Thursdays", "Fridays", "Saturdays"];

    if (count >= 48 && count <= 54) {
      label = "Weekly (" + dayNames[lastDate.getDay()] + ")";
    } else if (count >= 11 && count <= 13) {
      var avgDay = 0;
      for (var d = 0; d < dates.length; d++) avgDay += dates[d].getDate();
      avgDay = Math.round(avgDay / dates.length);
      var timing = "Late-Month";
      if (avgDay <= 10) timing = "Early-Month";
      else if (avgDay <= 20) timing = "Mid-Month";
      label = "Monthly (" + timing + ")";
    } else if (count >= 3 && count <= 5) {
      var recent = dates.slice(Math.max(dates.length - 4, 0));
      recent.sort(function(a, b) { return a.getMonth() - b.getMonth(); });
      var qMonths = [];
      for (var q = 0; q < recent.length; q++) {
        var m = monthNames[recent[q].getMonth()];
        if (qMonths.indexOf(m) === -1) qMonths.push(m);
      }
      label = "Quarterly (" + qMonths.join("/") + ")";
    } else if (count === 2) {
      var m1 = monthNames[dates[0].getMonth()];
      var m2 = monthNames[dates[1].getMonth()];
      label = "Semi-Annually (" + m1 + "/" + m2 + ")";
    } else if (count === 1) {
      label = "Annually (" + monthNames[lastDate.getMonth()] + ")";
    } else {
      label = count + "x / Year";
    }

    cache.put("freq_" + cleanTicker, label, TTL_FREQUENCY);
    return label;

  } catch (e) {
    return "N/A";
  }
}

/**
 * Automatically hunts down the correct dividend or yield field in Yahoo Finance.
 * Derives missing values using Live Price. The trailing _salt argument is
 * unused -- see SMART_FREQUENCY.
 *
 * @customfunction
 */
function SMART_DIVIDEND(ticker, requestType, _salt) {
  if (!ticker) return "N/A";

  // 1. Fetch the raw rate (Dollars) and raw yield (Percentage). These go
  // through the cached YAHOO_FINANCE wrapper, so the dollar cell and the
  // yield cell -- which need the same summaryDetail fields -- share one
  // real fetch instead of each redoing it.
  var rate = YAHOO_FINANCE(ticker, "summaryDetail", "dividendRate");
  if (typeof rate === "object" || rate === "N/A" || rate === "") rate = null;
  var yld = YAHOO_FINANCE(ticker, "summaryDetail", "dividendYield");
  if (typeof yld === "object" || yld === "N/A" || yld === "") {
    yld = YAHOO_FINANCE(ticker, "summaryDetail", "yield");
  }
  if (typeof yld === "object" || yld === "N/A" || yld === "") {
    yld = YAHOO_FINANCE(ticker, "defaultKeyStatistics", "yield");
  }
  if (typeof yld === "object" || yld === "N/A" || yld === "") yld = null;
  // 2. Return the requested format, deriving it mathematically if it's missing!
  if (requestType === "dollar") {
    if (rate !== null) return rate; // We have the exact dollar amount
    if (yld !== null) {
      // Derive: Dollar = Yield * Price
      var price = YAHOO_PRICE(ticker);
      if (typeof price === "number") return yld * price;
    }
    return "N/A";
  }
  if (requestType === "yield") {
    if (yld !== null) return yld; // We have the exact percentage
    if (rate !== null) {
      // Derive: Yield = Dollar / Price
      var price2 = YAHOO_PRICE(ticker);
      if (typeof price2 === "number" && price2 > 0) return rate / price2;
    }
    return "N/A";
  }
  return "N/A";
}

/**
 * Detects whether a ticker is a Stock, ETF, Mutual Fund, Crypto, or Index.
 * The trailing _salt argument is unused -- see SMART_FREQUENCY.
 *
 * NOTE: the "Ticker not found" / "Error: ..." return strings are load-
 * bearing -- the app's getSheetSyncStatus (sheetPricing.js) classifies
 * per-symbol badges off exactly these strings. Don't reword them.
 */
function SECURITY_TYPE(ticker, _salt) {
  if (!ticker) return "";

  var cleanTicker = ticker.toString().replace("MUTF:", "").trim().toUpperCase();

  var cache = CacheService.getScriptCache();
  var cachedType = cache.get("type_" + cleanTicker);
  if (cachedType) return cachedType;
  var url = "https://query1.finance.yahoo.com/v8/finance/chart/" +
            encodeURIComponent(cleanTicker) +
            "?interval=1d&range=1d";
  var options = {
    "muteHttpExceptions": true,
    "headers": {
      "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36"
    }
  };
  try {
    var response = UrlFetchApp.fetch(url, options);
    if (response.getResponseCode() !== 200) return "Ticker not found";
    var json = JSON.parse(response.getContentText());
    if (!json.chart || !json.chart.result || json.chart.result.length === 0) return "Unknown";
    var rawType = json.chart.result[0].meta.instrumentType || "";
    var formattedType = "Unknown";
    switch (rawType.toUpperCase()) {
      case "EQUITY": formattedType = "Stock"; break;
      case "ETF": formattedType = "ETF"; break;
      case "MUTUALFUND": formattedType = "Mutual Fund"; break;
      case "INDEX": formattedType = "Index"; break;
      case "CRYPTOCURRENCY": formattedType = "Crypto"; break;
      case "CURRENCY": formattedType = "Currency / Forex"; break;
      case "FUTURE": formattedType = "Futures"; break;
      default: formattedType = rawType || "Unknown";
    }
    cache.put("type_" + cleanTicker, formattedType, TTL_TYPE);
    return formattedType;
  } catch (e) {
    return "Error: " + e.message;
  }
}

/**
 * Custom function to fetch Yahoo Finance statistics. Successful lookups
 * are cached (module data for 4 h) so repeated recalculations don't
 * re-hit Yahoo for data that barely changes; failures are never cached.
 */
function YAHOO_FINANCE(ticker, moduleName, dataPoint) {
  if (!ticker || !moduleName || !dataPoint) return "Missing parameters";

  var cleanTicker0 = ticker.toString().replace("MUTF:", "").trim().toUpperCase();
  var cache = CacheService.getScriptCache();
  var cacheKey = "yf_" + cleanTicker0 + "_" + moduleName + "_" + dataPoint;
  var cached = cache.get(cacheKey);
  if (cached !== null) {
    try { return JSON.parse(cached).v; } catch (ignored) { /* fall through to live fetch */ }
  }

  try {
    var auth = getYahooAuth_();
    if (!auth || !auth.crumb) return "Error: Yahoo auth failed";
    var cleanTicker = ticker.toString().replace("MUTF:", "").trim();
    var url = "https://query1.finance.yahoo.com/v10/finance/quoteSummary/" +
              encodeURIComponent(cleanTicker) +
              "?modules=" + encodeURIComponent(moduleName) +
              "&crumb=" + encodeURIComponent(auth.crumb);
    var options = {
      "method": "get",
      "muteHttpExceptions": true,
      "headers": {
        "User-Agent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/122.0.0.0 Safari/537.36",
        "Cookie": auth.cookie
      }
    };
    var response = UrlFetchApp.fetch(url, options);
    if (response.getResponseCode() !== 200) {
      CacheService.getScriptCache().remove("yahoo_auth");
      return "HTTP Error " + response.getResponseCode();
    }
    var json = JSON.parse(response.getContentText());
    if (!json.quoteSummary || !json.quoteSummary.result || json.quoteSummary.result.length === 0) {
      return "Ticker not found";
    }
    var moduleObj = json.quoteSummary.result[0][moduleName];
    if (!moduleObj || moduleObj[dataPoint] === undefined) {
      return "N/A";
    }
    var result = moduleObj[dataPoint];

    // Yahoo sometimes returns empty objects {} for missing data
    if (result && typeof result === "object") {
      if (Object.keys(result).length === 0) return "N/A";

      if (dataPoint.toLowerCase().includes("date") && result.raw) {
        result = result.fmt || new Date(result.raw * 1000).toISOString().split("T")[0];
      } else if (result.raw !== undefined) {
        result = result.raw;
      }
    }

    // Cache only real values -- numbers and non-error strings. "N/A" is a
    // real (negative) answer worth caching too, so a no-dividend stock
    // doesn't re-fetch its empty summaryDetail on every recalculation.
    if (typeof result === "number" || (typeof result === "string" && result.indexOf("Error") === -1 && result.indexOf("HTTP") === -1)) {
      cache.put(cacheKey, JSON.stringify({ v: result }), TTL_MODULE);
    }
    return result;
  } catch (err) {
    return "Error: " + err.message;
  }
}

/**
 * Price fallback for tickers GOOGLEFINANCE can't resolve (e.g. many
 * open-end mutual funds). v3: reads Yahoo's v8 chart endpoint, which
 * needs no cookie/crumb session at all (same endpoint SECURITY_TYPE
 * already uses) -- one less fragile moving part than the old
 * quoteSummary+crumb flow -- and caches the price for 30 minutes.
 * Returns a plain number so IFERROR(...) in the Price column formula can
 * use it as a drop-in second argument; returns "" (blank cell, not an
 * error string) for anything Yahoo couldn't resolve, so a bad lookup
 * never gets treated as a price. The trailing _salt argument is unused --
 * see SMART_FREQUENCY.
 *
 * @customfunction
 */
function YAHOO_PRICE(ticker, _salt) {
  if (!ticker) return "";
  var cleanTicker = ticker.toString().replace("MUTF:", "").trim().toUpperCase();

  var cache = CacheService.getScriptCache();
  var cached = cache.get("price_" + cleanTicker);
  if (cached !== null) {
    var num = parseFloat(cached);
    if (!isNaN(num)) return num;
  }

  var url = "https://query1.finance.yahoo.com/v8/finance/chart/" +
            encodeURIComponent(cleanTicker) + "?interval=1d&range=1d";
  var options = {
    "muteHttpExceptions": true,
    "headers": { "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36" }
  };
  try {
    var response = UrlFetchApp.fetch(url, options);
    if (response.getResponseCode() !== 200) return "";
    var json = JSON.parse(response.getContentText());
    var meta = json.chart && json.chart.result && json.chart.result[0] && json.chart.result[0].meta;
    var price = meta && meta.regularMarketPrice;
    if (typeof price !== "number") return "";
    cache.put("price_" + cleanTicker, String(price), TTL_PRICE);
    return price;
  } catch (e) {
    return "";
  }
}

/**
 * Session auth token helper for Yahoo Finance (still used by
 * YAHOO_FINANCE's quoteSummary lookups -- dividend rate/yield).
 */
function getYahooAuth_() {
  var cache = CacheService.getScriptCache();
  var cachedAuth = cache.get("yahoo_auth");
  if (cachedAuth) return JSON.parse(cachedAuth);
  var userAgent = "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/122.0.0.0 Safari/537.36";
  var init = UrlFetchApp.fetch("https://fc.yahoo.com", { "muteHttpExceptions": true, "headers": { "User-Agent": userAgent } });
  var rawCookie = init.getAllHeaders()["Set-Cookie"] || init.getAllHeaders()["set-cookie"];
  var cookieStr = Array.isArray(rawCookie) ? rawCookie.map(function(c) { return c.split(";")[0]; }).join("; ") : (rawCookie ? rawCookie.split(";")[0] : "");
  var crumbResp = UrlFetchApp.fetch("https://query2.finance.yahoo.com/v1/test/getcrumb", {
    "muteHttpExceptions": true,
    "headers": { "User-Agent": userAgent, "Cookie": cookieStr }
  });
  var crumb = crumbResp.getContentText();
  if (!crumb || crumb.length > 50 || crumb.indexOf("<") !== -1) return null;
  var authObj = { cookie: cookieStr, crumb: crumb };
  cache.put("yahoo_auth", JSON.stringify(authObj), TTL_AUTH);
  return authObj;
}

// ── v4.4: Fund holdings from SEC N-PORT (via retire.cash) ──────────
//
// Pulls each fund's full portfolio from its latest public N-PORT filing on
// SEC EDGAR and stores it in two tabs:
//
//   Snapshots -- one row per (ticker, filing): which SEC filing we loaded
//                for that ticker, its as-of date, holding count and total
//                weight. This is the index the app reads first.
//   Holdings  -- one row per security per filing, in long format. A new
//                filing APPENDS rows (history is kept); an already-loaded
//                filing is never downloaded or written twice.
//
// The Securities tab is not touched. Holdings rows are keyed by SEC series
// ID (not ticker) because several tickers can be share classes of one
// portfolio -- e.g. VOO and VFIAX are both series S000002839 -- so the
// holdings are stored once and every class's Snapshots row points at them.
//
// WHY THIS GOES THROUGH retire.cash: Google appends "Google-Apps-Script"
// to the User-Agent of every UrlFetchApp request, and SEC rejects any
// request carrying it (HTTP 403) -- this script cannot reach SEC directly.
// Two small stateless functions on the retire.cash site (api/nport.js,
// api/nport-filing.js in the repo) fetch from SEC and return parsed JSON:
//   POST /api/nport          {"tickers":[...]} -> latest filing number per
//                            ticker. Tickers go in the POST body, not the
//                            URL, so they stay out of request logs.
//   GET  /api/nport-filing?cik=&accession=&series=
//                            -> that filing's holdings. Only called for a
//                            filing this Sheet doesn't have yet; responses
//                            are edge-cached for a year (filings never
//                            change), so SEC is hit about once per filing.
// Nothing is stored on the server, and it never sees share counts, this
// Sheet, or who you are -- only public fund tickers/filing numbers. The
// user's own positions stay in the browser; the app does the look-through
// math client-side.
//
// What N-PORT does NOT cover: stocks, crypto, money market funds (they
// file N-MFP instead), and funds not registered under the Investment
// Company Act -- SPY and DIA (unit investment trusts), GLD, IBIT and other
// grantor trusts. Those come back as "No SEC fund match" / "No N-PORT
// filings" and are simply skipped.

var HOLDINGS_SHEET_NAME = "Holdings";
var SNAPSHOTS_SHEET_NAME = "Snapshots";
var HOLDINGS_HEADERS = ["SeriesId", "AsOf", "Key", "Name", "CUSIP", "ISIN",
                        "Ticker", "Weight", "AssetCat", "Derivative",
                        "Payoff", "Accession", "Country"];
var SNAPSHOTS_HEADERS = ["Ticker", "SeriesId", "SeriesName", "AsOf",
                         "Filed", "Accession", "HoldingCount",
                         "TotalWeight", "FetchedAt"];

// Where the SEC relay functions live (no trailing slash).
var HOLDINGS_API_BASE = "https://retire.cash/api";

var MAX_HOLDINGS_TICKERS = 100;  // per ?action=pullHoldings call
var CHECK_CHUNK = 15;            // tickers per /api/nport request (keeps each under the function time limit)
var HOLDINGS_TIME_BUDGET_MS = 4.5 * 60 * 1000; // leave headroom under Apps Script's 6-min cap

// Retention: keep only the newest N filings per fund (series). N-PORT is
// quarterly, so 4 is about a year of history. Older Holdings and Snapshots
// rows are deleted right after a new filing is added. Without a cap, a
// ~70-ticker portfolio adds ~54k Holdings rows per quarter (bond funds
// like BND alone are 17k), which would reach Google Sheets' 10M-cell limit
// in about 3.5 years and slow down every ?action=holdings read.
var KEEP_FILINGS = 4;

// Funds whose share-class ticker SEC's data doesn't list, mapped by hand to
// their SEC series ID (found in SEC's Investment Company Series and Class
// dataset by fund name). Add a line here if REFRESH_HOLDINGS reports
// "No SEC fund match" for a fund you know files N-PORT.
var FUND_SERIES_OVERRIDES = {
  ODVYX: "S000064706", // Invesco Developing Markets Fund, Class Y
  BHYIX: "S000062360"  // BlackRock High Yield Portfolio, Institutional
};

// Tickers with nothing to look through, and why (money market funds, stocks,
// tickers SEC doesn't know). The app uses this to show money market funds
// as cash instead of "not looked through".
var COVERAGE_SHEET_NAME = "Coverage";
var COVERAGE_HEADERS = ["Ticker", "Status", "CheckedAt"];

// v4.2 classification: what each security a fund holds actually is --
// stock (with its SEC SIC industry), a fund held by the fund (so it can be
// looked through too), cash, or unknown. Filled by REFRESH_HOLDINGS via
// retire.cash/api/classify, one row per security, looked up once.
var CLASSIFY_SHEET_NAME = "Classifications";
var CLASSIFY_HEADERS = ["Key", "Kind", "Ticker", "CIK", "SIC", "Industry", "SeriesId", "CheckedAt"];
// Only securities at least this big (% of some fund) are classified; the
// long tail of tiny positions is shown as "Unclassified (small)". 0.05%
// covers ~1,700 securities for a 70-ticker portfolio.
var CLASSIFY_MIN_WEIGHT = 0.05;
var CLASSIFY_CHUNK = 100;       // securities per /api/classify call
var FUND_OF_FUNDS_ROUNDS = 2;   // how many layers of funds-inside-funds to pull

/**
 * Run ONCE from the editor (Run > setupHoldingsTrigger): checks every fund
 * in the Securities tab for a newer N-PORT filing on the 1st of each month.
 * Only funds with a NEW filing are downloaded. Safe to re-run.
 */
function setupHoldingsTrigger() {
  removeHoldingsTrigger();
  ScriptApp.newTrigger("REFRESH_HOLDINGS").timeBased().onMonthDay(1).atHour(5).create();
}

/** Removes the REFRESH_HOLDINGS trigger(s). */
function removeHoldingsTrigger() {
  var triggers = ScriptApp.getProjectTriggers();
  for (var i = 0; i < triggers.length; i++) {
    if (triggers[i].getHandlerFunction() === "REFRESH_HOLDINGS") {
      ScriptApp.deleteTrigger(triggers[i]);
    }
  }
}

/**
 * The monthly refresh (also safe to run by hand from the editor):
 *   1. Pull new N-PORT filings for every ticker in Securities (plus any
 *      already in Snapshots).
 *   2. Classify securities the funds hold that haven't been classified yet
 *      (stock + SEC industry, fund, cash).
 *   3. Pull holdings for funds found INSIDE funds (target-date funds, YMAX),
 *      then classify their holdings too -- up to FUND_OF_FUNDS_ROUNDS deep.
 * One shared time budget. If it runs out, the work left is picked up by a
 * follow-up run scheduled 5 minutes later (CONTINUE_HOLDINGS), as long as
 * the run made progress. Returns and logs per-ticker statuses.
 */
function REFRESH_HOLDINGS() {
  var started = Date.now();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var tickers = {};
  [SHEET_NAME, SNAPSHOTS_SHEET_NAME].forEach(function (name) {
    var sh = ss.getSheetByName(name);
    if (!sh || sh.getLastRow() < 2) return;
    var col = sh.getRange(2, 1, sh.getLastRow() - 1, 1).getValues();
    for (var i = 0; i < col.length; i++) {
      var t = (col[i][0] || "").toString().toUpperCase().trim();
      if (t && isValidTicker_(t)) tickers[t] = true;
    }
  });
  var lock = LockService.getScriptLock();
  if (!lock.tryLock(30000)) return { error: "Busy" };
  try {
    var log = runHoldingsPipeline_(Object.keys(tickers), started);
    Logger.log(JSON.stringify(log));
    return log;
  } finally {
    lock.releaseLock();
  }
}

// The shared pipeline behind REFRESH_HOLDINGS and ?action=pullHoldings:
// pull filings for `tickers` -> classify new securities -> pull funds found
// inside funds -> classify their holdings. Stops at the time budget implied
// by `started`; if work is left and progress was made, schedules a
// CONTINUE_HOLDINGS run. Caller must hold the script lock.
function runHoldingsPipeline_(tickers, started) {
  var log = { pulled: {}, classified: 0, classifyPending: 0, underlying: {} };
  // Filing downloads get at most ~60% of the run, so classification always
  // gets time too when SEC is slow (unfinished funds come back "Deferred"
  // and the follow-up run picks them up).
  log.pulled = pullHoldings_(tickers, started - HOLDINGS_TIME_BUDGET_MS * 0.4);
  for (var round = 0; round < FUND_OF_FUNDS_ROUNDS; round++) {
    var c = classifyHoldings_(started);
    log.classified += c.classified;
    log.classifyPending = c.pending;
    var inner = underlyingFundsToPull_();
    if (!Object.keys(inner).length) break;
    var r = pullHoldings_(Object.keys(inner), started, inner);
    for (var k in r) log.underlying[k] = r[k];
  }
  if (Object.keys(log.underlying).length) {
    var c2 = classifyHoldings_(started);  // holdings of the funds just pulled
    log.classified += c2.classified;
    log.classifyPending = c2.pending;
  }
  var deferred = log.classifyPending > 0, progressed = log.classified > 0;
  [log.pulled, log.underlying].forEach(function (m) {
    for (var t in m) {
      if (m[t] === "Deferred") deferred = true;
      if (m[t] === "Added") progressed = true;
    }
  });
  if (deferred && progressed) scheduleContinuation_();
  log.continuing = deferred && progressed;
  return log;
}

/** One-off follow-up run for work a REFRESH_HOLDINGS run couldn't finish. */
function CONTINUE_HOLDINGS() {
  removeTriggersFor_("CONTINUE_HOLDINGS");
  return REFRESH_HOLDINGS();
}

function scheduleContinuation_() {
  removeTriggersFor_("CONTINUE_HOLDINGS");
  ScriptApp.newTrigger("CONTINUE_HOLDINGS").timeBased().after(5 * 60 * 1000).create();
}

function removeTriggersFor_(handler) {
  var triggers = ScriptApp.getProjectTriggers();
  for (var i = 0; i < triggers.length; i++) {
    if (triggers[i].getHandlerFunction() === handler) ScriptApp.deleteTrigger(triggers[i]);
  }
}

// Upserts non-fund statuses (money market, no SEC match, ...) into the
// Coverage tab; removes a ticker's row once it has holdings.
function recordCoverage_(results) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = getOrCreateTab_(ss, COVERAGE_SHEET_NAME, COVERAGE_HEADERS);
  var rows = sh.getLastRow() >= 2 ? sh.getRange(2, 1, sh.getLastRow() - 1, COVERAGE_HEADERS.length).getValues() : [];
  var byTicker = {};
  for (var i = 0; i < rows.length; i++) byTicker[rows[i][0]] = rows[i];
  var changed = false;
  for (var t in results) {
    var st = results[t];
    if (st === "Money market" || st === "No SEC fund match" || st === "No N-PORT filings") {
      byTicker[t] = [t, st, new Date()]; changed = true;
    } else if ((st === "Added" || st === "Current") && byTicker[t]) {
      delete byTicker[t]; changed = true;
    }
  }
  if (!changed) return;
  var out = Object.keys(byTicker).sort().map(function (k) { return byTicker[k]; });
  if (sh.getLastRow() >= 2) sh.getRange(2, 1, sh.getLastRow() - 1, COVERAGE_HEADERS.length).clearContent();
  if (out.length) sh.getRange(2, 1, out.length, COVERAGE_HEADERS.length).setValues(out);
}

// Classifies securities held by the funds (weight >= CLASSIFY_MIN_WEIGHT,
// plus anything named like a fund) and directly held stocks from
// Securities, skipping ones already in the Classifications tab. Biggest
// weights first, so the ones that matter are done first if time runs out.
// Returns { classified, pending }.
function classifyHoldings_(started) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var cls = getOrCreateTab_(ss, CLASSIFY_SHEET_NAME, CLASSIFY_HEADERS);
  var done = {};
  if (cls.getLastRow() >= 2) {
    var k = cls.getRange(2, 1, cls.getLastRow() - 1, 1).getValues();
    for (var i = 0; i < k.length; i++) done[k[i][0]] = true;
  }
  var want = {}; // key -> item
  var hold = ss.getSheetByName(HOLDINGS_SHEET_NAME);
  if (hold && hold.getLastRow() >= 2) {
    var h = hold.getRange(2, 1, hold.getLastRow() - 1, HOLDINGS_HEADERS.length).getValues();
    for (var r = 0; r < h.length; r++) {
      var key = fixKey_(h[r][2], h[r][5], h[r][3]);
      if (!key || done[key] || holdingBucket_(h[r][8], h[r][9])) continue;
      var w = Number(h[r][7]) || 0, name = String(h[r][3] || "");
      if (w < CLASSIFY_MIN_WEIGHT && !/\b(ETF|FUND|TRUST)\b/i.test(name)) continue;
      var it = want[key];
      if (!it) want[key] = it = { id: key, cusip: h[r][4], isin: h[r][5], ticker: h[r][6], name: name, w: 0 };
      if (w > it.w) it.w = w;
      if (!it.ticker && h[r][6]) it.ticker = h[r][6];
    }
  }
  var sec = ss.getSheetByName(SHEET_NAME);
  if (sec && sec.getLastRow() >= 2) {
    var srows = sec.getRange(2, 1, sec.getLastRow() - 1, 2).getValues();
    for (var j = 0; j < srows.length; j++) {
      var t = String(srows[j][0] || "").toUpperCase().trim();
      if (t && srows[j][1] === "Stock" && !done["TICKER:" + t]) {
        want["TICKER:" + t] = { id: "TICKER:" + t, ticker: t, name: "", w: 100 };
      }
    }
  }
  var items = Object.keys(want).map(function (x) { return want[x]; })
    .sort(function (a, b) { return b.w - a.w; });
  var classified = 0;
  for (var n = 0; n < items.length; n += CLASSIFY_CHUNK) {
    if (Date.now() - started > HOLDINGS_TIME_BUDGET_MS - 60000) break; // each call can take ~45 s
    var batch = items.slice(n, n + CLASSIFY_CHUNK).map(function (it) {
      return { id: it.id, cusip: it.cusip || "", isin: it.isin || "", ticker: it.ticker || "", name: it.name };
    });
    var resp;
    try { resp = apiFetch_("/classify", { items: batch }); } catch (e) { Logger.log("classify: " + e.message); break; }
    var out = [], now = new Date();
    for (var id in resp.results) {
      var v = resp.results[id];
      out.push([id, v.k || "unknown", v.t || "", v.c || "", v.s || "", v.d || "", v.sr || "", now]);
    }
    if (out.length) cls.getRange(cls.getLastRow() + 1, 1, out.length, CLASSIFY_HEADERS.length).setValues(out);
    classified += out.length;
    if (resp.deferred && resp.deferred.length === batch.length) break; // rate-limited: stop for now
  }
  var total = items.length;
  return { classified: classified, pending: Math.max(0, total - classified) };
}

// Funds found inside funds that have a known SEC series but no holdings
// loaded yet -> { ticker: seriesId } for pullHoldings_.
function underlyingFundsToPull_() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var cls = ss.getSheetByName(CLASSIFY_SHEET_NAME);
  if (!cls || cls.getLastRow() < 2) return {};
  var have = {};
  var snaps = ss.getSheetByName(SNAPSHOTS_SHEET_NAME);
  if (snaps && snaps.getLastRow() >= 2) {
    var s = snaps.getRange(2, 1, snaps.getLastRow() - 1, 2).getValues();
    for (var i = 0; i < s.length; i++) { have[s[i][0]] = true; have[s[i][1]] = true; }
  }
  var cov = ss.getSheetByName(COVERAGE_SHEET_NAME);
  if (cov && cov.getLastRow() >= 2) {
    var c = cov.getRange(2, 1, cov.getLastRow() - 1, 1).getValues();
    for (var j = 0; j < c.length; j++) have[c[j][0]] = true;
  }
  var out = {};
  var rows = cls.getRange(2, 1, cls.getLastRow() - 1, CLASSIFY_HEADERS.length).getValues();
  for (var r = 0; r < rows.length; r++) {
    var kind = rows[r][1], t = String(rows[r][2] || "").toUpperCase(), sid = rows[r][6];
    if (kind !== "fund" || !t || !sid || !isValidTicker_(t)) continue;
    if (have[t] || have[sid]) continue;  // already loaded (maybe under another share class)
    out[t] = sid;
  }
  return out;
}

// ?action=pullHoldings&tickers=A,B -- load holdings right away for funds the
// app just added: pull their filings, classify their securities, pull any
// funds inside them. Capped at WEB_PULL_BUDGET_MS so the page isn't left
// waiting; anything left over is finished by a CONTINUE_HOLDINGS run.
var WEB_PULL_BUDGET_MS = 150 * 1000;
function servePullHoldings_(tickersParam) {
  var list = parseTickerList_(tickersParam);
  if (list.error) return jsonOut_({ ok: false, version: 4, error: list.error });
  var lock = LockService.getScriptLock();
  if (!lock.tryLock(30000)) return jsonOut_({ ok: false, version: 4, error: "Busy, try again" });
  try {
    // Shift the start so the pipeline's usual time checks give it only
    // WEB_PULL_BUDGET_MS instead of the full scheduled-run budget.
    var started = Date.now() - (HOLDINGS_TIME_BUDGET_MS - WEB_PULL_BUDGET_MS);
    var log = runHoldingsPipeline_(list.valid, started);
    for (var k in list.invalid) log.pulled[k] = "Invalid";
    return jsonOut_({ ok: true, version: 4, results: log.pulled, classified: log.classified,
                      pending: log.classifyPending, underlying: log.underlying, continuing: log.continuing });
  } finally {
    lock.releaseLock();
  }
}

// ?action=holdings[&tickers=A,B][&compact=1] -- read the latest stored
// snapshot for each ticker (all tickers when omitted). Read-only; never
// calls out.
// Full response: { ok, version, funds: { TICKER: { seriesId, seriesName,
//   asOf, filed, accession, holdings: [[key, name, ticker, weight,
//   assetCat, derivative, payoff, isin, country], ...] } }, missing: { TICKER: "No snapshot" },
//   classes: { KEY: [kind, ticker, SIC, industry, seriesId] },
//   coverage: { TICKER: "Money market" | "No SEC fund match" | ... } }
// funds[T].underlying = true marks a fund that's only there because one of
// the requested funds holds it.
// compact=1 (what the app uses): funds[T] has no holdings array; instead
//   series: { SERIESID: [[key, name, ticker, weight, isin, country], ...] } lists each
//   series once (VTI and VTSAX share one), with only stocks/preferreds/fund
//   shares as individual rows and everything else summed per fund into
//   "BUCKET:<group>" rows (bonds, cash, derivatives, notes, other). The
//   ISIN (when the key isn't already it) lets the app link one security
//   that different filers identify differently -- e.g. Fidelity lists Linde
//   by ISIN only, Vanguard by CUSIP G54950103 plus that ISIN. BND's
//   17k bonds become a handful of rows, which keeps the response small.
function serveHoldings_(tickersParam, compact) {
  var wanted = null;
  if (tickersParam) {
    var list = parseTickerList_(tickersParam);
    if (list.error) return jsonOut_({ ok: false, version: 4, error: list.error });
    wanted = list.valid;
  }
  var out = readHoldings_(wanted);
  if (compact) out = compactHoldings_(out);
  return jsonOut_(out);
}

// Bucket for a holding that isn't looked through individually, or "" for
// one that is (common/preferred stock and fund shares). The app applies
// the same rules to full responses, so both paths agree.
var HOLDING_BUCKETS = {
  DERIV: "Options & derivatives",
  CASH: "Cash & short-term",
  DEBT: "Bonds & other debt",
  NOTES: "Structured notes",
  OTHER: "Other assets"
};
function holdingBucket_(assetCat, derivative) {
  var c = (assetCat || "").toString().toUpperCase();
  if (derivative === "Y" || derivative === true || /^D(E|FE|IR|CO|CR|O|C)$/.test(c)) return "DERIV";
  if (c === "EC" || c === "EP" || c === "RF") return "";
  if (c === "STIV") return "CASH";
  if (c === "SN") return "NOTES";
  if (c === "DBT" || c === "LON" || c.indexOf("ABS") === 0) return "DEBT";
  return "OTHER";
}

function compactHoldings_(full) {
  var series = {};
  for (var t in full.funds) {
    var f = full.funds[t];
    if (!series[f.seriesId]) {
      var rows = [], buckets = {};
      for (var i = 0; i < f.holdings.length; i++) {
        var h = f.holdings[i]; // [key, name, ticker, weight, assetCat, derivative, payoff, isin]
        var b = holdingBucket_(h[4], h[5]);
        if (b) buckets[b] = (buckets[b] || 0) + (Number(h[3]) || 0);
        else rows.push([h[0], h[1], h[2], Number(h[3]) || 0,
                        (h[7] && h[0] !== "ISIN:" + h[7]) ? h[7] : "", h[8] || ""]);
      }
      for (var k in buckets) rows.push(["BUCKET:" + k, HOLDING_BUCKETS[k], "", Math.round(buckets[k] * 10000) / 10000]);
      series[f.seriesId] = rows;
    }
    delete f.holdings;
  }
  full.series = series;
  full.compact = true;
  return full;
}

function readHoldings_(wanted) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var snaps = ss.getSheetByName(SNAPSHOTS_SHEET_NAME);
  var hold = ss.getSheetByName(HOLDINGS_SHEET_NAME);
  var latest = {}; // ticker -> snapshot row (latest AsOf)
  if (snaps && snaps.getLastRow() >= 2) {
    var rows = snaps.getRange(2, 1, snaps.getLastRow() - 1, SNAPSHOTS_HEADERS.length).getValues();
    for (var i = 0; i < rows.length; i++) {
      var t = (rows[i][0] || "").toString().toUpperCase();
      if (!t) continue;
      var asOf = dateStr_(rows[i][3]);
      if (!latest[t] || asOf > latest[t].asOf) {
        latest[t] = { seriesId: rows[i][1], seriesName: rows[i][2], asOf: asOf,
                      filed: dateStr_(rows[i][4]), accession: rows[i][5], holdings: [] };
      }
    }
  }
  var tickers = wanted || Object.keys(latest);
  var funds = {}, missing = {}, byAccession = {};
  for (var j = 0; j < tickers.length; j++) {
    var tk = tickers[j];
    if (!latest[tk]) { missing[tk] = "No snapshot"; continue; }
    funds[tk] = latest[tk];
    (byAccession[latest[tk].accession] = byAccession[latest[tk].accession] || []).push(tk);
  }

  // v4.2: classifications (stock industry / fund / cash) for every
  // classified security, and the holdings of funds that sit inside the
  // requested funds (target-date funds, YMAX), so the app can look through
  // them. Underlying funds are added under their own ticker with
  // underlying: true; the app never treats them as a position of yours.
  var classes = {};
  var cls = ss.getSheetByName(CLASSIFY_SHEET_NAME);
  if (cls && cls.getLastRow() >= 2) {
    var crows = cls.getRange(2, 1, cls.getLastRow() - 1, CLASSIFY_HEADERS.length).getValues();
    for (var c = 0; c < crows.length; c++) {
      // [kind, ticker, SIC, industry, seriesId]
      classes[crows[c][0]] = [crows[c][1], crows[c][2], String(crows[c][4] || ""), crows[c][5], crows[c][6]];
    }
  }
  var latestBySeries = {};
  for (var lt in latest) {
    var ls = latest[lt];
    if (!latestBySeries[ls.seriesId] || ls.asOf > latestBySeries[ls.seriesId].snap.asOf) {
      latestBySeries[ls.seriesId] = { ticker: lt, snap: ls };
    }
  }
  var haveSeries = {};
  for (var ft in funds) haveSeries[funds[ft].seriesId] = true;
  for (var key in classes) {
    var cv = classes[key];
    if (cv[0] !== "fund" || !cv[4] || haveSeries[cv[4]] || !latestBySeries[cv[4]]) continue;
    var u = latestBySeries[cv[4]];
    var copy = { seriesId: u.snap.seriesId, seriesName: u.snap.seriesName, asOf: u.snap.asOf,
                 filed: u.snap.filed, accession: u.snap.accession, holdings: [], underlying: true };
    var ut = funds[u.ticker] ? u.ticker + "@" + u.snap.seriesId : u.ticker;
    funds[ut] = copy;
    haveSeries[cv[4]] = true;
    (byAccession[copy.accession] = byAccession[copy.accession] || []).push(ut);
  }

  // Why a requested ticker has no holdings (money market, stock, ...).
  var coverage = {};
  var cov = ss.getSheetByName(COVERAGE_SHEET_NAME);
  if (cov && cov.getLastRow() >= 2) {
    var cvr = cov.getRange(2, 1, cov.getLastRow() - 1, 2).getValues();
    for (var q = 0; q < cvr.length; q++) if (missing[cvr[q][0]]) coverage[cvr[q][0]] = cvr[q][1];
  }
  if (hold && hold.getLastRow() >= 2 && Object.keys(byAccession).length) {
    var h = hold.getRange(2, 1, hold.getLastRow() - 1, HOLDINGS_HEADERS.length).getValues();
    for (var r = 0; r < h.length; r++) {
      var owners = byAccession[h[r][11]];
      if (!owners) continue;
      var rec = [fixKey_(h[r][2], h[r][5], h[r][3]), h[r][3], h[r][6], h[r][7], h[r][8], h[r][9], h[r][10], h[r][5], h[r][12] || ""];
      for (var o = 0; o < owners.length; o++) funds[owners[o]].holdings.push(rec);
    }
  }
  return { ok: true, version: 4, funds: funds, missing: missing, classes: classes, coverage: coverage };
}

// Core pipeline. Caller must hold the script lock. Returns
// { TICKER: status } where status is one of: "Added", "Current",
// "Money market", "No SEC fund match", "No N-PORT filings", "Deferred"
// (time budget hit -- run again), or "Error: ...". `started` shares one
// time budget across phases; `seriesHints` maps ticker -> SEC series for
// funds found inside other funds.
function pullHoldings_(tickers, started, seriesHints) {
  started = started || Date.now();
  var results = {};
  if (!tickers.length) return results;

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var snapSheet = getOrCreateTab_(ss, SNAPSHOTS_SHEET_NAME, SNAPSHOTS_HEADERS);
  var holdSheet = getOrCreateTab_(ss, HOLDINGS_SHEET_NAME, HOLDINGS_HEADERS);

  // What's already stored: ticker+accession pairs, and accessions whose
  // holdings rows exist (shared across share classes of one series).
  var haveTickerAcc = {}, haveAcc = {};
  if (snapSheet.getLastRow() >= 2) {
    var s = snapSheet.getRange(2, 1, snapSheet.getLastRow() - 1, SNAPSHOTS_HEADERS.length).getValues();
    for (var i = 0; i < s.length; i++) {
      haveTickerAcc[(s[i][0] + "|" + s[i][5]).toUpperCase()] = true;
      haveAcc[s[i][5]] = { seriesName: s[i][2], asOf: s[i][3], count: s[i][6], total: s[i][7] };
    }
  }

  // 1) Ask retire.cash for the latest filing number of every ticker, in
  //    chunks. Anything the server couldn't finish comes back "Deferred"
  //    and is re-queued while time remains.
  var latest = {};
  var queue = tickers.slice();
  while (queue.length) {
    if (Date.now() - started > HOLDINGS_TIME_BUDGET_MS) {
      for (var q = 0; q < queue.length; q++) results[queue[q]] = "Deferred";
      break;
    }
    var chunk = queue.splice(0, CHECK_CHUNK);
    var resp;
    try {
      resp = apiFetch_("/nport", { tickers: chunk.map(function (t) {
        var sid = (seriesHints && seriesHints[t]) || FUND_SERIES_OVERRIDES[t];
        return sid ? t + "=" + sid : t;
      }) });
    } catch (e) {
      for (var c = 0; c < chunk.length; c++) results[chunk[c]] = "Error: " + e.message;
      continue;
    }
    var deferred = [];
    for (var k = 0; k < chunk.length; k++) {
      var r = resp.results && resp.results[chunk[k]];
      if (!r) { results[chunk[k]] = "Error: no answer"; continue; }
      if (r.status === "ok") latest[chunk[k]] = r;
      else if (r.status === "Deferred") deferred.push(chunk[k]);
      else results[chunk[k]] = r.status;
    }
    if (deferred.length === chunk.length) {           // server made no progress -- don't spin
      for (var d = 0; d < deferred.length; d++) results[deferred[d]] = "Deferred";
    } else {
      queue = queue.concat(deferred);
    }
  }

  // 2) Download only filings this Sheet doesn't have yet.
  var needsCountry = null; // accession -> {start, count} of Holdings rows lacking a country
  for (var t in latest) {
    var f = latest[t];
    if (haveTickerAcc[(t + "|" + f.accession).toUpperCase()]) {
      results[t] = "Current";
      // v4.4: filings stored before countries were kept get them filled in once.
      if (needsCountry === null) needsCountry = accessionsMissingCountry_(holdSheet);
      if (needsCountry[f.accession] && Date.now() - started < HOLDINGS_TIME_BUDGET_MS) {
        try { backfillCountry_(holdSheet, f, needsCountry[f.accession]); } catch (e3) { Logger.log("country backfill " + t + ": " + e3.message); }
        delete needsCountry[f.accession];
      }
      continue;
    }
    if (Date.now() - started > HOLDINGS_TIME_BUDGET_MS) { results[t] = "Deferred"; continue; }
    try {
      var info = haveAcc[f.accession];
      if (!info) {
        var filing = fetchFilingApi_(f);
        var rows = [], total = 0;
        for (var p = 0; p < filing.holdings.length; p++) {
          var h = filing.holdings[p]; // [key, name, cusip, isin, ticker, weight, assetCat, derivative, payoff, country]
          total += h[5];
          rows.push([f.seriesId, filing.asOf, fixKey_(h[0], h[3], h[1]), h[1], h[2], h[3], h[4], h[5], h[6],
                     h[7] ? "Y" : "N", h[8], f.accession, h[9] || ""]);
        }
        if (rows.length) {
          holdSheet.getRange(holdSheet.getLastRow() + 1, 1, rows.length, HOLDINGS_HEADERS.length).setValues(rows);
        }
        info = { seriesName: filing.seriesName, asOf: filing.asOf, count: rows.length,
                 total: Math.round(total * 100) / 100 };
        haveAcc[f.accession] = info;
      }
      snapSheet.appendRow([t, f.seriesId, info.seriesName, info.asOf, f.filed,
                           f.accession, info.count, info.total, new Date()]);
      haveTickerAcc[(t + "|" + f.accession).toUpperCase()] = true;
      results[t] = "Added";
    } catch (e2) {
      results[t] = "Error: " + e2.message;
    }
  }
  recordCoverage_(results);
  var added = false;
  for (var a in results) if (results[a] === "Added") { added = true; break; }
  if (added) pruneOldFilings_(snapSheet, holdSheet, KEEP_FILINGS);
  SpreadsheetApp.flush();
  return results;
}

// Deletes Holdings and Snapshots rows for filings older than the newest
// `keep` per series. Each filing's Holdings rows were written as one
// contiguous block, so this deletes whole row ranges (bottom-up, so row
// numbers stay valid) after reading only the Accession column -- cheap
// even with hundreds of thousands of rows.
function pruneOldFilings_(snapSheet, holdSheet, keep) {
  if (snapSheet.getLastRow() < 2) return;
  var snaps = snapSheet.getRange(2, 1, snapSheet.getLastRow() - 1, SNAPSHOTS_HEADERS.length).getValues();
  var bySeries = {}; // seriesId -> { accession: asOf }
  for (var i = 0; i < snaps.length; i++) {
    var sid = snaps[i][1], acc = snaps[i][5];
    if (!sid || !acc) continue;
    (bySeries[sid] = bySeries[sid] || {})[acc] = dateStr_(snaps[i][3]);
  }
  var drop = {};
  for (var s in bySeries) {
    var accs = Object.keys(bySeries[s]).sort(function (x, y) {
      var dx = bySeries[s][x], dy = bySeries[s][y];
      return dx < dy ? 1 : dx > dy ? -1 : (x < y ? 1 : -1); // newest first
    });
    for (var j = keep; j < accs.length; j++) drop[accs[j]] = true;
  }
  if (!Object.keys(drop).length) return;
  deleteRowsWhere_(holdSheet, HOLDINGS_HEADERS.length, function (acc) { return drop[acc]; });
  deleteRowsWhere_(snapSheet, 6, function (acc) { return drop[acc]; });
}

// Deletes every data row whose value in column `col` matches `test`,
// grouping adjacent matches into single deleteRows() calls.
function deleteRowsWhere_(sheet, col, test) {
  var last = sheet.getLastRow();
  if (last < 2) return;
  var vals = sheet.getRange(2, col, last - 1, 1).getValues();
  var r = vals.length - 1;
  while (r >= 0) {
    if (!test(vals[r][0])) { r--; continue; }
    var end = r;
    while (r >= 0 && test(vals[r][0])) r--;
    sheet.deleteRows(r + 3, end - r); // matching data indexes r+1..end = sheet rows r+3..end+2
  }
}

// One filing's parsed holdings from retire.cash. v=2 is the response shape
// that includes each holding's country.
function fetchFilingApi_(f) {
  return apiFetch_("/nport-filing?cik=" + encodeURIComponent(f.cik || "") +
                   "&accession=" + encodeURIComponent(f.accession) +
                   "&series=" + encodeURIComponent(f.seriesId) + "&v=2");
}

// Accessions whose Holdings rows have no Country yet -> {start, count}
// (sheet row of the block's first row, and how many rows). Each filing was
// written as one contiguous block.
function accessionsMissingCountry_(holdSheet) {
  var out = {};
  var last = holdSheet.getLastRow();
  if (last < 2) return out;
  if (!holdSheet.getRange(1, 13).getValue()) holdSheet.getRange(1, 13).setValue("Country");
  var v = holdSheet.getRange(2, 12, last - 1, 2).getValues(); // Accession, Country
  var cur = null, curHas = false, start = 0;
  function close(end) { if (cur && !curHas) out[cur] = { start: start + 2, count: end - start }; }
  for (var i = 0; i < v.length; i++) {
    if (v[i][0] !== cur) { close(i); cur = v[i][0]; curHas = false; start = i; }
    if (v[i][1]) curHas = true;
  }
  close(v.length);
  return out;
}

// Fills the Country column for one stored filing. Rows were written in the
// filing's own order, so they line up with a fresh download row by row.
function backfillCountry_(holdSheet, f, block) {
  var filing = fetchFilingApi_(f);
  if (!filing.holdings || filing.holdings.length !== block.count) return;
  var col = filing.holdings.map(function (h) { return [h[9] || ""]; });
  holdSheet.getRange(block.start, 13, block.count, 1).setValues(col);
}

// Calls the retire.cash relay. payload -> POST JSON, otherwise GET.
function apiFetch_(path, payload) {
  var opts = { muteHttpExceptions: true };
  if (payload) {
    opts.method = "post";
    opts.contentType = "application/json";
    opts.payload = JSON.stringify(payload);
  }
  var resp = UrlFetchApp.fetch(HOLDINGS_API_BASE + path, opts);
  var body;
  try { body = JSON.parse(resp.getContentText()); } catch (e) { body = null; }
  if (resp.getResponseCode() !== 200 || !body || !body.ok) {
    throw new Error((body && body.error) || ("HTTP " + resp.getResponseCode()));
  }
  return body;
}

// Holdings keys written before placeholder IDs were filtered (e.g.
// "CUSIP:999999999", which one filer uses for every foreign stock) would
// merge unrelated securities. Re-key those rows by ISIN, else by name.
var PLACEHOLDER_KEY_RE = /^(CUSIP|ISIN):(.)\2+$|^(CUSIP|ISIN):(N\/A|NONE)$/;
function fixKey_(key, isin, name) {
  if (!PLACEHOLDER_KEY_RE.test(String(key))) return key;
  if (isin && !/^(.)\1+$/.test(isin)) return "ISIN:" + isin;
  return "NAME:" + String(name || "").toUpperCase().replace(/[^A-Z0-9]/g, "");
}

function parseTickerList_(param) {
  var raw = (param || "").split(",");
  if (raw.length > MAX_HOLDINGS_TICKERS) {
    return { error: "Too many tickers in one call (max " + MAX_HOLDINGS_TICKERS + ")" };
  }
  var valid = [], invalid = {}, seen = {};
  for (var i = 0; i < raw.length; i++) {
    var t = raw[i].toUpperCase().replace("MUTF:", "").trim();
    if (!t || seen[t]) continue;
    seen[t] = true;
    if (isValidTicker_(t)) valid.push(t); else invalid[t] = true;
  }
  return { valid: valid, invalid: invalid };
}

function getOrCreateTab_(ss, name, headers) {
  var sheet = ss.getSheetByName(name);
  if (!sheet) {
    sheet = ss.insertSheet(name);
    sheet.appendRow(headers);
    sheet.setFrozenRows(1);
  }
  return sheet;
}

// Sheets turns "2026-05-31" into a Date on write; normalize back to text.
function dateStr_(v) {
  if (v instanceof Date) {
    return Utilities.formatDate(v, SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone(), "yyyy-MM-dd");
  }
  return (v || "").toString();
}

function jsonOut_(obj) {
  return ContentService.createTextOutput(JSON.stringify(obj)).setMimeType(ContentService.MimeType.JSON);
}

// ── v4.5: Template Sheet menu ─────────────────────────────────────
// A copy of the Portfolio X-Ray template Sheet brings this script along,
// so nobody has to paste it. What Google does NOT copy is the triggers and
// the Web App deployment, so the "Portfolio X-Ray" menu below walks
// through those: Set up installs both triggers (and asks for permission),
// then shows how to deploy the Web App and connect retire.cash.

var XRAY_SETUP_URL = "https://retire.cash/#setup";

// A broad mix for demos: index funds, dividend/income, sector, active
// mutual funds, a target-date fund (fund of funds), bonds, international,
// REIT, money market, and a few single stocks that the funds also hold.
var SAMPLE_TICKERS = [
  "VTI", "VOO", "QQQ", "VT", "VXUS", "VEA", "VWO",
  "SCHD", "VYM", "JEPI", "JEPQ",
  "VGT", "XLK", "SMH", "FSELX", "FCNTX", "AGTHX",
  "VFIFX", "BND", "AGG", "VNQ", "SPAXX",
  "AAPL", "MSFT", "NVDA", "AMZN", "GOOGL"
];

/** Adds the Portfolio X-Ray menu each time the Sheet opens. */
function onOpen() {
  SpreadsheetApp.getUi().createMenu("Portfolio X-Ray")
    .addItem("1. Set up (run once)", "XRAY_SETUP")
    .addItem("2. How to connect retire.cash", "XRAY_SHOW_CONNECT")
    .addSeparator()
    .addItem("Refresh prices now", "XRAY_REFRESH_PRICES")
    .addItem("Load fund holdings now", "XRAY_LOAD_HOLDINGS")
    .addItem("Add sample tickers", "XRAY_ADD_SAMPLES")
    .addSeparator()
    .addSubMenu(SpreadsheetApp.getUi().createMenu("Advanced")
      .addItem("Make this Sheet the read-only public demo", "XRAY_DEMO_ON")
      .addItem("Turn off read-only demo", "XRAY_DEMO_OFF"))
    .addToUi();
}

/** Menu: install the price and holdings triggers, then explain the Web App step. */
function XRAY_SETUP() {
  setupRefreshTrigger();
  setupHoldingsTrigger();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  getOrCreateSheet_();
  REFRESH_QUOTES();
  ss.toast("Prices refresh every 6 hours and fund holdings monthly.", "Setup done", 8);
  XRAY_SHOW_CONNECT();
}

/** Menu: the Web App deployment steps, with a link to the Setup tab. */
function XRAY_SHOW_CONNECT() {
  var html =
    '<div style="font:14px/1.5 Arial,sans-serif;color:#2c3e50">' +
    '<p style="margin-top:0">Last step: publish this Sheet\'s script as a Web App so retire.cash can read it.</p>' +
    '<ol style="padding-left:18px">' +
    '<li>Open <b>Extensions → Apps Script</b>.</li>' +
    '<li>Click the blue <b>Deploy</b> button → <b>New deployment</b>.</li>' +
    '<li>Click the gear next to <i>Select type</i> → <b>Web app</b>.</li>' +
    '<li>Set <b>Execute as: Me</b> and <b>Who has access: Anyone</b>, then click <b>Deploy</b>.</li>' +
    '<li>Copy the <b>Web app URL</b> (it ends in <code>/exec</code>).</li>' +
    '<li>Paste it on <a href="' + XRAY_SETUP_URL + '" target="_blank">retire.cash → Setup</a> and click <b>Save &amp; test connection</b>.</li>' +
    '</ol>' +
    '<p style="color:#7f8c8d;font-size:12px">Seeing <i>"Sorry, unable to open the file at this time"</i>? You\'re signed in to more than one Google account. ' +
    'Open this Sheet in a window (or Incognito) where only its owner is signed in.</p>' +
    '<p style="color:#7f8c8d;font-size:12px">"Anyone" lets the page call your script without a Google sign-in. ' +
    'The URL is long and unguessable, and your share counts are never stored in this Sheet.</p></div>';
  SpreadsheetApp.getUi().showModalDialog(
    HtmlService.createHtmlOutput(html).setWidth(460).setHeight(360), "Connect Portfolio X-Ray");
}

/** Menu: bump the refresh salt so every price cell recalculates now. */
function XRAY_REFRESH_PRICES() {
  REFRESH_QUOTES();
  SpreadsheetApp.getActiveSpreadsheet().toast("Prices are recalculating.", "Portfolio X-Ray", 5);
}

/** Menu: run the holdings pipeline now (continues on its own if needed). */
function XRAY_LOAD_HOLDINGS() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  ss.toast("Loading fund holdings from SEC filings. This can take a few minutes.", "Portfolio X-Ray", 10);
  var log = REFRESH_HOLDINGS() || {};
  if (log.error) { ss.toast("Another refresh is running. Try again in a few minutes.", "Portfolio X-Ray", 8); return; }
  var n = Object.keys(log.pulled || {}).length;
  var more = ScriptApp.getProjectTriggers().some(function (t) { return t.getHandlerFunction() === "CONTINUE_HOLDINGS"; });
  ss.toast("Checked " + n + " funds." + (more ? " More work is left; it continues on its own in 5 minutes." : " All done."), "Portfolio X-Ray", 10);
}

/** Menu: add the demo tickers to Securities (existing ones are skipped). */
function XRAY_ADD_SAMPLES() {
  var lock = LockService.getScriptLock();
  if (!lock.tryLock(30000)) { SpreadsheetApp.getActiveSpreadsheet().toast("Busy, try again.", "Portfolio X-Ray", 5); return; }
  var added = 0;
  try {
    var sheet = getOrCreateSheet_();
    SAMPLE_TICKERS.forEach(function (t) { if (registerOne_(sheet, t) === "Success") added++; });
  } finally {
    lock.releaseLock();
  }
  SpreadsheetApp.getActiveSpreadsheet().toast(
    added + " sample tickers added. Use \"Load fund holdings now\" to pull their holdings.", "Portfolio X-Ray", 8);
}

// ── v4.6: read-only public demo ────────────────────────────────────
// The demo is tied to THIS spreadsheet's ID, so a copy of the demo Sheet
// is never read-only (its ID is different), whether or not Google copies
// the script's properties along with it. Menu functions and the scheduled
// refreshes still work in demo mode; only Web App writes are refused.
function isDemo_() {
  var id = PropertiesService.getScriptProperties().getProperty("DEMO_SHEET_ID");
  return !!id && id === SpreadsheetApp.getActiveSpreadsheet().getId();
}

/** Menu: make this Sheet's Web App read-only (for the public demo). */
function XRAY_DEMO_ON() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  PropertiesService.getScriptProperties().setProperty("DEMO_SHEET_ID", ss.getId());
  ss.toast("The Web App now only serves prices and holdings; it won't add tickers or pull filings.", "Read-only demo on", 8);
}

/** Menu: allow Web App writes again. */
function XRAY_DEMO_OFF() {
  PropertiesService.getScriptProperties().deleteProperty("DEMO_SHEET_ID");
  SpreadsheetApp.getActiveSpreadsheet().toast("The Web App can add tickers and pull filings again.", "Read-only demo off", 8);
}