-- Step 1: Show soft deleted candidate
SELECT 
    'SOFT DELETED CANDIDATE' as info,
    '' as value
UNION ALL
SELECT 
    CONCAT('ID: ', id, ' - ', majira_id, ' - ', first_name),
    email
FROM candidates
WHERE deleted_at IS NOT NULL;

-- Step 2: Permanently delete soft-deleted candidates and their calls
DELETE cc FROM candidate_calls cc
INNER JOIN candidates c ON cc.candidate_id = c.id
WHERE c.deleted_at IS NOT NULL;

DELETE FROM candidates WHERE deleted_at IS NOT NULL;

-- Step 3: Show candidates WITHOUT calls
SELECT 
    '' as separator,
    '' as s2
UNION ALL
SELECT 
    'CANDIDATES WITHOUT CALLS' as info,
    CAST(COUNT(*) AS CHAR)
FROM candidates c
LEFT JOIN candidate_calls cc ON c.id = cc.candidate_id
WHERE c.deleted_at IS NULL
  AND cc.id IS NULL;

-- Step 4: Backfill ALL missing calls
INSERT INTO candidate_calls (candidate_id, admin_id, call_status, notes, called_at, created_at, updated_at)
SELECT 
    c.id,
    COALESCE(c.registered_by, 1) as admin_id,
    CASE 
        WHEN c.status = 'approved' THEN 'picked'
        WHEN c.status = 'registered' THEN 'declined_unreachable'
        WHEN c.status = 'pending' THEN 'callback'
        WHEN c.status = 'rejected' THEN 'declined_unreachable'
        ELSE 'picked'
    END as call_status,
    'Backfilled - call not recorded during entry' as notes,
    c.created_at as called_at,
    NOW() as created_at,
    NOW() as updated_at
FROM candidates c
LEFT JOIN candidate_calls cc ON c.id = cc.candidate_id
WHERE c.deleted_at IS NULL
  AND cc.id IS NULL;

-- Step 5: Update candidates
UPDATE candidates c
LEFT JOIN candidate_calls cc ON c.id = cc.candidate_id
SET 
    c.current_call_status = CASE 
        WHEN c.status = 'approved' THEN 'picked'
        WHEN c.status = 'registered' THEN 'declined_unreachable'
        WHEN c.status = 'pending' THEN 'callback'
        ELSE 'picked'
    END,
    c.total_call_attempts = 1,
    c.last_called_at = c.created_at
WHERE c.deleted_at IS NULL
  AND c.current_call_status IS NULL;

-- Step 6: Final verification
SELECT 
    '✅ FINAL RESULTS' as status,
    '' as count
UNION ALL
SELECT 
    'Total Candidates (active)',
    CAST(COUNT(*) AS CHAR)
FROM candidates
WHERE deleted_at IS NULL
UNION ALL
SELECT 
    'Total Calls',
    CAST(COUNT(*) AS CHAR)
FROM candidate_calls
UNION ALL
SELECT 
    'Missing Calls',
    CAST(COUNT(*) AS CHAR)
FROM candidates c
LEFT JOIN candidate_calls cc ON c.id = cc.candidate_id
WHERE c.deleted_at IS NULL
  AND cc.id IS NULL
UNION ALL
SELECT 
    '',
    ''
UNION ALL
SELECT 
    '📊 CALL BREAKDOWN',
    ''
UNION ALL
SELECT 
    '✅ Picked',
    CAST(COUNT(*) AS CHAR)
FROM candidate_calls
WHERE call_status = 'picked'
UNION ALL
SELECT 
    '❌ Declined',
    CAST(COUNT(*) AS CHAR)
FROM candidate_calls
WHERE call_status = 'declined_unreachable'
UNION ALL
SELECT 
    '📞 Callback',
    CAST(COUNT(*) AS CHAR)
FROM candidate_calls
WHERE call_status = 'callback'
UNION ALL
SELECT 
    '📵 Missed',
    CAST(COUNT(*) AS CHAR)
FROM candidate_calls
WHERE call_status = 'missed';