--
-- Le schéma relationel
--

CREATE TABLE PRODUIT (
   ID INTEGER PRIMARY KEY,
   NOM TEXT NOT NULL,
   PRIX FLOAT NOT NULL
);

CREATE TABLE CLIENT (
   ID INTEGER PRIMARY KEY,
   NOM TEXT NOT NULL,
   TYPE TEXT CHECK (TYPE="PRO" OR TYPE="PART")
);

CREATE TABLE COMMANDE (
  ID INTEGER PRIMARY KEY,
  FRAIS FLOAT,
  ADRESSE TEXT,
  ID_CLIENT INTEGER,
  FOREIGN KEY (ID_CLIENT) REFERENCES CLIENT(ID)
);

CREATE TABLE CONTENU (
  ID_COMMANDE INTEGER,
  ID_PRODUIT INTEGER,
  QUANTITE INT,
  PRIMARY KEY (ID_COMMANDE,ID_PRODUIT)
  FOREIGN KEY (ID_COMMANDE) REFERENCES COMMANDE(ID),
  FOREIGN KEY (ID_PRODUIT) REFERENCES PRODUIT(ID)
);

CREATE TABLE HISTORIQUE (
  ID_COMMANDE INTEGER,
  ETAT TEXT NOT NULL,
  DATE TIMESTAMP NOT NULL,
  PRIMARY KEY (ID_COMMANDE,ETAT),
  FOREIGN KEY (ID_COMMANDE) REFERENCES COMMANDE(ID),
  CHECK (ETAT='PLACEE' OR 
         ETAT='PAYEE' OR 
         ETAT='TRAITEE' OR
         ETAT='EXPEDIEE' OR 
         ETAT='RECUE' OR
         ETAT='RETOURNEE' OR 
         ETAT='ANNULEE' OR
         ETAT='REMBOURSEE') 
);

CREATE TABLE AVIS (
  ID_PRODUIT INTEGER,
  ID_CLIENT INTEGER,
  DATE DATE,
  AVIS TEXT,
  NOTE INTEGER CHECK (NOTE >= 1 AND NOTE <= 5),
  PRIMARY KEY (ID_PRODUIT,ID_CLIENT),
  FOREIGN KEY (ID_PRODUIT) REFERENCES PRODUIT(ID),
  FOREIGN KEY (ID_CLIENT) REFERENCES CLIENT(ID)
);

--
-- Les vues
--

-- Une vue qui affiche le montant total de la commande et l'état curant de la commande
CREATE VIEW COMMANDE_INFO AS 
SELECT COMMANDE.ID, 
       COMMANDE.ADRESSE, 
       COMMANDE.FRAIS+(
         SELECT SUM(PRODUIT.PRIX*CONTENU.QUANTITE)
         FROM PRODUIT, CONTENU
         WHERE PRODUIT.ID = CONTENU.ID_PRODUIT
           AND CONTENU.ID_COMMANDE = COMMANDE.ID
       ) AS PRIX_TOTAL,
       (SELECT ETAT 
        FROM HISTORIQUE
        WHERE ID_COMMANDE = COMMANDE.ID
          AND DATE = (SELECT MAX(DATE) FROM HISTORIQUE WHERE ID_COMMANDE = COMMANDE.ID)
       ) AS ETAT
FROM COMMANDE;


