path: root/build-aux/jlcpcb-bom-tool

#!/usr/bin/env python3
# build-aux/jlcpcb-bom-tool - CLI wrapper for JLCPCB's web-based BOM tool (https://jlcpcb.com/parts/bom-tool/)
#
# Copyright (C) 2025  Luke T. Shumaker <lukeshu@lukeshu.com>
# SPDX-License-Identifier: AGPL-3.0-or-later


import argparse
import dataclasses
import enum
import io
import os
import os.path
import re
import sys
import time
import typing

import xlrd
import xlwt

common = os.path.abspath(os.path.dirname(__file__))
if common not in sys.path:
    sys.path.insert(0, common)

import ansiterm  # pylint: disable=wrong-import-position
import jlcpcb_api  # pylint: disable=wrong-import-position

# https://github.com/pylint-dev/pylint/issues/9258
if typing.TYPE_CHECKING:
    print(_HAS_DEFAULT_FACTORY)  # type: ignore

# Simple utilities #############################################################


def normalize_designator(designator: str) -> str:
    return ",".join([d.strip() for d in designator.upper().split(",")])


# Data model types #############################################################


class USD:
    centicents: int

    def __init__(self, usd: float = 0) -> None:
        self.centicents = int(usd * 10000)

    def __float__(self) -> float:
        return self.centicents / 10000.0

    def __str__(self) -> str:
        return f"${float(self):,.4f}"

    def __repr__(self) -> str:
        return f"USD({float(self):,.4f})"

    def __add__(self, other: "USD") -> "USD":
        ret = USD()
        ret.centicents = self.centicents + other.centicents
        return ret

    def __mul__(self, x: int) -> "USD":
        ret = USD()
        ret.centicents = self.centicents * x
        return ret

    def __rmul__(self, x: int) -> "USD":
        ret = USD()
        ret.centicents = self.centicents * x
        return ret

    def __bool__(self) -> bool:
        return bool(self.centicents)

    def __lt__(self, other: typing.Any) -> bool:
        assert isinstance(other, USD)
        return self.centicents < other.centicents

    def __le__(self, other: typing.Any) -> bool:
        assert isinstance(other, USD)
        return self.centicents <= other.centicents

    def __eq__(self, other: typing.Any) -> bool:
        assert isinstance(other, USD)
        return self.centicents == other.centicents

    def __ne__(self, other: typing.Any) -> bool:
        assert isinstance(other, USD)
        return self.centicents != other.centicents

    def __gt__(self, other: typing.Any) -> bool:
        assert isinstance(other, USD)
        return self.centicents > other.centicents

    def __ge__(self, other: typing.Any) -> bool:
        assert isinstance(other, USD)
        return self.centicents >= other.centicents


def usd_sum(iterable: typing.Iterable[USD], /) -> USD:
    ret = USD()
    ret.centicents = sum(usd.centicents for usd in iterable)
    return ret


class PCBA(enum.Enum):
    economical = enum.auto()
    standard_singlesided = enum.auto()
    standard_doublesided = enum.auto()


@dataclasses.dataclass
class Price:
    beg_qty: int
    end_qty: int
    unit_price: USD


@dataclasses.dataclass
class UserRow:
    """User-supplied BOM"""

    comment: str
    designator: str
    footprint: str
    lcsc: str

    @property
    def qty(self) -> int:
        return len(self.designator.split(","))

    @property
    def footprint_short(self) -> str:
        if ":" in self.footprint:
            return self.footprint.split(":", 1)[1]
        return self.footprint


@dataclasses.dataclass
class MatchRow:
    sum_user_qty: int

    # Match ID
    lcsc: str
    id: int
    name: str
    description: str
    footprint: str
    warnings: set[str]

    # Quantities/Pricing
    library_type: jlcpcb_api.BOMFileMatchLibraryType
    stock: int
    moq: int
    attrition: int
    unit_assembly_fee: USD
    unit_prices: list[Price]


