/**
 * PuntersEdge odds for Google Sheets  (version sheets-1.0, 19 October 2026)
 * https://puntersedge.online/developers/google-sheets
 *
 * Puts the next Australian races into this spreadsheet with every bookmaker's win price side
 * by side and the best price in each row highlighted. No coding: everything you can change is
 * a cell on the "PuntersEdge" tab, and everything you can do is on the PuntersEdge menu.
 *
 * Your API key is stored with your Google account for this script only (Apps Script user
 * properties). It is not written into a cell, and a copy of this sheet does not carry it.
 *
 * Credits: one refresh is one call to /v1/racing/next-to-go, which
 * costs 2 credits for up to 20 races (3 for 21-60). If nothing has changed since the last
 * refresh the API answers "not modified" and charges nothing. Checking credits is free.
 */

var PE_API = 'https://api.puntersedge.online';
var PE_CLIENT = 'sheets-1.0';
var ODDS_TAB = 'Next to go';
var SETUP_TAB = 'PuntersEdge';

// Display names for the bookmaker keys the API returns. A book that is not listed here still
// appears, under its key.
var BOOK_NAMES = {
  sportsbet: 'Sportsbet', tab: 'TAB', ladbrokes_au: 'Ladbrokes', neds: 'Neds',
  pointsbetau: 'PointsBet', betr_au: 'Betr', bet365: 'bet365', unibet: 'Unibet',
  tabtouch: 'TABtouch', betright: 'BetRight', palmerbet: 'Palmerbet', playup: 'NextBet',
  betdeluxe: 'BetDeluxe', boostbet: 'BoostBet', betgold: 'BetGold', dafabet: 'Dafabet',
  ponybet: 'PonyBet', premiumbet: 'PremiumBet', realbookie: 'RealBookie',
  stablebet: 'StableBet', winnersbet: 'WinnersBet', betway_uk: 'Betway'
};

// The settings block on the PuntersEdge tab: [label, default, note]. Rows 4-9, column B.
var SETTINGS = [
  ['Country', 'AU', 'Two-letter codes, comma-separated: AU, NZ, AU,NZ, GB,IE'],
  ['Race types', '', 'Blank for all, or any of: horse, greyhound, harness'],
  ['Races to show', 10, 'Up to 20 costs 2 credits a refresh; 21 to 60 costs 3'],
  ['Bookmakers', '', 'Blank for every bookmaker, or keys like: sportsbet,tab,ladbrokes_au'],
  ['Auto-refresh hours', '11-23', 'Australian Eastern hours the auto-refresh runs, e.g. 11-23'],
  ['Time zone for start times', 'Australia/Sydney', 'Any IANA zone, e.g. Australia/Perth']
];
var STATUS_ROW = 12;   // rows 12-18 hold the status block

// ---------------------------------------------------------------------------- menu ---------

function onOpen() {
  SpreadsheetApp.getUi().createMenu('PuntersEdge')
    .addItem('Set up / change API key', 'peSetup')
    .addItem('Refresh odds now', 'peRefresh')
    .addSeparator()
    .addItem('Auto-refresh every 5 minutes', 'peAuto5')
    .addItem('Auto-refresh every 15 minutes', 'peAuto15')
    .addItem('Stop auto-refresh', 'peAutoOff')
    .addSeparator()
    .addItem('Check my credits (free)', 'peCredits')
    .addToUi();
  setupTab_();
}

function peSetup() {
  var ui = SpreadsheetApp.getUi();
  setupTab_();
  var r = ui.prompt('PuntersEdge API key',
    'Paste your API key. No key yet? Get a free one (no card) at puntersedge.online/api',
    ui.ButtonSet.OK_CANCEL);
  if (r.getSelectedButton() !== ui.Button.OK) return;
  var key = String(r.getResponseText() || '').trim();
  if (!key) return;
  var usage = call_('/v1/usage', {}, key, null);
  if (usage.code === 401) {
    ui.alert('That key was not accepted. Copy it again from your PuntersEdge console and retry.');
    return;
  }
  PropertiesService.getUserProperties().setProperty('PE_API_KEY', key);
  if (usage.code === 200) writeUsage_(JSON.parse(usage.body));
  status_('Key saved. Choose PuntersEdge > Refresh odds now.');
  ui.alert('Key saved', 'Now choose PuntersEdge > Refresh odds now.', ui.ButtonSet.OK);
}

