-- 
-- packages/library/sql/library-create.sql
--
-- Credit for the ACS 3 version of this module goes to:
-- @author Branimir Dolicki (branimir@arsdigita.com)
-- @author Carsten Clasohm (carsten@arsdigita.com)
-- @author Sarah Arnold (zylonne@web.de)
--
-- The upgrade of this module to ACS 4 was done by
-- @author Dirk Gomez (dirk@arsdigita.com)
-- in June/July 2001.
--
-- @creation-date 22-June 2001
-- @cvs-id $Id: library-create.sql,v 1.1 2003/07/02 12:19:42 peterm Exp $

-- Changes, (at least those made by me) in reverse chronological order

-- (bran 006) 2001-09-18 Moved everything having to do with linking to a --
-- separate package - sn_links.  So basically changes 001, 002, 004 and 005 are
-- there and not here but in packages/sn-links/sql/oracle/sn-links.

-- (bran 005) 2001-09-12 As we are using the low-tech approach w/o objects we
-- need a sequence to generate link_id's.

-- (bran 004) 2001-09-10 There ain't gonna be any stinkin' objects here.  Links
-- are just rows in a table.  Period.  Removed acs_object_type.create_type.  Same
-- goes for acs_rel_type.create_role and friends

-- (bran 003) 2001-09-10 Removed question_id from sn_links because it is
-- library-specific.  We have a new table in library:
-- sn_question_link_map

-- (bran 002) 2001-09-10 Removed the sn_link_comments table and put that stuff
-- into sn_links.  Finally we admit that it doesn't make sense to have one comment
-- on two links!

-- (bran 001) 2001-09-10 Removed the link_type_id column from sn_link_types.
-- We are now refering to link types by link_type and not by link_type_id.

------------------------------------------------------------------------------
-- The questions table holds all of the data about the kinds of
-- questions that can be asked about a knowledge object.

create table sn_questions (
        question_id
          integer 
            constraint sn_questions_question_id_fk
              references acs_objects (object_id)
            constraint sn_questions_question_id_pk 
              primary key,
        pretty_name
          varchar(4000),
        abstract_data_type
          varchar(50) 
            constraint sn_questions_abstract_data_nn not null,
        presentation_type
          varchar(100)
            constraint sn_questions_presentation_t_nn not null,
        -- for all questions which display lists
        order_by
          varchar(100),
        -- default for input forms
        default_value
          varchar(4000),
        entry_explanation
          varchar(4000),
        tag_width
          integer,
        tag_height
          integer,
        help_text
          varchar(4000),
        -- for object_link: which type to link to
        target_object_type_id
          integer,
        -- that is merged in from a change I (and Carsten ;) already did in
        -- 7.1
	defaults_question_id
	    integer default null
	    constraint sn_questions_defaults_qu_id_fk
	      references sn_questions(question_id),
        references_question_id
          integer
            constraint sn_questions_referenc_qu_id_fk
              references sn_questions(question_id),
	-- for question of data type category
	tree_id
	  integer
	    constraint sn_questions_tree_ref references generic_trees,
	node_id
	  integer
	    constraint sn_questions_node_ref references sw_category_dim,
        browse_p
          varchar(1) default 't'
            constraint sn_questions_browse_p_ck
              check (browse_p in ('t','f')),
        -- This is only used during data migration from ShareNet 7.
        category_id
          integer,
	max_categories
	  integer default null,
	-- this is only for abstract_data_type date to specify what years
	-- should be in the select box in respect to the default date.
	year_from
	  integer default -5,
	year_to
	  integer default 5
);


begin
  acs_object_type.create_type ( 
    supertype     => 'acs_object', 
    object_type   => 'sn_question', 
    pretty_name   => 'Question', 
    pretty_plural => 'Questions', 
    table_name    => 'SN_QUESTIONS', 
    id_column     => 'QUESTION_ID' 
  );     
end;
/
show errors

insert into sn_questions (question_id, pretty_name, abstract_data_type, presentation_type)
select object_id, name, 'dummy', 'dummy' from 
acs_magic_objects where name = 'km_dummy_object';