@dataclasses.dataclass
class Row:
    user: UserRow
    match: MatchRow | None

    # PCBA costs ###############################################################
    #
    # https://jlcpcb.com/help/article/pcb-assembly-price

    def component_pcba_qty(self, pcba_qty: int) -> int:
        if self.match is None:
            return pcba_qty * self.user.qty
        return pcba_qty * self.match.sum_user_qty

    def pcba_fixed_price(self, pcba: PCBA) -> USD:
        if self.match is None:
            return USD(0.0)
        ret: USD
        match pcba:
            case PCBA.economical:
                match self.match.library_type:
                    case jlcpcb_api.BOMFileMatchLibraryType.basic:
                        ret = USD(0.00)
                    case jlcpcb_api.BOMFileMatchLibraryType.extended:
                        ret = USD(3.00)
            case PCBA.standard_singlesided | PCBA.standard_doublesided:
                ret = USD(1.50)
        return ret

    def pcba_unit_price(self, pcba: PCBA) -> USD:
        if self.match is None:
            return USD(0.0)

        ret = self.match.unit_assembly_fee
        match pcba:
            case PCBA.economical:
                # FIXME: include $0.0016/joint
                pass
            case PCBA.standard_singlesided | PCBA.standard_doublesided:
                # FIXME: include per-joint cost
                pass
        return ret

    # BOM costs ################################################################

    def component_order_qty(self, pcba_qty: int) -> int:
        if self.match is None:
            return 0
        return max(
            pcba_qty * self.match.sum_user_qty + self.match.attrition, self.match.moq
        )

    def bom_unit_price(self, pcba_qty: int) -> USD:
        if self.match is None:
            return USD(0.0)
        component_order_qty = self.component_order_qty(pcba_qty)

        ret: USD
        for price in self.match.unit_prices:
            if (price.beg_qty <= component_order_qty <= price.end_qty) or (
                price.end_qty < 0 and price.beg_qty <= component_order_qty
            ):
                ret = price.unit_price
        return ret

    def attrition_price(self, pcba_qty: int) -> USD:
        if self.match is None:
            return USD(0.0)
        return self.match.attrition * self.bom_unit_price(pcba_qty)

    # Aggregate costs ##########################################################

    def sum_unit_price(self, pcba: PCBA, pcba_qty: int) -> USD:
        if self.match is None:
            return USD(0.0)
        return self.match.sum_user_qty * (
            self.pcba_unit_price(pcba) + self.bom_unit_price(pcba_qty)
        )

    def moq_overhead(self, pcba_qty: int) -> USD:
        if self.match is None:
            return USD(0.0)
        overhead_qty = max(
            0, self.match.moq - self.component_pcba_qty(pcba_qty) - self.match.attrition
        )
        return overhead_qty * self.bom_unit_price(pcba_qty)

    def extended_price(self, pcba: PCBA, pcba_qty: int) -> USD:
        if self.match is None:
            return USD(0.0)
        a = (
            (self.component_order_qty(pcba_qty) * self.bom_unit_price(pcba_qty))
            + self.pcba_fixed_price(pcba)
            + (self.component_pcba_qty(pcba_qty) * self.pcba_unit_price(pcba))
        )
        b = (
            (self.sum_unit_price(pcba, pcba_qty) * pcba_qty)
            + self.pcba_fixed_price(pcba)
            + self.attrition_price(pcba_qty)
            + self.moq_overhead(pcba_qty)
        )
        assert a == b
        return a


