#!/usr/bin/env python3
"""REACH Compliance Checklist — Phoenix Metals B.V."""
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side, numbers
from openpyxl.utils import get_column_letter
from openpyxl.worksheet.datavalidation import DataValidation

wb = Workbook()

# ── Branding colours ──
NAVY   = "1B2A4A"
TEAL   = "0D7377"
GOLD   = "D4A843"
WHITE  = "FFFFFF"
LGRAY  = "F2F2F2"
RED    = "C0392B"
ORANGE = "E67E22"
GREEN  = "27AE60"
BLUE   = "2980B9"

hdr_font = Font(name="Calibri", bold=True, color=WHITE, size=11)
hdr_fill = PatternFill("solid", fgColor=NAVY)
sub_fill = PatternFill("solid", fgColor=TEAL)
alt_fill = PatternFill("solid", fgColor=LGRAY)
gold_fill = PatternFill("solid", fgColor=GOLD)
thin_border = Border(
    left=Side("thin"), right=Side("thin"),
    top=Side("thin"), bottom=Side("thin"),
)
wrap = Alignment(wrap_text=True, vertical="top")
center = Alignment(horizontal="center", vertical="center", wrap_text=True)

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

def style_data(ws, start_row, end_row, cols):
    for r in range(start_row, end_row + 1):
        for c in range(1, cols + 1):
            cell = ws.cell(row=r, column=c)
            cell.border = thin_border
            cell.alignment = wrap
            if (r - start_row) % 2 == 1:
                cell.fill = alt_fill

def auto_width(ws, cols, min_w=12, max_w=45):
    for c in range(1, cols + 1):
        ws.column_dimensions[get_column_letter(c)].width = min_w
    # manual overrides per sheet below

# ════════════════════════════════════════════════════════════
# SHEET 1 — Stoffeninventarisatie
# ════════════════════════════════════════════════════════════
ws1 = wb.active
ws1.title = "Stoffeninventarisatie"
ws1.sheet_properties.tabColor = NAVY

headers1 = ["Stofnaam", "CAS-nummer", "Categorie", "Leverancier",
            "Volume/jaar (geschat)", "REACH-status", "SDS aanwezig", "Acties nodig"]

substances = [
    ["Sodium hydroxide 50%", "1310-73-2", "Grondstof", "To be confirmed", "< 10 ton", "Geregistreerd", "Nee", "SDS aanvragen; leverancier vastleggen"],
    ["Sulfuric acid 25-75%", "7664-93-9", "Grondstof", "To be confirmed", "< 5 ton", "Geregistreerd", "Nee", "SDS aanvragen; exacte concentratie bepalen"],
    ["Aluminum sulfate", "10043-01-3", "Grondstof", "To be confirmed", "< 2 ton", "Geregistreerd", "Nee", "SDS aanvragen"],
    ["Calcium oxide", "1305-78-8", "Grondstof", "To be confirmed", "< 1 ton", "Geregistreerd", "Nee", "SDS aanvragen; registratie-volume verifiëren"],
    ["SARU Ash", "Mengsel (geen CAS)", "Invoer (afvalstroom)", "Shell", "Variable", "End-of-waste bepaling nodig", "Nee", "EoW-status verifiëren; samengestelde analyse aanvragen"],
    ["Staalslakken", "Mengsel (geen CAS)", "Invoer (afvalstroom)", "Tata Steel", "Variable", "End-of-waste bepaling nodig", "Nee", "EoW-status verifiëren; samengestelde analyse aanvragen"],
    ["Vanadium(V) oxide", "1314-62-1", "Tussentijds product", "Eigen productie", "< 0,5 ton", "PPORD (Art. 9)", "Nee", "PPORD-notificatie indienen; SDS opstellen"],
    ["Vanadium-elektrolyt", "Mengsel (geen CAS)", "Product", "Eigen productie", "< 0,2 ton", "PPORD (Art. 9)", "Nee", "PPORD-notificatie; SDS + CLP opstellen"],
    ["Magnesiumhydroxide", "1309-42-8", "Grondstof", "To be confirmed", "< 1 ton", "Geregistreerd", "Nee", "SDS aanvragen"],
    ["IJzeroxide", "1309-37-1", "Bijproduct / afval", "Eigen productie", "< 0,5 ton", "SR&D (Art. 3(23))", "Nee", "Bepaal of afval of product; REACH-verplichting beoordelen"],
    ["Calciumhydroxide", "1305-62-0", "Grondstof", "To be confirmed", "< 1 ton", "Geregistreerd", "Nee", "SDS aanvragen"],
    ["Gips (calciumsulfaat)", "7778-18-9", "Bijproduct", "Eigen productie", "< 1 ton", "SR&D (Art. 3(23))", "Nee", "EoW-status beoordelen; marktbereidheid verifiëren"],
]

