--
--
-- package/jabber/sql/oracle/jabber-create.sql
-- @author Luis Mosteiro Fernandez
--
--

create table jb_services (
			  service	 varchar(255) primary key,
			  pretty_name    varchar(255),
				-- active_check_p to control whether service is available (t) or not (f)
			  active_check_p char check(active_check_p in ('t','f'))
			 );


insert into jb_services (service , pretty_name , active_check_p) values ('jabber' , 'Jabber' , 't');
insert into jb_services (service , pretty_name , active_check_p) values ('aim' , 'AIM' , 't');
insert into jb_services (service , pretty_name , active_check_p) values ('yahoo' , 'Yahoo' , 't');
insert into jb_services (service , pretty_name , active_check_p) values ('msn' , 'MSN' , 't');
insert into jb_services (service , pretty_name , active_check_p) values ('icq' , 'ICQ' , 't');

-- Screen
-- Definition as an object
-- Screen saves every screenname, it can be taken as a buddylist
-- users within the OACS System can have several screens, because they can be member in different im-systems
-- moreover the screens of external users are stored in this table
-- implementation as object, because it is imaginable to put a permission
-- on the screenname so not everyone can add this screen to his buddylist

create function inline_jab ()
returns integer as '
begin
perform acs_object_type__create_type (
 ''jb_screen'' ,
 ''Screen'' ,
 ''Screens'' ,
 ''acs_object'' ,
 ''JB_SCREENS'' ,
 ''SCREEN_ID'' ,
 ''jabber'' ,
 ''f'' ,
 null ,
 null
);

return null;
end;' language 'plpgsql';

select inline_jab();
drop function inline_jab();

  
select acs_attribute__create_attribute (
	'jb_screen',		   -- object_type    
	'user_id',					   -- attribute_name 
	'integer',					   -- datatype       
	'User ID',					   -- pretty_name    
	'User IDs',					   -- pretty_plural  
	null,						   -- table_name
	null,						   -- column_name
	null,					   -- default_value
	1,					   -- min_n_values
	1,					   -- max_n_values
	null,					   -- sort_order
	null,			   -- storage
	'f'					   -- static_p
);

  
select acs_attribute__create_attribute (
	'jb_screen',		   -- object_type    
	'im_screen_name',				   -- attribute_name 
	'string',					   -- datatype       
	'IM Screen Name',				   -- pretty_name    
	'IM Screen Names',				   -- pretty_plural  
	null,						   -- table_name
	null,						   -- column_name
	null,					   -- default_value
	1,					   -- min_n_values
	1,					   -- max_n_values
	null,					   -- sort_order
	null,			   -- storage
	'f'					   -- static_p
);

 
select acs_attribute__create_attribute (
	'jb_screen',		   -- object_type    
	'service',				   -- attribute_name 
	'string',					   -- datatype       
	'Service',				   -- pretty_name    
	'Services',				   -- pretty_plural  
	null,						   -- table_name
	null,						   -- column_name
	null,					   -- default_value
	1,					   -- min_n_values
	1,					   -- max_n_values
	null,					   -- sort_order
	null,			   -- storage
	'f'					   -- static_p
);

 
select acs_attribute__create_attribute (
	'jb_screen',		   -- object_type    
	'status',				   -- attribute_name 
	'string',					   -- datatype       
	'Status',				   -- pretty_name    
	'Status',				   -- pretty_plural  
	null,						   -- table_name
	null,						   -- column_name
	null,					   -- default_value
	1,					   -- min_n_values
	1,					   -- max_n_values
	null,					   -- sort_order
	null,			   -- storage
	'f'					   -- static_p
);


select acs_attribute__create_attribute (
	'jb_screen',		   -- object_type    
	'away_msg',				   -- attribute_name 
	'string',					   -- datatype       
	'Away_Message',				   -- pretty_name    
	'Away_Messages',				   -- pretty_plural  
	null,						   -- table_name
	null,						   -- column_name
	null,					   -- default_value
	1,					   -- min_n_values
	1,					   -- max_n_values
	null,					   -- sort_order
	null,			   -- storage
	'f'					   -- static_p
);


