Set it up.

/**
 * Audit Helper — Google Sheets backend for the CCH Engagement Organizer extension.
 *
 * Container-bound to the shared sheet. Run setup() once from the editor, then
 * deploy as a web app (Execute as: me, Who has access: Anyone).
 *
 * Sheets it owns: Assignments, Team, Log.
 */

/**
 * Goes up by one whenever the extension comes to need something new from this
 * file. The extension updates itself; this file is pasted by hand, so it says
 * which one it is and the extension asks for an update when it has to.
 */
var BACKEND_VERSION = 5;

var ASSIGNMENTS = 'Assignments';
var TEAM = 'Team';
var LOG = 'Log';

var HEADERS = [
  'organizerUid',
  'organizerName',
  'itemUid',
  'sectionName',
  'description',
  'cchDueDate',
  'cchState',
  'assigneeEmail',
  'assigneeName',
  'internalDueDate',
  'status',
  'notes',
  'updatedBy',
  'updatedAt',
  'notifiedAssigned',
  'notifiedReminder',
  'notifiedOverdue',
];

var TEAM_HEADERS = ['email', 'name', 'active'];
var ORGANIZER_URL = 'https://engagementorganizer.cchaxcess.com/ui/client/organizer;id=';

// CCH's own completion states, read from the Angular bundle's enum:
//   all=-1, pending=0, notApplicable=1, completed=2, accepted=3, rejected=4
var CCH = { PENDING: 0, NOT_APPLICABLE: 1, COMPLETED: 2, ACCEPTED: 3, REJECTED: 4 };

/**
 * The status we adopt when CCH moves an item into a given completion state.
 * Returns null for states we deliberately do not mirror.
 */
function statusForCchState(state) {
  var s = Number(state);
  if (s === CCH.COMPLETED) return 'Submitted';
  if (s === CCH.NOT_APPLICABLE) return 'Done';
  // The auditor sent it back, so it needs rework. Putting it back in play is
  // what re-arms the reminder and digest emails.
  if (s === CCH.REJECTED) return 'In Progress';
  return null;
}

// ---------------------------------------------------------------- one-time setup

/**
 * Creates the tabs, generates the shared secret, and installs the email triggers.
 * Safe to re-run: it will not clobber existing data or rotate an existing secret.
 */
function setup() {
  var ss = SpreadsheetApp.getActive();

  ensureSheet(ss, ASSIGNMENTS, HEADERS);
  var team = ensureSheet(ss, TEAM, TEAM_HEADERS);
  ensureSheet(ss, LOG, ['when', 'event', 'detail']);
  ensureSettings(ss);

  if (team.getLastRow() < 2) {
    team.getRange(2, 1, 1, 3).setValues([['', 'leave blank to use the CCH client roster', false]]);
  }

  var props = PropertiesService.getScriptProperties();
  var secret = props.getProperty('SHARED_SECRET');
  if (!secret) {
    secret = Utilities.getUuid().replace(/-/g, '') + Utilities.getUuid().replace(/-/g, '');
    props.setProperty('SHARED_SECRET', secret);
  }
  if (!props.getProperty('REMINDER_DAYS')) props.setProperty('REMINDER_DAYS', '3');
  if (!props.getProperty('DIGEST_WEEKDAY')) props.setProperty('DIGEST_WEEKDAY', 'MONDAY');

  installTriggers();

  Logger.log('=======================================================');
  Logger.log("SHARED SECRET (paste into the extension's settings):");
  Logger.log(secret);
  Logger.log('=======================================================');
  Logger.log('Now: Deploy > New deployment > Web app');
  Logger.log('  Execute as: Me      Who has access: Anyone');

  showConnection();
}

/**
 * The shared secret, in the sheet, so nobody has to find it in the execution
 * log. Does nothing when run from the editor, which has no sheet to draw on.
 * Anyone who can open this menu can already read the script properties.
 */
function showConnection() {
  var ui;
  try {
    ui = SpreadsheetApp.getUi();
  } catch (_) {
    return;
  }

  var secret = PropertiesService.getScriptProperties().getProperty('SHARED_SECRET');
  if (!secret) {
    ui.alert('Audit Helper is not set up on this sheet yet. Choose Audit Helper > Set up first.');
    return;
  }

  var html =
    '<div style="font:13px/1.5 Arial,sans-serif;color:#23303d">' +
    '<p style="margin:0 0 4px"><b>Shared secret</b></p>' +
    '<input readonly onclick="this.select()" value="' +
    esc(secret) +
    '" style="width:100%;box-sizing:border-box;padding:6px;font:12px monospace">' +
    '<p>Everyone on the team pastes this into the extension, with the web app URL. ' +
    'Treat the two together as a password.</p>' +
    '<p style="margin-bottom:4px"><b>The web app URL</b></p>' +
    '<p style="margin-top:0">First time: in the Apps Script editor, choose ' +
    'Deploy &gt; New deployment &gt; Web app, with Execute as <b>Me</b> and ' +
    'Who has access <b>Anyone</b>.<br>' +
    'Already deployed: Deploy &gt; Manage deployments shows it. It ends in /exec.</p>' +
    '<p><a href="https://applauseconsulting.com/audit-helper/setup/" target="_blank">Setup guide</a></p>' +
    '</div>';

  ui.showModalDialog(HtmlService.createHtmlOutput(html).setWidth(460).setHeight(330), 'Audit Helper');
}

/**
 * Touches every service the manifest declares a scope for, so running it from
 * the editor forces the authorization prompt on purpose. Run this when Google
 * has dropped the grant (symptom: every extension action fails with "You do
 * not have permission to call SpreadsheetApp.getActive") or after a scope
 * change. Web-app requests and triggers run as the deployer, so re-authorizing
 * here fixes them too; no redeploy needed.
 */
