#!/usr/bin/env python3
"""
Stavaza Template v1.1 Generator
Lions MD110 Gouverneursraad — Portefeuille Update Template
"""
import openpyxl
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter
from openpyxl.worksheet.datavalidation import DataValidation
from openpyxl.formatting.rule import CellIsRule, FormulaRule
from openpyxl.workbook.defined_name import DefinedName

# ── COLOURS ──
C_NAVY="003366"; C_WHITE="FFFFFF"; C_GREEN="00A859"; C_ORANGE="F59E0B"
C_RED="EF4444"; C_BLUE="3B82F6"; C_GRAY="9CA3AF"; C_LIGHT_YEL="FEF3C7"
C_BG="FFFFFF"; C_CARD="F8F9FA"; C_BORDER="E5E7EB"; C_ZEBRA="F3F4F6"
C_INSTRUCT="6B7280"; C_DARK="1F2937"; C_SUBNAVY="335599"
FN="Calibri"

def font(sz=11,b=False,c=C_DARK,i=False):
    return Font(name=FN,size=sz,bold=b,color=c,italic=i)
def fill(h):
    return PatternFill(start_color=h,end_color=h,fill_type="solid")
def tborder(c=C_BORDER):
    s=Side(style="thin",color=c); return Border(left=s,right=s,top=s,bottom=s)
def mborder(c=C_NAVY):
    s=Side(style="medium",color=c); return Border(left=s,right=s,top=s,bottom=s)
CTR=Alignment(horizontal="center",vertical="center",wrap_text=True)
LFT=Alignment(horizontal="left",vertical="center",wrap_text=True)
LTOP=Alignment(horizontal="left",vertical="top",wrap_text=True)
RGT=Alignment(horizontal="right",vertical="center",wrap_text=True)

wb=Workbook()

# ── Create sheets in display order ──
ws_stav=wb.active; ws_stav.title="Stavaza Update"
ws_dash=wb.create_sheet("Samenvatting")
ws_list=wb.create_sheet("Lijsten")
ws_doc=wb.create_sheet("Toelichting")

# ════════════════════════════════════════════
# SHEET 3: "Lijsten" (build first for named ranges)
# ════════════════════════════════════════════
ws_list.sheet_properties.tabColor=C_NAVY
ws_list.sheet_view.showGridLines=False
for col,w in {"A":22,"B":14,"C":14,"D":18,"E":28,"F":18}.items():
    ws_list.column_dimensions[col].width=w

ws_list.merge_cells("A1:F1")
c=ws_list["A1"]; c.value="LIJSTEN — Instelbare Dropdown Waarden"
c.font=font(14,True,C_WHITE); c.fill=fill(C_NAVY); c.alignment=CTR
ws_list.row_dimensions[1].height=28

ws_list.merge_cells("A2:F2")
c=ws_list["A2"]; c.value="Pas waarden aan of voeg toe om dropdowns in het hoofdformulier te wijzigen. Maximaal 49 waarden per kolom (rij 3 t/m 51)."
c.font=font(10,False,C_INSTRUCT,True); c.alignment=LFT
ws_list.row_dimensions[2].height=22

for cell_ref,title in [("A3","Portefeuille"),("B3","Status"),("C3","Prioriteit"),
                        ("D3","Categorie"),("E3","Commissie/Werkgroep"),("F3","Portefeuillehouder")]:
    c=ws_list[cell_ref]; c.value=title; c.font=font(11,True,C_WHITE)
    c.fill=fill(C_NAVY); c.alignment=CTR; c.border=tborder(C_NAVY)

# Preset values
presets={
    "A":["Secretariaat","Financiën","Activiteiten/GST","Ledenbeleid/GMT","Opleidingen/GLT","Jeugd","Overige"],
    "B":["Groen","Geel","Rood","N.v.t."],
    "C":["Laag","Gemiddeld","Hoog","Urgent"],
    "D":["Bestuurlijk","Financieel","Operationeel","Communicatie","Strategisch","Juridisch"],
    "E":["" for _ in range(20)],
    "F":["CW","CC","AZ","BN","AN","CO","BZ"],
}
for col,vals in presets.items():
    for i,v in enumerate(vals):
        r=4+i
        if r>51: break
        c=ws_list[f"{col}{r}"]; c.value=v; c.font=font(11); c.alignment=LFT; c.border=tborder()
