#!/usr/bin/env node
/**
 * ObsiGate — JSDOM integration tests for the Excel viewer (ROADMAP #153 P0).
 *
 * Loads the real viewer.js module and drives renderXlsxViewer():
 *  - A1 : `xlsx_lossy_features` renders a warning banner; a save on such a
 *         workbook gets 409 `xlsx_lossy_content`, asks for confirmation and
 *         retries with `force: true` (or gives up when refused);
 *  - A4 : the f(x) toggle flips `allow_formula` in the save payload.
 *  - A8 : a sheet bigger than the render caps shows the truncation notice.
 *  - A9bis : the tail of a truncated sheet is fetched window by window from
 *         GET …/xlsx/sheet (scroll sentinel + click), and the appended rows are
 *         editable like the initial ones.
 *  - A7 : formula bar mirrors the active cell; arrows/Tab navigate; editing
 *         from the bar marks the cell dirty; Escape reverts.
 *  - A13 : header click sorts the rendered rows, the filter hides rows, the
 *         find highlights matches, CSV export downloads the visible sheet.
 *  - A14 : the structure menu sends one PUT …/xlsx/structure with the action,
 *         then re-renders from the server; destructive actions confirm first.
 *
 * Usage: node tests/frontend/xlsx-viewer.test.mjs
 */

import { strict as assert } from "node:assert";
import { JSDOM } from "jsdom";
import { fileURLToPath, pathToFileURL } from "node:url";
import { readFileSync } from "node:fs";
import path from "node:path";

const __filename = fileURLToPath(import.meta.url);
const __dirname = path.dirname(__filename);
const REPO_ROOT = path.resolve(__dirname, "..", "..");

// ── JSDOM bootstrap ─────────────────────────────────────────────────────────
const dom = new JSDOM(
  `<!DOCTYPE html>
<html>
<body>
  <div id="content-area"></div>
</body>
</html>`,
  { url: "http://localhost/", pretendToBeVisual: true }
);

const w = dom.window;
globalThis.window = w;
globalThis.document = w.document;
globalThis.DOMParser = w.DOMParser;
globalThis.HTMLElement = w.HTMLElement;
globalThis.Element = w.Element;
globalThis.Node = w.Node;
globalThis.Event = w.Event;
globalThis.CustomEvent = w.CustomEvent;
globalThis.MouseEvent = w.MouseEvent;
globalThis.localStorage = w.localStorage;
globalThis.sessionStorage = w.sessionStorage;
globalThis.requestAnimationFrame = (cb) => setTimeout(() => cb(Date.now()), 0);
Object.defineProperty(globalThis, "navigator", {
  value: w.navigator,
  configurable: true,
  writable: true,
});

// ── fetch / confirm doubles ─────────────────────────────────────────────────
let calls = [];
let nextResponse = () => ({ ok: true, status: 200, body: { status: "ok" } });
let confirmAnswer = true;
let confirmCalls = 0;
// Every confirm() prompt is captured so the tests can assert on its text.
let confirmPrompts = [];

const FR = JSON.parse(
  readFileSync(path.join(REPO_ROOT, "frontend", "locales", "fr.json"), "utf8")
);

globalThis.fetch = async (url, opts = {}) => {
  // The i18n bootstrap fetches the locale files: serve the real FR one so the
  // assertions run on the shipped strings, not on raw keys.
  if (url.includes("/static/locales/")) {
    return { ok: true, status: 200, json: async () => FR };
  }
  calls.push({ url, body: opts.body ? JSON.parse(opts.body) : null });
  const res = nextResponse(url, opts);
  return {
    ok: res.ok,
    status: res.status,
    json: async () => res.body,
  };
};
globalThis.confirm = (msg) => { confirmCalls++; confirmPrompts.push(msg); return confirmAnswer; };
w.confirm = globalThis.confirm;

// ── Helpers ─────────────────────────────────────────────────────────────────
let testCount = 0;
let passCount = 0;

async function test(name, fn) {
  testCount++;
  calls = [];
  confirmCalls = 0;
  confirmPrompts = [];
  confirmAnswer = true;
  apiQueue = [];
  nextResponse = () => {
    if (apiQueue.length) return apiQueue.shift();
    return { ok: true, status: 200, body: { status: "ok" } };
  };
  // #154-A2 — themed dialogs live in document.body; clear any left behind by a
  // previous test so the helpers always reach the current one.
  document.querySelectorAll(".obsigate-modal-overlay").forEach((n) => n.remove());
  // #156-A10 — the undo/redo stacks are keyed by file and every helper mounts
  // the same vault/path: reset them so tests stay independent.
  resetXlsxHistory();
  try {
    await fn();
    console.log(`  ✓ ${name}`);
    passCount++;
  } catch (e) {
    console.log(`  ✗ ${name}`);
    console.log(`    ${e.message}`);
    if (e.stack) console.log(`    ${e.stack.split("\n").slice(1, 3).join("\n    ")}`);
  }
}

const { renderXlsxViewer, resetXlsxHistory } = await import(
  pathToFileURL(path.join(REPO_ROOT, "frontend", "js", "viewer.js")).href
);
// Load the FR catalog so t() resolves the real strings.
const { initI18n } = await import(
  pathToFileURL(path.join(REPO_ROOT, "frontend", "js", "i18n.js")).href
);
await initI18n();

const sheetHtml = (value) =>
  '<div class="csv-table-wrapper"><table class="csv-table xlsx-table">' +
  '<thead><tr><th class="xlsx-corner"></th><th>A</th><th>B</th></tr></thead><tbody>' +
  `<tr><th class="xlsx-rownum">1</th><td data-cell="A1">${value}</td><td data-cell="B1">B1</td></tr>` +
  '<tr><th class="xlsx-rownum">2</th><td data-cell="A2">A2</td><td data-cell="B2">B2</td></tr>' +
  "</tbody></table></div>";

// The JSDOM fetch double serves locale files; everything else is a recorded
// API call answered by `nextResponse`. `apiQueue` lets a test script the
// successive windows a lazy-loading sheet will request.
let apiQueue = [];

function mount({ lossy = [], sheet = {}, revision = null } = {}) {
  const area = document.getElementById("content-area");
  area.innerHTML = "";
  renderXlsxViewer(area, {
    vault: "V",
    path: "data.xlsx",
    is_xlsx: true,
    xlsx_sheets: [{ name: "Feuille1", html: sheetHtml("100"), ...sheet }],
    xlsx_lossy_features: lossy,
    // #156-A12 — optimistic-concurrency token returned by the read.
    ...(revision ? { xlsx_revision: revision } : {}),
  });
  return area;
}

/** Mount a sheet flagged truncated so wireLazyRows() arms the footnote. */
function mountTruncated({ total = 520, rows = 1 } = {}) {
  return mount({
    sheet: { rows, cols: 3, total_rows: total, total_cols: 3, max_rows: 500, max_cols: 40, truncated: total > 500 },
  });
}

/**
 * Mount a CSV exactly as the backend serves it: a single `html` table and NO
 * `xlsx_sheets` (the shape that used to crash the save path, BUG-096).
 */
function mountCsv(html = sheetHtml("100")) {
  const area = document.getElementById("content-area");
  area.innerHTML = "";
  renderXlsxViewer(area, {
    vault: "V", path: "budget.csv", is_csv: true, is_markdown: false, html,
  });
  return area;
}

/** Mark a cell dirty the way a user edit would. */
function editCell(area, ref, text) {
  const td = area.querySelector(`td[data-cell="${ref}"]`);
  td.textContent = text;
  td.dispatchEvent(new w.Event("input", { bubbles: true }));
  return td;
}

/** #154-A2 — the themed dialog is the last overlay in document.body. */
const lastDialog = () => {
  const overlays = document.querySelectorAll(".obsigate-modal-overlay");
  return overlays[overlays.length - 1] || null;
};
const clickDialog = (which) => {
  const btn = lastDialog()?.querySelector(`[data-dialog="${which}"]`);
  assert.ok(btn, `dialog button "${which}" is present`);
  btn.click();
};

/** #155 — open the grid context menu on a cell (right-click). */
const openMenuOn = (area, ref) => {
  const td = area.querySelector(`td[data-cell="${ref}"]`);
  assert.ok(td, `cell ${ref} exists`);
  td.dispatchEvent(new w.MouseEvent("contextmenu", { bubbles: true, clientX: 12, clientY: 12 }));
  return document.querySelector(".xlsx-context-menu");
};
const menuItem = (label) =>
  [...document.querySelectorAll(".xlsx-context-item")].find((b) => b.textContent === label);

const lossyError = {
  ok: false,
  status: 409,
  body: {
    detail: "Saving this workbook would drop features…",
    code: "xlsx_lossy_content",
    details: { features: ["slicers"] },
  },
};

console.log("\n── xlsx viewer JSDOM integration tests (#153 P0) ──\n");

// ── A1 — warning banner ─────────────────────────────────────────────────────

await test("no banner when the workbook has nothing at risk", () => {
  const area = mount();
  assert.equal(area.querySelector(".xlsx-warning"), null);
});

await test("banner lists every lossy feature reported by the backend", () => {
  const area = mount({ lossy: ["cached_values", "slicers"] });
  const banner = area.querySelector(".xlsx-warning");
  assert.ok(banner, "banner absent");
  const tags = [...banner.querySelectorAll(".xlsx-warning-tag")].map((n) => n.textContent);
  assert.equal(tags.length, 2);
  assert.ok(tags.includes(FR["xlsx.feature_cached_values"]), tags.join("|"));
  assert.ok(tags.includes(FR["xlsx.feature_slicers"]), tags.join("|"));
  assert.ok(banner.textContent.includes(FR["xlsx.lossy_title"]));
  assert.ok(banner.textContent.includes(FR["xlsx.lossy_hint"]));
});

await test("formula toggle is present and starts unpressed", () => {
  const area = mount();
  const btn = area.querySelector("#xlsx-formula-btn");
  assert.ok(btn);
  assert.equal(btn.getAttribute("aria-pressed"), "false");
  assert.equal(btn.getAttribute("title"), FR["xlsx.formula_toggle_title"]);
});

// ── Save payload ────────────────────────────────────────────────────────────

await test("save sends one PUT per dirty sheet with the cell map", async () => {
  const area = mount();
  editCell(area, "A1", "250");
  area.querySelector("#xlsx-save-btn").click();
  await new Promise((r) => setTimeout(r, 0));
  assert.equal(calls.length, 1);
  assert.match(calls[0].url, /\/api\/file\/V\/xlsx\/save\?path=data\.xlsx/);
  assert.deepEqual(calls[0].body.cells, { A1: "250" });
  assert.equal(calls[0].body.sheet, "Feuille1");
  assert.equal(calls[0].body.force, false);
  assert.equal(calls[0].body.allow_formula, false);
});

await test("save button stays disabled when nothing is dirty", async () => {
  const area = mount();
  const btn = area.querySelector("#xlsx-save-btn");
  assert.equal(btn.disabled, true);
  btn.click();
  await new Promise((r) => setTimeout(r, 0));
  assert.equal(calls.length, 0);
});

// ── A4 — formula toggle ─────────────────────────────────────────────────────

await test("f(x) toggle flips allow_formula on the next save", async () => {
  const area = mount();
  area.querySelector("#xlsx-formula-btn").click();
  assert.equal(area.querySelector("#xlsx-formula-btn").getAttribute("aria-pressed"), "true");
  editCell(area, "A1", "=B1*2");
  area.querySelector("#xlsx-save-btn").click();
  await new Promise((r) => setTimeout(r, 0));
  assert.equal(calls[0].body.allow_formula, true);
});

// ── A1 — 409 confirmation & force retry ─────────────────────────────────────

await test("409 xlsx_lossy_content asks once then retries with force", async () => {
  const area = mount({ lossy: ["slicers"] });
  editCell(area, "A1", "250");
  nextResponse = () => (calls.length === 1 ? lossyError : { ok: true, status: 200, body: {} });
  area.querySelector("#xlsx-save-btn").click();
  await new Promise((r) => setTimeout(r, 10));
  // #154-A2 — a themed dialog replaces window.confirm(), and no retry happens
  // before the user answers.
  const dialog = lastDialog();
  assert.ok(dialog, "a themed confirmation is shown");
  assert.ok(dialog.textContent.includes(FR["xlsx.feature_slicers"]), dialog.textContent);
  assert.equal(calls.length, 1, "nothing is retried before the answer");
  clickDialog("confirm");
  await new Promise((r) => setTimeout(r, 10));
  assert.equal(calls.length, 2);
  assert.equal(calls[0].body.force, false);
  assert.equal(calls[1].body.force, true);
  // Save succeeded → cells are no longer dirty.
  assert.equal(area.querySelectorAll("td.xlsx-dirty").length, 0);
});

await test("confirming once is enough for the following saves", async () => {
  const area = mount({ lossy: ["cached_values"] });
  editCell(area, "A1", "1");
  nextResponse = () => (calls.length === 1 ? lossyError : { ok: true, status: 200, body: {} });
  area.querySelector("#xlsx-save-btn").click();
  await new Promise((r) => setTimeout(r, 10));
  clickDialog("confirm");
  await new Promise((r) => setTimeout(r, 10));
  editCell(area, "A1", "2");
  area.querySelector("#xlsx-save-btn").click();
  await new Promise((r) => setTimeout(r, 10));
  assert.equal(calls.length, 3);
  assert.equal(calls[2].body.force, true);
  // Wait out the dialog close animation, then confirm none is left.
  await new Promise((r) => setTimeout(r, 250));
  assert.equal(document.querySelectorAll(".obsigate-modal-overlay").length, 0, "the user is not asked twice");
});

await test("refusing the confirmation writes nothing and keeps the cells dirty", async () => {
  const area = mount({ lossy: ["slicers"] });
  editCell(area, "A1", "250");
  nextResponse = () => lossyError;
  area.querySelector("#xlsx-save-btn").click();
  await new Promise((r) => setTimeout(r, 10));
  clickDialog("cancel");
  await new Promise((r) => setTimeout(r, 10));
  assert.equal(calls.length, 1, "no retry after a refusal");
  assert.equal(area.querySelectorAll("td.xlsx-dirty").length, 1);
  assert.equal(area.querySelector("#xlsx-save-btn").disabled, false);
});

await test("a 409 conflict shows a non-blocking retry banner and keeps the edits", async () => {
  const area = mount();
  editCell(area, "A1", "250");
  nextResponse = () => ({ ok: false, status: 409, body: { detail: "busy", code: "conflict" } });
  area.querySelector("#xlsx-save-btn").click();
  await new Promise((r) => setTimeout(r, 10));
  const banner = area.querySelector(".xlsx-banner-conflict");
  assert.ok(banner, "a conflict banner is shown");
  assert.ok(banner.textContent.includes(FR["xlsx.conflict_msg"]), banner.textContent);
  // The edit is preserved and the save button is usable again.
  assert.equal(area.querySelectorAll("td.xlsx-dirty").length, 1);
  assert.equal(area.querySelector("#xlsx-save-btn").disabled, false);
  // The retry re-runs the save.
  nextResponse = () => ({ ok: true, status: 200, body: {} });
  banner.querySelector(".xlsx-banner-retry").click();
  await new Promise((r) => setTimeout(r, 10));
  // #156-A12 — the retry re-reads the file first (to pick up the current
  // revision), then re-sends the save: one extra request.
  assert.equal(calls.length, 3);
  assert.match(calls[1].url, /\/api\/file\/V\?path=data\.xlsx/);
  assert.equal(area.querySelectorAll("td.xlsx-dirty").length, 0);
  assert.equal(area.querySelector(".xlsx-banner-conflict"), null, "banner cleared on retry");
});

await test("a non-409 failure is not retried", async () => {
  const area = mount();
  editCell(area, "A1", "250");
  nextResponse = () => ({ ok: false, status: 500, body: { detail: "boom" } });
  area.querySelector("#xlsx-save-btn").click();
  await new Promise((r) => setTimeout(r, 10));
  assert.equal(calls.length, 1);
  assert.equal(confirmCalls, 0);
  assert.equal(area.querySelectorAll("td.xlsx-dirty").length, 1);
});

// ── A8 — truncation notice ───────────────────────────────────────────────────

await test("no notice when the sheet fits within the render caps", () => {
  const area = mount({
    sheet: { rows: 500, cols: 40, total_rows: 500, total_cols: 40, max_rows: 500, max_cols: 40, truncated: false },
  });
  assert.equal(area.querySelector(".xlsx-truncated"), null);
});

await test("notice states the cap and the real size of a truncated sheet", () => {
  const area = mount({
    sheet: { rows: 500, cols: 12, total_rows: 1200, total_cols: 12, max_rows: 500, max_cols: 40, truncated: true },
  });
  const note = area.querySelector(".xlsx-truncated");
  assert.ok(note, "notice absent");
  const txt = note.textContent;
  assert.ok(txt.includes(FR["xlsx.truncated_title"]), txt);
  // {shown} is the CAP (500), not the post-trim row count: a sparse sheet
  // renders 1 row but the view still reaches 500 of them.
  const expected = FR["xlsx.truncated_rows"].replace("{shown}", "500").replace("{total}", "1200");
  assert.ok(txt.includes(expected), `${txt} !includes ${expected}`);
  // Nothing to say about the columns here (12 < 40).
  assert.ok(!txt.includes(FR["xlsx.truncated_cols"]), txt);
});

await test("notice mentions both axes when rows AND columns overflow", () => {
  const area = mount({
    sheet: { rows: 1, cols: 40, total_rows: 501, total_cols: 45, max_rows: 500, max_cols: 40, truncated: true },
  });
  const txt = area.querySelector(".xlsx-truncated").textContent;
  assert.ok(
    txt.includes(FR["xlsx.truncated_cols"].replace("{shown}", "40").replace("{total}", "45")),
    txt
  );
});

await test("a payload without the dimensions shows no notice", () => {
  // Backward compatibility: an older cached response must not produce "NaN".
  const area = mount({ sheet: { name: "Feuille1" } });
  assert.equal(area.querySelector(".xlsx-truncated"), null);
  assert.ok(!area.textContent.includes("NaN"));
});

