from pathlib import Path
import re, math, json, hashlib
from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.comments import Comment
from openpyxl.worksheet.datavalidation import DataValidation

ROOT = Path(__file__).resolve().parent
TEXT = (ROOT / '2027年产品战略与产能规划.md').read_text()
WB = Workbook()
WB.remove(WB.active)

def table_after(marker, occurrence=0):
    text = TEXT.split(marker, 1)[1]
    tables = re.findall(r'(?m)^\|.*\n(?:\|.*\n)+', text)
    lines = tables[occurrence].strip().splitlines()
    return [[c.strip() for c in line.strip('|').split('|')] for line in lines if not re.match(r'^\|[-: ]+\|', line)]

def sheet(name, title, heads, rows, note='D级建议，未批准；具体范围、预算、负责人和阈值按证据核定。'):
    ws = WB.create_sheet(name)
    ws.append([title]); ws.append([note]); ws.append([]); ws.append(heads)
    for row in rows: ws.append(row)
    return ws

sheet('使用说明', '2027 产品规划与经营测算 V0.1', ['事项','说明'], [
    ['日期/状态','2026-10-08；经营评审建议稿；不是批准预算或真实业绩'],
    ['范围','数字业务：智慧营养食堂主线，运动营养/科训协同线；公司全部食品制造产线未覆盖'],
    ['输入与公式','蓝色/浅黄色数字为可修改假设；黑色公式；绿色跨表引用。所有产能数字为D级演示'],
    ['真实数据','产品收入/贡献/增长/负责人待核；本轮没有刷新CRM、财务、供应全量台账'],
    ['三个产能情景','人员按彼此独立分配演示；人数/日/效率/人天/齐套可修改；同一人跨环节不能重复投入'],
    ['计划容量','理论瓶颈×可用月数×波动缓冲；稳态示例不代替跨月排程/销售/现金约束'],
    ['收费客户','机构去重；豁免、成熟续费群与当年服务客户月分别统计'],
    ['收入边界','年化合同价值不是确认收入；服务期间模型不替代财务政策；项目/模块收入本模型未加'],
    ['空白评分','APPEALS/PDC分数故意留空；缺经营证据不能把空白当0分'],
    ['同步','当前只本地保存；仓库已有其他任务改动/个人配置，未自动拉取/提交/推送'],
], '完整正文和来源账本在同目录。建议/情景均为D，不视为实际值。')

maps=table_after('### 4.2 六类产品包')
heads=maps[0]+['主区域','渠道','2026收入（元）','贡献率','增长率','实际负责人','证据/状态']
rows=[r+['待核定','直销/渠道需分别核','待采','待采','待采','待指派','D级产品包规划；S03为B级资料登记'] for r in maps[1:]]
sheet('产品地图','六类可售产品包与待补经营数据', heads, rows)
for name,title,mark in [
 ('市场与购买','目标细分与购买系统','### 3.1 市场边界与五层细分'),
 ('季度路标','2026 Q4准备与2027季度门禁','## 13. 季度路线图'),
 ('90天行动','预算前置与行动清单','### 17.1 从现在到年度预算'),
 ('指标字典','经营与产能指标口径','## 15. 2027 经营与产能指标'),
 ('供应核查','供应八类问题的证据与改进','### 9.2 供应必须检查'),
 ('研究院接口','专业成果到产品与交付','### 11.1 分工与交接成果'),
 ('研究院项目','三项联合项目的门禁','### 11.3 2027 三个联合项目'),
 ('资源建议','7-2-1新增资源建议','### 5.4 7-2-1'),
 ('数据缺口','待补数据及其决策影响','### 17.2 最关键的缺口'),
]:
    t=table_after(mark); sheet(name,title,t[0],t[1:])

t=table_after('### 5.2 APPEALS')
sheet('APPEALS待评分','客户价值权重建议；分数待证', t[0]+['康比特1-5分','替代A1-5分','替代B1-5分','同条件来源'], [r+[None,None,None,'待核'] for r in t[1:]])
sheet('PDC待评分','正式排序需补齐经营与采购数据', ['对象','市场吸引力1-5','竞争地位1-5','财务能力1-5','综合分','市场权重','竞争权重','财务权重','门禁/来源'], [[x,None,None,None,None,.4,.35,.25,'待补：预算/贡献/回款/交付；不因总分高越过门禁'] for x in ['P1核心与标准交付','P2/P6营养','P3/P4治理','P5/渠道','医院/科训新增投资']])
for r in range(5,10):
    WB['PDC待评分'][f'E{r}']=f'=IF(COUNT(B{r}:D{r})=3,SUMPRODUCT(B{r}:D{r},F{r}:H{r}),"待补证")'

