SELECT urm.id AS role_map_id, u.id AS user_id, u.phone_number, u.full_name
FROM user_role_map urm
JOIN users u     ON u.id = urm.user_id
JOIN user_role r ON r.id = urm.role_id
WHERE r.role_key = 'ROLE_RELATIONAL_MANAGER'
  AND u.phone_number IN ('9841727022', '9841021495', '9791140214', '9841670000')
ORDER BY u.id;

DELETE FROM user_role_map
WHERE role_id = (SELECT id FROM user_role WHERE role_key = 'ROLE_RELATIONAL_MANAGER')
  AND user_id IN (
    SELECT id FROM users
    WHERE phone_number IN ('9841727022', '9841021495', '9791140214', '9841670000')
  );

INSERT INTO user_role_map (user_id, role_id)
SELECT u.id, (SELECT id FROM user_role WHERE role_key = 'ROLE_VIEWER')
FROM users u
WHERE u.phone_number IN ('9841727022', '9841021495', '9791140214', '9841670000')
  AND NOT EXISTS (
    SELECT 1 FROM user_role_map urm
    WHERE urm.user_id = u.id
      AND urm.role_id = (SELECT id FROM user_role WHERE role_key = 'ROLE_VIEWER')
  );

UPDATE users
SET role = 'viewer'
WHERE phone_number IN ('9841727022', '9841021495', '9791140214', '9841670000')
  AND role IS DISTINCT FROM 'viewer';

SELECT u.id,
       u.phone_number,
       u.full_name,
       u.role AS users_role,
       EXISTS (
         SELECT 1 FROM user_role_map urm
         JOIN user_role r ON r.id = urm.role_id
         WHERE urm.user_id = u.id AND r.role_key = 'ROLE_RELATIONAL_MANAGER'
       ) AS has_rm,
       EXISTS (
         SELECT 1 FROM user_role_map urm
         JOIN user_role r ON r.id = urm.role_id
         WHERE urm.user_id = u.id AND r.role_key = 'ROLE_VIEWER'
       ) AS has_viewer
FROM users u
WHERE u.phone_number IN ('9841727022', '9841021495', '9791140214', '9841670000')
ORDER BY u.id;




