"""Build the Excel calculator predictor_ypN0.xlsx using openpyxl."""

import math
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

OUT = Path(__file__).parent / "predictor_ypN0.xlsx"

# Replicate the predictor's coefficients (must match predictor.py)
INTERCEPT = math.log(0.10 / 0.90)
OR_HR_HER2pos = 40.0
OR_HRpos_HER2pos = 14.82
OR_TNBC = 6.91
OR_KI67_HIGH = 3.0
OR_AGE_PER_YEAR = 0.99
OR_CN23 = 0.65
OR_PERTU = 1.5
OR_CARBO = 1.4
OR_PEMBRO = 1.42
OR_GEN_HIGH = 2.0
OR_GEN_LOW = 0.5

# ---------------------------------------------------------------------------
# Styles
# ---------------------------------------------------------------------------
thin = Side(border_style="thin", color="888888")
BORDER_ALL = Border(left=thin, right=thin, top=thin, bottom=thin)
HEADER_FILL = PatternFill("solid", fgColor="1F4E78")
HEADER_FONT = Font(bold=True, color="FFFFFF", size=12)
INPUT_FILL = PatternFill("solid", fgColor="FFF2CC")
OUT_FILL = PatternFill("solid", fgColor="D9E1F2")
TITLE_FONT = Font(bold=True, size=16, color="1F4E78")
BAND_LOW = PatternFill("solid", fgColor="C6EFCE")
BAND_MED = PatternFill("solid", fgColor="FFEB9C")
BAND_HIGH = PatternFill("solid", fgColor="F4CCCC")

wb = Workbook()

# ===========================================================================
# SHEET 1: CALCULADORA
# ===========================================================================
ws = wb.active
ws.title = "Calculadora"

ws["A1"] = "Calculadora ypN0 -- Nivel 1"
ws["A1"].font = TITLE_FONT
ws.merge_cells("A1:D1")

ws["A2"] = ("Probabilidad de respuesta patologica completa axilar tras "
            "neoadyuvancia en cancer de mama cN+")
ws.merge_cells("A2:D2")
ws["A2"].font = Font(italic=True, color="555555")

# --- Labels ----------------------------------------------------------------
labels = [
    ("A4", "Variable",                "B4", "Valor"),
    ("A5", "Subtipo molecular",       "B5", "HR+/HER2-"),
    ("A6", "Ki67 (%)",                "B6", 20),
    ("A7", "Edad (anios)",            "B7", 50),
    ("A8", "Estadio axilar clinico",  "B8", "cN1"),
    ("A9", "Pertuzumab (si HER2+)",   "B9", "No"),
    ("A10", "Carboplatino (si TNBC)", "B10", "No"),
    ("A11", "Pembrolizumab (si TNBC)", "B11", "No"),
    ("A12", "Riesgo genomico (si HR+/HER2-)", "B12", "desconocido"),
]
ws["A4"] = "Variable";  ws["B4"] = "Valor"
ws["A4"].font = HEADER_FONT; ws["A4"].fill = HEADER_FILL
ws["B4"].font = HEADER_FONT; ws["B4"].fill = HEADER_FILL

input_rows = [
    ("Subtipo molecular",            "HR+/HER2-"),
    ("Ki67 (%)",                     20),
    ("Edad (anios)",                 50),
    ("Estadio axilar clinico",       "cN1"),
    ("Pertuzumab (si HER2+)",        "No"),
    ("Carboplatino (si TNBC)",       "No"),
    ("Pembrolizumab (si TNBC)",      "No"),
    ("Riesgo genomico (si HR+/HER2-)", "desconocido"),
]
for i, (lab, val) in enumerate(input_rows, start=5):
    ws[f"A{i}"] = lab
    ws[f"B{i}"] = val
    ws[f"B{i}"].fill = INPUT_FILL
    ws[f"B{i}"].border = BORDER_ALL
    ws[f"A{i}"].font = Font(bold=True)

# Data validations
dv_subtype = DataValidation(type="list",
                            formula1='"HR+/HER2-,HR+/HER2+,HR-/HER2+,TNBC"',
                            allow_blank=False)
dv_subtype.add("B5")
ws.add_data_validation(dv_subtype)

dv_cn = DataValidation(type="list", formula1='"cN1,cN2,cN3"', allow_blank=False)
dv_cn.add("B8")
ws.add_data_validation(dv_cn)

