--
-- acs-messaging sql/upgrade-4.0-4.0.1a.sql
--
-- @author jmp@arsdigita.com
-- @creation-date 2000-11-03
-- @cvs-id $Id: upgrade-4.0-4.0.1a.sql,v 1.1.1.1 2002/07/09 17:34:58 rmello Exp $
--

alter table acs_messages add (
    sent_date date
        constraint acs_messages_sent_date_nn
            not null
                disable,
    sender integer
        constraint acs_messages_sender_fk
            references parties (party_id)
                disable,
    rfc822_id varchar2(250)
        constraint acs_messages_rfc822_id_nn
            not null
                disable
        constraint acs_messages_rfc822_id_un
            unique
                disable
);

create table acs_mess_up (
    id integer primary key,
    sent_date date,
    sender integer,
    rfc822_id varchar2(250)
);

insert into acs_mess_up
    select m.message_id,
            r.publish_date as sent_date,
            o.creation_user as sender,
            (sysdate || '.' || message_id || '@'
             || utl_inaddr.get_host_name||'.hate') as rfc822_id
        from acs_objects o, cr_items i, cr_revisions r, acs_messages m
        where m.message_id = i.item_id
            and m.message_id = o.object_id
            and r.revision_id = i.live_revision;

update acs_messages
    set sent_date = (select sent_date from acs_mess_up where id = message_id),
        sender = (select sender from acs_mess_up where id = message_id),
        rfc822_id = (select rfc822_id from acs_mess_up where id = message_id);

drop table acs_mess_up;

alter table acs_messages modify constraint acs_messages_sent_date_nn enable;
alter table acs_messages modify constraint acs_messages_sender_fk enable;
alter table acs_messages modify constraint acs_messages_rfc822_id_nn enable;
alter table acs_messages modify constraint acs_messages_rfc822_id_un enable;

create or replace view acs_messages_all as
    select m.message_id, m.reply_to, m.sent_date, m.sender, m.rfc822_id,
           r.title, r.mime_type, r.content, o.context_id 
        from acs_objects o, cr_items i, cr_revisions r, acs_messages m
        where o.object_id = m.message_id and i.item_id = m.message_id
            and r.revision_id = i.live_revision;

create table acs_messages_outgoing (
    message_id integer
        constraint amo_message_id_fk
            references acs_messages (message_id) on delete cascade,
    recipient_id integer
        constraint amo_recipient_id_fk
            references parties (party_id),
    grouping_id integer,
    wait_until date not null,
    constraint acs_messages_outgoing_pk
        primary key (message_id, recipient_id)
);

comment on column acs_messages_outgoing.grouping_id is '
    This identifier is used to group sets of messages to be sent as
    digests.  When a message is about to be sent, any other messages
    with the same grouping_id will be put together with it in a
    digest.  It is recommended but not required that an object id is
    used.  Bboard, for example, might use the forum id that the user''s
    subscribed to.  For instant (non-digest) updates, it would be
    appropriate to use null, which is never equal to anything else.
';

comment on column acs_messages_outgoing.wait_until is '
    Don''t schedule a send until after this date.  If another message with
    the same grouping ID is scheduled to be sent, then this message may be
    sent at the same time.  (So, for example, daily digests would be
    achieved by setting the grouping_id to the same value, and the wait_until
    value to the end of the current day.  As soon as one message in the group
    is to be sent, all will be sent.)
';

create or replace package acs_message
as

    function new (
        message_id    in acs_messages.message_id%TYPE   default null,
        reply_to      in acs_messages.reply_to%TYPE     default null,
        sent_date     in acs_messages.sent_date%TYPE    default sysdate,
        sender        in acs_messages.sender%TYPE       default null,
        rfc822_id     in acs_messages.rfc822_id%TYPE    default null,
        title         in cr_revisions.title%TYPE        default null,
        mime_type     in cr_revisions.mime_type%TYPE    default 'text/plain',
        text          in varchar2                       default null,
        data          in cr_revisions.content%TYPE      default null,
        context_id    in acs_objects.context_id%TYPE,
        creation_date in acs_objects.creation_date%TYPE default sysdate,
        creation_user in acs_objects.creation_user%TYPE default null,
        creation_ip   in acs_objects.creation_ip%TYPE   default null,
        object_type   in acs_objects.object_type%TYPE   default 'acs_message'
    ) return acs_objects.object_id%TYPE;

    procedure delete (
        message_id    in acs_messages.message_id%TYPE
    );

    function message_p (
        message_id    in acs_messages.message_id%TYPE
    ) return char;

    procedure send (
        message_id    in acs_messages.message_id%TYPE,
        recipient_id  in parties.party_id%TYPE,
        grouping_id   in integer default NULL,
        wait_until    in date default SYSDATE
    );

