/* ADDS A NEW NOTIFICATION TO THE NOTIFICATION QUEUE TO BE SENT */
/* =============================================================================================================== */
DROP PROCEDURE IF EXISTS addNotificationToQueue;
COMMIT;

DELIMITER //

CREATE PROCEDURE `addNotificationToQueue`(`p_userId` INT, `p_templateId` INT, `p_startDate` DATETIME, 
  `p_endDate` DATETIME, `p_due` DATETIME, `p_payload` TEXT)
BEGIN

DECLARE errno INT;
DECLARE iEmail INT;

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

	INSERT INTO debug_log(activity, ts_when, log) 
  SELECT 'PROC ERR: loadNotif', NOW(), errno; 

	RESIGNAL;
END;

START TRANSACTION;

  INSERT IGNORE INTO `NotificationQueue`(`userId`, `templateId`, `startDate`, `endDate`, `actionPayload`, `due`, 
    `status`, `token`, `attempts`, `ticket`, `receipt`, `error`, `sentAt`, `createdAt`, `updatedAt`)
  SELECT `_id`, p_templateId, p_startDate, p_endDate, p_payload, p_due, 'ready', `pushToken`, 0, NULL, NULL, 
    NULL, NULL, NOW(), NOW()
  FROM `Users`
  WHERE `_id` = p_userId
  AND `status` = 'active';

COMMIT WORK;
END
//

COMMIT //

DELIMITER ;

