<bmx-formula-bar>

<style>
  #ex-fb { display: grid; gap: 0.75rem; inline-size: 100%; }
  #ex-fb-sheet { inline-size: 100%; overflow-x: auto; }
  #ex-fb-sheet table { border-collapse: collapse; min-inline-size: 32rem; inline-size: 100%; font-variant-numeric: tabular-nums; background: var(--bmx-surface); }
  #ex-fb-sheet th { padding: 0.25rem 0.5rem; border: 1px solid var(--bmx-border); background: var(--bmx-surface-sunken); color: var(--bmx-text-muted); font-weight: 500; font-size: var(--bmx-font-size-xs); }
  #ex-fb-sheet td { position: relative; padding: 0.375rem 0.625rem; border: 1px solid var(--bmx-border); text-align: end; cursor: cell; white-space: nowrap; }
  #ex-fb-sheet td.text { text-align: start; }
  #ex-fb-sheet td[aria-selected='true'] { outline: 2px solid var(--bmx-primary); outline-offset: -2px; }
  #ex-fb-sheet td[data-ref] { box-shadow: inset 0 0 0 2px var(--ref); background: color-mix(in oklab, var(--ref) 10%, transparent); }
  #ex-fb-sheet td.error { color: var(--bmx-danger); }
</style>

<bmx-formula-engine
  id="ex-fb-engine"
  locale="en-GB"
  sheets='[{"name":"Sales","cells":[
    ["Region","Q1","Q2","Q3","Total","Share"],
    ["North",120,140,135,"=SUM(B2:D2)","=E2/$E$6"],
    ["South",95,130,160,"=SUM(B3:D3)","=E3/$E$6"],
    ["East",210,180,205,"=SUM(B4:D4)","=E4/$E$6"],
    ["West",80,95,110,"=SUM(B5:D5)","=E5/$E$6"],
    ["Total","=SUM(B2:B5)","=SUM(C2:C5)","=SUM(D2:D5)","=SUM(E2:E5)","=SUM(F2:F5)"]],
    "formats":{"F2:F6":"0.0%"}}]'
  names='[{"name":"Target","formula":"1800","comment":"The year&apos;s sales target"}]'
></bmx-formula-engine>

<div id="ex-fb">
  <bmx-formula-bar id="ex-fb-bar" engine="ex-fb-engine" cell="Sales!E6" label="Formula for the selected cell"></bmx-formula-bar>
  <div id="ex-fb-sheet"></div>
</div>

<div class="row">
  <span class="note">
    <strong>Click a cell, then edit its formula.</strong> Type <code>=VL</code> for suggestions, a function name and
    <code>(</code> for its arguments, F4 on a reference for <code>$</code>s, Shift+F3 for the function picker. While a
    formula is open, clicking cells inserts references - each in its own colour, outlined in the table too.
  </span>
</div>

<script type="module">
  await customElements.whenDefined('bmx-formula-engine');
  const engine = document.getElementById('ex-fb-engine');
  const bar = document.getElementById('ex-fb-bar');
  const host = document.getElementById('ex-fb-sheet');
  const book = await engine.getWorkbook();
  const cols = ['A', 'B', 'C', 'D', 'E', 'F'];
  let editing = false;

  // A plain table, drawn from the workbook.
  const table = document.createElement('table');
  table.setAttribute('role', 'grid');
  table.setAttribute('aria-label', 'Sales');
  table.innerHTML = `<tr><th></th>${cols.map(c => `<th scope="col">${c}</th>`).join('')}</tr>` +
    [1, 2, 3, 4, 5, 6].map(r => `<tr><th scope="row">${r}</th>${cols.map(c => `<td data-cell="${c}${r}" tabindex="-1"></td>`).join('')}</tr>`).join('');
  host.appendChild(table);

  const draw = () => {
    for (const td of table.querySelectorAll('td')) {
      const address = `Sales!${td.dataset.cell}`;
      const value = book.getValue(address);
      td.textContent = book.getText(address);
      td.classList.toggle('text', typeof value === 'string');
      td.classList.toggle('error', !!(value && value.code));
      td.setAttribute('aria-selected', String(bar.cell === address));
    }
  };
  draw();
  engine.addEventListener('bmxFormulaChange', draw);

  table.addEventListener('mousedown', event => {
    const td = event.target.closest('td');
    if (!td) return;
    // While a formula is being typed, a click inserts a reference rather than moving.
    if (editing) {
      event.preventDefault();
      bar.insertReference(td.dataset.cell);
      return;
    }
    bar.cell = `Sales!${td.dataset.cell}`;
    draw();
  });

  bar.addEventListener('bmxFormulaInput', event => (editing = event.detail.value.startsWith('=')));
  bar.addEventListener('bmxFormulaCancel', () => (editing = false));
  bar.addEventListener('bmxFormulaCommit', event => {
    editing = false;
    // Enter moves down, Tab moves right, as a spreadsheet does.
    const [, col, row] = /([A-F])(\d+)$/.exec(bar.cell) ?? [];
    if (col && event.detail.move === 'down' && Number(row) < 6) bar.cell = `Sales!${col}${Number(row) + 1}`;
    if (col && event.detail.move === 'right' && col !== 'F') bar.cell = `Sales!${cols[cols.indexOf(col) + 1]}${row}`;
    draw();
  });
  bar.addEventListener('bmxFormulaNavigate', draw);

  // Outline each reference in the formula in the colour the bar draws it in.
  bar.addEventListener('bmxFormulaReferences', event => {
    for (const td of table.querySelectorAll('td[data-ref]')) {
      td.removeAttribute('data-ref');
      td.style.removeProperty('--ref');
    }
    for (const ref of event.detail.references) {
      const [from, to = from] = ref.range.split(':');
      const [, c1, r1] = /([A-Z]+)(\d+)/.exec(from) ?? [];
      const [, c2, r2] = /([A-Z]+)(\d+)/.exec(to) ?? [];
      if (!c1 || !c2) continue;
      for (let r = Math.min(r1, r2); r <= Math.max(r1, r2); r += 1) {
        for (let c = Math.min(cols.indexOf(c1), cols.indexOf(c2)); c <= Math.max(cols.indexOf(c1), cols.indexOf(c2)); c += 1) {
          const td = table.querySelector(`td[data-cell="${cols[c]}${r}"]`);
          if (td) {
            td.setAttribute('data-ref', String(ref.colorIndex));
            td.style.setProperty('--ref', ref.color);
          }
        }
      }
    }
  });
</script>