#!/usr/bin/env python3
"""Fase 3 — Financieel Plan HSEQ Consultancy: DOCX + XLSX generator."""

import os
from docx import Document
from docx.shared import Cm, Pt, Inches, Emu, RGBColor
from docx.enum.text import WD_ALIGN_PARAGRAPH
from docx.enum.table import WD_TABLE_ALIGNMENT
from docx.oxml.ns import qn, nsdecls
from docx.oxml import parse_xml
import openpyxl
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side, numbers
from openpyxl.utils import get_column_letter
from copy import copy

BASE = "/root/projects/jg/2026-consult-hseq-businessplan"
DEL_DOCX = f"{BASE}/deliverables/docx"
DEL_XLSX = f"{BASE}/deliverables/xlsx"
LOG_DIR = f"{BASE}/logs"
LOGO = "/root/projects/jg/assets/branding/jvg-logo-white-medium.png"

os.makedirs(DEL_DOCX, exist_ok=True)
os.makedirs(DEL_XLSX, exist_ok=True)
os.makedirs(LOG_DIR, exist_ok=True)

# ── Colour constants ──────────────────────────────────────────
PRIMARY = RGBColor(0x00, 0x33, 0x66)
DARK_TEXT = RGBColor(0x1F, 0x29, 0x37)
SECONDARY = RGBColor(0x37, 0x41, 0x51)
WHITE = RGBColor(0xFF, 0xFF, 0xFF)
GREY_BG = "F8F9FA"
HEADER_BG = "003366"
ALT_ROW = "F8F9FA"

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

def set_cell_text(cell, text, bold=False, color=None, size=10, align=None):
    cell.text = ""
    p = cell.paragraphs[0]
    if align:
        p.alignment = align
    run = p.add_run(str(text))
    run.font.size = Pt(size)
    run.font.name = "Segoe UI"
    if bold:
        run.bold = True
    if color:
        run.font.color.rgb = color
    # spacing
    p.paragraph_format.space_before = Pt(2)
    p.paragraph_format.space_after = Pt(2)

def styled_table(doc, headers, rows, col_widths=None):
    """Create a professionally styled table."""
    table = doc.add_table(rows=1 + len(rows), cols=len(headers))
    table.style = 'Table Grid'
    table.alignment = WD_TABLE_ALIGNMENT.CENTER
    # Header row
    for i, h in enumerate(headers):
        c = table.rows[0].cells[i]
        set_cell_shading(c, HEADER_BG)
        set_cell_text(c, h, bold=True, color=WHITE, size=10, align=WD_ALIGN_PARAGRAPH.CENTER)
    # Data rows
    for ri, row in enumerate(rows):
        for ci, val in enumerate(row):
            c = table.rows[ri + 1].cells[ci]
            if ri % 2 == 1:
                set_cell_shading(c, ALT_ROW)
            set_cell_text(c, val, size=10)
    if col_widths:
        for i, w in enumerate(col_widths):
            for row in table.rows:
                row.cells[i].width = Cm(w)
    return table

def add_heading_styled(doc, text, level=1):
    h = doc.add_heading(text, level=level)
    for run in h.runs:
        run.font.color.rgb = PRIMARY
        run.font.name = "Segoe UI"
    return h

def add_body(doc, text):
    p = doc.add_paragraph(text)
    p.paragraph_format.space_after = Pt(6)
    for run in p.runs:
        run.font.size = Pt(11)
        run.font.name = "Segoe UI"
        run.font.color.rgb = DARK_TEXT
    return p

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

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

# ── Header with logo ──
header = doc.sections[0].header
header.is_linked_to_previous = False
hp = header.paragraphs[0]
hp.alignment = WD_ALIGN_PARAGRAPH.RIGHT
run = hp.add_run()
run.add_picture(LOGO, width=Cm(2.0), height=Cm(1.8))

# ── Footer ──
footer = doc.sections[0].footer
footer.is_linked_to_previous = False
fp = footer.paragraphs[0]
fp.alignment = WD_ALIGN_PARAGRAPH.CENTER
run = fp.add_run("JvG Consultancy | Safety • Governance • Advisory | 2026")
run.font.size = Pt(8)
run.font.color.rgb = WHITE
set_cell_shading = lambda c, ch: None  # no-op for footer

# ── Title Page ──
doc.add_paragraph()  # spacer
p = doc.add_paragraph()
p.alignment = WD_ALIGN_PARAGRAPH.CENTER
run = p.add_run("FINANCIEEL PLAN")
run.bold = True
run.font.size = Pt(36)
run.font.color.rgb = PRIMARY
run.font.name = "Segoe UI"

p = doc.add_paragraph()
p.alignment = WD_ALIGN_PARAGRAPH.CENTER
run = p.add_run("HSEQ Consultancy — Opstart & Groei (2027–2029)")
run.font.size = Pt(18)
run.font.color.rgb = SECONDARY
run.font.name = "Segoe UI"

doc.add_paragraph()

# Metadata table on title page
meta_data = [
    ("Project", "JvG Consultancy — HSEQ Business Plan"),
    ("Type", "Financieel Plan (Fase 3)"),
    ("Auteur", "Business Analyst (OR_03) — Kas Taskforce"),
    ("Opdrachtgever", "Director (Jorick van Gemert)"),
    ("Versie", "1.0"),
    ("Datum", "15 april 2026"),
    ("Status", "Concept"),
    ("Classificatie", "Vertrouwelijk"),
]

meta_table = doc.add_table(rows=len(meta_data), cols=2)
meta_table.style = 'Table Grid'
meta_table.alignment = WD_TABLE_ALIGNMENT.CENTER
for i, (k, v) in enumerate(meta_data):
    set_cell_shading(meta_table.rows[i].cells[0], HEADER_BG)
    set_cell_text(meta_table.rows[i].cells[0], k, bold=True, color=WHITE, size=10)
    set_cell_text(meta_table.rows[i].cells[1], v, size=10)
    meta_table.rows[i].cells[0].width = Cm(4)
    meta_table.rows[i].cells[1].width = Cm(12)

