-- ============================================================
-- MODULE 5: Kwalificaties, Competenties & Auditering
-- Phoenix Metals HSEQ VBS — VBS-05 & VBS-06
-- Contractors, Training Matrix, Certifications, Audits
-- FK order: roles → employees/contractors → rest
-- ============================================================

-- ═══ ROLES (eerst aanmaken, FK target voor employees) ═══

CREATE TABLE IF NOT EXISTS roles (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    role_name TEXT UNIQUE NOT NULL,
    description TEXT,
    department TEXT,
    plant_area TEXT DEFAULT 'DMC Plant',
    safety_critical INTEGER DEFAULT 0,
    created_at TEXT DEFAULT (datetime('now')),
    updated_at TEXT DEFAULT (datetime('now'))
);

-- ═══ EMPLOYEES ═══

CREATE TABLE IF NOT EXISTS employees (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    employee_number TEXT UNIQUE NOT NULL,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    email TEXT UNIQUE,
    phone TEXT,
    hire_date TEXT NOT NULL,
    role_id INTEGER,
    department TEXT,
    location TEXT DEFAULT 'DMC Plant',
    status TEXT DEFAULT 'active' CHECK (status IN ('active','inactive','on_leave')),
    created_at TEXT DEFAULT (datetime('now')),
    updated_at TEXT DEFAULT (datetime('now')),
    FOREIGN KEY (role_id) REFERENCES roles(id)
);

-- ═══ CONTRACTORS (uitgebreid, BRZO-proof) ═══

CREATE TABLE IF NOT EXISTS m5_contractors (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    company_name TEXT NOT NULL,
    kvk_number TEXT,
    contact_person TEXT,
    email TEXT,
    phone TEXT,
    trade TEXT,
    vca_level TEXT,
    vca_expiry_date TEXT,
    insurance_company TEXT,
    insurance_policy_number TEXT,
    insurance_valid_until TEXT,
    safety_file_approved INTEGER DEFAULT 0,
    risk_assessment_approved INTEGER DEFAULT 0,
    safety_plan_approved INTEGER DEFAULT 0,
    hfa_validated INTEGER DEFAULT 0,
    pre_qualified INTEGER DEFAULT 0,
    rating TEXT DEFAULT 'medium' CHECK (rating IN ('low','medium','high','critical')),
    status TEXT DEFAULT 'active' CHECK (status IN ('active','suspended','blacklisted','inactive')),
    blacklist_reason TEXT,
    notes TEXT,
    created_at TEXT DEFAULT (datetime('now')),
    updated_at TEXT DEFAULT (datetime('now'))
);

-- ═══ CONTRACTOR EMPLOYEES ═══

CREATE TABLE IF NOT EXISTS contractor_employees (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    contractor_id INTEGER NOT NULL,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    email TEXT,
    phone TEXT,
    role_id INTEGER,
    vca_certificate_number TEXT,
    vca_expiry_date TEXT,
    site_induction_date TEXT,
    site_induction_expiry TEXT,
    hf_training_date TEXT,
    hf_training_expiry TEXT,
    status TEXT DEFAULT 'active' CHECK (status IN ('active','inactive','denied_access')),
    denial_reason TEXT,
    badge_number TEXT,
    notes TEXT,
    created_at TEXT DEFAULT (datetime('now')),
    updated_at TEXT DEFAULT (datetime('now')),
    FOREIGN KEY (contractor_id) REFERENCES m5_contractors(id),
    FOREIGN KEY (role_id) REFERENCES roles(id)
);

-- ═══ TRAINING PROGRAMS ═══

CREATE TABLE IF NOT EXISTS training_programs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    program_code TEXT UNIQUE NOT NULL,
    program_name TEXT NOT NULL,
    description TEXT,
    category TEXT NOT NULL CHECK (category IN ('general','safety','technical','emergency','special','brzo_mandatory')),
    duration_hours INTEGER,
    valid_period_days INTEGER DEFAULT 365,
    renewal_required INTEGER DEFAULT 1,
    renewal_days_before_expiry INTEGER DEFAULT 30,
    regulatory_reference TEXT,
    pbzo_requirement_code TEXT,
    is_mandatory INTEGER DEFAULT 0,
    applies_to_contractors INTEGER DEFAULT 0,
    created_at TEXT DEFAULT (datetime('now')),
    updated_at TEXT DEFAULT (datetime('now'))
);

