#!/usr/bin/env python3
"""
HSEQ Intelligence Dashboard
JvG Consultancy — HSEQ Intelligence Monitor v2.0
Built by Kas, AI Operations Director
Architecture: Single-file Flask app (LGCC pattern) — inline CSS, embedded templates, render_template_string
"""

import os
import sys
import json
import sqlite3
import uuid
import re
import urllib.request
import urllib.parse
import ssl
import tempfile
import shutil
from datetime import datetime, date

from flask import (Flask, request, render_template_string, redirect, url_for,
                   jsonify, Response, stream_with_context, send_file)

from docx_gen import generate_docx
from pptx_gen import generate_pptx
from indexer import init_db, index_file, DB_PATH, categorize
from search import (search_documents, get_document, get_categories,
                    get_authors, get_recent_documents, get_stats,
                    get_all_documents, get_db)

app = Flask(__name__)
app.config['MAX_CONTENT_LENGTH'] = 50 * 1024 * 1024  # 50MB upload limit

# ─── Config ──────────────────────────────────────────────────────────────────
BASE_PATH = os.environ.get('BASE_PATH', '')
UPLOAD_DIR = os.path.join(os.path.dirname(__file__), 'uploads')
os.makedirs(UPLOAD_DIR, exist_ok=True)

AI_PROVIDER = os.environ.get('AI_PROVIDER', 'openrouter')
OPENROUTER_URL = os.environ.get('OPENROUTER_URL', 'https://openrouter.ai/api/v1/chat/completions')
OPENROUTER_KEY = os.environ.get('OPENROUTER_API_KEY', '')
OPENROUTER_MODEL = os.environ.get('OPENROUTER_MODEL', 'google/gemini-2.0-flash-001')
AI_API_URL = os.environ.get('AI_API_URL', 'https://api.zai.io/v1/chat/completions')
AI_API_KEY = os.environ.get('AI_API_KEY', '43a6c7e3d7b240daafae006e8488f674.ivYYRRLySgUwgqVE')
AI_MODEL = os.environ.get('AI_MODEL', 'glm-5')

DELIVERABLES_DIR = '/tmp/hseq_deliverables'
ARCHIVE_DIR = '/tmp/hseq_deliverables/archive'
os.makedirs(DELIVERABLES_DIR, exist_ok=True)
os.makedirs(ARCHIVE_DIR, exist_ok=True)

# ─── HSEQ System Prompt ─────────────────────────────────────────────────────
HSEQ_SYSTEM_PROMPT = """Je bent een senior HSEQ (Health, Safety, Environment & Quality) specialist met 20+ jaar ervaring in de zware industrie, specifiek bij BRZO/Seveso-bedrijven (chemie, petrochemie, offshore, opslag van gevaarlijke stoffen).

## Jouw Expertise
- **BRZO 2015** (Besluit risico's zware ongevallen) & Seveso III richtlijn
- **ARIE** (Algemene Richtlijn Industriële Veiligheid)
- **PGS-richtlijnen** (Publieke Gezondheid & Stoffen)
- **Arbowetgeving** & Arbobesluit
- **REACH/CLP** (ECHA) & milieuregelgeving
- **MIR** (Meldplicht Incidenten Gevaarlijke Stoffen)
- **Omgevingswet** & vergunningen
- **Veiligheidsrapportages** & Company Emergency Plans

## Jouw Rol
Je helpt HSEQ Managers bij BRZO-bedrijven met:
1. Compliance-vragen over wet- en regelgeving
2. Interpretatie van richtlijnen en normen
3. Risicobeheersing en veiligheidsadvies
4. Voorbereiding op inspecties en audits
5. Documentatie- en procedurele vragen

## Communicatiestijl
- Professioneel, beknopt, autoritair in je vakgebied
- Gebruik concrete voorbeelden uit de praktijk
- Verwijs naar specifieke wetgeving/artikelen waar mogelijk
- Gebruik bronverwijzingen uit de kennisbank (zie hieronder)
- Taal: Nederlands, technische termen in het Engels waar gebruikelijk

## Belangrijk
- Als je het niet zeker weet, zeg dat eerlijk
- Raadpleeg altijd de kennisbank-bronnen voor feitelijke vragen
- Noem altijd relevante wetgeving of richtlijnen bij je antwoord
- Denk mee als een consultant: wat betekent dit voor de HSEQ Manager in de praktijk?
"""

# ─── AI Functions ────────────────────────────────────────────────────────────
def ai_call(messages, stream=False):
    """Call AI (ZAI, OpenRouter) and return response."""
    if AI_PROVIDER == 'openrouter':
        payload = json.dumps({
            "model": OPENROUTER_MODEL,
            "messages": messages,
            "stream": stream,
            "temperature": 0.7,
            "max_tokens": 8000
        }).encode('utf-8')
        req = urllib.request.Request(OPENROUTER_URL, data=payload, headers={
            "Content-Type": "application/json",
            "Authorization": f"Bearer {OPENROUTER_KEY}",
            "HTTP-Referer": "https://mescalinerabbit.shop",
            "X-Title": "HSEQ Intelligence Dashboard"
        })
        resp = urllib.request.urlopen(req, timeout=60)
        return resp
    else:
        payload = json.dumps({
            "model": AI_MODEL,
            "messages": messages,
            "stream": stream,
            "temperature": 0.7,
            "max_tokens": 8000
        }).encode('utf-8')
        req = urllib.request.Request(AI_API_URL, data=payload, headers={
            "Content-Type": "application/json",
            "Authorization": f"Bearer {AI_API_KEY}"
        })
        ctx = ssl.create_default_context()
        ctx.check_hostname = False
        ctx.verify_mode = ssl.CERT_NONE
        resp = urllib.request.urlopen(req, context=ctx, timeout=120)
        return resp


def ai_complete(messages):
    """Non-streaming AI call, returns text."""
    resp = ai_call(messages, stream=False)
    data = json.loads(resp.read().decode('utf-8'))
    if AI_PROVIDER == 'ollama':
        return data['message']['content']
    return data['choices'][0]['message']['content']



# ─── SOP & Styleguide Context Loader ──────────────────────────────────────

def get_sop_context():
    """Load key SOP/Styleguide sections as persistent advisor context."""
    try:
        with open('/root/projects/jg/MASTER_SOP.md', 'r') as f:
            sop = f.read()
        with open('/root/projects/jg/MASTER_STYLEGUIDE.md', 'r') as f:
            sg = f.read()
        
        # Extract key sections only (not the full 600+ lines)
        sop_sections = []
        for line in sop.split('\n'):
            if line.startswith('## '):
                sop_sections.append(line)
            elif line.strip() and sop_sections:
                sop_sections[-1] += '\n' + line
        
        # Build compact context
        parts = ["## JvG Consultancy — MASTER SOP Referentie\n"]
        # §5 Quality Gates
        if '§5' in sop:
            start = sop.find('## §5')
            end = sop.find('## §6', start)
            if end == -1: end = sop.find('## §7', start)
            parts.append(sop[start:end].strip() + '\n')
        # §9 HSEQ Knowledge Architecture  
        if '§9' in sop:
            start = sop.find('## §9')
            end = sop.find('## §10', start)
            if end == -1: end = len(sop)
            parts.append(sop[start:end].strip() + '\n')
        # §7 Branding
        if '§7' in sop:
            start = sop.find('## §7')
            end = sop.find('## §8', start)
            if end == -1: end = sop.find('## §9', start)
            parts.append(sop[start:end].strip() + '\n')
        
        # Styleguide §1 + §8
        if '§1' in sg:
            start = sg.find('## §1')
            end = sg.find('## §2', start)
            parts.append(sg[start:end].strip() + '\n')
        if '§8' in sg:
            start = sg.find('## §8')
            end = sg.find('## §9', start)
            if end == -1: end = len(sg)
            parts.append(sg[start:end].strip() + '\n')
        
        result = '\n'.join(parts)
        if len(result) > 8000:
            result = result[:8000] + '\n[... afgekort ...]'
        return result
    except Exception as e:
        return f"[SOP niet beschikbaar: {e}]"


def get_rag_context(query, max_chars=15000, category=None):
    """Deep RAG: search knowledge base and return FULL document content as context.
    
    Changed from shallow snippet-based (3 chunks × 800 chars = 2400 per doc)
    to deep retrieval: top 10 documents, full text, max 50k chars total.
    """
    if not query or not query.strip():
        return "", []
    
    import re
    stop_words = {'de', 'het', 'een', 'van', 'zijn', 'is', 'met', 'voor', 'in', 'op', 'bij', 'aan', 'te', 'en', 'of', 'wat', 'hoe', 'welke', 'welk', 'wel', 'zijn', 'niet', 'ook', 'nog', 'als', 'dan', 'maar', 'door', 'tot', 'naar', 'uit', 'over', 'dit', 'dat', 'hier', 'daar', 'kan', 'moet', 'zal', 'zou', 'kun', 'heb', 'ben', 'heb', 'meer', 'alle', 'elk', 'elke', 'veel', 'bij', 'werknemer'}
    
    words = re.findall(r'\b[A-Za-zÀ-ÿ]+\b', query.lower())
    keywords = [w for w in words if w not in stop_words and len(w) > 2]
    
    # Multiple search strategies for maximum recall
    all_results = {}
    search_queries = []
    for kw in keywords[:5]:
        search_queries.append(kw)
    for i in range(min(len(keywords)-1, 4)):
        search_queries.append(f'{keywords[i]} {keywords[i+1]}')
    if len(keywords) >= 2:
        search_queries.append(' '.join(keywords[:3]))
    if len(keywords) >= 3:
        search_queries.append(' '.join(keywords[:5]))
    
    seen = set()
    for sq in search_queries:
        try:
            results = search_documents(sq, page=1, per_page=5)
            for r in results['results']:
                if r['id'] not in seen:
                    all_results[r['id']] = r
                    seen.add(r['id'])
        except:
            pass
    
    # Also search by category if provided
    if category and category.strip():
        try:
            cat_results = search_documents(category, page=1, per_page=5)
            for r in cat_results['results']:
                if r['id'] not in seen:
                    all_results[r['id']] = r
                    seen.add(r['id'])
        except:
            pass
    
    if not all_results:
        return "", []
    
    context_parts = []
    sources = []
    total_chars = 0
    
    # Sort by rank (lower = better in FTS5), take top 10
    sorted_docs = sorted(all_results.items(), key=lambda x: x[1].get('rank', 999))[:10]
    
    for doc_id, r in sorted_docs:
        doc_data = get_document(doc_id)
        if not doc_data:
            continue
        
        doc = doc_data['document']
        title = doc.get('title') or doc.get('filename', 'Onbekend')
        cat = doc.get('category', '')
        
        # Use full_text from document row (complete content, not chunks)
        full_text = doc.get('full_text', '')
        if not full_text and doc_data.get('chunks'):
            full_text = '\n\n'.join(c.get('content', '') for c in doc_data['chunks'])
        
        if not full_text or len(full_text.strip()) < 50:
            continue
        
        # Per-document limit: max 15000 chars to ensure diversity across docs
        per_doc_limit = 15000
        if len(full_text) > per_doc_limit:
            full_text = full_text[:per_doc_limit] + "\n\n[... document afgekort wegens lengte — volledige tekst beschikbaar in kennisbank ...]"
        
        # Build document header
        doc_header = f"### {title}"
        if cat:
            doc_header += f"\nCategorie: {cat}"
        if doc.get('author'):
            doc_header += f"\nAuteur: {doc['author']}"
        if doc.get('pages'):
            doc_header += f"\nPagina's: {doc['pages']}"
        doc_header += "\n"
        
        doc_content = doc_header + full_text
        
        # Check if we can fit this document
        if total_chars + len(doc_content) > max_chars:
            remaining = max_chars - total_chars - len(doc_header) - 100
            if remaining > 2000:
                doc_content = doc_header + full_text[:remaining] + "\n\n[... context afgekort ...]"
            else:
                break
        
        sources.append(f"{title} (cat: {cat})")
        context_parts.append(doc_content)
        total_chars += len(doc_content)
        context_parts.append("")
    
    context = '\n'.join(context_parts)
    return context, sources


# ─── CSS & Layout ────────────────────────────────────────────────────────────
CSS = """
@import url('https://fonts.googleapis.com/css2?family=Inter:wght@300;400;500;600;700&display=swap');
*{margin:0;padding:0;box-sizing:border-box}
body{font-family:'Inter',system-ui,sans-serif;background:#F1F3F5;color:#333;min-height:100vh}
.sidebar{position:fixed;left:0;top:0;bottom:0;width:260px;background:linear-gradient(180deg,#001F3F 0%,#003366 100%);color:#fff;padding:20px 0;z-index:100;transition:transform .3s;overflow-y:auto}
.sidebar .logo{padding:10px 24px 24px;border-bottom:1px solid rgba(255,255,255,.1)}
.sidebar .logo h2{font-size:16px;font-weight:700;margin:0}
.sidebar .logo span{color:#4ECDC4}
.sidebar .logo p{font-size:10px;color:rgba(255,255,255,.5);margin:4px 0 0}
.sidebar nav a{display:flex;align-items:center;gap:10px;padding:11px 24px;color:rgba(255,255,255,.75);text-decoration:none;font-size:13px;font-weight:500;transition:all .2s}
.sidebar nav a:hover,.sidebar nav a.active{background:rgba(255,255,255,.1);color:#fff}
.sidebar nav a .emoji{font-size:17px;width:24px;text-align:center}
.main{margin-left:260px;min-height:100vh}
.header{background:#fff;padding:16px 32px;border-bottom:1px solid #E9ECEF;display:flex;align-items:center;justify-content:space-between;position:sticky;top:0;z-index:50}
.header h1{font-size:18px;font-weight:600;color:#003366}
.header .subtitle{font-size:11px;color:#868E96;margin-top:2px}
.content{padding:24px 32px;max-width:1400px}
.card{background:#fff;border-radius:10px;padding:20px;margin-bottom:16px;box-shadow:0 1px 3px rgba(0,0,0,.06);border:1px solid #E9ECEF}
.card h2{font-size:16px;font-weight:600;color:#003366;margin-bottom:12px}
.stats-grid{display:grid;grid-template-columns:repeat(auto-fit,minmax(200px,1fr));gap:12px}
.stat-card{background:#fff;border-radius:10px;padding:16px;text-align:center;box-shadow:0 1px 3px rgba(0,0,0,.06);border:1px solid #E9ECEF}
.stat-card .value{font-size:28px;font-weight:700;color:#003366}
.stat-card .label{font-size:11px;color:#868E96;margin-top:4px;text-transform:uppercase;letter-spacing:.5px}
.btn{display:inline-flex;align-items:center;gap:6px;padding:8px 16px;border-radius:6px;font-size:13px;font-weight:500;cursor:pointer;border:none;text-decoration:none;transition:all .2s;font-family:inherit}
.btn-primary{background:#003366;color:#fff}.btn-primary:hover{background:#004488}
.btn-outline{background:transparent;color:#003366;border:1px solid #003366}.btn-outline:hover{background:#003366;color:#fff}
.btn-success{background:#2B8A3E;color:#fff}.btn-success:hover{background:#37B24D}
.btn-danger{background:#E03131;color:#fff}.btn-danger:hover{background:#FA5252}
.btn-sm{padding:4px 10px;font-size:11px}
.form-control{border:1px solid #DEE2E6;border-radius:6px;padding:8px 12px;font-size:13px;font-family:inherit;outline:none;width:100%}
.form-control:focus{border-color:#003366;box-shadow:0 0 0 3px rgba(0,51,102,.1)}
table{width:100%;border-collapse:collapse}
th{background:#003366;color:#fff;padding:10px 12px;text-align:left;font-size:12px;font-weight:600;white-space:nowrap}
td{padding:10px 12px;border-bottom:1px solid #E9ECEF;font-size:13px}
tr:hover td{background:#F8F9FA}
.badge{display:inline-block;padding:2px 8px;border-radius:10px;font-size:10px;font-weight:600}
.badge-procedures{background:#D0EBFF;color:#1864AB}
.badge-wetgeving{background:#D3F9D8;color:#2B8A3E}
.badge-scraped{background:#FFF3BF;color:#E67700}
.badge-arbeidsgroepen{background:#FFE8CC;color:#D9480F}
.badge-overig{background:#F1F3F5;color:#495057}
.page-nav{display:flex;gap:4px;justify-content:center;margin-top:16px}
.page-nav a,.page-nav span{display:inline-flex;align-items:center;justify-content:center;min-width:32px;height:32px;padding:0 8px;border-radius:6px;font-size:12px;text-decoration:none;border:1px solid #DEE2E6;color:#495057}
.page-nav a:hover{background:#003366;color:#fff;border-color:#003366}
.page-nav .active{background:#003366;color:#fff;border-color:#003366}
.search-bar{display:flex;gap:8px;margin-bottom:16px}
.search-bar input{flex:1}
.cat-pills{display:flex;flex-wrap:wrap;gap:6px;margin-bottom:16px}
.cat-pill{display:inline-block;padding:5px 12px;border-radius:16px;font-size:11px;font-weight:500;cursor:pointer;text-decoration:none;border:1px solid #DEE2E6;color:#495057;transition:all .2s}
.cat-pill:hover,.cat-pill.active{background:#003366;color:#fff;border-color:#003366}
.empty-state{text-align:center;padding:48px 24px;color:#868E96}
.empty-state .emoji{font-size:48px;margin-bottom:12px}
.empty-state h3{color:#495057;margin-bottom:8px}
.empty-state p{font-size:13px}
@keyframes bounce{0%,80%,100%{transform:translateY(0)}40%{transform:translateY(-6px)}}
@keyframes spin{to{transform:rotate(360deg)}}
.upload-zone{border:2px dashed #CED4DA;border-radius:10px;padding:32px;text-align:center;cursor:pointer;transition:all .2s;background:#FAFBFC}
.upload-zone:hover,.upload-zone.dragover{border-color:#003366;background:#F0F4F8}
.upload-zone .emoji{font-size:36px;margin-bottom:8px}
.upload-zone p{font-size:13px;color:#495057}
.upload-zone small{font-size:11px;color:#868E96}
.upload-progress{height:6px;background:#E9ECEF;border-radius:3px;overflow:hidden;margin-top:12px}
.upload-progress .bar{height:100%;background:#003366;border-radius:3px;transition:width .3s}
/* Chat styles */
#hseqMessages .msg{max-width:88%;padding:12px 16px;border-radius:12px;font-size:13px;line-height:1.65;overflow-wrap:break-word;word-break:break-word}
#hseqMessages .msg.user{align-self:flex-end;background:#003366;color:#fff;border-bottom-right-radius:4px}
#hseqMessages .msg.assistant{align-self:flex-start;background:#fff;color:#333;border:1px solid #E9ECEF;border-bottom-left-radius:4px;box-shadow:0 1px 3px rgba(0,0,0,.04)}
#hseqMessages .msg.assistant h1,#hseqMessages .msg.assistant h2,#hseqMessages .msg.assistant h3{font-size:14px;font-weight:700;color:#003366;margin:12px 0 6px 0}
#hseqMessages .msg.assistant h1:first-child,#hseqMessages .msg.assistant h2:first-child,#hseqMessages .msg.assistant h3:first-child{margin-top:0}
#hseqMessages .msg.assistant p{margin:4px 0}
#hseqMessages .msg.assistant ul,#hseqMessages .msg.assistant ol{margin:4px 0 4px 18px;padding:0}
#hseqMessages .msg.assistant li{margin:3px 0}
#hseqMessages .msg.assistant strong{color:#003366}
#hseqMessages .msg.assistant table{border-collapse:collapse;width:100%;margin:8px 0;font-size:12px}
#hseqMessages .msg.assistant th{background:#003366;color:#fff;padding:6px 10px;text-align:left;font-weight:600}
#hseqMessages .msg.assistant td{padding:5px 10px;border-bottom:1px solid #E9ECEF}
#hseqMessages .msg.assistant tr:nth-child(even) td{background:#F8F9FA}
#hseqMessages .msg.assistant .source-ref{display:block;margin-top:8px;padding-top:6px;border-top:1px solid #DEE2E6;font-size:11px;color:#868E96;font-style:italic}
/* Compliance Tracker badges */
.status-compliant{background:#D3F9D8;color:#2B8A3E}
.status-in_progress{background:#FFF3BF;color:#E67700}
.status-overdue{background:#FFE3E3;color:#E03131}
.status-open{background:#E9ECEF;color:#495057}
.status-not_applicable{background:#F1F3F5;color:#868E96}
.priority-critical{background:#E03131;color:#fff}
.priority-high{background:#FF922B;color:#fff}
.priority-medium{background:#FAB005;color:#333}
.priority-low{background:#E9ECEF;color:#495057}
.deadline-overdue{color:#E03131;font-weight:600}
.deadline-soon{color:#FF922B;font-weight:600}
.compliance-table td{vertical-align:middle}
.compliance-actions{display:flex;gap:4px;flex-wrap:wrap}
.compliance-filters{display:flex;gap:8px;flex-wrap:wrap;margin-bottom:16px;align-items:center}
.compliance-filters select,.compliance-filters input{padding:6px 10px;border:1px solid #DEE2E6;border-radius:6px;font-size:12px}
.editable{cursor:pointer;padding:2px 4px;border-radius:4px;transition:background .2s}
.editable:hover{background:#E9ECEF}
/* Risk Matrix */
.risk-matrix{display:grid;grid-template-columns:40px repeat(5,1fr);gap:2px;max-width:500px;margin:0 auto}
.risk-matrix-cell{aspect-ratio:1;display:flex;align-items:center;justify-content:center;font-size:12px;font-weight:600;border-radius:4px;cursor:pointer;transition:all .2s;position:relative}
.risk-matrix-cell:hover{transform:scale(1.05);z-index:10;box-shadow:0 4px 12px rgba(0,0,0,.2)}
.risk-matrix .label-x,.risk-matrix .label-y{background:#F8F9FA;color:#868E96;font-size:10px;font-weight:500}
.risk-low{background:#D3F9D8;color:#2B8A3E}
.risk-medium{background:#FFF3BF;color:#E67700}
.risk-high{background:#FFE8CC;color:#D9480F}
.risk-extreme{background:#FFE3E3;color:#E03131}
.risk-empty{background:#F1F3F5;color:#ADB5BD}
/* PDCA Board */
.pdca-board{display:grid;grid-template-columns:repeat(4,1fr);gap:12px;min-height:500px}
.pdca-column{background:#F8F9FA;border-radius:8px;padding:12px;min-height:400px}
.pdca-column h3{font-size:13px;font-weight:600;margin-bottom:12px;padding-bottom:8px;border-bottom:2px solid}
.pdca-column.plan h3{border-color:#3B82F6;color:#3B82F6}
.pdca-column.do h3{border-color:#F59E0B;color:#F59E0B}
.pdca-column.check h3{border-color:#10B981;color:#10B981}
.pdca-column.act h3{border-color:#8B5CF6;color:#8B5CF6}
.pdca-card{background:#fff;border-radius:6px;padding:10px;margin-bottom:8px;border:1px solid #E9ECEF;font-size:12px;cursor:pointer;transition:all .2s}
.pdca-card:hover{box-shadow:0 2px 8px rgba(0,0,0,.1);transform:translateY(-1px)}
.pdca-card .title{font-size:11px;font-weight:600;color:#003366;margin-bottom:4px}
.pdca-card .meta{font-size:10px;color:#868E96}
.pdca-card .progress-wrapper{margin-top:6px}
.pdca-card .progress-bar{height:4px;background:#E9ECEF;border-radius:2px;overflow:hidden}
.pdca-card .progress-fill{height:100%;background:#2B8A3E;border-radius:2px;transition:width .3s}
.risk-level-low{background:#D3F9D8;color:#2B8A3E}
.risk-level-medium{background:#FFF3BF;color:#E67700}
.risk-level-high{background:#FFE8CC;color:#D9480F}
.risk-level-extreme{background:#FFE3E3;color:#E03131}
.phase-badge{display:inline-block;padding:2px 8px;border-radius:4px;font-size:9px;font-weight:600;text-transform:uppercase}
.phase-plan{background:#3B82F6;color:#fff}
.phase-do{background:#2B8A3E;color:#fff}
.phase-check{background:#F59E0B;color:#fff}
.phase-act{background:#845EF7;color:#fff}
/* Mobile */
@media(max-width:768px){
  .sidebar{transform:translateX(-100%)}
  .sidebar.open{transform:translateX(0)}
  .main{margin-left:1}
  .content{padding:16px}
  .stats-grid{grid-template-columns:repeat(2,1fr)}
  .pdca-board{grid-template-columns:1fr}
  .risk-matrix-cell{height:30px}
}
.pdca-card{background:#fff;border-radius:6px;padding:10px;margin-bottom:8px;border:1px solid #E9ECEF;font-size:12px;cursor:pointer;transition:all .2s}
.pdca-card:hover{box-shadow:0 2px 8px rgba(0,0,0,.1);transform:translateY(-1px)}
.pdca-card .title{font-weight:600;color:#003366;margin-bottom:4px}
.pdca-card .meta{font-size:10px;color:#868E96}
.pdca-card .progress-bar{height:4px;background:#E9ECEF;border-radius:2px;margin-top:6px;overflow:hidden}
.pdca-card .progress-fill{height:100%;background:#2B8A3E;transition:width .3s}
/* Risk Level badges */
.risk-level-low{background:#D3F9D8;color:#2B8A3E}
.risk-level-medium{background:#FFF3BF;color:#E67700}
.risk-level-high{background:#FFE8CC;color:#D9480F}
.risk-level-extreme{background:#FFE3E3;color:#E03131}
/* Phase badges */
.phase-plan{background:#D0EBFF;color:#1864AB}
.phase-do{background:#FFF3BF;color:#E67700}
.phase-check{background:#D3F9D8;color:#2B8A3E}
.phase-act{background:#E5DBFF;color:#6741D9}
/* Risk detail modal */
.risk-detail{background:#fff;border-radius:10px;max-width:800px;width:100%;max-height:90vh;overflow-y:auto}
.risk-detail-header{background:linear-gradient(135deg,#003366,#005580);color:#fff;padding:20px;border-radius:10px 10px 0 0}
.risk-detail-body{padding:20px}
/* Progress indicator */
.progress-indicator{display:flex;align-items:center;gap:4px}
.progress-indicator .dot{width:8px;height:8px;border-radius:50%;background:#E9ECEF}
.progress-indicator .dot.filled{background:#2B8A3E}
/* Mobile */
@media(max-width:768px){
  .sidebar{transform:translateX(-100%)}
  .sidebar.open{transform:translateX(0)}
  .main{margin-left:0}
  .content{padding:16px}
  .stats-grid{grid-template-columns:repeat(2,1fr)}
  .pdca-board{grid-template-columns:1fr;min-height:auto}
  .pdca-column{min-height:200px}
}
"""

# ─── Page Template (LGCC pattern) ───────────────────────────────────────────
PAGE_TEMPLATE = """<!DOCTYPE html>
<html lang="nl">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width,initial-scale=1.0">
<title>{{page_title}} — HSEQ Intelligence Dashboard</title>
<style>{{css}}</style>
</head>
<body>
<!-- Sidebar -->
<div class="sidebar" id="sidebar">
  <div class="logo">
    <h2><span>🛡️</span> HSEQ Intelligence</h2>
    <p>JvG Consultancy — Dashboard v2.0</p>
  </div>
  <nav>
    <a href="{{BASE_PATH}}/" class="{{'active' if active=='dashboard' else ''}}"><span class="emoji">📊</span> Dashboard</a>
    <div style="padding:12px 24px 6px;font-size:10px;text-transform:uppercase;letter-spacing:1px;color:rgba(255,255,255,.4);margin-top:8px">Diensten</div>
    <a href="{{BASE_PATH}}/services/arbo" class="{{'active' if active=='arbo' else ''}}"><span class="emoji">👷</span> Arbo & Veiligheid</a>
    <a href="{{BASE_PATH}}/services/brzo" class="{{'active' if active=='brzo' else ''}}"><span class="emoji">🏭</span> BRZO / Seveso</a>
    <a href="{{BASE_PATH}}/services/milieu" class="{{'active' if active=='milieu' else ''}}"><span class="emoji">🌿</span> Milieu & Omgeving</a>
    <a href="{{BASE_PATH}}/services/kam" class="{{'active' if active=='kam' else ''}}"><span class="emoji">📋</span> KAM Management</a>
    <a href="{{BASE_PATH}}/services/training" class="{{'active' if active=='training' else ''}}"><span class="emoji">🎓</span> Training & Opleiding</a>
    <a href="{{BASE_PATH}}/services/energy" class="{{'active' if active=='energy' else ''}}"><span class="emoji">⚡</span> Energie & Proces</a>
    <div style="padding:12px 24px 6px;font-size:10px;text-transform:uppercase;letter-spacing:1px;color:rgba(255,255,255,.4);margin-top:8px">Tools</div>
    <a href="{{BASE_PATH}}/knowledge" class="{{'active' if active=='knowledge' else ''}}"><span class="emoji">📚</span> Kennisbank</a>
    <a href="{{BASE_PATH}}/upload" class="{{'active' if active=='upload' else ''}}"><span class="emoji">📤</span> Upload</a>
    <a href="{{BASE_PATH}}/advisor" class="{{'active' if active=='advisor' else ''}}"><span class="emoji">🧠</span> HSEQ Advisor</a>
        <a href="{{BASE_PATH}}/compliance" class="{{'active' if active=='compliance' else ''}}"><span class="emoji">⚖️</span> Compliance</a>
        <a href="{{BASE_PATH}}/risks" class="{{'active' if active=='risks' else ''}}"><span class="emoji">⚠️</span> Risico's</a>
        <a href="{{BASE_PATH}}/actions" class="{{'active' if active=='actions' else ''}}"><span class="emoji">📋</span> Acties</a>
        <a href="{{BASE_PATH}}/moc" class="{{'active' if active=='moc' else ''}}"><span class="emoji">🔄</span> MoC</a>
        <a href="{{BASE_PATH}}/ptw" class="{{'active' if active=='ptw' else ''}}"><span class="emoji">🔒</span> Vergunningen</a>
        <a href="{{BASE_PATH}}/contractors" class="{{'active' if active=='contractors' else ''}}"><span class="emoji">👷</span> Aannemers</a>
        <a href="{{BASE_PATH}}/incidents" class="{{'active' if active=='incidents' else ''}}"><span class="emoji">🚨</span> Incidenten</a>
        <a href="{{BASE_PATH}}/environment" class="{{'active' if active=='environment' else ''}}"><span class="emoji">🌱</span> Milieu</a>
        <a href="{{BASE_PATH}}/audits" class="{{'active' if active=='audits' else ''}}"><span class="emoji">📋</span> Audit</a>
    </nav>
</div>

<!-- Mobile menu toggle -->
<button onclick="document.getElementById('sidebar').classList.toggle('open')" style="display:none;position:fixed;top:12px;left:12px;z-index:200;background:#003366;color:#fff;border:none;border-radius:8px;width:40px;height:40px;font-size:18px;cursor:pointer" id="menuBtn">☰</button>

<!-- Main -->
<div class="main">
  <div class="header">
    <div>
      <h1>{{page_title}}</h1>
      {% if page_subtitle %}<div class="subtitle">{{page_subtitle}}</div>{% endif %}
    </div>
    <div style="font-size:12px;color:#868E96">{{now}}</div>
  </div>
  <div class="content">
    {{page_body|safe}}
  </div>
</div>

<script>
// Mobile detection
if(window.innerWidth<=768){document.getElementById('menuBtn').style.display='block'}
window.addEventListener('resize',function(){document.getElementById('menuBtn').style.display=window.innerWidth<=768?'block':'none';if(window.innerWidth>768)document.getElementById('sidebar').classList.remove('open')});
</script>
</body>
</html>
"""


# ─── Helper: page() ─────────────────────────────────────────────────────────
def page(body_html, active='dashboard', page_title='Dashboard', page_subtitle=None, **kwargs):
    """Render page with layout template (LGCC pattern)."""
    if page_subtitle is None:
        page_subtitle = 'JvG Consultancy — HSEQ Intelligence Dashboard'
    kwargs['BASE_PATH'] = BASE_PATH
    body_rendered = render_template_string(body_html, **kwargs)
    kwargs['active'] = active
    kwargs['now'] = datetime.now().strftime('%d %B %Y')
    kwargs['css'] = CSS
    kwargs['page_body'] = body_rendered
    kwargs['page_title'] = page_title
    kwargs['page_subtitle'] = page_subtitle
    kwargs['BASE_PATH'] = BASE_PATH
    return render_template_string(PAGE_TEMPLATE, **kwargs)


# ─── Init DB ────────────────────────────────────────────────────────────────
@app.before_request
def ensure_db():
    if not os.path.exists(DB_PATH):
        init_db()
    # Initialize compliance module
    try:
        init_db_compliance()
        seed_compliance_data()
    except Exception as e:
        pass  # Fail silently to not break app
    # Initialize risk & action module
    try:
        init_db_risks()
    except Exception as e:
        pass
    # Initialize VBS module
    try:
        init_db_vbs()
    except Exception as e:
        pass  # Fail silently to not break app
    # Initialize incident & environment module
    try:
        init_db_incidents()
    except Exception as e:
        pass
    # Initialize audit & VKA module
    try:
        init_db_audits()
    except Exception as e:
        pass


