import os
from qcloud_cos import CosConfig, CosS3Client
from qcloud_cos.cos_exception import CosServiceError
import pandas as pd
import math
from decimal import Decimal, ROUND_HALF_UP
from var import SECRET_ID, SECRET_KEY, REGION, BUCKET, MYSQL_HOST, MYSQL_PORT, MYSQL_USER, MYSQL_PASSWORD, MYSQL_DB, OCR_MODEL
import json
import logging
import pymysql


# 配置日志系统
def setup_logging():
    """配置日志系统，同时输出到文件和控制台"""
    # 创建logger
    logger = logging.getLogger()
    logger.setLevel(logging.DEBUG)

    # 避免重复添加handler
    if logger.handlers:
        for handler in logger.handlers[:]:
            logger.removeHandler(handler)

    # 设置日志格式
    formatter = logging.Formatter(
        '%(asctime)s - %(name)s - %(levelname)s - [%(filename)s:%(lineno)d] - %(message)s',
        datefmt='%Y-%m-%d %H:%M:%S'
    )

    # 控制台处理器
    console_handler = logging.StreamHandler()
    console_handler.setLevel(logging.INFO)
    console_handler.setFormatter(formatter)

    logger.addHandler(console_handler)

    return logger


# 初始化日志
logger = setup_logging()

db_connection = pymysql.connect(
    host=MYSQL_HOST,
    port=MYSQL_PORT,
    user=MYSQL_USER,
    password=MYSQL_PASSWORD,
    database=MYSQL_DB,
    charset='utf8mb4'
)

logger.info("MySQL数据库连接已建立，主机: %s, 数据库: %s", MYSQL_HOST, MYSQL_DB)


def insert_into_mysql(file_id, filename, cos_url_full, ocr_json_result, file_size,
                      task_status, ocr_cost_time, input_tokens, output_tokens):
    """
    将OCR任务数据插入MySQL数据库的独立函数

    参数:
        file_id: 文件ID
        filename: 文件名
        cos_url_full: 文件在COS中的完整URL
        ocr_json_result: OCR识别结果(JSON格式)
        file_size: 文件大小
        task_status: 任务状态
        ocr_cost_time: OCR处理耗时
        input_tokens: 输入token数
        output_tokens: 输出token数
    """
    logger.info("开始将数据插入MySQL数据库: %s", filename)

    try:
        # 检查数据库连接
        db_connection.ping(reconnect=True)
        with db_connection.cursor() as cursor:
            sql = """
            INSERT INTO ocr_task 
            (file_id, file_name, file_storage_url, ocr_json_result, file_size, task_status, ocr_engine, ocr_cost_time, input_tokens, output_tokens) 
            VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
            """
            params = (
                file_id,
                filename,
                cos_url_full,
                ocr_json_result,
                file_size,
                task_status,
                OCR_MODEL,  # 使用参数传入的ocr_model
                ocr_cost_time,
                input_tokens,
                output_tokens
            )

            # 打印实际执行的SQL语句（调试用）
            actual_sql = cursor.mogrify(sql, params)
            logger.debug("执行的SQL语句: %s", actual_sql)

            cursor.execute(sql, params)
        db_connection.commit()
        logger.info("数据成功插入MySQL数据库: %s", filename)
        return True  # 插入成功返回True

    except Exception as e:
        logger.error("MySQL插入错误: %s", str(e), exc_info=True)
        import traceback
        traceback.print_exc()
        return False  # 插入失败返回False

    finally:
        # 注意：关闭数据库连接可能会影响其他操作
        # 如果这是唯一的数据库操作，可以关闭；否则建议在函数外部管理连接
        db_connection.close()
        logger.debug("数据库连接已关闭")


def upload_image_to_cos(local_file_path, cos_file_path):
    config = CosConfig(Region=REGION, SecretId=SECRET_ID, SecretKey=SECRET_KEY)
    client = CosS3Client(config)

    try:
        # 上传文件
        response = client.upload_file(
            Bucket=BUCKET,
            LocalFilePath=local_file_path,
            Key=cos_file_path.replace('\\', '/'),  # 确保使用正斜杠
            EnableMD5=False
        )
        # 判断返回结果
        if response['ETag']:
            return True
        else:
            return False
    except CosServiceError as e:
        return False
    except Exception as e:
        return False


def ruiwen_upload_image_directory(input_dir):
    # input_dir下面全都是jpg文件，遍历这些文件，并重命名为01.jpg, 02.jpg, 03.jpg...
    success_count = 0
    fail_count = 0
    files = [f for f in os.listdir(input_dir) if f.lower().endswith(('.jpg', '.jpeg', '.png'))]
    files = sorted(files)  # 确保文件按名称排序
    for idx, file in enumerate(files):
        local_path = os.path.join(input_dir, file)
        # 构建 COS 路径，保持 images/wind/01.jpg 结构
        cos_path = f"images/ruiwen/{file}"
        print(f"正在上传: {local_path} -> {cos_path}")
        success = upload_image_to_cos(local_path, cos_path)
        if success:
            success_count += 1
        else:
            fail_count += 1


