-- Connecting to Chalmers postgresql server:
-- psql -h ate.ita.chalmers.se -U <username>

-- Reverse order because GivenCourses depend on People and Courses
-- If we remove People or Courses first, the references in GivenCourses
-- become invalid, and psql complains.
DROP SCHEMA IF EXISTS lecture10 CASCADE;
CREATE SCHEMA lecture10;
SET SCHEMA 'lecture10';

-- three tables: People, Courses and GivenCourses
CREATE TABLE People (
    ssn 		INTEGER,
    name 		TEXT 		NOT NULL,
    PRIMARY KEY (ssn)
);

CREATE TABLE Courses (
    code 		CHAR(6),
    name 		TEXT 		NOT NULL,
    PRIMARY KEY (code)
);

CREATE TABLE GivenCourses (
    code 		CHAR(6) 	REFERENCES Courses,
    period 		INTEGER 	CHECK(period in (1,2,3,4)),
    studentcount 	INTEGER 	CHECK(studentcount >= 0) NOT NULL,
    teacherssn 		INTEGER 	REFERENCES People(ssn) NOT NULL,
    PRIMARY KEY (code, period)
);


-- Create some example people
INSERT INTO People (ssn, name) VALUES (1, 'name1');
INSERT INTO People (ssn, name) VALUES (2, 'name2');
INSERT INTO People (ssn, name) VALUES (3, 'name3');
INSERT INTO People (ssn, name) VALUES (4, 'name4');

-- Create some example courses
INSERT INTO Courses (code, name) VALUES ('TDA357', 'Databases');
INSERT INTO Courses (code, name) VALUES ('TIN090', 'Algorithms');

-- Create some example givencourses
INSERT INTO GivenCourses (code, period, studentcount, teacherssn) VALUES ('TDA357', 2, 199, 1);
INSERT INTO GivenCourses (code, period, studentcount, teacherssn) VALUES ('TDA357', 3, 75, 4);
INSERT INTO GivenCourses (code, period, studentcount, teacherssn) VALUES ('TIN090', 1, 120, 2);
INSERT INTO GivenCourses (code, period, studentcount, teacherssn) VALUES ('TIN090', 2, 151, 2);
INSERT INTO GivenCourses (code, period, studentcount, teacherssn) VALUES ('TIN090', 3, 103, 2);
INSERT INTO GivenCourses (code, period, studentcount, teacherssn) VALUES ('TIN090', 4, 3, 2);

-- example (cartesian or cross) product query
SELECT c.code, c.name, g.period, g.studentcount, p.ssn, p.name 
    FROM Courses c, GivenCourses g, People p 
    WHERE c.code = g.code AND g.teacherssn = p.ssn;

-- example delete query
--DELETE from GivenCourses WHERE studentcount < 20;

-- example update query
--UPDATE GivenCourses SET teacherssn = 3 WHERE period = 3;

-- example (cartesian or cross) product query (again, to see the changes)
--SELECT c.code, c.name, g.period, g.studentcount, p.ssn, p.name 
--    FROM Courses c, GivenCourses g, People p 
--    WHERE c.code = g.code AND g.teacherssn = p.ssn;

-- same query, but written with JOIN
--SELECT * 
--    FROM Courses JOIN GivenCourses ON Courses.code = GivenCourses.code
--    	JOIN People ON GivenCourses.teacherssn = People.ssn;

-- same query, but now also using NATURAL JOIN
--SELECT * 
--    FROM Courses NATURAL JOIN GivenCourses
--    	JOIN People ON GivenCourses.teacherssn = People.ssn;


-- Instead of temporarily creating a relation/table, we can also make it permanent as a view:
CREATE VIEW BetterPeople AS (SELECT ssn AS teacherssn, name AS teachername FROM People);
-- ... and then treat BetterPeople as any other table:
SELECT * from BetterPeople NATURAL JOIN GivenCourses;


-- Let's create a FullPicture VIEW which combines People, Courses and GivenCourses
CREATE VIEW FullPicture AS 
  SELECT code, name, period, studentcount, teacherssn, teachername
    FROM Courses NATURAL JOIN GivenCourses NATURAL JOIN BetterPeople;



CREATE TABLE Rooms(
    roomname VARCHAR(10),
    capacity INTEGER CHECK(capacity > 0) NOT NULL,
    campus TEXT NOT NULL,
    PRIMARY KEY(roomname)
);

CREATE TABLE Lectures(
    code CHAR(6),
    period INTEGER,
    roomname VARCHAR(10) REFERENCES Rooms,
    weekday TEXT CHECK(weekday IN ('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday')),
    FOREIGN KEY(code, period) REFERENCES GivenCourses(code, period),
    PRIMARY KEY(code, period, weekday)
);

