#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""2026 Q4 备货预测（大客户部：刘子研 / 赵云浩）

口径
  · 90 天窗口：2026-07-27 ~ 2026-10-24（ERP 最新件）
  · 只算：标准销售订单 + 已审核 + 非赠品 + 剔除宝锐系
  · 预测期：2026-10-25 ~ 2026-12-31（68 天）
  · 月均 = 90 天数量 / 3；需求 = 月均 × (68/30.4)；备货 = 需求 × 1.2 安全系数
  · 只列 90 天金额 ≥ 2 万元的“客户×货号×规格”，按金额降序
  · 客户要求规格/质量要求：有台账字段就用，没有写“待销售确认”，不编造
输出
  Desktop/Hermes输出-工作类/经营分析/产销/备货预测_2026Q4_大客户部/备货及转产预测2026Q4-大客户部_预测版.xlsx
"""
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 = 20000
START, END = dt.date(2026, 7, 27), dt.date(2026, 10, 24)
FCAST_START, FCAST_END = dt.date(2026, 10, 25), dt.date(2026, 12, 31)
DAYS = (FCAST_END - FCAST_START).days + 1  # 68
SAFE = 1.2
RD = ('分子试剂应用研究部', '分子试剂产品部', '基因工程部')

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

# 订单特殊要求：客户特殊要求库（客户+货号命中）→ 摘要；无则回落到 ERP 备注关键词
def special_req(cust, code):
    """优先货号级要求；没有才用客户级（并标注“客户级”）"""
    cu = cust.replace('（', '(').replace('）', ')')
    spec_hits, cust_hits = [], []
    for it in _REQ:
        c = str(it.get('customer') or '')
        if not c or not (c[:8] in cu or cu[:8] in c):
            continue
        if str(it.get('status') or 'active') != 'active':
            continue
        sk = [str(x) for x in (it.get('skus') or [])]
        if sk:
            hit = False
            for x in sk:
                if code == x or code.startswith(x + '-') or x == code.split('-')[0]:
                    hit = True
                    break
            if hit:
                spec_hits.append(it)
        else:
            cust_hits.append(it)
    order = {'包装/标签': 0, '质检/文件': 1, '交付/物流': 2, '配方/体系': 3, '搭配规则': 4, '价格/账期': 5, '其他': 6}
    use, prefix = (spec_hits, '') if spec_hits else (cust_hits, '客户级：')
    if not use:
        return ''
    # 文本再校验：要求正文/标题必须出现该货号或其系列，避免把同一客户别货号的备注串过来
    import re as _re
    series = code.split('-')[0]
    def _t(it):
        return (str(it.get('title') or '') + ' ' + str(it.get('content') or '')).upper()
    _filtered = [it for it in use if _re.search(r'(?<![A-Z0-9])' + _re.escape(series) + r'(?![A-Z0-9])', _t(it))]
    if _filtered:
        use = _filtered
    use.sort(key=lambda x: (order.get(x.get('category'), 9), -len(str(x.get('content') or ''))))
    parts = []
    for it in use[:2]:
        t = str(it.get('title') or '').strip()
        v = str(it.get('content') or '').strip().replace('\n', ' ')[:26]
        parts.append(('%s：%s' % (t, v)) if t and t not in v else v)
    return (prefix + '；'.join(parts))[:80]


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

# 订单特殊要求：客户特殊要求库（客户+货号命中）→ 摘要；无则回落到 ERP 备注关键词
def special_req(cust, code):
    cu = cust.replace('（', '(').replace('）', ')')
    cands = []
    for it in _REQ:
        c = str(it.get('customer') or '')
        if not c:
            continue
        if not (c[:8] in cu or cu[:8] in c):
            continue
        sk = [str(x) for x in (it.get('skus') or [])]
        if sk and not any(code == x or code.startswith(x + '-') or x.startswith(code.split('-')[0]) for x in sk):
            continue
        if str(it.get('status') or 'active') != 'active':
            continue
        cands.append(it)
    if not cands:
        return ''
    order = {'包装/标签': 0, '质检/文件': 1, '交付/物流': 2, '配方/体系': 3, '搭配规则': 4, '价格/账期': 5, '其他': 6}
    cands.sort(key=lambda x: (order.get(x.get('category'), 9), -len(str(x.get('content') or ''))))
    parts = []
    for it in cands[:3]:
        t = str(it.get('title') or '').strip()
        v = str(it.get('content') or '').strip().replace('\n', ' ')[:26]
        parts.append(('%s：%s' % (t, v)) if t and t not in v else v)
    return '；'.join(parts)[:80]
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(), 'ords': set()})
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 '')
    k = (person, cust, code, unit)
    x = g[k]
    x['q'] += float(r[ix['销售数量']] or 0)
    x['n'] += 1
    x['amt'] += float(r[ix['价税合计']] or 0)
    x['d'].append(d)
    x['ords'].add(str(r[ix['单据编号']] or ''))
    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(name):
    for suf in ('股份有限公司', '有限责任公司', '有限公司', '（中国）'):
        if name.endswith(suf):
            return name[:-len(suf)]
    return name


def round_up(v, unit):
    if unit.startswith('T'):
        step = 100.0
    elif unit == 'ml':
        step = 10.0
    else:
        step = 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)]
    med = statistics.median(gaps) or 30
    nd = ds[-1] + dt.timedelta(days=int(med))
    if nd < FCAST_START:
        nd = FCAST_START + dt.timedelta(days=7)
    return min(nd, FCAST_END)


rows = []
for k, v in g.items():
    person, cust, code, unit = k
    if v['amt'] < MIN_AMT:
        continue
    monthly = v['q'] / 3.0
    demand = monthly * DAYS / 30.4
    stock = round_up(demand * SAFE, unit)
    rd_cnt = sum(c for d, c in v['dept'].items() if d in RD)
    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': stock, 'date': next_date(v['d']),
                 'last': max(v['d']), 'rd_share': rd_cnt / max(v['n'], 1),
                 'dept': v['dept'].most_common(1)[0][0]})
rows.sort(key=lambda r: -r['amt90'])

# ---------- 写模板 ----------
wbt = openpyxl.load_workbook(TEMPLATE)
s1 = wbt['季度拿货预测']
s2 = wbt['季度转产建议']
HF = PatternFill('solid', fgColor='1F4E79')
HFONT = 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]
manual1 = [m[:4] + [''] + m[4:] for m in manual1]
manual2 = [m[:4] + [''] + m[4:] for m in manual2]
for sh in (s1, s2):
    sh.delete_rows(2, sh.max_row)
s1.cell(row=1, column=5, value='订单特殊要求')
s1.cell(row=1, column=6, value='季度需求量预测\n（T或ml）')
s1.cell(row=1, column=7, value='关联客户')
s1.cell(row=1, column=8, value='预测拿货日期')
s2.cell(row=1, column=5, value='订单特殊要求')
s2.cell(row=1, column=6, value='季度需求量预测\n（T或ml）')
s2.cell(row=1, column=7, value='关联客户')
s2.cell(row=1, column=8, value='关联商机+金额\n（万元/年）')
s2.cell(row=1, column=9, value='建议最晚完成转产日期')
s2.cell(row=1, column=10, value='排序')
for sh in (s1, s2):
    for c in range(9, 12):
        sh.cell(row=1, column=c, value=None)
for sh in (s1, s2):
    for c in range(1, 10):
        cell = sh.cell(row=1, column=c)
        cell.fill = HF; cell.font = HFONT
        cell.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)

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

# 手填优先（用短名反查全称补“订单特殊要求”）
def full_cust(short_name):
    sn = str(short_name or '')
    if not sn:
        return ''
    for r in rows:
        if sn in r['cust'] or r['cust_s'] == sn:
            return r['cust']
    return sn

seen = set()
for m in manual1:
    if not m[4]:
        m[4] = special_req(full_cust(m[6]), m[1]) or '无特殊要求记录'
    style_row(s1, m)
    seen.add((m[0], m[1]))
def _dup(person, code, cust_s, seen_keys):
    for (p_, c_) in seen_keys:
        if p_ == person and c_ == code:
            return True
    return False

for r in rows:
    if _dup(r['person'], r['code'], r['cust_s'], seen):
        continue
    spec = r['spec'] or ('待销售确认（历史按 %s 计价）' % r['unit'])
    val = '%s%s' % ('{:,.0f}'.format(r['stock']), 'T' if r['unit'].startswith('T') else r['unit'])
    style_row(s1, [r['person'], r['code'], spec, '待销售确认', special_req(r['cust'], r['code']) or '无特殊要求记录',
                   val, r['cust_s'], r['date'].strftime('%Y%m%d')])
    seen.add((r['person'], r['code']))

# 转产建议：研发承接占比 ≥50%
seen2 = set()
for m in manual2:
    if not m[4]:
        m[4] = special_req(full_cust(m[6]), m[1]) or '无特殊要求记录'
    style_row(s2, m)
    seen2.add((m[0], m[1]))
for r in rows:
    if r['rd_share'] < 0.5:
        continue
    key = (r['person'], r['code'], r['cust_s'])
    if (r['person'], r['code']) in seen2:
        continue
    spec = r['spec'] or ('待销售确认（历史按 %s 计价）' % r['unit'])
    val = '%s%s' % ('{:,.0f}'.format(r['stock']), 'T' if r['unit'].startswith('T') else r['unit'])
    annual = r['amt90'] / 298.0 * 365 / 1e4
    latest = max(dt.date(2026, 11, 15), r['date'] - dt.timedelta(days=30))
    style_row(s2, [r['person'], r['code'], spec, '待销售确认', special_req(r['cust'], r['code']) or '无特殊要求记录',
                   val, r['cust_s'], round(annual, 1), latest.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:
    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'], r['last'].strftime('%Y-%m-%d'), r['dept'],
               special_req(r['cust'], r['code']) or '无特殊要求记录'])
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, 11, 18, 52]):
    s3.column_dimensions[col].width = w

os.makedirs(OUTDIR, exist_ok=True)
wbt.save(OUT)
json.dump({'window': [str(START), str(END)], 'forecast': [str(FCAST_START), str(FCAST_END)],
           '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) if os.path.isdir(os.path.join(OUTDIR, '数据')) else None

print('候选（90天金额≥%d）%d 行' % (MIN_AMT, len(rows)))
print('拿货预测表 %d 行（含手填 %d）｜转产建议 %d 行（含手填 %d）' % (
    s1.max_row - 1, len(manual1), s2.max_row - 1, len(manual2)))
for r in rows[:10]:
    print('  %-4s %-12s %-24s %10s ← 90天%.0f%s/%d单' % (
        r['person'], r['cust_s'][:12], r['code'], '{:,.0f}'.format(r['stock']) + ('T' if r['unit'].startswith('T') else r['unit']), r['q90'], r['unit'], r['orders']))
print('✅', OUT)