-- 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,
	-- 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')),
        -- denormalized, computed values
        -- these are computed but stored in projects table for efficient
        -- access.
        estimated_completion_date       timestamptz,
        earlier_completion_date         timestamptz,
        latest_completion_date          timestamptz,
        actual_hours_completed          numeric,
        estimated_hours_total           numeric
);


-- 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_project_roles (
        role_id                 integer
                                constraint pm_project_role_id_pk
                                primary key,
        one_line                varchar(100)
                                constraint pm_project_role_one_line_uq
                                unique,
        description             varchar(2000),
        sort_order              integer
);


comment on table pm_project_roles is '
  Roles represent the way in which a party participates in a project. For example,
  they could be a manager, or client, or participant.. The sort order determines 
  what order it is displayed in.
';

insert into pm_project_roles (role_id, one_line, description, sort_order) values ('1','Participant','Team members who are responsible for the completion of the project','10');
insert into pm_project_roles (role_id, one_line, description, sort_order) values ('2','Manager','Manages the team to complete the project on time and on budget.','20');


create sequence pm_project_role_seq start 3;


create table pm_project_role_map (
        project_id              integer
                                constraint pm_project_role_map_project_fk
                                references pm_projects
                                on delete cascade,
        role_id                 integer
                                constraint pm_project_role_map_role_fk
                                references pm_project_roles,
        party_id                integer
                                constraint pm_project_role_map_user_id_fk 
                                references parties(party_id)
                                on delete cascade
);

comment on table pm_project_role_map is '
  Maps who is a part of what project, and in what capacity
';


create table pm_project_default_roles (
        role_id                 integer
                                constraint pm_proj_default_role_fk
                                references pm_project_roles
                                on delete cascade,
        party_id                 integer
                                constraint pm_proj_default_role_party_fk 
                                references parties(party_id)
                                on delete cascade
);

comment on table pm_project_role_map is '
  Specifies what role a person is a part of by default, for projects
';

-- 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 sequence pm_tasks_number_seq;

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
        end_date                        timestamptz,
        -- keep track of completion status
        percent_complete                numeric,
        -- PERT charts require minimum and maximum estimates
        -- if we are not using these, then we set the min and max
        -- estimates to the same value.
        estimated_hours_work_min        numeric,
        estimated_hours_work_max        numeric,
        -- this should be computed by checking with logger? The actual
        -- data should be in logger, logged by who did it, when etc..
        -- or we can create a separate table to keep track of task hours
        -- and make sure its data model is similar to logger? 
        actual_hours_worked             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
    '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
);

select content_type__create_attribute (
    'pm_task', -- content_type
    'estimated_hours_work_min', -- attribute_name
    'number',           -- datatype
    'Estimated minimum hours', -- pretty_name
    'Estimated minimum hours', -- pretty_plural
    null, -- sort_order
    null, -- default value
    'numeric' -- column_spec
);

select content_type__create_attribute (
    'pm_task', -- content_type
    'estimated_hours_work_max', -- attribute_name
    'number',           -- datatype
    'Estimated maximum hours', -- pretty_name
    'Estimated maximum hours', -- pretty_plural
    null, -- sort_order
    null, -- default value
    'numeric' -- column_spec
);

select content_type__create_attribute (
    'pm_task', -- content_type
    'actual_hours_worked', -- attribute_name
    'number',           -- datatype
    'Actual hours worked', -- pretty_name
    'Actual hours worked', -- 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 (
        short_name                      varchar(100)
                                        constraint pm_task_const_sn_pk
                                        primary key,
        description                     varchar(1000)
);

insert into pm_task_constraint_types (short_name, description) values ('start_before_start','Must start before this task can start');
insert into pm_task_constraint_types (short_name, description) values ('start_before_finish','Must start before this task can finish');
insert into pm_task_constraint_types (short_name, description) values ('finish_before_start','Must finish before this task can start');
insert into pm_task_constraint_types (short_name, description) values ('finish_before_finish','Must finish before this task can finish');


create sequence pm_task_constraint_seq;

create table pm_task_constraints (
        constraint_id                   integer
                                        constraint pm_task_const_id_pk
                                        primary key,
        task_id                         integer
                                        constraint pm_tasks_const_task_id_fk
                                        references pm_tasks
                                        on delete cascade,
        parent_task_id                  integer
                                        constraint pm_tasks_const_parent_id_fk
                                        references pm_tasks
                                        on delete cascade,
        const_type                       varchar
                                        constraint pm_tasks_const_type
                                        references pm_task_constraint_types
);


-- assignments


create table pm_task_roles (
        role_id                 integer
                                constraint pm_task_role_id_pk
                                primary key,
        one_line                varchar(100)
                                constraint pm_task_role_one_line_uq
                                unique,
        description             varchar(2000),
        is_observer_p           char(1) default 'f'
                                constraint pm_task_role_ck
                                check (is_observer_p in ('t','f')),
        sort_order              integer
);

comment on table pm_task_roles is '
  Roles represent the way in which a role participates in a task. For example,
  they could be an assignee, or a watcher. They can be divided up the same way 
  the organization is broken up, by sales and R and D for example. These terms
  can be made up to fit the conditions of the local company. Defaults are set
  up by another table, pm_task_default_role 
  The is_observer_p specifies
  whether they are directly responsible for the task. People not directly
  responsible will not get email notifications reminding them that tasks are
  overdue, for example.
';

insert into pm_task_roles (role_id, one_line, description, is_observer_p, sort_order) values ('1','Manager','This person oversees someone responsible for completion of the task','t','10');
insert into pm_task_roles (role_id, one_line, description, is_observer_p, sort_order) values ('2','Assignee','This person is directly responsible for completion of the task','f','20');
insert into pm_task_roles (role_id, one_line, description, is_observer_p, sort_order) values ('3','Watcher','This person is interested in developments of a ticket, but not directly responsible for it.','t','30');


create sequence pm_task_role_seq start 4;


create table pm_task_role_map (
        task_id                 integer
                                constraint pm_task_role_map_task_fk
                                references pm_tasks
                                on delete cascade,
        role_id                 integer
                                constraint pm_task_roles_fk
                                references pm_task_roles,
        party_id                integer
                                constraint pm_task_role_map_user_id_fk
                                references parties(party_id)
                                on delete cascade
);

comment on table pm_task_role_map is '
  Maps who is a part of what task, and in what capacity
';

create table pm_task_default_roles (
        role_id                 integer
                                constraint pm_task_default_role_fk
                                references pm_task_roles
                                on delete cascade,
        party_id                integer
                                constraint pm_task_default_role_party_fk 
                                references parties(party_id)
                                on delete cascade
);

comment on table pm_task_role_map is '
  Specifies what role a person is a part of by default, for tasks
';
