Ventilator Flow Sheet - Respiratory Therapist

#!/usr/bin/env python3
"""
Ventilator Flow Sheet - Excel Template for Respiratory Therapists
AARC 2024 CPG aligned | ARDSNet targets | VAP Bundle
"""

from openpyxl import Workbook
from openpyxl.styles import (
    PatternFill, Font, Alignment, Border, Side, GradientFill
)
from openpyxl.utils import get_column_letter
from openpyxl.worksheet.datavalidation import DataValidation
from openpyxl.formatting.rule import ColorScaleRule, CellIsRule, FormulaRule
from openpyxl.styles.differential import DifferentialStyle
import openpyxl

wb = Workbook()

# ─────────────────────────────────────────────
# COLOUR PALETTE
# ─────────────────────────────────────────────
DARK_BLUE    = "1F3864"   # header bg
MED_BLUE     = "2E75B6"   # sub-header bg
LIGHT_BLUE   = "DEEAF1"   # alternate row
TEAL_HEADER  = "17375E"
ORANGE_HDR   = "C55A11"
GREEN_HDR    = "375623"
YELLOW_BG    = "FFFF00"
LIGHT_YELLOW = "FFFACD"
LIGHT_GREEN  = "E2EFDA"
LIGHT_RED    = "FFE2E2"
LIGHT_GRAY   = "F2F2F2"
WHITE        = "FFFFFF"
ALERT_RED    = "FF0000"
ALERT_ORANGE = "FFC000"
PASS_GREEN   = "70AD47"

def hdr_font(size=11, bold=True, color=WHITE):
    return Font(name="Arial", size=size, bold=bold, color=color)

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

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

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

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

def thin_border():
    s = Side(style="thin", color="BFBFBF")
    return Border(left=s, right=s, top=s, bottom=s)

def thick_border():
    s = Side(style="medium", color="000000")
    return Border(left=s, right=s, top=s, bottom=s)

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

def merge_write(ws, cell_range, text, bg=DARK_BLUE, fg=WHITE, size=12,
                bold=True, align="center", wrap=False, border=True):
    ws.merge_cells(cell_range)
    first = ws[cell_range.split(":")[0]]
    first.value = text
    first.font = Font(name="Arial", size=size, bold=bold, color=fg)
    first.fill = fill(bg)
    first.alignment = Alignment(horizontal=align, vertical="center",
                                 wrap_text=wrap)
    if border:
        first.border = thick_border()

def write_cell(ws, row, col, value, bg=WHITE, fg="000000", size=10,
               bold=False, h_align="left", wrap=True, border=True,
               number_format=None):
    c = ws.cell(row=row, column=col, value=value)
    c.font = Font(name="Arial", size=size, bold=bold, color=fg)
    c.fill = fill(bg)
    c.alignment = Alignment(horizontal=h_align, vertical="center",
                             wrap_text=wrap)
    if border:
        c.border = thin_border()
    if number_format:
        c.number_format = number_format
    return c

def input_cell(ws, row, col, value="", bg=LIGHT_YELLOW, border=True):
    c = ws.cell(row=row, column=col, value=value)
    c.font = Font(name="Arial", size=10, color="000080")  # blue = user input
    c.fill = fill(bg)
    c.alignment = Alignment(horizontal="center", vertical="center")
    if border:
        c.border = thin_border()
    return c

# ══════════════════════════════════════════════════════════════════════════════
# SHEET 1 – PATIENT INFO & DEMOGRAPHICS
# ══════════════════════════════════════════════════════════════════════════════
ws1 = wb.active
ws1.title = "Patient Info"
ws1.sheet_view.showGridLines = False
ws1.page_setup.orientation = "landscape"
ws1.page_setup.fitToPage = True

# Title banner
ws1.row_dimensions[1].height = 35
ws1.row_dimensions[2].height = 20
merge_write(ws1, "A1:N1",
            "🫁  MECHANICAL VENTILATION FLOW SHEET  |  RESPIRATORY THERAPY DEPARTMENT",
            bg=DARK_BLUE, fg=WHITE, size=14)
merge_write(ws1, "A2:N2",
            "AARC 2024 Clinical Practice Guideline Aligned  |  ARDSNet Lung-Protective Targets",
            bg=MED_BLUE, fg=WHITE, size=10, bold=False)

# Column widths
widths = [3, 18, 20, 3, 18, 20, 3, 18, 20, 3, 18, 20, 3, 3]
for i, w in enumerate(widths, 1):
    set_col_width(ws1, i, w)

r = 4
# ── PATIENT DEMOGRAPHICS ──────────────────────────────────────────────────────
merge_write(ws1, f"A{r}:N{r}", "PATIENT DEMOGRAPHICS", bg=TEAL_HEADER, size=11)
ws1.row_dimensions[r].height = 22
r += 1

demo_fields = [
    ("Patient Name:", "", "Date of Admission:", "", "MRN #:", "", "Bed / Unit:", ""),
    ("Attending MD:", "", "Consulting MD:", "", "Date of Intubation:", "", "Time of Intubation:", ""),
    ("Height (cm):", "", "Weight (kg):", "", "PBW Male (kg):", "=IF(F{r}>0,ROUND(50+2.3*((F{r}/2.54)-60),1),\"\")",
     "PBW Female (kg):", "=IF(F{r}>0,ROUND(45.5+2.3*((F{r}/2.54)-60),1),\"\")"),
    ("Diagnosis:", "", "Intubation Indication:", "", "Allergies:", "", "Code Status:", ""),
]

label_cols  = [2, 5, 8, 11]
value_cols  = [3, 6, 9, 12]

