--
-- The Intrasite-Message Package
--
-- @author Richard Hamilton (richard.hamilton@webteamworks.com)
-- @version $Id:
--
--
-- GNU GPL v2
--


------ start of folder new proc

-- select define_function_args('im_folder__new','folder_name','user_id, 'immutable_p');

create or replace function im_folder__new (varchar,integer, boolean)
returns boolean as '

declare
  p_folder_name          alias for $1; -- The name of the required new folder.
  p_user_id              alias for $2; -- The user_id of the user to which the folder belongs.
  p_immutable_p          alias for $3; -- System default folder or user changeable.

  -- local vars
  v_folder_id integer;

begin

  select nextval(''im_folder_id_seq'') into v_folder_id;

  insert into im_folder (
    folder_id,
    folder_name,
    user_id,
    created_timestampz,
    immutable_p
  )
  values (
    v_folder_id,
    p_folder_name,
    p_user_id,
    now(),
    p_immutable_p
  );

  if FOUND = true then
    raise NOTICE ''Creating folder - %'',p_folder_name;
  else
    raise NOTICE ''Folder create failed'';
  end if;

  return FOUND;

end;' language 'plpgsql';

------ end folder new proc

------ start of folder del proc

-- select define_function_args('im_folder__del','folder_id','user_id');

create or replace function im_folder__del (integer, integer)
returns boolean as '
declare
 p_folder_id        alias for $1;
 p_user_id          alias for $2;

 -- local vars
 v_message_count    integer;
 v_owner_p          boolean;

begin

  -- Start by checking that the requesting user owns the folder.
  
  select im_message__folder_owner_p(p_folder_id, p_user_id) into v_owner_p;
  
  if v_owner_p = true then

    -- Check that the folder is empty to protect against url surgery. Remember that
    -- deleted_p is only set when the message is deleted from the ''deleted'' folder.

    select count(*) into v_message_count
    from im_addressees
    where folder_id = p_folder_id
    and deleted_p = false;

    if v_message_count = 0 then

      -- Cater for possibility that a deleted and therefore invisible message remains in a user
      -- added folder. This should never happen! :-)

      update im_addressees
        set folder_id = 4
        where deleted_p = true
        and folder_id = p_folder_id;

      delete from im_folder
        where folder_id = p_folder_id
        and user_id = p_user_id
        and immutable_p = false;

    else

      FOUND := false;
    
    end if;
  
  else
  
  FOUND := false;
  
  end if;

  if FOUND = true then
    raise NOTICE ''Deleting folder with id - %'',p_folder_id;
  else
    raise NOTICE ''Delete failed'';
  end if;

  return FOUND;

end;' language 'plpgsql';

------ end folder del proc

------ start of im_message__folder_owner_p

-- select define_function_args('im_message__folder_owner_p','folder_id', 'user_id');

create or replace function im_message__folder_owner_p (integer, integer)
returns boolean as '
declare
 p_folder_id      alias for $1;
 p_user_id        alias for $2;

 -- local vars
 v_owner_p        boolean;

begin

  -- Start by checking that the requesting user owns the folder.
  
  select true into v_owner_p
  from im_folder
  where folder_id = p_folder_id
  and user_id = p_user_id
  and immutable_p = false;

  if v_owner_p = true then
    return v_owner_p;
  else
    return false;
  end if;

end;' language 'plpgsql';

------ end of im_message__folder_owner_p proc

------ start of message new proc

-- select define_function_args('im_message__new','sender_user_id','subject','body','priority_p');

create or replace function im_message__new (integer,varchar,varchar,boolean)
returns integer as '

declare
  p_sender_user_id       alias for $1; -- The id of the user that is sending the message.
  p_subject              alias for $2; -- The subject of the message.
  p_body                 alias for $3; -- The body text of the message.
  p_priority_p           alias for $4; -- Whether the message is high priority or not.

  -- local vars
  v_message_id           integer;

begin

  select nextval(''im_message_id_seq'') into v_message_id;

  insert into im_message (
    message_id,
    sender_user_id,
    subject,
    body,
    priority_p,
    created_timestampz
  )
  values (
    v_message_id,
    p_sender_user_id,
    p_subject,
    p_body,
    p_priority_p,
    now()
  );

  if FOUND = true then
    
    raise NOTICE ''Creating message from user_id - %'',p_sender_user_id;

    insert into im_addressees (
      message_id,
      addressee_id,
      address_mode_id,
      folder_id
    )
    values (
      v_message_id,
      p_sender_user_id,
      0,
      3
    );

  else
    raise NOTICE ''Message create failed'';
    v_message_id := FOUND;
  end if;

  return v_message_id;

