DO $$
BEGIN
    IF EXISTS (
        SELECT 1 FROM information_schema.columns
        WHERE table_name='risk_assessments' AND column_name='reference_id'
    ) THEN
        ALTER TABLE risk_assessments
            ADD COLUMN IF NOT EXISTS form_data jsonb DEFAULT NULL;

        UPDATE risk_assessments
        SET form_data = jsonb_build_object(
            'reference_id', reference_id,
            'assessor_names', assessor_names,
            'approver_name', approver_name,
            'work_description', work_description,
            'people_involved', people_involved,
            'duration', duration,
            'permit_requirements', permit_requirements
        );

        ALTER TABLE risk_assessments
            DROP COLUMN IF EXISTS reference_id,
            DROP COLUMN IF EXISTS assessor_names,
            DROP COLUMN IF EXISTS approver_name,
            DROP COLUMN IF EXISTS work_description,
            DROP COLUMN IF EXISTS people_involved,
            DROP COLUMN IF EXISTS duration,
            DROP COLUMN IF EXISTS permit_requirements;
    END IF;
END $$;