# Borders for remaining cells
for col in "ABCDEF":
    for r in range(4,52):
        c=ws_list[f"{col}{r}"]
        if c.border is None or c.border.left is None:
            c.border=tborder(); c.font=font(11); c.alignment=LFT
ws_list.freeze_panes="A4"

# ── Named ranges ──
def add_nr(name,sheet,col,r1=4,r2=51):
    ref=f"'{sheet}'!${col}${r1}:${col}${r2}"
    wb.defined_names[name]=DefinedName(name,attr_text=ref)
add_nr("_Portefeuille","Lijsten","A")
add_nr("_Status","Lijsten","B")
add_nr("_Prioriteit","Lijsten","C")
add_nr("_Categorie","Lijsten","D")
add_nr("_Commissie","Lijsten","E")
add_nr("_Houder","Lijsten","F")


# ════════════════════════════════════════════
# SHEET 1: "Stavaza Update"
# ════════════════════════════════════════════
ws_stav.sheet_properties.tabColor=C_NAVY
ws_stav.sheet_view.showGridLines=False

for col,w in {"A":5,"B":28,"C":14,"D":10,"E":10,"F":28,"G":28,"H":28,"I":28,"J":16,"K":12,"L":14}.items():
    ws_stav.column_dimensions[col].width=w

# R1: Title
ws_stav.merge_cells("A1:L1")
c=ws_stav["A1"]; c.value="STAVAZA — STAND VAN ZAKEN"
c.font=font(20,True,C_WHITE); c.fill=fill(C_NAVY); c.alignment=CTR
ws_stav.row_dimensions[1].height=42
# R2: Subtitle
ws_stav.merge_cells("A2:L2")
c=ws_stav["A2"]; c.value="Gouverneursraad Vergadering — Portefeuille Update"
c.font=font(14,False,C_WHITE); c.fill=fill(C_SUBNAVY); c.alignment=CTR
ws_stav.row_dimensions[2].height=28
# R3: Portefeuille + Houder
ws_stav["A3"].value="Portefeuille:"; ws_stav["A3"].font=font(11,True,C_NAVY); ws_stav["A3"].alignment=RGT
ws_stav.merge_cells("B3:E3"); ws_stav["B3"].fill=fill(C_CARD); ws_stav["B3"].border=tborder(); ws_stav["B3"].alignment=LFT; ws_stav["B3"].font=font(11)
ws_stav["F3"].value="Portefeuillehouder:"; ws_stav["F3"].font=font(11,True,C_NAVY); ws_stav["F3"].alignment=RGT
ws_stav.merge_cells("G3:L3"); ws_stav["G3"].fill=fill(C_CARD); ws_stav["G3"].border=tborder(); ws_stav["G3"].alignment=LFT; ws_stav["G3"].font=font(11)
ws_stav.row_dimensions[3].height=24
# R4: Datum + Commissie
ws_stav["A4"].value="Vergadering datum:"; ws_stav["A4"].font=font(11,True,C_NAVY); ws_stav["A4"].alignment=RGT
ws_stav.merge_cells("B4:E4"); ws_stav["B4"].fill=fill(C_CARD); ws_stav["B4"].border=tborder(); ws_stav["B4"].alignment=LFT; ws_stav["B4"].font=font(11); ws_stav["B4"].number_format="dd-mm-yyyy"
ws_stav["F4"].value="Commissie/Werkgroep:"; ws_stav["F4"].font=font(11,True,C_NAVY); ws_stav["F4"].alignment=RGT
ws_stav.merge_cells("G4:L4"); ws_stav["G4"].fill=fill(C_CARD); ws_stav["G4"].border=tborder(); ws_stav["G4"].alignment=LFT; ws_stav["G4"].font=font(11)
ws_stav.row_dimensions[4].height=24
# R5: Datum ingevuld + Gemaakt door
ws_stav["A5"].value="Datum ingevuld:"; ws_stav["A5"].font=font(11,True,C_NAVY); ws_stav["A5"].alignment=RGT
ws_stav.merge_cells("B5:E5"); ws_stav["B5"].fill=fill(C_CARD); ws_stav["B5"].border=tborder(); ws_stav["B5"].alignment=LFT; ws_stav["B5"].font=font(11); ws_stav["B5"].number_format="dd-mm-yyyy"
ws_stav["F5"].value="Gemaakt door:"; ws_stav["F5"].font=font(11,True,C_NAVY); ws_stav["F5"].alignment=RGT
ws_stav.merge_cells("G5:L5"); ws_stav["G5"].fill=fill(C_CARD); ws_stav["G5"].border=tborder(); ws_stav["G5"].alignment=LFT; ws_stav["G5"].font=font(11)
ws_stav.row_dimensions[5].height=24
# R6: Instruction
ws_stav.merge_cells("A6:L6")
c=ws_stav["A6"]; c.value="Vul dit formulier 48 uur vóór de Gouverneursraad vergadering in. Eén rij per onderwerp. Status: Groen = op schema, Geel = aandacht, Rood = kritiek/blokkade. Licht Rood-statussen VÓÓR de vergadering toe bij de voorzitter."
c.font=font(10,False,C_INSTRUCT,True); c.alignment=LFT; c.fill=fill(C_CARD)
ws_stav.row_dimensions[6].height=30
# R7: spacer
ws_stav.row_dimensions[7].height=8
# R8: Column headers
headers=["Nr.","Onderwerp / Activiteit","Categorie","Status","Prioriteit",
         "Wat gaat goed","Aandacht nodig","Kritieke punten / Blokkades",
         "Actie / Beslissing gevraagd","Actie-eigenaar","Deadline","Escalatie naar GR?"]
