mplusDemo/mplus-fatima/script/mariadb/maria_create_table.sql

######################################################################################################

CREATE TABLE MP_API_CATEGORY 
(	
   CAT_SEQ 			INT COMMENT '시퀀스', 
	CAT_NAME 		VARCHAR(255) COMMENT '카테고리명', 
	CAT_DESC 		VARCHAR(500) COMMENT '설명', 
	PARENT_CAT_SEQ INT COMMENT '부모 시퀀스', 
	PATH_NAME 		VARCHAR(255) COMMENT '카테고리 경로', 
	CREATE_DT 		DATETIME COMMENT '생성일시', 
	CREATE_ID 		VARCHAR(255) COMMENT '생성자', 
	UPDATE_DT 		DATETIME COMMENT '수정일시', 
	UPDATE_ID 		VARCHAR(255) COMMENT '수정한 사람', 
	CONSTRAINT PK_MP_API_CATEGORY PRIMARY KEY (CAT_SEQ), 
	CONSTRAINT UQ_API_CAT_PATH_NAME UNIQUE(PATH_NAME)
) COMMENT 'API를 묶어주는 카테고리';

CREATE TABLE MP_API 
(	
   API_SEQ 				INT COMMENT '시퀀스', 
	API_TYPE 			VARCHAR(50) COMMENT '유형 (SQL, WEB SERVICE..)', 
	DATA_SOURCE_NAME 	VARCHAR(255) COMMENT 'SQL타입인 경우 사용할 DataSource명', 
	API_DESC 			VARCHAR(500) COMMENT  '설명', 
	HTTP_METHOD_TYPE 	VARCHAR(50) COMMENT 'HTTP통신이 사용되는 경우 메소드 유형 (GET/PUT/POST/DELETE)', 
	API_NAME 			VARCHAR(255) COMMENT '이름', 
	TARGET_NAME			VARCHAR(255) COMMENT '프로시저명', 
	QUERY_MSG 			VARCHAR(2048) COMMENT 'DB연동인 경우 질의문', 
	RESULT_TYPE 		VARCHAR(50) COMMENT '결과유형 (MAP, LIST, INT..)', 
	REQ_URL_ADDR 		VARCHAR(1024) COMMENT '요청과 맵핑되는 주소 (category가 제외된 형태)', 
	TARGET_URL_ADDR 	VARCHAR(1024) COMMENT 'HTTP 통신인 경우, API가 호출해야 하는 상대편 웹 서비스 주소 (파라미터 포함)', 
	RES_SAMPLE_CL 		VARCHAR(4000) COMMENT '응답샘플', 
	CAT_SEQ 				INT COMMENT 'API가 어느 카테고리에 포함되는지 시퀀스', 
	CAT_PATH_NAME 		VARCHAR(255) COMMENT 'API가 포함되는 카테고리 경로명 (반정규화)', 
	CREATE_DT 			DATETIME COMMENT '생성일시', 
	CREATE_ID 			VARCHAR(255) COMMENT '생성자', 
	UPDATE_DT 			DATETIME COMMENT '수정일시', 
	UPDATE_ID 			VARCHAR(255) COMMENT '수정한 사람', 
	CONSTRAINT PK_MP_API PRIMARY KEY (API_SEQ),
	CONSTRAINT FK_API_CAT_SEQ FOREIGN KEY (CAT_SEQ) REFERENCES MP_API_CATEGORY (CAT_SEQ)
) COMMENT 'API정보';

CREATE TABLE MP_API_HEADER 
(	
   HEADER_SEQ 		INT COMMENT '시퀀스', 
	API_SEQ 			INT COMMENT '참조 API의 시퀀스', 
	HEADER_NAME 	VARCHAR(255) COMMENT '헤더명', 
	HEADER_VALUE 	VARCHAR(255) COMMENT '헤더값',
	CONSTRAINT FK_API_HEADER_API_SEQ FOREIGN KEY (API_SEQ) REFERENCES MP_API (API_SEQ)
) COMMENT 'API 헤더 정보';