INSERT INTO Rooms(roomname, capacity, campus) VALUES('HB2', 186, 'Johanneberg');
INSERT INTO Rooms(roomname, capacity, campus) VALUES('HC1', 105, 'Johanneberg');
INSERT INTO Rooms(roomname, capacity, campus) VALUES('HC2', 115, 'Johanneberg');
INSERT INTO Rooms(roomname, capacity, campus) VALUES('Jupiter44', 64, 'Lindholmen');
INSERT INTO Rooms(roomname, capacity, campus) VALUES('Svea239', 60, 'Lindholmen');
INSERT INTO Rooms(roomname, capacity, campus) VALUES('VR', 300, 'Neverland');

INSERT INTO Lectures(code, period, roomname, weekday) VALUES('TDA357', 2, 'HB2', 'Tuesday');
INSERT INTO Lectures(code, period, roomname, weekday) VALUES('TDA357', 2, 'HB2', 'Wednesday');
INSERT INTO Lectures(code, period, roomname, weekday) VALUES('TIN090', 2, 'Svea239', 'Friday');
INSERT INTO Lectures(code, period, roomname, weekday) VALUES('TDA357', 3, 'HC1', 'Monday');





------ IN/ANY/ALL/EXISTS/...
-- select all givencourses in either period 1 or 3
SELECT * FROM GivenCourses WHERE period IN(1,3);
SELECT * FROM GivenCourses WHERE period = ANY (ARRAY[1,3]);

-- select all entries from the courses table, if their 'code' matches a course in the lectures table
SELECT * FROM Courses WHERE EXISTS (SELECT * FROM Lectures WHERE Courses.code = Lectures.code);

-- select all rooms with a capacity that can fit each of the studentgroups listed in GivenCourses
SELECT * FROM Rooms WHERE capacity >= ALL(SELECT studentcount FROM GivenCourses);

----- arithmetic/constants
SELECT 1;
SELECT 1 + 1;
SELECT 'hello world' AS test;

-- find all lectures where rooms have at least 10 free seats
SELECT * FROM Lectures NATURAL JOIN Rooms NATURAL JOIN GivenCourses WHERE studentcount <= capacity - 10;

-- Beware of how SELECT operates! In this example, 'abc' is returned for every row in People
SELECT 'abc' FROM People;

----- String operators

-- Strings can also be compared using < > <= >= etc.
SELECT * FROM People WHERE name > 'Donald Duck';

-- the LIKE operator allows matching substrings
SELECT * FROM People WHERE name LIKE '%1%';

----- Aggregate functions. These are special since they work across multiple rows!

-- calculate the total capacity of all known rooms together
SELECT SUM(capacity) FROM Rooms;

-- calculate min, max and average amount of students over all given courses
SELECT MIN(studentcount), MAX(studentcount), AVG(studentcount) FROM GivenCourses;

-- Count the number of entries in People
SELECT COUNT(*) FROM People;

-- list all attributes of the room with the largest capacity
-- Note that we have to use  subquery here, because of the peculiar nature of the aggregate functions
SELECT * FROM Rooms WHERE capacity = (SELECT MAX(capacity) FROM Rooms);

-- for instance, this won't work:
--SELECT * FROM Rooms WHERE capacity = MAX(capacity);


CREATE TABLE BadRooms(
    roomname VARCHAR(10),
    capacity INTEGER CHECK(capacity > 0), -- capacity is nullable now!
    campus TEXT NOT NULL,
    PRIMARY KEY(roomname)
);

INSERT INTO BadRooms(roomname, capacity, campus) VALUES('A',  10, 'Johanneberg');
INSERT INTO BadRooms(roomname, capacity, campus) VALUES('B',  40, 'Johanneberg');
INSERT INTO BadRooms(roomname, capacity, campus) VALUES('C', 100, 'Johanneberg');
INSERT INTO BadRooms(roomname, capacity, campus) VALUES('D', NULL, 'Johanneberg');

-- select all rooms that have either more than 50 or less-or-equal than 50 capacity
-- logically, this should be all rooms, but this will not take any NULLs into account
SELECT * FROM BadRooms WHERE capacity > 50 OR capacity <= 50;

-- aggregate functions ignore NULL values. If calculated over all 4 rows, the average should (mathematically) be 37.5, not 50
SELECT AVG(capacity) FROM BadRooms;


-- To list the rooms with the largest capacity
SELECT roomname, capacity FROM Rooms WHERE capacity = (SELECT MAX(capacity) FROM Rooms);

-- To list the campus with largest combined capacity, we have to group by campus first
-- and aggregate per group
SELECT campus, SUM(capacity) FROM Rooms GROUP BY campus;

