alter table forums_forums add (last_post date);
alter table forums_messages add (last_child_post date);

declare
  v_date date; 
begin

  for row in (select forum_id
                  from forums_forums)
  loop

    select last_modified into v_date
    from acs_objects
    where object_id = row.forum_id;

    update forums_forums
    set last_post = v_date
    where forum_id = row.forum_id;

  end loop;

  for row in (select message_id
                  from forums_messages
                  where parent_id is null)
  loop

    -- forums 0.1d did not properly set the last_modified field of the object
    -- row associated with the root message of a thread, so we need to calculate
    -- it here.

    select max(o.last_modified) into v_date
    from acs_objects o, forums_messages fm
    where forums_message.root_message_id(fm.message_id) = row.message_id
      and object_id = fm.message_id;

    update forums_messages
    set last_child_post = v_date
    where message_id = row.message_id;

  end loop;

end;
/
show errors;

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

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

drop view forums_forums_enabled;
create view forums_forums_enabled
as
    select *
    from forums_forums
    where enabled_p = 't';

create or replace package body forums_message
as

    function new (
        message_id in forums_messages.message_id%TYPE default null,
        object_type in acs_objects.object_type%TYPE default 'forums_message',
        forum_id in forums_messages.forum_id%TYPE,
        subject in forums_messages.subject%TYPE,
        content in varchar,
        html_p in forums_messages.html_p%TYPE default 'f',
        user_id in forums_messages.user_id%TYPE,
        posting_date in forums_messages.posting_date%TYPE default sysdate,
        state in forums_messages.state%TYPE default null,
        parent_id in forums_messages.parent_id%TYPE default null,
        creation_date in acs_objects.creation_date%TYPE default sysdate,
        creation_user in acs_objects.creation_user%TYPE,
        creation_ip in acs_objects.creation_ip%TYPE,
        context_id in acs_objects.context_id%TYPE default null
    ) return forums_messages.message_id%TYPE
    is
        v_message_id acs_objects.object_id%TYPE;
        v_sortkey forums_messages.tree_sortkey%TYPE;
        v_forum_policy forums_forums.posting_policy%TYPE;
        v_state forums_messages.state%TYPE;
    begin

        v_message_id := acs_object.new(
            object_id => message_id,
            object_type => object_type,
            creation_date => creation_date,
            creation_user => creation_user,
            creation_ip => creation_ip,
            context_id => nvl(context_id, forum_id)
        );

        if state is null
        then
            select posting_policy
            into v_forum_policy
            from forums_forums
            where forum_id= new.forum_id;

            if v_forum_policy = 'moderated' then
                v_state := 'pending';
            else
                v_state := 'approved';
            end if;
        else
            v_state := state;
        end if;

        insert into forums_messages
        (message_id, forum_id, subject, content, html_p, user_id, posting_date, parent_id, state)
        values
        (v_message_id, forum_id, subject, content, html_p, user_id, posting_date, parent_id, v_state);

        -- DRB: Can't use root_message_id() here because it triggers a "mutating table" error

        select tree_sortkey into v_sortkey
        from forums_messages
        where message_id = v_message_id;

        update forums_forums
        set last_post = posting_date
        where forum_id = forums_message.new.forum_id;

        update forums_messages
        set last_child_post = posting_date
        where forum_id = forums_message.new.forum_id
          and tree_sortkey = tree.ancestor_key(v_sortkey, 1);

        return v_message_id;
    end new;

    function root_message_id (
        message_id in forums_messages.message_id%TYPE
    ) return forums_messages.message_id%TYPE
    is
        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 = root_message_id.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 root_message_id;

    procedure thread_open (
        message_id in forums_messages.message_id%TYPE
    )
    is
        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 = thread_open.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 = thread_open.message_id;
    end thread_open;

    procedure thread_close (
        message_id in forums_messages.message_id%TYPE
    )
    is
        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 = thread_close.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 = thread_close.message_id;
    end thread_close;

    procedure delete (
        message_id in forums_messages.message_id%TYPE
    )
    is
    begin
        acs_object.delete(message_id);
    end delete;

    procedure delete_thread (
        message_id in forums_messages.message_id%TYPE
    )
    is
        v_forum_id forums_messages.forum_id%TYPE;
        v_sortkey forums_messages.tree_sortkey%TYPE;
        v_message forums_messages%ROWTYPE;
    begin
        select forum_id, tree_sortkey
        into v_forum_id, v_sortkey
        from forums_messages
        where message_id = delete_thread.message_id;

        -- if it's already deleted
        if SQL%NOTFOUND then
            return;
        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
            forums_message.delete(v_message.message_id);
        end loop;

        -- delete the message itself
        forums_message.delete(delete_thread.message_id);
    end delete_thread;

    function name (
        message_id in forums_messages.message_id%TYPE
    ) return varchar
    is
        v_name forums_messages.subject%TYPE;
    begin
        select subject
        into v_name
        from forums_messages
        where message_id = forums_message.name.message_id;

        return v_name;
    end name;

end forums_message;
/
show errors

create or replace package body forums_forum
as

    function new (
        forum_id in forums_forums.forum_id%TYPE  default null,
        object_type in acs_objects.object_type%TYPE default 'forums_forum',
        name in forums_forums.name%TYPE,
        charter in forums_forums.charter%TYPE default null,
        presentation_type in forums_forums.presentation_type%TYPE,
        posting_policy in forums_forums.posting_policy%TYPE,
        package_id in forums_forums.package_id%TYPE,
        creation_date in acs_objects.creation_date%TYPE default sysdate,
        creation_user in acs_objects.creation_user%TYPE,
        creation_ip in acs_objects.creation_ip%TYPE,
        context_id in acs_objects.context_id%TYPE default null
    ) return forums_forums.forum_id%TYPE
    is
        v_forum_id forums_forums.forum_id%TYPE;
    begin
        v_forum_id := acs_object.new(
            object_id => forum_id,
            object_type => object_type,
            creation_date => creation_date,
            creation_user => creation_user,
            creation_ip => creation_ip,
            context_id => nvl(context_id, package_id)
        );
        
        insert into forums_forums
        (forum_id, name, charter, presentation_type, posting_policy, package_id)
        values
        (v_forum_id, name, charter, presentation_type, posting_policy, package_id);

        return v_forum_id;
    end new;

    function name (
        forum_id in forums_forums.forum_id%TYPE
    ) return varchar
    is
        v_name forums_forums.name%TYPE;
    begin
        select name
        into v_name
        from forums_forums
        where forum_id = name.forum_id;

        return v_name;
    end name;
    
    procedure delete (
        forum_id in forums_forums.forum_id%TYPE
    )
    is
    begin
        acs_object.delete(forum_id);
    end delete;

end forums_forum;
/
show errors