Paihia is an ok town, average beach, ok bars (try the Becahhouse cafe/bar and if you like a game of pool, the Sandpit right behind it is really good), good takeaways (One Hot Tuna on the waterront by the pier), and ok walks (try going along the coast south, should reach Opua in 2-3 hours, you can get a taxi back from there).
For the 'proper' Bay of Islands experience, make sure you get out into the islands. When you get out there you'll start to feel why the Bay is a popular holiday destination - they are beautiful. Although every tour goes on about the '144 islands' theres basically around 7-10 biggish ones. But they are spaced out enough that you'll likely have an island to yourself for some time at least.
If you can sail I'd recommend renting a boat (Great Escape Yacht Charters in Opua) or a motorboat (Darryls Dinner Cruise in Paihia Wharf - yes they also do a highly recommended evening cruise up the river to Haruru Falls). Alternatively one of the day sailing trips is also recommended (Gungha/She's a Lady for a feel of a 'life at sea' or one of the Sail NZ boats for a less comfy but faster ride.
If your mobility is reduced or you don't feel like doing much (or maybe your time is limited) jump on a fullers or kings cruise, they go to the over-hyped holr in the rock a couple times a day (the trip there is worth it, though the hole is nothing special). For more adrenaline and a faster ride I'd recommend the Mack Attack speed boat.
I spent a few days in the Bay and really enjoyed it. I found a great site for general bay of islands information (things to do and places to stay): bayofislands.net
Fondest memory: what a view ! it's definetly a must to spend some days in this little township up north in the north island.