-- ═══ TRAINING-ROLE MATRIX (waterdichte koppeling) ═══

CREATE TABLE IF NOT EXISTS training_matrix (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    role_id INTEGER NOT NULL,
    training_program_id INTEGER NOT NULL,
    role_level INTEGER DEFAULT 1,
    is_required INTEGER DEFAULT 1,
    priority TEXT DEFAULT 'medium' CHECK (priority IN ('low','medium','high','critical')),
    applies_to_contractors INTEGER DEFAULT 0,
    notes TEXT,
    created_at TEXT DEFAULT (datetime('now')),
    FOREIGN KEY (role_id) REFERENCES roles(id),
    FOREIGN KEY (training_program_id) REFERENCES training_programs(id),
    UNIQUE(role_id, training_program_id, role_level)
);

-- ═══ CERTIFICATIONS (intern personeel + contractors) ═══

CREATE TABLE IF NOT EXISTS certifications (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    employee_id INTEGER,
    contractor_employee_id INTEGER,
    training_program_id INTEGER NOT NULL,
    trainer_name TEXT,
    training_date TEXT NOT NULL,
    expiry_date TEXT NOT NULL,
    certificate_number TEXT,
    file_path TEXT,
    verification_method TEXT DEFAULT 'signed' CHECK (verification_method IN ('signed','online','manager','external')),
    status TEXT DEFAULT 'active' CHECK (status IN ('active','expired','renewed','cancelled','revoked')),
    renewal_date TEXT,
    score DECIMAL(5,2),
    notes TEXT,
    created_at TEXT DEFAULT (datetime('now')),
    updated_at TEXT DEFAULT (datetime('now')),
    FOREIGN KEY (employee_id) REFERENCES employees(id),
    FOREIGN KEY (contractor_employee_id) REFERENCES contractor_employees(id),
    FOREIGN KEY (training_program_id) REFERENCES training_programs(id),
    CHECK (employee_id IS NOT NULL OR contractor_employee_id IS NOT NULL)
);

-- ═══ AUDIT SCHEDULES ═══

CREATE TABLE IF NOT EXISTS audit_schedules (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    audit_type TEXT NOT NULL CHECK (audit_type IN ('internal','external','brzo_inspection','ilt_inspection','compliance','safety_walk')),
    audit_name TEXT NOT NULL,
    audit_scope TEXT,
    standard_referenced TEXT,
    scheduled_date TEXT NOT NULL,
    actual_start_date TEXT,
    actual_end_date TEXT,
    audit_status TEXT DEFAULT 'planned' CHECK (audit_status IN ('planned','in_progress','completed','cancelled','deferred')),
    audit_lead TEXT,
    audit_team TEXT,
    location TEXT DEFAULT 'DMC Plant',
    compliance_score DECIMAL(5,2),
    non_compliance_count INTEGER DEFAULT 0,
    is_mandatory INTEGER DEFAULT 1,
    next_audit_date TEXT,
    notes TEXT,
    created_at TEXT DEFAULT (datetime('now')),
    updated_at TEXT DEFAULT (datetime('now'))
);

-- ═══ AUDIT FINDINGS ═══

CREATE TABLE IF NOT EXISTS audit_findings (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    audit_schedule_id INTEGER NOT NULL,
    finding_number TEXT UNIQUE NOT NULL,
    finding_area TEXT NOT NULL,
    finding_description TEXT NOT NULL,
    non_compliance TEXT,
    risk_rating TEXT NOT NULL CHECK (risk_rating IN ('low','medium','high','critical')),
    recommendation TEXT,
    root_cause TEXT,
    vbs_element TEXT,
    verification_required INTEGER DEFAULT 1,
    status TEXT DEFAULT 'open' CHECK (status IN ('open','in_progress','verified','closed')),
    created_at TEXT DEFAULT (datetime('now')),
    updated_at TEXT DEFAULT (datetime('now')),
    FOREIGN KEY (audit_schedule_id) REFERENCES audit_schedules(id)
);

