# -*- coding: utf-8 -*-
"""从 MCP 明细 JSON 生成科技部老师业绩明细 Excel（2026-03）。

JSON 须由「对齐日报落月」的 SQL 生成，见同目录 mcp_tech_kjb_detail_aligned_202603.sql：
  开单：按 lq_kd_kdjlb.kdrq 落在统计月（不再仅用 lq_kd_kjbsyj.yjsj 筛月）；
  退卡：按 lq_hytk_hytk.tksj 落在统计月（不再仅用 lq_hytk_kjbsyj.tksj 筛月）。
品项仍默认 F_ItemCategory=科美（与历史 MCP 导出一致）。"""
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_detail_202603.json"
OUT_XLSX = ROOT / "2026年3月_科技部老师业绩明细_MCP.xlsx"

HEADERS = [
    ("bizType", "业务类型"),
    ("storeName", "门店名称"),
    ("teacherId", "科技部老师ID"),
    ("teacherName", "科技部老师姓名"),
    ("teacherAccount", "科技部老师账号"),
    ("itemName", "品项名称"),
    ("itemId", "品项ID"),
    ("itemCategory", "品项分类"),
    ("beautyType", "科美类型"),
    ("amount", "业绩金额"),
    ("bizTime", "业绩时间"),
    ("refBillingId", "关联单号"),
    ("billingItemLineId", "明细行ID"),
    ("laborCost", "手工费"),
    ("billingDate", "单据日期"),
]


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

    wb = Workbook()
    ws = wb.active
    ws.title = "科技部老师业绩明细"

    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=10)
    header_align = Alignment(horizontal="center", vertical="center", wrap_text=True)
    total_fill = PatternFill("solid", fgColor="FFF2F2F2")
    total_font = Font(bold=True)

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

    keys = [k for k, _ in HEADERS]
    amount_col = keys.index("amount") + 1
    labor_col = keys.index("laborCost") + 1

    for r_i, r in enumerate(rows, start=2):
        for col, key in enumerate(keys, start=1):
            val = r.get(key)
            if val is None:
                val = ""
            elif key in ("amount", "laborCost"):
                val = float(val) if val != "" else 0
            c = ws.cell(row=r_i, column=col, value=val)
            c.border = grid
            if key in ("amount", "laborCost"):
                c.number_format = "#,##0.00"
                c.alignment = Alignment(horizontal="right", vertical="center")
            elif key in ("bizTime", "billingDate"):
                c.alignment = Alignment(horizontal="left", vertical="center")
            else:
                c.alignment = Alignment(horizontal="left", vertical="center", wrap_text=False)

    last_data = 1 + len(rows)
    total_row = last_data + 1
    amt_letter = get_column_letter(amount_col)
    lab_letter = get_column_letter(labor_col)

    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
    for col in range(2, len(HEADERS) + 1):
        ws.cell(row=total_row, column=col).fill = total_fill
        ws.cell(row=total_row, column=col).border = grid

    ws.cell(
        row=total_row,
        column=amount_col,
        value=f"=SUM({amt_letter}2:{amt_letter}{last_data})",
    )
    ws.cell(row=total_row, column=amount_col).number_format = "#,##0.00"
    ws.cell(row=total_row, column=amount_col).font = total_font
    ws.cell(row=total_row, column=amount_col).alignment = Alignment(horizontal="right")

    ws.cell(
        row=total_row,
        column=labor_col,
        value=f"=SUM({lab_letter}2:{lab_letter}{last_data})",
    )
    ws.cell(row=total_row, column=labor_col).number_format = "#,##0.00"
    ws.cell(row=total_row, column=labor_col).font = total_font
    ws.cell(row=total_row, column=labor_col).alignment = Alignment(horizontal="right")

    ws.freeze_panes = "A2"
    ws.auto_filter.ref = f"A1:{get_column_letter(len(HEADERS))}{last_data}"

    widths = [8, 16, 18, 12, 14, 22, 20, 8, 10, 12, 20, 20, 20, 10, 20]
    for i, w in enumerate(widths, 1):
        ws.column_dimensions[get_column_letter(i)].width = w

    note_row = total_row + 2
    note = (
        "数据来源：MCP 只读查询，落月与日报科技部老师统计一致。"
        "开单=lq_kd_kjbsyj 关联 lq_kd_kdjlb，科美、有效，按单据 kdrq 落在 2026-03；"
        "业绩时间列 bizTime 仍为子表 yjsj，单据日期 billingDate 为 kdrq。"
        "退卡=lq_hytk_kjbsyj 关联 lq_hytk_hytk，科美、有效，按主表 tksj 落在 2026-03；"
        "bizTime 为子表 tksj，billingDate 为主表 tksj。"
        "关联单号：开单 glkdbh，退卡 gltkbh；明细行：开单 F_kdpxid，退卡 F_CardReturn。"
        "取数 SQL 模板：mcp_tech_kjb_detail_aligned_202603.sql"
    )
    ws.merge_cells(start_row=note_row, start_column=1, end_row=note_row + 1, end_column=len(HEADERS))
    nc = ws.cell(row=note_row, column=1, value=note)
    nc.font = Font(size=9, color="FF666666")
    nc.alignment = Alignment(wrap_text=True, vertical="top")

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


if __name__ == "__main__":
    main()