/* =============================================================================================================== */
-- Name: authoriseUserId
-- Purpose: Authorise a user based on his/her userId
-- Parameters:
--    > p_userId BIGINT - the user's Id that needs authorizing
-- Return:
--    > User: authorised, active, deleted
/* =============================================================================================================== */

DROP PROCEDURE IF EXISTS authoriseUserId;
COMMIT;

DELIMITER //

CREATE PROCEDURE `authoriseUserId`(`p_userId` BIGINT)

BEGIN

DECLARE errno INT;
DECLARE tIsUserActive TINYINT DEFAULT 0;
DECLARE tIsUserDeleted TINYINT DEFAULT 0;
DECLARE tIsAuthrised TINYINT DEFAULT 0;

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

  RESIGNAL;
END;

SELECT COUNT(*)
FROM `Users`
WHERE `_id` = p_userId
AND `status` = 'active'
INTO tIsUserActive;

SELECT COUNT(*)
FROM `DeletedUsers`
WHERE `_id` = p_userId
INTO tIsUserDeleted;

SELECT IF(tIsUserActive > 0, 1, 0) AS `authorised`, tIsUserActive AS `active`, tIsUserDeleted AS `deleted`;

END 
//

COMMIT //

DELIMITER ;