for idx,h in enumerate(headers,1):
    col=get_column_letter(idx); c=ws_stav[f"{col}8"]
    c.value=h; c.font=font(11,True,C_WHITE); c.fill=fill(C_NAVY); c.alignment=CTR; c.border=mborder()
ws_stav.row_dimensions[8].height=36
# R9-28: Data rows
for row in range(9,29):
    ws_stav.row_dimensions[row].height=42
    zebra=(row-9)%2==1
    for ci in range(1,13):
        col=get_column_letter(ci); c=ws_stav[f"{col}{row}"]
        c.border=tborder(); c.font=font(10); c.alignment=LTOP
        if zebra: c.fill=fill(C_ZEBRA)
    ws_stav[f"A{row}"].value=row-8; ws_stav[f"A{row}"].alignment=CTR; ws_stav[f"A{row}"].font=font(10,True,C_NAVY)
    ws_stav[f"K{row}"].number_format="dd-mm-yyyy"

ws_stav.freeze_panes="A9"

# Print settings
ws_stav.page_setup.orientation="landscape"
ws_stav.page_setup.paperSize=ws_stav.PAPERSIZE_A4
ws_stav.page_setup.fitToWidth=1; ws_stav.page_setup.fitToHeight=0
ws_stav.sheet_properties.pageSetUpPr.fitToPage=True
ws_stav.print_title_rows="1:8"
ws_stav.print_options.horizontalCentered=True
ws_stav.page_margins.left=0.4; ws_stav.page_margins.right=0.4
ws_stav.page_margins.top=0.5; ws_stav.page_margins.bottom=0.5

# ── Data Validations ──
dvs=[
    ("=_Portefeuille","B3","Kies een geldige portefeuille","Ongeldige invoer","Selecteer portefeuille","Portefeuille"),
    ("=_Houder","G3","Kies een geldige portefeuillehouder","Ongeldige invoer","Selecteer portefeuillehouder","Portefeuillehouder"),
    ("=_Commissie","G4","","","Selecteer commissie/werkgroep","Commissie/Werkgroep"),
]
for f1,cell,err,errt,pr,prt in dvs:
    dv=DataValidation(type="list",formula1=f1,allow_blank=True)
    if err: dv.error=err; dv.errorTitle=errt
    dv.prompt=pr; dv.promptTitle=prt
    ws_stav.add_data_validation(dv); dv.add(cell)

