begin;

alter table chat_rooms add login_messages_p char(1) default 't';
alter table chat_rooms add constraint chat_login_messaages_flush_ck check(login_messages_p in ('t','f'));

alter table chat_rooms add logout_messages_p char(1) default 't';
alter table chat_rooms add constraint chat_logout_messaages_flush_ck check(logout_messages_p in ('t','f'));

create or replace package chat_room
as
    function new (
        room_id        in chat_rooms.room_id%TYPE        default null,
        pretty_name    in chat_rooms.pretty_name%TYPE,
        description    in chat_rooms.description%TYPE    default null,
        moderated_p    in chat_rooms.moderated_p%TYPE    default 'f',
        active_p       in chat_rooms.active_p%TYPE       default 't',
        archive_p      in chat_rooms.archive_p%TYPE      default 'f',
	auto_flush_p   in chat_rooms.auto_flush_p%TYPE	 default 't',
	auto_transcript_p in chat_rooms.auto_transcript_p%TYPE default 'f',
	login_messages_p  in chat_rooms.login_messages_p%TYPE default  't',
	logout_messages_p in chat_rooms.logout_messages_p%TYPE default 't',
        context_id     in acs_objects.context_id%TYPE    default null,
        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 'chat_room'
    ) return acs_objects.object_id%TYPE;

    procedure del (
        room_id        in chat_rooms.room_id%TYPE
    );

    procedure edit (
        room_id        in chat_rooms.room_id%TYPE,
        pretty_name    in chat_rooms.pretty_name%TYPE,
        description    in chat_rooms.description%TYPE,
        moderated_p    in chat_rooms.moderated_p%TYPE,
        active_p       in chat_rooms.active_p%TYPE,
        archive_p      in chat_rooms.archive_p%TYPE,
   		auto_flush_p   in chat_rooms.auto_flush_p%TYPE,
		auto_transcript_p	in chat_rooms.auto_transcript_p%TYPE
    );

    function name (
        room_id        in chat_rooms.room_id%TYPE
    ) return chat_rooms.pretty_name%TYPE;

    procedure message_post (
        room_id        in chat_msgs.room_id%TYPE,
        msg            in chat_msgs.msg%TYPE             default null,
        html_p         in chat_msgs.html_p%TYPE          default 'f',
        approved_p     in chat_msgs.approved_p%TYPE      default 't',
        creation_user  in chat_msgs.creation_user%TYPE,
        creation_ip    in chat_msgs.creation_ip%TYPE     default null,
        creation_date  in chat_msgs.creation_date%TYPE   default sysdate
    );

    function message_count (
        room_id        in chat_rooms.room_id%TYPE
    ) return integer;

    procedure delete_all_msgs (
        room_id        in chat_rooms.room_id%TYPE
    );

end chat_room;

