/* GET THE BEINIFICIARY SCRIPTS LINKED TO USER EMAIL (VERIFIED) */
/* =============================================================================================================== */
DROP PROCEDURE IF EXISTS getUserBeneficiaryScript;
COMMIT;

DELIMITER //

CREATE PROCEDURE `getUserBeneficiaryScript`(`p_userId` BIGINT, `p_scriptCount` INT, `p_transaction` VARCHAR(36))

BEGIN

DECLARE errno INT;
DECLARE tEmailVerified 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: getUBS', now(), errno; 

  RESIGNAL;
END;

START TRANSACTION;

  IF p_scriptCount IS NOT NULL AND p_scriptCount > 0 THEN 
    UPDATE `BeneficiaryScriptLines` AS `BSL`
    INNER JOIN `TempBeneficiaryScriptLineStatuses` AS `TBSLS` ON `BSL`.`ID` = `TBSLS`.`BeneficiaryScriptLineId`
    SET `BSL`.`status` = `TBSLS`.`status`,
      `BSL`.`updatedAt` = NOW()
    WHERE `TBSLS`.`transaction` = p_transaction
    AND `BSL`.`status` IN ('LOADED', 'READY');

    DELETE FROM `TempBeneficiaryScriptLineStatuses`
    WHERE `transaction` = p_transaction;
  END IF;

  DROP TEMPORARY TABLE IF EXISTS `t_bsl`;

  CREATE TEMPORARY TABLE `t_bsl` (
    `scriptLineId` INT NOT NULL,
    `scriptId` INT NOT NULL,
    `userId` BIGINT NOT NULL,
    `pharmacyName` VARCHAR(40) NOT NULL, 
    `pharmacyAddress1` VARCHAR(50) DEFAULT NULL,
    `pharmacyAddress2` VARCHAR(50) DEFAULT NULL,
    `pharmacyAddress3` VARCHAR(50) DEFAULT NULL,
    `pharmacyAddressCode` VARCHAR(4) DEFAULT NULL,
    `medicineId` INT NOT NULL, 
    `FullName` VARCHAR(255) NOT NULL, 
    `TradeName` VARCHAR(255) NOT NULL, 
    `TradeStr` VARCHAR(255) DEFAULT NULL, 
    `TradeForm` VARCHAR(255) DEFAULT NULL, 
    `Schedule` VARCHAR(255) DEFAULT NULL,   
    `TradeManufacturer` VARCHAR(255) DEFAULT NULL, 
    `ImageDate` DATETIME DEFAULT NULL, 
    `amount` INT NOT NULL,
    `dosage` INT NOT NULL,
    `frequencyDescription` VARCHAR(255) DEFAULT NULL,
    `time1` VARCHAR(10) NOT NULL,
    `time2` VARCHAR(10) DEFAULT NULL,
    `time3` VARCHAR(10) DEFAULT NULL,
    `time4` VARCHAR(10) DEFAULT NULL,
    `status` VARCHAR(10) NOT NULL,
    `ischronic` TINYINT NOT NULL,
    `date` DATE NOT NULL,
    PRIMARY KEY (`scriptLineId`)
  ) ENGINE=MEMORY;

  INSERT INTO `t_bsl`(`scriptLineId`, `scriptId`, `userId`, `pharmacyName`, `pharmacyAddress1`, `pharmacyAddress2`, 
    `pharmacyAddress3`, `pharmacyAddressCode`, `medicineId`, `FullName`, `TradeName`, `TradeStr`, `TradeForm`, 
    `Schedule`, `TradeManufacturer`, `ImageDate`, `amount`, `dosage`, `frequencyDescription`, `time1`, `time2`, 
    `time3`, `time4`, `status`, `isChronic`, `date`)
  SELECT `BSL`.`ID`, `BS`.`ID`, `U`.`_id`, `P`.`name`, `P`.`address1`, `P`.`address2`, `P`.`address3`, 
    `P`.`addressCode`, `M`.`ID` AS `medicineId`, `M`.`FullName`, `M`.`TradeName`, `M`.`TradeStr`, `M`.`TradeForm`,
    `M`.`Schedule`, `M`.`TradeManufacturer`, `M`.`ImageDate`, `BSL`.`qtyDispensed`, `BSL`.`qtyPerDose`, 
    `SF`.`description`, `SF`.`time1`, `SF`.`time2`, `SF`.`time3`, `SF`.`time4`, `BSL`.`status`, 
    IF(`BSL`.`qtyDispensed`/`BSL`.`qtyPerDose` > 25, 1, 0), `BSL`.`createdAt`
  FROM `BeneficiaryScriptLines` AS `BSL`
  INNER JOIN `BeneficiaryScripts` AS `BS` ON `BSL`.`beneficiaryScriptId` = `BS`.`ID`
  INNER JOIN `Users` AS `U` ON `BS`.`email` = `U`.`email`
  INNER JOIN `Pharmacies` AS `P` ON `BS`.`pharmacyID` = `P`.`ID`
  INNER JOIN `AtoZofMeds`.`TradeNames` AS `T` ON `BSL`.`nappi` = `T`.`Nappi`
  INNER JOIN `Medicines` AS `M` ON `T`.`ID` = `M`.`ID`
  INNER JOIN `ScriptFrequencies` AS `SF` ON `BSL`.`frequency` = `SF`.`frequency`
  WHERE `BSL`.`status` IN ('READY', 'LOADED')
  AND `U`.`_id` = p_userId
  AND `U`.`status` = 'active'
  AND `U`.`emailVerified` = GET_MISC_VALUE('email_verification', 'verified');

  UPDATE `BeneficiaryScriptLines`
  SET `status` = 'LOADED',
    `updatedAt` = NOW()
  WHERE `ID` IN (
    SELECT `scriptLineId`
    FROM `t_bsl`
  );

  SELECT * 
  FROM `t_bsl`;

COMMIT WORK; 

END
//

COMMIT //

DELIMITER ;