for row_data in demo_fields:
    ws1.row_dimensions[r].height = 22
    for i, (lbl, val) in enumerate(zip(row_data[::2], row_data[1::2])):
        lc = label_cols[i]
        vc = value_cols[i]
        write_cell(ws1, r, lc, lbl, bg=LIGHT_BLUE, bold=True, h_align="right")
        input_cell(ws1, r, vc)
    r += 1

r += 1

# ── AIRWAY DETAILS ────────────────────────────────────────────────────────────
merge_write(ws1, f"A{r}:N{r}", "AIRWAY DETAILS", bg=TEAL_HEADER, size=11)
ws1.row_dimensions[r].height = 22
r += 1

airway_rows = [
    ("Tube Type:", "ETT / Trach / LMA", "Tube Size (mm ID):", "", "Position at Lip/Stoma (cm):", "", "Confirmed By:", "CXR / Auscultation"),
    ("Cuff Type:", "High volume / Low pressure", "Cuff Pressure Target:", "20-30 cmH₂O", "Humidifier Type:", "HME / Heated wire", "Circuit Change Date:", ""),
    ("Suction Type:", "Inline / Open", "Suction Catheter Size:", "", "Last Tube Change:", "", "Reason:", ""),
]

for row_data in airway_rows:
    ws1.row_dimensions[r].height = 22
    for i, (lbl, val) in enumerate(zip(row_data[::2], row_data[1::2])):
        lc = label_cols[i]
        vc = value_cols[i]
        write_cell(ws1, r, lc, lbl, bg=LIGHT_BLUE, bold=True, h_align="right")
        c = write_cell(ws1, r, vc, val, bg=LIGHT_YELLOW if val == "" else WHITE,
                       fg="000080" if val == "" else "000000")
    r += 1

r += 1

# ── INITIAL VENTILATOR SETTINGS ORDER ────────────────────────────────────────
merge_write(ws1, f"A{r}:N{r}", "INITIAL VENTILATOR SETTINGS ORDER", bg=ORANGE_HDR, size=11)
ws1.row_dimensions[r].height = 22
r += 1

init_settings = [
    ("Mode:", "", "Set Rate (br/min):", "", "Tidal Volume (mL):", "", "VT mL/kg PBW:", ""),
    ("PEEP (cmH₂O):", "", "FiO₂ (%):", "", "Insp Pressure (cmH₂O):", "", "Pressure Support (cmH₂O):", ""),
    ("I:E Ratio:", "", "Insp Time (sec):", "", "Flow Rate (L/min):", "", "Sensitivity/Trigger:", ""),
    ("High P Alarm:", "", "Low P Alarm:", "", "Low VT Alarm:", "", "Apnea Time (sec):", "20"),
    ("Ordered by MD:", "", "Date / Time of Order:", "", "RT Signature:", "", "RT License #:", ""),
]

for row_data in init_settings:
    ws1.row_dimensions[r].height = 22
    for i, (lbl, val) in enumerate(zip(row_data[::2], row_data[1::2])):
        lc = label_cols[i]
        vc = value_cols[i]
        write_cell(ws1, r, lc, lbl, bg=PatternFill("solid", fgColor="FCE4D6").fgColor.rgb if False else "FCE4D6",
                   bold=True, h_align="right")
        # reuse write_cell properly
        c = ws1.cell(row=r, column=lc, value=lbl)
        c.font = Font(name="Arial", size=10, bold=True, color="000000")
        c.fill = fill("FCE4D6")
        c.alignment = Alignment(horizontal="right", vertical="center")
        c.border = thin_border()
        input_cell(ws1, r, vc, val)
    r += 1

r += 1
# ── NOTES ────────────────────────────────────────────────────────────────────
merge_write(ws1, f"A{r}:N{r}", "CLINICAL NOTES / SPECIAL INSTRUCTIONS", bg=GREEN_HDR, size=11)
ws1.row_dimensions[r].height = 22
r += 1
for nr in range(r, r+5):
    ws1.row_dimensions[nr].height = 18
    ws1.merge_cells(f"B{nr}:N{nr}")
    c = ws1.cell(row=nr, column=2)
    c.border = thin_border()
    c.fill = fill(WHITE)
r += 5

# ══════════════════════════════════════════════════════════════════════════════
# SHEET 2 – VENTILATOR CHECKS (4-hourly)
# ══════════════════════════════════════════════════════════════════════════════
ws2 = wb.create_sheet("Vent Checks")
ws2.sheet_view.showGridLines = False
ws2.page_setup.orientation = "landscape"
ws2.page_setup.fitToPage = True

# Title
ws2.row_dimensions[1].height = 35
ws2.row_dimensions[2].height = 20
merge_write(ws2, "A1:P1",
            "🫁  VENTILATOR CHECK LOG  |  Every 4 Hours (±15 min)  |  Document ALL changes",
            bg=DARK_BLUE, fg=WHITE, size=13)
merge_write(ws2, "A1:P1",
            "🫁  VENTILATOR CHECK LOG  |  Every 4 Hours (±15 min)  |  Document ALL changes",
            bg=DARK_BLUE, fg=WHITE, size=13)

merge_write(ws2, "A2:P2",
            "Patient Name: ______________________________    Date: ______________    MRN: ______________    Bed: ______",
            bg=MED_BLUE, fg=WHITE, size=10, bold=False, align="left")

# Column setup: A=category, B=parameter, C=target, D-P=time slots (13 columns, 4-hourly x 3 days)
time_labels = ["00:00","04:00","08:00","12:00","16:00","20:00",
               "00:00","04:00","08:00","12:00","16:00","20:00","24:00"]
col_widths_2 = [18, 28, 22] + [9]*13
for i, w in enumerate(col_widths_2, 1):
    set_col_width(ws2, i, w)

