CREATE TABLE `consolidations`
(
 `id`          int(11) unsigned NOT NULL AUTO_INCREMENT ,
 `nom`         varchar(50) NOT NULL ,
 `description` varchar(255) NOT NULL ,

PRIMARY KEY (`id`)
);

CREATE TABLE `statuts`
(
 `id`   int(11) unsigned NOT NULL AUTO_INCREMENT ,
 `nom`  varchar(45) NOT NULL ,
 `icon` varchar(45) NOT NULL ,

PRIMARY KEY (`id`)
);

CREATE TABLE `structures`
(
 `id`               int(11) unsigned NOT NULL AUTO_INCREMENT ,
 `nom`              varchar(255) NOT NULL ,
 `description`      varchar(255) NULL ,
 `adresse`          varchar(255) NULL ,
 `logo`             varchar(255) NULL ,
 `consolidation_id` int(11) unsigned NULL ,

PRIMARY KEY (`id`),
KEY `fkIdx_284` (`consolidation_id`),
CONSTRAINT `FK_284` FOREIGN KEY `fkIdx_284` (`consolidation_id`) REFERENCES `consolidations` (`id`)
);

CREATE TABLE `paats`
(
 `id`                        int(11) unsigned NOT NULL AUTO_INCREMENT ,
 `statut`                    enum('initialisation','en_cours','cloture','soumis') NOT NULL DEFAULT 'initialisation',
 `description`               varchar(255) NOT NULL DEFAULT '0' ,
 `annee_reference`           varchar(4) NOT NULL DEFAULT '0' ,
 `date_debut`                date NOT NULL ,
 `date_fin`                  date NOT NULL ,
 `commentaire_soumission`    varchar(255) NULL DEFAULT NULL ,
 `structure_id`              int(11) unsigned NOT NULL ,

PRIMARY KEY (`id`),
KEY `fkIdx_539` (`structure_id`),
CONSTRAINT `FK_539` FOREIGN KEY `fkIdx_539` (`structure_id`) REFERENCES `structures` (`id`)
);

CREATE TABLE `thematiques`
(
 `id`           int(11) unsigned NOT NULL AUTO_INCREMENT ,
 `nom`          varchar(255) NOT NULL ,
 `description`  varchar(255) NULL ,
 `structure_id` int(11) unsigned NOT NULL ,

PRIMARY KEY (`id`),
KEY `fkIdx_372` (`structure_id`),
CONSTRAINT `FK_372` FOREIGN KEY `fkIdx_372` (`structure_id`) REFERENCES `structures` (`id`)
);

CREATE TABLE `actions`
(
 `id`                     int(11) unsigned NOT NULL AUTO_INCREMENT ,
 `nom`                    varchar(255) NOT NULL ,
 `description`            varchar(255) NULL ,
 `visible`                tinyint(1) NOT NULL DEFAULT false ,
 `thematique_id`          int(11) unsigned NOT NULL ,

PRIMARY KEY (`id`),
KEY `fkIdx_383` (`thematique_id`),
CONSTRAINT `FK_383` FOREIGN KEY `fkIdx_383` (`thematique_id`) REFERENCES `thematiques` (`id`)
);

CREATE TABLE `familles`
(
 `id`           int(11) unsigned NOT NULL AUTO_INCREMENT ,
 `nom`          varchar(255) NOT NULL ,
 `description`  varchar(255) NULL ,
 `structure_id` int(11) unsigned NOT NULL ,

PRIMARY KEY (`id`),
KEY `fkIdx_348` (`structure_id`),
CONSTRAINT `FK_348` FOREIGN KEY `fkIdx_348` (`structure_id`) REFERENCES `structures` (`id`)
);

CREATE TABLE `domaines`
(
 `id`          int(11) unsigned NOT NULL AUTO_INCREMENT ,
 `nom`         varchar(255) NOT NULL ,
 `description` varchar(255) NULL ,
 `famille_id`  int(11) unsigned NOT NULL ,

PRIMARY KEY (`id`),
KEY `fkIdx_333` (`famille_id`),
CONSTRAINT `FK_333` FOREIGN KEY `fkIdx_333` (`famille_id`) REFERENCES `familles` (`id`)
);