CREATE TABLE MP_API_PARAM 
(	
   PARAM_SEQ 		INT COMMENT '시퀀스', 
	API_SEQ 			INT COMMENT '참조 API 의 시퀀스', 
	DATA_TYPE 		VARCHAR(50) COMMENT '파라미터의 데이터 타입', 
	PARAM_DESC 		VARCHAR(500) COMMENT '설명', 
	PARAM_NAME 		VARCHAR(255) COMMENT '이름', 
	SAMPLE_VALUE 	VARCHAR(4000) COMMENT '샘플 값',
	CONSTRAINT FK_API_PARAM_API_SEQ FOREIGN KEY (API_SEQ) REFERENCES MP_API (API_SEQ)
) COMMENT 'API 파라미터 정보';

######################################################################################################

CREATE TABLE MP_MENU 
(	
	MENU_SEQ				INT COMMENT '시퀀스',
	MENU_ID 				VARCHAR(255) COMMENT '식별자', 
	PARENT_MENU_ID 	VARCHAR(255) COMMENT '부모메뉴 식별자', 
	MENU_ORDER 			INT COMMENT '표시순서', 
	MENU_TYPE 			VARCHAR(50) DEFAULT 'MAIN' COMMENT '메뉴 유형 (NAVI, SIDE, CONT..)', 
	MENU_NAME 			VARCHAR(255) COMMENT '메뉴명 (사용자가 보는 text)', 
	ENABLED_YN 			CHAR(1) DEFAULT 'N' COMMENT '사용되는지 여부', 
	ACCESS_URI_ADDR 	VARCHAR(1024) COMMENT '접근주소', 
	IMAGE_URI_ADDR 	VARCHAR(1024) COMMENT '이미지 주소', 
	MENU_DESC 			VARCHAR(500) COMMENT '설명', 
	CONSTRAINT PK_MP_MENU PRIMARY KEY (MENU_ID),
	CONSTRAINT UQ_MENU_SEQ_ID UNIQUE (MENU_SEQ, MENU_ID)
) COMMENT '메뉴 정보';

CREATE TABLE MP_MENU_PARAM 
(
	MENU_PARAM_SEQ	INT COMMENT '파라미터 SEQ', 	
	MENU_ID 			VARCHAR(255) COMMENT '메뉴 ID', 
	PARAM_NAME 		VARCHAR(255) NOT NULL COMMENT '파라미터명', 
	DATA_TYPE 		VARCHAR(50) NOT NULL COMMENT '데이터 타입', 
	PARAM_VALUE 	VARCHAR(4000) COMMENT '파리미터 값', 
	PARAM_DESC		VARCHAR(500) COMMENT '파라미터 설명', 
	CONSTRAINT FK_PARAM_MENU_ID FOREIGN KEY(MENU_ID) REFERENCES MP_MENU(MENU_ID), 
	CONSTRAINT UQ_PARAM_PARAM_NAME UNIQUE (MENU_ID, PARAM_NAME)
) COMMENT '메뉴 파라미터 정보';

CREATE TABLE MP_I18N 
(	
   MENU_ID 	VARCHAR(255) COMMENT '메뉴 ID', 
	I18N_CD	CHAR(2) COMMENT '언어코드', 
	CD_TEXT 	VARCHAR(255) COMMENT '문자열',
	CONSTRAINT UQ_I18N_MENU_ID_I18N_CD UNIQUE (MENU_ID, I18N_CD), 
	CONSTRAINT FK_I18N_MENU_ID FOREIGN KEY (MENU_ID) REFERENCES MP_MENU (MENU_ID)
) COMMENT '메뉴에 적용되는 다국어 정보';

######################################################################################################

