# ============================================================
# MODULE 5: Kwalificaties, Competenties & Auditering
# Phoenix Metals HSEQ VBS — module_training_audits.py
# ============================================================

import os
import sqlite3
from datetime import datetime, timedelta
from flask import jsonify, request, render_template
from search import get_db


# ─── Helpers ───────────────────────────────────────────────────────────────

def _d(row):
    return dict(row) if row else None


def _cert_status(expiry_date):
    """Return status based on expiry_date vs today."""
    if not expiry_date:
        return 'unknown'
    today = datetime.now().date()
    exp = datetime.strptime(expiry_date, '%Y-%m-%d').date() if isinstance(expiry_date, str) else expiry_date
    if exp < today:
        return 'expired'
    if exp < today + timedelta(days=30):
        return 'expiring_soon'
    return 'active'


def _days_until(expiry_date):
    if not expiry_date:
        return None
    today = datetime.now().date()
    exp = datetime.strptime(expiry_date, '%Y-%m-%d').date() if isinstance(expiry_date, str) else expiry_date
    return (exp - today).days


# ─── 1. Dashboard Stats ───────────────────────────────────────────────────

def _training_dashboard():
    db = get_db()
    try:
        te = db.execute("SELECT COUNT(*) as c FROM employees").fetchone()['c']
        ae = db.execute("SELECT COUNT(*) as c FROM employees WHERE status='active'").fetchone()['c']
        tc = db.execute("SELECT COUNT(*) as c FROM m5_contractors").fetchone()['c']
        ace = db.execute("SELECT COUNT(*) as c FROM contractor_employees WHERE status='active'").fetchone()['c']
        tp = db.execute("SELECT COUNT(*) as c FROM training_programs").fetchone()['c']
        mp = db.execute("SELECT COUNT(*) as c FROM training_programs WHERE is_mandatory=1").fetchone()['c']
        tcert = db.execute("SELECT COUNT(*) as c FROM certifications").fetchone()['c']

        today_str = datetime.now().strftime('%Y-%m-%d')
        future = (datetime.now() + timedelta(days=30)).strftime('%Y-%m-%d')
        active_c = db.execute("SELECT COUNT(*) as c FROM certifications WHERE expiry_date >= ?", (today_str,)).fetchone()['c']
        expired_c = db.execute("SELECT COUNT(*) as c FROM certifications WHERE expiry_date < ?", (today_str,)).fetchone()['c']
        expiring_c = db.execute("SELECT COUNT(*) as c FROM certifications WHERE expiry_date >= ? AND expiry_date < ?", (today_str, future)).fetchone()['c']

        # Compliance: required certs via matrix
        total_req = db.execute("""
            SELECT COUNT(*) as c FROM training_matrix tm
            JOIN employees e ON e.role_id = tm.role_id AND e.status='active'
        """).fetchone()['c']
        compliant_req = db.execute("""
            SELECT COUNT(*) as c FROM training_matrix tm
            JOIN employees e ON e.role_id = tm.role_id AND e.status='active'
            JOIN certifications c ON c.training_program_id = tm.training_program_id
                AND c.employee_id = e.id AND c.expiry_date >= date('now')
        """).fetchone()['c']
        comp_pct = round((compliant_req / total_req * 100), 1) if total_req > 0 else 0

        ta = db.execute("SELECT COUNT(*) as c FROM audit_schedules").fetchone()['c']
        ac = db.execute("SELECT COUNT(*) as c FROM audit_schedules WHERE audit_status='completed'").fetchone()['c']
        ao = db.execute("SELECT COUNT(*) as c FROM audit_schedules WHERE audit_status='scheduled' AND scheduled_date < date('now')").fetchone()['c']
        of_ = db.execute("SELECT COUNT(*) as c FROM audit_findings WHERE status != 'closed'").fetchone()['c']
        cf = db.execute("SELECT COUNT(*) as c FROM audit_findings WHERE status != 'closed' AND risk_rating='critical'").fetchone()['c']

        return jsonify({
            'total_employees': te, 'active_employees': ae,
            'total_contractors': tc, 'active_contractor_employees': ace,
            'total_training_programs': tp, 'mandatory_programs': mp,
            'total_certifications': tcert, 'active_certs': active_c,
            'expired_certs': expired_c, 'expiring_30_days': expiring_c,
            'compliance_percentage': comp_pct,
            'total_audits_planned': ta, 'audits_completed': ac, 'audits_overdue': ao,
            'open_audit_findings': of_, 'critical_findings': cf
        })
    finally:
        db.close()


# ─── 2. Employees CRUD ───────────────────────────────────────────────────

def _employees_list():
    db = get_db()
    try:
        q = 'SELECT e.*, r.role_name FROM employees e LEFT JOIN roles r ON e.role_id = r.id WHERE 1=1'
        p = []
        if request.args.get('role_id'):
            q += ' AND e.role_id = ?'; p.append(request.args.get('role_id'))
        if request.args.get('department'):
            q += ' AND e.department = ?'; p.append(request.args.get('department'))
        if request.args.get('status'):
            q += ' AND e.status = ?'; p.append(request.args.get('status'))
        q += ' ORDER BY e.last_name, e.first_name LIMIT 200'
        return jsonify([_d(r) for r in db.execute(q, p).fetchall()])
    finally:
        db.close()


def _employee_detail(eid):
    db = get_db()
    try:
        emp = db.execute('SELECT e.*, r.role_name FROM employees e LEFT JOIN roles r ON e.role_id = r.id WHERE e.id = ?', (eid,)).fetchone()
        if not emp:
            return jsonify({'error': 'Not found'}), 404
        certs = db.execute('SELECT c.*, tp.program_name, tp.program_code FROM certifications c JOIN training_programs tp ON c.training_program_id = tp.id WHERE c.employee_id = ? ORDER BY c.expiry_date', (eid,)).fetchall()
        return jsonify({'employee': _d(emp), 'certifications': [_d(c) for c in certs]})
    finally:
        db.close()


def _employee_create():
    db = get_db()
    try:
        data = request.get_json()
        if not data:
            return jsonify({'error': 'No data'}), 400
        db.execute('''INSERT INTO employees (employee_number,first_name,last_name,email,phone,hire_date,role_id,department,location,status)
            VALUES (?,?,?,?,?,?,?,?,?)''', (
            data.get('employee_number'), data.get('first_name'), data.get('last_name'),
            data.get('email'), data.get('phone'), data.get('hire_date'),
            data.get('role_id'), data.get('department'), data.get('location'),
            data.get('status', 'active')
        ))
        db.commit()
        return jsonify({'status': 'created'}), 201
    finally:
        db.close()


