/* GET COACH POSTS FROM WP DB */
/* =============================================================================================================== */
DROP PROCEDURE IF EXISTS getCoachPosts;
COMMIT;

DELIMITER //

CREATE PROCEDURE `getCoachPosts`(`p_mode` VARCHAR(10), `p_userId` BIGINT(20), `p_imageDefault` VARCHAR(555))
BEGIN

IF p_mode = 'PROMO' THEN

	SELECT `P`.`_id` AS `Id`, `P`.`title`, `P`.`content`, `P`.`date`, 'post' AS `type`,
		IF(`P`.`image` = NULL OR `P`.`image` = '', p_imageDefault, `P`.`image`) AS `image`,
		`P`.`type` AS `mediaType`, `PC`.`category`
	FROM `Posts` AS `P`
	INNER JOIN `PostToPostCategories` AS `PTPC` ON `P`.`_id` = `PTPC`.`postId`
	INNER JOIN `PostCategories` AS `PC` ON `PTPC`.`categoryId` = `PC`.`_id`
	WHERE `PC`.`slug` = 'promo'
	AND `P`.`_id` NOT IN (
		SELECT `articleId` 
		FROM `ReadPosts` 
		WHERE `userId` = p_userId
	)
	AND `PTPC`.`isActive` = 1
	LIMIT 4;

ELSE

	SELECT DISTINCT `P`.`_id` AS `Id`, `P`.`title`, `P`.`content`, `P`.`date`, 'post' AS `type`,
		IF(`P`.`image` = NULL OR `P`.`image` = '', p_imageDefault, `P`.`image`) AS `image`,
		`P`.`type` AS `mediaType`, `C2`.`category`
	FROM `Posts` `P`
	INNER JOIN `PostToPostCategories` AS `PTPC` ON `P`.`_id` = `PTPC`.`postId`
	INNER JOIN `PostCategories` AS `C` ON `PTPC`.`categoryId` = `C`.`_id`
	INNER JOIN `PostCategories` AS `C2` ON IF(`C`.`parent` = 0, `C`.`_id`, `C`.`parent`) = `C2`.`_id`
	INNER JOIN `PostSubscriptions` AS `PS` ON `PS`.`userId` = p_userId
	WHERE FIND_IN_SET(`C`.`_id`, `PS`.`categories`)
	AND `PTPC`.`isActive` = 1
	AND `P`.`_id` NOT IN (
		SELECT `articleId` 
		FROM `ReadPosts` 
		WHERE `userId` = p_userId
	)
	ORDER BY `P`.`date`
	LIMIT 4;

END IF;

END
//

COMMIT //

DELIMITER ;

