--
-- data-model.sql for generic bboard system
-- bashed for Oracle8 12/3/97
-- bashed to run with community data model (users table rather
-- than unauthenticated email/name) by Tracy Adams in the summer of 1998
-- bashed 9/13/98 by philg to run with the Scorecard data model
-- edited on 11/15/98 by philg so that the usgeospatial style
-- of forum, from www.scorecard.org, would be part of the
-- generic community system
-- added active_p to bboard_topics teadams@mit.edu 1/7/98
-- edited by Tracy Adams (teadams@mit.edu) on 2/7/98 to prevent
--   multiple row inserted into msg_id_generator if this file
--   is loaded more than once
--
-- updated by hqm to use numeric sequence vals as primary keys,
-- and to integrate better with ACS user/group model 
-- see /doc/bboard-new.html 
-- hqm@ai.mit.edu 8/99

-- Copyright 1996, 1997 Philip Greenspun (philg@mit.edu)
--

create sequence bboard_topic_id_sequence;

create table bboard_topics (
	topic_id	integer not null primary key,
	-- topic name
	topic		varchar(200) unique not null,
	-- read access rights
	-- can be one of any (anonymous), public (any registered user), group
	read_access	varchar(16) default 'any' check (read_access in ('any','public','group')),
	-- write (post new message) access
	-- can be one of (public, group)
	write_access 	varchar(16) default 'public' check (write_access in ('public','group')),
 	users_can_initiate_threads_p	boolean default 't',
	backlink	varchar(4000),	-- a URL pointing back to the relevant page
	backlink_title	varchar(4000),	-- what to say for the link back
	blather		varchar(4000),	-- arbitrary HTML text that goes at the top of the page
	-- posting is always restricted to members
	-- is viewing restricted to members or only posting?
	restricted_p	boolean default 'f',
	primary_maintainer_id	integer not null references users(user_id),
	subject_line_suffix	varchar(40),	-- whether to put something after the subject line, e.g., 'name', 'date'
	notify_of_new_postings_p	char(1) default 't' check (notify_of_new_postings_p in ('t','f')),	-- send email when a message is added?
	pre_post_caveat		varchar(4000),	-- special HTML to encourage user to search elsewhere before posting a new message
	-- 'unmoderated', 'new_threads_by_maintainer', 'new_threads_by_helpers'
	-- 'all_threads_by_maintainer', 'all_threads_by_helpers','answers_only_from_helpers', 'moderated_topics'
	moderation_policy	varchar(40),
	-- used for keeping messages for 50 US states, for example
	-- where each state is a top level posting but not really a 
	-- question
	-- if this isn't NULL then we put in an "about" link
	policy_statement	varchar(4000),
	-- presentation_type  q-and-a (Question and answer format), threads (standard listserve), or ed_com (Question and response pages separated, editiorial language)
	presentation_type	varchar(20) default 'q_and_a' constraint check_presentation_type check(presentation_type in ('q_and_a','threads', 'ed_com', 'usgeospatial')),
	-- stuff just for Q&A  use
	q_and_a_sort_order	varchar(4) default 'asc' not null check (q_and_a_sort_order in ('asc','desc')),
	q_and_a_categorized_p	boolean default 'f',
	q_and_a_new_days	integer default 7,
	q_and_a_solicit_category_p	boolean default 't',
	q_and_a_cats_user_extensible_p	boolean default 'f',
	-- use the interest level system
	q_and_a_use_interest_level_p 	boolean default 't',
	-- for popular boards, only show categories for non-new msgs
	q_and_a_show_cats_only_p	boolean default 'f',
	-- for things like NE43 memory project and 6.001 pset site 
	-- top level threads can have custom sort keys, e.g., date
	-- of story (rather than date of posting)
	custom_sort_key_p 		boolean default 'f',
	custom_sort_key_name		varchar(50),	-- for display
	-- SQL data type, lowercase, e.g., "date" (ANSI format so that it sorts)
	-- we really only use this for user input validation
	custom_sort_key_type		varchar(20),
	custom_sort_order		varchar(4) default 'asc' not null check (custom_sort_order in ('asc','desc')),
	-- display to user if there aren't message yet
	custom_sort_not_found_text	varchar(4000),
	-- ask user to supply a sort key with new postings
	custom_sort_solicit_p		boolean default 'f',
	-- ask user to supply a pretty sort key for display
	-- e.g., "Fall 1997" instead of 9-29-97
	custom_sort_solicit_pretty_p   boolean default 'f',
	custom_sort_pretty_name		varchar(50),	-- for display
	custom_sort_pretty_explanation	varchar(100),	-- why we ask for it
	-- fragment of Tcl code that evaluates to 0 if a sort key is
	-- bad, 1 if OK, assumed to include "$custom_sort_key"
	custom_sort_validation_code	varchar(4000),
	-- for the 2nd round of 6.001 discussion thinking
	category_centric_p		boolean default 'f',
	-- image and file uploading
	uploads_anticipated		varchar(30) check (uploads_anticipated is null or uploads_anticipated in ('images','files','images_or_files')),
	-- should this forum come up on the user interface?
	active_p			boolean default 't',
	group_id                        integer references user_groups
);


