from ast import parse, walk, Call, Constant, keyword
from collections import defaultdict
from copy import copy
from datetime import datetime, date
from pathlib import Path
from zipfile import ZipFile
from xml.etree import ElementTree as ET
import re
import shutil

import openpyxl
import pymysql
from openpyxl.styles import Alignment, Font, PatternFill

BASE = Path('/Users/kkfluous/Downloads')
OUTPUT = BASE / '7月核算结果/7月考核车辆多源里程汇总_含车型.xlsx'
MAIN_OUTPUT = BASE / '7月核算结果/7月考核车辆多源里程汇总.xlsx'
BACKUP = BASE / '7月核算结果/7月考核车辆多源里程汇总_新日规则前.xlsx'
ASSESSMENT = BASE / '7月核算结果/租赁任务考核_2026年7月.xlsx'
TBOX_FILE = BASE / '车辆里程查询_20260701-20260731_1024辆.xlsx'
PLATFORM_DIR = BASE / '广州新能源平台_纯氢纯电里程_2026年7月_按日'
DETAILS = [BASE / '里程统计[天][2026-07-01至2026-07-31].xlsx', BASE / '里程统计[天][2026-07-01至2026-07-31] (1).xlsx']
NS = '{http://schemas.openxmlformats.org/spreadsheetml/2006/main}'

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

def db_config():
    tree = parse((BASE / 'replace_gps_jun.py').read_text())
    for node in walk(tree):
        if isinstance(node, Call) and getattr(node.func, 'attr', None) == 'connect':
            values = {x.arg: x.value.value for x in node.keywords if isinstance(x.value, Constant)}
            if {'host', 'user', 'password', 'database'} <= values.keys(): return values
    raise RuntimeError('未找到既有GPS数据库配置')

def read_detail_gps():
    out = defaultdict(dict)
    for path in DETAILS:
        with ZipFile(path) as zf:
            root = ET.fromstring(zf.read('xl/worksheets/sheet1.xml'))
            for row in root.findall(f'.//{NS}row'):
                if int(row.get('r')) < 4: continue
                values = {}
                for cell in row.findall(f'{NS}c'):
                    col = re.match(r'[A-Z]+', cell.get('r')).group()
                    if cell.get('t') == 'inlineStr':
                        values[col] = ''.join(t.text or '' for t in cell.findall(f'.//{NS}t'))
                    else:
                        node = cell.find(f'{NS}v'); values[col] = node.text if node is not None else None
                p = norm(values.get('H')); ds = str(values.get('O') or '').strip()
                if p and p != '合计' and re.fullmatch(r'2026-07-\d{2}', ds):
                    out[p][datetime.strptime(ds, '%Y-%m-%d').date()] = float(values.get('P') or 0)
    return out

# 测试候选车辆以7月最终考核源表中黄色干预行为准。
awb = openpyxl.load_workbook(ASSESSMENT, data_only=True)
aws = awb['业务考核视图']
candidates = set()
for row in range(2, aws.max_row + 1):
    fills = {aws.cell(row, c).fill.fgColor.rgb for c in range(1, aws.max_column + 1) if aws.cell(row, c).fill.fill_type == 'solid'}
    if any(str(x).upper().endswith('FFFF00') for x in fills if x):
        candidates.add(norm(aws.cell(row, 1).value))
awb.close()

# 每日TBOX。文件中的空白日按0处理，不会触发测试条件。
twb = openpyxl.load_workbook(TBOX_FILE, read_only=True, data_only=True)
tws = twb['里程查询']
header = next(tws.iter_rows(min_row=6, max_row=6, values_only=True))
day_cols = {i: value.date() for i, value in enumerate(header) if isinstance(value, datetime)}
tbox = defaultdict(dict)
for row in tws.iter_rows(min_row=7, values_only=True):
    p = norm(row[0])
    if p:
        for i, day in day_cols.items(): tbox[p][day] = float(row[i] or 0)
twb.close()

