-- MySQL Workbench Forward Engineering


-- -----------------------------------------------------
-- Schema eod_authentication
-- -----------------------------------------------------
-- 인증 데이터베이스
-- -----------------------------------------------------
-- Schema eod_game
-- -----------------------------------------------------
-- 게임 데이터베이스
-- -----------------------------------------------------
-- Schema eod_log
-- -----------------------------------------------------
-- 로그 데이터베이스
-- -----------------------------------------------------
-- Schema eod_ranking
-- -----------------------------------------------------
-- 랭킹 데이터베이스
-- -----------------------------------------------------
-- Schema eod_community
-- -----------------------------------------------------
-- 커뮤니티 데이터베이스
-- -----------------------------------------------------
-- Schema eod_tool
-- -----------------------------------------------------
-- 서비스 관리도구용 데이터베이스
-- -----------------------------------------------------
-- Schema eod_configuration
-- -----------------------------------------------------
-- 서비스 설정 데이터베이스
-- -----------------------------------------------------
-- Schema eod_global
-- -----------------------------------------------------
-- 전역 서비스 관리 데이터베이스

-- -----------------------------------------------------
-- Schema eod_global
--
-- 전역 서비스 관리 데이터베이스
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `eod_global` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
USE `eod_global` ;
USE `eod_global` ;

-- -----------------------------------------------------
-- procedure sp_register_player_nickname
-- -----------------------------------------------------

DELIMITER $$
USE `eod_global`$$
CREATE PROCEDURE `sp_register_player_nickname`(IN inAppName VARCHAR(64),
                                               IN inCategory INT,
                                               IN inInputName VARCHAR(64),
                                               IN inRegisterKey VARCHAR(256),
                                               IN inTimeSeconds BIGINT,
                                               IN inIsFixed TINYINT)
BEGIN
    -- 성공(Eod.Result.Common.Success)
    DECLARE ResultSuccess INT DEFAULT 0;

    -- 실패(Eod.Result.Common.Failed)
    DECLARE ResultFailed INT DEFAULT -1;

    -- 해당 이름이 이미 존재함.(Eod.Result.Common.SameNameExistAlready)
    DECLARE ResultRewardAlready INT DEFAULT 1106;


    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        SELECT ResultRewardAlready;
        ROLLBACK;
    END;


    SET AutoCommit = 0;

    START TRANSACTION;

        SET @LimitTimeSeconds = 0;

        SELECT `time_seconds`, `fixed`
        INTO @LimitTimeSeconds, @IsFixed
        FROM `eod_global`.`table_player_nicknames`
        WHERE (`application_identifier` = inAppName) AND (`name` = inInputName);

        IF (@LimitTimeSeconds <> 0) THEN
            IF (@LimitTimeSeconds >= inTimeSeconds) OR (@IsFixed = 1) THEN
                SELECT ResultRewardAlready;
            ELSE
                UPDATE `eod_global`.`table_player_nicknames`
                SET
                    `application_identifier` = `inAppName`,
                    `category_code` = inCategory,
                    `name` = inInputName,
                    `name_description` = inInputName,
                    `time_seconds` = inTimeSeconds + 1,
                    `fixed` = inIsFixed,
                    `register_key` = inRegisterKey,
                    `generate_datetime` = NOW()
                WHERE (`application_identifier` = inAppName) AND (`name` = inInputName);
                SELECT ResultSuccess;
            END IF;
        ELSE
            INSERT INTO `eod_global`.`table_player_nicknames`
                (`application_identifier`,
                 `category_code`,
                 `name`,
                 `name_description`,
                 `register_key`,
                 `time_seconds`,
                 `fixed`)
            VALUES
                (inAppName,
                 inCategory,
                 inInputName,
                 inInputName,
                 inRegisterKey,
                 (inTimeSeconds + 1),
                 inIsFixed);
            SELECT ResultSuccess;
        END IF;

    COMMIT;

    SET AutoCommit = 1;
END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure sp_register_guild_name
-- -----------------------------------------------------

DELIMITER $$
USE `eod_global`$$
CREATE PROCEDURE `sp_register_guild_name`(IN inAppName VARCHAR(64),
                                          IN inCategory INT,
                                          IN inInputName VARCHAR(64),
                                          IN inRegisterKey VARCHAR(256),
                                          IN inTimeSeconds BIGINT,
                                          IN inIsFixed TINYINT)