def _employee_update(eid):
    db = get_db()
    try:
        existing = db.execute('SELECT * FROM employees WHERE id = ?', (eid,)).fetchone()
        if not existing:
            return jsonify({'error': 'Not found'}), 404
        data = request.get_json()
        if not data:
            return jsonify({'error': 'No data'}), 400
        fields = ['employee_number','first_name','last_name','email','phone','hire_date','role_id','department','location','status']
        sets, vals = [], []
        for f in fields:
            if data.get(f) is not None:
                sets.append(f'{f} = ?'); vals.append(data.get(f))
        if sets:
            vals.append(eid)
            db.execute(f'UPDATE employees SET {", ".join(sets)} WHERE id = ?', vals)
            db.commit()
        return jsonify({'status': 'updated'})
    finally:
        db.close()


# ─── 3. Roles & Training Matrix ─────────────────────────────────────────

def _roles_list():
    db = get_db()
    try:
        return jsonify([_d(r) for r in db.execute('SELECT * FROM roles ORDER BY role_name').fetchall()])
    finally:
        db.close()


def _programs_list():
    db = get_db()
    try:
        return jsonify([_d(r) for r in db.execute('SELECT * FROM training_programs ORDER BY program_code').fetchall()])
    finally:
        db.close()


def _matrix_list():
    db = get_db()
    try:
        rows = db.execute('''
            SELECT tm.*, r.role_name, tp.program_name, tp.program_code, tp.category, tp.is_mandatory
            FROM training_matrix tm
            JOIN roles r ON tm.role_id = r.id
            JOIN training_programs tp ON tm.training_program_id = tp.id
            ORDER BY r.role_name, tp.program_name
        ''').fetchall()
        return jsonify([_d(r) for r in rows])
    finally:
        db.close()


def _matrix_role(role_id):
    db = get_db()
    try:
        rows = db.execute('''
            SELECT tm.*, r.role_name, tp.program_name, tp.program_code, tp.category, tp.is_mandatory, tp.valid_period_days
            FROM training_matrix tm
            JOIN roles r ON tm.role_id = r.id
            JOIN training_programs tp ON tm.training_program_id = tp.id
            WHERE tm.role_id = ?
            ORDER BY tp.program_name
        ''', (role_id,)).fetchall()
        return jsonify([_d(r) for r in rows])
    finally:
        db.close()


# ─── 4. Certifications CRUD ─────────────────────────────────────────────

def _certifications_list():
    db = get_db()
    try:
        q = 'SELECT c.*, tp.program_name, tp.program_code FROM certifications c JOIN training_programs tp ON c.training_program_id = tp.id WHERE 1=1'
        p = []
        if request.args.get('employee_id'):
            q += ' AND c.employee_id = ?'; p.append(request.args.get('employee_id'))
        if request.args.get('status'):
            q += ' AND c.status = ?'; p.append(request.args.get('status'))
        if request.args.get('expiring') == '30':
            q += ' AND c.expiry_date >= date(\'now\') AND c.expiry_date < date(\'now\', \'+30 days\')'
        q += ' ORDER BY c.expiry_date ASC LIMIT 300'
        return jsonify([_d(r) for r in db.execute(q, p).fetchall()])
    finally:
        db.close()


def _certification_create():
    db = get_db()
    try:
        data = request.get_json()
        if not data:
            return jsonify({'error': 'No data'}), 400
        # Auto-check expiry
        status = 'active'
        if data.get('expiry_date'):
            status = _cert_status(data['expiry_date'])
        db.execute('''INSERT INTO certifications (employee_id,contractor_employee_id,training_program_id,trainer_name,
            training_date,expiry_date,certificate_number,file_path,verification_method,status,renewal_date,score)
            VALUES (?,?,?,?,?,?,?,?,?,?,?,?)''', (
            data.get('employee_id'), data.get('contractor_employee_id'), data.get('training_program_id'),
            data.get('trainer_name'), data.get('training_date'), data.get('expiry_date'),
            data.get('certificate_number'), data.get('file_path'), data.get('verification_method'),
            status, data.get('renewal_date'), data.get('score')
        ))
        db.commit()
        return jsonify({'status': 'created', 'cert_status': status}), 201
    finally:
        db.close()


def _certification_update(cid):
    db = get_db()
    try:
        existing = db.execute('SELECT * FROM certifications WHERE id = ?', (cid,)).fetchone()
        if not existing:
            return jsonify({'error': 'Not found'}), 404
        data = request.get_json()
        if not data:
            return jsonify({'error': 'No data'}), 400
        fields = ['employee_id','contractor_employee_id','training_program_id','trainer_name',
                   'training_date','expiry_date','certificate_number','file_path','verification_method',
                   'renewal_date','score']
        sets, vals = [], []
        for f in fields:
            if data.get(f) is not None:
                sets.append(f'{f} = ?'); vals.append(data.get(f))
        if 'status' in data:
            sets.append('status = ?'); vals.append(data['status'])
        elif data.get('expiry_date'):
            sets.append('status = ?'); vals.append(_cert_status(data['expiry_date']))
        if sets:
            vals.append(cid)
            db.execute(f'UPDATE certifications SET {", ".join(sets)} WHERE id = ?', vals)
            db.commit()
        return jsonify({'status': 'updated'})
    finally:
        db.close()


def _employee_status(eid):
    """CRITICAL: Full compliance overview for an employee."""
    db = get_db()
    try:
        emp = db.execute('SELECT e.*, r.role_name, r.safety_critical FROM employees e LEFT JOIN roles r ON e.role_id = r.id WHERE e.id = ?', (eid,)).fetchone()
        if not emp:
            return jsonify({'error': 'Not found'}), 404
        if not emp['role_id']:
            return jsonify({'employee': _d(emp), 'training_status': [], 'compliance': 'unknown'})

        # Get required trainings for this role
        required = db.execute('''
            SELECT tm.*, tp.program_name, tp.program_code, tp.category, tp.is_mandatory, tp.valid_period_days, tp.pbzo_requirement_code
            FROM training_matrix tm
            JOIN training_programs tp ON tm.training_program_id = tp.id
            WHERE tm.role_id = ? AND tm.is_required = 1
            ORDER BY tp.program_name
        ''', (emp['role_id'],)).fetchall()

        results = []
        for req in required:
            cert = db.execute('''
                SELECT * FROM certifications
                WHERE employee_id = ? AND training_program_id = ?
                ORDER BY expiry_date DESC LIMIT 1
            ''', (eid, req['training_program_id'])).fetchone()

            if cert:
                status = _cert_status(cert['expiry_date'])
                results.append({
                    'program_name': req['program_name'],
                    'program_code': req['program_code'],
                    'category': req['category'],
                    'required': True,
                    'certified': True,
                    'status': status,
                    'cert_id': cert['id'],
                    'expiry_date': cert['expiry_date'],
                    'days_remaining': _days_until(cert['expiry_date']),
                    'pbzo_reference': req['pbzo_requirement_code']
                })
            else:
                results.append({
                    'program_name': req['program_name'],
                    'program_code': req['program_code'],
                    'category': req['category'],
                    'required': True,
                    'certified': False,
                    'status': 'missing',
                    'cert_id': None,
                    'expiry_date': None,
                    'days_remaining': None,
                    'pbzo_reference': req['pbzo_requirement_code']
                })

        compliant = sum(1 for r in results if r['status'] == 'active')
        total = len(results)
        comp_pct = round((compliant / total * 100), 1) if total > 0 else 100
        return jsonify({
            'employee': _d(emp),
            'training_status': results,
            'compliant_count': compliant,
            'total_required': total,
            'compliance_percentage': comp_pct,
            'compliance': 'compliant' if comp_pct >= 95 else 'at_risk' if comp_pct >= 70 else 'non_compliant'
        })
    finally:
        db.close()


