/* UPDATE SUBSCRIPTIONS LINKED TO USER/BUDDY/BENEFICIARY EMAIL */
/* =============================================================================================================== */
DROP PROCEDURE IF EXISTS updateEmailSubscriptions;
COMMIT;

DELIMITER //

CREATE PROCEDURE `updateEmailSubscriptions`(`p_email` VARCHAR(255), `p_subscriptions` VARCHAR(255))

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

  RESIGNAL;
END;

START TRANSACTION;

  UPDATE `EmailSubscriptions`
  SET `isActive` = 0,
    `updatedAt` = NOW()
  WHERE `email` = p_email
  AND `isActive` = 1
  AND `category` != 1;

  UPDATE `EmailSubscriptions`
  SET `isActive` = 1,
    `updatedAt` = NOW()
  WHERE `email` = p_email
  AND FIND_IN_SET(`category`, p_subscriptions)
  AND isActive = 0;

COMMIT WORK; 

END
//

COMMIT //

DELIMITER ;