// ── A9bis — lazy loading of the truncated tail ──────────────────────────────

const windowHtml = (from, to) =>
  '<div class="csv-table-wrapper"><table class="csv-table xlsx-table">' +
  "<tbody>" +
  Array.from({ length: to - from + 1 }, (_, i) => {
    const r = from + i;
    return `<tr><th class="xlsx-rownum">${r}</th><td data-cell="A${r}">Ligne ${r}</td></tr>`;
  }).join("") +
  "</tbody></table></div>";

await test("a truncated sheet gets a load-more footnote, a normal one does not", () => {
  const truncated = mountTruncated();
  assert.ok(truncated.querySelector(".xlsx-load-more"), "footnote absent");
  assert.ok(truncated.querySelector(".xlsx-load-more").textContent.includes(FR["xlsx.load_more"]));

  const plain = mount({ sheet: { rows: 10, cols: 2, total_rows: 10, total_cols: 2, max_rows: 500, max_cols: 40, truncated: false } });
  assert.equal(plain.querySelector(".xlsx-load-more"), null);
});

await test("clicking the footnote fetches the next window with the right query", async () => {
  const area = mountTruncated();
  apiQueue.push({
    ok: true,
    status: 200,
    body: { sheet: "Feuille1", offset: 1, limit: 500, rows: 2, total_rows: 3, truncated: true, has_more: false, html: windowHtml(2, 3) },
  });
  area.querySelector(".xlsx-load-more").click();
  await new Promise((r) => setTimeout(r, 5));
  assert.equal(calls.length, 1);
  assert.match(calls[0].url, /\/api\/file\/V\/xlsx\/sheet\?path=data\.xlsx&sheet=Feuille1&offset=1&limit=500/);
  // Rows 2 and 3 landed in the table with their real coordinates.
  assert.ok(area.querySelector('td[data-cell="A2"]'));
  assert.ok(area.querySelector('td[data-cell="A3"]'));
  assert.ok(area.querySelector('th.xlsx-rownum') && area.textContent.includes("Ligne 3"));
  // Everything loaded → the footnote is hidden (kept in the DOM, class `done`).
  const foot = area.querySelector(".xlsx-load-more");
  assert.ok(foot, "the footnote element survives");
  assert.equal(foot.classList.contains("done"), true);
  assert.ok(!foot.textContent.includes(FR["xlsx.load_more"]), foot.textContent);
});

await test("appended rows are editable and tracked as dirty", async () => {
  const area = mountTruncated();
  apiQueue.push({
    ok: true,
    status: 200,
    body: { sheet: "Feuille1", offset: 1, limit: 500, rows: 1, total_rows: 3, truncated: true, has_more: false, html: windowHtml(2, 2) },
  });
  area.querySelector(".xlsx-load-more").click();
  await new Promise((r) => setTimeout(r, 5));
  editCell(area, "A2", "modifié");
  area.querySelector("#xlsx-save-btn").click();
  await new Promise((r) => setTimeout(r, 5));
  assert.equal(calls.length, 2, "the save went out");
  assert.equal(calls[1].url.includes("/xlsx/save"), true);
  assert.deepEqual(calls[1].body.cells, { A2: "modifié" });
  assert.equal(calls[1].body.sheet, "Feuille1");
});

await test("a failed window fetch keeps the footnote and shows an error toast", async () => {
  const area = mountTruncated();
  apiQueue.push({ ok: false, status: 500, body: { detail: "boom" } });
  area.querySelector(".xlsx-load-more").click();
  await new Promise((r) => setTimeout(r, 5));
  const foot = area.querySelector(".xlsx-load-more");
  assert.ok(foot, "footnote must survive a failed fetch");
  assert.ok(foot.textContent.includes(FR["xlsx.load_more"]), foot.textContent);
  // `api()` itself toasts the failure (shared behaviour, asserted in E2E);
  // here we assert the local consequence: the footnote keeps its label.
  // Retrying works once the server answers again.
  apiQueue.push({
    ok: true,
    status: 200,
    body: { sheet: "Feuille1", offset: 1, limit: 500, rows: 1, total_rows: 3, truncated: true, has_more: false, html: windowHtml(2, 2) },
  });
  foot.click();
  await new Promise((r) => setTimeout(r, 5));
  assert.ok(area.querySelector('td[data-cell="A2"]'));
  assert.equal(area.querySelector(".xlsx-load-more").classList.contains("done"), true);
});

// ── A7 — formula bar & keyboard navigation ──────────────────────────────────

await test("the formula bar starts empty and disabled", () => {
  const area = mount();
  assert.equal(area.querySelector("#xlsx-active-cell").value, "");
  assert.equal(area.querySelector("#xlsx-formula-input").disabled, true);
});

await test("focusing a cell shows its name and content in the bar", () => {
  const area = mount();
  const td = area.querySelector('td[data-cell="A1"]');
  td.dispatchEvent(new w.Event("focus", { bubbles: false }));
  // JSDOM does not run the default focus behaviour on dispatchEvent, so go
  // through the real API:
  td.focus();
  assert.equal(area.querySelector("#xlsx-active-cell").value, "A1");
  assert.equal(area.querySelector("#xlsx-formula-input").value, "100");
  assert.equal(area.querySelector("#xlsx-formula-input").disabled, false);
});

await test("typing in the bar edits the cell live and marks it dirty", () => {
  const area = mount();
  const td = area.querySelector('td[data-cell="A1"]');
  td.focus();
  const input = area.querySelector("#xlsx-formula-input");
  input.value = "depuis la barre";
  input.dispatchEvent(new w.Event("input", { bubbles: true }));
  assert.equal(td.textContent, "depuis la barre");
  assert.equal(td.classList.contains("xlsx-dirty"), true);
  assert.equal(area.querySelector("#xlsx-save-btn").disabled, false);
  // The save payload carries the cell edit.
  area.querySelector("#xlsx-save-btn").click();
  return new Promise((r) => setTimeout(r, 5)).then(() => {
    assert.deepEqual(calls[0].body.cells, { A1: "depuis la barre" });
  });
});

await test("Tab and arrows move to the neighbour cell", () => {
  const area = mount();
  const a1 = area.querySelector('td[data-cell="A1"]');
  a1.focus();
  a1.dispatchEvent(new w.KeyboardEvent("keydown", { key: "Tab", bubbles: true }));
  assert.equal(area.querySelector("#xlsx-active-cell").value, "B1");
  const b1 = area.querySelector('td[data-cell="B1"]');
  b1.dispatchEvent(new w.KeyboardEvent("keydown", { key: "ArrowDown", bubbles: true }));
  assert.equal(area.querySelector("#xlsx-active-cell").value, "B2");
  const b2 = area.querySelector('td[data-cell="B2"]');
  b2.dispatchEvent(new w.KeyboardEvent("keydown", { key: "ArrowLeft", bubbles: true }));
  assert.equal(area.querySelector("#xlsx-active-cell").value, "A2");
});

await test("Enter commits and Shift+Tab goes backwards", () => {
  const area = mount();
  const a1 = area.querySelector('td[data-cell="A1"]');
  a1.focus();
  a1.dispatchEvent(new w.KeyboardEvent("keydown", { key: "Tab", bubbles: true }));
  const b1 = area.querySelector('td[data-cell="B1"]');
  b1.dispatchEvent(new w.KeyboardEvent("keydown", { key: "Tab", shiftKey: true, bubbles: true }));
  assert.equal(area.querySelector("#xlsx-active-cell").value, "A1");
});

await test("a saved edit from the bar resets the dirty flag and orig value", async () => {
  const area = mount();
  const td = area.querySelector('td[data-cell="A1"]');
  td.focus();
  const input = area.querySelector("#xlsx-formula-input");
  input.value = "200";
  input.dispatchEvent(new w.Event("input", { bubbles: true }));
  area.querySelector("#xlsx-save-btn").click();
  await new Promise((r) => setTimeout(r, 5));
  assert.equal(area.querySelectorAll("td.xlsx-dirty").length, 0);
  assert.equal(td.dataset.orig, "200");
});

// ── A13 — sort / filter / find / CSV export ─────────────────────────────────

const mountGrid = (revision = null) => {
  const area = document.getElementById("content-area");
  area.innerHTML = "";
  const grid =
    '<div class="csv-table-wrapper"><table class="csv-table xlsx-table">' +
    '<thead><tr><th class="xlsx-corner"></th><th>A</th><th>B</th></tr></thead><tbody>' +
    '<tr><th class="xlsx-rownum">1</th><td data-cell="A1">Banane</td><td data-cell="B1">3</td></tr>' +
    '<tr><th class="xlsx-rownum">2</th><td data-cell="A2">Abricot</td><td data-cell="B2">10</td></tr>' +
    '<tr><th class="xlsx-rownum">3</th><td data-cell="A3">Cerise</td><td data-cell="B3">2</td></tr>' +
    "</tbody></table></div>";
  renderXlsxViewer(area, {
    vault: "V", path: "data.xlsx", is_xlsx: true,
    xlsx_sheets: [{ name: "Fruits", html: grid, rows: 3, cols: 2, total_rows: 3, total_cols: 2, max_rows: 500, max_cols: 40, truncated: false }],
    xlsx_lossy_features: [],
    ...(revision ? { xlsx_revision: revision } : {}),
  });
  return area;
};

await test("the context menu sorts the rows numerically or lexically", () => {
  const area = mountGrid();
  // Sort by column B (numbers) ascending: 2, 3, 10.
  openMenuOn(area, "B1");
  menuItem(FR["xlsx.sort_asc"]).click();
  let cells = [...area.querySelectorAll("tbody td[data-cell^=\"B\"]")].map((td) => td.textContent);
  assert.deepEqual(cells, ["2", "3", "10"]);
  // Re-open and sort descending.
  openMenuOn(area, "B1");
  menuItem(FR["xlsx.sort_desc"]).click();
  cells = [...area.querySelectorAll("tbody td[data-cell^=\"B\"]")].map((td) => td.textContent);
  assert.deepEqual(cells, ["10", "3", "2"]);
});

await test("a dirty cell travels with its row during a sort", () => {
  const area = mountGrid();
  editCell(area, "A3", "Cerise modifiée");
  openMenuOn(area, "B1");
  menuItem(FR["xlsx.sort_asc"]).click();
  const aCells = [...area.querySelectorAll("tbody td[data-cell^=\"A\"]")].map((td) => td.textContent);
  assert.ok(aCells.includes("Cerise modifiée"), aCells.join("|"));
  assert.equal(area.querySelectorAll("td.xlsx-dirty").length, 1);
});

await test("the filter hides the rows that do not match", () => {
  const area = mountGrid();
  // The filter reuses the find input: type and the rows filter live.
  const input = area.querySelector("#xlsx-find-input");
  input.value = "abri";
  input.dispatchEvent(new w.Event("input", { bubbles: true }));
  const visible = [...area.querySelectorAll("tbody tr")].filter((tr) => tr.style.display !== "none");
  assert.equal(visible.length, 1);
  assert.ok(visible[0].textContent.includes("Abricot"));
});

await test("find highlights matches and navigates with the counter", () => {
  const area = mountGrid();
  const input = area.querySelector("#xlsx-find-input");
  const count = area.querySelector("#xlsx-find-count");
  input.value = "cerise"; // lowercase: the default search ignores the case
  input.dispatchEvent(new w.Event("input", { bubbles: true }));
  assert.equal(area.querySelectorAll("mark.xlsx-find-hit").length, 1);
  assert.ok(count.textContent.includes("1/1"), count.textContent);
  // The hit is inside the matching cell.
  assert.ok(area.querySelector('td[data-cell="A3"] mark.xlsx-find-hit'));
});

await test("CSV export downloads the visible sheet without the cached shadows", () => {
  const area = mountGrid();
  const clicks = [];
  const realCreate = document.createElement.bind(document);
  const anchor = realCreate("a");
  document.createElement = (tag) => {
    if (tag === "a") { clicks.push(1); return anchor; }
    return realCreate(tag);
  };
  let href = "";
  Object.defineProperty(anchor, "href", { set(v) { href = v; }, get: () => href });
  URL.createObjectURL = () => "blob:x";
  URL.revokeObjectURL = () => {};
  area.querySelector("#xlsx-csv-btn").click();
  document.createElement = realCreate;
  assert.equal(clicks.length, 1);
  assert.equal(anchor.download, "Fruits.csv");
});

// ── A14 — structure menu ───────────────────────────────────────────────────

await test("sheet_add asks for a name, PUTs the action and applies it locally", async () => {
  const area = mount();
  apiQueue.push({ ok: true, status: 200, body: { status: "ok" } }); // PUT
  area.querySelector("#xlsx-structure-btn").click();
  const addBtn = [...area.querySelectorAll(".xlsx-structure-item")].find((b) => b.textContent === FR["xlsx.sheet_add"]);
  addBtn.click();
  await new Promise((r) => setTimeout(r, 5));
  // #154-A2 — a themed prompt replaces window.prompt().
  const input = lastDialog()?.querySelector('[data-dialog="input"]');
  assert.ok(input, "a themed prompt asks for the sheet name");
  input.value = "Feuille 2";
  clickDialog("confirm");
  await new Promise((r) => setTimeout(r, 10));
  assert.equal(calls.length, 1, "one locked rewrite, applied locally without re-read");
  assert.match(calls[0].url, /\/xlsx\/structure\?path=data\.xlsx/);
  assert.deepEqual(calls[0].body.actions, [{ op: "sheet_add", name: "Feuille 2" }]);
  assert.equal(calls[0].body.force, false);
  const tab = [...area.querySelectorAll(".xlsx-tab")].find((x) => x.textContent === "Feuille 2");
  assert.ok(tab, "the new tab is added locally");
  assert.ok(tab.classList.contains("active"), "the new sheet is activated");
});

await test("sheet_delete confirms and is refused on the last sheet", async () => {
  const area = mount();
  const delBtn = () => {
    area.querySelector("#xlsx-structure-btn").click();
    const items = [...area.querySelectorAll(".xlsx-structure-item")];
    const b = items.find((x) => x.textContent === FR["xlsx.sheet_delete"]);
    b.click();
  };
  // One sheet only → blocked before even confirming (no network call).
  delBtn();
  assert.equal(calls.length, 0, "nothing sent: last sheet");
});

await test("the 409 lossy flow re-emits with force after confirmation", async () => {
  const area = mount();
  // The prompt is answered through the themed dialog.
  apiQueue.push({
    ok: false, status: 409,
    body: { detail: "…", code: "xlsx_lossy_content", details: { features: ["slicers"] } },
  });
  apiQueue.push({ ok: true, status: 200, body: { status: "ok" } }); // retry w/ force
  area.querySelector("#xlsx-structure-btn").click();
  [...area.querySelectorAll(".xlsx-structure-item")].find((b) => b.textContent === FR["xlsx.sheet_add"]).click();
  await new Promise((r) => setTimeout(r, 5));
  lastDialog().querySelector('[data-dialog="input"]').value = "Feuille 2";
  clickDialog("confirm");
  await new Promise((r) => setTimeout(r, 10));
  const lossyDialog = lastDialog();
  assert.ok(lossyDialog && lossyDialog.textContent.includes(FR["xlsx.feature_slicers"]), "a lossy confirmation is shown");
  clickDialog("confirm");
  await new Promise((r) => setTimeout(r, 10));
  assert.equal(calls.length, 2, "refused write + forced retry, no re-read");
  assert.equal(calls[1].body.force, true);
});

// ── BUG-094 — structure actions work on an empty/new sheet ─────────────────

await test("the structure menu offers row/column actions without an active cell", () => {
  const area = mount();
  area.querySelector("#xlsx-structure-btn").click();
  const labels = [...area.querySelectorAll(".xlsx-structure-item")].map((b) => b.textContent);
  assert.ok(labels.includes(FR["xlsx.row_insert"]), "row insert available before any click");
  assert.ok(labels.includes(FR["xlsx.col_insert"]), "column insert available before any click");
});

// ── A17 — dashboard panel ─────────────────────────────────────────────
await test("the dashboard button fetches the metadata and renders named ranges + KPIs", async () => {
  const area = mount();
  const dashBtn = area.querySelector("#xlsx-dashboard-btn");
  assert.ok(dashBtn, "the dashboard button exists for an editable workbook");
  apiQueue.push({
    ok: true,
    status: 200,
    body: {
      vault: "V",
      path: "data.xlsx",
      named_ranges: [{ name: "MaPlage", scope: "", ref: "Data!$A$1:$B$5" }],
      objects: { charts: 2, pivots: 1 },
      sheets: [{ name: "Feuille1", cells: 9, rows: 3, cols: 3, formulas: 1, numeric: 2, kpi: [{ label: "A2", value: 12 }] }],
    },
  });
  dashBtn.click();
  await new Promise((r) => setTimeout(r, 5));
  assert.match(calls[0].url, /\/xlsx\/dashboard\?path=data\.xlsx/);
  const panel = area.querySelector(".xlsx-dashboard");
  assert.ok(panel, "the dashboard panel is rendered");
  assert.ok(panel.textContent.includes("MaPlage"), "named range is listed");
  assert.ok(panel.textContent.includes("A2"), "KPI label is rendered");
  assert.ok(panel.textContent.includes("12"), "KPI value is rendered");
  // Second click closes the panel.
  dashBtn.click();
  assert.equal(area.querySelector(".xlsx-dashboard"), null, "panel toggles closed");
});

