"""Build an internal weekly draft from explicitly selected, dated evidence."""
from pathlib import Path
from copy import copy
import csv, json, hashlib, math
from openpyxl import load_workbook, Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.comments import Comment
from openpyxl.utils import get_column_letter
from openpyxl.worksheet.pagebreak import Break

HERE = Path(__file__).resolve().parent
ROOT = HERE.parents[3]
OUT = HERE / 'output'
OUT.mkdir(exist_ok=True)
TEMPLATE = Path('/Users/jack/同步空间/cpt/05_经营管理与会议/005_日常管理/周例会（事业部层面）/2026年/周报模板.xlsx')
DATA = json.loads((HERE/'report-data.json').read_text())
SOURCES = {
'S1': ('9/4交付与运维周会（日期暂记）', 'work/2026-09-04-multi-project-delivery-ops-weekly-meeting/meeting-minutes.md'),
'S2': ('9/7数科事业部会议纪要', 'output/meeting-minutes/20260907/20260907_数科事业部周例会会议纪要.docx'),
'S3': ('9/11组长周会准备材料/自报进展，非会后决议', 'work/2026-09-11-weekly-meeting/weekly-meeting-content.md'),
'S4': ('9/5项目总表历史快照，非当前全量', 'work/weekly-reports/2026-W36/product-rnd-delivery-review/project-portfolio.csv'),
'S5': ('9/11用户提供的客户回访输入', 'work/weekly-reports/2026-W37/customer-return-visits/summary.md'),
'S6': ('9/7杜铭任务计划，未代表实际执行', 'work/2026-09-07-duming-task-plan/README.md'),
'S7': ('9/11金斯瑞数据库/代码诊断，未修复上线', 'work_store/20260911-jsr-full-refund-status/summary.md'),
'S8': ('9/7读取的9月确收计划历史快照', 'work/weekly-reports/2026-W36/weekly-0907-project-photos/revenue-source.json'),
'S9': ('9/10国信补单诊断/规则澄清', 'work_store/20260910-guoxin-convenience-supplement-limit-diagnosis/summary.md'),
'S10': ('9/11新吴区代码诊断/待现场日志', 'work_store/20260911-xinwu-menu-loss/summary.md'),
'S11': ('9/11用户28天服务产品输入及边界', 'work/weekly-reports/2026-W37/ai-health-28-day-service/source-summary.md'),
'S12': ('9/9起410扫码审计与执行包，未真机验收', 'work/team-learning/thread-records/2026-09-09/youkate-scan-sdk-audit.md'),
'S13': ('三级等保原计划，未证明签发', 'work/2026-08-13-mlps-certification-schedule/mlps-certification-schedule.md'),
'S14': ('9/11饮食照片复用方案/Apifox阻断', 'work_store/2026-09-11-diet-photo-record-reuse/plan.md'),
'S15': ('9/10上传20MB诊断，未开发', 'work_ai_app/2026-09-10-document-upload-20mb/summary.md'),
}
BLUE='155E9A'; ORANGE='A75117'; NAVY='19354C'; LIGHT='EAF3FA'; PALE='FFF2E5'
LINE=Side(style='thin',color='DBE4EC')

def source_note(ids):
    return '\n'.join(f'{i}: {SOURCES[i][0]}\n{ROOT/SOURCES[i][1]}' for i in ids.split('/') if i in SOURCES)

def init_sheet(ws, title, note, headers, widths):
    for merge in list(ws.merged_cells.ranges): ws.unmerge_cells(str(merge))
    ws.delete_rows(1,ws.max_row)
    n=len(headers)
    ws.merge_cells(start_row=1,start_column=1,end_row=1,end_column=n)
    ws.cell(1,1,title)
    ws.merge_cells(start_row=2,start_column=1,end_row=2,end_column=n)
    ws.cell(2,1,note)
    for i,h in enumerate(headers,1): ws.cell(4,i,h)
    ws.row_dimensions[1].height=30
    ws.row_dimensions[2].height=45
    ws.row_dimensions[4].height=32
    for i,w in enumerate(widths,1): ws.column_dimensions[get_column_letter(i)].width=w
    ws.freeze_panes='A5'
    ws.sheet_view.showGridLines=False
    ws.sheet_view.zoomScale=85
    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'
    ws.page_margins.left=ws.page_margins.right=0.25
    ws.page_margins.top=ws.page_margins.bottom=0.35
    ws.oddFooter.center.text='内部周报初稿 · 截至2026-09-12 · 第 &P 页'
    ws.oddFooter.center.size=9

