from pathlib import Path
from copy import copy
import json, hashlib, shutil, subprocess, os
from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from pypdf import PdfReader
import pypdfium2 as pdfium
from PIL import Image

ROOT=Path(__file__).resolve().parents[1]
EV=ROOT/'evidence/matrix-simplification-20261007'
SOURCE=EV/'软件版本功能对比_重排前.xlsx'
TARGET=ROOT/'output/智慧食堂软件版本功能对比.xlsx'
protected=[ROOT/'output/智慧食堂软件报价单.xlsx',ROOT/'output/服务器选型与价格参考.xlsx']
hashes={p.name:hashlib.sha256(p.read_bytes()).hexdigest() for p in protected}
W=load_workbook(SOURCE)
M=W['模块对比'];D=W['细项参考']
old_names=[M.cell(3,c).value for c in range(4,9)]
order=[0,1,2,4,3]
names=['基础结算','AI识别','按克计费','营养结算','全部结算']
prices=[M.cell(4,i+4).value for i in order]
records=[]
core_ids={*range(44,49),*range(50,53),*range(56,59),60,61,62,71}
extra_ids={4,5,31,*range(37,44),49,*range(53,56),59,63,*range(64,67),*range(72,82),*range(96,102)}
manage_ids=set(range(82,87))
for r in range(4,D.max_row+1):
    fid=D.cell(r,1).value
    if not fid:continue
    n=int(fid[1:]);values=[D.cell(r,c).value for c in range(1,11)]
    where='core' if n in core_ids else ('extra' if n in extra_ids else ('manage' if n in manage_ids else 'common'))
    records.append({'id':fid,'n':n,'module':values[1],'feature':values[2],'terminal':values[3],
                    'marks':[values[4+i] for i in order],'where':where,'comment':copy(D.cell(r,3).comment),
                    'prior_note':values[9]})
assert len(records)==101 and len({x['id'] for x in records})==101
parts={k:[x for x in records if x['where']==k] for k in ['core','common','extra','manage']}
assert {k:len(v) for k,v in parts.items()}=={'core':15,'common':46,'extra':35,'manage':5}
summary={k:[M.cell(r,i+4).value for i in order] for k,r in [('people',5),('pain',6),('flow',7)]}
MONEY='#,##0" 元";[Red]-#,##0" 元";0" 元"'
NAVY,TEAL,PALE='183B56','007F79','EFF6FA'


def setup(s,widths,portrait=False):
    from openpyxl.utils import get_column_letter
    for c,w in enumerate(widths,1):s.column_dimensions[get_column_letter(c)].width=w
    s.sheet_view.showGridLines=False;s.sheet_view.zoomScale=90
    s.page_setup.paperSize=s.PAPERSIZE_A3 if not portrait else s.PAPERSIZE_A4
    s.page_setup.orientation='landscape' if not portrait else 'portrait'
    s.page_setup.fitToWidth=1;s.page_setup.fitToHeight=0
    s.sheet_properties.pageSetUpPr.fitToPage=True
    s.page_margins.left=s.page_margins.right=.25;s.page_margins.top=s.page_margins.bottom=.25
    s.oddFooter.center.text='康比特数字体育科技 · 2026-10-07';s.oddFooter.right.text='&P / &N'


def row(s,r,vals,head=False,height=28):
    s.row_dimensions[r].height=height
    for c,v in enumerate(vals,1):
        x=s.cell(r,c,v)
        x.font=Font(name='Microsoft YaHei',size=11,bold=head,color='FFFFFF' if head else NAVY)
        x.fill=PatternFill('solid',fgColor=TEAL if head else (PALE if r%2==0 else 'FFFFFF'))
        x.alignment=Alignment(vertical='center',wrap_text=True)
        x.border=Border(bottom=Side(style='hair',color='CBD8E2'))


