--
-- packages/ticket-tracker/sql/ttracker-workflow-create.sql
--
-- 
-- @author Phong Nguyen (phong@arsdigita.com)
-- @author Tony Tseng (tony@arsidigta.com)
-- 
-- @creation-date 2000-11-15
--
-- @cvs-id $Id: ttracker-workflow-create.sql,v 1.2 2001/06/12 06:25:49 vinodk Exp $
--

-- This table will hold one row for each case using this workflow. 
create table wf_ttracker_cases (
    case_id    integer 
               constraint wf_ttkr_cases_pk
               primary key
			   constraint wf_ticket_cases_case_fk
			   references wf_cases
);


--declare
--    v_workflow_key      wf_workflows.workflow_key%TYPE;
--begin
    select workflow__create_workflow(
		'ttracker_wf',					-- workflow_key  
		'Ticket Tracker Process',		-- pretty_name   
		'Ticket Tracker Process',		-- pretty_plural 
		'Workflow for processing a ticket in the ticket-tracker', -- description   
		'wf_ttracker_cases',			-- table_name    
		'case_id'						-- id_column
    );

    -- Creating places    
    select workflow__add_place (
        'ttracker_wf',					-- workflow_key 
        'start',						-- place_key    
        'Needs to be resolved',			-- place_name   
        1								-- sort_order   
    );    

    select workflow__add_place (
        'ttracker_wf',					-- workflow_key 
        'to_be_clarified',				-- place_key    
        'Needs to be clarified',		-- place_name   
        2								-- sort_order   
    );

    select workflow__add_place (
        'ttracker_wf',					-- workflow_key 
        'to_be_verified',				-- place_key    
        'Needs to be verified',			-- place_name   
        3								-- sort_order   
    );

    select workflow__add_place (
        'ttracker_wf',					-- workflow_key 
        'end',							-- place_key    
        'Closed',						-- place_name   
        4								-- sort_order   
    );

    -- Creating transitions
    select workflow__add_transition (
        'ttracker_wf',					-- workflow_key    
        'resolve',						-- transition_key  
        'Resolve ticket',				-- transition_name 
        1,								-- sort_order      
        'user'							-- trigger_type    
    );

	select workflow__add_transition (
        'ttracker_wf',					-- workflow_key    
        'clarify',						-- transition_key  
        'Clarify ticket description',	-- transition_name 
        2,								-- sort_order      
        'user'							-- trigger_type    
    );

    select workflow__add_transition (
        'ttracker_wf',					-- workflow_key    
        'verify',						-- transition_key  
        'Verify result',				-- transition_name 
        3,								-- sort_order      
        'user'							-- trigger_type    
    );

--end;
--/
--show errors