# ─── 5. Contractors ─────────────────────────────────────────────────────

def _contractors_list():
    db = get_db()
    try:
        q = 'SELECT * FROM m5_contractors WHERE 1=1'
        p = []
        if request.args.get('status'):
            q += ' AND status = ?'; p.append(request.args.get('status'))
        q += ' ORDER BY company_name'
        return jsonify([_d(r) for r in db.execute(q, p).fetchall()])
    finally:
        db.close()


def _contractor_create():
    db = get_db()
    try:
        data = request.get_json()
        if not data:
            return jsonify({'error': 'No data'}), 400
        db.execute('''INSERT INTO m5_contractors (company_name,kvk_number,contact_person,email,phone,trade,
            vca_level,vca_expiry_date,insurance_valid_until,safety_file_approved,risk_assessment_approved,
            hfa_validated,pre_qualified,rating,status,blacklist_reason)
            VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)''', (
            data.get('company_name'), data.get('kvk_number'), data.get('contact_person'),
            data.get('email'), data.get('phone'), data.get('trade'), data.get('vca_level'),
            data.get('vca_expiry_date'), data.get('insurance_valid_until'),
            data.get('safety_file_approved', 0), data.get('risk_assessment_approved', 0),
            data.get('hfa_validated', 0), data.get('pre_qualified', 0),
            data.get('rating'), data.get('status', 'active'), data.get('blacklist_reason')
        ))
        db.commit()
        return jsonify({'status': 'created'}), 201
    finally:
        db.close()


def _contractor_update(cid):
    db = get_db()
    try:
        existing = db.execute('SELECT * FROM m5_contractors WHERE id = ?', (cid,)).fetchone()
        if not existing:
            return jsonify({'error': 'Not found'}), 404
        data = request.get_json()
        if not data:
            return jsonify({'error': 'No data'}), 400
        fields = ['company_name','kvk_number','contact_person','email','phone','trade',
                   'vca_level','vca_expiry_date','insurance_valid_until','safety_file_approved',
                   'risk_assessment_approved','hfa_validated','pre_qualified','rating','status','blacklist_reason']
        sets, vals = [], []
        for f in fields:
            if data.get(f) is not None:
                sets.append(f'{f} = ?'); vals.append(data.get(f))
        if sets:
            vals.append(cid)
            db.execute(f'UPDATE m5_contractors SET {", ".join(sets)} WHERE id = ?', vals)
            db.commit()
        return jsonify({'status': 'updated'})
    finally:
        db.close()


def _contractor_employees_list():
    db = get_db()
    try:
        q = '''SELECT ce.*, mc.company_name, r.role_name
               FROM contractor_employees ce
               LEFT JOIN m5_contractors mc ON ce.contractor_id = mc.id
               LEFT JOIN roles r ON ce.role_id = r.id WHERE 1=1'''
        p = []
        if request.args.get('contractor_id'):
            q += ' AND ce.contractor_id = ?'; p.append(request.args.get('contractor_id'))
        if request.args.get('status'):
            q += ' AND ce.status = ?'; p.append(request.args.get('status'))
        q += ' ORDER BY ce.last_name, ce.first_name'
        return jsonify([_d(r) for r in db.execute(q, p).fetchall()])
    finally:
        db.close()


def _contractor_employee_create():
    db = get_db()
    try:
        data = request.get_json()
        if not data:
            return jsonify({'error': 'No data'}), 400
        db.execute('''INSERT INTO contractor_employees (contractor_id,first_name,last_name,email,phone,
            role_id,vca_certificate_number,vca_expiry_date,site_induction_date,site_induction_expiry,
            hf_training_date,hf_training_expiry,status,denial_reason,badge_number)
            VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)''', (
            data.get('contractor_id'), data.get('first_name'), data.get('last_name'),
            data.get('email'), data.get('phone'), data.get('role_id'),
            data.get('vca_certificate_number'), data.get('vca_expiry_date'),
            data.get('site_induction_date'), data.get('site_induction_expiry'),
            data.get('hf_training_date'), data.get('hf_training_expiry'),
            data.get('status', 'active'), data.get('denial_reason'), data.get('badge_number')
        ))
        db.commit()
        return jsonify({'status': 'created'}), 201
    finally:
        db.close()


def _contractor_employee_update(ceid):
    db = get_db()
    try:
        existing = db.execute('SELECT * FROM contractor_employees WHERE id = ?', (ceid,)).fetchone()
        if not existing:
            return jsonify({'error': 'Not found'}), 404
        data = request.get_json()
        if not data:
            return jsonify({'error': 'No data'}), 400
        fields = ['contractor_id','first_name','last_name','email','phone','role_id',
                   'vca_certificate_number','vca_expiry_date','site_induction_date','site_induction_expiry',
                   'hf_training_date','hf_training_expiry','status','denial_reason','badge_number']
        sets, vals = [], []
        for f in fields:
            if data.get(f) is not None:
                sets.append(f'{f} = ?'); vals.append(data.get(f))
        if sets:
            vals.append(ceid)
            db.execute(f'UPDATE contractor_employees SET {", ".join(sets)} WHERE id = ?', vals)
            db.commit()
        return jsonify({'status': 'updated'})
    finally:
        db.close()