# Title row
ws1.merge_cells("A1:H1")
title_cell = ws1["A1"]
title_cell.value = "REACH Compliance Checklist — Phoenix Metals B.V.\nStoffeninventarisatie"
title_cell.font = Font(name="Calibri", bold=True, color=WHITE, size=14)
title_cell.fill = PatternFill("solid", fgColor=TEAL)
title_cell.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
ws1.row_dimensions[1].height = 40

# Headers row 2
for i, h in enumerate(headers1, 1):
    ws1.cell(row=2, column=i, value=h)
style_header(ws1, 2, len(headers1))

for ri, row_data in enumerate(substances, 3):
    for ci, val in enumerate(row_data, 1):
        ws1.cell(row=ri, column=ci, value=val)
style_data(ws1, 3, 3 + len(substances) - 1, len(headers1))

# Data validations
dv_reach = DataValidation(type="list", formula1='"Geregistreerd,PPORD (Art. 9),SR&D (Art. 3(23)),End-of-waste bepaling nodig,Niet van toepassing"', allow_blank=True)
dv_sds = DataValidation(type="list", formula1='"Ja,Nee,Onderweg"', allow_blank=True)
ws1.add_data_validation(dv_reach)
ws1.add_data_validation(dv_sds)
dv_reach.add(f"F3:F50")
dv_sds.add(f"G3:G50")

ws1.column_dimensions["A"].width = 26
ws1.column_dimensions["B"].width = 22
ws1.column_dimensions["C"].width = 20
ws1.column_dimensions["D"].width = 18
ws1.column_dimensions["E"].width = 20
ws1.column_dimensions["F"].width = 28
ws1.column_dimensions["G"].width = 14
ws1.column_dimensions["H"].width = 42

ws1.print_options.horizontalCentered = True
ws1.page_setup.orientation = "landscape"
ws1.page_setup.fitToWidth = 1

# ════════════════════════════════════════════════════════════
# SHEET 2 — Compliance Checklist
# ════════════════════════════════════════════════════════════
ws2 = wb.create_sheet("Compliance Checklist")
ws2.sheet_properties.tabColor = TEAL

headers2 = ["Item #", "Categorie", "Checklist-item", "Verantwoordelijke",
            "Status", "Deadline", "Opmerkingen"]

