consentFatima/mplus-fatima/script/create_Oracle_Table_For_MPlus.sql

---------------------------------------------------------------------------------------------- 
---------- HOSPITAL TABLE 생성
---------------------------------------------------------------------------------------------- 
CREATE TABLE MP_ACCESS_HISTORY 
(
	HISTORY_SEQ	 		NUMBER(20,0) DEFAULT 0, 
	USER_ID  			VARCHAR2 (255),
---	DEVICE_UUID_ID 		VARCHAR2(255), 
	MENU_ID 			VARCHAR2(255), 
	MENU_NAME 			VARCHAR2(255), 
	REQ_URI_ADDR 		VARCHAR2(1024), 
	REMOTE_IP_ADDR 		VARCHAR2(1024), 
	ACCESS_DT 			DATE, 
	PLATFORM_TYPE 		VARCHAR2(50), 
	BROWSER_VALUE 		VARCHAR2(255), 
	SERVER_IP_ADDR 		VARCHAR2(1024),
	ACCESS_HASH_VALUE 	VARCHAR2(255) 
);
COMMENT ON TABLE MP_ACCESS_HISTORY IS '사용자의 접근 이력';
CREATE SEQUENCE SEQ_MP_ACCESS_HISTORY_HS INCREMENT BY 1 START WITH 1 MAXVALUE 9223372036854775807 NOORDER;
COMMENT ON COLUMN MP_ACCESS_HISTORY.HISTORY_SEQ  		IS '시퀀스'; 
COMMENT ON COLUMN MP_ACCESS_HISTORY.USER_ID 	 		IS '사용자 ID'; 
--- COMMENT ON COLUMN MP_ACCESS_HISTORY.DEVICE_UUID_ID  IS '장비 고유식별자 or IP'; 
COMMENT ON COLUMN MP_ACCESS_HISTORY.MENU_ID  			IS 'access원하는 메뉴식별자'; 
COMMENT ON COLUMN MP_ACCESS_HISTORY.MENU_NAME  			IS 'access원하는 메뉴명 (메뉴가 동적으로 바뀔 수 있으므로 이름백업)'; 
COMMENT ON COLUMN MP_ACCESS_HISTORY.REQ_URI_ADDR  		IS 'access 주소 (ui의 action)'; 
COMMENT ON COLUMN MP_ACCESS_HISTORY.REMOTE_IP_ADDR  	IS 'client의 ip'; 
COMMENT ON COLUMN MP_ACCESS_HISTORY.ACCESS_DT  			IS '요청일시'; 
COMMENT ON COLUMN MP_ACCESS_HISTORY.PLATFORM_TYPE 	 	IS 'http 요청의 user agent 분석을 통해 결정되는 플랫폼 유형'; 
COMMENT ON COLUMN MP_ACCESS_HISTORY.BROWSER_VALUE  		IS 'http 요청의 user agent 분석을 통해 결정되는 브라우저 유형'; 
COMMENT ON COLUMN MP_ACCESS_HISTORY.SERVER_IP_ADDR  	IS '서버주소'; 
COMMENT ON COLUMN MP_ACCESS_HISTORY.ACCESS_HASH_VALUE  	IS '접근이력 위변조 방지용'; 

---------------------------------------------------------------------------------------------- 
CREATE TABLE MP_AGG_ACCESS 
(	
  	ACCESS_SEQ 		NUMBER(20,0), 
	MENU_ID 		VARCHAR2(255), 
	MENU_NAME 		VARCHAR2(255), 
	REQ_URI_ADDR 	VARCHAR2(1024), 
	HIT_CNT 		NUMBER(*,0), 
	AGG_DT 			DATE, 
	CONSTRAINT UQ_ACCESS_DT_MENU_ID UNIQUE (AGG_DT, MENU_ID)
); 
COMMENT ON TABLE MP_AGG_ACCESS IS '메뉴 접근에 대한 통계';
CREATE SEQUENCE SEQ_MP_AGG_ACCESS_AS INCREMENT BY 1 START WITH 1 MAXVALUE 9223372036854775807 NOORDER;
COMMENT ON COLUMN MP_AGG_ACCESS.ACCESS_SEQ  IS '시퀀스'; 
COMMENT ON COLUMN MP_AGG_ACCESS.MENU_ID  IS '메뉴ID'; 
COMMENT ON COLUMN MP_AGG_ACCESS.MENU_NAME  IS '메뉴명'; 
COMMENT ON COLUMN MP_AGG_ACCESS.REQ_URI_ADDR  IS 'access 주소 (ui의 action)'; 
COMMENT ON COLUMN MP_AGG_ACCESS.HIT_CNT  IS '방문횟수'; 
COMMENT ON COLUMN MP_AGG_ACCESS.AGG_DT  IS '통계일시 (이 시간에 해당 메뉴에 얼마나 방문했는지)'; 
 