end;' language 'plpgsql';

------ end of message new proc

------ start of administrators message del proc

-- select define_function_args('im_message__admin_del','message_id');

create or replace function im_message__admin_del (integer)
returns boolean as '
declare
 p_message_id    alias for $1;

begin

  -- This function deletes a message by flagging deleted_p = true in the im_message table which
  -- has the effect of removing it from view for all users.

  update im_message set deleted_p = true, deleted_timestampz = now()
    where message_id = p_message_id
    and deleted_p = false;

  if FOUND = true then
    raise NOTICE ''Deleting message with id - %'',p_message_id;
  else
    raise NOTICE ''Message delete failed'';
  end if;

   return FOUND;

end;' language 'plpgsql';

------ end of administrators message del proc

------ start of message del proc

-- select define_function_args('im_message__del','message_id','user_id');

create or replace function im_message__del (integer, integer)
returns boolean as '
declare
 p_message_id    alias for $1;
 p_user_id       alias for $2;

begin

  update im_addressees set deleted_p = true, deleted_timestampz = now()
    where message_id = p_message_id
    and addressee_id = p_user_id
    and folder_id = 4
    and deleted_p = false;

  if FOUND = true then
    raise NOTICE ''Deleting message with id - %'',p_message_id;
  else
    raise NOTICE ''Message delete failed'';
  end if;

   return FOUND;

end;' language 'plpgsql';

------ end of message del proc

------ start of message delete proc

-- select define_function_args('im_message__delete','message_id','user_id');

create or replace function im_message__delete (integer, integer)
returns boolean as '
declare
 p_message_id    alias for $1;
 p_user_id       alias for $2;

 --local vars
 v_folder_id	 integer;
 v_addressee_p   boolean;

begin

  select im_message__addressee_p(p_message_id, p_user_id) into v_addressee_p;

  if v_addressee_p = true then

    select folder_id from im_addressees into v_folder_id
      where message_id = p_message_id
      and addressee_id = p_user_id;

    if v_folder_id = 4 then
      perform im_message__del(p_message_id, p_user_id);
    else
      update im_addressees set folder_id = 4
        where message_id = p_message_id
        and addressee_id = p_user_id
        and folder_id <> 4
        and deleted_p = false;
    end if;

  else
  
    raise NOTICE ''Permission denied'';
    FOUND := false;

  end if;

  if FOUND = true then
    raise NOTICE ''Delete operation successful for message with id - %'',p_message_id;
  else
    raise NOTICE ''Delete operation failed'';
  end if;

  return FOUND;

end;' language 'plpgsql';

------ end message delete proc

------ start of message move proc

-- select define_function_args('im_message__move','message_id','user_id');

create or replace function im_message__move (integer, integer, integer)
returns boolean as '
declare
 p_message_id     alias for $1;
 p_user_id        alias for $2;
 p_new_folder_id  alias for $3;

 --local vars
 v_folder_id	 integer;
 v_addressee_p   boolean;

begin

  select im_message__addressee_p(p_message_id, p_user_id) into v_addressee_p;

  if v_addressee_p = true then

    update im_addressees set folder_id = p_new_folder_id
      where message_id = p_message_id
      and addressee_id = p_user_id
      and folder_id <> p_new_folder_id
      and deleted_p = false;

  else
  
    raise NOTICE ''Permission denied'';
    FOUND := false;

  end if;

  if FOUND = true then
    raise NOTICE ''Move operation successful for message with id - %'',p_message_id;
  else
    raise NOTICE ''Move operation failed'';
  end if;

  return FOUND;

end;' language 'plpgsql';

------ end message move proc

------ start of folder display procs

-- select define_function_args('im_message__del','folder_id');

create or replace function im_folder_display__links (integer)
returns varchar as '
declare
 p_folder_id           alias for $1;

-- local vars
 v_return_string       varchar;
 v_num_messages        integer;

begin

  select count(*) into v_num_messages
  from im_addressees
  where folder_id = p_folder_id
  and deleted_p = false;

  if v_num_messages <> 0 then
    v_return_string := ''<a class="im_folder_list_actions" href="/messages/folder-add-edit?folder_id=''||p_folder_id||''">|&nbsp;Edit&nbsp;|</a>'';
  else
    v_return_string := ''<a class="im_folder_list_actions" href="/messages/folder-delete?folder_id=''||p_folder_id||''">|&nbsp;Del&nbsp;|</a>'';
  end if;

   return v_return_string;

