/** Bullion API Sheets integration. Network requests only run from the Refresh menu. */
var BULLION_BASE_ = 'https://api.bullionapi.dev';
var BULLION_NOTICE_ = 'Cached, refreshed hourly, not live';
var BULLION_TTL_ = 60 * 60 * 1000;
var BULLION_METALS_ = ['gold', 'silver', 'palladium', 'copper', 'platinum'];
var BULLION_KARATS_ = [
  '24k',
  '23k',
  '22k',
  '21.6k',
  '21k',
  '18k',
  '16k',
  '14k',
  '12k',
  '10k',
  '9k',
  '8k',
  '6k',
];

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Bullion API')
    .addItem('Settings', 'bullionSettings')
    .addItem('Create template tabs', 'bullionCreateTemplate')
    .addItem('Refresh prices', 'bullionRefresh')
    .addToUi();
}

function bullionSettings() {
  SpreadsheetApp.getUi().showModalDialog(
    HtmlService.createHtmlOutputFromFile('Settings').setWidth(420).setHeight(360),
    'Bullion API settings',
  );
}

function bullionSaveSettings(key, limit) {
  var lock = LockService.getDocumentLock();
  if (!lock.tryLock(1000)) throw new Error('A refresh is running. Try again shortly.');
  try {
    key = String(key || '').trim();
    limit = Number(limit);
    if (!key || key.length > 512 || /\s/.test(key)) throw new Error('Enter a valid API key.');
    if (!Number.isInteger(limit) || limit < 1 || limit > 100000)
      throw new Error('Choose a monthly cap from 1 to 100000.');
    PropertiesService.getUserProperties().setProperties({ bullionKey: key, bullionLimit: String(limit) });
  } finally {
    lock.releaseLock();
  }
}

/** Creates a local template without overwriting existing tabs or spending API quota. */
function bullionCreateTemplate() {
  var book = SpreadsheetApp.getActiveSpreadsheet();
  if (book.getSheetByName('Bullion Requests') || book.getSheetByName('Bullion Prices')) {
    SpreadsheetApp.getUi().alert('Template tabs already exist. Nothing was changed.');
    return;
  }
  if (book.getRangeByName('BullionRefresh')) {
    SpreadsheetApp.getUi().alert('The name BullionRefresh already exists. Nothing was changed.');
    return;
  }
  var requests = book.insertSheet('Bullion Requests');
  requests.getRange('A1:G4').setValues([
    ['Type', 'Metal', 'Currency', 'Unit', 'Start (YYYY-MM-DD)', 'End (YYYY-MM-DD)', 'Karat'],
    ['price', 'gold', 'GBP', 'gram', '', '', ''],
    ['carat', 'gold', 'GBP', 'gram', '', '', '18k'],
    ['history', 'gold', 'GBP', 'troy_oz', '2026-10-01', '2026-10-07', ''],
  ]);
  requests.setFrozenRows(1);
  requests.autoResizeColumns(1, 7);
  var prices = book.insertSheet('Bullion Prices');
  prices.getRange('A1:B3').setValues([
    ['Bullion API', BULLION_NOTICE_],
    ['Refresh token (UTC)', 'Not refreshed'],
    ['Exact decimal text; observation timestamps remain authoritative.', ''],
  ]);
  book.setNamedRange('BullionRefresh', prices.getRange('B2'));
  prices.getRange('A5').setFormula('=BULLION_PRICE("gold","GBP","gram",BullionRefresh)');
  prices.getRange('A9').setFormula('=BULLION_CARAT("18k","GBP",BullionRefresh)');
  prices
    .getRange('A13')
    .setFormula('=BULLION_HISTORY("gold","GBP","2026-10-01","2026-10-07",BullionRefresh)');
  prices.autoResizeColumns(1, 6);
}

/**
 * Cached metal price with observation time and exact decimal text.
 * @param {string} metal gold, silver, palladium, copper or platinum.
 * @param {string} currency Three-letter currency code.
 * @param {string} unit gram, kilogram or troy_oz.
 * @param {string} refresh Reference to BullionRefresh for recalculation.
 * @return {string[][]} Price and metadata table.
 * @customfunction
 */
function BULLION_PRICE(metal, currency, unit, refresh) {
  return bullionRead_(['price', metal, currency, unit || 'troy_oz', '', '', ''], refresh);
}

/**
 * Cached gold carat price per gram, with observation time.
 * @param {string} karat A supported karat such as 18k or 24k.
 * @param {string} currency Three-letter currency code.
 * @param {string} refresh Reference to BullionRefresh for recalculation.
 * @return {string[][]} Price and metadata table.
 * @customfunction
 */
function BULLION_CARAT(karat, currency, refresh) {
  return bullionRead_(['carat', 'gold', currency, 'gram', '', '', karat], refresh);
}

