CREATE TABLE Type_Mission(
   Id_Type_Mission INT,
   Designation_TMission VARCHAR(50),
   PRIMARY KEY(Id_Type_Mission)
);

CREATE TABLE Type_Site(
   Id_Type_Site INT,
   Designation_TSte VARCHAR(50),
   PRIMARY KEY(Id_Type_Site)
);

CREATE TABLE Type_Reservation(
   Id_Type_Reservation INT,
   Designation_T_Reservation VARCHAR(50),
   PRIMARY KEY(Id_Type_Reservation)
);

CREATE TABLE Type_Transport(
   Id_Type_Transport INT,
   Designation_TTransport VARCHAR(50),
   PRIMARY KEY(Id_Type_Transport)
);

CREATE TABLE Poste(
   Id_Poste INT,
   Nom_Poste VARCHAR(50),
   PRIMARY KEY(Id_Poste)
);

CREATE TABLE TypeFrais(
   IdTypeFrais INT,
   DesignatioTfrais VARCHAR(50),
   PRIMARY KEY(IdTypeFrais)
);

CREATE TABLE Modele(
   IdModele INT,
   NomModele VARCHAR(50),
   PRIMARY KEY(IdModele)
);

CREATE TABLE Mission(
   Id_Mission INT,
   date_Debut DATETIME,
   Date_Fin DATETIME,
   Objectif VARCHAR(50),
   Id_Type_Mission INT NOT NULL,
   PRIMARY KEY(Id_Mission),
   FOREIGN KEY(Id_Type_Mission) REFERENCES Type_Mission(Id_Type_Mission)
);

CREATE TABLE Voiture(
   Id_Voiture INT,
   Immatriculation VARCHAR(50),
   IdModele INT NOT NULL,
   PRIMARY KEY(Id_Voiture),
   FOREIGN KEY(IdModele) REFERENCES Modele(IdModele)
);

CREATE TABLE Site(
   Id_Site INT,
   Nom_Site VARCHAR(50),
   Adresse_Site VARCHAR(50),
   Id_Type_Site INT NOT NULL,
   PRIMARY KEY(Id_Site),
   FOREIGN KEY(Id_Type_Site) REFERENCES Type_Site(Id_Type_Site)
);

CREATE TABLE Service(
   Id_Site INT,
   Id_Service INT,
   Designation_Service VARCHAR(50),
   PRIMARY KEY(Id_Site, Id_Service),
   FOREIGN KEY(Id_Site) REFERENCES Site(Id_Site)
);

CREATE TABLE Voiture_service(
   Id_Voiture INT,
   PRIMARY KEY(Id_Voiture),
   FOREIGN KEY(Id_Voiture) REFERENCES Voiture(Id_Voiture)
);

CREATE TABLE Employe(
   IdEmploye INT,
   Matricule CHAR(15) NOT NULL,
   Nom VARCHAR(50),
   Prenom VARCHAR(50),
   Id_Poste INT NOT NULL,
   Id_Site INT NOT NULL,
   Id_Service INT NOT NULL,
   PRIMARY KEY(IdEmploye),
   UNIQUE(Matricule),
   FOREIGN KEY(Id_Poste) REFERENCES Poste(Id_Poste),
   FOREIGN KEY(Id_Site, Id_Service) REFERENCES Service(Id_Site, Id_Service)
);

CREATE TABLE Demande_Reservation(
   Id_Mission INT,
   Id_Reservation INT,
   date_debut_Reservation DATE,
   Date_Fin_Reservation DATE,
   Nombre_De_Nuits INT,
   Id_Site INT NOT NULL,
   Id_Type_Reservation INT NOT NULL,
   PRIMARY KEY(Id_Mission, Id_Reservation),
   FOREIGN KEY(Id_Mission) REFERENCES Mission(Id_Mission),
   FOREIGN KEY(Id_Site) REFERENCES Site(Id_Site),
   FOREIGN KEY(Id_Type_Reservation) REFERENCES Type_Reservation(Id_Type_Reservation)
);

CREATE TABLE EmployeMission(
   Id_Mission INT,
   IdEmploye INT,
   PRIMARY KEY(Id_Mission, IdEmploye),
   FOREIGN KEY(Id_Mission) REFERENCES Mission(Id_Mission),
   FOREIGN KEY(IdEmploye) REFERENCES Employe(IdEmploye)
);

CREATE TABLE Chauffeur(
   IdEmploye INT,
   PRIMARY KEY(IdEmploye),
   FOREIGN KEY(IdEmploye) REFERENCES Employe(IdEmploye)
);

CREATE TABLE chauffeur_occasionel(
   IdEmploye INT,
   PRIMARY KEY(IdEmploye),
   FOREIGN KEY(IdEmploye) REFERENCES Chauffeur(IdEmploye)
);

CREATE TABLE Chauffeur_Ste(
   IdEmploye INT,
   PRIMARY KEY(IdEmploye),
   FOREIGN KEY(IdEmploye) REFERENCES Chauffeur(IdEmploye)
);

CREATE TABLE Voiture_Personnel(
   Id_Voiture INT,
   IdEmploye INT NOT NULL,
   PRIMARY KEY(Id_Voiture),
   UNIQUE(IdEmploye),
   FOREIGN KEY(Id_Voiture) REFERENCES Voiture(Id_Voiture),
   FOREIGN KEY(IdEmploye) REFERENCES chauffeur_occasionel(IdEmploye)
);

CREATE TABLE Utiliser_Transport(
   Id_Mission INT,
   Id_Type_Transport INT,
   PRIMARY KEY(Id_Mission, Id_Type_Transport),
   FOREIGN KEY(Id_Mission) REFERENCES Mission(Id_Mission),
   FOREIGN KEY(Id_Type_Transport) REFERENCES Type_Transport(Id_Type_Transport)
);

CREATE TABLE Utliser_Voiture(
   Id_Mission INT,
   Id_Voiture INT NOT NULL,
   PRIMARY KEY(Id_Mission),
   FOREIGN KEY(Id_Mission) REFERENCES Mission(Id_Mission),
   FOREIGN KEY(Id_Voiture) REFERENCES Voiture(Id_Voiture)
);

CREATE TABLE Defrayer(
   Id_Mission INT,
   IdEmploye INT,
   IdTypeFrais INT,
   FraisMontant DOUBLE,
   PRIMARY KEY(Id_Mission, IdEmploye, IdTypeFrais),
   FOREIGN KEY(Id_Mission, IdEmploye) REFERENCES EmployeMission(Id_Mission, IdEmploye),
   FOREIGN KEY(IdTypeFrais) REFERENCES TypeFrais(IdTypeFrais)
);

CREATE TABLE Planifier(
   Id_Mission INT,
   Id_Site INT,
   PRIMARY KEY(Id_Mission, Id_Site),
   FOREIGN KEY(Id_Mission) REFERENCES Mission(Id_Mission),
   FOREIGN KEY(Id_Site) REFERENCES Site(Id_Site)
);

CREATE TABLE Affecter(
   IdEmploye INT,
   Id_Voiture INT,
   PRIMARY KEY(IdEmploye, Id_Voiture),
   FOREIGN KEY(IdEmploye) REFERENCES Chauffeur_Ste(IdEmploye),
   FOREIGN KEY(Id_Voiture) REFERENCES Voiture_service(Id_Voiture)
);