end;' language 'plpgsql';

------ end folder display procs

------ start of message send proc

-- select define_function_args('im_message__send','message_id','user_id');

create or replace function im_message__send (integer, integer)
returns boolean as '
declare
 p_message_id    alias for $1;
 p_user_id       alias for $2;
 
 -- local vars
 v_sent_p     boolean;
 v_author_p   boolean;
 v_success_p  boolean;

begin

   v_sent_p := im_message__sent_p(p_message_id);
   v_author_p := im_message__author_p(p_message_id, p_user_id);

   if v_sent_p = true then
     raise NOTICE ''Attempt to send a message that has already been "sent". message_id - %'',p_message_id;
     v_success_p := FALSE;
   elsif v_author_p = false then
     raise NOTICE ''Attempt to send a message by user who is not the "sender". message_id- %'',p_message_id;
     v_success_p := FALSE;
   else
     update im_message set sent_p = true, sent_timestamptz = now()
     where message_id = p_message_id
     and deleted_p = false;
     
     if FOUND = FALSE then
       raise NOTICE ''Message send failed for message_id - %'',p_message_id;
       v_success_p := FALSE;
     else
       raise NOTICE ''Message send successful for message_id - %'',p_message_id;
       v_success_p := FOUND;

       update im_addressees set folder_id = 2
       where message_id = p_message_id
       and address_mode_id = 0
       and deleted_p = false;
     
     end if;
   end if;

  return v_success_p;

end;' language 'plpgsql';

------ end of message send proc

------ start of message sent? proc

-- select define_function_args('im_message__sent_p','message_id');

create or replace function im_message__sent_p (integer)
returns boolean as '
declare
 p_message_id    alias for $1;

 -- local vars
 v_sent_p    boolean;

begin

   select true into v_sent_p
   from im_message
   where message_id = p_message_id
   and sent_p = true;

   if v_sent_p = true then
     return TRUE;
   else
     return FALSE;
   end if;

end;' language 'plpgsql';

------ end of message sent proc

------ start of author_p proc

-- select define_function_args('im_message__author_p','message_id','user_id');

create or replace function im_message__author_p (integer, integer)
returns boolean as '
declare
 p_message_id    alias for $1;
 p_user_id       alias for $2;

 -- local vars
 v_author_p    boolean;

begin

   select true into v_author_p
   from im_message
   where message_id = p_message_id
   and sender_user_id = p_user_id;

   if v_author_p = true then
     return TRUE;
   else
     return FALSE;
   end if;

end;' language 'plpgsql';

------ end of author_p proc

-- select define_function_args('im_message__addressee_p','message_id','user_id');

create or replace function im_message__addressee_p (integer, integer)
returns boolean as '
declare
 p_message_id    alias for $1;
 p_user_id       alias for $2;

 -- local vars
 v_addressee_p    boolean;

 -- Only the sender and addreessees have the ability to do things with a message. This function
 -- answers the question ''Is this user allowed to do that?''. Note that the sender is considered
 -- an addressee,

begin

   select true into v_addressee_p
   from im_addressees
   where message_id = p_message_id
   and addressee_id = p_user_id;

   if v_addressee_p = true then
     return TRUE;
   else
     return FALSE;
   end if;

end;' language 'plpgsql';

------ end of addressee_p proc

------ start of addressee insert proc

-- select define_function_args('im_addressee__insert','message_id','selected_addressee_id','address_mode_id','user_id');

create or replace function im_addressee__insert (integer, integer, integer, integer)
returns boolean as '
declare
 p_message_id               alias for $1;
 p_selected_addressee_id    alias for $2;
 p_address_mode_id          alias for $3;
 p_user_id                  alias for $4;

 -- local vars
 v_sent_p     boolean;
 v_author_p   boolean;
 v_success_p  boolean;

begin

   v_sent_p := im_message__sent_p(p_message_id);
   v_author_p := im_message__author_p(p_message_id, p_user_id);

   if v_sent_p = true then
     raise NOTICE ''Attempt to insert addressees of "sent" message. message_id - %'',p_message_id;
     v_success_p := FALSE;
   elsif v_author_p = false then
     raise NOTICE ''Attempt to insert addressees by user who is not the "sender". message_id - %'',p_message_id;
     v_success_p := FALSE;
   else
     insert into im_addressees (message_id, addressee_id, address_mode_id)
     values (p_message_id, p_selected_addressee_id, p_address_mode_id);
        v_success_p := FOUND;
        if FOUND = FALSE then
          raise NOTICE ''Insert of addressee failed for message_id - %'',p_message_id;
        else
          raise NOTICE ''Addressee insert successful for message_id - %'',p_message_id;
        end if;
   end if;

   return v_success_p;

