DROP TABLE IF EXISTS `babyfeed`.`tbl_member_confirm` ;

CREATE TABLE IF NOT EXISTS `babyfeed`.`tbl_member_confirm` (
  `member_id` VARCHAR(30) NOT NULL,
  `email` VARCHAR(30) NULL DEFAULT NULL,
  `email_join_status_cd_no` VARCHAR(10) NOT NULL DEFAULT 'SEJS_R',
  `email_join_limit_dt` DATETIME NULL DEFAULT NULL,
  `email_enc_word` VARCHAR(40) NULL DEFAULT NULL,
  `phone_num` VARCHAR(15) NULL DEFAULT NULL,
  `phone_confirm_cd_no` VARCHAR(10) NOT NULL DEFAULT 'SPC_R',
  `phone_confirm_dt` DATETIME NULL DEFAULT NULL,
  `phone_confirm_status` VARCHAR(30) NULL DEFAULT NULL,
  `reg_dt` DATETIME NOT NULL,
  `mod_dt` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`member_id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

DROP TABLE IF EXISTS `babyfeed`.`tbl_member_issued_pwd` ;

CREATE TABLE IF NOT EXISTS `babyfeed`.`tbl_member_issued_pwd` (
  `smfp_no` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `member_id` VARCHAR(30) NOT NULL,
  `reset_tp` VARCHAR(10) NOT NULL DEFAULT 'email',
  `phone_num` VARCHAR(15) NULL DEFAULT NULL,
  `email` VARCHAR(30) NULL DEFAULT NULL,
  `enc_word` VARCHAR(60) NULL DEFAULT NULL,
  `temp_pwd` VARCHAR(60) NULL DEFAULT NULL,
  `is_update` CHAR(1) NOT NULL DEFAULT 'N',
  `req_ip` VARCHAR(30) NULL DEFAULT NULL,
  `reg_dt` DATETIME NOT NULL,
  `mod_dt` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`smfp_no`, `member_id`))
ENGINE = InnoDB
AUTO_INCREMENT = 1
DEFAULT CHARACTER SET = utf8;


DROP TABLE IF EXISTS `babyfeed`.`tbl_member_env` ;

CREATE TABLE IF NOT EXISTS `babyfeed`.`tbl_member_env` (
  `member_id` VARCHAR(30) NOT NULL,
  `sona_light_m` INT(11) NOT NULL DEFAULT '0',  
  `lang_cd_no` VARCHAR(30) NOT NULL DEFAULT 'S_L_KOREAN',
  `location_cd_no` VARCHAR(30) NOT NULL DEFAULT 'S_L_KOREAN',
  `reg_dt` DATETIME NOT NULL,
  `mod_dt` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`member_id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


DROP TABLE IF EXISTS `babyfeed`.`tbl_member_device` ;

CREATE TABLE IF NOT EXISTS `babyfeed`.`tbl_member_device` (
  `member_id` VARCHAR(30) NOT NULL,  
  `prod_id` VARCHAR(20) NULL DEFAULT NULL,
  `model_nm` VARCHAR(20) NULL DEFAULT NULL,
  `serial_no` VARCHAR(20) NULL DEFAULT NULL,
  `sw_ver` VARCHAR(20) NULL DEFAULT NULL,
  `hw_ver` VARCHAR(20) NULL DEFAULT NULL,
  `reg_dt` DATETIME NOT NULL,
  `mod_dt` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`member_id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


INSERT tbl_code_group (cg_cd, title, reg_dt, sys_member_id) Values ('S_PHONE_CONFIRM', '폰인증 상태', now(), 'renamaestro');
INSERT tbl_code_group (cg_cd, title, reg_dt, sys_member_id) Values ('S_EMAIL_JOIN_STATUS', '이메일가입승인상태', now(), 'renamaestro');
INSERT tbl_code_item_str (cd_no, title, order_no, reg_dt, sys_member_id, cg_cd) Values ('SPC_C', '폰인증 완료', 2, now(), 'renamaestro', 'S_PHONE_CONFIRM');
INSERT tbl_code_item_str (cd_no, title, order_no, reg_dt, sys_member_id, cg_cd) Values ('SPC_F', '폰인증실패', 3, now(), 'renamaestro', 'S_PHONE_CONFIRM');
INSERT tbl_code_item_str (cd_no, title, order_no, reg_dt, sys_member_id, cg_cd) Values ('SPC_R', '폰 인증 전', 1, now(), 'renamaestro', 'S_PHONE_CONFIRM');
INSERT tbl_code_item_str (cd_no, title, order_no, reg_dt, sys_member_id, cg_cd) Values ('SEJS_C', '사용자 확인', 3, now(), 'renamaestro', 'S_EMAIL_JOIN_STATUS');
INSERT tbl_code_item_str (cd_no, title, order_no, reg_dt, sys_member_id, cg_cd) Values ('SEJS_F', '발송 실패', 4, now(), 'renamaestro', 'S_EMAIL_JOIN_STATUS');
INSERT tbl_code_item_str (cd_no, title, order_no, reg_dt, sys_member_id, cg_cd) Values ('SEJS_R', '발송 전', 1, now(), 'renamaestro', 'S_EMAIL_JOIN_STATUS');
INSERT tbl_code_item_str (cd_no, title, order_no, reg_dt, sys_member_id, cg_cd) Values ('SEJS_S', '발송', 2, now(), 'renamaestro', 'S_EMAIL_JOIN_STATUS');