CREATE SCHEMA TournoiTT;
SET search_path TO TournoiTT,public;

CREATE TABLE TournoiTT.Tournoi (
  Jo1_Licence integer NOT NULL, 
  Jo1_Nom text NOT NULL, 
  Jo1_Points integer NOT NULL, 
  Jo1_Ne_le date NOT NULL, 
  Jo1_Iso_pays char(3) NOT NULL, 
  Jo1_Pays text NOT NULL, 
  Jo2_Licence integer NOT NULL, 
  Jo2_Nom text NOT NULL, 
  Jo2_Points integer NOT NULL, 
  Jo2_Ne_le date NOT NULL, 
  Jo2_Iso_pays char(3) NOT NULL, 
  Jo2_Pays text NOT NULL, 
  Tableau char(1) NOT NULL, 
  Tab_Intitule text NOT NULL, 
  Tab_PointsMax integer NOT NULL, 
  Tab_Ne_apres date NOT NULL, 
  Jo1_Gagne boolean NOT NULL, 
  PRIMARY KEY (Jo1_Licence, Jo2_Licence, Tableau), 
  CONSTRAINT Jo1_age_points CHECK(Jo1_Ne_le > Tab_Ne_apres AND Jo1_Points < Tab_PointsMax), 
  CONSTRAINT Jo2_age_points CHECK(Jo2_Ne_le > Tab_Ne_apres AND Jo2_Points < Tab_PointsMax), 
  CONSTRAINT Jo1_Jo2 CHECK(Jo1_Licence < Jo2_Licence)
);
CREATE TABLE TournoiTT.ListePays (
  Iso_pays Char(3) PRIMARY KEY, 
  Pays text NOT NULL
);
CREATE TABLE TournoiTT.Licencies (
  Licence integer PRIMARY KEY, 
  Nom text NOT NULL, 
  Points integer NOT NULL, 
  Ne_le date NOT NULL, 
  Iso_pays char(3) REFERENCES TournoiTT.ListePays ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE TournoiTT.ListeTableaux (
  Tableau char(1) PRIMARY KEY, 
  Intitule text NOT NULL, 
  Points_max integer NOT NULL, 
  Ne_apres date NOT NULL
);
CREATE TABLE TournoiTT.Inscrits (
  Licence integer REFERENCES TournoiTT.Licencies ON DELETE CASCADE ON UPDATE CASCADE, 
  Tableau char(1) REFERENCES TournoiTT.ListeTableaux ON DELETE CASCADE ON UPDATE CASCADE, 
  PRIMARY KEY (Licence, Tableau)
);

-- PostgreSQL n'accepte pas les contraintes d'integrite avec un SELECT
-- Il faut definir une fonction
CREATE FUNCTION TournoiTT.Inscrits_Erreur(integer, char(1)) RETURNS boolean AS $$ 
SELECT NOT EXISTS (
  SELECT * 
  FROM TournoiTT.ListeTableaux, TournoiTT.Licencies 
  WHERE TournoiTT.ListeTableaux.Tableau=$2 
    AND TournoiTT.Licencies.Licence=$1 
    AND (Licencies.Points > ListeTableaux.Points_max OR 
         Licencies.Ne_le < ListeTableaux.Ne_apres)) 
$$ LANGUAGE SQL;
-- puis appeler la fonction dans une contrainte d'integrite
ALTER TABLE TournoiTT.Inscrits ADD CONSTRAINT Jo_Age_Points 
  CHECK(TournoiTT.Inscrits_Erreur(Licence, Tableau) = TRUE);
CREATE TABLE TournoiTT.Parties (
  Licence1 integer NOT NULL, 
  Licence2 integer NOT NULL, 
  Tableau char(1) NOT NULL, 
  Jo1_gagne boolean NOT NULL, 
  PRIMARY KEY (Licence1, Licence2, Tableau), 
  FOREIGN KEY (Licence1, Tableau) REFERENCES TournoiTT.Inscrits(Licence, Tableau) 
    ON DELETE CASCADE ON UPDATE CASCADE, 
  FOREIGN KEY (Licence2, Tableau) REFERENCES TournoiTT.Inscrits(Licence, Tableau) 
    ON DELETE CASCADE ON UPDATE CASCADE, 
  CONSTRAINT Jo1_Jo2 CHECK(Licence1 < Licence2)
);
CREATE VIEW TournoiTT.TournoiEquiv AS 
SELECT Jo1.Licence AS Jo1_Licence, 
       Jo1.Nom AS Jo1_Nom, 
       Jo1.Points AS Jo1_Points, 
       Jo1.Ne_le AS Jo1_Ne_le, 
       Jo1.Iso_pays AS Jo1_Iso_pays, 
       Jo1.Pays AS Jo1_Pays, 
       Jo2.Licence AS Jo2_Licence, 
       Jo2.Nom AS Jo2_Nom, 
       Jo2.Points AS Jo2_Points, 
       Jo2.Ne_le AS Jo2_Ne_le, 
       Jo2.Iso_pays AS Jo2_Iso_pays, 
       Jo2.Pays AS Jo2_Pays, 
       P.Tableau AS Tableau, 
       P.Intitule AS Tab_Intitule, 
       P.Points_max AS Tab_Points_max, 
       P.Ne_apres AS Tab_Ne_apres, 
       P.Jo1_gagne AS Jo1_Gagne 
FROM   (TournoiTT.Parties NATURAL JOIN TournoiTT.ListeTableaux) AS P, 
       (TournoiTT.Licencies NATURAL JOIN TournoiTT.ListePays) AS Jo1, 
       (TournoiTT.Licencies NATURAL JOIN TournoiTT.ListePays) AS Jo2 
WHERE  P.Licence1=Jo1.Licence AND P.Licence2=Jo2.licence;
