--
-- packages/ticket-tracker/sql/category-create.sql
--
-- 
-- @author Tony Tseng (tony@arsidigta.com)
-- @author Phong Nguyen (phong@arsdigita.com)
-- 
-- @creation-date 2000-11-16
--
-- @cvs-id $Id: category-create.sql,v 1.2 2001/07/09 16:18:59 vinodk Exp $
--
--
-- Ported to OpenACS - Vinod Kurup (vkurup@massmed.org)
--

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

select acs_object_type__create_type (
	'ttracker_category',			 -- object_type   
	'Ticket Tracker Category',		 -- pretty_name   
	'Ticket Tracker Categories',	 -- pretty_plural 
	'acs_object',					 -- supertype     
	'ttracker_categories',			 -- table_name    
	'category_id',					 -- id_column     
	'ttracker_category',			 -- package_name  
	'f',							 -- abstract_p
	null,							 -- type_extension_table
	'ttracker_category__name'		 -- name_method   
);  
  
select acs_attribute__create_attribute (
	'ttracker_category',		   -- object_type    
	'name',						   -- attribute_name 
	'string',					   -- datatype       
	'Name',						   -- pretty_name    
	'Names',					   -- pretty_plural  
	null,						   -- table_name
	null,						   -- column_name
	null,						   -- default_value
	1,							   -- min_n_values
	1,							   -- max_n_values
	null,						   -- sort_order
	'type_specific',			   -- storage
	'f'							   -- static_p
);

select acs_attribute__create_attribute (
	'ttracker_category',		   -- object_type    
	'description',				   -- attribute_name 
	'string',					   -- datatype       
	'Description',				   -- pretty_name    
	'Descriptions',				   -- pretty_plural  
	null,						   -- table_name
	null,						   -- column_name
	null,						   -- default_value
	1,							   -- min_n_values
	1,							   -- max_n_values
	null,						   -- sort_order
	'type_specific',			   -- storage
	'f'							   -- static_p
);

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

create table ttracker_categories (
    category_id			integer
						constraint ttracker_categories_pk
						primary key
						constraint ttracker_categories_fk	
						references acs_objects(object_id) on delete cascade,
    package_id			integer
						constraint ttkr_cat_package_id_fk
						references apm_packages (package_id) on delete cascade
						constraint ttkr_cat_package_id_nn
						not null,
    name				varchar(100) 
						constraint ttracker_categories_name_nn
						not null,
    description			text,
    default_assignee    integer
						constraint ttracker_categories_assgn_fk
                        references parties(party_id)      
);

-- to prevent ttracker_categories from getting locked 
-- when persons table is being updated
create index ttkr_cat_assignee_idx on ttracker_categories (default_assignee);

-- to speed up duplication check
create index ttkr_cat_upper_name_idx on ttracker_categories (upper(name));

-- people often query ttracker_categories to 
-- find out the categories within a package
create unique index ttkr_cat_pkg_name_cat_idx on ttracker_categories (package_id, name, category_id);

comment on table ttracker_categories is '
    This table holds information about ticket tracker categories
';
comment on column ttracker_categories.package_id is '
    The package instance that contains this category.
    Right now it is set as "on delete cascade"  
    so that we can delete the instance of the package right after we mount it 
';
comment on column ttracker_categories.name is '
    The name of the category
';
comment on column ttracker_categories.description is '
    The detailed description of the category
';
comment on column ttracker_categories.default_assignee is '
    The default assignee of this category. 
';