Some comments from a long time programmer (coming up on my 50th anniversary) and Mathematica user (32 years):
First, Bernard Gress's comment that WL exists so that Stephen can get his projects done is truer than might be expected. Certainly, the best way to get feature 'x' implemented is to get Stephen interested.
Second, I think that there really is confusion about what Mathematica is for. I initially used it solely to solve maths problems, and for that it was, and is, superb. The first edition of the Mathematica book had the subtitle: "A system for doing mathematics by computer". Over the versions, the definition of what maths is has expanded, and a bunch of other stuff has been added.
The notebook interface is great for this type of investigation and interactive exploration.
However, it is rubbish at making stand-alone apps.
It is also far too hard to do computer-science or complicated programming. When I learned c, almost everything I needed to know was in the K&R book, some 125 pages or so, as I recall. Sure, you needed to understand the standard library, but the header files gave the information you needed. This level of documentation is missing in WL, although the general level of documentation otherwise is very good. With c, the step between the 'simple' stuff and a complex program was gradual and the simplicity of the core language made learning what to do relatively straight-forward. With Wolfram language, it is by no means clear what the core language is, and understanding it is not simple. This makes going beyond the basics much more difficult than it should be. At Apple in the early 2000s, they told developers that Xcode (and objective-c) made the regular stuff easy, and the hard stuff possible. With Mathematica, the mathematical functionality for interactive use is made very easy (compare to writing your own), but anything beyond that has been made much more difficult than it needs to be.
To give an example, it is not clear from the documentation how to pass a variable by reference -- if it is there, it is pretty well hidden.
Your comment about losing something when key people leave is very telling, especially for Mac users. When Theo Gray was involved, you could expect that Mathematica would make use of Apple technologies -- it was largely due to him that Mathematica remained a Mac program through the dark days. However, I am no longer feeling the love, specifically in the areas of UI and GPU utilization. As far as I can tell, Apple's neural engine is at least as good as anything based oil NVIDIA, yet it is not used at all for machine learning in Mathematica. Embracing other new OS features has been very slow -- it took far too long to move the front end to 64 bit, for example.
There is also the tendency to leave stuff at the 99% done level and move on to the next shiny object. There are simply too many bugs. There are too many similar functions that do pretty much the same thing, but which have subtle differences in the API or performance, and the documentation does not make that clear at all.
When I retired, I decided that I would let my skills in c (etc....) lapse in favor of doing all my coding in WL. I have come to regret this decision. When I use WL as part of my mathematical studies, I have (mostly) no problems. For anything else, it is a constant slog to achieve even moderate success.
I do have proposals for a solution:
Fix the bugs.
Market Mathematica (Wolfram|One, etc.) for what it does best -- interactive computation.
Fully implement the Wolfram engine so that it can be used as a resource when developing stand-alone code in pretty much any language for any platform. Specifically, this means making the engine work with iOS and iPadOS so that other developers can develop apps with a state-of-the-art UI and functionality, yet making use of the strengths of WL. (A similar case can be made for Android, as well.)
Make full use of the native capabilities of each hardware platform. This means making use of all the technologies in macOS (and iPad/iOS), Windows and Linux, although the most obvious mismatch is with macOS at the moment.
Institute a developer program (with nominal costs attached), so that developers will have access to essentially whatever or whoever tech support gets their undocumented answers from. I remember that the Wolfram Technology conference was originally called the Mathematica Developer's Conference.
As a corollary, provide some really state-of-the art developer tools. I tried using Eclipse, but compared to the tools that I used when developing Mac apps (Lightspeed c, Code Warrior, Xcode), the tool is clumsy and out-of-date. I have no experience in Java development or development on Linux or Windows; perhaps Eclipse is perfectly usable for these platforms. I know that discussing tools is likely to lead to a religious war -- after all there are those who think c++ is the best of all possible languages. However, if Wolfram Research wants to break out if its ghetto, it will need to integrate with modern IDEs, and Eclipse is not going to help with this (in my opinion, of course).
Consider making a new language. All older languages have had re-thinks about what the language means. C has had several successors, with different levels of success: c++, Objective-c, and Swift. While I am not fluent in Swift (yet) it seems to have captured the essence of what was good about c without the excesses of c++, and is easy to learn. A similar rethink of WL would be a really good idea. While many of the design objective of WL were good, not all were, and some of them are real howlers -- especially in the area of UI design and implementation. Done right, no old code would be obsolete (you can still use ANSI c in Swift), but things would be a lot easier both for typical users and experts. Of course, if Wolfram Engine were available for macOS and iPadOS, some enterprising person or group could re-implement WL from within Swift, so this might not be so far out after all. (Remember Swift is not proprietary -- and I am using that only as an example.)
When I first used Mathematica, I was developing (in-house) real time process control software for a large clinical lab. I would solve the mathematical problems and prototype graphs, etc., using Mathematica, then translate the results into c on a Macintosh. This combination worked extraordinarily well. If my suggestions were implemented, and I were doing the same thing today, I would do the prototyping in WL, then use the Wolfram Engine in a native program I would write in Swift for macOS and iPadOS that would make native use of Apple's Neural engine. This would result is a more useful and stable application than I could ever hope to do using WL alone.
So, to answer your question: what is Mathematica for?
In its current state, Mathematica is for interactive problem solving of primarily mathematical problems using the notebook interface.
I do not think it is effective when used for anything else (certainly by not anyone outside of Wolfram Research) : web deployment, etc. except as a way to publish notebooks interactively, seems to be a toy project.
With proper focus, as I outlined above, Wolfram Language could be a significant tool in the development and implementation of basically any type of scientific application.