@dataclasses.dataclass
class BOM:
    rows: list[Row]

    def pcba_startup_price(self, pcba: PCBA) -> USD:
        match pcba:
            case PCBA.economical:
                return USD(8.00 + 1.50 + 3.50)
            case PCBA.standard_singlesided:
                return USD(25.00 + 7.86 + 3.50)
            case PCBA.standard_doublesided:
                return USD(50.00 + 15.72 + 3.50)

    @property
    def deduped_rows(self) -> list[Row]:
        lcsc2row: dict[str, Row] = {}
        for row in self.rows:
            if row.match:
                lcsc2row[row.match.lcsc] = row
        return list(lcsc2row.values())

    def pcba_fixed_price(self, pcba: PCBA) -> USD:
        return usd_sum(
            row.pcba_fixed_price(pcba) for row in self.deduped_rows
        ) + self.pcba_startup_price(pcba)

    def sum_unit_price(self, pcba: PCBA, pcba_qty: int) -> USD:
        return usd_sum(row.sum_unit_price(pcba, pcba_qty) for row in self.deduped_rows)

    def attrition_price(self, pcba_qty: int) -> USD:
        return usd_sum(row.attrition_price(pcba_qty) for row in self.deduped_rows)

    def moq_overhead(self, pcba_qty: int) -> USD:
        return usd_sum(row.moq_overhead(pcba_qty) for row in self.deduped_rows)

    def extended_price(self, pcba: PCBA, pcba_qty: int) -> USD:
        a = self.pcba_startup_price(pcba) + usd_sum(
            row.extended_price(pcba, pcba_qty) for row in self.deduped_rows
        )
        b = (
            self.pcba_fixed_price(pcba)
            + (self.sum_unit_price(pcba, pcba_qty) * pcba_qty)
            + self.attrition_price(pcba_qty)
            + self.moq_overhead(pcba_qty)
        )
        assert a == b
        return a


################################################################################


def main() -> None:
    parser = argparse.ArgumentParser(
        prog=sys.argv[0],
        usage="JLCPCB_SESSION_ID=COOKIE_VALUE %(prog)s [options] BOM_FILENAME.xls",
        description="CLI wrapper for JLCPCB's web-based BOM tool (https://jlcpcb.com/parts/bom-tool/)",
    )
    parser.add_argument(
        "bom_filename",
        metavar="BOM_FILENAME",
        help="BOM file to upload to JLCPCB, must be in XLS format (not CSV or XLSX)",
    )
    parser.add_argument(
        "--output-format",
        required=True,
        metavar="FMT",
        choices=["term", "xls"],
        help="output format, valid values are 'term' (ANSI terminal rendering) and 'xls' (spreadsheet); written to stdout",
    )
    args = parser.parse_args(sys.argv[1:])
    if not args.bom_filename.endswith(".xls"):
        parser.error("argument BOM_FILENAME: must be a .xls file")
    if not os.environ.get("JLCPCB_SESSION_ID", ""):
        parser.error(
            "the following environment variables are required: JLCPCB_SESSION_ID"
        )

    session_id = os.environ["JLCPCB_SESSION_ID"]
    output_format = args.output_format
    bom_filename = args.bom_filename
    count = 5  # arbitrary, we'll re-calculate everything ourselves

    with open(bom_filename, "rb") as fh:
        bom_content = fh.read()
    orig_wb = xlrd.open_workbook(file_contents=bom_content)
    assert orig_wb.nsheets == 1
    orig_ws = orig_wb.sheet_by_index(0)

    matches = get_match(session_id, bom_filename, bom_content, count)

    print("", file=sys.stderr)

    joined = join_boms(orig_ws, matches, count)

    print("Generating report...", file=sys.stderr)
    report = generate_report(joined)

    print("Rendering to stdout...", file=sys.stderr)
    match output_format:
        case "term":
            out_bytes = render_term(report)
        case "xls":
            out_bytes = render_xls(report)
    sys.stdout.buffer.write(out_bytes)


def get_match(
    session_id: str, bom_filename: str, bom_content: bytes, count: int
) -> jlcpcb_api.BOMFileMatch:
    client = jlcpcb_api.Client(
        session_id=session_id,
        ua_string="jlcpcb-bom-tool (https://git.lukeshu.com/sbc-harness-hardware/tree/build-aux/jlcpcb-bom-tool)",
    )
    bomclient = client.core_platform.bom_match_record_controller

    print("API: Uploading BOM...", file=sys.stderr)
    bomfile_name = os.path.basename(bom_filename)
    bomfile_id = bomclient.upload_bomfile(bomfile_name, bom_content)

    print("API: Waiting for JLCPCB to parse BOM...", file=sys.stderr)
    while bomclient.bomfile_get_parse_status(bomfile_id) == 0:
        # This is now long the webui sleeps for when polling.
        time.sleep(1.5)

    print("API: Setting count...", file=sys.stderr)
    bomclient.bomfile_update_details(bomfile_id, {"editPatchNumber": count})

    print("API: Getting matched BOM...", file=sys.stderr)
    matches = bomclient.bomfile_get_match(bomfile_id, count)

    print("API: Deleting BOM from JLCPCB...", file=sys.stderr)
    bomclient.bomfile_update(bomfile_id, bomfile_name, {"delete": True})

    print("API: Done.", file=sys.stderr)

    return matches


