ANC Question Bank for MBBS Teaching Project

"""
ANC Teaching Project — Student Learning Outcomes Tracker
Builds a multi-sheet Excel workbook using openpyxl.
"""

from openpyxl import Workbook
from openpyxl.styles import (
    Font, PatternFill, Alignment, Border, Side, GradientFill
)
from openpyxl.utils import get_column_letter
from openpyxl.formatting.rule import ColorScaleRule, DataBarRule, CellIsRule
from openpyxl.styles.differential import DifferentialStyle
from openpyxl.chart import BarChart, Reference
from openpyxl.chart.series import SeriesLabel
import io

wb = Workbook()

# ─────────────────────────────────────────────────────────────────────────────
# STYLE HELPERS
# ─────────────────────────────────────────────────────────────────────────────
FONT_NAME = "Arial"

def hdr(text, bold=True, size=11, color="FFFFFF"):
    return Font(name=FONT_NAME, bold=bold, size=size, color=color)

def body(bold=False, size=10, color="000000"):
    return Font(name=FONT_NAME, bold=bold, size=size, color=color)

def fill(hex_color):
    return PatternFill("solid", fgColor=hex_color)

def center():
    return Alignment(horizontal="center", vertical="center", wrap_text=True)

def left():
    return Alignment(horizontal="left", vertical="center", wrap_text=True)

thin = Side(style="thin", color="AAAAAA")
med  = Side(style="medium", color="555555")

def thin_border():
    return Border(left=thin, right=thin, top=thin, bottom=thin)

def med_border():
    return Border(left=med, right=med, top=med, bottom=med)

# Brand palette
C_DARK_BLUE  = "1F3864"   # headers
C_MID_BLUE   = "2E75B6"   # sub-headers
C_LIGHT_BLUE = "D6E4F0"   # alt row
C_GREEN      = "1E7145"   # pass / positive
C_AMBER      = "BF8F00"   # borderline
C_RED        = "C00000"   # fail
C_YELLOW     = "FFF2CC"   # input cells
C_WHITE      = "FFFFFF"
C_GREY       = "F2F2F2"

def set_col_widths(ws, widths: dict):
    for col_letter, width in widths.items():
        ws.column_dimensions[col_letter].width = width

def merge_title(ws, cell, text, font_size=13, bg=C_DARK_BLUE):
    ws[cell] = text
    ws[cell].font = Font(name=FONT_NAME, bold=True, size=font_size, color=C_WHITE)
    ws[cell].fill = fill(bg)
    ws[cell].alignment = center()

def col_header(ws, row, cols_values: dict, bg=C_MID_BLUE):
    """Write column headers; cols_values = {col_letter: label}"""
    for col, val in cols_values.items():
        c = ws[f"{col}{row}"]
        c.value = val
        c.font = hdr(color=C_WHITE)
        c.fill = fill(bg)
        c.alignment = center()
        c.border = thin_border()


# ─────────────────────────────────────────────────────────────────────────────
# SHEET 1 — INSTRUCTIONS
# ─────────────────────────────────────────────────────────────────────────────
ws_info = wb.active
ws_info.title = "📋 Instructions"
ws_info.sheet_view.showGridLines = False

ws_info.merge_cells("A1:G1")
merge_title(ws_info, "A1",
    "ANC Teaching Project — Student Learning Outcomes Tracker",
    font_size=15)
ws_info.row_dimensions[1].height = 36

