-- Create Table statements for:
--
-- FORUM.APPLICATIONDSS
-- FORUM.APPLICATIONS
-- FORUM.ATTACHMENTS
-- FORUM.CENSORED
-- FORUM.CONFERENCES
-- FORUM.CONFERENCES_FORUMS
-- FORUM.FORUMS
-- FORUM.FORUMS_GROUPS_RIGHTS
-- FORUM.GROUPS
-- FORUM.MESSAGES
-- FORUM.MESSAGES_ARCHIVED
-- FORUM.REALMS
-- FORUM.RIGHTS_GROUPS_APPLICATIONS
-- FORUM.RIGHTS_USERS_APPLICATIONS
-- FORUM.THREADS
-- FORUM.THREADS_ARCHIVED
-- FORUM.THREADS_NOTIFICATIONREQUESTS
-- FORUM.USERS
-- FORUM.USERS_GROUPS
--
--
--

create user forum identified by pwdforum default tablespace usr temporary tablespace temporary;

grant connect, resource to forum;


DROP TABLE ApplicationDSs;
DROP TABLE Applications;
DROP TABLE Attachments;
DROP TABLE Censored;
DROP TABLE Conferences;
DROP TABLE Conferences_Forums;
DROP TABLE Config;
DROP TABLE Forums;
DROP TABLE Forums_Groups_Rights;
DROP TABLE Groups;
DROP TABLE Messages;
DROP TABLE Messages_Archived;
DROP TABLE Realms;
DROP TABLE Threads;
DROP TABLE Threads_Archived;
DROP TABLE Threads_NotificationRequests;
DROP TABLE Users;
DROP TABLE Users_Groups;


create table FORUM.ADMINISTRATORS(
  CFID   NUMBER,
  CFTOKEN   NUMBER,
  USERNAME   VARCHAR2(32),
  PASSWORD   VARCHAR2(32)
);


Create sequence FORUM.ADMINISTRATORS_SEQ
Start with 1
Increment by 1
/


Create or replace trigger 
Administrators_trig
before insert
on forum.administrators
for each row
begin
  select forum.administrators_seq.NEXTVAL into :NEW.CFID
  FROM DUAL;
end;
/



create table FORUM.APPLICATIONDSS(
  APPLICATIONDS_ID   NUMBER,
  APPLICATIONDSN   VARCHAR2(32),
  APPLICATIONDSDN   VARCHAR2(50),
  DATADIRECTORY   VARCHAR2(255),
  REALM_ID   NUMBER
);






create table FORUM.APPLICATIONS(
  APPLICATION_ID   NUMBER,
  APPLICATIONNAME   VARCHAR2(32),
  DESCRIPTIVENAME   VARCHAR2(64),
  APPLICATIONTYPE   VARCHAR2(32),
  APPLICATIONDS_ID   NUMBER,
  ISUSERNAMEREQUIRED   NUMBER,
  ISUSERAPPROVALREQUIRED   NUMBER,
  ISSESSIONLOGINREQUIRED   NUMBER
);






create table FORUM.ATTACHMENTS(
  ATTACHMENT_ID   NUMBER,
  MESSAGE_ID   NUMBER,
  ISEMBEDDED   NUMBER,
  CLIENTFILENAME   VARCHAR2(64),
  CLIENTFILEEXT   VARCHAR2(16),
  CONTENTTYPE   VARCHAR2(16),
  CONTENTSUBTYPE   VARCHAR2(16),
  SERVERFILE   VARCHAR2(64),
  FILESIZE   NUMBER,
  DATEUPLOADED   DATE 
);

create  index FORUM.I_ATTACHMENTS on FORUM.ATTACHMENTS(ATTACHMENT_ID);
create  index FORUM.I_ATTACHMENTS_MSG on FORUM.ATTACHMENTS(MESSAGE_ID);




create table FORUM.CENSORED(
  CID   NUMBER,
  NASTY   VARCHAR2(35)
);


