# LMS Academy — Technische Documentatie v1.0

**Versie**: 1.0  
**Datum**: 15 april 2026  
**Project**: HSEQ Intelligence Dashboard — LMS Module  
**Auteur**: Kas (Technical Writer, HSEQ Support Division)  
**Status**: Production Ready

---

## 1. Architectuuroverzicht

### 1.1 Systeemintegratie

Het LMS Academy is ontworpen als een **Flask module** binnen de bestaande HSEQ Intelligence Monitor applicatie. De module integreert naadloos met de bestaande infrastructuur zonder breaking changes.

**Applicatie Stack:**
- **Framework**: Flask (Python 3)
- **Database**: SQLite3 (`hseq_kennisbank.db`)
- **Process Manager**: PM2 (proces: `hseq-kennisbank`)
- **Poort**: 5052
- **Base Path**: `/hseq-dashboard`
- **LMS Routes Prefix**: `/lms/`

### 1.2 Module Registratie

De LMS module wordt geregistreerd via `app.register_blueprint(lms_bp)` of direct import:

```python
# In app.py:
from module_lms import lms_bp
app.register_blueprint(lms_bp)
```

**Navigatie Integratie:**
Nieuw tab in de sidebar: `🎓 LMS Academy` → linkt naar `/lms/`

### 1.3 Architectuur Diagram

```
┌─────────────────────────────────────────────────────────────┐
│                     Flask App (app.py)                     │
│  ┌──────────────────────────────────────────────────────┐ │
│  │              LMS Blueprint (module_lms.py)            │ │
│  │  ┌──────────┐  ┌──────────┐  ┌──────────┐  ┌────────┐ │ │
│  │  │ User     │  │ Course   │  │ Quiz     │  │ Admin  │ │ │
│  │  │ Portal   │  │ Player   │  │ Engine   │  │ Portal │ │ │
│  │  └──────────┘  └──────────┘  └──────────┘  └────────┘ │ │
│  └──────────────────────────────────────────────────────┘ │
└─────────────────────────────────────────────────────────────┘
                            │
                            ▼
┌─────────────────────────────────────────────────────────────┐
│              SQLite3 Database (hseq_kennisbank.db)          │
│  ┌──────────────┐  ┌──────────────┐  ┌──────────────┐     │
│  │ Bestaande     │  │ Nieuwe       │  │ Bestaande     │     │
│  │ Tabellen      │  │ Tabellen     │  │ Tabellen      │     │
│  └──────────────┘  └──────────────┘  └──────────────┘     │
└─────────────────────────────────────────────────────────────┘
```

### 1.4 Frontend Architektur

**Styling Strategy:**
- Alle CSS inline via `_base_css()` functie
- Geen externe CSS bestanden (bestaand patroon behouden)
- Responsive design (mobile-first, breakpoints: 768px / 1024px)
- CSS variabelen voor consistent kleurgebruik

**Content Delivery:**
- HTML-bestanden geladen via iframe in Course Player
- PostMessage protocol voor quiz integratie
- Inline content ondersteuning via `content_html` veld

---

## 2. Database Schema

### 2.1 Bestaande Tabellen (Onveranderd)

| Tabel | Beschrijving |
|-------|-------------|
| `employees` | Medewerkers (id, employee_number, first_name, last_name, email, role_id, status) |
| `roles` | Functies (id, role_name, department, safety_critical) |
| `elearning_modules` | Cursusdefinities (id, title, category, content_html, quiz_questions, passing_score, duration_minutes, status) |
| `elearning_assignments` | Legacy toewijzingen |
| `elearning_quiz_attempts` | Legacy quiz pogingen |
| `training_programs` | Trainingsprogramma's (id, program_code, program_name, valid_period_days) |
| `certifications` | Certificeringen (id, employee_id, training_program_id, expiry_date, status, score) |
| `training_matrix` | Koppeling rol ↔ training |
| `trn_certificates` | E-learning certificaten (id, employee_id, module_id, quiz_score, passed, certificate_ref) |

### 2.2 Nieuwe Tabellen

#### 2.2.1 lms_course_content

```sql
CREATE TABLE lms_course_content (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    elearning_module_id INTEGER NOT NULL,
    content_type TEXT NOT NULL DEFAULT 'html' CHECK(content_type IN ('html','scorm','video','pdf')),
    file_path TEXT NOT NULL,
    file_name TEXT,
    file_size INTEGER,
    display_order INTEGER DEFAULT 1,
    is_primary INTEGER DEFAULT 0,
    uploaded_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (elearning_module_id) REFERENCES elearning_modules(id) ON DELETE CASCADE
);
```

**Indexen:**
- `idx_lms_content_mod` op `elearning_module_id`

#### 2.2.2 lms_enrollments

```sql
CREATE TABLE lms_enrollments (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    employee_id INTEGER NOT NULL,
    elearning_module_id INTEGER NOT NULL,
    status TEXT DEFAULT 'not_started' CHECK(status IN ('not_started','in_progress','completed','failed','expired','withdrawn')),
    progress_percent INTEGER DEFAULT 0 CHECK(progress_percent BETWEEN 0 AND 100),
    enrolled_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    started_at DATETIME,
    completed_at DATETIME,
    last_accessed_at DATETIME,
    time_spent_seconds INTEGER DEFAULT 0,
    final_score INTEGER,
    final_passed INTEGER DEFAULT 0,
    attempts_allowed INTEGER DEFAULT 3,
    due_date DATE,
    assigned_by TEXT DEFAULT 'admin',
    notes TEXT,
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE,
    FOREIGN KEY (elearning_module_id) REFERENCES elearning_modules(id) ON DELETE CASCADE,
    UNIQUE(employee_id, elearning_module_id)
);
```

**Statussen:**
- `not_started` — Nog niet begonnen
- `in_progress` — Onderweg
- `completed` — Succesvol voltooid
- `failed` — Niet behaald (quiz score < passing_score)
- `expired` — Verlopen (due_date overschreden)
- `withdrawn` — Ingetrokken

