import openpyxl
from openpyxl.styles import Font, Alignment, PatternFill, Border, Side
from openpyxl.utils import get_column_letter

wb = openpyxl.Workbook()
ws = wb.active
ws.title = "Percentage Conversion"

FONT = "Arial"
header_font = Font(name=FONT, bold=True, color="FFFFFF", size=11)
header_fill = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid")
title_font = Font(name=FONT, bold=True, size=14)
sub_font = Font(name=FONT, bold=True, size=11, color="FFFFFF")
sub_fill = PatternFill(start_color="8EA9DB", end_color="8EA9DB", fill_type="solid")
normal_font = Font(name=FONT, size=11)
thin = Side(style="thin", color="B7B7B7")
border = Border(left=thin, right=thin, top=thin, bottom=thin)
center = Alignment(horizontal="center", vertical="center")

# ---- Data (as read from the photo) ----
intra_1 = [0.75,0.5,0.55,0.45,0.55,0.75,0.9,0.75,0.55,0.4,1,0.75,0.9,0.55,0.2,0.65,1,1,0.4,1,0.35,1]
intra_10 = [7,7.5,9.5,8.5,10,9.5,9,5.5,8.5,6.5,7,7,10,5.5,4.5,7.5,6.5,8.5]

inter_1 = [0.65,0.45,0.45,0.35,0.45,0.45,0.85,0.65,0.35,0.35,0.95,0.65,0.85,0.55,0.15,0.55,1,1,0.34,1,0.25,1]
inter_10 = [6.5,6.5,8.5,6.5,9.5,8.5,8.5,3.5,7.5,5.5,6.5,8.5,10,4.5,3,4.5,5.5,8]

# Title
ws.merge_cells("A1:F1")
ws["A1"] = "Intramanual / Intermanual Raw Scores Converted to Percentages"
ws["A1"].font = title_font
ws["A1"].alignment = center

ws["A2"] = "Rule: values scored out of 1 -> % = value x 100. Values scored out of 10 -> % = value x 10."
ws["A2"].font = Font(name=FONT, italic=True, size=10)
ws.merge_cells("A2:F2")

row0 = 4

def write_block(start_row, start_col, title, raw_list, max_val):
    r = start_row
    c = start_col
    ws.merge_cells(start_row=r, start_column=c, end_row=r, end_column=c+1)
    cell = ws.cell(row=r, column=c, value=f"{title} (out of {max_val})")
    cell.font = sub_font
    cell.fill = sub_fill
    cell.alignment = center
    r += 1
    h1 = ws.cell(row=r, column=c, value="Raw Score")
    h2 = ws.cell(row=r, column=c+1, value="Percentage")
    for h in (h1, h2):
        h.font = header_font
        h.fill = header_fill
        h.alignment = center
        h.border = border
    r += 1
    first_data_row = r
    for v in raw_list:
        rc = ws.cell(row=r, column=c, value=v)
        pc = ws.cell(row=r, column=c+1, value=f"=A{r}/{max_val}" if False else None)
        rc.font = normal_font
        rc.alignment = center
        rc.border = border
        pcell = ws.cell(row=r, column=c+1)
        col_letter = get_column_letter(c)
        pcell.value = f"={col_letter}{r}/{max_val}"
        pcell.number_format = "0.0%"
        pcell.font = normal_font
        pcell.alignment = center
        pcell.border = border
        r += 1
    return first_data_row, r-1

# INTRAMANUAL blocks
fr1, lr1 = write_block(row0, 1, "INTRAMANUAL", intra_1, 1)
fr2, lr2 = write_block(row0, 4, "INTRAMANUAL", intra_10, 10)

# leave gap, then INTERMANUAL blocks below the taller of the two
next_row = max(lr1, lr2) + 3
ws.merge_cells(start_row=next_row-1, start_column=1, end_row=next_row-1, end_column=6)

fr3, lr3 = write_block(next_row, 1, "INTERMANUAL", inter_1, 1)
fr4, lr4 = write_block(next_row, 4, "INTERMANUAL", inter_10, 10)

# Column widths
widths = {1:14, 2:13, 3:3, 4:14, 5:13, 6:3}
for col, w in widths.items():
    ws.column_dimensions[get_column_letter(col)].width = w

wb.save("/tmp/workspace/manual-dexterity/Intramanual_Intermanual_Percentages.xlsx")
print("saved")