------------------------------------------------------------------------------
-- Together with sn_question_object_type_map, table sn_object_types defines
-- the possible types of knowledge objects.

create table sn_types_map_short_name (
	object_type_id	
          integer,
	short_description
          integer
            constraint sn_types_map_short_desc_id_fk
            references sn_questions (question_id),
        position
          integer,
        constraint sn_types_map_short_name_pk 
          primary key (object_type_id, short_description)
);

create table sn_object_types (
	object_type_id	
          integer 
            constraint sn_object_types_pk 
              primary key,
        context_id
            constraint sn_object_types_context_id_fk
            references apm_packages (package_id)
                on delete cascade,
	short_name
	  varchar(100),
	pretty_name
          varchar(100) 
            constraint sn_object_types_pretty_name_nn not null,
	pretty_plural
          varchar(100),
	-- filename for the graphic icon. This is always
        -- $object_type_id.extension
	graphic
          varchar(200),
	graphic_p
          char(1)
            default 'f'
            constraint sn_object_types_graphic_p_nn not null,
            constraint sn_object_types_graphic_p_ck
             check (graphic_p in ('t', 'f')),
	deleted_p
          char(1)
            default 'f'
            constraint sn_object_types_deleted_p_nn not null,
            constraint sn_object_types_deleted_p_ck
             check (deleted_p in ('t', 'f')),
	browse_p
          char(1)
            default 't'
            constraint sn_object_types_browse_p_nn not null,
            constraint  sn_object_types_browse_p_ck 
            check (browse_p in ('t', 'f')),
	-- If the object type is not public, consult sn_object_type_access
	-- to determine if a user may access this type.
	public_p
          char(1) default 't'         
            constraint sn_object_types_public_p_nn not null,
            constraint sn_object_types_public_p_ck 
            check (public_p in ('t','f')),
	-- to be able to prevent standalone objects of this object type
        -- (meaning objects which didnt created as child or nephew)
        -- needed this for people sharenet - demand descriptions
	create_p
          char(1) default 't'         
            constraint sn_object_types_create_p_nn not null,
            constraint sn_object_types_create_p_ck 
            check (create_p in ('t','f')),
	sort_key
          integer,
	-- The admin can define which question is the name, overview or
	-- public_until date for this object type:
	short_description
          integer
            constraint sn_object_typ_short_desc_id_fk
            references sn_questions (question_id),
	long_description
          integer
            constraint sn_object_type_long_desc_id_fk
            references sn_questions (question_id),
	public_until
          integer
            constraint sn_object_t_public_until_id_fk
            references sn_questions (question_id),
	-- these two links to date questions have been added for
	-- people sharenet (project start/end date):
	start_date
	  integer
	    constraint sn_object_t_start_date_id_fk
	    references sn_questions (question_id),
	end_date
	  integer
	    constraint sn_object_t_end_date_id_fk
	    references sn_questions (question_id),
        -- that is merged in from a change I (and Carsten ;) already did in
        -- 7.1
        linked_question_id
          integer
            constraint sn_object_t_linked_quest_id_fk
            references sn_questions(question_id),
	default_age_filter
          integer default 365,
	-- can objects of this type be archived?
        archive_p
          char(1)
            default 'f'
            constraint sn_object_types_archive_p_nn not null,
            constraint sn_object_types_archive_p_ck
             check (archive_p in ('t', 'f')),
        -- are you allowed to copy objects of this type?
        copy_p
          char(1)
            default 'f'
            constraint sn_object_types_copy_p_nn not null,
            constraint sn_object_types_copy_p_ck
             check (copy_p in ('t', 'f')),
        -- which sweeper should be checking for old objects?
	-- outdated: objects haven't been modified for a certain time
	-- expired: public_until date has been exceeded
        sweeper
          varchar(10)
	    default 'none'
	    constraint sn_object_types_sweeper_nn not null,
	    constraint sn_object_types_sweeper_ck
	     check (sweeper in ('none','outdated','expired')),
	-- action to be performed on the objects by the sweeper
	sweeper_action
	  varchar(10)
	    default 'private'
	    constraint sn_object_types_sw_action_nn not null,
	    constraint sn_object_types_sw_action_ck
	     check (sweeper_action in ('private','archive')),
	-- if >0 a warning email will be sent if the object is outdated/expired
	-- specifies the amount of days the action should be performed after
	-- the warning email
        sweeper_warning_time
          integer
	    default 0
	    constraint sn_object_types_warning_nn not null,
	-- specified the amount of days after which an unchanged objects is
	-- regarded as outdated
        sweeper_outdated_time
          integer
	    default 30
);