---------------------------------------------------------------------------------------------- 
CREATE TABLE MP_AGG_DAILY 
(	
   	DAILY_SEQ 	NUMBER(20,0), 
	AGG_DT 		DATE, 
	HIT_CNT 	NUMBER(*,0), 
	WEEK_ORDER 	NUMBER(*,0), 
	CONSTRAINT UQ_AGG_DT_WEEK_ORDER UNIQUE (AGG_DT, WEEK_ORDER)
);
COMMENT ON TABLE MP_AGG_DAILY IS '날짜별, 요일별 접근 통계';
CREATE SEQUENCE SEQ_MP_AGG_DAILY_DS INCREMENT BY 1 START WITH 1 MAXVALUE 9223372036854775807 NOORDER;
COMMENT ON COLUMN MP_AGG_DAILY.DAILY_SEQ  IS '시퀀스'; 
COMMENT ON COLUMN MP_AGG_DAILY.AGG_DT  IS '통계일시 (이 시간에 해당 메뉴에 얼마나 방문했는지)'; 
COMMENT ON COLUMN MP_AGG_DAILY.HIT_CNT  IS '방문횟수'; 
COMMENT ON COLUMN MP_AGG_DAILY.WEEK_ORDER  IS '무슨요일인지'; 
   
---------------------------------------------------------------------------------------------- 
CREATE TABLE MP_AGG_HOURLY 
(	
	HOURLY_SEQ 	NUMBER(20,0), 
	AGG_DT 		DATE, 
	HIT_CNT 	NUMBER(*,0), 
	TIME_ORDER 	NUMBER(*,0), 
	CONSTRAINT UQ_AGG_DT_TIME_ORDER UNIQUE (AGG_DT, TIME_ORDER)
); 
COMMENT ON TABLE MP_AGG_HOURLY IS '시간별 접근 통계';
CREATE SEQUENCE SEQ_MP_AGG_HOURLY_HS INCREMENT BY 1 START WITH 1 MAXVALUE 9223372036854775807  NOORDER;
COMMENT ON COLUMN MP_AGG_HOURLY.HOURLY_SEQ  IS '시퀀스'; 
COMMENT ON COLUMN MP_AGG_HOURLY.AGG_DT  IS '통계일시 (이 시간에 해당 메뉴에 얼마나 방문했는지)'; 
COMMENT ON COLUMN MP_AGG_HOURLY.HIT_CNT  IS '방문횟수'; 
COMMENT ON COLUMN MP_AGG_HOURLY.TIME_ORDER  IS '몇시인지'; 

---------------------------------------------------------------------------------------------- 
CREATE TABLE MP_AGG_PLATFORM 
(	
	PLATFORM_SEQ 	NUMBER(20,0), 
	PLATFORM_TYPE 	VARCHAR2(50), 
	HIT_CNT 		NUMBER(*,0), 
	AGG_DT 			DATE, 
	CONSTRAINT UQ_AGG_DT_PLATFROM_TYPE UNIQUE (AGG_DT, PLATFORM_TYPE)
); 
COMMENT ON TABLE MP_AGG_PLATFORM IS '사용자 플랫폼별 접근 통계';
CREATE SEQUENCE SEQ_MP_AGG_PLATFORM_PS INCREMENT BY 1 START WITH 1 MAXVALUE 9223372036854775807 NOORDER;  
COMMENT ON COLUMN MP_AGG_PLATFORM.PLATFORM_SEQ  IS '시퀀스'; 
COMMENT ON COLUMN MP_AGG_PLATFORM.PLATFORM_TYPE  IS '플랫폼'; 
COMMENT ON COLUMN MP_AGG_PLATFORM.AGG_DT  IS '통계일시 (이 시간에 해당 메뉴에 얼마나 방문했는지)'; 
COMMENT ON COLUMN MP_AGG_PLATFORM.HIT_CNT  IS '방문횟수'; 

