"""Reference analysis. Install: pip install openpyxl matplotlib"""
from pathlib import Path
import statistics
import openpyxl
import matplotlib
matplotlib.use('Agg')
import matplotlib.pyplot as plt

root = Path(__file__).resolve().parent
book = openpyxl.load_workbook(root / 'research-training.xlsx', data_only=True)
raw = list(book['数据'].values)
rows, issues, seen = [], [], {}
for line, values in enumerate(raw[1:], 2):
    record = dict(zip(raw[0], values))
    key = record['record_id']
    if key in seen:
        if values != seen[key]:
            raise ValueError(f'Conflicting ID: {key}')
        issues.append([line, key, 'duplicate', 'removed exact duplicate'])
        continue
    seen[key] = values
    for field in ['score_before', 'score_after']:
        value = record[field]
        if value is None:
            issues.append([line, key, field, 'missing; no imputation'])
        elif not isinstance(value, (int, float)) or not 0 <= value <= 100:
            issues.append([line, key, field, f'invalid {value}; set missing'])
            record[field] = None
    record['change'] = (record['score_after'] - record['score_before']
                        if record['score_after'] is not None and record['score_before'] is not None else None)
    rows.append(record)
result = openpyxl.Workbook()
clean = result.active
clean.title = '清洗后数据'
clean.append(list(rows[0]))
for row in rows:
    clean.append(list(row.values()))
quality = result.create_sheet('质量问题')
quality.append(['Excel row', 'record_id', 'field', 'rule'])
for issue in issues:
    quality.append(issue)
summary = result.create_sheet('分组统计')
summary.append(['group', 'field', 'n', 'mean', 'sample_sd', 'median', 'min', 'max'])
report = ['# 模拟科研培训数据：参考结果', '', '完全虚构，仅用于教学。以下由配套 Python 脚本计算，不是 OpenScience 对话运行记录。', '', f'原始 {len(raw)-1} 行；清洗后 {len(rows)} 人；移除 1 条完全重复记录；2 个原始分数缺失；1 个越界值置为缺失。不插补。', '']
fig, ax = plt.subplots(figsize=(7, 4))
for idx, group in enumerate(['A', 'B']):
    members = [r for r in rows if r['group'] == group]
    paired = [r for r in members if r['change'] is not None]
    report.append(f"## Group {group}: total n={len(members)}, paired n={len(paired)}")
    for field in ['score_before', 'score_after', 'change']:
        values = [r[field] for r in members if r[field] is not None]
        stats = [group, field, len(values), statistics.mean(values), statistics.stdev(values), statistics.median(values), min(values), max(values)]
        summary.append(stats)
        report.append(f'- {field}: n={len(values)}, mean={stats[3]:.2f}, sample SD={stats[4]:.2f}, median={stats[5]:.2f}, range={stats[6]}–{stats[7]}')
    changes = [r['change'] for r in paired]
    ax.scatter([idx + (i % 5 - 2) * 0.035 for i in range(len(changes))], changes, alpha=.75)
    ax.hlines(statistics.mean(changes), idx-.22, idx+.22, color='black', linewidth=2)
ax.set_xticks([0, 1], ['Group A (paired n=13)', 'Group B (paired n=14)'])
ax.set_ylabel('After - before (score points)')
ax.set_title('Synthetic teaching data · Individual change and mean')
ax.axhline(0, color='grey', linestyle='--', linewidth=.7)
ax.set_ylim(-2, 20)
fig.tight_layout()
fig.savefig(root / 'change-by-group.png', dpi=160)
plt.close(fig)
notes = result.create_sheet('说明')
notes.append(['模拟教学用途；完全虚构；不代表真实培训效果。'])
notes.append(['前后分数各按非缺失记录汇总，change 仅按完整配对记录汇总；不同字段 n 可能不同。'])
for sheet in result:
    sheet.freeze_panes = 'A2'
    sheet.auto_filter.ref = sheet.dimensions
    for column in sheet.columns:
        sheet.column_dimensions[column[0].column_letter].width = 24
result.save(root / 'cleaned-analysis.xlsx')
report.extend(['', '## 限制', '前后均值的有效样本不完全相同，不能直接用两者之差代替配对 change 均值。', '分组不是随机化证据；只报告描述性结果，不计算 P 值，不作因果推断。小样本与缺失值会影响概括性。', '脚本实际执行验证文件，但 AI 生成代码可能选择不同清洗规则，必须逐项复核。'])
(root / 'report.md').write_text('\n'.join(report), encoding='utf-8')
assert len(rows) == 30
assert sum(r['change'] is not None for r in rows) == 27
assert openpyxl.load_workbook(root / 'cleaned-analysis.xlsx')['清洗后数据'].max_row == 31
print('Verified: 30 cleaned records, 27 complete pairs.')