#!/usr/bin/env python3
"""Generate Document #8: Alarm- & Meldingsformulierenpakket (DOCX + XLSX)"""

from docx import Document
from docx.shared import Cm, Inches, Pt, RGBColor, Emu
from docx.enum.text import WD_ALIGN_PARAGRAPH
from docx.enum.table import WD_TABLE_ALIGNMENT
from docx.enum.section import WD_ORIENT
import openpyxl
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.formatting.rule import CellIsRule
from openpyxl.utils import get_column_letter
from datetime import date

# --- Constants ---
PRIMARY = RGBColor(0x00, 0x33, 0x66)
TEXT_COLOR = RGBColor(0x1F, 0x29, 0x37)
LOGO_PATH = "/root/projects/jg/assets/branding/jvg-logo-white-medium.png"
FOOTER_TEXT = "JvG Consultancy | Safety • Governance • Advisory | © 2026"
DOC_HEADER = {
    "Project": "2026-PM-VBS-Element4",
    "Type": "Formulierenpakket",
    "Auteur": "EA_05",
    "Versie": "1.0",
    "Datum": "2026-04-29",
    "Status": "Final",
}

DOCX_OUT = "/root/projects/jg/2026-PM-VBS-Element4/deliverables/docx/PM_VBS04_08_Formulierenpakket_v1.0.docx"
XLSX_OUT = "/root/projects/jg/2026-PM-VBS-Element4/deliverables/xlsx/PM_VBS04_08_Formulierenpakket_v1.0.xlsx"

# Zones from meta-dossier
ZONES = ["Zone A — Ontvangst & Opslag", "Zone B — Uitloging & Filtratie", "Zone C — Zuivering",
         "Zone D — Droog & Calcinatie", "Zone E — Verpakking & Logistiek", "Zone F — Kantoor & Support"]

def set_cell_font(cell, size=11, bold=False, color=None):
    for p in cell.paragraphs:
        for r in p.runs:
            r.font.size = Pt(size)
            r.font.bold = bold
            r.font.name = "Calibri"
            if color:
                r.font.color.rgb = color

def add_field_table(doc, fields, col_widths=None):
    """Add a 2-column form table. fields = list of (label, placeholder_text)"""
    table = doc.add_table(rows=len(fields), cols=2, style='Table Grid')
    table.alignment = WD_TABLE_ALIGNMENT.CENTER
    for i, (label, placeholder) in enumerate(fields):
        c0 = table.cell(i, 0)
        c1 = table.cell(i, 1)
        c0.text = label
        c1.text = placeholder
        # Style
        for p in c0.paragraphs:
            p.runs[0].font.bold = True if p.runs else None
            p.runs[0].font.size = Pt(11)
            p.runs[0].font.name = "Calibri"
            p.runs[0].font.color.rgb = PRIMARY if p.runs else None
        set_cell_font(c1, 11, False, TEXT_COLOR)
        c0.width = Cm(6)
        c1.width = Cm(11)
    return table

def add_logo(doc):
    try:
        p = doc.add_paragraph()
        p.alignment = WD_ALIGN_PARAGRAPH.LEFT
        run = p.add_run()
        run.add_picture(LOGO_PATH, width=Cm(2.0), height=Cm(1.8))
    except:
        pass

def add_doc_header_table(doc):
    table = doc.add_table(rows=3, cols=4, style='Table Grid')
    table.alignment = WD_TABLE_ALIGNMENT.CENTER
    items = [
        ("Project", DOC_HEADER["Project"], "Type", DOC_HEADER["Type"]),
        ("Auteur", DOC_HEADER["Auteur"], "Versie", DOC_HEADER["Versie"]),
        ("Datum", DOC_HEADER["Datum"], "Status", DOC_HEADER["Status"]),
    ]
    for r, (l1, v1, l2, v2) in enumerate(items):
        for c, (lbl, val) in enumerate([(l1, v1), (l2, v2)]):
            cell = table.cell(r, c*2)
            cell.text = lbl
            cell2 = table.cell(r, c*2+1)
            cell2.text = val
            for p in cell.paragraphs:
                for run in p.runs:
                    run.font.bold = True
                    run.font.size = Pt(10)
                    run.font.name = "Calibri"
                    run.font.color.rgb = PRIMARY
            for p in cell2.paragraphs:
                for run in p.runs:
                    run.font.size = Pt(10)
                    run.font.name = "Calibri"

