"""Build the user's compact illustrated weekly report; media stays local."""
from pathlib import Path
from copy import copy
import json,csv,html,hashlib,math,re,zipfile,shutil
from openpyxl import Workbook,load_workbook
from openpyxl.styles import Font,PatternFill,Alignment,Border,Side
from openpyxl.drawing.image import Image as XImage
from openpyxl.comments import Comment
from openpyxl.utils import get_column_letter
from openpyxl.worksheet.pagebreak import Break
from PIL import Image

HERE=Path(__file__).resolve().parent;ROOT=HERE.parents[3]
PACK=HERE/'output/周报图文包';ASSETS=PACK/'assets'
D=json.loads((HERE/'visual-data.json').read_text())
PREV=HERE.parent/'weekly-report-draft'
OLD=json.loads((PREV/'report-data.json').read_text())
SOURCE=load_workbook(PREV/'output/数字技术中心与交付实施部周报_20260907-0913_初稿.xlsx')
NAVY='18394B';BLUE='18649B';ORANGE='AD531B';GRAY='566675';LIGHT='EDF4F8';LINE='DDE7ED'
wb=Workbook();wb.remove(wb.active)

def setup(name,title,sub):
 s=wb.create_sheet(name);s.sheet_view.showGridLines=False;s.sheet_view.zoomScale=90
 for c in range(1,13):s.column_dimensions[get_column_letter(c)].width=11
 s.freeze_panes='A5';s.sheet_properties.pageSetUpPr.fitToPage=True
 s.page_setup.orientation='landscape';s.page_setup.paperSize=s.PAPERSIZE_A3
 s.page_setup.fitToWidth=1;s.page_setup.fitToHeight=0
 s.page_margins.left=s.page_margins.right=.3;s.page_margins.top=s.page_margins.bottom=.35
 merged(s,1,1,12,title,26,NAVY,'FFFFFF',True)
 merged(s,2,1,12,sub,36,'FFF4E9',ORANGE)
 s.print_title_rows='1:2'
 return s

def merged(s,r,a,b,text,height=24,bg=None,color=GRAY,bold=False):
 s.merge_cells(start_row=r,start_column=a,end_row=r,end_column=b)
 c=s.cell(r,a,text);c.font=Font(name='微软雅黑',size=12,color=color,bold=bold)
 c.alignment=Alignment(wrap_text=True,vertical='center',indent=1)
 if bg:
  for col in range(a,b+1):s.cell(r,col).fill=PatternFill('solid',fgColor=bg)
 s.row_dimensions[r].height=max(s.row_dimensions[r].height or 0,height)
 return c

def link(s,r,text,target):
 c=merged(s,r,1,12,text,25,color=BLUE,bold=True);c.hyperlink=target

def picture(s,name,r,col,width=410,height=260):
 p=ASSETS/name
 assert p.exists(),p
 im=XImage(p);factor=min(width/im.width,height/im.height);im.width*=factor;im.height*=factor
 s.add_image(im,f'{get_column_letter(col)}{r}')
 return im

def gallery(s,names,r):
 # Two side-by-side images, each band occupies 11 rows of 20 pt.
 for i in range(0,len(names),2):
  for rr in range(r,r+11):s.row_dimensions[rr].height=20
  for j,n in enumerate(names[i:i+2]):picture(s,n,r,1+j*6,460,275)
  r+=12
 return r

def table(s,r,headers,rows,widths=None):
 # Columns are merged into bands totalling the common 12-column canvas.
 widths=widths or [2,2,3,5];starts=[];c=1
 for n in widths:starts.append((c,c+n-1));c+=n
 for (a,b),v in zip(starts,headers):merged(s,r,a,b,v,27,BLUE,'FFFFFF',True)
 for vals in rows:
  r+=1;maxlines=1
  for (a,b),v in zip(starts,vals):
   text=str(v or '待确认');limit=(b-a+1)*10
   maxlines=max(maxlines,sum(max(1,math.ceil(len(t)/limit)) for t in text.split('\n')))
   merged(s,r,a,b,text,20,None,GRAY)
  s.row_dimensions[r].height=max(32,maxlines*19+8)
 return r+2

