-- ============================================
-- COMPLETE PSS SAMPLE DATA SEEDER - SQL VERSION
-- ============================================

-- Disable foreign key checks
SET FOREIGN_KEY_CHECKS = 0;

-- ============================================
-- CLEAN EXISTING DATA (Run if needed)
-- ============================================
-- TRUNCATE TABLE pss_matches;
-- TRUNCATE TABLE pss_searches;
-- TRUNCATE TABLE candidate_skills;
-- TRUNCATE TABLE candidate_preferences;
-- TRUNCATE TABLE candidates;
-- TRUNCATE TABLE job_seekers;
-- DELETE FROM users WHERE user_type = 'job_seeker';

-- ============================================
-- INSERT USERS (Job Seekers)
-- ============================================

INSERT INTO `users` (`name`, `email`, `password`, `user_type`, `is_active`, `created_at`, `updated_at`) VALUES
('Emily Clark', 'emily.clark@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'job_seeker', 1, NOW(), NOW()),
('Daniel Okonkwo', 'daniel.okonkwo@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'job_seeker', 1, NOW(), NOW()),
('Patricia Okafor', 'patricia.okafor@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'job_seeker', 1, NOW(), NOW()),
('Stephen Ibrahim', 'stephen.ibrahim@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'job_seeker', 1, NOW(), NOW()),
('Grace Uche', 'grace.uche@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'job_seeker', 1, NOW(), NOW()),
('Oluwaseun Adebayo', 'oluwaseun.adebayo@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'job_seeker', 1, NOW(), NOW()),
('Adeola Williams', 'adeola.williams@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'job_seeker', 1, NOW(), NOW()),
('Chidi Eze', 'chidi.eze@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'job_seeker', 1, NOW(), NOW()),
('Funke Ogunleye', 'funke.ogunleye@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'job_seeker', 1, NOW(), NOW()),
('Tunde Bakare', 'tunde.bakare@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'job_seeker', 1, NOW(), NOW()),
('Ngozi Okeke', 'ngozi.okeke@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'job_seeker', 1, NOW(), NOW()),
('Emeka Nwosu', 'emeka.nwosu@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'job_seeker', 1, NOW(), NOW()),
('Chioma Okonkwo', 'chioma.okonkwo@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'job_seeker', 1, NOW(), NOW()),
('Uche Eze', 'uche.eze@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'job_seeker', 1, NOW(), NOW()),
('Bola Adeyemi', 'bola.adeyemi@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'job_seeker', 1, NOW(), NOW()),
('Kunle Ogunbiyi', 'kunle.ogunbiyi@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'job_seeker', 1, NOW(), NOW()),
('Femi Adebisi', 'femi.adebisi@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'job_seeker', 1, NOW(), NOW()),
('Tayo Alabi', 'tayo.alabi@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'job_seeker', 1, NOW(), NOW()),
('Segun Ogunlana', 'segun.ogunlana@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'job_seeker', 1, NOW(), NOW()),
('Amina Mohammed', 'amina.mohammed@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'job_seeker', 1, NOW(), NOW()),
('Bisi Ademola', 'bisi.ademola@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'job_seeker', 1, NOW(), NOW()),
('Ifeanyi Okonkwo', 'ifeanyi.okonkwo@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'job_seeker', 1, NOW(), NOW()),
('Zainab Abubakar', 'zainab.abubakar@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'job_seeker', 1, NOW(), NOW());

-- ============================================
-- GET USER IDs for reference
-- ============================================
-- This creates a temporary table to store user IDs by email
CREATE TEMPORARY TABLE temp_user_ids (
    email VARCHAR(255),
    user_id INT
);

INSERT INTO temp_user_ids (email, user_id)
SELECT email, id FROM users WHERE user_type = 'job_seeker';

-- ============================================
-- INSERT JOB SEEKERS (Linked to users)
-- ============================================

INSERT INTO `job_seekers` (`user_id`, `first_name`, `last_name`, `email`, `headline`, `industry`, `city`, `years_experience`, `expected_salary`, `pss_visible`, `is_searchable`, `bio`, `profile_completion_percentage`, `right_to_work_status`, `employment_type`, `desired_job_title`, `created_at`, `updated_at`) VALUES
((SELECT user_id FROM temp_user_ids WHERE email = 'emily.clark@example.com'), 'Emily', 'Clark', 'emily.clark@example.com', 'Registered Nurse', 'Healthcare', 'London', 7, 42000, 1, 1, 'Registered nurse with 7 years experience in acute care and emergency departments.', 92, 'british_citizen', 'full_time', 'Registered Nurse', NOW(), NOW()),
((SELECT user_id FROM temp_user_ids WHERE email = 'daniel.okonkwo@example.com'), 'Daniel', 'Okonkwo', 'daniel.okonkwo@example.com', 'Senior Care Assistant', 'Healthcare', 'Birmingham', 6, 26000, 1, 1, 'Dedicated senior care assistant specializing in dementia and elderly care.', 88, 'british_citizen', 'full_time', 'Senior Care Assistant', NOW(), NOW()),
((SELECT user_id FROM temp_user_ids WHERE email = 'patricia.okafor@example.com'), 'Patricia', 'Okafor', 'patricia.okafor@example.com', 'Pharmacy Technician', 'Healthcare', 'Manchester', 4, 28000, 1, 1, 'Qualified pharmacy technician with hospital and community pharmacy experience.', 85, 'british_citizen', 'full_time', 'Pharmacy Technician', NOW(), NOW()),
((SELECT user_id FROM temp_user_ids WHERE email = 'stephen.ibrahim@example.com'), 'Stephen', 'Ibrahim', 'stephen.ibrahim@example.com', 'Mental Health Support Worker', 'Healthcare', 'Leeds', 5, 27000, 1, 1, 'Mental health support worker passionate about helping individuals with mental health challenges.', 86, 'british_citizen', 'full_time', 'Mental Health Support Worker', NOW(), NOW()),
((SELECT user_id FROM temp_user_ids WHERE email = 'grace.uche@example.com'), 'Grace', 'Uche', 'grace.uche@example.com', 'Occupational Therapist', 'Healthcare', 'Bristol', 8, 45000, 1, 1, 'Experienced occupational therapist specializing in elderly and disability rehabilitation.', 94, 'british_citizen', 'full_time', 'Occupational Therapist', NOW(), NOW()),
((SELECT user_id FROM temp_user_ids WHERE email = 'oluwaseun.adebayo@example.com'), 'Oluwaseun', 'Adebayo', 'oluwaseun.adebayo@example.com', 'Site Manager', 'Construction', 'London', 10, 58000, 1, 1, 'Experienced site manager with 10 years overseeing residential and commercial projects.', 96, 'british_citizen', 'full_time', 'Site Manager', NOW(), NOW()),
((SELECT user_id FROM temp_user_ids WHERE email = 'adeola.williams@example.com'), 'Adeola', 'Williams', 'adeola.williams@example.com', 'Quantity Surveyor', 'Construction', 'Manchester', 7, 52000, 1, 1, 'Chartered quantity surveyor with expertise in cost management and contract administration.', 90, 'british_citizen', 'full_time', 'Quantity Surveyor', NOW(), NOW()),
((SELECT user_id FROM temp_user_ids WHERE email = 'chidi.eze@example.com'), 'Chidi', 'Eze', 'chidi.eze@example.com', 'Civil Engineer', 'Construction', 'Birmingham', 6, 48000, 1, 1, 'Civil engineer with strong background in structural design and infrastructure projects.', 89, 'british_citizen', 'full_time', 'Civil Engineer', NOW(), NOW()),
((SELECT user_id FROM temp_user_ids WHERE email = 'funke.ogunleye@example.com'), 'Funke', 'Ogunleye', 'funke.ogunleye@example.com', 'Store Manager', 'Retail', 'Liverpool', 8, 38000, 1, 1, 'Store manager with proven track record in increasing sales and improving customer satisfaction.', 91, 'british_citizen', 'full_time', 'Store Manager', NOW(), NOW()),
((SELECT user_id FROM temp_user_ids WHERE email = 'tunde.bakare@example.com'), 'Tunde', 'Bakare', 'tunde.bakare@example.com', 'Retail Assistant', 'Retail', 'Sheffield', 3, 22000, 1, 1, 'Friendly retail assistant with strong customer service skills and attention to detail.', 82, 'british_citizen', 'full_time', 'Retail Assistant', NOW(), NOW()),
((SELECT user_id FROM temp_user_ids WHERE email = 'ngozi.okeke@example.com'), 'Ngozi', 'Okeke', 'ngozi.okeke@example.com', 'Financial Analyst', 'Finance', 'London', 5, 55000, 1, 1, 'CFA Level 2 candidate with strong financial analysis and modeling skills.', 93, 'british_citizen', 'full_time', 'Financial Analyst', NOW(), NOW()),
((SELECT user_id FROM temp_user_ids WHERE email = 'emeka.nwosu@example.com'), 'Emeka', 'Nwosu', 'emeka.nwosu@example.com', 'Accountant', 'Finance', 'Manchester', 6, 42000, 1, 1, 'ACCA qualified accountant with expertise in SME accounting and tax compliance.', 94, 'british_citizen', 'full_time', 'Accountant', NOW(), NOW()),
((SELECT user_id FROM temp_user_ids WHERE email = 'chioma.okonkwo@example.com'), 'Chioma', 'Okonkwo', 'chioma.okonkwo@example.com', 'Paralegal', 'Legal', 'Birmingham', 4, 32000, 1, 1, 'Paralegal with experience in family law and civil litigation.', 87, 'british_citizen', 'full_time', 'Paralegal', NOW(), NOW()),
((SELECT user_id FROM temp_user_ids WHERE email = 'uche.eze@example.com'), 'Uche', 'Eze', 'uche.eze@example.com', 'Legal Assistant', 'Legal', 'Leeds', 3, 26000, 1, 1, 'Organized legal assistant supporting solicitors in corporate and commercial law.', 84, 'british_citizen', 'full_time', 'Legal Assistant', NOW(), NOW()),
((SELECT user_id FROM temp_user_ids WHERE email = 'bola.adeyemi@example.com'), 'Bola', 'Adeyemi', 'bola.adeyemi@example.com', 'Production Supervisor', 'Manufacturing', 'Coventry', 9, 45000, 1, 1, 'Production supervisor with Lean Six Sigma Green Belt certification.', 92, 'british_citizen', 'full_time', 'Production Supervisor', NOW(), NOW()),
((SELECT user_id FROM temp_user_ids WHERE email = 'kunle.ogunbiyi@example.com'), 'Kunle', 'Ogunbiyi', 'kunle.ogunbiyi@example.com', 'Quality Assurance Officer', 'Manufacturing', 'Nottingham', 5, 35000, 1, 1, 'QA officer ensuring compliance with ISO 9001 standards and continuous improvement.', 88, 'british_citizen', 'full_time', 'Quality Assurance Officer', NOW(), NOW()),
((SELECT user_id FROM temp_user_ids WHERE email = 'femi.adebisi@example.com'), 'Femi', 'Adebisi', 'femi.adebisi@example.com', 'Warehouse Manager', 'Logistics', 'Bristol', 7, 42000, 1, 1, 'Warehouse manager with experience in FMCG and e-commerce logistics.', 90, 'british_citizen', 'full_time', 'Warehouse Manager', NOW(), NOW()),
((SELECT user_id FROM temp_user_ids WHERE email = 'tayo.alabi@example.com'), 'Tayo', 'Alabi', 'tayo.alabi@example.com', 'Forklift Operator', 'Logistics', 'Southampton', 4, 26000, 1, 1, 'Certified forklift operator with counterbalance and reach truck licenses.', 82, 'british_citizen', 'full_time', 'Forklift Operator', NOW(), NOW()),
((SELECT user_id FROM temp_user_ids WHERE email = 'segun.ogunlana@example.com'), 'Segun', 'Ogunlana', 'segun.ogunlana@example.com', 'Security Supervisor', 'Security', 'London', 8, 38000, 1, 1, 'SIA licensed security supervisor with experience in corporate and retail security.', 91, 'british_citizen', 'full_time', 'Security Supervisor', NOW(), NOW()),
((SELECT user_id FROM temp_user_ids WHERE email = 'amina.mohammed@example.com'), 'Amina', 'Mohammed', 'amina.mohammed@example.com', 'Close Protection Officer', 'Security', 'Manchester', 6, 45000, 1, 1, 'SIA licensed Close Protection Officer with VIP and executive protection experience.', 93, 'british_citizen', 'full_time', 'Close Protection Officer', NOW(), NOW()),
((SELECT user_id FROM temp_user_ids WHERE email = 'bisi.ademola@example.com'), 'Bisi', 'Ademola', 'bisi.ademola@example.com', 'CCTV Operator', 'Security', 'Birmingham', 4, 28000, 1, 1, 'SIA licensed CCTV operator experienced in control room operations.', 85, 'british_citizen', 'full_time', 'CCTV Operator', NOW(), NOW()),
((SELECT user_id FROM temp_user_ids WHERE email = 'ifeanyi.okonkwo@example.com'), 'Ifeanyi', 'Okonkwo', 'ifeanyi.okonkwo@example.com', 'Hotel Manager', 'Hospitality', 'Edinburgh', 9, 52000, 1, 1, 'Hotel manager with 9 years experience in 4-star luxury hotels.', 95, 'british_citizen', 'full_time', 'Hotel Manager', NOW(), NOW()),
((SELECT user_id FROM temp_user_ids WHERE email = 'zainab.abubakar@example.com'), 'Zainab', 'Abubakar', 'zainab.abubakar@example.com', 'Head Chef', 'Hospitality', 'Glasgow', 10, 48000, 1, 1, 'Award-winning head chef specializing in modern British and international cuisine.', 94, 'british_citizen', 'full_time', 'Head Chef', NOW(), NOW());

-- ============================================
-- INSERT CANDIDATES (Linked to job_seekers)
-- ============================================

INSERT INTO `candidates` (`job_seeker_id`, `user_id`, `first_name`, `last_name`, `email`, `headline`, `industry`, `city`, `years_experience`, `expected_salary`, `pss_visible`, `is_searchable`, `bio`, `profile_completion_percentage`, `right_to_work_status`, `employment_type`, `desired_job_title`, `pds_active`, `pds_avg_rating`, `completed_pds_jobs`, `match_priority`, `is_relocatable`, `profile_complete`, `created_at`, `updated_at`)
SELECT 
    js.id, 
    js.user_id, 
    js.first_name, 
    js.last_name, 
    js.email, 
    js.headline, 
    js.industry, 
    js.city, 
    js.years_experience, 
    js.expected_salary, 
    js.pss_visible, 
    js.is_searchable, 
    js.bio, 
    js.profile_completion_percentage, 
    js.right_to_work_status, 
    js.employment_type, 
    js.desired_job_title, 
    0, 
    0.00, 
    0, 
    50, 
    0, 
    0, 
    NOW(), 
    NOW()
FROM job_seekers js
LEFT JOIN candidates c ON c.job_seeker_id = js.id
WHERE c.id IS NULL;

-- ============================================
-- INSERT CANDIDATE SKILLS (5 skills per candidate)
-- ============================================

-- Healthcare: Emily Clark
INSERT INTO `candidate_skills` (`candidate_id`, `job_seeker_id`, `skill_name`, `years_experience`, `proficiency`, `is_certified`, `created_at`, `updated_at`)
SELECT c.id, c.job_seeker_id, 'Patient Assessment', 7, 'advanced', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'emily.clark@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'Medication Administration', 7, 'advanced', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'emily.clark@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'Wound Care', 7, 'advanced', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'emily.clark@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'EMR Systems', 5, 'intermediate', 0, NOW(), NOW() FROM candidates c WHERE c.email = 'emily.clark@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'Emergency Response', 7, 'advanced', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'emily.clark@example.com';

-- Healthcare: Daniel Okonkwo
INSERT INTO `candidate_skills` (`candidate_id`, `job_seeker_id`, `skill_name`, `years_experience`, `proficiency`, `is_certified`, `created_at`, `updated_at`)
SELECT c.id, c.job_seeker_id, 'Elderly Care', 6, 'advanced', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'daniel.okonkwo@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'Dementia Support', 4, 'intermediate', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'daniel.okonkwo@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'Personal Care', 6, 'advanced', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'daniel.okonkwo@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'Moving & Handling', 6, 'advanced', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'daniel.okonkwo@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'Nutrition', 5, 'intermediate', 0, NOW(), NOW() FROM candidates c WHERE c.email = 'daniel.okonkwo@example.com';

-- Healthcare: Patricia Okafor
INSERT INTO `candidate_skills` (`candidate_id`, `job_seeker_id`, `skill_name`, `years_experience`, `proficiency`, `is_certified`, `created_at`, `updated_at`)
SELECT c.id, c.job_seeker_id, 'Dispensing', 4, 'advanced', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'patricia.okafor@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'Medication Management', 4, 'advanced', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'patricia.okafor@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'Stock Control', 4, 'intermediate', 0, NOW(), NOW() FROM candidates c WHERE c.email = 'patricia.okafor@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'Customer Service', 4, 'advanced', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'patricia.okafor@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'NHS Systems', 3, 'intermediate', 0, NOW(), NOW() FROM candidates c WHERE c.email = 'patricia.okafor@example.com';

-- Healthcare: Stephen Ibrahim
INSERT INTO `candidate_skills` (`candidate_id`, `job_seeker_id`, `skill_name`, `years_experience`, `proficiency`, `is_certified`, `created_at`, `updated_at`)
SELECT c.id, c.job_seeker_id, 'Counselling', 5, 'advanced', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'stephen.ibrahim@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'Crisis Intervention', 4, 'advanced', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'stephen.ibrahim@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'Safeguarding', 5, 'advanced', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'stephen.ibrahim@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'Care Planning', 4, 'intermediate', 0, NOW(), NOW() FROM candidates c WHERE c.email = 'stephen.ibrahim@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'Group Therapy', 3, 'intermediate', 0, NOW(), NOW() FROM candidates c WHERE c.email = 'stephen.ibrahim@example.com';

-- Healthcare: Grace Uche
INSERT INTO `candidate_skills` (`candidate_id`, `job_seeker_id`, `skill_name`, `years_experience`, `proficiency`, `is_certified`, `created_at`, `updated_at`)
SELECT c.id, c.job_seeker_id, 'Rehabilitation', 8, 'expert', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'grace.uche@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'Adaptive Equipment', 6, 'advanced', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'grace.uche@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'Patient Assessment', 8, 'expert', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'grace.uche@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'Home Visits', 6, 'advanced', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'grace.uche@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'Report Writing', 7, 'advanced', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'grace.uche@example.com';

-- Construction: Oluwaseun Adebayo
INSERT INTO `candidate_skills` (`candidate_id`, `job_seeker_id`, `skill_name`, `years_experience`, `proficiency`, `is_certified`, `created_at`, `updated_at`)
SELECT c.id, c.job_seeker_id, 'Site Supervision', 10, 'expert', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'oluwaseun.adebayo@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'Health & Safety', 10, 'expert', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'oluwaseun.adebayo@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'Project Planning', 8, 'advanced', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'oluwaseun.adebayo@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'Team Leadership', 8, 'advanced', 0, NOW(), NOW() FROM candidates c WHERE c.email = 'oluwaseun.adebayo@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'CSCS Card', 10, 'expert', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'oluwaseun.adebayo@example.com';

-- Construction: Adeola Williams
INSERT INTO `candidate_skills` (`candidate_id`, `job_seeker_id`, `skill_name`, `years_experience`, `proficiency`, `is_certified`, `created_at`, `updated_at`)
SELECT c.id, c.job_seeker_id, 'Cost Estimation', 7, 'advanced', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'adeola.williams@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'Contract Management', 6, 'advanced', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'adeola.williams@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'Procurement', 5, 'intermediate', 0, NOW(), NOW() FROM candidates c WHERE c.email = 'adeola.williams@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'BOQ Preparation', 6, 'advanced', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'adeola.williams@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'Valuation', 5, 'intermediate', 0, NOW(), NOW() FROM candidates c WHERE c.email = 'adeola.williams@example.com';

-- Construction: Chidi Eze
INSERT INTO `candidate_skills` (`candidate_id`, `job_seeker_id`, `skill_name`, `years_experience`, `proficiency`, `is_certified`, `created_at`, `updated_at`)
SELECT c.id, c.job_seeker_id, 'AutoCAD', 6, 'advanced', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'chidi.eze@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'Structural Design', 5, 'advanced', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'chidi.eze@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'Site Surveys', 5, 'intermediate', 0, NOW(), NOW() FROM candidates c WHERE c.email = 'chidi.eze@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'Material Testing', 4, 'intermediate', 0, NOW(), NOW() FROM candidates c WHERE c.email = 'chidi.eze@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'Project Management', 4, 'intermediate', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'chidi.eze@example.com';

-- Retail: Funke Ogunleye
INSERT INTO `candidate_skills` (`candidate_id`, `job_seeker_id`, `skill_name`, `years_experience`, `proficiency`, `is_certified`, `created_at`, `updated_at`)
SELECT c.id, c.job_seeker_id, 'Team Management', 6, 'advanced', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'funke.ogunleye@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'Inventory Control', 6, 'advanced', 0, NOW(), NOW() FROM candidates c WHERE c.email = 'funke.ogunleye@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'Customer Service', 8, 'expert', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'funke.ogunleye@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'Sales Analysis', 5, 'intermediate', 0, NOW(), NOW() FROM candidates c WHERE c.email = 'funke.ogunleye@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'Merchandising', 6, 'advanced', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'funke.ogunleye@example.com';

-- Retail: Tunde Bakare
INSERT INTO `candidate_skills` (`candidate_id`, `job_seeker_id`, `skill_name`, `years_experience`, `proficiency`, `is_certified`, `created_at`, `updated_at`)
SELECT c.id, c.job_seeker_id, 'Customer Service', 3, 'advanced', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'tunde.bakare@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'Cash Handling', 3, 'advanced', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'tunde.bakare@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'Stock Replenishment', 3, 'intermediate', 0, NOW(), NOW() FROM candidates c WHERE c.email = 'tunde.bakare@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'Visual Merchandising', 2, 'beginner', 0, NOW(), NOW() FROM candidates c WHERE c.email = 'tunde.bakare@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'POS Systems', 3, 'intermediate', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'tunde.bakare@example.com';

-- Finance: Ngozi Okeke
INSERT INTO `candidate_skills` (`candidate_id`, `job_seeker_id`, `skill_name`, `years_experience`, `proficiency`, `is_certified`, `created_at`, `updated_at`)
SELECT c.id, c.job_seeker_id, 'Financial Modeling', 4, 'advanced', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'ngozi.okeke@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'Excel', 5, 'expert', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'ngozi.okeke@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'Data Analysis', 4, 'advanced', 0, NOW(), NOW() FROM candidates c WHERE c.email = 'ngozi.okeke@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'Budgeting', 5, 'advanced', 0, NOW(), NOW() FROM candidates c WHERE c.email = 'ngozi.okeke@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'Forecasting', 4, 'intermediate', 0, NOW(), NOW() FROM candidates c WHERE c.email = 'ngozi.okeke@example.com';

-- Finance: Emeka Nwosu
INSERT INTO `candidate_skills` (`candidate_id`, `job_seeker_id`, `skill_name`, `years_experience`, `proficiency`, `is_certified`, `created_at`, `updated_at`)
SELECT c.id, c.job_seeker_id, 'Tax Preparation', 5, 'advanced', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'emeka.nwosu@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'Bookkeeping', 6, 'expert', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'emeka.nwosu@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'Sage', 4, 'advanced', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'emeka.nwosu@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'QuickBooks', 5, 'advanced', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'emeka.nwosu@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'VAT Returns', 5, 'advanced', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'emeka.nwosu@example.com';

-- Legal: Chioma Okonkwo
INSERT INTO `candidate_skills` (`candidate_id`, `job_seeker_id`, `skill_name`, `years_experience`, `proficiency`, `is_certified`, `created_at`, `updated_at`)
SELECT c.id, c.job_seeker_id, 'Legal Research', 4, 'advanced', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'chioma.okonkwo@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'Case Management', 4, 'intermediate', 0, NOW(), NOW() FROM candidates c WHERE c.email = 'chioma.okonkwo@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'Document Drafting', 4, 'advanced', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'chioma.okonkwo@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'Client Communication', 4, 'advanced', 0, NOW(), NOW() FROM candidates c WHERE c.email = 'chioma.okonkwo@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'Court Filings', 3, 'intermediate', 0, NOW(), NOW() FROM candidates c WHERE c.email = 'chioma.okonkwo@example.com';

-- Legal: Uche Eze
INSERT INTO `candidate_skills` (`candidate_id`, `job_seeker_id`, `skill_name`, `years_experience`, `proficiency`, `is_certified`, `created_at`, `updated_at`)
SELECT c.id, c.job_seeker_id, 'Administrative Support', 3, 'advanced', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'uche.eze@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'Document Management', 3, 'intermediate', 0, NOW(), NOW() FROM candidates c WHERE c.email = 'uche.eze@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'Scheduling', 3, 'intermediate', 0, NOW(), NOW() FROM candidates c WHERE c.email = 'uche.eze@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'Client Intake', 3, 'advanced', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'uche.eze@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'Filing', 3, 'intermediate', 0, NOW(), NOW() FROM candidates c WHERE c.email = 'uche.eze@example.com';

-- Manufacturing: Bola Adeyemi
INSERT INTO `candidate_skills` (`candidate_id`, `job_seeker_id`, `skill_name`, `years_experience`, `proficiency`, `is_certified`, `created_at`, `updated_at`)
SELECT c.id, c.job_seeker_id, 'Lean Manufacturing', 7, 'advanced', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'bola.adeyemi@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'Team Leadership', 7, 'advanced', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'bola.adeyemi@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'Quality Control', 7, 'advanced', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'bola.adeyemi@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'Health & Safety', 7, 'advanced', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'bola.adeyemi@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'Production Planning', 6, 'advanced', 0, NOW(), NOW() FROM candidates c WHERE c.email = 'bola.adeyemi@example.com';

-- Manufacturing: Kunle Ogunbiyi
INSERT INTO `candidate_skills` (`candidate_id`, `job_seeker_id`, `skill_name`, `years_experience`, `proficiency`, `is_certified`, `created_at`, `updated_at`)
SELECT c.id, c.job_seeker_id, 'Quality Control', 5, 'advanced', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'kunle.ogunbiyi@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'ISO Standards', 4, 'intermediate', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'kunle.ogunbiyi@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'Auditing', 4, 'advanced', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'kunle.ogunbiyi@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'Root Cause Analysis', 4, 'intermediate', 0, NOW(), NOW() FROM candidates c WHERE c.email = 'kunle.ogunbiyi@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'Inspection', 5, 'advanced', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'kunle.ogunbiyi@example.com';

-- Logistics: Femi Adebisi
INSERT INTO `candidate_skills` (`candidate_id`, `job_seeker_id`, `skill_name`, `years_experience`, `proficiency`, `is_certified`, `created_at`, `updated_at`)
SELECT c.id, c.job_seeker_id, 'Warehouse Management', 6, 'advanced', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'femi.adebisi@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'Inventory Control', 6, 'advanced', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'femi.adebisi@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'Team Leadership', 5, 'intermediate', 0, NOW(), NOW() FROM candidates c WHERE c.email = 'femi.adebisi@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'WMS', 4, 'intermediate', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'femi.adebisi@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'Health & Safety', 6, 'advanced', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'femi.adebisi@example.com';

-- Logistics: Tayo Alabi
INSERT INTO `candidate_skills` (`candidate_id`, `job_seeker_id`, `skill_name`, `years_experience`, `proficiency`, `is_certified`, `created_at`, `updated_at`)
SELECT c.id, c.job_seeker_id, 'Forklift Operation', 4, 'advanced', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'tayo.alabi@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'Warehouse Safety', 4, 'advanced', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'tayo.alabi@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'Stock Picking', 4, 'intermediate', 0, NOW(), NOW() FROM candidates c WHERE c.email = 'tayo.alabi@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'Loading/Unloading', 4, 'intermediate', 0, NOW(), NOW() FROM candidates c WHERE c.email = 'tayo.alabi@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'Inventory Count', 4, 'intermediate', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'tayo.alabi@example.com';

-- Security: Segun Ogunlana
INSERT INTO `candidate_skills` (`candidate_id`, `job_seeker_id`, `skill_name`, `years_experience`, `proficiency`, `is_certified`, `created_at`, `updated_at`)
SELECT c.id, c.job_seeker_id, 'Security Operations', 8, 'expert', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'segun.ogunlana@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'Team Management', 6, 'advanced', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'segun.ogunlana@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'Incident Response', 8, 'expert', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'segun.ogunlana@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'CCTV Monitoring', 6, 'advanced', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'segun.ogunlana@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'SIA Licensed', 8, 'expert', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'segun.ogunlana@example.com';

-- Security: Amina Mohammed
INSERT INTO `candidate_skills` (`candidate_id`, `job_seeker_id`, `skill_name`, `years_experience`, `proficiency`, `is_certified`, `created_at`, `updated_at`)
SELECT c.id, c.job_seeker_id, 'Close Protection', 6, 'advanced', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'amina.mohammed@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'Risk Assessment', 5, 'advanced', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'amina.mohammed@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'Surveillance', 5, 'intermediate', 0, NOW(), NOW() FROM candidates c WHERE c.email = 'amina.mohammed@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'Driving Skills', 6, 'advanced', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'amina.mohammed@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'First Aid', 6, 'advanced', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'amina.mohammed@example.com';

-- Security: Bisi Ademola
INSERT INTO `candidate_skills` (`candidate_id`, `job_seeker_id`, `skill_name`, `years_experience`, `proficiency`, `is_certified`, `created_at`, `updated_at`)
SELECT c.id, c.job_seeker_id, 'CCTV Systems', 4, 'advanced', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'bisi.ademola@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'Incident Reporting', 4, 'intermediate', 0, NOW(), NOW() FROM candidates c WHERE c.email = 'bisi.ademola@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'Surveillance', 4, 'intermediate', 0, NOW(), NOW() FROM candidates c WHERE c.email = 'bisi.ademola@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'Access Control', 4, 'intermediate', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'bisi.ademola@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'Communication Skills', 4, 'advanced', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'bisi.ademola@example.com';

-- Hospitality: Ifeanyi Okonkwo
INSERT INTO `candidate_skills` (`candidate_id`, `job_seeker_id`, `skill_name`, `years_experience`, `proficiency`, `is_certified`, `created_at`, `updated_at`)
SELECT c.id, c.job_seeker_id, 'Hotel Operations', 8, 'expert', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'ifeanyi.okonkwo@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'Staff Management', 8, 'expert', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'ifeanyi.okonkwo@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'Revenue Management', 6, 'advanced', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'ifeanyi.okonkwo@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'Customer Service', 9, 'expert', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'ifeanyi.okonkwo@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'Event Planning', 5, 'intermediate', 0, NOW(), NOW() FROM candidates c WHERE c.email = 'ifeanyi.okonkwo@example.com';

-- Hospitality: Zainab Abubakar
INSERT INTO `candidate_skills` (`candidate_id`, `job_seeker_id`, `skill_name`, `years_experience`, `proficiency`, `is_certified`, `created_at`, `updated_at`)
SELECT c.id, c.job_seeker_id, 'Menu Planning', 9, 'expert', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'zainab.abubakar@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'Kitchen Management', 8, 'expert', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'zainab.abubakar@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'Food Safety', 10, 'expert', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'zainab.abubakar@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'Team Leadership', 7, 'advanced', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'zainab.abubakar@example.com'
UNION ALL SELECT c.id, c.job_seeker_id, 'Cost Control', 8, 'expert', 1, NOW(), NOW() FROM candidates c WHERE c.email = 'zainab.abubakar@example.com';

-- ============================================
-- INSERT CANDIDATE PREFERENCES
-- ============================================

INSERT INTO `candidate_preferences` (`candidate_id`, `job_seeker_id`, `preferred_job_type`, `min_salary`, `preferred_location`, `open_to_remote`, `open_to_relocation`, `notice_period_days`, `industries_preferred`, `created_at`, `updated_at`)
SELECT 
    c.id, 
    c.job_seeker_id, 
    c.employment_type, 
    c.expected_salary, 
    c.city, 
    1, 
    0, 
    30, 
    JSON_ARRAY(c.industry, 'Other'), 
    NOW(), 
    NOW()
FROM candidates c
LEFT JOIN candidate_preferences cp ON cp.candidate_id = c.id
WHERE cp.id IS NULL;

-- ============================================
-- VERIFICATION QUERIES
-- ============================================
SELECT '=== SEEDING COMPLETE ===' AS Status;

SELECT 
    'Users' AS Table_Name, 
    COUNT(*) AS Record_Count 
FROM users WHERE user_type = 'job_seeker'
UNION ALL
SELECT 'Job Seekers', COUNT(*) FROM job_seekers
UNION ALL
SELECT 'Candidates', COUNT(*) FROM candidates
UNION ALL
SELECT 'Candidate Skills', COUNT(*) FROM candidate_skills
UNION ALL
SELECT 'Candidate Preferences', COUNT(*) FROM candidate_preferences;

-- Show sample data
SELECT 
    js.id AS job_seeker_id,
    js.first_name,
    js.last_name,
    js.email,
    js.industry,
    js.city,
    (SELECT COUNT(*) FROM candidate_skills cs WHERE cs.job_seeker_id = js.id) AS skill_count
FROM job_seekers js
LIMIT 10;

-- Re-enable foreign key checks
SET FOREIGN_KEY_CHECKS = 1;