#!/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 itertools
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 #############################################################

_T = typing.TypeVar("_T")


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


def _when(cond: bool, data: _T) -> list[_T]:
    return [data] if cond else []


# 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.StrEnum):
    economical = "Economical PCBA"
    standard_singlesided = "Standard single-sided PCBA"
    standard_doublesided = "Standard double-sided PCBA"


@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

    @property
    def joints(self) -> int:
        # TODO: get this from the KiCad footprint
        return 2


class LibraryType(enum.StrEnum):
    # ordered cheapest to most expensive
    basic = "Basic"
    extended_promotional = "Extended (Promotional)"
    extended = "Extended"
    extended_mechanical = "Extended (Mechanical)"


@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: LibraryType
    stock: int
    moq: int
    attrition: int
    unit_assembly_fee: USD
    unit_prices: list[Price]

    assembly_mode: jlcpcb_api.AssemblyMode


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

    def per_board_qty(self) -> int:
        if self.match is None:
            return 0
        return self.match.sum_user_qty

    def order_qty_no_moq(self, board_qty: int) -> int:
        if self.match is None:
            return 0
        # 2025-02-14: PCBA checkout isn't charging me attrition.
        return board_qty * self.per_board_qty()  # + self.match.attrition

    def order_qty(self, board_qty: int) -> int:
        if self.match is None:
            return 0
        return max(self.order_qty_no_moq(board_qty), self.match.moq)

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

    def pcbacost_fixed(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 LibraryType.basic | LibraryType.extended_promotional:
                        ret = USD(0.00)
                    case LibraryType.extended:
                        match self.match.assembly_mode:
                            case jlcpcb_api.AssemblyMode.smt_weld:
                                ret = USD(3.00)
                            case jlcpcb_api.AssemblyMode.manual_weld:
                                ret = USD(0.00)
                            case _:
                                assert False
                    case LibraryType.extended_mechanical:
                        # TODO: idk how mechanical assembly is charged
                        ret = USD(0.00)
                    case _:
                        assert False
            case PCBA.standard_singlesided | PCBA.standard_doublesided:
                ret = USD(1.50)
            case _:
                assert False
        return ret

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

        ret = self.match.unit_assembly_fee

        smt_joints = 0
        man_joints = 0
        match self.match.assembly_mode:
            case jlcpcb_api.AssemblyMode.smt_weld:
                smt_joints += self.user.joints
            case jlcpcb_api.AssemblyMode.manual_weld:
                man_joints += self.user.joints
            case _:
                assert False

        match pcba:
            case PCBA.economical:
                ret += USD(smt_joints * 0.0017)  # NB: web page says $0.0016

                if man_joints < 50000:
                    ret += USD(man_joints * 0.0157)
                elif man_joints < 100000:
                    ret += USD(man_joints * 0.0141)
                else:
                    ret += USD(man_joints * 0.011)
            case PCBA.standard_singlesided | PCBA.standard_doublesided:
                if smt_joints < 50000:
                    ret += USD(smt_joints * 0.0016)
                elif smt_joints < 100000:
                    ret += USD(smt_joints * 0.0013)
                else:
                    ret += USD(smt_joints * 0.0011)

                if man_joints < 50000:
                    ret += USD(man_joints * 0.0157)
                elif man_joints < 100000:
                    ret += USD(man_joints * 0.0141)
                else:
                    ret += USD(man_joints * 0.011)
            case _:
                assert False
        return ret

    def pcbacost_per_board(self, pcba: PCBA) -> USD:
        return self.per_board_qty() * self.pcbacost_per_part(pcba)

    def pcbacost_ext(self, pcba: PCBA, board_qty: int) -> USD:
        return self.pcbacost_fixed(pcba) + (board_qty * self.pcbacost_per_board(pcba))

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

    def bomcost_per_part(self, board_qty: int) -> USD:
        if self.match is None:
            return USD(0.0)
        order_qty = self.order_qty(board_qty)

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

    def bomcost_per_board(self, board_qty: int) -> USD:
        return self.per_board_qty() * self.bomcost_per_part(board_qty)

    def bomcost_attrition(self, board_qty: int) -> USD:
        if self.match is None:
            return USD(0.0)
        return self.match.attrition * self.bomcost_per_part(board_qty)

    def bomcost_moq_overhead(self, board_qty: int) -> USD:
        if self.match is None:
            return USD(0.0)

        overhead_qty = self.order_qty(board_qty) - self.order_qty_no_moq(board_qty)
        return overhead_qty * self.bomcost_per_part(board_qty)

    def bomcost_ext(self, board_qty: int) -> USD:
        return self.order_qty(board_qty) * self.bomcost_per_part(board_qty)

    # sum costs ################################################################

    def sumcost_per_board(self, pcba: PCBA, board_qty: int) -> USD:
        return self.pcbacost_per_board(pcba) + self.bomcost_per_board(board_qty)

    def sumcost_ext(self, pcba: PCBA, board_qty: int) -> USD:
        return self.pcbacost_ext(pcba, board_qty) + self.bomcost_ext(board_qty)


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

    @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())

    # PCBA costs ###############################################################

    def pcbacost_startup(self, pcba: PCBA) -> USD:
        # TODO: Consider adding "panel", "large size", "nitrogen
        # reflow soldering" fees.  Nitrogen is required for economic,
        # and optional for standard.
        match pcba:
            case PCBA.economical:
                return USD(
                    8.00  # setup fee
                    + 1.50  # stencil
                    + 3.50  # hand-soldering labor fee
                    + 0.85  # nitrogen reflow soldering
                )
            case PCBA.standard_singlesided:
                return USD(
                    25.00  # setup fee
                    + 7.86  # stencil
                    + 3.50  # hand-soldering labor fee
                )
            case PCBA.standard_doublesided:
                return USD(
                    50.00  # setup fee
                    + 15.72  # stencil
                    + 3.50  # hand-soldering labor fee
                )

    def total_pcbacost_fixed(self, pcba: PCBA) -> USD:
        return self.pcbacost_startup(pcba) + usd_sum(
            row.pcbacost_fixed(pcba) for row in self.deduped_rows
        )

    def total_pcbacost_per_board(self, pcba: PCBA) -> USD:
        return usd_sum(row.pcbacost_per_board(pcba) for row in self.deduped_rows)

    def total_pcbacost_ext(self, pcba: PCBA, board_qty: int) -> USD:
        a = self.total_pcbacost_fixed(pcba) + (
            board_qty * self.total_pcbacost_per_board(pcba)
        )
        b = self.pcbacost_startup(pcba) + usd_sum(
            row.pcbacost_ext(pcba, board_qty) for row in self.deduped_rows
        )
        assert a == b
        return a

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

    def total_bomcost_per_board(self, board_qty: int) -> USD:
        return usd_sum(row.bomcost_per_board(board_qty) for row in self.deduped_rows)

    def total_bomcost_attrition(self, board_qty: int) -> USD:
        return usd_sum(row.bomcost_attrition(board_qty) for row in self.deduped_rows)

    def total_bomcost_moq_overhead(self, board_qty: int) -> USD:
        return usd_sum(row.bomcost_moq_overhead(board_qty) for row in self.deduped_rows)

    def total_bomcost_ext(self, board_qty: int) -> USD:
        return usd_sum(row.bomcost_ext(board_qty) for row in self.deduped_rows)

    # sum costs ################################################################

    def total_sumcost_per_board(self, pcba: PCBA, board_qty: int) -> USD:
        return self.total_pcbacost_per_board(pcba) + self.total_bomcost_per_board(
            board_qty
        )

    def total_sumcost_ext(self, pcba: PCBA, board_qty: int) -> USD:
        return self.total_pcbacost_ext(pcba, board_qty) + self.total_bomcost_ext(
            board_qty
        )


