/**
 * Google Forms → InsightHub lead ingestion.
 *
 * Bind this to the FORM (⋮ → Apps Script), not the linked Sheet: only the
 * form-bound event exposes `e.response.getId()`, the stable response id we
 * send as `external_id` so a retry can never create a duplicate lead.
 *
 * Runs on Google's servers under the installing account — the respondent
 * never sees this code and the API key never reaches their browser.
 *
 * SETUP
 *   1. Project Settings → Script Properties:
 *        GATEWAY_URL = https://insighthub-gateway.sg5host.com/v2/leads
 *        API_KEY     = <a `public`-scope InsightHub key>
 *   2. Triggers → Add Trigger → onFormSubmit / From form / On form submit.
 *      MUST be an installable trigger. A *simple* onFormSubmit runs
 *      unauthorised and cannot call UrlFetchApp at all — it will fail
 *      silently every time.
 *   3. Triggers → Add Trigger → retryFailedSends / Time-driven /
 *      Minutes timer / Every 15 minutes.
 *
 * Install under a shared or service Google account, not a personal one:
 * the trigger runs as whoever installs it and stops firing silently if
 * that account is ever suspended.
 *
 * FIELD MAPPING
 *   Four EDIT ME blocks below, in the order you will need them:
 *     TITLE_TO_FIELD          question → lead field (name, email, …)
 *     TITLE_TO_PROPERTY       question → stable raw_data key
 *     TITLE_TO_CUSTOM_FIELD   question → custom-field slug
 *     CUSTOM_FIELD_VALUES     form label → the option value the field
 *                             expects, for select / multi_select
 *   An unmapped question is never lost — it still reaches raw_data under
 *   its literal title. A MIS-mapped custom field is different: a wrong
 *   slug or option value 422s the whole lead, and this script treats 4xx
 *   as permanent, so check those against the site's definitions first.
 *
 * RECOVERY
 *   If leads ever stop arriving, run `backfillAll` from the editor. It
 *   replays every response the form has and cannot create duplicates —
 *   see the note above that function. This is the reason the form's own
 *   response list (and its linked Sheet, if there is one) is worth
 *   keeping: it is the backstop this script recovers from.
 */

// ─────────────────────────────────────────────────────────────────────
// EDIT ME — question titles mapped to InsightHub lead fields.
//
// Titles are matched loosely: case, spaces, hyphens and punctuation are
// all ignored, so one 'E-Mail' entry catches "Email", "e-mail", "E Mail"
// and "E-mail:" alike. Keep the keys written the readable way.
//
// Deliberately keeps variants for forms this script is not installed on
// yet. An earlier version trimmed them to just the questions on the
// first form, and the very next form asked "Email" instead of
// "E-Mail-Adresse" — so the address never reached the lead, and the
// flow's welcome-email node failed on an empty recipient. A spare line
// costs nothing; a missing one costs a lead.
//
// An unmapped question is still never lost: it reaches raw_data under
// its literal title, and that untidy key is how you spot the gap.
//
// Valid targets: name, salutation, title_prefix, title_suffix, company,
// email, phone, message, campaign.
// Deliberately NOT settable from here: status, value, notes,
// last_contact_at — the engine refuses to let external ingestion
// overwrite those, because they are human CRM edits.
// ─────────────────────────────────────────────────────────────────────
const TITLE_TO_FIELD = {
  'Name': 'name',
  'Vor- und Nachname': 'name',
  'Ihr Name': 'name',
  // Prefer these two over `name` when the form asks separately — the
  // salutation builders and DocuSeal templates read the structured
  // columns, and a single `name` string cannot be split reliably.
  // Accepted by POST /v2/leads from engine 2.190.0; before that they
  // were silently dropped.
  'First name': 'first_name',
  'Vorname': 'first_name',
  'Last name': 'last_name',
  'Nachname': 'last_name',
  'E-Mail': 'email',
  'E-Mail-Adresse': 'email',
  'Telefon': 'phone',
  'Telefonnummer': 'phone',
  'Mobil': 'phone',
  'Firma': 'company',
  'Organization': 'company',
  'Nachricht': 'message',
  'Ihre Nachricht': 'message',
};

