#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""探针：货号→商机 的可关联性；客诉/活动 数据能否支撑"增减归因"。"""
import json, os
import pandas as pd

ROOT = '/Users/liuxinyuan/Desktop/Hermes输出-工作类'
XL = os.path.expanduser('~/.hermes/profiles/zone-b/cache/documents/doc_b68712feec68_刘子研三季度数据-截止至9.30(1).xls')

print('═══ ① .xls 里「商机」相关列（货号→商机 的接口）═══')
for sn in ['2026年销售数据', '2025年销售数据']:
    try:
        df = pd.read_excel(XL, sheet_name=sn, header=0, engine='xlrd')
    except Exception as e:
        print('  %s 读取失败 %s' % (sn, str(e)[:50])); continue
    df.columns = [str(c).strip() for c in df.columns]
    cand = [c for c in df.columns if ('商机' in c) or ('项目' in c)]
    print('  %s：%d 行 ｜ 商机/项目类列：%s' % (sn, len(df), cand or '（无）'))
    for c in cand:
        s = df[c].astype(str).str.strip().replace({'nan': '', 'None': ''})
        nz = int((s != '').sum())
        print('     %-18s 非空 %4d / %4d = %5.1f%%' % (c, nz, len(df), 100 * nz / max(len(df), 1)))
        if nz:
            print('        样例:', [x for x in s[s != ''].unique()[:3]])

print('\n═══ ② 客诉类数据（支撑"客诉致减"）═══')
for f in ['数据/mcp_complaints.json', '数据/sr_review_kac.json', '数据/customer_special_requirements.json']:
    p = os.path.join(ROOT, f)
    if not os.path.exists(p):
        print('  %-44s 不存在' % f); continue
    try:
        d = json.load(open(p, encoding='utf-8'))
        items = d if isinstance(d, list) else (d.get('items') or d.get('data') or next((v for v in d.values() if isinstance(v, list)), []))
        n = len(items) if hasattr(items, '__len__') else '?'
        ks = list(items[0].keys())[:14] if isinstance(items, list) and items and isinstance(items[0], dict) else []
        print('  %-44s %s 条' % (f, n))
        if ks: print('        字段:', ks)
    except Exception as e:
        print('  %-44s 读取异常 %s' % (f, str(e)[:40]))

print('\n═══ ③ 活动记录（支撑"机会/风险"归因：竞对、客户需求）═══')
p = os.path.join(ROOT, '数据/activities_feishu.json')
if os.path.exists(p):
    d = json.load(open(p, encoding='utf-8'))
    items = d if isinstance(d, list) else (d.get('items') or next((v for v in d.values() if isinstance(v, list)), []))
    print('  条数:', len(items))
    if items and isinstance(items[0], dict):
        ks = list(items[0].keys())
        print('  字段:', ks[:22])
        for k in ks:
            if any(x in str(k) for x in ('竞', '对', '需求', '商机', '货号', '客户', '描述')):
                nz = sum(1 for it in items[:3000] if str(it.get(k, '')).strip() not in ('', 'nan', 'None'))
                print('     %-24s 前3000条非空 %d' % (k, nz))
else:
    print('  activities_feishu.json 不存在')

print('\n═══ ④ 竞品情报类数据（支撑"竞品更好"）═══')
for pat in ['数据/product_selling_points.json', '数据/price_index.json', '数据/competitor', '知识库-copilot']:
    p = os.path.join(ROOT, pat)
    print('  %-40s %s' % (pat, '存在' if os.path.exists(p) else '不存在'))