**Indexen:**
- `idx_lms_enroll_emp` op `employee_id`
- `idx_lms_enroll_mod` op `elearning_module_id`
- `idx_lms_enroll_status` op `status`
- `idx_lms_enroll_due` op `due_date`

#### 2.2.3 lms_quiz_results

```sql
CREATE TABLE lms_quiz_results (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    enrollment_id INTEGER NOT NULL,
    attempt_number INTEGER NOT NULL DEFAULT 1,
    answers_json TEXT NOT NULL DEFAULT '{}',
    total_questions INTEGER DEFAULT 0,
    correct_answers INTEGER DEFAULT 0,
    score INTEGER NOT NULL,
    passed INTEGER NOT NULL DEFAULT 0,
    time_spent_seconds INTEGER DEFAULT 0,
    started_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    submitted_at DATETIME,
    FOREIGN KEY (enrollment_id) REFERENCES lms_enrollments(id) ON DELETE CASCADE
);
```

**Indexen:**
- `idx_lms_quiz_enroll` op `enrollment_id`

#### 2.2.4 lms_compliance_rules

```sql
CREATE TABLE lms_compliance_rules (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    elearning_module_id INTEGER NOT NULL,
    training_program_id INTEGER,
    role_ids TEXT DEFAULT '[]',
    valid_period_days INTEGER DEFAULT 365,
    renewal_days_before_expiry INTEGER DEFAULT 30,
    is_mandatory INTEGER DEFAULT 0,
    auto_assign INTEGER DEFAULT 0,
    auto_renew INTEGER DEFAULT 1,
    notification_days TEXT DEFAULT '[30,14,7,1]',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (elearning_module_id) REFERENCES elearning_modules(id) ON DELETE CASCADE,
    FOREIGN KEY (training_program_id) REFERENCES training_programs(id) ON DELETE SET NULL
);
```

**Velden:**
- `role_ids` — JSON array met role ID's (bijv. `"[1,2,3]"`)
- `notification_days` — JSON array met dagen voor verloop (bijv. `"[30,14,7,1]"`)
- `is_mandatory` — Verplichte training voor compliance
- `auto_assign` — Automatisch toewijzen aan nieuwe medewerkers
- `auto_renew` — Automatisch opnieuw toewijzen na verloop

**Indexen:**
- `idx_lms_compliance_mod` op `elearning_module_id`

### 2.3 Relaties

```
employees (1) ──────── (∞) lms_enrollments (∞) ──────── (1) elearning_modules
                                                           │
                                                           │ (1)
                                                           │
                                                       lms_course_content (∞)

lms_enrollments (1) ──────── (∞) lms_quiz_results

lms_compliance_rules (∞) ─── (1) elearning_modules
lms_compliance_rules (∞) ─── (0,1) training_programs

lms_enrollments (∞) ──────── (∞) trn_certificates
certifications (0,1) ─────── lms_compliance_rules (0,1)
```

---

## 3. API Reference

### 3.1 User Portal Endpoints

#### 3.1.1 Dashboard

**GET** `/lms/`
- Beschrijving: User LMS Dashboard — trainingen, voortgang, certificaten
- Authenticatie: Stub (eerste actieve employee)
- Response: HTML pagina met KPI cards en tabel

**GET** `/lms/my-courses`
- Beschrijving: Lijst toegewezen trainingen met status
- Response: HTML tabel met enrollment data

**GET** `/lms/my-certificates`
- Beschrijving: Mijn behaalde certificaten
- Response: HTML tabel met certificaten

#### 3.1.2 Course Player

**GET** `/lms/course/<module_id>`
- Beschrijving: Course viewer — laad e-learning HTML in iframe
- Auto-creëert enrollment indien niet bestaand
- Markeert als `in_progress` bij eerste bezoek
- Update `last_accessed_at`
- Response: HTML met iframe en quiz integratie script

**POST** `/lms/course/<module_id>/start`
- Beschrijving: Markeer enrollment als `in_progress`
- Body: None
- Response: Redirect naar `/lms/course/<module_id>`

**POST** `/lms/course/<module_id>/progress`
- Beschrijving: Update voortgang (progress_percent, time_spent)
- Body: JSON `{"progress_percent": 50, "time_spent_seconds": 120}`
- Response: JSON `{"success": true}`

**POST** `/lms/course/<module_id>/complete`
- Beschrijving: Markeer enrollment als `completed` (progress=100%)
- Body: None
- Response: Redirect naar `/lms/course/<module_id>`

**GET** `/lms/serve-content/<content_id>`
- Beschrijving: Serveer gekoppeld HTML content bestand
- Response: Raw HTML content of 404

**GET** `/lms/serve-content-inline/<module_id>`
- Beschrijving: Serveer inline content_html uit elearning_modules
- Response: Raw HTML content of 404

#### 3.1.3 Quiz Engine

**GET** `/lms/course/<module_id>/quiz`
- Beschrijving: Haal quiz-vragen op of serveer externe quiz HTML
- Prioriteit:
  1. Externe quiz HTML bestand (via lms_course_content)
  2. Inline quiz_questions JSON (uit elearning_modules)
- Response: HTML quiz interface

**POST** `/lms/course/<module_id>/quiz/submit`
- Beschrijving: Verwerk quiz-antwoorden (interne quiz engine)
- Body: JSON `{"answers": {"1": "A", "2": "B", "3": "C"}}`
- Response: JSON `{"success": true, "score": 83, "total_questions": 6, "correct_answers": 5, "passed": true, "passing_score": 70, "certificate_ref": "JvG-LMS-001-0001-ABC123"}`

### 3.2 Admin Portal Endpoints

#### 3.2.1 Course Management

**GET** `/lms/admin/courses`
- Beschrijving: Overzicht alle cursussen met statistieken
- Response: HTML tabel met modules, inschrijvingen, gem. score

