/* VALIDATE IMPORT SCRIPT `BeneficiaryScript`.`lines` WITH COUNT(`beneficiaryScriptLines`) */
/* =============================================================================================================== */
DROP PROCEDURE IF EXISTS importScriptValidator;
COMMIT;

DELIMITER //

CREATE PROCEDURE `importScriptValidator`(`p_beneficiaryScriptId` INT)
BEGIN

DECLARE errno INT;
DECLARE iLines INT;
DECLARE iLinesCount 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: isValidate', NOW(), errno; 

  SIGNAL SQLSTATE '45000'
  SET MESSAGE_TEXT = 'Failed to validate beneficiary scripts.';
END;

START TRANSACTION;

SELECT `lines` 
FROM `BeneficiaryScripts`
WHERE `ID` = p_beneficiaryScriptId 
INTO iLines;

SELECT COUNT(`ID`) 
FROM `BeneficiaryScriptLines` 
WHERE `beneficiaryScriptId` = p_beneficiaryScriptId
INTO iLinesCount;

IF iLines = iLinesCount THEN
  UPDATE `BeneficiaryScripts` 
  SET `status` = 'READY'
  WHERE `ID` = p_beneficiaryScriptId;
ELSE 
  UPDATE `BeneficiaryScripts`
  SET `status` = `INCOMPLETE`
  WHERE `ID` = p_beneficiaryScriptId;
END IF;

COMMIT WORK;

END
//

COMMIT //

DELIMITER ;

-- CALL importScriptValidator(1)