select  acs_object_type__create_type(
    'wf_ticket',		-- object_type => 'wf_ticket',
    'Ticket',			-- pretty_name => 'Ticket',
    'Tickets',			-- pretty_plural => 'Tickets',
    'acs_object',			-- supertype
    'wf_tickets',		-- table_name => 'wf_tickets',
    'ticket_id',		-- id_column => 'ticket_id',
    'wf_ticket',		-- package_name => 'wf_ticket',
    'f',			-- abstract_p
    null,			-- type_extension_table
    'wf_ticket.name'		-- name_method => 'wf_ticket.name'
  );

  -- XXX fill in all the attributes in later.

create table wf_tickets (
  ticket_id		integer
			constraint wf_tickets_pk
			primary key
			constraint wf_tickets_acs_object_fk
			references acs_objects on delete cascade,
  subject		varchar(400),
  description		varchar(4000),
  severity		integer,
  priority		integer
);

create function wf_ticket__new (
	integer,	-- ticket_id	in wf_tickets.ticket_id%TYPE default null,
        varchar,	-- subject	in wf_tickets.subject%TYPE,
        varchar,	-- description  in wf_tickets.description%TYPE,
	integer,	-- severity	in wf_tickets.severity%TYPE default 3,
	integer,	-- priority 	in wf_tickets.priority%TYPE default 3,
	timestamp,	-- creation_date   in acs_objects.creation_date%TYPE default sysdate,
	integer,	-- creation_user in acs_objects.creation_user%TYPE default null,
	varchar		-- creation_ip	in acs_objects.creation_ip%TYPE default null
    ) returns integer as '
    declare
	new__ticket_id		alias for $1;
        new__subject		alias for $2;
        new__description	alias for $3;
	new__severity		alias for $4;
	new__priority		alias for $5;
	new__creation_date	alias for $6;
	new__creation_user	alias for $7;
	new__creation_ip	alias for $8;
	v_creation_date		timestamp;
        v_ticket_id number;
	v_case_id number;
    begin
	if (new__creation_date is null) or (new__creation_date = 0) then
	    v_creation_date = now();
	else
	    v_creation_date = new__creation_date;
	end if;
	
        v_ticket_id := acs_object__new(
	    new__ticket_id,		-- object_id => ticket_id,	
	    ''wf_ticket'',		-- object_type => ''wf_ticket'',
	    new__creation_date, 	-- creation_date => new__creation_date,
	    new__creation_user,		-- creation_user => new__creation_user,
	    new__creation_ip,		-- creation_ip	=> new__creation_ip
	    null			-- context_id
        );

        insert into wf_tickets (ticket_id, subject, description, severity, priority)
	values (v_ticket_id, new__subject, new__description, new__severity, new__priority);

	v_case_id := workflow_case__new(
	    null,			-- case_id
	    ''ticket_wf'',		-- workflow_key => ''ticket_wf'',
	    null,			-- context_key
	    v_ticket_id,		-- object_id => v_ticket_id,
	    new__creation_date,		-- creation_date => new__creation_date,
	    new__creation_user,		-- creation_user => new__creation_user,
	    new__creation_ip		-- creation_ip => new__creation_ip
        );
 	
	workflow_case__start_case(
	    v_case_id,			-- case_id => v_case_id,
	    new__creation_user,		-- creation_user => new__creation_user,
	    new__creation_ip,		-- creation_ip => new__creation_ip
	    null			-- start_case__msg
        );
	
        return v_ticket_id;
    end;' language 'plpgsql';

create	function wf_ticket__delete(integer)
    returns integer as '
    declare
	delete__ticket_id 	alias for $1;
    begin
	delete from wf_tickets where ticket_id = delete__ticket_id;
        acs_object__delete(delete__ticket_id);
	return 0;
    end;' language 'plpgsql';

create    function wf_ticket__name (integer) 
    returns varchar as '
    declare
	name__ticket_id	alias 	for $1;
        v_subject wf_tickets.subject%TYPE;
    begin
	select subject into v_subject
	from   wf_tickets
	where  ticket_id = name__ticket_id;
	return v_subject;
    end;' language 'plpgsql';