r = 4
# Header row
ws2.row_dimensions[r].height = 35
for col, txt in enumerate(["CATEGORY", "PARAMETER", "TARGET / RANGE"] + time_labels, 1):
    c = ws2.cell(row=r, column=col, value=txt)
    c.font = hdr_font(size=9)
    c.fill = fill(DARK_BLUE)
    c.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
    c.border = thin_border()

# Day labels row
r += 1
ws2.row_dimensions[r].height = 16
merge_write(ws2, f"D{r}:I{r}", "◄ DAY 1 ►", bg=MED_BLUE, size=9, bold=False)
merge_write(ws2, f"J{r}:P{r}", "◄ DAY 2 ►", bg=TEAL_HEADER, size=9, bold=False)
r += 1

# ── Parameters ────────────────────────────────────────────────────────────────
params = [
    # (category, parameter, target, bg_cat, bg_row)
    # SETTINGS
    ("SETTINGS", "Mode (actual)", "Per order", ORANGE_HDR, "FCE4D6"),
    ("",         "Set Rate (br/min)", "Per order", ORANGE_HDR, "FCE4D6"),
    ("",         "Tidal Volume - Set (mL)", "Per order", ORANGE_HDR, "FCE4D6"),
    ("",         "PEEP Set (cmH₂O)", "Per order", ORANGE_HDR, "FCE4D6"),
    ("",         "FiO₂ (%)", "Titrate to SpO₂ target", ORANGE_HDR, "FCE4D6"),
    ("",         "Pressure Support (cmH₂O)", "Per order", ORANGE_HDR, "FCE4D6"),
    ("",         "Insp Pressure (cmH₂O)", "Per order", ORANGE_HDR, "FCE4D6"),
    # MONITORED VALUES
    ("MONITORED", "Total Rate (br/min)", "—", MED_BLUE, LIGHT_BLUE),
    ("",          "VT Exhaled (mL)", "See below", MED_BLUE, LIGHT_BLUE),
    ("",          "VT (mL/kg PBW) ⚠", "4–8 mL/kg", MED_BLUE, LIGHT_BLUE),
    ("",          "Minute Volume (L/min)", "5–10 L/min", MED_BLUE, LIGHT_BLUE),
    ("",          "Peak Insp Pressure (PIP, cmH₂O)", "< 35–40 cmH₂O", MED_BLUE, LIGHT_BLUE),
    ("",          "Plateau Pressure (Pplat) ⚠", "≤ 30 cmH₂O (adult)", MED_BLUE, LIGHT_BLUE),
    ("",          "Driving Pressure (Pplat−PEEP) ⚠", "≤ 15 cmH₂O", MED_BLUE, LIGHT_BLUE),
    ("",          "Auto-PEEP ⚠", "0 (detect if > 0)", MED_BLUE, LIGHT_BLUE),
    ("",          "Total PEEP (set + auto)", "Monitor", MED_BLUE, LIGHT_BLUE),
    ("",          "Mean Airway Pressure (cmH₂O)", "Monitor", MED_BLUE, LIGHT_BLUE),
    ("",          "Static Compliance (mL/cmH₂O)", "> 50 normal; ↓ = stiff lung", MED_BLUE, LIGHT_BLUE),
    ("",          "Dynamic Compliance (mL/cmH₂O)", "Monitor trend", MED_BLUE, LIGHT_BLUE),
    ("",          "Resistance (cmH₂O/L/s)", "< 5–8 normal; ↑ = obstruction", MED_BLUE, LIGHT_BLUE),
    ("",          "I:E Ratio", "1:2 typical", MED_BLUE, LIGHT_BLUE),
    # OXYGENATION
    ("OXYGENATION","SpO₂ (%)", "94–98% (92–96% ARDS)", "375623", LIGHT_GREEN),
    ("",           "EtCO₂ (mmHg)", "35–45 mmHg", "375623", LIGHT_GREEN),
    ("",           "FiO₂ Analyzed (%)", "Daily calibration", "375623", LIGHT_GREEN),
    # AIRWAY
    ("AIRWAY",    "Cuff Pressure (cmH₂O)", "20–30 cmH₂O", "7030A0", "E8D5FF"),
    ("",          "Tube Position at Lip/Stoma (cm)", "Confirm each check", "7030A0", "E8D5FF"),
    ("",          "Humidifier Temp at Wye (°C)", "31–35°C", "7030A0", "E8D5FF"),
    ("",          "Circuit Integrity (✓/✗)", "No leaks", "7030A0", "E8D5FF"),
    ("",          "Water Drained (✓/✗)", "Each check", "7030A0", "E8D5FF"),
    # PATIENT
    ("PATIENT",   "Breath Sounds (L/R)", "Equal bilateral", TEAL_HEADER, LIGHT_BLUE),
    ("",          "Chest Rise (equal/unequal)", "Equal", TEAL_HEADER, LIGHT_BLUE),
    ("",          "Patient-Vent Synchrony", "Synchronous/Dyssynchronous", TEAL_HEADER, LIGHT_BLUE),
    ("",          "Work of Breathing", "Normal/Increased/WOB↑", TEAL_HEADER, LIGHT_BLUE),
    ("",          "RASS / Sedation Score", "Target per order", TEAL_HEADER, LIGHT_BLUE),
    # RT
    ("RT",        "RT Initials", "", DARK_BLUE, LIGHT_GRAY),
    ("",          "Comments / Changes", "", DARK_BLUE, LIGHT_GRAY),
]