def retain_three_significant_digits(x):
    """
    将数值保留三位有效数字，并四舍五入。
    参数:
        x (int/float): 输入数值
    返回:
        int 或 float: 保留三位有效数字后的结果。保证小数点后最多三位。
    """
    if pd.isna(x) or x == 0:
        return x
    # 使用Decimal模块进行高精度四舍五入[5,6](@ref)
    abs_x = abs(x)
    exp = math.floor(math.log10(abs_x))
    scale = 10 ** exp
    
    # 将x缩放至1-10之间，四舍五入到两位小数
    rounded = round(x / scale, 2) * scale
    
    # 处理小数点后位数限制
    if isinstance(rounded, float) and abs(rounded) < 1:
        # 对于小于1的数，使用Decimal确保精确的四舍五入[6](@ref)
        decimal_value = Decimal(str(rounded))  # 通过字符串避免浮点精度问题
        rounded = float(decimal_value.quantize(Decimal('0.001'), rounding=ROUND_HALF_UP))
    
    # 如果结果是整数，返回int类型；否则返回float
    return int(rounded) if rounded == int(rounded) else rounded


def get_days_in_month(month, year=2023):
    """获取指定月份的天数"""
    if month in [1, 3, 5, 7, 8, 10, 12]:
        return 31
    elif month in [4, 6, 9, 11]:
        return 30
    elif month == 2:
        # 判断是否为闰年
        if (year % 4 == 0 and year % 100 != 0) or (year % 400 == 0):
            return 29
        else:
            return 28


def get_image_memory_size(img):
    """
    计算PIL Image对象在内存中占用的字节大小。
    
    参数:
        img: PIL.Image对象
        
    返回:
        int: 图像占用的字节数
    """
    width, height = img.size
    
    # 根据图像模式确定每像素字节数
    mode_to_bpp = {
        '1': 1/8,   # 1位像素，每个像素占1/8字节
        'L': 1,     # 8位像素，灰度，1字节
        'P': 1,     # 8位像素，调色板模式
        'RGB': 3,   # 24位真彩色，3字节
        'RGBA': 4,  # 32位真彩色带透明度，4字节
        'CMYK': 4,  # 4字节
        'LAB': 3,   # 3字节
        'HSV': 3,   # 3字节
        'I': 4,     # 32位有符号整数像素
        'F': 4,     # 32位浮点像素
    }
    
    bytes_per_pixel = mode_to_bpp.get(img.mode, 1)  # 默认为1字节/像素
    memory_size = width * height * bytes_per_pixel
    
    return int(memory_size)


def highlight_high_scores(row):
    """
    将误差行中绝对值大于0.001的单元格背景色设置为浅黄色
    """
    # 检查当前行是否是"误差"行
    if row['日期'] == "误差":  # row.name获取行索引
        return ['background-color: #FFFACD' if (not isinstance(x, str)) and (abs(x) > 0.001) else '' for x in row]
    else:
        return [''] * len(row)  # 其他行返回空样式


def json_format(json_str):
    if isinstance(json_str, dict):
        json_str = json.dumps(json_str, ensure_ascii=False)

    json_str = json_str.strip().strip('`').replace(r'(', '').replace(r')', '')  # 先去除首尾空白，再去除反引号
    json_res = json.loads(json_str)
    return json_res


def json_to_excel(json_res, excel_path):
    logger.info("开始将JSON数据转换为Excel文件: %s", excel_path)
    data_detail = dict()
    avg_data = []

    max_days = 31
    for j, (month_name, data) in enumerate(json_res.items()):
        right_days = get_days_in_month(j + 1)
        month_values = data['data']
        month_values = month_values[:right_days]

        if len(month_values) < max_days:
            # 补充None到31天
            padded_values = month_values + [None] * (max_days - len(month_values))
            data_detail[month_name] = padded_values
        else:
            data_detail[month_name] = month_values
        avg_data.append(data['avg'])
        logger.debug("处理月份: %s, 天数: %d, 数据长度: %d", month_name, right_days, len(month_values))

    df = pd.DataFrame(data_detail)
    df.index = range(1, 32)
    df.loc['计算平均值'] = df.mean().apply(retain_three_significant_digits)
    df.loc['原平均值'] = avg_data
    df.loc['误差'] = (df.loc['计算平均值'] - df.loc['原平均值']).round(3)
    df = df.reset_index(names='日期')

    styled_df = df.style.apply(highlight_high_scores, axis=1)
    styled_df.to_excel(excel_path, engine='openpyxl', index=False)
    logger.info("Excel文件生成成功: %s, 数据形状: %s", excel_path, df.shape)
    return data_detail


if __name__ == "__main__":
    image_path = r"D:\project\hydrological_data_analyse/input/test"
    ruiwen_upload_image_directory(image_path)