# ═══════════════════════════════════════════════════════════════════════════
# DASHBOARD
# ═══════════════════════════════════════════════════════════════════════════
@app.route('/')
def dashboard():
    stats = get_stats()
    categories = get_categories()
    recent = get_recent_documents(limit=8)
    return page("""

<!-- Stats -->
<div class="stats-grid">
  <div class="stat-card">
    <div class="value">{{stats['total_documents']}}</div>
    <div class="label">Documenten</div>
  </div>
  <div class="stat-card">
    <div class="value">{{stats['total_categories']}}</div>
    <div class="label">Categorieën</div>
  </div>
  <div class="stat-card">
    <div class="value">{{'%d' % (stats['total_pages'] or 0)}}</div>
    <div class="label">Totale Pagina's</div>
  </div>
  <div class="stat-card">
    <div class="value">{{'%.1f MB' % ((stats['total_size'] or 0) / 1048576)}}</div>
    <div class="label">Totale Grootte</div>
  </div>
</div>

<!-- Categorieën -->
<div class="card">
  <h2>📂 Categorieën</h2>
  <div class="cat-pills">
    {% for cat in categories %}
    <a href="{{BASE_PATH}}/knowledge?category={{cat['category']|urlencode}}" class="cat-pill">{{cat['category']}} ({{cat['count']}})</a>
    {% endfor %}
  </div>
</div>

<!-- Recente documenten -->
<div class="card">
  <h2>🕐 Recente Documenten</h2>
  {% if recent %}
  <table>
    <thead><tr><th>Document</th><th>Categorie</th><th>Pag.</th><th>Grootte</th><th>Geïndexeerd</th></tr></thead>
    <tbody>
    {% for doc in recent %}
    <tr>
      <td><a href="{{BASE_PATH}}/document/{{doc['id']}}" style="color:#003366;font-weight:500;text-decoration:none">{{doc.get('title') or doc.get('filename','—')}}</a></td>
      <td><span class="badge badge-{{doc['category'].lower()|replace(' ','_')|replace('& ','') if doc.get('category') else 'overig'}}">{{doc.get('category','—')}}</span></td>
      <td>{{doc.get('pages','—')}}</td>
      <td>{{'%.0f KB' % ((doc.get('file_size',0) or 0)/1024) if doc.get('file_size') else '—'}}</td>
      <td style="font-size:11px;color:#868E96">{{doc.get('indexed_date','')[:10] if doc.get('indexed_date') else '—'}}</td>
    </tr>
    {% endfor %}
    </tbody>
  </table>
  <div style="margin-top:12px"><a href="{{BASE_PATH}}/knowledge" class="btn btn-outline">Bekijk alle documenten →</a></div>
  {% else %}
  <div class="empty-state"><div class="emoji">📭</div><h3>Nog geen documenten</h3><p>Upload PDF's om te beginnen.</p></div>
  {% endif %}
</div>

<!-- Diensten Grid (KWA-stijl) -->
<div class="card">
  <h2>🔧 Onze Diensten</h2>
  <div style="display:grid;grid-template-columns:repeat(auto-fill,minmax(200px,1fr));gap:10px">
    <a href="{{BASE_PATH}}/services/arbo" class="card" style="text-decoration:none;cursor:pointer;border-left:3px solid #003366;transition:all .2s" onmouseover="this.style.transform='translateY(-2px)';this.style.boxShadow='0 4px 12px rgba(0,51,102,.12)'" onmouseout="this.style.transform='';this.style.boxShadow=''"><div style="font-size:20px;margin-bottom:4px">👷</div><h3 style="font-size:13px;color:#003366;margin:0 0 4px">Arbo & Veiligheid</h3><p style="font-size:11px;color:#868E96;margin:0">RI&E, arbeidshygiëne, PBM</p></a>
    <a href="{{BASE_PATH}}/services/brzo" class="card" style="text-decoration:none;cursor:pointer;border-left:3px solid #E03131;transition:all .2s" onmouseover="this.style.transform='translateY(-2px)';this.style.boxShadow='0 4px 12px rgba(224,49,49,.12)'" onmouseout="this.style.transform='';this.style.boxShadow=''"><div style="font-size:20px;margin-bottom:4px">🏭</div><h3 style="font-size:13px;color:#E03131;margin:0 0 4px">BRZO / Seveso</h3><p style="font-size:11px;color:#868E96;margin:0">Veiligheidsrapport, QRA, ARIE</p></a>
    <a href="{{BASE_PATH}}/services/milieu" class="card" style="text-decoration:none;cursor:pointer;border-left:3px solid #2B8A3E;transition:all .2s" onmouseover="this.style.transform='translateY(-2px)';this.style.boxShadow='0 4px 12px rgba(43,138,62,.12)'" onmouseout="this.style.transform='';this.style.boxShadow=''"><div style="font-size:20px;margin-bottom:4px">🌿</div><h3 style="font-size:13px;color:#2B8A3E;margin:0 0 4px">Milieu & Omgeving</h3><p style="font-size:11px;color:#868E96;margin:0">Vergunningen, emissies, MER</p></a>
    <a href="{{BASE_PATH}}/services/kam" class="card" style="text-decoration:none;cursor:pointer;border-left:3px solid #4263EB;transition:all .2s" onmouseover="this.style.transform='translateY(-2px)';this.style.boxShadow='0 4px 12px rgba(66,99,235,.12)'" onmouseout="this.style.transform='';this.style.boxShadow=''"><div style="font-size:20px;margin-bottom:4px">📋</div><h3 style="font-size:13px;color:#4263EB;margin:0 0 4px">KAM Management</h3><p style="font-size:11px;color:#868E96;margin:0">ISO, audits, compliance</p></a>
    <a href="{{BASE_PATH}}/services/training" class="card" style="text-decoration:none;cursor:pointer;border-left:3px solid #F76707;transition:all .2s" onmouseover="this.style.transform='translateY(-2px)';this.style.boxShadow='0 4px 12px rgba(247,103,7,.12)'" onmouseout="this.style.transform='';this.style.boxShadow=''"><div style="font-size:20px;margin-bottom:4px">🎓</div><h3 style="font-size:13px;color:#F76707;margin:0 0 4px">Training & Opleiding</h3><p style="font-size:11px;color:#868E96;margin:0">Toolbox talks, VCA, e-learning</p></a>
    <a href="{{BASE_PATH}}/services/energy" class="card" style="text-decoration:none;cursor:pointer;border-left:3px solid #F59F00;transition:all .2s" onmouseover="this.style.transform='translateY(-2px)';this.style.boxShadow='0 4px 12px rgba(245,159,0,.12)'" onmouseout="this.style.transform='';this.style.boxShadow=''"><div style="font-size:20px;margin-bottom:4px">⚡</div><h3 style="font-size:13px;color:#F59F00;margin:0 0 4px">Energie & Proces</h3><p style="font-size:11px;color:#868E96;margin:0">Energieaudit, procesanalyse</p></a>
  </div>
</div>

""", page_title='Dashboard', active='dashboard', stats=stats, categories=categories, recent=recent)


# ═══════════════════════════════════════════════════════════════════════════
# KNOWLEDGE BASE (Browse + Search)
# ═══════════════════════════════════════════════════════════════════════════
@app.route('/knowledge')
def knowledge():
    q = request.args.get('q', '').strip()
    category = request.args.get('category', '').strip()
    page_num = request.args.get('page', 1, type=int)
    if page_num < 1: page_num = 1

    categories = get_categories()

    if q:
        results = search_documents(q, category=category or None, page=page_num, per_page=20)
        is_search = True
    else:
        results = get_all_documents(category=category or None, page=page_num, per_page=20)
        is_search = False

    return page("""

<!-- Search -->
<div class="search-bar">
  <form method="get" action="{{BASE_PATH}}/knowledge" style="display:flex;gap:8px;flex:1">
    <input type="text" name="q" value="{{q}}" placeholder="Zoek in kennisbank..." class="form-control" style="flex:1">
    <button type="submit" class="btn btn-primary">🔍 Zoeken</button>
    {% if q %}
    <a href="{{BASE_PATH}}/knowledge" class="btn btn-outline">✕</a>
    {% endif %}
  </form>
</div>

<!-- Categorie filter -->
<div class="cat-pills">
  <a href="{{BASE_PATH}}/knowledge{% if q %}?q={{q}}{% endif %}" class="cat-pill {{'active' if not category else ''}}">Alle ({{results['total']}})</a>
  {% for cat in categories %}
  <a href="{{BASE_PATH}}/knowledge?category={{cat['category']|urlencode}}{% if q %}&q={{q}}{% endif %}" class="cat-pill {{'active' if category == cat['category'] else ''}}">{{cat['category']}} ({{cat['count']}})</a>
  {% endfor %}
</div>

<!-- Results -->
<div class="card">
  {% if is_search %}
  <h2>🔍 Zoekresultaten: "{{q}}" — {{results['total']}} resultaten</h2>
  {% else %}
  <h2>📚 Kennisbank — {{results['total']}} documenten</h2>
  {% endif %}

  {% if results['results'] %}
  <table>
    <thead><tr><th>Document</th><th>Categorie</th><th>Auteur</th><th>Pag.</th><th>Preview</th></tr></thead>
    <tbody>
    {% for doc in results['results'] %}
    <tr>
      <td><a href="{{BASE_PATH}}/document/{{doc['id']}}{% if q %}?q={{q}}{% endif %}" style="color:#003366;font-weight:500;text-decoration:none">{{doc.get('title') or doc.get('filename','—')}}</a></td>
      <td><span class="badge badge-overig">{{doc.get('category','—')}}</span></td>
      <td style="font-size:12px">{{doc.get('author','—') or '—'}}</td>
      <td>{{doc.get('pages','—')}}</td>
      <td style="font-size:11px;color:#868E96;max-width:250px;overflow:hidden;text-overflow:ellipsis;white-space:nowrap">{{doc.get('preview','')[:100] if doc.get('preview') else (doc.get('snippet','')[:100] if doc.get('snippet') else '')}}</td>
    </tr>
    {% endfor %}
    </tbody>
  </table>

  <!-- Pagination -->
  {% if results['pages'] > 1 %}
  <div class="page-nav">
    {% if results['page'] > 1 %}
    <a href="{{BASE_PATH}}/knowledge?page={{results['page']-1}}{% if q %}&q={{q}}{% endif %}{% if category %}&category={{category}}{% endif %}">←</a>
    {% endif %}
    {% for p in range(1, results['pages']+1) %}
      {% if p == results['page'] %}
      <span class="active">{{p}}</span>
      {% elif p <= 3 or p >= results['pages']-2 or (p >= results['page']-1 and p <= results['page']+1) %}
      <a href="{{BASE_PATH}}/knowledge?page={{p}}{% if q %}&q={{q}}{% endif %}{% if category %}&category={{category}}{% endif %}">{{p}}</a>
      {% elif p == 4 or p == results['pages']-3 %}
      <span>…</span>
      {% endif %}
    {% endfor %}
    {% if results['page'] < results['pages'] %}
    <a href="{{BASE_PATH}}/knowledge?page={{results['page']+1}}{% if q %}&q={{q}}{% endif %}{% if category %}&category={{category}}{% endif %}">→</a>
    {% endif %}
  </div>
  {% endif %}

  {% else %}
  <div class="empty-state">
    <div class="emoji">{{'🔍' if q else '📭'}}</div>
    <h3>{{'Geen resultaten gevonden' if q else 'Nog geen documenten'}}</h3>
    <p>{{'Probeer een andere zoekterm of breid de zoekopdracht uit.' if q else 'Upload PDF\\'s om te beginnen.'}}</p>
  </div>
  {% endif %}
</div>

""", page_title='Kennisbank', active='knowledge', q=q, category=category,
   categories=categories, results=results, is_search=bool(q), BASE_PATH=BASE_PATH)


# ═══════════════════════════════════════════════════════════════════════════
# DOCUMENT VIEWER
# ═══════════════════════════════════════════════════════════════════════════
@app.route('/document/<int:doc_id>')
def document(doc_id):
    data = get_document(doc_id)
    if not data:
        return page('<div class="empty-state"><div class="emoji">❌</div><h3>Document niet gevonden</h3></div>'), 404
    doc = data['document']
    chunks = data['chunks']
    highlight_q = request.args.get('q', '').strip()
    return page("""

<div style="margin-bottom:16px">
  <a href="{{BASE_PATH}}/knowledge" class="btn btn-outline btn-sm">← Terug naar kennisbank</a>
</div>

<div class="card">
  <h2>{{doc.get('title') or doc.get('filename','—')}}</h2>
  <div style="display:flex;gap:12px;flex-wrap:wrap;margin:12px 0;font-size:12px;color:#868E96">
    {% if doc.get('filename') %}<span>📄 {{doc['filename']}}</span>{% endif %}
    {% if doc.get('author') %}<span>✍️ {{doc['author']}}</span>{% endif %}
    {% if doc.get('category') %}<span>📂 {{doc['category']}}</span>{% endif %}
    {% if doc.get('pages') %}<span>📑 {{doc['pages']}} pagina's</span>{% endif %}
    {% if doc.get('file_size') %}<span>💾 {{'%.0f KB' % (doc['file_size']/1024)}}</span>{% endif %}
    {% if doc.get('indexed_date') %}<span>🕐 Geïndexeerd: {{doc['indexed_date'][:10]}}</span>{% endif %}
  </div>
</div>

<!-- Chunks / Content -->
{% for chunk in chunks %}
<div class="card">
  <h3 style="font-size:14px;color:#003366;margin-bottom:8px">{{chunk.get('title','') or 'Chunk ' + (chunk.get('chunk_index',loop.index)|string)}}</h3>
  <div style="font-size:13px;line-height:1.7;color:#333;white-space:pre-wrap;max-height:600px;overflow-y:auto">{{chunk.get('content','')|safe}}</div>
</div>
{% endfor %}

{% if not chunks %}
<div class="card">
  <div style="font-size:13px;line-height:1.7;color:#333;white-space:pre-wrap;max-height:800px;overflow-y:auto">{{doc.get('full_text','Geen content beschikbaar.')}}</div>
</div>
{% endif %}

""", page_title=doc.get('title', doc.get('filename', 'Document')),
   active='knowledge', doc=doc, chunks=chunks, highlight_q=highlight_q, BASE_PATH=BASE_PATH)


# ═══════════════════════════════════════════════════════════════════════════
# UPLOAD
# ═══════════════════════════════════════════════════════════════════════════
@app.route('/upload')
def upload_page():
    return page("""

<div class="card">
  <h2>📤 Document Upload</h2>
  <p style="font-size:13px;color:#868E96;margin-bottom:16px">Upload PDF-documenten om toe te voegen aan de HSEQ Kennisbank. Documenten worden automatisch geïndexeerd en doorzoekbaar.</p>

  <div class="upload-zone" id="dropZone" onclick="document.getElementById('fileInput').click()">
    <div class="emoji">📁</div>
    <p>Sleep PDF-bestanden hierheen of klik om te selecteren</p>
    <small>Max. 50 MB per bestand • PDF-formaat</small>
    <input type="file" id="fileInput" accept=".pdf" multiple style="display:none" onchange="handleFiles(this.files)">
  </div>

  <div id="uploadProgress" style="display:none;margin-top:16px">
    <div class="upload-progress"><div class="bar" id="progressBar" style="width:0%"></div></div>
    <p id="uploadStatus" style="font-size:12px;color:#868E96;margin-top:8px">Bezig met uploaden...</p>
  </div>

  <div id="uploadResults" style="margin-top:16px"></div>
</div>

<script>
var BASE = '{{BASE_PATH}}';

// Drag & drop
var dropZone = document.getElementById('dropZone');
['dragenter','dragover'].forEach(function(e){
  dropZone.addEventListener(e, function(ev){ev.preventDefault();dropZone.classList.add('dragover')});
});
['dragleave','drop'].forEach(function(e){
  dropZone.addEventListener(e, function(ev){ev.preventDefault();dropZone.classList.remove('dragover')});
});
dropZone.addEventListener('drop', function(ev){
  var files = ev.dataTransfer.files;
  handleFiles(files);
});

function handleFiles(files){
  if(!files || !files.length) return;
  var formData = new FormData();
  for(var i=0;i<files.length;i++){
    if(files[i].type !== 'application/pdf'){alert(files[i].name + ' is geen PDF.');continue}
    formData.append('files', files[i]);
  }
  if(!formData.has('files')) return;

  document.getElementById('uploadProgress').style.display = 'block';
  document.getElementById('progressBar').style.width = '30%';
  document.getElementById('uploadStatus').textContent = 'Bezig met uploaden...';
  document.getElementById('uploadResults').innerHTML = '';

  var xhr = new XMLHttpRequest();
  xhr.open('POST', BASE + '/api/upload');
  xhr.onload = function(){
    document.getElementById('progressBar').style.width = '100%';
    try {
      var data = JSON.parse(xhr.responseText);
      if(data.success){
        document.getElementById('uploadStatus').innerHTML = '<span style="color:#2B8A3E">✅ ' + (data.indexed||0) + ' documenten geïndexeerd</span>';
        var html = '<div class="card" style="margin-top:12px"><h3 style="font-size:14px">Upload Resultaat</h3><table><thead><tr><th>Bestand</th><th>Status</th><th>Categorie</th></tr></thead><tbody>';
        (data.results||[]).forEach(function(r){
          html += '<tr><td>'+r.filename+'</td><td>'+(r.success?'<span style="color:#2B8A3E">✅ OK</span>':'<span style="color:#E03131">❌ '+r.error+'</span>')+'</td><td>'+(r.category||'—')+'</td></tr>';
        });
        html += '</tbody></table></div>';
        document.getElementById('uploadResults').innerHTML = html;
      } else {
        document.getElementById('uploadStatus').innerHTML = '<span style="color:#E03131">❌ ' + (data.error||'Upload mislukt') + '</span>';
      }
    } catch(e){
      document.getElementById('uploadStatus').textContent = 'Upload voltooid.';
    }
  };
  xhr.onerror = function(){
    document.getElementById('uploadStatus').innerHTML = '<span style="color:#E03131">❌ Netwerkfout</span>';
  };
  xhr.upload.onprogress = function(e){
    if(e.lengthComputable){
      document.getElementById('progressBar').style.width = Math.round(e.loaded/e.total*70) + '%';
    }
  };
  xhr.send(formData);
}
</script>

""", page_title='Upload', active='upload', BASE_PATH=BASE_PATH)


@app.route('/api/upload', methods=['POST'])
def api_upload():
    """Handle PDF upload and indexing."""
    if 'files' not in request.files:
        return jsonify({'success': False, 'error': 'Geen bestanden ontvangen'}), 400

    files = request.files.getlist('files')
    if not files:
        return jsonify({'success': False, 'error': 'Geen bestanden geselecteerd'}), 400

    results = []
    indexed = 0
    init_db()

    for f in files:
        if not f.filename or not f.filename.lower().endswith('.pdf'):
            results.append({'filename': f.filename, 'success': False, 'error': 'Geen PDF'})
            continue

        # Save to temp file
        temp_path = os.path.join(UPLOAD_DIR, f.filename)
        try:
            f.save(temp_path)
            # Index the file
            result = index_file(temp_path, original_filename=f.filename)
            if result and result.get('success'):
                indexed += 1
                results.append({
                    'filename': f.filename,
                    'success': True,
                    'category': result.get('category', categorize(f.filename)),
                    'pages': result.get('pages', 0)
                })
            else:
                results.append({'filename': f.filename, 'success': False, 'error': result.get('error', 'Indexering mislukt') if result else 'Onbekende fout'})
        except Exception as e:
            results.append({'filename': f.filename, 'success': False, 'error': str(e)})
        finally:
            if os.path.exists(temp_path):
                os.remove(temp_path)

    return jsonify({
        'success': indexed > 0,
        'indexed': indexed,
        'total': len(files),
        'results': results
    })


# ═══════════════════════════════════════════════════════════════════════════
# HSEQ ADVISOR (RAG Chat)
# ═══════════════════════════════════════════════════════════════════════════
@app.route('/advisor')
def advisor():
    return page("""

<div class="card" style="grid-column:1/-1">
  <div style="display:flex;align-items:center;gap:12px;margin-bottom:16px">
    <div style="width:48px;height:48px;border-radius:50%;background:linear-gradient(135deg,#003366,#4ECDC4);display:flex;align-items:center;justify-content:center;font-size:24px">🧠</div>
    <div>
      <h2 style="margin:0">HSEQ Advisor</h2>
      <p style="margin:0;font-size:12px;color:#868E96">Je senior HSEQ specialist — gestructureerd advies op basis van BRZO, Seveso, Arbo & de kennisbank</p>
    </div>
  </div>

  <!-- Categorie tabs -->
  <div style="display:flex;gap:6px;flex-wrap:wrap;margin-bottom:16px">
    <button class="btn btn-outline btn-sm hseq-cat" onclick="setHseqCat(this,'')" style="font-size:11px">📋 Algemeen</button>
    <button class="btn btn-outline btn-sm hseq-cat" onclick="setHseqCat(this,'brzo')" style="font-size:11px">🏭 BRZO/Seveso</button>
    <button class="btn btn-outline btn-sm hseq-cat" onclick="setHseqCat(this,'arbo')" style="font-size:11px">👷 Arbowetgeving</button>
    <button class="btn btn-outline btn-sm hseq-cat" onclick="setHseqCat(this,'gevaarlijk')" style="font-size:11px">☢️ Gevaarlijke Stoffen</button>
    <button class="btn btn-outline btn-sm hseq-cat" onclick="setHseqCat(this,'milieu')" style="font-size:11px">🌿 Milieu & Omgeving</button>
    <button class="btn btn-outline btn-sm hseq-cat" onclick="setHseqCat(this,'ongeval')" style="font-size:11px">🚨 Incidenten & Ongelukken</button>
    <button class="btn btn-outline btn-sm hseq-cat" onclick="setHseqCat(this,'training')" style="font-size:11px">🎓 Training & Opleiding</button>
  </div>

  <!-- Chat -->
  <div style="border:1px solid #E9ECEF;border-radius:10px;overflow:hidden;background:#fff">
    <!-- Header -->
    <div style="background:linear-gradient(135deg,#003366,#005580);color:#fff;padding:12px 16px;display:flex;justify-content:space-between;align-items:center">
      <div style="display:flex;align-items:center;gap:8px">
        <span style="font-size:16px">🧠</span>
        <span style="font-size:13px;font-weight:600">HSEQ Advisor Chat</span>
        <span id="hseqCatBadge" style="font-size:10px;background:rgba(255,255,255,.2);padding:2px 8px;border-radius:10px;display:none"></span>
      </div>
      <div style="display:flex;gap:6px">
        <button onclick="clearHseqChat()" style="background:rgba(255,255,255,.15);border:none;color:#fff;border-radius:4px;padding:4px 10px;font-size:11px;cursor:pointer">🔄 Nieuw</button>
        <button onclick="exportHseqChat()" style="background:rgba(255,255,255,.15);border:none;color:#fff;border-radius:4px;padding:4px 10px;font-size:11px;cursor:pointer">📥 Export</button>
      </div>
    </div>

    <!-- Messages -->
    <div id="hseqMessages" style="height:450px;overflow-y:auto;padding:16px;display:flex;flex-direction:column;gap:12px;background:#FAFBFC">
      <div class="msg assistant" style="background:#fff;border:1px solid #E9ECEF;box-shadow:0 1px 3px rgba(0,0,0,.06)">
        <div style="font-weight:600;margin-bottom:8px;color:#003366">🧠 HSEQ Advisor</div>
        <div style="font-size:13px;line-height:1.6">
          Welkom! Ik ben je senior HSEQ specialist, gespecialiseerd in BRZO/Seveso-bedrijven in de zware industrie. Kies een <strong>categorie</strong> of stel direct je vraag.<br><br>
          💡 <em>Tip: Ik raadpleeg automatisch de kennisbank voor relevante documenten en wetgeving.</em>
        </div>
      </div>
    </div>

    <!-- Typing -->
    <div id="hseqTyping" style="display:none;padding:8px 16px;background:#FAFBFC;border-top:1px solid #F1F3F5">
      <div style="display:flex;align-items:center;gap:6px;font-size:12px;color:#868E96">
        <span style="display:inline-flex;gap:3px"><span style="width:6px;height:6px;border-radius:50%;background:#003366;animation:bounce 1s infinite">.</span><span style="width:6px;height:6px;border-radius:50%;background:#003366;animation:bounce 1s infinite .2s">.</span><span style="width:6px;height:6px;border-radius:50%;background:#003366;animation:bounce 1s infinite .4s">.</span></span>
        Advisor denkt na...
      </div>
    </div>

    <!-- Input -->
    <div style="padding:12px;border-top:1px solid #E9ECEF;display:flex;gap:8px;background:#fff">
      <input type="text" id="hseqInput" class="form-control" placeholder="Stel je HSEQ vraag..." style="flex:1;border-radius:20px;padding:10px 16px" onkeydown="if(event.key==='Enter'&&!event.shiftKey){event.preventDefault();sendHseqMessage()}">
      <button class="btn btn-primary" onclick="sendHseqMessage()" style="border-radius:50%;width:40px;height:40px;min-width:40px;padding:0;display:flex;align-items:center;justify-content:center" id="hseqSendBtn">➤</button>
    </div>
  </div>
</div>

<script>
var BASE = '{{BASE_PATH}}';
var hseqHistory = [];
var hseqCurrentCat = '';

var HSEQ_CATEGORIES = {
  'brzo': {label: 'BRZO/Seveso', context: 'Focus op BRZO 2015, Seveso III, ARIE, veiligheidsrapportages, company emergency plans, externe veiligheid.'},
  'arbo': {label: 'Arbowetgeving', context: 'Focus op Arbowet, Arbobesluit, RI&E, arbeidsomstandigheden, werkgeversverantwoordelijkheden.'},
  'gevaarlijk': {label: 'Gevaarlijke Stoffen', context: 'Focus op REACH, CLP, PGS-richtlijnen, SDS/MSDS, classificatie etikettering, opslag.'},
  'milieu': {label: 'Milieu', context: 'Focus op Omgevingswet, milieuvergunningen, emissies, afval, bodembescherming, MER.'},
  'ongeval': {label: 'Incidenten', context: 'Focus op MIR meldplicht, ongevallenonderzoek, root cause analysis, veiligheids cultuur.'},
  'training': {label: 'Training', context: 'Focus op veiligheidstrainingen, toolbox talks, competentie-eisen, instructie en voorlichting.'}
};

function setHseqCat(btn, cat) {
  hseqCurrentCat = cat;
  document.querySelectorAll('.hseq-cat').forEach(function(b){b.classList.remove('active');b.style.background='';b.style.color=''});
  btn.classList.add('active');
  btn.style.background='#003366';btn.style.color='#fff';
  var badge = document.getElementById('hseqCatBadge');
  if(cat && HSEQ_CATEGORIES[cat]){badge.textContent=HSEQ_CATEGORIES[cat].label;badge.style.display='inline'}
  else{badge.style.display='none'}
}

function escHtml(t){var d=document.createElement('div');d.textContent=t;return d.innerHTML}

function renderHseqMarkdown(text){
  if(!text) return '';
  var h = text;
  h = h.replace(/&/g,'&amp;').replace(/</g,'&lt;').replace(/>/g,'&gt;');
  // Tables
  h = h.replace(/^\|(.+)\|$/gm, function(m,row){
    if(row.match(/^[-:\s|]+$/)) return '';
    var cells = row.split('|').filter(function(c){return c.trim()});
    var isHeader = m.indexOf('---')===-1;
    return '<tr>' + cells.map(function(c){return '<'+(isHeader?'th':'td')+'>'+c.trim()+'</'+(isHeader?'th':'td')+'>';}).join('') + '</tr>';
  });
  h = h.replace(/((<tr>.*?<\/tr>\s*)+)/g, '<table>$1</table>');
  h = h.replace(/^### (.*)/gm,'<h3>$1</h3>');
  h = h.replace(/^## (.*)/gm,'<h2>$1</h2>');
  h = h.replace(/^# (.*)/gm,'<h1>$1</h1>');
  h = h.replace(/\*\*(.+?)\*\*/g,'<strong>$1</strong>');
  h = h.replace(/\*(.+?)\*/g,'<em>$1</em>');
  h = h.replace(/^---$/gm,'<hr>');
  h = h.replace(/^[-•] (.*)/gm,'<li>$1</li>');
  h = h.replace(/(<li>.*<\/li>\s*)+/g,'<ul>$&</ul>');
  h = h.replace(/^\d+\. (.*)/gm,'<li>$1</li>');
  h = h.replace(/^&gt; (.*)/gm,'<blockquote>$1</blockquote>');
  var codeParts=h.split(String.fromCharCode(96)+String.fromCharCode(96)+String.fromCharCode(96));if(codeParts.length>=3){var rb=codeParts[0];for(var ci=1;ci<codeParts.length-1;ci+=2){var cl=codeParts[ci];var nl=cl.indexOf(String.fromCharCode(10));var lang=nl>=0?cl.substring(0,nl):'';var code=nl>=0?cl.substring(nl+1):cl;if(ci+1<codeParts.length)rb+='<pre><code>'+code+'</code></pre>'+codeParts[ci+1];}h=rb;}
  h = h.replace(/`([^`]+)`/g,'<code>$1</code>');
  h = h.replace(new RegExp(String.fromCharCode(10)+String.fromCharCode(10),'g'),'</p><p>');
  h = h.replace(new RegExp(String.fromCharCode(10),'g'),'<br>');
  h = '<p>'+h+'</p>';
  h = h.replace(/<p>\s*<\/p>/g,'');
  return h;
}

function sendHseqMessage(){
  var input = document.getElementById('hseqInput');
  var msg = input.value.trim();
  if(!msg) return;
  input.value = '';
  document.getElementById('hseqSendBtn').disabled = true;

  var container = document.getElementById('hseqMessages');
  var displayMsg = msg;
  if(hseqCurrentCat && HSEQ_CATEGORIES[hseqCurrentCat]){
    displayMsg = '<span style="font-size:10px;color:#868E96">['+HSEQ_CATEGORIES[hseqCurrentCat].label+']</span><br>'+escHtml(msg);
  }
  container.innerHTML += '<div class="msg user">'+displayMsg+'</div>';
  hseqHistory.push({role:'user',content:msg});

  document.getElementById('hseqTyping').style.display='block';
  container.scrollTop = container.scrollHeight;

  fetch(BASE+'/api/chat/hseq',{
    method:'POST',
    headers:{'Content-Type':'application/json'},
    body:JSON.stringify({query:msg,history:hseqHistory.slice(-10),category:hseqCurrentCat})
  })
  .then(function(r){return r.json()})
  .then(function(d){
    document.getElementById('hseqTyping').style.display='none';
    document.getElementById('hseqSendBtn').disabled=false;
    if(d.error){
      container.innerHTML+='<div class="msg assistant">❌ '+escHtml(d.error)+'</div>';
    } else {
      var content = d.content||'';
      hseqHistory.push({role:'assistant',content:content});
      var rendered = renderHseqMarkdown(content);
      // Add sources
      if(d.sources && d.sources.length){
        rendered += '<div class="source-ref">📚 Bronnen: '+d.sources.join(' | ')+'</div>';
      }
      container.innerHTML+='<div class="msg assistant">'+rendered+'</div>';
    }
    container.scrollTop=container.scrollHeight;
  })
  .catch(function(err){
    document.getElementById('hseqTyping').style.display='none';
    document.getElementById('hseqSendBtn').disabled=false;
    container.innerHTML+='<div class="msg assistant">❌ Netwerkfout: '+escHtml(err.message)+'</div>';
    container.scrollTop=container.scrollHeight;
  });
}

function clearHseqChat(){
  hseqHistory=[];
  document.getElementById('hseqMessages').innerHTML='<div class="msg assistant" style="background:#fff;border:1px solid #E9ECEF;box-shadow:0 1px 3px rgba(0,0,0,.06)"><div style="font-weight:600;margin-bottom:8px;color:#003366">🧠 HSEQ Advisor</div><div style="font-size:13px;line-height:1.6">Nieuw gesprek! Stel je HSEQ vraag of kies een categorie.</div></div>';
}

function exportHseqChat(){
  if(!hseqHistory.length){alert('Geen gesprek om te exporteren.');return}
  var text='HSEQ Advisor — Gespreksexport\\nDatum: '+new Date().toLocaleString('nl-NL')+'\\n\\n';
  hseqHistory.forEach(function(m){text+=(m.role==='user'?'👤 Jij: ':'🧠 Advisor: ')+m.content+'\\n\\n'});
  var blob=new Blob([text],{type:'text/plain;charset=utf-8'});
  var a=document.createElement('a');a.href=URL.createObjectURL(blob);
  a.download='hseq-advies-'+new Date().toISOString().slice(0,10)+'.txt';a.click();
}
</script>

""", page_title='HSEQ Advisor', active='advisor', BASE_PATH=BASE_PATH)


@app.route('/api/chat/hseq', methods=['POST'])
def chat_hseq():
    """HSEQ Advisor chat endpoint with RAG."""
    data = request.get_json()
    query = data.get('query', '')
    history = data.get('history', [])
    category = data.get('category', '')

    if not query:
        return jsonify({'error': 'Vraag is verplicht'}), 400

    # Build system prompt
    system = HSEQ_SYSTEM_PROMPT

    # Add category context
    if category:
        cat_contexts = {
            'brzo': '## Huidige Focus: BRZO/Seveso\nDe gebruiker stelt vragen over BRZO 2015, Seveso III, ARIE, veiligheidsrapportages, company emergency plans, effectafstanden, kwantitatieve risicoberekeningen (QRA).',
            'arbo': '## Huidige Focus: Arbowetgeving\nDe gebruiker stelt vragen over de Arbowet, Arbobesluit, RI&E, arbeidsomstandigheden, werkgeversplichten, preventiemedewerker, bedrijfsarts.',
            'gevaarlijk': '## Huidige Focus: Gevaarlijke Stoffen\nDe gebruiker stelt vragen over REACH, CLP/GHS, PGS-richtlijnen, veiligheidsinformatiebladen (SDS), classificatie, etikettering, opslag van gevaarlijke stoffen, MPL-waarden.',
            'milieu': '## Huidige Focus: Milieu & Omgeving\nDe gebruiker stelt vragen over de Omgevingswet, milieuvergunningen, emissies, lozingen, afvalstoffen, bodembescherming, MER, CO2-reductie.',
            'ongeval': '## Huidige Focus: Incidenten & Ongelukken\nDe gebruiker stelt vragen over MIR-meldplicht, ongevallenonderzoek, root cause analysis (RCA), TRIPOD, veiligheidscultuur, leren van incidenten.',
            'training': '## Huidige Focus: Training & Opleiding\nDe gebruiker stelt vragen over veiligheidstrainingen, toolbox talks, competentie-eisen, instructie en voorlichting, voorbehouden handelingen, VCA.'
        }
        if category in cat_contexts:
            system += '\n\n' + cat_contexts[category]

    # Always include SOP/Styleguide context
    sop_context = get_sop_context()
    if sop_context:
        system += '\n\n' + sop_context
    
    # RAG: search knowledge bank for specific documents
    rag_context, sources = get_rag_context(query, max_chars=6000)
    if rag_context:
        system += '\n\n## Relevante Documenten uit de HSEQ Kennisbank:\n' + rag_context
    if sources:
        system += '\n\nBronnen (vermeld deze in je antwoord): ' + ', '.join(sources)
    
    # Add topic context if provided (from service page click)
    topic = data.get('topic', '')
    if topic:
        system += f'\n\n## Specifiek Onderwerp: {topic}\nDe gebruiker klikte op dit onderwerp in het dashboard. Geef een gericht, professioneel antwoord met praktische stappen.'

    # Build messages
    messages = [{'role': 'system', 'content': system}]
    for msg in history[-10:]:
        if msg.get('role') in ('user', 'assistant') and msg.get('content'):
            messages.append({'role': msg['role'], 'content': msg['content']})
    messages.append({'role': 'user', 'content': query})

    try:
        response_text = ai_complete(messages)
    except Exception as e:
        response_text = f"⚠️ AI-assistent momenteel niet beschikbaar. Fout: {str(e)}"

    return jsonify({'content': response_text, 'query': query, 'sources': sources})