**GET** `/lms/admin/courses/<id>`
- Beschrijving: Cursus detail pagina met content management
- Response: HTML formulier

**POST** `/lms/admin/courses`
- Beschrijving: Nieuwe cursus aanmaken
- Body: Form data
- Response: Redirect naar courses overzicht

**PUT** `/lms/admin/courses/<id>`
- Beschrijving: Cursus bijwerken
- Body: Form data
- Response: JSON success

**DELETE** `/lms/admin/courses/<id>`
- Beschrijving: Cursus deactiveren (status='archived')
- Response: JSON success

**POST** `/lms/admin/courses/<id>/content`
- Beschrijving: HTML-bestand koppelen aan cursus
- Body: `{"content_type": "html", "file_path": "/path/to/file.html", "file_name": "elearning.html"}`
- Response: JSON success

**POST** `/lms/admin/courses/<id>/publish`
- Beschrijving: Cursus status → published
- Response: JSON success

#### 3.2.2 Enrollment Management

**GET** `/lms/admin/enrollments`
- Beschrijving: Alle inschrijvingen met filters
- Query params: `?status=completed&module_id=1`
- Response: HTML tabel

**POST** `/lms/admin/enrollments`
- Beschrijving: Training toewijzen aan medewerker(s)
- Body: `{"employee_ids": [1,2,3], "module_id": 1, "due_date": "2026-06-01"}`
- Response: JSON success

**POST** `/lms/admin/enrollments/bulk`
- Beschrijving: Bulk-toewijzing per functie/afdeling
- Body: `{"role_ids": [1,2], "module_id": 1, "due_date": "2026-06-01"}`
- Response: JSON success

**DELETE** `/lms/admin/enrollments/<id>`
- Beschrijving: Inschrijving intrekken
- Response: JSON success

#### 3.2.3 Compliance Matrix

**GET** `/lms/admin/compliance`
- Beschrijving: Compliance dashboard — wie is compliant
- Response: HTML met KPI cards en heatmap

**GET** `/lms/admin/compliance/matrix`
- Beschrijving: Compliance matrix (rol × training)
- Response: HTML heatmap tabel (groen/amber/rood)

**GET** `/lms/admin/compliance/expired`
- Beschrijving: Lijst verlopen trainingen
- Response: HTML tabel

**GET** `/lms/admin/compliance/alerts`
- Beschrijving: Alerts: nakomende verlopen (< 30 dagen)
- Response: HTML tabel

**POST** `/lms/admin/compliance/rules`
- Beschrijving: Compliance-regels beheren
- Body: Form data of JSON
- Response: JSON success

#### 3.2.4 Analytics

**GET** `/lms/admin/analytics`
- Beschrijving: Overzicht: completion rates, scores, trends
- Response: HTML met KPI cards en charts

**GET** `/lms/admin/analytics/course/<id>`
- Beschrijving: Per-cursus statistieken
- Response: HTML met course metrics

**GET** `/lms/admin/analytics/employee/<id>`
- Beschrijving: Per-medewerker training historie
- Response: HTML met employee timeline

### 3.3 JSON API Endpoints

**GET** `/api/lms/modules`
- Beschrijving: Alle gepubliceerde modules (JSON)
- Response: JSON array `[{"id": 1, "title": "...", "status": "published"}, ...]`

**GET** `/api/lms/modules/<module_id>`
- Beschrijving: Module detail inclusief quiz-vragen en content files
- Response: JSON object met `content_files` array

**POST** `/api/lms/score`
- Beschrijving: Score-invoer vanuit externe HTML-quiz (via fetch/XHR)
- Body: JSON
  ```json
  {
    "employee_id": 1,
    "module_id": 2,
    "answers": {"1": "A", "2": "B", "3": "C"},
    "score": 83,
    "total_questions": 6,
    "correct_answers": 5,
    "time_spent": 420
  }
  ```
- Response: JSON
  ```json
  {
    "success": true,
    "score": 83,
    "passed": true,
    "certificate_ref": "JvG-LMS-001-0001-ABC123"
  }
  ```

**GET** `/api/lms/enrollments/<employee_id>`
- Beschrijving: Inschrijvingen per medewerker
- Response: JSON array met enrollment data

**GET** `/api/lms/compliance/summary`
- Beschrijving: Compliance samenvatting
- Response: JSON
  ```json
  {
    "total_employees": 50,
    "compliant": 42,
    "non_compliant": 8,
    "compliance_pct": 84
  }
  ```

**GET** `/api/lms/certificates/<employee_id>`
- Beschrijving: Certificaten per medewerker
- Response: JSON array met certificaten

---

## 4. Quiz Scoring Engine

### 4.1 Score Berekening

Het LMS ondersteunt twee quiz-modi:

#### 4.1.1 Interne Quiz Engine (JSON-based)

**Gebruikt:** `quiz_questions` veld in `elearning_modules` tabel

**Data formaat:**
```json
[
  {
    "question": "Wat is de juiste volgorde bij evacuatie?",
    "options": ["Alarm slaan", "Evacueren", "Assembleerpunt opzoeken", "Niet terugkeren"],
    "correct": "A"
  },
  {
    "question": "Welke PBM is verplicht bij werken met kwik?",
    "options": ["Veiligheidsbril", "Handschoenen", "Respirator", "Alle bovenstaande"],
    "correct": "D"
  }
]
```

**Algoritme:**
```python
# Pseudocode
score = (correct_answers / total_questions) * 100
passed = 1 if score >= module.passing_score else 0
```

**Flow:**
1. User vult quiz in (HTML formulier)
2. POST naar `/lms/course/<module_id>/quiz/submit`
3. LMS vergelijkt antwoorden met `correct` veld
4. Bereken score en update enrollment
5. Genereer certificaat indien passed

#### 4.1.2 Externe Quiz Engine (HTML-based)

**Gebruikt:** Bestaande HTML quiz bestanden (bijv. `kwik-quiz_v1.0.html`)

