CREATE  PROCEDURE sp_patient_details_for_api(IN a_patientID INT)
BEGIN

Select JSON_ARRAYAGG(JSON_OBJECT( 'tag', tag, 'dtl_info', dtl_info))  patient_dtl
FROM	(
	
	Select 'patient_info' tag, JSON_ARRAYAGG(JSON_OBJECT(	
			'id', id,
			'patient_unique_id', patient_unique_id,
			'admission_date', admission_date,
			'title', title,
			'first_name', first_name,
			'middle_name', middle_name,
			'last_name', last_name,
			'patient_name', patient_name,
			'age', age,
			'month', month,
			'days', days,
			'image', image,
			'dob', dob,
			'gender', gender,
			'marital_status', marital_status,
			'blood_group', blood_group
			)
		) dtl_info
	FROM	patients
		WHERE id = a_patientID
union


Select 'patient_nxt_kin' tag, JSON_ARRAYAGG(JSON_OBJECT(	
			'id', id,
			'patient_id', patient_id,
			'registration_date', registration_date,
			'relation_type', relation_type,
			'title', title,
			'first_name', first_name,
			'middle_name', middle_name,
			'last_name', last_name,
			'next_ofkin_name', next_ofkin_name,
			'image', image,
			'age', age,
			'dob', dob,
			'gender', gender
				)
		) dtl_info 
	FROM	next_ofkin
WHERE patient_id = a_patientID

union

Select 'address' tag, JSON_ARRAYAGG(JSON_OBJECT(		
	'id', addresses.id,
	'data_source', data_source,
	'data_source_id', data_source_id,
	'address_type', address_type,
	'pin', pin,
	'country', country,
	'state', state,
	'district', district,
	'city', city,
	'police_station', police_station,
	'locality', locality,
	'house_no', house_no,
	'street', street
				)
		) dtl_info 
	FROM	addresses, (SELECT 	 'kin' flag, id, next_ofkin_name data_source_name
										FROM next_ofkin WHERE patient_id = a_patientID
										UNION
										SELECT  'patient', a_patientID,'') patient_and_kin_id
WHERE data_source In ('patient', 'kin')
		AND data_source = flag
		AND	data_source_id  = patient_and_kin_id.id

union


Select 'contact' tag, JSON_ARRAYAGG(JSON_OBJECT(		
	'id', contacts.id,
'data_source', data_source,
'data_source_id', data_source_id,
'data_source_name', data_source_name,
'contact_type', contact_type,
'contact_details', contact_details,
'is_primary', is_primary
				)
		) dtl_info 
	FROM	contacts, (SELECT 	 'kin' flag, id , next_ofkin_name data_source_name
										FROM next_ofkin WHERE patient_id = a_patientID
										UNION
										SELECT  'patient', a_patientID,'') patient_and_kin_id
WHERE data_source In ('patient', 'kin')
		AND data_source = flag
		AND	data_source_id  = patient_and_kin_id.id
		
		
union


Select 'doc_id' tag, JSON_ARRAYAGG(JSON_OBJECT(		
	'id', doc_ids.id,
'data_source', data_source,
'data_source_id', data_source_id,
'data_source_name', data_source_name,
'doc_mast_type', doc_mast_type,
'doc_type', doc_type,
'doc_heading', doc_heading,
'doc_number', doc_number,
'image', image,
'is_active', is_active,
'created_at', created_at,
'created_by', created_by
				)
		) dtl_info 
	FROM	doc_ids, (SELECT 	 'kin' flag, id , next_ofkin_name data_source_name
										FROM next_ofkin WHERE patient_id = a_patientID
										UNION
										SELECT  'patient', a_patientID,'') patient_and_kin_id
WHERE data_source In ('patient', 'kin')
		AND data_source = flag
		AND	data_source_id  = patient_and_kin_id.id

) patient_dtls ;


END