dv_yesno = DataValidation(type="list", formula1='"Si,No"', allow_blank=False)
dv_yesno.add("B9"); dv_yesno.add("B10"); dv_yesno.add("B11")
ws.add_data_validation(dv_yesno)

dv_gen = DataValidation(type="list", formula1='"desconocido,bajo,alto"',
                        allow_blank=False)
dv_gen.add("B12")
ws.add_data_validation(dv_gen)

# --- Calculation cells (hidden helpers) ------------------------------------
ws["D4"] = "Calculo (log-odds)"; ws["D4"].font = HEADER_FONT
ws["D4"].fill = HEADER_FILL
ws["E4"] = "Valor"; ws["E4"].font = HEADER_FONT; ws["E4"].fill = HEADER_FILL

# Each component formula references B5..B12
components = [
    ("Intercepto (ancla 10%)", f"=LN(0.10/0.90)"),
    ("Subtipo (log OR)",
     f'=IF(B5="HR+/HER2-",0,'
     f'IF(B5="HR+/HER2+",LN({OR_HRpos_HER2pos}),'
     f'IF(B5="HR-/HER2+",LN({OR_HR_HER2pos}),'
     f'IF(B5="TNBC",LN({OR_TNBC}),0))))'),
    ("Ki67>=20% (solo HR+/HER2-)",
     f'=IF(AND(B5="HR+/HER2-",B6>=20),LN({OR_KI67_HIGH}),0)'),
    ("Edad (por anio centrado en 50)",
     f'=(B7-50)*LN({OR_AGE_PER_YEAR})'),
    ("cN2-3 (vs cN1)",
     f'=IF(OR(B8="cN2",B8="cN3"),LN({OR_CN23}),0)'),
    ("Pertuzumab (HER2+)",
     f'=IF(AND(OR(B5="HR+/HER2+",B5="HR-/HER2+"),B9="Si"),LN({OR_PERTU}),0)'),
    ("Carboplatino (TNBC)",
     f'=IF(AND(B5="TNBC",B10="Si"),LN({OR_CARBO}),0)'),
    ("Pembrolizumab (TNBC)",
     f'=IF(AND(B5="TNBC",B11="Si"),LN({OR_PEMBRO}),0)'),
    ("Riesgo genomico alto (HR+/HER2-)",
     f'=IF(AND(B5="HR+/HER2-",B12="alto"),LN({OR_GEN_HIGH}),0)'),
    ("Riesgo genomico bajo (HR+/HER2-)",
     f'=IF(AND(B5="HR+/HER2-",B12="bajo"),LN({OR_GEN_LOW}),0)'),
]
for i, (lab, formula) in enumerate(components, start=5):
    ws[f"D{i}"] = lab
    ws[f"E{i}"] = formula
    ws[f"E{i}"].number_format = "0.000"
    ws[f"D{i}"].font = Font(size=10, color="555555")
    ws[f"E{i}"].font = Font(size=10, color="555555")

# Sum of log-odds
sum_row = 5 + len(components)
ws[f"D{sum_row}"] = "Suma log-odds"
ws[f"D{sum_row}"].font = Font(bold=True)
ws[f"E{sum_row}"] = f"=SUM(E5:E{sum_row-1})"
ws[f"E{sum_row}"].font = Font(bold=True)
ws[f"E{sum_row}"].number_format = "0.000"

# --- Output ----------------------------------------------------------------
out_row = 14
ws[f"A{out_row}"] = "RESULTADO"
ws[f"A{out_row}"].font = HEADER_FONT
ws[f"A{out_row}"].fill = HEADER_FILL
ws.merge_cells(f"A{out_row}:B{out_row}")

ws[f"A{out_row+1}"] = "Probabilidad ypN0"
ws[f"B{out_row+1}"] = f"=EXP(E{sum_row})/(1+EXP(E{sum_row}))"
ws[f"B{out_row+1}"].number_format = "0.0%"
ws[f"B{out_row+1}"].fill = OUT_FILL
ws[f"B{out_row+1}"].font = Font(bold=True, size=14, color="1F4E78")
ws[f"B{out_row+1}"].border = BORDER_ALL

