"""Generate the Likert-5 scoring sheet for L3 fix-suggestion actionability.

Reads the 108 L3 runs (3 models x 12 samples x 3 repeats) from the
experiment database and produces a workbook with:
  Sheet 1 封面: title, key facts, sheet index, notes
  Sheet 2 评分说明: the 1-5 rubric and scoring guidelines
  Sheet 3 打分表: one row per L3 output, dropdown-validated score column
"""

import uuid
from pathlib import Path

from openpyxl import Workbook
from openpyxl.styles import Alignment, Border, Font, PatternFill, Side
from openpyxl.utils import get_column_letter
from openpyxl.worksheet.datavalidation import DataValidation

from app.db import SessionLocal, init_db
from app.models import ExperimentRun

EXPERIMENT_ID = "42a452df-6091-47c3-8632-a9ec29815aaa"
OUTPUT = Path(r"C:\Users\eeymoo\Documents\毕业论文\实验结果\L3修复建议_李克特评分表.xlsx")

GREY_FILL = PatternFill(start_color="F5F5F5", end_color="F5F5F5", fill_type="solid")
HEADER_FILL = PatternFill(start_color="333333", end_color="333333", fill_type="solid")
BLUE_FILL = PatternFill(start_color="E6F0FA", end_color="E6F0FA", fill_type="solid")
THIN = Side(style="thin", color="D0D0D0")
BORDER = Border(left=THIN, right=THIN, top=THIN, bottom=THIN)


def collect_l3_runs():
    init_db()
    db = SessionLocal()
    runs = (
        db.query(ExperimentRun)
        .filter(ExperimentRun.experiment_id == uuid.UUID(EXPERIMENT_ID), ExperimentRun.status == "done")
        .all()
    )
    l3 = [r for r in runs if r.template_version.template.level == "L3"]
    l3.sort(key=lambda r: (r.model_id, r.sample.language, str(r.sample_id), r.repeat_index))
    return l3


def style_header(ws, row, cols):
    for col in cols:
        c = ws.cell(row=row, column=col)
        c.font = Font(bold=True, color="FFFFFF", size=11)
        c.fill = HEADER_FILL
        c.alignment = Alignment(horizontal="center", vertical="center")


def build_cover(wb, n_rows):
    ws = wb.active
    ws.title = "封面"
    ws.sheet_view.showGridLines = False
    ws.column_dimensions["A"].width = 3
    for col, w in zip("BCDEF", (22, 26, 26, 26, 26)):
        ws.column_dimensions[col].width = w

    ws.merge_cells("B2:F2")
    ws["B2"] = "L3 修复建议可操作性评分表（李克特五级量表）"
    ws["B2"].font = Font(size=18, bold=True)
    ws["B2"].alignment = Alignment(horizontal="center", vertical="center")
    ws.row_dimensions[2].height = 38

    ws.merge_cells("B3:F3")
    ws["B3"] = "基于大模型的智能代码审查提示词优化研究 · 实验 full-factorial-v1"
    ws["B3"].font = Font(size=11, color="666666")
    ws["B3"].alignment = Alignment(horizontal="center")

    ws["B5"] = "关键信息"
    ws["B5"].font = Font(bold=True, size=12)
    facts = [
        ("待评分输出", f"{n_rows} 条（3 模型 × 12 样本 × 3 次重复的 L3 级输出）"),
        ("评分维度", "建议可操作性（李克特 1–5 级）"),
        ("评分对象", "模型给出的修复建议，对照 Ground Truth 与参考修复"),
        ("数据来源", "实验数据库 promptcr.db（experiment: full-factorial-v1）"),
    ]
    r = 6
    for k, v in facts:
        ws.cell(row=r, column=2, value=k).font = Font(bold=True)
        ws.cell(row=r, column=2).fill = BLUE_FILL
        ws.cell(row=r, column=2).border = BORDER
        ws.merge_cells(start_row=r, start_column=3, end_row=r, end_column=6)
        ws.cell(row=r, column=3, value=v).border = BORDER
        for col in range(3, 7):
            ws.cell(row=r, column=col).border = BORDER
        r += 1

    ws["B11"] = "工作表索引"
    ws["B11"].font = Font(bold=True, size=12)
    index = [
        ("评分说明", "李克特 1–5 级判据与评分注意事项（打钩前必读）"),
        ("打分表", f"{n_rows} 条 L3 输出逐条打分，评分列为下拉选择 1–5"),
    ]
    r = 12
    for name, desc in index:
        ws.cell(row=r, column=2, value=name).font = Font(bold=True, color="0066CC")
        ws.cell(row=r, column=2).border = BORDER
        ws.merge_cells(start_row=r, start_column=3, end_row=r, end_column=6)
        ws.cell(row=r, column=3, value=desc)
        for col in range(3, 7):
            ws.cell(row=r, column=col).border = BORDER
        r += 1

    ws["B15"] = "备注"
    ws["B15"].font = Font(bold=True, size=12)
    notes = [
        "1. 评分前请先阅读「评分说明」工作表，保持前后判据一致。",
        "2. run_id 与实验数据库中的实验单元一一对应，便于回溯原始输出与指标。",
        "3. 评分完成后，将按模型 × 级别做描述统计与模糊综合评判（参考亓莱滨方法）。",
        "4. 如中途打断，可分多次填写，评分列留空视为未评。",
    ]
    r = 16
    for note in notes:
        ws.merge_cells(start_row=r, start_column=2, end_row=r, end_column=6)
        ws.cell(row=r, column=2, value=note).font = Font(size=10, color="666666")
        r += 1