def add_rows(ws, rows, sources=None, color_cols=None):
    for ri,values in enumerate(rows,5):
        lines=1
        for ci,v in enumerate(values,1):
            c=ws.cell(ri,ci,v)
            if isinstance(v,str):
                # CJK occupies approximately two Latin-width units.
                width=ws.column_dimensions[get_column_letter(ci)].width
                count=sum(max(1,math.ceil(sum(1.7 if ord(ch)>255 else 1 for ch in line)/max(width-2,1))) for line in v.split('\n'))
                lines=max(lines,count)
            if sources and sources[ri-5]: c.comment=Comment(source_note(sources[ri-5]),'来源核对')
            if color_cols and ci in color_cols:
                c.font=Font(name='微软雅黑',size=12,color=color_cols[ci])
        ws.row_dimensions[ri].height=min(255,max(40,lines*18+12))
    ws.auto_filter.ref=f'A4:{get_column_letter(ws.max_column)}{ws.max_row}'

def finish(ws):
    for row in ws:
        for c in row:
            f=copy(c.font);f.name='微软雅黑';f.sz=12;c.font=f
            c.alignment=Alignment(vertical='top',wrap_text=True)
            if c.row in (1,4):
                c.fill=PatternFill('solid',fgColor=NAVY if c.row==1 else BLUE)
                c.font=Font(name='微软雅黑',size=12,bold=True,color='FFFFFF')
            elif c.row==2:
                c.fill=PatternFill('solid',fgColor=PALE)
                c.font=Font(name='微软雅黑',size=12,color='78420F')
            elif c.row>=5:
                if c.row%2: c.fill=PatternFill('solid',fgColor='F4F7FA')
                c.border=Border(bottom=LINE)
    ws.print_area=f'A1:{get_column_letter(ws.max_column)}{ws.max_row}'

def md_table(path, heading):
    s=path.read_text(); part=s.split(heading,1)[1].split('\n## ',1)[0]
    rows=[]
    for line in part.splitlines():
        if line.startswith('|'):
            vals=[v.strip().replace('**','') for v in line.strip('|').split('|')]
            if all(set(v)<=set('-: ') for v in vals): continue
            rows.append(vals)
    return rows[1:]

wb=load_workbook(TEMPLATE)
for ws in list(wb):
    if ws.title not in ['项目','产品']: wb.remove(ws)
summary=wb.create_sheet('本周汇报',0)
init_sheet(summary,'数字技术中心 & 交付实施部周报｜2026.09.07—09.13',
 '初稿 · 事实截至9月12日；下周9月14—20日。蓝色本周，橙色下周。企微文档权限过期，最新全量进度未取得；原计划节点均不视为完成。',
 ['汇报主线','可直接讲的内容'],[24,82])
add_rows(summary,DATA['summary'])
for r in range(5,14):
    color=ORANGE if r>=10 else BLUE
    summary.cell(r,1).font=Font(name='微软雅黑',size=12,bold=True,color=color)
    summary.cell(r,2).font=Font(name='微软雅黑',size=12,color=color)
    summary.row_dimensions[r].height=76 if r!=13 else 84
summary.page_setup.paperSize=summary.PAPERSIZE_A4
summary.page_setup.orientation='portrait'
summary.page_setup.fitToHeight=1

ws=wb['项目']
headers=['序号','项目类型','项目阶段','项目名称','关键时间','项目金额','负责人','合同','采购','交付','上线','验收','回款','本周进展（9/7—12）','下周计划/后续动作','问题与偏差','来源','链接']
init_sheet(ws,'项目｜关键进展、交付与问题闭环','仅汇总有本周要点或待衔接节点的项目。原计划已到期但无新证据的，标“结果待回报”；合同/采购/验收/回款保留模板字段，默认隐藏待核列。',headers,[6,12,14,23,26,16,25,14,14,16,16,14,14,48,48,37,14,18])
rows=[]
for i,p in enumerate(DATA['projects'],1):
    kind,stage,name,date,owner,actual,nxt,risk,src=p
    rows.append([i,kind,stage,name,date,'待商务核对',owner,'待核','待核','见本周进展','见本周进展','待签认核实','待财务核实',actual,nxt,risk,src,'见来源批注'])
add_rows(ws,rows,[p[-1] for p in DATA['projects']],{14:BLUE,15:ORANGE})
for col in ['F','H','I','J','K','L','M','Q','R']: ws.column_dimensions[col].hidden=True

