"""The merged table as an Excel workbook: one sheet per statement, the line names indented as in the results tree.

The tree comes from the breakdowns the merge already returns on every merged row (`breakdown.components`: the
series that add up to a total, in printed order). A line sits under the total whose current breakdown lists it;
lines nobody lists are top-level, in the order of the rows (alphabetical by name). Nothing is calculated here."""
import io
import re
from typing import Any, Dict, List, Optional, Tuple

from openpyxl import Workbook
from openpyxl.comments import Comment
from openpyxl.styles import Alignment, Border, Font, PatternFill, Side
from openpyxl.utils import get_column_letter

from .series_keys import report_end_year

# the results screen's statement tabs, in order; every other statement type goes to "Notes & Others"
SHEETS: List[Tuple[str, Tuple[str, ...]]] = [
    ("Balance Sheet", ("BALANCE_SHEET",)),
    ("Income Statement", ("INCOME_STATEMENT",)),
    ("Cash Flow", ("CASH_FLOW",)),
    ("Changes in Equity", ("CHANGES_IN_EQUITY",)),
]
OTHER_SHEET = "Notes & Others"
SCOPE_LABELS = {"CONSOLIDATED": "Consolidated", "STANDALONE": "Standalone"}
MAX_INDENT = 15                       # Excel's limit

_HEADER_FILL = PatternFill("solid", fgColor="1F2933")
_SCOPE_FILL = PatternFill("solid", fgColor="E4E7EB")
_RESTATED_FILL = PatternFill("solid", fgColor="FFF3C4")
_PER_UNIT = re.compile(r"/share|%|times|ratio", re.I)


def build_tree(rows: List[Dict[str, Any]]) -> List[Tuple[Dict[str, Any], int, bool]]:
    """`rows` (one statement and scope, in their given order) as [(row, depth, has_children)] in tree order."""
    by_id = {r["series_id"]: r for r in rows if r.get("series_id") is not None}
    position = {r["series_id"]: i for i, r in enumerate(rows)}
    parents: Dict[int, List[int]] = {}
    for r in rows:
        comps = (r.get("breakdown") or {}).get("components") or []
        for c in comps:
            sid = c.get("series_id")
            if sid in by_id and sid != r.get("series_id"):
                parents.setdefault(sid, []).append(r["series_id"])
    def listed(total_id: int, sid: int) -> int:
        """End year of the newest report that lists `sid` as a part of `total_id` (its component's `last_report`); -1 when unknown."""
        for c in by_id[total_id]["breakdown"]["components"]:
            if c.get("series_id") == sid and c.get("last_report"):
                try:
                    return report_end_year(str(c["last_report"]))
                except (TypeError, ValueError):
                    return -1
        return -1

    parent_of: Dict[int, int] = {}
    for sid, cands in parents.items():
        # The total that lists the line in the NEWEST report is its parent: the latest layout of the statement wins (Total Non-Current
        # Assets over Total Assets for a line an older report printed outside the subtotal; a total's parts are the union over all
        # reports, so an older report's layout is still in the bigger total). Among totals that list it in the same report (or when
        # no report is known), the smallest total (fewest parts) wins ("Total liabilities" over "Total equity and liabilities"), then the row order.
        parent_of[sid] = min(cands, key=lambda p: (-listed(p, sid), len(by_id[p]["breakdown"]["components"]), position[p]))
    children: Dict[int, List[int]] = {}
    for p in {p for p in parent_of.values()}:
        order = [c["series_id"] for c in by_id[p]["breakdown"]["components"] if parent_of.get(c["series_id"]) == p]
        children[p] = list(dict.fromkeys(order))

    out: List[Tuple[Dict[str, Any], int, bool]] = []
    seen = set()

    def walk(sid: int, depth: int) -> None:
        if sid in seen:
            return
        seen.add(sid)
        kids = children.get(sid, [])
        out.append((by_id[sid], depth, bool(kids)))
        for k in kids:
            walk(k, depth + 1)

    for r in rows:
        sid = r.get("series_id")
        if sid is None:
            out.append((r, 0, False))
        elif sid not in parent_of:
            walk(sid, 0)
    for r in rows:                       # a parent loop (never expected) must not drop a line
        if r.get("series_id") in by_id and r["series_id"] not in seen:
            walk(r["series_id"], 0)
    return out


def _sheet_name(statement_type: str) -> str:
    for name, types in SHEETS:
        if statement_type in types:
            return name
    return OTHER_SHEET


def _number_format(unit: Optional[str]) -> str:
    return "#,##0.00;-#,##0.00" if _PER_UNIT.search(str(unit or "")) else "#,##0;-#,##0"


def _number(v: Any) -> Optional[float]:
    try:
        return None if v in (None, "") else float(v)
    except (TypeError, ValueError):
        return None


