"""Export ALL experiment results (old + new) to Excel."""
import os, json, numpy as np
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment
from openpyxl.utils import get_column_letter

PROJECT_DIR='/home/user/goat'
os.chdir(PROJECT_DIR)

wb = Workbook()
header_font = Font(bold=True, color='FFFFFF', size=11)
header_fill = PatternFill(start_color='4472C4', end_color='4472C4', fill_type='solid')
green_fill = PatternFill(start_color='C6EFCE', end_color='C6EFCE', fill_type='solid')
yellow_fill = PatternFill(start_color='FFEB9C', end_color='FFEB9C', fill_type='solid')
red_fill = PatternFill(start_color='FFC7CE', end_color='FFC7CE', fill_type='solid')
orange_fill = PatternFill(start_color='FFDAB9', end_color='FFDAB9', fill_type='solid')

def write_header(ws, headers):
    for col, (desc, w) in enumerate(headers, 1):
        cell = ws.cell(row=1, column=col, value=desc)
        cell.font = header_font; cell.fill = header_fill
        cell.alignment = Alignment(horizontal='center', wrap_text=True)
        ws.column_dimensions[get_column_letter(col)].width = w

# ── Sheet 1: Complete Experiment Table (OLD + NEW) ──
ws1 = wb.active
ws1.title = "All Experiments"

headers1 = [
    ('#', 5), ('Experiment', 35), ('Architecture', 16), ('Phase', 14),
    ('imgsz', 8), ('Epochs', 8),
    ('mAP50 ↑', 10), ('mAP50-95 ↑', 12), ('Precision ↑', 10), ('Recall ↑', 10),
    ('Box Loss ↓', 10), ('Cls Loss ↓', 10), ('DFL Loss ↓', 10),
    ('Note', 30),
]
write_header(ws1, headers1)

all_exps = []

# ── OLD experiments (D:\DairyGoat, imgsz=960) ──
old_data = [
    ('yolo11s (old baseline)', 'yolo11s', 'v0-raw_baseline', 960, 80,
     0.9034, 0.4619, 0.8815, 0.8384, '-', '-', '-', '旧服务器基准, seed=3407'),
    ('yolov8s (old baseline)', 'yolov8s', 'v0-raw_baseline', 960, 80,
     0.9010, 0.4551, 0.8736, 0.8316, '-', '-', '-', '旧服务器基准'),
    ('yolo26s (old baseline)', 'yolo26s', 'v0-raw_baseline', 960, 80,
     0.8991, 0.4579, 0.8685, 0.8273, '-', '-', '-', '旧服务器基准'),
    ('yolo11n (old baseline)', 'yolo11n', 'v0-raw_baseline', 960, 80,
     0.8824, 0.4383, 0.8535, 0.8006, '-', '-', '-', '旧服务器基准'),
    ('yolo26n (old baseline)', 'yolo26n', 'v0-raw_baseline', 960, 80,
     0.8754, 0.4312, 0.8551, 0.7929, '-', '-', '-', '旧服务器基准'),
    ('Faster R-CNN R50 FPN', 'FasterRCNN', 'v0-non_yolo', 960, 30,
     0.6746, 0.3285, 0.6746, 0.8687, '-', '-', '-', '非YOLO对照, torchvision'),
    ('FCOS R50 FPN', 'FCOS', 'v0-non_yolo', 960, 30,
     0.3079, 0.1455, 0.3079, 0.9030, '-', '-', '-', '非YOLO对照, torchvision'),
    ('RTMDet-s', 'RTMDet', 'v0-non_yolo', '-', '-',
     '-', '-', '-', '-', '-', '-', '-', 'mmdet未安装,跳过'),
    ('yolo11s + night_gamma (old)', 'yolo11s', 'v0-blind_final', 960, 80,
     0.9016, 0.4506, 0.8769, 0.8335, '-', '-', '-', '旧服务器blind最终'),
    ('yolo11s + raw_control (old)', 'yolo11s', 'v0-blind_final', 960, 80,
     0.9022, 0.4491, 0.8702, 0.8353, '-', '-', '-', '旧服务器blind最终'),
]

for d in old_data:
    all_exps.append(d + ('OLD',))