**Integratie:** Via PostMessage protocol of direct fetch naar `/api/lms/score`

**Flow:**
1. HTML quiz laadt in iframe
2. JavaScript berekent score intern
3. Score verzonden naar LMS via PostMessage of fetch
4. LMS verwerkt score via `/api/lms/score` endpoint
5. Update enrollment en certificaten

### 4.2 Compliance Updates

Wanneer een quiz succesvol wordt afgerond (`passed=1`):

1. **Update `lms_enrollments`:**
   ```sql
   UPDATE lms_enrollments 
   SET status='completed', 
       progress_percent=100, 
       final_score=?, 
       final_passed=1, 
       completed_at=datetime('now')
   WHERE id=?
   ```

2. **Insert `lms_quiz_results`:**
   ```sql
   INSERT INTO lms_quiz_results 
   (enrollment_id, attempt_number, answers_json, total_questions, 
    correct_answers, score, passed, submitted_at)
   VALUES (?, ?, ?, ?, ?, ?, ?, datetime('now'))
   ```

3. **Genereer certificaat (`trn_certificates`):**
   ```sql
   INSERT INTO trn_certificates 
   (employee_id, module_id, quiz_score, passed, certificate_ref)
   VALUES (?, ?, ?, 1, ?)
   ```
   - `certificate_ref` formaat: `JvG-LMS-{module_id:03d}-{employee_id:04d}-{6-char-uuid}`

4. **Update `certifications` (als training_program gekoppeld):**
   ```sql
   SELECT training_program_id FROM lms_compliance_rules 
   WHERE elearning_module_id=?
   
   UPDATE certifications 
   SET expiry_date=?, 
       status='active', 
       score=?
   WHERE employee_id=? AND training_program_id=?
   
   -- OF insert als niet bestaand:
   INSERT INTO certifications 
   (employee_id, training_program_id, expiry_date, status, score)
   VALUES (?, ?, ?, 'active', ?)
   ```
   - `expiry_date` = `today + valid_period_days`

---

## 5. Quiz-Integratie Protocol

### 5.1 PostMessage Patroon

Bestaande HTML quiz bestanden communiceren met het LMS via `window.parent.postMessage`.

#### 5.1.1 Sender (Quiz HTML)

```javascript
// In de quiz-HTML (kind-iframe):
const calculateScore = function() {
    // Bereken score intern...
    const score = 83;
    const correct = 5;
    const total = 6;
    const answers = {1: "A", 2: "B", 3: "C", 4: "D", 5: "A", 6: "B"};
    
    // Verstuur naar LMS
    window.parent.postMessage({
        type: 'lms_quiz_complete',
        data: {
            module_id: 2,           // Uit URL parameter
            score: score,
            total_questions: total,
            correct_answers: correct,
            answers: answers,
            time_spent: 420         // seconden
        }
    }, '*');  // Wildcard: in productie specificere origin
};
```

#### 5.1.2 Receiver (Course Player)

```javascript
// In de Course Player (parent):
window.addEventListener('message', function(event) {
    // Validate origin in production!
    if (event.origin !== window.location.origin) return;
    
    if (event.data && event.data.type === 'lms_quiz_complete') {
        // Verstuur naar LMS API
        fetch('/hseq-dashboard/api/lms/score', {
            method: 'POST',
            headers: {'Content-Type': 'application/json'},
            body: JSON.stringify({
                employee_id: currentEmployeeId,  // Uit sessie
                ...event.data.data
            })
        })
        .then(r => r.json())
        .then(result => {
            if (result.success) {
                // Update UI: toon resultaat, voortgang, certificaat
                alert(`Quiz behaald! Score: ${result.score}%\nCertificaat: ${result.certificate_ref}`);
                location.href = '/hseq-dashboard/lms/';
            } else {
                alert('Fout bij opslaan quiz-resultaat: ' + result.error);
            }
        });
    }
});
```

### 5.2 Direct Fetch Patroon

Alternatief: Directe fetch vanuit quiz HTML naar LMS API (geen PostMessage nodig).

```javascript
// In quiz-HTML:
fetch('/hseq-dashboard/api/lms/score', {
    method: 'POST',
    headers: {'Content-Type': 'application/json'},
    body: JSON.stringify({
        employee_id: 1,          // Uit sessie/URL parameter
        module_id: 2,            // Uit URL parameter
        score: 83,
        total_questions: 6,
        correct_answers: 5,
        answers: {1: "A", 2: "B", 3: "C", 4: "D", 5: "A", 6: "B"},
        time_spent: 420
    })
})
.then(r => r.json())
.then(result => {
    console.log('Resultaat:', result);
    // Update UI...
});
```

### 5.3 URL Parameters

Bij het laden van een course in de iframe, URL parameters meesturen:

```
/lms/course/2?employee_id=1&lang=nl
```

In de quiz HTML:
```javascript
const urlParams = new URLSearchParams(window.location.search);
const employeeId = urlParams.get('employee_id');
const moduleId = urlParams.get('module_id') || parentModuleId;
```

---

## 6. Compliance Berekening

### 6.1 Algoritme

**Per medewerker:**

```python
def calculate_compliance(employee_id):
    # Haal alle verplichte trainingen op via training_matrix of compliance rules
    mandatory_trainings = get_mandatory_trainings(employee_id)
    
    for training in mandatory_trainings:
        # Check of certification bestaat en geldig is
        cert = get_latest_certification(employee_id, training.id)
        
        if not cert:
            return "non_compliant"
        
        if cert.status != 'active':
            return "non_compliant"
        
        if cert.expiry_date < today:
            return "expired"
        
        if cert.expiry_date < today + timedelta(days=30):
            return "expiring_soon"
    
    return "compliant"
```

**Statussen:**