CREATE TABLE `categories`
(
 `id`          int(11) unsigned NOT NULL AUTO_INCREMENT ,
 `nom`         varchar(255) NOT NULL ,
 `description` varchar(255) NULL ,
 `domaine_id`  int(11) unsigned NOT NULL ,

PRIMARY KEY (`id`),
KEY `fkIdx_340` (`domaine_id`),
CONSTRAINT `FK_340` FOREIGN KEY `fkIdx_340` (`domaine_id`) REFERENCES `domaines` (`id`)
);

CREATE TABLE `segments`
(
 `id`           int(11) unsigned NOT NULL AUTO_INCREMENT ,
 `nom`          varchar(255) NOT NULL ,
 `description`  varchar(255) NULL ,
 `categorie_id` int(11) unsigned NOT NULL ,

PRIMARY KEY (`id`),
KEY `fkIdx_345` (`categorie_id`),
CONSTRAINT `FK_345` FOREIGN KEY `fkIdx_345` (`categorie_id`) REFERENCES `categories` (`id`)
);

CREATE TABLE `leviers`
(
 `id`           int(11) unsigned NOT NULL AUTO_INCREMENT ,
 `nom`          varchar(255) NOT NULL ,
 `description`  varchar(255) NULL ,
 `structure_id` int(11) unsigned NOT NULL ,

PRIMARY KEY (`id`),
KEY `fkIdx_364` (`structure_id`),
CONSTRAINT `FK_364` FOREIGN KEY `fkIdx_364` (`structure_id`) REFERENCES `structures` (`id`)
);

CREATE TABLE `directions`
(
 `id`           int(11) unsigned NOT NULL AUTO_INCREMENT ,
 `nom`          varchar(255) NOT NULL ,
 `description`  varchar(255) NULL ,
 `structure_id` int(11) unsigned NOT NULL ,

PRIMARY KEY (`id`),
KEY `fkIdx_310` (`structure_id`),
CONSTRAINT `FK_310` FOREIGN KEY `fkIdx_310` (`structure_id`) REFERENCES `structures` (`id`)
);

CREATE TABLE `services`
(
 `id`           int(11) unsigned NOT NULL AUTO_INCREMENT ,
 `nom`          varchar(255) NOT NULL ,
 `description`  varchar(255) NULL ,
 `direction_id` int(11) unsigned NOT NULL ,

PRIMARY KEY (`id`),
KEY `fkIdx_313` (`direction_id`),
CONSTRAINT `FK_313` FOREIGN KEY `fkIdx_313` (`direction_id`) REFERENCES `directions` (`id`)
);

CREATE TABLE `portefeuilles_responsables_achats`
(
 `id`           int(11) unsigned NOT NULL AUTO_INCREMENT ,
 `nom`          varchar(255) NOT NULL ,
 `description`  varchar(255) NULL ,
 `structure_id` int(11) unsigned NOT NULL ,

PRIMARY KEY (`id`),
KEY `fkIdx_404` (`structure_id`),
CONSTRAINT `FK_404` FOREIGN KEY `fkIdx_404` (`structure_id`) REFERENCES `structures` (`id`)
);

CREATE TABLE `portefeuilles_acheteurs`
(
 `id`                                int(11) unsigned NOT NULL AUTO_INCREMENT ,
 `nom`                               varchar(255) NOT NULL ,
 `description`                       varchar(255) NULL ,
 `portefeuille_responsable_achat_id` int(11) unsigned NOT NULL ,

PRIMARY KEY (`id`),
KEY `fkIdx_407` (`portefeuille_responsable_achat_id`),
CONSTRAINT `FK_407` FOREIGN KEY `fkIdx_407` (`portefeuille_responsable_achat_id`) REFERENCES `portefeuilles_responsables_achats` (`id`)
);