def join_boms(
    orig_ws: xlrd.sheet.Sheet, _matches: jlcpcb_api.BOMFileMatch, count: int
) -> BOM:
    matches = [
        *_matches.jlcStock,
        *_matches.jlcBuy,
        *_matches.overseasShop,
        *_matches.notSelect,
        *_matches.notMatch,
    ]
    lcsc2matches: dict[str, list[jlcpcb_api.BOMFileMatchComponent]] = {}
    designator2match: dict[str, jlcpcb_api.BOMFileMatchComponent] = {}
    for match in matches:
        if match.designator == "DESIGNATOR":
            # Unlike when you're uploading a BOM for PCBA, when you
            # upload a BOM to the bom-tool, it intereprets the header
            # row as a component.  Given that there is a header row in
            # their example BOM template, this is a bug, and I suspect
            # they'll fix it.  So filter the header out here, instead
            # of filtering it out before we upload.
            continue
        if not match.designator:
            continue
        if match.componentCode:
            if match.componentCode not in lcsc2matches:
                lcsc2matches[match.componentCode] = []
            lcsc2matches[match.componentCode].append(match)
        designator2match[normalize_designator(match.designator)] = match

    rows: list[Row] = []
    for y in range(orig_ws.nrows):
        if orig_ws.cell_value(y, 0) == "Comment":
            continue
        row = Row(
            user=UserRow(
                comment=str(orig_ws.cell_value(y, 0)).strip(),
                designator=normalize_designator(str(orig_ws.cell_value(y, 1))),
                footprint=str(orig_ws.cell_value(y, 2)).strip(),
                lcsc=str(orig_ws.cell_value(y, 3)).strip(),
            ),
            match=None,
        )
        if not row.user.designator:
            continue

        match = designator2match[row.user.designator]
        if match.componentCode:
            assert match.componentModel is not None
            assert match.componentSpecification is not None
            assert match.componentLibraryType is not None
            assert match.componentDescription is not None
            assert match.componentBrand is not None
            assert match.componentName is not None
            assert match.lcscComponentId is not None
            assert match.recommendNumber is not None
            assert match.buyNumber is not None
            assert match.minPurchaseNum is not None
            assert match.overseasStockCount is not None
            assert match.prices is not None
            assert match.unitPrice is not None
            assert match.totalMoney is not None
            assert match.lossNumber is not None
            assert match.leastNumber is not None
            assert match.leastPatchNumber is not None
            assert match.encapsulationNumber is not None
            assert match.checked is not None
            assert match.secondTypeNameEn is not None
            assert match.matchWay is not None
            assert match.isBuyComponent is not None
            assert match.matchSource is not None
            assert match.needAuditFlag is not None
            # If any of these asserts breaks, then I should re-think some code.
            assert match.leastNumber == 0
            assert match.designatorNum == row.user.qty
            assert match.recommendNumber == max(
                sum(m.designatorNum for m in lcsc2matches[match.componentCode]) * count
                + match.lossNumber,
                match.minPurchaseNum,
                match.leastPatchNumber,
            )
            assert match.buyNumber == match.recommendNumber
            for price in match.prices:
                assert price.lcscPriceRangeCoefficient == 1

            warnings: set[str] = set()
            if match.multipleRowTipMessage:
                warnings.add(match.multipleRowTipMessage)
            if match.multipleBomRowTipMessage:
                warnings.add(match.multipleBomRowTipMessage)

            row.match = MatchRow(
                sum_user_qty=sum(
                    m.designatorNum for m in lcsc2matches[match.componentCode]
                ),
                # Match ID
                lcsc=match.componentCode,
                id=match.lcscComponentId,
                name=match.componentName,
                description=match.componentDescription,
                footprint=match.componentSpecification,
                warnings=warnings,
                # Quantities/Pricing
                library_type=match.componentLibraryType,
                stock=match.overseasStockCount,
                moq=max(match.minPurchaseNum, match.leastPatchNumber),
                attrition=match.lossNumber,
                unit_assembly_fee=USD(match.specialComponentFee or 0.0),
                unit_prices=[
                    Price(
                        beg_qty=p.startNumber,
                        end_qty=p.endNumber,
                        unit_price=USD(p.productPrice),
                    )
                    for p in match.prices
                ],
            )
        rows.append(row)
    return BOM(rows=rows)