begin
  acs_object_type.create_type ( 
    supertype     => 'acs_object', 
    object_type   => 'sn_object_type', 
    pretty_name   => 'Object Type', 
    pretty_plural => 'Object Types', 
    table_name    => 'SN_OBJECT_TYPES', 
    id_column     => 'OBJECT_TYPE_ID' 
  );     
end;
/
show errors

-- We can only add this foreign key after creating the object_type_table.
alter table sn_questions 
  add constraint sn_questions_object_type_id_fk 
    foreign key (target_object_type_id) 
    references sn_object_types(object_type_id);

alter table sn_types_map_short_name
  add constraint sn_types_map_object_type_id
    foreign key (object_type_id) 
    references sn_object_types(object_type_id);


------------------------------------------------------------------------------
-- This table holds the objects.

create table sn_objects (
        object_id
          integer
            constraint sn_objects_id_pk
              primary key,
        object_type_id
          integer
            constraint sn_objects_object_type_id_fk
              references sn_object_types (object_type_id),
        -- objects should be subsite-aware as well
        context_id
            constraint sn_objects_context_id_fk
            references apm_packages (package_id)
                on delete cascade,
        one_line_description
          varchar(4000),
        overview
          varchar(4000),
        overview_html_p
          char(1) 
            constraint sn_objects_overview_html_p_ck
            check (overview_html_p in ('t','f')),
        creation_date
          date,
        original_author_id
          integer 
            constraint sn_objects_orig_author_id_fk
              references users (user_id),
        last_modified
          date,
        last_modifying_user_id
          integer 
            constraint sn_objects_last_mod_user_id_fk
              references users (user_id),
        user_checkoff_date date,
	-- id of last publisher (user with km_publish permission) (BPM)
	publisher_id
	  integer
	    constraint sn_objects_publisher_id_fk
	      references users (user_id),
        expiration_date
          date default '9999-12-31' 
            constraint sn_objects_expiration_date_nn not null,
        public_until date,
	-- these two dates got added for people sharenet:
	start_date date,
	end_date date,
	public_p
	    char(1) default 'f'
            constraint sn_objects_public_p_ck
            check (public_p in ('t','f')),
	archived_p
	    char(1) default 'f'
            constraint sn_objects_archived_p_ck
            check (archived_p in ('t','f')),
	-- to mark object (i.e. nephews) that should be reviewd (BPM)
	in_review_p
	    char(1) default 'f'
            constraint sn_objects_in_review_p_ck
            check (in_review_p in ('t','f')),
        access_total integer default 0,
        access_month integer default 0,
	-- date the object got archived
        archiving_date
          date
	    default null,
	-- date a warning email got sent that object is outdated
        outdated_warning_date
          date
	    default null,
	-- date a warning email got sent that object is expired (public_until)
        expired_warning_date
          date
	    default null
);


begin
  acs_object_type.create_type ( 
    supertype     => 'acs_object', 
    object_type   => 'sn_object', 
    pretty_name   => 'Library Object', 
    pretty_plural => 'Library Objects', 
    table_name    => 'SN_OBJECTS', 
    id_column     => 'OBJECT_ID' 
  );     end;
/
show errors


-- Create some indexes for performance.

create index sn_objects_expiration_date_ix on sn_objects (expiration_date);

create index sn_objects_public_until_ix on sn_objects (public_until);

create index sn_objects_start_date_ix on sn_objects (start_date);

create index sn_objects_end_date_ix on sn_objects (end_date);

