from pathlib import Path
from copy import copy
import json,hashlib,math
from openpyxl import load_workbook
from openpyxl.styles import Font,PatternFill,Alignment,Border,Side
from openpyxl.drawing.image import Image
from openpyxl.comments import Comment
from openpyxl.utils import get_column_letter
from openpyxl.worksheet.pagebreak import Break

HERE=Path(__file__).resolve().parent;PACK=HERE/'output/项目驾驶舱周报包';A=PACK/'assets'
D=json.loads((HERE/'output/reviewed-projects.json').read_text());M=D['metrics'];H=json.loads((HERE/'output/product-highlights.json').read_text())
V=json.loads((HERE.parent/'weekly-report-visual/visual-data.json').read_text())
source=HERE.parent/'weekly-report-visual/output/周报图文包/本周周报_图文精简版.xlsx'
wb=load_workbook(source)
NAVY='17394E';BLUE='2869AA';ORANGE='A7681E';GRAY='607184'
colors={'已报完成':('E5F1EC','2B7050'),'进行中':('FFF0DB','98600E'),'阻塞':('FBE6E4','A34843'),'记录冲突':('FBE6E4','A34843'),'计划':('E6EEF9','326AB0'),'待核':('EEF1F4','6F7E8D')}

def merge(s,r,a,b,text,height=28,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)
 s.row_dimensions[r].height=max(s.row_dimensions[r].height or 0,height)
 if bg:
  for col in range(a,b+1):s.cell(r,col).fill=PatternFill('solid',fgColor=bg)
 return c
def create(name,index,title,note):
 if name in wb:wb.remove(wb[name])
 s=wb.create_sheet(name,index);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='A4';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;s.print_title_rows='1:2'
 merge(s,1,1,12,title,30,NAVY,'FFFFFF',True);merge(s,2,1,12,note,38,'F0F5F9',GRAY)
 return s
def picture(s,name,r,c,w=455,h=275):
 im=Image(A/name);scale=min(w/im.width,h/im.height);im.width*=scale;im.height*=scale;s.add_image(im,f'{get_column_letter(c)}{r}')
def imageband(s,names,r,h=275):
 for i in range(0,len(names),2):
  for n in range(r,r+11):s.row_dimensions[n].height=20
  for j,name in enumerate(names[i:i+2]):picture(s,name,r,1+j*6,455,h)
  r+=12
 return r

def fingerprint(s):
 # Business content/layout and drawings are checked on independent readbacks.
 payload={'values':[(c.coordinate,str(c.value),c.font.name,c.font.sz,c.number_format) for row in s for c in row if c.value is not None],
 'merges':sorted(str(x) for x in s.merged_cells.ranges),'rows':[(k,v.height,v.hidden) for k,v in s.row_dimensions.items()],
 'images':[(im.width,im.height,hashlib.sha256(im._data()).hexdigest()) for im in s._images]}
 return hashlib.sha256(json.dumps(payload,ensure_ascii=False,sort_keys=True).encode()).hexdigest()
baseline=fingerprint(load_workbook(source)['客户回访'])

s=create('项目',0,'交付项目驾驶舱｜整体、重点与逐项目详情','2026-09-12在线导出。54条项目总表记录；本周9/7—9/13。项目状态、阶段与验收分开，不以阶段换算完成率。')
for i,(label,value,note) in enumerate([('总表项目记录',54,'37有编号 / 17未填'),('交付阶段',14,'售后36 / 售前3 / 筹备1'),('本周工作记录',24,'4/5人填写本周'),('2026年条目',21,'年度规范化后计数')]):
 a=1+i*3;merge(s,4,a,a+2,label,25,color=BLUE,bold=True);merge(s,5,a,a+2,str(value),38,'EAF2FA',BLUE,True);merge(s,6,a,a+2,note,30)
merge(s,8,1,12,'层级一｜全量54条 → 团餐32 / 数科18 / 智能健身3 / 内部1 → 阶段、场景与部署结构',32,NAVY,'FFFFFF',True)
r=10
for i,pair in enumerate([['chart-business.png','chart-type.png'],['chart-stage.png','chart-deployment.png'],['chart-status.png','chart-complexity.png']]):
 r=imageband(s,pair,r)
 if i==1:s.row_breaks.append(Break(id=r-1))