// Written to `source` on every lead from this form. Deliberately the
// broad bucket the tenant already reports on, so Google Forms leads sit
// alongside the website-form leads instead of splitting the funnel into
// a second near-identical source.
//
// The mechanism stays distinguishable without a separate source value:
// website forms arrive as created_via="form", these as created_via="api".
const LEAD_SOURCE = 'google';

// The finer-grained channel, recorded in raw_data rather than on
// `source`, so a flow can target Google Forms leads specifically while
// reporting still rolls them up under `google`.
//
// Named `platform` deliberately: Facebook Lead Ads leads already arrive
// with raw_data.platform ("fb" / "ig") alongside form_id and
// external_id, so reusing the name means one filter vocabulary across
// every channel instead of a parallel one per integration.
const LEAD_PLATFORM = 'google-forms';

// NOTE: the form's own id is used as raw_data.form_id — see formId_()
// below. There is deliberately nothing to configure here.

// Questions appended to `message`, underneath whatever the customer
// wrote, instead of being mapped to a lead field of their own.
//
// For qualifying answers a rep needs to see at a glance but that have
// no good home on the lead — the loss bracket is a range string
// ("101.000€ - 200.000€"), so it cannot go in the numeric `value`
// field without inventing a figure.
//
// Each is written with its question kept in front of the answer, so the
// number still means something on its own. Same matching rule as
// TITLE_TO_FIELD: lower-cased, trimmed. Answers also stay in raw_data
// verbatim regardless, so nothing here is the only copy.
const APPEND_TO_MESSAGE = [
  'wie hoch ist der verlust?',
];

// Stable property names for answers inside raw_data — only needed for
// questions that have no TITLE_TO_FIELD entry, since anything mapped to
// a lead field reuses that field's name automatically.
//
// Why raw_data is keyed by machine names rather than question titles:
// the title is the only handle an automation filter has on an answer,
// so the day someone rewords "Wie hoch ist der Verlust?" to "Wie hoch
// ist Ihr Verlust?", every filter referencing it stops matching —
// silently, with no error. It also keeps these leads consistent with
// every other channel, which already use name/email/phone/message.
//
// An unmapped question keeps its literal title, so nothing is ever
// dropped and an unmapped answer is obvious on sight.
//
// Same matching rule as TITLE_TO_FIELD: lower-cased, trimmed. Keep the
// values lower_snake_case and collision-free against the keys the
// script writes itself (platform, form_id, external_id, submitted_at).
const TITLE_TO_PROPERTY = {
  'wie hoch ist der verlust?': 'loss_bracket',
};

// ─────────────────────────────────────────────────────────────────────
// EDIT ME — questions that fill InsightHub CUSTOM FIELDS.
//
// Question title → the custom field's SLUG (Site → Settings → Custom
// fields; the slug, not the label). Everything listed here is sent in
// the payload's `custom_fields` object and lands on the lead as a
// first-class field rather than only inside raw_data.
//
// ⚠ Get a slug or an option value wrong and the engine rejects the
// WHOLE LEAD with 422 — not just that field. Unknown slugs are refused
// on purpose ("silent drops would let a UI bug hide data loss"), and
// this script marks any 4xx except 429 as permanent, so the submission
// is never retried. Verify each slug against the site's definitions
// before going live; one typo loses every lead from this form.
// ─────────────────────────────────────────────────────────────────────
const TITLE_TO_CUSTOM_FIELD = {
  // 'Budget':           'budget',
  // 'Do you have cars?': 'do_you_have_cars',
  // 'Type of Car':      'type_of_car',
};