Create sequence FORUM.CENSORED_SEQ
Start with 1
Increment by 1
/


Create or replace trigger 
Censored_trig
before insert
on forum.Censored
for each row
begin
  select forum.Censored_seq.NEXTVAL into :NEW.CID
  FROM DUAL;
end;
/

create index FORUM.i_censored on forum.censored( cid );



create table FORUM.CONFERENCES(
  CID   NUMBER,
  CONFERENCE_ID   NUMBER,
  ISACTIVE   NUMBER,
  CONFERENCENAME   VARCHAR2(32),
  DESCRIPTIVENAME   VARCHAR2(64),
  ISUSERNAMEREQUIRED   NUMBER,
  ISUSERAPPROVALREQUIRED   NUMBER,
  ISSESSIONLOGINREQUIRED   NUMBER,
  ISMESSAGEAPPROVALREQUIRED   NUMBER,
  EXITURL   VARCHAR2(255),
  EXITTARGET   VARCHAR2(50),
  DEFAULTAGEFILTER   NUMBER,
  DISPLAYNEWTHREADSFIRST   NUMBER,
  ISADVMESSAGEDEFAULT   NUMBER,
  ISHTMLCODEWINDOWENABLED   NUMBER,
  ISFILEUPLOADENABLED   NUMBER,
  ISSEARCHENABLED   NUMBER,
  AREFRAMESENABLED   NUMBER,
  AREIMAGEBUTTONSENABLED   NUMBER,
  IMAGEDIRECTORY   VARCHAR2(128),
  BODYTEXTCOLOR   VARCHAR2(8),
  LABELTEXTCOLOR   VARCHAR2(8),
  BUTTONTEXTCOLOR   VARCHAR2(8),
  LINKCOLOR   VARCHAR2(8),
  VLINKCOLOR   VARCHAR2(8),
  ISBODYBACKGROUNDIMAGEENABLED   NUMBER,
  BODYBACKGROUNDCOLOR   VARCHAR2(8),
  BUTTONBACKGROUNDCOLOR   VARCHAR2(8),
  LABELBACKGROUNDCOLOR   VARCHAR2(8),
  FORMBACKGROUNDCOLOR   VARCHAR2(8),
  HEADER1   VARCHAR2(2000),
  HEADER2   VARCHAR2(2000),
  FOOTER1   VARCHAR2(2000),
  FOOTER2   VARCHAR2(2000),
  ISEMOTICONACTIVE   NUMBER,
  ISMSGCENSORED   NUMBER,
  LEFTCOLUMN   VARCHAR2(2000),
  FRAMEDSITE  NUMBER,
  OpenTag  VarChar2(50),
  CloseTag Varchar2(50)
);



Create sequence FORUM.CONFERENCES_SEQ
Start with 1
Increment by 1
/


Create or replace trigger 
Conferences_trig
before insert
on forum.Conferences
for each row
begin
  select forum.Conferences_seq.NEXTVAL into :NEW.CID
  FROM DUAL;
end;
/

create  index FORUM.I_CONFERENCES on FORUM.CONFERENCES(CONFERENCE_ID);
create  index FORUM.I_CONFERENCES_CID on FORUM.CONFERENCES(CID);



create table FORUM.CONFERENCES_FORUMS(
  CFID   NUMBER,
  CONFERENCE_ID   NUMBER,
  FORUM_ID   NUMBER,
  ISACTIVE   NUMBER,
  PRECEDENCE   NUMBER
);


Create sequence FORUM.CONFERENCES_FORUMS_SEQ
Start with 1
Increment by 1
/

Create or replace trigger 
Conferences_Forums_trig
before insert
on forum.Conferences_Forums
for each row
begin
  select forum.Conferences_Forums_seq.NEXTVAL into :NEW.CFID
  FROM DUAL;
end;
/

create index FORUM.i_conferences_forums on forum.conferences_forums(cfid);