end;' language 'plpgsql';

------ end of im_addressee__insert proc

----- start of addressee remove proc

-- select define_function_args('im_addressee__remove','message_id','selected_addressee_id','user_id');

create or replace function im_addressee__remove (integer, integer, integer, integer)
returns boolean as '
declare
 p_message_id               alias for $1;
 p_selected_addressee_id    alias for $2;
 p_address_mode_id          alias for $3;
 p_user_id                  alias for $4;

 -- local vars
 v_sent_p     boolean;
 v_author_p   boolean;
 v_success_p  boolean;

begin

   v_sent_p := im_message__sent_p(p_message_id);
   v_author_p := im_message__author_p(p_message_id, p_user_id);

   if v_sent_p = true then
     raise NOTICE ''Attempt to remove addressees of a "sent" message. message_id - %'',p_message_id;
     v_success_p := FALSE;
   elsif v_author_p = false then
     raise NOTICE ''Attempt to remove addressees by user who is not the "sender". message_id - %'',p_message_id;
     v_success_p := FALSE;
   else
     delete from im_addressees
     where message_id = p_message_id
     and addressee_id = p_selected_addressee_id
     and address_mode_id = p_address_mode_id;
     v_success_p := FOUND;
     if FOUND = FALSE then
       raise NOTICE ''Removal of addressee failed for message_id - %'',p_message_id;
     else
       raise NOTICE ''Addressee removal successful for message_id - %'',p_message_id;
      end if;
   end if;

   return v_success_p;

end;' language 'plpgsql';

------ end of im_addressee__remove proc

------ start of to_addressees_horiz_list proc

-- select define_function_args('to_addressees_horiz_list','message_id');

create or replace function im_to_addressees_horiz_list (integer)
returns varchar as '
declare
 p_message_id    alias for $1;

 -- local vars
 addressee       record;
 v_addressees    varchar;