-- Une vue qui etend l'information des produit avec l'avis moyen 
-- et un chiffre de ventes (nombre total d'articles vendus). 
CREATE VIEW PRODUIT_INFO AS 
SELECT P.*, 
       (SELECT AVG(A.NOTE) 
        FROM AVIS A
        WHERE A.ID_PRODUIT = P.ID
       ) AS NOTE_MOYENNE, 
       (SELECT SUM(C.QUANTITE)
        FROM COMMANDE CMD, CONTENU C, HISTORIQUE H
        WHERE C.ID_COMMANDE = CMD.ID
          AND H.ID_COMMANDE = CMD.ID
          AND C.ID_PRODUIT = P.ID
          AND H.ETAT = 'RECUE'
          AND NOT EXISTS (
                SELECT * 
                FROM HISTORIQUE HH
                WHERE HH.ID_COMMANDE = H.ID_COMMANDE
                  AND (HH.ETAT = 'RETOURNEE' OR 
                       HH.ETAT = 'ANNULEE' OR 
                       HH.ETAT = 'REMBOURSEE')
              )
       ) AS CHIFFRE_VENTES
FROM PRODUIT P;


--
-- Les triggers
--

-- On peut deposer un avis uniquement sur un produit qu'on a acheté et
-- reçu.
CREATE TRIGGER avis_sur_produit_achete BEFORE INSERT ON AVIS 
WHEN (
   SELECT COUNT(*)
   FROM COMMANDE, CONTENU, HISTORIQUE
   WHERE COMMANDE.ID = CONTENU.ID_COMMANDE
     AND COMMANDE.ID_CLIENT = NEW.ID_CLIENT
     AND CONTENU.ID_PRODUIT = NEW.ID_PRODUIT
     AND HISTORIQUE.ID_COMMANDE = COMMANDE.ID
     AND HISTORIQUE.ETAT = 'RECUE'
) = 0
BEGIN
  SELECT RAISE(ABORT,'Avis sur articles achetes uniquement');
END; 

-- L'avancement de l'état d'une commande est :
-- PLACEE -+-> PAYEE -+-> TRAITEE -+-> EXPEDIEE ---> RECUE ---> RETOURNEE -+-> REMBOURSEE
--         |          |            |                                       |
--         +----------+------------+-> ANNULEE --------------------------->+


CREATE TRIGGER avancement_etat BEFORE INSERT ON HISTORIQUE
WHEN NOT ( 
   (NEW.ETAT = 'PLACEE' AND
    (SELECT ETAT FROM COMMANDE_INFO WHERE ID = NEW.ID_COMMANDE) IS NULL) OR
   (NEW.ETAT = 'PAYEE' AND
    (SELECT ETAT FROM COMMANDE_INFO WHERE ID = NEW.ID_COMMANDE) = 'PLACEE') OR
   (NEW.ETAT = 'TRAITEE' AND
    (SELECT ETAT FROM COMMANDE_INFO WHERE ID = NEW.ID_COMMANDE) = 'PAYEE') OR
   (NEW.ETAT = 'EXPEDIEE' AND
    (SELECT ETAT FROM COMMANDE_INFO WHERE ID = NEW.ID_COMMANDE) = 'TRAITEE') OR
   (NEW.ETAT = 'RECUE' AND
    (SELECT ETAT FROM COMMANDE_INFO WHERE ID = NEW.ID_COMMANDE) = 'EXPEDIEE') OR
   (NEW.ETAT = 'RETOURNEE' AND
    (SELECT ETAT FROM COMMANDE_INFO WHERE ID = NEW.ID_COMMANDE) = 'RECUE') OR
   (NEW.ETAT = 'REMBOURSEE' AND
    (SELECT ETAT FROM COMMANDE_INFO WHERE ID = NEW.ID_COMMANDE) IN ('RETOURNEE','ANNULEE')) OR
   (NEW.ETAT = 'ANNULEE' AND
    (SELECT ETAT FROM COMMANDE_INFO WHERE ID = NEW.ID_COMMANDE) IN ('PLACEE','PAYEE','TRAITEE'))
)
BEGIN
   SELECT RAISE(ABORT, 'L''avancement incorrecte de l''etat de commande');
END;

--
-- Les données
--
INSERT INTO PRODUIT VALUES (1,'Velo',150);
INSERT INTO PRODUIT VALUES (2,'Voiture',25550);
INSERT INTO PRODUIT VALUES (3,'Avion',100000);

INSERT INTO CLIENT VALUES (1,'Air France','PRO');
INSERT INTO CLIENT VALUES (2,'H. Dubois','PART');

INSERT INTO COMMANDE VALUES (1,1000,'Aeroport CDG',1);
INSERT INTO COMMANDE VALUES (2,5000,'Aeroport CDG',1);
INSERT INTO COMMANDE VALUES (3,50,'Lille',2);

INSERT INTO CONTENU VALUES (1,3,1);
INSERT INTO CONTENU VALUES (1,1,1);
INSERT INTO CONTENU VALUES (2,3,5);
INSERT INTO CONTENU VALUES (2,2,1);
INSERT INTO CONTENU VALUES (3,1,1);

INSERT INTO HISTORIQUE VALUES (1,'PLACEE','2011-01-12 15:30:30');
INSERT INTO HISTORIQUE VALUES (1,'PAYEE','2011-01-12 16:00:23');
INSERT INTO HISTORIQUE VALUES (1,'TRAITEE','2011-01-14 17:00:23');
INSERT INTO HISTORIQUE VALUES (1,'EXPEDIEE','2011-01-16 12:00:23');
INSERT INTO HISTORIQUE VALUES (1,'RECUE','2011-01-21 12:00:23');
INSERT INTO HISTORIQUE VALUES (1,'RETOURNEE','2012-11-26 17:00:23');
INSERT INTO HISTORIQUE VALUES (1,'REMBOURSEE','2012-11-30 17:00:23');
INSERT INTO HISTORIQUE VALUES (2,'PLACEE','2012-01-12 15:30:30');
INSERT INTO HISTORIQUE VALUES (2,'PAYEE','2012-01-12 16:00:23');
INSERT INTO HISTORIQUE VALUES (2,'TRAITEE','2012-01-14 17:00:23');
INSERT INTO HISTORIQUE VALUES (2,'EXPEDIEE','2012-10-15 17:00:23');
INSERT INTO HISTORIQUE VALUES (2,'RECUE','2012-10-26 17:00:23');

INSERT INTO HISTORIQUE VALUES (3,'PLACEE','2012-10-12 15:30:30');

INSERT INTO AVIS VALUES (3,1,'2012-01-12 15:30:30','Il marche assez bien',4);
INSERT INTO AVIS VALUES (1,1,'2012-10-12 15:30:30','Ca roule',5);
-- INSERT INTO AVIS VALUES (1,2,'2012-10-12 15:30:30','Ca va',4);
-- INSERT INTO AVIS VALUES (1,3,'2012-10-12 15:30:30','Ca va',4);

--.header on
--.mode column

