-- ####################################################
-- 📌 MAILWIZZ BLACKLIST MANAGEMENT SQL SCRIPT (PATCHED)
-- ####################################################
-- FIX:
-- - NEVER blacklist Gmail policy/server-level bounces (UnsolicitedMessageError / 421 4.7.28 / Bulk Sender Guidelines)
-- - Defensive exclusions by BOTH reason_classified and message text
-- - Cleanup removes polluted blacklist entries using LIKE patterns (not just exact reason matches)

-- ------------------------------------------------------------
-- 0️⃣ CLEANUP FIRST: REMOVE POLLUTED BLACKLIST ENTRIES (POLICY / RATE LIMIT)
-- ------------------------------------------------------------
DELETE FROM mw_email_blacklist
WHERE
      reason LIKE '%UnsolicitedMessageError%'
   OR reason LIKE '%support.google.com/mail/?p=%'
   OR reason LIKE '%421 4.7.28%'
   OR reason LIKE '%Bulk Email Senders Guidelines%'
   OR reason LIKE '%Gmail Policy Block%'
   OR reason LIKE '%Gmail Rate Limit%'
   OR reason LIKE '%DMARC%'
   OR reason LIKE '%policy%'
   OR reason LIKE '%rate limit%'
;

-- 1️⃣ PERMANENTLY BLACKLIST **HARD BOUNCES** (NON-GMAIL ONLY) — REAL INVALIDS ONLY
INSERT IGNORE INTO mw_email_blacklist (email, reason, date_added, last_updated)
SELECT DISTINCT s.email, 'Hard bounce - permanent failure', NOW(), NOW()
FROM mw_campaign_bounce_log e
JOIN mw_campaign_delivery_log d
    ON e.campaign_id = d.campaign_id AND e.subscriber_id = d.subscriber_id
JOIN mw_list_subscriber s
    ON e.subscriber_id = s.subscriber_id
WHERE e.bounce_type = 'hard'
  AND s.email NOT LIKE '%@gmail.com%'
  AND s.email NOT IN (SELECT email FROM mw_email_blacklist)

  -- ✅ EXCLUDE SERVER/POLICY EVENTS (even if reason_classified is NULL)
  AND COALESCE(e.reason_classified,'') NOT IN ('Gmail Policy Block', 'Gmail Rate Limit', 'Gmail DMARC/Policy')
  AND e.message NOT LIKE '%UnsolicitedMessageError%'
  AND e.message NOT LIKE '%support.google.com/mail/?p=%'
  AND e.message NOT LIKE '%421 4.7.28%'
  AND e.message NOT LIKE '%Bulk Email Senders Guidelines%'
  AND e.message NOT LIKE '%temporarily deferred%'
  AND e.message NOT LIKE '%try again later%'
;

-- 2️⃣ PERMANENTLY BLACKLIST **GMAIL HARD BOUNCES (NoSuchUser / 5.1.1) ONLY**
INSERT IGNORE INTO mw_email_blacklist (email, reason, date_added, last_updated)
SELECT DISTINCT s.email, 'Hard bounce (Gmail) - No such user', NOW(), NOW()
FROM mw_campaign_bounce_log e
JOIN mw_campaign_delivery_log d
    ON e.campaign_id = d.campaign_id AND e.subscriber_id = d.subscriber_id
JOIN mw_list_subscriber s
    ON e.subscriber_id = s.subscriber_id
WHERE e.bounce_type = 'hard'
  AND s.email LIKE '%@gmail.com%'
  AND (
        e.message LIKE '%NoSuchUser%'
     OR e.message LIKE '%550 5.1.1%'
     OR e.message LIKE '%The email account that you tried to reach does not exist%'
     OR e.message LIKE '%does not exist%'
  )
  AND s.email NOT IN (SELECT email FROM mw_email_blacklist);

-- 3️⃣ TEMPORARY **60-DAY SUPPRESSION** FOR "MAILBOX FULL" OR OVERQUOTA
INSERT IGNORE INTO mw_email_blacklist (email, reason, date_added, last_updated)
SELECT DISTINCT s.email, 'Mailbox full - Temporary 60-day suppression', NOW(), NOW()
FROM mw_campaign_bounce_log e
JOIN mw_campaign_delivery_log d
    ON e.campaign_id = d.campaign_id AND e.subscriber_id = d.subscriber_id
JOIN mw_list_subscriber s
    ON e.subscriber_id = s.subscriber_id
WHERE (
    e.failed_type IN ('SmtpFullBox', 'SmtpTempErr', 'OverQuotaTemp', 'OverQuotaPerm')
    OR e.message LIKE '%mailbox full%'
    OR e.message LIKE '%552 5.2.2%'
    OR e.message LIKE '%quota exceeded%'
    OR e.message LIKE '%over quota%'
)
AND s.email NOT IN (SELECT email FROM mw_email_blacklist);

-- 4️⃣ PERMANENTLY BLACKLIST **SPAM COMPLAINTS**
INSERT IGNORE INTO mw_email_blacklist (email, reason, date_added, last_updated)
SELECT DISTINCT s.email, 'Spam complaint', NOW(), NOW()
FROM mw_campaign_complain_log e
JOIN mw_campaign_delivery_log d
    ON e.campaign_id = d.campaign_id AND e.subscriber_id = d.subscriber_id
JOIN mw_list_subscriber s
    ON e.subscriber_id = s.subscriber_id
WHERE s.email NOT IN (SELECT email FROM mw_email_blacklist);

-- 6️⃣ PERMANENTLY BLACKLIST **FAKE / INVALID EMAIL ADDRESSES**
INSERT IGNORE INTO mw_email_blacklist (email, reason, date_added, last_updated)
SELECT DISTINCT s.email, 'Invalid or Fake Email', NOW(), NOW()
FROM mw_campaign_bounce_log e
JOIN mw_campaign_delivery_log d
    ON e.campaign_id = d.campaign_id AND e.subscriber_id = d.subscriber_id
JOIN mw_list_subscriber s
    ON e.subscriber_id = s.subscriber_id
WHERE e.failed_type IN ('SmtpNxBox', 'SysOutInvRcpt', 'SysOutDnsResolveFail')
  AND s.email NOT IN (SELECT email FROM mw_email_blacklist)

  -- ✅ EXCLUDE POLICY/RATE EVENTS SO THEY CAN'T FALL INTO "invalid/fake" BY ACCIDENT
  AND COALESCE(e.reason_classified,'') NOT IN ('Gmail Policy Block', 'Gmail Rate Limit', 'Gmail DMARC/Policy')
  AND e.message NOT LIKE '%UnsolicitedMessageError%'
  AND e.message NOT LIKE '%support.google.com/mail/?p=%'
  AND e.message NOT LIKE '%421 4.7.28%'
  AND e.message NOT LIKE '%Bulk Email Senders Guidelines%'
;

-- 7️⃣ CLEANUP: REMOVE FALSE BLACKLISTS (GMAIL POLICY ERRORS / TEMPORARY BLOCKS)
-- NOTE: Your old cleanup only deleted exact reason strings.
-- This version deletes anything that looks like policy/rate-limit contamination.
DELETE FROM mw_email_blacklist
WHERE
      reason LIKE '%UnsolicitedMessageError%'
   OR reason LIKE '%support.google.com/mail/?p=%'
   OR reason LIKE '%421 4.7.28%'
   OR reason LIKE '%Bulk Email Senders Guidelines%'
   OR reason LIKE '%Gmail Policy Block%'
   OR reason LIKE '%Gmail Rate Limit%'
   OR reason LIKE '%try again later%'
   OR reason LIKE '%temporarily deferred%';