-- -----------------------------------------------------
영양전문가리뷰
-- -----------------------------------------------------
CREATE TABLE `tbl_nutrition_review` ( 
`nr_no` int(11) NOT NULL AUTO_INCREMENT,
`cate_cd_no` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
`nr_title` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
`nr_content` varchar(500) COLLATE utf8mb4_unicode_ci NOT NULL,
`sys_member_id` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`reg_dt` datetime NOT NULL,\n  `mod_dt` datetime DEFAULT NULL,
PRIMARY KEY (`nr_no`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci'

CREATE TABLE `tbl_nutrition_review_img` (
`nri_no` int(11) NOT NULL AUTO_INCREMENT,
`append_src_img_file` bigint(20) NOT NULL,
`append_300_img_file` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
`nr_no` int(11) NOT NULL,\n  PRIMARY KEY (`nri_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci'

CREATE TABLE IF NOT EXISTS `babyfeed`.`tbl_nutrition_review_like` (
  `nrl_no` INT NOT NULL AUTO_INCREMENT,
  `member_id` VARCHAR(30) NOT NULL,
  `reg_dt` DATETIME NOT NULL,
  `nr_no` INT NOT NULL,
  PRIMARY KEY (`member_id`, `nr_no`),
  UNIQUE INDEX `nrl_no_UNIQUE` (`nrl_no` ASC))
ENGINE = InnoDB;

CREATE TABLE `tbl_nutrition_review_comment` (
`nrc_id` int(11) NOT NULL AUTO_INCREMENT,
`p_nrc_id` int(11) NOT NULL DEFAULT '0',
`nr_no` int(11) NOT NULL DEFAULT '0',
`title` varchar(300) COLLATE utf8mb4_unicode_ci NOT NULL,
`reg_dt` datetime NOT NULL,
`member_id` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
PRIMARY KEY (`nrc_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

-- -----------------------------------------------------
성장전문가리뷰 
-- -----------------------------------------------------
CREATE TABLE `tbl_growth_review` (
`gr_no` int(11) NOT NULL AUTO_INCREMENT,
`gr_title` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
`gr_content` varchar(500) COLLATE utf8mb4_unicode_ci NOT NULL,
`sys_member_id` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`reg_dt` datetime NOT NULL,\n  `mod_dt` datetime DEFAULT NULL,
PRIMARY KEY (`gr_no`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci'

tbl_growth_review_img

CREATE TABLE `tbl_growth_review_img` (
`gri_no` int(11) NOT NULL AUTO_INCREMENT,
 `append_src_img_file` bigint(20) NOT NULL,
 `append_300_img_file` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
 `gr_no` int(11) NOT NULL,\n  PRIMARY KEY (`gri_no`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci'
 
CREATE TABLE IF NOT EXISTS `babyfeed`.`tbl_growth_review_like` (
  `grl_no` INT NOT NULL AUTO_INCREMENT,
  `member_id` VARCHAR(30) NOT NULL,
  `reg_dt` DATETIME NOT NULL,
  `gr_no` INT NOT NULL,
  PRIMARY KEY (`member_id`, `gr_no`),
  UNIQUE INDEX `nrl_no_UNIQUE` (`grl_no` ASC))
ENGINE = InnoDB;

CREATE TABLE `tbl_growth_review_comment` (
`grc_id` int(11) NOT NULL AUTO_INCREMENT,
`p_grc_id` int(11) NOT NULL DEFAULT '0',
`gr_no` int(11) NOT NULL DEFAULT '0',
`title` varchar(300) COLLATE utf8mb4_unicode_ci NOT NULL,
`reg_dt` datetime NOT NULL,
`member_id` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
PRIMARY KEY (`grc_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

-- -----------------------------------------------------
광고 
-- -----------------------------------------------------

CREATE TABLE `tbl_ad` (
`ad_no` int(11) NOT NULL AUTO_INCREMENT,
`ad_title` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
`ad_start_dt` datetime NOT NULL,
`ad_end_dt` datetime NOT NULL,
`sys_member_id` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
`reg_dt` datetime NOT NULL,
`mod_dt` datetime DEFAULT NULL,
PRIMARY KEY (`ad_no`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

CREATE TABLE `tbl_ad_img` (
`adi_no` int(11) NOT NULL AUTO_INCREMENT,
`append_src_img_file` bigint(20) NOT NULL,
`append_300_img_file` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
`ad_no` int(11) NOT NULL,
PRIMARY KEY (`adi_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci'

-- -----------------------------------------------------
영양사 추천 
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `babyfeed`.`tbl_nutritionist_recommend` (
  `nr_no` INT NOT NULL AUTO_INCREMENT,
  `nr_title` VARCHAR(100) NOT NULL,
  `nr_content` TEXT CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci' NULL DEFAULT NULL,
  `nr_start_dt` DATETIME NOT NULL,
  `nr_end_dt` DATETIME NOT NULL,
  `sys_member_id` VARCHAR(30) NOT NULL,
  `reg_dt` DATETIME NOT NULL,
  `mod_dt` DATETIME NULL,
  PRIMARY KEY (`nr_no`))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `babyfeed`.`tbl_nutritionist_recommend_img` (
  `nri_no` INT NOT NULL AUTO_INCREMENT,
  `append_src_img_file` BIGINT(20) NOT NULL,
  `append_300_img_file` VARCHAR(20) NOT NULL,
  `nr_no` INT NOT NULL,
  PRIMARY KEY (`nri_no`))
ENGINE = InnoDB;

-- -----------------------------------------------------
홈메이드 이유식 로그 
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `babyfeed`.`tbl_log_home_bf` (
  `lhb_no` INT NOT NULL AUTO_INCREMENT,
  `dhb_no` VARCHAR(11) NOT NULL,
  `member_id` VARCHAR(30) NOT NULL,
  `reg_dt` DATETIME NOT NULL,
  PRIMARY KEY (`lhb_no`))
ENGINE = InnoDB;

-- -----------------------------------------------------
홈메이드 이유식 기록 수정
-- -----------------------------------------------------

ALTER TABLE tbl_bf_daily_nutrition_9 CHANGE db_dn_no db_dhb_no int(11)

-- -----------------------------------------------------
대/소변 기록 
-- -----------------------------------------------------

INSERT tbl_code_group (cg_cd, title, reg_dt, sys_member_id) 
Values ('BF_FECES_QTY', '배변량', now(), 'so2');
INSERT tbl_code_group (cg_cd, title, reg_dt, sys_member_id) 
Values ('BF_FECES_COLOR', '대변색', now(), 'so2');
INSERT tbl_code_group (cg_cd, title, reg_dt, sys_member_id) 
Values ('BF_PEE_COLOR', '소변색', now(), 'so2');

INSERT tbl_code_item ( title, order_no, cg_cd, val_1, reg_dt, sys_member_id) 
Values ( '매우적음', 1, 'BF_FECES_QTY', 0, now(), 'so2');
INSERT tbl_code_item ( title, order_no, cg_cd, val_1, reg_dt, sys_member_id) 
Values ( '적음', 2, 'BF_FECES_QTY', 25, now(), 'so2');
INSERT tbl_code_item ( title, order_no, cg_cd, val_1, reg_dt, sys_member_id) 
Values ( '보통', 3, 'BF_FECES_QTY', 50, now(), 'so2');
INSERT tbl_code_item ( title, order_no, cg_cd, val_1, reg_dt, sys_member_id) 
Values ( '많음', 4, 'BF_FECES_QTY', 75, now(), 'so2');
INSERT tbl_code_item ( title, order_no, cg_cd, val_1, reg_dt, sys_member_id) 
Values ( '매우많음', 5, 'BF_FECES_QTY', 100, now(), 'so2');

INSERT tbl_code_item_str ( title, order_no, cd_no, val_1, reg_dt, sys_member_id,cg_cd) 
Values ( '매우적음', 1, 'BF_FECES_QTY_1', 0, now(), 'so2', 'BF_FECES_QTY');
INSERT tbl_code_item_str ( title, order_no, cd_no, val_1, reg_dt, sys_member_id,cg_cd) 
Values ( '적음', 2, 'BF_FECES_QTY_2', 25, now(), 'so2', 'BF_FECES_QTY');
INSERT tbl_code_item_str ( title, order_no, cd_no, val_1, reg_dt, sys_member_id,cg_cd) 
Values ( '보통', 3, 'BF_FECES_QTY_3', 50, now(), 'so2', 'BF_FECES_QTY');
INSERT tbl_code_item_str ( title, order_no, cd_no, val_1, reg_dt, sys_member_id,cg_cd) 
Values ( '많음', 4, 'BF_FECES_QTY_4', 75, now(), 'so2', 'BF_FECES_QTY');
INSERT tbl_code_item_str ( title, order_no, cd_no, val_1, reg_dt, sys_member_id,cg_cd) 
Values ( '매우많음', 5, 'BF_FECES_QTY_5', 100, now(), 'so2', 'BF_FECES_QTY');

INSERT tbl_code_item ( title, order_no, cg_cd, val_1, reg_dt, sys_member_id) 
Values ( '회색', 1, 'BF_FECES_COLOR', 0, now(), 'so2');
INSERT tbl_code_item ( title, order_no, cg_cd, val_1, reg_dt, sys_member_id) 
Values ( '진한갈색', 2, 'BF_FECES_COLOR', 1, now(), 'so2');
INSERT tbl_code_item ( title, order_no, cg_cd, val_1, reg_dt, sys_member_id) 
Values ( '갈색', 3, 'BF_FECES_COLOR', 2, now(), 'so2');
INSERT tbl_code_item ( title, order_no, cg_cd, val_1, reg_dt, sys_member_id) 
Values ( '황갈색', 4, 'BF_FECES_COLOR', 3, now(), 'so2');
INSERT tbl_code_item ( title, order_no, cg_cd, val_1, reg_dt, sys_member_id) 
Values ( '황금색', 5, 'BF_FECES_COLOR', 4, now(), 'so2');

INSERT tbl_code_item_str ( title, order_no, cd_no, val_1, reg_dt, sys_member_id,cg_cd) 
Values ( '회색', 1, 'BF_FECES_COLOR_1', 0, now(), 'so2', 'BF_FECES_COLOR');
INSERT tbl_code_item_str ( title, order_no, cd_no, val_1, reg_dt, sys_member_id,cg_cd) 
Values ( '진한갈색', 2, 'BF_FECES_COLOR_2', 1, now(), 'so2', 'BF_FECES_COLOR');
INSERT tbl_code_item_str ( title, order_no, cd_no, val_1, reg_dt, sys_member_id,cg_cd) 
Values ( '갈색', 3, 'BF_FECES_COLOR_3', 2, now(), 'so2', 'BF_FECES_COLOR');
INSERT tbl_code_item_str ( title, order_no, cd_no, val_1, reg_dt, sys_member_id,cg_cd) 
Values ( '황갈색', 4, 'BF_FECES_COLOR_4', 3, now(), 'so2', 'BF_FECES_COLOR');
INSERT tbl_code_item_str ( title, order_no, cd_no, val_1, reg_dt, sys_member_id,cg_cd) 
Values ( '황금색', 5, 'BF_FECES_COLOR_5', 4, now(), 'so2', 'BF_FECES_COLOR');

INSERT tbl_code_item ( title, order_no, cg_cd, val_1, reg_dt, sys_member_id) 
Values ( '진한노랑', 1, 'BF_PEE_COLOR', 0, now(), 'so2');
INSERT tbl_code_item ( title, order_no, cg_cd, val_1, reg_dt, sys_member_id) 
Values ( '노랑', 2, 'BF_PEE_COLOR', 33, now(), 'so2');
INSERT tbl_code_item ( title, order_no, cg_cd, val_1, reg_dt, sys_member_id) 
Values ( '연한노랑', 3, 'BF_PEE_COLOR', 66, now(), 'so2');
INSERT tbl_code_item ( title, order_no, cg_cd, val_1, reg_dt, sys_member_id) 
Values ( '무색', 4, 'BF_PEE_COLOR', 100, now(), 'so2');

INSERT tbl_code_item_str ( title, order_no, cd_no, val_1, reg_dt, sys_member_id,cg_cd) 
Values ( '진한노랑', 1, 'BF_PEE_COLOR_1', 0, now(), 'so2', 'BF_PEE_COLOR');
INSERT tbl_code_item_str ( title, order_no, cd_no, val_1, reg_dt, sys_member_id,cg_cd) 
Values ( '노랑', 2, 'BF_PEE_COLOR_2', 33, now(), 'so2', 'BF_PEE_COLOR');
INSERT tbl_code_item_str ( title, order_no, cd_no, val_1, reg_dt, sys_member_id,cg_cd) 
Values ( '연한노랑', 3, 'BF_PEE_COLOR_3', 66, now(), 'so2', 'BF_PEE_COLOR');
INSERT tbl_code_item_str ( title, order_no, cd_no, val_1, reg_dt, sys_member_id,cg_cd) 
Values ( '무색', 4, 'BF_PEE_COLOR_4', 100, now(), 'so2', 'BF_PEE_COLOR');


-- -----------------------------------------------------
예방접종
-- -----------------------------------------------------


CREATE TABLE IF NOT EXISTS `babyfeed`.`tbl_db_vac` (
  `dv_no` INT NOT NULL AUTO_INCREMENT,
  `dv_disease` VARCHAR(100) NULL,
  `dv_type` VARCHAR(100) NULL,
  `total_rounds` VARCHAR(20) NOT NULL,
  `reg_dt` DATETIME NOT NULL,
  `mod_dt` DATETIME NULL,
  `sys_member_id` VARCHAR(30) NULL,
  PRIMARY KEY (`dv_no`))
ENGINE = InnoDB;

INSERT INTO tbl_db_vac (dv_disease,dv_type,reg_dt,sys_member_id,total_rounds)
VALUES ('결핵','BCG(피내용)',NOW(),'so2',1);
INSERT INTO tbl_db_vac (dv_disease,dv_type,reg_dt,sys_member_id,total_rounds)
VALUES ('B형간염','HepB',NOW(),'so2',3);
INSERT INTO tbl_db_vac (dv_disease,dv_type,reg_dt,sys_member_id,total_rounds)
VALUES ('디프테리아','DTaP',NOW(),'so2',5);
INSERT INTO tbl_db_vac (dv_disease,dv_type,reg_dt,sys_member_id,total_rounds)
VALUES ('파상풍','Tdap/Td',NOW(),'so2',1);
INSERT INTO tbl_db_vac (dv_disease,reg_dt,sys_member_id,total_rounds)
VALUES ('백일해',NOW(),'so2',0);
INSERT INTO tbl_db_vac (dv_disease,dv_type,reg_dt,sys_member_id,total_rounds)
VALUES ('폴리오','IPV',NOW(),'so2',4);
INSERT INTO tbl_db_vac (dv_disease,dv_type,reg_dt,sys_member_id,total_rounds)
VALUES ('b형헤모필루스인플루엔자','Hib',NOW(),'so2',4);
INSERT INTO tbl_db_vac (dv_disease,dv_type,reg_dt,sys_member_id,total_rounds)
VALUES ('폐렴구균','PCV',NOW(),'so2',4);
INSERT INTO tbl_db_vac (dv_type,reg_dt,sys_member_id,total_rounds)
VALUES ('PCSV',NOW(),'so2',0);
INSERT INTO tbl_db_vac (dv_disease,dv_type,reg_dt,sys_member_id,total_rounds)
VALUES ('홍역','MMR',NOW(),'so2',4);
INSERT INTO tbl_db_vac (dv_disease,reg_dt,sys_member_id,total_rounds)
VALUES ('유행성이하선염',NOW(),'so2',0);
INSERT INTO tbl_db_vac (dv_disease,reg_dt,sys_member_id,total_rounds)
VALUES ('풍진',NOW(),'so2',0);
INSERT INTO tbl_db_vac (dv_disease,dv_type,reg_dt,sys_member_id,total_rounds)
VALUES ('수두','VAR',NOW(),'so2',1);
INSERT INTO tbl_db_vac (dv_disease,dv_type,reg_dt,sys_member_id,total_rounds)
VALUES ('A형간염','HepA',NOW(),'so2',2);
INSERT INTO tbl_db_vac (dv_disease,dv_type,reg_dt,sys_member_id,total_rounds)
VALUES ('일본뇌염','IJEV(불활성화 백신)',NOW(),'so2',5);
INSERT INTO tbl_db_vac (dv_disease,dv_type,reg_dt,sys_member_id,total_rounds)
VALUES ('일본뇌염','LJEV(약독화 생백신)',NOW(),'so2',2);
INSERT INTO tbl_db_vac (dv_disease,dv_type,reg_dt,sys_member_id,total_rounds)
VALUES ('사람유두종바이러스 감염증','HPV',NOW(),'so2',2);
INSERT INTO tbl_db_vac (dv_disease,dv_type,reg_dt,sys_member_id,total_rounds)
VALUES ('인플루엔자','IIV',NOW(),'so2',10);
INSERT INTO tbl_db_vac (dv_disease,dv_type,reg_dt,sys_member_id,total_rounds)
VALUES ('로타바이러스 감염증','RV1',NOW(),'so2',2);
INSERT INTO tbl_db_vac (dv_disease,dv_type,reg_dt,sys_member_id,total_rounds)
VALUES ('로타바이러스 감염증','RV5',NOW(),'so2',3);



CREATE TABLE IF NOT EXISTS `babyfeed`.`tbl_db_vac_schedule` (
  `dvs_no` INT NOT NULL AUTO_INCREMENT,
  `dv_no` INT(11) NOT NULL,
  `round` VARCHAR(20) NOT NULL,
  `start_label` INT(11) NOT NULL,
  `end_label` INT(11) NOT NULL,
  `reg_dt` DATETIME NOT NULL,
  `mod_dt` DATETIME NULL,
  `sys_member_id` VARCHAR(30) NULL,
  `dv_check` tinyint(1) unsigned zerofill DEFAULT 0,
  PRIMARY KEY (`dvs_no`))
ENGINE = InnoDB;

INSERT INTO tbl_db_vac_schedule (reg_dt,sys_member_id,dv_no,round,start_label,end_label) VALUES (NOW(),'so2',1,1,1,1);
INSERT INTO tbl_db_vac_schedule (reg_dt,sys_member_id,dv_no,round,start_label,end_label) VALUES (NOW(),'so2',2,1,1,1);
INSERT INTO tbl_db_vac_schedule (reg_dt,sys_member_id,dv_no,round,start_label,end_label) VALUES (NOW(),'so2',2,2,2,2);
INSERT INTO tbl_db_vac_schedule (reg_dt,sys_member_id,dv_no,round,start_label,end_label) VALUES (NOW(),'so2',2,3,5,5);
INSERT INTO tbl_db_vac_schedule (reg_dt,sys_member_id,dv_no,round,start_label,end_label) VALUES (NOW(),'so2',3,1,3,3);
INSERT INTO tbl_db_vac_schedule (reg_dt,sys_member_id,dv_no,round,start_label,end_label) VALUES (NOW(),'so2',3,2,4,4);
INSERT INTO tbl_db_vac_schedule (reg_dt,sys_member_id,dv_no,round,start_label,end_label) VALUES (NOW(),'so2',3,3,5,5);
INSERT INTO tbl_db_vac_schedule (reg_dt,sys_member_id,dv_no,round,start_label,end_label) VALUES (NOW(),'so2',3,4,7,8);
INSERT INTO tbl_db_vac_schedule (reg_dt,sys_member_id,dv_no,round,start_label,end_label) VALUES (NOW(),'so2',3,5,11,12);
INSERT INTO tbl_db_vac_schedule (reg_dt,sys_member_id,dv_no,round,start_label,end_label) VALUES (NOW(),'so2',4,1,13,14);
INSERT INTO tbl_db_vac_schedule (reg_dt,sys_member_id,dv_no,round,start_label,end_label) VALUES (NOW(),'so2',6,1,3,3);
INSERT INTO tbl_db_vac_schedule (reg_dt,sys_member_id,dv_no,round,start_label,end_label) VALUES (NOW(),'so2',6,2,4,4);
INSERT INTO tbl_db_vac_schedule (reg_dt,sys_member_id,dv_no,round,start_label,end_label) VALUES (NOW(),'so2',6,3,5,8);
INSERT INTO tbl_db_vac_schedule (reg_dt,sys_member_id,dv_no,round,start_label,end_label) VALUES (NOW(),'so2',6,4,11,12);
INSERT INTO tbl_db_vac_schedule (reg_dt,sys_member_id,dv_no,round,start_label,end_label) VALUES (NOW(),'so2',7,1,3,3);
INSERT INTO tbl_db_vac_schedule (reg_dt,sys_member_id,dv_no,round,start_label,end_label) VALUES (NOW(),'so2',7,2,4,4);
INSERT INTO tbl_db_vac_schedule (reg_dt,sys_member_id,dv_no,round,start_label,end_label) VALUES (NOW(),'so2',7,3,5,5);
INSERT INTO tbl_db_vac_schedule (reg_dt,sys_member_id,dv_no,round,start_label,end_label) VALUES (NOW(),'so2',7,4,6,7);
INSERT INTO tbl_db_vac_schedule (reg_dt,sys_member_id,dv_no,round,start_label,end_label) VALUES (NOW(),'so2',8,1,3,3);
INSERT INTO tbl_db_vac_schedule (reg_dt,sys_member_id,dv_no,round,start_label,end_label) VALUES (NOW(),'so2',8,2,4,4);
INSERT INTO tbl_db_vac_schedule (reg_dt,sys_member_id,dv_no,round,start_label,end_label) VALUES (NOW(),'so2',8,3,5,5);
INSERT INTO tbl_db_vac_schedule (reg_dt,sys_member_id,dv_no,round,start_label,end_label) VALUES (NOW(),'so2',8,4,6,7);
INSERT INTO tbl_db_vac_schedule (reg_dt,sys_member_id,dv_no,round,start_label,end_label) VALUES (NOW(),'so2',10,1,6,7);
INSERT INTO tbl_db_vac_schedule (reg_dt,sys_member_id,dv_no,round,start_label,end_label) VALUES (NOW(),'so2',10,2,11,12);
INSERT INTO tbl_db_vac_schedule (reg_dt,sys_member_id,dv_no,round,start_label,end_label) VALUES (NOW(),'so2',13,1,6,7);
INSERT INTO tbl_db_vac_schedule (reg_dt,sys_member_id,dv_no,round,start_label,end_label) VALUES (NOW(),'so2',14,1,6,7);
INSERT INTO tbl_db_vac_schedule (reg_dt,sys_member_id,dv_no,round,start_label,end_label) VALUES (NOW(),'so2',14,2,8,9);
INSERT INTO tbl_db_vac_schedule (reg_dt,sys_member_id,dv_no,round,start_label,end_label) VALUES (NOW(),'so2',15,1,6,7);
INSERT INTO tbl_db_vac_schedule (reg_dt,sys_member_id,dv_no,round,start_label,end_label) VALUES (NOW(),'so2',15,2,8,9);
INSERT INTO tbl_db_vac_schedule (reg_dt,sys_member_id,dv_no,round,start_label,end_label) VALUES (NOW(),'so2',15,3,10,10);
INSERT INTO tbl_db_vac_schedule (reg_dt,sys_member_id,dv_no,round,start_label,end_label) VALUES (NOW(),'so2',15,4,12,12);
INSERT INTO tbl_db_vac_schedule (reg_dt,sys_member_id,dv_no,round,start_label,end_label) VALUES (NOW(),'so2',15,5,14,14);
INSERT INTO tbl_db_vac_schedule (reg_dt,sys_member_id,dv_no,round,start_label,end_label) VALUES (NOW(),'so2',16,1,6,9);
INSERT INTO tbl_db_vac_schedule (reg_dt,sys_member_id,dv_no,round,start_label,end_label) VALUES (NOW(),'so2',16,2,10,10);
INSERT INTO tbl_db_vac_schedule (reg_dt,sys_member_id,dv_no,round,start_label,end_label) VALUES (NOW(),'so2',17,1,13,13);
INSERT INTO tbl_db_vac_schedule (reg_dt,sys_member_id,dv_no,round,start_label,end_label) VALUES (NOW(),'so2',17,2,14,14);
INSERT INTO tbl_db_vac_schedule (reg_dt,sys_member_id,dv_no,round,start_label,end_label) VALUES (NOW(),'so2',18,1,5,5);
INSERT INTO tbl_db_vac_schedule (reg_dt,sys_member_id,dv_no,round,start_label,end_label) VALUES (NOW(),'so2',18,1,6,6);
INSERT INTO tbl_db_vac_schedule (reg_dt,sys_member_id,dv_no,round,start_label,end_label) VALUES (NOW(),'so2',18,1,7,7);
INSERT INTO tbl_db_vac_schedule (reg_dt,sys_member_id,dv_no,round,start_label,end_label) VALUES (NOW(),'so2',18,1,8,8);
INSERT INTO tbl_db_vac_schedule (reg_dt,sys_member_id,dv_no,round,start_label,end_label) VALUES (NOW(),'so2',18,1,9,9);
INSERT INTO tbl_db_vac_schedule (reg_dt,sys_member_id,dv_no,round,start_label,end_label) VALUES (NOW(),'so2',18,1,10,10);
INSERT INTO tbl_db_vac_schedule (reg_dt,sys_member_id,dv_no,round,start_label,end_label) VALUES (NOW(),'so2',18,1,11,11);
INSERT INTO tbl_db_vac_schedule (reg_dt,sys_member_id,dv_no,round,start_label,end_label) VALUES (NOW(),'so2',18,1,12,12);
INSERT INTO tbl_db_vac_schedule (reg_dt,sys_member_id,dv_no,round,start_label,end_label) VALUES (NOW(),'so2',18,1,13,13);
INSERT INTO tbl_db_vac_schedule (reg_dt,sys_member_id,dv_no,round,start_label,end_label) VALUES (NOW(),'so2',18,1,14,14);
INSERT INTO tbl_db_vac_schedule (reg_dt,sys_member_id,dv_no,round,start_label,end_label) VALUES (NOW(),'so2',19,1,3,3);
INSERT INTO tbl_db_vac_schedule (reg_dt,sys_member_id,dv_no,round,start_label,end_label) VALUES (NOW(),'so2',19,2,4,4);
INSERT INTO tbl_db_vac_schedule (reg_dt,sys_member_id,dv_no,round,start_label,end_label) VALUES (NOW(),'so2',20,1,3,3);
INSERT INTO tbl_db_vac_schedule (reg_dt,sys_member_id,dv_no,round,start_label,end_label) VALUES (NOW(),'so2',20,2,4,4);
INSERT INTO tbl_db_vac_schedule (reg_dt,sys_member_id,dv_no,round,start_label,end_label) VALUES (NOW(),'so2',20,3,5,5);



INSERT INTO tbl_code_item(cg_cd,title,val_1,reg_dt,sys_member_id) VALUES ('BF_VAC_AGE','출생~1개월이내',1,NOW(),'so2');
INSERT INTO tbl_code_item(cg_cd,title,val_1,reg_dt,sys_member_id) VALUES ('BF_VAC_AGE','1개월',2,NOW(),'so2');
INSERT INTO tbl_code_item(cg_cd,title,val_1,reg_dt,sys_member_id) VALUES ('BF_VAC_AGE','2개월',3,NOW(),'so2');
INSERT INTO tbl_code_item(cg_cd,title,val_1,reg_dt,sys_member_id) VALUES ('BF_VAC_AGE','4개월',4,NOW(),'so2');
INSERT INTO tbl_code_item(cg_cd,title,val_1,reg_dt,sys_member_id) VALUES ('BF_VAC_AGE','6개월',5,NOW(),'so2');
INSERT INTO tbl_code_item(cg_cd,title,val_1,reg_dt,sys_member_id) VALUES ('BF_VAC_AGE','12개월',6,NOW(),'so2');
INSERT INTO tbl_code_item(cg_cd,title,val_1,reg_dt,sys_member_id) VALUES ('BF_VAC_AGE','15개월',7,NOW(),'so2');
INSERT INTO tbl_code_item(cg_cd,title,val_1,reg_dt,sys_member_id) VALUES ('BF_VAC_AGE','18개월',8,NOW(),'so2');
INSERT INTO tbl_code_item(cg_cd,title,val_1,reg_dt,sys_member_id) VALUES ('BF_VAC_AGE','19~23개월',9,NOW(),'so2');
INSERT INTO tbl_code_item(cg_cd,title,val_1,reg_dt,sys_member_id) VALUES ('BF_VAC_AGE','24~35개월',10,NOW(),'so2');
INSERT INTO tbl_code_item(cg_cd,title,val_1,reg_dt,sys_member_id) VALUES ('BF_VAC_AGE','만4세',11,NOW(),'so2');
INSERT INTO tbl_code_item(cg_cd,title,val_1,reg_dt,sys_member_id) VALUES ('BF_VAC_AGE','만6세',12,NOW(),'so2');
INSERT INTO tbl_code_item(cg_cd,title,val_1,reg_dt,sys_member_id) VALUES ('BF_VAC_AGE','만11세',13,NOW(),'so2');
INSERT INTO tbl_code_item(cg_cd,title,val_1,reg_dt,sys_member_id) VALUES ('BF_VAC_AGE','만12세',14,NOW(),'so2');



-- -----------------------------------------------------
카트
-- -----------------------------------------------------
CREATE TABLE `tbl_cart` ( 
	`c_no` int(11) NOT NULL AUTO_INCREMENT,
	`di_no` int(11)  NULL ,
	`dbi_no` int(11)  NULL ,
	`dbc_no` int(11)  NULL ,
	`title` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
	`member_id` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
	`reg_dt` datetime NOT NULL,
	`mod_dt` datetime DEFAULT NULL,
    
	PRIMARY KEY (`c_no`),
	
    CONSTRAINT `fk_tbl_cart_di_no`
	FOREIGN KEY (`di_no`)
    REFERENCES `babyfeed`.`tbl_db_ingre` (`di_no`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,

    CONSTRAINT `fk_tbl_cart_dbi_no`
	FOREIGN KEY (`dbi_no`)
    REFERENCES `babyfeed`.`tbl_db_bf_ingre` (`dbi_no`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
    
    CONSTRAINT `fk_tbl_cart_dbc_no`
    FOREIGN KEY (`dbc_no`)
    REFERENCES `babyfeed`.`tbl_db_bf_commerce` (`dbc_no`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;




CREATE TABLE `tbl_buy` ( 
	`b_no` int(11) NOT NULL AUTO_INCREMENT,
	`di_no` int(11)  NULL ,
	`dbi_no` int(11)  NULL ,
	`dbc_no` int(11)  NULL ,
	`member_id` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
	`reg_dt` datetime NOT NULL,
	`mod_dt` datetime DEFAULT NULL,
    
	PRIMARY KEY (`b_no`),
	
	CONSTRAINT `fk_tbl_buy_di_no`
	FOREIGN KEY (`di_no`)
    REFERENCES `babyfeed`.`tbl_db_ingre` (`di_no`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,  
    
    CONSTRAINT `fk_tbl_buy_dbi_no`
	FOREIGN KEY (`dbi_no`)
    REFERENCES `babyfeed`.`tbl_db_bf_ingre` (`dbi_no`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION, 
    
    CONSTRAINT `fk_tbl_buy_dbc_no`
    FOREIGN KEY (`dbc_no`)
    REFERENCES `babyfeed`.`tbl_db_bf_commerce` (`dbc_no`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci



CREATE TABLE IF NOT EXISTS `babyfeed`.`tbl_log_bf_ingre` (
  `lbi_no` INT NOT NULL AUTO_INCREMENT,
  `dbi_no` int(11) NOT NULL,
  `member_id` VARCHAR(30) NOT NULL,
  `reg_dt` DATETIME NOT NULL,
  PRIMARY KEY (`lbi_no`),
  
   CONSTRAINT `fk_tbl_log_bf_ingre`
    FOREIGN KEY (`dbi_no`)
    REFERENCES `babyfeed`.`tbl_db_bf_ingre` (`dbi_no`)
    ON DELETE NO ACTION
	ON UPDATE NO ACTION
    )
ENGINE = InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE `tbl_buy` ( 
	`b_no` int(11) NOT NULL AUTO_INCREMENT,
	`di_no` int(11)  NULL ,
	`dbi_no` int(11)  NULL ,
	`dbc_no` int(11)  NULL ,
	`member_id` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
	`reg_dt` datetime NOT NULL,
	`mod_dt` datetime DEFAULT NULL,
    
	PRIMARY KEY (`b_no`),
	
    CONSTRAINT `fk_tbl_buy_di_no`
	FOREIGN KEY (`di_no`)
    REFERENCES `babyfeed`.`tbl_db_ingre` (`di_no`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,

    CONSTRAINT `fk_tbl_buy_dbi_no`
	FOREIGN KEY (`dbi_no`)
    REFERENCES `babyfeed`.`tbl_db_bf_ingre` (`dbi_no`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
    
    CONSTRAINT `fk_tbl_buy_dbc_no`
    FOREIGN KEY (`dbc_no`)
    REFERENCES `babyfeed`.`tbl_db_bf_commerce` (`dbc_no`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;



CREATE TABLE IF NOT EXISTS `babyfeed`.`tbl_nutrition_review_comment_like` (
  `nrcl_no` INT NOT NULL AUTO_INCREMENT,
  `member_id` VARCHAR(30) NOT NULL,
  `reg_dt` DATETIME NOT NULL,
  `nrc_id` INT NOT NULL,
  PRIMARY KEY (`nrcl_no`))
ENGINE = InnoDB;


// 식품정보 로그
CREATE TABLE IF NOT EXISTS `babyfeed`.`tbl_log_db_nutrient` (
  `ldn_no` INT NOT NULL AUTO_INCREMENT,
  `dn_no` int(11) NOT NULL,
  `member_id` VARCHAR(30) NOT NULL,
  `reg_dt` DATETIME NOT NULL,
  PRIMARY KEY (`ldn_no`),
  
   CONSTRAINT `fk_tbl_log_bf_nutrient`
    FOREIGN KEY (`dn_no`)
    REFERENCES `babyfeed`.`tbl_db_nutrient` (`dn_no`)
    ON DELETE NO ACTION
	ON UPDATE NO ACTION
    )
ENGINE = InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


INSERT INTO tbl_code_item(order_no, val_1, title, cg_cd, reg_dt, sys_member_id) 
VALUES ('1','dqi_food_1', '모유', 'BF_DQI_FOOD',NOW(),'so2');
INSERT INTO tbl_code_item(order_no, val_1, title, cg_cd, reg_dt, sys_member_id) 
VALUES ('2','dqi_food_2', '분유', 'BF_DQI_FOOD',NOW(),'so2');
INSERT INTO tbl_code_item(order_no, val_1, title, cg_cd, reg_dt, sys_member_id) 
VALUES ('3','dqi_food_3', '유제품류', 'BF_DQI_FOOD',NOW(),'so2');
INSERT INTO tbl_code_item(order_no, val_1, title, cg_cd, reg_dt, sys_member_id) 
VALUES ('4','dqi_food_4', '통곡물', 'BF_DQI_FOOD',NOW(),'so2');
INSERT INTO tbl_code_item(order_no, val_1, title, cg_cd, reg_dt, sys_member_id) 
VALUES ('5','dqi_food_5', '정제된곡물', 'BF_DQI_FOOD',NOW(),'so2');
INSERT INTO tbl_code_item(order_no, val_1, title, cg_cd, reg_dt, sys_member_id) 
VALUES ('6','dqi_food_6', '단백질식품', 'BF_DQI_FOOD',NOW(),'so2');
INSERT INTO tbl_code_item(order_no, val_1, title, cg_cd, reg_dt, sys_member_id) 
VALUES ('7','dqi_food_7', '채소류', 'BF_DQI_FOOD',NOW(),'so2');
INSERT INTO tbl_code_item(order_no, val_1, title, cg_cd, reg_dt, sys_member_id) 
VALUES ('8','dqi_food_8', '과일류', 'BF_DQI_FOOD',NOW(),'so2');
INSERT INTO tbl_code_item(order_no, val_1, title, cg_cd, reg_dt, sys_member_id) 
VALUES ('9','dqi_food_9', '100%과일주스', 'BF_DQI_FOOD',NOW(),'so2');
INSERT INTO tbl_code_item(order_no, val_1, title, cg_cd, reg_dt, sys_member_id) 
VALUES ('10','dqi_food_10', '음료', 'BF_DQI_FOOD',NOW(),'so2');
INSERT INTO tbl_code_item(order_no, val_1, title, cg_cd, reg_dt, sys_member_id) 
VALUES ('11','dqi_food_11', '당함유제품', 'BF_DQI_FOOD',NOW(),'so2');
INSERT INTO tbl_code_item(order_no, val_1, title, cg_cd, reg_dt, sys_member_id) 
VALUES ('12','dqi_food_12', '짠과자', 'BF_DQI_FOOD',NOW(),'so2');


INSERT INTO tbl_code_item_str(order_no, val_1, title, cd_no, reg_dt, sys_member_id,cg_cd) 
VALUES ('1','dqi_food_1', '모유', 'BF_DQI_FOOD_1',NOW(),'so2', 'BF_DQI_FOOD');
INSERT INTO tbl_code_item_str(order_no, val_1, title, cd_no, reg_dt, sys_member_id,cg_cd) 
VALUES ('2','dqi_food_2', '분유', 'BF_DQI_FOOD_2',NOW(),'so2', 'BF_DQI_FOOD');
INSERT INTO tbl_code_item_str(order_no, val_1, title, cd_no, reg_dt, sys_member_id,cg_cd) 
VALUES ('3','dqi_food_3', '유제품류', 'BF_DQI_FOOD_3',NOW(),'so2', 'BF_DQI_FOOD');
INSERT INTO tbl_code_item_str(order_no, val_1, title, cd_no, reg_dt, sys_member_id,cg_cd) 
VALUES ('4','dqi_food_4', '통곡물', 'BF_DQI_FOOD_4',NOW(),'so2', 'BF_DQI_FOOD');
INSERT INTO tbl_code_item_str(order_no, val_1, title, cd_no, reg_dt, sys_member_id,cg_cd) 
VALUES ('5','dqi_food_5', '정제된곡물', 'BF_DQI_FOOD_5',NOW(),'so2', 'BF_DQI_FOOD');
INSERT INTO tbl_code_item_str(order_no, val_1, title, cd_no, reg_dt, sys_member_id,cg_cd) 
VALUES ('6','dqi_food_6', '단백질식품', 'BF_DQI_FOOD_6',NOW(),'so2', 'BF_DQI_FOOD');
INSERT INTO tbl_code_item_str(order_no, val_1, title, cd_no, reg_dt, sys_member_id,cg_cd) 
VALUES ('7','dqi_food_7', '채소류', 'BF_DQI_FOOD_7',NOW(),'so2', 'BF_DQI_FOOD');
INSERT INTO tbl_code_item_str(order_no, val_1, title, cd_no, reg_dt, sys_member_id,cg_cd) 
VALUES ('8','dqi_food_8', '과일류', 'BF_DQI_FOOD_8',NOW(),'so2', 'BF_DQI_FOOD');
INSERT INTO tbl_code_item_str(order_no, val_1, title, cd_no, reg_dt, sys_member_id,cg_cd) 
VALUES ('9','dqi_food_9', '100%과일주스', 'BF_DQI_FOOD_9',NOW(),'so2', 'BF_DQI_FOOD');
INSERT INTO tbl_code_item_str(order_no, val_1, title, cd_no, reg_dt, sys_member_id,cg_cd) 
VALUES ('10','dqi_food_10', '음료', 'BF_DQI_FOOD_10',NOW(),'so2', 'BF_DQI_FOOD');
INSERT INTO tbl_code_item_str(order_no, val_1, title, cd_no, reg_dt, sys_member_id,cg_cd) 
VALUES ('11','dqi_food_11', '당함유제품', 'BF_DQI_FOOD_11',NOW(),'so2', 'BF_DQI_FOOD');
INSERT INTO tbl_code_item_str(order_no, val_1, title, cd_no, reg_dt, sys_member_id,cg_cd) 
VALUES ('12','dqi_food_12', '짠과자', 'BF_DQI_FOOD_12',NOW(),'so2', 'BF_DQI_FOOD');


CREATE TABLE IF NOT EXISTS `babyfeed`.`tbl_db_milk_nutirition2` (
  `mn_no` INT NOT NULL AUTO_INCREMENT,
  `enerc` DECIMAL(10,2) NULL COMMENT '에너지',
  `water` DECIMAL(10,2) NULL COMMENT '수분',
  `procnp` DECIMAL(10,2) NULL COMMENT '단백질',
  `fat` DECIMAL(10,2) NULL COMMENT '지질',
  `ash` DECIMAL(10,2) NULL COMMENT '회분',
  `chotdf` DECIMAL(10,2) NULL COMMENT '탄수화물',
  `sugar` DECIMAL(10,2) NULL COMMENT '총당류',
  `fibtg` DECIMAL(10,2) NULL COMMENT '총 식이섬유',
  `aat19` DECIMAL(10,2) NULL COMMENT '총',
  `aae10a` DECIMAL(10,2) NULL COMMENT '필수',
  `aane` DECIMAL(10,2) NULL COMMENT '비필수',
  `fafref` DECIMAL(10,2) NULL COMMENT '총',
  `faessf` DECIMAL(10,2) NULL COMMENT '총 필수',
  `fasatf` DECIMAL(10,2) NULL COMMENT '총 포화',
  `famsf` DECIMAL(10,2) NULL COMMENT '총 단일 불포화',
  `fapuf` DECIMAL(10,2) NULL COMMENT '총 다중 불포화',
  `chole` DECIMAL(10,2) NULL COMMENT '콜레스테롤',
  `nacl` DECIMAL(10,2) NULL COMMENT '식염상당량',
  `ref` DECIMAL(10,2) NULL COMMENT '폐기율',
  `ca` DECIMAL(10,2) NULL COMMENT '칼슘',
  `fe` DECIMAL(10,2) NULL COMMENT '철',
  `mg` DECIMAL(10,2) NULL COMMENT '마그네슘',
  `p` DECIMAL(10,2) NULL COMMENT '인',
  `k` DECIMAL(10,2) NULL COMMENT '칼륨',
  `na` DECIMAL(10,2) NULL COMMENT '나트륨',
  `zn` DECIMAL(10,2) NULL COMMENT '아연',
  `cu` DECIMAL(10,2) NULL COMMENT '구리',
  `mn` DECIMAL(10,2) NULL COMMENT '망간',
  `se` DECIMAL(10,2) NULL COMMENT '셀레늄',
  `mo` DECIMAL(10,2) NULL COMMENT '몰리브덴',
  `id` DECIMAL(10,2) NULL COMMENT '요오드',
  `retol` DECIMAL(10,2) NULL COMMENT '레티놀',
  `cartb` DECIMAL(10,2) NULL COMMENT '베타카로틴',
  `vita` DECIMAL(10,2) NULL COMMENT '비타민A',
  `vitd` DECIMAL(10,2) NULL COMMENT '비타민D',
  `vite` DECIMAL(10,2) NULL COMMENT '비타민E',
  `vitk1` DECIMAL(10,2) NULL COMMENT '비타민K1',
  `thia` DECIMAL(10,2) NULL COMMENT '비타민B1',
  `ribf` DECIMAL(10,2) NULL COMMENT '비타민B2',
  `nia` DECIMAL(10,2) NULL COMMENT '니아신',
  `pantac` DECIMAL(10,2) NULL COMMENT '판토텐산',
  `pyrxn` DECIMAL(10,2) NULL COMMENT '비타민B6',
  `biot` DECIMAL(10,2) NULL COMMENT '비오틴',
  `fol` DECIMAL(10,2) NULL COMMENT '엽산',
  `vit12` DECIMAL(10,2) NULL COMMENT '비타민B12',
  `vitc` DECIMAL(10,2) NULL COMMENT '비타민C',
  `ci` DECIMAL(10,2) NULL COMMENT '염소',
  `reg_dt` DATETIME NOT NULL,
  `mod_dt` DATETIME NULL,
  `sys_member_id` VARCHAR(30) NOT NULL,
  PRIMARY KEY (`mn_no`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;