items = [
    # PPORD-notificatie
    [1, "PPORD-notificatie", "Bepaal of PPORD-notificatie vereist is voor pilotvolumes", "HSEQ Manager", "Niet gestart", "Q2 2026", "Volumes < 10 ton/jaar mogen SR&D vallen; > 10 ton vereist PPORD"],
    [2, "PPORD-notificatie", "Stel PPORD-dossier op (samenvatting, risicobeheer, RMM)", "HSEQ Manager", "Niet gestart", "Q2 2026", "ECHA-template gebruiken; bijlage met procesbeschrijving"],
    [3, "PPORD-notificatie", "Dien PPORD-notificatie in bij ECHA", "HSEQ Manager", "Niet gestart", "Q3 2026", "Via REACH-IT portal; bewijs van ontvangst bewaren"],
    [4, "PPORD-notificatie", "Monitor PPORD-vervaldatum en verlenging", "HSEQ Manager", "Niet gestart", "Q4 2027", "PPORD geldt max. 5 jaar; tijdig verlengen"],
    # Stoffeninventarisatie
    [5, "Stoffeninventarisatie", "Completeer stoffeninventarisatie (alle stoffen in/uit proces)", "Proces Engineer", "In uitvoering", "Q2 2026", "Zie werkblad 1; inclusief bijproducten en afvalstromen"],
    [6, "Stoffeninventarisatie", "Verifieer REACH-registratiestatus van alle grondstoffen", "HSEQ Manager", "Niet gestart", "Q2 2026", "Check ECHA-database per CAS-nummer"],
    [7, "Stoffeninventarisatie", "Bepaal tonnages per stof per kalenderjaar", "Proces Engineer", "Niet gestart", "Q3 2026", "Gebaseerd op verwachte pilotcapaciteit"],
    [8, "Stoffeninventarisatie", "Identificeer SVHC-stoffen in inventarisatie", "HSEQ Manager", "Niet gestart", "Q2 2026", "Kruisvergelijk met ECHA SVHC-kandidaatlijst"],
    # SDS-beheer
    [9, "SDS-beheer", "Verzamel alle SDS'en van leveranciers", "Inkoper", "Niet gestart", "Q2 2026", "Vereist per REACH Art. 31; laatste versie in 16 talen"],
    [10, "SDS-beheer", "Stel SDS op voor eigen tussentijdse producten", "HSEQ Manager", "Niet gestart", "Q3 2026", "Vanadium(V) oxide en -elektrolyt"],
    [11, "SDS-beheer", "Stel SDS op voor eigen eindproducten", "HSEQ Manager", "Niet gestart", "Q3 2026", "Vanadium-elektrolyt (indien afgeleverd aan derden)"],
    [12, "SDS-beheer", "Implementeer SDS-beheersysteem (versiebeheer, distributie)", "HSEQ Manager", "Niet gestart", "Q3 2026", "Elektronisch systeem met verloopwaarschuwing"],
    # CLP-labeling
    [13, "CLP-labeling", "Classificeer alle eigen stoffen/mengsels volgens CLP (EG 1272/2008)", "HSEQ Manager", "Niet gestart", "Q3 2026", "Gebruik ECHA CLP-guidance"],
    [14, "CLP-labeling", "Ontwerp etiketten voor opslagvaten en verpakkingen", "HSEQ Manager", "Niet gestart", "Q3 2026", "P-, H-zinnen; gevarenpictogrammen"],
    [15, "CLP-labeling", "Plaats etiketten op alle opslag- en transportverpakkingen", "Facilitair", "Niet gestart", "Q3 2026", "Audit-proof; foto-documentatie"],
    # ECHA-account
    [16, "ECHA-account", "Maak ECHA REACH-IT account aan voor Phoenix Metals B.V.", "HSEQ Manager", "Niet gestart", "Q2 2026", "Vereist UBA-nummer (legal entity identifier)"],
    [17, "ECHA-account", "Registreer voor SPIO-tool (Substance Information Exchange Forum)", "HSEQ Manager", "Niet gestart", "Q2 2026", "Relevant voor toekomstige consortium-deelname"],
    [18, "ECHA-account", "Configureer C&L Inventory notificatie-rechten", "HSEQ Manager", "Niet gestart", "Q3 2026", "Voor eigen classificaties en labeling"],
    # Vanadium Consortium
    [19, "Vanadium Consortium", "Onderzoek bestaande Vanadium REACH Consortium", "HSEQ Manager", "Niet gestart", "Q3 2026", "Contact via ECHA of industrie-vereniging"],
    [20, "Vanadium Consortium", "Beoordeel lidmaatschapsvoorwaarden en kosten", "HSEQ Manager", "Niet gestart", "Q4 2026", "FOAK-fase: gezamenlijke registratie kostenefficiënter"],
    [21, "Vanadium Consortium", "Sluit aan bij consortium (indien FOAK-doel bevestigd)", "Directie", "Niet gestart", "2028", "Afhankelijk van commerciële beslissing FOAK"],
    # End-of-waste
    [22, "End-of-waste bepaling", "Verifieer EoW-status van SARU Ash (Shell)", "HSEQ Manager", "Niet gestart", "Q2 2026", "Nederlandsestoffen op EU-afvallijst controleren"],
    [23, "End-of-waste bepaling", "Verifieer EoW-status van staalslakken (Tata Steel)", "HSEQ Manager", "Niet gestart", "Q2 2026", "Euroslag-protocol kan van toepassing zijn"],
    [24, "End-of-waste bepaling", "Bepaal REACH-verplichtingen bij EoW-transitie", "HSEQ Manager", "Niet gestart", "Q3 2026", "EoW = product → volledige REACH-plicht"],
    # Training
    [25, "Training", "REACH-basis training voor alle medewerkers", "HSEQ Manager", "Niet gestart", "Q3 2026", "Inclusief CLP, SDS-lezen, veiligheidsinformatie"],
    [26, "Training", "Specifieke training PPORD voor HSEQ-team", "HSEQ Manager", "Niet gestart", "Q3 2026", "Focus op notificatieprocedure en compliance"],
    [27, "Training", "Training chemie-veiligheid (omgang met zuren/basen)", "HSEQ Manager", "Niet gestart", "Q3 2026", "NaOH, H₂SO₄ handling procedures"],
    # Documentatie
    [28, "Documentatie", "Stel REACH-compliance dossier op", "HSEQ Manager", "Niet gestart", "Q4 2026", "Centraal document; alle bewijsstukken gebundeld"],
    [29, "Documentatie", "Creëer chemische register (leveranciers, CAS, volumes)", "HSEQ Manager", "Niet gestart", "Q3 2026", "Koppeling met inkoop en productie"],
    [30, "Documentatie", "Definieer documentbeheer-procedure (retentie, archivering)", "HSEQ Manager", "Niet gestart", "Q3 2026", "ISO 9001-kader gebruiken"],
    # FOAK-voorbereiding
    [31, "FOAK-voorbereiding", "Bepaal verwachte volumes voor FOAK-fase (> 1 ton/jaar)", "Proces Engineer", "Niet gestart", "Q4 2027", "Bepaalt registratieverplichting"],
    [32, "FOAK-voorbereiding", "Initieer REACH-registratie-traject (via consortium)", "HSEQ Manager", "Niet gestart", "2028", "Lead registrant of joint submission"],
    [33, "FOAK-voorbereiding", "Voer CSR/SR uit voor eigen stoffen", "HSEQ Manager", "Niet gestart", "2028", "Chemical Safety Report / Safety Report"],
    [34, "FOAK-voorbereiding", "Evalueer SVHC-status vanadiumverbindingen", "HSEQ Manager", "Niet gestart", "2027", "V₂O₅ staat op SVHC-kandidaatlijst"],
]

