"""Reproduce the impossible-IRR example: one soffice command line, two binaries.

Downloads the public Project Mosaic workbook attached to formulas issue #166,
verifies its SHA-256, and converts it to PDF through the identical `soffice`
command line twice: once with LibreOffice as `soffice`, once with the Wolf
binary as `soffice`. Only PATH differs between the two runs. Each PDF is read
with `pdftotext -layout`; RETURNS!I44, I45, I125 and I126 are located in it,
the management IRR rows are cropped from page renders, and the crops, a share
card, results.json and SHA256SUMS are published into
public/examples/impossible-irr/.

Run from engines/wolfxl/www:

    WOLF_BIN=/path/to/wolf uv run --no-project --with pillow==13.0.0 \
        --with openpyxl==3.1.5 python scripts/examples/impossible-irr/reproduce.py

Environment: WOLF_BIN (required), SOFFICE_BIN (default: the macOS LibreOffice
app bundle), DEMO_SCRATCH (default: /tmp/wolf-demos/impossible-irr). Poppler's
pdftotext, pdftoppm and pdfinfo must be on PATH. The workbooks, PDFs and page
renders stay in the scratch directory; only crops, the card and the receipts
are published.
"""

from __future__ import annotations

import hashlib
import html
import json
import os
import platform
import re
import shutil
import subprocess
import sys
import urllib.request
import zipfile
from dataclasses import dataclass
from importlib.metadata import version
from pathlib import Path
from xml.etree import ElementTree

import openpyxl
from PIL import Image, ImageDraw, ImageFont

WWW = Path(__file__).resolve().parents[3]
OUT = WWW / "public/examples/impossible-irr"
FONT_REGULAR = WWW / "public/og/archivo-500.ttf"
FONT_BOLD = WWW / "public/og/archivo-700-115.ttf"

SCRATCH = Path(os.environ.get("DEMO_SCRATCH", "/tmp/wolf-demos/impossible-irr")).resolve()
RUN = SCRATCH / "run"
WOLF_BIN = Path(os.environ["WOLF_BIN"]).resolve()
SOFFICE_BIN = Path(os.environ.get("SOFFICE_BIN", "/Applications/LibreOffice.app/Contents/MacOS/soffice")).resolve()

MOSAIC = {
    "name": "mosaic.xlsx",
    "url": "https://github.com/user-attachments/files/20989007/Project.Mosaic.2025-6-26.xlsx",
    "report": "https://github.com/vinci1it2000/formulas/issues/166",
    "sha256": "e46fd7ed99b6aee1ecd04d2781a7f036e35c7f9f117b169aaf0ce16652c46c87",
}
DEMURRAGE = {
    "name": "demurrage.xlsx",
    "url": "https://github.com/user-attachments/files/26447666/StorageDemurrageDailyCarLimit.5.xlsx",
    "report": "https://github.com/ClosedXML/ClosedXML/issues/2839",
    "sha256": "24cd498f161fb0aa180bbb23303940f2c60ad294f56b659477c4468134632d36",
}

# Each section heading is followed by its IRR, MOIC and Cash Gain rows; the cell column is I.
SECTIONS = {
    "management": {"heading": "Returns to Management", "cells": {"IRR": "I44", "MOIC": "I45"}, "flows": 40, "dates": 30},
    "revolver": {"heading": "Returns to Revolver", "cells": {"IRR": "I125", "MOIC": "I126"}, "flows": 122, "dates": 110},
}
ROW_LABELS = ("IRR", "MOIC", "Cash Gain")

# Not measured by this script: the earlier native Excel capture of the same workbook bytes.
EXCEL_REFERENCE = {
    "source": "Earlier native Microsoft Excel capture of the same workbook bytes; Excel was not run for this page.",
    "application": "Microsoft Excel for Windows x64, 16.0 build 20326",
    "calculation": "CalculateFullRebuild, macros and events disabled, links not refreshed",
    "values": {"I44": "NM", "I45": "NM", "I125": "NM", "I126": "NM"},
}

ARMS = ("libreoffice", "wolf")
DPI = 200
PT = DPI / 72


def sha256(path: Path) -> str:
    return hashlib.sha256(path.read_bytes()).hexdigest()