---------------------------------------------------------------------------------------------- 
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_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), 
	AUTH_YN 		CHAR(1) DEFAULT 'Y', 
	AUTH_VIEW_YN 	CHAR(1) DEFAULT 'N', 
	AGG_YN 			CHAR(1) DEFAULT 'N', 
	CONSTRAINT PK_MP_MENU PRIMARY KEY (MENU_ID)
); 
COMMENT ON TABLE MP_MENU 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 '설명'; 
COMMENT ON COLUMN MP_MENU.AUTH_YN  IS '로그인한 사용자만 쓰는지 여부'; 
COMMENT ON COLUMN MP_MENU.AUTH_VIEW_YN  IS '로그인한 사용자에게 보일지 여부'; 
COMMENT ON COLUMN MP_MENU.AGG_YN  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_INF_NURSE (
  NURSE_SEQ      	NUMBER (20),
  USER_ID           VARCHAR2 (255),
  REQ_CODE_VALUE 	VARCHAR2(255), 
  REQ_VALUE     	VARCHAR2(255),
  REQ_ETC_VALUE 	VARCHAR2(255), 
  REG_DT        	DATE DEFAULT SYSDATE, 
  UPDATE_DT     	DATE, 
  RECEIVE_DT		DATE 
); 
COMMENT ON TABLE MP_INF_NURSE IS '간호요청';
CREATE SEQUENCE SEQ_MP_IF_NURSE_SEQ INCREMENT BY 1 START WITH 1 NOORDER; 
COMMENT ON COLUMN MP_INF_NURSE.NURSE_SEQ  		IS '시퀀스'; 
COMMENT ON COLUMN MP_INF_NURSE.USER_ID   		IS '사용자 ID'; 
COMMENT ON COLUMN MP_INF_NURSE.REQ_CODE_VALUE  	IS '요청코드나열 (화면에서 사용하기 편한 형태로)'; 
COMMENT ON COLUMN MP_INF_NURSE.REQ_VALUE  		IS '요청을 문자료 표시 (부산대 요구사항)'; 
COMMENT ON COLUMN MP_INF_NURSE.REQ_ETC_VALUE  	IS '주관식 요청 '; 
COMMENT ON COLUMN MP_INF_NURSE.REG_DT  			IS '요청일과 시간'; 
COMMENT ON COLUMN MP_INF_NURSE.UPDATE_DT  		IS '기간계에서 요청을 가져간 시간'; 
COMMENT ON COLUMN MP_INF_NURSE.RECEIVE_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 '추가된 시간';


---------------------------------------------------------------------------------------------- 
CREATE TABLE MP_EVENT_LOG (
	LOG_SEQ 		NUMBER(20) DEFAULT 0, 
	USER_ID 		VARCHAR2 (255),
---	DEVICE_UUID_ID 	VARCHAR2(255), 
	EVENT_NAME 		VARCHAR2(255) NOT NULL, 
	LOG_DT DATE DEFAULT SYSDATE 
); 
COMMENT ON TABLE MP_EVENT_LOG IS '비콘 이벤트';
CREATE SEQUENCE SEQ_MP_EVENT_LOG_LS INCREMENT BY 1 START WITH 1 NOORDER; 
COMMENT ON COLUMN MP_EVENT_LOG.LOG_SEQ 		IS '시퀀스'; 
COMMENT ON COLUMN MP_EVENT_LOG.USER_ID 		IS '사용자 ID (NULL일 수 있음)'; 
--- COMMENT ON COLUMN MP_EVENT_LOG.DEVICE_UUID_ID  IS '토큰ID (NULL일 수 있음)'; 
COMMENT ON COLUMN MP_EVENT_LOG.EVENT_NAME  	IS '이벤트 이름'; 
COMMENT ON COLUMN MP_EVENT_LOG.LOG_DT  		IS '이벤트 수신 시간 (발생시간이 아님)';

---------------------------------------------------------------------------------------------- 
CREATE TABLE MP_AGG_EVENT (
	AGG_SEQ 	NUMBER(20) DEFAULT 0, 
	EVENT_NAME 	VARCHAR2(255) NOT NULL, 
	HIT_CNT 	NUMBER(*,0), 
	AGG_DT 		DATE DEFAULT SYSDATE 
); 
COMMENT ON TABLE MP_AGG_EVENT IS '비콘 이벤트 집계';
CREATE SEQUENCE SEQ_MP_AGG_EVENT_AS INCREMENT BY 1 START WITH 1 NOORDER; 
COMMENT ON COLUMN MP_AGG_EVENT.AGG_SEQ  IS '시퀀스'; 
COMMENT ON COLUMN MP_AGG_EVENT.EVENT_NAME  IS '발생된 이벤트명'; 
COMMENT ON COLUMN MP_AGG_EVENT.HIT_CNT  IS '발생횟수'; 
COMMENT ON COLUMN MP_AGG_EVENT.AGG_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 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.REQUIRED_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 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_AGG_MSG (
	AGG_SEQ			NUMBER(20) DEFAULT 0, 
	AGG_DT			DATE DEFAULT SYSDATE, 
	TOTAL_CNT		NUMBER(*, 0), 
	SUCCESS_CNT		NUMBER(*, 0)
); 
COMMENT ON TABLE MP_AGG_MSG IS '메시지 전송 결과 집계정보';
CREATE SEQUENCE SEQ_MP_AGG_MSG_MS INCREMENT BY 1 START WITH 1 NOORDER; 
COMMENT ON COLUMN MP_AGG_MSG.AGG_SEQ  IS '시퀀스'; 
COMMENT ON COLUMN MP_AGG_MSG.AGG_DT  IS '집계날짜'; 
COMMENT ON COLUMN MP_AGG_MSG.TOTAL_CNT  IS '전송횟수'; 
COMMENT ON COLUMN MP_AGG_MSG.SUCCESS_CNT  IS '성공횟수'; 

