-- TODO:
-- 
-- which items in this data model need to use the content repository?
-- need to add in workflow (for status among other things)
-- need to take into account acs-rels
-- add categories to projects

--
-- packages/test-project/sql/postgresql/test-project-table-create.sql
--
-- @author jader@bread.com, ncarroll@ee.usyd.edu.au and everyone else involved in this thread: http://openacs.org/forums/message-view?message_id=90742
-- @creation-date 2003-05-15
--

-- Project repository datamodel.
create table pm_projects (
	project_id		integer
				constraint project_manager_id_fk
				references cr_revisions on delete cascade
				constraint pm_projects_id_pk
				primary key,
	project_folder_id	integer
				constraint pm_project_folder_id_fk
				references cr_folders,
	project_name   		varchar(255) 
				constraint pm_projects_name_nn
				not null,
	-- a user-specified
	project_code		varchar(255),
	goal        		varchar(4000),
	-- is the deadline computed from the end date, or from 
	-- today?
	-- e = end, t = today
	deadline_scheduling	char(1) default 't'
				constraint pm_projects_dline_scheduling_ck
				check (deadline_scheduling in ('t','e')),
	planned_start_date  	timestamptz,
	planned_end_date	timestamptz,
	actual_start_date  	timestamptz,
	actual_end_date		timestamptz,
	ongoing_p		char(1) default 'f' 
				constraint pm_projects_ongoing_p_ck
				check (ongoing_p in ('t','f'))
);


-- create the content type
 select content_type__create_type (
   'pm_project',		-- content_type
   'content_revision',		-- supertype	
   'Project',			-- pretty_name 
   'Projects',			-- pretty_plural
   'pm_projects',		-- table_name 
   'project_id',		-- id_column 
   'pm_project__name'		-- name_method
 );


create table pm_root_folders (
    -- ID for this package instance
    package_id		integer
			constraint pm_root_folder_package_id_fk
			references apm_packages on delete cascade
			constraint pm_root_folder_package_id_pk
			primary key,
    -- ID of the root folder
    folder_id		integer
			constraint pm_root_folder_folder_id_fk
			references cr_folders
			constraint pm_root_folder_folder_id_un
			unique
);