-- ═══ AUDIT ACTIONS → CAPA (Module 4 integratie) ═══

CREATE TABLE IF NOT EXISTS audit_actions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    audit_finding_id INTEGER NOT NULL,
    capa_action_id INTEGER,
    action_title TEXT NOT NULL,
    assigned_to TEXT,
    assigned_role_id INTEGER,
    due_date TEXT NOT NULL,
    status TEXT DEFAULT 'pending' CHECK (status IN ('pending','in_progress','completed','overdue','deferred')),
    evidence_collected TEXT,
    verification_status TEXT DEFAULT 'not_started' CHECK (verification_status IN ('not_started','in_progress','completed','failed')),
    verification_date TEXT,
    notes TEXT,
    created_at TEXT DEFAULT (datetime('now')),
    updated_at TEXT DEFAULT (datetime('now')),
    FOREIGN KEY (audit_finding_id) REFERENCES audit_findings(id),
    FOREIGN KEY (capa_action_id) REFERENCES capa_actions(id),
    FOREIGN KEY (assigned_role_id) REFERENCES roles(id)
);

-- ═══ TRAINING PROVIDERS ═══

CREATE TABLE IF NOT EXISTS training_providers (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    provider_name TEXT NOT NULL,
    provider_type TEXT NOT NULL CHECK (provider_type IN ('internal','external','certified_body')),
    contact_person TEXT,
    email TEXT,
    phone TEXT,
    certification_number TEXT,
    certification_expiry TEXT,
    is_approved INTEGER DEFAULT 1,
    notes TEXT,
    created_at TEXT DEFAULT (datetime('now')),
    updated_at TEXT DEFAULT (datetime('now'))
);

-- ═══ TRAINING COMPETENCIES (PtW integratie) ═══

CREATE TABLE IF NOT EXISTS training_competencies (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    role_id INTEGER NOT NULL,
    competence_area TEXT NOT NULL,
    min_certification_level TEXT,
    related_risk_scenario_id INTEGER,
    required_for_ptw_types TEXT,
    atex_zone_restriction TEXT,
    created_at TEXT DEFAULT (datetime('now')),
    FOREIGN KEY (role_id) REFERENCES roles(id),
    FOREIGN KEY (related_risk_scenario_id) REFERENCES risk_scenarios(id)
);

-- ═══ SEED DATA: Phoenix Metals Specifieke Rollen ═══

INSERT OR IGNORE INTO roles (id, role_name, description, department, plant_area, safety_critical) VALUES
(1, 'Operator DMC', 'DMC Plant operator — veiligheidskritiek', 'Production', 'DMC Plant', 1),
(2, 'Operator Elektrolyse', 'Elektrolyse operator — HF blootstelling risico', 'Production', 'Elektrolyshal', 1),
(3, 'Operator HDI', 'HDI reactor operator — EX-zone werkzaamheden', 'Production', 'HDI Unit', 1),
(4, 'Supervisor Productie', 'Shift supervisor productie', 'Production', 'Alle', 1),
(5, 'Maintenance Technician', 'Onderhoudstechnicus — LOTO/AEX gecertificeerd', 'Maintenance', 'Alle', 1),
(6, 'HSEQ Officer', 'HSEQ functionaris', 'HSEQ', 'Kantoor + Veld', 0),
(7, 'Lab Analyst', 'Laboratorium medewerker', 'QC Lab', 'Lab', 0),
(8, 'Contractor General', 'Externe aannemer — algemeen', 'Contractor', 'Varieert', 1),
(9, 'Contractor Specialist', 'Externe specialist (HF/ATEX gecertificeerd)', 'Contractor', 'Varieert', 1),
(10, 'Plant Manager', 'Locatiemanager', 'Management', 'Alle', 0);

-- ═══ SEED DATA: Phoenix Metals BRZO Trainingen ═══

