"""W38 evidence-backed illustrated workbook. Raw sources and artifacts stay local."""
from pathlib import Path
from copy import copy
import json,hashlib,shutil,math
from openpyxl import Workbook,load_workbook
from openpyxl.styles import Font,PatternFill,Alignment,Border,Side
from openpyxl.comments import Comment
from openpyxl.drawing.image import Image as XImage
from openpyxl.drawing.spreadsheet_drawing import OneCellAnchor,AnchorMarker
from openpyxl.drawing.xdr import XDRPositiveSize2D
from openpyxl.utils import get_column_letter
from openpyxl.worksheet.pagebreak import Break
from PIL import Image,ImageOps

HERE=Path(__file__).resolve().parent; ROOT=HERE.parents[3]; OUT=HERE/'output'; A=OUT/'assets'; DEL=OUT/'精简版'; DEL.mkdir(exist_ok=True)
NAVY='193A4D';BLUE='216F92';TEAL='277D73';ORANGE='AC6224';GRAY='566875';LIGHT='EDF4F7';LINE='DCE5EA';WHITE='FFFFFF'
MEETING='https://zhctpmt.yyangpt.cn/work/2026-09-18-weekly-meeting/weekly-meeting-content.html'
SPACE='https://doc.weixin.qq.com/smartsheet/s3_AHkAH3hlAHwCNUzm1hPTKQQGwNBft_a'
COLLECT='https://doc.weixin.qq.com/sheet/e3_Ac0AJgaVAEYCN2YUa9YSsQKC6Sx37'
PACK='https://doc.weixin.qq.com/sheet/e3_AdYAzAZnACUCNCp19nO7KSR6LnvvB'
# Preserve the latest illustrated template's navy/blue 12-column card layout.
wb=Workbook();wb.remove(wb.active)
images=json.loads((OUT/'image-manifest.json').read_text());image_map={m['key']:Path(m['local']) for m in images}
for key,source in [('harbin-health','work/weekly-reports/2026-W36/weekly-0907-project-photos/assets/harbin-health.jpg'),('offline','work_android/CusumptionMachine/announcements/assets/offline-order-release/02-offline-order-list.png'),('school','work/weekly-reports/2026-W37/project-cockpit/output/项目驾驶舱周报包/assets/proto-school-analysis.jpg')]:
 src=ROOT/source; dst=A/(key+src.suffix);shutil.copy2(src,dst);image_map[key]=dst
 images.append(dict(key=key,source=source,local=str(dst),sha256=hashlib.sha256(src.read_bytes()).hexdigest()))
for i in range(3):image_map[f'certificate-{i}']=A/f'certificate-{i}.png'

def box(s,r1,c1,r2,c2,text='',bg=None,color=GRAY,bold=False,link=None,source=None):
 s.merge_cells(start_row=r1,start_column=c1,end_row=r2,end_column=c2)
 c=s.cell(r1,c1,text);c.font=Font(name='微软雅黑',size=12,bold=bold,color=color)
 c.alignment=Alignment(vertical='center',wrap_text=True,indent=1)
 if bg:
  for row in s.iter_rows(min_row=r1,max_row=r2,min_col=c1,max_col=c2):
   for q in row:q.fill=PatternFill('solid',fgColor=bg)
 if link:c.hyperlink=link
 if source:c.comment=Comment(source,'证据来源')
 return c

def sheet(name):
 s=wb.create_sheet(name);s.sheet_view.showGridLines=False;s.sheet_view.zoomScale=85
 for c in range(1,13):s.column_dimensions[get_column_letter(c)].width=13
 s.sheet_properties.pageSetUpPr.fitToPage=True
 s.page_setup.orientation='landscape';s.page_setup.paperSize=s.PAPERSIZE_A4;s.page_setup.fitToWidth=1;s.page_setup.fitToHeight=0
 s.page_margins.left=s.page_margins.right=.28;s.page_margins.top=s.page_margins.bottom=.28
 s.freeze_panes='A5';s.oddFooter.center.text='康比特数字技术中心  |  内部周报  |  &P'
 s.oddFooter.center.size=9
 return s

def page(s,title,subtitle):
 n=getattr(s,'_pages',0);r=n*28+1;s._pages=n+1
 for i in range(r,r+28):s.row_dimensions[i].height=20
 if n:s.row_breaks.append(Break(id=r-1))
 box(s,r,1,r+1,12,title,NAVY,WHITE,True)
 box(s,r+2,1,r+3,12,subtitle,LIGHT,GRAY)
 box(s,r+27,1,r+27,12,'9月14—20日周报  |  资料截至9月19日  |  9月20日精简版',color=GRAY)
 s.print_area=f'A1:L{r+27}'
 return r

