/**
 * FxFeed for Google Sheets: exchange-rate custom functions backed by the
 * FxFeed API (https://fxfeed.io).
 *
 *   =FXRATE("USD", "EUR")                     latest rate
 *   =FXRATE("USD", "EUR", "2024-01-02")       rate on a past day
 *   =FXCONVERT(100, "USD", "EUR")             100 USD in EUR
 *   =FXCONVERT(A2:A20, "USD", "EUR", B1)      a column of amounts on a day
 *   =FXRATES("USD", "EUR,GBP,JPY")            a two-column table of rates
 *
 * Install: Extensions > Apps Script, paste this file, save, reload the
 * spreadsheet, then FxFeed > Set API key. The key is kept in the script's
 * properties, never in a cell. Get a free key at https://fxfeed.io/signup.
 *
 * Every answer is cached (latest rates for an hour, past days for six
 * hours, the longest CacheService allows), and one request per base
 * currency and day serves every target currency, so a sheet full of
 * formulas spends very little of your monthly quota.
 *
 * Version 1.0.0. Documentation: https://fxfeed.io/docs/google-sheets
 */

var FXFEED_API_BASE = 'https://api.fxfeed.io/v2';
var FXFEED_KEY_PROPERTY = 'FXFEED_API_KEY';
var FXFEED_CACHE_LATEST_SECONDS = 60 * 60;
var FXFEED_CACHE_HISTORICAL_SECONDS = 6 * 60 * 60;
var FXFEED_RETRIES = 3;
var FXFEED_SIGNUP_URL = 'https://fxfeed.io/signup';
var FXFEED_PRICING_URL = 'https://fxfeed.io/pricing';

/**
 * Returns the exchange rate from one currency to another: how many units
 * of `to` one unit of `from` buys. Leave out the date for the latest rate.
 *
 * @param {string} from The currency to convert from, e.g. "USD".
 * @param {string} to The currency to convert to, e.g. "EUR".
 * @param {Date|string} [date] Optional day for a historical rate: a date cell or "YYYY-MM-DD".
 * @return {number} The exchange rate.
 * @customfunction
 */
function FXRATE(from, to, date) {
  var base = fxfeedCurrency_(from, 'from');
  var target = fxfeedCurrency_(to, 'to');
  var day = fxfeedDay_(date);
  if (base === target) {
    return 1;
  }
  var rates = fxfeedRates_(base, day);
  if (typeof rates[target] !== 'number') {
    throw new Error('FxFeed has no ' + base + '/' + target + ' rate' + (day ? ' for ' + day : '') + '.');
  }
  return rates[target];
}

/**
 * Converts an amount, or a range of amounts, from one currency to another
 * at the latest rate or at the rate of a past day.
 *
 * @param {number|Array<Array<number>>} amount The amount, or a range of amounts, to convert.
 * @param {string} from The currency to convert from, e.g. "USD".
 * @param {string} to The currency to convert to, e.g. "EUR".
 * @param {Date|string} [date] Optional day for a historical rate: a date cell or "YYYY-MM-DD".
 * @return {number|Array<Array<number>>} The converted amount(s).
 * @customfunction
 */
function FXCONVERT(amount, from, to, date) {
  // Read the amounts before the rate, so a typo costs no request.
  if (Array.isArray(amount)) {
    var amounts = amount.map(function (row) {
      return (Array.isArray(row) ? row : [row]).map(function (cell) {
        return cell === '' || cell === null ? '' : fxfeedAmount_(cell);
      });
    });
    var rateForRange = FXRATE(from, to, date);
    return amounts.map(function (row) {
      return row.map(function (cell) {
        return cell === '' ? '' : cell * rateForRange;
      });
    });
  }
  var value = fxfeedAmount_(amount);
  return value * FXRATE(from, to, date);
}

/**
 * Returns a two-column table of rates for one base currency: the currency
 * code in the first column and its rate in the second.
 *
 * @param {string} base The base currency, e.g. "USD".
 * @param {string} [currencies] Optional comma-separated currencies, e.g. "EUR,GBP". Leave out for every currency.
 * @param {Date|string} [date] Optional day for historical rates: a date cell or "YYYY-MM-DD".
 * @return {Array<Array<string|number>>} Rows of [currency, rate].
 * @customfunction
 */