prev_cat = None
for param in params:
    cat, name, target, cat_bg, row_bg = param
    ws2.row_dimensions[r].height = 20

    # Category column
    if cat and cat != prev_cat:
        c = ws2.cell(row=r, column=1, value=cat)
        c.font = Font(name="Arial", size=9, bold=True, color=WHITE)
        c.fill = fill(cat_bg)
        c.alignment = Alignment(horizontal="center", vertical="center",
                                 wrap_text=True, text_rotation=90)
        c.border = thin_border()
        prev_cat = cat
    else:
        c = ws2.cell(row=r, column=1)
        c.fill = fill(cat_bg)
        c.border = thin_border()

    # Parameter name
    c2 = ws2.cell(row=r, column=2, value=name)
    c2.font = Font(name="Arial", size=9, bold=("⚠" in name), color="000000")
    c2.fill = fill(row_bg)
    c2.alignment = left(wrap=True)
    c2.border = thin_border()

    # Target
    c3 = ws2.cell(row=r, column=3, value=target)
    c3.font = Font(name="Arial", size=8, bold=False, color="000000")
    c3.fill = fill(row_bg)
    c3.alignment = left(wrap=True)
    c3.border = thin_border()

    # Input cells (13 time columns)
    for col in range(4, 17):
        ic = ws2.cell(row=r, column=col)
        ic.fill = fill(LIGHT_YELLOW)
        ic.font = Font(name="Arial", size=9, color="000080")
        ic.alignment = center()
        ic.border = thin_border()

    r += 1

# ── Conditional Formatting hints note ────────────────────────────────────────
r += 1
ws2.row_dimensions[r].height = 40
merge_write(ws2, f"A{r}:P{r}",
    "⚠ KEY TARGETS (AARC 2024):  VT = 4–8 mL/kg PBW  |  Pplat ≤ 30 cmH₂O  |  Driving Pressure ≤ 15 cmH₂O  |  "
    "Auto-PEEP = detect & report  |  SpO₂ 94–98% (92–96% ARDS)  |  Cuff pressure 20–30 cmH₂O",
    bg=ALERT_ORANGE, fg=WHITE, size=10, bold=True)

# ══════════════════════════════════════════════════════════════════════════════
# SHEET 3 – ABG & LABS
# ══════════════════════════════════════════════════════════════════════════════
ws3 = wb.create_sheet("ABG & Labs")
ws3.sheet_view.showGridLines = False
ws3.page_setup.orientation = "landscape"

ws3.row_dimensions[1].height = 35
merge_write(ws3, "A1:M1",
            "🩸  ARTERIAL BLOOD GAS & LABORATORY LOG",
            bg=DARK_BLUE, fg=WHITE, size=13)
merge_write(ws3, "A2:M2",
            "Patient: ______________________________    Date: ______________    MRN: ______________",
            bg=MED_BLUE, fg=WHITE, size=10, bold=False, align="left")

col_widths_3 = [22, 18, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9, 9]
for i, w in enumerate(col_widths_3, 1):
    set_col_width(ws3, i, w)

r = 4
# ABG header
abg_times = ["Sample 1","Sample 2","Sample 3","Sample 4","Sample 5",
             "Sample 6","Sample 7","Sample 8","Sample 9","Sample 10","Sample 11"]
ws3.row_dimensions[r].height = 35
for col, txt in enumerate(["ABG PARAMETER", "NORMAL RANGE"] + abg_times, 1):
    c = ws3.cell(row=r, column=col, value=txt)
    c.font = hdr_font(size=9)
    c.fill = fill(DARK_BLUE)
    c.alignment = center(wrap=True)
    c.border = thin_border()
r += 1

# Date/Time row
ws3.row_dimensions[r].height = 20
write_cell(ws3, r, 1, "Date / Time", bg=LIGHT_BLUE, bold=True)
write_cell(ws3, r, 2, "—", bg=LIGHT_BLUE)
for col in range(3, 14):
    input_cell(ws3, r, col)
r += 1

abg_params = [
    ("pH",              "7.35 – 7.45"),
    ("PaCO₂ (mmHg)",    "35 – 45"),
    ("PaO₂ (mmHg)",     "75 – 100"),
    ("HCO₃ (mEq/L)",    "22 – 26"),
    ("Base Excess (mEq/L)", "-2 to +2"),
    ("SaO₂ (%)",        "95 – 100"),
    ("SpO₂ (%)",        "94 – 98"),
    ("Lactate (mmol/L)","0.5 – 2.0"),
    ("FiO₂ at time of ABG (%)", "—"),
    ("P/F Ratio (PaO₂/FiO₂)","≥ 300 normal; <200 ARDS"),
    ("A-a Gradient",    "< 10–15 mmHg (room air)"),
    ("Interpretation",  "—"),
    ("Ventilator change made?", "Yes / No"),
    ("RT Initials",     "—"),
]

alt = True
for name, nrange in abg_params:
    ws3.row_dimensions[r].height = 20
    bg = LIGHT_GRAY if alt else WHITE
    write_cell(ws3, r, 1, name, bg=bg, bold=True)
    write_cell(ws3, r, 2, nrange, bg=bg)
    for col in range(3, 14):
        input_cell(ws3, r, col)
    alt = not alt
    r += 1

r += 2
# Labs section
merge_write(ws3, f"A{r}:M{r}", "RELEVANT LABORATORY VALUES", bg=MED_BLUE, size=11)
ws3.row_dimensions[r].height = 22
r += 1

# Lab header
ws3.row_dimensions[r].height = 30
lab_times = ["Day 1","Day 2","Day 3","Day 4","Day 5","Day 6",
             "Day 7","Day 8","Day 9","Day 10","Day 11"]
for col, txt in enumerate(["LAB PARAMETER", "NORMAL RANGE"] + lab_times, 1):
    c = ws3.cell(row=r, column=col, value=txt)
    c.font = hdr_font(size=9)
    c.fill = fill(MED_BLUE)
    c.alignment = center(wrap=True)
    c.border = thin_border()
r += 1

# Date row
write_cell(ws3, r, 1, "Date", bg=LIGHT_BLUE, bold=True)
write_cell(ws3, r, 2, "—", bg=LIGHT_BLUE)
for col in range(3, 14):
    input_cell(ws3, r, col)
