from pathlib import Path

from openpyxl import Workbook, load_workbook
from openpyxl.comments import Comment
from openpyxl.styles import Alignment, Border, Font, PatternFill, Side
from openpyxl.worksheet.page import PageMargins


OUTPUT = Path(__file__).resolve().parents[1] / "康比特智慧食堂软件价格单_简版.xlsx"
ROWS = [
    ("管理软件", "待定", "单食堂 / 多食堂 / 集团版"),
    ("结算机适配", 5000, "结算机软件适配"),
    ("AI识别适配", 10000, "含结算机适配"),
    ("克重适配", 10000, "含结算机适配"),
    ("全适配", 15000, "含结算机、AI识别、克重适配"),
    ("其他硬件适配", 1000, "每种类型/型号加收；同型号多台只收一次"),
    ("云端部署", 0, "不收费"),
    ("本地化部署", 5000, "单独收取"),
    ("信创适配部署", 20000, "含本地化部署费"),
]


def main():
    workbook = Workbook()
    sheet = workbook.active
    sheet.title = "价格单"
    sheet.sheet_view.showGridLines = False
    sheet.merge_cells("A1:C1")
    sheet["A1"] = "康比特智慧食堂软件价格单"
    sheet["A1"].font = Font(name="Microsoft YaHei", size=18, bold=True, color="164B43")
    sheet["A1"].alignment = Alignment(horizontal="center", vertical="center")
    sheet.row_dimensions[1].height = 42
    sheet.append(["", "", ""])
    sheet.append(["项目", "价格（元）", "备注"])
    for row in ROWS:
        sheet.append(row)

    edge = Side(style="thin", color="D8DFDE")
    for row in sheet.iter_rows(min_row=3, max_row=12):
        for cell in row:
            cell.font = Font(name="Microsoft YaHei", size=11)
            cell.alignment = Alignment(vertical="center", wrap_text=True)
            cell.border = Border(left=edge, right=edge, top=edge, bottom=edge)
        row[1].alignment = Alignment(horizontal="center", vertical="center")
        if isinstance(row[1].value, int):
            row[1].number_format = '#,##0;(#,##0);"0"'
            row[1].comment = Comment("来源：用户确认，2026-09-30。", "康比特")
        sheet.row_dimensions[row[0].row].height = 38
    for cell in sheet[3]:
        cell.fill = PatternFill("solid", fgColor="164B43")
        cell.font = Font(name="Microsoft YaHei", size=11, bold=True, color="FFFFFF")
    sheet.merge_cells("A14:C14")
    sheet["A14"] = "以上为软件及部署费用，实物硬件另计。适配套餐按包含范围选择，不重复收费。"
    sheet["A14"].font = Font(name="Microsoft YaHei", size=10, color="555555")
    sheet["A14"].alignment = Alignment(wrap_text=True, vertical="center")
    sheet.row_dimensions[14].height = 36
    for column, width in {"A": 25, "B": 16, "C": 44}.items():
        sheet.column_dimensions[column].width = width
    sheet.freeze_panes = "A4"
    sheet.print_options.horizontalCentered = True
    sheet.print_area = "A1:C14"
    sheet.sheet_properties.pageSetUpPr.fitToPage = True
    sheet.page_setup.orientation = "portrait"
    sheet.page_setup.paperSize = sheet.PAPERSIZE_A4
    sheet.page_setup.fitToWidth = 1
    sheet.page_setup.fitToHeight = 1
    sheet.page_margins = PageMargins(left=0.35, right=0.35, top=0.5, bottom=0.5)
    workbook.save(OUTPUT)

    check = load_workbook(OUTPUT)
    assert check.sheetnames == ["价格单"]
    actual = list(check.active.iter_rows(min_row=4, max_row=12, values_only=True))
    assert actual == ROWS
    print(OUTPUT)
    print("Verified: one sheet, nine price rows, confirmed amounts and inclusion rules.")


if __name__ == "__main__":
    main()