/**
 * Intelligex GPT for Google Sheets
 * Version: 1.1.0
 *
 * Two modes:
 * 1. The user supplies their own OpenAI API key.
 * 2. The user verifies an email address and uses 100 Intelligex-funded calls.
 *
 * IMPORTANT:
 * The Intelligex server endpoints must be installed before the free-credit mode works.
 */

const INTELLIGEX_GPT_CONFIG = Object.freeze({
  CLIENT_VERSION: "1.1.0",

  // Change this only if your WordPress REST namespace is different.
  INTELLIGEX_API_BASE:
    "https://intelligex.ai/wp-json/intelligex-gpt/v1",

  OPENAI_RESPONSES_URL:
    "https://api.openai.com/v1/responses",

  DEFAULT_DIRECT_MODEL: "gpt-5-nano",
  DEFAULT_MAX_OUTPUT_TOKENS: 1000,
  MAX_DIRECT_OUTPUT_TOKENS: 8000,

  // Google Apps Script cache duration: six hours.
  CACHE_SECONDS: 21600,

  MODES: Object.freeze({
    OWN_KEY: "own_api_key",
    INTELLIGEX: "intelligex"
  }),

  PROPERTIES: Object.freeze({
    MODE: "INTELLIGEX_GPT_MODE",
    OPENAI_API_KEY: "INTELLIGEX_GPT_OPENAI_API_KEY",
    ACCESS_TOKEN: "INTELLIGEX_GPT_ACCESS_TOKEN",
    FIRST_NAME: "INTELLIGEX_GPT_FIRST_NAME",
    LAST_NAME: "INTELLIGEX_GPT_LAST_NAME",
    EMAIL: "INTELLIGEX_GPT_EMAIL"
  })
});


/* -------------------------------------------------------------------------- */
/* Spreadsheet menu and setup                                                 */
/* -------------------------------------------------------------------------- */

/**
 * Adds the Intelligex GPT menu whenever the spreadsheet opens.
 */
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu("Intelligex GPT")
    .addItem("Set up or change mode", "setupIntelligexGPT")
    .addSeparator()
    .addItem("Use my OpenAI API key", "setOwnOpenAIKey")
    .addItem("Register for 100 free calls", "registerForIntelligexFreeCalls")
    .addItem("Verify email code", "verifyIntelligexEmail")
    .addSeparator()
    .addItem("Switch to my API key", "switchToOwnKeyMode")
    .addItem("Switch to Intelligex credits", "switchToIntelligexMode")
    .addSeparator()
    .addItem("Check account and usage", "showIntelligexGPTStatus")
    .addItem("Clear all GPT settings", "clearIntelligexGPTSettings")
    .addToUi();
}


/**
 * Included for future use if this script is converted into a Sheets add-on.
 */
function onInstall(e) {
  onOpen(e);
}


/**
 * Lets the user choose a mode.
 *
 * YES: use the user's own OpenAI key.
 * NO: register or verify for Intelligex free calls.
 */
function setupIntelligexGPT() {
  const ui = SpreadsheetApp.getUi();

  const answer = ui.alert(
    "Set up Intelligex GPT",
    "Choose how GPT should work in this spreadsheet.\n\n" +
      "YES — Use your own OpenAI API key.\n" +
      "NO — Use up to 100 free calls provided by Intelligex.\n" +
      "CANCEL — Make no changes.",
    ui.ButtonSet.YES_NO_CANCEL
  );

  if (answer === ui.Button.YES) {
    setOwnOpenAIKey();
    return;
  }

  if (answer === ui.Button.NO) {
    const properties = getUserProperties_();
    const token = properties.getProperty(
      INTELLIGEX_GPT_CONFIG.PROPERTIES.ACCESS_TOKEN
    );

    if (token) {
      properties.setProperty(
        INTELLIGEX_GPT_CONFIG.PROPERTIES.MODE,
        INTELLIGEX_GPT_CONFIG.MODES.INTELLIGEX
      );

      ui.alert(
        "Intelligex credits enabled",
        "Your existing verified Intelligex access token is now active.",
        ui.ButtonSet.OK
      );
      return;
    }

    registerForIntelligexFreeCalls();
  }
}


/**
 * Collects and stores the user's personal OpenAI API key.
 *
 * UserProperties are used so credentials belong to the current Google user,
 * not to every editor of the spreadsheet.
 */
