-- Migration 018: ISO 12100 Machine Risk Model Extension for IACE
-- Adds lifecycle phases, extended hazard fields, and protective measures metadata.

-- ============================================================================
-- 1. Extend iace_hazards with ISO 12100 fields
-- ============================================================================

ALTER TABLE iace_hazards ADD COLUMN IF NOT EXISTS machine_module TEXT DEFAULT '';
ALTER TABLE iace_hazards ADD COLUMN IF NOT EXISTS function TEXT DEFAULT '';
ALTER TABLE iace_hazards ADD COLUMN IF NOT EXISTS lifecycle_phase TEXT DEFAULT '';
ALTER TABLE iace_hazards ADD COLUMN IF NOT EXISTS hazardous_zone TEXT DEFAULT '';
ALTER TABLE iace_hazards ADD COLUMN IF NOT EXISTS trigger_event TEXT DEFAULT '';
ALTER TABLE iace_hazards ADD COLUMN IF NOT EXISTS affected_person TEXT DEFAULT '';
ALTER TABLE iace_hazards ADD COLUMN IF NOT EXISTS possible_harm TEXT DEFAULT '';
ALTER TABLE iace_hazards ADD COLUMN IF NOT EXISTS sub_category TEXT DEFAULT '';
ALTER TABLE iace_hazards ADD COLUMN IF NOT EXISTS review_status TEXT DEFAULT 'draft';

-- ============================================================================
-- 2. Extend iace_hazard_library with ISO 12100 metadata
-- ============================================================================

ALTER TABLE iace_hazard_library ADD COLUMN IF NOT EXISTS sub_category TEXT DEFAULT '';
ALTER TABLE iace_hazard_library ADD COLUMN IF NOT EXISTS default_exposure INT DEFAULT 3;
ALTER TABLE iace_hazard_library ADD COLUMN IF NOT EXISTS default_avoidance INT DEFAULT 3;
ALTER TABLE iace_hazard_library ADD COLUMN IF NOT EXISTS typical_causes JSONB DEFAULT '[]';
ALTER TABLE iace_hazard_library ADD COLUMN IF NOT EXISTS typical_harm TEXT DEFAULT '';
ALTER TABLE iace_hazard_library ADD COLUMN IF NOT EXISTS relevant_lifecycle_phases JSONB DEFAULT '[]';
ALTER TABLE iace_hazard_library ADD COLUMN IF NOT EXISTS recommended_measures_design JSONB DEFAULT '[]';
ALTER TABLE iace_hazard_library ADD COLUMN IF NOT EXISTS recommended_measures_technical JSONB DEFAULT '[]';
ALTER TABLE iace_hazard_library ADD COLUMN IF NOT EXISTS recommended_measures_information JSONB DEFAULT '[]';
ALTER TABLE iace_hazard_library ADD COLUMN IF NOT EXISTS suggested_evidence JSONB DEFAULT '[]';
ALTER TABLE iace_hazard_library ADD COLUMN IF NOT EXISTS related_keywords JSONB DEFAULT '[]';

-- ============================================================================
-- 3. Reference table: Lifecycle phases (DE/EN labels)
-- ============================================================================

CREATE TABLE IF NOT EXISTS iace_lifecycle_phases (
    id TEXT PRIMARY KEY,
    label_de TEXT NOT NULL,
    label_en TEXT NOT NULL,
    sort_order INT NOT NULL DEFAULT 0
);

INSERT INTO iace_lifecycle_phases (id, label_de, label_en, sort_order) VALUES
    ('transport', 'Transport', 'Transport', 1),
    ('assembly', 'Montage', 'Assembly', 2),
    ('commissioning', 'Inbetriebnahme', 'Commissioning', 3),
    ('setup_teach', 'Einrichten / Teach', 'Setup / Teach', 4),
    ('normal_operation', 'Normalbetrieb', 'Normal Operation', 5),
    ('special_operation', 'Sonderbetrieb', 'Special Operation', 6),
    ('cleaning', 'Reinigung', 'Cleaning', 7),
    ('maintenance', 'Wartung', 'Maintenance', 8),
    ('fault_clearing', 'Stoerungsbeseitigung', 'Fault Clearing', 9),
    ('changeover', 'Umruestung', 'Changeover', 10),
    ('decommissioning', 'Ausserbetriebnahme', 'Decommissioning', 11),
    ('disposal', 'Demontage / Entsorgung', 'Dismantling / Disposal', 12)
ON CONFLICT (id) DO NOTHING;