def add_section_title(doc, title):
    h = doc.add_heading(title, level=2)
    for run in h.runs:
        run.font.color.rgb = PRIMARY
        run.font.name = "Calibri"
        run.font.size = Pt(14)

def add_footer_text(doc):
    p = doc.add_paragraph()
    p.alignment = WD_ALIGN_PARAGRAPH.CENTER
    run = p.add_run(FOOTER_TEXT)
    run.font.size = Pt(8)
    run.font.color.rgb = RGBColor(0x99, 0x99, 0x99)
    run.font.name = "Calibri"

# ============================================================
# DOCX GENERATION
# ============================================================
doc = Document()

# Default font
style = doc.styles['Normal']
style.font.name = 'Calibri'
style.font.size = Pt(11)
style.font.color.rgb = TEXT_COLOR

# Page margins
for section in doc.sections:
    section.top_margin = Cm(2)
    section.bottom_margin = Cm(2)
    section.left_margin = Cm(2.5)
    section.right_margin = Cm(2.5)

# Title page area
add_logo(doc)
title = doc.add_heading("Alarm- & Meldingsformulierenpakket", level=0)
for run in title.runs:
    run.font.color.rgb = PRIMARY
    run.font.name = "Calibri"
sub = doc.add_paragraph("VBS Element 4 — Planning voor Noodsituaties\nPhoenix Metals B.V. — IJmuiden")
sub.alignment = WD_ALIGN_PARAGRAPH.CENTER
for run in sub.runs:
    run.font.color.rgb = TEXT_COLOR
    run.font.name = "Calibri"
    run.font.size = Pt(12)

add_doc_header_table(doc)
doc.add_paragraph()  # spacer

# ---- Formulier 1: Alarmeringsformulier ----
add_section_title(doc, "Formulier 1: Alarmeringsformulier")
add_field_table(doc, [
    ("Incident Type", "☐ Brand  ☐ Lekkage  ☐ Ongeval  ☐ Milieu  ☐ Anders: ___"),
    ("Datum/Tijd Ontdekking", "__ / __ / 20__  __ : __"),
    ("Locatie", "Zone: ☐ A  ☐ B  ☐ C  ☐ D  ☐ E  ☐ F\nBeschrijving: _________________________"),
    ("Ontdekker (Naam, Functie)", "___________________________________"),
    ("Beschrijving Incident", "Wat gebeurt er: ____________________\nHoeveelheid: _____ Betrokken stoffen: ___________"),
    ("Alarmniveau Bepaald", "☐ Geel (Waarschuwing)  ☐ Oranje (Verhoogd)\n☐ Rood (Alarm)  ☐ Zwart (Crisis)"),
    ("Hulpdiensten Ingeschakeld", "☐ Ja  ☐ Nee\nWelke: __________ Tijd: __ : __"),
    ("Eerste Maatregelen Genomen", "___________________________________"),
    ("Geneeskundige Dienst Geïnformeerd", "☐ Ja  ☐ Nee"),
    ("Handtekening Melder", "___________________________"),
    ("Datum/Tijd Melding", "__ / __ / 20__  __ : __"),
])
add_footer_text(doc)
doc.add_page_break()

# ---- Formulier 2: Personen Telling ----
add_section_title(doc, "Formulier 2: Personen Telling Formulier")
add_field_table(doc, [
    ("Incident Referentie (Datum, Locatie)", "__ / __ / 20__  Locatie: _____________"),
    ("Tellingstype", "☐ Totaal  ☐ Per Zone"),
    ("Aanwezigen Totaal (Presentielijst)", "________ personen"),
    ("Aanwezigen Geteld op Verzamelpunt", "________ personen"),
    ("Vermiste Personen", "Naam: ______________ Laatst gezien: ________\nNaam: ______________ Laatst gezien: ________"),
    ("Bezoekers/Aannemers Geteld", "☐ Ja  ☐ Nee"),
    ("Telling Volledig", "☐ Ja  ☐ Nee"),
    ("Uitgevoerd door (Naam, Functie)", "___________________________________"),
    ("Tijd Telling", "__ : __"),
    ("Doorgegeven aan (Naam, Tijd)", "Naam: ____________ Tijd: __ : __"),
])
add_footer_text(doc)
doc.add_page_break()

