#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""① 修正城市/区域统计（用文本列）② 导出 DOCX 全部表格数字，供逐项对账。"""
import json
import re
from collections import Counter

import pandas as pd

XL = ('/Users/liuxinyuan/.hermes/profiles/zone-b/cache/documents/'
      'doc_3f5eafe34e61_宝锐生物客户满意度调查问卷-2026（按选项文本）-全部.xlsx')
DX = ('/Users/liuxinyuan/.hermes/profiles/zone-b/cache/documents/'
      'doc_54c8ee1b61aa_宝锐生物客户满意度调研报告-2026（ISO13485现场审核版）.docx')
OUT = '/Users/liuxinyuan/Desktop/Hermes输出-工作类/数据/survey2026_对账_DOCX表格.json'

d = pd.read_excel(XL, sheet_name='答卷数据')
C = list(d.columns)
main = d[d['答卷状态'].astype(str).str.contains('质控通过', na=False)]

print('=== ① 城市（文本列 24.市 / 24.【城市/地址题】）===')
for idx in (117, 116, 115, 114):
    s = main[C[idx]].astype(str).str.strip()
    s = s[(s != '') & (s != 'nan')]
    sample = list(s.head(5))
    print('  [%d] %-26s 非空 %3d ｜ 样例 %s' % (idx, str(C[idx])[:26], len(s), sample))

citycol = 117 if main[C[117]].notna().sum() >= main[C[116]].notna().sum() else 116
s = main[C[citycol]].astype(str).str.strip()
s = s[(s != '') & (s != 'nan')]
cc = Counter(s)
print()
print('=== 城市分布 TOP15（唯一 %d 城）===' % len(cc))
for k, v in cc.most_common(15):
    print('   %-12s %3d (%.1f%%)' % (k, v, 100.0 * v / len(s)))

prov = {'广东省': ['深圳', '广州', '珠海', '东莞', '中山', '佛山', '惠州', '汕头', '江门', '肇庆'],
        '长三角': ['上海', '杭州', '南京', '苏州', '宁波', '无锡', '常州', '合肥', '温州', '嘉兴', '湖州', '绍兴', '金华', '台州', '南通'],
        '京津': ['北京', '天津']}
print()
print('=== 区域汇总（分母 %d）===' % len(s))
tot, used = len(s), 0
for nm, ks in prov.items():
    n = sum(v for k, v in cc.items() if any(x in str(k) for x in ks))
    used += n
    print('   %-6s %3d (%.1f%%)' % (nm, n, 100.0 * n / tot))
print('   %-6s %3d (%.1f%%)' % ('其它', tot - used, 100.0 * (tot - used) / tot))
print('   %-6s %3d' % ('合计', tot))

# 岗位
print()
print('=== 岗位（文本列 113）===')
p = main[C[113]].astype(str).str.strip()
p = p[(p != '') & (p != 'nan')]
for k, v in Counter(p).most_common(8):
    print('   %-14s %3d (%.1f%%)' % (k[:14], v, 100.0 * v / len(p)))

# ② DOCX 表格
print()
print('=== ② DOCX 表格导出 ===')
from docx import Document
doc = Document(DX)
tables = []
for ti, t in enumerate(doc.tables):
    rows = [[c.text.strip() for c in r.cells] for r in t.rows]
    tables.append(rows)
print('  表格数:', len(tables))
json.dump({'tables': tables}, open(OUT, 'w', encoding='utf-8'), ensure_ascii=False, indent=1)
print('  → 已落盘', OUT)
print()
for ti, rows in enumerate(tables[:6]):
    print('  ── 表 %d（%d 行）' % (ti + 1, len(rows)))
    for r in rows[:4]:
        print('     |', ' | '.join(x[:22] for x in r[:6]))