--
-- chat.sql 
--
-- by philg@mit.edu on April 25, 1999
--

create sequence chat_room_id_sequence;

create table chat_rooms (
	chat_room_id		integer primary key,
	pretty_name		varchar(100),
	-- if set, this is a private chat room, associated with
	-- a particular user group; otherwise public
	private_group_id	integer references user_groups,
	moderated_p		char(1) default 'f' 
              check (moderated_p in ('t','f')),
	-- if NULL, this room gets archived permanently; can be fractional
	expiration_days		integer,
	creation_date		datetime default current_timestamp not null,
	active_p		char(1) default 't' check (active_p in ('t','f')),
	-- permissions can be expanded to be more complex later
    scope			varchar(20) not null,
	group_id		integer references user_groups
	 -- insure consistant state 
--DRB: commented out as this breaks PG 6.5 (but not PG 7.0)
--        	constraint chat_scope_not_null_check check ((scope='group' and group_id is not null) 
--                                                 or (scope='public' and group_id is null))
);

create index chat_rooms_group_idx on chat_rooms ( group_id );

create sequence chat_msg_id_sequence;

-- if the ACS the content tagging system, e.g., for naughty words, is
-- enabled, we store a content_tag (bit mask) for the original MSG
-- and also store a bowdlerized version of the MSG (if necessary) 
-- for quick serving to people who've enabled filtering.

-- so the query for a filtered user would be 
--     coalesce(msg_bowdlerized, msg) as filtered_msg 

create table chat_msgs (
	chat_msg_id	integer primary key,
	msg		varchar(4000) not null,
	msg_bowdlerized	varchar(4000),
	content_tag	integer,
	html_p		char(1) default 'f' check (html_p in ('t','f')),
	approved_p	char(1) default 't' check(approved_p in ('t','f')),
	-- things like "joe has entered the room" 
	system_note_p	char(1) default 'f' check(system_note_p in ('t','f')),
	creation_date	datetime not null,
	creation_user		integer not null references users(user_id),
	creation_ip_address	varchar(50) not null,
	-- if set, this is a 1:1 message
	recipient_user		integer references users(user_id),
	-- if set, this is a broadcast message of some sort
	chat_room_id		integer references chat_rooms
);

-- to support a garden variety chat room display

create index chat_msgs_by_room_date on chat_msgs ( chat_room_id, creation_date );

-- to support an admin looking into a user's history or a customer service 
-- rep's history

create index chat_msgs_by_user on chat_msgs ( creation_user );

-- to support a query by a user for "any new messages for me?"

create index chat_msgs_by_recipient on chat_msgs ( recipient_user, creation_date );

-- Helper to count messages in a room (workaround for no outer joins and no
-- subselects in target list)

create function chat_room_msgs(integer) returns integer as '
begin
  return count(*) from chat_msgs where chat_room_id = $1;
end;
' language 'plpgsql';