await test("the dashboard opens in the right-hand inspector, not inline in the grid", async () => {
  const area = mount();
  apiQueue.push({
    ok: true, status: 200,
    body: { vault: "V", path: "data.xlsx", named_ranges: [], objects: { charts: 0, pivots: 0 }, sheets: [{ name: "Feuille1", cells: 1, rows: 1, cols: 1, formulas: 0, numeric: 0, kpi: [] }] },
  });
  area.querySelector("#xlsx-dashboard-btn").click();
  await new Promise((r) => setTimeout(r, 5));
  const inspector = area.querySelector("#xlsx-inspector");
  assert.equal(inspector.classList.contains("open"), true, "the inspector opens");
  assert.equal(inspector.getAttribute("aria-hidden"), "false");
  assert.ok(inspector.querySelector("#xlsx-inspector-body .xlsx-dashboard"), "the dashboard lives in the inspector");
  // The grid is still present beside it (not replaced).
  assert.ok(area.querySelector(".xlsx-main .xlsx-table"), "the grid stays visible");
  // Closing removes the panel and closes the inspector.
  area.querySelector("#xlsx-inspector-close").click();
  assert.equal(area.querySelector(".xlsx-dashboard"), null);
  assert.equal(inspector.classList.contains("open"), false);
  assert.equal(inspector.getAttribute("aria-hidden"), "true");
});

await test("the inspector exposes an AI assistant entry point", () => {
  const area = mount();
  assert.ok(area.querySelector("#xlsx-inspector-ai"), "AI button in the inspector head");
  const title = area.querySelector("#xlsx-inspector .xlsx-inspector-title");
  assert.equal(title.textContent, FR["xlsx.inspector_title"]);
});

await test("a csv mounts without the dashboard button", () => {
  const area = document.getElementById("content-area");
  area.innerHTML = "";
  renderXlsxViewer(area, {
    vault: "V",
    path: "data.csv",
    is_csv: true,
    xlsx_sheets: [{ name: "Feuille1", html: sheetHtml("100") }],
    xlsx_lossy_features: [],
  });
  assert.equal(area.querySelector("#xlsx-dashboard-btn"), null, "no dashboard for csv");
  assert.equal(area.querySelector("#xlsx-structure-btn"), null, "no structure menu for csv");
  assert.ok(area.querySelector("td[data-cell=\"A1\"]"), "the grid is still editable");
});

await test("two windows in a row walk the whole sheet", async () => {
  const area = mountTruncated({ total: 1200 });
  apiQueue.push({
    ok: true,
    status: 200,
    body: { sheet: "Feuille1", offset: 1, limit: 500, rows: 500, total_rows: 1200, truncated: true, has_more: true, html: windowHtml(2, 501) },
  });
  area.querySelector(".xlsx-load-more").click();
  await new Promise((r) => setTimeout(r, 5));
  assert.ok(area.querySelector('td[data-cell="A501"]'));
  // The footnote still shows, with the updated progress.
  let foot = area.querySelector(".xlsx-load-more");
  assert.ok(foot, "more rows remain");
  assert.ok(foot.textContent.includes("501"), foot.textContent);

  apiQueue.push({
    ok: true,
    status: 200,
    body: { sheet: "Feuille1", offset: 501, limit: 500, rows: 200, total_rows: 1200, truncated: true, has_more: false, html: windowHtml(502, 701) },
  });
  foot.click();
  await new Promise((r) => setTimeout(r, 5));
  assert.ok(area.querySelector('td[data-cell="A701"]'));
  assert.equal(area.querySelector(".xlsx-load-more").classList.contains("done"), true);
});

// ── #154-A1 — command bar, sheet tabs, status pills ─────────────────────────

await test("a single-sheet workbook still shows its tab and a + button", () => {
  const area = mount();
  assert.ok(area.querySelector(".xlsx-tabs"), "the tab strip is always rendered");
  assert.equal(area.querySelectorAll(".xlsx-tab").length, 1);
  const add = area.querySelector("#xlsx-tab-add");
  assert.ok(add, "the + button is present for an editable sheet");
  assert.equal(add.getAttribute("title"), FR["xlsx.tabs_add_sheet"]);
});

await test("a csv shows no tab strip and no + button", () => {
  const area = document.getElementById("content-area");
  area.innerHTML = "";
  renderXlsxViewer(area, {
    vault: "V", path: "data.csv", is_csv: true,
    xlsx_sheets: [{ name: "Feuille1", html: sheetHtml("100") }],
    xlsx_lossy_features: [],
  });
  assert.equal(area.querySelector(".xlsx-tabs"), null);
  assert.equal(area.querySelector("#xlsx-tab-add"), null);
});

await test("saving an edited csv PUTs /csv/save (payload without xlsx_sheets)", async () => {
  // BUG-096 — a CSV read payload has no `xlsx_sheets`; building the save job
  // from `sheets[0].name` used to throw a TypeError before any request, so a
  // CSV edit could never be saved from the viewer.
  const area = mountCsv();
  editCell(area, "A1", "modifié");
  area.querySelector("#xlsx-save-btn").click();
  await new Promise((r) => setTimeout(r, 10));
  assert.equal(calls.length, 1, "exactly one save request");
  assert.match(calls[0].url, /\/api\/file\/V\/csv\/save\?path=budget\.csv/);
  assert.deepEqual(calls[0].body.cells, { A1: "modifié" });
  // Committed: the cell is no longer dirty and the button is disabled again.
  assert.equal(area.querySelectorAll("td.xlsx-dirty").length, 0);
  assert.equal(area.querySelector("#xlsx-save-btn").disabled, true);
});

await test("the + button adds a sheet through the structure endpoint", async () => {
  const area = mount();
  apiQueue.push({ ok: true, status: 200, body: { status: "ok" } }); // PUT
  apiQueue.push({
    ok: true, status: 200,
    body: { is_xlsx: true, vault: "V", path: "data.xlsx", xlsx_sheets: [{ name: "Feuille 2", html: sheetHtml("neuf") }], xlsx_lossy_features: [] },
  }); // re-read
  area.querySelector("#xlsx-tab-add").click();
  await new Promise((r) => setTimeout(r, 5));
  lastDialog().querySelector('[data-dialog="input"]').value = "Feuille 2";
  clickDialog("confirm");
  await new Promise((r) => setTimeout(r, 10));
  assert.equal(calls.length, 1, "one locked rewrite, applied locally without re-read");
  assert.match(calls[0].url, /\/xlsx\/structure\?path=data\.xlsx/);
  assert.deepEqual(calls[0].body.actions, [{ op: "sheet_add", name: "Feuille 2" }]);
  const tab = [...area.querySelectorAll(".xlsx-tab")].find((x) => x.textContent === "Feuille 2");
  assert.ok(tab, "the new tab is added locally");
});

await test("a read-only workbook shows the read-only pill and offers no editing", () => {
  const area = document.getElementById("content-area");
  area.innerHTML = "";
  renderXlsxViewer(area, {
    vault: "V", path: "data.xls", is_xlsx: true, xlsx_readonly: true,
    xlsx_sheets: [{ name: "Feuille1", html: sheetHtml("100") }],
    xlsx_lossy_features: [],
  });
  const pill = area.querySelector(".xlsx-status-readonly");
  assert.ok(pill, "read-only pill");
  assert.ok(pill.textContent.includes(FR["xlsx.readonly_badge"]), pill.textContent);
  assert.equal(area.querySelector("#xlsx-tab-add"), null, "no + for read-only");
  assert.equal(area.querySelector("#xlsx-structure-btn"), null, "no structure for read-only");
  assert.equal(area.querySelector("#xlsx-undo-btn"), null, "no history for read-only");
  assert.equal(area.querySelector('td[data-cell="A1"]').getAttribute("contenteditable"), null, "cells are not editable");
});

await test("no formulas pill is rendered anymore, on xlsx or csv (#180-A4)", () => {
  const area = mount();
  assert.equal(area.querySelector(".xlsx-status-formula"), null, "no formula pill on xlsx");

  const csvArea = document.getElementById("content-area");
  csvArea.innerHTML = "";
  renderXlsxViewer(csvArea, {
    vault: "V", path: "data.csv", is_csv: true,
    xlsx_sheets: [{ name: "Feuille1", html: sheetHtml("100") }],
    xlsx_lossy_features: [],
  });
  assert.equal(csvArea.querySelector(".xlsx-status-formula"), null, "no formula pill on csv");
  assert.equal(csvArea.querySelector(".xlsx-status-bar"), null, "no status bar at all without pills");
});

await test("the command bar exposes grouped actions with a primary save", () => {
  const area = mount();
  assert.ok(area.querySelector(".xlsx-cmdbar"), "grouped command bar");
  assert.ok(area.querySelector('#xlsx-save-btn').classList.contains("xlsx-save-primary"));
  assert.ok(area.querySelector(".xlsx-cmd-group[data-group='formulas']"));
  assert.ok(area.querySelector(".xlsx-cmd-group[data-group='insert']"));
  assert.equal(area.querySelector(".xlsx-cmd-group[data-group='file']"), null, "no file group left in the ribbon (#180-A3)");
});

// ── #154-A4 — undo/redo, ARIA ───────────────────────────────────────────────

await test("undo/redo restores cell edits from the command stack", () => {
  const area = mount();
  const td = area.querySelector('td[data-cell="A1"]');
  const undoBtn = area.querySelector("#xlsx-undo-btn");
  const redoBtn = area.querySelector("#xlsx-redo-btn");
  assert.equal(undoBtn.disabled, true, "nothing to undo yet");
  td.focus();
  editCell(area, "A1", "42");
  td.dispatchEvent(new w.Event("blur"));
  assert.equal(undoBtn.disabled, false, "undo becomes available after a committed edit");
  undoBtn.click();
  assert.equal(td.textContent, "100", "undo restores the original value");
  assert.equal(area.querySelectorAll("td.xlsx-dirty").length, 0, "the cell is clean again");
  assert.equal(redoBtn.disabled, false, "redo becomes available");
  redoBtn.click();
  assert.equal(td.textContent, "42", "redo reapplies the edit");
  assert.equal(area.querySelectorAll("td.xlsx-dirty").length, 1, "the edit is dirty again");
});

await test("a no-op undo/redo pair leaves the buttons disabled", () => {
  const area = mount();
  assert.equal(area.querySelector("#xlsx-undo-btn").disabled, true);
  assert.equal(area.querySelector("#xlsx-redo-btn").disabled, true);
});

await test("the grid exposes ARIA grid semantics", () => {
  const area = mount();
  const table = area.querySelector(".xlsx-table");
  assert.equal(table.getAttribute("role"), "grid");
  assert.equal(area.querySelector(".xlsx-table thead th").getAttribute("role"), "columnheader");
  assert.equal(area.querySelector('td[data-cell="A1"]').getAttribute("role"), "gridcell");
  assert.equal(area.querySelector("th.xlsx-rownum").getAttribute("role"), "rowheader");
});

// ── #154-A5 — extracted refs module & dashboard → grid link ────────────────

const { parseRef, columnName, firstCellOfRange, sheetOfRef } = await import(
  pathToFileURL(path.join(REPO_ROOT, "frontend", "js", "xlsx", "refs.js")).href
);

await test("parseRef / columnName round-trip A1 references", () => {
  assert.deepEqual(parseRef("A1"), { row: 1, col: 1 });
  assert.deepEqual(parseRef("B12"), { row: 12, col: 2 });
  assert.deepEqual(parseRef("AA3"), { row: 3, col: 27 });
  assert.equal(parseRef("A"), null);
  assert.equal(columnName(1), "A");
  assert.equal(columnName(26), "Z");
  assert.equal(columnName(27), "AA");
  assert.equal(columnName(parseRef("AB9").col), "AB");
});

await test("range helpers extract the sheet and the first cell", () => {
  assert.equal(firstCellOfRange("Data!$A$1:$B$5"), "A1");
  assert.equal(firstCellOfRange("'Mon onglet'!$C$3"), "C3");
  assert.equal(firstCellOfRange("A1:B2"), "A1");
  assert.equal(sheetOfRef("Data!$A$1:$B$5"), "Data");
  assert.equal(sheetOfRef("'Mon onglet'!$C$3"), "Mon onglet");
  assert.equal(sheetOfRef("A1:B2"), null);
});

await test("clicking a named range reveals its first cell in the grid", async () => {
  const area = mount();
  apiQueue.push({
    ok: true, status: 200,
    body: { vault: "V", path: "data.xlsx", named_ranges: [{ name: "MaPlage", scope: "", ref: "Feuille1!$A$1:$B$2" }], objects: { charts: 0, pivots: 0 }, sheets: [] },
  });
  area.querySelector("#xlsx-dashboard-btn").click();
  await new Promise((r) => setTimeout(r, 5));
  const row = area.querySelector(".xlsx-range-row[data-ref]");
  assert.ok(row, "the named range row is rendered");
  row.click();
  assert.equal(area.querySelector("#xlsx-active-cell").value, "A1", "the anchor cell becomes active");
  assert.ok(area.querySelector('td[data-cell="A1"]').classList.contains("xlsx-active"));
});

await test("the inspector exposes a resize handle", () => {
  const area = mount();
  assert.ok(area.querySelector("#xlsx-inspector-resize"), "resize handle present");
});

// ── #155 — selection & context menu ─────────────────────────────────────────

await test("a right-click on a cell opens a themed context menu", () => {
  const area = mount();
  const menu = openMenuOn(area, "A1");
  assert.ok(menu, "menu rendered");
  assert.ok(menuItem(FR["xlsx.insert_row_above"]), "insert row item");
  assert.ok(menuItem(FR["xlsx.sort_asc"]), "sort item");
  assert.ok(menuItem(FR["xlsx.clear_contents"]), "clear item");
});

await test("the context menu inserts a row through the structure endpoint", async () => {
  const area = mount();
  apiQueue.push({ ok: true, status: 200, body: { status: "ok" } }); // PUT
  openMenuOn(area, "A2");
  menuItem(FR["xlsx.insert_row_above"]).click();
  await new Promise((r) => setTimeout(r, 10));
  assert.equal(calls.length, 1, "one locked rewrite, applied locally without re-read");
  assert.match(calls[0].url, /\/xlsx\/structure\?path=data\.xlsx/);
  assert.deepEqual(calls[0].body.actions, [{ op: "row_insert", sheet: "Feuille1", at: 2, count: 1 }]);
  assert.equal(area.querySelector('td[data-cell="A2"]').textContent, "", "a blank row is inserted");
  assert.equal(area.querySelector('td[data-cell="A3"]').textContent, "A2", "the old row shifts down");
});

await test("clear contents empties the selected cells (local edit)", () => {
  const area = mount();
  openMenuOn(area, "A1");
  menuItem(FR["xlsx.clear_contents"]).click();
  assert.equal(area.querySelector('td[data-cell="A1"]').textContent, "");
  assert.equal(area.querySelectorAll("td.xlsx-dirty").length, 1);
  assert.equal(area.querySelector("#xlsx-save-btn").disabled, false);
});

await test("clicking a column header selects the whole column", () => {
  const area = mount();
  area.querySelector(".xlsx-table thead th:nth-child(2)").click(); // column A
  assert.equal(area.querySelector("#xlsx-active-cell").value, "A1:A2");
  const selected = [...area.querySelectorAll("td.xlsx-selected")].map((td) => td.dataset.cell).sort();
  assert.deepEqual(selected, ["A1", "A2"]);
});

await test("clicking a row header selects the whole row", () => {
  const area = mount();
  area.querySelector("th.xlsx-rownum").click(); // row 1
  // #184 — the row spans the whole laid-out grid (columns A → Z).
  assert.equal(area.querySelector("#xlsx-active-cell").value, "A1:Z1");
});

await test("dragging from a cell to another selects the range", () => {
  const area = mount();
  const viewer = area.querySelector(".xlsx-viewer");
  const a1 = area.querySelector('td[data-cell="A1"]');
  const b2 = area.querySelector('td[data-cell="B2"]');
  a1.dispatchEvent(new w.MouseEvent("mousedown", { bubbles: true, button: 0 }));
  b2.dispatchEvent(new w.MouseEvent("mouseover", { bubbles: true, button: 0 }));
  viewer.dispatchEvent(new w.MouseEvent("mouseup", { bubbles: true }));
  assert.equal(area.querySelector("#xlsx-active-cell").value, "A1:B2");
  assert.equal(area.querySelectorAll("td.xlsx-selected").length, 4);
});

await test("Shift+Arrow extends the selection from the anchor", () => {
  const area = mount();
  const a1 = area.querySelector('td[data-cell="A1"]');
  a1.focus();
  a1.dispatchEvent(new w.KeyboardEvent("keydown", { key: "ArrowRight", shiftKey: true, bubbles: true }));
  assert.equal(area.querySelector("#xlsx-active-cell").value, "A1:B1");
  assert.equal(area.querySelectorAll("td.xlsx-selected").length, 2);
});

await test("the context menu module exposes open/close helpers", async () => {
  const mod = await import(
    pathToFileURL(path.join(REPO_ROOT, "frontend", "js", "xlsx", "context-menu.js")).href
  );
  const menu = mod.openContextMenu(5, 5, [{ label: "Only" }]);
  assert.equal(menu.querySelectorAll(".xlsx-context-item").length, 1);
  assert.equal(mod.isContextMenuOpen(), true);
  mod.closeContextMenu();
  assert.equal(mod.isContextMenuOpen(), false);
});

// ── #156 (A5/A6/A7) — presse-papiers, clavier complet, zone Nom ────────────

/** #156-A5 — a 6×6 grid (A1:F6) so a pasted block has room to land. */
const mountWide = () => {
  const area = document.getElementById("content-area");
  area.innerHTML = "";
  const cols = ["A", "B", "C", "D", "E", "F"];
  const head = '<thead><tr><th class="xlsx-corner"></th>' +
    cols.map((c) => `<th>${c}</th>`).join("") + "</tr></thead>";
  let body = "<tbody>";
  for (let r = 1; r <= 6; r++) {
    body += `<tr><th class="xlsx-rownum">${r}</th>` +
      cols.map((c) => `<td data-cell="${c}${r}">${c}${r}</td>`).join("") + "</tr>";
  }
  body += "</tbody>";
  const html = `<div class="csv-table-wrapper"><table class="csv-table xlsx-table">${head}${body}</table></div>`;
  renderXlsxViewer(area, {
    vault: "V", path: "data.xlsx", is_xlsx: true,
    xlsx_sheets: [{ name: "Grille", html, rows: 6, cols: 6, total_rows: 6, total_cols: 6, max_rows: 500, max_cols: 40, truncated: false }],
    xlsx_lossy_features: [],
  });
  return area;
};