INSERT OR IGNORE INTO training_programs (id, program_code, program_name, description, category, duration_hours, valid_period_days, renewal_required, regulatory_reference, pbzo_requirement_code, is_mandatory, applies_to_contractors) VALUES
(1, 'TRN-VCA-B', 'Basisveiligheid VCA', 'VCA Basisveiligheid voor alle medewerkers', 'general', 8, 365, 1, 'Arbowet art.8', NULL, 1, 1),
(2, 'TRN-VCA-V', 'VOL-VCA (Leidinggevenden)', 'VCA voor leidinggevenden en supervisors', 'general', 16, 3650, 0, 'Arbowet art.8', NULL, 1, 1),
(3, 'TRN-HF-01', 'HF-Bewustwording', 'Waterstoffluoride veiligheidsbewustwording', 'brzo_mandatory', 4, 365, 1, 'BRZO Bijlage III', 'PM-VBS05-003', 1, 1),
(4, 'TRN-HF-02', 'Werken met HF — Geavanceerd', 'Diepgaande HF training voor operators met blootstellingsrisico', 'brzo_mandatory', 8, 365, 1, 'BRZO Bijlage III', 'PM-VBS05-003', 1, 0),
(5, 'TRN-EX-01', 'EX-Zone Bewustwording (ATEX)', 'ATEX/EX-zone veiligheidsbewustwording', 'safety', 4, 365, 1, 'ATEX 153/ATEX 114', NULL, 1, 1),
(6, 'TRN-ADO-01', 'Adembescherming Dragen', 'Gebruik van ademhalingsbeschermingsmiddelen', 'safety', 4, 365, 1, 'Arbowet art.3', NULL, 1, 0),
(7, 'TRN-LOTO-01', 'LOTO Procedures', 'Lock-Out/Tag-Out energie-isolatie', 'safety', 4, 730, 1, 'Seveso III', 'PM-VBS03-002', 1, 1),
(8, 'TRN-ER-01', 'Bedrijfsnoodplan & Evacuatie', 'Emergency response en evacuatieprocedure', 'emergency', 4, 365, 1, 'BRZO Bijlage III', 'PM-VBS04-001', 1, 1),
(9, 'TRN-FIRE-01', 'Brandbestrijding (Blusmiddelen)', 'Gebruik brandblussers en brandslanghaspels', 'emergency', 2, 365, 1, 'ARBO besluit art.3.28', NULL, 0, 0),
(10, 'TRN-SDS-01', 'Veiligheidsinformatiebladen Lezen', 'SDS/VIB interpretatie voor veilig werken', 'technical', 4, 730, 1, 'REACH/CLP', NULL, 0, 0),
(11, 'TRN-PTW-01', 'Werkvergunningensysteem', 'PtW procedure training', 'technical', 4, 730, 1, 'BRZO Bijlage III', 'PM-VBS03-004', 1, 1),
(12, 'TRN-CMR-01', 'Werken met CMR-stoffen', 'Carcinogene, Mutagene, Reproductietoxische stoffen', 'brzo_mandatory', 4, 365, 1, 'ARBO besluit 4a', NULL, 0, 0),
(13, 'TRN-H2S-01', 'H2S Bewustwording', 'Waterstofsulfide veiligheid', 'safety', 2, 365, 1, 'IDLH protocol', NULL, 0, 0),
(14, 'TRN-HEF-01', 'Hijsen & Tillen', 'Veilig hijsen en tillen', 'technical', 4, 730, 1, 'Machinerichtlijn', NULL, 0, 1),
(15, 'TRN-CON-01', 'Confined Space Entry', 'Betrekkingsruimte proced ure', 'safety', 4, 365, 1, 'ARBO besluit', 'PM-VBS03-003', 1, 1);

-- ═══ SEED DATA: Training-Role Matrix ═══

