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

select acs_object_type__create_type (
  'ticket_tracker_lite_area',
  'Ticket Tracker Lite Feature Area',
  'Ticket Tracker Lite Feature Area',
  'acs_object',
  'ttl_areas',
  'area_id',
  null,
  'f',
  null,
  null
);

select acs_object_type__create_type (
  'ticket_tracker_lite_ticket',
  'Ticket Tracker Lite Ticket',
  'Ticket Tracker Lite Tickets',
  'acs_object',
  'ttl_tickets',
  'ticket_id',
  null,
  'f',
  null,
  null
);

create table ttl_areas (
  area_id           integer 
                    constraint ttl_areas_object_fk
                    references acs_objects
                    constraint ttl_areas_pk
                    primary key,
  active_p          boolean default 't',
  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              boolean default 't',
  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              timestamptz,
  message               text,
  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              boolean default 't',
  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         timestamptz default current_timestamp,
  message               text,
  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         timestamptz default current_timestamp
);

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         timestamptz default current_timestamp
);

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                boolean,
  status                varchar(100),
  area_id               integer 
                        constraint ttl_pref_area_fk
                        references ttl_areas
);

\i views-and-plsql.sql