| Status | Beschrijving | Kleur |
|--------|-------------|-------|
| `compliant` | Alle verplichte trainingen geldig (expiry ≥ 30 dagen) | Groen |
| `expiring_soon` | Training verloopt binnen 30 dagen | Amber |
| `expired` | Training verlopen (expiry < today) | Rood |
| `non_compliant` | Ontbrekende of niet-behaalde trainingen | Rood |

### 6.2 Vervaldatum Checks

**Per certification:**
```sql
SELECT expiry_date, 
       CASE 
           WHEN expiry_date < date('now') THEN 'expired'
           WHEN expiry_date < date('now', '+30 days') THEN 'expiring_soon'
           ELSE 'active'
       END as status
FROM certifications
WHERE employee_id = ? AND training_program_id = ?
```

**Per enrollment (zonder training_program):**
```sql
SELECT completed_at, 
       (SELECT valid_period_days FROM lms_compliance_rules WHERE elearning_module_id = ?) as valid_days,
       date(completed_at, '+' || valid_days || ' days') as expiry_date
FROM lms_enrollments
WHERE employee_id = ? AND elearning_module_id = ? AND final_passed = 1
```

### 6.3 Compliance Dashboard

**KPI Metrics:**
- `total_employees` — Totaal actieve medewerkers
- `compliant` — Aantal compliant medewerkers
- `non_compliant` — Aantal niet-compliant medewerkers
- `compliance_pct` — Percentage compliant

**Heatmap (Rol × Training):**
```
           | Training A | Training B | Training C |
-----------|------------|------------|------------|
Operator   |     G      |     A      |     R      |
Supervisor |     G      |     G      |     G      |
Manager    |     G      |     A      |     G      |

G = Groen (compliant)
A = Amber (expiring_soon)
R = Rood (expired/missing)
```

**API Endpoint:**
```
GET /api/lms/compliance/summary
```

**Response:**
```json
{
  "total_employees": 50,
  "compliant": 42,
  "non_compliant": 8,
  "compliance_pct": 84
}
```

---

## 7. Deployment Guide

### 7.1 Prerequisites

- Python 3.8+
- SQLite3
- PM2 geïnstalleerd
- Bestaande HSEQ Intelligence Monitor applicatie (app.py, hseq_kennisbank.db)

### 7.2 Stap-voor-Stap Uitrol

#### Stap 1: Bestanden Kopiëren

```bash
# Ga naar app directory
cd /root/projects/jg/HSEQ-Intelligence-Monitor/app/

# Kopieer LMS module (indien elders gegenereerd)
cp /root/projects/jg/hseq-lms/deliverables/py/module_lms_v1.0.py module_lms.py
cp /root/projects/jg/hseq-lms/deliverables/py/lms_migration_v1.0.py lms_migration.py
```

#### Stap 2: Database Migratie

```bash
# Voer migratie uit
python3 lms_migration.py
```

**Output:**
```
[LMS Migration v1.0] Connecting to: /root/projects/jg/HSEQ-Intelligence-Monitor/app/hseq_kennisbank.db
  ✓ lms_course_content
  ✓ lms_enrollments
  ✓ lms_quiz_results
  ✓ lms_compliance_rules
  ✓ 7 indexes
  ✓ Seed: module 1 updated to published
  ✓ Seed: course content linked for module 1
  ✓ Seed: 6 employees enrolled in module 1

[LMS Migration v1.0] Complete. Alle tabellen, indexen en seed data verwerkt.
```

#### Stap 3: App.py Update

In `app.py`, voeg toe:

```python
# Import LMS module
from module_lms import lms_bp

# Registreer blueprint
app.register_blueprint(lms_bp)

# Of, als Blueprint niet gebruikt: direct route registratie
# import module_lms
# module_lms.register_routes(app)  # Als module_lms een register_routes functie heeft
```

**Navigatie Update:**
Voeg LMS tab toe aan sidebar:

```html
<a href="{{ BASE_PATH }}/lms/" class="nav-item {{ 'active' if request.path.startswith('/lms/') else '' }}">
    🎓 LMS Academy
</a>
```

#### Stap 4: Course Content

Kopieer course content HTML-bestanden:

```bash
# Maak course-content directory
mkdir -p /root/projects/jg/hseq-lms/course-content/kwik-elearning
mkdir -p /root/projects/jg/hseq-lms/course-content/straling-olie-gas

# Kopieer bestanden
cp /root/projects/jg/2026-pbm-KWIK/deliverables/html/kwik-elearning_v1.0.html \
   /root/projects/jg/hseq-lms/course-content/kwik-elearning/elearning.html

cp /root/projects/jg/2026-pbm-KWIK/deliverables/html/kwik-quiz_v1.0.html \
   /root/projects/jg/hseq-lms/course-content/kwik-elearning/quiz.html

cp /root/projects/jg/2026-pbm-STRALING-OLIE-GAS/deliverables/html/straling-elearning_v1.0.html \
   /root/projects/jg/hseq-lms/course-content/straling-olie-gas/elearning.html

cp /root/projects/jg/2026-pbm-STRALING-OLIE-GAS/deliverables/html/straling-quiz_v1.0.html \
   /root/projects/jg/hseq-lms/course-content/straling-olie-gas/quiz.html
```

#### Stap 5: PM2 Restart

```bash
# Restart PM2 proces
pm2 restart hseq-kennisbank

# Check status
pm2 status hseq-kennisbank

# Check logs
pm2 logs hseq-kennisbank --lines 50
```

#### Stap 6: Verificatie

```bash
# Check of LMS pagina reageert
curl -s http://localhost:5052/hseq-dashboard/lms/ | head -20

# Check API endpoints
curl -s http://localhost:5052/hseq-dashboard/api/lms/modules | jq '.'
```

**Verwachte output:**
```json
[
  {
    "id": 1,
    "title": "Gevaarlijke Stoffen — Basisveiligheid",
    "category": "HSE",
    "status": "published",
    ...
  }
]
```

### 7.3 Rollback Procedure

