/*
Employee ID | Version | Date Range  |CR ID          |Propose
100006       initial   20.01.22      100006-220120   Update visit_details opd_id
*/

DROP PROCEDURE IF EXISTS sp_reschedule_appointment;

CREATE PROCEDURE sp_reschedule_appointment(
	IN a_appointment_id VARCHAR ( 1000 ),
	IN a_appointment_date_time DATETIME,
	IN a_doctor_clinic_schedule INT,
	IN a_room_id INT,
	IN a_doctor_id INT,
	IN a_doctor_fees DECIMAL ( 12, 2 ),
	IN a_rescheduled_type VARCHAR ( 50 ),
	IN a_user_id INT,
	IN a_message VARCHAR ( 500 ),
	IN a_appo_reschedule_flag VARCHAR ( 1 ))
BEGIN
	DECLARE
		done INT DEFAULT 0;
	DECLARE
		v_appo_value INT DEFAULT 0;
	DECLARE
		v_appointment_srl INT DEFAULT 1;
	DECLARE
		v_appointment_queue INT DEFAULT 1;
	DECLARE
		cur_appointment CURSOR FOR SELECT
		appo_value 
	FROM
		JSON_TABLE ( a_appointment_id, "$[*]" COLUMNS ( appo_value INT PATH "$" ) ) DATA;
	DECLARE
		CONTINUE HANDLER FOR NOT FOUND 
		SET done = 1;
	OPEN cur_appointment;
	read_loop :
	LOOP
			FETCH cur_appointment INTO v_appo_value;
		IF
			done = 1 THEN
				LEAVE read_loop;
			
		END IF;
		IF
			a_appo_reschedule_flag = 'Y' THEN
			SELECT
				ifnull( MAX( appointment_srl ), 0 ) + 1,
				ifnull( MAX( appointment_queue ), 0 ) + 1 INTO v_appointment_srl,
				v_appointment_queue 
			FROM
				opd_doctor_appointments 
			WHERE
				doctor_clinic_schedule_id = a_doctor_clinic_schedule;
			
		END IF;
		INSERT INTO opd_doctor_appointments (
			booking_date,
			doctor_appointment_date,
			appointment_no,
			appointment_srl,
			appointment_queue,
			doctor_clinic_schedule_id,
			room_id,
			patient_id,
			is_register,
			registration_fees,
			doctor_id,
			doctor_fees,
			message,
			appointment_status,
			rescheduled_type,
			appointment_note,
			prev_appointment_id,
			created_at,
			created_by 
		) SELECT CURRENT_DATE
		,
		a_appointment_date_time,
		appointment_no,
		CASE
				
				WHEN a_appo_reschedule_flag = 'Y' THEN
				v_appointment_srl ELSE appointment_srl 
			END,
		CASE
				
				WHEN appointment_queue = 0 THEN
				0 
				WHEN a_appo_reschedule_flag = 'Y' THEN
				v_appointment_queue ELSE appointment_queue 
			END,
			a_doctor_clinic_schedule,
			a_room_id,
			patient_id,
			is_register,
			registration_fees,
			a_doctor_id,
			a_doctor_fees,
			message,
			appointment_status,
			rescheduled_type,
			appointment_note,
			v_appo_value,
			CURRENT_TIMESTAMP,
			a_user_id 
		FROM
			opd_doctor_appointments 
		WHERE
			id = v_appo_value;
		
		SET @last_id_in_table1 = LAST_INSERT_ID();
		IF
			@last_id_in_table1 > 0 THEN
			
			UPDATE patient_service_charges 
				SET service_source_id = @last_id_in_table1 
			WHERE
				billing_module_id = 3 
				AND service_source_id = v_appo_value;
			
			UPDATE visit_details
			INNER JOIN case_details ON case_details.id = visit_details.case_id 
			SET visit_details.opd_id = @last_id_in_table1 ,
				doctor_id = a_doctor_id
			WHERE
				case_details.opd_ipd_ot_flag = 'O' 
				AND visit_details.visit_status = 'O'
				AND case_details.opd_ipd_ot_id = v_appo_value;
			
			UPDATE case_details 
			SET opd_ipd_ot_id = @last_id_in_table1 
			WHERE
				opd_ipd_ot_flag = 'O' 
				AND opd_ipd_ot_id = v_appo_value;
			
		END IF;
		UPDATE opd_doctor_appointments 
		SET rescheduled_type = a_rescheduled_type,
		message = a_message,
		appointment_status = 'R' 
		WHERE
			id = v_appo_value;
		
	END LOOP read_loop;
	CLOSE cur_appointment;

END