function peRefresh() { refresh_(false); }
function peAutoTick() { refresh_(true); }
function peAuto5() { setAuto_(5); }
function peAuto15() { setAuto_(15); }

function peAutoOff() {
  ScriptApp.getProjectTriggers().forEach(function (t) {
    if (t.getHandlerFunction() === 'peAutoTick') ScriptApp.deleteTrigger(t);
  });
  PropertiesService.getDocumentProperties().deleteProperty('PE_AUTO_MIN');
  status_('Auto-refresh is off.');
  writeProjection_();
}

function peCredits() {
  var key = key_();
  if (!key) return;
  var r = call_('/v1/usage', {}, key, null);
  if (r.code !== 200) { status_(errorText_(r)); return; }
  writeUsage_(JSON.parse(r.body));
  status_('Credits checked (this costs nothing).');
}

// ---------------------------------------------------------------------------- refresh ------

function refresh_(fromTrigger) {
  var lock = LockService.getDocumentLock();
  if (!lock.tryLock(20000)) return;
  try {
    var key = key_();
    if (!key) return;
    var s = settings_();
    if (fromTrigger && !inHours_(s.hours)) return;

    var params = { num_races: s.races, include_unresolved: 'true' };
    if (s.country) params.country = s.country;
    if (s.types) params.categories = s.types;
    if (s.books) params.bookmakers = s.books;

    var ss = SpreadsheetApp.getActive();
    var sheet = ss.getSheetByName(ODDS_TAB) || ss.insertSheet(ODDS_TAB);
    var props = PropertiesService.getDocumentProperties();
    var query = qs_(params);
    // Only ask "has it changed?" when the sheet still holds the last answer to this query.
    var etag = (props.getProperty('PE_QUERY') === query && sheet.getLastRow() > 1)
      ? props.getProperty('PE_ETAG') : null;

    var r = call_('/v1/racing/next-to-go', params, key, etag);
    var now = Utilities.formatDate(new Date(), s.tz, 'd MMM HH:mm:ss');
    setCell_(STATUS_ROW, 'Last refresh', now);
    if (r.code === 304) {
      setCell_(STATUS_ROW + 1, 'Last refresh cost', '0 credits (nothing had changed)');
      status_('Up to date.');
      return;
    }
    if (r.code !== 200) { status_(errorText_(r)); return; }

    var races = JSON.parse(r.body);
    writeOdds_(sheet, races, s.tz);
    props.setProperty('PE_QUERY', query);
    if (r.headers['etag']) props.setProperty('PE_ETAG', r.headers['etag']);
    var cost = r.headers['x-credits-cost'];
    if (cost) props.setProperty('PE_LAST_COST', cost);
    setCell_(STATUS_ROW + 1, 'Last refresh cost', (cost || '?') + ' credits');
    if (r.headers['x-credits-remaining']) {
      setCell_(STATUS_ROW + 2, 'Credits left this month', Number(r.headers['x-credits-remaining']));
    }
    writeProjection_();
    status_(races.length ? 'Showing ' + races.length + ' races.' : 'No races match these settings right now.');
  } finally {
    lock.releaseLock();
  }
}