function FXRATES(base, currencies, date) {
  var from = fxfeedCurrency_(base, 'base');
  var day = fxfeedDay_(date);
  var rates = fxfeedRates_(from, day);
  var wanted = [];
  if (currencies === undefined || currencies === null || String(currencies).trim() === '') {
    wanted = Object.keys(rates).sort();
  } else {
    wanted = fxfeedFlatten_(currencies).join(',').split(',').filter(function (code) {
      return code.trim() !== '';
    }).map(function (code) {
      return fxfeedCurrency_(code, 'currencies');
    });
  }
  return wanted.map(function (code) {
    if (code === from) {
      return [code, 1];
    }
    if (typeof rates[code] !== 'number') {
      throw new Error('FxFeed has no ' + from + '/' + code + ' rate' + (day ? ' for ' + day : '') + '.');
    }
    return [code, rates[code]];
  });
}

/** Adds the FxFeed menu when the spreadsheet opens. */
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('FxFeed')
    .addItem('Set API key', 'fxfeedSetApiKey')
    .addItem('Test API key', 'fxfeedTestApiKey')
    .addItem('Remove API key', 'fxfeedRemoveApiKey')
    .addToUi();
}

/** Menu: asks for the API key and stores it in the script properties. */
function fxfeedSetApiKey() {
  var ui = SpreadsheetApp.getUi();
  var answer = ui.prompt(
    'FxFeed API key',
    'Paste your FxFeed API key (it starts with "fxf_"). Get a free key at ' + FXFEED_SIGNUP_URL + '.\n' +
      'It is stored in this script\'s properties, not in the spreadsheet.',
    ui.ButtonSet.OK_CANCEL
  );
  if (answer.getSelectedButton() !== ui.Button.OK) {
    return;
  }
  var key = answer.getResponseText().trim();
  if (!/^fxf_?[A-Za-z0-9_-]+$/.test(key)) {
    ui.alert('That does not look like an FxFeed API key: keys start with "fxf_". Nothing was saved.');
    return;
  }
  PropertiesService.getScriptProperties().setProperty(FXFEED_KEY_PROPERTY, key);
  ui.alert('Saved. FXRATE, FXCONVERT and FXRATES will use this key.');
}

/** Menu: makes one request with the stored key and reports the result. */
function fxfeedTestApiKey() {
  var ui = SpreadsheetApp.getUi();
  try {
    var body = fxfeedFetch_('/latest', { base: 'USD', currencies: 'EUR' });
    ui.alert('The key works. 1 USD = ' + body.rates.EUR + ' EUR (' + String(body.date).slice(0, 10) + ').');
  } catch (err) {
    ui.alert(err.message);
  }
}

/** Menu: forgets the stored API key. */
function fxfeedRemoveApiKey() {
  PropertiesService.getScriptProperties().deleteProperty(FXFEED_KEY_PROPERTY);
  SpreadsheetApp.getUi().alert('The FxFeed API key was removed from this spreadsheet\'s script.');
}

/**
 * Every rate for one base currency, latest (day empty) or on a day, from
 * the cache or one API request. A lock keeps a sheet recalculating many
 * cells at once from asking for the same rates several times.
 */
function fxfeedRates_(base, day) {
  var cache = CacheService.getScriptCache();
  var cacheKey = 'fxfeed:v1:' + base + ':' + (day || 'latest');
  var cached = cache.get(cacheKey);
  if (cached) {
    return JSON.parse(cached);
  }
  var lock = LockService.getScriptLock();
  var locked = lock.tryLock(20000);
  try {
    if (locked) {
      cached = cache.get(cacheKey);
      if (cached) {
        return JSON.parse(cached);
      }
    }
    var body = day
      ? fxfeedFetch_('/historical', { base: base, date: day })
      : fxfeedFetch_('/latest', { base: base });
    var rates = body.rates || {};
    if (day && Object.keys(rates).length === 0) {
      rates = fxfeedLastRatesBefore_(base, day);
    }
    cache.put(cacheKey, JSON.stringify(rates), day ? FXFEED_CACHE_HISTORICAL_SECONDS : FXFEED_CACHE_LATEST_SECONDS);
    return rates;
  } finally {
    if (locked) {
      lock.releaseLock();
    }
  }
}

/**
 * The rates of the last day up to `day` that has any. Rates are published
 * on working days, so a weekend or a bank holiday answers no rates of its
 * own; like most accounting practice, it takes the previous working day's.
 * One /timeseries request covers the week before.
 */
function fxfeedLastRatesBefore_(base, day) {
  var end = new Date(day + 'T00:00:00Z');
  var start = new Date(end.getTime() - 7 * 86400000);
  var body = fxfeedFetch_('/timeseries', {
    base: base,
    start_date: start.toISOString().slice(0, 10),
    end_date: day,
  });
  var days = Object.keys(body.rates || {}).filter(function (d) {
    return d <= day && Object.keys(body.rates[d]).length > 0;
  }).sort();
  if (days.length === 0) {
    throw new Error('FxFeed has no ' + base + ' rates on or in the week before ' + day + '.');
  }
  return body.rates[days[days.length - 1]];
}

