#!/usr/bin/env python3
import json
import re
import subprocess
import zipfile
from datetime import datetime
from pathlib import Path
from xml.sax.saxutils import escape


ROOT = Path("/Users/jack/code/010-cpt/008-zhct/zhctprompt")
OUT_DIR = ROOT / "work/2026-09-30-software-price-sheet"
PRIVATE_DIR = Path("/Users/jack/.codex/local-runtimes/cpt-software-pricing-20260930")
OUT_XLSX = OUT_DIR / "康比特智慧食堂软件端价格单_20260930.xlsx"
SNAPSHOT_JSON = PRIVATE_DIR / "odoo-software-pricing-source-20260930.json"


COMPONENT_LABEL = {
    "management_single": "管理软件｜单食堂版",
    "management_multi": "管理软件｜多食堂版",
    "management_group": "管理软件｜集团版",
    "function_pos": "功能软件｜消费机结算",
    "function_ai": "功能软件｜AI视觉识别结算",
    "function_weight_counter": "功能软件｜称重计量结算",
    "function_weight_bind": "功能软件｜绑盘称重结算",
    "function_weight_free": "功能软件｜免绑盘称重结算",
    "nutrition": "扩展｜营养健康",
    "k12": "扩展｜K12家校",
    "signage": "扩展｜电子菜牌",
    "deployment_saas": "部署服务｜云端部署",
    "deployment_local": "部署服务｜客户本地化部署",
    "deployment_private_cloud": "部署服务｜客户专属云部署",
    "xinchuang": "部署服务｜信创适配部署",
}


def psql(query):
    cmd = [
        "docker",
        "exec",
        "cpt-odoo-db",
        "psql",
        "-U",
        "odoo",
        "-d",
        "cpt_odoo_demo",
        "-F",
        "\t",
        "-A",
        "-c",
        query,
    ]
    result = subprocess.run(cmd, check=True, text=True, capture_output=True)
    lines = [line for line in result.stdout.splitlines() if line and not re.match(r"^\(\d+ rows?\)$", line)]
    if not lines:
        return []
    header = lines[0].split("\t")
    rows = []
    for line in lines[1:]:
        vals = line.split("\t")
        rows.append(dict(zip(header, vals)))
    return rows


def clean_name(raw):
    raw = raw or ""
    try:
        data = json.loads(raw)
        return data.get("zh_CN") or data.get("en_US") or next(iter(data.values()))
    except Exception:
        return raw


def money(value):
    if value in (None, ""):
        return ""
    try:
        num = float(value)
    except Exception:
        return value
    if abs(num - round(num)) < 0.00001:
        return int(round(num))
    return round(num, 2)


def collect_sources():
    matrix = psql(
        """
select component,audience,state,amount,basis,note
from cpt_matrix_pricing
order by audience,sequence,component;
""".strip()
    )
    products = psql(
        """
select pt.default_code, pt.name::text, pt.cpt_product_line, pt.cpt_product_family,
       pt.cpt_commercial_state, pt.list_price, pt.cpt_standard_price,
       pt.cpt_channel_price, pt.cpt_source_sheet
from product_template pt
where (
  pt.cpt_product_family='软件产品'
  or pt.cpt_product_line in ('营养结算','智慧食安','智慧采购','软件开发与接口')
  or pt.name::text ~ '系统|平台|软件|对接|开发|接口|菜品发布'
)
and (coalesce(pt.cpt_standard_price,0)>0 or coalesce(pt.list_price,0)>0)
order by pt.cpt_product_line, pt.cpt_product_family, pt.name::text;
""".strip()
    )
    for row in products:
        row["name"] = clean_name(row.get("name"))
        row["list_price"] = money(row.get("list_price"))
        row["cpt_standard_price"] = money(row.get("cpt_standard_price"))
        row["cpt_channel_price"] = money(row.get("cpt_channel_price"))
    PRIVATE_DIR.mkdir(parents=True, exist_ok=True)
    SNAPSHOT_JSON.write_text(
        json.dumps({"matrix": matrix, "software_products": products}, ensure_ascii=False, indent=2),
        encoding="utf-8",
    )
    return matrix, products


