create table TEMPLATE
(
	ID NUMBER default NULL not null
		constraint TEMPLATE_PK
			primary key,
	TYPE VARCHAR2(200) not null,
	PATH VARCHAR2(2000) default NULL not null,
	CONTENT CLOB default NULL not null
)
/

create unique index TEMPLATE_TYPE_PATH_UIN
	on TEMPLATE (TYPE, PATH)
/


create table NOTIFY_PLATFORM
(
	ID NUMBER not null
		primary key,
	PLATFORM VARCHAR2(50) not null,
	COMPONENT VARCHAR2(400) not null
)
/

create unique index NOTIFY_PLATFORM_UNIQ
	on NOTIFY_PLATFORM (PLATFORM)
/

create table NOTIFY_TYPE
(
	ID NUMBER not null
		constraint NOTIFY_TYPE_PK
			primary key,
	TYPE VARCHAR2(400) not null,
	EVENT_NAME VARCHAR2(400) not null,
	ACTIVE NUMBER not null,
	PLATFORM_ID NUMBER not null,
	CONTENT_TEMPLATE_ID NUMBER not null,
	TITLE_TEMPLATE_ID NUMBER,
	REQUEST_URLS VARCHAR2(4000)
)
/

ALTER TABLE NOTIFY_TYPE
ADD CONSTRAINT NOTIFY_TYPE_PLATFORM_ID_fk
FOREIGN KEY (PLATFORM_ID) REFERENCES NOTIFY_PLATFORM (ID);
/

ALTER TABLE NOTIFY_TYPE
ADD CONSTRAINT NOTIFY_TYPE_CONTENT_TEMP_ID_fk
FOREIGN KEY (CONTENT_TEMPLATE_ID) REFERENCES TEMPLATE (ID);
/

ALTER TABLE NOTIFY_TYPE
ADD CONSTRAINT NOTIFY_TYPE_TITLE_TEMP_ID_fk
FOREIGN KEY (TITLE_TEMPLATE_ID) REFERENCES TEMPLATE (ID);
/

create table NOTIFY_RECIPIENT
(
	ID NUMBER not null
		primary key,
	MAC VARCHAR2(255) not null,
	RECIPIENT VARCHAR2(400) not null,
	DELETED NUMBER default 0 not null,
	CREATE_DATE DATE default sysdate not null,
	DELETE_DATE DATE,
	NOTIFY_TYPE_ID NUMBER not null
)
/

create unique index NOTIFY_RECIPIENT_UNIQ_INDEX
	on NOTIFY_RECIPIENT (MAC, RECIPIENT, DELETED, NOTIFY_TYPE_ID)
/

ALTER TABLE NOTIFY_RECIPIENT
ADD CONSTRAINT NOTIFY_RECIPIENT_TYPE_ID_fk
FOREIGN KEY (NOTIFY_TYPE_ID) REFERENCES NOTIFY_TYPE (ID);
/

create sequence NOTIFY_RECIPIENT_SEQ
/

commit;