# Column ranges
col_dvs=[
    ("=_Status","D9:D28","Groen = op schema, Geel = aandacht, Rood = kritiek","Ongeldige status","Status"),
    ("=_Prioriteit","E9:E28","Kies prioriteit","Ongeldige invoer","Prioriteit"),
    ("=_Categorie","C9:C28","Kies categorie","Ongeldige invoer","Categorie"),
    ('"Ja,Nee,Overleg"',"L9:L28","Escalatie naar Gouverneursraad?","Ongeldige invoer","Escalatie"),
]
for f1,cell_range,err,errt,prt in col_dvs:
    dv=DataValidation(type="list",formula1=f1,allow_blank=True)
    if err: dv.error=err; dv.errorTitle=errt
    dv.promptTitle=prt
    ws_stav.add_data_validation(dv); dv.add(cell_range)

# ── Conditional Formatting ──
# Status D9:D28
for val,color in [("Groen",C_GREEN),("Geel",C_ORANGE),("Rood",C_RED)]:
    ws_stav.conditional_formatting.add("D9:D28",
        CellIsRule(operator="equal",formula=[f'"{val}"'],
                   fill=fill(color),font=Font(name=FN,size=10,bold=True,color=C_WHITE)))
# Prioriteit E9:E28
for val,color in [("Urgent",C_RED),("Hoog",C_ORANGE),("Gemiddeld",C_BLUE),("Laag",C_GRAY)]:
    ws_stav.conditional_formatting.add("E9:E28",
        CellIsRule(operator="equal",formula=[f'"{val}"'],
                   fill=fill(color),font=Font(name=FN,size=10,bold=True,color=C_WHITE)))
# Deadline K9:K28
ws_stav.conditional_formatting.add("K9:K28",
    FormulaRule(formula=['AND(K9<>"",K9<TODAY())'],fill=fill(C_RED),
                font=Font(name=FN,size=10,bold=True,color=C_WHITE)))
ws_stav.conditional_formatting.add("K9:K28",
    FormulaRule(formula=['AND(K9<>"",K9>=TODAY(),K9<=TODAY()+7)'],fill=fill(C_ORANGE),
                font=Font(name=FN,size=10,bold=True,color=C_WHITE)))
ws_stav.conditional_formatting.add("K9:K28",
    FormulaRule(formula=['AND(K9<>"",K9>TODAY()+7,K9<=TODAY()+30)'],fill=fill(C_LIGHT_YEL),
                font=Font(name=FN,size=10,color=C_DARK)))
# Escalatie L9:L28
ws_stav.conditional_formatting.add("L9:L28",
    CellIsRule(operator="equal",formula=['"Ja"'],
               font=Font(name=FN,size=10,bold=True,color=C_RED)))
ws_stav.conditional_formatting.add("L9:L28",
    CellIsRule(operator="equal",formula=['"Overleg"'],
               font=Font(name=FN,size=10,color=C_ORANGE)))


# ════════════════════════════════════════════
# SHEET 2: "Samenvatting" — Dashboard
# ════════════════════════════════════════════
ws_dash.sheet_properties.tabColor=C_BLUE
ws_dash.sheet_view.showGridLines=False
for col,w in {"A":3,"B":22,"C":12,"D":3,"E":22,"F":12,"G":3}.items():
    ws_dash.column_dimensions[col].width=w

# Title
ws_dash.merge_cells("B2:F2")
c=ws_dash["B2"]; c.value="Samenvatting Stavaza"
c.font=font(18,True,C_WHITE); c.fill=fill(C_NAVY); c.alignment=CTR
ws_dash.row_dimensions[2].height=36

# Meta
ws_dash["B4"].value="Portefeuille:"; ws_dash["B4"].font=font(11,True,C_NAVY); ws_dash["B4"].alignment=RGT
ws_dash.merge_cells("C4:F4"); ws_dash["C4"].value="='Stavaza Update'!B3"; ws_dash["C4"].font=font(12,True); ws_dash["C4"].alignment=LFT
ws_dash["B5"].value="Vergaderdatum:"; ws_dash["B5"].font=font(11,True,C_NAVY); ws_dash["B5"].alignment=RGT
ws_dash.merge_cells("C5:F5"); ws_dash["C5"].value="='Stavaza Update'!B4"; ws_dash["C5"].font=font(12,True); ws_dash["C5"].number_format="dd-mm-yyyy"; ws_dash["C5"].alignment=LFT
ws_dash["B6"].value="Datum ingevuld:"; ws_dash["B6"].font=font(11,True,C_NAVY); ws_dash["B6"].alignment=RGT
ws_dash.merge_cells("C6:F6"); ws_dash["C6"].value="='Stavaza Update'!B5"; ws_dash["C6"].font=font(12); ws_dash["C6"].number_format="dd-mm-yyyy"; ws_dash["C6"].alignment=LFT