def build_merged_workbook(rows: List[Dict[str, Any]], years: Optional[List[int]] = None) -> bytes:
    """The workbook bytes. `rows` are the merged rows (`company_results.merged_rows_json`)."""
    if years is None:
        years = sorted({int(y) for r in rows for y in (r.get("values") or {}) if str(y).isdigit()})
    grouped: Dict[str, List[Dict[str, Any]]] = {}
    for r in rows:
        grouped.setdefault(_sheet_name(r.get("statement_type") or ""), []).append(r)

    wb = Workbook()
    wb.remove(wb.active)
    names = [n for n, _ in SHEETS] + [OTHER_SHEET]
    for name in names:
        sheet_rows = grouped.get(name)
        if not sheet_rows:
            continue
        ws = wb.create_sheet(name)
        ws.append(["Field / Line Item", "Unit"] + [str(y) for y in years])
        for cell in ws[1]:
            cell.font = Font(bold=True, color="FFFFFF")
            cell.fill = _HEADER_FILL
            cell.alignment = Alignment(horizontal="left" if cell.column <= 2 else "right")
        scopes: Dict[str, List[Dict[str, Any]]] = {}
        for r in sheet_rows:
            scopes.setdefault((r.get("scope") or "CONSOLIDATED").upper(), []).append(r)
        for scope in sorted(scopes, key=lambda s: 0 if s == "CONSOLIDATED" else 1):
            ws.append([f"{SCOPE_LABELS.get(scope, scope.title())} ({len(scopes[scope])})"])
            for cell in ws[ws.max_row]:
                cell.font = Font(bold=True)
                cell.fill = _SCOPE_FILL
            for r, depth, has_children in build_tree(scopes[scope]):
                values = r.get("values") or {}
                ws.append([r.get("field") or "", r.get("unit") or ""] + [_number(values.get(str(y))) for y in years])
                row_no = ws.max_row
                heading = has_children or r.get("aggregation_level") in ("SUBTOTAL", "GRAND_TOTAL")
                name_cell = ws.cell(row=row_no, column=1)
                name_cell.alignment = Alignment(indent=min(depth, MAX_INDENT))
                if heading:
                    name_cell.font = Font(bold=True)
                fmt = _number_format(r.get("unit"))
                restated = r.get("restatement_variances") or {}
                for i, y in enumerate(years):
                    cell = ws.cell(row=row_no, column=3 + i)
                    cell.number_format = fmt
                    if heading:
                        cell.font = Font(bold=True)
                    info = restated.get(str(y))
                    if info and cell.value is not None:
                        cell.fill = _RESTATED_FILL
                        orig = info.get("original_value")
                        cell.comment = Comment(f"Restated. First printed as {orig:,.2f}" if orig is not None
                                               else "Restated", "Financial Studio")
                    chosen = (r.get("overrides") or {}).get(str(y))
                    if chosen and cell.value is not None:        # the current disclosure was chosen by a user
                        cell.comment = Comment("Chosen by " + str(chosen.get("set_by") or "a user")
                                               + (" (a disclosure added by a user)" if chosen.get("origin") == "USER_ADDED" else ""),
                                               "Financial Studio")
        ws.freeze_panes = "C2"
        ws.column_dimensions["A"].width = 58
        ws.column_dimensions["B"].width = 11
        for i in range(len(years)):
            ws.column_dimensions[get_column_letter(3 + i)].width = 18
    if not wb.sheetnames:
        wb.create_sheet("Merged")
    buf = io.BytesIO()
    wb.save(buf)
    return buf.getvalue()


_BANNER_FILL = PatternFill("solid", fgColor="D9E1F2")
_TOTAL_FILL = PatternFill("solid", fgColor="EDEFF2")
_CALC_ONLY_FILL = PatternFill("solid", fgColor="BDD7EE")       # a total the reports do not print for that year: calculated only
_INPUT_BLUE = "0000FF"
_TOTAL_BORDER = Border(top=Side(style="thin", color="808080"))
_STANDARD_SHEET = "Balance Sheet"


def _nest(flat: List[Tuple[Dict[str, Any], int, bool]]) -> List[Dict[str, Any]]:
    """build_tree's flat [(row, depth, has_children)] as nested nodes {row, kids}."""
    roots: List[Dict[str, Any]] = []
    stack: List[Tuple[int, Dict[str, Any]]] = []
    for row, depth, _ in flat:
        node = {"row": row, "kids": []}
        while stack and stack[-1][0] >= depth:
            stack.pop()
        (stack[-1][1]["kids"] if stack else roots).append(node)
        stack.append((depth, node))
    return roots


def _bare(name: Any) -> str:
    return re.sub(r"^total\s+", "", str(name or "").strip(), flags=re.I)


def _is_per_share(r: Dict[str, Any]) -> bool:
    return bool(_PER_UNIT.search(str(r.get("unit") or ""))) or (r.get("metric_type") not in (None, "", "CURRENCY"))