-- useful for maintaining FAQs

create table bboard_q_and_a_categories (
	topic_id	integer not null references bboard_topics,	
	category	varchar(200) not null
);

-- useful for keeping idiots out of forums, e.g., looking for
-- "aperature" in the photo.net Q&A forum

create table bboard_bozo_patterns (
	topic_id	integer not null references bboard_topics,
	the_regexp	varchar(200) not null,
	scope		varchar(20) default 'both' 
           check(scope in ('one_line','message','both')),
        message_to_user	varchar(4000),
	creation_date	datetime not null,
	creation_user 		integer not null references users(user_id),
	creation_comment	varchar(4000),
	primary key (topic_id, the_regexp)
);

-- **** primary key using index tablespace photonet_index

create table bboard (
	msg_id		char(6) primary key,
	refers_to	char(6),
        root_msg_id	char(6),
	topic_id	integer not null references bboard_topics,
	category	varchar(200),	-- only used for categorized Q&A forums
	originating_ip	varchar(16),	-- stored as string, separated by periods
	user_id		integer not null references users,
	one_line	varchar(700),
	-- message can now be full-lengthed text field in PostreSQL v7.0
	message		lztext,
	-- html_p - is the message in html or not
	html_p		boolean default 'f',
	posting_time	datetime,
	expiration_days	integer,	-- optional N days after posting_time to expire
	-- really only used for postings that initiate threads
	interest_level	integer check ( interest_level is null or interest_level >= 0 and interest_level <= 10 ),
	sort_key	varchar(700),
	-- only used for weirdo things like NE43 memory project and 
	-- 6.001
	-- if this is a DATE, it has to be an ANSI so that it will
	-- sort lexicographically
	-- I guess we should constraint this to be UNIQUE
	custom_sort_key		varchar(100),
	custom_sort_key_pretty	varchar(100),
	-- stuff for US geospatial forums
	epa_region	integer check(epa_region is null or epa_region >= 1 and epa_region <= 10),
	usps_abbrev	char(2) references states,
	fips_county_code	char(5) references counties,
	zip_code	varchar(5),
        urgent_p        boolean not null default 'f'
);

-- for all of the following indices: **** tablespace photonet_index

create index bboard_by_user on bboard (user_id);

-- this SORT_KEY index will make fetching single Q&A thread fast
-- but it will only work if sort_key is bashed down to 758 chars
-- (note:  Illustra could trivially have indexed this)

-- DRB
-- "like" on an indexed field doesn't work in 6.5, strange, and was
-- fixed in 6.5.1 by forcing a sequential scan.  I've added a root_msg_id
-- field and am indexing it to get around the problem.

-- create index bboard_by_sort_key on bboard ( sort_key );
create index bboard_by_root_msg_id on bboard ( root_msg_id );

-- we need this to avoid an O(N^2) search for "unanswered questions"
-- (made worse by stupid Illustra's inability to cache after a sequential
-- scan)

