-- ================================================================================

-- `MySQL NDB Cluster`

-- ================================================================================


-- ----------------------------------------
-- [`ndb_info`]
-- ----------------------------------------

-- 데이터 메모리 사용량 현황
SELECT * FROM `ndbinfo`.`memoryusage`;





-- ================================================================================

-- `eod_configuration`

-- ================================================================================


-- ----------------------------------------
-- [`table_game_server`]
-- ----------------------------------------

-- 게임 서버 현황
SELECT * FROM `eod_configuration`.`table_game_server`;

-- 점령전 서버 현환
SELECT * FROM `eod_configuration`.`table_occupation_war_server`;





-- ================================================================================

--  Miscellaneous

-- ================================================================================

SELECT
	`guild_id`,
	`name`,
	`mark_code`,
	`level`,
	`experience`,
	`state`,
	`need_join_permission`,
	`master_player_id`,
	`change_name_count`,
	`change_name_datetime`,
	`change_mark_count`,
	`change_mark_datetime`,
	`skill_reset_count`,
	`war_count`,
	`raid_count`,
	`generate_datetime`,
	`update_datetime`
FROM 
	`eod_community`.`table_guild` AS guild 
	INNER JOIN
		(SELECT 
			CEIL(RAND() * (SELECT MAX(`guild_id`) FROM `eod_community`.`table_guild`))
		AS guildId)
	AS randomGuild
WHERE (guild.guildId >= randomGuild.guildId)
ORDER BY guild.guildId ASC
LIMIT 1



SELECT 
    `character_id`, 
    `owner_user_id`,
    `character_class_type`, 
    COUNT(`owner_user_id`),
    COUNT(`character_class_type`)
FROM
    `eod_game`.`table_player_character`
GROUP BY `owner_user_id`, `character_id`
HAVING (COUNT(`character_class_type`) > 1);





select `owner_user_id`, `character_class_type`, MIN(`character_id`), count(`character_class_type`)
  from table_player_character
 group by `owner_user_id`, `character_class_type`
having count(`character_class_type`) > 1;

explain drop table `temp_character_id`;
explain create table `temp_character_id`
( `character_id` bigint(20) unsigned not null )


explain insert into `temp_character_id`
select t2.character_id
from 
   (select 
       `owner_user_id`, 
       `character_class_type`, 
       min(`character_id`) as min_character_id, 
       count(`character_class_type`)
    from `table_player_character`
	group by 
       `owner_user_id`, 
       `character_class_type`
	having (count(`character_class_type`) > 1)) AS t1
    inner join table_player_character t2
    on (t2.owner_user_id = t1.owner_user_id) and 
       (t2.character_id != t1.min_character_id);
   

explain delete from table_player_character
where character_id in (select character_id from temp_character_id);



SELECT 
    `joined_guild_id`,
    `joined_guild_name`,
    `guild_membership`,
    COUNT(`guild_membership`) AS cotable_playerunt
FROM `eod_game`.`table_player`
GROUP BY 
    `joined_guild_id`,
    `joined_guild_name`,
    `guild_membership`
HAVING 
    (`guild_membership` = 5) AND
    (COUNT(`guild_membership`) > 1);
    
    
    
SELECT
    `guild_id`,
    `membership`,
    COUNT(`membership`) AS count
FROM `eod_community`.`table_guild_membership`
GROUP BY
    `guild_id`,
    `membership`
HAVING 
    (`membership` = 5) AND
    (COUNT(`membership`) > 1);
    

EXPLAIN SELECT * FROM `eod_game`.`table_player`
WHERE (`user_id` NOT IN (1)) AND (`guardian_level` BETWEEN 100 AND 2000);