function diagnostics() {
  var ss = SpreadsheetApp.getActive();
  Logger.log('Sheet: ' + ss.getName() + ' (' + ss.getUrl() + ')');
  Logger.log('Running as: ' + Session.getEffectiveUser().getEmail());
  Logger.log('Mail quota left today: ' + MailApp.getRemainingDailyQuota());
  Logger.log('Trigger owner: ' + (PropertiesService.getScriptProperties().getProperty('TRIGGER_OWNER') ||
    'not claimed yet; run installTriggers()'));
  Logger.log(
    'Your triggers: ' +
      ScriptApp.getProjectTriggers()
        .map(function (t) {
          return t.getHandlerFunction();
        })
        .join(', ')
  );
  Logger.log('Shared secret set: ' + !!PropertiesService.getScriptProperties().getProperty('SHARED_SECRET'));
  Logger.log('All services reachable; the web app and triggers are authorized.');
}

var TRIGGER_HANDLERS = { dailyJob: true, weeklyDigest: true, flushOutbox: true };

/**
 * Triggers belong to whoever creates them, and getProjectTriggers() only sees
 * the caller's own, so an editor clicking the menu used to add a second set
 * that nobody else could see or delete: every digest went out twice, from
 * their account. The first account to install claims TRIGGER_OWNER; anyone
 * else is refused. To hand the schedule to someone new, delete TRIGGER_OWNER
 * in Project Settings > Script Properties and have them run installTriggers().
 */
function installTriggers() {
  var props = PropertiesService.getScriptProperties();
  var me = Session.getEffectiveUser().getEmail();
  var owner = props.getProperty('TRIGGER_OWNER');
  if (!me) throw new Error('Cannot tell which account is running this, so no triggers were installed.');
  if (owner && !sameEmail(owner, me)) {
    var msg = 'The mail schedule belongs to ' + owner + '. Ask them to apply the settings; ' +
      'installing it under your account as well would send every email twice.';
    try {
      SpreadsheetApp.getUi().alert(msg);
    } catch (_) {
      Logger.log(msg);
    }
    return;
  }
  if (!owner) props.setProperty('TRIGGER_OWNER', me);

  // Reached from the sheet menu as well as setup(), and someone applying
  // settings expects the tab to be there afterwards.
  ensureSettings(SpreadsheetApp.getActive());

  var s = readSettings();

  deleteOwnTriggers();

  // A trigger's schedule is fixed when it is created, so sendHour and
  // weeklyDigestDay only move once this runs again — hence the sheet menu.
  var hour = Math.floor(Number(s.sendhour));
  if (!(hour >= 0 && hour <= 23)) hour = 7;

  ScriptApp.newTrigger('dailyJob').timeBased().atHour(hour).everyDays(1).create();

  // Assignment emails are sent here rather than inline, so a save doesn't wait
  // on MailApp. flushOutbox itself holds mail outside working hours.
  ScriptApp.newTrigger('flushOutbox').timeBased().everyMinutes(15).create();

  var day = String(s.weeklydigestday || 'MONDAY').trim().toUpperCase();
  if (!ScriptApp.WeekDay[day]) day = 'MONDAY';
  ScriptApp.newTrigger('weeklyDigest')
    .timeBased()
    .onWeekDay(ScriptApp.WeekDay[day])
    .atHour(hour)
    .create();
}

/** Deletes this project's mail triggers owned by the running account; nobody else's are visible. */
function deleteOwnTriggers() {
  ScriptApp.getProjectTriggers().forEach(function (t) {
    if (TRIGGER_HANDLERS[t.getHandlerFunction()]) ScriptApp.deleteTrigger(t);
  });
}

/**
 * Called first by each trigger handler with its event object. A run fired by a
 * trigger owned by anyone but TRIGGER_OWNER deletes that account's triggers,
 * logs who it was, and returns true so the handler sends nothing. This is how
 * a stray set cleans itself up without its owner doing anything: their own
 * trigger is the one place their triggers can be seen from. Manual runs
 * (no triggerUid) and runs before anyone has claimed ownership are left alone.
 */
function isStrayTrigger(e) {
  if (!e || !e.triggerUid) return false;
  var owner = PropertiesService.getScriptProperties().getProperty('TRIGGER_OWNER');
  if (!owner) return false;
  var me = Session.getEffectiveUser().getEmail();
  if (!me || sameEmail(owner, me)) return false;
  deleteOwnTriggers();
  console.log('Removed triggers owned by ' + me + '; the schedule belongs to ' + owner);
  log('stray-triggers-removed', me);
  return true;
}

/** Sheet menu, so setup and the schedule settings can be reached without the editor. */
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Audit Helper')
    .addItem('Set up', 'setup')
    .addItem('Show connection details', 'showConnection')
    .addSeparator()
    .addItem('Apply settings (reschedules the mail)', 'installTriggers')
    .addItem('Send daily digest now', 'dailyDigest')
    .addSeparator()
    .addItem('🔑 Repair Authorization', 'repairAuth')
    .addToUi();
}

/**
 * Menu: 🔑 Repair Authorization (last item). Asks Google for any scope this
 * user's grant is missing: "You do not have permission to call ...", or a
 * scope added to appsscript.json that never prompted. Logs the granted
 * scopes; when nothing is missing it says so in a toast, otherwise
 * requireAllScopes() stops the run and shows the consent screen for just the
 * missing scopes. It only adds to the grant, so it needs no confirmation and
 * no owner check. Never call ScriptApp.invalidateAuth() here (best practices
 * §34): the grant is shared by every script on the same Cloud project, so a
 * revoke signs the person out of all of them.
 */
function repairAuth() {
  var info = ScriptApp.getAuthorizationInfo(ScriptApp.AuthMode.FULL);
  var granted = (info.getAuthorizedScopes() || []).map(function (s) {
    return s.replace('https://www.googleapis.com/auth/', '');
  });
  Logger.log('repairAuth: granted = ' + (granted.length ? granted.join(', ') : '(none)'));
  if (info.getAuthorizationStatus() === ScriptApp.AuthorizationStatus.NOT_REQUIRED) {
    SpreadsheetApp.getActiveSpreadsheet().toast('Every permission is granted. Nothing to repair.', 'Repair Authorization', 8);
    return;
  }
  Logger.log('repairAuth: missing scopes; showing the consent screen (or open ' + info.getAuthorizationUrl() + ')');
  ScriptApp.requireAllScopes(ScriptApp.AuthMode.FULL); // stops here and shows Google's prompt
}

