--
-- packages/ticket-tracker/sql/ticket-create.sql
--
-- 
-- @author Phong Nguyen (phong@arsdigita.com)
-- @author Tony Tseng (tony@arsidigta.com)
-- 
-- @creation-date 2000-11-15
--
-- @cvs-id $Id: ticket-create.sql,v 1.1.1.1 2002/07/09 17:35:13 rmello Exp $
--

----------------------------------
-- KNOWLEDGE LEVEL OBJECTS
----------------------------------
declare
    attr_id     acs_attributes.attribute_id%TYPE;
begin
    acs_object_type.create_type (
        object_type   => 'ttracker_ticket',
        pretty_name   => 'Ticket Tracker Ticket',
        pretty_plural => 'Ticket Tracker Tickets',
        supertype     => 'acs_message',
        table_name    => 'ttracker_tickets',
        id_column     => 'ticket_id',
        package_name  => 'ttracker_ticket',
        name_method   => 'ttracker_ticket.name'
    );    
    attr_id := acs_attribute.create_attribute (
        object_type    => 'ttracker_ticket',
        attribute_name => 'category_id',
        datatype       => 'integer',
        pretty_name    => 'Category ID',
        pretty_plural  => 'Category IDs'
    );
    attr_id := acs_attribute.create_attribute (
        object_type    => 'ttracker_ticket',
        attribute_name => 'subject',
        datatype       => 'string',
        pretty_name    => 'Subject',
        pretty_plural  => 'Subjects'
    );
    attr_id := acs_attribute.create_attribute (
        object_type    => 'ttracker_ticket',
        attribute_name => 'severity',
        datatype       => 'integer',
        pretty_name    => 'Severity',
        pretty_plural  => 'Severities'
    );
    attr_id := acs_attribute.create_attribute (
        object_type    => 'ttracker_ticket',
        attribute_name => 'priority',
        datatype       => 'integer',
        pretty_name    => 'Priority',
        pretty_plural  => 'Priorities'
    );
end;
/
show errors

----------------------------------
-- OPERATIONAL LEVEL
----------------------------------

create table ttracker_tickets (
    ticket_id        integer
                     constraint ttkr_tkt_pk
                     primary key
                     constraint ttkr_tkt_fk	
                     references acs_messages (message_id) on delete cascade,
    category_id      integer 
                     constraint ttkr_tkt_category_id_fk
                     references ttracker_categories (category_id) on delete cascade,
    creation_user    constraint ttkr_tkt_creation_user_fk
                     references persons(person_id)
                     constraint ttkr_tkt_creation_user_nn
                     not null,
    creation_date    date default sysdate
                     constraint ttkr_tkt_date_nn
                     not null,                 
    subject          varchar2(1000)
                     constraint ttkr_tkt_subject_nn
                     not null,
    severity         integer
                     constraint ttkr_tkt_severity_nn
                     not null,
    priority         integer
                     constraint ttkr_tkt_priority_nn
                     not null
);

-- to prevent ttracker_tickets from getting locked when ttracker_categories table is being updated
create index tt_category_id_idx on ttracker_tickets (category_id);

-- to prevent ttracker_tickets from getting locked when persons table is being updated
create index tt_creation_user_idx on ttracker_tickets (creation_user);

comment on table ttracker_tickets is '
    The contents of a ticket is stored as an acs-message. This table 
    extends the the acs_messages table to hold additional knowledge 
    level attributes for a ticket type.    
';
comment on column ttracker_tickets.creation_user is '
    The id of the user who submitted the ticket. It is stored 
    redundantly to avoid joining acs-objects.
';
comment on column ttracker_tickets.creation_date is '
    The creation date of the ticket. It is stored redundantly to 
    avoid joining against cr_items and cr_revisions.
';
comment on column ttracker_tickets.category_id is '
    The id of the category to associate this ticket to.
';
comment on column ttracker_tickets.subject is '
    The subject of the ticket. It is stored redundantly to avoid 
    joining against cr_items and cr_revisions. 
';
comment on column ttracker_tickets.severity is '
    The severity level of the ticket.
';
comment on column ttracker_tickets.priority is '
    The priority level of the ticket.
';