-- drop all objects;

CREATE TABLE Assessment (
	Name   VARCHAR PRIMARY KEY,
	Out_of NUMERIC(7,3) NOT NULL
);

CREATE TABLE Category (
	Category   VARCHAR PRIMARY KEY,
	Sort_Order REAL NOT NULL
);

CREATE TABLE Editor_Type (
	Editor_Type VARCHAR PRIMARY KEY
);

INSERT INTO Editor_Type VALUES ('Scale'),('Numeric');

CREATE TABLE Criterion_Type (
	Criterion_Type VARCHAR PRIMARY KEY
);

INSERT INTO Criterion_Type VALUES ('Normal'),('Special'),('Penalty');

CREATE TABLE Criterion (
	Criterion      VARCHAR PRIMARY KEY,
	Category       VARCHAR NOT NULL,
	Description    VARCHAR NOT NULL,
	Sort_order     REAL NOT NULL,
	Weight         REAL NOT NULL,
	Min            SMALLINT NOT NULL,
	Max            SMALLINT NOT NULL,
	Editor_Type    VARCHAR NOT NULL,
	Criterion_Type VARCHAR NOT NULL,
	CONSTRAINT FK_Criterion_Category FOREIGN KEY (Category) REFERENCES Category (Category),
	CONSTRAINT FK_Criterion_Editor_Type FOREIGN KEY (Editor_Type) REFERENCES Editor_type (Editor_Type),
	CONSTRAINT FK_Criterion_Criterion_Type FOREIGN KEY (Criterion_Type) REFERENCES Criterion_type (Criterion_Type)
);

CREATE TABLE Team (
	Team_ID       VARCHAR NOT NULL,
	Name          VARCHAR NOT NULL,
	CONSTRAINT PK_Team PRIMARY KEY (Team_ID)
);

CREATE TABLE Student (
	Submission_ID VARCHAR UNIQUE NOT NULL,
	Student_ID    VARCHAR UNIQUE NOT NULL,
	Username      VARCHAR UNIQUE NOT NULL,
	First_Name    VARCHAR NOT NULL,
	Last_Name     VARCHAR NOT NULL,
	Email         VARCHAR UNIQUE NOT NULL,
	Send_Feedback BOOLEAN NOT NULL DEFAULT TRUE,
	Email_Sent    BOOLEAN NOT NULL DEFAULT FALSE,
	Team_ID       VARCHAR UNIQUE,
	CONSTRAINT PK_Student PRIMARY KEY (Submission_ID),
	CONSTRAINT FK_Student_Team FOREIGN KEY (Team_ID) REFERENCES Team (Team_ID)
);

CREATE TABLE Submission (
	Submission_ID VARCHAR PRIMARY KEY,
	Name VARCHAR NOT NULL,
	CONSTRAINT FK_Submission_Student FOREIGN KEY (Submission_ID) REFERENCES Student (Submission_ID),
	CONSTRAINT FK_Submission_Team FOREIGN KEY (Submission_ID) REFERENCES Student (Team_ID)
);

CREATE TABLE Result (
	Submission_ID VARCHAR NOT NULL,
	Criterion     VARCHAR NOT NULL,
	Result        NUMERIC(10,6) 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 OR REPLACE VIEW Weighted_Normal AS
	SELECT
		Submission_ID,
		Cast(Sum(Weighted) * (
			SELECT
				Out_Of
			FROM
				Assessment) as NUMERIC(10,6)) AS Mark
	FROM (
		SELECT
			Submission_ID,
			Result,
			Weight,
			Max,
			(Result / Max * (Weight / (
						SELECT
							Sum(Weight)
						FROM
							Criterion
						WHERE
							Criterion_Type = 'Normal'))) AS Weighted
			FROM
				Result
				INNER JOIN Criterion ON Result.Criterion = Criterion.Criterion
			WHERE
				Criterion_Type = 'Normal')
	GROUP BY
		Submission_ID;

-- special marks are multiplied by the criterion weight and added to the mark- use
-- negative weight for penalties
CREATE OR REPLACE VIEW Weighted_Special AS
	SELECT Submission_id,
			Cast(Sum(Special) as NUMERIC(10,6)) AS special
		FROM
			(
				SELECT Submission_id, ( Result * Weight ) AS special
					FROM Result
						INNER JOIN Criterion ON Result.Criterion = Criterion.Criterion
					WHERE Criterion_Type = 'Special'

				UNION ALL

					SELECT DISTINCT Submission_id, 0
						FROM Result
			)
		GROUP BY Submission_id;

CREATE OR REPLACE VIEW Weighted_Penalty AS
	SELECT Submission_ID,
			Cast(Sum(Penalty) as NUMERIC(10,6))  AS Penalty
		FROM
			(
				SELECT Submission_ID, ( Result * Weight ) AS Penalty
					FROM Result
						INNER JOIN Criterion ON Result.Criterion = Criterion.Criterion
					WHERE Criterion_Type = 'Penalty'

				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 OR REPLACE VIEW Mark AS
	SELECT
		Weighted_Normal.Submission_ID,
		case when mark - penalty > 0
			then cast(round(Mark - Penalty,3) as NUMERIC(7,3))
			else 0
			end as Mark
	FROM Weighted_Normal
		INNER JOIN Weighted_Penalty USING (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 OR REPLACE VIEW All_Marks ("Submission", "Mark", "Penalty", "Special", "Complete", "Email Sent") AS
	SELECT
		Submission.Submission_ID,
		COALESCE(Cast(Round(Mark, 3) AS VARCHAR), ''),
		COALESCE(Cast(Round(Penalty, 3) AS VARCHAR), ''),
		COALESCE(Cast(Round(Special, 3) AS VARCHAR), ''),
		COALESCE(Cast(CASE WHEN Complete THEN 'Yes' ELSE 'No' END AS varchar), ''),
		COALESCE(Cast(CASE WHEN Email_Sent THEN 'Yes' ELSE 'No' END AS varchar), '')
	FROM
		Submission
		LEFT JOIN Mark USING (Submission_ID)
		LEFT JOIN Weighted_Special USING (Submission_ID)
		LEFT JOIN Weighted_Penalty 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 OR REPLACE VIEW weighted_schedule AS
SELECT
	cat.category,
	criterion,
	description,
	weight,
	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
			Criterion_Type = 'Normal')
		INNER JOIN category cat ON crit.category = cat.category
	WHERE Criterion_Type = 'Normal'
	ORDER BY
		cat.sort_order,
		crit.sort_order;

CREATE OR REPLACE VIEW weighted_categories AS
SELECT
	category,
	round(cast(sum(relative_weight) as real), 2) AS weight,
	round(cast(sum(relative_weight) / 100 * (select out_of from assessment) as real), 2) AS out_of,
FROM
	WEIGHTED_SCHEDULE
GROUP BY
	category;

CREATE OR REPLACE 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,
	round(penalty, 3) as penalty,
	round(special, 3) as special

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 s.Submission_ID = r.Submission_ID
	INNER JOIN mark m ON m.submission_id = r.Submission_ID
	INNER JOIN weighted_special ws on ws.submission_id = r.Submission_ID
	INNER JOIN Weighted_Penalty wp on wp.submission_id = r.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);


create or replace view unmarked as select * from submission where submission_id not in (select distinct submission_id from result);