/* GET THE SENT AT DATE OF THE LAST SENT EMAIL */
/* =============================================================================================================== */
DROP FUNCTION IF EXISTS GET_LAST_SENT_EMAIL;
COMMIT;

DELIMITER //

CREATE FUNCTION `GET_LAST_SENT_EMAIL`(`p_email` VARCHAR(255), `p_emailTemplateName` VARCHAR(100))
RETURNS DATE
BEGIN

  DECLARE dSentAt DATETIME;
  DECLARE iTemplateId INT DEFAULT NULL;

  SELECT GET_EMAIL_TEMPLATE(p_emailTemplateName)
  INTO iTemplateId;

  IF iTemplateId IS NULL THEN 
    RETURN NULL;
  END IF;

  SELECT MAX(`sentAt`)
  FROM `EmailQueue`
  WHERE `email` = p_email
  AND `templateId` = iTemplateId
  AND `status` = 'sent'
  INTO dSentAt;
	
	RETURN dSentAt;

END 
//
COMMIT //

DELIMITER ;