def _formula_parts(node: Dict[str, Any]) -> Optional[List[Tuple[int, int]]]:
    """[(series_id, sign)] a total adds up from: its direct children in the sheet's tree (each line is counted once, under the
    smallest total that lists it; the children are the union of the parts across reports), with the sign the total's breakdown gives
    them. None when the total is not a plain sum (another operation) or has no children."""
    r = node["row"]
    bd = r.get("breakdown") or {}
    if str(bd.get("operation") or "SUM").upper() != "SUM":
        return None
    signs = {c.get("series_id"): (-1 if (c.get("sign") or 1) < 0 else 1) for c in bd.get("components") or []}
    parts = [(k["row"]["series_id"], signs.get(k["row"]["series_id"], 1)) for k in node["kids"]]
    return parts or None


def _standard_format(unit: Optional[str]) -> str:
    return '#,##0.00;-#,##0.00;"-"' if _PER_UNIT.search(str(unit or "")) else '#,##0;-#,##0;"-"'


def _sheet_title(name: str, scope: str, several: bool) -> str:
    """A sheet name (Excel allows 31 characters): the statement, with the scope when the statement has both."""
    if not several:
        return name
    label = SCOPE_LABELS.get(scope, scope.title())
    title = f"{name} - {label}"
    return title if len(title) <= 31 else f"{name} - {label[:4]}."


def _tolerance(r: Dict[str, Any]) -> float:
    """How far a check may be from 0 and still count as matching: rounding of an amount (5 units), of a per-share figure or ratio (0.01)."""
    return 0.01 if _is_per_share(r) else 5.0


