/* SYNC TRACKER ANSWERS */
/* =============================================================================================================== */
DROP PROCEDURE IF EXISTS updateTrackerAnswers;
COMMIT;

DELIMITER //

CREATE PROCEDURE `updateTrackerAnswers`(`p_transaction` VARCHAR(36), `p_trackerAnswerCount` INT, `p_userId` VARCHAR(36))

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: uTrAnswer', 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;

  SELECT `_id`, `optionId`, `trackerId`, DATE_FORMAT(`timestamp`, '%Y-%m-%dT%H:%i:%s.000Z') AS `timestamp`, 
    1 AS `synced`
  FROM `TrackerAnswers`
  WHERE `userId` = p_userId
  AND `timestamp` > DATE_SUB(CURDATE(), INTERVAL 10 DAY);
  
COMMIT WORK;

END
//

COMMIT //

DELIMITER ;