-- 대전 상태 탐색
SELECT *
FROM `eod_game`.`table_player_character` AS characterTable
    INNER JOIN
        (SELECT * FROM `eod_game`.`table_player` AS playerTable
         WHERE (`guardian_level` BETWEEN 0 AND 2000)) AS selectedCharacter
        ON (`user_id` != 12)
    INNER JOIN 
        (SELECT CEIL(((SELECT COUNT(`character_id`) 
					   FROM `eod_game`.`table_player_character`) - 1) * RAND()) AS pickedId)
		 AS pickedCharacter
    	ON (`character_id` >= pickedCharacter.pickedId) AND (`owner_user_id` != 12)
WHERE (`level` BETWEEN 1 AND 50)
ORDER BY `character_id` 
LIMIT 1;


SELECT CEIL(((SELECT COUNT(`character_id`) 
			  FROM `eod_game`.`table_player_character`) - 1) * RAND());
              
SELECT * FROM `eod_game`.`table_player` AS playerTable
WHERE (`guardian_level` BETWEEN 0 AND 2000) AND 
      (`user_id` != 12);


SELECT * FROM `eod_authentication`.`table_account_relationship` WHERE (`user_id` = 0);


-- 조력자의 등급 값들을 1씩 올리기(enum 변경 때문에...)
DESCRIBE UPDATE `eod_game`.`table_servant`
SET
    `grade` = `grade` + 1;
    



SELECT * FROM `eod_game`.`table_guardian_stone` WHERE (`enhance_level` > 150);

UPDATE `eod_game`.`table_guardian_stone`
SET
    `enhance_level` = `enhance_level` - (`enhance_level` - 200)
WHERE (`enhance_level` > 200);




SELECT * FROM `eod_game`.`table_player` WHERE (`guardian_level` = 1);
UPDATE `eod_game`.`table_player` 
SET
    `guardian_level` = 1
WHERE (`guardian_level` = 0);



-- --------------------------------------------------------------------------------
-- 신규 캐릭터를 생성하였음에도 불구하고, 스킬 데이터가 하나도 생성이 안 된 캐릭터들의 목록
-- --------------------------------------------------------------------------------

SELECT *
FROM `eod_game`.`table_player_character` AS T1
WHERE (`level` <= 1)
GROUP BY 
    `character_id`
HAVING
    ((SELECT count(1) FROM `eod_game`.`table_skill` WHERE (`character_id` = T1.`character_id`)) <= 0)
LIMIT 1000;


-- --------------------------------------------------------------------------------
--  일반 던전과 정예 던전의 액트 기록 정보 비교
-- --------------------------------------------------------------------------------
SELECT * FROM `eod_game`.`table_stage_normal_act_record` WHERE (`character_id` = 1);
SELECT * FROM `eod_game`.`table_stage_elite_act_record` WHERE (`character_id` = 1);

#UPDATE `eod_game`.`table_stage_normal_act_record` SET `rewarded_clear_grade` = 0 WHERE (`character_id` = 1);
#UPDATE `eod_game`.`table_stage_elite_act_record` SET `rewarded_clear_grade` = 0 WHERE (`character_id` = 1);

SELECT * FROM `eod_game`.`table_stage_normal_act_record` AS table1
INNER JOIN
    (SELECT * FROM `eod_game`.`table_stage_elite_act_record`) AS table2
WHERE 
    (`table1`.`character_id` = `table2`.`character_id`) AND
    (`table1`.`act_number` = `table2`.`act_number`) AND
    (`table1`.`rewarded_clear_grade` != `table2`.`rewarded_clear_grade`)
ORDER BY `table1`.`character_id`;





-- --------------------------------------------------------------------------------
--  균열 던전 초기화한 사용자 목록
-- --------------------------------------------------------------------------------
SELECT
    `user_id`,
	`nickname`,
	selectedCharacterTable.`character_id`,
	`character_class_type`,
	`rift_reset_count`,
    `rift_reset_datetime`
