/**
 * Absence Alerts
 * A free absence notification system for daycares, preschools, camps, and youth programs.
 *
 * How it works:
 *   1. A program copies the template Sheet and fills in students and contacts.
 *   2. The menu builds a Google Form for daily attendance (no sign-in needed).
 *   3. When a student is checked as absent, each family contact on file
 *      gets an individual alert email from the program's own account.
 *
 * Runs entirely in the program's own Google account. Email delivery depends
 * on Google and each recipient's mail provider, and is subject to Google's
 * sending quotas.
 *
 * Setup guide and limitations: https://drlevidruin.github.io/absence-alerts/
 * Questions or feedback: levidruin@pardesdayschool.org
 *
 * MIT License. Provided free and as is, with no warranty and no liability
 * of any kind. This is a safety net, not a guarantee. It depends on
 * attendance being submitted and on emails being read.
 */

var SITE_URL = 'https://drlevidruin.github.io/absence-alerts/';
var MENU_NAME = '✅ Absence Alerts';
var APP_VERSION = '2.4.0';
var SCHEMA_VERSION = 2;
var TAB_START = 'Start Here';
var TAB_README = 'Read Me';
var TAB_STUDENTS = 'Students';
var TAB_LOG = 'Email Log';
var PROP_FORM_ID = 'FORM_ID';
var PROP_PAUSED = 'ALERTS_PAUSED';
var PROP_ACTIVE_OWNER_TOKEN = 'ACTIVE_OWNER_TOKEN';
var PROP_ACTIVE_SUBMIT_UID = 'ACTIVE_SUBMIT_TRIGGER_UID';
var PROP_ACTIVE_MORNING_UID = 'ACTIVE_MORNING_TRIGGER_UID';
var PROP_ACTIVE_MORNING_SCHEDULE = 'ACTIVE_MORNING_SCHEDULE';
var PROP_LAST_REMINDER_KEY = 'LAST_REMINDER_KEY';
var PROP_APP_VERSION = 'APP_VERSION';
var PROP_SCHEMA_VERSION = 'SCHEMA_VERSION';
var QUESTION_PREFIX = 'Who is absent from ';
var QUESTION_SUFFIX = ' today?';
var LEGACY_QUESTION_PREFIX = 'ABSENT today: ';
var CLASS_QUESTION = 'Which class or group are you checking?';
var TAKEN_BY_QUESTION = 'Taken by (optional)';
var NOTES_PREFIX = 'Notes for office: ';
var LEGACY_NOTES_QUESTION = 'Notes for the office (optional)';
var DEFAULT_CLASS = 'All Students';
var SAMPLE_MARKER = '(SAMPLE';
var DEFAULT_ACCENT = '#10653c';
var QUOTA_RESERVE = 3; // keep headroom for failure notices to the office
var MAX_LOG_TEXT = 1500;
var TZ_FALLBACK = 'America/New_York';
var DANGER_RED = '#b42318';
var PROP_OFFICE_NOTICE_COUNT = 'OFFICE_NOTICE_COUNT';
var PROP_URGENT_NOTICE_COUNT = 'URGENT_NOTICE_COUNT';
var PROP_STAFF_URL_CACHE = 'STAFF_URL_CACHE';
var PROP_EDIT_URL_CACHE = 'EDIT_URL_CACHE';
// Anyone with the unlisted staff link can submit, and every rejected or failed
// submission emails the office. Cap those notices per local day so a leaked
// link cannot flood the office inbox or burn the mail quota that real family
// alerts need. Family alert emails are never subject to this cap.
var OFFICE_NOTICE_DAILY_CAP = 20;
// Urgent notices, the ones that say a family was NOT reached, are counted
// against their own separate budget. Sharing one budget meant that ordinary
// chatter (an office note on each of twenty submissions, the morning reminder)
// could exhaust the cap by mid-morning and then silently swallow the email
// telling the office that a child's family had not been contacted.
var URGENT_NOTICE_DAILY_CAP = 40;
// Apps Script kills an execution at 6 minutes. Stop starting new sends before
// that wall so the remaining work is logged and reported instead of vanishing.
var SEND_TIME_BUDGET_MS = 270000;
// A busy 8 AM can queue several class submissions behind one lock. Waiting is
// cheap and dropping a submission is not, so submissions wait patiently.
var LOCK_WAIT_SUBMIT_MS = 120000;
var LOG_HEADERS = ['Timestamp', 'Date', 'Student', 'Class or group', 'Sent to', 'Status', 'Details', 'Taken by', 'Texted'];

// Families the office must text by hand are never marked SENT. The ledger has
// to say plainly that a human still owes this family a message, and stay that
// way until a human ticks the box.
var STATUS_TEXT_NEEDED = 'TEXT NEEDED';
// A text delivered through the program's own Twilio account. Kept distinct
// from SENT (email) so the ledger reads honestly, and from TEXT NEEDED so
// nothing red asks a human to redo work the machine already did.
var STATUS_TEXT_SENT = 'TEXT SENT';
// Twilio credentials live in Script Properties, never in a sheet cell: the
// Auth Token can send texts on the program's bill, and the spreadsheet is
// routinely shared with staff. Script Properties stay with the owner account.
var PROP_TWILIO_SID = 'TWILIO_ACCOUNT_SID';
var PROP_TWILIO_TOKEN = 'TWILIO_AUTH_TOKEN';
var PROP_TWILIO_FROM = 'TWILIO_FROM';
var PROP_TWILIO_CC = 'TWILIO_DEFAULT_COUNTRY';
var TWILIO_API_BASE = 'https://api.twilio.com/2010-04-01/Accounts/';
var CONTACT_EMAIL = 'Email';
var CONTACT_TEXT = 'Text';
var CONTACT_BOTH = 'Email and text';
var CONTACT_CHOICES = [CONTACT_EMAIL, CONTACT_TEXT, CONTACT_BOTH];

var HELP_CLASS = 'Choose one group. The next screen shows only that group.';
var HELP_TAKEN_BY = 'Your name or initials, so the office knows who took attendance. Optional.';
var HELP_PAGE = 'Check every student who is absent. Leave the list empty if everyone is here.';
var HELP_ABSENT = 'Check one or more absent students. Leave this empty if everyone is here. Submitting immediately emails every checked student\u2019s family.';
var HELP_NOTES = 'Optional. This note is logged and emailed only to the office.';

// Fixed cells on the Start Here tab
var CELL_SETTINGS = 'B4:B10';
var CELL_STAFF_LINK = 'B18';
var CELL_EDIT_LINK = 'B19';
var CELL_STATUS = 'B21';

// Colors for the template tabs
var C_NAVY = '#16243d';
var C_GOLD = '#c9a227';
var C_GREEN = '#10653c';
var C_GREEN_DARK = '#0a4a2b';
var C_INPUT = '#fffdf4';
var C_SAGE = '#eef4ef';
var C_HEADER = '#f1efe8';
var C_GRAY = '#5a6170';
var C_GRID = '#c9cfd8';      // visible table grid on white
var C_LABEL_FILL = '#f8f7f2'; // warm paper for label cells
var C_SECTION_FILL = '#eef4ef'; // sage section headers

// ---------------------------------------------------------------------------
// Menu
// ---------------------------------------------------------------------------

function onOpen() {
  try { repairLinkCellsFromCache_(SpreadsheetApp.getActiveSpreadsheet()); } catch (ignored) {}
  SpreadsheetApp.getUi()
    .createMenu(MENU_NAME)
    .addItem('Open setup helper', 'showSetupAssistant')
    .addSeparator()
    .addItem('Build or update system', 'setup')
    .addItem('Send preview email', 'testAlert')
    .addItem('Set up automatic texts', 'showTextingSetup')
    .addSeparator()
    .addItem('Run system check', 'runSystemCheck')
    .addItem('Replace a shared/exposed staff link', 'rotateStaffForm')
    .addItem('Pause alerts', 'pauseAlerts')
    .addItem('Resume alerts', 'resumeAlerts')
    .addSeparator()
    // No standalone design refresh: people read it as a reset button and
    // feared losing their setup. Build or update system does that repair now.
    .addItem('Help and setup guide', 'showHelp')
    .addToUi();
}

function showSetupAssistant() {
  ensureTabs_(SpreadsheetApp.getActiveSpreadsheet());
  var html = HtmlService.createHtmlOutputFromFile('SetupAssistant')
    .setTitle('Absence Alerts setup');
  SpreadsheetApp.getUi().showSidebar(html);
}

function showHelp() {
  var html = HtmlService.createHtmlOutput(
    '<div style="font-family:Arial,sans-serif;font-size:14px;line-height:1.6;padding:8px;">' +
    '<p><b>Full setup guide, limitations, and the complete code:</b></p>' +
    '<p><a href="' + SITE_URL + '" target="_blank">' + SITE_URL + '</a></p>' +
    '<p>Quick version: open the setup helper from the Absence Alerts menu. ' +
    'It asks for two program details, checks the Students tab, and gives you one build button. ' +
    'Setup works best on a computer, not a phone.</p>' +
    '</div>'
  ).setWidth(420).setHeight(220);
  SpreadsheetApp.getUi().showModalDialog(html, 'Absence Alerts Help');
}

// ---------------------------------------------------------------------------
// Pure helpers (exercised by test/test.js in the repo)
// ---------------------------------------------------------------------------

function isValidEmail_(s) {
  if (!s) return false;
  return /^[^\s@]+@[^\s@]+\.[^\s@]{2,}$/.test(String(s).trim());
}