/**
 * Dispatch a copy/cut/paste event carrying a plain-text payload (JSDOM has no
 * real clipboard, so `clipboardData` is a minimal stub).
 */
const clipboardEvent = (type, area, ref, text) => {
  // A leftover document selection would make the handler think the user is
  // editing a cell: start from a clean selection.
  const sel = w.getSelection();
  if (sel && sel.removeAllRanges) sel.removeAllRanges();
  const td = area.querySelector(`td[data-cell="${ref}"]`);
  assert.ok(td, `cell ${ref} exists`);
  const store = { text };
  const ev = new w.Event(type, { bubbles: true, cancelable: true });
  Object.defineProperty(ev, "clipboardData", {
    value: {
      getData: (kind) => (kind === "text/plain" || kind === "text" ? store.text : ""),
      setData: (kind, value) => {
        if (kind === "text/plain" || kind === "text") store.text = value;
      },
    },
  });
  td.dispatchEvent(ev);
  return { td, store, ev };
};

/** Select A1:B2 by dragging (the gesture the grid listens to). */
const dragSelect = (area, from, to) => {
  area.querySelector(`td[data-cell="${from}"]`).dispatchEvent(new w.MouseEvent("mousedown", { bubbles: true, button: 0 }));
  area.querySelector(`td[data-cell="${to}"]`).dispatchEvent(new w.MouseEvent("mouseover", { bubbles: true, button: 0 }));
  area.querySelector(".xlsx-viewer").dispatchEvent(new w.MouseEvent("mouseup", { bubbles: true }));
};

const selectedRefs = (area) =>
  [...area.querySelectorAll("td.xlsx-selected")].map((td) => td.dataset.cell).sort();

const cellTexts = (area, refs) => refs.map((r) => area.querySelector(`td[data-cell="${r}"]`).textContent);

const toastCount = () => document.querySelectorAll("#toast-container .toast").length;

const pressKey = (target, key, mods = {}) =>
  target.dispatchEvent(new w.KeyboardEvent("keydown", { key, bubbles: true, ...mods }));

// ── A5 — presse-papiers de plage ────────────────────────────────────────────

await test("copying a range exports TSV and pasting it fills the target block", () => {
  const area = mountWide();
  dragSelect(area, "A1", "B2");
  const copied = clipboardEvent("copy", area, "A1", "");
  assert.equal(copied.ev.defaultPrevented, true, "the range copy is handled by the grid");
  assert.equal(copied.store.text, "A1\tB1\nA2\tB2", "TSV block on the clipboard");

  const pasted = clipboardEvent("paste", area, "D5", copied.store.text);
  assert.equal(pasted.ev.defaultPrevented, true);
  assert.deepEqual(cellTexts(area, ["D5", "E5", "D6", "E6"]), ["A1", "B1", "A2", "B2"]);
  assert.equal(area.querySelector('td[data-cell="F6"]').textContent, "F6", "the rest of the grid is untouched");
  assert.equal(area.querySelector("#xlsx-active-cell").value, "D5:E6", "the pasted block stays selected");
  assert.deepEqual(selectedRefs(area), ["D5", "D6", "E5", "E6"]);
  assert.equal(area.querySelector("#xlsx-save-btn").disabled, false, "the paste is unsaved work");
});

await test("a block copied in Excel pastes as a range (trailing newline ignored)", () => {
  const area = mountWide();
  clipboardEvent("paste", area, "A3", "1\t2\n3\t4\n");
  assert.deepEqual(cellTexts(area, ["A3", "B3", "A4", "B4"]), ["1", "2", "3", "4"]);
  assert.equal(area.querySelector('td[data-cell="A5"]').textContent, "A5", "no phantom fifth row");
});

await test("a single-cell paste replaces the cell content", () => {
  const area = mountWide();
  const td = area.querySelector('td[data-cell="C4"]');
  td.focus();
  clipboardEvent("paste", area, "C4", "remplacé");
  assert.equal(td.textContent, "remplacé");
  assert.equal(td.classList.contains("xlsx-dirty"), true);
  assert.equal(area.querySelector("#xlsx-formula-input").value, "remplacé", "the bar follows");
});

await test("cutting a range clears the source once it is pasted elsewhere", () => {
  const area = mountWide();
  area.querySelector("th.xlsx-rownum").click(); // row 1 of a 6-column grid
  const cut = clipboardEvent("cut", area, "A1", "");
  // #184 — the row selection spans A → Z, so the clipboard carries 26 cells.
  assert.equal(cut.store.text,
    ["A1", "B1", "C1", "D1", "E1", "F1", ...Array(20).fill("")].join("\t"));
  clipboardEvent("paste", area, "A5", cut.store.text);
  assert.deepEqual(cellTexts(area, ["A5", "B5"]), ["A1", "B1"]);
  assert.equal(area.querySelector('td[data-cell="A1"]').textContent, "", "the cut source is cleared");
  assert.equal(area.querySelector('td[data-cell="B1"]').textContent, "");
});

await test("cut then paste in place keeps the pasted values", () => {
  const area = mountWide();
  area.querySelector("th.xlsx-rownum").click(); // A1:F1
  const cut = clipboardEvent("cut", area, "A1", "");
  clipboardEvent("paste", area, "A1", cut.store.text);
  assert.deepEqual(cellTexts(area, ["A1", "B1"]), ["A1", "B1"], "no self-erase");
});

await test("the context menu copies and pastes a block", () => {
  const area = mountWide();
  openMenuOn(area, "A1");
  assert.ok(menuItem(FR["xlsx.copy"]), "Copier");
  assert.ok(menuItem(FR["xlsx.cut"]), "Couper");
  assert.ok(menuItem(FR["xlsx.paste"]), "Coller");
  area.querySelector("th.xlsx-rownum").click();
  openMenuOn(area, "A1");
  menuItem(FR["xlsx.copy"]).click();
  openMenuOn(area, "A4");
  menuItem(FR["xlsx.paste"]).click();
  assert.equal(area.querySelector('td[data-cell="A4"]').textContent, "A1");
  assert.equal(area.querySelector('td[data-cell="B4"]').textContent, "B1");
});

await test("pasting with a stale clipboard is a no-op with a hint", async () => {
  const area = mountWide();
  const before = toastCount();
  openMenuOn(area, "A1");
  menuItem(FR["xlsx.paste"]).click();
  await new Promise((r) => setTimeout(r, 5));
  assert.equal(toastCount(), before + 1, "the user is told the clipboard is unusable");
  assert.equal(area.querySelector('td[data-cell="A1"]').textContent, "A1", "nothing was written");
});

await test("a block that overflows the rendered grid is trimmed and signalled", () => {
  const area = mountWide();
  const before = toastCount();
  clipboardEvent("paste", area, "E6", "1\t2\t3\n4\t5\t6");
  assert.deepEqual(cellTexts(area, ["E6", "F6"]), ["1", "2"]);
  assert.equal(area.querySelector('td[data-cell="F7"]'), null);
  assert.equal(toastCount(), before + 1, "the overflow is announced");
});

await test("a pasted block is undoable cell by cell", () => {
  const area = mountWide();
  clipboardEvent("paste", area, "D5", "x\ty");
  area.querySelector("#xlsx-undo-btn").click();
  assert.equal(area.querySelector('td[data-cell="E5"]').textContent, "E5", "the last pasted cell is back");
  area.querySelector("#xlsx-undo-btn").click();
  assert.equal(area.querySelector('td[data-cell="D5"]').textContent, "D5");
});

await test("the cached-value shadow never leaks into a copied block", () => {
  const area = document.getElementById("content-area");
  area.innerHTML = "";
  const html = '<div class="csv-table-wrapper"><table class="csv-table xlsx-table">' +
    '<thead><tr><th class="xlsx-corner"></th><th>A</th></tr></thead><tbody>' +
    '<tr><th class="xlsx-rownum">1</th><td data-cell="A1">=SOMME(B1:B2)' +
    '<span class="xlsx-cached" data-cached-value="42">42</span></td></tr>' +
    "</tbody></table></div>";
  renderXlsxViewer(area, {
    vault: "V", path: "data.xlsx", is_xlsx: true,
    xlsx_sheets: [{ name: "F", html }],
    xlsx_lossy_features: [],
  });
  area.querySelector('td[data-cell="A1"]').focus();
  const copied = clipboardEvent("copy", area, "A1", "");
  assert.equal(copied.store.text, "=SOMME(B1:B2)", "the formula, not Excel's shadow value");
});

// ── A6 — clavier complet ────────────────────────────────────────────────────

await test("Ctrl+S saves the pending edits", async () => {
  const area = mountGrid();
  editCell(area, "A1", "Ctrl+S");
  pressKey(area.querySelector(".xlsx-viewer"), "s", { ctrlKey: true });
  await new Promise((r) => setTimeout(r, 5));
  assert.equal(calls.length, 1, "one save request");
  assert.match(calls[0].url, /\/xlsx\/save/);
  assert.deepEqual(calls[0].body.cells, { A1: "Ctrl+S" });
});

await test("Suppr clears the selected range and stays undoable", () => {
  const area = mountGrid();
  area.querySelector("th.xlsx-rownum").click(); // A1:B1
  pressKey(area.querySelector(".xlsx-viewer"), "Delete");
  assert.deepEqual(cellTexts(area, ["A1", "B1"]), ["", ""]);
  assert.equal(area.querySelector('td[data-cell="A2"]').textContent, "Abricot", "only the selection");
  area.querySelector("#xlsx-undo-btn").click();
  assert.equal(area.querySelector('td[data-cell="B1"]').textContent, "3");
  area.querySelector("#xlsx-undo-btn").click();
  assert.equal(area.querySelector('td[data-cell="A1"]').textContent, "Banane");
});

await test("Suppr while typing in a cell deletes text instead of the selection", () => {
  const area = mountGrid();
  const td = area.querySelector('td[data-cell="A1"]');
  td.focus();
  // #181-A1 — the first keystroke enters edit mode AND replaces the value, so
  // start from a cell whose content survives (A2), then type over it.
  const td2 = area.querySelector('td[data-cell="A2"]');
  td2.focus();
  pressKey(td2, "x");
  assert.equal(td2.dataset.editing, "1", "typing enters edit mode");
  // JSDOM does not insert the typed character (no browser engine), so put a
  // value in place to check that Suppr stays a caret key instead of clearing.
  td2.textContent = "Abricot";
  pressKey(td2, "Delete");
  assert.equal(td2.textContent, "Abricot", "the caret key was not hijacked");
  assert.equal(area.querySelectorAll("td.xlsx-selected").length, 1, "the selection was not cleared");
});

await test("F2 enters the cell for text editing", () => {
  const area = mountGrid();
  const td = area.querySelector('td[data-cell="A1"]');
  td.focus();
  assert.equal(td.dataset.editing, "", "navigation mode after a plain focus");
  pressKey(td, "F2");
  assert.equal(td.dataset.editing, "1");
});

await test("Maj+Entrée inserts a line break inside the cell", () => {
  const area = mountGrid();
  const td = area.querySelector('td[data-cell="A1"]');
  td.focus();
  pressKey(td, "Enter", { shiftKey: true });
  assert.ok(td.textContent.includes("\n"), `expected a newline in ${JSON.stringify(td.textContent)}`);
  assert.equal(td.classList.contains("xlsx-dirty"), true);
});

await test("Ctrl+A selects the rendered sheet, Ctrl+Home/End walk its corners", () => {
  const area = mountGrid();
  const viewer = area.querySelector(".xlsx-viewer");
  area.querySelector('td[data-cell="B2"]').focus();
  pressKey(viewer, "a", { ctrlKey: true });
  // #184 — "the rendered sheet" is the laid-out one: 26 columns × 3 rows.
  assert.equal(area.querySelector("#xlsx-active-cell").value, "A1:Z3");
  assert.equal(area.querySelectorAll("td.xlsx-selected").length, 78);
  pressKey(viewer, "Home", { ctrlKey: true });
  assert.equal(area.querySelector("#xlsx-active-cell").value, "A1");
  pressKey(viewer, "End", { ctrlKey: true });
  assert.equal(area.querySelector("#xlsx-active-cell").value, "Z3");
});

await test("Home/End reach the row edges, Ctrl+flèches the data run", () => {
  const area = mountWide();
  const viewer = area.querySelector(".xlsx-viewer");
  area.querySelector('td[data-cell="C3"]').focus();
  pressKey(viewer, "End");
  assert.equal(area.querySelector("#xlsx-active-cell").value, "Z3");
  pressKey(viewer, "Home");
  assert.equal(area.querySelector("#xlsx-active-cell").value, "A3");
  pressKey(viewer, "ArrowDown", { ctrlKey: true });
  assert.equal(area.querySelector("#xlsx-active-cell").value, "A6", "end of the filled column");
});

await test("PageDown/PageUp move by a screenful", () => {
  const area = mountWide();
  const viewer = area.querySelector(".xlsx-viewer");
  area.querySelector('td[data-cell="A1"]').focus();
  pressKey(viewer, "PageDown");
  assert.equal(area.querySelector("#xlsx-active-cell").value, "A6", "clamped to the last rendered row");
  pressKey(viewer, "PageUp");
  assert.equal(area.querySelector("#xlsx-active-cell").value, "A1");
});

// ── A7 — zone Nom éditable & aide à la saisie ──────────────────────────────

await test("typing a reference in the Name Box activates that cell", () => {
  const area = mountGrid();
  const box = area.querySelector("#xlsx-active-cell");
  box.value = "B3";
  pressKey(box, "Enter");
  assert.equal(box.value, "B3");
  assert.equal(area.querySelector('td[data-cell="B3"]').classList.contains("xlsx-active"), true);
  assert.equal(area.querySelector("#xlsx-formula-input").value, "2");
});

await test("the Name Box accepts a range and normalises it", () => {
  const area = mountGrid();
  const box = area.querySelector("#xlsx-active-cell");
  box.value = "$a1:$b2";
  pressKey(box, "Enter");
  assert.equal(box.value, "A1:B2");
  assert.deepEqual(selectedRefs(area), ["A1", "A2", "B1", "B2"]);
});

await test("an unknown reference is refused and the previous address restored", () => {
  const area = mountGrid();
  area.querySelector('td[data-cell="A1"]').focus();
  const box = area.querySelector("#xlsx-active-cell");
  const before = toastCount();
  box.value = "XYZ";
  pressKey(box, "Enter");
  assert.equal(box.value, "A1");
  assert.equal(toastCount(), before + 1, "the user is warned");
});

await test("a reference naming another sheet switches tab", () => {
  const area = document.getElementById("content-area");
  area.innerHTML = "";
  const other = '<div class="csv-table-wrapper"><table class="csv-table xlsx-table">' +
    '<thead><tr><th class="xlsx-corner"></th><th>A</th></tr></thead><tbody>' +
    '<tr><th class="xlsx-rownum">1</th><td data-cell="A1">Autre</td></tr></tbody></table></div>';
  renderXlsxViewer(area, {
    vault: "V", path: "data.xlsx", is_xlsx: true,
    xlsx_sheets: [
      { name: "Feuille1", html: sheetHtml("100") },
      { name: "Autre", html: other },
    ],
    xlsx_lossy_features: [],
  });
  const box = area.querySelector("#xlsx-active-cell");
  box.value = "Autre!A1";
  pressKey(box, "Enter");
  assert.equal(box.value, "A1");
  assert.equal(area.querySelector('.xlsx-tab[data-sheet="1"]').classList.contains("active"), true);
  assert.equal(area.querySelector('.xlsx-panel[data-sheet="1"]').style.display, "", "the target sheet is shown");
});

await test("the formula bar offers function-name suggestions", () => {
  const area = mountGrid();
  const list = area.querySelector("#xlsx-function-list");
  assert.ok(list, "the datalist exists");
  const names = [...list.querySelectorAll("option")].map((o) => o.value);
  assert.ok(names.length >= 10, `expected a list of functions, got ${names.length}`);
  assert.ok(names.includes("SOMME"), names.join(","));
  assert.equal(area.querySelector("#xlsx-formula-input").getAttribute("list"), "xlsx-function-list");
});

// ── #156-A9 — calcul : pas de moteur, l'écart est annoncé ───────────────────

await test("saving a value starting with '=' warns it is stored as text (#156-A9)", async () => {
  const area = mountGrid();
  const before = toastCount();
  editCell(area, "A1", "=B1*2");
  area.querySelector("#xlsx-save-btn").click();
  await new Promise((r) => setTimeout(r, 5));
  assert.equal(calls.length, 1, "the save still goes out");
  assert.equal(calls[0].body.allow_formula, false);
  const added = [...document.querySelectorAll("#toast-container .toast")].slice(before);
  assert.ok(added.some((n) => n.textContent.includes("f(x")), "the f(x) hint is shown");
});

// ── #156-A10 — undo/redo unifié ─────────────────────────────────────────────

await test("undo replays an edit, a clear, then a sort (#156-A10)", () => {
  const area = mountGrid();
  const undoBtn = area.querySelector("#xlsx-undo-btn");
  const colA = () => [...area.querySelectorAll('td[data-cell^="A"]')].map((td) => td.textContent);

  const a1 = area.querySelector('td[data-cell="A1"]');
  a1.focus();
  editCell(area, "A1", "modifié");
  a1.dispatchEvent(new w.Event("blur"));
  area.querySelector("th.xlsx-rownum").click();
  pressKey(area.querySelector(".xlsx-viewer"), "Delete");
  openMenuOn(area, "B1");
  menuItem(FR["xlsx.sort_desc"]).click();
  assert.deepEqual(colA(), ["Cerise", "Abricot", ""], "sorted descending with the cleared row last");

  undoBtn.click(); // the sort
  assert.deepEqual(colA(), ["", "Abricot", "Cerise"], "the row order is restored");
  undoBtn.click(); // the clear (B1)
  assert.equal(area.querySelector('td[data-cell="B1"]').textContent, "3");
  undoBtn.click(); // the clear (A1)
  assert.equal(area.querySelector('td[data-cell="A1"]').textContent, "modifié");
  undoBtn.click(); // the edit
  assert.equal(area.querySelector('td[data-cell="A1"]').textContent, "Banane");
  assert.equal(undoBtn.disabled, true, "the whole history is consumed");
});

