/**
 * AYBARION TRADE for Google Sheets: custom functions on the AYBARION public API (Apps Script, V8 runtime).
 *
 * Setup (developers.html#spreadsheets): Extensions > Apps Script, paste this file, Sidebar.html and appsscript.json, save, reload
 * the spreadsheet, then AYBARION > Set API key. The key is an AYBARION API key (ayb_live_…) created by an owner or admin on
 * Integrations with the scopes the functions need (trade:read, requirements:read, directory:read, companies:read).
 *
 *   =AYB_IMPORTS(country, hs4, [year])        imports of an EU reporter by partner country (Eurostat Comext, EUR)
 *   =AYB_TOP_PARTNERS(country, hs, [flow])    top partner countries of an EU reporter, import (default) or export
 *   =AYB_DUTY(dest, hs, [origin])             import duty of a destination (GB, EU 27, US) for an HS code and an origin
 *   =AYB_DUTY_US(hts)                         US HTS tariff lines: general, special and column 2 rates
 *   =AYB_COMPANY(id)                          one company of the directory (digits) or one legal entity by LEI (20 characters)
 *
 * Every result is a table (2D array): a header row, the data rows and, last, a row with the source and its licence.
 * - The key is kept in the user properties of the person who saves it (PropertiesService.getUserProperties). Sheets runs custom
 *   functions with the properties of the spreadsheet owner, so in a shared spreadsheet the owner's key answers the functions.
 * - Answers are cached for 6 hours (CacheService, the maximum), per key and address; AYBARION > Clear cached results starts over.
 * - Requests go to https://aybarion.com only (appsscript.json urlFetchWhitelist); the key is sent in the Authorization header,
 *   never in the address. No CORS is involved: UrlFetchApp runs on Google's servers.
 * - The API returns company-level fields only: never e-mails, phone numbers or names of people.
 */

var AYB_BASE = 'https://aybarion.com';
var AYB_PUBLIC = '/api/v1/public';
var AYB_KEY_PROP = 'AYB_API_KEY';
var AYB_GEN_PROP = 'AYB_CACHE_GEN';
var AYB_CACHE_SECONDS = 21600; // 6 hours, the CacheService maximum
var AYB_CACHE_MAX = 95000; // CacheService keeps values up to 100 KB
var AYB_KEY_RE = /^ayb_live_[A-Za-z0-9_-]{20,64}$/;
var AYB_ISO2_RE = /^[A-Z]{2}$/;

/* ------------------------------------------------------------------ menu and sidebar */

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('AYBARION')
    .addItem('Set API key', 'aybShowSidebar')
    .addItem('Clear cached results', 'aybClearCache')
    .addToUi();
}

function onInstall(e) {
  onOpen(e);
}

function aybShowSidebar() {
  var html = HtmlService.createHtmlOutputFromFile('Sidebar').setTitle('AYBARION TRADE');
  SpreadsheetApp.getUi().showSidebar(html);
}

/** Sidebar: whether a key is saved, with its visible prefix only (the full key is never sent back to the browser). */
function aybKeyStatus() {
  var key = PropertiesService.getUserProperties().getProperty(AYB_KEY_PROP);
  return { set: !!key, prefix: key ? key.slice(0, 13) : null };
}

/** Sidebar: saves the key after checking its form and asking the API who it belongs to (GET /me, not cached). */
function aybSaveKey(key) {
  key = String(key || '').trim();
  if (!AYB_KEY_RE.test(key)) throw new Error('This is not an AYBARION API key. Keys start with ayb_live_ and are shown once when they are created.');
  var r = aybRequest_('/me', {}, { key: key, noCache: true });
  if (r.status !== 200) throw new Error(aybErrorText_(r.status, r.json));
  PropertiesService.getUserProperties().setProperty(AYB_KEY_PROP, key);
  aybClearCache();
  return aybMeSummary_(r.json);
}

/** Sidebar: removes the saved key. */
function aybRemoveKey() {
  PropertiesService.getUserProperties().deleteProperty(AYB_KEY_PROP);
  aybClearCache();
  return aybKeyStatus();
}