/**
 * Retained UTC weekday closes per troy ounce, with exact decimal text.
 * @param {string} metal gold, silver, palladium, copper or platinum.
 * @param {string} currency Three-letter currency code.
 * @param {string} start Inclusive YYYY-MM-DD date.
 * @param {string} end Inclusive YYYY-MM-DD date.
 * @param {string} refresh Reference to BullionRefresh for recalculation.
 * @return {string[][]} History and metadata table.
 * @customfunction
 */
function BULLION_HISTORY(metal, currency, start, end, refresh) {
  return bullionRead_(['history', metal, currency, 'troy_oz', start, end, ''], refresh);
}

function bullionDate_(value) {
  // Prefer ISO date text. Sheet date cells are interpreted in the sheet's timezone.
  var text =
    Object.prototype.toString.call(value) === '[object Date]'
      ? Utilities.formatDate(
          value,
          SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone(),
          'yyyy-MM-dd',
        )
      : String(value || '').trim();
  if (
    !/^\d{4}-\d{2}-\d{2}$/.test(text) ||
    !Number.isFinite(Date.parse(text)) ||
    new Date(text).toISOString().slice(0, 10) !== text
  ) {
    throw new Error('Use a valid YYYY-MM-DD date.');
  }
  return text;
}

function bullionRequest_(row) {
  var type = String(row[0] || '')
    .trim()
    .toLowerCase();
  var metal = String(row[1] || '')
    .trim()
    .toLowerCase();
  var currency = String(row[2] || 'USD')
    .trim()
    .toUpperCase();
  var unit = String(row[3] || 'troy_oz')
    .trim()
    .toLowerCase();
  unit = { g: 'gram', kg: 'kilogram', toz: 'troy_oz' }[unit] || unit;
  if (!['price', 'carat', 'history'].includes(type)) throw new Error('Type must be price, carat or history.');
  if (!BULLION_METALS_.includes(metal))
    throw new Error('Unknown metal. Use gold, silver, palladium, copper or platinum.');
  if (!/^[A-Z]{3}$/.test(currency)) throw new Error('Use a three-letter currency code.');
  if (!['gram', 'kilogram', 'troy_oz'].includes(unit))
    throw new Error('Unknown unit. Use gram, kilogram or troy_oz.');
  var karat =
    String(row[6] || '')
      .trim()
      .toLowerCase()
      .replace(/k$/, '') + 'k';
  if (type === 'carat' && (metal !== 'gold' || unit !== 'gram' || !BULLION_KARATS_.includes(karat))) {
    throw new Error('Choose a supported gold karat, per gram.');
  }
  var path = '/v1/' + metal + '/price?currency=' + currency + '&unit=' + unit;
  if (type === 'carat') path = '/v1/gold/carat?currency=' + currency;
  if (type === 'history') {
    if (unit !== 'troy_oz') throw new Error('History formulas use troy_oz.');
    var start = bullionDate_(row[4]);
    var end = bullionDate_(row[5]);
    var startDate = new Date(start);
    // Match lib/date-range.ts: clamp a leap-day anniversary to the month's final day.
    var maxEnd = new Date(0);
    maxEnd.setUTCFullYear(startDate.getUTCFullYear() + 5, startDate.getUTCMonth() + 1, 0);
    maxEnd.setUTCDate(Math.min(startDate.getUTCDate(), maxEnd.getUTCDate()));
    if (start > end || new Date(end) > maxEnd)
      throw new Error('History must be ordered and no longer than five years.');
    path += '&start=' + start + '&end=' + end;
  }
  return { path: path, type: type, metal: metal, currency: currency, unit: unit, karat: karat };
}

function bullionSnapshot_(state, path, fingerprint) {
  var entry = state[path];
  if (entry && entry.fingerprint !== fingerprint) entry = null;
  if (!entry) throw new Error('Add this request to Bullion Requests, then use Bullion API > Refresh prices.');
  if (Date.now() - entry.at >= BULLION_TTL_)
    throw new Error('Snapshot expired. Use Bullion API > Refresh prices.');
  if (entry.error) throw new Error(entry.error);
  return entry.data;
}