await test("a row insertion is undoable through the structure endpoint (#156-A10)", async () => {
  const area = mountGrid();
  apiQueue.push({ ok: true, status: 200, body: { status: "ok" } }); // insert PUT
  apiQueue.push({ ok: true, status: 200, body: { status: "ok" } }); // undo PUT
  openMenuOn(area, "A2");
  menuItem(FR["xlsx.insert_row_above"]).click();
  await new Promise((r) => setTimeout(r, 10));
  assert.deepEqual(calls[0].body.actions, [{ op: "row_insert", sheet: "Fruits", at: 2, count: 1 }]);
  assert.equal(area.querySelector("#xlsx-undo-btn").disabled, false, "the reversible batch is recorded");
  assert.equal(area.querySelector('td[data-cell="A3"]').textContent, "Abricot", "the row shifts down locally");

  area.querySelector("#xlsx-undo-btn").click();
  await new Promise((r) => setTimeout(r, 10));
  assert.match(calls[1].url, /\/xlsx\/structure/);
  assert.deepEqual(calls[1].body.actions, [{ op: "row_delete", sheet: "Fruits", at: 2, count: 1 }], "the inverse batch");
  assert.equal(area.querySelector('td[data-cell="A2"]').textContent, "Abricot", "undo restores the rows locally");
});

await test("a destructive structure action is not undoable (#156-A10)", async () => {
  const area = mountGrid();
  apiQueue.push({ ok: true, status: 200, body: { status: "ok" } });
  apiQueue.push({
    ok: true, status: 200,
    body: { is_xlsx: true, vault: "V", path: "data.xlsx", xlsx_sheets: [{ name: "Fruits", html: sheetHtml("100") }], xlsx_lossy_features: [] },
  });
  openMenuOn(area, "A2");
  menuItem(FR["xlsx.delete_rows"].replace("{n}", "1")).click();
  clickDialog("confirm");
  await new Promise((r) => setTimeout(r, 10));
  assert.deepEqual(calls[0].body.actions, [{ op: "row_delete", sheet: "Fruits", at: 2, count: 1 }]);
  assert.equal(area.querySelector("#xlsx-undo-btn").disabled, true, "a delete cannot be replayed");
});

await test("the filter is recorded once and cleared by a single undo (#156-A10)", () => {
  const area = mountGrid();
  const input = area.querySelector("#xlsx-find-input");
  const hidden = () => [...area.querySelectorAll("tbody tr")].filter((tr) => tr.style.display === "none").length;
  input.value = "ab";
  input.dispatchEvent(new w.Event("input", { bubbles: true }));
  input.value = "abr";
  input.dispatchEvent(new w.Event("input", { bubbles: true }));
  assert.equal(hidden(), 2, "two rows are hidden");
  area.querySelector("#xlsx-undo-btn").click();
  assert.equal(hidden(), 0, "one undo clears the whole typing session");
  assert.equal(input.value, "", "the field follows");
});

// ── #156-A12 — concurrence optimiste (ETag / If-Match) ──────────────────────

await test("a save carries the read revision and refreshes it from the response (#156-A12)", async () => {
  const area = mount({ revision: "rev-1" });
  editCell(area, "A1", "42");
  apiQueue.push({ ok: true, status: 200, body: { status: "ok", revision: "rev-2" } });
  area.querySelector("#xlsx-save-btn").click();
  await new Promise((r) => setTimeout(r, 5));
  assert.equal(calls[0].body.if_match, "rev-1", "the read token is sent back");

  // A second save chains on the revision the first response returned.
  editCell(area, "A2", "43");
  apiQueue.push({ ok: true, status: 200, body: { status: "ok", revision: "rev-3" } });
  area.querySelector("#xlsx-save-btn").click();
  await new Promise((r) => setTimeout(r, 5));
  assert.equal(calls[1].body.if_match, "rev-2");
});

await test("a stale revision is repaired by re-reading before the retry (#156-A12)", async () => {
  const area = mount({ revision: "rev-1" });
  editCell(area, "A1", "42");
  // 1) the save is refused as stale, 2) the retry re-reads the file, 3) the
  // re-send succeeds — all queued up front so the re-read really answers.
  apiQueue.push({
    ok: false, status: 409,
    body: { detail: "changed on disk", code: "conflict", details: { reason: "stale_revision", current_revision: "rev-9" } },
  });
  area.querySelector("#xlsx-save-btn").click();
  await new Promise((r) => setTimeout(r, 10));
  const banner = area.querySelector(".xlsx-banner-conflict");
  assert.ok(banner, "the conflict banner is shown");
  assert.equal(calls[0].body.if_match, "rev-1");

  // The retry re-reads the file, then re-sends with the CURRENT revision.
  apiQueue.push({
    ok: true, status: 200,
    body: { is_xlsx: true, vault: "V", path: "data.xlsx", xlsx_revision: "rev-9", xlsx_sheets: [{ name: "Feuille1", html: sheetHtml("100") }], xlsx_lossy_features: [] },
  });
  apiQueue.push({ ok: true, status: 200, body: { status: "ok", revision: "rev-10" } });
  banner.querySelector(".xlsx-banner-retry").click();
  await new Promise((r) => setTimeout(r, 10));
  assert.match(calls[1].url, /\/api\/file\/V\?path=data\.xlsx/);
  assert.equal(calls[2].body.if_match, "rev-9", "the retry uses the revision just read");
  assert.equal(area.querySelectorAll("td.xlsx-dirty").length, 0);
});

await test("a structural write carries the revision too (#156-A12)", async () => {
  const area = mountGrid("rev-1");
  apiQueue.push({ ok: true, status: 200, body: { status: "ok", revision: "rev-2" } });
  apiQueue.push({
    ok: true, status: 200,
    body: { is_xlsx: true, vault: "V", path: "data.xlsx", xlsx_sheets: [{ name: "Fruits", html: sheetHtml("100") }], xlsx_lossy_features: [] },
  });
  openMenuOn(area, "A2");
  menuItem(FR["xlsx.insert_row_above"]).click();
  await new Promise((r) => setTimeout(r, 10));
  assert.equal(calls[0].body.if_match, "rev-1");
});

// ── #156-A8 — mise en forme en écriture ────────────────────────────────────

/**
 * Make `ref` the ACTIVE cell: the grid wires setActiveCell() on the cell's
 * focus event, and td.focus() runs it in JSDOM (a synthetic click would not).
 */
const activateCell = (area, ref) => {
  const td = area.querySelector(`td[data-cell="${ref}"]`);
  td.focus();
  return td;
};

const openFormatMenu = (area) => {
  area.querySelector("#xlsx-format-btn").click();
  return area.querySelector(".xlsx-format-menu");
};

const formatItem = (area, label) => {
  const b = [...area.querySelectorAll(".xlsx-format-menu .xlsx-structure-item")]
    .find((item) => item.textContent === label);
  assert.ok(b, `format item "${label}" is present`);
  return b;
};

/** Queue the single PUT the formatting flow performs (applied locally). */
const queueStyleWrite = () => {
  apiQueue.push({ ok: true, status: 200, body: { status: "ok", revision: "rev-2" } });
};

await test("the formatting menu is offered on an editable workbook only (#156-A8)", () => {
  const area = mountGrid();
  assert.ok(area.querySelector("#xlsx-format-btn"), "the button is there");
  assert.equal(area.querySelector("#xlsx-format-btn").getAttribute("title"), FR["xlsx.format_btn"]);
  const csv = mountCsv();
  assert.equal(csv.querySelector("#xlsx-format-btn"), null, "no formatting on a csv");
});

await test("bold applied to a selection goes to /xlsx/style and paints locally (#156-A8)", async () => {
  const area = mountGrid();
  dragSelect(area, "A1", "B2");
  openFormatMenu(area);
  queueStyleWrite();
  formatItem(area, FR["xlsx.format_bold"]).click();
  await new Promise((r) => setTimeout(r, 10));
  assert.equal(calls.length, 1, "one locked rewrite, painted locally without re-read");
  assert.match(calls[0].url, /\/xlsx\/style\?path=data\.xlsx/);
  assert.deepEqual(calls[0].body.ops, [
    { op: "cell", sheet: "Fruits", range: "A1:B2", style: { bold: true } },
  ]);
  assert.equal(calls[0].body.force, false);
  for (const ref of ["A1", "B2"]) {
    assert.equal(
      area.querySelector(`td[data-cell="${ref}"]`).style.fontWeight,
      "600",
      `${ref} is bold on screen`,
    );
  }
});

await test("the bold toggle removes an existing bold (#156-A8)", async () => {
  const area = mountGrid();
  activateCell(area, "A1").style.fontWeight = "600";
  openFormatMenu(area);
  queueStyleWrite();
  formatItem(area, FR["xlsx.format_bold"]).click();
  await new Promise((r) => setTimeout(r, 10));
  assert.deepEqual(calls[0].body.ops, [
    { op: "cell", sheet: "Fruits", range: "A1", style: { bold: false } },
  ]);
});

await test("alignment, underline and number formats are written (#156-A8)", async () => {
  const area = mountGrid();
  activateCell(area, "B2");
  openFormatMenu(area);
  queueStyleWrite();
  formatItem(area, FR["xlsx.format_align_center"]).click();
  await new Promise((r) => setTimeout(r, 10));
  assert.deepEqual(calls[0].body.ops[0].style, { align: "center" });

  // `calls` keeps accumulating (same test): the second write is calls[1].
  const area2 = mountGrid();
  activateCell(area2, "B2");
  openFormatMenu(area2);
  queueStyleWrite();
  formatItem(area2, FR["xlsx.format_num_percent"]).click();
  await new Promise((r) => setTimeout(r, 10));
  assert.equal(calls.length, 2, "one PUT per formatting write");
  assert.deepEqual(calls[1].body.ops[0].style, { number_format: "0.0%" });
  assert.equal(calls[1].body.ops[0].range, "B2");
});

await test("merging needs a real range (#156-A8)", async () => {
  const area = mountGrid();
  activateCell(area, "A1");
  const before = toastCount();
  openFormatMenu(area);
  formatItem(area, FR["xlsx.format_merge"]).click();
  await new Promise((r) => setTimeout(r, 5));
  assert.equal(calls.length, 0, "nothing is written for a single cell");
  assert.equal(toastCount(), before + 1, "the user is told why");
});

await test("the merge action carries the selected range (#156-A8)", async () => {
  const area = mountGrid();
  dragSelect(area, "A1", "B1");
  openFormatMenu(area);
  queueStyleWrite();
  formatItem(area, FR["xlsx.format_merge"]).click();
  await new Promise((r) => setTimeout(r, 10));
  assert.deepEqual(calls[0].body.ops, [{ op: "merge", sheet: "Fruits", range: "A1:B1" }]);
});

await test("freezing uses the active cell, unfreezing sends an empty one (#156-A8)", async () => {
  const area = mountGrid();
  activateCell(area, "B3");
  openFormatMenu(area);
  queueStyleWrite();
  formatItem(area, FR["xlsx.format_freeze"]).click();
  await new Promise((r) => setTimeout(r, 10));
  assert.deepEqual(calls[0].body.ops, [{ op: "freeze", sheet: "Fruits", cell: "B3" }]);

  const area2 = mountGrid();
  openFormatMenu(area2);
  queueStyleWrite();
  formatItem(area2, FR["xlsx.format_unfreeze"]).click();
  await new Promise((r) => setTimeout(r, 10));
  assert.deepEqual(calls[1].body.ops, [{ op: "freeze", sheet: "Fruits", cell: "" }]);
});

await test("the column width prompt writes one op per selected column (#156-A8)", async () => {
  const area = mountGrid();
  dragSelect(area, "A1", "B2");
  openFormatMenu(area);
  formatItem(area, FR["xlsx.format_col_width"]).click();
  await new Promise((r) => setTimeout(r, 5));
  const input = lastDialog()?.querySelector('[data-dialog="input"]');
  assert.ok(input, "a themed prompt asks for the width");
  input.value = "30";
  clickDialog("confirm");
  queueStyleWrite();
  await new Promise((r) => setTimeout(r, 10));
  assert.deepEqual(calls[0].body.ops, [
    { op: "col_width", sheet: "Fruits", col: "A", width: 30 },
    { op: "col_width", sheet: "Fruits", col: "B", width: 30 },
  ]);
});

await test("a lossy refusal asks once then retries the style with force (#156-A8)", async () => {
  const area = mount({ lossy: ["cached_values"] });
  activateCell(area, "A1");
  openFormatMenu(area);
  apiQueue.push(lossyError); // 1) the style write is refused as lossy
  apiQueue.push({ ok: true, status: 200, body: { status: "ok", revision: "rev-2" } }); // 2) forced retry
  apiQueue.push({
    ok: true, status: 200,
    body: {
      is_xlsx: true, vault: "V", path: "data.xlsx",
      xlsx_sheets: [{ name: "Feuille1", html: sheetHtml("100") }],
      xlsx_lossy_features: [],
    },
  }); // 3) the re-read
  formatItem(area, FR["xlsx.format_italic"]).click();
  await new Promise((r) => setTimeout(r, 10));
  assert.equal(calls.length, 1, "nothing is retried before the answer");
  const dialog = lastDialog();
  assert.ok(dialog, "a themed confirmation is shown");
  // The 409 payload lists the features (slicers), not the mount's list.
  assert.ok(dialog.textContent.includes(FR["xlsx.feature_slicers"]), dialog.textContent);
  clickDialog("confirm");
  await new Promise((r) => setTimeout(r, 10));
  const retry = calls.find((c) => c.body && c.body.force === true);
  assert.ok(retry, "the retry goes out with force");
  assert.match(retry.url, /\/xlsx\/style\?path=data\.xlsx/);
});

// ── #156-A11 — sortie (CSV/MD/HTML/impression) + recherche classeur ────────

/**
 * Click an export control and capture the file name and the payload it built.
 * The real <a>/Blob constructors are swapped out because JSDOM has neither
 * downloads nor object URLs.
 */
const captureExport = (area, selector) => {
  const realCreate = document.createElement.bind(document);
  const realBlob = globalThis.Blob;
  const anchor = realCreate("a");
  let href = "";
  let text = "";
  Object.defineProperty(anchor, "href", { set(v) { href = v; }, get: () => href });
  document.createElement = (tag) => (tag === "a" ? anchor : realCreate(tag));
  URL.createObjectURL = () => "blob:x";
  URL.revokeObjectURL = () => {};
  globalThis.Blob = class extends realBlob {
    constructor(parts, opts) {
      super(parts, opts);
      text = parts.join("");
    }
  };
  try {
    area.querySelector(selector).click();
  } finally {
    document.createElement = realCreate;
    globalThis.Blob = realBlob;
  }
  return { anchor, text, href };
};

await test("the CSV export carries the selection when a range is active (#156-A11)", () => {
  const area = mountGrid();
  dragSelect(area, "A1", "B2");
  const { anchor, text } = captureExport(area, "#xlsx-csv-btn");
  assert.equal(anchor.download, "Fruits-A1B2.csv", "the file name pins the range");
  assert.equal(text, "\uFEFFBanane;3\nAbricot;10", text);
});

await test("without a selection the export still carries the whole sheet (#156-A11)", () => {
  const area = mountGrid();
  const { anchor, text } = captureExport(area, "#xlsx-csv-btn");
  assert.equal(anchor.download, "Fruits.csv");
  assert.equal(text.split("\n").length, 3, text);
  assert.ok(text.includes("Cerise"), text);
});

await test("the file menu offers Markdown and writes a table (#156-A11, #180-A3)", () => {
  const area = mountGrid();
  area.querySelector("#xlsx-file-btn").click();
  assert.equal(area.querySelector("#xlsx-file-menu").style.display, "block");
  const { anchor, text } = captureExport(area, '[data-xlsx-export="md"]');
  assert.equal(anchor.download, "Fruits.md");
  assert.equal(text.split("\n")[0], "| Banane | 3 |");
  assert.equal(text.split("\n")[1], "|---|---|");
  assert.equal(area.querySelector("#xlsx-file-menu").style.display, "none", "the menu closes");
});

await test("the file menu writes a standalone HTML document (#156-A11)", () => {
  const area = mountGrid();
  area.querySelector("#xlsx-file-btn").click();
  const { anchor, text } = captureExport(area, '[data-xlsx-export="html"]');
  assert.equal(anchor.download, "Fruits.html");
  assert.ok(text.startsWith("<!DOCTYPE html>"), text.slice(0, 40));
  assert.ok(text.includes("<td>Banane</td>"), text);
  assert.ok(text.includes("<td>Cerise</td>"), text);
});

await test("printing builds an off-screen frame and never touches the app chrome (#156-A11)", () => {
  const area = mountGrid();
  area.querySelector("#xlsx-file-btn").click();
  area.querySelector('[data-xlsx-export="print"]').click();
  const frame = document.querySelector("body > iframe.xlsx-print-frame");
  assert.ok(frame, "a print frame is created");
  const doc = frame.contentDocument || (frame.contentWindow && frame.contentWindow.document);
  assert.ok(doc, "the frame carries its own document");
  assert.ok(doc.body.textContent.includes("Banane"), doc.body.textContent);
  assert.equal(document.body.classList.contains("xlsx-print"), false, "the print class is cleared");
  // A second print does not pile frames up.
  area.querySelector("#xlsx-file-btn").click();
  area.querySelector('[data-xlsx-export="print"]').click();
  assert.equal(document.querySelectorAll("body > iframe.xlsx-print-frame").length, 1);
});