r += 1

lab_params = [
    ("WBC (×10³/µL)",      "4.5 – 11.0"),
    ("Hemoglobin (g/dL)",  "12 – 17"),
    ("Hematocrit (%)",     "36 – 50"),
    ("Platelets (×10³/µL)","150 – 400"),
    ("Sodium (mEq/L)",     "136 – 145"),
    ("Potassium (mEq/L)",  "3.5 – 5.0"),
    ("Chloride (mEq/L)",   "98 – 107"),
    ("Bicarbonate (mEq/L)","22 – 29"),
    ("BUN (mg/dL)",        "7 – 20"),
    ("Creatinine (mg/dL)", "0.6 – 1.2"),
    ("Glucose (mg/dL)",    "70 – 100"),
    ("Albumin (g/dL)",     "3.5 – 5.0"),
    ("Procalcitonin (ng/mL)", "< 0.1"),
    ("CRP (mg/L)",         "< 10"),
]

alt = True
for name, nrange in lab_params:
    ws3.row_dimensions[r].height = 20
    bg = LIGHT_GRAY if alt else WHITE
    write_cell(ws3, r, 1, name, bg=bg, bold=True)
    write_cell(ws3, r, 2, nrange, bg=bg)
    for col in range(3, 14):
        input_cell(ws3, r, col)
    alt = not alt
    r += 1

# ══════════════════════════════════════════════════════════════════════════════
# SHEET 4 – SBT (Spontaneous Breathing Trial)
# ══════════════════════════════════════════════════════════════════════════════
ws4 = wb.create_sheet("SBT & Weaning")
ws4.sheet_view.showGridLines = False

ws4.row_dimensions[1].height = 35
merge_write(ws4, "A1:J1",
            "🌬  SPONTANEOUS BREATHING TRIAL (SBT) & WEANING LOG",
            bg=DARK_BLUE, fg=WHITE, size=13)
merge_write(ws4, "A2:J2",
            "Patient: ______________________________    MRN: ______________    ICU Day: ______",
            bg=MED_BLUE, fg=WHITE, size=10, bold=False, align="left")

sbt_col_widths = [28, 28, 12, 12, 12, 12, 12, 12, 12, 12]
for i, w in enumerate(sbt_col_widths, 1):
    set_col_width(ws4, i, w)

r = 4
merge_write(ws4, f"A{r}:J{r}", "DAILY SBT READINESS SCREEN (Complete before AM Rounds)", bg=TEAL_HEADER, size=11)
ws4.row_dimensions[r].height = 22
r += 1

readiness_criteria = [
    ("FiO₂ ≤ 0.50", "FiO₂ met (≤50%)?"),
    ("PEEP ≤ 5–8 cmH₂O", "PEEP met (≤8)?"),
    ("Hemodynamically stable", "No high-dose vasopressors?"),
    ("Minute Ventilation < 10 L/min", "MV < 10?"),
    ("No neuromuscular blockade", "NMB reversed?"),
    ("Patient triggering ventilator", "Triggering?"),
    ("No active seizures/agitation", "Calm/cooperative?"),
    ("Adequate mentation / follows commands", "GCS ≥ 8 or follows?"),
    ("Fluid/electrolyte/acid-base OK", "Labs acceptable?"),
]

# Header
ws4.row_dimensions[r].height = 30
for col, hdr in enumerate(["CRITERION", "CLINICAL CHECK"] +
                            [f"Day {d}" for d in range(1, 9)], 1):
    c = ws4.cell(row=r, column=col, value=hdr)
    c.font = hdr_font(size=9)
    c.fill = fill(MED_BLUE)
    c.alignment = center(wrap=True)
    c.border = thin_border()
r += 1

for crit, check in readiness_criteria:
    ws4.row_dimensions[r].height = 22
    write_cell(ws4, r, 1, crit, bg=LIGHT_BLUE, bold=True)
    write_cell(ws4, r, 2, check)

    # Add Yes/No dropdown for days
    dv = DataValidation(type="list", formula1='"Yes,No,N/A"', allow_blank=True)
    ws4.add_data_validation(dv)
    for col in range(3, 11):
        ic = input_cell(ws4, r, col)
        dv.add(ic)
    r += 1

r += 1
merge_write(ws4, f"A{r}:J{r}", "SBT PROCEDURE & RESULTS", bg=ORANGE_HDR, size=11)
ws4.row_dimensions[r].height = 22
r += 1

sbt_headers = ["SBT DATE", "TIME STARTED", "METHOD\n(CPAP/PSV/T-piece)",
               "FiO₂ DURING", "DURATION\n(min)", "RR DURING",
               "SpO₂ DURING", "RESULT\n(Pass/Fail)", "REASON IF FAIL",
               "DECISION\n(Extubate/Continue/Defer)"]

ws4.row_dimensions[r].height = 40
for col, hdr in enumerate(sbt_headers, 1):
    c = ws4.cell(row=r, column=col, value=hdr)
    c.font = hdr_font(size=9)
    c.fill = fill(ORANGE_HDR)
    c.alignment = center(wrap=True)
    c.border = thin_border()
r += 1

for _ in range(10):
    ws4.row_dimensions[r].height = 22
    for col in range(1, 11):
        input_cell(ws4, r, col)
    r += 1

r += 1
merge_write(ws4, f"A{r}:J{r}", "SBT FAILURE CRITERIA (any one = FAIL)", bg=ALERT_RED, fg=WHITE, size=10)
ws4.row_dimensions[r].height = 22
r += 1