// Option-value translation for `select` / `multi_select` custom fields.
//
// The engine matches option values EXACTLY and case-sensitively, and a
// form shows labels rather than values — so "Mini Van" has to become
// "mini_van" before it is sent. Keyed by slug, then by the answer text
// as the form renders it (matched loosely: case, spaces, hyphens and
// punctuation ignored, same rule as the title maps).
//
// A field with no entry here is sent through unchanged, which is right
// for text and number fields. An answer with no matching entry is sent
// unchanged too — so it fails loudly at the engine rather than being
// quietly dropped here.
//
// Watch for inconsistent casing in the field definitions themselves:
// a select can legitimately have options like ['yes', 'No'].
const CUSTOM_FIELD_VALUES = {
  // type_of_car: { 'Truck': 'truck', 'Mini Van': 'mini_van', 'Sedan': 'sedan', 'Bus': 'bus' },
  // do_you_have_cars: { 'Yes': 'yes', 'No': 'No' },
};

// Slugs whose field is `multi_select` — the engine requires a LIST for
// these and 422s on a string, so a single-answer checkbox still has to
// be sent as a one-element array.
const MULTI_SELECT_CUSTOM_FIELDS = [
  // 'type_of_car',
];

/** Give up retrying after this many attempts. */
const MAX_RETRIES = 8;

/**
 * Reduce a question title to a comparison key: lower-case, with every
 * space, hyphen and punctuation mark removed.
 *
 * So "E-Mail", "Email", "e mail" and "E-Mail:" all collapse to "email"
 * and match one map entry. Exact-string matching is too brittle for
 * titles a non-technical person edits in a web UI — a stray hyphen or
 * trailing colon silently unmaps the question, and the lead arrives
 * missing a field with no error anywhere.
 */
function normTitle_(s) {
  return String(s).toLowerCase().replace(/[^a-z0-9]/g, '');
}

/** Re-key a title map by normTitle_ so lookups are punctuation-proof. */
function normMap_(map) {
  const out = {};
  Object.keys(map).forEach(function (k) {
    out[normTitle_(k)] = map[k];
  });
  return out;
}

const FIELD_BY_TITLE = normMap_(TITLE_TO_FIELD);
const PROPERTY_BY_TITLE = normMap_(TITLE_TO_PROPERTY);
const APPEND_TITLES = APPEND_TO_MESSAGE.map(normTitle_);
const CUSTOM_FIELD_BY_TITLE = normMap_(TITLE_TO_CUSTOM_FIELD);

/** Per-slug option-value lookups, re-keyed by normTitle_ like the rest. */
const CUSTOM_VALUE_BY_SLUG = (function () {
  const out = {};
  Object.keys(CUSTOM_FIELD_VALUES).forEach(function (slug) {
    out[slug] = normMap_(CUSTOM_FIELD_VALUES[slug]);
  });
  return out;
})();

/**
 * Translate one answer into the value the custom field expects.
 *
 * Anything without a mapping passes through untouched: right for text
 * and number fields, and for select fields it means an unmapped option
 * fails loudly at the engine instead of being silently dropped here.
 */
function customFieldValue_(slug, answer) {
  const table = CUSTOM_VALUE_BY_SLUG[slug];
  const one = function (v) {
    const mapped = table ? table[normTitle_(v)] : undefined;
    return mapped === undefined ? v : mapped;
  };
  if (Array.isArray(answer)) {
    return answer.map(one);
  }
  // multi_select needs a list even for a single answer — a bare string
  // is a 422.
  if (MULTI_SELECT_CUSTOM_FIELDS.indexOf(slug) !== -1) {
    return [one(answer)];
  }
  return one(answer);
}

// ─────────────────────────────────────────────────────────────────────

