-- ####################################################
-- 📌 MAILWIZZ BLACKLIST MANAGEMENT SQL SCRIPT (UPDATED)
-- ####################################################

-- 1️⃣ PERMANENTLY BLACKLIST **HARD BOUNCES** (NON-GMAIL 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);

-- 1️⃣✅ PERMANENTLY BLACKLIST **GMAIL HARD BOUNCES (NoSuchUser)**
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%')
  AND s.email NOT IN (SELECT email FROM mw_email_blacklist);

-- 2️⃣ TEMPORARY **60-DAY SUPPRESSION** FOR "MAILBOX FULL" AND OVERQUOTA
INSERT IGNORE INTO mw_email_blacklist (email, reason, date_added, last_updated)
SELECT DISTINCT s.email, 'Mailbox full or OverQuota - 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', 'SmtpMfdFreq', 'OverQuotaTemp', 'OverQuotaPerm', 'SmtpRefused')
    OR e.message LIKE '%OverQuota%'
)
AND s.email NOT IN (SELECT email FROM mw_email_blacklist);

-- 3️⃣ 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);

-- 4️⃣ KEEP ALL **UNSUBSCRIBES** BLACKLISTED
INSERT IGNORE INTO mw_email_blacklist (email, reason, date_added, last_updated)
SELECT DISTINCT email, 'User Unsubscribed', NOW(), NOW()
FROM mw_list_subscriber
WHERE status = 'unsubscribed';

-- 5️⃣ PERMANENTLY BLACKLIST **INVALID EMAILS** (FAKE OR NON-EXISTENT)
INSERT IGNORE INTO mw_email_blacklist (email, reason, date_added, last_updated)
SELECT DISTINCT s.email, 'Invalid or Fake Email Address', 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);

-- 6️⃣ REMOVE FALSE BLACKLISTS (GMAIL-SPECIFIC RATE LIMITS & THROTTLING)
DELETE FROM mw_email_blacklist
WHERE reason LIKE '%421 4.7.28 review our bulk email senders guidelines%'
   OR reason LIKE '%unsolicited mail%'
   OR reason LIKE '%try again later%';
