from pathlib import Path
from collections import Counter
import json,openpyxl,hashlib,re
R=Path(__file__).resolve().parent
ROOT=Path('/Users/jack/code/010-cpt/008-zhct/zhctprompt')
source=json.loads((ROOT/'outputs/2026-10-08-team-work-log-analysis/source/items.json').read_text())
mapped=json.loads((R/'source/mapped-items.json').read_text())
p=next(R.glob('虚拟*.xlsx'));wf=openpyxl.load_workbook(p,data_only=False);w=openpyxl.load_workbook(p,data_only=True);wr=openpyxl.load_workbook(R/'source/recalculated'/p.name,data_only=True)
assert len(source)==len(mapped)==4855
assert {x['key'] for x in source}=={x['key'] for x in mapped}
ss={x['key']:x for x in source}
for x in mapped:
 for k in ['log','date','name','projects','products','text','count','source','cell','mode']:
  assert x[k]==ss[x['key']][k],(x['key'],k)
labels={row[0] for row in list(w['统一标签字典'].values)[1:]};assert len(labels)==22
assert all(x['main'] in labels and all(z in labels for z in x['aux']) for x in mapped)
assert w['全量事项对齐'].max_row-1==4855 and w['原日志汇总'].max_row-1==463
assert w['报价单展示']['F2'].value is None
assert w['报价单展示']['G2'].value in [None,'']
assert w['报价单展示']['E2'].value==1
active=[x for x in mapped if x['count']];assert len(active)==2974
counts=Counter(x['name'] for x in active)
for row in list(w['人员标签统计'].values)[1:]:assert row[-1]==counts[row[0]]
counts=Counter(x['date'] for x in active)
for row in list(w['每日标签统计'].values)[1:]:assert row[-1]==counts[row[0]]
assert w['日期人员标签视图'].max_row-1==795
formulas=0
for s in wf:
 for row in s:
  for c in row:
   if c.data_type=='f':
    formulas+=1;a=w[s.title][c.coordinate].value;b=wr[s.title][c.coordinate].value
    assert (a in [None,''] and b in [None,'']) or a==b,(s.title,c.coordinate,a,b)
for s in w:
 for row in s:
  for c in row:
   assert c.data_type!='e',(s.title,c.coordinate)
   if isinstance(c.value,str):assert not re.search(r'(?i)\bCpt@\d+|\b(?:\d{1,3}\.){3}\d{1,3}\b|(?:admin|root)\s*/\s*\S+',c.value),(s.title,c.coordinate)
result={'status':'PASS','source_fragments':4855,'mapped_fragments':4855,'reports':463,'people':15,'calendar_days':53,'counted_fragments':2974,'main_label_dictionary':22,'service_contents':12,'formulas_independently_recalculated':formulas,'price_fixed':False,'unit_price_empty':True,'amount_empty_until_priced':True,'workbook_sha256':hashlib.sha256(p.read_bytes()).hexdigest(),'manual_check_records':sum(x['confidence']=='待人工核对' for x in mapped),'counted_manual_check_records':sum(x['count'] and x['confidence']=='待人工核对' for x in mapped)}
(R/'validation.json').write_text(json.dumps(result,ensure_ascii=False,indent=2));print(json.dumps(result,ensure_ascii=False))