/* SAVE COACH POSTCATEGORIES FROM WP DB */
/* =============================================================================================================== */
DROP PROCEDURE IF EXISTS saveCoachPosts;
COMMIT;

DELIMITER //

CREATE PROCEDURE `saveCoachPosts`(`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:savePCatego', NOW(), errno; 

  RESIGNAL;
END;

START TRANSACTION;

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

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

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

  INSERT INTO `Posts`(`_id`, `date`, `content`, `title`, `image`, `type`, `isActive`, `createdAt`, `updatedAt`)
  SELECT `T`.`_id`, `T`.`date`, `T`.`content`, `T`.`title`, `T`.`image`, `T`.`type`, 1, NOW(), NOW()
  FROM `TempPosts` AS `T`
  WHERE `T`.`transaction` = p_transaction
  ON DUPLICATE KEY
  UPDATE `date` = `T`.`date`,
    `content` = `T`.`content`,
    `title` = `T`.`title`,
    `image` = `T`.`image`,
    `type` = `T`.`type`,
    `isActive` = 1, 
    `updatedAt` = NOW();

  INSERT INTO `PostCategories`(`_id`, `category`, `slug`, `parent`, `isActive`, `createdAt`, `updatedAt`)
  SELECT `T`.`_id`, `T`.`category`, `T`.`slug`, `T`.`parent`, 1, NOW(), NOW()
  FROM `TempPostCategories` AS `T`
  WHERE `T`.`transaction` = p_transaction
  ON DUPLICATE KEY
  UPDATE `category` = `T`.`category`,
    `slug` = `T`.`slug`,
    `parent` = `T`.`parent`,
    `isActive` = 1,
    `updatedAt` = NOW();

  INSERT INTO `PostToPostCategories`(`postId`, `categoryId`, `isActive`, `createdAt`, `updatedAt`)
  SELECT `T`.`postId`, `T`.`categoryId`, 0, NOW(), NOW()
  FROM `TempPostToPostCategories` AS `T`
  WHERE `T`.`transaction` = p_transaction
  ON DUPLICATE KEY
  UPDATE `isActive` = 0,
    `updatedAt` = NOW();

  UPDATE `PostToPostCategories` AS `PTPC`
  INNER JOIN `Posts` AS `P` ON `PTPC`.`postId` = `P`.`_id`
  INNER JOIN `PostCategories` AS `PC` ON `PTPC`.`categoryId` = `PC`.`_id`
  SET `PTPC`.`isActive` = 1,
    `PTPC`.`updatedAt` = NOW()
  WHERE `PTPC`.`isActive` = 0;

  SELECT COUNT(*) AS `TempPosts` 
  FROM `TempPosts` 
  WHERE `transaction` = p_transaction; 
  SELECT COUNT(*) AS `Posts`
  FROM `Posts` 
  WHERE `isActive` = 1; 
  SELECT COUNT(*) AS `TempPostCategories` 
  FROM `TempPostCategories` 
  WHERE `transaction` = p_transaction; 
  SELECT COUNT(*) AS `PostCategories`
  FROM `PostCategories` 
  WHERE `isActive` = 1; 
  SELECT COUNT(*) AS `TempPostToPostCategories`
  FROM `TempPostToPostCategories` 
  WHERE `transaction` = p_transaction;
  SELECT COUNT(*) AS `PostToPostCategories`
  FROM `PostToPostCategories` 
  WHERE `isActive` = 1;

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

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

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

COMMIT WORK;

END
//

COMMIT //

DELIMITER ;
