-- suppinfo.sql
-- DDL for CS supplementary information form
-- ogsas-dev@cscf.cs.uaterloo.ca

-- Record the resume and a description of the applicant's research interests.
CREATE TABLE suppInfo_main (
  uwID                  UWID_TYPE,
  admitTerm             TERM_TYPE,
  PRIMARY KEY (uwID, admitTerm),

  researchInterests     VARCHAR,
  
  -- Applicant could fill in both of these, but no real reason to do so
  cvText                VARCHAR,
  cvPDF                 BYTEA,

  -- Have to be provided in order for foreign key declaration to work,
  -- but this form is really only for CS so these will never change
  groupCode             GROUP_TYPE          DEFAULT 'MATH' NOT NULL CHECK (groupCode = 'MATH'),
  department            DEPT_TYPE           DEFAULT 'CS' NOT NULL CHECK (department = 'CS'),

  FOREIGN KEY (groupCode, department, uwID, admitTerm)
    REFERENCES work_application (groupCode, department, uwID, admitTerm)
    ON UPDATE CASCADE ON DELETE CASCADE
);

-- Areas for past courses taken by applicant
CREATE TABLE suppInfo_course_area (
  courseArea            VARCHAR(3),
  PRIMARY KEY (courseArea),
  
  -- For sorting students' lists by course area
  sequence              INTEGER             UNIQUE NOT NULL,
  
  -- Human-readable name of course area
  description           VARCHAR             NOT NULL
);

INSERT INTO suppInfo_course_area VALUES ('ALG', 10, 'Algorithms/Data Structures');
INSERT INTO suppInfo_course_area VALUES ('AI',  15, 'Artificial Intelligence');
INSERT INTO suppInfo_course_area VALUES ('FDB', 20, 'File Structures/Databases');
INSERT INTO suppInfo_course_area VALUES ('GRP', 25, 'Graphics');
INSERT INTO suppInfo_course_area VALUES ('HA',  30, 'Hardware/Architecture');
INSERT INTO suppInfo_course_area VALUES ('NSC', 35, 'Numerical/Scientific Computing');
INSERT INTO suppInfo_course_area VALUES ('OS',  40, 'Operating Systems');
INSERT INTO suppInfo_course_area VALUES ('PLT', 45, 'Programming Language Theory');
INSERT INTO suppInfo_course_area VALUES ('SE',  50, 'Software Engineering');
INSERT INTO suppInfo_course_area VALUES ('THE', 55, 'Theory of Computing');
INSERT INTO suppInfo_course_area VALUES ('OTH', 60, 'Other CS courses');

-- Record courses taken in the past by the applicant
CREATE TABLE suppInfo_course (
  uwID                  UWID_TYPE,
  admitTerm             TERM_TYPE,
  courseArea            VARCHAR(3)          REFERENCES suppInfo_course_area (courseArea),
  courseNumber          VARCHAR,
  PRIMARY KEY (uwID, admitTerm, courseArea, courseNumber),

  courseTitle           VARCHAR,
  grade                 VARCHAR(3),
  
  FOREIGN KEY (uwID, admitTerm) REFERENCES suppInfo_main (uwID, admitTerm)
);
