/* SAVE COACH QUIZZES FROM WP DB */
/* =============================================================================================================== */
DROP PROCEDURE IF EXISTS saveCoachQuizzes;
COMMIT;

DELIMITER //

CREATE PROCEDURE `saveCoachQuizzes`(`p_transaction` VARCHAR(36))
BEGIN

DECLARE errno INT;

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
  GET CURRENT DIAGNOSTICS CONDITION 1 errno = MYSQL_ERRNO;
  SELECT errno AS MYSQL_ERROR;

  ROLLBACK;

	INSERT INTO debug_log(activity, ts_when, log) 
  SELECT 'PROC ERR:saveQCatego', NOW(), errno; 

  RESIGNAL;
END;

START TRANSACTION;

  UPDATE `Quizzes`
  SET `isActive` = 0,
    `updatedAt` = NOW()
  WHERE `isActive` <> 0;

  UPDATE `QuizCategories`
  SET `isActive` = 0,
    `updatedAt` = NOW()
  WHERE `isActive` <> 0;

  UPDATE `QuizQuestions`
  SET `isActive` = 0,
    `updatedAt` = NOW()
  WHERE `isActive` <> 0;

  UPDATE `QuizOptions`
  SET `isActive` = 0,
    `updatedAt` = NOW()
  WHERE `isActive` <> 0;

  INSERT INTO `Quizzes`(`_id`, `title`, `categoryId`, `isActive`, `createdAt`, `updatedAt`)
  SELECT `Q`.`_id`, `Q`.`title`, `Q`.`categoryId`, 1, NOW(), NOW()
  FROM `TempQuizzes` AS `Q`
  WHERE `Q`.`transaction` = p_transaction
  ON DUPLICATE KEY
  UPDATE `title` = `Q`.`title`,
    `categoryId` = `Q`.`categoryId`,
    `isActive` = 1, 
    `updatedAt` = NOW();

  INSERT INTO `QuizCategories`(`_id`, `category`, `isActive`, `createdAt`, `updatedAt`)
  SELECT `Q`.`_id`, `Q`.`category`, 1, NOW(), NOW()
  FROM `TempQuizCategories` AS `Q`
  WHERE `Q`.`transaction` = p_transaction
  ON DUPLICATE KEY
  UPDATE `category` = `Q`.`category`,
    `isActive` = 1,
    `updatedAt` = NOW();

  INSERT INTO `QuizQuestions`(`_id`, `quizId`, `question`, `type`, `isActive`, `createdAt`, `updatedAt`)
  SELECT `Q`.`_id`, `Q`.`quizId`, `Q`.`question`, `Q`.`type`, 1, NOW(), NOW()
  FROM `TempQuizQuestions` AS `Q`
  WHERE `Q`.`transaction` = p_transaction
  ON DUPLICATE KEY
  UPDATE `quizId` = `Q`.`quizId`,
    `question` = `Q`.`question`,
    `type` = `Q`.`type`,
    `isActive` = 1,
    `updatedAt` = NOW();

  INSERT INTO `QuizOptions`(`_id`, `questionId`, `optionWording`, `ordering`, `correct`, `isActive`, `createdAt`, 
    `updatedAt`)
  SELECT `Q`.`_id`, `Q`.`questionId`, `Q`.`optionWording`, `Q`.`ordering`, `Q`.`correct`, 1, NOW(), NOW()
  FROM `TempQuizOptions` AS `Q`
  WHERE `Q`.`transaction` = p_transaction
  ON DUPLICATE KEY
  UPDATE `questionId` = `Q`.`questionId`,
    `optionWording` = `Q`.`optionWording`,
    `ordering` = `Q`.`ordering`,
    `correct` = `Q`.`correct`,
    `isActive` = 1,
    `updatedAt` = NOW();

  SELECT COUNT(*) AS `TempQuizzes` 
  FROM `TempQuizzes` 
  WHERE `transaction` = p_transaction; 
  SELECT COUNT(*) AS `Quizzes`
  FROM `Quizzes` 
  WHERE `isActive` = 1; 

  SELECT COUNT(*) AS `TempQuizCategories` 
  FROM `TempQuizCategories` 
  WHERE `transaction` = p_transaction; 
  SELECT COUNT(*) AS `QuizCategories`
  FROM `QuizCategories` 
  WHERE `isActive` = 1; 

  SELECT COUNT(*) AS `TempQuizQuestions`
  FROM `TempQuizQuestions` 
  WHERE `transaction` = p_transaction;
  SELECT COUNT(*) AS `QuizQuestions`
  FROM `QuizQuestions` 
  WHERE `isActive` = 1;

  SELECT COUNT(*) AS `TempQuizOptions`
  FROM `TempQuizOptions` 
  WHERE `transaction` = p_transaction;
  SELECT COUNT(*) AS `QuizOptions`
  FROM `QuizOptions` 
  WHERE `isActive` = 1;

  DELETE FROM `TempQuizzes` 
  WHERE `transaction` = p_transaction;

  DELETE FROM `TempQuizCategories` 
  WHERE `transaction` = p_transaction;

  DELETE FROM `TempQuizQuestions` 
  WHERE `transaction` = p_transaction;

  DELETE FROM `TempQuizOptions` 
  WHERE `transaction` = p_transaction;

COMMIT WORK;

END
//

COMMIT //

DELIMITER ;
