SELECT s.*, p.project_code, p.question,
        (SELECT COUNT(*) FROM WEBRESEARCH_contacts c WHERE c.seed_url_id = s.id) AS contact_count,
        EXISTS(SELECT 1 FROM WEBRESEARCH_contacts c WHERE c.seed_url_id = s.id AND COALESCE(c.email,'') <> '') AS has_email,
        EXISTS(SELECT 1 FROM WEBRESEARCH_contacts c WHERE c.seed_url_id = s.id AND COALESCE(c.phone,'') <> '') AS has_phone,
        (SELECT COUNT(*) FROM COMMUNICATION_EVENTS_events e
    JOIN WEBRESEARCH_contacts c2 ON c2.id = e.contact_id
    WHERE c2.seed_url_id = s.id AND e.channel = 'email' AND e.sent_at IS NOT NULL) AS attempt_count,
        (SELECT COUNT(DISTINCT c3.id) FROM WEBRESEARCH_contacts c3
    JOIN EMail_blocklist b3 ON LOWER(b3.email) = LOWER(c3.email)
    WHERE c3.seed_url_id = s.id
      AND (b3.expires_at IS NULL OR b3.expires_at > NOW())) AS blocked_count,
        (SELECT c4.email FROM WEBRESEARCH_contacts c4
    JOIN EMail_blocklist b4 ON LOWER(b4.email) = LOWER(c4.email)
    WHERE c4.seed_url_id = s.id
      AND (b4.expires_at IS NULL OR b4.expires_at > NOW())
    ORDER BY c4.id ASC LIMIT 1) AS blocked_sample_email
    FROM WEBRESEARCH_seed_urls s
    LEFT JOIN WEBRESEARCH_projects p ON p.id = s.project_id
    WHERE s.is_legit = 0 AND s.is_craweled >= 1 AND s.is_craweled >= 1 AND NOT EXISTS (SELECT 1 FROM EMail_blocklist b
                    JOIN WEBRESEARCH_contacts c5 ON LOWER(b.email) = LOWER(c5.email)
                    WHERE c5.seed_url_id = s.id
                      AND (b.expires_at IS NULL OR b.expires_at > NOW())) AND s.project_id IN (SELECT id FROM WEBRESEARCH_projects WHERE status='active')
    ORDER BY s.id DESC
    LIMIT 50 OFFSET 0