/* GET QUIZ CATEGORIES LINKED TO USER VIA MEDICATIONS */
/* =============================================================================================================== */
DROP FUNCTION IF EXISTS GET_QUIZ_CATEGORIES;
COMMIT;

DELIMITER //

CREATE FUNCTION `GET_QUIZ_CATEGORIES`(`p_userId` varchar(36))
RETURNS VARCHAR(550)
BEGIN

	DECLARE iCategories VARCHAR(550);
	
	SET iCategories = (
    SELECT GROUP_CONCAT(`C`.`_id`)
    FROM `QuizCategories` AS `C`
    WHERE  
    `C`.`category` IN ('Lifestyle', 'Mental wellness')
    OR
    `C`.`_id` IN (
      SELECT `QC`.`_id`
      FROM `Medications` AS `M` 
      INNER JOIN `MedicinesATC` AS `MA` ON `M`.`medicineId` = `MA`.`TradeID`
      INNER JOIN `MedicineToQuizCategories` as `MQC` ON `MA`.`ATC_Code` LIKE CONCAT(`MQC`.`ATC_Code`, '%')
      INNER JOIN `QuizCategories` AS `QC` ON `MQC`.`Category_ID` = `QC`.`_ID` 
      WHERE `M`.`userId` = p_userId 
      AND `endDate` > NOW()
    )
  );
	
	RETURN iCategories;

END 
//
COMMIT //

DELIMITER ;