Create table FORUM.CONFIG(
  CFGID  NUMBER,
  HOSTHEADER VARCHAR2(255),
  HOSTFOOTER VARCHAR2(255),
  ADMINEMAIL VARCHAR2(75),
  SHOWCONFLIST NUMBER,
  SHOWWELCOME NUMBER,
  USEVERITY NUMBER,
  MSGCOLLECTION VARCHAR2(75),
  SUBJECTCOLLECTION VARCHAR2(75),
  OPENTAG  VARCHAR2(75),
  CLOSETAG VARCHAR2(75),
  MAXTHREADSDISPLAYED NUMBER,
  INCLUDEMESSAGEBODY NUMBER
);

Create sequence FORUM.Config_SEQ
Start with 1
Increment by 1
/

Create or replace trigger 
Config_trig
before insert
on forum.Config
for each row
begin
  select forum.Config_seq.NEXTVAL into :NEW.CFGID
  FROM DUAL;
end;
/

Create index FORUM.i_config on forum.config(cfgid);


create table FORUM.FORUMS(
  FORUM_ID   NUMBER,
  ISACTIVE   NUMBER,
  TITLE   VARCHAR2(64),
  FORUMDESCRIPTION   Varchar2(2000) ,
  FDate   Date,
  FID   NUMBER
);


Create sequence FORUM.FORUMS_SEQ
Start with 1
Increment by 1
/


Create or replace trigger 
Forums_trig
before insert
on Forum.Forums
for each row
begin
  select forum.Forums_seq.NEXTVAL into :NEW.FID
  FROM DUAL;
end;
/

create  index forum.I_FORUMS on FORUM.FORUMS(FID);
create  index forum.I_FORUMS_id on FORUM.FORUMS(Forum_id);



create table FORUM.FORUMS_GROUPS_RIGHTS(
  FORUM_ID   NUMBER,
  GROUP_ID   NUMBER,
  RIGHTS   NUMBER,
  ISACTIVE   NUMBER
);


create  index FORUM.I_FORUMS_G_R on FORUM.FORUMS_GROUPS_RIGHTS(FORUM_ID, GROUP_ID);




create table FORUM.GROUPS(
  GROUP_ID   NUMBER,
  GROUPNAME   VARCHAR2(32)
);

create index FORUM.i_groups on forum.groups( group_id);





create table FORUM.MESSAGES(
  MESSAGE_ID   NUMBER,
  ISACTIVE   NUMBER,
  ISAPPROVED   NUMBER,
  ISDELETED   NUMBER,
  THREAD_ID   NUMBER,
  PARENTMESSAGE_ID   NUMBER,
  REPLYLEVEL   NUMBER,
  USER_ID   NUMBER,
  AUTHOR   VARCHAR2(64),
  EMAIL   VARCHAR2(128),
  SUBJECT   VARCHAR2(64),
  MESSAGE   VARCHAR2(2000) ,
  HTML_CODE   VARCHAR2(2000),
  ISMESSAGEPREFORMATED   NUMBER,
  ISNOTIFICATIONREQUESTED   NUMBER,
  ARETHREADPARTICIPANTSNOTIFIED   NUMBER,
  DATECREATED   DATE ,
  DATEEDITED   DATE ,
  PVT   NUMBER
);

create index FORUM.i_message on forum.messages( message_id);
create index FORUM.i_message_thread on forum.messages( thread_id);
create index FORUM.i_message_user on forum.messages( user_id);



