CREATE TABLE assessment
  (
     name         VARCHAR PRIMARY KEY,
     bonus_weight DOUBLE NOT NULL,
     outof        DOUBLE NOT NULL
  );


CREATE TABLE category
  (
     category   VARCHAR PRIMARY KEY,
     sort_order SMALLINT
  );


CREATE TABLE criterion
  (
     criterion   VARCHAR PRIMARY KEY,
     category    VARCHAR NOT NULL,
     description VARCHAR NOT NULL,
     sort_order  SMALLINT,
     weight      REAL,
     min         SMALLINT,
     max         SMALLINT,
     special     BOOLEAN,
     CONSTRAINT fk_criterion_category FOREIGN KEY(category) REFERENCES category(
     category)
  );


CREATE TABLE submission
  (
     student VARCHAR PRIMARY KEY
  );


CREATE TABLE result
  (
     student   VARCHAR NOT NULL,
     criterion VARCHAR NOT NULL,
     result    REAL NOT NULL,
     comment   VARCHAR,
     CONSTRAINT pk_result PRIMARY KEY(student, criterion),
     CONSTRAINT fk_result_student FOREIGN KEY(student) REFERENCES submission(
     student),
     CONSTRAINT fk_result_criterion FOREIGN KEY(criterion) REFERENCES criterion(
     criterion)
  );


CREATE VIEW weighted_mark
AS
  SELECT student,
         Sum(weighted) * (SELECT outof
                          FROM   assessment) AS MARK
  FROM   (SELECT student,
                 result,
                 weight,
                 max,
                 ( result / max * ( weight / (SELECT Sum(weight)
                                              FROM   criterion
                                              WHERE  NOT special) ) ) AS
                 WEIGHTED
          FROM   result
                 INNER JOIN criterion
                         ON result.criterion = criterion.criterion
          WHERE  NOT special)
  GROUP  BY student;


-- UNION is a hack to ensure we always get something even if there are no special criterion
CREATE VIEW weighted_special
AS
  SELECT student,
         Sum(special) * (SELECT bonus_weight
                         FROM   assessment) AS special
  FROM
         (
				SELECT student,
						 result AS SPECIAL
				FROM   result
						 INNER JOIN criterion
									ON criterion.criterion = result.criterion
				WHERE  special

				UNION ALL

				SELECT DISTINCT student, 0
				FROM   result
         )

  GROUP  BY student


CREATE VIEW mark
AS
  SELECT weighted_mark.student,
         Round(Casewhen(mark + special <= (SELECT outof
                                           FROM   assessment), mark + special, (
               SELECT
               outof
               FROM
               assessment)), 5) AS MARK
  FROM   weighted_mark
         INNER JOIN weighted_special
                 ON weighted_mark.student = weighted_special.student;


CREATE VIEW marking_schedule
AS
  SELECT criterion.category,
         description,
         weight,
         max AS OUTOF
  FROM   criterion
         INNER JOIN category
                 ON category.category = criterion.category
  ORDER  BY category.sort_order,
            criterion.sort_order;


CREATE VIEW feedback
AS
  SELECT student,
         category.category,
         comment
  FROM   criterion
         INNER JOIN result
                 ON result.criterion = criterion.criterion
         INNER JOIN category
                 ON criterion.category = category.category
  WHERE  comment <> ''
  ORDER  BY category.sort_order,
            criterion.sort_order;


CREATE VIEW allmarks("Student", "Weighted Mark")
AS
  SELECT submission.student,
         COALESCE(Cast(Round(mark, 2) AS VARCHAR), '')
  FROM   submission
         LEFT JOIN mark
                ON submission.student = mark.student