#!/usr/bin/env python3
"""
eLearning Creator Module — Training & Opleiding Dienst
Integrates with HSEQ Intelligence Dashboard (Flask)
Provides: Advisory, Module Creator, Assignments, Compliance Tracking
"""

import os
import json
import sqlite3
from datetime import datetime, timedelta

DB_PATH = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'hseq_kennisbank.db')


# ─── Database Schema ─────────────────────────────────────────────────────────

SCHEMA_SQL = """
CREATE TABLE IF NOT EXISTS elearning_modules (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    category TEXT NOT NULL,
    content_html TEXT DEFAULT '',
    quiz_questions TEXT DEFAULT '[]',
    passing_score INTEGER DEFAULT 70,
    duration_minutes INTEGER DEFAULT 30,
    version TEXT DEFAULT '1.0',
    status TEXT DEFAULT 'draft',
    created_by TEXT DEFAULT 'system',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS elearning_assignments (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    employee_id INTEGER,
    contractor_employee_id INTEGER,
    elearning_module_id INTEGER NOT NULL,
    status TEXT DEFAULT 'pending',
    started_at DATETIME,
    completed_at DATETIME,
    score INTEGER,
    attempts INTEGER DEFAULT 0,
    assigned_by TEXT DEFAULT 'system',
    assigned_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    due_date DATETIME,
    FOREIGN KEY (employee_id) REFERENCES employees(id),
    FOREIGN KEY (contractor_employee_id) REFERENCES contractor_employees(id),
    FOREIGN KEY (elearning_module_id) REFERENCES elearning_modules(id)
);

CREATE TABLE IF NOT EXISTS elearning_quiz_attempts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    assignment_id INTEGER NOT NULL,
    answers TEXT DEFAULT '{}',
    score INTEGER,
    passed INTEGER DEFAULT 0,
    attempted_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (assignment_id) REFERENCES elearning_assignments(id)
);
"""


def get_db():
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    return conn


def init_elearning_tables():
    conn = get_db()
    conn.executescript(SCHEMA_SQL)
    conn.close()


# ─── Advisory Engine ─────────────────────────────────────────────────────────

ADVISORY_QUERIES = {
    'expired_certs': """
        SELECT e.name, r.name as role, tp.title as training, c.expiry_date
        FROM certifications c
        JOIN employees e ON c.employee_id = e.id
        JOIN roles r ON e.role_id = r.id
        JOIN training_programs tp ON c.program_id = tp.id
        WHERE c.expiry_date < date('now') AND c.status = 'active'
        ORDER BY c.expiry_date
    """,
    'expiring_certs': """
        SELECT e.name, r.name as role, tp.title as training, c.expiry_date
        FROM certifications c
        JOIN employees e ON c.employee_id = e.id
        JOIN roles r ON e.role_id = r.id
        JOIN training_programs tp ON c.program_id = tp.id
        WHERE c.expiry_date BETWEEN date('now') AND date('now', '+90 days')
        AND c.status = 'active'
        ORDER BY c.expiry_date
    """,
    'missing_training': """
        SELECT e.name, r.name as role, tp.title as required_training
        FROM training_matrix tm
        JOIN employees e ON e.role_id = tm.role_id
        JOIN roles r ON e.role_id = r.id
        JOIN training_programs tp ON tm.training_program_id = tp.id
        WHERE tm.is_required = 1
        AND e.status = 'active'
        AND NOT EXISTS (
            SELECT 1 FROM certifications c
            WHERE c.employee_id = e.id AND c.program_id = tm.training_program_id
            AND c.status = 'active'
        )
    """,
    'role_coverage': """
        SELECT r.name as role,
               COUNT(DISTINCT tm.training_program_id) as required,
               COUNT(DISTINCT CASE WHEN c.status='active' THEN tm.training_program_id END) as completed
        FROM roles r
        LEFT JOIN training_matrix tm ON tm.role_id = r.id AND tm.is_required = 1
        LEFT JOIN employees e ON e.role_id = r.id AND e.status = 'active'
        LEFT JOIN certifications c ON c.employee_id = e.id AND c.program_id = tm.training_program_id
        GROUP BY r.id
    """,
}


def run_advisory_scan():
    """Run compliance gap analysis and return advisory data."""
    conn = get_db()
    results = {}
    for key, sql in ADVISORY_QUERIES.items():
        try:
            rows = conn.execute(sql).fetchall()
            results[key] = [dict(r) for r in rows]
        except Exception as e:
            results[key] = {'error': str(e)}
    conn.close()
    return results


# ─── Module CRUD ─────────────────────────────────────────────────────────────