fail_criteria = [
    "SpO₂ < 90% on FiO₂ ≥ 0.50",
    "RR > 35 breaths/min for > 5 min",
    "HR > 140 or change ± 20% from baseline",
    "SBP > 180 or < 90 mmHg",
    "Increased agitation, anxiety, or diaphoresis",
    "Accessory muscle use / paradoxical breathing",
    "Altered mental status / decreased consciousness",
    "Rapid shallow breathing index (RSBI = f/VT) > 105",
]

for i, crit in enumerate(fail_criteria):
    ws4.row_dimensions[r].height = 18
    c = write_cell(ws4, r, 1, f"✗  {crit}", bg=LIGHT_RED, bold=False)
    ws4.merge_cells(f"A{r}:J{r}")
    r += 1

r += 1
merge_write(ws4, f"A{r}:J{r}", "RSBI = f ÷ VT(L)  |  < 80 = likely success  |  80–105 = borderline  |  > 105 = likely failure",
            bg=LIGHT_YELLOW, fg="000000", size=10, bold=True)
ws4.row_dimensions[r].height = 22

# ══════════════════════════════════════════════════════════════════════════════
# SHEET 5 – VAP BUNDLE
# ══════════════════════════════════════════════════════════════════════════════
ws5 = wb.create_sheet("VAP Bundle")
ws5.sheet_view.showGridLines = False

ws5.row_dimensions[1].height = 35
merge_write(ws5, "A1:O1",
            "🛡  VENTILATOR-ASSOCIATED PNEUMONIA (VAP) PREVENTION BUNDLE",
            bg=DARK_BLUE, fg=WHITE, size=13)
merge_write(ws5, "A2:O2",
            "Patient: ______________________________    MRN: ______________    Unit: ______    Month: ______",
            bg=MED_BLUE, fg=WHITE, size=10, bold=False, align="left")

vap_col_widths = [30, 25] + [8]*13
for i, w in enumerate(vap_col_widths, 1):
    set_col_width(ws5, i, w)

r = 4
merge_write(ws5, f"A{r}:O{r}", "Bundle Compliance  |  ✓ = Done  |  ✗ = Not done  |  N/A = Not applicable", bg=TEAL_HEADER, size=10)
ws5.row_dimensions[r].height = 22
r += 1

# Header
day_headers = [f"Day {d}" for d in range(1, 14)]
ws5.row_dimensions[r].height = 30
for col, hdr in enumerate(["BUNDLE ELEMENT", "RATIONALE"] + day_headers, 1):
    c = ws5.cell(row=r, column=col, value=hdr)
    c.font = hdr_font(size=9)
    c.fill = fill(DARK_BLUE)
    c.alignment = center(wrap=True)
    c.border = thin_border()
r += 1

# Date row
write_cell(ws5, r, 1, "Date", bg=LIGHT_BLUE, bold=True)
write_cell(ws5, r, 2, "—", bg=LIGHT_BLUE)
for col in range(3, 16):
    input_cell(ws5, r, col)
r += 1

bundle_items = [
    ("Head-of-Bed 30–45°",
     "Reduces aspiration & VAP risk",
     LIGHT_GREEN),
    ("Daily spontaneous awakening trial (SAT)",
     "Reduces sedation, enables SBT",
     LIGHT_GREEN),
    ("Daily SBT screen performed",
     "Shortest ventilation duration",
     LIGHT_GREEN),
    ("Oral care with 0.12% chlorhexidine",
     "Reduces oropharyngeal colonization",
     LIGHT_GREEN),
    ("Subglottic secretion drainage",
     "Reduces microaspiration",
     LIGHT_GREEN),
    ("Cuff pressure checked & documented (20–30 cmH₂O)",
     "Prevents microaspiration",
     LIGHT_GREEN),
    ("Circuit NOT changed unless visibly soiled",
     "Frequent changes increase VAP risk",
     LIGHT_BLUE),
    ("Inline suction (closed system preferred)",
     "Maintains sterility",
     LIGHT_BLUE),
    ("HME or heated wire humidifier in use",
     "Maintains airway moisture",
     LIGHT_BLUE),
    ("Hand hygiene before airway contact",
     "Standard precaution",
     LIGHT_BLUE),
    ("DVT prophylaxis ordered",
     "ICU bundle element",
     "E8D5FF"),
    ("PUD prophylaxis ordered",
     "ICU bundle element",
     "E8D5FF"),
    ("Need for MV reassessed daily",
     "Drive toward extubation",
     "E8D5FF"),
    ("VAE surveillance criteria reviewed",
     "NHSN reporting (if applicable)",
     "E8D5FF"),
    ("RT Initials",
     "—",
     LIGHT_GRAY),
]

for item, rationale, row_bg in bundle_items:
    ws5.row_dimensions[r].height = 22
    write_cell(ws5, r, 1, item, bg=row_bg, bold=True)
    write_cell(ws5, r, 2, rationale, bg=row_bg)
    dv = DataValidation(type="list", formula1='"✓,✗,N/A"', allow_blank=True)
    ws5.add_data_validation(dv)
    for col in range(3, 16):
        ic = input_cell(ws5, r, col)
        dv.add(ic)
    r += 1

# ══════════════════════════════════════════════════════════════════════════════
# SHEET 6 – QUICK REFERENCE
# ══════════════════════════════════════════════════════════════════════════════
ws6 = wb.create_sheet("Quick Reference")
ws6.sheet_view.showGridLines = False

ws6.row_dimensions[1].height = 35
merge_write(ws6, "A1:J1",
            "📋  QUICK REFERENCE  |  Ventilator Formulas, Targets & Troubleshooting",
            bg=DARK_BLUE, fg=WHITE, size=13)

ref_col_widths = [3, 25, 35, 3, 25, 35, 3, 25, 35, 3]
for i, w in enumerate(ref_col_widths, 1):
    set_col_width(ws6, i, w)

