from pathlib import Path
import os, json, subprocess, shutil
from openpyxl import load_workbook
from pypdf import PdfReader
import pypdfium2 as pdfium
from PIL import Image

ROOT = Path(__file__).resolve().parents[1]
OUT = ROOT/'output'
EV = ROOT/'evidence'
CASES = EV/'test-inputs'; CALC=EV/'recalculated'; PDF=EV/'rendered'
for p in [CASES,CALC,PDF]: p.mkdir(exist_ok=True)
conf=EV/'fonts.conf'
conf.write_text('''<?xml version="1.0"?><!DOCTYPE fontconfig SYSTEM "fonts.dtd"><fontconfig><dir>/Users/jack/Library/Fonts</dir><dir>/Users/jack/code/099-github/banana-slides/backend/fonts</dir><dir>/System/Library/Fonts/Supplemental</dir><cachedir>/tmp/cpt-pricing-font-cache</cachedir></fontconfig>''')
ENV=os.environ.copy(); ENV['FONTCONFIG_FILE']=str(conf.resolve()); ENV['SAL_USE_VCLPLUGIN']='svp'
QUOTE=OUT/'智慧食堂软件报价单.xlsx'
cases=[
    ('基础云端',{},5000),
    ('AI本地',{'B6':'AI识别结算','B8':'普通本地部署'},15000),
    ('全部信创',{'B6':'全部结算','B8':'信创适配部署'},35000),
    ('同型号多台只收一种',{'B9':1},6000),
    ('两种额外型号',{'B9':2},7000),
    ('集团空价',{'B7':'集团/多单位'},'待核价'),
    ('集团填写测试价',{'B7':'集团/多单位','软件价格表!E15':12000},17000),
    ('选配空价',{'B19':'电子菜牌'},'待核价'),
    ('选配填写测试价',{'B19':'电子菜牌','软件价格表!E29':2500},7500),
    ('重复选配',{'B19':'电子菜牌','B20':'电子菜牌','软件价格表!E29':2500},'待核价'),
    ('人工调整基础价',{'软件价格表!E6':6000},6000),
    ('追加自定义项',{'B25':'专项服务','E25':3,'F25':950},7850),
    ('负数型号',{'B9':-1},'待核价'),
    ('非整数型号',{'B9':1.5},'待核价'),
    ('非数字型号',{'B9':'待确认'},'待核价'),
    ('负数软件价',{'软件价格表!E6':-100},'待核价'),
    ('营养价与边界',{'B6':'营养结算'},20000),
]
for name,changes,expected in cases:
    w=load_workbook(QUOTE)
    for k,val in changes.items():
        sheet,cell=k.split('!',1) if '!' in k else ('项目报价',k)
        w[sheet][cell]=val
    w.save(CASES/(name+'.xlsx'))


def convert(fmt,out,paths):
    result=subprocess.run(['soffice','--headless','--convert-to',fmt,'--outdir',str(out.resolve()),*[str(p.resolve()) for p in paths]],env=ENV,capture_output=True,text=True,timeout=90)
    assert result.returncode==0,(result.stdout,result.stderr)
    return {'format':fmt,'code':result.returncode,'stdout':result.stdout,'stderr':result.stderr}


logs=[convert('xlsx',CALC,[*OUT.glob('*.xlsx'),*CASES.glob('*.xlsx')])]
for p in OUT.glob('*.xlsx'):
    assert (CALC/p.name).exists()
    shutil.copy2(CALC/p.name,p)
logs.append(convert('pdf',PDF,list(OUT.glob('*.xlsx'))))
tests=[]
for name,changes,expected in cases:
    p=CALC/(name+'.xlsx'); w=load_workbook(p,data_only=True)
    actual=w['项目报价']['G30'].value
    assert actual==expected,(name,actual,expected)
    tests.append({'case':name,'expected':expected,'actual':actual,'pass':True})

errors=[]; formulas=0
for p in OUT.glob('*.xlsx'):
    values=load_workbook(p,data_only=True); fs=load_workbook(p,data_only=False)
    assert not fs._external_links
    for s in values:
        for row in s:
            for c in row:
                if c.data_type=='e':errors.append([p.name,s.title,c.coordinate,c.value])
    for s in fs:
        formulas+=sum(c.data_type=='f' for row in s for c in row)
assert not errors,errors
q=load_workbook(QUOTE,data_only=False)
assert q.sheetnames==['软件价格表','项目报价']
for s in q:
    for row in s:
        for c in row:
            if c.value is not None:
                assert '食堂数' not in str(c.value) and '人数' not in str(c.value),(s.title,c.coordinate)
assert len(q['项目报价'].data_validations.dataValidation)>=8
assert load_workbook(QUOTE,data_only=True)['软件价格表']['E15'].value is None
v=load_workbook(OUT/'智慧食堂软件版本功能对比.xlsx',data_only=True)
assert v['细项参考'].max_row==104
ids=[v['细项参考'].cell(r,1).value for r in range(4,105)]
assert len(ids)==len(set(ids))==101
data=json.loads((ROOT/'pricing-feature-data.json').read_text())
assert len({f['module_id'] for f in data['features']})==22
assert [q['软件价格表'].cell(r,5).value for r in range(6,11)]==[v['模块对比'].cell(4,c).value for c in range(4,9)]
assert v['模块对比']['H17'].value=='待定' and v['模块对比']['H18'].value=='待定'

pages=[]
for p in PDF.glob('*.pdf'):
    reader=PdfReader(p); doc=pdfium.PdfDocument(str(p))
    assert '智慧食堂' in (reader.pages[0].extract_text() or ''),'Chinese rendering failed: '+p.name
    thumbs=[]
    for n,page in enumerate(doc):
        im=page.render(scale=1.4).to_pil()
        im.save(PDF/(p.stem+f'-{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)
    board=Image.new('RGB',(1340,880*((len(thumbs)+1)//2)),'#DCE5EC')
    for i,tile in enumerate(thumbs):board.paste(tile,((i%2)*670,(i//2)*880))
    board.save(PDF/(p.stem+'-contact.png'))
    pages.append({'file':p.name,'pages':len(reader.pages),'chinese_text_verified':True})
report={'status':'passed','cases':tests,'formula_count':formulas,'formula_errors':errors,
        'modules':22,'unique_features':101,'food_headcount_in_quote':False,'external_links':False,
        'cross_file_price_consistency':True,'pages':pages,'native_recalculation':'LibreOffice isolated-profile XLSX export',
        'font_config':'task-local config; system font configuration unchanged'}
(EV/'validation.json').write_text(json.dumps(report,ensure_ascii=False,indent=2))
(EV/'office-conversion.json').write_text(json.dumps(logs,ensure_ascii=False,indent=2))
print(json.dumps({k:v for k,v in report.items() if k!='cases'},ensure_ascii=False))
print('Business calculation cases:',len(tests),'passed')