#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""2026 Q4 备货预测（大客户部：刘子研 / 赵云浩）— v2（用户 2026-10-24 拍板口径）

拍板项：
  1) M0181-4 只备半成品、不备成品 → 新增「备货形态」列
  2) 客户质量要求取自工作台「客户特殊要求」库（数据/customer_special_requirements.json）
  3) 门槛：90 天金额 ≥ 5 万元
  4) 保留模板里手填的 11 行，不覆盖
其余口径：90 天（07-27~10-24）、标准订单已审核非赠品、68 天预测、1.2 安全系数。
"""
import datetime as dt
import json
import math
import os
import statistics
import sys
from collections import Counter, defaultdict

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

ROOT = '/Users/liuxinyuan/Desktop/Hermes输出-工作类'
sys.path.insert(0, os.path.join(ROOT, '脚本'))
import erp_sales_core as core

TEMPLATE = os.path.expanduser('~/.hermes/profiles/zone-b/cache/documents/doc_60b9fadb6e73_备货及转产预测2026Q4-大客户部.xlsx')
OUTDIR = os.path.join(ROOT, '经营分析', '产销', '备货预测_2026Q4_大客户部')
OUT = os.path.join(OUTDIR, '备货及转产预测2026Q4-大客户部_预测版.xlsx')
MIN_AMT = 50000
START, END = dt.date(2026, 7, 27), dt.date(2026, 10, 24)
FSTART, FEND = dt.date(2026, 10, 25), dt.date(2026, 12, 31)
DAYS = (FEND - FSTART).days + 1
SAFE = 1.2
RD = ('分子试剂应用研究部', '分子试剂产品部', '基因工程部')
FORM = {'M0181-4': '半成品（不备成品）'}   # 用户指定

REQ = json.load(open(os.path.join(ROOT, '数据', 'customer_special_requirements.json'), encoding='utf-8'))['items']
person_dept, lzy = core.load_mappings()

p = core.find_latest_erp()
wb = openpyxl.load_workbook(p, 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)]
ix = {h: j for j, h in enumerate(H)}

g = defaultdict(lambda: {'q': 0.0, 'n': 0, 'amt': 0.0, 'd': [], 'spec': '', 'name': '',
                         'dept': Counter()})
for r in it:
    if str(r[ix['单据类型']]) != '标准销售订单' or str(r[ix['单据状态']]) != '已审核':
        continue
    if str(r[ix['是否赠品']] or '') == '是':
        continue
    cust = str(r[ix['客户']] or '')
    if any(k in cust for k in ('宝锐', '宝泰', '横琴')):
        continue
    pt = str(r[ix['产品类型#']] or '') if '产品类型#' in ix else ''
    person, _ = core.map_person(str(r[ix['销售员']] or ''), cust, person_dept, lzy, pt)
    if person not in ('刘子研', '赵云浩'):
        continue
    d = r[ix['合同日期']]
    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:
            continue
    if not (START <= d <= END):
        continue
    code = str(r[ix['货号#']] or '').strip()
    if not code:
        continue
    unit = str(r[ix['销售单位']] or '')
    x = g[(person, cust, code, unit)]
    x['q'] += float(r[ix['销售数量']] or 0)
    x['n'] += 1
    x['amt'] += float(r[ix['价税合计']] or 0)
    x['d'].append(d)
    x['name'] = str(r[ix['物料名称']] or '')[:46]
    sp = str(r[ix['客户物料规格型号#']] or '').strip()
    if sp:
        x['spec'] = sp
    x['dept'][str(r[ix['备货部门']] or '')] += 1


def short(n):
    for suf in ('股份有限公司', '有限责任公司', '有限公司', '（中国）'):
        if n.endswith(suf):
            return n[:-len(suf)]
    return n


def round_up(v, unit):
    step = 100.0 if unit.startswith('T') else (10.0 if unit == 'ml' else 1.0)
    return math.ceil(v / step) * step


def next_date(ds):
    ds = sorted(ds)
    if len(ds) < 2:
        return dt.date(2026, 12, 30)
    gaps = [(ds[i + 1] - ds[i]).days for i in range(len(ds) - 1)]
    nd = ds[-1] + dt.timedelta(days=int(statistics.median(gaps) or 30))
    return min(max(nd, FSTART + dt.timedelta(days=7)), FEND)


def _match_cust(item, cust):
    c = str(item.get('customer') or '')
    cu = cust.replace('（', '(').replace('）', ')')
    return bool(c) and (c[:8] in cu or cu[:8] in c)


def _sku_hit(item, code):
    sk = [str(x) for x in (item.get('skus') or [])]
    if not sk:
        return None          # 客户级
    for x in sk:
        if code == x or code.startswith(x + '-') or x == code.split('-')[0]:
            return True
    return False


def _fmt(items, prefix=''):
    order = {'包装/标签': 0, '质检/文件': 1, '交付/物流': 2, '配方/体系': 3, '搭配规则': 4, '价格/账期': 5, '其他': 6}
    items.sort(key=lambda x: (order.get(x.get('category'), 9), -len(str(x.get('content') or ''))))
    parts = []
    for it in items[:2]:
        t = str(it.get('title') or '').strip()
        v = str(it.get('content') or '').strip().replace('\n', ' ')[:28]
        parts.append(('%s：%s' % (t, v)) if t and t not in v else v)
    return (prefix + '；'.join(parts))[:90]


def reqs(cust, code):
    spec, cust_lv = [], []
    for it in REQ:
        if str(it.get('status') or 'active') != 'active' or not _match_cust(it, cust):
            continue
        hit = _sku_hit(it, code)
        if hit:
            spec.append(it)
        elif hit is None:
            cust_lv.append(it)
    use, prefix = (spec, '') if spec else (cust_lv, '客户级：')
    q = [x for x in use if x.get('category') == '质检/文件']
    other = [x for x in use if x.get('category') != '质检/文件']
    return (_fmt(q) or '无记录'), (_fmt(other, prefix) or '无特殊要求记录')


rows = []
for (person, cust, code, unit), v in g.items():
    if v['amt'] < MIN_AMT:
        continue
    monthly = v['q'] / 3.0
    demand = monthly * DAYS / 30.4
    rows.append({'person': person, 'cust': cust, 'cust_s': short(cust), 'code': code, 'unit': unit,
                 'name': v['name'], 'spec': v['spec'], 'q90': v['q'], 'orders': v['n'],
                 'amt90': v['amt'], 'monthly': monthly, 'demand': demand,
                 'stock': round_up(demand * SAFE, unit), 'date': next_date(v['d']),
                 'last': max(v['d']), 'dept': v['dept'].most_common(1)[0][0],
                 'rd': sum(c for d, c in v['dept'].items() if d in RD) / max(v['n'], 1)})
rows.sort(key=lambda r: -r['amt90'])

wbt = openpyxl.load_workbook(TEMPLATE)
s1, s2 = wbt['季度拿货预测'], wbt['季度转产建议']
HF, HFONT = PatternFill('solid', fgColor='1F4E79'), Font(color='FFFFFF', bold=True, size=10)
manual1 = [[c.value for c in row] for row in s1.iter_rows(min_row=2, max_row=s1.max_row) if row[1].value]
manual2 = [[c.value for c in row] for row in s2.iter_rows(min_row=2, max_row=s2.max_row) if row[1].value]
s1.delete_rows(2, s1.max_row)
s2.delete_rows(2, s2.max_row)

H1 = ['销售员', '货号', '客户要求规格', '客户质量要求', '订单特殊要求', '备货形态',
      '季度需求量预测\n（T或ml）', '关联客户', '预测拿货日期']
H2 = ['销售员', '货号', '客户要求分装规格', '客户质量要求', '订单特殊要求', '备货形态',
      '季度需求量预测\n（T或ml）', '关联客户', '关联商机+金额\n（万元/年）',
      '建议最晚完成转产日期', '排序']
for sh, heads in ((s1, H1), (s2, H2)):
    for c, h in enumerate(heads, 1):
        cell = sh.cell(row=1, column=c, value=h)
        cell.fill = HF
        cell.font = HFONT
        cell.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)
    for c in range(len(heads) + 1, 13):
        sh.cell(row=1, column=c, value=None)


def add(sh, vals):
    sh.append(vals)
    for c in range(1, len(vals) + 1):
        sh.cell(row=sh.max_row, column=c).alignment = Alignment(vertical='center', wrap_text=True)


def full_cust(sn):
    sn = str(sn or '')
    for r in rows:
        if sn and (sn in r['cust'] or r['cust_s'] == sn):
            return r['cust']
    return str(sn or '')


# 手填行（保留，只补质量要求/形态）
for m in manual1:
    q, _ = reqs(full_cust(m[5]), m[1])
    q = m[3] or q          # 手填的质量要求优先，不被覆盖
    add(s1, [m[0], m[1], m[2], q, m[3] if len(m) > 3 else '', FORM.get(m[1], '成品'),
             m[4], m[5], m[6]])
seen = {(m[0], m[1]) for m in manual1}
for r in rows:
    if (r['person'], r['code']) in seen:
        continue
    q, sp = reqs(r['cust'], r['code'])
    val = '{:,.0f}{}'.format(r['stock'], 'T' if r['unit'].startswith('T') else r['unit'])
    add(s1, [r['person'], r['code'], r['spec'] or '待销售确认（历史按 %s 计价）' % r['unit'],
             q, sp, FORM.get(r['code'], '成品'), val, r['cust_s'], r['date'].strftime('%Y%m%d')])
    seen.add((r['person'], r['code']))

seen2 = {(m[0], m[1]) for m in manual2}
for m in manual2:
    q, _ = reqs(full_cust(m[5]), m[1])
    q = m[3] or q
    add(s2, [m[0], m[1], m[2], q, m[3] if len(m) > 3 else '', FORM.get(m[1], '成品'),
             m[4], m[5], m[6], m[7], m[8]])
for r in rows:
    if r['rd'] < 0.5 or (r['person'], r['code']) in seen2:
        continue
    q, sp = reqs(r['cust'], r['code'])
    val = '{:,.0f}{}'.format(r['stock'], 'T' if r['unit'].startswith('T') else r['unit'])
    add(s2, [r['person'], r['code'], r['spec'] or '待销售确认（历史按 %s 计价）' % r['unit'],
             q, sp, FORM.get(r['code'], '成品'), val, r['cust_s'],
             round(r['amt90'] / 298 * 365 / 1e4, 1),
             max(dt.date(2026, 11, 15), r['date'] - dt.timedelta(days=30)).strftime('%Y%m%d'),
             len(seen2) + 1])
    seen2.add((r['person'], r['code']))

s3 = wbt.create_sheet('测算依据')
s3.append(['销售员', '关联客户', '货号', '品名', '单位', '90天数量', '订单数', '90天金额(元)',
           '月均', 'Q4剩余需求', '建议备货', '备货形态', '最近下单', '备货部门'])
for r in rows:
    q, sp = reqs(r['cust'], r['code'])
    s3.append([r['person'], r['cust_s'], r['code'], r['name'], r['unit'], round(r['q90'], 1),
               r['orders'], round(r['amt90'], 2), round(r['monthly'], 1), round(r['demand'], 1),
               r['stock'], FORM.get(r['code'], '成品'), r['last'].strftime('%Y-%m-%d'), r['dept']])
for c in s3[1]:
    c.fill = HF
    c.font = HFONT
s3.freeze_panes = 'A2'
for col, w in zip('ABCDEFGHIJKLMN', [9, 22, 26, 34, 9, 11, 8, 13, 10, 12, 11, 16, 11, 18]):
    s3.column_dimensions[col].width = w

os.makedirs(OUTDIR, exist_ok=True)
wbt.save(OUT)
json.dump({'min_amt': MIN_AMT, 'form_override': FORM,
           'rows': [{k: (str(v) if isinstance(v, (dt.date, dt.datetime)) else v) for k, v in r.items()} for r in rows]},
          open(os.path.join(OUTDIR, '数据', 'q4_forecast_da.json'), 'w', encoding='utf-8'),
          ensure_ascii=False, indent=0)
print('门槛 %.0f 万｜计算行 %d｜拿货表 %d（手填 %d）｜转产表 %d（手填 %d）' % (
    MIN_AMT / 1e4, len(rows), s1.max_row - 1, len(manual1), s2.max_row - 1, len(manual2)))
for r in rows[:10]:
    print('  %-4s %-14s %-26s %10s' % (r['person'], r['cust_s'][:14], r['code'],
                                       '{:,.0f}{}'.format(r['stock'], 'T' if r['unit'].startswith('T') else r['unit'])))
print('✅', OUT)