def fresh(path: Path) -> Path:
    """Empty a directory below the scratch root."""
    if SCRATCH not in path.parents:
        sys.exit(f"refusing to clear {path}: outside {SCRATCH}")
    if path.exists():
        shutil.rmtree(path)
    path.mkdir(parents=True)
    return path


def public(text: str) -> str:
    """Replace scratch and binary locations so receipts carry no machine paths."""
    for path, name in ((SCRATCH, "$DEMO_SCRATCH"), (WOLF_BIN, "$WOLF_BIN"), (SOFFICE_BIN, "$SOFFICE_BIN")):
        text = text.replace(str(path), name)
    return text


def download(spec: dict) -> Path:
    path = SCRATCH / "inputs" / spec["name"]
    if not path.exists() or sha256(path) != spec["sha256"]:
        path.parent.mkdir(parents=True, exist_ok=True)
        partial = path.with_suffix(".part")
        urllib.request.urlretrieve(spec["url"], partial)
        partial.replace(path)
    digest = sha256(path)
    if digest != spec["sha256"]:
        sys.exit(f"{spec['url']}: sha256 {digest}, expected {spec['sha256']}")
    return path


def formula_inventory(path: Path) -> dict:
    """Count formula cells and those whose cached value is absent or empty."""
    ns = "{http://schemas.openxmlformats.org/spreadsheetml/2006/main}"
    formulas = empty = 0
    with zipfile.ZipFile(path) as package:
        for name in package.namelist():
            if not re.fullmatch(r"xl/worksheets/sheet\d+\.xml", name):
                continue
            for cell in ElementTree.fromstring(package.read(name)).iter(f"{ns}c"):
                if cell.find(f"{ns}f") is None:
                    continue
                formulas += 1
                value = cell.find(f"{ns}v")
                empty += value is None or not (value.text or "")
    return {"formula_cells": formulas, "formula_cells_without_cached_value": empty}


def run(argv: list[str], **kwargs) -> subprocess.CompletedProcess:
    return subprocess.run(argv, capture_output=True, text=True, timeout=300, **kwargs)


def tool_version(argv: list[str]) -> str:
    result = run(argv)
    return (result.stdout or result.stderr).strip().splitlines()[0]


def host() -> str:
    """Operating system and native CPU, also when this interpreter runs translated under Rosetta."""
    machine = platform.machine()
    if platform.system() == "Darwin" and run(["sysctl", "-n", "sysctl.proc_translated"]).stdout.strip() == "1":
        machine = "arm64"
    return f"{platform.system()} {machine}"


@dataclass
class Arm:
    name: str
    bin_dir: Path

    def soffice(self, argv: list[str], cwd: Path) -> subprocess.CompletedProcess:
        """Run `argv` with only this arm's `soffice` discoverable; the command line itself is shared."""
        env = {**os.environ, "PATH": f"{self.bin_dir}:/usr/bin:/bin", "WOLF_REPORT_DIR": str(cwd / "report")}
        return run(argv, cwd=cwd, env=env)


def make_arms() -> dict[str, Arm]:
    arms = {}
    for name, target in (("libreoffice", SOFFICE_BIN), ("wolf", WOLF_BIN)):
        bin_dir = fresh(RUN / f"bin-{name}")
        (bin_dir / "soffice").symlink_to(target)
        arms[name] = Arm(name, bin_dir)
    return arms


def convert(arm: Arm, workbook: Path, to: str, form: str) -> dict:
    """Convert `workbook` in a fresh work directory with one of the two shared soffice command lines."""
    work = fresh(RUN / "work")
    shutil.copy(workbook, work / workbook.name)
    (work / "out").mkdir()
    profile = f"-env:UserInstallation={(work / 'profile').as_uri()}"
    argv = {
        # The shape a document pipeline uses when it shells out to soffice for a PDF.
        "pipeline": ["soffice", profile, "--headless", "--invisible", "--convert-to", to, "--outdir", "out", workbook.name],
        # The documented form for a recalculated XLSX copy.
        "manual": ["soffice", "--headless", profile, "--convert-to", to, "--outdir", "out", workbook.name],
    }[form]
    result = arm.soffice(argv, work)
    produced = work / "out" / f"{workbook.stem}.{to}"
    kept = RUN / f"{arm.name}-{workbook.stem}.{to}"
    written = produced.exists()
    if written:
        produced.replace(kept)
    report = work / "report"
    receipts = {p.name: json.loads(p.read_text()) for p in sorted(report.glob("*.json"))} if report.exists() else {}
    return {
        "argv": [public(a) for a in argv],
        "exit": result.returncode,
        "output": kept if written else None,
        "stderr": [public(line) for line in result.stderr.splitlines() if not line.startswith("Fontconfig warning")],
        "receipts": receipts,
    }


