alter table forums_messages add format varchar2(30) default 'text/plain';

update forums_messages
set format = 'text/html'
where html_p = 't';
update forums_messages
set format = 'text/plain'
where html_p = 'f';

-- forums-messages-package-create.sql
--
-- The Forums Package
--
-- @author gwong@orchardlabs.com,ben@openforce.biz
-- @creation-date 2002-05-16
--
-- The Package for Messages
--
-- This code is newly concocted by Ben, but with heavy concepts and heavy code
-- chunks lifted from Gilbert. Thanks Orchard Labs!
--

select define_function_args ('forums_message__new', 'message_id,object_type;forums_message,forum_id,subject,content,format,user_id,posting_date,state,parent_id,creation_date,creation_user,creation_ip,context_id');

create function forums_message__new (integer,varchar,integer,varchar,text,char,integer,timestamptz,varchar,integer,timestamptz,integer,varchar,integer)
returns integer as '
declare
    p_message_id                    alias for $1;
    p_object_type                   alias for $2;
    p_forum_id                      alias for $3;
    p_subject                       alias for $4;
    p_content                       alias for $5;
    p_format                        alias for $6;
    p_user_id                       alias for $7;
    p_posting_date                  alias for $8;
    p_state                         alias for $9;
    p_parent_id                     alias for $10;
    p_creation_date                 alias for $11;
    p_creation_user                 alias for $12;
    p_creation_ip                   alias for $13;
    p_context_id                    alias for $14;
    v_message_id                    integer;
    v_forum_policy                  forums_forums.posting_policy%TYPE;
    v_state                         forums_messages.state%TYPE;
    v_posting_date                  forums_messages.posting_date%TYPE;
begin
    v_message_id := acs_object__new(
        p_message_id,
        p_object_type,
        p_creation_date,
        p_creation_user,
        p_creation_ip,
        coalesce(p_context_id, p_forum_id)
    );

    if p_state is null then
        select posting_policy
        into v_forum_policy
        from forums_forums
        where forum_id = p_forum_id;
             
        if v_forum_policy = ''moderated''
        then v_state := ''pending'';
        else v_state := ''approved'';
        end if;
    else
        v_state := p_state;
    end if;

    if p_posting_date is null then
        v_posting_date = now();
    else
        v_posting_date = p_posting_date;
    end if;

    insert into forums_messages
    (message_id, forum_id, subject, content, format, user_id, posting_date, parent_id, state)
    values
    (v_message_id, p_forum_id, p_subject, p_content, p_format, p_user_id, v_posting_date, p_parent_id, v_state);

    update forums_forums
    set last_post = v_posting_date
    where forum_id = p_forum_id;

    update forums_messages
    set last_child_post = v_posting_date
    where message_id = forums_message__root_message_id(v_message_id);
 
    return v_message_id;

end;
' language 'plpgsql';

select define_function_args ('forums_message__root_message_id', 'message_id');

create function forums_message__root_message_id (integer)
returns integer as '
declare
    p_message_id                    alias for $1;
    v_message_id                    forums_messages.message_id%TYPE;
    v_forum_id                      forums_messages.forum_id%TYPE;
    v_sortkey                       forums_messages.tree_sortkey%TYPE;
begin
    select forum_id, tree_sortkey
    into v_forum_id, v_sortkey
    from forums_messages
    where message_id = p_message_id;

    select message_id
    into v_message_id
    from forums_messages
    where forum_id = v_forum_id
    and tree_sortkey = tree_ancestor_key(v_sortkey, 1);

    return v_message_id;
end;
' language 'plpgsql' with(isstrict,iscachable);

select define_function_args ('forums_message__thread_open', 'message_id');

create function forums_message__thread_open (integer)
returns integer as '
declare
    p_message_id                    alias for $1;
    v_forum_id                      forums_messages.forum_id%TYPE;
    v_sortkey                       forums_messages.tree_sortkey%TYPE;
begin
    select forum_id, tree_sortkey
    into v_forum_id, v_sortkey
    from forums_messages
    where message_id = p_message_id;

    update forums_messages
    set open_p = ''t''
    where tree_sortkey between tree_left(v_sortkey) and tree_right(v_sortkey)
    and forum_id = v_forum_id;

    update forums_messages
    set open_p = ''t''
    where message_id = p_message_id;

    return 0;
end;
' language 'plpgsql';

select define_function_args ('forums_message__thread_close', 'message_id');

create function forums_message__thread_close (integer)
returns integer as '
declare
    p_message_id                    alias for $1;
    v_forum_id                      forums_messages.forum_id%TYPE;
    v_sortkey                       forums_messages.tree_sortkey%TYPE;
begin
    select forum_id, tree_sortkey
    into v_forum_id, v_sortkey
    from forums_messages
    where message_id = p_message_id;

    update forums_messages
    set open_p = ''f''
    where tree_sortkey between tree_left(v_sortkey) and tree_right(v_sortkey)
    and forum_id = v_forum_id;

    update forums_messages
    set open_p = ''f''
    where message_id = p_message_id;

    return 0;
end;
' language 'plpgsql';

select define_function_args ('forums_message__delete', 'message_id');

create function forums_message__delete (integer)
returns integer as '
declare
    p_message_id                    alias for $1;    
begin
    perform acs_object__delete(p_message_id);
    return 0;    
end;
' language 'plpgsql';

select define_function_args ('forums_message__delete_thread', 'message_id');

create function forums_message__delete_thread (integer)
returns integer as '
declare
    p_message_id                    alias for $1;
    v_forum_id                      forums_messages.forum_id%TYPE;
    v_sortkey                       forums_messages.tree_sortkey%TYPE;
    v_message                       RECORD;
begin
    select forum_id, tree_sortkey
    into v_forum_id, v_sortkey
    from forums_messages
    where message_id = p_message_id;
           
    -- if it is already deleted
    if v_forum_id is null
    then return 0;
    end if;

    -- delete all children
    -- order by tree_sortkey desc to guarantee
    -- that we never delete a parent before its child
    -- sortkeys are beautiful
    for v_message in select *
                     from forums_messages
                     where forum_id = v_forum_id
                     and tree_sortkey between tree_left(v_sortkey) and tree_right(v_sortkey)
                     order by tree_sortkey desc
    loop
        perform forums_message__delete(v_message.message_id);
    end loop;

    -- delete the message itself
    perform forums_message__delete(p_message_id);

    return 0;
end;
' language 'plpgsql';

select define_function_args('forums_message__name','message_id');

create function forums_message__name (integer)
returns varchar as '
declare
    p_message_id                    alias for $1;
begin
    return subject from forums_messages where message_id = p_message_id;
end;
' language 'plpgsql';

create or replace view forums_messages_approved as
    select *
    from forums_messages
    where state = 'approved';

create or replace view forums_messages_pending as
    select *
    from forums_messages
    where state = 'pending';