lines = [
    ("", ""),
    ("PURPOSE", "Track and compare student learning outcomes across two groups: Control (traditional lecture) and Intervention (new teaching technique) using a 10-question ANC quiz."),
    ("", ""),
    ("SHEET GUIDE", ""),
    ("📋 Instructions",   "This sheet — overview and guidance."),
    ("👥 Student Register","Enter all student details here before scoring."),
    ("📝 Pre-Test Scores", "Record Pre-Test marks for each of the 10 questions (Q1–Q10)."),
    ("📝 Post-Test Scores","Record Post-Test marks for each of the 10 questions (Q1–Q10)."),
    ("📊 Analysis",        "Auto-calculated: totals, gains, Bloom's level breakdown, group comparison."),
    ("🏆 Leaderboard",     "Ranked student performance sorted by post-test score."),
    ("", ""),
    ("SCORING RULES", ""),
    ("Correct answer",    "= 1 mark"),
    ("Wrong / blank",     "= 0 marks"),
    ("Max score per test","= 10 marks"),
    ("Pass mark",         "= 6 / 10  (60%)"),
    ("", ""),
    ("BLOOM'S TAXONOMY",  ""),
    ("Recall (Q1–Q3)",    "Levels 1–2: Remember / Understand"),
    ("Clinical/Logic (Q4–Q10)", "Levels 3–5: Apply / Analyze / Evaluate"),
    ("", ""),
    ("GROUPS", ""),
    ("Control (C)",       "Students taught by traditional lecture method"),
    ("Intervention (I)",  "Students taught by the new teaching technique"),
    ("", ""),
    ("HOW TO USE", ""),
    ("Step 1", "Go to 👥 Student Register — fill in Student ID, Name, Group (C or I)."),
    ("Step 2", "After Pre-Test — enter marks in 📝 Pre-Test Scores (1 or 0 per question)."),
    ("Step 3", "After Post-Test — enter marks in 📝 Post-Test Scores (1 or 0 per question)."),
    ("Step 4", "Go to 📊 Analysis to view auto-calculated results and group comparison."),
    ("Step 5", "Check 🏆 Leaderboard for ranked student performance."),
]

row = 2
for label, desc in lines:
    if label == "":
        row += 1
        continue
    ws_info[f"B{row}"] = label
    ws_info[f"C{row}"] = desc
    if desc == "":  # Section header
        ws_info[f"B{row}"].font = Font(name=FONT_NAME, bold=True, size=11, color=C_DARK_BLUE)
        ws_info[f"B{row}"].fill = fill(C_LIGHT_BLUE)
        ws_info[f"B{row}"].border = thin_border()
    else:
        ws_info[f"B{row}"].font = body(bold=True)
        ws_info[f"B{row}"].fill = fill(C_GREY)
        ws_info[f"C{row}"].font = body()
        ws_info[f"B{row}"].border = thin_border()
        ws_info[f"C{row}"].border = thin_border()
    ws_info[f"B{row}"].alignment = left()
    ws_info[f"C{row}"].alignment = left()
    row += 1

ws_info.merge_cells("C4:G4")
for r in range(5, row):
    ws_info.merge_cells(f"C{r}:G{r}")

set_col_widths(ws_info, {"A": 3, "B": 28, "C": 70, "D": 5, "E": 5, "F": 5, "G": 5})


# ─────────────────────────────────────────────────────────────────────────────
# SHEET 2 — STUDENT REGISTER
# ─────────────────────────────────────────────────────────────────────────────
ws_reg = wb.create_sheet("👥 Student Register")
ws_reg.sheet_view.showGridLines = False

ws_reg.merge_cells("A1:G1")
merge_title(ws_reg, "A1", "Student Register — ANC Teaching Project")
ws_reg.row_dimensions[1].height = 32

col_header(ws_reg, 2, {
    "A": "S.No",
    "B": "Student ID",
    "C": "Full Name",
    "D": "Group\n(C = Control\nI = Intervention)",
    "E": "Year / Batch",
    "F": "Gender\n(M/F/O)",
    "G": "Contact / Notes",
})
ws_reg.row_dimensions[2].height = 42

# 30 sample student rows (blank input cells)
for i in range(1, 31):
    r = i + 2
    ws_reg[f"A{r}"] = i
    ws_reg[f"A{r}"].alignment = center()
    ws_reg[f"A{r}"].font = body()
    ws_reg[f"A{r}"].border = thin_border()
    for col in ["B", "C", "D", "E", "F", "G"]:
        c = ws_reg[f"{col}{r}"]
        c.fill = fill(C_YELLOW)
        c.font = Font(name=FONT_NAME, size=10, color="00008B")
        c.alignment = center() if col in ["A","D","E","F"] else left()
        c.border = thin_border()
    # Alternate row shading
    if i % 2 == 0:
        for col in ["A","B","C","D","E","F","G"]:
            ws_reg[f"{col}{r}"].fill = fill("EAF4FB")

set_col_widths(ws_reg, {
    "A": 6, "B": 14, "C": 28, "D": 16, "E": 14, "F": 10, "G": 28
})