function setOwnOpenAIKey() {
  const ui = SpreadsheetApp.getUi();

  const response = ui.prompt(
    "Use your OpenAI API key",
    "Paste your OpenAI API key. It will be stored in your personal Apps Script User Properties.",
    ui.ButtonSet.OK_CANCEL
  );

  if (response.getSelectedButton() !== ui.Button.OK) {
    return;
  }

  const apiKey = response.getResponseText().trim();

  if (!isPlausibleOpenAIKey_(apiKey)) {
    ui.alert(
      "Invalid API key",
      "Enter a valid OpenAI API key beginning with “sk-”.",
      ui.ButtonSet.OK
    );
    return;
  }

  const properties = getUserProperties_();

  properties.setProperty(
    INTELLIGEX_GPT_CONFIG.PROPERTIES.OPENAI_API_KEY,
    apiKey
  );

  properties.setProperty(
    INTELLIGEX_GPT_CONFIG.PROPERTIES.MODE,
    INTELLIGEX_GPT_CONFIG.MODES.OWN_KEY
  );

  ui.alert(
    "OpenAI key saved",
    "Your own-key mode is active.\n\n" +
      'You can now use formulas such as =GPT("Write a short summary").',
    ui.ButtonSet.OK
  );
}


/**
 * Collects registration details and asks the Intelligex server to email a
 * verification code.
 */
function registerForIntelligexFreeCalls() {
  const ui = SpreadsheetApp.getUi();

  const consent = ui.alert(
    "Intelligex free calls",
    "Intelligex provides up to 100 free GPT calls after email verification.\n\n" +
      "In this mode, your prompts are sent to the Intelligex server and then " +
      "to the AI provider. Do not submit passwords, confidential data, " +
      "regulated personal information, or other sensitive content.\n\n" +
      "Continue with registration?",
    ui.ButtonSet.YES_NO
  );

  if (consent !== ui.Button.YES) {
    return;
  }

  const firstName = promptRequiredText_(
    "First name",
    "Enter your first name."
  );
  if (firstName === null) return;

  const lastName = promptRequiredText_(
    "Last name",
    "Enter your last name."
  );
  if (lastName === null) return;

  const email = promptRequiredText_(
    "Email address",
    "Enter the email address that should receive your verification code."
  );
  if (email === null) return;

  const normalizedEmail = email.trim().toLowerCase();

  if (!isValidEmail_(normalizedEmail)) {
    ui.alert(
      "Invalid email",
      "Enter a valid email address.",
      ui.ButtonSet.OK
    );
    return;
  }

  try {
    const result = callIntelligexPublicEndpoint_(
      "/register",
      {
        first_name: firstName.trim(),
        last_name: lastName.trim(),
        email: normalizedEmail,
        terms_accepted: true,
        source: "google_sheets",
        client_version: INTELLIGEX_GPT_CONFIG.CLIENT_VERSION
      }
    );

    const properties = getUserProperties_();

    properties.setProperties({
      [INTELLIGEX_GPT_CONFIG.PROPERTIES.FIRST_NAME]:
        firstName.trim(),
      [INTELLIGEX_GPT_CONFIG.PROPERTIES.LAST_NAME]:
        lastName.trim(),
      [INTELLIGEX_GPT_CONFIG.PROPERTIES.EMAIL]:
        normalizedEmail
    });

    ui.alert(
      "Verification code sent",
      result.message ||
        "Check your email for the verification code. Then select " +
        "Intelligex GPT → Verify email code.",
      ui.ButtonSet.OK
    );
  } catch (error) {
    ui.alert(
      "Registration failed",
      friendlyErrorMessage_(error),
      ui.ButtonSet.OK
    );
  }
}


/**
 * Verifies the emailed code and stores the server-issued access token.
 */
