#!/usr/bin/env python3
"""按月生成独立Excel文件 - 里程考核绩效"""
import os, openpyxl
from calc_engine import *
from excel_writer import *

os.chdir('/Users/kkfluous/Downloads')

# 读取
print("读取源数据...")
D = {1: read_file('租赁任务考核_2026年1月.xlsx', 1),
     2: read_file('租赁任务考核_2026年2月.xlsx', 2),
     3: read_file('租赁任务考核_2026年3月.xlsx', 3)}
for m in [1,2,3]: print(f"  {m}月: {len(D[m])}条")

# 分组+计算
G = {m: grp(D[m], m) for m in [1,2,3]}
calc_jan_carryover(G[1])
feb_data = calc_feb(G[1], G[2])
mar_data = calc_mar(G[1], G[2], G[3], feb_data)

# 发放追踪
vehicle_payments, vehicle_info = collect_vehicle_payments(G, feb_data, mar_data)
master_vehicles = read_master_vehicles()

# 所有业务员
all_persons = {}
for m in G:
    for k, g in G[m].items():
        all_persons[g['销售']] = g['部门']

# 打印汇总
jan_total = sum(r['奖金'] for r in D[1] if r['是否达标']=='达标')
feb_total = sum(sum(d['额'] for d in v) for v in feb_data.values())
mar_total = sum(sum(d['额'] for d in v) for v in mar_data.values())
print(f"\n1月: {jan_total:.2f}, 2月: {feb_total:.2f}, 3月: {mar_total:.2f}")
print(f"Q1总计: {jan_total + feb_total + mar_total:.2f}")

# ============================================================
# 按月生成
# ============================================================
for settle_month in [1, 2, 3]:
    print(f"\n生成 {settle_month}月核算文件...")
    wb = openpyxl.Workbook()

    # 当月数据
    if settle_month == 1:
        month_data = {'当月': [{'车牌':r['车牌号'],'销售':r['销售经理'],'部门':r['部门名称'],'额':r['奖金']}
                               for r in D[1] if r['是否达标']=='达标']}
    elif settle_month == 2:
        month_data = feb_data
    else:
        month_data = mar_data

    # Sheet 1: 考核奖励规则
    write_rules_sheet(wb)

    # Sheet 2: 里程明细
    write_detail_sheet(wb, D[settle_month], settle_month)

    # Sheet 3: 计算过程
    if settle_month == 1:
        write_calc_process_jan(wb, G[1])
    elif settle_month == 2:
        write_calc_process_feb(wb, G[1], G[2])
    else:
        write_calc_process_mar(wb, G[1], G[2], G[3], feb_data)

    # Sheet 4: 汇总
    if settle_month == 1:
        write_summary_jan(wb, D[1])
    elif settle_month == 2:
        write_summary_month(wb, 2, feb_data, ['结转','补发1月','当月','累计补发2月'])
    else:
        write_summary_month(wb, 3, mar_data, ['结转','补发1月','补发2月','当月','累计补发3月'])

    # Sheet 5: 车辆考核追踪
    write_vehicle_tracking_sheet(wb, settle_month, G, master_vehicles, vehicle_payments, vehicle_info)

    # Sheet 6-17: 业务员
    for person in sorted(all_persons.keys()):
        write_salesperson_sheet(wb, person, all_persons[person], settle_month, D, G, month_data, vehicle_payments)

    # 删除默认空sheet
    if 'Sheet' in wb.sheetnames:
        del wb['Sheet']

    fname = f'里程任务考核_{settle_month}月核算.xlsx'
    wb.save(fname)
    print(f"  ✅ {fname} ({len(wb.sheetnames)} sheets)")