def create_module(title, category, content_html='', quiz_questions=None,
                  passing_score=70, duration_minutes=30, created_by='system'):
    conn = get_db()
    quiz_json = json.dumps(quiz_questions or [])
    cur = conn.execute(
        """INSERT INTO elearning_modules (title, category, content_html, quiz_questions, passing_score, duration_minutes, created_by)
           VALUES (?, ?, ?, ?, ?, ?, ?)""",
        (title, category, content_html, quiz_json, passing_score, duration_minutes, created_by)
    )
    conn.commit()
    module_id = cur.lastrowid
    conn.close()
    return module_id


def get_modules(category=None):
    conn = get_db()
    if category:
        rows = conn.execute("SELECT * FROM elearning_modules WHERE category = ? ORDER BY created_at DESC", (category,)).fetchall()
    else:
        rows = conn.execute("SELECT * FROM elearning_modules ORDER BY created_at DESC").fetchall()
    conn.close()
    return [dict(r) for r in rows]


def get_module(module_id):
    conn = get_db()
    row = conn.execute("SELECT * FROM elearning_modules WHERE id = ?", (module_id,)).fetchone()
    conn.close()
    return dict(row) if row else None


def update_module(module_id, **kwargs):
    conn = get_db()
    sets = []
    vals = []
    for k, v in kwargs.items():
        if k == 'quiz_questions' and isinstance(v, (list, dict)):
            v = json.dumps(v)
        sets.append(f"{k} = ?")
        vals.append(v)
    sets.append("updated_at = CURRENT_TIMESTAMP")
    vals.append(module_id)
    conn.execute(f"UPDATE elearning_modules SET {', '.join(sets)} WHERE id = ?", vals)
    conn.commit()
    conn.close()


def delete_module(module_id):
    conn = get_db()
    conn.execute("DELETE FROM elearning_assignments WHERE elearning_module_id = ?", (module_id,))
    conn.execute("DELETE FROM elearning_modules WHERE id = ?", (module_id,))
    conn.commit()
    conn.close()


# ─── Assignments ─────────────────────────────────────────────────────────────

def assign_module(module_id, employee_ids=None, contractor_ids=None,
                  assigned_by='system', due_days=30):
    conn = get_db()
    due = (datetime.now() + timedelta(days=due_days)).isoformat()
    count = 0
    for eid in (employee_ids or []):
        conn.execute(
            """INSERT INTO elearning_assignments (employee_id, elearning_module_id, assigned_by, due_date)
               VALUES (?, ?, ?, ?)""", (eid, module_id, assigned_by, due))
        count += 1
    for cid in (contractor_ids or []):
        conn.execute(
            """INSERT INTO elearning_assignments (contractor_employee_id, elearning_module_id, assigned_by, due_date)
               VALUES (?, ?, ?, ?)""", (cid, module_id, assigned_by, due))
        count += 1
    conn.commit()
    conn.close()
    return count


def get_assignments(employee_id=None, module_id=None, status=None):
    conn = get_db()
    q = """SELECT a.*, m.title as module_title, m.category,
                  COALESCE(e.name, ce.name) as assignee_name
           FROM elearning_assignments a
           JOIN elearning_modules m ON a.elearning_module_id = m.id
           LEFT JOIN employees e ON a.employee_id = e.id
           LEFT JOIN contractor_employees ce ON a.contractor_employee_id = ce.id
           WHERE 1=1"""
    params = []
    if employee_id:
        q += " AND a.employee_id = ?"
        params.append(employee_id)
    if module_id:
        q += " AND a.elearning_module_id = ?"
        params.append(module_id)
    if status:
        q += " AND a.status = ?"
        params.append(status)
    q += " ORDER BY a.assigned_at DESC"
    rows = conn.execute(q, params).fetchall()
    conn.close()
    return [dict(r) for r in rows]


def record_quiz_attempt(assignment_id, answers, score, passed):
    conn = get_db()
    conn.execute(
        """INSERT INTO elearning_quiz_attempts (assignment_id, answers, score, passed)
           VALUES (?, ?, ?, ?)""", (assignment_id, json.dumps(answers), score, 1 if passed else 0))
    new_status = 'completed' if passed else 'failed'
    completed = ", completed_at = CURRENT_TIMESTAMP" if passed else ""
    conn.execute(
        f"""UPDATE elearning_assignments SET status = ?, score = ?, attempts = attempts + 1 {completed}
            WHERE id = ?""", (new_status, score, assignment_id))
    conn.commit()
    conn.close()


# ─── Demo Module: Gevaarlijke Stoffen ───────────────────────────────────────

