/**
 * SimpleVerifier for Google Sheets: one-file install.
 *
 * Paste this whole file into Extensions > Apps Script > Code.gs, save, then
 * reload the sheet. A "SimpleVerifier" menu appears with:
 *   - Set API key            stores your key in your Google account's user
 *                            properties for this script, never in the sheet
 *   - Verify selected emails checks the selected column and writes Status and
 *                            Reason into the two columns to its right
 * and two formulas:
 *   =SV_VERIFY(A2)                 status and reason for one email address
 *   =SV_PHONE_FORMAT(A2, "US")     numbering-plan check for one phone number
 *
 * Needs a SimpleVerifier plan with API access and an API key from
 * https://simpleverifier.com/api-keys. Guide: https://simpleverifier.com/integrations/google-sheets
 *
 * Use this file or the three-file sidebar add-on, not both in one project:
 * both define SV_VERIFY.
 *
 * @OnlyCurrentDoc
 */

var SV_SITE = 'https://simpleverifier.com';
var SV_API = SV_SITE + '/api/v1';
var SV_KEY_PROPERTY = 'SV_API_KEY';

/** The API allows 120 calls a minute per key; 100 leaves room for formulas running at the same time. */
var SV_MS_PER_CALL = 600;
var SV_BATCH_SIZE = 10;
/** Apps Script stops a run after 6 minutes; stop starting batches well before that. */
var SV_RUN_BUDGET_MS = 4.5 * 60 * 1000;
var SV_RETRY_PAUSE_MS = 15 * 1000;
var SV_MAX_RETRIES = 4;
/** CacheService keeps an entry for at most 6 hours. */
var SV_CACHE_SECONDS = 6 * 60 * 60;

var SV_HEADERS = ['Status', 'Reason'];
var SV_DONE_STATUSES = ['valid', 'risky', 'invalid'];

var SV_MESSAGES = {
  noKey: 'No SimpleVerifier API key saved. Use SimpleVerifier > Set API key. Create a key at ' + SV_SITE + '/api-keys',
  badKeyFormat: 'That does not look like a SimpleVerifier API key. Keys start with sv_live_.',
  badKey: 'Your API key was not accepted (401). Check it at ' + SV_SITE + '/api-keys and save it again.',
  inactive: 'Your SimpleVerifier plan is not active (402). Renew it at ' + SV_SITE + '/pricing, then run again.',
  rateLimited: 'Too many checks at once (429): the limit is 120 a minute per key. Wait a minute and try again.',
  unavailable: 'SimpleVerifier is briefly unavailable. Wait a minute and try again.',
};

// ---------------------------------------------------------------------------
// Pure helpers (no Apps Script services), kept separate so they can be tested.
// ---------------------------------------------------------------------------

/** Maps an HTTP status to the reason a run must stop, or '' when the row result is usable. */
function svStopReason_(code) {
  if (code === 200 || code === 400) return '';
  if (code === 401) return 'badKey';
  if (code === 402) return 'inactive';
  if (code === 429) return 'rateLimited';
  return 'unavailable';
}

/** Whether a stop reason is worth retrying after a pause. */
function svIsRetryable_(reason) {
  return reason === 'rateLimited' || reason === 'unavailable';
}

function svLooksLikeKey_(key) {
  return /^sv_live_[A-Za-z0-9_-]+$/.test(String(key || '').trim());
}

function svNormaliseCountry_(country) {
  var code = String(country === undefined || country === null || country === '' ? 'US' : country).trim().toUpperCase();
  if (!/^[A-Z]{2}$/.test(code)) throw new Error('Country must be a two-letter code, such as US or GB.');
  return code;
}

function svEmailRow_(body) {
  return [body.status || '', body.reason || ''];
}

function svPhoneFormatRow_(body) {
  return [body.phone_status || '', body.phone_e164 || '', body.phone_country || '', body.phone_type || ''];
}