def build_rubric(wb):
    ws = wb.create_sheet("评分说明")
    ws.sheet_view.showGridLines = False
    ws.column_dimensions["A"].width = 3
    ws.column_dimensions["B"].width = 8
    ws.column_dimensions["C"].width = 18
    ws.column_dimensions["D"].width = 80

    ws.merge_cells("B2:D2")
    ws["B2"] = "建议可操作性 · 李克特五级量表判据"
    ws["B2"].font = Font(size=15, bold=True)
    ws["B2"].alignment = Alignment(horizontal="center", vertical="center")
    ws.row_dimensions[2].height = 30

    header_row = 4
    ws.cell(row=header_row, column=2, value="分值")
    ws.cell(row=header_row, column=3, value="等级")
    ws.cell(row=header_row, column=4, value="判据")
    style_header(ws, header_row, (2, 3, 4))

    rubric = [
        (5, "完全可操作", "建议可直接应用：能针对预埋缺陷给出正确的修复方式与具体代码/步骤，无需修改即可采纳。"),
        (4, "基本可操作", "建议方向正确、内容具体，仅需少量人工调整（如修正细节、补全边界）即可应用。"),
        (3, "部分可操作", "建议方向基本正确，但存在明显缺漏或不够具体，需较多人工补充、验证后才能实施。"),
        (2, "可操作性差", "建议笼统模糊（如仅说「建议检查逻辑」「注意空指针」），或与预埋缺陷仅部分相关，难以直接指导修复。"),
        (1, "不可操作", "建议与预埋缺陷无关、明显错误、自相矛盾，或未给出任何修复建议。"),
    ]
    r = header_row + 1
    for score, label, desc in rubric:
        ws.cell(row=r, column=2, value=score).alignment = Alignment(horizontal="center", vertical="center")
        ws.cell(row=r, column=2).font = Font(bold=True, size=12, color="0066CC")
        ws.cell(row=r, column=3, value=label).font = Font(bold=True)
        ws.cell(row=r, column=3).alignment = Alignment(vertical="center")
        cell = ws.cell(row=r, column=4, value=desc)
        cell.alignment = Alignment(wrap_text=True, vertical="center")
        for col in (2, 3, 4):
            ws.cell(row=r, column=col).border = BORDER
            if score % 2 == 1:
                if ws.cell(row=r, column=col).fill.start_color.rgb in (None, "00000000"):
                    ws.cell(row=r, column=col).fill = GREY_FILL
        ws.row_dimensions[r].height = 42
        r += 1

    r += 1
    ws.cell(row=r, column=2, value="评分注意事项").font = Font(bold=True, size=12)
    notes = [
        "1. 仅评价修复建议的可操作性，不评价缺陷检出是否正确（检出正确性由检出率指标衡量）。",
        "2. 若一条输出包含多个问题的建议，以针对预埋缺陷（Ground Truth 列所示）的那条建议为评分对象。",
        "3. 对照「参考修复」判断建议的正确性，但措辞不要求一致，语义等价即可。",
        "4. 若模型未检出预埋缺陷（建议全部指向其他问题），评 1 分。",
        "5. 评分时保持标准前后一致；建议先抽 5 条试评，校准后再正式评分。",
    ]
    r += 1
    for note in notes:
        ws.merge_cells(start_row=r, start_column=2, end_row=r, end_column=4)
        cell = ws.cell(row=r, column=2, value=note)
        cell.font = Font(size=10, color="666666")
        cell.alignment = Alignment(wrap_text=True, vertical="center")
        ws.row_dimensions[r].height = 28
        r += 1