# ─────────────────────────────────────────────────────────────────────────────
# HELPER — Build a Score Sheet (Pre or Post)
# ─────────────────────────────────────────────────────────────────────────────
Q_LABELS = [
    "Q1\nEDD (Recall)",
    "Q2\nLab Tests (Recall)",
    "Q3\nSerum Screening (Recall)",
    "Q4\nDating Discrepancy",
    "Q5\nGDM Screening",
    "Q6\nProgestin Logic",
    "Q7\nPlacenta Previa",
    "Q8\nExercise Advice",
    "Q9\nEvidence Appraisal",
    "Q10\nSocio-econ Barriers",
]
Q_TYPES = ["Recall","Recall","Recall","Clinical","Clinical","Logic","Clinical","Clinical","Logic","Logic"]

def build_score_sheet(wb, title, sheet_name, is_post=False):
    ws = wb.create_sheet(sheet_name)
    ws.sheet_view.showGridLines = False

    ws.merge_cells("A1:P1")
    merge_title(ws, "A1", f"{sheet_name} — ANC Teaching Project")
    ws.row_dimensions[1].height = 32

    # Question type legend row
    ws.merge_cells("A2:B2")
    ws["A2"] = "Question Type Legend →"
    ws["A2"].font = body(bold=True)
    ws["A2"].alignment = center()
    ws["A2"].fill = fill(C_GREY)
    for q_idx in range(10):
        col = get_column_letter(3 + q_idx)
        c = ws[f"{col}2"]
        c.value = Q_TYPES[q_idx]
        c.font = body(bold=True, color="FFFFFF")
        c.fill = fill(C_MID_BLUE if Q_TYPES[q_idx]=="Recall" else C_GREEN)
        c.alignment = center()
        c.border = thin_border()

    # Column headers row 3
    headers = {
        "A": "S.No",
        "B": "Student ID",
    }
    for q_idx, ql in enumerate(Q_LABELS):
        headers[get_column_letter(3 + q_idx)] = ql
    headers["M"] = "Total Score\n(/10)"
    headers["N"] = "Recall Score\n(/3)"
    headers["O"] = "Clinical+Logic\nScore (/7)"
    headers["P"] = "Pass / Fail\n(≥6 = Pass)"

    col_header(ws, 3, headers)
    ws.row_dimensions[3].height = 46

    # 30 data rows
    for i in range(1, 31):
        r = i + 3
        ws[f"A{r}"] = i
        ws[f"A{r}"].alignment = center()
        ws[f"A{r}"].font = body()
        ws[f"A{r}"].border = thin_border()

        # Student ID — pull from register
        ws[f"B{r}"] = f"='👥 Student Register'!B{i+2}"
        ws[f"B{r}"].font = Font(name=FONT_NAME, size=10, color="006400")
        ws[f"B{r}"].alignment = center()
        ws[f"B{r}"].border = thin_border()

        # Q1–Q10 input cells (cols C to L)
        for q_idx in range(10):
            col = get_column_letter(3 + q_idx)
            c = ws[f"{col}{r}"]
            c.fill = fill(C_YELLOW)
            c.font = Font(name=FONT_NAME, size=10, color="00008B")
            c.alignment = center()
            c.border = thin_border()

        # Total = SUM(C:L)
        ws[f"M{r}"] = f"=SUM(C{r}:L{r})"
        ws[f"M{r}"].font = body(bold=True)
        ws[f"M{r}"].alignment = center()
        ws[f"M{r}"].border = thin_border()

        # Recall score (Q1–Q3) = SUM(C:E)
        ws[f"N{r}"] = f"=SUM(C{r}:E{r})"
        ws[f"N{r}"].font = body()
        ws[f"N{r}"].alignment = center()
        ws[f"N{r}"].border = thin_border()

        # Clinical+Logic (Q4–Q10) = SUM(F:L)
        ws[f"O{r}"] = f"=SUM(F{r}:L{r})"
        ws[f"O{r}"].font = body()
        ws[f"O{r}"].alignment = center()
        ws[f"O{r}"].border = thin_border()

        # Pass/Fail
        ws[f"P{r}"] = f'=IF(M{r}>=6,"PASS","FAIL")'
        ws[f"P{r}"].font = body(bold=True)
        ws[f"P{r}"].alignment = center()
        ws[f"P{r}"].border = thin_border()

        # Alt row fill for non-input cols
        if i % 2 == 0:
            for col in ["A","B","M","N","O","P"]:
                ws[f"{col}{r}"].fill = fill("EAF4FB")

    # ── Summary rows at bottom ──
    sr = 35  # summary start row
    ws.merge_cells(f"A{sr}:B{sr}")
    ws[f"A{sr}"] = "CLASS AVERAGE"
    ws[f"A{sr}"].font = hdr(color="FFFFFF")
    ws[f"A{sr}"].fill = fill(C_DARK_BLUE)
    ws[f"A{sr}"].alignment = center()
    ws[f"A{sr}"].border = thin_border()

    for q_idx in range(10):
        col = get_column_letter(3 + q_idx)
        c = ws[f"{col}{sr}"]
        c.value = f"=IFERROR(AVERAGE({col}4:{col}33),0)"
        c.number_format = "0.00"
        c.font = body(bold=True)
        c.fill = fill(C_DARK_BLUE)
        c.font = Font(name=FONT_NAME, bold=True, size=10, color=C_WHITE)
        c.alignment = center()
        c.border = thin_border()

    for col, formula in [
        ("M", f"=IFERROR(AVERAGE(M4:M33),0)"),
        ("N", f"=IFERROR(AVERAGE(N4:N33),0)"),
        ("O", f"=IFERROR(AVERAGE(O4:O33),0)"),
        ("P", f'=COUNTIF(P4:P33,"PASS")&" / 30 Pass"'),
    ]:
        c = ws[f"{col}{sr}"]
        c.value = formula
        c.number_format = "0.00"
        c.font = Font(name=FONT_NAME, bold=True, size=10, color=C_WHITE)
        c.fill = fill(C_DARK_BLUE)
        c.alignment = center()
        c.border = thin_border()

    # Conditional formatting — Pass=green, Fail=red on col P
    from openpyxl.formatting.rule import CellIsRule
    green_fill = PatternFill("solid", fgColor="C6EFCE")
    red_fill   = PatternFill("solid", fgColor="FFC7CE")
    green_font = Font(name=FONT_NAME, bold=True, color="276221")
    red_font   = Font(name=FONT_NAME, bold=True, color="9C0006")

    ws.conditional_formatting.add(f"P4:P33",
        CellIsRule(operator="equal", formula=['"PASS"'],
                   fill=green_fill, font=green_font))
    ws.conditional_formatting.add(f"P4:P33",
        CellIsRule(operator="equal", formula=['"FAIL"'],
                   fill=red_fill, font=red_font))

    # Color scale on M (total score)
    ws.conditional_formatting.add(f"M4:M33",
        ColorScaleRule(start_type="num", start_value=0, start_color="FFC7CE",
                       mid_type="num",   mid_value=5,   mid_color="FFEB9C",
                       end_type="num",   end_value=10,  end_color="C6EFCE"))

    set_col_widths(ws, {
        "A":5,"B":13,
        "C":12,"D":12,"E":12,"F":13,"G":13,
        "H":13,"I":13,"J":13,"K":14,"L":14,
        "M":13,"N":13,"O":16,"P":14
    })

    return ws