```bash
# Stop PM2
pm2 stop hseq-kennisbank

# Verwijder LMS import uit app.py (comment out)

# Restart
pm2 start hseq-kennisbank

# Optioneel: database rollback (indien nodig)
sqlite3 hseq_kennisbank.db "DROP TABLE IF EXISTS lms_course_content; DROP TABLE IF EXISTS lms_enrollments; DROP TABLE IF EXISTS lms_quiz_results; DROP TABLE IF EXISTS lms_compliance_rules;"
```

---

## 8. Troubleshooting

### 8.1 Veelvoorkomende Problemen

#### Probleem: LMS pagina toont 404

**Oorzaak:** Blueprint niet geregistreerd of route prefix incorrect.

**Oplossing:**
```python
# In app.py:
from module_lms import lms_bp
app.register_blueprint(lms_bp)  # Prefix: /lms/

# Verify: print(app.url_map) to see all registered routes
```

#### Probleem: Course content laadt niet (iframe blanco)

**Oorzaken:**
- Bestandspad niet correct
- Bestandspermissies ontbreken
- `lms_course_content` tabel leeg

**Oplossing:**
```bash
# Check bestand
ls -la /root/projects/jg/hseq-lms/course-content/kwik-elearning/

# Check database
sqlite3 hseq_kennisbank.db "SELECT * FROM lms_course_content WHERE elearning_module_id=1;"

# Check logs
tail -f pm2 logs hseq-kennisbank
```

#### Probleem: Quiz score wordt niet opgeslagen

**Oorzaken:**
- PostMessage niet ontvangen
- `/api/lms/score` endpoint faalt
- CORS issue (indien cross-origin)

**Oplossing:**
```javascript
// Check browser console voor errors
// Verify PostMessage listener exists
console.log('PostMessage listener:', window.addEventListener.toString().includes('lms_quiz_complete'));

// Verify endpoint
fetch('/hseq-dashboard/api/lms/score', {method: 'POST'})
  .then(r => console.log('Response status:', r.status));
```

#### Probleem: Compliance dashboard toont incorrecte data

**Oorzaken:**
- `lms_compliance_rules` niet geconfigureerd
- Certification expiry dates incorrect
- Employee enrollment status incorrect

**Oplossing:**
```sql
-- Check compliance rules
SELECT * FROM lms_compliance_rules;

-- Check certifications
SELECT e.first_name, e.last_name, tp.program_name, c.expiry_date, c.status
FROM certifications c
JOIN employees e ON e.id = c.employee_id
JOIN training_programs tp ON tp.id = c.training_program_id
WHERE e.status='active';

-- Check enrollments
SELECT e.first_name, e.last_name, m.title, en.status, en.final_passed, en.completed_at
FROM lms_enrollments en
JOIN employees e ON e.id = en.employee_id
JOIN elearning_modules m ON m.id = en.elearning_module_id
ORDER BY en.id DESC LIMIT 10;
```

#### Probleem: PM2 proces crasht na LMS update

**Oorzaken:**
- Syntax error in module_lms.py
- Import error
- Database locked

**Oplossing:**
```bash
# Check logs
pm2 logs hseq-kennisbank --lines 100

# Test module direct
python3 -c "import module_lms; print('OK')"

# Check database lock
sqlite3 hseq_kennisbank.db "PRAGMA integrity_check;"
```

### 8.2 Debugging Tips

**1. Logging toevoegen:**
```python
# In module_lms.py:
import logging
logging.basicConfig(level=logging.DEBUG)

@lms_bp.route('/course/<int:module_id>')
def course_player(module_id):
    logging.debug(f"Loading course {module_id} for employee {_current_employee_id()}")
    ...
```

**2. Database queries loggen:**
```python
# Na elke query:
result = conn.execute("SELECT ...")
logging.debug(f"Query result: {result.fetchone()}")
```

**3. API requests trace:**
```bash
# Start ngrok voor externe debugging
ngrok http 5052

# Of gebruik curl
curl -v http://localhost:5052/hseq-dashboard/api/lms/modules
```

**4. Browser Developer Tools:**
- Network tab: Check requests en responses
- Console tab: Check JavaScript errors
- Application tab: Check cookies en localStorage

---

## 9. Security Considerations

### 9.1 Sessiebeheer

**Current Implementation (Stub):**
```python
def _current_employee_id():
    """Stub: returns first active employee. Replace with real session auth."""
    conn = get_db()
    r = conn.execute("SELECT id FROM employees WHERE status='active' ORDER BY id LIMIT 1").fetchone()
    return r['id'] if r else 1
```

**Production Recommendation:**
```python
from flask_login import current_user

def _current_employee_id():
    if not current_user.is_authenticated:
        return None
    return current_user.id
```

**Session Security:**
- Gebruik `session.permanent = False` (default)
- Set `app.secret_key` naar strong random value
- Gebruik HTTPS in productie
- Implementeer CSRF protection (`flask_wtf.csrf`)

### 9.2 Input Validatie

**User Input:**
```python
# Sanitize file paths (prevent directory traversal)
import os
def safe_path(base_dir, user_path):
    full_path = os.path.abspath(os.path.join(base_dir, user_path))
    if not full_path.startswith(os.path.abspath(base_dir)):
        raise ValueError("Invalid path")
    return full_path
```

**SQL Injection Preventie:**
- Gebruik **altijd** parameterized queries (al geïmplementeerd)
- Never string interpolation in SQL queries

**Correct:**
```python
conn.execute("SELECT * FROM employees WHERE id = ?", (employee_id,))
```

**Incorrect:**
```python
conn.execute(f"SELECT * FROM employees WHERE id = {employee_id}")  # X-VERBODEN
```

### 9.3 File Upload Security

Indien file upload functionaliteit wordt toegevoegd:

