#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
为健康师工资Excel文件添加ID列
从数据库查询ID，根据员工姓名和门店名称匹配
"""

import openpyxl
import json
import sys
import os

# 数据库查询结果（从之前的查询中获取）
# 这里使用MCP MySQL工具查询的结果
db_records = [
    {"F_Id": "742726471423886597", "F_StoreName": "绿纤华润店", "F_EmployeeName": "王瑞琳"},
    {"F_Id": "742726471453246725", "F_StoreName": "绿纤华润店", "F_EmployeeName": "雷朝霞"},
    {"F_Id": "742726471453246726", "F_StoreName": "绿纤华润店", "F_EmployeeName": "赵玉晓"},
    {"F_Id": "742726471453246727", "F_StoreName": "绿纤川音店", "F_EmployeeName": "贺丽"},
    {"F_Id": "742726471453246728", "F_StoreName": "绿纤红光店", "F_EmployeeName": "包竹梅"},
    # ... 更多记录需要从数据库查询
]

def create_id_mapping_from_db():
    """从数据库创建ID映射字典"""
    # 这里应该从数据库查询，但为了测试，先使用部分数据
    # 实际应该通过MCP MySQL工具查询所有记录
    mapping = {}
    for record in db_records:
        key = (record["F_StoreName"], record["F_EmployeeName"])
        mapping[key] = record["F_Id"]
    return mapping

def add_id_column(excel_path, output_path=None):
    """为Excel文件添加ID列"""
    if output_path is None:
        output_path = excel_path.replace('.xlsx', '_带ID.xlsx')
    
    wb = openpyxl.load_workbook(excel_path)
    ws = wb['健康师工资']
    
    # 创建ID映射（实际应该从数据库查询）
    # 这里先读取Excel数据，准备匹配
    id_mapping = {}
    
    # 读取所有数据行（跳过标题行）
    data_rows = []
    for row_idx, row in enumerate(ws.iter_rows(min_row=2, values_only=True), start=2):
        if len(row) >= 2:
            store_name = str(row[0]).strip() if row[0] else ""
            employee_name = str(row[1]).strip() if len(row) > 1 and row[1] else ""
            data_rows.append((row_idx, store_name, employee_name, row))
    
    print(f"需要匹配的数据行数: {len(data_rows)}")
    
    # 这里需要从数据库查询所有记录的ID映射
    # 由于数据量大，需要分批查询或一次性查询
    print("提示：需要从数据库查询所有记录的ID映射")
    print("可以使用MCP MySQL工具查询: SELECT F_Id, F_StoreName, F_EmployeeName FROM lq_salary_statistics WHERE F_StatisticsMonth = '202509'")
    
    return output_path

if __name__ == "__main__":
    excel_path = "ExportFiles/工资导入/健康师工资_20260109211750.xlsx"
    output_path = add_id_column(excel_path)
    print(f"输出文件: {output_path}")