# ═══════════════════════════════════════════════════════════════════════════
# DIENSTEN (KWA-inspired service pages)
# ═══════════════════════════════════════════════════════════════════════════

SERVICES = {
    'arbo': {
        'title': 'Arbo & Veiligheid',
        'emoji': '👷',
        'subtitle': 'Arbowetgeving, RI&E, arbeidshygiëne en veiligheidsadvies',
        'description': 'Goede arbeidsomstandigheden en een veilige werkomgeving zijn essentieel voor een prettige werkplek. De wetgeving vereist een volledige en actuele RI&E als basis. Wij ondersteunen met onderzoek, metingen en advies op het gebied van ergonomie, gehoor, licht, klimaat, chemische stoffen en biologische agentia.',
        'capabilities': [
            ('📋', 'RI&E Opstellen', 'Risico-Inventarisatie & Evaluatie, van quickscan tot verdiepende RI&E met arbeidshygiënisch onderzoek'),
            ('🔍', 'Arbeidshygiëne', 'Onderzoek en metingen: geluid, trilling, stof, dampen, gevaarlijke stoffen, hitte, licht'),
            ('🦺', 'PBM Beleid', 'Advies over Persoonlijke Beschermingsmiddelen, selectie en gebruik'),
            ('🏗️', 'Bouwveiligheid', 'Veiligheidsplannen, V&G-coördinatie, UAV-GC compliance'),
            ('🏢', 'MVK / HVK', 'Veiligheidskundige op locatie, interim of projectmatig'),
            ('🤝', 'Veiligheidscultuur', 'Meten en verbeteren van veiligheidsgedrag en -cultuur'),
            ('📐', 'Ergonomie', 'Werkplekanalyse, tiladvies, beeldschermwerk, fysieke belasting'),
            ('📝', 'Arboverplichtingen', 'ISO 45001, VCA, Arbobesluit compliance en audits'),
        ],
        'advisor_category': 'arbo',
        'quick_questions': [
            'Welke PBM\'s zijn verplicht voor werk met gevaarlijke stoffen?',
            'Hoe vaak moet een RI&E worden geactualiseerd?',
            'Wat zijn de eisen voor geluidmeting op de werkvloer?',
            'Hoe zet ik een V&G-plan op voor een bouwproject?',
        ],
    },
    'brzo': {
        'title': 'BRZO / Seveso',
        'emoji': '🏭',
        'subtitle': 'BRZO 2015, Seveso III, ARIE en industriële veiligheid',
        'description': 'Bedrijven die vallen onder de BRZO-wetgeving hebben specifieke kennis en ervaring nodig. Van veiligheidsrapportages en Company Emergency Plans tot kwantitatieve risicoberekeningen en BRZO-werkgroepen. Wij ondersteunen bij alle aspecten van externe en procesveiligheid.',
        'capabilities': [
            ('📄', 'Veiligheidsrapport', 'Opstellen en actualiseren van het veiligheidsrapport (MID)'),
            ('📊', 'QRA', 'Kwantitatieve Risico Analyse en effectafstandberekeningen'),
            ('🚨', 'Emergency Plan', 'Company Emergency Plan (BHV / Ontruimingsplan op maat)'),
            ('📐', 'ARIE', 'Algemene Richtlijn Industriële Veiligheid implementatie en advies'),
            ('⛽', 'PGS-richtlijnen', 'Toepassing Publieke Gezondheid & Stoffen richtlijnen'),
            ('🔬', 'Procesveiligheid', 'HAZOP, LOPA, SIL-classificatie, veiligheidsfuncties'),
            ('👥', 'BRZO Werkgroep', 'Faciliteren en deelnemen aan regionale BRZO-werkgroepen'),
            ('🔄', 'MIR', 'Meldplicht Incidenten Gevaarlijke Stoffen — begeleiding bij melding'),
        ],
        'advisor_category': 'brzo',
        'quick_questions': [
            'Wanneer is mijn bedrijf BRZO-plichtig?',
            'Wat moet er in een veiligheidsrapport staan?',
            'Hoe bereken ik de effectafstand voor mijn inrichting?',
            'Wat is het verschil tussen BRZO en Seveso III?',
        ],
    },
    'milieu': {
        'title': 'Milieu & Omgeving',
        'emoji': '🌿',
        'subtitle': 'Omgevingswet, milieuvergunningen en duurzaamheid',
        'description': 'Ieder bedrijf beïnvloedt de leefomgeving. Om effecten te beperken is wet- en regelgeving opgesteld. Wij helpen bij het naleven van milieuwetgeving, het verkrijgen van vergunningen en het minimaliseren van milieubelasting door emissies, lozingen en afvalstoffen.',
        'capabilities': [
            ('📜', 'Omgevingswet', 'Advies en implementatie van de Omgevingswet en omgevingsvergunning'),
            ('💨', 'Luchtemissies', 'Emissieberekeningen, vergunningsaanvragen en monitoring'),
            ('💧', 'Water & Lozingen', 'Lozingsvergunningen, waterbeheer en hemelwater'),
            ('🗑️', 'Afvalstoffen', 'Afvalstoffenbeheer, NAUP, grondstoffenregistratie'),
            ('🌍', 'MER', 'Milieueffectrapportage en strategische MER'),
            ('🏭', 'BRZO Milieu', 'Milieubelasting bij BRZO-inrichtingen, effectafstanden'),
            ('📊', 'CO2 & Energietransitie', 'EU-ETS, CO2-reductie, energiebesparing en duurzaamheid'),
            ('🔍', 'Bodemonderzoek', 'Bodemkwaliteit, PFK, verontreiniging en sanering'),
        ],
        'advisor_category': 'milieu',
        'quick_questions': [
            'Welke vergunningen heb ik nodig voor mijn inrichting?',
            'Hoe rapporteer ik mijn emissies onder de Omgevingswet?',
            'Wat zijn de eisen voor lozingen op oppervlaktewater?',
            'Hoe bereken ik mijn CO2-voetafdruk?',
        ],
    },
    'kam': {
        'title': 'KAM Management',
        'emoji': '📋',
        'subtitle': 'Kwaliteit, Arbo, Milieu — managementsystemen en audits',
        'description': 'Wij ondersteunen bij bedrijfsdoelstellingen door in te zetten op goed functionerende processen, heldere taakverdeling, competente medewerkers en juiste middelen. Van compliance scans en audits tot het ontwerpen en implementeren van geïntegreerde managementsystemen.',
        'capabilities': [
            ('✅', 'ISO 9001', 'Kwaliteitsmanagementsysteem implementatie en certificering'),
            ('🛡️', 'ISO 45001', 'Arbomanagementsysteem opzetten en borgen'),
            ('🌿', 'ISO 14001', 'Milieumanagementsysteem en duurzaamheidsbeleid'),
            ('🔗', 'Geïntegreerd MMS', 'KAM-systeem dat Q, A en M samenbrengt in één systeem'),
            ('🔍', 'Audits & Scans', 'Interne audits, compliance scans en gap analyses'),
            ('📊', 'KPI Dashboard', 'KAM-prestaties meten, sturen en rapporteren'),
            ('📝', 'Documentatie', 'Procedures, werkinstructies en formulieren op orde'),
            ('🔄', 'Continu Verbeteren', 'PDCA-cyclus, verbetertrajecten en Lean Six Sigma'),
        ],
        'advisor_category': '',
        'quick_questions': [
            'Hoe start ik met ISO 45001 implementatie?',
            'Wat is het verschil tussen een interne audit en een certificeringsaudit?',
            'Hoe zet ik een effectief KAM-managementsysteem op?',
            "Welke KPI's moet ik meten voor veiligheid?",
        ],
    },
    'training': {
        'title': 'Training & Opleiding',
        'emoji': '🎓',
        'subtitle': 'Veiligheidstrainingen, toolbox talks en competentiebeheer',
        'description': 'Effectieve training en instructie is essentieel voor veilig werken. Van wettelijk verplichte certificeringen tot bedrijfsspecifieke toolbox talks. Wij helpen bij het opzetten van een trainingsbeleid, het ontwikkelen van materiaal en het borgen van competenties.',
        'capabilities': [
            ('🔧', 'Toolbox Talks', 'Korte interactieve veiligheidssessies op de werkvloer'),
            ('📚', 'Training Generator', 'Automatisch trainingen genereren uit procedures en richtlijnen'),
            ('🎓', 'VCA Training', 'Voorbereiding op VCA Basis en VCA VOL'),
            ('📝', 'Competentiebeheer', 'Register van voorbehouden handelingen en kwalificaties'),
            ('🎬', 'E-learning', 'Digitere leermodules met quiz en certificering'),
            ('📋', 'Instructiekaarten', 'Visuele werkinstructies voor taakgebonden risico\'s'),
            ('📊', 'Trainingsmatrix', 'Overzicht van verplichte trainingen per functie'),
            ('🔍', 'Effectmeting', 'Toetsen of trainingen daadwerkelijk gedrag veranderen'),
        ],
        'advisor_category': 'training',
        'quick_questions': [
            'Hoe vaak moeten medewerkers een veiligheidstraining volgen?',
            'Wat moet er in een toolbox talk staan?',
            'Wanneer is VCA-certificering verplicht?',
            'Hoe toets ik of een training effectief is?',
        ],
    },
    'energy': {
        'title': 'Energie & Proces',
        'emoji': '⚡',
        'subtitle': 'Energiebesparing, procesoptimalisatie en energietransitie',
        'description': 'Uw productieproces kent diverse levensfases: ontwerp, bouw, operationeel en transitie. Wij helpen bij het optimaliseren van procesefficiency en energie-efficiency, van procesanalyses en -metingen tot CO2-reductie en energietransitie strategieën.',
        'capabilities': [
            ('⚡', 'Energieaudit', 'Energiebesparingspotentieel in kaart brengen (EED verplicht)'),
            ('🏭', 'Procesanalyse', 'Procesefficiency, bottum analyses en optimalisatie'),
            ('🌡️', 'Warmte & Koude', 'Warmtenetwerken, warmtepompen, restwarmte benutting'),
            ('📊', 'EU-ETS', 'CO2-emissiehandel, monitoringsprotocollen en compliance'),
            ('🔄', 'Energietransitie', 'Roadmap naar CO2-reductie en duurzame energie'),
            ('🔧', 'Meettechniek', 'Procesmetingen, flow, temperatuur, druk, samenstelling'),
            ('📈', 'Performance', 'Operationele performance, OEE en proces KPI\'s'),
            ('📝', 'Subsidie & Regeling', 'DEI, SDE++, ISDE en andere energiemaatregelen'),
        ],
        'advisor_category': '',
    },
}


@app.route('/services/<service_id>')
def service_page(service_id):
    svc = SERVICES.get(service_id)
    if not svc:
        return page('<div class="empty-state"><div class="emoji">❌</div><h3>Dienst niet gevonden</h3></div>'), 404

    return page("""

<div style="margin-bottom:20px">
  <a href="{{BASE_PATH}}/" class="btn btn-outline btn-sm">← Dashboard</a>
</div>

<!-- Hero -->
<div class="card" style="background:linear-gradient(135deg,#003366,#005580);color:#fff;border:none">
  <div style="display:flex;align-items:center;gap:16px;margin-bottom:12px">
    <div style="width:56px;height:56px;border-radius:12px;background:rgba(255,255,255,.15);display:flex;align-items:center;justify-content:center;font-size:28px">{{svc['emoji']}}</div>
    <div>
      <h2 style="margin:0;color:#fff;font-size:20px">{{svc['title']}}</h2>
      <p style="margin:4px 0 0;font-size:12px;color:rgba(255,255,255,.7)">{{svc['subtitle']}}</p>
    </div>
  </div>
  <p style="font-size:13px;line-height:1.6;color:rgba(255,255,255,.9)">{{svc['description']}}</p>
</div>

<!-- Tool Grid: Elke capability is een actieve tool met actieknoppen -->
<div style="display:grid;grid-template-columns:repeat(auto-fill,minmax(300px,1fr));gap:16px;margin-bottom:24px">
  {% for cap in svc['capabilities'] %}
  <div class="card" style="border-left:4px solid {{svc.get('color','#003366')}};padding:0;overflow:hidden">
    <div style="padding:16px 16px 8px">
      <div style="display:flex;align-items:center;gap:10px;margin-bottom:8px">
        <span style="font-size:28px">{{cap[0]}}</span>
        <h3 style="font-size:15px;color:#003366;margin:0;font-weight:600">{{cap[1]}}</h3>
      </div>
      <p style="font-size:12px;color:#868E96;line-height:1.5;margin:0">{{cap[2]}}</p>
    </div>
    <div style="padding:8px 16px 16px;display:flex;flex-wrap:wrap;gap:6px">
      <button onclick="openIntake('{{cap[1]|e}}','advies','{{svc.get("advisor_category","")|e}}')" data-label="💡 Advies" style="flex:1;min-width:80px;padding:10px 10px;border:1px solid #003366;border-radius:6px;background:#003366;color:#fff;font-size:12px;cursor:pointer;font-weight:600;-webkit-tap-highlight-color:transparent">💡 Advies</button>
      <button onclick="openIntake('{{cap[1]|e}}','procedure','{{svc.get("advisor_category","")|e}}')" data-label="📋 Procedure" style="flex:1;min-width:80px;padding:10px 10px;border:1px solid #4ECDC4;border-radius:6px;background:#fff;color:#333;font-size:12px;cursor:pointer;font-weight:600;-webkit-tap-highlight-color:transparent">📋 Procedure</button>
      <button onclick="openIntake('{{cap[1]|e}}','checklist','{{svc.get("advisor_category","")|e}}')" data-label="✅ Checklist" style="flex:1;min-width:80px;padding:10px 10px;border:1px solid #FFB800;border-radius:6px;background:#fff;color:#333;font-size:12px;cursor:pointer;font-weight:600;-webkit-tap-highlight-color:transparent">✅ Checklist</button>
      <button onclick="openIntake('{{cap[1]|e}}','rapport','{{svc.get("advisor_category","")|e}}')" data-label="📊 Rapport" style="flex:1;min-width:80px;padding:10px 10px;border:1px solid #E74C3C;border-radius:6px;background:#fff;color:#333;font-size:12px;cursor:pointer;font-weight:600;-webkit-tap-highlight-color:transparent">📊 Rapport</button>
      <button onclick="openIntake('{{cap[1]|e}}','training','{{svc.get("advisor_category","")|e}}')" data-label="🎓 Training" style="flex:1;min-width:80px;padding:10px 10px;border:1px solid #9B59B6;border-radius:6px;background:#fff;color:#333;font-size:12px;cursor:pointer;font-weight:600;-webkit-tap-highlight-color:transparent">🎓 Training</button>
    </div>
  </div>
  {% endfor %}
</div>

<!-- Generation Output Panel -->
<div id="gen-panel" class="card" style="display:none;border:2px solid #003366">
  <div style="display:flex;justify-content:space-between;align-items:center;margin-bottom:12px">
    <h3 style="margin:0;color:#003366" id="gen-title">🚀 Deliverable Generator</h3>
    <button onclick="document.getElementById('gen-panel').style.display='none'" style="background:none;border:none;font-size:18px;cursor:pointer;color:#868E96">✕</button>
  </div>
  <div id="gen-meta" style="font-size:12px;color:#868E96;margin-bottom:12px"></div>
  <div id="gen-output" style="background:#F8F9FA;border-radius:8px;padding:16px;max-height:600px;overflow-y:auto;font-size:13px;line-height:1.7;white-space:pre-wrap"></div>
  <div id="gen-sources" style="margin-top:12px;font-size:11px;color:#868E96"></div>
</div>

<!-- Snelvragen -->
{% if svc.get('quick_questions') %}
<div class="card" style="margin-top:16px">
  <h2 style="font-size:14px;margin-bottom:12px">💬 Snelvragen</h2>
  <div style="display:grid;grid-template-columns:repeat(auto-fill,minmax(280px,1fr));gap:8px">
    {% for q in svc['quick_questions'] %}
    <a href="{{BASE_PATH}}/advisor?cat={{svc.get('advisor_category','')|urlencode}}&q={{q|urlencode}}" class="card" style="text-decoration:none;display:block;padding:10px 14px;border-left:3px solid #4ECDC4;font-size:13px;color:#333;transition:all .2s" onmouseover="this.style.background='#F0F7FF'" onmouseout="this.style.background=''">{{q}} →</a>
    {% endfor %}
  </div>
</div>
{% endif %}

<!-- Advisor CTA -->
<div style="margin-top:16px;text-align:center">
  <a href="{{BASE_PATH}}/advisor?cat={{svc.get('advisor_category','')|urlencode}}" class="btn btn-primary" style="padding:12px 24px;font-size:14px">🧠 Open HSEQ Advisor — Stel je eigen vraag</a>
</div>

<script>
var BASE = '{{BASE_PATH}}';
function closeGenPanel(){document.getElementById('gen-panel').style.display='none'}
var genLoading = false;
var INTAKE_TOPICS = {
  'RI&E Opstellen': [
    {id:'bestaande', label:'Bestaat er al een RI&E?', type:'select', options:['Ja, moet geactualiseerd worden','Ja, als referentie','Nee, moet nieuw opgesteld worden','Onbekend'], upload_if:['Ja, moet geactualiseerd worden','Ja, als referentie'], upload_label:'Upload de bestaande RI&E (PDF/DOCX)'},
    {id:'afdelingen', label:'Welke afdelingen/functies?', type:'multiselect', options:['Productie','Onderhoud','Logistiek','Laboratorium','Kantoor','Buiten locatie','Alle afdelingen']},
    {id:'medewerkers', label:'Totaal aantal medewerkers', type:'select', options:['1-10','11-25','26-50','51-100','101-250','250+']},
    {id:'risicotype', label:'Belangrijkste risicotype', type:'multiselect', options:['Fysieke belasting','Geluid','Chemische stoffen','Psychosociale belasting','Ergonomie','Veilige werkomgeving']},
    {id:'scope', label:'Scope van de RI&E', type:'textarea'}
  ],
  'Arbeidshygiëne': [
    {id:'blootstelling', label:'Type blootstelling', type:'multiselect', options:['Geluid','Trilling','Stof','Dampen','Gassen','Hitte','Licht','Biologische agentia']},
    {id:'meting', label:'Is er al blootstellingsmeting uitgevoerd?', type:'select', options:['Ja, recente metingen','Ja, maar verouderd','Nee, nog niet gemeten','Onbekend'], upload_if:['Ja, recente metingen','Ja, maar verouderd'], upload_label:'Upload het meetrapport (PDF/DOCX)'},
    {id:'grenswaarde', label:'Zijn grenswaarden overschreden?', type:'select', options:['Ja, meerdere overschrijdingen','Ja, incidenteel','Nee, binnen norm','Niet bekend']},
    {id:'medewerkers', label:'Aantal blootgestelde medewerkers', type:'select', options:['1-10','11-50','51-200','200+']},
    {id:'scope', label:'Wat moet onderzocht worden?', type:'textarea'}
  ],
  'PBM Beleid': [
    {id:'pbm_types', label:'Welke PBM-types zijn relevant?', type:'multiselect', options:['Adembescherming','Hoorbescherming','Valbeveiliging','Oogbescherming','Handbescherming','Werkkleding','Veiligheidsschoenen','Helm']},
    {id:'pbm_lijst', label:'Is er een PBM-lijst aanwezig?', type:'select', options:['Ja, actueel','Ja, verouderd','Nee','Onbekend'], upload_if:['Ja, actueel','Ja, verouderd'], upload_label:'Upload de bestaande PBM-lijst (PDF/DOCX)'},
    {id:'incidenten', label:'Zijn er PBM-gerelateerde incidenten geweest?', type:'select', options:['Ja, meerdere','Ja, incidenteel','Nee','Onbekend']},
    {id:'leverancier', label:'Is er een vaste PBM-leverancier?', type:'select', options:['Ja','Nee','Verschillende leveranciers','Onbekend']},
    {id:'scope', label:'Scope van het PBM-advies', type:'textarea'}
  ],
  'Bouwveiligheid': [
    {id:'projecttype', label:'Type project', type:'select', options:['Nieuwbouw','Renovatie','Onderhoud','Sloop','Utiliteitsbouw','Weg- en waterbouw']},
    {id:'vg_plan', label:'Is er een V&G-plan aanwezig?', type:'select', options:['Ja, actueel','Ja, moet geactualiseerd','Nee','Onbekend'], upload_if:['Ja, actueel','Ja, moet geactualiseerd'], upload_label:'Upload het bestaande V&G-plan (PDF/DOCX)'},
    {id:'uavgc', label:'UAV-GC certificaat?', type:'select', options:['Vereist','Gewenst','Niet van toepassing','Onbekend']},
    {id:'aannemers', label:'Aantal aannemers/onderaannemers', type:'select', options:['1','2-5','6-10','10+']},
    {id:'scope', label:'Scope van het veiligheidsadvies', type:'textarea'}
  ],
  'MVK / HVK': [
    {id:'functie', label:'Welke functie?', type:'select', options:['MVK (Middenkader Veiligheidskundige)','HVK (Hoofd Veiligheidskundige)','Beide','Interim inzet']},
    {id:'branche', label:'Branche', type:'select', options:['Bouw en infra','Industrie','Petrochemie','Overheid','Zorg','Utiliteit','Overig']},
    {id:'certificaat', label:'Huidige VCA-certificering', type:'select', options:['VCA**','VCA*','Geen VCA','In aanvraag']},
    {id:'scope', label:'Waar is ondersteuning nodig?', type:'textarea'}
  ],
  'Veiligheidscultuur': [
    {id:'cultuur', label:'Huidige veiligheidscultuur', type:'select', options:['Reactief (incident-gedreven)','Compliant (regels-gericht)','Proactief (risico-gedreven)','Generatief (veiligheid verweven in alles)']},
    {id:'incidenten', label:'Incidentfrequentie afgelopen jaar', type:'select', options:['0 incidenten','1-5 incidenten','6-20 incidenten','20+ incidenten']},
    {id:'meting', label:'Is er een cultuurmeting uitgevoerd?', type:'select', options:['Ja, recent','Ja, maar verouderd','Nee','Onbekend'], upload_if:['Ja, recent','Ja, maar verouderd'], upload_label:'Upload het cultuurmeetrapport (PDF/DOCX)'},
    {id:'scope', label:'Doel van het traject', type:'textarea'}
  ],
  'Ergonomie': [
    {id:'belasting', label:'Type fysieke belasting', type:'multiselect', options:['Tillen en dragen','Herhaalde bewegingen','Vaste houding','Beeldschermwerk','Werken op hoogte','Werken in gekromde houding']},
    {id:'klachten', label:'Zijn er werkgerelateerde klachten?', type:'select', options:['Ja, frequent','Ja, incidenteel','Nee','Onbekend']},
    {id:'arbeidsplekken', label:'Aantal te analyseren arbeidsplekken', type:'select', options:['1-5','6-15','16-30','30+']},
    {id:'scope', label:'Scope van het ergonomieonderzoek', type:'textarea'}
  ],
  'Arboverplichtingen': [
    {id:'norm', label:'Welke norm/certificering?', type:'multiselect', options:['ISO 45001','VCA*','VCA**','Arbobesluit compliance','BRZO','Overig']},
    {id:'status', label:'Huidige status', type:'select', options:['Gecertificeerd','In voorbereiding','Gap-analyse nodig','Audit gepland']},
    {id:'scope', label:'Wat is de scope van het verzoek?', type:'textarea'}
  ],
  'Veiligheidsrapport': [
    {id:'bestaand', label:'Bestaat er al een veiligheidsrapport?', type:'select', options:['Ja, actualisatie nodig','Ja, als referentie','Nee, moet nieuw','Onbekend'], upload_if:['Ja, actualisatie nodig','Ja, als referentie'], upload_label:'Upload het bestaande veiligheidsrapport (PDF)'},
    {id:'inrichting', label:'Type inrichting', type:'select', options:['Chemische opslag','Tankpark','Procesinstallatie','Terminal','Laboratorium','Overig']},
    {id:'stoffen', label:'Relevante stofgroepen', type:'multiselect', options:['Brandbare vloeistoffen','Giftige gassen','Oxiderende stoffen','Ontvlambare gassen','Gevaarlijke stoffen','Milieugevaarlijke stoffen']},
    {id:'mid', label:'Is er een MID (Major Incident Domains) analyse?', type:'select', options:['Ja','Nee','Deels','Onbekend']},
    {id:'scope', label:'Scope van het veiligheidsrapport', type:'textarea'}
  ],
  'QRA': [
    {id:'situatie', label:'Type locatie', type:'select', options:['Industriegebied','Woonwijk nabij','Gemengd','Afgelegen','Havengebied']},
    {id:'scenario', label:'Relevante scenario'+String.fromCharCode(39)+'s', type:'multiselect', options:['Vuurball','VCE (Vapor Cloud Explosion)','Jet fire','Pool fire','BLEVE','Toxic release']},
    {id:'model', label:'Welk QRA-model?', type:'select', options:['EFFECTS','SAFETI','PHAST','Onbekend','Nog geen keuze']},
    {id:'scope', label:'Scope van de QRA', type:'textarea'}
  ],
  'Emergency Plan': [
    {id:'type', label:'Type plan', type:'multiselect', options:['Ontruimingsplan','BHV-organisatie','Externe noodzorg','Company Emergency Plan','Communicatieplan']},
    {id:'bhv', label:'BHV-organisatie?', type:'select', options:['Ja, volledig ingericht','Ja, maar onvoldoende','Nee','In oprichting'], upload_if:['Ja, volledig ingericht','Ja, maar onvoldoende'], upload_label:'Upload het bestaande BHV-plan (PDF/DOCX)'},
    {id:'oefening', label:'Frequentie oefeningen', type:'select', options:['Maandelijks','Kwartaal','Halfjaarlijks','Jaarlijks','Nooit geoefend']},
    {id:'scope', label:'Scope van het noodplan', type:'textarea'}
  ],
  'ARIE': [
    {id:'status', label:'ARIE-status', type:'select', options:['ARIE-plichtig','Twijfelachtig','Niet ARIE-plichtig','Onbekend']},
    {id:'scope', label:'Scope van het ARIE-advies', type:'textarea'}
  ],
  'PGS-richtlijnen': [
    {id:'pgs', label:'Relevante PGS-richtlijnen', type:'multiselect', options:['PGS-1 (Algemeen)','PGS-2 (Classificatie)','PGS-5 (Opslag)','PGS-11 (Load/Unload)','PGS-12 (Bulkladings)','PGS-15 (Tankopslag)','PGS-22 (Process Safety)']},
    {id:'scope', label:'Waar is advies nodig?', type:'textarea'}
  ],
  'Procesveiligheid': [
    {id:'methode', label:'Gewenste analyse-methode', type:'multiselect', options:['HAZOP','LOPA','SIL-classificatie','What-If','FMEA','Bow-Tie']},
    {id:'installatie', label:'Type installatie', type:'select', options:['Chemische proces','Tankopslag','Gashandeling','Laboratorium','Utilities','Overig']},
    {id:'scope', label:'Scope van de procesveiligheidsanalyse', type:'textarea'}
  ],
  'BRZO Werkgroep': [
    {id:'rol', label:'Jouw rol in de werkgroep', type:'select', options:['Voorzitter','Deelnemer (bedrijf)','Deelnemer (overheid)','Secretaris','Adviseur']},
    {id:'regio', label:'Regio', type:'select', options:['Rijnmond','Amsterdam','Rotterdam','Moerdijk','Geleen','Groningen','Overig']},
    {id:'scope', label:'Waar is ondersteuning nodig?', type:'textarea'}
  ],
  'MIR': [
    {id:'incident', label:'Type incident', type:'select', options:['Lekkage','Brand/ontploffing','Blootstelling personen','Milieubelasting','Bijna-ongeval','Overig']},
    {id:'melder', label:'Wie meldt?', type:'select', options:['Werkgever','Directie','HSE-manager','Externe partij']},
    {id:'scope', label:'Beschrijf het incident', type:'textarea'}
  ],
  'Omgevingswet': [
    {id:'type', label:'Type vergunning', type:'multiselect', options:['Omgevingsvergunning activiteiten','Watervergunning','Milieubelastende activiteiten','Sloopvergunning','Bouwvergunning']},
    {id:'status', label:'Status', type:'select', options:['Vergunning aanwezig','Aanvraag in behandeling','Nog geen aanvraag','Wijziging nodig']},
    {id:'scope', label:'Scope van het verzoek', type:'textarea'}
  ],
  'Luchtemissies': [
    {id:'bronnen', label:'Emissiebronnen', type:'multiselect', options:['Stack/rookgaskanaal','Fugitieve emissies','Opslag','Laden/lossen','Procesventilatie','Overig']},
    {id:'stoffen', label:'Relevante stoffen', type:'multiselect', options:['NOx','SOx','PM10/PM2.5','VOC','CO','NH3','Overig']},
    {id:'scope', label:'Scope van de emissieberekening', type:'textarea'}
  ],
  'ISO 9001': [
    {id:'status', label:'Certificeringsstatus', type:'select', options:['Gecertificeerd','In voorbereiding','Gap-analyse nodig','Overweeg certificering']},
    {id:'scope', label:'Scope van de ondersteuning', type:'textarea'}
  ],
  'ISO 45001': [
    {id:'status', label:'Certificeringsstatus', type:'select', options:['Gecertificeerd','In voorbereiding','Gap-analyse nodig','Overweeg certificering']},
    {id:'scope', label:'Scope van de ondersteuning', type:'textarea'}
  ],
  'ISO 14001': [
    {id:'status', label:'Certificeringsstatus', type:'select', options:['Gecertificeerd','In voorbereiding','Gap-analyse nodig','Overweeg certificering']},
    {id:'scope', label:'Scope van de ondersteuning', type:'textarea'}
  ],
  'Toolbox Talks': [
    {id:'onderwerp', label:'Onderwerp', type:'select', options:['Veilig werken','Chemische stoffen','PBM-gebruik','Werken op hoogte','Hitte/koude','Noodprocedures','Overig']},
    {id:'doelgroep', label:'Doelgroep', type:'select', options:['Operateurs','Onderhoud','Bouw','Alle medewerkers','Contractors']},
    {id:'frequentie', label:'Gewenste frequentie', type:'select', options:['Wekelijks','Tweewekelijks','Maandelijks','Ad hoc']},
    {id:'scope', label:'Specifiek onderwerp of situatie', type:'textarea'}
  ],
  'Training Generator': [
    {id:'bron', label:'Bronmateriaal', type:'select', options:['Interne procedure','Wetgeving','Risicoanalyse','Incidentrapport','AI-blad','Overig']},
    {id:'doelgroep', label:'Doelgroep', type:'select', options:['Operateurs','Supervisors','Management','Contractors','Alle medewerkers']},
    {id:'niveau', label:'Niveau', type:'select', options:['Basisbewustwording','Gevorderd','Specialist','Refresher']},
    {id:'scope', label:'Onderwerp van de training', type:'textarea'}
  ],
  'VCA Training': [
    {id:'niveau', label:'VCA-niveau', type:'select', options:['VCA Basis','VCA VOL','VCA VOL hercertificering']},
    {id:'doelgroep', label:'Doelgroep', type:'select', options:['Operateurs','Uitvoerenden','Leidinggevenden','Bureau','Alle medewerkers']},
    {id:'scope', label:'Specifieke focus', type:'textarea'}
  ],
  'Energieaudit': [
    {id:'type', label:'Type installatie', type:'select', options:['Industrieel proces','Kantoor','Utiliteit','Koel/vries','Compressie','Overig']},
    {id:'verplicht', label:'Is de audit verplicht (EED)?', type:'select', options:['Ja, >250 TJ/jaar','Ja, >50 medewerkers','Nee, vrijwillig','Onbekend']},
    {id:'scope', label:'Scope van de energieaudit', type:'textarea'}
  ],
  'Procesanalyse': [
    {id:'proces', label:'Type proces', type:'select', options:['Chemisch','Fysisch','Mechanisch','Thermisch','Gecombineerd']},
    {id:'scope', label:'Scope van de procesanalyse', type:'textarea'}
  ]
};
var INTAKE_CATEGORY = {
  arbo: [
    {id:'werkzaamheden', label:'Type werkzaamheden', type:'select', options:['Bouw en constructie','Onderhoud en inspectie','Productie en proces','Laboratorium','Logistiek en transport','Kantoor en administratie','Overig']},
    {id:'risico', label:'Bekende risicofactoren', type:'multiselect', options:['Fysieke belasting','Geluid','Trilling','Chemische stoffen','Biologische agentia','Hitte/koude','Straling','Psychosociale arbeidsbelasting']},
    {id:'medewerkers', label:'Aantal betrokken medewerkers', type:'select', options:['1-10','11-50','51-200','200+']},
    {id:'scope', label:'Wat is de scope van het verzoek?', type:'textarea'}
  ],
  brzo: [
    {id:'inrichting', label:'Type inrichting', type:'select', options:['Chemische opslag','Tankpark','Procesinstallatie','Terminal','Laboratorium','Overig BRZO']},
    {id:'stoffen', label:'Relevante stoffen/groepen', type:'multiselect', options:['Brandbare vloeistoffen','Giftige gassen','Oxiderende stoffen','Ontvlambare gassen','Gevaarlijke vloeistoffen','Milieugevaarlijke stoffen']},
    {id:'rapport', label:'Bestaat er al een veiligheidsrapport?', type:'select', options:['Ja, actualisatie nodig','Ja, als referentie','Nee, moet nieuw','Onbekend']},
    {id:'scope', label:'Wat is de scope van het verzoek?', type:'textarea'}
  ],
  milieu: [
    {id:'type', label:'Type activiteit', type:'select', options:['Industriele productie','Opslag en distributie','Bouw','Afvalverwerking','Energieproductie','Overig']},
    {id:'emissie', label:'Relevante emissies', type:'multiselect', options:['Luchtemissies','Waterlozingen','Bodemverontreiniging','Geluid','Afvalstoffen','CO2/broeikasgassen']},
    {id:'vergunning', label:'Vergunningsstatus', type:'select', options:['Omgevingsvergunning aanwezig','Aanvraag in behandeling','Nog geen vergunning','Onbekend']},
    {id:'scope', label:'Wat is de scope van het verzoek?', type:'textarea'}
  ]
};
var currentIntake = {topic:'',type:'',category:''};

function toggleUpload(qid, value, triggers) {
  var el = document.getElementById('upload-'+qid);
  if (!el) return;
  el.style.display = triggers.indexOf(value) >= 0 ? 'block' : 'none';
}
function openIntake(topic, type, category) {
  currentIntake = {topic:topic,type:type,category:category};
  var typeLabels = {advies:'Advies',procedure:'Procedure',checklist:'Checklist',rapport:'Rapport',training:'Training'};
  var questions = INTAKE_TOPICS[topic] || INTAKE_CATEGORY[category] || INTAKE_CATEGORY.arbo;
  var panel = document.getElementById('gen-panel');
  var title = document.getElementById('gen-title');
  title.textContent = typeLabels[type] + ' - ' + topic;
  var html = '<div style="margin-bottom:16px;padding:12px;background:#F0F7FF;border-radius:8px;border:1px solid #B8D4FE"><strong>Intake - Context vaststellen</strong><p style="font-size:12px;color:#495057;margin:8px 0 0">Beantwoord de vragen zodat de AI gericht kan zoeken in de kennisbank.</p></div>';
  html += '<div id="intake-form" style="display:flex;flex-direction:column;gap:12px">';
  for(var i=0;i<questions.length;i++){
    var q=questions[i];
    html+='<div><label style="display:block;font-size:13px;font-weight:600;color:#003366;margin-bottom:4px">'+q.label+'</label>';
    if(q.type==='select'){
      var onchg='';
      if(q.upload_if){onchg=' onchange="toggleUpload('+String.fromCharCode(39)+q.id+String.fromCharCode(39)+',this.value,'+JSON.stringify(q.upload_if)+')"';}
      html+='<select id="intake-'+q.id+'"'+onchg+' style="width:100%;padding:8px 12px;border:1px solid #DEE2E6;border-radius:6px;font-size:13px"><option value="">- Selecteer -</option>';
      for(var j=0;j<q.options.length;j++) html+='<option value="'+q.options[j]+'">'+q.options[j]+'</option>';
      html+='</select>';
      if(q.upload_if){
        html+='<div id="upload-'+q.id+'" style="display:none;margin-top:8px;padding:10px;background:#FFF9DB;border:1px solid #FFB800;border-radius:6px"><label style="display:block;font-size:12px;font-weight:600;color:#E67700;margin-bottom:4px">'+(q.upload_label||'Upload document')+'</label><input type="file" id="file-'+q.id+'" accept=".pdf,.docx,.doc,.xlsx,.pptx,.txt" style="font-size:12px"><div style="font-size:11px;color:#868E96;margin-top:2px">Max 10 MB</div></div>';
      }
    } else if(q.type==='multiselect'){
      html+='<div style="display:flex;flex-wrap:wrap;gap:6px">';
      for(var j=0;j<q.options.length;j++) html+='<label style="display:flex;align-items:center;gap:4px;font-size:12px;padding:4px 8px;background:#F8F9FA;border:1px solid #DEE2E6;border-radius:4px;cursor:pointer"><input type="checkbox" value="'+q.options[j]+'"> '+q.options[j]+'</label>';
      html+='</div>';
    } else {
      html+='<textarea id="intake-'+q.id+'" placeholder="Beschrijf de specifieke situatie..." style="width:100%;padding:8px 12px;border:1px solid #DEE2E6;border-radius:6px;font-size:13px;min-height:80px"></textarea>';
    }
    html+='</div>';
  }
  html += '</div><div style="margin-top:16px;display:flex;gap:8px"><button onclick="submitIntake()" style="flex:1;padding:10px;background:#003366;color:#fff;border:none;border-radius:6px;font-size:13px;font-weight:600;cursor:pointer">Generate Deliverable</button><button onclick="closeGenPanel()" style="padding:10px 16px;background:#fff;color:#333;border:1px solid #DEE2E6;border-radius:6px;font-size:13px;cursor:pointer">Annuleren</button></div>';
  document.getElementById('gen-output').innerHTML = html;
  document.getElementById('gen-output').style.color = '#333';
  document.getElementById('gen-meta').textContent = '';
  document.getElementById('gen-sources').textContent = '';
  panel.style.display = 'block';
  panel.scrollIntoView({behavior:'smooth'});
}

function submitIntake() {
  if(genLoading) return;
  genLoading = true;
  var questions = INTAKE_TOPICS[currentIntake.topic] || INTAKE_CATEGORY[currentIntake.category] || INTAKE_CATEGORY.arbo;
  var answers = {};
  for(var i=0;i<questions.length;i++){
    var el = document.getElementById('intake-'+questions[i].id);
    if(!el) continue;
    if(questions[i].type==='multiselect'){
      var checked = el.parentElement.querySelectorAll('input:checked');
      answers[questions[i].id] = Array.from(checked).map(function(c){return c.value;});
    } else { answers[questions[i].id] = el.value; }
  }
  var output = document.getElementById('gen-output');
  var meta = document.getElementById('gen-meta');
  var sourcesEl = document.getElementById('gen-sources');
  meta.textContent = 'Context analyseren en kennisbank doorzoeken...';
  output.innerHTML = '<div style="text-align:center;padding:24px"><div style="display:inline-flex;gap:6px;margin-bottom:12px"><span style="width:10px;height:10px;border-radius:50%;background:#003366;animation:bounce 1s infinite">.</span><span style="width:10px;height:10px;border-radius:50%;background:#003366;animation:bounce 1s infinite .2s">.</span><span style="width:10px;height:10px;border-radius:50%;background:#003366;animation:bounce 1s infinite .4s">.</span></div><strong>Deliverable wordt gegenereerd...</strong><br><span style="color:#868E96;font-size:12px">Dit kan 15-30 seconden duren. Bestand wordt automatisch gedownload.</span></div>';
  sourcesEl.textContent = '';
  var fd = new FormData();
  fd.append('topic', currentIntake.topic);
  fd.append('type', currentIntake.type);
  fd.append('category', currentIntake.category);
  fd.append('intake', JSON.stringify(answers));
  for(var i=0;i<questions.length;i++){
    var q=questions[i];
    if(q.upload_if){
      var fe=document.getElementById('file-'+q.id);
      if(fe&&fe.files.length>0) fd.append('file_'+q.id, fe.files[0]);
    }
  }
  fetch(BASE+'/api/generate', {
    method:'POST',
    body: fd
  })
  .then(function(r){return r.json()})
  .then(function(data){
    genLoading = false;
    if(data.error){
      output.innerHTML = '<div style="color:#E03131;padding:12px">❌ '+data.error+'</div>';
      return;
    }
    var fmt = data.format ? data.format.toUpperCase() : 'DOCX';
    var sizeStr = data.size_kb ? ' ('+data.size_kb+' KB)' : '';
    var html = '<div style="padding:16px;background:#D3F9D8;border-radius:8px;border:1px solid #2B8A3E;margin-bottom:16px">';
    html += '<div style="font-size:16px;font-weight:700;color:#2B8A3E;margin-bottom:8px">✅ Deliverable gegenereerd</div>';
    html += '<div style="display:flex;gap:8px;flex-wrap:wrap">';
    html += '<a href="'+BASE+data.download+'" class="btn" style="background:#003366;color:#fff;padding:10px 20px;border-radius:6px;text-decoration:none;font-weight:600;font-size:13px">📥 Download '+fmt+sizeStr+'</a>';
    html += '<a href="'+BASE+data.download+'" target="_blank" class="btn" style="background:#fff;color:#003366;border:1px solid #003366;padding:10px 20px;border-radius:6px;text-decoration:none;font-weight:600;font-size:13px">🔗 Openen</a>';
    html += '</div></div>';
    if(data.preview){
      html += '<div style="border:1px solid #E9ECEF;border-radius:8px;padding:16px;background:#FAFBFC">';
      html += '<div style="font-size:12px;font-weight:600;color:#003366;margin-bottom:8px">📄 Preview</div>';
      html += '<div style="font-size:13px;line-height:1.7;color:#333">'+data.preview+'</div>';
      html += '</div>';
    }
    output.innerHTML = html;
    meta.textContent = 'Gegenereerd: '+(data.timestamp||'')+' — '+data.filename;
    if(data.sources && data.sources.length) sourcesEl.innerHTML = '<strong>Bronnen:</strong> '+data.sources.join(' | ');
    // Auto-download
    var a = document.createElement('a');
    a.href = BASE+data.download;
    a.download = data.filename || '';
    document.body.appendChild(a);
    a.click();
    document.body.removeChild(a);
  })
  .catch(function(err){
    output.innerHTML = '<div style="color:#E03131;padding:12px">❌ Fout: '+err.message+'</div>';
    genLoading = false;
  });
}
</script>pt>

""", page_title=svc['title'], active=service_id, svc=svc, BASE_PATH=BASE_PATH)


