/**
 * UPES 2026 Teaching Dashboard: Google Sheet backend (Apps Script web app).
 *
 * Setup (once):
 *  1. Paste this file as Code.gs (bound to a Sheet, or standalone: a Sheet is then created for you).
 *  2. Run setup() once (Run button). Approve the permissions. Copy the TOKEN from the log.
 *  3. Deploy > New deployment > Web app. Execute as: Me. Who has access: Anyone. Copy the /exec URL.
 *  4. In the dashboard open Settings, paste the URL and the TOKEN, press Connect.
 *  5. Optional: run installWeeklyTrigger() to get the weekly report every Saturday 18:00 by email.
 */

var TABLES = {
  Sessions: ['id', 'slotId', 'date', 'start', 'stream', 'course', 'cohort', 'status', 'covered', 'attendance', 'updatedAt'],
  Remarks:  ['id', 'slotId', 'date', 'stream', 'course', 'cohort', 'text', 'createdAt'],
  Tasks:    ['id', 'title', 'stream', 'course', 'cohort', 'due', 'priority', 'status', 'notes', 'slotId', 'createdAt', 'updatedAt', 'completedAt'],
  Reports:  ['id', 'weekStart', 'weekEnd', 'generatedAt', 'markdown'],
  Slots:    ['id', 'date', 'start', 'end', 'stream', 'course', 'cohort', 'cohorts', 'room', 'session', 'unit', 'topic', 'subtopics', 'reading', 'source']
};
var JSON_COLS = { cohorts: 1, subtopics: 1 };

/* Works bound to a Sheet or as a standalone project (then it creates its own Sheet). */
function ss_() {
  var ss = null;
  try { ss = SpreadsheetApp.getActiveSpreadsheet(); } catch (e) {}
  if (ss) return ss;
  var props = PropertiesService.getScriptProperties(), id = props.getProperty('SHEET_ID');
  if (id) { try { return SpreadsheetApp.openById(id); } catch (e) {} }
  ss = SpreadsheetApp.create('UPES 2026 Teaching Desk');
  props.setProperty('SHEET_ID', ss.getId());
  var first = ss.getSheets()[0];
  first.setName('About');
  first.getRange(1, 1, 3, 1).setValues([['UPES 2026 Teaching Desk data'], ['Synced with https://upes-2026.pages.dev'], ['Do not rename the tabs or the header row.']]);
  return ss;
}

function setup() {
  var ss = ss_();
  Object.keys(TABLES).forEach(function (name) {
    var sh = ss.getSheetByName(name) || ss.insertSheet(name);
    var head = TABLES[name];
    sh.getRange(1, 1, 1, head.length).setValues([head]).setFontWeight('bold').setBackground('#0b4f86').setFontColor('#ffffff');
    sh.setFrozenRows(1);
  });
  var props = PropertiesService.getScriptProperties();
  if (!props.getProperty('TOKEN')) props.setProperty('TOKEN', Utilities.getUuid().replace(/-/g, '').slice(0, 20));
  Logger.log('TOKEN = ' + props.getProperty('TOKEN'));
  Logger.log('SHEET = ' + ss.getUrl());
}

function json_(o) {
  return ContentService.createTextOutput(JSON.stringify(o)).setMimeType(ContentService.MimeType.JSON);
}

function authed_(token) {
  return token && token === PropertiesService.getScriptProperties().getProperty('TOKEN');
}

function sheet_(name) {
  if (!TABLES[name]) throw new Error('unknown table ' + name);
  var sh = ss_().getSheetByName(name);
  if (!sh) { setup(); sh = ss_().getSheetByName(name); }
  return sh;
}

function readTable_(name) {
  var sh = sheet_(name), head = TABLES[name], n = sh.getLastRow() - 1;
  if (n < 1) return [];
  var vals = sh.getRange(2, 1, n, head.length).getDisplayValues();
  return vals.filter(function (r) { return r[0]; }).map(function (r) {
    var o = {};
    head.forEach(function (h, i) {
      var v = r[i];
      if (JSON_COLS[h]) { try { v = v ? JSON.parse(v) : []; } catch (e) { v = []; } }
      o[h] = v;
    });
    return o;
  });
}

function upsert_(name, row) {
  var sh = sheet_(name), head = TABLES[name];
  var values = head.map(function (h) {
    var v = row[h];
    if (v === undefined || v === null) return '';
    return JSON_COLS[h] ? JSON.stringify(v) : String(v);
  });
  var n = sh.getLastRow() - 1;
  var ids = n > 0 ? sh.getRange(2, 1, n, 1).getDisplayValues().map(function (r) { return r[0]; }) : [];
  var i = ids.indexOf(String(row.id));
  var target = i >= 0 ? sh.getRange(i + 2, 1, 1, head.length) : sh.getRange(sh.getLastRow() + 1, 1, 1, head.length);
  target.setNumberFormat('@').setValues([values]);
}

