/**
 * Retail CRM — GM Clienteling Email System
 * Google Apps Script — runs from the GM's rolled-up workbook
 *
 * The GM's workbook ingests each associate's client data into a
 * same-named worksheet (e.g. "Associate 1") inside this workbook, plus:
 *   - "All Clients"      combined rollup of every associate's clients
 *   - "GM Clients"       the GM's own direct clients
 *   - "Associate Template"  one-off custom message copy per associate
 *   - "GM Template"         the GM's own default cadence copy
 *
 * Sends from whichever Google account is active when a function is run —
 * either the GM's own account or the shared store account, since both
 * have identical edit access to this workbook and its scripts.
 *
 * Eight functions in total:
 *   1-6. sendAssociateCongrats_<n>()   — one per associate, manual.
 *        Uses the congratulations trigger (not the day-since-purchase
 *        cadence) against the Associate Template sheet, for one-off
 *        custom outreach the GM sends on behalf of a specific associate.
 *   7.   sendAllAssociatesCadence()    — manual fallback.
 *        Runs the full day-since-purchase cadence across every associate
 *        sheet using the GM Template's default copy. Exists for cases
 *        where an associate's own timed trigger failed to fire.
 *        KNOWN LIMITATION: this writes sent-flags only to the GM's local
 *        copy of the associate's sheet inside this workbook, not back to
 *        the associate's own locked sheet. No write-back mechanism existed
 *        at the time. If this fallback ever fired, the GM would need to
 *        manually relay which flags were set so the associate could mark
 *        the same cells on their own sheet, preventing a future duplicate
 *        send once their trigger resumed. This path was never exercised
 *        in practice.
 *   8.   sendGMClientsCadence()        — time-triggered.
 *        Runs the full cadence against the GM's own client sheet using
 *        the GM Template's default copy.
 *
 * Email cadence (days since last purchase), used by functions 7 and 8:
 *   <=7 days  -> initial follow-up
 *   21 days   -> 3-week check-in
 *   91 days   -> 3-month re-engagement
 *   182 days  -> 6-month re-engagement
 *   365 days  -> 1-year anniversary
 */

var START_ROW = 6;
var ASSOCIATE_TEMPLATE_START_ROW = 3;   // row 3 = Associate 1, row 4 = Associate 2, etc.
var GM_TEMPLATE_ROW = 3;

var ASSOCIATE_SHEETS = [
  'Associate 1',
  'Associate 2',
  'Associate 3',
  'Associate 4',
  'Associate 5',
  'Associate 6'
];

// ── Functions 1-6: per-associate congratulations send (manual, one-off) ──

function sendAssociateCongrats_1() { sendAssociateCongrats(0); }
function sendAssociateCongrats_2() { sendAssociateCongrats(1); }
function sendAssociateCongrats_3() { sendAssociateCongrats(2); }
function sendAssociateCongrats_4() { sendAssociateCongrats(3); }
function sendAssociateCongrats_5() { sendAssociateCongrats(4); }
function sendAssociateCongrats_6() { sendAssociateCongrats(5); }

/**
 * Sends the one-off custom congratulations message for one associate's
 * clients, using the Associate Template sheet (not the GM default
 * template). Triggered manually by the GM for a specific associate at a
 * specific moment — not on the day-since-purchase cadence.
 */
function sendAssociateCongrats(associateIndex) {
  var ss = SpreadsheetApp.getActive();
  var assocTemplateSheet = ss.getSheetByName('Associate Template');
  var assocSheet = ss.getSheetByName(ASSOCIATE_SHEETS[associateIndex]);
  if (!assocSheet) return;

  var lastRow = assocSheet.getLastRow();
  if (lastRow < START_ROW) return;

  var templateRow = ASSOCIATE_TEMPLATE_START_ROW + associateIndex;
  var templateData = assocTemplateSheet.getRange(templateRow, 1, 1, 7).getValues()[0];
  var emailAddress = templateData[0];
  var congratsBody = templateData[6];

  if (!emailAddress) return;

  var congratsCell = assocTemplateSheet.getRange(templateRow, 7);
  if (!congratsCell.isBlank()) {
    sendAndMark(emailAddress, congratsBody, assocSheet, START_ROW, 8);
  }
}

// ── Function 7: all-associates combined cadence (manual fallback) ────────

