make_grading_sheet.py

#!/usr/bin/env python3
"""
Generate a multi-sheet XLSX grading workbook for manual human evaluation.

Sheets:
  Questions        — one row per question: ID, type, category, student question, cleaned code, evaluator notes
  Rubric           — one row per criterion per question: full rubric text for reference
  gemma3_27b       — one row per question: model response + 8 yellow score input cells + total + notes
  gpt-oss_20b      — same
  qwen3-coder_30b  — same

Usage:
  cd /path/to/INVITE
  python Benchmark/make_grading_sheet.py
"""

try:
    import openpyxl
    from openpyxl.styles import PatternFill, Font, Alignment, Border, Side
    from openpyxl.utils import get_column_letter
except ImportError:
    print("ERROR: openpyxl is not installed.")
    print("Install with: /opt/homebrew/opt/python@3.14/bin/python3.14 -m pip install openpyxl --break-system-packages")
    raise SystemExit(1)

import json
import os
import re
import xml.etree.ElementTree as ET

# ---------------------------------------------------------------------------
# Paths
# ---------------------------------------------------------------------------

SCRIPT_DIR = os.path.dirname(os.path.abspath(__file__))
BENCHMARK_JSONL = os.path.join(SCRIPT_DIR, "Dataset", "benchmark.jsonl")
DATASET_DIR = os.path.join(SCRIPT_DIR, "Dataset")
RESPONSES_DIR = os.path.join(SCRIPT_DIR, "Responses")
OUTPUT_PATH = os.path.join(SCRIPT_DIR, "grading_sheet.xlsx")

MODELS = [
    ("gemma3_27b",      "gemma3:27b"),
    ("gpt-oss_20b",     "gpt-oss:20b"),
    ("qwen3-coder_30b", "qwen3-coder:30b"),
]

# ---------------------------------------------------------------------------
# XML → readable pseudocode
# ---------------------------------------------------------------------------

BLOCK_NAMES = {
    "pg_events_when_started":       "when started",
    "pg_control_forever":           "forever",
    "pg_control_if_then_else":      "if [CONDITION] then / else",
    "pg_control_if_then":           "if [CONDITION] then",
    "pg_control_repeat":            "repeat",
    "pg_control_wait_until":        "wait until [CONDITION]",
    "pg_control_stop":              "stop",
    "pg_control_break":             "break",
    "pg_drivetrain_drive":          "drive",
    "pg_drivetrain_drive_for":      "drive for",
    "pg_drivetrain_turn":           "turn",
    "pg_drivetrain_turn_for":       "turn for",
    "pg_drivetrain_turn_to_heading":"turn to heading",
    "pg_drivetrain_stop":           "stop driving",
    "pg_sensing_distance_found":    "distance found",
    "pg_sensing_bumper":            "bumper pressed",
    "pg_sensing_distance_to":       "distance to object",
    "pg_magnet_set_magnet_state":   "set magnet",
}

def block_display(block_el):
    """Return a single-line label for a block element."""
    btype = block_el.get("type", "")
    name = BLOCK_NAMES.get(btype, btype.replace("pg_", "").replace("_", " "))
    fields = {f.get("name"): f.text for f in block_el.findall("field")}

    parts = [name]
    for k, v in fields.items():
        if v:
            parts.append(v)
    return " ".join(parts)


def condition_label(block_el):
    """Inline label for a condition block (used in if/wait_until)."""
    btype = block_el.get("type", "")
    return BLOCK_NAMES.get(btype, btype.replace("pg_", "").replace("_", " "))


def walk_block(block_el, depth=0):
    """Recursively render a block and its children as indented lines."""
    lines = []
    indent = "  " * depth
    btype = block_el.get("type", "")

    # Build base label
    label = BLOCK_NAMES.get(btype, btype.replace("pg_", "").replace("_", " "))
    fields = {f.get("name"): f.text for f in block_el.findall("field")}
    field_parts = [v for v in fields.values() if v]

    # Inline condition value
    cond_label = ""
    for val in block_el.findall("value"):
        child = val.find("block")
        if child is not None:
            cond_label = condition_label(child)

    # Build the display line
    display = label
    if field_parts:
        display += " " + " ".join(field_parts)
    if cond_label:
        display = display.replace("[CONDITION]", f"[{cond_label}]")

    lines.append(f"{indent}{display}")

    # Recurse into statements (DO, ELSE, etc.)
    for stmt in block_el.findall("statement"):
        sname = stmt.get("name", "")
        lines.append(f"{indent}  [{sname}]:")
        child = stmt.find("block")
        if child is not None:
            lines.extend(walk_block(child, depth + 2))

    # Recurse into next (sequential)
    nxt = block_el.find("next")
    if nxt is not None:
        child = nxt.find("block")
        if child is not None:
            lines.extend(walk_block(child, depth))

    return lines


