Digitalisation_EPA_Docker/sql/init-sql.sql

CREATE TABLE Ep(
   IdEP INT,
   IdCollaborateur VARCHAR(500) NOT NULL,
   IdReferent VARCHAR(500) NOT NULL,
   IdAgence INT,
   IdBU INT,
   Fonction VARCHAR(50),
   TypeEp VARCHAR(3) NOT NULL,
   NumeroEp INT,
   Obligatoire BOOL,
   Statut VARCHAR(50) NOT NULL,
   CV VARCHAR(50),
   DateCreation DATETIME NOT NULL,
   DatePrevisionnelle DATE,
   DateDisponibilite DATE,
   DateSaisie DATETIME,
   DateMiseAJour DATETIME,
   DateSignatureCollaborateur DATETIME,
   DateSignatureReferent DATETIME,
   DemandeEPI INT NOT NULL,
   AugmentationSalaire INT NOT NULL,
   DemandeDelegation INT NOT NULL,
   RdvEntretien INT NOT NULL,
   PRIMARY KEY(IdEP)
);

CREATE TABLE Engagement(
   IdEngagement BIGINT,
   Action VARCHAR(500) NOT NULL,
   Dispositif VARCHAR(50) NOT NULL,
   Modalite VARCHAR(50),
   DateLimite DATE,
   EtatEngagement INT NOT NULL,
   RaisonNonRealisable VARCHAR(50),
   Id_1 INT NOT NULL,
   PRIMARY KEY(Id),
   FOREIGN KEY(Id_1) REFERENCES Ep(IdEP)
   FOREIGN KEY(Id_1) REFERENCES Ep(EtatEngagement)
);

CREATE TABLE Note(
   Id INT,
   Titre VARCHAR(50),
   Texte VARCHAR(500),
   IdAuteur VARCHAR(50) NOT NULL,
   IdCollaborateur VARCHAR(500) NOT NULL,
   PRIMARY KEY(Id)
);

CREATE TABLE ParticipationEp(
   Id VARCHAR(50),
   IdPersonne VARCHAR(50),
   Id_1 INT NOT NULL,
   PRIMARY KEY(Id),
   FOREIGN KEY(Id_1) REFERENCES Ep(IdEP)
);

CREATE TABLE Theme(
   Id INT,
   Nom VARCHAR(50) NOT NULL,
   PRIMARY KEY(Id)
);

CREATE TABLE AugmentationSalaire(
   IdAugmentationSalaire INT,
   Augmentation DECIMAL(15,2) NOT NULL,
   PrimeMission DECIMAL(15,2),
   Message VARCHAR(50),
   Id_1 INT NOT NULL,
   PRIMARY KEY(IdAugmentationSalaire),
   UNIQUE(Id_1),
   FOREIGN KEY(Id_1) REFERENCES Ep(IdEP)
);

CREATE TABLE Champ(
   Id INT,
   Texte VARCHAR(100) NOT NULL,
   Section VARCHAR(100) NOT NULL,
   SousSection VARCHAR(50),
   Ordre VARCHAR(100) NOT NULL,
   TypeChamp INT NOT NULL,
   TypeSaisie VARCHAR(50) NOT NULL,
   PRIMARY KEY(Id)
);

CREATE TABLE Document(
   IdDocument INT,
   TypeDocument VARCHAR(50) NOT NULL,
   Id_1 INT NOT NULL,
   PRIMARY KEY(IdDocument),
   FOREIGN KEY(Id_1) REFERENCES Ep(IdEP)
);

CREATE TABLE DemandeDelegation(
   IdDemandeDelegation INT,
   IdReferent VARCHAR(500) NOT NULL,
   DateDemande DATETIME NOT NULL,
   RaisonDemande VARCHAR(50),
   Reponse BOOL,
   DateReponse DATETIME,
   RaisonRefus VARCHAR(500),
   EpIdEP INT NOT NULL,
   PRIMARY KEY(IdDemandeDelegation),
   UNIQUE(EpIdEP),
   FOREIGN KEY(EpIdEP) REFERENCES Ep(IdEP)
);

