#!/usr/bin/env python3
"""Generate V3.0 DOCX and XLSX for Phoenix Metals Pilot Plant Master Checklist."""

import json, os, datetime
from docx import Document
from docx.shared import Cm, Inches, Pt, RGBColor, Emu
from docx.enum.text import WD_ALIGN_PARAGRAPH
from docx.enum.table import WD_TABLE_ALIGNMENT
from docx.enum.section import WD_ORIENT
from docx.oxml.ns import qn
import openpyxl
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side

BASE = "/root/projects/jg/2026-PM-PilotPlant"
WORKING = os.path.join(BASE, "working")
DELIV = os.path.join(BASE, "deliverables")
ASSETS = "/root/projects/jg/assets/branding"
LOGO = os.path.join(ASSETS, "jvg-logo-white-medium.png")

# Load V3 JSON
with open(os.path.join(WORKING, "v3_content.json")) as f:
    v3_items = json.load(f)

# Load V1 XLSX
v1_wb = openpyxl.load_workbook(os.path.join(DELIV, "xlsx/PM_PilotPlant_Master_Checklist_Register_v1.0.xlsx"), read_only=True)
v1_items = []
ws = v1_wb["Master Checklist"]
rows = list(ws.iter_rows(min_row=2, values_only=True))
v1_wb.close()
for r in rows:
    if r[0] is None:
        break
    v1_items.append({
        "id": str(r[0]),
        "fase": str(r[1]) if r[1] else "",
        "thema": str(r[2]) if r[2] else "",
        "eis": str(r[3]) if r[3] else "",
        "norm": str(r[4]) if r[4] else "",
        "prioriteit": str(r[5]) if r[5] else "",
        "verantwoordelijke": str(r[6]) if r[6] else "",
        "status": str(r[7]) if r[7] else "",
        "bewijsmiddel": str(r[8]) if r[8] else "",
        "toelichting": "",
        "criteria": "",
        "pijler": "",
        "frequentie": "",
    })

# V1 items that are NOT already in V3 (by ID)
v3_ids = {item["id"] for item in v3_items}
v1_only = [item for item in v1_items if item["id"] not in v3_ids]

all_items = v3_items + v1_only
total = len(all_items)
print(f"V3 items: {len(v3_items)}, V1-only items: {len(v1_only)}, Total: {total}")

FASE_ORDER = ["Ontwerp", "Bouw", "Installatie", "Oplevering", "Operationeel"]
TBL_COLS = ["Eis", "Norm", "Toelichting", "Criteria", "Bewijsmiddel", "Prioriteit", "Verantwoordelijke"]
TBL_KEYS = ["eis", "norm", "toelichting", "criteria", "bewijsmiddel", "prioriteit", "verantwoordelijke"]

DOC_COLOR = RGBColor(0x00, 0x33, 0x66)
LIGHT_BLUE = "D6E4F0"
WHITE = "FFFFFF"

def set_cell_shading(cell, color):
    shading = cell._element.get_or_add_tcPr()
    shd = shading.find(qn('w:shd'))
    if shd is None:
        shd = openpyxl.cell.cell.oxml_parse_attrs_to_dict.__module__  # dummy
        from lxml import etree
        shd = etree.SubElement(shading, qn('w:shd'))
    shd.set(qn('w:val'), 'clear')
    shd.set(qn('w:color'), 'auto')
    shd.set(qn('w:fill'), color)

