INSERT INTO wmf_eoy_receipt_donor (year, email, status) SELECT 2024, email.email, 'queued'       
 FROM civicrm_contribution_recur contribution_recur               
 INNER JOIN civicrm_contribution contribution                           ON contribution.contribution_recur_id =
contribution_recur.id    
           INNER JOIN civicrm_email email                           
ON email.contact_id = contribution.contact_id
                     AND email.is_primary          
      INNER JOIN civicrm_contact contact ON contact.id = email.contact_id           
AND contact.is_deleted = 0              
  LEFT JOIN wmf_eoy_receipt_donor eoy ON email.email = eoy.email AND eoy.year = 2024       WHERE receive_date BETWEEN '2024-01-01 10:00:00' AND '2025-01-01 09:59:59'         AND contribution.contribution_status_id = 1         AND eoy.email IS NULL            AND contribution_recur.frequency_unit != 'year'     
 GROUP BY email.email;

INSERT INTO wmf_eoy_receipt_donor (year, email, status)
SELECT 2024, email, 'queued'
FROM (SELECT email.email, count(*) as c
      FROM civicrm_contribution_recur contribution_recur
               INNER JOIN civicrm_contribution contribution
                          ON contribution.contribution_recur_id = contribution_recur.id
               INNER JOIN civicrm_email email
                          ON email.contact_id = contribution.contact_id
                              AND email.is_primary
               INNER JOIN civicrm_contact contact ON contact.id = email.contact_id
          AND contact.is_deleted = 0
               LEFT JOIN wmf_eoy_receipt_donor eoy ON email.email = eoy.email AND eoy.year = 2024
      WHERE receive_date BETWEEN '2024-01-01 10:00:00' AND '2025-01-01 09:59:59'
        AND contribution.contribution_status_id = 1
        AND eoy.email IS NULL
-- We exclude annual recurring contributions when deciding WHO to email.
-- if they have an annual recurring AND a monthly then both (all) donations
-- will still be included in the WHAT to email.
        AND contribution_recur.frequency_unit != 'year'
      GROUP BY email.email
      HAVING c > 1) as emails

DELETE FROM wmf_eoy_receipt_donor WHERE email IN (
SELECT email FROM
                 (
SELECT d.email, count(c.id) as n FROM wmf_eoy_receipt_donor d
LEFT JOIN civicrm_email e ON e.email = d.email
    AND is_primary = 1
LEFT JOIN civicrm_contribution c ON e.contact_id = c.contact_id
  AND receive_date BETWEEN '2024-01-01 10:00:00' AND '2025-01-01 09:59:59'
  AND contribution_status_id = 1
  GROUP BY d.email
HAVING n < 2) as m);

CREATE TABLE wmf_eoy_no_email (
    `email` varchar(254) DEFAULT NULL,
    INDEX email(email));

INSERT INTO wmf_eoy_no_email
SELECT email FROM
                 (
SELECT d.email, count(c.id) as n FROM wmf_eoy_receipt_donor d
LEFT JOIN civicrm_email e ON e.email = d.email
    AND is_primary = 1
-- this is a cheap way to drop out the ones that are on hold.
    AND on_hold = 0
LEFT JOIN civicrm_contribution c ON e.contact_id = c.contact_id
  AND receive_date BETWEEN '2024-01-01 10:00:00' AND '2025-01-01 09:59:59'
  AND contribution_status_id = 1
  GROUP BY d.email
HAVING n < 2) as m;

--- Query OK, 60242 rows affected (3 min 19.405 sec)


DELETE d FROM wmf_eoy_receipt_donor d INNER JOIN wmf_eoy_no_email e ON e.email = d.email;

--- Query OK, 60242 rows affected (1.423 sec)

DROP TABLE wmf_eoy_no_email;