CREATE TABLE ETUDIANT (
   ID  INTEGER PRIMARY KEY,
   NOM TEXT NOT NULL,
   DdN DATE
);

CREATE TABLE SALLE (
   ID           INTEGER PRIMARY KEY,
   NUMERO       TEXT NOT NULL,
   CAPACITE     INTEGER NOT NULL,
   INFORMATIQUE BOOL -- Sale informatique ?
);

CREATE TABLE COURS (
  ID       INTEGER PRIMARY KEY,
  MATIERE  TEXT NOT NULL,
  ID_SALLE INTEGER,

  FOREIGN KEY (ID_SALLE) REFERENCES SALLE(ID)
);

CREATE TABLE INSCRIPTION (
  ID_ETUDIANT INTEGER,
  ID_COURS    INTEGER,
  NOTE        FLOAT,

  PRIMARY KEY (ID_ETUDIANT, ID_COURS),
  FOREIGN KEY (ID_ETUDIANT) REFERENCES ETUDIANT(ID),
  FOREIGN KEY (ID_COURS) REFERENCES COURS(ID)
);

/*

1. Remplir les tables avec des données (au minimum 2 étudiants, 2
cours, 1 sale). Ensuite écrire une instruction DELETE qui suprime tous
les étudiants nés après 1 décembre 1984 et une instruction UPDATE qui
ajoute "-INFO" au nom de toute salle informatique (par exemple la sale
informatique "B.205" devient "B.205-INFO"; Rappel: concatenation de 2
chaines de caracteres se fait avec l'operateur \verb+||+)
*/

INSERT INTO ETUDIANT VALUES (1,'Jean Dubois','1992-10-12');
INSERT INTO ETUDIANT VALUES (2,'Pierre Dupont','1983-01-23');

INSERT INTO SALLE VALUES (1,'B.115',20,0);
INSERT INTO SALLE VALUES (2,'A.116',10,1);

INSERT INTO COURS VALUES (1,'ADB',2);

INSERT INTO INSCRIPTION VALUES (2,1,17.0);

DELETE FROM ETUDIANT WHERE DdN > '1984-12-01';


UPDATE SALLE SET NUMERO = NUMERO || '-INFO'
             WHERE INFORMATIQUE == '1';

/*
2. Créer des vues suivantes : 

a) une vue contenant l'information sur les salles informatiques
   contentant pour chaque salle informatique une ligne avec son numéro
   et sa capacité.
*/

CREATE VIEW SALLE_INFO AS 
   SELECT ID, NUMERO, CAPACITE
   FROM SALLE
   WHERE INFORMATIQUE == '1';

/*

b) une vue synthèse avec les moyennes des notes des étudiants : pour
   chaque étudiant une ligne avec son nom et sa moyenne.
*/

CREATE VIEW MOYENNE_ETUDIANT AS 
   SELECT NOM, AVG(NOTE)
   FROM ETUDIANT, INSCRIPTION
   WHERE ETUDIANT.ID = INSCRIPTION.ID_ETUDIANT
   GROUP BY NOM;

/*

c) une vue avec les étudiants qui suivent des cours informatiques
   (cours qui se deroulent dans une salle informatique) : pour chaque
   étudiant qui suit un cours informatique une ligne avec son nom et
   sa date de naissance.
*/

CREATE VIEW ETUDIANT_INFO AS 
SELECT NOM, DdN 
FROM ETUDIANT, INSCRIPTION, COURS, SALLE
WHERE ETUDIANT.ID = INSCRIPTION.ID_ETUDIANT
  AND INSCRIPTION.ID_COURS = COURS.ID
  AND COURS.ID_SALLE = SALLE.ID
  AND SALLE.INFORMATIQUE == '1';