# Output types #################################################################


class Align(enum.Enum):
    left = enum.auto()
    center = enum.auto()
    right = enum.auto()


class Color(enum.Enum):
    default = enum.auto()
    separator = enum.auto()
    red = enum.auto()


class Cell:
    content: str | int | USD
    colspan: int

    bold: bool
    fg_color: Color
    bg_color: Color
    align: Align

    def __init__(
        self,
        content: str | int | USD,
        /,
        *,
        colspan: int = 1,
        bold: bool = False,
        fg_color: Color = Color.default,
        bg_color: Color = Color.default,
        align: Align | None = None,
    ) -> None:
        self.content = content
        self.colspan = colspan

        self.bold = bold
        self.fg_color = fg_color
        self.bg_color = bg_color
        if align is None:
            match content:
                case str():
                    align = Align.left
                case int():
                    align = Align.right
                case USD():
                    align = Align.right
        self.align = align

    @property
    def text(self) -> str:
        match self.content:
            case str():
                return self.content.replace("℃", "°C")
            case int():
                if self.content == 0:
                    return ""
                return f"{self.content:,}"
            case USD():
                if self.content.centicents == 0:
                    return ""
                return str(self.content)

    @classmethod
    def separator(cls) -> "Cell":
        return Cell("", bg_color=Color.separator)