select acs_attribute__create_attribute (
	'jb_screen',		   -- object_type    
	'resourc',				   -- attribute_name 
	'string',					   -- datatype       
	'Resource',				   -- pretty_name    
	'Resources',				   -- pretty_plural  
	null,						   -- table_name
	null,						   -- column_name
	null,					   -- default_value
	1,					   -- min_n_values
	1,					   -- max_n_values
	null,					   -- sort_order
	null,			   -- storage
	'f'					   -- static_p
);


select acs_attribute__create_attribute (
	'jb_screen',		   -- object_type    
	'refcount',				   -- attribute_name 
	'integer',					   -- datatype       
	'Reference_Count',				   -- pretty_name    
	'Reference_Counts',				   -- pretty_plural  
	null,						   -- table_name
	null,						   -- column_name
	null,					   -- default_value
	1,					   -- min_n_values
	1,					   -- max_n_values
	null,					   -- sort_order
	null,			   -- storage
	'f'					   -- static_p
);


create table jb_screens (
			screen_id       integer primary key references acs_objects(object_id),
				-- user_id is null, if the user is not member of the OACS
			user_id	 	integer references users(user_id), 
			im_screen_name  varchar(255),  
			service         varchar(255) references jb_services(service) ,
			status          varchar(255),
			away_msg        varchar(4000),
			resourc         varchar(255),
			refcount        integer,
			unique (im_screen_name , service)
			);


-- Defining the jb_screen package functions


create or replace function jb_screen__new (integer , integer , varchar , varchar , varchar , varchar , varchar , integer , varchar , timestamp with time zone , integer , varchar , integer) 
returns integer as '
declare

	p_screen_id	  alias for $1;
        p_user_id	  alias for $2;
	p_im_screen_name  alias for $3;
	p_service         alias for $4;
	p_status          alias for $5;
	p_away_msg        alias for $6;
	p_resourc         alias for $7;
	p_refcount        alias for $8;
	p_object_type     alias for $9;	
	p_creation_date   alias for $10;
	p_creation_user	  alias for $11;
	p_creation_ip	  alias for $12;
	p_context_id      alias for $13;
 	v_screen_id       integer;
	v_refcount        integer;
	v_object_type       varchar(255);
begin


if p_object_type is null then
	v_object_type := ''jb_screen'';
else
       v_object_type := p_object_type;
end if;

if p_refcount is null then 
	v_refcount :=  0;
else
	v_refcount := p_refcount;
end if;


v_screen_id := acs_object__new (
		p_screen_id,
                v_object_type,
	        p_creation_date,
        	p_creation_user,
	        p_creation_ip,
	        p_context_id ,
		''t''
	);



insert into jb_screens
	(screen_id, user_id, im_screen_name, service, status, away_msg, resourc, refcount)
values
	(v_screen_id, p_user_id, p_im_screen_name, p_service, p_status, p_away_msg, p_resourc, p_refcount);

return v_screen_id;
end;' language 'plpgsql';






create function jb_screen__delete(integer)
returns integer as '
declare
 	p_screen_id  alias for $1;
begin



	delete from jb_screens
	where screen_id = p_screen_id;

	perform acs_object__delete(p_screen_id);

return p_screen_id;
end;' language 'plpgsql';



-- jb_screen Package definition end


-- jb_user_jabber_information
-- this table stores jabber login information for each user

create table jb_user_jabber_information (
			user_id 	 integer primary key references users(user_id),
			jabber_regstate  char(2),
			jabber_passwd	 varchar(16),	
			autologin	 char(2) check (autologin in ('t','f'))
			);

-- jb_subscription_request


create table jb_subscription_requests (
			-- screen is a full Jabber-ID
			screen            varchar(255) primary key,
			asked             date
			);
-- jb_message_log
-- log table for messages


create sequence jb_message_log_seq start 1;

create table jb_message_log (
			message_id   integer primary key,
			user_id      integer references users(user_id),
			message      VARCHAR(4000),
			send         date,
			conn_state   varchar(2),
				-- receive jabber_id 
			receive_jid  varchar(1000)  
			); 

-- jb_xml_to_send

create table jb_xml_to_send ( 
			xml     bytea,
			stored  date
			);