def build_rows(matrix, products):
    external = [
        ["康比特智慧食堂软件端价格单", "", "", "", "", "", ""],
        ["生成日期", "2026-09-30", "口径", "客户指导报价草案", "币种", "人民币元", ""],
        ["重要说明", "本表仅列软件端与部署服务；硬件设备、服务器资源、短信/AI模型调用、第三方接口、驻场运维和客户专项开发未包含，需另行核价。", "", "", "", "", ""],
        ["不重复收费规则", "人员账户、公共PC后台、订单、手机订餐等公共能力只计一次；商户数不等于就餐单位数；同一项目不同餐线可组合功能模块。", "", "", "", "", ""],
        [],
        ["分类", "定价项", "计价单位", "客户指导价", "状态", "包含范围/增购规则", "备注"],
        ["管理软件", "单食堂版", "套/项目", "待确认", "待拍板", "一个就餐单位一个食堂；可含多档口/多独立商户权限与对账。", "不能沿用旧基础/标准/专业价冒充现价。"],
        ["管理软件", "多食堂版", "套/项目", "待确认", "待拍板", "一个就餐单位多个食堂；人员、补贴、账务统一或分级管理。", "商户数不是食堂数。"],
        ["管理软件", "集团版", "套/项目", "待确认", "待拍板", "多个就餐单位分别管理人员、补贴、账务，上层汇总。", "多企业共用一个食堂仍按就餐单位边界核定。"],
        ["功能模块", "消费机结算功能", "按项目/餐线待定", "待确认", "待拍板", "普通消费机刷卡/扫码/刷脸直付等基础结算能力。", "硬件另计。"],
        ["功能模块", "AI视觉识别结算功能", "按项目/餐线待定", "待确认", "待拍板", "AI菜品识别、模型服务、识别结算流程。", "设备、训练范围和菜品条件另核。"],
        ["功能模块", "称重计量结算功能", "按项目/餐线待定", "待确认", "待拍板", "称重台、绑盘/免绑盘称重结算相关软件能力。", "不同档口餐线可组合。"],
        ["扩展模块", "营养健康扩展", "套/项目", "待确认", "待拍板", "营养菜单、营养分析、健康相关展示或后台能力。", "专业服务和AI调用另核。"],
        ["扩展模块", "K12家校阳光消费扩展", "套/项目", "待确认", "待拍板", "学校/K12家校侧查看、阳光消费、监管展示等扩展。", "具体端和接口范围另核。"],
        ["扩展模块", "电子菜牌扩展", "套/项目", "待确认", "待拍板", "菜品发布、价签/菜牌展示相关软件能力。", "显示屏、价签等硬件另计。"],
        ["部署服务", "云端部署", "套/项目", 0, "已确认", "仅指部署服务费不另收；不代表软件授权、云资源、短信、AI调用或运维免费。", "用户2026-09-30确认。"],
        ["部署服务", "客户专属云部署", "套/项目", 0, "已确认", "按云端部署服务费不另收；客户专属资源、域名、等保、网络等另核。", "按用户确认的云端部署口径处理。"],
        ["部署服务", "客户本地化部署", "套/项目", 5000, "已确认", "普通本地环境部署适配服务。", "用户2026-09-30确认。"],
        ["部署服务", "信创适配部署", "套/项目", 20000, "已确认", "信创环境适配部署；已包含普通本地化部署费，不再叠加5000元。", "用户2026-09-30确认。"],
    ]

    status_rows = [["价格口径", "定价项", "Odoo状态", "金额", "计价单位", "说明"]]
    for row in matrix:
        if row["audience"] != "customer":
            continue
        state = {"pending": "待核价", "included": "已包含/不另收费", "priced": "价格已确认"}.get(row["state"], row["state"])
        basis = {"project": "套/项目", "device": "设备点位", "canteen": "食堂", "unit": "就餐单位"}.get(row["basis"], row["basis"])
        status_rows.append(["客户指导价", COMPONENT_LABEL.get(row["component"], row["component"]), state, money(row["amount"]), basis, row.get("note", "")])

    ref_rows = [["来源类型", "产品/历史项", "产品线", "家族/类型", "客户/目录价", "Odoo标准价", "Odoo渠道价", "状态", "来源"]]
    keep_keywords = ("系统", "平台", "软件", "对接", "开发", "接口", "菜品发布", "管理")
    for row in products:
        name = row["name"]
        if not any(k in name for k in keep_keywords):
            continue
        source_type = "Odoo现有目录价" if row.get("cpt_commercial_state") == "legacy" else "历史项目/报价参考"
        ref_rows.append(
            [
                source_type,
                name,
                row.get("cpt_product_line", ""),
                row.get("cpt_product_family", ""),
                row.get("list_price", ""),
                row.get("cpt_standard_price", ""),
                row.get("cpt_channel_price", ""),
                row.get("cpt_commercial_state", ""),
                row.get("cpt_source_sheet", ""),
            ]
        )

    pending = [
        ["待确认项", "当前处理", "为什么不能直接填0或沿用旧价"],
        ["单食堂/多食堂/集团管理软件价", "待商务拍板", "这是新管理边界，不等同旧基础/标准/专业价格。"],
        ["消费机、AI、称重功能软件模块费", "待商务拍板", "不同餐线可组合；硬件价格与软件模块费需分开。"],
        ["营养健康、K12、电子菜牌扩展费", "待商务拍板", "历史项目有参考价，但现价和包含范围未确认。"],
        ["渠道指导价", "待商务拍板", "不能用客户指导价自动折扣生成。"],
        ["软件授权范围/年费/续费/运维", "待项目核定", "本次部署费确认不等于授权、云资源、模型调用、短信或年服务免费。"],
        ["信创部署是否叠加普通本地化部署", "已确认不叠加", "信创适配部署20000元已包含普通本地化部署5000元。"],
    ]

    rule_rows = [
        ["规则", "说明"],
        ["管理版本单选", "单食堂/多食堂/集团三选一，按就餐单位管理边界判断，不按商户数判断。"],
        ["功能模块可多选", "消费机、AI、称重可按不同档口/餐线组合；对应软件模块费待确认。"],
        ["部署位置单选", "云端/客户本地/客户专属云按部署位置选择；信创是额外适配要求但费用已单列。"],
        ["公共能力不重复收费", "公共PC、人员账户、订单、手机订餐等只计一次，不因多餐线重复收。"],
        ["硬件单列", "消费机、AI结算台、称重台、电子菜牌、服务器等硬件不在本软件端价格单内。"],
        ["待确认不是0元", "待确认、待核价、价格确认、已包含是不同状态，不能混用。"],
    ]
    return {
        "外发价格单": external,
        "选配与不重复收费": rule_rows,
        "Odoo现价状态": status_rows,
        "历史软件参考": ref_rows,
        "待确认清单": pending,
    }