def generate_report(bom: BOM) -> list[list[Cell] | typing.Literal["-"]]:
    # qtys: list[int] = [5, 10, 50, 100, 500, 1000, 5000, 10000, 50000, 100000]
    qtys: list[int] = [5, 50, 500]
    pcbas: list[PCBA] = [PCBA.economical, PCBA.standard_singlesided]

    # Group these for comparison ###############################################

    def pcba_hdrs(pcba: PCBA, pcba_qty: int) -> list[Cell]:
        ret = [
            Cell.separator(),
            Cell(f"bom_unit@{pcba_qty}", bold=True),
            Cell(f"sum_unit@{pcba_qty}", bold=True),
            Cell(f"loss_ext@{pcba_qty}", bold=True),
        ]
        if bom.moq_overhead(pcba_qty):
            ret += [
                Cell(f"moq_overhead@{pcba_qty}", bold=True),
            ]
        ret += [
            Cell(f"ext@{pcba_qty}", bold=True),
        ]
        return ret

    def pcba_body(irow: Row, pcba: PCBA, pcba_qty: int) -> list[Cell]:
        assert irow.match
        fg_color = Color.default
        if irow.component_order_qty(pcba_qty) > irow.match.stock:
            fg_color = Color.red
        ret = [
            Cell.separator(),
            Cell(irow.bom_unit_price(pcba_qty), fg_color=fg_color),
            Cell(irow.sum_unit_price(pcba, pcba_qty), fg_color=fg_color, bold=True),
            Cell(irow.attrition_price(pcba_qty), fg_color=fg_color),
        ]
        if bom.moq_overhead(pcba_qty):
            ret += [
                Cell(irow.moq_overhead(pcba_qty), fg_color=fg_color),
            ]
        ret += [
            Cell(irow.extended_price(pcba, pcba_qty), fg_color=fg_color, bold=True),
        ]
        return ret

    def pcba_totals(pcba: PCBA, pcba_qty: int) -> list[Cell]:
        fg_color = Color.default
        if any(
            row.component_order_qty(pcba_qty) > row.match.stock
            for row in bom.rows
            if row.match
        ):
            fg_color = Color.red
        ret = [
            Cell.separator(),
            Cell(""),  # bom_unit
            Cell(bom.sum_unit_price(pcba, pcba_qty), fg_color=fg_color, bold=True),
            Cell(bom.attrition_price(pcba_qty), fg_color=fg_color),
        ]
        if bom.moq_overhead(pcba_qty):
            ret += [
                Cell(bom.moq_overhead(pcba_qty), fg_color=fg_color),
            ]
        ret += [
            Cell(bom.extended_price(pcba, pcba_qty), fg_color=fg_color, bold=True),
        ]
        return ret

    # Header ###################################################################
    hdr0: list[Cell] = [
        Cell(
            "original uploaded BOM",
            colspan=5,
            align=Align.center,
            bold=True,
        ),
        Cell.separator(),
        Cell("matched component", colspan=7, align=Align.center, bold=True),
        Cell.separator(),
        Cell(
            "Economical PCBA",
            colspan=2 + sum(len(pcba_hdrs(PCBA.economical, q)) for q in qtys),
            align=Align.center,
            bold=True,
        ),
        Cell.separator(),
        Cell(
            "Standard single-sided PCBA",
            colspan=2 + sum(len(pcba_hdrs(PCBA.standard_singlesided, q)) for q in qtys),
            align=Align.center,
            bold=True,
        ),
        Cell.separator(),
    ]
    hdr1: list[Cell] = [
        # orig
        Cell("comment", bold=True),
        Cell("designator", bold=True),
        Cell("footprint", bold=True),
        Cell("lcsc", bold=True),
        Cell("qty", bold=True),
        # match
        Cell.separator(),
        Cell("lcsc", bold=True),
        Cell("name", bold=True),
        Cell("description", bold=True),
        Cell("footprint", bold=True),
        Cell("stock", bold=True),
        Cell("moq", bold=True),
        Cell("loss", bold=True),
    ]
    for pcba in pcbas:
        hdr1 += [
            Cell.separator(),
            Cell("pcba_fixed", bold=True),
            Cell("pcba_unit", bold=True),
        ]
        for pcba_qty in qtys:
            hdr1 += pcba_hdrs(pcba, pcba_qty)
    hdr1 += [
        Cell.separator(),
        Cell("warnings", bold=True),
    ]
    table: list[list[Cell] | typing.Literal["-"]] = [hdr0, hdr1, "-"]

    # Body #####################################################################
    def sort_key(r: Row) -> tuple[int, int]:
        grp: int = {
            "J": 1,
            "SW": 1,
            "L": 2,
            "D": 2,
            "C": 3,
            "R": 4,
        }.get(re.sub(r"[0-9]+(,.*)?", "", r.user.designator), 0)
        price = r.sum_unit_price(PCBA.economical, qtys[0]).centicents
        return (grp, -price)

    seen_lcsc: set[str] = set()
    for irow in sorted(bom.rows, key=sort_key):
        orow = [
            Cell(irow.user.comment),
            Cell(irow.user.designator),
            Cell(irow.user.footprint_short),
            Cell(irow.user.lcsc),
            Cell(irow.user.qty),
        ]
        if not irow.match:
            orow += [Cell("", bg_color=Color.red)] * (len(hdr1) - len(orow))
        else:
            orow += [
                Cell.separator(),
                Cell(
                    irow.match.lcsc,
                    bg_color=(
                        Color.red
                        if irow.match.lcsc != irow.user.lcsc or irow.match.warnings
                        else Color.default
                    ),
                ),
                Cell(irow.match.name),
                Cell(irow.match.description),
                Cell(irow.match.footprint),
                Cell(irow.match.stock),
                Cell(irow.match.moq),
                Cell(irow.match.attrition),
            ]
            if irow.match.lcsc not in seen_lcsc:
                for pcba in pcbas:
                    orow += [
                        Cell.separator(),
                        Cell(irow.pcba_fixed_price(pcba)),
                        Cell(irow.pcba_unit_price(pcba)),
                    ]
                    for pcba_qty in qtys:
                        orow += pcba_body(irow, pcba, pcba_qty)
                seen_lcsc.add(irow.match.lcsc)
            else:
                for pcba in pcbas:
                    orow += [
                        Cell.separator(),
                        Cell(""),
                        Cell(""),
                    ]
                    for pcba_qty in qtys:
                        orow += [Cell.separator()] + [Cell("")] * (
                            len(pcba_body(irow, pcba, pcba_qty)) - 1
                        )
            orow += [
                Cell.separator(),
                Cell(", ".join(sorted(irow.match.warnings)), fg_color=Color.red),
            ]
        table.append(orow)

    # Footer: assembly #########################################################
    table.append("-")
    orow = [
        Cell(
            "assembly",
            colspan=sum(c.colspan for c in hdr0[:3]),
        )
    ]
    for pcba in pcbas:
        orow += [
            Cell.separator(),
            Cell(bom.pcba_startup_price(pcba)),  # pcba_fixed
            Cell(""),  # pcba_unit
        ]
        for pcba_qty in qtys:
            orow += [
                Cell.separator(),
                *[Cell("")] * len(pcba_hdrs(pcba, pcba_qty)[1:]),
            ]
    orow += [Cell.separator()]
    table.append(orow)

    # Footer: totals ###########################################################
    table.append("-")
    orow = [
        Cell(
            "total",
            colspan=sum(c.colspan for c in hdr0[:3]),
            bold=True,
        )
    ]
    for pcba in pcbas:
        orow += [
            Cell.separator(),
            Cell(bom.pcba_fixed_price(pcba)),
            Cell(""),  # pcba_unit
        ]
        for pcba_qty in qtys:
            orow += pcba_totals(pcba, pcba_qty)
    orow += [Cell.separator()]
    table.append(orow)

    return table