await test("the find scans every sheet and steps across tabs (#156-A11)", () => {
  const area = document.getElementById("content-area");
  area.innerHTML = "";
  const oneCell = (value) =>
    '<div class="csv-table-wrapper"><table class="csv-table xlsx-table">' +
    '<thead><tr><th class="xlsx-corner"></th><th>A</th><th>B</th></tr></thead><tbody>' +
    `<tr><th class="xlsx-rownum">1</th><td data-cell="A1">${value}</td><td data-cell="B1">x</td></tr>` +
    "</tbody></table></div>";
  renderXlsxViewer(area, {
    vault: "V", path: "data.xlsx", is_xlsx: true,
    xlsx_sheets: [
      { name: "Un", html: oneCell("pomme") },
      { name: "Deux", html: oneCell("pomme") },
    ],
    xlsx_lossy_features: [],
  });
  const input = area.querySelector("#xlsx-find-input");
  input.value = "pomme";
  input.dispatchEvent(new w.Event("input", { bubbles: true }));
  assert.equal(area.querySelectorAll("mark.xlsx-find-hit").length, 2, "both sheets are scanned");
  assert.ok(area.querySelector("#xlsx-find-count").textContent.includes("2 feuilles"), area.querySelector("#xlsx-find-count").textContent);
  assert.equal(area.querySelector('.xlsx-panel[data-sheet="1"]').style.display, "none", "sheet 2 stays hidden while its hit is not focused");
  area.querySelector("#xlsx-find-next").click();
  assert.equal(area.querySelector('.xlsx-tab[data-sheet="1"]').classList.contains("active"), true, "the tab of the hit is activated");
  assert.equal(area.querySelector('.xlsx-panel[data-sheet="1"]').style.display, "", "the sheet holding the hit is shown");
  assert.equal(area.querySelectorAll("mark.xlsx-find-current").length, 1, "exactly one hit is current");
  assert.ok(area.querySelector('.xlsx-panel[data-sheet="1"] mark.xlsx-find-current'), "the current hit sits in sheet 2");
});

// ── #179 — menu icons + dismissal at viewer level ─────────────────────────
await test("the grid context menu shows one icon per entry (#179-A1)", () => {
  const area = mount();
  const menu = openMenuOn(area, "A1");
  assert.ok(menu, "menu opens on right-click");
  const items = [...menu.querySelectorAll(".xlsx-context-item")];
  assert.ok(items.length > 3, "several entries");
  for (const b of items) {
    const icon = b.querySelector("i[data-lucide]");
    assert.ok(icon, `"${b.textContent}" carries an icon`);
    assert.match(icon.getAttribute("data-lucide"), /^[a-z0-9-]+$/);
  }
  assert.equal(
    menu.querySelector(".xlsx-context-item .xlsx-menu-label").textContent,
    FR["xlsx.cut"]
  );
  const danger = items.find(
    (b) => b.textContent === FR["xlsx.delete_rows"].replace("{n}", "1")
  );
  assert.ok(danger && danger.querySelector('i[data-lucide="trash-2"]'), "delete reads as danger");
});

await test("the grid context menu closes when the focus leaves it (#179-A3)", () => {
  const area = mount();
  openMenuOn(area, "A1");
  assert.ok(document.querySelector(".xlsx-context-menu"), "menu open");
  const first = document.querySelector(".xlsx-context-item");
  const outside = document.createElement("button");
  document.body.appendChild(outside);
  first.dispatchEvent(new w.FocusEvent("focusout", { bubbles: true, relatedTarget: outside }));
  assert.equal(document.querySelector(".xlsx-context-menu"), null, "menu closed on focus loss");
  outside.remove();
});

await test("the structure menu shows icons and closes on outside click (#179-A2/A3)", () => {
  const area = mount();
  area.querySelector("#xlsx-structure-btn").click();
  const menu = area.querySelector(".xlsx-structure-menu");
  assert.ok(menu, "structure menu opens");
  const icons = [...menu.querySelectorAll(".xlsx-structure-item i[data-lucide]")].map((i) =>
    i.getAttribute("data-lucide")
  );
  assert.ok(icons.includes("file-plus"), "sheet add icon");
  assert.ok(icons.includes("trash-2"), "delete icon");
  assert.equal(
    area.querySelector("#xlsx-structure-btn").getAttribute("aria-expanded"),
    "true"
  );
  document.body.dispatchEvent(new w.MouseEvent("click", { bubbles: true }));
  assert.equal(area.querySelector(".xlsx-structure-menu"), null, "outside click closes");
  assert.equal(
    area.querySelector("#xlsx-structure-btn").getAttribute("aria-expanded"),
    "false"
  );
});

await test("the structure and format menus open anchored under their button", () => {
  const area = mount();
  const btn = area.querySelector("#xlsx-structure-btn");
  btn.click();
  const menu = area.querySelector(".xlsx-structure-menu");
  assert.ok(menu, "structure menu opens");
  assert.match(menu.style.top, /px$/, "menu is explicitly positioned vertically");
  assert.match(menu.style.left, /px$/, "menu is explicitly positioned horizontally");
  document.body.dispatchEvent(new w.MouseEvent("click", { bubbles: true }));

  area.querySelector("#xlsx-format-btn").click();
  const fmt = area.querySelector(".xlsx-format-menu");
  assert.ok(fmt, "format menu opens");
  assert.match(fmt.style.top, /px$/, "format menu is explicitly positioned");
  document.body.dispatchEvent(new w.MouseEvent("click", { bubbles: true }));
});

await test("the format menu shows icons and closes on Escape (#179-A2/A3)", () => {
  const area = mount();
  area.querySelector("#xlsx-format-btn").click();
  const menu = area.querySelector(".xlsx-format-menu");
  assert.ok(menu, "format menu opens");
  const find = (icon) => menu.querySelector(`.xlsx-structure-item i[data-lucide="${icon}"]`);
  assert.ok(find("bold"), "bold icon");
  assert.ok(find("align-center"), "align icon");
  assert.ok(menu.querySelector('.xlsx-format-colour i[data-lucide="palette"]'), "font colour icon");
  document.dispatchEvent(new w.KeyboardEvent("keydown", { key: "Escape", bubbles: true }));
  assert.equal(area.querySelector(".xlsx-format-menu"), null, "Escape closes");
});

await test("the file menu closes on outside click (#179-A3, #180-A3)", () => {
  const area = mount();
  area.querySelector("#xlsx-file-btn").click();
  assert.equal(area.querySelector("#xlsx-file-menu").style.display, "block", "menu opens");
  document.body.dispatchEvent(new w.MouseEvent("click", { bubbles: true }));
  assert.equal(area.querySelector("#xlsx-file-menu").style.display, "none", "outside click closes");
});

await test("formulas compute on screen while the bar keeps the raw text", () => {
  const area = document.getElementById("content-area");
  area.innerHTML = "";
  const html = '<div class="csv-table-wrapper"><table class="csv-table xlsx-table">' +
    '<thead><tr><th class="xlsx-corner"></th><th>A</th><th>B</th></tr></thead><tbody>' +
    '<tr><th class="xlsx-rownum">1</th><td data-cell="A1">2</td><td data-cell="B1">3</td></tr>' +
    '<tr><th class="xlsx-rownum">2</th><td data-cell="A2">=A1*10+SOMME(A1:B1)</td><td data-cell="B2">=1/0</td></tr>' +
    "</tbody></table></div>";
  renderXlsxViewer(area, {
    vault: "V", path: "data.xlsx", is_xlsx: true,
    xlsx_sheets: [{ name: "F", html }],
    xlsx_lossy_features: [],
  });
  const a2 = area.querySelector('td[data-cell="A2"]');
  assert.equal(a2.textContent, "25", "2*10 + (2+3)");
  assert.equal(a2.dataset.formula, "=A1*10+SOMME(A1:B1)");
  assert.equal(area.querySelector('td[data-cell="B2"]').textContent, "#DIV/0!");
  // The formula bar shows the raw formula, not the display value.
  a2.focus();
  assert.equal(area.querySelector("#xlsx-formula-input").value, "=A1*10+SOMME(A1:B1)");
});

await test("editing a precedent recomputes its dependents", () => {
  const area = document.getElementById("content-area");
  area.innerHTML = "";
  const html = '<div class="csv-table-wrapper"><table class="csv-table xlsx-table">' +
    '<thead><tr><th class="xlsx-corner"></th><th>A</th></tr></thead><tbody>' +
    '<tr><th class="xlsx-rownum">1</th><td data-cell="A1">1</td></tr>' +
    '<tr><th class="xlsx-rownum">2</th><td data-cell="A2">=A1+1</td></tr>' +
    "</tbody></table></div>";
  renderXlsxViewer(area, {
    vault: "V", path: "data.xlsx", is_xlsx: true,
    xlsx_sheets: [{ name: "F", html }],
    xlsx_lossy_features: [],
  });
  const a2 = area.querySelector('td[data-cell="A2"]');
  assert.equal(a2.textContent, "2");
  const a1 = area.querySelector('td[data-cell="A1"]');
  a1.textContent = "10";
  a1.dispatchEvent(new w.Event("input", { bubbles: true }));
  a1.blur();
  assert.equal(a2.textContent, "11", "dependent recomputed after commit");
});

await test("saving a formula cell sends the formula, not the display", async () => {
  const area = document.getElementById("content-area");
  area.innerHTML = "";
  const html = '<div class="csv-table-wrapper"><table class="csv-table xlsx-table">' +
    '<thead><tr><th class="xlsx-corner"></th><th>A</th></tr></thead><tbody>' +
    '<tr><th class="xlsx-rownum">1</th><td data-cell="A1">=1+1</td></tr>' +
    "</tbody></table></div>";
  renderXlsxViewer(area, {
    vault: "V", path: "data.xlsx", is_xlsx: true,
    xlsx_sheets: [{ name: "F", html }],
    xlsx_lossy_features: [],
  });
  const a1 = area.querySelector('td[data-cell="A1"]');
  assert.equal(a1.textContent, "2");
  a1.textContent = "3";
  a1.dispatchEvent(new w.Event("input", { bubbles: true }));
  area.querySelector("#xlsx-save-btn").click();
  await new Promise((r) => setTimeout(r, 5));
  assert.match(calls[0].url, /\/xlsx\/save/);
  assert.deepEqual(calls[0].body.cells, { A1: "3" });
  // Now type a formula and save: the payload carries the raw formula.
  a1.textContent = "=2+2";
  a1.dispatchEvent(new w.Event("input", { bubbles: true }));
  a1.blur();
  assert.equal(a1.textContent, "4", "computed on commit");
  area.querySelector("#xlsx-save-btn").click();
  await new Promise((r) => setTimeout(r, 5));
  assert.deepEqual(calls[1].body.cells, { A1: "=2+2" });
});

await test("an unknown function defers to Excel's cached shadow", () => {
  const area = document.getElementById("content-area");
  area.innerHTML = "";
  const html = '<div class="csv-table-wrapper"><table class="csv-table xlsx-table">' +
    '<thead><tr><th class="xlsx-corner"></th><th>A</th></tr></thead><tbody>' +
    '<tr><th class="xlsx-rownum">1</th><td data-cell="A1">=RECHERCHEV(A2;A:B)' +
    '<span class="xlsx-cached" data-cached-value="1">42</span></td></tr>' +
    "</tbody></table></div>";
  renderXlsxViewer(area, {
    vault: "V", path: "data.xlsx", is_xlsx: true,
    xlsx_sheets: [{ name: "F", html }],
    xlsx_lossy_features: [],
  });
  const a1 = area.querySelector('td[data-cell="A1"]');
  assert.ok(a1.querySelector(".xlsx-cached"), "shadow kept instead of #NOM?");
  assert.equal(a1.dataset.formula, undefined, "not engine-managed");
});

await test("=LIEN renders a real anchor and comments show a marker", () => {
  const area = document.getElementById("content-area");
  area.innerHTML = "";
  const html = '<div class="csv-table-wrapper"><table class="csv-table xlsx-table">' +
    '<thead><tr><th class="xlsx-corner"></th><th>A</th></tr></thead><tbody>' +
    '<tr><th class="xlsx-rownum">1</th><td data-cell="A1">=LIEN("https://x.test";"ici")</td></tr>' +
    '<tr><th class="xlsx-rownum">2</th><td data-cell="A2">note</td></tr>' +
    "</tbody></table></div>";
  renderXlsxViewer(area, {
    vault: "V", path: "data.xlsx", is_xlsx: true,
    xlsx_sheets: [{ name: "F", html, comments: { A2: "à relire" } }],
    xlsx_lossy_features: [],
  });
  const a = area.querySelector('td[data-cell="A1"] a.xlsx-formula-link');
  assert.ok(a, "anchor rendered");
  assert.equal(a.getAttribute("href"), "https://x.test");
  assert.equal(a.textContent, "ici");
  const dot = area.querySelector('td[data-cell="A2"] .xlsx-comment-dot');
  assert.ok(dot, "comment marker rendered");
  assert.equal(dot.title, "à relire");
});

await test("the ribbon save and tab + buttons carry icons (#179-A2)", () => {
  const area = mount();
  assert.equal(
    area.querySelector('#xlsx-save-btn i[data-lucide]').getAttribute("data-lucide"),
    "save"
  );
  assert.equal(
    area.querySelector('#xlsx-tab-add i[data-lucide]').getAttribute("data-lucide"),
    "plus"
  );
});

// ── Sheets-like ribbon ────────────────────────────────────────────────────
await test("the ribbon exposes the Sheets controls with stable ids", () => {
  const area = mountGrid();
  for (const id of [
    "xs-search", "xs-print", "xs-paint", "xs-zoom", "xs-cur", "xs-pct",
    "xs-decminus", "xs-decplus", "xs-num123", "xs-numfmt", "xs-fsminus",
    "xs-fssize", "xs-fsplus", "xs-b", "xs-i", "xs-s", "xs-u", "xs-fgcolor",
    "xs-bgcolor", "xs-borders", "xs-merge", "xs-alignh", "xs-alignv",
    "xs-wrap", "xs-rot", "xs-more", "xlsx-save-btn", "xlsx-formula-btn",
  ]) {
    assert.ok(area.querySelector(`#${id}`), `#${id} present`);
  }
});

await test("the B toggle writes bold for the active cell", async () => {
  const area = mountGrid();
  activateCell(area, "A1");
  queueStyleWrite();
  area.querySelector("#xs-b").click();
  await new Promise((r) => setTimeout(r, 10));
  assert.match(calls[0].url, /\/xlsx\/style\?path=data\.xlsx/);
  assert.deepEqual(calls[0].body.ops, [
    { op: "cell", sheet: "Fruits", range: "A1", style: { bold: true } },
  ]);
});

await test("the borders menu writes all borders", async () => {
  const area = mountGrid();
  activateCell(area, "A1");
  queueStyleWrite();
  area.querySelector("#xs-borders").click();
  const item = [...area.querySelectorAll(".xlsx-structure-item")]
    .find((b) => b.textContent === FR["xlsx.border_all"]);
  assert.ok(item, "border entry present with an icon");
  assert.ok(item.querySelector("i[data-lucide]"), "border entry iconised");
  item.click();
  await new Promise((r) => setTimeout(r, 10));
  assert.deepEqual(calls[0].body.ops, [
    { op: "cell", sheet: "Fruits", range: "A1", style: { border: "all" } },
  ]);
  assert.equal(area.querySelector(".xlsx-structure-menu"), null, "menu dismissed after pick");
});

await test("zoom scales the panels and persists", () => {
  const area = mountGrid();
  const sel = area.querySelector("#xs-zoom");
  sel.value = "150";
  sel.dispatchEvent(new w.Event("change", { bubbles: true }));
  assert.equal(area.querySelector(".xlsx-panels").style.zoom, "150%");
  assert.equal(w.localStorage.getItem("obsigate_xlsx_zoom"), "150");
  sel.value = "100";
  sel.dispatchEvent(new w.Event("change", { bubbles: true }));
  assert.equal(area.querySelector(".xlsx-panels").style.zoom, "");
});

await test("fewer decimals rewrites the number format", async () => {
  const area = document.getElementById("content-area");
  area.innerHTML = "";
  const html = '<div class="csv-table-wrapper"><table class="csv-table xlsx-table">' +
    '<thead><tr><th class="xlsx-corner"></th><th>A</th></tr></thead><tbody>' +
    '<tr><th class="xlsx-rownum">1</th><td data-cell="A1">3.50</td></tr>' +
    "</tbody></table></div>";
  renderXlsxViewer(area, {
    vault: "V", path: "data.xlsx", is_xlsx: true,
    xlsx_sheets: [{ name: "F", html, formats: { A1: "0.00" } }],
    xlsx_lossy_features: [],
  });
  activateCell(area, "A1");
  queueStyleWrite();
  area.querySelector("#xs-decminus").click();
  await new Promise((r) => setTimeout(r, 10));
  assert.deepEqual(calls[0].body.ops, [
    { op: "cell", sheet: "F", range: "A1", style: { number_format: "0.0" } },
  ]);
});

await test("merge fuses the selected range", async () => {
  const area = mountGrid();
  dragSelect(area, "A1", "B2");
  queueStyleWrite();
  area.querySelector("#xs-merge").click();
  await new Promise((r) => setTimeout(r, 10));
  assert.deepEqual(calls[0].body.ops, [
    { op: "merge", sheet: "Fruits", range: "A1:B2" },
  ]);
});

await test("the mini-bar shows on ranges and hides on single cells", () => {
  const area = mountGrid();
  assert.equal(area.querySelector("#xlsx-minibar").hidden, true);
  dragSelect(area, "A1", "B2");
  const bar = area.querySelector("#xlsx-minibar");
  assert.equal(bar.hidden, false, "visible on a range");
  assert.ok(bar.style.left.endsWith("px") && bar.style.top.endsWith("px"), "positioned");
  activateCell(area, "A1");
  assert.equal(area.querySelector("#xlsx-minibar").hidden, true, "hidden on a single cell");
});

