#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""刘子研 2027 商机预算预填：从 2026Q3 汇报 WorkBook1 生成"""
import os, re, json
import pandas as pd
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter

ROOT = '/Users/liuxinyuan/Desktop/Hermes输出-工作类'
SRC = os.path.join(ROOT, '经营分析/2026Q3汇报_嵌入Excel/大客户部/1-大客户部-2026年三季度销售工作汇报-刘子研__Workbook1.xlsx')
OUT = os.path.join(ROOT, '销售分析/2027商机预算_刘子研_预填_20261027.xlsx')

df = pd.read_excel(SRC, sheet_name='Sheet1', header=0, dtype={'商机ID': str})
df = df.dropna(subset=['商机名称']).copy()

def s(v):
    if pd.isna(v): return ''
    t = str(v).replace('　',' ').replace('\\n',' ').replace('\n',' ')
    return re.sub(r'\s+', ' ', t).strip()

def num(v):
    if pd.isna(v): return 0.0
    if isinstance(v,(int,float)): return float(v)
    t=str(v).replace(',','').replace('\\','').strip()
    try: return float(t)
    except: return 0.0

def code(text):
    text = s(text)
    pats = [
        r'\b\d?FM[A-Z]?\d{3,5}(?:-[A-Za-z0-9]+)*',
        r'\b(?:MD|PFM|FMD|FM|M|E|BR|AS)\d{2,6}(?:-[A-Za-z0-9]+)*',
        r'\bBte-8\b',
    ]
    found=[]
    for p in pats:
        for m in re.findall(p,text,flags=re.I):
            if m not in found: found.append(m)
    return '、'.join(found)

def rate(target, actual):
    return actual/target if target>0 else 0

rows=[]
for _,r in df.iterrows():
    cust=s(r.get('客户名称')); name=s(r.get('商机名称'))
    typ=s(r.get('2026新增')); biz=s(r.get('业务类型')) or '诊断原料/PCR'
    if biz.startswith('一、诊断原料'): biz='诊断原料/PCR'
    target=num(r.get('2026目标')); actual=num(r.get('2026达成'))
    y24=num(r.get('2024年销售额')); y25=num(r.get('2025年销售额'))
    rid=s(r.get('商机ID'))
    if rid.endswith('.0'): rid=rid[:-2]
    lost = ('已丢单' in s(r.get('四季度冲刺难点及措施'))) or ('丢单' in s(r.get('前三季度异常原因复盘')))
    test = any(k in name for k in ['测试中','预研','立项'])
    # 商机类型
    if typ=='存量': otype='存量商机'
    elif actual<=0 or lost: otype='探索性商机'
    elif test and target<=5000: otype='探索性商机'
    else: otype='突破性商机'
    # 2027 预填目标（逐项拍板用的基线）
    rr=rate(target,actual)
    if target<=0 or actual<=0:
        amount = 100000 if lost else 50000
    elif rr>=0.8: amount=target*1.1
    elif rr>=0.5: amount=target
    elif rr>=0.3: amount=max(target*0.8, actual/9*12*1.1)
    else: amount=max(actual/9*12*1.1, 50000)
    amount=int(round(amount/1000)*1000)
    # 季度拆分
    qa=[num(r.get(f'{i}季度\n实绩')) for i in range(1,4)]
    qp=[num(r.get(f'{i}季度\n计划')) for i in range(1,5)]
    if sum(qa)>0:
        base=sum(qa)+max(qp[3],0)
        shares=[x/base for x in qa]+[max(qp[3],0)/base]
    elif sum(qp)>0:
        base=sum(qp); shares=[x/base for x in qp]
    else:
        shares=[0.25]*4
    # 若 Q4 为 0 但前三季度有实绩，按前三季度均值补 Q4
    if shares[3]==0 and sum(qa)>0:
        avg=sum(qa)/3; base=sum(qa)+avg; shares=[x/base for x in qa]+[avg/base]
    qs=[int(round(amount*x/1000)*1000) for x in shares]
    diff=amount-sum(qs); qs[3]+=diff
    if qs[3]<0:
        qs[2]+=qs[3]; qs[3]=0
    rows.append({
        '年份':2027,'销售':'刘子研','部门':'诊断原料大客户销售部',
        '客户名称':cust,'商机名称':name,'关联货号':code(name+' '+s(r.get('商机背景概述'))+' '+s(r.get('资源需求'))),
        '商机ID(飞书)':rid,'商机类型':otype,
        '所需资源保障及时间节点':s(r.get('资源需求'))+('；'+s(r.get('关键时间节点')) if s(r.get('关键时间节点')) else ''),
        '业务线':biz,'年度目标合计(元)':amount,
        'Q1预测（元）':qs[0],'Q2预测（元）':qs[1],'Q3预测（元）':qs[2],'Q4预测（元）':qs[3],
        '_2026目标':target,'_2026达成':actual,'_达成率':round(rr,3),
        '_2024':y24,'_2025':y25,'_2026新增':typ,'_lost':lost,
    })

out=pd.DataFrame(rows)
print('rows',len(out),'target',out['年度目标合计(元)'].sum(),'qsum',(out[['Q1预测（元）','Q2预测（元）','Q3预测（元）','Q4预测（元）']].sum(axis=1)-out['年度目标合计(元)']).abs().sum())
print(out[['客户名称','商机名称','商机ID(飞书)','商机类型','业务线','年度目标合计(元)']].head(10).to_string(max_colwidth=40))
print('missing id',sum(out['商机ID(飞书)'].eq('')))
print('types',out['商机类型'].value_counts().to_dict())
print('by biz',out['业务线'].value_counts().to_dict())
out.to_pickle('/tmp/liu2027.pkl')