build_score_sheet(wb, "Pre-Test",  "📝 Pre-Test Scores",  is_post=False)
build_score_sheet(wb, "Post-Test", "📝 Post-Test Scores", is_post=True)


# ─────────────────────────────────────────────────────────────────────────────
# SHEET 5 — ANALYSIS
# ─────────────────────────────────────────────────────────────────────────────
ws_an = wb.create_sheet("📊 Analysis")
ws_an.sheet_view.showGridLines = False

ws_an.merge_cells("A1:J1")
merge_title(ws_an, "A1", "📊 Analysis — Learning Outcome Comparison", font_size=14)
ws_an.row_dimensions[1].height = 36

# ── Section 1: Individual student summary ──
ws_an.merge_cells("A3:J3")
ws_an["A3"] = "INDIVIDUAL STUDENT SUMMARY"
ws_an["A3"].font = Font(name=FONT_NAME, bold=True, size=11, color=C_WHITE)
ws_an["A3"].fill = fill(C_MID_BLUE)
ws_an["A3"].alignment = center()

hdr_row4 = {
    "A":"S.No","B":"Student ID","C":"Student Name","D":"Group",
    "E":"Pre-Test\nTotal (/10)","F":"Post-Test\nTotal (/10)",
    "G":"Score Gain","H":"% Gain","I":"Pre Pass/Fail","J":"Post Pass/Fail"
}
col_header(ws_an, 4, hdr_row4, bg=C_DARK_BLUE)
ws_an.row_dimensions[4].height = 36