CREATE TABLE StatutFormation(
   IdStatutFormation INT,
   Libelle VARCHAR(50) NOT NULL,
   PRIMARY KEY(IdStatutFormation),
   UNIQUE(Libelle)
);

CREATE TABLE TypeEntretien(
   IdTypeEntretien INT,
   Libelle VARCHAR(50),
   PRIMARY KEY(IdTypeEntretien),
   UNIQUE(Libelle)
);

CREATE TABLE ObjectifPrecedent(
   IdObjectif INT,
   Libelle VARCHAR(50),
   Atteint VARCHAR(50),
   Commentaire VARCHAR(50),
   EpIdEP INT NOT NULL,
   PRIMARY KEY(IdObjectif),
   FOREIGN KEY(EpIdEP) REFERENCES Ep(IdEP)
);

CREATE TABLE Objectif(
   IdObjectif INT,
   Libelle VARCHAR(50),
   EpIdEP INT NOT NULL,
   PRIMARY KEY(Id),
   FOREIGN KEY(EpIdEP) REFERENCES Ep(IdEP)
);

CREATE TABLE OrigineFormation(
   IdOrigineFormation INT,
   Libelle VARCHAR(50) NOT NULL,
   PRIMARY KEY(IdOrigineFormation),
   UNIQUE(Libelle)
);

CREATE TABLE OrigineDemande(
   IdOrigineDemande INT,
   Libelle VARCHAR(50) NOT NULL,
   PRIMARY KEY(IdOrigineDemande),
   UNIQUE(Libelle)
);

CREATE TABLE TypeFormation(
   IdTypeFormation VARCHAR(50),
   Libelle VARCHAR(50) NOT NULL,
   PRIMARY KEY(IdTypeFormation),
   UNIQUE(Libelle)
);

CREATE TABLE ModeFormation(
   IdModeFormation INT,
   Libelle VARCHAR(50) NOT NULL,
   PRIMARY KEY(IdModeFormation),
   UNIQUE(Libelle)
);

CREATE TABLE DemandeEpI(
   IdDemandeEPI INT,
   IdCollaborateur VARCHAR(50) NOT NULL,
   IdReferent VARCHAR(50) NOT NULL,
   DateDemande DATETIME,
   RaisonRefus VARCHAR(50),
   DateReponse DATETIME,
   Etat INT,
   Reponse BOOL,
   EpIdEP INT,
   PRIMARY KEY(IdDemandeEPI),
   UNIQUE(EpIdEP),
   FOREIGN KEY(EpIdEP) REFERENCES Ep(IdEP)
);

CREATE TABLE CommentaireAssistante(
   IdCommentaireAssistant INT,
   idAssistante VARCHAR(50) NOT NULL,
   dateCommentaire DATETIME NOT NULL,
   commentaire VARCHAR(50) NOT NULL,
   EpIdEP INT NOT NULL,
   PRIMARY KEY(IdCommentaireAssistant),
   FOREIGN KEY(EpIdEP) REFERENCES Ep(IdEP)
);

CREATE TABLE RdvEntretien(
   IdRdvEntretien INT,
   DateEntretien DATETIME NOT NULL,
   EpIdEP INT NOT NULL,
   TypeEntretienIdTypeEntretien INT NOT NULL,
   EpIdEP INT NOT NULL,
   PRIMARY KEY(IdRdvEntretien),
   UNIQUE(EpIdEP),
   FOREIGN KEY(EpIdEP) REFERENCES Ep(IdEP),
   FOREIGN KEY(TypeEntretienIdTypeEntretien) REFERENCES TypeEntretien(IdTypeEntretien),
   FOREIGN KEY(EpIdEP) REFERENCES Ep(IdEP)
);

