/* =============================================================================================================== */
-- Name: getAccountCredentialsWithEmail
-- Purpose: Get account credentials linked to email address
-- Parameters:
--    > p_email VARCHAR(255) - the user's email address
--    > p_activeAccount TINYINT - should db check if account is active (1) or not (0)
-- Return:
--    > User's _id, salt, encryptedPassword and status
/* =============================================================================================================== */

DROP PROCEDURE IF EXISTS getAccountCredentialsWithEmail;
COMMIT;

DELIMITER //

CREATE PROCEDURE `getAccountCredentialsWithEmail`(`p_email` VARCHAR(255), `p_activeAccount` TINYINT)

BEGIN

DECLARE errno INT;
DECLARE iUserId BIGINT;
DECLARE vUserStatus VARCHAR(12);

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:getUserCred', NOW(), errno; 

  RESIGNAL;
END;

SELECT `_id`, `status`
FROM `Users` 
WHERE `email` = p_email
INTO iUserId, vUserStatus;

IF p_activeAccount > 0 AND iUserId IS NOT NULL AND vUserStatus != 'active' THEN
  IF vUserStatus = 'deleted' THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'This account has been deleted.';
  ELSEIF vUserStatus = 'inactive' THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'This account has been deactivated. Please contact us if you want to recover your account.';
  ELSE
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = "Something is wrong with this account. Please report this issue and we'll fix it asap.";
  END IF;
END IF;

SELECT `_id`, `salt`, `encryptedPassword`, `status`
FROM `Users`
WHERE `_id` = iUserId;

END
//

COMMIT //

DELIMITER ;
