--
-- packages/bboard/sql/bboard-create.sql
--
-- @author Anukul Kapoor <akk@arsdigita.com>
-- @author John Prevost <jmp@arsdigita.com>
-- @creation-date 2000-08-29
-- @cvs-id $Id: bboard-create.sql,v 1.6 2003/02/23 16:24:35 danw Exp $
--

-- separate parts so that if one fails, the rest happens

-- create the privileges

begin;

 select acs_privilege__create_privilege('bboard_create_forum',null,null);
 select acs_privilege__create_privilege('bboard_create_category',null,null);
 select acs_privilege__create_privilege('bboard_create_message',null,null);
 select acs_privilege__create_privilege('bboard_write_forum',null,null);
 select acs_privilege__create_privilege('bboard_write_category',null,null);
 select acs_privilege__create_privilege('bboard_write_message',null,null);
 select acs_privilege__create_privilege('bboard_read_forum',null,null);
 select acs_privilege__create_privilege('bboard_read_category',null,null);
 select acs_privilege__create_privilege('bboard_read_message',null,null);
 select acs_privilege__create_privilege('bboard_delete_forum',null,null);
 select acs_privilege__create_privilege('bboard_delete_category',null,null);
 select acs_privilege__create_privilege('bboard_delete_message',null,null);
 select acs_privilege__create_privilege('bboard_moderate_forum',null,null);
 select acs_privilege__add_child('create','bboard_create_forum');
 select acs_privilege__add_child('create','bboard_create_category');
 select acs_privilege__add_child('create','bboard_create_message');
 select acs_privilege__add_child('write','bboard_write_forum');
 select acs_privilege__add_child('write','bboard_write_category');
 select acs_privilege__add_child('write','bboard_write_message');
 select acs_privilege__add_child('read','bboard_read_forum');
 select acs_privilege__add_child('read','bboard_read_category');
 select acs_privilege__add_child('read','bboard_read_message');
 select acs_privilege__add_child('delete','bboard_delete_forum');
 select acs_privilege__add_child('delete','bboard_delete_category');
 select acs_privilege__add_child('delete','bboard_delete_message');
 select acs_privilege__add_child('admin','bboard_moderate_forum');
end;


create function inline_0 ()
returns integer as '
declare
    default_context integer;
    registered_users integer;
    the_public integer;
begin

    default_context := acs__magic_object_id(''default_context'');
    registered_users := acs__magic_object_id(''registered_users'');
    the_public := acs__magic_object_id(''the_public'');

    -- give registered users the power to post by default

    perform acs_permission__grant_permission (
        default_context,
        registered_users,
        ''bboard_create_message''
    );

    -- give the public the power to read by default

    perform acs_permission__grant_permission (
        default_context,
        the_public,
        ''bboard_read_message''
    );

    perform acs_permission__grant_permission (
        default_context,
        the_public,
        ''bboard_read_category''
    );

    perform acs_permission__grant_permission (
        default_context,
        the_public,
        ''bboard_read_forum''
    );


    return 0;
end;
' language 'plpgsql';

select inline_0 ();
drop function inline_0 ();


select acs_object_type__create_type (
    'bboard_forum',
    'BBoard Forum',
    'BBoard Forums',
    'acs_object',
    'BBOARD_FORUMS',
    'FORUM_ID',
    null,
    'f',
    null,
    'BBOARD_FORUM__NAME'
);

select acs_object_type__create_type (
    'bboard_category',
    'BBoard Category',
    'BBoard Categories',
    'acs_object',
    'BBOARD_CATEGORIES',
    'CATEGORY_ID',
    null,
    'f',
     null,
    'BBOARD_CATEGORY__NAME'
);

select acs_object_type__create_type (
	'bboard_message',
	'BBoard Message',
	'BBoard Messages',
	'acs_message',
	'BBOARD_MESSAGES',
	'MESSAGE_ID',
	null,
	'f',
	null,
	null
);

create table bboard_messages (
	message_id				 integer
							 constraint bboard_messages_msg_id_fk
							 references acs_messages(message_id)
							 on delete cascade
							 constraint bboard_messages_msg_id_pk
							 primary key
);

-- bboard forums
--
-- these act as primary containers for messages
-- a message's context_id will point to its forum