create or replace package body chat_room
as
    function new (
        room_id        in chat_rooms.room_id%TYPE        default null,
        pretty_name    in chat_rooms.pretty_name%TYPE,
        description    in chat_rooms.description%TYPE    default null,
        moderated_p    in chat_rooms.moderated_p%TYPE    default 'f',
        active_p       in chat_rooms.active_p%TYPE       default 't',
        archive_p      in chat_rooms.archive_p%TYPE      default 'f',
	auto_flush_p   in chat_rooms.auto_flush_p%TYPE	 default 't',
	auto_transcript_p in chat_rooms.auto_transcript_p%TYPE default 'f',
	login_messages_p  in chat_rooms.login_messages_p%TYPE default  't',
	logout_messages_p in chat_rooms.logout_messages_p%TYPE default 't',
        context_id     in acs_objects.context_id%TYPE    default null,
        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 'chat_room'
    ) return acs_objects.object_id%TYPE
    is
        v_room_id chat_rooms.room_id%TYPE;
    begin
        v_room_id := acs_object.new (
            object_type   => chat_room.new.object_type,
            creation_date => chat_room.new.creation_date,
            creation_user => chat_room.new.creation_user,
            creation_ip   => chat_room.new.creation_ip,
            context_id    => chat_room.new.context_id
        );

        insert into chat_rooms (
            room_id,
            pretty_name,
            description,
            moderated_p,
            active_p,
            archive_p,
	    auto_flush_p,
	    auto_transcript_p,
	    login_messages_p,
	    logout_messages_p)
	values (
            v_room_id,
            chat_room.new.pretty_name,
            chat_room.new.description,
            chat_room.new.moderated_p,
            chat_room.new.active_p,
            chat_room.new.archive_p,
	    chat_room.new.auto_flush_p,
	    chat_room.new.auto_transcript_p,
	    chat_room.new.login_messages_p,
	    chat_room.new.logout_messages_p);

        return v_room_id;
    end new;

    procedure del (
        room_id        in chat_rooms.room_id%TYPE
    )
    is
    begin
        -- First erase all the messages relate to this chat room.
        delete from chat_msgs where room_id = chat_room.del.room_id;

        -- Delete all privileges associate with this room
        delete from acs_permissions where object_id = chat_room.del.room_id;

        -- Now delete the chat room itself.
        delete from chat_rooms where room_id = chat_room.del.room_id;

        acs_object.del(room_id);
    end del;

    procedure edit (
        room_id        in chat_rooms.room_id%TYPE,
        pretty_name    in chat_rooms.pretty_name%TYPE,
        description    in chat_rooms.description%TYPE,
        moderated_p    in chat_rooms.moderated_p%TYPE,
        active_p       in chat_rooms.active_p%TYPE,
        archive_p      in chat_rooms.archive_p%TYPE,
	auto_flush_p   in chat_rooms.auto_flush_p%TYPE,
	auto_transcript_p	in chat_rooms.auto_transcript_p%TYPE,
	login_messages_p  in chat_rooms.login_messages_p%TYPE default  't',
	logout_messages_p in chat_rooms.logout_messages_p%TYPE default 't'
    )
    is
    begin
        update chat_rooms set
            pretty_name       = chat_room.edit.pretty_name,
            description       = chat_room.edit.description,
            moderated_p       = chat_room.edit.moderated_p,
            active_p          = chat_room.edit.active_p,
            archive_p         = chat_room.edit.archive_p,
            auto_flush_p      = chat_room.edit.auto_flush_p,
            login_messages_p  = chat_room.edit.login_messages_p,
            logout_messages_p = chat_room.edit.logout_messages_p	    
        where
            room_id = chat_room.edit.room_id;
    end edit;

    function name (
        room_id        in chat_rooms.room_id%TYPE
    ) return chat_rooms.pretty_name%TYPE
    is
        v_room_name chat_rooms.pretty_name%TYPE;
    begin
        select pretty_name into v_room_name
        from chat_rooms
        where room_id = chat_room.name.room_id;

        return v_room_name;
    end name;

    procedure message_post (
        room_id        in chat_msgs.room_id%TYPE,
        msg            in chat_msgs.msg%TYPE             default null,
        html_p         in chat_msgs.html_p%TYPE          default 'f',
        approved_p     in chat_msgs.approved_p%TYPE      default 't',
        creation_user  in chat_msgs.creation_user%TYPE,
        creation_ip    in chat_msgs.creation_ip%TYPE     default null,
        creation_date  in chat_msgs.creation_date%TYPE   default sysdate
    )
    is
        v_msg_id chat_msgs.msg_id%TYPE;
        v_msg_archive_p chat_rooms.archive_p%TYPE;
        v_msg chat_msgs.msg%TYPE;
    begin
        -- Get msg id from the global acs_object sequence.
        select acs_object_id_seq.nextval into v_msg_id from dual;

        select archive_p into v_msg_archive_p
        from chat_rooms
        where room_id = chat_room.message_post.room_id;

        if v_msg_archive_p = 't' then
            v_msg := msg;
        else
            v_msg := null;
        end if;

        -- Insert into chat_msgs table.
        insert into chat_msgs (
            msg_id,
            room_id,
            msg,
            msg_len,
            html_p,
            approved_p,
            creation_user,
            creation_ip,
            creation_date)
        values (
            v_msg_id,
            room_id,
            v_msg,
            nvl(length(msg), 0),
            html_p,
            approved_p,
            creation_user,
            creation_ip,
            creation_date) ;
    end message_post;


    function message_count (
        room_id        in chat_rooms.room_id%TYPE
    ) return integer
    is
        v_count integer;
    begin
        select count(*) into v_count
        from chat_msgs
        where room_id = chat_room.message_count.room_id;

        return v_count;
    end message_count;

    procedure delete_all_msgs (
        room_id        in chat_rooms.room_id%TYPE
    )
    is
    begin
        delete from chat_msgs where room_id = chat_room.delete_all_msgs.room_id;
    end delete_all_msgs;

end chat_room;

end;