# ---- Formulier 3: Crisiscommunicatie Log ----
add_section_title(doc, "Formulier 3: Crisiscommunicatie Log")
add_field_table(doc, [
    ("Incident Referentie", "___________________________________"),
    ("Datum/Tijd", "__ / __ / 20__  __ : __"),
    ("Communicatie Type", "☐ Intern  ☐ Extern"),
    ("Stakeholder", "☐ Brandweer  ☐ Politie  ☐ Ambulance\n☐ Gemeente  ☐ Provincie  ☐ RIVM  ☐ Anders: ___"),
    ("Bericht Inhoud", "___________________________________"),
    ("Door Wie Gecommuniceerd", "___________________________________"),
    ("Via Welk Kanaal", "☐ Telefoon  ☐ E-mail  ☐ Radio  ☐ PA-systeem"),
    ("Reactie Ontvangen", "☐ Ja  ☐ Nee\nSamenvatting: _______________________"),
    ("Opvolging Vereist", "☐ Ja  ☐ Nee\nWat: _______________________________"),
])
p = doc.add_paragraph()
p.alignment = WD_ALIGN_PARAGRAPH.LEFT
run = p.add_run("Herhaal bovenstaande regels voor elke communicatiemoment.")
run.font.italic = True
run.font.size = Pt(9)
run.font.color.rgb = RGBColor(0x99, 0x99, 0x99)
add_footer_text(doc)
doc.add_page_break()

# ---- Formulier 4: Incident Rapport ----
add_section_title(doc, "Formulier 4: Incident Rapport Formulier")
add_field_table(doc, [
    ("Incident Nummer", "PM-INC-________-______"),
    ("Datum, Locatie", "__ / __ / 20__  Locatie: _____________"),
    ("Type Incident", "☐ Brand  ☐ Lekkage  ☐ Ongeval  ☐ Milieu  ☐ Near-miss"),
    ("Ernst", "☐ Geel  ☐ Oranje  ☐ Rood  ☐ Zwart"),
    ("Betrokken Personen", "Naam: ________ Functie: ______ Letsel: ☐ Ja ☐ Nee\nNaam: ________ Functie: ______ Letsel: ☐ Ja ☐ Nee"),
    ("Beschrijving Gebeurtenis (Timeline)", "Tijd: __ : __  ___________________________________\nTijd: __ : __  ___________________________________"),
    ("Oorzaak (Initieel Idee)", "___________________________________"),
    ("Maatregelen Genomen (Direct)", "___________________________________"),
    ("Schade", "Materieel: __________ Milieu: __________ Personen: ________"),
    ("Foto/Video Referentie", "___________________________________"),
    ("Onderzoek Vereist", "☐ Ja  Type: __________ ☐ Nee"),
    ("Rapporteur (Naam, Functie)", "___________________________________"),
    ("Handtekening + Datum", "___________  __ / __ / 20__"),
])
add_footer_text(doc)
doc.add_page_break()

# ---- Formulier 5: Oefen Evaluatie ----
add_section_title(doc, "Formulier 5: Oefen Evaluatie Formulier")

# Part A: General info
p = doc.add_paragraph()
run = p.add_run("A. Algemene Gegevens")
run.font.bold = True
run.font.color.rgb = PRIMARY
add_field_table(doc, [
    ("Oefening Type", "☐ Tabletop  ☐ Drill  ☐ Full-scale"),
    ("Datum, Locatie", "__ / __ / 20__  Locatie: _____________"),
    ("Scenario", "☐ A (Brand/Explosie)  ☐ B (Toxic Release)\n☐ C (Milieu)  ☐ D (Persoonlijk Ongeval)  ☐ E (Externe Dreiging)"),
    ("Deelnemers", "___________________________________"),
])

# Part B: Scores
p = doc.add_paragraph()
run = p.add_run("B. Evaluatiecriteria (1 = Onvoldoende, 5 = Uitstekend)")
run.font.bold = True
run.font.color.rgb = PRIMARY

criteria = ["Responstijd", "Communicatie (intern)", "Evacuatie", "BHV-inzet", "Externe communicatie"]
score_table = doc.add_table(rows=len(criteria)+2, cols=3, style='Table Grid')
score_table.alignment = WD_TABLE_ALIGNMENT.CENTER
# Header
for j, hdr in enumerate(["Criterium", "Score (1-5)", "Opmerking"]):
    cell = score_table.cell(0, j)
    cell.text = hdr
    for run in cell.paragraphs[0].runs:
        run.font.bold = True
        run.font.color.rgb = RGBColor(0xFF, 0xFF, 0xFF)
        run.font.size = Pt(10)
        run.font.name = "Calibri"
    from docx.oxml.ns import qn
    shading = cell._element.get_or_add_tcPr()
    shading_elm = shading.makeelement(qn('w:shd'), {
        qn('w:fill'): '003366',
        qn('w:val'): 'clear'
    })
    shading.append(shading_elm)

