--
--  Copyright (C) 2002 OpenForce, Inc.
--
--  This file is part of dotLRN.
--
--  dotLRN is free software; you can redistribute it and/or modify it under the
--  terms of the GNU General Public License as published by the Free Software
--  Foundation; either version 2 of the License, or (at your option) any later
--  version.
--
--  dotLRN is distributed in the hope that it will be useful, but WITHOUT ANY
--  WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
--  FOR A PARTICULAR PURPOSE.  See the GNU General Public License for more
--  details.
--

--
-- The DotLRN Recruiting system
-- copyright 2002, OpenForce, Inc.
-- distributed under the GNU GPL v2
--
-- for PG 7.1.3 and above
--
-- @author chak (chak@openforce.net)
-- @creation-date 2002-07-27
-- @version $Id: recruiting-create.sql,v 1.2 2002/07/29 23:38:09 chak Exp $

create table recruiting_status_types (
       -- this is a full fledged acs_object
       status_type_id              integer
                                   constraint recruiting_status_type_fk
                                   references acs_objects(object_id)
                                   constraint recruiting_status_type_pk
                                   primary key,
       package_id                  integer
                                   constraint rc_status_package_id_fk
                                   references apm_packages(package_id)
                                   constraint rc_status_package_id_nn
                                   not null,

       short_desc                  varchar(2000) 
                                   constraint rc_status_short_desc_nn
                                   not null,
       long_desc                   varchar(4000),
       enabled_p                   boolean default 't'
);

select acs_object_type__create_type (
       'recruiting_status_type',
       'Recruiting Status Type',
       'Recruiting Status Types',
       'acs_object',
       'recruiting_status_types',
       'status_type_id',
       'recruiting_status_type',
       'f',
       null,
       'recruiting_status__name'
);

create table recruiting_criteria (
       -- this is a full fledged acs_object
       criteria_id                 integer
                                   constraint recruiting_criteria_fk
                                   references acs_objects(object_id)
                                   constraint recruiting_criteria_pk
                                   primary key,
       package_id                  integer
                                   constraint rc_criteria_package_id_fk
                                   references apm_packages(package_id)
                                   constraint rc_criteria_package_id_nn
                                   not null,

       criteria_name               varchar(2000)
                                   constraint rc_criteria_name_nn
                                   not null,
       description                 varchar(4000),
       enabled_p                   boolean default 't'
);

select acs_object_type__create_type (
       'recruiting_criteria',
       'Recruiting Criteria',
       'Recruiting Criteria',
       'acs_object',
       'recruiting_criteria',
       'criteria_id',
       'recruiting_criteria',
       'f',
       null,
       'recruiting_criteria__name'
);

create table recruiting_candidates (
       -- this is a full fledged acs_object
       candidate_id                integer
                                   constraint recruiting_candidate_id_fk
                                   references acs_objects(object_id)
                                   constraint recruiting_candidate_pk
                                   primary key,
       package_id                  integer
                                   constraint rc_package_id_fk
                                   references apm_packages(package_id)
                                   constraint rc_package_id_nn
                                   not null,

       -- address info
       first_name                  varchar(256),
       last_name                   varchar(256),
       address1                    varchar(2048),
       address2                    varchar(2048),
       city                        varchar(1024),
       state                       varchar(1024),
       zip                         varchar(5),
       zip_plus_four               varchar(4),
       country                     varchar(1024),

       email                       varchar(1024),

       status                      integer
                                   constraint recruiting_status_fk
                                   references recruiting_status_types(status_type_id)
);

select acs_object_type__create_type (
       'recruiting_candidate',
       'Recruiting Candidate',
       'Recruiting Candidates',
       'acs_object',
       'recruiting_candidates',
       'candidate_id',
       'recruiting_candidate',
       'f',
       null,
       'recruiting_candidate__name'
);

create table recruiting_interviews (
       -- this is a full fledged acs_object
       interview_id                integer
                                   constraint recruiting_interviews_id_fk
                                   references acs_objects(object_id)
                                   constraint recruiting_interviews_id_pk
                                   primary key,
       package_id                  integer
                                   constraint rc_interviews_package_id_fk
                                   references apm_packages(package_id)
                                   constraint rc_interviews_package_id_nn
                                   not null,

       interviewer_id              integer 
                                   references users(user_id),
       candidate_id                integer
                                   references recruiting_candidates(candidate_id)
);

select acs_object_type__create_type (
       'recruiting_interview',
       'Recruiting Interview',
       'Recruiting Interviews',
       'acs_object',
       'recruiting_interview',
       'interview_id',
       'recruiting_interview',
       'f',
       null,
       'recruiting_interview__name'
);

create table recruiting_ratings (
       -- this is a full fledged acs_object
       rating_id                   integer
                                   constraint recruiting_ratings_id_fk
                                   references acs_objects(object_id)
                                   constraint recruiting_ratings_id_pk
                                   primary key,
       package_id                  integer
                                   constraint rc_ratings_package_id_fk
                                   references apm_packages(package_id)
                                   constraint rc_ratings_package_id_nn
                                   not null,

       interview_id                integer
                                   constraint recruiting_rtng_interview_id_fk
                                   references recruiting_interviews(interview_id),
       criteria_id                 integer
                                   constraint recruiting_rtng_criteria_id_fk
                                   references recruiting_criteria(criteria_id),
       rating                      integer
                                   constraint recruiting_rating_range
                                   check (rating between 1 and 5)
);

select acs_object_type__create_type (
       'recruiting_rating',
       'Recruiting Rating',
       'Recruiting Ratings',
       'acs_object',
       'recruiting_ratings',
       'rating_id',
       'recruiting_rating',
       'f',
       null,
       'recruiting_rating__name'
);

\i recruiting-api-create.sql