# 每日GPS：两份日明细优先，数据库补充缺失日期。
gps = read_detail_gps()
cfg = db_config(); cfg.update(charset='utf8mb4', connect_timeout=8, read_timeout=30, write_timeout=30)
conn = pymysql.connect(**cfg)
try:
    with conn.cursor() as cur:
        placeholders = ','.join(['%s'] * len(candidates))
        cur.execute(
            f'SELECT plate_number,dates,total_mileage FROM ln_vehicle_g7_mileage WHERE dates >= %s AND dates <= %s AND plate_number IN ({placeholders})',
            [date(2026, 7, 1), date(2026, 7, 31), *sorted(candidates)],
        )
        db_rows = cur.fetchall()
finally: conn.close()
for p, day, raw in db_rows:
    if isinstance(day, datetime): day = day.date()
    gps[norm(p)].setdefault(day, float(raw or 0) / 100000.0)

# 广州平台每日纯氢里程。
hydrogen = defaultdict(dict)
for path in sorted(PLATFORM_DIR.glob('2026-07-??_纯氢纯电里程.xlsx')):
    wb = openpyxl.load_workbook(path, read_only=True, data_only=True)
    ws = wb.active
    for row in ws.iter_rows(min_row=2, values_only=True):
        p = norm(row[5]); match = re.search(r'2026-07-\d{2}', str(row[6] or ''))
        if p and match:
            day = datetime.strptime(match.group(), '%Y-%m-%d').date()
            hydrogen[p][day] = hydrogen[p].get(day, 0) + float(row[8] or 0)
    wb.close()

if not BACKUP.exists(): shutil.copy2(OUTPUT, BACKUP)
wb = openpyxl.load_workbook(OUTPUT)
ws = wb.active
header_row = next(r for r in range(1, 8) if ws.cell(r, 2).value == '车牌')
headers = [ws.cell(header_row, c).value for c in range(1, ws.max_column + 1)]
if '考核里程(km)' not in headers:
    for merged in list(ws.merged_cells.ranges):
        if merged.min_row == 1 and merged.max_row == 1:
            ws.unmerge_cells(str(merged))
    total_col = headers.index('广州平台总里程(km)') + 1
    ws.insert_cols(total_col + 1, 1)
    assessment_col = total_col + 1
else:
    assessment_col = headers.index('考核里程(km)') + 1
ws.cell(header_row, assessment_col).value = '考核里程(km)'
ws.cell(header_row, assessment_col)._style = copy(ws.cell(header_row, assessment_col - 1)._style)
ws.column_dimensions[openpyxl.utils.get_column_letter(assessment_col)].width = 18

headers = [ws.cell(header_row, c).value for c in range(1, ws.max_column + 1)]
col = {v: i + 1 for i, v in enumerate(headers)}
yes_records = 0; test_days_total = 0; excluded_hydrogen_total = 0.0; no_hydrogen = 0
fallback_tbox = 0; fallback_gps = 0; fallback_missing = 0
white = PatternFill('solid', fgColor='FFFFFF')
platform_fill = PatternFill('solid', fgColor='DDEBF7')
assessment_fill = PatternFill('solid', fgColor='FCE4D6')
tbox_fill = PatternFill('solid', fgColor='E2F0D9')
gps_fill = PatternFill('solid', fgColor='E4DFEC')
yellow = PatternFill('solid', fgColor='FFF2CC')
grey = PatternFill('solid', fgColor='F2F2F2')