doc.add_page_break()

# ── Disclaimer ──
add_heading_styled(doc, "Disclaimer", 2)
add_body(doc, "Dit document is een strategisch financieel plan en dient uitsluitend als interne richtlijn voor de opstart van JvG Consultancy. De berekeningen zijn gebaseerd op publiek beschikbare markttarieven, fiscale regelgeving (belastingplan 2026/2027) en conservatieve aannames. Dit document vormt geen financieel, fiscaal of juridisch advies. Raadpleeg een erkend fiscalist of accountant voor beslissingen met fiscale of juridische consequenties.")

# ══════════════════════════════════════════════════════════════
# 1. PRIJSSTRATEGIE
# ══════════════════════════════════════════════════════════════
add_heading_styled(doc, "1. Prijsstrategie", 1)
add_body(doc, "De prijsstrategie is gebaseerd op marktonderzoek (Fase 1) en positioneert JvG Consultancy in het mid-to-premium segment. HSEQ niche-consultancy in de olie-, gas- en petrochemische sector rechtvaardigt een premie ten opzichte van algemene QHSE-diensten wegens de vereiste technische diepgang, certficeringen en branchekennis.")

add_heading_styled(doc, "1.1 Tariefstructuur per Diensttype", 2)
styled_table(doc,
    ["Diensttype", "Uurtarief", "Dagtarief (8u)", "Opmerking"],
    [
        ["Interim HSEQ Manager", "€95–€140", "€760–€1.120", "Lange termijn opdrachten (3–12 mnd)"],
        ["HSEQ Audit & Compliance", "€120–€170", "€960–€1.360", "Projectmatig, scope-gebonden"],
        ["Training & Workshop", "€130–€170", "€1.040–€1.360", "Groepstraining (excl. materiaal)"],
        ["Strategisch Advies", "€140–€170", "€1.120–€1.360", "Directie-advisering, rapportages"],
        ["Projectmanagement HSEQ", "€110–€150", "€880–€1.200", "Implementatietrajecten"],
    ],
    [4, 3, 3.5, 6]
)

add_heading_styled(doc, "1.2 Staffelprijzen (Groeipad)", 2)
styled_table(doc,
    ["Fase", "Periode", "Interim (dag)", "Audit (dag)", "Advies (dag)"],
    [
        ["Starttarief", "0–12 mnd", "€850", "€960", "€1.040"],
        ["Geestablished", "12–24 mnd", "€950", "€1.100", "€1.200"],
        ["Premium", "24+ mnd", "€1.100", "€1.250", "€1.350"],
    ],
    [3, 2.5, 3, 3, 3]
)

add_heading_styled(doc, "1.3 Concurrentiepositie", 2)
styled_table(doc,
    ["Segment", "Dagtarief", "Voorbeeld", "Positie JvG"],
    [
        ["Low-end", "€600–€800", "Algemene veiligheidsadviseurs", "Boven — niche-premie"],
        ["Midden", "€800–€1.100", "Regionale HSEQ consultants", "In lijn — concurrerend"],
        ["Premium", "€1.100–€1.500", "Big-4 / internationale bureaus", "Onder — waardepropositie"],
    ],
    [3, 3, 5, 5]
)

add_heading_styled(doc, "1.4 Offshore Premie & Minimum Contractwaarden", 2)
add_body(doc, "Offshore opdrachten dragen een premie van +15–25% ten opzichte van onshore, wegens logistieke complexiteit, verblijfskosten en vereiste veiligheidscertificeringen (OPITO, BOSIET). Minimum contractwaarde: €5.000 per opdracht. Interim-contracten: minimum 3 maanden.")

add_heading_styled(doc, "1.5 Advies: Starttarief vs Groeipad", 2)
add_body(doc, "Advies: start met het midden van de range (€850/dag interim) om referenties op te bouwen. Verhoog naar €950–€1.100 na 12 maanden op basis van bewezen trackrecord. Passeer €1.000/dag alleen met minimaal 3 succesvolle projectreferenties. Offshore altijd premie tarief (+20%).")

# ══════════════════════════════════════════════════════════════
# 2. KOSTENSTRUCTUUR
# ══════════════════════════════════════════════════════════════
add_heading_styled(doc, "2. Kostenstructuur", 1)

add_heading_styled(doc, "2.1 Startup Kosten (Eenmalig)", 2)
styled_table(doc,
    ["Categorie", "Bedrag", "Opmerking"],
    [
        ["KvK inschrijving", "€75", "Eenmanszaak"],
        ["Website (ontwerp + hosting jaar 1)", "€2.500", "Professionele WordPress-site"],
        ["Branding (logo, huisstijl)", "€1.500", "Reeds beschikbaar via JvG"],
        ["Laptop (business)", "€2.000", "MacBook Pro / equivalente business laptop"],
        ["Certificeringen (NEBOSH, IVVK)", "€3.500", "Her-/vercertificering"],
        ["VCA VOL (indien niet geldig)", "€350", "Verplicht voor industrie"],
        ["Juridisch (AV, algemene voorwaarden)", "€800", "Via juridisch specialist"],
        ["Bedrijfsrekening", "€100", "Eenmalig setup"],
        ["Overig (kantoormateriaal, etc.)", "€500", "Diversen"],
        ["TOTAAL STARTUP", "€11.325", "—"],
    ],
    [5, 3, 8]
)

add_heading_styled(doc, "2.2 Vaste Lasten (Maandelijks)", 2)
styled_table(doc,
    ["Categorie", "Bedrag/maand", "Jaartotaal"],
    [
        ["BA-verzekering (beroepsaansprakelijkheid)", "€175", "€2.100"],
        ["AOV (arbeidsongeschiktheid)", "€200", "€2.400"],
        ["Accounting / boekhouder", "€250", "€3.000"],
        ["Software (Office 365, Adobe, CRM)", "€80", "€960"],
        ["Auto/transport (lease of KM-vergoeding)", "€350", "€4.200"],
        ["Telefoon (abonnement)", "€40", "€480"],
        ["Internet (zakelijk deel)", "€40", "€480"],
        ["Website hosting & onderhoud", "€25", "€300"],
        ["Lidmaatschappen (VHK, etc.)", "€35", "€420"],
        ["TOTAAL VAST", "€1.195", "€14.340"],
    ],
    [5, 3, 3]
)