/** Sidebar: checks the saved key (plan, scopes, today's use). */
function aybTestKey() {
  var r = aybRequest_('/me', {}, { noCache: true });
  if (r.status !== 200) throw new Error(aybErrorText_(r.status, r.json));
  return aybMeSummary_(r.json);
}

/** Cached answers cannot be listed, so clearing moves to a new cache generation (part of every cache key). */
function aybClearCache() {
  var p = PropertiesService.getUserProperties();
  p.setProperty(AYB_GEN_PROP, String(Number(p.getProperty(AYB_GEN_PROP) || 0) + 1));
}

function aybMeSummary_(j) {
  var d = (j && j.data) || {};
  var k = d.key || {};
  var lim = d.limits || {};
  var use = d.usage || {};
  return {
    prefix: k.prefix || null, scopes: k.scopes || [], expiresAt: k.expires_at || null, plan: (d.member && d.member.plan) || null,
    perMinute: lim.per_minute_per_key == null ? null : lim.per_minute_per_key, perDay: lim.per_day_company == null ? null : lim.per_day_company,
    usedToday: use.company_today == null ? null : use.company_today,
  };
}

/* ------------------------------------------------------------------ custom functions */

/**
 * Imports of an EU reporter country by partner country for one product (Eurostat Comext, values in EUR).
 *
 * @param {string} country Reporting country, ISO alpha-2 (an EU member such as "DE") or "EU27".
 * @param {string} hs4 HS code with 2 or 4 digits, e.g. "9401", or "TOTAL".
 * @param {string} year Optional period such as "2024" or "2025-07"; empty for the latest.
 * @return Partner, value in EUR, net mass in kg, period and record id, then the source row.
 * @customfunction
 */
function AYB_IMPORTS(country, hs4, year) {
  var reporter = aybCountry_(country, 'country', { allowEu27: true });
  var hs = aybTradeHs_(hs4, 'hs4');
  var period = aybPeriod_(year);
  var q = { reporter: reporter, hs: hs, flow: 'import', per_page: 500 };
  if (period) q.period = period;
  var pages = [aybGet_('/trade/flows', q)];
  var pg = (pages[0].meta && pages[0].meta.pagination) || {};
  for (var p = 2; p <= Math.min(pg.total_pages || 1, 5); p++) {
    q.page = p;
    pages.push(aybGet_('/trade/flows', q));
  }
  return aybImportsTable_(pages);
}

/**
 * Top partner countries of an EU reporter for one product (Eurostat Comext, values in EUR).
 *
 * @param {string} country Reporting country, ISO alpha-2 (an EU member such as "DE") or "EU27".
 * @param {string} hs HS code with 2 or 4 digits, e.g. "0802", or "TOTAL".
 * @param {string} flow Optional: "import" (default) or "export".
 * @return Rank, partner, value in EUR, net mass in kg and share, then the source row.
 * @customfunction
 */
function AYB_TOP_PARTNERS(country, hs, flow) {
  var reporter = aybCountry_(country, 'country', { allowEu27: true });
  var code = aybTradeHs_(hs, 'hs');
  var fl = aybFlow_(flow);
  return aybTopPartnersTable_(aybGet_('/trade/top-partners', { reporter: reporter, hs: code, flow: fl, limit: 50 }));
}

/**
 * Import duty of a destination for an HS code and an origin: the rate that applies, its basis, the third country duty and the
 * best preference (UK tariff, EU TARIC, US HTS).
 *
 * @param {string} dest Destination country, ISO alpha-2: "GB", an EU member or "US".
 * @param {string} hs HS or tariff code with 4 to 10 digits, e.g. "640399".
 * @param {string} origin Optional country of origin, ISO alpha-2, e.g. "TR".
 * @return Code, applied rate, basis, third country duty, preference and trade defence count per tariff line, then the source row.
 * @customfunction
 */