def build_scoring(wb, runs):
    ws = wb.create_sheet("打分表")
    ws.sheet_view.showGridLines = False

    headers = [
        ("序号", 6), ("run_id", 34), ("模型", 10), ("语言", 12), ("仓库", 10),
        ("commit", 10), ("重复", 6), ("Ground Truth 缺陷", 36), ("参考修复", 36),
        ("模型 L3 输出（含修复建议）", 70), ("评分(1-5)", 10), ("备注", 14),
    ]
    header_row = 2
    for i, (title, width) in enumerate(headers, start=2):
        col = get_column_letter(i)
        ws.column_dimensions[col].width = width
        ws.cell(row=header_row, column=i, value=title)
    ws.column_dimensions["A"].width = 2
    style_header(ws, header_row, range(2, 2 + len(headers)))
    ws.row_dimensions[header_row].height = 24
    ws.freeze_panes = "B3"

    dv = DataValidation(type="list", formula1='"1,2,3,4,5"', allow_blank=True, showDropDown=False)
    dv.error = "请选择 1-5 的整数分值"
    dv.errorTitle = "无效评分"
    ws.add_data_validation(dv)

    r = header_row + 1
    for idx, run in enumerate(runs, start=1):
        gt = run.sample.defects[0] if run.sample.defects else None
        values = [
            idx,
            str(run.id),
            run.model_id,
            run.sample.language,
            run.sample.repo,
            run.sample.commit_sha[:7],
            run.repeat_index,
            (gt.description or "") if gt else "",
            (gt.reference_fix or "") if gt else "",
            run.result.raw_output or "",
            None,
            None,
        ]
        for i, v in enumerate(values, start=2):
            cell = ws.cell(row=r, column=i, value=v)
            cell.border = BORDER
            if i in (8, 9, 10, 11):  # GT / 参考修复 / 模型输出 → wrap
                cell.alignment = Alignment(wrap_text=True, vertical="top")
            else:
                cell.alignment = Alignment(horizontal="center", vertical="top")
        ws.cell(row=r, column=12).fill = BLUE_FILL  # 评分列高亮
        dv.add(ws.cell(row=r, column=12))
        ws.row_dimensions[r].height = 110
        r += 1

    ws.auto_filter.ref = f"B{header_row}:M{r - 1}"


def main():
    runs = collect_l3_runs()
    print(f"L3 runs: {len(runs)}")
    wb = Workbook()
    build_cover(wb, len(runs))
    build_rubric(wb)
    build_scoring(wb, runs)
    OUTPUT.parent.mkdir(parents=True, exist_ok=True)
    wb.save(OUTPUT)
    print("saved:", OUTPUT)

    # 校验：重开文件确认结构
    from openpyxl import load_workbook
    wb2 = load_workbook(OUTPUT)
    assert wb2.sheetnames == ["封面", "评分说明", "打分表"], wb2.sheetnames
    ws = wb2["打分表"]
    assert ws.max_row == len(runs) + 2, (ws.max_row, len(runs))
    assert ws["L2"].value == "评分(1-5)"
    print("verify ok: sheets =", wb2.sheetnames, "| data rows =", ws.max_row - 2)


if __name__ == "__main__":
    main()