r = 3
# ── PBW Calculator ────────────────────────────────────────────────────────────
merge_write(ws6, f"B{r}:C{r}", "🧮  PBW CALCULATOR", bg=TEAL_HEADER, size=11)
merge_write(ws6, f"E{r}:F{r}", "📏  KEY FORMULAS", bg=ORANGE_HDR, size=11)
merge_write(ws6, f"H{r}:I{r}", "🚨  DOPE TROUBLESHOOTING", bg=ALERT_RED, size=11)
ws6.row_dimensions[r].height = 22
r += 1

# PBW
write_cell(ws6, r, 2, "Sex", bg=LIGHT_BLUE, bold=True, h_align="center")
write_cell(ws6, r, 3, "Male", bg=LIGHT_BLUE, bold=True, h_align="center")
ws6.row_dimensions[r].height = 20; r += 1

write_cell(ws6, r, 2, "Height (cm)", bold=True)
input_cell(ws6, r, 3); ws6.row_dimensions[r].height = 20; r += 1

pbw_row = r
write_cell(ws6, r, 2, "PBW Male (kg)", bg=LIGHT_GREEN, bold=True)
c = ws6.cell(row=r, column=3,
             value=f"=ROUND(50+2.3*(({get_column_letter(3)}{pbw_row-1}/2.54)-60),1)")
c.font = Font(name="Arial", size=10, bold=True, color="000080")
c.fill = fill(LIGHT_GREEN)
c.alignment = center()
c.border = thin_border()
ws6.row_dimensions[r].height = 20; r += 1

write_cell(ws6, r, 2, "PBW Female (kg)", bg=LIGHT_GREEN, bold=True)
c = ws6.cell(row=r, column=3,
             value=f"=ROUND(45.5+2.3*(({get_column_letter(3)}{pbw_row-1}/2.54)-60),1)")
c.font = Font(name="Arial", size=10, bold=True, color="000080")
c.fill = fill(LIGHT_GREEN)
c.alignment = center()
c.border = thin_border()
ws6.row_dimensions[r].height = 20; r += 1

write_cell(ws6, r, 2, "VT 4 mL/kg PBW (M)", bg=LIGHT_YELLOW, bold=True)
c = ws6.cell(row=r, column=3,
             value=f"=ROUND(4*{get_column_letter(3)}{pbw_row},0)&\" mL\"")
c.font = Font(name="Arial", size=10, color="000080"); c.fill = fill(LIGHT_YELLOW)
c.alignment = center(); c.border = thin_border()
ws6.row_dimensions[r].height = 20; r += 1

write_cell(ws6, r, 2, "VT 6 mL/kg PBW (M)", bg=LIGHT_YELLOW, bold=True)
c = ws6.cell(row=r, column=3,
             value=f"=ROUND(6*{get_column_letter(3)}{pbw_row},0)&\" mL\"")
c.font = Font(name="Arial", size=10, color="000080"); c.fill = fill(LIGHT_YELLOW)
c.alignment = center(); c.border = thin_border()
ws6.row_dimensions[r].height = 20; r += 1

write_cell(ws6, r, 2, "VT 8 mL/kg PBW (M)", bg=LIGHT_YELLOW, bold=True)
c = ws6.cell(row=r, column=3,
             value=f"=ROUND(8*{get_column_letter(3)}{pbw_row},0)&\" mL\"")
c.font = Font(name="Arial", size=10, color="000080"); c.fill = fill(LIGHT_YELLOW)
c.alignment = center(); c.border = thin_border()
ws6.row_dimensions[r].height = 20; r += 1

r += 1
write_cell(ws6, r, 2, "Enter height in C5 above to auto-calculate targets",
           bg=LIGHT_YELLOW, bold=False); ws6.row_dimensions[r].height = 20; r += 1

# ── Formulas (middle column) ──────────────────────────────────────────────────
formulas = [
    ("Formula", "Expression", True),
    ("Driving Pressure", "Pplat − PEEP", False),
    ("Static Compliance", "VT ÷ (Pplat − PEEP)", False),
    ("Dynamic Compliance", "VT ÷ (PIP − PEEP)", False),
    ("Airway Resistance", "(PIP − Pplat) ÷ Flow (L/s)", False),
    ("P/F Ratio", "PaO₂ ÷ FiO₂", False),
    ("RSBI", "RR ÷ VT(L)   [< 80 = wean likely]", False),
    ("Mean Airway Pressure", "(PIP×Ti + PEEP×Te) ÷ (Ti+Te)", False),
    ("A-a Gradient", "PAO₂ − PaO₂\nPAO₂ = (FiO₂×713) − (PaCO₂÷0.8)", False),
    ("I-Time", "(I-time × LPM × 1000) ÷ 60", False),
]

fr = 4
for label, expr, is_header in formulas:
    ws6.row_dimensions[fr].height = 22 if "\n" not in expr else 35
    bg_e = MED_BLUE if is_header else LIGHT_GRAY
    fg_e = WHITE if is_header else "000000"
    write_cell(ws6, fr, 5, label, bg=bg_e, fg=fg_e, bold=is_header, h_align="center")
    write_cell(ws6, fr, 6, expr, bg=bg_e if is_header else WHITE,
               fg=fg_e, bold=False, wrap=True, h_align="left")
    fr += 1

# ── DOPE Troubleshooting ──────────────────────────────────────────────────────
dope_items = [
    ("D – DISPLACEMENT",
     "Tube position OK? Bilateral BS?\nPost-CXR? ETT at lip mark?",
     LIGHT_RED),
    ("O – OBSTRUCTION",
     "Secretions? Biting? Kinked circuit?\nInline suction open?",
     "FFE9CE"),
    ("P – PNEUMOTHORAX",
     "Unequal rise? Absent BS?\nTracheal deviation? Urgent CXR/US",
     LIGHT_YELLOW),
    ("E – EQUIPMENT",
     "Circuit leak? Vent malfunction?\nO₂ failure? Power issue?",
     LIGHT_GREEN),
    ("",
     "⚡ IF UNSURE:\nRemove tube → BVM 100% O₂",
     LIGHT_RED),
]

