Don’t be put off by a “no” conclusion - the answer is clearly “Yes” when SharePoint Foundation provides doc storage, metadata, editing, version-control, security and workflows – all out-of-the-box. I do understand frustration that it does too much at the same time as having limitations and some clunkiness – but those are things to assess against requirements, price and user-adoption.
Tiago’s link is to Huddle which is merely a competitor and although it has many good points, as he noted they are not all correct or updated. I expect he is a skilled but slightly jaded SharePoint developer – and any programmer can empathise with that! Plus, SharePoint has certainly evolved from your 2001 version! Allow me to address your concerns about Foundation a little more...
It’s not really valid to undermine any product because of limitations with its free (Foundation) edition. But it is expected to use that to prove off the basics, and surprisingly it does that very well, mainly because Foundation is the core of the fuller licenced editions, and not a different codebase. We went to some effort to make Foundation work as a DMS for our client (a few hundred users) and they are very happy with it for 2 years, even though previous supplier SharePoint implementations failed due to lack of planning and development ability. Budget a couple of weeks for prototype provision (not always an option, admittedly) – Office365 now saves time setting up but is not the on-prem Foundation edition.
Your suspicion of too much work and whistles is valid – it definitely helps, often critically, if you are able to spend time developing web/app-parts, event receivers etc, because otherwise the bare system lacks a single place to view all document statuses (CQWP Content Query WebParts can help but are limited in Foundation), send emails after approving etc. The workflow system should help and has been overhauled for 2013 Server licenced, but for Foundation you’ll have to stick with 2010 workflows or event receiver code. However, as a .NET developer (back end code shouldn’t be much more taxing than front end), you should be able to write the code and be confident you are adding value, not patching a dog. It is this type of awkwardness which jades developers though, when customers expect everything for peanuts.
Unique DocumentID, Document Sets, Content Organisers etc are great and may become essential for any business – but not necessarily for everybody (at the cost of upgrading). Metadata management is much better now (tags can be managed, not just free text) but that again is only in the licenced Server edition. However even in Foundation you can create a new Custom Template for documents and libraries, then add extra fields there – we do it for Review Dates and then use a developed WebPart to show all the docs coming up for review – works fine.
Be sure to check Designing large lists and maximizing list performance and also deal with the inevitable thorny question of metadata-vs-folders, as many users still expect folders and custom Permission Levels may need to be structured around that.
There are no doubt easier out-of-the-box solutions, eg Huddle, but there are also a lot more clunky Enterprise DMS, and the extensibility of SharePoint is excellent, as this Stack Exchange portal attests. Hope you are able to get into the system!