ws2.merge_cells("A1:G1")
t2 = ws2["A1"]
t2.value = "REACH Compliance Checklist — Phoenix Metals B.V.\nCompliance Checklist"
t2.font = Font(name="Calibri", bold=True, color=WHITE, size=14)
t2.fill = PatternFill("solid", fgColor=TEAL)
t2.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
ws2.row_dimensions[1].height = 40

for i, h in enumerate(headers2, 1):
    ws2.cell(row=2, column=i, value=h)
style_header(ws2, 2, len(headers2))

for ri, row_data in enumerate(items, 3):
    for ci, val in enumerate(row_data, 1):
        ws2.cell(row=ri, column=ci, value=val)
style_data(ws2, 3, 3 + len(items) - 1, len(headers2))

dv_status = DataValidation(type="list", formula1='"Niet gestart,In uitvoering,Afgerond,Niet van toepassing"', allow_blank=True)
ws2.add_data_validation(dv_status)
dv_status.add("E3:E100")

ws2.column_dimensions["A"].width = 8
ws2.column_dimensions["B"].width = 22
ws2.column_dimensions["C"].width = 52
ws2.column_dimensions["D"].width = 18
ws2.column_dimensions["E"].width = 18
ws2.column_dimensions["F"].width = 14
ws2.column_dimensions["G"].width = 44

ws2.page_setup.orientation = "landscape"
ws2.page_setup.fitToWidth = 1

# ════════════════════════════════════════════════════════════
# SHEET 3 — Actielijst
# ════════════════════════════════════════════════════════════
ws3 = wb.create_sheet("Actielijst")
ws3.sheet_properties.tabColor = GOLD

headers3 = ["Actie #", "Prioriteit", "Omschrijving", "Verantwoordelijke",
            "Deadline", "Status", "Afhankelijkheden"]

