#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""刘子研 2027 商机预算预填（v2，输出 xlsx）"""
import os, re
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 codes(text):
    text = s(text)
    pre=r'(?<![A-Za-z0-9])'; post=r'(?![A-Za-z0-9])'
    pats = [pre+r'\d?FM[A-Z]?\d{3,5}(?:-[A-Za-z0-9]+)*'+post,
            pre+r'\d[A-Z]{1,4}\d{2,6}(?:-[A-Za-z0-9]+)*'+post,
            pre+r'(?:MD|PFM|FMD|FM|M|E|BR|AS)\d{2,6}(?:-[A-Za-z0-9]+)*'+post,
            pre+r'pm\d{3}'+post,
            pre+r'Bte-?8'+post]
    found=[]
    for p in pats:
        for m in re.findall(p,text,flags=re.I):
            if m.upper() not in [x.upper() for x in found]: found.append(m)
    return '、'.join(found)

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达成'))
    rid=s(r.get('商机ID'))
    if rid.endswith('.0'): rid=rid[:-2]
    txt=s(r.get('四季度冲刺难点及措施'))+' '+s(r.get('前三季度异常原因复盘'))
    lost = ('丢单' in txt) or ('已丢' in txt)
    test = any(k in name for k in ['测试中','预研','立项'])
    rr = actual/target if target>0 else 0

    if typ=='存量' and actual>0 and not lost:
        otype='存量商机'
    elif lost or actual<=0 or target<=0 or test:
        otype='探索性商机'
    else:
        otype='突破性商机'

    if lost:
        amount=100000; basis='已丢单，按探索性 10 万'
    elif target<=0 or actual<=0:
        amount = 100000 if target>=500000 else 50000
        basis=f'零达成/零目标，按探索性 {amount//10000} 万'
    elif rr>=0.8:
        amount=target*1.1; basis=f'达成率{rr:.0%}≥80%，2026目标×1.1'
    elif rr>=0.5:
        amount=target; basis=f'达成率{rr:.0%}，维持2026目标'
    elif rr>=0.3:
        amount=max(target*0.8, actual/9*12*1.1); basis=f'达成率{rr:.0%}，取目标80%与年化实绩×1.1较高者'
    else:
        amount=max(actual/9*12*1.1, 50000); basis=f'达成率{rr:.0%}，按年化实绩×1.1，下限5万'
    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
    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]
    qs[3]+=amount-sum(qs)
    if qs[3]<0:
        qs[2]+=qs[3]; qs[3]=0

    rows.append({
        '年份':2027,'销售':'刘子研','部门':'诊断原料大客户销售部',
        '客户名称':cust,'商机名称':name,
        '关联货号':codes(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),
        '_依据':basis,'_2026新增':typ,'_lost':lost,
    })

out=pd.DataFrame(rows)
cols=['年份','销售','部门','客户名称','商机名称','关联货号','商机ID(飞书)','商机类型','所需资源保障及时间节点','业务线','年度目标合计(元)','Q1预测（元）','Q2预测（元）','Q3预测（元）','Q4预测（元）']
qsum=(out[['Q1预测（元）','Q2预测（元）','Q3预测（元）','Q4预测（元）']].sum(axis=1)-out['年度目标合计(元)']).abs().sum()

# ---------- workbook ----------
wb=Workbook(); ws=wb.active; ws.title='商机预算'
thin=Side(style='thin',color='D9E2EC')
border=Border(left=thin,right=thin,top=thin,bottom=thin)
hdr_fill=PatternFill('solid',fgColor='1F4E79'); hdr_font=Font(color='FFFFFF',bold=True,size=10)
for j,c in enumerate(cols,1):
    cell=ws.cell(1,j,c); cell.fill=hdr_fill; cell.font=hdr_font
    cell.alignment=Alignment(horizontal='center',vertical='center',wrap_text=True); cell.border=border
for i,row in enumerate(out[cols].itertuples(index=False),2):
    for j,v in enumerate(row,1):
        cell=ws.cell(i,j,v); cell.border=border
        cell.font=Font(size=9)
        cell.alignment=Alignment(vertical='top',wrap_text=(j in (5,6,9)))
        if j>=11: cell.number_format='#,##0'
ws.freeze_panes='A2'; ws.auto_filter.ref=f'A1:{get_column_letter(len(cols))}{len(out)+1}'
widths={'年份':7,'销售':8,'部门':20,'客户名称':30,'商机名称':44,'关联货号':24,'商机ID(飞书)':14,'商机类型':12,'所需资源保障及时间节点':46,'业务线':16,'年度目标合计(元)':15,'Q1预测（元）':13,'Q2预测（元）':13,'Q3预测（元）':13,'Q4预测（元）':13}
for j,c in enumerate(cols,1): ws.column_dimensions[get_column_letter(j)].width=widths.get(c,14)
for i in range(2,len(out)+2): ws.row_dimensions[i].height=30

