#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""转产优先级：研发承接货号（分子试剂应用研究部 / 分子试剂产品部）→ 试剂生产部

判定逻辑：研发部门承接早期交付；当出现「多客户 + 复购 + 持续月份 + 规模」时应尽快转产。
评分（0–100，越大越急）：
  规模 50%   2026 销售额（百分位）
  复购 20%   复购客户数（同一客户 ≥2 张订单，百分位）
  通用 20%   采购客户数（百分位）
  持续 15%   有销售的月份数（百分位）
  研发依赖度仅作门槛（≥50%），不计入分数
门槛：销售额 ≥ 3 万元；研发占比 ≥ 50%。
"""
import datetime as dt
import json
import math
import os
import statistics
from collections import defaultdict

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

W = '/Users/liuxinyuan/Desktop/Hermes输出-工作类'
OUTDIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
P = '/Users/liuxinyuan/Desktop/hermas输入-工作台数据库/月度销售额数据/销售订单_20260101-20261020.xlsx'
RD = ('分子试剂应用研究部', '分子试剂产品部')
PROD = '试剂生产部'
EXCL = ('宝锐', '宝泰', '横琴')
MIN_AMT = 30000
RECENT_FROM = '2026-08'

sku = defaultdict(lambda: {'amt': 0.0, 'orders': set(), 'cust_orders': defaultdict(set),
                           'cust_amt': defaultdict(float), 'dept': defaultdict(float),
                           'months': set(), 'recent': 0.0, 'names': set(), 'last': None})
total = 0.0
wb = openpyxl.load_workbook(P, read_only=True, data_only=True)
ws = wb.active
rows = ws.iter_rows(values_only=True)
H = {h: i for i, h in enumerate([str(x).strip() for x in next(rows)])}
for r in rows:
    if str(r[H['单据类型']] or '') != '标准销售订单' or str(r[H['单据状态']] or '') != '已审核':
        continue
    if str(r[H['是否赠品']] or '') == '是':
        continue
    cust = str(r[H['客户']] or '')
    if any(k in cust for k in EXCL):
        continue
    code = str(r[H['货号#']] or '').strip()
    if not code:
        continue
    try:
        a = float(r[H['价税合计']] or 0)
    except Exception:
        a = 0.0
    d = r[H['合同日期']]
    if isinstance(d, dt.datetime):
        d = d.date()
    elif not isinstance(d, dt.date):
        try:
            d = dt.datetime.strptime(str(d)[:10], '%Y-%m-%d').date()
        except Exception:
            d = None
    mon = d.strftime('%Y-%m') if d else ''
    x = sku[code]
    x['amt'] += a
    x['orders'].add(str(r[H['单据编号']] or ''))
    x['cust_orders'][cust].add(str(r[H['单据编号']] or ''))
    x['cust_amt'][cust] += a
    x['dept'][str(r[H['备货部门']] or '未填')] += a
    x['names'].add(str(r[H['物料名称']] or '').strip())
    if mon:
        x['months'].add(mon)
        x['last'] = d if x['last'] is None or d > x['last'] else x['last']
        if mon >= RECENT_FROM:
            x['recent'] += a
    total += a

cands = []
for code, x in sku.items():
    if x['amt'] < MIN_AMT:
        continue
    rd_amt = sum(v for k, v in x['dept'].items() if k in RD)
    share = rd_amt / x['amt'] if x['amt'] else 0
    if share < 0.5:
        continue
    rep = sum(1 for v in x['cust_orders'].values() if len(v) >= 2)
    cands.append({'code': code, 'name': sorted(x['names'])[0][:52] if x['names'] else '',
                  'amt': x['amt'], 'share': share, 'orders': len(x['orders']),
                  'custs': len(x['cust_orders']), 'rep': rep, 'months': len(x['months']),
                  'recent': x['recent'], 'last': str(x['last'] or ''),
                  'dept': dict(x['dept']),
                  'top_cust': sorted(x['cust_amt'].items(), key=lambda kv: -kv[1])[:3]})

# 归一化：百分位排名（0–100），避免个别长尾把小货号抬到头部
def prank(vals):
    order = sorted(range(len(vals)), key=lambda i: vals[i])
    rank = [0] * len(vals)
    for pos, i in enumerate(order):
        rank[i] = pos / (len(vals) - 1) * 100 if len(vals) > 1 else 100
    return rank

p_amt = prank([c['amt'] for c in cands])
p_rep = prank([c['rep'] for c in cands])
p_cus = prank([c['custs'] for c in cands])
p_mon = prank([c['months'] for c in cands])
for i, c in enumerate(cands):
    c['score'] = round(0.50 * p_amt[i] + 0.20 * p_rep[i] + 0.15 * p_cus[i] + 0.15 * p_mon[i], 1)
cands.sort(key=lambda c: -c['score'])
top = cands[:10]


def action(c):
    if c['custs'] >= 3 and c['rep'] >= 2 and c['months'] >= 4:
        return '立即转产（多客户+复购+持续出货）'
    if c['rep'] >= 1 and c['months'] >= 3:
        return '试产验证后转产（已有复购，先做批次一致性）'
    if c['months'] >= 2:
        return '列入转产计划（连续出货，复购待观察）'
    return '观察（一次性/单客户，暂不转）'


for c in cands:
    c['action'] = action(c)

wb2 = openpyxl.Workbook()
HF = PatternFill('solid', fgColor='1F4E79')
HFONT = Font(color='FFFFFF', bold=True, size=10)


def style(ws, heads, widths, data, money=(), pct=()):
    for i, h in enumerate(heads, 1):
        cell = ws.cell(row=1, column=i, value=h)
        cell.fill = HF; cell.font = HFONT
        cell.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)
        ws.column_dimensions[get_column_letter(i)].width = widths[i - 1]
    for ri, row in enumerate(data, 2):
        for ci, v in enumerate(row, 1):
            cell = ws.cell(row=ri, column=ci, value=v)
            if ci in money:
                cell.number_format = '#,##0.00'
            if ci in pct:
                cell.number_format = '0.0"%"'
    ws.freeze_panes = 'A2'


ws1 = wb2.active; ws1.title = '转产TOP10'
d1 = []
for i, c in enumerate(top, 1):
    d1.append([i, c['code'], c['name'], round(c['amt'], 2), c['score'], round(c['share'] * 100, 1),
               c['custs'], c['rep'], c['orders'], c['months'], round(c['recent'], 2),
               c['last'], '、'.join(k[:10] for k, _ in c['top_cust']), c['action']])
style(ws1, ['排名', '货号', '品名', '2026销售额(元)', '转产紧迫度', '研发部门占比%', '客户数', '复购客户数',
            '订单数', '有销月份', '近3月销售额(元)', '最近成交', '主要客户', '建议动作'],
      [6, 22, 44, 15, 11, 12, 8, 10, 8, 9, 15, 11, 30, 34], d1, money=(4, 11), pct=(6,))

ws2 = wb2.create_sheet('候选取数明细')
d2 = []
for i, c in enumerate(cands, 1):
    d2.append([i, c['code'], c['name'], round(c['amt'], 2), c['score'], round(c['share'] * 100, 1),
               c['custs'], c['rep'], c['orders'], c['months'], round(c['recent'], 2),
               c['dept'].get('分子试剂应用研究部', 0), c['dept'].get('分子试剂产品部', 0),
               c['dept'].get('试剂生产部', 0), c['action']])
style(ws2, ['排名', '货号', '品名', '销售额(元)', '紧迫度', '研发占比%', '客户数', '复购客户数', '订单数', '有销月份',
            '近3月销售额', '应用研究部金额', '产品部金额', '已转生产部金额', '建议'],
      [6, 22, 40, 14, 9, 10, 8, 10, 8, 9, 14, 15, 14, 15, 32], d2, money=(4, 11, 12, 13, 14), pct=(6,))

ws3 = wb2.create_sheet('口径与评分')
notes = [
    ['项目', '说明'],
    ['数据源', '销售订单_20260101-20261020.xlsx（26,301 行，截至 2026-10-20）'],
    ['口径', '标准销售订单 + 已审核 + 非赠品 + 剔除宝锐/宝泰/横琴；金额=价税合计'],
    ['候选范围', '备货部门为「分子试剂应用研究部 / 分子试剂产品部」的金额占比 ≥50%，且 2026 销售额 ≥3 万元'],
    ['复购客户数', '同一客户对该货号有 ≥2 张有效订单'],
    ['有销月份', '2026 年内该货号出现销售的月份数（衡量是否持续，而非一次性）'],
    ['近3月', '2026-08-01 ~ 2026-10-20'],
    ['评分权重', '规模 50% + 复购客户 20% + 客户数 20% + 有销月份 15%（各因子取百分位 0–100 后加权）；研发占比仅作门槛 ≥50%'],
    ['候选数', '%d 个货号，合计 %.2f 万元' % (len(cands), sum(c['amt'] for c in cands) / 1e4)],
    ['注意', 'A3 等品名泛化的编码可能为汇总码/套件码，转产前需先核实 BOM 与命名'],
]
for i, row in enumerate(notes, 1):
    for j, v in enumerate(row, 1):
        cell = ws3.cell(row=i, column=j, value=v)
        if i == 1:
            cell.fill = HF; cell.font = HFONT
ws3.column_dimensions['A'].width = 14
ws3.column_dimensions['B'].width = 108

wb2.save(os.path.join(OUTDIR, '转产优先级_TOP10_2026-10-21.xlsx'))
json.dump({'total': round(total, 2), 'cands': [{k: v for k, v in c.items() if k != 'dept'} | {'dept': c['dept']} for c in cands[:60]]},
          open(os.path.join(OUTDIR, '数据', 'transfer_top.json'), 'w'), ensure_ascii=False, indent=1, default=str)
print('候选 %d 个｜合计 %.2f 万' % (len(cands), sum(c['amt'] for c in cands) / 1e4))
for i, c in enumerate(top, 1):
    print('%2d %-22s %10.0f 分%5.1f 客户%d 复购%d 月%d %s' % (
        i, c['code'], c['amt'], c['score'], c['custs'], c['rep'], c['months'], c['action'][:14]))
print('✅', os.path.join(OUTDIR, '转产优先级_TOP10_2026-10-21.xlsx'))