/**
 * Budget Pacing Alarm — Apps Script
 * by Utkarsh Jukaria · pacing-tracker.utkarshjukaria.com
 *
 * Paste this whole file into Extensions ▸ Apps Script (replace the empty Code.gs), save,
 * reload the sheet and use the "Pacing Alarm" menu.
 *
 * Secrets (Slack webhook, Meta token, web-app key) are kept in Script Properties.
 * They are never written into a cell, so they do not travel with "Make a copy".
 */

var CFG = {
  PACING_SHEET: 'Pacing',
  SETUP_SHEET: 'Setup',
  SPEND_SHEET: 'Spend',
  LOG_SHEET: 'Alert log',
  FIRST_ROW: 5,          // first data row on Pacing
  LAST_ROW: 54,          // last data row on Pacing
  SETUP_FIRST_ROW: 9,
  ALERT_HOUR: 9,         // daily trigger hour, in the sheet's time zone
  META_API_VERSION: 'v23.0' // update if Meta retires it: developers.facebook.com/docs/graph-api/changelog
};

// Pacing column positions (1-based), matching the template
var COL = { client: 1, platform: 2, campaign: 3, budget: 4, start: 5, end: 6, elapsed: 8, left: 9,
  expected: 10, actual: 11, pacing: 12, avg: 13, projected: 14, runout: 16, recDaily: 18,
  variance: 21, status: 22, alert: 23, channel: 24, owner: 25, message: 26 };

/* ---------------------------------------------------------------- menu */

function onOpen() {
  SpreadsheetApp.getUi().createMenu('Pacing Alarm')
    .addItem('Check pacing and send alerts now', 'runDailyCheck')
    .addItem('Send test alert', 'sendTestAlert')
    .addSeparator()
    .addItem('Turn on daily alerts (~9 AM)', 'installDailyTrigger')
    .addItem('Turn off daily alerts', 'removeTriggers')
    .addSeparator()
    .addItem('Set Slack webhook', 'promptSlackWebhook')
    .addItem('Connect Meta ads', 'promptMetaConnection')
    .addItem('Pull Meta spend now (this month)', 'pullMetaSpend')
    .addItem('Turn on live dashboard link', 'promptWebAppKey')
    .addToUi();
}

/* ---------------------------------------------------------------- core */

function readPacingRows_() {
  var ss = SpreadsheetApp.getActive();
  SpreadsheetApp.flush();
  var sh = ss.getSheetByName(CFG.PACING_SHEET);
  var n = CFG.LAST_ROW - CFG.FIRST_ROW + 1;
  var values = sh.getRange(CFG.FIRST_ROW, 1, n, COL.message).getValues();
  return values.filter(function (r) { return r[COL.campaign - 1] !== ''; }).map(function (r) {
    var o = {};
    Object.keys(COL).forEach(function (k) { o[k] = r[COL[k] - 1]; });
    return o;
  });
}

/** Daily job. Only sends for campaigns outside their threshold, once per day. */
function runDailyCheck() {
  var props = PropertiesService.getScriptProperties();
  var tz = SpreadsheetApp.getActive().getSpreadsheetTimeZone();
  var today = Utilities.formatDate(new Date(), tz, 'yyyy-MM-dd');
  var rows = readPacingRows_().filter(function (r) { return r.alert === 'YES' && r.message; });
  var asOf = SpreadsheetApp.getActive().getSheetByName(CFG.PACING_SHEET).getRange('C2').getValue();
  var asOfText = asOf instanceof Date ? Utilities.formatDate(asOf, tz, 'EEE, MMM d') : String(asOf);

  if (!rows.length) { log_('—', 'No alerts', 'Every active campaign is inside its threshold.'); return; }

  // Avoid sending the same campaign twice on the same day (manual run + trigger)
  var sentKey = 'SENT_' + today;
  var sent = JSON.parse(props.getProperty(sentKey) || '[]');
  rows = rows.filter(function (r) { return sent.indexOf(r.campaign) === -1; });
  if (!rows.length) return;

  // Email: one digest per owner
  var byOwner = {};
  rows.forEach(function (r) {
    if (/email/i.test(r.channel) && r.owner) (byOwner[r.owner] = byOwner[r.owner] || []).push(r);
  });
  Object.keys(byOwner).forEach(function (owner) {
    var list = byOwner[owner];
    var subject = '⏰ Pacing alarm: ' + list.length + ' campaign' + (list.length > 1 ? 's' : '') +
      ' off track (data through ' + asOfText + ')';
    MailApp.sendEmail({ to: owner, subject: subject, htmlBody: emailHtml_(list, asOfText),
      body: list.map(function (r) { return r.message; }).join('\n\n') });
    list.forEach(function (r) { log_(r.campaign, 'Email → ' + owner, r.message); });
  });

  // Slack: one message for all Slack rows
  var hook = props.getProperty('SLACK_WEBHOOK');
  var slackRows = rows.filter(function (r) { return /slack/i.test(r.channel); });
  if (slackRows.length && hook) {
    postSlack_(hook, slackRows, asOfText);
    slackRows.forEach(function (r) { log_(r.campaign, 'Slack', r.message); });
  } else if (slackRows.length) {
    log_('—', 'Slack skipped', 'No webhook set. Use Pacing Alarm ▸ Set Slack webhook.');
  }

  props.setProperty(sentKey, JSON.stringify(sent.concat(rows.map(function (r) { return r.campaign; }))));
  cleanupSentKeys_(props, today);
}