-- don't think we need this anymore because we never ask for
-- refers_to without a topic spec (hence the new_questions 
-- concat index will work fine)
-- OOOps *** we do in fact need this for the unanswered questions

create index bboard_index_by_refers_to on bboard ( refers_to );

-- this is designed to make checking for already posted messages faster
-- on a system where not all of the messages are in one TOPIC then
-- this should be a concatenated index on topic, one_line

create index bboard_index_by_one_line on bboard ( one_line );
-- don't need this anymore because "new_questions one works"
-- create index bboard_by_topic on bboard ( topic );

-- let's try to make the very top-level query load faster

create index bboard_for_new_questions on bboard ( topic_id, refers_to, posting_time );

-- let's try to make the "postings in one category" faster

create index bboard_for_one_category on bboard ( topic_id, category, refers_to );

-- you might want this depending on how you think custom sort keys are handled
-- can't have just custom_sort_key unique because then you can't have the 
-- same one for two topics

-- create unique index bboard_index_custom on bboard ( topic_id, custom_sort_key );


-- let's try to make the "first N days" query fast
-- create index bboard_for_top_N on bboard using btree ( topic, refers_to, posting_time );
-- fails: W01P0G:warning: index hint for range variable bboard is unusable

-- takes a sort_key and returns just the six digit root
-- doesn't work as well as you'd think because you can't 
-- GROUP BY a functional result

--create function bboard_root_msg(text) returns char(6)
--as 
--return substring ( $1 from 1 for 6 );

create view bboard_new_answers_helper 
as
select b.root_msg_id, b.topic_id, b.posting_time from bboard b
where b.refers_to is not null;

create function bboard_uninteresting_p (integer)
returns char
AS
'
BEGIN
  IF ($1 < 4) THEN
    return ''t'';  
  ELSE
    return ''f'';
  END IF;
END;
' language 'plpgsql';

--create index bboard_pls_index on bboard using pls
--( one_line, message, email, name );

create table msg_id_generator (
	last_msg_id	char(6)
);

-- Apparently Ben and I (DRB) ran into similar problems trying
-- to port over the generator, and both punted and just did an
-- insert...but this insert's a little fancier than his, because
-- it only inserts if the table's empty.

insert into msg_id_generator 
select '000000'
where 0 = (select count(*) from msg_id_generator);

--
-- an "email me if changed" system
--

create table bboard_email_alerts (
	user_id		integer not null references users,
	topic_id	integer not null references bboard_topics,
	valid_p		boolean default 't',	-- we set this to 'f' if we get bounces
	frequency	varchar(30),		-- 'instant', 'daily', 'Monday/Thursday', 'weekly', etc.	
	keywords	varchar(2000)	-- stuff the user is interested in
);

create index bboard_email_alerts_idx on bboard_email_alerts(user_id);

-- Alert by thread system; obsoletes notify field in bboard table.
create table bboard_thread_email_alerts (
	thread_id	char(6) references bboard, -- references msg_id of thread root
	user_id		integer references users,
	primary key (thread_id, user_id)
);



--
-- this holds the last time we sent out notices and the total
-- number of messages sent (just for fun)
--

-- had to change name of table from 
-- bboard_email_alerts_last_updates

create table bboard_email_alerts_updates (
	weekly	datetime,
	weekly_total	integer,
	daily	datetime,
	daily_total	integer,
	monthu	datetime,
	monthu_total	integer
);

-- need something to initialize this table 

insert into bboard_email_alerts_updates 
(weekly, weekly_total, daily, daily_total, monthu, monthu_total)
values
(current_timestamp,0,current_timestamp,0,current_timestamp,0);

create function bboard_contains (varchar, varchar, varchar, varchar, varchar)
returns integer as '
declare
  email alias for $1;
  user_name alias for $2;
  one_line alias for $3;
  message alias for $4;
  space_sep_list_untrimmed alias for $5;
  space_sep_list        varchar(32000);
  upper_indexed_stuff   varchar(32000);
  -- if you call this var START you get hosed royally
  first_space           integer;
  score                 integer;
