DROP SCHEMA IF EXISTS lecture8 CASCADE;
CREATE SCHEMA lecture8;
SET SCHEMA 'lecture8';

CREATE TABLE Tracks (
  trackId INT PRIMARY KEY,
  title varchar(50),
  length INT
);

INSERT INTO Tracks(trackId, title, length) VALUES (1, 'Title1', 5);
INSERT INTO Tracks(trackId, title, length) VALUES (2, 'Title2', 3);
INSERT INTO Tracks(trackId, title, length) VALUES (3, 'Title3', 7);
INSERT INTO Tracks(trackId, title, length) VALUES (4, 'Title4', 4);
INSERT INTO Tracks(trackId, title, length) VALUES (5, 'Title5', 7);
INSERT INTO Tracks(trackId, title, length) VALUES (6, 'Title6', 7);
INSERT INTO Tracks(trackId, title, length) VALUES (7, 'Title7', 4);
INSERT INTO Tracks(trackId, title, length) VALUES (8, 'Title8', 5);

CREATE TABLE Artists (
  artistId INT PRIMARY KEY,
  name varchar(50)
);

INSERT INTO Artists(artistId, name) VALUES (1, 'Artist1');
INSERT INTO Artists(artistId, name) VALUES (2, 'Artist2');
INSERT INTO Artists(artistId, name) VALUES (3, 'Artist3');
INSERT INTO Artists(artistId, name) VALUES (4, 'Artist4');
INSERT INTO Artists(artistId, name) VALUES (5, 'Artist5');
INSERT INTO Artists(artistId, name) VALUES (6, 'Artist6');

CREATE TABLE Participates (
  track INT,
  artist INT,
  FOREIGN KEY(track) REFERENCES Tracks(trackId),
  FOREIGN KEY(artist) REFERENCES Artists(artistId)  
);

INSERT INTO Participates(track, artist) VALUES (6, 1);
INSERT INTO Participates(track, artist) VALUES (1, 2);
INSERT INTO Participates(track, artist) VALUES (2, 4);
INSERT INTO Participates(track, artist) VALUES (3, 3);
INSERT INTO Participates(track, artist) VALUES (4, 5);
INSERT INTO Participates(track, artist) VALUES (5, 6);
INSERT INTO Participates(track, artist) VALUES (7, 1);
INSERT INTO Participates(track, artist) VALUES (8, 3);

CREATE TABLE Albums (
  albumId INT PRIMARY KEY,
  title varchar(50),
  yearReleased date
);

INSERT INTO Albums(albumId, title, yearReleased) VALUES (1, 'Title1', '1/1/2015');
INSERT INTO Albums(albumId, title, yearReleased) VALUES (2, 'Title2', '1/1/2016');
INSERT INTO Albums(albumId, title, yearReleased) VALUES (3, 'Title3', '1/1/2016');
INSERT INTO Albums(albumId, title, yearReleased) VALUES (4, 'Title4', '1/1/2014');
INSERT INTO Albums(albumId, title, yearReleased) VALUES (5, 'Title5', '1/1/2014');

CREATE TABLE TracksOnAlbum (
  track INT,
  album INT,
  trackNr INT,
  PRIMARY KEY(album, track),
  FOREIGN KEY(track) REFERENCES Tracks(trackId),
  FOREIGN KEY(album) REFERENCES Albums(albumId)
);

INSERT INTO TracksOnAlbum(track, album, trackNr) VALUES (1, 1 ,4);
INSERT INTO TracksOnAlbum(track, album, trackNr) VALUES (1, 2 ,3);
INSERT INTO TracksOnAlbum(track, album, trackNr) VALUES (2, 5 ,8);
INSERT INTO TracksOnAlbum(track, album, trackNr) VALUES (3, 3 ,10);
INSERT INTO TracksOnAlbum(track, album, trackNr) VALUES (4, 2 ,12);
INSERT INTO TracksOnAlbum(track, album, trackNr) VALUES (6, 1 ,111);
INSERT INTO TracksOnAlbum(track, album, trackNr) VALUES (8, 4 ,13);
INSERT INTO TracksOnAlbum(track, album, trackNr) VALUES (2, 3 ,130);
INSERT INTO TracksOnAlbum(track, album, trackNr) VALUES (5, 3 ,130);

CREATE TABLE Users (
  email varchar(50),
  username varchar(50) PRIMARY KEY,
  name varchar(50),
  UNIQUE (email)
);