# PDF text geometry ------------------------------------------------------------------------------


@dataclass
class Word:
    x0: float
    y0: float
    x1: float
    y1: float
    text: str


def page_count(pdf: Path) -> int:
    return int(re.search(r"^Pages:\s+(\d+)", run(["pdfinfo", str(pdf)]).stdout, re.M).group(1))


def pdf_words(pdf: Path) -> list[list[Word]]:
    """Words with their boxes in points, one list per page, from `pdftotext -bbox`."""
    source = run(["pdftotext", "-bbox", str(pdf), "-"]).stdout
    pages = []
    for body in re.findall(r"<page [^>]*>(.*?)</page>", source, re.S):
        words = re.findall(
            r'<word xMin="([\d.]+)" yMin="([\d.]+)" xMax="([\d.]+)" yMax="([\d.]+)">(.*?)</word>', body
        )
        pages.append([Word(float(a), float(b), float(c), float(d), html.unescape(t)) for a, b, c, d, t in words])
    if len(pages) != page_count(pdf):
        sys.exit(f"{pdf.name}: parsed {len(pages)} pages, pdfinfo reports {page_count(pdf)}")
    return pages


def page_lines(words: list[Word]) -> list[list[Word]]:
    rows: dict[float, list[Word]] = {}
    for word in words:
        rows.setdefault(round(word.y0, 1), []).append(word)
    return [sorted(row, key=lambda w: w.x0) for _, row in sorted(rows.items())]


def joined(words: list[Word]) -> str:
    return " ".join(w.text for w in words)


def collapse(text: str) -> str:
    return " ".join(text.split())


@dataclass
class Row:
    page: int
    words: list[Word]
    label: str

    @property
    def y0(self) -> float:
        return min(w.y0 for w in self.words)

    @property
    def y1(self) -> float:
        return max(w.y1 for w in self.words)

    @property
    def label_words(self) -> list[Word]:
        return self.words[: len(self.label.split())]

    @property
    def value_words(self) -> list[Word]:
        return self.words[len(self.label.split()) :]


def row_at(pages: list[list[Word]], page: int, y0: float) -> list[Word]:
    return sorted((w for w in pages[page - 1] if abs(w.y0 - y0) < 0.6), key=lambda w: w.x0)


def locate(pages: list[list[Word]], heading: str, page_sheets: dict[int, str]) -> dict:
    """Find a section heading and the IRR, MOIC and Cash Gain rows that follow it in reading order.

    When a row carries no value on the label's page (the label column printed as its own page panel),
    the value is read from the one later page of the same worksheet, per the converter's render
    report, that has text on all three row positions and none on the heading's position.
    """
    order = [(number, line) for number, words in enumerate(pages, 1) for line in page_lines(words)]
    start = next(i for i, (_, line) in enumerate(order) if joined(line) == heading)
    found = {"heading": Row(order[start][0], order[start][1], heading)}
    for offset, label in enumerate(ROW_LABELS, 1):
        number, line = order[start + offset]
        if not joined(line).startswith(label):
            sys.exit(f"after {heading!r}: expected a {label!r} row, found {joined(line)!r}")
        found[label] = Row(number, line, label)

    values: dict[str, Row] = {}
    if all(found[label].value_words for label in ROW_LABELS):
        for label in ROW_LABELS:
            values[label] = Row(found[label].page, found[label].value_words, "")
        return {"labels": found, "values": values, "value_page": None}

    label_page = found["IRR"].page
    if any(found[label].page != label_page for label in ROW_LABELS) or found["heading"].page != label_page:
        sys.exit(f"{heading!r}: label rows span pages and carry no values")
    if label_page not in page_sheets:
        sys.exit(f"{heading!r}: values sit on another page, and no render report maps pages to worksheets")
    candidates = [
        page
        for page in range(label_page + 1, len(pages) + 1)
        if page_sheets.get(page) == page_sheets[label_page]
        and not row_at(pages, page, found["heading"].y0)
        and all(row_at(pages, page, found[label].y0) for label in ROW_LABELS)
    ]
    if len(candidates) != 1:
        sys.exit(f"{heading!r}: expected one value page after page {label_page}, found {candidates}")
    value_page = candidates[0]
    for label in ROW_LABELS:
        values[label] = Row(value_page, row_at(pages, value_page, found[label].y0), "")
    return {"labels": found, "values": values, "value_page": value_page}