def _layout_rows(sections: List[Tuple[Optional[str], List[Dict[str, Any]]]], years: List[int], style: str) -> List[Dict[str, Any]]:
    """The rows of one standard sheet, as data. `sections` are [(banner or None, rows)] (Notes & Others has one per statement type);
    `style` is "balance" (the Balance Sheet) or "flow" (every other statement). Each row is a dict (kinds below); the Excel writer
    (`_render_sheet`) and `GET /results/standard` both read them, so the two never disagree.

    Both styles: a total shows the figure the reports print (the merged value, a user's chosen disclosure included) as its main value.
    When its breakdown is known it is also CALCULATED, year by year, from the breakdown the report that states the year printed
    (`breakdown.by_year[year]`, `parts_by_year`; a minus-signed part is subtracted as -ABS(), like the extraction's own arithmetic; a part
    counts with its main value in the sheet, or as printed when it is not on the sheet); a year whose stored breakdown does not add up
    (status MISMATCH) or that no report prints a total for is added up from the union of the parts across reports (each line counted once,
    the direct children in the tree). Under the total an "As calculated" row (kind `calculated`) holds that sum and a
    "Check (calculated - reported)" row the difference, followed by one "gap" row. **A year whose report stored no breakdown has nothing to
    check: both cells are `null` (no entry in `ok`)**, and a total with no such year at all gets no `calculated` / `check` / `gap` rows (its
    row is `calculated: false`). `null` as well for a year with no printed total or no figure in the parts. Only for a year no report
    prints a total for, the calculated figure is the main value (listed in `calculated_only`; blue in the xlsx). A total without a usable
    breakdown keeps its merged value (`calculated: false`).

    "balance": a banner per top-level total (ASSETS, EQUITY AND LIABILITIES), a group header over each group that has lines of its
    own (the lines indented under it) and the group's "Total ..." row after them; then the "balance_check" row (assets minus equity
    and liabilities) and the lines in no total under PER SHARE DATA / OTHER LINES banners.
    "flow" (as a statement is printed): a group's lines first, then its total; a total that is part of a larger one stays at the
    same level (Gross Profit, Profit from Operations, ..., Net Profit read down one column) and a group's own lines are indented one
    level; lines in no total follow their section, per-share lines go under a PER SHARE DATA banner at the end.

    The first letter of a banner, header, line or total name is upper-cased when the merged name starts in lower case; nothing else in a
    name changes.

    Row fields. All: `kind` (banner | header | line | total | calculated | check | balance_check | gap), `name`, `depth`, `unit`,
    `series_id` (None when the row is no merged row), `values` ({year: number or None}, None for banner / header / gap).
    line / total: `restated` {year: original value or None}, `chosen` {year: {set_by, origin}} (a user's choice); only for
    years the row shows a figure in.
    total: `calculated`, and when calculated `parts` [{series_id, sign}], `calculated_years` (years the parts add up to a figure, the
    figures of the "As calculated" row), `calculated_only` (years where the reports print no total at all: the main value is the
    calculated one) and `reported_only` (years where no part has a figure or the stating report stored no breakdown: only the printed total is shown and there is no check).
    calculated / check: `of` (the total's series_id). check / balance_check: `ok` {year: whether |difference| is within rounding} and
    `tolerance`. check: `notes` {year: text} for a year whose stored breakdown does not add up to its own printed total (status MISMATCH): the
    reason the check cannot match (an extraction or OCR error, not a merge error); shown as a tooltip / a cell comment in the xlsx.
    balance_check: `refs` {assets, funding}: the series_ids of the two totals it subtracts (per-share totals are never one of them)."""
    out: List[Dict[str, Any]] = []
    by_id = {r["series_id"]: r for _, rs in sections for r in rs if r.get("series_id") is not None}
    shown: Dict[Any, Dict[str, Optional[float]]] = {}          # series_id -> the figure per year the sheet shows on its row
    spec_of: Dict[Any, Dict[str, Any]] = {}

    def merged(r: Dict[str, Any]) -> Dict[str, Optional[float]]:
        vals = r.get("values") or {}
        return {str(y): _number(vals.get(str(y))) for y in years}

    def marks(r: Dict[str, Any], vals: Optional[Dict[str, Optional[float]]] = None) -> Dict[str, Any]:
        """`restated` / `chosen` of a row, only for the years the row shows a figure in (`vals`, default the merged values)."""
        vals = merged(r) if vals is None else vals
        shown_years = {str(x) for x in years if vals.get(str(x)) is not None}
        restated = {str(y): (info or {}).get("original_value") for y, info in (r.get("restatement_variances") or {}).items()
                    if info and str(y) in shown_years}
        chosen = {str(y): {"set_by": c.get("set_by"), "origin": c.get("origin")} for y, c in (r.get("overrides") or {}).items()
                  if c and str(y) in shown_years}
        return {"restated": restated, "chosen": chosen}

    def add(kind: str, name: str, depth: int, r: Optional[Dict[str, Any]] = None, **extra) -> Dict[str, Any]:
        if kind in ("banner", "header", "line", "total") and name[:1].islower():
            name = name[:1].upper() + name[1:]                  # a name never starts with a lower-case letter ("equity:" reads "Equity:")
        spec = {"kind": kind, "name": name, "depth": depth,
                "unit": ((r or {}).get("unit") or "") if kind in ("line", "total", "calculated", "check") else "",
                "series_id": (r or {}).get("series_id") if kind in ("line", "total") else None,
                "values": merged(r) if (r is not None and kind in ("line", "total")) else None, **extra}
        out.append(spec)
        if kind in ("line", "total") and r is not None and r.get("series_id") is not None:
            shown[r["series_id"]] = spec["values"]
            spec_of[r["series_id"]] = spec
        return spec

    def line(name: str, r: Optional[Dict[str, Any]], depth: int, kind: str) -> Dict[str, Any]:
        extra = marks(r) if (r is not None and kind in ("line", "total")) else {}
        return add(kind, name, depth, r, **extra)

    def total(node: Dict[str, Any], name: str, depth: int) -> None:
        r = node["row"]
        parts = _formula_parts(node)
        if parts is None:                                    # no usable breakdown: keep the merged value
            line(name, r, depth, "total")["calculated"] = False
            return
        calc: Dict[str, Optional[float]] = {}                # the total added up from its parts (None: no part has a figure that year)
        bd_row = r.get("breakdown") or {}
        by_year = bd_row.get("by_year") or {}
        per_year_format = "by_year" in bd_row                # a merge that keeps what each report printed (older merges: the union for every year)
        printed_total = merged(r)                            # the total as the reports state it
        year_parts: Dict[str, List[Dict[str, Any]]] = {}     # the parts the report that states the year printed (when it has a breakdown)
        for y in years:
            key = str(y)
            printed_parts = (by_year.get(key) or {}).get("components")
            if printed_parts:
                # the year is added up from the breakdown its own report printed: each part's figure for the year, a part in no
                # series as printed; a minus-signed part is subtracted as -ABS()
                total_sum, has_figure = 0.0, False
                for c in printed_parts:
                    sid, sign = c.get("series_id"), (-1 if (c.get("sign") or 1) < 0 else 1)
                    if sid in shown:
                        v = shown[sid].get(key)
                    elif sid in by_id:
                        v = _number((by_id[sid].get("values") or {}).get(key))
                    else:
                        v = _number(c.get("value"))          # in no series, or its series is not on this sheet: as printed
                    if v is not None:
                        has_figure = True
                        total_sum += v if sign > 0 else -abs(v)
                if has_figure:
                    calc[key] = total_sum
                    year_parts[key] = [{k: c[k] for k in ("series_id", "sign", "field", "value") if k in c} for c in printed_parts]
                else:
                    calc[key] = None
                continue
            if per_year_format and key not in by_year and printed_total[key] is not None:
                calc[key] = None                             # the report that states the year stored no breakdown: nothing to check against
                continue
            # a stored breakdown that does not add up (MISMATCH), a year no report prints a total for, or an older merge: the union of the parts
            printed = any(_number((by_id[s].get("values") or {}).get(key)) is not None for s, _ in parts if s in by_id)
            refs = [(sign, s) for s, sign in parts if s in shown]
            if not printed or not refs:
                calc[key] = None                             # older reports print the total without its breakdown: nothing to add up
                continue
            calc[key] = sum((shown[s].get(key) or 0.0) if sign > 0 else -abs(shown[s].get(key) or 0.0) for sign, s in refs)
        if all(v is None for v in calc.values()):            # no year has a breakdown to add up: no calculated / check rows at all
            line(name, r, depth, "total")["calculated"] = False
            return
        spec = line(name, r, depth, "total")
        reported = dict(spec["values"])                      # the total as the reports state it (a user's chosen disclosure included)
        # The figure the reports print is the main value; the calculated one stands in only for a year no report prints a total for.
        main = {str(y): (reported[str(y)] if reported[str(y)] is not None else calc[str(y)]) for y in years}
        spec.update(calculated=True, parts=[{"series_id": s, "sign": sign} for s, sign in parts], parts_by_year=year_parts, values=main,
                    calculated_years=[y for y in years if calc[str(y)] is not None],
                    calculated_only=[y for y in years if calc[str(y)] is not None and reported[str(y)] is None],
                    reported_only=[y for y in years if calc[str(y)] is None and reported[str(y)] is not None], **marks(r, main))
        shown[r["series_id"]] = main
        # under it: the total added up from its parts, then calculated minus reported, then a gap
        add("calculated", "As calculated", depth + 1, r, values=calc, of=r.get("series_id"))
        diff = {str(y): (calc[str(y)] - reported[str(y)]) if (calc[str(y)] is not None and reported[str(y)] is not None) else None
                for y in years}
        tol = _tolerance(r)
        notes = {str(y): f"The breakdown printed in the {by_year[str(y)].get('report')} report does not add up to its own printed total (extraction or OCR), so this check cannot match."
                 for y in years if diff[str(y)] is not None and (by_year.get(str(y)) or {}).get("status") == "MISMATCH"}
        add("check", "Check (calculated - reported)", depth + 1, r, values=diff, of=r.get("series_id"), tolerance=tol, notes=notes,
            ok={str(y): abs(v) <= tol for y, v in ((y, diff[str(y)]) for y in years) if v is not None})
        add("gap", "", 0)

    def emit_balance(node: Dict[str, Any], depth: int, root: bool) -> None:
        r, kids = node["row"], node["kids"]
        if not kids:
            line(r.get("field") or "", r, depth, "line")
            return
        bare = _bare(r.get("field"))
        child_depth = depth
        if root:
            line(bare.upper(), None, 0, "banner")
        elif not all(k["kids"] for k in kids):           # a group with lines of its own gets a header over them
            line(bare if bare.endswith(":") else bare + ":", None, depth, "header")     # a heading over its lines reads "Cash total:"
            child_depth = depth + 1
        for k in kids:
            emit_balance(k, child_depth, False)
        own = str(r.get("field") or "")
        total(node, own if re.search(r"\btotal\b", own, re.I) else "Total " + bare, depth)

    def emit_flow(node: Dict[str, Any], depth: int) -> None:
        r, kids = node["row"], node["kids"]
        if not kids:
            line(r.get("field") or "", r, depth, "line")
            return
        for k in kids:                                   # a sub-total stays at this level; the group's own lines are indented
            emit_flow(k, depth if k["kids"] else depth + 1)
        total(node, r.get("field") or "", depth)

    def balance_check(trees: List[Dict[str, Any]]) -> None:
        """The Balance Sheet's last check: total assets minus total equity and liabilities."""
        low = lambda r: str(r.get("field") or "").lower()          # noqa: E731
        amounts = [n["row"] for n in trees if not _is_per_share(n["row"])]      # "Net Asset Value Per Share" is no assets total
        assets_c = [r for r in amounts if "asset" in low(r) and "liabilit" not in low(r)]
        funding_c = [r for r in amounts if "liabilit" in low(r)]
        assets = next((r for r in assets_c if re.match(r"\s*total\s+assets", low(r))), assets_c[0] if assets_c else None)
        funding = next((r for r in funding_c if re.match(r"\s*total\s+(equity\s+and\s+|owners?'?s?\s+equity\s+and\s+)?liabilit", low(r))), funding_c[0] if funding_c else None)
        if assets is None or funding is None:
            return
        a, f = assets["series_id"], funding["series_id"]
        vals: Dict[str, Optional[float]] = {}
        for y in years:
            key = str(y)
            has = _number((assets.get("values") or {}).get(key)) is not None and _number((funding.get("values") or {}).get(key)) is not None
            vals[key] = ((shown[a].get(key) or 0.0) - (shown[f].get(key) or 0.0)) if has else None
        tol = _tolerance(assets)
        add("balance_check", f"Check: {spec_of[a]['name']} - {spec_of[f]['name']} (0 when it balances; up to 5 units = rounding)", 0, assets,
            unit=assets.get("unit") or "", values=vals, refs={"assets": a, "funding": f}, tolerance=tol,
            ok={k: abs(v) <= tol for k, v in vals.items() if v is not None})
        add("gap", "", 0)

    per_share: List[Dict[str, Any]] = []
    for banner, rows in sections:
        if banner:
            line(banner, None, 0, "banner")
        nested = _nest(build_tree(rows))
        trees = [n for n in nested if n["kids"]]
        loose = [n["row"] for n in nested if not n["kids"]]
        for n in trees:
            emit_balance(n, 0, True) if style == "balance" else emit_flow(n, 0)
        if style == "balance":
            balance_check(trees)
            for title, picks in (("PER SHARE DATA", [r for r in loose if _is_per_share(r)]),
                                 ("OTHER LINES", [r for r in loose if not _is_per_share(r)])):
                if picks:
                    line(title, None, 0, "banner")
                    for r in picks:
                        line(r.get("field") or "", r, 1, "line")
        else:
            for r in loose:
                if _is_per_share(r):
                    per_share.append(r)
                else:
                    line(r.get("field") or "", r, 0, "line")
    if per_share:
        line("PER SHARE DATA", None, 0, "banner")
        for r in per_share:
            line(r.get("field") or "", r, 1, "line")
    return out