function esc_(s) {
  return String(s)
    .replace(/&/g, '&amp;')
    .replace(/</g, '&lt;')
    .replace(/>/g, '&gt;')
    .replace(/"/g, '&quot;')
    .replace(/'/g, '&#39;');
}

// Dial string from a phone value; anything after "ext"/"x"/"#" is an
// extension and must not end up in the tel: link.
function telDigits_(phone) {
  var main = String(phone || '').split(/ext\.?|extension|#|(?:^|[\s.,;])x(?=[\s.\d])/i)[0];
  return main.replace(/[^+\d]/g, '');
}

// Turn a Google Drive share link (or a plain image URL) into a URL that
// renders inside an email. Handles /file/d/ID, ?id=ID, /d/ID, or a raw id.
function driveImageUrl_(link) {
  var s = String(link || '').trim();
  if (!s) return '';
  if (/^https?:\/\/[^\s]+\.(png|jpe?g|gif|webp)(\?|#|$)/i.test(s)) return s;
  var id = '';
  var m = s.match(/\/file\/d\/([a-zA-Z0-9_-]+)/) ||
          s.match(/[?&]id=([a-zA-Z0-9_-]+)/) ||
          s.match(/\/d\/([a-zA-Z0-9_-]+)/);
  if (m) id = m[1];
  else if (/^[a-zA-Z0-9_-]{20,}$/.test(s)) id = s;
  if (!id) return '';
  return 'https://lh3.googleusercontent.com/d/' + id + '=w320';
}

function normalizeColor_(s) {
  var t = String(s || '').trim();
  if (/^#?[0-9a-fA-F]{6}$/.test(t)) return (t.charAt(0) === '#' ? t : '#' + t).toLowerCase();
  return DEFAULT_ACCENT;
}

// Relative luminance and WCAG contrast, so button text is always the more
// readable of white or navy against whatever accent the program picks.
function relLum_(hex) {
  var h = String(hex || '').replace('#', '');
  if (h.length !== 6) return 0;
  function ch(x) { var v = parseInt(x, 16) / 255; return v <= 0.03928 ? v / 12.92 : Math.pow((v + 0.055) / 1.055, 2.4); }
  return 0.2126 * ch(h.substr(0, 2)) + 0.7152 * ch(h.substr(2, 2)) + 0.0722 * ch(h.substr(4, 2));
}

function contrastRatio_(a, b) {
  var la = relLum_(a), lb = relLum_(b);
  var hi = Math.max(la, lb), lo = Math.min(la, lb);
  return (hi + 0.05) / (lo + 0.05);
}

// Pick navy or white text, whichever reads better on the accent color.
function readableText_(hex) {
  return contrastRatio_(hex, '#16243d') >= contrastRatio_(hex, '#ffffff') ? '#16243d' : '#ffffff';
}

function cleanText_(value, maxLen) {
  var s = String(value === null || value === undefined ? '' : value)
    .replace(/[\u0000\u0008\u000B\u000C]/g, '')
    .trim();
  var limit = maxLen || MAX_LOG_TEXT;
  return s.length > limit ? s.substring(0, limit - 1) + '\u2026' : s;
}

// Names, classes, and program details are single-line values. A cell newline
// (Alt+Enter) must never reach an email subject, a Form choice, or a sender
// name, where line breaks read as header-injection attempts.
function singleLine_(value, maxLen) {
  var flat = String(value === null || value === undefined ? '' : value)
    .replace(/[\r\n\t]+/g, ' ')
    .replace(/ {2,}/g, ' ');
  return cleanText_(flat, maxLen);
}

// Sheet values beginning with these characters can be interpreted as formulas.
// Prefixing an apostrophe keeps anonymous Form text inert in the audit log.
function safeCellText_(value, maxLen) {
  var s = cleanText_(value, maxLen);
  return /^[=+\-@]/.test(s) ? "'" + s : s;
}

function normalizeKeyText_(value) {
  var s = cleanText_(value, 500).replace(/\s+/g, ' ');
  try { s = s.normalize('NFKC'); } catch (ignored) {}
  return s.toLowerCase();
}

function groupRosterByClass_(roster) {
  var order = [];
  var byClass = {};
  roster.forEach(function (r) {
    var key = normalizeKeyText_(r.cls);
    if (!byClass[key]) { byClass[key] = { cls: r.cls, names: [] }; order.push(key); }
    byClass[key].names.push(r.name);
  });
  return order.map(function (key) { return byClass[key]; });
}

function dedupKey_(name, cls) {
  return JSON.stringify([normalizeKeyText_(name), normalizeKeyText_(cls)]);
}

function deliveryKey_(name, cls, email) {
  return JSON.stringify([normalizeKeyText_(name), normalizeKeyText_(cls), normalizeKeyText_(email)]);
}

function parseClassFromTitle_(title) {
  var value = String(title || '');
  if (value.indexOf(QUESTION_PREFIX) === 0 && value.slice(-QUESTION_SUFFIX.length) === QUESTION_SUFFIX) {
    return value.substring(QUESTION_PREFIX.length, value.length - QUESTION_SUFFIX.length).trim();
  }
  if (value.indexOf(LEGACY_QUESTION_PREFIX) === 0) {
    return value.substring(LEGACY_QUESTION_PREFIX.length).trim();
  }
  return null;
}

function isWeekend_(day) {
  return day === 0 || day === 6;
}

// Sheets auto-converts a written "2026-07-17" into a real date value, so a
// log cell can come back as either a string or a Date. Normalize both.
function normalizeDateCell_(v, tz) {
  if (v && typeof v.getFullYear === 'function') {
    return Utilities.formatDate(v, tz, 'yyyy-MM-dd');
  }
  return String(v).trim();
}

function desiredFormStructure_(roster) {
  var groups = groupRosterByClass_(roster);
  var spec = [{
    // MultipleChoiceItem is intentional: unlike ListItem, Google exposes its
    // choice destinations through Choice.getGotoPage(), so routing can be
    // fingerprinted and verified instead of merely assumed.
    type: 'MULTIPLE_CHOICE', title: CLASS_QUESTION, help: HELP_CLASS, required: true,
    choices: groups.map(function (g) { return { value: g.cls, goto: g.cls }; })
  }];
  groups.forEach(function (g) {
    spec.push({ type: 'PAGE_BREAK', title: g.cls, help: HELP_PAGE, navigation: 'SUBMIT' });
    spec.push({ type: 'CHECKBOX', title: QUESTION_PREFIX + g.cls + QUESTION_SUFFIX, help: HELP_ABSENT, required: false, choices: g.names.slice() });
  });
  return spec;
}

// Exact match, used to verify a Form THIS code just rebuilt, where item order
// is deterministic. Live drift detection must use formStructuresEquivalent_.
function formStructureMatches_(actual, expected) {
  return JSON.stringify(actual) === JSON.stringify(expected);
}

// Order-insensitive fingerprint. Sorting or reordering the Students tab
// changes first-seen group order but not the safety contract, and must never
// take the system down. Question titles, help text, required flags, routing
// targets, and exact choice membership are still enforced.
function canonicalFormFingerprint_(spec) {
  var classItem = null;
  var pages = {};
  var checkboxes = {};
  var extras = [];
  (spec || []).forEach(function (item) {
    var type = String(item.type || '');
    if (type === 'MULTIPLE_CHOICE' && String(item.title || '') === CLASS_QUESTION && !classItem) {
      classItem = item;
      return;
    }
    if (type === 'PAGE_BREAK') {
      pages[normalizeKeyText_(item.title)] = { title: item.title, help: item.help || '', navigation: item.navigation || '' };
      return;
    }
    if (type === 'CHECKBOX') {
      checkboxes[normalizeKeyText_(item.title)] = {
        title: item.title,
        help: item.help || '',
        required: !!item.required,
        choices: (item.choices || []).slice().sort()
      };
      return;
    }
    extras.push({ type: type, title: item.title || '' });
  });
  var classChoices = (classItem ? (classItem.choices || []) : []).map(function (choice) {
    return { value: choice.value, goto: choice.goto };
  }).sort(function (a, b) {
    var ka = normalizeKeyText_(a.value);
    var kb = normalizeKeyText_(b.value);
    return ka < kb ? -1 : ka > kb ? 1 : 0;
  });
  return JSON.stringify({
    classItem: classItem
      ? { title: classItem.title, help: classItem.help || '', required: !!classItem.required, choices: classChoices }
      : null,
    pages: Object.keys(pages).sort().map(function (key) { return pages[key]; }),
    checkboxes: Object.keys(checkboxes).sort().map(function (key) { return checkboxes[key]; }),
    extras: extras
  });
}

function formStructuresEquivalent_(actual, expected) {
  return canonicalFormFingerprint_(actual) === canonicalFormFingerprint_(expected);
}

function missingClasses_(expected, covered) {
  var coveredSet = {};
  covered.forEach(function (c) { coveredSet[normalizeKeyText_(c)] = true; });
  return expected.filter(function (c) { return !coveredSet[normalizeKeyText_(c)]; });
}

function reminderKey_(date) {
  // One successful watchdog email per spreadsheet-local day. The missing list
  // is still included in the email/log, but cannot create duplicate reminders.
  return String(date);
}

function shouldRunMorningReminder_(configuredHour, currentHour) {
  if (configuredHour === null || configuredHour === undefined || configuredHour === '') return false;
  var configured = Number(configuredHour);
  var current = Number(currentHour);
  return isFinite(configured) && isFinite(current) && configured === current;
}

function morningScheduleKey_(configuredHour, timezone) {
  if (configuredHour === null || configuredHour === undefined || configuredHour === '') return 'OFF';
  return JSON.stringify({ hour: Number(configuredHour), timezone: String(timezone || '') });
}

function submissionLifecycleProblems_(snapshot) {
  var problems = [];
  if (snapshot.paused) problems.push('alerts are paused');
  if (!snapshot.ownerToken) problems.push('the active automation-owner token is missing');
  if (!triggerUidMatches_(snapshot.actualTriggerUid, snapshot.expectedTriggerUid)) problems.push('the active submit-trigger generation changed');
  if (!sourceIdMatches_(snapshot.actualFormId, snapshot.expectedFormId)) problems.push('the active attendance Form changed');
  return problems;
}

function acceptedClassesFromLogRows_(rows, today, tz, expectedClasses) {
  var expected = {};
  (expectedClasses || []).forEach(function (cls) { expected[normalizeKeyText_(cls)] = cls; });
  var seen = {};
  var covered = [];
  (rows || []).forEach(function (row) {
    if (normalizeDateCell_(row[1], tz) !== today) return;
    if (String(row[5] || '') !== 'RECEIVED') return;
    if (normalizeKeyText_(row[2]) !== '(attendance)') return;
    var canonical = expected[normalizeKeyText_(row[3])];
    if (!canonical || seen[normalizeKeyText_(canonical)]) return;
    seen[normalizeKeyText_(canonical)] = true;
    covered.push(canonical);
  });
  return covered;
}

function idsMatch_(actual, expected) {
  return !!actual && !!expected && String(actual) === String(expected);
}

function triggerUidMatches_(actual, expected) {
  return idsMatch_(actual, expected);
}

function sourceIdMatches_(actual, expected) {
  return idsMatch_(actual, expected);
}

// The Email Log is the dedup and coverage ledger. If it is renamed or its
// columns move, dedup reads silently return nothing and families can be
// double-alerted, so runtime validation fails closed on ledger shape.
function logHeaderProblems_(headerRow) {
  for (var i = 0; i < LOG_HEADERS.length; i++) {
    var actual = String((headerRow || [])[i] === undefined || (headerRow || [])[i] === null ? '' : headerRow[i]).trim();
    if (actual !== LOG_HEADERS[i]) {
      return ['The Email Log header row changed. Column ' + (i + 1) + ' should be "' + LOG_HEADERS[i] +
        '" but is "' + actual + '". Choose Absence Alerts, then Build or update system, to restore the ledger columns.'];
    }
  }
  return [];
}

function logSheetProblems_(ss) {
  var sh = ss.getSheetByName(TAB_LOG);
  if (!sh) {
    return ['The Email Log tab is missing. It is the audit and dedup ledger. Choose Absence Alerts, then Build or update system, to restore it.'];
  }
  return logHeaderProblems_(sh.getRange(1, 1, 1, LOG_HEADERS.length).getValues()[0]);
}

// Pure planning for the send loop: every recipient decision (send, skip as
// duplicate, fail on quota, fail on roster gaps) is computed here so the most
// safety-critical logic in the system is testable without Google services.
// Blank means Email, so every roster written before v2.3 keeps behaving
// exactly as it did. Anything unrecognized also falls back to Email rather
// than silently dropping a family out of the alert path.
function normalizeContactBy_(raw) {
  var value = normalizeKeyText_(raw);
  if (!value) return CONTACT_EMAIL;
  for (var i = 0; i < CONTACT_CHOICES.length; i++) {
    if (normalizeKeyText_(CONTACT_CHOICES[i]) === value) return CONTACT_CHOICES[i];
  }
  if (value.indexOf('text') !== -1 && value.indexOf('email') !== -1) return CONTACT_BOTH;
  if (value.indexOf('text') !== -1 || value.indexOf('sms') !== -1) return CONTACT_TEXT;
  return CONTACT_EMAIL;
}

// Gmail deletes the href of any sms: link, leaving a button that renders
// perfectly and does nothing. Verified by inspecting a delivered message in
// Gmail: the anchor came through with no href at all. https is preserved, so
// the button points at a tiny page on the project site that carries the real
// sms: link and knows which syntax this phone wants.
//
// The number and message ride in the URL FRAGMENT, never the query string,
// because browsers never transmit a fragment to the server. Nothing about a
// student reaches GitHub, and the page scrubs the fragment on load.
function textBridgeLink_(phone, message) {
  var digits = telDigits_(phone);
  if (!digits) return '';
  return SITE_URL + 'text.html#n=' + encodeURIComponent(digits) +
    '&m=' + encodeURIComponent(String(message || ''));
}

// The office texts from a staff member's own handset, so the family sees an
// unknown mobile number. The program's own phone number is the only thing that
// makes the message verifiable, which is why it is read from the same settings
// field the email path uses. Reading a field name that readSettings_ never
// produces silently dropped it from every text that has ever been sent.
function textTaskMessage_(settings, name, whenStr) {
  var school = (settings && settings.school) ? settings.school : 'School';
  var phone = (settings && settings.phone) ? String(settings.phone).trim() : '';
  // whenStr is the full formatted date and time, so "today at" would stack on
  // top of it and read as "absent today at Sunday, July 19 at 9:31 AM".
  var msg = school + ': ' + name + ' was marked absent on ' + whenStr + '. ' +
    'If ' + name + ' was expected today, please check and contact us right away';
  return phone ? (msg + ' at ' + phone + '.') : (msg + '.');
}

// ---------------------------------------------------------------------------
// Automatic texts through Twilio (optional, off unless the program connects
// its own account). Pure helpers here; the network call lives further down
// with the other Google-service code so Node tests can exercise these.
// ---------------------------------------------------------------------------

// "+1", "1", "001", "+44" all mean the same thing to the person typing them.
// Anything unusable falls back to +1: the template's audience is mostly US
// programs, and a wrong default only affects numbers typed without their own
// country code, which the send then reports as a failure rather than hiding.
function normalizeCountryCode_(raw) {
  var digits = String(raw || '').replace(/\D/g, '').replace(/^0+/, '');
  if (!digits || digits.length > 3) return '+1';
  return '+' + digits;
}

// Twilio only accepts full international numbers (+13055550123). Rosters hold
// whatever a parent wrote on a form. This converts the common shapes and
// returns '' for anything ambiguous, so the send fails loudly into the manual
// office list instead of texting a stranger in another country.
function twilioE164_(raw, defaultCc) {
  var digits = telDigits_(raw); // keeps a leading +, strips extensions
  if (!digits) return '';
  var plain = digits.replace(/\D/g, '');
  if (digits.charAt(0) === '+') {
    return (plain.length >= 8 && plain.length <= 15) ? '+' + plain : '';
  }
  if (plain.indexOf('00') === 0 && plain.length > 10) {
    return '+' + plain.slice(2);
  }
  var cc = normalizeCountryCode_(defaultCc);
  var national = plain.replace(/^0+/, '');
  if (cc === '+1') {
    // North America: exactly ten digits, or eleven starting with 1. Anything
    // else is not a texting number there, so refuse rather than guess.
    if (national.length === 11 && national.charAt(0) === '1') return '+' + national;
    if (national.length === 10) return '+1' + national;
    return '';
  }
  if (national.length < 5 || (cc.length - 1) + national.length > 15) return '';
  return cc + national;
}

// The From box accepts either a rented Twilio number or a Messaging Service
// SID (MG...). They travel under different parameter names in the API call.
function twilioFromField_(from) {
  return /^MG[0-9a-fA-F]{32}$/.test(String(from || '').trim()) ? 'MessagingServiceSid' : 'From';
}

function looksLikeTwilioSid_(sid) {
  return /^AC[0-9a-fA-F]{32}$/.test(String(sid || '').trim());
}

// Shown in dialogs and the system check. Never show the full SID, and never
// show the Auth Token anywhere at all.
function maskTwilioSid_(sid) {
  var s = String(sid || '').trim();
  return s.length > 6 ? s.slice(0, 2) + '…' + s.slice(-4) : s;
}

// The plan actions settled in the text-first pass. Kept as one named predicate
// so the first pass and the send loop can never disagree about which actions
// have already been handled.
function isTextPlanAction_(action) {
  return action === 'TEXT_BY_OFFICE' || action === 'TEXT_AUTO' || action === 'SKIP_TEXT_LISTED' || action === 'FAIL_NO_PHONE';
}

// Everyone still ahead in the plan who has not been reached. SKIP_ALREADY_SENT
// is excluded because that contact really was alerted earlier today; the text
// actions are excluded because the first pass already logged and listed them.
// Every other remaining action means silence, so it must be named to the office
// rather than filtered away.
function unreachedRemainder_(plan, fromIndex) {
  return plan.slice(fromIndex).filter(function (rest) {
    return rest.action !== 'SKIP_ALREADY_SENT' && !isTextPlanAction_(rest.action);
  });
}

function planRecipientSends_(absentees, lookup, settings, alreadySent, quotaLeft) {
  var plan = [];
  var quota = (quotaLeft === null || quotaLeft === undefined) ? null : Number(quotaLeft);
  (absentees || []).forEach(function (absentee) {
    var student = lookup[dedupKey_(absentee.name, absentee.cls)];
    if (!student) {
      plan.push({ name: absentee.name, cls: absentee.cls, addr: '', action: 'FAIL_NOT_FOUND' });
      return;
    }
    // Derived here from contactBy alone, never from precomputed flags: a roster
    // object missing them (an older copy, or any other caller) must fall back
    // to Email and keep getting alerts, never silently drop out of the plan.
    var contactBy = normalizeContactBy_(student.contactBy);
    var wantsText = contactBy === CONTACT_TEXT || contactBy === CONTACT_BOTH;
    var wantsEmail = contactBy !== CONTACT_TEXT;
    // A family the office must text is planned first, so the office list is
    // built even if the email half of the same submission later aborts.
    if (wantsText) {
      if (telDigits_(student.phone)) {
        // Whether this family is ALSO emailed travels with the step. Without
        // it the office email and the ledger both told an organization that nobody
        // had been contacted, while the same run was emailing that family.
        var alsoEmailed = contactBy === CONTACT_BOTH;
        if (alreadySent[deliveryKey_(student.name, student.cls, telDigits_(student.phone))]) {
          plan.push({ name: student.name, cls: student.cls, addr: student.phone, action: 'SKIP_TEXT_LISTED', alsoEmailed: alsoEmailed });
        } else {
          // settings.autoText is attached by the submit flow when a Twilio
          // account is connected. Absent or false means every copy built
          // before v2.4, and every copy that never connected Twilio, keeps
          // the manual office list exactly as it was.
          plan.push({ name: student.name, cls: student.cls, addr: student.phone, action: settings && settings.autoText ? 'TEXT_AUTO' : 'TEXT_BY_OFFICE', alsoEmailed: alsoEmailed });
        }
      } else {
        plan.push({ name: student.name, cls: student.cls, addr: '', action: 'FAIL_NO_PHONE' });
      }
    }
    if (!wantsEmail) return;
    var recipients = student.emails.slice();
    if (settings && settings.bccOffice && isValidEmail_(settings.officeEmail) &&
        recipients.indexOf(String(settings.officeEmail).toLowerCase()) === -1) {
      recipients.push(String(settings.officeEmail).toLowerCase());
    }
    // A rostered student whose email cells were cleared after the Form was
    // built still resolves here; without this guard the absence would be
    // dropped silently with no email, no log row, and no alert.
    if (recipients.length === 0) {
      plan.push({ name: student.name, cls: student.cls, addr: '', action: 'FAIL_NO_EMAIL' });
      return;
    }
    recipients.forEach(function (addr) {
      // Dedup per contact (not per student) so a contact that failed earlier
      // today is retried on re-submission, while ones already sent are skipped.
      if (alreadySent[deliveryKey_(student.name, student.cls, addr)]) {
        plan.push({ name: student.name, cls: student.cls, addr: addr, action: 'SKIP_ALREADY_SENT' });
        return;
      }
      if (quota !== null && quota <= QUOTA_RESERVE) {
        plan.push({ name: student.name, cls: student.cls, addr: addr, action: 'FAIL_QUOTA' });
        return;
      }
      if (quota !== null) quota--;
      plan.push({ name: student.name, cls: student.cls, addr: addr, action: 'SEND' });
    });
  });
  return plan;
}

// Office notices are capped per local day (see OFFICE_NOTICE_DAILY_CAP).
// State format: "yyyy-MM-dd|count". A new day resets the count.
function officeNoticeAllowance_(storedValue, todayKey, cap) {
  var parts = String(storedValue || '').split('|');
  var count = (parts[0] === todayKey) ? (Number(parts[1]) || 0) : 0;
  var limit = (cap === undefined || cap === null) ? OFFICE_NOTICE_DAILY_CAP : Number(cap);
  return { count: count, allowed: count < limit };
}

// Convert item responses into a validated submission without depending on
// Google service objects. This is also the contract exercised by Node tests.
function parseSubmissionAnswers_(answers, roster) {
  var selectedClass = '';
  var takenBy = '';
  var notes = '';
  var notesClass = '';
  var genericNotes = '';
  var absentRows = [];
  var errors = [];
  var lookup = rosterLookup_(roster);
  var groups = {};
  groupRosterByClass_(roster).forEach(function (g) { groups[normalizeKeyText_(g.cls)] = g.cls; });

  (answers || []).forEach(function (answer) {
    var title = String(answer.title || '');
    var value = answer.response;
    if (title === CLASS_QUESTION) {
      selectedClass = singleLine_(value, 250);
      return;
    }
    if (title === TAKEN_BY_QUESTION) {
      takenBy = singleLine_(value, 120);
      return;
    }
    if (title === LEGACY_NOTES_QUESTION) {
      genericNotes = cleanText_(value, 1000);
      return;
    }
    if (title.indexOf(NOTES_PREFIX) === 0) {
      var noteClass = singleLine_(title.substring(NOTES_PREFIX.length), 250);
      var noteValue = cleanText_(value, 1000);
      if (noteValue) {
        notes = noteValue;
        notesClass = noteClass;
      }
      return;
    }
    var cls = parseClassFromTitle_(title);
    if (!cls || value === '' || value === null || value === undefined) return;
    var names = Array.isArray(value) ? value : [value];
    names.forEach(function (name) {
      name = singleLine_(name, 250);
      if (name) absentRows.push({ name: name, cls: cls });
    });
  });

  if (!selectedClass) errors.push('No class or group was selected.');
  else if (!groups[normalizeKeyText_(selectedClass)]) errors.push('The selected class or group is not in the current roster.');
  if (notes && selectedClass && normalizeKeyText_(notesClass) !== normalizeKeyText_(selectedClass)) {
    errors.push('The office note did not match the selected class or group.');
  }
  if (!notes) notes = genericNotes;

  var seen = {};
  var absentees = [];
  absentRows.forEach(function (a) {
    if (selectedClass && normalizeKeyText_(a.cls) !== normalizeKeyText_(selectedClass)) {
      errors.push('The absent-student page did not match the selected class or group.');
      return;
    }
    var key = dedupKey_(a.name, a.cls);
    if (!lookup[key]) {
      errors.push(a.name + ' is not in the current roster for ' + a.cls + '.');
      return;
    }
    if (!seen[key]) { seen[key] = true; absentees.push({ name: lookup[key].name, cls: lookup[key].cls }); }
  });

  return {
    ok: errors.length === 0,
    errors: errors,
    selectedClass: groups[normalizeKeyText_(selectedClass)] || selectedClass,
    takenBy: takenBy,
    notes: notes,
    absentees: absentees
  };
}

// ---------------------------------------------------------------------------
// Reading settings and roster
// ---------------------------------------------------------------------------

function readSettings_(ss) {
  var sh = ss.getSheetByName(TAB_START);
  if (!sh) return null;
  var v = sh.getRange(CELL_SETTINGS).getValues();
  var officeCopyRaw = String(v[5][0]).trim();
  var hourRaw = String(v[6][0]).trim();
  return {
    school: singleLine_(v[0][0], 250),
    officeEmail: String(v[1][0]).trim(),
    phone: singleLine_(v[2][0], 120),
    logoUrl: driveImageUrl_(v[3][0]),
    accent: normalizeColor_(v[4][0]),
    officeCopyRaw: officeCopyRaw,
    bccOffice: officeCopyRaw.toLowerCase() === 'yes',
    morningRaw: hourRaw,
    morningHour: (/^(8|9|10|11)$/.test(hourRaw)) ? parseInt(hourRaw, 10) : null,
    tz: ss.getSpreadsheetTimeZone()
  };
}

// Spreadsheet error values, read back as ordinary text. An organization that fills the
// roster with formulas (a lookup against another tab is a common thing to do)
// can end up with these in a cell, and read as data they become the child's
// name: the system would then email a family about a student called #N/A.
var CELL_ERRORS = ['#N/A', '#REF!', '#VALUE!', '#DIV/0!', '#NAME?', '#NULL!', '#NUM!', '#ERROR!'];

function cellErrorText_(value) {
  var text = String(value === null || value === undefined ? '' : value).trim().toUpperCase();
  for (var i = 0; i < CELL_ERRORS.length; i++) {
    if (text === CELL_ERRORS[i]) return CELL_ERRORS[i];
  }
  return '';
}

// Sheets parses anything typed with a leading =, + or - as a formula, and no
// cell format prevents it. Plain text formatting saves the leading zero of
// 07700900123, but +972501234567 still becomes the number 972501234567 with the
// country code gone. Under this template's plain-text column a numeric value
// therefore means a prefix was eaten, and the office would text an unroutable
// number with nothing on screen showing what was lost.
function phoneLostItsPrefix_(raw) {
  return typeof raw === 'number';
}

function readRoster_(ss) {
  var sh = ss.getSheetByName(TAB_STUDENTS);
  var roster = [];
  var problems = [];
  var sampleRows = 0;
  if (!sh) return { roster: roster, problems: ['The Students tab is missing.'], sampleRows: 0 };
  var last = sh.getLastRow();
  if (last < 2) return { roster: roster, problems: [], sampleRows: 0 };
  var values = sh.getRange(2, 1, last - 1, 7).getValues();
  var seen = {};
  values.forEach(function (row, i) {
    var rowNum = i + 2;
    // Checked before anything else, because an error value read as data becomes
    // a student name, an address, or a phone number without ever looking wrong.
    var rowError = '';
    for (var ci = 0; ci < row.length; ci++) {
      rowError = cellErrorText_(row[ci]);
      if (rowError) break;
    }
    if (rowError) {
      problems.push('Row ' + rowNum + ': a cell shows the spreadsheet error ' + rowError +
        '. An alert would carry that error instead of a real name or contact. Fix the formula, or retype the cell as plain text.');
      return;
    }
    var name = singleLine_(row[0], 250);
    if (!name) {
      var partialRow = row.slice(1).some(function (value) { return String(value).trim() !== ''; });
      if (partialRow) problems.push('Row ' + rowNum + ': this row has class or contact data but no student name. Complete the name or clear the entire row.');
      return;
    }
    // Case-insensitive, matching the template's own conditional-format rule,
    // so a lowercase "(sample" row can never be enrolled as a real student.
    if (name.toUpperCase().indexOf(SAMPLE_MARKER) !== -1) { sampleRows++; return; }
    var cls = singleLine_(row[1], 250) || DEFAULT_CLASS;
    var emails = [];
    [row[2], row[3], row[4]].forEach(function (e) {
      var em = String(e).trim();
      if (!em) return;
      if (isValidEmail_(em)) {
        if (emails.indexOf(em.toLowerCase()) === -1) emails.push(em.toLowerCase());
      } else {
        problems.push('Row ' + rowNum + ': "' + em + '" does not look like an email address.');
      }
    });
    var phone = singleLine_(row[5], 40);
    var contactBy = normalizeContactBy_(row[6]);
    var wantsEmail = contactBy !== CONTACT_TEXT;
    var wantsText = contactBy === CONTACT_TEXT || contactBy === CONTACT_BOTH;
    if (wantsEmail && emails.length === 0) {
      problems.push('Row ' + rowNum + ' (' + name + '): no valid family contact email. Every student needs at least one reachable adult email, or set Contact by to "' + CONTACT_TEXT + '" and add a phone number.');
    }
    if (wantsText && phoneLostItsPrefix_(row[5])) {
      problems.push('Row ' + rowNum + ' (' + name + '): the phone was saved as a number, so a leading + or 0 has been lost and the text may not reach anyone. Retype it with an apostrophe in front, for example \'+972501234567.');
    }
    if (wantsText && !telDigits_(phone)) {
      problems.push('Row ' + rowNum + ' (' + name + '): Contact by is "' + contactBy + '" but there is no usable phone number. Add the family phone, or change Contact by to "' + CONTACT_EMAIL + '".');
    }
    var key = dedupKey_(name, cls);
    if (seen[key]) {
      problems.push('Row ' + rowNum + ': "' + name + '" appears twice in "' + cls + '". Alerts could reach the wrong family. Make each name unique inside its class or group, for example by adding a last initial.');
    }
    seen[key] = true;
    roster.push({ name: name, cls: cls, emails: emails, phone: phone, contactBy: contactBy, wantsEmail: wantsEmail, wantsText: wantsText, row: rowNum });
  });
  return { roster: roster, problems: problems, sampleRows: sampleRows };
}

function rosterLookup_(roster) {
  var map = {};
  roster.forEach(function (r) { map[dedupKey_(r.name, r.cls)] = r; });
  return map;
}

function configurationProblems_(settings, rosterResult) {
  var problems = [];
  if (!settings) return ['The Start Here tab is missing.'];
  if (!settings.school) problems.push('Add your school or organization name.');
  if (!isValidEmail_(settings.officeEmail)) problems.push('Add a valid office email.');
  if (!/^(yes|no)$/i.test(String(settings.officeCopyRaw || ''))) problems.push('Office copy must be Yes or No.');
  if (!/^(off|8|9|10|11)$/i.test(String(settings.morningRaw || ''))) problems.push('Morning reminder must be Off, 8, 9, 10, or 11.');
  if (!rosterResult || rosterResult.roster.length === 0) problems.push('No real students were found on the Students tab.');
  if (rosterResult) problems = problems.concat(rosterResult.problems || []);
  return problems;
}

function validateConfiguration_(ss) {
  var settings = readSettings_(ss);
  var rosterResult = readRoster_(ss);
  var problems = configurationProblems_(settings, rosterResult);
  // The ledger is part of the safety contract: a missing or reshaped Email
  // Log breaks dedup and coverage, so it blocks sends like any other problem.
  try { problems = problems.concat(logSheetProblems_(ss)); }
  catch (err) { problems.push('The Email Log tab could not be read: ' + String(err && err.message || err)); }
  return {
    settings: settings,
    rosterResult: rosterResult,
    problems: problems
  };
}

// ---------------------------------------------------------------------------
// Setup (build or update the form, install triggers)
// ---------------------------------------------------------------------------

// The connected Form object is the only source of truth for the staff and
// editor links. Cells B18/B19 are display outputs, never trusted inputs: a
// pasted foreign Form URL must never look ready or be copied to staff.
function resolveFormLinks_(form) {
  var staffUrl = '';
  var editUrl = '';
  try {
    var published = form.getPublishedUrl();
    try { staffUrl = form.shortenFormUrl(published); }
    catch (ignored) { staffUrl = published; }
  } catch (ignored) {}
  try { editUrl = form.getEditUrl(); } catch (ignored) {}
  return { staffUrl: String(staffUrl || ''), editUrl: String(editUrl || '') };
}

function linkCellsNeedRepair_(currentStaff, currentEdit, links) {
  return String(currentStaff || '').trim() !== String(links.staffUrl || '').trim() ||
    String(currentEdit || '').trim() !== String(links.editUrl || '').trim();
}

function repairLinkCells_(ss, links) {
  var sh = ss.getSheetByName(TAB_START);
  if (!sh) return;
  var currentStaff = sh.getRange(CELL_STAFF_LINK).getDisplayValue();
  var currentEdit = sh.getRange(CELL_EDIT_LINK).getDisplayValue();
  if (linkCellsNeedRepair_(currentStaff, currentEdit, links)) {
    writeLinkCells_(ss, links);
  }
}

function setupAssistantState() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  ensureTabs_(ss);
  var sh = ss.getSheetByName(TAB_START);
  var settingsValues = sh.getRange(CELL_SETTINGS).getDisplayValues();
  var config = validateConfiguration_(ss);
  var settings = config.settings || {};
  var rosterResult = config.rosterResult || { roster: [], problems: [], sampleRows: 0 };
  var groups = groupRosterByClass_(rosterResult.roster || []);
  var requiredProblems = [];
  if (!settings.school) requiredProblems.push('Add your school or organization name.');
  if (!isValidEmail_(settings.officeEmail)) requiredProblems.push('Add a valid office email.');
  var rosterProblems = [];
  if (!rosterResult.roster.length) rosterProblems.push('Replace the sample rows with at least one real student.');
  rosterProblems = rosterProblems.concat(rosterResult.problems || []);
  var props = PropertiesService.getDocumentProperties();
  var formId = props.getProperty(PROP_FORM_ID) || '';
  var status = String(sh.getRange(CELL_STATUS).getDisplayValue() || '').trim();
  var paused = props.getProperty(PROP_PAUSED) === 'yes';
  var formConnected = false;
  var formHealthy = false;
  var staffUrl = '';
  var editUrl = '';
  if (formId) {
    try {
      var form = FormApp.openById(formId);
      formConnected = true;
      var links = resolveFormLinks_(form);
      staffUrl = links.staffUrl;
      editUrl = links.editUrl;
      formHealthy = rosterProblems.length === 0 && liveFormSafetyProblems_(form, rosterResult.roster).length === 0;
      if (formHealthy && !paused) formHealthy = form.isAcceptingResponses();
      // Opening the helper repairs drifted, foreign, or missing link cells.
      try { repairLinkCells_(ss, links); } catch (ignored) {}
    } catch (ignored) {}
  }
  var ready = formConnected && formHealthy && !!staffUrl && !paused && /^READY\b/i.test(status);
  return {
    school: String(settingsValues[0][0] || ''),
    officeEmail: String(settingsValues[1][0] || ''),
    phone: String(settingsValues[2][0] || ''),
    logo: String(settingsValues[3][0] || ''),
    accent: /^#?[0-9a-fA-F]{6}$/.test(String(settingsValues[4][0] || '').trim())
      ? normalizeColor_(settingsValues[4][0]) : DEFAULT_ACCENT,
    officeCopy: /yes/i.test(String(settingsValues[5][0] || '')) ? 'Yes' : 'No',
    morning: /^(8|9|10|11)$/.test(String(settingsValues[6][0] || '').trim())
      ? String(settingsValues[6][0]).trim() : 'Off',
    requiredReady: requiredProblems.length === 0,
    requiredProblems: requiredProblems,
    rosterReady: rosterProblems.length === 0,
    rosterProblems: rosterProblems.slice(0, 12),
    studentCount: rosterResult.roster.length,
    groupCount: groups.length,
    sampleCount: rosterResult.sampleRows || 0,
    ready: ready,
    paused: paused,
    status: status,
    staffUrl: staffUrl,
    editUrl: editUrl,
    formConnected: formConnected,
    progress: ready ? 3 : ((requiredProblems.length === 0 ? 1 : 0) + (rosterProblems.length === 0 ? 1 : 0))
  };
}

function saveSetupAssistantSettings_(ss, data) {
  data = data || {};
  ensureTabs_(ss);
  var officeCopy = String(data.officeCopy || '').toLowerCase() === 'yes' ? 'Yes' : 'No';
  var morning = /^(8|9|10|11)$/.test(String(data.morning || '').trim()) ? String(data.morning).trim() : 'Off';
  var accent = /^#?[0-9a-fA-F]{6}$/.test(String(data.accent || '').trim())
    ? normalizeColor_(data.accent) : DEFAULT_ACCENT;
  ss.getSheetByName(TAB_START).getRange(CELL_SETTINGS).setValues([
    [safeCellText_(data.school, 250)],
    [safeCellText_(data.officeEmail, 320)],
    [safeCellText_(data.phone, 120)],
    [safeCellText_(data.logo, 1500)],
    [accent],
    [officeCopy],
    [morning]
  ]);
  SpreadsheetApp.flush();
}

function saveSetupAndOpenStudents(data) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  saveSetupAssistantSettings_(ss, data);
  ss.setActiveSheet(ss.getSheetByName(TAB_STUDENTS));
  return setupAssistantState();
}

function openStudentsForSetup() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  ensureTabs_(ss);
  ss.setActiveSheet(ss.getSheetByName(TAB_STUDENTS));
  return setupAssistantState();
}

function performSetup_(ss) {
  // The design and ledger refresh rides along with the deliberate build now
  // that the standalone menu item is gone. A cosmetic failure here must never
  // block the alert system itself, so fall back to the missing-tab check.
  try { buildTemplateTabs(); } catch (ignored) { ensureTabs_(ss); }
  var initialConfig = validateConfiguration_(ss);
  if (initialConfig.problems.length > 0) {
    writeStatus_(ss, 'ACTION NEEDED', 'Finish the items shown in the setup helper. Alerts were not changed.', DANGER_RED);
    return {
      ok: false,
      title: 'A few things need attention',
      message: 'Fix the highlighted items, then build the system again.',
      problems: initialConfig.problems.slice(0, 15)
    };
  }
  var props = PropertiesService.getDocumentProperties();
  // Set the shared stop flag before waiting so an in-flight handler checks it
  // before its next recipient. Form/trigger mutations still happen under lock.
  props.setProperty(PROP_PAUSED, 'yes');
  var acquired = acquireScriptLock_(30000);
  if (!acquired.lock) {
    writeStatus_(ss, 'ACTION NEEDED', 'Setup could not get the safety lock. Alerts are paused. Try again.', DANGER_RED);
    return {
      ok: false,
      title: 'Setup is busy',
      message: 'Another attendance operation did not finish within 30 seconds. Alerts are paused. Try again.',
      problems: [acquired.error]
    };
  }
  var config = null;
  var settings = null;
  var roster = null;
  var form = null;
  var setupError = null;
  try {
    config = validateConfiguration_(ss);
    if (config.problems.length > 0) {
      throw new Error('Setup data changed or failed validation: ' + config.problems.join(' | '));
    }
    settings = config.settings;
    roster = config.rosterResult.roster;
    form = getOrCreateForm_(ss, settings);
    setFormAccepting_(form, false);
    rebuildFormItems_(form, roster);
    var formProblems = liveFormSafetyProblems_(form, roster);
    if (formProblems.length) throw new Error(formProblems.join(' | '));
    installTriggers_(form, settings);
    setVersionProperties_();
    updateFormLinks_(ss, form);
    setFormAccepting_(form, true);
    if (form.supportsAdvancedResponderPermissions && form.supportsAdvancedResponderPermissions() && !form.isPublished()) {
      throw new Error('The attendance Form was not published for responders.');
    }
    props.deleteProperty(PROP_PAUSED);
    var now = Utilities.formatDate(new Date(), settings.tz, 'MMMM d, yyyy h:mm a');
    writeStatus_(ss, 'READY', 'Alerts on. Last verified ' + now + ' (' + settings.tz + ').', C_GREEN);
  } catch (err) {
    setupError = err;
    props.setProperty(PROP_PAUSED, 'yes');
    if (form) { try { setFormAccepting_(form, false); } catch (ignored) {} }
    writeStatus_(ss, 'ACTION NEEDED', 'Setup did not finish. Alerts are paused. Open the setup helper and try again.', DANGER_RED);
  } finally {
    releaseScriptLock_(acquired.lock);
  }
  if (setupError) {
    return {
      ok: false,
      title: 'Setup did not finish',
      message: 'Alerts remain paused. Fix the issue and try again.',
      problems: [String(setupError && setupError.message || setupError)]
    };
  }

  var groups = groupRosterByClass_(roster);
  var reminderNote = settings.morningHour !== null
    ? 'The office reminder runs once on weekdays, sometime between ' + settings.morningHour + ':00 and ' + settings.morningHour + ':59.'
    : 'The morning reminder is off.';
  return {
    ok: true,
    title: 'Your absence alert system is ready',
    message: roster.length + ' students in ' + groups.length + ' groups are connected. ' + reminderNote +
      ' Share the staff link only with staff.',
    problems: []
  };
}

function runSetupAssistant(data) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  saveSetupAssistantSettings_(ss, data);
  var result = performSetup_(ss);
  result.state = setupAssistantState();
  return result;
}

function setup() {
  var ui = SpreadsheetApp.getUi();
  var result = performSetup_(SpreadsheetApp.getActiveSpreadsheet());
  var body = result.message || '';
  if (result.problems && result.problems.length) body += '\n\n' + result.problems.join('\n\n');
  ui.alert(result.title, body, ui.ButtonSet.OK);
}

function sendPreviewFromAssistant() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var result = sendPreview_(ss);
  result.state = setupAssistantState();
  return result;
}

function setVersionProperties_() {
  PropertiesService.getDocumentProperties().setProperties({
    APP_VERSION: APP_VERSION,
    SCHEMA_VERSION: String(SCHEMA_VERSION)
  });
}

function configureForm_(form, ss, settings) {
  form.setTitle(settings.school + ' Absence Form');
  form.setDescription(
    'For staff only. Choose your class or group, then check only the students who are absent. ' +
    'You can choose more than one student. If everyone is here, leave the list empty and submit. ' +
    'Do not mark anyone present. Family contacts of checked students are emailed automatically.'
  );
  form.setCollectEmail(false);
  try { form.setRequireLogin(false); } catch (ignored) {}
  form.setLimitOneResponsePerUser(false);
  try { form.setAllowResponseEdits(false); } catch (ignored) {}
  try { form.setShowLinkToRespondAgain(true); } catch (ignored) {}
  try { form.setShuffleQuestions(false); } catch (ignored) {}
  try { form.setProgressBar(false); } catch (ignored) {}
  try { form.setPublishingSummary(false); } catch (ignored) {}
  try { form.setConfirmationMessage('Done. Your absence report was received. Checked students were reported absent. If you left the list empty, no absences were reported. If you reported someone by mistake, contact the office immediately.'); } catch (ignored) {}
  try { form.setCustomClosedFormMessage('Absence Alerts is paused or being updated. Please contact the office if attendance is urgent.'); } catch (ignored) {}
  try {
    if (form.getDestinationId() !== ss.getId()) form.setDestination(FormApp.DestinationType.SPREADSHEET, ss.getId());
  } catch (err) {
    form.setDestination(FormApp.DestinationType.SPREADSHEET, ss.getId());
  }
  return form;
}

function anonymousFormProblems_(form) {
  var problems = [];
  try {
    if (form.collectsEmail()) problems.push('The Form is collecting responder email addresses.');
  } catch (err) { problems.push('The Form email-collection setting could not be verified.'); }
  try {
    if (form.hasLimitOneResponsePerUser()) problems.push('The Form limits each person to one response, which can force sign-in.');
  } catch (err) { problems.push('The Form one-response setting could not be verified.'); }
  if (form.requiresLogin) {
    try {
      if (form.requiresLogin()) problems.push('The Form requires a Google Workspace sign-in.');
    } catch (ignored) {
      // Google deprecated this getter and it throws for some valid Forms. The
      // setter above is still attempted. A readable explicit true remains a
      // blocker, while an unavailable legacy verification does not break setup.
    }
  }
  return problems;
}

function liveFormSafetyProblems_(form, roster) {
  var problems = [];
  try {
    // Order-insensitive on purpose: sorting the Students tab (the template
    // ships a filter that invites exactly that) must not disable the system.
    if (!formStructuresEquivalent_(actualFormStructure_(form), desiredFormStructure_(roster))) {
      problems.push('The live Form structure no longer matches the current roster.');
    }
  } catch (err) {
    problems.push('The live Form structure could not be verified: ' + String(err && err.message || err));
  }
  return problems.concat(anonymousFormProblems_(form));
}

function setFormAccepting_(form, enabled) {
  if (enabled) {
    try {
      if (form.supportsAdvancedResponderPermissions && form.supportsAdvancedResponderPermissions() && !form.isPublished()) {
        form.setPublished(true);
      }
    } catch (ignored) {}
  }
  form.setAcceptingResponses(!!enabled);
  if (form.isAcceptingResponses() !== !!enabled) throw new Error('Google Forms did not apply the requested open/closed state.');
}

function getOrCreateForm_(ss, settings) {
  var props = PropertiesService.getDocumentProperties();
  var formId = props.getProperty(PROP_FORM_ID);
  var form = null;
  if (formId) {
    try { form = FormApp.openById(formId); }
    catch (e) { form = null; }
  }
  if (!form) {
    form = FormApp.create(settings.school + ' Absence Form');
    props.setProperty(PROP_FORM_ID, form.getId());
  }
  return configureForm_(form, ss, settings);
}

function actualFormStructure_(form) {
  return form.getItems().map(function (item) {
    var type = String(item.getType());
    var row = { type: type, title: item.getTitle() };
    if (type === 'MULTIPLE_CHOICE') {
      var multipleChoice = item.asMultipleChoiceItem();
      row.help = multipleChoice.getHelpText() || '';
      row.required = multipleChoice.isRequired();
      row.choices = multipleChoice.getChoices().map(function (choice) {
        var goto = '';
        try { var page = choice.getGotoPage(); goto = page ? page.getTitle() : ''; } catch (ignored) {}
        return { value: choice.getValue(), goto: goto };
      });
    } else if (type === 'TEXT') {
      var textItem = item.asTextItem();
      row.help = textItem.getHelpText() || '';
      row.required = textItem.isRequired();
    } else if (type === 'CHECKBOX') {
      var checkbox = item.asCheckboxItem();
      row.help = checkbox.getHelpText() || '';
      row.required = checkbox.isRequired();
      row.choices = checkbox.getChoices().map(function (choice) { return choice.getValue(); });
    } else if (type === 'PARAGRAPH_TEXT') {
      var paragraph = item.asParagraphTextItem();
      row.help = paragraph.getHelpText() || '';
      row.required = paragraph.isRequired();
    } else if (type === 'PAGE_BREAK') {
      var pageBreak = item.asPageBreakItem();
      row.help = pageBreak.getHelpText() || '';
      try { row.navigation = String(pageBreak.getPageNavigationType()); } catch (ignored) { row.navigation = ''; }
    } else {
      row.help = '';
    }
    return row;
  });
}

function rebuildFormItems_(form, roster) {
  var desired = desiredFormStructure_(roster);
  if (formStructuresEquivalent_(actualFormStructure_(form), desired)) return false;

  // Google Forms refuses to delete a page break while any multiple-choice
  // answer still navigates to it (the API throws "Invalid data"), so strip
  // every navigation reference before deleting anything. Without this, a
  // hand-edited Form could not be repaired by building again.
  var items = form.getItems();
  items.forEach(function (item) {
    var type = String(item.getType());
    if (type === 'MULTIPLE_CHOICE') {
      try { item.asMultipleChoiceItem().setChoiceValues(['Removed']); } catch (ignored) {}
    } else if (type === 'PAGE_BREAK') {
      try { item.asPageBreakItem().setGoToPage(FormApp.PageNavigationType.CONTINUE); } catch (ignored) {}
    }
  });
  items = form.getItems();
  for (var i = items.length - 1; i >= 0; i--) {
    form.deleteItem(items[i]);
  }

  var groups = groupRosterByClass_(roster);
  var classItem = form.addMultipleChoiceItem()
    .setTitle(CLASS_QUESTION)
    .setHelpText(HELP_CLASS)
    .setRequired(true);
  var choices = [];
  groups.forEach(function (g) {
    var page = form.addPageBreakItem()
      .setTitle(g.cls)
      .setHelpText(HELP_PAGE);
    page.setGoToPage(FormApp.PageNavigationType.SUBMIT);
    form.addCheckboxItem()
      .setTitle(QUESTION_PREFIX + g.cls + QUESTION_SUFFIX)
      .setHelpText(HELP_ABSENT)
      .setChoiceValues(g.names)
      .setRequired(false);
    choices.push(classItem.createChoice(g.cls, page));
  });
  classItem.setChoices(choices);
  if (!formStructureMatches_(actualFormStructure_(form), desired)) {
    throw new Error('The rebuilt Form structure does not match the current roster.');
  }
  return true;
}

function installTriggers_(form, settings) {
  var created = [];
  try {
    var submit = ScriptApp.newTrigger('handleFormSubmit').forForm(form).onFormSubmit().create();
    created.push(submit);
    var morning = null;
    if (settings.morningHour !== null) {
      morning = ScriptApp.newTrigger('morningCheck').timeBased().atHour(settings.morningHour)
        .everyDays(1).inTimezone(settings.tz).create();
      created.push(morning);
    }
    var submitUid = String(submit.getUniqueId());
    var morningUid = morning ? String(morning.getUniqueId()) : '';
    var ownerToken = Utilities.getUuid ? Utilities.getUuid() : String(new Date().getTime());
    PropertiesService.getDocumentProperties().setProperties({
      ACTIVE_OWNER_TOKEN: ownerToken,
      ACTIVE_SUBMIT_TRIGGER_UID: submitUid,
      ACTIVE_MORNING_TRIGGER_UID: morningUid,
      ACTIVE_MORNING_SCHEDULE: morningScheduleKey_(settings.morningHour, settings.tz)
    });

    // A user cannot see another editor's triggers. The shared active UIDs make
    // those old triggers inert; this cleanup removes only this user's old ones.
    ScriptApp.getProjectTriggers().forEach(function (trigger) {
      var fn = trigger.getHandlerFunction();
      var uid = String(trigger.getUniqueId());
      if ((fn === 'handleFormSubmit' || fn === 'morningCheck') && uid !== submitUid && uid !== morningUid) {
        try { ScriptApp.deleteTrigger(trigger); } catch (ignored) {}
      }
    });
    return { ownerToken: ownerToken, submitUid: submitUid, morningUid: morningUid };
  } catch (err) {
    created.forEach(function (trigger) { try { ScriptApp.deleteTrigger(trigger); } catch (ignored) {} });
    throw err;
  }
}

function deleteOurTriggers_() {
  ScriptApp.getProjectTriggers().forEach(function (t) {
    var fn = t.getHandlerFunction();
    if (fn === 'handleFormSubmit' || fn === 'morningCheck') {
      ScriptApp.deleteTrigger(t);
    }
  });
}

function activeTriggerEvent_(e, propertyName, allowManual) {
  if ((!e || !e.triggerUid) && allowManual) return true;
  if (!e || !e.triggerUid) return false;
  var props = PropertiesService.getDocumentProperties();
  if (!props.getProperty(PROP_ACTIVE_OWNER_TOKEN)) return false;
  var expected = props.getProperty(propertyName);
  return triggerUidMatches_(e.triggerUid, expected);
}

function acquireScriptLock_(timeoutMs) {
  var lock = null;
  try {
    lock = LockService.getScriptLock();
    lock.waitLock(timeoutMs || 30000);
    return { lock: lock, error: '' };
  } catch (err) {
    return { lock: null, error: String(err && err.message || err) };
  }
}

function releaseScriptLock_(lock) {
  if (lock) { try { lock.releaseLock(); } catch (ignored) {} }
}

function currentSubmissionLifecycle_(e, sourceId) {
  var props = PropertiesService.getDocumentProperties();
  var snapshot = {
    paused: props.getProperty(PROP_PAUSED) === 'yes',
    ownerToken: props.getProperty(PROP_ACTIVE_OWNER_TOKEN),
    actualTriggerUid: e && e.triggerUid,
    expectedTriggerUid: props.getProperty(PROP_ACTIVE_SUBMIT_UID),
    actualFormId: sourceId,
    expectedFormId: props.getProperty(PROP_FORM_ID)
  };
  var problems = submissionLifecycleProblems_(snapshot);
  return { ok: problems.length === 0, problems: problems };
}

function updateFormLinks_(ss, form) {
  writeLinkCells_(ss, resolveFormLinks_(form));
}

// Explicit full styling on every write: these cells may hold the gray italic
// placeholder the template ships, and real links must never inherit it. The
// verified links are also cached in document properties, which sheet editors
// cannot modify, so the display cells can self-heal without a Forms call.
function writeLinkCells_(ss, links) {
  var sh = ss.getSheetByName(TAB_START);
  if (!sh) return;
  sh.getRange(CELL_STAFF_LINK).setValue(links.staffUrl)
    .setFontStyle('normal').setFontWeight('bold').setFontLine('underline')
    .setFontColor(C_GREEN_DARK).setFontSize(10);
  sh.getRange(CELL_EDIT_LINK).setValue(links.editUrl)
    .setFontStyle('normal').setFontWeight('normal').setFontLine('underline')
    .setFontColor(C_GRAY).setFontSize(10);
  try {
    PropertiesService.getDocumentProperties().setProperties({
      STAFF_URL_CACHE: String(links.staffUrl || ''),
      EDIT_URL_CACHE: String(links.editUrl || '')
    });
  } catch (ignored) {}
}

// A pasted or disguised link must never sit beside a READY status waiting
// for someone to open the helper. Every Sheet open and every submission
// restores the display cells from the verified cache.
function repairLinkCellsFromCache_(ss) {
  var props = PropertiesService.getDocumentProperties();
  var cachedStaff = String(props.getProperty(PROP_STAFF_URL_CACHE) || '');
  var cachedEdit = String(props.getProperty(PROP_EDIT_URL_CACHE) || '');
  if (!cachedStaff && !cachedEdit) return false;
  var sh = ss.getSheetByName(TAB_START);
  if (!sh) return false;
  var currentStaff = String(sh.getRange(CELL_STAFF_LINK).getDisplayValue() || '').trim();
  var currentEdit = String(sh.getRange(CELL_EDIT_LINK).getDisplayValue() || '').trim();
  if (currentStaff === cachedStaff && currentEdit === cachedEdit) return false;
  writeLinkCells_(ss, { staffUrl: cachedStaff, editUrl: cachedEdit });
  return true;
}

function rotateStaffForm() {
  var ui = SpreadsheetApp.getUi();
  var answer = ui.alert('Replace the staff link?',
    'Use this only if the current staff link was shared outside staff. The old Form will stop accepting responses and staff must receive the new link.',
    ui.ButtonSet.YES_NO);
  if (answer !== ui.Button.YES) return;
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var initialConfig = validateConfiguration_(ss);
  if (initialConfig.problems.length) {
    ui.alert('Fix setup first', initialConfig.problems.slice(0, 15).join('\n\n'), ui.ButtonSet.OK);
    return;
  }
  var props = PropertiesService.getDocumentProperties();
  props.setProperty(PROP_PAUSED, 'yes');
  var acquired = acquireScriptLock_(30000);
  if (!acquired.lock) {
    writeStatus_(ss, 'ACTION NEEDED', 'Link replacement could not get the safety lock. Alerts are paused.', DANGER_RED);
    ui.alert('Link replacement is busy', 'Alerts are paused, but the Form was not replaced. Run link replacement again.\n\n' + acquired.error, ui.ButtonSet.OK);
    return;
  }
  var config = null;
  var oldId = '';
  var oldForm = null;
  var newForm = null;
  var oldRetirementProblem = '';
  var resultTitle = '';
  var resultBody = '';
  try {
    config = validateConfiguration_(ss);
    if (config.problems.length) throw new Error('Setup data changed or failed validation: ' + config.problems.join(' | '));
    oldId = props.getProperty(PROP_FORM_ID);
    if (oldId) { try { oldForm = FormApp.openById(oldId); } catch (ignored) {} }
    // Retire the exposed entry point before a replacement is activated. If the
    // current Form can be opened but cannot be closed, fail closed and leave the
    // system paused instead of pretending the exposed link was retired.
    if (oldForm) {
      setFormAccepting_(oldForm, false);
      try { oldForm.setCustomClosedFormMessage('This absence report link was replaced. Ask the office for the current link.'); } catch (ignored) {}
    } else if (oldId) {
      oldRetirementProblem = 'The previous Form could not be opened, so its closed state could not be verified. Close it manually from Google Drive.';
    }
    newForm = FormApp.create(config.settings.school + ' Absence Form');
    configureForm_(newForm, ss, config.settings);
    setFormAccepting_(newForm, false);
    rebuildFormItems_(newForm, config.rosterResult.roster);
    var formProblems = liveFormSafetyProblems_(newForm, config.rosterResult.roster);
    if (formProblems.length) throw new Error(formProblems.join(' | '));
    props.setProperty(PROP_FORM_ID, newForm.getId());
    try { installTriggers_(newForm, config.settings); }
    catch (triggerErr) {
      // Restore the exact prior state: an empty-string FORM_ID would read as
      // "some form exists" and make the next build mint yet another orphan.
      if (oldId) props.setProperty(PROP_FORM_ID, oldId);
      else props.deleteProperty(PROP_FORM_ID);
      try { newForm.setTitle('[NOT ACTIVE] ' + newForm.getTitle()); } catch (ignored) {}
      throw triggerErr;
    }
    setVersionProperties_();
    updateFormLinks_(ss, newForm);
    setFormAccepting_(newForm, true);
    if (newForm.supportsAdvancedResponderPermissions && newForm.supportsAdvancedResponderPermissions() && !newForm.isPublished()) {
      throw new Error('The replacement Form was not published for responders.');
    }
    props.deleteProperty(PROP_PAUSED);
    if (oldForm) {
      try { oldForm.setTitle('[RETIRED] ' + oldForm.getTitle().replace(/^\[RETIRED\]\s*/, '')); } catch (ignored) {}
    }
    if (oldRetirementProblem) {
      writeStatus_(ss, 'ACTION NEEDED', 'New staff link is active, but the previous Form could not be verified closed.', DANGER_RED);
      resultTitle = 'New staff link ready. Old Form needs attention';
      resultBody = 'The Start Here tab has the new unlisted link, and only that Form can trigger alerts.\n\n' + oldRetirementProblem;
    } else {
      writeStatus_(ss, 'READY', 'New unlisted staff link is active. Redistribute it to staff only.', C_GREEN);
      resultTitle = 'New staff link ready';
      resultBody = 'The Start Here tab now has the new link. The old Form is closed and can no longer trigger alerts.';
    }
  } catch (err) {
    props.setProperty(PROP_PAUSED, 'yes');
    if (newForm) { try { setFormAccepting_(newForm, false); } catch (ignored) {} }
    writeStatus_(ss, 'ACTION NEEDED', 'Link replacement did not finish. Alerts are paused.', DANGER_RED);
    resultTitle = 'Link replacement did not finish';
    resultBody = String(err && err.message || err) + '\n\nAlerts remain paused.';
  } finally {
    releaseScriptLock_(acquired.lock);
  }
  ui.alert(resultTitle, resultBody, ui.ButtonSet.OK);
}

function isPaused_() {
  return PropertiesService.getDocumentProperties().getProperty(PROP_PAUSED) === 'yes';
}

function reportSubmissionStopped_(ss, settings, today, detail, takenBy) {
  if (ss) {
    try { logRow_(ss, [new Date(), today, '(submission)', '', '', 'FAILED', detail, takenBy || '']); } catch (ignored) {}
    try { writeStatus_(ss, 'ACTION NEEDED', 'A submission stopped before all alerts were sent. Check Email Log and follow up now.', DANGER_RED); } catch (ignored) {}
  }
  if (settings) {
    notifyContact_(settings,
      'URGENT: Absence report stopped at ' + (settings.school || 'your program'),
      detail + '\n\nDo not assume families were alerted. Review Email Log and contact affected families another way now.', '', true);
  }
}

// ---------------------------------------------------------------------------
// The moment that matters: a form submission arrives
// ---------------------------------------------------------------------------

function handleFormSubmit(e) {
  if (!e || !e.response) return;
  if (!activeTriggerEvent_(e, PROP_ACTIVE_SUBMIT_UID, false)) return;
  var props = PropertiesService.getDocumentProperties();
  var expectedFormId = props.getProperty(PROP_FORM_ID);
  var sourceId = '';
  try { sourceId = e.source && e.source.getId ? e.source.getId() : ''; } catch (ignored) {}
  if (!sourceIdMatches_(sourceId, expectedFormId)) return;

  // Waiting beats dropping: on a busy morning several classes can submit at
  // once, and a submission that gives up is gone forever while the teacher
  // already saw "Done". Two minutes absorbs any realistic queue.
  var acquired = acquireScriptLock_(LOCK_WAIT_SUBMIT_MS);
  if (!acquired.lock) {
    var lockSs = null;
    var lockSettings = null;
    var lockToday = Utilities.formatDate(new Date(), TZ_FALLBACK, 'yyyy-MM-dd');
    var lockClass = '';
    try {
      e.response.getItemResponses().forEach(function (itemResponse) {
        if (String(itemResponse.getItem().getTitle()) === CLASS_QUESTION) {
          lockClass = singleLine_(itemResponse.getResponse(), 250);
        }
      });
    } catch (ignored) {}
    try {
      lockSs = SpreadsheetApp.getActiveSpreadsheet();
      lockSettings = readSettings_(lockSs);
      var lockTz = lockSettings && lockSettings.tz ? lockSettings.tz : TZ_FALLBACK;
      var lockTimestamp = e.response.getTimestamp ? (e.response.getTimestamp() || new Date()) : new Date();
      lockToday = Utilities.formatDate(lockTimestamp, lockTz, 'yyyy-MM-dd');
    } catch (ignored) {}
    reportSubmissionStopped_(lockSs, lockSettings, lockToday,
      'A submission' + (lockClass ? ' for ' + lockClass : '') + ' waited 2 minutes for the safety lock and was not processed. ' +
      'No family alerts were sent for it. Have that group submit the attendance form again now. Lock error: ' + acquired.error, '');
    return;
  }
  var ss = null;
  var settings = null;
  try {
    ss = SpreadsheetApp.getActiveSpreadsheet();
    settings = readSettings_(ss);
    if (!settings) throw new Error('The Start Here tab is missing.');
    var tz = settings.tz;
    var submittedAt = new Date();
    try { submittedAt = e.response.getTimestamp() || submittedAt; } catch (ignored) {}
    var today = Utilities.formatDate(submittedAt, tz, 'yyyy-MM-dd');
    var whenStr = Utilities.formatDate(submittedAt, tz, 'EEEE, MMMM d, yyyy \'at\' h:mm a');

    var lifecycle = currentSubmissionLifecycle_(e, sourceId);
    if (!lifecycle.ok) {
      reportSubmissionStopped_(ss, settings, today,
        'The submission became stale while waiting to run: ' + lifecycle.problems.join(' | ') + '. No family alerts were sent.', '');
      return;
    }

    var config = validateConfiguration_(ss);
    if (config.problems.length > 0) {
      var configDetail = 'Runtime safety check stopped all sends: ' + config.problems.join(' | ');
      logRow_(ss, [new Date(), today, '(configuration)', '', '', 'FAILED', configDetail, '']);
      writeStatus_(ss, 'ACTION NEEDED', 'Roster or settings failed a live safety check. No family alerts were sent.', DANGER_RED);
      notifyContact_(settings,
        'URGENT: Absence Alerts stopped before sending at ' + (settings.school || 'your program'),
        configDetail + '\n\nNo family alerts were sent for this submission. Contact affected families another way now, then open the setup helper and build again.', '', true);
      return;
    }

    var roster = config.rosterResult.roster;
    var formProblems = liveFormSafetyProblems_(e.source, roster);
    if (formProblems.length) {
      var formDetail = 'Live Form safety check stopped all sends: ' + formProblems.join(' | ');
      logRow_(ss, [new Date(), today, '(form)', '', '', 'FAILED', formDetail, '']);
      writeStatus_(ss, 'ACTION NEEDED', 'The live Form no longer matches the roster or anonymous-access contract. No family alerts were sent.', DANGER_RED);
      notifyContact_(settings, 'URGENT: Attendance Form drift stopped alerts at ' + settings.school,
        formDetail + '\n\nNo family alerts were sent. Open the setup helper and build again, then follow up manually.', '', true);
      return;
    }
    var lookup = rosterLookup_(roster);
    var plainAnswers = e.response.getItemResponses().map(function (ir) {
      return { title: ir.getItem().getTitle(), response: ir.getResponse() };
    });
    var submission = parseSubmissionAnswers_(plainAnswers, roster);
    if (!submission.ok) {
      var responseDetail = 'Submission rejected: ' + submission.errors.join(' | ');
      logRow_(ss, [new Date(), today, '(submission)', submission.selectedClass, '', 'FAILED', responseDetail, submission.takenBy]);
      writeStatus_(ss, 'ACTION NEEDED', 'A Form submission did not match the live roster. No family alerts were sent.', DANGER_RED);
      notifyContact_(settings, 'Absence Alerts rejected a mismatched submission at ' + settings.school,
        responseDetail + '\n\nNo family alerts were sent. Open the setup helper and build again to repair the Form and verify the roster.');
      return;
    }

    lifecycle = currentSubmissionLifecycle_(e, sourceId);
    if (!lifecycle.ok) {
      reportSubmissionStopped_(ss, settings, today,
        'The active lifecycle changed after validation: ' + lifecycle.problems.join(' | ') + '. No family alerts were sent.', submission.takenBy);
      return;
    }

    var alreadySent = todaysSentKeys_(ss, today, tz);
    lifecycle = currentSubmissionLifecycle_(e, sourceId);
    if (!lifecycle.ok) {
      reportSubmissionStopped_(ss, settings, today,
        'The active lifecycle changed immediately before acceptance: ' + lifecycle.problems.join(' | ') + '. No family alerts were sent.', submission.takenBy);
      return;
    }
    try { repairLinkCellsFromCache_(ss); } catch (ignored) {}

    var receivedDetail = 'Absence report received. Absent selected: ' + submission.absentees.length + '.';
    if (submission.notes) receivedDetail += ' Office note: ' + submission.notes;
    logRow_(ss, [new Date(), today, '(attendance)', submission.selectedClass, '', 'RECEIVED', receivedDetail, submission.takenBy]);

    var failures = [];
    var quotaLeft = null;
    try { quotaLeft = MailApp.getRemainingDailyQuota(); } catch (err) {}

    // Every recipient decision is planned up front by pure, tested logic.
    // The loop below only executes the plan against Google services.
    // Twilio spending is bounded the same way email volume is: per-contact
    // dedup means one text per family per local day, so even a leaked staff
    // link can cost at most the roster, once.
    var twilio = twilioConfig_();
    settings.autoText = !!twilio;
    var plan = planRecipientSends_(submission.absentees, lookup, settings, alreadySent, quotaLeft);
    var mailCache = {};
    var textTasks = [];
    var lifecycleAbort = '';
    var timeAbort = false;
    var unsent = [];
    var startedAt = new Date().getTime();
    var lastCheckedStudentKey = '';

    // Every family the office must text is handled in a complete first pass,
    // before a single email is sent. These are the families nobody has told
    // yet, and handling them costs no email quota: one log row each, plus one
    // office email at the end. Interleaving them with the sends meant that an
    // abort partway through the plan left a text family with no log row, no
    // entry on the office list, and no mention in the stop report, so that
    // child was reported absent to nobody and no record said so.
    for (var ti = 0; ti < plan.length; ti++) {
      var first = plan[ti];
      if (first.action === 'TEXT_AUTO') {
        // The program's own Twilio account texts this family right now. On
        // any failure the family lands on the manual office list below, so an
        // API outage degrades to exactly the pre-Twilio behavior, never to
        // silence.
        var autoResult = sendTwilioText_(twilio, first.addr, textTaskMessage_(settings, first.name, whenStr));
        if (autoResult.ok) {
          var autoRow = logRow_(ss, [new Date(), today, first.name, first.cls, first.addr, STATUS_TEXT_SENT,
            first.alsoEmailed
              ? 'Texted automatically through Twilio, in addition to email.'
              : 'Texted automatically through Twilio. Nobody was emailed.', submission.takenBy, true]);
          markTextedCheckbox_(ss, autoRow);
          alreadySent[deliveryKey_(first.name, first.cls, telDigits_(first.addr))] = true;
        } else {
          var fallbackRow = logRow_(ss, [new Date(), today, first.name, first.cls, first.addr, STATUS_TEXT_NEEDED,
            'The automatic text failed: ' + autoResult.detail +
            ' Send the text by hand, then tick the box to turn this row green.', submission.takenBy, false]);
          markTextedCheckbox_(ss, fallbackRow);
          textTasks.push({ name: first.name, cls: first.cls, phone: first.addr, alsoEmailed: first.alsoEmailed });
        }
      } else if (first.action === 'TEXT_BY_OFFICE') {
        var textRow = logRow_(ss, [new Date(), today, first.name, first.cls, first.addr, STATUS_TEXT_NEEDED,
          first.alsoEmailed
            ? 'Contacted by text as well as email. Send the text, then tick the box to turn this row green.'
            : 'Contacted by text. Nobody was emailed. Send the text, then tick the box to turn this row green.', submission.takenBy, false]);
        markTextedCheckbox_(ss, textRow);
        textTasks.push({ name: first.name, cls: first.cls, phone: first.addr, alsoEmailed: first.alsoEmailed });
      } else if (first.action === 'SKIP_TEXT_LISTED') {
        logRow_(ss, [new Date(), today, first.name, first.cls, first.addr, 'SKIPPED', 'This family was already texted today, or is already on today\u2019s office text list.', submission.takenBy]);
      } else if (first.action === 'FAIL_NO_PHONE') {
        logRow_(ss, [new Date(), today, first.name, first.cls, '', 'FAILED', 'This student is set to be contacted by text but has no usable phone number. Nobody was alerted. Fix the Students tab, then build again.', submission.takenBy]);
        failures.push(first.name + ': no usable phone number for a text-only family');
      }
    }

    for (var pi = 0; pi < plan.length; pi++) {
      var step = plan[pi];
      if (isTextPlanAction_(step.action)) continue; // settled in the first pass above
      if (step.action === 'FAIL_NOT_FOUND') {
        logRow_(ss, [new Date(), today, step.name, step.cls, '', 'FAILED', 'Student not found on Students tab. Open the setup helper and build again after roster changes.', submission.takenBy]);
        failures.push(step.name + ': not found on the Students tab');
        continue;
      }
      if (step.action === 'FAIL_NO_EMAIL') {
        logRow_(ss, [new Date(), today, step.name, step.cls, '', 'FAILED', 'No family contact email on file for this student. Nobody was alerted. Add an email on the Students tab, then open the setup helper and build again.', submission.takenBy]);
        failures.push(step.name + ': no family contact email on file');
        continue;
      }
      if (step.action === 'SKIP_ALREADY_SENT') {
        logRow_(ss, [new Date(), today, step.name, step.cls, step.addr, 'SKIPPED', 'This contact was already alerted for this student today.', submission.takenBy]);
        continue;
      }
      if (step.action === 'FAIL_QUOTA') {
        logRow_(ss, [new Date(), today, step.name, step.cls, step.addr, 'FAILED', 'Daily Google email quota is at the safety reserve. This contact was NOT emailed.', submission.takenBy]);
        failures.push(step.name + ' (' + step.addr + '): daily email quota exhausted');
        continue;
      }
      // Stop before the Apps Script execution wall so remaining recipients are
      // reported as unsent instead of vanishing when Google kills the run.
      if (new Date().getTime() - startedAt > SEND_TIME_BUDGET_MS) {
        timeAbort = true;
        unsent = unreachedRemainder_(plan, pi);
        break;
      }
      // Re-validate lifecycle once per student, not per recipient: the lock is
      // held, and the pause flag is the emergency brake this check honors.
      var studentKey = dedupKey_(step.name, step.cls);
      if (studentKey !== lastCheckedStudentKey) {
        lifecycle = currentSubmissionLifecycle_(e, sourceId);
        if (!lifecycle.ok) {
          lifecycleAbort = 'The active lifecycle changed immediately before sending to ' + step.addr + ': ' + lifecycle.problems.join(' | ') + '.';
          unsent = unreachedRemainder_(plan, pi);
          break;
        }
        lastCheckedStudentKey = studentKey;
      }
      var mail = mailCache[studentKey];
      if (!mail) {
        mail = buildAlertEmail_(settings, step.name, step.cls, whenStr, false);
        mailCache[studentKey] = mail;
      }
      try {
        // One recipient per message so family contacts never see each other.
        MailApp.sendEmail({
          to: step.addr,
          subject: mail.subject,
          htmlBody: mail.html,
          body: mail.text,
          name: settings.school,
          replyTo: settings.officeEmail
        });
        alreadySent[deliveryKey_(step.name, step.cls, step.addr)] = true;
        logRow_(ss, [new Date(), today, step.name, step.cls, step.addr, 'SENT', '', submission.takenBy]);
      } catch (err) {
        logRow_(ss, [new Date(), today, step.name, step.cls, step.addr, 'FAILED', String(err && err.message || err), submission.takenBy]);
        failures.push(step.name + ' (' + step.addr + '): ' + String(err && err.message || err));
      }
    }

    // The office list goes out even if the email half aborted below: these
    // families were never emailed, so the office needs them either way.
    if (textTasks.length) {
      var task = buildTextTaskEmail_(settings, textTasks, whenStr);
      if (!notifyContact_(settings, task.subject, task.text, task.html, true)) {
        failures.push('the office text list could not be emailed. Open Email Log and text every TEXT NEEDED row by hand.');
        logRow_(ss, [new Date(), today, '(office text list)', submission.selectedClass, settings.officeEmail, 'FAILED',
          'The families needing a text were logged but the office list email could not be sent. Text every TEXT NEEDED row by hand.', submission.takenBy]);
      }
    }

    if (timeAbort || lifecycleAbort) {
      // Some remaining steps have no address (a student who fell off the roster
      // mid-run), so the name alone has to carry the report rather than an
      // empty pair of brackets.
      var unsentList = unsent.map(function (rest) {
        return rest.addr ? (rest.name + ' (' + rest.addr + ')') : rest.name;
      });
      // This branch returns, so it is the last thing the office will hear about
      // this submission. Anything already in failures has to travel with it or
      // it is never reported at all.
      var stopDetail = (timeAbort
        ? 'The submission ran out of safe execution time before every contact was emailed.'
        : lifecycleAbort) +
        (unsentList.length ? ' These contacts were NOT emailed: ' + unsentList.join(', ') + '.' : '') +
        (failures.length ? ' These problems also happened before it stopped: ' + failures.join('; ') + '.' : '') +
        ' Review SENT rows to see which contacts were reached, and contact the rest another way now.';
      reportSubmissionStopped_(ss, settings, today, stopDetail, submission.takenBy);
      return;
    }

    if (submission.notes) {
      lifecycle = currentSubmissionLifecycle_(e, sourceId);
      if (!lifecycle.ok) {
        reportSubmissionStopped_(ss, settings, today,
          'The active lifecycle changed before the office note was sent: ' + lifecycle.problems.join(' | ') + '. The note remains in Email Log.', submission.takenBy);
        return;
      }
      var noteBody = 'Attendance note for ' + submission.selectedClass + ' at ' + settings.school + '.\n\n' +
        submission.notes + '\n\nTaken by: ' + (submission.takenBy || 'Not provided') +
        '\nSubmitted: ' + whenStr + '\nAbsent selected: ' +
        (submission.absentees.length ? submission.absentees.map(function (a) { return a.name; }).join(', ') : 'None');
      if (!notifyContact_(settings, 'Attendance note: ' + submission.selectedClass + ' at ' + settings.school, noteBody)) {
        failures.push(submission.selectedClass + ': office note could not be emailed');
        logRow_(ss, [new Date(), today, '(office note)', submission.selectedClass, settings.officeEmail, 'FAILED', 'The office note was logged but its email could not be sent.', submission.takenBy]);
      }
    }

    if (failures.length > 0) {
      setProblemStatus_(ss, tz, failures.length);
      notifyContact_(settings,
        'Absence Alerts: ' + failures.length + ' alert(s) FAILED at ' + settings.school,
        'These absence alerts could not be sent:\n\n' + failures.join('\n') +
        '\n\nPlease notify these families another way right now, then check the Email Log tab.\n' +
        'Help: ' + SITE_URL, '', true);
    }
  } catch (err) {
    var message = 'Unexpected submission error: ' + String(err && err.message || err);
    if (ss) {
      var fallbackTz = settings && settings.tz ? settings.tz : TZ_FALLBACK;
      try { logRow_(ss, [new Date(), Utilities.formatDate(new Date(), fallbackTz, 'yyyy-MM-dd'), '(system)', '', '', 'FAILED', message, '']); } catch (ignored) {}
      try { writeStatus_(ss, 'ACTION NEEDED', 'An automatic submission failed. Check Email Log and contact families manually.', DANGER_RED); } catch (ignored) {}
    }
    if (settings) notifyContact_(settings, 'URGENT: Absence Alerts execution failed', message + '\n\nCheck the Email Log and contact affected families another way now.', '', true);
    try { console.error(message); } catch (ignored) {}
  } finally {
    releaseScriptLock_(acquired.lock);
  }
}

// urgent marks a notice that reports a family was not reached. Those are
// counted against their own budget so ordinary traffic can never silence one.
function notifyContact_(settings, subject, body, htmlBody, urgent) {
  if (!settings || !isValidEmail_(settings.officeEmail)) return false;
  try {
    // The unlisted link means anyone holding it can generate rejections, and
    // every rejection wants to email the office. The daily cap turns a leaked
    // link from a quota-draining inbox flood into at most a capped burst; the
    // Email Log still records every event, capped or not.
    var props = PropertiesService.getDocumentProperties();
    var todayKey = Utilities.formatDate(new Date(), settings.tz || TZ_FALLBACK, 'yyyy-MM-dd');
    var countKey = urgent ? PROP_URGENT_NOTICE_COUNT : PROP_OFFICE_NOTICE_COUNT;
    var cap = urgent ? URGENT_NOTICE_DAILY_CAP : OFFICE_NOTICE_DAILY_CAP;
    var allowance = officeNoticeAllowance_(props.getProperty(countKey), todayKey, cap);
    if (!allowance.allowed) {
      try { console.error('Office notice suppressed by the daily cap of ' + cap + ': ' + subject); } catch (ignored) {}
      return false;
    }
    var quota = null;
    try { quota = MailApp.getRemainingDailyQuota(); } catch (ignored) {}
    if (quota !== null && quota <= 0) return false;
    var message = {
      to: settings.officeEmail,
      subject: subject,
      body: body,
      name: 'Absence Alerts'
    };
    if (htmlBody) message.htmlBody = htmlBody;
    MailApp.sendEmail(message);
    props.setProperty(countKey, todayKey + '|' + (allowance.count + 1));
    return true;
  } catch (err) {
    try { console.error('Could not email the office: ' + err); } catch (ignored) {}
    return false;
  }
}

function reportWatchdogFailure_(detail, ss, settings) {
  var targetSs = ss;
  var targetSettings = settings;
  try { if (!targetSs) targetSs = SpreadsheetApp.getActiveSpreadsheet(); } catch (ignored) {}
  try { if (!targetSettings && targetSs) targetSettings = readSettings_(targetSs); } catch (ignored) {}
  var tz = targetSettings && targetSettings.tz ? targetSettings.tz : TZ_FALLBACK;
  var today = Utilities.formatDate(new Date(), tz, 'yyyy-MM-dd');
  if (targetSs) {
    try { logRow_(targetSs, [new Date(), today, '(system)', '', '', 'FAILED', 'Morning watchdog failure: ' + detail, '']); } catch (ignored) {}
    try { writeStatus_(targetSs, 'ACTION NEEDED', 'Morning watchdog failed. Check attendance manually and review Email Log.', DANGER_RED); } catch (ignored) {}
  }
  if (targetSettings) {
    notifyContact_(targetSettings, 'URGENT: Attendance morning watchdog failed at ' + (targetSettings.school || 'your program'),
      detail + '\n\nCheck attendance manually now and review the Email Log.', '', true);
  }
  try { console.error('Morning watchdog failure: ' + detail); } catch (ignored) {}
}

function setProblemStatus_(ss, tz, failCount) {
  var stamp = Utilities.formatDate(new Date(), tz, 'MMMM d, yyyy h:mm a');
  writeStatus_(ss, 'ACTION NEEDED', failCount + ' alert(s) failed at ' + stamp + '. Check Email Log now.', DANGER_RED);
}

function todaysSentKeys_(ss, today, tz) {
  var out = {};
  var sh = ss.getSheetByName(TAB_LOG);
  if (!sh) return out;
  var last = sh.getLastRow();
  if (last < 2) return out;
  var values = sh.getRange(2, 1, last - 1, 6).getValues();
  values.forEach(function (row) {
    if (normalizeDateCell_(row[1], tz) !== today) return;
    var status = String(row[5]);
    if (status === 'SENT') {
      // Key includes the exact address so dedup is per contact, not per student.
      out[deliveryKey_(row[2], row[3], row[4])] = true;
    } else if (status === STATUS_TEXT_NEEDED || status === STATUS_TEXT_SENT) {
      // Already texted automatically, or already on today's office list.
      // Either way, a repeat submission must not text the family again. Keyed
      // by dial digits, matching how the planner checks a roster phone.
      out[deliveryKey_(row[2], row[3], telDigits_(row[4]))] = true;
    }
  });
  return out;
}

function todaysAcceptedClasses_(ss, today, tz, expectedClasses) {
  var sh = ss.getSheetByName(TAB_LOG);
  if (!sh || sh.getLastRow() < 2) return [];
  var rows = sh.getRange(2, 1, sh.getLastRow() - 1, 6).getValues();
  return acceptedClassesFromLogRows_(rows, today, tz, expectedClasses);
}

function logRow_(ss, arr) {
  var sh = ss.getSheetByName(TAB_LOG);
  if (!sh) {
    sh = ss.insertSheet(TAB_LOG);
    sh.getRange(1, 1, 1, LOG_HEADERS.length).setValues([LOG_HEADERS.slice()]);
  }
  var safe = [];
  for (var i = 0; i < LOG_HEADERS.length; i++) {
    var value = i < arr.length ? arr[i] : '';
    if (i === 1) {
      // The Date column must stay text: if Sheets coerced it into a Date
      // value, a later spreadsheet-timezone change would shift history a day
      // and silently reset same-day dedup. The apostrophe is Sheets' own
      // text marker and is not displayed.
      safe.push("'" + cleanText_(value, 40));
    } else {
      safe.push(value instanceof Date || typeof value === 'number' || typeof value === 'boolean' ? value : safeCellText_(value, MAX_LOG_TEXT));
    }
  }
  // appendRow is atomic against concurrent appends. A computed next-row write
  // would let two executions (for example a lock-timeout report and the lock
  // holder) overwrite each other's audit rows and corrupt dedup history.
  sh.appendRow(safe);
  var writtenRow = sh.getLastRow();
  // The caller may need this index to place the Texted checkbox. Most sends
  // hold the script lock, but preview sends and the lock-timeout report do
  // not, so getLastRow can overshoot onto a row someone else just appended.
  // Confirm the row really is the one we wrote, and walk back if it is not,
  // otherwise the checkbox lands on a stranger's row and the TEXT NEEDED row
  // stays red with no box for the office to tick.
  try {
    var probeFrom = Math.max(2, writtenRow - 5);
    var probe = sh.getRange(probeFrom, 1, writtenRow - probeFrom + 1, LOG_HEADERS.length).getValues();
    for (var p = probe.length - 1; p >= 0; p--) {
      if (String(probe[p][2]) === String(safe[2]) &&
          String(probe[p][4]) === String(safe[4]) &&
          String(probe[p][5]) === String(safe[5])) {
        writtenRow = probeFrom + p;
        break;
      }
    }
  } catch (ignored) {}
  try {
    sh.getRange(writtenRow, 1, 1, LOG_HEADERS.length).setWrap(true).setVerticalAlignment('middle');
  } catch (ignored) {}
  return writtenRow;
}

// ---------------------------------------------------------------------------
// Twilio runtime: the network call, the saved connection, and the setup dialog
// server half. Everything here runs only when a program has connected its own
// Twilio account; without one, twilioConfig_ returns null and no code below
// ever executes.
// ---------------------------------------------------------------------------

function twilioConfig_() {
  var props = PropertiesService.getScriptProperties();
  var sid = String(props.getProperty(PROP_TWILIO_SID) || '').trim();
  var token = String(props.getProperty(PROP_TWILIO_TOKEN) || '').trim();
  var from = String(props.getProperty(PROP_TWILIO_FROM) || '').trim();
  if (!looksLikeTwilioSid_(sid) || !token || !from) return null;
  return {
    sid: sid,
    token: token,
    from: from,
    cc: normalizeCountryCode_(props.getProperty(PROP_TWILIO_CC))
  };
}

// One POST to Twilio's Messages endpoint. Returns { ok, detail } and never
// throws: the caller decides what a failure means (fall back to the office
// list, or show the dialog an error). The Auth Token is used only inside the
// Authorization header and never appears in any log, error, or dialog.
function sendTwilioText_(cfg, toRaw, body) {
  if (!cfg) return { ok: false, detail: 'Automatic texting is not set up.' };
  var to = twilioE164_(toRaw, cfg.cc);
  if (!to) {
    return { ok: false, detail: 'The number "' + String(toRaw || '').trim() + '" could not be converted to a full international number.' };
  }
  var payload = { To: to, Body: String(body || '') };
  payload[twilioFromField_(cfg.from)] = cfg.from;
  try {
    var resp = UrlFetchApp.fetch(TWILIO_API_BASE + encodeURIComponent(cfg.sid) + '/Messages.json', {
      method: 'post',
      payload: payload,
      headers: { Authorization: 'Basic ' + Utilities.base64Encode(cfg.sid + ':' + cfg.token) },
      muteHttpExceptions: true
    });
    return twilioResult_(resp.getResponseCode(), resp.getContentText());
  } catch (err) {
    return { ok: false, detail: 'Twilio could not be reached: ' + String(err && err.message || err) };
  }
}

// Shared response reading for sends and the credential probe, so every Twilio
// error surfaces as the same short human sentence.
function twilioResult_(httpCode, bodyText) {
  if (httpCode >= 200 && httpCode < 300) return { ok: true, detail: '' };
  var detail = '';
  try {
    var parsed = JSON.parse(bodyText);
    if (parsed && parsed.message) {
      detail = String(parsed.message);
      if (parsed.code) detail += ' (Twilio error ' + parsed.code + ')';
    }
  } catch (ignored) {}
  if (!detail) detail = 'Twilio replied with HTTP ' + httpCode + '.';
  if (httpCode === 401) detail += ' Check the Account SID and Auth Token.';
  return { ok: false, detail: detail };
}

// GET the account itself: proves the SID and Auth Token are real without
// sending a message or spending anything.
function twilioProbe_(cfg) {
  try {
    var resp = UrlFetchApp.fetch(TWILIO_API_BASE + encodeURIComponent(cfg.sid) + '.json', {
      method: 'get',
      headers: { Authorization: 'Basic ' + Utilities.base64Encode(cfg.sid + ':' + cfg.token) },
      muteHttpExceptions: true
    });
    return twilioResult_(resp.getResponseCode(), resp.getContentText());
  } catch (err) {
    return { ok: false, detail: 'Twilio could not be reached: ' + String(err && err.message || err) };
  }
}

function showTextingSetup() {
  ensureTabs_(SpreadsheetApp.getActiveSpreadsheet());
  var html = HtmlService.createHtmlOutputFromFile('TextingSetup')
    .setWidth(430).setHeight(560);
  SpreadsheetApp.getUi().showModalDialog(html, 'Automatic texts');
}

// Dialog server half. Only ever returns masked identifiers; the Auth Token
// goes into Script Properties and is never read back out to any interface.
function textingStatus() {
  var cfg = twilioConfig_();
  if (!cfg) return { on: false };
  return { on: true, sid: maskTwilioSid_(cfg.sid), from: cfg.from, cc: cfg.cc };
}

function saveTextingSetup(data) {
  data = data || {};
  var sid = String(data.sid || '').trim();
  var token = String(data.token || '').trim();
  var fromRaw = String(data.from || '').trim();
  var cc = normalizeCountryCode_(data.cc);
  if (!looksLikeTwilioSid_(sid)) {
    return { ok: false, detail: 'That does not look like an Account SID. It starts with AC and is 34 characters long, from the Twilio Console home page.' };
  }
  if (!token) return { ok: false, detail: 'Paste the Auth Token from the Twilio Console home page.' };
  var from = twilioFromField_(fromRaw) === 'MessagingServiceSid' ? fromRaw : twilioE164_(fromRaw, cc);
  if (!from) {
    return { ok: false, detail: 'The sending number must be a full number like +13055550123 (or a Messaging Service SID starting with MG). Copy it from Phone Numbers in the Twilio Console.' };
  }
  var probe = twilioProbe_({ sid: sid, token: token });
  if (!probe.ok) return { ok: false, detail: probe.detail };
  var props = PropertiesService.getScriptProperties();
  props.setProperty(PROP_TWILIO_SID, sid);
  props.setProperty(PROP_TWILIO_TOKEN, token);
  props.setProperty(PROP_TWILIO_FROM, from);
  props.setProperty(PROP_TWILIO_CC, cc);
  return { ok: true, detail: 'Connected. Families set to Text will now be texted automatically.', status: textingStatus() };
}

function sendTextingTest(number) {
  var cfg = twilioConfig_();
  if (!cfg) return { ok: false, detail: 'Save the Twilio details first.' };
  var settings = readSettings_(SpreadsheetApp.getActiveSpreadsheet());
  var school = settings && settings.school ? settings.school : 'your program';
  var result = sendTwilioText_(cfg, String(number || ''),
    school + ': this is a test of automatic absence texts. It worked.');
  if (result.ok) return { ok: true, detail: 'Test text sent. Check that phone.' };
  return result;
}

function disableTexting() {
  var props = PropertiesService.getScriptProperties();
  props.deleteProperty(PROP_TWILIO_SID);
  props.deleteProperty(PROP_TWILIO_TOKEN);
  props.deleteProperty(PROP_TWILIO_FROM);
  props.deleteProperty(PROP_TWILIO_CC);
  return { ok: true, detail: 'Automatic texts are off. The office text list is back to manual.' };
}

// Only rows that owe a family a text get a checkbox, so the column stays quiet
// everywhere else and a ticked box always means a real person sent a real text.
function markTextedCheckbox_(ss, row) {
  if (!row) return;
  try {
    var sh = ss.getSheetByName(TAB_LOG);
    if (!sh) return;
    sh.getRange(row, LOG_HEADERS.length)
      .setDataValidation(SpreadsheetApp.newDataValidation().requireCheckbox().build())
      .setHorizontalAlignment('center');
  } catch (ignored) {}
}

// The office list of families to text by hand. Every entry is one tap on a
// phone: the link opens the messaging app with the number and the message
// already written. The plain number and message are repeated underneath,
// because prefill support varies by handset and the office must never be stuck.
function buildTextTaskEmail_(settings, tasks, whenStr) {
  var org = esc_(settings.school);
  var count = tasks.length;
  var noun = count === 1 ? 'family' : 'families';
  var subject = 'ACTION NEEDED: text ' + count + ' ' + noun + ' now, ' + settings.school;
  var accent = normalizeColor_(settings.accent);
  // A family set to "Email and text" HAS already been emailed, so the urgent
  // "nobody has been told yet" line is only true when every family on the list
  // is text-only. Saying it otherwise made the office email and the ledger
  // contradict a SENT row sitting directly above them.
  var allTextOnly = tasks.every(function (t) { return !t.alsoEmailed; });
  var leadHtml = allTextOnly
    ? org + ' has no email for these families, so nobody has been told yet. Tap a family, then Open messaging app, then send.'
    : org + ' needs a text sent to these families. Tap a family, then Open messaging app, then send.';
  var leadText = allTextOnly
    ? 'No email for these families, so nobody has been told yet. Text each one, then tick the Texted box in the Email Log.'
    : 'These families need a text from the office. Text each one, then tick the Texted box in the Email Log.';
  var rows = '';
  var lines = [];
  tasks.forEach(function (task) {
    var message = textTaskMessage_(settings, task.name, whenStr);
    var link = textBridgeLink_(task.phone, message);
    var dial = telDigits_(task.phone);
    rows +=
      '<tr><td style="padding:16px 0;border-top:1px solid #e7e2d4;">' +
        '<div style="font-family:Arial,Helvetica,sans-serif;font-size:17px;font-weight:bold;color:#16243d;">' + esc_(task.name) + '</div>' +
        '<div style="font-family:Arial,Helvetica,sans-serif;font-size:13px;color:#6b7280;padding:2px 0 12px;">' + esc_(task.cls) + (task.alsoEmailed ? ' \u00b7 also emailed' : '') + '</div>' +
        (link ? '<div style="padding:0 0 10px;"><a href="' + link + '" style="display:inline-block;background:' + accent + ';color:#ffffff;font-family:Arial,Helvetica,sans-serif;font-size:15px;font-weight:bold;text-decoration:none;padding:13px 20px;border-radius:7px;">Text ' + esc_(task.name) + '\u2019s family</a></div>' : '') +
        '<div style="font-family:Arial,Helvetica,sans-serif;font-size:14px;color:#333c4d;">Number: <a href="tel:' + dial + '" style="color:' + accent + ';font-weight:bold;">' + esc_(task.phone) + '</a></div>' +
        '<div style="font-family:Arial,Helvetica,sans-serif;font-size:13px;line-height:1.55;color:#333c4d;background:#f6f4ee;border:1px solid #e7e2d4;border-radius:6px;padding:10px 12px;margin-top:8px;">' + esc_(message) + '</div>' +
      '</td></tr>';
    lines.push('- ' + task.name + ' (' + task.cls + (task.alsoEmailed ? ', also emailed' : '') + '), ' + task.phone + '\n  ' + message);
  });
  var html =
    '<table role="presentation" width="100%" cellpadding="0" cellspacing="0" style="background:#f6f4ee;"><tr><td align="center" style="padding:28px 14px;">' +
    '<table role="presentation" width="100%" cellpadding="0" cellspacing="0" style="max-width:560px;text-align:left;">' +
    '<tr><td style="background:#ffffff;border:1px solid #e7e2d4;border-top:4px solid ' + DANGER_RED + ';border-radius:10px;padding:26px 24px;">' +
    '<div style="font-family:Arial,Helvetica,sans-serif;font-size:11px;letter-spacing:2px;text-transform:uppercase;color:' + DANGER_RED + ';font-weight:bold;">Action needed</div>' +
    '<div style="font-family:Georgia,\'Times New Roman\',serif;font-size:23px;line-height:1.3;font-weight:bold;color:#16243d;padding:8px 0 6px;">Text ' + count + ' ' + noun + ' now</div>' +
    '<div style="font-family:Arial,Helvetica,sans-serif;font-size:14px;line-height:1.6;color:#333c4d;padding:0 0 4px;">' + leadHtml +
    '</div>' +
    '<table role="presentation" width="100%" cellpadding="0" cellspacing="0">' + rows + '</table>' +
    '<div style="font-family:Arial,Helvetica,sans-serif;font-size:12.5px;line-height:1.7;color:#8a8577;border-top:1px solid #e7e2d4;padding:14px 0 0;margin-top:6px;">' +
    'After sending, tick the Texted box in the Email Log. The row turns green once you do.' +
    (settings.autoText ? '' : ' Prefer these to send themselves? Connect your own Twilio account under ' + esc_(MENU_NAME) + ', Set up automatic texts.') +
    '</div>' +
    '</td></tr></table></td></tr></table>';
  var text = 'ACTION NEEDED at ' + settings.school + '\n\n' +
    leadText + '\n\n' +
    lines.join('\n\n') + '\n' +
    (settings.autoText ? '' : '\nPrefer these to send themselves? Connect your own Twilio account: ' + MENU_NAME + ' menu, Set up automatic texts.\n');
  return { subject: subject, html: html, text: text };
}

// ---------------------------------------------------------------------------
// The email family contacts receive
// ---------------------------------------------------------------------------

function buildAlertEmail_(settings, name, cls, whenStr, isTest) {
  var org = settings.school;
  var accent = settings.accent || DEFAULT_ACCENT;
  var btnText = readableText_(accent);
  var subject = (isTest ? '[PREVIEW] ' : '') + 'Attendance check: ' + name + ' was marked absent';

  var telHref = telDigits_(settings.phone);
  var eName = esc_(name);
  var eOrg = esc_(org);
  var eCls = esc_(cls);
  var eWhen = esc_(whenStr);
  var ePhone = esc_(settings.phone);

  var previewRow = isTest
    ? '<tr><td style="padding:0 0 16px;"><div style="border:1px solid #e3ddcc;background:#fbf9f2;border-radius:8px;padding:11px 14px;font-family:Arial,Helvetica,sans-serif;font-size:13px;color:#5a6170;">This is a preview sent from the menu. Real alerts are triggered by the attendance form and sent to family contacts.</div></td></tr>'
    : '';
  var logoRow = settings.logoUrl
    ? '<tr><td style="padding:0 0 14px;"><img src="' + esc_(settings.logoUrl) + '" alt="' + eOrg + '" height="48" style="height:48px;max-height:48px;width:auto;display:block;border:0;outline:none;"></td></tr>'
    : '';
  var callRow = telHref
    ? '<tr><td style="padding:6px 0 22px;"><a href="tel:' + telHref + '" style="display:block;background:' + accent + ';color:' + btnText + ';text-decoration:none;font-weight:bold;font-size:17px;padding:16px 14px;border-radius:9px;text-align:center;font-family:Arial,Helvetica,sans-serif;">Call ' + eOrg + ' &#183; ' + ePhone + '</a></td></tr>'
    : '';

  var html =
    '<table role="presentation" width="100%" cellpadding="0" cellspacing="0" style="background:#f6f4ee;"><tr><td align="center" style="padding:32px 14px;">' +
    '<table role="presentation" width="100%" cellpadding="0" cellspacing="0" style="max-width:520px;text-align:left;">' +
    previewRow +
    '<tr><td style="background:#ffffff;border:1px solid #e7e2d4;border-top:4px solid ' + accent + ';border-radius:10px;padding:30px 28px;">' +
    '<table role="presentation" width="100%" cellpadding="0" cellspacing="0" style="text-align:left;">' +
    logoRow +
    '<tr><td style="font-family:Arial,Helvetica,sans-serif;font-size:16px;font-weight:bold;color:#16243d;padding:0 0 2px;">' + eOrg + '</td></tr>' +
    '<tr><td style="font-family:Arial,Helvetica,sans-serif;font-size:11px;letter-spacing:2px;text-transform:uppercase;color:#8a8577;padding:0 0 18px;border-bottom:1px solid #e7e2d4;">Attendance check</td></tr>' +
    '<tr><td style="font-family:Georgia,\'Times New Roman\',serif;font-size:25px;line-height:1.3;font-weight:bold;color:#16243d;padding:22px 0 12px;">' + eName + ' was marked absent today.</td></tr>' +
    '<tr><td style="font-family:Arial,Helvetica,sans-serif;font-size:15px;line-height:1.65;color:#333c4d;padding:0 0 14px;">' + eName + ' (' + eCls + ') was marked absent at ' + eOrg + ' on ' + eWhen + '.</td></tr>' +
    '<tr><td style="font-family:Arial,Helvetica,sans-serif;font-size:16px;line-height:1.6;font-weight:bold;color:#16243d;padding:0 0 8px;">If ' + eName + ' was expected to be there, please confirm their location and call now.</td></tr>' +
    callRow +
    '<tr><td style="font-family:Arial,Helvetica,sans-serif;font-size:14.5px;line-height:1.6;color:#333c4d;padding:0 0 20px;">If this absence is expected, no action is needed.</td></tr>' +
    '<tr><td style="font-family:Arial,Helvetica,sans-serif;font-size:12.5px;line-height:1.7;color:#8a8577;border-top:1px solid #e7e2d4;padding:16px 0 0;">' +
    'If you received this for the wrong child, or the record is wrong, reply to this email.<br>' +
    'Sent automatically by ' + eOrg + '. Replies go to the office.</td></tr>' +
    '</table></td></tr></table></td></tr></table>';

  var text =
    (isTest ? 'PREVIEW. Real alerts are triggered by the attendance form.\n\n' : '') +
    org + ' | Attendance check\n\n' +
    name + ' (' + cls + ') was marked absent at ' + org + ' on ' + whenStr + '.\n\n' +
    'If ' + name + ' was expected to be there, please confirm their location and call ' + org + ' now' +
    (settings.phone ? ' at ' + settings.phone : '') + '.\n\n' +
    'If this absence is expected, no action is needed.\n\n' +
    'If you received this for the wrong child, or the record is wrong, reply to this email.\n' +
    'Sent automatically by ' + org + '. Replies go to the office.';

  return { subject: subject, html: html, text: text };
}

// ---------------------------------------------------------------------------
// Preview alert
// ---------------------------------------------------------------------------

function sendPreview_(ss) {
  var settings = readSettings_(ss);
  if (!settings || !isValidEmail_(settings.officeEmail)) {
    return { ok: false, title: 'Office email needed', message: 'Add a valid office email.first.' };
  }
  var whenStr = Utilities.formatDate(new Date(), settings.tz, 'EEEE, MMMM d, yyyy \'at\' h:mm a');
  if (!settings.school) settings.school = 'Your School';
  var mail = buildAlertEmail_(settings, 'Sample Student', 'Sample Group', whenStr, true);
  try {
    MailApp.sendEmail({
      to: settings.officeEmail,
      subject: mail.subject,
      htmlBody: mail.html,
      body: mail.text,
      name: settings.school,
      replyTo: settings.officeEmail
    });
    logRow_(ss, [new Date(), Utilities.formatDate(new Date(), settings.tz, 'yyyy-MM-dd'), 'Sample Student', 'Sample Group', settings.officeEmail, 'PREVIEW', 'Preview alert sent from the menu.', '']);
    return {
      ok: true,
      title: 'Preview sent',
      message: 'A preview was emailed to ' + settings.officeEmail + '. Check that inbox and look in spam the first time.'
    };
  } catch (err) {
    return { ok: false, title: 'Preview could not be sent', message: 'The preview failed: ' + String(err && err.message || err) };
  }
}

function testAlert() {
  var ui = SpreadsheetApp.getUi();
  var result = sendPreview_(SpreadsheetApp.getActiveSpreadsheet());
  ui.alert(result.title, result.message, ui.ButtonSet.OK);
}

// ---------------------------------------------------------------------------
// Morning watchdog: which classes have not taken attendance
// ---------------------------------------------------------------------------

function morningCheck(e) {
  if (!activeTriggerEvent_(e, PROP_ACTIVE_MORNING_UID, true)) return;
  var acquired = acquireScriptLock_(10000);
  if (!acquired.lock) {
    reportWatchdogFailure_('The watchdog could not acquire its 10-second safety lock. ' + acquired.error, null, null);
    return;
  }
  var ss = null;
  var settings = null;
  try {
    ss = SpreadsheetApp.getActiveSpreadsheet();
    if (!activeTriggerEvent_(e, PROP_ACTIVE_MORNING_UID, true) || isPaused_()) return;
    var config = validateConfiguration_(ss);
    settings = config.settings;
    if (config.problems.length) throw new Error('Runtime configuration check failed: ' + config.problems.join(' | '));
    if (!settings || !isValidEmail_(settings.officeEmail)) throw new Error('The office email is unavailable.');
    var tz = settings.tz;
    var now = new Date();
    var currentHour = parseInt(Utilities.formatDate(now, tz, 'H'), 10);
    // Trigger schedules can outlive a Sheet edit. Read B10 live so changing it
    // to Off disables an old trigger immediately, and changing the hour cannot
    // send at the old hour before setup installs the replacement schedule.
    if (!shouldRunMorningReminder_(settings.morningHour, currentHour)) return;
    var day = parseInt(Utilities.formatDate(now, tz, 'u'), 10) % 7;
    if (isWeekend_(day)) return;
    var props = PropertiesService.getDocumentProperties();
    var expected = groupRosterByClass_(config.rosterResult.roster).map(function (g) { return g.cls; });
    if (!expected.length) return;
    var today = Utilities.formatDate(now, tz, 'yyyy-MM-dd');
    // Only a handler-validated RECEIVED audit row counts as coverage. Raw Form
    // responses can be paused, stale, malformed, or rejected after roster drift.
    var covered = todaysAcceptedClasses_(ss, today, tz, expected);
    var missing = missingClasses_(expected, covered);
    if (!missing.length) return;

    var reminderKey = reminderKey_(today);
    if (props.getProperty(PROP_LAST_REMINDER_KEY) === reminderKey) return;
    var timeStr = Utilities.formatDate(now, tz, 'h:mm a');
    var liveSettings = readSettings_(ss);
    if (!activeTriggerEvent_(e, PROP_ACTIVE_MORNING_UID, true) || isPaused_() ||
        !liveSettings || morningScheduleKey_(liveSettings.morningHour, liveSettings.tz) !== morningScheduleKey_(settings.morningHour, settings.tz) ||
        !shouldRunMorningReminder_(liveSettings.morningHour, currentHour)) return;
    var sent = notifyContact_(settings,
      'Absence form reminder: ' + missing.length + ' group(s) have not submitted at ' + settings.school,
      'As of ' + timeStr + ', these classes or groups have not submitted the absence form today at ' + settings.school + ':\n\n' +
      missing.map(function (c) { return '- ' + c; }).join('\n') +
      '\n\nIf the day is in session, make sure each group submits the absence form now. Absence alerts only go out after a real submission.\n\n' +
      'If today is a holiday or day off, ignore this.\n\nSent automatically by Absence Alerts. ' + SITE_URL);
    if (sent) {
      props.setProperty(PROP_LAST_REMINDER_KEY, reminderKey);
      logRow_(ss, [new Date(), today, '(system)', missing.join(', '), settings.officeEmail, 'REMINDER', 'One morning reminder sent for the missing groups.', '']);
    } else {
      logRow_(ss, [new Date(), today, '(system)', missing.join(', '), settings.officeEmail, 'FAILED', 'Morning reminder email could not be sent.', '']);
      writeStatus_(ss, 'ACTION NEEDED', 'Morning reminder could not be emailed. Check attendance manually.', DANGER_RED);
    }
  } catch (err) {
    reportWatchdogFailure_(String(err && err.message || err), ss, settings);
  } finally {
    releaseScriptLock_(acquired.lock);
  }
}

// ---------------------------------------------------------------------------
// Pause and resume
// ---------------------------------------------------------------------------

function pauseAlerts() {
  var ui = SpreadsheetApp.getUi();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var settings = readSettings_(ss);
  var props = PropertiesService.getDocumentProperties();
  // Pause is an emergency brake: publish the shared flag immediately so an
  // in-flight handler stops before its next recipient, then serialize cleanup.
  props.setProperty(PROP_PAUSED, 'yes');
  var acquired = acquireScriptLock_(30000);
  var tz = settings && settings.tz ? settings.tz : TZ_FALLBACK;
  if (!acquired.lock) {
    writeStatus_(ss, 'ACTION NEEDED', 'Alerts are blocked, but Form closure could not get the safety lock. Try Pause again.', DANGER_RED);
    ui.alert('Alerts blocked. Closure still needs attention',
      'The shared pause flag is on, so handlers will not continue sending. The staff Form and triggers could not be verified closed because another operation stayed busy. Run Pause alerts again.\n\n' + acquired.error,
      ui.ButtonSet.OK);
    return;
  }
  var closeProblem = '';
  var triggerProblem = '';
  try {
    var formId = props.getProperty(PROP_FORM_ID);
    if (formId) {
      try { setFormAccepting_(FormApp.openById(formId), false); }
      catch (err) { closeProblem = 'The staff Form could not be verified closed: ' + String(err && err.message || err); }
    }
    try { deleteOurTriggers_(); }
    catch (err) { triggerProblem = 'This account could not remove all of its triggers: ' + String(err && err.message || err); }
  } finally {
    releaseScriptLock_(acquired.lock);
  }
  var problem = [closeProblem, triggerProblem].filter(function (value) { return !!value; }).join(' ');
  if (problem) {
    writeStatus_(ss, 'ACTION NEEDED', 'Alerts are blocked by the pause flag, but cleanup needs attention.', DANGER_RED);
    ui.alert('Alerts blocked. Cleanup needs attention', problem + '\n\nNo handler will send while the shared pause flag remains on. Try Pause alerts again or close the Form manually.', ui.ButtonSet.OK);
  } else {
    writeStatus_(ss, 'PAUSED', 'Form closed and alerts paused since ' + Utilities.formatDate(new Date(), tz, 'MMMM d, yyyy h:mm a') + '.', C_GOLD);
    ui.alert('Alerts paused', 'The staff Form is closed and no alerts will be sent until you use Resume alerts.', ui.ButtonSet.OK);
  }
}

function resumeAlerts() {
  var ui = SpreadsheetApp.getUi();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var initialConfig = validateConfiguration_(ss);
  if (initialConfig.problems.length) {
    ui.alert('Fix these before resuming', initialConfig.problems.slice(0, 15).join('\n\n'), ui.ButtonSet.OK);
    return;
  }
  var props = PropertiesService.getDocumentProperties();
  props.setProperty(PROP_PAUSED, 'yes');
  var acquired = acquireScriptLock_(30000);
  if (!acquired.lock) {
    writeStatus_(ss, 'ACTION NEEDED', 'Resume could not get the safety lock. Alerts remain paused.', DANGER_RED);
    ui.alert('Could not resume', 'Another attendance operation stayed busy. Alerts remain paused; try Resume again.\n\n' + acquired.error, ui.ButtonSet.OK);
    return;
  }
  var config = null;
  var form = null;
  var resumeError = null;
  try {
    config = validateConfiguration_(ss);
    if (config.problems.length) throw new Error('Setup data changed or failed validation: ' + config.problems.join(' | '));
    var formId = props.getProperty(PROP_FORM_ID);
    if (formId) { try { form = FormApp.openById(formId); } catch (ignored) {} }
    if (!form) throw new Error('There is no accessible attendance Form for this Sheet. Open the setup helper and build the system instead.');
    setFormAccepting_(form, false);
    configureForm_(form, ss, config.settings);
    rebuildFormItems_(form, config.rosterResult.roster);
    var formProblems = liveFormSafetyProblems_(form, config.rosterResult.roster);
    if (formProblems.length) throw new Error(formProblems.join(' | '));
    installTriggers_(form, config.settings);
    setVersionProperties_();
    updateFormLinks_(ss, form);
    setFormAccepting_(form, true);
    if (form.supportsAdvancedResponderPermissions && form.supportsAdvancedResponderPermissions() && !form.isPublished()) {
      throw new Error('The attendance Form was not published for responders.');
    }
    props.deleteProperty(PROP_PAUSED);
    writeStatus_(ss, 'READY', 'Alerts resumed and verified ' + Utilities.formatDate(new Date(), config.settings.tz, 'MMMM d, yyyy h:mm a') + '.', C_GREEN);
  } catch (err) {
    resumeError = err;
    props.setProperty(PROP_PAUSED, 'yes');
    if (form) { try { setFormAccepting_(form, false); } catch (ignored) {} }
    writeStatus_(ss, 'ACTION NEEDED', 'Resume did not finish. Alerts remain paused.', DANGER_RED);
  } finally {
    releaseScriptLock_(acquired.lock);
  }
  if (resumeError) {
    ui.alert('Could not resume', String(resumeError && resumeError.message || resumeError) + '\n\nAlerts remain paused.', ui.ButtonSet.OK);
    return;
  }
  ui.alert('Alerts resumed', 'Roster, Form, and active-trigger checks passed. Absence alerts are back on.', ui.ButtonSet.OK);
}

function writeStatus_(ss, label, detail, color) {
  try {
    var sh = ss.getSheetByName(TAB_START);
    if (!sh) return;
    sh.getRange(CELL_STATUS).setValue(label + '  \u2022  ' + detail + '  \u2022  v' + APP_VERSION);
    var style = statusStyleFromText_(label);
    sh.getRange(CELL_STATUS).setBackground(color || style.color)
      .setFontColor(style.fontColor)
      .setFontWeight('bold').setWrap(true).setVerticalAlignment('middle');
  } catch (ignored) {}
}

function statusStyleFromText_(text) {
  var value = String(text || '').trim().toUpperCase();
  if (/^(READY|ALERTS ON)\b/.test(value)) return { color: C_GREEN, fontColor: '#ffffff' };
  if (/^(ACTION NEEDED|FAILED|ERROR)\b/.test(value)) return { color: DANGER_RED, fontColor: '#ffffff' };
  return { color: C_GOLD, fontColor: C_NAVY };
}

function systemCheck_(ss) {
  var issues = [];
  var warnings = [];
  var config = validateConfiguration_(ss);
  issues = issues.concat(config.problems);
  var props = PropertiesService.getDocumentProperties();
  var paused = isPaused_();
  var formId = props.getProperty(PROP_FORM_ID);
  var form = null;
  if (!formId) issues.push('No attendance Form is connected. Open the setup helper and build the system.');
  else {
    try { form = FormApp.openById(formId); }
    catch (err) { issues.push('The connected attendance Form cannot be opened. Open the setup helper and build again.'); }
  }
  if (form && config.rosterResult) {
    try { if (form.getDestinationId() !== ss.getId()) issues.push('The Form response destination is not this spreadsheet.'); }
    catch (err) { issues.push('The Form response destination could not be verified.'); }
    var formSafetyProblems = liveFormSafetyProblems_(form, config.rosterResult.roster);
    formSafetyProblems.forEach(function (problem) { issues.push(problem + ' Open the setup helper and build again.'); });
    try {
      if (!paused && form.supportsAdvancedResponderPermissions && form.supportsAdvancedResponderPermissions() && !form.isPublished()) {
        issues.push('The Form is not published for responders. Open the setup helper and build again.');
      }
    } catch (err) { issues.push('The Form publication state could not be verified.'); }
    try {
      if (!paused && !form.isAcceptingResponses()) issues.push('Alerts are marked on, but the staff Form is closed.');
      if (paused && form.isAcceptingResponses()) warnings.push('Alerts are paused, but Google reports the Form is still open.');
    } catch (err) { issues.push('The Form open/closed state could not be verified.'); }
  }

  var triggerIds = {};
  var ourTriggerCount = 0;
  try {
    ScriptApp.getProjectTriggers().forEach(function (trigger) {
      triggerIds[String(trigger.getUniqueId())] = true;
      var fn = trigger.getHandlerFunction();
      if (fn === 'handleFormSubmit' || fn === 'morningCheck') ourTriggerCount++;
    });
  }
  catch (err) { issues.push('This account could not inspect its Apps Script triggers.'); }
  if (!paused) {
    if (!props.getProperty(PROP_ACTIVE_OWNER_TOKEN)) issues.push('The active automation-owner token is missing. Open the setup helper and build again.');
    var submitUid = props.getProperty(PROP_ACTIVE_SUBMIT_UID);
    if (!submitUid || !triggerIds[submitUid]) issues.push('This account is not the active submit-trigger owner. Open the setup helper from the designated office account and build again.');
    if (config.settings) {
      var expectedSchedule = morningScheduleKey_(config.settings.morningHour, config.settings.tz);
      if (props.getProperty(PROP_ACTIVE_MORNING_SCHEDULE) !== expectedSchedule) {
        issues.push('The installed reminder schedule does not match the current settings or spreadsheet timezone. Open the setup helper and build again.');
      }
      var morningUid = props.getProperty(PROP_ACTIVE_MORNING_UID);
      if (config.settings.morningHour !== null) {
        if (!morningUid || !triggerIds[morningUid]) issues.push('The morning reminder trigger is missing for this account. Open the setup helper and build again.');
      } else if (morningUid) {
        issues.push('A reminder trigger is still recorded even though reminders are Off. Open the setup helper and build again.');
      }
    }
  }
  try {
    var quota = MailApp.getRemainingDailyQuota();
    if (quota <= QUOTA_RESERVE) issues.push('Google email quota is at the safety reserve (' + quota + ' recipients left).');
    else if (quota < 20) warnings.push('Only ' + quota + ' Google email recipients remain today.');
  } catch (err) { warnings.push('Google email quota could not be checked.'); }
  try {
    var twilioCfg = twilioConfig_();
    if (twilioCfg) {
      var twilioProbeResult = twilioProbe_(twilioCfg);
      if (!twilioProbeResult.ok) {
        issues.push('Automatic texts are on, but Twilio rejected the saved connection: ' + twilioProbeResult.detail +
          ' Family texts will fall back to the manual office list until this is fixed. Open ' + MENU_NAME + ', Set up automatic texts.');
      }
    }
  } catch (err) { warnings.push('The Twilio texting connection could not be checked.'); }
  try {
    if (repairLinkCellsFromCache_(ss)) {
      warnings.push('The staff or editor link cell on Start Here had been edited. It was restored from the verified connected Form.');
    }
  } catch (err) { warnings.push('The link cells could not be verified against the cached values.'); }
  if (!paused && config.settings) {
    var expectedTriggerCount = config.settings.morningHour !== null ? 2 : 1;
    if (ourTriggerCount > expectedTriggerCount) {
      warnings.push('This account has ' + (ourTriggerCount - expectedTriggerCount) +
        ' leftover Absence Alerts trigger(s) from older builds. They are inert but add overhead. Open the setup helper and build again from this account to clear them.');
    }
  }
  if (props.getProperty(PROP_APP_VERSION) !== APP_VERSION || props.getProperty(PROP_SCHEMA_VERSION) !== String(SCHEMA_VERSION)) {
    warnings.push('This copy has not recorded the current v' + APP_VERSION + ' setup. Open the setup helper and build the system.');
  }
  return { issues: issues, warnings: warnings, paused: paused, form: form, config: config };
}

function runSystemCheck() {
  var ui = SpreadsheetApp.getUi();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var result = systemCheck_(ss);
  var tz = result.config.settings && result.config.settings.tz ? result.config.settings.tz : TZ_FALLBACK;
  var stamp = Utilities.formatDate(new Date(), tz, 'MMMM d, yyyy h:mm a');
  var warningText = result.warnings.length ? '\n\nWarnings:\n' + result.warnings.join('\n') : '';
  if (result.issues.length) {
    writeStatus_(ss, 'ACTION NEEDED', result.issues.length + ' system check(s) failed at ' + stamp + '.', DANGER_RED);
    ui.alert('System check: action needed', result.issues.join('\n\n') + warningText, ui.ButtonSet.OK);
  } else if (result.paused) {
    writeStatus_(ss, 'PAUSED', 'System is healthy but alerts are paused. Checked ' + stamp + '.', C_GOLD);
    ui.alert('System check passed. Alerts paused', 'Configuration and Form checks passed. Alerts remain paused.' + warningText, ui.ButtonSet.OK);
  } else {
    writeStatus_(ss, 'READY', 'System check passed ' + stamp + '.', C_GREEN);
    ui.alert('System check passed', 'Roster, Form, active triggers, and email quota checks passed.' + warningText, ui.ButtonSet.OK);
  }
}

// ---------------------------------------------------------------------------
// Template tabs (used to build the template, and by do-it-yourself setups)
// ---------------------------------------------------------------------------

// One consistent section-header style: sage fill, deep green text, gold
// left accent. Three different header treatments read as three different
// products; one treatment reads as one designed system.
function applySectionHeader_(sh, a1, title) {
  sh.getRange(a1).merge().setValue(title)
    .setBackground(C_SECTION_FILL).setFontColor(C_GREEN_DARK).setFontWeight('bold').setFontSize(10)
    .setHorizontalAlignment('left').setVerticalAlignment('middle')
    .setBorder(true, true, true, true, null, null, '#dde6de', SpreadsheetApp.BorderStyle.SOLID);
  sh.getRange(a1).setBorder(null, true, null, null, null, null, C_GOLD, SpreadsheetApp.BorderStyle.SOLID_THICK);
}

function ensureTabs_(ss) {
  if (!ss.getSheetByName(TAB_START) || !ss.getSheetByName(TAB_STUDENTS) || !ss.getSheetByName(TAB_LOG) || !ss.getSheetByName(TAB_README)) {
    buildTemplateTabs();
  }
}

function buildTemplateTabs() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  setVersionProperties_();

  // Start Here tab
  var sh = ss.getSheetByName(TAB_START);
  var isNew = !sh;
  if (!sh) sh = ss.insertSheet(TAB_START, 0);
  try { sh.getRange('A1:D23').breakApart(); } catch (ignored) {}
  try { sh.getRange('D4:D10').clearContent(); } catch (ignored) {}
  sh.setHiddenGridlines(true);
  sh.setFrozenRows(2);
  sh.getRange('A1:B23').setFontFamily('Montserrat').setBackground('#ffffff').setFontColor(C_NAVY).setWrap(true);
  sh.setColumnWidth(1, 270);
  sh.setColumnWidth(2, 320);
  try {
    sh.showColumns(1, 2);
    if (sh.getMaxColumns() > 2) sh.hideColumns(3, sh.getMaxColumns() - 2);
  } catch (ignored) {}

  sh.setTabColor(C_GREEN);
  // Calm paper banner with a gold rule, matching the public site. A dark
  // slab here read as scary; the wordmark carries the identity instead.
  sh.getRange('A1:B1').merge().setValue('Absence Alerts')
    .setFontFamily('Lora').setBackground('#ffffff').setFontColor(C_NAVY).setFontSize(21).setFontWeight('bold')
    .setHorizontalAlignment('left').setVerticalAlignment('middle')
    .setBorder(null, null, true, null, null, null, C_GOLD, SpreadsheetApp.BorderStyle.SOLID_MEDIUM);
  sh.setRowHeight(1, 56);
  sh.getRange('A2:B2').merge().setValue('The whole system is free and program-owned. Staff report absences. Families hear right away.')
    .setBackground('#ffffff').setFontColor('#5a6472').setFontSize(10).setWrap(true)
    .setHorizontalAlignment('left').setVerticalAlignment('middle')
    .setBorder(null, null, true, null, null, null, '#e5e2d8', SpreadsheetApp.BorderStyle.SOLID);
  sh.setRowHeight(2, 34);
  applySectionHeader_(sh, 'A3:B3', 'REQUIRED  \u2022  complete these 2 yellow fields');
  sh.setRowHeight(3, 30);

  var labels = [
    ['Program or organization name  *', 'Appears as the sender in every family alert.'],
    ['Office email  *', 'Receives replies, previews, and safety notices.'],
    ['OPTIONAL  |  Office phone', 'Adds a tap-to-call button to family alerts.'],
    ['Logo link', 'Google Drive sharing link. Leave blank for no logo.'],
    ['Email accent color', 'Hex color, for example #1a3d7c. Blank uses green.'],
    ['Copy office on every alert?', 'Default is No.'],
    ['Morning reminder', 'Default is Off. Choose a weekday reminder hour if wanted.']
  ];
  for (var i = 0; i < labels.length; i++) {
    var row = 4 + i;
    var required = row <= 5;
    sh.getRange(row, 1).setValue(labels[i][0]).setFontColor(required ? C_GREEN_DARK : C_NAVY)
      .setBackground(C_LABEL_FILL)
      .setFontWeight('bold').setFontSize(10).setWrap(true).setVerticalAlignment('middle');
    sh.getRange(row, 1).setNote(labels[i][1]);
    sh.getRange(row, 2).setBackground(required ? '#fff2bd' : '#ffffff').setFontSize(11).setVerticalAlignment('middle');
    sh.setRowHeight(row, required ? 46 : 36);
  }
  // One full grid over the whole settings table so every row and the
  // label/input split read instantly, then a gold box around EACH yellow
  // field. horizontal:true is the fix for the missing row 4/5 divider.
  sh.getRange('A4:B10').setBorder(true, true, true, true, true, true, C_GRID, SpreadsheetApp.BorderStyle.SOLID);
  sh.getRange('B4:B5').setBorder(true, true, true, true, false, true, C_GOLD, SpreadsheetApp.BorderStyle.SOLID_MEDIUM);
  var ruleYesNo = SpreadsheetApp.newDataValidation().requireValueInList(['Yes', 'No'], true).setAllowInvalid(false).build();
  sh.getRange('B9').setDataValidation(ruleYesNo);
  var ruleHour = SpreadsheetApp.newDataValidation().requireValueInList(['Off', '8', '9', '10', '11'], true).setAllowInvalid(false).build();
  sh.getRange('B10').setDataValidation(ruleHour);
  if (isNew || !sh.getRange('B9').getValue()) sh.getRange('B9').setValue('No');
  if (isNew || !sh.getRange('B10').getValue()) sh.getRange('B10').setValue('Off');
  sh.setRowHeight(11, 10);

  applySectionHeader_(sh, 'A12:B12', 'ONE GUIDED SETUP');
  sh.setRowHeight(12, 30);
  var steps = [
    'Open the ' + MENU_NAME + ' menu and choose Open setup helper.',
    '1. Program details: Enter your program name and office email.',
    '2. Add students: Replace the sample rows. The helper checks every row.',
    '3. Build the system: Press Build my alert system.'
  ];
  for (var s = 0; s < steps.length; s++) {
    sh.getRange(13 + s, 1, 1, 2).merge().setValue(steps[s]).setWrap(true).setFontSize(10).setVerticalAlignment('middle')
      .setBorder(false, false, true, false, false, false, '#ebe7da', SpreadsheetApp.BorderStyle.SOLID);
    sh.setRowHeight(13 + s, s === 0 ? 40 : 34);
  }
  sh.getRange('A13:B13').setBackground('#e9f5ef').setFontColor(C_GREEN_DARK).setFontWeight('bold');
  sh.setRowHeight(17, 10);

  // The old label read as a form about absent staff. This link is for staff
  // to report student absences, so the label names what it reports.
  sh.getRange('A18').setValue('ABSENCE REPORT FORM').setFontColor(C_NAVY).setBackground(C_LABEL_FILL).setFontWeight('bold').setFontSize(10).setVerticalAlignment('middle');
  sh.getRange('A18').setNote('Staff open this link to report student absences. Share it with staff only.');
  sh.getRange('A19').setValue('FORM EDITOR').setFontColor(C_NAVY).setBackground(C_LABEL_FILL).setFontWeight('bold').setFontSize(10).setVerticalAlignment('middle');
  sh.getRange('B18').setBackground('#eef7f1').setFontColor(C_GREEN_DARK).setFontWeight('bold').setFontSize(10).setWrap(true).setVerticalAlignment('middle');
  sh.getRange('B19').setBackground('#ffffff').setFontColor(C_GRAY).setFontSize(10).setWrap(true).setVerticalAlignment('middle');
  // Empty link cells confused first-time viewers; a quiet placeholder says
  // what will appear. The build overwrites both cells with the real links.
  var placeholderCells = ['B18', 'B19'];
  for (var pc = 0; pc < placeholderCells.length; pc++) {
    var cellRange = sh.getRange(placeholderCells[pc]);
    var cellValue = String(cellRange.getValue() || '').trim();
    if (!cellValue || cellValue === 'Appears here after the build') {
      // Reset every styling channel: an earlier link left underline and bold
      // behind, which made the placeholder look like a clickable link.
      cellRange.setValue('Appears here after the build')
        .setFontWeight('normal').setFontStyle('italic').setFontLine('none')
        .setFontColor('#8a94a3').setFontSize(10);
    }
  }
  sh.getRange('A18:B19').setBorder(true, true, true, true, true, true, C_GRID, SpreadsheetApp.BorderStyle.SOLID);
  sh.setRowHeight(18, 38);
  sh.setRowHeight(19, 34);
  applySectionHeader_(sh, 'A20:B20', 'SYSTEM STATUS');
  sh.setRowHeight(20, 30);
  sh.getRange('A21').setValue('LAST CHECK').setFontColor(C_NAVY).setBackground(C_LABEL_FILL).setFontWeight('bold').setFontSize(10).setVerticalAlignment('middle');
  sh.getRange('B21').setFontSize(10).setWrap(true).setVerticalAlignment('middle');
  sh.getRange('A21:B21').setBorder(true, true, true, true, true, true, C_GRID, SpreadsheetApp.BorderStyle.SOLID);
  sh.setRowHeight(21, 52);
  var existingStatus = String(sh.getRange(CELL_STATUS).getValue() || '');
  if (isNew || !existingStatus || /^NOT READY\b/i.test(existingStatus)) {
    // Pre-setup copies (and the public template) get the canned text rewritten
    // so the displayed version always matches the installed script. READY,
    // PAUSED, and ACTION NEEDED text is live state and is preserved.
    writeStatus_(ss, 'NOT READY', 'Alerts are off until the setup helper finishes building the system.', C_GOLD);
  } else {
    var existingStatusStyle = statusStyleFromText_(existingStatus);
    sh.getRange('B21').setBackground(existingStatusStyle.color).setFontColor(existingStatusStyle.fontColor)
      .setFontWeight('bold').setWrap(true).setVerticalAlignment('middle');
  }
  sh.getRange('A22:B22').merge().setValue('Daily use: choose a group, check absent students only, and submit. Select more than one when needed. Leave the list empty if everyone is here.')
    .setBackground('#f7f4ea').setFontColor('#3f4a5a').setFontSize(9).setWrap(true).setVerticalAlignment('middle');
  sh.setRowHeight(22, 40);
  sh.getRange('A23:B23').merge().setValue(
    'Absence Alerts v' + APP_VERSION + ' \u2022 The whole system is free and open source \u2022 Guide: ' + SITE_URL
  ).setWrap(true).setBackground('#f1efe8').setFontColor(C_GRAY).setFontSize(9).setVerticalAlignment('middle');
  sh.setRowHeight(23, 46);

  // Students tab
  var st = ss.getSheetByName(TAB_STUDENTS);
  var stNew = !st;
  if (!st) st = ss.insertSheet(TAB_STUDENTS, 1);
  st.setTabColor(C_GOLD);
  st.setHiddenGridlines(true);
  st.getRange(1, 1, st.getMaxRows(), 7).setFontFamily('Montserrat').setVerticalAlignment('middle');
  var headers = ['Student name', 'Class or group', 'Family email 1', 'Family email 2 (optional)', 'Family email 3 (optional)', 'Family phone (for text)', 'Contact by'];
  st.getRange(1, 1, 1, 7).setValues([headers])
    .setBackground('#ffffff').setFontColor(C_NAVY).setFontWeight('bold').setFontSize(10)
    .setWrap(true).setVerticalAlignment('middle')
    .setBorder(false, false, true, false, false, false, C_GOLD, SpreadsheetApp.BorderStyle.SOLID_MEDIUM);
  st.getRange('A1').setNote('Student and class or group must be unique. Open the setup helper and build again after any roster change.');
  st.getRange('C1:E1').setNote('One email address per cell. Every real student needs at least one valid family contact.');
  st.getRange('F1').setNote('Only for families contacted by text. Any format, for example 305-555-0123. For a country code, type an apostrophe first, like \'+972501234567, because Sheets treats a leading + as a formula.');
  st.getRange('G1').setNote('Email is the default. Choose Text for a family with no email, such as a flip phone: the office texts them from a ready-made list. Email and text does both.');
  // Phone numbers are text, not quantities. Left as a number, Sheets eats the
  // leading zero of 07700900123 and the plus of +972501234567, and the office
  // then texts an unroutable number with no sign that anything was lost.
  st.getRange(2, 6, Math.max(1, st.getMaxRows() - 1), 1).setNumberFormat('@');
  st.setFrozenRows(1);
  st.setFrozenColumns(2);
  st.setRowHeight(1, 42);
  var studentWidths = [220, 130, 190, 190, 190, 160, 130];
  for (var c = 1; c <= 7; c++) st.setColumnWidth(c, studentWidths[c - 1]);
  try {
    st.showColumns(1, 7);
    if (st.getMaxColumns() > 7) st.hideColumns(8, st.getMaxColumns() - 7);
  } catch (ignored) {}
  if (stNew || st.getLastRow() < 2) {
    st.getRange(2, 1, 3, 7).setValues([
      ['Avi Cohen (SAMPLE - replace me)', 'Group 1', 'parent1@example.com', 'parent2@example.com', '', '', CONTACT_EMAIL],
      ['Maya Levy (SAMPLE - replace me)', 'Group 1', 'maya.mom@example.com', '', '', '', CONTACT_EMAIL],
      ['Eli Gold (SAMPLE - replace me)', 'Group 2', '', '', '', '305-555-0123', CONTACT_TEXT]
    ]);
  }
  try {
    var emailRule = SpreadsheetApp.newDataValidation().requireTextIsEmail().setAllowInvalid(false)
      .setHelpText('Enter one valid email address, or leave the optional cell blank.').build();
    st.getRange(2, 3, st.getMaxRows() - 1, 3).setDataValidation(emailRule);
  } catch (ignored) {}
  try {
    var contactRule = SpreadsheetApp.newDataValidation().requireValueInList(CONTACT_CHOICES, true)
      .setAllowInvalid(false)
      .setHelpText('Email is the default. Choose Text for a family with no email.').build();
    st.getRange(2, 7, st.getMaxRows() - 1, 1).setDataValidation(contactRule);
  } catch (ignored) {}
  // Preserve the copy's active filter/sort state during a visual refresh.
  try { if (!st.getFilter()) st.getRange(1, 1, st.getMaxRows(), 7).createFilter(); } catch (ignored) {}
  try { st.getBandings().forEach(function (banding) { banding.remove(); }); } catch (ignored) {}
  try { st.getRange(2, 1, st.getMaxRows() - 1, 7).applyRowBanding(SpreadsheetApp.BandingTheme.LIGHT_GREY, false, false); } catch (ignored) {}
  // Explicit normal style: earlier template versions set the sample look as
  // direct formatting, which real student names then inherited.
  st.getRange(2, 1, st.getMaxRows() - 1, 7).setFontSize(10).setWrap(true).setVerticalAlignment('middle')
    .setFontStyle('normal').setFontColor('#1f2a3a')
    .setBorder(true, true, true, true, true, true, '#d7dce2', SpreadsheetApp.BorderStyle.SOLID);
  try { st.setRowHeights(2, st.getMaxRows() - 1, 30); } catch (ignored) {}
  var studentRules = [];
  try {
    studentRules.push(SpreadsheetApp.newConditionalFormatRule()
      .whenFormulaSatisfied('=REGEXMATCH(UPPER($A2),"\\(SAMPLE")')
      .setBackground('#f7f4ea').setFontColor('#6b6253')
      .setRanges([st.getRange(2, 1, st.getMaxRows() - 1, 7)]).build());
    studentRules.push(SpreadsheetApp.newConditionalFormatRule()
      .whenFormulaSatisfied('=AND($A2<>"",COUNTIFS($A$2:$A,$A2,$B$2:$B,$B2)>1)')
      .setBackground('#fde7e7').setFontColor('#8a1c1c')
      .setRanges([st.getRange(2, 1, st.getMaxRows() - 1, 7)]).build());
    studentRules.push(SpreadsheetApp.newConditionalFormatRule()
      .whenFormulaSatisfied('=AND($A2<>"",NOT(REGEXMATCH(UPPER($A2),"\\(SAMPLE")),COUNTA($C2:$E2)=0,$F2="")')
      .setBackground('#fff1c7').setFontColor('#6b4f00')
      .setRanges([st.getRange(2, 1, st.getMaxRows() - 1, 7)]).build());
    st.setConditionalFormatRules(studentRules);
  } catch (ignored) {}

  // Email Log tab
  var lg = ss.getSheetByName(TAB_LOG);
  if (!lg) {
    lg = ss.insertSheet(TAB_LOG, 2);
  }
  // Always rewrite the header row: Build or update system runs this and is the
  // documented repair path when an edited header breaks the ledger contract.
  lg.getRange(1, 1, 1, LOG_HEADERS.length).setValues([LOG_HEADERS.slice()]);
  lg.setTabColor(C_NAVY);
  lg.setHiddenGridlines(true);
  lg.getRange(1, 1, lg.getMaxRows(), 9).setFontFamily('Montserrat').setVerticalAlignment('middle');
  lg.getRange(1, 1, 1, 9).setBackground('#ffffff').setFontColor(C_NAVY).setFontWeight('bold').setFontSize(10)
    .setWrap(true).setVerticalAlignment('middle')
    .setBorder(false, false, true, false, false, false, C_GOLD, SpreadsheetApp.BorderStyle.SOLID_MEDIUM);
  lg.getRange('I1').setNote('Tick after you send the text; the row turns green. The system cannot know you did, so this is the only record. Status stays TEXT NEEDED because that is what the system did.');
  lg.getRange('F1').setNote('SENT means Google accepted the send request. It does not prove inbox delivery or that the message was read. Every FAILED row needs immediate manual follow-up.');
  lg.getRange('G1').setNote('Staff-entered text is neutralized before writing so it cannot run as a spreadsheet formula.');
  lg.setFrozenRows(1);
  lg.setFrozenColumns(2);
  lg.setRowHeight(1, 42);
  var logWidths = [145, 95, 155, 135, 195, 95, 250, 135, 80];
  for (var lc = 1; lc <= 9; lc++) lg.setColumnWidth(lc, logWidths[lc - 1]);
  try {
    // Taken by (8) stays hidden as before; Texted (9) must be visible, because
    // ticking it is the office's half of the job.
    lg.showColumns(1, 9);
    lg.hideColumns(8, 1);
    if (lg.getMaxColumns() > 9) lg.hideColumns(10, lg.getMaxColumns() - 9);
  } catch (ignored) {}
  lg.getRange(2, 1, Math.max(1, lg.getMaxRows() - 1), 1).setNumberFormat('mmm d, yyyy h:mm:ss am/pm');
  // Plain text on purpose: date strings must never be coerced into Date
  // values, or a later timezone change would shift history and break dedup.
  lg.getRange(2, 2, Math.max(1, lg.getMaxRows() - 1), 1).setNumberFormat('@');
  lg.getRange(1, 1, lg.getMaxRows(), 9).setWrap(true);
  try { if (!lg.getFilter()) lg.getRange(1, 1, lg.getMaxRows(), 9).createFilter(); } catch (ignored) {}
  try { lg.getBandings().forEach(function (banding) { banding.remove(); }); } catch (ignored) {}
  try { lg.getRange(2, 1, lg.getMaxRows() - 1, 9).applyRowBanding(SpreadsheetApp.BandingTheme.LIGHT_GREY, false, false); } catch (ignored) {}
  lg.getRange(2, 1, Math.max(1, lg.getMaxRows() - 1), 9).setFontSize(9).setVerticalAlignment('middle')
    .setBorder(true, true, true, true, true, true, '#d7dce2', SpreadsheetApp.BorderStyle.SOLID);
  try {
    var statusRange = lg.getRange(2, 1, lg.getMaxRows() - 1, 9);
    lg.setConditionalFormatRules([
      SpreadsheetApp.newConditionalFormatRule().whenFormulaSatisfied('=AND($F2="' + STATUS_TEXT_NEEDED + '",$I2<>TRUE)').setBackground('#fde7e7').setFontColor('#8a1c1c').setRanges([statusRange]).build(),
      SpreadsheetApp.newConditionalFormatRule().whenFormulaSatisfied('=AND($F2="' + STATUS_TEXT_NEEDED + '",$I2=TRUE)').setBackground('#e6f4ea').setFontColor('#0a4a2b').setRanges([statusRange]).build(),
      SpreadsheetApp.newConditionalFormatRule().whenFormulaSatisfied('=$F2="' + STATUS_TEXT_SENT + '"').setBackground('#e6f4ea').setFontColor('#0a4a2b').setRanges([statusRange]).build(),
      SpreadsheetApp.newConditionalFormatRule().whenFormulaSatisfied('=$F2="FAILED"').setBackground('#fde7e7').setFontColor('#8a1c1c').setRanges([statusRange]).build(),
      SpreadsheetApp.newConditionalFormatRule().whenFormulaSatisfied('=$F2="SENT"').setBackground('#e6f4ea').setFontColor('#0a4a2b').setRanges([statusRange]).build(),
      SpreadsheetApp.newConditionalFormatRule().whenFormulaSatisfied('=$F2="RECEIVED"').setBackground('#e8f0fe').setFontColor('#183c73').setRanges([statusRange]).build(),
      SpreadsheetApp.newConditionalFormatRule().whenFormulaSatisfied('=OR($F2="REMINDER",$F2="PAUSED")').setBackground('#fff1c7').setFontColor('#6b4f00').setRanges([statusRange]).build(),
      SpreadsheetApp.newConditionalFormatRule().whenFormulaSatisfied('=$F2="SKIPPED"').setFontColor('#6b7280').setRanges([statusRange]).build()
    ]);
  } catch (ignored) {}

  var isNewReadMe = buildReadMeTab_(ss);

  // Remove the default sheet only when it is truly empty
  var extra = ss.getSheetByName('Sheet1');
  if (extra && ss.getSheets().length > 3 && extra.getLastRow() === 0 && extra.getLastColumn() === 0) {
    ss.deleteSheet(extra);
  }

  // Deterministic tab order: the manual first, so an organization that just copied
  // the Sheet reads before it clicks, then the control panel and the data.
  var tabOrder = [TAB_README, TAB_START, TAB_STUDENTS, TAB_LOG];
  try {
    for (var t = 0; t < tabOrder.length; t++) {
      var ordered = ss.getSheetByName(tabOrder[t]);
      if (!ordered) continue;
      ss.setActiveSheet(ordered);
      ss.moveActiveSheet(t + 1);
    }
  } catch (ignored) {}

  // A fresh copy lands on the Read Me. An existing copy pressing build is
  // returned to the control panel instead of being yanked into the manual.
  var landing = ss.getSheetByName(isNewReadMe ? TAB_README : TAB_START);
  if (landing) ss.setActiveSheet(landing);
}

