-- In the true Arsdigita spirit,AAAAAAA
-- the ticket tracker contains parts
-- of code by Eve, Jin, Ben, and Tracy!

-- but this latest release is maintained by Henry Minsky (hqm@arsdigita.com)
-- (from ACS 2.0, July 1999)

-- massively changed and ported to PGSQL by Ben Adida (ben@openforce.net)

-- Create an admin group for BITS
select administration_group_add ('BITS Administration', 'bits', 'bits', '', 'f', '/team'); 

-- create a group type for Teams
insert into user_group_types 
(group_type, pretty_name, pretty_plural, approval_policy, group_module_administration)
values
('team', 'Team', 'Teams', 'closed', 'full');


-- first thing is first(we need to create ticket_issues before we reference it)  -js

create sequence ticket_project_id_sequence;

create table ticket_projects (
        project_id      integer not null primary key,
        parent_project_id       integer references ticket_projects,
        title           varchar(100),
        -- person who request the project and will be the owner
        customer_id     integer not null references users,
        start_date      datetime,
        end_date        datetime,
        -- person who gets defaultly assigned to new tickets in the project     
        default_assignee integer references users
);

create index ticket_proj_parent_indx on ticket_projects(parent_project_id);

create view open_ticket_projects as select * from ticket_projects where end_date is null;

create table ticket_priorities (
        priority                integer not null primary key,
        name                    varchar(20)
);

insert into ticket_priorities values (3, 'low');
insert into ticket_priorities values (2, 'medium');
insert into ticket_priorities values (1, 'high');

create sequence ticket_issue_id_sequence;

create table ticket_issues (
        msg_id                  integer not null primary key,
        project_id              integer not null references ticket_projects,
        user_id                 integer references users,
        group_id                integer references user_groups,
        root_project_id         integer references ticket_projects,
        release_id              integer,
        fixed_release_id        integer,
        posting_time            datetime not null,
        modification_time       datetime,
        ticket_type             varchar(100),
        one_line                varchar(700),
        message                 varchar(6000),
        indexed_stuff           varchar(6000),
        close_date              datetime,
        closed_by               integer references users,
        fix_date                datetime,
        fixed_by                integer references users,
        -- When it is important for it to be finished.
        deadline                datetime,
        -- Status: open, development, fixed waiting approval, closed 
        status                  varchar(100),
        priority                integer references ticket_priorities,
        severity                varchar(100),
        -- A NEW FIELD for LEVEL of PRIVACY of issues
        privacy                 integer,
        -- who was responsible for creating this message
        source                  varchar(100),
        -- user name who last modified 
        last_modified_by        integer references users,
        -- When was the last "nag" notification sent 
        last_notification       datetime,
        -- Ticket author's contact info
        contact_name            varchar(200),
        contact_email           varchar(200),
        contact_info1           varchar(700),
        contact_info2           varchar(700),
        -- product-specific fields
        -- data1                        varchar(700),
        -- data2                        varchar(700),
        -- data3                        varchar(700),
        -- data4                        varchar(700),
        -- data5                        varchar(700),
        -- is this ticket visible to customers
        public_p                char(1) default 't' check(public_p in ('t','f')),
        -- if notify_p is 't', member of that project will receive notification email
        notify_p                char(1) default 't' check(notify_p in ('t','f'))
);


create view ticket_defects as select * from ticket_issues where ticket_type='Defect';
create view ticket_enhancements as select * from ticket_issues where ticket_type='Enhancement Request';
create view ticket_issues_issues as select * from ticket_issues where ticket_type='Issue';


create sequence ticket_field_id_sequence start 1;

create table ticket_projects_fields (
       project_id		    integer not null,
       field_id			    integer not null,
       primary key(project_id, field_id),
       field_name		    varchar(200) not null,
       field_pretty_name	    varchar(200) not null,
       view_in_list		    char(1) check (view_in_list in ('t','f')),
       -- potential vals, separated by vertical bars
       field_vals		    varchar(4000),
       field_type		    varchar(100) not null
);

create unique index ticket_project_field_name_indx on ticket_projects_fields(project_id, field_name);

-- We do things this way because creating a new table
-- for every project seems pretty bad, and not that useful
create table ticket_projects_field_vals (
       project_id			integer not null,
       field_id				integer not null,
       foreign key (project_id, field_id) references ticket_projects_fields,
       issue_id				  integer not null references ticket_issues,
       primary key (project_id, field_id, issue_id),
       field_val			  varchar(200)
);