add_heading_styled(doc, "2.3 Variabele Kosten (Jaarlijks)", 2)
styled_table(doc,
    ["Categorie", "Geschat Jaarlijks", "Opmerking"],
    [
        ["Reiskosten (niet-declarabel)", "€1.500", "Resterend na klantdeclaratie"],
        ["Representatie", "€1.200", "Max. €4.800 aftrekbaar (100%)"],
        ["Opleidingen & bijscholing", "€2.000", "CPD, conferenties, cursussen"],
        ["Marketing (LinkedIn, netwerken)", "€800", "Digitale aanwezigheid"],
        ["Overige variabelen", "€500", "Diversen"],
        ["TOTAAL VARIABEL", "€6.000", "—"],
    ],
    [5, 3, 8]
)

add_heading_styled(doc, "2.4 Kostenoverzicht Samenvatting", 2)
styled_table(doc,
    ["Type", "Jaar 1", "Jaar 2", "Jaar 3"],
    [
        ["Startup (eenmalig)", "€11.325", "—", "—"],
        ["Vaste lasten", "€14.340", "€14.890", "€15.350"],
        ["Variabele kosten", "€6.000", "€6.500", "€7.000"],
        ["TOTAAL KOSTEN", "€31.665", "€21.390", "€22.350"],
    ],
    [5, 3, 3, 3]
)

# ══════════════════════════════════════════════════════════════
# 3. AFTREKPOSTEN BIJ START
# ══════════════════════════════════════════════════════════════
add_heading_styled(doc, "3. Fiscale Aftrekposten bij Start", 1)
add_body(doc, "De volgende aftrekposten zijn van toepassing op een eenmanszaak in Nederland (belastingjaar 2027, belastingplan 2026). Bedragen zijn indicatief en gebaseerd op huidige wetgeving.")

styled_table(doc,
    ["Aftrekpost", "Bedrag", "Voorwaarden", "Periode"],
    [
        ["Zelfstandigenaftrek", "€7.500", "Voldoet aan urencriterium (>1.225 u/j)", "Elk jaar als ZZP"],
        ["Startersaftrek", "€2.123", "Eerste 3 jaar na start (uitsluitend)", "Jaar 1–3"],
        ["MKB-winstvrijstelling", "19%", "Over eerste €200.000 winst", "Elk jaar"],
        ["Afschrijving laptop (5 jr)", "€400/j", "Lineaire afschrijving", "Jaar 1–5"],
        ["Afschrijving apparatuur", "€200/j", "Overig materieel", "Jaar 1–5"],
        ["Thuiskantoor (ruimte-eis)", "~€1.500/j", "Min. 10% van woning, exclusief gebruik", "Elk jaar"],
        ["Internet/telefoon (zakelijk deel)", "~€960/j", "50–75% zakelijk gebruik", "Elk jaar"],
        ["Reiskosten (eigen auto)", "€0,23/km", "Onbelaste kilometervergoeding", "Elk jaar"],
        ["Opleidingskosten", "Volledig", "Gerelateerd aan beroep", "Elk jaar"],
        ["Verzekeringen (BA, AOV)", "Volledig", "Zakelijk verplicht", "Elk jaar"],
        ["Accounting", "Volledig", "Zakelijk verplicht", "Elk jaar"],
        ["Representatie", "Max €4.800", "100% aftrekbaar tot plafond", "Elk jaar"],
    ],
    [4, 2.5, 5, 2.5]
)

add_body(doc, "Toelichting: De Energie-investeringsaftrek (EIA) en MIA/Vamil zijn niet direct van toepassing op een puur consultancy-praktijk zonder materiële investeringen in energiezuinige assets. Bij eventuele investering in een elektrische bedrijfsauto kan MIA wel relevant worden (tot 36% extra aftrek).")

# ══════════════════════════════════════════════════════════════
# 4. OMZETPROGNOSE
# ══════════════════════════════════════════════════════════════
add_heading_styled(doc, "4. Omzetprognose (3 Jaar)", 1)

add_heading_styled(doc, "4.1 Jaar 1 (2027) — Opstartfase", 2)
styled_table(doc,
    ["Scenario", "Fact. dagen", "Gem. dagtarief", "Omzet (excl. BTW)", "BTW (21%)"],
    [
        ["Conservatief", "100", "€850", "€85.000", "€17.850"],
        ["Normaal", "140", "€900", "€126.000", "€26.460"],
        ["Optimistisch", "170", "€950", "€161.500", "€33.915"],
    ],
    [3, 3, 3, 3, 3]
)

add_heading_styled(doc, "4.2 Jaar 2 (2028) — Groeifase", 2)
styled_table(doc,
    ["Scenario", "Fact. dagen", "Gem. dagtarief", "Omzet (excl. BTW)", "BTW (21%)"],
    [
        ["Conservatief", "140", "€950", "€133.000", "€27.930"],
        ["Normaal", "170", "€1.000", "€170.000", "€35.700"],
        ["Optimistisch", "200", "€1.050", "€210.000", "€44.100"],
    ],
    [3, 3, 3, 3, 3]
)

add_heading_styled(doc, "4.3 Jaar 3 (2029) — Geestablished", 2)
styled_table(doc,
    ["Scenario", "Fact. dagen", "Gem. dagtarief", "Omzet (excl. BTW)", "BTW (21%)"],
    [
        ["Conservatief", "170", "€1.000", "€170.000", "€35.700"],
        ["Normaal", "200", "€1.100", "€220.000", "€46.200"],
        ["Optimistisch", "220", "€1.200", "€264.000", "€55.440"],
    ],
    [3, 3, 3, 3, 3]
)