def dash_section(row, title):
    ws_dash.merge_cells(f"B{row}:F{row}")
    c=ws_dash[f"B{row}"]; c.value=title; c.font=font(13,True,C_WHITE)
    c.fill=fill(C_NAVY); c.alignment=CTR
    ws_dash.row_dimensions[row].height=26
    return row+1

def dash_header_row(row):
    for cell,label in [(f"B{row}","Categorie"),(f"C{row}","Aantal")]:
        ws_dash[cell].value=label; ws_dash[cell].font=font(11,True,C_NAVY)
        ws_dash[cell].fill=fill(C_CARD); ws_dash[cell].border=tborder(); ws_dash[cell].alignment=CTR
    ws_dash.merge_cells(f"D{row}:F{row}")
    ws_dash[f"D{row}"].value="Visuele indicatie"; ws_dash[f"D{row}"].font=font(11,True,C_NAVY)
    ws_dash[f"D{row}"].fill=fill(C_CARD); ws_dash[f"D{row}"].border=tborder(); ws_dash[f"D{row}"].alignment=CTR
    return row+1

def dash_count_row(row,label,count_formula,color):
    ws_dash[f"B{row}"].value=label; ws_dash[f"B{row}"].font=font(11); ws_dash[f"B{row}"].border=tborder(); ws_dash[f"B{row}"].alignment=LFT
    ws_dash[f"C{row}"].value=count_formula; ws_dash[f"C{row}"].font=font(14,True); ws_dash[f"C{row}"].border=tborder(); ws_dash[f"C{row}"].alignment=CTR
    ws_dash.merge_cells(f"D{row}:F{row}"); ws_dash[f"D{row}"].fill=fill(color); ws_dash[f"D{row}"].border=tborder()
    ws_dash.row_dimensions[row].height=28
    return row+1

# Status verdeling
r=8; r=dash_section(r,"STATUS VERDELING"); r=dash_header_row(r)
for label,color in [("Groen",C_GREEN),("Geel",C_ORANGE),("Rood",C_RED),("N.v.t.",C_GRAY)]:
    r=dash_count_row(r,label,f'=COUNTIF(\'Stavaza Update\'!D9:D28,"{label}")',color)
r+=1
# Escalaties
r=dash_section(r,"ESCALATIES NAAR GOUVERNEURSRAAD")
for label,formula,color in [
    ("Aantal escalaties (Ja)",'=COUNTIF(\'Stavaza Update\'!L9:L28,"Ja")',C_RED),
    ("Aantal 'Overleg'",'=COUNTIF(\'Stavaza Update\'!L9:L28,"Overleg")',C_ORANGE)]:
    ws_dash.merge_cells(f"B{r}:C{r}"); ws_dash[f"B{r}"].value=label; ws_dash[f"B{r}"].font=font(11); ws_dash[f"B{r}"].border=tborder(); ws_dash[f"B{r}"].alignment=LFT
    ws_dash.merge_cells(f"D{r}:F{r}"); ws_dash[f"D{r}"].value=formula; ws_dash[f"D{r}"].font=font(16,True,color); ws_dash[f"D{r}"].border=tborder(); ws_dash[f"D{r}"].alignment=CTR
    ws_dash.row_dimensions[r].height=30; r+=1
r+=1
# Prioriteit
r=dash_section(r,"PRIORITEIT VERDELING"); r=dash_header_row(r)
for label,color in [("Urgent",C_RED),("Hoog",C_ORANGE),("Gemiddeld",C_BLUE),("Laag",C_GRAY)]:
    r=dash_count_row(r,label,f'=COUNTIF(\'Stavaza Update\'!E9:E28,"{label}")',color)
