/**
 * ZeroVaR Sheets demo pack — custom functions for a team walkthrough.
 *
 * Setup:
 *   1. Extensions → Apps Script → paste this file (replace Code.gs or add alongside).
 *   2. Project Settings → Script properties → RD_API_TOKEN = zvrd_…
 *   3. Run smokeSpot() once to authorize UrlFetchApp, then use formulas in the sheet.
 *
 * Tier A (live on staging today): ZV_SPOT, ZV_SPOT_HISTORY, ZV_FORWARD, ZV_VOL,
 *   ZV_CARRY, ZV_WHOAMI, ZV_TCA_PNL
 * Tier B (needs rd-routes deploy): ZV_TICK, ZV_OPTION_PRICE, ZV_MY_CASHFLOWS, ZV_MY_TRADES
 *
 * Demo layout: see apps/api-keys/SHEETS_DEMO.md
 */

const RD_API_BASE = 'https://staging.api.zerovar.com';

function _token_() {
  const token = PropertiesService.getScriptProperties().getProperty('RD_API_TOKEN');
  if (!token) {
    throw new Error('Set Script property RD_API_TOKEN to your zvrd_ key');
  }
  return token;
}

function _get_(path) {
  const res = UrlFetchApp.fetch(RD_API_BASE + path, {
    method: 'get',
    headers: { Authorization: 'Bearer ' + _token_(), Accept: 'application/json' },
    muteHttpExceptions: true,
  });
  return _parse_(res);
}

function _post_(path, body) {
  const res = UrlFetchApp.fetch(RD_API_BASE + path, {
    method: 'post',
    contentType: 'application/json',
    payload: JSON.stringify(body),
    headers: { Authorization: 'Bearer ' + _token_(), Accept: 'application/json' },
    muteHttpExceptions: true,
  });
  return _parse_(res);
}

function _parse_(res) {
  const code = res.getResponseCode();
  const text = res.getContentText();
  if (code < 200 || code >= 300) {
    throw new Error('API ' + code + ': ' + text);
  }
  return text ? JSON.parse(text) : null;
}

function _ccy_(x) {
  return String(x || '')
    .trim()
    .toUpperCase();
}

function _dateStr_(d) {
  if (d instanceof Date) {
    return Utilities.formatDate(d, 'UTC', 'yyyy-MM-dd');
  }
  return String(d).trim().slice(0, 10);
}

function _iso_(d) {
  if (d instanceof Date) {
    return d.toISOString();
  }
  return String(d).trim();
}

// ---- Tier A ----------------------------------------------------------------

/**
 * Indicative spot (foreign per base).
 * @param {string} base
 * @param {string} foreign
 * @param {string} side optional MID|BID|ASK
 * @return {number}
 * @customfunction
 */
function ZV_SPOT(base, foreign, side) {
  side = (side || 'MID').toString().toUpperCase();
  const b = _ccy_(base);
  const f = _ccy_(foreign);
  const data = _get_(
    '/api/v1/excel/spot?base=' +
      encodeURIComponent(b) +
      '&foreign=' +
      encodeURIComponent(f) +
      '&side=' +
      encodeURIComponent(side)
  );
  return data.rate;
}

/**
 * Daily spot history as a 2-column spill: date | rate.
 * @param {string} base
 * @param {string} foreign
 * @param {string|Date} start
 * @param {string|Date} end
 * @customfunction
 */
function ZV_SPOT_HISTORY(base, foreign, start, end) {
  const b = _ccy_(base);
  const f = _ccy_(foreign);
  const data = _get_(
    '/api/v1/market/spot/history?base=' +
      encodeURIComponent(b) +
      '&foreign=' +
      encodeURIComponent(f) +
      '&start=' +
      encodeURIComponent(_dateStr_(start)) +
      '&end=' +
      encodeURIComponent(_dateStr_(end))
  );
  const points = data.points || [];
  if (!points.length) {
    return [['(no points)', '']];
  }
  return points.map(function (p) {
    return [p.date, p.rate];
  });
}

/**
 * Forward mid at a weekly horizon.
 * @param {string} base
 * @param {string} foreign
 * @param {number} weeks
 * @param {string|Date} on optional valuation date
 * @return {number}
 * @customfunction
 */
function ZV_FORWARD(base, foreign, weeks, on) {
  const b = _ccy_(base);
  const f = _ccy_(foreign);
  let path =
    '/api/v1/market/forward?base=' +
    encodeURIComponent(b) +
    '&foreign=' +
    encodeURIComponent(f) +
    '&horizon_weeks=' +
    encodeURIComponent(String(weeks));
  if (on) {
    path += '&on=' + encodeURIComponent(_dateStr_(on));
  }
  const data = _get_(path);
  return data.mid != null ? data.mid : data.rate;
}

/**
 * Annualised volatility.
 * @param {string} base
 * @param {string} foreign
 * @param {string|Date} on optional
 * @return {number}
 * @customfunction
 */
function ZV_VOL(base, foreign, on) {
  const b = _ccy_(base);
  const f = _ccy_(foreign);
  let path =
    '/api/v1/market/volatility?base=' +
    encodeURIComponent(b) +
    '&foreign=' +
    encodeURIComponent(f);
  if (on) {
    path += '&on=' + encodeURIComponent(_dateStr_(on));
  }
  const data = _get_(path);
  return data.annualised_vol;
}

/**
 * Annualised carry in basis points from spot vs forward.
 * @param {number} spot
 * @param {number} forward
 * @param {number} tenor_weeks
 * @return {number}
 * @customfunction
 */
function ZV_CARRY(spot, forward, tenor_weeks) {
  const data = _post_('/api/v1/analytics/carry', {
    spot: Number(spot),
    forward: Number(forward),
    tenor_weeks: Number(tenor_weeks),
  });
  return data.carry_bps;
}