CREATE TABLE `users`
(
 `id`                                int(11) unsigned NOT NULL AUTO_INCREMENT ,
 `nom`                               varchar(45) NOT NULL ,
 `prenom`                            varchar(45) NOT NULL ,
 `email`                             varchar(255) NOT NULL ,
 `password`                          varchar(255) NOT NULL ,
 `last_login`                        datetime NULL DEFAULT NULL ,
 `active`                            tinyint(1) unsigned NULL DEFAULT 1 ,
 `structure_id`                      int(11) unsigned NOT NULL ,
 `direction_id`                      int(11) unsigned NULL ,
 `service_id`                        int(11) unsigned NULL ,
 `portefeuille_responsable_achat_id` int(11) unsigned NULL ,
 `portefeuille_acheteur_id`          int(11) unsigned NULL ,

PRIMARY KEY (`id`),
KEY `fkIdx_281` (`structure_id`),
CONSTRAINT `FK_281` FOREIGN KEY `fkIdx_281` (`structure_id`) REFERENCES `structures` (`id`),
KEY `fkIdx_302` (`service_id`),
CONSTRAINT `FK_302` FOREIGN KEY `fkIdx_302` (`service_id`) REFERENCES `services` (`id`),
KEY `fkIdx_316` (`direction_id`),
CONSTRAINT `FK_316` FOREIGN KEY `fkIdx_316` (`direction_id`) REFERENCES `directions` (`id`),
KEY `fkIdx_701` (`portefeuille_responsable_achat_id`),
CONSTRAINT `FK_701` FOREIGN KEY `fkIdx_701` (`portefeuille_responsable_achat_id`) REFERENCES `portefeuilles_responsables_achats` (`id`),
KEY `fkIdx_704` (`portefeuille_acheteur_id`),
CONSTRAINT `FK_704` FOREIGN KEY `fkIdx_704` (`portefeuille_acheteur_id`) REFERENCES `portefeuilles_acheteurs` (`id`)
);

CREATE TABLE `niveaux_mutualisations`
(
 `id`           int(11) unsigned NOT NULL AUTO_INCREMENT ,
 `nom`          varchar(255) NOT NULL ,
 `description`  varchar(255) NULL ,
 `structure_id` int(11) unsigned NOT NULL ,

PRIMARY KEY (`id`),
KEY `fkIdx_415` (`structure_id`),
CONSTRAINT `FK_415` FOREIGN KEY `fkIdx_415` (`structure_id`) REFERENCES `structures` (`id`)
);

CREATE TABLE `operateurs_mutualisations`
(
 `id`           int(11) unsigned NOT NULL AUTO_INCREMENT ,
 `nom`          varchar(255) NOT NULL ,
 `description`  varchar(255) NULL ,
 `structure_id` int(11) unsigned NOT NULL ,

PRIMARY KEY (`id`),
KEY `fkIdx_423` (`structure_id`),
CONSTRAINT `FK_423` FOREIGN KEY `fkIdx_423` (`structure_id`) REFERENCES `structures` (`id`)
);

CREATE TABLE `imputations_budgetaires`
(
 `id`           int(11) unsigned NOT NULL AUTO_INCREMENT ,
 `nom`          varchar(255) NOT NULL ,
 `description`  varchar(255) NULL ,
 `structure_id` int(11) unsigned NOT NULL ,

PRIMARY KEY (`id`),
KEY `fkIdx_356` (`structure_id`),
CONSTRAINT `FK_356` FOREIGN KEY `fkIdx_356` (`structure_id`) REFERENCES `structures` (`id`)
);

CREATE TABLE `alertes`
(
 `id`         int(11) unsigned NOT NULL AUTO_INCREMENT ,
 `critere`    enum('gains_securises', 'montant_reference', 'gains_notifies') NOT NULL ,
 `formule`    enum('<', '>', '=', '<=', '>=') NOT NULL ,
 `indicateur` int NOT NULL ,
 `user_id`    int(11) unsigned NOT NULL ,

PRIMARY KEY (`id`),
KEY `fkIdx_491` (`user_id`),
CONSTRAINT `FK_491` FOREIGN KEY `fkIdx_491` (`user_id`) REFERENCES `users` (`id`)
);