def _render_sheet(ws, rows: List[Dict[str, Any]], years: List[int]) -> None:
    """Writes one sheet of `_layout_rows` into an openpyxl sheet: the same figures as Excel formulas (totals and checks), the layout's
    styles. Lines are blue, totals bold on grey, restated cells yellow with a comment, a total only calculated (no printed total that year)
    blue with a comment, "As calculated" and check rows grey italic."""
    ws.append(["Line Item", "Unit"] + [str(y) for y in years])
    for cell in ws[1]:
        cell.font = Font(bold=True, color="FFFFFF")
        cell.fill = _HEADER_FILL
        cell.alignment = Alignment(horizontal="left" if cell.column <= 2 else "right")
    row_of: Dict[Any, int] = {}                     # series_id -> sheet row of its total (or line) row
    pending: List[Tuple[int, Dict[str, Any]]] = []  # calculated totals: their formulas are written once every row has its place
    last = 2 + len(years)

    def put_values(row_no: int, spec: Dict[str, Any], bold: bool, blue: bool, fill: Optional[PatternFill] = None) -> None:
        fmt = _standard_format(spec.get("unit"))
        restated, chosen = spec.get("restated") or {}, spec.get("chosen") or {}
        for i, y in enumerate(years):
            key = str(y)
            cell = ws.cell(row=row_no, column=3 + i, value=(spec.get("values") or {}).get(key))
            cell.number_format = fmt
            cell.font = Font(bold=bold, color=_INPUT_BLUE if blue else None)
            if fill:
                cell.fill = fill
            if cell.value is None:
                continue
            if key in restated:
                cell.fill = _RESTATED_FILL
                orig = restated[key]
                cell.comment = Comment(f"Restated. First printed as {orig:,.2f}" if orig is not None else "Restated", "Financial Studio")
            pick = chosen.get(key)
            if pick:
                cell.comment = Comment("Chosen by " + str(pick.get("set_by") or "a user")
                                       + (" (a disclosure added by a user)" if pick.get("origin") == "USER_ADDED" else ""), "Financial Studio")

    for spec in rows:
        kind = spec["kind"]
        if kind == "gap":
            ws.append([])                            # a one-row gap after every check row
            continue
        ws.append([spec["name"], spec.get("unit") or ""])
        n = ws.max_row
        a = ws.cell(row=n, column=1)
        if kind in ("banner", "header", "line", "total"):
            a.alignment = Alignment(indent=min(spec["depth"], MAX_INDENT))
        if kind == "banner":
            a.font = Font(bold=True, color="1F3864")
            for c in range(1, last + 1):
                ws.cell(row=n, column=c).fill = _BANNER_FILL
        elif kind == "header":
            a.font = Font(bold=True)
        elif kind == "line":
            put_values(n, spec, False, True)
            row_of[spec["series_id"]] = n
        elif kind == "total":
            for c in range(1, last + 1):
                ws.cell(row=n, column=c).fill = _TOTAL_FILL
                ws.cell(row=n, column=c).border = _TOTAL_BORDER
            a.font = Font(bold=True)
            row_of[spec["series_id"]] = n
            put_values(n, spec, True, False, _TOTAL_FILL)           # the figures the reports print (the formulas of calculated-only years follow)
            if spec.get("calculated"):
                pending.append((n, spec))
        elif kind in ("calculated", "check"):
            a.alignment = Alignment(indent=min(spec["depth"], MAX_INDENT))
            total_row = row_of[spec["of"]]
            if kind == "check":
                calculated_row = n - 1                              # the "As calculated" row right above
                for i, y in enumerate(years):
                    col = get_column_letter(3 + i)
                    cell = ws.cell(row=n, column=3 + i, value=f"={col}{calculated_row}-{col}{total_row}" if spec["values"].get(str(y)) is not None else None)
                    cell.number_format = _standard_format(spec.get("unit"))
                    note = (spec.get("notes") or {}).get(str(y))
                    if note and cell.value is not None:
                        cell.comment = Comment(note, "Financial Studio")
            for c in range(1, last + 1):
                cell = ws.cell(row=n, column=c)
                cell.font = Font(italic=True, color="808080")
                if cell.fill.fill_type is None:
                    cell.fill = _TOTAL_FILL
        elif kind == "balance_check":
            a_row, f_row = row_of[spec["refs"]["assets"]], row_of[spec["refs"]["funding"]]
            for i, y in enumerate(years):
                col = get_column_letter(3 + i)
                cell = ws.cell(row=n, column=3 + i, value=f"={col}{a_row}-{col}{f_row}" if spec["values"].get(str(y)) is not None else None)
                cell.number_format = _standard_format(spec.get("unit"))
            for c in range(1, last + 1):
                ws.cell(row=n, column=c).font = Font(italic=True, color="808080")

    calc_sids = {s["series_id"] for _, s in pending}
    for n, spec in pending:                            # the formulas, now that every row has its place: "As calculated" is the row below the total
        for i, y in enumerate(years):
            col = get_column_letter(3 + i)
            if y not in spec["calculated_years"]:
                continue                                 # no part has a figure for this year: leave the cell empty
            year_list = (spec.get("parts_by_year") or {}).get(str(y))      # the parts the year's own report printed, else the union
            plist = year_list if year_list is not None else spec["parts"]
            refs = []
            literal = False
            for p in plist:
                sign = -1 if (p.get("sign") or 1) < 0 else 1
                if p["series_id"] in row_of:
                    refs.append((sign, f"{col}{row_of[p['series_id']]}"))
                elif _number(p.get("value")) is not None:      # in no series, or its series is not on this sheet: as printed
                    refs.append((sign, f"({_number(p['value']):.0f})" if float(p["value"]).is_integer() else f"({_number(p['value'])!r})"))
                    literal = True
            if not refs:
                continue
            if all(sign > 0 for sign, _ in refs):
                joined = ",".join(ref for _, ref in refs)
                formula = ("=" + "+".join(ref for _, ref in refs)) if (literal or all(p["series_id"] in calc_sids for p in plist)) else f"=SUM({joined})"
            else:                                        # a deducted part subtracts its size, however it is printed
                formula = "=" + "".join((f"+{ref}" if sign > 0 else f"-ABS({ref})") for sign, ref in refs).lstrip("+")
            cell = ws.cell(row=n + 1, column=3 + i, value=formula)
            cell.number_format = _standard_format(spec.get("unit"))
            if y in spec["calculated_only"]:              # no report prints a total for this year: the total shows the calculated figure
                top = ws.cell(row=n, column=3 + i, value=f"={col}{n + 1}")
                top.number_format = _standard_format(spec.get("unit"))
                top.font = Font(bold=True)
                top.fill = _CALC_ONLY_FILL
                top.border = _TOTAL_BORDER
                top.comment = Comment("Calculated from its parts: the reports print no total for this year", "Financial Studio")
    ws.freeze_panes = "C2"
    ws.column_dimensions["A"].width = 58
    ws.column_dimensions["B"].width = 11
    for i in range(len(years)):
        ws.column_dimensions[get_column_letter(3 + i)].width = 18


