/*********
*
*	Créations de tables
*
*********/



DROP TABLE Evaluation;
DROP TABLE Covoiturage;
DROP TABLE voyage;
DROP TABLE Ville;
DROP TABLE Vehicule;
DROP TABLE Voyageur;
CREATE TABLE Voyageur (NVoyageur INTEGER PRIMARY KEY,  --Numero du voyageur
                       Nom VARCHAR(20),                --Le nom
                       Prenom VARCHAR(20),             --Le prenom
                       Sexe VARCHAR(1) CONSTRAINT sexe_voyageur CHECK (Sexe IN ('m', 'f') OR Sexe IS NULL),
                       Adresse VARCHAR(100),
                       CONSTRAINT nom_et_prenom_unique UNIQUE (Prenom, Nom)
                      );
CREATE TABLE Vehicule (NVehicule INTEGER PRIMARY KEY,
                       Conducteur INTEGER,
                       Immatricule VARCHAR(8),
                       place INTEGER,
                       type VARCHAR(15) CONSTRAINT type_de_vehicule CHECK (type IN ('Berline', 'Citadine', 'Utilitaire', '4*4', 'PicUp')),
                       FOREIGN KEY (Conducteur) REFERENCES voyageur (NVoyageur)
                       );
CREATE TABLE Ville (NVille INTEGER,
                    N_Ville VARCHAR(20),
                    CP INTEGER
                    );
CREATE TABLE VOYAGE ( Id INTEGER PRIMARY KEY,
                      IdV INTEGER,    --Code voiture
                      VilleS INTEGER,
                      VilleD INTEGER,
                      Jour DATE,
                      Place INTEGER,   --A mettre à jour par un trigger
                      CONSTRAINT conducteur_existe FOREIGN KEY (IdV) REFERENCES Vehicule (NVehicule)		
                     );
CREATE TABLE Covoiturage ( ID_cov INTEGER PRIMARY KEY,
                           Idv INTEGER,         --Code voyage
                           Idcvoyageur INTEGER, --Code voyageur
                           Passager INTEGER,    --Numero de passager (ajouter le conducteur)
                           CONSTRAINT enr_passager_une_fois_slment UNIQUE (Idv, Idcvoyageur, Passager),
                           CONSTRAINT Voyageur_doit_exister FOREIGN KEY (Idcvoyageur) REFERENCES Voyageur (NVoyageur),
                           CONSTRAINT Voyage_doit_exister FOREIGN KEY (Idv) REFERENCES Voyage (Id)
                         );
CREATE TABLE Evaluation (Idev INTEGER PRIMARY KEY,  --id evaluation
                         Idv_e INTEGER,             --Numero de voyage
                         Idvoyageur1 INTEGER,       --Voyageur qui donne la note
                         Idvoyageur2 INTEGER,       --Voyageur qui prend la note
                         Note INTEGER,
                         Commantaire VARCHAR(200),
                         CONSTRAINT une_eval_par_voyage UNIQUE (Idv_e,Idvoyageur1,Idvoyageur2),
                         CONSTRAINT Voyage_eval_doit_exister FOREIGN KEY (Idv_e) REFERENCES Covoiturage (ID_cov)
                         );
COMMIT;