def _contractor_employee_status(ceid):
    """Compliance overview for a contractor employee."""
    db = get_db()
    try:
        ce = db.execute('''SELECT ce.*, mc.company_name, r.role_name, r.safety_critical
                           FROM contractor_employees ce
                           LEFT JOIN m5_contractors mc ON ce.contractor_id = mc.id
                           LEFT JOIN roles r ON ce.role_id = r.id
                           WHERE ce.id = ?''', (ceid,)).fetchone()
        if not ce:
            return jsonify({'error': 'Not found'}), 404

        results = []
        if ce['role_id']:
            required = db.execute('''
                SELECT tm.*, tp.program_name, tp.program_code, tp.category, tp.is_mandatory, tp.pbzo_requirement_code
                FROM training_matrix tm JOIN training_programs tp ON tm.training_program_id = tp.id
                WHERE tm.role_id = ? AND tm.is_required = 1 AND tm.applies_to_contractors = 1
                ORDER BY tp.program_name
            ''', (ce['role_id'],)).fetchall()

            for req in required:
                cert = db.execute('''
                    SELECT * FROM certifications
                    WHERE contractor_employee_id = ? AND training_program_id = ?
                    ORDER BY expiry_date DESC LIMIT 1
                ''', (ceid, req['training_program_id'])).fetchone()
                if cert:
                    status = _cert_status(cert['expiry_date'])
                    results.append({
                        'program_name': req['program_name'], 'program_code': req['program_code'],
                        'required': True, 'certified': True, 'status': status,
                        'cert_id': cert['id'], 'expiry_date': cert['expiry_date'],
                        'days_remaining': _days_until(cert['expiry_date']),
                        'pbzo_reference': req['pbzo_requirement_code']
                    })
                else:
                    results.append({
                        'program_name': req['program_name'], 'program_code': req['program_code'],
                        'required': True, 'certified': False, 'status': 'missing',
                        'cert_id': None, 'expiry_date': None, 'days_remaining': None,
                        'pbzo_reference': req['pbzo_requirement_code']
                    })

        # Check VCA from contractor_employees table directly
        vca_status = 'missing'
        if ce['vca_expiry_date']:
            vca_status = _cert_status(ce['vca_expiry_date'])

        # Check HF training
        hf_status = 'missing'
        if ce['hf_training_expiry']:
            hf_status = _cert_status(ce['hf_training_expiry'])

        # Check site induction
        induction_status = 'missing'
        if ce['site_induction_expiry']:
            induction_status = _cert_status(ce['site_induction_expiry'])

        # Check HFA validation
        hfa_ok = bool(ce['hfa_validated']) if 'hfa_validated' in (ce.keys() if hasattr(ce, 'keys') else []) else None
        # Try from contractor
        contractor = db.execute('SELECT hfa_validated FROM m5_contractors WHERE id = ?', (ce['contractor_id'],)).fetchone()
        if contractor:
            hfa_ok = bool(contractor['hfa_validated'])

        compliant = sum(1 for r in results if r['status'] == 'active')
        total = len(results)
        comp_pct = round((compliant / total * 100), 1) if total > 0 else 100

        return jsonify({
            'contractor_employee': _d(ce),
            'training_status': results,
            'vca_status': vca_status,
            'hf_training_status': hf_status,
            'site_induction_status': induction_status,
            'hfa_validated': hfa_ok,
            'compliant_count': compliant,
            'total_required': total,
            'compliance_percentage': comp_pct,
            'compliance': 'compliant' if comp_pct >= 95 and vca_status == 'active' else 'at_risk' if comp_pct >= 70 else 'non_compliant'
        })
    finally:
        db.close()


# ─── 6. Training Gap Analyzer (AI) ──────────────────────────────────────

