OK, I've started on a script for this, and here's what it does. I would like feedback on this, but I'm also writing for my own benefit, so I can get clear idea of what is going on.
The old system is pretty crappy and suboptimal. There are two main possible situations:
1) Forum replies. In this case, the plugin loops through each group member and checks a piece of usermeta called 'bb_favorites', which is an array of topic_ids. If the current topic_id is in the user's bb_favorites, the user gets the email.
2) New topics. In this case, the plugin loops through each group member and checks to see if they are subscribed to new topics (that's stored in a piece of usermeta keyed as gfsub_x, where x is the group_id). If yes, then the new topic_id is added to the user's bb_favorites and the email is sent.
Users can unsubscribe from specific topics, which removes the topic_id from their bb_favorites.
So that's the old system. I've written a script that does the following. I figure that bb_favorites data is not really very reliable, so I'm just skipping it (more on that in a minute). In pseudocode:
foreach group x in the system {
get all values of gfsub_x (and corresponding user_ids) from the usermeta table
foreach of these user_ids {
if gfsub_x is 'yes', subscribe them. otherwise don't
}
}
This will work fine for almost everyone. The only tricky part will be people who have actively unsubscribed to specific topics. Like I said, in the old system, unsubscribing meant removal from bb_favorites (because bb_favorites is opt-in). The new system instead assumes that most people will subscribe to everything, so has a mute list instead (opt-out). In theory, I could pull up a list of every forum topic ever started for a given group, compare that list of topic ids against each member's bb_favorites, and infer the mute list from those topics missing from bb_favorites. But lots could go wrong: the original bb_favorites data could be messed up (which, as a quick glance at the database suggests, it totally is); forums could have been deleted; people could have left groups; etc.
My take: There will be very few cases in which an individual has actively unsubscribed from a topic at some point in the last few years, and that topic will be active in the future. So the chances for annoying a member are quite small, and the current solution - relying only on group-level subscription data, rather than topic-level data, is sufficient.
Feedback?