mailing_prevent_resend.sql

DROP PROCEDURE IF EXISTS mailing_prevent_resend;

DELIMITER //

CREATE PROCEDURE mailing_prevent_resend
(target_mailing_id INT, prevent_resend BOOLEAN, target_promotion_id INT)

BEGIN

DROP TEMPORARY TABLE IF EXISTS promotion_attempt_emails;
CREATE TEMPORARY TABLE promotion_attempt_emails
SELECT audience_members.email
FROM audience_members
INNER JOIN promotion_attempts ON promotion_attempts.audience_member_id = audience_members.id
WHERE promotion_attempts.mailing_id = target_mailing_id;

DROP TEMPORARY TABLE IF EXISTS audience_list_emails;
CREATE TEMPORARY TABLE audience_list_emails
SELECT audience_members.email
FROM audience_members
INNER JOIN memberships ON memberships.audience_member_id = audience_members.id
INNER JOIN audience_lists_mailings ON audience_lists_mailings.audience_list_id = memberships.audience_list_id
WHERE audience_lists_mailings.mailing_id = target_mailing_id;

DROP TEMPORARY TABLE IF EXISTS resulting;
CREATE TEMPORARY TABLE resulting
SELECT email FROM promotion_attempt_emails;

INSERT INTO resulting
SELECT email FROM audience_list_emails
ON DUPLICATE KEY UPDATE resulting.email = audience_list_emails.email;

IF prevent_resend = 1 THEN

	DROP TEMPORARY TABLE IF EXISTS excluded;
	CREATE TEMPORARY TABLE excluded
	SELECT promotion_attempts.email
	FROM promotion_attempts
	INNER JOIN mailings ON mailings.id = promotion_attempts.mailing_id
	WHERE promotion_attempts.state IN ('failed', 'sent', 'received', 'clicked_through', 'bounced', 'retried', 'retry_failed', 'forwarded', 'opted_out', 'abused')
	AND mailings.promotion_id = target_promotion_id;

	CREATE INDEX email_on_excluded ON excluded(email);
	CREATE INDEX email_on_resulting ON resulting(email);
	
	DELETE resulting FROM resulting, excluded WHERE excluded.email = resulting.email;
END IF;

-- callers can now use the 'resulting' table

END;
//

DELIMITER ;