/**
 * Who the PAT belongs to (kind | level | subject prefix).
 * @return {string}
 * @customfunction
 */
function ZV_WHOAMI() {
  const data = _get_('/api/v1/me');
  const level = data.level_name || data.level || '';
  const kind = data.kind || '';
  const sub = (data.subject || '').toString().slice(0, 8);
  return kind + ' · ' + level + (sub ? ' · ' + sub + '…' : '');
}

/**
 * USD PnL on USD notional for USDSEK-style rates (foreign per 1 USD).
 * side: "SELL_USD" (buy SEK) or "BUY_USD" (sell SEK).
 * @param {number} usd_notional
 * @param {string} side
 * @param {number} exec_rate
 * @param {number} arrival_mid
 * @return {number}
 * @customfunction
 */
function ZV_TCA_PNL(usd_notional, side, exec_rate, arrival_mid) {
  const n = Number(usd_notional);
  const rExec = Number(exec_rate);
  const rMid = Number(arrival_mid);
  if (!(n > 0) || !(rExec > 0) || !(rMid > 0)) {
    throw new Error('usd_notional, exec_rate, arrival_mid must be positive');
  }
  const s = String(side || '')
    .trim()
    .toUpperCase()
    .replace(/\s+/g, '_');
  if (s === 'SELL_USD' || s === 'BUY_SEK' || s === 'SELLUSD') {
    return (n * (rMid - rExec)) / rMid;
  }
  if (s === 'BUY_USD' || s === 'SELL_SEK' || s === 'BUYUSD') {
    return (n * (rExec - rMid)) / rMid;
  }
  throw new Error('side must be SELL_USD or BUY_USD');
}

// ---- Tier B (requires deployed rd-routes) ----------------------------------

/**
 * Nearest 1m tick mid around an instant (UTC). Needs GET /api/v1/excel/tick.
 * @param {string} base
 * @param {string} foreign
 * @param {string|Date} when
 * @param {string} fidelity optional default 1m
 * @return {number}
 * @customfunction
 */
function ZV_TICK(base, foreign, when, fidelity) {
  const b = _ccy_(base);
  const f = _ccy_(foreign);
  fidelity = (fidelity || '1m').toString();
  const data = _get_(
    '/api/v1/excel/tick?base=' +
      encodeURIComponent(b) +
      '&foreign=' +
      encodeURIComponent(f) +
      '&at=' +
      encodeURIComponent(_iso_(when)) +
      '&fidelity=' +
      encodeURIComponent(fidelity)
  );
  return data.mid != null ? data.mid : data.rate;
}

/**
 * Vanilla option premium (absolute). Needs POST /api/v1/excel/option-price.
 * @param {string} base
 * @param {string} foreign
 * @param {string|Date} expiry
 * @param {number} strike
 * @param {number} notional
 * @param {boolean|string} is_call true/false
 * @param {boolean|string} is_bought default true
 * @return {number}
 * @customfunction
 */
function ZV_OPTION_PRICE(base, foreign, expiry, strike, notional, is_call, is_bought) {
  const body = {
    base: _ccy_(base),
    foreign: _ccy_(foreign),
    expiry: _dateStr_(expiry),
    strike: Number(strike),
    notional: Number(notional),
    leverage: 1,
    is_call: is_call === true || String(is_call).toLowerCase() === 'true' || String(is_call).toUpperCase() === 'CALL',
    is_bought: is_bought === undefined || is_bought === '' || is_bought === true || String(is_bought).toLowerCase() === 'true',
    is_american: false,
  };
  const data = _post_('/api/v1/excel/option-price', body);
  return data.premium;
}

/**
 * Caller's cash flows (spill). Needs GET /api/v1/me/cash-flows.
 * @param {number} page optional
 * @customfunction
 */
function ZV_MY_CASHFLOWS(page) {
  page = page || 1;
  const data = _get_('/api/v1/me/cash-flows?page=' + encodeURIComponent(String(page)) + '&page_size=25');
  const items = data.items || data.results || [];
  if (!items.length) {
    return [['(none)', '', '', '']];
  }
  const rows = [['cash_flow_id', 'entity_id', 'notional', 'maturity_date']];
  items.forEach(function (cf) {
    rows.push([
      cf.cash_flow_id || '',
      cf.entity_id || '',
      cf.notional != null ? cf.notional : '',
      cf.maturity_date || '',
    ]);
  });
  return rows;
}

/**
 * Caller's trades (spill). Needs GET /api/v1/me/trades.
 * @param {number} page optional
 * @customfunction
 */
function ZV_MY_TRADES(page) {
  page = page || 1;
  const data = _get_('/api/v1/me/trades?page=' + encodeURIComponent(String(page)) + '&page_size=25');
  const items = data.items || data.results || [];
  if (!items.length) {
    return [['(none)', '', '']];
  }
  const rows = [['trade_id', 'notional', 'settlement_date', 'market_spot_rate']];
  items.forEach(function (t) {
    rows.push([
      t.trade_id || t.id || '',
      t.notional != null ? t.notional : '',
      t.settlement_date || t.trade_date || '',
      t.market_spot_rate != null ? t.market_spot_rate : '',
    ]);
  });
  return rows;
}

// ---- Helpers for menu / smoke ----------------------------------------------

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('ZeroVaR')
    .addItem('Refresh demo spot (A1)', 'smokeSpot')
    .addToUi();
}

/** One-shot authorize + write USDSEK to A1/B1. */
function smokeSpot() {
  const data = _get_('/api/v1/excel/spot?base=USD&foreign=SEK&side=MID');
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.getRange('A1').setValue(data.rate);
  sheet.getRange('B1').setValue(data.as_of);
}