function writeOdds_(sheet, races, tz) {
  // One column per bookmaker that priced anything, the most widely quoting books first.
  var seen = {};
  races.forEach(function (race) {
    (race.runners || []).forEach(function (rn) {
      (rn.bookmakers || []).forEach(function (b) {
        if (b.win_price && !b.stale) seen[b.key] = (seen[b.key] || 0) + 1;
      });
    });
  });
  var books = Object.keys(seen).sort(function (a, b) { return seen[b] - seen[a] || (a < b ? -1 : 1); });
  var head = ['Start', 'Venue', 'Race', 'Type', 'No.', 'Runner', 'Best win', 'Best book']
    .concat(books.map(function (k) { return BOOK_NAMES[k] || k; }));

  var rows = [], best = [], band = [], shade = false;
  races.forEach(function (race) {
    shade = !shade;
    var start = Utilities.formatDate(new Date(race.start_time), tz, 'HH:mm');
    var scratched = {};
    (race.scratchings || []).forEach(function (x) {
      var n = (x && typeof x === 'object') ? (x.number || x.runner_number) : x;
      if (n !== undefined && n !== null) scratched[String(n)] = true;
    });
    (race.runners || []).forEach(function (rn) {
      var prices = {}, top = null;
      (rn.bookmakers || []).forEach(function (b) {
        if (!b.win_price || b.stale) return;   // a stale price is left out, never shown as live
        prices[b.key] = b.win_price;
        if (top === null || b.win_price > top) top = b.win_price;
      });
      // Every book at the top price is highlighted; the Best book cell names up to two.
      var atTop = books.filter(function (k) { return top !== null && prices[k] === top; });
      var names = atTop.map(function (k) { return BOOK_NAMES[k] || k; });
      var topBook = names.length > 2 ? names.slice(0, 2).join(', ') + ' +' + (names.length - 2)
                                     : names.join(', ');
      var scr = scratched[String(rn.number)];
      rows.push([start, race.venue || '', race.race_number || '', race.category || '',
                 rn.number || '', (rn.name || '') + (scr ? ' (scratched)' : ''),
                 scr ? '' : (top === null ? '' : top), scr ? '' : topBook]
        .concat(books.map(function (k) { return scr || prices[k] === undefined ? '' : prices[k]; })));
      best.push(scr ? [] : atTop.map(function (k) { return books.indexOf(k); }));
      band.push(shade);
    });
  });

  sheet.clear();
  sheet.getRange(1, 1, 1, head.length).setValues([head])
    .setFontWeight('bold').setBackground('#0f3d2e').setFontColor('#ffffff');
  sheet.setFrozenRows(1);
  sheet.setFrozenColumns(6);
  if (!rows.length) return;
  var body = sheet.getRange(2, 1, rows.length, head.length);
  body.setValues(rows);
  sheet.getRange(2, 7, rows.length, head.length - 6).setNumberFormat('0.00');
  sheet.getRange(2, 7, rows.length, 1).setFontWeight('bold');
  var bg = band.map(function (b) {
    var row = [];
    for (var i = 0; i < head.length; i++) row.push(b ? '#f4f7f5' : '#ffffff');
    return row;
  });
  best.forEach(function (cols, i) { cols.forEach(function (col) { bg[i][8 + col] = '#d9efe2'; }); });
  body.setBackgrounds(bg);
  sheet.autoResizeColumns(1, head.length);
}

// ---------------------------------------------------------------------------- auto ---------

function setAuto_(minutes) {
  ScriptApp.getProjectTriggers().forEach(function (t) {
    if (t.getHandlerFunction() === 'peAutoTick') ScriptApp.deleteTrigger(t);
  });
  ScriptApp.newTrigger('peAutoTick').timeBased().everyMinutes(minutes).create();
  PropertiesService.getDocumentProperties().setProperty('PE_AUTO_MIN', String(minutes));
  status_('Auto-refresh every ' + minutes + ' minutes during the hours set above.');
  writeProjection_();
  refresh_(false);
}

function inHours_(spec) {
  var m = String(spec || '').match(/^\s*(\d{1,2})\s*-\s*(\d{1,2})\s*$/);
  if (!m) return true;
  var h = Number(Utilities.formatDate(new Date(), 'Australia/Sydney', 'H'));
  return h >= Number(m[1]) && h < Number(m[2]);
}

// The most a month of auto-refresh can cost: refreshes that find nothing changed cost nothing.
function writeProjection_() {
  var props = PropertiesService.getDocumentProperties();
  var every = Number(props.getProperty('PE_AUTO_MIN') || 0);
  if (!every) { setCell_(STATUS_ROW + 3, 'Auto-refresh', 'off'); setCell_(STATUS_ROW + 4, 'At this rate, at most', ''); return; }
  var m = String(settings_().hours).match(/^\s*(\d{1,2})\s*-\s*(\d{1,2})\s*$/);
  var hours = m ? Math.max(0, Number(m[2]) - Number(m[1])) : 24;
  var cost = Number(props.getProperty('PE_LAST_COST') || 2);
  var month = Math.round(hours * 60 / every * cost * 30);
  setCell_(STATUS_ROW + 3, 'Auto-refresh', 'every ' + every + ' minutes, ' + hours + ' hours a day');
  setCell_(STATUS_ROW + 4, 'At this rate, at most', month.toLocaleString() + ' credits a month');
}