add_body(doc, "Aanname: 220 werkbare dagen per jaar (na vakanties, feestdagen, ziekte). Conservatief scenario hanteert 45–77% bezettingsgraad, normaal 64–91%, optimistisch 77–100%. Realistisch verwachte bezetting jaar 1: 60–70% (120–150 dagen).")

# ══════════════════════════════════════════════════════════════
# 5. WINST- EN VERLIESREKENING
# ══════════════════════════════════════════════════════════════
add_heading_styled(doc, "5. Winst- en Verliesrekening (3 Jaar)", 1)
add_body(doc, "Uitgaande van het normaal scenario.")

styled_table(doc,
    ["Post", "Jaar 1 (2027)", "Jaar 2 (2028)", "Jaar 3 (2029)"],
    [
        ["Omzet (excl. BTW)", "€126.000", "€170.000", "€220.000"],
        ["Vaste lasten", "–€14.340", "–€14.890", "–€15.350"],
        ["Variabele kosten", "–€6.000", "–€6.500", "–€7.000"],
        ["Startup (eenmalig)", "–€11.325", "—", "—"],
        ["Brutowinst", "€94.335", "€148.610", "€197.650"],
        ["", "", "", ""],
        ["Fiscale aftrek:", "", "", ""],
        ["  Zelfstandigenaftrek", "€7.500", "€7.500", "€7.500"],
        ["  Startersaftrek", "€2.123", "€2.123", "€2.123"],
        ["  Afschrijvingen", "€600", "€600", "€600"],
        ["  Thuiskantoor + telecom", "€2.460", "€2.460", "€2.460"],
        ["Totaal fiscale aftrek", "€12.683", "€12.683", "€12.683"],
        ["", "", "", ""],
        ["Belastbare winst", "€81.652", "€135.927", "€184.967"],
        ["  – MKB-winstvrijstelling (19%)", "€15.514", "€25.826", "€35.144"],
        ["Fiscale winst", "€66.138", "€110.101", "€149.823"],
        ["", "", "", ""],
        ["Inkomstenbelasting (schijf 1+2)", "~€21.960", "~€40.840", "~€56.930"],
        ["  (indicatief, 36,97%–49,50%)", "", "", ""],
        ["Nettowinst (na belasting)", "€72.375", "€107.770", "€140.720"],
        ["Operationele marge", "57,4%", "63,4%", "64,0%"],
    ],
    [5, 3.5, 3.5, 3.5]
)

add_body(doc, "Toelichting: De inkomstenbelasting is berekend op basis van de progressieve schijven voor 2027 (indicatief: schijf 1 tot €75.518 op 36,97%, schijf 2 daarboven op 49,50%). Exacte belastingdruk verschilt per persoonlijke situatie (toeslagen, partnerinkomen, etc.). De MKB-winstvrijstelling (19%) wordt toegepast op de winst vóór aftrek van de zelfstandigen- en startersaftrek.")

# ══════════════════════════════════════════════════════════════
# 6. CASHFLOW PROGNOSE
# ══════════════════════════════════════════════════════════════
add_heading_styled(doc, "6. Cashflow Prognose (3 Jaar)", 1)
add_body(doc, "Uitgaande van het normaal scenario. Betalingstermijn klanten: 14–30 dagen. BTW-aangifte: per kwartaal. Inkomstenbelasting: voorlopige aanslag, corrigerend na aangifte.")

add_heading_styled(doc, "6.1 Jaar 1 — Kwartaaloverzicht", 2)
styled_table(doc,
    ["Post", "Q1", "Q2", "Q3", "Q4", "Jaartotaal"],
    [
        ["Inkomsten (facturatie)", "€15.750", "€31.500", "€31.500", "€47.250", "€126.000"],
        ["Uitgaven vast", "–€3.585", "–€3.585", "–€3.585", "–€3.585", "–€14.340"],
        ["Uitgaven variabel", "–€1.500", "–€1.500", "–€1.500", "–€1.500", "–€6.000"],
        ["Startup investering", "–€11.325", "—", "—", "—", "–€11.325"],
        ["BTW betaling (netto)", "–€500", "€750", "€750", "€1.000", "€2.000"],
        ["Netto kasstroom", "–€1.160", "€27.165", "€27.165", "€43.165", "€96.335"],
        ["Cumulatieve kas", "–€1.160", "€26.005", "€53.170", "€96.335", "—"],
    ],
    [3.5, 2.5, 2.5, 2.5, 2.5, 2.5]
)

add_body(doc, "Kasbuffer vereist: minimaal €15.000 bij start (3 maanden vaste lasten + startup kosten). Advies: €20.000–€25.000 voor comfortabele buffer. Q1 is negatief door investeringskosten en opstartvertraging in facturatie.")

add_heading_styled(doc, "6.2 Break-even Timing", 2)
add_body(doc, "Break-even wordt bereikt in maand 7–8 van jaar 1 (normaal scenario). Vanaf dat moment overtreffen maandelijkse inkomsten de maandelijkse kosten. De cumulatieve kaspositie is positief vanaf Q2.")

# ══════════════════════════════════════════════════════════════
# 7. BREAK-EVEN ANALYSE
# ══════════════════════════════════════════════════════════════
add_heading_styled(doc, "7. Break-even Analyse", 1)

styled_table(doc,
    ["Parameter", "Waarde", "Berekening"],
    [
        ["Maandelijkse vaste lasten", "€1.195", "Excl. variabele kosten"],
        ["Variabele kosten per dag", "€43", "€6.000 ÷ 140 dagen"],
        ["Totale kosten per dag", "€1.238", "Vast + variabel per fact. dag"],
        ["Break-even dagen/maand", "1,5 dagen", "€1.238 ÷ €850 (starttarief)"],
        ["Break-even dagen/jaar", "18 dagen", "€14.340 ÷ €850 × 140 ÷ 12"],
        ["Min. dagtarief bij 10 dgn/maand", "€124", "€1.238 × 10 ÷ 100 (deelse facturatie)"],
        ["Min. dagtarief bij 8 dgn/maand", "€155", "€1.238 × 8 ÷ ..."],
    ],
    [4, 2.5, 8]
)