-- an alternative way:
SELECT DISTINCT campus, (SELECT SUM(capacity) FROM Rooms r2 WHERE r2.campus=r1.campus) FROM Rooms r1;

-- List all campuses with a capacity over 200
-- Note that we can't use WHERE here, because the filter uses an aggregate function
SELECT campus, SUM(capacity) FROM Rooms GROUP BY campus HAVING SUM(capacity) > 200;

-- List all rooms sorted by descending capacity
SELECT * FROM Rooms ORDER BY capacity DESC;

-- UNION between 2 relations with similar columns
-- Note that this UNION makes no sense, since we are mixing Rooms and People, yet SQL happily allows it
-- The first column is a text field, the second is a number
SELECT roomname, capacity FROM Rooms UNION SELECT name, ssn FROM People;


-- List all periods in which courses are given, except those in which lectures are given
-- This means: find all given courses that have no lectures
-- (This query removes all duplicates from both SELECTs before EXCEPT)
SELECT period FROM FullPicture EXCEPT SELECT period FROM Lectures;
-- (This query removes does not remove duplicates from either SELECTs before EXCEPT)
SELECT period FROM FullPicture EXCEPT ALL SELECT period FROM Lectures;

-- List all teachers that give a course, avoid duplicates
SELECT DISTINCT teachername FROM FullPicture;

-- Common idiom: List all rooms with the courses taught in them. If there is no course, list NULL
SELECT roomname, code FROM Lectures NATURAL JOIN Rooms 
	UNION 
	SELECT roomname, NULL FROM Rooms WHERE roomname NOT IN (SELECT roomname FROM Lectures);




-- Different types of joins, in this case all NATURAL
SELECT * FROM FullPicture NATURAL JOIN Lectures;
SELECT * FROM FullPicture NATURAL FULL OUTER JOIN Lectures;
SELECT * FROM FullPicture NATURAL LEFT OUTER JOIN Lectures;
SELECT * FROM FullPicture NATURAL RIGHT OUTER JOIN Lectures;

-- full outer join between 3 tables
SELECT * FROM FullPicture NATURAL FULL OUTER JOIN Lectures NATURAL FULL OUTER JOIN Rooms;

-- mirror all rooms to Neverland by using INSERT from a subquery
SELECT * FROM Rooms;
INSERT INTO Rooms (SELECT CONCAT('X', roomname), capacity, 'Neverland' FROM Rooms WHERE campus != 'Neverland');
SELECT * FROM Rooms;
-- remove them again to not disturb future uses of these SQL files
DELETE FROM Rooms WHERE roomname like 'X%';


DELETE FROM GivenCourses WHERE code = 'TDA357' and period = 4;
DELETE FROM GivenCourses WHERE code = 'TIN090' and period = 4;

CREATE TABLE ExamsCascade (
    code                CHAR(6),
    period              INTEGER,
    examdate        	DATE,
    FOREIGN KEY (code, period) REFERENCES GivenCourses(code, period)
    	ON DELETE CASCADE
	ON UPDATE CASCADE,
    PRIMARY KEY(code, period)
);

CREATE TABLE ExamsRestrict (
    code                CHAR(6),
    period              INTEGER,
    examdate        	DATE,
    FOREIGN KEY (code, period) REFERENCES GivenCourses(code, period)
    	ON DELETE RESTRICT,
    PRIMARY KEY(code, period)
);

SELECT * FROM GivenCourses;
---- create a new GivenCourse
INSERT INTO GivenCourses(code, period, studentcount, teacherssn) VALUES('TDA357', 4, 100, 1);
---- schedule an exam
INSERT INTO ExamsCascade(code, period, examdate) VALUES('TDA357', 4, DATE('1969-07-24'));
SELECT * FROM ExamsCascade;
---- update the givencourse and change TDA357 to TIN090, will also update the ExamsCascade table
UPDATE GivenCourses SET code='TIN090' WHERE code = 'TDA357' and period = 4;
SELECT * FROM ExamsCascade;
---- deleting the givencourses entry, will also delete from the ExamsCascade table
DELETE FROM GivenCourses WHERE code = 'TIN090' and period = 4;
SELECT * FROM ExamsCascade;


SELECT * FROM GivenCourses;
---- create a new GivenCourse
INSERT INTO GivenCourses(code, period, studentcount, teacherssn) VALUES('TDA357', 4, 100, 1);
---- schedule an exam
INSERT INTO ExamsRestrict(code, period, examdate) VALUES('TDA357', 4, DATE('1969-07-24'));
---- now delete the givencourse...
--DELETE FROM GivenCourses WHERE code = 'TDA357' and period = 4; -- This SQL statement results in an expected error!
---- because of "RESTRICT", the GivenCourse was not deleted, and neither was the ExamsRestrict entry
SELECT * FROM GivenCourses WHERE code = 'TDA357' and period = 4;
SELECT * FROM ExamsRestrict;

