/**
 * Jsonpage for Google Sheets: custom functions and a menu.
 *
 * Install: in your sheet open Extensions > Apps Script, delete the sample code,
 * paste this file, save, then reload the sheet. A "Jsonpage" menu appears:
 * choose "Set API key" and paste your key (Script Properties, never in a cell).
 *
 * Functions (the first argument is a company name, a SIREN, a SIRET, a VAT number
 * or a global identifier such as FR-552032534):
 *   =JSONPAGE_SIREN(A2)               registry number of the best match
 *   =JSONPAGE_NAME(A2)                official name
 *   =JSONPAGE_VAT(A2)                 VAT number
 *   =JSONPAGE_VAT_STATUS(A2)          VAT check result (valid, invalid, pending...)
 *   =JSONPAGE_ADDRESS(A2)             formatted address
 *   =JSONPAGE_STATUS(A2)              active, closed or unknown
 *   =JSONPAGE_COMPANY(A2; "city")     any field: id, name, status, legal_form,
 *                                     activity, address, postal_code, city, vat, vat_status
 *   =JSONPAGE_VALIDATE(A2)            "OK" or the list of failed checks
 * Optional second argument of the name functions: a country code ("FR", "BE"...).
 *
 * Menu "Complete selected rows": for the selected rows, column A is read and
 * the official name, SIREN, VAT number and address are written in B, C, D, E.
 *
 * Each call counts as a request of your plan. Answers are cached for 6 hours.
 */

var JSONPAGE_BASE = 'https://api.jsonpage.com/v2';
var JSONPAGE_CACHE_SECONDS = 21600;

function onOpen() {
  SpreadsheetApp.getUi().createMenu('Jsonpage')
    .addItem('Set API key', 'jsonpageSetKey')
    .addItem('Complete selected rows', 'jsonpageCompleteSelection')
    .addToUi();
}

function jsonpageSetKey() {
  var ui = SpreadsheetApp.getUi();
  var answer = ui.prompt('Jsonpage API key', 'Paste your key (create one for free on jsonpage.com/account).', ui.ButtonSet.OK_CANCEL);
  if (answer.getSelectedButton() === ui.Button.OK && answer.getResponseText().trim()) {
    PropertiesService.getScriptProperties().setProperty('JSONPAGE_API_KEY', answer.getResponseText().trim());
  }
}

function jsonpageKey_() {
  var key = PropertiesService.getScriptProperties().getProperty('JSONPAGE_API_KEY');
  if (!key) {
    throw new Error('No API key: use the Jsonpage menu > Set API key.');
  }
  return key;
}

function jsonpageGet_(path, params) {
  var query = Object.keys(params || {}).map(function (k) {
    return encodeURIComponent(k) + '=' + encodeURIComponent(params[k]);
  }).join('&');
  var url = JSONPAGE_BASE + path + (query ? '?' + query : '');
  var cache = CacheService.getScriptCache();
  var cacheKey = 'jp:' + Utilities.base64EncodeWebSafe(Utilities.computeDigest(Utilities.DigestAlgorithm.MD5, url)).slice(0, 40);
  var hit = cache.get(cacheKey);
  if (hit) {
    return JSON.parse(hit);
  }
  var response = UrlFetchApp.fetch(url, { method: 'get', headers: { 'X-API-Key': jsonpageKey_() }, muteHttpExceptions: true });
  var code = response.getResponseCode();
  var body = JSON.parse(response.getContentText() || '{}');
  if (code === 401) {
    throw new Error('Invalid API key.');
  }
  if (code === 429) {
    throw new Error('Rate limit reached, try again in a minute.');
  }
  if (code >= 500) {
    throw new Error('Jsonpage is temporarily unavailable.');
  }
  var result = { code: code, body: body };
  if (code === 200 || code === 404) {
    cache.put(cacheKey, JSON.stringify(result), JSONPAGE_CACHE_SECONDS);
  }
  return result;
}

/** Turns what the user typed into a global identifier, or null when it has to be searched by name. */
function jsonpageIdentifier_(value, country) {
  var text = String(value || '').trim().toUpperCase().replace(/[\s.\-]/g, '');
  if (/^[A-Za-z]{2}-[0-9A-Za-z]+$/.test(String(value).trim())) {
    return String(value).trim().toUpperCase();
  }
  var vat = /^FR[0-9A-Z]{2}([0-9]{9})$/.exec(text);
  if (vat) {
    return 'FR-' + vat[1];
  }
  if (/^[0-9]{14}$/.test(text)) {
    return 'FR-' + text.slice(0, 9);
  }
  if (/^[0-9]{9}$/.test(text)) {
    return 'FR-' + text;
  }
  return null;
}