create index sn_objects_checkoff_date_ix on sn_objects (user_checkoff_date);

create index sn_objects_last_modified_ix on sn_objects (last_modified);

create index sn_objects_new_stuff_ix on sn_objects (last_modified,
user_checkoff_date, expiration_date, object_type_id);

create index sn_objects_type_id_ix on sn_objects (object_type_id);

create index sn_objects_browse_ix on sn_objects (object_type_id, context_id, expiration_date, last_modified, public_p, archived_p, object_id);

create index sn_objects_one_line_desc_ix on sn_objects (substr(upper(one_line_description),1,1));

create index sn_objects_archived_p_ix on sn_objects (archived_p);

create index sn_objects_o_warning_date_ix on sn_objects (outdated_warning_date);

create index sn_objects_e_warning_date_ix on sn_objects (expired_warning_date);

create index sn_object_publisher_id_ix on sn_objects (publisher_id);

------------------------------------------------------------------------------
-- This defines the set of questions associated with a given object
-- type. Questions can be used in common between object types and grouped
-- together under one parent_question_id for composite questions.
create table sn_question_object_type_map (
        question_id
          integer
            constraint sn_question_otm_question_id_fk
            references sn_questions (question_id),
        object_type_id
          integer
            constraint sn_question_otm_object_t_id_fk
            references sn_object_types (object_type_id),
        sort_key
          integer,
        form_number
          integer,
        mandatory_p
          char(1) 
            constraint sn_question_otm_mandatory_p_ck 
            check (mandatory_p in ('t','f')),
        question_state
          varchar(100) 
            constraint sn_question_otm_question_st_ck 
            check(question_state in
            ('active','deprecated','read-only','invisible')),
        -- used for composite and branches
	-- -50 is the magic object from acs_magic_objects where name='km_dummy_object'. Don't change it!
        parent_question_id
          integer
            default -50
            constraint sn_question_otm_parent_q_id_nn not null
            constraint sn_question_otm_parent_q_id_fk
            references sn_questions (question_id),
        -- t if this question itself is a branch, NOT if this question LEADS to branches
        branch_p
          char(1)
            constraint sn_question_otm_branch_p_ck 
            check (branch_p in ('t','f')),
        -- applies to the question as the parent node of a branch
        branch_operator
          varchar(4000),
        -- the answers apply to the child nodes
        branch_answer
          varchar(4000), 
        -- can reference categories or sn_answer_options
        branch_answer_foreign_key
          integer,
        default_branch
          integer
            constraint sn_question_otm_default_bra_fk
            references sn_questions (question_id),
        constraint sn_question_object_type_map_pk 
          primary key (question_id, object_type_id)
);

------------------------------------------------------------------------------
-- ***** Object Data *****

-- This table stores the content of the one-to-one questions having abstract
-- data type "text" for any given object.

-- The questions_id shows which question the content answers. If a single
-- question has more than one answer, then that data belongs in the
-- multiple-choice answers tables (sn_answer_options and sn_object_option_map)
-- having the abstract data type "multiple_choice" or should be constructed as
-- composite question having the abstract data type "composite".

create table sn_content (
        object_id
          integer 
            constraint sn_content_object_id_fk
              references sn_objects(object_id),
        question_id
          integer  
            constraint sn_content_question_id_fk
              references sn_questions(question_id),
        content
          clob,
        html_p
          char(1)
            constraint sn_content_html_p_ck 
            check (html_p in ('t', 'f')),
        constraint sn_content_pk 
          primary key (object_id, question_id)
);       

------------------------------------------------------------------------------
-- ***** Auditing *****
-- Whenever the content of an object is changed then we store it here. We
-- don't store changes in the categorization, linking or multiple choice
-- questions.

create table sn_audit_table (
        object_id
          integer 
            constraint sn_audit_table_object_id_fk
              references sn_objects(object_id),
        question_id
          integer  
            constraint sn_audit_table_question_id_fk
              references sn_questions(question_id),
        --this refers to the question being modified
        last_modified
          date 
            constraint sn_audit_table_last_modifie_nn not null,
        last_modifying_user_id
          integer 
            constraint sn_audit_table_last_mo_u_id_fk
              references users (user_id),
        content
          varchar(4000)
);