actions = [
    ["A-01", "Hoog", "ECHA REACH-IT account aanmaken", "HSEQ Manager", "Mei 2026", "Niet gestart", "UBA-nummer vereist"],
    ["A-02", "Hoog", "SDS verzamelen van alle grondstofleveranciers", "Inkoper", "Jun 2026", "Niet gestart", "Leverancierslijst compleet"],
    ["A-03", "Hoog", "PPORD-notificatie opstellen en indienen", "HSEQ Manager", "Aug 2026", "Niet gestart", "A-01; stoffeninventarisatie compleet"],
    ["A-04", "Hoog", "CLP-classificatie van alle eigen stoffen/mengsels", "HSEQ Manager", "Aug 2026", "Niet gestart", "Samenstelling product vastgesteld"],
    ["A-05", "Hoog", "End-of-waste status SARU Ash bepalen", "HSEQ Manager", "Jun 2026", "Niet gestart", "Shell-specificaties ontvangen"],
    ["A-06", "Hoog", "End-of-waste status staalslakken bepalen", "HSEQ Manager", "Jun 2026", "Niet gestart", "Tata Steel specificaties ontvangen"],
    ["A-07", "Middel", "Stoffeninventarisatie completeren (12 stoffen)", "Proces Engineer", "Jun 2026", "In uitvoering", "Proces flowsheet definitief"],
    ["A-08", "Middel", "REACH-registratiestatus verifiëren per CAS-nummer", "HSEQ Manager", "Jul 2026", "Niet gestart", "A-07"],
    ["A-09", "Middel", "SDS opstellen voor vanadium(V) oxide", "HSEQ Manager", "Sep 2026", "Niet gestart", "A-04; procegegevens beschikbaar"],
    ["A-10", "Middel", "SDS opstellen voor vanadium-elektrolyt", "HSEQ Manager", "Sep 2026", "Niet gestart", "A-04; samenstelling vastgesteld"],
    ["A-11", "Middel", "Etiketten ontwerpen en plaatsen (CLP)", "HSEQ Manager", "Sep 2026", "Niet gestart", "A-04"],
    ["A-12", "Middel", "REACH-basis training medewerkers", "HSEQ Manager", "Sep 2026", "Niet gestart", "Trainingsmateriaal beschikbaar"],
    ["A-13", "Middel", "Chemisch register opzetten", "HSEQ Manager", "Aug 2026", "Niet gestart", "A-07; A-08"],
    ["A-14", "Middel", "Vanadium Consortium verkennen", "HSEQ Manager", "Q4 2026", "Niet gestart", "A-01"],
    ["A-15", "Middel", "Documentbeheer-procedure definiëren", "HSEQ Manager", "Aug 2026", "Niet gestart", "ISO 9001-kader"],
    ["A-16", "Middel", "REACH-compliance dossier opstellen", "HSEQ Manager", "Q4 2026", "Niet gestart", "A-01 t/m A-13"],
    ["A-17", "Laag", "PPORD-vervaldatum monitoren (Q4 2027)", "HSEQ Manager", "Q4 2027", "Niet gestart", "A-03"],
    ["A-18", "Laag", "FOAK-volume-prognose opstellen", "Proces Engineer", "Q4 2027", "Niet gestart", "Pilotdata beschikbaar"],
    ["A-19", "Laag", "Consortium lidmaatschap beslissing", "Directie", "2028", "Niet gestart", "A-14; A-18"],
    ["A-20", "Laag", "REACH-registratie initieren (FOAK)", "HSEQ Manager", "2028", "Niet gestart", "A-19; volumes > 1 ton/jaar"],
]

ws3.merge_cells("A1:G1")
t3 = ws3["A1"]
t3.value = "REACH Compliance Checklist — Phoenix Metals B.V.\nActielijst"
t3.font = Font(name="Calibri", bold=True, color=WHITE, size=14)
t3.fill = PatternFill("solid", fgColor=TEAL)
t3.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
ws3.row_dimensions[1].height = 40

for i, h in enumerate(headers3, 1):
    ws3.cell(row=2, column=i, value=h)
style_header(ws3, 2, len(headers3))

for ri, row_data in enumerate(actions, 3):
    for ci, val in enumerate(row_data, 1):
        ws3.cell(row=ri, column=ci, value=val)
style_data(ws3, 3, 3 + len(actions) - 1, len(headers3))

# Priority colour coding
for ri in range(3, 3 + len(actions)):
    p = ws3.cell(row=ri, column=2).value
    if p == "Hoog":
        ws3.cell(row=ri, column=2).font = Font(bold=True, color=RED)
    elif p == "Middel":
        ws3.cell(row=ri, column=2).font = Font(bold=True, color=ORANGE)
    elif p == "Laag":
        ws3.cell(row=ri, column=2).font = Font(bold=True, color=GREEN)

dv_pri = DataValidation(type="list", formula1='"Hoog,Middel,Laag"', allow_blank=True)
ws3.add_data_validation(dv_pri)
dv_pri.add("B3:B100")

ws3.column_dimensions["A"].width = 8
ws3.column_dimensions["B"].width = 12
ws3.column_dimensions["C"].width = 44
ws3.column_dimensions["D"].width = 18
ws3.column_dimensions["E"].width = 14
ws3.column_dimensions["F"].width = 16
ws3.column_dimensions["G"].width = 36

ws3.page_setup.orientation = "landscape"

# ════════════════════════════════════════════════════════════
# SHEET 4 — Tijdlijn & Fases
# ════════════════════════════════════════════════════════════
ws4 = wb.create_sheet("Tijdlijn & Fases")
ws4.sheet_properties.tabColor = GREEN

