/*** CK VIDEOS CHECKLIST — Apps Script (Sheets + Calendar) ***/
/*** VERSION: 2026-08-14a — adds ping (source identity) + save-token verify ***/

function doGet(e) {
  const p = e && e.parameter;
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('Data') || ss.insertSheet('Data');

  if (p && p.action === 'load') {
    return ContentService.createTextOutput(
      JSON.stringify({
        data: sheet.getRange('A1').getValue(),
        teamNotes: sheet.getRange('C1').getValue()
      }))
      .setMimeType(ContentService.MimeType.JSON);
  }

  // Identity check — lets the dashboard PROVE which Spreadsheet + deployment
  // it is actually talking to, and show it visibly on screen.
  if (p && p.action === 'ping') {
    return ContentService.createTextOutput(
      JSON.stringify({
        status: 'ok',
        spreadsheetId: ss.getId(),
        spreadsheetName: ss.getName(),
        spreadsheetUrl: ss.getUrl(),
        scriptId: ScriptApp.getScriptId(),
        lastSaveTs: sheet.getRange('B1').getValue(),
        serverTime: new Date().toISOString()
      }))
      .setMimeType(ContentService.MimeType.JSON);
  }

  // Double-check protocol — after a save, the dashboard polls this with the
  // exact token it just sent. A match PROVES that specific save round-tripped
  // to THIS spreadsheet (not just that "some" save happened somewhere).
  if (p && p.action === 'verify') {
    const storedToken = sheet.getRange('D1').getValue();
    return ContentService.createTextOutput(
      JSON.stringify({
        status: 'ok',
        supported: true,
        match: !!(p.token && storedToken && String(storedToken) === String(p.token)),
        storedToken: storedToken,
        spreadsheetId: ss.getId(),
        spreadsheetName: ss.getName()
      }))
      .setMimeType(ContentService.MimeType.JSON);
  }

  return ContentService.createTextOutput(JSON.stringify({ status: 'ok' }))
    .setMimeType(ContentService.MimeType.JSON);
}

function doPost(e) {
  const body = JSON.parse(e.postData.contents);
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('Data') || ss.insertSheet('Data');

  if (body.action === 'save') {
    const ex = sheet.getRange('A1').getValue();
    const exTs = sheet.getRange('B1').getValue();
    if (ex) {
      const lr = sheet.getLastRow();
      sheet.getRange('A' + (lr + 1)).setValue(ex);
      sheet.getRange('B' + (lr + 1)).setValue(exTs);
    }
    sheet.getRange('A1').setValue(body.data);
    sheet.getRange('B1').setValue(new Date().toISOString());
    if (body.teamNotes !== undefined) sheet.getRange('C1').setValue(body.teamNotes);
    // Store the per-save token so ?action=verify can prove this exact save landed.
    if (body.saveToken !== undefined) sheet.getRange('D1').setValue(body.saveToken);
    if (body.humanRows && body.humanRows.length) {
      let vs = ss.getSheetByName('Videos') || ss.insertSheet('Videos');
      vs.clearContents();
      vs.getRange(1, 1, body.humanRows.length, body.humanRows[0].length).setValues(body.humanRows);
      vs.getRange(1, 1, 1, body.humanRows[0].length).setFontWeight('bold');
      vs.setFrozenRows(1);
    }
  }

  if (body.action === 'calendar') {
    const result = upsertCalendarEvent(body);
    return ContentService.createTextOutput(JSON.stringify({ status: 'ok', cal: result }))
      .setMimeType(ContentService.MimeType.JSON);
  }

  if (body.action === 'calendar_delete') {
    const result = deleteCalendarEvent(body.itemId, body.tag);
    return ContentService.createTextOutput(JSON.stringify({ status: 'ok', cal: result }))
      .setMimeType(ContentService.MimeType.JSON);
  }

  return ContentService.createTextOutput(JSON.stringify({ status: 'ok' }))
    .setMimeType(ContentService.MimeType.JSON);
}

