-- LOG1 - Phone or Email Login readiness checks

-- 1) Active users with duplicate exact phone values
SELECT
    phone,
    COUNT(*) AS staff_count,
    GROUP_CONCAT(CONCAT(id, ': ', full_name) SEPARATOR ' | ') AS staff
FROM users
WHERE phone IS NOT NULL
  AND TRIM(phone) <> ''
  AND is_active = 1
GROUP BY phone
HAVING COUNT(*) > 1;

-- 2) Active users missing phone numbers
SELECT
    id,
    COALESCE(staff_code, employee_code) AS staff_id,
    full_name,
    email,
    phone
FROM users
WHERE is_active = 1
  AND (phone IS NULL OR TRIM(phone) = '')
ORDER BY full_name;

-- 3) All active login identities
SELECT
    id,
    COALESCE(staff_code, employee_code) AS staff_id,
    full_name,
    email,
    phone,
    is_active
FROM users
WHERE is_active = 1
ORDER BY full_name;