function emailHtml_(rows, asOfText) {
  var colour = { Over: '#C2512B', Under: '#9A6B00', 'No spend': '#1F1B16' };
  var items = rows.map(function (r) {
    return '<tr><td style="padding:14px 16px;border-left:4px solid ' + (colour[r.status] || '#1F1B16') +
      ';background:#FBF8F2;font:15px/1.45 Arial,sans-serif;color:#1F1B16">' + escapeHtml_(r.message) + '</td></tr>' +
      '<tr><td style="height:10px"></td></tr>';
  }).join('');
  return '<div style="max-width:620px;margin:0 auto;font-family:Arial,sans-serif;color:#1F1B16">' +
    '<p style="font-size:12px;letter-spacing:.12em;text-transform:uppercase;color:#6B6357">Budget Pacing Alarm · data through ' + asOfText + '</p>' +
    '<table role="presentation" width="100%" cellspacing="0" cellpadding="0">' + items + '</table>' +
    '<p style="font-size:13px;color:#6B6357">Open the sheet: <a href="' + SpreadsheetApp.getActive().getUrl() + '">' +
    escapeHtml_(SpreadsheetApp.getActive().getName()) + '</a></p></div>';
}

function postSlack_(hook, rows, asOfText) {
  var blocks = [{ type: 'header', text: { type: 'plain_text', text: '⏰ Pacing alarm — ' + rows.length + ' off track' } },
    { type: 'context', elements: [{ type: 'mrkdwn', text: 'Data through ' + asOfText }] }];
  rows.forEach(function (r) { blocks.push({ type: 'section', text: { type: 'mrkdwn', text: r.message } }); });
  blocks.push({ type: 'context', elements: [{ type: 'mrkdwn', text: '<' + SpreadsheetApp.getActive().getUrl() + '|Open the pacing sheet>' }] });
  var res = UrlFetchApp.fetch(hook, { method: 'post', contentType: 'application/json',
    payload: JSON.stringify({ text: rows.map(function (r) { return r.message; }).join('\n'), blocks: blocks }),
    muteHttpExceptions: true });
  if (res.getResponseCode() >= 300) log_('—', 'Slack error ' + res.getResponseCode(), res.getContentText());
}

function sendTestAlert() {
  var ui = SpreadsheetApp.getUi();
  var me = Session.getActiveUser().getEmail();
  var sample = [{ status: 'Over', campaign: 'TEST', message:
    '⚠️ Test client — Meta is pacing 23% over. At this rate it runs out on Nov 24. Lower daily budget to $412 to land on target.' }];
  if (me) MailApp.sendEmail({ to: me, subject: '⏰ Pacing alarm: test', htmlBody: emailHtml_(sample, 'test'), body: sample[0].message });
  var hook = PropertiesService.getScriptProperties().getProperty('SLACK_WEBHOOK');
  if (hook) postSlack_(hook, sample, 'test');
  ui.alert('Test sent' + (me ? ' to ' + me : '') + (hook ? ' and to Slack.' : '. (No Slack webhook set.)'));
}

/* ---------------------------------------------------------------- triggers */

function installDailyTrigger() {
  removeTriggers(true);
  ScriptApp.newTrigger('runDailyCheck').timeBased().everyDays(1).atHour(CFG.ALERT_HOUR).create();
  ScriptApp.newTrigger('pullMetaSpendDaily_').timeBased().everyDays(1).atHour(Math.max(0, CFG.ALERT_HOUR - 1)).create();
  SpreadsheetApp.getUi().alert('Daily alerts are on. Google runs them between ' + CFG.ALERT_HOUR + ' and ' +
    (CFG.ALERT_HOUR + 1) + ' o\'clock (' + SpreadsheetApp.getActive().getSpreadsheetTimeZone() + ').');
}

