// Jsonpage for Excel on the web (Microsoft 365): Office Script.
//
// Install: Automate > New Script, paste this file, paste your API key on the API_KEY line, Save.
// The script reads the table named Clients (column "Entreprise": company name, SIREN, SIRET,
// VAT number or identifier such as FR-552032534) and writes or refreshes the columns
// "Official name", "SIREN", "Status", "VAT number", "VAT status" and "Address".
// It can also run from Power Automate (Run script) on a schedule or when a file changes.
// Each row counts as two requests of your plan (search, then company record).

const API_KEY = "PASTE_YOUR_JSONPAGE_API_KEY";
const BASE = "https://api.jsonpage.com/v2";
const TABLE = "Clients";
const INPUT_COLUMN = "Entreprise";
const OUTPUT_COLUMNS = ["Official name", "SIREN", "Status", "VAT number", "VAT status", "Address"];

async function get(path: string): Promise<any> {
  const response = await fetch(BASE + path, { headers: { "X-API-Key": API_KEY } });
  if (response.status === 404) {
    return { data: null };
  }
  if (!response.ok) {
    throw new Error("Jsonpage answered " + response.status + " for " + path.split("?")[0]);
  }
  return response.json();
}

function toIdentifier(value: string): string | null {
  const trimmed = value.trim();
  if (/^[A-Za-z]{2}-[0-9A-Za-z]+$/.test(trimmed)) {
    return trimmed.toUpperCase();
  }
  const compact = trimmed.toUpperCase().replace(/[\s.\-]/g, "");
  const vat = /^FR[0-9A-Z]{2}([0-9]{9})$/.exec(compact);
  if (vat) {
    return "FR-" + vat[1];
  }
  if (/^[0-9]{14}$/.test(compact)) {
    return "FR-" + compact.slice(0, 9);
  }
  if (/^[0-9]{9}$/.test(compact)) {
    return "FR-" + compact;
  }
  return null;
}

async function resolve(value: string): Promise<string | null> {
  const known = toIdentifier(value);
  if (known) {
    return known;
  }
  if (value.trim().length < 3) {
    return null;
  }
  const hits = await get("/autocomplete?limit=1&q=" + encodeURIComponent(value.trim()));
  return hits.data && hits.data.length > 0 ? hits.data[0].id : null;
}

async function main(workbook: ExcelScript.Workbook) {
  const table = workbook.getTable(TABLE);
  if (!table) {
    throw new Error("Table " + TABLE + " not found.");
  }
  const names = table.getHeaderRowRange().getValues()[0].map((v) => String(v));
  for (const name of OUTPUT_COLUMNS) {
    if (names.indexOf(name) < 0) {
      table.addColumn(-1, undefined, name);
    }
  }
  const input = table.getColumnByName(INPUT_COLUMN);
  if (!input) {
    throw new Error("Column " + INPUT_COLUMN + " not found.");
  }
  const queries = input.getRangeBetweenHeaderAndTotal().getValues().map((row) => String(row[0] ?? ""));
  const results: (string | number | boolean)[][] = [];
  for (const query of queries) {
    const id = query ? await resolve(query) : null;
    const company = id ? (await get("/companies/" + encodeURIComponent(id) + "?include=address,vat")).data : null;
    if (!query) {
      results.push(["", "", "", "", "", ""]);
    } else if (!company) {
      results.push(["", "", "NOT_FOUND", "", "", ""]);
    } else {
      const vat = company.vat || {};
      const address = company.address || {};
      results.push([company.name || "", company.registry_id || "", company.status || "", vat.number || "", vat.status || "", address.formatted || ""]);
    }
  }
  // The output columns are written together, in the order of OUTPUT_COLUMNS.
  OUTPUT_COLUMNS.forEach((name, index) => {
    const column = table.getColumnByName(name);
    if (column) {
      column.getRangeBetweenHeaderAndTotal().setValues(results.map((r) => [r[index]]));
    }
  });
}