def _gap_analysis():
    db = get_db()
    try:
        today = datetime.now().date()
        today_str = today.strftime('%Y-%m-%d')
        future30 = (today + timedelta(days=30)).strftime('%Y-%m-%d')

        critical_gaps = []
        expiring_soon_list = []
        contractor_alerts = []
        training_schedule = []
        total_personnel = 0
        fully_compliant = 0
        with_gaps = 0
        total_critical = 0
        total_expiring = 0

        # ── Employees ──
        employees = db.execute('''
            SELECT e.id, e.employee_number, e.first_name, e.last_name, e.role_id, e.status,
                   r.role_name, r.safety_critical, r.department, r.plant_area
            FROM employees e LEFT JOIN roles r ON e.role_id = r.id WHERE e.status = 'active'
        ''').fetchall()

        for emp in employees:
            person_name = f"{emp['first_name']} {emp['last_name']}"
            total_personnel += 1
            person_compliant = True

            if not emp['role_id']:
                continue

            required = db.execute('''
                SELECT tm.*, tp.program_name, tp.program_code, tp.is_mandatory, tp.category,
                       tp.pbzo_requirement_code, tp.regulatory_reference
                FROM training_matrix tm JOIN training_programs tp ON tm.training_program_id = tp.id
                WHERE tm.role_id = ? AND tm.is_required = 1
            ''', (emp['role_id'],)).fetchall()

            for req in required:
                cert = db.execute('''
                    SELECT * FROM certifications
                    WHERE employee_id = ? AND training_program_id = ?
                    ORDER BY expiry_date DESC LIMIT 1
                ''', (emp['id'], req['training_program_id'])).fetchone()

                prog_name = f"{req['program_name']} ({req['program_code']})"
                dept = emp['department'] or ''
                plant = emp['plant_area'] or ''

                if cert:
                    status = _cert_status(cert['expiry_date'])
                    if status == 'expired':
                        person_compliant = False
                        is_brzo = bool(req['pbzo_requirement_code'])
                        priority = 'critical' if is_brzo else 'high'
                        total_critical += 1
                        risk = f"{'CRITICAL — BRZO/PBZO vereiste' if is_brzo else 'HOOG — '}Verlopen training voor {prog_name}. Werkzaamheden niet toegestaan zonder geldige certificering."
                        if req['program_code'] in ('TRN-HF-01',) or 'HF' in req['program_name'].upper():
                            risk = f"CRITICAL — HF blootstellingsrisico in {plant}. Medewerker mag niet werken in HF-zone zonder geldige training."
                            priority = 'critical'
                        if req['program_code'] in ('TRN-EX-01',) or 'ATEX' in req['program_name'].upper():
                            risk = f"CRITICAL — ATEX/EX-zone vereiste in {plant}. Medewerker mag geen werkzaamheden uitvoeren in EX-zone."
                            priority = 'critical'
                        if 'VCA' in req['program_name'].upper() and emp['safety_critical']:
                            risk = f"CRITICAL — VCA verlopen voor veiligheidskritieke functie ({emp['role_name']}). Direct her certificeren."
                            priority = 'critical'

                        critical_gaps.append({
                            'person': person_name, 'employee_number': emp['employee_number'],
                            'role': emp['role_name'], 'missing_training': prog_name,
                            'gap_type': 'expired', 'previous_expiry': cert['expiry_date'],
                            'risk': risk, 'priority': priority,
                            'action': f"Ontzeg onmiddellijk toegang. Plan herhalingstraining zo spoedig mogelijk.{' Binnen 48u i.v.m. BRZO.' if is_brzo else ''}",
                            'pbzo_reference': req['pbzo_requirement_code']
                        })

                    elif status == 'expiring_soon':
                        days = _days_until(cert['expiry_date'])
                        total_expiring += 1
                        expiring_soon_list.append({
                            'person': person_name, 'employee_number': emp['employee_number'],
                            'role': emp['role_name'], 'training': prog_name,
                            'expires': cert['expiry_date'], 'days_remaining': days,
                            'risk': f"WAARSCHUWING — {prog_name} verloopt over {days} dagen.",
                            'action': f"Plan herhalingstraining vóór {cert['expiry_date']}"
                        })
                else:
                    # Missing certification
                    person_compliant = False
                    is_mandatory = bool(req['is_mandatory'])
                    priority = 'high' if is_mandatory else 'medium'

                    # Special flags
                    if req['program_code'] in ('TRN-EX-01',) or 'ATEX' in req['program_name'].upper():
                        priority = 'critical'
                        total_critical += 1
                        risk = f"CRITICAL — EX-zone training ONTBREEKT voor {emp['role_name']}. Geen toegang tot ATEX-zone toegestaan."
                        critical_gaps.append({
                            'person': person_name, 'employee_number': emp['employee_number'],
                            'role': emp['role_name'], 'missing_training': prog_name,
                            'gap_type': 'missing', 'previous_expiry': None,
                            'risk': risk, 'priority': 'critical',
                            'action': "Ontzeg toegang tot EX-zones. Plan training vóór start werkzaamheden.",
                            'pbzo_reference': req['pbzo_requirement_code']
                        })
                        continue
                    if req['program_code'] in ('TRN-HF-01',) or 'HF' in req['program_name'].upper():
                        priority = 'critical'
                        total_critical += 1
                        critical_gaps.append({
                            'person': person_name, 'employee_number': emp['employee_number'],
                            'role': emp['role_name'], 'missing_training': prog_name,
                            'gap_type': 'missing', 'previous_expiry': None,
                            'risk': f"CRITICAL — HF training ONTBREEKT. Geen werkzaamheden in HF-zone toegestaan.",
                            'priority': 'critical',
                            'action': "Ontzeg HF-zone toegang. Plan HF-bewustwordingstraining als prioriteit.",
                            'pbzo_reference': req['pbzo_requirement_code']
                        })
                        continue
                    if 'VCA' in req['program_name'].upper() and emp['safety_critical']:
                        priority = 'critical'
                        total_critical += 1

                    if priority == 'critical':
                        critical_gaps.append({
                            'person': person_name, 'employee_number': emp['employee_number'],
                            'role': emp['role_name'], 'missing_training': prog_name,
                            'gap_type': 'missing', 'previous_expiry': None,
                            'risk': f"{'CRITICAL' if priority == 'critical' else 'HOOG'} — {prog_name} ontbreekt voor {emp['role_name']}.",
                            'priority': priority,
                            'action': f"Plan {prog_name} training.",
                            'pbzo_reference': req['pbzo_requirement_code']
                        })

            if person_compliant:
                fully_compliant += 1
            else:
                with_gaps += 1

        # ── Contractor Employees ──
        contractors = db.execute('''
            SELECT ce.*, mc.company_name, mc.hfa_validated as contractor_hfa,
                   r.role_name, r.safety_critical
            FROM contractor_employees ce
            LEFT JOIN m5_contractors mc ON ce.contractor_id = mc.id
            LEFT JOIN roles r ON ce.role_id = r.id
            WHERE ce.status = 'active'
        ''').fetchall()

        for ce in contractors:
            total_personnel += 1
            person_name = f"{ce['first_name']} {ce['last_name']}"
            company = ce['company_name'] or 'Onbekend'
            ce_ok = True

            # Check VCA
            if ce['vca_expiry_date']:
                vca_st = _cert_status(ce['vca_expiry_date'])
                if vca_st == 'expired':
                    ce_ok = False
                    total_critical += 1
                    contractor_alerts.append({
                        'company': company, 'person': person_name,
                        'gap': f"VCA verlopen op {ce['vca_expiry_date']}",
                        'action': "Contractor medewerker mag geen werkzaamheden uitvoeren. Blokkeer toegang.",
                        'priority': 'critical'
                    })
            elif ce['role_id']:
                ce_ok = False
                contractor_alerts.append({
                    'company': company, 'person': person_name,
                    'gap': "VCA certificaat ontbreekt",
                    'action': "Contractor medewerker mag geen werkzaamheden uitvoeren zonder geldig VCA.",
                    'priority': 'high'
                })

            # Check HF
            if ce['hf_training_expiry']:
                hf_st = _cert_status(ce['hf_training_expiry'])
                if hf_st == 'expired':
                    ce_ok = False
                    contractor_alerts.append({
                        'company': company, 'person': person_name,
                        'gap': f"HF-training verlopen op {ce['hf_training_expiry']}",
                        'action': "Contractor medewerker mag geen werkzaamheden uitvoeren in HF-zone.",
                        'priority': 'critical'
                    })

            # Check site induction
            if ce['site_induction_expiry']:
                si_st = _cert_status(ce['site_induction_expiry'])
                if si_st in ('expired',):
                    ce_ok = False
                    contractor_alerts.append({
                        'company': company, 'person': person_name,
                        'gap': f"Site induction verlopen op {ce['site_induction_expiry']}",
                        'action': "Opnieuw site inductie afnemen vóór toelating tot het terrein.",
                        'priority': 'high'
                    })

            # HFA validation
            if not ce['contractor_hfa']:
                contractor_alerts.append({
                    'company': company, 'person': person_name,
                    'gap': "HFA (Huisvestings- en Facilitaire Audit) niet gevalideerd",
                    'action': "Laat HFA validatie uitvoeren door veiligheidsafdeling.",
                    'priority': 'medium'
                })

            # Check certifications via training_matrix
            if ce['role_id']:
                required = db.execute('''
                    SELECT tm.*, tp.program_name, tp.program_code, tp.is_mandatory, tp.pbzo_requirement_code
                    FROM training_matrix tm JOIN training_programs tp ON tm.training_program_id = tp.id
                    WHERE tm.role_id = ? AND tm.is_required = 1 AND tm.applies_to_contractors = 1
                ''', (ce['role_id'],)).fetchall()
                for req in required:
                    cert = db.execute('''
                        SELECT * FROM certifications
                        WHERE contractor_employee_id = ? AND training_program_id = ?
                        ORDER BY expiry_date DESC LIMIT 1
                    ''', (ce['id'], req['training_program_id'])).fetchone()
                    if cert:
                        st = _cert_status(cert['expiry_date'])
                        if st == 'expired':
                            ce_ok = False
                            total_critical += 1
                            contractor_alerts.append({
                                'company': company, 'person': person_name,
                                'gap': f"{req['program_name']} verlopen op {cert['expiry_date']}",
                                'action': "Contractor medewerker mag niet werken. Blokkeer toegang.",
                                'priority': 'critical'
                            })
                    else:
                        if req['is_mandatory']:
                            ce_ok = False

            if ce_ok:
                fully_compliant += 1
            else:
                with_gaps += 1

        # Build training schedule suggestion
        # Group critical gaps by training
        by_training = {}
        for g in critical_gaps:
            tn = g['missing_training'].split(' (')[0] if ' (' in g['missing_training'] else g['missing_training']
            if tn not in by_training:
                by_training[tn] = {'candidates': [], 'priority': g.get('priority', 'high')}
            by_training[tn]['candidates'].append(g['person'])

        for tn, info in by_training.items():
            if info['priority'] == 'critical':
                training_schedule.append({'training': tn, 'urgent': 'critical', 'candidates': info['candidates'], 'suggested_date': 'binnen 48u'})
            else:
                training_schedule.append({'training': tn, 'urgent': 'high', 'candidates': info['candidates'], 'suggested_date': 'deze week'})

        for e in expiring_soon_list[:5]:
            tn = e['training'].split(' (')[0] if ' (' in e['training'] else e['training']
            if tn not in by_training:
                training_schedule.append({'training': tn, 'urgent': 'medium', 'candidates': [e['person']], 'suggested_date': f"vóór {e['expires']}"})

        comp_pct = round((fully_compliant / total_personnel * 100), 1) if total_personnel > 0 else 100

        # AI recommendation
        parts = []
        if total_critical > 0:
            parts.append(f"🚨 KRITIEK: {total_critical} kritieke training gaps geconstateerd. Directe actie vereist.")
        if total_expiring > 0:
            parts.append(f"⚠️ {total_expiring} certificaten verlopen binnen 30 dagen — tijdig plannen.")
        if contractor_alerts:
            crit_ca = len([a for a in contractor_alerts if a.get('priority') == 'critical'])
            if crit_ca:
                parts.append(f"🚫 {crit_ca} contractor medewerkers moeten worden geblokkeerd (ontoereikende certificering).")
        if comp_pct < 95:
            parts.append(f"Totale compliancy: {comp_pct}% (target >95% voor BRZO-audit).")
        else:
            parts.append(f"✅ Totale compliancy: {comp_pct}% — op peil.")

        recommendation = " ".join(parts)

        return jsonify({
            'analysis_date': today_str,
            'analyst': 'Kas — Training Gap Analyzer',
            'summary': {
                'total_personnel_checked': total_personnel,
                'fully_compliant': fully_compliant,
                'with_gaps': with_gaps,
                'compliance_percentage': comp_pct,
                'critical_gaps': total_critical,
                'expiring_30_days': total_expiring
            },
            'critical_gaps': critical_gaps[:20],
            'expiring_soon': expiring_soon_list[:20],
            'contractor_alerts': contractor_alerts[:20],
            'ai_recommendation': recommendation,
            'training_schedule_suggestion': training_schedule[:15]
        })
    finally:
        db.close()


