from pathlib import Path
from collections import Counter
import ast,re,json,hashlib
import openpyxl
R=Path(__file__).resolve().parent
P=next(R.glob('团队*.xlsx'))
raw=json.loads((R/'source/logs.json').read_text())
items=json.loads((R/'source/items.json').read_text())
w=openpyxl.load_workbook(P,data_only=False)
v=openpyxl.load_workbook(P,data_only=True)
ns={'re':re}
tree=ast.parse((R/'analyze_logs.py').read_text())
func=next(n for n in tree.body if isinstance(n,ast.FunctionDef) and n.name=='redact')
exec(compile(ast.Module(body=[func],type_ignores=[]),'redactor','exec'),ns)
redact=ns['redact']
checks=[]
def test(name,ok,detail):
 checks.append({'check':name,'passed':bool(ok),'detail':detail})
 if not ok:raise AssertionError(name+': '+str(detail))
reports=list(v['原始日志汇总'].values)[1:]
test('汇报全量保留',len(reports)==len(raw)==463,len(reports))
test('人员全量保留',{r[2] for r in reports}=={x['name'] for x in raw},len({r[2] for r in reports}))
lookup={(x['source'],x['source_row']):x for x in raw}
expected={}
for r in reports:
 x=lookup[(r[10],r[11])]
 test('汇报身份-'+r[0],r[1]==x['date'] and r[2]==x['name'] and r[3]==x['time'],r[0])
 test('汇报原文-'+r[0],(r[6] or '')==redact(x['other']) and (r[7] or '')==redact(x['tomorrow']) and (r[8] or '')==redact(x['learning']),r[0])
 for i,row in enumerate(x['all_rows']):expected[(x['source'],x['source_row']+i)]=[redact(t) for t in row[6:]]
actual={ (row[3],row[4]):[t or '' for t in row[5:]] for row in list(v['原始字段逐行'].values)[1:]}
test('1218源数据行逐字段一致',actual==expected and len(actual)==1218,len(actual))
active=[x for x in items if x['count']]
expected_types=Counter(x['category'] for x in active)
actual_types={row[0]:row[2] for row in list(v['工作类型总览'].values)[1:]}
test('工作类型汇总对账',all(actual_types.get(k)==n for k,n in expected_types.items()) and sum(actual_types.values())==len(active),len(active))
person_counts={row[0]:row[-1] for row in list(v['人员类型交叉表'].values)[1:]}
test('人员类型汇总对账',person_counts==dict(Counter(x['name'] for x in active)),len(person_counts))
day_counts={row[0]:row[-1] for row in list(v['每日类型交叉表'].values)[1:]}
test('每天类型汇总对账',all(day_counts[k]==n for k,n in Counter(x['date'] for x in active).items()) and sum(day_counts.values())==len(active),len(day_counts))
test('795个日期人员组合',v['日期人员日览'].max_row-1==53*15,v['日期人员日览'].max_row-1)
test('53天每日矩阵',v['每日人员矩阵'].max_row-1==53,v['每日人员矩阵'].max_row-1)
recalc=R/'source/recalculated'/P.name
rv=openpyxl.load_workbook(recalc,data_only=True)
errors={'#REF!','#DIV/0!','#VALUE!','#N/A','#NAME?','#NUM!','#NULL!'}
formula_count=0
for s in w:
 for row in s:
  for c in row:
   value=v[s.title][c.coordinate].value
   test('缓存无公式错误-'+s.title+'!'+c.coordinate,not (isinstance(value,str) and value in errors),'无错误')
   if c.data_type=='f':
    formula_count+=1
    actual=rv[s.title][c.coordinate].value
    if isinstance(value,(int,float)) and isinstance(actual,(int,float)):
     match=abs(value-actual)<1e-8
    else:match=value==actual
    test('LibreOffice独立复算-'+s.title+'!'+c.coordinate,match,{'cached':value,'recalculated':actual})
test('所有公式独立复算一致',formula_count>0,formula_count)
leaks=[]
pat=r'(?i)\bCpt@\d+\b|\b(?:\d{1,3}\.){3}\d{1,3}\b|(?:admin|root)\s*/\s*\S+'
for s in v:
 for row in s:
  for c in row:
   if isinstance(c.value,str) and re.search(pat,c.value):leaks.append([s.title,c.coordinate])
test('分析表敏感模式隐藏',not leaks,leaks)
manifest=[]
for p in sorted((R/'source').glob('0*-原始*.xlsx')):
 manifest.append({'file':p.name,'bytes':p.stat().st_size,'sha256':hashlib.sha256(p.read_bytes()).hexdigest()})
(R/'source/source-manifest.json').write_text(json.dumps(manifest,ensure_ascii=False,indent=2))
result={'status':'PASS','raw_reports':len(raw),'raw_data_rows':len(actual) if isinstance(actual,dict) else 1218,'people':15,'calendar_days':53,'sheets':len(w.sheetnames),'active_segments':len(active),'independently_recalculated_formulas':formula_count,'sensitive_pattern_hits':len(leaks),'original_files':manifest,'workbook_sha256':hashlib.sha256(P.read_bytes()).hexdigest(),'check_count':len(checks)}
(R/'validation-final.json').write_text(json.dumps(result,ensure_ascii=False,indent=2))
print(json.dumps(result,ensure_ascii=False,indent=2))