def standard_layout(rows: List[Dict[str, Any]], years: Optional[List[int]] = None) -> Dict[str, Any]:
    """The standard layout as data: {"years": [...], "sheets": [{name, statement, scope, style, rows}]}: Balance Sheet, Income
    Statement, Cash Flow, Changes in Equity and Notes & Others (only those with rows), a statement with both scopes as one sheet per
    scope ("Income Statement - Consolidated"). `rows` are the merged rows. The Balance Sheet is in the "balance" style, the others in
    the "flow" style; Notes & Others has a banner per statement type. The row kinds and fields are described in `_layout_rows`;
    totals show the printed figure, with their "As calculated" and check rows. `build_standard_workbook` writes the same layout."""
    if years is None:
        years = sorted({int(y) for r in rows for y in (r.get("values") or {}) if str(y).isdigit()})
    grouped: Dict[str, List[Dict[str, Any]]] = {}
    for r in rows:
        grouped.setdefault(_sheet_name(r.get("statement_type") or ""), []).append(r)
    sheets: List[Dict[str, Any]] = []
    for name in [n for n, _ in SHEETS] + [OTHER_SHEET]:
        scopes: Dict[str, List[Dict[str, Any]]] = {}
        for r in grouped.get(name) or []:
            scopes.setdefault((r.get("scope") or "CONSOLIDATED").upper(), []).append(r)
        for scope in sorted(scopes, key=lambda s: 0 if s == "CONSOLIDATED" else 1):
            if name == OTHER_SHEET:                      # one section per statement type, in the order they first appear
                types: Dict[str, List[Dict[str, Any]]] = {}
                for r in scopes[scope]:
                    types.setdefault(r.get("statement_type") or "OTHER", []).append(r)
                sections = [(t.replace("_", " ").upper(), rs) for t, rs in types.items()]
            else:
                sections = [(None, scopes[scope])]
            style = "balance" if name == _STANDARD_SHEET else "flow"
            sheets.append({"name": _sheet_title(name, scope, len(scopes) > 1), "statement": name, "scope": scope, "style": style,
                           "rows": _layout_rows(sections, years, style)})
    return {"years": years, "sheets": sheets}