function svParseJson_(text) {
  try {
    return JSON.parse(text);
  } catch (err) {
    return {};
  }
}

/** A row already holding a final result is skipped, so running again never re-spends a check. */
function svIsDone_(status) {
  return SV_DONE_STATUSES.indexOf(String(status || '').toLowerCase()) !== -1;
}

/** Header detection for the first selected row: a heading has no @. */
function svIsHeaderRow_(rowNumber, value) {
  return rowNumber === 1 && String(value || '').indexOf('@') === -1;
}

/** How long to wait after a batch so the run averages SV_MS_PER_CALL per call. */
function svPaceMs_(startedAt, calls, now) {
  return Math.max(0, startedAt + calls * SV_MS_PER_CALL - now);
}

function svColumnLetter_(index) {
  var letters = '';
  var n = index;
  while (n > 0) {
    var rem = (n - 1) % 26;
    letters = String.fromCharCode(65 + rem) + letters;
    n = Math.floor((n - 1) / 26);
  }
  return letters;
}

// ---------------------------------------------------------------------------
// Menu and API key
// ---------------------------------------------------------------------------

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('SimpleVerifier')
    .addItem('Set API key', 'svSetApiKey')
    .addItem('Verify selected emails', 'svVerifySelectedEmails')
    .addSeparator()
    .addItem('Remove API key', 'svRemoveApiKey')
    .addItem('Help', 'svShowHelp')
    .addToUi();
}

function svGetKey_() {
  return PropertiesService.getUserProperties().getProperty(SV_KEY_PROPERTY) || '';
}

function svFetch_(url, key) {
  return UrlFetchApp.fetch(url, {
    method: 'get',
    headers: { Authorization: 'Bearer ' + key },
    muteHttpExceptions: true,
  });
}

/** Checks a key without spending a verification: the API checks the key before it asks for an email. */
function svCheckKey_(key) {
  var reason = svStopReason_(svFetch_(SV_API + '/verify', key).getResponseCode());
  if (reason) throw new Error(SV_MESSAGES[reason]);
}

function svSetApiKey() {
  var ui = SpreadsheetApp.getUi();
  var current = svGetKey_();
  var prompt = ui.prompt(
    'SimpleVerifier API key',
    (current ? 'A key ending in ' + current.slice(-4) + ' is saved. ' : '') +
      'Paste your API key from ' + SV_SITE + '/api-keys. It is stored in your Google account, not in this sheet.',
    ui.ButtonSet.OK_CANCEL
  );
  if (prompt.getSelectedButton() !== ui.Button.OK) return;
  var key = prompt.getResponseText().trim();
  if (!svLooksLikeKey_(key)) {
    ui.alert(SV_MESSAGES.badKeyFormat);
    return;
  }
  try {
    svCheckKey_(key);
  } catch (err) {
    ui.alert(err.message);
    return;
  }
  PropertiesService.getUserProperties().setProperty(SV_KEY_PROPERTY, key);
  ui.alert('Saved. Select a column of email addresses and choose SimpleVerifier > Verify selected emails.');
}

function svRemoveApiKey() {
  PropertiesService.getUserProperties().deleteProperty(SV_KEY_PROPERTY);
  SpreadsheetApp.getUi().alert('Your SimpleVerifier API key was removed from this script.');
}

function svShowHelp() {
  SpreadsheetApp.getUi().alert(
    'SimpleVerifier',
    'Verify selected emails: select one column of addresses and run it from this menu. Status and Reason go into ' +
      'the two columns to the right. Rows that already have a result are skipped, so you can run it again to ' +
      'carry on after a stop.\n\nFormulas: =SV_VERIFY(A2) and =SV_PHONE_FORMAT(A2, "US").\n\nGuide: ' +
      SV_SITE + '/integrations/google-sheets',
    SpreadsheetApp.getUi().ButtonSet.OK
  );
}

// ---------------------------------------------------------------------------
// Verify selected emails
// ---------------------------------------------------------------------------