function writeUsage_(u) {
  setCell_(STATUS_ROW + 2, 'Credits left this month', u.credits_remaining);
  setCell_(STATUS_ROW + 5, 'Your plan', String(u.plan || '') + ' (' + Number(u.credits_limit || 0).toLocaleString() + ' credits a month)');
  if (u.next_reset_at) setCell_(STATUS_ROW + 6, 'Credits reset', String(u.next_reset_at).slice(0, 10));
}

// ---------------------------------------------------------------------------- helpers ------

function call_(path, params, key, etag) {
  var headers = { 'X-API-Key': key, 'X-PE-Client': PE_CLIENT };
  if (etag) headers['If-None-Match'] = etag;
  var q = qs_(params);
  var res = UrlFetchApp.fetch(PE_API + path + (q ? '?' + q : ''),
    { method: 'get', headers: headers, muteHttpExceptions: true });
  var h = {}, all = res.getAllHeaders();
  Object.keys(all).forEach(function (k) { h[k.toLowerCase()] = String(all[k]); });
  return { code: res.getResponseCode(), body: res.getContentText(), headers: h };
}

function errorText_(r) {
  var detail = '';
  try { var j = JSON.parse(r.body); detail = j.detail || j.title || ''; } catch (e) {}
  if (typeof detail !== 'string') detail = JSON.stringify(detail);
  var lead = {
    401: 'Your API key was not accepted. Use PuntersEdge > Set up to paste it again.',
    402: 'This key has used its credits for the month.',
    403: 'This key\'s plan does not include that.',
    422: 'A setting on the PuntersEdge tab was not accepted.',
    429: 'Too many requests a minute for this key. Try again shortly.'
  }[r.code] || ('The API answered HTTP ' + r.code + '.');
  return lead + (detail ? ' ' + detail : '');
}

function key_() {
  var key = PropertiesService.getUserProperties().getProperty('PE_API_KEY');
  if (!key) status_('No API key yet: choose PuntersEdge > Set up / change API key.');
  return key;
}

function settings_() {
  var sh = setupTab_();
  var v = sh.getRange(4, 2, SETTINGS.length, 1).getValues().map(function (r) { return r[0]; });
  var races = Math.max(1, Math.min(60, Number(v[2]) || 10));
  return {
    country: String(v[0] || '').replace(/\s+/g, '').toUpperCase(),
    types: String(v[1] || '').replace(/\s+/g, '').toLowerCase(),
    races: races,
    books: String(v[3] || '').replace(/\s+/g, '').toLowerCase(),
    hours: String(v[4] || ''),
    tz: String(v[5] || 'Australia/Sydney').trim() || 'Australia/Sydney'
  };
}

function setupTab_() {
  var ss = SpreadsheetApp.getActive();
  var sh = ss.getSheetByName(SETUP_TAB);
  if (sh) return sh;
  sh = ss.insertSheet(SETUP_TAB, 0);
  sh.getRange('A1').setValue('PuntersEdge odds').setFontSize(14).setFontWeight('bold');
  sh.getRange('A2').setValue('Change the yellow cells, then PuntersEdge > Refresh odds now. Prices: puntersedge.online');
  sh.getRange('A3:C3').setValues([['Setting', 'Value', 'What it does']]).setFontWeight('bold');
  sh.getRange(4, 1, SETTINGS.length, 3).setValues(SETTINGS);
  sh.getRange(4, 2, SETTINGS.length, 1).setBackground('#fff4cc');
  sh.getRange(STATUS_ROW - 1, 1).setValue('Status').setFontWeight('bold');
  sh.setColumnWidth(1, 190); sh.setColumnWidth(2, 260); sh.setColumnWidth(3, 470);
  return sh;
}

function setCell_(row, label, value) {
  var sh = setupTab_();
  sh.getRange(row, 1, 1, 2).setValues([[label, value]]);
}

function status_(text) {
  setCell_(STATUS_ROW + 7, 'Message', text);
}

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