// The Read Me tab exists because Start Here must stay a control panel, not a
// manual: every step in one column, its full detail in the next, then the menu
// item by item, then the questions people actually ask. Rebuilt idempotently
// by every build. Returns true when it was created for the first time.
function buildReadMeTab_(ss) {
  var rm = ss.getSheetByName(TAB_README);
  var isNewReadMe = !rm;
  if (!rm) rm = ss.insertSheet(TAB_README, 0);
  rm.setTabColor(C_GREEN);
  rm.setHiddenGridlines(true);
  try { rm.getRange('A1:B80').breakApart(); } catch (ignored) {}
  rm.getRange('A1:B80').clearContent().clearNote()
    .setFontFamily('Montserrat').setBackground('#ffffff').setFontColor(C_NAVY)
    .setWrap(true).setVerticalAlignment('middle').setFontStyle('normal')
    .setFontWeight('normal').setFontSize(10).setBorder(false, false, false, false, false, false);
  rm.setColumnWidth(1, 210);
  rm.setColumnWidth(2, 590);
  try {
    rm.showColumns(1, 2);
    if (rm.getMaxColumns() > 2) rm.hideColumns(3, rm.getMaxColumns() - 2);
  } catch (ignored) {}

  // Tab names in the text become real jump links, so nobody has to hunt for
  // the tab being described.
  var baseUrl = '';
  try { baseUrl = String(ss.getUrl() || '').replace(/[?#].*$/, ''); } catch (ignored) {}
  function tabUrl_(name) {
    var target = ss.getSheetByName(name);
    return (baseUrl && target) ? baseUrl + '#gid=' + target.getSheetId() : '';
  }
  var startLink = { find: TAB_START, url: tabUrl_(TAB_START) };
  var studentsLink = { find: TAB_STUDENTS, url: tabUrl_(TAB_STUDENTS) };
  var logLink = { find: TAB_LOG, url: tabUrl_(TAB_LOG) };
  var siteLink = { find: SITE_URL, url: SITE_URL };

  function writeDetail_(row, text, links) {
    var cell = rm.getRange(row, 2);
    var valid = [];
    (links || []).forEach(function (link) {
      if (link && link.url && text.indexOf(link.find) !== -1) valid.push(link);
    });
    if (!valid.length) { cell.setValue(text); return; }
    var builder = SpreadsheetApp.newRichTextValue().setText(text);
    valid.forEach(function (link) {
      var at = text.indexOf(link.find);
      builder.setLinkUrl(at, at + link.find.length, link.url);
    });
    cell.setRichTextValue(builder.build());
  }

  function section_(row, title, entries) {
    applySectionHeader_(rm, 'A' + row + ':B' + row, title);
    rm.setRowHeight(row, 30);
    var r = row + 1;
    for (var i = 0; i < entries.length; i++) {
      rm.getRange(r, 1).setValue(entries[i][0]).setFontWeight('bold').setFontSize(10)
        .setFontColor(C_NAVY).setBackground(C_LABEL_FILL).setVerticalAlignment('middle');
      writeDetail_(r, entries[i][1], entries[i][3]);
      rm.getRange(r, 2).setFontSize(10).setFontColor('#3f4a5a').setVerticalAlignment('middle');
      rm.setRowHeight(r, entries[i][2]);
      r++;
    }
    rm.getRange(row + 1, 1, entries.length, 2)
      .setBorder(true, true, true, true, true, true, C_GRID, SpreadsheetApp.BorderStyle.SOLID);
    rm.setRowHeight(r, 12);
    return r + 1;
  }

  rm.getRange('A1:B1').merge().setValue('Read Me')
    .setFontFamily('Lora').setFontColor(C_NAVY).setFontSize(21).setFontWeight('bold')
    .setHorizontalAlignment('left').setVerticalAlignment('middle')
    .setBorder(null, null, true, null, null, null, C_GOLD, SpreadsheetApp.BorderStyle.SOLID_MEDIUM);
  rm.setRowHeight(1, 56);
  rm.getRange('A2:B2').merge().setValue('Plain words, start to finish. This is an absence alert system: staff report which students are absent, and every family contact is emailed right away. It is not an attendance register.')
    .setFontColor('#5a6472').setFontSize(10)
    .setBorder(null, null, true, null, null, null, '#e5e2d8', SpreadsheetApp.BorderStyle.SOLID);
  rm.setRowHeight(2, 40);
  rm.getRange('A3:B3').merge().setValue('Stuck anywhere? Any AI assistant can walk you through this. Open ChatGPT, Claude, Gemini, or whichever you use, share this tab or the guide link at the bottom, and ask it your question in your own words.')
    .setBackground('#e9f5ef').setFontColor(C_GREEN_DARK).setFontSize(10).setFontWeight('bold')
    .setBorder(true, true, true, true, false, false, '#c7e0d2', SpreadsheetApp.BorderStyle.SOLID);
  rm.setRowHeight(3, 44);
  rm.setRowHeight(4, 12);

  var row = section_(5, 'WHAT HAPPENS', [
    ['Staff report absences', 'Staff open the absence report link (on Start Here, row 18), choose their group, check every absent student, and submit. No sign-in. Leaving the list empty reports that everyone is present.', 52, [startLink]],
    ['Families hear right away', 'Every family contact of every checked student gets one clear email from your program within seconds: student name, group, time, and a call button if you entered an office phone.', 52, []],
    ['Families with no email', 'A family set to Contact by Text is never emailed. The office gets one urgent email, taps a button per family, and sends the ready-made text. Then tick the Texted box in the Email Log, which is the only record that it went. Or connect your own Twilio account (menu: Set up automatic texts) and these texts send themselves.', 62, [logLink]],
    ['The office sees every result', 'Each send is written to the Email Log tab: SENT, SKIPPED, RECEIVED, or FAILED. A FAILED row means nobody was emailed for that problem. Contact that family another way, fix the issue, and build again.', 62, [logLink]]
  ]);

  row = section_(row, 'SETUP, STEP BY STEP', [
    ['1. Fill the two yellow fields', 'On Start Here: your program or organization name, and the office email that receives replies, previews, and safety notices.', 46, [startLink]],
    ['2. Add optional extras', 'Office phone adds a tap-to-call button to every alert. Logo link shows your logo (see the FAQ below for sharing). Accent color and the weekday morning reminder are optional too.', 52, []],
    ['3. Replace the sample students', 'On the Students tab: one row per student with their class or group and up to three family emails. For a family with no email, such as a flip phone, put their number in Family phone and set Contact by to Text. Email and text does both. Every student needs either a family email or a phone.', 62, [studentsLink]],
    ['4. Build the system', 'Open the ' + MENU_NAME + ' menu and choose Open setup helper, then press Build my alert system. The build creates the Form, connects alerts, runs its safety checks, and writes the links on Start Here.', 56, [startLink]],
    ['5. Approve Google’s access once', 'This happens the FIRST time you open the menu, before any of the steps above. Google says authorization is required, then asks you to allow access. Some accounts also see a warning that the app is not verified: that is normal for a private copy, so click Advanced, then Go to Absence Alerts. It can read and update this spreadsheet, create and manage Google Forms, show its own menu and sidebar, run on a schedule, and send email as your account. Google names the Forms permission broadly because Apps Script has no narrower one. It never touches Gmail or your contacts.', 82, []],
    ['6. Test on yourself, then go live', 'Test before anyone relies on it. The sample rows are ignored on purpose, so put in one or two pretend students using your own email address, build, and submit a practice absence. Confirm the alert arrives and the Email Log fills in. Only then enter the real roster, build again, and share the staff link with whoever marks attendance.', 76, [logLink]]
  ]);

  row = section_(row, 'THE MENU, ITEM BY ITEM', [
    ['Open setup helper', 'The guided panel for first-time setup. It shows the three parts, checks your work, and gives you one build button. Setup works best on a computer, not a phone.', 52, []],
    ['Build or update system', 'Press this after any change to your students or program details. It rebuilds the Form to match your roster, reconnects alerts, refreshes this workbook, and runs the safety checks. Alerts pause while it runs and turn back on when it finishes.', 68, []],
    ['Send preview email', 'Sends one sample alert to your office email so you can see exactly what a family receives. No family is emailed.', 46, []],
    ['Set up automatic texts', 'Optional. Connect your program’s own Twilio account (a paid texting service) and every family set to Text is texted the moment they are marked absent, no office taps needed. Without it, the office keeps texting by hand from the ready-made list.', 62, []],
    ['Run system check', 'A health check of the roster, the live Form, the alert triggers, and your email quota. The result is written to Start Here, row 21. Safe to run any time.', 52, [startLink]],
    ['Replace a shared/exposed staff link', 'Creates a brand-new absence report link and closes the old one immediately. Use it if the link reached anyone outside staff, then share the new link from Start Here.', 56, [startLink]],
    ['Pause alerts', 'Closes the absence report form and stops all sending. Use it for holidays, closures, and vacations.', 46, []],
    ['Resume alerts', 'Reopens the form and turns alerts back on after rechecking the roster, the Form, and the triggers.', 46, []],
    ['Help and setup guide', 'Opens the public guide with the full explanation, the source code, and the exact permissions this system asks for.', 46, []]
  ]);

  row = section_(row, 'EASY FAQ', [
    ['Can our AI help us set this up?', 'Yes, and it is a good idea. Share this Read Me tab or the guide link with ChatGPT, Claude, Gemini, or any assistant and ask it to walk you through setup, explain a menu item, or make sense of a message you see. Everything about this system is public and free, so an AI can read it all.', 68, []],
    ['What is the logo link?', 'A link to your logo image. In Google Drive: upload the image, right-click it, choose Share, set General access to Anyone with the link, and paste the link on Start Here. Without that sharing setting, families see no logo. Any public image address also works.', 62, [startLink]],
    ['A parent has no email, only a flip phone. What now?', 'Put their number in Family phone and set Contact by to Text. The office gets an urgent email with a button per family: tap it, tap Open messaging app, and the number and message are ready to send. A few seconds each. Manual is the free default, because no free way to text automatically survives the carriers. Those rows stay TEXT NEEDED in red until someone ticks the box. Want the texts to send themselves? See the next answer.', 68, [logLink]],
    ['Can the texts send themselves?', 'Yes, optionally. Menu: Set up automatic texts. You create an account at twilio.com (a pay-as-you-go texting service), get a phone number there, and paste in three details: Account SID, Auth Token, and your Twilio number. Typical cost, paid to Twilio: about a dollar a month for the number and about a penny per text. US numbers usually need Twilio’s short business-texting registration first; Twilio walks you through it. Successful texts turn green as TEXT SENT in the Email Log, and if one ever fails, that family drops onto the manual office list, so nobody is missed.', 80, [logLink]],
    ['Who is the absence report form for?', 'Staff only, and it reports student absences. Staff are never marked absent with it, and nobody signs in. The link works like a key to student names, so share it only in trusted staff channels.', 52, []],
    ['I changed the Students tab. Why did alerts stop?', 'After any roster change the live Form no longer matches, so the system stops all sends and emails the office instead of guessing. Choose Build or update system from the menu; alerts resume.', 56, [studentsLink]],
    ['What does SKIPPED mean in the Email Log?', 'That contact was already alerted for that student today, so a repeated submission does not send a duplicate email.', 46, [logLink]],
    ['How do we pause for holidays?', 'Menu: Pause alerts. The form closes and nothing sends. Choose Resume alerts to turn everything back on.', 46, []],
    ['What does the morning reminder do?', 'If Start Here row 10 is set to a weekday hour, the office gets one email that morning listing groups that have not submitted the absence form yet. Times are approximate within the chosen hour.', 52, [startLink]],
    ['Where does our data live?', 'Everything stays in this spreadsheet and its Form, inside your program’s own Google account. There is no outside server or database, and the whole system is free.', 52, []]
  ]);

  // Merge first: merging a range whose right cell holds the text would discard
  // it. The footer link is written into the merged top-left cell.
  var footer = rm.getRange(row, 1, 1, 2).merge();
  footer.setBackground('#f1efe8').setFontColor(C_GRAY).setFontSize(9).setVerticalAlignment('middle');
  var footerText = 'Full guide, source code, and the permissions file: ' + SITE_URL;
  var footerAt = footerText.indexOf(SITE_URL);
  rm.getRange(row, 1).setRichTextValue(
    SpreadsheetApp.newRichTextValue().setText(footerText)
      .setLinkUrl(footerAt, footerAt + SITE_URL.length, SITE_URL).build()
  );
  rm.setRowHeight(row, 34);
  return isNewReadMe;
}