def col_name(n):
    s = ""
    while n:
        n, r = divmod(n - 1, 26)
        s = chr(65 + r) + s
    return s


def cell_xml(r, c, value, style=None):
    ref = f"{col_name(c)}{r}"
    s_attr = f' s="{style}"' if style is not None else ""
    if value is None:
        value = ""
    if isinstance(value, (int, float)) and not isinstance(value, bool):
        return f'<c r="{ref}"{s_attr}><v>{value}</v></c>'
    text = escape(str(value))
    return f'<c r="{ref}" t="inlineStr"{s_attr}><is><t>{text}</t></is></c>'


def sheet_xml(rows, widths=None):
    max_cols = max((len(r) for r in rows), default=1)
    cols = []
    widths = widths or {}
    for i in range(1, max_cols + 1):
        w = widths.get(i, 16)
        cols.append(f'<col min="{i}" max="{i}" width="{w}" customWidth="1"/>')
    row_xml = []
    for ridx, row in enumerate(rows, 1):
        cells = []
        for cidx, value in enumerate(row, 1):
            style = None
            if ridx == 1:
                style = 1
            elif ridx == 6 and rows[0] and rows[0][0] == "康比特智慧食堂软件端价格单":
                style = 2
            elif ridx == 1 or (row and str(row[0]).endswith("项")):
                style = 2
            cells.append(cell_xml(ridx, cidx, value, style))
        row_xml.append(f'<row r="{ridx}">{"".join(cells)}</row>')
    return (
        '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
        '<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" '
        'xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">'
        '<sheetViews><sheetView workbookViewId="0"><pane ySplit="1" topLeftCell="A2" activePane="bottomLeft" state="frozen"/></sheetView></sheetViews>'
        f'<cols>{"".join(cols)}</cols><sheetData>{"".join(row_xml)}</sheetData>'
        '</worksheet>'
    )