function ensureSheet(ss, name, headers) {
  var sh = ss.getSheetByName(name);
  if (!sh) sh = ss.insertSheet(name);
  if (sh.getLastRow() === 0) {
    sh.getRange(1, 1, 1, headers.length).setValues([headers]).setFontWeight('bold');
    sh.setFrozenRows(1);
  }
  return sh;
}

// ------------------------------------------------------------------- web app

function doGet() {
  return json({
    ok: true,
    message: 'Audit Helper backend is deployed. Use POST.',
    backendVersion: BACKEND_VERSION,
  });
}

/** What every reply carries about the script itself, whatever was asked. */
function withMeta(reply) {
  reply.backendVersion = BACKEND_VERSION;
  // The send window is in this zone, and the extension says when mail will go.
  reply.timeZone = Session.getScriptTimeZone();
  return reply;
}

function doPost(e) {
  try {
    var req = JSON.parse((e && e.postData && e.postData.contents) || '{}');

    if (!checkToken(req.token)) return json({ ok: false, error: 'bad token' });

    switch (req.action) {
      case 'ping':
        // sheetUrl travels with the name because the extension has no way to
        // work it out: a deployment id says nothing about the file behind it.
        return json(
          withMeta({
            ok: true,
            sheetName: SpreadsheetApp.getActive().getName(),
            sheetUrl: SpreadsheetApp.getActive().getUrl(),
          })
        );
      case 'sync':
        return json(withMeta(handleSync(req)));
      case 'assign':
        return json(withMeta(handleAssign(req)));
      case 'notifyState':
        return json(handleNotifyState(req));
      default:
        return json({ ok: false, error: 'unknown action: ' + req.action });
    }
  } catch (err) {
    log('error', String(err && err.stack ? err.stack : err));
    return json({ ok: false, error: String(err) });
  }
}

function json(obj) {
  return ContentService.createTextOutput(JSON.stringify(obj)).setMimeType(
    ContentService.MimeType.JSON
  );
}

// Apps Script reuses a script instance across consecutive requests, so holding
// the secret in module scope skips a PropertiesService round trip per call.
// After rotating the secret, the old value can survive for as long as a warm
// instance lives (seconds to a few minutes).
var CACHED_SECRET = null;

/** Length-independent comparison so a wrong token leaks nothing via timing. */
function checkToken(supplied) {
  if (CACHED_SECRET === null) {
    CACHED_SECRET = PropertiesService.getScriptProperties().getProperty('SHARED_SECRET') || '';
  }
  var expected = CACHED_SECRET;
  supplied = String(supplied || '');
  if (!expected) return false;
  var diff = supplied.length ^ expected.length;
  for (var i = 0; i < Math.max(supplied.length, expected.length); i++) {
    diff |= supplied.charCodeAt(i % (supplied.length || 1)) ^ expected.charCodeAt(i % expected.length);
  }
  return diff === 0;
}

// -------------------------------------------------------------------- actions

/**
 * Upserts the current request-item list for an organizer, then returns every
 * assignment row for it plus the effective team roster.
 */
function handleSync(req) {
  var lock = LockService.getScriptLock();
  lock.waitLock(25000);
  try {
    var sh = SpreadsheetApp.getActive().getSheetByName(ASSIGNMENTS);
    var rows = readAll(sh);
    var index = indexRows(rows);
    var appended = [];
    var mirrored = [];
    var changed = [];
    var fresh = [];
    var now = new Date();

    (req.items || []).forEach(function (item) {
      var existing = index[key(req.organizerUid, item.itemUid)];
      if (existing) {
        var beforeRow = rowSignature(existing);
        // The auditor owns these four columns; refresh them, leave ours alone.
        existing.organizerName = req.organizerName || existing.organizerName;
        existing.sectionName = item.sectionName;
        existing.description = item.description;
        existing.cchDueDate = item.cchDueDate;

        // Mirror CCH's completion state onto our status, but only on an actual
        // transition. Doing it unconditionally would stomp a manual edit every
        // time we sync; this way, whoever touched it last wins until CCH moves.
        var before = existing.cchState;
        existing.cchState = item.cchState;
        if (String(before) !== String(item.cchState)) {
          var mapped = statusForCchState(item.cchState);
          if (mapped && existing.status !== mapped) {
            existing.status = mapped;
            existing.updatedBy = 'CCH';
            existing.updatedAt = now;
            // Coming back into play (a rejection) has to clear the "already
            // emailed" stamps, or an item reminded about before it was submitted
            // would never be reminded about again.
            if (isOpen(existing)) {
              existing.notifiedReminder = '';
              existing.notifiedOverdue = '';
            }
            mirrored.push(item.description + ' -> ' + mapped);
          }
        }
        // Almost every sync changes nothing at all. Only touch the sheet for
        // rows that actually moved.
        if (rowSignature(existing) !== beforeRow) changed.push(existing);
      } else {
        var row = blank();
        row.organizerUid = req.organizerUid;
        row.organizerName = req.organizerName || '';
        row.itemUid = item.itemUid;
        row.sectionName = item.sectionName;
        row.description = item.description;
        row.cchDueDate = item.cchDueDate;
        row.cchState = item.cchState;
        // An item already Completed/Not Applicable in CCH before we ever saw it
        // should land on the mapped status, not "Open".
        row.status = statusForCchState(item.cchState) || 'Open';
        row.updatedAt = now;
        rows.push(row);
        fresh.push(row);
        index[key(req.organizerUid, item.itemUid)] = row;
        appended.push(item.itemUid);
      }
    });

    changed.forEach(function (r) {
      writeRow(sh, r);
    });
    if (fresh.length) appendRows(sh, fresh);

    if (appended.length) log('sync', req.organizerUid + ': added ' + appended.length + ' item(s)');
    if (mirrored.length) log('cch-state', mirrored.join('; '));

    var mine = rows.filter(function (r) {
      return r.organizerUid === req.organizerUid;
    });

    return { ok: true, assignments: mine.map(outbound), team: effectiveTeam(req.roster) };
  } finally {
    lock.releaseLock();
  }
}