def picture(s,key,r,c,maxw,maxh):
 src=image_map[key]
 im=ImageOps.exif_transpose(Image.open(src)).convert('RGB');im.thumbnail((1900,1900))
 dest=A/(key+'-display.jpg');im.save(dest,quality=93)
 x=XImage(dest);f=min(maxw/x.width,maxh/x.height);x.width*=f;x.height*=f
 # Center inside its reserved box, without stretching or cropping evidence.
 x.anchor=OneCellAnchor(_from=AnchorMarker(col=c-1,row=r-1,colOff=int((maxw-x.width)/2*9525),rowOff=int((maxh-x.height)/2*9525)),ext=XDRPositiveSize2D(int(x.width*9525),int(x.height*9525)))
 s.add_image(x)

def para(s,r,c1,c2,title,body,height=3,color=BLUE):
 box(s,r,c1,r,c2,title,LIGHT,color,True)
 box(s,r+1,c1,r+height,c2,body)

# The user's latest order and product-state correction override older report conventions.
for key,source in [('rongcheng','modules/product/products/smart-canteen/product-solutions/2026-09-17-cad-canteen-3d/v2/local-assets/preview-final.png'),('scale','output/inventory-scale-20260916/assets/cpt-inventory-scale-brochure.png'),('gov','work/weekly-reports/2026-W37/project-cockpit/output/项目驾驶舱周报包/assets/proto-supply.jpg')]:
 image_map[key]=ROOT/source

def grid(s,r,rows,source):
 bands=[(1,2,'客户 / 项目'),(3,7,'本周工作与形成结果'),(8,10,'当前问题 / 下一步'),(11,12,'责任人')]
 for a,b,t in bands:box(s,r+4,a,r+4,b,t,BLUE,WHITE,True)
 for i,row in enumerate(rows):
  q=r+5+i*3
  for (a,b,_),v in zip(bands,row):box(s,q,a,q+2,b,v,LIGHT if i%2==0 else None,color=BLUE if a==1 else GRAY,bold=a==1,source=source)

s=sheet('售前支持');r=page(s,'01 售前支持｜把客户需求变成可判断、可推进的方案','需求说清 → 匹配产品 → 算清投入 → 备齐资料')
for a,b,title,body in [
(1,3,'需求与方案','明确场景、流程和配置\n容城3D、教委及体科所方案'),
(4,6,'产品与差距','核对参数、接口和硬件\n苏美达、中煤响应评估'),
(7,9,'投入与条件','明确开发量及交付前提\n第五医院估算、宣武审阅'),
(10,12,'资料与复用','成套提供介绍与对接资料\n彩页、手册、视频、投标材料')]:
 box(s,6,a,7,b,title,BLUE,WHITE,True);box(s,8,a,10,b,body,LIGHT)
picture(s,'rongcheng',12,1,720,245);picture(s,'scale',12,9,360,245)
box(s,22,1,23,8,'容城：可调整设备、摆位并导出清单的3D方案',color=BLUE,link='https://img.kxunpt.cn/public/ai-marketing-platform/modules/product/products/smart-canteen/product-solutions/2026-09-17-cad-canteen-3d/v2/smart-canteen-3d-v2.html')
box(s,22,9,23,12,'安达泰康：出入库秤资料包',color=BLUE)
box(s,25,1,27,12,'下一步｜收齐客户反馈，确认需求偏差、配置报价和定制周期，复用本周方案与资料。',LIGHT,ORANGE,True)

s=sheet('项目');r=page(s,'02 交付项目｜已有进展，重点补齐现场主流程','开发完成、现场测试、客户验收分别确认')
bands=[(1,3,'项目'),(4,8,'本周进展'),(9,12,'下一步')]
for a,b,t in bands:box(s,5,a,5,b,t,BLUE,WHITE,True)
rows=[
('哈尔滨','设备安装、机器人培训及接口联调完成；现场全流程自报80%。','补菜品同步、现场复测和交付视频。'),
('三全','支付宝配置完成；现场权限和食安点位已协调，其他集成未闭环。','先完成支付与食安部署，确认其余对接顺序。'),
('四方达 / 重庆','四方达初始化完成，端口方案测试50%；重庆推荐升级上线，扫码认证70%。','分别完成试运行验证、扫码真机及终端显示。'),
('保康 / 央美','保康部署与一楼摄像头调试完成；央美一卡通补传同步完成，食安设备联调。','完成各餐厅设备联调与异常恢复复测。'),
('张家港 / 机场','张家港接口及部署包完成；机场13台临时消费机完成软件联调。','张家港现场部署；机场账户流水查询排期。'),
('滨州 / 金斯瑞 / 国康','滨州核销待收口；金斯瑞小票、地址功能完成；国康售卖柜联调完成。','核销上线、现场版本复测、简化取餐流程。'),
('国信 / 江西 / 安庆','国信补单上线；江西体重管理等模块开发完成；安庆大屏调试完成。','报表开发、模块测试上线与现场支持。')]
for i,row in enumerate(rows):
 q=6+i*3
 for (a,b,_),t in zip(bands,row):box(s,q,a,q+2,b,t,LIGHT if i%2==0 else None,color=BLUE if a==1 else GRAY,bold=a==1,source='9/18周会、9/19在线交付周报。百分比为团队自报。')