r+=1
# Kritieke deadlines
r=dash_section(r,"KRITIEKE DEADLINES")
for label,formula,color in [
    ("Verlopen ( < vandaag )",'=SUMPRODUCT((\'Stavaza Update\'!K9:K28<>"")*(\'Stavaza Update\'!K9:K28<TODAY())*1)',C_RED),
    ("Binnen 7 dagen",'=SUMPRODUCT((\'Stavaza Update\'!K9:K28<>"")*(\'Stavaza Update\'!K9:K28>=TODAY())*(\'Stavaza Update\'!K9:K28<=TODAY()+7)*1)',C_ORANGE),
    ("Binnen 30 dagen",'=SUMPRODUCT((\'Stavaza Update\'!K9:K28<>"")*(\'Stavaza Update\'!K9:K28>TODAY()+7)*(\'Stavaza Update\'!K9:K28<=TODAY()+30)*1)',C_LIGHT_YEL)]:
    ws_dash.merge_cells(f"B{r}:C{r}"); ws_dash[f"B{r}"].value=label; ws_dash[f"B{r}"].font=font(11); ws_dash[f"B{r}"].border=tborder(); ws_dash[f"B{r}"].alignment=LFT
    ws_dash.merge_cells(f"D{r}:F{r}"); ws_dash[f"D{r}"].value=formula
    tc=C_WHITE if color in (C_RED,C_ORANGE) else C_DARK
    ws_dash[f"D{r}"].font=font(16,True,tc); ws_dash[f"D{r}"].fill=fill(color); ws_dash[f"D{r}"].border=tborder(); ws_dash[f"D{r}"].alignment=CTR
    ws_dash.row_dimensions[r].height=30; r+=1
r+=1
# Total
ws_dash.merge_cells(f"B{r}:C{r}"); ws_dash[f"B{r}"].value="Totaal aantal items"; ws_dash[f"B{r}"].font=font(11,True,C_NAVY); ws_dash[f"B{r}"].border=tborder(); ws_dash[f"B{r}"].alignment=LFT
ws_dash.merge_cells(f"D{r}:F{r}"); ws_dash[f"D{r}"].value='=COUNTA(\'Stavaza Update\'!B9:B28)'; ws_dash[f"D{r}"].font=font(16,True,C_NAVY); ws_dash[f"D{r}"].border=tborder(); ws_dash[f"D{r}"].alignment=CTR
ws_dash.row_dimensions[r].height=30

ws_dash.page_setup.orientation="portrait"
ws_dash.page_setup.fitToWidth=1
ws_dash.sheet_properties.pageSetUpPr.fitToPage=True


# ════════════════════════════════════════════
# SHEET 4: "Toelichting"
# ════════════════════════════════════════════
ws_doc.sheet_properties.tabColor=C_GRAY
ws_doc.sheet_view.showGridLines=False
ws_doc.column_dimensions["A"].width=3
ws_doc.column_dimensions["B"].width=30
ws_doc.column_dimensions["C"].width=70

ws_doc.merge_cells("B2:C2")
c=ws_doc["B2"]; c.value="TOELICHTING — Stavaza Template v1.1"
c.font=font(16,True,C_WHITE); c.fill=fill(C_NAVY); c.alignment=CTR
ws_doc.row_dimensions[2].height=32

