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, "含纯结算版，增加AI识别结算。"),
    ("软件套餐", "减重版", 10000, "含纯结算版，增加减重结算；与AI识别版任选一种。"),
    ("软件套餐", "全场景适配版", 15000, "含纯结算、AI识别、减重三种结算场景。"),
    ("功能选配", "营养分析", None, "菜品营养、菜单分析、个人摄入记录及报告，含对应PC/手机功能。"),
    ("功能选配", "健康管理", None, "健康档案、目标、体重及健康趋势。"),
    ("功能选配", "AI营养服务", None, "营养问答、图片记餐；模型调用资源另核。"),
    ("功能选配", "电子菜牌", None, "菜单、图片、价格、营养标签发布；屏幕硬件另计。"),
    ("功能选配", "运营数据看板", None, "就餐、消费、菜品及营养数据展示；定制看板另评估。"),
    ("功能选配", "菜品余量管理", None, "余量显示、缺菜提醒；需要对应设备数据。"),
    ("功能选配", "取餐叫号", None, "备餐、出餐、待取餐及叫号展示。"),
    ("功能选配", "终端远程排菜", None, "向设备下发及切换菜品，与基础后台菜谱管理区分。"),
    ("组织授权", "多食堂管理", None, "同一单位内分食堂权限、运营、对账；包含数量及增购价格待定。"),
    ("组织授权", "多单位管理", None, "各单位独立人员、补贴、账务及数据权限；园区按实际管理单位核定。"),
    ("组织授权", "总部监管", None, "跨单位汇总、分级授权及跨单位对账；集团按实际接入范围核定。"),
    ("云端服务", "人数容量/资源服务", None, "人数计量、容量档位、云资源、服务费用及周期待确认。"),
    ("软件适配", "其他硬件适配", 1000, "每种类型/型号1,000元，同型号多台只收一次；已含标准接入时去重。"),
    ("部署方式", "云端部署", 0, "部署服务免费；云资源及云端服务费用另核。"),
    ("部署方式", "本地化部署", 5000, "客户本地环境部署服务；服务器等实物硬件另计。"),
    ("部署方式", "信创适配部署", 20000, "含信创适配及本地化部署，不再叠加本地化5,000元。"),
    ("定制开发", "轻量调整档", 20000, "现有页面、字段、报表或打印模板调整；沿用既有业务流程及权限。"),
    ("定制开发", "流程扩展档", 50000, "现有平台内新增/调整约定业务流程、配套页面报表，或约定范围的接口联调。"),
    ("定制开发", "专项开发档", 100000, "跨模块业务闭环、多系统联动或复杂权限；按专项方案及验收清单交付。"),
]
NOTES = [
    "选择：软件套餐择一，部署方式择一；定制按需求复杂度择档，三档不叠加，另加软件及部署费用。",
    "套餐拆分：基础结算5,000元，AI/减重扩展各加5,000元；软件收费周期与授权规模尚待确认。",
    "定制选档：只改现有呈现选2万；增加约定流程/接口选5万；跨模块、多系统或复杂权限选10万。",
    "定制范围为建议，须确认具体需求、接口、工作边界、验收及周期；套餐价格不包含所有功能选配。",
    "待定不代表免费；其他硬件适配不含设备采购；接口、短信/AI调用、现场实施、培训及维保另核。",
]


def main():
    workbook = Workbook()
    sheet = workbook.active
    sheet.title = "前厅软件报价"
    sheet.sheet_view.showGridLines = False
    sheet.merge_cells("A1:D1")
    sheet["A1"] = "康比特智慧食堂前厅软件报价单"
    sheet["A1"].font = Font(name="Microsoft YaHei", size=16, bold=True, color="164B43")
    sheet["A1"].alignment = Alignment(horizontal="center", vertical="center")
    sheet.row_dimensions[1].height = 32
    sheet.merge_cells("A2:D2")
    sheet["A2"] = "人民币元 | 软件、部署、信创及定制一页汇总 | 2026-09-30"
    sheet["A2"].font = Font(name="Microsoft YaHei", size=9, color="555555")
    sheet["A2"].alignment = Alignment(horizontal="center", vertical="center")
    sheet.row_dimensions[2].height = 19
    sheet.append(["分类", "项目", "价格（元）", "包含范围 / 选择规则"])
    border = Side(style="thin", color="D7DEDD")
    for row_index, item in enumerate(ROWS, 4):
        sheet.append(item)
        for cell in sheet[row_index]:
            cell.font = Font(name="Microsoft YaHei", size=10)
            cell.alignment = Alignment(vertical="center", wrap_text=True)
            cell.border = Border(left=border, right=border, top=border, bottom=border)
        price = sheet.cell(row_index, 3)
        price.alignment = Alignment(horizontal="center", vertical="center")
        if price.value is None:
            price.value = "待定"
            price.fill = PatternFill("solid", fgColor="FFF2CC")
        else:
            price.number_format = '#,##0;(#,##0);"0"'
            price.comment = Comment("来源：用户确认，2026-09-30；定制包含范围为建议，按需求清单确认。", "康比特")
        if item[0] in ("部署方式", "定制开发"):
            for cell in sheet[row_index]:
                cell.fill = PatternFill("solid", fgColor="EAF3F1" if item[0] == "部署方式" else "F1F2F6")
        sheet.row_dimensions[row_index].height = 28
    for cell in sheet[3]:
        cell.fill = PatternFill("solid", fgColor="164B43")
        cell.font = Font(name="Microsoft YaHei", size=10, bold=True, color="FFFFFF")
        cell.alignment = Alignment(vertical="center", horizontal="center")
    sheet.row_dimensions[3].height = 23
    for row_index, note in enumerate(NOTES, len(ROWS) + 5):
        sheet.merge_cells(start_row=row_index, start_column=1, end_row=row_index, end_column=4)
        cell = sheet.cell(row_index, 1, note)
        cell.font = Font(name="Microsoft YaHei", size=9, color="555555")
        cell.alignment = Alignment(vertical="center", wrap_text=True)
        sheet.row_dimensions[row_index].height = 24
    for column, width in {"A": 12, "B": 22, "C": 14, "D": 58}.items():
        sheet.column_dimensions[column].width = width
    sheet.freeze_panes = "A4"
    sheet.print_area = f"A1:D{sheet.max_row}"
    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.25, right=0.25, top=0.25, bottom=0.25)
    sheet.print_options.horizontalCentered = True
    workbook.save(OUTPUT)
    check = load_workbook(OUTPUT)
    assert check.sheetnames == ["前厅软件报价"]
    assert [check.active.cell(r, 3).value for r in (4, 5, 6, 7)] == [5000, 10000, 10000, 15000]
    assert [check.active.cell(r, 3).value for r in range(21, 27)] == [0, 5000, 20000, 20000, 50000, 100000]
    assert not any(c.data_type == "e" for row in check.active for c in row)
    print(OUTPUT)
    print("Verified: one worksheet; scenario, deployment and custom prices correct.")


if __name__ == "__main__":
    main()