projects=setup('项目','本周项目｜进展与下一节点','2026.09.07—09.13 · 截至9/12。蓝色本周，橙色下一步；现场图按来源日期标注。')
r=4
merged(projects,r,1,12,'先保哈尔滨、三全联调与央美开餐；其余项目按现场问题和交付条件收口。',32,LIGHT,BLUE,True);r+=2
for i in range(0,len(D['projects']),2):
 for j,p in enumerate(D['projects'][i:i+2]):
  a=1+j*6;b=a+5
  merged(projects,r,a,b,p['name'],28,BLUE,'FFFFFF',True)
  for rr in range(r+1,r+12):projects.row_dimensions[rr].height=20
  picture(projects,p['image'],r+1,a,455,275)
  merged(projects,r+12,a,b,p['caption'],34,color=GRAY)
  merged(projects,r+13,a,b,'本周｜'+p['progress'],46,color=BLUE)
  merged(projects,r+14,a,b,'下一步｜'+p['next'],46,color=ORANGE)
  merged(projects,r+15,a,b,p['owner'],34,color=GRAY)
  cell=projects.cell(r,a);cell.comment=Comment(p['gate'],'状态边界')
 r+=18
 projects.row_breaks.append(Break(id=r-2))

selected=['哈尔滨','中央美院','滨州','三全','四方达','金斯瑞','国康','江西206']
remaining=[p for p in OLD['projects'] if not any(n in p[2] for n in selected)]
short=[]
for p in remaining:
 kind,stage,name,date,owner,current,nxt,risk,src=p
 # Full source wording remains in cell comments; visible rows carry only the actionable core.
 short.append([name,date,owner,(current.split('；')[0]+'；'+nxt.split('；')[0]).strip('。')])
other_start=r
r=table(projects,r,['其他项目','关键时间','责任/协同','进展与对接'],short,[2,2,3,5])
projects.row_breaks.append(Break(id=other_start+11))
visible_end=r-1
detail_heading=r
merged(projects,r,1,12,'完整协同节点（68条）｜点击左侧“＋”展开',30,LIGHT,BLUE,True);r+=1
nodes=[[SOURCE['交付节点'].cell(rr,c).value for c in range(1,9)] for rr in range(5,SOURCE['交付节点'].max_row+1)]
for n in nodes:
 for c,val in enumerate(n[:6],1):
  cell=merged(projects,r,2*c-1,2*c,str(val or '待确认'),42,color=GRAY)
  cell.comment=Comment('来源/边界：'+str(n[6])+'；'+str(n[7]),'节点来源')
 projects.row_dimensions[r].hidden=True;projects.row_dimensions[r].outlineLevel=1;r+=1
projects.row_dimensions[r].collapsed=True
projects.sheet_properties.outlinePr.summaryBelow=True
projects.print_area=f'A1:L{visible_end}'

product=setup('产品','本周产品｜原型、设备与标准材料','本周主题对应已有原型完整放入展示包；画面中的人名、指标和数量为演示数据，不计为项目实绩。')
r=4
for item in D['product_sections']:
 start=r
 merged(product,r,1,12,item['title'],30,BLUE,'FFFFFF',True);r+=1
 merged(product,r,1,12,item['text'],38,color=BLUE);r+=1
 merged(product,r,1,12,item['status'],34,color=ORANGE);r+=2
 r=gallery(product,item['images'],r)
 link(product,r,item['link_text']+' ↗',item['link']);r+=1
 merged(product,r,1,12,item['note'],28,color=GRAY);r+=3
 product.row_breaks.append(Break(id=r-2))
r=table(product,r,['事项','原定节点','工作内容','当前处理'],D['product_work'],[2,2,4,4])
product.print_area=f'A1:L{r-1}'