merge(s,r,1,12,'层级二｜5人本周工作结构与排期：24条有效记录；18个人日有多项目标记，需核时段与远程/现场安排。',36,NAVY,'FFFFFF',True);r+=2
r=imageband(s,['chart-people.png','chart-schedule.png'],r)
merge(s,r,1,12,'工作记录不是工时。王金洋未填本周，不是零工作；60格排期按项目—人员—日统计，不计算人力饱和度。',36,color=GRAY);r+=3
s.row_breaks.append(Break(id=r-1))
merge(s,r,1,12,'层级三｜8个重点项目交付关口：已报完成 / 进行中 / 阻塞或冲突 / 计划 / 待核',30,NAVY,'FFFFFF',True);r+=1
headers=['重点项目','范围/方案','设备/环境','集成/测试','培训/支持','交接/下一关口']
for i,h in enumerate(headers):merge(s,r,1+i*2,2+i*2,h,28,'EDF3F8',BLUE,True)
r+=1
for f in D['focus']:
 c=merge(s,r,1,2,f['name'],68,color=BLUE,bold=True);c.hyperlink='project-cockpit.html?project='+f['id']
 for i,g in enumerate(f['gates']):
  bg,col=colors[g['state']];merge(s,r,3+i*2,4+i*2,g['label']+'\n'+g['state'],68,bg,col)
 r+=1
r+=2;s.row_breaks.append(Break(id=r-1))
for i in range(0,len(D['focus']),2):
 for j,f in enumerate(D['focus'][i:i+2]):
  a=1+j*6;b=a+5
  merge(s,r,a,b,f['name'],30,BLUE,'FFFFFF',True)
  for n in range(r+1,r+12):s.row_dimensions[n].height=20
  if f['image']:picture(s,f['image'],r+1,a)
  else:
   # A truthful milestone graphic is used when no verified project photo is available.
   for k,g in enumerate(f['gates']):
    bg,col=colors[g['state']];merge(s,r+1+k*2,a,b,g['label']+'｜'+g['state'],34,bg,col)
  merge(s,r+12,a,b,f['latest'],56,color=BLUE)
  merge(s,r+13,a,b,f['next'],72,color=ORANGE)
  merge(s,r+14,a,b,f['note'],64)
  c=merge(s,r+15,a,b,'打开完整项目详情 ↗',28,color=BLUE,bold=True);c.hyperlink='project-cockpit.html?project='+f['id']
 r+=18;s.row_breaks.append(Break(id=r-1))

merge(s,r,1,12,'层级四｜全部54条项目：点击项目名打开本周记录、排期、内容与关联售后详情',32,NAVY,'FFFFFF',True);r+=1
for biz in M['businesses']:
 ps=[p for p in D['projects'] if p['business']==biz]
 merge(s,r,1,12,biz+'｜'+str(len(ps))+'条',30,'E4EDF5',BLUE,True);r+=1
 for a,b,title in [(1,2,'项目'),(3,4,'场景/产品'),(5,6,'阶段/负责人'),(7,9,'项目内容与价值定位'),(10,12,'本周与下一步')]:merge(s,r,a,b,title,28,'F0F4F7',BLUE,True)
 r+=1
 for p in ps:
  profile=p.get('profile',{});scope=p['overview'] or p['software'] or profile.get('核心问题') or p['products'] or '项目内容待补'
  value=profile.get('客户价值')
  if value:scope+='；价值定位：'+value
  latest=p.get('focus',{}).get('latest') or ('；'.join(x['work'] for x in p['weekly']) if p['weekly'] else p.get('reportContext',{}).get('actual','当周周报未匹配；不代表未开展工作'))
  nxt=p.get('focus',{}).get('next') or p['gate'] or p['expected'] or '下一日期待补'
  vals=[p['name'],p['type']+'\n'+(p['products'] or '产品未填'),p['stage']+' / '+p['status']+'\n'+('、'.join(p['owners']) or '负责人未填'),scope[:130]+('…' if len(scope)>130 else ''),(latest[:85]+('…' if len(latest)>85 else ''))+'\n下一步：'+nxt[:85]]
  bands=[(1,2),(3,4),(5,6),(7,9),(10,12)]
  heights=[]
  for (a,b),text in zip(bands,vals):
   cap=max(8,(b-a+1)*5);lines=sum(max(1,math.ceil(len(line)/cap)) for line in text.split('\n'));heights.append(lines*17+12)
   c=merge(s,r,a,b,text,30,color=BLUE if a==1 else GRAY,bold=a==1)
   c.comment=Comment('来源：项目总表第'+str(p['sourceRow'])+'行；'+scope+'\n本周：'+latest+'\n下一步：'+nxt,'来源与完整内容')
   if a==1:c.hyperlink='project-cockpit.html?project='+p['id']
  s.row_dimensions[r].height=min(280,max(heights));r+=1
 r+=1