function verifyIntelligexEmail() {
  const ui = SpreadsheetApp.getUi();
  const properties = getUserProperties_();

  let email = (
    properties.getProperty(
      INTELLIGEX_GPT_CONFIG.PROPERTIES.EMAIL
    ) || ""
  ).trim().toLowerCase();

  if (!email) {
    email = promptRequiredText_(
      "Email address",
      "Enter the email address used during registration."
    );

    if (email === null) return;

    email = email.trim().toLowerCase();
  }

  if (!isValidEmail_(email)) {
    ui.alert(
      "Invalid email",
      "Enter a valid email address.",
      ui.ButtonSet.OK
    );
    return;
  }

  const code = promptRequiredText_(
    "Verification code",
    "Enter the verification code sent to " + email + "."
  );

  if (code === null) return;

  if (!/^\d{6}$/.test(code.trim())) {
    ui.alert(
      "Invalid code",
      "The verification code must contain six digits.",
      ui.ButtonSet.OK
    );
    return;
  }

  try {
    const result = callIntelligexPublicEndpoint_(
      "/verify",
      {
        email: email,
        code: code.trim(),
        source: "google_sheets",
        client_version: INTELLIGEX_GPT_CONFIG.CLIENT_VERSION
      }
    );

    if (!result.access_token) {
      throw new Error(
        "The Intelligex server did not return an access token."
      );
    }

    properties.setProperties({
      [INTELLIGEX_GPT_CONFIG.PROPERTIES.EMAIL]: email,
      [INTELLIGEX_GPT_CONFIG.PROPERTIES.ACCESS_TOKEN]:
        result.access_token,
      [INTELLIGEX_GPT_CONFIG.PROPERTIES.MODE]:
        INTELLIGEX_GPT_CONFIG.MODES.INTELLIGEX
    });

    ui.alert(
      "Email verified",
      "Intelligex free-credit mode is active.\n\n" +
        "Calls available: " +
        String(
          typeof result.calls_remaining === "number"
            ? result.calls_remaining
            : 100
        ) +
        '\n\nYou can now use formulas such as =GPT("Write a summary").',
      ui.ButtonSet.OK
    );
  } catch (error) {
    ui.alert(
      "Verification failed",
      friendlyErrorMessage_(error),
      ui.ButtonSet.OK
    );
  }
}


/**
 * Switches to the user's OpenAI API key without deleting the Intelligex token.
 */
function switchToOwnKeyMode() {
  const ui = SpreadsheetApp.getUi();
  const properties = getUserProperties_();

  const apiKey = properties.getProperty(
    INTELLIGEX_GPT_CONFIG.PROPERTIES.OPENAI_API_KEY
  );

  if (!apiKey) {
    setOwnOpenAIKey();
    return;
  }

  properties.setProperty(
    INTELLIGEX_GPT_CONFIG.PROPERTIES.MODE,
    INTELLIGEX_GPT_CONFIG.MODES.OWN_KEY
  );

  ui.alert(
    "Mode changed",
    "GPT will now use your own OpenAI API key.",
    ui.ButtonSet.OK
  );
}


/**
 * Switches to the previously verified Intelligex access token.
 */
function switchToIntelligexMode() {
  const ui = SpreadsheetApp.getUi();
  const properties = getUserProperties_();

  const token = properties.getProperty(
    INTELLIGEX_GPT_CONFIG.PROPERTIES.ACCESS_TOKEN
  );

  if (!token) {
    ui.alert(
      "Verification required",
      "Register and verify your email before using Intelligex free credits.",
      ui.ButtonSet.OK
    );
    registerForIntelligexFreeCalls();
    return;
  }

  properties.setProperty(
    INTELLIGEX_GPT_CONFIG.PROPERTIES.MODE,
    INTELLIGEX_GPT_CONFIG.MODES.INTELLIGEX
  );

  ui.alert(
    "Mode changed",
    "GPT will now use your Intelligex free-call balance.",
    ui.ButtonSet.OK
  );
}


/**
 * Displays the current mode and, for Intelligex mode, retrieves live usage.
 */
function showIntelligexGPTStatus() {
  const ui = SpreadsheetApp.getUi();
  const properties = getUserProperties_();
  const mode = getConfiguredMode_();

  if (!mode) {
    ui.alert(
      "Not configured",
      "Select Intelligex GPT → Set up or change mode.",
      ui.ButtonSet.OK
    );
    return;
  }

  if (mode === INTELLIGEX_GPT_CONFIG.MODES.OWN_KEY) {
    const hasKey = Boolean(
      properties.getProperty(
        INTELLIGEX_GPT_CONFIG.PROPERTIES.OPENAI_API_KEY
      )
    );

    ui.alert(
      "GPT status",
      "Mode: Your OpenAI API key\n" +
        "API key stored: " +
        (hasKey ? "Yes" : "No") +
        "\nBilling and limits: Managed in your OpenAI account",
      ui.ButtonSet.OK
    );
    return;
  }

  try {
    const status = getIntelligexStatus_();

    ui.alert(
      "Intelligex GPT status",
      formatIntelligexStatus_(status),
      ui.ButtonSet.OK
    );
  } catch (error) {
    ui.alert(
      "Could not retrieve status",
      friendlyErrorMessage_(error),
      ui.ButtonSet.OK
    );
  }
}


