--
-- packages/general-comments/sql/general-comments-create.sql
--
-- @author Phong Nguyen (phong@arsdigita.com)
-- @creation-date 2000-10-12
--
-- @cvs-id $Id: general-comments-create.sql,v 1.2 2003/06/19 18:04:03 daveb Exp $
--
-- General comments: Commenting facility for any object in ACS 4.0
-- 

-- create a table to extend cr_items
create table general_comments (
    comment_id  integer  constraint general_comments_comment_id_fk
                references cr_revision (revision_id) on delete cascade 
                constraint general_comments_pk
                primary key,
    object_id   integer constraint general_comments_object_id_fk
	        references acs_objects (object_id) on delete cascade,
    package_id  integer constraing general_comments_package_id_fk
		references apm_packages (package_id) on delete cascade
);


comment on table general_comments is '
    Extends the cr_revisions table to hold item level data.
'; 
comment on column general_comments.object_id is '
    The id of the object to associate message with
';
-- unused catergory feature removed. Should be new categories package. DaveB

-- create an index on foreign key constraint
create index general_comments_object_id_idx on general_comments (object_id);

-- define and grant privileges
declare
    registered_users acs_objects.object_id%TYPE;
    default_context  acs_objects.object_id%TYPE;
begin

    -- retreive object ids for magic objects
    registered_users := acs.magic_object_id('registered_users');
    default_context  := acs.magic_object_id('default_context');

    -- create privileges
    acs_privilege.create_privilege('general_comments_create');

    -- associte privileges to global privileges
    acs_privilege.add_child('create','general_comments_create');
  
-- This is questionable. If we allow seperate gc instances per subsite this
-- will not work. DaveB
  
    -- allow registered users to create comments
--    acs_permission.grant_permission (
--       object_id  => default_context,
--       grantee_id => registered_users,
--       privilege  => 'general_comments_create'
--    );

end;
/
show errors

-- NOTE: this is only temporary until we figure out how
--       packages will register child types to an acs-message
begin

    content_type.register_child_type (
        parent_type => 'acs_message_revision',
        child_type  => 'content_revision'
    );
    content_type.register_child_type (
        parent_type => 'acs_message_revision',
        child_type  => 'image'
    );
    content_type.register_child_type (
        parent_type => 'acs_message_revision',
        child_type  => 'content_extlink'
    );

end;
/
show errors


-- create general comment type as subtype of cr_revision
begin

select content_type.create_type (
        content_type => 'general_comment',
	supertype => 'content_revision',
	pretty_name => 'General Comment',
	pretty_plural => 'General Comments',
	table_name => 'general_comments',
	id_column => 'comment_id',
	name_method => 'content_revision.revision_name'
);

end;
/
show errors