---------------------------------------------------------
-- thesaurus tables
---------------------------------------------------------
drop table thesaurus cascade constraint
/
create table thesaurus(
 id		number(11) not null
,name		varchar2(255) not null
,description 	varchar2(2000)
)
/
--pk
create unique index idx_thesaurus_id_pk on thesaurus ( id asc );
alter table thesaurus add constraint thesaurus_pk primary key ( id ) ;
--uk
create unique index idx_thesaurus_name_uk on thesaurus (name);
--comment
comment on table thesaurus is 'the name and the meaning of the thesaurus' ;
comment on column thesaurus.id is 'primary key' ;
comment on column thesaurus.name is 'name of the thesaurus' ;
comment on column thesaurus.description is 'description of the thesaurus' ;

---------------------
drop table thesaurus_phrases  cascade constraint
/

create table thesaurus_phrases(
 id		number(11) not null
,ths_id 	number(11) not null
,phrase 	varchar2(255) not null
)
/
--pk
create unique index idx_thesaurus_phrases_id_pk on thesaurus_phrases ( id asc );
alter table thesaurus_phrases add constraint thesaurus_phrases_pk primary key ( id ) ;
--uk
create unique index idx_thesaurus_phrases_uk on thesaurus_phrases (phrase);
--comment
comment on table thesaurus_phrases is 'all phrases of this thesaurus';
comment on column thesaurus_phrases.id is 'primary key' ;
comment on column thesaurus_phrases.ths_id is 'fk to the thesaurus name' ;
comment on column thesaurus_phrases.phrase is 'the phrase' ;
---------------------
drop table thesaurus_relations cascade constraint
/

create table thesaurus_relations(
id 		number(11) not null
,ths_id 	number(11)
,thp_phrase 	number(11) not null
,relation 	varchar2(3) not null
,thp_rel_phrase number(11) not null
)
/
create unique index idx_thesaurus_relations_pk on thesaurus_relations ( id asc );
alter table thesaurus_relations add constraint thesaurus_relations_pk primary key ( id ) ;

--uk
create unique index idx_thesaurus_relations_uk on thesaurus_relations (ths_id,thp_phrase,relation,thp_rel_phrase);

--comment
comment on table thesaurus_relations is 'all relations of this thesaurus';
comment on column thesaurus_relations.id is 'primary key' ;
comment on column thesaurus_relations.ths_id is 'fk to the thesaurus name' ;
comment on column thesaurus_relations.relation is 'the relation between the phrases' ;
comment on column thesaurus_relations.thp_phrase is 'from this phrase' ;
comment on column thesaurus_relations.thp_rel_phrase is 'to this phrase' ;
---------------------
-- fk
---------------------
alter table thesaurus_relations add constraint thesaurus_relations_t_fk foreign key (ths_id) references thesaurus (id);
alter table thesaurus_relations add constraint thesaurus_relations_p1_fk foreign key (thp_phrase) references thesaurus_phrases (id);
alter table thesaurus_relations add constraint thesaurus_relations_p2_fk foreign key (thp_rel_phrase) references thesaurus_phrases (id);
-------
alter table thesaurus_phrases add constraint thesaurus_phrases_t_fk foreign key (ths_id) references thesaurus (id);

---------------------
-- Sequence
---------------------
drop sequence thesaurus_seq;
drop sequence thesaurus_phrases_seq;
drop sequence thesaurus_relations_seq;

create sequence thesaurus_seq;
create sequence thesaurus_phrases_seq;
create sequence thesaurus_relations_seq;