from pathlib import Path
from datetime import date
import json,re,hashlib,math
import openpyxl
R=Path(__file__).resolve().parent
p=R/'recalculated/14人团队_实际与建议交付时间对比_20261008.xlsx'
d=json.loads((R/'analysis-data.json').read_text())
w=openpyxl.load_workbook(p,data_only=True);f=openpyxl.load_workbook(p,data_only=False)
checks=[]
assert len(w.sheetnames)==7
for i,c in enumerate(d['cases'],2):
 s=w['项目时间对比'];assert s.cell(i,1).value==c[0]
 days=(date.fromisoformat(c[3])-date.fromisoformat(c[2])).days
 assert s.cell(i,5).value==days
 assert s.cell(i,12).value==days-c[6]
for i,h in enumerate(d['historical'],2):assert w['历史台账周期'].cell(i,4).value==(date.fromisoformat(h[2])-date.fromisoformat(h[1])).days
assert abs(w['历史台账周期']['D6'].value-280/3)<1e-8
assert w['历史台账周期']['D7'].value==48
s=w['14人人员产能']
for cell,v in {'F2':60,'F3':36,'F4':72,'G2':180,'G3':108,'G4':216,'B11':10,'C11':170,'D11':56,'E11':176,'B13':40,'D16':10,'D17':52,'D18':40}.items():assert s[cell].value==v,(cell,s[cell].value,v)
assert sum(x[1] for x in d['roles'])==14
src=json.loads((R.parent/'2026-10-08-team-work-log-analysis/source/items.json').read_text());keys={x['key']:x for x in src}
for k in d['evidence_keys']:assert k in keys
for pth,sha in d['source_sha256'].items():assert hashlib.sha256(Path(pth).read_bytes()).hexdigest()==sha
formula_count=0
for sh in f:
 for row in sh:
  for c in row:
   if c.data_type=='f':
    formula_count+=1
    assert w[sh.title][c.coordinate].value is not None,(sh.title,c.coordinate)
for sh in w:
 for row in sh:
  for c in row:
   if c.value is None:continue
   assert c.data_type!='e',(sh.title,c.coordinate,c.value)
   if isinstance(c.value,str):
    assert not re.search(r'\b(?:\d{1,3}\.){3}\d{1,3}\b',c.value),(sh.title,c.coordinate)
assert formula_count==58,formula_count
original=openpyxl.load_workbook(R/'14人团队_实际与建议交付时间对比_20261008.xlsx',data_only=True)
for sh in original:
 for row in sh:
  for c in row:
   if c.value is None:continue
   assert c.font.name=='微软雅黑',(sh.title,c.coordinate,c.font.name)
   if f[sh.title][c.coordinate].data_type=='f':
    expected=w[sh.title][c.coordinate].value
    assert isinstance(c.value,(int,float)) and abs(c.value-expected)<1e-7,(sh.title,c.coordinate,c.value,expected)
original.close()
result={'status':'passed','sheets':w.sheetnames,'cases':12,'historical_complete_ledger_samples':3,'evidence_records':55,'formulas':formula_count,'independent_recalculation':'LibreOffice headless xlsx round-trip; formulas and cached values independently checked','checks':['日期跨度与机械差值','历史小样本统计','角色人数与人天组合','全部58公式回读无错误','源文件SHA256不变','证据编号存在','微软雅黑字体','无IP模式泄露'],'limitations':['缺真实齐套/启动/客户签字验收及角色工时','未刷新2026-08-08在线台账','目标周期、人天、60%可用率为D级假设']}
(R/'validation.json').write_text(json.dumps(result,ensure_ascii=False,indent=2))
print(json.dumps(result,ensure_ascii=False))
w.close();f.close()