#!/usr/bin/env python3 """ LMS Database Migration Script v1.0 JvG Consultancy — HSEQ Intelligence Dashboard Creates LMS tables, indexes, and seed data in hseq_kennisbank.db Idempotent: uses IF NOT EXISTS throughout. """ import sqlite3 import os import sys DB_PATH = os.path.join(os.path.dirname(os.path.abspath(__file__)), '..', '..', '..', 'HSEQ-Intelligence-Monitor', 'app', 'hseq_kennisbank.db') # Fallback to direct path if relative doesn't resolve if not os.path.exists(DB_PATH): DB_PATH = '/root/projects/jg/HSEQ-Intelligence-Monitor/app/hseq_kennisbank.db' def migrate(): print(f"[LMS Migration v1.0] Connecting to: {DB_PATH}") if not os.path.exists(DB_PATH): print(f"[FATAL] Database not found at {DB_PATH}") sys.exit(1) conn = sqlite3.connect(DB_PATH) conn.execute("PRAGMA foreign_keys = ON") cur = conn.cursor() # ── 1. lms_course_content ──────────────────────────────────────────────── cur.execute(""" 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 ) """) print(" ✓ lms_course_content") # ── 2. lms_enrollments ─────────────────────────────────────────────────── cur.execute(""" 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) ) """) print(" ✓ lms_enrollments") # ── 3. lms_quiz_results ────────────────────────────────────────────────── cur.execute(""" 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 ) """) print(" ✓ lms_quiz_results") # ── 4. lms_compliance_rules ────────────────────────────────────────────── cur.execute(""" 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 ) """) print(" ✓ lms_compliance_rules") # ── 5. Indexen ─────────────────────────────────────────────────────────── 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)", ] for idx_sql in indexes: cur.execute(idx_sql) print(f" ✓ {len(indexes)} indexes") # ── 6. Seed Data ───────────────────────────────────────────────────────── # Update existing module 1 to published cur.execute("SELECT id FROM elearning_modules WHERE id=1") if cur.fetchone(): cur.execute("UPDATE elearning_modules SET status='published', title='Gevaarlijke Stoffen — Basisveiligheid' WHERE id=1") print(" ✓ Seed: module 1 updated to published") # Link HTML content for module 1 cur.execute("SELECT COUNT(*) FROM lms_course_content WHERE elearning_module_id=1") if cur.fetchone()[0] == 0: cur.execute(""" INSERT INTO lms_course_content (elearning_module_id, content_type, file_path, file_name, is_primary) VALUES (1, 'html', '/root/projects/jg/hseq-lms/course-content/kwik-elearning/elearning.html', 'kwik-elearning.html', 1) """) print(" ✓ Seed: course content linked for module 1") # Enroll all active employees in module 1 cur.execute(""" INSERT OR IGNORE INTO lms_enrollments (employee_id, elearning_module_id, status, due_date) SELECT e.id, 1, 'not_started', date('now', '+30 days') FROM employees e WHERE e.status='active' """) enrolled = cur.rowcount if enrolled > 0: print(f" ✓ Seed: {enrolled} employees enrolled in module 1") conn.commit() conn.close() print("\n[LMS Migration v1.0] Complete. Alle tabellen, indexen en seed data verwerkt.") if __name__ == '__main__': migrate()