# ═══════════════════════════════════════════════════════════════════════════

# ─── DELIVERABLE GENERATOR ────────────────────────────────────────────────

@app.route('/api/generate', methods=['POST'])
def generate_deliverable():
    """Generate a file-based deliverable using SOP + knowledge bank context."""
    # Handle multipart (file uploads) or JSON
    files_data = {}
    if request.files:
        for key, f in request.files.items():
            if key.startswith('file_'):
                import uuid as _uuid
                safe_name = re.sub(r'[^A-Za-z0-9._-]', '_', f.filename)
                fpath = os.path.join('/tmp/hseq_uploads', f'{_uuid.uuid4().hex[:8]}_{safe_name}')
                os.makedirs('/tmp/hseq_uploads', exist_ok=True)
                f.save(fpath)
                files_data[key[5:]] = fpath
    if request.content_type and 'multipart' in (request.content_type or ''):
        intake_str = request.form.get('intake', '{}')
        topic = request.form.get('topic', '')
        deliv_type = request.form.get('type', 'advies')
        category = request.form.get('category', '')
        try: intake = json.loads(intake_str)
        except: intake = {}
    else:
        data = request.get_json()
        topic = data.get('topic', '')
        deliv_type = data.get('type', 'advies')
        category = data.get('category', '')
        intake = data.get('intake', {})

    if not topic:
        return jsonify({'error': 'Onderwerp is verplicht'}), 400

    system = """Je bent een senior HSEQ consultant bij JvG Consultancy met 20+ jaar ervaring in de oliën-, gas- en petrochemische industrie. Je produceert deliverables op consultancy-niveau (Arcadis/KWA-niveau) voor BRZO-bedrijven.

n## Golden Standard
n- Feitelijk, beknopt, hyper-professioneel
n- Geen AI-clichés, geen "misschien", geen "waarschijnlijk"
n- Autoritair in je vakgebied
n- Elke aanbeveling MOET gebaseerd zijn op een specifieke wetsregel, AI-blad, PGS-richtlijn of brondocument
n- Gebruik exacte nummers: Arbowet art. 3.4, AI-28 lid 3.2, PGS-15 par. 4.3, BRZO art. 8 lid 1, etc.
n- Risicoberekening: gebruik de risicomatrix (waarschijnlijkheid × Ernst) met concrete scores 1-5
n- Als informatie ontbreekt, vermeld dit expliciet — verzint niets
n- JvG Consultancy branding

n## Deliverable Types
n- advies: Gestructureerd advies met risicobenadering en actiepunten
n- procedure: Stap-voor-stap met verantwoordelijkheden en controlemomenten
n- checklist: Uitvoerbare checklist met inspectiepunten
n- rapport: Formeel rapport met executive summary en aanbevelingen
n- training: Training outline met leerdoelen, inhoud en evaluatie

n## Output
nProduceer als gestructureerde markdown met header, inhoud en bronvermelding."""

    sop = get_sop_context()
    if sop:
        system += '\n' + sop

    from wetgeving_overlay import filter_overlay_by_topic
    rag, sources = get_rag_context(topic, max_chars=15000, category=category)
    # Extract text from uploaded files and add to RAG context
    if files_data:
        for qid, fpath in files_data.items():
            try:
                txt = ''
                if fpath.endswith('.pdf'):
                    try:
                        import PyPDF2
                        with open(fpath, 'rb') as pf:
                            reader = PyPDF2.PdfReader(pf)
                            for pg in reader.pages[:30]:
                                t = pg.extract_text()
                                if t: txt += t + '\n'
                    except Exception:
                        try:
                            import subprocess
                            txt = subprocess.check_output(['pdftotext', fpath, '-'], text=True, timeout=15)
                        except Exception: pass
                elif fpath.endswith('.docx'):
                    try:
                        from docx import Document as _D
                        d = _D(fpath)
                        txt = '\n'.join(p.text for p in d.paragraphs)
                    except Exception: pass
                elif fpath.endswith(('.txt','.md')):
                    with open(fpath, 'r', errors='ignore') as tf:
                        txt = tf.read()
                if txt:
                    rag += '\n\n## Geupload Document: ' + qid + '\n' + txt[:8000]
                    sources.append('Geupload document (' + qid + ')')
            except Exception as e:
                sources.append('Upload fout (' + qid + '): ' + str(e))
    wetgeving = filter_overlay_by_topic(category, topic)
    if rag:
        system += '\n\n## Relevante Documenten (Volledige Tekst):\n' + rag
    if sources:
        system += '\n\n## Bronnen uit Kennisbank:\n' + '\n'.join(f'- {s}' for s in sources)
    if wetgeving:
        system += '\n\n' + wetgeving

    prompts = {
        'advies': f"""Schrijf een uitgebreid professioneel HSEQ-advies over: {topic}.

STRUCTUUR (VERPLICHT):
1. Executive Summary (max 200 woorden)
2. Inleiding en scope
3. Wettelijk kader (verwijs naar Arbowet, BRZO, Seveso, etc.)
4. Risicoanalyse per categorie (gebruik tabellen met kolommen: Risico, Waarschijnlijkheid, Ernst, Risicoklasse, Beheersmaatregel)
5. Aanbevelingen (gesorteerd op prioriteit: hoog/medium/laag)
6. Actieplan met tabel (Actie, Verantwoordelijke, Deadline, Status)
7. Conclusie
8. Bronvermelding

Elke sectie moet minimaal 3-5 alinea's bevatten met concrete, feitelijke inhoud. Geen vage bewoordingen. Verwijs naar specifieke wetgeving en normen.""",
        'procedure': f"""Schrijf een gedetailleerde HSEQ-procedure voor: {topic}.

STRUCTUUR (VERPLICHT):
1. Doel en reikwijdte
2. Definities
3. Verantwoordelijkheden (tabel met rol en taak)
4. Vereisten (materialen, middelen, kwalificaties)
5. Stapsgewijze uitvoering (genummerde stappen met sub-stappen)
6. Veiligheidsmaatregelen per stap
7. Controlemomenten en kwaliteitscriteria
8. Afwijkingsprocedure
9. Documentbeheer en revisiehistorie
10. Bijlagen (checklists, formulieren)

Elke sectie moet concreet en direct toepasbaar zijn.""",
        'checklist': f"""Maak een uitgebreide HSEQ-checklist voor: {topic}.

STRUCTUUR (VERPLICHT):
1. Doel en toepassingsgebied
2. Instructies voor de inspecteur
3. Algemene checklist (10+ items per categorie)
4. Specifieke checklist per werkzaamheid/type
5. Elk item met: inspectiepunt, norm/criterium, methode, frequentie
6. Beoordelingsschaal (goed/na verbetering/afgekeurd)
7. Opmerkingenvelden
8. Afsluiting en ondertekening

Gebruik tabellen voor de checklist-items.""",
        'rapport': f"""Schrijf een uitgebreid HSEQ-rapport over: {topic}.

STRUCTUUR (VERPLICHT):
1. Titelpagina gegevens (project, datum, versie, auteur)
2. Management Summary (max 1 pagina)
3. Inleiding en doelstelling
4. Methodologie en scope
5. Bevindingen per categorie (met tabellen)
6. Risicoclassificatie matrix
7. Conclusies
8. Aanbevelingen met prioriteit
9. Bijlagen en referenties

Schrijf als professioneel adviseur. Gebruik specifieke data en verwijzingen.""",
        'training': f"""Ontwerp een uitgebreide training over: {topic}.

STRUCTUUR (VERPLICHT):
1. Training overzicht (doelgroep, duur, niveau, voorkennis)
2. Leerdoelen (minimaal 5 meetbare doelen)
3. Module 1: Inleiding en context (inclusief wetgeving)
4. Module 2: Theorie en concepten
5. Module 3: Praktijk en toepassing
6. Module 4: Casestudies en voorbeelden
7. Module 5: Evaluatie en toetsing
8. Draaiboek voor de trainer (per module: tijd, activiteit, materiaal)
9. Evalutieformulier

Elke module moet concrete inhoud hebben met voorbeelden uit de praktijk.""",
    }
    gen_prompt = prompts.get(deliv_type, prompts['advies'])

    # Add intake context (intake already defined above from either multipart or JSON)
    if intake:
        ctx_parts = []
        for key, val in intake.items():
            if val:
                if isinstance(val, list):
                    val = ', '.join(val)
                ctx_parts.append(f'- {key}: {val}')
        if ctx_parts:
            gen_prompt += '\n\n## Contextgegevens (uit intake):\n' + '\n'.join(ctx_parts)
            gen_prompt += '\n\nGebruik deze context om het deliverable specifiek te maken. Verwijs naar relevante wetgeving op basis van het type werkzaamheden en risicofactoren.'

    gen_prompt += "\n\nBelangrijk: Schrijf DIRECT in markdown formaat (koppen met #, tabellen met |, bullets met -). Geen inleiding, geen preamble, geen \u0060\u0060\u0060 code blocks eromheen. Begin direct met # Titel.\n\nFORMATTING REGELS:\n- Gebruik > ⚠ voor Belangrijk-callouts (bijv. > ⚠ Deze vereiste is wettelijk verplicht onder BRZO)\n- Maximaal 6 bullets per sectie (voor PPTX compatibiliteit)\n- Voeg na de Executive Summary een ## Samenvatting sectie toe met max 5 bullets\n- Houd tabellen overzichtelijk (max 6 kolommen)\n\nBronvermelding: vermeld alle gebruikte bronnen uit de kennisbank aan het einde onder een ## Bronvermelding sectie."

    messages = [{'role': 'system', 'content': system}, {'role': 'user', 'content': gen_prompt}]

    try:
        response_text = ai_complete(messages)
    except Exception as e:
        return jsonify({'error': f'AI fout: {str(e)}'}), 500

    # Generate file
    file_id = str(uuid.uuid4())[:12]
    ts = datetime.now().strftime('%Y%m%d_%H%M')
    safe_topic = re.sub(r'[^A-Za-z0-9\- ]', '', topic)[:40].strip()

    try:
        if deliv_type == 'training':
            prs = generate_pptx(topic, category, intake, response_text, sources)
            filename = f'{ts}_{safe_topic}_training.pptx'
            filepath = os.path.join(DELIVERABLES_DIR, f'{file_id}_{filename}')
            prs.save(filepath)
            mime = 'application/vnd.openxmlformats-officedocument.presentationml.presentation'
        else:
            doc = generate_docx(topic, deliv_type, category, intake, response_text, sources)
            type_ext = {'advies': 'advies', 'procedure': 'procedure', 'checklist': 'checklist', 'rapport': 'rapport'}
            filename = f'{ts}_{safe_topic}_{type_ext.get(deliv_type, "rapport")}.docx'
            filepath = os.path.join(DELIVERABLES_DIR, f'{file_id}_{filename}')
            doc.save(filepath)
            mime = 'application/vnd.openxmlformats-officedocument.wordprocessingml.document'

        fsize = os.path.getsize(filepath)
        if fsize > 5 * 1024 * 1024:
            return jsonify({'error': 'Bestand te groot (>5MB)'}), 500

        preview = response_text[:1500].replace('\n', '<br>')
        preview = re.sub(r'\*\*(.+?)\*\*', r'<strong>\1</strong>', preview)
        preview = re.sub(r'\*(.+?)\*', r'<em>\1</em>', preview)

        return jsonify({
            'download': f'/api/download/{file_id}',
            'filename': filename,
            'type': deliv_type,
            'format': 'pptx' if deliv_type == 'training' else 'docx',
            'size_kb': round(fsize / 1024),
            'preview': preview,
            'sources': sources,
            'timestamp': datetime.now().strftime('%Y-%m-%d %H:%M')
        })
    except Exception as e:
        return jsonify({'error': f'Bestandsgeneratie fout: {str(e)}'}), 500


@app.route('/api/download/<file_id>')
def download_deliverable(file_id):
    """Serve a generated deliverable file."""
    for fname in os.listdir(DELIVERABLES_DIR):
        if fname.startswith(file_id + '_'):
            filepath = os.path.join(DELIVERABLES_DIR, fname)
            if os.path.isfile(filepath):
                if fname.endswith('.pptx'):
                    mime = 'application/vnd.openxmlformats-officedocument.presentationml.presentation'
                else:
                    mime = 'application/vnd.openxmlformats-officedocument.wordprocessingml.document'
                return send_file(filepath, as_attachment=True, download_name=fname, mimetype=mime)
    return jsonify({'error': 'Bestand niet gevonden'}), 404


# API ENDPOINTS
# ═══════════════════════════════════════════════════════════════════════════
@app.route('/api/stats')
def api_stats():
    return jsonify(get_stats())


@app.route('/api/categories')
def api_categories():
    return jsonify(get_categories())


# ═══════════════════════════════════════════════════════════════════════════
# COMPLIANCE TRACKER MODULE
# ═══════════════════════════════════════════════════════════════════════════

COMPLIANCE_CATEGORIES = ['BRZO', 'Omgevingswet', 'Arbowet', 'ISO 45001', 'ISO 14001', 'ISO 9001', 'VBS', 'Overig']
COMPLIANCE_STATUSES = ['open', 'in_progress', 'compliant', 'overdue', 'not_applicable']
COMPLIANCE_PRIORITIES = ['critical', 'high', 'medium', 'low']


def init_db_compliance():
    """Initialize compliance_items table."""
    try:
        conn = get_db()
        conn.execute('''CREATE TABLE IF NOT EXISTS compliance_items (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            category TEXT NOT NULL,
            requirement TEXT NOT NULL,
            article_ref TEXT,
            status TEXT DEFAULT 'open',
            priority TEXT DEFAULT 'medium',
            owner TEXT,
            deadline TEXT,
            evidence TEXT,
            notes TEXT,
            created_date TEXT DEFAULT CURRENT_TIMESTAMP,
            updated_date TEXT DEFAULT CURRENT_TIMESTAMP,
            due_soon INTEGER DEFAULT 0
        )''')
        conn.commit()
    except Exception as e:
        print(f"[Compliance] DB init error: {e}")


def seed_compliance_data():
    """Seed BRZO 2015 compliance items if table is empty."""
    try:
        conn = get_db()
        cursor = conn.execute('SELECT COUNT(*) FROM compliance_items')
        count = cursor.fetchone()[0]
        if count > 0:
            return  # Already seeded
        
        brzo_items = [
            ('BRZO', 'Veiligheidsrapport (MID)', 'BRZO 2015, art. 9', 'open', 'critical', None, None, None, 'Major Incident Domains analyse vereist'),
            ('BRZO', 'Extern noodplan / Company Emergency Plan', 'BRZO 2015, art. 12', 'open', 'critical', None, None, None, 'Coördinatie met regionale hulpverleners'),
            ('BRZO', 'Intern noodplan', 'BRZO 2015, art. 11', 'open', 'high', None, None, None, 'Inclusief BHV en ontruimingsoefeningen'),
            ('BRZO', 'Veiligheidsbeheerssysteem (VBS)', 'BRZO 2015, art. 8', 'open', 'critical', None, None, None, 'MOC, procedures, competentiebeheer'),
            ('BRZO', 'Melding zwaar ongeval', 'BRZO 2015, art. 15', 'open', 'critical', None, None, None, 'Binnen 24 uur melden bij ILT'),
            ('BRZO', 'Informatie aan publiek', 'BRZO 2015, art. 14', 'open', 'medium', None, None, None, 'Voorlichting aan omwonenden'),
            ('BRZO', 'ARIE-notificatie', 'BRZO 2015, art. 7', 'open', 'high', None, None, None, 'Algemene Richtlijn Industriële Veiligheid'),
            ('BRZO', 'Preventiebeleid', 'BRZO 2015, art. 5', 'open', 'high', None, None, None, 'Kader voor risicobeheersing'),
            ('BRZO', 'Kwantitatieve risicoberekening (QRA)', 'BRZO 2015, art. 9', 'open', 'high', None, None, None, 'Effectafstanden en risicocontouren'),
            ('BRZO', 'Bevi (Besluit externe veiligheid inrichtingen)', 'Bevi', 'open', 'high', None, None, None, 'LPG, grootschalige opslag'),
            ('BRZO', 'Inspectie-readiness check', 'Intern', 'open', 'medium', None, None, None, 'Voorbereiding op ILT/OD inspecties'),
            ('BRZO', 'VKA-keuringen (veiligheidskritische apparatuur)', 'Intern', 'open', 'high', None, None, None, 'Periodieke inspectie en onderhoud'),
            ('BRZO', 'Milieupermissies / vergunningen', 'Omgevingswet', 'open', 'high', None, None, None, 'Actuele vergunningen check'),
            ('Arbowet', 'Arbo RI&E', 'Arbowet art. 5', 'open', 'high', None, None, None, 'Risico-inventarisatie & evaluatie'),
            ('ISO 45001', 'ISO audit planning', 'ISO 45001', 'open', 'medium', None, None, None, 'Interne en externe audits'),
        ]
        
        for item in brzo_items:
            conn.execute('''INSERT INTO compliance_items 
                (category, requirement, article_ref, status, priority, owner, deadline, evidence, notes)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)''', item)
        conn.commit()
        print(f"[Compliance] Seeded {len(brzo_items)} BRZO items")
    except Exception as e:
        print(f"[Compliance] Seed error: {e}")


def get_compliance_stats():
    """Get compliance statistics."""
    try:
        conn = get_db()
        stats = {'total': 0, 'compliant': 0, 'in_progress': 0, 'open': 0, 'overdue': 0, 'not_applicable': 0, 'due_soon': 0, 'compliant_pct': 0}
        cursor = conn.execute('SELECT COUNT(*) FROM compliance_items')
        stats['total'] = cursor.fetchone()[0] or 0
        
        cursor = conn.execute("SELECT COUNT(*) FROM compliance_items WHERE status='compliant'")
        stats['compliant'] = cursor.fetchone()[0] or 0
        
        cursor = conn.execute("SELECT COUNT(*) FROM compliance_items WHERE status='in_progress'")
        stats['in_progress'] = cursor.fetchone()[0] or 0
        
        cursor = conn.execute("SELECT COUNT(*) FROM compliance_items WHERE status='open'")
        stats['open'] = cursor.fetchone()[0] or 0
        
        cursor = conn.execute("SELECT COUNT(*) FROM compliance_items WHERE status='overdue'")
        stats['overdue'] = cursor.fetchone()[0] or 0
        
        cursor = conn.execute("SELECT COUNT(*) FROM compliance_items WHERE status='not_applicable'")
        stats['not_applicable'] = cursor.fetchone()[0] or 0
        
        cursor = conn.execute('SELECT COUNT(*) FROM compliance_items WHERE due_soon=1')
        stats['due_soon'] = cursor.fetchone()[0] or 0
        
        if stats['total'] > 0:
            stats['compliant_pct'] = round((stats['compliant'] / stats['total']) * 100, 1)
        
        # Per category
        cursor = conn.execute('SELECT category, COUNT(*) as cnt FROM compliance_items GROUP BY category')
        stats['by_category'] = {row[0]: row[1] for row in cursor.fetchall()}
        
        return stats
    except Exception as e:
        print(f"[Compliance] Stats error: {e}")
        return {'total': 0, 'compliant': 0, 'in_progress': 0, 'open': 0, 'overdue': 0, 'not_applicable': 0, 'due_soon': 0, 'compliant_pct': 0, 'by_category': {}}


def update_due_soon_flags():
    """Update due_soon flags based on deadlines."""
    try:
        conn = get_db()
        today = date.today()
        conn.execute('''UPDATE compliance_items 
            SET due_soon = CASE 
                WHEN deadline IS NOT NULL AND deadline != '' 
                AND date(deadline) BETWEEN date(?) AND date(?, '+30 days')
                THEN 1 ELSE 0 END,
            status = CASE
                WHEN deadline IS NOT NULL AND deadline != '' 
                AND date(deadline) < date(?) AND status = 'open'
                THEN 'overdue' ELSE status END
        ''', (today.isoformat(), today.isoformat(), today.isoformat()))
        conn.commit()
    except Exception as e:
        print(f"[Compliance] Due soon update error: {e}")


