I finally had the chance to do some digging in the driver side wheel well and find out exactly which tube corresponds to the drain that's situated right below the bottom left corner of the windshield - the one where I could see a torn-up tube.

I inserted a medium-sized vinyl hose into this drain pipe and poured a little water into the hose, making sure that the hose is inserted deep enough below the tear in the drain pipe so that the water didn't end up inside the car. Sure enough, the water came out of the big drain pipe that I originally found in the front driver side wheel well running along the backside of the fender.

The drain pipe runs up behind a plastic lining of the wheel well. So I removed a few fastening bolts and partially removed the lining to be able to access the rear cavity of the front fender. I felt my way through the confined spaces along the drain pipe (you need long arms for that!), and it led me to the windshield drain hole, as I knew it would.

The pipe could not hold itself in place, as it expanded, cracked, and tore up over the years. I simply yanked it out. Then it's off to the hardware store. After much deliberation in the plumbing supply sections of Home Depot and Lowe's, I bought a few rubber fittings and some vinyl hose in whatever closest size was available. The factory drain pipe is installed over a protruding metal pipe on the underside of the windshield drain hole. But to get in there to be able to slip some hose onto that metal pipe was simply too difficult with my big hands. It would really be best to remove the fender to do that job properly. So I decided instead to feed the vinyl hose through the drain hole and all the way down into the wheel well and then creatively use a rubber fitting/coupler (which I was VERY lucky to find) to fill the annular space between the hose and the metal drain pipe (I could not find the hose with large enough outside diameter to fit snugly to the metal drain pipe's inner diameter). The rubber coupler turned out to be perfect size in mating the vinyl pipe snugly to the metal drain pipe - snug enough for me to be able silicone-seal any seams and expect it to hold up.

The hardware that I used to rig this up probably cost me around $5-7. My homemade drain is smaller in diameter than the factory drain pipe, of course, but I don't think that it's small enough to limit any water flow even during the very heavy rains. I have every reason to expect this set up to hold up. If it leaks, I will just have to think of a better way to seal it at the drain - maybe use a different sealant than silicone. Time will tell. When the sealant cures, I'll try to flood that thing with water and see if I get any in the footwell. I do hope that there's not some OTHER drain that may be causing my driver side footwell to get wet during a rain... Would be nice to be done with this problem!