/**
 * Runs the full day-since-purchase cadence across every associate sheet
 * in this workbook, using the GM Template's default copy. Manual fallback
 * for when an associate's own timed trigger failed to launch.
 *
 * Writes sent-flags to the GM's local copy of the associate's sheet only —
 * there was no mechanism to write back to the associate's own locked sheet.
 * Recovering from a fallback send required the GM to manually relay the
 * flagged cells to the associate so they could mark them on their own
 * sheet and avoid a duplicate send once their trigger resumed.
 */
function sendAllAssociatesCadence() {
  var ss = SpreadsheetApp.getActive();
  var gmTemplateSheet = ss.getSheetByName('GM Template');

  for (var a = 0; a < ASSOCIATE_SHEETS.length; a++) {
    var assocSheet = ss.getSheetByName(ASSOCIATE_SHEETS[a]);
    if (!assocSheet) continue;

    runCadence(assocSheet, gmTemplateSheet, GM_TEMPLATE_ROW);
  }
}

// ── Function 8: GM's own clients cadence (time-triggered) ────────────────

/**
 * Runs the full day-since-purchase cadence against the GM's own client
 * sheet, using the GM Template's default copy. Runs on a time-based
 * trigger, same as each associate's own script.
 */
function sendGMClientsCadence() {
  var ss = SpreadsheetApp.getActive();
  var gmClientSheet = ss.getSheetByName('GM Clients');
  var gmTemplateSheet = ss.getSheetByName('GM Template');

  runCadence(gmClientSheet, gmTemplateSheet, GM_TEMPLATE_ROW);
}

// ── Shared cadence logic (used by functions 7 and 8) ──────────────────────

/**
 * Evaluates one client sheet against the day-since-purchase cadence and
 * sends/marks any due emails, using the given template sheet and row.
 */
function runCadence(clientSheet, templateSheet, templateRow) {
  var lastRow = clientSheet.getLastRow();
  if (lastRow < START_ROW) return;

  var numRows = lastRow - START_ROW + 1;
  var reminderValues = clientSheet.getRange(START_ROW, 8, numRows, 1).getValues();

  var sentCols = {
    initial:    clientSheet.getRange(START_ROW, 9,  numRows, 1),
    threeWeek:  clientSheet.getRange(START_ROW, 11, numRows, 1),
    threeMonth: clientSheet.getRange(START_ROW, 13, numRows, 1),
    sixMonth:   clientSheet.getRange(START_ROW, 15, numRows, 1),
    oneYear:    clientSheet.getRange(START_ROW, 17, numRows, 1)
  };

  var templateData = templateSheet.getRange(templateRow, 1, 1, 7).getValues()[0];
  var emailAddress = templateData[0];
  var templates = {
    initial:    templateData[1],
    threeWeek:  templateData[2],
    threeMonth: templateData[3],
    sixMonth:   templateData[4],
    oneYear:    templateData[5],
    congrats:   templateData[6]
  };

  if (!emailAddress) return;

  for (var i = 0; i < numRows; i++) {
    var remind = reminderValues[i][0];
    var rowSent = {
      initial:    sentCols.initial.getCell(i + 1, 1).isBlank(),
      threeWeek:  sentCols.threeWeek.getCell(i + 1, 1).isBlank(),
      threeMonth: sentCols.threeMonth.getCell(i + 1, 1).isBlank(),
      sixMonth:   sentCols.sixMonth.getCell(i + 1, 1).isBlank(),
      oneYear:    sentCols.oneYear.getCell(i + 1, 1).isBlank()
    };

    if (remind <= 7 && rowSent.initial) {
      sendAndMark(emailAddress, templates.initial, clientSheet, START_ROW + i, 9);
    } else if (remind == 21 && rowSent.threeWeek) {
      sendAndMark(emailAddress, templates.threeWeek, clientSheet, START_ROW + i, 11);
    } else if (remind == 91 && rowSent.threeMonth) {
      sendAndMark(emailAddress, templates.threeMonth, clientSheet, START_ROW + i, 13);
    } else if (remind == 182 && rowSent.sixMonth) {
      sendAndMark(emailAddress, templates.sixMonth, clientSheet, START_ROW + i, 15);
    } else if (remind == 365 && rowSent.oneYear) {
      sendAndMark(emailAddress, templates.oneYear, clientSheet, START_ROW + i, 17);
    }
  }
}

/**
 * Sends a clienteling email and marks the sent-flag cell for that row.
 * Flushes immediately to protect against script interruption.
 */
function sendAndMark(emailAddress, body, sheet, row, flagCol) {
  MailApp.sendEmail(emailAddress, 'Clienteling Reminder', body);
  sheet.getRange(row, flagCol, 1, 1).setValue('EMAIL_SENT');
  SpreadsheetApp.flush();
}