create table FORUM.MESSAGES_ARCHIVED(
  MESSAGE_ID   NUMBER,
  ISACTIVE   NUMBER,
  ISAPPROVED   NUMBER,
  ISDELETED   NUMBER,
  THREAD_ID   NUMBER,
  PARENTMESSAGE_ID   NUMBER,
  REPLYLEVEL   NUMBER,
  USER_ID   NUMBER,
  AUTHOR   VARCHAR2(64),
  EMAIL   VARCHAR2(128),
  SUBJECT   VARCHAR2(64),
  MESSAGE   VARCHAR2(2000) ,
  HTML_CODE   VARCHAR2(2000),
  ISMESSAGEPREFORMATED   NUMBER,
  ISNOTIFICATIONREQUESTED   NUMBER,
  ARETHREADPARTICIPANTSNOTIFIED   NUMBER,
  DATECREATED   DATE ,
  DATEEDITED   DATE ,
  PVT   NUMBER
);

create index FORUM.i_message_arch on forum.messages_archived( message_id);





create table FORUM.REALMS(
  REALM_ID   NUMBER,
  REALMNAME   VARCHAR2(64),
  REALMDSN   VARCHAR2(32)
);





create table FORUM.RIGHTS_GROUPS_APPLICATIONS(
  GROUP_ID   NUMBER,
  APPLICATION_ID   NUMBER,
  RIGHTS   NUMBER,
  ISACTIVE   NUMBER
);


create  index FORUM.I_RIGHTS_G on FORUM.RIGHTS_GROUPS_APPLICATIONS(GROUP_ID, APPLICATION_ID);




create table FORUM.RIGHTS_USERS_APPLICATIONS(
  USER_ID   NUMBER,
  APPLICATION_ID   NUMBER,
  RIGHTS   NUMBER,
  DATECREATED   DATE 
);

create index FORUM.i_rua on forum.RIGHTS_USERS_APPLICATIONS(user_id);





create table FORUM.THREADS(
  THREAD_ID   NUMBER,
  ISACTIVE   NUMBER,
  FORUM_ID   NUMBER,
  SUBJECT   VARCHAR2(64),
  MESSAGECOUNT   NUMBER,
  TIMELASTUPDATED   DATE 
);


create index FORUM.i_threads on forum.threads(thread_id);
create index FORUM.i_threads_forum on forum.threads(forum_id);



create table FORUM.THREADS_ARCHIVED(
  THREAD_ID   NUMBER,
  ISACTIVE   NUMBER,
  FORUM_ID   NUMBER,
  SUBJECT   VARCHAR2(64),
  MESSAGECOUNT   NUMBER,
  TIMELASTUPDATED   DATE 
);

create index FORUM.i_threads_arch on forum.threads_archived(thread_id);





create table FORUM.THREADS_NOTIFICATIONREQUESTS(
  USER_ID   NUMBER,
  THREAD_ID   NUMBER,
  TID   NUMBER
);



Create sequence FORUM.THREADS_NOT_SEQ
Start with 1
Increment by 1
/

Create or replace trigger 
Threads_Not_trig
before insert
on Forum.Threads_NotificationRequests
for each row
begin
  select forum.Threads_Not_seq.NEXTVAL into :NEW.TID
  FROM DUAL;
end;
/

create  index FORUM.I_THREADS_not on FORUM.THREADS_NOTIFICATIONREQUESTS(TID);


create table FORUM.USERS(
  USER_ID   NUMBER,
  CFID   NUMBER,
  ST   NUMBER,
  USERNAME   VARCHAR2(64),
  DESCRIPTION   VARCHAR2(255),
  PASSWORD   VARCHAR2(30),
  EMAIL   VARCHAR2(128),
  DATECREATED   DATE ,
  ROLE   VARCHAR2(50),
  PROFILE   LONG ,
  URL   VARCHAR2(75),
  SITENAME   VARCHAR2(75),
  ADMIN   NUMBER,
  KOMMENT   VARCHAR2(255),
  CNT   NUMBER,
  LASTLOGIN   DATE 
);

create index FORUM.i_users on forum.users( user_id);




create table FORUM.USERS_GROUPS(
  USER_ID   NUMBER,
  GROUP_ID   NUMBER
);

create index FORUM.i_users_users on forum.users_groups( user_id);
create index FORUM.i_users_groups on forum.users_groups( group_id);