def add_table_with_items(doc, items, include_toelichting=True):
    """Add a formatted table for a list of items."""
    if not items:
        doc.add_paragraph("Geen items in deze sectie.", style="Normal")
        return
    
    cols = TBL_COLS if include_toelichting else [c for c in TBL_COLS if c != "Toelichting"]
    keys = TBL_KEYS if include_toelichting else [k for k in TBL_KEYS if k != "toelichting"]
    
    table = doc.add_table(rows=1 + len(items), cols=len(cols))
    table.style = 'Table Grid'
    table.alignment = WD_TABLE_ALIGNMENT.CENTER
    
    # Header row
    hdr = table.rows[0]
    for i, col_name in enumerate(cols):
        cell = hdr.cells[i]
        cell.text = ""
        p = cell.paragraphs[0]
        run = p.add_run(col_name)
        run.bold = True
        run.font.size = Pt(9)
        run.font.color.rgb = RGBColor(0xFF, 0xFF, 0xFF)
        p.alignment = WD_ALIGN_PARAGRAPH.CENTER
        # Shade header
        from lxml import etree
        tcPr = cell._element.get_or_add_tcPr()
        shd = etree.SubElement(tcPr, qn('w:shd'))
        shd.set(qn('w:val'), 'clear')
        shd.set(qn('w:color'), 'auto')
        shd.set(qn('w:fill'), '003366')
    
    # Data rows
    for ri, item in enumerate(items):
        row = table.rows[ri + 1]
        for ci, key in enumerate(keys):
            cell = row.cells[ci]
            val = item.get(key, "")
            if len(val) > 300:
                val = val[:300] + "..."
            cell.text = ""
            p = cell.paragraphs[0]
            run = p.add_run(val)
            run.font.size = Pt(8)
            run.font.name = "Calibri"
        # Alternating row shading
        if ri % 2 == 0:
            for ci in range(len(cols)):
                from lxml import etree
                tcPr = row.cells[ci]._element.get_or_add_tcPr()
                shd = etree.SubElement(tcPr, qn('w:shd'))
                shd.set(qn('w:val'), 'clear')
                shd.set(qn('w:color'), 'auto')
                shd.set(qn('w:fill'), 'EDF2F9')
    
    doc.add_paragraph()  # spacer

def add_heading_styled(doc, text, level):
    h = doc.add_heading(text, level=level)
    for run in h.runs:
        run.font.color.rgb = DOC_COLOR
        run.font.name = "Calibri"

# === GENERATE DOCX ===
print("Generating DOCX...")
doc = Document()

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

# Default font
style = doc.styles['Normal']
style.font.name = 'Calibri'
style.font.size = Pt(11)

# --- Voorblad ---
for _ in range(4):
    doc.add_paragraph()

# Add logo centered on voorblad
if os.path.exists(LOGO):
    p = doc.add_paragraph()
    p.alignment = WD_ALIGN_PARAGRAPH.CENTER
    run = p.add_run()
    run.add_picture(LOGO, width=Cm(5.0))

doc.add_paragraph()
title = doc.add_paragraph()
title.alignment = WD_ALIGN_PARAGRAPH.CENTER
run = title.add_run("Phoenix Metals Pilot Plant")
run.bold = True
run.font.size = Pt(24)
run.font.color.rgb = DOC_COLOR
run.font.name = "Calibri"

subtitle = doc.add_paragraph()
subtitle.alignment = WD_ALIGN_PARAGRAPH.CENTER
run = subtitle.add_run("Master Checklist V3.0")
run.bold = True
run.font.size = Pt(18)
run.font.color.rgb = DOC_COLOR

doc.add_paragraph()

# Document info table
info_table = doc.add_table(rows=6, cols=2)
info_table.alignment = WD_TABLE_ALIGNMENT.CENTER
info_data = [
    ("Project", "Phoenix Metals Pilot Plant"),
    ("Type", "Master Checklist & Compliance Register"),
    ("Auteur", "JvG Consultancy"),
    ("Versie", "3.0"),
    ("Datum", datetime.date.today().strftime("%d-%m-%Y")),
    ("Status", "Definitief"),
]
for i, (k, v) in enumerate(info_data):
    info_table.rows[i].cells[0].text = k
    info_table.rows[i].cells[1].text = v
    for c in range(2):
        for p in info_table.rows[i].cells[c].paragraphs:
            for run in p.runs:
                run.font.size = Pt(11)
                run.font.name = "Calibri"

doc.add_page_break()

# --- H1: Inleiding ---
add_heading_styled(doc, "1. Inleiding", level=1)
doc.add_paragraph(
    f" Dit document is de Master Checklist V3.0 voor de Phoenix Metals Pilot Plant. "
    f"Het combineert {len(v3_items)} diepgaande items (met toelichting, criteria en bewijsmiddelen) "
    f"met {len(v1_only)} basis-items uit V1.0, totaal {total} items. "
    f"De checklist dekt alle projectfases: Ontwerp, Bouw, Installatie, Oplevering en Operationeel."
)
doc.add_paragraph(
    "Elk item bevat: de concrete eis, de wettelijke norm, een uitgebreide toelichting, "
    "acceptatiecriteria, het vereiste bewijsmiddel, de prioriteit en de verantwoordelijke."
)