function bullionRead_(row, _refresh) {
  var request = bullionRequest_(row);
  var props = PropertiesService.getUserProperties();
  var key = props.getProperty('bullionKey');
  if (!key) throw new Error('The sheet owner must configure Bullion API Settings.');
  var state = bullionLoad_(props);
  var fingerprint = bullionDigest_(key);
  var data = bullionSnapshot_(state, request.path, fingerprint);
  var attribution = data.attribution;
  if (request.type === 'carat') {
    var companion = bullionRequest_(['price', 'gold', request.currency, 'gram']);
    var price = bullionSnapshot_(state, companion.path, fingerprint);
    if (price.observations[0].date !== data.as_of)
      throw new Error('Carat and price observation times differ. Refresh after the snapshots expire.');
    attribution = price.attribution;
  }
  var observations =
    request.type === 'carat' ? [{ value: data.karats[request.karat], date: data.as_of }] : data.observations;
  return [['Value (exact text)', 'Observed (UTC)', 'Currency', 'Unit', 'Status', 'Attribution']].concat(
    observations.map(function (point) {
      return [point.value, point.date, request.currency, request.unit, BULLION_NOTICE_, attribution];
    }),
  );
}

function bullionDigest_(text) {
  return Utilities.base64EncodeWebSafe(Utilities.computeDigest(Utilities.DigestAlgorithm.SHA_256, text));
}

function bullionLoad_(props) {
  for (var attempt = 0; attempt < 3; attempt++) {
    var manifest = props.getProperty('bullionSnapshot');
    if (!manifest) return {};
    try {
      var meta = JSON.parse(manifest);
      var text = '';
      for (var i = 0; i < meta.count; i++)
        text += props.getProperty('bullionData_' + meta.slot + '_' + i) || '';
      var state = JSON.parse(text);
      if (props.getProperty('bullionSnapshot') === manifest) return state;
    } catch (_error) {
      if (props.getProperty('bullionSnapshot') === manifest) break;
    }
    // A menu refresh replaced the snapshot during this read. Read the new generation.
  }
  throw new Error('Stored snapshot is unavailable. Use Bullion API > Refresh prices.');
}

function bullionStore_(props, state) {
  var text = JSON.stringify(state);
  if (Utilities.newBlob(text).getBytes().length > 180000)
    throw new Error('Too much history. Shorten the ranges or reduce the request table.');
  var previous = JSON.parse(props.getProperty('bullionSnapshot') || '{"slot":"b","count":0}');
  var slot = previous.slot === 'a' ? 'b' : 'a';
  var count = Math.ceil(text.length / 2000);
  for (var i = 0; i < count; i++)
    props.setProperty('bullionData_' + slot + '_' + i, text.slice(i * 2000, (i + 1) * 2000));
  // Switch the manifest last so formulas cannot read a partially written snapshot.
  props.setProperty(
    'bullionSnapshot',
    JSON.stringify({ slot: slot, count: count, revision: (previous.revision || 0) + 1 }),
  );
  for (var j = 0; j < previous.count; j++) props.deleteProperty('bullionData_' + previous.slot + '_' + j);
}

function bullionError_(status) {
  if (status === 401) return 'Invalid API key. The owner should check Settings.';
  if (status === 403) return 'Verify the email address for your Bullion API account.';
  if (status === 429) return 'API request limit reached. Check your account quota and try later.';
  if (status === 400) return 'Unsupported currency, unit or date range. Check the request.';
  if (status === 404) return 'No cached observations available for this request.';
  return 'Bullion API is unavailable. Try a manual refresh later.';
}

function bullionData_(body, request) {
  function decimal(value) {
    return typeof value === 'string' && /^\d+(\.\d+)?$/.test(value);
  }
  if (body.status !== 'success' || body.currency !== request.currency || body.unit !== request.unit)
    throw new Error('Invalid response');
  if (request.type === 'carat') {
    if (
      !/^\d{4}-\d{2}-\d{2}T/.test(body.as_of) ||
      !BULLION_KARATS_.every(function (k) {
        return decimal(body.karats[k]);
      })
    )
      throw new Error('Invalid response');
    return { karats: body.karats, as_of: body.as_of };
  }
  var series = body.series[0];
  if (
    body.metal !== request.metal ||
    series.id !== request.metal + '_price' ||
    !series.observations.length ||
    !series.observations.every(function (p) {
      return decimal(p.value) && typeof p.date === 'string' && /^\d{4}-\d{2}-\d{2}/.test(p.date);
    })
  )
    throw new Error('Invalid response');
  var attribution = series.conversion
    ? 'Exchange Rates By UniRateAPI (https://unirateapi.com/); FX snapshot ' +
      series.conversion.snapshot_at +
      (series.conversion.stale ? '; FX snapshot stale' : '')
    : '';
  return {
    observations: series.observations.map(function (p) {
      return { value: p.value, date: p.date };
    }),
    attribution: attribution,
  };
}

function bullionRefresh() {
  var lock = LockService.getDocumentLock();
  if (!lock.tryLock(1000)) {
    SpreadsheetApp.getUi().alert('A refresh is already running.');
    return;
  }
  var message;
  try {
    message = bullionRefresh_();
  } catch (_error) {
    message =
      'Refresh could not finish. Check Settings and the request table, then try again. No credentials or server diagnostics are shown.';
  } finally {
    lock.releaseLock();
  }
  SpreadsheetApp.getUi().alert(message);
}