@dataclasses.dataclass
class APIMatch:
    bom: jlcpcb_api.BOMFileMatchComponent
    pageinfo: jlcpcb_api.SMTComponentListPageInfoItem | None = None
    details: jlcpcb_api.ComponentDetails | None = None


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


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)
        case _:
            assert False
    sys.stdout.buffer.write(out_bytes)


def fetch_smt_component_pageinfo(
    client: jlcpcb_api.Client, lcsc: str
) -> jlcpcb_api.SMTComponentListPageInfoItem:
    presale_types: list[typing.Literal["", "stock", "buy", "post"]] = [
        "stock",
        "buy",
        "post",
    ]
    for presale_type in presale_types:
        res = client.pcb_order.shopping_cart.smt_good.select_smt_component_list(
            keyword=lcsc,
            search_type=2,
            presale_type=presale_type,
            page_size=30,
        )
        for item in res.componentPageInfo.list or []:
            if item.componentCode == lcsc:
                return item
    assert False, f"could not find pageinfo for {lcsc!r}"


def get_match(
    session_id: str, bom_filename: str, bom_content: bytes, count: int
) -> list[APIMatch]:
    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: Fetching auxiliary part info...", file=sys.stderr)
    matches = [
        APIMatch(bom=m)
        for m in [
            *_matches.jlcStock,
            *_matches.jlcBuy,
            *_matches.overseasShop,
            *_matches.notSelect,
            *_matches.notMatch,
        ]
    ]
    for match in matches:
        if not match.bom.componentCode:
            continue
        print(f" - {match.bom.componentCode}", file=sys.stderr)
        match match.bom.componentLibraryType:
            case jlcpcb_api.LibraryType.basic:
                pass
            case jlcpcb_api.LibraryType.extended:
                match.pageinfo = fetch_smt_component_pageinfo(
                    client, match.bom.componentCode
                )
            case _:
                assert False
        match.details = (
            client.core_platform.shopping_cart.smt_good.get_component_detail(
                match.bom.componentCode
            )
        )

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

    return matches