function bulk_(name, rows) {
  if (!rows.length) return;
  var sh = sheet_(name), head = TABLES[name], n = sh.getLastRow() - 1;
  var ids = n > 0 ? sh.getRange(2, 1, n, 1).getDisplayValues().map(function (r) { return r[0]; }) : [];
  var fresh = [];
  rows.forEach(function (row) {
    var values = head.map(function (h) {
      var v = row[h];
      if (v === undefined || v === null) return '';
      return JSON_COLS[h] ? JSON.stringify(v) : String(v);
    });
    var i = ids.indexOf(String(row.id));
    if (i >= 0) sh.getRange(i + 2, 1, 1, head.length).setNumberFormat('@').setValues([values]);
    else { fresh.push(values); ids.push(String(row.id)); }
  });
  if (fresh.length) sh.getRange(sh.getLastRow() + 1, 1, fresh.length, head.length).setNumberFormat('@').setValues(fresh);
}

function delete_(name, id) {
  var sh = sheet_(name), n = sh.getLastRow() - 1;
  if (n < 1) return;
  var ids = sh.getRange(2, 1, n, 1).getDisplayValues().map(function (r) { return r[0]; });
  var i = ids.indexOf(String(id));
  if (i >= 0) sh.deleteRow(i + 2);
}

function all_() {
  var out = { ok: true, serverTime: new Date().toISOString() };
  Object.keys(TABLES).forEach(function (t) { out[t] = readTable_(t); });
  return out;
}

function doGet(e) {
  var p = e.parameter || {};
  if (!authed_(p.token)) return json_({ ok: false, error: 'bad token' });
  return json_(all_());
}

function doPost(e) {
  var body = {};
  try { body = JSON.parse(e.postData.contents); } catch (err) { return json_({ ok: false, error: 'bad json' }); }
  if (!authed_(body.token)) return json_({ ok: false, error: 'bad token' });
  var lock = LockService.getScriptLock();
  lock.waitLock(20000);
  try {
    (body.ops || []).forEach(function (op) {
      if (op.action === 'upsert') upsert_(op.table, op.row);
      else if (op.action === 'delete') delete_(op.table, op.id);
      else if (op.action === 'bulk') bulk_(op.table, op.rows || []);
    });
    SpreadsheetApp.flush();
  } finally {
    lock.releaseLock();
  }
  return json_(all_());
}

/* ---------- weekly report (server side, by email) ---------- */

function weeklyReport() {
  var tz = Session.getScriptTimeZone();
  var now = new Date();
  var mon = new Date(now); mon.setDate(now.getDate() - ((now.getDay() + 6) % 7)); mon.setHours(0, 0, 0, 0);
  var sun = new Date(mon); sun.setDate(mon.getDate() + 6);
  var ws = Utilities.formatDate(mon, tz, 'yyyy-MM-dd'), we = Utilities.formatDate(sun, tz, 'yyyy-MM-dd');
  var inWeek = function (d) { return d >= ws && d <= we; };

  var sessions = readTable_('Sessions').filter(function (s) { return inWeek(s.date); });
  var remarks = readTable_('Remarks').filter(function (r) { return inWeek(r.date); });
  var tasks = readTable_('Tasks');
  var open = tasks.filter(function (t) { return t.status !== 'done'; });
  var done = tasks.filter(function (t) { return t.status === 'done' && inWeek((t.completedAt || '').slice(0, 10)); });

  var md = ['# Weekly teaching report ' + ws + ' to ' + we, ''];
  md.push('## Classes logged (' + sessions.length + ')');
  sessions.sort(function (a, b) { return (a.date + a.start).localeCompare(b.date + b.start); }).forEach(function (s) {
    md.push('- ' + s.date + ' ' + s.start + ' ' + s.course + ' [' + s.cohort + '] ' + s.status + (s.covered ? ': ' + s.covered : ''));
  });
  md.push('', '## Remarks (' + remarks.length + ')');
  remarks.forEach(function (r) { md.push('- ' + r.createdAt.replace('T', ' ').slice(0, 16) + ' ' + r.course + ' [' + r.cohort + ']: ' + r.text); });
  md.push('', '## Tasks completed this week (' + done.length + ')');
  done.forEach(function (t) { md.push('- ' + t.title + ' [' + t.cohort + ']'); });
  md.push('', '## Pending tasks (' + open.length + ')');
  open.forEach(function (t) { md.push('- ' + (t.due || 'no date') + ' ' + t.priority + ' ' + t.title + ' [' + t.cohort + ']'); });
  var text = md.join('\n');

  upsert_('Reports', { id: 'auto_' + ws, weekStart: ws, weekEnd: we, generatedAt: new Date().toISOString(), markdown: text });
  MailApp.sendEmail(Session.getEffectiveUser().getEmail(), 'UPES weekly teaching report ' + ws, text);
}

function installWeeklyTrigger() {
  ScriptApp.getProjectTriggers().forEach(function (t) {
    if (t.getHandlerFunction() === 'weeklyReport') ScriptApp.deleteTrigger(t);
  });
  ScriptApp.newTrigger('weeklyReport').timeBased().onWeekDay(ScriptApp.WeekDay.SATURDAY).atHour(18).create();
}