def layout_checks(layout: Dict[str, Any]) -> Dict[str, int]:
    """{"total": check cells, "off": those outside rounding} over every check and balance-check row of a standard layout."""
    total = off = 0
    for sheet in layout["sheets"]:
        for r in sheet["rows"]:
            if r["kind"] in ("check", "balance_check"):
                for y, ok in (r.get("ok") or {}).items():
                    total += 1
                    off += 0 if ok else 1
    return {"total": total, "off": off}


def build_standard_workbook(rows: List[Dict[str, Any]], years: Optional[List[int]] = None) -> bytes:
    """The "standard" workbook: the sheets of `standard_layout` (Balance Sheet, Income Statement, Cash Flow, Changes in Equity and
    Notes & Others, only those with rows; one sheet per scope when a statement has both), each laid out and styled by
    `_render_sheet`, with totals and checks as Excel formulas. `rows` are the merged rows. The merged workbook
    (`build_merged_workbook`) is a different, unchanged export."""
    layout = standard_layout(rows, years)
    wb = Workbook()
    wb.remove(wb.active)
    for sheet in layout["sheets"]:
        _render_sheet(wb.create_sheet(sheet["name"]), sheet["rows"], layout["years"])
    if not wb.sheetnames:
        wb.create_sheet(_STANDARD_SHEET)
    buf = io.BytesIO()
    wb.save(buf)
    return buf.getvalue()


