
DROP SCHEMA public CASCADE;
CREATE SCHEMA public;
GRANT ALL ON SCHEMA public TO postgres;
\set QUIET false


-- Ex 1

CREATE TABLE Artists (
  name TEXT PRIMARY KEY );
  
CREATE TABLE Songs (
  title TEXT,
  length INT NOT NULL,
  artistName TEXT,
  PRIMARY KEY (title, artistName),
  FOREIGN KEY (artistName) REFERENCES Artists(name) );
 
CREATE TABLE Playlists (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  owner TEXT NOT NULL );
  
CREATE TABLE PlaylistSongs (
  playlist TEXT NOT NULL REFERENCES Playlists (id),
  song TEXT,
  artist TEXT,
  position INT NOT NULL,
  FOREIGN KEY (song, artist) REFERENCES Songs (title, artistName),
-- DEFERRABLE here means it will be checked after several actions took place
-- Needed for the trigger
  PRIMARY KEY (playlist, position) DEFERRABLE );


-- Some values
INSERT INTO Artists VALUES ('ABBA');
INSERT INTO Artists VALUES ('Pink Floyd');
INSERT INTO Artists VALUES ('Roxette');
INSERT INTO Artists VALUES ('John Lundvik');
INSERT INTO Songs VALUES ('Dancing Queen', 4, 'ABBA');
INSERT INTO Songs VALUES ('It must have been love', 3, 'Roxette');
INSERT INTO Songs VALUES ('Too late for love', 5, 'John Lundvik');
INSERT INTO Songs VALUES ('Animals', 4, 'Pink Floyd');
INSERT INTO Playlists VALUES ('M123', 'My Favourites', 'Ana');
INSERT INTO Playlists VALUES ('XX', 'Other', 'Ana');
INSERT INTO PlaylistSongs VALUES ('M123', 'Dancing Queen', 'ABBA', 1);
INSERT INTO PlaylistSongs VALUES ('M123', 'Too late for love', 'John Lundvik', 2);
INSERT INTO PlaylistSongs VALUES ('XX','It must have been love', 'Roxette', 1);


-- Ex 1.2
CREATE OR REPLACE VIEW SongsInM123 AS
SELECT position, song, artist, length
FROM PlaylistSongs JOIN Songs ON (song = title AND artist = artistName)
WHERE playlist = 'M123'
ORDER BY position;

SELECT * FROM SongsInM123;


-- Ex 1.3
/*
Recall that even if the type would be serial it doesn't guarantee the order,
so one needs a trigger with the following behavior

if newPos >= maxPos + 1                         (or newPos > maxPos)
   then we will insert in (maxPos+1)
if newPos < maxPos + 1                          (or newPos <= maxPos) 
   then we need to shift by 1 all positions >= newPos 
        and then insert in newPos
*/

CREATE OR REPLACE FUNCTION insertInM123 () RETURNS TRIGGER AS $$
DECLARE maxPos INT;
BEGIN
  maxPos := (SELECT MAX(position) FROM PlaylistSongs
             WHERE playlist = 'M123');
  IF (NEW.position > maxPos)
  THEN INSERT INTO PlaylistSongs
       VALUES ('M123', NEW.song, NEW.artist, maxPos + 1);
  ELSE -- if not with "DEFERRABLE" above this would be a problem since when
       -- doing position = position + 1 we might be using an existing position
       UPDATE PlaylistSongs
       SET position = position + 1
       WHERE position >= NEW.position AND playlist = 'M123';
       -- we now insert
       INSERT INTO PlaylistSongs
       VALUES ('M123', NEW.song, NEW.artist, NEW.position);
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;


CREATE TRIGGER insertPlayList
INSTEAD OF INSERT ON SongsInM123
FOR EACH ROW
EXECUTE FUNCTION insertInM123 ();


-- will get place 3
INSERT INTO SongsInM123 VALUES (8, 'Animals', 'Pink Floyd', 7);

SELECT * FROM SongsInM123;

-- This insert will move songs from positions 2 and 3 to positions 3 and 4 resp.
-- Without the DEFERRABLE this insert will give an error since
-- moving the song in position 2 to position 3
-- will put 2 songs in the same position (at least momentarely)
INSERT INTO SongsInM123 VALUES (2, 'It must have been love', 'Roxette', 7);

SELECT * FROM SongsInM123;