add_body(doc, "Conclusie: break-even is uiterst bereikbaar. Bij een starttarief van €850/dag zijn slechts 1,5 factureerbare dagen per maand nodig om de vaste lasten te dekken. Dit biedt een aanzienlijke veiligheidsmarge. Zelfs bij een bezettingsgraad van 20% (44 dagen/jaar) resteert een nettowinst van ~€26.000.")

# ══════════════════════════════════════════════════════════════
# 8. FINANCIËLE RISICO'S & BUFFERS
# ══════════════════════════════════════════════════════════════
add_heading_styled(doc, "8. Financiële Risico's & Buffers", 1)

styled_table(doc,
    ["Risico", "Impact", "Waarschijnlijkheid", "Mitigatie"],
    [
        ["Seizoensfluctuaties", "Middel", "Middel", "Buffer: €20K; Q2/Q3 marketing push"],
        ["Klantconcentratie", "Hoog", "Middel", "Max 40% omzet per klant; actief pipelining"],
        ["Ziekte-uitval", "Hoog", "Laag", "AOV-verzekering (€200/mnd); buffer 3 mnd"],
        ["Late betalingen", "Middel", "Middel", "Betalingstermijn 14 dagen; follow-up protocol"],
        ["Tariefdruk (concurrentie)", "Middel", "Laag", "Niche positionering; kwaliteit > prijs"],
        ["Wetgeving/Fiscale wijzigingen", "Middel", "Laag", "Accountant raadplegen; buffer in prognose"],
        ["Pensioenopbouw", "Lang. termijn", "Zeker", "Lijfrenterekening: €500/mnd vanaf jaar 2"],
    ],
    [3.5, 2, 2.5, 8]
)

add_heading_styled(doc, "8.1 Aanbevolen Kasbuffer", 2)
add_body(doc, "Minimaal 3 maanden vaste lasten: €3.585. Advies: 6 maanden vast + variabel = €10.500. Inclusief startup: €20.000–€25.000 startkapitaal.")

add_heading_styled(doc, "8.2 Pensioenopbouw", 2)
add_body(doc, "Als ZZP'er bouw je geen werkgeverspensioen op. Aanbevolen: netto €300–€500/maand storten in een lijfrenterekening (fiscaal aftrekbaar via jaarruimte). Vanaf jaar 2: structureel inrichten. Jaarruimte 2027 (indicatief): tot €13.665 fiscaal aftrekbaar (afhankelijk van inkomen).")

# ══════════════════════════════════════════════════════════════
# 9. FINANCIËLE ROADMAP
# ══════════════════════════════════════════════════════════════
add_heading_styled(doc, "9. Financiële Roadmap", 1)

styled_table(doc,
    ["Periode", "Mijlpaal", "Acties", "Budget"],
    [
        ["Start (mnd 0)", "Inschrijving & setup", "KvK, bankrekening, verzekeringen, website", "€11.325"],
        ["Maand 1–3", "Investeringsfase", "Netwerken, eerste leads, certificeringen afronden", "€5.000 (operationeel)"],
        ["Maand 4–6", "Eerste omzet", "1–2 interim-contracten, eerste facturen", "Break-even Q2"],
        ["Maand 7–12", "Break-even", "Stabiele facturatie, 120–150 dagen/jaar", "Cashflow positief"],
        ["Jaar 2", "Groei & consolidatie", "Tariefverhoging, 170+ dagen, referentieportfolio", "€170K omzet"],
        ["Jaar 3", "BV-conversie + schalen", "BV oprichten bij winst >€100K, DGA-salaris", "€220K omzet"],
    ],
    [2.5, 3, 6, 3.5]
)

add_body(doc, "BV-conversie advies: converteer naar BV wanneer de fiscale besparing (Box 1 vs Box 2 tarief) de extra administratieve lasten (€2.000–€5.000/jaar) overstijgt. Indicatief break-even punt BV-conversie: winst >€80.000–€100.000. DGA-salaris: minimaal €56.000/jaar (wettelijk vereist vanaf 2024).")

# ── Save DOCX ──
docx_path = f"{DEL_DOCX}/financieel-plan_v1.0.docx"
doc.save(docx_path)
print(f"✅ DOCX saved: {docx_path}")

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

# Styles
hdr_font = Font(name="Segoe UI", size=11, bold=True, color="FFFFFF")
hdr_fill = PatternFill(start_color=HEADER_BG, end_color=HEADER_BG, fill_type="solid")
body_font = Font(name="Segoe UI", size=10)
body_font_bold = Font(name="Segoe UI", size=10, bold=True)
money_fmt = '€#,##0'
pct_fmt = '0.0%'
alt_fill = PatternFill(start_color=ALT_ROW, end_color=ALT_ROW, fill_type="solid")
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')
)
center_align = Alignment(horizontal='center', vertical='center')
left_align = Alignment(horizontal='left', vertical='center')

def style_header(ws, row, cols):
    for c in range(1, cols + 1):
        cell = ws.cell(row=row, column=c)
        cell.font = hdr_font
        cell.fill = hdr_fill
        cell.alignment = center_align
        cell.border = thin_border

def style_body(ws, row, cols, alt=False):
    for c in range(1, cols + 1):
        cell = ws.cell(row=row, column=c)
        cell.font = body_font
        cell.border = thin_border
        if alt:
            cell.fill = alt_fill

# ── Sheet 1: Aannames ──
ws1 = wb.active
ws1.title = "Aannames"
ws1.sheet_properties.tabColor = "003366"