INSERT INTO Users(username, email, name) VALUES ('username1', 'email1', 'name1');
INSERT INTO Users(username, email, name) VALUES ('username2', 'email2', 'name2');
INSERT INTO Users(username, email, name) VALUES ('username3', 'email3', 'name3');
INSERT INTO Users(username, email, name) VALUES ('username4', 'email4', 'name4');
INSERT INTO Users(username, email, name) VALUES ('username5', 'email5', 'name5');
INSERT INTO Users(username, email, name) VALUES ('username6', 'email6', 'name6');

CREATE TABLE Playlists (
  username varchar(50) REFERENCES Users,
  playlistName varchar(50),
  PRIMARY KEY (username, playlistName)
);

INSERT INTO Playlists(username, playlistName) VALUES ('username1', 'playlistName1');
INSERT INTO Playlists(username, playlistName) VALUES ('username1', 'playlistName2');
INSERT INTO Playlists(username, playlistName) VALUES ('username2', 'playlistName1');
INSERT INTO Playlists(username, playlistName) VALUES ('username2', 'playlistName2');
INSERT INTO Playlists(username, playlistName) VALUES ('username3', 'playlistName1');
INSERT INTO Playlists(username, playlistName) VALUES ('username3', 'playlistName2');
INSERT INTO Playlists(username, playlistName) VALUES ('username3', 'playlistName3');
INSERT INTO Playlists(username, playlistName) VALUES ('username4', 'playlistName1');
INSERT INTO Playlists(username, playlistName) VALUES ('username4', 'playlistName2');
INSERT INTO Playlists(username, playlistName) VALUES ('username4', 'playlistName3');
INSERT INTO Playlists(username, playlistName) VALUES ('username5', 'playlistName1');
INSERT INTO Playlists(username, playlistName) VALUES ('username5', 'playlistName2');
INSERT INTO Playlists(username, playlistName) VALUES ('username5', 'playlistName3');
INSERT INTO Playlists(username, playlistName) VALUES ('username6', 'playlistName1');



CREATE TABLE InList (
  username varchar(50),
  playlistName varchar(50),
  number INT,
  track INT,
  FOREIGN KEY(track) REFERENCES Tracks(trackId),
  FOREIGN KEY(username,playlistName) REFERENCES Playlists,
  PRIMARY KEY (username, playlistName, track)
);

INSERT INTO InList(username, playlistName, number, track) VALUES ('username5', 'playlistName3', 5, 1);
INSERT INTO InList(username, playlistName, number, track) VALUES ('username2', 'playlistName2', 4, 2);
INSERT INTO InList(username, playlistName, number, track) VALUES ('username3', 'playlistName3', 3, 3);
INSERT INTO InList(username, playlistName, number, track) VALUES ('username3', 'playlistName2', 2, 4);
INSERT INTO InList(username, playlistName, number, track) VALUES ('username4', 'playlistName1', 1, 5);
INSERT INTO InList(username, playlistName, number, track) VALUES ('username5', 'playlistName1', 5, 6);
INSERT INTO InList(username, playlistName, number, track) VALUES ('username6', 'playlistName1', 57, 6);
INSERT INTO InList(username, playlistName, number, track) VALUES ('username5', 'playlistName1', 52, 7);
INSERT INTO InList(username, playlistName, number, track) VALUES ('username1', 'playlistName1', 15, 8);
INSERT INTO InList(username, playlistName, number, track) VALUES ('username5', 'playlistName2', 35, 1);



CREATE TABLE PlayLog (
  track INT,
  username varchar(50),
  time date,
  UNIQUE (username, time),
  PRIMARY KEY(username,time),
  FOREIGN KEY(track) REFERENCES Tracks,
  FOREIGN KEY(username) REFERENCES Users  
);

INSERT INTO PlayLog(track, username, time) VALUES (6, 'username1','10/1/98');
INSERT INTO PlayLog(track, username, time) VALUES (1, 'username6','9/3/97');
INSERT INTO PlayLog(track, username, time) VALUES (3, 'username2','11/1/96');
INSERT INTO PlayLog(track, username, time) VALUES (4, 'username1','2/7/98');
INSERT INTO PlayLog(track, username, time) VALUES (5, 'username3','4/9/95');
INSERT INTO PlayLog(track, username, time) VALUES (1, 'username4','3/12/96');
INSERT INTO PlayLog(track, username, time) VALUES (5, 'username3','5/2/97');
INSERT INTO PlayLog(track, username, time) VALUES (6, 'username2','7/3/98');
INSERT INTO PlayLog(track, username, time) VALUES (6, 'username2','7/3/99');