ws=wb['产品']
init_sheet(ws,'产品｜差异性、标准化、AI赋能','三条主线下说明本周变化、项目验证与下周门槛；方案/原型/代码诊断不等于上线。',
 ['序号','一级分类','二级分类','工作内容','对应项目/场景','时间节点','负责人','本周输出/目标','下周门槛/备注'],[6,16,21,42,23,26,26,47,51])
add_rows(ws,[[i]+p[:-1] for i,p in enumerate(DATA['products'],1)],[p[-1] for p in DATA['products']],{8:BLUE,9:ORANGE})

# Preserve every scheduled item from the selected meeting and deployment plans.
nodes=[]
for r in md_table(ROOT/SOURCES['S1'][1],'## 四、按负责人形成的交付清单'):
    owner,project,when,status,action,evidence=r
    nodes.append([project,when,owner,status,action,evidence,'9/4原计划；本周执行结果待核','S1'])
for project,owner,action in [
    ('彩育3校','侯世伟','回收平台恢复/网络配置与培训确认'),('旧宫中学','交付负责人待核','回收手持机、网络、打印机培训及关闭记录'),
    ('荣海小学','交付负责人待核','确认新负责人可独立操作'),('国康','柴玉龙；孙海琴回访','核410及麻辣烫设备问题，补标准操作和回访报告')]:
    nodes.append([project,'日期待补',owner,'历史有处理记录；本周闭环待核',action,'客户确认/复测记录','9/4原记录；9月状态待核','S1/S5'])
for r in md_table(ROOT/SOURCES['S3'][1],'## 八、责任锁定'):
    project,owner,when,evidence=r
    nodes.append([project,when,owner,'9/11周会材料拟定目标，待确认','落实范围、环境、资源和唯一验收人',evidence,'拟定目标；非已批准客户承诺','S3'])