await test("paint copies the source style onto the target", async () => {
  const area = mountGrid();
  activateCell(area, "A1").style.fontWeight = "600";
  queueStyleWrite();
  area.querySelector("#xs-paint").click();
  assert.ok(area.querySelector("#xs-paint").classList.contains("active"), "paint armed");
  activateCell(area, "B2");
  area.dispatchEvent(new w.MouseEvent("click", { bubbles: true }));
  await new Promise((r) => setTimeout(r, 10));
  assert.match(calls[0].url, /\/xlsx\/style\?path=data\.xlsx/);
  // #184-A1 — the paint tool carries the source's COMPLETE format (a source
  // with no explicit format resets the target instead of doing nothing).
  assert.deepEqual(calls[0].body.ops, [
    { op: "cell", sheet: "Fruits", range: "B2", style: {
      bold: true, italic: false, underline: false, strike: false,
      font_color: "", fill_color: "", font_size: 11, align: "left",
      wrap: false, rotation: 0, number_format: "General", border: "none",
    } },
  ]);
  assert.ok(!area.querySelector("#xs-paint").classList.contains("active"), "paint disarmed");
});

await test("a comment round-trips through the overflow menu", async () => {
  const area = mountGrid();
  activateCell(area, "B2");
  queueStyleWrite();
  area.querySelector("#xs-more").click();
  const item = [...area.querySelectorAll(".xlsx-structure-item")]
    .find((b) => b.textContent === FR["xlsx.comment_add"]);
  assert.ok(item, "comment entry present");
  item.click();
  await new Promise((r) => setTimeout(r, 5));
  lastDialog().querySelector('[data-dialog="input"]').value = "À relire";
  clickDialog("confirm");
  await new Promise((r) => setTimeout(r, 10));
  assert.deepEqual(calls[0].body.ops, [
    { op: "comment_set", sheet: "Fruits", ref: "B2", text: "À relire" },
  ]);
});

await test("SUM inserts a range total above the active cell", () => {
  const area = mountGrid();
  dragSelect(area, "B3", "B1");
  area.querySelector('#xlsx-minibar [data-mini="sum"]').click();
  const b3 = area.querySelector('td[data-cell="B3"]');
  assert.equal(b3.textContent, "13", "3 + 10 recomputed below");
  assert.ok(b3.classList.contains("xlsx-dirty"), "inserted formula is dirty");
});

await test("dragging a column edge writes its width", async () => {
  const area = mountGrid();
  queueStyleWrite();
  const th = area.querySelectorAll(".xlsx-table thead th")[1];
  th.dispatchEvent(new w.MouseEvent("mousedown", { bubbles: true, button: 0, clientX: 5, clientY: 5 }));
  document.dispatchEvent(new w.MouseEvent("mousemove", { bubbles: true, clientX: 60, clientY: 5 }));
  document.dispatchEvent(new w.MouseEvent("mouseup", { bubbles: true }));
  await new Promise((r) => setTimeout(r, 10));
  assert.match(calls[0].url, /\/xlsx\/style\?path=data\.xlsx/);
  assert.equal(calls[0].body.ops[0].op, "col_width");
  assert.equal(calls[0].body.ops[0].col, "A");
});

await test("a plain header click selects without writing", () => {
  const area = mountGrid();
  area.querySelectorAll(".xlsx-table thead th")[1].click();
  assert.equal(calls.length, 0, "no style write without a drag");
});

// ── #180 — sheet strip, file menu, resize cursor, themed grid ──────────────

await test("the sheet strip holds the tabs and Save on the right (#180-A3, #185)", () => {
  const area = mount();
  const strip = area.querySelector(".xlsx-sheetbar");
  assert.ok(strip, "sheet strip rendered");
  assert.ok(strip.querySelector(".xlsx-tabs"), "tabs live in the strip");
  const end = strip.querySelector(".xlsx-sheetbar-end");
  assert.ok(end, "right-end slot rendered");
  assert.ok(end.querySelector("#xlsx-save-btn"), "save pinned on the right");
  // #185 — the file outputs left the strip for the Fichier menu of the menu bar.
  assert.equal(end.querySelector("#xlsx-file-btn"), null, "no file dropdown on the strip");
  assert.equal(area.querySelector(".xlsx-cmdbar .xlsx-tabs"), null, "tabs left the ribbon");
});

await test("the Fichier menu groups Download, CSV, Markdown, HTML and Print (#185)", () => {
  const area = mount();
  const btn = area.querySelector("#xlsx-file-btn");
  assert.equal(btn.textContent.trim(), FR["xlsx.mbar_file"], "the menu bar owns the File menu");
  btn.click();
  const menu = area.querySelector("#xlsx-file-menu");
  assert.equal(menu.style.display, "block", "menu opens");
  assert.ok(menu.querySelector("#xlsx-download-btn"), "download entry kept its id");
  assert.ok(menu.querySelector("#xlsx-csv-btn"), "csv entry kept its id");
  assert.equal(
    menu.querySelector("#xlsx-csv-btn .xlsx-menu-label").textContent,
    FR["xlsx.export_csv"]
  );
  assert.deepEqual(
    [...menu.querySelectorAll("[data-xlsx-export]")].map((n) => n.dataset.xlsxExport),
    ["md", "html", "print"]
  );
  // Toggle: a second click closes.
  btn.click();
  assert.equal(menu.style.display, "none", "toggle closes");
});

await test("hovering a column edge shows the col-resize icon (#180-A1)", () => {
  const area = mount();
  const table = area.querySelector(".xlsx-table");
  // JSDOM zeroes every rect, so clientX=5 lands within 8px of each header's
  // right edge — exactly the resize zone.
  table.dispatchEvent(new w.MouseEvent("mousemove", { bubbles: true, clientX: 5, clientY: 5 }));
  assert.equal(table.style.cursor, "col-resize", "table cursor");
  assert.ok(table.classList.contains("xlsx-resize-col"), "resize class paints the cells");
  const edgeTh = area.querySelectorAll(".xlsx-table thead th")[1];
  assert.equal(edgeTh.style.cursor, "col-resize", "edge header mirrors the icon");
  // Far from any edge the cursors go back to normal.
  table.dispatchEvent(new w.MouseEvent("mousemove", { bubbles: true, clientX: 500, clientY: 500 }));
  assert.equal(table.style.cursor, "");
  assert.ok(!table.classList.contains("xlsx-resize-col"));
  assert.ok(!table.classList.contains("xlsx-resize-row"));
});

await test("hovering a row edge shows the row-resize icon (#180-A1)", () => {
  const area = mount();
  const table = area.querySelector(".xlsx-table");
  table.dispatchEvent(new w.MouseEvent("mousemove", { bubbles: true, clientX: 500, clientY: 5 }));
  assert.equal(table.style.cursor, "row-resize", "table cursor");
  assert.ok(table.classList.contains("xlsx-resize-row"), "resize class paints the cells");
  const edgeTh = area.querySelector("th.xlsx-rownum");
  assert.equal(edgeTh.style.cursor, "row-resize", "edge row header mirrors the icon");
  table.dispatchEvent(new w.MouseEvent("mouseleave", { bubbles: true }));
  assert.equal(table.style.cursor, "");
  assert.equal(edgeTh.style.cursor, "pointer", "header cursor restored");
});

await test("the grid tokens follow the active theme, all modes included (#180-A2)", async () => {
  const { applyTheme } = await import(
    pathToFileURL(path.join(REPO_ROOT, "frontend", "js", "themes.js")).href
  );
  const cssVar = (name) =>
    document.documentElement.style.getPropertyValue(name).trim();
  applyTheme("spotify", "dark");
  assert.equal(cssVar("--grid-bg"), "#121212", "grid ground = theme surface");
  assert.equal(cssVar("--grid-header-bg"), "#1a1a1a", "header = theme hover");
  assert.equal(cssVar("--grid-header-text"), "#ffffff", "header text = theme text");
  // #185 — one uniform, legible line colour for the WHOLE grid: a blend of the
  // text colour into the sheet surface, not `--border` (which was invisible).
  assert.ok(cssVar("--grid-border").startsWith("color-mix(in srgb, #ffffff"),
    `grid line = ink over surface (${cssVar("--grid-border")})`);
  assert.ok(cssVar("--grid-border").includes("#121212"), "the grid surface closes the blend");
  assert.ok(cssVar("--grid-zebra").includes("color-mix"), "zebra stays translucent");
  applyTheme("notion", "light");
  assert.equal(cssVar("--grid-bg"), "#f7f6f3", "light theme surface");
  applyTheme("github", "high-contrast");
  assert.equal(cssVar("--grid-bg"), "#0a0a0a", "generated high-contrast surface");
  applyTheme("github", "sepia");
  assert.equal(cssVar("--grid-bg"), "#eee3c8", "generated sepia surface");
});

// ── #181 — modèle de saisie Google Sheets (sélection ≠ édition) ─────────────

await test("a plain click or arrow SELECTS the cell without an editing caret (#181-A1)", () => {
  const area = mountGrid();
  const td = area.querySelector('td[data-cell="A1"]');
  td.focus();
  assert.equal(td.dataset.editing, "", "focus alone never edits");
  assert.ok(!td.classList.contains("xlsx-editing"), "no editing class");
  assert.ok(td.classList.contains("xlsx-cell"), "the cell is styled as a cell");
  // Every rendered cell starts selected, never editing.
  assert.equal(area.querySelectorAll("td.xlsx-editing").length, 0, "no cell opens in edit mode");
});

await test("double-click switches the cell to editing and keeps the caret (#181-A1)", () => {
  const area = mountGrid();
  const td = area.querySelector('td[data-cell="A1"]');
  td.focus();
  assert.equal(td.dataset.editing, "", "selected first");
  td.dispatchEvent(new w.MouseEvent("dblclick", { bubbles: true, clientX: 5, clientY: 5 }));
  assert.equal(td.dataset.editing, "1", "double-click enters editing");
  assert.ok(td.classList.contains("xlsx-editing"), "the editing class is set");
  assert.equal(td.textContent, "Banane", "double-click edits in place, it does not clear");
});

await test("the first keystroke on a selected cell REPLACES its content (#181-A1)", () => {
  const area = mountGrid();
  const td = area.querySelector('td[data-cell="A1"]');
  td.focus();
  assert.equal(td.textContent, "Banane");
  pressKey(td, "x");
  assert.equal(td.dataset.editing, "1", "typing enters edit mode");
  assert.equal(td.textContent, "", "the previous value is replaced, not appended to");
  assert.ok(td.classList.contains("xlsx-dirty"), "the cell is unsaved work");
});

await test("a second keystroke does not wipe what is being typed (#181-A1)", () => {
  const area = mountGrid();
  const td = area.querySelector('td[data-cell="A1"]');
  td.focus();
  pressKey(td, "x");
  td.textContent = "xy";
  pressKey(td, "z");
  assert.equal(td.textContent, "xy", "only the FIRST keystroke replaces the value");
});

await test("Enter on a selected cell switches it to EDITING mode (#181-A3)", () => {
  const area = mountGrid();
  const td = area.querySelector('td[data-cell="A1"]');
  td.focus();
  assert.equal(td.dataset.editing, "", "starts selected");
  pressKey(td, "Enter");
  assert.equal(td.dataset.editing, "1", "Enter enters editing mode");
  assert.equal(document.activeElement, td, "the focus stays on the edited cell");
  assert.equal(td.textContent, "Banane", "entering edit mode does not touch the value");
});

await test("Enter while editing commits and moves to the cell BELOW (#181-A3)", () => {
  const area = mountGrid();
  const td = area.querySelector('td[data-cell="A1"]');
  td.focus();
  pressKey(td, "x"); // edit mode, value cleared
  td.textContent = "12";
  pressKey(td, "Enter");
  assert.equal(td.dataset.editing, "", "the edited cell leaves edit mode");
  const below = area.querySelector('td[data-cell="A2"]');
  assert.equal(document.activeElement, below, "focus moved to the cell below");
  assert.equal(below.dataset.editing, "", "the new cell is SELECTED, not editing");
  assert.equal(area.querySelector("#xlsx-active-cell").value, "A2", "the Name Box follows");
  assert.equal(area.querySelector('td[data-cell="A1"]').textContent, "12", "the value was committed");
});

await test("Shift+arrows extend a range with a visible outline (#181-A2)", () => {
  const area = mountGrid();
  area.querySelector('td[data-cell="A1"]').focus();
  // Each extension starts from the cell the focus actually landed on (the
  // browser sends the next keydown there, as a real user would).
  const press = (key) =>
    pressKey(document.activeElement, key, { shiftKey: true });
  press("ArrowDown");
  press("ArrowRight");
  assert.equal(area.querySelector("#xlsx-active-cell").value, "A1:B2", "the Name Box shows the range");
  assert.equal(area.querySelectorAll("td.xlsx-selected").length, 4, "the 4 cells are selected");
  // The outline traces the block: each cell carries the edges it is on.
  const edge = (ref, cls) =>
    Boolean(area.querySelector(`td[data-cell="${ref}"]`).classList.contains(cls));
  assert.ok(edge("A1", "xlsx-sel-top") && edge("A1", "xlsx-sel-left"), "anchor: top + left");
  assert.ok(edge("B1", "xlsx-sel-top") && edge("B1", "xlsx-sel-right"), "B1: top + right");
  assert.ok(edge("A2", "xlsx-sel-bottom") && edge("A2", "xlsx-sel-left"), "A2: bottom + left");
  assert.ok(edge("B2", "xlsx-sel-bottom") && edge("B2", "xlsx-sel-right"), "B2: bottom + right");
  // Interior-only classes must not leak on the corners.
  assert.ok(!edge("B1", "xlsx-sel-left"), "B1 is not on the left edge");
  // The covered headers are highlighted too.
  assert.ok(area.querySelector('.xlsx-table thead th.xlsx-hdr-selected'), "column header A/B highlighted");
  assert.equal(area.querySelectorAll("th.xlsx-rownum.xlsx-hdr-selected").length, 2, "rows 1 and 2 highlighted");
});

await test("dragging with the mouse paints the same range outline (#181-A2)", () => {
  const area = mountGrid();
  const a1 = area.querySelector('td[data-cell="A1"]');
  a1.dispatchEvent(new w.MouseEvent("mousedown", { bubbles: true, button: 0 }));
  const b2 = area.querySelector('td[data-cell="B2"]');
  b2.dispatchEvent(new w.MouseEvent("mouseover", { bubbles: true }));
  // The release lands on the cell (the drag listener is on the viewer).
  b2.dispatchEvent(new w.MouseEvent("mouseup", { bubbles: true }));
  assert.equal(area.querySelectorAll("td.xlsx-selected").length, 4, "the block is selected");
  assert.ok(b2.classList.contains("xlsx-sel-bottom") && b2.classList.contains("xlsx-sel-right"),
    "the dragged corner carries both edges");
  assert.ok(area.querySelector(".xlsx-viewer").classList.contains("xlsx-selecting") === false,
    "the selecting flag is cleared on release");
});

await test("a plain click after editing drops the caret and keeps the cell selected (#181-A1)", () => {
  const area = mountGrid();
  const a1 = area.querySelector('td[data-cell="A1"]');
  a1.focus();
  pressKey(a1, "x"); // editing
  assert.equal(a1.dataset.editing, "1");
  const b3 = area.querySelector('td[data-cell="B3"]');
  b3.dispatchEvent(new w.MouseEvent("mousedown", { bubbles: true, button: 0 }));
  document.dispatchEvent(new w.MouseEvent("mouseup", { bubbles: true }));
  assert.equal(a1.dataset.editing, "", "the previously edited cell left edit mode");
  assert.ok(!a1.classList.contains("xlsx-editing"), "no editing class left behind");
});

await test("Escape leaves editing and restores the value (#181-A1)", () => {
  const area = mountGrid();
  const td = area.querySelector('td[data-cell="A1"]');
  td.focus();
  pressKey(td, "x");
  td.textContent = "perdu";
  pressKey(td, "Escape");
  assert.equal(td.textContent, "Banane", "the original value is restored");
  assert.equal(td.dataset.editing, "", "edit mode is left");
  assert.equal(area.querySelectorAll("td.xlsx-dirty").length, 0, "the cell is clean again");
});

// ── #182 — désélection & couleurs sur une plage ────────────────────────────

await test("a plain click always collapses a stuck range (#182)", () => {
  const area = mountGrid();
  area.querySelector('td[data-cell="A1"]').focus();
  const press = (k) => pressKey(document.activeElement, k, { shiftKey: true });
  press("ArrowDown");
  press("ArrowRight");
  assert.equal(area.querySelectorAll("td.xlsx-selected").length, 4, "the range is selected");

  // A Shift+click on an already-focused cell used to leave the range stuck:
  // `preventDefault()` blocked the focus move, so the next click on that cell
  // fired no focus event and the range refused to clear.
  const b2 = area.querySelector('td[data-cell="B2"]');
  b2.dispatchEvent(new w.MouseEvent("mousedown", { bubbles: true, button: 0, shiftKey: true }));
  b2.dispatchEvent(new w.MouseEvent("mouseup", { bubbles: true }));
  assert.equal(area.querySelectorAll("td.xlsx-selected").length, 4, "the Shift+click keeps the 2x2 range");

  // Now a plain click on the cell that still holds the focus must collapse it.
  const a1 = area.querySelector('td[data-cell="A1"]');
  a1.dispatchEvent(new w.MouseEvent("mousedown", { bubbles: true, button: 0 }));
  a1.dispatchEvent(new w.MouseEvent("mouseup", { bubbles: true }));
  assert.equal(area.querySelectorAll("td.xlsx-selected").length, 1, "the range collapsed");
  assert.equal(area.querySelector("#xlsx-active-cell").value, "A1", "the Name Box follows");
});

await test("a drag keeps the range in the Name Box after the release (#182)", () => {
  const area = mountGrid();
  const a1 = area.querySelector('td[data-cell="A1"]');
  a1.dispatchEvent(new w.MouseEvent("mousedown", { bubbles: true, button: 0 }));
  const b2 = area.querySelector('td[data-cell="B2"]');
  b2.dispatchEvent(new w.MouseEvent("mouseover", { bubbles: true }));
  b2.dispatchEvent(new w.MouseEvent("mouseup", { bubbles: true }));
  assert.equal(area.querySelectorAll("td.xlsx-selected").length, 4, "the block stays selected");
  assert.equal(area.querySelector("#xlsx-active-cell").value, "A1:B2",
    "the Name Box shows the range, not just the anchor");
});

