--
-- workflow__drop_workflow/1
--
create or replace function workflow__drop_workflow(
  character varying
) returns int4 as $$

declare
  drop_workflow__workflow_key      alias for $1;  
  v_table_name                     varchar;  
  v_num_rows                       integer;        
  attribute_rec                    record;
begin
        select table_name into v_table_name 
        from   acs_object_types
        where  object_type = drop_workflow__workflow_key;

        select case when count(*) = 0 then 0 else 1 end into v_num_rows 
        from   pg_class
        where  relname = lower(v_table_name);

        if v_num_rows > 0 then
            raise EXCEPTION '-20000: The table "%" must be dropped before calling workflow__drop_workflow.', v_table_name;
        end if;

        select case when count(*) = 0 then 0 else 1 end into v_num_rows 
        from   wf_cases
        where  workflow_key = drop_workflow__workflow_key;

        if v_num_rows > 0 then
            raise EXCEPTION '-20000: You must delete all cases of workflow "%" before dropping the workflow definition.', drop_workflow__workflow_key;
        end if;

        /* Delete all the auxillary stuff */
        delete from wf_context_task_panels where workflow_key = drop_workflow__workflow_key;
        delete from wf_context_assignments where workflow_key = drop_workflow__workflow_key;
        delete from wf_context_role_info where workflow_key = drop_workflow__workflow_key; 
        delete from wf_context_transition_info where workflow_key = drop_workflow__workflow_key; 
        delete from wf_context_workflow_info where workflow_key = drop_workflow__workflow_key;
        delete from wf_arcs where workflow_key = drop_workflow__workflow_key;
        delete from wf_places where workflow_key = drop_workflow__workflow_key;
	delete from wf_transition_role_assign_map where workflow_key = drop_workflow__workflow_key;
	delete from wf_transitions where workflow_key = drop_workflow__workflow_key;
        delete from wf_roles where workflow_key = drop_workflow__workflow_key;

        /* Drop all attributes */
        for attribute_rec in 
        select attribute_id, attribute_name 
        from acs_attributes 
        where object_type = drop_workflow__workflow_key
        LOOP
            /* there is no on delete cascade, so we have to manually 
             * delete all the values 
             */

            delete from acs_attribute_values where attribute_id = attribute_rec.attribute_id;

            PERFORM workflow__drop_attribute (
                drop_workflow__workflow_key,
                attribute_rec.attribute_name
            );
        end loop;

        /* Delete the workflow */
        delete from wf_workflows where workflow_key = drop_workflow__workflow_key;
        
        PERFORM acs_object_type__drop_type (
            drop_workflow__workflow_key,
            'f'
        );

        return 0; 
end;$$ language plpgsql;