DROP PROCEDURE IF EXISTS authoriseRemeToken;
COMMIT;

DELIMITER //

CREATE PROCEDURE `authoriseRemeToken`(`p_key` VARCHAR(36), `p_route` VARCHAR(255))

BEGIN

DECLARE errno INT;
DECLARE iProviderId INTEGER DEFAULT NULL;
DECLARE tProviderAuthorised TINYINT DEFAULT NULL;

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

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

  RESIGNAL;
END;

SELECT `_id`
FROM `Providers`
WHERE `key` = p_key
AND `status` = 'active'
AND IF(`keyExpireInterval` IS NULL, `keyExpireAt` IS NULL, TIMESTAMPDIFF(MINUTE, NOW(), `keyExpireAt`) >= 0)
INTO iProviderId;

IF iProviderId IS NULL THEN
  SIGNAL SQLSTATE '45000'
  SET MESSAGE_TEXT = 'You are not authorised to access this endpoint.';
END IF;

UPDATE `Providers` 
SET `requests` = `requests` + 1,
  `lastRequestedAt` = NOW(),
  `updatedAt` = NOW()
WHERE `_id` = iProviderId;

SELECT IF(`P`.`_id` IS NOT NULL, 1, 0) AS `authorised`
FROM `Providers` AS `P`
INNER JOIN `Routes` AS `R` 
  ON `P`.`routes` LIKE CONCAT('%|', `R`.`_id`, '|%')
WHERE `P`.`_id` = iProviderId
AND `R`.`_id` = GET_ROUTE_ID(p_route)
AND `R`.`isActive` = 1
AND `R`.`private` = 0
INTO tProviderAuthorised;

IF tProviderAuthorised IS NULL OR tProviderAuthorised != 1 THEN
  SIGNAL SQLSTATE '45000'
  SET MESSAGE_TEXT = 'You are not authorised to access this endpoint.';
END IF;

SELECT iProviderId AS `providerId`;

END 
//

COMMIT //

DELIMITER ;