--begin
    -- creating arcs
    -- resolve in
    -- in
    select workflow__add_arc (
        'ttracker_wf',					-- workflow_key   
        'resolve',						-- transition_key 
        'start',						-- place_key      
        'in',							-- direction      
		null,							-- guard_callback
		null,							-- guard_custom_arg
		null							-- guard_description
    );

    -- out
    select workflow__add_arc (
        'ttracker_wf',					-- workflow_key      
        'resolve',						-- transition_key    
        'to_be_clarified',				-- place_key         
        'out',							-- direction         
        'wf_callback__guard_attribute_true',					 -- guard_callback    
        'need_clarification',			-- guard_custom_arg  
        'Description needs clarification'  -- guard_description 
    );

    select workflow__add_arc (
        'ttracker_wf',					-- workflow_key      
        'resolve',						-- transition_key    
        'to_be_verified',				-- place_key         
        'out',							-- direction         
        '#',							-- guard_callback    
		null,							-- guard_custom_arg
        'Description clear; issue resolved'					 -- guard_description 
    );
    
    -- verify
    -- in
    select workflow__add_arc (
        'ttracker_wf',					-- workflow_key   
        'verify',						-- transition_key 
        'to_be_verified',				-- place_key      
        'in',							-- direction      
		null,							-- guard_callback
		null,							-- guard_custom_arg
		null							-- guard_description
    );

    -- out
    select workflow__add_arc (
        'ttracker_wf',					-- workflow_key      
        'verify',						-- transition_key    
        'end',							-- place_key         
        'out',							-- direction         
        'wf_callback__guard_attribute_true',		 -- guard_callback    
        'verified',						-- guard_custom_arg  
        'Result okay'					-- guard_description 
    );

    select workflow__add_arc (
        'ttracker_wf',					-- workflow_key      
        'verify',						-- transition_key    
        'start',						-- place_key         
        'out',							-- direction         
        '#',							-- guard_callback    
        null,							-- guard_custom_arg  
        'Result not okay'				-- guard_description 
    );

    -- clarify
    -- in
    select workflow__add_arc (
        'ttracker_wf',					-- workflow_key   
        'clarify',						-- transition_key 
        'to_be_clarified',				-- place_key      
        'in',							-- direction      
		null,							-- guard_callback
		null,							-- guard_custom_arg
		null							-- guard_description
    );

    -- out
    select workflow__add_arc (
        'ttracker_wf',					-- workflow_key   
        'clarify',						-- transition_key 
        'start',						-- place_key      
        'out',							-- direction      
		null,							-- guard_callback
		null,							-- guard_custom_arg
		null							-- guard_description
    );

--end;
--/
--show errors

create function inline_0 ()
returns integer as '
declare
	v_attribute_id		acs_attributes.attribute_id%TYPE;
begin
	select workflow__create_attribute(
		''ttracker_wf'',				-- workflow_key   
		''need_clarification'',			-- attribute_name 
		''boolean'',					-- datatype       
		''Description needs clarification'',	  -- pretty_name
		null,							-- pretty_plural    
		null,							-- table_name
		null,							-- column_name
		''f'',							-- default_value  
		1,								-- min_n_values
		1,								-- max_n_values
		null,							-- sort_order
		''generic'',					-- storage
		''none''						-- wf_datatype
    ) into v_attribute_id;

    insert into wf_transition_attribute_map
        (workflow_key, transition_key, attribute_id, sort_order) 
    values
        (''ttracker_wf'', ''resolve'', v_attribute_id, 1);

    return 0;
end;' language 'plpgsql';

select inline_0 ();

drop function inline_0 ();


create function inline_1 ()
returns integer as '
declare
	v_attribute_id		acs_attributes.attribute_id%TYPE;
begin
    select workflow__create_attribute(
		''ttracker_wf'',				-- workflow_key   
		''verified'',					-- attribute_name 
		''boolean'',					-- datatype       
		''Result is okay'',				-- pretty_name    
		null,							-- pretty_plural    
		null,							-- table_name
		null,							-- column_name
		''t'',							-- default_value  
		1,								-- min_n_values
		1,								-- max_n_values
		null,							-- sort_order
		''generic'',					-- storage
		''none''						-- wf_datatype
    ) into v_attribute_id;

    insert into wf_transition_attribute_map
        (workflow_key, transition_key, attribute_id, sort_order) 
    values
        (''ttracker_wf'', ''verify'', v_attribute_id, 1);
	
    return 0;
end;' language 'plpgsql';

select inline_1 ();

drop function inline_1 ();

