create table wfs.Tre_Package (
	packageId number(10,0) not null, 
	n_Version number(10,0) not null, 
	name nvarchar2 (255) not null, 
	createdate DATE default (SYSDATE), 
	updatedate DATE default (SYSDATE), 
	primary key (packageId));

create table wfs.Tre_Predicate (
	predicateId NUMBER not null,
	CLASS_ID varchar2(255 char) not null,
	n_Version number(10,0) not null, 
	fieldLeft NVARCHAR2(1024) not null,
	fieldRight NVARCHAR2(1024) null,
	fieldLeftSectionType NVARCHAR2(64) null,
	fieldRightSectionType NVARCHAR2(64) null,
	operator  NVARCHAR2(32) not null,
	ParentPredicateId NUMBER(10,0),
	childOrder NUMBER(10,0),
	ignorecase NUMBER(1,0) default '0',
	createdate DATE default (SYSDATE), 
	updatedate DATE default (SYSDATE), 
	primary key (predicateId));

create table wfs.Tre_PredicateValue (
	predicateId NUMBER not null,
	value_ NVARCHAR2(2000) not null,
	order_ NUMBER null);

create table wfs.Tre_RULESET_EVENT (
	eventId number(10,0) not null, 
	RuleSetId number(10,0) not null, 
	primary key (RuleSetId, eventId));

create table wfs.Tre_RuleSet (
	ruleSetId number(10,0) not null, 
	n_Version number(10,0) not null,
	ruleName nvarchar2 (255) not null,
	packageId NUMBER null, 
	titleFilterId NUMBER null,
	enabled NUMBER(1,0) default(1),
	order_ number default '0',
	createdate DATE default (SYSDATE),
	updatedate DATE default (SYSDATE),
	primary key (ruleSetId));

create table wfs.Tre_ActionParameterValue (
	valueId number(10,0) not null, 
	name nvarchar2(256) not null,
	isLiteral NUMBER(1,0) default (1) not null, 
	value blob null, 
	createdate DATE default (SYSDATE), 
	updatedate DATE default (SYSDATE), 
	actionId number(10,0),
	order_ number(10,0), 
	primary key (valueId));

create table wfs.Tre_action (
	actionId number(10,0) not null, 
	method nvarchar2 (512) not null, 
	order_ NUMBER null, 
	createdate DATE default (SYSDATE), 
	updatedate DATE default (SYSDATE), 
	ruleId number(10,0), 
	primary key (actionId));

create table wfs.Tre_event (
	eventId NUMBER not null, 
	name nvarchar2 (25), 
	description nvarchar2 (255), 
	createdate DATE default (SYSDATE), 
	updatedate DATE default (SYSDATE), 
	primary key (eventId));

create table wfs.Tre_rule (
	ruleId number(10,0) not null, 
	n_Version number(10,0) not null, 
	message nvarchar2 (512) null,
	text clob, 
	ruleSetId NUMBER, 
	rootNodeId NUMBER, 
	createdate DATE default (SYSDATE), 
	updatedate DATE default (SYSDATE), 
	order_ number(10,0), 
	primary key (ruleId));

create table wfs.Tre_expNode (
	nodeId NUMBER not null, 
	CLASS_ID varchar2(10 char) not null,
	predicateId NUMBER, 
	operator varchar2(256 char) null,
	parentId NUMBER null, 
	order_	 NUMBER null,
	createdate DATE default (SYSDATE), 
	updatedate DATE default (SYSDATE), 
	primary key (nodeId));

create table wfs.Tre_TitleFilter (
	titleFilterId			NUMBER,
	rootNodeId				NUMBER,
	isSearchFieldMetadata	NUMBER(1,0),
	searchProperty 			NVARCHAR2 (256),
	sortColumnName			NVARCHAR2 (256),
	sortColumnSection		NVARCHAR2 (256),
	sortOrder				NVARCHAR2 (256),
	isSortFieldMetadata 	NUMBER(1,0),
	isInternalSearch 		NUMBER(1,0),
	externalInstanceId 		NVARCHAR2 (256),
	primary key (titleFilterId));