headers4 = ["Fase", "Periode", "Milestone", "Deliverable", "Status"]

timeline = [
    ["Fase 1: Voorbereiding", "Q2 2026", "Stoffeninventarisatie compleet", "Chemisch register (Excel/DB)", "In uitvoering"],
    ["", "", "ECHA-account actief", "REACH-IT login credentials", "Niet gestart"],
    ["", "", "Alle SDS verzameld", "SDS-dossier (12 stoffen)", "Niet gestart"],
    ["", "", "EoW-status afvalstromen bepaald", "Juridisch memo EoW", "Niet gestart"],
    ["", "", "CLP-classificatie afgerond", "Classificatietabel", "Niet gestart"],
    ["", "", "PPORD-dossier opgesteld", "PPORD-notificatiepakket (ECHA)", "Niet gestart"],
    ["Fase 2: Pilot-operatie", "Q3 2026 – Q4 2027", "PPORD ingediend en bevestigd", "ECHA-bevestigingsbrief", "Niet gestart"],
    ["", "", "SDS eigen producten opgesteld", "SDS V₂O₅ + Vanadium-elektrolyt", "Niet gestart"],
    ["", "", "CLP-etikettering geïmplementeerd", "Gelabelde vaten/verpakkingen", "Niet gestart"],
    ["", "", "Medewerker training afgerond", "Trainingslogboek + certificaten", "Niet gestart"],
    ["", "", "Compliance dossier opgeleverd", "REACH-compliance dossier", "Niet gestart"],
    ["", "", "Consortium-verkenning afgerond", "Adviesmemo consortium", "Niet gestart"],
    ["", "", "PPORD-verlenging (indien nodig)", "Verlengingsaanvraag ECHA", "Niet gestart"],
    ["Fase 3: FOAK-voorbereiding", "2028+", "FOAK-volume-prognose beschikbaar", "Tonnage-rapport", "Niet gestart"],
    ["", "", "Consortium lidmaatschap besloten", "Lidmaatschapscontract", "Niet gestart"],
    ["", "", "REACH-registratie gestart", "Joint submission dossier", "Niet gestart"],
    ["", "", "CSR/SR opgesteld", "Chemical Safety Report", "Niet gestart"],
    ["", "", "Volledige REACH-compliance FOAK", "Registratiecertificaat ECHA", "Niet gestart"],
]

ws4.merge_cells("A1:E1")
t4 = ws4["A1"]
t4.value = "REACH Compliance Checklist — Phoenix Metals B.V.\nTijdlijn & Fases"
t4.font = Font(name="Calibri", bold=True, color=WHITE, size=14)
t4.fill = PatternFill("solid", fgColor=TEAL)
t4.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
ws4.row_dimensions[1].height = 40

for i, h in enumerate(headers4, 1):
    ws4.cell(row=2, column=i, value=h)
style_header(ws4, 2, len(headers4))

for ri, row_data in enumerate(timeline, 3):
    for ci, val in enumerate(row_data, 1):
        ws4.cell(row=ri, column=ci, value=val)
style_data(ws4, 3, 3 + len(timeline) - 1, len(headers4))

# Merge phase cells
ws4.merge_cells("A3:A8")
ws4.merge_cells("A9:A15")
ws4.merge_cells("A16:A19")
for cell_ref in ["A3", "A9", "A16"]:
    c = ws4[cell_ref]
    c.font = Font(bold=True, size=11, color=NAVY)
    c.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)

# Phase colour bars
phase_fills = {
    3: PatternFill("solid", fgColor="D6EAF8"),  # light blue
    9: PatternFill("solid", fgColor="D5F5E3"),  # light green
    16: PatternFill("solid", fgColor="FDEBD0"), # light orange
}
for start, fill in phase_fills.items():
    for row in range(start, start + 6):
        if row <= 19:
            ws4.cell(row=row, column=1).fill = fill

ws4.column_dimensions["A"].width = 28
ws4.column_dimensions["B"].width = 20
ws4.column_dimensions["C"].width = 36
ws4.column_dimensions["D"].width = 36
ws4.column_dimensions["E"].width = 18

ws4.page_setup.orientation = "landscape"

# ════════════════════════════════════════════════════════════
# SAVE
# ════════════════════════════════════════════════════════════
out = "/root/projects/jg/2026-reach-phoenix-metals/deliverables/xlsx/REACH_Compliance_Checklist_Phoenix_Metals_v1.0.xlsx"
wb.save(out)
print(f"OK → {out}")