-- Get all candidates with their source and profile information
SELECT 
    c.candidate_id,
    c.name,
    c.email,
    c.phone,
    c.lineup_date,
    s.name as source,
    p.name as profile,
    c.status,
    c.added_date
FROM candidates c
LEFT JOIN sources s ON c.source_id = s.id
LEFT JOIN profiles p ON c.profile_id = p.id
ORDER BY c.created_at DESC;

-- Get interview history for a specific candidate
SELECT 
    c.candidate_id,
    c.name,
    i.interview_date,
    i.interview_type,
    i.status,
    i.result,
    i.interviewer_name,
    i.notes
FROM candidates c
JOIN interviews i ON c.id = i.candidate_id
WHERE c.candidate_id = 'IX-10001'
ORDER BY i.interview_date;

-- Get candidates by status with counts
SELECT 
    status,
    COUNT(*) as count
FROM candidates
GROUP BY status
ORDER BY count DESC;

-- Get joining details for selected candidates
SELECT 
    c.candidate_id,
    c.name,
    jd.joining_date,
    jd.actual_joining_date,
    jd.joining_status,
    jd.salary_offered,
    jd.department,
    jd.position
FROM candidates c
JOIN joining_details jd ON c.id = jd.candidate_id
WHERE c.status IN ('selected', 'joined')
ORDER BY jd.joining_date;

-- Get candidates who need follow-up (rescheduled or on hold)
SELECT 
    c.candidate_id,
    c.name,
    c.email,
    c.phone,
    c.status,
    c.lineup_date,
    CASE 
        WHEN c.status = 'rescheduled' THEN 'Follow up for rescheduled interview'
        WHEN c.status = 'hold' THEN 'Follow up on hold status'
        ELSE 'Other follow-up needed'
    END as action_needed
FROM candidates c
WHERE c.status IN ('rescheduled', 'hold')
ORDER BY c.lineup_date;

-- Get recruitment pipeline summary
SELECT 
    s.name as source,
    COUNT(c.id) as total_candidates,
    SUM(CASE WHEN c.status = 'selected' THEN 1 ELSE 0 END) as selected,
    SUM(CASE WHEN c.status = 'joined' THEN 1 ELSE 0 END) as joined,
    SUM(CASE WHEN c.status = 'rejected' THEN 1 ELSE 0 END) as rejected,
    ROUND((SUM(CASE WHEN c.status = 'selected' THEN 1 ELSE 0 END) * 100.0 / COUNT(c.id)), 2) as selection_rate
FROM sources s
LEFT JOIN candidates c ON s.id = c.source_id
GROUP BY s.id, s.name
ORDER BY total_candidates DESC;

-- Get monthly recruitment trends
SELECT 
    DATE_FORMAT(added_date, '%Y-%m') as month,
    COUNT(*) as candidates_added,
    SUM(CASE WHEN status = 'selected' THEN 1 ELSE 0 END) as selected,
    SUM(CASE WHEN status = 'joined' THEN 1 ELSE 0 END) as joined
FROM candidates
GROUP BY DATE_FORMAT(added_date, '%Y-%m')
ORDER BY month DESC;
