"""Excel输出：工具函数 + 各类sheet生成"""
import openpyxl
from openpyxl.styles import Font, Alignment, Border, Side, PatternFill
from collections import defaultdict
from calc_engine import RULES, DAYS

hf = Font(bold=True)
hfl = PatternFill(start_color='D9E1F2', end_color='D9E1F2', fill_type='solid')
bd = Border(left=Side(style='thin'),right=Side(style='thin'),top=Side(style='thin'),bottom=Side(style='thin'))

def WH(ws, headers, row=1):
    for c, h in enumerate(headers, 1):
        cl = ws.cell(row=row, column=c, value=h)
        cl.font=hf; cl.fill=hfl; cl.border=bd; cl.alignment=Alignment(horizontal='center', wrap_text=True)

def WR(ws, rn, vals):
    for c, v in enumerate(vals, 1):
        cl = ws.cell(row=rn, column=c, value=v); cl.border=bd

def AW(ws):
    for col in ws.columns:
        ml = max((len(str(c.value or '')) for c in col), default=0)
        ws.column_dimensions[col[0].column_letter].width = min(ml+3, 22)

def R(v, n=2):
    return round(v, n) if isinstance(v, (int,float)) and v else v

def agg(dl):
    bp = defaultdict(lambda: {'n':0,'a':0})
    for d in dl: bp[d['销售']]['n']+=1; bp[d['销售']]['a']+=d['额']
    return dict(bp)

def write_sec(ws, rn, title, dl):
    ws.cell(row=rn, column=1, value=title).font=Font(bold=True, size=11); rn+=1
    WH(ws, ['销售人员','车辆数','金额'], rn); rn+=1
    bp=agg(dl); tv=ta=0
    for p in sorted(bp.keys()):
        WR(ws,rn,[p,bp[p]['n'],R(bp[p]['a'])]); tv+=bp[p]['n']; ta+=bp[p]['a']; rn+=1
    WR(ws,rn,['总计',tv,R(ta)]); ws.cell(row=rn,column=1).font=hf; return rn+2

def write_total(ws, rn, month, all_data):
    total_bp = defaultdict(lambda: {'部门':'','额':0})
    for cat,dl in all_data.items():
        for d in dl: total_bp[d['销售']]['额']+=d['额']; total_bp[d['销售']]['部门']=d['部门']
    ws.cell(row=rn,column=1,value=f'{month}月合计应发奖励（按销售人员）').font=Font(bold=True,size=11); rn+=1
    WH(ws,['销售人员','部门名称','合计应发奖励'],rn); rn+=1
    gt=0
    for p in sorted(total_bp.keys()):
        WR(ws,rn,[p,total_bp[p]['部门'],R(total_bp[p]['额'])]); gt+=total_bp[p]['额']; rn+=1
    WR(ws,rn,['合计','',R(gt)]); ws.cell(row=rn,column=1).font=hf; rn+=2
    by_dept=defaultdict(float)
    for p,d in total_bp.items(): by_dept[d['部门']]+=d['额']
    ws.cell(row=rn,column=1,value=f'{month}月合计应发奖励（按部门）').font=Font(bold=True,size=11); rn+=1
    WH(ws,['部门名称','合计应发奖励'],rn); rn+=1
    for dept in sorted(by_dept.keys()): WR(ws,rn,[dept,R(by_dept[dept])]); rn+=1
    WR(ws,rn,['合计',R(gt)]); ws.cell(row=rn,column=1).font=hf

# ============================================================
# Sheet生成函数
# ============================================================

def write_rules_sheet(wb):
    ws = wb.active; ws.title='考核奖励规则'
    WH(ws,['考核目标','月度目标里程(km)','奖励金额(元)'])
    for i,(n,km,b) in enumerate([('交投40辆4.5T普货',3000,150),('交投190辆4.5T冷链车',3000,150),
        ('羚牛136辆4.5T冷链车',5000,260),('恒运50辆4.5T普货',5000,260),('羚牛100辆18T',6000,1000)],2):
        WR(ws,i,[n,km,b])
    AW(ws)

