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

SELECT DISTINCT LOWER(TRIM(c.email)) AS 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)
  )
ORDER BY email;