function upsertCalendarEvent(body) {
  if (!body.itemId) return 'no-id';
  const cal = CalendarApp.getDefaultCalendar();

  const tag = body.tag || ('ExcelSpine_' + (body.start || '').split('T')[0]);
  const legacyTag = '[CKVID:' + body.itemId + ']';

  const start = new Date(body.start);
  const end = new Date(start.getTime() + 60 * 60 * 1000);
  const title = body.title || 'CK Video';

  const purpose = body.purpose || '';
  const notes = body.notes || '';
  const visibleDesc = [purpose, notes].filter(Boolean).join('\n\n');
  const desc = (visibleDesc ? visibleDesc + '\n\n' : '') + tag;

  const searchFrom = new Date(start.getTime() - 365 * 24 * 60 * 60 * 1000);
  const searchTo   = new Date(start.getTime() + 365 * 24 * 60 * 60 * 1000);
  const events = cal.getEvents(searchFrom, searchTo);

  let deleted = 0;
  for (let i = 0; i < events.length; i++) {
    const d = events[i].getDescription() || '';
    if (d.indexOf(tag) !== -1 || d.indexOf(legacyTag) !== -1) {
      events[i].deleteEvent();
      deleted++;
    }
  }

  cal.createEvent(title, start, end, { description: desc });
  return deleted > 0 ? 'replaced' : 'created';
}

function deleteCalendarEvent(itemId, tag) {
  const cal = CalendarApp.getDefaultCalendar();
  const legacyTag = itemId ? '[CKVID:' + itemId + ']' : null;
  const now = new Date();
  const searchFrom = new Date(now.getTime() - 2 * 365 * 24 * 60 * 60 * 1000);
  const searchTo   = new Date(now.getTime() + 2 * 365 * 24 * 60 * 60 * 1000);
  const events = cal.getEvents(searchFrom, searchTo);

  let deleted = 0;
  for (let i = 0; i < events.length; i++) {
    const d = events[i].getDescription() || '';
    const matchNew = tag && d.indexOf(tag) !== -1;
    const matchLegacy = legacyTag && d.indexOf(legacyTag) !== -1;
    if (matchNew || matchLegacy) {
      events[i].deleteEvent();
      deleted++;
    }
  }
  return deleted > 0 ? 'deleted-' + deleted : 'not-found';
}

/*** ONE-TIME CLEANUP — run manually from Apps Script editor ***/
function cleanupCountOnly() { return _cleanup(false); }
function cleanupDeleteNow() { return _cleanup(true); }
function _cleanup(reallyDelete) {
  const cal = CalendarApp.getDefaultCalendar();
  const now = new Date();
  const from = new Date(now.getTime() - 2 * 365 * 24 * 60 * 60 * 1000);
  const to   = new Date(now.getTime() + 2 * 365 * 24 * 60 * 60 * 1000);
  const events = cal.getEvents(from, to);
  let hit = 0;
  for (let i = 0; i < events.length; i++) {
    const d = events[i].getDescription() || '';
    const t = events[i].getTitle() || '';
    if (d.indexOf('[CKVID:') !== -1 || d.indexOf('[CK Video]') !== -1
        || d.indexOf('ExcelSpine_') !== -1 || t.indexOf('[CK Video]') !== -1) {
      hit++;
      if (reallyDelete) events[i].deleteEvent();
    }
  }
  const msg = (reallyDelete ? 'DELETED ' : 'WOULD DELETE ') + hit + ' events.';
  Logger.log(msg);
  return msg;
}

/*** ONE-TIME DIAGNOSTIC — run manually to confirm which file this script is bound to ***/
function whichSpreadsheetAmIBoundTo() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  Logger.log('Name: ' + ss.getName());
  Logger.log('ID: ' + ss.getId());
  Logger.log('URL: ' + ss.getUrl());
  Logger.log('Script ID: ' + ScriptApp.getScriptId());
  return ss.getName() + ' | ' + ss.getId();
}