from pathlib import Path
import csv, json, re
from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font, PatternFill, Alignment
from openpyxl.utils import get_column_letter
from openpyxl.comments import Comment

root = Path(__file__).resolve().parent
text = (root / '战略规划.md').read_text()
tables = []
heading = ''
lines = text.splitlines()
i = 0
while i < len(lines):
    line = lines[i]
    if line.startswith('## '):
        heading = line[3:]
    if line.startswith('|') and i + 1 < len(lines) and re.match(r'^\|[\s:|-]+\|$', lines[i + 1]):
        rows = []
        while i < len(lines) and lines[i].startswith('|'):
            if not re.match(r'^\|[\s:|-]+\|$', lines[i]):
                row = [re.sub(r'\*\*', '', x.strip()) for x in lines[i].strip('|').split('|')]
                rows.append(row)
            i += 1
        tables.append({'heading': heading, 'rows': rows})
        continue
    i += 1

names = ['四季度作战','现在卖什么','真实项目回验','八项产品行动','首批商机选择','六周作战','达标口径','执行版依据']
assert len(tables) == len(names), (len(tables), [x['heading'] for x in tables])
wb = Workbook()
guide = wb.active
guide.title = '使用说明'
for row in [
    ['智慧食堂四季度作战计划','2026-10-09 / V0.2执行建议稿'],
    ['用户确认周期','2026 Q4—2027 Q3；从现在起未来四季度'],
    ['用户确认目标','提高赢单率和销售收入'],
    ['建议主题','标准方案卖清交稳→专业营养额外成交→多餐线/集团复制→存量增购与服务'],
    ['使用顺序','六周作战→八项产品行动→真实项目回验→商机推进台账→四季度作战'],
    ['状态','首轮公开研究与草案完成；正式战略、预算与项目尚未批准'],
    ['数据边界','最新CRM、合同、财务、回款、成本、工时尚未刷新'],
    ['数值性质','日期/数量为建议；撤回统一客单+15%、赢率+10个百分点强制门槛'],
    ['目标测算','蓝字/黄色单元格输入真实假设；空白显示待补证；签约不等于收入和到账'],
    ['项目卡','必须填写真实机会、责任人、批准阈值、观察窗与证据；否则DoR BLOCKED'],
    ['报告真源','战略规划.md；history/v0.1保留研究底稿；当前网页为V0.2'],
    ['资料口径','官网C；候选/入围仅证明相应事件；历史报价B派生未重读原件'],
    ['发布','仅本地内部评审，未公开上传、未派工、未写云效'],
]: guide.append(row)
for name, table in zip(names, tables):
    ws = wb.create_sheet(name)
    for row in table['rows']: ws.append(row)
    if name == '执行版依据':
        with (root/'execution-source-ledger.csv').open('w', newline='', encoding='utf-8-sig') as f:
            csv.writer(f).writerows(table['rows'])
        for row in ws.iter_rows(min_row=2):
            m = re.search(r'\]\((https?://[^)]+)\)', str(row[1].value))
            if m: row[1].hyperlink=m.group(1)

ws = wb.create_sheet('目标测算')
ws.append(['季度','可比合格机会数','同类预计赢率','可交付项目上限','同范围净客单元','独立增购签约元','独立服务签约元','预期新赢项目数','预期净签约额元','财务确认收入目标元','实际回款目标元','目标批准状态'])
for r, quarter in enumerate(['2026 Q4','2027 Q1','2027 Q2','2027 Q3'], 2):
    ws.append([quarter,None,None,None,None,None,None,
       f'=IF(COUNT(B{r}:G{r})<6,"待补证",IF(OR(B{r}<0,C{r}<0,C{r}>1,D{r}<0,E{r}<0,F{r}<0,G{r}<0),"输入无效",MIN(B{r}*C{r},D{r})))',
       f'=IF(ISNUMBER(H{r}),H{r}*E{r}+F{r}+G{r},"待补证")',None,None,'待核定'])
    ws.cell(r,3).number_format='0.0%'
    for c in [5,6,7,9,10,11]: ws.cell(r,c).number_format='#,##0.00'
    for c in [2,3,4,5,6,7,10,11]:
        ws.cell(r,c).comment=Comment('Source: 待补充最新CRM/财务/交付原件；2026-10-09。实际输入不得用历史混合均价。','Codex')
ws.append(['说明','模型为D级情景，不是已批准预算；未来合同与旧服务重叠需先去重。预计项目数为期望值，资源排程另取整数。'])
ws.append(['输入规则','机会/产能/金额非负；赢率0—1；输入0表示已确认无此项，空白表示未知。收入与回款由财务按履约和付款期独立核定。'])