vis=setup('客户回访','客户回访｜只回答两个问题','每月直接回访1—3个项目；交付协助联系。每次形成项目报告，月度汇总、季度复盘。')
merged(vis,4,1,12,'① 当时是什么决定我们要采购这套系统？具体是A、B、C哪些问题或建设目标？',35,BLUE,'FFFFFF',True)
merged(vis,5,1,12,'② 使用前后有什么变化？有没有同口径的量化数据支撑？',35,BLUE,'FFFFFF',True)
r=7
for case in D['visits']:
 merged(vis,r,1,12,case['name'],29,NAVY,'FFFFFF',True);r+=1
 merged(vis,r,1,12,'为什么采购｜'+case['why'],36,color=BLUE);r+=2
 merged(vis,r,1,6,'BEFORE｜'+case['before'],52,LIGHT,GRAY)
 merged(vis,r,7,12,'AFTER｜'+case['after'],52,'FFF3E8',ORANGE);r+=2
 r=gallery(vis,case['images'],r)
 merged(vis,r,1,12,case['caption'],30,color=GRAY);r+=1
 merged(vis,r,1,12,'量化证据｜'+case['metric'],36,'FFF3E8',ORANGE,True);r+=1
 merged(vis,r,1,12,'下一步｜'+case['next'],30,color=GRAY);r+=3
 vis.row_breaks.append(Break(id=r-2))
rows=list(csv.DictReader((HERE.parent/'customer-return-visits/visit-register.csv').open(encoding='utf-8-sig')))
calendar=[]
for x in rows:calendar.append([x['原计划时间'],x['项目名称'],x['负责人'],x['当前状态']+'；'+x['下一步/待确认']])
r=table(vis,r,['回访时间','项目','负责人','安排/待补'],calendar,[2,2,2,6])
vis.print_area=f'A1:L{r-1}'

for s in wb:
 for row in s:
  for c in row:
   f=copy(c.font);f.name='微软雅黑';f.sz=12;c.font=f
 s.sheet_properties.tabColor=BLUE if s.title=='项目' else ORANGE
 s.oddFooter.center.text='内部周报 · 第 &P 页';s.oddFooter.center.size=9
wb.active=0
xlsx=PACK/'本周周报_图文精简版.xlsx';wb.save(xlsx)

# Derive the companion webpage from the reviewed pakco webpage template.
# The imported wrapper fires empty-iframe onload before its script is parsed.
# Guard only the local presentation copy; its later srcdoc load still initializes normally.
wrapper=PACK/'prototypes/health-pc-mobile.html'
raw=wrapper.read_text()
raw=raw.replace('onload="initPc()"','onload="if(typeof initPc===\'function\')initPc()"')
raw=raw.replace('onload="initMobile()"','onload="if(typeof initMobile===\'function\')initMobile()"')
wrapper.write_text(raw)
template=Path('/Users/jack/code/099-github/pakco-html/templates/webpage.html').read_text()
css='''
:root{--bg:#f3f6f8;--paper:#fff;--ink:#163447;--muted:#647686;--blue:#1b6eab;--orange:#b65b21;--line:#dbe5eb;--radius:16px;--font:"Microsoft YaHei","PingFang SC",sans-serif}
*{box-sizing:border-box}body{margin:0;background:var(--bg);color:var(--ink);font:16px/1.65 var(--font)}.page{max-width:1180px;margin:auto;padding:36px 24px}h1{font-size:36px;line-height:1.25;margin:10px 0}h2{font-size:25px;margin:0 0 14px}h3{font-size:19px;margin:8px 0}p{margin:8px 0}.hero{padding:22px 0}.kicker,small,.note{color:var(--muted)}.kicker{letter-spacing:.1em;font-weight:700}.nav{display:flex;gap:12px;flex-wrap:wrap;margin:22px 0}.nav a,.button{padding:9px 18px;background:var(--blue);color:white;text-decoration:none;border-radius:8px;display:inline-block}section{scroll-margin-top:20px;margin:30px 0}.grid{display:grid;grid-template-columns:repeat(2,minmax(0,1fr));gap:20px}.card{background:var(--paper);border:1px solid var(--line);border-radius:var(--radius);padding:24px;min-width:0}.card img{display:block;width:100%;height:300px;object-fit:contain;background:var(--bg);border-radius:10px}.gallery{display:grid;grid-template-columns:repeat(2,minmax(0,1fr));gap:16px}.gallery img{height:300px}.wide{grid-column:1/-1}.next{color:var(--orange)}.fact{color:var(--blue)}.note{font-size:13px}.questions{background:var(--ink);color:white;padding:24px;border-radius:var(--radius)}.ba{display:grid;grid-template-columns:1fr 1fr;gap:20px;margin:16px 0}.ba>div{padding:16px;background:var(--bg);border-radius:10px}.metric{padding:14px;background:#fff1e4;color:var(--orange);font-weight:700;border-radius:10px}video{display:block;width:100%;max-height:600px;background:#111;border-radius:12px}.media-pair{display:grid;grid-template-columns:1fr 1fr;gap:24px}.media-pair img{width:100%;height:580px;object-fit:contain}details{padding:14px;background:var(--paper);border:1px solid var(--line);border-radius:10px;margin-top:18px}summary{cursor:pointer;font-weight:700}iframe{width:100%;height:780px;border:1px solid var(--line);background:white;margin-top:16px}table{border-collapse:collapse;width:100%;font-size:14px}th,td{padding:12px;text-align:left;border-bottom:1px solid var(--line);vertical-align:top}th{color:var(--blue)}.table-wrap{overflow:auto}.page-footer{padding:24px 0;color:var(--muted);font-size:13px}a{color:var(--blue)}a:focus-visible,summary:focus-visible{outline:3px solid var(--orange)}@media(max-width:700px){.page{padding:18px 14px}h1{font-size:29px}.grid,.gallery,.ba,.media-pair{grid-template-columns:1fr}.card{padding:16px}.card img,.gallery img{height:auto;max-height:420px}.media-pair img{height:330px}iframe{height:650px}.nav a{padding:8px 12px}.table-wrap{max-width:100%}}'''
e=lambda x:html.escape(str(x or ''))
def img(name,caption=''):
 return f'<a href="assets/{e(name)}" target="_blank"><img loading="lazy" src="assets/{e(name)}" alt="{e(caption or name)}"></a>'