---------------------------------------------------------------------------------------------- 
CREATE TABLE MP_AGG_USER_AGE 
(
	AGG_SEQ		NUMBER(20, 0), 
	AGE_ORDER	NUMBER(*, 0), 
	DATA_CNT	NUMBER(*,0), 
	AGG_DT		DATE
); 
COMMENT ON TABLE MP_AGG_USER_AGE IS '가입자 연령별 통계';
CREATE SEQUENCE SEQ_MP_AGG_USER_AU INCREMENT BY 1 START WITH 1 MAXVALUE 9223372036854775807 NOORDER;
COMMENT ON COLUMN MP_AGG_USER_AGE.AGG_SEQ IS '시퀀스'; 
COMMENT ON COLUMN MP_AGG_USER_AGE.AGE_ORDER IS '연령대'; 
COMMENT ON COLUMN MP_AGG_USER_AGE.DATA_CNT IS '인원수'; 
COMMENT ON COLUMN MP_AGG_USER_AGE.AGG_DT IS '통계일시'; 
---------------------------------------------------------------------------------------------- 

CREATE TABLE MP_AGG_USER_TMP --- 사용자 연령대와 우편번호 통계데이터 마련을 위한 임시 테이블. 지우고, 데이터 추가  
(
	AGE_ORDER		NUMBER(*, 0), 
	POSTNO_VALUE	VARCHAR2(255) 
); 
COMMENT ON TABLE MP_AGG_USER_TMP IS '가입자 통계를 위한 임시 테이블';
COMMENT ON COLUMN MP_AGG_USER_TMP.AGE_ORDER IS '연령대'; 
COMMENT ON COLUMN MP_AGG_USER_TMP.POSTNO_VALUE IS '우편번호'; 
commit; 

---------------------------------------------------------------------------------------------- 
CREATE TABLE MP_AGG_USER_POSTNO 
(
	AGG_SEQ			NUMBER(20, 0) NOT NULL, 
	POSTNO_VALUE	VARCHAR2(255) NOT NULL, 
	DATA_CNT		NUMBER(*, 0) NOT NULL, 
	AGG_DT			DATE NOT NULL  
); 
COMMENT ON TABLE MP_AGG_USER_POSTNO IS '위치별 가입자 통계';
CREATE SEQUENCE SEQ_MP_AGG_POSTNO_AP INCREMENT BY 1 START WITH 1 MAXVALUE 9223372036854775807 NOORDER;
COMMENT ON COLUMN MP_AGG_USER_POSTNO.AGG_SEQ IS '시퀀스'; 
COMMENT ON COLUMN MP_AGG_USER_POSTNO.POSTNO_VALUE IS '우편번호에 해당하는 시 정보'; 
COMMENT ON COLUMN MP_AGG_USER_POSTNO.DATA_CNT IS '인원수'; 
COMMENT ON COLUMN MP_AGG_USER_POSTNO.AGG_DT IS '통계일시'; 
---------------------------------------------------------------------------------------------- 
CREATE TABLE MP_AGG_USER_REGISTER 
(
	REG_SEQ 		NUMBER(20, 0) NOT NULL, 
	REG_CNT 		NUMBER(*, 0) NOT NULL, 
	AGG_DT			DATE NOT NULL  
); 
COMMENT ON TABLE MP_AGG_USER_REGISTER IS '날짜별 가입인원 통계';
CREATE SEQUENCE SEQ_MP_USER_REGISTER_UR INCREMENT BY 1 START WITH 1 MAXVALUE 9223372036854775807 NOORDER;
COMMENT ON COLUMN MP_AGG_USER_REGISTER.REG_SEQ IS '시퀀스'; 
COMMENT ON COLUMN MP_AGG_USER_REGISTER.REG_CNT IS '인원수'; 
COMMENT ON COLUMN MP_AGG_USER_REGISTER.AGG_DT IS '통계일시'; 

