/* Schedule Welcome notifications */
/* =============================================================================================================== */
DROP PROCEDURE IF EXISTS scheduleWelcomeNotifications;
COMMIT;

DELIMITER //

CREATE PROCEDURE `scheduleWelcomeNotifications`()
BEGIN

DECLARE errno 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: getNotifs', 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 `U`.`_id`, `T`.`_id`, NOW(), DATE_ADD(NOW(), INTERVAL 3 DAY), NULL, NOW(), 'ready', `U`.`pushToken`,
    0, NULL, NULL, NULL, NULL, NOW(), NOW()
  FROM `Users` AS `U`
  INNER JOIN `NotificationTemplates` AS `T` ON `T`.`title` LIKE 'Welcome! Let\'s get started.' AND `T`.`isActive` = 1
  WHERE (
    SELECT COUNT(*)
    FROM `Medications`
    WHERE `userId` = `U`.`_id`
  ) = 0
  AND `U`.`status` = 'active'
  AND `U`.`pushToken` IS NOT NULL
  AND (
    SELECT COUNT(*) 
    FROM `NotificationQueue`
    WHERE `userId` = `U`.`_id`
    AND `templateId` = `T`.`_id`
  ) = 0;

COMMIT WORK;
END
//

COMMIT //

DELIMITER ;