FROM `eod_game`.`table_player`
INNER JOIN
(
	SELECT 
		tableCharacter.`character_id`,
		`owner_user_id`,
		`character_class_type`,
		`rift_reset_count`,
		`rift_reset_datetime`
	FROM `eod_game`.`table_player_character` AS tableCharacter
	INNER JOIN
	(
		SELECT 
			`character_id`, 
			`rift_reset_count`,
			`rift_reset_datetime`
		FROM `eod_game`.`table_stage_record`
	) AS tableStageRecord
	ON (tableCharacter.`character_id` = tableStageRecord.`character_id`) AND
	   (`rift_reset_count` > 0) AND 
   (`rift_reset_datetime` < NOW())
) AS selectedCharacterTable
ON (`user_id` = `selectedCharacterTable`.`owner_user_id`)
ORDER BY `user_id`;



-- --------------------------------------------------------------------------------
--  1.05 패치 전에 결투장 보상 받은 플레이어와 캐릭터 목록
-- --------------------------------------------------------------------------------

SELECT * FROM `temp_arena_async_reward` ORDER BY `user_id` LIMIT 1000;

SELECT 
    `player_table`.`user_id`,
    `player_table`.`nickname`
FROM `temp_arena_async_reward` AS temp_table
INNER JOIN
(
    SELECT * FROM `eod_game`.`table_player`
) AS player_table
ON (`temp_table`.`user_id` = `player_table`.`user_id`);


SELECT 
    `character_table`.`character_id`,
    `character_table`.`character_class_type`
FROM `temp_arena_async_reward` AS temp_table
INNER JOIN
(
    SELECT * FROM `eod_game`.`table_player_character`
) AS character_table
ON (`temp_table`.`character_id` = `character_table`.`character_id`);



-- --------------------------------------------------------------------------------
--  1.26 패치 이후에 아이템 강화로 자원 소모한 내역들
-- --------------------------------------------------------------------------------
-- 골드 소모 내역
SELECT * FROM `eod_log`.`table_gold` 
WHERE (`log_time` >= '2017-08-29 20:00:00') AND (`log_time` <= '2017-08-30 23:59:59') AND
      (`consume_route` = 19)
ORDER BY `user_id`, `character_id`;
      
SELECT * FROM `eod_log`.`table_item` 
WHERE (`log_time` >= '2017-08-29 20:00:00') AND (`log_time` <= '2017-08-30 23:59:59') AND
      (`consume_route` = 19)
ORDER BY `user_id`, `character_id`;

-- --------------------------------------------------------------------------------
--  1.26 패치 이후에 아이템 강화로 자원 소모한 플레이어와 캐릭터 클래스들
-- --------------------------------------------------------------------------------
IF NOT EXIST CREATE TABLE `eod_game`.`temp_enhance_item`
(
    `user_id` BIGINT(20) UNSIGNED NOT NULL DEFAULT '0',
    `character_id` BIGINT(20) UNSIGNED NOT NULL DEFAULT '0',
    `character_level` INT(11) NOT NULL DEFAULT '1',
    `gold` INT(11) NOT NULL DEFAULT '0',
    `essence` INT(11) NOT NULL DEFAULT '0',
    INDEX `idx_temp_enhance_item_user_id_character_id` (`user_id` ASC, `character_id` ASC)
)
ENGINE = InnoDB;



SELECT
    tablePlayer.`user_id`,
    `nickname`,
    selectedCharacterTable.`character_id`,
    `character_class_type`,
    `gold`,
    `essence`
FROM `eod_game`.`table_player` AS tablePlayer
INNER JOIN
(
    SELECT
        `user_id`,
        `temp_enhance_item`.`character_id`,
        `character_class_type`,
        `gold`,
        `essence`
    FROM `eod_game`.`temp_enhance_item`
	INNER JOIN
    (
		SELECT
			`character_id`,
			`owner_user_id`,
			`character_class_type`
		FROM `eod_game`.`table_player_character`
	) AS enhanceItemCharacterTable
	ON (`temp_enhance_item`.`character_id` = enhanceItemCharacterTable.`character_id`)
) AS selectedCharacterTable
ON (tablePlayer.`user_id` = selectedCharacterTable.`user_id`)
GROUP BY selectedCharacterTable.`character_id`
ORDER BY tablePlayer.`user_id`, `character_class_type`;