create table Paper_Offering
( Paper_ID      char(18),
  Paper_Code    char(7) not null,
  Year_Offered  smallint not null,
  Period_Code   char(2) not null,
  Campus_Code   char(3) not null,
  
  primary key ( Paper_ID )
);

--------------------------------------------------------------------------------
--
-- TRIG_Generate_Paper_ID
--
-- Trigger to automatically generate a paper identifier.
--
create function Generate_Paper_ID() returns trigger as '
  begin
    if (new.Paper_ID is null) then
      new.Paper_ID := new.Paper_Code || ''_'' ||
                      new.Period_Code || new.Campus_Code || ''_'' ||
                      new.Year_Offered;
    end if;
    return new;
  end;
' language 'plpgsql';

create trigger Generate_Paper_ID before insert on Paper_Offering
    for each row execute procedure Generate_Paper_ID();


create table Week
( Paper_ID      char(18),
  Start_Date    date,
  End_Date      date not null check ( End_Date > Start_Date ),
  Is_Holiday    boolean default false not null,
  
  primary key ( Paper_ID, Start_Date ),
  foreign key ( Paper_ID ) references Paper_Offering
);

create table Section
( Paper_ID      char(18),
  Section_ID    varchar(10),
  Title         text not null,
  Continuation  text not null, -- needed if a section spans mid-semester break
  URL           text,
  
  primary key ( Paper_ID, Section_ID ),
  foreign key ( Paper_ID ) references Paper_Offering
);

create table Lecture
( Paper_ID      char(18),
  Week_Starting date,
  Lecture_Date  date check ( Lecture_Date >= Week_Starting ),
  Title         text not null,
  URL           text,
  Section_ID    varchar(10) not null,
  Is_Holiday    boolean default false not null,
  
  primary key ( Paper_ID, Week_Starting, Lecture_Date ),
  foreign key ( Paper_ID, Week_Starting ) references Week,
  foreign key ( Paper_ID, Section_ID ) references Section
);

create table Other_Class
( Paper_ID      char(18),
  Week_Starting date,
  Class_Type    varchar(10) check ( Class_Type in ( 'laboratory', 'tutorial' ) ),
  Title         text not null,
  URL           text,
  
  primary key ( Paper_ID, Week_Starting, Class_Type ),
  foreign key ( Paper_ID, Week_Starting ) references Week
);

create table Reading
( Paper_ID      char(18),
  Week_Starting date,
  Content       text not null,
  
  primary key ( Paper_ID, Week_Starting ),
  foreign key ( Paper_ID, Week_Starting ) references Week
);

create table Assessment
( Paper_ID      char(18),
  Week_Starting date,
  -- We're assuming here that we'll never have two assessments for the same
  -- paper due at exactly the same time. This seems reasonable.
  Deadline      timestamp without time zone,
  Title         text not null,
  URL           text,
  
  primary key ( Paper_ID, Week_Starting, Deadline ),
  foreign key ( Paper_ID, Week_Starting ) references Week
);



-- Some test data.
--insert into paper_offering (paper_Code, year_offered, period_code, campus_code) values ('INFO212', 2009, 'S1', 'DNS');
--insert into paper_offering (paper_Code, year_offered, period_code, campus_code) values ('INFO321', 2009, 'S2', 'DNS');
--
--insert into week values ('INFO212_S1DNS_2009', date '2009-03-02', date '2009-03-06', false);
--insert into week values ('INFO212_S1DNS_2009', date '2009-03-09', date '2009-03-13', false);
--insert into week values ('INFO212_S1DNS_2009', date '2009-03-30', date '2009-04-03', false);
--insert into week values ('INFO212_S1DNS_2009', date '2009-04-13', date '2009-04-17', true);
--
--insert into section values ('INFO212_S1DNS_2009', 'intro', 'Introduction', 'http://info-nts-12.otago.ac.nz/info212/Lectures/Chapter0combined.pdf');
--insert into section values ('INFO212_S1DNS_2009', 'RDBMS', 'Relational DBMS', 'http://info-nts-12.otago.ac.nz/info212/Lectures/Chapter1combined.pdf');
--
--insert into lecture values ('INFO212_S1DNS_2009', date '2009-03-02', date '2009-03-02', 'Introduction', null, 'intro', false);
--insert into lecture values ('INFO212_S1DNS_2009', date '2009-03-02', date '2009-03-05', 'What is a DBMS?', null, 'RDBMS', false);
--insert into lecture values ('INFO212_S1DNS_2009', date '2009-03-09', date '2009-03-09', 'Functions of a DBMS', null, 'RDBMS', false);
--insert into lecture values ('INFO212_S1DNS_2009', date '2009-03-09', date '2009-03-12', 'The relational model', null, 'RDBMS', false);
--
--insert into other_class values ('INFO212_S1DNS_2009', date '2009-03-09', 'laboratory', 'Introduction (Oracle, TOAD)', 'http://info-nts-12.otago.ac.nz/info212/Labs/lab01/lab01-questions.html');
--insert into other_class values ('INFO212_S1DNS_2009', date '2009-03-09', 'tutorial', 'Database management systems', 'http://info-nts-12.otago.ac.nz/info212/Tutorials/tut01/tut01-questions.html');
--
--insert into reading values ('INFO212_S1DNS_2009', date '2009-03-02', 'Text Chs. 1 & 2');
--insert into reading values ('INFO212_S1DNS_2009', date '2009-03-09', 'Text �1�2');
--
--insert into assessment values ('INFO212_S1DNS_2009', date '2009-03-30', timestamp '2009-04-01 17:00:00', 'Assignment 1', 'http://info-nts-12.otago.ac.nz/info212/assessment/Assignment1.html');