---------------------------------------------------------------------------------------------- 
CREATE TABLE MP_ZIPCODE 
(
	ZIP_SEQ			NUMBER(20, 0) DEFAULT 0, 
	ZIP_ID			VARCHAR2(255) NOT NULL, 
	SI_ID			VARCHAR2(255) NOT NULL, 
	SI_VALUE		VARCHAR2(255) NOT NULL, 
	GUNGU_VALUE		VARCHAR2(255) 
); 
COMMENT ON TABLE MP_ZIPCODE IS '통계를 위한 우편번호 정보';
CREATE SEQUENCE SEQ_MP_ZIPCODE_ZC INCREMENT BY 1 START WITH 1 MAXVALUE 9223372036854775807 NOORDER;
COMMENT ON COLUMN MP_ZIPCODE.ZIP_SEQ  IS '시퀀스'; 
COMMENT ON COLUMN MP_ZIPCODE.ZIP_ID  IS '시군구우편번호 (5자리체계에서 앞에 3자리)'; 
COMMENT ON COLUMN MP_ZIPCODE.SI_ID  IS '시군구우편번호 (5자리체계에서 앞에 2자리)'; 
COMMENT ON COLUMN MP_ZIPCODE.SI_VALUE  IS '시'; 
COMMENT ON COLUMN MP_ZIPCODE.GUNGU_VALUE  IS '군/구'; 

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

CREATE TABLE MP_HELPER_CONTENTS 
(
	HELPER_SEQ		NUMBER(20, 0), 
	USER_ID			VARCHAR2 (255) NOT NULL,
	USER_MSG		VARCHAR2(4000) NOT NULL, 
	MENU_ID			VARCHAR2(255), 
	SEND_DT			DATE DEFAULT SYSDATE
); 
COMMENT ON TABLE MP_HELPER_CONTENTS IS '도우미 메시지';
CREATE INDEX IDX_HELPER_CONTENTS ON MP_HELPER_CONTENTS(USER_ID, SEND_DT); 
CREATE SEQUENCE SEQ_MP_HELPER_HS INCREMENT BY 1 START WITH 1 MAXVALUE 9223372036854775807 NOORDER;  
COMMENT ON COLUMN MP_HELPER_CONTENTS.HELPER_SEQ  IS '시퀀스';
COMMENT ON COLUMN MP_HELPER_CONTENTS.USER_ID  IS '사용자 ID';
COMMENT ON COLUMN MP_HELPER_CONTENTS.USER_MSG  IS '도우미 화면에 보일 메시지';
COMMENT ON COLUMN MP_HELPER_CONTENTS.MENU_ID  IS '어느 메뉴에 붙는지 메뉴 ID (분석용)';
COMMENT ON COLUMN MP_HELPER_CONTENTS.SEND_DT  IS '보낸시간. Push를 통해서는 전송실패되었을 수 있으나 어쨌든 서버는 보낸 메시지';



---------------------------------------------------------------------------------------------- 
CREATE TABLE MP_LOGIN_HISTORY 
(	
  	LOGIN_SEQ			NUMBER(20,0), 
	USER_ID				VARCHAR2(255), 
	LOGIN_DT			DATE, 
	REMEMBER_ME_YN		CHAR(1),
	LOGIN_HASH_VALUE	VARCHAR2 (255)
); 
COMMENT ON TABLE MP_LOGIN_HISTORY IS '사용자 로그인 이력';
CREATE SEQUENCE SEQ_MP_LOGIN_HISTORY_LS INCREMENT BY 1 START WITH 1 MAXVALUE 9223372036854775807 NOORDER;   
COMMENT ON COLUMN MP_LOGIN_HISTORY.LOGIN_SEQ  IS '시퀀스'; 
COMMENT ON COLUMN MP_LOGIN_HISTORY.USER_ID  IS '사용자 ID'; 
COMMENT ON COLUMN MP_LOGIN_HISTORY.LOGIN_DT  IS '로그인 일시'; 
COMMENT ON COLUMN MP_LOGIN_HISTORY.REMEMBER_ME_YN  IS '자동 로그인 여부'; 
COMMENT ON COLUMN MP_LOGIN_HISTORY.LOGIN_HASH_VALUE  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_HELPER_CONTENTS 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 '로그 남기는 시간';