assumptions = [
    ["Parameter", "Waarde", "Eenheid", "Bron/Opmerking"],
    ["Starttarief interim (dag)", 850, "EUR/dag", "Marktanalyse Fase 1"],
    ["Starttarief audit (dag)", 960, "EUR/dag", "Marktanalyse Fase 1"],
    ["Starttarief advies (dag)", 1040, "EUR/dag", "Marktanalyse Fase 1"],
    ["Premium interim (dag, jaar 3)", 1100, "EUR/dag", "Staffel model"],
    ["Premium audit (dag, jaar 3)", 1250, "EUR/dag", "Staffel model"],
    ["Premium advies (dag, jaar 3)", 1350, "EUR/dag", "Staffel model"],
    ["Offshore premie", 0.20, "%", "Branche standaard"],
    ["Min. contractwaarde", 5000, "EUR", "Beleid"],
    ["Werkbare dagen/jaar", 220, "dagen", "Na vakantie/feestdagen"],
    ["Jaar 1 fact. dagen (normaal)", 140, "dagen", "64% bezetting"],
    ["Jaar 2 fact. dagen (normaal)", 170, "dagen", "77% bezetting"],
    ["Jaar 3 fact. dagen (normaal)", 200, "dagen", "91% bezetting"],
    ["Vaste lasten/maand", 1195, "EUR", "Zie Startbudget tab"],
    ["Variabele kosten/jaar", 6000, "EUR", "Jaar 1 basis"],
    ["Startup kosten", 11325, "EUR", "Eenmalig"],
    ["BTW tarief", 0.21, "%", "2026 standaard"],
    ["Zelfstandigenaftrek", 7500, "EUR", "Belastingplan 2026"],
    ["Startersaftrek", 2123, "EUR", "Eerste 3 jaar"],
    ["MKB-winstvrijstelling", 0.19, "%", "Tot €200K winst"],
    ["Afschrijvingen/jaar", 600, "EUR", "Laptop + apparatuur"],
    ["Thuiskantoor aftrek/jaar", 1500, "EUR", "Indicatief"],
    ["AOV premie/maand", 200, "EUR", "Indicatief"],
    ["AOV uitkering/maand", 1500, "EUR", "Indicatief (bij ziekte)"],
    ["Pensioen bijdrage/maand", 400, "EUR", "Vanaf jaar 2"],
    ["Kasbuffer (advies)", 20000, "EUR", "6 mnd vast + variabel + startup"],
]

for r, row in enumerate(assumptions, 1):
    for c, val in enumerate(row, 1):
        ws1.cell(row=r, column=c, value=val)
    if r == 1:
        style_header(ws1, r, 4)
    else:
        style_body(ws1, r, 4, alt=(r % 2 == 0))
        if c == 2 and isinstance(val, (int, float)):
            ws1.cell(row=r, column=2).number_format = money_fmt if val > 10 else pct_fmt

ws1.column_dimensions['A'].width = 35
ws1.column_dimensions['B'].width = 15
ws1.column_dimensions['C'].width = 12
ws1.column_dimensions['D'].width = 30

# ── Sheet 2: P&L 3-jaar ──
ws2 = wb.create_sheet("P&L 3-jaar")
ws2.sheet_properties.tabColor = "003366"

pl_data = [
    ["Winst- en Verliesrekening", "Jaar 1 (2027)", "Jaar 2 (2028)", "Jaar 3 (2029)"],
    ["", "", "", ""],
    ["OMZET", "", "", ""],
    ["Omzet (excl. BTW)", 126000, 170000, 220000],
    ["", "", "", ""],
    ["KOSTEN", "", "", ""],
    ["Vaste lasten", -14340, -14890, -15350],
    ["Variabele kosten", -6000, -6500, -7000],
    ["Startup (eenmalig)", -11325, 0, 0],
    ["Totaal kosten", -31665, -21390, -22350],
    ["", "", "", ""],
    ["BRUTOWINST", 94335, 148610, 197650],
    ["Operationele marge", "", "", ""],
    ["", "", "", ""],
    ["FISCALE AFTREK", "", "", ""],
    ["Zelfstandigenaftrek", 7500, 7500, 7500],
    ["Startersaftrek", 2123, 2123, 2123],
    ["Afschrijvingen", 600, 600, 600],
    ["Thuiskantoor + telecom", 2460, 2460, 2460],
    ["Totaal fiscale aftrek", 12683, 12683, 12683],
    ["", "", "", ""],
    ["Belastbare winst", 81652, 135927, 184967],
    ["MKB-winstvrijstelling (19%)", -15514, -25826, -35144],
    ["Fiscale winst", 66138, 110101, 149823],
    ["IB (indicatief)", -21960, -40840, -56930],
    ["", "", "", ""],
    ["NETTOWINST", 72375, 107770, 140720],
]

for r, row in enumerate(pl_data, 1):
    for c, val in enumerate(row, 1):
        ws2.cell(row=r, column=c, value=val)
    if r == 1:
        style_header(ws2, r, 4)
    else:
        style_body(ws2, r, 4, alt=(r % 2 == 0))
        if isinstance(val, (int, float)) and val != 0:
            ws2.cell(row=r, column=c).number_format = money_fmt

# Bold section headers
for r in [3, 6, 11, 14, 20, 27]:
    for c in range(1, 5):
        ws2.cell(row=r, column=c).font = body_font_bold

# Add margin formulas
ws2.cell(row=12, column=2, value="=B11/B4")
ws2.cell(row=12, column=2).number_format = pct_fmt
ws2.cell(row=12, column=3, value="=C11/C4")
ws2.cell(row=12, column=3).number_format = pct_fmt
ws2.cell(row=12, column=4, value="=D11/D4")
ws2.cell(row=12, column=4).number_format = pct_fmt

ws2.column_dimensions['A'].width = 30
ws2.column_dimensions['B'].width = 18
ws2.column_dimensions['C'].width = 18
ws2.column_dimensions['D'].width = 18

# ── Sheet 3: Cashflow 3-jaar ──
ws3 = wb.create_sheet("Cashflow 3-jaar")
ws3.sheet_properties.tabColor = "003366"

