--
-- packages/general-comments/sql/general-comments-create.sql
--
-- @author Phong Nguyen (phong@arsdigita.com)
-- @author Pascal Scheffers (pascal@scheffers.net)
-- @creation-date 2000-10-12
--
-- @cvs-id $Id: general-comments-create.sql,v 1.4 2003/06/05 00:49:52 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_revisions (revision_id) on delete cascade 
                constraint general_comments_pk
                primary key,
    related_object_id   integer constraint general_comments_object_id_fk
	        references acs_objects (object_id) on delete cascade,
    package_id  integer constraint 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.related_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_rel_object_id_idx on general_comments (related_object_id);

create function inline_0 ()
returns integer as '
-- 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
    PERFORM acs_privilege__create_privilege(''general_comments_create'', null, null);

    -- associte privileges to global privileges
    PERFORM acs_privilege__add_child(''create'',''general_comments_create'');
    
    -- allow registered users to create comments
-- This is questionable. If we allow seperate gc instances per subsite this
-- will not work. DaveB

--    PERFORM acs_permission__grant_permission (
--        default_context,
--        registered_users,
--        ''general_comments_create''
--    );
    
    return 0;
end;' language 'plpgsql';

select inline_0 ();

drop function inline_0 ();

-- show errors


-- Do we need this or anything like it? DaveB

-- NOTE: this is only temporary until we figure out how
--       packages will register child types to an acs-message
-- create function inline_1 ()
-- returns integer as '
-- begin

--     PERFORM content_type__register_child_type (
--         /* parent_type => */ ''acs_message_revision'',
--         /* child_type  => */ ''content_revision'',
-- 	''generic'', 0, null
--     );
--     PERFORM content_type__register_child_type (
--         /* parent_type => */ ''acs_message_revision'',
--         /* child_type  => */ ''image'',
-- 	''generic'', 0, null
--     );
--     PERFORM content_type__register_child_type (
--         /* parent_type => */ ''acs_message_revision'',
--         /* child_type  => */ ''content_extlink'',
-- 	''generic'', 0, null
--     );
--     return 0;
-- end;' language 'plpgsql';

-- select inline_1 ();

-- drop function inline_1 ();

-- create general comment type as subtype or cr_revision

select content_type__create_type (
        'general_comment',        -- content_type
	'content_revision',         -- supertype
	'General Comment',       -- pretty_name
	'General Comments',      -- pretty_plural
	'general_comments',            -- table_name
	'comment_id',              -- id_column
	'content_revision__revision_name'  -- name_method
);


-- source plpgsql functions

\i general-comments-plsql-create.sql