for i, crit in enumerate(criteria):
    score_table.cell(i+1, 0).text = crit
    score_table.cell(i+1, 1).text = "___"
    score_table.cell(i+1, 2).text = ""
    for c in range(3):
        for run in score_table.cell(i+1, c).paragraphs[0].runs:
            run.font.size = Pt(10)
            run.font.name = "Calibri"

# Total row
score_table.cell(len(criteria)+1, 0).text = "TOTAAL"
score_table.cell(len(criteria)+1, 1).text = "___ / 25"
for run in score_table.cell(len(criteria)+1, 0).paragraphs[0].runs:
    run.font.bold = True
    run.font.name = "Calibri"
for run in score_table.cell(len(criteria)+1, 1).paragraphs[0].runs:
    run.font.bold = True
    run.font.name = "Calibri"

# Part C: Qualitative
p = doc.add_paragraph()
run = p.add_run("C. Kwalitatieve Evaluatie")
run.font.bold = True
run.font.color.rgb = PRIMARY
add_field_table(doc, [
    ("Sterke Punten", "___________________________________"),
    ("Verbeterpunten", "___________________________________"),
    ("Acties (Wat, Door Wie, Wanneer)", "Wat: ______________ Door wie: _______ Wanneer: ________\nWat: ______________ Door wie: _______ Wanneer: ________"),
    ("Geëvalueerd door (Naam, Functie)", "___________________________________"),
    ("Datum Evaluatie", "__ / __ / 20__"),
])
add_footer_text(doc)

# ---- TierVerify & Bronverwijzingen ----
doc.add_page_break()
add_section_title(doc, "Bronverwijzingen")
refs = [
    "[1] BRZO 2015 (Besluit risico's zware ongevallen 2015), Art. 11 — Noodplannen",
    "[2] Seveso III Richtlijn 2012/18/EU, Artikel 12 — Emergency planning",
    "[3] NTA 8620:2017 — Veiligheidsrapportage BRZO-inrichtingen",
    "[4] Arbowet (Wet van 10 maart 1998) — Arbeidsomstandigheden",
    "[5] Phoenix Metals Meta-Dossier VBS Element 4 v1.0 (29 april 2026)",
]
for ref in refs:
    p = doc.add_paragraph(ref)
    for run in p.runs:
        run.font.size = Pt(10)
        run.font.name = "Calibri"

add_section_title(doc, "TierVerify Log")
verify_items = [
    ("Documentheader (§2.1)", "✅ Aanwezig — Project, Type, Auteur, Versie, Datum, Status"),
    ("Bronverwijzingen (§1.8)", "✅ BRZO 2015, Seveso III, NTA 8620, Arbowet, Meta-Dossier"),
    ("Logo (§8)", "✅ JvG logo 2.0×1.8 cm, wit op donker"),
    ("Footer", "✅ JvG Consultancy | Safety • Governance • Advisory | © 2026"),
    ("Kleurenpalet", "✅ Primair #003366, Tekst #1F2937"),
    ("Lettertype", "✅ Calibri 11pt body, 14pt bold koppen"),
    ("Alarmniveaus", "✅ Geel/Oranje/Rood/Zwart conform Meta-Dossier §4"),
    ("Scenario's A-E", "✅ Conform Meta-Dossier §3"),
    ("Zones A-F", "✅ Conform Meta-Dossier processtappen"),
]
vt = doc.add_table(rows=len(verify_items)+1, cols=2, style='Table Grid')
vt.cell(0, 0).text = "Controle"
vt.cell(0, 1).text = "Status"
for run in vt.cell(0, 0).paragraphs[0].runs:
    run.font.bold = True
    run.font.color.rgb = RGBColor(0xFF, 0xFF, 0xFF)
    run.font.name = "Calibri"
for run in vt.cell(0, 1).paragraphs[0].runs:
    run.font.bold = True
    run.font.color.rgb = RGBColor(0xFF, 0xFF, 0xFF)
    run.font.name = "Calibri"
for c in range(2):
    shading = vt.cell(0, c)._element.get_or_add_tcPr()
    shading_elm = shading.makeelement(qn('w:shd'), {qn('w:fill'): '003366', qn('w:val'): 'clear'})
    shading.append(shading_elm)

