import os
import json
import openpyxl
import csv
# Copy-Item -Path "d:\Github\docs\tool\config\*" -Destination "D:\Github\pet_home_server\src\server\gamedata\config\" -Force -Recurse
current_dir = os.getcwd()
# 读取配置文件
cfg_path = os.path.join(current_dir, 'tool/cfg/cfg_xlsx.json')
with open(cfg_path, 'r', encoding='utf-8') as f:
    cfg = json.load(f)

def read_table(file_path, sheet_name=None):
    if file_path.lower().endswith('.xlsx'):
        workbook = openpyxl.load_workbook(file_path, data_only=True)
        sheet = workbook[sheet_name] if sheet_name else workbook.active
        fieldnames = [cell.value for cell in sheet[1]]
        rows = list(sheet.iter_rows(values_only=True))[2:]
    elif file_path.lower().endswith('.csv'):
        # 尝试多种编码格式
        encodings = ['utf-8', 'gbk', 'gb2312', 'cp936', 'utf-8-sig']
        data = None
        
        for encoding in encodings:
            try:
                with open(file_path, 'r', encoding=encoding) as csvfile:
                    reader = csv.reader(csvfile)
                    data = list(reader)
                    break
            except UnicodeDecodeError:
                continue
        
        if data is None:
            raise ValueError(f"Unable to decode CSV file {file_path} with any of the attempted encodings: {encodings}")
        
        fieldnames = data[0]
        rows = data[2:]
    else:
        raise ValueError("Unsupported file type")
    return fieldnames, rows

file_list = cfg['file_list']
target_dir = cfg['target_dir']
source_dir = cfg['source_dir']
fields_to_remove = cfg.get('fields_to_remove', [])

# 确保目标目录存在
os.makedirs(target_dir, exist_ok=True)

# 遍历文件列表并转换文件
for file_cfg in file_list:
    source_file_path = os.path.join(current_dir, source_dir, file_cfg["in_file"])
    target_file_path = os.path.join(current_dir, target_dir, file_cfg["out_file"])
    sheet_name = file_cfg.get("sheet_name")

    # 使用 read_table 函数读取文件
    fieldnames, rows = read_table(source_file_path, sheet_name)
    
    # 以第一列的值作为索引，并移除第一列
    indexed_data = {}
    for row in rows:
        row_dict = {fieldnames[i]: row[i] for i in range(len(fieldnames))}
        index = row_dict.pop(file_cfg["key"])  # 获取第一列的值作为索引并移除第一列
        
        # 仅保留需要的字段
        row_dict = {field: row_dict[field] for field in file_cfg['fields'] if field in row_dict}
        for key, value in row_dict.items():
            try:
                row_dict[key] = json.loads(value)
            except (json.JSONDecodeError, TypeError):
                pass  # 如果解析失败，则保持原值
        indexed_data[index] = row_dict
    
    # 将JSON写入目标文件
    with open(target_file_path, 'w', encoding='utf-8') as json_file:
        json.dump(indexed_data, json_file, ensure_ascii=False, indent=4)
    
    print(f"Converted: {source_file_path} to {target_file_path}")