/** The identifier of the best match for a name, null when nothing matches. */
function jsonpageResolve_(value, country) {
  var id = jsonpageIdentifier_(value, country);
  if (id) {
    return id;
  }
  var text = String(value || '').trim();
  if (text.length < 3) {
    return null;
  }
  var params = { q: text, limit: 1 };
  if (country) {
    params.country = String(country).toUpperCase();
  }
  var found = jsonpageGet_('/autocomplete', params);
  var list = found.body && found.body.data;
  return list && list.length ? list[0].id : null;
}

function jsonpageCompanyData_(value, country) {
  var id = jsonpageResolve_(value, country);
  if (!id) {
    return null;
  }
  var result = jsonpageGet_('/companies/' + encodeURIComponent(id), { include: 'address,vat' });
  return result.code === 200 ? result.body.data : null;
}

function jsonpageField_(company, field) {
  var vat = company.vat || {};
  var address = company.address || {};
  var map = {
    id: company.id,
    siren: company.registry_id,
    name: company.name,
    status: company.status,
    legal_form: company.legal_form && company.legal_form.label,
    activity: company.activity && company.activity.label,
    address: address.formatted,
    postal_code: address.postal_code,
    city: address.city,
    vat: vat.number,
    vat_status: vat.status
  };
  var value = map[String(field).toLowerCase()];
  return value === undefined || value === null ? '' : value;
}

/**
 * Any field of a company.
 * @param {string} query Name, SIREN, SIRET, VAT number or global identifier.
 * @param {string} field id, siren, name, status, legal_form, activity, address, postal_code, city, vat, vat_status.
 * @param {string} country Optional country code for a search by name.
 * @customfunction
 */
function JSONPAGE_COMPANY(query, field, country) {
  if (Array.isArray(query)) {
    return query.map(function (row) { return [JSONPAGE_COMPANY(row[0], field, country)]; });
  }
  if (!query) {
    return '';
  }
  var company = jsonpageCompanyData_(query, country);
  return company ? jsonpageField_(company, field) : 'NOT_FOUND';
}

/** @customfunction */
function JSONPAGE_SIREN(query, country) { return JSONPAGE_COMPANY(query, 'siren', country); }

/** @customfunction */
function JSONPAGE_NAME(query, country) { return JSONPAGE_COMPANY(query, 'name', country); }

/** @customfunction */
function JSONPAGE_VAT(query, country) { return JSONPAGE_COMPANY(query, 'vat', country); }

/** @customfunction */
function JSONPAGE_VAT_STATUS(query, country) { return JSONPAGE_COMPANY(query, 'vat_status', country); }

/** @customfunction */
function JSONPAGE_ADDRESS(query, country) { return JSONPAGE_COMPANY(query, 'address', country); }

/** @customfunction */
function JSONPAGE_STATUS(query, country) { return JSONPAGE_COMPANY(query, 'status', country); }

/**
 * Checks a SIREN, SIRET or VAT number in one call: "OK" or the failed checks.
 * @param {string} value A SIREN, a SIRET or a VAT number.
 * @customfunction
 */
function JSONPAGE_VALIDATE(value) {
  if (Array.isArray(value)) {
    return value.map(function (row) { return [JSONPAGE_VALIDATE(row[0])]; });
  }
  var text = String(value || '').trim().replace(/[\s.\-]/g, '');
  if (!text) {
    return '';
  }
  var param = /^[A-Za-z]{2}[0-9A-Za-z]+$/.test(text) ? 'vat' : (text.length === 14 ? 'siret' : 'id');
  var arg = param === 'id' ? 'FR-' + text : text;
  var result = jsonpageGet_('/validate', (function () { var p = {}; p[param] = arg; return p; })());
  var checks = (result.body && result.body.data && result.body.data.checks) || [];
  var failed = checks.filter(function (c) { return !c.ok; }).map(function (c) { return c.code; });
  return failed.length ? 'KO: ' + failed.join(', ') : 'OK';
}

/** Menu action: completes the selected rows from the value in column A. */
function jsonpageCompleteSelection() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getActiveRange();
  var first = range.getRow();
  for (var r = first; r < first + range.getNumRows(); r++) {
    var query = sheet.getRange(r, 1).getValue();
    if (!query) {
      continue;
    }
    var company = jsonpageCompanyData_(query, '');
    sheet.getRange(r, 2, 1, 4).setValues([company ? [
      jsonpageField_(company, 'name'), jsonpageField_(company, 'siren'),
      jsonpageField_(company, 'vat'), jsonpageField_(company, 'address')
    ] : ['NOT_FOUND', '', '', '']]);
  }
}