function svVerifySelectedEmails() {
  var ui = SpreadsheetApp.getUi();
  var key = svGetKey_();
  if (!key) {
    ui.alert(SV_MESSAGES.noKey);
    return;
  }

  var range = SpreadsheetApp.getActiveRange();
  if (!range || range.getNumColumns() !== 1) {
    ui.alert('Select the email addresses in one column first, for example A2:A500.');
    return;
  }

  var sheet = range.getSheet();
  var inputCol = range.getColumn();
  var outCol = inputCol + 1;
  var firstRow = range.getRow();
  var lastRow = Math.min(range.getLastRow(), Math.max(sheet.getLastRow(), firstRow));
  var rowCount = lastRow - firstRow + 1;
  var inputs = sheet.getRange(firstRow, inputCol, rowCount, 1).getDisplayValues();
  var outRange = sheet.getRange(firstRow, outCol, rowCount, SV_HEADERS.length);
  var existing = outRange.getDisplayValues();

  var startIndex = svIsHeaderRow_(firstRow, inputs[0][0]) ? 1 : 0;
  var hasOtherData = existing.some(function (row, i) {
    if (i < startIndex) return false;
    return (row[0] || row[1]) && !svIsDone_(row[0]) && row[0] !== 'error';
  });
  if (hasOtherData) {
    var columns = svColumnLetter_(outCol) + ' and ' + svColumnLetter_(outCol + 1);
    var answer = ui.alert(
      'Overwrite columns ' + columns + '?',
      'Results are written into columns ' + columns + ', which already hold other data in the selected rows.',
      ui.ButtonSet.YES_NO
    );
    if (answer !== ui.Button.YES) return;
  }
  if (startIndex === 1 && !existing[0][0] && !existing[0][1]) {
    sheet.getRange(firstRow, outCol, 1, SV_HEADERS.length).setValues([SV_HEADERS]).setFontWeight('bold');
  }

  var pending = [];
  for (var i = startIndex; i < rowCount; i++) {
    var value = String(inputs[i][0]).trim();
    if (value && !svIsDone_(existing[i][0])) pending.push({ index: i, email: value });
  }
  if (!pending.length) {
    ui.alert('Nothing to verify: every selected row is empty or already has a result.');
    return;
  }

  var deadline = Date.now() + SV_RUN_BUDGET_MS;
  var counts = { valid: 0, risky: 0, invalid: 0, error: 0 };
  var done = 0;
  var retries = 0;
  var stopMessage = '';

  while (done < pending.length) {
    if (Date.now() > deadline) {
      stopMessage = 'Stopped before Google\'s 6-minute limit. Run Verify selected emails again on the same ' +
        'selection to carry on; finished rows are skipped.';
      break;
    }
    var batch = pending.slice(done, done + SV_BATCH_SIZE);
    var started = Date.now();
    var responses = UrlFetchApp.fetchAll(batch.map(function (item) {
      return {
        url: SV_API + '/verify?email=' + encodeURIComponent(item.email),
        method: 'get',
        headers: { Authorization: 'Bearer ' + key },
        muteHttpExceptions: true,
      };
    }));

    var stopReason = '';
    var completed = 0;
    for (var r = 0; r < responses.length; r++) {
      var code = responses[r].getResponseCode();
      stopReason = svStopReason_(code);
      if (stopReason) break;
      var body = svParseJson_(responses[r].getContentText());
      var row = code === 200 ? svEmailRow_(body) : ['error', body.error || 'The API could not read this value.'];
      sheet.getRange(firstRow + batch[r].index, outCol, 1, SV_HEADERS.length).setValues([row]);
      counts[row[0]] = (counts[row[0]] || 0) + 1;
      completed += 1;
    }
    done += completed;
    SpreadsheetApp.flush();

    if (stopReason) {
      if (!svIsRetryable_(stopReason) || retries >= SV_MAX_RETRIES) {
        stopMessage = SV_MESSAGES[stopReason];
        break;
      }
      retries += 1;
      SpreadsheetApp.getActiveSpreadsheet().toast('Pausing 15 seconds: ' + SV_MESSAGES[stopReason], 'SimpleVerifier', 15);
      Utilities.sleep(SV_RETRY_PAUSE_MS);
      continue;
    }

    retries = 0;
    SpreadsheetApp.getActiveSpreadsheet().toast(done + ' of ' + pending.length + ' checked', 'SimpleVerifier', 5);
    Utilities.sleep(svPaceMs_(started, batch.length, Date.now()));
  }

  var summary = done + ' of ' + pending.length + ' checked: ' + counts.valid + ' valid, ' + counts.risky +
    ' risky, ' + counts.invalid + ' invalid' + (counts.error ? ', ' + counts.error + ' unreadable' : '') + '.';
  ui.alert(stopMessage ? summary + '\n\n' + stopMessage : summary);
}