/**
 * Calls the FxFeed API and returns the parsed JSON body, or throws an
 * error worded for the person reading the cell. A rate-limited request is
 * retried with a short pause; failures are never cached.
 */
function fxfeedFetch_(path, params) {
  var key = PropertiesService.getScriptProperties().getProperty(FXFEED_KEY_PROPERTY);
  if (!key) {
    throw new Error('No FxFeed API key: choose FxFeed > Set API key (free key at ' + FXFEED_SIGNUP_URL + ').');
  }
  var query = Object.keys(params).map(function (name) {
    return encodeURIComponent(name) + '=' + encodeURIComponent(params[name]);
  }).join('&');
  var url = FXFEED_API_BASE + path + '?' + query;
  var response;
  for (var attempt = 1; attempt <= FXFEED_RETRIES; attempt++) {
    response = UrlFetchApp.fetch(url, {
      method: 'get',
      headers: { 'X-API-Key': key, Accept: 'application/json' },
      muteHttpExceptions: true,
    });
    if (response.getResponseCode() !== 429 || attempt === FXFEED_RETRIES) {
      break;
    }
    Utilities.sleep(1000 * attempt);
  }
  var status = response.getResponseCode();
  var body = {};
  try {
    body = JSON.parse(response.getContentText());
  } catch (e) {
    body = {};
  }
  if (status === 200 && body.success !== false) {
    return body;
  }
  throw new Error(fxfeedErrorMessage_(status, body));
}

/** Words a failed response for a cell's error tooltip. */
function fxfeedErrorMessage_(status, body) {
  var message = body && (body.message || (body.error && body.error.info));
  switch (status) {
    case 401:
      return 'FxFeed rejected the API key (401). Check it with FxFeed > Set API key.';
    case 402:
      return 'FxFeed monthly request limit reached (402). Upgrade your plan at ' +
        ((body && body.upgrade_url) || FXFEED_PRICING_URL) + ' or wait for the new month (UTC).';
    case 403:
      return 'Your FxFeed plan does not include this (403)' + (message ? ': ' + message : '.');
    case 429:
      return 'FxFeed rate limit reached (429): too many requests at once. Wait a few seconds and recalculate.';
    default:
      if (status >= 500) {
        return 'FxFeed is unavailable right now (' + status + '). Try again in a minute.';
      }
      return 'FxFeed error (' + status + ')' + (message ? ': ' + message : '.');
  }
}

/** A three-letter currency code from a cell, upper-cased. */
function fxfeedCurrency_(value, name) {
  var code = String(value === undefined || value === null ? '' : value).trim().toUpperCase();
  if (!/^[A-Z]{3}$/.test(code)) {
    throw new Error('"' + name + '" must be a three-letter currency code such as USD, got "' + code + '".');
  }
  return code;
}

/**
 * A day as "YYYY-MM-DD" from a date cell, a date serial number or text;
 * empty for the latest rate. A date cell is read in the spreadsheet's own
 * time zone, which is how Sheets hands it to the function.
 */
function fxfeedDay_(value) {
  if (value === undefined || value === null || value === '') {
    return '';
  }
  if (Object.prototype.toString.call(value) === '[object Date]') {
    if (isNaN(value.getTime())) {
      throw new Error('"date" is not a valid date.');
    }
    return Utilities.formatDate(value, fxfeedTimeZone_(), 'yyyy-MM-dd');
  }
  if (typeof value === 'number') {
    // A date serial number: days since 30 December 1899.
    var ms = Math.round((value - 25569) * 86400000);
    return Utilities.formatDate(new Date(ms), 'UTC', 'yyyy-MM-dd');
  }
  var text = String(value).trim();
  if (!/^\d{4}-\d{2}-\d{2}$/.test(text)) {
    throw new Error('"date" must be a date cell or text like 2024-01-31, got "' + text + '".');
  }
  return text;
}

/** The spreadsheet's time zone, or the script's outside a spreadsheet. */
function fxfeedTimeZone_() {
  try {
    return SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone();
  } catch (e) {
    return Session.getScriptTimeZone();
  }
}

/** A number from a cell, or an error naming the bad value. */
function fxfeedAmount_(value) {
  var amount = typeof value === 'number' ? value : Number(String(value).replace(/,/g, ''));
  if (typeof value === 'boolean' || isNaN(amount)) {
    throw new Error('"amount" must be a number, got "' + value + '".');
  }
  return amount;
}

/** The values of a cell or a range, as one flat list of strings. */
function fxfeedFlatten_(value) {
  if (!Array.isArray(value)) {
    return [String(value)];
  }
  var out = [];
  value.forEach(function (item) {
    out = out.concat(fxfeedFlatten_(item));
  });
  return out;
}