alter table wfs.Tre_PredicateValue add constraint FK_PredicateVal_Predicate foreign key (predicateId) references wfs.Tre_Predicate;

alter table wfs.Tre_RULESET_EVENT add constraint FK_RULESETEVENT_RULE foreign key (RuleSetId) references wfs.Tre_RuleSet;
alter table wfs.Tre_RULESET_EVENT add constraint FK_RULESETEVENT_EVENT foreign key (eventId) references wfs.Tre_event;

alter table wfs.Tre_RuleSet add constraint FK_RULESET_PACKAGE foreign key (PackageId) references wfs.Tre_Package;
alter table wfs.Tre_RuleSet add constraint FK_TreeNode_Filter foreign key (titleFilterId) references wfs.Tre_TitleFilter;

alter table wfs.Tre_ActionParameterValue add constraint FK_WOMP_WOParamValue foreign key (actionId) references wfs.Tre_action;

alter table wfs.Tre_action add constraint FK_Rule_Action foreign key (ruleId) references wfs.Tre_rule;

alter table wfs.Tre_rule add constraint FK_RuleSet_Rule foreign key (ruleSetId) references wfs.Tre_RuleSet;
alter table wfs.Tre_rule add constraint FK_Rule_ExpNode foreign key (rootNodeId) references wfs.Tre_expNode;

alter table wfs.Tre_expNode add constraint FK_ExpNode_ExpNode_Parent foreign key (parentId) references wfs.Tre_expNode;
alter table wfs.Tre_expNode add constraint FK_ExpNode_predicate foreign key (predicateId) references wfs.Tre_Predicate;

alter table wfs.Tre_Predicate add constraint FK_TrePred_TrePred_Parent foreign key (parentPredicateId) references wfs.Tre_Predicate;

alter table wfs.Tre_TitleFilter add constraint FK_TitleFilter_ExpNode foreign key (rootNodeId) references wfs.Tre_expNode;

create sequence wfs.Tre_Predicate_seq NOCACHE;
create sequence wfs.Tre_predicateValue_seq NOCACHE;
create sequence wfs.tre_ActionParamValue_seq NOCACHE;
create sequence wfs.Tre_action_seq NOCACHE;
create sequence wfs.Tre_package_seq NOCACHE;
create sequence wfs.Tre_ruleSet_seq NOCACHE;
create sequence wfs.Tre_rule_seq NOCACHE;
create sequence wfs.tre_titleFilter_seq NOCACHE;
create sequence wfs.tre_expNode_seq NOCACHE;

begin
insert into wfs.Tre_Event(EventId, Name, Description) VALUES(0,'Ingested','Occurs when a title is ingested.');
insert into wfs.Tre_Event(EventId, Name, Description) VALUES(1,'TitleUICreated','Occurs when a title is created through the UI.');
insert into wfs.Tre_Event(EventId, Name, Description) VALUES(2,'TitleUIUpdated','Occurs when a title is updated through the UI.');
insert into wfs.Tre_Event(EventId, Name, Description) VALUES(3,'TitleNationalized','Occurs when a title is nationalized.');
insert into wfs.Tre_Event(EventId, Name, Description) VALUES(4,'Schedule','Can be triggered through an job in PMM job module.');

insert into wfs.Tre_PACKAGE(PACKAGEID, N_VERSION,NAME) values(1,1,'VALIDATION');
insert into wfs.Tre_PACKAGE(PACKAGEID, N_VERSION,NAME) values(2,1,'NORMALIZATION');
insert into wfs.Tre_PACKAGE(PACKAGEID, N_VERSION,NAME) values(3,1,'TARGETING');
insert into wfs.Tre_PACKAGE(PACKAGEID, N_VERSION,NAME) values(4,1,'PROCESSING');

commit;
end;
/

exit