// ---------------------------------------------------------------------------
// Custom functions
// ---------------------------------------------------------------------------

function svSingleValue_(input, name) {
  if (Array.isArray(input)) {
    throw new Error(name + ' takes one cell. For a column, select it and use SimpleVerifier > Verify selected emails.');
  }
  return String(input === null || input === undefined ? '' : input).trim();
}

function svCache_() {
  try {
    return CacheService.getDocumentCache();
  } catch (err) {
    return null;
  }
}

/** Formulas cannot show dialogs, so every failure is thrown as a message the cell shows on hover. */
function svCallForCell_(url, cacheKey) {
  var cache = svCache_();
  var cached = cache ? cache.get(cacheKey) : null;
  if (cached) return JSON.parse(cached);

  var key = svGetKey_();
  if (!key) throw new Error(SV_MESSAGES.noKey);

  var response = svFetch_(url, key);
  if (response.getResponseCode() === 429) {
    Utilities.sleep(2000 + Math.floor(Math.random() * 3000));
    response = svFetch_(url, key);
  }
  var code = response.getResponseCode();
  var body = svParseJson_(response.getContentText());
  if (code === 400) throw new Error(body.error || 'The API could not read this value.');
  var reason = svStopReason_(code);
  if (reason) throw new Error(SV_MESSAGES[reason]);

  if (cache) cache.put(cacheKey, JSON.stringify(body), SV_CACHE_SECONDS);
  return body;
}

/**
 * Verifies one email address with SimpleVerifier: valid, risky or invalid, and
 * the reason, in two cells. Results are cached for 6 hours.
 *
 * @param {string} email The email address, or a cell that holds one.
 * @return The status and reason.
 * @customfunction
 */
function SV_VERIFY(email) {
  var value = svSingleValue_(email, 'SV_VERIFY');
  if (!value) return '';
  var body = svCallForCell_(SV_API + '/verify?email=' + encodeURIComponent(value), 'sv:e:' + value.toLowerCase());
  return [svEmailRow_(body)];
}

/**
 * Checks one phone number against its country's numbering plan with
 * SimpleVerifier: status, E.164 form, country and line type from the numbering
 * plan, in four cells. No live lookup is made, and it does not say whether the
 * number is in service. Results are cached for 6 hours.
 *
 * @param {string} phone The phone number, or a cell that holds one.
 * @param {string} country Two-letter country for numbers written without +, such as "GB". Defaults to "US".
 * @return The status, E.164 number, country and line type.
 * @customfunction
 */
function SV_PHONE_FORMAT(phone, country) {
  var value = svSingleValue_(phone, 'SV_PHONE_FORMAT');
  if (!value) return '';
  var region = svNormaliseCountry_(country);
  var url = SV_API + '/phone?phone=' + encodeURIComponent(value) + '&country=' + region + '&live=false';
  var body = svCallForCell_(url, 'sv:p:' + region + ':' + value);
  return [svPhoneFormatRow_(body)];
}