/* GET NEXT EMAIL FOR QUEUE TO HANDLE */
/* =============================================================================================================== */
DROP PROCEDURE IF EXISTS getNextEmailInQueue;
COMMIT;

DELIMITER //

CREATE PROCEDURE `getNextEmailInQueue`()
BEGIN

DECLARE errno INT;
DECLARE errmsg VARCHAR(128);
DECLARE iEmail INT;

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
	GET CURRENT DIAGNOSTICS CONDITION 1 errno = MYSQL_ERRNO;
	GET CURRENT DIAGNOSTICS CONDITION 1 errmsg = MESSAGE_TEXT;

	ROLLBACK;

  IF errmsg != 'All emails due in the queue has been sent.' THEN 
    INSERT INTO debug_log(activity, ts_when, log) 
    SELECT 'PROC ERR: nextEmail', NOW(), errno; 
  END IF;

	RESIGNAL;
END;

START TRANSACTION;

	SELECT `_id`
	FROM `EmailQueue`
	WHERE TIMESTAMPDIFF(SECOND, `due`, NOW()) >= 0
	AND IF(`expire` IS NULL, 1, TIMESTAMPDIFF(SECOND, `expire`, NOW()) <= 0)
	AND `status` = 'ready'
	ORDER BY `createdAt` DESC
	LIMIT 1
	INTO iEmail;

	IF iEmail IS NULL THEN
		SIGNAL SQLSTATE '45000'
		SET MESSAGE_TEXT = 'All emails due in the queue has been sent.';
	END IF;

	SELECT `q`.`_id` as queueId, `q`.`email`, `q`.`payload`, `t`.`template`, `t`.`subject`, `t`.`sender`, `t`.`short`
	FROM `EmailQueue` AS `q`
	INNER JOIN `EmailTemplates` AS `t` on `q`.`templateId` = `t`.`_id`
	WHERE `q`.`_id` = iEmail;

	UPDATE `EmailQueue`
	SET `status` = 'sending'
	WHERE `_id` = iEmail;

COMMIT WORK;
END
//

COMMIT //

DELIMITER ;