begin

   v_addressees := '''';

   for addressee in
     select p.first_names||'' ''||p.last_name as identity
     from im_message m, im_addressees a, persons p
     where m.message_id = a.message_id
     and a.addressee_id = p.person_id
     and m.message_id = p_message_id
     and a.address_mode_id = ''1''

   loop

     v_addressees := v_addressees||addressee.identity||''; '';

   end loop;

   v_addressees := trim ( trailing ''; '' from v_addressees );

   return v_addressees;

end;' language 'plpgsql';

------ end of to_addressees_horiz_list proc

------ start of count_of_pending_messages

-- select define_function_args('count_of_pending_messages','folder_id', 'user_id');

create or replace function count_of_pending_messages (integer, integer)
returns varchar as '
declare
 p_folder_id      alias for $1;
 p_user_id        alias for $2;

 -- local vars
 v_message_count  integer;

begin

   select count(*) into v_message_count
   from im_addressees a, im_message m
   where a.message_id = m.message_id
   and a.addressee_id = p_user_id
   and a.folder_id = p_folder_id
   and a.deleted_p = false
   and ((a.read_p = ''f'' and m.sent_p = ''t'' and a.address_mode_id <> ''0'')
   or (m.sent_p = ''f'' and m.sender_user_id = p_user_id));

   if v_message_count = 0 then
     v_message_count := null;
   end if;

   return ''(''||v_message_count||'')'';

end;' language 'plpgsql';

------ end of count_of_pending_messages proc

------ start of im_mark_read_p

-- select define_function_args('im_mark_read_p','message_id', 'user_id');

create or replace function im_mark_read_p (integer, integer)
returns boolean as '
declare
 p_message_id     alias for $1;
 p_user_id        alias for $2;

begin

  update im_addressees set read_p = true
    where message_id = p_message_id
    and addressee_id = p_user_id
    and address_mode_id <> 0;

  return FOUND;

end;' language 'plpgsql';

------ end of im_mark_read_p proc

------ start of im_embolden_unread

-- select define_function_args('im_embolden_unread','message_id', 'user_id');

create or replace function im_embolden_unread (varchar, integer, integer)
returns varchar as '
declare
 p_input_string   alias for $1;
 p_message_id     alias for $2;
 p_user_id        alias for $3;

 -- local vars
 v_read_p         boolean;
 v_output_string  varchar;

begin

  select read_p into v_read_p
    from im_addressees
    where message_id = p_message_id
    and addressee_id = p_user_id
    and address_mode_id <> 0;
  
  if v_read_p = false then
    v_output_string = ''<b>''||p_input_string||''</b>'';
  else
    v_output_string = p_input_string;
  end if;

  return v_output_string;

end;' language 'plpgsql';

------ end of im_embolden_unread proc

------ start of im_message__user_bcc_p

-- select define_function_args('im_message__user_bcc_p','message_id', 'user_id');

create or replace function im_message__user_bcc_p (integer, integer)
returns boolean as '
declare
 p_message_id     alias for $1;
 p_user_id        alias for $2;

 -- local vars
 v_user_bcc_p     boolean;

begin

  select true into v_user_bcc_p
  from im_addressees
  where message_id = p_message_id
  and addressee_id = p_user_id
  and address_mode_id = 3;

  if v_user_bcc_p = true then
    return v_user_bcc_p;
  else
    return false;
  end if;

end;' language 'plpgsql';

------ end of im_message__user_bcc_p proc

------ start of im_message__new_reply

-- select define_function_args('im_message__new', 'sender_user_id', 'subject', 'body', 'priority_p', 'original_sender_id', 'original_message_id');

create or replace function im_message__new_reply (integer, varchar, varchar, boolean, integer, integer)
returns integer as '

declare
  p_sender_user_id       alias for $1; -- The id of the user that is sending the reply.
  p_subject              alias for $2; -- The subject of the message.
  p_body                 alias for $3; -- The body text of the message.
  p_priority_p           alias for $4; -- Whether the message is high priority or not.
  p_original_sender_id   alias for $5; -- The user_id of the person that sent the message to be replied to.
  p_original_message_id  alias for $6; -- The original message_id.

  -- local vars
  v_message_id           integer; -- This will be the unique id of the new reply message.
  v_cc_count             integer; -- For checking how many cc addressees there are.

begin

  select nextval(''im_message_id_seq'') into v_message_id;

  insert into im_message (
    message_id,
    sender_user_id,
    subject,
    body,
    priority_p,
    created_timestampz
  )
  values (
    v_message_id,
    p_sender_user_id,
    p_subject,
    p_body,
    p_priority_p,
    now()
  );

  if FOUND = true then

    raise NOTICE ''Created message from user_id - %'',p_sender_user_id;

    insert into im_addressees (
      message_id,
      addressee_id,
      address_mode_id,
      folder_id
    )
    values (
      v_message_id,
      p_sender_user_id,
      0,
      3
    );

    if FOUND = true then

        raise NOTICE ''Added user as sender to reply for message_id - %'',v_message_id;

        insert into im_addressees (
          message_id,
          addressee_id,
          address_mode_id,
          folder_id
          )
          values (
          v_message_id,
          p_original_sender_id,
          1,
          1
          );

        if FOUND = true then

            raise NOTICE ''Added original sender as recipient for reply with message_id - %'',v_message_id;
            
            select count(*) into v_cc_count
              from im_addressees
              where message_id = p_original_message_id
              and addressee_id not in (p_original_sender_id, p_sender_user_id)
              and address_mode_id <> 3;
              
            if v_cc_count > 0 then

              insert into im_addressees (
                select
                v_message_id,
                addressee_id,
                address_mode_id,
                false,
                1 as folder_id
                      from im_addressees
                      where message_id = p_original_message_id
                      and addressee_id not in (p_original_sender_id, p_sender_user_id)
                      and address_mode_id <> 3
                      );
            end if;

            if FOUND = true then
            
                raise NOTICE ''Addressees for cc added to reply with message_id - %'',v_message_id;

            else
              raise NOTICE ''Addition of cc addressees failed'';
              v_message_id := FOUND;
            end if;
        else
          raise NOTICE ''Addition of original sender to reply as recipient failed'';
          v_message_id := FOUND;
        end if;
    else
      raise NOTICE ''Addition of user to reply as sender failed'';
      v_message_id := FOUND;
    end if;
  else
    raise NOTICE ''Creation of reply failed'';
    v_message_id := FOUND;
  end if;

  return v_message_id;

end;' language 'plpgsql';

------ end of im_message__new_reply

------ start of im_message__priority_sign_p

-- select define_function_args('im_message__priority_sign_p','value');

create or replace function im_message__priority_sign_p (boolean)
returns varchar as '
declare
 p_value     alias for $1;

begin

  if p_value = true then
    return ''!'';
  else
    return '''';
  end if;

end;' language 'plpgsql';

------ end of im_message__priority_sign_p proc