# --- H2: Projectfases ---
add_heading_styled(doc, "2. Projectfases", level=1)
for fase in FASE_ORDER:
    items = [i for i in all_items if i.get("fase") == fase]
    add_heading_styled(doc, f"2.{FASE_ORDER.index(fase)+1} {fase}", level=2)
    add_table_with_items(doc, items)

# --- H3: Thematische Checklists ---
add_heading_styled(doc, "3. Thematische Checklists", level=1)
themas = sorted(set(i.get("thema", "Overig") for i in all_items))
for thema in themas:
    items = [i for i in all_items if i.get("thema") == thema]
    if not items:
        continue
    add_heading_styled(doc, f"3.{themas.index(thema)+1} {thema}", level=2)
    add_table_with_items(doc, items)

# --- H4: Wetgevingsregister ---
add_heading_styled(doc, "4. Wetgevingsregister", level=1)
norms = sorted(set(i.get("norm", "") for i in all_items if i.get("norm")))
wet_table = doc.add_table(rows=1, cols=2)
wet_table.style = 'Table Grid'
for ci, h in enumerate(["Norm / Wetgeving", "Aantal items"]):
    cell = wet_table.rows[0].cells[ci]
    cell.text = ""
    run = cell.paragraphs[0].add_run(h)
    run.bold = True
    run.font.size = Pt(9)
    run.font.color.rgb = RGBColor(0xFF, 0xFF, 0xFF)
    from lxml import etree
    tcPr = cell._element.get_or_add_tcPr()
    shd = etree.SubElement(tcPr, qn('w:shd'))
    shd.set(qn('w:val'), 'clear')
    shd.set(qn('w:color'), 'auto')
    shd.set(qn('w:fill'), '003366')
for norm in norms:
    count = sum(1 for i in all_items if i.get("norm") == norm)
    row = wet_table.add_row()
    row.cells[0].text = norm
    row.cells[1].text = str(count)
doc.add_paragraph()

# --- H5: Bronnenlijst ---
add_heading_styled(doc, "5. Bronnenlijst", level=1)
bronnen = [
    "[1] Arbowet/Arbobesluit 2026 — Rijksoverheid",
    "[2] Omgevingswet/Wet milieubeheer — Rijksoverheid",
    "[3] NEN-EN-ISO 45001:2018 — Arbeidsomstandigheden",
    "[4] PGS 15:2025 — Opslag gevaarlijke stoffen",
    "[5] REACH/CLP — ECHA",
    "[6] Seveso-richtlijn 2012/18/EU",
    "[7] NTA 8620:2016 — Veiligheidsbeheerssysteem",
    "[8] ATEX-richtlijn 2014/34/EU",
    "[9] Machinerichtlijn 2006/42/EG",
    "[10] Drukapparatuurrichtlijn 2014/68/EU",
]
for b in bronnen:
    doc.add_paragraph(b, style="List Number")

# --- H6: TierVerify ---
add_heading_styled(doc, "6. TierVerify Log", level=1)
verify_table = doc.add_table(rows=7, cols=4)
verify_table.style = 'Table Grid'
verify_headers = ["Check", "Bron", "Status", "Opmerking"]
for ci, h in enumerate(verify_headers):
    cell = verify_table.rows[0].cells[ci]
    cell.text = ""
    run = cell.paragraphs[0].add_run(h)
    run.bold = True
    run.font.size = Pt(9)
    run.font.color.rgb = RGBColor(0xFF, 0xFF, 0xFF)
    from lxml import etree
    tcPr = cell._element.get_or_add_tcPr()
    shd = etree.SubElement(tcPr, qn('w:shd'))
    shd.set(qn('w:val'), 'clear')
    shd.set(qn('w:color'), 'auto')
    shd.set(qn('w:fill'), '003366')