def clean_xml(xml_content):
    """Convert Blockly XML to human-readable indented pseudocode."""
    # The id attributes contain bare & characters which are invalid XML.
    # Sanitize only attribute values by replacing unescaped & with &
    xml_content = re.sub(r'&(?![a-zA-Z#]\w*;)', '&', xml_content)
    try:
        root = ET.fromstring(xml_content)
    except ET.ParseError:
        return "(could not parse XML)"

    # Strip namespace prefixes so findall/find work without qualification
    for el in root.iter():
        if "}" in el.tag:
            el.tag = el.tag.split("}")[1]

    lines = []
    for block in root.findall("block"):
        lines.extend(walk_block(block))
    return "\n".join(lines)


def build_code_structure(entry):
    """Build the code_structure string for an entry (handles multi-attempt)."""
    paths = entry["code_xml_paths"]
    sections = []
    for i, rel_path in enumerate(paths, 1):
        full_path = os.path.join(DATASET_DIR, rel_path)
        if not os.path.exists(full_path):
            sections.append(f"(file not found: {rel_path})")
            continue
        content = open(full_path, encoding="utf-8").read()
        cleaned = clean_xml(content)
        if len(paths) > 1:
            sections.append(f"--- Attempt {i} ---\n{cleaned}")
        else:
            sections.append(cleaned)
    return "\n\n".join(sections)


# ---------------------------------------------------------------------------
# Response extraction
# ---------------------------------------------------------------------------

def extract_response(filepath):
    if not os.path.exists(filepath):
        return "(response file not found)"
    content = open(filepath, encoding="utf-8").read()
    match = re.search(r"\[RESPONSE\](.*?)\[/RESPONSE\]", content, re.DOTALL)
    if match:
        return match.group(1).strip()
    # Fallback: strip time line and thinking block
    content = re.sub(r"^Time:.*\n?", "", content, flags=re.MULTILINE)
    content = re.sub(r"\[THINKING\].*?\[/THINKING\]", "", content, flags=re.DOTALL)
    return content.strip()


# ---------------------------------------------------------------------------
# Styles
# ---------------------------------------------------------------------------

HEADER_FILL   = PatternFill("solid", fgColor="D0E4F7")
YELLOW_FILL   = PatternFill("solid", fgColor="FFF2CC")
ALT_FILL      = PatternFill("solid", fgColor="F5F5F5")
BOLD_FONT     = Font(bold=True)
WRAP_ALIGN    = Alignment(wrap_text=True, vertical="top")
TOP_ALIGN     = Alignment(vertical="top")

def style_header(ws, row=1):
    for cell in ws[row]:
        cell.fill = HEADER_FILL
        cell.font = BOLD_FONT
        cell.alignment = WRAP_ALIGN

def set_col_width(ws, col_letter, width):
    ws.column_dimensions[col_letter].width = width

def freeze_top_row(ws):
    ws.freeze_panes = "A2"


# ---------------------------------------------------------------------------
# Sheet builders
# ---------------------------------------------------------------------------

def build_questions_sheet(ws, entries):
    ws.title = "Questions"
    headers = ["ID", "question_type", "bug_category", "student_question", "code_structure", "evaluator_notes"]
    ws.append(headers)
    style_header(ws)
    freeze_top_row(ws)

    for i, entry in enumerate(entries, 2):
        code = build_code_structure(entry)
        ws.append([
            entry["id"],
            entry["question_type"],
            entry["bug_category"],
            entry["student_question"],
            code,
            entry.get("evaluator_notes", ""),
        ])
        for col in range(1, 7):
            ws.cell(row=i, column=col).alignment = WRAP_ALIGN
        if i % 2 == 0:
            for col in range(1, 7):
                ws.cell(row=i, column=col).fill = ALT_FILL

    widths = {"A": 6, "B": 22, "C": 30, "D": 60, "E": 55, "F": 60}
    for col, w in widths.items():
        set_col_width(ws, col, w)
    ws.row_dimensions[1].height = 20