-- cleanup
DELETE FROM ExamsRestrict WHERE code = 'TDA357' and period = 4;
DELETE FROM GivenCourses WHERE code = 'TDA357' and period = 4;


SELECT 
	NOT EXISTS( 
		SELECT count(code), period, teachername 
		FROM fullpicture 
		GROUP BY period, teachername 
		HAVING count(code) > 1
		);

-- Make sure teacherssn does not teach in period 4, so we are not confused by the results later.
DELETE FROM GivenCourses WHERE teacherssn = 1 and period = 4;

CREATE OR REPLACE FUNCTION myFunction() RETURNS TRIGGER AS $$
BEGIN
	IF EXISTS ( 
		SELECT count(code), period, teacherssn 
		FROM fullpicture 
		WHERE teacherssn = NEW.teacherssn AND period = NEW.period
		GROUP BY period, teacherssn 
		HAVING count(code) > 0
	)
	    THEN RAISE EXCEPTION 'teachers should not hold more than 1 course!!';
	ELSE
	    RETURN NEW;
	END IF;
END
$$ LANGUAGE 'plpgsql';

-- Next, we can define a trigger on GivenCourses which will be executed before any INSERT,
-- calling the function myFunction on every row. 
CREATE TRIGGER myTrigger BEFORE INSERT ON GivenCourses 
    FOR EACH ROW
    EXECUTE PROCEDURE myFunction();
    

-- Now let's have someone teach TDA357 in period 4
INSERT INTO GivenCourses(code, period, studentcount, teacherssn) VALUES('TDA357', 4, 100, 1);
-- and have the same person teach TIN090 in period 4
-- This last insert will fail because it triggers the assertion that noone can teach more than 1 course per period
INSERT INTO GivenCourses(code, period, studentcount, teacherssn) VALUES('TIN090', 4, 100, 1); -- This SQL statement results in an expected error!

-- cleanup
DELETE FROM GivenCourses WHERE teacherssn = 1 and period = 4;







----
----
-- Triggers to update views
----
----

-- This stored procedure will handle insertions into the FullPicture view
-- Remember that FullPicture = Courses NATURAL JOIN GivenCourses NATURAL JOIN BetterPeople
CREATE OR REPLACE FUNCTION insertInFullpicture() RETURNS TRIGGER AS $$
BEGIN
    -- check if the teacher already exists with the same data
    IF NOT EXISTS(SELECT ssn FROM People WHERE ssn = NEW.teacherssn AND name = NEW.teachername)
    THEN
        INSERT INTO People(ssn, name) VALUES(NEW.teacherssn, NEW.teachername);
    END IF;

    -- check if the course already exists with the same data
    IF NOT EXISTS(SELECT code FROM Courses WHERE code = NEW.code AND name = NEW.name)
    THEN
        INSERT INTO Courses(code, name) VALUES(NEW.code, NEW.name);
    END IF;

    -- check if the givencourse already exists with the same data
    IF NOT EXISTS(SELECT code FROM GivenCourses WHERE code = NEW.code AND period = NEW.period AND studentcount = NEW.studentcount AND teacherssn = NEW.teacherssn)
    THEN
        INSERT INTO GivenCourses(code, period, studentcount, teacherssn) VALUES(NEW.code, NEW.period, NEW.studentcount, NEW.teacherssn);
    END IF;
    RETURN NEW;
END
$$ LANGUAGE 'plpgsql';

-- This trigger will call insertInFullpicture() INSTEAD OF an INSERT on FullPicture
CREATE TRIGGER onInsertFullPicture INSTEAD OF INSERT ON FullPicture 
    FOR EACH ROW
    EXECUTE PROCEDURE insertInFullpicture();

-- Now we can insert into the FullPicture view, thereby updating the underlying tables
INSERT INTO FullPicture(code, name, period, studentcount, teacherssn, teachername) VALUES('POK000', 'Pokemon Hunting', 3, 1000, 7, 'Mickey Mouse');

-- To verify, check all the underlying tables
SELECT * FROM People WHERE ssn = 7;
SELECT * FROM BetterPeople WHERE teacherssn = 7; -- BetterPeople is an updatable view on People
SELECT * FROM Courses WHERE code = 'POK000';
SELECT * FROM GivenCourses WHERE code = 'POK000';
SELECT * FROM FullPicture WHERE code = 'POK000';
