/* CALCULATE AVERAGE SUGAR FOR USER WITHIN TIMESPAN */
/* =============================================================================================================== */
DROP FUNCTION IF EXISTS GET_AVERAGE_SUGAR;
COMMIT;

delimiter //

CREATE FUNCTION `GET_AVERAGE_SUGAR`(`p_userId` varchar(36), `p_start` datetime, `p_end` datetime)
RETURNS DECIMAL(10, 2)
BEGIN

	DECLARE avgSugar DECIMAL(10, 2);
	
	SET avgSugar = (
    SELECT ROUND(SUM(IF(`unit` = 'mmol/L', `sugar`, `sugar`/18))/COUNT(`sugar`) * 100) / 100
    FROM `Sugars`
    WHERE `userId` = p_userId
    AND `day` >= p_start
    AND `day` < p_end
    AND `status` NOT IN ('inactive', 'delete')
    AND `dependant` IS NULL
  );
	
	RETURN avgSugar;

END 
//
COMMIT //

delimiter ;
