#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""在《附件3-货号验证执行计划》上新增两张可执行表（原两 Sheet 不动）：
  ① 执行台账：一货号一行，补验证类型/已有资料/缺失类型/销售额/优先级/验收标准
  ② Q4 冲刺：12 个货号 + 责任人 + 起止月 + 验收标准

输出：同目录 *_可执行版.xlsx
口径：
  · 2026 销售额 = ERP 2026-01-01~10-22，按货号精确或前缀汇总（含税）
  · 已有资料 = 数据/validation_index.json（56 个系列目录、165 份 PDF）按系列前缀匹配
  · 原表左栏（货号/完成情况）与右栏（项目类型/技术方法）行不对应，故不强行配对，标「待补」
"""
import json
import os
import re
import shutil
from collections import Counter, defaultdict

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

SRC = '/Users/liuxinyuan/.hermes/profiles/zone-b/cache/documents/doc_8fae84623120_2026-10-24-附件3-货号验证执行计划.xlsx'
DST = '/Users/liuxinyuan/Desktop/Hermes输出-工作类/数据分析/2026-10-24-附件3-货号验证执行计划_可执行版.xlsx'
IDX = '/Users/liuxinyuan/Desktop/Hermes输出-工作类/数据/validation_index.json'
ERP = '/Users/liuxinyuan/Desktop/hermas输入-工作台数据库/月度销售额数据/销售订单_20260101-20261022.xlsx'

shutil.copy2(SRC, DST)

# ---------- 1. 原表数据 ----------
wb0 = openpyxl.load_workbook(SRC, data_only=True)
ws1 = wb0['货号来源']
status = {}
for r in ws1.iter_rows(min_row=3, values_only=True):
    code, st = r[1], r[2]
    if code:
        c = str(code).split('\n')[0].strip()
        c = re.sub(r'（.*?）|\(.*?\)', '', c).strip()
        if c:
            status[c] = str(st).strip() if st else ''

ws2 = wb0['计划甘特图']
plan = []
for r in ws2.iter_rows(min_row=3, values_only=True):
    if r[0]:
        plan.append({'code': str(r[0]).strip(), 'owner': str(r[1] or '').strip()})

# ---------- 2. 验证资料覆盖 ----------
idx = json.load(open(IDX, encoding='utf-8'))
series = {}
for x in idx:
    f = x['file']
    t = ('冻融' if '冻融' in f else '加速' if ('加速' in f or '55度' in f or '45度' in f or '37' in f)
         else '运输' if ('运输' in f or '物流' in f or '航空' in f)
         else '效期' if ('效期' in f or '实时稳定' in f or '长期' in f)
         else '性能' if any(k in f for k in ('扩增', '检出率', '灵敏度', '直扩', '对比', '分型'))
         else '其他')
    series.setdefault(x['sku'].upper(), Counter())[t] += 1

# ---------- 3. 2026 销售 ----------
wb = openpyxl.load_workbook(ERP, data_only=True, read_only=True)
ws = wb.worksheets[0]
it = ws.iter_rows(values_only=True)
H = [str(x or '').strip() for x in next(it)]
i = {h: j for j, h in enumerate(H)}
sales = defaultdict(float)
custs = defaultdict(set)
for r in it:
    code = str(r[i['货号#']] or '').strip()
    if not code:
        continue
    a = float(r[i['销售数量']] or 0) * float(r[i['含税单价']] or 0)
    sales[code] += a
    custs[code].add(str(r[i['客户']] or ''))


def amt(code):
    base = code.rstrip('-')
    tot = 0.0
    cs = set()
    for k, v in sales.items():
        if k == base or k.startswith(base + '-') or k.upper() == base.upper():
            tot += v
            cs |= custs[k]
    return tot, len(cs)


def series_key(code):
    m = re.match(r'([A-Za-z]+\d+(?:-\d+)?)', code)
    return m.group(1).upper() if m else code.upper()


rows = []
for p in plan:
    code = p['code']
    a, ncust = amt(code)
    sk = series_key(code)
    cov = series.get(sk, Counter())
    miss = [t for t in ('效期', '运输', '加速', '冻融') if cov.get(t, 0) == 0]
    st = status.get(code, '') or status.get(code.rstrip('-'), '')
    rows.append({**p, 'amt': a, 'cust': ncust, 'series': sk,
                 'cov': dict(cov), 'miss': miss, 'status': st})

# 优先级：销售 50 + 缺效期 25 + 缺运输 15 + 未启动 10
maxa = max((r['amt'] for r in rows), default=1) or 1
for r in rows:
    r['score'] = round(50 * r['amt'] / maxa
                       + (25 if '效期' in r['miss'] else 0)
                       + (15 if '运输' in r['miss'] else 0)
                       + (10 if not r['status'] else 0), 1)
rows.sort(key=lambda r: -r['score'])

# ---------- 4. 写两张新表 ----------
wbx = openpyxl.load_workbook(DST)
HF = PatternFill('solid', fgColor='1F4E79')
HFONT = Font(color='FFFFFF', bold=True, size=10)


def head(ws, cols, widths):
    for j, (h, w) in enumerate(zip(cols, widths), 1):
        c = ws.cell(row=1, column=j, value=h)
        c.fill = HF
        c.font = HFONT
        c.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)
        ws.column_dimensions[get_column_letter(j)].width = w
    ws.freeze_panes = 'C2'


t1 = wbx.create_sheet('执行台账')
head(t1, ['序号', '系列', '货号', '责任人', '2026销售额(元)', '客户数', '已有资料(冻融/加速/运输/效期)',
          '缺失类型', '原表状态', '优先级分', '建议验证类型', '计划起止', '验收标准', '备注'],
     [6, 14, 22, 9, 14, 8, 22, 18, 14, 9, 16, 12, 34, 26])
for n, r in enumerate(rows, 1):
    cov = r['cov']
    covs = '%d/%d/%d/%d' % (cov.get('冻融', 0), cov.get('加速', 0), cov.get('运输', 0), cov.get('效期', 0))
    vtype = '稳定性补件（效期+运输）' if ('效期' in r['miss'] or '运输' in r['miss']) else '应用性能验证'
    q = 'Q4' if n <= 12 else ('2027H1' if n <= 35 else '2027H2')
    t1.append([n, r['series'], r['code'], r['owner'], round(r['amt'], 2), r['cust'], covs,
               '、'.join(r['miss']) or '无', r['status'] or '未启动', r['score'], vtype, q,
               '报告归档+结论页+原始数据表' if vtype.startswith('稳定性') else '结论页+扩增数据表+批号记录',
               '原表左右栏不对应，项目类型/技术方法待补' if not r['status'] else ''])

t2 = wbx.create_sheet('Q4冲刺')
head(t2, ['序号', '货号', '系列', '责任人', '2026销售额(万)', '缺失类型', '10月', '11月', '12月',
          '交付物', '验收人', '状态'],
     [6, 22, 14, 9, 13, 18, 8, 8, 8, 30, 9, 9])
q4 = rows[:12]
# 产能：叶璐 8 个、刘春芳 4 个（按分数顺位分配，避免一人压满）
cap = {'叶璐': 8, '刘春芳': 4}
used = Counter()
months = ['10月', '11月', '12月']
mi = 0
for n, r in enumerate(q4, 1):
    owner = r['owner'] or '叶璐'
    if used[owner] >= cap.get(owner, 0):
        owner = '刘春芳' if owner == '叶璐' else '叶璐'
    used[owner] += 1
    slot = months[min(2, (used[owner] - 1) // 2)]
    marks = {m: ('●' if m == slot else '') for m in months}
    t2.append([n, r['code'], r['series'], owner, round(r['amt'] / 1e4, 1),
               '、'.join(r['miss']) or '无', marks['10月'], marks['11月'], marks['12月'],
               '稳定性报告（效期/运输）' if ('效期' in r['miss'] or '运输' in r['miss']) else '应用性能验证报告',
               '质量部', '待启动'])

wbx.save(DST)
print('执行台账 %d 行｜Q4 冲刺 %d 行' % (len(rows), len(q4)))
print('Q4 名单：', '、'.join('%s(%s)' % (r['code'], r['owner']) for r in q4))
print('✅', DST)