DROP TABLE IF EXISTS CHMM_RESOURCE_ROLE_MAP;
DROP TABLE IF EXISTS CHMM_RESOURCE_INFO;
DROP TABLE IF EXISTS CHMM_MULTI_USER_GROUP_INFO;
DROP TABLE IF EXISTS CHMM_MULTI_ROLE_GROUP_INFO;
DROP TABLE IF EXISTS CHMM_USER_ROLE_MAP;
DROP TABLE IF EXISTS CHMM_USER_INFO;
DROP TABLE IF EXISTS CHMM_ROLE_INFO;
DROP TABLE IF EXISTS CHMM_USER_GROUP_MAP;
DROP TABLE IF EXISTS CHMM_USER_TOKEN;
DROP TABLE IF EXISTS CHMM_GROUP_ROLE_MAP;
DROP TABLE IF EXISTS CHMM_GROUP_INFO;
DROP TABLE IF EXISTS BOARD;

CREATE TABLE IF NOT EXISTS BOARD
(
    ID              BIGINT AUTO_INCREMENT PRIMARY KEY,
    TITLE           VARCHAR(255) NOT NULL,
    CONTENT         TEXT         NOT NULL,
    SYS_INSERT_DTM  TIMESTAMP DEFAULT NULL,
    SYS_UPDATE_DTM  TIMESTAMP DEFAULT NULL
);

CREATE TABLE IF NOT EXISTS CHMM_USER_INFO (
    USER_ID VARCHAR(255) NOT NULL,
    USER_EMAIL VARCHAR(255) NOT NULL,
    USER_MOBILE VARCHAR(14) DEFAULT NULL,
    USER_NAME VARCHAR(255) NOT NULL,
    USER_NICK VARCHAR(255) DEFAULT NULL,
    USER_PWD VARCHAR(255) NOT NULL,
    USER_IMG VARCHAR(4000) DEFAULT NULL,
    USER_MSG VARCHAR(4000) DEFAULT NULL,
    USER_DESC VARCHAR(1000) DEFAULT NULL,
    USER_STAT_CD VARCHAR(16) DEFAULT NULL,
    USER_SNSID VARCHAR(255) DEFAULT NULL,
    ACCOUNT_NON_LOCK CHAR(1) NOT NULL DEFAULT '1',
    ACCOUNT_START_DT VARCHAR(8) NOT NULL DEFAULT '00000101',
    ACCOUNT_END_DT VARCHAR(8) NOT NULL DEFAULT '99991231',
    PASSWORD_EXPIRE_DT VARCHAR(8) NOT NULL DEFAULT '99991231',
    USE_YN CHAR(1) NOT NULL DEFAULT '1',
    SYS_INSERT_DTM DATETIME DEFAULT NULL,
    SYS_INSERT_USER_ID VARCHAR(255) DEFAULT NULL,
    SYS_UPDATE_DTM DATETIME DEFAULT NULL,
    SYS_UPDATE_USER_ID VARCHAR(255) DEFAULT NULL,
    PASSWORD_LOCK_CNT INT(11) NOT NULL DEFAULT 0,
    EXCEPTION_SEND_YN CHAR(1) DEFAULT NULL,
    LOG_SEND_YN CHAR(1) DEFAULT NULL,
    PRIMARY KEY (USER_ID)
    );

CREATE TABLE CHMM_USER_TOKEN
(
    SEQ                BIGINT			 NOT NULL AUTO_INCREMENT,
    USER_ID            VARCHAR(255)	 NOT NULL COMMENT '사용자ID',
    ACCESS_TOKEN       TEXT        	 NOT NULL COMMENT 'ACCESSTOKEN',
    REFRESH_TOKEN      TEXT        	 NOT NULL COMMENT 'REFRESHTOKEN',
    EXPIRY_DATE        DATETIME   	 NOT NULL COMMENT '토큰유효기간',
    SYS_INSERT_DTM     DATETIME   	 DEFAULT NULL COMMENT '등록시간',
    SYS_INSERT_USER_ID VARCHAR(255)	 DEFAULT NULL COMMENT '등록자ID',
    PRIMARY KEY (SEQ)
);

