-- new table
CREATE TABLE `member_item` (
  `idx` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'idx',
  `member_idx` int(11) DEFAULT 0 COMMENT '회원 idx',
  `item_id` int(11) DEFAULT 0 COMMENT '구매 아이템 id',
  `item_value` decimal(6,2) DEFAULT 0.00 COMMENT '구매 당시 value, 0일 때 적특패치',
  `status` tinyint(2) DEFAULT 0 COMMENT '0 : 미사용 1 : 사용 ,2: 기간마감 ',
  `create_dt` datetime DEFAULT current_timestamp() COMMENT '등록날짜',
  `update_dt` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT '수정날짜',
  PRIMARY KEY (`idx`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='유저 보유 아이템';

CREATE TABLE `popups` (
  `idx` int(11) NOT NULL AUTO_INCREMENT,
  `thumbnail` varchar(1024) DEFAULT '',
  `status` tinyint(4) DEFAULT 1 COMMENT '0-미사용, 1-사용',
  `rank` tinyint(1) DEFAULT NULL COMMENT '팝업순번',
  `create_dt` datetime DEFAULT current_timestamp(),
  `update_dt` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `delete_dt` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`idx`),
  KEY `thumbnail` (`thumbnail`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8 COMMENT='팝업 테이블';

CREATE TABLE `banners` (
  `idx` int(11) NOT NULL AUTO_INCREMENT,
  `thumbnail` varchar(1024) DEFAULT '',
  `display_type` tinyint(1) DEFAULT NULL COMMENT '표시 타입 (1: PC, 2: 모바일)',
  `status` tinyint(4) DEFAULT 1 COMMENT '0-미사용, 1-사용',
  `rank` tinyint(1) DEFAULT NULL COMMENT '배너순번',
  `create_dt` datetime DEFAULT current_timestamp(),
  `update_dt` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `delete_dt` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`idx`),
  KEY `thumbnail` (`thumbnail`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=82 DEFAULT CHARSET=utf8 COMMENT='배너 테이블';

INSERT INTO `dist_type` (`id`, `name`, `create_dt`, `update_dt`) VALUES ('0', '기본', '2022-10-01 18:46:10', '2022-10-01 18:46:10');
INSERT INTO `dist_type` (`id`, `name`, `create_dt`, `update_dt`) VALUES ('1', '죽장', '2022-10-01 18:46:10', '2022-10-01 18:46:10');
INSERT INTO `dist_type` (`id`, `name`, `create_dt`, `update_dt`) VALUES ('2', '롤링', '2022-10-01 18:46:10', '2022-10-01 18:46:10');
INSERT INTO `dist_type` (`id`, `name`, `create_dt`, `update_dt`) VALUES ('3', '배너', '2022-10-01 18:46:11', '2022-10-01 18:46:11');


CREATE TABLE `dist_type` (
  `id` int(11) NOT NULL DEFAULT 0,
  `name` varchar(45) DEFAULT '',
  `create_dt` datetime DEFAULT current_timestamp(),
  `update_dt` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Distributor type definition table';

CREATE TABLE `item` (
  `id` int(11) NOT NULL DEFAULT 0 COMMENT '고유 아이디',
  `name` varchar(45) DEFAULT NULL COMMENT '아이템 이름',
  `desc` varchar(256) DEFAULT NULL COMMENT '상세설명',
  `type` tinyint(2) DEFAULT 0 COMMENT '1: 환급, 2: 배당up,3:적특 ',
  `value` decimal(6,2) DEFAULT NULL COMMENT '0일 때 적특패치',
  `is_open` varchar(2) DEFAULT 'Y' COMMENT '프론트에 노출여부  Y => 노출 , N => 미노출 ',
  `buy_type` tinyint(2) DEFAULT 0 COMMENT '0 : g_point , 1 : 포인트 2 : 머니,3 : 무료',
  `price` int(11) DEFAULT 0,
  `create_dt` datetime DEFAULT current_timestamp(),
  `update_dt` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='아이템 관리 테이블';

INSERT INTO `item` (`id`, `name`, `desc`, `type`, `value`, `is_open`, `buy_type`, `price`, `create_dt`, `update_dt`) VALUES ('1', '20% 환급패치', '미적중시 베팅금액의 20% 를 돌려드립니다.', '1', '0.20', 'Y', '0', '40000', '2022-05-13 14:51:11', '2022-05-13 18:02:00');
INSERT INTO `item` (`id`, `name`, `desc`, `type`, `value`, `is_open`, `buy_type`, `price`, `create_dt`, `update_dt`) VALUES ('2', '35% 환급패치', '미적중시 베팅금액의 35% 를 돌려드립니다.', '1', '0.35', 'Y', '0', '70000', '2022-05-02 15:51:25', '2022-05-13 18:02:16');
INSERT INTO `item` (`id`, `name`, `desc`, `type`, `value`, `is_open`, `buy_type`, `price`, `create_dt`, `update_dt`) VALUES ('3', '50% 환급패치', '미적중시 베팅금액의 50% 를 돌려드립니다.', '1', '0.50', 'Y', '0', '100000', '2022-05-02 15:51:25', '2022-05-13 18:02:17');
INSERT INTO `item` (`id`, `name`, `desc`, `type`, `value`, `is_open`, `buy_type`, `price`, `create_dt`, `update_dt`) VALUES ('4', '5% 배당up 패치', '해당 배당률이 5% 증가합니다', '2', '0.05', 'Y', '0', '10000', '2022-05-13 15:57:09', '2022-05-13 18:02:17');
INSERT INTO `item` (`id`, `name`, `desc`, `type`, `value`, `is_open`, `buy_type`, `price`, `create_dt`, `update_dt`) VALUES ('5', '10% 배당up 패치', '해당 배당률이 10% 증가합니다', '2', '0.10', 'Y', '0', '20000', '2022-05-13 15:57:09', '2022-05-18 20:05:03');
INSERT INTO `item` (`id`, `name`, `desc`, `type`, `value`, `is_open`, `buy_type`, `price`, `create_dt`, `update_dt`) VALUES ('6', '15% 배당up 패치', '해당 배당률이 15% 증가합니다', '2', '0.15', 'Y', '0', '30000', '2022-05-13 15:57:09', '2022-05-18 20:05:03');
INSERT INTO `item` (`id`, `name`, `desc`, `type`, `value`, `is_open`, `buy_type`, `price`, `create_dt`, `update_dt`) VALUES ('7', '적특패치', '원하는 경기를 적중특례 시킵니다.', '3', '0.00', 'Y', '0', '50000', '2022-05-13 16:02:12', '2022-05-13 17:59:18');


ALTER TABLE `t_domain_code` 
ADD COLUMN `type` TINYINT(4) NULL DEFAULT 1 AFTER `reg_time`;

ALTER TABLE `member` 
ADD COLUMN `membercol` VARCHAR(45) NULL FIRST,
ADD COLUMN `g_money` INT(11) NULL DEFAULT 0 AFTER `mobile_carrier`,
ADD COLUMN `is_set_day_nt_gm` ENUM('Y', 'N') NULL DEFAULT 'N' AFTER `g_money`,
ADD COLUMN `a_ip` VARCHAR(30) NULL DEFAULT NULL AFTER `is_set_day_nt_gm`,
ADD COLUMN `a_before_ip` VARCHAR(30) NULL DEFAULT NULL AFTER `a_ip`,
ADD COLUMN `keep_login_access_token` VARCHAR(50) NULL AFTER `a_before_ip`,
ADD COLUMN `dist_type` VARCHAR(45) NULL DEFAULT 0 AFTER `is_holdem_start_dt`;

ALTER TABLE `mydb`.`member_bet` 
ADD COLUMN `is_betting_slip` enum('ON','OFF') NULL DEFAULT 'OFF' AFTER `passivity_hit_flag`,
ADD COLUMN `item_idx` BIGINT(20) NULL DEFAULT 0 AFTER `is_betting_slip`,
ADD COLUMN `flag_bet_sum` VARCHAR(2) NULL DEFAULT 'P' AFTER `item_idx`,
ADD COLUMN `item_bonus_price` DOUBLE NULL DEFAULT 0 AFTER `flag_bet_sum`,
ADD COLUMN `bet_count` TINYINT(4) NULL DEFAULT 0 AFTER `item_bonus_price`,
ADD COLUMN `is_classic` ENUM('ON', 'OFF') NULL DEFAULT 'OFF' AFTER `bet_count`;

-- member_money_charge_history charge_point_yn

ALTER TABLE `mydb`.`shop_calculate_result` 
ADD COLUMN `low_calculate_point` DECIMAL(11,2) NULL DEFAULT 0.00 AFTER `calculate_point`,
ADD COLUMN `pre_bet_sum_d_3` DECIMAL(11,2) NULL DEFAULT 0.00 AFTER `pre_bet_sum_d_2`,
ADD COLUMN `pre_bet_sum_d_4` DECIMAL(11,2) NULL DEFAULT 0.00 AFTER `pre_bet_sum_d_3`,
ADD COLUMN `pre_bet_sum_d_5_more` DECIMAL(11,2) NULL DEFAULT 0.00 AFTER `pre_bet_sum_d_4`,
-- ADD COLUMN `bet_pre_d_2_fee` VARCHAR(45) NULL AFTER `pre_bet_sum_d_5_more`,
ADD COLUMN `bet_pre_d_3_fee` DECIMAL(7,2) NULL DEFAULT 0.00 AFTER `bet_pre_d_2_fee`,
ADD COLUMN `bet_pre_d_4_fee` DECIMAL(7,2) NULL DEFAULT 0.00 AFTER `bet_pre_d_3_fee`,
ADD COLUMN `bet_pre_d_5_more_fee` DECIMAL(7,2) NULL DEFAULT 0.00 AFTER `bet_pre_d_4_fee`,
ADD COLUMN `pre_bet_take_d_3` DECIMAL(11,2) NULL DEFAULT 0.00 AFTER `pre_bet_take_2_d`,
ADD COLUMN `pre_bet_take_d_4` DECIMAL(11,2) NULL DEFAULT 0.00 AFTER `pre_bet_take_d_3`,
ADD COLUMN `pre_bet_take_d_5_more` DECIMAL(11,2) NULL DEFAULT 0.00 AFTER `pre_bet_take_d_4`,
ADD COLUMN `pre_classic_bet_take_d` DECIMAL(11,2) NULL DEFAULT 0.00 AFTER `holdem_bet_take`,
ADD COLUMN `pre_classic_bet_sum_s` DECIMAL(11,2) NULL DEFAULT 0.00 AFTER `pre_classic_bet_take_d`,
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`,
CHANGE COLUMN `pre_bet_sum_d` `pre_bet_sum_d_2` DECIMAL(11,2) NULL DEFAULT 0.00 COMMENT '프리멀티 정산금' ,
CHANGE COLUMN `bet_pre_d_fee` `bet_pre_d_2_fee` DECIMAL(7,2) NULL DEFAULT 0.00 COMMENT '베팅롤링 프리매치다폴더 수수료' ,
CHANGE COLUMN `pre_bet_take_d` `pre_bet_take_2_d` DECIMAL(11,2) NULL DEFAULT 0.00 ;


ALTER TABLE `mydb`.`shop_config` 
ADD COLUMN `bet_pre_d_3_fee` DECIMAL(7,2) NULL DEFAULT 0.00 AFTER `bet_pre_d_2_fee`,
ADD COLUMN `bet_pre_d_4_fee` DECIMAL(7,2) NULL DEFAULT 0.00 AFTER `bet_pre_d_3_fee`,
ADD COLUMN `bet_pre_d_5_more_fee` DECIMAL(7,2) NULL DEFAULT 0.00 AFTER `bet_pre_d_4_fee`,
CHANGE COLUMN `bet_pre_d_fee` `bet_pre_d_2_fee` DECIMAL(7,2) NULL DEFAULT 0.00 COMMENT '베팅롤링 프리매치다폴더' ;

ALTER TABLE `t_log_cash` 
ADD COLUMN `a_country` VARCHAR(45) NULL DEFAULT NULL AFTER `u_ip`,
ADD COLUMN `g_money` INT(11) NULL DEFAULT 0 AFTER `holdem_user_money`;




USE `mydb`;
DROP function IF EXISTS `FN_ADM_MSG_SEND_ALL`;

DELIMITER $$
USE `mydb`$$
CREATE FUNCTION `FN_ADM_MSG_SEND_ALL`(`$p_msg_idx` INT
) RETURNS int(11)
BEGIN
	DECLARE $ret 			VARCHAR(50);
	DECLARE $no_more_rows 		BOOL DEFAULT FALSE;

	DECLARE $db_user_idx			INTEGER DEFAULT 0;

	DECLARE cursor_1 CURSOR FOR
		SELECT idx FROM mydb.member WHERE status=1;
		
	DECLARE CONTINUE HANDLER FOR NOT FOUND
		SET $no_more_rows := true;

	OPEN cursor_1;
	LOOP1: loop
		FETCH cursor_1 INTO $db_user_idx;

		IF $no_more_rows THEN 
			CLOSE cursor_1;
			LEAVE LOOP1;
		END IF;
		
					
		INSERT INTO t_message(msg_idx,member_idx) VALUES($p_msg_idx,$db_user_idx);
		
	END loop LOOP1;
	
	SET $ret = 1000;

	RETURN $ret;
RETURN 1;
END$$

DELIMITER ;

CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `day_bet_classic` AS select 'bet_tot_ing' AS `stype`,`member_bet`.`bet_status` AS `status`,count(0) AS `cnt`,sum(`member_bet`.`total_bet_money`) AS `s_money`,0 AS `s_win_money` from (`member_bet` left join `member` on(`member_bet`.`member_idx` = `member`.`idx`)) where `member_bet`.`bet_type` = 1 and `member_bet`.`is_classic` = 'ON' and `member_bet`.`bet_status` = 1 and `member`.`level` <> 9 union all select 'bet_tot' AS `stype`,`member_bet`.`bet_status` AS `status`,count(0) AS `cnt`,`member_bet`.`total_bet_money` AS `s_money`,`member_bet`.`take_money` AS `s_win_money` from (`member_bet` left join `member` on(`member_bet`.`member_idx` = `member`.`idx`)) where `member_bet`.`calculate_dt` >= date_format(current_timestamp(),'%Y-%m-%d 00:00:00') and `member_bet`.`calculate_dt` <= date_format(current_timestamp(),'%Y-%m-%d 23:59:59') and `member_bet`.`bet_type` = 1 and `member_bet`.`bet_status` = 3 and `member_bet`.`is_classic` = 'ON' and `member`.`level` <> 9 and `member_bet`.`total_bet_money` <> `member_bet`.`take_money` group by `member_bet`.`idx` union all select 'bet_tot_sports' AS `stype`,`member_bet`.`bet_status` AS `status`,count(0) AS `cnt`,`member_bet`.`total_bet_money` AS `s_money`,sum(`member_bet`.`take_money`) AS `s_win_money` from (`member_bet` left join `member` on(`member_bet`.`member_idx` = `member`.`idx`)) where `member_bet`.`calculate_dt` >= date_format(current_timestamp(),'%Y-%m-%d 00:00:00') and `member_bet`.`calculate_dt` <= date_format(current_timestamp(),'%Y-%m-%d 23:59:59') and `member_bet`.`bet_type` = 1 and `member_bet`.`bet_status` = 3 and `member_bet`.`is_classic` = 'ON' and `member_bet`.`total_bet_money` <> `member_bet`.`take_money` and `member`.`level` <> 9;


DELIMITER $$
CREATE PROCEDURE `day_bet_mini_distributor`(
	-- DECLARE ret_code INT;
)
BEGIN
	SELECT 
        'bet_tot_mini_powerball' AS `stype`,
        SUM(`mini_game_member_bet`.`total_bet_money`) AS `s_money`
    FROM
        (`mini_game_member_bet`
        LEFT JOIN `member` ON (`mini_game_member_bet`.`member_idx` = `member`.`idx`))
    WHERE
        `mini_game_member_bet`.`calculate_dt` >= DATE_FORMAT(CURRENT_TIMESTAMP(), '%Y-%m-%d 00:00:00')
            AND `mini_game_member_bet`.`calculate_dt` <= DATE_FORMAT(CURRENT_TIMESTAMP(), '%Y-%m-%d 23:59:59')
            AND `mini_game_member_bet`.`bet_type` = 3
            AND `mini_game_member_bet`.`bet_status` = 3
            AND `member`.`level` <> 9
            AND `mini_game_member_bet`.`total_bet_money` <> `mini_game_member_bet`.`take_money` 
    UNION ALL SELECT 
        'bet_tot_mini_powerball_win' AS `stype`,
        SUM(`mini_game_member_bet`.`take_money`) AS `s_money`
    FROM
        (`mini_game_member_bet`
        LEFT JOIN `member` ON (`mini_game_member_bet`.`member_idx` = `member`.`idx`))
    WHERE
        `mini_game_member_bet`.`calculate_dt` >= DATE_FORMAT(CURRENT_TIMESTAMP(), '%Y-%m-%d 00:00:00')
            AND `mini_game_member_bet`.`calculate_dt` <= DATE_FORMAT(CURRENT_TIMESTAMP(), '%Y-%m-%d 23:59:59')
            AND `mini_game_member_bet`.`bet_type` = 3
            AND `mini_game_member_bet`.`bet_status` = 3
            AND `mini_game_member_bet`.`take_money` > 0
            AND `member`.`level` <> 9 
    UNION ALL SELECT 
        'bet_tot_mini_pladder' AS `stype`,
        SUM(`mini_game_member_bet`.`total_bet_money`) AS `s_money`
    FROM
        (`mini_game_member_bet`
        LEFT JOIN `member` ON (`mini_game_member_bet`.`member_idx` = `member`.`idx`))
    WHERE
        `mini_game_member_bet`.`calculate_dt` >= DATE_FORMAT(CURRENT_TIMESTAMP(), '%Y-%m-%d 00:00:00')
            AND `mini_game_member_bet`.`calculate_dt` <= DATE_FORMAT(CURRENT_TIMESTAMP(), '%Y-%m-%d 23:59:59')
            AND `mini_game_member_bet`.`bet_type` = 4
            AND `mini_game_member_bet`.`bet_status` = 3
            AND `member`.`level` <> 9
            AND `mini_game_member_bet`.`total_bet_money` <> `mini_game_member_bet`.`take_money` 
    UNION ALL SELECT 
        'bet_tot_mini_pladder_win' AS `stype`,
        SUM(`mini_game_member_bet`.`take_money`) AS `s_money`
    FROM
        (`mini_game_member_bet`
        LEFT JOIN `member` ON (`mini_game_member_bet`.`member_idx` = `member`.`idx`))
    WHERE
        `mini_game_member_bet`.`calculate_dt` >= DATE_FORMAT(CURRENT_TIMESTAMP(), '%Y-%m-%d 00:00:00')
            AND `mini_game_member_bet`.`calculate_dt` <= DATE_FORMAT(CURRENT_TIMESTAMP(), '%Y-%m-%d 23:59:59')
            AND `mini_game_member_bet`.`bet_type` = 4
            AND `mini_game_member_bet`.`bet_status` = 3
            AND `mini_game_member_bet`.`take_money` > 0
            AND `member`.`level` <> 9 
    UNION ALL SELECT 
        'bet_tot_mini_kladder' AS `stype`,
        SUM(`mini_game_member_bet`.`total_bet_money`) AS `s_money`
    FROM
        (`mini_game_member_bet`
        LEFT JOIN `member` ON (`mini_game_member_bet`.`member_idx` = `member`.`idx`))
    WHERE
        `mini_game_member_bet`.`calculate_dt` >= DATE_FORMAT(CURRENT_TIMESTAMP(), '%Y-%m-%d 00:00:00')
            AND `mini_game_member_bet`.`calculate_dt` <= DATE_FORMAT(CURRENT_TIMESTAMP(), '%Y-%m-%d 23:59:59')
            AND `mini_game_member_bet`.`bet_type` = 5
            AND `mini_game_member_bet`.`bet_status` = 3
            AND `member`.`level` <> 9
            AND `mini_game_member_bet`.`total_bet_money` <> `mini_game_member_bet`.`take_money` 
    UNION ALL SELECT 
        'bet_tot_mini_kladder_win' AS `stype`,
        SUM(`mini_game_member_bet`.`take_money`) AS `s_money`
    FROM
        (`mini_game_member_bet`
        LEFT JOIN `member` ON (`mini_game_member_bet`.`member_idx` = `member`.`idx`))
    WHERE
        `mini_game_member_bet`.`calculate_dt` >= DATE_FORMAT(CURRENT_TIMESTAMP(), '%Y-%m-%d 00:00:00')
            AND `mini_game_member_bet`.`calculate_dt` <= DATE_FORMAT(CURRENT_TIMESTAMP(), '%Y-%m-%d 23:59:59')
            AND `mini_game_member_bet`.`bet_type` = 5
            AND `mini_game_member_bet`.`bet_status` = 3
            AND `mini_game_member_bet`.`take_money` > 0
            AND `member`.`level` <> 9 
    UNION ALL SELECT 
        'bet_tot_mini_b_soccer' AS `stype`,
        SUM(`mini_game_member_bet`.`total_bet_money`) AS `s_money`
    FROM
        (`mini_game_member_bet`
        LEFT JOIN `member` ON (`mini_game_member_bet`.`member_idx` = `member`.`idx`))
    WHERE
        `mini_game_member_bet`.`calculate_dt` >= DATE_FORMAT(CURRENT_TIMESTAMP(), '%Y-%m-%d 00:00:00')
            AND `mini_game_member_bet`.`calculate_dt` <= DATE_FORMAT(CURRENT_TIMESTAMP(), '%Y-%m-%d 23:59:59')
            AND `mini_game_member_bet`.`bet_type` = 6
            AND `mini_game_member_bet`.`bet_status` = 3
            AND `member`.`level` <> 9
            AND `mini_game_member_bet`.`total_bet_money` <> `mini_game_member_bet`.`take_money` 
    UNION ALL SELECT 
        'bet_tot_mini_b_soccer_win' AS `stype`,
        SUM(`mini_game_member_bet`.`take_money`) AS `s_money`
    FROM
        (`mini_game_member_bet`
        LEFT JOIN `member` ON (`mini_game_member_bet`.`member_idx` = `member`.`idx`))
    WHERE
        `mini_game_member_bet`.`calculate_dt` >= DATE_FORMAT(CURRENT_TIMESTAMP(), '%Y-%m-%d 00:00:00')
            AND `mini_game_member_bet`.`calculate_dt` <= DATE_FORMAT(CURRENT_TIMESTAMP(), '%Y-%m-%d 23:59:59')
            AND `mini_game_member_bet`.`bet_type` = 6
            AND `mini_game_member_bet`.`bet_status` = 3
            AND `mini_game_member_bet`.`take_money` > 0
            AND `member`.`level` <> 9;
END$$
DELIMITER ;
