SELECT s.*, p.project_code, p.question,
        (SELECT COUNT(*) FROM WEBRESEARCH_jobs j WHERE j.seed_url_id=s.id AND j.expired=0) AS bew_count,
        (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,
        (SELECT c5.email FROM WEBRESEARCH_contacts c5
    WHERE c5.seed_url_id = s.id AND COALESCE(c5.email,'')<>''
    ORDER BY (COALESCE(c5.person_name,'')<>'') DESC, COALESCE(c5.quality_score,999) ASC, c5.id ASC LIMIT 1) AS primary_email,
        (SELECT COUNT(*) FROM EMail_blocklist b5
    WHERE LOWER(b5.email) = LOWER((SELECT c5.email FROM WEBRESEARCH_contacts c5
    WHERE c5.seed_url_id = s.id AND COALESCE(c5.email,'')<>''
    ORDER BY (COALESCE(c5.person_name,'')<>'') DESC, COALESCE(c5.quality_score,999) ASC, c5.id ASC LIMIT 1))
      AND (b5.expires_at IS NULL OR b5.expires_at > NOW())) AS primary_blocked,
        (SELECT COUNT(*) FROM WEBRESEARCH_contacts c6
    WHERE c6.seed_url_id = s.id AND c6.street<>'' AND c6.postal_code<>'' AND c6.city<>'') AS has_address,
        (SELECT js.status FROM WEBRESEARCH_jobscan js WHERE js.seed_url_id=s.id ORDER BY js.scanned_at DESC LIMIT 1) AS js_status,
        (SELECT (js.opp_jobs|js.opp_digital|js.opp_tender|js.opp_consult) FROM WEBRESEARCH_jobscan js WHERE js.seed_url_id=s.id ORDER BY js.scanned_at DESC LIMIT 1) AS js_signal,
        (SELECT COUNT(*) FROM WEBRESEARCH_jobscan_queue q WHERE q.seed_url_id=s.id) AS js_queued,
        (SELECT c.city FROM WEBRESEARCH_contacts c WHERE c.seed_url_id=s.id AND COALESCE(c.city,'')<>'' ORDER BY c.id LIMIT 1) AS contact_city
    FROM WEBRESEARCH_seed_urls s
    LEFT JOIN WEBRESEARCH_projects p ON p.id = s.project_id
    WHERE s.is_legit = 1 AND s.is_craweled >= 1 AND NOT EXISTS (SELECT 1 FROM WEBRESEARCH_contacts c5 WHERE c5.seed_url_id = s.id AND LOWER(c5.email) IN ('arch.u.schneider-tego@t-online.de','mail@anika-mueller.eu','info@mtc-media.de','m.kenter@kmpro-muc.de','kanzlei@menos-gmbh.de','info@rmk-dresden.de','gregor.schwertfeger@steuerweg.com','info@wetekam.de','wp.k@deutax.com','info@vielerweb.de','julia.roelfs@roelfs-bc.de','info@assp-stb.de','sebastian.koglin@t-online.de','kontakt@immonuernberg.de','info@perfekte-wohnung.de','niesing@aol.com','info@kriemelmann-immobilien.de','info@hanno-verlag.de','info@mb-finanzplanung.de','office@investmentduo.de','info@euregiofinanz.de','n.schuemann@finanzen-kiel.de','info@pl-wirtschaftsberatung.de','info@georggerhardt.de','info@spreefinance.de','info@plan-invest.org','fa@fbn-gmbh.de','%20info@finanzberatung-rebel.de','info@fwb-kretschmer.de','info@berater-mainz.de','info@phoenix-consult.de.de','info@finanzberatung-jb.de','info@mc.thewanderinginvestor.com','gigi@realtordr.com','mail@jhgi.de','careers@ibm.com','spain@pendulum.com','karriere@oraylis.de','3997-0personal@minda.comminda','d46e55ffdd114093971dd659e5bf890b@o377622.ingest.sentry.io','kontakt@adesso-mobile.de','409de5b70a2543dd86a70596f9748d8a@o259195.ingest.sentry.io','calculatorssupport@globalcitizens.co','serviceomp@test957.com','data-protection@appliedai.de','employmentopportunities@hensoldt.net','jobs@hensoldt.net','personalne@volkswagen-groupservices.com','850info@vhs-haar.de','bauinfo@vhs-dle.de')) AND (s.project_id = 397 OR s.project_id IN (SELECT id FROM WEBRESEARCH_projects WHERE parent_project_id = 397)) AND (SELECT COUNT(*) FROM WEBRESEARCH_contacts c WHERE c.seed_url_id = s.id) > 0
    ORDER BY s.id DESC
    LIMIT 50 OFFSET 0