---------------------------------------------------------------------------------------------- 병원마다 메뉴 구조가 다를 수 있어서 별도의 FORM이 만들어지기 때문에 공통이 될 수 없어서 병원구분이 들어갔음  
CREATE TABLE MP_INF_PUSH_MSG ( 
	PUSH_SEQ			NUMBER(20), 
	FORM_ID				VARCHAR2(255) NOT NULL, 
	HOSPITAL_VALUE		VARCHAR2(255) NOT NULL, 
	USER_ID				VARCHAR2(255) NOT NULL, 
	USER_NAME			VARCHAR2(255), 
	MESSAGE_VALUE		VARCHAR2(255) NOT NULL,
	PARAM_VALUE			VARCHAR2(255), 
	STAGE_ORDER			NUMBER(2) DEFAULT 40, 
	REQ_DT				DATE DEFAULT SYSDATE, 
	SENDER_ID			VARCHAR2(255) DEFAULT 'UNKNOWN', 
	PUMPING_DT			DATE, 
	SEND_YN				CHAR(1) DEFAULT 'N', 
	RESERVE_SEND_DT		DATE DEFAULT SYSDATE
); 
COMMENT ON TABLE MP_INF_PUSH_MSG IS '기간계의 메시지 전송 요청내역';
CREATE SEQUENCE SEQ_MP_INF_FORM_PUSH_PS INCREMENT BY 1 START WITH 1 MAXVALUE 9223372036854775807 NOORDER;  
COMMENT ON COLUMN MP_INF_PUSH_MSG.PUSH_SEQ  		IS '시퀀스'; 
COMMENT ON COLUMN MP_INF_PUSH_MSG.FORM_ID 			IS '[기간계] 메시지 형식 (DB가 달라서 FK를 걸어둘 수 없음)'; 
COMMENT ON COLUMN MP_INF_PUSH_MSG.HOSPITAL_VALUE 	IS '[기간계] 어느병원에서 쏘는 메시지인지 구분. 이 병원에 등록된 FORM_ID 대로 PUSH가 나갈것임'; 
COMMENT ON COLUMN MP_INF_PUSH_MSG.USER_ID  			IS '[기간계] 메시지 수신자'; 
COMMENT ON COLUMN MP_INF_PUSH_MSG.USER_NAME 		IS '[기간계/옵션] 메시지 수신자 이름'; 
COMMENT ON COLUMN MP_INF_PUSH_MSG.MESSAGE_VALUE 	IS '[기간계] 메시지 내용'; 
COMMENT ON COLUMN MP_INF_PUSH_MSG.PARAM_VALUE  		IS '[기간계/옵션] 메시지 형식이 필요로 하는 파라미터. 예) MAP-목적지 (옵션)'; 
COMMENT ON COLUMN MP_INF_PUSH_MSG.STAGE_ORDER 		IS '[기간계/필수] 접수(10)-진료(20)-수납(30)-귀가(40).'; 
COMMENT ON COLUMN MP_INF_PUSH_MSG.SENDER_ID  		IS '[기간계/옵션] 누가 메시지 전송을 의뢰했는지 확인용'; 
COMMENT ON COLUMN MP_INF_PUSH_MSG.REQ_DT 			IS '전송 요청시간'; 
COMMENT ON COLUMN MP_INF_PUSH_MSG.PUMPING_DT  		IS '요청을 꺼낸시간'; 
COMMENT ON COLUMN MP_INF_PUSH_MSG.SEND_YN  			IS '전송의뢰 성공여부'; 
COMMENT ON COLUMN MP_INF_PUSH_MSG.RESERVE_SEND_DT 	IS '[기간계/옵션] 예약전송시간'; 