for name,presales,delivery,supply in [('产能_示例基线',8,20,4),('产能_标准化',4,12,4),('产能_供应改善',4,12,6)]:
    ws=sheet(name, name+'：假设模型，不代表现实团队', ['环节','可分配人数','工作日/月','有效比例','项目人天','月容量/等价项目','来源/口径'], [
        ['售前',2,20,.75,presales,None,'D：方案同一规模/范围，独立人力'],
        ['项目研发/适配',4,20,.65,6,None,'D：仅项目分摊，共用平台研发需另排'],
        ['测试',2,20,.70,4,None,'D：完整关键场景验证，不能减掉必要测试'],
        ['现场实施',5,20,.70,delivery,None,'D：含现场工作，同类范围'],
        ['首期客户成功',2,20,.60,2,None,'D：首期投入，不代替存量长期支持容量'],
        ['供应齐套',None,None,None,None,supply,'D：合格齐套已换算相同项目等价量'],
    ], '全部为D级示例。有效比例包含例行占用；另有15%月间波动缓冲。真实排程/现金/存量支持另核。')
    for r in range(5,10): ws[f'F{r}']=f'=IF(E{r}>0,B{r}*C{r}*D{r}/E{r},"输入需大于0")'
    ws['A12']='最小环节月容量'; ws['F12']='=IF(COUNT(F5:F10)=6,MIN(F5:F10),"输入无效")'
    ws['A13']='全年可用月数'; ws['B13']=12
    ws['A14']='波动缓冲系数'; ws['B14']=.85
    ws['A15']='示例年计划项目数'; ws['B15']='=IF(ISNUMBER(F12),ROUNDDOWN(F12*B13*B14,0),"输入无效")'
    ws['A17']='限制'; ws['B17']='不含季节/爬坡/客户就绪/销售/现金约束；共用人力必须重新分配，不能重复计算。'
    dv=DataValidation(type='decimal',operator='greaterThan',formula1='0'); dv.errorTitle='输入错误'; dv.error='人天必须大于0'; dv.showErrorMessage=True
    ws.add_data_validation(dv);dv.add('E5:E9')

ws=sheet('经营测算','年费、客户目标与产能约束：全为假设', ['指标','输入/结果','单位','说明'], [
 ['年末收费机构',300,'家','D：去重机构；不是已批准目标'],
 ['年服务价格',1000,'元/机构年','D：来自会议举例，不是批准价'],
 ['单客年直接服务成本',600,'元/机构年','D：假设，实际含云/支持/专业/渠道归属成本'],
 ['服务共用固定年投入',300000,'元/年','D：假设；未与单客直接成本重复归集'],
 ['同口径合格机会赢率',.25,'比例','D：假设；不重复乘前序漏斗'],
 ['获客周期月数',12,'月','D：假设全年可开展'],
 ['当年服务客户月',3600,'机构月','D：300家全年12个月的示例；年中新增请按实际月数改'],
 ['当年服务收入模型', '=B11*B6/12','元','按服务期间简单演示，实际财务确认另核'],
 ['年末服务年化合同价值','=B5*B6','元/年','不是当年确认收入/现金'],
 ['单客年直接贡献','=B6-B7','元/年','不含共用固定成本'],
 ['服务收入减直接成本及固定投入','=B12-B11*B7/12-B8','元','纯服务模型，未加项目/模块收益'],
 ['全年客数盈亏平衡','=IF(B14>0,ROUNDUP(B8/B14,0),"不可盈亏平衡")','机构全年','固定投入/单客贡献，贡献不正则无法靠规模平衡'],
 ['若全是新客：资格机会需求','=IF(B9>0,ROUNDUP(B5/B9,0),"赢率需大于0")','条','存量贡献应另扣；不是线索曝光数'],
 ['月均合格机会需求','=IF(AND(ISNUMBER(B17),B10>0),B17/B10,"输入无效")','条/月','全年平均；真实采购季节另排'],
 ['示例基线年计划量',"='产能_示例基线'!B15",'项目等价量','跨表假设；年35项目不自动等于35收费机构'],
 ['若全是1家1重项目的容量差距','=MAX(0,B5-B19)','项目','条件假设：收费群全部新获，每机构一个同类重项目'],
])