function bullionRefresh_() {
  var book = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = book.getSheetByName('Bullion Requests');
  var refresh = book.getRangeByName('BullionRefresh');
  if (!sheet || !refresh) return 'Use Create template tabs first.';
  var props = PropertiesService.getUserProperties();
  var key = props.getProperty('bullionKey');
  if (!key) return 'The sheet owner must configure Settings first.';
  var rows = sheet
    .getDataRange()
    .getValues()
    .slice(1)
    .filter(function (row) {
      return row.some(function (v) {
        return v !== '';
      });
    });
  if (!rows.length || rows.length > 20) return 'Configure between 1 and 20 request rows.';
  var requests;
  try {
    requests = rows.map(bullionRequest_);
  } catch (error) {
    return error.message;
  }
  // The carat route omits FX metadata. Reuse the matching public gold-price snapshot.
  requests = requests.concat(
    requests
      .filter(function (request) {
        return request.type === 'carat';
      })
      .map(function (request) {
        return bullionRequest_(['price', 'gold', request.currency, 'gram']);
      }),
  );
  var state = {};
  var previous;
  try {
    previous = bullionLoad_(props);
  } catch (_error) {
    previous = {};
  }
  var activePaths = requests.map(function (request) {
    return request.path;
  });
  Object.keys(previous).forEach(function (path) {
    if (!activePaths.includes(path)) delete previous[path];
  });
  var fingerprint = bullionDigest_(key);
  var month = new Date().toISOString().slice(0, 7);
  var budgetKey = 'bullionBudget_' + fingerprint;
  var budget = JSON.parse(props.getProperty(budgetKey) || '{}');
  if (budget.month !== month) budget = { month: month, used: 0 };
  var cap = Number(props.getProperty('bullionLimit') || '30');
  var cache = CacheService.getUserCache();
  var calls = 0;
  var stop = false;
  var started = Date.now();
  requests.forEach(function (request) {
    if (state[request.path]) return;
    var cacheKey = bullionDigest_(fingerprint + request.path);
    var entry = previous[request.path];
    if (!entry || entry.fingerprint !== fingerprint) {
      try {
        entry = JSON.parse(cache.get(cacheKey) || 'null');
      } catch (_error) {
        entry = null;
      }
    }
    if (entry && entry.fingerprint === fingerprint && Date.now() - entry.at < BULLION_TTL_) {
      state[request.path] = entry;
      return;
    }
    entry = { at: Date.now(), fingerprint: fingerprint };
    if (stop || Date.now() - started > 240000) {
      entry.error = 'Refresh stopped. Try a manual refresh later.';
    } else if (budget.used >= cap) {
      entry.error = 'Local monthly request cap reached. Review Settings and account usage.';
    } else {
      // Reserve before fetching. Network failures count locally; never retry automatically.
      budget.used++;
      props.setProperty(budgetKey, JSON.stringify(budget));
      calls++;
      try {
        var response = UrlFetchApp.fetch(BULLION_BASE_ + request.path, {
          headers: { 'X-API-Key': key },
          muteHttpExceptions: true,
          followRedirects: false,
        });
        var status = response.getResponseCode();
        if (status !== 200) {
          entry.error = bullionError_(status);
          if ([401, 403, 429].includes(status)) stop = true;
        } else {
          entry.data = bullionData_(JSON.parse(response.getContentText()), request);
        }
      } catch (_error) {
        entry.error = 'Unable to read cached prices. Try a manual refresh later.';
      }
      // Durable checkpoint before the next network call; eviction cannot cause a duplicate fetch.
      state[request.path] = entry;
      if (Utilities.newBlob(JSON.stringify(Object.assign({}, previous, state))).getBytes().length > 180000) {
        delete entry.data;
        entry.error = 'Too much history. Shorten the ranges or reduce the request table.';
      }
      bullionStore_(props, Object.assign({}, previous, state));
      var serialized = JSON.stringify(entry);
      try {
        if (Utilities.newBlob(serialized).getBytes().length < 90000) cache.put(cacheKey, serialized, 3600);
      } catch (_error) {
        /* The durable snapshot is authoritative when cache storage fails. */
      }
    }
    state[request.path] = entry;
  });
  bullionStore_(props, state);
  refresh.setValue(new Date().toISOString());
  var failures = Object.keys(state).filter(function (path) {
    return state[path].error;
  }).length;
  return (
    calls +
    ' API request(s) made; ' +
    budget.used +
    '/' +
    cap +
    ' local requests this UTC month. ' +
    failures +
    ' request(s) unavailable. See cell errors. Cached entries are reused for up to one hour.'
  );
}