CREATE TABLE MP_MANAGER 
(	
   USER_ID 		VARCHAR(255) COMMENT '로그인 ID', 
	USER_NAME 	VARCHAR(255) COMMENT '이름', 
	ENABLED_YN 	CHAR(1) DEFAULT 'N' COMMENT '활성화 여부', 
	DEPT_NAME 	VARCHAR(255) COMMENT  '소속', 
	PWD_VALUE 	VARCHAR(255) COMMENT  '로그인 암호', 
	CREATE_DT 	DATETIME COMMENT '생성일시', 
	CREATE_ID 	VARCHAR(255) COMMENT '생성자', 
	UPDATE_DT 	DATETIME COMMENT '수정일시', 
	UPDATE_ID 	VARCHAR(255) COMMENT '수정한 사람', 
	CONSTRAINT PK_MP_MANAGER PRIMARY KEY (USER_ID)
) COMMENT '관리자 계정';

CREATE TABLE MP_TELNO 
(	
	TELNO_SEQ 		INT COMMENT '시퀀스', 
	BUILDING_DESC 	VARCHAR(500) COMMENT '건물', 
	ROOM_DESC 		VARCHAR(500) COMMENT '건물내의 방', 
	TEL_VALUE 		VARCHAR(255) COMMENT '전화번호', 
	TELNO_ORDER 	INT DEFAULT 1 COMMENT '표시순서'
) COMMENT '병원 전화번호';

CREATE TABLE MP_VERSION 
(	
  	PLATFORM_TYPE 		VARCHAR(50) COMMENT '플랫폼 유형', 
  	CERT_TYPE 			VARCHAR(50) DEFAULT 'InHouse' COMMENT '인증유형 구분 (InHouse, AppStore 등..)', 
  	VERSION_ORDER		INT COMMENT '현재 최신버전. 이 버전보다 이전 버전들은 업데이트를 해야함. 이후버전(지금 심사중인 버전)들은 안해도됨.',
  	APP_NAME 			VARCHAR(255) COMMENT '배포된 이름', 
	MARKET_URL 			VARCHAR(1024) COMMENT '앱을 다운로드 받기 위한 마켓 주소', 
  CONSTRAINT UQ_MP_VERSION UNIQUE (PLATFORM_TYPE, CERT_TYPE, APP_NAME) 
) COMMENT '앱 버전정보'; 

CREATE TABLE MP_USER
(
   USER_SEQ                INT COMMENT '시퀀스',
   USER_ID            		VARCHAR(255) COMMENT '사용자 ID',
   USER_NAME               VARCHAR(255) COMMENT '사용자 이름',
   LOGIN_FAIL_CNT          INT COMMENT '로그인 실패 횟수',
   PASSWORD_VALUE          VARCHAR(255) COMMENT '로그인 암호 (sha-256)',
   PASSWORD_UPDATE_DT      DATETIME COMMENT '패스워드 업데이트 일시',
   REGISTER_DT             DATETIME COMMENT '등록일시',
   LOCAL_CIPHER_KEY_VALUE  VARCHAR(255) COMMENT '기기 로컬 암호화 키',
   USE_ATTR_ORDER				INT COMMENT '사용자가 선택한 속성 순서',
   CONSTRAINT PK_USER_ID PRIMARY KEY (USER_ID)
) COMMENT '사용자 정보'; 