cf_headers = ["Post", "J1 Q1", "J1 Q2", "J1 Q3", "J1 Q4", "J1 Totaal", "J2 Q1", "J2 Q2", "J2 Q3", "J2 Q4", "J2 Totaal", "J3 Q1", "J3 Q2", "J3 Q3", "J3 Q4", "J3 Totaal"]
cf_data = [
    ["Inkomsten", 15750, 31500, 31500, 47250, None, 42500, 42500, 42500, 42500, None, 55000, 55000, 55000, 55000, None],
    ["Uitgaven vast", -3585, -3585, -3585, -3585, None, -3723, -3723, -3723, -3723, None, -3838, -3838, -3838, -3838, None],
    ["Uitgaven variabel", -1500, -1500, -1500, -1500, None, -1625, -1625, -1625, -1625, None, -1750, -1750, -1750, -1750, None],
    ["Startup", -11325, 0, 0, 0, None, 0, 0, 0, 0, None, 0, 0, 0, 0, None],
    ["BTW (netto)", -500, 750, 750, 1000, None, 800, 800, 800, 800, None, 1000, 1000, 1000, 1000, None],
    ["Netto kasstroom", None, None, None, None, None, None, None, None, None, None, None, None, None, None, None],
    ["Cumulatief", None, None, None, None, None, None, None, None, None, None, None, None, None, None, None],
]

for c, h in enumerate(cf_headers, 1):
    ws3.cell(row=1, column=c, value=h)
style_header(ws3, 1, len(cf_headers))

for r, row in enumerate(cf_data, 2):
    for c, val in enumerate(row, 1):
        ws3.cell(row=r, column=c, value=val)
    style_body(ws3, r, len(cf_headers), alt=(r % 2 == 0))
    if isinstance(row[0], str):
        ws3.cell(row=r, column=1).font = body_font_bold

# Netto kasstroom formulas (row 7): sum of rows 2-6 per column
for c in range(2, 17):
    col_l = get_column_letter(c)
    ws3.cell(row=7, column=c, value=f"=SUM({col_l}2:{col_l}6)")
    ws3.cell(row=7, column=c).number_format = money_fmt

# Jaartotaal formulas (col 6, 11, 16)
for r in range(2, 8):
    ws3.cell(row=r, column=6, value=f"=SUM(B{r}:E{r})")
    ws3.cell(row=r, column=6).number_format = money_fmt
    ws3.cell(row=r, column=11, value=f"=SUM(G{r}:J{r})")
    ws3.cell(row=r, column=11).number_format = money_fmt
    ws3.cell(row=r, column=16, value=f"=SUM(L{r}:O{r})")
    ws3.cell(row=r, column=16).number_format = money_fmt

# Cumulatief (row 8): B8 = B7, C8 = B8 + C7, etc.
ws3.cell(row=8, column=2, value="=B7")
ws3.cell(row=8, column=2).number_format = money_fmt
for c in range(3, 17):
    prev = get_column_letter(c - 1)
    cur = get_column_letter(c)
    ws3.cell(row=8, column=c, value=f"={prev}8+{cur}7")
    ws3.cell(row=8, column=c).number_format = money_fmt

# Jaartotaal for cumulatief
ws3.cell(row=8, column=6, value="=E8")
ws3.cell(row=8, column=6).number_format = money_fmt
ws3.cell(row=8, column=11, value="=J8")
ws3.cell(row=8, column=11).number_format = money_fmt
ws3.cell(row=8, column=16, value="=O8")
ws3.cell(row=8, column=16).number_format = money_fmt

ws3.column_dimensions['A'].width = 22
for c in range(2, 17):
    ws3.column_dimensions[get_column_letter(c)].width = 12

# ── Sheet 4: Break-even ──
ws4 = wb.create_sheet("Break-even")
ws4.sheet_properties.tabColor = "003366"

be_data = [
    ["Break-even Analyse", "", ""],
    ["", "", ""],
    ["Parameter", "Waarde", "Berekening"],
    ["Maandelijkse vaste lasten", 1195, "Verzekeringen + accounting + software + auto + telecom + internet + lidmaatschappen"],
    ["Variabele kosten per fact. dag", 43, "€6.000 ÷ 140 dagen"],
    ["Totale kosten per dag", None, "=B4+B5"],
    ["Starttarief (dag)", 850, "Fase 1 marktanalyse"],
    ["Break-even dagen/maand", None, "=ROUNDUP(B6/B7,1)"],
    ["Break-even dagen/jaar", None, "=ROUNDUP(14340/850,0)"],
    ["Min. dagtarief bij 8 dgn/maand", None, "=ROUNDUP((B4+B5*8)/8,0)"],
    ["Min. dagtarief bij 10 dgn/maand", None, "=ROUNDUP((B4+B5*10)/10,0)"],
    ["Min. dagtarief bij 12 dgn/maand", None, "=ROUNDUP((B4+B5*12)/12,0)"],
    ["", "", ""],
    ["Scenario Analyse", "", ""],
    ["Scenario", "Omzet", "Winst na kosten"],
    ["60 dagen/jaar @ €850", None, "=B16*850-14340-6000"],
    ["100 dagen/jaar @ €900", None, "=B17*900-14340-6000"],
    ["140 dagen/jaar @ €900", None, "=B18*900-14340-6000"],
    ["170 dagen/jaar @ €1000", None, "=B19*1000-14340-6000"],
    ["200 dagen/jaar @ €1100", None, "=B20*1100-14340-6000"],
    ["220 dagen/jaar @ €1100", None, "=B21*1100-14340-6000"],
]

for r, row in enumerate(be_data, 1):
    for c, val in enumerate(row, 1):
        ws4.cell(row=r, column=c, value=val)
    if r in [1, 3, 14, 15]:
        style_header(ws4, r, 3)
    elif r > 1:
        style_body(ws4, r, 3, alt=(r % 2 == 0))

# Fill scenario column A
for r in range(16, 22):
    ws4.cell(row=r, column=1).font = body_font
    ws4.cell(row=r, column=1).border = thin_border

# Format money
for r in [4, 5, 7]:
    ws4.cell(row=r, column=2).number_format = money_fmt