---------------------------------------------------------------------------------------------- 
CREATE TABLE MP_PAYMENT_MASTER 
(
	HOSPITAL_VALUE				VARCHAR2(255) 	NOT NULL, 
	MSG_SEQ						NUMBER(9)		NOT NULL, 
	MSG_TYPE					VARCHAR2(50)	NOT NULL, 	
	ORDER_ID					NUMBER(13)		NOT NULL, 
	USER_ID						VARCHAR2(255) 	NOT NULL, 
	DEPARTMENT_TYPE				VARCHAR2(50), 
	ORDER_TYPE					VARCHAR2(50), 
	EXAM_DT						DATE, 
	REQ_DT						DATE 			DEFAULT SYSDATE, 	
	PG_TYPE						VARCHAR2(50)	NOT NULL, 
	CARD_COMPANY_TYPE			VARCHAR2(50), 
	CARD_TYPE					VARCHAR2(50), 
	CARD_NO_VALUE				VARCHAR2(255), 
	CARD_QUOTA_VALUE			VARCHAR2(255), 
	CARD_INTEREST_VALUE			VARCHAR2(255), 
	TRANS_DT					DATE, 
	TRANS_VALUE					VARCHAR2(255), 
	TRANS_ID_VALUE				VARCHAR2(255), 
	AMOUNT_VALUE				VARCHAR2(255)	NOT NULL, 
	CANCEL_ORIGIN_ORDER_ID		VARCHAR2(255), 
	CANCEL_ORIGIN_TRANS_DT		DATE, 
	SEND_DT						DATE, 
	SEND_ERROR_YN				VARCHAR2(255), 
	SEND_RETRY_CNT				NUMBER (10) DEFAULT 0,
	AUTH_REQ_VALUE		        VARCHAR2(255),
	REMARK_VALUE				VARCHAR2(255), 
	CONSTRAINT PK_PAYMENT_ORDER_ID PRIMARY KEY (ORDER_ID)
); 
COMMENT ON TABLE MP_PAYMENT_MASTER IS '거래마스터'; 
CREATE SEQUENCE SEQ_MP_PAYMENT_MSG_PM INCREMENT BY 1 START WITH 1 MAXVALUE 999999999 ORDER CYCLE;
CREATE SEQUENCE SEQ_MP_PAYMENT_ORDER_PO INCREMENT BY 1 START WITH 1 MAXVALUE 9999999999999 ORDER NOCYCLE;
COMMENT ON COLUMN MP_PAYMENT_MASTER.HOSPITAL_VALUE  IS '병원코드 (031, 032)';
COMMENT ON COLUMN MP_PAYMENT_MASTER.MSG_SEQ  IS '전송일자별로 unique한 전문번호. KIS전문 제한에 따라 10자리';
COMMENT ON COLUMN MP_PAYMENT_MASTER.MSG_TYPE  IS '승인인지 취소인지여부 (승인 02, 취소12)';
COMMENT ON COLUMN MP_PAYMENT_MASTER.ORDER_ID  IS 'PK. 주문번호. Nice의 주문번호, KIS전문의 거래일련번호.';
COMMENT ON COLUMN MP_PAYMENT_MASTER.USER_ID  IS '사용자번호';
COMMENT ON COLUMN MP_PAYMENT_MASTER.DEPARTMENT_TYPE  IS '진료과코드 (15자리 정도)';
COMMENT ON COLUMN MP_PAYMENT_MASTER.ORDER_TYPE  IS '진료유형 (I:입원, O:외래, E:응급)';
COMMENT ON COLUMN MP_PAYMENT_MASTER.EXAM_DT  IS '진료일 (시간은 없음)';
COMMENT ON COLUMN MP_PAYMENT_MASTER.REQ_DT  IS '거래요청시간. MSG_TYPE에 따라 승인 혹은 취소 요청시간';
COMMENT ON COLUMN MP_PAYMENT_MASTER.PG_TYPE  IS 'PG사 유형 (SSG, R2P)';
COMMENT ON COLUMN MP_PAYMENT_MASTER.CARD_COMPANY_TYPE  IS '카드사코드 (PG사에서 받은 그대로 추가됨)';
COMMENT ON COLUMN MP_PAYMENT_MASTER.CARD_TYPE  IS '카드타입(01:신용카드, 02:체크카드, 03:해외). 해외는 넣을 수 없음. ';
COMMENT ON COLUMN MP_PAYMENT_MASTER.CARD_NO_VALUE  IS '카드번호';
COMMENT ON COLUMN MP_PAYMENT_MASTER.CARD_QUOTA_VALUE  IS '할부개월 (00:일시불, 03:3개월)';
COMMENT ON COLUMN MP_PAYMENT_MASTER.CARD_INTEREST_VALUE  IS '가맹점 무이자 유무 (0:일반, 1:가맹점부담 무이자 사용)';
COMMENT ON COLUMN MP_PAYMENT_MASTER.TRANS_DT  IS '거래완료 일시. 승인이면 승인일시, 취소면 취소일시';
COMMENT ON COLUMN MP_PAYMENT_MASTER.TRANS_VALUE  IS '거래결과번호. 승인이면 승인번호, 취소이면 취소번호';
COMMENT ON COLUMN MP_PAYMENT_MASTER.TRANS_ID_VALUE  IS '거래식별자(NicePay의 T_ID). 취소 요청시 필요함';
COMMENT ON COLUMN MP_PAYMENT_MASTER.AMOUNT_VALUE  IS '승인/취소 금액';
COMMENT ON COLUMN MP_PAYMENT_MASTER.CANCEL_ORIGIN_ORDER_ID  IS '취소 시 원거래번호';
COMMENT ON COLUMN MP_PAYMENT_MASTER.CANCEL_ORIGIN_TRANS_DT  IS '취소 시 원거래 승인일시';
COMMENT ON COLUMN MP_PAYMENT_MASTER.SEND_DT  IS '실시간 배치 전송 시간';
COMMENT ON COLUMN MP_PAYMENT_MASTER.SEND_ERROR_YN  IS '실시간 배치 전송 에러 여부';
COMMENT ON COLUMN MP_PAYMENT_MASTER.SEND_RETRY_CNT  IS '실패 재전송 횟수';
COMMENT ON COLUMN MP_PAYMENT_MASTER.AUTH_REQ_VALUE  IS '요청의 유효성 검증 코드';
COMMENT ON COLUMN MP_PAYMENT_MASTER.REMARK_VALUE  IS '참고용 ';
---------------------------------------------------------------------------------------------- 