create index ticket_proj_fval_issue_id on ticket_projects_field_vals(issue_id);

create table ticket_project_teams (
       project_id	integer not null primary key references ticket_projects,
       team_id		integer not null references user_groups
);

--
-- releases
create table ticket_project_releases (
       project_id	integer not null,
       release_id	integer not null,
       release		varchar(100) not null,
       creation_date	datetime,
       release_date	datetime,
       build_file	varchar(200),
       primary key (project_id, release_id)
);

create sequence release_id_sequence;

-- a trigger to make sure that we always have releases at the top level
create function trig_ticket_release_edit() returns opaque
as '
DECLARE
BEGIN
	NEW.project_id:= project_root_project_id(NEW.project_id);
	return NEW;
END;
' language 'plpgsql';

create trigger ticket_release_edit
before insert or update on ticket_project_releases
for each row
execute procedure trig_ticket_release_edit();

-- A table to assign people to projects

create sequence ticket_assignment_id_sequence;

create table ticket_assignments (
	project_id	integer references ticket_projects,
	user_id	 	integer references users,
	rate		integer, -- e.g. 125
	purpose		varchar(4000), -- e.g. "HTML, Java, etc..."
	-- we add this active flag in case someone gets taken off the
	-- project.
	active_p	char(1) check (active_p in ('t','f')),
	admin_p		char(1) default 'f' check (admin_p in ('t','f')),
	primary key (project_id, user_id)
);


create table ticket_issue_user_interest_map (
       msg_id			    integer not null references ticket_issues,
       user_id			    integer not null references users,
       primary key (msg_id, user_id),
       start_date		datetime
); 
       
       

-- quick postgres hack
create function parent_project_p(integer,integer)
returns char as '
DECLARE
BEGIN
	if $1 = $2
	THEN return ''t'';
	else return ''f'';
	END IF;
END;
' language 'plpgsql';

-- pl/sql for parent projects
-- create function parent_project_p(integer, integer)
-- returns char as '
-- DECLARE
-- 	v_parent_project_id alias for $1;
-- 	v_project_id alias for $2;
-- 	count_check integer;
-- BEGIN
-- 	select count(*) into count_check from ticket_projects where project_id=v_project_id connect by parent_project_id= prior project_id start with project_id=v_parent_project_id;

-- 	if (count_check > 0)
-- 	then return ''t'';
-- 	else return ''f'';
-- 	end if;
-- END;
-- ' language 'plpgsql';
	
-- pl/sql hack
create function project_root_project_id(integer)
returns integer as '
DECLARE
	p_id alias for $1;
BEGIN
	return p_id;
END;
' language 'plpgsql';

-- pl/sql to get root project_id
-- create function project_root_project_id(integer)
-- returns integer as '
-- DECLARE
-- 	v_project_id alias for $1;
-- 	return_project_id integer;
-- BEGIN
-- 	select project_id into return_project_id from ticket_projects where parent_project_id is NULL connect by project_id=prior parent_project_id start with project_id=v_project_id;
-- 	return return_project_id;
-- END;
-- ' language 'plpgsql';

-- pl/sql to get the full name
-- quick postgres hack
create function project_full_name(integer)
returns varchar as '
DECLARE
	v_project_id alias for $1;
	v_name varchar(200);
BEGIN
	select title into v_name from ticket_projects where project_id= v_project_id;
	return(v_name);
END;
' language 'plpgsql';

-- create function project_full_name(integer)
-- returns char as '
-- DECLARE
-- 	v_project_id alias for $1;
-- 	full_name varchar(4000);
--       CURSOR select_parents is 
-- 	     select title from ticket_projects 
-- 	     connect by project_id= prior parent_project_id 
-- 	     start with project_id= v_project_id;
-- BEGIN
-- 	full_name:= NULL;
-- 	FOR one_record IN select_parents LOOP
-- 	    IF full_name IS NOT NULL
-- 	    THEN full_name:= '' // '' || full_name;
-- 	    ELSE full_name:= '''';
-- 	    END IF;

-- 	    full_name:= one_record.title || full_name;
-- 	END LOOP;

-- 	return(full_name);
-- END;
-- ' language 'plpgsql';
	

-- pl/sql for permissions
create function ticket_user_can_see_project_p(integer, integer)
returns char as '
DECLARE
	v_user_id alias for $1;
	v_project_id alias for $2;
	count_check integer;
BEGIN
	if ad_admin_group_member_p(''bits'','''', v_user_id) = ''t''
	then return ''t'';
	end if;

	select count(*) into count_check from user_group_map where user_id= v_user_id and group_id in (select team_id from ticket_project_teams where parent_project_p(project_id,v_project_id)=''t'');

	IF count_check > 0
	THEN return(''t'');
	ELSE return(''f'');
	END IF;