def band(s,r,text,last,title=False,height=29):
    s.merge_cells(start_row=r,start_column=1,end_row=r,end_column=last)
    x=s.cell(r,1,text);x.font=Font(name='Microsoft YaHei',size=18 if title else 11,bold=title,color='FFFFFF' if title else NAVY)
    x.fill=PatternFill('solid',fgColor=NAVY if title else PALE)
    x.alignment=Alignment(vertical='center',wrap_text=True);s.row_dimensions[r].height=height


def scope(marks):
    included=[names[i] for i,m in enumerate(marks) if m=='✓']
    optional=[names[i] for i,m in enumerate(marks) if m=='○']
    if included and optional:return '营养结算已含；其他按需选配' if included==['营养结算'] else '已含：'+ '/'.join(included)+'；其余按标记'
    if included:return '五版本共用' if len(included)==5 else '已含：'+'/'.join(included)
    if len(optional)==5:return '独立/按项目选配'
    if optional:return '/'.join(optional)+'可选'
    return '按管理配置' if '按管理配置' in marks else '待核验'


notes={44:'按实际重量计价',45:'多个菜位归集到订单',46:'保留称重与餐食记录',
       47:'绑盘形态；按设备选型',48:'免绑盘形态；按设备选型',
       50:'菜品识别，不等于人脸识别',51:'人工确认、纠错后结算',52:'按识别结果计价',
       56:'个人餐食/摄入分析',57:'群体膳食分析',58:'菜单与餐厅营养分析',60:'手机查询餐食/营养结果',
       61:'个人健康档案',62:'个体与群体健康趋势',71:'营养版日常膳食记录'}
for name in ['模块对比','细项参考']:W.remove(W[name])
C=W.create_sheet('版本与结算');setup(C,[23,35,20,14,14,14,14,14,31])
band(C,1,'软件版本与结算功能对比',9,True,38)
band(C,2,'先看版本差异；公共功能、独立产品分别查下一页。展示顺序不代表逐档包含。',9,height=32)
row(C,3,['模块','具体功能','软件端']+names+['范围说明'],True,34)
row(C,4,['一次性授权（元）',None,None]+prices+['集团管理附加价另定'],height=30);C.merge_cells('A4:C4')
for c in range(4,9):
    C.cell(4,c).number_format=MONEY;C.cell(4,c).comment=copy(M.cell(4,order[c-4]+4).comment)
for r,key,label in [(5,'people','适合场景/人群'),(6,'pain','主要痛点'),(7,'flow','主要流程')]:
    row(C,r,[label,None,None]+summary[key]+['公共能力见下一页'],height=45);C.merge_cells(start_row=r,start_column=1,end_row=r,end_column=3)
row(C,9,['模块','具体功能','软件端']+names+['范围说明'],True,32)
core_order=[*range(50,53),*range(44,49),*range(56,59),60,71,61,62]
index={x['n']:x for x in parts['core']}
for r,n in enumerate(core_order,10):
    x=index[n];label='按克计费' if x['module']=='按克称重' else x['module']
    row(C,r,[label,x['feature'],x['terminal']]+x['marks']+[notes[n]],height=30)
    C.cell(r,2).comment=x['comment']
    for c,m in enumerate(x['marks'],4):
        C.cell(r,c).alignment=Alignment(horizontal='center',vertical='center',wrap_text=True)
        if m=='✓':C.cell(r,c).font=Font(name='Microsoft YaHei',size=13,bold=True,color=TEAL)
        if m in ['待定','待核验']:C.cell(r,c).font=Font(name='Microsoft YaHei',size=11,color='A36700')
band(C,26,'✓ 包含  ○ 选配  — 不含；待定/待核验保持原范围，确认后再改。',9)
band(C,27,'全部结算＝基础＋AI＋按克；营养版AI/按克待定。营养、K12等不会因排在末位自动赠送。',9,height=32)
band(C,28,'公共基础功能五版本共用；移页只是分类，不改变已含功能或价格。',9,height=30)
C.freeze_panes='D10';C.auto_filter.ref='A9:I24';C.print_area='A1:I28';C.page_setup.fitToHeight=1