for i in range(1, 31):
    r = i + 4
    ws_an[f"A{r}"] = i
    ws_an[f"A{r}"].alignment = center()
    ws_an[f"A{r}"].font = body()
    ws_an[f"A{r}"].border = thin_border()

    for col, formula in [
        ("B", f"='👥 Student Register'!B{i+2}"),
        ("C", f"='👥 Student Register'!C{i+2}"),
        ("D", f"='👥 Student Register'!D{i+2}"),
        ("E", f"='📝 Pre-Test Scores'!M{i+3}"),
        ("F", f"='📝 Post-Test Scores'!M{i+3}"),
        ("G", f"=F{r}-E{r}"),
        ("H", f"=IFERROR((F{r}-E{r})/IF(E{r}=0,1,E{r}),0)"),
        ("I", f"='📝 Pre-Test Scores'!P{i+3}"),
        ("J", f"='📝 Post-Test Scores'!P{i+3}"),
    ]:
        c = ws_an[f"{col}{r}"]
        c.value = formula
        c.font = body(color="006400") if col in ["B","C","D","I","J"] else body()
        c.alignment = center()
        c.border = thin_border()
        if i % 2 == 0:
            c.fill = fill("EAF4FB")

    # Format % gain
    ws_an[f"H{r}"].number_format = "0.0%"

# Conditional formatting on gain (G)
ws_an.conditional_formatting.add("G5:G34",
    ColorScaleRule(start_type="num", start_value=-3, start_color="FFC7CE",
                   mid_type="num",   mid_value=0,    mid_color="FFEB9C",
                   end_type="num",   end_value=5,    end_color="C6EFCE"))

# Pass/Fail conditional
green_fill = PatternFill("solid", fgColor="C6EFCE")
red_fill   = PatternFill("solid", fgColor="FFC7CE")
green_font = Font(name=FONT_NAME, bold=True, color="276221")
red_font   = Font(name=FONT_NAME, bold=True, color="9C0006")
for col_range in ["I5:I34", "J5:J34"]:
    ws_an.conditional_formatting.add(col_range,
        CellIsRule(operator="equal", formula=['"PASS"'], fill=green_fill, font=green_font))
    ws_an.conditional_formatting.add(col_range,
        CellIsRule(operator="equal", formula=['"FAIL"'], fill=red_fill, font=red_font))


# ── Section 2: Group Comparison Table ──
gr = 37
ws_an.merge_cells(f"A{gr}:J{gr}")
ws_an[f"A{gr}"] = "GROUP COMPARISON — Control (C) vs Intervention (I)"
ws_an[f"A{gr}"].font = Font(name=FONT_NAME, bold=True, size=11, color=C_WHITE)
ws_an[f"A{gr}"].fill = fill(C_MID_BLUE)
ws_an[f"A{gr}"].alignment = center()

col_header(ws_an, gr+1, {
    "A":"Metric",
    "B":"Control Group (C)",
    "C":"Intervention Group (I)",
    "D":"Difference\n(I − C)",
}, bg=C_DARK_BLUE)
ws_an.row_dimensions[gr+1].height = 36