INSERT OR IGNORE INTO training_matrix (role_id, training_program_id, role_level, is_required, priority, applies_to_contractors) VALUES
-- Operator DMC
(1, 1, 1, 1, 'critical', 0),   -- VCA-B
(1, 3, 1, 1, 'critical', 0),   -- HF-Bewustwording
(1, 4, 1, 1, 'critical', 0),   -- HF Geavanceerd
(1, 5, 1, 1, 'high', 0),       -- EX-Zone
(1, 6, 1, 1, 'high', 0),       -- Adembescherming
(1, 7, 1, 1, 'high', 0),       -- LOTO
(1, 8, 1, 1, 'critical', 0),   -- Noodplan
(1, 11, 1, 1, 'high', 0),      -- PtW
-- Operator Elektrolyse
(2, 1, 1, 1, 'critical', 0),
(2, 3, 1, 1, 'critical', 0),
(2, 4, 1, 1, 'critical', 0),
(2, 6, 1, 1, 'critical', 0),
(2, 7, 1, 1, 'critical', 0),
(2, 8, 1, 1, 'critical', 0),
(2, 11, 1, 1, 'high', 0),
(2, 15, 1, 1, 'high', 0),      -- Confined Space
-- Operator HDI (EX-zone zwaar)
(3, 1, 1, 1, 'critical', 0),
(3, 5, 1, 1, 'critical', 0),   -- EX-Zone VERPLICHT
(3, 6, 1, 1, 'high', 0),
(3, 7, 1, 1, 'high', 0),
(3, 8, 1, 1, 'critical', 0),
(3, 11, 1, 1, 'high', 0),
-- Supervisor
(4, 2, 2, 1, 'critical', 0),   -- VOL-VCA
(4, 3, 2, 1, 'critical', 0),
(4, 4, 2, 1, 'high', 0),
(4, 5, 2, 1, 'high', 0),
(4, 7, 2, 1, 'high', 0),
(4, 8, 2, 1, 'critical', 0),
(4, 11, 2, 1, 'critical', 0),
-- Maintenance
(5, 1, 1, 1, 'critical', 0),
(5, 5, 1, 1, 'critical', 0),
(5, 6, 1, 1, 'critical', 0),
(5, 7, 1, 1, 'critical', 0),   -- LOTO VERPLICHT
(5, 8, 1, 1, 'critical', 0),
(5, 11, 1, 1, 'critical', 0),
(5, 15, 1, 1, 'high', 0),
-- HSEQ Officer
(6, 2, 3, 1, 'critical', 0),
(6, 10, 3, 1, 'high', 0),
(6, 12, 3, 1, 'high', 0),
-- Contractor General
(8, 1, 1, 1, 'critical', 1),   -- VCA-B
(8, 8, 1, 1, 'critical', 1),   -- Noodplan
(8, 11, 1, 1, 'high', 1),      -- PtW
-- Contractor Specialist
(9, 1, 1, 1, 'critical', 1),
(9, 3, 1, 1, 'critical', 1),   -- HF-Bewustwording
(9, 5, 1, 1, 'high', 1),       -- EX-Zone
(9, 7, 1, 1, 'high', 1),       -- LOTO
(9, 8, 1, 1, 'critical', 1),
(9, 11, 1, 1, 'high', 1);

-- ═══ SEED DATA: Test Medewerkers ═══

INSERT OR IGNORE INTO employees (id, employee_number, first_name, last_name, email, hire_date, role_id, department, location, status) VALUES
(1, 'PM-001', 'Jan', 'de Vries', 'j.devries@phoenixmetals.nl', '2022-03-15', 1, 'Production', 'DMC Plant', 'active'),
(2, 'PM-002', 'Pieter', 'Bakker', 'p.bakker@phoenixmetals.nl', '2021-06-01', 2, 'Production', 'Elektrolyshal', 'active'),
(3, 'PM-003', 'Ahmed', 'El Amrani', 'a.elamrani@phoenixmetals.nl', '2023-01-10', 3, 'Production', 'HDI Unit', 'active'),
(4, 'PM-004', 'Lisa', 'van Dijk', 'l.vandijk@phoenixmetals.nl', '2020-09-01', 4, 'Production', 'Alle', 'active'),
(5, 'PM-005', 'Marco', 'Jansen', 'm.jansen@phoenixmetals.nl', '2021-11-15', 5, 'Maintenance', 'Alle', 'active'),
(6, 'PM-006', 'Sophie', 'Hofman', 's.hofman@phoenixmetals.nl', '2022-05-20', 6, 'HSEQ', 'Kantoor + Veld', 'active');

