#!/usr/bin/env python3
"""Generate Stavaza Portefeuille Update Excel template."""

from openpyxl import Workbook
from openpyxl.styles import (
    Font, PatternFill, Alignment, Border, Side, NamedStyle
)
from openpyxl.utils import get_column_letter
from openpyxl.formatting.rule import CellIsRule
import os

# ── Constants ──────────────────────────────────────────────────────────────
OUTPUT = "/root/projects/jg/2026-stavaza-portefeuille-template/deliverables/stavaza_template_v1.xlsx"

# Colours
NAVY       = "003366"
WHITE      = "FFFFFF"
LIGHT_GREY = "F8F9FA"
BORDER_CLR = "E5E7EB"
DARK_TEXT  = "1F2937"
GREEN      = "00A859"
YELLOW     = "F59E0B"
RED        = "EF4444"

# Fonts
TITLE_FONT   = Font(name="Calibri", size=18, bold=True, color=WHITE)
SUB_FONT     = Font(name="Calibri", size=12, bold=True, color=WHITE)
LABEL_FONT   = Font(name="Calibri", size=11, bold=True, color=DARK_TEXT)
HEADER_FONT  = Font(name="Calibri", size=11, bold=True, color=WHITE)
INSTR_FONT   = Font(name="Calibri", size=9, italic=True, color="6B7280")
BODY_FONT    = Font(name="Calibri", size=10, color=DARK_TEXT)
SECTION_FONT = Font(name="Calibri", size=13, bold=True, color=NAVY)
BODY_TEXT    = Font(name="Calibri", size=11, color=DARK_TEXT)
TIP_FONT     = Font(name="Calibri", size=10, color=DARK_TEXT)

# Fills
NAVY_FILL    = PatternFill("solid", fgColor=NAVY)
GREY_FILL    = PatternFill("solid", fgColor=LIGHT_GREY)
WHITE_FILL   = PatternFill("solid", fgColor=WHITE)

# Borders
thin_side = Side(style="thin", color=BORDER_CLR)
ALL_BORDER = Border(left=thin_side, right=thin_side, top=thin_side, bottom=thin_side)

# Alignments
CENTER  = Alignment(horizontal="center", vertical="center", wrap_text=True)
LEFT    = Alignment(horizontal="left", vertical="center", wrap_text=True)
LEFT_TOP = Alignment(horizontal="left", vertical="top", wrap_text=True)

# ── Column definitions ─────────────────────────────────────────────────────
COLUMNS = [
    ("A", "Nr.",                          5),
    ("B", "Onderwerp / Activiteit",      30),
    ("C", "Status",                      12),
    ("D", "Wat gaat goed",               30),
    ("E", "Aandacht nodig",              30),
    ("F", "Kritieke punten / Blokkades", 30),
    ("G", "Actie / Beslissing gevraagd", 30),
    ("H", "Actie-eigenaar",              18),
    ("I", "Deadline",                    14),
]

DATA_START_ROW = 8
DATA_END_ROW   = 22


