/* IMPORT SCRIPT FOR PHARMACY */
/* =============================================================================================================== */
DROP PROCEDURE IF EXISTS importScriptPharmacy;
COMMIT;

DELIMITER //

CREATE PROCEDURE `importScriptPharmacy`(`p_system` VARCHAR(20), `p_name` VARCHAR(40), `p_practiceNum` VARCHAR(20), 
  `p_email` VARCHAR(100), `p_address1` VARCHAR(50), `p_address2` VARCHAR(50), `p_address3` VARCHAR(50), 
  `p_addressCode` VARCHAR(4))
BEGIN

DECLARE errno INT;
DECLARE errsqlstate VARCHAR(100);

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

  ROLLBACK;

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

  IF errsqlstate = '45000' THEN
    RESIGNAL;
  END IF;

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

START TRANSACTION;

  IF p_system IS NULL OR p_system = '' THEN 
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = "Pharmacy 'system' is required. No 'system' property provided.";
  END IF;

  INSERT IGNORE INTO `Pharmacies`(`system`, `name`, `practiceNum`, `email`, `address1`, `address2`, `address3`, 
    `addressCode`, `createdAt`, `updatedAt`)
  VALUES (p_system, p_name, p_practiceNum, p_email, p_address1, p_address2, p_address3, p_addressCode, NOW(), NOW());

  SELECT `ID`
  FROM `Pharmacies`
  WHERE `name` = p_name
  AND `practiceNum` = p_practiceNum;

COMMIT WORK;

END
//

COMMIT //

DELIMITER ;

-- CALL importScriptPharmacy('diverseit', 'AZ Pharmacy', 'A70Z0FM3D1C1N35', 'pharmacy@atozofmedicines.com', 'The Planet Art', 'Somewhere in Cape Town', NULL, '0000');