extra=[
 ['哈尔滨膳质舍','9/15现场联调；9/22整体交付','赵野/申海宇；产研协同','9/7纪要计划','确认体检、机器人、标准版与整体交付范围','现场联调、跟餐、交付确认','9/7原计划；与9/20、历史9/23分别核对','S2/S3'],
 ['四方达','9/7—13先约200人试用','赵野','联调完成为纪要自报','回收实际试用日、问题和正式切换日','试用/切换回执','试用计划；未见实际完成回报','S2'],
 ['河北省体校','9/10前','产品/业务待确认','招投标材料原计划','核正式版本和业务接收结果','材料与回执','节点已到；完成情况待核','S2'],
 ['陕西举摔柔中心','9/11','业务/交付待确认','标准系统试用部署原计划','核试用环境、账号与培训','部署与试用记录','节点已到；完成情况待核','S2'],
 ['江西体科一','9/28软件交付','刘劲玮；研发待确认','原计划','核与体科医/9/24记录的项目及节点关系','服务器交接、上线确认','9/7原计划；命名与阶段待核','S2'],
 ['宁波卫生职业技术学院','9/22交付','边豪；采购/交付待核','9/7记录已中标','核签约、采购供货、安装培训','合同/发货/交付确认','9/7原计划；进度待回报','S2'],
 ['浙江体科所精准膳食','9月底软件交付','业务/产品/研发待确认','9/7记录已挂网','核投标结果、合同范围与交付门槛','合同/软件验收','9/7原计划','S2'],
 ['大兴区政府','9/7—13材料；复审日待定','王朔、赖清涛；业务协同','阶段材料形成；技术审核待确认','回应科信技术反馈并约复审','审查意见与确认记录','本周材料自报，客户审批未证实','S2/S3'],
 ['三级等保','原9/6备案、9/25报告争取目标','王朔；赖清涛','9/11仍等网安审核和电子备案证','核受理、复扫、签发实际节点','回函、备案证及测评报告','历史目标保留，当前外部日期待确认','S3/S13'],
 ['金斯瑞退款状态','修复与发布日待定','负责人待分配；交付协助','本周诊断已复现，未修改生产','防重复申请、全退状态保护及存量处理评审','部署版本、并发回归、状态回读','技术诊断；非修复完成','S7'],
 ['无锡新吴区菜谱','日志取证与修复日待定','交付/运维/研发待确认','代码反例已复现，现场路径未定','取保存/复制日志及版本，定位后排修复','脱敏日志、复现/回归记录','待现场取证','S10'],
 ['国信便利店补单','商务确认后排期','孙海琴；交付/研发待核','源码限制路径已核；现场未核','取得报错日志并冻结设备范围规则','日志、规则确认及回归','规则澄清；未实施','S3/S9'],
 ['410扫码器/滨州','厂家报告与真机窗口待定','尹旭亮、王金洋；厂商','审计与执行方案形成，未真机验收','完成厂家协议/报告、评审及真机复测','设备日志、扫码换盘与异常回归','方案不等于故障关闭','S3/S12'],
 ['保康中学','下周部署日期待定','孙倩倩；交付协同','9/11仍计划中','明确环境和部署日，完成业务冒烟','部署/测试/交接记录','未批准排期','S3'],
 ['央美开餐与交付材料','下周9/14—20，具体日待定','柴玉龙、赵野；产品/研发','手机端/管理端完成自报','现场支持，逐项核合同功能和资料','开餐、培训、问题关闭、签章','不等同整体验收','S3'],
 ['客户回访统筹','每月1—3项目；9月国康、滨州','孙海琴、李潇；交付联系客户','国康1次，报告待补','见回访计划全部21行；确认客户时间和数据','单项目报告、前后口径及来源','用户已定长期方向；具体预约待确认','S5']
]
nodes+=extra
nodes += [
 ['商户支付配置培训','原下周；现恢复日待定','李潇；赵野协调','9/11因无演示账号暂停','准备可用演示账号，再演示商户号/证书配置','培训记录与可复用操作检查单','暂停项，不能写培训完成','S1/S3'],
 ['湖南科技大学实训室','9/15后继续推进；验收延至四季度','业务负责人按确收表核对','9/7纪要记录验收推迟','重核客户实施与验收窗口','客户排期确认','原会议计划，非最新实绩','S2/S8'],
 ['玉溪体校','可能延至2027年','业务负责人按确收表核对','学校合并，预算暂停为9/7记录','核预算恢复与是否退出9月预估','业务与客户确认','不把历史计划金额算当月必达','S2/S8'],
 ['山东营地','预计11月启动','业务/交付待确认','9/7纪要计划','启动前确认范围、资源和客户窗口','启动与交付计划','未来意向，非已交付','S2'],
 ['山西体科所','领导调整，下一节点待定','业务负责人按确收表核对','9/7纪要记录外部依赖','核决策人及采购/交付时间变化','客户反馈和更新排期','历史状态待更新','S2/S8'],
 ['滨州二次增订','9/7—13推进商务合同','业务/商务；交付核设备范围','9/7设备及数量基本确定','核合同、到货与新增安装，不覆盖一期交付','增订合同与独立交付清单','计划结果待回报','S2'],
]
plan=ROOT/SOURCES['S6'][1]
for heading in ['## 一、服务器自动部署','## 二、系统自动化测试','## 三、智慧食堂售前采集系统接入企业微信']:
    for r in md_table(plan,heading):
        no,task,status,deliverable,date,dependency,acceptance=r
        nodes.append([task,date,'杜铭；'+dependency,status,deliverable,acceptance,'9/7制定计划；本周执行结果待核','S6'])
ws=wb.create_sheet('交付节点')
init_sheet(ws,'交付实施对接｜已知排期与待闭环事项','原计划、周会拟定目标与待回报分开。所有节点均保留来源日期；同项目不同阶段分别列示；回访全量见“回访计划”。本表未覆盖权限阻断后的最新在线更新。',
 ['项目/事项','时间节点（原口径）','负责人/协同','已知状态','下一步对接事项','完成凭证/验收要求','节点性质与边界','来源'],[26,31,34,39,46,42,39,10])
add_rows(ws,nodes,[r[-1] for r in nodes],{5:ORANGE})
ws.column_dimensions['H'].hidden=True

visits=list(csv.reader((ROOT/'work/weekly-reports/2026-W37/customer-return-visits/visit-register.csv').open(encoding='utf-8-sig')))
ws=wb.create_sheet('回访计划')
init_sheet(ws,'客户回访｜每月1—3项目','原21行全部保留。国康重复行待核；四方达原2026年1月未自动改为2027；首通3校与央美可能超出11月1—3点位，调整建议尚未确认。',visits[0],[7,22,27,36,18,16,64])
add_rows(ws,visits[1:],['S5']*21,{7:ORANGE})

