#!/usr/bin/env python3
"""将用户确认的多源核算里程按车牌+考核区间回填到7月考核源表。"""
import os
import re
import shutil
from datetime import datetime
from pathlib import Path

import openpyxl

BASE = Path('/Users/kkfluous/Downloads')
MILEAGE = BASE / '7月考核车辆多源里程汇总_用户确认版.xlsx'
SOURCE = BASE / '7月核算结果/租赁任务考核_2026年7月.xlsx'
OUTPUT_DIR = Path(os.environ.get('JULY_OUTPUT_DIR', BASE / '7月绩效重算_多源考核里程_20260817'))
OUTPUT = OUTPUT_DIR / '租赁任务考核_2026年7月.xlsx'

def norm(v):
    return re.sub(r'\s+', '', str(v or '')).upper()

def day(v):
    return v.date() if isinstance(v, datetime) else v

OUTPUT_DIR.mkdir(parents=True, exist_ok=True)
shutil.copy2(SOURCE, OUTPUT)

mw = openpyxl.load_workbook(MILEAGE, data_only=True)
ms = mw['7月考核车辆里程汇总']
mh = {ms.cell(2, c).value: c for c in range(1, ms.max_column + 1)}
mileage = {}
for r in range(3, ms.max_row + 1):
    key = (norm(ms.cell(r, mh['车牌']).value), day(ms.cell(r, mh['考核开始日期']).value),
           day(ms.cell(r, mh['考核结束日期']).value))
    value = ms.cell(r, mh['核算里程(km)']).value
    if key in mileage:
        raise RuntimeError(f'多源汇总存在重复键: {key}')
    if not isinstance(value, (int, float)):
        raise RuntimeError(f'核算里程不是数值: {key}={value}')
    mileage[key] = float(value)
mw.close()

wb = openpyxl.load_workbook(OUTPUT)
ws = wb['业务考核视图']
h = {ws.cell(1, c).value: c for c in range(1, ws.max_column + 1)}
used = set()
changed = 0
for r in range(2, ws.max_row + 1):
    key = (norm(ws.cell(r, h['车牌号']).value), day(ws.cell(r, h['考核开始日期']).value),
           day(ws.cell(r, h['考核结束日期']).value))
    if key not in mileage:
        raise RuntimeError(f'7月源表记录在多源汇总中缺失: {key}')
    used.add(key)
    actual = round(mileage[key], 2)
    original = float(ws.cell(r, h['原TBOX里程(km)']).value or 0)
    target = float(ws.cell(r, h['应考核里程(km)']).value or 0)
    old_actual = float(ws.cell(r, h['实际行驶里程(km)']).value or 0)
    if abs(old_actual - actual) > 0.005:
        changed += 1
    ws.cell(r, h['实际行驶里程(km)']).value = actual
    ws.cell(r, h['测试里程(km)']).value = round(max(0, original - actual), 2)
    rate = round(actual / target * 100, 2) if target > 0 else 0
    ws.cell(r, h['完成率(%)']).value = rate
    ws.cell(r, h['是否达标']).value = '达标' if actual >= target and target > 0 else '未达标'
    for name in ['原TBOX里程(km)', '实际行驶里程(km)', '测试里程(km)']:
        ws.cell(r, h[name]).number_format = '#,##0.00'
    ws.cell(r, h['完成率(%)']).number_format = '0.00'

extra = set(mileage) - used
if extra:
    raise RuntimeError(f'多源汇总存在源表未使用记录: {list(extra)[:5]}')

ws.freeze_panes = 'A2'
wb.save(OUTPUT)
wb.close()
print({'output': str(OUTPUT), 'rows': len(used), 'changed_rows': changed})