-- jb_friends
-- this table stores information for all user friends (whether external or internal friends)

create table jb_friends (
			user_id		    integer references users(user_id),
			friend_screen_id    integer references jb_screens(screen_id),
			friend_first_name   varchar(255),
			friend_last_name    varchar(255),
			primary key(user_id , friend_screen_id)
			);


-- Conference Room
-- implementation as object to use the permissioning system

create or replace function inline_jab ()
returns integer as '
begin
perform acs_object_type__create_type (
 ''jb_conference_room'' ,
 ''Conference Room'' ,
 ''Conference Rooms'' ,
 ''acs_object'' ,
 ''JB_CONFERENCE_ROOMS'' ,
 ''room_id'' ,
 ''jabber_conference'' ,
 ''f'' ,
 null ,
 null
);

return null;
end;' language 'plpgsql';

select inline_jab();
drop function inline_jab();


select acs_attribute__create_attribute (
	'jb_conference_room',		   -- object_type    
	'creator_jabber_id',		   -- attribute_name 
	'string',			   -- datatype       
	'Creator Jabber ID',		   -- pretty_name    
	'Creator Jabber IDs',		   -- pretty_plural  
	null,				   -- table_name
	null,				   -- column_name
	null,				   -- default_value
	1,				   -- min_n_values
	1,				   -- max_n_values
	null,				   -- sort_order
	null,			 	   -- storage
	'f'				   -- static_p
);



select acs_attribute__create_attribute (
	'jb_conference_room',		   -- object_type    
	'name',		   		   -- attribute_name 
	'string',			   -- datatype       
	'Name',				   -- pretty_name    
	'Names',			   -- pretty_plural  
	null,				   -- table_name
	null,				   -- column_name
	null,				   -- default_value
	1,				   -- min_n_values
	1,				   -- max_n_values
	null,				   -- sort_order
	null,			 	   -- storage
	'f'				   -- static_p
);


select acs_attribute__create_attribute (
	'jb_conference_room',		   -- object_type    
	'description',		   		   -- attribute_name 
	'string',			   -- datatype       
	'Description',				   -- pretty_name    
	'Descriptions',			   -- pretty_plural  
	null,				   -- table_name
	null,				   -- column_name
	null,				   -- default_value
	1,				   -- min_n_values
	1,				   -- max_n_values
	null,				   -- sort_order
	null,			 	   -- storage
	'f'				   -- static_p
);



select acs_attribute__create_attribute (
	'jb_conference_room',		   -- object_type    
	'room_id',		   		   -- attribute_name 
	'integer',			   -- datatype       
	'Room ID',				   -- pretty_name    
	'Room IDs',			   -- pretty_plural  
	null,				   -- table_name
	null,				   -- column_name
	null,				   -- default_value
	1,				   -- min_n_values
	1,				   -- max_n_values
	null,				   -- sort_order
	null,			 	   -- storage
	'f'				   -- static_p
);



select acs_attribute__create_attribute (
	'jb_conference_room',		   -- object_type    
	'type',		   		   -- attribute_name 
	'string',			   -- datatype       
	'Type',				   -- pretty_name    
	'Types',			   -- pretty_plural  
	null,				   -- table_name
	null,				   -- column_name
	null,				   -- default_value
	1,				   -- min_n_values
	1,				   -- max_n_values
	null,				   -- sort_order
	null,			 	   -- storage
	'f'				   -- static_p
);




create table jb_conference_rooms ( 
			room_id                 integer primary key references acs_objects(object_id),
			creator_jabber_id       varchar(255),
				-- jabber_id of the creator
				-- only users having a jabber_id can create a conference room
				-- jabber_ids look like: screen_name_of_the_service@service_name.jabber_server
				-- therefore it has to be varchar
			name                    varchar(100) unique,  
			description             varchar(1000),
                        type                    varchar(15)
			);






create or replace function jb_conference_room__new (integer , varchar , varchar , varchar ,  varchar , timestamp with time zone , integer , varchar , integer) 
returns integer as '
declare
        p_room_id             alias for $1;
        p_creator_jabber_id   alias for $2;
        p_name                alias for $3;
        p_description         alias for $4;
	p_object_type         alias for $5;	
	p_creation_date       alias for $6;
	p_creation_user	      alias for $7;
	p_creation_ip	      alias for $8;
	p_context_id          alias for $9;
        v_room_id             integer;
        v_object_type         varchar;
