DROP TRIGGER IF EXISTS `WeeklyScore_ai`;
COMMIT;

DELIMITER //

CREATE TRIGGER `WeeklyScore_ai` AFTER INSERT ON `WeeklyScores` FOR EACH ROW

BEGIN

  DECLARE iUserBuddies INT;
  DECLARE vUserEmail VARCHAR(255);
  DECLARE vUserName VARCHAR(255);
  DECLARE vBuddyOneEmail VARCHAR(255);
  DECLARE vBuddyTwoEmail VARCHAR(255) DEFAULT NULL;
  DECLARE iCurScore INT;
  DECLARE vCurColor VARCHAR(9);
  DECLARE vCurMessage VARCHAR(255);
  DECLARE iPrevScore INT DEFAULT NULL;
  DECLARE vPrevColor VARCHAR(9) DEFAULT NULL;
  DECLARE vPrevMessage VARCHAR(255) DEFAULT NULL;
  DECLARE tPayload TEXT DEFAULT NULL;
  DECLARE iEmailTemplate INT;

  IF new.medicationAdherence IS NOT NULL THEN
    SELECT USER_HAS_BUDDIES(new.userId)
    INTO iUserBuddies;

    IF iUserBuddies IS NOT NULL AND iUserBuddies <> 0 THEN 
      SELECT `email`, IFNULL(`name`, `email`), new.medicationAdherence
      FROM `Users`
      WHERE `_id` = new.userId
      INTO vUserEmail, vUserName, iCurScore;

      SELECT IF(iCurScore > 80, '#1ACA9F', IF(iCurScore >= 60, '#FABB10', '#E3166C')),
        IF(iCurScore > 80, GET_MESSAGE_WORDING('notify_buddy', 3), 
          IF(iCurScore >= 60, GET_MESSAGE_WORDING('notify_buddy', 2), GET_MESSAGE_WORDING('notify_buddy', 1))
        )
      INTO vCurColor, vCurMessage;
      
      SELECT `medicationAdherence` 
      FROM `WeeklyScores`
      WHERE `userId` = new.userId
      AND DATEDIFF(`weekStart`, DATE_SUB(new.weekStart, INTERVAL 7 DAY)) = 0
      INTO iPrevScore;

      IF iPrevScore IS NOT NULL THEN
        SELECT IF(iPrevScore > 80, '#1ACA9F', IF(iPrevScore >= 60, '#FABB10', '#E3166C')),
          IF(iPrevScore > 80, GET_MESSAGE_WORDING('notify_buddy', 3), 
            IF(iPrevScore >= 60, GET_MESSAGE_WORDING('notify_buddy', 2), GET_MESSAGE_WORDING('notify_buddy', 1))
          )
        INTO vPrevColor, vPrevMessage;
      
        SELECT GET_EMAIL_TEMPLATE('notifyBuddy'), CONCAT(
          '{"name":"', vUserName, '","email":"', vUserEmail, '","cur_score":"', iCurScore, '", "cur_color":"', 
          vCurColor, '","cur_message":"', vCurMessage, '","prev_score":"', iPrevScore, 
          '","prev_color":"', vPrevColor, '","prev_message":"', vPrevMessage, '"}'
        )
        INTO iEmailTemplate, tPayload;
      ELSE
        SELECT GET_EMAIL_TEMPLATE('notifyBuddyThisWeek'), CONCAT(
          '{"name":"', vUserName, '","email":"', vUserEmail, '","cur_score":"', iCurScore, '", "cur_color":"', 
          vCurColor, '","cur_message":"', vCurMessage, '"}'
        )
        INTO iEmailTemplate, tPayload;
      END IF;

      SELECT `email`
      FROM `MedBuddies`
      WHERE `userId` = new.userId
      AND `status` = 'active'
      LIMIT 1
      INTO vBuddyOneEmail;

      CALL addEmailToQueue(vBuddyOneEmail, iEmailTemplate, NOW(), DATE_ADD(NOW(), INTERVAL 5 DAY), tPayload);

      SELECT `email`
      FROM `MedBuddies`
      WHERE `userId` = new.userId
      AND `status` = 'active'
      LIMIT 1 
      OFFSET 1
      INTO vBuddyTwoEmail;

      IF vBuddyTwoEmail IS NOT NULL THEN
        CALL addEmailToQueue(vBuddyOneEmail, iEmailTemplate, NOW(), DATE_ADD(NOW(), INTERVAL 5 DAY), tPayload);
      END IF;
    END IF;
  END IF;

END
//

COMMIT //

DELIMITER ;
