mplusDemo/mplus-fatima/script/oracle/create_Oracle_Table_For_MPlus.sql

CREATE TABLE MP_API_CATEGORY 
(	
    CAT_SEQ 		NUMBER(20,0), 
	CAT_NAME 		VARCHAR2(255), 
	CAT_DESC 		VARCHAR2(500), 
	PARENT_CAT_SEQ 	NUMBER(20,0), 
	PATH_NAME 		VARCHAR2(255), 
	CREATE_DT 		DATE, 
	CREATE_ID 		VARCHAR2(255), 
	UPDATE_DT 		DATE, 
	UPDATE_ID 		VARCHAR2(255), 
	CONSTRAINT PK_MP_API_CATEGORY PRIMARY KEY (CAT_SEQ), 
	CONSTRAINT UQ_API_CAT_PATH_NAME UNIQUE(PATH_NAME)
); 
COMMENT ON TABLE MP_API_CATEGORY IS 'API를 묶어주는 카테고리';
CREATE SEQUENCE SEQ_MP_API_CATEGORY_CS INCREMENT BY 1 START WITH 1 MAXVALUE 9223372036854775807 NOORDER;    
COMMENT ON COLUMN MP_API_CATEGORY.CAT_SEQ  IS '시퀀스'; 
COMMENT ON COLUMN MP_API_CATEGORY.CAT_NAME  IS '카테고리명'; 
COMMENT ON COLUMN MP_API_CATEGORY.CAT_DESC  IS '설명'; 
COMMENT ON COLUMN MP_API_CATEGORY.PARENT_CAT_SEQ  IS '부모 시퀀스'; 
COMMENT ON COLUMN MP_API_CATEGORY.PATH_NAME  IS '카테고리 경로';   
COMMENT ON COLUMN MP_API_CATEGORY.CREATE_DT  IS '생성일시'; 
COMMENT ON COLUMN MP_API_CATEGORY.CREATE_ID  IS '생성자'; 
COMMENT ON COLUMN MP_API_CATEGORY.UPDATE_DT  IS '수정일시'; 
COMMENT ON COLUMN MP_API_CATEGORY.UPDATE_ID  IS '수정한 사람';  

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

---------------------------------------------------------------------------------------------- 
CREATE TABLE MP_API_HEADER 
(	
    HEADER_SEQ 		NUMBER(20,0), 
	API_SEQ 		NUMBER(20,0), 
	HEADER_NAME 	VARCHAR2(255), 
	HEADER_VALUE 	VARCHAR2(255),
	CONSTRAINT FK_API_HEADER_API_SEQ FOREIGN KEY (API_SEQ) REFERENCES MP_API (API_SEQ)
); 
COMMENT ON TABLE MP_API_HEADER IS 'API 헤더 정보';
CREATE SEQUENCE SEQ_MP_API_HEADER_HS INCREMENT BY 1 START WITH 1 MAXVALUE 9223372036854775807 NOORDER;   
COMMENT ON COLUMN MP_API_HEADER.HEADER_SEQ  IS '시퀀스'; 
COMMENT ON COLUMN MP_API_HEADER.API_SEQ  IS '어떤 API의 헤더인지에 대한 시퀀스';
COMMENT ON COLUMN MP_API_HEADER.HEADER_NAME  IS '헤더명';
COMMENT ON COLUMN MP_API_HEADER.HEADER_VALUE  IS '헤더값';

---------------------------------------------------------------------------------------------- 
CREATE TABLE MP_API_PARAM 
(	
    PARAM_SEQ 		NUMBER(20,0), 
	API_SEQ 		NUMBER(20,0), 
	DATA_TYPE 		VARCHAR2(50), 
	PARAM_DESC 		VARCHAR2(500), 
	PARAM_NAME 		VARCHAR2(255), 
	SAMPLE_VALUE 	VARCHAR2(4000),
	CONSTRAINT FK_API_PARAM_API_SEQ FOREIGN KEY (API_SEQ) REFERENCES MP_API (API_SEQ)
); 
COMMENT ON TABLE MP_API_PARAM IS 'API 파라미터 정보';
CREATE SEQUENCE SEQ_MP_API_PARAM_PS INCREMENT BY 1 START WITH 1 MAXVALUE 9223372036854775807 NOORDER;   
COMMENT ON COLUMN MP_API_PARAM.PARAM_SEQ  IS '시퀀스'; 
COMMENT ON COLUMN MP_API_PARAM.API_SEQ  IS '어떤 API에 대한 파라미터인지'; 
COMMENT ON COLUMN MP_API_PARAM.DATA_TYPE  IS '파라미터의 데이터 타입'; 
COMMENT ON COLUMN MP_API_PARAM.PARAM_DESC  IS '설명'; 
COMMENT ON COLUMN MP_API_PARAM.PARAM_NAME  IS '이름'; 
COMMENT ON COLUMN MP_API_PARAM.SAMPLE_VALUE  IS '샘플 값'; 
 
