/* GET THE SUBSCRIPTIONS LINKED TO AN EMAIL */
/* =============================================================================================================== */
DROP PROCEDURE IF EXISTS getEmailSubscriptions;
COMMIT;

DELIMITER //

CREATE PROCEDURE `getEmailSubscriptions`(`p_email` VARCHAR(255))

BEGIN

DECLARE errno INT;
DECLARE tIsUser BIGINT DEFAULT NULL;
DECLARE tIsUserVerified TINYINT DEFAULT NULL;
DECLARE tIsBuddy TINYINT DEFAULT NULL;
DECLARE tIsBeneficiary TINYINT DEFAULT NULL;
DECLARE iSubscriptionCount INT DEFAULT 0;

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

  RESIGNAL;
END;

START TRANSACTION;

  SELECT COUNT(*)
  FROM `EmailSubscriptions`
  WHERE `email` = p_email
  AND `isActive` = 1
  INTO iSubscriptionCount;

  IF iSubscriptionCount IS NULL OR iSubscriptionCount = 0 THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'No subscriptions to unsubscribe from.';
  END IF;

  DROP TEMPORARY TABLE IF EXISTS `t_subscriptions`;
  CREATE TEMPORARY TABLE `t_subscriptions` (
    `categoryId` BIGINT NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(100) NOT NULL,
    `description` VARCHAR(550) NOT NULL,
    `isActive` TINYINT NOT NULL DEFAULT 0,
    `type` VARCHAR(36) DEFAULT NULL,
    PRIMARY KEY (`categoryId`)
  ) ENGINE=MEMORY;

  SELECT `_id`, `emailVerified` = GET_MISC_VALUE('email_verification', 'verified')
  FROM `Users`
  WHERE `email` = p_email
  AND `status` = 'active'
  INTO tIsUser, tIsUserVerified;

  SELECT COUNT(*)
  FROM `MedBuddies`
  WHERE `email` = p_email
  AND `status` = 'active'
  INTO tIsBuddy;

  SELECT COUNT(*)
  FROM `BeneficiaryScripts`
  WHERE `email` = p_email
  AND `status` = 'INVITED'
  INTO tIsBeneficiary;

  IF tIsUser IS NOT NULL AND tIsUser > 0 THEN

    IF tIsUserVerified THEN
      INSERT INTO `t_subscriptions`(`categoryId`, `name`, `description`)
      SELECT `_id`, `name`, `description`
      FROM `EmailCategories`
      WHERE FIND_IN_SET('user', `isSubscription`);
    ELSE 
      INSERT INTO `t_subscriptions`(`categoryId`, `name`, `description`)
      SELECT `_id`, `name`, `description`
      FROM `EmailCategories`
      WHERE FIND_IN_SET('verification', `isSubscription`);
    END IF;
    
  ELSEIF tIsBeneficiary IS NOT NULL AND tIsBeneficiary > 0 THEN

    INSERT INTO `t_subscriptions`(`categoryId`, `name`, `description`)
    SELECT `_id`, `name`, `description`
    FROM `EmailCategories`
    WHERE FIND_IN_SET('beneficiary', `isSubscription`);

  END IF;

  IF tIsBuddy IS NOT NULL AND tIsBuddy > 0 THEN
    INSERT INTO `t_subscriptions`(`categoryId`, `name`, `description`)
    SELECT `_id`, `name`, `description`
    FROM `EmailCategories`
    WHERE FIND_IN_SET('buddy', `isSubscription`);
  END IF;

  SELECT COUNT(*)
  FROM `t_subscriptions`
  INTO iSubscriptionCount;

  IF iSubscriptionCount IS NULL OR iSubscriptionCount = 0 THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'No subscriptions to unsubscribe from.';
  END IF;

  UPDATE `t_subscriptions` AS `T`
  INNER JOIN `EmailSubscriptions` AS `ES` ON `T`.`categoryId` = `ES`.`category`
  SET `T`.`isActive` = `ES`.`isActive`,
    `T`.`type` = `ES`.`type`;

  SELECT * 
  FROM `t_subscriptions`;

COMMIT WORK; 

END
//

COMMIT //

DELIMITER ;