ws[f"A{out_row+2}"] = "Banda de riesgo"
ws[f"B{out_row+2}"] = (
    f'=IF(B{out_row+1}<0.20,"Bajo (<20%)",'
    f'IF(B{out_row+1}<0.50,"Medio (20-50%)","Alto (>=50%)"))'
)
ws[f"B{out_row+2}"].fill = OUT_FILL
ws[f"B{out_row+2}"].font = Font(bold=True, size=12)
ws[f"B{out_row+2}"].border = BORDER_ALL

ws[f"A{out_row+3}"] = "Interpretacion"
ws[f"B{out_row+3}"] = (
    f'=IF(B{out_row+1}<0.20,"Considerar ALND estandar / sin desescalada axilar",'
    f'IF(B{out_row+1}<0.50,"Decision individualizada; valorar TAD/MARI o SLNB+ALND",'
    f'"Buen candidato a SLNB/TAD sin ALND si SLN- (segun guias)"))'
)
ws[f"B{out_row+3}"].alignment = Alignment(wrap_text=True, vertical="center")
ws.row_dimensions[out_row+3].height = 40

# Conditional formatting via direct fill is not dynamic in openpyxl,
# but we can add real conditional formatting:
from openpyxl.formatting.rule import CellIsRule
ws.conditional_formatting.add(
    f"B{out_row+2}",
    CellIsRule(operator="equal", formula=['"Bajo (<20%)"'], fill=BAND_LOW)
)
ws.conditional_formatting.add(
    f"B{out_row+2}",
    CellIsRule(operator="equal", formula=['"Medio (20-50%)"'], fill=BAND_MED)
)
ws.conditional_formatting.add(
    f"B{out_row+2}",
    CellIsRule(operator="equal", formula=['"Alto (>=50%)"'], fill=BAND_HIGH)
)

# Column widths
ws.column_dimensions["A"].width = 32
ws.column_dimensions["B"].width = 28
ws.column_dimensions["C"].width = 3
ws.column_dimensions["D"].width = 36
ws.column_dimensions["E"].width = 12

# Note
note_row = out_row + 5
ws[f"A{note_row}"] = ("NOTA: las celdas amarillas son entradas; el resto es "
                     "calculo automatico. Banda de color segun nivel de riesgo.")
ws[f"A{note_row}"].font = Font(italic=True, size=9, color="777777")
ws.merge_cells(f"A{note_row}:E{note_row}")

# ===========================================================================
# SHEET 2: COEFICIENTES
# ===========================================================================
ws2 = wb.create_sheet("Coeficientes")
ws2["A1"] = "Coeficientes del modelo logistico nivel 1"
ws2["A1"].font = TITLE_FONT
ws2.merge_cells("A1:F1")

headers2 = ["Predictor", "Comparacion", "OR", "log(OR)", "Fuente (PMID)", "Notas"]
for i, h in enumerate(headers2, start=1):
    c = ws2.cell(row=3, column=i, value=h)
    c.font = HEADER_FONT
    c.fill = HEADER_FILL
    c.border = BORDER_ALL