@app.route('/compliance')
def compliance_page():
    """Compliance Tracker pagina."""
    try:
        init_db_compliance()
        seed_compliance_data()
        update_due_soon_flags()
        stats = get_compliance_stats()
        
        # Filters
        category_filter = request.args.get('category', '')
        status_filter = request.args.get('status', '')
        priority_filter = request.args.get('priority', '')
        search_query = request.args.get('q', '').strip()
        
        conn = get_db()
        query = 'SELECT * FROM compliance_items WHERE 1=1'
        params = []
        
        if category_filter:
            query += ' AND category = ?'
            params.append(category_filter)
        if status_filter:
            query += ' AND status = ?'
            params.append(status_filter)
        if priority_filter:
            query += ' AND priority = ?'
            params.append(priority_filter)
        if search_query:
            query += ' AND (requirement LIKE ? OR article_ref LIKE ? OR notes LIKE ?)'
            like_term = f'%{search_query}%'
            params.extend([like_term, like_term, like_term])
        
        query += ' ORDER BY CASE priority WHEN "critical" THEN 1 WHEN "high" THEN 2 WHEN "medium" THEN 3 ELSE 4 END, deadline ASC'
        
        cursor = conn.execute(query, params)
        items = cursor.fetchall()
        
        # Convert to list of dicts
        items_list = []
        for row in items:
            items_list.append({
                'id': row[0],
                'category': row[1],
                'requirement': row[2],
                'article_ref': row[3],
                'status': row[4],
                'priority': row[5],
                'owner': row[6],
                'deadline': row[7],
                'evidence': row[8],
                'notes': row[9],
                'created_date': row[10],
                'updated_date': row[11],
                'due_soon': row[12]
            })
    except Exception as e:
        print(f"[Compliance] Page error: {e}")
        stats = {'total': 0, 'compliant': 0, 'overdue': 0, 'due_soon': 0, 'compliant_pct': 0, 'by_category': {}}
        items_list = []
    
    return page('''

<!-- Stats Bar -->
<div class="stats-grid" style="margin-bottom:20px">
  <div class="stat-card">
    <div class="value">{{stats['total']}}</div>
    <div class="label">Totaal Items</div>
  </div>
  <div class="stat-card" style="border-left:3px solid #2B8A3E">
    <div class="value" style="color:#2B8A3E">{{stats['compliant_pct']}}%</div>
    <div class="label">Compliant</div>
  </div>
  <div class="stat-card" style="border-left:3px solid #E03131">
    <div class="value" style="color:#E03131">{{stats['overdue']}}</div>
    <div class="label">Overdue</div>
  </div>
  <div class="stat-card" style="border-left:3px solid #FF922B">
    <div class="value" style="color:#FF922B">{{stats['due_soon']}}</div>
    <div class="label">Due Soon (30d)</div>
  </div>
</div>

<!-- Filters -->
<div class="compliance-filters">
  <form method="get" style="display:flex;gap:8px;flex-wrap:wrap;align-items:center">
    <select name="category" onchange="this.form.submit()">
      <option value="">Alle categorieën</option>
      {% for cat in categories %}
      <option value="{{cat}}" {% if category_filter==cat %}selected{% endif %}>{{cat}}</option>
      {% endfor %}
    </select>
    <select name="status" onchange="this.form.submit()">
      <option value="">Alle statussen</option>
      {% for st in statuses %}
      <option value="{{st}}" {% if status_filter==st %}selected{% endif %}>{{st|replace('_',' ')|title}}</option>
      {% endfor %}
    </select>
    <select name="priority" onchange="this.form.submit()">
      <option value="">Alle prioriteiten</option>
      {% for pr in priorities %}
      <option value="{{pr}}" {% if priority_filter==pr %}selected{% endif %}>{{pr|title}}</option>
      {% endfor %}
    </select>
    <input type="text" name="q" value="{{search_query}}" placeholder="Zoeken..." style="width:150px">
    <button type="submit" class="btn btn-sm btn-outline">🔍</button>
    {% if category_filter or status_filter or priority_filter or search_query %}
    <a href="{{BASE_PATH}}/compliance" class="btn btn-sm btn-outline">✕ Reset</a>
    {% endif %}
  </form>
  <div style="margin-left:auto">
    <button onclick="openAddModal()" class="btn btn-sm btn-primary">+ Nieuw Item</button>
  </div>
</div>

<!-- Tabel -->
<div class="card">
  <h2>⚖️ Compliance Tracker</h2>
  {% if items %}
  <div style="overflow-x:auto">
    <table class="compliance-table">
      <thead>
        <tr>
          <th>Requirement</th>
          <th>Categorie</th>
          <th>Status</th>
          <th>Priority</th>
          <th>Owner</th>
          <th>Deadline</th>
          <th>Actions</th>
        </tr>
      </thead>
      <tbody>
      {% for item in items %}
      <tr>
        <td>
          <div style="font-weight:500">{{item['requirement']}}</div>
          {% if item['article_ref'] %}<div style="font-size:11px;color:#868E96">{{item['article_ref']}}</div>{% endif %}
        </td>
        <td><span class="badge badge-overig">{{item['category']}}</span></td>
        <td><span class="badge status-{{item['status']}}">{{item['status']|replace('_',' ')|title}}</span></td>
        <td><span class="badge priority-{{item['priority']}}">{{item['priority']|title}}</span></td>
        <td class="editable" onclick="editField({{item['id']}},'owner',this)">{{item['owner'] or '—'}}</td>
        <td class="{% if item['status']=='overdue' %}deadline-overdue{% elif item['due_soon'] %}deadline-soon{% endif %}">
          {{item['deadline'] or '—'}}
        </td>
        <td>
          <div class="compliance-actions">
            <button onclick="editItem({{item['id']}})" class="btn btn-sm btn-outline" title="Bewerk">✏️</button>
            <button onclick="deleteItem({{item['id']}})" class="btn btn-sm btn-danger" title="Verwijder">🗑️</button>
          </div>
        </td>
      </tr>
      {% endfor %}
      </tbody>
    </table>
  </div>
  {% else %}
  <div class="empty-state">
    <div class="emoji">⚖️</div>
    <h3>Geen compliance items</h3>
    <p>Voeg items toe of wijzig filters.</p>
  </div>
  {% endif %}
</div>

<!-- Add/Edit Modal -->
<div id="complianceModal" style="display:none;position:fixed;top:0;left:0;right:0;bottom:0;background:rgba(0,0,0,.5);z-index:1000;align-items:center;justify-content:center">
  <div style="background:#fff;border-radius:10px;padding:24px;width:90%;max-width:500px;max-height:90vh;overflow-y:auto">
    <div style="display:flex;justify-content:space-between;align-items:center;margin-bottom:16px">
      <h3 id="modalTitle" style="margin:0">Nieuw Item</h3>
      <button onclick="closeModal()" style="background:none;border:none;font-size:20px;cursor:pointer">✕</button>
    </div>
    <form id="complianceForm" onsubmit="return saveItem(event)">
      <input type="hidden" id="itemId" name="id">
      <div style="margin-bottom:12px">
        <label style="display:block;font-size:12px;font-weight:600;margin-bottom:4px">Categorie *</label>
        <select id="itemCategory" name="category" required style="width:100%;padding:8px;border:1px solid #DEE2E6;border-radius:6px">
          {% for cat in categories %}<option value="{{cat}}">{{cat}}</option>{% endfor %}
        </select>
      </div>
      <div style="margin-bottom:12px">
        <label style="display:block;font-size:12px;font-weight:600;margin-bottom:4px">Requirement *</label>
        <input type="text" id="itemRequirement" name="requirement" required style="width:100%;padding:8px;border:1px solid #DEE2E6;border-radius:6px">
      </div>
      <div style="margin-bottom:12px">
        <label style="display:block;font-size:12px;font-weight:600;margin-bottom:4px">Artikel/Wetverwijzing</label>
        <input type="text" id="itemArticle" name="article_ref" style="width:100%;padding:8px;border:1px solid #DEE2E6;border-radius:6px">
      </div>
      <div style="display:grid;grid-template-columns:1fr 1fr;gap:12px;margin-bottom:12px">
        <div>
          <label style="display:block;font-size:12px;font-weight:600;margin-bottom:4px">Status</label>
          <select id="itemStatus" name="status" style="width:100%;padding:8px;border:1px solid #DEE2E6;border-radius:6px">
            {% for st in statuses %}<option value="{{st}}">{{st|replace('_',' ')|title}}</option>{% endfor %}
          </select>
        </div>
        <div>
          <label style="display:block;font-size:12px;font-weight:600;margin-bottom:4px">Priority</label>
          <select id="itemPriority" name="priority" style="width:100%;padding:8px;border:1px solid #DEE2E6;border-radius:6px">
            {% for pr in priorities %}<option value="{{pr}}">{{pr|title}}</option>{% endfor %}
          </select>
        </div>
      </div>
      <div style="display:grid;grid-template-columns:1fr 1fr;gap:12px;margin-bottom:12px">
        <div>
          <label style="display:block;font-size:12px;font-weight:600;margin-bottom:4px">Eigenaar</label>
          <input type="text" id="itemOwner" name="owner" style="width:100%;padding:8px;border:1px solid #DEE2E6;border-radius:6px">
        </div>
        <div>
          <label style="display:block;font-size:12px;font-weight:600;margin-bottom:4px">Deadline</label>
          <input type="date" id="itemDeadline" name="deadline" style="width:100%;padding:8px;border:1px solid #DEE2E6;border-radius:6px">
        </div>
      </div>
      <div style="margin-bottom:12px">
        <label style="display:block;font-size:12px;font-weight:600;margin-bottom:4px">Evidence</label>
        <input type="text" id="itemEvidence" name="evidence" placeholder="Pad naar bewijsstuk of beschrijving" style="width:100%;padding:8px;border:1px solid #DEE2E6;border-radius:6px">
      </div>
      <div style="margin-bottom:16px">
        <label style="display:block;font-size:12px;font-weight:600;margin-bottom:4px">Notes</label>
        <textarea id="itemNotes" name="notes" rows="3" style="width:100%;padding:8px;border:1px solid #DEE2E6;border-radius:6px"></textarea>
      </div>
      <div style="display:flex;gap:8px">
        <button type="submit" class="btn btn-primary" style="flex:1">💾 Opslaan</button>
        <button type="button" onclick="closeModal()" class="btn btn-outline">Annuleren</button>
      </div>
    </form>
  </div>
</div>

<script>
var BASE = '{{BASE_PATH}}';

function openAddModal() {
  document.getElementById('modalTitle').textContent = 'Nieuw Compliance Item';
  document.getElementById('complianceForm').reset();
  document.getElementById('itemId').value = '';
  document.getElementById('complianceModal').style.display = 'flex';
}

function editItem(id) {
  fetch(BASE + '/api/compliance/' + id)
    .then(r => r.json())
    .then(data => {
      if (data.error) { alert(data.error); return; }
      document.getElementById('modalTitle').textContent = 'Bewerk Item';
      document.getElementById('itemId').value = data.id;
      document.getElementById('itemCategory').value = data.category || '';
      document.getElementById('itemRequirement').value = data.requirement || '';
      document.getElementById('itemArticle').value = data.article_ref || '';
      document.getElementById('itemStatus').value = data.status || 'open';
      document.getElementById('itemPriority').value = data.priority || 'medium';
      document.getElementById('itemOwner').value = data.owner || '';
      document.getElementById('itemDeadline').value = data.deadline || '';
      document.getElementById('itemEvidence').value = data.evidence || '';
      document.getElementById('itemNotes').value = data.notes || '';
      document.getElementById('complianceModal').style.display = 'flex';
    })
    .catch(err => alert('Fout bij laden: ' + err));
}

function closeModal() {
  document.getElementById('complianceModal').style.display = 'none';
}

function saveItem(e) {
  e.preventDefault();
  var id = document.getElementById('itemId').value;
  var formData = new FormData(document.getElementById('complianceForm'));
  var data = {};
  formData.forEach((v, k) => data[k] = v);
  
  var url = BASE + '/api/compliance';
  var method = 'POST';
  if (id) {
    url = BASE + '/api/compliance/' + id;
    method = 'PUT';
  }
  
  fetch(url, {
    method: method,
    headers: {'Content-Type': 'application/json'},
    body: JSON.stringify(data)
  })
  .then(r => r.json())
  .then(result => {
    if (result.error) { alert(result.error); return; }
    closeModal();
    location.reload();
  })
  .catch(err => alert('Fout bij opslaan: ' + err));
  
  return false;
}

function deleteItem(id) {
  if (!confirm('Weet je zeker dat je dit item wilt verwijderen?')) return;
  fetch(BASE + '/api/compliance/' + id, {method: 'DELETE'})
    .then(r => r.json())
    .then(result => {
      if (result.error) { alert(result.error); return; }
      location.reload();
    })
    .catch(err => alert('Fout bij verwijderen: ' + err));
}

function editField(id, field, el) {
  var current = el.textContent.trim();
  if (current === '—') current = '';
  var newVal = prompt('Nieuwe waarde voor ' + field + ':', current);
  if (newVal === null) return;
  
  fetch(BASE + '/api/compliance/' + id, {
    method: 'PUT',
    headers: {'Content-Type': 'application/json'},
    body: JSON.stringify({[field]: newVal})
  })
  .then(r => r.json())
  .then(result => {
    if (result.error) { alert(result.error); return; }
    el.textContent = newVal || '—';
  })
    .catch(err => alert('Fout bij opslaan: ' + err));
}
</script>

''', 
        page_title='Compliance Tracker', 
        active='compliance', 
        stats=stats, 
        items=items_list,
        categories=COMPLIANCE_CATEGORIES,
        statuses=COMPLIANCE_STATUSES,
        priorities=COMPLIANCE_PRIORITIES,
        category_filter=category_filter,
        status_filter=status_filter,
        priority_filter=priority_filter,
        search_query=search_query,
        BASE_PATH=BASE_PATH)


@app.route('/api/compliance')
def api_compliance_list():
    """Get all compliance items as JSON."""
    try:
        conn = get_db()
        cursor = conn.execute('SELECT * FROM compliance_items ORDER BY id DESC')
        items = []
        for row in cursor.fetchall():
            items.append({
                'id': row[0], 'category': row[1], 'requirement': row[2],
                'article_ref': row[3], 'status': row[4], 'priority': row[5],
                'owner': row[6], 'deadline': row[7], 'evidence': row[8],
                'notes': row[9], 'created_date': row[10], 'updated_date': row[11],
                'due_soon': row[12]
            })
        return jsonify({'items': items})
    except Exception as e:
        return jsonify({'error': str(e)}), 500


@app.route('/api/compliance/stats')
def api_compliance_stats():
    """Get compliance statistics."""
    return jsonify(get_compliance_stats())


@app.route('/api/compliance/<int:item_id>')
def api_compliance_get(item_id):
    """Get a single compliance item."""
    try:
        conn = get_db()
        cursor = conn.execute('SELECT * FROM compliance_items WHERE id = ?', (item_id,))
        row = cursor.fetchone()
        if not row:
            return jsonify({'error': 'Item niet gevonden'}), 404
        return jsonify({
            'id': row[0], 'category': row[1], 'requirement': row[2],
            'article_ref': row[3], 'status': row[4], 'priority': row[5],
            'owner': row[6], 'deadline': row[7], 'evidence': row[8],
            'notes': row[9], 'created_date': row[10], 'updated_date': row[11],
            'due_soon': row[12]
        })
    except Exception as e:
        return jsonify({'error': str(e)}), 500


@app.route('/api/compliance', methods=['POST'])
def api_compliance_create():
    """Create a new compliance item."""
    try:
        data = request.get_json()
        if not data.get('requirement'):
            return jsonify({'error': 'Requirement is verplicht'}), 400
        
        conn = get_db()
        conn.execute('''INSERT INTO compliance_items 
            (category, requirement, article_ref, status, priority, owner, deadline, evidence, notes)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)''',
            (data.get('category', 'Overig'), data['requirement'], data.get('article_ref'),
             data.get('status', 'open'), data.get('priority', 'medium'),
             data.get('owner'), data.get('deadline'), data.get('evidence'), data.get('notes')))
        conn.commit()
        return jsonify({'success': True, 'message': 'Item toegevoegd'})
    except Exception as e:
        return jsonify({'error': str(e)}), 500


@app.route('/api/compliance/<int:item_id>', methods=['PUT'])
def api_compliance_update(item_id):
    """Update a compliance item."""
    try:
        data = request.get_json()
        conn = get_db()
        
        # Build update query dynamically
        fields = []
        values = []
        for key in ['category', 'requirement', 'article_ref', 'status', 'priority', 'owner', 'deadline', 'evidence', 'notes']:
            if key in data:
                fields.append(f'{key} = ?')
                values.append(data[key])
        
        if not fields:
            return jsonify({'error': 'Geen velden om te updaten'}), 400
        
        fields.append('updated_date = CURRENT_TIMESTAMP')
        values.append(item_id)
        
        conn.execute(f"UPDATE compliance_items SET {', '.join(fields)} WHERE id = ?", values)
        conn.commit()
        return jsonify({'success': True, 'message': 'Item bijgewerkt'})
    except Exception as e:
        return jsonify({'error': str(e)}), 500


@app.route('/api/compliance/<int:item_id>', methods=['DELETE'])
def api_compliance_delete(item_id):
    """Delete a compliance item."""
    try:
        conn = get_db()
        conn.execute('DELETE FROM compliance_items WHERE id = ?', (item_id,))
        conn.commit()
        return jsonify({'success': True, 'message': 'Item verwijderd'})
    except Exception as e:
        return jsonify({'error': str(e)}), 500


# ═══════════════════════════════════════════════════════════════════════════
# RISK & ACTION MANAGEMENT MODULE (Fase 2)
# ═══════════════════════════════════════════════════════════════════════════

RISK_CATEGORIES = ['Process Safety', 'Chemical', 'Mechanical', 'Electrical', 'Ergonomic', 'Environmental']
RISK_SOURCES = ['HAZOP', 'LOPA', 'RI&E', 'Inspectie', 'Incident', 'Audit']
RISK_STATUSES = ['open', 'in_progress', 'closed', 'monitoring']
RISK_PRIORITIES = ['critical', 'high', 'medium', 'low']
ACTION_PHASES = ['plan', 'do', 'check', 'act']
ACTION_STATUSES = ['open', 'in_progress', 'completed', 'overdue', 'cancelled']
ACTION_CATEGORIES = ['Safety', 'Environment', 'Quality', 'Process', 'Training', 'Maintenance']

def init_db_risks():
    try:
        conn = sqlite3.connect(DB_PATH)
        c = conn.cursor()
        c.execute('''CREATE TABLE IF NOT EXISTS risk_items (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            hazard TEXT NOT NULL, category TEXT NOT NULL, source TEXT,
            likelihood REAL DEFAULT 3.0, exposure REAL DEFAULT 2.0, consequence REAL DEFAULT 7.0,
            risk_score REAL, risk_category TEXT,
            existing_controls TEXT, gap TEXT, recommended_action TEXT,
            action_type TEXT, priority TEXT DEFAULT 'medium',
            status TEXT DEFAULT 'open', owner TEXT,
            target_date TEXT, completion_date TEXT,
            residual_likelihood REAL, residual_exposure REAL, residual_consequence REAL,
            residual_score REAL, residual_category TEXT,
            notes TEXT,
            created_date TEXT DEFAULT CURRENT_TIMESTAMP,
            updated_date TEXT DEFAULT CURRENT_TIMESTAMP
        )''')
        c.execute('''CREATE TABLE IF NOT EXISTS action_items (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            risk_id INTEGER, title TEXT NOT NULL, description TEXT,
            phase TEXT DEFAULT 'plan', priority TEXT DEFAULT 'medium',
            status TEXT DEFAULT 'open', category TEXT,
            owner TEXT, assignee TEXT,
            created_date TEXT DEFAULT CURRENT_TIMESTAMP,
            due_date TEXT, completion_date TEXT,
            progress INTEGER DEFAULT 0, evidence TEXT, notes TEXT,
            updated_date TEXT DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (risk_id) REFERENCES risk_items(id)
        )''')
        conn.commit(); conn.close()
        seed_risk_data()
    except Exception as e:
        print(f"[WARN] init_db_risks: {e}")

# Fine & Kinney Risk Matrix — BRZO standaard
# Likelihood (A): 0.1–10 | Exposure (B): 0.5–10 | Consequence (C): 1–100
# Risk Score = A × B × C
# Categorieën: Trivial(<20) Tolerable(20-70) Moderate(70-200) Substantial(200-500) Intolerable(>500)
FK_LIKELIHOOD = [
    (0.1, 'A - Practically impossible'),
    (0.2, 'B - Remote'),
    (0.5, 'C - Seldom'),
    (1.0, 'D - Unlikely'),
    (3.0, 'E - Possible'),
    (6.0, 'F - Likely'),
    (10.0, 'G - Completely certain'),
]
FK_EXPOSURE = [
    (0.5, 'A - Very rare'),
    (1.0, 'B - Rare'),
    (2.0, 'C - Occasional'),
    (3.0, 'D - Regular'),
    (6.0, 'E - Continuous'),
    (10.0, 'F - Permanent'),
]
FK_CONSEQUENCE = [
    (1, 'A - Minor injury / no production loss'),
    (3, 'B - Medical treatment / minor production loss'),
    (7, 'C - Lost workday injury / moderate production loss'),
    (15, 'D - Permanent disability / major production loss'),
    (40, 'E - Single fatality / facility shutdown'),
    (100, 'F - Multiple fatalities / catastrophic'),
]

def fk_risk_score(a, b, c):
    """Fine & Kinney: A × B × C → (score, category, color)"""
    s = (a or 1.0) * (b or 1.0) * (c or 1.0)
    if s < 20: return s, 'Trivial', '#2B8A3E'
    if s < 70: return s, 'Tolerable', '#51CF66'
    if s < 200: return s, 'Moderate', '#F76707'
    if s < 500: return s, 'Substantial', '#E03131'
    return s, 'Intolerable', '#C92A2A'

def calc_risk(l, c):
    """Fallback legacy matrix (niet meer primair)"""
    s = (l or 0) * (c or 0)
    if s <= 6: return s, 'Low'
    if s <= 12: return s, 'Medium'
    if s <= 19: return s, 'High'
    return s, 'Extreme'

def seed_risk_data():
    try:
        conn = sqlite3.connect(DB_PATH)
        cnt = conn.execute('SELECT COUNT(*) FROM risk_items').fetchone()[0]
        if cnt > 0:
            conn.close(); return
        # Fine & Kinney seed data: (hazard, category, source, likelihood, exposure, consequence,
        #   existing_controls, gap, recommended_action, action_type, priority)
        risks = [
            ('Chemische lekkage opslagtank BRZO-stof','Process Safety','HAZOP',3.0,6.0,15.0,
                'Tanks.inspectie, lekdetectie, dubbele wand','Onduidelijkheid over maximale vullingsgraad bij hoge bezetting',
                'Installeer extra level transmitter met alarm op 85%',
                'Engineering','critical'),
            ('Overschrijding NOx emissiegrenzen stack C','Environmental','RI&E',6.0,3.0,7.0,
                'Continue emissiemonitoring (CEMS)','Geen automatische procesafsluiting bij limietoverschrijding',
                'Implementeer automatische cut-off bij 90% limiet',
                'Engineering','high'),
            ('Onvoldoende werkvergunningprocedure','Mechanical','Inspectie',6.0,6.0,3.0,
                'Paper-based PTW, checklist aanwezig','Geen digitaal goedkeuringsproces, vertragingen','Digitaliseer PTW workflow met mobiele app',
                'Administrative','high'),
            ('Blootstelling gevaarlijke stoffen personeel','Chemical','HAZOP',3.0,6.0,7.0,
                'PBM beschikbaar, LEL meters op perron','Onvoldoende gasdetectie dekking in zone B','Uitbreiding gasdetectie netwerk zone B',
                'Engineering','high'),
            ('Falen veiligheidskritische apparatuur (VKA)','Mechanical','LOPA',1.0,3.0,40.0,
                'Preventief onderhoud schema, reservoir analyse','Onvoldoende SIL-classificatie voor SIF functies',
                'Voer SIL-studie uit volgens IEC 61511',
                'Engineering','critical'),
            ('Brand/explosie procesgebied ATEX zone 1','Process Safety','HAZOP',0.5,3.0,40.0,
                'ATEX zones gedefinieerd, Ex-apparatuur','Onvoldoende frequentie ATEX-inspecties','Intensificeer ATEX-inspectieprogramma naar kwartaal',
                'Maintenance','high'),
            ('Ongeval tijdens onderhoudswerkzaamheden','Mechanical','Incident',3.0,6.0,7.0,
                'LOTO procedure aanwezig','Niet altijd consequent toegepast, geen verificatie',
                'Versterk LOTO training + onafhankelijke verificatie',
                'Training','medium'),
            ('Onvoldoende noodrespons training','Training','Audit',3.0,2.0,7.0,
                'Jaarlijke BRZO-oefeningen','Scenario\'s te beperkt, geen externe oefening',
                'Breid scenario\'s uit + jaarlijke externe BRZO-oefening',
                'Training','moderate'),
            ('Milieubelasting afwatering olie/water separator','Environmental','RI&E',1.0,3.0,7.0,
                'Oliewater separator aanwezig','Onvoldoende monitoring van effluent kwaliteit',
                'Installeer online olie-in-water monitor',
                'Engineering','moderate'),
            ('Onjuist aannemersbeheer pre-qualification','Mechanical','Inspectie',6.0,3.0,3.0,
                'Aannemerscontract template','Geen gestructureerd pre-qualification proces',
                'Implementeer VCA+ pre-qual systeem',
                'Administrative','medium'),
        ]
        actions_data = []
        for i, (h,cat,src,l,e,c,ctrl,gap,act,atype,pri) in enumerate(risks, 1):
            s, cat_f, clr = fk_risk_score(l, e, c)
            conn.execute('''INSERT INTO risk_items (hazard,category,source,likelihood,exposure,consequence,
                risk_score,risk_category,existing_controls,gap,recommended_action,action_type,priority)
                VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)''', (h,cat,src,l,e,c,round(s,1),cat_f,ctrl,gap,act,atype,pri))
            if i <= 3:
                actions_data.append((i, f'Actie {i}.1: {act[:50]}', 'Plan fase: onderzoek en specificatie', 'plan', pri, 'Safety'))
                actions_data.append((i, f'Actie {i}.2: Implementatie {h[:30]}', 'Implementatie en validatie', 'do', pri, 'Process'))
        for risk_id, title, desc, phase, pri, cat in actions_data:
            conn.execute('''INSERT INTO action_items (risk_id,title,description,phase,priority,status,category)
                VALUES (?,?,?,?,?,?,?)''', (risk_id, title, desc, phase, pri, 'open', cat))
        conn.commit(); conn.close()
        print("[OK] Seeded 10 risks (Fine & Kinney) + 20 actions")
    except Exception as e:
        print(f"[WARN] seed_risk_data: {e}")

# --- API Routes ---
@app.route('/api/risks')
def api_risks_list():
    try:
        conn = sqlite3.connect(DB_PATH); conn.row_factory = sqlite3.Row
        q = 'SELECT * FROM risk_items WHERE 1=1'
        params = []
        for f in ['category','risk_category','status','source']:
            v = request.args.get(f)
            if v: q += f' AND {f}=?'; params.append(v)
        s = request.args.get('search')
        if s: q += ' AND hazard LIKE ?'; params.append(f'%{s}%')
        q += ' ORDER BY risk_score DESC'
        rows = [dict(r) for r in conn.execute(q, params).fetchall()]
        conn.close()
        return jsonify({'items': rows, 'total': len(rows)})
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@app.route('/api/risks/stats')
def api_risks_stats():
    try:
        conn = sqlite3.connect(DB_PATH); conn.row_factory = sqlite3.Row
        total = conn.execute('SELECT COUNT(*) as c FROM risk_items').fetchone()['c']
        categories = {}
        for r in conn.execute('SELECT risk_category, COUNT(*) as c FROM risk_items GROUP BY risk_category').fetchall():
            categories[r['risk_category'] or 'open'] = r['c']
        cats = {}
        for r in conn.execute('SELECT category, COUNT(*) as c FROM risk_items GROUP BY category').fetchall():
            cats[r['category']] = r['c']
        avg = conn.execute('SELECT COALESCE(AVG(risk_score),0) as a FROM risk_items').fetchone()['a']
        intolerable = categories.get('Intolerable',0) + categories.get('Substantial',0)
        open_acts = conn.execute("SELECT COUNT(*) as c FROM action_items WHERE status='open'").fetchone()['c']
        conn.close()
        return jsonify({'total': total, 'by_category_fk': categories, 'by_hazard_type': cats, 'avg_score': round(avg,1),
            'intolerable_substantial': intolerable, 'open_actions': open_acts})
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@app.route('/api/risks', methods=['POST'])
def api_risk_create():
    try:
        d = request.get_json(); conn = sqlite3.connect(DB_PATH)
        a, b, c = d.get('likelihood',3.0), d.get('exposure',2.0), d.get('consequence',7.0)
        s, cat_f, clr = fk_risk_score(a, b, c)
        conn.execute('''INSERT INTO risk_items (hazard,category,source,likelihood,exposure,consequence,
            risk_score,risk_category,existing_controls,gap,recommended_action,action_type,priority,owner,target_date,notes)
            VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)''',
            (d.get('hazard',''), d.get('category','Process Safety'), d.get('source'),
             a, b, c, round(s,1), cat_f,
             d.get('existing_controls'), d.get('gap'), d.get('recommended_action'),
             d.get('action_type'), d.get('priority','medium'), d.get('owner'), d.get('target_date'), d.get('notes')))
        conn.commit(); rid = conn.execute('SELECT last_insert_rowid()').fetchone()[0]; conn.close()
        return jsonify({'id': rid, 'risk_score': round(s,1), 'risk_category': cat_f})
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@app.route('/api/risks/<int:rid>', methods=['PUT'])
def api_risk_update(rid):
    try:
        d = request.get_json(); conn = sqlite3.connect(DB_PATH)
        sets = []; params = []
        for k in ['hazard','category','source','existing_controls','gap','recommended_action',
                  'action_type','priority','status','owner','target_date','completion_date','notes']:
            if k in d: sets.append(f'{k}=?'); params.append(d[k])
        if 'likelihood' in d and 'exposure' in d and 'consequence' in d:
            a, b, c = d['likelihood'], d['exposure'], d['consequence']
            s, cat_f, clr = fk_risk_score(a, b, c)
            sets += ['likelihood=?','exposure=?','consequence=?','risk_score=?','risk_category=?']
            params += [a, b, c, round(s,1), cat_f]
        if 'residual_likelihood' in d and 'residual_exposure' in d and 'residual_consequence' in d:
            rs, rc, rclr = fk_risk_score(d['residual_likelihood'], d['residual_exposure'], d['residual_consequence'])
            sets += ['residual_likelihood=?','residual_exposure=?','residual_consequence=?','residual_score=?','residual_category=?']
            params += [d['residual_likelihood'], d['residual_exposure'], d['residual_consequence'], round(rs,1), rc]
        if sets:
            sets.append("updated_date=CURRENT_TIMESTAMP")
            params.append(rid)
            conn.execute(f'UPDATE risk_items SET {", ".join(sets)} WHERE id=?', params)
            conn.commit()
        conn.close()
        return jsonify({'ok': True})
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@app.route('/api/risks/<int:rid>', methods=['DELETE'])
def api_risk_delete(rid):
    try:
        conn = sqlite3.connect(DB_PATH)
        conn.execute('DELETE FROM action_items WHERE risk_id=?', (rid,))
        conn.execute('DELETE FROM risk_items WHERE id=?', (rid,))
        conn.commit(); conn.close()
        return jsonify({'ok': True})
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@app.route('/api/actions')
def api_actions_list():
    try:
        conn = sqlite3.connect(DB_PATH); conn.row_factory = sqlite3.Row
        q = 'SELECT a.*, r.hazard as risk_hazard FROM action_items a LEFT JOIN risk_items r ON a.risk_id=r.id WHERE 1=1'
        params = []
        for f in ['phase','status','priority','category']:
            v = request.args.get(f)
            if v: q += f' AND a.{f}=?'; params.append(v)
        s = request.args.get('search')
        if s: q += ' AND a.title LIKE ?'; params.append(f'%{s}%')
        q += ' ORDER BY a.due_date ASC NULLS LAST, a.priority DESC'
        rows = [dict(r) for r in conn.execute(q, params).fetchall()]
        conn.close()
        return jsonify({'items': rows, 'total': len(rows)})
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@app.route('/api/actions/stats')
def api_actions_stats():
    try:
        conn = sqlite3.connect(DB_PATH); conn.row_factory = sqlite3.Row
        total = conn.execute('SELECT COUNT(*) as c FROM action_items').fetchone()['c']
        by_phase = {}
        for r in conn.execute("SELECT phase, COUNT(*) as c FROM action_items GROUP BY phase").fetchall():
            by_phase[r['phase'] or 'plan'] = r['c']
        by_status = {}
        for r in conn.execute("SELECT status, COUNT(*) as c FROM action_items GROUP BY status").fetchall():
            by_status[r['status'] or 'open'] = r['c']
        avg_prog = conn.execute('SELECT COALESCE(AVG(progress),0) as a FROM action_items').fetchone()['a']
        conn.close()
        return jsonify({'total': total, 'by_phase': by_phase, 'by_status': by_status, 'avg_progress': round(avg_prog,0)})
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@app.route('/api/actions', methods=['POST'])
def api_action_create():
    try:
        d = request.get_json(); conn = sqlite3.connect(DB_PATH)
        conn.execute('''INSERT INTO action_items (risk_id,title,description,phase,priority,status,category,owner,assignee,due_date,notes)
            VALUES (?,?,?,?,?,?,?,?,?,?,?)''',
            (d.get('risk_id'), d.get('title',''), d.get('description'), d.get('phase','plan'),
             d.get('priority','medium'), d.get('status','open'), d.get('category','Safety'),
             d.get('owner'), d.get('assignee'), d.get('due_date'), d.get('notes')))
        conn.commit(); aid = conn.execute('SELECT last_insert_rowid()').fetchone()[0]; conn.close()
        return jsonify({'id': aid})
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@app.route('/api/actions/<int:aid>', methods=['PUT'])
def api_action_update(aid):
    try:
        d = request.get_json(); conn = sqlite3.connect(DB_PATH)
        sets = []; params = []
        for k in ['title','description','phase','priority','status','category','owner','assignee',
                  'due_date','completion_date','progress','evidence','notes']:
            if k in d: sets.append(f'{k}=?'); params.append(d[k])
        if sets:
            sets.append("updated_date=CURRENT_TIMESTAMP")
            params.append(aid)
            conn.execute(f'UPDATE action_items SET {", ".join(sets)} WHERE id=?', params)
            conn.commit()
        conn.close()
        return jsonify({'ok': True})
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@app.route('/api/actions/<int:aid>', methods=['DELETE'])
def api_action_delete(aid):
    try:
        conn = sqlite3.connect(DB_PATH)
        conn.execute('DELETE FROM action_items WHERE id=?', (aid,))
        conn.commit(); conn.close()
        return jsonify({'ok': True})
    except Exception as e:
        return jsonify({'error': str(e)}), 500