begin


IF p_object_type is null then
  
      v_object_type := ''jb_conference_room'';

ELSE 

      v_object_type := p_object_type;

END IF;


v_room_id := acs_object__new (
	p_room_id,
	v_object_type,
	p_creation_date,
	p_creation_user,
	p_creation_ip,
	p_context_id
	);


insert into jb_conference_rooms
	(room_id, creator_jabber_id, name, description)
values
	(v_room_id, p_creator_jabber_id, p_name, p_description);


return v_room_id;

end;
' language 'plpgsql';





create or replace function jb_conference_room__delete (integer )
returns integer as '
declare
	p_room_id     alias for $1;
begin
	delete from jb_conference_rooms
	where room_id = p_room_id;


perform acs_object__delete(p_room_id);
return p_room_id;
end;
' language 'plpgsql';








-- Conference Room Thread
-- no implementation as object
-- user permission controlled for entire room, not for a single thread 




create sequence jb_conference_room_threads_seq start 1;
create sequence jb_conference_rooms_seq start 1;

create table jb_conference_room_threads (
	thread_id         integer primary key,
	room_id	          integer not null references jb_conference_rooms(room_id),
	start_time	  date,	
	end_time          date,
	subject           varchar(200),
	description       varchar(1000),
	creator_jabber_id varchar(255),
	creator_user_id   integer references users(user_id)                                                      
	);


-- Maps Rooms to the threads where the dicussion Started, for storing the structure of a discussion on an major topic, that maybe
-- displayed later .



create  table jb_room_start_from_thread (
                                                  thread_id    integer not null references jb_conference_room_threads(thread_id),
                                                  room_id       integer not null references jb_conference_rooms(room_id),
                                                  primary key(thread_id , room_id)
                                                  );



-- maps a Room to a Party so oher users/groups can posibly browes the discussions.

create table jb_room_party_map (
                                party_id      integer not null references parties(party_id),
                                room_id       integer not null references  jb_conference_rooms(room_id),
                                primary key (party_id , room_id) 
	                       );

-- stores the group_id's that have access to the conference system
-- conference_only_p indicates if this group was created for conferencing ('t') and we (jabber admin) may be allowed to change it,
-- or if this groups originated some where else in the package.


create table jb_conference_groups (
                                  group_id integer primary key references groups(group_id),
				  conference_only_p  char(1) default 't' check (conference_only_p in ('t' , 'f') ),
                                  default_room_id integer unique references jb_conference_rooms(room_id)
                                  );  


create table jb_groups_blocked_conference(
	                                    group_id integer primary key references groups(group_id)
				             );


-- IMPORTANT
-- creation of a dummy conference room and conference room thread, so the C-module can work!
-- C-module has an own sequence for the threads, but it needs to have a start point, so the database has to provide this start point




create table jb_conference_protocols (
	jabber_id   varchar(100),
	user_id	    integer references users(user_id),
	message     varchar(1000),
	stored      timestamp,
	room_id     integer references jb_conference_rooms(room_id),
	thread_id   integer references jb_conference_room_threads(thread_id)   
	) ;

create index jb_conference_protocols_id on jb_conference_protocols(room_id, thread_id);





-- Now come some PL/SQL procedures and functions, which are used by the c-modules

-- validate whether user is registered

create or replace function jb_reg_validation(varchar , varchar)  returns integer as '
declare
	screen_in    alias for $1;
	regstate     alias for $2;
        v_user_id jb_screens.user_id%TYPE;     
    BEGIN 
     v_user_id := v_user_id;     
     return 1;
    END;
' language 'plpgsql';
	
	
  


 

 
-- Update the online state of a user
create or replace function jb_update_status (varchar , varchar ,  varchar , varchar) returns integer as '
declare 
	status_in      alias for $1;
	resourc_in     alias for $2;
	screen_in      alias for $3;
	service_in     alias for $4;	
        service_long varchar(255);
        aim  varchar;
	jabber varchar;
BEGIN