max_widths: dict[str, int] = {
    "designator": 15,
    "footprint": 20,
    "name": 30,
    "description": 30,
}

# Render: terminal #############################################################


def _term_cell_render(cell: Cell, width: int) -> str:
    if width == 0:
        return ""
    assert width > 2
    _width = width - 2

    ret = cell.text

    if len(ret) > _width:
        ret = ret[: _width - 1] + "…"

    if len(ret) < _width:
        match cell.align:
            case Align.left:
                ret = f"{ret:<{_width}}"
            case Align.center:
                ret = f"{ret:^{_width}}"
            case Align.right:
                ret = f"{ret:>{_width}}"
    ret = f" {ret} "

    style = ansiterm.sgr
    if cell.bold:
        style = style.bold
    match cell.fg_color:
        case Color.default | Color.separator:
            pass
        case Color.red:
            style = style.fg_red
    match cell.bg_color:
        case Color.default | Color.separator:
            pass
        case Color.red:
            style = style.bg_red
    if style != ansiterm.sgr:
        ret = f"{style}{ret}{ansiterm.sgr.reset}"

    return ret


def render_term(table: list[list[Cell] | typing.Literal["-"]]) -> bytes:
    assert isinstance(table[1], list)
    hdr1: list[Cell] = table[1]

    # Naively calculate widths.
    widths: list[int] = [0] * max(
        sum(c.colspan for c in r) for r in table if isinstance(r, list)
    )
    for row in table:
        if row == "-":
            continue
        x = 0
        for cell in row:
            if cell.colspan == 1:
                widths[x] = max(widths[x], len(cell.text))
            x += cell.colspan
    # Apply max_widths.
    for x, cell in enumerate(hdr1):
        if cell.text in max_widths:
            widths[x] = min(widths[x], max_widths[cell.text])
    # Add padding.
    for x, _ in enumerate(widths):
        if widths[x]:
            widths[x] += 2

    # Render.
    s = ""
    for row in table:
        s += "|"
        if row == "-":
            for w in widths:
                s += str(ansiterm.sgr.bold) + "-" * w + str(ansiterm.sgr.reset) + "|"
        else:
            x = 0
            for cell in row:
                w = sum(widths[x + n] for n in range(cell.colspan)) + cell.colspan - 1
                s += _term_cell_render(cell, w) + "|"
                x += cell.colspan
        s += "\n"
    return s.encode("utf-8")