def write_detail_sheet(wb, records, month):
    ws = wb.create_sheet(f'里程明细{month}月')
    WH(ws,['车牌号','部门名称','销售经理','客户名称','合同编号','考核目标','月度目标里程','对应月奖励金额',
            '考核天数',f'{month}月应考核里程',f'{month}月实际行驶里程','完成率',f'{month}月是否达标',
            f'{month}月奖金(天数折算)','多跑里程','可结转月数','考核状态'])
    rn=2
    for r in sorted(records, key=lambda x: (x['销售经理'],x['车牌号'])):
        t,a = r['应考核里程(km)'], r['实际行驶里程(km)']
        WR(ws,rn,[r['车牌号'],r['部门名称'],r['销售经理'],r.get('客户名称',''),r.get('合同编号',''),
            r.get('考核目标',''),r['月度目标里程'],r['对应月奖励金额'],r['考核天数'],
            R(t),R(a),R(a/t,4) if t>0 else 0,r['是否达标'],R(r['奖金']),R(r['多跑']),r['可结转'],
            r.get('考核状态','')]); rn+=1
    AW(ws)

def write_calc_process_jan(wb, G1):
    ws = wb.create_sheet('1月计算过程')
    WH(ws,['车牌号','销售经理','部门','①记录数','②总考核天数','③总应考核里程','④总实际里程',
            '⑤有达标记录','⑥达标记录奖金合计','⑦总多跑里程','⑧月度目标里程','⑨可结转月数=floor(⑦/⑧)',
            '→1月发放金额','→发放类型'])
    rn=2
    for k in sorted(G1.keys(), key=lambda x: (x[1],x[0])):
        g=G1[k]
        excess=sum(r['多跑'] for r in g['recs'] if r['是否达标']=='达标')
        WR(ws,rn,[k[0],k[1],g['部门'],len(g['recs']),g['天数'],R(g['应考核']),R(g['实际']),
            '是' if g['有达标'] else '否',R(g['奖金']),R(excess),g['目标km'],g['可结转'],
            R(g['奖金']) if g['有达标'] else 0,'当月达标' if g['有达标'] else '未达标']); rn+=1
    AW(ws)

def write_calc_process_feb(wb, G1, G2):
    ws = wb.create_sheet('2月计算过程')
    WH(ws,['车牌号','销售经理','部门',
        '1月总应考核','1月总实际','1月有达标','1月奖金','1月多跑','1月可结转',
        '2月记录数','2月总天数','2月总应考核','2月总实际','2月有达标','2月达标奖金',
        '累计应完成','累计实际','累计是否达标',
        '判断①结转','→结转金额(完整月)','判断②补发1月','→补发1月金额',
        '判断③当月','→2月当月金额','判断④累计补发2月','→累计补发2月金额','2月发放合计'])
    rn=2
    for k in sorted(G2.keys(), key=lambda x: (x[1],x[0])):
        g2=G2[k]; g1=G1.get(k)
        j_q=g1['有达标'] if g1 else False; j_c=g1['可结转'] if g1 else 0
        j_t=g1['应考核'] if g1 else 0; j_a=g1['实际'] if g1 else 0
        j_bonus=g1['奖金'] if g1 else 0
        je=sum(r['多跑'] for r in g1['recs'] if r['是否达标']=='达标') if g1 else 0
        ct=g2.get('cum_t',0); ca=g2.get('cum_a',0); cq=g2.get('cum_q',False)
        carry=g2.get('结转',0); bp1=g2.get('补发1月',0); bonus2=g2.get('当月奖金',0); cbp2=g2.get('累计补发2月',0)
        c1=f"1月达标={j_q},可结转={j_c}→{'是' if carry>0 else '否'}"
        c2=f"1月未达标={not j_q},累计达标={cq}→{'是' if bp1>0 else '否'}" if g1 else "无1月"
        c3=f"2月达标={g2['有达标']},无结转={carry==0}→{'是' if bonus2>0 else '否'}"
        c4=f"2月未达标={not g2['有达标']},无结转={carry==0},累计达标={cq}→{'是' if cbp2>0 else '否'}"
        total=carry+bp1+bonus2+cbp2
        WR(ws,rn,[k[0],k[1],g2['部门'],R(j_t),R(j_a),'是' if j_q else '否',R(j_bonus),R(je),j_c,
            len(g2['recs']),g2['天数'],R(g2['应考核']),R(g2['实际']),'是' if g2['有达标'] else '否',R(g2['奖金']),
            R(ct),R(ca),'达标' if cq else '未达标',
            c1,R(carry) if carry>0 else 0,c2,R(bp1) if bp1>0 else 0,
            c3,R(bonus2) if bonus2>0 else 0,c4,R(cbp2) if cbp2>0 else 0,R(total) if total>0 else 0]); rn+=1
    AW(ws)