def join_boms(
    orig_ws: xlrd.sheet.Sheet,
    matches: list[APIMatch],
    count: int,
) -> BOM:
    lcsc2matches: dict[str, list[APIMatch]] = {}
    designator2match: dict[str, APIMatch] = {}
    for match in matches:
        if match.bom.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.bom.designator:
            continue
        if match.bom.componentCode:
            if match.bom.componentCode not in lcsc2matches:
                lcsc2matches[match.bom.componentCode] = []
            lcsc2matches[match.bom.componentCode].append(match)
        designator2match[normalize_designator(match.bom.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.bom.componentCode:
            assert match.bom.componentModel is not None
            assert match.bom.componentSpecification is not None
            assert match.bom.componentLibraryType is not None
            assert match.bom.componentDescription is not None
            assert match.bom.componentBrand is not None
            assert match.bom.componentName is not None
            assert match.bom.lcscComponentId is not None
            assert match.bom.recommendNumber is not None
            assert match.bom.buyNumber is not None
            assert match.bom.minPurchaseNum is not None
            assert match.bom.overseasStockCount is not None
            assert match.bom.prices is not None
            assert match.bom.unitPrice is not None
            assert match.bom.totalMoney is not None
            assert match.bom.lossNumber is not None
            assert match.bom.leastNumber is not None
            assert match.bom.leastPatchNumber is not None
            assert match.bom.encapsulationNumber is not None
            assert match.bom.checked is not None
            assert match.bom.secondTypeNameEn is not None
            assert match.bom.matchWay is not None
            assert match.bom.isBuyComponent is not None
            assert match.bom.matchSource is not None
            assert match.bom.needAuditFlag is not None
            # If any of these asserts breaks, then I should re-think some code.
            assert match.bom.leastNumber == 0
            assert match.bom.designatorNum == row.user.qty
            assert match.bom.recommendNumber == max(
                sum(m.bom.designatorNum for m in lcsc2matches[match.bom.componentCode])
                * count
                + match.bom.lossNumber,
                match.bom.minPurchaseNum,
                match.bom.leastPatchNumber,
            )
            assert match.bom.buyNumber == match.bom.recommendNumber
            for price in match.bom.prices:
                assert price.lcscPriceRangeCoefficient == 1
            assert match.details

            library_type: LibraryType
            match match.bom.componentLibraryType:
                case jlcpcb_api.LibraryType.basic:
                    library_type = LibraryType.basic
                case jlcpcb_api.LibraryType.extended:
                    assert match.pageinfo
                    if match.pageinfo.preferredComponentFlag:
                        library_type = LibraryType.extended_promotional
                    elif match.pageinfo.assemblyComponentFlag:
                        library_type = LibraryType.extended_mechanical
                    else:
                        library_type = LibraryType.extended
                case _:
                    assert False

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

            row.match = MatchRow(
                sum_user_qty=sum(
                    m.bom.designatorNum for m in lcsc2matches[match.bom.componentCode]
                ),
                # Match ID
                lcsc=match.bom.componentCode,
                id=match.bom.lcscComponentId,
                name=match.bom.componentName,
                description=match.bom.componentDescription,
                footprint=match.bom.componentSpecification,
                warnings=warnings,
                # Quantities/Pricing
                library_type=library_type,
                stock=match.bom.overseasStockCount,
                moq=max(match.bom.minPurchaseNum, match.bom.leastPatchNumber),
                attrition=match.bom.lossNumber,
                unit_assembly_fee=USD(match.bom.specialComponentFee or 0.0),
                unit_prices=[
                    Price(
                        beg_qty=p.startNumber,
                        end_qty=p.endNumber,
                        unit_price=USD(p.productPrice),
                    )
                    for p in match.bom.prices
                ],
                assembly_mode=match.details.assemblyMode,
            )
        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
                case _:
                    assert False
        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)
            case _:
                assert False

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


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

    seen_lcsc: set[tuple[str, PCBA | int]] = set()

    # pcbacost cells ###########################################################

    def pcbacost_hdr0(pcba: PCBA) -> list[Cell]:
        return [
            Cell(
                pcba.value,
                colspan=len(pcbacost_hdr1(pcba)),
                align=Align.center,
                bold=True,
            ),
        ]

    def pcbacost_hdr1(pcba: PCBA) -> list[Cell]:
        return [
            Cell("fixed", bold=True),
            Cell("per_part", bold=True),
            Cell("per_board", bold=True),
        ]

    def pcbacost_body(pcba: PCBA, irow: Row) -> list[Cell]:
        nonlocal seen_lcsc
        if not irow.match:
            return [Cell("", bg_color=Color.red)] * 3
        if (irow.match.lcsc, pcba) in seen_lcsc:
            return [Cell("")] * 3
        seen_lcsc.add((irow.match.lcsc, pcba))
        return [
            Cell(irow.pcbacost_fixed(pcba)),
            Cell(irow.pcbacost_per_part(pcba)),
            Cell(irow.pcbacost_per_board(pcba)),
        ]

    def pcbacost_other(pcba: PCBA) -> list[Cell]:
        return [
            Cell(bom.pcbacost_startup(pcba)),
            Cell(""),
            Cell(""),
        ]

    def pcbacost_total(pcba: PCBA) -> list[Cell]:
        return [
            Cell(bom.total_pcbacost_fixed(pcba)),
            Cell(""),
            Cell(bom.total_pcbacost_per_board(pcba)),
        ]

    # bomcost cells ############################################################

    def bomcost_hdr0() -> list[Cell]:
        colspan = -1
        for board_qty in board_qtys:
            colspan += 1 + len(bomcost_hdr1(board_qty))
        return [
            Cell("BOM cost", colspan=colspan, align=Align.center, bold=True),
        ]

    def bomcost_hdr1(board_qty: int) -> list[Cell]:
        return [
            Cell(f"per_part@{board_qty}", bold=True),
            Cell(f"per_board@{board_qty}", bold=True),
            Cell(f"loss@{board_qty}", bold=True),
            *_when(
                bool(bom.total_bomcost_moq_overhead(board_qty)),
                Cell(f"moq_overhead@{board_qty}", bold=True),
            ),
            Cell(f"ext@{board_qty}", bold=True),
        ]

    def bomcost_body(board_qty: int, irow: Row) -> list[Cell]:
        nonlocal seen_lcsc
        if not irow.match:
            return [Cell("", bg_color=Color.red)] * len(bomcost_hdr1(board_qty))
        if (irow.match.lcsc, board_qty) in seen_lcsc:
            return [Cell("")] * len(bomcost_hdr1(board_qty))
        seen_lcsc.add((irow.match.lcsc, board_qty))
        fg_color = Color.default
        if irow.order_qty(board_qty) > irow.match.stock:
            fg_color = Color.red
        return [
            Cell(irow.bomcost_per_part(board_qty), fg_color=fg_color),
            Cell(irow.bomcost_per_board(board_qty), fg_color=fg_color, bold=True),
            Cell(irow.bomcost_attrition(board_qty), fg_color=fg_color),
            *_when(
                bool(bom.total_bomcost_moq_overhead(board_qty)),
                Cell(irow.bomcost_moq_overhead(board_qty), fg_color=fg_color),
            ),
            Cell(irow.bomcost_ext(board_qty), fg_color=fg_color, bold=True),
        ]

    def bomcost_other(board_qty: int) -> list[Cell]:
        return [Cell("")] * len(bomcost_hdr1(board_qty))

    def bomcost_total(board_qty: int) -> list[Cell]:
        fg_color = Color.default
        if any(
            row.order_qty(board_qty) > row.match.stock for row in bom.rows if row.match
        ):
            fg_color = Color.red
        return [
            Cell(""),
            Cell(bom.total_bomcost_per_board(board_qty), fg_color=fg_color, bold=True),
            Cell(bom.total_bomcost_attrition(board_qty), fg_color=fg_color),
            *_when(
                bool(bom.total_bomcost_moq_overhead(board_qty)),
                Cell(bom.total_bomcost_moq_overhead(board_qty), fg_color=fg_color),
            ),
            Cell(bom.total_bomcost_ext(board_qty), fg_color=fg_color, bold=True),
        ]

    # Header ###################################################################
    hdr0: list[Cell] = [
        Cell("uploaded BOM", colspan=6, align=Align.center, bold=True),
        Cell.separator(),
        Cell("matched component", colspan=9, align=Align.center, bold=True),
        *itertools.chain(*([Cell.separator(), *pcbacost_hdr0(pcba)] for pcba in pcbas)),
        Cell.separator(),
        *bomcost_hdr0(),
        Cell.separator(),
    ]
    hdr1: list[Cell] = [
        # orig
        Cell("comment", bold=True),  # 1
        Cell("designator", bold=True),  # 2
        Cell("footprint", bold=True),  # 3
        Cell("lcsc", bold=True),  # 4
        Cell("qty", bold=True),  # 5
        Cell("joints", bold=True),  # 6
        # match
        Cell.separator(),
        Cell("lcsc", bold=True),  # 1
        Cell("name", bold=True),  # 2
        Cell("description", bold=True),  # 3
        Cell("footprint", bold=True),  # 4
        Cell("stock", bold=True),  # 5
        Cell("moq", bold=True),  # 6
        Cell("loss", bold=True),  # 7
        Cell("lib", bold=True),  # 8
        Cell("asy_mode", bold=True),  # 9
        # pcbacost
        *itertools.chain(*([Cell.separator(), *pcbacost_hdr1(pcba)] for pcba in pcbas)),
        # bomcost
        *itertools.chain(
            *([Cell.separator(), *bomcost_hdr1(board_qty)] for board_qty in board_qtys)
        ),
        # warnings
        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.sumcost_per_board(PCBA.economical, board_qtys[0]).centicents
        return (grp, -price)

    for irow in sorted(bom.rows, key=sort_key):
        table.append(
            [
                Cell(irow.user.comment),
                Cell(irow.user.designator),
                Cell(irow.user.footprint_short),
                Cell(irow.user.lcsc),
                Cell(irow.user.qty),
                Cell(irow.user.joints),
                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),
                        Cell(irow.match.library_type),
                        Cell(irow.match.assembly_mode),
                    ]
                    if irow.match
                    else ([Cell("", bg_color=Color.red)] * hdr1[2].colspan)
                ),
                *itertools.chain(
                    *([Cell.separator(), *pcbacost_body(pcba, irow)] for pcba in pcbas)
                ),
                *itertools.chain(
                    *(
                        [Cell.separator(), *bomcost_body(board_qty, irow)]
                        for board_qty in board_qtys
                    )
                ),
                Cell.separator(),
                Cell(
                    ", ".join(sorted(irow.match.warnings)) if irow.match else "",
                    fg_color=Color.red,
                ),
            ]
        )

    # Footer: other ############################################################
    table.append("-")
    table.append(
        [
            Cell("other", colspan=sum(c.colspan for c in hdr0[:3])),
            *itertools.chain(
                *([Cell.separator(), *pcbacost_other(pcba)] for pcba in pcbas)
            ),
            *itertools.chain(
                *(
                    [Cell.separator(), *bomcost_other(board_qty)]
                    for board_qty in board_qtys
                )
            ),
            Cell.separator(),
        ]
    )

    # Footer: total ############################################################
    table.append("-")
    table.append(
        [
            Cell("total", colspan=sum(c.colspan for c in hdr0[:3]), bold=True),
            *itertools.chain(
                *([Cell.separator(), *pcbacost_total(pcba)] for pcba in pcbas)
            ),
            *itertools.chain(
                *(
                    [Cell.separator(), *bomcost_total(board_qty)]
                    for board_qty in board_qtys
                )
            ),
            Cell.separator(),
        ]
    )

    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}}"
            case _:
                assert False
    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
        case _:
            assert False
    match cell.bg_color:
        case Color.default | Color.separator:
            pass
        case Color.red:
            style = style.bg_red
        case _:
            assert False
    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)
        case _:
            assert False


def _xls_cell_style(cell: Cell) -> xlwt.XFStyle:
    style = xlwt.XFStyle()
    match cell.content:
        case str():
            pass
        case int():
            style.num_format_str = "#,##0"
        case USD():
            style.num_format_str = '"$"#,##0.0000'
        case _:
            assert False
    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
        case _:
            assert False
    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
        case _:
            assert False
    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
        case _:
            assert False
    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()