CREATE TABLE assessment
  (
     name         VARCHAR PRIMARY KEY,
     bonus_weight DOUBLE NOT NULL,
     out_of        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 (
     submission_id VARCHAR PRIMARY KEY
);

CREATE TABLE student (
	submission_id VARCHAR UNIQUE,
	student_id VARCHAR UNIQUE,
	username VARCHAR UNIQUE,
	first_name VARCHAR,
	last_name VARCHAR,
	email VARCHAR UNIQUE,
	send_feedback BOOLEAN,
	email_sent BOOLEAN
);

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


CREATE VIEW weighted_mark
AS
  SELECT submission_id,
         Sum(weighted) * (SELECT out_of
                          FROM   assessment) AS MARK
  FROM   (SELECT submission_id,
                 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 submission_id;


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

            UNION ALL

            SELECT DISTINCT submission_id, 0
            FROM result
        )

    GROUP  BY submission_id;

-- version to use when bonus contributes to other assessment (maximum mark CAN be exceeded)
--CREATE VIEW mark
--AS
--  SELECT weighted_mark.submission,
--         Round(mark + special, 2) AS MARK
--  FROM   weighted_mark
--         INNER JOIN weighted_special
--                 ON weighted_mark.submission = weighted_special.submission;


-- version to use when bonus contributes only to this assessment (maximum mark can not be exceeded)
CREATE VIEW mark
AS
  SELECT weighted_mark.submission_id,
         Round(Casewhen(mark + special <= (SELECT out_of
                                           FROM   assessment), mark + special, (
               SELECT
               out_of
               FROM
               assessment)), 5) AS MARK
  FROM   weighted_mark
         INNER JOIN weighted_special
                 ON weighted_mark.submission_id = weighted_special.submission_id;

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 allmarks("Student", "Weighted Mark", "Marking Complete", "Email Sent")
AS
	SELECT submission.submission_id,
			 COALESCE(Cast(Round(mark, 2) AS VARCHAR), ''),
			 COALESCE(Cast(complete AS VARCHAR), ''),
			 COALESCE(Cast(email_sent AS VARCHAR), '')
	FROM   submission
			 LEFT JOIN mark USING (submission_id)
			 LEFT JOIN (SELECT submission_id, Count(criterion) = (SELECT Count(criterion) FROM criterion)
							AS complete
							FROM result
							GROUP BY submission_id) USING (submission_id)
			LEFT JOIN student USING (submission_id);


-- view for showing relative weightings as percentages of total mark
create view weighted_schedule as
select cat.category, criterion, description, weight, special, round(weight / total_weight * 100, 2) as relative_weight, round(weight / total_weight * (select out_of from assessment), 2) as out_of
from criterion crit, (select sum(weight) as total_weight from criterion where not special)
inner join category cat on crit.category = cat.category
order by cat.sort_order, crit.sort_order;

create view weighted_categories as
select category,round(sum(relative_weight),1) as weight FROM WEIGHTED_SCHEDULE where not special group by category;


create view report as
select
	a.name as assessment, a.out_of,
	s.student_id, s.first_name || ' ' || s.last_name as name, s.username,
	cat.category, cat.sort_order as cat_order,
	cr.description as criterion, cr.max as max, cr.sort_order as cr_order,
	r.result,r.comment,
	round(m.mark,3) as mark
from assessment a
	join category cat
	inner join criterion cr on cat.category = cr.category
	inner join result r on r.criterion=cr.criterion
	inner join student s on r.submission_id =s.submission_id
	inner join mark m on r.submission_id = m.submission_id
order by student_id, cat_order,cr_order


-- use this if you want to produce useful category totals (and you don't care about weighting for individual criterion)
--UPDATE criterion
--SET    weight = Cast(max AS DOUBLE) / (SELECT Count(*)
--                                       FROM   criterion);