ws = wb.create_sheet('产品任务跟踪')
ws.append(['项目ID','季度','真实机会或客户ID','唯一负责人','预算与采购触发','假设','冻结阈值','基线来源','观察窗与成熟日','范围版本','合同证据','验收与专业审核','成本证据','到账证据','失败或未决','DoR','Go Hold Stop'])
for project in next(t['rows'][1:] for n,t in zip(names,tables) if n=='八项产品行动'):
    pid=project[0].split()[0]
    quarter='2026 Q4'
    ws.append([pid,quarter,None,None,None,project[3],project[4],None,None,None,None,None,None,None,None,'BLOCKED：实际负责人/资源/授权待核','尚未执行'])

ws = wb.create_sheet('商机推进台账')
ws.append(['位置','真实项目名或CRM ID','负责销售','买方与付款方','采购预算元','采购时间','主要对手或原系统','三项选择标准','产品版与餐线','审批净报价元','本周动作','下一采购节点','签约日期','合同净额元','确认收入元','实际到账元','交付人天','项目贡献元','来源','状态'])
for pid in ['M01','M02','M03']:
    ws.append([pid,None,None,None,None,None,None,None,None,None,None,None,None,None,None,None,None,None,None,'待真实CRM输入；不是已有商机'])

ws = wb.create_sheet('项目回验跟踪')
ws.append(['已有项目','要验证什么','当前资料状态','实际负责人','当前部署SHA与设备组合','批准测试方法或误差','授权验证窗口','回验结果与原始证据','实际关闭日期','客户确认','可售限制','可对外案例许可'])
ws.append(['中央美院','餐盘识别/累计重量/接口及异常','10/8报告：P01处理中，P02待复测',None,None,None,None,None,None,None,'受影响组合待验证；其他型号不能无证据连带限售',None])
ws.append(['国信','消费策略/账户/退款等政企配置','当前部署及验收状态待核；旧方案不能当已上线',None,None,None,None,None,None,None,'按真实已验能力报价',None])
ws.append(['张家港','真实Windows升级/恢复与银行业务','本地包验证存在；现场端到端待验',None,None,None,None,None,None,None,'HTTP200不替代银行核销验收',None])

ws = wb.create_sheet('竞争卡模板')
ws.append(['字段','实际输入','证据规则'])
for field, rule in [
('机会ID与采购范围','同一买方/预算/范围'),('买方与批准人','后勤、财务、IT、最终付款者分别确认'),('采购预算/时间','文件或授权访谈，不以官网代替'),('替代方案/具体竞品','客户原系统与不采购也列入'),('关键3项选择标准','买方原意和证据日期'),('我方已证明结果','当前版本/实测/案例证据'),('竞品证据状态','有/部分/待核实；暂无要反证'),('同范围首年与三年TCO','授权/设备/部署/接口/实施/服务/退出'),('采购准入','资格/接口权限/信创等逐项核定'),('责任与下一动作','一个负责人和日期'),('赢输或未决策原因','不删除失败；保留证据'),('竞争卡版本和更新时间','同一机会不反复新建不一致卡')]: ws.append([field,None,rule])

for ws in wb:
    ws.freeze_panes='A2'
    ws.auto_filter.ref=ws.dimensions
    ws.sheet_view.zoomScale=80
    for row in ws:
        for cell in row:
            color='000000' if cell.data_type=='f' else '24352F'
            isinput=(ws.title=='目标测算' and 2<=cell.row<=5 and cell.column in [2,3,4,5,6,7,10,11])
            cell.font=Font(name='微软雅黑',size=12,color='0000FF' if isinput else color)
            cell.alignment=Alignment(vertical='top',wrap_text=True)
            if isinput: cell.fill=PatternFill('solid',fgColor='FFF3CC')
    for cell in ws[1]:
        cell.font=Font(name='微软雅黑',size=12,bold=True,color='FFFFFF')
        cell.fill=PatternFill('solid',fgColor='294A40')
    ws.row_dimensions[1].height=46
    for r in range(2,ws.max_row+1): ws.row_dimensions[r].height=85 if ws.title not in ['目标测算','使用说明'] else 55
    for c in range(1,ws.max_column+1):
        ws.column_dimensions[get_column_letter(c)].width=18 if c==1 else 42
    if ws.title=='目标测算':
        for c in range(2,ws.max_column+1): ws.column_dimensions[get_column_letter(c)].width=23
    ws.page_setup.orientation='landscape'
    ws.page_setup.paperSize=ws.PAPERSIZE_A3
    ws.page_setup.fitToWidth=1
    ws.sheet_properties.pageSetUpPr.fitToPage=True
    ws.print_title_rows='1:1'

wb.save(root/'四季度战略与项目验证.xlsx')
(root/'table-data.json').write_text(json.dumps(dict(zip(names,tables)),ensure_ascii=False,indent=2))
print(json.dumps({'sheets':wb.sheetnames,'tables':len(tables),'formulas':8,'execution_sources':len(tables[-1]['rows'])-1},ensure_ascii=False))