Major work. You'll need a landscape architect or engineer for the new retaining wall calculations. Drainage changes will need to be handled. You may need a safety fence on top of the resulting tall wall. You will also need permits. In my area, hillside work also requires notifying the neighbors and working with them to make sure water and landslide damage don't occur. Start with a licensed landscape architect to discuss options.

If you have a landscape designer with experience dealing with structural work, I wouldn't say you need a landscape architect. As far as whether it's worth it, that comes down to what your goals are for the backyard and what it's going to take to achieve them. A good landscape designer with install experience can help you make that judgement as they have a handle on actual, real world costs.

To make more space at kitchen level you will have to demo all or part of the first retaining wall and stairs, then excavate and rebuild it further back. That will make the space with the covered sitting area smaller. That is a job for a mason.