DROP FUNCTION IF EXISTS fn_ot_details;
CREATE FUNCTION fn_ot_details(a_data_flag varchar(10), a_ot_booking_id int)
RETURNS VARCHAR(1000)
BEGIN
	DECLARE	v_return VARCHAR(1000);

	IF a_data_flag = 'OTDOC' THEN
		SELECT JSON_ARRAYAGG(JSON_OBJECT(	
			'designation', designation,
			'staff_name', staff_name
			))
		INTO	v_return
		FROM
		(
			SELECT	CONCAT(us.first_name, ' ', us.last_name) as staff_name, sd.designation as designation
			FROM	staff_designation sd, ot_staff_details os, users us
			WHERE	sd.id = us.designation_id
			AND		os.staff_role = sd.id 
			AND		us.id = os.staff_id
			AND		os.ot_booking_id = a_ot_booking_id
		) ot_staff_dtls;
	END IF;
	return v_return;
END;