/* GET COACH QUIZZES FROM WP DB */
/* =============================================================================================================== */
DROP PROCEDURE IF EXISTS getCoachQuizzes;
COMMIT;

DELIMITER //

CREATE PROCEDURE `getCoachQuizzes`(`p_mode` VARCHAR(10), `p_userId` BIGINT(20))
BEGIN

IF p_mode = 'PROMO' THEN

    SELECT `Q`.`_id` AS `Id`, `Q`.`title`, 'quiz' AS `type`, `QC`.`category`
    FROM `Quizzes` AS `Q` 
    INNER JOIN `QuizCategories` AS `QC` ON `Q`.`categoryId` = `QC`.`_id` 
    WHERE `QC`.`category` LIKE 'Promo'
    AND `Q`.`_id` NOT IN (
        SELECT `quizId` 
        FROM `QuizAnswers` 
        WHERE `userId` = p_userId
    )  
    AND `Q`.`isActive` = 1 
    LIMIT 4;

ELSE

    SELECT DISTINCT `Q`.`_id` AS `Id`, `Q`.`title`, 'quiz' AS `type`, `QC`.`category`
    FROM `Quizzes` AS `Q`
    INNER JOIN `QuizCategories` AS `QC` ON `Q`.`categoryId` = `QC`.`_id`
    INNER JOIN `QuizSubscriptions` AS `QS` ON `QS`.`userId` = p_userId
    WHERE FIND_IN_SET(`Q`.`categoryId`, `QS`.`categories`)
    AND `Q`.`_id` NOT IN (
        SELECT `quizId` 
        FROM `QuizAnswers` 
        WHERE `userId` = p_userId
    ) 
    AND `Q`.`isActive` = 1 
    AND `QC`.`isActive` = 1 
    LIMIT 4;

END IF;

END
//

COMMIT //

DELIMITER ;
