CREATE TABLE assessment (
	name varchar PRIMARY KEY,
	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;

-- special marks are multiplied by the criterion weight and added to the mark - use
-- negative weight for penalties
CREATE VIEW weighted_special AS
SELECT
	submission_id,
	Sum(special) AS special
FROM (
	SELECT
		submission_id,
		(result * weight) AS special
	FROM
		result
		INNER JOIN criterion ON result.criterion = criterion.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_id,
		Round(mark + special, 3) AS MARK
FROM
	weighted_mark
	INNER JOIN weighted_special ON weighted_mark.submission_id = weighted_special.submission_id;

-- 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);