def build_raw_workbook(rows: List[Dict[str, Any]], raw_columns: List[Dict[str, Any]]) -> bytes:
    """The raw table as a workbook: one sheet per statement (the same sheets as the merged one), two header rows
    (the report, e.g. "FY 2022-23", over its columns; each column's year, or its printed date for a restated
    column), then one row per printed line in the table's order. `rows` are the raw rows (`raw_rows`) and
    `raw_columns` their columns (`raw_columns`); a cell's value is `row["values"][column["key"]]`."""
    grouped: Dict[str, List[Dict[str, Any]]] = {}
    for r in rows:
        grouped.setdefault(_sheet_name(r.get("statement_type") or ""), []).append(r)
    fixed = ["Field / Raw Line Item", "Scope", "Unit"]
    wb = Workbook()
    wb.remove(wb.active)
    for name in [n for n, _ in SHEETS] + [OTHER_SHEET]:
        sheet_rows = grouped.get(name)
        if not sheet_rows:
            continue
        ws = wb.create_sheet(name)
        ws.append(fixed + [c.get("group_header") or "" for c in raw_columns])
        ws.append([""] * len(fixed) + [c.get("header") or "" for c in raw_columns])
        for col in range(1, len(fixed) + 1):
            ws.merge_cells(start_row=1, start_column=col, end_row=2, end_column=col)
        start = len(fixed) + 1                       # merge each report's group header over its columns
        i = 0
        while i < len(raw_columns):
            j = i
            while j + 1 < len(raw_columns) and raw_columns[j + 1].get("group_header") == raw_columns[i].get("group_header"):
                j += 1
            if j > i:
                ws.merge_cells(start_row=1, start_column=start + i, end_row=1, end_column=start + j)
            i = j + 1
        for row in ws.iter_rows(min_row=1, max_row=2):
            for cell in row:
                cell.font = Font(bold=True, color="FFFFFF")
                cell.fill = _HEADER_FILL
                cell.alignment = Alignment(horizontal="left" if cell.column <= len(fixed) else "center", vertical="center")
        for k, c in enumerate(raw_columns):
            if c.get("restated"):
                ws.cell(row=2, column=start + k).fill = PatternFill("solid", fgColor="8A6D1D")
        for r in sheet_rows:
            values = r.get("values") or {}
            scope = (r.get("scope") or "CONSOLIDATED").upper()
            ws.append([r.get("field") or "", SCOPE_LABELS.get(scope, scope.title()), r.get("unit") or ""]
                      + [_number(values.get(c["key"])) for c in raw_columns])
            fmt = _number_format(r.get("unit"))
            for k in range(len(raw_columns)):
                ws.cell(row=ws.max_row, column=start + k).number_format = fmt
        ws.freeze_panes = ws.cell(row=3, column=start)
        ws.column_dimensions["A"].width = 58
        ws.column_dimensions["B"].width = 13
        ws.column_dimensions["C"].width = 11
        for k in range(len(raw_columns)):
            ws.column_dimensions[get_column_letter(start + k)].width = 16
    if not wb.sheetnames:
        wb.create_sheet("Raw")
    buf = io.BytesIO()
    wb.save(buf)
    return buf.getvalue()
