import glob
import os

import yaml
import pymysql
from py_jftech import get_project_path
import pandas as pd
import json


# 读取YAML配置文件
def read_yaml_config(file_path):
    with open(file_path, 'r') as file:
        return yaml.safe_load(file)


# 连接到MySQL数据库
def connect_to_mysql(config):
    try:
        # 使用MySQL配置连接数据库
        connection = pymysql.connect(
            host=config['host'],
            user=config['user'],
            password=config['password'],
            database=config['database']
        )
        return connection
    except Exception as e:
        print(f"Error connecting to MySQL: {e}")
        return None


# 执行数据库操作
def execute_db_operations(connection, sql):
    try:
        with connection.cursor() as cursor:
            # 执行数据库操作
            cursor.execute(sql)
            result = cursor.fetchall()
            print(result)
    finally:
        connection.close()


def read_excel_to_json(file_path_or_obj):
    """
    根据路径或文件对象读取Excel文件中的note_review工作表，并返回JSON格式的内容。

    :param file_path_or_obj: Excel文件的路径或文件对象
    :return: 包含note_review工作表内容的JSON字符串
    """
    try:
        # 读取Excel文件中的note_review工作表
        df = pd.read_excel(file_path_or_obj, sheet_name='note_review')

        # 将DataFrame转换为JSON格式
        json_data = df.to_json(orient='records', force_ascii=False)

        return json_data
    except Exception as e:
        print(f"读取Excel文件时出错: {e}")
        return None


# 主函数
def main():
    project_path = get_project_path()
    # 1.遍历project_path下面所有yml配置，保存到list（yml_paths）
    yml_paths = glob.glob(os.path.join(project_path, '**', '*.yml'), recursive=True)
    excel_path = os.path.join(project_path,'SV投資組合配置說明_1224v1.xlsx')
    datas = json.loads(read_excel_to_json(excel_path))
    for data in datas:
        rebalance_date = data.get('rebalance_date')
        guid = data.get('portfolio_guid')
        formal_review = data.get('formal_review')
        # 遍历yml_paths, 找到对应guid的yml文件，读取数据库相关配置，获取数据库连接
        for path in yml_paths:
            with open(path, 'r', encoding='utf-8') as f:
                config = yaml.load(f, Loader=yaml.FullLoader)
                if config.get('web'):
                    if config['web']['guid'] == guid:
                        print(f"找到配置文件: {path}")
                        break
        connection = connect_to_mysql(config)
        # todo 2.执行数据库操作，将formal_review更新到数据库中
        sql = f"UPDATE portfolio SET formal_review = '{formal_review}' WHERE guid = '{guid}'"
        execute_db_operations(connection, sql)


if __name__ == "__main__":
    main()