#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""重算对比指标（不重新调用模型）

修正两处口径：
1. DeepSeek 常把字段输出成数组（["林总","洪总"]），原脚本直接 str(list) 比字符串 → 一致率被压低；
   这里统一把数组按「；」拼接后再比。
2. 「四项全有」过于苛刻（很多记录本来就没有卡点/下一步），改为**逐字段非空率**。
"""
import json
import os
import random
import difflib
import openpyxl
from openpyxl.styles import Alignment, Font, PatternFill
from openpyxl.utils import get_column_letter

ROOT = '/Users/liuxinyuan/Desktop/Hermes输出-工作类'
SRC = os.path.join(ROOT, '数据', 'daily_activities_mcp.json')
CACHE = os.path.join(ROOT, '数据', 'activity_extract_cache.json')
OUT = os.path.join(ROOT, '销售分析', '活动抽取_本地8b_vs_DeepSeek_2026-10-26.xlsx')
KEYS = ['客户', '项目或货号', '卡点', '下一步动作']
EMPTY = ('', '未提及', '无', 'none', 'null', '未提供')


def norm(v):
    if isinstance(v, (list, tuple)):
        v = '；'.join(str(x) for x in v if str(x).strip())
    v = str(v or '').strip().strip('；,，。 ')
    return '' if v.lower() in EMPTY else v


def sim(a, b):
    if not a and not b:
        return 1.0
    if not a or not b:
        return 0.0
    return difflib.SequenceMatcher(None, a, b).ratio()


def main():
    items = json.load(open(SRC, encoding='utf-8'))['items']
    pool = [x for x in items if 300 <= len(str(x.get('field_b99055') or '')) <= 1200]
    random.seed(42)
    sample = random.sample(pool, min(200, len(pool)))
    cache = json.load(open(CACHE, encoding='utf-8'))

    rows = []
    for x in sample:
        wid = str(x.get('work_item_id'))
        c = cache.get(wid) or {}
        lo, ds = c.get('local') or {}, c.get('ds') or {}
        r = {'wid': wid, 'name': x.get('name'), 'owner': (x.get('owner') or {}).get('name_cn'),
             'date': x.get('start_time'), 'text': str(x.get('field_b99055'))[:400]}
        for k in KEYS:
            r['L_' + k] = norm(lo.get(k))
            r['D_' + k] = norm(ds.get(k))
        rows.append(r)

    n = len(rows)
    stat = []
    for k in KEYS:
        lp = sum(1 for r in rows if r['L_' + k]) / n * 100
        dp = sum(1 for r in rows if r['D_' + k]) / n * 100
        both = [r for r in rows if r['L_' + k] and r['D_' + k]]
        ag = sum(sim(r['L_' + k], r['D_' + k]) for r in both) / len(both) * 100 if both else 0
        stat.append((k, lp, dp, ag, len(both)))

    # 综合：两模型都为空的字段视为一致；单边为空的记为不一致
    def row_score(r):
        s = []
        for k in KEYS:
            a, b = r['L_' + k], r['D_' + k]
            s.append(1.0 if (not a and not b) else (0.0 if (not a or not b) else sim(a, b)))
        return sum(s) / len(KEYS)

    for r in rows:
        r['score'] = row_score(r)
    agree = sum(1 for r in rows if r['score'] >= 0.75) / n * 100

    wb = openpyxl.Workbook()
    ws = wb.active
    ws.title = '汇总'
    ws.append(['字段', '本地 qwen3:8b 有值率(%)', 'DeepSeek 有值率(%)', '双方都有值时的平均一致度(%)', '双方都有值条数'])
    for s in stat:
        ws.append([s[0], round(s[1], 1), round(s[2], 1), round(s[3], 1), s[4]])
    ws.append([])
    ws.append(['整体一致率(≥0.75)', round(agree, 1), '', '', ''])
    ws.append(['样本量', n, '', '', ''])
    ws.append(['说明', '无人工标注：有值率反映抽取覆盖，一致度反映两模型是否说同一件事，≠准确率', '', '', ''])
    for c in ws[1]:
        c.font = Font(color='FFFFFF', bold=True)
        c.fill = PatternFill('solid', fgColor='1F4E79')
    for i, w in enumerate([26, 24, 22, 26, 16], 1):
        ws.column_dimensions[get_column_letter(i)].width = w

    ws2 = wb.create_sheet('差异清单')
    ws2.append(['活动名称', '销售', '日期', '字段', '本地8b', 'DeepSeek', '一致度'])
    gaps = []
    for r in rows:
        for k in KEYS:
            a, b = r['L_' + k], r['D_' + k]
            if (a or b) and (not a or not b or sim(a, b) < 0.7):
                gaps.append((sim(a, b) if (a and b) else 0, r, k, a, b))
    gaps.sort(key=lambda z: z[0])
    for sc, r, k, a, b in gaps[:40]:
        ws2.append([r['name'], r['owner'], r['date'], k, a[:180], b[:180], round(sc, 2)])
    for c in ws2[1]:
        c.font = Font(color='FFFFFF', bold=True)
        c.fill = PatternFill('solid', fgColor='1F4E79')
    for i, w in enumerate([30, 8, 11, 12, 50, 50, 8], 1):
        ws2.column_dimensions[get_column_letter(i)].width = w

    ws3 = wb.create_sheet('明细')
    ws3.append(['活动名称', '销售', '日期', '原文摘要'] + ['本地·' + k for k in KEYS] + ['DS·' + k for k in KEYS] + ['一致度'])
    for r in rows:
        ws3.append([r['name'], r['owner'], r['date'], r['text'][:200]] +
                   [r['L_' + k][:120] for k in KEYS] + [r['D_' + k][:120] for k in KEYS] + [round(r['score'], 2)])
    for c in ws3[1]:
        c.font = Font(color='FFFFFF', bold=True)
        c.fill = PatternFill('solid', fgColor='1F4E79')
    for i in range(1, 14):
        ws3.column_dimensions[get_column_letter(i)].width = 26 if i > 4 else 30
    for s in (ws2, ws3):
        s.freeze_panes = 'A2'
        for row in s.iter_rows(min_row=2):
            for c in row:
                c.alignment = Alignment(vertical='top', wrap_text=True)

    wb.save(OUT)
    print('✅', OUT)
    for s in stat:
        print('  %-8s 有值率 local %.0f%% / ds %.0f%% ｜ 一致 %.0f%%（%d 条）' % (s[0], s[1], s[2], s[3], s[4]))
    print('  整体一致率 %.0f%%' % agree)


if __name__ == '__main__':
    main()