# --- Page Routes ---
@app.route('/risks')
def risks_page():
    return page('''
<div class="stats-grid">
  <div class="stat-card"><div class="value" id="s-total">-</div><div class="label">Risico\'s</div></div>
  <div class="stat-card" style="border-left:4px solid #C92A2A"><div class="value" id="s-intol">-</div><div class="label">Intolerable / Substantial</div></div>
  <div class="stat-card" style="border-left:4px solid #F76707"><div class="value" id="s-actions">-</div><div class="label">Open Acties</div></div>
  <div class="stat-card" style="border-left:4px solid #4263EB"><div class="value" id="s-avg">-</div><div class="label">Gem. Score</div></div>
</div>
<!-- Fine & Kinney Risk Matrix -->
<div class="card">
  <h2>📊 Fine & Kinney Risico Matrix (A × B × C)</h2>
  <div style="display:grid;grid-template-columns:80px 1fr;gap:12px;margin-top:12px;font-size:11px">
    <div style="text-align:right;font-weight:600;color:#868E96">Likelihood (A)</div>
    <div style="display:flex;gap:4px;flex-wrap:wrap">
      <span style="background:#E9ECEF;padding:4px 8px;border-radius:4px">0.1 Practically impossible</span>
      <span style="background:#E9ECEF;padding:4px 8px;border-radius:4px">0.2 Remote</span>
      <span style="background:#E9ECEF;padding:4px 8px;border-radius:4px">0.5 Seldom</span>
      <span style="background:#E9ECEF;padding:4px 8px;border-radius:4px">1 Unlikely</span>
      <span style="background:#FFF3BF;padding:4px 8px;border-radius:4px">3 Possible</span>
      <span style="background:#FFE3E3;padding:4px 8px;border-radius:4px">6 Likely</span>
      <span style="background:#FFC9C9;padding:4px 8px;border-radius:4px">10 Certain</span>
    </div>
    <div style="text-align:right;font-weight:600;color:#868E96">Exposure (B)</div>
    <div style="display:flex;gap:4px;flex-wrap:wrap">
      <span style="background:#E9ECEF;padding:4px 8px;border-radius:4px">0.5 Very rare</span>
      <span style="background:#E9ECEF;padding:4px 8px;border-radius:4px">1 Rare</span>
      <span style="background:#FFF3BF;padding:4px 8px;border-radius:4px">2 Occasional</span>
      <span style="background:#FFF3BF;padding:4px 8px;border-radius:4px">3 Regular</span>
      <span style="background:#FFE3E3;padding:4px 8px;border-radius:4px">6 Continuous</span>
      <span style="background:#FFC9C9;padding:4px 8px;border-radius:4px">10 Permanent</span>
    </div>
    <div style="text-align:right;font-weight:600;color:#868E96">Consequence (C)</div>
    <div style="display:flex;gap:4px;flex-wrap:wrap">
      <span style="background:#D3F9D8;padding:4px 8px;border-radius:4px">1 Minor injury</span>
      <span style="background:#D3F9D8;padding:4px 8px;border-radius:4px">3 Medical treatment</span>
      <span style="background:#FFF3BF;padding:4px 8px;border-radius:4px">7 Lost workday</span>
      <span style="background:#FFE3E3;padding:4px 8px;border-radius:4px">15 Permanent disability</span>
      <span style="background:#FFC9C9;padding:4px 8px;border-radius:4px">40 Single fatality</span>
      <span style="background:#FFC9C9;padding:4px 8px;border-radius:4px">100 Multiple fatalities</span>
    </div>
  </div>
  <!-- Risk Score Categories -->
  <div style="display:flex;gap:8px;margin-top:16px;flex-wrap:wrap;justify-content:center">
    <div style="background:#2B8A3E;color:#fff;padding:6px 14px;border-radius:4px;font-size:11px;font-weight:600">Trivial (&lt;20)</div>
    <div style="background:#51CF66;color:#000;padding:6px 14px;border-radius:4px;font-size:11px;font-weight:600">Tolerable (20-70)</div>
    <div style="background:#F76707;color:#fff;padding:6px 14px;border-radius:4px;font-size:11px;font-weight:600">Moderate (70-200)</div>
    <div style="background:#E03131;color:#fff;padding:6px 14px;border-radius:4px;font-size:11px;font-weight:600">Substantial (200-500)</div>
    <div style="background:#C92A2A;color:#fff;padding:6px 14px;border-radius:4px;font-size:11px;font-weight:600">Intolerable (&gt;500)</div>
  </div>
  <div style="text-align:center;font-size:10px;color:#868E96;margin-top:8px">Formule: A × B × C = Risk Score</div>
</div>
<!-- Filters -->
<div class="card" style="margin-top:12px">
  <div style="display:flex;flex-wrap:wrap;gap:8px;align-items:center">
    <select id="f-cat" onchange="loadRisks()" style="padding:6px;border:1px solid #dee2e6;border-radius:4px;font-size:12px"><option value="">Alle categorieën</option><option>Process Safety</option><option>Chemical</option><option>Mechanical</option><option>Environmental</option><option>Training</option></select>
    <select id="f-level" onchange="loadRisks()" style="padding:6px;border:1px solid #dee2e6;border-radius:4px;font-size:12px"><option value="">Alle niveaus</option><option>Trivial</option><option>Tolerable</option><option>Moderate</option><option>Substantial</option><option>Intolerable</option></select>
    <select id="f-status" onchange="loadRisks()" style="padding:6px;border:1px solid #dee2e6;border-radius:4px;font-size:12px"><option value="">Alle status</option><option>open</option><option>in_progress</option><option>closed</option><option>monitoring</option></select>
    <input id="f-search" placeholder="Zoek risico..." oninput="loadRisks()" style="padding:6px;border:1px solid #dee2e6;border-radius:4px;font-size:12px;flex:1;min-width:150px">
    <button onclick="showRiskForm()" class="btn btn-primary" style="font-size:12px">+ Nieuw Risico</button>
  </div>
</div>
<!-- Risk Table -->
<div class="card" style="margin-top:12px;overflow-x:auto">
  <table id="risk-table" style="width:100%;font-size:12px;border-collapse:collapse">
    <thead><tr style="background:#f8f9fa;text-align:left">
      <th style="padding:8px">Gevaar</th><th>Categorie</th><th>A×B×C</th><th>Score</th><th>Risico Niveau</th><th>Priority</th><th>Owner</th><th>Residu</th><th></th>
    </tr></thead>
    <tbody id="risk-tbody"></tbody>
  </table>
</div>
<!-- Add Risk Modal -->
<div id="risk-modal" style="display:none;position:fixed;top:0;left:0;width:100%;height:100%;background:rgba(0,0,0,.5);z-index:1000;align-items:center;justify-content:center">
  <div style="background:#fff;border-radius:8px;padding:20px;max-width:550px;width:90%;max-height:85vh;overflow-y:auto">
    <h3>⚠️ Nieuw Risico (Fine & Kinney)</h3>
    <div style="display:grid;gap:8px;margin-top:12px">
      <input id="rm-hazard" placeholder="Gevaar/risico beschrijving" style="padding:8px;border:1px solid #dee2e6;border-radius:4px">
      <div style="display:grid;grid-template-columns:1fr 1fr;gap:8px">
        <select id="rm-cat" style="padding:8px;border:1px solid #dee2e6;border-radius:4px"><option>Process Safety</option><option>Chemical</option><option>Mechanical</option><option>Environmental</option><option>Training</option></select>
        <select id="rm-src" style="padding:8px;border:1px solid #dee2e6;border-radius:4px"><option>HAZOP</option><option>LOPA</option><option>RI&E</option><option>Inspectie</option><option>Incident</option><option>Audit</option></select>
      </div>
      <div style="background:#f8f9fa;padding:10px;border-radius:4px">
        <div style="font-weight:600;font-size:11px;margin-bottom:6px">Fine & Kinney Scores</div>
        <div style="display:grid;grid-template-columns:1fr 1fr 1fr;gap:8px">
          <div><label style="font-size:10px">Likelihood (A)</label>
            <select id="rm-a" style="padding:8px;border:1px solid #dee2e6;border-radius:4px;width:100%">
              <option value="0.1">A - 0.1 Practically impossible</option><option value="0.2">B - 0.2 Remote</option>
              <option value="0.5">C - 0.5 Seldom</option><option value="1">D - 1 Unlikely</option>
              <option value="3" selected>E - 3 Possible</option><option value="6">F - 6 Likely</option>
              <option value="10">G - 10 Completely certain</option></select></div>
          <div><label style="font-size:10px">Exposure (B)</label>
            <select id="rm-b" style="padding:8px;border:1px solid #dee2e6;border-radius:4px;width:100%">
              <option value="0.5">A - 0.5 Very rare</option><option value="1">B - 1 Rare</option>
              <option value="2" selected>C - 2 Occasional</option><option value="3">D - 3 Regular</option>
              <option value="6">E - 6 Continuous</option><option value="10">F - 10 Permanent</option></select></div>
          <div><label style="font-size:10px">Consequence (C)</label>
            <select id="rm-c" style="padding:8px;border:1px solid #dee2e6;border-radius:4px;width:100%">
              <option value="1">A - 1 Minor injury</option><option value="3">B - 3 Medical treatment</option>
              <option value="7" selected>C - 7 Lost workday</option><option value="15">D - 15 Permanent disability</option>
              <option value="40">E - 40 Single fatality</option><option value="100">F - 100 Multiple fatalities</option></select></div>
        </div>
        <div id="rm-preview" style="text-align:center;margin-top:6px;font-size:12px;font-weight:600"></div>
      </div>
      <textarea id="rm-ctrl" placeholder="Bestaande beheersmaatregelen" rows="2" style="padding:8px;border:1px solid #dee2e6;border-radius:4px"></textarea>
      <textarea id="rm-gap" placeholder="Wat ontbreekt / verbetering" rows="2" style="padding:8px;border:1px solid #dee2e6;border-radius:4px"></textarea>
      <textarea id="rm-act" placeholder="Aanbevolen actie" rows="2" style="padding:8px;border:1px solid #dee2e6;border-radius:4px"></textarea>
      <div style="display:grid;grid-template-columns:1fr 1fr;gap:8px">
        <select id="rm-pri" style="padding:8px;border:1px solid #dee2e6;border-radius:4px"><option>critical</option><option>high</option><option selected>medium</option><option>low</option></select>
        <input id="rm-owner" placeholder="Verantwoordelijke" style="padding:8px;border:1px solid #dee2e6;border-radius:4px">
      </div>
      <input id="rm-date" type="date" style="padding:8px;border:1px solid #dee2e6;border-radius:4px">
    </div>
    <div style="display:flex;gap:8px;margin-top:16px">
      <button onclick="saveRisk()" class="btn btn-primary">Opslaan</button>
      <button onclick="closeRiskForm()" class="btn btn-outline">Annuleer</button>
    </div>
  </div>
</div>
<script>
const bp = "{{BASE_PATH}}";
function fkColor(cat){return cat==="Intolerable"?"#C92A2A":cat==="Substantial"?"#E03131":cat==="Moderate"?"#F76707":cat==="Tolerable"?"#51CF66":"#2B8A3E";}
function fkCalc(a,b,c){let s=a*b*c;return s<20?["Trivial","#2B8A3E"]:s<70?["Tolerable","#51CF66"]:s<200?["Moderate","#F76707"]:s<500?["Substantial","#E03131"]:["Intolerable","#C92A2A"];}
function priColor(p){return p==="critical"?"#C92A2A":p==="high"?"#E03131":p==="medium"?"#F76707":"#868E96";}
function badge(t,c){return "<span style="background:"+c+";color:#fff;padding:2px 8px;border-radius:10px;font-size:11px">"+t+"</span>";}
function loadStats(){
  fetch(bp+"/api/risks/stats").then(r=>r.json()).then(d=>{
    document.getElementById("s-total").textContent=d.total;
    document.getElementById("s-intol").textContent=d.intolerable_substantial;
    document.getElementById("s-actions").textContent=d.open_actions;
    document.getElementById("s-avg").textContent=d.avg_score;
  });
}
function loadRisks(){
  let url=bp+"/api/risks?";
  let cat=document.getElementById("f-cat").value;
  let level=document.getElementById("f-level").value;
  let status=document.getElementById("f-status").value;
  let search=document.getElementById("f-search").value;
  if(cat)url+="category="+encodeURIComponent(cat)+"&";
  if(level)url+="risk_category="+encodeURIComponent(level)+"&";
  if(status)url+="status="+encodeURIComponent(status)+"&";
  if(search)url+="search="+encodeURIComponent(search)+"&";
  fetch(url).then(r=>r.json()).then(d=>{
    document.getElementById("risk-tbody").innerHTML=d.items.map(r=>{
      let resHtml=r.residual_score?"<span style=color:"+fkColor(r.residual_category)+";font-weight:600>"+r.residual_score+" "+r.residual_category+"</span>":"-";
      return "<tr style=border-bottom:1px solid #eee>"+
        "<td style=padding:8px><strong>"+r.hazard+"</strong><br><span style=font-size:10px;color:#868E96>"+(r.source||"")+"</span></td>"+
        "<td style=padding:8px>"+r.category+"</td>"+
        "<td style=padding:8px;font-weight:600>"+r.likelihood+" x "+r.exposure+" x "+r.consequence+"</td>"+
        "<td style=padding:8px;font-weight:700;font-size:14px;color:"+fkColor(r.risk_category)+">"+r.risk_score+"</td>"+
        "<td style=padding:8px>"+badge(r.risk_category,fkColor(r.risk_category))+"</td>"+
        "<td style=padding:8px>"+badge(r.priority,priColor(r.priority))+"</td>"+
        "<td style=padding:8px>"+(r.owner||"-")+"</td>"+
        "<td style=padding:8px;font-size:11px>"+resHtml+"</td>"+
        "<td style=padding:8px><button onclick=deleteRisk("+r.id+") style=background:none;border:none;color:#E03131;cursor:pointer>x</button></td>"+
      "</tr>";
    }).join("");
  });
}
function showRiskForm(){document.getElementById("risk-modal").style.display="flex";updatePreview();}
function closeRiskForm(){document.getElementById("risk-modal").style.display="none";}
function updatePreview(){
  let a=+document.getElementById("rm-a").value,b=+document.getElementById("rm-b").value,c=+document.getElementById("rm-c").value;
  let s=a*b*c;let[cat,col]=fkCalc(a,b,c);
  document.getElementById("rm-preview").innerHTML="A x B x C = "+a+" x "+b+" x "+c+" = <span style=color:"+col+">"+s+" ("+cat+")</span>";
}
document.getElementById("rm-a").addEventListener("change",updatePreview);
document.getElementById("rm-b").addEventListener("change",updatePreview);
document.getElementById("rm-c").addEventListener("change",updatePreview);
function saveRisk(){
  fetch(bp+"/api/risks",{method:"POST",headers:{"Content-Type":"application/json"},body:JSON.stringify({
    hazard:document.getElementById("rm-hazard").value,category:document.getElementById("rm-cat").value,
    source:document.getElementById("rm-src").value,likelihood:+document.getElementById("rm-a").value,
    exposure:+document.getElementById("rm-b").value,consequence:+document.getElementById("rm-c").value,
    existing_controls:document.getElementById("rm-ctrl").value,gap:document.getElementById("rm-gap").value,
    recommended_action:document.getElementById("rm-act").value,priority:document.getElementById("rm-pri").value,
    owner:document.getElementById("rm-owner").value,target_date:document.getElementById("rm-date").value
  })}).then(r=>r.json()).then(()=>{closeRiskForm();loadRisks();loadStats();});
}
function deleteRisk(id){if(confirm("Risico verwijderen?"))fetch(bp+"/api/risks/"+id,{method:"DELETE"}).then(()=>{loadRisks();loadStats();});}
loadStats();loadRisks();
</script>
''', page_title='Risico Register', active='risks')

@app.route('/actions')
def actions_page():
    return page('''
<div class="stats-grid">
  <div class="stat-card"><div class="value" id="as-total">-</div><div class="label">Acties</div></div>
  <div class="stat-card" style="border-left:4px solid #F76707"><div class="value" id="as-open">-</div><div class="label">Open</div></div>
  <div class="stat-card" style="border-left:4px solid #4263EB"><div class="value" id="as-prog">-</div><div class="label">In Progress</div></div>
  <div class="stat-card" style="border-left:4px solid #2B8A3E"><div class="value" id="as-done">-</div><div class="label">Completed</div></div>
</div>
<!-- PDCA Board -->
<div class="card">
  <h2>🔄 PDCA Board</h2>
  <div id="pdca-board" style="display:grid;grid-template-columns:repeat(4,1fr);gap:8px;margin-top:8px">
    <div id="pdca-plan" style="background:#f8f9fa;border-radius:6px;padding:8px;min-height:120px"><div style="font-weight:600;font-size:12px;color:#4263EB;margin-bottom:8px">📋 Plan</div></div>
    <div id="pdca-do" style="background:#f8f9fa;border-radius:6px;padding:8px;min-height:120px"><div style="font-weight:600;font-size:12px;color:#F76707;margin-bottom:8px">🔨 Do</div></div>
    <div id="pdca-check" style="background:#f8f9fa;border-radius:6px;padding:8px;min-height:120px"><div style="font-weight:600;font-size:12px;color:#2B8A3E;margin-bottom:8px">✅ Check</div></div>
    <div id="pdca-act" style="background:#f8f9fa;border-radius:6px;padding:8px;min-height:120px"><div style="font-weight:600;font-size:12px;color:#E03131;margin-bottom:8px">⚡ Act</div></div>
  </div>
</div>
<!-- Filters -->
<div class="card" style="margin-top:12px">
  <div style="display:flex;flex-wrap:wrap;gap:8px;align-items:center">
    <select id="af-phase" onchange="loadActions()" style="padding:6px;border:1px solid #dee2e6;border-radius:4px;font-size:12px"><option value="">Alle fases</option><option>plan</option><option>do</option><option>check</option><option>act</option></select>
    <select id="af-status" onchange="loadActions()" style="padding:6px;border:1px solid #dee2e6;border-radius:4px;font-size:12px"><option value="">Alle status</option><option>open</option><option>in_progress</option><option>completed</option><option>overdue</option></select>
    <select id="af-pri" onchange="loadActions()" style="padding:6px;border:1px solid #dee2e6;border-radius:4px;font-size:12px"><option value="">Alle prioriteit</option><option>critical</option><option>high</option><option>medium</option><option>low</option></select>
    <input id="af-search" placeholder="Zoek actie..." oninput="loadActions()" style="padding:6px;border:1px solid #dee2e6;border-radius:4px;font-size:12px;flex:1;min-width:150px">
    <button onclick="showActionForm()" class="btn btn-primary" style="font-size:12px">+ Nieuwe Actie</button>
  </div>
</div>
<!-- Actions Table -->
<div class="card" style="margin-top:12px;overflow-x:auto">
  <table style="width:100%;font-size:12px;border-collapse:collapse">
    <thead><tr style="background:#f8f9fa;text-align:left">
      <th style="padding:8px">Actie</th><th>Fase</th><th>Status</th><th>Priority</th><th>Owner</th><th>Deadline</th><th>Progress</th><th></th>
    </tr></thead>
    <tbody id="act-tbody"></tbody>
  </table>
</div>
<!-- Add Action Modal -->
<div id="act-modal" style="display:none;position:fixed;top:0;left:0;width:100%;height:100%;background:rgba(0,0,0,.5);z-index:1000;align-items:center;justify-content:center">
  <div style="background:#fff;border-radius:8px;padding:20px;max-width:500px;width:90%">
    <h3>Nieuwe Actie</h3>
    <div style="display:grid;gap:8px;margin-top:12px">
      <input id="am-title" placeholder="Actie beschrijving" style="padding:8px;border:1px solid #dee2e6;border-radius:4px">
      <textarea id="am-desc" placeholder="Uitgebreide beschrijving" rows="2" style="padding:8px;border:1px solid #dee2e6;border-radius:4px"></textarea>
      <div style="display:grid;grid-template-columns:1fr 1fr;gap:8px">
        <select id="am-phase" style="padding:8px;border:1px solid #dee2e6;border-radius:4px"><option>plan</option><option>do</option><option>check</option><option>act</option></select>
        <select id="am-pri" style="padding:8px;border:1px solid #dee2e6;border-radius:4px"><option>critical</option><option>high</option><option selected>medium</option><option>low</option></select>
      </div>
      <select id="am-cat" style="padding:8px;border:1px solid #dee2e6;border-radius:4px"><option>Safety</option><option>Environment</option><option>Quality</option><option>Process</option><option>Training</option><option>Maintenance</option></select>
      <input id="am-owner" placeholder="Verantwoordelijke" style="padding:8px;border:1px solid #dee2e6;border-radius:4px">
      <input id="am-date" type="date" style="padding:8px;border:1px solid #dee2e6;border-radius:4px">
    </div>
    <div style="display:flex;gap:8px;margin-top:16px">
      <button onclick="saveAction()" class="btn btn-primary">Opslaan</button>
      <button onclick="closeActionForm()" class="btn btn-outline">Annuleer</button>
    </div>
  </div>
</div>
<script>
const bp2='{{BASE_PATH}}';
function loadActionStats(){
  fetch(bp2+'/api/actions/stats').then(r=>r.json()).then(d=>{
    document.getElementById('as-total').textContent=d.total;
    document.getElementById('as-open').textContent=d.by_status.open||0;
    document.getElementById('as-prog').textContent=d.by_status.in_progress||0;
    document.getElementById('as-done').textContent=d.by_status.completed||0;
  });
}
function loadActions(){
  let url=bp2+'/api/actions?';
  ['phase','status','priority'].forEach(f=>{let v=document.getElementById('af-'+(f==='priority'?'pri':f)).value;if(v)url+=f+'='+v+'&';});
  let s=document.getElementById('af-search').value;if(s)url+='search='+encodeURIComponent(s)+'&';
  fetch(url).then(r=>r.json()).then(d=>{
    // PDCA board
    ['plan','do','check','act'].forEach(ph=>{
      let el=document.getElementById('pdca-'+ph);
      let items=d.items.filter(a=>a.phase===ph);
      el.innerHTML='<div style="font-weight:600;font-size:12px;margin-bottom:8px">'+
        (ph==='plan'?'📋 Plan':ph==='do'?'🔨 Do':ph==='check'?'✅ Check':'⚡ Act')+'</div>'+
        items.map(a=>'<div style="background:#fff;border-radius:4px;padding:6px;margin-bottom:4px;border-left:3px solid '+priColor2(a.priority)+';font-size:11px">'+
          '<strong>'+a.title+'</strong><br><span style="color:#868E96">'+(a.risk_hazard||'')+'</span></div>').join('');
    });
    // Table
    document.getElementById('act-tbody').innerHTML=d.items.map(a=>{
      let dl=a.due_date?new Date(a.due_date):null;
      let now=new Date();
      let dc='';
      if(dl&&dl<now&&a.status!=='completed')dc='color:#E03131;font-weight:600';
      else if(dl&&(dl-now)<7*86400000&&a.status!=='completed')dc='color:#F76707';
      return '<tr style="border-bottom:1px solid #eee">'+
        '<td style="padding:8px"><strong>'+a.title+'</strong>'+(a.risk_hazard?'<br><span style="font-size:10px;color:#868E96">'+a.risk_hazard+'</span>':'')+'</td>'+
        '<td style="padding:8px">'+badge2(a.phase,'#4263EB')+'</td>'+
        '<td style="padding:8px">'+badge2(a.status,a.status==='completed'?'#2B8A3E':a.status==='overdue'?'#E03131':'#F76707')+'</td>'+
        '<td style="padding:8px">'+badge2(a.priority,priColor2(a.priority))+'</td>'+
        '<td style="padding:8px">'+(a.owner||'-')+'</td>'+
        '<td style="padding:8px;font-size:11px;'+dc+'">'+(a.due_date||'-')+'</td>'+
        '<td style="padding:8px"><div style="background:#e9ecef;border-radius:4px;height:16px;width:80px;overflow:hidden"><div style="background:'+(a.progress>=100?'#2B8A3E':a.progress>50?'#4263EB':'#F76707')+';height:100%;width:'+a.progress+'%;border-radius:4px"></div></div><span style="font-size:10px">'+a.progress+'%</span></td>'+
        '<td style="padding:8px"><button onclick="deleteAction('+a.id+')" style="background:none;border:none;color:#E03131;cursor:pointer;font-size:14px">✕</button></td>'+
      '</tr>';
    }).join('');
  });
}
function badge2(t,c){return '<span style="background:'+c+';color:#fff;padding:2px 8px;border-radius:10px;font-size:11px">'+t+'</span>';}
function priColor2(p){return p==='critical'?'#C92A2A':p==='high'?'#E03131':p==='medium'?'#F76707':'#868E96';}
function showActionForm(){document.getElementById('act-modal').style.display='flex';}
function closeActionForm(){document.getElementById('act-modal').style.display='none';}
function saveAction(){
  fetch(bp2+'/api/actions',{method:'POST',headers:{'Content-Type':'application/json'},body:JSON.stringify({
    title:document.getElementById('am-title').value,description:document.getElementById('am-desc').value,
    phase:document.getElementById('am-phase').value,priority:document.getElementById('am-pri').value,
    category:document.getElementById('am-cat').value,owner:document.getElementById('am-owner').value,
    due_date:document.getElementById('am-date').value
  })}).then(r=>r.json()).then(()=>{closeActionForm();loadActions();loadActionStats();});
}
function deleteAction(id){if(confirm('Actie verwijderen?'))fetch(bp2+'/api/actions/'+id,{method:'DELETE'}).then(()=>{loadActions();loadActionStats();});}
loadActionStats();loadActions();
</script>
''', page_title='Actielijst', active='actions')

# ═══════════════════════════════════════════════════════════════════════════
# VBS & PROCEDURES MODULE (Fase 3)
# MoC, Werkvergunningen, Aannemersbeheer
# ═══════════════════════════════════════════════════════════════════════════

def init_db_vbs():
    try:
        conn = sqlite3.connect(DB_PATH)
        c = conn.cursor()
        c.execute('''CREATE TABLE IF NOT EXISTS moc_items (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            title TEXT NOT NULL,
            description TEXT,
            change_type TEXT DEFAULT 'process',
            reason TEXT,
            initiator TEXT,
            reviewer TEXT,
            approver TEXT,
            status TEXT DEFAULT 'draft',
            priority TEXT DEFAULT 'medium',
            risk_assessment TEXT,
            p_idra_completed INTEGER DEFAULT 0,
            affected_documents TEXT,
            training_required INTEGER DEFAULT 0,
            implementation_date TEXT,
            completion_date TEXT,
            notes TEXT,
            created_date TEXT DEFAULT CURRENT_TIMESTAMP,
            updated_date TEXT DEFAULT CURRENT_TIMESTAMP
        )''')
        c.execute('''CREATE TABLE IF NOT EXISTS ptw_items (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            title TEXT NOT NULL,
            description TEXT,
            permit_type TEXT DEFAULT 'hot_work',
            location TEXT,
            requested_by TEXT,
            supervisor TEXT,
            contractor TEXT,
            start_date TEXT,
            end_date TEXT,
            gas_test INTEGER DEFAULT 0,
            loto INTEGER DEFAULT 0,
            barricades INTEGER DEFAULT 0,
            ppe TEXT,
            hazards TEXT,
            status TEXT DEFAULT 'requested',
            closed_by TEXT,
            closed_date TEXT,
            notes TEXT,
            created_date TEXT DEFAULT CURRENT_TIMESTAMP,
            updated_date TEXT DEFAULT CURRENT_TIMESTAMP
        )''')
        c.execute('''CREATE TABLE IF NOT EXISTS contractor_items (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            company TEXT NOT NULL,
            contact_person TEXT,
            email TEXT,
            phone TEXT,
            trade TEXT,
            qualification TEXT,
            insurance_valid_until TEXT,
            vca_level TEXT,
            pre_qualified INTEGER DEFAULT 0,
            risk_assessment INTEGER DEFAULT 0,
            safety_plan INTEGER DEFAULT 0,
            status TEXT DEFAULT 'active',
            rating TEXT DEFAULT 'medium',
            notes TEXT,
            created_date TEXT DEFAULT CURRENT_TIMESTAMP,
            updated_date TEXT DEFAULT CURRENT_TIMESTAMP
        )''')
        conn.commit(); conn.close()
        seed_vbs_data()
    except Exception as e:
        print(f"[WARN] init_db_vbs: {e}")

def seed_vbs_data():
    try:
        conn = sqlite3.connect(DB_PATH)
        if conn.execute('SELECT COUNT(*) FROM moc_items').fetchone()[0] > 0:
            conn.close(); return
        mocs = [
            ('Wijziging opslagcapaciteit vanadium elektrolyt','Uitbreiding van opslagtank van 10m³ naar 25m³ voor verdere productieschaalvergroting.','process','Productieverhoging','HSEQ Manager','Plant Manager','Director','in_review','high','HAZOP benodigd voor nieuwe configuratie',1,'Veiligheidsrapport, Omgevingsvergunning',1,'2026-06-01',''),
            ('Installatie gasdetectiesysteem zone B','Uitbreiding gasdetectie netwerk met 12 extra sensoren in productiehal.','equipment','Verbetering veiligheid','HSEQ Manager','Instrumentation','Director','approved','high',None,0,'P&ID, ATEX zone indeling',0,'2026-05-15',''),
            ('Nieuw aannemersbeleid implementatie','Implementatie van pre-qualification proces voor alle externe aannemers volgens BRZO art. 8.','organizational','Compliance verbetering','HSEQ Manager','HR Manager','Director','draft','medium','Beïnvloedt alle aannemerscontracten',0,'Aannemersbeleid, Contractvoorwaarden',1,'2026-07-01',''),
            ('Aanpassing noodplan evacuatie','Actualisatie evacuatieroutes na bouw van nieuwe pilot plant.','organizational','Noodrespons update','HSEQ Manager','Safety Coordinator','Director','draft','medium','Evacuatieoefening nodig na implementatie',0,'Bedrijfsnoodplan, Evacuatieplan',0,'2026-05-01',''),
            ('Wijziging PGS-15 classificatie opslag','Herclassificatie van opgeslagen stoffen na laboratoriumanalyse nieuwe batch.','process','Regelgeving','HSEQ Manager','Quality Manager','Director','pending_info','high','PGS-15 bepalingen wijzigen mogelijk vergunningsvoorwaarden',0,'PGS-15, Veiligheidsrapport, Vergunning',1,'',''),
        ]
        for m in mocs:
            conn.execute('''INSERT INTO moc_items (title,description,change_type,reason,initiator,reviewer,approver,
                status,priority,risk_assessment,p_idra_completed,affected_documents,training_required,implementation_date,completion_date)
                VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)''', m)
        ptws = [
            ('Werkvergunning laswerkzaamheden tank T-201','Lassen en snijden aan opslagtank T-201 in ATEX zone 1','hot_work','Tankveld Zuid','J. de Vries','P. Jansen','Staalbouw BV','2026-04-10','2026-04-12',0,1,1,'Vlamwerend, gasmasker, handschoenen','ATEX zone, chemische stoffen, valgevaar','approved'),
            ('Werkvergunning confined space reactor R-1','Inspectie en onderhoud binnenzijde reactor R-1','confined_space','Productiehal 2','M. Bakker','K. Visser','Onderhoud Co','2026-04-08','2026-04-08',1,1,1,'SCBA, gasdetector, veiligheidsharnas','Zuurstoftekort, giftige dampen, beknelling','active'),
            ('Werkvergunning hijswerk pompsysteem','Vervanging pomp P-301 in perrongebied','lifting','Perrongebied','A. Mulder','R. Smit','Installatietechniek BV','2026-04-15','2026-04-16',0,1,0,'Helm, veiligheidsschoenen, handschoenen','Valgevaar, hijsgereedschap, personeel in de buurt','requested'),
            ('Werkvergunning elektrisch werk MCC-3','Onderhoud aan motor control center MCC-3','electrical','Utilities gebouw','L. van Dijk','E. Postma','Elektrotechniek BV','2026-04-12','2026-04-13',0,1,0,'Isolatiemiddelen, VDE-gereedschap, helm','Spanningsdragend, kortsluiting, boogvonk','draft'),
        ]
        for p in ptws:
            conn.execute('''INSERT INTO ptw_items (title,description,permit_type,location,requested_by,supervisor,
                contractor,start_date,end_date,gas_test,loto,barricades,ppe,hazards,status)
                VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)''', p)
        contractors = [
            ('Staalbouw BV','Jan Jansen','jan@staalbouw.nl','06-12345678','Lassen en constructie','ISO 3834, NEN-EN 1090','2027-01-01','VCA**',1,1,1,'active','high'),
            ('Onderhoud Co','Peter de Groot','info@onderhoudco.nl','06-23456789','Mechanisch onderhoud','ISO 9001, VCA*','2026-12-01','VCA*',1,1,0,'active','medium'),
            ('Installatietechniek BV','Mark Visser','mark@installbv.nl','06-34567890','Elektrotechniek, instrumentation','NEN 1010, NEN 3140, ATEX','2026-11-01','VCA**',1,0,0,'active','high'),
            ('Elektrotechniek BV','Linda van Dijk','linda@elektrobv.nl','06-45678901','Elektra, besturingstechniek','NEN 1010, NEN 3140','2026-10-15','VCA**',0,0,0,'pending','low'),
            ('ChemClean Services','Tom Bakker','tom@chemclean.nl','06-56789012','Industriële reiniging','Specialist chemische reiniging','2026-08-01','VCA*',0,0,0,'inactive','medium'),
        ]
        for co in contractors:
            conn.execute('''INSERT INTO contractor_items (company,contact_person,email,phone,trade,qualification,
                insurance_valid_until,vca_level,pre_qualified,risk_assessment,safety_plan,status,rating)
                VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)''', co)
        conn.commit(); conn.close()
        print("[OK] Seeded VBS: 5 MoC, 4 PTW, 5 contractors")
    except Exception as e:
        print(f"[WARN] seed_vbs_data: {e}")

# --- MoC API ---
@app.route('/api/moc')
def api_moc_list():
    try:
        conn = sqlite3.connect(DB_PATH); conn.row_factory = sqlite3.Row
        q = 'SELECT * FROM moc_items WHERE 1=1'; p = []
        for f in ['status','change_type','priority']:
            v = request.args.get(f)
            if v: q += f' AND {f}=?'; p.append(v)
        s = request.args.get('search')
        if s: q += ' AND title LIKE ?'; p.append(f'%{s}%')
        q += ' ORDER BY created_date DESC'
        rows = [dict(r) for r in conn.execute(q, p).fetchall()]
        conn.close()
        return jsonify({'items': rows, 'total': len(rows)})
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@app.route('/api/moc/stats')
def api_moc_stats():
    try:
        conn = sqlite3.connect(DB_PATH); conn.row_factory = sqlite3.Row
        total = conn.execute('SELECT COUNT(*) as c FROM moc_items').fetchone()['c']
        by_status = {}
        for r in conn.execute("SELECT status, COUNT(*) as c FROM moc_items GROUP BY status").fetchall():
            by_status[r['status'] or 'draft'] = r['c']
        open_count = by_status.get('draft',0)+by_status.get('in_review',0)+by_status.get('pending_info',0)
        conn.close()
        return jsonify({'total': total, 'by_status': by_status, 'open': open_count})
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@app.route('/api/moc', methods=['POST'])
def api_moc_create():
    try:
        d = request.get_json(); conn = sqlite3.connect(DB_PATH)
        conn.execute('''INSERT INTO moc_items (title,description,change_type,reason,initiator,reviewer,approver,
            status,priority,risk_assessment,affected_documents,training_required,implementation_date,notes)
            VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)''',
            (d.get('title',''), d.get('description'), d.get('change_type','process'),
             d.get('reason'), d.get('initiator'), d.get('reviewer'), d.get('approver'),
             d.get('status','draft'), d.get('priority','medium'), d.get('risk_assessment'),
             d.get('affected_documents'), d.get('training_required',0), d.get('implementation_date'), d.get('notes')))
        conn.commit(); conn.close()
        return jsonify({'ok': True})
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@app.route('/api/moc/<int:mid>', methods=['PUT'])
def api_moc_update(mid):
    try:
        d = request.get_json(); conn = sqlite3.connect(DB_PATH)
        sets = ['updated_date=CURRENT_TIMESTAMP']; params = []
        for k in ['title','description','change_type','reason','initiator','reviewer','approver',
                  'status','priority','risk_assessment','p_idra_completed','affected_documents',
                  'training_required','implementation_date','completion_date','notes']:
            if k in d: sets.append(f'{k}=?'); params.append(d[k])
        params.append(mid)
        conn.execute(f'UPDATE moc_items SET {", ".join(sets)} WHERE id=?', params)
        conn.commit(); conn.close()
        return jsonify({'ok': True})
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@app.route('/api/moc/<int:mid>', methods=['DELETE'])
def api_moc_delete(mid):
    try:
        conn = sqlite3.connect(DB_PATH)
        conn.execute('DELETE FROM moc_items WHERE id=?', (mid,))
        conn.commit(); conn.close()
        return jsonify({'ok': True})
    except Exception as e:
        return jsonify({'error': str(e)}), 500