```python
ALLOWED_EXTENSIONS = {'html', 'htm', 'pdf'}
MAX_FILE_SIZE = 10 * 1024 * 1024  # 10MB

def allowed_file(filename):
    return '.' in filename and \
           filename.rsplit('.', 1)[1].lower() in ALLOWED_EXTENSIONS

def validate_file_upload(file):
    # Check extension
    if not allowed_file(file.filename):
        raise ValueError("Invalid file type")
    
    # Check size
    file.seek(0, os.SEEK_END)
    if file.tell() > MAX_FILE_SIZE:
        raise ValueError("File too large")
    
    # Scan for malicious content (optional)
    content = file.read().decode('utf-8', errors='ignore')
    if '<script' in content.lower():
        raise ValueError("Script tags not allowed")
```

### 9.4 PostMessage Origin Validation

**Current (Wildcard):**
```javascript
window.parent.postMessage(data, '*');  // Wildcard: unsafe in production
```

**Production:**
```javascript
// Sender (quiz HTML):
window.parent.postMessage(data, 'https://mescalinerabbit.shop');

// Receiver (Course Player):
window.addEventListener('message', function(event) {
    if (event.origin !== 'https://mescalinerabbit.shop') {
        console.warn('Invalid origin:', event.origin);
        return;
    }
    // Process message
});
```

### 9.5 API Authentication

Indien API endpoints extern toegankelijk moeten zijn:

```python
from functools import wraps

def api_auth_required(f):
    @wraps(f)
    def decorated_function(*args, **kwargs):
        api_key = request.headers.get('X-API-Key')
        if not api_key or not validate_api_key(api_key):
            return jsonify(success=False, error="Unauthorized"), 401
        return f(*args, **kwargs)
    return decorated_function

@lms_bp.route('/api/lms/score', methods=['POST'])
@api_auth_required
def api_score():
    ...
```

**Environment Variables:**
```bash
export LMS_API_KEY="your-secure-random-key"
```

### 9.6 Rate Limiting

```python
from flask_limiter import Limiter
from flask_limiter.util import get_remote_address

limiter = Limiter(app=app, key_func=get_remote_address)

@lms_bp.route('/api/lms/score', methods=['POST'])
@limiter.limit("10 per minute")
def api_score():
    ...
```

### 9.7 Logging & Auditing

```python
import logging
from logging.handlers import RotatingFileHandler

# Setup logging
handler = RotatingFileHandler('/var/log/hseq-kennisbank/lms.log', maxBytes=10*1024*1024, backupCount=5)
handler.setFormatter(logging.Formatter('%(asctime)s %(levelname)s: %(message)s'))
logging.getLogger('lms').addHandler(handler)

# Log critical actions
@lms_bp.route('/api/lms/score', methods=['POST'])
def api_score():
    logging.info(f"Quiz submitted: employee_id={emp_id}, module_id={module_id}, score={score}")
    ...
```

### 9.8 Database Backup

```bash
# Automated backup script
#!/bin/bash
BACKUP_DIR="/backup/hseq-kennisbank"
DATE=$(date +%Y%m%d_%H%M%S)
DB_PATH="/root/projects/jg/HSEQ-Intelligence-Monitor/app/hseq_kennisbank.db"

mkdir -p $BACKUP_DIR
cp $DB_PATH $BACKUP_DIR/hseq_kennisbank_$DATE.db

# Retain last 7 days
find $BACKUP_DIR -name "*.db" -mtime +7 -delete
```

**Cron Job:**
```
0 2 * * * /root/scripts/backup_lms.sh
```

---

## 10. Appendix A: CSS Variabelen

```css
:root {
  --primary: #003366;
  --primary-light: #1a5276;
  --success: #00A859;
  --warning: #F59E0B;
  --danger: #EF4444;
  --bg: #F3F4F6;
  --card: #ffffff;
  --text: #1F2937;
  --text-muted: #6B7280;
  --border: #E5E7EB;
}
```

---

## 11. Appendix B: Database Schema (Full DDL)

```sql
-- lms_course_content
CREATE TABLE IF NOT EXISTS lms_course_content (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    elearning_module_id INTEGER NOT NULL,
    content_type TEXT NOT NULL DEFAULT 'html' CHECK(content_type IN ('html','scorm','video','pdf')),
    file_path TEXT NOT NULL,
    file_name TEXT,
    file_size INTEGER,
    display_order INTEGER DEFAULT 1,
    is_primary INTEGER DEFAULT 0,
    uploaded_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (elearning_module_id) REFERENCES elearning_modules(id) ON DELETE CASCADE
);

-- lms_enrollments
CREATE TABLE IF NOT EXISTS lms_enrollments (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    employee_id INTEGER NOT NULL,
    elearning_module_id INTEGER NOT NULL,
    status TEXT DEFAULT 'not_started' CHECK(status IN ('not_started','in_progress','completed','failed','expired','withdrawn')),
    progress_percent INTEGER DEFAULT 0 CHECK(progress_percent BETWEEN 0 AND 100),
    enrolled_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    started_at DATETIME,
    completed_at DATETIME,
    last_accessed_at DATETIME,
    time_spent_seconds INTEGER DEFAULT 0,
    final_score INTEGER,
    final_passed INTEGER DEFAULT 0,
    attempts_allowed INTEGER DEFAULT 3,
    due_date DATE,
    assigned_by TEXT DEFAULT 'admin',
    notes TEXT,
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE,
    FOREIGN KEY (elearning_module_id) REFERENCES elearning_modules(id) ON DELETE CASCADE,
    UNIQUE(employee_id, elearning_module_id)
);

-- lms_quiz_results
CREATE TABLE IF NOT EXISTS lms_quiz_results (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    enrollment_id INTEGER NOT NULL,
    attempt_number INTEGER NOT NULL DEFAULT 1,
    answers_json TEXT NOT NULL DEFAULT '{}',
    total_questions INTEGER DEFAULT 0,
    correct_answers INTEGER DEFAULT 0,
    score INTEGER NOT NULL,
    passed INTEGER NOT NULL DEFAULT 0,
    time_spent_seconds INTEGER DEFAULT 0,
    started_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    submitted_at DATETIME,
    FOREIGN KEY (enrollment_id) REFERENCES lms_enrollments(id) ON DELETE CASCADE
);

-- lms_compliance_rules
CREATE TABLE IF NOT EXISTS lms_compliance_rules (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    elearning_module_id INTEGER NOT NULL,
    training_program_id INTEGER,
    role_ids TEXT DEFAULT '[]',
    valid_period_days INTEGER DEFAULT 365,
    renewal_days_before_expiry INTEGER DEFAULT 30,
    is_mandatory INTEGER DEFAULT 0,
    auto_assign INTEGER DEFAULT 0,
    auto_renew INTEGER DEFAULT 1,
    notification_days TEXT DEFAULT '[30,14,7,1]',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (elearning_module_id) REFERENCES elearning_modules(id) ON DELETE CASCADE,
    FOREIGN KEY (training_program_id) REFERENCES training_programs(id) ON DELETE SET NULL
);

-- Indexes
CREATE INDEX IF NOT EXISTS idx_lms_enroll_emp ON lms_enrollments(employee_id);
CREATE INDEX IF NOT EXISTS idx_lms_enroll_mod ON lms_enrollments(elearning_module_id);
CREATE INDEX IF NOT EXISTS idx_lms_enroll_status ON lms_enrollments(status);
CREATE INDEX IF NOT EXISTS idx_lms_enroll_due ON lms_enrollments(due_date);
CREATE INDEX IF NOT EXISTS idx_lms_quiz_enroll ON lms_quiz_results(enrollment_id);
CREATE INDEX IF NOT EXISTS idx_lms_content_mod ON lms_course_content(elearning_module_id);
CREATE INDEX IF NOT EXISTS idx_lms_compliance_mod ON lms_compliance_rules(elearning_module_id);
```