# ── NEW experiments (current server, imgsz=1536) ──
exp = 'runs/detect/Detection_experiments'
for ename in sorted(os.listdir(exp)):
    csv_path = f'{exp}/{ename}/results.csv'
    if not os.path.exists(csv_path): continue
    with open(csv_path) as f: lines = f.readlines()
    if len(lines) < 2: continue
    h = [s.strip() for s in lines[0].split(',')]
    try: idxs = {col: h.index(col) for col in h}
    except: continue

    i5095 = h.index('metrics/mAP50-95(B)')
    best_idx = 0; best_v = 0
    for i, line in enumerate(lines[1:]):
        try:
            v = float(line.strip().split(',')[i5095])
            if v > best_v: best_v = v; best_idx = i+1
        except: pass

    cols = lines[best_idx].strip().split(',')
    def g(col):
        try:
            if col in idxs: return float(cols[idxs[col]])
            return '-'
        except: return '-'

    n = ename.lower()
    if 'yolo11m' in n: arch = 'yolo11m'
    elif 'yolo11n' in n: arch = 'yolo11n'
    elif 'yolo26' in n: arch = 'yolo26'
    elif 'yolov8' in n: arch = 'yolov8'
    else: arch = 'yolo11s'

    if 'eastleft' in n: phase = 'cam-specialized'
    elif 'westright' in n: phase = 'cam-specialized'
    elif 'gmm' in n: phase = 'GMM-W2-loss'
    elif 'selfchallenge' in n: phase = 'self-challenge'
    elif 'distilled' in n: phase = 'distillation'
    elif 'whatif' in n: phase = 'what-if-aug'
    elif 'mask' in n: phase = 'mask-refine'
    elif 'expanded' in n: phase = 'data-expand'
    elif 'sc3' in n or 'p2_s' in n: phase = 'architecture'
    elif 'gwd' in n or 'wiou' in n or 'accumulate' in n or 'cosine' in n or 'scale_s' in n or 'close' in n or 'refine' in n or 'combined' in n: phase = 'loss/aug tuning'
    elif 'final_v3' in n: phase = 'label-refine'
    elif 'seed' in n: phase = 'seed'
    elif 'baseline' in n: phase = 'baseline'
    else: phase = 'other'

    ep_total = len(lines)-1
    note = ''
    mAP5095 = g('metrics/mAP50-95(B)')
    if isinstance(mAP5095, float):
        if mAP5095 >= 0.513: note = '🏆 BEST'
        elif mAP5095 >= 0.511: note = '⭐ GOOD'
        elif mAP5095 < 0.50: note = '❌ FAIL'
        if ep_total <= 50 and mAP5095 < 0.50: note = '🔄 TRAINING'

    all_exps.append((
        ename, arch, phase,
        1536, 120,  # approximate
        g('metrics/mAP50(B)'),
        mAP5095,
        g('metrics/precision(B)'),
        g('metrics/recall(B)'),
        g('train/box_loss'),
        g('train/cls_loss'),
        g('train/dfl_loss'),
        note, 'NEW',
    ))

# Sort by mAP50-95
def sort_key(x):
    v = x[6]
    if isinstance(v, (int, float)): return v
    return 0
all_exps.sort(key=sort_key, reverse=True)

for i, r in enumerate(all_exps):
    row = i + 2
    ws1.cell(row=row, column=1, value=i+1)
    for j, val in enumerate(r[:-1]):  # skip source tag
        cell = ws1.cell(row=row, column=j+2, value=val if val != '-' else '-')
        if j in [5, 6, 7, 8] and isinstance(val, float):  # mAP/Prec/Rec
            cell.number_format = '0.0000'
            if j == 6 and val >= 0.52: cell.fill = green_fill
            elif j == 6 and val >= 0.50: cell.fill = yellow_fill
        elif j in [9, 10, 11] and isinstance(val, float):  # losses
            cell.number_format = '0.0000'
    # Color old vs new
    src_row = ws1.cell(row=row, column=1)
    if r[-1] == 'OLD': src_row.fill = orange_fill

# ── Sheet 2: WBF Ensemble (same as before) ──
ws2 = wb.create_sheet("WBF Ensemble")
headers2 = [
    ('Method', 35), ('mAP50-95 ↑', 14), ('IoU@75 ↑', 12), ('Delta', 16),
    ('N Models', 10), ('N Sources', 10), ('Note', 30),
]
write_header(ws2, headers2)