END;
' language 'plpgsql';
	
create function ticket_user_can_edit_project_p(integer, integer)
returns char as '
DECLARE
	v_user_id alias for $1;
	v_project_id alias for $2;
	count_check integer;
BEGIN
	if ad_admin_group_member_p(''bits'','''', v_user_id) = ''t''
	then return ''t'';
	end if;

	select count(*) into count_check from user_group_map where user_id= v_user_id and (role=''internal'' or role=''administrator'') and group_id in (select team_id from ticket_project_teams where parent_project_p(project_id,v_project_id)=''t'');

	IF count_check > 0
	THEN return(''t'');
	ELSE return(''f'');
	END IF;
END;
' language 'plpgsql';

-- drop function ticket_user_can_close_issue_p(integer, integer);

create function ticket_user_can_close_issue_p(integer, integer)
returns char as '
DECLARE
	v_user_id alias for $1;
	v_msg_id alias for $2;
	msg_closeable record;
BEGIN
	if ad_admin_group_member_p(''bits'','''', v_user_id) = ''t''
	then return ''t'';
	end if;

	select into msg_closeable
               count(ti.msg_id) as chk,
               max(ti.project_id) as pid 
          from ticket_issues ti
         where ti.user_id = v_user_id 
           and ti.msg_id  = v_msg_id
           and 0 < (select count(*) 
                      from user_group_map 
                     where user_id= v_user_id 
                       and (role=''internal'' or role=''administrator'') 
                       and group_id = (select tm.team_id 
                                from ticket_project_teams tm
                               where parent_project_p(tm.project_id,ti.project_id)=''t''));

	IF msg_closeable.chk > 0
	THEN return(''t'');
	ELSE return(''f'');
	END IF;
END;
' language 'plpgsql';	

-------------- From The Community System ---------------
-- table state, country_codes, users
--------------------------------------------------------


-- to make sure we keep the root_project_id synchronized
create function trig_ticket_issue_modify() returns opaque
AS '
DECLARE
BEGIN
	NEW.root_project_id:= project_root_project_id(NEW.project_id);

	IF NEW.status != ''fixed waiting approval'' AND NEW.status != ''closed''
	THEN
		NEW.fix_date:= NULL;
		NEW.fixed_by:= NULL;
	END IF;

	IF TG_OP = ''INSERT''
	THEN RETURN NEW;
	END IF;

	IF NEW.status = ''fixed waiting approval'' AND OLD.status != NEW.status
	THEN
		NEW.fix_date:= sysdate();
		NEW.fixed_by:= NEW.last_modified_by;
	END IF;

	RETURN NEW;
END;
' language 'plpgsql';

create trigger ticket_issue_modify
before insert or update on ticket_issues
for each row
execute procedure trig_ticket_issue_modify();


-- Reference the release table
-- alter table ticket_issues add 
-- (constraint R_issue_release foreign key (root_project_id, release_id)
-- references ticket_project_releases);

-- alter table ticket_issues add 
-- (constraint R_issue_fixed_release foreign key (root_project_id, fixed_release_id)
-- references ticket_project_releases);

create function trig_ticket_modification_time() returns opaque
as '
DECLARE
BEGIN
	NEW.modification_time:= sysdate();
	return NEW;
END;
' language 'plpgsql';

create trigger ticket_modification_time
before insert or update on ticket_issues
for each row
execute procedure trig_ticket_modification_time();

-- the ticket_changes table can reference ticket_issues
-- but only in Oracle 8.1.5 or newer; Oracle 8.0.5 gets 
-- bent out of shape with a mutating trigger from
-- ticket_activity_logger

--- keep track of changes to a ticket
create table ticket_changes (
  msg_id    integer not null, -- references ticket_issues
  who 	    varchar(256),
  what      varchar(256),
  old_value varchar(256),
  new_value varchar(256),
  modification_date  datetime
);

create index ticket_changes_by_msg_id on ticket_changes(msg_id);

-- track changes to tickets
create function trig_ticket_activity_logger() returns opaque
as '
DECLARE
BEGIN
  if (OLD.project_id != NEW.project_id) then
   insert into ticket_changes (msg_id, who, what, old_value, new_value, modification_date)
   values
   (NEW.msg_id, NEW.last_modified_by, ''Project ID'', OLD.project_id, NEW.project_id, sysdate());
  end if;

  if (OLD.ticket_type != NEW.ticket_type) then
   insert into ticket_changes (msg_id, who, what, old_value, new_value, modification_date)
   values
   (NEW.msg_id, NEW.last_modified_by, ''Ticket Type'', OLD.ticket_type, NEW.ticket_type, sysdate());
  end if;

  if (OLD.one_line != NEW.one_line) then
   insert into ticket_changes (msg_id, who,what, old_value, new_value, modification_date) 
   values
   (NEW.msg_id, NEW.last_modified_by, ''Synopsis'', OLD.one_line, NEW.one_line, sysdate());
  end if;

  if (OLD.deadline != NEW.deadline) then
   insert into ticket_changes (msg_id, who,what, old_value, new_value, modification_date) 
   values
   (NEW.msg_id, NEW.last_modified_by, ''Deadline'', OLD.deadline, NEW.deadline, sysdate());
  end if;

  if (OLD.status != NEW.status) then
   insert into ticket_changes (msg_id, who,what, old_value, new_value, modification_date) 
   values
   (NEW.msg_id, NEW.last_modified_by, ''Status'', OLD.status, NEW.status, sysdate());
  end if;

  if (OLD.priority != NEW.priority) then
   insert into ticket_changes (msg_id, who,what, old_value, new_value, modification_date) 
   values
   (NEW.msg_id, NEW.last_modified_by, ''Priority'', OLD.priority, NEW.priority, sysdate());
  end if;

  if (OLD.severity != NEW.severity) then
   insert into ticket_changes (msg_id, who,what, old_value, new_value, modification_date) 
   values
   (NEW.msg_id, NEW.last_modified_by, ''Severity'', OLD.severity, NEW.severity, sysdate());
  end if;

  RETURN NEW;
end;
' language 'plpgsql';

create trigger ticket_activity_logger
before update on ticket_issues
for each row
execute procedure trig_ticket_activity_logger();


-- attachments
create table ticket_issues_attachments (
       msg_id			       integer not null references ticket_issues,
       attachment_name		       varchar(200),
       filename			       varchar(200)
);

create index ticket_attachments_by_msg_id on ticket_issues_attachments(msg_id);


-- For notification purposes
insert into user_notification_classes (notification_class_id, notification_class, item_url, class_url, item_pretty_name, item_varname)
values (nextval('notification_class_id_sequence'), 'Ticket Tracker', '/new-ticket/issue-view.tcl', '/new-ticket', 'Issue ID', 'msg_id');

create function ticket_notification_class_id() returns integer
as '
DECLARE
BEGIN
	return notification_class_id from user_notification_classes where notification_class=''Ticket Tracker'';
END;
' language 'plpgsql';

create function trig_ticket_change_insert() returns opaque
as '
DECLARE
	v_check integer;
BEGIN
	v_check:= user_add_notification(ticket_notification_class_id(), NEW.msg_id, NEW.who || '' changed '' || NEW.what || '' from '' || NEW.old_value || '' to '' || NEW.new_value);

	RETURN NEW;
END;
' language 'plpgsql';

create trigger ticket_change_insert
before insert on ticket_changes
for each row
execute procedure trig_ticket_change_insert();
	

--- a table to assign users to  issues
--- the selection list for this will be the
--- ticket_assignments table constratained by the appropriate project


create table ticket_issue_assignments (
	msg_id		integer not NULL references ticket_issues,
	user_id		integer not null references users,
	purpose		varchar(4000), -- e.g. "HTML, Java, etc..."
	-- we add this active flag in case someone gets taken off the
	-- issue.
	active_p	char(1) check (active_p in ('t','f')),
	unique (msg_id, user_id)
);

create function ticket_n_assigned(integer)
returns integer as '
DECLARE
	v_msg_id alias for $1;
	v_count integer;
BEGIN
	select count(*) into v_count from ticket_issue_assignments where msg_id= v_msg_id;
	return v_count;
END;
' language 'plpgsql';
	
-- cross reference table mapping issues to other issues
create table ticket_xrefs (
       from_ticket integer not null references ticket_issues(msg_id),
       to_ticket integer not null references ticket_issues(msg_id),
       primary key (from_ticket,to_ticket)
);


create sequence ticket_response_id_sequence;

create table ticket_issue_responses (
        response_id             integer not null primary key,
        response_to             integer not null references ticket_issues,
        user_id                 integer references users,
        posting_time            datetime not null,
	public_p		char(1) default('t') check(public_p in ('t','f')),
        message                 varchar(6000)
);


-- update the ticket's modification timestamp
create function trig_ticket_response_mod_time() returns opaque
as '
DECLARE
BEGIN
	update ticket_issues set modification_time=sysdate() 
	where msg_id= NEW.response_to;
	return NEW;
END;
' language 'plpgsql';

create trigger ticket_response_mod_time
before insert or update on ticket_issue_responses
for each row
execute procedure trig_ticket_response_mod_time();


create table ticket_issue_notifications (
        msg_id                  integer not null references ticket_issues,
        user_id                 integer not null references users,
        primary key (msg_id, user_id)
);


-- called by /tcl/email-queue.tcl 
-- and /ticket/issue-response-2.tcl 
create function ticket_update_for_response(integer) returns integer
AS '
DECLARE
	v_response_id alias for $1;
	v_response_row ticket_issue_responses%ROWTYPE;
BEGIN
 select ticket_issue_responses.* into v_response_row
   from ticket_issue_responses
   where response_id = v_response_id;

 if v_response_row.message is not null then 
   update ticket_issues 
   set
   indexed_stuff= indexed_stuff || v_response_row.message
   where msg_id= v_response_row.response_to;

   return user_add_notification(ticket_notification_class_id(), v_response_row.response_to, ''New Response to issue:\n'' || v_response_row.message);
 end if;

 return 0;
END;
' language 'plpgsql';


create function ticket_one_if_high_priority(integer, varchar)
returns integer as '
DECLARE
	priority alias for $1;
	status alias for $2;
BEGIN
	IF ((priority = 1) AND (status <> ''closed'') AND (status <> ''deferred'')) THEN
	return 1;
	ELSE
	return 0;
	END IF;
END;
' language 'plpgsql';

create function ticket_one_if_blocker(varchar,varchar)
returns integer as '
DECLARE
	severity alias for $1;
	status alias for $2;
BEGIN
  IF ((severity = ''blocker'') AND (status <> ''closed'') AND (status <> ''deferred'')) THEN
    return 1;
  ELSE 
    return 0;
  END IF;
END;
' language 'plpgsql';


create function ticket_assignees (integer)
returns varchar as '
DECLARE
	v_msg_id alias for $1;
	v_one_user users%ROWTYPE;
	v_assignees varchar(1000);
BEGIN
	v_assignees:= '''';
	FOR v_one_user IN select * from users where user_id in (select user_id from ticket_issue_assignments where msg_id= v_msg_id) LOOP
		IF v_assignees != ''''
		THEN v_assignees:= v_assignees || '', '';
		END IF;
		v_assignees := v_assignees || v_one_user.first_names || '' '' || substr(v_one_user.last_name, 1,1);
	END LOOP;

	return v_assignees;
END;
' language 'plpgsql';


create function ticket_release_name(integer)
returns varchar as '
DECLARE
	v_release_id alias for $1;
	v_release varchar(100);
BEGIN
	select release into v_release from ticket_project_releases
	where release_id= v_release_id;

	return v_release;
END;
' language 'plpgsql';


-- for custom field fetching
create function ticket_fetch_custom_field (integer, integer, varchar)
returns char
as '
DECLARE
	v_msg_id alias for $1;
	v_project_id alias for $2;
	v_field alias for $3;
	v_field_id integer;
BEGIN
	select field_id into v_field_id from ticket_projects_fields where project_id= v_project_id and field_name= v_field;

	if v_field_id is null then return null; end if;

	return field_val from ticket_projects_field_vals where field_id = v_field_id and project_id=v_project_id and issue_id= v_msg_id;
END;
' language 'plpgsql';