/* INVITE SCRIPT BENEFICIARY THAT'S NOT YET A USER ON REME */
/* =============================================================================================================== */
DROP PROCEDURE IF EXISTS inviteScriptBeneficiary;
COMMIT;

DELIMITER //

CREATE PROCEDURE `inviteScriptBeneficiary`(`p_email` VARCHAR(255), `p_due` DATETIME, `p_expire` DATETIME, 
  `p_payload` TEXT)

BEGIN

DECLARE errno INT;
DECLARE iTemplateId INT;
DECLARE iCategory 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: inviteSB', NOW(), errno; 

  RESIGNAL;
END;

START TRANSACTION;

  SELECT `_id`, `category`
  FROM `EmailTemplates`
  WHERE `name` = 'userInvitation'
  INTO iTemplateId, iCategory;

  CALL addEmailToQueue(p_email, iTemplateId, p_due, p_expire, p_payload);

  INSERT INTO `EmailSubscriptions`(`email`, `type`, `category`, `isActive`, `createdAt`, `updatedAt`)
  VALUES (p_email, 'beneficiary', iCategory, 1, NOW(), NOW())
  ON DUPLICATE KEY
  UPDATE `isActive` = 1,
    `updatedAt` = NOW();

  UPDATE `BeneficiaryScripts`
  SET `status` = 'INVITING',
    `inviteDate` = NOW(),
    `inviteCount` = 1,
    `updatedAt` = NOW()
  WHERE `status` = 'READY'
  AND `email` = p_email;

COMMIT WORK;

END
//

COMMIT //

DELIMITER ;