wbf_data = [
    ('v6_1 Single (训练验证)', 0.5125, 0.4900, '-', 1, 1, '训练验证标准, 基准'),
    ('v6_1 Single (eval框架)', 0.5521, 0.5789, '—', 1, 1, 'Eval框架基准,单图推理'),
    ('1 Model x 3 Scales WBF', 0.5626, 0.6003, '+0.010', 1, 3, '单模型多尺度'),
    ('5 Checkpoint Ensemble', 0.5660, None, '+0.014', 1, 5, '单模型5cp WBF'),
    ('1m Kitchen Sink (12x)', 0.5691, 0.6055, '+0.017', 1, 12, '单模型x12变体'),
    ('5 Models x 3 Scales WBF', 0.5776, 0.6169, '+0.025', 5, 15, ''),
    ('KS 60x (5m)', 0.5816, 0.6203, '+0.030', 5, 60, '5模型x12变体'),
    ('KS 7 Models', 0.5872, 0.6215, '+0.035', 7, 84, '含yolo11n'),
    ('KS 10 Models', 0.5880, 0.6266, '+0.036', 10, 120, '10模型x12变体'),
    ('KS 13 Models 🏆', 0.5888, 0.6266, '+0.037', 13, 156, '当前最佳WBF集成'),
    ('Mask Weighted WBF', 0.5690, None, '+0.017', 1, 12, '掩码加权→微弱'),
    ('Prior WBF', 0.5689, None, '-0.0002', 1, 12, '位置先验→无效'),
]
for i, row_data in enumerate(wbf_data):
    for j, val in enumerate(row_data):
        cell = ws2.cell(row=i+2, column=j+1, value=val if val is not None else '-')
        if j == 1 and isinstance(val, float):
            cell.number_format = '0.0000'
            if val >= 0.58: cell.fill = green_fill

# ── Sheet 3: Per-Camera ──
ws3 = wb.create_sheet("Per-Camera")
headers3 = [
    ('Method', 30), ('EastLeft ↑', 14), ('EastRight ↑', 14),
    ('WestLeft ↑', 14), ('WestRight ↑', 14), ('Overall ↑', 12), ('Note', 25),
]
write_header(ws3, headers3)

cam_data = [
    ('v6_1 Single', 0.5240, 0.6082, 0.5465, 0.5399, 0.5521, '基准'),
    ('v19_eastleft (特化)', 0.5283, 0.6049, 0.5469, 0.5396, 0.5526, 'EastLeft +0.0043'),
    ('KS 7 Models', 0.5602, 0.6299, 0.5772, 0.5690, 0.5820, 'WBF全面提升'),
    ('KS 10 Models', None, None, None, None, 0.5880, ''),
    ('KS 13 Models BEST', None, None, None, None, 0.5888, '当前最佳'),
]
for i, row_data in enumerate(cam_data):
    for j, val in enumerate(row_data):
        cell = ws3.cell(row=i+2, column=j+1, value=val if val is not None else '-')
        if isinstance(val, float): cell.number_format = '0.0000'

# ── Sheet 4: Idea Progress ──
ws4 = wb.create_sheet("Idea Progress")
headers4 = [('Idea', 35), ('Status', 12), ('Best Result', 18), ('Note', 40)]
write_header(ws4, headers4)