function handleAssign(req) {
  var rec = req.record || {};
  if (!rec.organizerUid || !rec.itemUid) return { ok: false, error: 'missing organizerUid/itemUid' };

  var lock = LockService.getScriptLock();
  lock.waitLock(25000);

  var row;
  var notify = false;
  try {
    var sh = SpreadsheetApp.getActive().getSheetByName(ASSIGNMENTS);
    var rows = readAll(sh);
    var index = indexRows(rows);

    row = index[key(rec.organizerUid, rec.itemUid)];
    var isNew = !row;
    if (isNew) {
      row = blank();
      row.organizerUid = rec.organizerUid;
      row.itemUid = rec.itemUid;
      row.description = rec.description || '';
      rows.push(row);
    }

    var previousAssignee = row.assigneeEmail;

    row.organizerName = rec.organizerName || row.organizerName;
    row.assigneeEmail = rec.assigneeEmail || '';
    row.assigneeName = rec.assigneeName || '';
    row.internalDueDate = rec.internalDueDate || '';
    row.status = rec.status || 'Open';
    row.notes = rec.notes || '';
    row.updatedBy = rec.actor || '';
    row.updatedAt = new Date();

    // Re-arm the reminder/overdue emails whenever the assignee or date moves.
    if (row.assigneeEmail !== previousAssignee || rec.internalDueDate) {
      row.notifiedReminder = '';
      row.notifiedOverdue = '';
    }

    if (row.assigneeEmail && row.assigneeEmail !== previousAssignee) {
      // Empty stamp == "needs an assignment email". flushOutbox picks it up.
      row.notifiedAssigned = '';
      notify = true;
    }

    // One row, one write. Rewriting the whole range here was the single biggest
    // cost in this request.
    if (isNew) appendRows(sh, [row]);
    else writeRow(sh, row);
  } finally {
    lock.releaseLock();
  }

  // Deliberately NOT sending mail here. MailApp costs about a second, and the
  // user is waiting on this response to see their own chip update. flushOutbox
  // runs on a 15-minute trigger and does the sending.
  if (notify) queueOutbox();

  return { ok: true, record: outbound(row) };
}

/**
 * Reports what became of the assignment emails for a handful of items.
 *
 * Extensions up to 0.4.2 call this about a minute after a save, because
 * handleAssign cannot answer the question: flushOutbox has not run yet when it
 * returns. Later versions say when the mail will go instead; keep this until
 * nobody is on 0.4.2.
 * Deliberately narrow — one read, no writes, and only the fields the caller
 * needs, rather than making it re-sync the whole organizer to learn one thing.
 *
 * No lock: this reads and never writes, and a row is always written in a single
 * setValues, so the worst case is reading a row one flush behind.
 */
function handleNotifyState(req) {
  var wanted = {};
  (req.itemUids || []).forEach(function (uid) {
    wanted[uid] = true;
  });

  var sh = SpreadsheetApp.getActive().getSheetByName(ASSIGNMENTS);

  var states = readAll(sh)
    .filter(function (r) {
      return r.organizerUid === req.organizerUid && wanted[r.itemUid];
    })
    .map(function (r) {
      return {
        itemUid: r.itemUid,
        assigneeEmail: r.assigneeEmail,
        assigneeName: r.assigneeName,
        notifyState: notifyState(r),
        notifyError: notifyError(r),
      };
    });

  return { ok: true, states: states };
}

/**
 * What happened to this row's assignment email, from the stamp flushOutbox
 * leaves behind: a Date once it went out, 'retry:N' between attempts, 'failed'
 * once it has given up, empty while it is still queued.
 *
 * @return {string} 'sent' | 'pending' | 'failed' | 'none'
 */
function notifyState(row) {
  if (!row.assigneeEmail) return 'none';
  var stamp = String(row.notifiedAssigned || '');
  if (stamp.indexOf('failed') === 0) return 'failed';
  if (stamp === '' || stamp.indexOf('retry:') === 0) return 'pending';
  return 'sent';
}

/**
 * The reason a row's assignment email gave up, trimmed for a toast; empty when
 * the row has not failed (or predates the stamp carrying the text). Used by
 * handleNotifyState so the extension can tell the user what actually went
 * wrong instead of guessing at the cause.
 */
function notifyError(row) {
  var stamp = String(row.notifiedAssigned || '');
  if (stamp.indexOf('failed|') !== 0) return '';
  return clipText(stamp.slice(7), 160);
}

/** Apps Script errors stringify as 'Exception: ...'; strip that and clip. */
function clipText(s, n) {
  s = String(s || '').replace(/^Exception:\s*/, '').trim();
  return s.length > n ? s.slice(0, n - 1) + '…' : s;
}

// ------------------------------------------------------------------ row model

function blank() {
  var o = {};
  HEADERS.forEach(function (h) {
    o[h] = '';
  });
  return o;
}

function key(organizerUid, itemUid) {
  return organizerUid + ' ' + itemUid;
}

