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

	IF a_data_flag = 'PADDR' THEN
		SELECT	CONCAT(IFNULL(CONCAT(street, ','), ''), IFNULL(CONCAT(locality, ','), ''), IFNULL(CONCAT(city, ','), ''), IFNULL(pin, ''))
		INTO	v_return
		FROM	patients pt, addresses ad
		WHERE	pt.id= ad.data_source_id 
		AND		ad.data_source = 'Patient'
		AND		ad.data_source_id = a_patient_id
		AND		ad.is_active= 'Y'
		AND		ad.address_type = 'P'
		ORDER BY ad.created_at desc
		LIMIT 1;
	ELSEIF a_data_flag = 'MADDR' THEN
		SELECT	CONCAT(IFNULL(CONCAT(street, ','), ''), IFNULL(CONCAT(locality, ','), ''), IFNULL(CONCAT(city, ','), ''), IFNULL(pin, ''))
		INTO	v_return
		FROM	patients pt, addresses ad
		WHERE	pt.id= ad.data_source_id 
		AND		ad.data_source = 'Patient'
		AND		ad.data_source_id = a_patient_id
		AND		ad.is_active= 'Y'
		AND		ad.address_type = 'M'
		ORDER BY ad.created_at desc
		LIMIT 1;
	ELSEIF a_data_flag = 'MOB' THEN
		SELECT	contact_details
		INTO	v_return
		FROM	patients pt, contacts ad
		WHERE	pt.id= ad.data_source_id 
		AND		ad.data_source = 'Patient'
		AND		ad.data_source_id = a_patient_id
		AND		ad.is_active= 'Y'
		AND		ad.contact_type = 'M'
		ORDER BY ad.created_at desc
		LIMIT 1;
	ELSEIF a_data_flag = 'EMAIL' THEN
		SELECT	contact_details
		INTO	v_return
		FROM	patients pt, contacts ad
		WHERE	pt.id= ad.data_source_id 
		AND		ad.data_source = 'Patient'
		AND		ad.data_source_id = a_patient_id
		AND		ad.is_active= 'Y'
		AND		ad.contact_type = 'E'
		ORDER BY ad.created_at desc
		LIMIT 1;
	ELSEIF	a_data_flag = 'KIN' THEN
		SELECT JSON_ARRAYAGG(JSON_OBJECT(	
			'next_ofkin_name', next_ofkin_name,
			'gender', gender,
			'age', age
			))
		INTO	v_return
		FROM
		(
		SELECT 	next_ofkin_name, gender, age
		FROM	next_ofkin
		WHERE	patient_id = a_patient_id
		AND		is_active= 'Y'
		) kin;
	END IF;
	return v_return;
END;