/* CALCULATE AND SAVE POST CATEGORIES LINKED TO USERS BASED ON MEDICATIONS */
/* =============================================================================================================== */
DROP PROCEDURE IF EXISTS savePostSubscriptions;
COMMIT;

DELIMITER //

CREATE PROCEDURE `savePostSubscriptions`()

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: savePSubs', now(), errno; 

  RESIGNAL;
END;

START TRANSACTION;

  INSERT INTO `PostSubscriptions`(`userId`, `categories`, `createdAt`, `updatedAt`)
  SELECT `U`.`_id`, GET_POST_CATEGORIES(`U`.`_id`),  NOW(), NOW() 
  FROM `Users` AS `U`
  WHERE `U`.`status` = 'active'
  ON DUPLICATE KEY
  UPDATE `categories` = GET_POST_CATEGORIES(`U`.`_id`),
  `updatedAt` = NOW();

  INSERT INTO `QuizSubscriptions`(`userId`, `categories`, `createdAt`, `updatedAt`)
  SELECT `U`.`_id`, GET_QUIZ_CATEGORIES(`U`.`_id`),  NOW(), NOW() 
  FROM `Users` AS `U`
  WHERE `U`.`status` = 'active'
  ON DUPLICATE KEY
  UPDATE `categories` = GET_QUIZ_CATEGORIES(`U`.`_id`),
  `updatedAt` = NOW();

  SELECT * 
  FROM `PostSubscriptions`;
  
  SELECT * 
  FROM `QuizSubscriptions`;

COMMIT WORK; 

END
//

COMMIT //

DELIMITER ;
