--
-- packages/resource-list/sql/postgresql/resource-list-functions-create.sql
--
-- @author jade@bread.com, ncarroll@ee.usyd.edu.au
-- @creation-date 2003-05-15
-- @cvs-id $Id: resource-list-functions-create.sql,v 1.2 2004/02/20 01:11:52 jader Exp $
--
--

-- When we created the acs object type above, we specified a
-- 'name_method'.  This is the name of a function that will return the
-- name of the object.  This is a convention ensuring that all objects
-- can be identified.  Now we have to build that function.  In this case,
-- we'll return a field called title as the name. 

-- Create a new root folder

select define_function_args('rl_resource__new_root_folder', 'package_id');

create or replace function rl_resource__new_root_folder (integer)
returns integer as '
declare
	p_package_id		alias for $1;

	v_folder_id		cr_folders.folder_id%TYPE;
	v_folder_name		cr_items.name%TYPE;
begin

        raise notice ''in new root folder'';

	-- Set the folder name
	v_folder_name := rl_resource__new_name (p_package_id);

	v_folder_id := content_folder__new (
	    v_folder_name,                              -- name
	    ''Resources'',                              -- label
	    ''Resources Repository'',                   -- description
	    0                                           -- parent_id
	);

	-- Register the standard content types
        PERFORM content_folder__register_content_type (
                v_folder_id,            -- folder_id
                ''rl_resource'',        -- content_type
                ''f''                   -- include_subtypes
	);

        -- there is no facility in the API for adding in the package_id,
        -- so we have to do it ourselves

        update cr_folders 
        set package_id = p_package_id 
        where folder_id = v_folder_id;

	-- TODO: Handle Permissions here for this folder.

	return v_folder_id;
end;' language 'plpgsql';


-- Returns the root folder corresponding to a particular package instance.
-- Creates a new root folder if one does not exist for the specified package
-- instance.

select define_function_args('rl_resource__get_root_folder', 'package_id,create_if_not_present_p');

create or replace function rl_resource__get_root_folder (integer, boolean)
returns integer as '
declare
	p_package_id		alias for $1;
        p_create_if_not_present_p alias for $2;

	v_folder_id		cr_folders.folder_id%TYPE;
	v_count			integer;
begin

        -- raise notice ''in get root folder p_create_if_not_present_p = %'',p_create_if_not_present_p;

	select count(*) into v_count
	from cr_folders
	where package_id = p_package_id;

        -- raise notice ''count is % for package_id %'', v_count, p_package_id;

	if v_count > 1 then
                raise exception ''More than one repository for this application instance'';
	elsif v_count = 1 then
                select folder_id into v_folder_id
                from cr_folders 
                where package_id = p_package_id;
	else
                if p_create_if_not_present_p = true then
        		-- Must be a new instance.  Create a new root folder.
                        raise notice ''creating a new root repository folder'';
	        	v_folder_id := rl_resource__new_root_folder(p_package_id);
                else
                        -- raise notice ''setting to null'';
                        v_folder_id := null;
                end if;
        end if;

        -- raise notice ''v_folder_id is %'', v_folder_id;

	return v_folder_id;

end; ' language 'plpgsql';


-- Create a resource item.

create or replace function rl_resource__new_item (
	integer,        -- resource_item_id
	varchar,        -- resource_name
	varchar,        -- description
	timestamptz,    -- creation_date
	integer,        -- creation_user
	varchar,        -- creation_ip
	integer         -- package_id
) returns integer 
as '
declare
	p_resource_item_id                      alias for $1;
	p_resource_name				alias for $2;
	p_description				alias for $3;
	p_creation_date				alias for $4;
	p_creation_user				alias for $5;
	p_creation_ip				alias for $6;
	p_package_id				alias for $7;

	v_item_id		cr_items.item_id%TYPE;
	v_revision_id		cr_revisions.revision_id%TYPE;
	v_id			cr_items.item_id%TYPE;
	v_resource_item_id      integer;
	v_resource_number       cr_items.item_id%TYPE;
	v_parent_id		cr_items.parent_id%TYPE;