ideas = [
    ('WBF Multi-Model Ensemble', 'WORKS', '+0.037 (13m)', '最有效路径'),
    ('Multi-Scale + Aug WBF', 'WORKS', '+0.010', '免费推理增强'),
    ('Per-Camera Adaptive WBF', 'WORKS', 'EastLeft +0.036', '逐机位阈值+尺度'),
    ('Per-Camera Specialized Training', 'WORKS', 'Best single 0.5134', 'EastLeft oversampling'),
    ('Cross-Validation', 'WORKS', '0.552 +/-0.011', '确认真实mAP'),
    ('Checkpoint Ensemble', 'WORKS', '+0.014', '单模型5cp免费'),
    ('Knowledge Distillation', 'DEAD', '0.5018', 'TTA自蒸馏退化'),
    ('What-If Augmentation', 'DEAD', '0.5067', '亚像素增强'),
    ('BRN Boundary Refinement', 'DEAD', '退化', '合成噪声不匹配'),
    ('Model Soup', 'DEAD', '崩溃', 'BN不兼容'),
    ('Snake/Edge Refinement', 'DEAD', '退化', '山羊毛边界模糊'),
    ('GWD/WIoU/InnerIoU Loss', 'DEAD', '持平/退化', '改不动天花板'),
    ('Label Refine v2/v3', 'DEAD', '退化', '引入新偏差'),
    ('Auto-Labeling v13', 'DEAD', '0.5094', '伪标签上限=老师'),
    ('Mask-based Label Refine', 'DEAD', '0.4927', '掩码质量不够'),
    ('Position-Size Prior', 'DEAD', '-0.0002', '模型已校准'),
    ('Self-Challenge Training', 'RUNNING', '0.5084', '聚焦边界案例'),
    ('GMM Wasserstein Loss', 'RUNNING', '0.4925', '多高斯建模山羊'),
    ('WestRight Specialized', 'RUNNING', '0.4435', '刚启动epoch5'),
    ('Background Diff Segment', 'READY', '掩码已生成', '待训分割头'),
    ('Attention Probe', 'TODO', '-', '内部特征定位'),
    ('Contrastive Cross-Camera', 'TODO', '-', '跨机位不变特征'),
    ('Iterative Denoising Detection', 'TODO', '-', '多轮渐进精修'),
    ('Orthogonal Error Experts', 'TODO', '-', '刻意制造互补'),
    ('Stereo Geometry', 'TODO', '-', '双机位立体约束'),
    ('Iterative Consensus v4', 'TODO', '-', '打破标注天花板'),
]
for i, (idea, status, result, note) in enumerate(ideas):
    for j, val in enumerate([idea, status, result, note]):
        cell = ws4.cell(row=i+2, column=j+1, value=val)
        if status == 'WORKS': cell.fill = green_fill
        elif status == 'DEAD': cell.fill = red_fill
        elif status == 'RUNNING': cell.fill = yellow_fill
        elif status == 'READY': cell.fill = orange_fill

# ── Sheet 5: Legend ──
ws5 = wb.create_sheet("Legend")
ws5.column_dimensions['A'].width = 25
ws5.column_dimensions['B'].width = 65
ws5.cell(row=1, column=1, value='指标').font = Font(bold=True)
ws5.cell(row=1, column=2, value='说明').font = Font(bold=True)
legend = [
    ('mAP50', 'IoU=0.5时的平均精度,衡量"检测能力"。越高越好。当前最佳: 0.9616 (v19_eastleft)'),
    ('mAP50-95', 'IoU 0.5→0.95的平均精度,衡量"定位精度"。越高越好。单模型最佳: 0.5134, WBF集成最佳: 0.5888'),
    ('Precision', '预测框中真正是羊的比例。越高越好。'),
    ('Recall', '真羊中被检出的比例。越高越好。'),
    ('Box Loss (train)', '训练边界框回归损失。越低越好。'),
    ('Cls Loss (train)', '训练分类损失。越低越好。'),
    ('DFL Loss (train)', 'Distribution Focal Loss。越低越好。反映框边缘分布精度。'),
    ('★ 两个mAP口径不同!', '训练验证mAP(0.5125) ≠ Eval框架mAP(0.5521)。前者是ultralytics批处理评估,后者是单图推理评估。两个不可混用。'),
    ('★ 新旧实验不可混排!', '旧实验(橙色行): imgsz=960, epochs=80, 不同数据划分。新实验(无色): imgsz=1536, epochs=100-150, 统一划分。'),
    ('WBF', 'Weighted Box Fusion: 多模型多尺度预测的加权融合。推理时多模型×多变体→WBF融合→高精度。'),
    ('IoU@75', 'IoU阈值0.75时的召回率。越高越好。衡量高精度定位能力。旧v6_1曾只有0.49, 最新WBF达到0.627。'),
    ('Faster R-CNN / FCOS', '旧服务器上的非YOLO对照。使用TorchVision实现,仅训练30 epochs。'),
]
for i, (k, v) in enumerate(legend):
    ws5.cell(row=i+3, column=1, value=k)
    ws5.cell(row=i+3, column=2, value=v)

path = 'logs/DairyGoat_All_Results_v2.xlsx'
wb.save(path)
print(f'Saved: {path}')
print(f'Sheets: {wb.sheetnames}')