#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""2026Q3 汇报：把 25 张嵌入表归一成 5 张标准表 → 数据/2026Q3汇报_汇总.json。

表型判定（按表头行签名）：
  T1 商机明细      含「客户名称」+「商机名称」
  T2 逾期应收明细  含「往来单位」+「单据编号」（附表_逾期&未逾期（汇总））
  T3 逾期回款计划/进度 含「往来单位」或「总逾期应收」
  T4 客户季度分析  含「客户名称」+「年份」+「1季度」
  T5 个人月度销售额 无表头、形如 年/月 + 数值网格
"""
import json
import os
import re

import pandas as pd

EXT = '/Users/liuxinyuan/Desktop/Hermes输出-工作类/经营分析/2026Q3汇报_嵌入Excel'
CACHE = os.path.expanduser('~/.hermes/profiles/zone-b/cache/documents')
SENT = os.path.join(CACHE, 'doc_afb3f475048f_工作表 在 诊断原料-2026年3季度.xlsx')
OUT = '/Users/liuxinyuan/Desktop/Hermes输出-工作类/数据/2026Q3汇报_汇总.json'

T1_COLS = ['销售', '部门', '序号', '集团客户', '客户名称', '商机名称', '商机性质', '2026新增', '2026丢失',
           '商机背景概述', '资源需求', '关键时间节点', '前三季度异常原因复盘', '三季度异常原因复盘',
           '四季度冲刺难点及措施', '四季度目标保障难点及措施', '业务类型', '研发',
           '2024年销售额', '2025年销售额', '25年销售额', '2026年销售额', '26年销售额',
           '2026目标', '2026年度达成%', '1季度\n计划', '1季度\n实绩', '2季度\n计划', '2季度\n实绩',
           '3季度\n计划', '3季度\n实绩', '4季度\n计划', '4季度\n实绩', '商机ID']


def clean(v):
    if v is None:
        return ''
    s = str(v).replace('\xa0', ' ').strip()
    return '' if s.lower() in ('nan', 'nat', 'none') else s


def find_header(df, keys, max_scan=8):
    for i in range(min(max_scan, len(df))):
        row = [clean(v) for v in df.iloc[i].tolist()]
        joined = '|'.join(row)
        if all(k in joined for k in keys):
            return i, row
    return None, None


def map_cols(hdr, aliases):
    idx = {}
    for canon, names in aliases.items():
        for j, h in enumerate(hdr):
            hh = re.sub(r'\s+', '', h)
            if any(re.sub(r'\s+', '', n) == hh for n in names):
                idx[canon] = j
                break
    return idx


NOTE_RE = re.compile(r'^\s*(说明[:：]|\d+\s*[、.．]|注[:：])|数据来源|商机合计|单位是|需要具体到')


def is_note(vals):
    """表内注释/合计行：不是业务数据，别当行。"""
    txt = ' '.join(vals[:6])
    if NOTE_RE.search(txt):
        return True
    if any(v == '合计' for v in vals[:3]):
        return True
    core = [v for v in vals if v]
    return len(core) <= 1


def sheet_rows(df, hdr_i, cols_map, source, who, extra=None):
    out = []
    for _, r in df.iloc[hdr_i + 1:].iterrows():
        vals = [clean(v) for v in r.tolist()]
        if not any(vals) or is_note(vals):
            continue
        rec = {k: (vals[j] if j < len(vals) else '') for k, j in cols_map.items()}
        rec['_来源'] = source
        rec['_汇报人'] = who
        if extra:
            rec.update(extra)
        out.append(rec)
    return out


def parse_monthly(df, who, src, sheet):
    """月度网格 → 结构化：两种版式（月份在行 / 月份在列）。"""
    mono = re.compile(r'^\s*(\d{1,2})\s*月')
    outs = []
    vals = df.values.tolist()
    for i, row in enumerate(vals):
        cells = [clean(v) for v in row]
        m = [j for j, c in enumerate(cells) if mono.match(c)]
        if not m:
            continue
        for j in m:
            mm = int(mono.match(cells[j]).group(1))
            nums = [c for c in cells[j + 1:]]
            num = [n for n in nums if re.fullmatch(r'-?\d+(\.\d+)?', n.replace(',', ''))]
            if num:
                outs.append({'汇报人': who, '来源文件': src, '工作表': sheet, '月份': mm,
                             '2026': num[0] if len(num) > 0 else '', '2025': num[1] if len(num) > 1 else ''})
        break
    if not outs:                       # 版式二：月份在第一行，数值在下面一行
        hdr = [clean(v) for v in vals[0]]
        m = [j for j, c in enumerate(hdr) if mono.match(c)]
        if m:
            for r2 in vals[1:]:
                cells = [clean(v) for v in r2]
                if not any(re.fullmatch(r'-?\d+(\.\d+)?', c.replace(',', '')) or c == '' for c in cells):
                    continue
                for j in m:
                    mm = int(mono.match(hdr[j]).group(1))
                    v = cells[j] if j < len(cells) else ''
                    if re.fullmatch(r'-?\d+(\.\d+)?', v.replace(',', '')):
                        outs.append({'汇报人': who, '来源文件': src, '工作表': sheet, '月份': mm,
                                     '数值': v})
                break
    return outs


def parse_ls_target(df, who, src, sheet):
    """生命科学部「2026年销售目标」表：月份在列、指标在行。"""
    vals = [[clean(v) for v in r] for r in df.values.tolist()]
    if not vals:
        return []
    hdr = vals[0]
    mon_cols = {}
    for j, h in enumerate(hdr):
        m = re.match(r'^\s*(\d{1,2})\s*月', h)
        if m:
            mon_cols[j] = int(m.group(1))
    out = []
    rowmap = {}
    for r in vals[1:]:
        if not r:
            continue
        lab = r[0]
        if lab in ('销售目标', '实际完成', '完成率'):
            rowmap[lab] = r
    for j, mm in sorted(mon_cols.items()):
        rec = {'汇报人': who, '来源文件': src, '工作表': sheet, '月份': mm}
        for lab in ('销售目标', '实际完成', '完成率'):
            r = rowmap.get(lab)
            rec[lab] = r[j] if r and j < len(r) else ''
        out.append(rec)
    return out


LS_KT_COLS = ['学校', '院系', '名称', '商机状态', '意向产品', '导师研究方向', '决策人', '课题组模式',
              '痛点', '合作产品', '年度目标（w）', '上半年开发计划', '上半年异常原因复盘',
              '下半年目标保障难点及措施']


def parse_ls_kt(df, who, src, sheet):
    """生命科学部「重点课题组」表（课题组=商机单位）。"""
    vals = [[clean(v) for v in r] for r in df.values.tolist()]
    if not vals:
        return []
    hdr = vals[0]
    idx = {}
    for j, h in enumerate(hdr):
        hh = re.sub(r'\s+', '', h)
        for c in LS_KT_COLS:
            if re.sub(r'\s+', '', c) == hh:
                idx[c] = j
    out = []
    for r in vals[1:]:
        if not any(r):
            continue
        rec = {'汇报人': who, '来源文件': src}
        for c in LS_KT_COLS:
            j = idx.get(c)
            rec[c] = r[j] if j is not None and j < len(r) else ''
        if rec.get('名称'):
            out.append(rec)
    return out


def parse_ls_forecast(df, who, src, sheet):
    """生命科学部「2026年销售预测（四季度/下半年）」表：表头即列名。"""
    vals = [[clean(v) for v in r] for r in df.values.tolist()]
    if not vals:
        return []
    hdr = [h for h in vals[0]]
    out = []
    for r in vals[1:]:
        if not any(r):
            continue
        rec = {'汇报人': who, '来源文件': src, '工作表': sheet}
        for j, h in enumerate(hdr):
            if h and j < len(r):
                rec[h] = r[j]
        if rec.get('客户名称') or rec.get('课题组'):
            out.append(rec)
    return out


def parse_big_personal(df, who, src, sheet):
    """大客户部「个人业绩完成情况」模板：年度目标 / 月度实绩 / 剩余目标 / 已完成 / 完成率
    ＋ 客户同比归因（客户 / 原因分析 / 重点货号 / 备注）。原表直读，不做换算。

    版式（刘子研 Workbook1/Sheet2）：标签「个人业绩完成情况」所在行左侧为年度目标，
    下一行同列左侧为各月实绩、该列为合计；标签列右侧为剩余目标 / 已完成 / 完成率；
    下方为「客户 | 去年同期同比原因分析 | 重点货号 | 备注」块（客户列纵向合并需前向填充）。
    """
    vals = [[clean(v) for v in r] for r in df.values.tolist()]

    def _num(s):
        try:
            return float(str(s).replace(',', '').replace('万元', '').strip())
        except Exception:
            return None

    r0 = c0 = None
    for i, row in enumerate(vals):
        for j, v in enumerate(row):
            if '个人业绩完成情况' in str(v):
                r0, c0 = i, j
                break
        if r0 is not None:
            break

    per = []
    if r0 is not None:
        tgcol = None
        for j in range(c0 - 1, -1, -1):
            if _num(vals[r0][j]) is not None:
                tgcol = j
                break
        rec = {'汇报人': who, '来源文件': src, '工作表': sheet, '年度目标（万元）': None,
               '月度实绩': '', '月度合计（万元）': None,
               '2026年剩余目标（万元）': None, '已完成（万元）': None, '完成率': None}
        if tgcol is not None:
            rec['年度目标（万元）'] = _num(vals[r0][tgcol])
            if r0 + 1 < len(vals):
                months = [_num(vals[r0 + 1][j]) for j in range(0, tgcol)]
                months = [m for m in months if m is not None]
                rec['月度实绩'] = '、'.join('%g' % m for m in months)
                rec['月度合计（万元）'] = _num(vals[r0 + 1][tgcol])
        for i in range(r0, min(r0 + 6, len(vals))):
            row = vals[i]
            if c0 < len(row):
                lab = str(row[c0])
                for key in ('剩余目标', '已完成', '完成率'):
                    if key in lab:
                        v = None
                        for jj in range(c0 + 1, len(row)):
                            v = _num(row[jj])
                            if v is not None:
                                break
                        rec[key if key == '完成率' else [k for k in ('2026年剩余目标（万元）', '已完成（万元）') if key in k][0]] = v
        per.append(rec)

    attr = []
    hr = None
    for i, row in enumerate(vals):
        if any(str(v).strip() == '客户' for v in row) and any('重点货号' in str(v) for v in row):
            hr = i
            break
    if hr is not None:
        hdr = vals[hr]

        def _ci(*keys):
            for j, v in enumerate(hdr):
                if any(k in str(v) for k in keys):
                    return j
            return None
        ci, ri = _ci('客户'), _ci('原因分析', '原因')
        gi, ni = _ci('重点货号'), _ci('备注')

        def _cell(r, i):
            if i is None or i >= len(r):
                return ''
            v = str(r[i]).strip()
            return '' if v in ('nan', 'None') else v

        cur = ''
        for r in vals[hr + 1:]:
            if _cell(r, ci):
                cur = _cell(r, ci)
            reason, goods, note = _cell(r, ri), _cell(r, gi), _cell(r, ni)
            if not (reason or goods or note):
                continue
            if cur:
                attr.append({'汇报人': who, '来源文件': src, '客户': cur,
                             '原因分析': reason, '重点货号': goods, '备注': note})
    return per, attr


def parse_ls_prod_mix(df, who, src, sheet):
    """生命科学部「产品线销售额构成」表（列1 / 销售额 / 占比）。原表直读，单位随原表。"""
    vals = [[clean(v) for v in r] for r in df.values.tolist()]
    out = []
    for r in vals:
        if len(r) < 2:
            continue
        name = str(r[0]).strip()
        if not name or name in ('列1', '产品线', 'nan', 'None'):
            continue
        amt, pct = str(r[1]).strip(), (str(r[2]).strip() if len(r) > 2 else '')
        if not re.fullmatch(r'-?\d+(\.\d+)?', amt.replace(',', '')) and not re.fullmatch(r'0?\.\d+', pct):
            continue
        out.append({'汇报人': who, '来源文件': src, '工作表': sheet,
                    '产品线': name, '销售额': amt, '占比': pct})
    return out


def parse_year_monthly(df, who, src, sheet):
    """版式三：首列「YYYY年」，同行其余列为各月数值。原表直读。"""
    vals = [[clean(v) for v in r] for r in df.values.tolist()]
    out = []
    for r in vals:
        if not r:
            continue
        m = re.match(r'^\s*(20\d\d)\s*年', str(r[0]))
        if not m:
            continue
        yr = m.group(1)
        for j, v in enumerate(r[1:], 1):
            if re.fullmatch(r'-?\d+(\.\d+)?', str(v).replace(',', '')):
                rec = {'汇报人': who, '来源文件': src, '工作表': sheet, '月份': j}
                rec[yr] = str(v)
                out.append(rec)
    return out


def parse_ls_market_scan(df, who, src, sheet):
    """广东区「市场扫描」：按课题组记录在用品牌（两阶段：分子扩增 / 细胞蛋白）与容量。"""
    vals = [[clean(v) for v in r] for r in df.values.tolist()]
    P1 = [('高保真PCR', 4), ('同源重组酶', 6), ('小提/大提', 8), ('RT逆转录', 10),
          ('Q荧光定量', 12), ('普通PCR', 14), ('内切酶', 16)]
    P2 = [('ECL发光液', 19), ('蛋白marker', 21), ('蛋白胶', 23), ('蛋白定量', 25), ('PVDF', 27)]
    out, unit, loc = [], '', ''
    for r in vals:
        if len(r) < 3:
            continue
        if str(r[0]).strip() == '单位' or str(r[2]).strip() == '课题组':
            continue
        if str(r[0]).strip() not in ('', 'nan'):
            unit = str(r[0]).strip()
        if len(r) > 1 and str(r[1]).strip() not in ('', 'nan'):
            loc = str(r[1]).strip()
        grp = str(r[2]).strip()
        if not grp or grp in ('nan', 'None'):
            continue
        rec = {'汇报人': who, '来源文件': src, '工作表': sheet, '单位': unit, '位置': loc, '课题组': grp}
        got = False
        for name, j in P1 + P2:
            if j < len(r) and str(r[j]).strip() not in ('', 'nan'):
                rec[name] = str(r[j]).strip()
                if name in ('高保真PCR', '同源重组酶', '小提/大提', 'RT逆转录', 'Q荧光定量', '普通PCR', '内切酶') \
                        and j + 1 < len(r) and str(r[j + 1]).strip() not in ('', 'nan'):
                    rec[name + '_容量'] = str(r[j + 1]).strip()
                got = True
        if got:
            out.append(rec)
    return out


def main():
    files = []
    for root, dirs, fs in os.walk(EXT):
        for f in sorted(fs):
            if f.endswith(('.xlsx', '.xls')) and not f.startswith('_'):
                files.append(os.path.join(root, f))
    if os.path.exists(SENT):
        files.append(SENT)

    # 同一人同主题的多版本（v01/v02、副本、-2）→ 只保留最高版本，避免重复计数
    def _vkey(name):
        m = re.search(r'[vV](\d{1,2})', name)
        return int(m.group(1)) if m else 0
    best = {}
    for p in files:
        b = os.path.basename(p)
        # 关键：按「主体 | 工作簿」两级做键，只对同一工作簿做版本比较
        parts = b.split('__')
        bare = re.sub(r'[vV]\d{1,2}', '', parts[0]).strip()
        wb = parts[1] if len(parts) > 1 else ''
        stem = bare + '|' + wb
        cur = best.get(stem)
        if cur is None or _vkey(b) > _vkey(os.path.basename(cur)):
            best[stem] = p
    dropped = [os.path.basename(p) for p in files if p not in set(best.values())]
    files = list(best.values())

    # 文件名不含姓名的件 → 按投递目录/文件名特征归人（徐卓凡在 6-徐 目录投递部门模板）
    WHO_FIX = {
        '工作表 在 诊断原料-2026年3季度': '徐卓凡',
        '副本工作表 在 诊断原料-2026年3季度': '徐卓凡',
        '诊断原料-2026年三季度销售工作汇报（销售人员版本）': '徐卓凡',
    }

    T = {k: [] for k in ('商机明细', '逾期应收明细', '逾期回款计划', '逾期回款进度', '客户季度分析', '个人月度销售额')}
    inventory = []
    for p in files:
        dept = os.path.basename(os.path.dirname(p))
        who = os.path.basename(p).split('__')[0]
        for _k, _v in WHO_FIX.items():
            if _k in who:
                who = _v
                break
        if who.startswith('doc_'):
            who = '徐卓凡'
        try:
            x = pd.ExcelFile(p)
        except Exception as e:
            inventory.append({'文件': os.path.basename(p), '状态': '读失败:' + str(e)[:40]})
            continue
        for s in x.sheet_names:
            df = x.parse(s, header=None)
            if df.empty or df.dropna(how='all').empty:
                inventory.append({'文件': os.path.basename(p), '工作表': s, '形状': '%d×%d' % df.shape,
                                  '判定': '空表（跳过）', '表头行': None})
                continue
            # ── 生命科学部专用模板（目标 / 重点课题组 / 四季度预测）──
            s_norm = re.sub(r'\s+', '', s)
            if s_norm in ('2026年销售目标', '2026年销售目标表'):
                kind = '生命科学_月度目标'
                recs = parse_ls_target(df, who, os.path.basename(p), s)
                T.setdefault(kind, []).extend(recs)
                inventory.append({'文件': os.path.basename(p), '工作表': s, '形状': '%d×%d' % df.shape,
                                  '判定': kind, '表头行': 1, '真实数据行': len(recs)})
                continue
            if '重点课题组' in s_norm:
                kind = '生命科学_课题组'
                recs = parse_ls_kt(df, who, os.path.basename(p), s)
                T.setdefault(kind, []).extend(recs)
                inventory.append({'文件': os.path.basename(p), '工作表': s, '形状': '%d×%d' % df.shape,
                                  '判定': kind, '表头行': 1, '真实数据行': len(recs)})
                continue
            if s_norm.startswith('2026年销售预测'):
                kind = '生命科学_四季度预测'
                recs = parse_ls_forecast(df, who, os.path.basename(p), s)
                T.setdefault(kind, []).extend(recs)
                inventory.append({'文件': os.path.basename(p), '工作表': s, '形状': '%d×%d' % df.shape,
                                  '判定': kind, '表头行': 1, '真实数据行': len(recs)})
                continue

            # ── 大客户部「个人业绩完成情况」模板（含客户同比归因 + 重点货号）──
            _flat = ' '.join(str(v) for r in df.values.tolist() for v in r)
            if '个人业绩完成情况' in _flat:
                recs, attr = parse_big_personal(df, who, os.path.basename(p), s)
                if recs:
                    T.setdefault('大客户部_个人业绩', []).extend(recs)
                if attr:
                    T.setdefault('大客户部_客户同比归因', []).extend(attr)
                inventory.append({'文件': os.path.basename(p), '工作表': s, '形状': '%d×%d' % df.shape,
                                  '判定': '大客户部_个人业绩', '表头行': 1,
                                  '真实数据行': len(recs) + len(attr)})
                continue

            # ── 生命科学部·产品线销售额构成（叶信福 Workbook3 版式）──
            if '产品线销售额' in _flat or ('列1' in _flat and '销售额' in _flat and len(df) <= 20):
                recs = parse_ls_prod_mix(df, who, os.path.basename(p), s)
                if recs:
                    T.setdefault('生命科学_产品线构成', []).extend(recs)
                    inventory.append({'文件': os.path.basename(p), '工作表': s, '形状': '%d×%d' % df.shape,
                                      '判定': '生命科学_产品线构成', '表头行': 1, '真实数据行': len(recs)})
                    continue
            # ── 广东区「市场扫描」：课题组竞品在用矩阵 ──
            if '市场扫描' in s_norm:
                recs = parse_ls_market_scan(df, who, os.path.basename(p), s)
                if recs:
                    T.setdefault('生命科学_市场扫描', []).extend(recs)
                    inventory.append({'文件': os.path.basename(p), '工作表': s, '形状': '%d×%d' % df.shape,
                                      '判定': '生命科学_市场扫描', '表头行': 2, '真实数据行': len(recs)})
                    continue

            hdr_i, hdr = find_header(df, ['客户名称', '商机名称'])
            if hdr_i is None:
                hdr_i, hdr = find_header(df, ['往来单位', '单据编号'])
            if hdr_i is None:
                hdr_i, hdr = find_header(df, ['客户名称', '年份'])
            if hdr_i is None and s == '逾期应收回款_进度':
                hdr_i, hdr = find_header(df, ['总逾期应收'])
            kind = None
            if hdr_i is None:
                recs = parse_monthly(df, who, os.path.basename(p), s)
                if not recs:
                    recs = parse_year_monthly(df, who, os.path.basename(p), s)
                if recs:
                    kind = '个人月度销售额'
                    T[kind].extend(recs)
                else:
                    # 不是月度网格、也不是已知表型（如生命科学部的目标/课题组/预测模板）
                    kind = '未识别'
            else:
                j = '|'.join(hdr)
                if '客户名称' in j and '商机名称' in j:
                    kind = '商机明细'
                    m = map_cols(hdr, {c: [c] for c in T1_COLS})
                    rows = sheet_rows(df, hdr_i, m, os.path.basename(p), who, {'工作表': s})
                    T[kind] += [r for r in rows if (r.get('客户名称') or r.get('商机名称'))]
                elif '往来单位' in j and '单据编号' in j:
                    kind = '逾期应收明细'
                    df = df[~df.apply(lambda r2: '合计' in [clean(v) for v in r2.tolist()][:4], axis=1)]
                    # 双行表头会拼成「逾期应收逾期天数」这类复合名，每个字段给足别名
                    aliases = {'销售部门': ['销售部门'], '销售员': ['销售员'], '往来单位': ['往来单位'],
                               '单据编号': ['单据编号'], '合同编号': ['合同编号'], '业务日期': ['业务日期'],
                               '到期日': ['到期日'], '应收金额原币': ['应收金额原币'],
                               '逾期天数': ['逾期天数', '逾期应收逾期天数'],
                               '逾期金额': ['逾期金额', '逾期应收逾期金额'],
                               '未逾期天数': ['未逾期天数', '未逾期应收未逾期天数'],
                               '未逾期金额': ['未逾期金额', '未逾期应收未逾期金额'],
                               '10月回款进度': ['2026年10月回款进度', '2026年10月回款进度 ']}
                    # 两行表头：把下移一行的「逾期天数/逾期金额/未逾期天数/未逾期金额」并进表头
                    if hdr_i + 1 < len(df):
                        sub = [clean(v) for v in df.iloc[hdr_i + 1].tolist()]
                        for j2, v in enumerate(sub):
                            if v and j2 < len(hdr):
                                hdr[j2] = (hdr[j2] + v) if hdr[j2] in ('逾期应收', '未逾期应收') else (hdr[j2] or v)
                    m = map_cols(hdr, aliases)
                    T[kind] += sheet_rows(df, hdr_i + 1 if '逾期天数' in j else hdr_i, m, os.path.basename(p), who)
                elif '往来单位' in j or '总逾期应收' in j:
                    kind = '逾期回款计划' if '往来单位' in j else '逾期回款进度'
                    m = map_cols(hdr, {c: [c] for c in
                                       ['销售部门', '销售员', '往来单位', '单据编号', '到期日', '逾期金额', '逾期应收',
                                        '总逾期应收', '60天以上', '10月计划', '计划回款日期', '实际回款日期', '备注']})
                    T[kind] += sheet_rows(df, hdr_i, m, os.path.basename(p), who, {'工作表': s})
                elif '客户名称' in j and '年份' in j:
                    kind = '客户季度分析'
                    m = map_cols(hdr, {'集团名称': ['集团名称'], '客户名称': ['客户名称'], '年份': ['年份'],
                                       '1季度': ['1季度', '第一季度'], '2季度': ['2季度', '第二季度'],
                                       '3季度': ['3季度', '第三季度'], '4季度': ['4季度', '第四季度'],
                                       '合计': ['合计'], '同比': ['同比'], '原因分析': ['去年同期同比增长或下降原因分析']})
                    T[kind] += sheet_rows(df, hdr_i, m, os.path.basename(p), who)
            real = None
            if kind == '商机明细':
                real = sum(1 for r in T[kind][-len(sheet_rows(df, hdr_i, {}, '', '')) - 1:]
                           if r.get('客户名称') or r.get('商机名称')) if hdr_i is not None else 0
            inventory.append({'文件': os.path.basename(p), '工作表': s, '形状': '%d×%d' % df.shape,
                              '判定': kind or '未识别', '表头行': (hdr_i + 1) if hdr_i is not None else None,
                              '真实数据行': real})

    # 逾期应收明细去重（同一张附表明细被多人在 PPT 里复用）
    seen, dedup = {}, []
    for r in T['逾期应收明细']:
        k = '|'.join(str(r.get(x, '')) for x in ('销售员', '往来单位', '单据编号', '到期日', '逾期金额', '未逾期金额'))
        if k in seen:
            seen[k]['_来源'] = seen[k]['_来源'] + '；' + r['_来源'] if r['_来源'] not in seen[k]['_来源'] else seen[k]['_来源']
            continue
        seen[k] = r
        dedup.append(r)
    T['逾期应收明细'] = dedup
    # 月度销售额去重（同一份被多人嵌入）
    seen2, dedup2 = {}, []
    for r in T['个人月度销售额']:
        k = '%s|%s' % (r.get('汇报人'), r.get('月份'))
        if k in seen2:
            continue
        seen2[k] = r
        dedup2.append(r)
    T['个人月度销售额'] = dedup2

    # 报送覆盖核对（应有/实收）+ 元信息
    EXPECT = {
        '大客户部': ['刘子研', '赵云浩'],
        '销售拓展部': ['陈溪子', '黄文才', '林潇', '李江辉', '张文蝶', '徐卓凡',
                  '宋学伟', '冯贤平', '刘虹晶', '杨天雄', '路要峰'],
        '生命科学销售部': ['黄建华', '王诗香', '陈彩红', '叶信福', '侯海瑜', '袁殿果', '吴云', '韩远怀'],
    }
    got = {}
    for p in files:
        d = os.path.basename(os.path.dirname(p))
        b = os.path.basename(p)
        for nm in EXPECT.get(d, []):
            if nm in b or (nm == '徐卓凡' and '诊断原料-2026年3季度' in b):
                got.setdefault(d, set()).add(nm)
    cover = []
    for d, names in EXPECT.items():
        g = got.get(d, set())
        miss = [n for n in names if n not in g]
        cover.append('%s：%d/%d %s' % (d, len(g), len(names), '（缺 ' + '、'.join(miss) + '）' if miss else '（齐全）'))

    ppt_n = len({os.path.basename(p).split('__')[0] for p in files if '__原表' not in os.path.basename(p)})
    stand_n = len([p for p in files if '__原表' in os.path.basename(p)])

    res = {'meta': {'生成': pd.Timestamp.now().strftime('%Y-%m-%d %H:%M:%S'),
                    '来源': '%d 份 PPT 内嵌 Excel + %d 份独立投递表（2026Q3 汇报）' % (ppt_n, stand_n),
                    '口径': '原表直读，未做任何换算；金额单位随原表（多为元）'},
           '计数': {k: len(v) for k, v in T.items()}, 'tables': T, 'inventory': inventory,
           '缺口': {
               '报送覆盖（应有/实收）': cover,
               '版本取舍（同人多版本只取最高版）': sorted(set(dropped)),
               '大客户部口径说明': ['大客户部（刘子研/赵云浩）把数据直接提炼进 PPT 表格，'
                              '不以附件形式挂 Excel —— 属部门报送口径，不是未完成。'],
               'PPT 内嵌空模板（保留供对照）': [i['文件'] + ' / ' + i['工作表'] for i in inventory
                                    if i.get('真实数据行') == 0 and i.get('判定') == '商机明细'],
               '空工作表': [i['文件'] + ' / ' + i['工作表'] for i in inventory if str(i.get('判定', '')).startswith('空表')],
               '未识别工作表': [i['文件'] + ' / ' + i['工作表'] for i in inventory if i.get('判定') == '未识别'],
               '按指令未读': ['转正答辩PPT（2026.9.27）'],
           }}
    os.makedirs(os.path.dirname(OUT), exist_ok=True)
    json.dump(res, open(OUT, 'w', encoding='utf-8'), ensure_ascii=False, indent=1)
    print('归一结果：')
    for k, v in T.items():
        print('  %-12s %5d 行' % (k, len(v)))
    print()
    print('工作簿盘点（%d 张表）：' % len(inventory))
    for it in inventory:
        if '判定' in it:
            print('  %-58s %-8s %-14s 表头行 %s' % (it['文件'][:58], it['形状'], it['判定'], it['表头行']))
    print('\n→ %s（%.1f KB）' % (OUT, os.path.getsize(OUT) / 1024.0))


if __name__ == '__main__':
    main()