function AYB_DUTY(dest, hs, origin) {
  var q = { dest: aybCountry_(dest, 'dest'), hs: aybCode_(hs, 'hs', 4, 10) };
  var og = aybCountry_(origin, 'origin', { optional: true });
  if (og) q.origin = og;
  // the duty endpoint of the public API (key, scope requirements:read); a server without it (404 or 405 without an error code, remembered
  // for 6 hours) answers through the open route of the duty calculator on tools.html
  var cache = CacheService.getUserCache();
  var r = cache.get('ayb1:duty-route') === 'open' ? null : aybRequest_('/duty', q);
  if (!r || ((r.status === 404 || r.status === 405) && !(r.json && r.json.error && r.json.error.code))) {
    if (r) cache.put('ayb1:duty-route', 'open', AYB_CACHE_SECONDS);
    r = aybRequest_('/api/v1/duty', q, { absolute: true });
    // the open route takes no key: a 401 there is the preview password of the site, not the key
    if (r.status === 401) throw new Error('AYBARION: the duty service is not open to spreadsheets on this server (HTTP 401).');
  }
  if (r.status !== 200) throw new Error(aybErrorText_(r.status, r.json));
  return aybDutyTable_(r.json);
}

/**
 * US import duty rates of the Harmonized Tariff Schedule for an HTS code.
 *
 * @param {string} hts HTS code with 4 to 10 digits, e.g. "6403996075".
 * @return HTS code, description, unit, general, special and column 2 rates, then the source row.
 * @customfunction
 */
function AYB_DUTY_US(hts) {
  return aybDutyUsTable_(aybGet_('/requirements', { country: 'US', hs: aybCode_(hts, 'hts', 4, 10) }), 'US');
}

/**
 * One company: a directory id (digits) or a legal entity identifier (LEI, 20 characters). Company-level fields only.
 *
 * @param {string} id Directory company id or LEI.
 * @return Field and value rows, then the source row.
 * @customfunction
 */
function AYB_COMPANY(id) {
  var raw = aybScalar_(id, 'id');
  var v = String(raw == null ? '' : raw).trim().toUpperCase();
  if (/^\d{1,12}$/.test(v)) return aybCompanyTable_(aybGet_('/directory/companies/' + v, {}));
  if (/^[A-Z0-9]{20}$/.test(v)) return aybCompanyTable_(aybGet_('/companies/' + v, {}));
  throw new Error('id must be a directory company id (digits) or a 20 character LEI');
}

/* ------------------------------------------------------------------ requests */

/** GET on the public API → { status, json }. Answers with status 200 are cached for 6 hours per key and address. */
function aybRequest_(path, query, opts) {
  opts = opts || {};
  var props = PropertiesService.getUserProperties();
  var key = opts.key || props.getProperty(AYB_KEY_PROP);
  if (!key) throw new Error('No AYBARION API key yet: open AYBARION > Set API key in the menu.');
  var url = AYB_BASE + (opts.absolute ? '' : AYB_PUBLIC) + path + aybQuery_(query);
  var cache = opts.noCache ? null : CacheService.getUserCache();
  var ck = cache ? 'ayb1:' + aybHex_(key + '\n' + (props.getProperty(AYB_GEN_PROP) || '0') + '\n' + url) : null;
  if (cache) {
    var hit = cache.get(ck);
    if (hit) return { status: 200, json: JSON.parse(hit), cached: true };
  }
  var res = UrlFetchApp.fetch(url, {
    method: 'get', muteHttpExceptions: true, followRedirects: false,
    headers: { Authorization: 'Bearer ' + key, Accept: 'application/json' },
  });
  var status = res.getResponseCode();
  var text = res.getContentText();
  var json = null;
  try {
    json = JSON.parse(text);
  } catch (e) {
    json = null;
  }
  if (status === 200 && json && cache && text.length <= AYB_CACHE_MAX) cache.put(ck, text, AYB_CACHE_SECONDS);
  return { status: status, json: json };
}

function aybGet_(path, query) {
  var r = aybRequest_(path, query);
  if (r.status !== 200) throw new Error(aybErrorText_(r.status, r.json));
  return r.json;
}