CREATE TABLE Formation(
   IdFormation INT,
   Intitule VARCHAR(50),
   DateDebut DATETIME,
   DateFin DATETIME,
   IdAgence INT NOT NULL,
   Heure INT,
   Jour INT NOT NULL,
   Organisme VARCHAR(50),
   EstCertifiee BOOL NOT NULL,
   EstRealisee BOOL NOT NULL,
   ModeFormationIdModeFormation INT NOT NULL,
   TypeFormationIdTypeFormation VARCHAR(50) NOT NULL,
   OrigineIdOrigineFormation INT NOT NULL,
   StatutIdStatutFormation INT NOT NULL,
   PRIMARY KEY(IdFormation),
   FOREIGN KEY(ModeFormationIdModeFormation) REFERENCES ModeFormation(IdModeFormation),
   FOREIGN KEY(TypeFormationIdTypeFormation) REFERENCES TypeFormation(IdTypeFormation),
   FOREIGN KEY(OrigineIdOrigineFormation) REFERENCES OrigineFormation(IdOrigineFormation),
   FOREIGN KEY(StatutIdStatutFormation) REFERENCES StatutFormation(IdStatutFormation)
);

CREATE TABLE DemandeFormation(
   IdDemandeFormation INT,
   Libelle VARCHAR(50) NOT NULL,
   Description VARCHAR(50),
   DemandeRH BOOL,
   DateDemande VARCHAR(50),
   Etat INT,
   CommentaireRefus VARCHAR(50),
   DateDerniereReponse DATETIME,
   OrigineDemandeIdOrigineDemande INT NOT NULL,
   EpIdEP INT NOT NULL,
   PRIMARY KEY(IdDemandeFormation),
   FOREIGN KEY(OrigineDemandeIdOrigineDemande) REFERENCES OrigineDemande(Id),
   FOREIGN KEY(EpIdEP) REFERENCES Ep(IdEP)
);

CREATE TABLE ParticipationFormation(
   IdParticipationFormation INT,
   DateCreation DATETIME NOT NULL,
   EstEvaluee BOOL NOT NULL,
   IdDemandeFormation INT NOT NULL,
   FormationIdFormation INT NOT NULL,
   PRIMARY KEY(IdParticipationFormation),
   UNIQUE(IdParticipationFormation),
   FOREIGN KEY(IdDemandeFormation) REFERENCES DemandeFormation(IdDemandeFormation),
   FOREIGN KEY(FormationIdFormation) REFERENCES Formation(IdFormation)
);

CREATE TABLE Saisie(
   IdSaisie INT,
   Note INT,
   Texte VARCHAR(50),
   Texte2 VARCHAR(50),
   Niveau VARCHAR(50),
   TypeSaisie INT NOT NULL,
   ParticipationFormationIdParticipationFormation INT,
   DocumentIdDocument INT,
   ChampIdChamp INT NOT NULL,
   PRIMARY KEY(IdSaisie),
   FOREIGN KEY(ParticipationFormationIdParticipationFormation) REFERENCES ParticipationFormation(IdParticipationFormation),
   FOREIGN KEY(DocumentIdDocument) REFERENCES Document(IdDocument),
   FOREIGN KEY(ChampIdChamp) REFERENCES Champ(IdChamp)
);

CREATE TABLE ChoisirTypeEntretien(
   IdChoixTypeEntretien INT,
   EpIdEP INT,
   Ordre INT NOT NULL,
   TypeEntretienIdTypeEntretien INT NOT NULL;
   PRIMARY KEY(IdChoixTypeEntretien, EpIdEP),
   FOREIGN KEY(EpIdEP) REFERENCES Ep(IdEP),
   FOREIGN KEY(TypeEntretienIdTypeEntretien) REFERENCES TypeEntretien(IdTypeEntretien)
);

ALTER TABLE Ep ADD FOREIGN KEY(DemandeEPIIdDemandeEPI) REFERENCES DemandeEpI(IdDemandeEPI);
ALTER TABLE Ep ADD FOREIGN KEY(AugmentationSalaireIdAugmentationSalaire) REFERENCES AugmentationSalaire(IdAugmentationSalaire);
ALTER TABLE Ep ADD FOREIGN KEY(DemandeDelegationIdDemandeDelegation) REFERENCES DemandeDelegation(IdDemandeDelegation);
ALTER TABLE Ep ADD FOREIGN KEY(RdvEntretienIdRdvEntretien) REFERENCES RdvEntretien(IdRdvEntretien);
ALTER TABLE Ep ADD FOREIGN KEY(DemandeDelegation) REFERENCES RdvEntretien(IdRdvEntretien);