def htable(headers,rows):
 return '<div class="table-wrap"><table><thead><tr>'+''.join('<th>'+e(h)+'</th>' for h in headers)+'</tr></thead><tbody>'+''.join('<tr>'+''.join('<td>'+e(c)+'</td>' for c in r)+'</tr>' for r in rows)+'</tbody></table></div>'
body='<section class="hero"><div class="kicker">康比特 · 本周图文周报</div><h1>项目看进展，产品看变化，回访看价值。</h1><p>2026年9月7—13日 · 截至9月12日</p><nav class="nav"><a href="#projects">项目与交付</a><a href="#product">产品原型</a><a href="#health">体检视频</a><a href="#visits">客户回访</a></nav></section>'
body+='<section id="projects"><h2>项目与下一节点</h2><div class="grid">'
for p in D['projects']:
 body+=f'<article class="card"><h3>{e(p["name"])}</h3>{img(p["image"],p["caption"])}<p class="note">{e(p["caption"])}</p><p class="fact">{e(p["progress"])}</p><p class="next">{e(p["next"])}</p><p class="note">{e(p["owner"])}</p></article>'
body+='</div><details><summary>展开其他项目与完整协同节点</summary>'+htable(['项目','节点','责任','进展/对接'],short)+htable(['事项','时间','负责人','下一步'],[[n[0],n[1],n[2],n[4]] for n in nodes])+'</details></section>'
body+='<section id="health"><h2>健康体检一体机 · 2分52秒现场演示</h2><div class="card"><div class="media-pair">'+img('health-device.jpg','用户提供的体检设备照片')+'<video controls preload="metadata" playsinline poster="assets/health-video-70.jpg"><source src="video/health-check-demo.mp4" type="video/mp4">请使用支持HTML5视频的浏览器。</video></div><h3>一次体检 → 28天饮食与运动服务</h3><p>建档 → 个体方案 → 每日提醒 → 语音反馈 → 暂停/重启 → 周期复盘</p><p class="note">视频为用户提供的设备演示。28天连续服务为本周产品定义，尚需真实数据、提醒和语音试点验证。</p></div></section>'
body+='<section id="product"><h2>产品与完整原型</h2>'
for p in D['product_sections'][1:]:
 body+=f'<article class="card" style="margin-bottom:24px"><h3>{e(p["title"])}</h3><p>{e(p["text"])}</p><p class="next">{e(p["status"])}</p><div class="gallery">'+''.join(img(x,p['title']+' · 原型演示') for x in p['images'])+f'</div><p class="note">{e(p["note"])}</p><a class="button" target="_blank" href="{e(p["link"])}">{e(p["link_text"])}</a><details><summary>在这里展开完整原型</summary><iframe loading="lazy" sandbox="allow-scripts allow-same-origin allow-downloads" src="{e(p["link"])}" title="{e(p["title"])}"></iframe></details></article>'