function aybQuery_(q) {
  var parts = [];
  for (var k in q) if (q[k] !== undefined && q[k] !== null && q[k] !== '') parts.push(encodeURIComponent(k) + '=' + encodeURIComponent(String(q[k])));
  return parts.length ? '?' + parts.join('&') : '';
}

function aybHex_(s) {
  var b = Utilities.computeDigest(Utilities.DigestAlgorithm.SHA_256, s, Utilities.Charset.UTF_8);
  var out = '';
  for (var i = 0; i < b.length; i++) out += ((b[i] + 256) % 256).toString(16).padStart(2, '0');
  return out;
}

/** Error text of an API answer: the API's own message, with what to do for a missing key, scope or plan. */
function aybErrorText_(status, json) {
  var e = (json && json.error) || {};
  var msg = e.message || (status === 0 ? 'AYBARION could not be reached' : 'The request failed (HTTP ' + status + ')');
  if (status === 401) msg += '. Set a valid key: AYBARION > Set API key.';
  else if (status === 403 && e.code === 'insufficient_scope') msg += '. Create a key with this scope on aybarion.com, Integrations.';
  else if (status === 402) msg += '. The Developer API needs a Professional plan or higher with an active subscription or trial.';
  return 'AYBARION: ' + msg;
}

/* ------------------------------------------------------------------ arguments (one cell each) */

function aybScalar_(v, name) {
  if (Array.isArray(v)) {
    if (v.length === 1 && Array.isArray(v[0]) && v[0].length === 1) return v[0][0];
    throw new Error(name + ' must be one value, not a range');
  }
  return v;
}

function aybCountry_(v, name, opts) {
  opts = opts || {};
  v = aybScalar_(v, name);
  var c = String(v == null ? '' : v).trim().toUpperCase();
  if (!c) {
    if (opts.optional) return null;
    throw new Error(name + ' is required: an ISO alpha-2 country code such as DE');
  }
  if (opts.allowEu27 && c === 'EU27') return c;
  if (!AYB_ISO2_RE.test(c)) throw new Error(name + ' must be an ISO alpha-2 country code such as DE');
  return c;
}

/** HS or tariff code from text or a number cell: digits only, a leading zero restored when a number cell dropped it. */
function aybCode_(v, name, min, max) {
  v = aybScalar_(v, name);
  var s = String(v == null ? '' : v).replace(/[\s.]/g, '');
  if (!/^\d+$/.test(s)) throw new Error(name + ' must be an HS code of ' + min + ' to ' + max + ' digits');
  if (s.length % 2 === 1) s = '0' + s;
  if (s.length < min || s.length > max) throw new Error(name + ' must be an HS code of ' + min + ' to ' + max + ' digits');
  return s;
}

function aybTradeHs_(v, name) {
  var raw = aybScalar_(v, name);
  if (String(raw == null ? '' : raw).trim().toUpperCase() === 'TOTAL') return 'TOTAL';
  return aybCode_(raw, name, 2, 4);
}

function aybPeriod_(v) {
  v = aybScalar_(v, 'year');
  if (v === undefined || v === null || v === '') return null;
  if (Object.prototype.toString.call(v) === '[object Date]') return String(v.getFullYear());
  var s = String(v).trim();
  if (/^latest$/i.test(s)) return null;
  if (!/^\d{4}(-\d{2})?$/.test(s)) throw new Error('year must be a year such as 2024, a month such as 2025-07, or empty for the latest');
  return s;
}

function aybFlow_(v) {
  v = aybScalar_(v, 'flow');
  var s = String(v == null ? '' : v).trim().toLowerCase();
  if (!s || s === 'import' || s === 'imports' || s === '1') return 'import';
  if (s === 'export' || s === 'exports' || s === '2') return 'export';
  throw new Error('flow must be import or export');
}

/* ------------------------------------------------------------------ tables (same shapes as assets/js/excel-functions.js) */

function aybCell_(v) {
  return v === undefined || v === null ? '' : v;
}

