/* PROCESS BENEFICIARY SCRIPTS TO READY IF ALREADY A USER AND GET EMAILS TO INVITE IF NOT ALREADY A USER */
/* =============================================================================================================== */
drop procedure if exists processImportedBeneficiaryScripts;
commit;

delimiter //

CREATE PROCEDURE `processImportedBeneficiaryScripts`()

BEGIN

DECLARE errno INT;

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
  GET CURRENT DIAGNOSTICS CONDITION 1 errno = MYSQL_ERRNO;

	INSERT INTO debug_log(activity, ts_when, log) 
  SELECT 'PROC ERR: processIBS', now(), errno; 

  RESIGNAL;
END;

START TRANSACTION;

  UPDATE `BeneficiaryScripts` AS `BS`
  INNER JOIN `Users` AS `U` ON `BS`.`email` = `U`.`email`
  SET `BS`.`status` = 'INVITED',
    `BS`.`updatedAt` = NOW()
  WHERE `BS`.`status` = 'READY';

  UPDATE `BeneficiaryScriptLines` AS `BSL`
  INNER JOIN `BeneficiaryScripts` AS `BS` ON `BSL`.`beneficiaryScriptId` = `BS`.`ID`
  INNER JOIN `Users` AS `U` ON `BS`.`email` = `U`.`email`
  SET `BSL`.`status` = 'READY',
    `BSL`.`updatedAt` = NOW()
  WHERE `BS`.`status` = 'INVITED'
  AND `BSL`.`status` = 'PENDING'
  AND `U`.`status` = 'active'
  AND `U`.`emailVerified` = GET_MISC_VALUE('email_verification', 'verified');

  SELECT DISTINCT `BS`.`email`, `BS`.`firstName`, `BS`.`surname`, `P`.`name` as `pharmacy`, `BS`.`status`
  FROM `BeneficiaryScripts` AS `BS`
  INNER JOIN `Pharmacies` AS `P` ON `BS`.`pharmacyID` = `P`.`ID`
  WHERE `BS`.`status` = 'READY';
  -- UNION 
  -- SELECT DISTINCT `BS`.`email`, `BS`.`firstName`, `BS`.`surname`, `P`.`name` as `pharmacy`, `BS`.`status`
  -- FROM `BeneficiaryScripts` AS `BS`
  -- INNER JOIN `Pharmacies` AS `P` ON `BS`.`pharmacyID` = `P`.`ID`
  -- INNER JOIN `Users` AS `U` ON `BS`.`email` = `U`.`email`
  -- WHERE `BS`.`status` = 'INVITED'
  -- AND `U`.`emailVerified` = 0;

COMMIT WORK;

END
//

commit //

delimiter ;