# 测算说明
ws2=wb.create_sheet('测算说明')
lines=[
 ['项目','内容'],
 ['数据源', os.path.basename(SRC)],
 ['商机数', f'{len(out)} 个（存量 {sum(out["商机类型"]=="存量商机")} / 突破性 {sum(out["商机类型"]=="突破性商机")} / 探索性 {sum(out["商机类型"]=="探索性商机")}）'],
 ['2026目标合计', f'{out["_2026目标"].sum():,.0f} 元'],
 ['2026前三季度达成', f'{out["_2026达成"].sum():,.0f} 元'],
 ['2027预填目标合计', f'{out["年度目标合计(元)"].sum():,.0f} 元'],
 ['2027目标口径', '逐项拍板（D）。本表按 2026 目标/达成率生成“预填基线”，最终以你逐项修改为准。'],
 ['目标规则', '达成率≥80%：2026目标×1.1；50–80%：维持2026目标；30–50%：取目标80%与年化实绩×1.1较高者；<30%：年化实绩×1.1、下限5万；丢单：10万；零达成：5万（目标≥50万的项目10万）。'],
 ['季度拆分', '按 2026 年 Q1–Q3 实绩比例；Q4 无实绩，按 2026 Q4 计划占全年计划比例补足；若 Q4 计划也为 0，则按前三季度均值补 Q4。'],
 ['商机类型规则', '存量且 2026 有达成→存量商机；丢单/零达成/零目标/测试中或预研→探索性商机；其余增量→突破性商机。'],
 ['业务线', '统一为“诊断原料/PCR”；原表 3 个空值也按此填。'],
 ['ID 空缺', f'{sum(out["商机ID(飞书)"].eq(""))} 个商机 ID 留空，导入时这些行会报错，需补飞书 ID。'],
]
for i,row in enumerate(lines,1):
    for j,v in enumerate(row,1):
        c=ws2.cell(i,j,v); c.border=border
        if i==1: c.fill=hdr_fill; c.font=hdr_font
        else: c.font=Font(size=10); c.alignment=Alignment(wrap_text=True,vertical='top')
ws2.column_dimensions['A'].width=20; ws2.column_dimensions['B'].width=110
for i in range(2,len(lines)+1): ws2.row_dimensions[i].height=42

# 数据质量
ws3=wb.create_sheet('数据质量')
q=[
 ['类型','序号','客户','商机','说明'],
]
for _,r in out.iterrows():
    srcrow=df.loc[df['商机名称'].apply(s)==r['商机名称']].head(1)
    seq=int(srcrow['序号'].iloc[0]) if len(srcrow) else ''
    if r['商机ID(飞书)']=='':
        q.append(['缺飞书ID',seq,r['客户名称'],r['商机名称'],'导入会报错，需补 ID'])
    if r['关联货号']=='':
        q.append(['缺关联货号',seq,r['客户名称'],r['商机名称'],'名称/背景中未提取到货号'])
for _,r in out.iterrows():
    if r['_2026达成']<=0 or r['_lost']:
        srcrow=df.loc[df['商机名称'].apply(s)==r['商机名称']].head(1)
        seq=int(srcrow['序号'].iloc[0]) if len(srcrow) else ''
        q.append(['零达成/丢单',seq,r['客户名称'],r['商机名称'],r['_依据']])
for i,row in enumerate(q,1):
    for j,v in enumerate(row,1):
        c=ws3.cell(i,j,v); c.border=border; c.font=Font(size=9)
        if i==1: c.fill=hdr_fill; c.font=hdr_font
for col,w in zip('ABCDE',[14,7,30,44,40]): ws3.column_dimensions[col].width=w
ws3.freeze_panes='A2'

# 逐项测算
ws4=wb.create_sheet('逐项测算')
hdr=['序号','客户','商机','2026目标','2026达成','达成率','2027预填','测算依据']
for j,h in enumerate(hdr,1):
    c=ws4.cell(1,j,h); c.fill=hdr_fill; c.font=hdr_font; c.border=border
for i,(_,r) in enumerate(out.iterrows(),2):
    srcrow=df.loc[df['商机名称'].apply(s)==r['商机名称']].head(1)
    seq=int(srcrow['序号'].iloc[0]) if len(srcrow) else ''
    vals=[seq,r['客户名称'],r['商机名称'],r['_2026目标'],r['_2026达成'],r['_达成率'],r['年度目标合计(元)'],r['_依据']]
    for j,v in enumerate(vals,1):
        c=ws4.cell(i,j,v); c.border=border; c.font=Font(size=9)
        c.alignment=Alignment(wrap_text=(j in (3,8)),vertical='top')
        if j in (4,5,7): c.number_format='#,##0'
        if j==6: c.number_format='0%'
for col,w in zip('ABCDEFGH',[7,30,44,12,12,10,12,46]): ws4.column_dimensions[col].width=w
ws4.freeze_panes='A2'

wb.save(OUT)
print('saved',OUT)
print('rows',len(out),'target',out['年度目标合计(元)'].sum(),'qdiff',qsum)
print('types',out['商机类型'].value_counts().to_dict())
print('missing id',sum(out['商机ID(飞书)'].eq('')))
print('blank sku',sum(out['关联货号'].eq('')))