-- referee.sql
-- DDL for electronic version of the
-- "Reference Form for Admission to Graduate Studies"
-- ogsas-dev@cscf.cs.uaterloo.ca

-- Referee form-related data types
CREATE DOMAIN REF_RATING_CODE VARCHAR
	CHECK (VALUE IN ('TOP05', 'TOP10', 'TOP20', 'TOP40'));

CREATE DOMAIN REF_RECOMMEND_STATUS VARCHAR
	CHECK (VALUE IN ('HIGHLY', 'RECOMMEND', 'RESERVATIONS', 'NOT'));

CREATE DOMAIN REF_WEB_GUID VARCHAR(36);

-- reference table
--
-- This table represents the data captured from the 
-- "Reference Form for Admission to Graduate Studies".
--
-- There is a one-to-one mapping between a reference and
-- a referee, we use different tables for access control purposes.
CREATE TABLE ref_reference (
  groupCode             GROUP_TYPE,
  department            DEPT_TYPE,
  uwID                  UWID_TYPE,
  admitTerm             TERM_TYPE,
  refID                 VARCHAR,
  PRIMARY KEY (groupCode, department, uwID, admitTerm, refID),

  -- Workflow management
  
  -- Uniquely identifies form once referee has started to fill it out
  -- Process which sends email fills this in
  webGUID               REF_WEB_GUID        UNIQUE NOT NULL,

  -- Filled in at top of form
  -- Filled in with current date upon first form submission by referee
  creationDate          DATE,

  -- Filled in near bottom of section 2
  -- Referee can edit form as long as this is NULL
  -- Choosing to print the form sets this to the current date
  -- and generates the PDF
  completionDate        DATE,
  -- PDF version of this form
  pdf                   BYTEA,

  -- Filled in when office receives printed and signed form
  received              DATE,
  
  -- Applicant-supplied information (section 1)
  -- Copied from current applicant information at creation time
  surname               VARCHAR,
  givenNames            VARCHAR,
  unit                  VARCHAR,
  level                 CHAR(1),
  specialization        VARCHAR,

  
  -- Referee-supplied information (section 2)
  timeAndCapacity       VARCHAR,              -- 2(a)
  recommend             REF_RECOMMEND_STATUS, -- 2(b)
  recommendRemarks      VARCHAR,              -- 2(b)
  isRecordFair          BOOLEAN,              -- 2(c)
  recordFairRemarks     VARCHAR,              -- 2(c)
  compareGroup          VARCHAR,              -- 2(d)
  compareGroupPlace     REF_RATING_CODE,      -- 2(d)
  
  -- Rating grid is handled by ref_rating

  -- Referee information mostly copied from OUAC reference data
  -- Referee may correct this information
  
  -- Left column
  name                  VARCHAR,              -- from gso_referee.name
  title                 VARCHAR,              -- from gso_referee.title
  institution           VARCHAR,              -- from gso_referee.organization
  dept                  VARCHAR,
  address1              VARCHAR,              -- from gso_referee.address1
  address2              VARCHAR,              -- from gso_referee.address2
  address3              VARCHAR,              -- from gso_referee.address3
  address4              VARCHAR,              -- from gso_referee.address4
  city                  VARCHAR,              -- from gso_referee.city
  provState             VARCHAR,              -- from gso_referee.provState
  postalCode            VARCHAR,              -- from gso_referee.postalCode
  country               CHAR(3),              -- from gso_referee.country
  
  -- Right column
  -- Signature is a written signature
  -- Date is filled in with completion date
  phone                 VARCHAR,              -- from gso_referee.phone
  email                 VARCHAR,              -- from gso_referee.email
  fax                   VARCHAR,
  url                   VARCHAR,
  
  -- Referee could fill in both of these, but no real reason to do so
  referenceText         VARCHAR,              -- Filled in text field
  referencePDF          BYTEA,                -- Uploaded PDF file
  
  FOREIGN KEY (groupCode, department, uwID, admitTerm) REFERENCES work_application (groupCode, department, uwID, admitTerm)
    ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE ref_rating_category (
  catCode               VARCHAR(3),
  PRIMARY KEY (catCode),
  
  sequence              INTEGER             UNIQUE NOT NULL,
  description           VARCHAR             NOT NULL
);

INSERT INTO ref_rating_category VALUES ('ACA', 10, 'Academic ability');
INSERT INTO ref_rating_category VALUES ('ANA', 20, 'Analytical ability');
INSERT INTO ref_rating_category VALUES ('ORI', 30, 'Originality');
INSERT INTO ref_rating_category VALUES ('JUD', 40, 'Judgement');
INSERT INTO ref_rating_category VALUES ('IND', 50, 'Industry');
INSERT INTO ref_rating_category VALUES ('RES', 60, 'Research skills/potential');
INSERT INTO ref_rating_category VALUES ('ORE', 70, 'Oral expression');
INSERT INTO ref_rating_category VALUES ('WRT', 80, 'Written expression');
INSERT INTO ref_rating_category VALUES ('OVR', 90, 'Overall Rating');

CREATE TABLE ref_rating (
  webGUID               REF_WEB_GUID        REFERENCES ref_reference (webGUID) ON DELETE CASCADE,
  catCode               VARCHAR(3)          REFERENCES ref_rating_category (catCode),
  PRIMARY KEY (webGUID, catCode),
  
  rating                REF_RATING_CODE,
  remarks               VARCHAR
);

-- Initialize a referee's ratings to be all blank whenever a ref_reference
-- record is created.
CREATE RULE ref_reference_insert AS ON INSERT TO ref_reference DO ALSO
  INSERT INTO ref_rating (webGUID, catCode) SELECT NEW.webGUID, catCode FROM ref_rating_category;
