#!/usr/bin/env python
# -*- coding: utf-8 -*-
"""
模块名称：excel_reader.py
模块描述：功能测试报告 - Excel 数据读取模块，定义 TestCase/Bug 数据类及读取函数

移植自独立工具 FunctionalTestReportGeneration/src/excel_reader.py，
去除 CLI 依赖，import 改为相对导入。

作者：czj
创建日期：2026-08-12
最后修改：2026-08-12
"""

import re
from dataclasses import dataclass, field
from typing import List, Optional

import openpyxl

from .config import (
    CASE_DATA_START_ROW,
    BUG_DATA_START_ROW,
    CASE_COL_SERIAL_NUMBER,
    CASE_COL_MODULE,
    CASE_COL_CATEGORY,
    CASE_COL_CASE_NUMBER,
    CASE_COL_DESCRIPTION,
    CASE_COL_LEVEL,
    CASE_COL_FUNCTION_NUMBER,
    CASE_COL_NAME,
    CASE_COL_PRE_CONDITION,
    CASE_COL_STEPS,
    CASE_COL_JSON,
    CASE_COL_EXPECTED_RESULT,
    CASE_COL_TEST_RESULT,
    CASE_COL_TEST_FREQUENCY,
    CASE_COL_LOGS_SCREENSHOTS,
    CASE_COL_SCREENSHOT2,
    CASE_COL_REMARKS,
    BUG_COL_PROJECT_NAME,
    BUG_COL_BUG_TYPE,
    BUG_COL_BUG_LEVEL,
    BUG_COL_BUG_STATUS,
    BUG_COL_BUG_NAME,
    BUG_COL_REPORTER,
    BUG_COL_OWNER,
    BUG_COL_DEADLINE,
    BUG_COL_REPRODUCTION_STEPS,
    TEST_RESULT_PASS,
    TEST_RESULT_FAIL,
    TEST_RESULT_UNVERIFIED,
    TEST_RESULT_UNDEVELOPED,
    BUG_STATUS_ACTIVE,
    BUG_STATUS_RESOLVED,
    BUG_STATUS_CLOSED,
    BUG_LEVEL_1,
    BUG_LEVEL_2,
    BUG_LEVEL_3,
    BUG_LEVEL_4,
)


@dataclass
class TestCase:
    """测试用例数据类"""
    serial_number: int = 0
    module: str = ""
    category: str = ""
    case_number: str = ""
    description: str = ""
    level: str = ""
    function_number: str = ""
    name: str = ""
    pre_condition: str = ""
    steps: str = ""
    json_config: str = ""
    expected_result: str = ""
    test_result: str = ""
    test_frequency: str = ""
    logs_screenshots: str = ""
    screenshot2: str = ""
    remarks: str = ""


@dataclass
class Bug:
    """BUG数据类"""
    project_name: str = ""
    bug_type: str = ""
    bug_level: str = ""
    bug_status: str = ""
    bug_name: str = ""
    reporter: str = ""
    owner: str = ""
    deadline: str = ""
    reproduction_steps: str = ""
    related_case_numbers: List[str] = field(default_factory=list)


def _extract_case_number_from_steps(steps: str) -> List[str]:
    """
    从复现步骤中提取用例编号

    Args:
        steps: 复现步骤文本

    Returns:
        用例编号列表
    """
    if not steps:
        return []
    pattern = r'[A-Z]+-\d{3,}'
    return re.findall(pattern, steps)


def _get_cell_value(row, col_idx: int, default: str = "") -> str:
    """
    获取单元格值，处理 None 和空值

    Args:
        row: openpyxl 行对象
        col_idx: 列索引（从1开始）
        default: 默认值

    Returns:
        单元格的字符串值
    """
    cell = row[col_idx - 1]
    if cell is None or cell.value is None:
        return default
    return str(cell.value).strip()