CREATE TABLE IF NOT EXISTS CHMM_USER_ROLE_MAP
(
    USER_ID VARCHAR(255) NOT NULL,
    ROLE_ID VARCHAR(255) NOT NULL,
    USE_YN CHAR(1) DEFAULT NULL,
    SYS_INSERT_DTM DATETIME DEFAULT NULL,
    PRIMARY KEY (USER_ID,ROLE_ID)
);

CREATE TABLE CHMM_USER_GROUP_MAP
(
    GROUP_ID       		VARCHAR(255) NOT NULL,
    USER_ID        		VARCHAR(255) NOT NULL,
    SYS_INSERT_DTM 		TIMESTAMP,
    SYS_INSERT_USER_ID 	VARCHAR(255),
    USE_YN         		CHAR(1),
    PRIMARY KEY (GROUP_ID, USER_ID)
);

CREATE TABLE CHMM_GROUP_INFO
(
    GROUP_ID           VARCHAR(255)        NOT NULL,
    GROUP_NAME         VARCHAR(255)        NOT NULL,
    GROUP_DESC         VARCHAR(1000),
    USE_YN             CHAR(1) DEFAULT '0' NOT NULL,
    SYS_INSERT_DTM     TIMESTAMP,
    SYS_INSERT_USER_ID VARCHAR(255),
    SYS_UPDATE_DTM     TIMESTAMP,
    SYS_UPDATE_USER_ID VARCHAR(255),
    PRIMARY KEY (GROUP_ID)
);

CREATE TABLE CHMM_GROUP_ROLE_MAP
(
    GROUP_ID       		VARCHAR(255) NOT NULL,
    ROLE_ID        		VARCHAR(255) NOT NULL,
    SYS_INSERT_DTM 		TIMESTAMP,
    SYS_INSERT_USER_ID 	VARCHAR(255),
    USE_YN         		CHAR(1),
    PRIMARY KEY (GROUP_ID, ROLE_ID)
);

CREATE TABLE IF NOT EXISTS CHMM_MULTI_USER_GROUP_INFO
(
    PARENT_GROUP_ID VARCHAR(255) NOT NULL,
    CHILD_GROUP_ID VARCHAR(255) NOT NULL,
    PRIMARY KEY (PARENT_GROUP_ID,CHILD_GROUP_ID)
);

CREATE TABLE IF NOT EXISTS CHMM_RESOURCE_ROLE_MAP
(
    RESOURCE_ID VARCHAR(255) NOT NULL,
    ROLE_ID VARCHAR(255) NOT NULL,
    USE_YN CHAR(1) DEFAULT NULL,
    SYS_INSERT_DTM DATETIME DEFAULT NULL,
    PRIMARY KEY (RESOURCE_ID,ROLE_ID)
);

CREATE TABLE IF NOT EXISTS CHMM_MULTI_ROLE_GROUP_INFO (
    PARENT_ROLE_ID VARCHAR(255) NOT NULL,
    CHILD_ROLE_ID VARCHAR(255) NOT NULL,
    PRIMARY KEY (PARENT_ROLE_ID,CHILD_ROLE_ID)
);

CREATE TABLE IF NOT EXISTS CHMM_RESOURCE_INFO (
    RESOURCE_ID VARCHAR(255) NOT NULL,
    RESOURCE_URI VARCHAR(255) NOT NULL,
    RESOURCE_NAME VARCHAR(255) DEFAULT NULL,
    RESOURCE_DESC VARCHAR(1000) DEFAULT NULL,
    RESOURCE_HTTPMETHOD VARCHAR(400) DEFAULT NULL,
    SECURITY_ORDER INT(11) NOT NULL,
    USE_YN CHAR(1) NOT NULL,
    SYS_INSERT_DTM DATETIME DEFAULT NULL,
    SYS_INSERT_USER_ID VARCHAR(255) DEFAULT NULL,
    SYS_UPDATE_DTM DATETIME DEFAULT NULL,
    SYS_UPDATE_USER_ID VARCHAR(255) DEFAULT NULL,
    PRIMARY KEY (RESOURCE_ID)
);

