-- Confirm whether ATWORK intersection exists in the BASELINE (pre-patch) audience
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 _baseline_audience (
  email VARCHAR(255) NOT NULL PRIMARY KEY
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO _baseline_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'));

SELECT 'BASELINE_BCS_BY_CONTACT_COMPANY' AS metric,
       (SELECT COUNT(*) FROM _baseline_audience f
          JOIN contacts c ON LOWER(TRIM(c.email))=f.email WHERE c.company_name='BCS') AS value
UNION ALL
SELECT 'BASELINE_BCS_BY_MEMBERSHIP',
       (SELECT COUNT(*) FROM _baseline_audience f
          JOIN contacts c ON LOWER(TRIM(c.email))=f.email
          WHERE EXISTS (SELECT 1 FROM contact_list_memberships m WHERE m.contact_id=c.id AND m.company_name='BCS'))
UNION ALL
SELECT 'BASELINE_ATWORK_BY_CONTACT_COMPANY',
       (SELECT COUNT(*) FROM _baseline_audience f
          JOIN contacts c ON LOWER(TRIM(c.email))=f.email WHERE c.company_name='ATWORK/CONROE')
UNION ALL
SELECT 'BASELINE_ATWORK_BY_MEMBERSHIP',
       (SELECT COUNT(*) FROM _baseline_audience f
          JOIN contacts c ON LOWER(TRIM(c.email))=f.email
          WHERE EXISTS (SELECT 1 FROM contact_list_memberships m WHERE m.contact_id=c.id AND m.company_name='ATWORK/CONROE'))
UNION ALL
SELECT 'BASELINE_SENDGRID_SUP',
       (SELECT COUNT(*) FROM _baseline_audience f
          JOIN sendgrid_suppressions s ON s.email=f.email
          WHERE s.suppression_type IN ('asm_unsubscribe','bounce','block','spam_report','invalid_email'));