await test("text and fill colours apply to every cell of a selection (#182)", async () => {
  const area = mountGrid();
  area.querySelector('td[data-cell="A1"]').focus();
  const press = (k) => pressKey(document.activeElement, k, { shiftKey: true });
  press("ArrowDown");
  press("ArrowRight");

  // #183 — the ribbon swatches open the Sheets-like colour panel; the colour
  // comes from its palette (no native <input type="color"> any more).
  const pick = async (btnId, hex) => {
    area.querySelector(`#${btnId}`).click();
    const panels = document.querySelectorAll(".xlsx-colour-panel");
    const swatch = [...panels[panels.length - 1].querySelectorAll(".xlsx-colour-swatch-btn")]
      .find((b) => b.dataset.colour === hex);
    assert.ok(swatch, `${btnId}: palette carries ${hex}`);
    swatch.click();
    await new Promise((r) => setTimeout(r, 10));
  };
  await pick("xs-fgcolor", "#d93025");
  await pick("xs-bgcolor", "#f4b400");

  const styleWrite = calls.filter((c) => c.url.includes("/xlsx/style"));
  assert.equal(styleWrite.length, 2, "one style write per colour");
  assert.deepEqual(styleWrite[0].body.ops, [{
    op: "cell", sheet: "Fruits", range: "A1:B2", style: { font_color: "#d93025" },
  }]);
  assert.deepEqual(styleWrite[1].body.ops, [{
    op: "cell", sheet: "Fruits", range: "A1:B2", style: { fill_color: "#f4b400" },
  }]);
  // Every cell of the range carries both colours.
  for (const ref of ["A1", "B1", "A2", "B2"]) {
    const style = area.querySelector(`td[data-cell="${ref}"]`).getAttribute("style");
    assert.match(style, /color:#d93025/, `${ref} font colour`);
    assert.match(style, /background:#f4b400/, `${ref} fill colour`);
  }
  // The swatches on the buttons mirror the chosen colours.
  const fgSwatch = area.querySelector('[data-swatch-for="xs-fgcolor"]');
  const bgSwatch = area.querySelector('[data-swatch-for="xs-bgcolor"]');
  assert.equal(fgSwatch.style.background, "rgb(217, 48, 37)", fgSwatch.style.background);
  assert.equal(bgSwatch.style.background, "rgb(244, 180, 0)", bgSwatch.style.background);
});

// ── #182b — le contour suit la sélection, sans.framework résiduel ───────────

await test("the range outline is cleared, never piled up (#182)", () => {
  const area = mountGrid();
  const edgesOf = (ref) => {
    const td = area.querySelector(`td[data-cell="${ref}"]`);
    return ["xlsx-sel-top", "xlsx-sel-bottom", "xlsx-sel-left", "xlsx-sel-right"]
      .filter((c) => td.classList.contains(c));
  };
  area.querySelector('td[data-cell="A1"]').focus();
  assert.equal(edgesOf("A1").length, 4, "the focused cell is boxed");
  // Walk the grid: every cell but the focused one must be COMPLETELY clean,
  // otherwise the outlines accumulate (the selector used to be missing its
  // leading dot and matched nothing, so nothing was ever cleared).
  for (const k of ["ArrowRight", "ArrowRight", "ArrowDown", "ArrowDown", "ArrowLeft", "ArrowUp"]) {
    pressKey(document.activeElement, k);
  }
  const EDGE_CLASSES = ["xlsx-sel-top", "xlsx-sel-bottom", "xlsx-sel-left", "xlsx-sel-right"];
  const outlined = [...area.querySelectorAll("td[data-cell]")]
    .filter((td) => EDGE_CLASSES.some((c) => td.classList.contains(c)))
    .map((td) => td.dataset.cell);
  assert.deepEqual(outlined, [document.activeElement.dataset.cell],
    "only the focused cell keeps an outline");
  for (const cls of EDGE_CLASSES) {
    assert.equal(area.querySelectorAll(`.${cls}`).length, 1, `exactly one "${cls}" in the grid`);
  }
});

await test("a range outline is replaced, not merged with the previous one (#182)", () => {
  const area = mountGrid();
  area.querySelector('td[data-cell="A1"]').focus();
  const press = (k) => pressKey(document.activeElement, k, { shiftKey: true });
  press("ArrowRight"); // A1:B1
  press("ArrowDown"); // A1:B2
  assert.equal(area.querySelectorAll(".xlsx-sel-top").length, 2, "two top edges for two columns");
  // Collapse back to a single cell: the other edges must go.
  area.querySelector('td[data-cell="A1"]').dispatchEvent(new w.MouseEvent("mousedown", { bubbles: true, button: 0 }));
  assert.equal(area.querySelectorAll(".xlsx-sel-top").length, 1, "one top edge again");
  assert.equal(area.querySelectorAll(".xlsx-sel-bottom").length, 1, "one bottom edge again");
  assert.equal(area.querySelectorAll(".xlsx-sel-right").length, 1, "one right edge again");
});

await test("an arrow during editing commits and moves the selection (#182)", () => {
  const area = mountGrid();
  const td = area.querySelector('td[data-cell="A1"]');
  td.focus();
  pressKey(td, "x"); // editing, value replaced
  td.textContent = "12";
  pressKey(td, "ArrowDown");
  assert.equal(td.dataset.editing, "", "the edited cell left edit mode");
  assert.equal(td.textContent, "12", "the value was committed");
  const below = area.querySelector('td[data-cell="A2"]');
  assert.equal(document.activeElement, below, "the focus moved to the cell below");
  assert.ok(below.classList.contains("xlsx-active"), "the outline follows the new cell");
  assert.ok(!td.classList.contains("xlsx-active"), "the previous cell is no longer outlined");
});

// ── #185 — barre de menus Google Sheets + boutons du ruban ──────────────────

await test("the viewer ships the ten Sheets menus above the ribbon (#185)", () => {
  const area = mount();
  const bar = area.querySelector(".xlsx-menubar");
  assert.ok(bar, "menu bar rendered");
  const labels = [...bar.querySelectorAll(".xlsx-menubtn")].map((b) => b.textContent.trim());
  assert.deepEqual(labels, [
    FR["xlsx.mbar_file"], FR["xlsx.mbar_edit"], FR["xlsx.mbar_view"],
    FR["xlsx.mbar_insert"], FR["xlsx.mbar_format"], FR["xlsx.mbar_data"],
    FR["xlsx.mbar_tools"], FR["xlsx.mbar_gemini"],
    FR["xlsx.mbar_extensions"], FR["xlsx.mbar_help"],
  ]);
  // The bar sits first inside the toolbar, i.e. above the buttons.
  const toolbar = area.querySelector(".xlsx-toolbar");
  assert.equal(toolbar.firstElementChild.className, "xlsx-menubar");
  assert.ok(area.querySelector(".xlsx-cmdbar"), "the ribbon is still there, below the menus");
});

await test("every menu entry is wired, or says plainly that ObsiGate has none (#185)", () => {
  const area = mount();
  const rows = [...area.querySelectorAll(".xlsx-menu-item")];
  assert.ok(rows.length > 120, `all the Sheets entries are listed (${rows.length})`);
  const unavailable = rows.filter((r) => r.hasAttribute("data-xlsx-unavailable"));
  assert.ok(unavailable.length > 0, "the honest dead ends are still listed");
  // Clicking one answers with the localized message instead of doing nothing.
  const label = unavailable[0].querySelector(".xlsx-menu-label").textContent;
  unavailable[0].click();
  const toasts = [...document.querySelectorAll(".toast")].map((n) => n.textContent);
  assert.ok(
    toasts.some((x) => x.includes(label)),
    `the unavailable entry explains itself (${toasts.join(" | ")})`,
  );
  assert.equal(calls.length, 0, "and nothing was sent to the server");
});

await test("Affichage ▸ Show toggles the formula bar, the grid and the headers (#185)", () => {
  const area = mountGrid();
  const viewer = area.querySelector(".xlsx-viewer");
  const bar = area.querySelector(".xlsx-formula-bar");
  area.querySelector('[data-xlsx-action="toggle-formula-bar"]').click();
  assert.equal(bar.hidden, true, "formula bar hidden");
  area.querySelector('[data-xlsx-action="toggle-formula-bar"]').click();
  assert.equal(bar.hidden, false, "formula bar back");
  area.querySelector('[data-xlsx-action="toggle-grid"]').click();
  assert.ok(viewer.classList.contains("xlsx-no-grid"), "gridlines off");
  assert.equal(area.querySelector("#xs-grid").getAttribute("aria-pressed"), "false");
  area.querySelector('#xs-grid').click();
  assert.ok(!viewer.classList.contains("xlsx-no-grid"), "the ribbon switch is in step");
  area.querySelector('[data-xlsx-action="toggle-headers"]').click();
  assert.ok(viewer.classList.contains("xlsx-no-headers"), "headers off");
  area.querySelector('[data-xlsx-action="toggle-headers"]').click();
  assert.ok(!viewer.classList.contains("xlsx-no-headers"), "headers back");
});

await test("Affichage ▸ Zoom reuses the ribbon's zoom (#185)", () => {
  const area = mountGrid();
  area.querySelector('[data-xlsx-action="zoom"][data-xlsx-arg="125"]').click();
  assert.equal(area.querySelector("#xs-zoom").value, "125");
  assert.equal(area.querySelector(".xlsx-panels").style.zoom, "125%");
  area.querySelector('[data-xlsx-action="zoom"][data-xlsx-arg="100"]').click();
  assert.equal(area.querySelector(".xlsx-panels").style.zoom, "", "back to 100%");
});

await test("Insertion ▸ rows writes the structure op (#185)", async () => {
  const area = mountGrid();
  activateCell(area, "A2");
  area.querySelector('[data-xlsx-action="insert-rows-above"]').click();
  await new Promise((r) => setTimeout(r, 10));
  assert.equal(calls.length, 1, "one locked rewrite");
  assert.match(calls[0].url, /\/xlsx\/structure\?path=data\.xlsx/);
  assert.deepEqual(calls[0].body.actions, [
    { op: "row_insert", sheet: "Fruits", at: 2, count: 1 },
  ]);
});

await test("Format ▸ Nombre writes the number format (#185)", async () => {
  const area = mountGrid();
  queueStyleWrite();
  activateCell(area, "A1");
  area.querySelector('[data-xlsx-action="numfmt"][data-xlsx-arg="0.0%"]').click();
  await new Promise((r) => setTimeout(r, 10));
  assert.equal(calls.length, 1);
  assert.match(calls[0].url, /\/xlsx\/style\?path=data\.xlsx/);
  assert.deepEqual(calls[0].body.ops[0].style, { number_format: "0.0%" });
});

await test("Format ▸ Banded colours alternates the fill of the selection (#185)", async () => {
  const area = mountGrid();
  dragSelect(area, "A1", "B3");
  queueStyleWrite();
  area.querySelector('[data-xlsx-action="banded"]').click();
  await new Promise((r) => setTimeout(r, 20));
  const writes = calls.filter((c) => String(c.url).includes("/xlsx/style"));
  assert.equal(writes.length, 1, "one batched write");
  const ops = writes[0].body.ops;
  assert.equal(ops.length, 3, "one op per row of the selection");
  assert.equal(ops[0].range, "A1:B1");
  assert.equal(ops[2].range, "A3:B3");
  assert.ok(ops[0].style.fill_color, "the first row is shaded");
  assert.equal(ops[1].style.fill_color, "", "the second row is cleared");
  assert.ok(ops[2].style.fill_color, "the third row is shaded again");
});

await test("Format ▸ Thème fills its section on demand and applies a theme (#185)", async () => {
  const area = mountGrid();
  const fmt = area.querySelector('[data-xlsx-menu="format"]');
  fmt.click();
  const drop = area.querySelector("#xlsx-menu-format");
  const theme = drop.querySelector('[data-xlsx-action="theme-menu"]');
  assert.equal(theme.nextElementSibling.children.length, 0, "empty section");
  theme.click();
  await new Promise((r) => setTimeout(r, 60));
  const themes = drop.querySelectorAll('[data-xlsx-action="apply-theme"]');
  assert.ok(themes.length >= 10, `the theme list is built (${themes.length})`);
  assert.equal(theme.getAttribute("aria-expanded"), "true");
  assert.equal(drop.style.display, "block", "the menu stays open while choosing");
  themes[0].click();
  await new Promise((r) => setTimeout(r, 40));
  assert.equal(drop.style.display, "none", "picking a theme closes the menu");
  assert.ok(
    document.documentElement.style.getPropertyValue("--bg-primary"),
    "the theme was applied to the document",
  );
});

await test("Outils ▸ Accessibilité toggles the enhanced grid (#185)", () => {
  const area = mountGrid();
  const viewer = area.querySelector(".xlsx-viewer");
  area.querySelector('[data-xlsx-action="a11y"]').click();
  assert.ok(viewer.classList.contains("xlsx-a11y"));
  area.querySelector('[data-xlsx-action="a11y"]').click();
  assert.ok(!viewer.classList.contains("xlsx-a11y"));
});

await test("Aide ▸ Raccourcis clavier lists them in a dialog (#185)", async () => {
  const area = mountGrid();
  area.querySelector('[data-xlsx-action="shortcuts"]').click();
  await new Promise((r) => setTimeout(r, 10));
  const dialog = lastDialog();
  assert.ok(dialog, "a dialog opened");
  assert.ok(dialog.textContent.includes(FR["xlsx.shortcuts_title"]), FR["xlsx.shortcuts_title"]);
  assert.ok(dialog.textContent.includes("Ctrl"), "the shortcuts are listed");
  clickDialog("confirm");
});

await test("a read-only workbook keeps the view menus but drops the editing ones (#185)", () => {
  const area = document.getElementById("content-area");
  area.innerHTML = "";
  renderXlsxViewer(area, {
    vault: "V", path: "old.xls", is_xlsx: true, xlsx_readonly: true,
    xlsx_sheets: [{ name: "F", html: sheetHtml("a") }],
  });
  assert.equal(area.querySelector('[data-xlsx-action="bold"]'), null, "no Bold");
  assert.equal(area.querySelector('[data-xlsx-action="insert-cells"]'), null, "no Insert ▸ Cells");
  assert.ok(area.querySelector('[data-xlsx-action="copy"]'), "Copy stays");
  assert.ok(area.querySelector('[data-xlsx-action="toggle-grid"]'), "Gridlines stay");
});

// ── #189 — mise en page mobile : barres repliées derrière un basculeur ──────

await test("the sheet strip carries the tools toggle that flips the toolbar (#189)", () => {
  const area = mountGrid();
  const strip = area.querySelector(".xlsx-sheetbar");
  const toggle = area.querySelector("#xlsx-tools-toggle");
  assert.ok(toggle, "toggle rendered");
  assert.equal(toggle.closest(".xlsx-sheetbar"), strip, "it lives in the sheet strip");
  assert.equal(toggle.getAttribute("aria-expanded"), "false", "starts collapsed");
  const toolbar = area.querySelector(".xlsx-toolbar");
  assert.ok(!toolbar.classList.contains("xlsx-tools-open"), "toolbar starts closed");
  toggle.click();
  assert.ok(toolbar.classList.contains("xlsx-tools-open"), "one click opens the tools");
  assert.equal(toggle.getAttribute("aria-expanded"), "true");
  assert.ok(toggle.classList.contains("active"), "the toggle shows its own state");
  toggle.click();
  assert.ok(!toolbar.classList.contains("xlsx-tools-open"), "a second click closes them");
  assert.equal(toggle.getAttribute("aria-expanded"), "false");
});

await test("style.css hides menus/ribbon/find by default under 768px and reveals them on .xlsx-tools-open (#189)", () => {
  const css = readFileSync(path.join(REPO_ROOT, "frontend", "style.css"), "utf8");
  // The desktop default: the toggle itself is out of the way.
  assert.match(css, /\.xlsx-tools-toggle\s*\{\s*display:\s*none/, "toggle hidden on desktop");
  // Mobile block: collapsed by default… (CRLF-safe slice, from the #189
  // comment up to the next top-level rule).
  const from = css.indexOf("/* #189");
  assert.ok(from > 0, "the #189 mobile block exists after the coarse-pointer rules");
  const to = css.indexOf(".xlsx-context-item", from);
  assert.ok(to > from, "the #189 block is followed by the next rule");
  const block = css.slice(from, to);
  for (const cls of [".xlsx-menubar", ".xlsx-cmdbar", ".xlsx-find-group"]) {
    const hidden = new RegExp(cls.replace(".", "\\.") + "[^}]*display:\\s*none").test(block)
      || new RegExp(cls.replace(".", "\\.") + ",[\\s\\S]{0,120}?display:\\s*none").test(block);
    assert.ok(hidden, `${cls} hidden by default on mobile`);
    assert.ok(
      block.includes(`.xlsx-toolbar.xlsx-tools-open ${cls}`),
      `${cls} revealed by .xlsx-tools-open`,
    );
  }
  // The formula bar always stays, and the open state can wrap it (find group).
  assert.ok(block.includes(".xlsx-toolbar.xlsx-tools-open .xlsx-formula-bar"), "formula bar wraps when open");
  // Touch: 44px targets, tabs on one scrollable row, 16px inputs (no iOS zoom).
  assert.match(block, /min-width:\s*44px/, "44px touch targets");
  assert.match(block, /\.xlsx-sheetbar \.xlsx-tabs\s*\{[^}]*overflow-x:\s*auto/, "tabs scroll horizontally");
  assert.match(block, /font-size:\s*16px/, "inputs at 16px avoid the iOS focus zoom");
});

// ── Report ──────────────────────────────────────────────────────────────────
console.log(`\n${passCount}/${testCount} tests passed\n`);
process.exit(passCount === testCount ? 0 : 1);