# ─── 7. PtW Competency Check (Hard-Block) ───────────────────────────────

def _ptw_competency_check():
    db = get_db()
    try:
        data = request.get_json()
        if not data:
            return jsonify({'error': 'No data'}), 400

        employee_id = data.get('employee_id')
        contractor_employee_id = data.get('contractor_employee_id')
        permit_type = data.get('permit_type', 'general')
        atex_zone = data.get('atex_zone')
        permit_id = data.get('permit_id')

        # If permit_id provided, look up permit
        if permit_id:
            permit = db.execute('SELECT * FROM ptw_permits WHERE id = ?', (permit_id,)).fetchone()
            if not permit:
                return jsonify({'error': 'Permit not found'}), 404
            pd = _d(permit)
            employee_id = employee_id or pd.get('assigned_employee_id')
            contractor_employee_id = contractor_employee_id or pd.get('assigned_contractor_employee_id')
            permit_type = pd.get('permit_type', permit_type)
            atex_zone = atex_zone or pd.get('atex_zone')

        if not employee_id and not contractor_employee_id:
            return jsonify({'error': 'employee_id or contractor_employee_id required'}), 400

        # Get person info
        if employee_id:
            person = db.execute('SELECT e.*, r.role_name, r.safety_critical FROM employees e LEFT JOIN roles r ON e.role_id = r.id WHERE e.id = ?', (employee_id,)).fetchone()
            cert_where = 'employee_id'
            cert_id = employee_id
        else:
            person = db.execute('SELECT ce.*, r.role_name, r.safety_critical FROM contractor_employees ce LEFT JOIN roles r ON ce.role_id = r.id WHERE ce.id = ?', (contractor_employee_id,)).fetchone()
            cert_where = 'contractor_employee_id'
            cert_id = contractor_employee_id

        if not person:
            return jsonify({'error': 'Person not found'}), 404

        person_name = f"{person['first_name']} {person['last_name']}"
        role_name = person['role_name'] or 'Onbekend'

        # Determine required training codes for this PtW
        required_codes = ['TRN-VCA-B', 'TRN-PTW-01', 'TRN-ER-01']  # VCA, PtW procedure, Emergency

        if permit_type == 'hot_work':
            required_codes.append('TRN-EX-01')
        if permit_type == 'confined_space':
            required_codes.append('TRN-CON-01')
        if atex_zone:
            if 'TRN-EX-01' not in required_codes:
                required_codes.append('TRN-EX-01')

        # Check certifications
        checked = []
        failures = []
        all_passed = True

        for code in required_codes:
            prog = db.execute('SELECT * FROM training_programs WHERE program_code = ?', (code,)).fetchone()
            if not prog:
                continue
            cert = db.execute(f'''
                SELECT * FROM certifications
                WHERE {cert_where} = ? AND training_program_id = ?
                ORDER BY expiry_date DESC LIMIT 1
            ''', (cert_id, prog['id'])).fetchone()

            if cert:
                st = _cert_status(cert['expiry_date'])
                if st == 'active':
                    checked.append({'training': prog['program_name'], 'status': 'compliant', 'expiry': cert['expiry_date']})
                else:
                    all_passed = False
                    checked.append({'training': prog['program_name'], 'status': st.upper(), 'expiry': cert['expiry_date']})
                    failures.append(f"{prog['program_name']}: {'VERLOPEN' if st == 'expired' else st.upper()}")
            else:
                all_passed = False
                checked.append({'training': prog['program_name'], 'status': 'MISSING', 'expiry': None})
                failures.append(f"{prog['program_name']}: ONTBREEKT")

        # Update permit if permit_id was given
        if permit_id and not all_passed:
            db.execute('UPDATE ptw_permits SET competency_check_passed = 0, competency_check_details = ? WHERE id = ?',
                       (str(failures), permit_id))
            db.commit()
        elif permit_id and all_passed:
            db.execute('UPDATE ptw_permits SET competency_check_passed = 1, competency_check_details = ? WHERE id = ?',
                       ('All checks passed', permit_id))
            db.commit()

        result = {
            'permit_id': permit_id,
            'person': person_name,
            'role': role_name,
            'competency_check_passed': all_passed,
            'checked_requirements': checked
        }

        if not all_passed:
            result['blocked'] = True
            result['block_reason'] = f"VERGUNNING GEBLOKKEERD: {person_name} ({role_name}) mist vereiste competenties: [{', '.join(failures)}]. Voldoe eerst aan de trainingsvereisten."
        else:
            result['blocked'] = False
            result['message'] = "Competentie-check geslaagd. Vergunning mag worden uitgegeven."

        return jsonify(result)
    finally:
        db.close()