/*

d) une vue synthèse d'occupation des sales : pour chaque cours une
   ligne avec le numero de la salle, son numéro, sa capacité, le
   nombre d'étudiants inscrits.

*/
CREATE VIEW OCCUPATION_SALLE AS 
   SELECT COURS.ID AS ID_COURS, 
          SALLE.ID AS ID_SALLE, 
          SALLE.NUMERO AS NUMERO, 
          SALLE.CAPACITE AS CAPACITE, 
          COUNT(ID_ETUDIANT) AS INSCRITS
   FROM SALLE, INSCRIPTION, COURS
   WHERE COURS.ID_SALLE = SALLE.ID 
     AND INSCRIPTION.ID_COURS = COURS.ID
   GROUP BY COURS.ID, SALLE.ID, SALLE.NUMERO, SALLE.CAPACITE;

/*


3. Créer des triggers suivants : 

a) un ensemble des triggers qui enregistrent l'historique des
   changements (insertions du contenu de table ETUDIANT (avec la
   définition d'une table auxiliaire qui contiendra l'historique)

*/

CREATE TABLE ETUDIANT_HISTORIQUE (
  ID INTEGER,
  NOM TEXT,
  DdN DATE,
  TYPE_MODIF TEXT,
  DATE_MODIF TIMESTAMP
);

CREATE TRIGGER etudiant_historique_insert AFTER INSERT ON ETUDIANT FOR EACH ROW 
BEGIN
   INSERT INTO ETUDIANT_HISTORIQUE (ID,NOM,DdN,TYPE_MODIF,DATE_MODIF)
   VALUES (NEW.ID,NEW.NOM,NEW.DdN,"INSERT",datetime('now'));
END;

CREATE TRIGGER etudiant_historique_update AFTER UPDATE ON ETUDIANT FOR EACH ROW 
BEGIN
   INSERT INTO ETUDIANT_HISTORIQUE (ID,NOM,DdN,TYPE_MODIF,DATE_MODIF)
   VALUES (NEW.ID,NEW.NOM,NEW.DdN,"UPDATE",datetime('now'));
END;

CREATE TRIGGER etudiant_historique_delete AFTER DELETE ON ETUDIANT FOR EACH ROW 
BEGIN
   INSERT INTO ETUDIANT_HISTORIQUE (ID,NOM,DdN,TYPE_MODIF,DATE_MODIF)
   VALUES (OLD.ID,OLD.NOM,OLD.DdN,"DELETE",datetime('now'));
END;

/*
b) un trigger qui assurera la contrainte suivante : le nombre
   d'étudiants inscrits à un cours ne peut pas dépasser la capacité de
   la salle du cours.
*/

CREATE TRIGGER capacite_depassee 
BEFORE INSERT ON INSCRIPTION
WHEN (SELECT CAPACITE >= INSCRITS 
      FROM OCCUPATION_SALLE 
      WHERE ID_COURS == NEW.ID_COURS)
BEGIN
   SELECT RAISE(ABORT,'trop d''inscrits');
END;

/*
c) un trigger qui assurera la contrainte suivante : un étudiant peut
   suivre au maximum 3 cours informatiques.
*/

CREATE TRIGGER trois_cours_info_maximum BEFORE INSERT ON INSCRIPTION
WHEN
  (SELECT COUNT(*) FROM INSCRIPTION, COURS, SALLE
   WHERE INSCRIPTION.ID_ETUDIANT = NEW.ID_ETUDIANT
     AND INSCRIPTION.ID_COURS = COURS.ID
     AND COURS.ID_SALLE = SALLE.ID
     AND SALLE.INFORMATIQUE == '1') >= 3
BEGIN
   SELECT RAISE(ABORT,'trop de cours informatiques');
END;

/*
d) un trigger qui perpétrera d'ajouter de nouvelles salles
   informatiques à la vue de l'exercice 1.a.
*/

CREATE TRIGGER salle_info_insert 
   INSTEAD OF INSERT ON SALLE_INFO
   BEGIN
      INSERT INTO SALLE 
      VALUES (NEW.ID, NEW.NUMERO, NEW.CAPACITE, '1');
   END; 
