CREATE TABLE `HOLDEM_BET_HIST` (
  `HOLDEM_BET_IDX` bigint(21) unsigned NOT NULL AUTO_INCREMENT COMMENT 'uniqe idx',
  `REG_DTM` datetime NOT NULL DEFAULT current_timestamp() COMMENT 'reg_dt',
  `MBR_IDX` int(11) NOT NULL DEFAULT 0 COMMENT 'member id',
  `GAME_NUM` int(11) NOT NULL DEFAULT 0 COMMENT 'round num',
  `START_MONEY` int(11) unsigned NOT NULL DEFAULT 0 COMMENT 'start money',
  `BET_MONEY` int(11) unsigned NOT NULL DEFAULT 0 COMMENT 'betting money',
  `FINAL_MONEY` int(11) NOT NULL DEFAULT 0 COMMENT 'final money',
  `WIN_MONEY` int(11) NOT NULL DEFAULT 0 COMMENT 'win money',
  `STACK` int(11) NOT NULL DEFAULT 0,
  `UPDATE_DT` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `EVENT` varchar(45) NOT NULL DEFAULT '',
  `FEE` decimal(7,2) NOT NULL DEFAULT 0.00,
  `CALL_BACK_DATA` text DEFAULT NULL,
  PRIMARY KEY (`HOLDEM_BET_IDX`),
  KEY `REG_DTM_INDEX` (`REG_DTM`),
  KEY `MBR_ID_INDEX` (`MBR_IDX`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci COMMENT='holdem bet table';

CREATE TABLE `HOLDEM_CALL_BACK_DATA` (
  `IDX` bigint(21) unsigned NOT NULL AUTO_INCREMENT COMMENT 'uniqe idx',
  `MBR_IDX` int(11) NOT NULL COMMENT 'member idx',
  `MONEY` int(11) DEFAULT 0,
  `EVENT` varchar(45) NOT NULL DEFAULT '',
  `DATA` text DEFAULT NULL,
  `CREATE_DT` datetime NOT NULL DEFAULT current_timestamp(),
  `FUNCTION` varchar(45) DEFAULT '' COMMENT '호출 함수명',
  PRIMARY KEY (`IDX`),
  KEY `MBR_IDX` (`MBR_IDX`),
  KEY `CREATE_DT` (`CREATE_DT`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci COMMENT='holdem call back data';

ALTER TABLE `charge_event` 
ADD COLUMN `pay_back_value` INT(11) NOT NULL DEFAULT 0 AFTER `max_bonus`;

ALTER TABLE `charge_type` 
ADD COLUMN `bonus_1_charge_first_money` INT(11) NULL DEFAULT 0 AFTER `name`,
ADD COLUMN `bonus_1_charge_first_per` INT(11) NULL DEFAULT 0 AFTER `bonus_1_charge_first_money`,
ADD COLUMN `bonus_1_charge_first_max_money` INT(11) NULL DEFAULT 0 AFTER `bonus_1_charge_first_per`,
ADD COLUMN `bonus_1_charge_money` INT(11) NULL DEFAULT 0 AFTER `bonus_1_charge_first_max_money`,
ADD COLUMN `bonus_1_charge_max_money` INT(11) NULL DEFAULT 0 AFTER `bonus_1_charge_money`,
ADD COLUMN `bonus_1_charge_per` INT(11) NULL DEFAULT 0 AFTER `bonus_1_charge_max_money`,

ADD COLUMN `bonus_2_charge_first_money` INT(11) NULL DEFAULT 0 AFTER `bonus_1_charge_per`,
ADD COLUMN `bonus_2_charge_first_per` INT(11) NULL DEFAULT 0 AFTER `bonus_2_charge_first_money`,
ADD COLUMN `bonus_2_charge_first_max_money` INT(11) NULL DEFAULT 0 AFTER `bonus_2_charge_first_per`,
ADD COLUMN `bonus_2_charge_money` INT(11) NULL DEFAULT 0 AFTER `bonus_2_charge_first_max_money`,
ADD COLUMN `bonus_2_charge_max_money` INT(11) NULL DEFAULT 0 AFTER `bonus_2_charge_money`,
ADD COLUMN `bonus_2_charge_per` INT(11) NULL DEFAULT 0 AFTER `bonus_2_charge_max_money`,

ADD COLUMN `bonus_3_charge_first_money` INT(11) NULL DEFAULT 0 AFTER `bonus_2_charge_per`,
ADD COLUMN `bonus_3_charge_first_per` INT(11) NULL DEFAULT 0 AFTER `bonus_3_charge_first_money`,
ADD COLUMN `bonus_3_charge_first_max_money` INT(11) NULL DEFAULT 0 AFTER `bonus_3_charge_first_per`,
ADD COLUMN `bonus_3_charge_money` INT(11) NULL DEFAULT 0 AFTER `bonus_3_charge_first_max_money`,
ADD COLUMN `bonus_3_charge_max_money` INT(11) NULL DEFAULT 0 AFTER `bonus_3_charge_money`,
ADD COLUMN `bonus_3_charge_per` INT(11) NULL DEFAULT 0 AFTER `bonus_3_charge_max_money`,

ADD COLUMN `bonus_4_charge_first_money` INT(11) NULL DEFAULT 0 AFTER `bonus_3_charge_per`,
ADD COLUMN `bonus_4_charge_first_per` INT(11) NULL DEFAULT 0 AFTER `bonus_4_charge_first_money`,
ADD COLUMN `bonus_4_charge_first_max_money` INT(11) NULL DEFAULT 0 AFTER `bonus_4_charge_first_per`,
ADD COLUMN `bonus_4_charge_money` INT(11) NULL DEFAULT 0 AFTER `bonus_4_charge_first_max_money`,
ADD COLUMN `bonus_4_charge_max_money` INT(11) NULL DEFAULT 0 AFTER `bonus_4_charge_money`,
ADD COLUMN `bonus_4_charge_per` INT(11) NULL DEFAULT 0 AFTER `bonus_4_charge_max_money`,

ADD COLUMN `bonus_5_charge_first_money` INT(11) NULL DEFAULT 0 AFTER `bonus_4_charge_per`,
ADD COLUMN `bonus_5_charge_first_per` INT(11) NULL DEFAULT 0 AFTER `bonus_5_charge_first_money`,
ADD COLUMN `bonus_5_charge_first_max_money` INT(11) NULL DEFAULT 0 AFTER `bonus_5_charge_first_per`,
ADD COLUMN `bonus_5_charge_money` INT(11) NULL DEFAULT 0 AFTER `bonus_5_charge_first_max_money`,
ADD COLUMN `bonus_5_charge_max_money` INT(11) NULL DEFAULT 0 AFTER `bonus_5_charge_money`,
ADD COLUMN `bonus_5_charge_per` INT(11) NULL DEFAULT 0 AFTER `bonus_5_charge_max_money`;

ALTER TABLE `member` 
ADD COLUMN `g_point` INT(11) NULL DEFAULT 0 AFTER `mobile_carrier`,
ADD COLUMN `is_holdem_register` ENUM('Y', 'N') NULL DEFAULT 'N' COMMENT '홀덤 가입 여부' AFTER `info_change_dt`,
ADD COLUMN `is_holdem_start` ENUM('Y', 'N') NULL DEFAULT 'N' COMMENT '홀덤 세션시작 여부' AFTER `is_holdem_register`,
ADD COLUMN `is_holdem_start_dt` DATETIME NULL DEFAULT CURRENT_TIMESTAMP() COMMENT '홀덤 최근 플레이 시간' AFTER `is_holdem_start`;

ALTER TABLE `member_bet_detail` 
CHANGE COLUMN `other_ls_bet_id` `other_ls_bet_id` BIGINT(20) NULL DEFAULT 0 ,
CHANGE COLUMN `other_bet_price` `other_bet_price` DOUBLE NULL DEFAULT 0 ;

ALTER TABLE `member_money_charge_history` 
ADD COLUMN `classic_bet_s_money` INT(11) NULL DEFAULT 0 AFTER `real_bet_d_money`,
ADD COLUMN `classic_bet_d_money` INT(11) NULL DEFAULT 0 AFTER `classic_bet_s_money`,
ADD COLUMN `holdem_bet_money` INT(11) NULL DEFAULT 0 AFTER `hash_bet_money`,
ADD COLUMN `bonus_option_idx` INT(11) NULL DEFAULT 0 COMMENT '선택한 보너스 옵션(-1:적용안함, 0:디폴트 보너스,1: 1번보너스 2:2번 보너스)' AFTER `charge_point_yn`,
ADD COLUMN `ratio_value` INT(11) NULL DEFAULT 0 COMMENT '보너스 비율' AFTER `bonus_option_idx`,
ADD COLUMN `const_value` INT(11) NULL DEFAULT 0 COMMENT '보너스 금액' AFTER `ratio_value`,
ADD COLUMN `manual_bonus_point` INT(11) NULL DEFAULT 0 COMMENT '수동지급 포인트' AFTER `const_value`,
ADD COLUMN `bonus_level` INT(11) NULL DEFAULT 0 AFTER `manual_bonus_point`;

ALTER TABLE `member_money_charge_history` 
ADD INDEX `idx_update_dt` (`update_dt` ASC);

ALTER TABLE `shop_calculate_result` 
ADD COLUMN `total_holdem_bet_money` DECIMAL(11,2) NULL DEFAULT 0.00 AFTER `total_espt_bet_money`,
ADD COLUMN `bet_holdem_fee` DECIMAL(7,2) NULL DEFAULT 0.00 AFTER `bet_hash_fee`,
ADD COLUMN `holdem_bet_take` DECIMAL(11,2) NULL DEFAULT 0.00 AFTER `espt_bet_take`,
ADD COLUMN `pre_classic_bet_sum_s` DECIMAL(11,2) NULL DEFAULT 0.00 AFTER `holdem_bet_take`,
ADD COLUMN `pre_classic_bet_take_s` DECIMAL(11,2) NULL DEFAULT 0.00 AFTER `pre_classic_bet_sum_s`,
ADD COLUMN `pre_classic_bet_sum_d` DECIMAL(11,2) NULL DEFAULT 0.00 AFTER `pre_classic_bet_take_s`,
ADD COLUMN `pre_classic_bet_take_d` DECIMAL(11,2) NULL DEFAULT 0.00 AFTER `pre_classic_bet_sum_d`;

ALTER TABLE `shop_config` 
ADD COLUMN `bet_holdem_fee` DECIMAL(7,2) NULL DEFAULT 0.00 AFTER `bet_hash_fee`;

ALTER TABLE `t_adm_ip` 
ADD COLUMN `type` TINYINT(4) NULL DEFAULT 1 COMMENT '1:ipv4, 2:ipv6' AFTER `idx`;

ALTER TABLE `t_adm_ip` 
CHANGE COLUMN `a_ip` `a_ip` VARCHAR(60) NULL DEFAULT NULL ;

CREATE TABLE `tb_static_bonus` (
  `idx` int(11) NOT NULL DEFAULT 0,
  `desc` varchar(128) DEFAULT '',
  `flag` enum('ON','OFF') DEFAULT 'ON',
  `bonus_1_desc` varchar(128) DEFAULT '',
  `bonus_1_flag` enum('ON','OFF') DEFAULT 'ON',
  `bonus_2_desc` varchar(128) DEFAULT '',
  `bonus_2_flag` enum('ON','OFF') DEFAULT 'ON',
  `create_dt` datetime DEFAULT current_timestamp(),
  `update_dt` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`idx`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci COMMENT='보너스 포인트 관리 테이블 ';

CREATE TABLE `tb_user_pay_back_info` (
  `member_idx` int(11) NOT NULL DEFAULT 0,
  `charge` int(11) DEFAULT 0,
  `exchange` int(11) DEFAULT 0,
  `tot_bet_money` int(11) DEFAULT 0,
  `update_dt` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `create_dt` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`member_idx`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

ALTER TABLE `total_member_cash` 
ADD COLUMN `holdem_total_money` BIGINT(20) NULL DEFAULT 0 AFTER `take_hash_total_money`,
ADD COLUMN `take_holdem_total_money` BIGINT(20) NULL DEFAULT 0 AFTER `holdem_total_money`;

-- procedure
DELIMITER ;;
CREATE PROCEDURE `payAllUserItems`()
BEGIN
	DECLARE ret_code INT;
	DECLARE errno INT;
    DECLARE v_txt_error_msg text;
    
    DECLARE $next TEXT;
	DECLARE $value TEXT;
    
    DECLARE ret_idx INT;
    DECLARE ret_money INT;
    DECLARE ret_point INT;
    DECLARE ret_g_money INT;
    DECLARE ret_id  VARCHAR(45);
       
    DECLARE add_idx BIGINT;
    
    
    DECLARE done INTEGER DEFAULT 0;
    DEClARE openCursor CURSOR FOR SELECT idx,id,money,`point`,g_money FROM member where `status` = 1 and level <> 9 and u_business = 1 AND  reg_time < '2022-10-01 00:00:00';
     
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;
    
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
		GET CURRENT DIAGNOSTICS CONDITION 1 errno = MYSQL_ERRNO
		, v_txt_error_msg = MESSAGE_TEXT;
        set ret_code = -1000;
		SELECT ret_code,errno AS MYSQL_ERROR,v_txt_error_msg as MESSAGE_TEXT;
		ROLLBACK;
    END;
           
    START TRANSACTION;
        OPEN openCursor;
                  read_loop: LOOP
                           FETCH openCursor INTO ret_idx,ret_id,ret_money,ret_point,ret_g_money;
                           
                           IF done THEN
                                   LEAVE read_loop;
                           END IF;
                             
                           insert into `member_item`(member_idx,item_id,item_value,`status`) 
                           values(ret_idx,4,0.05,0);
                           set add_idx = LAST_INSERT_ID();

	                       insert into `t_log_cash`(member_idx, ac_code,ac_idx,r_money, be_r_money, af_r_money, m_kind, coment,point,be_point,af_point,g_money) 
                           values(ret_idx,512,add_idx,0,ret_money,ret_money,'P','운영자 아이템 보상 지급',0,ret_point,ret_point,0);

                  END LOOP read_loop;
         CLOSE openCursor;
	COMMIT;
END ;;
DELIMITER ;

-- data
INSERT INTO `tb_static_bonus` (`idx`, `desc`, `flag`, `bonus_1_desc`, `bonus_1_flag`, `bonus_2_desc`, `bonus_2_flag`, `create_dt`, `update_dt`) VALUES ('1', '※  스포츠, 라이브스포츠 100% (단폴가능), 그 외 게임 200%', 'ON', '※ 스포츠 100% [각각1.4배당이상 2폴더 가능] 라이브스포츠200%[각각1.4배당이상 2폴더 가능] E스포츠 200%, 미니,해쉬,슬롯 300%, 카지노 600%', 'ON', '※  스포츠 100% [각각1.4 배당이상] 3폴더이상 배팅만 인정, 그 외 게임 불인정', 'ON', '2023-01-24 18:21:41', '2023-03-15 22:01:49');