import pandas as pd
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side

df = pd.read_excel("/home/nsm/dr-transcripts-deductive-V1/outputs/analysis/detailed_justifications.xlsx", sheet_name="Justifications")
df = df[["group", "theme", "code"]].dropna(subset=["group", "theme", "code"])
df = df.drop_duplicates()
df = df.sort_values(["group", "theme", "code"])

wb = Workbook()
ws = wb.active
ws.title = "Hierarchy Table"

header_font  = Font(name="Arial", bold=True, color="FFFFFF", size=11)
header_fill  = PatternFill("solid", start_color="1F4E79")
group_fill   = PatternFill("solid", start_color="D6E4F0")
theme_fill   = PatternFill("solid", start_color="EBF3FB")
code_fill    = PatternFill("solid", start_color="FFFFFF")
center_align = Alignment(horizontal="center", vertical="center", wrap_text=True)
left_align   = Alignment(horizontal="left",   vertical="center", wrap_text=True)
thin         = Side(style="thin", color="B0B0B0")
border       = Border(left=thin, right=thin, top=thin, bottom=thin)

def style_cell(cell, font=None, fill=None, alignment=None):
    cell.border = border
    if font:      cell.font      = font
    if fill:      cell.fill      = fill
    if alignment: cell.alignment = alignment

for col, h in enumerate(["Group", "Theme", "Code"], 1):
    cell = ws.cell(row=1, column=col, value=h)
    style_cell(cell, font=header_font, fill=header_fill, alignment=center_align)

ws.column_dimensions["A"].width = 35
ws.column_dimensions["B"].width = 35
ws.column_dimensions["C"].width = 45

current_row = 2

for group, g_df in df.groupby("group", sort=False):
    group_start = current_row
    for theme, t_df in g_df.groupby("theme", sort=False):
        theme_start = current_row
        for code in t_df["code"].unique().tolist():
            ws.cell(row=current_row, column=1)
            ws.cell(row=current_row, column=2)
            cell_c = ws.cell(row=current_row, column=3, value=code)
            style_cell(cell_c, fill=code_fill, alignment=left_align)
            cell_c.font = Font(name="Arial", size=10)
            current_row += 1
        ws.merge_cells(start_row=theme_start, start_column=2, end_row=current_row-1, end_column=2)
        cell_t = ws.cell(row=theme_start, column=2, value=theme)
        style_cell(cell_t, fill=theme_fill, alignment=center_align)
        cell_t.font = Font(name="Arial", size=10, bold=True)
    ws.merge_cells(start_row=group_start, start_column=1, end_row=current_row-1, end_column=1)
    cell_g = ws.cell(row=group_start, column=1, value=group)
    style_cell(cell_g, fill=group_fill, alignment=center_align)
    cell_g.font = Font(name="Arial", size=10, bold=True)

ws.freeze_panes = "A2"
wb.save("hierarchy_table_output.xlsx")
print("Done → hierarchy_table_output.xlsx")