BEGIN 
  space_sep_list := upper(ltrim(rtrim(space_sep_list_untrimmed)));
  upper_indexed_stuff := upper(email || user_name || one_line || substr(message,30000));
  score := 0;
  IF space_sep_list is null or upper_indexed_stuff is null THEN
    RETURN score;  
  END IF;
  LOOP
   first_space := position(space_sep_list in '' '');
   IF first_space = 0 THEN
     -- one token or maybe end of list
     IF position(upper_indexed_stuff in space_sep_list) <> 0 THEN
        RETURN score+10;
     END IF;
     RETURN score;
   ELSE
   -- first_space <> 0
     IF position(upper_indexed_stuff in substr(space_sep_list,1,first_space-1)) <> 0 THEN
        score := score + 10;
     END IF;
   END IF;
    space_sep_list := substr(space_sep_list,first_space+1);
  END LOOP;  
END;
' language 'plpgsql';

-- for geospatialized forum

-- There must be one row for every state, though we guess that you
-- don't have to use the same 10 EPA regions that we used for
-- Scorecard

-- if you want to use this, feed your database the epa-regions.dmp 
-- file that is in the /install directory

-- create table bboard_epa_regions (
-- 	state_name		varchar(30),
--  	fips_numeric_code	char(2),
-- 	epa_region		integer,
-- 	usps_abbrev		char(2),
--         -- "Great Lakes Region", "Central Region", etc. 
--         -- Not very normalized, but easy.... -jsc
--         description             varchar(50)
-- );

-- for uploading files with bboard postings

-- these are stored in a configurable directory

-- we add photos, Word and Excel documents, etc.
-- file_type is "photo", "spreadsheet", "plaintext"
-- "pdf", "html", "word", "miscbinary", "audio"

-- we only allow one upload per message

create sequence bboard_upload_id_sequence;

create table bboard_uploaded_files (
	bboard_upload_id	integer primary key,
	msg_id			char(6) not null unique references bboard,
	file_type		varchar(100),	-- e.g., "photo"
	file_extension		varchar(50), 	-- e.g., "jpg"
	-- can be useful when deciding whether to present all of something
	n_bytes			integer,
	-- what this file was called on the client machine
	client_filename		varchar(4000) not null,
	-- generally the filename will be "*msg_id*-*upload_id*.extension"
	-- where the extension was the originally provided (so 
	-- that ns_guesstype will work)
	filename_stub		varchar(200) not null,
	-- fields that only make sense if this is an image
	caption			varchar(4000),
	-- will be null if the photo was small to begin with
	thumbnail_stub		varchar(200),
	original_width		integer,
	original_height		integer
);

-- Part of a series of security fixes
-- (BMA, spec'ed by aD)
create function bboard_user_can_view_topic_p (integer,integer)	
returns char AS '
DECLARE
	v_user_id alias for $1;
	v_topic_id alias for $2;
	v_read_access varchar(16);	     
	v_group_id    integer;
	v_count       integer;
BEGIN
	select read_access, group_id into v_read_access, v_group_id
	from bboard_topics
	where topic_id = v_topic_id;

	IF v_read_access = ''any'' or v_read_access = ''public'' THEN
	RETURN ''t'';
	END IF;

	select count(*) into v_count
	from user_group_map
	where user_id = v_user_id
	and group_id = v_group_id;      

	IF v_count > 0 THEN
	RETURN ''t'';
	END IF;

	RETURN ''f'';
END;
' language 'plpgsql';



-- Extra added stuff for Postgres
-- by Ben
create function bboard_mod_pol_number(varchar) returns integer
as '
declare
	mod_pol alias for $1;
begin
	if mod_pol=''''
	then return 1;
	end if;
	
	if mod_pol= ''featured''
	then return 2;
	end if;

	if mod_pol= ''moderated''
	then return 3;
	end if;

	if mod_pol= ''unmoderated''
	then return 4;
	end if;

	if mod_pol= ''private''
	then return 5;
	end if;

	return 6;
end;
' language 'plpgsql';