/* GET NEXT NOTIFICATION FOR QUEUE TO HANDLE */
/* =============================================================================================================== */
DROP PROCEDURE IF EXISTS getNextNotificationInQueue;
COMMIT;

DELIMITER //

CREATE PROCEDURE `getNextNotificationInQueue`()
BEGIN

DECLARE errno INT;
DECLARE errmsg VARCHAR(128);
DECLARE iNotification INT;

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
	GET CURRENT DIAGNOSTICS CONDITION 1 errno = MYSQL_ERRNO;
	GET CURRENT DIAGNOSTICS CONDITION 1 errmsg = MESSAGE_TEXT;
  
	ROLLBACK;

  IF errmsg != 'All notifications due in the queue has been sent.' THEN 
    INSERT INTO debug_log(activity, ts_when, log) 
    SELECT 'PROC ERR: nextNotif', NOW(), errno; 
  END IF;

	RESIGNAL;
END;

START TRANSACTION;

SELECT `_id`
FROM `NotificationQueue`
WHERE TIMESTAMPDIFF(SECOND, `due`, NOW()) >= 0
AND IF(`endDate` IS NULL, 1, TIMESTAMPDIFF(SECOND, `endDate`, NOW()) <= 0)
AND `status` = 'ready'
ORDER BY `createdAt` DESC
LIMIT 1
INTO iNotification;

IF iNotification IS NULL THEN
  SIGNAL SQLSTATE '45000'
  SET MESSAGE_TEXT = 'All notifications due in the queue has been sent.';
END IF;

SELECT `q`.`_id` AS queueId, `q`.`startDate`, `q`.`endDate`, `q`.`actionPayload`, `q`.`token`, `t`.`_id` AS Id, 
  `t`.`title`, `t`.`subtitle`, `t`.`targets`, `t`.`priority`, `t`.`actionKey`, `t`.`actionLabel`, 
  `t`.`dismissLabel`, `t`.`actionMessage`, `t`.`color`, `t`.`background`
FROM `NotificationQueue` AS `q`
INNER JOIN `NotificationTemplates` AS `t` on `q`.`templateId` = `t`.`_id`
WHERE `q`.`_id` = iNotification;

UPDATE `NotificationQueue`
SET `status` = 'sending'
WHERE `_id` = iNotification;

COMMIT WORK;
END
//

COMMIT //

DELIMITER ;