DEMO_MODULE = {
    'title': 'Gevaarlijke Stoffen — Basisveiligheid',
    'category': 'gevaarlijke_stoffen',
    'duration_minutes': 45,
    'passing_score': 70,
    'content_html': """
<div class="el-section">
    <h3>1. Wat zijn gevaarlijke stoffen?</h3>
    <p>Gevaarlijke stoffen zijn stoffen die door hun chemische, fysische of biologische eigenschappen een risico vormen voor de gezondheid, veiligheid of het milieu. Denk oplossingsmiddelen, zuren, logen, gassen en fijnstof.</p>
    <div class="el-highlight">
        <strong>Wettelijk kader:</strong> Arbowet art. 4.1c, Arbobesluit hoofdstuk 4, REACH/CLP-verordening
    </div>
</div>

<div class="el-section">
    <h3>2. Classificatie en Etikettering (CLP)</h3>
    <p>De CLP-verordening (EC 1272/2008) classifyert stoffen in:</p>
    <ul>
        <li><strong>Fysieke gevaren</strong> — Ontplofbaar, brandbaar, oxiderend</li>
        <li><strong>Gezondheidsgevaren</strong> — Giftig, irriterend, sensitiserend, carcinogeen</li>
        <li><strong>Milieugevaren</strong> — Aquatisch giftig, ozonlaag beschadigend</li>
    </ul>
    <p><strong>GHS-pictogrammen</strong> zijn de standaard waarschuwingssymbolen op elke verpakking.</p>
</div>

<div class="el-section">
    <h3>3. Veiligheidsinformatieblad (VIB / SDS)</h3>
    <p>Elke gevaarlijke stof moet een up-to-date VIB hebben (REACH art. 31). Het VIB bevat 16 secties met essentiële informatie over gevaren, eerste hulp, brandbestrijding en persoonlijke beschermingsmiddelen.</p>
    <div class="el-highlight">
        <strong>Tip:</strong> Controleer of VIB's niet ouder dan 2 jaar zijn. Bij twijfel: raadpleeg leverancier.
    </div>
</div>

<div class="el-section">
    <h3>4. Risicobeheersing — De HIërarchie</h3>
    <p>Volg altijd de stop-methode bij blootstelling aan gevaarlijke stoffen:</p>
    <ol>
        <li><strong>S</strong>ubstitutie — Vervang door minder gevaarlijke stof</li>
        <li><strong>T</strong>echnische maatregelen — Afzuiging, ventilatie, gesloten systeem</li>
        <li><strong>O</strong>rganisatorische maatregelen — Beperkte blootstellingstijd, rotatie</li>
        <li><strong>P</strong>ersoonlijke beschermingsmiddelen — Handschoenen, masker, bril</li>
    </ol>
</div>

<div class="el-section">
    <h3>5. Blootstellingslimieten</h4>
    <p><strong>TGG-8u</strong> (Tijdgewogen Gemiddelde): Maximale concentratie over 8 uur. <strong>TGG-15min</strong>: Kortstondige blootstellingslimiet. Bron: SER-publicatie gezondheidswaarden.</p>
</div>

<div class="el-section">
    <h3>6. Eerste hulp bij blootstelling</h3>
    <table class="el-table">
        <tr><th>Blootstelling</th><th>Eerste actie</th></tr>
        <tr><td>Huidcontact</td><td>Direct spoelen met water (min. 15 min)</td></tr>
        <tr><td>Oogcontact</td><td>Ogen spoelen met water (min. 15 min), arts raadplegen</td></tr>
        <tr><td>Inademing</td><td>Naar frisse lucht, bij aanhoudende klachten 112 bellen</td></tr>
        <tr><td>Insllikken</td><td>Niet laten braken, direct arts raadplegen</td></tr>
    </table>
</div>
""",
    'quiz_questions': [
        {
            'id': 1,
            'question': 'Welke verordening regelt de classificatie en etikettering van gevaarlijke stoffen in de EU?',
            'options': ['REACH (EC 1907/2006)', 'CLP (EC 1272/2008)', 'Seveso III richtlijn', 'Arbobesluit'],
            'correct': 1,
            'explanation': 'De CLP-verordening (Classification, Labelling and Packaging) regelt de uniforme classificatie en etikettering.'
        },
        {
            'id': 2,
            'question': 'Wat is de juiste volgorde van risicobeheersing bij gevaarlijke stoffen?',
            'options': [
                'PBM → Technisch → Organisatorisch → Substitutie',
                'Substitutie → Technisch → Organisatorisch → PBM',
                'Technisch → Substitutie → PBM → Organisatorisch',
                'Organisatorisch → Substitutie → Technisch → PBM'
            ],
            'correct': 1,
            'explanation': 'De hiërarchie volgt het STOP-principe: Substitutie is altijd de voorkeursoptie.'
        },
        {
            'id': 3,
            'question': 'Hoe vaak moet een Veiligheidsinformatieblad (VIB) herzien worden?',
            'options': ['Elk jaar', 'Elke 2 jaar', 'Elke 5 jaar', 'Alleen bij nieuwe gevareninformatie'],
            'correct': 3,
            'explanation': 'REACH art. 31: Het VIB moet herzien worden zodra nieuwe gevareninformatie beschikbaar komt.'
        },
        {
            'id': 4,
            'question': 'Wat betekent TGG-8u?',
            'options': [
                'Totaal Gevaarlijk Gebied — 8 uur blootstelling',
                'Tijdgewogen Gemiddelde concentratie over een 8-urige werkdag',
                'Technische Grens Gebied — 8 uur limiet',
                'Toegestane Gevaarlijke Grens — 8 uur'
            ],
            'correct': 1,
            'explanation': 'TGG-8u is het tijdgewogen gemiddelde van de concentratie in de lucht over een 8-urige werkdag.'
        },
        {
            'id': 5,
            'question': 'Bij huidcontact met een irriterende vloeistof is de eerste actie:',
            'options': [
                'PBM aantrekken en doorgaan',
                'Direct spoelen met water gedurende minimaal 15 minuten',
                'Een verband aanleggen',
                'Wachten tot de irritatie verdwijnt'
            ],
            'correct': 1,
            'explanation': 'Bij huidcontact altijd direct minimaal 15 minuten spoelen met ruim water.'
        },
        {
            'id': 6,
            'question': 'Welke GHS-pictogrammen behoren tot de fysieke gevaren?',
            'options': [
                'Doodshoofd, gezondheidsgevaar, uitroepteken',
                'Vlam, exploderende bom, vlam boven cirkel',
                'Boom/vis, tankwagen, gloeilamp',
                'Corrosie, gasfles, omgevingsgevaar'
            ],
            'correct': 1,
            'explanation': 'Fysieke gevaren omvatten brandbaar (vlam), ontplofbaar (bom) en oxiderend (vlam boven cirkel).'
        },
    ]
}