def write_xlsx(sheets):
    OUT_DIR.mkdir(parents=True, exist_ok=True)
    rels = []
    workbook_sheets = []
    content_overrides = [
        '<Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>',
        '<Default Extension="xml" ContentType="application/xml"/>',
        '<Override PartName="/xl/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>',
        '<Override PartName="/xl/styles.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"/>',
    ]
    for idx, name in enumerate(sheets, 1):
        workbook_sheets.append(f'<sheet name="{escape(name)}" sheetId="{idx}" r:id="rId{idx}"/>')
        rels.append(f'<Relationship Id="rId{idx}" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheets/sheet{idx}.xml"/>')
        content_overrides.append(f'<Override PartName="/xl/worksheets/sheet{idx}.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>')
    rels.append(f'<Relationship Id="rId{len(sheets)+1}" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles" Target="styles.xml"/>')
    workbook = (
        '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
        '<workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" '
        'xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">'
        f'<sheets>{"".join(workbook_sheets)}</sheets></workbook>'
    )
    styles = (
        '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
        '<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">'
        '<fonts count="3"><font><sz val="11"/><name val="Microsoft YaHei"/></font><font><b/><sz val="16"/><name val="Microsoft YaHei"/><color rgb="FF1F4E46"/></font><font><b/><sz val="11"/><name val="Microsoft YaHei"/></font></fonts>'
        '<fills count="3"><fill><patternFill patternType="none"/></fill><fill><patternFill patternType="gray125"/></fill><fill><patternFill patternType="solid"><fgColor rgb="FFFFE3C2"/><bgColor indexed="64"/></patternFill></fill></fills>'
        '<borders count="2"><border/><border><left style="thin"><color rgb="FFD9D9D9"/></left><right style="thin"><color rgb="FFD9D9D9"/></right><top style="thin"><color rgb="FFD9D9D9"/></top><bottom style="thin"><color rgb="FFD9D9D9"/></bottom></border></borders>'
        '<cellStyleXfs count="1"><xf numFmtId="0" fontId="0" fillId="0" borderId="0"/></cellStyleXfs>'
        '<cellXfs count="3"><xf numFmtId="0" fontId="0" fillId="0" borderId="1" xfId="0" applyBorder="1"/><xf numFmtId="0" fontId="1" fillId="0" borderId="0" xfId="0" applyFont="1"/><xf numFmtId="0" fontId="2" fillId="2" borderId="1" xfId="0" applyFont="1" applyFill="1" applyBorder="1"/></cellXfs>'
        '<cellStyles count="1"><cellStyle name="Normal" xfId="0" builtinId="0"/></cellStyles>'
        '</styleSheet>'
    )
    root_rels = (
        '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
        '<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">'
        '<Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/>'
        '</Relationships>'
    )
    wb_rels = (
        '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
        '<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">'
        + "".join(rels)
        + '</Relationships>'
    )
    content_types = (
        '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>'
        '<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">'
        + "".join(content_overrides)
        + '</Types>'
    )
    widths = {
        "外发价格单": {1: 13, 2: 26, 3: 15, 4: 16, 5: 13, 6: 54, 7: 42},
        "选配与不重复收费": {1: 22, 2: 92},
        "Odoo现价状态": {1: 14, 2: 28, 3: 16, 4: 12, 5: 14, 6: 66},
        "历史软件参考": {1: 18, 2: 32, 3: 18, 4: 18, 5: 14, 6: 14, 7: 14, 8: 14, 9: 60},
        "待确认清单": {1: 34, 2: 24, 3: 70},
    }
    with zipfile.ZipFile(OUT_XLSX, "w", zipfile.ZIP_DEFLATED) as z:
        z.writestr("[Content_Types].xml", content_types)
        z.writestr("_rels/.rels", root_rels)
        z.writestr("xl/workbook.xml", workbook)
        z.writestr("xl/_rels/workbook.xml.rels", wb_rels)
        z.writestr("xl/styles.xml", styles)
        for idx, (name, rows) in enumerate(sheets.items(), 1):
            z.writestr(f"xl/worksheets/sheet{idx}.xml", sheet_xml(rows, widths.get(name)))
    return OUT_XLSX


def main():
    matrix, products = collect_sources()
    sheets = build_rows(matrix, products)
    path = write_xlsx(sheets)
    print(path)
    print(SNAPSHOT_JSON)


if __name__ == "__main__":
    main()