CREATE TABLE Categorie
(
   CID      serial,
   Nom       varchar(25) NOT NULL,
   CONSTRAINT UN_Categorie UNIQUE (Nom),
   CONSTRAINT PK_Categorie PRIMARY KEY (CID)
);

CREATE TABLE Produit 
(
   PID         serial,
   Nom         varchar(25) NOT NULL,
   Marque      varchar(25) NOT NULL,
   CONSTRAINT UN_Produit UNIQUE (Nom,Marque),
   Score       integer,
   CONSTRAINT CK_Produit_Score CHECK (Score >=0 AND Score <=100),
   NbCalories  integer,
   CONSTRAINT CK_Produit_NbCalories CHECK (NbCalories >=0),
   NbAdditifs  integer,
   CONSTRAINT CK_Produit_NbAdditifs CHECK (NbAdditifs >=0),
   CID        integer NOT NULL,
   CONSTRAINT PK_Produit PRIMARY KEY (PID),
   CONSTRAINT "FK_Produit_CID"
      FOREIGN KEY (CID)
        REFERENCES Categorie (CID)
          ON UPDATE RESTRICT ON DELETE RESTRICT
);

CREATE TABLE Additif
(
   AID     serial,
   Nom     varchar(25) NOT NULL,
   Niveau  varchar(25)  NOT NULL,
   CONSTRAINT PK_Additif PRIMARY KEY (AID),
   CONSTRAINT CK_Additif_Niveau CHECK (Niveau IN ('sans risque', 'faible', 'moyen', 'fort'))
);

CREATE TABLE Contient
(
   PID     integer NOT NULL,
   AID     integer NOT NULL,
   CONSTRAINT PK_Contient PRIMARY KEY (PID,AID),
   CONSTRAINT "FK_Contient_PID"
      FOREIGN KEY (PID)
        REFERENCES Produit (PID)
          ON UPDATE RESTRICT ON DELETE RESTRICT,
   CONSTRAINT "FK_Contient_AID"
      FOREIGN KEY (AID)
        REFERENCES Additif (AID)
          ON UPDATE RESTRICT ON DELETE RESTRICT
);

CREATE OR REPLACE FUNCTION MiseAJourAdd() RETURNS trigger AS
'
DECLARE
   n int;
   BEGIN
   SELECT INTO n COUNT(*) 
      FROM Produit p NATURAL JOIN Contient a
        WHERE NEW.PID = p.PID; 
 
    UPDATE Produit SET NbAdditifs = n WHERE Produit.PID = NEW.PID;
    RETURN NEW;
	  
   END;
'
LANGUAGE 'plpgsql';

CREATE TRIGGER AjoutAdditif
AFTER INSERT ON Contient
FOR EACH ROW
EXECUTE PROCEDURE MiseAJourAdd(); 

                                       
INSERT INTO Additif(Nom,Niveau) VALUES ('Diphosphates', 'fort');
INSERT INTO Additif(Nom,Niveau) VALUES ('Acide citrique','sans risque');
INSERT INTO Additif(Nom,Niveau) VALUES ('Glycerol', 'moyen');

INSERT INTO Categorie(Nom) VALUES ('cereales');
INSERT INTO Categorie(Nom) VALUES ('yahourts');
INSERT INTO Categorie(Nom) VALUES ('gateaux');
INSERT INTO Categorie(Nom) VALUES ('boissons');

INSERT INTO Produit(Nom,Marque,Score,NbCalories,CID) 
VALUES ('Muesli', 'Marque1', 95, 230, (SELECT CID FROM Categorie WHERE NOM='cereales'));

INSERT INTO Produit(Nom,Marque,Score,NbCalories,CID)  
VALUES ('jus d''orange', 'MonMagasin', 75, 60, (SELECT CID FROM Categorie WHERE NOM='boissons')); 

INSERT INTO Produit(Nom,Marque,Score,NbCalories,CID)  
VALUES ('Bon choco', 'St Jacques', 4, 1500, (SELECT CID FROM Categorie WHERE NOM='gateaux')); 

INSERT INTO Contient VALUES ((SELECT PID FROM Produit WHERE Nom='Bon choco'),(SELECT AID FROM Additif WHERE Nom = 'Diphosphates'));

INSERT INTO Contient VALUES ((SELECT PID FROM Produit WHERE Nom='Bon choco'),(SELECT AID FROM Additif WHERE Nom = 'Glycerol'));

INSERT INTO Produit(Nom,Marque,Score,NbCalories,CID)  
VALUES ('Bon choco2', 'St Jacques', 45, 1500, (SELECT CID FROM Categorie WHERE NOM='gateaux')); 
                                               
