#!/usr/bin/env python3
"""Dispo 2026 → Excel. Farben und Layout identisch mit dispo2026.html."""

from openpyxl import Workbook
from openpyxl.styles import (
    PatternFill, Font, Alignment, Border, Side, GradientFill
)
from openpyxl.utils import get_column_letter

wb = Workbook()
ws = wb.active
ws.title = "Drehplan Resilienz 2026"

# ── Farben ──────────────────────────────────────────────────────────────────
C_BLACK   = "1A1A1A"
C_WHITE   = "FFFFFF"
C_IT      = "8FAFC8"   # Stahlblau
C_HAFTUNG = "C8A87A"   # Terracotta
C_PERSONAL= "7AAE84"   # Salbei
C_SOME    = "E4E4E4"   # SoMe grau
C_OFFEN   = "EFEFEF"   # offen grau
C_PAUSE   = "F0F0F0"
C_ANKUNFT = "E8E8E8"
C_HEADER_BG = "F5F5F5"
C_BADGE   = "E8E8E8"

def fill(hex_color):
    return PatternFill("solid", fgColor=hex_color)

def thin_border():
    s = Side(style="thin", color="CCCCCC")
    return Border(left=s, right=s, top=s, bottom=s)

def cell_style(ws, row, col, value="", bg=None, bold=False, italic=False,
               font_color="1A1A1A", font_size=10, align="center",
               valign="center", wrap=True, border=True):
    c = ws.cell(row=row, column=col, value=value)
    if bg:
        c.fill = fill(bg)
    c.font = Font(name="Helvetica Neue", size=font_size,
                  bold=bold, italic=italic, color=font_color)
    c.alignment = Alignment(horizontal=align, vertical=valign,
                            wrap_text=wrap)
    if border:
        c.border = thin_border()
    return c

# ── Spaltenbreiten (Zeit | Mo | Di | Mi | Do) ───────────────────────────────
ws.column_dimensions["A"].width = 14   # Zeit
ws.column_dimensions["B"].width = 22   # Mo
ws.column_dimensions["C"].width = 22   # Di
ws.column_dimensions["D"].width = 22   # Mi
ws.column_dimensions["E"].width = 22   # Do

# ── Titel ────────────────────────────────────────────────────────────────────
ws.row_dimensions[1].height = 22
ws.merge_cells("A1:E1")
c = ws.cell(row=1, column=1,
            value="Drehplan Resilienz Experten 2026")
c.font = Font(name="Helvetica Neue", size=13, bold=True, color=C_BLACK)
c.alignment = Alignment(horizontal="left", vertical="center")
c.fill = fill(C_WHITE)

ws.row_dimensions[2].height = 14
ws.merge_cells("A2:E2")
c = ws.cell(row=2, column=1,
            value="7. bis 10. September  |  Flow Spaces      STATUS: VORLÄUFIG")
c.font = Font(name="Helvetica Neue", size=9, color="8C8C8C")
c.alignment = Alignment(horizontal="left", vertical="center")
c.fill = fill(C_WHITE)

# ── Legende ──────────────────────────────────────────────────────────────────
ws.row_dimensions[3].height = 16
for col, (label, color) in enumerate([
    ("IT-Sicherheit", C_IT),
    ("Haftungsrisiken", C_HAFTUNG),
    ("Personalinstrumente", C_PERSONAL),
], start=1):
    c = ws.cell(row=3, column=col, value=f"  {label}")
    c.fill = fill(color)
    c.font = Font(name="Helvetica Neue", size=9, bold=True, color=C_BLACK)
    c.alignment = Alignment(horizontal="left", vertical="center")
    c.border = thin_border()
ws.cell(row=3, column=4, value="").fill = fill(C_WHITE)
ws.cell(row=3, column=5, value="").fill = fill(C_WHITE)

# ── Kopfzeile Tage ───────────────────────────────────────────────────────────
ws.row_dimensions[4].height = 18
headers = ["", "Mo 7.9.", "Di 8.9.", "Mi 9.9.", "Do 10.9."]
for col, h in enumerate(headers, start=1):
    c = ws.cell(row=4, column=col, value=h)
    c.fill = fill(C_BLACK)
    c.font = Font(name="Helvetica Neue", size=10, bold=True, color=C_WHITE)
    c.alignment = Alignment(horizontal="center", vertical="center")
    c.border = thin_border()