def _get_int_cell_value(row, col_idx: int, default: int = 0) -> int:
    """
    获取整数单元格值

    Args:
        row: openpyxl 行对象
        col_idx: 列索引（从1开始）
        default: 默认值

    Returns:
        整数值
    """
    value = _get_cell_value(row, col_idx, str(default))
    try:
        return int(float(value))
    except (ValueError, TypeError):
        return default


def read_test_cases(file_path: str) -> List[TestCase]:
    """
    读取测试用例Excel文件

    Args:
        file_path: Excel文件路径

    Returns:
        测试用例列表
    """
    wb = openpyxl.load_workbook(file_path, data_only=True)
    ws = wb.active

    test_cases = []

    for row in ws.iter_rows(min_row=CASE_DATA_START_ROW):
        serial_number = _get_int_cell_value(row, CASE_COL_SERIAL_NUMBER)

        # 序列号为0时跳过该行
        if serial_number == 0:
            continue

        case = TestCase(
            serial_number=serial_number,
            module=_get_cell_value(row, CASE_COL_MODULE),
            category=_get_cell_value(row, CASE_COL_CATEGORY),
            case_number=_get_cell_value(row, CASE_COL_CASE_NUMBER),
            description=_get_cell_value(row, CASE_COL_DESCRIPTION),
            level=_get_cell_value(row, CASE_COL_LEVEL),
            function_number=_get_cell_value(row, CASE_COL_FUNCTION_NUMBER),
            name=_get_cell_value(row, CASE_COL_NAME),
            pre_condition=_get_cell_value(row, CASE_COL_PRE_CONDITION),
            steps=_get_cell_value(row, CASE_COL_STEPS),
            json_config=_get_cell_value(row, CASE_COL_JSON),
            expected_result=_get_cell_value(row, CASE_COL_EXPECTED_RESULT),
            test_result=normalize_test_result(_get_cell_value(row, CASE_COL_TEST_RESULT)),
            test_frequency=_get_cell_value(row, CASE_COL_TEST_FREQUENCY),
            logs_screenshots=_get_cell_value(row, CASE_COL_LOGS_SCREENSHOTS),
            screenshot2=_get_cell_value(row, CASE_COL_SCREENSHOT2),
            remarks=_get_cell_value(row, CASE_COL_REMARKS),
        )
        test_cases.append(case)

    wb.close()
    return test_cases


def read_bug_list(file_path: str) -> List[Bug]:
    """
    读取BUG列表Excel文件

    Args:
        file_path: Excel文件路径

    Returns:
        BUG列表
    """
    wb = openpyxl.load_workbook(file_path, data_only=True)
    ws = wb.active

    bugs = []

    for row in ws.iter_rows(min_row=BUG_DATA_START_ROW):
        project_name = _get_cell_value(row, BUG_COL_PROJECT_NAME)

        # 项目名称为空时跳过该行
        if not project_name:
            continue

        reproduction_steps = _get_cell_value(row, BUG_COL_REPRODUCTION_STEPS)
        related_case_numbers = _extract_case_number_from_steps(reproduction_steps)

        bug = Bug(
            project_name=project_name,
            bug_type=_get_cell_value(row, BUG_COL_BUG_TYPE),
            bug_level=normalize_bug_level(_get_cell_value(row, BUG_COL_BUG_LEVEL)),
            bug_status=normalize_bug_status(_get_cell_value(row, BUG_COL_BUG_STATUS)),
            bug_name=_get_cell_value(row, BUG_COL_BUG_NAME),
            reporter=_get_cell_value(row, BUG_COL_REPORTER),
            owner=_get_cell_value(row, BUG_COL_OWNER),
            deadline=_get_cell_value(row, BUG_COL_DEADLINE),
            reproduction_steps=reproduction_steps,
            related_case_numbers=related_case_numbers,
        )
        bugs.append(bug)

    wb.close()
    return bugs


