-- ============================================================
-- MODULE 4: Incidentmanagement & Milieu
-- Phoenix Metals HSEQ VBS — Migratie
-- ============================================================

-- Legacy tabel voor bestaande incident_items data
CREATE TABLE IF NOT EXISTS incident_items_legacy (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    legacy_id TEXT,
    title TEXT,
    description TEXT,
    status TEXT,
    created_at TEXT,
    source TEXT
);

-- Migrate existing incident_items into legacy
INSERT OR IGNORE INTO incident_items_legacy (legacy_id, title, description, status, created_at, source)
SELECT 'INC-LEG-' || id, title, description, status, created_date, 'incident_items' FROM incident_items;

-- ============================================================
-- INCIDENTS — Uitgebreid incident management
-- ============================================================
CREATE TABLE IF NOT EXISTS incidents (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    incident_code TEXT UNIQUE NOT NULL,
    title TEXT NOT NULL,
    description TEXT,
    incident_domain TEXT DEFAULT 'process_safety',  -- process_safety, arbo, environmental, near_miss
    incident_type TEXT DEFAULT 'near_miss',         -- near_miss, unsafe_condition, minor_incident, significant_incident, major_incident
    severity TEXT DEFAULT 'low',                    -- negligible, minor, moderate, major, catastrophic
    status TEXT DEFAULT 'open',                     -- open, investigating, capa_pending, closed
    date_occurred TEXT,
    date_reported TEXT DEFAULT CURRENT_TIMESTAMP,
    location TEXT,
    reported_by TEXT,
    reporter_role TEXT,
    reporter_department TEXT,
    reporter_contact TEXT,
    anonymous_report INTEGER DEFAULT 0,
    
    -- Relaties
    related_substance_id INTEGER,
    related_risk_scenario_id INTEGER,
    related_moc_id INTEGER,
    related_ptw_id INTEGER,
    
    -- Proces/Equipment info
    equipment_tag TEXT,
    process_unit TEXT,
    
    -- Stof details
    substance_involved INTEGER DEFAULT 0,
    substance_name TEXT,
    substance_quantity_released REAL,
    substance_quantity_unit TEXT,
    release_pathway TEXT,
    containment_type TEXT,
    
    -- Letsel
    number_of_casualties INTEGER DEFAULT 0,
    number_of_fatalities INTEGER DEFAULT 0,
    first_aid_required INTEGER DEFAULT 0,
    medical_treatment_required INTEGER DEFAULT 0,
    hospitalization_required INTEGER DEFAULT 0,
    lost_time_injury INTEGER DEFAULT 0,
    lost_time_days INTEGER DEFAULT 0,
    
    -- Milieu impact
    environmental_impact TEXT,  -- none, minor_local, moderate_local, significant_regional, major_widespread
    spill_contained INTEGER DEFAULT 0,
    spill_reached_drain INTEGER DEFAULT 0,
    spill_reached_soil INTEGER DEFAULT 0,
    spill_reached_water INTEGER DEFAULT 0,
    air_emission_exceeded INTEGER DEFAULT 0,
    brzo_major_accident INTEGER DEFAULT 0,
    
    -- Directe acties
    immediate_actions TEXT,
    scene_secured INTEGER DEFAULT 0,
    evidence_preserved INTEGER DEFAULT 0,
    witnesses_interviewed INTEGER DEFAULT 0,
    
    -- Onderzoek & Melding
    investigation_required INTEGER DEFAULT 0,
    investigation_lead TEXT,
    investigation_due_date TEXT,
    reporting_required INTEGER DEFAULT 0,
    reporting_authority TEXT,
    reporting_deadline TEXT,
    reporting_submitted INTEGER DEFAULT 0,
    reporting_submitted_date TEXT,
    
    -- Meta
    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
    updated_at TEXT DEFAULT CURRENT_TIMESTAMP
);

-- Incident Investigations (5-Why, Tripod Beta, etc.)
CREATE TABLE IF NOT EXISTS incident_investigations (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    incident_id INTEGER NOT NULL,
    investigation_method TEXT DEFAULT 'five_why',  -- five_why, tripod_beta, fishbone, bowtie
    status TEXT DEFAULT 'in_progress',              -- in_progress, completed, reviewed
    
    -- 5-Why velden
    why_1 TEXT,
    why_2 TEXT,
    why_3 TEXT,
    why_4 TEXT,
    why_5 TEXT,
    root_cause TEXT,
    
    -- Tripod Beta velden
    tripod_preconditions TEXT,
    tripod_unsafe_acts TEXT,
    tripod_organisational TEXT,
    tripod_hardware TEXT,
    
    -- Algemeen
    contributing_factors TEXT,
    conclusions TEXT,
    lessons_learned TEXT,
    recommendations TEXT,
    
    lead_investigator TEXT,
    investigation_team TEXT,
    started_at TEXT DEFAULT CURRENT_TIMESTAMP,
    completed_at TEXT,
    reviewed_by TEXT,
    reviewed_at TEXT,
    
    FOREIGN KEY (incident_id) REFERENCES incidents(id)
);

