logging report: groups handling

we ran across some data scenarios that affect the listing of groups in the contact changelog (advanced logging). time should be tracked against NYSS #6056. here are steps to reproduce:

• contact was added to group A – associated log record created
• group A was deleted – log record indicating contact was removed from group A correctly created
• group B was created – with the same id as group A. that's unusual, but can happen. the innodb auto_increment field is stored in memory, not on disk. if the server is restarted, the auto_increment is reset to be max(id) + 1. that appears to be what happened.
• when you review the contact's log history, it shows they were added/removed from group B – even though they were never actually part of that group.

and on a side note:

• if contact is added to group A and then group A is deleted – the log history shows the group log record but doesn't show the group name (because it's connected to the main group table, not the log group table)

so... not sure the best way to handle this. I see a couple options –

• we implement a group trashing concept so that we're never really deleting groups. I suspect that would be significant work
• we devise a more accurate join between the group_contact log table and the group log table that joins based on the "most recent group log record prior to the group_contact log record". in other words...

• if group A is titled "my cool group"
• I add contact X to group A
• I change group A to be titled "my super cool group"
• the log record should still reflect the name of the group at the time the contact was added

that will require a subtable join instead of a straight join, but we could just do something like:
SELECT title
FROM civicrm_group
WHERE log_civicrm_group.id = groupID
AND log_civicrm_group.log_date <= groupContactLogDate
ORDER BY log_civicrm_group.log_date DESC
LIMIT 1