CREATE TABLE MP_AGREEMENT 
(	
	AGREEMENT_SEQ		INT COMMENT '시퀀스', 
	AGREEMENT_ORDER	INT COMMENT '동의서 순서', 
	AGREEMENT_NAME		VARCHAR(255) NOT NULL COMMENT '동의서 이름', 
	AGREEMENT_CL		VARCHAR(8000) NOT NULL COMMENT '동의서 내용',
	AGREEMENT_ID		VARCHAR(255) COMMENT '동의서 식별자', 
	TYPE_NAME			VARCHAR(255) NOT NULL COMMENT '동의서유형(ALL, MINOR). 현재는 일반/미성년자만 있지만 조만간 외국인도 생길듯하여 NAME으로함', 
	VERSION_NUMBER 	INT COMMENT '동의서 버전 ', 	
	REQUIRED_YN 		CHAR(1) DEFAULT 'N' COMMENT '필수 여부',
	ENABLED_YN 	 	 	CHAR(1) DEFAULT 'N' COMMENT '활성화 여부', 
	NEW_YN 		    	CHAR(1) DEFAULT 'Y' COMMENT '최신 동의서 여부', 
	CONSTRAINT PK_AGREEMENT_SEQ PRIMARY KEY (AGREEMENT_SEQ)
) COMMENT '서비스 약관(동의서) 정보'; 


CREATE TABLE MP_USER_AGREEMENT
(
   USER_AGREEMENT_SEQ	INT COMMENT '시퀀스',
   AGREEMENT_SEQ			INT NOT NULL COMMENT  '동의서 시퀀스',
   AGREEMENT_ID			VARCHAR(255) NOT NULL COMMENT '동의서 ID',
   USER_ID					VARCHAR(255) NOT NULL COMMENT '사용자 ID',
   AGREEMENT_YN			CHAR(1) DEFAULT 'N' COMMENT '동의여부 (Y/N)', 
   REGISTER_DT				DATETIME COMMENT '등록일시',
   CONSTRAINT PK_USER_AGREEMENT_SEQ PRIMARY KEY (USER_AGREEMENT_SEQ),
   CONSTRAINT FK_USER_AGREEMENT_SEQ FOREIGN KEY (AGREEMENT_SEQ) REFERENCES MP_AGREEMENT(AGREEMENT_SEQ), 
   CONSTRAINT FK_USER_USER_ID FOREIGN KEY (USER_ID) REFERENCES MP_USER(USER_ID) 
) COMMENT '서비스 약관(동의서) 동의여부'; 

CREATE TABLE MP_LOG
(	
   LOG_TYPE 			VARCHAR(20) COMMENT '로그 구분', 
	USER_ID  			VARCHAR(20) COMMENT '사용자 아이디', 
	USER_NAME		 	VARCHAR(30) COMMENT '사용자 이름', 
	DEPT_NAME 			VARCHAR(100) COMMENT  '부서명', 
	TITLE		 			VARCHAR(100) COMMENT  '제목', 
	CONTENT	 			VARCHAR(1024) COMMENT '내용', 
	CONTENT2	 			VARCHAR(1024) COMMENT '내용2', 
	CONTENT3	 			VARCHAR(1024) COMMENT '내용3', 
	CREATE_DT 			DATETIME COMMENT '생성일시',
	CONSTRAINT PK_MP_LOG PRIMARY KEY (USER_ID, CREATE_DT, TITLE)
) COMMENT 'LOG';

######################################################################################################


CREATE TABLE MP_PUSH_FORM (
	FORM_ID 				VARCHAR(255) COMMENT '메시지 형식에 대한 식별자', 
	FORM_TYPE			VARCHAR(50) COMMENT '유형 : PAGE, MAP, GUIDE', 
	USE_YN				CHAR(1) COMMENT '사용여부 (PUSH_MSG쪽에서 FK로 참조될꺼라서 삭제기능이 없을것임)', 
	MENU_ID				VARCHAR(255) COMMENT '유형이 PAGE인 경우 메뉴 식별자. 확장자가 page인 메뉴만 가능',
	INCLUDE_NAME_YN 	CHAR(1) COMMENT '메시지에 이름포함 여부', 
	FORM_DESC			VARCHAR(500) COMMENT '형식에 대한 설명',
	REG_DT				DATETIME COMMENT  '추가된 시간' DEFAULT CURRENT_TIMESTAMP,  
	CONSTRAINT PK_MP_PUSH_FORM PRIMARY KEY (FORM_ID), 
	CONSTRAINT FK_PUSH_FORM_MENU_ID FOREIGN KEY (MENU_ID) REFERENCES MP_MENU (MENU_ID)
) COMMENT '사용자에게 전송될 메시지 형식';

