Great question @iputmybloodintothis !
For the purposes of this post I'm assuming you mean urinary catheters. Other types of catheters, including IV/central line catheters, chest tubes, etc... can be covered on request.
The answer is that we have examples of urinary catheters going back to 3000 BCE. There were catheters found in Pompeii and ancient Egyptian archeological sites.
These were what we would today call "straight caths"- catheters that were inserted to drain the bladder and then immediately removed. This is because there are lots of problems that come up as we age that prevent urine from passing out of the bladder. Some of these include bladder paralysis, bladder stones, urethral strictures or inflammation (swelling or scarring shut of the tube that sends urine from bladder to toilet), or prostate enlargement.
It's also a pretty simple concept for olde timey medical folks- person can't pee, but they really have to pee, so we're gonna stick a tube up where the pee comes out to get the pee out. The ability to do this would have been life saving.
Despite being life saving, they were not comfortable. Ancient catheters were made of reeds, straw, or onion leaves. Hopefully this was an occasional problem for an individual, and not a multiple-times-per-day one.
Over the course of history, reed or straw-type disposable catheters gave way to metal re-usable ones. These came in silver, brass, copper, gold, and lead. They were not particularly malleable, but could be curved to fit the individual who was using them.
In 1752, Benjamin Franklin's brother needed a catheter due to urinary obstruction from bladder stones. The inventor created a silver coil catheter that would have been one of the first flexible catheters available. Below you can see the catheter he invented. The top tube is actually a tightly wound silver spring, which would be covered with gut to make it waterproof. The bottom tube is an insert that would be used to give rigidity while inserting or applying the gut. It is also thought that Franklin himself used this later in his life.
In the late 1800s, the first flexible rubber catheters were created. They unfortunately tended to break down when exposed to urine, leaving debris behind in the bladder. This got a little better once rubber vulcanization and latex rubber were invented.
It wasn't until 1935 that the modern Foley catheter was invented. The Foley catheter is named after it's inventor Fredric Eugene Basil Foley, who designed a catheter with a balloon on the part inside a patient's bladder. The balloon was filled with sterile water or saline, and was inflated to keep the catheter in place. Prior to this, if continuous drainage of a bladder was needed, an ordinary rubber straight catheter would be stitched or taped to the labia or head of the penis.
Today catheters are either made of latex or (more commonly where I am) silicone. People who are paralyzed or have chronic problems emptying their bladders may self-straight cath several times per day. People who need continuous bladder drainage (say, during or just after a surgery where they are unable to get out of bed temporarily, after bladder/prostate surgery, for precise intake/output measurements, when a patient has severe skin breakdown and is incontinent) usually have a foley catheter inserted. Today they look a lot like this:
As far as unconscious/confused people go, they pretty much just pee. Catheters carry a risk of infection, so unless there is a very specific reason for one, we often don't put one in unless the person couldn't pee without it, or them peeing the bed would be detrimental to them physically (severe wounds, need for perfect immobilization, surgeries, etc...).