metrics = [
    ("No. of Students",
     '=COUNTIF(\'👥 Student Register\'!D3:D32,"C")',
     '=COUNTIF(\'👥 Student Register\'!D3:D32,"I")',
     "=C{r}-B{r}"),
    ("Avg Pre-Test Score",
     '=IFERROR(AVERAGEIF(\'👥 Student Register\'!D3:D32,"C",\'📝 Pre-Test Scores\'!M4:M33),0)',
     '=IFERROR(AVERAGEIF(\'👥 Student Register\'!D3:D32,"I",\'📝 Pre-Test Scores\'!M4:M33),0)',
     "=C{r}-B{r}"),
    ("Avg Post-Test Score",
     '=IFERROR(AVERAGEIF(\'👥 Student Register\'!D3:D32,"C",\'📝 Post-Test Scores\'!M4:M33),0)',
     '=IFERROR(AVERAGEIF(\'👥 Student Register\'!D3:D32,"I",\'📝 Post-Test Scores\'!M4:M33),0)',
     "=C{r}-B{r}"),
    ("Avg Score Gain",
     "=IFERROR(AVERAGEIF(D5:D34,\"C\",G5:G34),0)",
     "=IFERROR(AVERAGEIF(D5:D34,\"I\",G5:G34),0)",
     "=C{r}-B{r}"),
    ("Avg Recall Score (Pre)",
     '=IFERROR(AVERAGEIF(\'👥 Student Register\'!D3:D32,"C",\'📝 Pre-Test Scores\'!N4:N33),0)',
     '=IFERROR(AVERAGEIF(\'👥 Student Register\'!D3:D32,"I",\'📝 Pre-Test Scores\'!N4:N33),0)',
     "=C{r}-B{r}"),
    ("Avg Recall Score (Post)",
     '=IFERROR(AVERAGEIF(\'👥 Student Register\'!D3:D32,"C",\'📝 Post-Test Scores\'!N4:N33),0)',
     '=IFERROR(AVERAGEIF(\'👥 Student Register\'!D3:D32,"I",\'📝 Post-Test Scores\'!N4:N33),0)',
     "=C{r}-B{r}"),
    ("Avg Clinical+Logic Score (Pre)",
     '=IFERROR(AVERAGEIF(\'👥 Student Register\'!D3:D32,"C",\'📝 Pre-Test Scores\'!O4:O33),0)',
     '=IFERROR(AVERAGEIF(\'👥 Student Register\'!D3:D32,"I",\'📝 Pre-Test Scores\'!O4:O33),0)',
     "=C{r}-B{r}"),
    ("Avg Clinical+Logic Score (Post)",
     '=IFERROR(AVERAGEIF(\'👥 Student Register\'!D3:D32,"C",\'📝 Post-Test Scores\'!O4:O33),0)',
     '=IFERROR(AVERAGEIF(\'👥 Student Register\'!D3:D32,"I",\'📝 Post-Test Scores\'!O4:O33),0)',
     "=C{r}-B{r}"),
    ("Pass Rate Pre-Test",
     '=IFERROR(COUNTIFS(\'👥 Student Register\'!D3:D32,"C",\'📝 Pre-Test Scores\'!P4:P33,"PASS")/COUNTIF(\'👥 Student Register\'!D3:D32,"C"),0)',
     '=IFERROR(COUNTIFS(\'👥 Student Register\'!D3:D32,"I",\'📝 Pre-Test Scores\'!P4:P33,"PASS")/COUNTIF(\'👥 Student Register\'!D3:D32,"I"),0)',
     "=C{r}-B{r}"),
    ("Pass Rate Post-Test",
     '=IFERROR(COUNTIFS(\'👥 Student Register\'!D3:D32,"C",\'📝 Post-Test Scores\'!P4:P33,"PASS")/COUNTIF(\'👥 Student Register\'!D3:D32,"C"),0)',
     '=IFERROR(COUNTIFS(\'👥 Student Register\'!D3:D32,"I",\'📝 Post-Test Scores\'!P4:P33,"PASS")/COUNTIF(\'👥 Student Register\'!D3:D32,"I"),0)',
     "=C{r}-B{r}"),
]

pct_rows = set()
for idx, (label, ctrl_f, int_f, diff_f) in enumerate(metrics):
    r = gr + 2 + idx
    ws_an[f"A{r}"] = label
    ws_an[f"A{r}"].font = body(bold=True)
    ws_an[f"A{r}"].border = thin_border()
    ws_an[f"A{r}"].alignment = left()

    ws_an[f"B{r}"] = ctrl_f
    ws_an[f"C{r}"] = int_f
    ws_an[f"D{r}"] = diff_f.format(r=r)

    for col in ["B","C","D"]:
        c = ws_an[f"{col}{r}"]
        c.font = body()
        c.alignment = center()
        c.border = thin_border()
        c.number_format = "0.00"

    if "Pass Rate" in label:
        pct_rows.add(r)
        for col in ["B","C","D"]:
            ws_an[f"{col}{r}"].number_format = "0.0%"

    if idx % 2 == 0:
        for col in ["A","B","C","D"]:
            ws_an[f"{col}{r}"].fill = fill(C_GREY)

