SET search_path TO 'TPNOTE';

DROP TABLE IF EXISTS Lecture;
DROP TABLE IF EXISTS Possède;
DROP TABLE IF EXISTS Critique;
DROP TABLE IF EXISTS Utilisateur;
DROP TABLE IF EXISTS Livre;

CREATE TABLE Livre (
idLivre int NOT NULL PRIMARY KEY,
isbn text NOT NULL UNIQUE,
titre text NOT NULL,
auteur text NOT NULL);

INSERT INTO Livre VALUES
(1, 'abcd', 'Peter Pan', 'Nicolas'),
(2, 'efgh', 'Pinocchio', 'Henri'),
(3, 'ijkl', 'Zelda', 'Thomas'),
(4, 'mnop', 'A ne pas lire', 'Ingrid'),
(5, 'qrst', 'A ne voir', 'Ingrid');

CREATE TABLE Utilisateur (
login text NOT NULL PRIMARY KEY,
mdp text NOT NULL,
email text NOT NULL UNIQUE CHECK (email LIKE '%@%'));

INSERT INTO Utilisateur VALUES
('Mario', 'mushroom', 'Mario@hotmail.fr'),
('Peach', 'princesse', 'Peach@hotmail.fr'),
('Link', 'mastersword', 'Link@hotmail.fr');

CREATE TABLE Critique (
idLivre int NOT NULL REFERENCES Livre(idLivre),
login text NOT NULL REFERENCES Utilisateur(login),
note int NOT NULL,
texte text NOT NULL CHECK (LENGTH(texte)>=10),
PRIMARY KEY (idLivre, login));

INSERT INTO Critique VALUES
(3, 'Link', 10, 'j adore le hero'),
(3, 'Mario', 6, 'je n aime pas beaucoup la princesse'),
(1, 'Peach', 7, 'se lit bien'),
(1, 'Mario', 8, 'le hero est cool'),
(2, 'Peach', 4, 'decevant comme livre');

CREATE TABLE Possède (
idLivre int NOT NULL REFERENCES Livre(idLivre),
login text NOT NULL REFERENCES Utilisateur(login),
PRIMARY KEY (idLivre, login));

INSERT INTO Possède VALUES
(1, 'Mario'),
(3, 'Mario'),
(1, 'Peach'),
(2, 'Peach'),
(3, 'Link');

CREATE TABLE Lecture (
idLivre int NOT NULL REFERENCES Livre(idLivre),
login text NOT NULL REFERENCES Utilisateur(login),
idLecture int NOT NULL,
dateLecture date NOT NULL,
PRIMARY KEY (idLivre, login, idLecture));

INSERT INTO Lecture VALUES
(1, 'Mario', 1, '2016-4-1'),
(3, 'Mario', 2, '2015-10-1'),
(1, 'Peach', 3, '2016-9-4'),
(2, 'Peach', 4, '2014-1-9'),
(3, 'Link', 5, '2016-4-20'),
(1, 'Mario', 6, '2016-12-1'),
(2, 'Peach', 7, '2014-12-20');

SET search_path TO 'TPNOTE';

/*
4.a

SELECT DISTINCT idLivre, titre, auteur FROM Livre
JOIN Lecture USING (idLivre)
WHERE login='Mario';
*/

/*
4.b

SELECT DISTINCT titre FROM Livre
JOIN Lecture USING (idLivre)
WHERE login='Mario'
AND dateLecture >= '2016-1-1'
AND dateLecture < '2017-1-1';
*/

/*
4.c

SELECT idLivre, titre, avg(note) AS MoyenneLivre FROM Livre
JOIN Critique USING (idLivre)
GROUP BY idLivre
order by MoyenneLivre DESC;
*/

/*
4.d

SELECT auteur, count(*) AS Nblecture FROM Livre
JOIN Lecture USING (idLivre)
GROUP BY idLivre;
*/

/*
4.e

SELECT idLivre, titre FROM Livre
JOIN Lecture USING (idLivre)
WHERE dateLecture >= '2014-1-1'
AND dateLecture < '2015-1-1'
GROUP BY idLivre
HAVING count(*)>=2
*/

/*
4.f

SELECT idLivre, titre FROM Livre
WHERE idLivre NOT IN ( select idLivre from Lecture)
*/