/** Cheap change detector so we can skip writing rows that did not move. */
function rowSignature(row) {
  return HEADERS.map(function (h) {
    return row[h] instanceof Date ? row[h].getTime() : String(row[h]);
  }).join('
');
}

function toArray(row) {
  return HEADERS.map(function (h) {
    return row[h];
  });
}

/** Writes a single row in place. Requires _sheetRow, set by readAll/appendRows. */
function writeRow(sh, row) {
  if (!row._sheetRow) throw new Error('writeRow: row has no _sheetRow');
  sh.getRange(row._sheetRow, 1, 1, HEADERS.length).setValues([toArray(row)]);
}

/** Appends rows in one call and stamps each with the row number it landed on. */
function appendRows(sh, rows) {
  if (!rows.length) return;
  var start = sh.getLastRow() + 1;
  sh.getRange(start, 1, rows.length, HEADERS.length).setValues(rows.map(toArray));
  rows.forEach(function (r, i) {
    r._sheetRow = start + i;
  });
}

function readAll(sh) {
  var last = sh.getLastRow();
  if (last < 2) return [];
  var values = sh.getRange(2, 1, last - 1, HEADERS.length).getValues();
  return values
    .map(function (r, i) {
      var o = { _sheetRow: i + 2 };
      HEADERS.forEach(function (h, j) {
        o[h] = r[j];
      });
      return o;
    })
    .filter(function (o) {
      return o.itemUid;
    });
}

function indexRows(rows) {
  var index = {};
  rows.forEach(function (r) {
    index[key(r.organizerUid, r.itemUid)] = r;
  });
  return index;
}

/** Dates come back from the sheet as Date objects; the extension wants ISO days. */
function outbound(row) {
  var o = {};
  HEADERS.forEach(function (h) {
    o[h] = row[h] instanceof Date ? iso(row[h]) : row[h];
  });
  return o;
}

function iso(d) {
  return Utilities.formatDate(d, Session.getScriptTimeZone(), 'yyyy-MM-dd');
}

function today() {
  return iso(new Date());
}

// ---------------------------------------------------------------- mail outbox
//
// Assignment emails are queued rather than sent inline: MailApp costs roughly a
// second, and the user is waiting on the HTTP response to see their own edit
// land. A row with an assignee and an empty notifiedAssigned stamp *is* the
// queue — no separate table to keep in step.

/** Assignment mail goes out 8am-5pm, Monday to Friday, script time zone. */
var WORK_START_HOUR = 8;
var WORK_END_HOUR = 17;

function isWorkingHours(date) {
  var h = date.getHours();
  return isSendDay('weekdays', date) && h >= WORK_START_HOUR && h < WORK_END_HOUR;
}

/** Marks that there is mail to send, so the outbox trigger can no-op cheaply. */
function queueOutbox() {
  PropertiesService.getScriptProperties().setProperty('OUTBOX', '1');
}

/** The queue is exactly the rows notifyState calls pending. One definition, two readers. */
function needsAssignEmail(row) {
  return notifyState(row) === 'pending';
}

/**
 * Sends queued assignment emails. Runs every 15 minutes; outside working hours
 * it returns before touching anything, and the flag check keeps an idle run in
 * hours down to a single PropertiesService read.
 *
 * @param {boolean} [force] ignore the flag. Strictly `true`: the trigger passes
 *   its event object here, which must not count.
 * @param {Date} [now] the time being run for — tests pass one in
 */
function flushOutbox(force, now) {
  now = now || new Date();
  // Mail queued in the evening or at the weekend waits, flag and all, for the
  // first run of the next working morning.
  if (!isWorkingHours(now)) return;

  var props = PropertiesService.getScriptProperties();
  if (force !== true && props.getProperty('OUTBOX') !== '1') return;
  // After the flag check so an idle run stays at one read; dailyJob re-flags
  // every morning, so a stray set still meets this within a working day.
  if (isStrayTrigger(force)) return;

  // Cleared before the scan, so an assignment arriving mid-flush re-flags and
  // gets picked up next run rather than being swallowed.
  props.deleteProperty('OUTBOX');

  var sh = SpreadsheetApp.getActive().getSheetByName(ASSIGNMENTS);
  var all = readAll(sh);
  var rows = all.filter(needsAssignEmail);
  if (!rows.length) return;

  rows.forEach(function (row) {
    try {
      sendAssignment(row, all);
      row.notifiedAssigned = new Date();
    } catch (err) {
      // Retry a couple of times for transient failures, then give up — a bad
      // address would otherwise be retried every run forever. The stamp
      // carries the error text so the extension can tell the user the real
      // reason ('failed|You do not have permission...') rather than guessing.
      var stamp = String(row.notifiedAssigned || '');
      var attempt = stamp.indexOf('retry:') === 0 ? Number(stamp.slice(6)) + 1 : 1;
      row.notifiedAssigned = attempt >= 3 ? 'failed|' + clipText(String(err), 160) : 'retry:' + attempt;
      log('mail-error', 'assign ' + row.itemUid + ' (attempt ' + attempt + '): ' + err);
    }
    writeRow(sh, row);
  });
}

/** The Team tab overrides the CCH roster when it has active entries. */
function effectiveTeam(cchRoster) {
  var sh = SpreadsheetApp.getActive().getSheetByName(TEAM);
  var manual = [];
  if (sh && sh.getLastRow() > 1) {
    sh.getRange(2, 1, sh.getLastRow() - 1, 3)
      .getValues()
      .forEach(function (r) {
        if (r[0] && String(r[0]).indexOf('@') > 0 && r[2] !== false) {
          manual.push({ email: String(r[0]).trim(), name: String(r[1] || r[0]).trim() });
        }
      });
  }
  return manual.length ? manual : cchRoster || [];
}

function log(event, detail) {
  try {
    SpreadsheetApp.getActive().getSheetByName(LOG).appendRow([new Date(), event, detail]);
  } catch (_) {}
}

// --------------------------------------------------------------------- emails

function organizerLink(row) {
  return ORGANIZER_URL + String(row.organizerUid || '').replace(/-/g, '');
}

function due(row) {
  return row.internalDueDate instanceof Date ? iso(row.internalDueDate) : String(row.internalDueDate || '');
}

function isOpen(row) {
  return row.status !== 'Done' && row.status !== 'Submitted';
}

function byDueDate(a, b) {
  return (due(a) || '9999').localeCompare(due(b) || '9999');
}

/** Case-insensitive address match, so a sheet typed in mixed case still finds its rows. */
function sameEmail(a, b) {
  return String(a || '').trim().toLowerCase() === String(b || '').trim().toLowerCase();
}

/** One line of an item list, with the overdue flag and the status, for use in any mail. */
function itemLine(r, t) {
  var d = due(r);
  var late = d && d < t;
  return (
    '<li>' +
    (late ? '<b style="color:#a3231b">OVERDUE</b> ' : '') +
    esc(r.description) +
    ' — <i>' +
    esc(r.sectionName) +
    '</i>' +
    (d ? ' — due ' + esc(d) : ' — no due date') +
    ' — ' +
    esc(String(r.status || 'Open')) +
    '</li>'
  );
}

/** Every mail ends with both places the reader might want to go next. */
function linksFooter(row) {
  var organizer = row ? organizerLink(row) : ORGANIZER_URL.replace(/;id=$/, '');
  return (
    '<p><a href="' +
    organizer +
    '">Open the Engagement Organizer</a><br>' +
    '<a href="' +
    SpreadsheetApp.getActive().getUrl() +
    '">Open the tracker sheet</a></p>'
  );
}

/**
 * "Your open items" — every non-complete row assigned to this address, sorted
 * by due date. Empty string when there are none, so a mail about a single item
 * does not gain a heading with nothing under it.
 */
function openItemsFor(email, rows, t, heading) {
  var mine = rows
    .filter(function (r) {
      return isOpen(r) && sameEmail(r.assigneeEmail, email);
    })
    .sort(byDueDate);
  if (!mine.length) return '';
  return (
    '<h3>' +
    (heading || 'All your open items') +
    ' (' +
    mine.length +
    ')</h3><ul>' +
    mine
      .map(function (r) {
        return itemLine(r, t);
      })
      .join('') +
    '</ul>'
  );
}

/** The tail shared by every mail to one person: their open list, then the links. */
function personalFooter(email, rows, t, row) {
  return openItemsFor(email, rows, t) + linksFooter(row);
}

/**
 * For a mail that goes to several addresses at once: one "open items" block per
 * recipient who has any, each labelled, so the reader can find their own.
 */
function recipientsFooter(emails, rows, t, row) {
  return (
    emails
      .map(function (email) {
        return openItemsFor(email, rows, t, 'Open items assigned to ' + esc(email));
      })
      .join('') + linksFooter(row)
  );
}

function sendAssignment(row, all) {
  if (!row.assigneeEmail) return;
  var d = due(row);
  MailApp.sendEmail({
    noReply: true,
    to: row.assigneeEmail,
    subject: 'Audit request assigned to you: ' + row.description,
    htmlBody:
      '<p>' +
      esc(row.updatedBy || 'Someone') +
      ' assigned you an audit request item.</p>' +
      '<p><b>' +
      esc(row.description) +
      '</b><br>' +
      'Section: ' +
      esc(row.sectionName) +
      '<br>' +
      'Internal due date: ' +
      (d ? esc(d) : 'not set') +
      '<br>' +
      "Auditor's due date: " +
      esc(String(row.cchDueDate || 'n/a')) +
      '</p>' +
      (row.notes ? '<p>Notes: ' + esc(row.notes) + '</p>' : '') +
      personalFooter(row.assigneeEmail, all || [row], today(), row),
  });
}

/**
 * Daily trigger: upcoming-due reminders and overdue nudges, weekdays only.
 *
 * @param {Object} [e] the trigger's event object; unused
 * @param {Date} [now] the day being run for — tests pass one in
 */
function dailyJob(e, now) {
  if (isStrayTrigger(e)) return;
  now = now || new Date();

  // Sheets that predate the Settings tab grow one on the first run after this
  // ships, rather than waiting for someone to re-run setup().
  ensureSettings(SpreadsheetApp.getActive());

  // Nobody works the request list at the weekend, so nudging them then is noise.
  // An item that goes overdue on Saturday gets its nudge on Monday; the outbox
  // safety net below still runs, since assignment mail is not a daily update.
  if (isSendDay('weekdays', now)) sendReminders();

  // Safety net: if a flushOutbox run died after clearing the flag, assignment
  // mail would sit unsent. Re-flag rather than flush, since this runs before
  // working hours; it costs the day's first outbox run one sheet read.
  queueOutbox();

  // Last, so it reports on the reminders this run just sent.
  dailyDigest(now);
}

/** The per-person half of dailyJob: "due soon" once, "overdue" once a day. */
function sendReminders() {
  var reminderDays = Number(readSettings().reminderdays);
  if (!(reminderDays >= 0)) reminderDays = 3;
  var sh = SpreadsheetApp.getActive().getSheetByName(ASSIGNMENTS);
  var rows = readAll(sh);
  var t = today();
  var horizon = iso(new Date(Date.now() + reminderDays * 86400000));

  rows.forEach(function (row) {
    var d = due(row);
    if (!row.assigneeEmail || !d || !isOpen(row)) return;

    if (d < t) {
      // Nudge at most once a day.
      if (row.notifiedOverdue instanceof Date && iso(row.notifiedOverdue) === t) return;
      try {
        MailApp.sendEmail({
          noReply: true,
          to: row.assigneeEmail,
          subject: 'OVERDUE: ' + row.description,
          htmlBody:
            '<p>This audit request item was due <b>' +
            esc(d) +
            '</b> and is still marked <b>' +
            esc(String(row.status || 'Open')) +
            '</b>.</p><p>' +
            esc(row.description) +
            ' (' +
            esc(row.sectionName) +
            ')</p>' +
            personalFooter(row.assigneeEmail, rows, t, row),
        });
        row.notifiedOverdue = new Date();
        writeRow(sh, row);
      } catch (err) {
        log('mail-error', 'overdue ' + row.itemUid + ': ' + err);
      }
      return;
    }

    if (reminderDays && d <= horizon && !(row.notifiedReminder instanceof Date)) {
      try {
        MailApp.sendEmail({
          noReply: true,
          to: row.assigneeEmail,
          subject: 'Due ' + d + ': ' + row.description,
          htmlBody:
            '<p>Reminder — this audit request item is due <b>' +
            esc(d) +
            '</b>.</p><p>' +
            esc(row.description) +
            ' (' +
            esc(row.sectionName) +
            ')</p>' +
            personalFooter(row.assigneeEmail, rows, t, row),
        });
        row.notifiedReminder = new Date();
        writeRow(sh, row);
      } catch (err) {
        log('mail-error', 'reminder ' + row.itemUid + ': ' + err);
      }
    }
  });
}

/** Weekly trigger: per-person open-item digest, plus a roll-up to whoever runs this. */
function weeklyDigest(e) {
  if (isStrayTrigger(e)) return;
  var rollupTo = settingList(readSettings().weeklydigestto);
  var rows = readAll(SpreadsheetApp.getActive().getSheetByName(ASSIGNMENTS));
  var t = today();
  var byPerson = {};

  rows.forEach(function (row) {
    if (!row.assigneeEmail || !isOpen(row)) return;
    (byPerson[row.assigneeEmail] = byPerson[row.assigneeEmail] || []).push(row);
  });

  var rollup = [];

  Object.keys(byPerson).forEach(function (email) {
    var mine = byPerson[email].sort(byDueDate);
    var overdue = mine.filter(function (r) {
      return due(r) && due(r) < t;
    }).length;

    rollup.push(esc(email) + ': ' + mine.length + ' open, ' + overdue + ' overdue');

    try {
      MailApp.sendEmail({
        noReply: true,
        to: email,
        subject: 'Your audit items this week (' + mine.length + ' open' +
          (overdue ? ', ' + overdue + ' overdue' : '') + ')',
        htmlBody:
          '<p>Your open audit request items:</p><ul>' +
          mine
            .map(function (r) {
              return itemLine(r, t);
            })
            .join('') +
          '</ul>' +
          linksFooter(mine[0]),
      });
    } catch (err) {
      log('mail-error', 'digest ' + email + ': ' + err);
    }
  });

  var unassigned = rows.filter(function (r) {
    return !r.assigneeEmail && isOpen(r);
  }).length;

  var rollupRecipients = rollupTo.length ? rollupTo : [Session.getEffectiveUser().getEmail()];

  try {
    MailApp.sendEmail({
      noReply: true,
      to: rollupRecipients.join(','),
      subject: 'Audit status roll-up',
      htmlBody:
        '<p>Open items by person:</p><ul><li>' +
        (rollup.join('</li><li>') || 'nobody has open items') +
        '</li></ul><p><b>' +
        unassigned +
        '</b> open item(s) are still unassigned.</p>' +
        recipientsFooter(rollupRecipients, rows, t, rows[0]),
    });
  } catch (err) {
    log('mail-error', 'rollup: ' + err);
  }
}

// ------------------------------------------------------------------ settings
//
// One tab, three columns, so whoever runs this can change who gets the reports
// and when, without opening the script editor.
//
// The line drawn here: mail *about your own work* (assignment, due soon,
// overdue, your weekly list) goes to the person doing it — there is nobody else
// it could sensibly go to, so it is not configurable. Mail that *reports on
// everyone* (the daily digest, the weekly roll-up) has no natural recipient, so
// it is. A blank recipients cell turns that report off.

var SETTINGS = 'Settings';
var SETTING_HEADERS = ['setting', 'value', 'what it does'];

var SETTING_DEFAULTS = [
  [
    'dailyDigestTo',
    '',
    'Who gets the daily activity digest. Comma-separated. Blank turns it off. Starts as whoever ran setup.',
  ],
  [
    'dailyDigestDays',
    'weekdays',
    'weekdays, or daily. Little moves on the request list at the weekend.',
  ],
  [
    'weeklyDigestTo',
    '',
    'Who gets the weekly roll-up of everyone. Blank means whoever ran setup.',
  ],
  ['weeklyDigestDay', 'MONDAY', 'MONDAY ... SUNDAY. Needs Audit Helper > Apply settings.'],
  ['sendHour', '7', 'Hour of day (0-23) the scheduled mail goes out. Needs Apply settings.'],
  ['reminderDays', '3', 'Days before your internal due date to send "due soon". 0 turns it off.'],
];

/**
 * Creates the tab if it is missing and seeds it, carrying over the two values
 * that used to live in script properties so an existing sheet keeps behaving the
 * way it did before this tab existed.
 */
function ensureSettings(ss) {
  var sh = ensureSheet(ss, SETTINGS, SETTING_HEADERS);
  if (sh.getLastRow() < 2) {
    var props = PropertiesService.getScriptProperties();
    var legacy = {
      reminderDays: props.getProperty('REMINDER_DAYS'),
      weeklyDigestDay: props.getProperty('DIGEST_WEEKDAY'),
    };
    // The digest reports on everyone, so it starts with the one person known to
    // be running this sheet. The default itself stays blank: a sheet with no tab
    // yet must not mail anybody it was never told about.
    var seed = { dailyDigestTo: Session.getEffectiveUser().getEmail() };
    var seeded = SETTING_DEFAULTS.map(function (d) {
      return [d[0], legacy[d[0]] || seed[d[0]] || d[1], d[2]];
    });
    sh.getRange(2, 1, seeded.length, 3).setValues(seeded);
  }
  return sh;
}

/**
 * Every setting, keyed lowercase, defaults filled in. Read once per job rather
 * than cached in module scope: an edit to the tab should take effect on the next
 * run, not whenever a warm instance happens to be recycled.
 *
 * A blank cell beats the default — that is how a report gets turned off.
 */
function readSettings() {
  var out = {};
  SETTING_DEFAULTS.forEach(function (d) {
    out[d[0].toLowerCase()] = d[1];
  });

  // Deployed before the tab existed? The old script properties still apply.
  var props = PropertiesService.getScriptProperties();
  if (props.getProperty('REMINDER_DAYS')) out.reminderdays = props.getProperty('REMINDER_DAYS');
  if (props.getProperty('DIGEST_WEEKDAY')) out.weeklydigestday = props.getProperty('DIGEST_WEEKDAY');

  var sh = SpreadsheetApp.getActive().getSheetByName(SETTINGS);
  if (sh && sh.getLastRow() > 1) {
    sh.getRange(2, 1, sh.getLastRow() - 1, 2)
      .getValues()
      .forEach(function (r) {
        var k = String(r[0] || '').trim().toLowerCase();
        if (k) out[k] = String(r[1] == null ? '' : r[1]).trim();
      });
  }
  return out;
}

/** Splits a recipients cell. Anything without an @ is a note to a human, not an address. */
function settingList(value) {
  return String(value || '')
    .split(/[,;\s]+/)
    .filter(function (s) {
      return s.indexOf('@') > 0;
    });
}

/** @param {Date} date the day being considered, passed in so this stays testable */
function isSendDay(mode, date) {
  if (String(mode || '').trim().toLowerCase() !== 'weekdays') return true;
  var d = date.getDay();
  return d >= 1 && d <= 5;
}

// --------------------------------------------------------------- daily digest

/**
 * What moved yesterday and what is on fire, for the people running the
 * engagement rather than working the items.
 *
 * Silent on a genuinely quiet day — nothing changed, nothing overdue, nothing
 * unassigned. A daily mail that says "nothing happened" every day is a daily
 * mail nobody reads by the second week.
 */
function dailyDigest(now) {
  // The menu item calls this with no argument; anything but a Date means today.
  if (Object.prototype.toString.call(now) !== '[object Date]') now = new Date();

  var s = readSettings();
  var to = settingList(s.dailydigestto);
  if (!to.length) return;
  if (!isSendDay(s.dailydigestdays, now)) return;

  var rows = readAll(SpreadsheetApp.getActive().getSheetByName(ASSIGNMENTS));
  var t = today();

  // On a weekdays schedule Monday's digest picks up where Friday's left off, so
  // whatever moved over the weekend is not lost between two 24-hour windows.
  var day = 24 * 3600 * 1000;
  var sinceFriday = now.getDay() === 1 && !isSendDay(s.dailydigestdays, new Date(now.getTime() - day));
  var cutoff = new Date(now.getTime() - (sinceFriday ? 3 : 1) * day);

  var changed = rows
    .filter(function (r) {
      return r.updatedAt instanceof Date && r.updatedAt >= cutoff;
    })
    .sort(byDueDate);

  var overdue = rows
    .filter(function (r) {
      return isOpen(r) && due(r) && due(r) < t;
    })
    .sort(byDueDate);

  var unassigned = rows.filter(function (r) {
    return isOpen(r) && !r.assigneeEmail;
  });

  if (!changed.length && !overdue.length && !unassigned.length) return;

  var perPerson = {};
  rows.forEach(function (r) {
    if (!isOpen(r) || !r.assigneeEmail) return;
    var who = r.assigneeName || r.assigneeEmail;
    perPerson[who] = (perPerson[who] || 0) + 1;
  });

  var line = function (r) {
    var d = due(r);
    return (
      '<li>' +
      esc(r.description) +
      ' — <i>' +
      esc(r.sectionName) +
      '</i>' +
      (r.organizerName ? ' — ' + esc(r.organizerName) : '') +
      '<br><small>' +
      esc(r.assigneeName || r.assigneeEmail || 'unassigned') +
      ' — ' +
      esc(String(r.status || 'Open')) +
      (d ? ' — due ' + esc(d) : ' — no due date') +
      (r.updatedBy ? ' — last touched by ' + esc(r.updatedBy) : '') +
      '</small></li>'
    );
  };

  var section = function (title, items) {
    if (!items.length) return '';
    return '<h3>' + title + '</h3><ul>' + items.map(line).join('') + '</ul>';
  };

  var counts = Object.keys(perPerson)
    .sort()
    .map(function (who) {
      return '<li>' + esc(who) + ': ' + perPerson[who] + ' open</li>';
    });

  var body =
    section(
      (sinceFriday ? 'Changed since Friday (' : 'Changed in the last day (') + changed.length + ')',
      changed
    ) +
    section('Overdue (' + overdue.length + ')', overdue) +
    (counts.length ? '<h3>Open work by person</h3><ul>' + counts.join('') + '</ul>' : '') +
    (unassigned.length
      ? '<p><b>' + unassigned.length + '</b> open item(s) still have nobody on them.</p>'
      : '') +
    recipientsFooter(to, rows, t, rows[0]);

  try {
    MailApp.sendEmail({
      noReply: true,
      to: to.join(','),
      subject:
        'Audit Helper daily — ' +
        changed.length +
        ' change(s)' +
        (overdue.length ? ', ' + overdue.length + ' overdue' : ''),
      htmlBody: body,
    });
    log(
      'daily-digest',
      to.join(', ') + ': ' + changed.length + ' change(s), ' + overdue.length + ' overdue'
    );
  } catch (err) {
    log('mail-error', 'daily digest: ' + err);
  }
}

function esc(s) {
  return String(s == null ? '' : s).replace(/[&<>"]/g, function (c) {
    return { '&': '&amp;', '<': '&lt;', '>': '&gt;', '"': '&quot;' }[c];
  });
}

{
  "timeZone": "America/Chicago",
  "dependencies": {},
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8",
  "oauthScopes": [
    "https://www.googleapis.com/auth/spreadsheets.currentonly",
    "https://www.googleapis.com/auth/script.send_mail",
    "https://www.googleapis.com/auth/script.scriptapp",
    "https://www.googleapis.com/auth/script.container.ui",
    "https://www.googleapis.com/auth/userinfo.email"
  ],
  "webapp": {
    "executeAs": "USER_DEPLOYING",
    "access": "ANYONE_ANONYMOUS"
  }
}

{"ok":true,"message":"Audit Helper backend is deployed.