CREATE TABLE MNS_CONTENTS 
(	
	CONTENTS_SEQ		INT COMMENT '시퀀스', 
	CONTENTS_MSG		VARCHAR(4000) NOT NULL COMMENT '메시지 내용', 
	SENDER_ID			VARCHAR(100) NOT NULL COMMENT '발송자아이디', 
	CREATE_DT			DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '레코드 등록일시', 
	RESERVE_SEND_DT	DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '예약전송일시', 
	CONSTRAINT PK_MNS_CONTENTS PRIMARY KEY (CONTENTS_SEQ)
) COMMENT '전송될 메시지 정보'; 

CREATE TABLE MNS_CONTENTS_RESULT 
(	
	CONTENTS_SEQ	INT COMMENT '발송순서', 
	COMPLETE_DT		DATETIME NOT NULL COMMENT '전체 수신자에 대한 발송완료 일시',
	CONSTRAINT FK_CONTENTS_SEQ FOREIGN KEY (CONTENTS_SEQ) REFERENCES MNS_CONTENTS (CONTENTS_SEQ), 
	CONSTRAINT UQ_CONTENTS_SEQ UNIQUE (CONTENTS_SEQ)
) COMMENT '메시지 전송결과';

CREATE TABLE MNS_RECEIVER 
(	
	CONTENTS_SEQ	INT COMMENT '발송순서', 
	RECEIVER_ID		VARCHAR(255) NOT NULL COMMENT '수신자 아이디',
	CONSTRAINT FK_RC_CONTENTS_SEQ FOREIGN KEY (CONTENTS_SEQ) REFERENCES MNS_CONTENTS (CONTENTS_SEQ), 
	CONSTRAINT UQ_RC_CONTENTS_SEQ_RECEIVER_ID UNIQUE (CONTENTS_SEQ, RECEIVER_ID)
) COMMENT '메시지 수신자';

CREATE TABLE MNS_RECEIVER_RESULT 
(	
	CONTENTS_SEQ		INT COMMENT '발송순서', 
	RECEIVER_ID			VARCHAR(255) COMMENT '수신자 아이디', 
	DEVICE_TOKEN_ID	VARCHAR(255) COMMENT '토큰', 
	PLATFORM_TYPE		VARCHAR(50) COMMENT '플랫폼 구분', 
	SEND_DT				DATETIME NOT NULL COMMENT '발송일시', 
	SUCCESS_YN			CHAR(1) NOT NULL COMMENT '발송 성공여부', 
	ERROR_TYPE			VARCHAR(50) COMMENT '발송실패 에러응답코드', 
	DELETE_YN			CHAR(1) COMMENT '메시지 삭제여부(삭제예정필드)', 
	READ_YN				CHAR(1) COMMENT '메시지 읽음여부(삭제예정필드)',
	CONSTRAINT FK_CONTENTS_SEQ_RECEIVER_ID FOREIGN KEY (CONTENTS_SEQ, RECEIVER_ID) REFERENCES MNS_RECEIVER (CONTENTS_SEQ, RECEIVER_ID)
) COMMENT '메시지 수신자별 전송결과';

CREATE TABLE MP_MNS_MSGMAPPING 
(
	USER_ID			VARCHAR(255) NOT NULL COMMENT '사용자 ID (검색용)',
	CONTENTS_SEQ	INT NOT NULL COMMENT 'PUSH 메시지 구분자',
	FORM_ID			VARCHAR(255) NOT NULL COMMENT '메시지 유형',  	
	MESSAGE_VALUE	VARCHAR(255) NOT NULL COMMENT '메시지 본문 내용',
	LOG_DT			DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '로그 남기는 시간'
) COMMENT '메시지 추적을 위한 맵핑정보';
CREATE INDEX IDX_MSGMAPPING_CONTENTS ON MP_MNS_MSGMAPPING(USER_ID); 