/** Installable trigger: fires once per submission. */
function onFormSubmit(e) {
  const payload = buildPayload_(e.response);
  const result = postLead_(payload);

  if (result.ok) {
    console.log('Lead sent: HTTP ' + result.code + ' ' + result.body);
    return;
  }

  // A 4xx other than 429 will never succeed on retry (bad payload, bad
  // key, missing identity) — record it as permanent so the sweep does not
  // hammer the gateway forever. Everything else is worth retrying.
  const permanent = result.code >= 400 && result.code < 500 && result.code !== 429;
  console.error(
    (permanent ? 'Lead REJECTED' : 'Lead send failed, queued for retry') +
      ': HTTP ' + result.code + ' ' + result.body,
  );
  queueFailure_(payload, result, permanent);
}

/**
 * The Google form's own id, written to raw_data.form_id — the routing
 * key, mirroring the form_id that Facebook Lead Ads leads already carry
 * so "which form was this?" is one filter whatever the channel.
 *
 * Read from the form itself rather than hand-set, so it is unique and
 * stable without anyone maintaining it, and so copying this script to a
 * second form cannot leave both reporting the same id — which would
 * break routing with no error anywhere.
 *
 * Cached per execution: backfillAll would otherwise re-ask Google once
 * per response.
 */
let formIdCache_ = null;
function formId_() {
  if (formIdCache_ === null) {
    try {
      formIdCache_ = FormApp.getActiveForm().getId();
    } catch (err) {
      // Never let this break a send: an unidentified lead still beats a
      // lost one. Empty is visibly wrong in a filter, which is the point.
      console.error('Could not read the form id: ' + err);
      formIdCache_ = '';
    }
  }
  return formIdCache_;
}

/** Build the InsightHub lead payload from a FormResponse. */
function buildPayload_(response) {
  const raw = {};
  const lead = {
    source: LEAD_SOURCE,
    // Stable per submission — the engine's primary dedup key, so a
    // retried POST merges into the same lead instead of duplicating it.
    external_id: response.getId(),
  };

  // Collected in the loop, where the question's original wording is
  // still to hand — the message wants the human title ("Wie hoch ist
  // der Verlust? 30.000€"), not the machine key ("loss_bracket: 30.000€").
  const extras = [];

  // Filled from TITLE_TO_CUSTOM_FIELD; only attached below if non-empty,
  // so a form with no custom-field mappings sends no `custom_fields` key
  // at all rather than an empty object.
  const customFields = {};

  response.getItemResponses().forEach(function (item) {
    const title = item.getItem().getTitle();
    let answer = item.getResponse();

    // Keep the array form before it is flattened — a multi_select custom
    // field needs the list, and "Truck, Mini Van" cannot be split back
    // apart safely once an option value contains a comma.
    const rawAnswer = answer;

    // Checkbox grids and multi-select answers arrive as arrays.
    if (Array.isArray(answer)) {
      answer = answer.join(', ');
    }
    if (answer === null || answer === undefined || answer === '') {
      return;
    }

    const key = normTitle_(title);
    const field = FIELD_BY_TITLE[key];

    const customSlug = CUSTOM_FIELD_BY_TITLE[key];
    if (customSlug) {
      customFields[customSlug] = customFieldValue_(customSlug, rawAnswer);
    }

    // raw_data is keyed by machine name: the explicit TITLE_TO_PROPERTY
    // entry, else the lead field this answer already maps to, else the
    // literal title so an unmapped question is still never dropped.
    raw[PROPERTY_BY_TITLE[key] || field || title] = answer;

    if (field && !lead[field]) {
      lead[field] = String(answer);
    }

    if (APPEND_TITLES.indexOf(key) !== -1) {
      // "Wie hoch ist der Verlust? 30.000€" reads better than "…?: 30.000€".
      extras.push(title + (/\?\s*$/.test(title) ? ' ' : ': ') + answer);
    }
  });

  if (extras.length) {
    lead.message = lead.message
      ? lead.message + '\n\n' + extras.join('\n')
      : extras.join('\n');
  }

  // Google Forms' built-in "Collect email addresses" setting is NOT a
  // question, so it never appears in getItemResponses() — it lives on
  // the response itself. Without this the address is invisible to the
  // script even though the form shows it as a required field, and the
  // lead arrives with no email at all.
  //
  // That is not just a missing contact detail: email is the engine's
  // dedup key after external_id, so without it the same person
  // submitting twice creates two leads instead of merging into one.
  //
  // Runs only as a fallback, so a real "E-Mail" question still wins.
  if (!lead.email) {
    try {
      const respondent = response.getRespondentEmail();
      if (respondent) {
        lead.email = respondent;
        // Under the same key a mapped "E-Mail" question would have used,
        // so a filter on raw_data.email works however it was collected.
        raw['email'] = respondent;
      }
    } catch (err) {
      // Collection disabled, or the form predates it. Nothing to do —
      // the lead still has external_id, so it is never rejected.
      console.log('No respondent email available: ' + err);
    }
  }

  raw['platform'] = LEAD_PLATFORM;
  raw['form_id'] = formId_();
  raw['submitted_at'] = response.getTimestamp().toISOString();
  lead.raw_data = raw;

  if (Object.keys(customFields).length) {
    lead.custom_fields = customFields;
  }

  return lead;
}

