"""Export ALL experiments - old + new, all specialized experiments."""
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: ALL Experiments ═══
ws1 = wb.active
ws1.title = "All Experiments"
h1 = [('#',5),('Experiment',36),('Architecture',14),('Phase',18),('imgsz',7),('Ep',5),
      ('mAP50 ↑',10),('mAP50-95 ↑',12),('Precision ↑',10),('Recall ↑',10),
      ('BoxL ↓',8),('ClsL ↓',8),('DflL ↓',8),('Note',35)]
write_header(ws1, h1)

all_exps = []

# ── Phase 0: Old Raw Baselines (D:\DairyGoat, imgsz=960) ──
old_raw = [
    ('yolo11s (old baseline)', 'yolo11s', 'v0-raw', 960, 80, 0.9034,0.4619,0.8815,0.8384,'旧基准 seed=3407'),
    ('yolov8s (old baseline)', 'yolov8s', 'v0-raw', 960, 80, 0.9010,0.4551,0.8736,0.8316,''),
    ('yolo26s (old baseline)', 'yolo26s', 'v0-raw', 960, 80, 0.8991,0.4579,0.8685,0.8273,''),
    ('yolo11n (old baseline)', 'yolo11n', 'v0-raw', 960, 80, 0.8824,0.4383,0.8535,0.8006,''),
    ('yolo26n (old baseline)', 'yolo26n', 'v0-raw', 960, 80, 0.8754,0.4312,0.8551,0.7929,''),
]
for r in old_raw: all_exps.append(r+('OLD-raw',))

# ── Phase 0: Non-YOLO ──
all_exps.append(('Faster R-CNN R50 FPN', 'FasterRCNN', 'v0-nonYOLO', 960, 30, 0.6746,0.3285,0.6746,0.8687,'torchvision')+('OLD-nonYOLO',))
all_exps.append(('FCOS R50 FPN', 'FCOS', 'v0-nonYOLO', 960, 30, 0.3079,0.1455,0.3079,0.9030,'torchvision')+('OLD-nonYOLO',))

# ── Phase 0: Night Screening (4种夜间增强) ──
night_data = [
    ('yolo11s RAW night', 'yolo11s', 'v0-night-raw', 960, 80, 0.9059,0.4544,0.8824,0.8362,'夜间raw'),
    ('yolo11s GAMMA night', 'yolo11s', 'v0-night-gamma', 960, 80, 0.9027,0.4519,0.8851,0.8289,'夜间gamma_only'),
    ('yolo11s CLAHE night', 'yolo11s', 'v0-night-clahe', 960, 80, 0.8888,0.4386,0.8645,0.8306,'夜间gamma_clahe'),
    ('yolo11s RETINEX night', 'yolo11s', 'v0-night-retinex', 960, 80, 0.8427,0.4074,0.8639,0.7642,'夜间retinex_glare'),
    ('yolo26s RAW night', 'yolo26s', 'v0-night-raw', 960, 80, 0.9075,0.4489,0.8799,0.8323,''),
    ('yolo26s GAMMA night', 'yolo26s', 'v0-night-gamma', 960, 80, 0.9043,0.4466,0.8793,0.8342,''),
    ('yolo26s CLAHE night', 'yolo26s', 'v0-night-clahe', 960, 80, 0.9005,0.4473,0.8548,0.8387,''),
    ('yolo26s RETINEX night', 'yolo26s', 'v0-night-retinex', 960, 80, 0.8200,0.3963,0.8423,0.7453,''),
]
for r in night_data: all_exps.append(r+('OLD-night',))

# ── Phase 0: Edge Rescue (边缘遮挡可见性感知) ──
all_exps.append(('EdgeRescue baseline', 'yolo11s', 'v0-edge', 960, 80, '-',0.4163,'-','-','边缘遮挡基准')+('OLD-edge',))
all_exps.append(('EdgeRescue visibility_aware', 'yolo11s', 'v0-edge', 960, 80, '-',0.4131,'-','-','可见性感知阈值-无显著增益')+('OLD-edge',))
all_exps.append(('EdgeRescue stricter_committee', 'yolo11s', 'v0-edge', 960, 80, '-',0.3996,'-','-','严格边缘委员会')+('OLD-edge',))

# ── Phase 0: Goat Dedup (重复框抑制) ──
all_exps.append(('GoatDedup baseline', 'yolo11s', 'v0-dedup', 960, 80, 0.8561,0.4203,0.5697,0.6273,'基准,count_mae=3.5')+('OLD-dedup',))
all_exps.append(('GoatDedup v2_1 (backup)', 'yolo11s', 'v0-dedup', 960, 80, 0.8490,0.4159,0.6135,0.6149,'去重,count_mae=1.9↓')+('OLD-dedup',))
all_exps.append(('GoatDedup v2_1 (mainline)', 'yolo11s', 'v0-dedup', 960, 80, 0.8494,0.4126,0.6072,0.6079,'去重,count_mae=2.2↓')+('OLD-dedup',))

