-- /packages/ticket-tracker-lite/sql/ticket-tracker-lite-create.sql
--
-- @author dvr@arsdigita.com
-- @created 2001-02-18
--
---------------------------------------------------

begin

    acs_object_type.create_type (
       object_type => 'ticket_tracker_lite_area',
       pretty_name => 'Ticket Tracker Lite Feature Area',
       pretty_plural => 'Ticket Tracker Lite Feature Area',
       table_name => 'ttl_areas',
       id_column => 'area_id'
    );

    acs_object_type.create_type (
       object_type => 'ticket_tracker_lite_ticket',
       pretty_name => 'Ticket Tracker Lite Ticket',
       pretty_plural => 'Ticket Tracker Lite Tickets',
       table_name => 'ttl_tickets',
       id_column => 'ticket_id'
    );
end;
/

create table ttl_areas (
  area_id           integer 
                    constraint ttl_areas_object_fk
                    references acs_objects
                    constraint ttl_areas_pk
                    primary key,
  active_p          char(1) default 't'
                    constraint ttl_areas_active_ck
                    check(active_p in ('t','f')),
  name              varchar(200)
                    constraint ttl_areas_name_nn
                    not null
);

create table ttl_status (
  status            varchar(20) 
                    constraint ttl_status_pk
                    primary key,
  status_name       varchar(100) 
                    constraint ttl_status_name_nn
                    not null,
  sort_key          integer
);

insert into ttl_status (status, status_name, sort_key) values ('queued', 'Queued', 1);
insert into ttl_status (status, status_name, sort_key) values ('dev', 'Development', 2);
insert into ttl_status (status, status_name, sort_key) values ('staging', 'Staging', 3);
insert into ttl_status (status, status_name, sort_key) values ('completed', 'Completed', 4);
insert into ttl_status (status, status_name, sort_key) values ('revision', 'Needs revision', 5);
insert into ttl_status (status, status_name, sort_key) values ('deferred', 'Deferred', 6);

create table ttl_tickets (
  ticket_id             integer 
                        constraint ttl_tickets_fk
                        references acs_objects
                        constraint ttl_tickets_pk
                        primary key,
  active_p              char(1) default 't' 
                        constraint ttl_tickets_active_ck
                        check(active_p in ('t','f')),
  area_id               integer 
                        constraint ttl_tickets_area_fk
                        references ttl_areas,
  name                  varchar(200) 
                        constraint ttl_tickets_name_nn
                        not null,
  type                  varchar(20),
  status                varchar(20) 
                        constraint ttl_tickets_status_fk
                        references ttl_status,
  url                   varchar(400),
  deadline              date,
  message               clob,
  message_format        varchar(10) default 'formatted'
                        constraint ttl_tickets_message_format_ck
                        check(message_format in ('plain','formatted','html')),
  priority              integer default 2
);

create table ttl_comments (
  comment_id            integer 
                        constraint ttl_comments_pk
                        primary key,
  active_p              char(1) default 't'
                        constraint ttl_comments_active_ck
                        check(active_p in ('t','f')),
  ticket_id             integer
                        constraint ttl_comments_ticket_nn
                        not null
                        constraint ttl_comments_ticket_fk
                        references ttl_tickets,
  creation_user         integer 
                        constraint ttl_comments_user_fk
                        references users
                        constraint ttl_comments_user_nn
                        not null, 
  creation_date         date default sysdate,
  message               clob,
  message_format        varchar(10) default 'formatted'
                        constraint ttl_comments_message_format_ck
                        check(message_format in ('plain','formatted','html'))
);

create table ttl_assignments (
  ticket_id             integer 
                        constraint ttl_assignments_ticket_fk
                        references ttl_tickets,
  user_id               integer 
                        constraint ttl_assignments_user_fk
                        references users,
  constraint ttl_assignments_pk
  primary key(ticket_id, user_id),
  date_assigned         date default sysdate
);

create table ttl_area_assignments (
  area_id               integer 
                        constraint ttl_area_assignments_ticket_fk
                        references ttl_areas,
  user_id               integer 
                        constraint ttl_area_assignments_user_fk
                        references users,
  constraint ttl_area_assignments_pk
  primary key(area_id, user_id),
  date_assigned         date default sysdate
);

create table ttl_prefs (
  user_id               integer 
                        constraint ttl_prefs_user_ref
                        references users
                        constraint ttl_prefs_user_pk
                        primary key,
  sort                  varchar(100),
  mine_p                char(1) default 't'
                        constraint ttl_prefs_mine_ck
                        check(mine_p in ('t', 'f')),
  status                varchar(100),
  area_id               integer 
                        constraint ttl_pref_area_fk
                        references ttl_areas
);

@@ views-and-plsql