def build_sheet1(ws):
    """Build the main 'Stavaza Update' sheet."""
    # ── Column widths ──
    for letter, _, width in COLUMNS:
        ws.column_dimensions[letter].width = width

    # ── Row heights for header area ──
    ws.row_dimensions[1].height = 36
    ws.row_dimensions[2].height = 22
    ws.row_dimensions[3].height = 24
    ws.row_dimensions[4].height = 24
    ws.row_dimensions[5].height = 22
    ws.row_dimensions[6].height = 30
    ws.row_dimensions[7].height = 28

    last_col = "I"

    # ── Rij 1: Titel ──
    ws.merge_cells(f"A1:{last_col}1")
    c = ws["A1"]
    c.value = "STAVAZA — STAND VAN ZAKEN"
    c.font = TITLE_FONT
    c.fill = NAVY_FILL
    c.alignment = CENTER

    # ── Rij 2: Subtitel ──
    ws.merge_cells(f"A2:{last_col}2")
    c = ws["A2"]
    c.value = "Portefeuille Update Formulier"
    c.font = SUB_FONT
    c.fill = NAVY_FILL
    c.alignment = CENTER

    # ── Rij 3: Portefeuille naam ──
    ws.merge_cells(f"A3:{last_col}3")
    c = ws["A3"]
    c.value = "Portefeuille naam: ______________________________________________"
    c.font = LABEL_FONT
    c.fill = GREY_FILL
    c.alignment = LEFT

    # ── Rij 4: Houder + Datum vergadering ──
    ws.merge_cells("A4:E4")
    c = ws["A4"]
    c.value = "Portefeuille houder: ___________________________"
    c.font = LABEL_FONT
    c.fill = GREY_FILL
    c.alignment = LEFT

    ws.merge_cells("F4:I4")
    c = ws["F4"]
    c.value = "Datum vergadering: ____________________"
    c.font = LABEL_FONT
    c.fill = GREY_FILL
    c.alignment = LEFT

    # ── Rij 5: Datum ingevuld ──
    ws.merge_cells(f"A5:{last_col}5")
    c = ws["A5"]
    c.value = "Datum ingevuld: _____________________"
    c.font = LABEL_FONT
    c.fill = GREY_FILL
    c.alignment = LEFT

    # ── Rij 6: Instructie ──
    ws.merge_cells(f"A6:{last_col}6")
    c = ws["A6"]
    c.value = (
        "Vul dit formulier vóór de vergadering in. Eén rij per onderwerp/activiteit.  "
        "Status: Groen = op schema, Geel = aandacht nodig, Rood = kritiek/blokkade."
    )
    c.font = INSTR_FONT
    c.fill = GREY_FILL
    c.alignment = LEFT

    # ── Rij 7: Kolom headers ──
    for letter, title, _ in COLUMNS:
        c = ws[f"{letter}7"]
        c.value = title
        c.font = HEADER_FONT
        c.fill = NAVY_FILL
        c.alignment = CENTER
        c.border = ALL_BORDER

    # ── Data rijen 8-22 ──
    for row_idx in range(DATA_START_ROW, DATA_END_ROW + 1):
        zebra = WHITE_FILL if (row_idx - DATA_START_ROW) % 2 == 0 else PatternFill("solid", fgColor=LIGHT_GREY)
        for letter, _, _ in COLUMNS:
            c = ws[f"{letter}{row_idx}"]
            c.font = BODY_FONT
            c.fill = zebra
            c.border = ALL_BORDER
            c.alignment = LEFT_TOP
        # Nr. kolom auto-nummeren
        ws[f"A{row_idx}"] = row_idx - DATA_START_ROW + 1
        ws[f"A{row_idx}"].alignment = CENTER
        ws[f"A{row_idx}"].font = BODY_FONT

    # ── Conditional formatting op Status kolom (C8:C22) ──
    rng = f"C{DATA_START_ROW}:C{DATA_END_ROW}"

    green_font = Font(name="Calibri", size=10, bold=True, color=GREEN)
    yellow_font = Font(name="Calibri", size=10, bold=True, color=YELLOW)
    red_font   = Font(name="Calibri", size=10, bold=True, color=RED)

    ws.conditional_formatting.add(rng, CellIsRule(
        operator="equal", formula=['"Groen"'], font=green_font
    ))
    ws.conditional_formatting.add(rng, CellIsRule(
        operator="equal", formula=['"Geel"'], font=yellow_font
    ))
    ws.conditional_formatting.add(rng, CellIsRule(
        operator="equal", formula=['"Rood"'], font=red_font
    ))

    # ── Freeze panes ──
    ws.freeze_panes = "A8"

    # ── Print settings ──
    ws.page_setup.orientation = ws.ORIENTATION_LANDSCAPE
    ws.page_setup.fitToWidth = 1
    ws.page_setup.fitToHeight = 0
    ws.sheet_properties.pageSetUpPr.fitToPage = True
    ws.page_margins.left = 0.5
    ws.page_margins.right = 0.5
    ws.page_margins.top = 0.7
    ws.page_margins.bottom = 0.5


