/* SYNC TRACKER ANSWERS AND PREFERENCES */
/* =============================================================================================================== */
DROP PROCEDURE IF EXISTS updateTrackers;
COMMIT;

DELIMITER //

CREATE PROCEDURE `updateTrackers`(`p_transaction` VARCHAR(36), `p_trackerAnswerCount` INT, 
  `p_userId` VARCHAR(36), `p_trackerIds` VARCHAR(500), `p_startOfWeek` DATETIME)

BEGIN

DECLARE errno INT;
DECLARE iTrackerAnswerCount 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: uTrackers', now(), errno; 

  RESIGNAL;
END;

START TRANSACTION;

  IF p_trackerAnswerCount IS NOT NULL AND p_trackerAnswerCount > 0 THEN

    SELECT COUNT(*)
    FROM `TempTrackerAnswers`
    WHERE `transaction` = p_transaction
    INTO iTrackerAnswerCount;

    IF iTrackerAnswerCount <> p_trackerAnswerCount THEN
      SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Temp tracker answer count does not match provided tracker answer count.';
    END IF;

    INSERT INTO `TrackerAnswers`(`userId`, `trackerId`, `trackerQuestion`, `optionId`, `trackerOption`, 
      `timestamp`, `updatedAt`, `createdAt`)
    SELECT `TTA`.`userId`, `TTA`.`trackerId`, `T`.`question`, `TTA`.`optionId`, `O`.`optionWording`, 
      `TTA`.`timestamp`, `TTA`.`updatedAt`, `TTA`.`createdAt`
    FROM `TempTrackerAnswers` AS `TTA`
    LEFT JOIN `Trackers` AS `T` ON `TTA`.`trackerId` = `T`.`_id`
    LEFT JOIN `TrackerOptions` AS `O` ON `TTA`.`optionId` = `O`.`_id`
    WHERE `TTA`.`transaction` = p_transaction
    ON DUPLICATE KEY
    UPDATE `optionId` = `TTA`.`optionId`,
      `trackerOption` = `O`.`optionWording`,
      `updatedAt` = `TTA`.`updatedAt`;

    DELETE FROM `TempTrackerAnswers`
    WHERE `transaction` = p_transaction;

  END IF;

  IF p_trackerIds IS NOT NULL THEN 

    INSERT INTO `TrackerPreferences`(`userId`, `trackerIds`, `createdAt`, `updatedAt`)
    VALUES (p_userId, p_trackerIds, NOW(), NOW())
    ON DUPLICATE KEY 
    UPDATE `trackerIds` = p_trackerIds,
      `updatedAt` = NOW();

  END IF;

  SELECT `T`.`_id` AS `Id`, `T`.`slug` AS `slug`, `T`.`question` AS `question`,
  IF(FIND_IN_SET(`T`.`_id`, `P`.`trackerIds`), 1, 0) as `pref`
  FROM `Trackers` AS `T`
  LEFT JOIN `TrackerPreferences` AS `P` ON `P`.`userId` = p_userId
  WHERE `T`.`isActive` = 1;

  SELECT `_id` AS `Id`, `optionWording` AS `answer`, `trackerId`
  FROM `TrackerOptions`
  WHERE `isActive` = 1;

  SELECT `_id` AS `Id`, `trackerId`, `optionId`, DATE_FORMAT(`timestamp`, '%Y-%m-%dT%H:%i:%s.000Z') AS `timestamp`
  FROM `TrackerAnswers`
  WHERE `userId` = p_userId
  AND `timestamp` > p_startOfWeek;
  

COMMIT WORK;

END
//

COMMIT //

DELIMITER ;
