/**
 * Retail CRM — Associate Clienteling Email System
 * Google Apps Script — runs on each associate's own locked sheet
 *
 * Each associate has their own copy of this script, installed directly in
 * their locked client-tracking sheet (not shared, not parameterized — the
 * sheet name and template row are hardcoded per associate since each
 * associate only ever needs their own data).
 *
 * Runs on a time-based trigger. Sends from whichever Google account is
 * active when the trigger fires — the associate's own account, since this
 * script lives in their own sheet.
 *
 * Email cadence (days since last purchase):
 *   <=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
 *   Special   -> congratulations trigger (column G of this associate's
 *                template row; fires independent of the cadence above)
 *
 * To adapt this script for a different associate: change TEMPLATE_ROW
 * below to that associate's row on the Email Template sheet. The client
 * sheet itself is always the active sheet, so no sheet name is needed.
 */

var START_ROW = 6;
var TEMPLATE_ROW = 3;   // this associate's row on the Email Template sheet

function sendClientelingEmails() {
  var ss = SpreadsheetApp.getActive();
  var clientSheet = ss.getSheetByName('My Clients');
  var templateSheet = ss.getSheetByName('Email Template');

  var lastRow = clientSheet.getLastRow();
  if (lastRow < START_ROW) return;   // no client data

  var numRows = lastRow - START_ROW + 1;

  // Column H: days-since-last-purchase countdown value
  var reminderValues = clientSheet.getRange(START_ROW, 8, numRows, 1).getValues();

  // Sent-flag column references
  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)
  };

  // This associate's template row — columns:
  // [email, initial, 3-week, 3-month, 6-month, 1-year, congrats]
  var templateData = templateSheet.getRange(TEMPLATE_ROW, 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;   // no email configured

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

  // Congratulations trigger — fires when column G of this associate's
  // template row is populated, independent of cadence above
  var congratsCell = templateSheet.getRange(TEMPLATE_ROW, 7);
  if (!congratsCell.isBlank()) {
    sendAndMark(emailAddress, templates.congrats, clientSheet, START_ROW, 8);
  }
}

/**
 * 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();
}