-- 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_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
 );


-- we create two tables to store task information
-- the information that we keep revisions on is in the 
-- pm_task_revisions table, the rest is in pm_task


create table pm_tasks (
        task_id                         integer
                                        constraint pm_tasks_task_id_fk
                                        references cr_items 
                                        on delete cascade
                                        constraint pm_task_task_id_pk
                                        primary key,
        task_number                     integer
);


create table pm_tasks_revisions (
        task_revision_id                integer
                                        constraint pm_task_revs_id_fk
                                        references cr_revisions 
                                        on delete cascade
                                        constraint pm_task_revs_id_pk
                                        primary key,
        -- dates are optional, because it may be computed in reference
        -- to all other items, or simply not have a deadline
        start_date                      timestamptz,
        end_date                        timestamptz,
        -- keep track of completion status
        percent_complete                numeric
);

-- create the content type
select content_type__create_type (
   'pm_task',                   -- content_type
   'content_revision',		-- supertype	
   'Task',                      -- pretty_name 
   'Tasks',                     -- pretty_plural
   'pm_tasks_revisions',        -- table_name (should this be pm_task?)
   'task_revision_id',          -- id_column 
   'pm_task__name'              -- name_method
);

-- add in attributes

select content_type__create_attribute (
    'pm_task', -- content_type
    'start_date', -- attribute_name
    'date',     -- datatype (string, number, boolean, date, keyword, integer)
    'Start date', -- pretty_name
    'Start dates', -- pretty_plural
    null, -- sort_order
    null, -- default value
    'timestamptz' -- column_spec
);

select content_type__create_attribute (
    'pm_task', -- content_type
    'end_date', -- attribute_name
    'date',     -- datatype
    'End date', -- pretty_name
    'End dates', -- pretty_plural
    null, -- sort_order
    null, -- default value
    'timestamptz' -- column_spec
);

select content_type__create_attribute (
    'pm_task', -- content_type
    'percent_complete', -- attribute_name
    'number',           -- datatype
    'Percent complete', -- pretty_name
    'Percents complete', -- pretty_plural
    null, -- sort_order
    null, -- default value
    'numeric' -- column_spec
);



-- constraint types
-- such as:
-- cannot start until Task X finishes
-- cannot start until Task X begins
-- cannot finish until Task X finishes
-- cannot finish until Task X begins

create table pm_task_constraint_types (
        task_constraint_type_id         integer
                                        constraint pm_task_const_type_id_pk
                                        primary key,
        short_name                      varchar(100),
        description                     varchar(1000)
);

create table pm_task_constraints (
        task_id                         integer
                                        constraint pm_tasks_const_task_id_fk
                                        references pm_tasks
                                        on delete cascade
                                        constraint pm_task_const_task_id_pk
                                        primary key,
        parent_task_id                  integer
                                        constraint pm_tasks_const_parent_id_fk
                                        references pm_tasks
                                        on delete cascade,
        task_type                       integer
                                        constraint pm_tasks_const_type
                                        references pm_task_constraint_types
);