/* DELETE USER AND USER ACCOUNT DATA */
/* =============================================================================================================== */
DROP PROCEDURE IF EXISTS `dangerouslyDeleteUserAccount`;
COMMIT;

DELIMITER //

CREATE PROCEDURE `dangerouslyDeleteUserAccount`(`p_userId` BIGINT, `p_reason` VARCHAR(255))
BEGIN

DECLARE errno INT;
DECLARE iDeleted INT;

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

  ROLLBACK;
  
	INSERT INTO debug_log(activity, ts_when, log) 
  VALUES ('PROC ERR: deleteUser', NOW(), errno); 

  RESIGNAL;
END;

START TRANSACTION;

  INSERT INTO debug_log(activity, ts_when, log) 
  VALUES ('DELETEUSER_START', NOW(), CONCAT('user ', p_userId, ' is being deleted because: ', p_reason)); 

  DELETE FROM `TempSugars` 
  WHERE `userId` = p_userId;
  
  DELETE FROM `Sugars` 
  WHERE `userId` = p_userId;

  DELETE FROM `TempBMIs` 
  WHERE `userId` = p_userId;
  
  DELETE FROM `BMIs` 
  WHERE `userId` = p_userId;

  DELETE FROM `TempBloodPressures` 
  WHERE `userId` = p_userId;
  
  DELETE FROM `BloodPressures` 
  WHERE `userId` = p_userId;

  DELETE FROM `MedicineRisks` 
  WHERE `userId` = p_userId;
  
  DELETE FROM `MedicineRiskToCalculate` 
  WHERE `userId` = p_userId;

  DELETE FROM `TempTrackerAnswers` 
  WHERE `userId` = p_userId;
  
  DELETE FROM `TrackerAnswers` 
  WHERE `userId` = p_userId;
  
  DELETE FROM `TrackerPreferences` 
  WHERE `userId` = p_userId;

  DELETE FROM `QuizAnswers` 
  WHERE `userId` = p_userId;
  
  DELETE FROM `ReadPosts` 
  WHERE `userId` = p_userId;

  DELETE FROM `QuizSubscriptions` 
  WHERE `userId` = p_userId;
  
  DELETE FROM `PostSubscriptions` 
  WHERE `userId` = p_userId;

  DELETE FROM `Dependants` 
  WHERE `userId` = p_userId;
  
  DELETE FROM `MedBuddies` 
  WHERE `userId` = p_userId;

  DELETE FROM `TempReminders` 
  WHERE `userId` = p_userId;
  
  DELETE FROM `Reminders` 
  WHERE `userId` = p_userId;

  DELETE FROM `TempMedications` 
  WHERE `userId` = p_userId;
  
  DELETE FROM `Medications` 
  WHERE `userId` = p_userId;

  DELETE `ES`.* 
  FROM `EmailSubscriptions` AS `ES`
  INNER JOIN `Users` AS `U` ON `ES`.`email` = `U`.`email` 
  WHERE `U`.`_id` = p_userId;
  
  DELETE FROM `WeeklyScores` 
  WHERE `userId` = p_userId;

  DELETE FROM `Users` 
  WHERE `_id` = p_userId;

  SELECT COUNT(*)
  FROM `Users`
  WHERE `_id` = p_userId
  INTO iDeleted;

  IF iDeleted > 0 THEN 
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'Something went wrong while attempting to delete the account. Please try again later or contact us.';
  END IF;

  INSERT INTO debug_log(activity, ts_when, log) 
  VALUES ('DELETEUSER_END', NOW(), CONCAT('user ', p_userId, ' successfully deleted because: ', p_reason)); 

  INSERT INTO `DeletedUsers`(`_id`, `reason`, `createdAt`)
  VALUES (p_userId, p_reason, NOW());

  SELECT *
  FROM `DeletedUsers`
  WHERE `_id` = p_userId;

COMMIT WORK;

END
//

COMMIT //

DELIMITER ;
