--
-- 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.3 2003/05/17 12:33:30 jeffd Exp $
--
-- Ported to OpenACS - Vinod Kurup (vkurup@massmed.org)
--

----------------------------------
-- KNOWLEDGE LEVEL OBJECTS
----------------------------------

select acs_object_type__create_type (
	'ttracker_ticket',
	'Ticket Tracker Ticket',
	'Ticket Tracker Tickets',
	'acs_message',
	'ttracker_tickets',
	'ticket_id',
	'ttracker_ticket',
	'f',
	null,
	'ttracker_ticket.name'
);    

select acs_attribute__create_attribute (
	'ttracker_ticket',
	'category_id',
	'integer',
	'Category ID',
	'Category IDs',
	null,
	null,
	null,
	1,
	1,
	null,
	'type_specific',
	'f'
);

select acs_attribute__create_attribute (
	'ttracker_ticket',
	'subject',
	'string',
	'Subject',
	'Subjects',
	null,
	null,
	null,
	1,
	1,
	null,
	'type_specific',
	'f'
);

select acs_attribute__create_attribute (
	'ttracker_ticket',
	'severity',
	'integer',
	'Severity',
	'Severities',
	null,
	null,
	null,
	1,
	1,
	null,
	'type_specific',
	'f'
);

select acs_attribute__create_attribute (
	'ttracker_ticket',
	'priority',
	'integer',
	'Priority',
	'Priorities',
	null,
	null,
	null,
	1,
	1,
	null,
	'type_specific',
	'f'
);

----------------------------------
-- 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    integer
					 constraint ttkr_tkt_creation_user_fk
                     references persons(person_id)
                     constraint ttkr_tkt_creation_user_nn
                     not null,
    creation_date    timestamptz default current_timestamp
                     constraint ttkr_tkt_date_nn
                     not null,                 
    subject          varchar(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.
';