---------------------------------------------------------------------------------------------- 
CREATE TABLE MP_MENU 
(	
	MENU_SEQ 		NUMBER(20,0),
	MENU_ID 		VARCHAR2(255), 
	PARENT_MENU_ID 	VARCHAR2(255), 
	MENU_ORDER 		NUMBER(*,0), 
	MENU_TYPE 		VARCHAR2(50) DEFAULT 'MAIN', 
	MENU_NAME 		VARCHAR2(255), 
	ENABLED_YN 		CHAR(1) DEFAULT 'N', 
	ACCESS_URI_ADDR VARCHAR2(1024), 
	IMAGE_URI_ADDR 	VARCHAR2(1024), 
	MENU_DESC 		VARCHAR2(500), 
	CONSTRAINT PK_MP_MENU PRIMARY KEY (MENU_ID),
	CONSTRAINT UQ_MENU_SEQ_ID UNIQUE (MENU_SEQ, MENU_ID)
); 
COMMENT ON TABLE MP_MENU IS '메뉴 정보';
CREATE SEQUENCE SEQ_MP_MENU INCREMENT BY 1 START WITH 1 MAXVALUE 9223372036854775807 NOORDER;
COMMENT ON COLUMN MP_MENU.MENU_SEQ  IS '시퀀스'; 
COMMENT ON COLUMN MP_MENU.MENU_ID  IS '식별자'; 
COMMENT ON COLUMN MP_MENU.PARENT_MENU_ID  IS '부모메뉴 식별자'; 
COMMENT ON COLUMN MP_MENU.MENU_ORDER  IS '표시순서'; 
COMMENT ON COLUMN MP_MENU.MENU_TYPE  IS '메뉴 유형 (NAVI, SIDE, CONT..)'; 
COMMENT ON COLUMN MP_MENU.MENU_NAME  IS '메뉴명 (사용자가 보는 text)'; 
COMMENT ON COLUMN MP_MENU.ENABLED_YN  IS '사용되는지 여부'; 
COMMENT ON COLUMN MP_MENU.ACCESS_URI_ADDR  IS '접근주소'; 
COMMENT ON COLUMN MP_MENU.IMAGE_URI_ADDR  IS '이미지 주소'; 
COMMENT ON COLUMN MP_MENU.MENU_DESC  IS '설명'; 

