/* IMPORT SCRIPT FOR BENEFICIARY */
/* =============================================================================================================== */
DROP PROCEDURE IF EXISTS importScriptBeneficiary;
COMMIT;

DELIMITER //

CREATE PROCEDURE `importScriptBeneficiary`(`p_pharmacyId` INT, `p_identifier` VARCHAR(20), `p_firstName` VARCHAR(30), 
  `p_surname` VARCHAR(30), `p_gender` VARCHAR(1), `p_birthDate` VARCHAR(15), `p_IDnumber` VARCHAR(15), 
  `p_email` VARCHAR(100), `p_cellphone` VARCHAR(20), `p_lines` INT)
BEGIN

DECLARE errno INT;
DECLARE iID 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: isBenefici', NOW(), errno; 

  SIGNAL SQLSTATE '45000'
  SET MESSAGE_TEXT = 'Failed to import script beneficiary.';
END;

START TRANSACTION;

INSERT INTO `BeneficiaryScripts`(`pharmacyID`, `beneficiaryIdentifier`, `firstName`, `surname`, `gender`, 
  `birthDate`, `IDnumber`, `email`, `cellphone`, `status`, `lines`, `inviteDate`, `inviteCount`, `createdAt`, 
  `updatedAt`)
VALUES (p_pharmacyId, p_identifier, p_firstName, p_surname, p_gender, p_birthDate, p_IDnumber, p_email, p_cellphone, 
  'IMPORTING', p_lines, NULL, 0, NOW(), NOW());

SELECT LAST_INSERT_ID() AS `ID`;

COMMIT WORK;

END
//

COMMIT //

DELIMITER ;

-- CALL importScriptBeneficiary(1, '0123456789', 'John', 'Doe', 'M', '20010101', '010101234568', 'johndoe@somedomain.com', '0820820820', 2);