def validate_test_case_data(cases: List[TestCase]) -> List[str]:
    """
    验证测试用例数据完整性

    Args:
        cases: 测试用例列表

    Returns:
        错误信息列表
    """
    errors = []
    for i, case in enumerate(cases):
        if not case.case_number:
            errors.append(f"第{i+1}行: 用例编号为空")
        if not case.module:
            errors.append(f"第{i+1}行 ({case.case_number}): 功能模块为空")
        if not case.test_result:
            errors.append(f"第{i+1}行 ({case.case_number}): 测试结果为空")
    return errors


def validate_bug_data(bugs: List[Bug]) -> List[str]:
    """
    验证BUG数据完整性

    Args:
        bugs: BUG列表

    Returns:
        错误信息列表
    """
    errors = []
    for i, bug in enumerate(bugs):
        if not bug.bug_name:
            errors.append(f"第{i+1}行: BUG名称为空")
        if not bug.bug_level:
            errors.append(f"第{i+1}行 ({bug.bug_name}): BUG等级为空")
        if not bug.bug_status:
            errors.append(f"第{i+1}行 ({bug.bug_name}): BUG状态为空")
    return errors


def normalize_test_result(result: str) -> str:
    """
    标准化测试结果

    Args:
        result: 原始测试结果字符串

    Returns:
        标准化后的测试结果
    """
    if not result:
        return ""

    result = result.strip()

    # 通过结果映射
    pass_aliases = {"通过", "Pass", "PASS", "pass", "成功", "P", "√", "✓", "是", "yes", "Yes", "YES"}
    if result in pass_aliases:
        return TEST_RESULT_PASS

    # 失败结果映射
    fail_aliases = {"失败", "Fail", "FAIL", "fail", "F", "×", "✗", "否", "no", "No", "NO"}
    if result in fail_aliases:
        return TEST_RESULT_FAIL

    # 未验证结果映射
    unverified_aliases = {"未验证", "Unverified", "UNVERIFIED", "N/A", "NA", "na", "", "-", "未执行", "未测"}
    if result in unverified_aliases:
        return TEST_RESULT_UNVERIFIED

    # 未开发结果映射
    undeveloped_aliases = {"未开发", "Undeveloped", "UNDEVELOPED", "未实现", "未完成"}
    if result in undeveloped_aliases:
        return TEST_RESULT_UNDEVELOPED

    # 默认返回原值
    return result


def normalize_bug_status(status: str) -> str:
    """
    标准化BUG状态

    Args:
        status: 原始BUG状态字符串

    Returns:
        标准化后的BUG状态
    """
    if not status:
        return ""

    status = status.strip()

    active_aliases = {"激活", "Active", "New", "Open", "Reopen", "活跃", "打开"}
    if status in active_aliases:
        return BUG_STATUS_ACTIVE

    resolved_aliases = {"已解决", "Resolved", "Fixed", "已修复", "已完成"}
    if status in resolved_aliases:
        return BUG_STATUS_RESOLVED

    closed_aliases = {"已关闭", "Closed", "Done", "完成", "关闭"}
    if status in closed_aliases:
        return BUG_STATUS_CLOSED

    return status


def normalize_bug_level(level: str) -> str:
    """
    标准化BUG等级

    Args:
        level: 原始BUG等级字符串

    Returns:
        标准化后的BUG等级
    """
    if not level:
        return ""

    level = level.strip()

    # 1级（致命）
    level_1_aliases = {"1级", "致命", "1", "Critical", "P0", "Urgent", "一级", "1-致命"}
    if level in level_1_aliases:
        return BUG_LEVEL_1

    # 2级（严重）
    level_2_aliases = {"2级", "严重", "2", "Major", "P1", "High", "二级", "2-严重"}
    if level in level_2_aliases:
        return BUG_LEVEL_2

    # 3级（一般）
    level_3_aliases = {"3级", "一般", "3", "Normal", "P2", "Medium", "三级", "3-一般", "普通"}
    if level in level_3_aliases:
        return BUG_LEVEL_3

    # 4级（轻微）
    level_4_aliases = {"4级", "轻微", "4", "Low", "P3", "Minor", "四级", "4-轻微", "建议"}
    if level in level_4_aliases:
        return BUG_LEVEL_4

    return level