/**
 * Deletes credentials and registration details stored for the current user.
 */
function clearIntelligexGPTSettings() {
  const ui = SpreadsheetApp.getUi();

  const answer = ui.alert(
    "Clear all GPT settings?",
    "This removes your stored OpenAI key, Intelligex access token, " +
      "email, name, and selected mode from this Apps Script project.",
    ui.ButtonSet.YES_NO
  );

  if (answer !== ui.Button.YES) {
    return;
  }

  getUserProperties_().deleteAllProperties();
  ui.alert(
    "Settings cleared",
    "Your locally stored GPT settings have been removed.",
    ui.ButtonSet.OK
  );
}


/* -------------------------------------------------------------------------- */
/* Google Sheets custom functions                                             */
/* -------------------------------------------------------------------------- */

/**
 * Sends a prompt to GPT.
 *
 * Examples:
 * =GPT("Explain workflow automation in one sentence")
 * =GPT(A2)
 * =GPT(A2, "Rewrite professionally. Return only the rewritten text.")
 * =GPT(A2, "Return valid HTML only.", "gpt-5-nano", 2000)
 * =GPT(A2, "", "", 1000, $Z$1)
 *
 * In own-key mode, the model argument selects the OpenAI model.
 * In Intelligex mode, the server controls the model and may ignore this value.
 *
 * Change the refresh argument to force a new request instead of reusing a
 * cached or deduplicated response.
 *
 * @param {string|Array<Array<*>>} prompt Prompt, cell, or range.
 * @param {string} instructions Optional instructions.
 * @param {string} model Optional model for own-key mode.
 * @param {number} maxOutputTokens Optional maximum output tokens.
 * @param {*} refresh Optional value used to force a new request.
 * @return {string} Generated text or an error message.
 * @customfunction
 */
function GPT(
  prompt,
  instructions,
  model,
  maxOutputTokens,
  refresh
) {
  try {
    const promptText = normalizeInput_(prompt).trim();

    if (!promptText) {
      return "";
    }

    const instructionText =
      normalizeInput_(instructions).trim() ||
      "Provide a clear and accurate answer. Return only the requested output without introductory commentary.";

    const selectedModel =
      normalizeInput_(model).trim() ||
      INTELLIGEX_GPT_CONFIG.DEFAULT_DIRECT_MODEL;

    const tokenLimit = normalizeTokenLimit_(maxOutputTokens);
    const refreshValue = normalizeInput_(refresh);
    const mode = getConfiguredMode_();

    if (!mode) {
      throw new Error(
        "GPT is not configured. Reload the spreadsheet and select " +
          "Intelligex GPT → Set up or change mode."
      );
    }

    const requestData = {
      prompt: promptText,
      instructions: instructionText,
      model:
        mode === INTELLIGEX_GPT_CONFIG.MODES.OWN_KEY
          ? selectedModel
          : "server-controlled",
      max_output_tokens: tokenLimit,
      refresh: refreshValue,
      mode: mode
    };

    const requestId = createRequestId_(requestData);
    const cacheKey = "IXGPT_" + requestId;
    const cache = CacheService.getUserCache();
    const cached = cache.get(cacheKey);

    if (cached !== null) {
      return cached;
    }

    let output;

    if (mode === INTELLIGEX_GPT_CONFIG.MODES.OWN_KEY) {
      output = callOpenAIDirectly_(
        promptText,
        instructionText,
        selectedModel,
        tokenLimit
      );
    } else if (
      mode === INTELLIGEX_GPT_CONFIG.MODES.INTELLIGEX
    ) {
      output = callIntelligexGenerate_(
        promptText,
        instructionText,
        tokenLimit,
        requestId
      );
    } else {
      throw new Error("The stored GPT mode is invalid.");
    }

    if (output.length < 90000) {
      cache.put(
        cacheKey,
        output,
        INTELLIGEX_GPT_CONFIG.CACHE_SECONDS
      );
    }

    return output;
  } catch (error) {
    return "GPT error: " + friendlyErrorMessage_(error);
  }
}


