DO $$
BEGIN
    IF EXISTS (
        SELECT 1 FROM information_schema.columns
        WHERE table_name='ohs_incidents' AND column_name='nature'
    ) THEN
        ALTER TABLE ohs_incidents
            ADD COLUMN IF NOT EXISTS submitted_by INTEGER,
            ADD COLUMN IF NOT EXISTS status VARCHAR(50) DEFAULT 'reported',
            ADD COLUMN IF NOT EXISTS form_data jsonb DEFAULT NULL;

        UPDATE ohs_incidents oi
        SET form_data = jsonb_build_object(
            'nature', nature,
            'workplace_part', workplace_part,
            'activity', activity,
            'unexpected', unexpected,
            'how_sustained', how_sustained,
            'preferred_language', preferred_language,
            'employment_type', employment_type,
            'employee_type', employee_type,
            'experience', experience,
            'completed_by', completed_by,
            'position', position,
            'signature', signature,
            'completion_date', completion_date,
            'rehabilitation', rehabilitation,
            'outcome', outcome,
            'preventative_action', preventative_action,
            'resumption_date', resumption_date,
            'days_lost', days_lost,
            'required_training_id', required_training_id,
            'damaged_item_id', damaged_item_id,
            'status_id', status_id,
            'latitude', latitude,
            'longitude', longitude,
            'pendingSync', pendingSync
        );

        UPDATE ohs_incidents oi
        SET status = s.name
        FROM ohs_incident_statuses s
        WHERE oi.status_id = s.id AND oi.status IS NULL;

        UPDATE ohs_incidents
        SET status = COALESCE(status, 'reported');

        ALTER TABLE ohs_incidents
            DROP COLUMN IF EXISTS nature,
            DROP COLUMN IF EXISTS workplace_part,
            DROP COLUMN IF EXISTS activity,
            DROP COLUMN IF EXISTS unexpected,
            DROP COLUMN IF EXISTS how_sustained,
            DROP COLUMN IF EXISTS preferred_language,
            DROP COLUMN IF EXISTS employment_type,
            DROP COLUMN IF EXISTS employee_type,
            DROP COLUMN IF EXISTS experience,
            DROP COLUMN IF EXISTS completed_by,
            DROP COLUMN IF EXISTS position,
            DROP COLUMN IF EXISTS signature,
            DROP COLUMN IF EXISTS completion_date,
            DROP COLUMN IF EXISTS rehabilitation,
            DROP COLUMN IF EXISTS outcome,
            DROP COLUMN IF EXISTS preventative_action,
            DROP COLUMN IF EXISTS resumption_date,
            DROP COLUMN IF EXISTS days_lost,
            DROP COLUMN IF EXISTS required_training_id,
            DROP COLUMN IF EXISTS damaged_item_id,
            DROP COLUMN IF EXISTS status_id,
            DROP COLUMN IF EXISTS latitude,
            DROP COLUMN IF EXISTS longitude,
            DROP COLUMN IF EXISTS pendingSync;
    END IF;
END $$;
