#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""飞书 MCP 客诉：① 未闭环明细 ② 近 90 天按货号归因

输出：销售分析/客诉_未闭环与近90天货号归因_2026-10-24.xlsx
     数据/complaints_open_sku90.json
口径：数据/mcp_complaints.json（MCP 每日刷新）；
     未闭环 = status != '结束'；近 90 天 = created >= 2026-07-26（基准日 2026-10-24）；
     货号识别 = 用 order_sku_index.json 的 7,589 个在用货号做最长匹配，未命中再按短码正则兜底。
"""
import json
import os
import re
from collections import Counter, defaultdict

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, '数据', 'mcp_complaints.json')
IDX = os.path.join(ROOT, '数据', 'order_sku_index.json')
OUT = os.path.join(ROOT, '销售分析', '客诉_未闭环与近90天货号归因_2026-10-24.xlsx')
BASE_DAY = '2026-10-24'
RECENT_FROM = '2026-07-26'

d = json.load(open(SRC, encoding='utf-8'))
items = d['items']
skus = json.load(open(IDX, encoding='utf-8'))['skus']
name_of = {k: (v.get('name') or '') for k, v in skus.items()}
known = sorted(skus.keys(), key=len, reverse=True)

SHORT = re.compile(r'(?<![A-Z0-9])(AS\d{2,4}|E\d{1,3}|M\d{4}|FM\d{4}|FMD\d{4})(?![A-Z0-9])', re.I)


def extract_sku(text):
    t = str(text or '').upper()
    for s in known:
        su = s.upper()
        if len(su) < 4:
            continue
        if su in t:
            # 边界检查：避免 A3 命中 B3A3 之类
            i = t.find(su)
            left = t[i - 1] if i > 0 else ' '
            right = t[i + len(su)] if i + len(su) < len(t) else ' '
            if not (left.isalnum() or right.isalnum() and right not in '-_'):
                return s
            return s
    m = SHORT.search(t)
    if m:
        return m.group(1).upper()
    m2 = re.search(r'(?<![A-Z0-9])([A-Z]{2,5}\d{2,4}(?:-[A-Z0-9]+){1,5})(?![A-Z0-9])', t)
    return m2.group(1) if m2 else ''


def d10(x):
    return str(x or '')[:10]


for x in items:
    x['sku'] = extract_sku(x.get('name'))
    x['created'] = d10(x.get('created'))

open_items = [x for x in items if str(x.get('status') or '') != '结束']
recent = [x for x in items if x['created'] >= RECENT_FROM]

URG = {'critical': 0, 'high': 1, 'medium': 2, 'normal': 3}
open_items.sort(key=lambda x: (URG.get(x.get('urgency'), 9), -int(x.get('days') or 0)))
recent.sort(key=lambda x: (x['created'], URG.get(x.get('urgency'), 9)), reverse=True)

grp = defaultdict(lambda: {'n': 0, 'cust': set(), 'open': 0, 'crit': 0, 'high': 0,
                           'last': '', 'names': [], 'status': Counter()})
for x in recent:
    k = x['sku'] or '未识别'
    g = grp[k]
    g['n'] += 1
    g['cust'].add(x.get('customer') or '')
    if str(x.get('status')) != '结束':
        g['open'] += 1
    if x.get('urgency') == 'critical':
        g['crit'] += 1
    if x.get('urgency') == 'high':
        g['high'] += 1
    g['last'] = max(g['last'], x['created'])
    g['names'].append(x.get('name') or '')
    g['status'][x.get('status') or ''] += 1

rows_sku = []
for k, g in sorted(grp.items(), key=lambda kv: (-kv[1]['n'], kv[0])):
    rows_sku.append([k, (name_of.get(k) or '')[:46], g['n'], len(g['cust'] - {''}), g['open'],
                     g['crit'], g['high'], g['last'], '、'.join(sorted(c for c in g['cust'] if c)[:4]),
                     '；'.join(n[:34] for n in g['names'][:3])])

wb = openpyxl.Workbook()
HF = PatternFill('solid', fgColor='1F4E79')
HFONT = Font(color='FFFFFF', bold=True, size=10)


def head(ws, cols, widths):
    for i, h in enumerate(cols, 1):
        c = ws.cell(row=1, column=i, value=h)
        c.fill = HF
        c.font = HFONT
        c.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)
        ws.column_dimensions[get_column_letter(i)].width = widths[i - 1]
    ws.freeze_panes = 'A2'


ws1 = wb.active
ws1.title = '未闭环明细'
head(ws1, ['序号', '工作项ID', '创建日', '挂起天数', '紧急度', '区域', '客户', '客诉主题', '当前状态', '当前节点', '创建人', '处理人'],
     [6, 12, 11, 9, 9, 9, 30, 58, 20, 14, 10, 16])
for i, x in enumerate(open_items, 1):
    op = x.get('operator') or []
    if isinstance(op, list):
        op = '、'.join(str(o.get('name') or o) if isinstance(o, dict) else str(o) for o in op)
    ws1.append([i, x.get('id'), x['created'], x.get('days'), x.get('urgency'), x.get('area'),
                x.get('customer'), x.get('name'), x.get('status'), x.get('node'), x.get('creator'), op])
ws1.auto_filter.ref = ws1.dimensions

ws2 = wb.create_sheet('近90天按货号')
head(ws2, ['货号', '品名', '客诉数', '涉及客户数', '未闭环', 'critical', 'high', '最近日期', '主要客户', '典型问题'],
     [24, 42, 8, 10, 8, 9, 7, 11, 40, 66])
for r in rows_sku:
    ws2.append(r)
ws2.auto_filter.ref = ws2.dimensions

ws3 = wb.create_sheet('近90天明细')
head(ws3, ['创建日', '紧急度', '区域', '客户', '货号', '客诉主题', '状态', '挂起天数', '创建人'],
     [11, 9, 9, 30, 22, 60, 20, 9, 10])
for x in recent:
    ws3.append([x['created'], x.get('urgency'), x.get('area'), x.get('customer'), x['sku'] or '未识别',
                x.get('name'), x.get('status'), x.get('days'), x.get('creator')])
ws3.auto_filter.ref = ws3.dimensions

# 产品族视图
fam = defaultdict(lambda: {'n': 0, 'open': 0, 'cust': set(), 'skus': set(), 'crit': 0})
for x in recent:
    k = x['sku'] or '未识别'
    f = k.split('-')[0] if k != '未识别' else '未识别'
    z = fam[f]
    z['n'] += 1
    z['skus'].add(k)
    z['cust'].add(x.get('customer') or '')
    if str(x.get('status')) != '结束':
        z['open'] += 1
    if x.get('urgency') == 'critical':
        z['crit'] += 1

ws_f = wb.create_sheet('近90天按产品族')
head(ws_f, ['产品族', '客诉数', '未闭环', 'critical', '货号数', '涉及客户数', '货号明细'],
     [16, 8, 8, 9, 8, 10, 70])
for f, z in sorted(fam.items(), key=lambda kv: -kv[1]['n']):
    ws_f.append([f, z['n'], z['open'], z['crit'], len(z['skus']), len(z['cust'] - {''}),
                 '、'.join(sorted(z['skus'])[:10])])

ws4 = wb.create_sheet('口径')
notes = [['项目', '说明'],
         ['数据源', '数据/mcp_complaints.json（飞书 MCP 客诉工作项，更新 %s，共 %d 条）' % (d.get('updated'), len(items))],
         ['未闭环', '当前状态 != 结束，共 %d 条' % len(open_items)],
         ['近90天', '创建日 >= %s（基准日 %s），共 %d 条' % (RECENT_FROM, BASE_DAY, len(recent))],
         ['货号识别', '用 order_sku_index.json 的 %d 个在用货号做最长匹配；未命中按短码（AS/E/M/FM/FMD）兜底；仍无则记「未识别」' % len(known)],
         ['注意', '客诉主题是自由文本，货号可能写在正文而非标题，未识别条目需人工补；同一客诉可能涉及多货号，本表只取首个命中']]
for row in notes:
    ws4.append(row)
ws4['A1'].font = Font(bold=True, color='FFFFFF')
ws4['B1'].font = Font(bold=True, color='FFFFFF')
for c in ('A1', 'B1'):
    ws4[c].fill = HF
ws4.column_dimensions['A'].width = 12
ws4.column_dimensions['B'].width = 110

wb.save(OUT)
json.dump({'open': open_items, 'recent': recent}, open(os.path.join(ROOT, '数据', 'complaints_open_sku90.json'), 'w'),
          ensure_ascii=False, indent=0, default=str)

print('未闭环', len(open_items), '｜近90天', len(recent), '｜未识别货号', grp.get('未识别', {}).get('n', 0))
for r in rows_sku[:12]:
    print('  %-22s %-30s 客诉%2d 客户%2d 未闭环%2d' % (r[0], r[1][:26], r[2], r[3], r[4]))
print('✅', OUT)