/**
 * Returns account information as a spillable table.
 *
 * Example:
 * =GPT_USAGE()
 *
 * @return {Array<Array<*>>} Current mode and usage details.
 * @customfunction
 */
function GPT_USAGE() {
  try {
    const mode = getConfiguredMode_();

    if (!mode) {
      return [
        ["Setting", "Value"],
        ["Status", "Not configured"],
        [
          "Action",
          "Use Intelligex GPT → Set up or change mode"
        ]
      ];
    }

    if (mode === INTELLIGEX_GPT_CONFIG.MODES.OWN_KEY) {
      return [
        ["Setting", "Value"],
        ["Mode", "Your OpenAI API key"],
        ["Intelligex call limit", "Not applicable"],
        ["Billing", "Your OpenAI account"]
      ];
    }

    const status = getIntelligexStatus_();

    return [
      ["Setting", "Value"],
      ["Mode", "Intelligex free credits"],
      ["Email", status.email || ""],
      ["Account status", status.status || ""],
      ["Calls used", numberOrBlank_(status.calls_used)],
      ["Calls remaining", numberOrBlank_(status.calls_remaining)],
      ["Calls limit", numberOrBlank_(status.calls_limit)]
    ];
  } catch (error) {
    return [
      ["Setting", "Value"],
      ["Error", friendlyErrorMessage_(error)]
    ];
  }
}


/* -------------------------------------------------------------------------- */
/* OpenAI direct mode                                                         */
/* -------------------------------------------------------------------------- */

/**
 * Calls the OpenAI Responses API with the user's own key.
 */
function callOpenAIDirectly_(
  prompt,
  instructions,
  model,
  maxOutputTokens
) {
  const apiKey = getUserProperties_().getProperty(
    INTELLIGEX_GPT_CONFIG.PROPERTIES.OPENAI_API_KEY
  );

  if (!apiKey) {
    throw new Error(
      "Your OpenAI API key is missing. Select " +
        "Intelligex GPT → Use my OpenAI API key."
    );
  }

  const payload = {
    model: model,
    instructions: instructions,
    input: prompt,
    max_output_tokens: maxOutputTokens,
    store: false
  };

  // Use low reasoning effort for GPT-5-family spreadsheet requests.
  if (/^gpt-5/i.test(model)) {
    payload.reasoning = {
      effort: "low"
    };
  }

  const result = requestJson_(
    INTELLIGEX_GPT_CONFIG.OPENAI_RESPONSES_URL,
    {
      method: "post",
      headers: {
        Authorization: "Bearer " + apiKey
      },
      payload: payload
    }
  );

  const output = extractOpenAIOutputText_(result);

  if (!output) {
    if (result.status === "incomplete") {
      throw new Error(
        "OpenAI returned an incomplete response: " +
          JSON.stringify(result.incomplete_details || {})
      );
    }

    throw new Error("OpenAI returned no text output.");
  }

  return output;
}


/**
 * Extracts generated text from a raw Responses API response.
 */
function extractOpenAIOutputText_(responseData) {
  if (
    typeof responseData.output_text === "string" &&
    responseData.output_text.trim()
  ) {
    return responseData.output_text.trim();
  }

  const textParts = [];
  const outputItems = Array.isArray(responseData.output)
    ? responseData.output
    : [];

  outputItems.forEach(function(item) {
    if (!item || !Array.isArray(item.content)) {
      return;
    }

    item.content.forEach(function(contentItem) {
      if (
        contentItem &&
        contentItem.type === "output_text" &&
        typeof contentItem.text === "string"
      ) {
        textParts.push(contentItem.text);
      }

      if (
        contentItem &&
        contentItem.type === "refusal" &&
        typeof contentItem.refusal === "string"
      ) {
        textParts.push(contentItem.refusal);
      }
    });
  });

  return textParts.join("\n").trim();
}


/* -------------------------------------------------------------------------- */
/* Intelligex free-credit mode                                                */
/* -------------------------------------------------------------------------- */

/**
 * Calls the private Intelligex GPT proxy.
 *
 * The Intelligex OpenAI key must exist only on the server.
 */