coef_rows = [
    ("Intercepto", "HR+/HER2-, cN1, edad 50, Ki67 20%, NAC estandar",
     0.10/0.90, INTERCEPT, "Calibracion",
     "Probabilidad ancla ~10% (Samiei 2021, van Amstel 2024)"),
    ("Subtipo", "HR-/HER2+ vs HR+/HER2-", OR_HR_HER2pos, math.log(OR_HR_HER2pos),
     "39302345 (van Amstel BJS 2024)", "OR muy alto; IC no reportado en abstract"),
    ("Subtipo", "HR+/HER2+ vs HR+/HER2-", OR_HRpos_HER2pos,
     math.log(OR_HRpos_HER2pos),
     "39302345 (van Amstel BJS 2024)", "RISAS cN+ n=185"),
    ("Subtipo", "TNBC vs HR+/HER2-", OR_TNBC, math.log(OR_TNBC),
     "39302345 (van Amstel BJS 2024)", "p<0.001 para efecto global"),
    ("Ki67", ">=20% vs <20% (solo HR+/HER2-)", OR_KI67_HIGH,
     math.log(OR_KI67_HIGH),
     "35569077 (Boughey 2022)", "Tasa npCR 28.4% vs 8.1% en luminales"),
    ("Edad", "por anio (continua)", OR_AGE_PER_YEAR,
     math.log(OR_AGE_PER_YEAR),
     "40684017 (An NCDB 2025)", "Efecto pequeno pero consistente"),
    ("cN", "cN2-3 vs cN1", OR_CN23, math.log(OR_CN23),
     "39745737 (Cabioglu NEOSENTITURK 2025)",
     "Disease-specific survival peor en cN2-3"),
    ("Regimen", "Pertuzumab en HER2+", OR_PERTU, math.log(OR_PERTU),
     "TRAIN-2 / Chowdhary", "Anadir HP a quimio mejora pCR"),
    ("Regimen", "Carboplatino en TNBC", OR_CARBO, math.log(OR_CARBO),
     "NeoSTOP / GeparSixto", "Efecto modesto, AE manejable"),
    ("Regimen", "Pembrolizumab en TNBC", OR_PEMBRO, math.log(OR_PEMBRO),
     "KEYNOTE-522 (Schmid 2020)", "Nodal pCR 76.7% vs 69.9% (OR derivado ~1.42)"),
    ("Genomico", "Alto vs intermedio (HR+/HER2-)", OR_GEN_HIGH,
     math.log(OR_GEN_HIGH),
     "35876925 (Boughey 2022)", "MammaPrint/Oncotype alto -> mas pCR"),
    ("Genomico", "Bajo vs intermedio (HR+/HER2-)", OR_GEN_LOW,
     math.log(OR_GEN_LOW),
     "35876925 (Boughey 2022)", "Bajo -> menos pCR (OR 0.49 residuo)"),
]
for i, row in enumerate(coef_rows, start=4):
    for j, val in enumerate(row, start=1):
        c = ws2.cell(row=i, column=j, value=val)
        c.border = BORDER_ALL
        if isinstance(val, float):
            c.number_format = "0.000"

for col_letter, width in zip("ABCDEF", (12, 38, 8, 9, 32, 50)):
    ws2.column_dimensions[col_letter].width = width

# ===========================================================================
# SHEET 3: CASOS TEST
# ===========================================================================
ws3 = wb.create_sheet("Casos test")
ws3["A1"] = "Casos clinicos de prueba con probabilidades esperadas"
ws3["A1"].font = TITLE_FONT
ws3.merge_cells("A1:K1")

headers3 = ["#", "Descripcion", "Subtipo", "Ki67%", "Edad", "cN",
            "Pertu", "Carbo", "Pembro", "Riesgo gen.", "ypN0 esperado"]
for i, h in enumerate(headers3, start=1):
    c = ws3.cell(row=3, column=i, value=h)
    c.font = HEADER_FONT; c.fill = HEADER_FILL; c.border = BORDER_ALL

test_cases = [
    (1, "HR+/HER2- luminal A, edad 65, Ki67 10%, cN1",
     "HR+/HER2-", 10, 65, "cN1", "No", "No", "No", "desconocido", "8.7%"),
    (2, "HR+/HER2- joven, Ki67 30%, cN1, riesgo genomico alto",
     "HR+/HER2-", 30, 45, "cN1", "No", "No", "No", "alto", "41.2%"),
    (3, "TNBC, edad 45, cN1, AC-T+carbo+pembrolizumab",
     "TNBC", 70, 45, "cN1", "No", "Si", "Si", "desconocido", "61.6%"),
    (4, "TNBC, edad 60, cN2, sin agentes especiales",
     "TNBC", 60, 60, "cN2", "No", "No", "No", "desconocido", "31.1%"),
    (5, "HR-/HER2+, edad 50, cN1, TCH+pertuzumab",
     "HR-/HER2+", 40, 50, "cN1", "Si", "No", "No", "desconocido", "87.0%"),
    (6, "HR+/HER2+, edad 55, cN2, TCH+pertuzumab",
     "HR+/HER2+", 25, 55, "cN2", "Si", "No", "No", "desconocido", "60.4%"),
    (7, "HR+/HER2+, edad 70, cN1, TCH sin pertuzumab",
     "HR+/HER2+", 15, 70, "cN1", "No", "No", "No", "desconocido", "57.4%"),
    (8, "HR+/HER2- joven, riesgo genomico bajo, cN1",
     "HR+/HER2-", 15, 42, "cN1", "No", "No", "No", "bajo", "5.7%"),
    (9, "TNBC, edad 38, cN3, AC-T+carbo+pembro",
     "TNBC", 80, 38, "cN3", "No", "Si", "Si", "desconocido", "52.8%"),
    (10, "HR-/HER2+, edad 35, cN3, TCHP",
     "HR-/HER2+", 60, 35, "cN3", "Si", "No", "No", "desconocido", "83.4%"),
]
for i, row in enumerate(test_cases, start=4):
    for j, val in enumerate(row, start=1):
        c = ws3.cell(row=i, column=j, value=val)
        c.border = BORDER_ALL