aim := ''a'';
jabber := ''j'';


   IF service_in = ''a'' THEN	service_long := ''aim'';   END IF;

   IF service_in = ''j'' THEN service_long := ''jabber'';   END IF;

   IF service_in = ''m'' THEN service_long := ''msn'';   END IF;
   IF service_in = ''r'' THEN service_long := ''irc'';   END IF;
   IF service_in = ''i'' THEN service_long := ''icq'';   END IF;
 

--IF service_in = ''j'' THEN service_long := ''jabber''; END IF;
	
   update jb_screens SET status = status_in , resourc= resourc_in WHERE LOWER(im_screen_name)=LOWER(screen_in) and service = service_long;
 
   return 1;

END;
' language 'plpgsql';




-- Clear the online states of every one on Login or Logout

create or replace function jb_clear_status() returns integer as ' 
Begin
update jb_screens SET status=''offline'' , resourc=''offline'';
return 1;
End;
' language 'plpgsql';


 



--checks if jid_in is in the table of the subscription requests , we store them in ther because we may have to wait 
--for a while till some one (jid_in) answers the request, it returns 0 in case there is nothing known about jid_in
--or n > 0  in case there is something known about jid_in 
     
create or replace function jb_subscription_check (varchar)
returns integer as '
declare
jid_in   alias for $1;
r_exists integer;
BEGIN
 r_exists := 0;
 select count(screen) into r_exists from jb_subscription_requests where lower(screen) = lower(jid_in) ;

 return r_exists;
END;
' language 'plpgsql';







--inserts a new subscription request

create or replace function jb_subscription_insert(varchar) returns integer as '
declare 
	jid_in    alias for $1;
BEGIN
    insert into jb_subscription_requests (screen , asked ) values ( jid_in , sysdate);

return 1;   
END;
' language 'plpgsql';


--removes a certain subscription request

create or replace function jb_subscription_del (varchar) returns integer as '
DECLARE
	jid_in alias for $1;
BEGIN
          delete from jb_subscription_requests where lower(screen) = lower(jid_in);
          
return 1;
END;
' language 'plpgsql';






--Find the user_id to a given jid, when someone invites us through  sending a jabber invite from his jabber client:

create or replace function jb_get_user_id (varchar) returns integer AS '
DECLARE
 screen_in alias for $1;
 r_user_id integer;  

BEGIN

        Select persons.person_id INTO r_user_id
        FROM persons,  jb_screens
        WHERE persons.person_id = jb_screens.user_id
        AND jb_screens.service = ''jabber''
        AND jb_screens.im_screen_name = screen_in;

        return r_user_id;

END;
' language 'plpgsql';








--Get the jid from a given user_id 

create or replace function jb_get_jid (integer) returns VARCHAR AS '
DECLARE 
 user_id_in   alias for $1;
 r_jid        varchar(150);
BEGIN
  
 Select sn.im_screen_name INTO r_jid 
 FROM jb_screens sn 
 WHERE sn.user_id = user_id_in 
 AND sn.service = ''jabber'';

 return r_jid;

END;
' language 'plpgsql';



--if a room is destroyed or the topic changes, the  discussion on the last topic (thread) ends ,insert this time as thread end time:
 

create or replace function jb_conference_thread_finish(integer) returns integer as '
DECLARE
	thread_id_in    alias for $1;
BEGIN
     update jb_conference_room_threads set end_time = sysdate where thread_id = thread_id_in;
return 1;     
END;
' language 'plpgsql';





--When a room starts or the  topic changes a new Thread is created :


create or replace function jb_conference_thread_new (integer , integer , varchar , varchar ,  integer) returns integer as '
DECLARE
	thread_id_in       alias for $1;
	room_id_in         alias for $2;
	subject_in         alias for $3;
	creator_jid_in     alias for $4;
	creator_user_id_in alias for $5;
	
BEGIN

 IF creator_user_id_in = 0 THEN

   insert into jb_conference_room_threads (thread_id , room_id , start_time , subject , creator_jabber_id )
   values (thread_id_in , room_id_in , CURRENT_TIMESTAMP , subject_in , creator_jid_in );

 ELSE
  
   insert into jb_conference_room_threads (thread_id , room_id , start_time , subject , creator_jabber_id , creator_user_id )
   values (thread_id_in , room_id_in , CURRENT_TIMESTAMP , subject_in , creator_jid_in , creator_user_id_in);

 END IF;

 return 1;  