function callIntelligexGenerate_(
  prompt,
  instructions,
  maxOutputTokens,
  requestId
) {
  const accessToken = getUserProperties_().getProperty(
    INTELLIGEX_GPT_CONFIG.PROPERTIES.ACCESS_TOKEN
  );

  if (!accessToken) {
    throw new Error(
      "Your Intelligex access token is missing. Register and verify " +
        "your email from the Intelligex GPT menu."
    );
  }

  const result = requestJson_(
    INTELLIGEX_GPT_CONFIG.INTELLIGEX_API_BASE + "/generate",
    {
      method: "post",
      headers: {
        Authorization: "Bearer " + accessToken,
        "X-Intelligex-Client": "google-sheets",
        "X-Intelligex-Client-Version":
          INTELLIGEX_GPT_CONFIG.CLIENT_VERSION
      },
      payload: {
        prompt: prompt,
        instructions: instructions,
        max_output_tokens: maxOutputTokens,
        request_id: requestId,
        source: "google_sheets",
        client_version:
          INTELLIGEX_GPT_CONFIG.CLIENT_VERSION
      }
    }
  );

  if (
    typeof result.output !== "string" ||
    !result.output.trim()
  ) {
    throw new Error(
      "The Intelligex server returned no text output."
    );
  }

  return result.output.trim();
}


/**
 * Retrieves current Intelligex account status without consuming a GPT call.
 */
function getIntelligexStatus_() {
  const accessToken = getUserProperties_().getProperty(
    INTELLIGEX_GPT_CONFIG.PROPERTIES.ACCESS_TOKEN
  );

  if (!accessToken) {
    throw new Error(
      "No Intelligex access token is stored."
    );
  }

  return requestJson_(
    INTELLIGEX_GPT_CONFIG.INTELLIGEX_API_BASE + "/status",
    {
      method: "get",
      headers: {
        Authorization: "Bearer " + accessToken,
        "X-Intelligex-Client": "google-sheets",
        "X-Intelligex-Client-Version":
          INTELLIGEX_GPT_CONFIG.CLIENT_VERSION
      }
    }
  );
}


/**
 * Calls an unauthenticated registration or verification endpoint.
 */
function callIntelligexPublicEndpoint_(path, payload) {
  return requestJson_(
    INTELLIGEX_GPT_CONFIG.INTELLIGEX_API_BASE + path,
    {
      method: "post",
      headers: {
        "X-Intelligex-Client": "google-sheets",
        "X-Intelligex-Client-Version":
          INTELLIGEX_GPT_CONFIG.CLIENT_VERSION
      },
      payload: payload
    }
  );
}


/* -------------------------------------------------------------------------- */
/* HTTP, storage, validation, and formatting helpers                          */
/* -------------------------------------------------------------------------- */

/**
 * Performs an HTTP request and parses a JSON response.
 */
function requestJson_(url, options) {
  const fetchOptions = {
    method: String(options.method || "get").toLowerCase(),
    muteHttpExceptions: true,
    followRedirects: true,
    headers: Object.assign(
      {
        Accept: "application/json"
      },
      options.headers || {}
    )
  };

  if (
    typeof options.payload !== "undefined" &&
    options.payload !== null
  ) {
    fetchOptions.contentType = "application/json";
    fetchOptions.payload = JSON.stringify(options.payload);
  }

  let response;

  try {
    response = UrlFetchApp.fetch(url, fetchOptions);
  } catch (error) {
    throw new Error(
      "The server could not be reached. " +
        friendlyErrorMessage_(error)
    );
  }

  const statusCode = response.getResponseCode();
  const responseText = response.getContentText();
  let data = {};

  if (responseText) {
    try {
      data = JSON.parse(responseText);
    } catch (error) {
      throw new Error(
        "The server returned invalid JSON. HTTP " +
          statusCode +
          "."
      );
    }
  }

  if (statusCode < 200 || statusCode >= 300) {
    const message =
      data &&
      data.error &&
      typeof data.error.message === "string"
        ? data.error.message
        : data && typeof data.message === "string"
          ? data.message
          : "Request failed.";

    if (statusCode === 401 || statusCode === 403) {
      throw new Error(
        message +
          " Reverify your account or update your API key."
      );
    }

    if (statusCode === 402) {
      throw new Error(
        message || "Your free-call balance has been used."
      );
    }

    if (statusCode === 429) {
      throw new Error(
        message ||
          "Too many requests. Try again after the server rate limit resets."
      );
    }

    throw new Error(
      message + " HTTP " + statusCode + "."
    );
  }

  if (data && data.success === false) {
    throw new Error(
      data.message || "The request was not successful."
    );
  }

  return data;
}