# ─── 8. Audits CRUD ─────────────────────────────────────────────────────

def _audits_list():
    db = get_db()
    try:
        q = 'SELECT * FROM audit_schedules WHERE 1=1'
        p = []
        if request.args.get('type'):
            q += ' AND audit_type = ?'; p.append(request.args.get('type'))
        if request.args.get('status'):
            q += ' AND audit_status = ?'; p.append(request.args.get('status'))
        if request.args.get('date_from'):
            q += ' AND scheduled_date >= ?'; p.append(request.args.get('date_from'))
        if request.args.get('date_to'):
            q += ' AND scheduled_date <= ?'; p.append(request.args.get('date_to'))
        q += ' ORDER BY scheduled_date DESC LIMIT 200'
        return jsonify([_d(r) for r in db.execute(q, p).fetchall()])
    finally:
        db.close()


def _audit_create():
    db = get_db()
    try:
        data = request.get_json()
        if not data:
            return jsonify({'error': 'No data'}), 400
        db.execute('''INSERT INTO audit_schedules (audit_type,audit_name,audit_scope,standard_referenced,
            scheduled_date,actual_start_date,actual_end_date,audit_status,audit_lead,audit_team,
            location,compliance_score,non_compliance_count,is_mandatory,next_audit_date)
            VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)''', (
            data.get('audit_type'), data.get('audit_name'), data.get('audit_scope'),
            data.get('standard_referenced'), data.get('scheduled_date'),
            data.get('actual_start_date'), data.get('actual_end_date'),
            data.get('audit_status', 'scheduled'), data.get('audit_lead'),
            data.get('audit_team'), data.get('location'),
            data.get('compliance_score'), data.get('non_compliance_count'),
            data.get('is_mandatory', 0), data.get('next_audit_date')
        ))
        db.commit()
        return jsonify({'status': 'created'}), 201
    finally:
        db.close()


def _audit_detail(aid):
    db = get_db()
    try:
        audit = db.execute('SELECT * FROM audit_schedules WHERE id = ?', (aid,)).fetchone()
        if not audit:
            return jsonify({'error': 'Not found'}), 404
        findings = db.execute('SELECT * FROM audit_findings WHERE audit_schedule_id = ? ORDER BY finding_number', (aid,)).fetchall()
        actions = []
        for f in findings:
            fa = db.execute('SELECT * FROM audit_actions WHERE audit_finding_id = ?', (f['id'],)).fetchall()
            for a in fa:
                actions.append(_d(a))
        return jsonify({'audit': _d(audit), 'findings': [_d(f) for f in findings], 'actions': actions})
    finally:
        db.close()


def _audit_update(aid):
    db = get_db()
    try:
        existing = db.execute('SELECT * FROM audit_schedules WHERE id = ?', (aid,)).fetchone()
        if not existing:
            return jsonify({'error': 'Not found'}), 404
        data = request.get_json()
        if not data:
            return jsonify({'error': 'No data'}), 400
        fields = ['audit_type','audit_name','audit_scope','standard_referenced','scheduled_date',
                   'actual_start_date','actual_end_date','audit_status','audit_lead','audit_team',
                   'location','compliance_score','non_compliance_count','is_mandatory','next_audit_date']
        sets, vals = [], []
        for f in fields:
            if data.get(f) is not None:
                sets.append(f'{f} = ?'); vals.append(data.get(f))
        if sets:
            vals.append(aid)
            db.execute(f'UPDATE audit_schedules SET {", ".join(sets)} WHERE id = ?', vals)
            db.commit()
        return jsonify({'status': 'updated'})
    finally:
        db.close()


def _audit_add_finding(aid):
    db = get_db()
    try:
        audit = db.execute('SELECT * FROM audit_schedules WHERE id = ?', (aid,)).fetchone()
        if not audit:
            return jsonify({'error': 'Audit not found'}), 404
        data = request.get_json()
        if not data:
            return jsonify({'error': 'No data'}), 400
        # Generate finding number
        count = db.execute('SELECT COUNT(*) as c FROM audit_findings WHERE audit_schedule_id = ?', (aid,)).fetchone()['c']
        finding_num = f"F-{count + 1:03d}"
        db.execute('''INSERT INTO audit_findings (audit_schedule_id,finding_number,finding_area,
            finding_description,non_compliance,risk_rating,recommendation,root_cause,vbs_element,
            verification_required,status)
            VALUES (?,?,?,?,?,?,?,?,?,?,?)''', (
            aid, finding_num, data.get('finding_area'), data.get('finding_description'),
            data.get('non_compliance'), data.get('risk_rating'), data.get('recommendation'),
            data.get('root_cause'), data.get('vbs_element'),
            data.get('verification_required', 0), data.get('status', 'open')
        ))
        db.commit()
        return jsonify({'status': 'created', 'finding_number': finding_num}), 201
    finally:
        db.close()


def _finding_update(fid):
    db = get_db()
    try:
        existing = db.execute('SELECT * FROM audit_findings WHERE id = ?', (fid,)).fetchone()
        if not existing:
            return jsonify({'error': 'Not found'}), 404
        data = request.get_json()
        if not data:
            return jsonify({'error': 'No data'}), 400
        fields = ['finding_area','finding_description','non_compliance','risk_rating','recommendation',
                   'root_cause','vbs_element','verification_required','status']
        sets, vals = [], []
        for f in fields:
            if data.get(f) is not None:
                sets.append(f'{f} = ?'); vals.append(data.get(f))
        if sets:
            vals.append(fid)
            db.execute(f'UPDATE audit_findings SET {", ".join(sets)} WHERE id = ?', vals)
            db.commit()
        return jsonify({'status': 'updated'})
    finally:
        db.close()


