#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""盘点「宝锐产品资料/验证数据」：按 SKU × 验证类型建覆盖矩阵。

输出：/Users/liuxinyuan/Desktop/Hermes输出-工作类/数据分析/验证数据盘点_2026-10-24.xlsx
用法：python3 脚本/inventory_validation_data.py [根目录]
"""
import os
import re
import sys
from collections import Counter, defaultdict

import openpyxl
from openpyxl.styles import Alignment, Font, PatternFill
from openpyxl.utils import get_column_letter

ROOT = sys.argv[1] if len(sys.argv) > 1 else '/Users/liuxinyuan/Desktop/hermas输入-工作台数据库/宝锐产品资料/验证数据'
OUT = '/Users/liuxinyuan/Desktop/Hermes输出-工作类/数据分析/验证数据盘点_2026-10-24.xlsx'

TYPES = [
    ('冻融稳定性', ['冻融']),
    ('加速稳定性', ['加速', '55度', '45度', '37℃', '37度']),
    ('运输稳定性', ['运输', '物流', '航空']),
    ('效期/实时', ['效期', '实时稳定', '长期']),
    ('性能/扩增数据', ['快速扩增', '检出率', '灵敏度', '直扩', '扩增结果', '对比', '分型']),
    ('产品资料册', ['产品资料册', '说明书']),
]


def classify(name):
    low = name.lower()
    for t, kws in TYPES:
        if any(k.lower() in low for k in kws):
            return t
    return '其他'


rows = []
for dp, _, fs in os.walk(ROOT):
    for f in fs:
        if f.startswith('.') or not f.lower().endswith(('.pdf', '.docx', '.doc', '.xlsx')):
            continue
        p = os.path.join(dp, f)
        sku = os.path.relpath(dp, ROOT).split(os.sep)[0]
        if sku == '.':
            sku = '(根目录)'
        y = re.findall(r'(?<!\d)(20(?:1[0-9]|2[0-6]))(?!\d)', f)
        year = y[0] if y else ''
        st = os.stat(p)
        rows.append({'sku': sku, 'file': f, 'type': classify(f), 'year': year,
                     'size': st.st_size, 'blocks': st.st_blocks})

rows.sort(key=lambda r: (r['sku'], r['type'], r['file']))
skus = sorted({r['sku'] for r in rows})
types = [t for t, _ in TYPES] + ['其他']

wb = openpyxl.Workbook()
ws = wb.active
ws.title = '覆盖矩阵'
H = ['SKU', '文件数'] + types + ['最早年份', '最晚年份']
ws.append(H)
for sku in skus:
    rs = [r for r in rows if r['sku'] == sku]
    c = Counter(r['type'] for r in rs)
    years = sorted({r['year'] for r in rs if r['year']})
    ws.append([sku, len(rs)] + [c.get(t, 0) for t in types] + [years[0] if years else '', years[-1] if years else ''])
HF = PatternFill('solid', fgColor='1F4E79')
for cell in ws[1]:
    cell.font = Font(color='FFFFFF', bold=True)
    cell.fill = HF
    cell.alignment = Alignment(horizontal='center', wrap_text=True)
ws.freeze_panes = 'B2'
ws.column_dimensions['A'].width = 22
for i in range(2, len(H) + 1):
    ws.column_dimensions[get_column_letter(i)].width = 11

ws2 = wb.create_sheet('文件清单')
ws2.append(['SKU', '文件', '类型', '年份', '大小(MB)', '是否落地'])
for r in rows:
    ws2.append([r['sku'], r['file'], r['type'], r['year'], round(r['size'] / 1e6, 2),
                '是' if r['blocks'] > 0 else '否'])
for cell in ws2[1]:
    cell.font = Font(color='FFFFFF', bold=True)
    cell.fill = HF
ws2.freeze_panes = 'A2'
for col, w in zip('ABCDEF', [20, 70, 14, 8, 10, 9]):
    ws2.column_dimensions[col].width = w

ws3 = wb.create_sheet('缺口')
ws3.append(['缺口类型', 'SKU 数', 'SKU 列表'])
def gap(pred, label):
    miss = [s for s in skus if not any(r['sku'] == s and pred(r) for r in rows)]
    ws3.append([label, len(miss), '、'.join(miss)])
gap(lambda r: r['type'] == '冻融稳定性', '无冻融稳定性资料')
gap(lambda r: r['type'] == '加速稳定性', '无加速稳定性资料')
gap(lambda r: r['type'] == '运输稳定性', '无运输稳定性资料')
gap(lambda r: r['type'] == '效期/实时', '无效期/实时稳定性资料')
gap(lambda r: r['type'] == '性能/扩增数据', '无性能/扩增数据')
for cell in ws3[1]:
    cell.font = Font(color='FFFFFF', bold=True)
    cell.fill = HF
for col, w in zip('ABC', [24, 8, 110]):
    ws3.column_dimensions[col].width = w

os.makedirs(os.path.dirname(OUT), exist_ok=True)
wb.save(OUT)
print('SKU %d ｜文件 %d ｜输出 %s' % (len(skus), len(rows), OUT))
print('类型分布', Counter(r['type'] for r in rows).most_common())
print('年份分布', Counter(r['year'] for r in rows).most_common())