for i, (check, status) in enumerate(verify_items):
    vt.cell(i+1, 0).text = check
    vt.cell(i+1, 1).text = status
    for c in range(2):
        for run in vt.cell(i+1, c).paragraphs[0].runs:
            run.font.size = Pt(10)
            run.font.name = "Calibri"

doc.save(DOCX_OUT)
print(f"DOCX saved: {DOCX_OUT}")

# ============================================================
# XLSX GENERATION
# ============================================================
wb = openpyxl.Workbook()

HEADER_FILL = PatternFill(start_color="003366", end_color="003366", fill_type="solid")
HEADER_FONT = Font(name="Calibri", bold=True, color="FFFFFF", size=11)
BODY_FONT = Font(name="Calibri", size=11, color="1F2937")
THIN_BORDER = Border(
    left=Side(style='thin'), right=Side(style='thin'),
    top=Side(style='thin'), bottom=Side(style='thin')
)
WRAP = Alignment(wrap_text=True, vertical='top')

def style_header(ws, row, cols):
    for c in range(1, cols+1):
        cell = ws.cell(row=row, column=c)
        cell.fill = HEADER_FILL
        cell.font = HEADER_FONT
        cell.border = THIN_BORDER
        cell.alignment = Alignment(horizontal='center', vertical='center')

def style_body(ws, start_row, end_row, cols):
    for r in range(start_row, end_row+1):
        for c in range(1, cols+1):
            cell = ws.cell(row=r, column=c)
            cell.font = BODY_FONT
            cell.border = THIN_BORDER
            cell.alignment = WRAP

def set_col_widths(ws, widths):
    for i, w in enumerate(widths, 1):
        ws.column_dimensions[get_column_letter(i)].width = w

# --- Tab 1: Alarmering ---
ws1 = wb.active
ws1.title = "Alarmering"
headers1 = ["Incident Nr", "Datum", "Tijd", "Type (Brand/Lekkage/Ongeval/Milieu/Anders)",
            "Zone (A-F)", "Locatie Beschrijving", "Ontdekker", "Functie",
            "Beschrijving Incident", "Betrokken Stoffen", "Hoeveelheid",
            "Alarmniveau (Geel/Oranje/Rood/Zwart)", "Hulpdiensten (J/N)", "Welke Hulpdiensten",
            "Tijd Hulpdiensten", "Eerste Maatregelen", "Geneeskundige Dienst (J/N)",
            "Melder", "Handtekening", "Datum/Tijd Melding"]
for c, h in enumerate(headers1, 1):
    ws1.cell(row=1, column=c, value=h)
style_header(ws1, 1, len(headers1))
for r in range(2, 52):
    for c in range(1, len(headers1)+1):
        ws1.cell(row=r, column=c, value="")
style_body(ws1, 2, 51, len(headers1))
set_col_widths(ws1, [14, 12, 8, 18, 10, 22, 18, 16, 30, 18, 12, 18, 12, 22, 12, 30, 12, 18, 16, 18])

# --- Tab 2: Telling ---
ws2 = wb.create_sheet("Telling")
headers2 = ["Incident Ref", "Datum Incident", "Locatie", "Tellingstype (Totaal/Per Zone)",
            "Zone (indien per zone)", "Aanwezigen volgens Presentielijst",
            "Geteld op Verzamelpunt", "Verschil", "Vermiste Personen (Namen)",
            "Laatst Gezien (Locatie)", "Bezoekers/Aannemers Geteld (J/N)",
            "Telling Volledig (J/N)", "Uitgevoerd door", "Functie",
            "Tijd Telling", "Doorgegeven aan", "Tijd Doorgegeven"]
for c, h in enumerate(headers2, 1):
    ws2.cell(row=1, column=c, value=h)
style_header(ws2, 1, len(headers2))
style_body(ws2, 2, 51, len(headers2))
set_col_widths(ws2, [14, 14, 18, 18, 16, 18, 16, 10, 24, 20, 14, 12, 18, 16, 10, 18, 12])

# --- Tab 3: Crisislog ---
ws3 = wb.create_sheet("Crisislog")
headers3 = ["Incident Ref", "Datum", "Tijd", "Type (Intern/Extern)",
            "Stakeholder", "Bericht Inhoud", "Door Wie",
            "Kanaal (Tel/E-mail/Radio/PA)", "Reactie Ontvangen (J/N)",
            "Reactie Samenvatting", "Opvolging Vereist (J/N)", "Opvolging Wat"]
for c, h in enumerate(headers3, 1):
    ws3.cell(row=1, column=c, value=h)