INSERT INTO Produit(Nom,Marque,Score,NbCalories,CID)  
VALUES ('Bon choco3', 'St Jacques', 55, 1500, (SELECT CID FROM Categorie WHERE NOM='gateaux')); 

INSERT INTO Contient VALUES ((SELECT PID FROM Produit WHERE Nom='Bon choco2'),(SELECT AID FROM Additif WHERE Nom = 'Glycerol'));

--a) Quels sont les additifs (en donnant leur nom) contenus dans un produit x ?
SELECT a.Nom 
 FROM Additif a, Contient c, Produit p
  WHERE a.AID=c.AID AND p.PID=c.PID AND p.Nom = 'Bon choco';

--b) Quel est le nombre de produits par catégorie ? (en affichant toutes les catégories - y compris celles sans produit)
SELECT c.Nom, COUNT(PID) As NbProduits
 FROM Categorie c LEFT OUTER JOIN Produit p ON c.CID=p.CID
  GROUP BY c.Nom;


--c) Quels est le nombre moyen d’additifs par catégorie de produits ?
SELECT c.Nom, AVG(p.NbAdditifs) As Moyenne
 FROM Categorie c LEFT OUTER JOIN Produit p ON c.CID=p.CID
  GROUP BY c.Nom;

-- d) Quels produits (en donnant leur nom) contiennent au moins 2 additifs ? (Écrire 2 requêtes SQL, une requête sans GROUP BY et une requête avec GROUP BY)
SELECT DISTINCT p.Nom 
 FROM Additif a1, Contient c1, Additif a2, Contient c2, Produit p
  WHERE a1.AID=c1.AID AND p.PID=c1.PID AND a2.AID=c2.AID AND p.PID=c2.PID;

SELECT p.Nom 
 FROM Additif a, Contient c, Produit p
  WHERE a.AID=c.AID AND p.PID=c.PID
   GROUP BY p.NOM HAVING COUNT(a.AID) >=2;

--e) Quels produits (en donnant leur nom) ne contiennent aucun additif de risque fort ?
SELECT p.Nom 
 FROM Additif a, Contient c, Produit p
  WHERE a.AID=c.AID AND p.PID=c.PID AND a.AID NOT IN (SELECT AID FROM Additif WHERE niveau='fort');

--f) Quel additif apparaît dans tous les produits ?
SELECT a.Nom FROM Additif a WHERE NOT EXISTS
   (SELECT * FROM Produit p WHERE NOT EXISTS
      (SELECT * FROM Contient c WHERE c.AID = a.AID AND p.PID = c.PID)
   );

---g) g.	Créer une vue permettant de compter le nombre d’additifs de niveau fort ou moyen de chaque produit (en donnant l’identifiant et le nom du produit).
CREATE VIEW nbAdditifsRisques AS
SELECT p.PID, p.Nom, COUNT(a.AID) AS nbAdRisques
 FROM Additif a, Contient c, Produit p  
   WHERE a.AID=c.AID AND p.PID=c.PID 
    AND niveau IN ('moyen','fort')
GROUP BY p.PID, p.Nom;

--h) Quels sont les produits (en donnant leur nom) alternatifs d’un produit x, i.e. les produits de la même catégorie, ayant un meilleur score que x mais moins d’additifs à risque fort ou moyen que x ? ? (utiliser la vue précédente et ordonner les produits alternatifs par score, du plus grand score au plus faible)
SELECT DISTINCT p1.Nom, p1.Score 
 FROM Produit p1, Produit p2
  WHERE p2.Nom = 'Bon choco' AND p2.CID = p1.CID AND p1.Score > p2.Score AND 
     (NOT EXISTS( SELECT * FROM Contient c WHERE c.PID = p1.PID) OR 
     ((SELECT nbAdRisques FROM nbAdditifsRisques ar1 WHERE ar1.PID =p1.PID) 
  < (SELECT nbAdRisques FROM nbAdditifsRisques ar2 WHERE ar2.PID =p2.PID))) 
ORDER BY p1.Score DESC;

--i) Quelle est la qualité de chaque produit (en donnant son nom), où qualité est « Excellent » si le score du produit est >80, « Bon » si le score est compris entre 80 et 50, « Médiocre » s’il est compris entre 50 et 30 et « Mauvais » si le score est < 30.
SELECT Nom,
CASE WHEN Score > 80 THEN 'Excellent'
     WHEN Score > 50 AND Score <= 80 THEN 'Bon'
     WHEN Score > 30 AND Score <= 50 THEN 'Mediocre'
     WHEN Score <= 30 THEN 'Mauvais'
END AS Qualité
FROM Produit;