def write_calc_process_mar(wb, G1, G2, G3, feb_data):
    ws = wb.create_sheet('3月计算过程')
    WH(ws,['车牌号','销售经理','部门',
        '1月应考核','1月实际','1月有达标','1月奖金已发',
        '2月应考核','2月实际','2月有达标','2月奖金已发',
        '3月记录数','3月总天数','3月总应考核','3月总实际','3月有达标','3月达标奖金',
        '累计应完成','累计实际','累计是否达标',
        '判断①结转','→结转金额','判断②补发1月','→补发1月金额',
        '判断③补发2月','→补发2月金额','判断④当月','→3月当月金额',
        '判断⑤累计补发3月','→累计补发3月金额','3月发放合计'])
    rn=2
    for k in sorted(G3.keys(), key=lambda x: (x[1],x[0])):
        g3=G3[k]; g2=G2.get(k); g1=G1.get(k)
        j_t=g1['应考核'] if g1 else 0; j_a=g1['实际'] if g1 else 0
        j_q=g1['有达标'] if g1 else False; j_paid=(g1['奖金']>0) if g1 else False
        if g1 and j_q: j_paid=True
        if g2 and g2.get('1月已补发',False): j_paid=True
        f_t=g2['应考核'] if g2 else 0; f_a=g2['实际'] if g2 else 0
        f_q=g2['有达标'] if g2 else False; f_paid=g2['2月已发'] if g2 else False
        f_carry=g2['可结转'] if g2 else 0
        ct=g3.get('cum_t',0); ca=g3.get('cum_a',0); cq=g3.get('cum_q',False)
        carry=g3.get('结转',0); bj=g3.get('补发1月',0); bf2=g3.get('补发2月',0)
        bonus3=g3.get('当月奖金',0); cbp3=g3.get('累计补发3月',0)
        c1=f"2月可结转={f_carry}→{'是' if carry>0 else '否'}"
        c2=f"1月未发={not j_paid},累计达标={cq}→{'是' if bj>0 else '否'}"
        c3=f"2月未发={not f_paid},累计达标={cq}→{'是' if bf2>0 else '否'}"
        c4=f"3月达标={g3['有达标']},无结转={carry==0}→{'是' if bonus3>0 else '否'}"
        c5=f"3月未达标={not g3['有达标']},无结转={carry==0},累计达标={cq}→{'是' if cbp3>0 else '否'}"
        total=carry+bj+bf2+bonus3+cbp3
        WR(ws,rn,[k[0],k[1],g3['部门'],R(j_t),R(j_a),'是' if j_q else '否','是' if j_paid else '否',
            R(f_t),R(f_a),'是' if f_q else '否','是' if f_paid else '否',
            len(g3['recs']),g3['天数'],R(g3['应考核']),R(g3['实际']),'是' if g3['有达标'] else '否',R(g3['奖金']),
            R(ct),R(ca),'达标' if cq else '未达标',
            c1,R(carry) if carry>0 else 0,c2,R(bj) if bj>0 else 0,
            c3,R(bf2) if bf2>0 else 0,c4,R(bonus3) if bonus3>0 else 0,
            c5,R(cbp3) if cbp3>0 else 0,R(total) if total>0 else 0]); rn+=1
    AW(ws)

def write_summary_jan(wb, records):
    ws = wb.create_sheet('1月汇总')
    jan_dl=[{'车牌':r['车牌号'],'销售':r['销售经理'],'部门':r['部门名称'],'额':r['奖金']} for r in records if r['是否达标']=='达标']
    rn=write_sec(ws,1,'1月达标奖励',jan_dl)
    write_total(ws,rn,1,{'达标':jan_dl})
    AW(ws)

def write_summary_month(wb, month, month_data, section_names):
    ws = wb.create_sheet(f'{month}月汇总')
    rn=1
    for i,cat in enumerate(section_names):
        label_map = {'结转':f'一、结转：{month-1}月多跑在{month}月发',
                     '补发1月':'二、补发1月','补发2月':'三、补发2月',
                     '当月':f'{"三" if month==2 else "四"}、{month}月当月奖励',
                     f'累计补发{month}月':f'{"四" if month==2 else "五"}、累计达标补发{month}月'}
        rn=write_sec(ws,rn,label_map.get(cat,cat),month_data.get(cat,[]))
    write_total(ws,rn,month,month_data)
    AW(ws)