def build_rubric_sheet(ws, entries):
    ws.title = "Rubric"
    headers = ["ID", "criterion", "broad_description", "entry_specific", "score_2", "score_1", "score_0"]
    ws.append(headers)
    style_header(ws)
    freeze_top_row(ws)

    row_num = 2
    for q_idx, entry in enumerate(entries):
        shade = (q_idx % 2 == 1)
        for criterion in entry["rubric"]:
            ws.append([
                entry["id"],
                criterion["criterion"],
                criterion["broad_description"],
                criterion.get("entry_specific") or "",
                criterion["scoring_guide"]["2"],
                criterion["scoring_guide"]["1"],
                criterion["scoring_guide"]["0"],
            ])
            for col in range(1, 8):
                cell = ws.cell(row=row_num, column=col)
                cell.alignment = WRAP_ALIGN
                if shade:
                    cell.fill = ALT_FILL
            row_num += 1

    widths = {"A": 6, "B": 26, "C": 50, "D": 45, "E": 45, "F": 45, "G": 45}
    for col, w in widths.items():
        set_col_width(ws, col, w)


def build_model_sheet(ws, model_slug, model_name, entries):
    ws.title = model_slug

    SCORE_COLS = [
        "score_conciseness",
        "score_vocabulary",
        "score_accuracy",
        "score_formatting",
        "score_tone",
        "score_actionability",
        "score_targetedness",
        "score_type_specific",
    ]

    headers = ["ID", "type_specific_criterion", "response"] + SCORE_COLS + ["total", "notes"]
    ws.append(headers)
    style_header(ws)
    freeze_top_row(ws)

    # Column letters for score inputs (D through K = cols 4-11)
    score_start_col = 4
    score_end_col = score_start_col + len(SCORE_COLS) - 1
    total_col = score_end_col + 1
    notes_col = total_col + 1

    for i, entry in enumerate(entries, 2):
        # Find the type-specific criterion name (last criterion)
        type_specific_name = entry["rubric"][-1]["criterion"] if entry["rubric"] else ""

        response_path = os.path.join(RESPONSES_DIR, model_slug, "Trial1", f"{entry['id']}.txt")
        response_text = extract_response(response_path)

        total_formula = (
            f"=SUM({get_column_letter(score_start_col)}{i}:"
            f"{get_column_letter(score_end_col)}{i})"
        )

        row = [entry["id"], type_specific_name, response_text] + [""] * len(SCORE_COLS) + [total_formula, ""]
        ws.append(row)

        # Style cells
        ws.cell(row=i, column=1).alignment = TOP_ALIGN
        ws.cell(row=i, column=2).alignment = WRAP_ALIGN
        ws.cell(row=i, column=3).alignment = WRAP_ALIGN

        # Yellow fill + centered alignment for score inputs
        for col in range(score_start_col, score_end_col + 1):
            cell = ws.cell(row=i, column=col)
            cell.fill = YELLOW_FILL
            cell.alignment = Alignment(horizontal="center", vertical="top")

        ws.cell(row=i, column=total_col).alignment = Alignment(horizontal="center", vertical="top")
        ws.cell(row=i, column=notes_col).alignment = WRAP_ALIGN

    # Column widths
    set_col_width(ws, "A", 6)
    set_col_width(ws, "B", 26)
    set_col_width(ws, "C", 70)
    for col in range(score_start_col, score_end_col + 1):
        set_col_width(ws, get_column_letter(col), 10)
    set_col_width(ws, get_column_letter(total_col), 8)
    set_col_width(ws, get_column_letter(notes_col), 45)

    freeze_top_row(ws)
    # Also freeze the response column so score columns are visible while scrolling
    ws.freeze_panes = "D2"


# ---------------------------------------------------------------------------
# Main
# ---------------------------------------------------------------------------

def main():
    # Load benchmark entries
    entries = []
    with open(BENCHMARK_JSONL, encoding="utf-8") as f:
        for line in f:
            line = line.strip()
            if line:
                entries.append(json.loads(line))

    print(f"Loaded {len(entries)} benchmark entries.")

    wb = openpyxl.Workbook()

    # Sheet 1: Questions
    ws_q = wb.active
    build_questions_sheet(ws_q, entries)
    print("Built Questions sheet.")

    # Sheet 2: Rubric
    ws_r = wb.create_sheet()
    build_rubric_sheet(ws_r, entries)
    print("Built Rubric sheet.")

    # Sheets 3-5: one per model
    for model_slug, model_name in MODELS:
        ws_m = wb.create_sheet()
        build_model_sheet(ws_m, model_slug, model_name, entries)
        print(f"Built {model_slug} sheet.")

    wb.save(OUTPUT_PATH)
    print(f"\nSaved: {OUTPUT_PATH}")
    print("Open in Google Sheets via File → Import, or drag the file into drive.google.com")


if __name__ == "__main__":
    main()