You have 10.5.8 now.
If you want to try an upgrade to Lion, there's two methods to get there (assuming that you don't already have any newer OS X installers, and have not purchased any)
You have to purchase Lion, which you can get at the Apple online store from
here.
But, the purchase will give you a redemption code that you use in your App Store.
You need a Mac that has an App Store, which means that you have to upgrade to Snow Leopard first, then fully update to 10.6.8 (as the installer by itself does not have the App Store, you have to install, then update through Software Update.
If you don't have Snow Leopard, you will need to purchase that installer DVD, which you can get
HERE.
THEN, with Snow Leopard, you have the App Store, where you download and then install Lion.
Or, you could ignore Lion, and go to Mountain Lion (OS X 10.8.5), which I think would be a better match, and closer to current system. You would also have to purchase Mountain Lion from
HERE (you would not need Lion if you do that) Those are the only choices that you have to upgrade directly from Apple, unless you want to update to the El Capitan (which would be a big leap for you, mostly because of your software needs)
So, just to recap, if you want to move to more modern system, you have to first go to Snow Leopard, then you have access to several choices for upgrades through the App Store.
The OTHER choice, is to get the download for Lion, or Mountain Lion, or something newer, on another Mac, where you can get the download. And, you can make a bootable flash drive from that downloaded installer app. If you get the installer through another Mac, or even download it through a friend's Mac, that downloaded installer can work to install a newer OS X version on whatever Mac will be compatible with that system. No need to go through the upgrade to Snow Leopard first if you already have the Lion or Mountain Lion installer through another method.
If you already have a Mac with a newer system (Snow Leopard or newer), or if you know someone who may be able to help you with a download, then that will be an ideal situation.
I would ALSO recommend that if you want to upgrade your OS X, you should also consider additional RAM. Your iMac can be upgraded to a maximum of 8GB.
That would be a good upgrade for the software that you use, even if you choose NOT to upgrade your OS X version.
To answer your question about going back to Leopard.
Yes, make a full external backup of your present drive before you begin any upgrades. You can then always simply erase your internal hard drive, and restore from your backup.
If you do a simple upgrade from your present Leopard to Snow Leopard, then upgrade to Lion (or Mountain Lion), that would not directly affect the OTHER software - Adobe, etc) that is already installed. Your OS X system simply gets upgraded, along with the other updates to Apple's apps that are part of the OS X system. When you are upgraded as far as you want to go, you may want to test your primary software, maybe after upgrading to Snow Leopard, then again after going to Lion, etc.
It will be fun! (I hope...)