/** Header, rows, then note rows and the source row last; every row padded to the header width (a rectangle). */
function aybTable_(header, rows, notes, source) {
  var w = header.length;
  var out = [header];
  var pad = function (r) {
    var x = r.map(aybCell_);
    while (x.length < w) x.push('');
    return x;
  };
  rows.forEach(function (r) { out.push(pad(r)); });
  (notes || []).filter(Boolean).forEach(function (n) { out.push(pad([n])); });
  out.push(pad([source || 'Source: AYBARION TRADE public API']));
  return out;
}

/** "Source: name, publisher. Licence: X. attribution. Snapshot: date. url" from one source object (camel or snake case). */
function aybSourceLine_(s, extra) {
  s = s || {};
  var name = [s.name || s.source, s.dataset].filter(Boolean).join(' ');
  var who = [name, s.publisher].filter(Boolean).join(', ');
  var parts = ['Source: ' + (who || 'AYBARION TRADE public API')];
  if (s.licence) parts.push('Licence: ' + s.licence);
  if (s.attribution && s.attribution !== who) parts.push(String(s.attribution).replace(/^Source:\s*/i, 'Attribution: '));
  if (s.share_alike) parts.push(s.share_alike);
  var snap = s.snapshot || s.period;
  if (snap) parts.push((s.period && !s.snapshot ? 'Period: ' : 'Snapshot: ') + snap);
  if (extra) parts.push(extra);
  if (s.url) parts.push(s.url);
  return parts.map(function (x) { return String(x).replace(/[.\s]+$/, ''); }).join('. ');
}

function aybImportsTable_(pages) {
  var first = pages[0] || {};
  var meta = first.meta || {};
  var rows = [];
  pages.forEach(function (p) { (p.data || []).forEach(function (r) { rows.push(r); }); });
  rows.sort(function (a, b) { return (b.value_eur || 0) - (a.value_eur || 0); });
  var f = meta.filters || {};
  var product = rows.length ? rows[0].product : null;
  var notes = [];
  if (!rows.length) notes.push('No partner rows for these filters.');
  else notes.push([f.reporter || rows[0].reporter, 'imports of HS', f.hs || rows[0].hs, product ? '(' + product + ')' : '', 'by partner, period', f.period || rows[0].period].filter(Boolean).join(' '));
  var pg = meta.pagination || {};
  if (pg.total && pg.total > rows.length) notes.push('Showing ' + rows.length + ' of ' + pg.total + ' rows.');
  return aybTable_(['Partner', 'Value (EUR)', 'Net mass (kg)', 'Period', 'Record id'],
    rows.map(function (r) { return [r.partner, r.value_eur, r.net_mass_kg, r.period, r.id]; }), notes, aybSourceLine_(meta.source));
}

function aybTopPartnersTable_(j) {
  var m = j.meta || {};
  var rows = j.data || [];
  var notes = [[m.reporter, m.flow === 'export' ? 'exports' : 'imports', 'of HS', m.hs, m.product ? '(' + m.product + ')' : '', 'period', m.period].filter(Boolean).join(' ')
    + (m.world_value_eur != null ? '. World total: ' + m.world_value_eur + ' EUR' : '')];
  if (!rows.length) notes.unshift('No partner rows for these filters.');
  return aybTable_(['Rank', 'Partner', 'Value (EUR)', 'Net mass (kg)', 'Share (%)'],
    rows.map(function (r) { return [r.rank, r.partner, r.value_eur, r.net_mass_kg, r.share_pct]; }), notes, aybSourceLine_(m.source));
}

