-- TODO:
-- 
-- need to add in workflow (for status among other things)
-- add categories to projects

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

-- PROJECTS

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
	project_code		varchar(255),
	goal        		varchar(4000),
	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_finish_date           timestamptz,
        earliest_finish_date            timestamptz,
        latest_finish_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
 );



-- ROLES

create sequence pm_role_seq start 3;

create table pm_roles (
        role_id                 integer
                                constraint pm_role_id_pk
                                primary key,
        one_line                varchar(100)
                                constraint pm_role_one_line_uq
                                unique,
        description             varchar(2000),
        sort_order              integer,
        is_observer_p           char(1) default 'f'
                                constraint pm_role_is_observer_ck
                                check (is_observer_p in ('t','f'))
);


comment on table pm_roles is '
  Roles represent the way in which a party participates in a project
  or task. For example, they could be a manager, or client, or
  participant.. The sort order determines what order it is displayed
  in.  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_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_roles (role_id, one_line, description, sort_order) values ('2','Manager','Manages the team to complete the project or task on time and on budget.','20');


create table pm_default_roles (
        role_id                 integer
                                constraint pm_default_role_fk
                                references pm_roles
                                on delete cascade,
        party_id                integer
                                constraint pm_default_role_party_fk 
                                references parties(party_id)
                                on delete cascade,
        constraint pm_default_roles_uq
        unique (role_id, party_id)
);

comment on table pm_default_roles is '
  Specifies what role a person is a part of by default
';

-- PROJECT ASSIGNMENT

create table pm_project_assignment (
        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_roles,
        party_id                integer
                                constraint pm_project_role_map_user_id_fk 
                                references parties(party_id)
                                on delete cascade,
        constraint pm_project_assignment_uq
        unique (project_id, role_id, party_id)
);


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


-- TASKS

-- 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
                                        constraint pm_task_per_complete_gt_ck
                                        check(percent_complete >= 0)
                                        constraint pm_task_per_complete_lt_ck
                                        check(percent_complete <= 100),
        estimated_hours_work            numeric,
        -- PERT charts require minimum and maximum estimates
        -- these are optionally used
        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,
        -- network diagram stuff, computed
        earliest_start                  timestamptz,
        earliest_finish                 timestamptz,
        latest_start                    timestamptz,
        latest_finish                   timestamptz
);

-- 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', -- attribute_name
    'number',           -- datatype
    'Estimated hours work', -- pretty_name
    'Estimated hours work', -- 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
);

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

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

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

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


-- DEPENDENCIES

-- dependency 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_dependency_types (
        short_name                      varchar(100)
                                        constraint pm_task_const_sn_pk
                                        primary key,
        description                     varchar(1000)
);

insert into pm_task_dependency_types (short_name, description) values ('start_before_start','Start before this starts');
insert into pm_task_dependency_types (short_name, description) values ('start_before_finish','Start before this finishes');
insert into pm_task_dependency_types (short_name, description) values ('finish_before_start','Finish before this starts');
insert into pm_task_dependency_types (short_name, description) values ('finish_before_finish','Finish before this finishes');


create sequence pm_task_dependency_seq;

create table pm_task_dependency (
        dependency_id                   integer
                                        constraint pm_task_const_id_pk
                                        primary key,
        task_id                         integer
                                        constraint pm_task_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,
        dependency_type                 varchar
                                        constraint pm_tasks_const_type
                                        references pm_task_dependency_types,
        constraint pm_task_dependency_uq
        unique (task_id, parent_task_id)
);


-- WORKGROUPS

create sequence pm_workgroup_seq;

create table pm_workgroup (
        workgroup_id            integer
                                constraint pm_workgroup_id_pk
                                primary key,
        one_line                varchar(100)
                                constraint pm_workgroup_one_line_uq
                                unique,
        description             varchar(2000),
        sort_order              integer
);

create table pm_workgroup_parties (
        workgroup_id            integer
                                constraint pm_workgroup_parties_wg_id_fk
                                references pm_workgroup(workgroup_id)
                                on delete cascade,
        party_id                integer
                                constraint pm_workgroup_party_fk 
                                references parties(party_id)
                                on delete cascade,
        role_id                 integer
                                constraint pm_workgroup_role_id
                                references pm_roles,
        constraint pm_workgroup_parties_uq
        unique (workgroup_id, party_id, role_id)
);


-- TASK ASSIGNMENTS

create table pm_task_assignment (
        task_id                 integer
                                constraint pm_task_assignment_task_fk
                                references pm_tasks(task_id)
                                on delete cascade,
        role_id                 integer
                                constraint pm_task_assignment_role_fk
                                references pm_roles,
        party_id                integer
                                constraint pm_task_assignment_party_fk 
                                references parties(party_id)
                                on delete cascade,
        constraint pm_task_assignment_uq
        unique (task_id, role_id, party_id)
);


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