-- EMAIL-CLEANING-004C Phase 3 preview. Mirrors the patched eligibility
-- (post 004C, with the AND c.company_name='LINDERLAKE' gate).

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 _audience_after (
  email VARCHAR(255) NOT NULL PRIMARY KEY,
  contact_id BIGINT NOT NULL,
  company_name VARCHAR(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO _audience_after(email, contact_id, company_name)
SELECT LOWER(TRIM(c.email)), c.id, c.company_name
FROM contacts c
WHERE c.status = 'active'
  AND c.unsubscribed = 0
  AND c.email REGEXP '^[^@[:space:]]+@[^@[:space:]]+\\.[^@[:space:]]+$'
  AND c.company_name = 'LINDERLAKE'
  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))
GROUP BY LOWER(TRIM(c.email)), c.id, c.company_name;

SELECT 'ELIGIBLE_AFTER_COMPANY_GATE' AS metric, COUNT(*) AS value FROM _audience_after
UNION ALL
SELECT 'ATWORK_CONROE_INTERSECTION',
       (SELECT COUNT(*) FROM _audience_after 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 'BCS_INTERSECTION',
       (SELECT COUNT(*) FROM _audience_after 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 'MANUAL_SUPPRESSIONS_INTERSECTION',
       (SELECT COUNT(*) FROM _audience_after 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 'SENDGRID_SUPPRESSION_VIOLATIONS',
       (SELECT COUNT(*) FROM _audience_after f
          JOIN sendgrid_suppressions s ON s.email=f.email
         WHERE s.suppression_type IN
               ('asm_unsubscribe','bounce','block','spam_report','invalid_email'));