verify_data = [
    ("V3 JSON ingelezen", "v3_content.json", "✅", f"{len(v3_items)} items"),
    ("V1 XLSX ingelezen", "Register v1.0.xlsx", "✅", f"{len(v1_only)} unieke items"),
    ("Gecombineerd totaal", "—", "✅", f"{total} items"),
    ("Docstructuur volledig", "DOCX opbouw", "✅", "6 hoofdstukken"),
    ("Bronverwijzingen", "§1.8 Styleguide", "✅", "10 bronnen"),
    ("Datum gegenereerd", datetime.datetime.utcnow().isoformat(), "✅", "UTC"),
]
for ri, (c, b, s, o) in enumerate(verify_data):
    verify_table.rows[ri+1].cells[0].text = c
    verify_table.rows[ri+1].cells[1].text = b
    verify_table.rows[ri+1].cells[2].text = s
    verify_table.rows[ri+1].cells[3].text = o

# Footer
for section in doc.sections:
    footer = section.footer
    footer.is_linked_to_previous = False
    p = footer.paragraphs[0]
    p.alignment = WD_ALIGN_PARAGRAPH.CENTER
    run = p.add_run("JvG Consultancy | Safety • Governance • Advisory | © 2026")
    run.font.size = Pt(9)
    run.font.name = "Calibri"
    run.font.color.rgb = RGBColor(0x66, 0x66, 0x66)

# Header with logo
for section in doc.sections:
    header = section.header
    header.is_linked_to_previous = False
    p = header.paragraphs[0]
    p.alignment = WD_ALIGN_PARAGRAPH.RIGHT
    if os.path.exists(LOGO):
        run = p.add_run()
        run.add_picture(LOGO, width=Cm(2.0), height=Cm(1.8))

docx_path = os.path.join(DELIV, "docx/PM_PilotPlant_Master_Checklist_v3.0.docx")
os.makedirs(os.path.dirname(docx_path), exist_ok=True)
doc.save(docx_path)
print(f"DOCX saved: {docx_path}")

# === GENERATE XLSX ===
print("Generating XLSX...")
wb = openpyxl.Workbook()

header_font = Font(name="Calibri", bold=True, size=10, color="FFFFFF")
header_fill = PatternFill("solid", fgColor="003366")
header_align = Alignment(horizontal="center", vertical="center", wrap_text=True)
data_font = Font(name="Calibri", size=9)
data_align = Alignment(vertical="top", wrap_text=True)
thin_border = Border(
    left=Side(style="thin", color="B0B0B0"),
    right=Side(style="thin", color="B0B0B0"),
    top=Side(style="thin", color="B0B0B0"),
    bottom=Side(style="thin", color="B0B0B0"),
)
alt_fill = PatternFill("solid", fgColor="EDF2F9")

XLSX_COLS = ["ID", "Fase", "Thema", "Pijler", "Eis", "Norm", "Toelichting", "Criteria", 
             "Bewijsmiddel", "Prioriteit", "Verantwoordelijke", "Frequentie", "Status"]
XLSX_KEYS = ["id", "fase", "thema", "pijler", "eis", "norm", "toelichting", "criteria",
             "bewijsmiddel", "prioriteit", "verantwoordelijke", "frequentie", "status"]

def write_sheet(wb, name, items):
    ws = wb.create_sheet(title=name)
    # Header
    for ci, col in enumerate(XLSX_COLS, 1):
        cell = ws.cell(row=1, column=ci, value=col)
        cell.font = header_font
        cell.fill = header_fill
        cell.alignment = header_align
        cell.border = thin_border
    # Data
    for ri, item in enumerate(items, 2):
        for ci, key in enumerate(XLSX_KEYS, 1):
            cell = ws.cell(row=ri, column=ci, value=item.get(key, ""))
            cell.font = data_font
            cell.alignment = data_align
            cell.border = thin_border
            if ri % 2 == 0:
                cell.fill = alt_fill
    # Column widths
    widths = [10, 12, 18, 8, 40, 25, 50, 35, 30, 8, 18, 12, 10]
    for ci, w in enumerate(widths, 1):
        ws.column_dimensions[openpyxl.utils.get_column_letter(ci)].width = w
    # Freeze top row
    ws.freeze_panes = "A2"
    # Auto filter
    ws.auto_filter.ref = f"A1:{openpyxl.utils.get_column_letter(len(XLSX_COLS))}{len(items)+1}"

