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

DELIMITER //

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

	DECLARE iCategories VARCHAR(550);
	
	SET iCategories = (
    SELECT GROUP_CONCAT(`C`.`_id`)
    FROM `PostCategories` AS `C`
    WHERE  
    `C`.`slug` IN ('lifestyle', 'mental-wellness')
    OR
    `C`.`_id` IN (
      SELECT `PC`.`_id`
      FROM `Medications` `M` 
      INNER JOIN `MedicinesATC` AS `MA` ON `M`.`medicineId` = `MA`.`TradeID`
      INNER JOIN `MedicineToPostCategories` AS `MPC` ON `MA`.`ATC_Code` LIKE CONCAT(`MPC`.`ATC_Code`, '%')
      INNER JOIN `PostCategories` AS `PC` ON `MPC`.`Category_ID` = `PC`.`_ID` 
      WHERE `M`.`userId` = p_userId
      AND `endDate` > NOW()
    )
  );
	
	RETURN iCategories;

END 
//
COMMIT //

DELIMITER ;