create table bboard_forums (
    forum_id integer
        constraint bboard_forums_forum_id_fk
            references acs_objects (object_id)
        constraint bboard_forums_pk
            primary key,    
    short_name varchar(400)
        constraint bboard_forums_short_name_nn
            not null,
    charter varchar(4000),
    moderated_p char(1)
        constraint bboard_forums_moderated_p_nn
            not null
        constraint bboard_forums_moderated_p_ck
            check (moderated_p in ('t','f')),
    bboard_id integer
        constraint bboard_forums_bboard_id_nn
            not null
        constraint bboard_forums_bboard_id_fk
            references apm_packages (package_id)
                on delete cascade
);

create index bboard_forums_bboard_id_idx
    on bboard_forums (bboard_id);

create table bboard_forum_message_map (
    forum_id integer
        constraint bboard_fmm_forum_id_fk
            references bboard_forums (forum_id)
                on delete cascade,
    message_id integer
        constraint bboard_fmm_message_id_fk
            references bboard_messages (message_id)
                on delete cascade,
    status varchar(20)
        constraint bboard_fmm_status_ck
            check (status in ('unmoderated', 'approved', 'rejected'))
        constraint bboard_fmm_status_nn
            not null,
    last_reply_date timestamp,
    num_replies integer,
    constraint bboard_forum_message_map_pk
        primary key (forum_id, message_id)
);

create index bboard_fmm_message_id_idx
    on bboard_forum_message_map (message_id);

create index bboard_fmm_status_idx
    on bboard_forum_message_map (status);

-- bboard categories
--
-- these are for intra-forum categorization
-- categories will be scoped to forums via their acs_object.context_id

create table bboard_categories (
    category_id integer
        constraint bboard_c_category_id_fk
            references acs_objects (object_id)
			on delete cascade
        constraint bboard_c_category_id_pk
            primary key,
    short_name varchar(400)
        constraint bboard_c_short_name_nn
            not null,
    forum_id integer
        constraint bboard_c_forum_id_fk
            references bboard_forums (forum_id)
                on delete cascade
        constraint bboard_c_forum_id_nn
            not null,
    description varchar(4000)
);

create index bboard_categories_forum_id_idx
    on bboard_categories (forum_id);

create table bboard_category_message_map (
    category_id integer
        constraint bboard_cmm_category_id_fk
            references bboard_categories (category_id)
                on delete cascade,
    message_id integer
        constraint bboard_cmm_message_id_fk
            references bboard_messages (message_id)
                on delete cascade,
    constraint bboard_category_message_map_pk
        primary key (category_id, message_id)
);

create index bboard_cmm_message_id_idx
    on bboard_category_message_map (message_id);

-- @@ bboard-views

-- Tables to track subscriptions

create table bboard_forum_subscribers (
    forum_id integer
        constraint bboard_fs_forum_id_fk
            references bboard_forums (forum_id)
                on delete cascade,
    subscriber_id integer
        constraint bboard_fs_subscriber_id_fk
            references parties (party_id)
                on delete cascade,
    constraint bboard_forum_subscribers_pk
        primary key (forum_id, subscriber_id)
);

create index bboard_fs_subscriber_id_idx
    on bboard_forum_subscribers (subscriber_id);

create table bboard_category_subscribers (
    category_id integer
        constraint bboard_cs_category_id_fk
            references bboard_categories (category_id)
                on delete cascade,
    subscriber_id integer
        constraint bboard_cs_subscriber_id_fk
            references parties (party_id)
                on delete cascade,
    constraint bboard_category_subscribers_pk
        primary key (category_id, subscriber_id)
);

create index bboard_cs_subscriber_id_idx
    on bboard_category_subscribers (subscriber_id);

create table bboard_thread_subscribers (
    thread_id integer
        constraint bboard_ts_thread_id_fk
            references bboard_messages (message_id)
                on delete cascade,
    subscriber_id integer
        constraint bboard_ts_subscriber_id_fk
            references parties (party_id)
                on delete cascade,
    constraint bboard_thread_subscribers_pk
        primary key (thread_id, subscriber_id)
);

create index bboard_ts_subscriber_id_idx
    on bboard_thread_subscribers (subscriber_id);

\i bboard-views.sql
\i bboard-packages.sql

insert into cr_mime_types (mime_type) 
 values ('text/plain; format=flowed');