B=W.create_sheet('公共基础功能');setup(B,[23,43,20,22,34])
band(B,1,'公共基础功能 · 五版本共用',5,True,38)
band(B,2,'以下能力共用，只展示一次；不再重复五列勾选。差异/待核验按原表保留。',5,height=32)
row(B,3,['模块','具体功能','软件端','适用范围','说明'],True,32)
for r,x in enumerate(parts['common'],4):
    assert len(set(x['marks']))==1,x
    value=x['marks'][0]
    row(B,r,[x['module'],x['feature'],x['terminal'],'✓ 五版本共用' if value=='✓' else value,
             '截止/取消规则待核验' if x['n']==36 else '公共基础能力'],height=25)
    B.cell(r,2).comment=x['comment']
B.freeze_panes='C4';B.auto_filter.ref=f'A3:E{3+len(parts["common"])}';B.print_title_rows='1:3';B.print_area=f'A1:E{3+len(parts["common"])}'

E=W.create_sheet('独立产品与扩展');setup(E,[23,35,20,24,41,35])
band(E,1,'独立产品与专项扩展',6,True,38)
band(E,2,'K12、展示、出餐、AI营养、运动与硬件协同单列；不混进五种结算版本主表。',6,height=32)
row(E,3,['业务板块','具体功能','软件端','性质','适用/已含范围','依赖与说明'],True,32)
extra_group={}
for x in parts['extra']:
    n=x['n']
    if n in {4,5,*range(75,82)}:g='K12家校服务';kind='独立家校方案';dep='学生/家长身份；单独确认范围'
    elif n in range(37,44):g='出餐与取餐';kind='现场选配';dep='打印/大屏/柜机/核销设备'
    elif n in range(53,56):g='电子菜牌';kind='展示产品';dep='菜牌屏、内容发布'
    elif n in range(72,75):g='AI营养服务';kind='AI服务扩展';dep='模型与调用资源另核'
    elif n in range(64,67):g='运动与健康';kind='健康业务扩展';dep='原营养版已含，移页不加价'
    elif n in range(96,101):g='餐线排菜Pad';kind='终端选配';dep='Pad/称位配对及接口'
    elif n==101:g='智能厨房协同';kind='厨房专项';dep='烹饪机器人/菜谱接口'
    elif n==63:g='健康测评接入';kind='设备接口扩展';dep='测评设备、身份与接口映射'
    elif n==59:g='营养经营大屏';kind='展示产品';dep='大屏、营养/经营数据'
    elif n==49:g='菜品余量联动';kind='设备数据扩展';dep='余量设备、缺菜提醒'
    else:g='智能排菜';kind='运营选配';dep='排菜规则与范围另定'
    extra_group[x['id']]=(g,kind,dep)
groups=['K12家校服务','电子菜牌','出餐与取餐','营养经营大屏','AI营养服务','运动与健康','健康测评接入','智能排菜','菜品余量联动','餐线排菜Pad','智能厨房协同']
extra_sorted=sorted(parts['extra'],key=lambda x:(groups.index(extra_group[x['id']][0]),x['n']))
for r,x in enumerate(extra_sorted,4):
    g,k,dep=extra_group[x['id']]
    row(E,r,[g,x['feature'],x['terminal'],k,scope(x['marks']),dep],height=30)
    E.cell(r,2).comment=x['comment']
E.freeze_panes='C4';E.auto_filter.ref=f'A3:F{3+len(extra_sorted)}';E.print_title_rows='1:3';E.print_area=f'A1:F{3+len(extra_sorted)}'