CREATE TABLE `projets`
(
 `id`                          int(11) unsigned NOT NULL AUTO_INCREMENT ,
 `reference`                   varchar(45) NULL ,
 `finess`                      int(11) NULL ,
 `description`                 text NULL ,
 `objet`                       varchar(255) NOT NULL ,
 `date_debut`                  date NOT NULL ,
 `date_fin`                    date NOT NULL ,
 `date_mise_en_oeuvre`         date NULL ,
 `code_nomenclature`           varchar(45) NULL ,
 `libelle_marche_a_renouveler` varchar(255) NULL ,
 `numero_marche_a_renouveler`  varchar(255) NULL ,
 `libelle_nouveau_marche`      varchar(255) NULL ,
 `numero_nouveau_marche`       varchar(255) NULL ,
 `programmation`               tinyint(1) NOT NULL ,
 `ondam`                       tinyint(1) NOT NULL ,
 `montant_reference`           int(11) NULL ,
 `montant_notifie`             int(11) NULL ,
 `montant_realise`             int(11) NULL ,
 `is_achat_complexe`           tinyint(1) NOT NULL ,
 `impact_budgetaire`           decimal(5, 2) NOT NULL DEFAULT 0,
 `pourcentage_gains_cible`     decimal(5,2) NULL ,
 `gains_cible`                 int(11) NULL ,
 `gains_notifie`               int(11) NULL ,
 `gains_securise`              int(11) NULL ,
 `duree_mois`                  int(11) NULL ,
 `commentaire`                 text NULL ,
 `programme`                   tinyint(1) NOT NULL DEFAULT 0,
 `soumis`                      tinyint(1) NOT NULL DEFAULT 0,
 `prochaine_echeance`          date NULL ,
 `prochaine_etape`             text NULL ,
 `etat_avancement`             text NULL ,
 `cloture`                     tinyint(1) NOT NULL DEFAULT 0,
 `valide_controleur_gestion`   tinyint(1) NOT NULL DEFAULT 0,
 `risque`                      enum('maitrise', 'retard', 'retard_important') NOT NULL DEFAULT 'maitrise',
 `paat_id`                     int(11) unsigned NOT NULL ,
 `owner_id`                    int(11) unsigned NOT NULL ,
 `structure_id`                int(11) unsigned NOT NULL ,
 `direction_id`                int(11) unsigned NOT NULL ,
 `service_id`                  int(11) unsigned NOT NULL ,
 `niveau_mutualisation_id`      int(11) unsigned NOT NULL ,
 `operateur_mutualisation_id`  int(11) unsigned NOT NULL ,
 `imputation_budgetaire_id`    int(11) unsigned NOT NULL ,
 `famille_id`                  int(11) unsigned NULL ,
 `domaine_id`                  int(11) unsigned NULL ,
 `categorie_id`                int(11) unsigned NULL ,
 `segment_id`                  int(11) unsigned NULL ,

PRIMARY KEY (`id`),
KEY `fkIdx_431` (`paat_id`),
CONSTRAINT `FK_431` FOREIGN KEY `fkIdx_431` (`paat_id`) REFERENCES `paats` (`id`),
KEY `fkIdx_459` (`owner_id`),
CONSTRAINT `FK_459` FOREIGN KEY `fkIdx_459` (`owner_id`) REFERENCES `users` (`id`),
KEY `fkIdx_627` (`structure_id`),
CONSTRAINT `FK_627` FOREIGN KEY `fkIdx_627` (`structure_id`) REFERENCES `structures` (`id`),
KEY `fkIdx_630` (`direction_id`),
CONSTRAINT `FK_630` FOREIGN KEY `fkIdx_630` (`direction_id`) REFERENCES `directions` (`id`),
KEY `fkIdx_633` (`service_id`),
CONSTRAINT `FK_633` FOREIGN KEY `fkIdx_633` (`service_id`) REFERENCES `services` (`id`),
KEY `fkIdx_636` (`niveau_mutualisation_id`),
CONSTRAINT `FK_636` FOREIGN KEY `fkIdx_636` (`niveau_mutualisation_id`) REFERENCES `niveaux_mutualisations` (`id`),
KEY `fkIdx_639` (`operateur_mutualisation_id`),
CONSTRAINT `FK_639` FOREIGN KEY `fkIdx_639` (`operateur_mutualisation_id`) REFERENCES `operateurs_mutualisations` (`id`),
KEY `fkIdx_642` (`imputation_budgetaire_id`),
CONSTRAINT `FK_642` FOREIGN KEY `fkIdx_642` (`imputation_budgetaire_id`) REFERENCES `imputations_budgetaires` (`id`),
KEY `fkIdx_645` (`famille_id`),
CONSTRAINT `FK_645` FOREIGN KEY `fkIdx_645` (`famille_id`) REFERENCES `familles` (`id`),
KEY `fkIdx_648` (`domaine_id`),
CONSTRAINT `FK_648` FOREIGN KEY `fkIdx_648` (`domaine_id`) REFERENCES `domaines` (`id`),
KEY `fkIdx_651` (`categorie_id`),
CONSTRAINT `FK_651` FOREIGN KEY `fkIdx_651` (`categorie_id`) REFERENCES `categories` (`id`),
KEY `fkIdx_654` (`segment_id`),
CONSTRAINT `FK_654` FOREIGN KEY `fkIdx_654` (`segment_id`) REFERENCES `segments` (`id`)
);