CREATE TABLE MP_USER_DEVICE
(
	DEVICE_SEQ			INT COMMENT '사용자 Device SEQ',
	USER_ID				VARCHAR(255) NOT NULL COMMENT '사용자 ID',
	DEVICE_ID			VARCHAR(255) NOT NULL COMMENT 'Device ID',
	DEVICE_MODEL		VARCHAR(255) NOT NULL COMMENT 'Device Model(iPhone6s, 갤럭시s6)',
  	DEVICE_TYPE			VARCHAR(255) NOT NULL COMMENT 'Device Type (iOS, android)',
	DEVICE_ALIAS		VARCHAR(255) NOT NULL COMMENT '사용자 장비 별칭',
	CONSTRAINT FK_MP_USER_DEVICE_USER_ID FOREIGN KEY (USER_ID) REFERENCES MP_USER(USER_ID),
	CONSTRAINT UQ_MP_USER_DEVICE UNIQUE (USER_ID, DEVICE_ID)
) COMMENT '사용자 Device';

CREATE TABLE MP_PUBLIC_DEVICE
(
	PUBLICE_DEVICE_SEQ 	INT COMMENT '공용 Device SEQ',
	DEVICE_ID				VARCHAR(255) NOT NULL COMMENT 'Device ID',
	DEVICE_MODEL			VARCHAR(255) NOT NULL COMMENT 'Device Model(iPhone6s, 갤럭시s6)',
 	DEVICE_TYPE				VARCHAR(255) NOT NULL COMMENT 'Device Type (iOS, android)',
	DEVICE_ALIAS			VARCHAR(255) NOT NULL COMMENT '공용 장비 별칭',
	CONSTRAINT UQ_MP_PUBLICE_DEVICE UNIQUE (DEVICE_ID)
) COMMENT '공용 Device';


######################################################################################################

CREATE TABLE MP_ATTR
(
	ATTR_SEQ			INT COMMENT '업무 속성 SEQ',
	ATTR_TYPE		VARCHAR(20) NOT NULL COMMENT '업무 속성 타입',
	ATTR_ID			VARCHAR(255) NOT NULL COMMENT '업무 속성 ID',
	ATTR_NAME		VARCHAR(255) NOT NULL COMMENT '업무 속성 이름',
	UPPER_ATTR		VARCHAR(255) DEFAULT 'none',
	CONSTRAINT UQ_MP_ATTR UNIQUE (ATTR_TYPE, ATTR_ID)
) COMMENT '업무 속성';

CREATE TABLE MP_USER_ATTR
(
	USER_ATTR_SEQ			INT COMMENT '사용자 업무 SEQ',
	USER_ATTR_ORDER		INT NOT NULL COMMENT '사용자 업무 우선순위', 
	USER_ID					VARCHAR(255) NOT NULL COMMENT '사용자 ID',
	HOSPITAL_ID				VARCHAR(255) NOT NULL COMMENT '소속 ID',
  	DUTY_ID					VARCHAR(255) NOT NULL COMMENT '직무 ID',
  	WORK_ID					VARCHAR(255) NOT NULL COMMENT '업무 ID',
  CONSTRAINT PK_USER_ATTR_SEQ PRIMARY KEY (USER_ATTR_SEQ), 
  CONSTRAINT FK_USER_ATTR_USER_ID FOREIGN KEY (USER_ID) REFERENCES MP_USER(USER_ID),
  CONSTRAINT UQ_MP_USER_ATTR UNIQUE (USER_ID, HOSPITAL_ID, DUTY_ID, WORK_ID),
  CONSTRAINT UQ_MP_USER_ATTR_ORDER UNIQUE(USER_ID, USER_ATTR_ORDER )
) COMMENT '사용자 업무 테이블'; 

