-- EMAIL-CLEANING-004C Phase 1 root cause (READ-ONLY).
-- Classify every ATWORK/CONROE contact in the post-004B eligible audience
-- according to how it currently qualifies.

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

-- Post-004B eligible audience (identical to EMAIL-CLEANING-004B post-patch).
CREATE TEMPORARY TABLE _audience (
  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(email, contact_id, company_name)
SELECT LOWER(TRIM(c.email)) AS 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 (
    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 'AUDIENCE_TOTAL' AS metric, COUNT(*) AS value FROM _audience
UNION ALL
SELECT 'AUDIENCE_LINDERLAKE', COUNT(*) FROM _audience WHERE company_name='LINDERLAKE'
UNION ALL
SELECT 'AUDIENCE_ATWORK_CONROE', COUNT(*) FROM _audience WHERE company_name='ATWORK/CONROE'
UNION ALL
SELECT 'AUDIENCE_BCS', COUNT(*) FROM _audience WHERE company_name='BCS';

-- Classify the 4022 ATWORK leakers by how they qualify
SELECT
  'ATWORK_A_LL_MEMBERSHIP_ONLY' AS bucket,
  COUNT(*) AS value
FROM _audience a
WHERE a.company_name='ATWORK/CONROE'
  AND     EXISTS (SELECT 1 FROM contact_list_memberships m
                  WHERE m.contact_id=a.contact_id AND m.company_name='LINDERLAKE')
  AND NOT EXISTS (SELECT 1 FROM _ops008_audience s WHERE s.email=a.email)
UNION ALL
SELECT 'ATWORK_B_ALLOW_SET_ONLY',
  COUNT(*)
FROM _audience a
WHERE a.company_name='ATWORK/CONROE'
  AND NOT EXISTS (SELECT 1 FROM contact_list_memberships m
                  WHERE m.contact_id=a.contact_id AND m.company_name='LINDERLAKE')
  AND     EXISTS (SELECT 1 FROM _ops008_audience s WHERE s.email=a.email)
UNION ALL
SELECT 'ATWORK_C_BOTH',
  COUNT(*)
FROM _audience a
WHERE a.company_name='ATWORK/CONROE'
  AND     EXISTS (SELECT 1 FROM contact_list_memberships m
                  WHERE m.contact_id=a.contact_id AND m.company_name='LINDERLAKE')
  AND     EXISTS (SELECT 1 FROM _ops008_audience s WHERE s.email=a.email)
UNION ALL
SELECT 'ATWORK_D_NEITHER_SHOULD_NOT_HAPPEN',
  COUNT(*)
FROM _audience a
WHERE a.company_name='ATWORK/CONROE'
  AND NOT EXISTS (SELECT 1 FROM contact_list_memberships m
                  WHERE m.contact_id=a.contact_id AND m.company_name='LINDERLAKE')
  AND NOT EXISTS (SELECT 1 FROM _ops008_audience s WHERE s.email=a.email);

-- Which LINDERLAKE lists are these ATWORK contacts attached to? (top names)
SELECT 'TOP_LL_LISTS_FOR_ATWORK_LEAKERS' AS metric,
       m.list_name AS list_name,
       COUNT(DISTINCT a.email) AS atwork_distinct
FROM _audience a
JOIN contact_list_memberships m
  ON m.contact_id = a.contact_id AND m.company_name = 'LINDERLAKE'
WHERE a.company_name='ATWORK/CONROE'
GROUP BY m.list_name
ORDER BY atwork_distinct DESC
LIMIT 25;