merge(s,r,1,12,'总表外本周支持｜11条工作保留原名，待建立关联，不计入总表54条',30,NAVY,'FFFFFF',True);r+=1
for w in D['unmatchedWeekly']:
 merge(s,r,1,3,w['project'],45,color=BLUE,bold=True);merge(s,r,4,5,w['person']+' / '+w['category'],45);merge(s,r,6,12,w['work'][:160],75);r+=1
print_end=r-1
merge(s,r,1,12,'5人本周24条原工作记录｜点击左侧“＋”展开',30,'EDF4F8',BLUE,True);r+=1
for w in D['weekly']:
 merge(s,r,1,2,w['person'],90);merge(s,r,3,4,w['project'],90);merge(s,r,5,10,w['work'],150);merge(s,r,11,12,w['period'],90)
 s.row_dimensions[r].hidden=True;s.row_dimensions[r].outlineLevel=1;r+=1
s.row_dimensions[r].collapsed=True;s.print_area=f'A1:L{print_end}'

# The user subsequently authorized a concise product update; retain its validated media.
s=create('产品',1,'产品与研发｜6个重点与对应原型','依据2026-09-12在线《2026年组长周计划&总结》。只讲关键成果、未闭环与下一节点；原型/视频继续保留。')
r=4
for i,h in enumerate(H):
 merge(s,r,1,12,h['tag']+'｜'+h['title'],30,BLUE,'FFFFFF',True);r+=1
 merge(s,r,1,12,'本周：'+h['result'],50,color=BLUE);r+=1
 merge(s,r,1,12,'下一步：'+h['next'],45,color=ORANGE);r+=1
 c=merge(s,r,1,12,h['owner']+'｜'+h['boundary'],35);c.comment=Comment(h['source'],'在线周计划来源');r+=2
 if i in [2,5]:s.row_breaks.append(Break(id=r-1))
for item in V['product_sections']:
 merge(s,r,1,12,item['title'],30,NAVY,'FFFFFF',True);r+=1
 merge(s,r,1,12,item['text'],40,color=BLUE);r+=2
 r=imageband(s,item['images'],r)
 c=merge(s,r,1,12,item['link_text']+' ↗',28,color=BLUE,bold=True);c.hyperlink=item['link'];r+=1
 merge(s,r,1,12,item['note'],32);r+=3;s.row_breaks.append(Break(id=r-1))
s.print_area=f'A1:L{r-2}'

for s in wb:
 for row in s:
  for c in row:
   f=copy(c.font);f.name='微软雅黑';f.sz=12;c.font=f
wb.active=0
out=PACK/'本周周报_图文精简版.xlsx';wb.save(out)
read=load_workbook(out)
assert read.sheetnames==['项目','产品','客户回访']
assert fingerprint(read['客户回访'])==baseline
assert len(read['产品']._images)==13
assert all(c.font.name=='微软雅黑' and c.font.sz==12 for s in read for row in s for c in row if c.value is not None)
receipt={'sheets':read.sheetnames,'project_rows':54,'focus_projects':8,'charts':8,'weekly_records':24,'people':5,'product_highlights':6,'product_images':13,'customer_visit_unchanged':True,'customer_visit_fingerprint':baseline,'xlsx_sha256':hashlib.sha256(out.read_bytes()).hexdigest(),'xlsx_bytes':out.stat().st_size,'font':'微软雅黑12'}
(HERE/'workbook-validation.json').write_text(json.dumps(receipt,ensure_ascii=False,indent=2)+'\n')
print(json.dumps(receipt,ensure_ascii=False,indent=2))