sections=[
    ("ALGEMEEN",[
        ("Doel","Dit formulier wordt gebruikt binnen Lions MD110 Nederland voor Gouverneursraad vergaderingen. Elke portefeuillehouder verzamelt stavaza-informatie van commissieleden en werkgroepleiders."),
        ("Workflow","1. Commissieleden/werkgroepleiders vullen de stavaza in.\n2. Portefeuillehouder reviewt en agendeert escalaties.\n3. De stavaza wordt 48 uur vóór de Gouverneursraad verspreid.\n4. Tijdens de GR worden alleen statussen Rood/Geel en escalaties besproken."),
    ]),
    ("LIJSTEN — INSTELBARE DROPDOWNS",[
        ("Zelf aanpassen","Open het werkblad 'Lijsten' om de waarden aan te passen die in de dropdowns verschijnen. Voeg nieuwe waarden toe of wijzig bestaande. De dropdowns op het hoofdformulier verwijzen automatisch naar deze lijsten."),
        ("Named Ranges","De volgende benoemde bereiken worden gebruikt:\n• _Portefeuille → Lijsten kolom A\n• _Status → Lijsten kolom B\n• _Prioriteit → Lijsten kolom C\n• _Categorie → Lijsten kolom D\n• _Commissie → Lijsten kolom E\n• _Houder → Lijsten kolom F"),
        ("Uitbreiden","Elke kolom op het Lijsten-werkblad heeft ruimte tot rij 51 (maximaal 49 waarden per kolom). Voeg gewoon een nieuwe waarde toe in de eerstvolgende lege rij."),
    ]),
    ("AUTOMATISCHE KLEURCODERING",[
        ("Status","Groen = op schema • Geel = aandacht nodig • Rood = kritiek/blokkade. De cel wordt automatisch gekleurd op basis van de gekozen status."),
        ("Prioriteit","Urgent (rood) • Hoog (oranje) • Gemiddeld (blauw) • Laag (grijs). Kleuren worden automatisch toegepast."),
        ("Deadline","Datakleuring op basis van nabijheid:\n• Verlopen (rood) — deadline ligt in het verleden\n• ≤ 7 dagen (oranje) — nadrukt met spoed\n• ≤ 30 dagen (geel) — binnen afzienbare termijn"),
        ("Escalatie","Bij 'Ja' wordt de tekst rood en vetgedrukt. Bij 'Overleg' wordt de tekst oranje."),
    ]),
    ("SAMENVATTING DASHBOARD",[
        ("Automatische berekening","Het werkblad 'Samenvatting' toont automatisch het aantal items per status, prioriteit, escalaties en kritieke deadlines. Alle waarden worden berekend met formules (COUNTIF/SUMPRODUCT) op basis van de ingevulde rijen op het hoofdformulier."),
        ("Geen handmatige actie","Het dashboard hoeft niet handmatig te worden bijgewerkt. Zodra je regels toevoegt of wijzigt in 'Stavaza Update', wordt het dashboard automatisch vernieuwd."),
    ]),
    ("LIONS MD110 CONTEXT",[
        ("Portefeuilles","A. Secretariaat (Lions Media, Secretariaat, ICT)\nB. Financiën (Kascommissie, Financiële Commissie, Lions Helpen, Statutencommissie)\nC. Activiteiten/GST (diverse werkgroepen)\nD. Ledenbeleid/GMT (Ledenbeleid, New Voices, Leo's-Lions)\nE. Opleidingen/GLT (Opleidingen)\nF. Jeugd (Peace Poster, Young Ambassador, Young Musician)"),
        ("Portefeuillehouders","CW = Council Chair • CC = Vice Governor • AZ = Secretary/Treasurer\nBN = Treasurer • AN = Membership • CO = Operations • BZ = District Governor"),
        ("Versie","Stavaza Template v1.1 — Gemaakt voor Lions MD110 Nederland.\nVoor vragen of wijzigingen, contacteer de portefeuillehouder."),
    ]),
]

row=4
for sec_title,items in sections:
    ws_doc.merge_cells(f"B{row}:C{row}")
    c=ws_doc[f"B{row}"]; c.value=sec_title; c.font=font(12,True,C_WHITE)
    c.fill=fill(C_NAVY); c.alignment=CTR; c.border=tborder()
    ws_doc.row_dimensions[row].height=26
    row+=1
    for label,text in items:
        ws_doc[f"B{row}"].value=label; ws_doc[f"B{row}"].font=font(11,True,C_NAVY)
        ws_doc[f"B{row}"].alignment=LTOP; ws_doc[f"B{row}"].border=tborder()
        ws_doc[f"C{row}"].value=text; ws_doc[f"C{row}"].font=font(11)
        ws_doc[f"C{row}"].alignment=LTOP; ws_doc[f"C{row}"].border=tborder()
        # Estimate height
        lines=text.count("\n")+1
        ws_doc.row_dimensions[row].height=max(30, lines*18)
        row+=1
    row+=1  # spacer

ws_doc.page_setup.orientation="portrait"
ws_doc.page_setup.fitToWidth=1
ws_doc.sheet_properties.pageSetUpPr.fitToPage=True


# ════════════════════════════════════════════
# SAVE
# ════════════════════════════════════════════
out_path="/root/projects/jg/2026-stavaza-portefeuille-template/deliverables/stavaza_template_v1.1.xlsx"
wb.save(out_path)
print(f"✅ Saved: {out_path}")