# ── Hilfsfunktion: Experten-Block ────────────────────────────────────────────
def experte(ws, row, col, name, thema, color, row_span=1):
    """Füllt einen Experten-Slot, optional als Merge über row_span Zeilen."""
    if row_span > 1:
        ws.merge_cells(
            start_row=row, start_column=col,
            end_row=row + row_span - 1, end_column=col
        )
    text = f"{name}\n{thema}"
    c = ws.cell(row=row, column=col, value=text)
    c.fill = fill(color)
    c.font = Font(name="Helvetica Neue", size=10, color=C_BLACK)
    c.alignment = Alignment(horizontal="left", vertical="center",
                            wrap_text=True)
    c.border = thin_border()

def some_slot(ws, row, col, label="SoMe\nnoch offen", row_span=1):
    if row_span > 1:
        ws.merge_cells(
            start_row=row, start_column=col,
            end_row=row + row_span - 1, end_column=col
        )
    c = ws.cell(row=row, column=col, value=label)
    c.fill = fill(C_SOME)
    c.font = Font(name="Helvetica Neue", size=9, italic=True, color="999999")
    c.alignment = Alignment(horizontal="left", vertical="center",
                            wrap_text=True)
    c.border = thin_border()

def offen_slot(ws, row, col):
    c = ws.cell(row=row, column=col, value="noch offen")
    c.fill = fill(C_OFFEN)
    c.font = Font(name="Helvetica Neue", size=9, italic=True, color="BBBBBB")
    c.alignment = Alignment(horizontal="left", vertical="center")
    c.border = thin_border()

def zeit_cell(ws, row, label, height=40):
    ws.row_dimensions[row].height = height
    c = ws.cell(row=row, column=1, value=label)
    c.font = Font(name="Helvetica Neue", size=9, color="8C8C8C")
    c.alignment = Alignment(horizontal="right", vertical="center")
    c.fill = fill(C_WHITE)
    c.border = thin_border()

def ankunft_row(ws, row, label="Ankunft + Aufbau"):
    ws.row_dimensions[row].height = 12
    c = ws.cell(row=row, column=1, value="")
    c.fill = fill(C_ANKUNFT)
    c.border = thin_border()
    for col in range(2, 6):
        cc = ws.cell(row=row, column=col, value=label)
        cc.fill = fill(C_ANKUNFT)
        cc.font = Font(name="Helvetica Neue", size=8, color="555555")
        cc.alignment = Alignment(horizontal="center", vertical="center")
        cc.border = thin_border()

def pause_row(ws, row, label="Mittagspause"):
    ws.row_dimensions[row].height = 14
    c = ws.cell(row=row, column=1, value="12:30–13:30")
    c.font = Font(name="Helvetica Neue", size=9, color="8C8C8C")
    c.alignment = Alignment(horizontal="right", vertical="center")
    c.fill = fill(C_PAUSE)
    c.border = thin_border()
    for col in range(2, 6):
        cc = ws.cell(row=row, column=col, value=label)
        cc.fill = fill(C_PAUSE)
        cc.font = Font(name="Helvetica Neue", size=9, italic=True, color="999999")
        cc.alignment = Alignment(horizontal="center", vertical="center")
        cc.border = thin_border()

def abbau_row(ws, row):
    ws.row_dimensions[row].height = 12
    c = ws.cell(row=row, column=1, value="16:30")
    c.font = Font(name="Helvetica Neue", size=9, color="8C8C8C")
    c.fill = fill(C_ANKUNFT)
    c.border = thin_border()
    for col in range(2, 6):
        cc = ws.cell(row=row, column=col, value="Abbau")
        cc.fill = fill(C_ANKUNFT)
        cc.font = Font(name="Helvetica Neue", size=8, color="555555")
        cc.alignment = Alignment(horizontal="center", vertical="center")
        cc.border = thin_border()

# ── Zeilen-Layout ─────────────────────────────────────────────────────────────
# R5:  Ankunft 8:00
# R6:  8:30–11:00  (2.5h — tall: 2 Excel-Zeilen zusammengemergt)
# R7:  (merge-Fortsetzung für tall-Slots)
# R8:  Ankunft 10:30
# R9:  11:00–12:30
# R10: Mittagspause 12:30–13:30
# R11: Ankunft 13:00
# R12: 13:30–15:00
# R13: Ankunft 14:30
# R14: 15:00–16:30
# R15: Abbau 16:30
# R16: Leerzeile
# R17: Hinweise

