-- ####################################################
-- 📌 MAILWIZZ BLACKLIST MANAGEMENT SQL SCRIPT (FINAL FIX)
-- ####################################################

-- 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)
  AND e.reason_classified NOT IN ('Gmail Policy Block', 'Gmail Rate Limit');

-- 2️⃣ PERMANENTLY BLACKLIST **GMAIL HARD BOUNCES (NoSuchUser) 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%')
  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%'
)
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);

-- 5️⃣ BLACKLIST ALL **UNSUBSCRIBES**
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';

-- 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);

-- 7️⃣ CLEANUP: REMOVE FALSE BLACKLISTS (Gmail Policy Errors / Temporary Blocks)
DELETE FROM mw_email_blacklist
WHERE reason IN (
  '421 4.7.28 review our bulk email senders guidelines',
  'unsolicited mail',
  'try again later',
  'Gmail Policy Block',
  'Gmail Rate Limit'
);