src=(ROOT/'source-index.md').read_text()
ledger=re.search(r'(?m)^\| 证据ID.*\n(?:\|.*\n)+',src).group(0)
rr=[[x.strip() for x in l.strip('|').split('|')] for l in ledger.strip().splitlines() if not re.match(r'^\|[-: ]+\|',l)]
sheet('证据账本','主要事实与不证明的事项',rr[0],rr[1:],'B会议/内部材料；C公开官方参考；D建议。详情见source-index.md，不将同源纪要当独立证据。')

for ws in WB:
    n=ws.max_column
    ws.merge_cells(start_row=1,start_column=1,end_row=1,end_column=n)
    ws.merge_cells(start_row=2,start_column=1,end_row=2,end_column=n)
    ws.row_dimensions[1].height=38;ws.row_dimensions[2].height=55;ws.row_dimensions[4].height=38
    ws.freeze_panes='B5'; ws.auto_filter.ref=f'A4:{ws.cell(ws.max_row,n).coordinate}'
    ws.sheet_view.showGridLines=False
    ws.sheet_properties.pageSetUpPr.fitToPage=True
    ws.page_setup.orientation='landscape';ws.page_setup.paperSize=ws.PAPERSIZE_A3
    ws.page_setup.fitToWidth=1;ws.page_setup.fitToHeight=0
    ws.print_title_rows='1:4'
    for c in range(1,n+1):
        width=26 if n>5 else (34 if c==1 else 65 if n==2 else 45 if c==4 else 25)
        ws.column_dimensions[ws.cell(4,c).column_letter].width=width
    for row in ws.iter_rows():
        for cell in row:
            color='202C38'; fill='FFFFFF'
            if cell.row==1: color='FFFFFF';fill='243D39'
            elif cell.row==2: color='625334';fill='FFF4D9'
            elif cell.row==4: color='FFFFFF';fill='45655E'
            elif cell.row>=5 and cell.row%2==1:fill='F3F7F6'
            if cell.data_type=='f': color='008050' if '!' in cell.value else '000000';cell.number_format='#,##0.00;(#,##0.00);-'
            elif isinstance(cell.value,(int,float)) and cell.row>=5:
                color='0000FF';fill='FFF4CC';cell.number_format='#,##0.00;(#,##0.00);-'
                cell.comment=Comment('Source: 2027战略规划V0.1, 2026-10-08, D级建议/示例输入；不是实测或批准值。','Codex')
            cell.font=Font(name='微软雅黑',size=12,bold=cell.row in [1,4],color=color)
            cell.fill=PatternFill('solid',fgColor=fill)
            cell.alignment=Alignment(wrap_text=True,vertical='top')
            cell.border=Border(bottom=Side(style='hair',color='DCE5E1'))
        if row[0].row>=5:
            longest=max((len(str(c.value or '')) for c in row), default=0)
            ws.row_dimensions[row[0].row].height=min(210,max(46,math.ceil(longest/(20 if n>5 else 35))*20))
    if ws.title.startswith('产能_'):
        for r in range(5,10):ws[f'D{r}'].number_format='0%'
        ws['B14'].number_format='0%'
    if ws.title=='PDC待评分':
        for r in range(5,10):
            for col in 'FGH':ws[f'{col}{r}'].number_format='0%'
    if ws.title=='经营测算':ws['B9'].number_format='0%'

path=ROOT/'2027产品规划与经营测算.xlsx'
WB.save(path)
print(json.dumps({'sheets':len(WB.sheetnames),'formula_count':sum(c.data_type=='f' for w in WB for row in w for c in row),'path':str(path)},ensure_ascii=False))

files=[
Path('/Users/jack/Library/Containers/com.tencent.WeWorkMac/Data/Documents/Profiles/A74DF84611CC2ED79C4C206E40B5D80E/Caches/Files/2026-10/5c0eb3f9af1ff44e80af067ad7f6394b/纪要_业务经营对齐：产能逻辑重构与销售策略调整.pdf'),
Path('/Users/jack/Library/Containers/com.tencent.WeWorkMac/Data/Documents/Profiles/A74DF84611CC2ED79C4C206E40B5D80E/Caches/Files/2026-10/84cff4b9793cc164fed87dde954107d9/原文_业务经营对齐：产能逻辑重构与销售策略调整.pdf')]
from pypdf import PdfReader
audit=[{'name':p.name,'sha256':hashlib.sha256(p.read_bytes()).hexdigest(),'pages':len(PdfReader(p).pages),'full_text_read':True,'visual_review':'page1','conversion':'MarkItDown failed missing PDF extras; Poppler successful'} for p in files]
(ROOT/'evidence/source-audit.json').write_text(json.dumps(audit,ensure_ascii=False,indent=2))