end acs_message;
/
show errors

create or replace package body acs_message
as

    function new (
        message_id    in acs_messages.message_id%TYPE   default null,
        reply_to      in acs_messages.reply_to%TYPE     default null,
        sent_date     in acs_messages.sent_date%TYPE    default sysdate,
        sender        in acs_messages.sender%TYPE       default null,
        rfc822_id     in acs_messages.rfc822_id%TYPE    default null,
        title         in cr_revisions.title%TYPE        default null,
        mime_type     in cr_revisions.mime_type%TYPE    default 'text/plain',
        text          in varchar2                       default null,
        data          in cr_revisions.content%TYPE      default null,
        context_id    in acs_objects.context_id%TYPE,
        creation_date in acs_objects.creation_date%TYPE default sysdate,
        creation_user in acs_objects.creation_user%TYPE default null,
        creation_ip   in acs_objects.creation_ip%TYPE   default null,
        object_type   in acs_objects.object_type%TYPE   default 'acs_message'
    ) return acs_objects.object_id%TYPE
    is
        v_message_id acs_messages.message_id%TYPE;
        v_rfc822_id  acs_messages.rfc822_id%TYPE;
        v_name       cr_items.name%TYPE;
    begin
        if message_id is null then
            select acs_object_id_seq.nextval into v_message_id from dual;
        else
            v_message_id := message_id;
        end if;

        if rfc822_id is null then
            v_rfc822_id := sysdate || '.' || v_message_id || '@' ||
                utl_inaddr.get_host_name || '.hate';
        else
            v_rfc822_id := rfc822_id;
        end if;

        v_name := v_rfc822_id;

        v_message_id := content_item.new (
            name => v_name,
            parent_id => context_id,
            item_id => message_id,
            creation_date => creation_date,
            creation_user => creation_user,
            creation_ip => creation_ip,
            item_subtype => object_type,
            title => title,
            mime_type => mime_type,
            text => text,
            data => data,
            is_live => 't'
        );

        -- I hate you, milkman CR.
        -- Fix the broken permissions stuff content_item.new does
        update acs_objects set security_inherit_p = 't'
            where object_id = v_message_id;
        delete from acs_permissions where object_id = v_message_id;

        insert into
            acs_messages (message_id, reply_to, sent_date, sender, rfc822_id)
            values (v_message_id, reply_to, sent_date, sender, v_rfc822_id);

        return v_message_id;    
    end new;

    procedure delete (
        message_id in acs_messages.message_id%TYPE
    )
    is
    begin
        delete from acs_messages
            where message_id = acs_message.delete.message_id;
        content_item.delete(message_id);
    end;

    function message_p (
        message_id in acs_messages.message_id%TYPE
    ) return char
    is
        v_check_message_id char(1);
    begin
        select decode(count(message_id),0,'f','t') into v_check_message_id
            from acs_messages
            where message_id = message_p.message_id;
        return v_check_message_id;
    end message_p;

    procedure send (
        message_id    in acs_messages.message_id%TYPE,
        recipient_id  in parties.party_id%TYPE,
        grouping_id   in integer default NULL,
        wait_until    in date default SYSDATE
    )
    is
        v_wait_until date;
    begin
        v_wait_until := nvl(wait_until, SYSDATE);
        insert into acs_messages_outgoing
            (message_id, recipient_id, grouping_id, wait_until)
        values
            (message_id, recipient_id, grouping_id, nvl(wait_until,SYSDATE));
    end;

end acs_message;
/
show errors