-- MySQL Workbench Forward Engineering


-- -----------------------------------------------------
-- Schema eod_authentication
-- -----------------------------------------------------
-- 인증 데이터베이스
-- -----------------------------------------------------
-- Schema eod_game
-- -----------------------------------------------------
-- 게임 데이터베이스

-- -----------------------------------------------------
-- Schema eod_game
--
-- 게임 데이터베이스
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `eod_game` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
-- -----------------------------------------------------
-- Schema eod_log
-- -----------------------------------------------------
-- 로그 데이터베이스
-- -----------------------------------------------------
-- Schema eod_ranking
-- -----------------------------------------------------
-- 랭킹 데이터베이스
-- -----------------------------------------------------
-- Schema eod_community
-- -----------------------------------------------------
-- 커뮤니티 데이터베이스
-- -----------------------------------------------------
-- Schema eod_tool
-- -----------------------------------------------------
-- 서비스 관리도구용 데이터베이스
-- -----------------------------------------------------
-- Schema eod_configuration
-- -----------------------------------------------------
-- 서비스 설정 데이터베이스
-- -----------------------------------------------------
-- Schema eod_global
-- -----------------------------------------------------
-- 전역 서비스 관리 데이터베이스
USE `eod_game` ;
USE `eod_game` ;

-- -----------------------------------------------------
-- procedure usp_a_delete_equipment
-- -----------------------------------------------------

DELIMITER $$
USE `eod_game`$$
CREATE PROCEDURE `usp_a_delete_equipment`(IN delete_count INT)
BEGIN
	
	DECLARE _row_count INT;
	DECLARE _start_index BIGINT UNSIGNED;
	
	SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
	
	CREATE TABLE IF NOT EXISTS `eod_game`.`delete_equipment_temp`
	(
		`item_id` BIGINT(20) UNSIGNED,
		PRIMARY KEY (`item_id`)
	);
	
	TRUNCATE TABLE `eod_game`.`delete_equipment_temp`;
	
	INSERT INTO `eod_game`.`delete_equipment_temp`
	(`item_id`)
	SELECT `item_id`
	FROM `eod_game`.`table_equipment_item`
	WHERE (`item_id` > 0) AND (`state` != 0)
      AND (`update_datetime` <= DATE_ADD(NOW(), INTERVAL -3 DAY));
	
	repeat_loop : REPEAT
		
		SET _start_index = (SELECT MIN(`item_id`) FROM `eod_game`.`delete_equipment_temp`);
		
		IF (_start_index IS NULL) THEN
			LEAVE repeat_loop;
		END IF;
	
		DELETE t1 FROM `eod_game`.`table_equipment_item` AS t1
		INNER JOIN 
			(
				SELECT `item_id`
				FROM `eod_game`.`delete_equipment_temp`
				WHERE `item_id` BETWEEN _start_index AND _start_index + delete_count
			) AS t2
			ON t1.`item_id` = t2.`item_id`;
	
		SET _row_count = (SELECT ROW_COUNT());
		
		DELETE FROM `eod_game`.`delete_equipment_temp` 
		WHERE `item_id` BETWEEN _start_index AND _start_index + delete_count;
		
	UNTIL _row_count = 0
	END REPEAT;
	
END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure usp_a_delete_post
-- -----------------------------------------------------

DELIMITER $$
USE `eod_game`$$
CREATE PROCEDURE `usp_a_delete_post`(IN delete_count INT)
BEGIN
	DECLARE _row_count INT;
	DECLARE _start_index BIGINT UNSIGNED;
	
	SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
	
	CREATE TABLE IF NOT EXISTS `eod_game`.`delete_post_temp`
	(
		`no` BIGINT(20) UNSIGNED,
		PRIMARY KEY (`no`)
	);
	
	TRUNCATE TABLE `eod_game`.`delete_post_temp`;

	INSERT INTO `eod_game`.`delete_post_temp`
	(`no`)
	SELECT `no`
	FROM `eod_game`.`table_post`
	WHERE `no` > 0 AND `state` != 0;
	
	repeat_loop : REPEAT
		
		SET _start_index = (SELECT MIN(`no`) FROM `eod_game`.`delete_post_temp`);

		IF (_start_index IS NULL) THEN
			LEAVE repeat_loop;
		END IF;
	
		DELETE t1 FROM `eod_game`.`table_post` AS t1
		INNER JOIN
			(
				SELECT `no`
				FROM `eod_game`.`delete_post_temp`
				WHERE `no` BETWEEN _start_index AND _start_index + delete_count
			) AS t2
			ON t1.`no` = t2.`no`;
	
		SET _row_count = (SELECT ROW_COUNT());
		
		DELETE FROM `eod_game`.`delete_post_temp` 
		WHERE `no` BETWEEN _start_index AND _start_index + delete_count;
		
	UNTIL _row_count = 0
	END REPEAT;
    
END$$

DELIMITER ;