---------------------------------------------------------------------------------------------- 
CREATE TABLE MP_MENU_PARAM 
(
	MENU_PARAM_SEQ	NUMBER(20,0), 	
	MENU_ID 		VARCHAR2(255), 
	PARAM_NAME 		VARCHAR2(255) NOT NULL, 
	DATA_TYPE 		VARCHAR2(50) NOT NULL, 
	PARAM_VALUE 	VARCHAR2(4000), 
	PARAM_DESC		VARCHAR2(500), 
	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 ON TABLE MP_MENU_PARAM IS '메뉴 파라미터 정보';
CREATE SEQUENCE SEQ_MP_MENU_PARAM_SEQ_MPS INCREMENT BY 1 START WITH 1 MAXVALUE 9223372036854775807 NOORDER;   
COMMENT ON COLUMN MP_MENU_PARAM.MENU_PARAM_SEQ IS '파라미터 SEQ'; 
COMMENT ON COLUMN MP_MENU_PARAM.MENU_ID IS '메뉴 ID'; 
COMMENT ON COLUMN MP_MENU_PARAM.PARAM_NAME IS '파라미터명'; 
COMMENT ON COLUMN MP_MENU_PARAM.DATA_TYPE IS '데이터 타입'; 
COMMENT ON COLUMN MP_MENU_PARAM.PARAM_VALUE IS '파리미터 값'; 
COMMENT ON COLUMN MP_MENU_PARAM.PARAM_DESC IS '파라미터 설명'; 

----------------------------------------------------------------------------------------------
CREATE TABLE MP_I18N 
(	
    MENU_ID 	VARCHAR2(255), 
	I18N_CD		CHAR(2), 
	CD_TEXT 	VARCHAR2(255),
	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 ON TABLE MP_MENU_PARAM IS '메뉴에 적용되는 다국어 정보';
COMMENT ON COLUMN MP_I18N.MENU_ID  IS '메뉴 ID';     
COMMENT ON COLUMN MP_I18N.I18N_CD  IS '언어코드'; 
COMMENT ON COLUMN MP_I18N.CD_TEXT  IS '문자열'; 

---------------------------------------------------------------------------------------------- 

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

---------------------------------------------------------------------------------------------- 
CREATE TABLE MP_TELNO 
(	
	TELNO_SEQ 		NUMBER(20,0), 
	BUILDING_DESC 	VARCHAR2(500), 
	ROOM_DESC 		VARCHAR2(500), 
	TEL_VALUE 		VARCHAR2(255), 
	TELNO_ORDER 	NUMBER(*,0) DEFAULT 1
); 
COMMENT ON TABLE MP_TELNO IS '병원 전화번호';
CREATE SEQUENCE SEQ_MP_TELNO_TS INCREMENT BY 1 START WITH 1 MAXVALUE 9223372036854775807 NOORDER;  
COMMENT ON COLUMN MP_TELNO.TELNO_SEQ  IS '시퀀스'; 
COMMENT ON COLUMN MP_TELNO.BUILDING_DESC  IS '건물'; 
COMMENT ON COLUMN MP_TELNO.ROOM_DESC  IS '건물내의 방'; 
COMMENT ON COLUMN MP_TELNO.TEL_VALUE  IS '전화번호'; 
COMMENT ON COLUMN MP_TELNO.TELNO_ORDER  IS '표시순서'; 

---------------------------------------------------------------------------------------------- 
CREATE TABLE MP_AGREEMENT 
(	
	AGREEMENT_SEQ		NUMBER(20,0), 
	AGREEMENT_ORDER		NUMBER(*,0), 
	AGREEMENT_NAME		VARCHAR2(255) NOT NULL, 
	AGREEMENT_CL		CLOB NOT NULL,
	AGREEMENT_ID		VARCHAR2(255 BYTE), 
	TYPE_NAME			VARCHAR2(255) NOT NULL, 
	VERSION_NUMBER 		NUMBER(20,0), 	
	REQUIRED_YN 		CHAR(1) DEFAULT 'N',
	ENABLED_YN 	 	 	CHAR(1) DEFAULT 'N', 
	NEW_YN 		    	CHAR(1) DEFAULT 'Y', 
	CONSTRAINT PK_AGREEMENT_SEQ PRIMARY KEY (AGREEMENT_SEQ)
); 
COMMENT ON TABLE MP_AGREEMENT IS '서비스 약관(동의서) 정보'; 
CREATE SEQUENCE SEQ_MP_AGREEMENT INCREMENT BY 1 START WITH 1 MAXVALUE 9223372036854775807 NOORDER;  
COMMENT ON COLUMN MP_AGREEMENT.AGREEMENT_SEQ  IS '시퀀스'; 
COMMENT ON COLUMN MP_AGREEMENT.AGREEMENT_ORDER  IS '동의서 순서'; 
COMMENT ON COLUMN MP_AGREEMENT.AGREEMENT_NAME  IS '동의서 이름'; 
COMMENT ON COLUMN MP_AGREEMENT.AGREEMENT_CL  IS '동의서 내용';
COMMENT ON COLUMN MP_AGREEMENT.AGREEMENT_ID  IS '동의서 식별자';
COMMENT ON COLUMN MP_AGREEMENT.TYPE_NAME  IS '동의서유형(ALL, MINOR). 현재는 일반/미성년자만 있지만 조만간 외국인도 생길듯하여 NAME으로함';
COMMENT ON COLUMN MP_AGREEMENT.VERSION_NUMBER  IS '동의서 버전 ';
COMMENT ON COLUMN MP_AGREEMENT.REQUIRED_YN  IS '필수 여부';
COMMENT ON COLUMN MP_AGREEMENT.ENABLED_YN IS '활성화 여부';
COMMENT ON COLUMN MP_AGREEMENT.NEW_YN IS '최신 동의서 여부';
---------------------------------------------------------------------------------------------- 
CREATE TABLE MP_USER_AGREEMENT
(
   USER_AGREEMENT_SEQ	NUMBER (20),
   AGREEMENT_SEQ		NUMBER (20) NOT NULL,
   AGREEMENT_ID			VARCHAR2(255) NOT NULL,
   USER_ID				VARCHAR2(255) NOT NULL,
   AGREEMENT_YN			CHAR (1) DEFAULT 'N', 
   REGISTER_DT			DATE,
   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 ON TABLE MP_USER_AGREEMENT IS '서비스 약관(동의서) 동의여부'; 
CREATE SEQUENCE SEQ_MP_USER_AGREEMENT_UA INCREMENT BY 1 START WITH 1 MAXVALUE 9223372036854775807 NOORDER; 
COMMENT ON COLUMN MP_USER_AGREEMENT.USER_AGREEMENT_SEQ IS '시퀀스'; 
COMMENT ON COLUMN MP_USER_AGREEMENT.AGREEMENT_SEQ IS '동의서 시퀀스'; 
COMMENT ON COLUMN MP_USER_AGREEMENT.AGREEMENT_ID IS '동의서 ID'; 
COMMENT ON COLUMN MP_USER_AGREEMENT.USER_ID IS '사용자 ID'; 
COMMENT ON COLUMN MP_USER_AGREEMENT.AGREEMENT_YN IS '동의여부 (Y/N)';
COMMENT ON COLUMN MP_USER_AGREEMENT.REGISTER_DT IS '등록일시'; 


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


-- 푸쉬서버 볼 때 체크하기

CREATE TABLE MP_PUSH_FORM (
	FORM_ID 			VARCHAR2(255), 
	FORM_TYPE			VARCHAR2(50), 
	USE_YN				CHAR(1), 
	MENU_ID				VARCHAR2(255),
	INCLUDE_NAME_YN 	CHAR(1), 
	FORM_DESC			VARCHAR2(500),
	REG_DT				DATE DEFAULT SYSDATE,  
	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 ON TABLE MP_PUSH_FORM 					IS '사용자에게 전송될 메시지 형식';
COMMENT ON COLUMN MP_PUSH_FORM.FORM_ID 			IS '메시지 형식에 대한 식별자'; 
COMMENT ON COLUMN MP_PUSH_FORM.FORM_TYPE  		IS '유형 : PAGE, MAP, GUIDE';
COMMENT ON COLUMN MP_PUSH_FORM.USE_YN	 		IS '사용여부 (PUSH_MSG쪽에서 FK로 참조될꺼라서 삭제기능이 없을것임)'; 
COMMENT ON COLUMN MP_PUSH_FORM.MENU_ID	 		IS '유형이 PAGE인 경우 메뉴 식별자. 확장자가 page인 메뉴만 가능'; 
COMMENT ON COLUMN MP_PUSH_FORM.INCLUDE_NAME_YN	IS '메시지에 이름포함 여부'; 
COMMENT ON COLUMN MP_PUSH_FORM.FORM_DESC 		IS '형식에 대한 설명';
COMMENT ON COLUMN MP_PUSH_FORM.REG_DT  			IS '추가된 시간';


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

---------------------------------------------------------------------------------------------- 
CREATE TABLE MP_USER
(
   USER_SEQ                 NUMBER (20),
   USER_ID            		VARCHAR2 (255),
   USER_NAME               	VARCHAR2 (255),
   LOGIN_FAIL_CNT           NUMBER (10),
   PASSWORD_VALUE           VARCHAR2 (255),
   PASSWORD_UPDATE_DT       DATE,
   REGISTER_DT              DATE,
   LOCAL_CIPHER_KEY_VALUE   VARCHAR2 (255),
   USE_ATTR_ORDER			NUMBER (20),
   CONSTRAINT PK_USER_ID PRIMARY KEY (USER_ID)
); 
COMMENT ON TABLE MP_USER IS '사용자 정보'; 
CREATE SEQUENCE SEQ_MP_USER_US INCREMENT BY 1 START WITH 1 MAXVALUE 9223372036854775807 NOORDER; 
COMMENT ON COLUMN MP_USER.USER_SEQ IS '시퀀스'; 
COMMENT ON COLUMN MP_USER.USER_ID IS '사용자 ID'; 
COMMENT ON COLUMN MP_USER.USER_NAME IS '사용자 이름'; 
COMMENT ON COLUMN MP_USER.LOGIN_FAIL_CNT IS '사용자 이름'; 
COMMENT ON COLUMN MP_USER.PASSWORD_VALUE IS '로그인 암호 (sha-256)'; 
COMMENT ON COLUMN MP_USER.REGISTER_DT IS '등록일';  

---------------------------------------------------------------------------------------------- 
CREATE TABLE MNS_RECEIVER_DEVICE 
(	
	RECEIVER_ID			VARCHAR2(255) NOT NULL, 
	DEVICE_TOKEN_ID		VARCHAR2(255), 
	PLATFORM_TYPE		VARCHAR2(50) NOT NULL,
	CERT_TYPE			VARCHAR2(50) DEFAULT '',
	REGISTER_DT			DATE DEFAULT SYSDATE, 
	UPDATE_DT			DATE DEFAULT SYSDATE, 
	CONSTRAINT UQ_RECEIVER_TOKEN_ID UNIQUE (RECEIVER_ID, DEVICE_TOKEN_ID, PLATFORM_TYPE)
); 
COMMENT ON TABLE MNS_RECEIVER_DEVICE IS 'device token 정보'; 
COMMENT ON COLUMN MNS_RECEIVER_DEVICE.RECEIVER_ID  IS '수신자 아이디 (예 : 사용자ID)';
COMMENT ON COLUMN MNS_RECEIVER_DEVICE.DEVICE_TOKEN_ID  IS '토큰';
COMMENT ON COLUMN MNS_RECEIVER_DEVICE.PLATFORM_TYPE  IS '플랫폼 구분 (A : Android, I : iPhone)'; 
COMMENT ON COLUMN MNS_RECEIVER_DEVICE.CERT_TYPE	IS '인증유형 구분 (InHouse, AppStore 등..)';
COMMENT ON COLUMN MNS_RECEIVER_DEVICE.REGISTER_DT  IS '토큰 등록일';
COMMENT ON COLUMN MNS_RECEIVER_DEVICE.UPDATE_DT  IS '토큰 수정일';
---------------------------------------------------------------------------------------------- 

CREATE TABLE MNS_CONTENTS 
(	
	CONTENTS_SEQ		NUMBER(20,0), 
	CONTENTS_MSG		VARCHAR2(4000) NOT NULL, 
	SENDER_ID			VARCHAR2(100) NOT NULL, 
	CREATE_DT			DATE DEFAULT SYSDATE, 
	RESERVE_SEND_DT		DATE DEFAULT SYSDATE, 
	CONSTRAINT PK_MNS_CONTENTS PRIMARY KEY (CONTENTS_SEQ)
); 
COMMENT ON TABLE MNS_CONTENTS IS '전송될 메시지 정보'; 
CREATE SEQUENCE SEQ_MNS_CONTENTS INCREMENT BY 1 START WITH 1 MAXVALUE 9223372036854775807 NOORDER;   
COMMENT ON COLUMN MNS_CONTENTS.CONTENTS_SEQ  IS '시퀀스';
COMMENT ON COLUMN MNS_CONTENTS.CONTENTS_MSG  IS '메시지 내용';
COMMENT ON COLUMN MNS_CONTENTS.SENDER_ID  IS '발송자아이디';
COMMENT ON COLUMN MNS_CONTENTS.CREATE_DT  IS '레코드 등록일시';
COMMENT ON COLUMN MNS_CONTENTS.RESERVE_SEND_DT  IS '예약전송일시';

---------------------------------------------------------------------------------------------- 

CREATE TABLE MNS_CONTENTS_RESULT 
(	
	CONTENTS_SEQ	NUMBER(20,0), 
	COMPLETE_DT		DATE NOT NULL,
	CONSTRAINT FK_CONTENTS_SEQ FOREIGN KEY (CONTENTS_SEQ) REFERENCES MNS_CONTENTS (CONTENTS_SEQ), 
	CONSTRAINT UQ_CONTENTS_SEQ UNIQUE (CONTENTS_SEQ)
); 
COMMENT ON TABLE MNS_CONTENTS_RESULT IS '메시지 전송결과';
COMMENT ON COLUMN MNS_CONTENTS_RESULT.CONTENTS_SEQ  IS '발송순서'; 
COMMENT ON COLUMN MNS_CONTENTS_RESULT.COMPLETE_DT  IS '전체 수신자에 대한 발송완료 일시'; 
  
---------------------------------------------------------------------------------------------- 
 
CREATE TABLE MNS_RECEIVER 
(	
	CONTENTS_SEQ	NUMBER(20,0) , 
	RECEIVER_ID		VARCHAR2(255) NOT NULL,
	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) VALIDATE
); 
COMMENT ON TABLE MNS_RECEIVER IS '메시지 수신자';
COMMENT ON COLUMN MNS_RECEIVER.CONTENTS_SEQ  IS '발송순서';   
COMMENT ON COLUMN MNS_RECEIVER.RECEIVER_ID  IS '수신자 아이디';   

---------------------------------------------------------------------------------------------- 

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

---------------------------------------------------------------------------------------------- 
-- 푸쉬서버 볼때 체크하기
CREATE TABLE MP_MNS_MSGMAPPING 
(
	USER_ID			VARCHAR2 (255) NOT NULL,
	CONTENTS_SEQ	NUMBER(20,0) NOT NULL,
	FORM_ID			VARCHAR2(255) NOT NULL, 	
	MESSAGE_VALUE	VARCHAR2(255) NOT NULL,
	LOG_DT			DATE DEFAULT SYSDATE 
); 
COMMENT ON TABLE MP_MNS_MSGMAPPING IS '메시지 추적을 위한 맵핑정보';
CREATE INDEX IDX_MSGMAPPING_CONTENTS ON MP_MNS_MSGMAPPING(USER_ID); 
COMMENT ON COLUMN MP_MNS_MSGMAPPING.USER_ID			 IS '사용자 ID (검색용)';
COMMENT ON COLUMN MP_MNS_MSGMAPPING.CONTENTS_SEQ 	 IS 'PUSH 메시지 구분자';
COMMENT ON COLUMN MP_MNS_MSGMAPPING.FORM_ID 		 IS '메시지 유형';
COMMENT ON COLUMN MP_MNS_MSGMAPPING.MESSAGE_VALUE	 IS '메시지 본문 내용';
COMMENT ON COLUMN MP_MNS_MSGMAPPING.LOG_DT  		 IS '로그 남기는 시간';


---------------------------- MP_AUTH 권한/속성 관리 --------------------------
-- 속성타입은 삭제함 (MP_ATTR_TYPE)
-- USER_ATTR 에서 컬럼이  hospital_id, duty_id, work_id 로 구성되어 있어서, 속성타입을 수정하면 이 구조에서는 USER_ATTR 테이블을 수정하고 모든 쿼리를 수정해야함
-- 따라서 속성타임은 hospital, duty, work 로 고정하고, 소스코드에서도 정의해서 사용하도록 한다.


CREATE TABLE MP_ATTR
(
	ATTR_SEQ		NUMBER(20),
	ATTR_TYPE		VARCHAR2(20) NOT NULL,
	ATTR_ID			VARCHAR2(255) NOT NULL,
	ATTR_NAME		VARCHAR2(255) NOT NULL,
	UPPER_ATTR		VARCHAR2(255) DEFAULT 'none',
	CONSTRAINT UQ_MP_ATTR UNIQUE (ATTR_TYPE, ATTR_ID)
);
CREATE SEQUENCE SEQ_MP_ATTR INCREMENT BY 1 START WITH 1 MAXVALUE 9223372036854775807 NOORDER;
COMMENT ON TABLE MP_ATTR IS '업무 속성';
COMMENT ON COLUMN MP_ATTR.ATTR_SEQ IS '업무 속성 SEQ';
COMMENT ON COLUMN MP_ATTR.ATTR_TYPE IS '업무 속성 타입';
COMMENT ON COLUMN MP_ATTR.ATTR_ID	IS '업무 속성 ID';
COMMENT ON COLUMN MP_ATTR.ATTR_NAME IS '업무 속성 이름';

--

CREATE TABLE MP_USER_ATTR
(
	USER_ATTR_SEQ		NUMBER (20),
	USER_ATTR_ORDER		NUMBER (*,0)  NOT NULL, 
	USER_ID				VARCHAR2(255) NOT NULL,
	HOSPITAL_ID			VARCHAR2(255) NOT NULL,
  	DUTY_ID				VARCHAR2(255) NOT NULL,
  	WORK_ID				VARCHAR2(255) NOT NULL,
  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 ON TABLE MP_USER_ATTR IS '사용자 업무 테이블'; 
CREATE SEQUENCE SEQ_MP_USER_ATTR INCREMENT BY 1 START WITH 1 MAXVALUE 9223372036854775807 NOORDER;
COMMENT ON COLUMN MP_USER_ATTR.USER_ATTR_SEQ		IS '사용자 업무 SEQ';
COMMENT ON COLUMN MP_USER_ATTR.USER_ATTR_ORDER		IS '사용자 업무 우선순위';
COMMENT ON COLUMN MP_USER_ATTR.USER_ID 				IS '사용자 ID';
COMMENT ON COLUMN MP_USER_ATTR.HOSPITAL_ID			IS '소속 ID'; 
COMMENT ON COLUMN MP_USER_ATTR.DUTY_ID 				IS '직무 ID'; 
COMMENT ON COLUMN MP_USER_ATTR.WORK_ID				IS '업무 ID';

--

CREATE TABLE MP_EXTRA_ATTR
(
	EXTRA_ATTR_SEQ		NUMBER(20),
	EXTRA_ATTR_ID		VARCHAR2(255) 	NOT NULL,
	EXTRA_ATTR_NAME		VARCHAR2(255)	NOT NULL,
  	EXTRA_ATTR_DESC		VARCHAR2(500),
  	CONSTRAINT PK_EXTRA_WORK_ID PRIMARY KEY (EXTRA_ATTR_ID), 
	CONSTRAINT UQ_MP_EXTRA_ATTR UNIQUE (EXTRA_ATTR_ID, EXTRA_ATTR_NAME)
);
COMMENT ON TABLE MP_EXTRA_ATTR IS '확장 속성'; 
CREATE SEQUENCE SEQ_MP_EXTRA_ATTR INCREMENT BY 1 START WITH 1 MAXVALUE 9223372036854775807 NOORDER;
COMMENT ON COLUMN MP_EXTRA_ATTR.EXTRA_ATTR_SEQ 				IS '업무 SEQ'; 
COMMENT ON COLUMN MP_EXTRA_ATTR.EXTRA_ATTR_ID 				IS '확장 업무 ID';
COMMENT ON COLUMN MP_EXTRA_ATTR.EXTRA_ATTR_NAME 			IS '확장 업무 이름'; 
COMMENT ON COLUMN MP_EXTRA_ATTR.EXTRA_ATTR_DESC 			IS '확장 업무 설명'; 

--

CREATE TABLE MP_EXTRA_ATTR_USER
(
	EXTRA_ATTR_USER_SEQ		NUMBER(20),
	EXTRA_ATTR_ID			VARCHAR2(255) NOT NULL,
	USER_ID					VARCHAR2(255) NOT NULL,
  EXTRA_ATTR_USER_ORDER   NUMBER(*,0),
  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 ON TABLE MP_EXTRA_ATTR_USER 			IS '확장 업무 사용자'; 
CREATE SEQUENCE SEQ_MP_EXTRA_ATTR_USER INCREMENT BY 1 START WITH 1 MAXVALUE 9223372036854775807 NOORDER;
COMMENT ON COLUMN MP_EXTRA_ATTR_USER.EXTRA_ATTR_USER_SEQ 	IS '확장 업무 사용자 SEQ'; 
COMMENT ON COLUMN MP_EXTRA_ATTR_USER.EXTRA_ATTR_ID       	IS '확장 업무 ID';
COMMENT ON COLUMN MP_EXTRA_ATTR_USER.USER_ID               	IS '확장 업무 사용자 ID'; 

--

CREATE TABLE MP_AUTH
(
	AUTH_SEQ		NUMBER(20),
	AUTH_ID			VARCHAR2(255) NOT NULL,
	AUTH_NAME		VARCHAR2(255) NOT NULL,
 	AUTH_DESC		VARCHAR2(500),
  	CONSTRAINT PK_AUTH_ID PRIMARY KEY (AUTH_ID), 
	CONSTRAINT UQ_MP_AUTH UNIQUE (AUTH_ID, AUTH_NAME)
);
COMMENT ON TABLE MP_AUTH 					IS '권한'; 
CREATE SEQUENCE SEQ_MP_AUTH INCREMENT BY 1 START WITH 1 MAXVALUE 9223372036854775807 NOORDER;
COMMENT ON COLUMN MP_AUTH.AUTH_SEQ			IS '권한 SEQ'; 
COMMENT ON COLUMN MP_AUTH.AUTH_ID			IS '권한 ID';
COMMENT ON COLUMN MP_AUTH.AUTH_NAME 		IS '권한 이름'; 

--

CREATE TABLE MP_AUTH_ATTR
(
	AUTH_ATTR_SEQ		NUMBER(20),
	AUTH_ID				VARCHAR2(255) NOT NULL,
	AUTH_TYPE			VARCHAR2(255) NOT NULL,
	AUTH_ATTR_TYPE		VARCHAR2(255) NOT NULL,
	AUTH_ATTR_ID		VARCHAR2(255) NOT NULL,
	CONSTRAINT FK_AUTH_ID FOREIGN KEY (AUTH_ID) REFERENCES MP_AUTH(AUTH_ID)
);
COMMENT ON TABLE MP_AUTH_ATTR 							IS '권한 속성';
CREATE INDEX MP_AUTH_ATTR_IDX1 ON MP_AUTH_ATTR(AUTH_ATTR_ID, AUTH_ATTR_TYPE, AUTH_TYPE);
CREATE SEQUENCE SEQ_MP_AUTH_ATTR INCREMENT BY 1 START WITH 1 MAXVALUE 9223372036854775807 NOORDER;
COMMENT ON COLUMN MP_AUTH_ATTR.AUTH_ATTR_SEQ			IS '권한 속성 SEQ'; 
COMMENT ON COLUMN MP_AUTH_ATTR.AUTH_ID					IS '권한 ID';
COMMENT ON COLUMN MP_AUTH_ATTR.AUTH_TYPE	 			IS '권한 타입 (ADD, MINUS)';
COMMENT ON COLUMN MP_AUTH_ATTR.AUTH_ATTR_TYPE 			IS '권한 속성 타입'; 
COMMENT ON COLUMN MP_AUTH_ATTR.AUTH_ATTR_ID 			IS '권한 속성 ID';

--

CREATE TABLE MP_MENU_AUTH
(
	MENU_AUTH_SEQ	NUMBER(20),
	MENU_ID			VARCHAR2(255) NOT NULL,
	AUTH_ID			VARCHAR2(255) NOT NULL,
	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 ON TABLE MP_MENU_AUTH 							IS '메뉴 권한 속성';
CREATE SEQUENCE SEQ_MP_MENU_AUTH INCREMENT BY 1 START WITH 1 MAXVALUE 9223372036854775807 NOORDER;
COMMENT ON COLUMN MP_MENU_AUTH.MENU_AUTH_SEQ			IS '메뉴 권한 SEQ'; 
COMMENT ON COLUMN MP_MENU_AUTH.MENU_ID					IS '메뉴 ID';
COMMENT ON COLUMN MP_MENU_AUTH.AUTH_ID		 			IS '권한 ID';


--------------- Device 정보
CREATE TABLE MP_USER_DEVICE
(
	DEVICE_SEQ			NUMBER(20),
	USER_ID				VARCHAR2(255) NOT NULL,
	DEVICE_ID			VARCHAR2(255) NOT NULL,
	DEVICE_MODEL		VARCHAR2(255) NOT NULL,
  	DEVICE_TYPE			VARCHAR2(255) NOT NULL,
	DEVICE_ALIAS		VARCHAR2(255) NOT NULL,
	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 ON TABLE MP_USER_DEVICE 							IS '사용자 Device';
CREATE SEQUENCE SEQ_MP_USER_DEVICE INCREMENT BY 1 START WITH 1 MAXVALUE 9223372036854775807 NOORDER;
COMMENT ON COLUMN MP_USER_DEVICE.DEVICE_SEQ					IS '사용자 Device SEQ'; 
COMMENT ON COLUMN MP_USER_DEVICE.USER_ID					IS '사용자 ID';
COMMENT ON COLUMN MP_USER_DEVICE.DEVICE_ID	 				IS 'Device ID';
COMMENT ON COLUMN MP_USER_DEVICE.DEVICE_MODEL		 		IS 'Device Model(iPhone6s, 갤럭시s6)';
COMMENT ON COLUMN MP_USER_DEVICE.DEVICE_TYPE       			IS 'Device Type (iOS, android)';
COMMENT ON COLUMN MP_USER_DEVICE.DEVICE_ALIAS		 		IS '사용자 장비 별칭';

CREATE TABLE MP_PUBLIC_DEVICE
(
	PUBLICE_DEVICE_SEQ 		NUMBER(20),
	DEVICE_ID				VARCHAR2(255) NOT NULL,
	DEVICE_MODEL			VARCHAR2(255) NOT NULL,
 	DEVICE_TYPE				VARCHAR2(255) NOT NULL,
	DEVICE_ALIAS			VARCHAR2(255) NOT NULL,
	CONSTRAINT UQ_MP_PUBLICE_DEVICE UNIQUE (DEVICE_ID)
);
COMMENT ON TABLE MP_PUBLIC_DEVICE 							IS '공용 Device';
CREATE SEQUENCE SEQ_MP_PUBLIC_DEVICE INCREMENT BY 1 START WITH 1 MAXVALUE 9223372036854775807 NOORDER;
COMMENT ON COLUMN MP_PUBLIC_DEVICE.PUBLICE_DEVICE_SEQ		IS '공용 Device SEQ'; 
COMMENT ON COLUMN MP_PUBLIC_DEVICE.DEVICE_ID	    		IS 'Device ID';
COMMENT ON COLUMN MP_PUBLIC_DEVICE.DEVICE_MODEL				IS 'Device Model(iPhone6s, 갤럭시s6)';
COMMENT ON COLUMN MP_PUBLIC_DEVICE.DEVICE_TYPE         		IS 'Device Type (iOS, android)';
COMMENT ON COLUMN MP_PUBLIC_DEVICE.DEVICE_ALIAS				IS '공용 장비 별칭';


COMMIT;