"""分析智能IT助手数据报表"""
import openpyxl

wb = openpyxl.load_workbook(
    r"C:\Users\simon\Downloads\智能IT助手数据报表_20260526T051947.xlsx",
    data_only=True
)

# 统计查询结果sheet的数据分布
hit_count = 0
miss_count = 0
pending_count = 0
processed_count = 0
none_count = 0
transfer_yes = 0
transfer_no = 0
intervene_yes = 0
intervene_no = 0
total_records = 0
time_range = []
miss_questions = []
hit_questions = []  # 命中的问题样例
transfer_questions = []  # 转人工的问题样例
operator_count = {}  # 操作用户统计

for sheet_name in wb.sheetnames:
    if not sheet_name.startswith("查询结果"):
        continue
    ws = wb[sheet_name]
    for row in ws.iter_rows(min_row=2, values_only=True):
        if row[0] is None:
            continue
        total_records += 1

        # 知识库命中
        hit_val = str(row[5]).strip() if row[5] else ""
        if hit_val == "命中":
            hit_count += 1
        elif hit_val == "未命中":
            miss_count += 1
            if row[2]:
                miss_questions.append(str(row[2])[:80])
        elif hit_val == "待处理":
            pending_count += 1
        elif hit_val == "已处理":
            processed_count += 1
        elif hit_val == "无":
            none_count += 1

        # 转人工
        transfer_val = str(row[6]).strip() if row[6] else ""
        if transfer_val == "是":
            transfer_yes += 1
            if row[2]:
                transfer_questions.append(str(row[2])[:80])
        elif transfer_val == "否":
            transfer_no += 1

        # 人工主动介入
        intervene_val = str(row[7]).strip() if row[7] else ""
        if intervene_val == "是":
            intervene_yes += 1
        elif intervene_val == "否":
            intervene_no += 1

        # 操作用户
        op_val = str(row[9]).strip() if len(row) > 9 and row[9] else ""
        if op_val and op_val != "无":
            operator_count[op_val] = operator_count.get(op_val, 0) + 1

        # 时间范围
        if row[4]:
            time_str = str(row[4])[:10]
            if time_str.startswith("2026"):
                time_range.append(time_str)

print("=== 数据总量 ===")
print(f"总记录数: {total_records}")
if time_range:
    print(f"时间范围: {min(time_range)} ~ {max(time_range)}")

print("\n=== 知识库命中分布 ===")
print(f"命中: {hit_count} ({hit_count/total_records*100:.1f}%)")
print(f"未命中: {miss_count} ({miss_count/total_records*100:.1f}%)")
print(f"待处理: {pending_count} ({pending_count/total_records*100:.1f}%)")
print(f"已处理: {processed_count} ({processed_count/total_records*100:.1f}%)")
print(f"无(人工导入): {none_count} ({none_count/total_records*100:.1f}%)")

print("\n=== 转人工分布 ===")
print(f"转人工-是: {transfer_yes} ({transfer_yes/total_records*100:.1f}%)")
print(f"转人工-否: {transfer_no} ({transfer_no/total_records*100:.1f}%)")

print("\n=== 人工主动介入分布 ===")
print(f"人工介入-是: {intervene_yes}")
print(f"人工介入-否: {intervene_no}")

print("\n=== 操作用户统计 ===")
for op, cnt in sorted(operator_count.items(), key=lambda x: -x[1]):
    print(f"  {op}: {cnt}")

print(f"\n=== 未命中问题样例 (前30条，共{len(miss_questions)}条) ===")
for i, q in enumerate(miss_questions[:30]):
    print(f"  {i+1}. {q}")

print(f"\n=== 转人工问题样例 (前20条，共{len(transfer_questions)}条) ===")
for i, q in enumerate(transfer_questions[:20]):
    print(f"  {i+1}. {q}")

# 计算自助解决率
# 自助解决 = 命中且未转人工
auto_resolve = hit_count - transfer_yes  # 近似值，因为有些命中但转人工
print(f"\n=== 自助解决率估算 ===")
print(f"AI命中且未转人工(估算): {hit_count - transfer_yes}")
print(f"自助解决率(估算): {(hit_count - transfer_yes)/total_records*100:.1f}%")
print(f"官方统计自助解决率: 70.2%")