My experience with sleep deprivation is very limited. I do use stimulants, but I've never done the whole binge and stay up for days thing. Nor have I been tempted to abuse stims like that honestly, for some reason. The strongest stims I've done are Adderall and propylhexedrine. I do pull all-nighters from time to time if I have to get a lot done for work. Honestly, the mental effects of sleep deprivation can begin to set in at a low level long before the numbers that people are talking about here. There's definitely a change in mental state around 24 hours honestly, which isn't anything crazy but definitely is noticeable.
I've even seen stuff out of the corner of my eye, heard noises, and began getting a little paranoid around the 24 hour mark. On one occasion, I stayed up for around 36-40 hours or so. This was without anything other than caffeine. This is the absolute longest I've ever gone. I didn't become a lunatic or even come close to that, it was similar to the mild alterations that I notice at around the 24 hour mark (e.g. getting a bit batty but not outright losing it). No sleep deprivation hallucinations/delusions or anything like that. However, the day after when I had been up all night, I decided to take some edibles. And the high was very different than it would have been had I slept the night before.
It had a very psychedelic feel to it, and I even had actual visuals a little bit. I have used classical psychedelics around 40-50 times (e.g. mushrooms and morning glory seeds mostly, though I've also tried acid, 2C-B, and 25-I), and cannabis has had different effects on me as a result. For some reason, cannabis affects me much differently after I've taken psychedelics, has kind of a psychedelic-like quality to it. However, the sleep deprivation magnified these effects dramatically, and I actually started having visuals and everything. My friend stayed up for 48 hours and had a similar (but more intense) experience. So, yes, staying awake for long periods of time can definitely wreak havoc on your mind.
Somewhere around 72 hours or thereafter is where things begin to get really weird potentially, from what I've heard. I've never gone anywhere near that long myself, but you can start heading towards sleep deprivation psychosis after around 72 hours. And you'll start having "microsleeps" where you nod off for brief periods of time unconsciously. Though, it can take a bit longer for some people (e.g. five days or so). The actual effects vary greatly from person to person. Some people just get a bit paranoid and delusional, while others tend to have a more hallucinatory experience. It just depends. Also, staying up this long is very difficult. Eventually, it becomes impossible and you pass out involuntarily.
Theoretically, if you stayed up for long enough, you would die. Sleep is critical to vital functions in the body. The specific timeline of how long this takes is variable. Though, no one would ever experience this (with one exception). Because as I was saying, your body will eventually force you to sleep. However, there is a very rare genetic disorder, which runs in families, that causes the sufferer to become completely unable to ever sleep. The disorder is called fatal familial insomnia, and people who suffer from it will stay awake until the point of death.
It's a universally fatal prion disease with no known treatment, but it's incredibly rare. A healthy person would not be able to stay up to the point where death is a possibility. However, the whole experience of long term sleep deprivation is similar to deliriants, such as DPH and Datura. It's not pleasant and can result in serious problems. However, the problem resolves after you go back to sleep. You can't permanently go psychotic from sleep deprivation, but it can happen temporarily after 72+ hours. Cutting off sleep aids abruptly can lead to serious withdrawal that can make it very difficult to get to sleep, which can result in some of these issues (along with others potentially). So, definitely talk to your doctor to set up a taper schedule instead of going cold turkey on the sleep aids.