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.
 */

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 options):');
  Logger.log(secret);
  Logger.log('=======================================================');
  Logger.log('Now: Deploy > New deployment > Web app');
  Logger.log('  Execute as: Me      Who has access: Anyone');
}

function installTriggers() {
  // 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();

  var keep = { dailyJob: true, weeklyDigest: true, flushOutbox: true };
  ScriptApp.getProjectTriggers().forEach(function (t) {
    if (keep[t.getHandlerFunction()]) ScriptApp.deleteTrigger(t);
  });

  // 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 an assignment lands
  // within a minute instead of adding a second to every save.
  ScriptApp.newTrigger('flushOutbox').timeBased().everyMinutes(1).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();
}

/** Sheet menu, so the two schedule settings can be applied without the editor. */
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Audit Helper')
    .addItem('Apply settings (reschedules the mail)', 'installTriggers')
    .addItem('Send daily digest now', 'dailyDigest')
    .addToUi();
}

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.' });
}

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({
          ok: true,
          sheetName: SpreadsheetApp.getActive().getName(),
          sheetUrl: SpreadsheetApp.getActive().getUrl(),
        });
      case 'sync':
        return json(handleSync(req));
      case 'assign':
        return json(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 one-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.
 *
 * The extension calls this about a minute after a save, because handleAssign
 * cannot answer the question: flushOutbox has not run yet when it returns.
 * 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),
      };
    });

  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 === 'failed') return 'failed';
  if (stamp === '' || stamp.indexOf('retry:') === 0) return 'pending';
  return 'sent';
}

// ------------------------------------------------------------------ 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.

/** Marks that there is mail to send, so the minute 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 minute; the flag check keeps an
 * idle run down to a single PropertiesService read.
 *
 * @param {boolean} [force] ignore the flag — used by dailyJob as a safety net in
 *   case a crashed run stranded the queue.
 */
function flushOutbox(force) {
  var props = PropertiesService.getScriptProperties();
  if (!force && props.getProperty('OUTBOX') !== '1') return;

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

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

  rows.forEach(function (row) {
    try {
      sendAssignment(row);
      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 minute forever.
      var stamp = String(row.notifiedAssigned || '');
      var attempt = stamp.indexOf('retry:') === 0 ? Number(stamp.slice(6)) + 1 : 1;
      row.notifiedAssigned = attempt >= 3 ? 'failed' : '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 sendAssignment(row) {
  if (!row.assigneeEmail) return;
  var d = due(row);
  MailApp.sendEmail({
    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>' +
      'Our 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>' : '') +
      '<p><a href="' +
      organizerLink(row) +
      '">Open the organizer</a></p>',
  });
}

/** Daily trigger: upcoming-due reminders and overdue nudges. */
function dailyJob() {
  // 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());

  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({
          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><p><a href="' +
            organizerLink(row) +
            '">Open the organizer</a></p>',
        });
        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({
          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><p><a href="' +
            organizerLink(row) +
            '">Open the organizer</a></p>',
        });
        row.notifiedReminder = new Date();
        writeRow(sh, row);
      } catch (err) {
        log('mail-error', 'reminder ' + row.itemUid + ': ' + err);
      }
    }
  });

  // Safety net: if a flushOutbox run died after clearing the flag, assignment
  // mail would sit unsent. Costs one read on the days there is nothing to do.
  flushOutbox(true);

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

/** Weekly trigger: per-person open-item digest, plus a roll-up to whoever runs this. */
function weeklyDigest() {
  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(function (a, b) {
      return (due(a) || '9999').localeCompare(due(b) || '9999');
    });
    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({
        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) {
              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>'
              );
            })
            .join('') +
          '</ul><p><a href="' +
          organizerLink(mine[0]) +
          '">Open the organizer</a></p>',
      });
    } catch (err) {
      log('mail-error', 'digest ' + email + ': ' + err);
    }
  });

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

  try {
    MailApp.sendEmail({
      to: rollupTo.length ? rollupTo.join(',') : Session.getEffectiveUser().getEmail(),
      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>',
    });
  } 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',
    '[email protected], [email protected]',
    'Who gets the daily activity digest. Comma-separated. Blank turns it off.',
  ],
  [
    'dailyDigestDays',
    'weekdays',
    'weekdays, or daily. Little moves on the request list at the weekend.',
  ],
  [
    'weeklyDigestTo',
    '',
    'Who gets the Monday roll-up of everyone. Blank means the sheet owner.',
  ],
  ['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'),
    };
    var seeded = SETTING_DEFAULTS.map(function (d) {
      return [d[0], legacy[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() {
  var s = readSettings();
  var to = settingList(s.dailydigestto);
  if (!to.length) return;
  if (!isSendDay(s.dailydigestdays, new Date())) return;

  var rows = readAll(SpreadsheetApp.getActive().getSheetByName(ASSIGNMENTS));
  var t = today();
  var cutoff = new Date(Date.now() - 24 * 3600 * 1000);

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

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

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

  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('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>'
      : '') +
    (rows.length ? '<p><a href="' + organizerLink(rows[0]) + '">Open the organizer</a></p>' : '');

  try {
    MailApp.sendEmail({
      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"
  }
}