/* COUNT REMINDERS LINKED TO MEDICATION */
/* =============================================================================================================== */
drop function if exists countMedicationReminders;
commit;

delimiter //

CREATE FUNCTION `countMedicationReminders`(`p_medicationId` varchar(36), `p_userId` bigint)
RETURNS INT
BEGIN

	DECLARE noReminders int;
	
	SET noReminders = (SELECT COUNT(`_id`)
	FROM `Reminders` 
	WHERE `userId` = p_userId 
	AND `medicationId` = p_medicationId);
	
	return noReminders;
END 
//
commit //

delimiter ;

/* COUNT REMAINING MEDICINES */
/* =============================================================================================================== */
drop function if exists countMedicationRemaining;
commit;

delimiter //

CREATE FUNCTION `countMedicationRemaining`(`p_medicationId` varchar(36), `p_userId` bigint)
RETURNS DECIMAL(10, 1)
BEGIN

	DECLARE noRemaining decimal(10, 1);
	
	SET noRemaining = (SELECT IFNULL(SUM(`dosage`), 0) 
	FROM `Reminders` 
	WHERE `medicationId` = p_medicationId 
	AND `userId` = p_userId 
	AND `status` IN ('active', 'snoozed'));
	
	return noRemaining;
END 
//
commit //

delimiter ;

/* FIND LAST REMINDER LINKED TO A MEDICINE */
/* =============================================================================================================== */
drop function if exists findLastReminderId;
commit;

delimiter //

CREATE FUNCTION `findLastReminderId`(`p_medicationId` varchar(36), `p_userId` bigint)
RETURNS INT
BEGIN

	DECLARE lastReminder int;
	
	SET lastReminder = (SELECT `_id` 
		FROM `Reminders`
		WHERE `userId` = p_userId
		AND `medicationId` = medicationId
		AND `timestamp` = (SELECT DISTINCT MAX(`timestamp`) 
			FROM `Reminders`
			WHERE `userId` = p_userId
			AND `medicationId` = p_medicationId
			AND status IN ('active', 'snoozed')
			LIMIT 1
		)
		LIMIT 1
	);
	
	return lastReminder;
END 
//
commit //

delimiter ;

/* FIND LAST REMINDER LINKED TO A MEDICINE */
/* =============================================================================================================== */
drop function if exists findLastReminderIfShouldUpdateDosage;
commit;

delimiter //

CREATE FUNCTION `findLastReminderIfShouldUpdateDosage`(`p_medicationId` varchar(36), `p_userId` bigint)
RETURNS INT
BEGIN

	DECLARE lastReminder int;
	
	SET lastReminder = (SELECT `R`.`_id` 
		FROM `Reminders` `R`
		INNER JOIN `Medications` `M` ON `R`.`medicationId` = `M`.`remoteId` AND `R`.`userId` = `M`.`userId`
		WHERE `R`.`userId` = p_userId
		AND `R`.`medicationId` = medicationId
		AND `R`.`dosage` != `M`.`dosage`
		AND `R`.`timestamp` = (SELECT DISTINCT MAX(`timestamp`) 
			FROM `Reminders`
			WHERE `userId` = p_userId
			AND `medicationId` = p_medicationId
			AND status IN ('active', 'snoozed')
			LIMIT 1
		)
		LIMIT 1
	);
	
	return lastReminder;
END 
//
commit //

delimiter ;

/* FIND LAST REMINDER TIMESTAMP TO CHECK END-DATE OF MEDICATION */
/* =============================================================================================================== */
drop function if exists findMedicationEndDate;
commit;

delimiter //

CREATE FUNCTION `findMedicationEndDate`(`p_medicationId` varchar(36), `p_userId` bigint)
RETURNS DATETIME
BEGIN

	DECLARE dtLastTimestamp DATETIME;
	
	SET dtLastTimestamp = (SELECT DISTINCT MAX(`timestamp`) 
			FROM `Reminders`
			WHERE `userId` = p_userId
			AND `medicationId` = p_medicationId
			AND status IN ('active', 'snoozed', 'skipped', 'missed', 'taken')
		);
	
	return dtLastTimestamp;
END 
//
commit //

delimiter ;

/* FIND MEDICATION ID LINKED TO REMINDER */
/* =============================================================================================================== */
drop function if exists findReminderMedication;
commit;

delimiter //

CREATE FUNCTION `findReminderMedication`(`p_reminderId` varchar(36), `p_userId` bigint)
RETURNS VARCHAR(36)
BEGIN

	DECLARE vMedicationId VARCHAR(36);
	
	SET vMedicationId = (
		SELECT `medicationId` 
		FROM `Reminders`
		WHERE `userId` = p_userId
		AND `remoteId` = p_reminderId
	);
	
	RETURN vMedicationId;
END 
//
commit //

delimiter ;

/* FIND STATUS LINKED TO REMINDER */
/* =============================================================================================================== */
drop function if exists findMedicationReminderStatus;
commit;

delimiter //

CREATE FUNCTION `findMedicationReminderStatus`(`p_reminderId` varchar(36), `p_userId` bigint)
RETURNS VARCHAR(15)
BEGIN

	DECLARE vReminderStatus VARCHAR(15);
	
	SET vReminderStatus = (
		SELECT IF(`M`.`status` <> 'active', `M`.`status`, `R`.`status`) as `status` 
		FROM `Reminders` `R` 
		INNER JOIN `Medications` `M` ON `R`.`medicationId` = `M`.`remoteId` AND `R`.`userId` = `M`.`userId` 
		WHERE `R`.`remoteId` = p_reminderId
		AND `R`.`userId` = p_userId
	);
	
	RETURN vReminderStatus;
END 
//
commit //

delimiter ;