/**
 * Returns properties private to the current Google user.
 */
function getUserProperties_() {
  return PropertiesService.getUserProperties();
}


/**
 * Returns the selected mode, or an empty string when not configured.
 */
function getConfiguredMode_() {
  return (
    getUserProperties_().getProperty(
      INTELLIGEX_GPT_CONFIG.PROPERTIES.MODE
    ) || ""
  );
}


/**
 * Prompts for required text and returns null when cancelled.
 */
function promptRequiredText_(title, message) {
  const ui = SpreadsheetApp.getUi();

  const response = ui.prompt(
    title,
    message,
    ui.ButtonSet.OK_CANCEL
  );

  if (response.getSelectedButton() !== ui.Button.OK) {
    return null;
  }

  const value = response.getResponseText().trim();

  if (!value) {
    ui.alert(
      "Required field",
      "This field cannot be empty.",
      ui.ButtonSet.OK
    );
    return null;
  }

  return value;
}


/**
 * Converts a cell, scalar value, or two-dimensional range into text.
 */
function normalizeInput_(value) {
  if (value === null || typeof value === "undefined") {
    return "";
  }

  if (Array.isArray(value)) {
    return value
      .map(function(row) {
        if (!Array.isArray(row)) {
          return normalizeInput_(row);
        }

        return row
          .map(function(cell) {
            return normalizeInput_(cell);
          })
          .join("\t");
      })
      .join("\n");
  }

  if (value instanceof Date) {
    return value.toISOString();
  }

  return String(value);
}


/**
 * Normalizes and caps the output token limit.
 */
function normalizeTokenLimit_(value) {
  const parsed = Number(value);

  if (!Number.isFinite(parsed) || parsed <= 0) {
    return INTELLIGEX_GPT_CONFIG.DEFAULT_MAX_OUTPUT_TOKENS;
  }

  return Math.max(
    16,
    Math.min(
      Math.floor(parsed),
      INTELLIGEX_GPT_CONFIG.MAX_DIRECT_OUTPUT_TOKENS
    )
  );
}


/**
 * Creates a deterministic request ID.
 *
 * Identical requests use the same ID, allowing the Intelligex server to avoid
 * charging twice for spreadsheet recalculation or network retries.
 * Change the GPT refresh argument when a genuinely new answer is required.
 */
function createRequestId_(requestData) {
  const digest = Utilities.computeDigest(
    Utilities.DigestAlgorithm.SHA_256,
    JSON.stringify(requestData),
    Utilities.Charset.UTF_8
  );

  return Utilities.base64EncodeWebSafe(digest)
    .replace(/=+$/g, "");
}


/**
 * Basic API-key shape validation. The OpenAI server performs final validation.
 */
function isPlausibleOpenAIKey_(value) {
  return /^sk-[A-Za-z0-9_-]{20,}$/.test(value);
}


/**
 * Basic email validation. The server must also validate and normalize it.
 */
function isValidEmail_(value) {
  return /^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(value);
}


/**
 * Converts unknown errors into safe user-facing text.
 */
function friendlyErrorMessage_(error) {
  if (!error) {
    return "An unknown error occurred.";
  }

  if (typeof error === "string") {
    return error;
  }

  if (
    typeof error.message === "string" &&
    error.message.trim()
  ) {
    return error.message.trim();
  }

  return String(error);
}


/**
 * Formats the status response for the spreadsheet menu.
 */
function formatIntelligexStatus_(status) {
  return [
    "Mode: Intelligex free credits",
    "Email: " + String(status.email || ""),
    "Status: " + String(status.status || ""),
    "Calls used: " + String(numberOrBlank_(status.calls_used)),
    "Calls remaining: " +
      String(numberOrBlank_(status.calls_remaining)),
    "Calls limit: " + String(numberOrBlank_(status.calls_limit))
  ].join("\n");
}


/**
 * Preserves zero while returning an empty string for missing values.
 */
function numberOrBlank_(value) {
  return typeof value === "number" ? value : "";
}