-- Incident Witnesses
CREATE TABLE IF NOT EXISTS incident_witnesses (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    incident_id INTEGER NOT NULL,
    witness_name TEXT NOT NULL,
    witness_role TEXT,
    witness_department TEXT,
    witness_contact TEXT,
    statement TEXT,
    statement_date TEXT,
    interviewed INTEGER DEFAULT 0,
    interviewed_by TEXT,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (incident_id) REFERENCES incidents(id)
);

-- CAPA Actions (overkoepelend — kan van incident, audit, inspectie komen)
CREATE TABLE IF NOT EXISTS capa_actions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    capa_code TEXT UNIQUE,
    source_type TEXT DEFAULT 'incident',  -- incident, audit, inspection, management_review
    source_id INTEGER,
    
    action_type TEXT DEFAULT 'corrective',  -- corrective, preventive, improvement
    category TEXT,  -- containment, notification, procedure, training, equipment, system
    description TEXT NOT NULL,
    
    priority TEXT DEFAULT 'medium',  -- low, medium, high, critical
    status TEXT DEFAULT 'open',      -- open, in_progress, completed, verified, closed
    
    assigned_to TEXT,
    due_date TEXT,
    completed_date TEXT,
    verified_by TEXT,
    verified_date TEXT,
    
    effectiveness_check TEXT,
    effectiveness_check_date TEXT,
    
    progress_notes TEXT,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
    updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (source_id) REFERENCES incidents(id)
);

-- ============================================================
-- MILIEU — Environmental Metrics & Waste Streams
-- ============================================================
CREATE TABLE IF NOT EXISTS environmental_metrics (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    metric_type TEXT NOT NULL,       -- air_emission, water_discharge, noise, soil, waste, energy
    parameter_name TEXT NOT NULL,
    parameter_code TEXT,
    
    value REAL NOT NULL,
    unit TEXT,
    measurement_date TEXT,
    measurement_period TEXT,         -- 2026-Q1, 2026-M03, etc.
    
    source_location TEXT,
    emission_point_id TEXT,
    process_unit TEXT,
    
    permit_limit REAL,
    permit_limit_unit TEXT,
    alert_threshold REAL,
    
    percentage_of_limit REAL,
    compliance_status TEXT DEFAULT 'compliant',  -- compliant, warning, non_compliant, exceedance
    trend_direction TEXT,            -- improving, stable, deteriorating
    
    notes TEXT,
    measured_by TEXT,
    verified INTEGER DEFAULT 0,
    
    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
    updated_at TEXT DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS waste_streams (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    stream_name TEXT NOT NULL,
    waste_category TEXT NOT NULL,    -- hazardous, non_hazardous, mixed
    waste_code_eural TEXT,           -- Europese afvalstoffenlijst
    waste_description TEXT,
    
    quantity REAL,
    quantity_unit TEXT DEFAULT 'kg',
    period TEXT,                     -- 2026-Q1, 2026-M03
    
    source_process_unit TEXT,
    source_location TEXT,
    
    disposal_method TEXT,            -- recycling, incineration, landfill, specialist, reuse
    disposal_contractor TEXT,
    disposal_date TEXT,
    disposal_certificate TEXT,
    
    storage_location TEXT,
    storage_start_date TEXT,
    storage_compliant INTEGER DEFAULT 1,
    
    cost REAL,
    notes TEXT,
    
    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
    updated_at TEXT DEFAULT CURRENT_TIMESTAMP
);

-- Indexen voor performance
CREATE INDEX IF NOT EXISTS idx_incidents_status ON incidents(status);
CREATE INDEX IF NOT EXISTS idx_incidents_domain ON incidents(incident_domain);
CREATE INDEX IF NOT EXISTS idx_incidents_severity ON incidents(severity);
CREATE INDEX IF NOT EXISTS idx_incidents_date ON incidents(date_occurred);
CREATE INDEX IF NOT EXISTS idx_incidents_substance ON incidents(related_substance_id);
CREATE INDEX IF NOT EXISTS idx_investigations_incident ON incident_investigations(incident_id);
CREATE INDEX IF NOT EXISTS idx_witnesses_incident ON incident_witnesses(incident_id);
CREATE INDEX IF NOT EXISTS idx_capa_source ON capa_actions(source_type, source_id);
CREATE INDEX IF NOT EXISTS idx_capa_status ON capa_actions(status);
CREATE INDEX IF NOT EXISTS idx_env_metrics_type ON environmental_metrics(metric_type);
CREATE INDEX IF NOT EXISTS idx_env_metrics_date ON environmental_metrics(measurement_date);
CREATE INDEX IF NOT EXISTS idx_env_metrics_compliance ON environmental_metrics(compliance_status);
CREATE INDEX IF NOT EXISTS idx_waste_category ON waste_streams(waste_category);
CREATE INDEX IF NOT EXISTS idx_waste_period ON waste_streams(period);