#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""把 2026年3季度汇报/2026半年度汇报 下的 PPTX / XLSX 逐份转成 Markdown。

⚠️ 必须清 PYTHONPATH，否则 /usr/bin/python3(3.9) 会加载 python3.14 venv 的 PIL 而崩：
    env -u PYTHONPATH /usr/bin/python3 脚本/q3_h1_report_extract.py

产出：
    2026年3季度汇报/_汇总/h1报告原文/<分组>__<人名或文件名>.md
    2026年3季度汇报/_汇总/_提取清单.md
"""
import os, sys, re, json

BASE = os.path.expanduser('~/Desktop/Hermes输出-工作类/2026年3季度汇报')
SRC = os.path.join(BASE, '2026半年度汇报')
OUT = os.path.join(BASE, '_汇总', 'h1报告原文')


def clean(s):
    """单元格/文本框文本清洗：去零宽、归并空白，保留换行语义。"""
    if s is None:
        return ''
    s = str(s).replace('\u200b', '').replace('\xa0', ' ')
    s = re.sub(r'[ \t]+', ' ', s)
    return s.strip()


def cell(x):
    """表格单元格 → 单行文本（换行转 <br>，管道符转义，防破坏 MD 表）。"""
    t = clean(x).replace('\n', '<br>').replace('|', '\\|')
    return t if t else '—'


def pptx_to_md(path):
    from pptx import Presentation
    prs = Presentation(path)
    lines = []
    for i, slide in enumerate(prs.slides, 1):
        lines.append('\n---\n')
        lines.append('## Slide %d\n' % i)
        texts, tables, pics = [], [], 0
        for sh in slide.shapes:
            try:
                if getattr(sh, 'has_table', False):
                    tb = sh.table
                    rows = []
                    for r in tb.rows:
                        rows.append('| ' + ' | '.join(cell(c.text) for c in r.cells) + ' |')
                    if rows:
                        ncol = len(tb.columns)
                        sep = '|' + '|'.join(['---'] * ncol) + '|'
                        tables.append('\n'.join([rows[0], sep] + rows[1:]))
                elif sh.shape_type == 13:
                    pics += 1
                elif sh.has_text_frame:
                    t = clean(sh.text_frame.text)
                    if t:
                        texts.append(t)
                if sh.shape_type == 6:  # group
                    for sub in getattr(sh, 'shapes', []):
                        if getattr(sub, 'has_text_frame', False):
                            t = clean(sub.text_frame.text)
                            if t:
                                texts.append(t)
            except Exception:
                continue
        if texts:
            for t in texts:
                lines.append(t.replace('\n', '  \n') + '\n')
        for tb in tables:
            lines.append('\n' + tb + '\n')
        if pics:
            lines.append('\n> [本页含 %d 张图片/截图，原件见对应 pptx]\n' % pics)
    return '\n'.join(lines)


def xlsx_to_md(path):
    import openpyxl
    wb = openpyxl.load_workbook(path, data_only=True)
    lines = []
    for ws in wb.worksheets:
        lines.append('\n### Sheet: %s（%d 行 × %d 列）\n' % (ws.title, ws.max_row, ws.max_column))
        n = 0
        for row in ws.iter_rows(values_only=True):
            vals = [clean(v) for v in row]
            while vals and vals[-1] == '':
                vals.pop()
            if not vals:
                continue
            lines.append('| ' + ' | '.join(cell(v) for v in vals) + ' |')
            n += 1
            if n >= 120:
                lines.append('| …（超 120 行已截断，完整数据见原表）|')
                break
    return '\n'.join(lines)


def main():
    os.makedirs(OUT, exist_ok=True)
    manifest = []
    files = []
    for root, dirs, fs in os.walk(SRC):
        for f in fs:
            files.append(os.path.join(root, f))
    files.sort()

    for p in files:
        rel = os.path.relpath(p, SRC)
        ext = os.path.splitext(p)[1].lower()
        group = rel.split(os.sep)[0] if os.sep in rel else '根'
        stem = os.path.splitext(os.path.basename(p))[0]
        safe = re.sub(r'[\\/:*?"<>|]', '_', stem)[:60]
        if len(rel.split(os.sep)) > 2:
            safe = re.sub(r'[\\/:*?"<>|]', '_', rel.replace(os.sep, '__'))[:80]
        # ⚠️ 必须把扩展名并进文件名：同一分组下常有同名 .pptx / .xlsx（如赵云浩），
        #    只用 stem 会互相覆盖（实测 xlsx 覆盖了 pptx）。
        outname = '%s__%s__%s.md' % (group, safe, ext.lstrip('.'))

        try:
            if ext == '.pptx':
                md = pptx_to_md(p)
            elif ext in ('.xlsx',):
                md = xlsx_to_md(p)
            elif ext == '.xls':
                md = '> .xls（旧格式）未提取，需 xlrd；原件：%s\n' % rel
            elif ext == '.pdf':
                md = '> PDF 未提取（产品说明书类，原件：%s）\n' % rel
            else:
                continue
        except Exception as e:
            md = '> ⚠️ 提取失败: %s\n' % e

        hdr = '# %s\n\n- 来源: `%s`\n- 大小: %.1f KB\n- 段落数: %d\n' % (
            stem, rel, os.path.getsize(p) / 1024, md.count('\n'))
        with open(os.path.join(OUT, outname), 'w', encoding='utf-8') as fh:
            fh.write(hdr + '\n' + md)
        manifest.append((group, stem, rel, len(md)))
        print('  ✅ %-46s %6d chars' % (outname[:46], len(md)))

    with open(os.path.join(BASE, '_汇总', '_提取清单.md'), 'w', encoding='utf-8') as fh:
        fh.write('# 半年度汇报提取清单\n\n| 分组 | 文件 | 原路径 | 字符数 |\n|---|---|---|---|\n')
        for g, s, r, n in manifest:
            fh.write('| %s | %s | `%s` | %d |\n' % (g, s, r, n))
    print('\n  共 %d 份 → %s' % (len(manifest), OUT))


if __name__ == '__main__':
    main()