/**
 * POST one lead. Never throws — returns {ok, code, body} so the caller
 * decides what to do, which keeps the trigger from dying mid-run and
 * losing the chance to queue a retry.
 */
function postLead_(payload) {
  const props = PropertiesService.getScriptProperties();
  const url = props.getProperty('GATEWAY_URL');
  const key = props.getProperty('API_KEY');

  if (!url || !key) {
    return { ok: false, code: 0, body: 'GATEWAY_URL or API_KEY missing from Script Properties' };
  }

  try {
    const res = UrlFetchApp.fetch(url, {
      method: 'post',
      contentType: 'application/json',
      headers: { 'X-API-Key': key },
      payload: JSON.stringify(payload),
      muteHttpExceptions: true, // inspect the status instead of throwing
    });
    const code = res.getResponseCode();
    // 201 = new lead, 200 = merged into an existing one. Both are success.
    return { ok: code === 200 || code === 201, code: code, body: res.getContentText().slice(0, 500) };
  } catch (err) {
    // Network-level failure (DNS, timeout) — no HTTP status exists.
    return { ok: false, code: 0, body: String(err) };
  }
}

// ── Retry safety net ─────────────────────────────────────────────────
// Apps Script does NOT re-run a failed trigger, so without this a brief
// gateway outage drops the lead silently. The response still sits in the
// form's own results, but nobody would notice it never arrived.

/** Time-driven trigger: re-send anything queued, oldest first. */
function retryFailedSends() {
  const sheet = failureSheet_();
  const rows = sheet.getDataRange().getValues();
  if (rows.length < 2) {
    return;
  }

  // Walk bottom-up so deleting a row cannot shift the ones still to check.
  for (let i = rows.length - 1; i >= 1; i--) {
    const [, payloadJson, attempts, status] = rows[i];
    if (status === 'permanent' || Number(attempts) >= MAX_RETRIES) {
      continue;
    }

    let payload;
    try {
      payload = JSON.parse(payloadJson);
    } catch (err) {
      sheet.getRange(i + 1, 4).setValue('permanent');
      continue;
    }

    const result = postLead_(payload);
    if (result.ok) {
      sheet.deleteRow(i + 1);
      console.log('Retry succeeded for ' + payload.external_id);
    } else {
      const permanent = result.code >= 400 && result.code < 500 && result.code !== 429;
      sheet.getRange(i + 1, 3).setValue(Number(attempts) + 1);
      sheet.getRange(i + 1, 4).setValue(permanent ? 'permanent' : 'pending');
      sheet.getRange(i + 1, 5).setValue('HTTP ' + result.code + ' ' + result.body);
    }
  }
}

