--
-- packages/project-manager/sql/postgresql/project-manager-functions-create.sql
--
-- @author jade@bread.com, ncarroll@ee.usyd.edu.au
-- @creation-date 2003-05-15
-- @cvs-id $Id: project-manager-functions-create.sql,v 1.2 2003/06/15 13:01:52 nickc 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. 
select define_function_args('pm_project__name', 'project_id');
create or replace function pm_project__name (integer)
returns varchar as '
declare
    p_pm_project_id      alias for $1;
    v_pm_project_name    pm_projects.project_name%TYPE;
begin
        select project_name into v_pm_project_name
                from pm_projects
                where project_id = p_pm_project_id;
    return v_pm_project_name;
end;
' language 'plpgsql';


-- Create a new root folder
select define_function_args('pm_project__new_root_folder', 'package_id');
create function pm_project__new_root_folder (integer)
returns integer as '
declare
	p_package_id		alias for $1;

	v_folder_id		pm_root_folders.folder_id%TYPE;
	v_folder_name		cr_items.name%TYPE;
begin
	-- Set the folder name
	v_folder_name := pm_project__new_unique_name (p_package_id);

	v_folder_id := content_folder__new (
	    v_folder_name,				-- name
	    ''Projects'',				-- label
	    ''Project Repository'',			-- description
	    null					-- parent_id
	);

	insert into pm_root_folders
	(package_id, folder_id)
	values
	(p_package_id, v_folder_id);

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

	PERFORM content_folder__register_content_type(
	    v_folder_id,            -- folder_id
	    ''content_folder'',     -- content_type
	    ''f''                   -- include_subtypes (default)
	);

	-- 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('pm_project__get_root_folder', 'package_id');
create function pm_project__get_root_folder (integer)
returns integer as '
declare
	p_package_id		alias for $1;
	v_folder_id		pm_root_folders.folder_id%TYPE;
	v_count			integer;
begin
	select count(*) into v_count
	from pm_root_folders
	where package_id = p_package_id;

	if v_count > 0 then
		select folder_id into v_folder_id
		from pm_root_folders
		where package_id = p_package_id;
	else
		-- Must be a new instance.  Create a new root folder.
		v_folder_id := pm_project__new_root_folder(p_package_id);
	end if;

	return v_folder_id;

end; ' language 'plpgsql';


-- Create a new project folder.
-- Top level project folders should be created under the PROJECTS
-- container folder.
-- Refer to file-storage-package-create.sql
select define_function_args('pm_project__new_project_folder', 'folder_name, description, parent_id, creation_user, creation_ip, package_id');
create function pm_project__new_project_folder (varchar, varchar, integer, 
                                        	integer, varchar, integer)
returns integer as '
declare
	p_folder_name		alias for $1;
	p_description		alias for $2;
	p_parent_id		alias for $3;
	p_creation_user		alias for $4;
	p_creation_ip		alias for $5;
	p_package_id		alias for $6;

	v_folder_id		cr_folders.folder_id%TYPE;
	v_parent_id		cr_items.parent_id%TYPE;
	v_name			cr_items.name%TYPE;
begin
	-- If p_parent_id is null then treat the new folder as
	-- a new root project folder.  Therefore set parent_id to
	-- the PROJECTS folder where all root project folders should
	-- reside.
	if p_parent_id is null
	then
		v_parent_id := pm_project__get_root_folder(p_package_id);
	else
		v_parent_id := p_parent_id;
	end if;

	-- Set the folder name
	v_name := pm_project__new_unique_name (p_package_id);

	-- Create a new folder
	v_folder_id := content_folder__new (
	    v_name,		-- name
	    p_folder_name,	-- label
	    p_description,	-- description
	    v_parent_id,	-- parent_id
	    null,		-- context_id
	    null,		-- folder_id
	    now(),		-- creation_date
	    p_creation_user,	-- creation_user
	    p_creation_ip	-- creation_ip
	);

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

	PERFORM content_folder__register_content_type(
	    v_folder_id,            -- folder_id
	    ''content_folder'',     -- content_type
	    ''t''                   -- include_subtypes (default)
	);

	-- TODO: Handle Permissions here for this folder.

	return v_folder_id;

end;' language 'plpgsql';


-- Create a project item.
-- A project item should be placed within a folder.  Therefore a new project
-- item is associated with creating a new project folder that will contain
-- the project item.  A new root project folder will be created if parent_id
-- is null.  Otherwise a project folder will be created as a sub-folder
-- of an existing project folder.
select define_function_args('pm_project__new_project_item', 'project_id, project_name, project_code, parent_id, goal, description, deadline_scheduling, planned_start_date, planned_end_date, actual_start_date, actual_end_date, ongoing_p, creation_date, creation_user, creation_ip, package_id');
create function pm_project__new_project_item (integer, varchar, varchar,
	integer, varchar, varchar, char(1), timestamptz, timestamptz,
	timestamptz, timestamptz, char(1), timestamptz, integer, 
	varchar, integer)
returns integer as '
declare
	p_project_id				alias for $1;
	p_project_name				alias for $2;
	p_project_code				alias for $3;
	p_parent_id				alias for $4;
	p_goal					alias for $5;
	p_description				alias for $6;
	p_deadline_scheduling			alias for $7;
	p_planned_start_date			alias for $8;
	p_planned_end_date			alias for $9;
	p_actual_start_date			alias for $10;
	p_actual_end_date			alias for $11;
	p_ongoing_p				alias for $12;
	p_creation_date				alias for $13;
	p_creation_user				alias for $14;
	p_creation_ip				alias for $15;
	p_package_id				alias for $16;

	v_item_id		cr_items.item_id%TYPE;
	v_revision_id		cr_revisions.revision_id%TYPE;
	v_id			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;

	-- Need to create a new project folder for the project item.
	-- If p_parent_id is null then a new root project folder will
	-- be created.  Otherwise a new folder will be created under
	-- the folder with the folder_id equal to p_parent_id.
	v_parent_id := pm_project__new_project_folder (
		p_project_name,		-- folder_name
		p_description,		-- description
		p_parent_id,		-- parent_id
		p_creation_user,	-- creation_user
		p_creation_ip,		-- creation_ip
		p_package_id		-- package_id
	);

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

	v_revision_id := content_revision__new (
		p_project_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);

	insert into pm_projects (
		project_id, project_folder_id, project_name, project_code, 
		goal, deadline_scheduling, planned_start_date, 
		planned_end_date, actual_start_date, actual_end_date, 
		ongoing_p) 
	values (
		v_revision_id, v_parent_id, p_project_name, p_project_code, 
		p_goal, p_deadline_scheduling, p_planned_start_date, 
		p_planned_end_date, p_actual_start_date, 
		p_actual_end_date, p_ongoing_p);

	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('pm_project___delete_project_item', 'project_id');
create or replace function pm_project__delete_project_item (integer)
returns integer as '
declare
	p_project_id				alias for $1;
begin
	delete from acs_permissions
		where object_id = p_project_id;

	delete from pm_projects
		where project_id = p_project_id;

        raise NOTICE ''Deleting pm_project...'';

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


-- Creates and returns a unique name.
select define_function_args('pm_project__new_unique_name', 'package_id');
create function pm_project__new_unique_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';