# ── Phase 0: Stage2/21 (难例加权+拥挤后处理+高分辨率) ──
stage_data = [
    ('Stage21 baseline', 'yolo11s', 'v0-stage21', 960, 80, 0.8464,0.4163,0.5695,0.6272,'基准'),
    ('Stage21 A6_combo (BEST)', 'yolo11s', 'v0-stage21', 960, 80, 0.8469,0.4131,0.6051,0.6079,'最佳组合'),
    ('Stage21 soft_nms', 'yolo11s', 'v0-stage21', 960, 80, 0.8468,0.4130,0.6029,0.6088,'Soft NMS'),
    ('Stage21 visibility_aware', 'yolo11s', 'v0-stage21', 960, 80, 0.8430,0.4084,0.5655,0.6201,'可见性感知后处理'),
    ('Stage21 edge_context_padding', 'yolo11s', 'v0-stage21', 960, 80, 0.8242,0.3983,0.5488,0.6090,'边缘上下文填充'),
    ('Stage21 border_tile_fusion', 'yolo11s', 'v0-stage21', 960, 80, 0.7538,0.3633,0.3718,0.6064,'边界瓦片融合-退化'),
    ('Stage21 full_plus_sahi_wbf', 'yolo11s', 'v0-stage21', 960, 80, 0.7169,0.3326,0.2921,0.5964,'SAHI+WBF-退化'),
    ('Stage21 sahi_1280', 'yolo11s', 'v0-stage21', 960, 80, 0.5002,0.2043,0.2168,0.4639,'SAHI 1280-严重退化'),
]
for r in stage_data: all_exps.append(r+('OLD-stage',))

