/* SIGN USER UP */
/* =============================================================================================================== */
DROP PROCEDURE IF EXISTS signUserUp;
COMMIT;

delimiter //

CREATE PROCEDURE `signUserUp`(`p_email` VARCHAR(255), `p_encryptedPassword` VARCHAR(555), `p_salt` VARCHAR(40), 
  `p_token` TEXT)

BEGIN

DECLARE errno INT;
DECLARE vUserStatus VARCHAR(10) DEFAULT NULL;
DECLARE iUserId INT;

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

  RESIGNAL;
END;

START TRANSACTION;

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

  IF vUserStatus IS NOT NULL AND vUserStatus = 'active' THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'There is already an account associated with that email address.';
  ELSEIF vUserStatus IS NOT NULL THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'There is an account associated with that email address. Please contact us if you cannot access it.';
  END IF;

  INSERT INTO `Users`(`name`, `surname`, `email`, `mobileNumber`, `encryptedPassword`, `salt`, `status`, `dateOfBirth`, 
    `gender`, `country`, `termsAccepted`, `pushToken`, `emailVerified`, `createdAt`, `updatedAt`)
  VALUES (NULL, NULL, p_email, NULL, p_encryptedPassword, p_salt, 'active', NULL, NULL, NULL, 1, p_token, GET_MISC_VALUE('email_verification', 'unverified'), NOW(), NOW());

  SELECT `_id` 
  FROM `Users`
  WHERE `email` = p_email
  AND `encryptedPassword` = p_encryptedPassword
  INTO iUserId;

  IF iUserId IS NULL THEN 
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'Failed to sign user up';
  END IF; 

  INSERT INTO `TrackerPreferences`(`userId`, `trackerIds`, `createdAt`, `updatedAt`)
  VALUES (iUserId, '1,2', NOW(), NOW());

  INSERT INTO `PostSubscriptions`(`userId`, `categories`, `createdAt`, `updatedAt`)
  VALUES (iUserId, GET_POST_CATEGORIES(iUserId), NOW(), NOW())
  ON DUPLICATE KEY
  UPDATE `categories` = GET_POST_CATEGORIES(iUserId),
    `updatedAt` = NOW();

  INSERT INTO `QuizSubscriptions`(`userId`, `categories`, `createdAt`, `updatedAt`)
  VALUES (iUserId, GET_QUIZ_CATEGORIES(iUserId), NOW(), NOW())
  ON DUPLICATE KEY
  UPDATE `categories` = GET_QUIZ_CATEGORIES(iUserId),
    `updatedAt` = NOW();

  SELECT * 
  FROM `Users`
  WHERE `_id` = iUserId; 

COMMIT WORK;

END
//

COMMIT //

delimiter ;