for row in range(header_row + 1, ws.max_row + 1):
    p = norm(ws.cell(row, col['车牌']).value)
    start = ws.cell(row, col['考核开始日期']).value.date()
    end = ws.cell(row, col['考核结束日期']).value.date()
    days = [start.fromordinal(start.toordinal() + n) for n in range((end - start).days + 1)]
    test_days = []
    if p in candidates:
        for day in days:
            if day in gps.get(p, {}) and tbox[p].get(day, 0) - gps[p][day] > 50:
                test_days.append(day)
    has_test = bool(test_days)
    ws.cell(row, col['是否存在测试']).value = '是' if has_test else '否'
    if has_test: yes_records += 1
    test_days_total += len(test_days)
    daily_h = hydrogen.get(p, {})
    present = [day for day in days if day in daily_h]
    if not present:
        no_hydrogen += 1
        fallback_name = 'GPS里程(km)' if p in candidates else 'TBOX仪表里程(km)'
        fallback_value = ws.cell(row, col[fallback_name]).value
        if isinstance(fallback_value, (int, float)):
            ws.cell(row, assessment_col).value = round(float(fallback_value), 2)
            ws.cell(row, assessment_col).number_format = '#,##0.0'
            if p in candidates:
                fallback_gps += 1
            else:
                fallback_tbox += 1
        else:
            ws.cell(row, assessment_col).value = '无来源'
            fallback_missing += 1
    else:
        excluded_hydrogen_total += sum(daily_h.get(day, 0) for day in test_days)
        ws.cell(row, assessment_col).value = round(sum(daily_h.get(day, 0) for day in days if day not in test_days), 2)
        ws.cell(row, assessment_col).number_format = '#,##0.0'

    # 重建行配色：新规则判定存在测试时整行黄色，否则恢复各数据组颜色。
    if has_test:
        for c in range(1, ws.max_column + 1): ws.cell(row, c).fill = copy(yellow)
    else:
        for c in range(1, ws.max_column + 1): ws.cell(row, c).fill = copy(white)
        for name in ['纯氢里程(km)', '纯电里程(km)', '广州平台总里程(km)']:
            ws.cell(row, col[name]).fill = copy(platform_fill)
        ws.cell(row, assessment_col).fill = copy(assessment_fill)
        ws.cell(row, col['TBOX仪表里程(km)']).fill = copy(tbox_fill)
        ws.cell(row, col['GPS里程(km)']).fill = copy(gps_fill)
        for name in ['纯氢里程(km)', '纯电里程(km)', '广州平台总里程(km)', '考核里程(km)', 'TBOX仪表里程(km)', 'GPS里程(km)']:
            cell = ws.cell(row, col[name])
            if cell.value == '无来源':
                cell.fill = copy(grey); cell.font = Font(color='7F7F7F', italic=True)

for merged in list(ws.merged_cells.ranges):
    if merged.min_row == 1 and merged.max_row == 1:
        ws.unmerge_cells(str(merged))
ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=ws.max_column)
ws['A1'] = (
    '1. 考核里程按纯氢里程计算：普通车辆取考核区间纯氢里程；测试候选车辆逐日比较，'
    '当日TBOX里程－当日GPS里程＞50km时判为测试日，并剔除该日纯氢里程；'
    '无纯氢里程的4.5T普货车辆，测试候选车辆取GPS里程，其他车辆取TBOX仪表里程\n'
    '2. 每日TBOX来源：车辆里程查询_20260701-20260731_1024辆.xlsx；空白日按0处理。每日GPS优先两份7月日明细，数据库补充\n'
    '3. 4.5T普货的TBOX或GPS备选来源也缺失时，考核里程标记“无来源”\n'
    '4. 纯电里程及广州平台总里程仅作对照，不参与考核里程'
)
ws['A1'].alignment = Alignment(horizontal='left', vertical='center', wrap_text=True)
ws.auto_filter.ref = f'A{header_row}:{openpyxl.utils.get_column_letter(ws.max_column)}{ws.max_row}'
ws.row_dimensions[1].height = 58
wb.save(OUTPUT)
shutil.copy2(OUTPUT, MAIN_OUTPUT)

print({'candidate_plates': len(candidates), 'test_records_new_rule': yes_records, 'test_days': test_days_total, 'excluded_hydrogen_km': round(excluded_hydrogen_total, 2), 'pure_hydrogen_missing': no_hydrogen, 'fallback_tbox_records': fallback_tbox, 'fallback_gps_records': fallback_gps, 'assessment_mileage_still_missing': fallback_missing, 'columns': ws.max_column})