/* =============================================================================================================== */
DROP PROCEDURE IF EXISTS authoriseRemeUser;
COMMIT;

DELIMITER //

CREATE PROCEDURE `authoriseRemeUser`(`p_key` VARCHAR(36), `p_route` VARCHAR(255), `p_userId` BIGINT, `p_authId` INT, 
  `p_authType` VARCHAR(10))

BEGIN

DECLARE errno INT;
DECLARE iProviderId INTEGER DEFAULT NULL;
DECLARE tRoutePrivate TINYINT DEFAULT NULL;
DECLARE iAuthenticationId INTEGER 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:authRUser', 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 `R`.`private`
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
INTO tRoutePrivate;

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

IF tRoutePrivate = 1 THEN
  IF p_authType = 'email' THEN
    SELECT `E`.`_id`
    FROM `EmailAuthentication` AS `E`
    INNER JOIN `RemeUsers` AS `U` ON `E`.`userId` = `U`.`_id`
    INNER JOIN `Providers` AS `P` ON `E`.`providerId` = `P`.`_id`
    WHERE `E`.`_id` = p_authId
    AND `E`.`userId` = p_userId
    AND `E`.`providerId` = iProviderId
    AND `E`.`isActive` = 1
    AND `U`.`status` = 'active'
    AND `P`.`emailAuthentication` = 1
    INTO iAuthenticationId;
  ELSEIF p_authType = 'mobile' THEN
    SELECT `M`.`_id`
    FROM `MobileAuthentication` AS `M`
    INNER JOIN `RemeUsers` AS `U` ON `M`.`userId` = `U`.`_id`
    INNER JOIN `Providers` AS `P` ON `M`.`providerId` = `P`.`_id`
    WHERE `M`.`_id` = p_authId
    AND `M`.`userId` = p_userId
    AND `M`.`providerId` = iProviderId
    AND `M`.`isActive` = 1
    AND `U`.`status` = 'active'
    AND `P`.`mobileAuthentication` = 1
    INTO iAuthenticationId;
  ELSEIF p_authType = 'whatsapp' THEN
    SELECT `W`.`_id`
    FROM `WhatsappAuthentication` AS `W`
    INNER JOIN `RemeUsers` AS `U` ON `W`.`userId` = `U`.`_id`
    INNER JOIN `Providers` AS `P` ON `W`.`providerId` = `P`.`_id`
    WHERE `W`.`_id` = p_authId
    AND `W`.`userId` = p_userId
    AND `W`.`providerId` = iProviderId
    AND `W`.`isActive` = 1
    AND `U`.`status` = 'active'
    AND `P`.`whatsappAuthentication` = 1
    INTO iAuthenticationId;
  END IF;

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

  IF p_authType = 'email' THEN
    UPDATE `EmailAuthentication`
    SET `lastAuthenticatedAt` = NOW(),
      `isSignedIn` = 1,
      `updatedAt` = NOW()
    WHERE `_id` = p_authId;
  ELSEIF p_authType = 'mobile' THEN
    UPDATE `MobileAuthentication`
    SET `lastAuthenticatedAt` = NOW(),
      `isSignedIn` = 1,
      `updatedAt` = NOW()
    WHERE `_id` = p_authId;
  ELSEIF p_authType = 'whatsapp' THEN
    UPDATE `WhatsappAuthentication`
    SET `lastAuthenticatedAt` = NOW(),
      `isSignedIn` = 1,
      `updatedAt` = NOW()
    WHERE `_id` = p_authId;
  END IF;
END IF;

SELECT iProviderId AS `providerId`, iAuthenticationId AS 'authenticationId', p_userId AS `userId`, 
  p_authType AS `authenticationType`, tRoutePrivate AS 'privateRoute';

END 
//

COMMIT //

DELIMITER ;
