keeper_db_init.sql

-- MySQL Script generated by MySQL Workbench
-- Tue Sep 26 11:43:15 2023
-- Model: New Model    Version: 1.0
-- MySQL Workbench Forward Engineering

SET NAMES utf8mb4;


SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

-- -----------------------------------------------------
-- Schema keeper
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema keeper
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `keeper` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ;
USE `keeper` ;

-- -----------------------------------------------------
-- Table `keeper`.`file`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `keeper`.`file` ;

CREATE TABLE IF NOT EXISTS `keeper`.`file` (
                                               `id` INT NOT NULL AUTO_INCREMENT,
                                               `file_name` VARCHAR(256) NOT NULL,
    `file_path` VARCHAR(512) NOT NULL,
    `file_size` BIGINT NOT NULL,
    `upload_time` DATETIME NOT NULL DEFAULT (CURRENT_TIME),
    `ip_address` VARCHAR(128) NOT NULL,
    PRIMARY KEY (`id`))
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `keeper`.`thumbnail`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `keeper`.`thumbnail` ;

CREATE TABLE IF NOT EXISTS `keeper`.`thumbnail` (
                                                    `id` INT NOT NULL AUTO_INCREMENT,
                                                    `path` VARCHAR(512) NULL,
    `file_id` INT NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `fk_thumbnail_file1_idx` (`file_id` ASC) VISIBLE,
    CONSTRAINT `fk_thumbnail_file1`
    FOREIGN KEY (`file_id`)
    REFERENCES `keeper`.`file` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `keeper`.`book_department`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `keeper`.`book_department` ;

CREATE TABLE IF NOT EXISTS `keeper`.`book_department` (
                                                          `id` INT NOT NULL AUTO_INCREMENT,
                                                          `name` VARCHAR(45) NOT NULL,
    PRIMARY KEY (`id`))
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `keeper`.`book`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `keeper`.`book` ;

CREATE TABLE IF NOT EXISTS `keeper`.`book` (
                                               `id` INT NOT NULL AUTO_INCREMENT,
                                               `title` VARCHAR(250) NOT NULL,
    `author` VARCHAR(40) NOT NULL,
    `register_time` DATETIME NOT NULL DEFAULT (CURRENT_TIME),
    `update_time` DATETIME NOT NULL DEFAULT (CURRENT_TIME),
    `book_department_id` INT NOT NULL DEFAULT 6,
    `total_quantity` INT NOT NULL DEFAULT 1,
    `thumbnail_id` INT NULL,
    PRIMARY KEY (`id`),
    INDEX `fk_book_thumbnail1_idx` (`thumbnail_id` ASC) VISIBLE,
    INDEX `fk_book_book_department1_idx` (`book_department_id` ASC) VISIBLE,
    CONSTRAINT `fk_book_thumbnail1`
    FOREIGN KEY (`thumbnail_id`)
    REFERENCES `keeper`.`thumbnail` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
    CONSTRAINT `fk_book_book_department1`
    FOREIGN KEY (`book_department_id`)
    REFERENCES `keeper`.`book_department` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);


-- -----------------------------------------------------
-- Table `keeper`.`equipment`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `keeper`.`equipment` ;

CREATE TABLE IF NOT EXISTS `keeper`.`equipment` (
                                                    `id` INT NOT NULL AUTO_INCREMENT,
                                                    `name` VARCHAR(250) NOT NULL,
    `information` TEXT NULL,
    `total` INT NOT NULL DEFAULT 1,
    `borrow` INT NOT NULL DEFAULT 0,
    `enable` INT NOT NULL DEFAULT 1,
    `register_time` DATETIME NOT NULL DEFAULT (CURRENT_TIME),
    `update_time` DATETIME NOT NULL DEFAULT (CURRENT_TIME),
    `thumbnail_id` INT NULL,
    PRIMARY KEY (`id`),
    INDEX `fk_equipment_thumbnail1_idx` (`thumbnail_id` ASC) VISIBLE,
    CONSTRAINT `fk_equipment_thumbnail1`
    FOREIGN KEY (`thumbnail_id`)
    REFERENCES `keeper`.`thumbnail` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);


-- -----------------------------------------------------
-- Table `keeper`.`member_type`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `keeper`.`member_type` ;

CREATE TABLE IF NOT EXISTS `keeper`.`member_type` (
                                                      `id` INT NOT NULL AUTO_INCREMENT,
                                                      `name` VARCHAR(45) NULL,
    `badge_thumbnail_id` INT NULL,
    PRIMARY KEY (`id`),
    INDEX `fk_member_type_thumbnail1_idx` (`badge_thumbnail_id` ASC) VISIBLE,
    CONSTRAINT `fk_member_type_thumbnail1`
    FOREIGN KEY (`badge_thumbnail_id`)
    REFERENCES `keeper`.`thumbnail` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `keeper`.`member_rank`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `keeper`.`member_rank` ;

CREATE TABLE IF NOT EXISTS `keeper`.`member_rank` (
                                                      `id` INT NOT NULL AUTO_INCREMENT,
                                                      `name` VARCHAR(45) NULL,
    `badge_thumbnail_id` INT NULL,
    PRIMARY KEY (`id`),
    INDEX `fk_member_rank_thumbnail1_idx` (`badge_thumbnail_id` ASC) VISIBLE,
    CONSTRAINT `fk_member_rank_thumbnail1`
    FOREIGN KEY (`badge_thumbnail_id`)
    REFERENCES `keeper`.`thumbnail` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `keeper`.`member`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `keeper`.`member` ;

CREATE TABLE IF NOT EXISTS `keeper`.`member` (
                                                 `id` INT NOT NULL AUTO_INCREMENT,
                                                 `login_id` VARCHAR(80) NOT NULL,
    `email_address` VARCHAR(250) NOT NULL,
    `password` VARCHAR(512) NOT NULL,
    `real_name` VARCHAR(40) NOT NULL,
    `birthday` DATE NULL,
    `student_id` VARCHAR(45) NULL,
    `register_time` DATETIME NOT NULL DEFAULT (CURRENT_TIME),
    `update_time` DATETIME NOT NULL DEFAULT (CURRENT_TIME),
    `member_type_id` INT NULL DEFAULT 1,
    `member_rank_id` INT NULL DEFAULT 1,
    `point` INT NOT NULL DEFAULT 0,
    `level` INT NOT NULL DEFAULT 0,
    `thumbnail_id` INT NULL,
    `generation` FLOAT NULL,
    `total_attendance` INT NOT NULL DEFAULT 0,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `login_id_UNIQUE` (`login_id` ASC) VISIBLE,
    UNIQUE INDEX `email_address_UNIQUE` (`email_address` ASC) VISIBLE,
    UNIQUE INDEX `student_id_UNIQUE` (`student_id` ASC) VISIBLE,
    INDEX `fk_member_member_type1_idx` (`member_type_id` ASC) VISIBLE,
    INDEX `fk_member_member_rank1_idx` (`member_rank_id` ASC) VISIBLE,
    INDEX `fk_member_thumbnail1_idx` (`thumbnail_id` ASC) VISIBLE,
    CONSTRAINT `fk_member_member_type1`
    FOREIGN KEY (`member_type_id`)
    REFERENCES `keeper`.`member_type` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
    CONSTRAINT `fk_member_member_rank1`
    FOREIGN KEY (`member_rank_id`)
    REFERENCES `keeper`.`member_rank` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
    CONSTRAINT `fk_member_thumbnail1`
    FOREIGN KEY (`thumbnail_id`)
    REFERENCES `keeper`.`thumbnail` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `keeper`.`book_borrow_status`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `keeper`.`book_borrow_status` ;

CREATE TABLE IF NOT EXISTS `keeper`.`book_borrow_status` (
                                                             `id` INT NOT NULL AUTO_INCREMENT,
                                                             `status` VARCHAR(20) NOT NULL,
    PRIMARY KEY (`id`))
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `keeper`.`book_borrow_info`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `keeper`.`book_borrow_info` ;

CREATE TABLE IF NOT EXISTS `keeper`.`book_borrow_info` (
                                                           `id` INT UNIQUE NOT NULL AUTO_INCREMENT,
                                                           `member_id` INT NOT NULL,
                                                           `book_id` INT NOT NULL,
                                                           `status_id` INT NOT NULL,
                                                           `borrow_date` DATE NULL,
                                                           `expire_date` DATE NULL,
                                                           `register_time` DATETIME NOT NULL DEFAULT (CURRENT_TIME),
    `update_time` DATETIME NOT NULL DEFAULT (CURRENT_TIME),
    `last_request_date` DATE NULL DEFAULT (CURRENT_DATE),
    PRIMARY KEY (`id`),
    INDEX `fk_book_borrow_info_book1_idx` (`book_id` ASC) VISIBLE,
    INDEX `fk_book_borrow_info_member1_idx` (`member_id` ASC) VISIBLE,
    INDEX `fk_book_borrow_info_book_borrow_status1_idx` (`status_id` ASC) VISIBLE,
    CONSTRAINT `fk_book_borrow_info_book1`
    FOREIGN KEY (`book_id`)
    REFERENCES `keeper`.`book` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
    CONSTRAINT `fk_book_borrow_info_member1`
    FOREIGN KEY (`member_id`)
    REFERENCES `keeper`.`member` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
    CONSTRAINT `fk_book_borrow_info_book_borrow_status1`
    FOREIGN KEY (`status_id`)
    REFERENCES `keeper`.`book_borrow_status` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);


-- -----------------------------------------------------
-- Table `keeper`.`equipment_borrow_info`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `keeper`.`equipment_borrow_info` ;

CREATE TABLE IF NOT EXISTS `keeper`.`equipment_borrow_info` (
                                                                `id` INT UNIQUE NOT NULL AUTO_INCREMENT,
                                                                `quantity` INT NOT NULL,
                                                                `borrow_date` DATE NOT NULL DEFAULT (CURRENT_DATE),
    `expire_date` DATE NOT NULL DEFAULT (CURRENT_DATE),
    `member_id` INT NOT NULL,
    `equipment_id` INT NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `fk_equipment_borrow_info_equipment1_idx` (`equipment_id` ASC) VISIBLE,
    INDEX `fk_equipment_borrow_info_member1_idx` (`member_id` ASC) VISIBLE,
    CONSTRAINT `fk_equipment_borrow_info_equipment1`
    FOREIGN KEY (`equipment_id`)
    REFERENCES `keeper`.`equipment` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
    CONSTRAINT `fk_equipment_borrow_info_member1`
    FOREIGN KEY (`member_id`)
    REFERENCES `keeper`.`member` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);


-- -----------------------------------------------------
-- Table `keeper`.`category`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `keeper`.`category` ;

CREATE TABLE IF NOT EXISTS `keeper`.`category` (
                                                   `id` INT NOT NULL AUTO_INCREMENT,
                                                   `name` VARCHAR(250) NOT NULL,
    PRIMARY KEY (`id`))
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `keeper`.`posting`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `keeper`.`posting` ;

CREATE TABLE IF NOT EXISTS `keeper`.`posting` (
                                                  `id` INT NOT NULL AUTO_INCREMENT,
                                                  `title` VARCHAR(250) NOT NULL,
    `content` TEXT NOT NULL,
    `member_id` INT NOT NULL,
    `visit_count` INT NOT NULL DEFAULT 0,
    `register_time` DATETIME NOT NULL DEFAULT (CURRENT_TIME),
    `update_time` DATETIME NOT NULL DEFAULT (CURRENT_TIME),
    `ip_address` VARCHAR(128) NOT NULL,
    `allow_comment` TINYINT NOT NULL DEFAULT 1,
    `is_notice` TINYINT NOT NULL DEFAULT 0,
    `is_secret` TINYINT NOT NULL DEFAULT 0,
    `is_temp` TINYINT NOT NULL DEFAULT 0,
    `password` VARCHAR(512) NULL,
    `category_id` INT NOT NULL,
    `thumbnail_id` INT NULL,
    PRIMARY KEY (`id`),
    INDEX `fk_posting_member1_idx` (`member_id` ASC) VISIBLE,
    INDEX `fk_posting_category1_idx` (`category_id` ASC) VISIBLE,
    INDEX `fk_posting_thumbnail1_idx` (`thumbnail_id` ASC) VISIBLE,
    CONSTRAINT `fk_posting_member1`
    FOREIGN KEY (`member_id`)
    REFERENCES `keeper`.`member` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
    CONSTRAINT `fk_posting_category1`
    FOREIGN KEY (`category_id`)
    REFERENCES `keeper`.`category` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
    CONSTRAINT `fk_posting_thumbnail1`
    FOREIGN KEY (`thumbnail_id`)
    REFERENCES `keeper`.`thumbnail` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `keeper`.`comment`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `keeper`.`comment` ;

CREATE TABLE IF NOT EXISTS `keeper`.`comment` (
                                                  `id` INT NOT NULL AUTO_INCREMENT,
                                                  `content` TEXT NOT NULL,
                                                  `register_time` DATETIME NOT NULL DEFAULT (CURRENT_TIME),
    `update_time` DATETIME NOT NULL DEFAULT (CURRENT_TIME),
    `ip_address` VARCHAR(128) NOT NULL,
    `member_id` INT NOT NULL,
    `posting_id` INT NOT NULL,
    `parent_id` INT NULL,
    `is_deleted` TINYINT NULL DEFAULT 0,
    PRIMARY KEY (`id`),
    INDEX `fk_comment_member1_idx` (`member_id` ASC) VISIBLE,
    INDEX `fk_comment_posting1_idx` (`posting_id` ASC) VISIBLE,
    INDEX `fk_comment_comment1_idx` (`parent_id` ASC) VISIBLE,
    CONSTRAINT `fk_comment_member1`
    FOREIGN KEY (`member_id`)
    REFERENCES `keeper`.`member` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
    CONSTRAINT `fk_comment_posting1`
    FOREIGN KEY (`posting_id`)
    REFERENCES `keeper`.`posting` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
    CONSTRAINT `fk_comment_comment1`
    FOREIGN KEY (`parent_id`)
    REFERENCES `keeper`.`comment` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `keeper`.`member_job`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `keeper`.`member_job` ;

CREATE TABLE IF NOT EXISTS `keeper`.`member_job` (
                                                     `id` INT NOT NULL AUTO_INCREMENT,
                                                     `name` VARCHAR(45) NULL,
    PRIMARY KEY (`id`))
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `keeper`.`member_has_member_job`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `keeper`.`member_has_member_job` ;

CREATE TABLE IF NOT EXISTS `keeper`.`member_has_member_job` (
                                                                `id` INT NOT NULL AUTO_INCREMENT,
                                                                `member_id` INT NOT NULL,
                                                                `member_job_id` INT NOT NULL,
                                                                INDEX `fk_member_has_member_job_member_job1_idx` (`member_job_id` ASC) VISIBLE,
    INDEX `fk_member_has_member_job_member1_idx` (`member_id` ASC) VISIBLE,
    PRIMARY KEY (`id`),
    CONSTRAINT `fk_member_has_member_job_member1`
    FOREIGN KEY (`member_id`)
    REFERENCES `keeper`.`member` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
    CONSTRAINT `fk_member_has_member_job_member_job1`
    FOREIGN KEY (`member_job_id`)
    REFERENCES `keeper`.`member_job` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `keeper`.`member_has_posting_like`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `keeper`.`member_has_posting_like` ;

CREATE TABLE IF NOT EXISTS `keeper`.`member_has_posting_like` (
                                                                  `member_id` INT NOT NULL,
                                                                  `posting_id` INT NOT NULL,
                                                                  PRIMARY KEY (`member_id`, `posting_id`),
    INDEX `fk_member_has_posting_posting1_idx` (`posting_id` ASC) VISIBLE,
    INDEX `fk_member_has_posting_member1_idx` (`member_id` ASC) VISIBLE,
    CONSTRAINT `fk_member_has_posting_member1`
    FOREIGN KEY (`member_id`)
    REFERENCES `keeper`.`member` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
    CONSTRAINT `fk_member_has_posting_posting1`
    FOREIGN KEY (`posting_id`)
    REFERENCES `keeper`.`posting` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `keeper`.`member_has_posting_dislike`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `keeper`.`member_has_posting_dislike` ;

CREATE TABLE IF NOT EXISTS `keeper`.`member_has_posting_dislike` (
                                                                     `member_id` INT NOT NULL,
                                                                     `posting_id` INT NOT NULL,
                                                                     PRIMARY KEY (`member_id`, `posting_id`),
    INDEX `fk_member_has_posting1_posting1_idx` (`posting_id` ASC) VISIBLE,
    INDEX `fk_member_has_posting1_member1_idx` (`member_id` ASC) VISIBLE,
    CONSTRAINT `fk_member_has_posting1_member1`
    FOREIGN KEY (`member_id`)
    REFERENCES `keeper`.`member` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
    CONSTRAINT `fk_member_has_posting1_posting1`
    FOREIGN KEY (`posting_id`)
    REFERENCES `keeper`.`posting` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `keeper`.`member_has_comment_like`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `keeper`.`member_has_comment_like` ;

CREATE TABLE IF NOT EXISTS `keeper`.`member_has_comment_like` (
                                                                  `member_id` INT NOT NULL,
                                                                  `comment_id` INT NOT NULL,
                                                                  PRIMARY KEY (`member_id`, `comment_id`),
    INDEX `fk_member_has_comment_comment1_idx` (`comment_id` ASC) VISIBLE,
    INDEX `fk_member_has_comment_member1_idx` (`member_id` ASC) VISIBLE,
    CONSTRAINT `fk_member_has_comment_member1`
    FOREIGN KEY (`member_id`)
    REFERENCES `keeper`.`member` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
    CONSTRAINT `fk_member_has_comment_comment1`
    FOREIGN KEY (`comment_id`)
    REFERENCES `keeper`.`comment` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `keeper`.`member_has_comment_dislike`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `keeper`.`member_has_comment_dislike` ;

CREATE TABLE IF NOT EXISTS `keeper`.`member_has_comment_dislike` (
                                                                     `member_id` INT NOT NULL,
                                                                     `comment_id` INT NOT NULL,
                                                                     PRIMARY KEY (`member_id`, `comment_id`),
    INDEX `fk_member_has_comment1_comment1_idx` (`comment_id` ASC) VISIBLE,
    INDEX `fk_member_has_comment1_member1_idx` (`member_id` ASC) VISIBLE,
    CONSTRAINT `fk_member_has_comment1_member1`
    FOREIGN KEY (`member_id`)
    REFERENCES `keeper`.`member` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
    CONSTRAINT `fk_member_has_comment1_comment1`
    FOREIGN KEY (`comment_id`)
    REFERENCES `keeper`.`comment` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `keeper`.`static_write_title`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `keeper`.`static_write_title` ;

CREATE TABLE IF NOT EXISTS `keeper`.`static_write_title` (
                                                             `id` INT NOT NULL AUTO_INCREMENT,
                                                             `title` VARCHAR(50) NULL,
    `type` VARCHAR(40) NULL,
    PRIMARY KEY (`id`))
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `keeper`.`static_write_subtitle_image`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `keeper`.`static_write_subtitle_image` ;

CREATE TABLE IF NOT EXISTS `keeper`.`static_write_subtitle_image` (
                                                                      `id` INT NOT NULL AUTO_INCREMENT,
                                                                      `subtitle` VARCHAR(250) NULL,
    `static_write_title_id` INT NOT NULL,
    `thumbnail_id` INT NULL,
    `display_order` INT NOT NULL DEFAULT 0,
    PRIMARY KEY (`id`),
    INDEX `fk_static_write_subtitle_image_static_write_title1_idx` (`static_write_title_id` ASC) VISIBLE,
    INDEX `fk_static_write_subtitle_image_thumbnail1_idx` (`thumbnail_id` ASC) VISIBLE,
    CONSTRAINT `fk_static_write_subtitle_image_static_write_title1`
    FOREIGN KEY (`static_write_title_id`)
    REFERENCES `keeper`.`static_write_title` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
    CONSTRAINT `fk_static_write_subtitle_image_thumbnail1`
    FOREIGN KEY (`thumbnail_id`)
    REFERENCES `keeper`.`thumbnail` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `keeper`.`static_write_content`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `keeper`.`static_write_content` ;

CREATE TABLE IF NOT EXISTS `keeper`.`static_write_content` (
                                                               `id` INT NOT NULL AUTO_INCREMENT,
                                                               `content` TEXT NULL,
                                                               `static_write_subtitle_image_id` INT NOT NULL,
                                                               `display_order` INT NOT NULL DEFAULT 0,
                                                               PRIMARY KEY (`id`),
    INDEX `fk_static_write_content_static_write_subtitle_image1_idx` (`static_write_subtitle_image_id` ASC) VISIBLE,
    CONSTRAINT `fk_static_write_content_static_write_subtitle_image1`
    FOREIGN KEY (`static_write_subtitle_image_id`)
    REFERENCES `keeper`.`static_write_subtitle_image` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `keeper`.`attendance`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `keeper`.`attendance` ;

CREATE TABLE IF NOT EXISTS `keeper`.`attendance` (
                                                     `id` INT NOT NULL AUTO_INCREMENT,
                                                     `time` DATETIME NOT NULL DEFAULT (CURRENT_TIME),
    `date` DATE NOT NULL DEFAULT (CURRENT_DATE),
    `point` INT NOT NULL DEFAULT 100,
    `random_point` INT NOT NULL DEFAULT 0,
    `rank_point` INT NOT NULL DEFAULT 0,
    `continuous_point` INT NOT NULL DEFAULT 0,
    `ip_address` VARCHAR(128) NOT NULL,
    `greetings` VARCHAR(250) NULL,
    `continuous_day` INT NOT NULL,
    `member_id` INT NOT NULL,
    `rank` INT NULL,
    PRIMARY KEY (`id`),
    INDEX `fk_attendance_member1_idx` (`member_id` ASC) VISIBLE,
    UNIQUE INDEX `is_duplicated` (`member_id` ASC, `date` ASC) VISIBLE,
    CONSTRAINT `fk_attendance_member1`
    FOREIGN KEY (`member_id`)
    REFERENCES `keeper`.`member` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `keeper`.`non_absence`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `keeper`.`non_absence` ;

CREATE TABLE IF NOT EXISTS `keeper`.`non_absence` (
                                                      `id` INT NOT NULL AUTO_INCREMENT,
                                                      `term` VARCHAR(45) NOT NULL,
    `day` INT NOT NULL,
    `point` INT NOT NULL DEFAULT 0,
    PRIMARY KEY (`id`))
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `keeper`.`friend`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `keeper`.`friend` ;

CREATE TABLE IF NOT EXISTS `keeper`.`friend` (
                                                 `id` INT NOT NULL AUTO_INCREMENT,
                                                 `follower` INT NOT NULL,
                                                 `followee` INT NOT NULL,
                                                 `register_time` DATETIME NOT NULL DEFAULT (CURRENT_TIME),
    PRIMARY KEY (`id`),
    INDEX `fk_friend_member1_idx` (`follower` ASC) VISIBLE,
    INDEX `fk_friend_member2_idx` (`followee` ASC) VISIBLE,
    CONSTRAINT `fk_friend_member1`
    FOREIGN KEY (`follower`)
    REFERENCES `keeper`.`member` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
    CONSTRAINT `fk_friend_member2`
    FOREIGN KEY (`followee`)
    REFERENCES `keeper`.`member` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `keeper`.`point_log`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `keeper`.`point_log` ;

CREATE TABLE IF NOT EXISTS `keeper`.`point_log` (
                                                    `id` INT NOT NULL AUTO_INCREMENT,
                                                    `time` DATETIME NOT NULL DEFAULT (CURRENT_TIME),
    `member_id` INT NOT NULL,
    `point` INT NOT NULL DEFAULT 0,
    `detail` VARCHAR(45) NULL,
    `presented` INT NULL,
    `is_spent` TINYINT NOT NULL DEFAULT 1,
    PRIMARY KEY (`id`),
    INDEX `fk_point_log_member1_idx` (`presented` ASC) VISIBLE,
    INDEX `fk_point_log_member2_idx` (`member_id` ASC) VISIBLE,
    CONSTRAINT `fk_point_log_member1`
    FOREIGN KEY (`presented`)
    REFERENCES `keeper`.`member` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
    CONSTRAINT `fk_point_log_member2`
    FOREIGN KEY (`member_id`)
    REFERENCES `keeper`.`member` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `keeper`.`game_member_info`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `keeper`.`game_member_info` ;

CREATE TABLE IF NOT EXISTS `keeper`.`game_member_info` (
                                                           `id` INT NOT NULL AUTO_INCREMENT,
                                                           `member_id` INT NOT NULL,
                                                           `dice_per_day` INT NOT NULL DEFAULT 0,
                                                           `roulette_per_day` INT NOT NULL DEFAULT 0,
                                                           `lotto_per_day` INT NOT NULL DEFAULT 0,
                                                           `baseball_per_day` INT NOT NULL DEFAULT 0,
                                                           `last_play_time` DATETIME NULL DEFAULT (CURRENT_TIME),
    `dice_day_point` INT NOT NULL DEFAULT 0,
    `roulette_day_point` INT NOT NULL DEFAULT 0,
    `lotto_day_point` INT NOT NULL DEFAULT 0,
    `baseball_day_point` INT NOT NULL DEFAULT 0,
    `play_date` DATE NOT NULL DEFAULT (CURRENT_DATE),
    PRIMARY KEY (`id`),
    INDEX `fk_game_member_info_member1_idx` (`member_id` ASC) VISIBLE,
    UNIQUE INDEX `is_duplicated` (`member_id` ASC, `play_date` DESC) VISIBLE,
    CONSTRAINT `fk_game_member_info_member1`
    FOREIGN KEY (`member_id`)
    REFERENCES `keeper`.`member` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `keeper`.`study`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `keeper`.`study` ;

CREATE TABLE IF NOT EXISTS `keeper`.`study` (
                                                `id` INT NOT NULL AUTO_INCREMENT,
                                                `title` VARCHAR(45) NOT NULL,
    `information` VARCHAR(256) NOT NULL,
    `head_member_id` INT NOT NULL,
    `thumbnail_id` INT NOT NULL DEFAULT 1,
    `register_time` DATETIME NOT NULL DEFAULT (CURRENT_TIME),
    `update_time` DATETIME NOT NULL DEFAULT (CURRENT_TIME),
    `year` INT NULL,
    `season` INT NULL,
    `git_link` VARCHAR(256) NULL,
    `notion_link` VARCHAR(256) NULL,
    `etc_link` VARCHAR(256) NULL,
    `etc_title` VARCHAR(45) NULL,
    PRIMARY KEY (`id`),
    INDEX `fk_study_thumbnail1_idx` (`thumbnail_id` ASC) VISIBLE,
    INDEX `fk_study_member1_idx` (`head_member_id` ASC) VISIBLE,
    CONSTRAINT `fk_study_thumbnail1`
    FOREIGN KEY (`thumbnail_id`)
    REFERENCES `keeper`.`thumbnail` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
    CONSTRAINT `fk_study_member1`
    FOREIGN KEY (`head_member_id`)
    REFERENCES `keeper`.`member` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `keeper`.`study_has_member`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `keeper`.`study_has_member` ;

CREATE TABLE IF NOT EXISTS `keeper`.`study_has_member` (
                                                           `study_id` INT NOT NULL,
                                                           `member_id` INT NOT NULL,
                                                           `register_time` DATETIME(6) NOT NULL DEFAULT (CURRENT_TIME),
    PRIMARY KEY (`study_id`, `member_id`),
    INDEX `fk_study_has_member_member1_idx` (`member_id` ASC) VISIBLE,
    INDEX `fk_study_has_member_study1_idx` (`study_id` ASC) VISIBLE,
    CONSTRAINT `fk_study_has_member_study1`
    FOREIGN KEY (`study_id`)
    REFERENCES `keeper`.`study` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
    CONSTRAINT `fk_study_has_member_member1`
    FOREIGN KEY (`member_id`)
    REFERENCES `keeper`.`member` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `keeper`.`ctf_contest`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `keeper`.`ctf_contest` ;

CREATE TABLE IF NOT EXISTS `keeper`.`ctf_contest` (
                                                      `id` INT NOT NULL AUTO_INCREMENT,
                                                      `name` VARCHAR(45) NOT NULL DEFAULT 'DEFAULT_CONTEST_NAME',
    `description` VARCHAR(45) NULL,
    `register_time` DATETIME NOT NULL DEFAULT (CURRENT_TIME),
    `update_time` DATETIME NOT NULL DEFAULT (CURRENT_TIME),
    `creator` INT NOT NULL DEFAULT 1,
    `is_joinable` TINYINT NOT NULL DEFAULT 0,
    PRIMARY KEY (`id`),
    INDEX `fk_ctf_contest_member1_idx` (`creator` ASC) VISIBLE,
    CONSTRAINT `fk_ctf_contest_member1`
    FOREIGN KEY (`creator`)
    REFERENCES `keeper`.`member` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `keeper`.`ctf_team`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `keeper`.`ctf_team` ;

CREATE TABLE IF NOT EXISTS `keeper`.`ctf_team` (
                                                   `id` INT NOT NULL AUTO_INCREMENT,
                                                   `name` VARCHAR(45) NOT NULL DEFAULT 'DEFAULT_TEAM_NAME',
    `description` VARCHAR(200) NULL,
    `register_time` DATETIME NOT NULL DEFAULT (CURRENT_TIME),
    `update_time` DATETIME NOT NULL DEFAULT (CURRENT_TIME),
    `creator` INT NOT NULL DEFAULT 1,
    `score` INT NOT NULL DEFAULT 0,
    `contest_id` INT NOT NULL DEFAULT 1,
    `last_solve_time` DATETIME NOT NULL DEFAULT (CURRENT_TIME),
    PRIMARY KEY (`id`),
    INDEX `fk_ctf_team_member1_idx` (`creator` ASC) VISIBLE,
    INDEX `fk_ctf_team_ctf_contest1_idx` (`contest_id` ASC) VISIBLE,
    UNIQUE INDEX `is_duplicated` (`name` ASC, `contest_id` ASC) VISIBLE,
    CONSTRAINT `fk_ctf_team_member1`
    FOREIGN KEY (`creator`)
    REFERENCES `keeper`.`member` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
    CONSTRAINT `fk_ctf_team_ctf_contest1`
    FOREIGN KEY (`contest_id`)
    REFERENCES `keeper`.`ctf_contest` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `keeper`.`ctf_challenge_type`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `keeper`.`ctf_challenge_type` ;

CREATE TABLE IF NOT EXISTS `keeper`.`ctf_challenge_type` (
                                                             `id` INT NOT NULL AUTO_INCREMENT,
                                                             `name` VARCHAR(45) NOT NULL,
    PRIMARY KEY (`id`))
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `keeper`.`ctf_challenge`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `keeper`.`ctf_challenge` ;

CREATE TABLE IF NOT EXISTS `keeper`.`ctf_challenge` (
                                                        `id` INT NOT NULL AUTO_INCREMENT,
                                                        `name` VARCHAR(200) NOT NULL DEFAULT 'DEFAULT_CHALLENGE_NAME',
    `description` TEXT NULL,
    `register_time` DATETIME NOT NULL DEFAULT (CURRENT_TIME),
    `update_time` DATETIME NOT NULL DEFAULT (CURRENT_TIME),
    `creator` INT NOT NULL DEFAULT 1,
    `is_solvable` TINYINT NOT NULL DEFAULT 0,
    `type_id` INT NULL,
    `score` INT NOT NULL DEFAULT 0,
    `contest_id` INT NOT NULL DEFAULT 1,
    `max_submit_count` INT NOT NULL DEFAULT 1,
    PRIMARY KEY (`id`),
    INDEX `fk_ctf_challenge_ctf_challenge_type1_idx` (`type_id` ASC) VISIBLE,
    INDEX `fk_ctf_challenge_ctf_contest1_idx` (`contest_id` ASC) VISIBLE,
    INDEX `fk_ctf_challenge_member1_idx` (`creator` ASC) VISIBLE,
    CONSTRAINT `fk_ctf_challenge_ctf_challenge_type1`
    FOREIGN KEY (`type_id`)
    REFERENCES `keeper`.`ctf_challenge_type` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
    CONSTRAINT `fk_ctf_challenge_member1`
    FOREIGN KEY (`creator`)
    REFERENCES `keeper`.`member` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
    CONSTRAINT `fk_ctf_challenge_ctf_contest1`
    FOREIGN KEY (`contest_id`)
    REFERENCES `keeper`.`ctf_contest` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `keeper`.`ctf_challenge_category`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `keeper`.`ctf_challenge_category` ;

CREATE TABLE IF NOT EXISTS `keeper`.`ctf_challenge_category` (
                                                                 `id` INT NOT NULL AUTO_INCREMENT,
                                                                 `name` VARCHAR(45) NOT NULL,
    PRIMARY KEY (`id`))
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `keeper`.`ctf_team_has_member`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `keeper`.`ctf_team_has_member` ;

CREATE TABLE IF NOT EXISTS `keeper`.`ctf_team_has_member` (
                                                              `ctf_team_id` INT NOT NULL,
                                                              `member_id` INT NOT NULL,
                                                              PRIMARY KEY (`ctf_team_id`, `member_id`),
    INDEX `fk_ctf_team_has_member_member1_idx` (`member_id` ASC) VISIBLE,
    INDEX `fk_ctf_team_has_member_ctf_team1_idx` (`ctf_team_id` ASC) VISIBLE,
    CONSTRAINT `fk_ctf_team_has_member_ctf_team1`
    FOREIGN KEY (`ctf_team_id`)
    REFERENCES `keeper`.`ctf_team` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
    CONSTRAINT `fk_ctf_team_has_member_member1`
    FOREIGN KEY (`member_id`)
    REFERENCES `keeper`.`member` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `keeper`.`ctf_flag`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `keeper`.`ctf_flag` ;

CREATE TABLE IF NOT EXISTS `keeper`.`ctf_flag` (
                                                   `id` INT NOT NULL AUTO_INCREMENT,
                                                   `content` VARCHAR(200) NOT NULL DEFAULT 'DEFAULT_FLAG_CONTENT',
    `team_id` INT NOT NULL DEFAULT 1,
    `challenge_id` INT NOT NULL DEFAULT 1,
    `is_correct` TINYINT NOT NULL DEFAULT 0,
    `solved_time` DATETIME NULL,
    `last_try_time` DATETIME NULL,
    `remained_submit_count` INT NOT NULL DEFAULT 1,
    PRIMARY KEY (`id`),
    INDEX `fk_ctf_flag_ctf_team1_idx` (`team_id` ASC) INVISIBLE,
    INDEX `fk_ctf_flag_ctf_challenge1_idx` (`challenge_id` ASC) VISIBLE,
    CONSTRAINT `fk_ctf_flag_ctf_team1`
    FOREIGN KEY (`team_id`)
    REFERENCES `keeper`.`ctf_team` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
    CONSTRAINT `fk_ctf_flag_ctf_challenge1`
    FOREIGN KEY (`challenge_id`)
    REFERENCES `keeper`.`ctf_challenge` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `keeper`.`ctf_submit_log`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `keeper`.`ctf_submit_log` ;

CREATE TABLE IF NOT EXISTS `keeper`.`ctf_submit_log` (
                                                         `id` INT NOT NULL AUTO_INCREMENT,
                                                         `submit_time` DATETIME NOT NULL DEFAULT (CURRENT_TIME),
    `flag_submitted` VARCHAR(200) NULL DEFAULT 'DEFAULT_FLAG_SUBMITTED',
    `is_correct` TINYINT NOT NULL DEFAULT 0,
    `team_name` VARCHAR(45) NOT NULL DEFAULT 'DEFAULT_TEAM_NAME',
    `submitter_login_id` VARCHAR(80) NOT NULL DEFAULT 'DEFAULT_SUBMITTER_LOGIN_ID',
    `submitter_realname` VARCHAR(45) NOT NULL DEFAULT 'DEFAULT_SUBMITTER_REALNAME',
    `challenge_name` VARCHAR(200) NOT NULL DEFAULT 'DEFAULT_CHALLENGE_NAME',
    `contest_name` VARCHAR(45) NOT NULL DEFAULT 'DEFAULT_CONTEST_NAME',
    `ctf_contest_id` INT NOT NULL DEFAULT 1,
    PRIMARY KEY (`id`),
    INDEX `fk_ctf_submit_log_ctf_contest1_idx` (`ctf_contest_id` ASC) VISIBLE,
    CONSTRAINT `fk_ctf_submit_log_ctf_contest1`
    FOREIGN KEY (`ctf_contest_id`)
    REFERENCES `keeper`.`ctf_contest` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `keeper`.`ctf_dynamic_challenge_info`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `keeper`.`ctf_dynamic_challenge_info` ;

CREATE TABLE IF NOT EXISTS `keeper`.`ctf_dynamic_challenge_info` (
                                                                     `challenge_id` INT NOT NULL DEFAULT 1,
                                                                     `min_score` INT NOT NULL DEFAULT 1,
                                                                     `max_score` INT NOT NULL DEFAULT 100,
                                                                     PRIMARY KEY (`challenge_id`),
    INDEX `fk_ctf_dynamic_challenge_info_ctf_challenge1_idx` (`challenge_id` ASC) VISIBLE,
    CONSTRAINT `fk_ctf_dynamic_challenge_info_ctf_challenge1`
    FOREIGN KEY (`challenge_id`)
    REFERENCES `keeper`.`ctf_challenge` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `keeper`.`election`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `keeper`.`election` ;

CREATE TABLE IF NOT EXISTS `keeper`.`election` (
                                                   `id` INT NOT NULL AUTO_INCREMENT,
                                                   `name` VARCHAR(45) NOT NULL,
    `description` VARCHAR(200) NULL,
    `creator` INT NOT NULL,
    `register_time` DATETIME NOT NULL DEFAULT (CURRENT_TIME),
    `is_available` TINYINT NOT NULL DEFAULT 0,
    PRIMARY KEY (`id`),
    INDEX `fk_election_member1_idx` (`creator` ASC) VISIBLE,
    CONSTRAINT `fk_election_member1`
    FOREIGN KEY (`creator`)
    REFERENCES `keeper`.`member` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `keeper`.`election_voter`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `keeper`.`election_voter` ;

CREATE TABLE IF NOT EXISTS `keeper`.`election_voter` (
                                                         `voter_id` INT NOT NULL DEFAULT 1,
                                                         `election_id` INT NOT NULL DEFAULT 1,
                                                         `is_voted` TINYINT NOT NULL DEFAULT 0,
                                                         PRIMARY KEY (`election_id`, `voter_id`),
    INDEX `fk_election_voter_election1_idx` (`election_id` ASC) VISIBLE,
    CONSTRAINT `fk_election_voter_member1`
    FOREIGN KEY (`voter_id`)
    REFERENCES `keeper`.`member` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
    CONSTRAINT `fk_election_voter_election1`
    FOREIGN KEY (`election_id`)
    REFERENCES `keeper`.`election` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `keeper`.`election_candidate`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `keeper`.`election_candidate` ;

CREATE TABLE IF NOT EXISTS `keeper`.`election_candidate` (
                                                             `id` INT NOT NULL AUTO_INCREMENT,
                                                             `election_id` INT NOT NULL DEFAULT 1,
                                                             `candidate_id` INT NOT NULL DEFAULT 1,
                                                             `member_job_id` INT NOT NULL DEFAULT 9,
                                                             `description` VARCHAR(200) NULL,
    `register_time` DATETIME NOT NULL DEFAULT (CURRENT_TIME),
    `vote_count` INT NOT NULL DEFAULT 0,
    PRIMARY KEY (`id`),
    INDEX `fk_election_candidate_member_job1_idx` (`member_job_id` ASC) VISIBLE,
    INDEX `fk_election_candidate_member1_idx` (`candidate_id` ASC) VISIBLE,
    INDEX `fk_election_candidate_election1_idx` (`election_id` ASC) VISIBLE,
    CONSTRAINT `fk_election_candidate_member_job1`
    FOREIGN KEY (`member_job_id`)
    REFERENCES `keeper`.`member_job` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
    CONSTRAINT `fk_election_candidate_member1`
    FOREIGN KEY (`candidate_id`)
    REFERENCES `keeper`.`member` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
    CONSTRAINT `fk_election_candidate_election1`
    FOREIGN KEY (`election_id`)
    REFERENCES `keeper`.`election` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `keeper`.`election_chart_log`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `keeper`.`election_chart_log` ;

CREATE TABLE IF NOT EXISTS `keeper`.`election_chart_log` (
                                                             `id` INT NOT NULL AUTO_INCREMENT,
                                                             `vote_time` DATETIME NOT NULL DEFAULT (CURRENT_TIME),
    `election_candidate_id` INT NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `fk_election_chart_log_election_candidate1_idx` (`election_candidate_id` ASC) VISIBLE,
    CONSTRAINT `fk_election_chart_log_election_candidate1`
    FOREIGN KEY (`election_candidate_id`)
    REFERENCES `keeper`.`election_candidate` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `keeper`.`survey`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `keeper`.`survey` ;

CREATE TABLE IF NOT EXISTS `keeper`.`survey` (
                                                 `id` INT NOT NULL AUTO_INCREMENT,
                                                 `open_time` DATETIME NOT NULL DEFAULT (CURRENT_TIME),
    `close_time` DATETIME NULL,
    `name` VARCHAR(100) NULL,
    `description` VARCHAR(200) NULL,
    `is_visible` TINYINT NOT NULL DEFAULT 0,
    `register_time` DATETIME NOT NULL DEFAULT (CURRENT_TIME),
    `update_time` DATETIME NOT NULL DEFAULT (CURRENT_TIME),
    PRIMARY KEY (`id`))
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `keeper`.`survey_reply`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `keeper`.`survey_reply` ;

CREATE TABLE IF NOT EXISTS `keeper`.`survey_reply` (
                                                       `id` INT NOT NULL AUTO_INCREMENT,
                                                       `type` VARCHAR(10) NOT NULL,
    PRIMARY KEY (`id`))
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `keeper`.`survey_member_reply`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `keeper`.`survey_member_reply` ;

CREATE TABLE IF NOT EXISTS `keeper`.`survey_member_reply` (
                                                              `id` INT NOT NULL AUTO_INCREMENT,
                                                              `member_id` INT NOT NULL DEFAULT 1,
                                                              `survey_id` INT NOT NULL DEFAULT 1,
                                                              `reply_id` INT NOT NULL DEFAULT 1,
                                                              `reply_time` DATETIME NOT NULL DEFAULT (CURRENT_TIME),
    PRIMARY KEY (`id`),
    INDEX `fk_survey_member_reply_member1_idx` (`member_id` ASC) VISIBLE,
    INDEX `fk_survey_member_reply_survey1_idx` (`survey_id` ASC) VISIBLE,
    INDEX `fk_survey_member_reply_survey_reply1_idx` (`reply_id` ASC) VISIBLE,
    UNIQUE INDEX `is_reply_duplicated` (`member_id` ASC, `survey_id` ASC) INVISIBLE,
    CONSTRAINT `fk_survey_member_reply_member1`
    FOREIGN KEY (`member_id`)
    REFERENCES `keeper`.`member` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
    CONSTRAINT `fk_survey_member_reply_survey1`
    FOREIGN KEY (`survey_id`)
    REFERENCES `keeper`.`survey` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
    CONSTRAINT `fk_survey_member_reply_survey_reply1`
    FOREIGN KEY (`reply_id`)
    REFERENCES `keeper`.`survey_reply` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `keeper`.`survey_reply_excuse`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `keeper`.`survey_reply_excuse` ;

CREATE TABLE IF NOT EXISTS `keeper`.`survey_reply_excuse` (
                                                              `survey_member_reply_id` INT NOT NULL DEFAULT 1,
                                                              `rest_excuse` VARCHAR(200) NOT NULL,
    PRIMARY KEY (`survey_member_reply_id`),
    CONSTRAINT `fk_survey_reply_excuse_survey_member_reply1`
    FOREIGN KEY (`survey_member_reply_id`)
    REFERENCES `keeper`.`survey_member_reply` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `keeper`.`seminar`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `keeper`.`seminar` ;

CREATE TABLE IF NOT EXISTS `keeper`.`seminar` (
                                                  `id` INT NOT NULL AUTO_INCREMENT,
                                                  `open_time` DATETIME NOT NULL DEFAULT (CURRENT_TIME),
    `attendance_start_time` DATETIME NULL,
    `attendance_close_time` DATETIME NULL,
    `lateness_close_time` DATETIME NULL,
    `attendance_code` VARCHAR(10) NULL,
    `name` VARCHAR(100) NULL,
    `register_time` DATETIME NOT NULL DEFAULT (CURRENT_TIME),
    `update_time` DATETIME NOT NULL DEFAULT (CURRENT_TIME),
    `starter_id` INT NULL,
    PRIMARY KEY (`id`),
    INDEX `fk_seminar_member1_idx` (`starter_id` ASC) VISIBLE,
    CONSTRAINT `fk_seminar_member1`
    FOREIGN KEY (`starter_id`)
    REFERENCES `keeper`.`member` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `keeper`.`seminar_attendance_status`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `keeper`.`seminar_attendance_status` ;

CREATE TABLE IF NOT EXISTS `keeper`.`seminar_attendance_status` (
                                                                    `id` INT NOT NULL AUTO_INCREMENT,
                                                                    `type` VARCHAR(10) NOT NULL,
    PRIMARY KEY (`id`))
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `keeper`.`seminar_attendance`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `keeper`.`seminar_attendance` ;

CREATE TABLE IF NOT EXISTS `keeper`.`seminar_attendance` (
                                                             `id` INT NOT NULL AUTO_INCREMENT,
                                                             `seminar_id` INT NOT NULL DEFAULT 1,
                                                             `member_id` INT NOT NULL DEFAULT 1,
                                                             `status_id` INT NOT NULL DEFAULT 1,
                                                             `attend_time` DATETIME NOT NULL DEFAULT (CURRENT_TIME),
    PRIMARY KEY (`id`),
    INDEX `fk_seminar_attendance_seminar1_idx` (`seminar_id` ASC) VISIBLE,
    INDEX `fk_seminar_attendance_member1_idx` (`member_id` ASC) VISIBLE,
    INDEX `fk_seminar_attendance_seminar_attendance_status1_idx` (`status_id` ASC) VISIBLE,
    UNIQUE INDEX `is_seminar_attendance_duplicated` (`member_id` ASC, `seminar_id` ASC) VISIBLE,
    CONSTRAINT `fk_seminar_attendance_seminar1`
    FOREIGN KEY (`seminar_id`)
    REFERENCES `keeper`.`seminar` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
    CONSTRAINT `fk_seminar_attendance_member1`
    FOREIGN KEY (`member_id`)
    REFERENCES `keeper`.`member` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
    CONSTRAINT `fk_seminar_attendance_seminar_attendance_status1`
    FOREIGN KEY (`status_id`)
    REFERENCES `keeper`.`seminar_attendance_status` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `keeper`.`seminar_attendance_excuse`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `keeper`.`seminar_attendance_excuse` ;

CREATE TABLE IF NOT EXISTS `keeper`.`seminar_attendance_excuse` (
                                                                    `seminar_attendance_id` INT NOT NULL,
                                                                    `absence_excuse` VARCHAR(200) NOT NULL,
    PRIMARY KEY (`seminar_attendance_id`),
    CONSTRAINT `fk_seminar_attendance_excuse_seminar_attendance1`
    FOREIGN KEY (`seminar_attendance_id`)
    REFERENCES `keeper`.`seminar_attendance` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `keeper`.`merit_type`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `keeper`.`merit_type` ;

CREATE TABLE IF NOT EXISTS `keeper`.`merit_type` (
                                                     `id` INT NOT NULL AUTO_INCREMENT,
                                                     `merit` INT NOT NULL DEFAULT 0,
                                                     `detail` VARCHAR(45) NULL,
    `is_merit` TINYINT NOT NULL DEFAULT 0,
    PRIMARY KEY (`id`))
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `keeper`.`merit_log`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `keeper`.`merit_log` ;

CREATE TABLE IF NOT EXISTS `keeper`.`merit_log` (
                                                    `id` INT NOT NULL AUTO_INCREMENT,
                                                    `member_id` INT NOT NULL DEFAULT 1,
                                                    `member_realname` VARCHAR(45) NOT NULL,
    `member_generation` VARCHAR(45) NOT NULL,
    `time` DATETIME NOT NULL DEFAULT (CURRENT_TIME),
    `merit_type_id` INT NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `fk_merit_log_merit_type1_idx` (`merit_type_id` ASC) VISIBLE,
    CONSTRAINT `fk_merit_log_merit_type1`
    FOREIGN KEY (`merit_type_id`)
    REFERENCES `keeper`.`merit_type` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `keeper`.`ctf_challenge_has_ctf_challenge_category`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `keeper`.`ctf_challenge_has_ctf_challenge_category` ;

CREATE TABLE IF NOT EXISTS `keeper`.`ctf_challenge_has_ctf_challenge_category` (
                                                                                   `id` INT NOT NULL AUTO_INCREMENT,
                                                                                   `ctf_challenge_id` INT NOT NULL,
                                                                                   `ctf_challenge_category_id` INT NOT NULL,
                                                                                   PRIMARY KEY (`id`),
    INDEX `fk_ctf_challenge_has_ctf_challenge_category_ctf_challenge_c_idx` (`ctf_challenge_category_id` ASC) VISIBLE,
    INDEX `fk_ctf_challenge_has_ctf_challenge_category_ctf_challenge1_idx` (`ctf_challenge_id` ASC) VISIBLE,
    CONSTRAINT `fk_ctf_challenge_has_ctf_challenge_category_ctf_challenge1`
    FOREIGN KEY (`ctf_challenge_id`)
    REFERENCES `keeper`.`ctf_challenge` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
    CONSTRAINT `fk_ctf_challenge_has_ctf_challenge_category_ctf_challenge_cat1`
    FOREIGN KEY (`ctf_challenge_category_id`)
    REFERENCES `keeper`.`ctf_challenge_category` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `keeper`.`posting_has_file`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `keeper`.`posting_has_file` ;

CREATE TABLE IF NOT EXISTS `keeper`.`posting_has_file` (
                                                           `posting_id` INT NOT NULL,
                                                           `file_id` INT NOT NULL,
                                                           PRIMARY KEY (`posting_id`, `file_id`),
    INDEX `fk_posting_has_file_file1_idx` (`file_id` ASC) VISIBLE,
    INDEX `fk_posting_has_file_posting1_idx` (`posting_id` ASC) VISIBLE,
    CONSTRAINT `fk_posting_has_file_posting1`
    FOREIGN KEY (`posting_id`)
    REFERENCES `keeper`.`posting` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
    CONSTRAINT `fk_posting_has_file_file1`
    FOREIGN KEY (`file_id`)
    REFERENCES `keeper`.`file` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `keeper`.`ctf_challenge_has_file`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `keeper`.`ctf_challenge_has_file` ;

CREATE TABLE IF NOT EXISTS `keeper`.`ctf_challenge_has_file` (
                                                                 `ctf_challenge_id` INT NOT NULL,
                                                                 `file_id` INT NOT NULL,
                                                                 PRIMARY KEY (`ctf_challenge_id`, `file_id`),
    INDEX `fk_ctf_challenge_has_file_file1_idx` (`file_id` ASC) VISIBLE,
    INDEX `fk_ctf_challenge_has_file_ctf_challenge1_idx` (`ctf_challenge_id` ASC) VISIBLE,
    CONSTRAINT `fk_ctf_challenge_has_file_ctf_challenge1`
    FOREIGN KEY (`ctf_challenge_id`)
    REFERENCES `keeper`.`ctf_challenge` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
    CONSTRAINT `fk_ctf_challenge_has_file_file1`
    FOREIGN KEY (`file_id`)
    REFERENCES `keeper`.`file` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `keeper`.`book_borrow_log`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `keeper`.`book_borrow_log` ;

CREATE TABLE IF NOT EXISTS `keeper`.`book_borrow_log` (
                                                          `id` INT NOT NULL AUTO_INCREMENT,
                                                          `borrow_date` DATE NULL,
                                                          `expire_date` DATE NULL,
                                                          `return_date` DATE NULL,
                                                          `reject_date` DATE NULL,
                                                          `book_id` INT NOT NULL,
                                                          `book_title` VARCHAR(250) NOT NULL,
    `book_author` VARCHAR(40) NOT NULL,
    `borrow_id` INT NOT NULL,
    `status` VARCHAR(40) NOT NULL,
    `member_id` INT NOT NULL,
    `member_realname` VARCHAR(40) NOT NULL,
    `register_time` DATETIME NOT NULL DEFAULT (CURRENT_TIME),
    `update_time` DATETIME NOT NULL DEFAULT (CURRENT_TIME),
    PRIMARY KEY (`id`))
    ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `keeper`.`member_read_posting`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `keeper`.`member_read_posting` ;

CREATE TABLE IF NOT EXISTS `keeper`.`member_read_posting` (
                                                              `id` INT NOT NULL AUTO_INCREMENT,
                                                              `member_id` INT NOT NULL,
                                                              `posting_id` INT NOT NULL,
                                                              PRIMARY KEY (`id`),
    INDEX `fk_member_read_posting_member1_idx` (`member_id` ASC) VISIBLE,
    INDEX `fk_member_read_posting_posting1_idx` (`posting_id` ASC) VISIBLE,
    CONSTRAINT `fk_member_read_posting_member1`
    FOREIGN KEY (`member_id`)
    REFERENCES `keeper`.`member` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
    CONSTRAINT `fk_member_read_posting_posting1`
    FOREIGN KEY (`posting_id`)
    REFERENCES `keeper`.`posting` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
    ENGINE = InnoDB;

USE `keeper`;

DELIMITER $$

USE `keeper`$$
DROP TRIGGER IF EXISTS `keeper`.`book_BEFORE_UPDATE` $$
USE `keeper`$$
CREATE DEFINER = CURRENT_USER TRIGGER `keeper`.`book_BEFORE_UPDATE` BEFORE UPDATE ON `book` FOR EACH ROW
BEGIN
	SET NEW.update_time = CURRENT_TIME;
END$$


USE `keeper`$$
DROP TRIGGER IF EXISTS `keeper`.`equipment_BEFORE_UPDATE` $$
USE `keeper`$$
CREATE DEFINER = CURRENT_USER TRIGGER `keeper`.`equipment_BEFORE_UPDATE` BEFORE UPDATE ON `equipment` FOR EACH ROW
BEGIN
	SET NEW.update_time = CURRENT_TIME;
END$$


USE `keeper`$$
DROP TRIGGER IF EXISTS `keeper`.`member_BEFORE_UPDATE` $$
USE `keeper`$$
CREATE DEFINER = CURRENT_USER TRIGGER `keeper`.`member_BEFORE_UPDATE` BEFORE UPDATE ON `member` FOR EACH ROW
BEGIN
	SET NEW.update_time = CURRENT_TIME;
END$$


USE `keeper`$$
DROP TRIGGER IF EXISTS `keeper`.`book_borrow_info_BEFORE_UPDATE` $$
USE `keeper`$$
CREATE DEFINER = CURRENT_USER TRIGGER `keeper`.`book_borrow_info_BEFORE_UPDATE` BEFORE UPDATE ON `book_borrow_info` FOR EACH ROW
BEGIN
	SET NEW.update_time = CURRENT_TIME;
END$$


USE `keeper`$$
DROP TRIGGER IF EXISTS `keeper`.`attendance_AFTER_INSERT` $$
USE `keeper`$$
CREATE DEFINER = CURRENT_USER TRIGGER `keeper`.`attendance_AFTER_INSERT` AFTER INSERT ON `attendance` FOR EACH ROW
BEGIN
UPDATE member SET total_attendance = total_attendance + 1 WHERE id = NEW.member_id;
END$$


USE `keeper`$$
DROP TRIGGER IF EXISTS `keeper`.`study_BEFORE_UPDATE` $$
USE `keeper`$$
CREATE DEFINER = CURRENT_USER TRIGGER `keeper`.`study_BEFORE_UPDATE` BEFORE UPDATE ON `study` FOR EACH ROW
BEGIN
	SET NEW.update_time = CURRENT_TIME;
END$$


USE `keeper`$$
DROP TRIGGER IF EXISTS `keeper`.`ctf_contest_BEFORE_UPDATE` $$
USE `keeper`$$
CREATE DEFINER = CURRENT_USER TRIGGER `keeper`.`ctf_contest_BEFORE_UPDATE` BEFORE UPDATE ON `ctf_contest` FOR EACH ROW
BEGIN
	SET NEW.update_time = CURRENT_TIME;
END$$


USE `keeper`$$
DROP TRIGGER IF EXISTS `keeper`.`ctf_contest_BEFORE_DELETE` $$
USE `keeper`$$
CREATE DEFINER = CURRENT_USER TRIGGER `keeper`.`ctf_contest_BEFORE_DELETE` BEFORE DELETE ON `ctf_contest` FOR EACH ROW
BEGIN
UPDATE ctf_submit_log SET ctf_contest_id = 1 WHERE ctf_contest_id = OLD.id;
END$$


USE `keeper`$$
DROP TRIGGER IF EXISTS `keeper`.`ctf_team_BEFORE_UPDATE` $$
USE `keeper`$$
CREATE DEFINER = CURRENT_USER TRIGGER `keeper`.`ctf_team_BEFORE_UPDATE` BEFORE UPDATE ON `ctf_team` FOR EACH ROW
BEGIN
	SET NEW.update_time = CURRENT_TIME;
END$$


USE `keeper`$$
DROP TRIGGER IF EXISTS `keeper`.`ctf_challenge_BEFORE_UPDATE` $$
USE `keeper`$$
CREATE DEFINER = CURRENT_USER TRIGGER `keeper`.`ctf_challenge_BEFORE_UPDATE` BEFORE UPDATE ON `ctf_challenge` FOR EACH ROW
BEGIN
	SET NEW.update_time = CURRENT_TIME;
END$$


USE `keeper`$$
DROP TRIGGER IF EXISTS `keeper`.`survey_BEFORE_INSERT` $$
USE `keeper`$$
CREATE DEFINER = CURRENT_USER TRIGGER `keeper`.`survey_BEFORE_INSERT` BEFORE INSERT ON `survey` FOR EACH ROW
BEGIN
	IF NEW.name IS NULL THEN
        SET NEW.name = DATE_FORMAT(CURRENT_TIME,'%Y%m%d_활동인원조사');
END IF;
END$$


USE `keeper`$$
DROP TRIGGER IF EXISTS `keeper`.`survey_BEFORE_UPDATE` $$
USE `keeper`$$
CREATE DEFINER = CURRENT_USER TRIGGER `keeper`.`survey_BEFORE_UPDATE` BEFORE UPDATE ON `survey` FOR EACH ROW
BEGIN
	SET NEW.update_time = CURRENT_TIME;
END$$


USE `keeper`$$
DROP TRIGGER IF EXISTS `keeper`.`seminar_BEFORE_INSERT` $$
USE `keeper`$$
CREATE DEFINER = CURRENT_USER TRIGGER `keeper`.`seminar_BEFORE_INSERT` BEFORE INSERT ON `seminar` FOR EACH ROW
BEGIN
	IF NEW.name IS NULL THEN
        SET NEW.name = DATE_FORMAT(NEW.open_time,'%Y-%m-%d');
END IF;
END$$


USE `keeper`$$
DROP TRIGGER IF EXISTS `keeper`.`seminar_BEFORE_UPDATE` $$
USE `keeper`$$
CREATE DEFINER = CURRENT_USER TRIGGER `keeper`.`seminar_BEFORE_UPDATE` BEFORE UPDATE ON `seminar` FOR EACH ROW
BEGIN
	SET NEW.update_time = CURRENT_TIME;
END$$


USE `keeper`$$
DROP TRIGGER IF EXISTS `keeper`.`book_borrow_log_BEFORE_UPDATE` $$
USE `keeper`$$
CREATE DEFINER = CURRENT_USER TRIGGER `keeper`.`book_borrow_log_BEFORE_UPDATE` BEFORE UPDATE ON `book_borrow_log` FOR EACH ROW
BEGIN
	SET NEW.update_time = CURRENT_TIME;
END$$


DELIMITER ;

-- -----------------------------------------------------
-- Data for table `keeper`.`file`
-- -----------------------------------------------------
START TRANSACTION;
USE `keeper`;
INSERT INTO `keeper`.`file` (`id`, `file_name`, `file_path`, `file_size`, `upload_time`, `ip_address`) VALUES (1, 'default_thumbnail_member.png', 'keeper_files/thumbnail/default/default_thumbnail_member.png', 33609, DEFAULT, '1.1.1.1');
INSERT INTO `keeper`.`file` (`id`, `file_name`, `file_path`, `file_size`, `upload_time`, `ip_address`) VALUES (2, 'default_thumbnail_posting.png', 'keeper_files/thumbnail/default/default_thumbnail_posting.png', 33609, DEFAULT, '1.1.1.1');
INSERT INTO `keeper`.`file` (`id`, `file_name`, `file_path`, `file_size`, `upload_time`, `ip_address`) VALUES (3, 'profile_badge_grade_first.gif', 'keeper_files/thumbnail/badge/profile_badge_grade_first.gif', 33609, DEFAULT, '1.1.1.1');
INSERT INTO `keeper`.`file` (`id`, `file_name`, `file_path`, `file_size`, `upload_time`, `ip_address`) VALUES (4, 'profile_badge_grade_second.gif', 'keeper_files/thumbnail/badge/profile_badge_grade_second.gif', 33609, DEFAULT, '1.1.1.1');
INSERT INTO `keeper`.`file` (`id`, `file_name`, `file_path`, `file_size`, `upload_time`, `ip_address`) VALUES (5, 'profile_badge_state_graduate.gif', 'keeper_files/thumbnail/badge/profile_badge_state_graduate.gif', 33609, DEFAULT, '1.1.1.1');
INSERT INTO `keeper`.`file` (`id`, `file_name`, `file_path`, `file_size`, `upload_time`, `ip_address`) VALUES (6, 'profile_badge_state_quit.png', 'keeper_files/thumbnail/badge/profile_badge_state_quit.png', 33609, DEFAULT, '1.1.1.1');
INSERT INTO `keeper`.`file` (`id`, `file_name`, `file_path`, `file_size`, `upload_time`, `ip_address`) VALUES (7, 'profile_badge_state_sleep.gif', 'keeper_files/thumbnail/badge/profile_badge_state_sleep.gif', 33609, DEFAULT, '1.1.1.1');
INSERT INTO `keeper`.`file` (`id`, `file_name`, `file_path`, `file_size`, `upload_time`, `ip_address`) VALUES (8, 'profile_badge_state_regular.gif', 'keeper_files/thumbnail/badge/profile_badge_state_regular.gif', 33609, DEFAULT, '1.1.1.1');
INSERT INTO `keeper`.`file` (`id`, `file_name`, `file_path`, `file_size`, `upload_time`, `ip_address`) VALUES (9, 'default_thumbnail_intro.png', 'keeper_files/thumbnail/default/default_thumbnail_intro.png', 33609, DEFAULT, '1.1.1.1');
INSERT INTO `keeper`.`file` (`id`, `file_name`, `file_path`, `file_size`, `upload_time`, `ip_address`) VALUES (10, 'default_thumbnail_book.png', 'keeper_files/thumbnail/default/default_thumbnail_book.png', 33609, DEFAULT, '1.1.1.1');
INSERT INTO `keeper`.`file` (`id`, `file_name`, `file_path`, `file_size`, `upload_time`, `ip_address`) VALUES (11, 'profile_badge_role_chairman.gif', 'keeper_files/thumbnail/badge/profile_role_chairman.gif', 33609, DEFAULT, '1.1.1.1');
INSERT INTO `keeper`.`file` (`id`, `file_name`, `file_path`, `file_size`, `upload_time`, `ip_address`) VALUES (12, 'profile_badge_role_vice_chairman.gif', 'keeper_files/thumbnail/badge/profile_role_vice_chairman.gif', 33609, DEFAULT, '1.1.1.1');
INSERT INTO `keeper`.`file` (`id`, `file_name`, `file_path`, `file_size`, `upload_time`, `ip_address`) VALUES (13, 'profile_badge_role_external_manager.gif', 'keeper_files/thumbnail/badge/profile_badge_role_external_manager.gif', 33609, DEFAULT, '1.1.1.1');
INSERT INTO `keeper`.`file` (`id`, `file_name`, `file_path`, `file_size`, `upload_time`, `ip_address`) VALUES (14, 'profile_badge_role_study_manager.gif', 'keeper_files/thumbnail/badge/profile_badge_role_study_manager.gif', 33609, DEFAULT, '1.1.1.1');
INSERT INTO `keeper`.`file` (`id`, `file_name`, `file_path`, `file_size`, `upload_time`, `ip_address`) VALUES (15, 'profile_badge_role_it_manager.gif', 'keeper_files/thumbnail/badge/profile_badge_role_it_manager.gif', 33609, DEFAULT, '1.1.1.1');
INSERT INTO `keeper`.`file` (`id`, `file_name`, `file_path`, `file_size`, `upload_time`, `ip_address`) VALUES (16, 'profile_badge_role_clerk.gif', 'keeper_files/thumbnail/badge/profile_badge_role_clerk.gif', 33609, DEFAULT, '1.1.1.1');
INSERT INTO `keeper`.`file` (`id`, `file_name`, `file_path`, `file_size`, `upload_time`, `ip_address`) VALUES (17, 'profile_badge_role_general_affairs.gif', 'keeper_files/thumbnail/badge/profile_badge_role_general_affairs.gif', 33609, DEFAULT, '1.1.1.1');
INSERT INTO `keeper`.`file` (`id`, `file_name`, `file_path`, `file_size`, `upload_time`, `ip_address`) VALUES (18, 'profile_badge_role_librarian.gif', 'keeper_files/thumbnail/badge/profile_badge_role_librarian.gif', 33609, DEFAULT, '1.1.1.1');

COMMIT;


-- -----------------------------------------------------
-- Data for table `keeper`.`thumbnail`
-- -----------------------------------------------------
START TRANSACTION;
USE `keeper`;
INSERT INTO `keeper`.`thumbnail` (`id`, `path`, `file_id`) VALUES (1, 'keeper_files/thumbnail/default/default_thumbnail_member.png', 1);
INSERT INTO `keeper`.`thumbnail` (`id`, `path`, `file_id`) VALUES (2, 'keeper_files/thumbnail/default/default_thumbnail_posting.png', 2);
INSERT INTO `keeper`.`thumbnail` (`id`, `path`, `file_id`) VALUES (3, 'keeper_files/thumbnail/badge/profile_badge_grade_first.gif', 3);
INSERT INTO `keeper`.`thumbnail` (`id`, `path`, `file_id`) VALUES (4, 'keeper_files/thumbnail/badge/profile_badge_grade_second.gif', 4);
INSERT INTO `keeper`.`thumbnail` (`id`, `path`, `file_id`) VALUES (5, 'keeper_files/thumbnail/badge/profile_badge_state_graduate.gif', 5);
INSERT INTO `keeper`.`thumbnail` (`id`, `path`, `file_id`) VALUES (6, 'keeper_files/thumbnail/badge/profile_badge_state_quit.png', 6);
INSERT INTO `keeper`.`thumbnail` (`id`, `path`, `file_id`) VALUES (7, 'keeper_files/thumbnail/badge/profile_badge_state_sleep.gif', 7);
INSERT INTO `keeper`.`thumbnail` (`id`, `path`, `file_id`) VALUES (8, 'keeper_files/thumbnail/badge/profile_badge_state_regular.gif', 8);
INSERT INTO `keeper`.`thumbnail` (`id`, `path`, `file_id`) VALUES (9, 'keeper_files/thumbnail/default/default_thumbnail_intro.png', 9);
INSERT INTO `keeper`.`thumbnail` (`id`, `path`, `file_id`) VALUES (10, 'keeper_files/thumbnail/default/default_thumbnail_book.png', 10);
INSERT INTO `keeper`.`thumbnail` (`id`, `path`, `file_id`) VALUES (11, 'keeper_files/thumbnail/badge/profile_badge_role_chairman.gif', 11);
INSERT INTO `keeper`.`thumbnail` (`id`, `path`, `file_id`) VALUES (12, 'keeper_files/thumbnail/badge/profile_badge_role_vice_chairman.gif', 12);
INSERT INTO `keeper`.`thumbnail` (`id`, `path`, `file_id`) VALUES (13, 'keeper_files/thumbnail/badge/profile_badge_role_external_manager.gif', 13);
INSERT INTO `keeper`.`thumbnail` (`id`, `path`, `file_id`) VALUES (14, 'keeper_files/thumbnail/badge/profile_badge_role_study_manager.gif', 14);
INSERT INTO `keeper`.`thumbnail` (`id`, `path`, `file_id`) VALUES (15, 'keeper_files/thumbnail/badge/profile_badge_role_it_manager.gif', 15);
INSERT INTO `keeper`.`thumbnail` (`id`, `path`, `file_id`) VALUES (16, 'keeper_files/thumbnail/badge/profile_badge_role_clerk.gif', 16);
INSERT INTO `keeper`.`thumbnail` (`id`, `path`, `file_id`) VALUES (17, 'keeper_files/thumbnail/badge/profile_badge_role_general_affairs.gif', 17);
INSERT INTO `keeper`.`thumbnail` (`id`, `path`, `file_id`) VALUES (18, 'keeper_files/thumbnail/badge/profile_badge_role_librarian.gif', 18);
INSERT INTO `keeper`.`thumbnail` (`id`, `path`, `file_id`) VALUES (19, 'dummy', 1);
INSERT INTO `keeper`.`thumbnail` (`id`, `path`, `file_id`) VALUES (20, 'dummy', 1);
INSERT INTO `keeper`.`thumbnail` (`id`, `path`, `file_id`) VALUES (21, 'dummy', 1);
INSERT INTO `keeper`.`thumbnail` (`id`, `path`, `file_id`) VALUES (22, 'dummy', 1);
INSERT INTO `keeper`.`thumbnail` (`id`, `path`, `file_id`) VALUES (23, 'dummy', 1);
INSERT INTO `keeper`.`thumbnail` (`id`, `path`, `file_id`) VALUES (24, 'dummy', 1);
INSERT INTO `keeper`.`thumbnail` (`id`, `path`, `file_id`) VALUES (25, 'dummy', 1);
INSERT INTO `keeper`.`thumbnail` (`id`, `path`, `file_id`) VALUES (26, 'dummy', 1);
INSERT INTO `keeper`.`thumbnail` (`id`, `path`, `file_id`) VALUES (27, 'dummy', 1);
INSERT INTO `keeper`.`thumbnail` (`id`, `path`, `file_id`) VALUES (28, 'dummy', 1);
INSERT INTO `keeper`.`thumbnail` (`id`, `path`, `file_id`) VALUES (29, 'dummy', 1);
INSERT INTO `keeper`.`thumbnail` (`id`, `path`, `file_id`) VALUES (30, 'dummy', 1);

COMMIT;


-- -----------------------------------------------------
-- Data for table `keeper`.`book_department`
-- -----------------------------------------------------
START TRANSACTION;
USE `keeper`;
INSERT INTO `keeper`.`book_department` (`id`, `name`) VALUES (DEFAULT, 'language');
INSERT INTO `keeper`.`book_department` (`id`, `name`) VALUES (DEFAULT, 'security');
INSERT INTO `keeper`.`book_department` (`id`, `name`) VALUES (DEFAULT, 'textbook');
INSERT INTO `keeper`.`book_department` (`id`, `name`) VALUES (DEFAULT, 'certification');
INSERT INTO `keeper`.`book_department` (`id`, `name`) VALUES (DEFAULT, 'document');
INSERT INTO `keeper`.`book_department` (`id`, `name`) VALUES (DEFAULT, 'etc');

COMMIT;


-- -----------------------------------------------------
-- Data for table `keeper`.`member_type`
-- -----------------------------------------------------
START TRANSACTION;
USE `keeper`;
INSERT INTO `keeper`.`member_type` (`id`, `name`, `badge_thumbnail_id`) VALUES (DEFAULT, '비회원', NULL);
INSERT INTO `keeper`.`member_type` (`id`, `name`, `badge_thumbnail_id`) VALUES (DEFAULT, '정회원', 8);
INSERT INTO `keeper`.`member_type` (`id`, `name`, `badge_thumbnail_id`) VALUES (DEFAULT, '휴면회원', 7);
INSERT INTO `keeper`.`member_type` (`id`, `name`, `badge_thumbnail_id`) VALUES (DEFAULT, '졸업', 5);

COMMIT;


-- -----------------------------------------------------
-- Data for table `keeper`.`member_rank`
-- -----------------------------------------------------
START TRANSACTION;
USE `keeper`;
INSERT INTO `keeper`.`member_rank` (`id`, `name`, `badge_thumbnail_id`) VALUES (DEFAULT, '일반회원', 4);
INSERT INTO `keeper`.`member_rank` (`id`, `name`, `badge_thumbnail_id`) VALUES (DEFAULT, '우수회원', 3);

COMMIT;


-- -----------------------------------------------------
-- Data for table `keeper`.`member`
-- -----------------------------------------------------
START TRANSACTION;
USE `keeper`;
INSERT INTO `keeper`.`member` (`id`, `login_id`, `email_address`, `password`, `real_name`, `birthday`, `student_id`, `register_time`, `update_time`, `member_type_id`, `member_rank_id`, `point`, `level`, `thumbnail_id`, `generation`, `total_attendance`) VALUES (1, 'virtual_member', 'virtual_email', 'virtual_password', '탈퇴회원', NULL, '000000000', '2022-01-01 00:00:00', DEFAULT, NULL, NULL, 0, 0, 6, NULL, DEFAULT);

COMMIT;


-- -----------------------------------------------------
-- Data for table `keeper`.`book_borrow_status`
-- -----------------------------------------------------
START TRANSACTION;
USE `keeper`;
INSERT INTO `keeper`.`book_borrow_status` (`id`, `status`) VALUES (DEFAULT, '대출 대기');
INSERT INTO `keeper`.`book_borrow_status` (`id`, `status`) VALUES (DEFAULT, '대출 반려');
INSERT INTO `keeper`.`book_borrow_status` (`id`, `status`) VALUES (DEFAULT, '대출 중');
INSERT INTO `keeper`.`book_borrow_status` (`id`, `status`) VALUES (DEFAULT, '반납 대기');
INSERT INTO `keeper`.`book_borrow_status` (`id`, `status`) VALUES (DEFAULT, '반납 완료');

COMMIT;


-- -----------------------------------------------------
-- Data for table `keeper`.`category`
-- -----------------------------------------------------
START TRANSACTION;
USE `keeper`;
INSERT INTO `keeper`.`category` (`id`, `name`) VALUES (1, 'virtual_category');
INSERT INTO `keeper`.`category` (`id`, `name`) VALUES (101, '공지사항');
INSERT INTO `keeper`.`category` (`id`, `name`) VALUES (102, '건의사항');
INSERT INTO `keeper`.`category` (`id`, `name`) VALUES (103, '정보게시판');
INSERT INTO `keeper`.`category` (`id`, `name`) VALUES (104, '자유게시판');
INSERT INTO `keeper`.`category` (`id`, `name`) VALUES (105, '익명게시판');
INSERT INTO `keeper`.`category` (`id`, `name`) VALUES (106, '졸업생게시판');
INSERT INTO `keeper`.`category` (`id`, `name`) VALUES (107, '시험게시판');
INSERT INTO `keeper`.`category` (`id`, `name`) VALUES (201, '스터디');
INSERT INTO `keeper`.`category` (`id`, `name`) VALUES (202, '발표자료');
INSERT INTO `keeper`.`category` (`id`, `name`) VALUES (203, '기술문서');
INSERT INTO `keeper`.`category` (`id`, `name`) VALUES (204, '회계부');

COMMIT;


-- -----------------------------------------------------
-- Data for table `keeper`.`posting`
-- -----------------------------------------------------
START TRANSACTION;
USE `keeper`;
INSERT INTO `keeper`.`posting` (`id`, `title`, `content`, `member_id`, `visit_count`, `register_time`, `update_time`, `ip_address`, `allow_comment`, `is_notice`, `is_secret`, `is_temp`, `password`, `category_id`, `thumbnail_id`) VALUES (1, 'virtual_posting', 'virtual_posting_content', 1, DEFAULT, '1970-01-01 00:00:00', '1970-01-01 00:00:00', '0.0.0.0', DEFAULT, DEFAULT, DEFAULT, DEFAULT, NULL, 1, NULL);

COMMIT;


-- -----------------------------------------------------
-- Data for table `keeper`.`member_job`
-- -----------------------------------------------------
START TRANSACTION;
USE `keeper`;
INSERT INTO `keeper`.`member_job` (`id`, `name`) VALUES (1, 'ROLE_회장');
INSERT INTO `keeper`.`member_job` (`id`, `name`) VALUES (2, 'ROLE_부회장');
INSERT INTO `keeper`.`member_job` (`id`, `name`) VALUES (3, 'ROLE_대외부장');
INSERT INTO `keeper`.`member_job` (`id`, `name`) VALUES (4, 'ROLE_학술부장');
INSERT INTO `keeper`.`member_job` (`id`, `name`) VALUES (5, 'ROLE_FRONT_전산관리자');
INSERT INTO `keeper`.`member_job` (`id`, `name`) VALUES (6, 'ROLE_서기');
INSERT INTO `keeper`.`member_job` (`id`, `name`) VALUES (7, 'ROLE_총무');
INSERT INTO `keeper`.`member_job` (`id`, `name`) VALUES (8, 'ROLE_사서');
INSERT INTO `keeper`.`member_job` (`id`, `name`) VALUES (9, 'ROLE_회원');
INSERT INTO `keeper`.`member_job` (`id`, `name`) VALUES (10, 'ROLE_출제자');
INSERT INTO `keeper`.`member_job` (`id`, `name`) VALUES (11, 'ROLE_BACK_전산관리자');
INSERT INTO `keeper`.`member_job` (`id`, `name`) VALUES (12, 'ROLE_INFRA_전산관리자');

COMMIT;


-- -----------------------------------------------------
-- Data for table `keeper`.`static_write_title`
-- -----------------------------------------------------
START TRANSACTION;
USE `keeper`;
INSERT INTO `keeper`.`static_write_title` (`id`, `title`, `type`) VALUES (DEFAULT, '키퍼(KEEPER) 소개글', 'intro');
INSERT INTO `keeper`.`static_write_title` (`id`, `title`, `type`) VALUES (DEFAULT, '정기 활동', 'activity');
INSERT INTO `keeper`.`static_write_title` (`id`, `title`, `type`) VALUES (DEFAULT, '동아리 자랑', 'excellence');
INSERT INTO `keeper`.`static_write_title` (`id`, `title`, `type`) VALUES (DEFAULT, '동아리 연혁', 'history');

COMMIT;


-- -----------------------------------------------------
-- Data for table `keeper`.`static_write_subtitle_image`
-- -----------------------------------------------------
START TRANSACTION;
USE `keeper`;
INSERT INTO `keeper`.`static_write_subtitle_image` (`id`, `subtitle`, `static_write_title_id`, `thumbnail_id`, `display_order`) VALUES (DEFAULT, 'NULL', 1, NULL, 1);
INSERT INTO `keeper`.`static_write_subtitle_image` (`id`, `subtitle`, `static_write_title_id`, `thumbnail_id`, `display_order`) VALUES (DEFAULT, '세미나', 2, NULL, 1);
INSERT INTO `keeper`.`static_write_subtitle_image` (`id`, `subtitle`, `static_write_title_id`, `thumbnail_id`, `display_order`) VALUES (DEFAULT, '스터디 & 멘토링', 2, NULL, 2);
INSERT INTO `keeper`.`static_write_subtitle_image` (`id`, `subtitle`, `static_write_title_id`, `thumbnail_id`, `display_order`) VALUES (DEFAULT, '기술문서', 2, NULL, 3);
INSERT INTO `keeper`.`static_write_subtitle_image` (`id`, `subtitle`, `static_write_title_id`, `thumbnail_id`, `display_order`) VALUES (DEFAULT, '동아리 지원', 3, NULL, 1);
INSERT INTO `keeper`.`static_write_subtitle_image` (`id`, `subtitle`, `static_write_title_id`, `thumbnail_id`, `display_order`) VALUES (DEFAULT, '이벤트', 3, NULL, 2);
INSERT INTO `keeper`.`static_write_subtitle_image` (`id`, `subtitle`, `static_write_title_id`, `thumbnail_id`, `display_order`) VALUES (DEFAULT, '대외활동', 3, NULL, 3);
INSERT INTO `keeper`.`static_write_subtitle_image` (`id`, `subtitle`, `static_write_title_id`, `thumbnail_id`, `display_order`) VALUES (DEFAULT, '2010', 4, NULL, 1);
INSERT INTO `keeper`.`static_write_subtitle_image` (`id`, `subtitle`, `static_write_title_id`, `thumbnail_id`, `display_order`) VALUES (DEFAULT, '2011', 4, NULL, 2);
INSERT INTO `keeper`.`static_write_subtitle_image` (`id`, `subtitle`, `static_write_title_id`, `thumbnail_id`, `display_order`) VALUES (DEFAULT, '2012', 4, NULL, 3);
INSERT INTO `keeper`.`static_write_subtitle_image` (`id`, `subtitle`, `static_write_title_id`, `thumbnail_id`, `display_order`) VALUES (DEFAULT, '2013', 4, NULL, 4);
INSERT INTO `keeper`.`static_write_subtitle_image` (`id`, `subtitle`, `static_write_title_id`, `thumbnail_id`, `display_order`) VALUES (DEFAULT, '2014', 4, NULL, 5);
INSERT INTO `keeper`.`static_write_subtitle_image` (`id`, `subtitle`, `static_write_title_id`, `thumbnail_id`, `display_order`) VALUES (DEFAULT, '2015', 4, NULL, 6);
INSERT INTO `keeper`.`static_write_subtitle_image` (`id`, `subtitle`, `static_write_title_id`, `thumbnail_id`, `display_order`) VALUES (DEFAULT, '2016', 4, NULL, 7);
INSERT INTO `keeper`.`static_write_subtitle_image` (`id`, `subtitle`, `static_write_title_id`, `thumbnail_id`, `display_order`) VALUES (DEFAULT, '2017', 4, NULL, 8);
INSERT INTO `keeper`.`static_write_subtitle_image` (`id`, `subtitle`, `static_write_title_id`, `thumbnail_id`, `display_order`) VALUES (DEFAULT, '2018', 4, NULL, 9);
INSERT INTO `keeper`.`static_write_subtitle_image` (`id`, `subtitle`, `static_write_title_id`, `thumbnail_id`, `display_order`) VALUES (DEFAULT, '2019', 4, NULL, 10);
INSERT INTO `keeper`.`static_write_subtitle_image` (`id`, `subtitle`, `static_write_title_id`, `thumbnail_id`, `display_order`) VALUES (DEFAULT, '2020', 4, NULL, 11);
INSERT INTO `keeper`.`static_write_subtitle_image` (`id`, `subtitle`, `static_write_title_id`, `thumbnail_id`, `display_order`) VALUES (DEFAULT, '2021', 4, NULL, 12);
INSERT INTO `keeper`.`static_write_subtitle_image` (`id`, `subtitle`, `static_write_title_id`, `thumbnail_id`, `display_order`) VALUES (DEFAULT, '2022', 4, NULL, 13);

COMMIT;


-- -----------------------------------------------------
-- Data for table `keeper`.`static_write_content`
-- -----------------------------------------------------
START TRANSACTION;
USE `keeper`;
INSERT INTO `keeper`.`static_write_content` (`id`, `content`, `static_write_subtitle_image_id`, `display_order`) VALUES (DEFAULT, '\\\'지키다\\\'라는 의미를 가진 단어 \\\'KEEP\\\'에서 착안하여, 정보보호에 관한 연구를 진행하고\\n그 성과를 공유하기 위해 만들어진 동아리입니다.\\n\\n전국의 많은 정보보안 동아리들과 교류하고 있으며,\\n습득한 지식을 시험해 볼 수 있는 다양한 해킹방어대회에 참여하고 있습니다. \\n\\n정보보안은 학과 커리큘럼에서 굉장히 늦게, 조금 포함되어 있고\\n인터넷에 자료도 많지 않아 혼자 공부하기 어려운 부분이 있습니다. \\n\\nKEEPER에 가입하시면 선배, 후배, 동기들과 다 같이 정보보안을 공부할 수 있습니다.\\n1학기 초에 열리는 오픈 세미나에서 신입 회원을 모집합니다.\\n특별한 재능이 있지 않아도, 성실하고 의지 넘치는 분이면 환영합니다!', 1, 1);
INSERT INTO `keeper`.`static_write_content` (`id`, `content`, `static_write_subtitle_image_id`, `display_order`) VALUES (DEFAULT, '매주 금요일 마다 정기적으로 운영', 2, 1);
INSERT INTO `keeper`.`static_write_content` (`id`, `content`, `static_write_subtitle_image_id`, `display_order`) VALUES (DEFAULT, '공지사항 전달 및 건의', 2, 2);
INSERT INTO `keeper`.`static_write_content` (`id`, `content`, `static_write_subtitle_image_id`, `display_order`) VALUES (DEFAULT, '개인 발표 또는 팀 발표', 2, 3);
INSERT INTO `keeper`.`static_write_content` (`id`, `content`, `static_write_subtitle_image_id`, `display_order`) VALUES (DEFAULT, '매학기 활동 시작 전 원하는 스터디 개설 및 스터디원 구성', 3, 4);
INSERT INTO `keeper`.`static_write_content` (`id`, `content`, `static_write_subtitle_image_id`, `display_order`) VALUES (DEFAULT, '스터디원과의 시간 협의 후 가능한 시간에 매주 스터디 진행', 3, 5);
INSERT INTO `keeper`.`static_write_content` (`id`, `content`, `static_write_subtitle_image_id`, `display_order`) VALUES (DEFAULT, '신입 회원의 경우 멘토링 진행 (기본적인 프로그래밍, 기초 보안 학습)', 3, 6);
INSERT INTO `keeper`.`static_write_content` (`id`, `content`, `static_write_subtitle_image_id`, `display_order`) VALUES (DEFAULT, '여름/겨울 방학 기간에 원하는 주제로 사람을 모집하여 기술 문서 작성', 4, 1);
INSERT INTO `keeper`.`static_write_content` (`id`, `content`, `static_write_subtitle_image_id`, `display_order`) VALUES (DEFAULT, '1년에 1건 이상 작성 요구', 4, 2);
INSERT INTO `keeper`.`static_write_content` (`id`, `content`, `static_write_subtitle_image_id`, `display_order`) VALUES (DEFAULT, '제안서 발표, 중간 발표, 최종 발표로 진행상황 공유', 4, 3);
INSERT INTO `keeper`.`static_write_content` (`id`, `content`, `static_write_subtitle_image_id`, `display_order`) VALUES (DEFAULT, '도서 및 기자재 지원', 5, 1);
INSERT INTO `keeper`.`static_write_content` (`id`, `content`, `static_write_subtitle_image_id`, `display_order`) VALUES (DEFAULT, '스터디룸 비용 지원', 5, 2);
INSERT INTO `keeper`.`static_write_content` (`id`, `content`, `static_write_subtitle_image_id`, `display_order`) VALUES (DEFAULT, '회식비 지원', 5, 3);
INSERT INTO `keeper`.`static_write_content` (`id`, `content`, `static_write_subtitle_image_id`, `display_order`) VALUES (DEFAULT, '키퍼 내 CTF', 6, 1);
INSERT INTO `keeper`.`static_write_content` (`id`, `content`, `static_write_subtitle_image_id`, `display_order`) VALUES (DEFAULT, '퀴즈 이벤트', 6, 2);
INSERT INTO `keeper`.`static_write_content` (`id`, `content`, `static_write_subtitle_image_id`, `display_order`) VALUES (DEFAULT, 'CTF 참가', 7, 1);
INSERT INTO `keeper`.`static_write_content` (`id`, `content`, `static_write_subtitle_image_id`, `display_order`) VALUES (DEFAULT, '한국인터넷진흥원(KISA) 주관 대학 정보보호동아리 지원사업(KUCIS)에 대학 정보보호 동아리로서 참여', 8, 1);
INSERT INTO `keeper`.`static_write_content` (`id`, `content`, `static_write_subtitle_image_id`, `display_order`) VALUES (DEFAULT, '본교 학생회관에서 KUCIS 영남권역 세미나를 개최하여 부산 외의 인접지역에 있는 정보보호동아리들과 지식을 공유하고 교류함', 10, 1);
INSERT INTO `keeper`.`static_write_content` (`id`, `content`, `static_write_subtitle_image_id`, `display_order`) VALUES (DEFAULT, '정보보호동아리 및 정보보호 분야의 현업에 종사하고 있는 사람들까지 대상으로한 경남권 정보보호 세미나를 주최함', 12, 1);
INSERT INTO `keeper`.`static_write_content` (`id`, `content`, `static_write_subtitle_image_id`, `display_order`) VALUES (DEFAULT, '경남권 동아리와 함께 청소년을 대상으로 정보보호의 입문을 돕기위한 Security One을 주최함', 12, 2);
INSERT INTO `keeper`.`static_write_content` (`id`, `content`, `static_write_subtitle_image_id`, `display_order`) VALUES (DEFAULT, 'KUCIS 사업 참여, MS(MicroSoft) Windows10 서포터즈 1등', 13, 1);
INSERT INTO `keeper`.`static_write_content` (`id`, `content`, `static_write_subtitle_image_id`, `display_order`) VALUES (DEFAULT, 'KUCIS 사업 참여', 14, 1);
INSERT INTO `keeper`.`static_write_content` (`id`, `content`, `static_write_subtitle_image_id`, `display_order`) VALUES (DEFAULT, '교내 서비스 취약점 분석', 15, 1);
INSERT INTO `keeper`.`static_write_content` (`id`, `content`, `static_write_subtitle_image_id`, `display_order`) VALUES (DEFAULT, 'KUCIS, 시원포럼 경남지역 연합세미나 주최', 16, 1);
INSERT INTO `keeper`.`static_write_content` (`id`, `content`, `static_write_subtitle_image_id`, `display_order`) VALUES (DEFAULT, '암호동아리 지원사업 참가', 18, 1);
INSERT INTO `keeper`.`static_write_content` (`id`, `content`, `static_write_subtitle_image_id`, `display_order`) VALUES (DEFAULT, '코드게이트 본선 진출', 20, 1);
INSERT INTO `keeper`.`static_write_content` (`id`, `content`, `static_write_subtitle_image_id`, `display_order`) VALUES (DEFAULT, '제6회 동서발전 정보보안 온라인 경진대회 우수상', 18, 2);
INSERT INTO `keeper`.`static_write_content` (`id`, `content`, `static_write_subtitle_image_id`, `display_order`) VALUES (DEFAULT, '화이트해커 양성 프로젝트 참여', 19, 1);
INSERT INTO `keeper`.`static_write_content` (`id`, `content`, `static_write_subtitle_image_id`, `display_order`) VALUES (DEFAULT, '전국 연합 동아리 CCA 가입', 20, 2);
INSERT INTO `keeper`.`static_write_content` (`id`, `content`, `static_write_subtitle_image_id`, `display_order`) VALUES (DEFAULT, '영남권 연합 동아리 참여', 20, 3);
INSERT INTO `keeper`.`static_write_content` (`id`, `content`, `static_write_subtitle_image_id`, `display_order`) VALUES (DEFAULT, '암호동아리 지원사업 참가', 19, 2);

COMMIT;


-- -----------------------------------------------------
-- Data for table `keeper`.`non_absence`
-- -----------------------------------------------------
START TRANSACTION;
USE `keeper`;
INSERT INTO `keeper`.`non_absence` (`id`, `term`, `day`, `point`) VALUES (DEFAULT, 'YEAR', 365, 20000);
INSERT INTO `keeper`.`non_absence` (`id`, `term`, `day`, `point`) VALUES (DEFAULT, 'MONTH', 28, 5000);
INSERT INTO `keeper`.`non_absence` (`id`, `term`, `day`, `point`) VALUES (DEFAULT, 'WEEK', 7, 1000);

COMMIT;


-- -----------------------------------------------------
-- Data for table `keeper`.`ctf_contest`
-- -----------------------------------------------------
START TRANSACTION;
USE `keeper`;
INSERT INTO `keeper`.`ctf_contest` (`id`, `name`, `description`, `register_time`, `update_time`, `creator`, `is_joinable`) VALUES (1, 'virtual_ctf_contest', 'virtual_ctf_contest_description', '2022-01-01 00:00:00', DEFAULT, 1, 0);

COMMIT;


-- -----------------------------------------------------
-- Data for table `keeper`.`ctf_team`
-- -----------------------------------------------------
START TRANSACTION;
USE `keeper`;
INSERT INTO `keeper`.`ctf_team` (`id`, `name`, `description`, `register_time`, `update_time`, `creator`, `score`, `contest_id`, `last_solve_time`) VALUES (1, 'virtual_ctf_team', 'virtual_ctf_team_description', '2022-01-01 00:00:00', DEFAULT, 1, 0, 1, DEFAULT);

COMMIT;


-- -----------------------------------------------------
-- Data for table `keeper`.`ctf_challenge_type`
-- -----------------------------------------------------
START TRANSACTION;
USE `keeper`;
INSERT INTO `keeper`.`ctf_challenge_type` (`id`, `name`) VALUES (1, 'STANDARD');
INSERT INTO `keeper`.`ctf_challenge_type` (`id`, `name`) VALUES (2, 'DYNAMIC');

COMMIT;


-- -----------------------------------------------------
-- Data for table `keeper`.`ctf_challenge`
-- -----------------------------------------------------
START TRANSACTION;
USE `keeper`;
INSERT INTO `keeper`.`ctf_challenge` (`id`, `name`, `description`, `register_time`, `update_time`, `creator`, `is_solvable`, `type_id`, `score`, `contest_id`, `max_submit_count`) VALUES (1, 'virtual_ctf_challenge', 'virtual_ctf_challenge_description', '2022-01-01 00:00:00', DEFAULT, 1, 0, NULL, 0, 1, DEFAULT);

COMMIT;


-- -----------------------------------------------------
-- Data for table `keeper`.`ctf_challenge_category`
-- -----------------------------------------------------
START TRANSACTION;
USE `keeper`;
INSERT INTO `keeper`.`ctf_challenge_category` (`id`, `name`) VALUES (1, 'Misc');
INSERT INTO `keeper`.`ctf_challenge_category` (`id`, `name`) VALUES (2, 'System');
INSERT INTO `keeper`.`ctf_challenge_category` (`id`, `name`) VALUES (3, 'Reversing');
INSERT INTO `keeper`.`ctf_challenge_category` (`id`, `name`) VALUES (4, 'Forensic');
INSERT INTO `keeper`.`ctf_challenge_category` (`id`, `name`) VALUES (5, 'Web');
INSERT INTO `keeper`.`ctf_challenge_category` (`id`, `name`) VALUES (6, 'Crypto');
INSERT INTO `keeper`.`ctf_challenge_category` (`id`, `name`) VALUES (7, 'OSINT');

COMMIT;


-- -----------------------------------------------------
-- Data for table `keeper`.`ctf_flag`
-- -----------------------------------------------------
START TRANSACTION;
USE `keeper`;
INSERT INTO `keeper`.`ctf_flag` (`id`, `content`, `team_id`, `challenge_id`, `is_correct`, `solved_time`, `last_try_time`, `remained_submit_count`) VALUES (1, 'virtual_ctf_flag', 1, 1, 0, NULL, NULL, DEFAULT);

COMMIT;


-- -----------------------------------------------------
-- Data for table `keeper`.`ctf_submit_log`
-- -----------------------------------------------------
START TRANSACTION;
USE `keeper`;
INSERT INTO `keeper`.`ctf_submit_log` (`id`, `submit_time`, `flag_submitted`, `is_correct`, `team_name`, `submitter_login_id`, `submitter_realname`, `challenge_name`, `contest_name`, `ctf_contest_id`) VALUES (1, '2022-01-01 00:00:00', 'virtual_ctf_submit_log_flag_submitted', 0, 'virtual_ctf_submit_log_team_name', 'virtual_ctf_submit_log_submitter_login_id', 'virtual_ctf_submit_log_submitter_realname', 'virtual_ctf_submit_log_challenge_name', 'virtual_ctf_submit_log_contest_name', 1);

COMMIT;


-- -----------------------------------------------------
-- Data for table `keeper`.`election`
-- -----------------------------------------------------
START TRANSACTION;
USE `keeper`;
INSERT INTO `keeper`.`election` (`id`, `name`, `description`, `creator`, `register_time`, `is_available`) VALUES (1, 'virtual_election', NULL, 1, '2022-01-01 00:00:00', 0);

COMMIT;


-- -----------------------------------------------------
-- Data for table `keeper`.`election_candidate`
-- -----------------------------------------------------
START TRANSACTION;
USE `keeper`;
INSERT INTO `keeper`.`election_candidate` (`id`, `election_id`, `candidate_id`, `member_job_id`, `description`, `register_time`, `vote_count`) VALUES (1, 1, 1, 9, NULL, '2022-01-01 00:00:00', 0);

COMMIT;


-- -----------------------------------------------------
-- Data for table `keeper`.`survey`
-- -----------------------------------------------------
START TRANSACTION;
USE `keeper`;
INSERT INTO `keeper`.`survey` (`id`, `open_time`, `close_time`, `name`, `description`, `is_visible`, `register_time`, `update_time`) VALUES (DEFAULT, '2022-01-01 00:00:00', '2022-01-01 00:00:00', 'virtual_survey', NULL, 0, DEFAULT, DEFAULT);

COMMIT;


-- -----------------------------------------------------
-- Data for table `keeper`.`survey_reply`
-- -----------------------------------------------------
START TRANSACTION;
USE `keeper`;
INSERT INTO `keeper`.`survey_reply` (`id`, `type`) VALUES (DEFAULT, '활동');
INSERT INTO `keeper`.`survey_reply` (`id`, `type`) VALUES (DEFAULT, '휴면(군휴학)');
INSERT INTO `keeper`.`survey_reply` (`id`, `type`) VALUES (DEFAULT, '휴면(기타)');
INSERT INTO `keeper`.`survey_reply` (`id`, `type`) VALUES (DEFAULT, '졸업');
INSERT INTO `keeper`.`survey_reply` (`id`, `type`) VALUES (DEFAULT, '탈퇴');

COMMIT;


-- -----------------------------------------------------
-- Data for table `keeper`.`seminar`
-- -----------------------------------------------------
START TRANSACTION;
USE `keeper`;
INSERT INTO `keeper`.`seminar` (`id`, `open_time`, `attendance_start_time`, `attendance_close_time`, `lateness_close_time`, `attendance_code`, `name`, `register_time`, `update_time`, `starter_id`) VALUES (DEFAULT, '2022-01-01 00:00:00', '2022-01-01 00:00:00', '2022-01-01 00:00:00', '2022-01-01 00:00:00', '000', 'virtual_seminar', DEFAULT, DEFAULT, NULL);

COMMIT;


-- -----------------------------------------------------
-- Data for table `keeper`.`seminar_attendance_status`
-- -----------------------------------------------------
START TRANSACTION;
USE `keeper`;
INSERT INTO `keeper`.`seminar_attendance_status` (`id`, `type`) VALUES (DEFAULT, '출석');
INSERT INTO `keeper`.`seminar_attendance_status` (`id`, `type`) VALUES (DEFAULT, '지각');
INSERT INTO `keeper`.`seminar_attendance_status` (`id`, `type`) VALUES (DEFAULT, '결석');
INSERT INTO `keeper`.`seminar_attendance_status` (`id`, `type`) VALUES (DEFAULT, '개인사정');
INSERT INTO `keeper`.`seminar_attendance_status` (`id`, `type`) VALUES (DEFAULT, '출석 전');

COMMIT;


-- -----------------------------------------------------
-- Data for table `keeper`.`merit_type`
-- -----------------------------------------------------
START TRANSACTION;
USE `keeper`;
INSERT INTO `keeper`.`merit_type` (`id`, `merit`, `detail`, `is_merit`) VALUES (DEFAULT, 0, 'virtual_merit', 0);
INSERT INTO `keeper`.`merit_type` (`id`, `merit`, `detail`, `is_merit`) VALUES (DEFAULT, 3, '결석', 0);
INSERT INTO `keeper`.`merit_type` (`id`, `merit`, `detail`, `is_merit`) VALUES (DEFAULT, 3, '지각 2회', 0);

COMMIT;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;