def build_sheet2(ws):
    """Build the 'Toelichting' sheet."""
    ws.column_dimensions["A"].width = 3
    ws.column_dimensions["B"].width = 100

    row = 1

    # Titel
    ws.merge_cells(f"A{row}:B{row}")
    c = ws[f"A{row}"]
    c.value = "Toelichting — Stavaza Portefeuille Update"
    c.font = Font(name="Calibri", size=16, bold=True, color=WHITE)
    c.fill = NAVY_FILL
    c.alignment = LEFT
    ws.row_dimensions[row].height = 34
    row += 2

    def add_section(title, lines, numbered=False):
        nonlocal row
        # Section header
        c = ws[f"B{row}"]
        c.value = title
        c.font = SECTION_FONT
        c.alignment = LEFT
        ws.row_dimensions[row].height = 24
        row += 1

        for i, line in enumerate(lines, 1):
            c = ws[f"B{row}"]
            if numbered:
                c.value = f"{i}.  {line}"
            else:
                c.value = line
            c.font = BODY_TEXT
            c.alignment = LEFT_TOP
            # Estimate row height based on text length
            ws.row_dimensions[row].height = max(20, min(60, (len(line) // 90 + 1) * 18))
            row += 1
        row += 1  # spacer

    add_section("Doel van dit formulier", [
        "Dit formulier biedt een gestructureerde manier om de stand van zaken (stavaza) van jouw "
        "portefeuille te presenteren tijdens vergaderingen. Het zorgt ervoor dat alle teamleden op "
        "dezelfde manier rapporteren en dat vergaderingen efficiënt verlopen.",
    ])

    add_section("Hoe te gebruiken", [
        "Vul de header-gegevens in (portefeuille naam, je naam, datums).",
        "Voeg één rij toe per onderwerp of activiteit binnen jouw portefeuille.",
        "Geef per onderwerp een status (Groen/Geel/Rood).",
        "Beschrijf wat goed gaat, wat aandacht nodig heeft, en kritieke punten.",
        "Noteer welke actie of beslissing je vraagt van de vergadering.",
        "Wijs een actie-eigenaar toe en noteer de deadline.",
        "Lever het formulier uiterlijk 24 uur vóór de vergadering in.",
    ], numbered=True)

    # Status kleuren sectie met gekleurde tekst
    c = ws[f"B{row}"]
    c.value = "Status kleuren"
    c.font = SECTION_FONT
    c.alignment = LEFT
    ws.row_dimensions[row].height = 24
    row += 1

    for label, desc, clr in [
        ("Groen", "Op schema, geen problemen. Loopt soepel.", GREEN),
        ("Geel",  "Aandacht nodig. Risico op vertraging of problemen.", YELLOW),
        ("Rood",  "Kritiek. Blokkade of urgent probleem dat directe aandacht vereist.", RED),
    ]:
        c = ws[f"B{row}"]
        c.value = f"  ▌  {label}:  {desc}"
        c.font = Font(name="Calibri", size=11, bold=True, color=clr)
        c.alignment = LEFT_TOP
        ws.row_dimensions[row].height = 22
        row += 1
    row += 1

    add_section("Tips voor effectief vergaderen", [
        "Bereid elke vergadering goed voor.",
        "Houd updates kort en bondig.",
        "Eén actiepunt = één eigenaar.",
        "Parkeren van langlopende discussies.",
    ], numbered=True)

    # Print settings
    ws.page_setup.orientation = ws.ORIENTATION_PORTRAIT
    ws.page_setup.fitToWidth = 1
    ws.page_setup.fitToHeight = 0
    ws.sheet_properties.pageSetUpPr.fitToPage = True


def main():
    wb = Workbook()

    ws1 = wb.active
    ws1.title = "Stavaza Update"
    build_sheet1(ws1)

    ws2 = wb.create_sheet("Toelichting")
    build_sheet2(ws2)

    os.makedirs(os.path.dirname(OUTPUT), exist_ok=True)
    wb.save(OUTPUT)
    print(f"✅ Saved: {OUTPUT}")


if __name__ == "__main__":
    main()