def seed_demo_module():
    """Create demo module if it doesn't exist."""
    conn = get_db()
    existing = conn.execute("SELECT id FROM elearning_modules WHERE title = ?", (DEMO_MODULE['title'],)).fetchone()
    if not existing:
        conn.execute(
            """INSERT INTO elearning_modules (title, category, content_html, quiz_questions, passing_score, duration_minutes, status, created_by)
               VALUES (?, ?, ?, ?, ?, ?, 'published', 'system')""",
            (DEMO_MODULE['title'], DEMO_MODULE['category'], DEMO_MODULE['content_html'],
             json.dumps(DEMO_MODULE['quiz_questions']), DEMO_MODULE['passing_score'],
             DEMO_MODULE['duration_minutes'])
        )
        conn.commit()
        print("✅ Demo module 'Gevaarlijke Stoffen' aangemaakt")
    else:
        print("ℹ️ Demo module bestaat al")
    conn.close()


# ─── Test Runner ─────────────────────────────────────────────────────────────

def run_tests():
    """Run self-tests."""
    import sys
    passed = 0
    failed = 0
    total = 0

    def test(name, fn):
        nonlocal passed, failed, total
        total += 1
        try:
            fn()
            print(f"  ✅ {name}")
            passed += 1
        except Exception as e:
            print(f"  ❌ {name}: {e}")
            failed += 1

    print("\n🧪 eLearning Module Tests\n")

    test("DB tables created", lambda: (
        init_elearning_tables(),
        get_db().execute("SELECT count(*) FROM elearning_modules").fetchone()
    ))

    test("Create module", lambda: (
        _ := create_module("Test Module", "test", "<p>Test</p>",
                           [{"question": "Q?", "options": ["A","B"], "correct": 0}]),
        _ is not None or True
    ))

    test("Get modules", lambda: (
        modules := get_modules(),
        len(modules) >= 1 or _assert(False, "No modules found")
    ))

    test("Get single module", lambda: (
        m := get_modules("test"),
        len(m) >= 1 or _assert(False, "No test modules")
    ))

    test("Update module", lambda: (
        m := get_modules("test")[0],
        update_module(m['id'], title="Test Module Updated"),
        get_module(m['id'])['title'] == "Test Module Updated" or _assert(False, "Title not updated")
    ))

    test("Assign module", lambda: (
        m := get_modules("test")[0],
        assign_module(m['id'], employee_ids=[], due_days=14),
        True
    ))

    test("Advisory scan", lambda: (
        run_advisory_scan() is not None or True
    ))

    test("Seed demo module", lambda: seed_demo_module())

    # Cleanup test module
    test("Delete module", lambda: (
        m := get_modules("test")[0],
        delete_module(m['id']),
        get_module(m['id']) is None or _assert(False, "Module not deleted")
    ))

    print(f"\n📊 Resultaat: {passed}/{total} geslaagd, {failed} gefaald\n")
    return failed == 0


def _assert(cond, msg):
    if not cond:
        raise AssertionError(msg)


if __name__ == '__main__':
    init_elearning_tables()
    seed_demo_module()
    run_tests()