CREATE TABLE `leviers_projets`
(
 `id`        int(11) unsigned NOT NULL AUTO_INCREMENT ,
 `levier_id` int(11) unsigned NOT NULL ,
 `projet_id` int(11) unsigned NOT NULL ,

PRIMARY KEY (`id`),
KEY `fkIdx_479` (`levier_id`),
CONSTRAINT `FK_479` FOREIGN KEY `fkIdx_479` (`levier_id`) REFERENCES `leviers` (`id`),
KEY `fkIdx_482` (`projet_id`),
CONSTRAINT `FK_482` FOREIGN KEY `fkIdx_482` (`projet_id`) REFERENCES `projets` (`id`)
);

CREATE TABLE `membres_projets`
(
 `id`        int(11) unsigned NOT NULL AUTO_INCREMENT ,
 `projet_id` int(11) unsigned NOT NULL ,
 `user_id`   int(11) unsigned NOT NULL ,

PRIMARY KEY (`id`),
KEY `fkIdx_467` (`projet_id`),
CONSTRAINT `FK_467` FOREIGN KEY `fkIdx_467` (`projet_id`) REFERENCES `projets` (`id`),
KEY `fkIdx_470` (`user_id`),
CONSTRAINT `FK_470` FOREIGN KEY `fkIdx_470` (`user_id`) REFERENCES `users` (`id`)
);

CREATE TABLE `projets_statuts`
(
 `id`        int(11) unsigned NOT NULL AUTO_INCREMENT ,
 `message`   varchar(255) NULL ,
 `user_id`   int(11) unsigned NOT NULL ,
 `statut_id` int(11) unsigned NOT NULL ,
 `projet_id` int(11) unsigned NOT NULL ,

PRIMARY KEY (`id`),
KEY `fkIdx_450` (`user_id`),
CONSTRAINT `FK_450` FOREIGN KEY `fkIdx_450` (`user_id`) REFERENCES `users` (`id`),
KEY `fkIdx_453` (`statut_id`),
CONSTRAINT `FK_453` FOREIGN KEY `fkIdx_453` (`statut_id`) REFERENCES `statuts` (`id`),
KEY `fkIdx_456` (`projet_id`),
CONSTRAINT `FK_456` FOREIGN KEY `fkIdx_456` (`projet_id`) REFERENCES `projets` (`id`)
);

CREATE TABLE `montants_achats_complexes_projets`
(
 `id`        int(11) unsigned NOT NULL AUTO_INCREMENT ,
 `type`      enum('reference', 'realise') NOT NULL ,
 `annee`     year NOT NULL ,
 `valeur`    int(11) NOT NULL ,
 `projet_id` int(11) unsigned NOT NULL ,

PRIMARY KEY (`id`),
KEY `fkIdx_583` (`projet_id`),
CONSTRAINT `FK_583` FOREIGN KEY `fkIdx_583` (`projet_id`) REFERENCES `projets` (`id`)
);

