-- Phase 6 intersection gates. All must be 0.
-- Rebuild the post-patch audience as a temp table, then run explicit joins.

CREATE TEMPORARY TABLE _ops008_audience (
  email VARCHAR(255) NOT NULL PRIMARY KEY
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

LOAD DATA LOCAL INFILE '/home/linder8/marketing.linderlake.com/sendgrid-ops/linderlake_audience_source.txt'
INTO TABLE _ops008_audience
CHARACTER SET utf8mb4
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
(email);

CREATE TEMPORARY TABLE _final_audience (
  email VARCHAR(255) NOT NULL PRIMARY KEY
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO _final_audience(email)
SELECT DISTINCT LOWER(TRIM(c.email))
FROM contacts c
WHERE c.status = 'active'
  AND c.unsubscribed = 0
  AND c.email REGEXP '^[^@[:space:]]+@[^@[:space:]]+\\.[^@[:space:]]+$'
  AND (
    EXISTS (SELECT 1 FROM contact_list_memberships m
            WHERE m.contact_id = c.id AND m.company_name = 'LINDERLAKE')
    OR EXISTS (SELECT 1 FROM _ops008_audience a WHERE a.email = c.email)
  )
  AND NOT EXISTS (SELECT 1 FROM contact_list_memberships m
                  WHERE m.contact_id = c.id AND m.company_name = 'BCS')
  AND NOT EXISTS (SELECT 1 FROM contact_list_memberships m
                  WHERE m.contact_id = c.id
                    AND m.list_name IN (
                      '2025_BCS_P_BCS_2026_02_20','2025_BCS_P_Myrex_Competitors',
                      '2025_C_BCSENGINEERS_Clients','2025_Clients_Dlcossich','2025_Clients_Ecook'))
  AND NOT EXISTS (SELECT 1 FROM sendgrid_suppressions s
                  WHERE s.email = c.email
                    AND s.suppression_type IN
                        ('asm_unsubscribe','bounce','block','spam_report','invalid_email'))
  AND NOT EXISTS (SELECT 1 FROM contact_manual_suppressions ms
                  WHERE LOWER(TRIM(ms.email)) = LOWER(TRIM(c.email))
                    AND ms.active = 1
                    AND (ms.effective_date IS NULL OR ms.effective_date <= CURRENT_DATE));

SELECT 'FINAL_AUDIENCE_SIZE' AS metric, COUNT(*) AS value FROM _final_audience
UNION ALL
SELECT 'MANUAL_SUPPRESSIONS_IN_FINAL_AUDIENCE',
       (SELECT COUNT(*) FROM _final_audience f
          JOIN contact_manual_suppressions ms
            ON LOWER(TRIM(ms.email)) = f.email
         WHERE ms.active = 1
           AND (ms.effective_date IS NULL OR ms.effective_date <= CURRENT_DATE))
UNION ALL
SELECT 'BCS_INTERSECTION',
       (SELECT COUNT(*) FROM _final_audience f
          JOIN contacts c ON LOWER(TRIM(c.email)) = f.email
         WHERE c.company_name = 'BCS'
            OR EXISTS (SELECT 1 FROM contact_list_memberships m
                       WHERE m.contact_id = c.id AND m.company_name = 'BCS'))
UNION ALL
SELECT 'ATWORK_CONROE_INTERSECTION',
       (SELECT COUNT(*) FROM _final_audience f
          JOIN contacts c ON LOWER(TRIM(c.email)) = f.email
         WHERE c.company_name = 'ATWORK/CONROE'
            OR EXISTS (SELECT 1 FROM contact_list_memberships m
                       WHERE m.contact_id = c.id AND m.company_name = 'ATWORK/CONROE'))
UNION ALL
SELECT 'SENDGRID_SUPPRESSION_VIOLATIONS',
       (SELECT COUNT(*) FROM _final_audience f
          JOIN sendgrid_suppressions s ON s.email = f.email
         WHERE s.suppression_type IN
               ('asm_unsubscribe','bounce','block','spam_report','invalid_email'));