create index audit_table_ix on sn_audit_table(object_id);

-- The ShareNet admins want to give a reason for object deletion to supervise
-- the incentive system more efficiently.

create table sn_object_delete_reasons (
        object_id
          integer 
            constraint sn_object_delete_rea_obj_id_fk
              references sn_objects(object_id)
            constraint sn_object_delete_reasons_pk 
            primary key,
        reason_for_deleting
          varchar(4000),
        deleted_on   
          date default sysdate
);

-- For the archive

create table sn_object_archive_reasons (
        object_id
          integer 
            constraint sn_object_archive_re_obj_id_fk
              references sn_objects(object_id),
        reason_for_archiving
          varchar(4000),
        archived_on   
          date default sysdate,
	constraint sn_object_archive_reasons_pk 
	  primary key (object_id, archived_on)
);

------------------------------------------------------------------------------
-- This table holds the answers to multiple choice questions
create table sn_answer_options (
        option_id
          integer
            constraint sn_answer_options_option_id_pk 
              primary key,
        question_id
          integer
            constraint sn_answer_options_questi_id_fk
              references sn_questions(question_id),
        answer_option
          varchar(4000),
        sort_key
          integer
);

------------------------------------------------------------------------------
-- This table links multiple-choice answers to objects
create table sn_object_option_map (
       object_id
          integer 
            constraint sn_object_option_map_obj_id_fk
              references sn_objects(object_id),
       option_id
          integer 
            constraint sn_object_option_map_opt_id_fk
              references sn_answer_options(option_id)
);

------------------------------------------------------------------------------
-- (bran 003)
-- When linking was library-specific thing we
-- had question_id column in the sn_links table.
-- Now we have a special table which answers the
-- question: "Given a link_id, to which question
-- was the user answering when he created that link?"
create table sn_question_link_map (
        link_id
          integer
            constraint sn_question_link_ma_link_id_fk
            references sn_links (link_id)
                on delete cascade
            constraint sn_question_link_map_pk
                primary key,
        question_id
          integer
            constraint sn_question_lin_question_id_fk
                references sn_questions (question_id)
            constraint sn_question_lin_question_id_nn
                not null
);

create index sn_question_link_ma_q_id_ix on sn_question_link_map (question_id);

------------------------------------------------------------------------------
-- Excursions & Navigation

create table km_path (
        path_id
          integer
          constraint km_path_pk
            primary key
          constraint km_path_path_id_nn not null ,
        last_path_id
          integer,
        action
          varchar(100),
        values_list
          varchar(4000),
        return_to		
          varchar(4000),
        start_time    
          date
);

------------------------------------------------------------------------------
-- access counting
create table sn_access_counts (
	object_id    
          integer
            constraint sn_access_counts_object_id_fk
              references sn_objects(object_id),
	access_count 
          integer,
	access_date
          date,
        constraint sn_access_counts_pk 
          primary key (object_id,access_date)
);

create index sn_access_counts_composite_ix on sn_access_counts (object_id,access_date,access_count);

------------------------------------------------------------------------------
-- For backwards compatibility. Old-style URLs looked like this:
-- /library/browse-one-type.tcl?object_type=market 
-- and they need to be converted to /library/browse-one-type.tcl?object_type_id

create table sn_table_name_map (
	object_type_id	
          integer 
            constraint sn_table_name_map_pk 
              primary key,
        context_id
            constraint sn_table_name_map_cont_id_fk
            references apm_packages (package_id)
                on delete cascade,
       object_type_name        varchar(100)
);

------------------------------------------------------------------------------
--   We're maintaining km_flat_object_hierarchy with triggers that fire
--   on insert or delete from sn_links for link type parent_child.  
--   Since it's not allowed to access sn_links from triggers defined on it
--   (table is mutating etc etc), we keep the copy of the relevant data in
--   flat_object_hierarchy itself. Suppose we have following hierarchy:

-- --------------
--  102
--      106
--           107
--      141
--           121
-- --------------
--
--   From source sn_links table:

-- SITE_WIDE_ID_A     SITE_WIDE_ID_B
-- ------------------ -----------------
--                102               106
--                106               107
--                141               121
--                102               141


--   ... we arrive on flat_object_hierarchy


--    PARENT      CHILD DISTANCE
-- --------- ---------- --------
--       141        121 1
--       102        121 2
--       102        141 1
--       102        106 1
--       102        107 2
--       106        107 1


create table km_flat_object_hierarchy (
        object_hierarchy_id
          integer
            constraint km_flat_object_hierarchy_id_pk
              primary key,
        parent
          integer 
	    constraint km_flat_object_hier_parent_nn not null
	    constraint km_flat_object_hier_parent_fk 
	      references acs_objects (object_id),
	child
          integer
	    constraint km_flat_object_hier_child_nn not null
	    constraint km_flat_object_hier_child_fk 
	      references acs_objects (object_id),
        distance          
	  integer,
	link_type
	  varchar2(100),
        constraint km_flat_object_hier_unique unique (parent, child)
);

create index km_flat_object_hier_child_ix on km_flat_object_hierarchy(child);


------------------------------------------------------------------------------
-- This table is pretty damn useful if you want to track long-running
-- transaction.  It is being filled by a pl/sql procedure that commits
-- within a autonomous transaction.

create table km_logger_table (
  log_date 
    date 
      default sysdate, 
  who_is_logging
    varchar2(4000),
  log_text 
    varchar2(4000)
);


-----------------------------------------------------------------------------
-- This table is for the library sweeper proc that takes care of outdated
-- or expired objects and sets them to private or archive them if
-- the user didn't take action after a warning email

create table km_sweeper (
       user_id
         integer
	   constraint km_sweeper_user_id_fk
	     references users,
       package_id
         integer
	   constraint km_sweeper_package_id_fk
	     references apm_packages,
       object_type_id
         integer
	   constraint km_sweeper_obj_type_id_fk
	     references sn_object_types,
       object_id
         integer
	   constraint km_sweeper_object_id_fk
	     references sn_objects,
       object_name
         varchar2(4000),
       content
         varchar2(4000),
       primary key (user_id, object_id)
);

-- this table holds the library sweeper emails for publishers
create table km_sweeper_publisher (
       user_id
         integer
	   constraint km_sweeper_publ_user_id_fk
	     references users,
       package_id
         integer
	   constraint km_sweeper_publ_package_id_fk
	     references apm_packages,
       object_type_id
         integer
	   constraint km_sweeper_publ_obj_type_id_fk
	     references sn_object_types,
       object_id
         integer
	   constraint km_sweeper_publ_object_id_fk
	     references sn_objects,
       object_name
         varchar2(4000),
       content
         varchar2(4000),
       primary key (user_id, object_id)
);


------------------------------------------------------------------------------
create table km_library_searches (
       km_library_searches_id
         integer
           constraint km_library_searches_id_pk
             primary key,
       user_id
	 integer
           constraint km_library_searches_user_id_fk
             references users (user_id),
       name
	 varchar(200),
       url_params
	 clob,
       creation_date
         date 
	   default sysdate
);

create table km_advanced_search_settings (
       search_id        
         integer
           constraint km_advanced_ss_search_id_fk
             references km_library_searches(km_library_searches_id),
       parameter_key
         varchar(4000),
       parameter_value
         clob
);

------------------------------------------------------------------------------
create table sn_owner_history (
	object_id
	  integer
	    constraint sn_own_hist_object_id_fk
	      references sn_objects(object_id),
	change_user_id
	  integer
	    constraint sn_own_hist_change_user_id_fk
	      references users(user_id),
	old_user_id
	  integer
	    constraint sn_own_hist_old_user_id_fk
	      references users(user_id),
	new_user_id
	  integer
	    constraint sn_own_hist_new_user_id_fk
	      references users(user_id),
	change_date
	  date,
	reason
	  varchar(4000)
);
	
