#!/usr/bin/env python3
"""wx-quote-assistant — Excel 报价单生成器

输入 full_data.json，输出标准格式的 .xlsx 报价单。
样式：深蓝表头 + 浅色备注行，6 章节结构。

CLI 用法:
  python3 excel_builder.py build --data <full_data.json> --output <path>
"""

import argparse
import json
import os
import sys

import openpyxl
from openpyxl.styles import Font, Alignment, Border, Side, PatternFill, Color
from openpyxl.utils import get_column_letter

# ── 色板 (匹配立镁家风格) ──
PRIMARY = "001a5276"
MUTED = "00718096"
DARK = "00000000"
WHITE = "00FFFFFF"
SUCCESS = "001e8449"
WARN = "00e67e22"
DANGER = "00e74c3c"
GREEN_BG = "00d5f5e3"
YELLOW_BG = "00fef9e7"
BLUE_BG = "00ebf5fb"
LIGHT_BG = "00f8fafc"
DARK_BLUE_BG = "001a5276"

NO_FILL = PatternFill(fill_type=None)


def _pct(v, total):
    if total == 0:
        return "0%"
    return f"{round(v / total * 100, 1)}%"


def _fmt(n):
    return f"{n:,}"


def build_excel(data: dict, output_path: str) -> str:
    """从 full_data.json 构建标准 Excel 报价单。"""
    products = data.get("products", [])
    cat_totals = {}
    for p in products:
        cat = p.get("category", "其他")
        cat_totals[cat] = cat_totals.get(cat, 0) + p.get("subtotal", 0)

    discount_total = sum(
        p.get("unit_price", 0) * p.get("qty", 0) - p.get("subtotal", 0)
        for p in products
    )

    wb = openpyxl.Workbook()
    ws = wb.active
    ws.title = "报价单"

    for i, w in enumerate([18, 16, 24, 14, 16, 18, 18], 1):
        ws.column_dimensions[get_column_letter(i)].width = w
    ws.row_dimensions[1].height = 36

    thin_side = Side(style="thin", color="00e2e8f0")
    thin_border = Border(
        left=thin_side, right=thin_side, top=thin_side, bottom=thin_side
    )
    no_border = Border()

    def sc(row, col, value, font_name="Arial", font_size=11, bold=False,
           color=DARK, fill=NO_FILL, halign="left", valign=None,
           wrap=False, border=thin_border):
        cell = ws.cell(row=row, column=col, value=value)
        cell.font = Font(name=font_name, size=font_size, bold=bold,
                         color=Color(rgb=color) if color else None)
        cell.fill = fill
        cell.alignment = Alignment(horizontal=halign, vertical=valign, wrap_text=wrap)
        cell.border = border
        return cell

    def ms(r1, c1, r2, c2, value, **kwargs):
        ws.merge_cells(start_row=r1, start_column=c1, end_row=r2, end_column=c2)
        return sc(r1, c1, value, **kwargs)

    # ── HEADER ──
    company = data.get("company_name", "")
    ms(1, 1, 1, 7, f"{company} · 项目报价单",
       font_size=22, bold=True, color=PRIMARY, border=no_border)
    ms(2, 1, 2, 7, "工业节能改造 · 设备运行监测 · 维保服务 · 整体解决方案",
       font_size=11, color=MUTED, border=no_border)
    ms(3, 1, 3, 3,
       f"报价编号：{data.get('quote_no','')}　|　客户：{data.get('client_name','')}",
       font_size=10, color=MUTED, border=no_border)
    ms(3, 4, 3, 7,
       f"报价日期：{data.get('quote_date','')}　|　有效期：{data.get('valid_until','')}（30天）",
       font_size=10, color=MUTED, halign="right", border=no_border)
    ms(4, 1, 4, 3,
       f"报价人：{data.get('quoter_name','')}　|　{data.get('quoter_phone','')}",
       font_size=10, color=MUTED, border=no_border)
    ms(4, 4, 4, 7,
       f"邮箱：{data.get('quoter_email','')}",
       font_size=10, color=MUTED, halign="right", border=no_border)

    r = 6

    # ── 一、项目概况 ──
    ms(r, 1, r, 7, "一、项目概况", font_size=14, bold=True, color=PRIMARY, border=no_border)
    r += 1
    project_rows = data.get("project_overview", [])
    for kv in project_rows:
        sc(r, 1, kv[0], color=MUTED); ms(r, 2, r, 3, kv[1], bold=True)
        sc(r, 4, kv[2], color=MUTED); ms(r, 5, r, 7, kv[3], bold=True)
        r += 1
    r += 1

    # ── 二、产品明细 ──
    ms(r, 1, r, 7, "二、产品与服务明细", font_size=14, bold=True, color=PRIMARY, border=no_border)
    r += 1
    hdr_fill = PatternFill(start_color=DARK_BLUE_BG, end_color=DARK_BLUE_BG, fill_type="solid")
    for i, h in enumerate(["序号", "型号", "名称", "数量", "单位", "单价（¥）", "小计（¥）"], 1):
        sc(r, i, h, font_size=10, bold=True, color=WHITE, fill=hdr_fill,
           halign="center", valign="center")
    r += 1
    for p in products:
        cat_label = f"{p.get('name','')} [{p.get('category','')}]"
        sc(r, 1, p.get("seq", ""), halign="center")
        sc(r, 2, p.get("model", ""), bold=True)
        sc(r, 3, cat_label)
        sc(r, 4, p.get("qty", ""), halign="center")
        sc(r, 5, p.get("unit", ""), halign="center")
        sc(r, 6, p.get("unit_price", ""), halign="right")
        sc(r, 7, p.get("subtotal", ""), bold=True, halign="right")
        r += 1
    r += 1

    # ── 三、价格汇总 ──
    ms(r, 1, r, 7, "三、价格汇总", font_size=14, bold=True, color=PRIMARY, border=no_border)
    r += 1
    for c, h in [(1, "序号"), (2, "项目"), (6, "小计（¥）"), (7, "备注")]:
        sc(r, c, h, font_size=10, bold=True, color=WHITE, fill=hdr_fill,
           halign="center", valign="center")
    if c == 2:
        ms(r, 2, r, 5, h, font_size=10, bold=True, color=WHITE, fill=hdr_fill,
           halign="center", valign="center")
    r += 1

    cat_order = data.get("category_order", ["设备", "硬件", "软件", "服务", "备件"])
    cat_names = data.get("category_names", {
        "设备": "设备类", "硬件": "硬件类", "软件": "软件类",
        "服务": "服务类", "备件": "备件类"
    })
    seq = 1
    for cat in cat_order:
        total = cat_totals.get(cat, 0)
        if total == 0:
            continue
        sc(r, 1, seq, halign="center")
        ms(r, 2, r, 5, cat_names.get(cat, cat), bold=True)
        sc(r, 6, total, bold=True, halign="right")
        seq += 1
        r += 1

    # Discount row
    green_fill = PatternFill(start_color=GREEN_BG, end_color=GREEN_BG, fill_type="solid")
    ms(r, 2, r, 5, "整单折扣合计", bold=True, color=SUCCESS, fill=green_fill, halign="right")
    sc(r, 6, -discount_total, bold=True, color=SUCCESS, fill=green_fill, halign="right")
    ms(r, 7, r, 7, data.get("discount_desc", ""), font_size=10, color=SUCCESS, fill=green_fill)
    r += 1

    blue_fill = PatternFill(start_color=BLUE_BG, end_color=BLUE_BG, fill_type="solid")
    ms(r, 2, r, 5, "折扣后合计（未含税）", bold=True, color=DANGER, fill=blue_fill, halign="right")
    sc(r, 6, data.get("discounted_total", 0), font_size=14, bold=True,
       color=DANGER, fill=blue_fill, halign="right")
    ms(r, 7, r, 7, "已含全部折扣 ✓", font_size=10, color=DANGER, fill=blue_fill)
    r += 1

    ms(r, 2, r, 5, "增值税费（专票 13%）")
    sc(r, 6, round(data.get("tax_amount", 0)), halign="right")
    sc(r, 7, "13%增值税专票", font_size=10, color=MUTED)
    r += 1

    shipping = data.get("shipping_fee", 0)
    ms(r, 2, r, 5, "运费")
    sc(r, 6, shipping, halign="right")
    sc(r, 7, "同城免运费" if shipping == 0 else "", font_size=10, color=MUTED)
    r += 1

    ms(r, 2, r, 5, "含税总计", bold=True, color=PRIMARY, fill=blue_fill, halign="right")
    sc(r, 6, round(data.get("grand_total", 0)), font_size=14, bold=True,
       color=PRIMARY, fill=blue_fill, halign="right")
    ms(r, 7, r, 7, "专票含税 ✓", font_size=10, color=PRIMARY, fill=blue_fill)
    r += 1

    ms(r, 2, r, 5, "大写金额", bold=True, color=PRIMARY, halign="right")
    ms(r, 6, r, 7, data.get("grand_total_cn", ""), bold=True, color=PRIMARY, halign="left")
    r += 2

    # ── 四、方案概览 (从 data 读取) ──
    tech_specs = data.get("tech_overview", [])
    if tech_specs:
        ms(r, 1, r, 7, "四、方案技术概览", font_size=14, bold=True,
           color=PRIMARY, border=no_border)
        r += 1
        for k, v in tech_specs:
            sc(r, 1, k, color=MUTED); ms(r, 2, r, 7, v)
            r += 1
        r += 1

    # ── 五、商务条款 ──
    ms(r, 1, r, 7, "五、商务条款", font_size=14, bold=True,
       color=PRIMARY, border=no_border)
    r += 1
    for k, v in [
        ("供货周期", data.get("delivery_date", "")),
        ("付款条件", data.get("payment_terms", "")),
        ("质保条款", data.get("warranty", "")),
        ("服务范围", data.get("service_scope", "")),
        ("不含项", data.get("delivery_boundary", "")),
    ]:
        sc(r, 1, k, color=MUTED); ms(r, 2, r, 7, v)
        r += 1
    r += 1

    # ── FOOTER ──
    ms(r, 1, r, 7, data.get("footer_text", f"{company} · 工业节能改造"),
       font_size=9, color="00a0aec0", border=no_border)

    # 打印设置
    ws.sheet_properties.pageSetUpPr = openpyxl.worksheet.properties.PageSetupProperties(
        fitToPage=True)
    ws.page_setup.fitToWidth = 1

    os.makedirs(os.path.dirname(output_path) or ".", exist_ok=True)
    wb.save(output_path)
    return output_path


# ── CLI ──

def cmd_build(args):
    if args.data == "-":
        data = json.load(sys.stdin)
    else:
        with open(args.data, "r", encoding="utf-8") as f:
            data = json.load(f)
    out = build_excel(data, args.output)
    print(json.dumps({"ok": True, "output": out}, ensure_ascii=False))


def main():
    parser = argparse.ArgumentParser(description="wx-quote-assistant Excel Builder")
    sub = parser.add_subparsers(dest="command", required=True)

    p = sub.add_parser("build", help="Build Excel from full_data.json")
    p.add_argument("--data", required=True, help="Path to full_data.json")
    p.add_argument("--output", required=True, help="Output .xlsx path")
    p.set_defaults(func=cmd_build)

    args = parser.parse_args()
    args.func(args)


if __name__ == "__main__":
    main()