---

## 12. Appendix C: Codevoorbeelden

### C.1 Enrollment Creatie

```python
# Toewijzen aan enkele medewerker
def enroll_employee(employee_id, module_id, due_date=None):
    conn = get_db()
    try:
        conn.execute("""
            INSERT OR IGNORE INTO lms_enrollments 
            (employee_id, elearning_module_id, status, due_date)
            VALUES (?, ?, 'not_started', ?)
        """, (employee_id, module_id, due_date))
        conn.commit()
    finally:
        conn.close()

# Bulk toewijzing per rol
def enroll_role(role_id, module_id, due_date=None):
    conn = get_db()
    try:
        conn.execute("""
            INSERT OR IGNORE INTO lms_enrollments 
            (employee_id, elearning_module_id, status, due_date)
            SELECT e.id, ?, 'not_started', ?
            FROM employees e
            WHERE e.role_id = ? AND e.status = 'active'
        """, (module_id, due_date, role_id))
        conn.commit()
    finally:
        conn.close()
```

### C.2 Compliance Check

```python
from datetime import datetime, timedelta, date

def check_employee_compliance(employee_id):
    conn = get_db()
    try:
        # Get mandatory trainings via compliance rules
        rules = conn.execute("""
            SELECT cr.elearning_module_id, cr.valid_period_days
            FROM lms_compliance_rules cr
            WHERE cr.is_mandatory = 1
        """).fetchall()
        
        if not rules:
            return {"status": "compliant", "details": "No mandatory trainings"}
        
        issues = []
        
        for rule in rules:
            # Get latest successful enrollment
            enrollment = conn.execute("""
                SELECT final_passed, completed_at
                FROM lms_enrollments
                WHERE employee_id = ? AND elearning_module_id = ?
                ORDER BY completed_at DESC LIMIT 1
            """, (employee_id, rule['elearning_module_id'])).fetchone()
            
            if not enrollment or not enrollment['final_passed']:
                issues.append(f"Module {rule['elearning_module_id']}: not passed")
                continue
            
            # Check expiry
            completed = datetime.strptime(enrollment['completed_at'], '%Y-%m-%d %H:%M:%S')
            expiry = completed + timedelta(days=rule['valid_period_days'])
            
            if expiry.date() < date.today():
                issues.append(f"Module {rule['elearning_module_id']}: expired on {expiry.date()}")
            elif expiry.date() < date.today() + timedelta(days=30):
                issues.append(f"Module {rule['elearning_module_id']}: expiring on {expiry.date()}")
        
        status = "non_compliant" if issues else "compliant"
        return {"status": status, "issues": issues}
    finally:
        conn.close()
```

### C.3 Quiz Resultaten Export

```python
def export_quiz_results(module_id, output_path):
    conn = get_db()
    try:
        rows = conn.execute("""
            SELECT e.first_name, e.last_name, qr.attempt_number, qr.score, 
                   qr.passed, qr.total_questions, qr.correct_answers, 
                   qr.submitted_at
            FROM lms_quiz_results qr
            JOIN lms_enrollments en ON en.id = qr.enrollment_id
            JOIN employees e ON e.id = en.employee_id
            WHERE en.elearning_module_id = ?
            ORDER BY qr.submitted_at DESC
        """, (module_id,)).fetchall()
        
        import csv
        with open(output_path, 'w', newline='') as f:
            writer = csv.writer(f)
            writer.writerow(['First Name', 'Last Name', 'Attempt', 'Score', 
                           'Passed', 'Total', 'Correct', 'Submitted'])
            for row in rows:
                writer.writerow([row['first_name'], row['last_name'], 
                               row['attempt_number'], row['score'], 
                               row['passed'], row['total_questions'], 
                               row['correct_answers'], row['submitted_at']])
        
        print(f"Exported {len(rows)} results to {output_path}")
    finally:
        conn.close()
```

---

**Einde Technische Documentatie v1.0**

*Documentatie gegenereerd op 15 april 2026 door Technical Writer (HSEQ Support Division)*
*Alle code voorbeelden zijn gebaseerd op module_lms_v1.0.py en lms_migration_v1.0.py*