# -*- coding: utf-8 -*-
"""一次性脚本：从 MCP 快照 JSON 生成带格式与合计行的科技部业绩 Excel。"""
import json
from pathlib import Path

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

ROOT = Path(__file__).resolve().parent
DATA_JSON = ROOT / "_mcp_tech_kjb_202603_data.json"
OUT_XLSX = ROOT / "2026年3月_科技部业绩_MCP汇总.xlsx"

HEADERS = [
    "门店名称",
    "Cell开单业绩",
    "Cell退款业绩",
    "溯源系统开单业绩",
    "溯源系统退款业绩",
    "Slim开单业绩",
    "Slim退款业绩",
]

NOTE_LINES = [
    "统计口径（MCP 只读查询，与日报科技部老师落月一致）：",
    "开单=lq_kd_kjbsyj INNER JOIN lq_kd_kdjlb，F_ItemCategory=科美，有效；按 kdjlb.kdrq 落在 2026-03；",
    "  对 kjblsyj 按 BeautyType 分列汇总：cell/Cell、溯源系统/溯源、slim/Slim。",
    "退款=lq_hytk_kjbsyj INNER JOIN lq_hytk_hytk，条件同上；按 hytk.tksj 落在 2026-03；对 kjblsyj 求和。",
    "明细级取数见：mcp_tech_kjb_detail_aligned_202603.sql；老师明细 Excel：_generate_mcp_tech_kjb_detail_excel.py",
    "合计行使用 Excel SUM 公式，修改明细行后会自动重算。",
]


def main():
    with open(DATA_JSON, "r", encoding="utf-8") as f:
        rows = json.load(f)

    wb = Workbook()
    ws = wb.active
    ws.title = "202603科技部业绩"

    thin = Side(style="thin", color="FF000000")
    grid = Border(left=thin, right=thin, top=thin, bottom=thin)
    header_fill = PatternFill("solid", fgColor="FF4472C4")
    header_font = Font(bold=True, color="FFFFFFFF", size=11)
    header_align = Alignment(horizontal="center", vertical="center", wrap_text=True)
    total_fill = PatternFill("solid", fgColor="FFF2F2F2")
    total_font = Font(bold=True, size=11)
    num_fmt = "#,##0.00"

    for col, h in enumerate(HEADERS, 1):
        c = ws.cell(row=1, column=col, value=h)
        c.font = header_font
        c.fill = header_fill
        c.alignment = header_align
        c.border = grid

    for i, r in enumerate(rows, start=2):
        ws.cell(row=i, column=1, value=r["storeName"]).border = grid
        for col, key in enumerate(
            ["cellBilling", "cellRefund", "traceBilling", "traceRefund", "slimBilling", "slimRefund"],
            start=2,
        ):
            c = ws.cell(row=i, column=col, value=float(r[key]))
            c.number_format = num_fmt
            c.alignment = Alignment(horizontal="right", vertical="center")
            c.border = grid

    first_data = 2
    last_data = first_data + len(rows) - 1
    total_row = last_data + 1

    ws.cell(row=total_row, column=1, value="合计").font = total_font
    ws.cell(row=total_row, column=1).fill = total_fill
    ws.cell(row=total_row, column=1).border = grid
    ws.cell(row=total_row, column=1).alignment = Alignment(horizontal="center", vertical="center")

    for col in range(2, 8):
        letter = get_column_letter(col)
        c = ws.cell(
            row=total_row,
            column=col,
            value=f"=SUM({letter}{first_data}:{letter}{last_data})",
        )
        c.number_format = num_fmt
        c.font = total_font
        c.fill = total_fill
        c.border = grid
        c.alignment = Alignment(horizontal="right", vertical="center")

    # 表区域加粗外框（合计行已含在底部）
    for r in range(1, total_row + 1):
        for col in range(1, 8):
            ws.cell(row=r, column=col).border = grid

    ws.freeze_panes = "A2"
    ws.auto_filter.ref = f"A1:G{last_data}"
    ws.column_dimensions["A"].width = 22
    for col in range(2, 8):
        ws.column_dimensions[get_column_letter(col)].width = 16

    note_start = total_row + 2
    note_end = note_start + len(NOTE_LINES) - 1
    ws.merge_cells(start_row=note_start, start_column=1, end_row=note_end, end_column=7)
    ncell = ws.cell(row=note_start, column=1, value="\n".join(NOTE_LINES))
    ncell.font = Font(size=9, color="FF666666")
    ncell.alignment = Alignment(wrap_text=True, vertical="top", horizontal="left")

    wb.save(OUT_XLSX)
    print("Saved:", OUT_XLSX)


if __name__ == "__main__":
    main()