class LayoutText:
    """`pdftotext -layout` output of one PDF: the text a downstream pipeline ingests."""

    def __init__(self, pdf: Path):
        self.pdf = pdf
        txt = RUN / f"{pdf.stem}.txt"
        run(["pdftotext", "-layout", str(pdf), str(txt)], check=True)
        self.pages = [page.splitlines() for page in txt.read_text().split("\f")]
        self.cursor: dict[int, int] = {}

    def line(self, page: int, words: list[Word]) -> str:
        """The full-page layout line holding `words`, matched in page order so repeated text resolves."""
        region = run(
            [
                "pdftotext", "-layout", "-f", str(page), "-l", str(page),
                "-x", "0", "-y", str(int(min(w.y0 for w in words)) - 1),
                "-W", "2000", "-H", str(int(max(w.y1 for w in words) - min(w.y0 for w in words)) + 3),
                str(self.pdf), "-",
            ],
        ).stdout
        target = collapse(joined(words))
        if collapse(region) != target:
            sys.exit(f"{self.pdf.name} page {page}: region text {region!r} differs from words {target!r}")
        lines = self.pages[page - 1]
        for index in range(self.cursor.get(page, 0), len(lines)):
            if target in collapse(lines[index]):
                self.cursor[page] = index + 1
                return lines[index].rstrip()
        sys.exit(f"{self.pdf.name} page {page}: no layout line holds {target!r}")


# Crops and card -----------------------------------------------------------------------------------

PAPER = (255, 255, 255)
TAG = (95, 94, 90)
RULE = (190, 188, 182)


def font(path: Path, size: int) -> ImageFont.FreeTypeFont:
    return ImageFont.truetype(str(path), size)


def page_image(pdf: Path, page: int) -> Image.Image:
    prefix = RUN / "renders" / f"{pdf.stem}-{page}"
    prefix.parent.mkdir(exist_ok=True)
    run(["pdftoppm", "-r", str(DPI), "-f", str(page), "-l", str(page), "-png", "-singlefile", str(pdf), str(prefix)], check=True)
    with Image.open(f"{prefix}.png") as image:
        return image.convert("RGB")


def clip(pdf: Path, page: int, box: tuple[float, float, float, float]) -> Image.Image:
    x0, y0, x1, y1 = box
    return page_image(pdf, page).crop((round(x0 * PT), round(y0 * PT), round(x1 * PT), round(y1 * PT)))


def tagged(image: Image.Image, tag: str) -> Image.Image:
    """Prefix a rendered panel with a small page tag, drawn outside the rendered pixels."""
    strip = 46
    out = Image.new("RGB", (image.width, image.height + strip), PAPER)
    ImageDraw.Draw(out).text((4, strip - 12), tag, font=font(FONT_REGULAR, 24), fill=TAG, anchor="lb")
    out.paste(image, (0, strip))
    return out


def dashed(draw: ImageDraw.ImageDraw, start: tuple[int, int], end: tuple[int, int]) -> None:
    (x0, y0), (x1, y1) = start, end
    length = max(abs(x1 - x0), abs(y1 - y0))
    for offset in range(0, length, 14):
        t0, t1 = offset / length, min(offset + 7, length) / length
        draw.line((x0 + (x1 - x0) * t0, y0 + (y1 - y0) * t0, x0 + (x1 - x0) * t1, y0 + (y1 - y0) * t1), fill=RULE, width=2)


