CREATE TABLE IF NOT EXISTS `babyfeed`.`tbl_bf_family` (
  `bf_no` INT NOT NULL AUTO_INCREMENT,
  `parent_tp` VARCHAR(30) NOT NULL,
  `name` VARCHAR(30) CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci' NOT NULL,
  `email` VARCHAR(50) NULL,
  `birthday` DATETIME NOT NULL,
  `weight` DECIMAL(4,1) NOT NULL DEFAULT 0,
  `height` DECIMAL(4,1) NOT NULL DEFAULT 0,
  `reg_dt` DATETIME NOT NULL,
  `mod_dt` DATETIME NULL,
  `member_id` VARCHAR(30) NOT NULL,
  PRIMARY KEY (`bf_no`),
  INDEX `fk_tbl_bf_family_tbl_member_idx` (`member_id` ASC),
  CONSTRAINT `fk_tbl_bf_family_tbl_member`
    FOREIGN KEY (`member_id`)
    REFERENCES `babyfeed`.`tbl_member` (`member_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_unicode_ci;


-- -----------------------------------------------------
-- Table `babyfeed`.`tbl_bf_family_disease`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `babyfeed`.`tbl_bf_family_disease` (
  `fd_no` INT NOT NULL AUTO_INCREMENT,
  `disease_cd_no` VARCHAR(30) NOT NULL,
  `bf_no` INT NOT NULL,
  PRIMARY KEY (`fd_no`),
  INDEX `fk_tbl_bf_family_disease_tbl_bf_family1_idx` (`bf_no` ASC),
  CONSTRAINT `fk_tbl_bf_family_disease_tbl_bf_family1`
    FOREIGN KEY (`bf_no`)
    REFERENCES `babyfeed`.`tbl_bf_family` (`bf_no`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS `babyfeed`.`tbl_bf_family_baby` (
  `smc_no` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(30) CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci' NOT NULL,
  `gender` VARCHAR(1) NOT NULL DEFAULT 'M',
  `birthday` DATETIME NOT NULL,
  `weight_birth` DECIMAL(4,1) NULL,
  `height_birth` DECIMAL(4,1) NULL,
  `weight` DECIMAL(4,1) NOT NULL DEFAULT 0,
  `height` DECIMAL(4,1) NOT NULL DEFAULT 0,
  `allergie` VARCHAR(60) NULL,
  `append_src_img_file` BIGINT(20) NULL,
  `append_300_img_file` BIGINT(20) NULL,
  `reg_dt` DATETIME NOT NULL,
  `mod_dt` DATETIME NULL,
  `member_id` VARCHAR(30) NOT NULL,
  PRIMARY KEY (`smc_no`),
  INDEX `fk_tbl_bf_family_baby_tbl_member1_idx` (`member_id` ASC),
  CONSTRAINT `fk_tbl_bf_family_baby_tbl_member1`
    FOREIGN KEY (`member_id`)
    REFERENCES `babyfeed`.`tbl_member` (`member_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS `babyfeed`.`tbl_bf_family_baby_disease` (
  `fbd_no` INT NOT NULL AUTO_INCREMENT,
  `disease_cd_no` VARCHAR(30) NOT NULL,
  `smc_no` INT NOT NULL,
  PRIMARY KEY (`fbd_no`),
  INDEX `fk_tbl_bf_family_baby_disease_tbl_bf_family_baby1_idx` (`smc_no` ASC),
  CONSTRAINT `fk_tbl_bf_family_baby_disease_tbl_bf_family_baby1`
    FOREIGN KEY (`smc_no`)
    REFERENCES `babyfeed`.`tbl_bf_family_baby` (`smc_no`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_unicode_ci;


INSERT tbl_code_group (cg_cd, title, reg_dt, sys_member_id) Values ('BF_PARENT_TP', '부모', now(), 'renamaestro');
INSERT tbl_code_item_str (cd_no, title, order_no, reg_dt, sys_member_id, cg_cd) Values ('BF_P_T_1', '엄마', 1, now(), 'renamaestro', 'BF_PARENT_TP');
INSERT tbl_code_item_str (cd_no, title, order_no, reg_dt, sys_member_id, cg_cd) Values ('BF_P_T_2', '아빠', 2, now(), 'renamaestro', 'BF_PARENT_TP');


INSERT tbl_code_group (cg_cd, title, reg_dt, sys_member_id) Values ('BF_BABY_DISEASE', '부모아기 질환', now(), 'renamaestro');
INSERT tbl_code_item_str (cd_no, title, order_no, reg_dt, sys_member_id, cg_cd) Values ('BF_B_D_1', '아토피', 1, now(), 'renamaestro', 'BF_BABY_DISEASE');
INSERT tbl_code_item_str (cd_no, title, order_no, reg_dt, sys_member_id, cg_cd) Values ('BF_B_D_2', '알레르기', 2, now(), 'renamaestro', 'BF_BABY_DISEASE');
INSERT tbl_code_item_str (cd_no, title, order_no, reg_dt, sys_member_id, cg_cd) Values ('BF_B_D_3', '비염', 3, now(), 'renamaestro', 'BF_BABY_DISEASE');
INSERT tbl_code_item_str (cd_no, title, order_no, reg_dt, sys_member_id, cg_cd) Values ('BF_B_D_4', '천식', 4, now(), 'renamaestro', 'BF_BABY_DISEASE');


CREATE TABLE IF NOT EXISTS `babyfeed`.`tbl_bf_growth_body` (
  `sgb_no` INT NOT NULL AUTO_INCREMENT,
  `member_id` VARCHAR(30) NOT NULL,
  `base_dt` DATETIME NOT NULL,
  `weight` DECIMAL(4,1) NOT NULL DEFAULT 0,
  `height` DECIMAL(4,1) NOT NULL DEFAULT 0,
  `data_from` VARCHAR(1) NOT NULL DEFAULT '0' COMMENT '0:auto 1:hand',
  `reg_dt` DATETIME NOT NULL,
  `mod_dt` DATETIME NULL DEFAULT NULL,
  `smc_no` INT NOT NULL,
  PRIMARY KEY (`sgb_no`, `smc_no`, `base_dt`),
  INDEX `bf_growth_body_smc_no_base_dt` (`smc_no` ASC, `base_dt` ASC))
ENGINE = InnoDB
AUTO_INCREMENT = 1
DEFAULT CHARACTER SET = utf8



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

CREATE TABLE IF NOT EXISTS `babyfeed`.`tbl_bf_family_baby_order` (
  `smc_no` INT(11) NOT NULL,
  `smc_order` INT(11) NULL,
  `reg_dt` DATETIME NOT NULL,
  `mod_dt` DATETIME NULL,
  PRIMARY KEY (`smc_no`))
ENGINE = InnoDB
AUTO_INCREMENT = 6
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_unicode_ci;