# ── Section 3: Bloom's Breakdown ──
br = gr + 2 + len(metrics) + 2
ws_an.merge_cells(f"A{br}:J{br}")
ws_an[f"A{br}"] = "BLOOM'S TAXONOMY BREAKDOWN — Class Average per Question"
ws_an[f"A{br}"].font = Font(name=FONT_NAME, bold=True, size=11, color=C_WHITE)
ws_an[f"A{br}"].fill = fill(C_MID_BLUE)
ws_an[f"A{br}"].alignment = center()

col_header(ws_an, br+1, {
    "A":"Question","B":"Type","C":"Bloom's Level",
    "D":"Avg Pre-Test\nScore","E":"Avg Post-Test\nScore","F":"Improvement"
}, bg=C_DARK_BLUE)
ws_an.row_dimensions[br+1].height = 36

bloom_levels = [
    ("Q1 — EDD Calculation",          "Recall",   "L1: Remember"),
    ("Q2 — Routine Lab Tests",         "Recall",   "L1: Remember"),
    ("Q3 — Serum Screening",           "Recall",   "L2: Understand"),
    ("Q4 — Dating Discrepancy",        "Clinical", "L3: Apply"),
    ("Q5 — GDM Screening",             "Clinical", "L3: Apply"),
    ("Q6 — Progestin Logic",           "Logic",    "L4: Analyze"),
    ("Q7 — Placenta Previa Advice",    "Clinical", "L3: Apply"),
    ("Q8 — Exercise in Pregnancy",     "Clinical", "L3: Apply"),
    ("Q9 — Evidence Appraisal",        "Logic",    "L5: Evaluate"),
    ("Q10 — Socio-econ Barriers",      "Logic",    "L4: Analyze"),
]

pre_q_cols  = ["C","D","E","F","G","H","I","J","K","L"]
post_q_cols = ["C","D","E","F","G","H","I","J","K","L"]

for idx, (qlabel, qtype, bloom) in enumerate(bloom_levels):
    r = br + 2 + idx
    pre_col  = pre_q_cols[idx]
    post_col = post_q_cols[idx]

    ws_an[f"A{r}"] = qlabel
    ws_an[f"B{r}"] = qtype
    ws_an[f"C{r}"] = bloom
    ws_an[f"D{r}"] = f"='📝 Pre-Test Scores'!{pre_col}35"
    ws_an[f"E{r}"] = f"='📝 Post-Test Scores'!{post_col}35"
    ws_an[f"F{r}"] = f"=E{r}-D{r}"

    for col in ["A","B","C","D","E","F"]:
        c = ws_an[f"{col}{r}"]
        c.font = body(bold=(col in ["A","B","C"]))
        c.alignment = center() if col in ["B","C","D","E","F"] else left()
        c.border = thin_border()
        c.number_format = "0.00"
        if idx % 2 == 0:
            c.fill = fill(C_GREY)

    # Color type column
    ws_an[f"B{r}"].fill = fill(C_MID_BLUE if qtype=="Recall" else "1E7145")
    ws_an[f"B{r}"].font = Font(name=FONT_NAME, bold=True, color=C_WHITE)

# Color scale on improvement col F
ws_an.conditional_formatting.add(f"F{br+2}:F{br+11}",
    ColorScaleRule(start_type="num", start_value=-1, start_color="FFC7CE",
                   mid_type="num",   mid_value=0,    mid_color="FFEB9C",
                   end_type="num",   end_value=1,    end_color="C6EFCE"))

set_col_widths(ws_an, {
    "A":32,"B":16,"C":22,"D":16,"E":16,"F":14,"G":14,"H":14,"I":14,"J":14
})


# ─────────────────────────────────────────────────────────────────────────────
# SHEET 6 — LEADERBOARD
# ─────────────────────────────────────────────────────────────────────────────
ws_lb = wb.create_sheet("🏆 Leaderboard")
ws_lb.sheet_view.showGridLines = False

ws_lb.merge_cells("A1:H1")
merge_title(ws_lb, "A1", "🏆 Leaderboard — Post-Test Rankings", font_size=14)
ws_lb.row_dimensions[1].height = 36