body+='<details><summary>学校原型全部9页截图</summary><div class="gallery">'+''.join(img('proto-school-'+x+'.jpg','校园餐原型 · '+x) for x in ['home','tasks','candidates','editor','calculation','approval','preparation','analysis','reports'])+'</div></details>'
body+='<div class="card" style="margin-top:24px"><h3>李潇本周工作主题与标准材料</h3>'+htable(['事项','节点','工作内容','当前处理'],D['product_work'])+'</div></section>'
body+='<section id="visits"><h2>客户回访：重点是两个问题</h2><div class="questions"><h3>① 当时是什么决定我们采购这套系统？</h3><p>具体是哪几个问题、建设目标或选择因素？</p><h3>② 使用前后有什么量化变化？</h3><p>同口径的基线、结果、统计周期与数据来源在哪里？</p></div>'
for c in D['visits']:
 body+=f'<article class="card" style="margin-top:24px"><h3>{e(c["name"])}</h3><p class="fact">为什么采购：{e(c["why"])}</p><div class="ba"><div><b>BEFORE</b><p>{e(c["before"])}</p></div><div><b>AFTER</b><p>{e(c["after"])}</p></div></div><div class="gallery">'+''.join(img(x,c['name']+' · 现场图') for x in c['images'])+f'</div><p class="note">{e(c["caption"])}</p><p class="metric">{e(c["metric"])}</p><p>下一步：{e(c["next"])}</p></article>'
body+='<details><summary>每月1—3个项目 · 展开完整回访排期</summary>'+htable(['时间','项目','负责人','安排'],calendar)+'</details></section><footer class="page-footer">本机同步微盘与用户提供素材 · 原型/方案不等于上线，照片不替代验收与量化效果。最新在线全量仍受机器人授权过期影响。</footer>'
page=template[:template.index('<body')]+f'<body class="webpage"><div class="page">{body}</div></body></html>'
page=re.sub(r'<link[^>]+>', '',page)
page=re.sub(r'<title>.*?</title>','<title>本周周报 · 项目、产品与客户回访</title>',page)
page=page.replace('</head>','<style>'+css+'</style></head>')
(PACK/'weekly-visual.html').write_text(page)
(PACK/'使用说明.txt').write_text('先打开 本周周报_图文精简版.xlsx。\n视频和完整原型：双击 weekly-visual.html。\n整个文件夹一起保留/发送，Excel中的相对链接才可使用。\n项目页下方左侧“＋”可展开68条完整协同节点。\n本包为内部资料，不公开发布。\n')

check=load_workbook(xlsx)
assert check.sheetnames==['项目','产品','客户回访']
assert len(nodes)==68 and len(rows)==21
assert all(c.font.name=='微软雅黑' and c.font.sz==12 for s in check for row in s for c in row if c.value is not None)
assert len(check['项目']._images)==8
assert len(check['产品']._images)==13
assert len(check['客户回访']._images)==5
receipt={'sheets':check.sheetnames,'embedded_images':sum(len(s._images) for s in check),'project_cards':8,'remaining_project_rows':len(remaining),'preserved_delivery_nodes':len(nodes),'visit_register_rows':len(rows),'school_prototype_pages':9,'font':'微软雅黑 12','live_wedrive':'850003 authorization expired; used local synchronized WeDrive files','xlsx_sha256':hashlib.sha256(xlsx.read_bytes()).hexdigest(),'xlsx_bytes':xlsx.stat().st_size,'prototype_screenshots':len(list(ASSETS.glob('proto-*.jpg')))}
(HERE/'validation.json').write_text(json.dumps(receipt,ensure_ascii=False,indent=2)+'\n')
print(json.dumps(receipt,ensure_ascii=False,indent=2))