-- uwdir.sql
-- DDL for import of UWdir extract and classlist
-- ogsas-dev@cscf.cs.uaterloo.ca

-- Store one-per-person information.
-- The pdbid comes from Extract column 20 for some reason.
-- Columns 1-4, 8-17, and 21 get fields here
CREATE TABLE uwdir_person (
  pdbid                 INTEGER,
  primary key (pdbid),
  
  surname               VARCHAR,             -- Extract column 1
  givennames            VARCHAR,             -- Extract column 2
  nicknames             VARCHAR,             -- Extract column 3
  initials              VARCHAR,             -- Extract column 4

  fax                   VARCHAR,             -- Extract column 8
  email                 VARCHAR,             -- Extract column 9
  homepage              VARCHAR,             -- Extract column 10
  visible               BOOLEAN,             -- Extract column 11
  authority             VARCHAR,             -- Extract column 12
  created               DATE,                -- Extract column 13
  lastMentioned         DATE,                -- Extract column 14
  activated             DATE,                -- Extract column 15
  lastChanged           DATE,                -- Extract column 16
  expires               DATE,                -- Extract column 17

  -- Extract column 21 is split up into the following two fields.
  -- The full column may be obtained by concatenating these back together.
  -- In practice the useridext is almost entirely useless.
  userid                VARCHAR(8) NOT NULL UNIQUE,
  useridext             VARCHAR NOT NULL
);

-- Extract column 5
CREATE TABLE uwdir_personDepartment (
  pdbid                 INTEGER REFERENCES uwdir_person (pdbid),
  department            VARCHAR,
  PRIMARY KEY (pdbid, department)
);

-- Extract column 6
CREATE TABLE uwdir_personOffice (
  pdbid                 INTEGER REFERENCES uwdir_person (pdbid),
  office                VARCHAR,
  PRIMARY KEY (pdbid, office)
);

-- Extract column 7
CREATE TABLE uwdir_personPhone (
  pdbid                 INTEGER REFERENCES uwdir_person (pdbid),
  phone                 VARCHAR,
  PRIMARY KEY (pdbid, phone)
);

-- Extract column 18
CREATE TABLE uwdir_personHrId (
  pdbid                 INTEGER REFERENCES uwdir_person (pdbid),
  hrid                  CHAR(6),
  PRIMARY KEY (pdbid, hrid)
);

-- Extract column 19
CREATE TABLE uwdir_personStudentId (
  pdbid                 INTEGER REFERENCES uwdir_person (pdbid),
  uwID                  UWID_TYPE UNIQUE,
  PRIMARY KEY (pdbid, uwID)
);

-- Extract column 20, telephone services ID only
CREATE TABLE uwdir_personOtherId (
  pdbid                 INTEGER REFERENCES uwdir_person (pdbid),
  tsid                  CHAR(7),
  PRIMARY KEY (pdbid, tsid)
);

-- Extract column 22
CREATE TABLE uwdir_personDepartmentCode (
  pdbid                 INTEGER REFERENCES uwdir_person (pdbid),
  deptCode              VARCHAR,
  primary key (pdbid, deptCode)
);

-- Extract column 23
CREATE TABLE uwdir_personOU (
  pdbid                 INTEGER REFERENCES uwdir_person (pdbid),
  ou                    VARCHAR,
  PRIMARY KEY (pdbid, ou)
);


-- Store one-per-person-term class information.
-- Column 1 is the UW student ID number
-- Columns 2-4, 12-14 duplicate name information from the Extract
-- Column 5 is always blank for us
-- Columns 6-10, 15-16 get fields here
CREATE TABLE uwdir_studentTerm (
  uwID                  UWID_TYPE REFERENCES uwdir_personStudentId (uwID),
  term                  TERM_TYPE REFERENCES uw_term (term),
  PRIMARY KEY (uwID, term),

  groupCode             GROUP_TYPE NOT NULL, -- ClassList column 6
  plan                  PLAN_TYPE NOT NULL,  -- ClassList column 7
  status                CHAR(1) NOT NULL,    -- ClassList column 8
  time                  CHAR(1) NOT NULL,    -- ClassList column 9
  level                 CHAR(3) NOT NULL,    -- ClassList column 10
  
  type                  CHAR(3) NOT NULL,    -- ClassList column 15
  field                 VARCHAR(10) NOT NULL -- ClassList column 16
);

-- ClassList column 11
-- Really all but uwID and term should be replaced with the class number.
CREATE TABLE uwdir_studentClass (
  uwID                  UWID_TYPE REFERENCES uwdir_personStudentId (uwID),
  term                  TERM_TYPE REFERENCES uw_term (term),
  subject               VARCHAR(6) NOT NULL,
  catalog               VARCHAR(6) NOT NULL,
  section               CHAR(3) NOT NULL,
  session               CHAR(3) NOT NULL,
  meet                  CHAR(3) NOT NULL,
  PRIMARY KEY  (uwID, term, subject, catalog, section, session, meet),
  
  FOREIGN KEY (uwID, term) REFERENCES uwdir_studentTerm (uwID, term)
);