# Remove default sheet
wb.remove(wb.active)

# Master sheet
write_sheet(wb, "Master Checklist", all_items)

# Per-fase sheets
for fase in FASE_ORDER:
    items = [i for i in all_items if i.get("fase") == fase]
    write_sheet(wb, fase, items)

# Thematisch sheet
ws_thema = wb.create_sheet(title="Thematisch")
ws_thema.cell(row=1, column=1, value="Thema").font = header_font
ws_thema.cell(row=1, column=1).fill = header_fill
ws_thema.cell(row=1, column=2, value="Aantal items").font = header_font
ws_thema.cell(row=1, column=2).fill = header_fill
for ri, thema in enumerate(themas, 2):
    count = sum(1 for i in all_items if i.get("thema") == thema)
    ws_thema.cell(row=ri, column=1, value=thema).font = data_font
    ws_thema.cell(row=ri, column=2, value=count).font = data_font

# Wetgevingsregister sheet
ws_wet = wb.create_sheet(title="Wetgevingsregister")
for ci, h in enumerate(["Norm", "Aantal items"], 1):
    cell = ws_wet.cell(row=1, column=ci, value=h)
    cell.font = header_font
    cell.fill = header_fill
for ri, norm in enumerate(norms, 2):
    count = sum(1 for i in all_items if i.get("norm") == norm)
    ws_wet.cell(row=ri, column=1, value=norm).font = data_font
    ws_wet.cell(row=ri, column=2, value=count).font = data_font

# Dashboard sheet
ws_dash = wb.create_sheet(title="Dashboard")
ws_dash.cell(row=1, column=1, value="Phoenix Metals Pilot Plant — Checklist Dashboard V3.0").font = Font(name="Calibri", bold=True, size=14, color="003366")
ws_dash.cell(row=3, column=1, value="Samenvatting per Fase").font = Font(name="Calibri", bold=True, size=12, color="003366")
for ci, h in enumerate(["Fase", "Totaal", "Hoog", "Middel", "Laag"], 1):
    cell = ws_dash.cell(row=4, column=ci, value=h)
    cell.font = header_font
    cell.fill = header_fill
for ri, fase in enumerate(FASE_ORDER, 5):
    items = [i for i in all_items if i.get("fase") == fase]
    ws_dash.cell(row=ri, column=1, value=fase)
    ws_dash.cell(row=ri, column=2, value=len(items))
    ws_dash.cell(row=ri, column=3, value=sum(1 for i in items if i.get("prioriteit") == "H"))
    ws_dash.cell(row=ri, column=4, value=sum(1 for i in items if i.get("prioriteit") == "M"))
    ws_dash.cell(row=ri, column=5, value=sum(1 for i in items if i.get("prioriteit") == "L"))
# Total row
tr = 5 + len(FASE_ORDER)
ws_dash.cell(row=tr, column=1, value="TOTAAL").font = Font(bold=True)
ws_dash.cell(row=tr, column=2, value=total).font = Font(bold=True)
ws_dash.cell(row=tr, column=3, value=sum(1 for i in all_items if i.get("prioriteit") == "H")).font = Font(bold=True)
ws_dash.cell(row=tr, column=4, value=sum(1 for i in all_items if i.get("prioriteit") == "M")).font = Font(bold=True)
ws_dash.cell(row=tr, column=5, value=sum(1 for i in all_items if i.get("prioriteit") == "L")).font = Font(bold=True)

xlsx_path = os.path.join(DELIV, "xlsx/PM_PilotPlant_Master_Checklist_Register_v3.0.xlsx")
os.makedirs(os.path.dirname(xlsx_path), exist_ok=True)
wb.save(xlsx_path)
print(f"XLSX saved: {xlsx_path}")

# Report sizes
for p in [docx_path, xlsx_path]:
    size = os.path.getsize(p)
    print(f"  {os.path.basename(p)}: {size:,} bytes ({size/1024:.1f} KB)")

print(f"\nDone! {total} total items ({len(v3_items)} deep + {len(v1_only)} from V1)")