# Render: XLS ##################################################################


def _xls_cell_label(cell: Cell) -> str | int | float:
    match cell.content:
        case str():
            return cell.content
        case int():
            if cell.content == 0:
                return ""
            return cell.content
        case USD():
            if cell.content.centicents == 0:
                return ""
            return float(cell.content)


def _xls_cell_style(cell: Cell) -> xlwt.XFStyle:
    style = xlwt.XFStyle()
    match cell.content:
        case int():
            style.num_format_str = "#,##0"
        case USD():
            style.num_format_str = '"$"#,##0.0000'
    style.font.bold = cell.bold
    match cell.fg_color:
        case Color.default:
            pass
        case Color.separator:
            assert False
        case Color.red:
            style.font.colour_index = 0x02  # built-in red
    match cell.bg_color:
        case Color.default:
            pass
        case Color.separator:
            style.pattern.pattern = xlwt.Pattern.SOLID_PATTERN
            style.pattern.pattern_fore_colour = 0x17  # "gray50" (by default)
        case Color.red:
            style.pattern.pattern = xlwt.Pattern.SOLID_PATTERN
            style.pattern.pattern_fore_colour = 0x02  # built-in red
    match cell.align:
        case Align.left:
            style.alignment.horz = xlwt.Alignment.HORZ_LEFT
        case Align.center:
            style.alignment.horz = xlwt.Alignment.HORZ_CENTER
        case Align.right:
            style.alignment.horz = xlwt.Alignment.HORZ_RIGHT
    return style


def render_xls(table: list[list[Cell] | typing.Literal["-"]]) -> bytes:
    assert isinstance(table[1], list)
    hdr1: list[Cell] = table[1]

    num_cols = max(sum(c.colspan for c in r) for r in table if isinstance(r, list))

    # Main #####################################################################

    wb = xlwt.Workbook(encoding="utf-8")
    ws = wb.add_sheet("BOM matches")

    for y, row in enumerate(table):
        if row == "-":
            style = _xls_cell_style(Cell.separator())
            for x in range(num_cols):
                ws.write(y, x, None, style)
            ws.row(y).height = 32  # in twips, 1/20 of a point,
        else:
            x = 0
            for cell in row:
                if cell.colspan == 1:
                    ws.write(y, x, _xls_cell_label(cell), _xls_cell_style(cell))
                    if cell.bg_color == Color.separator:
                        ws.col(x).width = (
                            96  # in 1/256 of the width of "0" in the default font
                        )
                else:
                    ws.write_merge(
                        y,
                        y,
                        x,
                        x + cell.colspan - 1,
                        _xls_cell_label(cell),
                        _xls_cell_style(cell),
                    )
                x += cell.colspan

    # Adjust column widths #####################################################
    # Widths are in 1/256 of the width of "0" in the default font.

    # Naively calculate widths (in characters).
    widths: list[int] = [0] * num_cols
    for row in table:
        if row == "-":
            continue
        x = 0
        for cell in row:
            if cell.colspan == 1:
                widths[x] = max(widths[x], len(cell.text))
            x += cell.colspan
    # Apply max_widths.
    for x, cell in enumerate(hdr1):
        if cell.text in max_widths:
            widths[x] = min(widths[x], max_widths[cell.text])
    # Convert units.
    for x, _ in enumerate(widths):
        # This is dumb and bad; it assumes that on average characters
        # are 1.25 the width of "0".  The default font is Arial (or a
        # metric-compatible font); I should add a lookup table of the
        # font metrics for Arial.
        widths[x] *= 256 + 64
    # Add padding.
    for x, _ in enumerate(widths):
        if widths[x]:
            widths[x] += 128
        else:
            widths[x] = 96
    # Apply.
    for x, w in enumerate(widths):
        ws.col(x).width = w

    # Return ###################################################################
    ret = io.BytesIO()
    wb.save(ret)
    return ret.getvalue()


################################################################################

if __name__ == "__main__":
    main()