/* Fetched notifications to schedule */
/* =============================================================================================================== */
DROP PROCEDURE IF EXISTS scheduleProfileNotifications;
COMMIT;

DELIMITER //

CREATE PROCEDURE `scheduleProfileNotifications`()
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 'Who are you?' AND `T`.`isActive` = 1
  WHERE (
    `U`.`gender` IS NULL 
    OR `U`.`dateOfBirth` IS NULL 
    OR (`U`.`name` IS NULL AND `U`.`surname` IS NULL) 
    OR (`U`.`mobileNumber` IS NULL AND `U`.`country` IS NULL)
  )
  AND `U`.`status` = 'active'
  AND `U`.`pushToken` IS NOT NULL
  AND ((
    SELECT COUNT(*) 
    FROM `NotificationQueue`
    WHERE `userId` = `U`.`_id`
    AND `templateId` = `T`.`_id`
  ) = 0 OR DATEDIFF(NOW(), (
    SELECT MAX(`endDate`)
    FROM `NotificationQueue`
    WHERE `userId` = `U`.`_id`
    AND `templateId` = `T`.`_id`
  )) > 15);

COMMIT WORK;
END
//

COMMIT //

DELIMITER ;