--declare
--    v_attribute_id acs_attributes.attribute_id%TYPE;
--begin
--    v_attribute_id := workflow.create_attribute(
--	workflow_key   => 'ttracker_wf',
--	attribute_name => 'need_clarification',
--	datatype       => 'boolean',
--	pretty_name    => 'Description needs clarification',
--	default_value  => 'f'
--    );
--
--    insert into wf_transition_attribute_map
--        (workflow_key, transition_key, attribute_id, sort_order) 
--    values
--        ('ttracker_wf', 'resolve', v_attribute_id, 1);
--
--    v_attribute_id := workflow.create_attribute(
--	workflow_key   => 'ttracker_wf',
--	attribute_name => 'verified',
--	datatype       => 'boolean',
--	pretty_name    => 'Result is okay',
--	default_value  => 't'
--    );
--
--    insert into wf_transition_attribute_map
--        (workflow_key, transition_key, attribute_id, sort_order) 
--    values
--        ('ttracker_wf', 'verify', v_attribute_id, 1);
--end;
--/
--show errors

insert into wf_context_transition_info (
     context_key, 
     workflow_key, 
     transition_key, 
     assignment_callback, 
     fire_callback, 
     notification_callback,
     notification_custom_arg,
     unassigned_callback,
     access_privilege
) values (
     'default', 
     'ttracker_wf', 
     'resolve', 
     'ttracker_callback__assign_to_assignee', 
     'ttracker_callback__resolve_fire', 
     'ttracker_callback__notification',
     'Resolve Ticket',
     'ttracker_callback__notify_admin',
     'admin'
);

insert into wf_context_transition_info (
     context_key, 
     workflow_key, 
     transition_key, 
     assignment_callback, 
     fire_callback, 
     notification_callback,
     notification_custom_arg,
     access_privilege
) values (
     'default', 
     'ttracker_wf', 
     'clarify', 
     'ttracker_callback__assign_to_submitter', 
     'ttracker_callback__clarify_fire', 
     'ttracker_callback__notification',
     'Clarify Description',
     'admin'
);

insert into wf_context_transition_info ( 
     context_key, 
     workflow_key, 
     transition_key, 
     assignment_callback, 
     notification_callback,
     notification_custom_arg,
     access_privilege
) values (
     'default', 
     'ttracker_wf', 
     'verify', 
     'ttracker_callback__assign_to_submitter', 
     'ttracker_callback__notification',
     'Verify Result',
     'admin'
);


insert into wf_context_task_panels (
    context_key, 
    workflow_key, 
    transition_key, 
    sort_key, 
    header, 
    template_url
) values (
    'default', 
    'ttracker_wf', 
    'resolve', 
    1, 
    'Ticket Info', 
    '/packages/ticket-tracker/wf-templates/ticket-info'
);

insert into wf_context_task_panels (
    context_key, 
    workflow_key, 
    transition_key, 
    sort_key, 
    header, 
    template_url
) values (
    'default', 
    'ttracker_wf', 
    'resolve', 
    2, 
    'What to do', 
    '/packages/ticket-tracker/wf-templates/resolve'
);

insert into wf_context_task_panels (
    context_key, 
    workflow_key, 
    transition_key, 
    sort_key, 
    header, 
    template_url
) values (
    'default', 
    'ttracker_wf', 
    'clarify', 
    1, 
    'Ticket Info', 
    '/packages/ticket-tracker/wf-templates/ticket-info'
);

insert into wf_context_task_panels (
    context_key, 
    workflow_key, 
    transition_key, 
    sort_key, 
    header, 
    template_url
) values (
    'default', 
    'ttracker_wf', 
    'clarify', 
    2, 
    'What to do', 
    '/packages/ticket-tracker/wf-templates/clarify'
);

insert into wf_context_task_panels (
    context_key, 
    workflow_key, 
    transition_key, 
    sort_key, 
    header, 
    template_url
) values (
    'default', 
    'ttracker_wf', 
    'verify', 
    1, 
    'Ticket Info', 
    '/packages/ticket-tracker/wf-templates/ticket-info'
);

insert into wf_context_task_panels (
    context_key, 
    workflow_key, 
    transition_key, 
    sort_key, 
    header, 
    template_url
) values (
    'default', 
    'ttracker_wf', 
    'verify', 
    2, 
    'What to do', 
    '/packages/ticket-tracker/wf-templates/verify'
);
      