------------------------------------------------------------------------------
-- th 2001-10-22  tables added for people sharenet: application form
------------------
create table psn_res_applications (
       application_id	      integer primary key,
       object_id	      constraint psn_res_app_object_id_fk references sn_objects on delete cascade,
       resource_req_id	      constraint psn_res_app_res_req_id_fk references sn_objects on delete cascade,
       user_id                constraint psn_res_app_user_id_fk references users,
       creation_date	      date default sysdate,
       application_date	      date default null,
       sent_p		      char(1) default 'f' constraint psn_res_app_sent_p_ck check (sent_p in ('t','f')),
       recipient	      varchar2(4000),
       subject		      varchar2(1000),
       contact_data	      varchar2(4000),
       nationality	      varchar2(4000),
       manager_email	      varchar2(4000),
       working_area	      varchar2(4000),
       role_other             varchar2(1000),
       first_language         varchar2(1000),
       second_language_id     integer,
       third_language_id      integer,
       first_language_prof_id integer,
       second_language_prof_id integer,
       third_language_prof_id integer,
       other_language	      varchar2(4000),
       from_date	      date,
       to_date		      date,
       strengths	      varchar2(4000),
       leadership	      varchar2(4000),
       intercultural	      varchar2(4000),
       comments		      varchar2(4000),
       conditions	      varchar2(4000)
);

create index psn_res_app_object_idx on psn_res_applications (object_id);

create index psn_res_app_res_idx on psn_res_applications (resource_req_id);

create index psn_res_app_user_idx on psn_res_applications (user_id);

create table psn_res_application_roles (
       application_id   integer constraint psn_res_app_roles_app_fk references psn_res_applications on delete cascade,
       role_id		integer,
       primary key (application_id, role_id)
);

create table psn_category_trees (
       package_id		integer primary key,
       role_magic_id		constraint psn_cat_trees_role_fk references acs_objects (object_id) on delete set null,
       language_magic_id	constraint psn_cat_trees_lang_fk references acs_objects (object_id) on delete set null,
       proficiency_magic_id	constraint psn_cat_trees_prof_fk references acs_objects (object_id) on delete set null
);

create table psn_attachments (
       attachment_id	     integer primary key,
       application_id	     constraint psn_attach_appl_id_fk references psn_res_applications on delete cascade,
       title		     varchar2(1000),
       mime_type	     varchar2(200) default 'text/plain',
       filename		     varchar2(200),
       attachment	     blob default empty_blob()
);

create index psn_attach_appl_id_idx on psn_attachments(application_id);

create sequence psn_attachment_id_seq start with 1;

begin
  acs_object_type.create_type ( 
    supertype     => 'acs_object',
    object_type   => 'psn_tree_object',
    pretty_name   => 'Demand Application Form',
    pretty_plural => 'Demand Application Forms',
    table_name    => 'PSN_CATEGORY_TREES'
  );     
end;
/
show errors

begin
  acs_object_type.create_type ( 
    supertype     => 'acs_object',
    object_type   => 'psn_application',
    pretty_name   => 'Demand Application',
    pretty_plural => 'Demand Applications',
    table_name    => 'PSN_RES_APPLICATIONS',
    id_column     => 'APPLICATION_ID'
  );     
end;
/
show errors


create table approval_coordinators (
        package_id integer
                      constraint sn_approval_coord_packag_id_fk
                        references apm_packages (package_id)
                        on delete cascade,
        coordinator_id
          integer 
                      constraint sn_approval_coord_coord_id_fk
                      references users (user_id),
        constraint approval_coordinators_pk 
          primary key (package_id, coordinator_id)
);

------------------------------------------------------------------------------
-- now go on and create the packages

@@library-temp-tables
@@library-packages
@@library-package-bodies
@@library-triggers
@@library-workflow-create
@@library-workflow-packages

begin
   acs_privilege.create_privilege('km_publish');
   acs_privilege.add_child('km_publish','read');
   acs_privilege.add_child('km_publish','write');
   acs_privilege.add_child('km_publish','delete');
end;
/
show errors