---------------------------- MP_AUTH 관련 테이블 --------------------------
CREATE TABLE MP_ATTR_TYPE
(
	ATTR_TYPE_SEQ			NUMBER(20),
	ATTR_TYPE				VARCHAR2(255) NOT NULL,
	ATTR_TYPE_NAME			VARCHAR2(255) NOT NULL,
	CONSTRAINT PK_MP_ATTR_TYPE PRIMARY KEY (ATTR_TYPE),
	CONSTRAINT UQ_MP_ATTR_TYPE UNIQUE (ATTR_TYPE, ATTR_TYPE_NAME)
);
CREATE SEQUENCE SEQ_MP_ATTR_TYPE INCREMENT BY 1 START WITH 1 MAXVALUE 9223372036854775807 NOORDER;
COMMENT ON TABLE MP_ATTR_TYPE IS '업무 속 타입';
COMMENT ON COLUMN MP_ATTR_TYPE.ATTR_TYPE_SEQ 	IS '업무 속성 타입 SEQ';
COMMENT ON COLUMN MP_ATTR_TYPE.ATTR_TYPE 		IS '업무 속성 타입';
COMMENT ON COLUMN MP_ATTR_TYPE.ATTR_TYPE_NAME	IS '업무 속성 타입 이름';

CREATE TABLE MP_ATTR
(
	ATTR_SEQ		NUMBER(20),
	ATTR_TYPE		VARCHAR2(255) 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),
	CONSTRAINT FK_MP_ATTR_ATTR_TYPE FOREIGN KEY (ATTR_TYPE) REFERENCES MP_ATTR_TYPE(ATTR_TYPE)
);
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';

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 '공용 장비 별칭';

CREATE TABLE MP_DEMO_USER
(
	USER_ID					VARCHAR2(255 BYTE), 
	USER_NAME				VARCHAR2(255 BYTE), 
	TELNO_VALUE 		    VARCHAR2(255 BYTE) DEFAULT NULL, 
	INHOSPITAL_YN			CHAR(1 BYTE) DEFAULT 'N', 
	CARNO_VALUE				VARCHAR2(255 BYTE), 
	HEIGHT_VALUE			VARCHAR2(255 BYTE) DEFAULT '0.0', 
	WEIGHT_VALUE			VARCHAR2(255 BYTE) DEFAULT '0.0', 
	CHECK_DT				DATE DEFAULT NULL, 
	BIRTHDAY_DT				DATE, 
	GENDER_VALUE			VARCHAR2(255 BYTE) DEFAULT 'M', 
	ZIPCODE_VALUE			VARCHAR2(255 BYTE), 
	ZIPCODE_TXT_VALUE		VARCHAR2(255 BYTE), 
	ADDR_VALUE				VARCHAR2(255 BYTE),
	CONSTRAINT PK_MCARE_DEMO_USER PRIMARY KEY (USER_ID)
);
COMMENT ON COLUMN MP_DEMO_USER.USER_ID 				IS '사용자 ID';
COMMENT ON COLUMN MP_DEMO_USER.USER_NAME 			IS '사용자 이름';
COMMENT ON COLUMN MP_DEMO_USER.TELNO_VALUE 			IS '전화번호';
COMMENT ON COLUMN MP_DEMO_USER.INHOSPITAL_YN 		IS '재원여부(병원에 있는지 여부)';
COMMENT ON COLUMN MP_DEMO_USER.CARNO_VALUE 			IS '차량번호';
COMMENT ON COLUMN MP_DEMO_USER.HEIGHT_VALUE 		IS '키';
COMMENT ON COLUMN MP_DEMO_USER.WEIGHT_VALUE 		IS '몸무게';
COMMENT ON COLUMN MP_DEMO_USER.CHECK_DT 			IS '키/몸무게 측정일시';
COMMENT ON COLUMN MP_DEMO_USER.BIRTHDAY_DT 			IS '생년월일';
COMMENT ON COLUMN MP_DEMO_USER.GENDER_VALUE 		IS '성별 (1,3:남, 2,4:여, 5,7:외국인남, 6,8:외국인여)';
COMMENT ON COLUMN MP_DEMO_USER.ZIPCODE_VALUE 		IS '우편번호';
COMMENT ON COLUMN MP_DEMO_USER.ZIPCODE_TXT_VALUE 	IS '우편번호 대응주소';
COMMENT ON COLUMN MP_DEMO_USER.ADDR_VALUE 			IS '우편번호 나머지 ';

COMMIT;