-- Find and analyze duplicate users in organization 11

-- 1. Find users with similar names (likely duplicates)
SELECT 
    'Potential Name Duplicates:' as info,
    GROUP_CONCAT(id) as user_ids,
    GROUP_CONCAT(email) as emails,
    SUBSTRING_INDEX(full_name, ' ', 1) as first_name_part,
    COUNT(*) as duplicate_count
FROM users 
WHERE organization_id = 11 
  AND is_active = 1
GROUP BY SUBSTRING_INDEX(full_name, ' ', 1)
HAVING COUNT(*) > 1;

-- 2. Find users with similar email domains/prefixes  
SELECT 
    'Potential Email Duplicates:' as info,
    id, full_name, email, created_at, last_login_at,
    SUBSTRING_INDEX(email, '@', 1) as email_prefix
FROM users 
WHERE organization_id = 11 
  AND is_active = 1
ORDER BY email_prefix, created_at;

-- 3. Show all users in org 11 with full details for manual review
SELECT 
    'All Organization 11 Users:' as info,
    id, full_name, email, role, is_active, created_at, last_login_at,
    slack_member_id
FROM users 
WHERE organization_id = 11
ORDER BY full_name, created_at;

-- 4. Check task assignments for these users to see which ones are actively used
SELECT 
    'User Task Assignments:' as info,
    u.id, u.full_name, u.email,
    COUNT(DISTINCT ft.id) as total_tasks,
    COUNT(CASE WHEN ft.status = 'in_progress' THEN 1 END) as active_tasks,
    MAX(ft.updated_at) as last_task_activity
FROM users u
LEFT JOIN flochief_tasks ft ON u.id = ft.assigned_to
WHERE u.organization_id = 11 AND u.is_active = 1
GROUP BY u.id, u.full_name, u.email
ORDER BY total_tasks DESC, last_task_activity DESC;

-- 5. Check Slack integration status
SELECT 
    'Slack Integration Status:' as info,
    id, full_name, email, 
    slack_member_id,
    CASE WHEN slack_member_id IS NOT NULL THEN 'Connected' ELSE 'Not Connected' END as slack_status
FROM users 
WHERE organization_id = 11 AND is_active = 1
ORDER BY slack_member_id IS NOT NULL DESC, full_name;