style_header(ws3, 1, len(headers3))
style_body(ws3, 2, 51, len(headers3))
set_col_widths(ws3, [14, 12, 8, 14, 18, 30, 18, 18, 12, 30, 12, 30])

# --- Tab 4: Incident Rapport ---
ws4 = wb.create_sheet("Incident Rapport")
headers4 = ["Incident Nr", "Datum", "Locatie", "Type (Brand/Lekkage/Ongeval/Milieu/Near-miss)",
            "Ernst (Geel/Oranje/Rood/Zwart)", "Betrokken Persoon 1", "Functie 1",
            "Letsel 1 (J/N)", "Betrokken Persoon 2", "Functie 2", "Letsel 2 (J/N)",
            "Beschrijving Gebeurtenis (Timeline)", "Oorzaak (Initieel)",
            "Maatregelen Genomen", "Schade Materieel", "Schade Milieu", "Schade Personen",
            "Foto/Video Ref", "Onderzoek Vereist (J/N)", "Type Onderzoek",
            "Rapporteur", "Functie Rapporteur", "Datum Rapport"]
for c, h in enumerate(headers4, 1):
    ws4.cell(row=1, column=c, value=h)
style_header(ws4, 1, len(headers4))
style_body(ws4, 2, 51, len(headers4))
set_col_widths(ws4, [16, 12, 18, 18, 18, 18, 14, 10, 18, 14, 10, 35, 25, 30, 18, 18, 18, 18, 12, 18, 18, 16, 14])

# --- Tab 5: Oefen Evaluatie ---
ws5 = wb.create_sheet("Oefen Evaluatie")
headers5 = ["Oefening Nr", "Type (Tabletop/Drill/Full-scale)", "Datum", "Locatie",
            "Scenario (A-E)", "Deelnemers",
            "Score Responstijd (1-5)", "Score Communicatie (1-5)", "Score Evacuatie (1-5)",
            "Score BHV-inzet (1-5)", "Score Externe Comm (1-5)", "Totaalscore (/25)",
            "Sterke Punten", "Verbeterpunten", "Acties", "Geëvalueerd door", "Functie", "Datum Evaluatie"]
for c, h in enumerate(headers5, 1):
    ws5.cell(row=1, column=c, value=h)
style_header(ws5, 1, len(headers5))
style_body(ws5, 2, 51, len(headers5))
set_col_widths(ws5, [12, 20, 12, 18, 12, 30, 12, 12, 12, 12, 12, 12, 30, 30, 30, 18, 16, 14])

# Conditional formatting for scores (columns G-K, rows 2-51) — Red/Yellow/Green
RED_FILL = PatternFill(start_color="FF4444", end_color="FF4444", fill_type="solid")
YELLOW_FILL = PatternFill(start_color="FFD700", end_color="FFD700", fill_type="solid")
GREEN_FILL = PatternFill(start_color="00C851", end_color="00C851", fill_type="solid")

for col_letter in ['G', 'H', 'I', 'J', 'K']:
    rng = f"{col_letter}2:{col_letter}51"
    ws5.conditional_formatting.add(rng, CellIsRule(operator='lessThanOrEqual', formula=['2'], fill=RED_FILL))
    ws5.conditional_formatting.add(rng, CellIsRule(operator='equal', formula=['3'], fill=YELLOW_FILL))
    ws5.conditional_formatting.add(rng, CellIsRule(operator='greaterThanOrEqual', formula=['4'], fill=GREEN_FILL))

# Totaalscore column L — conditional
rng = "L2:L51"
ws5.conditional_formatting.add(rng, CellIsRule(operator='lessThanOrEqual', formula=['10'], fill=RED_FILL))
ws5.conditional_formatting.add(rng, CellIsRule(operator='between', formula=['11', '17'], fill=YELLOW_FILL))
ws5.conditional_formatting.add(rng, CellIsRule(operator='greaterThanOrEqual', formula=['18'], fill=GREEN_FILL))

# Print setup for all sheets
for ws in [ws1, ws2, ws3, ws4, ws5]:
    ws.sheet_properties.pageSetUpPr = openpyxl.worksheet.properties.PageSetupProperties(fitToPage=True)
    ws.page_setup.fitToWidth = 1
    ws.page_setup.fitToHeight = 0
    ws.page_setup.orientation = 'landscape'
    ws.print_title_rows = '1:1'

wb.save(XLSX_OUT)
print(f"XLSX saved: {XLSX_OUT}")
print("DONE — Document #8 generated successfully.")