begin
	select acs_object_id_seq.nextval into v_id from dual;

        v_parent_id := rl_resource__get_root_folder (p_package_id, ''t'');

        -- raise notice ''v_parent_id (%) p_parent_id (%)'', v_parent_id, p_parent_id;

	v_item_id := content_item__new (
		v_id::varchar,	        -- name
		v_parent_id,		-- parent_id
		v_id,			-- item_id
		null,			-- locale
		now(),			-- creation_date
		p_creation_user,	-- creation_user
		v_parent_id,		-- context_id
		p_creation_ip,		-- creation_ip
		''content_item'',	-- item_subtype
		''rl_resource'',        -- content_type
		p_resource_name,        -- title
		p_description,		-- description
		''text/plain'',		-- mime_type
		null,			-- nls_language
		null			-- data
	);

	v_revision_id := content_revision__new (
		p_resource_name,        -- title
		p_description,          -- description
		now(),			-- publish_date
		''text/plain'',		-- mime_type
		NULL,			-- nls_language
		NULL,			-- data
		v_item_id,		-- item_id
		NULL,			-- revision_id
		now(),			-- creation_date
		p_creation_user,	-- creation_user
		p_creation_ip		-- creation_ip
	);

	PERFORM content_item__set_live_revision (v_revision_id);

        -- select nextval(''rl_resources_number_seq.nextval'') into v_resource_number from dual;

	insert into rl_resources (
		resource_item_id, resource_number, approved_p)
	values (
		v_item_id, v_id, ''f'');

	insert into rl_resources_revisions (
		resource_revision_id) 
	values (
                v_revision_id);

	PERFORM acs_permission__grant_permission(
		v_revision_id,
		p_creation_user,
		''admin''
	);

	return v_revision_id;
end;' language 'plpgsql';


-- The delete function deletes a record and all related overhead. 

select define_function_args('rl_resource__delete_item', 'resource_id');

create or replace function rl_resource__delete_item (integer)
returns integer as '
declare
	p_resource_item_id                      alias for $1;
begin
        raise NOTICE ''Deleting rl_resource...'';

	delete from acs_permissions
		where object_id = p_resource_item_id;

        delete from rl_resource_category_map where resource_item_id = p_resource_item_id;
        
        delete from rl_resources_revisions where resource_revision_id in (select revision_id from cr_revisions where item_id = p_resource_item_id);

	delete from rl_resources
		where resource_item_id = p_resource_item_id;

        PERFORM content_item__delete(p_resource_item_id);
        return 0;
end;' language 'plpgsql';


select define_function_args('rl_resource__new_revision', 'resource_item_id, resource_name, description, creation_date, creation_user, creation_ip, package_id');

create or replace function rl_resource__new_revision (
	integer,        -- resource_item_id
	varchar,        -- resource_name
	varchar,        -- description
	timestamptz,    -- creation_date
	integer,        -- creation_user
	varchar,        -- creation_ip
        integer         -- package_id
) returns integer 
as '
declare
	p_resource_item_id                      alias for $1;
	p_resource_name	                        alias for $2;
	p_description				alias for $3;
	p_creation_date				alias for $4;
	p_creation_user				alias for $5;
	p_creation_ip				alias for $6;
	p_package_id				alias for $7;

	v_revision_id		cr_revisions.revision_id%TYPE;
begin

        -- the item_id is the resource_id

	v_revision_id := content_revision__new (
		p_resource_name,        -- title
		p_description,		-- description
		now(),			-- publish_date
		''text/plain'',		-- mime_type
		NULL,			-- nls_language
		NULL,			-- data
		p_resource_item_id,     -- item_id
		NULL,			-- revision_id
		now(),			-- creation_date
		p_creation_user,	-- creation_user
		p_creation_ip		-- creation_ip
	);

	PERFORM content_item__set_live_revision (v_revision_id);

	insert into rl_resources_revisions (
		resource_revision_id) 
	values (
                v_revision_id);

	PERFORM acs_permission__grant_permission(
		v_revision_id,
		p_creation_user,
		''admin''
	);

	return v_revision_id;
end;' language 'plpgsql';



-- Creates and returns a unique name.

select define_function_args('rl_resource__new_name', 'package_id');

create or replace function rl_resource__new_name (integer)
returns text as '
declare
	p_package_id		alias for $1;

	v_name			cr_items.name%TYPE;
	v_package_key		apm_packages.package_key%TYPE;
	v_id			integer;
begin
	select package_key into v_package_key from apm_packages
	    where package_id = p_package_id;

	select acs_object_id_seq.nextval into v_id from dual;

	-- Set the name
	select v_package_key || ''_'' || 
	    to_char(current_timestamp, ''YYYYMMDD'') || ''_'' ||
	    v_id into v_name;

	return v_name;
end;' language 'plpgsql';

----------------------------------
-- Resources
----------------------------------

-- When we created the acs object type above, we specified a
-- 'name_method'.  This is the name of a function that will return the
-- name of the object.  This is a convention ensuring that all objects
-- can be identified.  Now we have to build that function.  In this case,
-- we'll return a field called title as the name. 

select define_function_args('rl_resource__name', 'resource_item_id');

create or replace function rl_resource__name (integer)
returns varchar as '
declare
    p_rl_resource_item_id    alias for $1;
begin
    return p_rl_resource_item_id;
end;
' language 'plpgsql';