def stack(panels: list[Image.Image], vertical: bool, pad: int = 28, gap: int = 40) -> Image.Image:
    """Join page panels on white paper with a dashed rule at every page boundary."""
    if vertical:
        width = max(p.width for p in panels) + 2 * pad
        height = sum(p.height for p in panels) + gap * (len(panels) - 1) + 2 * pad
    else:
        width = sum(p.width for p in panels) + gap * (len(panels) - 1) + 2 * pad
        height = max(p.height for p in panels) + 2 * pad
    out = Image.new("RGB", (width, height), PAPER)
    draw = ImageDraw.Draw(out)
    x = y = pad
    for index, panel in enumerate(panels):
        if index:
            if vertical:
                dashed(draw, (pad, y - gap // 2), (width - pad, y - gap // 2))
            else:
                dashed(draw, (x - gap // 2, pad), (x - gap // 2, height - pad))
        out.paste(panel, (x, y))
        if vertical:
            y += panel.height + gap
        else:
            x += panel.width + gap
    return out


def irr_crop(pdf: Path, found: dict) -> tuple[Image.Image, list[int]]:
    """Crop the management heading, IRR, MOIC and Cash Gain rows, following them across page panels."""
    labels, values = found["labels"], found["values"]
    heading, cash = labels["heading"], labels["Cash Gain"]
    if found["value_page"] is None:
        right = max(w.x1 for row in values.values() for w in row.words) + 14
        left = heading.words[0].x0 - 10
        if heading.page == labels["IRR"].page:
            box = (left, heading.y0 - 8, right, cash.y1 + 8)
            return tagged(clip(pdf, heading.page, box), f"page {heading.page}"), [heading.page]
        top = clip(pdf, heading.page, (left, heading.y0 - 8, right, heading.y1 + 6))
        rows = clip(pdf, labels["IRR"].page, (left, labels["IRR"].y0 - 8, right, cash.y1 + 8))
        panels = [tagged(top, f"page {heading.page}"), tagged(rows, f"page {labels['IRR'].page}")]
        return stack(panels, vertical=True), [heading.page, labels["IRR"].page]
    band = (heading.y0 - 8, cash.y1 + 8)
    label_right = max(w.x1 for label in ("heading", *ROW_LABELS) for w in labels[label].words) + 24
    label_panel = clip(pdf, heading.page, (heading.words[0].x0 - 10, band[0], label_right, band[1]))
    value_words = [w for row in values.values() for w in row.words]
    value_panel = clip(
        pdf, found["value_page"], (min(w.x0 for w in value_words) - 32, band[0], max(w.x1 for w in value_words) + 10, band[1])
    )
    panels = [tagged(label_panel, f"page {heading.page}"), tagged(value_panel, f"page {found['value_page']}")]
    return stack(panels, vertical=False), [heading.page, found["value_page"]]


def fit(image: Image.Image, width: int, height: int) -> Image.Image:
    scale = min(width / image.width, height / image.height)
    return image.resize((round(image.width * scale), round(image.height * scale)), Image.LANCZOS)


def fitted_font(text: str, path: Path, size: int, width: int) -> ImageFont.FreeTypeFont:
    """The largest font at or below `size` that sets `text` within `width` pixels."""
    while font(path, size).getlength(text) > width:
        size -= 1
    return font(path, size)


def card(crops: dict[str, Image.Image], results: dict) -> Image.Image:
    """1200x675 share card: a one-line claim over one row per converter, each value beside its crop."""
    canvas, ink, muted, rule = (5, 5, 6), (238, 241, 245), (154, 154, 160), (52, 52, 56)
    tones = {"libreoffice": (248, 113, 113), "wolf": (63, 207, 127)}
    values = {arm: results["arms"][arm]["cells"]["I44"]["text"] for arm in ARMS}
    lo_version = results["environment"]["libreoffice"].split()[1]
    wolf_version = results["environment"]["wolf"]["version"].split()[1]
    titles = {"libreoffice": f"LibreOffice {lo_version}", "wolf": f"Wolf {wolf_version}"}

    image = Image.new("RGB", (1200, 675), canvas)
    draw = ImageDraw.Draw(image)
    draw.text((64, 44), "WolfSDK", font=font(FONT_BOLD, 30), fill=ink, anchor="lt")
    draw.text((1136, 50), "wolfsdk.com/examples/impossible-irr", font=font(FONT_REGULAR, 20), fill=muted, anchor="rt")
    claim = f"Same soffice command. LibreOffice: {values['libreoffice']}. Wolf: {values['wolf']}."
    draw.text((64, 104), claim, font=fitted_font(claim, FONT_BOLD, 42, 1072), fill=ink, anchor="lt")
    subtitle = "Management IRR, RETURNS!I44, of one public financial model, converted to PDF."
    draw.text((64, 160), subtitle, font=font(FONT_REGULAR, 21), fill=muted, anchor="lt")

    rows = {"libreoffice": (204, 432), "wolf": (448, 604)}
    for arm, (top, bottom) in rows.items():
        draw.text((64, top + 18), "soffice =", font=font(FONT_REGULAR, 20), fill=muted, anchor="lt")
        draw.text((64, top + 46), titles[arm], font=font(FONT_REGULAR, 24), fill=ink, anchor="lt")
        draw.text((64, top + 86), values[arm], font=fitted_font(values[arm], FONT_BOLD, 52, 300), fill=tones[arm], anchor="lt")
        crop = fit(crops[arm], 1136 - 400 - 32, bottom - top - 32)
        draw.rounded_rectangle((400, top, 400 + crop.width + 32, bottom), radius=14, fill=PAPER)
        image.paste(crop, (416, top + (bottom - top - crop.height) // 2))

    draw.line((64, 626, 1136, 626), fill=rule, width=1)
    excel = EXCEL_REFERENCE["values"]["I44"]
    reference = f"Reference: {excel} in Microsoft Excel for this cell (earlier native capture)."
    draw.text((64, 640), reference, font=font(FONT_REGULAR, 19), fill=muted, anchor="lt")
    return image


# Main ---------------------------------------------------------------------------------------------


def calculated_values(path: Path) -> dict:
    sheet = openpyxl.load_workbook(path, data_only=True)["RETURNS"]
    values = {cell: sheet[cell].value for section in SECTIONS.values() for cell in section["cells"].values()}
    for section in SECTIONS.values():
        flows, dates = section["flows"], section["dates"]
        values[f"I{flows}:N{flows}"] = [sheet.cell(flows, column).value for column in range(9, 15)]
        values[f"I{dates}:N{dates}"] = [sheet.cell(dates, column).value.date().isoformat() for column in range(9, 15)]
    return values


def main() -> None:
    for tool in ("pdftotext", "pdftoppm", "pdfinfo"):
        if not shutil.which(tool):
            sys.exit(f"{tool} is not on PATH")
    fresh(RUN)
    OUT.mkdir(parents=True, exist_ok=True)
    mosaic, demurrage = download(MOSAIC), download(DEMURRAGE)
    arms = make_arms()

    source = openpyxl.load_workbook(mosaic)["RETURNS"]
    results: dict = {
        "generated_by": "scripts/examples/impossible-irr/reproduce.py",
        "environment": {
            "host": host(),
            "wolf": {"version": tool_version([str(WOLF_BIN), "--version"]), "sha256": sha256(WOLF_BIN)},
            "libreoffice": tool_version([str(SOFFICE_BIN), "--version"]),
            "poppler": tool_version(["pdftotext", "-v"]),
            "python": f"{platform.python_version()} ({platform.machine()})",
            "pillow": version("pillow"),
            "openpyxl": version("openpyxl"),
        },
        "workbook": {
            "url": MOSAIC["url"],
            "report": MOSAIC["report"],
            "sha256": sha256(mosaic),
            "bytes": mosaic.stat().st_size,
            **formula_inventory(mosaic),
            "cells": {
                cell: {"label": f"{name.capitalize()} {label}", "formula": source[cell].value}
                for name, section in SECTIONS.items()
                for label, cell in section["cells"].items()
            },
        },
        "excel_reference": EXCEL_REFERENCE,
        "arms": {},
    }

    crops: dict[str, Image.Image] = {}
    argvs = []
    for name, arm in arms.items():
        conversion = convert(arm, mosaic, "pdf", "pipeline")
        if conversion["exit"] != 0 or conversion["output"] is None:
            sys.exit(f"{name}: conversion failed: {conversion['stderr']}")
        argvs.append(conversion["argv"])
        pdf: Path = conversion["output"]
        pages = pdf_words(pdf)
        layout = LayoutText(pdf)
        receipt = conversion["receipts"].get(f"soffice.{mosaic.stem}.render-report.json")
        page_sheets = {page["number"]: page["sheet"] for page in receipt["outputs"][0]["pages"]} if receipt else {}
        cells, excerpts = {}, {}
        for section_name, section in SECTIONS.items():
            found = locate(pages, section["heading"], page_sheets)
            labels, values = found["labels"], found["values"]
            for label, cell in section["cells"].items():
                cells[cell] = {
                    "label_page": labels[label].page,
                    "value_page": values[label].page,
                    "text": joined(values[label].words),
                }
            excerpt = []
            for row in ("heading", *ROW_LABELS):
                excerpt.append({"page": labels[row].page, "line": layout.line(labels[row].page, labels[row].words)})
            if found["value_page"] is not None:
                for row in ROW_LABELS:
                    excerpt.append({"page": values[row].page, "line": layout.line(values[row].page, values[row].words)})
            excerpts[section_name] = excerpt
            if section_name == "management":
                crops[name], crop_pages = irr_crop(pdf, found)
                crop_file = f"{name}-irr.png"
                crops[name].save(OUT / crop_file, optimize=True)
        results["arms"][name] = {
            "exit": conversion["exit"],
            "pdf_pages": len(pages),
            "cells": cells,
            "layout_text_excerpts": excerpts,
            "crop": {"file": crop_file, "pages": crop_pages, "dpi": DPI, "width": crops[name].width, "height": crops[name].height},
            **({"render_report_warnings": receipt["outputs"][0]["warnings"]} if receipt else {}),
        }
    if argvs[0] != argvs[1]:
        sys.exit(f"command lines differ: {argvs}")
    results["command"] = {
        "argv": argvs[0],
        "cwd": "$DEMO_SCRATCH/run/work",
        "path": {name: f"$DEMO_SCRATCH/run/bin-{name}:/usr/bin:/bin" for name in arms},
        "soffice": {"libreoffice": "symlink to $SOFFICE_BIN", "wolf": "symlink to $WOLF_BIN"},
        "text_extraction": "pdftotext -layout <pdf> <txt>",
    }

    # What each engine calculates for the cells and their cash-flow inputs, from an XLSX copy.
    calculated = {}
    for name, arm in arms.items():
        conversion = convert(arm, mosaic, "xlsx", "manual")
        if conversion["exit"] != 0 or conversion["output"] is None:
            sys.exit(f"{name}: xlsx conversion failed: {conversion['stderr']}")
        calculated[name] = calculated_values(conversion["output"])
    results["calculated_values"] = {"argv": conversion["argv"], **calculated}

    # The same Wolf release on a second public workbook from the same search. The earlier search is
    # recorded, not measured here: five public workbooks, of which Wolf converted all but this one.
    check = {
        "url": DEMURRAGE["url"],
        "report": DEMURRAGE["report"],
        "sha256": sha256(demurrage),
        "earlier_search": {"public_workbooks": 5, "wolf_conversion_failures": 1},
    }
    for name, arm in arms.items():
        conversion = convert(arm, demurrage, "pdf", "pipeline")
        check[name] = {
            "exit": conversion["exit"],
            "pdf_pages": page_count(conversion["output"]) if conversion["output"] else None,
            "errors": [line for line in conversion["stderr"] if "error" in line.lower() or "orientation" in line],
        }
    results["demurrage_check"] = check

    card(crops, results).save(OUT / "card.png", optimize=True)
    shutil.copy(Path(__file__), OUT / "reproduce.py")
    (OUT / "results.json").write_text(json.dumps(results, indent=1, ensure_ascii=False) + "\n")
    published = [OUT / name for name in ("libreoffice-irr.png", "wolf-irr.png", "card.png", "reproduce.py", "results.json")]
    (OUT / "SHA256SUMS").write_text("".join(f"{sha256(p)}  {p.name}\n" for p in published))
    for arm in ARMS:
        print(arm, {cell: value["text"] for cell, value in results["arms"][arm]["cells"].items()})
    print("published", len(published), "files to", OUT.relative_to(WWW))


if __name__ == "__main__":
    main()