for r in range(16, 22):
    for c in [2, 3]:
        ws4.cell(row=r, column=c).number_format = money_fmt

ws4.column_dimensions['A'].width = 35
ws4.column_dimensions['B'].width = 18
ws4.column_dimensions['C'].width = 50

# ── Sheet 5: Startbudget ──
ws5 = wb.create_sheet("Startbudget")
ws5.sheet_properties.tabColor = "003366"

sb_data = [
    ["Startbudget Overzicht", "", "", ""],
    ["", "", "", ""],
    ["EENMALIGE STARTUP KOSTEN", "Bedrag", "Categorie", "Prioriteit"],
    ["KvK inschrijving", 75, "Administratief", "Verplicht"],
    ["Website (ontwerp + hosting jaar 1)", 2500, "Marketing", "Hoog"],
    ["Branding (logo, huisstijl)", 1500, "Branding", "Gemiddeld"],
    ["Laptop (business)", 2000, "Apparatuur", "Hoog"],
    ["Certificeringen (NEBOSH, IVVK)", 3500, "Professioneel", "Hoog"],
    ["VCA VOL", 350, "Certificering", "Hoog"],
    ["Juridisch (AV, voorwaarden)", 800, "Juridisch", "Hoog"],
    ["Bedrijfsrekening setup", 100, "Administratief", "Verplicht"],
    ["Overig (kantoormateriaal)", 500, "Diversen", "Laag"],
    ["TOTAAL STARTUP", 11325, "", ""],
    ["", "", "", ""],
    ["MAANDELIJKSE VASTE LASTEN", "Bedrag/maand", "Jaartotaal", "Categorie"],
    ["BA-verzekering", 175, 2100, "Verzekering"],
    ["AOV", 200, 2400, "Verzekering"],
    ["Accounting", 250, 3000, "Administratief"],
    ["Software (Office, Adobe, CRM)", 80, 960, "Software"],
    ["Auto/transport", 350, 4200, "Transport"],
    ["Telefoon", 40, 480, "Telecom"],
    ["Internet (zakelijk deel)", 40, 480, "Telecom"],
    ["Website hosting", 25, 300, "Marketing"],
    ["Lidmaatschappen (VHK)", 35, 420, "Professioneel"],
    ["TOTAAL VAST", 1195, 14340, ""],
    ["", "", "", ""],
    ["VARIABELE KOSTEN (JAARLIJKS)", "Jaartotaal", "", ""],
    ["Reiskosten (niet-declarabel)", 1500, "", "Transport"],
    ["Representatie", 1200, "", "Relatiebeheer"],
    ["Opleidingen & bijscholing", 2000, "", "Professioneel"],
    ["Marketing (LinkedIn, netwerken)", 800, "", "Marketing"],
    ["Overige variabelen", 500, "", "Diversen"],
    ["TOTAAL VARIABEL", 6000, "", ""],
    ["", "", "", ""],
    ["TOTAAL INVESTERING JAAR 1", 31665, "", "Startup + Vast + Variabel"],
    ["AANBEVOLEN KASBUFFER", 20000, "", "6 mnd vast + variabel + startup"],
]

for r, row in enumerate(sb_data, 1):
    for c, val in enumerate(row, 1):
        ws5.cell(row=r, column=c, value=val)
    if r in [1, 3, 13, 23]:
        style_header(ws5, r, 4)
    elif r > 1:
        style_body(ws5, r, 4, alt=(r % 2 == 0))

# Bold totals
for r in [13, 22, 28, 30, 31]:
    for c in range(1, 5):
        ws5.cell(row=r, column=c).font = body_font_bold

# Format money columns
for r in range(4, 14):
    ws5.cell(row=r, column=2).number_format = money_fmt
for r in range(15, 23):
    ws5.cell(row=r, column=2).number_format = money_fmt
    ws5.cell(row=r, column=3).number_format = money_fmt
for r in range(24, 29):
    ws5.cell(row=r, column=2).number_format = money_fmt
ws5.cell(row=30, column=2).number_format = money_fmt
ws5.cell(row=31, column=2).number_format = money_fmt

ws5.column_dimensions['A'].width = 38
ws5.column_dimensions['B'].width = 18
ws5.column_dimensions['C'].width = 18
ws5.column_dimensions['D'].width = 18

# ── Save XLSX ──
xlsx_path = f"{DEL_XLSX}/financieel-model_v1.0.xlsx"
wb.save(xlsx_path)
print(f"✅ XLSX saved: {xlsx_path}")

# ══════════════════════════════════════════════════════════════
# CHANGELOG
# ══════════════════════════════════════════════════════════════
changelog_path = f"{LOG_DIR}/changelog.md"
entry = """## [1.0] — 2026-04-15

### Financieel Plan HSEQ Consultancy (Fase 3)
- **DOCX**: `deliverables/docx/financieel-plan_v1.0.docx` — 9 secties: prijsstrategie, kostenstructuur, fiscale aftrekposten, omzetprognose (3 scenario's × 3 jaar), P&L, cashflow, break-even, risico's, roadmap
- **XLSX**: `deliverables/xlsx/financieel-model_v1.0.xlsx` — 5 tabs: Aannames, P&L 3-jaar, Cashflow 3-jaar, Break-even, Startbudget. Formules op alle berekende velden.
- **Aannames**: Conservatief, normaal, optimistisch scenario. Starttarief €850/dag interim. Break-even bij 1,5 dagen/maand.
- **Agent**: Business Analyst (OR_03) — Kas Taskforce
"""

if os.path.exists(changelog_path):
    with open(changelog_path, 'r') as f:
        existing = f.read()
    with open(changelog_path, 'w') as f:
        f.write(entry + "\n---\n" + existing)
else:
    with open(changelog_path, 'w') as f:
        f.write("# Changelog — HSEQ Business Plan\n\n" + entry)

print(f"✅ Changelog updated: {changelog_path}")
print("\n🎯 FASE 3 VOLTOOID — Beide bestanden opgeleverd.")