-- ═══ SEED DATA: Test Certificeringen (met gap voor Gap Analyzer) ═══
-- Jan de Vries: VCA geldig, HF verlopen!
INSERT OR IGNORE INTO certifications (employee_id, training_program_id, trainer_name, training_date, expiry_date, certificate_number, status) VALUES
(1, 1, 'NIBHV', '2025-06-15', '2026-06-15', 'VCA-2025-001', 'active'),
(1, 3, 'Phoenix Metals Internal', '2025-01-10', '2026-01-10', 'HF-JDV-2025', 'expired'),
(1, 8, 'Bedrijfsbrandweer', '2025-08-01', '2026-08-01', 'ER-JDV-2025', 'active'),
-- Pieter Bakker: alles geldig
(2, 1, 'NIBHV', '2025-09-01', '2026-09-01', 'VCA-2025-002', 'active'),
(2, 4, 'Phoenix Metals Internal', '2025-10-15', '2026-10-15', 'HF-ADV-PB-2025', 'active'),
(2, 6, '3M Safety', '2025-07-20', '2026-07-20', 'ADO-PB-2025', 'active'),
-- Ahmed El Amrani: VCA geldig maar EX-Zone ontbreekt! (gap)
(3, 1, 'NIBHV', '2025-11-01', '2026-11-01', 'VCA-2025-003', 'active'),
-- Marco Jansen: VCA bijna verlopen (binnen 30 dagen)
(5, 1, 'NIBHV', '2025-04-20', '2026-04-20', 'VCA-2025-005', 'active'),
(5, 7, 'Phoenix Metals Internal', '2025-03-01', '2027-03-01', 'LOTO-MJ-2025', 'active');

-- ═══ SEED DATA: Test Contractors ═══

INSERT OR IGNORE INTO m5_contractors (id, 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) VALUES
(1, 'Technisch Installatiebedrijf Van der Berg B.V.', '12345678', 'Klaas van der Berg', 'k.vdberg@vanderberg.nl', '06-12345678', 'Elektrotechnisch installateur', 'VCA*', '2026-12-31', '2026-06-01', 1, 1, 1, 1, 'high', 'active'),
(2, 'PipeFit Solutions B.V.', '87654321', 'Henk Mulder', 'h.mulder@pipefit.nl', '06-87654321', 'Leidingwerk/Piping', 'VCA**', '2026-03-15', '2026-09-01', 1, 1, 0, 1, 'medium', 'active');

INSERT OR IGNORE INTO contractor_employees (id, contractor_id, first_name, last_name, role_id, vca_certificate_number, vca_expiry_date, site_induction_date, site_induction_expiry, status) VALUES
(1, 1, 'Klaas', 'van der Berg', 8, 'VCA*-KVB-2025', '2026-12-31', '2025-09-01', '2026-09-01', 'active'),
(2, 1, 'Piet', 'Smit', 8, 'VCA*-PS-2025', '2026-08-15', '2025-09-01', '2026-09-01', 'active'),
(3, 2, 'Bert', 'de Graaf', 9, 'VCA**-BDG-2025', '2026-03-15', '2025-11-01', '2026-11-01', 'active');

-- ═══ PtW EXTENSION: Kolommen voor competentie check ═══
-- Voeg assigned_employee_id en assigned_contractor_employee_id toe aan ptw_permits

ALTER TABLE ptw_permits ADD COLUMN assigned_employee_id INTEGER REFERENCES employees(id);
ALTER TABLE ptw_permits ADD COLUMN assigned_contractor_employee_id INTEGER REFERENCES contractor_employees(id);
ALTER TABLE ptw_permits ADD COLUMN competency_check_passed INTEGER DEFAULT 0;
ALTER TABLE ptw_permits ADD COLUMN competency_check_details TEXT;