CREATE TABLE `gains_achats_complexes_projets`
(
 `id`        int(11) unsigned NOT NULL AUTO_INCREMENT ,
 `type`      enum('cible', 'notifie', 'securise') NOT NULL ,
 `annee`     year NOT NULL ,
 `valeur`    int(11) NOT NULL ,
 `projet_id` int(11) unsigned NOT NULL ,

PRIMARY KEY (`id`),
KEY `fkIdx_593` (`projet_id`),
CONSTRAINT `FK_593` FOREIGN KEY `fkIdx_593` (`projet_id`) REFERENCES `projets` (`id`)
);

CREATE TABLE `etapes_projets`
(
 `id`        int(11) unsigned NOT NULL AUTO_INCREMENT ,
 `etape`     enum('creation', 'leviers', 'achats_simples', 'achats_complexes', 'resp_soc_env', 'gestion_projet', 'suivi', 'statut') NOT NULL ,
 `projet_id` int(11) unsigned NOT NULL ,

PRIMARY KEY (`id`),
KEY `fkIdx_547` (`projet_id`),
CONSTRAINT `FK_547` FOREIGN KEY `fkIdx_547` (`projet_id`) REFERENCES `projets` (`id`)
);

CREATE TABLE `alertes_envoyees`
(
 `id`         int(11) unsigned NOT NULL AUTO_INCREMENT ,
 `indicateur` int NOT NULL ,
 `alerte_id`  int(11) unsigned NOT NULL ,
 `projet_id`  int(11) unsigned NOT NULL ,

PRIMARY KEY (`id`),
KEY `fkIdx_497` (`alerte_id`),
CONSTRAINT `FK_497` FOREIGN KEY `fkIdx_497` (`alerte_id`) REFERENCES `alertes` (`id`),
KEY `fkIdx_500` (`projet_id`),
CONSTRAINT `FK_500` FOREIGN KEY `fkIdx_500` (`projet_id`) REFERENCES `projets` (`id`)
);

CREATE TABLE `achats_complexes_projets`
(
 `id`        int(11) unsigned NOT NULL AUTO_INCREMENT ,
 `type`      enum('amortissements_investissements', 'maintenances', 'couts_installations', 'consommables', 'couts_formations', 'resources_internes', 'energies', 'autres') NOT NULL ,
 `annee`     year NOT NULL ,
 `valeur`    int(11) NOT NULL ,
 `projet_id` int(11) unsigned NOT NULL ,

PRIMARY KEY (`id`),
KEY `fkIdx_586` (`projet_id`),
CONSTRAINT `FK_586` FOREIGN KEY `fkIdx_586` (`projet_id`) REFERENCES `projets` (`id`)
);

CREATE TABLE `projets_actions_resp_soc_env`
(
 `id`        int(11) unsigned NOT NULL AUTO_INCREMENT ,
 `action_id` int(11) unsigned NOT NULL ,
 `projet_id` int(11) unsigned NOT NULL ,

PRIMARY KEY (`id`),
KEY `fkIdx_529` (`action_id`),
CONSTRAINT `FK_529` FOREIGN KEY `fkIdx_529` (`action_id`) REFERENCES `actions` (`id`),
KEY `fkIdx_535` (`projet_id`),
CONSTRAINT `FK_535` FOREIGN KEY `fkIdx_535` (`projet_id`) REFERENCES `projets` (`id`)
);

CREATE TABLE `fichiers`
(
 `id`   int(11) unsigned NOT NULL AUTO_INCREMENT ,
 `nom`  varchar(255) NOT NULL ,
 `type` enum('achat_simple', 'achat_complexe') NOT NULL ,
 `url`  text NOT NULL ,

PRIMARY KEY (`id`)
);

CREATE TABLE `fichiers_has_projets`
(
 `id`         int(11) unsigned NOT NULL AUTO_INCREMENT ,
 `fichier_id` int(11) unsigned NOT NULL ,
 `projet_id`  int(11) unsigned NOT NULL ,

PRIMARY KEY (`id`),
KEY `fkIdx_668` (`fichier_id`),
CONSTRAINT `FK_668` FOREIGN KEY `fkIdx_668` (`fichier_id`) REFERENCES `fichiers` (`id`),
KEY `fkIdx_671` (`projet_id`),
CONSTRAINT `FK_671` FOREIGN KEY `fkIdx_671` (`projet_id`) REFERENCES `projets` (`id`)
);
