/* IMPORT SCRIPT FOR BENEFICIARY DRUG LINE */
/* =============================================================================================================== */
DROP PROCEDURE IF EXISTS importScriptDrugLine;
COMMIT;

DELIMITER //

CREATE PROCEDURE `importScriptDrugLine`(`p_beneficiaryScriptId` INT, `p_nappi` VARCHAR(10), `p_name` VARCHAR(40), 
  `p_qtyDispensed` FLOAT, `p_qtyPerDose` FLOAT, `p_frequency` VARCHAR(10), `p_repeats` INT, 
  `p_directions` VARCHAR(1000), `p_lineNumber` VARCHAR(30))
BEGIN

DECLARE errno INT;
DECLARE tFrequencyExists TINYINT;

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

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

START TRANSACTION;

  IF p_frequency IS NOT NULL THEN 
    SELECT COUNT(*) > 0
    FROM `ScriptFrequencies`
    WHERE `frequency` = LOWER(p_frequency)
    INTO tFrequencyExists;
  ELSE
    SELECT 0
    INTO tFrequencyExists;
  END IF;

  IF p_frequency IS NOT NULL AND tFrequencyExists = 0 THEN
    INSERT INTO `ScriptFrequencies`(`frequency`, `description`, `time1`, `time2`, `time3`, `time4`, `status`, 
      `createdAt`, `updatedAt`)
    VALUES (LOWER(p_frequency), NULL, '07:00', NULL, NULL, NULL, 'unready', NOW(), NOW());

    SELECT COUNT(*) > 0
    FROM `ScriptFrequencies`
    WHERE `frequency` = LOWER(p_frequency)
    INTO tFrequencyExists;
  END IF;

  INSERT INTO `BeneficiaryScriptLines`(`beneficiaryScriptId`, `nappi`, `name`, `qtyDispensed`, `qtyPerDose`, 
    `frequency`, `repeats`, `directions`, `lineNumber`, `status`, `createdAt`, `updatedAt`)
  SELECT p_beneficiaryScriptId, p_nappi, p_name, p_qtyDispensed, p_qtyPerDose, 
    IF(tFrequencyExists = 0, 'default', p_frequency), p_repeats, p_directions, p_lineNumber, 'IMPORTING', NOW(), NOW();

  COMMIT WORK;

END
//

COMMIT //

DELIMITER ;

-- CALL importScriptDrugLine(7, '123456', 'AZ Vitamin', '30', '1', 'bd'', NULL, 'Take one tablet twice per day', '11111111111111');