#!/usr/bin/env python3
"""Fix May source: remove 4 vehicles from intervention, fix 粤A02239F GPS data."""
import os, shutil, openpyxl, warnings
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter
warnings.filterwarnings('ignore')
os.chdir('/Users/kkfluous/Downloads')

SRC = '租赁任务考核_2026年5月.xlsx'
BAK = '租赁任务考核_2026年5月_原版备份.xlsx'

wb = openpyxl.load_workbook(SRC)
ws = wb['业务考核视图']
h = [c.value for c in next(ws.iter_rows(min_row=1, max_row=1))]
print(f"Headers ({len(h)}):")
for i, hh in enumerate(h): print(f"  [{i}] {hh}")

# Column indices
COL_PLATE = 0
COL_ACTUAL = h.index('实际行驶里程(km)')  # GPS or仪表盘
COL_TBOX = h.index('原TBOX里程(km)')
COL_TEST = h.index('测试里程(km)')
COL_RATE = h.index('完成率(%)')
COL_PASS = h.index('是否达标')

YELLOW = PatternFill('solid', fgColor='FFFF00')
NO_FILL = PatternFill()  # default/empty

# ── Fix 1: Remove 4 vehicles from intervention ──
REMOVE = {'粤A03186F', '粤A01128F', '粤A08391F', '粤A01396F'}

removed_count = 0
for row in ws.iter_rows(min_row=2, max_row=ws.max_row):
    plate = str(row[COL_PLATE].value or '').strip()
    if plate not in REMOVE:
        continue

    tbox = row[COL_TBOX].value  # this is the original仪表盘里程
    if tbox is None or float(tbox or 0) <= 0:
        continue

    original_km = float(tbox)
    target_km = float(row[h.index('应考核里程(km)')].value or 0)

    # Restore: 实际行驶里程 = original仪表盘
    row[COL_ACTUAL].value = original_km
    row[COL_TBOX].value = None  # clear TBOX (non-intervention)
    row[COL_TEST].value = 0     # clear test

    # Recalculate
    rate = round(original_km / target_km * 100, 2) if target_km > 0 else 0
    new_pass = '达标' if original_km >= target_km and target_km > 0 else '未达标'
    row[COL_RATE].value = rate
    row[COL_PASS].value = new_pass

    # Remove yellow highlight
    for c in range(len(h)):
        row[c].fill = NO_FILL

    removed_count += 1
    print(f"  移除: {plate} 实际恢复={original_km} 达标={new_pass}")

print(f"移除干预: {removed_count} 辆")

# ── Fix 2: 粤A02239F GPS = 539.7 ──
plate = '粤A02239F'
for row in ws.iter_rows(min_row=2, max_row=ws.max_row):
    p = str(row[COL_PLATE].value or '').strip()
    if p != plate:
        continue

    tbox = float(row[COL_TBOX].value or 0)
    gps_new = 539.7
    target_km = float(row[h.index('应考核里程(km)')].value or 0)

    row[COL_ACTUAL].value = gps_new
    row[COL_TEST].value = round(tbox - gps_new, 2)
    rate = round(gps_new / target_km * 100, 2) if target_km > 0 else 0
    new_pass = '达标' if gps_new >= target_km and target_km > 0 else '未达标'
    row[COL_RATE].value = rate
    row[COL_PASS].value = new_pass

    print(f"\n修复: {plate} GPS={gps_new} TBOX={tbox} Test={round(tbox-gps_new,2)} 达标={new_pass}")
    break

# ── Save ──
wb.save(SRC)
print(f"\n✅ 已保存: {SRC}")

# ── Quick stats ──
wb2 = openpyxl.load_workbook(SRC, data_only=True)
ws2 = wb2['业务考核视图']
h2 = [c.value for c in next(ws2.iter_rows(min_row=1, max_row=1))]
total = 0; pass_count = 0
for row in ws2.iter_rows(min_row=2, values_only=True):
    if not row[0]: continue
    r = dict(zip(h2, row))
    total += 1
    if r.get('是否达标') == '达标': pass_count += 1
wb2.close()
print(f"总记录={total} 达标={pass_count}")