for col_letter, width in zip("ABCDEFGHIJK",
                             (4, 45, 12, 7, 7, 6, 7, 7, 7, 14, 14)):
    ws3.column_dimensions[col_letter].width = width

ws3["A16"] = ("Estos valores se obtienen ejecutando predictor.py "
              "(modulo de referencia).")
ws3["A16"].font = Font(italic=True, size=9, color="777777")
ws3.merge_cells("A16:K16")

# ===========================================================================
# SHEET 4: INSTRUCCIONES
# ===========================================================================
ws4 = wb.create_sheet("Instrucciones")
ws4["A1"] = "Instrucciones de uso"
ws4["A1"].font = TITLE_FONT
ws4.merge_cells("A1:A1")

instructions = [
    "",
    "1. OBJETIVO",
    "Esta calculadora estima la probabilidad de respuesta patologica completa",
    "axilar (ypN0) tras quimioterapia neoadyuvante en pacientes con cancer de",
    "mama y axila clinicamente positiva (cN+) al diagnostico.",
    "",
    "2. COMO USARLA",
    "- Ve a la hoja 'Calculadora'.",
    "- Rellena las celdas amarillas con los datos de la paciente.",
    "  * Subtipo molecular: usa receptores hormonales (HR) y HER2 del CDB.",
    "  * Ki67 %: el valor inmunohistoquimico (solo modifica en HR+/HER2-).",
    "  * Edad: en anios cumplidos.",
    "  * cN: clasificacion clinica/ecografica AJCC al diagnostico.",
    "  * Pertuzumab/Carboplatino/Pembrolizumab: 'Si' si formaron parte del esquema.",
    "  * Riesgo genomico: solo aplica en HR+/HER2- (Oncotype o MammaPrint).",
    "- La probabilidad y la banda de riesgo se calculan automaticamente.",
    "",
    "3. BANDAS DE RIESGO",
    "- Bajo (<20%): bajo rendimiento esperado de desescalada axilar.",
    "- Medio (20-50%): decision individualizada; valorar TAD/MARI o SLNB+ALND.",
    "- Alto (>=50%): candidato a SLNB/TAD sin ALND si SLN- (segun guias).",
    "",
    "4. FUENTES",
    "Ver hoja 'Coeficientes' para ORs, PMIDs y notas de cada predictor.",
    "Estudios clave: van Amstel 2024 (RISAS), Boughey 2022, An 2025 (NCDB),",
    "KEYNOTE-522, TRAIN-2, NeoSTOP, Cabioglu NEOSENTITURK 2025.",
    "",
    "5. LIMITACIONES",
    "- Modelo de NIVEL 1: combinacion deterministica de ORs publicados.",
    "- No validado externamente con cohorte de pacientes individuales.",
    "- No incluye imagen post-NAC (eco/PET/RM) ni biopsia post-NAC.",
    "- Algunas ORs (HR-/HER2+) tienen IC amplios o sin reportar en abstracts.",
    "- Pembrolizumab/Carboplatino solo se aplican en TNBC.",
    "- Pertuzumab solo se aplica en HER2+.",
    "- Riesgo genomico solo se aplica en HR+/HER2-.",
    "",
    "6. AVISO",
    "Esta herramienta es de apoyo a la decision clinica y NO sustituye al",
    "juicio del equipo multidisciplinar ni a las guias institucionales.",
    "",
    "Generado: 2026-05-12   |   Autor: predictor_ypN0 (nivel 1)",
]
for i, line in enumerate(instructions, start=2):
    ws4.cell(row=i, column=1, value=line)
    if line and not line.startswith(" "):
        if line[0].isdigit() and line[1] == ".":
            ws4.cell(row=i, column=1).font = Font(bold=True, color="1F4E78")
ws4.column_dimensions["A"].width = 90

# ===========================================================================
# SAVE
# ===========================================================================
wb.save(OUT)
print(f"Excel generado: {OUT}")