/** Duty answer: the open route's object, or the public API envelope { data, meta } (camel or snake case keys). */
function aybDutyTable_(j) {
  var d = j && j.data && (j.data.lines || j.data.available !== undefined) ? j.data : j || {};
  var g = function (o, a, b) { return o ? (o[a] !== undefined ? o[a] : o[b]) : undefined; };
  var ms = j && j.meta && j.meta.source;
  // public API envelope: one tariff source → its own entry (name and snapshot), else the joined publisher and licence
  var src = d.source || (ms && ms.sources && ms.sources.length === 1 ? ms.sources[0] : ms) || null;
  var srcLine = src ? aybSourceLine_(src) : 'Source: AYBARION TRADE duty calculator';
  if (d.available === false) return aybTable_(['Duty'], [['No duty rate: ' + (d.note || 'no data for this destination and code')]], [], srcLine);
  var lines = d.lines || [];
  var rows = lines.map(function (l) {
    var a = l.applied || {};
    var pref = l.preference;
    var prefText = pref ? [pref.text || g(pref.parsed, 'text'), g(pref, 'geoName', 'geo_name') || pref.program].filter(Boolean).join(' for ') : '';
    var mfn = l.mfn ? l.mfn.text || g(l.mfn.parsed, 'text') : '';
    var td = g(l, 'tradeDefenceTotal', 'trade_defence_total');
    return [l.code, a.rate || g(a.parsed, 'text') || '', a.basis || '', mfn, prefText, td == null ? (l.tradeDefence || l.trade_defence || []).length : td];
  });
  var notes = [];
  var head = [d.dest, d.via ? '(' + d.via + ')' : '', 'HS', d.hs, d.origin ? 'origin ' + d.origin : 'no origin given'].filter(Boolean).join(' ');
  notes.push('Import duty ' + head);
  if (d.total && d.total > lines.length) notes.push('Showing ' + lines.length + ' of ' + d.total + ' tariff lines: use a longer code.');
  if (d.note) notes.push(d.note);
  if (!rows.length) notes.unshift('No tariff lines for this code.');
  return aybTable_(['Code', 'Applied rate', 'Basis', 'Third country duty (MFN)', 'Preference', 'Trade defence measures'], rows, notes, srcLine);
}

function aybDutyUsTable_(j) {
  var d = j.data || {};
  var r = d.rates;
  var src = ((j.meta && j.meta.source && j.meta.source.sources) || []).filter(function (s) {
    return (s.record_ids || []).some(function (x) { return /:rate$/.test(x); });
  })[0] || (j.meta && j.meta.source) || null;
  if (!r || !(r.lines || []).length) return aybTable_(['HTS code'], [['No US tariff lines for HTS ' + (d.hs || '')]], [], aybSourceLine_(src));
  var rows = r.lines.map(function (l) {
    var special = (l.special || []).map(function (s) {
      return s.rate + ' (' + (s.programs || []).map(function (p) { return p.code; }).join(' ') + ')';
    }).join('; ');
    return [l.code, l.description, l.unit, l.general, special, l.other];
  });
  var notes = [];
  if (r.total && r.total > r.lines.length) notes.push('Showing ' + r.lines.length + ' of ' + r.total + ' tariff lines: use a longer code.');
  if (r.additional_duties) notes.push(r.additional_duties);
  return aybTable_(['HTS code', 'Description', 'Unit', 'General rate', 'Special rate (programmes)', 'Column 2 rate'], rows, notes, aybSourceLine_(src));
}

var AYB_COMPANY_FIELDS = [
  ['legal_name', 'Legal name'], ['country', 'Country'], ['city', 'City'], ['status', 'Status'], ['legal_form', 'Legal form'],
  ['registry_id', 'Registry id'], ['lei', 'LEI'], ['nace', 'NACE section'], ['nace_basis', 'NACE basis'], ['founded_year', 'Founded'],
  ['website', 'Website'], ['id', 'Directory id'], ['passport', 'Record'], ['record_id', 'Record id'], ['source', 'Source id'], ['licence', 'Licence'],
];

function aybCompanyTable_(j) {
  var c = j.data || {};
  var rows = AYB_COMPANY_FIELDS.filter(function (f) { return c[f[0]] !== undefined && c[f[0]] !== null && c[f[0]] !== ''; })
    .map(function (f) { return [f[1], c[f[0]]]; });
  var s = (j.meta && j.meta.source) || {};
  var one = (s.sources || [])[0] || s;
  return aybTable_(['Field', 'Value'], rows, [], aybSourceLine_(one));
}