-- Remove campaigns older than 6 months from phpList including all stats.
-- Start transaction
START TRANSACTION;

-- Delete user-message links for campaigns older than 6 months
DELETE FROM phplist_usermessage
WHERE messageid IN (
    SELECT id FROM phplist_message WHERE DATE_ADD(entered, INTERVAL 6 MONTH) < NOW()
);

-- Delete link tracking data for campaigns older than 6 months
DELETE FROM phplist_linktrack
WHERE messageid IN (
    SELECT id FROM phplist_message WHERE DATE_ADD(entered, INTERVAL 6 MONTH) < NOW()
);

-- Delete bounce data for campaigns older than 6 months
DELETE FROM phplist_bounce
WHERE messageid IN (
    SELECT id FROM phplist_message WHERE DATE_ADD(entered, INTERVAL 6 MONTH) < NOW()
);

-- Delete event log entries for campaigns older than 6 months
DELETE FROM phplist_eventlog
WHERE messageid IN (
    SELECT id FROM phplist_message WHERE DATE_ADD(entered, INTERVAL 6 MONTH) < NOW()
);

-- Delete the campaigns themselves that are older than 6 months
DELETE FROM phplist_message
WHERE DATE_ADD(entered, INTERVAL 6 MONTH) < NOW();

-- Commit the transaction
COMMIT;