function queueFailure_(payload, result, permanent) {
  try {
    failureSheet_().appendRow([
      new Date(),
      JSON.stringify(payload),
      1,
      permanent ? 'permanent' : 'pending',
      'HTTP ' + result.code + ' ' + result.body,
    ]);
  } catch (err) {
    // Last resort: if even the queue write fails the execution log is the
    // only remaining record, so make sure the payload is in it.
    console.error('Could not queue failed send: ' + err + ' payload=' + JSON.stringify(payload));
  }
}

/**
 * The queue lives in its own spreadsheet rather than the form's linked
 * responses sheet — a form may have no linked sheet at all, and mixing
 * an internal queue into the customer-visible responses tab invites
 * someone to "tidy up" and delete it.
 */
function failureSheet_() {
  const props = PropertiesService.getScriptProperties();
  let id = props.getProperty('FAILED_SHEET_ID');

  if (id) {
    try {
      return SpreadsheetApp.openById(id).getSheets()[0];
    } catch (err) {
      id = null; // deleted or inaccessible — fall through and recreate
    }
  }

  const ss = SpreadsheetApp.create('InsightHub — failed lead sends');
  const sheet = ss.getSheets()[0];
  sheet.appendRow(['queued_at', 'payload_json', 'attempts', 'status', 'last_error']);
  sheet.setFrozenRows(1);
  props.setProperty('FAILED_SHEET_ID', ss.getId());
  console.log('Created failure queue: ' + ss.getUrl());

  return sheet;
}

// ── Recovery: replay every response ──────────────────────────────────
// The retry queue above only rescues sends that were actually attempted.
// It cannot help when the trigger never fired at all — someone deleted
// it, the installing account was suspended, an authorisation lapsed.
// That failure is silent: responses keep piling up in the form, nothing
// reaches InsightHub, and there is no error anywhere to notice.
//
// This is the answer to that, and the reason a pull/polling connector
// was not needed: polling is self-healing because it re-reads the whole
// source, and so is this.
//
// Safe to run as often as you like. `external_id` is the response id and
// InsightHub dedups on it before email or phone, so replaying a response
// that already arrived merges into the same lead rather than creating a
// second one. Re-running cannot duplicate anything.

/**
 * Apps Script hard-kills an execution at 6 minutes on consumer accounts
 * (30 on Workspace). Stopping short of that and saving a cursor turns
 * "dies halfway with nothing recorded" into "resume on the next run".
 */
const BACKFILL_BUDGET_MS = 4 * 60 * 1000;

/** Replay every form response through the normal send path. */
function backfillAll() {
  const props = PropertiesService.getScriptProperties();
  const responses = FormApp.getActiveForm().getResponses();
  const started = Date.now();

  // Responses come back oldest-first and new ones are appended, so an
  // index cursor stays valid even if submissions land mid-backfill.
  let i = Number(props.getProperty('BACKFILL_CURSOR') || 0);
  if (i >= responses.length) {
    i = 0; // previous pass finished (or responses were cleared) — start over
  }

  let sent = 0;
  let failed = 0;

  for (; i < responses.length; i++) {
    if (Date.now() - started > BACKFILL_BUDGET_MS) {
      props.setProperty('BACKFILL_CURSOR', String(i));
      console.log(
        'Backfill paused at ' + i + '/' + responses.length +
          ' (time budget) — ' + sent + ' sent, ' + failed +
          ' queued. Run backfillAll again to continue from here.',
      );
      return;
    }

    const payload = buildPayload_(responses[i]);
    const result = postLead_(payload);

    if (result.ok) {
      sent++;
    } else {
      // Same triage as the live trigger: hand it to the retry queue so a
      // transient failure during backfill is not lost either.
      failed++;
      const permanent = result.code >= 400 && result.code < 500 && result.code !== 429;
      queueFailure_(payload, result, permanent);
    }
  }

  props.deleteProperty('BACKFILL_CURSOR');
  console.log(
    'Backfill complete: ' + responses.length + ' responses, ' + sent +
      ' sent, ' + failed + ' queued for retry.',
  );
}