DROP PROCEDURE sp_doctor_details;

CREATE  PROCEDURE sp_doctor_details(IN a_doctorID int)
BEGIN

Select JSON_ARRAYAGG(JSON_OBJECT( 'tag', tag, 'dtl_info', dtl_info))  doctor_dtl
FROM	(
	#Doctor Basic info information
	Select 'doctor_info' tag, JSON_ARRAYAGG(JSON_OBJECT(	
			'id', id,
			'doctor_id', doctor_id,
			'doctor_name', doctor_name,
			'prnt_name', prnt_name,
			'qualification', qualification,
			'dept_id', dept_id,
			'sub_dept_id', sub_dept_id,
			'specialty_id', specialty_id,
			'dob', dob,
			'gender', gender,
			'phone_no', phone_no,
			'reg_no', reg_no,
			'pan_no', pan_no,
			'attached_with', attached_with,
			'is_doctor', is_doctor,
			'doc_type', doc_type,
			'ip_applicable', ip_applicable,
			'ip_pay_mode', ip_pay_mode,
			'visit_charge', visit_charge,
			'ipd_charge', ipd_charge,
			'hos_share', hos_share,
			'share_mode', share_mode,
			'share_rate', share_rate,
			'is_registration_applicable', is_registration_applicable,
			'registration_charge', registration_charge,
			'reff_by', reff_by,
			'is_active', is_active,
			'created_at', created_at,
			'created_by', created_by,
			'updated_at', updated_at,
			'updated_by', updated_by
			)
		) dtl_info
	FROM	doctors
		WHERE id = a_doctorID

union

# address of doctor
Select 'address' tag, JSON_ARRAYAGG(JSON_OBJECT(		
	'id', addresses.id,
	'data_source', data_source,
	'data_source_id', data_source_id,
	'data_source', data_source,
	'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,
	'is_active', doctors.is_active,
	'created_at', doctors.created_at,
	'created_by', doctors.created_by,
	'updated_at', doctors.updated_at,
	'updated_by', doctors.updated_by
				)
		) dtl_info 
	FROM	addresses, doctors
WHERE doctors.id = a_doctorID
AND	addresses.data_source_id  = doctors.id
AND data_source = 'doctor'
AND doctors.is_active = 'Y'

union

	# Base IP and Dept, Sub-Dept, test wise IP information
Select 'ip_dtls' tag, JSON_ARRAYAGG(JSON_OBJECT(		
			'id', id,
			'doctor_id', doctor_id,
			'ip_config_category', ip_config_category,
			'category_id', category_id,
			'is_active', is_active,
			'ip_type', ip_type,
			'ip_rate', ip_rate,
			'created_at', created_at,
			'created_by', created_by,
			'updated_at', updated_at,
			'updated_by', updated_by
				)
		) dtl_info 
	FROM	doctor_ip_config
WHERE doctor_id  = a_doctorID
AND is_active = 'Y'

union

	# Base IP and Dept, Sub-Dept, test wise IP information
Select 'signup_charge_dtls' tag, JSON_ARRAYAGG(JSON_OBJECT(		
			'id', id,
			'doctor_id', doctor_id,
			'signup_config_category', signup_config_category,
			'category_id', category_id,
			'is_active', is_active,
			'signup_charge_type', signup_charge_type,
			'signup_charge_rate', signup_charge_rate,
			'created_at', created_at,
			'created_by', created_by,
			'updated_at', updated_at,
			'updated_by', updated_by
				)
		) dtl_info 
	FROM	doctor_signup_charge
WHERE doctor_id  = a_doctorID
AND is_active = 'Y'
) doctor_dtls ;


END