# ── NEW experiments (current server) ──
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'
    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'
    mAP = g('metrics/mAP50-95(B)')
    note = ''
    if isinstance(mAP, float):
        if mAP >= 0.513: note = 'BEST'
        elif mAP >= 0.511: note = 'GOOD'
        elif mAP < 0.50 and len(lines)-1 > 50: note = 'FAIL'
        elif mAP < 0.50: note = 'TRAINING'
    all_exps.append((ename, arch, phase, 1536, 120,
        g('metrics/mAP50(B)'), mAP, 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 sk(x):
    v = x[6]
    if isinstance(v, (int,float)): return v
    if v == '-': return 0
    return 0
all_exps.sort(key=sk, 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):
        cell = ws1.cell(row=row, column=j+2, value=val if val != '-' else '-')
        if j in [5,6,7,8] and isinstance(val, float):
            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

# ═══ Sheet 2: WBF Ensemble ═══
ws2 = wb.create_sheet("WBF Ensemble")
h2 = [('Method',35),('mAP50-95 ↑',14),('IoU@75 ↑',12),('Delta',14),('N Models',9),('N Sources',9),('Note',30)]
write_header(ws2, h2)
wbf = [
    ('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'),
    ('1m Kitchen Sink (12x)',0.5691,0.6055,'+0.017',1,12,''),
    ('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,''),
    ('KS 7 Models',0.5872,0.6215,'+0.035',7,84,''),
    ('KS 10 Models',0.5880,0.6266,'+0.036',10,120,''),
    ('KS 13 Models BEST',0.5888,0.6266,'+0.037',13,156,'当前最佳WBF'),
]
for i,rd in enumerate(wbf):
    for j,val in enumerate(rd):
        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")
h3=[('Method',30),('EastLeft ↑',14),('EastRight ↑',14),('WestLeft ↑',14),('WestRight ↑',14),('Overall ↑',12),('Note',25)]
write_header(ws3,h3)
cam=[
    ('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 13 Models BEST',None,None,None,None,0.5888,'当前最佳'),
]
for i,rd in enumerate(cam):
    for j,val in enumerate(rd):
        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: Phase0 Specialized Experiments ═══
ws4=wb.create_sheet("Phase0 Specialized")
h4=[('Experiment',36),('Arch',10),('Category',14),('imgsz',7),('Ep',5),('mAP50 ↑',10),('mAP50-95 ↑',12),('Precision ↑',10),('Recall ↑',10),('Note',40)]
write_header(ws4,h4)
spec=night_data + [
    ('EdgeRescue baseline','yolo11s','edge-occlusion',960,80,'-',0.4163,'-','-','边缘遮挡基准'),
    ('EdgeRescue visibility_aware','yolo11s','edge-occlusion',960,80,'-',0.4131,'-','-','可见性感知-无增益'),
    ('GoatDedup baseline','yolo11s','dedup',960,80,0.8561,0.4203,0.5697,0.6273,'count_mae=3.5'),
    ('GoatDedup v2_1','yolo11s','dedup',960,80,0.8490,0.4159,0.6135,0.6149,'count_mae=1.9(提升!)'),
    ('Stage21 baseline','yolo11s','stage21',960,80,0.8464,0.4163,0.5695,0.6272,''),
    ('Stage21 A6_combo BEST','yolo11s','stage21',960,80,0.8469,0.4131,0.6051,0.6079,'最佳组合'),
    ('Stage21 soft_nms','yolo11s','stage21',960,80,0.8468,0.4130,0.6029,0.6088,''),
    ('Stage21 SAHI variants','yolo11s','stage21',960,80,0.5002,0.2043,0.2168,0.4639,'SAHI严重退化'),
    ('Stage21 border_tile_fusion','yolo11s','stage21',960,80,0.7538,0.3633,0.3718,0.6064,'退化'),
]
for i,rd in enumerate(spec):
    for j,val in enumerate(rd):
        cell=ws4.cell(row=i+2,column=j+1,value=val if val != '-' else '-')
        if isinstance(val,float): cell.number_format='0.0000'

# ═══ Sheet 5: Idea Progress ═══
ws5=wb.create_sheet("Idea Progress")
h5=[('Idea',35),('Status',12),('Best Result',18),('Note',42)]
write_header(ws5,h5)
ideas=[
    ('WBF Multi-Model Ensemble','WORKS','+0.037 (13m)','最有效路径-WBF集成'),
    ('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免费'),
    ('Goat Dedup Fusion (old)','WORKS*','count_mae 3.5→1.9','去重后处理有效-计数提升'),
    ('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','模型已校准'),
    ('Night Enhancement (old)','DEAD*','gamma≈raw','4种方案无显著增益'),
    ('Edge Rescue (old)','DEAD*','no clear gain','可见性感知无增益'),
    ('SAHI (old)','DEAD*','严重退化','SAHI+WBF均退化'),
    ('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=ws5.cell(row=i+2,column=j+1,value=val)
        if 'WORKS' in status: cell.fill=green_fill
        elif 'DEAD' in status: cell.fill=red_fill
        elif status=='RUNNING': cell.fill=yellow_fill
        elif status=='READY': cell.fill=orange_fill

# ═══ Sheet 6: Legend ═══
ws6=wb.create_sheet("Legend")
ws6.column_dimensions['A'].width=28
ws6.column_dimensions['B'].width=70
ws6.cell(row=1,column=1,value='Legend').font=Font(bold=True,size=14)
legend=[
    ('mAP50','IoU=0.5时平均精度-衡量"检测能力"。越高越好。'),
    ('mAP50-95','IoU 0.5→0.95平均精度-衡量"定位精度"。越高越好。单模型:0.5134 WBF:0.5888'),
    ('Precision/Recall','预测框精确率/召回率。越高越好。'),
    ('Box/Cls/DFL Loss','训练损失。越低越好。'),
    ('★ 两个mAP口径','训练验证(0.5125)≠Eval框架(0.5521)。不可混用。'),
    ('★ OLD=橙色','旧服务器 imgsz=960 ep=80 不同数据划分。仅相对比较。'),
    ('★ NEW=无色','当前服务器 imgsz=1536 ep=100+ 统一划分。'),
    ('Phase 0 夜间增强','raw/gamma_only/gamma_clahe/retinex_glare。4种方案均无显著增益。'),
    ('Phase 0 边缘遮挡','visibility_aware/stricter_committee。均无显著增益。'),
    ('Phase 0 去重融合','goat_dedup_fusion。mAP略降但count_mae从3.5→1.9,大幅改善计数。'),
    ('Phase 0 Stage2/21','难例加权/拥挤后处理/SAHI。A6_combo(soft_nms+dedup)最佳。SAHI全部退化。'),
    ('WBF','Weighted Box Fusion: 多模型多尺度预测加权融合。当前最佳:13模型 0.5888。'),
    ('IoU@75','IoU阈值0.75时召回率。旧v6_1仅0.49,最新WBF达0.627。'),
    ('Faster R-CNN/FCOS','TorchVision实现,仅30epoch。0.329/0.146,远低于YOLO。'),
]
for i,(k,v) in enumerate(legend):
    ws6.cell(row=i+3,column=1,value=k)
    ws6.cell(row=i+3,column=2,value=v)

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