CREATE TABLE MP_EXTRA_ATTR
(
	EXTRA_ATTR_SEQ		INT COMMENT '업무 SEQ',
	EXTRA_ATTR_ID		VARCHAR(255) NOT NULL COMMENT '확장 업무 ID',
	EXTRA_ATTR_NAME	VARCHAR(255) NOT NULL COMMENT '확장 업무 이름',
  	EXTRA_ATTR_DESC	VARCHAR(500) COMMENT '확장 업무 설명',
  	CONSTRAINT PK_EXTRA_WORK_ID PRIMARY KEY (EXTRA_ATTR_ID), 
	CONSTRAINT UQ_MP_EXTRA_ATTR UNIQUE (EXTRA_ATTR_ID, EXTRA_ATTR_NAME)
) COMMENT '확장 속성'; 

CREATE TABLE MP_EXTRA_ATTR_USER
(
	EXTRA_ATTR_USER_SEQ		INT COMMENT '확장 업무 사용자 SEQ',
	EXTRA_ATTR_ID				VARCHAR(255) NOT NULL COMMENT '확장 업무 ID',
	USER_ID						VARCHAR(255) NOT NULL COMMENT '확장 업무 사용자 ID',
	EXTRA_ATTR_USER_ORDER   INT COMMENT '확장 업무 사용자 순서 (SEQ나 ORDER 둘중에 하나 삭제해도 될것같다)',
	CONSTRAINT PK_EXTRA_ATTR_USER_SEQ PRIMARY KEY (EXTRA_ATTR_USER_SEQ), 
 	CONSTRAINT FK_EXTRA_ATTR_USER_USER_ID FOREIGN KEY (USER_ID) REFERENCES MP_USER(USER_ID),
	CONSTRAINT FK_EXTRA_ATTR_ID FOREIGN KEY (EXTRA_ATTR_ID) REFERENCES MP_EXTRA_ATTR(EXTRA_ATTR_ID)
) COMMENT '확장 업무 사용자'; 

CREATE TABLE MP_AUTH
(
	AUTH_SEQ			INT COMMENT '권한 SEQ',
	AUTH_ID			VARCHAR(255) NOT NULL COMMENT '권한 ID',
	AUTH_NAME		VARCHAR(255) NOT NULL COMMENT '권한 이름',
 	AUTH_DESC		VARCHAR(500) COMMENT '권한 설명',
  	CONSTRAINT PK_AUTH_ID PRIMARY KEY (AUTH_ID), 
	CONSTRAINT UQ_MP_AUTH UNIQUE (AUTH_ID, AUTH_NAME)
) COMMENT '권한'; 

CREATE TABLE MP_AUTH_ATTR
(
	AUTH_ATTR_SEQ		INT COMMENT '권한 속성 SEQ',
	AUTH_ID				VARCHAR(255) NOT NULL COMMENT '권한 ID',
	AUTH_TYPE			VARCHAR(255) NOT NULL COMMENT '권한 타입 (ADD, MINUS)',
	AUTH_ATTR_TYPE		VARCHAR(255) NOT NULL COMMENT '권한 속성 타입',
	AUTH_ATTR_ID		VARCHAR(255) NOT NULL COMMENT '권한 속성 ID',
	CONSTRAINT FK_AUTH_ID FOREIGN KEY (AUTH_ID) REFERENCES MP_AUTH(AUTH_ID)
) COMMENT '권한 속성';
CREATE INDEX MP_AUTH_ATTR_IDX1 ON MP_AUTH_ATTR(AUTH_ATTR_ID, AUTH_ATTR_TYPE, AUTH_TYPE);