ws_lb.merge_cells("A2:H2")
ws_lb["A2"] = "ℹ️  This sheet uses LARGE() formulas to rank students by Post-Test score. Data refreshes automatically when scores are entered."
ws_lb["A2"].font = Font(name=FONT_NAME, italic=True, size=10, color="555555")
ws_lb["A2"].alignment = left()

col_header(ws_lb, 3, {
    "A": "Rank",
    "B": "Student ID",
    "C": "Student Name",
    "D": "Group\n(C/I)",
    "E": "Pre-Test\nScore (/10)",
    "F": "Post-Test\nScore (/10)",
    "G": "Score Gain",
    "H": "Medal",
}, bg=C_DARK_BLUE)
ws_lb.row_dimensions[3].height = 40

for i in range(1, 31):
    r = i + 3
    # Rank by Post-Test score using LARGE + INDEX/MATCH
    ws_lb[f"A{r}"] = i
    ws_lb[f"A{r}"].alignment = center()
    ws_lb[f"A{r}"].font = body(bold=True)
    ws_lb[f"A{r}"].border = thin_border()

    # Use LARGE to find the i-th highest post score, then MATCH to find row
    # We use a helper: sort by post-test score descending
    ws_lb[f"F{r}"] = f"=IFERROR(LARGE('📝 Post-Test Scores'!M4:M33,{i}),\"\")"
    ws_lb[f"B{r}"] = f"=IFERROR(INDEX('👥 Student Register'!B3:B32,MATCH(F{r},'📝 Post-Test Scores'!M4:M33,0)),\"\")"
    ws_lb[f"C{r}"] = f"=IFERROR(INDEX('👥 Student Register'!C3:C32,MATCH(F{r},'📝 Post-Test Scores'!M4:M33,0)),\"\")"
    ws_lb[f"D{r}"] = f"=IFERROR(INDEX('👥 Student Register'!D3:D32,MATCH(F{r},'📝 Post-Test Scores'!M4:M33,0)),\"\")"
    ws_lb[f"E{r}"] = f"=IFERROR(INDEX('📝 Pre-Test Scores'!M4:M33,MATCH(F{r},'📝 Post-Test Scores'!M4:M33,0)),\"\")"
    ws_lb[f"G{r}"] = f"=IFERROR(F{r}-E{r},\"\")"
    ws_lb[f"H{r}"] = f'=IF(A{r}=1,"🥇 Gold",IF(A{r}=2,"🥈 Silver",IF(A{r}=3,"🥉 Bronze","")))'

    for col in ["B","C","D","E","F","G","H"]:
        c = ws_lb[f"{col}{r}"]
        c.font = Font(name=FONT_NAME, size=10,
                      color="006400" if col in ["B","C","D"] else "000000")
        c.alignment = center()
        c.border = thin_border()
        if i <= 3:
            c.fill = fill("FFF2CC")  # Gold highlight for top 3
        elif i % 2 == 0:
            c.fill = fill("EAF4FB")

    # Rank number highlight for top 3
    if i <= 3:
        ws_lb[f"A{r}"].fill = fill("FFD700" if i==1 else ("C0C0C0" if i==2 else "CD7F32"))
        ws_lb[f"A{r}"].font = Font(name=FONT_NAME, bold=True, size=11,
                                   color="FFFFFF" if i==1 else "000000")

# Color scale on post-test score
ws_lb.conditional_formatting.add("F4:F33",
    ColorScaleRule(start_type="num", start_value=0, start_color="FFC7CE",
                   mid_type="num",   mid_value=5,   mid_color="FFEB9C",
                   end_type="num",   end_value=10,  end_color="C6EFCE"))

set_col_widths(ws_lb, {
    "A":7,"B":14,"C":28,"D":10,"E":14,"F":14,"G":12,"H":12
})


# ─────────────────────────────────────────────────────────────────────────────
# Save
# ─────────────────────────────────────────────────────────────────────────────
out_path = "/home/daytona/workspace/anc-teaching-project/ANC_Learning_Outcomes_Tracker.xlsx"
wb.save(out_path)
print(f"Saved to: {out_path}")

Successfully replaced 1 instance(s) of the string in '/home/daytona/workspace/anc-teaching-project/build_tracker.py'