#!/usr/bin/env python3
"""Generate Document #7: Werkvergunning Formulierenpakket (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
from docx.oxml.ns import qn, nsdecls
from docx.oxml import parse_xml
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 copy import copy
import os

# Paths
LOGO = "/root/projects/jg/assets/branding/jvg-logo-white-medium.png"
DOCX_OUT = "/root/projects/jg/2026-PM-VBS-Element3/deliverables/docx/PM_VBS03_07_Formulierenpakket_v1.0.docx"
XLSX_OUT = "/root/projects/jg/2026-PM-VBS-Element3/deliverables/xlsx/PM_VBS03_07_Formulierenpakket_v1.0.xlsx"

PRIMARY = RGBColor(0x00, 0x33, 0x66)
DARK_TEXT = RGBColor(0x1F, 0x2F, 0x37)
FOOTER_TEXT = "JvG Consultancy | Safety • Governance • Advisory | © 2026"

def set_cell_shading(cell, color_hex):
    """Set cell background color."""
    shading = parse_xml(f'<w:shd {nsdecls("w")} w:fill="{color_hex}"/>')
    cell._tc.get_or_add_tcPr().append(shading)

def set_cell_border(cell, **kwargs):
    """Set cell borders."""
    tc = cell._tc
    tcPr = tc.get_or_add_tcPr()
    tcBorders = parse_xml(f'<w:tcBorders {nsdecls("w")}></w:tcBorders>')
    for edge, val in kwargs.items():
        element = parse_xml(
            f'<w:{edge} {nsdecls("w")} w:val="{val["val"]}" w:sz="{val["sz"]}" w:space="0" w:color="{val["color"]}"/>'
        )
        tcBorders.append(element)
    tcPr.append(tcBorders)

def add_header_row(table, row_idx, texts, color="003366"):
    """Style a table header row."""
    for i, txt in enumerate(texts):
        cell = table.cell(row_idx, i)
        cell.text = ""
        p = cell.paragraphs[0]
        run = p.add_run(txt)
        run.font.bold = True
        run.font.color.rgb = RGBColor(0xFF, 0xFF, 0xFF)
        run.font.size = Pt(10)
        run.font.name = "Calibri"
        set_cell_shading(cell, color)

def add_field_table(doc, fields, col_widths=None):
    """Add a 2-column field table (label | value)."""
    table = doc.add_table(rows=len(fields), cols=2)
    table.style = 'Table Grid'
    table.alignment = WD_TABLE_ALIGNMENT.CENTER
    for i, (label, value) in enumerate(fields):
        c0 = table.cell(i, 0)
        c1 = table.cell(i, 1)
        # Label
        p0 = c0.paragraphs[0]
        r0 = p0.add_run(label)
        r0.font.bold = True
        r0.font.size = Pt(10)
        r0.font.name = "Calibri"
        r0.font.color.rgb = PRIMARY
        set_cell_shading(c0, "F0F4F8")
        c0.width = Cm(5.5)
        # Value
        p1 = c1.paragraphs[0]
        r1 = p1.add_run(value)
        r1.font.size = Pt(10)
        r1.font.name = "Calibri"
        c1.width = Cm(11.5)
    return table

def add_checkbox_line(doc, items, indent=False):
    """Add checkbox items as paragraph."""
    p = doc.add_paragraph()
    p.paragraph_format.space_before = Pt(2)
    p.paragraph_format.space_after = Pt(2)
    text = "   ".join([f"☐ {item}" for item in items])
    run = p.add_run(text)
    run.font.size = Pt(10)
    run.font.name = "Calibri"

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

def add_doc_header(doc):
    """Add document header block."""
    p = doc.add_paragraph()
    p.alignment = WD_ALIGN_PARAGRAPH.LEFT
    details = [
        ("Project", "2026-PM-VBS-Element3"),
        ("Type", "Formulierenpakket"),
        ("Auteur", "EA_05"),
        ("Versie", "1.0"),
        ("Datum", "2026-04-29"),
        ("Status", "Final"),
    ]
    for label, val in details:
        run = p.add_run(f"{label}: {val}  |  ")
        run.font.size = Pt(8)
        run.font.name = "Calibri"
        run.font.color.rgb = RGBColor(0x37, 0x41, 0x51)
    p.paragraph_format.space_after = Pt(4)
    # Divider
    p2 = doc.add_paragraph()
    run2 = p2.add_run("─" * 80)
    run2.font.size = Pt(6)
    run2.font.color.rgb = RGBColor(0xE5, 0xE7, 0xEB)

def add_footer(doc):
    """Add footer to all sections."""
    for section in doc.sections:
        footer = section.footer
        footer.is_linked_to_previous = False
        p = footer.paragraphs[0]
        p.alignment = WD_ALIGN_PARAGRAPH.CENTER
        run = p.add_run(FOOTER_TEXT)
        run.font.size = Pt(8)
        run.font.name = "Calibri"
        run.font.color.rgb = RGBColor(0x37, 0x41, 0x51)

def add_logo_header(doc):
    """Add logo + title header."""
    if os.path.exists(LOGO):
        p = doc.add_paragraph()
        p.alignment = WD_ALIGN_PARAGRAPH.LEFT
        run = p.add_run()
        run.add_picture(LOGO, width=Cm(2.0), height=Cm(1.8))
        p.paragraph_format.space_after = Pt(2)

# ===== BUILD DOCX =====
doc = Document()

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

# Page setup
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)

add_doc_header(doc)
add_logo_header(doc)

title = doc.add_heading("Werkvergunning Formulierenpakket", level=0)
for run in title.runs:
    run.font.color.rgb = PRIMARY
    run.font.name = "Calibri"
subtitle = doc.add_paragraph("VBS Element 3 — Controle op de Exploitatie | Phoenix Metals")
subtitle.alignment = WD_ALIGN_PARAGRAPH.CENTER
for run in subtitle.runs:
    run.font.size = Pt(12)
    run.font.color.rgb = PRIMARY
    run.font.name = "Calibri"

doc.add_paragraph()

# ===================== FORMULIER 1: WV-ALG =====================
doc.add_page_break()
add_section_title(doc, "Formulier 1: Algemene Werkvergunning (WV-ALG)")

p = doc.add_paragraph()
r = p.add_run("Procedure Ref: PE-290-01 | Risicomatrix Ref: Element 2, Doc #03")
r.font.size = Pt(9)
r.font.color.rgb = RGBColor(0x37, 0x41, 0x51)
r.font.italic = True

add_field_table(doc, [
    ("Vergunningnummer", ""),
    ("Datum aanvraag", ""),
    ("Geldig van", ""),
    ("Geldig tot", ""),
    ("Locatie / Afdeling", ""),
    ("Omschrijving werkzaamheden", ""),
    ("TRA Referentienummer", ""),
    ("Risicoclassificatie", "☐ Laag   ☐ Gemiddeld   ☐ Hoog   ☐ Kritiek"),
])

doc.add_paragraph()
p = doc.add_paragraph()
r = p.add_run("Vereiste Maatregelen")
r.font.bold = True
r.font.size = Pt(11)
r.font.color.rgb = PRIMARY
add_checkbox_line(doc, ["LOTO", "Afzetting", "PBM", "Ventilatie", "Bewaking", "Gasmeting"])

doc.add_paragraph()
p = doc.add_paragraph()
r = p.add_run("Aanvrager")
r.font.bold = True
r.font.color.rgb = PRIMARY
add_field_table(doc, [
    ("Naam", ""),
    ("Functie", ""),
    ("Handtekening", ""),
    ("Datum", ""),
])

doc.add_paragraph()
p = doc.add_paragraph()
r = p.add_run("Goedkeuring (Bevoegd Persoon)")
r.font.bold = True
r.font.color.rgb = PRIMARY
add_field_table(doc, [
    ("Naam", ""),
    ("Functie", ""),
    ("Handtekening", ""),
    ("Datum", ""),
])

doc.add_paragraph()
p = doc.add_paragraph()
r = p.add_run("Uitvoerder")
r.font.bold = True
r.font.color.rgb = PRIMARY
add_field_table(doc, [
    ("Naam", ""),
    ("Functie", ""),
    ("Handtekening", ""),
])

doc.add_paragraph()
p = doc.add_paragraph()
r = p.add_run("Afsluiting")
r.font.bold = True
r.font.color.rgb = PRIMARY
add_checkbox_line(doc, ["Werk voltooid", "Locatie opgeruimd", "Vergunning gesloten"])
add_field_table(doc, [
    ("Handtekening", ""),
    ("Datum/tijd", ""),
])

# ===================== FORMULIER 2: WV-HW =====================
doc.add_page_break()
add_section_title(doc, "Formulier 2: Heet Werk Vergunning (WV-HW)")

p = doc.add_paragraph()
r = p.add_run("Procedure Ref: PE-290-01-11 | ATEX-zones Ref: Element 2, Doc #06")
r.font.size = Pt(9)
r.font.color.rgb = RGBColor(0x37, 0x41, 0x51)
r.font.italic = True

add_field_table(doc, [
    ("Vergunningnummer", ""),
    ("Datum aanvraag", ""),
    ("Geldig van", ""),
    ("Geldig tot", ""),
    ("Locatie / Afdeling", ""),
    ("Omschrijving werkzaamheden", ""),
    ("TRA Referentienummer", ""),
    ("Risicoclassificatie", "☐ Laag   ☐ Gemiddeld   ☐ Hoog   ☐ Kritiek"),
])

doc.add_paragraph()
p = doc.add_paragraph()
r = p.add_run("Type Heet Werk")
r.font.bold = True
r.font.color.rgb = PRIMARY
add_checkbox_line(doc, ["Lassen", "Snijden", "Slijpen", "Boren", "Verhitten", "Anders: ________"])

doc.add_paragraph()
p = doc.add_paragraph()
r = p.add_run("Vereiste Maatregelen")
r.font.bold = True
r.font.color.rgb = PRIMARY
add_checkbox_line(doc, ["LOTO", "Afzetting", "PBM", "Ventilatie", "Bewaking", "Gasmeting"])

doc.add_paragraph()
p = doc.add_paragraph()
r = p.add_run("Gasmeting Resultaat")
r.font.bold = True
r.font.color.rgb = PRIMARY
add_field_table(doc, [
    ("LEL (%)", ""),
    ("O₂ (%)", ""),
    ("Tijdstip", ""),
    ("Gemeten door", ""),
])

doc.add_paragraph()
p = doc.add_paragraph()
r = p.add_run("Brandpreventie Maatregelen")
r.font.bold = True
r.font.color.rgb = PRIMARY
add_checkbox_line(doc, ["13m radius vrij van brandbaar materiaal", "Brandblusmiddelen aanwezig", "Brandbare stoffen verwijderd"])

doc.add_paragraph()
p = doc.add_paragraph()
r = p.add_run("Brandwacht")
r.font.bold = True
r.font.color.rgb = PRIMARY
add_field_table(doc, [
    ("Naam brandwacht", ""),
    ("Aanwezig van", ""),
    ("Aanwezig tot", ""),
])

doc.add_paragraph()
p = doc.add_paragraph()
r = p.add_run("Aanvrager / Goedkeuring / Uitvoerder")
r.font.bold = True
r.font.color.rgb = PRIMARY
add_field_table(doc, [
    ("Aanvrager (naam, functie, handtekening, datum)", ""),
    ("Goedkeuring (bevoegd persoon, handtekening, datum)", ""),
    ("Uitvoerder (naam, functie, handtekening)", ""),
])

doc.add_paragraph()
p = doc.add_paragraph()
r = p.add_run("Nabewaking (60 minuten na afloop)")
r.font.bold = True
r.font.color.rgb = PRIMARY
add_field_table(doc, [
    ("Resultaat nabewaking", ""),
    ("Tijdstip", ""),
    ("Handtekening", ""),
])

doc.add_paragraph()
p = doc.add_paragraph()
r = p.add_run("Afsluiting")
r.font.bold = True
r.font.color.rgb = PRIMARY
add_checkbox_line(doc, ["Werk voltooid", "Locatie opgeruimd", "Vergunning gesloten"])
add_field_table(doc, [("Handtekening", ""), ("Datum/tijd", "")])

# ===================== FORMULIER 3: WV-HO =====================
doc.add_page_break()
add_section_title(doc, "Formulier 3: Werken op Hoogte (WV-HO)")

p = doc.add_paragraph()
r = p.add_run("Procedure Ref: PE-290-02-07 | Arbobesluit art. 3.16-3.23")
r.font.size = Pt(9)
r.font.color.rgb = RGBColor(0x37, 0x41, 0x51)
r.font.italic = True

add_field_table(doc, [
    ("Vergunningnummer", ""),
    ("Datum aanvraag", ""),
    ("Geldig van", ""),
    ("Geldig tot", ""),
    ("Locatie / Afdeling", ""),
    ("Omschrijving werkzaamheden", ""),
    ("TRA Referentienummer", ""),
    ("Risicoclassificatie", "☐ Laag   ☐ Gemiddeld   ☐ Hoog   ☐ Kritiek"),
    ("Hoogte (meters)", ""),
])

doc.add_paragraph()
p = doc.add_paragraph()
r = p.add_run("Type Werk")
r.font.bold = True
r.font.color.rgb = PRIMARY
add_checkbox_line(doc, ["Steiger", "Ladder", "Dak", "Hijsen", "Anders: ________"])

doc.add_paragraph()
p = doc.add_paragraph()
r = p.add_run("Vereiste Maatregelen")
r.font.bold = True
r.font.color.rgb = PRIMARY
add_checkbox_line(doc, ["LOTO", "Afzetting", "PBM", "Ventilatie", "Bewaking", "Gasmeting"])

doc.add_paragraph()
p = doc.add_paragraph()
r = p.add_run("Valbeveiliging")
r.font.bold = True
r.font.color.rgb = PRIMARY
add_checkbox_line(doc, ["Harnas gekeurd", "Ankerpunt geverifieerd", "Lifeline geïnstalleerd", "Vangnet aanwezig"])

doc.add_paragraph()
p = doc.add_paragraph()
r = p.add_run("Weersomstandigheden")
r.font.bold = True
r.font.color.rgb = PRIMARY
add_field_table(doc, [
    ("Wind (Bft)", ""),
    ("Regen (ja/nee)", ""),
    ("Onweer (ja/nee)", ""),
])

doc.add_paragraph()
p = doc.add_paragraph()
r = p.add_run("Gereedschapsbeveiliging")
r.font.bold = True
r.font.color.rgb = PRIMARY
add_checkbox_line(doc, ["Gereedschapsriem gebruikt", "Hekwerk aanwezig"])

doc.add_paragraph()
add_field_table(doc, [
    ("Aanvrager (naam, functie, handtekening, datum)", ""),
    ("Goedkeuring (bevoegd persoon, handtekening, datum)", ""),
    ("Uitvoerder (naam, functie, handtekening)", ""),
])

doc.add_paragraph()
p = doc.add_paragraph()
r = p.add_run("Afsluiting")
r.font.bold = True
r.font.color.rgb = PRIMARY
add_checkbox_line(doc, ["Werk voltooid", "Locatie opgeruimd", "Vergunning gesloten"])
add_field_table(doc, [("Handtekening", ""), ("Datum/tijd", "")])

# ===================== FORMULIER 4: WV-LO =====================
doc.add_page_break()
add_section_title(doc, "Formulier 4: LOTO Vergunning (WV-LO)")

p = doc.add_paragraph()
r = p.add_run("Procedure Ref: PE-290-01 (LOTO protocol) | FMECA Ref: Element 2, Doc #04")
r.font.size = Pt(9)
r.font.color.rgb = RGBColor(0x37, 0x41, 0x51)
r.font.italic = True

add_field_table(doc, [
    ("Vergunningnummer", ""),
    ("Datum aanvraag", ""),
    ("Geldig van", ""),
    ("Geldig tot", ""),
    ("Locatie / Afdeling", ""),
    ("Omschrijving werkzaamheden", ""),
    ("TRA Referentienummer", ""),
    ("Risicoclassificatie", "☐ Laag   ☐ Gemiddeld   ☐ Hoog   ☐ Kritiek"),
    ("Apparaat / Installatie ID", ""),
])

doc.add_paragraph()
p = doc.add_paragraph()
r = p.add_run("Energiestatus")
r.font.bold = True
r.font.color.rgb = PRIMARY
add_checkbox_line(doc, ["Elektrisch", "Hydraulisch", "Pneumatisch", "Thermisch", "Chemisch"])

doc.add_paragraph()
p = doc.add_paragraph()
r = p.add_run("Isolatiepunten")
r.font.bold = True
r.font.color.rgb = PRIMARY

iso_table = doc.add_table(rows=6, cols=5)
iso_table.style = 'Table Grid'
add_header_row(iso_table, 0, ["#", "Locatie", "Type", "Slotnummer", "Uitgevoerd door"])
for i in range(1, 6):
    iso_table.cell(i, 0).text = str(i)

doc.add_paragraph()
p = doc.add_paragraph()
r = p.add_run("Verificatie")
r.font.bold = True
r.font.color.rgb = PRIMARY
add_field_table(doc, [
    ("Energie = nul bevestigd", "☐ Ja   ☐ Nee"),
    ("Getest door", ""),
    ("Datum/tijd", ""),
])

doc.add_paragraph()
add_field_table(doc, [
    ("Aanvrager (naam, functie, handtekening, datum)", ""),
    ("Goedkeuring (bevoegd persoon, handtekening, datum)", ""),
    ("Uitvoerder (naam, functie, handtekening)", ""),
])

doc.add_paragraph()
p = doc.add_paragraph()
r = p.add_run("LOTO Verwijdering")
r.font.bold = True
r.font.color.rgb = PRIMARY
add_field_table(doc, [
    ("Alle sloten verwijderd", "☐ Ja   ☐ Nee"),
    ("Geverifieerd door", ""),
    ("Datum/tijd", ""),
])

# ===================== FORMULIER 5: TRA =====================
doc.add_page_break()
add_section_title(doc, "Formulier 5: Task Risk Assessment (TRA)")

p = doc.add_paragraph()
r = p.add_run("Procedure Ref: PE-290-01 (TRA protocol) | Gevarenregister Ref: Element 2, Doc #02")
r.font.size = Pt(9)
r.font.color.rgb = RGBColor(0x37, 0x41, 0x51)
r.font.italic = True

add_field_table(doc, [
    ("TRA Nummer", ""),
    ("Datum", ""),
    ("Locatie", ""),
    ("Werkzaamheden", ""),
    ("Teamleden", ""),
])

doc.add_paragraph()
p = doc.add_paragraph()
r = p.add_run("Stap 1: Taken Opsplitsen")
r.font.bold = True
r.font.color.rgb = PRIMARY

t1 = doc.add_table(rows=6, cols=2)
t1.style = 'Table Grid'
add_header_row(t1, 0, ["Taak Nr.", "Taakomschrijving"])
for i in range(1, 6):
    t1.cell(i, 0).text = str(i)

doc.add_paragraph()
p = doc.add_paragraph()
r = p.add_run("Stap 2: Gevaren Identificeren")
r.font.bold = True
r.font.color.rgb = PRIMARY

t2 = doc.add_table(rows=6, cols=3)
t2.style = 'Table Grid'
add_header_row(t2, 0, ["Taak", "Gevaar", "Wie blootgesteld"])

doc.add_paragraph()
p = doc.add_paragraph()
r = p.add_run("Stap 3: Risico Beoordelen")
r.font.bold = True
r.font.color.rgb = PRIMARY

t3 = doc.add_table(rows=6, cols=5)
t3.style = 'Table Grid'
add_header_row(t3, 0, ["Gevaar", "Kans (1-5)", "Effect (1-5)", "Risicoscore", "Classificatie"])

doc.add_paragraph()
p = doc.add_paragraph()
r = p.add_run("Stap 4: Maatregelen (Hiërarchie)")
r.font.bold = True
r.font.color.rgb = PRIMARY

t4 = doc.add_table(rows=6, cols=6)
t4.style = 'Table Grid'
add_header_row(t4, 0, ["Gevaar", "Eliminatie", "Substitutie", "Technisch", "Organisatorisch", "PBM"])

doc.add_paragraph()
p = doc.add_paragraph()
r = p.add_run("Stap 5: Restrisico")
r.font.bold = True
r.font.color.rgb = PRIMARY

t5 = doc.add_table(rows=6, cols=4)
t5.style = 'Table Grid'
add_header_row(t5, 0, ["Gevaar", "Kans (1-5)", "Effect (1-5)", "Restrisicoscore"])

doc.add_paragraph()
p = doc.add_paragraph()
r = p.add_run("Goedkeuring")
r.font.bold = True
r.font.color.rgb = PRIMARY
add_field_table(doc, [
    ("Teamleider (naam, handtekening)", ""),
    ("HSEQ (naam, handtekening)", ""),
    ("Datum", ""),
])

# ===================== FORMULIER 6: OVERBRUGGINGSLOG =====================
doc.add_page_break()
add_section_title(doc, "Formulier 6: Overbruggingslog")

p = doc.add_paragraph()
r = p.add_run("Procedure Ref: PE-140-19 | SIL Ref: Element 2, FMECA Doc #04")
r.font.size = Pt(9)
r.font.color.rgb = RGBColor(0x37, 0x41, 0x51)
r.font.italic = True

add_field_table(doc, [
    ("Log Nummer", ""),
    ("Datum/tijd", ""),
    ("Systeem / Interlock ID", ""),
    ("SIL Classificatie", "☐ SIL 1   ☐ SIL 2   ☐ SIL 3   ☐ SIL 4"),
    ("Reden Overbrugging", ""),
    ("Compenserende Maatregelen", ""),
])

doc.add_paragraph()
p = doc.add_paragraph()
r = p.add_run("Goedkeuring (Dubbele Handtekening VERPLICHT)")
r.font.bold = True
r.font.color.rgb = PRIMARY
add_field_table(doc, [
    ("Operations (naam, functie, handtekening)", ""),
    ("HSEQ (naam, functie, handtekening)", ""),
    ("Datum", ""),
])

doc.add_paragraph()
p = doc.add_paragraph()
r = p.add_run("Tijdslimiet: Maximaal 8 uur. Bij overschrijding: nieuwe aanvraag vereist.")
r.font.bold = True
r.font.size = Pt(10)
r.font.color.rgb = RGBColor(0xFF, 0x6D, 0x00)

doc.add_paragraph()
p = doc.add_paragraph()
r = p.add_run("Terugplaatsing")
r.font.bold = True
r.font.color.rgb = PRIMARY
add_field_table(doc, [
    ("Teruggeplaatst", "☐ Ja   ☐ Nee"),
    ("Datum/tijd", ""),
    ("Functietest OK", "☐ Ja   ☐ Nee"),
    ("Geverifieerd door (naam, functie)", ""),
])

# ===================== BRONVERWIJZINGEN =====================
doc.add_page_break()
add_section_title(doc, "Bronverwijzingen", level=2)
refs = [
    "[1] BRZO 2015, Art. 7 — Controle op de Exploitatie (Element IV)",
    "[2] NTA 8620 — Control of Work, TRA, LOTO, Hot Work, Werken op Hoogte",
    "[3] Arbowet/Arbobesluit — Art. 3.16-3.23 (werken op hoogte), heet werk",
    "[4] Phoenix Metals Procedures: PE-290-01, PE-290-01-11, PE-290-02-07, PE-140-19",
    "[5] Meta-Dossier VBS Element 3, versie 1.0 (2026-04-29)",
    "[6] PGS 15:2025 — Opslag gevaarlijke stoffen",
]
for ref in refs:
    p = doc.add_paragraph(ref)
    p.paragraph_format.space_after = Pt(4)
    for run in p.runs:
        run.font.size = Pt(10)
        run.font.name = "Calibri"

# ===================== TIERVERIFY =====================
doc.add_paragraph()
add_section_title(doc, "TierVerify — Validatielog", level=2)
verify_items = [
    ("Bronverifie", "Alle formulieren gebaseerd op BRZO 2015 Art. 7, NTA 8620, Phoenix Metals procedures [1][2][4]"),
    ("Volledigheid", "6 formulieren gegenereerd: WV-ALG, WV-HW, WV-HO, WV-LO, TRA, Overbruggingslog ✅"),
    ("Consistentie", "Formuliervelden consistent met Meta-Dossier §2.2 NTA 8620 eisen ✅"),
    ("Branding", "JvG logo, kleurenpalet #003366, Calibri typeface, footer aanwezig ✅"),
    ("Format", ".docx en .xlsx gegenereerd per protocol ✅"),
    ("Procedure-refs", "PE-290-01, PE-290-01-11, PE-290-02-07, PE-140-19 gekoppeld ✅"),
]
t_verify = doc.add_table(rows=len(verify_items)+1, cols=3)
t_verify.style = 'Table Grid'
add_header_row(t_verify, 0, ["Check", "Resultaat", "Status"])
for i, (check, result) in enumerate(verify_items):
    t_verify.cell(i+1, 0).text = check
    t_verify.cell(i+1, 1).text = result
    t_verify.cell(i+1, 2).text = "✅"

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

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

PRIMARY_HEX = "003366"
HEADER_FILL = PatternFill(start_color=PRIMARY_HEX, end_color=PRIMARY_HEX, fill_type="solid")
HEADER_FONT = Font(name="Calibri", bold=True, color="FFFFFF", size=11)
BODY_FONT = Font(name="Calibri", size=10, color="1F2937")
TITLE_FONT = Font(name="Calibri", bold=True, size=14, color=PRIMARY_HEX)
THIN_BORDER = Border(
    left=Side(style='thin', color='E5E7EB'),
    right=Side(style='thin', color='E5E7EB'),
    top=Side(style='thin', color='E5E7EB'),
    bottom=Side(style='thin', color='E5E7EB')
)
ALT_FILL = PatternFill(start_color="F0F4F8", end_color="F0F4F8", fill_type="solid")

# Conditional formatting fills
RED_FILL = PatternFill(start_color="FEE2E2", end_color="FEE2E2", fill_type="solid")
AMBER_FILL = PatternFill(start_color="FEF3C7", end_color="FEF3C7", fill_type="solid")
GREEN_FILL = PatternFill(start_color="DCFCE7", end_color="DCFCE7", fill_type="solid")

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

def style_body(ws, row, max_col, alt=False):
    for col in range(1, max_col + 1):
        c = ws.cell(row=row, column=col)
        c.font = BODY_FONT
        c.border = THIN_BORDER
        if alt:
            c.fill = ALT_FILL

def auto_width(ws, max_col, min_w=12, max_w=30):
    for col in range(1, max_col + 1):
        max_len = min_w
        letter = get_column_letter(col)
        for cell in ws[letter]:
            if cell.value:
                max_len = max(max_len, min(len(str(cell.value)) + 2, max_w))
        ws.column_dimensions[letter].width = max_len

def add_title_row(ws, title, max_col):
    ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=max_col)
    c = ws.cell(row=1, column=1)
    c.value = title
    c.font = TITLE_FONT
    c.alignment = Alignment(horizontal='left')
    ws.row_dimensions[1].height = 30

# Tab 1: WV-ALG
ws1 = wb.active
ws1.title = "WV-ALG"
headers1 = ["Vergunningnummer", "Datum aanvraag", "Geldig van", "Geldig tot", "Locatie/Afdeling",
            "Omschrijving werkzaamheden", "TRA Ref.nr.", "Risicoclassificatie",
            "LOTO", "Afzetting", "PBM", "Ventilatie", "Bewaking", "Gasmeting",
            "Aanvrager", "Functie aanvrager", "Handtekening aanvrager", "Datum aanvrager",
            "Goedkeuring door", "Handtekening goedkeuring", "Datum goedkeuring",
            "Uitvoerder", "Functie uitvoerder", "Handtekening uitvoerder",
            "Werk voltooid", "Locatie opgeruimd", "Vergunning gesloten",
            "Handtekening afsluiting", "Datum/tijd afsluiting"]
add_title_row(ws1, "Algemene Werkvergunning (WV-ALG)", len(headers1))
for i, h in enumerate(headers1):
    ws1.cell(row=2, column=i+1, value=h)
style_header(ws1, 2, len(headers1))
for r in range(3, 13):
    style_body(ws1, r, len(headers1), alt=(r % 2 == 0))
auto_width(ws1, len(headers1))
ws1.sheet_properties.pageSetUpPr = openpyxl.worksheet.properties.PageSetupProperties(fitToPage=True)
ws1.page_setup.fitToWidth = 1

# Tab 2: WV-HW
ws2 = wb.create_sheet("WV-HW")
headers2 = ["Vergunningnummer", "Datum aanvraag", "Geldig van", "Geldig tot", "Locatie/Afdeling",
            "Omschrijving werkzaamheden", "TRA Ref.nr.", "Risicoclassificatie",
            "Lassen", "Snijden", "Slijpen", "Boren", "Verhitten", "Anders",
            "LEL (%)", "O₂ (%)", "Tijdstip gasmeting", "Gemeten door",
            "13m radius vrij", "Brandblusmiddelen", "Brandbare stoffen verwijderd",
            "Naam brandwacht", "Aanwezig van", "Aanwezig tot",
            "Aanvrager", "Goedkeuring door", "Uitvoerder",
            "Nabewaking resultaat", "Tijdstip nabewaking", "Handtekening nabewaking",
            "Werk voltooid", "Locatie opgeruimd", "Vergunning gesloten", "Handtekening afsluiting", "Datum/tijd"]
add_title_row(ws2, "Heet Werk Vergunning (WV-HW)", len(headers2))
for i, h in enumerate(headers2):
    ws2.cell(row=2, column=i+1, value=h)
style_header(ws2, 2, len(headers2))
for r in range(3, 13):
    style_body(ws2, r, len(headers2), alt=(r % 2 == 0))
auto_width(ws2, len(headers2))
ws2.sheet_properties.pageSetUpPr = openpyxl.worksheet.properties.PageSetupProperties(fitToPage=True)
ws2.page_setup.fitToWidth = 1

# Tab 3: WV-HO
ws3 = wb.create_sheet("WV-HO")
headers3 = ["Vergunningnummer", "Datum aanvraag", "Geldig van", "Geldig tot", "Locatie/Afdeling",
            "Omschrijving werkzaamheden", "TRA Ref.nr.", "Risicoclassificatie",
            "Hoogte (m)", "Steiger", "Ladder", "Dak", "Hijsen", "Anders",
            "Harnas gekeurd", "Ankerpunt", "Lifeline", "Vangnet",
            "Wind (Bft)", "Regen", "Onweer",
            "Gereedschapsriem", "Hekwerk",
            "Aanvrager", "Goedkeuring door", "Uitvoerder",
            "Werk voltooid", "Locatie opgeruimd", "Vergunning gesloten", "Handtekening afsluiting", "Datum/tijd"]
add_title_row(ws3, "Werken op Hoogte (WV-HO)", len(headers3))
for i, h in enumerate(headers3):
    ws3.cell(row=2, column=i+1, value=h)
style_header(ws3, 2, len(headers3))
for r in range(3, 13):
    style_body(ws3, r, len(headers3), alt=(r % 2 == 0))
auto_width(ws3, len(headers3))
ws3.sheet_properties.pageSetUpPr = openpyxl.worksheet.properties.PageSetupProperties(fitToPage=True)
ws3.page_setup.fitToWidth = 1

# Tab 4: WV-LO
ws4 = wb.create_sheet("WV-LO")
headers4 = ["Vergunningnummer", "Datum aanvraag", "Geldig van", "Geldig tot", "Locatie/Afdeling",
            "Omschrijving werkzaamheden", "TRA Ref.nr.", "Risicoclassificatie",
            "Apparaat/Installatie ID",
            "Elektrisch", "Hydraulisch", "Pneumatisch", "Thermisch", "Chemisch",
            "Isolatiepunt #", "Locatie isolatiepunt", "Type", "Slotnummer", "Uitgevoerd door",
            "Energie = nul", "Getest door", "Datum/tijd verificatie",
            "Aanvrager", "Goedkeuring door", "Uitvoerder",
            "Alle sloten verwijderd", "Geverifieerd door", "Datum/tijd verwijdering"]
add_title_row(ws4, "LOTO Vergunning (WV-LO)", len(headers4))
for i, h in enumerate(headers4):
    ws4.cell(row=2, column=i+1, value=h)
style_header(ws4, 2, len(headers4))
for r in range(3, 13):
    style_body(ws4, r, len(headers4), alt=(r % 2 == 0))
auto_width(ws4, len(headers4))
ws4.sheet_properties.pageSetUpPr = openpyxl.worksheet.properties.PageSetupProperties(fitToPage=True)
ws4.page_setup.fitToWidth = 1

# Tab 5: TRA
ws5 = wb.create_sheet("TRA")
headers5 = ["TRA Nummer", "Datum", "Locatie", "Werkzaamheden", "Teamleden",
            "Taak Nr.", "Taakomschrijving",
            "Gevaar", "Wie blootgesteld",
            "Kans (1-5)", "Effect (1-5)", "Risicoscore (K×E)", "Classificatie",
            "Eliminatie", "Substitutie", "Technisch", "Organisatorisch", "PBM",
            "Rest Kans", "Rest Effect", "Restrisicoscore",
            "Goedkeuring teamleider", "Goedkeuring HSEQ", "Datum goedkeuring"]
add_title_row(ws5, "Task Risk Assessment (TRA)", len(headers5))
for i, h in enumerate(headers5):
    ws5.cell(row=2, column=i+1, value=h)
style_header(ws5, 2, len(headers5))
for r in range(3, 23):
    style_body(ws5, r, len(headers5), alt=(r % 2 == 0))

# Conditional formatting for risicoscore columns (L = col 12) and Restrisico (U = col 21)
for score_col in ['L', 'U']:
    ws5.conditional_formatting.add(f'{score_col}3:{score_col}22',
        CellIsRule(operator='greaterThanOrEqual', formula=['20'], fill=RED_FILL))
    ws5.conditional_formatting.add(f'{score_col}3:{score_col}22',
        CellIsRule(operator='between', formula=['10', '19'], fill=AMBER_FILL))
    ws5.conditional_formatting.add(f'{score_col}3:{score_col}22',
        CellIsRule(operator='lessThan', formula=['10'], fill=GREEN_FILL))

auto_width(ws5, len(headers5))
ws5.sheet_properties.pageSetUpPr = openpyxl.worksheet.properties.PageSetupProperties(fitToPage=True)
ws5.page_setup.fitToWidth = 1

# Tab 6: Overbruggingslog
ws6 = wb.create_sheet("Overbruggingslog")
headers6 = ["Log Nummer", "Datum/tijd", "Systeem/Interlock ID", "SIL Classificatie",
            "Reden Overbrugging", "Compenserende Maatregelen",
            "Operations (naam)", "Operations (handtekening)",
            "HSEQ (naam)", "HSEQ (handtekening)", "Datum goedkeuring",
            "Tijdslimiet (max 8 uur)",
            "Teruggeplaatst (J/N)", "Datum/tijd terugplaatsing",
            "Functietest OK (J/N)", "Geverifieerd door"]
add_title_row(ws6, "Overbruggingslog", len(headers6))
for i, h in enumerate(headers6):
    ws6.cell(row=2, column=i+1, value=h)
style_header(ws6, 2, len(headers6))
for r in range(3, 23):
    style_body(ws6, r, len(headers6), alt=(r % 2 == 0))

# Conditional formatting for SIL column (D)
ws6.conditional_formatting.add('D3:D22',
    CellIsRule(operator='equal', formula=['"SIL 3"'], fill=RED_FILL))
ws6.conditional_formatting.add('D3:D22',
    CellIsRule(operator='equal', formula=['"SIL 4"'], fill=RED_FILL))
ws6.conditional_formatting.add('D3:D22',
    CellIsRule(operator='equal', formula=['"SIL 2"'], fill=AMBER_FILL))
ws6.conditional_formatting.add('D3:D22',
    CellIsRule(operator='equal', formula=['"SIL 1"'], fill=GREEN_FILL))

auto_width(ws6, len(headers6))
ws6.sheet_properties.pageSetUpPr = openpyxl.worksheet.properties.PageSetupProperties(fitToPage=True)
ws6.page_setup.fitToWidth = 1

wb.save(XLSX_OUT)
print(f"XLSX saved: {XLSX_OUT}")
print("DONE")