The fact that alternate tunings are confusing to you is perhaps an indicator that your approach to standard tuning might be based more on "learning" than "understanding".
What I mean by this is that, while it's perfectly possible to become a relatively accomplished guitarist by simply memorising chord shapes and specific guitar parts, it's more valuable to understand what it is that you're playing.
For example if I say "play a D chord" you would probably go straight to
x x 0 2 3 2, but if I said "play me the third from that chord" or "play me the f# note" would you know where they were in that chord? If you have to stop and think before answering both of those question, then it's an indication that you're playing with an "opaque" approach, rather than a "transparent" one. I'll use this analogy because I can't think of a better one, so I hope you'll forgive me if it is a little patronising and a bit too extreme: it's kind of being able to write your name, but not knowing what the letters actually mean.
I would recommend, as an exercise, take some songs that you know, and try playing them in different positions, or transposing them up and down an octave.
You mentioned that you have learnt some "scales", but I wonder if perhaps you have learnt these more as "shapes" than as conceptual sets of notes. One way to get around this is to learn the same scale in all positions, but another equally (if not more) useful thing to do is to work out simple melodies by ear, and then play them at every possible position on the neck. This will help you to visualise how notes actually fall across the strings much more naturally than memorised scales, because you actually have a connection to what the notes are "doing". You will end up seeing some scales shapes you've already learnt when you play the melody in certain places too, which is useful, and interesting. Things like: "Happy Birthday", "When The Saints Go Marching In"; nursery rhymes, christmas carols, folk songs, pop melodies etc. etc.
Playing melodies in different places will mean you cross the G-B string gap at different points in the melody. This means that you will inevitably get in the practice of compensating for how the interval between strings affects the note layout of notes across them, which is the main hurdle to overcome when trying to play in a new tuning.
For a similar activity for chord based playing, you can use a capo. Usually, we use a capo to take a song from a "difficult key" to an "easy key" (very often, we put songs in G). I'd advise using a capo for the opposite purpose, taking a song from a key you already know it in to one you don't. Take a song in, Say G, and try to play it with a capo on 3, 5, 7, and 10 (so your G would become an E, D, C and A respectively, the rest you would have to work out for yourself).
The reason this is an intermediate step is that it forces you to think outside you existing memorised shapes for that specific song, but keeps the relative tuning the same; this way you're not stepping completely into the unknown, but you're not able to just "paint by numbers" either; you should get more of an idea of what the notes are actually "for".
With these activities, you will necessarily start to understand how the notes are laid out in standard tuning and have more of an understanding of what you already "know" in standard tuning. After that, changing the intervals between the strings becomes much less of a stretch, and playing in alternative turnings becomes much more transparent.
Also, of course, listening to existing music in open tunings for an idea of what's possible is a great place to get ideas about what to "do" with the options opened up (and closed down) by open tunings:
You mentioned Drop D; that's basically the same as standard but you've got a power chord at the bottom now. Pointers for songs to start on, I might say learn the riffs to "killing in the name" by RATM, and "dead star" by muse. HEart Shaped box is another great Drop D song to play.
3 loosely defined open tuning traditions
Major open tunings: "bluesy" style
Links incoming, watch this space.
"suspended tunings : "Modern" style
- Candyrat guitarists from 2000s/early 2010s
Also check out Irish DADGAD music. Literally just google "Irish DADGAD" to see some great stuff.