CREATE TABLE IF NOT EXISTS CHMM_ROLE_INFO (
    ROLE_ID VARCHAR(255) NOT NULL,
    ROLE_NAME VARCHAR(255) NOT NULL,
    ROLE_DESC VARCHAR(1000) DEFAULT NULL,
    ROLE_START_DT VARCHAR(8) NOT NULL,
    ROLE_END_DT VARCHAR(8) NOT NULL,
    USE_YN CHAR(1) NOT NULL,
    SYS_INSERT_DTM DATETIME DEFAULT NULL,
    SYS_INSERT_USER_ID VARCHAR(255) DEFAULT NULL,
    SYS_UPDATE_DTM DATETIME DEFAULT NULL,
    SYS_UPDATE_USER_ID VARCHAR(255) DEFAULT NULL,
    PRIMARY KEY (ROLE_ID)
);

CREATE TABLE IF NOT EXISTS CHMM_MAIN_COMMON_CODE (
    CATEGORY_ID VARCHAR(255) NOT NULL,
    CATEGORY_DESC VARCHAR(1000) DEFAULT NULL,
    ORDER_NUM INT(11) DEFAULT NULL,
    USE_YN CHAR(1) NOT NULL,
    CREATED_TIME DATETIME DEFAULT NULL,
    CREATE_USER VARCHAR(255) DEFAULT NULL,
    UPDATED_TIME DATETIME DEFAULT NULL,
    UPDATE_USER VARCHAR(255) DEFAULT NULL,
    REAL_VALUE VARCHAR(100) DEFAULT NULL,
    PRIMARY KEY (CATEGORY_ID)
);

CREATE TABLE IF NOT EXISTS CHMM_MIDDLE_COMMON_CODE (
    CATEGORY_ID VARCHAR(255) NOT NULL,
    CODE_ID VARCHAR(255) NOT NULL,
    CODE_DESC VARCHAR(1000) DEFAULT NULL,
    ORDER_NUM INT(11) DEFAULT NULL,
    USE_YN CHAR(1) NOT NULL,
    CREATED_TIME DATETIME DEFAULT NULL,
    CREATE_USER VARCHAR(255) DEFAULT NULL,
    UPDATED_TIME DATETIME DEFAULT NULL,
    UPDATE_USER VARCHAR(255) DEFAULT NULL,
    REAL_VALUE VARCHAR(100) DEFAULT NULL,
    PRIMARY KEY (CATEGORY_ID, CODE_ID)
);

CREATE TABLE IF NOT EXISTS CHMM_SUB_COMMON_CODE (
    CATEGORY_ID VARCHAR(255) NOT NULL,
    CODE_ID VARCHAR(255) NOT NULL,
    CODE_ITEM_ID VARCHAR(255) NOT NULL,
    CODE_ITEM_DESC VARCHAR(1000) DEFAULT NULL,
    ORDER_NUM INT(11) DEFAULT NULL,
    USE_YN CHAR(1) NOT NULL,
    CREATED_TIME DATETIME DEFAULT NULL,
    CREATE_USER VARCHAR(255) DEFAULT NULL,
    UPDATED_TIME DATETIME DEFAULT NULL,
    UPDATE_USER VARCHAR(255) DEFAULT NULL,
    REAL_VALUE VARCHAR(100) DEFAULT NULL,
    PRIMARY KEY (CATEGORY_ID, CODE_ID, CODE_ITEM_ID)
);
