/* SYNC TRACKER PREFERENCES */
/* =============================================================================================================== */
DROP PROCEDURE IF EXISTS updateTrackerPreferences;
COMMIT;

DELIMITER //

CREATE PROCEDURE `updateTrackerPreferences`(`p_userId` VARCHAR(36), `p_trackerIds` VARCHAR(500))

BEGIN

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

  RESIGNAL;
END;

START TRANSACTION;

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

  SELECT `T`.`_id` AS `Id`, `T`.`slug` AS `slug`, `T`.`question` AS `question`, 
    IF(FIND_IN_SET(`T`.`_id`, `P`.`trackerIds`), 1, 0) AS `preference`,
    `O`.`_id` AS `optionId`, `O`.`optionWording` AS `answer`, `O`.`order` AS `ordering`,
    1 AS `synced`
  FROM `Trackers` AS `T`
  INNER JOIN `TrackerOptions` AS `O` ON `T`.`_id` = `O`.`trackerId`
  LEFT JOIN `TrackerPreferences` AS `P` ON `P`.`userId` = p_userId
  WHERE `T`.`isActive` = 1
  AND `O`.`isActive` = 1;

COMMIT WORK;

END
//

COMMIT //

DELIMITER ;
