-- ================================================================
-- HR-Employee Mapping Migration
-- Ensures all employees have created_by (HR assignment)
-- ================================================================

-- Step 1: Check current status
SELECT 
    'Employees without HR assignment' as status,
    COUNT(*) as count
FROM employees 
WHERE created_by IS NULL AND employment_status = 'active';

-- Step 2: List HR Admins available for assignment
SELECT 
    u.user_id,
    u.full_name,
    u.username,
    s.site_name,
    COUNT(e.employee_id) as current_employee_count
FROM users u
INNER JOIN roles r ON u.role_id = r.role_id
LEFT JOIN sites s ON u.site_id = s.site_id
LEFT JOIN employees e ON e.created_by = u.user_id AND e.employment_status = 'active'
WHERE r.role_code = 'hr_admin' AND u.user_status = 'active'
GROUP BY u.user_id, u.full_name, u.username, s.site_name
ORDER BY s.site_name, u.full_name;

-- Step 3: Assign unassigned employees to their site's first HR Admin
-- (You can modify this logic as needed)

UPDATE employees e
SET created_by = (
    SELECT u.user_id
    FROM users u
    INNER JOIN roles r ON u.role_id = r.role_id
    WHERE r.role_code = 'hr_admin' 
      AND u.site_id = e.site_id
      AND u.user_status = 'active'
    LIMIT 1
)
WHERE created_by IS NULL 
  AND employment_status = 'active';

-- Step 4: Verify - should show 0 unassigned employees
SELECT 
    'Employees still without HR assignment' as status,
    COUNT(*) as count
FROM employees 
WHERE created_by IS NULL AND employment_status = 'active';

-- Step 5: Show employee distribution per HR
SELECT 
    u.user_id,
    u.full_name as hr_name,
    s.site_name,
    COUNT(e.employee_id) as employee_count
FROM users u
INNER JOIN roles r ON u.role_id = r.role_id
LEFT JOIN sites s ON u.site_id = s.site_id
LEFT JOIN employees e ON e.created_by = u.user_id AND e.employment_status = 'active'
WHERE r.role_code = 'hr_admin'
GROUP BY u.user_id, u.full_name, s.site_name
ORDER BY s.site_name, u.full_name;

-- ================================================================
-- MANUAL ASSIGNMENT (Optional)
-- If you want to assign specific employees to specific HRs
-- ================================================================

-- Example: Assign employee ID 1,2,3 to HR user_id 5
-- UPDATE employees 
-- SET created_by = 5
-- WHERE employee_id IN (1, 2, 3);

-- Example: Reassign all employees from HR 3 to HR 4
-- UPDATE employees 
-- SET created_by = 4
-- WHERE created_by = 3 AND employment_status = 'active';

-- ================================================================
-- VERIFICATION QUERIES
-- ================================================================

-- Check employee-HR mapping with names
SELECT 
    e.employee_id,
    e.employee_code,
    CONCAT(e.first_name, ' ', e.last_name) as employee_name,
    s.site_name,
    u.full_name as managed_by_hr,
    e.employment_status
FROM employees e
LEFT JOIN sites s ON e.site_id = s.site_id
LEFT JOIN users u ON e.created_by = u.user_id
WHERE e.employment_status = 'active'
ORDER BY s.site_name, u.full_name, e.employee_code;

-- Check for orphaned employees (no HR assigned)
SELECT 
    e.employee_id,
    e.employee_code,
    CONCAT(e.first_name, ' ', e.last_name) as employee_name,
    s.site_name,
    'NO HR ASSIGNED' as warning
FROM employees e
LEFT JOIN sites s ON e.site_id = s.site_id
WHERE e.created_by IS NULL 
  AND e.employment_status = 'active'
ORDER BY s.site_name, e.employee_code;

-- ================================================================
-- NOTES:
-- 1. Run Step 1-2 first to understand current state
-- 2. Review HR Admin list before running Step 3
-- 3. Step 3 will auto-assign employees to first HR in their site
-- 4. You can manually reassign using the MANUAL ASSIGNMENT section
-- 5. Run verification queries to confirm correct mapping
-- ================================================================