revenue=json.loads((ROOT/SOURCES['S8'][1]).read_text())
ws=wb.create_sheet('9月确收历史计划')
init_sheet(ws,'9月确收｜9月7日历史计划核对','已读数科14条、团餐17条历史记录；不是当前完整财务表，不代表已确收/回款。空白仍未知，按部门分别核算，金额及阶段待商务财务回读。',
 ['项目','业务负责人','月计划（元）','当时预估（元）','当时填写实际（元）','部门','当前需核对'],[55,20,20,20,24,23,52])
rr=[]
for r in revenue:
    rr.append([r['project'],r['owner'],r['plan'],r['forecast'],r['actual'],r['department'],'核验合同范围、交付与验收单、财务确认条件和当前预估日期；未获9/12更新。'])
add_rows(ws,rr,['S8']*len(rr))
last=ws.max_row
for dept in ['数科业务中心','团餐业务中心']:
    total=ws.max_row+1
    ws.cell(total,1,dept+'已填值合计（非最新）')
    for c in range(3,6):
        col=get_column_letter(c); ws.cell(total,c,f'=SUMIF(F5:F{last},"{dept}",{col}5:{col}{last})')
        for r in range(5,total+1): ws.cell(r,c).number_format='#,##0.00'
    ws.cell(total,7,'仅汇总已填值，未填项未知。')

history=list(csv.reader((ROOT/SOURCES['S4'][1]).open(encoding='utf-8-sig')))
ws=wb.create_sheet('历史项目底册')
init_sheet(ws,'项目总表底册｜2026年9月5日快照','52条有名称记录，不代表52个当前唯一项目；原总表53行中1行无名称。用于核漏，不与本周成果合并计数；所有状态/时间为历史值，最新在线全量未取得。',
 ['项目名称','原明细链接','历史阶段','历史状态','历史预期上线日','历史验收日','历史验收状态','历史交付负责人','历史本周成果','历史下一门槛'],[30,24,16,16,21,20,23,25,37,38])
add_rows(ws,[[v or '未填' for v in r] for r in history[1:]],['S4']*52)
for r in range(5,ws.max_row+1):
    cell=ws.cell(r,2)
    if str(cell.value).startswith('https://'):
        cell.hyperlink=cell.value;cell.value='打开原项目明细'

for ws in wb: finish(ws)
wb.active=0
path=OUT/'数字技术中心与交付实施部周报_20260907-0913_初稿.xlsx'
wb.save(path)
# Separate one-sheet rendering input keeps the review overview readable.
preview=load_workbook(path)
for ws in list(preview):
    if ws.title!='本周汇报': preview.remove(ws)
preview.save(OUT/'周报汇报页_预览.xlsx')

check=load_workbook(path)
issues=[]
for ws in check:
    for row in ws:
        for c in row:
            if c.value is not None and (c.font.name!='微软雅黑' or c.font.sz!=12): issues.append(f'font:{ws.title}:{c.coordinate}')
            if isinstance(c.value,str) and ('20260309' in c.value or '1752868' in c.value): issues.append(f'old-template:{ws.title}:{c.coordinate}')
            if c.data_type=='e': issues.append(f'error:{ws.title}:{c.coordinate}')
assert not issues,issues
assert check['历史项目底册'].max_row-4==52
assert check['回访计划'].max_row-4==21
assert len(revenue)==31
assert len(check.sheetnames)==7
assert all((ROOT/p).exists() for _,p in SOURCES.values())
receipt={'status':'passed','date':'2026-09-12','week':'2026-W37','cutoff':'2026-09-12',
 'sheets':check.sheetnames,'project_rows':len(DATA['projects']),'product_rows':len(DATA['products']),
 'delivery_node_rows':len(nodes),'visit_rows':21,'historical_portfolio_rows':52,'historical_revenue_rows':31,
 'font':'微软雅黑 12','old_template_values':0,'cell_errors':0,
 'source_scope':'Live WeCom document read failed with 850003; historical data explicitly dated.',
 'revenue_filled_sums':{d:{k:sum(r[k] or 0 for r in revenue if r['department']==d) for k in ['plan','forecast','actual']} for d in ['数科业务中心','团餐业务中心']},
 'xlsx_sha256':hashlib.sha256(path.read_bytes()).hexdigest(),'xlsx_bytes':path.stat().st_size}
(HERE/'validation.json').write_text(json.dumps(receipt,ensure_ascii=False,indent=2)+'\n')
print(json.dumps(receipt,ensure_ascii=False,indent=2))