dr = 4
for title, detail, bg in dope_items:
    ws6.row_dimensions[dr].height = 40
    write_cell(ws6, dr, 8, title, bg=bg, bold=True, h_align="center", wrap=True)
    write_cell(ws6, dr, 9, detail, bg=bg, bold=False, wrap=True)
    dr += 1

# ── P/F Ratio Severity ────────────────────────────────────────────────────────
dr += 1
merge_write(ws6, f"H{dr}:I{dr}", "P/F RATIO — ARDS SEVERITY", bg=MED_BLUE, size=10)
ws6.row_dimensions[dr].height = 22; dr += 1

pf_table = [
    ("≥ 300", "Normal", WHITE),
    ("200–299", "Mild ARDS (Berlin)", LIGHT_YELLOW),
    ("100–199", "Moderate ARDS", "FFD580"),
    ("< 100",  "Severe ARDS", LIGHT_RED),
]
for ratio, severity, bg in pf_table:
    ws6.row_dimensions[dr].height = 20
    write_cell(ws6, dr, 8, ratio, bg=bg, bold=True, h_align="center")
    write_cell(ws6, dr, 9, severity, bg=bg)
    dr += 1

# ── ARDS Network PEEP / FiO₂ Table ───────────────────────────────────────────
r = max(r, dr) + 2

merge_write(ws6, f"B{r}:I{r}",
            "ARDS NETWORK PEEP / FiO₂ TABLE (Lower PEEP Strategy)",
            bg=TEAL_HEADER, size=11)
ws6.row_dimensions[r].height = 22; r += 1

peep_fio2 = [
    ("FiO₂", 0.30, 0.40, 0.40, 0.50, 0.50, 0.60, 0.70, 0.70, 0.70, 0.80, 0.90, 0.90, 1.00),
    ("PEEP", 5,    5,    8,    8,    10,   10,   10,   12,   14,   14,   14,   16,   18),
]

ws6.row_dimensions[r].height = 20
for col_idx, val in enumerate(peep_fio2[0], 2):
    bg = MED_BLUE if col_idx == 2 else LIGHT_BLUE
    fg = WHITE if col_idx == 2 else "000000"
    c = ws6.cell(row=r, column=col_idx, value=val)
    c.font = Font(name="Arial", size=9, bold=True, color=fg)
    c.fill = fill(bg)
    c.alignment = center()
    c.border = thin_border()
r += 1

ws6.row_dimensions[r].height = 20
for col_idx, val in enumerate(peep_fio2[1], 2):
    bg = MED_BLUE if col_idx == 2 else WHITE
    fg = WHITE if col_idx == 2 else "000080"
    c = ws6.cell(row=r, column=col_idx, value=val)
    c.font = Font(name="Arial", size=9, bold=(col_idx > 2), color=fg)
    c.fill = fill(bg)
    c.alignment = center()
    c.border = thin_border()
r += 2

# Footer note
merge_write(ws6, f"B{r}:I{r}",
    "Source: AARC CPG Patient-Ventilator Assessment 2024 | ARDSNet Protocol | NHSN VAE 2024  "
    "| Template created for Respiratory Therapy Dept use",
    bg=LIGHT_GRAY, fg="555555", size=8, bold=False)

# ══════════════════════════════════════════════════════════════════════════════
# SHEET 7 – AIRWAY MANAGEMENT LOG
# ══════════════════════════════════════════════════════════════════════════════
ws7 = wb.create_sheet("Airway Log")
ws7.sheet_view.showGridLines = False

ws7.row_dimensions[1].height = 35
merge_write(ws7, "A1:K1",
            "🫁  AIRWAY MANAGEMENT LOG  |  Suction / Oral Care / Tube Changes",
            bg=DARK_BLUE, fg=WHITE, size=13)
merge_write(ws7, "A2:K2",
            "Patient: ______________________________    MRN: ______________    Date: ______________",
            bg=MED_BLUE, fg=WHITE, size=10, bold=False, align="left")

airway_col_widths = [10, 12, 14, 14, 12, 14, 16, 14, 14, 14, 12]
for i, w in enumerate(airway_col_widths, 1):
    set_col_width(ws7, i, w)

r = 4
airway_headers = [
    "Date", "Time", "Suction\n(Inline/Open)",
    "Secretion\nAmount\n(Scant/Mod/Cop)",
    "Color &\nConsistency",
    "Oral Care\n(✓/✗)",
    "HOB\nPosition",
    "Cuff P\n(cmH₂O)",
    "Tube Position\n(cm at lip)",
    "Performed by\n(RT Initials)",
    "Notes"
]
ws7.row_dimensions[r].height = 45
for col, hdr in enumerate(airway_headers, 1):
    c = ws7.cell(row=r, column=col, value=hdr)
    c.font = hdr_font(size=9)
    c.fill = fill(DARK_BLUE)
    c.alignment = center(wrap=True)
    c.border = thin_border()
r += 1

alt = True
for _ in range(30):
    ws7.row_dimensions[r].height = 20
    for col in range(1, 12):
        bg = LIGHT_YELLOW if alt else WHITE
        input_cell(ws7, r, col, bg=bg)
    alt = not alt
    r += 1

# Final save
output_path = "/tmp/workspace/ventilator-sheet/Ventilator_Flow_Sheet_RT.xlsx"
wb.save(output_path)
print(f"SUCCESS: Saved to {output_path}")