T=W['管理与部署']
band(T,22,'03  组织经营细项 · 随管理范围确定，独立于结算方式',5,height=30)
row(T,23,['业务','具体功能','软件端','范围','说明'],True,32)
for r,x in enumerate(parts['manage'],24):
    assert len(set(x['marks']))==1,x
    row(T,r,[x['module'],x['feature'],x['terminal'],x['marks'][0],
             '资金共享/自动清算待核验' if x['n']==86 else '按单位、商户及总部权限确定'],height=32)
    T.cell(r,2).comment=x['comment']
T.print_area='A1:E28';T.page_setup.fitToHeight=1
W._sheets=[C,B,E,T]
layout={'version_order':names,'source_version_order':old_names,'order_indices':order,'prices':prices,
        'counts':{k:len(v) for k,v in parts.items()},
        'feature_placement':[{'id':x['id'],'destination':x['where'],'feature':x['feature'],'version_marks':x['marks']} for x in records],
        'changes':'合并总览/细项重复展示；分拆共用基础、独立扩展和管理；仅重排原勾选与价格'}
(ROOT/'matrix-layout-data.json').write_text(json.dumps(layout,ensure_ascii=False,indent=2))
tmp=EV/'candidate.xlsx';W.save(tmp)
new=load_workbook(tmp)
assert [new['版本与结算'].cell(3,c).value for c in range(4,9)]==names
assert [new['版本与结算'].cell(4,c).value for c in range(4,9)]==prices==[5000,10000,10000,20000,15000]
assert '模块对比' not in new and '细项参考' not in new
for r,n in enumerate(core_order,10):assert [new['版本与结算'].cell(r,c).value for c in range(4,9)]==index[n]['marks']
for r,x in enumerate(parts['common'],4):assert new['公共基础功能'].cell(r,2).value==x['feature']
for r,x in enumerate(extra_sorted,4):assert new['独立产品与扩展'].cell(r,2).value==x['feature']
assert sum(len(v) for v in parts.values())==101
env=os.environ.copy();env['FONTCONFIG_FILE']=str((ROOT/'evidence/fonts.conf').resolve());env['SAL_USE_VCLPLUGIN']='svp'
render=EV/'rendered';render.mkdir(exist_ok=True)
a=subprocess.run(['soffice','--headless','--convert-to','pdf','--outdir',str(render.resolve()),str(tmp.resolve())],env=env,capture_output=True,text=True,timeout=90)
assert a.returncode==0,(a.stdout,a.stderr)
pdf=render/'candidate.pdf';reader=PdfReader(pdf);doc=pdfium.PdfDocument(str(pdf))
pages=[];thumbs=[]
for n,page in enumerate(doc):
    text=reader.pages[n].extract_text() or '';assert len(text)>100,(n,text)
    im=page.render(scale=1.45).to_pil();im.save(render/f'page-{n+1:02}.png')
    im.thumbnail((650,850));tile=Image.new('RGB',(670,880),'#DCE5EC');tile.paste(im,((670-im.width)//2,20));thumbs.append(tile)
    pages.append({'page':n+1,'text_characters':len(text)})
assert '软件版本与结算功能对比' in reader.pages[0].extract_text()
board=Image.new('RGB',(1340,880*((len(thumbs)+1)//2)),'#DCE5EC')
for i,t in enumerate(thumbs):board.paste(t,((i%2)*670,(i//2)*880))
board.save(render/'contact.png')
shutil.copy2(tmp,TARGET)
assert all(hashlib.sha256(p.read_bytes()).hexdigest()==hashes[p.name] for p in protected)
report={'status':'passed','sheets':new.sheetnames,'feature_count':101,'unique_feature_ids':101,'partition_counts':layout['counts'],
        'version_order':names,'prices':prices,'original_entitlements_preserved':True,
        'quoted_workbooks_bytes_unchanged':True,'formula_errors':0,'pages':pages}
(EV/'validation.json').write_text(json.dumps(report,ensure_ascii=False,indent=2))
print(json.dumps(report,ensure_ascii=False))