-- Roles

-- Role for students
create role u3952239_Student;

-- Role for tutors
create role u3952239_Tutor;

-- Role for Course Managers
create role u3952239_CourseManagers;


-- Views

-- Public
create view Tutorial as 
select TutId, TutorName, Room, Day, Time
from B_Tutorial;

-- Student, Tutor, Manager
create view AssessmentItem as
select AId, ADate, TotalMark, FinalPercent
from B_AssessmentItem;

-- Student, Tutor, Manager
create view AssignmentSummary as
select AId, max(AMark) as Maximum, min(AMark) as Minimum,
round(avg(AMark),2) as Average, count(StudentId) as Submissions
from B_Mark
where lower(substr(aid,1,1)) = 'a'
group by aid;

-- Student
create view Student_Student as
select StudentId, Surname, FirstName, Username, TutId, Withdrawn
from B_A3Student
where StudentId = substr(user,8);

-- Student
create view Student_Mark as
select StudentId, AId, AMark, DateSubmitted
from B_Mark
where AId != 'exam'
and StudentId = substr(user,8,14);

-- Tutor
create view Tutor_Student as
select StudentId, Surname, FirstName, Username, S.TutId, Withdrawn
from B_A3Student S, B_Tutorial T
where S.TutId = T.TutId
and upper(TutorName) = upper(substr(user, 6));

-- Tutor
create view Tutor_Mark as
select M.StudentId, AId, AMark, DateSubmitted
from B_Mark M, B_A3Student S, B_Tutorial T
where S.TutId = T.TutId
and M.StudentId = S.StudentId
and upper(TutorName) = upper(substr(user, 6));

-- Tutor
create view MarkSummary as
select S.TutId, M.AId, max(AMark) as Maximum, min(AMark) as Miniumum,
round(avg(AMark),2) as Average, count(M.StudentId) as Subsissions
from B_Mark M, B_A3Student S
where M.StudentId = S.StudentId
and lower(substr(M.AId,1,1)) = 'a'
group by TutId, M.AId;

-- Managers
create view Student as
select * from B_A3Student;

-- Managers
create view Mark as
select * from B_Mark;

-- Managers
create view FinalMark as
select StudentId,
ROUND(SUM(FinalPercent*(AMark/TotalMark)),2) as Final_Mark
from B_Mark, B_AssessmentItem
where B_Mark.AId = B_AssessmentItem.AId
group by StudentId
order by Final_Mark desc;


-- amark, totalmark, finalpercent

-- granting roles to people
grant u3952239_Student to Student2010123;
grant u3952239_Student to Student2010999;
grant u3952239_Tutor to TutorJones;
grant u3952239_Tutor to TutorSmith;
grant u3952239_CourseManagers to Manager8100234;
grant u3952239_CourseManagers to Manager9100888;

-- make tutorial public
grant select on tutorial to Public;

-- Part 1
grant select on AssessmentItem to u3952239_Student;
grant select on AssessmentItem to u3952239_Tutor;
grant select on AssessmentItem to u3952239_CourseManagers;

-- Part 1
grant select on AssignmentSummary to u3952239_Student;
grant select on AssignmentSummary to u3952239_Tutor;
grant select on AssignmentSummary to u3952239_CourseManagers;

-- Part 2
grant select on Student_Student to u3952239_Student;
grant select on Student_Mark to u3952239_Student;

-- Part 3
grant select on Tutor_Student to u3952239_Tutor;
grant insert, update, delete, select on Tutor_Mark to u3952239_Tutor;
grant select on MarkSummary to u3952239_Tutor;

-- Part 4
grant delete, update, insert on Tutorial to u3952239_CourseManagers;
grant select, delete, update, insert on Student to u3952239_CourseManagers;
grant select, delete, update, insert on AssessmentItem to u3952239_CourseManagers;
grant select, delete, update, insert on Mark to u3952239_CourseManagers;
grant select on FinalMark to U3952239_CourseManagers;