# ============================================================
# 新增：业务员sheet
# ============================================================

def write_salesperson_sheet(wb, person, dept, settle_month, D, G, month_data, vehicle_payments):
    ws = wb.create_sheet(f'业务员_{person}')
    ws.cell(row=1,column=1,value=f'{person} | {dept} | {settle_month}月考核').font=Font(bold=True,size=14)

    # 收集该人在1-settle_month中有记录的所有车牌
    plates = set()
    for m in range(1, settle_month+1):
        for k in G.get(m, {}):
            if k[1] == person: plates.add(k[0])

    # 本月发放总额
    person_total = sum(d['额'] for cat,dl in month_data.items() for d in dl if d['销售']==person)
    ws.cell(row=2,column=1,value=f'本月考核车辆: {len(plates)}辆 | 本月发放合计: {R(person_total)}元').font=Font(bold=True,size=11)

    rn = 4
    for plate in sorted(plates):
        # 车辆信息（取最近月的group）
        g_cur = None
        for m in range(settle_month, 0, -1):
            g_cur = G.get(m, {}).get((plate, person))
            if g_cur: break
        if not g_cur: continue
        first = g_cur['recs'][0]

        ws.cell(row=rn,column=1,value=plate).font=Font(bold=True,size=11)
        ws.cell(row=rn,column=2,value=first.get('合同编号',''))
        ws.cell(row=rn,column=3,value=first.get('客户名称',''))
        ws.cell(row=rn,column=4,value=first.get('考核目标',''))
        ws.cell(row=rn,column=5,value=f'月度目标{g_cur["目标km"]}km | 月奖励{g_cur["奖励额"]}元')
        rn += 1

        # 表头
        headers = ['月份','考核天数','应考核里程','实际里程','完成率','达标','奖金','发放说明']
        if settle_month >= 2:
            headers += ['累计应完成','累计实际','累计达标']
        WH(ws, headers, rn); rn += 1

        # 每月一行
        cum_t = 0; cum_a = 0
        for m in range(1, settle_month+1):
            gm = G.get(m, {}).get((plate, person))
            if not gm:
                WR(ws, rn, [f'{m}月','','','','','无记录']); rn+=1; continue

            t=gm['应考核']; a=gm['实际']; cum_t+=t; cum_a+=a
            qual='达标' if gm['有达标'] else '未达标'
            rate=R(a/t*100,0) if t>0 else 0
            mkm=gm['目标km']

            # 发放金额和说明（动态生成，包含关键数字）
            pay_amt=0; desc=''
            if m == settle_month:
                carry=gm.get('结转',0); bonus=gm.get('当月奖金',0)
                cum_bp=gm.get(f'累计补发{m}月',0)
                excess=sum(r['多跑'] for r in gm['recs'] if r['是否达标']=='达标')
                if carry>0:
                    # 结转：上月多跑够整月
                    prev=m-1
                    g_prev=G.get(prev,{}).get((plate,person))
                    prev_excess=sum(r['多跑'] for r in g_prev['recs'] if r['是否达标']=='达标') if g_prev else 0
                    desc=f'{prev}月多跑{R(prev_excess,0)}≥{mkm}，结转(完整月奖金)'
                    pay_amt=carry
                elif bonus>0:
                    desc='当月达标'
                    if excess>0 and int(excess//mkm)>=1:
                        desc+=f'，多跑{R(excess,0)}≥{mkm}可结转'
                    pay_amt=bonus
                elif cum_bp>0:
                    desc=f'1-{m}月累计{R(cum_a,0)}≥{R(cum_t,0)}，累计达标补发'
                    pay_amt=cum_bp
                elif gm.get('结转占位'):
                    desc='结转占位，本月不另发'
                else:
                    desc=f'未达标(实际{R(a,0)}<目标{R(t,0)})'
            else:
                # 历史月份
                if gm['有达标']:
                    pay_amt=gm.get('奖金',0)
                    desc=f'({m}月已发{R(pay_amt)})'
                else:
                    desc=f'未达标(实际{R(a,0)}<目标{R(t,0)})'

            row_data=[f'{m}月',gm['天数'],R(t),R(a),f'{rate}%',qual,R(pay_amt) if pay_amt>0 else 0,desc]
            if settle_month>=2:
                cum_q='达标' if (cum_a>=cum_t and cum_t>0) else '未达标'
                row_data+=[R(cum_t),R(cum_a),cum_q]
            WR(ws,rn,row_data); rn+=1

        # 补发行（当前月补发之前月份）
        if settle_month >= 2:
            g_s = G.get(settle_month, {}).get((plate, person), {})
            for prev_m in range(1, settle_month):
                bp_key = f'补发{prev_m}月'
                bp_amt = g_s.get(bp_key, 0) if isinstance(g_s, dict) else 0
                if bp_amt > 0:
                    desc=f'1-{settle_month}月累计{R(cum_a,0)}≥{R(cum_t,0)}，补发{prev_m}月'
                    row_data = [f'→补发{prev_m}月','','','','','',R(bp_amt),desc]
                    if settle_month>=2: row_data += ['','','']
                    WR(ws,rn,row_data); rn+=1

        # 奖金池
        pays = vehicle_payments.get(plate, [])
        total_periods = len(pays)
        plate_this = sum(p['金额'] for p in pays if p['结算月']==settle_month and p['业务员']==person)
        ws.cell(row=rn,column=1,value=f'小计: {R(plate_this)}元 | 奖金池: 已发{total_periods}期/共12期, 剩余{12-total_periods}期').font=Font(italic=True)
        rn += 2

    # 尾部
    ws.cell(row=rn,column=1,value=f'{person} {settle_month}月合计: {len(plates)}辆车, 发放 {R(person_total)}元').font=Font(bold=True,size=12)
    AW(ws)

# ============================================================
# 新增：车辆考核追踪sheet
# ============================================================

def write_vehicle_tracking_sheet(wb, settle_month, G, master_vehicles, vehicle_payments, vehicle_info):
    ws = wb.create_sheet('车辆考核追踪')
    headers = ['车牌号','车架号','归属公司','车型','考核目标','月度奖励金额']
    for m in range(1, settle_month+1):
        headers += [f'{m}月业务员',f'{m}月应考核',f'{m}月实际',f'{m}月达标']
    if settle_month >= 2:
        headers += ['累计应完成','累计实际','累计达标']
    headers += ['本月发放金额','发放给谁','发放类型','累计已发期数','累计已发金额','剩余可发期数']
    WH(ws, headers)

    rn=2
    for mv in master_vehicles:
        plate=mv['车牌号']
        info=vehicle_info.get(plate, {})
        pays=vehicle_payments.get(plate, [])
        row=[plate,mv.get('车架号',''),mv.get('归属公司',''),mv.get('车型确定',''),
             info.get('考核目标',''),info.get('月度奖励',0) or '']
        cum_t=0; cum_a=0
        for m in range(1, settle_month+1):
            mgs=[(k,g) for k,g in G.get(m,{}).items() if k[0]==plate]
            if mgs:
                persons=', '.join(sorted(set(g['销售'] for _,g in mgs)))
                ts=sum(g['应考核'] for _,g in mgs); As=sum(g['实际'] for _,g in mgs)
                q='达标' if any(g['有达标'] for _,g in mgs) else '未达标'
                cum_t+=ts; cum_a+=As
                row+=[persons,R(ts),R(As),q]
            else:
                row+=['','','','']
        if settle_month>=2:
            row+=[R(cum_t),R(cum_a),'达标' if (cum_a>=cum_t and cum_t>0) else '未达标']

        # 本月发放（截至settle_month）
        tp=[p for p in pays if p['结算月']==settle_month]
        if tp:
            row+=[R(sum(p['金额'] for p in tp)),', '.join(sorted(set(p['业务员'] for p in tp))),
                  ', '.join(sorted(set(p['类型'] for p in tp)))]
        else:
            row+=[0,'','']

        # 奖金池（截至settle_month）
        pays_to_date=[p for p in pays if p['结算月']<=settle_month]
        tp_count=len(pays_to_date); tp_amt=sum(p['金额'] for p in pays_to_date)
        row+=[tp_count,R(tp_amt) if tp_amt>0 else 0,12-tp_count]
        WR(ws,rn,row); rn+=1
    AW(ws)