def _finding_add_action(fid):
    db = get_db()
    try:
        finding = db.execute('SELECT * FROM audit_findings WHERE id = ?', (fid,)).fetchone()
        if not finding:
            return jsonify({'error': 'Finding not found'}), 404
        data = request.get_json()
        if not data:
            return jsonify({'error': 'No data'}), 400
        db.execute('''INSERT INTO audit_actions (audit_finding_id,capa_action_id,action_title,
            assigned_to,assigned_role_id,due_date,status,evidence_collected,verification_status,verification_date)
            VALUES (?,?,?,?,?,?,?,?,?,?)''', (
            fid, data.get('capa_action_id'), data.get('action_title'),
            data.get('assigned_to'), data.get('assigned_role_id'), data.get('due_date'),
            data.get('status', 'open'), data.get('evidence_collected'),
            data.get('verification_status'), data.get('verification_date')
        ))
        db.commit()
        return jsonify({'status': 'created'}), 201
    finally:
        db.close()


def _action_update(actid):
    db = get_db()
    try:
        existing = db.execute('SELECT * FROM audit_actions WHERE id = ?', (actid,)).fetchone()
        if not existing:
            return jsonify({'error': 'Not found'}), 404
        data = request.get_json()
        if not data:
            return jsonify({'error': 'No data'}), 400
        fields = ['audit_finding_id','capa_action_id','action_title','assigned_to','assigned_role_id',
                   'due_date','status','evidence_collected','verification_status','verification_date']
        sets, vals = [], []
        for f in fields:
            if data.get(f) is not None:
                sets.append(f'{f} = ?'); vals.append(data.get(f))
        if sets:
            vals.append(actid)
            db.execute(f'UPDATE audit_actions SET {", ".join(sets)} WHERE id = ?', vals)
            db.commit()
        return jsonify({'status': 'updated'})
    finally:
        db.close()


# ─── Route Registration ──────────────────────────────────────────────────

def register_training_audit_routes(app, page, BASE_PATH="/hseq-dashboard"):
    # Page route
    @app.route(BASE_PATH + '/training-audits')
    def training_audits_dashboard_page():
        bp = os.environ.get('BASE_PATH', '')
        body = render_template('training_audits_dashboard.html', BASE_PATH=bp)
        return page(body, active='training-audits', page_title='Kwalificaties & Audits')

    # 1. Dashboard
    app.add_url_rule(BASE_PATH + '/api/training/dashboard', view_func=_training_dashboard, methods=['GET'])

    # 2. Employees
    app.add_url_rule(BASE_PATH + '/api/training/employees', view_func=_employees_list, methods=['GET'])
    app.add_url_rule(BASE_PATH + '/api/training/employees', view_func=_employee_create, methods=['POST'])
    app.add_url_rule(BASE_PATH + '/api/training/employees/<int:eid>', view_func=_employee_detail, methods=['GET'])
    app.add_url_rule(BASE_PATH + '/api/training/employees/<int:eid>', view_func=_employee_update, methods=['PUT'])

    # 3. Roles & Matrix
    app.add_url_rule(BASE_PATH + '/api/training/roles', view_func=_roles_list, methods=['GET'])
    app.add_url_rule(BASE_PATH + '/api/training/programs', view_func=_programs_list, methods=['GET'])
    app.add_url_rule(BASE_PATH + '/api/training/matrix', view_func=_matrix_list, methods=['GET'])
    app.add_url_rule(BASE_PATH + '/api/training/matrix/<int:role_id>', view_func=_matrix_role, methods=['GET'])

    # 4. Certifications
    app.add_url_rule(BASE_PATH + '/api/training/certifications', view_func=_certifications_list, methods=['GET'])
    app.add_url_rule(BASE_PATH + '/api/training/certifications', view_func=_certification_create, methods=['POST'])
    app.add_url_rule(BASE_PATH + '/api/training/certifications/<int:cid>', view_func=_certification_update, methods=['PUT'])
    app.add_url_rule(BASE_PATH + '/api/training/employee/<int:eid>/status', view_func=_employee_status, methods=['GET'])

    # 5. Contractors
    app.add_url_rule(BASE_PATH + '/api/training/contractors', view_func=_contractors_list, methods=['GET'])
    app.add_url_rule(BASE_PATH + '/api/training/contractors', view_func=_contractor_create, methods=['POST'])
    app.add_url_rule(BASE_PATH + '/api/training/contractors/<int:cid>', view_func=_contractor_update, methods=['PUT'])
    app.add_url_rule(BASE_PATH + '/api/training/contractor-employees', view_func=_contractor_employees_list, methods=['GET'])
    app.add_url_rule(BASE_PATH + '/api/training/contractor-employees', view_func=_contractor_employee_create, methods=['POST'])
    app.add_url_rule(BASE_PATH + '/api/training/contractor-employees/<int:ceid>', view_func=_contractor_employee_update, methods=['PUT'])
    app.add_url_rule(BASE_PATH + '/api/training/contractor-employee/<int:ceid>/status', view_func=_contractor_employee_status, methods=['GET'])

    # 6. Gap Analysis (AI)
    app.add_url_rule(BASE_PATH + '/api/training/gap-analysis', view_func=_gap_analysis, methods=['GET'])

    # 7. PtW Competency Check
    app.add_url_rule(BASE_PATH + '/api/training/ptw-competency-check', view_func=_ptw_competency_check, methods=['POST'])

    # 8. Audits
    app.add_url_rule(BASE_PATH + '/api/training/audits', view_func=_audits_list, methods=['GET'])
    app.add_url_rule(BASE_PATH + '/api/training/audits', view_func=_audit_create, methods=['POST'])
    app.add_url_rule(BASE_PATH + '/api/training/audits/<int:aid>', view_func=_audit_detail, methods=['GET'])
    app.add_url_rule(BASE_PATH + '/api/training/audits/<int:aid>', view_func=_audit_update, methods=['PUT'])
    app.add_url_rule(BASE_PATH + '/api/training/audits/<int:aid>/findings', view_func=_audit_add_finding, methods=['POST'])
    app.add_url_rule(BASE_PATH + '/api/training/findings/<int:fid>', view_func=_finding_update, methods=['PUT'])
    app.add_url_rule(BASE_PATH + '/api/training/findings/<int:fid>/actions', view_func=_finding_add_action, methods=['POST'])
    app.add_url_rule(BASE_PATH + '/api/training/actions/<int:actid>', view_func=_action_update, methods=['PUT'])