# R5 — Ankunft 8:00
ankunft_row(ws, 5, "Ankunft + Aufbau")
ws.cell(row=5, column=1).value = "8:00"
ws.cell(row=5, column=1).font = Font(name="Helvetica Neue", size=9, color="8C8C8C")

# R6+R7 — 8:30–11:00 (SoMe / tall)
ws.row_dimensions[6].height = 46
ws.row_dimensions[7].height = 24

zeit_cell(ws, 6, "8:30–11:00", height=46)
# Zeit-Merge über R6+R7
ws.merge_cells("A6:A7")
ws.cell(row=6, column=1).alignment = Alignment(horizontal="right", vertical="center")

# Mo: Oliver Meier (IT, SoMe)
experte(ws, 6, 2, "Oliver Meier", "IT-Sicherheit · SoMe", C_IT, row_span=2)
# Di: Nico Rietbrock (Haftung · SoMe)
experte(ws, 6, 3, "Nico Rietbrock", "Haftungsrisiken · SoMe", C_HAFTUNG, row_span=2)
# Mi: Tim Gysbers (Personal · SoMe)
experte(ws, 6, 4, "Tim Gysbers", "Personalinstrumente · SoMe", C_PERSONAL, row_span=2)
# Do: Auswechsel-Slot (frei)
some_slot(ws, 6, 5, "Auswechsel-Slot\nfreigehalten", row_span=2)

# R8 — Ankunft 10:30
ankunft_row(ws, 8, "Ankunft + Aufbau")
ws.cell(row=8, column=1).value = "10:30"
ws.cell(row=8, column=1).font = Font(name="Helvetica Neue", size=9, color="8C8C8C")

# R9 — 11:00–12:30
ws.row_dimensions[9].height = 44
zeit_cell(ws, 9, "11:00–12:30", height=44)
experte(ws, 9, 2, "Jürgen Höfert",      "Haftungsrisiken",     C_HAFTUNG)
experte(ws, 9, 3, "Nicolai Geiger",     "IT-Sicherheit",       C_IT)
experte(ws, 9, 4, "Corinna Sparr",      "Haftungsrisiken",     C_HAFTUNG)
experte(ws, 9, 5, "Luca Keller",        "Haftungsrisiken",     C_HAFTUNG)

# R10 — Mittagspause
pause_row(ws, 10)

# R11 — Ankunft 13:00
ankunft_row(ws, 11, "Ankunft + Aufbau")
ws.cell(row=11, column=1).value = "13:00"
ws.cell(row=11, column=1).font = Font(name="Helvetica Neue", size=9, color="8C8C8C")

# R12 — 13:30–15:00
ws.row_dimensions[12].height = 44
zeit_cell(ws, 12, "13:30–15:00", height=44)
experte(ws, 12, 2, "Lukas Gabriel",      "IT-Sicherheit",         C_IT)
experte(ws, 12, 3, "Thorsten Niggemann", "Haftungsrisiken",       C_HAFTUNG)
experte(ws, 12, 4, "Emre Liman",         "Personalinstrumente",   C_PERSONAL)
experte(ws, 12, 5, "Jens Behrens",       "Personalinstrumente",   C_PERSONAL)

# R13 — Ankunft 14:30
ankunft_row(ws, 13, "Ankunft + Aufbau")
ws.cell(row=13, column=1).value = "14:30"
ws.cell(row=13, column=1).font = Font(name="Helvetica Neue", size=9, color="8C8C8C")

# R14 — 15:00–16:30
ws.row_dimensions[14].height = 44
zeit_cell(ws, 14, "15:00–16:30", height=44)
experte(ws, 14, 2, "Steffen Bölger",  "IT-Sicherheit",       C_IT)
experte(ws, 14, 3, "Stefan Schell",   "Personalinstrumente", C_PERSONAL)
experte(ws, 14, 4, "Gina Schneider",  "Personalinstrumente", C_PERSONAL)
offen_slot(ws, 14, 5)

# R15 — Abbau
abbau_row(ws, 15)

# ── Druckbereich + Seiteneinrichtung ─────────────────────────────────────────
ws.print_area = "A1:E15"
ws.page_setup.orientation = "landscape"
ws.page_setup.fitToPage = True
ws.page_setup.fitToWidth = 1
ws.page_setup.fitToHeight = 1
ws.sheet_properties.pageSetUpPr.fitToPage = True

out = "/Users/victorholland/Vibe Coding/dispatcher/cockpit/dispo2026.xlsx"
wb.save(out)
print(f"OK: {out}")