END;
' language 'plpgsql';




--When a message is received from a conference room it is stored in the db:

 
create or replace function jb_conference_message(varchar ,  varchar , integer , integer , integer) returns integer as '
DECLARE
	jid_in        alias for $1;
	message_in    alias for $2;
	room_id_in    alias for $3;
	thread_id_in  alias for $4;
	user_id_in    alias for $5;
BEGIN

  IF user_id_in = 0 THEN
    
       insert into jb_conference_protocols (jabber_id , message , stored , room_id , thread_id )
       values (jid_in , message_in , CURRENT_TIMESTAMP , room_id_in , thread_id_in );

  ELSE 

      insert into jb_conference_protocols (jabber_id , user_id , message , stored , room_id , thread_id)
      values  (jid_in , user_id_in , message_in , CURRENT_TIMESTAMP , room_id_in , thread_id_in );

  END IF;

 return 1;

END;
' language 'plpgsql';






-- When a room is created (it is maybe recreated every Monday at 8:00 ) see if we already have a room_id for that room.

create or replace function jb_conference_room_check(varchar) returns integer AS '
DECLARE
	room_name_in  alias for $1;
        r_room_id     integer;
BEGIN
      r_room_id := -1;

      select room_id  INTO r_room_id from jb_conference_rooms where lower(name)=lower(room_name_in);

     return r_room_id;

END;
' language 'plpgsql';






-- When a room is fully new created , create a new Table entry. We do not use the room_id given by the c-module due to the fact that 
-- conference_room is implemented as object


create or replace function jb_conference_rooms_new ( integer ,  varchar , varchar , varchar) returns integer as ' 
DECLARE
	user_id_in      alias for $1;
	creator_jid_in  alias for $2;
	name_in         alias for $3;
	description_in  alias for $4;
	v_id integer;

BEGIN
  
  
  IF user_id_in = 0 THEN

        
	v_id := jb_conference_room__new (
	       null ,
	       creator_jid_in ,
	       name_in ,
	       description_in ,
	       ''jb_conference_room'' ,
 	       current_timestamp , 
  	       null , 
	       null ,
	       null
		);
	


  ELSE


	v_id := jb_conference_room__new (
	       null ,
	       creator_jid_in ,
	       name_in ,
	       description_in ,
	       ''jb_conference_room'' ,
 	       current_timestamp , 
  	       user_id_in , 
	       null ,
	       null
		);
			
		
     
  END IF;

return 1;


END;
' language 'plpgsql';




-- Get the next room_id. This procedure is used in the database to get a new room_id, but it takes the ACS3.x datamodel as basis
-- In the OACS4.x datamodel conference_room is implemented as object, so we do not have a sequence, but we do not want the c-module
-- generate an error, so we give him an integer back, but we do not use this room_id (look above)

create or replace function jb_conference_next_room_id() returns integer AS '
DECLARE
 r_room_id integer;
BEGIN
  SELECT nextval(''jb_conference_rooms_seq'') INTO r_room_id;
 
  return r_room_id;
END;
' language 'plpgsql'; 



-- Get the next thread_id

create or replace function jb_conference_next_thread_id() returns integer AS '
DECLARE
 r_thread_id integer;
BEGIN
  
  SELECT  nextval(''jb_conference_room_threads_seq'') INTO r_thread_id;
  
  return r_thread_id;

END;
' language 'plpgsql';
 


create or replace function jb_make_group_conference_group(varchar , integer) returns integer AS '
DECLARE
  p_room_name alias for $1;
  p_group_id  alias for $2;
  v_room_id   integer;
BEGIN

  SELECT room_id INTO v_room_id
  FROM jb_conference_rooms
  WHERE lower(name) = lower(p_room_name);

  UPDATE jb_conference_groups
  SET default_room_id = v_room_id
  WHERE group_id = p_group_id;

 return 1;

END;
' language 'plpgsql';



--\i jabber-notification-create.sql
