#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""把 xlsx 模板的 20 个产品方向 → 结构化 JSON schema（字段/单位/下拉/必填）

2026-09-15 新增排除规则：跳过「签字/盖章/签名」类纸质表单行
（如「客户确认签字：____ 日期：____ 销售确认：____」），网页表单不需要。

用法: python3 脚本/extract_test_report_schema.py <来源.xlsx>
"""
import openpyxl, re, json, os, sys
from collections import OrderedDict

SRC = sys.argv[1] if len(sys.argv) > 1 else '/tmp/form_v1.xlsx'
OUT = os.path.expanduser('~/Desktop/Hermes输出-工作类/数据/test_report_schema.json')

# 纸质表单专用行，不进网页字段
SKIP_LABEL = re.compile(r'(签字|签章|签名|盖章|签收)')
wb = openpyxl.load_workbook(SRC, data_only=False)

ov = wb['产品清单总览']
meta = OrderedDict()
for r in range(4, ov.max_row + 1):
    name = ov.cell(row=r, column=2).value
    if not name:
        continue
    meta[str(ov.cell(row=r, column=4).value)] = {
        'seq': ov.cell(row=r, column=1).value,
        'name': str(name).strip(),
        'type': str(ov.cell(row=r, column=3).value or '').strip(),
        'sheet': str(ov.cell(row=r, column=4).value),
    }


def dv_map(ws):
    """坐标 → 下拉选项列表"""
    m = {}
    dvs = ws.data_validations.dataValidation if ws.data_validations else []
    for dv in dvs:
        if dv.type != 'list' or not dv.formula1:
            continue
        opts = [o.strip() for o in dv.formula1.strip('"').split(',') if o.strip()]
        for rng in str(dv.sqref).split():
            if ':' in rng:
                c1, c2 = rng.split(':')
                col = re.sub(r'\d', '', c1)
                r1 = int(re.sub(r'\D', '', c1)); r2 = int(re.sub(r'\D', '', c2))
                for rr in range(r1, r2 + 1):
                    for cc in range(openpyxl.utils.column_index_from_string(col),
                                    openpyxl.utils.column_index_from_string(re.sub(r'\d', '', c2)) + 1):
                        m['%s%d' % (openpyxl.utils.get_column_letter(cc), rr)] = opts
            else:
                m[rng] = opts
    return m


def slug(label, i):
    s = re.sub(r'[^0-9A-Za-z\u4e00-\u9fa5]', '', str(label))
    return 'f%02d_%s' % (i, s[:18])


products, skipped = [], []
for sheet, info in meta.items():
    if sheet not in wb.sheetnames:
        print('⚠ 缺表:', sheet)
        continue
    ws = wb[sheet]
    dvm = dv_map(ws)
    sections, cur, n = [], None, 0
    for r in range(3, ws.max_row + 1):
        a = ws.cell(row=r, column=1).value
        if a is None or str(a).strip() == '':
            continue
        a = str(a).strip()
        if re.match(r'^[一二三四五六七八九十]+、', a):
            cur = {'title': a, 'fields': []}
            sections.append(cur)
            continue
        if cur is None:
            continue
        if SKIP_LABEL.search(a):
            skipped.append((sheet, a[:40]))
            continue
        hint = ws.cell(row=r, column=4).value or ws.cell(row=r, column=3).value or ''
        hint = str(hint).strip()
        req = a.startswith('**')
        label = a.lstrip('*').strip()
        n += 1
        opts = dvm.get('B%d' % r, [])
        ftype = 'number' if re.search(r'(ng|μL|uL|mg|μg|ug|%|分钟|bp|kb|G|个|倍|M reads|pg|℃|分钟)', hint) and not opts else ('select' if opts else 'text')
        if re.search(r'(描述|结果|说明|编号|类型|适应性|残留|对比)', label + hint) and not opts:
            ftype = 'text'
        if opts:
            ftype = 'select'
        if re.search(r'(打分|评分|1-5分|1-5 分)', hint) or any(o.startswith('1-') for o in opts):
            ftype = 'rating'
        cur['fields'].append({'key': slug(label, n), 'label': label, 'required': req,
                              'type': ftype, 'unit': hint, 'options': opts})
    products.append({'sheet': sheet, 'name': info['name'], 'type': info['type'],
                     'seq': info['seq'], 'sections': sections})

schema = {'source': os.path.basename(SRC), 'generated': '2026-09-15',
          'product_count': len(products), 'products': products}
os.makedirs(os.path.dirname(OUT), exist_ok=True)
json.dump(schema, open(OUT, 'w', encoding='utf-8'), ensure_ascii=False, indent=1)

total = sum(len(s['fields']) for p in products for s in p['sections'])
print('✅ %s（%.1f KB）｜ 方向 %d ｜ 字段 %d ｜ 跳过纸质行 %d'
      % (OUT, os.path.getsize(OUT) / 1024, len(products), total, len(skipped)))
for s in skipped[:3]:
    print('   跳过示例:', s[0], '→', s[1])