function removeTriggers(silent) {
  ScriptApp.getProjectTriggers().forEach(function (t) {
    if (['runDailyCheck', 'pullMetaSpendDaily_'].indexOf(t.getHandlerFunction()) > -1) ScriptApp.deleteTrigger(t);
  });
  if (silent !== true) SpreadsheetApp.getUi().alert('Daily alerts are off.');
}

/* ---------------------------------------------------------------- settings prompts */

function promptSlackWebhook() {
  var ui = SpreadsheetApp.getUi();
  var r = ui.prompt('Slack webhook', 'Paste your Slack incoming webhook URL (https://hooks.slack.com/services/…). Leave blank to remove.', ui.ButtonSet.OK_CANCEL);
  if (r.getSelectedButton() !== ui.Button.OK) return;
  var url = r.getResponseText().trim();
  var p = PropertiesService.getScriptProperties();
  if (!url) { p.deleteProperty('SLACK_WEBHOOK'); ui.alert('Slack webhook removed.'); return; }
  if (!/^https:\/\/hooks\.slack\.com\//.test(url)) { ui.alert('That does not look like a Slack webhook URL.'); return; }
  p.setProperty('SLACK_WEBHOOK', url);
  ui.alert('Saved. Try Pacing Alarm ▸ Send test alert.');
}

function promptMetaConnection() {
  var ui = SpreadsheetApp.getUi();
  var t = ui.prompt('Connect Meta ads (1/2)',
    'Paste a Meta access token with ads_read permission.\nBest: a System User token from Business Settings ▸ System users.',
    ui.ButtonSet.OK_CANCEL);
  if (t.getSelectedButton() !== ui.Button.OK) return;
  var a = ui.prompt('Connect Meta ads (2/2)', 'Ad account IDs, comma separated (numbers only, e.g. 1234567890, 9876543210).', ui.ButtonSet.OK_CANCEL);
  if (a.getSelectedButton() !== ui.Button.OK) return;
  var ids = a.getResponseText().split(',').map(function (s) { return s.replace(/[^0-9]/g, ''); }).filter(String);
  PropertiesService.getScriptProperties().setProperties({ META_TOKEN: t.getResponseText().trim(), META_ACCOUNTS: ids.join(',') });
  ui.alert('Saved ' + ids.length + ' account(s). Run Pacing Alarm ▸ Pull Meta spend now to test.');
}

function promptWebAppKey() {
  var ui = SpreadsheetApp.getUi();
  var key = Utilities.getUuid().replace(/-/g, '').slice(0, 20);
  PropertiesService.getScriptProperties().setProperty('WEB_KEY', key);
  ui.alert('Live dashboard key created',
    'Key: ' + key + '\n\nNow: Deploy ▸ New deployment ▸ Web app ▸ Execute as: Me ▸ Who has access: Anyone ▸ Deploy.\n' +
    'Paste the web app URL and this key into pacing-tracker.utkarshjukaria.com ▸ Connect ▸ Google Sheet.\n' +
    'Without the key the URL returns nothing.', ui.ButtonSet.OK);
}

/* ---------------------------------------------------------------- Meta pull */

function pullMetaSpendDaily_() { if (PropertiesService.getScriptProperties().getProperty('META_TOKEN')) pullMetaSpend(true); }

/** Pulls daily campaign spend for the current month from every connected Meta ad account into Spend. */
function pullMetaSpend(silent) {
  var p = PropertiesService.getScriptProperties();
  var token = p.getProperty('META_TOKEN'), accounts = (p.getProperty('META_ACCOUNTS') || '').split(',').filter(String);
  if (!token || !accounts.length) { if (silent !== true) SpreadsheetApp.getUi().alert('Connect Meta ads first.'); return; }
  var tz = SpreadsheetApp.getActive().getSpreadsheetTimeZone();
  var now = new Date();
  var since = Utilities.formatDate(new Date(now.getFullYear(), now.getMonth(), 1), tz, 'yyyy-MM-dd');
  var until = Utilities.formatDate(new Date(now.getTime() - 864e5), tz, 'yyyy-MM-dd');
  var rows = [];
  accounts.forEach(function (id) {
    var url = 'https://graph.facebook.com/' + CFG.META_API_VERSION + '/act_' + id + '/insights?level=campaign' +
      '&fields=campaign_name,spend&time_increment=1&limit=500&time_range=' +
      encodeURIComponent(JSON.stringify({ since: since, until: until })) + '&access_token=' + encodeURIComponent(token);
    while (url) {
      var res = UrlFetchApp.fetch(url, { muteHttpExceptions: true });
      var json = JSON.parse(res.getContentText());
      if (json.error) { log_('act_' + id, 'Meta error', json.error.message); break; }
      (json.data || []).forEach(function (d) { rows.push([d.date_start, d.campaign_name, Number(d.spend), 'Meta', 'Meta API']); });
      url = json.paging && json.paging.next;
    }
  });
  var n = upsertSpend_(rows);
  if (silent !== true) SpreadsheetApp.getUi().alert('Meta: ' + n + ' day-campaign rows written to Spend.');
}

/**
 * Writes [date(yyyy-MM-dd), campaign, spend, platform, source] rows into Spend.
 * Same date + campaign replaces the old value, so re-running never double-counts.
 */
function upsertSpend_(rows) {
  if (!rows.length) return 0;
  var sh = SpreadsheetApp.getActive().getSheetByName(CFG.SPEND_SHEET);
  var tz = SpreadsheetApp.getActive().getSpreadsheetTimeZone();
  var last = Math.max(1, sh.getLastRow());
  var existing = last > 1 ? sh.getRange(2, 1, last - 1, 5).getValues() : [];
  var index = {};
  existing.forEach(function (r, i) {
    var d = r[0] instanceof Date ? Utilities.formatDate(r[0], tz, 'yyyy-MM-dd') : String(r[0]);
    index[d + '|' + r[1]] = i;
  });
  rows.forEach(function (r) {
    var parts = r[0].split('-');
    var row = [new Date(+parts[0], +parts[1] - 1, +parts[2]), r[1], r[2], r[3], r[4]];
    var k = r[0] + '|' + r[1];
    if (k in index) existing[index[k]] = row; else { index[k] = existing.length; existing.push(row); }
  });
  sh.getRange(2, 1, existing.length, 5).setValues(existing);
  sh.getRange(2, 1, existing.length, 1).setNumberFormat('yyyy-mm-dd');
  return rows.length;
}

/* ---------------------------------------------------------------- live link for the website */

/** Web app endpoint used by pacing-tracker.utkarshjukaria.com (Connect ▸ Google Sheet). Read-only. */
function doGet(e) {
  var key = PropertiesService.getScriptProperties().getProperty('WEB_KEY');
  if (!key || !e || !e.parameter || e.parameter.key !== key) return json_({ error: 'Missing or wrong key' });
  var ss = SpreadsheetApp.getActive(), tz = ss.getSpreadsheetTimeZone();
  var fmt = function (d) { return d instanceof Date ? Utilities.formatDate(d, tz, 'yyyy-MM-dd') : String(d || ''); };
  var setup = ss.getSheetByName(CFG.SETUP_SHEET).getRange(CFG.SETUP_FIRST_ROW, 1, 50, 10).getValues()
    .filter(function (r) { return r[2] !== ''; })
    .map(function (r) { return { client: r[0], platform: r[1], campaign: r[2], budget: Number(r[3]), start: fmt(r[4]),
      end: fmt(r[5]), threshold: r[6] === '' ? null : Number(r[6]), channel: r[7], active: r[9] !== 'No' }; });
  var settings = ss.getSheetByName(CFG.SETUP_SHEET).getRange('C3:C5').getValues();
  var sp = ss.getSheetByName(CFG.SPEND_SHEET);
  var spend = sp.getLastRow() > 1 ? sp.getRange(2, 1, sp.getLastRow() - 1, 4).getValues()
    .filter(function (r) { return r[0] !== '' && r[1] !== ''; })
    .map(function (r) { return [fmt(r[0]), r[1], Number(r[2]), r[3]]; }) : [];
  return json_({ name: ss.getName(), dataThrough: fmt(settings[0][0]) || null, window: Number(settings[1][0]) || 7,
    threshold: Number(settings[2][0]) || 0.1, campaigns: setup, spend: spend, generated: new Date().toISOString() });
}

function json_(o) { return ContentService.createTextOutput(JSON.stringify(o)).setMimeType(ContentService.MimeType.JSON); }

/* ---------------------------------------------------------------- helpers */

function log_(campaign, channel, message) {
  var ss = SpreadsheetApp.getActive();
  var sh = ss.getSheetByName(CFG.LOG_SHEET);
  if (!sh) {
    sh = ss.insertSheet(CFG.LOG_SHEET);
    sh.appendRow(['Sent at', 'Campaign', 'Channel', 'Message']);
    sh.getRange('A1:D1').setFontWeight('bold').setBackground('#1F1B16').setFontColor('#F3EDE2');
    sh.setFrozenRows(1); sh.setColumnWidth(4, 700);
  }
  sh.appendRow([new Date(), campaign, channel, message]);
}

function cleanupSentKeys_(props, today) {
  Object.keys(props.getProperties()).forEach(function (k) {
    if (k.indexOf('SENT_') === 0 && k !== 'SENT_' + today) props.deleteProperty(k);
  });
}

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