// PrepareBuddy → Google Sheets. Pulls every final result; re-marked results update their row.
var BASE = 'https://www.preparebuddy.com/api/v1';   // your PrepareBuddy address (keep the www.)

var RESULT_HEADERS = ['Result ID', 'Email', 'First name', 'Last name', 'Kind', 'Title', 'Test type',
                      'Score', 'Out of', 'Percentage', 'Completed', 'Released', 'Result link'];

function syncResults() {
  var props = PropertiesService.getScriptProperties();
  var key = props.getProperty('PREPAREBUDDY_API_KEY');
  if (!key) throw new Error('Add PREPAREBUDDY_API_KEY under Project Settings > Script properties.');
  var sheet = sheet_('Results', RESULT_HEADERS);
  var rowById = rowsById_(sheet);
  var cursor = props.getProperty('RESULTS_CURSOR') || '';
  var tz = encodeURIComponent(Session.getScriptTimeZone());

  for (var page = 0; page < 50; page++) {            // up to 25,000 results per run
    var url = BASE + '/results/?limit=500&tz=' + tz + (cursor ? '&cursor=' + encodeURIComponent(cursor) : '');
    var body = get_(url, key);
    body.results.forEach(function (r) {
      var row = [r.id, r.student.email, r.student.first_name, r.student.last_name, r.kind, r.item.title,
                 r.item.type_label || r.item.type || '', r.score, r.max_score, r.percentage,
                 r.completed_at || '', r.released_at || '', r.result_url];
      if (rowById[r.id]) {
        sheet.getRange(rowById[r.id], 1, 1, row.length).setValues([row]);   // re-marked: update in place
      } else {
        sheet.appendRow(row);
        rowById[r.id] = sheet.getLastRow();
      }
    });
    if (body.next_cursor) {
      cursor = body.next_cursor;
      props.setProperty('RESULTS_CURSOR', cursor);   // next run continues from here
    }
    if (!body.has_more) break;
  }
}

// Optional: a "Daily trend" tab — one row per student per day per test type, last 30 days.
function syncDailyTrend() {
  var key = PropertiesService.getScriptProperties().getProperty('PREPAREBUDDY_API_KEY');
  var headers = ['Date', 'Email', 'First name', 'Kind', 'Test type', 'Attempts',
                 'Average %', 'Best %', 'Average score', 'Best score', 'Out of'];
  var sheet = sheet_('Daily trend', headers);
  var body = get_(BASE + '/progress/daily/?tz=' + encodeURIComponent(Session.getScriptTimeZone()), key);
  var rows = body.days.map(function (d) {
    return [d.date, d.student.email, d.student.first_name, d.kind, d.type_label || d.type, d.attempts,
            d.average_percentage, d.best_percentage, d.average_score, d.best_score, d.max_score];
  });
  if (sheet.getLastRow() > 1) sheet.getRange(2, 1, sheet.getLastRow() - 1, headers.length).clearContent();
  if (rows.length) sheet.getRange(2, 1, rows.length, headers.length).setValues(rows);
}

// Run once: refresh both tabs every night at about 2 am.
function installNightlyRefresh() {
  ScriptApp.getProjectTriggers().forEach(function (t) { ScriptApp.deleteTrigger(t); });
  ScriptApp.newTrigger('syncResults').timeBased().everyDays(1).atHour(2).create();
  ScriptApp.newTrigger('syncDailyTrend').timeBased().everyDays(1).atHour(3).create();
}

// Run if you want to start again from the very first result.
function startOver() {
  PropertiesService.getScriptProperties().deleteProperty('RESULTS_CURSOR');
}

function get_(url, key) {
  var res = UrlFetchApp.fetch(url, {headers: {'X-API-Key': key}, muteHttpExceptions: true});
  if (res.getResponseCode() !== 200) {
    throw new Error('PrepareBuddy answered ' + res.getResponseCode() + ': ' + res.getContentText());
  }
  return JSON.parse(res.getContentText());
}

function sheet_(name, headers) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(name) || ss.insertSheet(name);
  if (sheet.getLastRow() === 0) sheet.appendRow(headers);
  return sheet;
}

function rowsById_(sheet) {
  var map = {};
  var last = sheet.getLastRow();
  if (last < 2) return map;
  sheet.getRange(2, 1, last - 1, 1).getValues().forEach(function (v, i) { map[v[0]] = i + 2; });
  return map;
}