CREATE TABLE MP_MENU_AUTH
(
	MENU_AUTH_SEQ	INT COMMENT '메뉴 권한 SEQ',
	MENU_ID			VARCHAR(255) NOT NULL COMMENT '메뉴 ID',
	AUTH_ID			VARCHAR(255) NOT NULL COMMENT '권한 ID',
	CONSTRAINT FK_MENU_AUTH_MENU_ID FOREIGN KEY (MENU_ID) REFERENCES MP_MENU(MENU_ID),
	CONSTRAINT FK_MENU_AUTH_AUTH_ID FOREIGN KEY (AUTH_ID) REFERENCES MP_AUTH(AUTH_ID),
	CONSTRAINT UQ_MP_MENU_AUTH UNIQUE (MENU_ID, AUTH_ID)
) COMMENT '메뉴 권한 속성';

######################################################################################################

CREATE TABLE `mplus`.`sequence_data` (
    `sequence_name` varchar(100) NOT NULL,
    `sequence_increment` int(11) unsigned NOT NULL DEFAULT 1,
    `sequence_min_value` int(11) unsigned NOT NULL DEFAULT 1,
    `sequence_max_value` bigint(20) unsigned NOT NULL DEFAULT 18446744073709551615,
    `sequence_cur_value` bigint(20) unsigned DEFAULT 1,
    `sequence_cycle` boolean NOT NULL DEFAULT FALSE,
    PRIMARY KEY (`sequence_name`)
);


DELIMITER $$
 
CREATE FUNCTION `nextval` (`seq_name` varchar(100))
RETURNS bigint(20) NOT DETERMINISTIC
BEGIN
    DECLARE cur_val bigint(20);
 
    SELECT
        sequence_cur_value INTO cur_val
    FROM
        mplus.sequence_data
    WHERE
        sequence_name = seq_name
    ;
 
    IF cur_val IS NOT NULL THEN
        UPDATE
            mplus.sequence_data
        SET
            sequence_cur_value = IF (
                (sequence_cur_value + sequence_increment) > sequence_max_value,
                IF (
                    sequence_cycle = TRUE,
                    sequence_min_value,
                    NULL
                ),
                sequence_cur_value + sequence_increment
            )
        WHERE
            sequence_name = seq_name
        ;
    END IF;
 
    RETURN cur_val;
END$$

insert into sequence_data (sequence_name) values ('SEQ_MP_API_CATEGORY_CS');
insert into sequence_data (sequence_name) values ('SEQ_MP_API_AS');
insert into sequence_data (sequence_name) values ('SEQ_MP_API_HEADER_HS');
insert into sequence_data (sequence_name) values ('SEQ_MP_API_PARAM_PS');
insert into sequence_data (sequence_name) values ('SEQ_MP_MENU');
insert into sequence_data (sequence_name) values ('SEQ_MP_MENU_PARAM_SEQ_MPS');
insert into sequence_data (sequence_name) values ('SEQ_MP_TELNO_TS');
insert into sequence_data (sequence_name) values ('SEQ_MP_AGREEMENT');
insert into sequence_data (sequence_name) values ('SEQ_MP_USER_AGREEMENT_UA');
insert into sequence_data (sequence_name) values ('SEQ_MP_USER_US');
insert into sequence_data (sequence_name) values ('SEQ_MNS_CONTENTS');
insert into sequence_data (sequence_name) values ('SEQ_MP_USER_DEVICE');
insert into sequence_data (sequence_name) values ('SEQ_MP_PUBLIC_DEVICE');
insert into sequence_data (sequence_name) values ('SEQ_MP_ATTR');
insert into sequence_data (sequence_name) values ('SEQ_MP_USER_ATTR');
insert into sequence_data (sequence_name) values ('SEQ_MP_EXTRA_ATTR');
insert into sequence_data (sequence_name) values ('SEQ_MP_EXTRA_ATTR_USER');
insert into sequence_data (sequence_name) values ('SEQ_MP_AUTH');
insert into sequence_data (sequence_name) values ('SEQ_MP_AUTH_ATTR');
insert into sequence_data (sequence_name) values ('SEQ_MP_MENU_AUTH');