# --- PTW API ---
@app.route('/api/ptw')
def api_ptw_list():
    try:
        conn = sqlite3.connect(DB_PATH); conn.row_factory = sqlite3.Row
        q = 'SELECT * FROM ptw_items WHERE 1=1'; p = []
        for f in ['status','permit_type']:
            v = request.args.get(f)
            if v: q += f' AND {f}=?'; p.append(v)
        s = request.args.get('search')
        if s: q += ' AND title LIKE ?'; p.append(f'%{s}%')
        q += ' ORDER BY start_date ASC'
        rows = [dict(r) for r in conn.execute(q, p).fetchall()]
        conn.close()
        return jsonify({'items': rows, 'total': len(rows)})
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@app.route('/api/ptw/stats')
def api_ptw_stats():
    try:
        conn = sqlite3.connect(DB_PATH); conn.row_factory = sqlite3.Row
        total = conn.execute('SELECT COUNT(*) as c FROM ptw_items').fetchone()['c']
        by_status = {}
        for r in conn.execute("SELECT status, COUNT(*) as c FROM ptw_items GROUP BY status").fetchall():
            by_status[r['status'] or 'requested'] = r['c']
        active = by_status.get('approved',0)+by_status.get('active',0)
        conn.close()
        return jsonify({'total': total, 'by_status': by_status, 'active': active})
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@app.route('/api/ptw', methods=['POST'])
def api_ptw_create():
    try:
        d = request.get_json(); conn = sqlite3.connect(DB_PATH)
        conn.execute('''INSERT INTO ptw_items (title,description,permit_type,location,requested_by,supervisor,
            contractor,start_date,end_date,ppe,hazards,status,notes)
            VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)''',
            (d.get('title',''), d.get('description'), d.get('permit_type','hot_work'),
             d.get('location'), d.get('requested_by'), d.get('supervisor'), d.get('contractor'),
             d.get('start_date'), d.get('end_date'), d.get('ppe'), d.get('hazards'),
             d.get('status','requested'), d.get('notes')))
        conn.commit(); conn.close()
        return jsonify({'ok': True})
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@app.route('/api/ptw/<int:pid>', methods=['PUT'])
def api_ptw_update(pid):
    try:
        d = request.get_json(); conn = sqlite3.connect(DB_PATH)
        sets = ['updated_date=CURRENT_TIMESTAMP']; params = []
        for k in ['title','description','permit_type','location','requested_by','supervisor','contractor',
                  'start_date','end_date','gas_test','loto','barricades','ppe','hazards',
                  'status','closed_by','closed_date','notes']:
            if k in d: sets.append(f'{k}=?'); params.append(d[k])
        params.append(pid)
        conn.execute(f'UPDATE ptw_items SET {", ".join(sets)} WHERE id=?', params)
        conn.commit(); conn.close()
        return jsonify({'ok': True})
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@app.route('/api/ptw/<int:pid>', methods=['DELETE'])
def api_ptw_delete(pid):
    try:
        conn = sqlite3.connect(DB_PATH)
        conn.execute('DELETE FROM ptw_items WHERE id=?', (pid,))
        conn.commit(); conn.close()
        return jsonify({'ok': True})
    except Exception as e:
        return jsonify({'error': str(e)}), 500

# --- Contractor API ---
@app.route('/api/contractors')
def api_contractor_list():
    try:
        conn = sqlite3.connect(DB_PATH); conn.row_factory = sqlite3.Row
        q = 'SELECT * FROM contractor_items WHERE 1=1'; p = []
        for f in ['status','vca_level','rating']:
            v = request.args.get(f)
            if v: q += f' AND {f}=?'; p.append(v)
        s = request.args.get('search')
        if s: q += ' AND company LIKE ?'; p.append(f'%{s}%')
        q += ' ORDER BY company ASC'
        rows = [dict(r) for r in conn.execute(q, p).fetchall()]
        conn.close()
        return jsonify({'items': rows, 'total': len(rows)})
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@app.route('/api/contractors', methods=['POST'])
def api_contractor_create():
    try:
        d = request.get_json(); conn = sqlite3.connect(DB_PATH)
        conn.execute('''INSERT INTO contractor_items (company,contact_person,email,phone,trade,qualification,
            insurance_valid_until,vca_level,pre_qualified,risk_assessment,safety_plan,status,rating,notes)
            VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)''',
            (d.get('company',''), d.get('contact_person'), d.get('email'), d.get('phone'),
             d.get('trade'), d.get('qualification'), d.get('insurance_valid_until'),
             d.get('vca_level'), d.get('pre_qualified',0), d.get('risk_assessment',0),
             d.get('safety_plan',0), d.get('status','active'), d.get('rating','medium'), d.get('notes')))
        conn.commit(); conn.close()
        return jsonify({'ok': True})
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@app.route('/api/contractors/<int:cid>', methods=['PUT'])
def api_contractor_update(cid):
    try:
        d = request.get_json(); conn = sqlite3.connect(DB_PATH)
        sets = ['updated_date=CURRENT_TIMESTAMP']; params = []
        for k in ['company','contact_person','email','phone','trade','qualification',
                  'insurance_valid_until','vca_level','pre_qualified','risk_assessment',
                  'safety_plan','status','rating','notes']:
            if k in d: sets.append(f'{k}=?'); params.append(d[k])
        params.append(cid)
        conn.execute(f'UPDATE contractor_items SET {", ".join(sets)} WHERE id=?', params)
        conn.commit(); conn.close()
        return jsonify({'ok': True})
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@app.route('/api/contractors/<int:cid>', methods=['DELETE'])
def api_contractor_delete(cid):
    try:
        conn = sqlite3.connect(DB_PATH)
        conn.execute('DELETE FROM contractor_items WHERE id=?', (cid,))
        conn.commit(); conn.close()
        return jsonify({'ok': True})
    except Exception as e:
        return jsonify({'error': str(e)}), 500

# --- MoC Page ---
@app.route('/moc')
def moc_page():
    return page('''
<div class="stats-grid">
  <div class="stat-card"><div class="value" id="ms-total">-</div><div class="label">MoC\'s</div></div>
  <div class="stat-card" style="border-left:4px solid #F76707"><div class="value" id="ms-open">-</div><div class="label">Open</div></div>
  <div class="stat-card" style="border-left:4px solid #4263EB"><div class="value" id="ms-review">-</div><div class="label">In Review</div></div>
  <div class="stat-card" style="border-left:4px solid #2B8A3E"><div class="value" id="ms-done">-</div><div class="label">Afgerond</div></div>
</div>
<!-- Workflow visualization -->
<div class="card">
  <h2>🔄 MoC Workflow</h2>
  <div style="display:flex;gap:4px;align-items:center;justify-content:center;flex-wrap:wrap;margin-top:8px;font-size:12px">
    <span style="background:#868E96;color:#fff;padding:6px 12px;border-radius:4px">Draft</span>
    <span style="font-size:16px">→</span>
    <span style="background:#4263EB;color:#fff;padding:6px 12px;border-radius:4px">In Review</span>
    <span style="font-size:16px">→</span>
    <span style="background:#F76707;color:#fff;padding:6px 12px;border-radius:4px">Pending Info</span>
    <span style="font-size:16px">→</span>
    <span style="background:#2B8A3E;color:#fff;padding:6px 12px;border-radius:4px">Approved</span>
    <span style="font-size:16px">→</span>
    <span style="background:#003366;color:#fff;padding:6px 12px;border-radius:4px">Implemented</span>
    <span style="font-size:16px">→</span>
    <span style="background:#868E96;color:#fff;padding:6px 12px;border-radius:4px">Closed</span>
  </div>
</div>
<!-- Filters -->
<div class="card" style="margin-top:12px">
  <div style="display:flex;flex-wrap:wrap;gap:8px;align-items:center">
    <select id="mf-status" onchange="loadMoC()" style="padding:6px;border:1px solid #dee2e6;border-radius:4px;font-size:12px"><option value="">Alle status</option><option>draft</option><option>in_review</option><option>pending_info</option><option>approved</option><option>implemented</option><option>closed</option><option>rejected</option></select>
    <select id="mf-type" onchange="loadMoC()" style="padding:6px;border:1px solid #dee2e6;border-radius:4px;font-size:12px"><option value="">Alle types</option><option>process</option><option>equipment</option><option>organizational</option><option>facility</option></select>
    <input id="mf-search" placeholder="Zoek MoC..." oninput="loadMoC()" style="padding:6px;border:1px solid #dee2e6;border-radius:4px;font-size:12px;flex:1;min-width:150px">
    <button onclick="showMoCForm()" class="btn btn-primary" style="font-size:12px">+ Nieuwe MoC</button>
  </div>
</div>
<!-- Table -->
<div class="card" style="margin-top:12px;overflow-x:auto">
  <table style="width:100%;font-size:12px;border-collapse:collapse">
    <thead><tr style="background:#f8f9fa;text-align:left"><th style="padding:8px">Wijziging</th><th>Type</th><th>Status</th><th>Priority</th><th>Initiator</th><th>IDRA</th><th>Implementatie</th><th></th></tr></thead>
    <tbody id="moc-tbody"></tbody>
  </table>
</div>
<!-- Modal -->
<div id="moc-modal" style="display:none;position:fixed;top:0;left:0;width:100%;height:100%;background:rgba(0,0,0,.5);z-index:1000;align-items:center;justify-content:center">
  <div style="background:#fff;border-radius:8px;padding:20px;max-width:550px;width:90%;max-height:80vh;overflow-y:auto">
    <h3>Nieuwe Management of Change</h3>
    <div style="display:grid;gap:8px;margin-top:12px">
      <input id="mm-title" placeholder="Titel van wijziging" style="padding:8px;border:1px solid #dee2e6;border-radius:4px">
      <textarea id="mm-desc" placeholder="Beschrijving" rows="2" style="padding:8px;border:1px solid #dee2e6;border-radius:4px"></textarea>
      <div style="display:grid;grid-template-columns:1fr 1fr;gap:8px">
        <select id="mm-type" style="padding:8px;border:1px solid #dee2e6;border-radius:4px"><option value="process">Proces</option><option value="equipment">Apparatuur</option><option value="organizational">Organisatie</option><option value="facility">Facilitair</option></select>
        <select id="mm-pri" style="padding:8px;border:1px solid #dee2e6;border-radius:4px"><option>critical</option><option>high</option><option selected>medium</option><option>low</option></select>
      </div>
      <input id="mm-reason" placeholder="Reden voor wijziging" style="padding:8px;border:1px solid #dee2e6;border-radius:4px">
      <div style="display:grid;grid-template-columns:1fr 1fr 1fr;gap:8px">
        <input id="mm-init" placeholder="Initiator" style="padding:8px;border:1px solid #dee2e6;border-radius:4px">
        <input id="mm-rev" placeholder="Reviewer" style="padding:8px;border:1px solid #dee2e6;border-radius:4px">
        <input id="mm-appr" placeholder="Approver" style="padding:8px;border:1px solid #dee2e6;border-radius:4px">
      </div>
      <input id="mm-date" type="date" style="padding:8px;border:1px solid #dee2e6;border-radius:4px">
      <label style="font-size:12px"><input type="checkbox" id="mm-training"> Training vereist</label>
    </div>
    <div style="display:flex;gap:8px;margin-top:16px">
      <button onclick="saveMoC()" class="btn btn-primary">Opslaan</button>
      <button onclick="document.getElementById('moc-modal').style.display='none'" class="btn btn-outline">Annuleer</button>
    </div>
  </div>
</div>
<script>
const bp3='{{BASE_PATH}}';
function mocSC(s){return s==='approved'||s==='implemented'||s==='closed'?'#2B8A3E':s==='rejected'?'#E03131':s==='in_review'?'#4263EB':s==='pending_info'?'#F76707':'#868E96';}
function mocBadge(t,c){return '<span style="background:'+c+';color:#fff;padding:2px 8px;border-radius:10px;font-size:11px">'+t+'</span>';}
function loadMoCStats(){
  fetch(bp3+'/api/moc/stats').then(r=>r.json()).then(d=>{
    document.getElementById('ms-total').textContent=d.total;
    document.getElementById('ms-open').textContent=d.by_status.draft||0;
    document.getElementById('ms-review').textContent=d.by_status.in_review||0;
    document.getElementById('ms-done').textContent=(d.by_status.approved||0)+(d.by_status.implemented||0)+(d.by_status.closed||0);
  });
}
function loadMoC(){
  let url=bp3+'/api/moc?';
  let st=document.getElementById('mf-status').value,tp=document.getElementById('mf-type').value,sr=document.getElementById('mf-search').value;
  if(st)url+='status='+st+'&';if(tp)url+='change_type='+tp+'&';if(sr)url+='search='+encodeURIComponent(sr)+'&';
  fetch(url).then(r=>r.json()).then(d=>{
    document.getElementById('moc-tbody').innerHTML=d.items.map(m=>'<tr style="border-bottom:1px solid #eee">'+
      '<td style="padding:8px"><strong>'+m.title+'</strong><br><span style="font-size:10px;color:#868E96">'+(m.reason||'')+'</span></td>'+
      '<td style="padding:8px">'+mocBadge(m.change_type,'#4263EB')+'</td>'+
      '<td style="padding:8px">'+mocBadge(m.status,mocSC(m.status))+'</td>'+
      '<td style="padding:8px">'+mocBadge(m.priority,mocSC(m.priority))+'</td>'+
      '<td style="padding:8px">'+(m.initiator||'-')+'</td>'+
      '<td style="padding:8px">'+(m.p_idra_completed?'✅':'❌')+'</td>'+
      '<td style="padding:8px;font-size:11px">'+(m.implementation_date||'-')+'</td>'+
      '<td style="padding:8px"><button onclick="delMoC('+m.id+')" style="background:none;border:none;color:#E03131;cursor:pointer">✕</button></td>'+
    '</tr>').join('');
  });
}
function showMoCForm(){document.getElementById('moc-modal').style.display='flex';}
function saveMoC(){
  fetch(bp3+'/api/moc',{method:'POST',headers:{'Content-Type':'application/json'},body:JSON.stringify({
    title:document.getElementById('mm-title').value,description:document.getElementById('mm-desc').value,
    change_type:document.getElementById('mm-type').value,priority:document.getElementById('mm-pri').value,
    reason:document.getElementById('mm-reason').value,initiator:document.getElementById('mm-init').value,
    reviewer:document.getElementById('mm-rev').value,approver:document.getElementById('mm-appr').value,
    implementation_date:document.getElementById('mm-date').value,training_required:document.getElementById('mm-training').checked?1:0
  })}).then(()=>{document.getElementById('moc-modal').style.display='none';loadMoC();loadMoCStats();});
}
function delMoC(id){if(confirm('MoC verwijderen?'))fetch(bp3+'/api/moc/'+id,{method:'DELETE'}).then(()=>{loadMoC();loadMoCStats();});}
loadMoCStats();loadMoC();
</script>
''', page_title='Management of Change', active='moc')

# --- PTW Page ---
@app.route('/ptw')
def ptw_page():
    return page('''
<div class="stats-grid">
  <div class="stat-card"><div class="value" id="ps-total">-</div><div class="label">Vergunningen</div></div>
  <div class="stat-card" style="border-left:4px solid #F76707"><div class="value" id="ps-active">-</div><div class="label">Actief</div></div>
  <div class="stat-card" style="border-left:4px solid #E03131"><div class="value" id="ps-req">-</div><div class="label">Aangevraagd</div></div>
  <div class="stat-card" style="border-left:4px solid #2B8A3E"><div class="value" id="ps-closed">-</div><div class="label">Gesloten</div></div>
</div>
<div class="card" style="margin-top:12px">
  <div style="display:flex;flex-wrap:wrap;gap:8px;align-items:center">
    <select id="pf-status" onchange="loadPTW()" style="padding:6px;border:1px solid #dee2e6;border-radius:4px;font-size:12px"><option value="">Alle status</option><option>requested</option><option>draft</option><option>approved</option><option>active</option><option>closed</option><option>rejected</option></select>
    <select id="pf-type" onchange="loadPTW()" style="padding:6px;border:1px solid #dee2e6;border-radius:4px;font-size:12px"><option value="">Alle types</option><option>hot_work</option><option>confined_space</option><option>lifting</option><option>electrical</option><option>excavation</option></select>
    <input id="pf-search" placeholder="Zoek vergunning..." oninput="loadPTW()" style="padding:6px;border:1px solid #dee2e6;border-radius:4px;font-size:12px;flex:1;min-width:150px">
    <button onclick="showPTWForm()" class="btn btn-primary" style="font-size:12px">+ Nieuwe Vergunning</button>
  </div>
</div>
<div class="card" style="margin-top:12px;overflow-x:auto">
  <table style="width:100%;font-size:12px;border-collapse:collapse">
    <thead><tr style="background:#f8f9fa;text-align:left"><th style="padding:8px">Vergunning</th><th>Type</th><th>Locatie</th><th>Status</th><th>Periode</th><th>Gas/Loto/Bar</th><th>Aannemer</th><th></th></tr></thead>
    <tbody id="ptw-tbody"></tbody>
  </table>
</div>
<div id="ptw-modal" style="display:none;position:fixed;top:0;left:0;width:100%;height:100%;background:rgba(0,0,0,.5);z-index:1000;align-items:center;justify-content:center">
  <div style="background:#fff;border-radius:8px;padding:20px;max-width:550px;width:90%;max-height:80vh;overflow-y:auto">
    <h3>Nieuwe Werkvergunning</h3>
    <div style="display:grid;gap:8px;margin-top:12px">
      <input id="pm-title" placeholder="Titel" style="padding:8px;border:1px solid #dee2e6;border-radius:4px">
      <textarea id="pm-desc" placeholder="Beschrijving werkzaamheden" rows="2" style="padding:8px;border:1px solid #dee2e6;border-radius:4px"></textarea>
      <div style="display:grid;grid-template-columns:1fr 1fr;gap:8px">
        <select id="pm-type" style="padding:8px;border:1px solid #dee2e6;border-radius:4px"><option value="hot_work">Lassen/Snijden (Hot Work)</option><option value="confined_space">Besloten Ruimte</option><option value="lifting">Hijswerk</option><option value="electrical">Elektrisch Werk</option><option value="excavation">Grondwerk</option></select>
        <input id="pm-loc" placeholder="Locatie" style="padding:8px;border:1px solid #dee2e6;border-radius:4px">
      </div>
      <div style="display:grid;grid-template-columns:1fr 1fr 1fr;gap:8px">
        <input id="pm-req" placeholder="Aangevraagd door" style="padding:8px;border:1px solid #dee2e6;border-radius:4px">
        <input id="pm-sup" placeholder="Supervisor" style="padding:8px;border:1px solid #dee2e6;border-radius:4px">
        <input id="pm-con" placeholder="Aannemer" style="padding:8px;border:1px solid #dee2e6;border-radius:4px">
      </div>
      <div style="display:grid;grid-template-columns:1fr 1fr;gap:8px">
        <input id="pm-start" type="date" style="padding:8px;border:1px solid #dee2e6;border-radius:4px">
        <input id="pm-end" type="date" style="padding:8px;border:1px solid #dee2e6;border-radius:4px">
      </div>
      <input id="pm-ppe" placeholder="PBM vereisten" style="padding:8px;border:1px solid #dee2e6;border-radius:4px">
      <input id="pm-haz" placeholder="Gevaren" style="padding:8px;border:1px solid #dee2e6;border-radius:4px">
    </div>
    <div style="display:flex;gap:8px;margin-top:16px">
      <button onclick="savePTW()" class="btn btn-primary">Opslaan</button>
      <button onclick="document.getElementById('ptw-modal').style.display='none'" class="btn btn-outline">Annuleer</button>
    </div>
  </div>
</div>
<script>
const bp4='{{BASE_PATH}}';
function ptwSC(s){return s==='approved'||s==='active'?'#2B8A3E':s==='closed'?'#868E96':s==='rejected'?'#E03131':'#F76707';}
function ptwBadge(t,c){return '<span style="background:'+c+';color:#fff;padding:2px 8px;border-radius:10px;font-size:11px">'+t+'</span>';}
function loadPTWStats(){
  fetch(bp4+'/api/ptw/stats').then(r=>r.json()).then(d=>{
    document.getElementById('ps-total').textContent=d.total;
    document.getElementById('ps-active').textContent=d.active;
    document.getElementById('ps-req').textContent=d.by_status.requested||0;
    document.getElementById('ps-closed').textContent=d.by_status.closed||0;
  });
}
function loadPTW(){
  let url=bp4+'/api/ptw?';
  let st=document.getElementById('pf-status').value,tp=document.getElementById('pf-type').value,sr=document.getElementById('pf-search').value;
  if(st)url+='status='+st+'&';if(tp)url+='permit_type='+tp+'&';if(sr)url+='search='+encodeURIComponent(sr)+'&';
  fetch(url).then(r=>r.json()).then(d=>{
    document.getElementById('ptw-tbody').innerHTML=d.items.map(p=>{
      let chk=(p.gas_test?'✅':'❌')+' / '+(p.loto?'✅':'❌')+' / '+(p.barricades?'✅':'❌');
      return '<tr style="border-bottom:1px solid #eee">'+
        '<td style="padding:8px"><strong>'+p.title+'</strong></td>'+
        '<td style="padding:8px">'+ptwBadge(p.permit_type,'#4263EB')+'</td>'+
        '<td style="padding:8px">'+(p.location||'-')+'</td>'+
        '<td style="padding:8px">'+ptwBadge(p.status,ptwSC(p.status))+'</td>'+
        '<td style="padding:8px;font-size:11px">'+(p.start_date||'')+' → '+(p.end_date||'')+'</td>'+
        '<td style="padding:8px;font-size:11px">'+chk+'</td>'+
        '<td style="padding:8px">'+(p.contractor||'-')+'</td>'+
        '<td style="padding:8px"><button onclick="delPTW('+p.id+')" style="background:none;border:none;color:#E03131;cursor:pointer">✕</button></td>'+
    '</tr>';
    }).join('');
  });
}
function showPTWForm(){document.getElementById('ptw-modal').style.display='flex';}
function savePTW(){
  fetch(bp4+'/api/ptw',{method:'POST',headers:{'Content-Type':'application/json'},body:JSON.stringify({
    title:document.getElementById('pm-title').value,description:document.getElementById('pm-desc').value,
    permit_type:document.getElementById('pm-type').value,location:document.getElementById('pm-loc').value,
    requested_by:document.getElementById('pm-req').value,supervisor:document.getElementById('pm-sup').value,
    contractor:document.getElementById('pm-con').value,start_date:document.getElementById('pm-start').value,
    end_date:document.getElementById('pm-end').value,ppe:document.getElementById('pm-ppe').value,
    hazards:document.getElementById('pm-haz').value
  })}).then(()=>{document.getElementById('ptw-modal').style.display='none';loadPTW();loadPTWStats();});
}
function delPTW(id){if(confirm('Vergunning verwijderen?'))fetch(bp4+'/api/ptw/'+id,{method:'DELETE'}).then(()=>{loadPTW();loadPTWStats();});}
loadPTWStats();loadPTW();
</script>
''', page_title='Werkvergunningen', active='ptw')

# --- Contractor Page ---
@app.route('/contractors')
def contractors_page():
    return page('''
<div class="stats-grid">
  <div class="stat-card"><div class="value" id="cs-total">-</div><div class="label">Aannemers</div></div>
  <div class="stat-card" style="border-left:4px solid #2B8A3E"><div class="value" id="cs-qual">-</div><div class="label">Pre-qualified</div></div>
  <div class="stat-card" style="border-left:4px solid #F76707"><div class="value" id="cs-pending">-</div><div class="label">Pending</div></div>
  <div class="stat-card" style="border-left:4px solid #E03131"><div class="value" id="cs-expired">-</div><div class="label">Verlopen</div></div>
</div>
<div class="card" style="margin-top:12px">
  <div style="display:flex;flex-wrap:wrap;gap:8px;align-items:center">
    <select id="cf-status" onchange="loadC()" style="padding:6px;border:1px solid #dee2e6;border-radius:4px;font-size:12px"><option value="">Alle status</option><option>active</option><option>pending</option><option>inactive</option></select>
    <select id="cf-vca" onchange="loadC()" style="padding:6px;border:1px solid #dee2e6;border-radius:4px;font-size:12px"><option value="">Alle VCA</option><option>VCA**</option><option>VCA*</option><option>Geen</option></select>
    <input id="cf-search" placeholder="Zoek aannemer..." oninput="loadC()" style="padding:6px;border:1px solid #dee2e6;border-radius:4px;font-size:12px;flex:1;min-width:150px">
    <button onclick="showCForm()" class="btn btn-primary" style="font-size:12px">+ Nieuwe Aannemer</button>
  </div>
</div>
<div class="card" style="margin-top:12px;overflow-x:auto">
  <table style="width:100%;font-size:12px;border-collapse:collapse">
    <thead><tr style="background:#f8f9fa;text-align:left"><th style="padding:8px">Bedrijf</th><th>Contact</th><th>VCA</th><th>Pre-Q</th><th>Verzekering</th><th>Status</th><th>Rating</th><th></th></tr></thead>
    <tbody id="c-tbody"></tbody>
  </table>
</div>
<div id="c-modal" style="display:none;position:fixed;top:0;left:0;width:100%;height:100%;background:rgba(0,0,0,.5);z-index:1000;align-items:center;justify-content:center">
  <div style="background:#fff;border-radius:8px;padding:20px;max-width:550px;width:90%;max-height:80vh;overflow-y:auto">
    <h3>Nieuwe Aannemer</h3>
    <div style="display:grid;gap:8px;margin-top:12px">
      <input id="cm-co" placeholder="Bedrijfsnaam" style="padding:8px;border:1px solid #dee2e6;border-radius:4px">
      <div style="display:grid;grid-template-columns:1fr 1fr;gap:8px">
        <input id="cm-cp" placeholder="Contactpersoon" style="padding:8px;border:1px solid #dee2e6;border-radius:4px">
        <input id="cm-email" placeholder="E-mail" style="padding:8px;border:1px solid #dee2e6;border-radius:4px">
      </div>
      <div style="display:grid;grid-template-columns:1fr 1fr;gap:8px">
        <input id="cm-phone" placeholder="Telefoon" style="padding:8px;border:1px solid #dee2e6;border-radius:4px">
        <input id="cm-trade" placeholder="Specialisatie" style="padding:8px;border:1px solid #dee2e6;border-radius:4px">
      </div>
      <input id="cm-qual" placeholder="Kwalificaties" style="padding:8px;border:1px solid #dee2e6;border-radius:4px">
      <div style="display:grid;grid-template-columns:1fr 1fr;gap:8px">
        <input id="cm-ins" type="date" title="Verzekering geldig t/m" style="padding:8px;border:1px solid #dee2e6;border-radius:4px">
        <select id="cm-vca" style="padding:8px;border:1px solid #dee2e6;border-radius:4px"><option>Geen</option><option>VCA*</option><option>VCA**</option></select>
      </div>
    </div>
    <div style="display:flex;gap:8px;margin-top:16px">
      <button onclick="saveC()" class="btn btn-primary">Opslaan</button>
      <button onclick="document.getElementById('c-modal').style.display='none'" class="btn btn-outline">Annuleer</button>
    </div>
  </div>
</div>
<script>
const bp5='{{BASE_PATH}}';
function loadCStats(){
  fetch(bp5+'/api/contractors').then(r=>r.json()).then(d=>{
    document.getElementById('cs-total').textContent=d.total;
    let qual=d.items.filter(i=>i.pre_qualified).length;
    let pend=d.items.filter(i=>i.status==='pending').length;
    let now=new Date().toISOString().split('T')[0];
    let exp=d.items.filter(i=>i.insurance_valid_until&&i.insurance_valid_until<now).length;
    document.getElementById('cs-qual').textContent=qual;
    document.getElementById('cs-pending').textContent=pend;
    document.getElementById('cs-expired').textContent=exp;
  });
}
function loadC(){
  let url=bp5+'/api/contractors?';
  let st=document.getElementById('cf-status').value,vc=document.getElementById('cf-vca').value,sr=document.getElementById('cf-search').value;
  if(st)url+='status='+st+'&';if(vc)url+='vca_level='+vc+'&';if(sr)url+='search='+encodeURIComponent(sr)+'&';
  fetch(url).then(r=>r.json()).then(d=>{
    let now=new Date().toISOString().split('T')[0];
    document.getElementById('c-tbody').innerHTML=d.items.map(c=>{
      let insC=c.insurance_valid_until&&c.insurance_valid_until<now?'color:#E03131;font-weight:600':'color:#2B8A3E';
      let chk=(c.pre_qualified?'✅':'❌')+' / '+(c.risk_assessment?'✅':'❌')+' / '+(c.safety_plan?'✅':'❌');
      return '<tr style="border-bottom:1px solid #eee">'+
        '<td style="padding:8px"><strong>'+c.company+'</strong><br><span style="font-size:10px;color:#868E96">'+(c.trade||'')+'</span></td>'+
        '<td style="padding:8px">'+c.contact_person+'<br><span style="font-size:10px;color:#868E96">'+(c.email||'')+'</span></td>'+
        '<td style="padding:8px">'+c.vca_level+'</td>'+
        '<td style="padding:8px">'+c.pre_qualified?'✅':'❌'+'</td>'+
        '<td style="padding:8px;font-size:11px;'+insC+'">'+(c.insurance_valid_until||'-')+'</td>'+
        '<td style="padding:8px">'+cBdg(c.status)+'</td>'+
        '<td style="padding:8px">'+cBdg(c.rating)+'</td>'+
        '<td style="padding:8px"><button onclick="delC('+c.id+')" style="background:none;border:none;color:#E03131;cursor:pointer">✕</button></td>'+
      '</tr>';
    }).join('');
  });
}
function cBdg(t){let c=t==='active'?'#2B8A3E':t==='pending'?'#F76707':t==='high'?'#E03131':t==='medium'?'#F76707':'#868E96';return '<span style="background:'+c+';color:#fff;padding:2px 8px;border-radius:10px;font-size:11px">'+t+'</span>';}
function showCForm(){document.getElementById('c-modal').style.display='flex';}
function saveC(){
  fetch(bp5+'/api/contractors',{method:'POST',headers:{'Content-Type':'application/json'},body:JSON.stringify({
    company:document.getElementById('cm-co').value,contact_person:document.getElementById('cm-cp').value,
    email:document.getElementById('cm-email').value,phone:document.getElementById('cm-phone').value,
    trade:document.getElementById('cm-trade').value,qualification:document.getElementById('cm-qual').value,
    insurance_valid_until:document.getElementById('cm-ins').value,vca_level:document.getElementById('cm-vca').value
  })}).then(()=>{document.getElementById('c-modal').style.display='none';loadC();loadCStats();});
}
function delC(id){if(confirm('Aannemer verwijderen?'))fetch(bp5+'/api/contractors/'+id,{method:'DELETE'}).then(()=>{loadC();loadCStats();});}
loadCStats();loadC();
</script>
''', page_title='Aannemersbeheer', active='contractors')

# ═══════════════════════════════════════════════════════════════════════════
# INCIDENT & ENVIRONMENT MODULE (Fase 4)
# ═══════════════════════════════════════════════════════════════════════════

def init_db_incidents():
    try:
        conn = sqlite3.connect(DB_PATH)
        c = conn.cursor()
        c.execute('''CREATE TABLE IF NOT EXISTS incident_items (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            title TEXT NOT NULL,
            description TEXT,
            incident_type TEXT DEFAULT 'near_miss',
            severity TEXT DEFAULT 'medium',
            category TEXT DEFAULT 'Safety',
            location TEXT,
            reported_by TEXT,
            date_occurred TEXT,
            date_reported TEXT DEFAULT CURRENT_TIMESTAMP,
            root_cause TEXT,
            immediate_actions TEXT,
            corrective_actions TEXT,
            status TEXT DEFAULT 'open',
            investigation_required INTEGER DEFAULT 0,
            mir_required INTEGER DEFAULT 0,
            lessons_learned TEXT,
            evidence_files TEXT,
            related_risk_id INTEGER,
            notes TEXT,
            created_date TEXT DEFAULT CURRENT_TIMESTAMP,
            updated_date TEXT DEFAULT CURRENT_TIMESTAMP
        )''')
        c.execute('''CREATE TABLE IF NOT EXISTS environment_kpis (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            kpi_name TEXT NOT NULL,
            category TEXT DEFAULT 'Emissies',
            unit TEXT,
            target_value REAL,
            current_value REAL,
            trend TEXT DEFAULT 'stable',
            measurement_date TEXT,
            measurement_frequency TEXT DEFAULT 'monthly',
            permit_limit REAL,
            status TEXT DEFAULT 'on_track',
            notes TEXT,
            created_date TEXT DEFAULT CURRENT_TIMESTAMP,
            updated_date TEXT DEFAULT CURRENT_TIMESTAMP
        )''')
        conn.commit(); conn.close()
        seed_incident_data()
    except Exception as e:
        print(f"[WARN] init_db_incidents: {e}")