BEGIN
    -- 성공(Eod.Result.Common.Success)
    DECLARE ResultSuccess INT DEFAULT 0;

    -- 실패(Eod.Result.Common.Failed)
    DECLARE ResultFailed INT DEFAULT -1;

    -- 해당 이름이 이미 존재함.(Eod.Result.Common.SameNameExistAlready)
    DECLARE ResultRewardAlready INT DEFAULT 1106;


    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        SELECT ResultRewardAlready;
        ROLLBACK;
    END;


    SET AutoCommit = 0;

    START TRANSACTION;

        SET @LimitTimeSeconds = 0;

        SELECT `time_seconds`, `fixed`
        INTO @LimitTimeSeconds, @IsFixed
        FROM `eod_global`.`table_guild_names`
        WHERE (`application_identifier` = inAppName) AND (`name` = inInputName);

        IF (@LimitTimeSeconds <> 0) THEN
            IF (@LimitTimeSeconds >= inTimeSeconds) OR (@IsFixed = 1) THEN
                SELECT ResultRewardAlready;
            ELSE
                UPDATE `eod_global`.`table_guild_names`
                SET
                    `application_identifier` = inAppName,
                    `category_code` = inCategory,
                    `name` = inInputName,
                    `name_description` = inInputName,
                    `time_seconds` = inTimeSeconds - 1,
                    `fixed` = inIsFixed,
                    `register_key` = inRegisterKey,
                    `generate_datetime` = NOW()
                WHERE (`application_identifier` = inAppName) AND (`name` = inInputName);
                SELECT ResultSuccess;
            END IF;
        ELSE
            INSERT INTO `eod_global`.`table_guild_names`
                (`application_identifier`,
                 `category_code`,
                 `name`,
                 `name_description`,
                 `register_key`,
                 `time_seconds`,
                 `fixed`)
            VALUES
                (inAppName,
                 inCategory,
                 inInputName,
                 inInputName,
                 inRegisterKey,
                 (inTimeSeconds - 1),
                 inIsFixed);
            SELECT ResultSuccess;
        END IF;

    COMMIT;

    SET AutoCommit = 1;
END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure sp_promotion_reward
-- -----------------------------------------------------

DELIMITER $$
USE `eod_global`$$
CREATE PROCEDURE `sp_promotion_reward`(IN inUserId BIGINT(20),
                                       IN inServerCode INT(11),
                                       IN inUniqueKey VARCHAR(256),
                                       IN inRewardInfo VARCHAR(256),
                                       IN inSignValue VARCHAR(256),
                                       IN inCharacterId BIGINT(20),
                                       IN inEventApplication VARCHAR(256))
BEGIN
    -- 성공(Eod.Result.Common.Success)
    DECLARE ResultSuccess INT DEFAULT 0;

    -- 실패(Eod.Result.Common.Failed)
    DECLARE ResultFailed INT DEFAULT -1;

    -- 보상을 이미 수령함.(Eod.Result.Common.RewardAlready)
    DECLARE ResultRewardAlready INT DEFAULT 3102;


    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        SELECT ResultRewardAlready;
        ROLLBACK;
    END;


    SET AutoCommit = 0;

    START TRANSACTION;

        IF(SELECT `user_id`
           FROM `eod_global`.`table_promotion_data`
           WHERE (`user_id` = inUserId) AND
                 (`unique_key` = inUniqueKey) AND
                 (`event_application` = inEventApplication)) THEN
            -- 보상을 이미 수령함.(Eod.Result.Common.RewardAlready)
            SELECT ResultRewardAlready;
        ELSE
            INSERT INTO `eod_global`.`table_promotion_data`
                (`user_id`,
                 `server_code`,
                 `unique_key`,
                 `character_id`,
                 `event_application`,
                 `reward_info`,
                 `content_data`)
            VALUES
                (inUserId,
                 inServerCode,
                 inUniqueKey,
                 inCharacterId,
                 inEventApplication,
                 inRewardInfo,
                 inSignValue);
            SELECT ResultSuccess;
        END IF;

    COMMIT;

    SET AutoCommit = 1;
END$$

DELIMITER ;

-- -----------------------------------------------------
-- procedure sp_register_account_user_serial_code
-- -----------------------------------------------------

DELIMITER $$
USE `eod_global`$$
CREATE PROCEDURE `sp_register_account_user_serial_code`(IN inSerialCode VARCHAR(24),
                                                        IN inGenerateDateTime DATETIME)
BEGIN
    -- 성공(Eod.Result.Common.Success)
    DECLARE ResultSuccess INT DEFAULT 0;

    -- 실패(Eod.Result.Common.Failed)
    DECLARE ResultFailed INT DEFAULT -1;

    -- 해당 계정이 이미 존재함.(Eod.Result.Common.SerialCodeExistAlready)
    DECLARE ResultSerialCodeExistAlready INT DEFAULT 1052;


    -- 개수를 측정하는 변수
    DECLARE existanceCount INT DEFAULT 0;


    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        SELECT ResultSerialCodeExistAlready;
        ROLLBACK;
    END;


    START TRANSACTION;

        SELECT COUNT(1)
        INTO existanceCount
        FROM `eod_global`.`table_account_user_serial_codes`
        WHERE (`serial_code` = inSerialCode);

        IF (existanceCount <= 0) THEN
            INSERT INTO `eod_global`.`table_account_user_serial_codes`
            VALUES (inSerialCode, inGenerateDateTime);
            SELECT ResultSuccess;
        ELSE
            SELECT ResultSerialCodeExistAlready;
        END IF;

    COMMIT;

    SET AutoCommit = 1;
END$$

DELIMITER ;