s=sheet('交付物建设');r=page(s,'03 交付物建设｜让一次交付留下可交接、可复用的资料','两份表衔接：项目采集表 + 交付物料准备包')
for a,b,title,body in [
(1,3,'① 进场前：问清','需求、网络、部署\n接口、设备、基础数据'),
(4,6,'② 实施前：备齐','软硬件前置条件\n手册、物料、易损备件'),
(7,9,'③ 实施中：留下','部署、调试、培训\n前后对比及就餐流程'),
(10,12,'④ 完成后：复用','7项项目核心信息\n客户反馈、案例图文视频')]:
 box(s,6,a,8,b,title,BLUE,WHITE,True);box(s,9,a,12,b,body,LIGHT)
para(s,15,1,6,'已形成','采集、准备、回收与归档要求已建表；\n重庆、滨州已填写部分项目核心信息。',3)
para(s,15,7,12,'还需补齐','逐项目核对照片、视频与实际配置；\n补客户效果反馈，不能把建表当作收齐。',3,color=ORANGE)
box(s,21,1,23,12,'分工｜项目交付负责人收口资料 → 推广中心鲁洁组织包装、补拍与复用。',LIGHT,TEAL,True)
box(s,25,1,27,12,'下一步｜先把重庆、滨州两份案例资料包补完整，再按同一清单用于其他项目。',color=ORANGE,bold=True,source='建议动作；两份在线表实际内容与填报情况已核对。')
s['A3'].hyperlink=COLLECT;s['A15'].comment=Comment('来源：'+COLLECT+'；'+PACK,'来源')

s=sheet('产品');r=page(s,'04 产品｜两条营养产品线设计完成，正在开发','政企营养与学校营养：按已定方案推进实现、联调和测试')
for a,b,title,key,nxt in [(1,6,'政企营养｜开发中','gov','带量食谱、食谱解读、营养分析'),(7,12,'学校营养｜开发中','school','供餐模板、食谱编制、审核发布与配餐推荐')]:
 box(s,6,a,7,b,title,BLUE,WHITE,True);picture(s,key,9,a,550,170);box(s,16,a,17,b,nxt,color=BLUE,source='用户最新确认两条产品均为设计完成、开发中；图为设计展示。')
picture(s,'certificate-0',19,1,170,205);picture(s,'certificate-1',19,3,170,205)
box(s,19,5,21,12,'产品增量｜离线订单、订单详情、补打小票等已完成；商户移动端原型待评审。',LIGHT,BLUE)
box(s,22,5,24,12,'新增软著｜营养点餐、消费终端营养结算，均于9月16日登记。',color=TEAL,source='2026SR0946678、2026SR0946737，已核验PDF原件。')
box(s,25,5,27,12,'下一步｜营养主流程开发与测试；完善移动端方案和标准发布资料。',LIGHT,ORANGE,True)

# Keep source details accessible without expanding the presentation.
old=load_workbook(OUT/'归纳版'/'本周周报_售前归纳-交付-交付物-产品_20260914-0920.xlsx')
for s in wb:
 box(s,30,1,30,12,'完整说明与项目明细｜点击左侧“＋”展开（不打印）',LIGHT,BLUE,True)
 rr=31
 for row in old[s.title]:
  for c in row:
   if c.value is None:continue
   box(s,rr,1,rr+1,12,str(c.value),source=f'归纳版 {s.title}!{c.coordinate}')
   for n in [rr,rr+1]:s.row_dimensions[n].height=20;s.row_dimensions[n].hidden=True;s.row_dimensions[n].outlineLevel=1
   rr+=2
 s.row_dimensions[30].collapsed=True;s.sheet_properties.outlinePr.summaryBelow=False;s.print_area='A1:L28'
 for row in s:
  for c in row:
   f=copy(c.font);f.name='微软雅黑';f.sz=12;c.font=f
out=DEL/'本周周报_四页精简版_20260914-0920.xlsx';wb.save(out)
w=load_workbook(out)
assert w.sheetnames==['售前支持','项目','交付物建设','产品']
assert all(c.font.name=='微软雅黑' and c.font.sz==12 for s in w for row in s for c in row if c.value is not None)
count=lambda book:sum(len(str(c.value)) for s in book for row in s.iter_rows(max_row=28 if book is w else (56 if s.title=='售前支持' else s.max_row)) for c in row if c.value is not None)
record={'file':out.name,'sheets':w.sheetnames,'pages':4,'images':sum(len(s._images) for s in w),'main_text_chars':count(w),'sha256':hashlib.sha256(out.read_bytes()).hexdigest(),'details':'collapsed after row 30; excluded from print','font':'微软雅黑12','data_cutoff':'2026-09-19','revision_date':'2026-09-20'}
(HERE/'concise-validation.json').write_text(json.dumps(record,ensure_ascii=False,indent=2)+'\n');print(json.dumps(record,ensure_ascii=False))