def seed_incident_data():
    try:
        conn = sqlite3.connect(DB_PATH)
        if conn.execute('SELECT COUNT(*) FROM incident_items').fetchone()[0] > 0:
            conn.close(); return
        incidents = [
            ('Kleine lekkage flensverbinding leiding L-201','Tijdens routine inspectie een kleine lekkage geconstateerd aan flensverbinding. Ongeveer 0.5 liter vanadium elektrolyt gelekt. Direct afgeschermd en opgeruimd.','spill','medium','Safety','Leidingveld noord','J. de Vries','2026-03-25','Verouderde pakking','Afgeschermd, opgeruimd, nieuwe pakking besteld','Vervanging pakking volgens onderhoudsschema','closed',0,0,'Controleer alle flensverbindingen in zone A voorafgaand aan onderhoud',''),
            ('Near-miss: medewerker zonder PBM in ATEX zone','Medewerker betrad ATEX zone 1 zonder voorgeschreven PBM (vlamwerend, gasdetectie). Gemeld door supervisor.','near_miss','high','Safety','Productiehal 2','P. Jansen','2026-03-28','Onvoldoende bewustzijning procedures','Direct gecorrigeerd, medewerker aangesproken','Extra toolbox talk PBM-protocol gepland','in_progress',0,0,'Herhalingscontrole PBM naleving 1 maand',''),
            ('Near-miss: hijskraan boven beloop gebied','Hijskraan opererde boven beloopgebied waar medewerkers liepen. Barricades waren niet correct geplaatst.','near_miss','high','Safety','Perrongebied','M. Bakker','2026-03-20','Onvoldoende barricade plaatsing','Operatie gestopt, gebied afgezet','Barricade procedure herzien en training gepland','in_progress',0,0,'Overweeg vaste fysieke barrières',''),
            ('Oversschrijding emissiewaarde NOx','Routinemeeting toonde NOx-waarde van 85 mg/Nm³ tegen een limiet van 80 mg/Nm³. Tijdelijke overschrijding tijdens opstart.','environmental','medium','Environment','Stack C','A. Mulder','2026-03-15','Verhoogde belasting tijdens opstartfase','Procesparameters aangepast','Optimaliseer opstartprocedure emissies','closed',0,0,'Documenteer als afwijking in emissieregistratie',''),
            ('Defect aan Noodstop knop reactor R-1','Tijdige inspectie bracht defect aan noodstop knop aan de reactor aan. Knop reageerde niet bij functionele test.','equipment_failure','high','Safety','Productiehal 2','K. Visser','2026-03-10','Mechanische slijtage/vochtinfiltratie','Vervanging noodstop knop uitgevoerd','Uitbreiding inspectieprogramma noodstoppen alle eenheden','closed',0,0,'Inclusief alle E-stop kettingen in inspectieschema',''),
        ]
        for inc in incidents:
            conn.execute('''INSERT INTO incident_items (title,description,incident_type,severity,category,
                location,reported_by,date_occurred,root_cause,immediate_actions,corrective_actions,
                status,investigation_required,mir_required,lessons_learned,evidence_files)
                VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)''', inc)
        kpis = [
            ('NOx emissies stack C','Emissies','mg/Nm³',80.0,75.2,'improving','2026-03-31','monthly',80.0,'on_track'),
            ('SO2 emissies stack A','Emissies','mg/Nm³',50.0,42.1,'stable','2026-03-31','monthly',50.0,'on_track'),
            ('Afvalwater pH waarde','Lozing','pH',6.5,7.1,'stable','2026-03-31','weekly',6.0,'on_track'),
            ('Spill incidenten (cumulatief)','Spills','aantal/jaar',4.0,1.0,'improving','2026-03-31','monthly',None,'on_track'),
            ('Vaste afval productie','Afval','ton/maand',5.0,4.2,'stable','2026-03-31','monthly',None,'on_track'),
            ('Energieverbruik per ton product','Energie','kWh/ton',2500.0,2680.0,'worsening','2026-03-31','monthly',None,'attention'),
            ('CO2 emissies scope 1','Emissies','ton CO2/jaar',500.0,320.0,'stable','2026-03-31','yearly',None,'on_track'),
            ('Waterverbruik','Water','m³/maand',200.0,185.0,'improving','2026-03-31','monthly',None,'on_track'),
        ]
        for k in kpis:
            conn.execute('''INSERT INTO environment_kpis (kpi_name,category,unit,target_value,current_value,
                trend,measurement_date,measurement_frequency,permit_limit,status)
                VALUES (?,?,?,?,?,?,?,?,?,?)''', k)
        conn.commit(); conn.close()
        print("[OK] Seeded incidents: 5 + env KPIs: 8")
    except Exception as e:
        print(f"[WARN] seed_incident_data: {e}")

# --- Incident API ---
@app.route('/api/incidents')
def api_incidents_list():
    try:
        conn = sqlite3.connect(DB_PATH); conn.row_factory = sqlite3.Row
        q = 'SELECT * FROM incident_items WHERE 1=1'; p = []
        for f in ['status','incident_type','severity','category']:
            v = request.args.get(f)
            if v: q += f' AND {f}=?'; p.append(v)
        s = request.args.get('search')
        if s: q += ' AND title LIKE ?'; p.append(f'%{s}%')
        q += ' ORDER BY date_occurred DESC'
        rows = [dict(r) for r in conn.execute(q, p).fetchall()]
        conn.close()
        return jsonify({'items': rows, 'total': len(rows)})
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@app.route('/api/incidents/stats')
def api_incidents_stats():
    try:
        conn = sqlite3.connect(DB_PATH); conn.row_factory = sqlite3.Row
        total = conn.execute('SELECT COUNT(*) as c FROM incident_items').fetchone()['c']
        by_type = {}
        for r in conn.execute("SELECT incident_type, COUNT(*) as c FROM incident_items GROUP BY incident_type").fetchall():
            by_type[r['incident_type'] or 'other'] = r['c']
        by_status = {}
        for r in conn.execute("SELECT status, COUNT(*) as c FROM incident_items GROUP BY status").fetchall():
            by_status[r['status'] or 'open'] = r['c']
        open_count = by_status.get('open',0)+by_status.get('in_progress',0)
        conn.close()
        return jsonify({'total': total, 'by_type': by_type, 'by_status': by_status, 'open': open_count})
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@app.route('/api/incidents', methods=['POST'])
def api_incident_create():
    try:
        d = request.get_json(); conn = sqlite3.connect(DB_PATH)
        conn.execute('''INSERT INTO incident_items (title,description,incident_type,severity,category,
            location,reported_by,date_occurred,root_cause,immediate_actions,corrective_actions,
            status,investigation_required,mir_required,lessons_learned,notes)
            VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)''',
            (d.get('title',''),d.get('description'),d.get('incident_type','near_miss'),
             d.get('severity','medium'),d.get('category','Safety'),d.get('location'),
             d.get('reported_by'),d.get('date_occurred'),d.get('root_cause'),
             d.get('immediate_actions'),d.get('corrective_actions'),d.get('status','open'),
             d.get('investigation_required',0),d.get('mir_required',0),d.get('lessons_learned'),d.get('notes')))
        conn.commit(); conn.close()
        return jsonify({'ok': True})
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@app.route('/api/incidents/<int:iid>', methods=['PUT'])
def api_incident_update(iid):
    try:
        d = request.get_json(); conn = sqlite3.connect(DB_PATH)
        sets = ['updated_date=CURRENT_TIMESTAMP']; params = []
        for k in ['title','description','incident_type','severity','category','location','reported_by',
                  'date_occurred','root_cause','immediate_actions','corrective_actions','status',
                  'investigation_required','mir_required','lessons_learned','notes']:
            if k in d: sets.append(f'{k}=?'); params.append(d[k])
        params.append(iid)
        conn.execute(f'UPDATE incident_items SET {", ".join(sets)} WHERE id=?', params)
        conn.commit(); conn.close()
        return jsonify({'ok': True})
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@app.route('/api/incidents/<int:iid>', methods=['DELETE'])
def api_incident_delete(iid):
    try:
        conn = sqlite3.connect(DB_PATH)
        conn.execute('DELETE FROM incident_items WHERE id=?', (iid,))
        conn.commit(); conn.close()
        return jsonify({'ok': True})
    except Exception as e:
        return jsonify({'error': str(e)}), 500

# --- Environment KPI API ---
@app.route('/api/environment')
def api_env_list():
    try:
        conn = sqlite3.connect(DB_PATH); conn.row_factory = sqlite3.Row
        q = 'SELECT * FROM environment_kpis WHERE 1=1'; p = []
        cat = request.args.get('category')
        if cat: q += ' AND category=?'; p.append(cat)
        q += ' ORDER BY category, kpi_name'
        rows = [dict(r) for r in conn.execute(q, p).fetchall()]
        conn.close()
        return jsonify({'items': rows, 'total': len(rows)})
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@app.route('/api/environment/stats')
def api_env_stats():
    try:
        conn = sqlite3.connect(DB_PATH); conn.row_factory = sqlite3.Row
        total = conn.execute('SELECT COUNT(*) as c FROM environment_kpis').fetchone()['c']
        on_track = conn.execute("SELECT COUNT(*) as c FROM environment_kpis WHERE status='on_track'").fetchone()['c']
        attention = conn.execute("SELECT COUNT(*) as c FROM environment_kpis WHERE status='attention'").fetchone()['c']
        conn.close()
        return jsonify({'total': total, 'on_track': on_track, 'attention': attention})
    except Exception as e:
        return jsonify({'error': str(e)}), 500

# --- Incident Page ---
@app.route('/incidents')
def incidents_page():
    return page('''
<div class="stats-grid">
  <div class="stat-card"><div class="value" id="is-total">-</div><div class="label">Incidenten</div></div>
  <div class="stat-card" style="border-left:4px solid #E03131"><div class="value" id="is-open">-</div><div class="label">Open</div></div>
  <div class="stat-card" style="border-left:4px solid #F76707"><div class="value" id="is-nm">-</div><div class="label">Near Misses</div></div>
  <div class="stat-card" style="border-left:4px solid #2B8A3E"><div class="value" id="is-closed">-</div><div class="label">Gesloten</div></div>
</div>
<div class="card" style="margin-top:12px">
  <div style="display:flex;flex-wrap:wrap;gap:8px;align-items:center">
    <select id="isf-type" onchange="loadI()" style="padding:6px;border:1px solid #dee2e6;border-radius:4px;font-size:12px"><option value="">Alle types</option><option>near_miss</option><option>spill</option><option>injury</option><option>fire</option><option>environmental</option><option>equipment_failure</option></select>
    <select id="isf-sev" onchange="loadI()" style="padding:6px;border:1px solid #dee2e6;border-radius:4px;font-size:12px"><option value="">Alle ernst</option><option>low</option><option>medium</option><option>high</option><option>critical</option></select>
    <select id="isf-status" onchange="loadI()" style="padding:6px;border:1px solid #dee2e6;border-radius:4px;font-size:12px"><option value="">Alle status</option><option>open</option><option>in_progress</option><option>closed</option></select>
    <input id="isf-search" placeholder="Zoek incident..." oninput="loadI()" style="padding:6px;border:1px solid #dee2e6;border-radius:4px;font-size:12px;flex:1;min-width:150px">
    <button onclick="showIForm()" class="btn btn-danger" style="font-size:12px">🚨 Meld Incident</button>
  </div>
</div>
<div class="card" style="margin-top:12px;overflow-x:auto">
  <table style="width:100%;font-size:12px;border-collapse:collapse">
    <thead><tr style="background:#f8f9fa;text-align:left"><th style="padding:8px">Incident</th><th>Type</th><th>Ernst</th><th>Locatie</th><th>Datum</th><th>Gemeld door</th><th>Status</th><th></th></tr></thead>
    <tbody id="is-tbody"></tbody>
  </table>
</div>
<div id="is-modal" style="display:none;position:fixed;top:0;left:0;width:100%;height:100%;background:rgba(0,0,0,.5);z-index:1000;align-items:center;justify-content:center">
  <div style="background:#fff;border-radius:8px;padding:20px;max-width:600px;width:90%;max-height:85vh;overflow-y:auto">
    <h3>🚨 Nieuw Incident Melden</h3>
    <div style="display:grid;gap:8px;margin-top:12px">
      <input id="im-title" placeholder="Korte beschrijving" style="padding:8px;border:1px solid #dee2e6;border-radius:4px">
      <textarea id="im-desc" placeholder="Wat is er gebeurd?" rows="3" style="padding:8px;border:1px solid #dee2e6;border-radius:4px"></textarea>
      <div style="display:grid;grid-template-columns:1fr 1fr 1fr;gap:8px">
        <select id="im-type" style="padding:8px;border:1px solid #dee2e6;border-radius:4px"><option value="near_miss">Near Miss</option><option value="spill">Spill / Lekkage</option><option value="injury">Blessure</option><option value="fire">Brand</option><option value="environmental">Milieu</option><option value="equipment_failure">Apparatuur</option></select>
        <select id="im-sev" style="padding:8px;border:1px solid #dee2e6;border-radius:4px"><option>low</option><option selected>medium</option><option>high</option><option>critical</option></select>
        <input id="im-loc" placeholder="Locatie" style="padding:8px;border:1px solid #dee2e6;border-radius:4px">
      </div>
      <div style="display:grid;grid-template-columns:1fr 1fr;gap:8px">
        <input id="im-reporter" placeholder="Gemeld door" style="padding:8px;border:1px solid #dee2e6;border-radius:4px">
        <input id="im-date" type="date" style="padding:8px;border:1px solid #dee2e6;border-radius:4px">
      </div>
      <textarea id="im-imm" placeholder="Directe acties ondernomen" rows="2" style="padding:8px;border:1px solid #dee2e6;border-radius:4px"></textarea>
      <label style="font-size:12px"><input type="checkbox" id="im-inv"> Onderzoek vereist</label>
      <label style="font-size:12px"><input type="checkbox" id="im-mir"> MIR-melding vereist</label>
    </div>
    <div style="display:flex;gap:8px;margin-top:16px">
      <button onclick="saveI()" class="btn btn-danger">Melden</button>
      <button onclick="document.getElementById('is-modal').style.display='none'" class="btn btn-outline">Annuleer</button>
    </div>
  </div>
</div>
<script>
const bp6='{{BASE_PATH}}';
function iSev(s){return s==='critical'?'#C92A2A':s==='high'?'#E03131':s==='medium'?'#F76707':'#2B8A3E';}
function iBdg(t,c){return '<span style="background:'+c+';color:#fff;padding:2px 8px;border-radius:10px;font-size:11px">'+t+'</span>';}
function loadIStats(){
  fetch(bp6+'/api/incidents/stats').then(r=>r.json()).then(d=>{
    document.getElementById('is-total').textContent=d.total;
    document.getElementById('is-open').textContent=d.open;
    document.getElementById('is-nm').textContent=d.by_type.near_miss||0;
    document.getElementById('is-closed').textContent=d.by_status.closed||0;
  });
}
function loadI(){
  let url=bp6+'/api/incidents?';
  ['type','severity','status'].forEach(f=>{let v=document.getElementById('isf-'+f).value;if(v)url+=f+'='+v+'&';});
  let s=document.getElementById('isf-search').value;if(s)url+='search='+encodeURIComponent(s)+'&';
  fetch(url).then(r=>r.json()).then(d=>{
    document.getElementById('is-tbody').innerHTML=d.items.map(i=>'<tr style="border-bottom:1px solid #eee">'+
      '<td style="padding:8px"><strong>'+i.title+'</strong>'+(i.mir_required?' <span style="background:#E03131;color:#fff;padding:1px 6px;border-radius:8px;font-size:9px">MIR</span>':'')+'</td>'+
      '<td style="padding:8px">'+iBdg(i.incident_type,'#4263EB')+'</td>'+
      '<td style="padding:8px">'+iBdg(i.severity,iSev(i.severity))+'</td>'+
      '<td style="padding:8px">'+(i.location||'-')+'</td>'+
      '<td style="padding:8px;font-size:11px">'+(i.date_occurred||i.date_reported||'')+'</td>'+
      '<td style="padding:8px">'+(i.reported_by||'-')+'</td>'+
      '<td style="padding:8px">'+iBdg(i.status,i.status==='closed'?'#2B8A3E':i.status==='in_progress'?'#4263EB':'#F76707')+'</td>'+
      '<td style="padding:8px"><button onclick="delI('+i.id+')" style="background:none;border:none;color:#E03131;cursor:pointer">✕</button></td>'+
    '</tr>').join('');
  });
}
function showIForm(){document.getElementById('is-modal').style.display='flex';}
function saveI(){
  fetch(bp6+'/api/incidents',{method:'POST',headers:{'Content-Type':'application/json'},body:JSON.stringify({
    title:document.getElementById('im-title').value,description:document.getElementById('im-desc').value,
    incident_type:document.getElementById('im-type').value,severity:document.getElementById('im-sev').value,
    location:document.getElementById('im-loc').value,reported_by:document.getElementById('im-reporter').value,
    date_occurred:document.getElementById('im-date').value,immediate_actions:document.getElementById('im-imm').value,
    investigation_required:document.getElementById('im-inv').checked?1:0,mir_required:document.getElementById('im-mir').checked?1:0
  })}).then(()=>{document.getElementById('is-modal').style.display='none';loadI();loadIStats();});
}
function delI(id){if(confirm('Incident verwijderen?'))fetch(bp6+'/api/incidents/'+id,{method:'DELETE'}).then(()=>{loadI();loadIStats();});}
loadIStats();loadI();
</script>
''', page_title='Incidenten', active='incidents')

# --- Environment Page ---
@app.route('/environment')
def environment_page():
    return page('''
<div class="stats-grid">
  <div class="stat-card"><div class="value" id="es-total">-</div><div class="label">KPI\'s</div></div>
  <div class="stat-card" style="border-left:4px solid #2B8A3E"><div class="value" id="es-ok">-</div><div class="label">On Track</div></div>
  <div class="stat-card" style="border-left:4px solid #F76707"><div class="value" id="es-att">-</div><div class="label">Attention</div></div>
  <div class="stat-card" style="border-left:4px solid #4263EB"><div class="value" id="es-emit">-</div><div class="label">Emissie KPI\'s</div></div>
</div>
<div class="card" style="margin-top:12px">
  <div style="display:flex;flex-wrap:wrap;gap:8px;align-items:center">
    <select id="ef-cat" onchange="loadE()" style="padding:6px;border:1px solid #dee2e6;border-radius:4px;font-size:12px"><option value="">Alle categorieën</option><option>Emissies</option><option>Lozing</option><option>Spills</option><option>Afval</option><option>Energie</option><option>Water</option></select>
    <input id="ef-search" placeholder="Zoek KPI..." oninput="loadE()" style="padding:6px;border:1px solid #dee2e6;border-radius:4px;font-size:12px;flex:1;min-width:150px">
  </div>
</div>
<div class="card" style="margin-top:12px;overflow-x:auto">
  <table style="width:100%;font-size:12px;border-collapse:collapse">
    <thead><tr style="background:#f8f9fa;text-align:left"><th style="padding:8px">KPI</th><th>Categorie</th><th>Huidig</th><th>Target</th><th>Limiet</th><th>Trend</th><th>Status</th></tr></thead>
    <tbody id="es-tbody"></tbody>
  </table>
</div>
<script>
const bp7='{{BASE_PATH}}';
function trendIcon(t){return t==='improving'?'📈':t==='worsening'?'📉':'➡️';}
function loadEStats(){
  fetch(bp7+'/api/environment/stats').then(r=>r.json()).then(d=>{
    document.getElementById('es-total').textContent=d.total;
    document.getElementById('es-ok').textContent=d.on_track;
    document.getElementById('es-att').textContent=d.attention;
    let emit=d.total; // approx
    fetch(bp7+'/api/environment?category=Emissies').then(r=>r.json()).then(d=>{document.getElementById('es-emit').textContent=d.total;});
  });
}
function loadE(){
  let url=bp7+'/api/environment?';
  let cat=document.getElementById('ef-cat').value,sr=document.getElementById('ef-search').value;
  if(cat)url+='category='+cat+'&';if(sr)url+='search='+encodeURIComponent(sr)+'&';
  fetch(url).then(r=>r.json()).then(d=>{
    document.getElementById('es-tbody').innerHTML=d.items.map(k=>{
      let pct=k.target_value?Math.round((k.current_value/k.target_value)*100):0;
      let pc=pct>100?'#E03131':pct>85?'#F76707':'#2B8A3E';
      return '<tr style="border-bottom:1px solid #eee">'+
        '<td style="padding:8px"><strong>'+k.kpi_name+'</strong><br><span style="font-size:10px;color:#868E96">'+k.measurement_frequency+' | '+k.measurement_date+'</span></td>'+
        '<td style="padding:8px">'+k.category+'</td>'+
        '<td style="padding:8px;font-weight:600;color:'+pc+'">'+k.current_value+' '+k.unit+'</td>'+
        '<td style="padding:8px">'+k.target_value+' '+k.unit+'</td>'+
        '<td style="padding:8px">'+(k.permit_limit?k.permit_limit+' '+k.unit:'-')+'</td>'+
        '<td style="padding:8px">'+trendIcon(k.trend)+' '+k.trend+'</td>'+
        '<td style="padding:8px">'+('<span style="background:'+(k.status==='on_track'?'#2B8A3E':'#F76707')+';color:#fff;padding:2px 8px;border-radius:10px;font-size:11px">'+k.status+'</span>')+'</td>'+
    '</tr>';
    }).join('');
  });
}
loadEStats();loadE();
</script>
''', page_title='Milieu KPI\'s', active='environment')

# ═══════════════════════════════════════════════════════════════════════════
# AUDIT & INSPECTION READINESS MODULE (Fase 5)
# ═══════════════════════════════════════════════════════════════════════════

def init_db_audits():
    try:
        conn = sqlite3.connect(DB_PATH)
        c = conn.cursor()
        c.execute('''CREATE TABLE IF NOT EXISTS audit_items (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            title TEXT NOT NULL,
            audit_type TEXT DEFAULT 'internal',
            scope TEXT,
            auditor TEXT,
            auditee TEXT,
            scheduled_date TEXT,
            completed_date TEXT,
            status TEXT DEFAULT 'planned',
            findings_open INTEGER DEFAULT 0,
            findings_closed INTEGER DEFAULT 0,
            findings_critical INTEGER DEFAULT 0,
            overall_score TEXT,
            recommendation TEXT,
            notes TEXT,
            created_date TEXT DEFAULT CURRENT_TIMESTAMP,
            updated_date TEXT DEFAULT CURRENT_TIMESTAMP
        )''')
        c.execute('''CREATE TABLE IF NOT EXISTS vka_items (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            equipment_name TEXT NOT NULL,
            equipment_type TEXT DEFAULT 'pressure_vessel',
            location TEXT,
            tag_number TEXT,
            last_inspection TEXT,
            next_inspection_due TEXT,
            inspection_interval TEXT DEFAULT '12 months',
            certifying_authority TEXT,
            status TEXT DEFAULT 'valid',
            max_pressure REAL,
            max_temperature REAL,
            design_code TEXT,
            notes TEXT,
            created_date TEXT DEFAULT CURRENT_TIMESTAMP,
            updated_date TEXT DEFAULT CURRENT_TIMESTAMP
        )''')
        conn.commit(); conn.close()
        seed_audit_data()
    except Exception as e:
        print(f"[WARN] init_db_audits: {e}")

def seed_audit_data():
    try:
        conn = sqlite3.connect(DB_PATH)
        if conn.execute('SELECT COUNT(*) FROM audit_items').fetchone()[0] > 0:
            conn.close(); return
        audits = [
            ('BRZO Veiligheidsbeheerssysteem audit','Intern','VBS: MoC, PTW, noodplannen, training','HSEQ Manager','Plant Manager','2026-06-15','planned',0,0,0,'',''),
            ('ISO 45001 interne audit','Intern','Arbobeleid, RI&E, PBM, ongevallenregistratie','HSEQ Manager','HR Manager','2026-05-20','planned',0,0,0,'',''),
            ('Pre-inspectie BRZO (ILT)','Extern','Volledige BRZO-readiness check','HSEQ Manager','Director','2026-07-01','planned',0,0,0,'',''),
            ('Aannemersveiligheid audit','Intern','Pre-qualification, VCA, werkvergunning naleving','HSEQ Manager','Procurement','2026-05-01','planned',0,0,0,'',''),
            ('Milieupermissie compliance audit','Intern','Emissies, lozingen, afval, rapportages','HSEQ Manager','Operations','2026-06-01','planned',0,0,0,'',''),
        ]
        for a in audits:
            conn.execute('''INSERT INTO audit_items (title,audit_type,scope,auditor,auditee,scheduled_date,
                status,findings_open,findings_closed,findings_critical,recommendation,notes)
                VALUES (?,?,?,?,?,?,?,?,?,?,?,?)''', a)
        vkas = [
            ('Opslagslak vanadium elektrolyt T-201','pressure_vessel','Tankveld Zuid','T-201','2026-01-15','2027-01-15','12 months','Lloyd\'s Register','valid',8.0,150.0,'PED 2014/68/EU'),
            ('Reactor R-1','pressure_vessel','Productiehal 2','R-001','2025-11-20','2026-11-20','12 months','TÜV Nederland','valid',10.0,200.0,'PED 2014/68/EU'),
            ('Warmtewisselaar W-301','pressure_vessel','Utilities','W-301','2026-02-10','2027-02-10','12 months','Lloyd\'s Register','valid',6.0,180.0,'PED 2014/68/EU'),
            ('Pomp P-301 (circulatie)','rotating_equipment','Perrongebied','P-301','2025-12-01','2026-06-01','6 months','Internal','due_soon',15.0,120.0,'API 610'),
            ('Veiligheidsklep SV-101','safety_valve','Productiehal 2','SV-101','2026-03-01','2027-03-01','12 months','Emerson','valid',12.0,250.0,'API 526'),
            ('Gasdetectiesysteem zone A','safety_system','Productiehal 2','GD-A','2026-01-20','2026-07-20','6 months','Dräger','valid',None,None,'EN 60079-29-1'),
            ('Noodstop systeem reactor R-1','safety_system','Productiehal 2','ES-001','2026-03-10','2026-09-10','6 months','Internal','valid',None,None,'IEC 61511'),
            ('ATEX verlichting productiehal','electrical','Productiehal 2','EX-L1','2025-09-15','2026-09-15','12 months','ATEX Cert','due_soon',None,None,'IECEx/ATEX'),
        ]
        for v in vkas:
            conn.execute('''INSERT INTO vka_items (equipment_name,equipment_type,location,tag_number,
                last_inspection,next_inspection_due,inspection_interval,certifying_authority,
                status,max_pressure,max_temperature,design_code)
                VALUES (?,?,?,?,?,?,?,?,?,?,?,?)''', v)
        conn.commit(); conn.close()
        print("[OK] Seeded audits: 5 + VKA: 8")
    except Exception as e:
        print(f"[WARN] seed_audit_data: {e}")

# --- Audit API ---
@app.route('/api/audits')
def api_audits_list():
    try:
        conn = sqlite3.connect(DB_PATH); conn.row_factory = sqlite3.Row
        q = 'SELECT * FROM audit_items WHERE 1=1'; p = []
        for f in ['status','audit_type']:
            v = request.args.get(f)
            if v: q += f' AND {f}=?'; p.append(v)
        q += ' ORDER BY scheduled_date ASC'
        rows = [dict(r) for r in conn.execute(q, p).fetchall()]
        conn.close()
        return jsonify({'items': rows, 'total': len(rows)})
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@app.route('/api/audits/stats')
def api_audits_stats():
    try:
        conn = sqlite3.connect(DB_PATH); conn.row_factory = sqlite3.Row
        total = conn.execute('SELECT COUNT(*) as c FROM audit_items').fetchone()['c']
        planned = conn.execute("SELECT COUNT(*) as c FROM audit_items WHERE status='planned'").fetchone()['c']
        completed = conn.execute("SELECT COUNT(*) as c FROM audit_items WHERE status='completed'").fetchone()['c']
        conn.close()
        return jsonify({'total': total, 'planned': planned, 'completed': completed})
    except Exception as e:
        return jsonify({'error': str(e)}), 500

# --- VKA API ---
@app.route('/api/vka')
def api_vka_list():
    try:
        conn = sqlite3.connect(DB_PATH); conn.row_factory = sqlite3.Row
        q = 'SELECT * FROM vka_items WHERE 1=1'; p = []
        for f in ['status','equipment_type']:
            v = request.args.get(f)
            if v: q += f' AND {f}=?'; p.append(v)
        q += ' ORDER BY next_inspection_due ASC'
        rows = [dict(r) for r in conn.execute(q, p).fetchall()]
        conn.close()
        return jsonify({'items': rows, 'total': len(rows)})
    except Exception as e:
        return jsonify({'error': str(e)}), 500

@app.route('/api/vka/stats')
def api_vka_stats():
    try:
        conn = sqlite3.connect(DB_PATH); conn.row_factory = sqlite3.Row
        total = conn.execute('SELECT COUNT(*) as c FROM vka_items').fetchone()['c']
        valid = conn.execute("SELECT COUNT(*) as c FROM vka_items WHERE status='valid'").fetchone()['c']
        due_soon = conn.execute("SELECT COUNT(*) as c FROM vka_items WHERE status='due_soon'").fetchone()['c']
        overdue = conn.execute("SELECT COUNT(*) as c FROM vka_items WHERE status='overdue'").fetchone()['c']
        conn.close()
        return jsonify({'total': total, 'valid': valid, 'due_soon': due_soon, 'overdue': overdue})
    except Exception as e:
        return jsonify({'error': str(e)}), 500

# --- Audit Page ---
@app.route('/audits')
def audits_page():
    return page('''
<div class="stats-grid">
  <div class="stat-card"><div class="value" id="aus-total">-</div><div class="label">Audits</div></div>
  <div class="stat-card" style="border-left:4px solid #4263EB"><div class="value" id="aus-plan">-</div><div class="label">Gepland</div></div>
  <div class="stat-card" style="border-left:4px solid #2B8A3E"><div class="value" id="aus-done">-</div><div class="label">Afgerond</div></div>
  <div class="stat-card" style="border-left:4px solid #F76707"><div class="value" id="aus-vka">-</div><div class="label">VKA Due Soon</div></div>
</div>
<div class="card" style="margin-top:12px">
  <div style="display:flex;flex-wrap:wrap;gap:8px;align-items:center">
    <select id="auf-type" onchange="loadA()" style="padding:6px;border:1px solid #dee2e6;border-radius:4px;font-size:12px"><option value="">Alle types</option><option>internal</option><option>external</option><option>certification</option></select>
    <select id="auf-status" onchange="loadA()" style="padding:6px;border:1px solid #dee2e6;border-radius:4px;font-size:12px"><option value="">Alle status</option><option>planned</option><option>in_progress</option><option>completed</option></select>
  </div>
</div>
<div class="card" style="margin-top:12px;overflow-x:auto">
  <h2>📋 Audit Planning</h2>
  <table style="width:100%;font-size:12px;border-collapse:collapse;margin-top:8px">
    <thead><tr style="background:#f8f9fa;text-align:left"><th style="padding:8px">Audit</th><th>Type</th><th>Auditor</th><th>Gepland</th><th>Status</th><th>Findings</th></tr></thead>
    <tbody id="aus-tbody"></tbody>
  </table>
</div>
<div class="card" style="margin-top:12px;overflow-x:auto">
  <h2>🔧 VKA - Veiligheidskritische Apparatuur</h2>
  <div style="display:flex;flex-wrap:wrap;gap:8px;margin-top:8px">
    <select id="auf-vka-status" onchange="loadVKA()" style="padding:6px;border:1px solid #dee2e6;border-radius:4px;font-size:12px"><option value="">Alle status</option><option>valid</option><option>due_soon</option><option>overdue</option></select>
  </div>
  <table style="width:100%;font-size:12px;border-collapse:collapse;margin-top:8px">
    <thead><tr style="background:#f8f9fa;text-align:left"><th style="padding:8px">Apparatuur</th><th>Type</th><th>Tag</th><th>Locatie</th><th>Volgende Keuring</th><th>Interval</th><th>Instantie</th><th>Status</th></tr></thead>
    <tbody id="vka-tbody"></tbody>
  </table>
</div>
<script>
const bp8='{{BASE_PATH}}';
function loadAStats(){
  fetch(bp8+'/api/audits/stats').then(r=>r.json()).then(d=>{
    document.getElementById('aus-total').textContent=d.total;
    document.getElementById('aus-plan').textContent=d.planned;
    document.getElementById('aus-done').textContent=d.completed;
  });
  fetch(bp8+'/api/vka/stats').then(r=>r.json()).then(d=>{
    document.getElementById('aus-vka').textContent=d.due_soon+d.overdue;
  });
}
function loadA(){
  let url=bp8+'/api/audits?';
  let tp=document.getElementById('auf-type').value,st=document.getElementById('auf-status').value;
  if(tp)url+='audit_type='+tp+'&';if(st)url+='status='+st+'&';
  fetch(url).then(r=>r.json()).then(d=>{
    document.getElementById('aus-tbody').innerHTML=d.items.map(a=>'<tr style="border-bottom:1px solid #eee">'+
      '<td style="padding:8px"><strong>'+a.title+'</strong><br><span style="font-size:10px;color:#868E96">'+(a.scope||'')+'</span></td>'+
      '<td style="padding:8px">'+aBdg(a.audit_type)+'</td>'+
      '<td style="padding:8px">'+(a.auditor||'-')+'</td>'+
      '<td style="padding:8px;font-size:11px">'+(a.scheduled_date||'-')+'</td>'+
      '<td style="padding:8px">'+aBdg(a.status)+'</td>'+
      '<td style="padding:8px">'+a.findings_open+'/'+a.findings_closed+(a.findings_critical?' <span style="color:#E03131">'+a.findings_critical+' crit</span>':'')+'</td>'+
    '</tr>').join('');
  });
}
function aBdg(t){let c=t==='internal'?'#4263EB':t==='external'?'#E03131':t==='completed'?'#2B8A3E':t==='planned'?'#F76707':'#868E96';return '<span style="background:'+c+';color:#fff;padding:2px 8px;border-radius:10px;font-size:11px">'+t+'</span>';}
function loadVKA(){
  let url=bp8+'/api/vka?';
  let st=document.getElementById('auf-vka-status').value;
  if(st)url+='status='+st+'&';
  fetch(url).then(r=>r.json()).then(d=>{
    document.getElementById('vka-tbody').innerHTML=d.items.map(v=>{
      let sc=v.status==='valid'?'#2B8A3E':v.status==='due_soon'?'#F76707':'#E03131';
      return '<tr style="border-bottom:1px solid #eee">'+
        '<td style="padding:8px"><strong>'+v.equipment_name+'</strong><br><span style="font-size:10px;color:#868E96">'+(v.design_code||'')+'</span></td>'+
        '<td style="padding:8px">'+v.equipment_type+'</td>'+
        '<td style="padding:8px;font-weight:600">'+(v.tag_number||'-')+'</td>'+
        '<td style="padding:8px">'+(v.location||'-')+'</td>'+
        '<td style="padding:8px;font-size:11px;color:'+sc+';font-weight:600">'+(v.next_inspection_due||'-')+'</td>'+
        '<td style="padding:8px">'+(v.inspection_interval||'-')+'</td>'+
        '<td style="padding:8px">'+(v.certifying_authority||'-')+'</td>'+
        '<td style="padding:8px"><span style="background:'+sc+';color:#fff;padding:2px 8px;border-radius:10px;font-size:11px">'+v.status+'</span></td>'+
    '</tr>';
    }).join('');
  });
}
loadAStats();loadA();loadVKA();
</script>
''', page_title='Audit & Inspectie', active='audits')

# ─── Main ───────────────────────────────────────────────────────────────────
if __name__ == '__main__':
    port = 5052
    for arg in sys.argv[1:]:
        if arg.startswith('--port'):
            val = arg.split('=', 1)[1] if '=' in arg else '5052'
            port = int(val)
            break
    init_db()
    print(f"HSEQ Intelligence Dashboard starting on http://0.0.0.0:{port}")
    app.run(host='0.0.0.0', port=port, debug=False)