USE strydeus_sparkmyresume;

-- Remove Activites
DELETE FROM candidate_activities_junc WHERE candidate_id IN (SELECT id FROM candidates WHERE source_id <> 1 AND prescreen_complete = 0);

-- Contact Log
DELETE FROM candidate_contact_log WHERE candidate_id IN (SELECT id FROM candidates WHERE source_id <> 1 AND prescreen_complete = 0); 

-- Meta
DELETE FROM candidate_industries_junc  WHERE candidate_id IN (SELECT id FROM candidates WHERE source_id <> 1 AND prescreen_complete = 0);

-- Job Categories
DELETE FROM candidate_job_categories_junc  WHERE candidate_id IN (SELECT id FROM candidates WHERE source_id <> 1 AND prescreen_complete = 0);

-- candidate_meta
DELETE FROM candidate_meta  WHERE candidate_id IN (SELECT id FROM candidates WHERE source_id <> 1 AND prescreen_complete = 0);

-- candidate_profile
DELETE FROM candidate_profile  WHERE candidate_id IN (SELECT id FROM candidates WHERE source_id <> 1 AND prescreen_complete = 0);

-- candidate_profile_view_log
DELETE FROM candidate_profile_view_log  WHERE candidate_id IN (SELECT id FROM candidates WHERE source_id <> 1 AND prescreen_complete = 0);

-- candidate_request_logs
DELETE FROM candidate_request_logs  WHERE candidate_id IN (SELECT id FROM candidates WHERE source_id <> 1 AND prescreen_complete = 0);

-- candidate_survey_junc
 DELETE FROM candidate_survey_junc  WHERE candidate_id IN (SELECT id FROM candidates WHERE source_id <> 1 AND prescreen_complete = 0);

 -- users
DELETE FROM users WHERE id IN (SELECT user_id FROM candidates WHERE source_id <> 1 AND prescreen_complete = 0);

CREATE TEMPORARY TABLE excludedUsers ENGINE=MEMORY AS (
    SELECT id FROM candidates WHERE source_id <> 1 AND prescreen_complete = 0
);

-- candidates
DELETE FROM candidates WHERE id IN (SELECT id FROM excludedUsers);
