Like I suggested above, themes of an heroic or historical nature, as well as ones dealing with mythical or dramatic or philosophical subjects. Particularly, the kind of stories that need telling and retelling, dealing with the eternal mysteries of life, the supernatural, the eternal, victory, quests, etc. Anything that kind of falls into this area... the kind of thing one would consider reading, were it a novel.
What I want to avoid are light, fluffy, humorous pieces with little more than an immediate (one-time) entertainment value... the kind of thing one would not read, were it a novel.
You need to keep in mind that opera is primarily a
musical form and that long, convoluted stories are not generally suitable for a musical presentation a few hours long. Good opera stories keep events to a minimum and allow music to carry the message. You don't want them to read like novels. That said, here are some interesting operas that deal with some of the themes you mention.
Wagner, definitely. You've described most of his subject matter: myth, legend, the supernatural, gods, heroes, quests for salvation, philosophical ruminations - serious stuff and epic music. The
Ring alone could keep you occupied for a month.
Der Fliegende Hollander,
Tannhauser,
Lohengrin and
Parsifal also have mythical or legendary, often symbolic stories.
Die Meistersinger is a comedy, and
Tristan und Isolde is a love story, but both deal with serious themes.
Baroque operas are often based on history or mythology, e.g. Handel's. There isn't much of the "eternal mysteries of life" there, however. Mostly a lot of confusing intrigue, difficult arias, and mezzo-sopranos pretending to be castrati (good music, though!).
The Orpheus story (Monteverdi and Gluck) is Greek mythology and takes us to Hades and back.
Beethoven's
Fidelio is a story of a woman's heroic rescue of her husband from prison.
Weber's
Der Freischutzis a supernatural folk tale about magic bullets, with some spooky music.
Both Purcell's
Dido and Aeneas and Berlioz's
Les Troyens tell the legendary story (from Virgil) of Trojan warrior Aeneas and Dido, queen of Carthage. The Purcell is brief but the Berlioz is on an epic scale.
Dvorak's
Rusalka is the fairy tale of the "little mermaid" who wanted to be human. Very atmospheric, with water goblins and stuff.
Mussorgsky's
Boris Godunov and
Khovanshchina are from Russian history, as are Borodin's
Prince Igor and Tchaikovsky's
Mazeppa.
Boris is a powerful masterpiece.
Rimsky-Korsakov wrote a number of colorful operas based on Russian history and folk tales.
Verdi's plots can be hard to follow, but the story of
Macbeth is clear enough. Many of his operas are based on plays based on history;
Don Carlos and
Simon Boccanegra are great works with strong characters.
The Faust legend is treated by Gounod in
Faust, Boito in
Mefistofele, and Busoni in
Doktor Faust. The Gounod is rather sentimental; the Busoni is fascinating (IMO).
Stravinsky's
Le Rossignol and Puccini's
Turandot are both fairy tales set in China, both with magical scores.
Prokofiev's
War and Peace is about... war and peace! (Tolstoy, of course).
Bartok's
Bluebeard's Castle is a powerful psychological study based on the story of Duke Bluebeard.
Strauss's
Die Frau Ohne Schatten is a lengthy, quasi-mythical supernatural tale.
Szymanowski's
King Roger is a semi-historical supernatural tale.
Enescu's
Oedipe is an impressive telling of the Oedipus myth, undeservedly obscure, IMO.
Vaughan Williams's
The Pilgrim's Progress is John Bunyan's Christian allegory of the journey of a soul.
Walter Braunfels's
Die Voegel (The Birds) is a fascinating opera based on the play by Aristophanes.
Britten's
The Turn of the Screw is based on a ghost story by Henry James. His
Billy Budd sets the story by Herman Melville, and his
Peter Grimes tells the tragic story of a social misfit.
Schoenberg's
Moses und Aron deals with the difficulty of communicating spiritual truth.
