Création des tables et des views

Nous rejoindre sur Discord

Les tables regroupent toutes les informations lors de l'utilisation du projet All in One, les views permettent de regrouper les informations de plusieurs tables en une seule.

Création des tables

Respectez l'ordre d'exécution des requêtes !

Pour la création des tables et des views nécessaires au fonctionnement du projet, veuillez exécuté les requêtes sql suivantes (disponible également en téléchargement : allinone.sql)

CREATE TABLE IF NOT EXISTS `personnes` (
  `id` INTEGER NOT NULL AUTO_INCREMENT UNIQUE,
  `nom` varchar(32) NOT NULL,
  `prenom` varchar(32) NOT NULL,
  `DOB` DATE NOT NULL,
  `phone` varchar(32),
  `nationality` varchar(32) NOT NULL,
  `sexe` ENUM('M','F') NOT NULL DEFAULT 'M',
  `grp_sanguin` varchar(32),
  `job` varchar(32),
  `ppa` ENUM('0','1') NOT NULL DEFAULT '0',
  `permis` ENUM('0','1') NOT NULL DEFAULT '0',
  `donneur` ENUM('0','1') NOT NULL DEFAULT '0',
  `date_permis` INTEGER NULL DEFAULT NULL,
  `photo` varchar(1024) NOT NULL,
  `present` ENUM('0','1') NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `lspd_delit` (
  `id` INTEGER NOT NULL AUTO_INCREMENT UNIQUE,
  `intitule` varchar(512) NOT NULL,
  `amende` INTEGER,
  `temps_prison` INTEGER,
  `type_delit` ENUM('0','1','2','3') NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `ems_liste_intervention` (
  `id` INTEGER NOT NULL AUTO_INCREMENT UNIQUE,
  `intitule` varchar(512) NOT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `lspd_categorie_arme` (
  `id` INTEGER NOT NULL AUTO_INCREMENT UNIQUE,
  `nom` varchar(128) NOT NULL,
  `prefix` varchar(4) NOT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `lspd_liste_arme` (
  `id` INTEGER NOT NULL AUTO_INCREMENT UNIQUE,
  `nom` varchar(64) NOT NULL,
  `categorie` INTEGER NOT NULL,
  `photo` varchar(128) NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (categorie) REFERENCES lspd_categorie_arme(id)
);

CREATE TABLE IF NOT EXISTS `lspd_historique_connexion` (
  `id` INTEGER NOT NULL AUTO_INCREMENT UNIQUE,
  `matricule_utilise` varchar(32) NOT NULL,
  `mpd_utilise` varchar(128) NOT NULL,
  `adresse_ip` varchar(32) NOT NULL,
  `etat` ENUM('Réussite','Echec') NOT NULL DEFAULT 'Echec',
  `commentaire` varchar(128) NOT NULL,
	`date_connexion` DATETIME NOT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `ems_historique_connexion` (
  `id` INTEGER NOT NULL AUTO_INCREMENT UNIQUE,
  `matricule_utilise` varchar(32) NOT NULL,
  `mpd_utilise` varchar(128) NOT NULL,
  `adresse_ip` varchar(32) NOT NULL,
  `etat` ENUM('Réussite','Echec') NOT NULL DEFAULT 'Echec',
  `commentaire` varchar(128) NOT NULL,
	`date_connexion` DATETIME NOT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `lspd_grade` (
  `id` INTEGER NOT NULL AUTO_INCREMENT UNIQUE,
  `nom` varchar(32)  NOT NULL,
  `position` INT(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `ems_grade` (
  `id` INTEGER NOT NULL AUTO_INCREMENT UNIQUE,
  `nom` varchar(32)  NOT NULL,
  `position` INT(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `ems_liste_medicament` (
  `id` INTEGER NOT NULL AUTO_INCREMENT UNIQUE,
  `nom` varchar(128) NOT NULL,
  `description` varchar(1024)  NOT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `lspd_policier` (
  `id` INTEGER NOT NULL AUTO_INCREMENT UNIQUE,
  `matricule` varchar(32) NOT NULL UNIQUE,
  `Passwd` varchar(128) NOT NULL,
  `Sel_de_table` varchar(32) NOT NULL,
  `personne` INTEGER NOT NULL UNIQUE,
  `rang` INTEGER NOT NULL DEFAULT '1',
  `note` varchar(64) DEFAULT NULL,
  `lspd_admin` ENUM('0','1') NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`, `matricule`),
  FOREIGN KEY (personne) REFERENCES personnes(id),
  FOREIGN KEY (rang) REFERENCES lspd_grade(id)
);

CREATE TABLE IF NOT EXISTS `lspd_arme` (
  `id` INTEGER NOT NULL AUTO_INCREMENT UNIQUE,
  `numero` varchar(4) NOT NULL UNIQUE,
  `type` INTEGER NOT NULL,
  `proprio` INTEGER NOT NULL,
  `enregistrer_par` INTEGER NOT NULL,
  `enregistrer_name` VARCHAR(512),
  PRIMARY KEY (`id`),
  FOREIGN KEY (proprio) REFERENCES personnes(id),
  FOREIGN KEY (type) REFERENCES lspd_liste_arme(id),
  FOREIGN KEY (enregistrer_par) REFERENCES lspd_policier(id)
);

CREATE TABLE IF NOT EXISTS `ems_hopital` (
  `id` INTEGER NOT NULL AUTO_INCREMENT UNIQUE,
  `matricule` varchar(32) NOT NULL UNIQUE,
  `Passwd` varchar(128) NOT NULL,
  `Sel_de_table` varchar(32) NOT NULL,
  `personne` INTEGER NOT NULL UNIQUE,
  `rang` INTEGER NOT NULL DEFAULT '1',
  `note` varchar(64) DEFAULT NULL,
  `admin_ems` ENUM('0','1') NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`, `matricule`),
  FOREIGN KEY (personne) REFERENCES personnes(id),
  FOREIGN KEY (rang) REFERENCES ems_grade(id)
);

CREATE TABLE IF NOT EXISTS `modeles_vehicules` (
  `id` INTEGER NOT NULL AUTO_INCREMENT UNIQUE,
  `nom` varchar(128) NOT NULL,
  `lien` varchar(1024),
  PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `vehicules` (
  `id` INTEGER NOT NULL AUTO_INCREMENT UNIQUE,
  `modeles` INTEGER NOT NULL,
  `couleur` varchar(32) NOT NULL,
  `plaque` varchar(32) NOT NULL,
  `proprietaire` INTEGER NOT NULL,
  `enregistre_par` INTEGER NOT NULL,
  `enregistrer_name` VARCHAR(512),
  `en_circulation` ENUM('0','1') NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  FOREIGN KEY (modeles) REFERENCES modeles_vehicules(id),
  FOREIGN KEY (proprietaire) REFERENCES personnes(id),
  FOREIGN KEY (enregistre_par) REFERENCES lspd_policier(id)
);

CREATE TABLE IF NOT EXISTS `lspd_habilitation` (
  `id` INTEGER NOT NULL AUTO_INCREMENT UNIQUE,
  `matricule` INTEGER NOT NULL NOT NULL UNIQUE,
  `hab_1` ENUM('0','1','2') NOT NULL DEFAULT '0',
  `hab_2` ENUM('0','1','2') NOT NULL DEFAULT '0',
  `hab_3` ENUM('0','1','2') NOT NULL DEFAULT '0',
  `hab_4` ENUM('0','1','2') NOT NULL DEFAULT '0',
  `hab_5` ENUM('0','1','2') NOT NULL DEFAULT '0',
  `hab_6` ENUM('0','1','2') NOT NULL DEFAULT '0',
  `hab_7` ENUM('0','1','2') NOT NULL DEFAULT '0',
  `hab_8` ENUM('0','1','2') NOT NULL DEFAULT '0',
  `hab_9` ENUM('0','1','2') NOT NULL DEFAULT '0',
  `hab_10` ENUM('0','1','2') NOT NULL DEFAULT '0',
  `hab_11` ENUM('0','1','2') NOT NULL DEFAULT '0',
  `hab_12` ENUM('0','1','2') NOT NULL DEFAULT '0',
  `hab_13` ENUM('0','1','2') NOT NULL DEFAULT '0',
  `hab_14` ENUM('0','1','2') NOT NULL DEFAULT '0',
  `hab_15` ENUM('0','1','2') NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  FOREIGN KEY (matricule) REFERENCES lspd_policier(id)
);

CREATE TABLE IF NOT EXISTS `ems_habilitation` (
  `id` INTEGER NOT NULL AUTO_INCREMENT UNIQUE,
  `matricule` INTEGER NOT NULL NOT NULL UNIQUE,
  `hab_1` ENUM('0','1','2') NOT NULL DEFAULT '0',
  `hab_2` ENUM('0','1','2') NOT NULL DEFAULT '0',
  `hab_3` ENUM('0','1','2') NOT NULL DEFAULT '0',
  `hab_4` ENUM('0','1','2') NOT NULL DEFAULT '0',
  `hab_5` ENUM('0','1','2') NOT NULL DEFAULT '0',
  `hab_6` ENUM('0','1','2') NOT NULL DEFAULT '0',
  `hab_7` ENUM('0','1','2') NOT NULL DEFAULT '0',
  `hab_8` ENUM('0','1','2') NOT NULL DEFAULT '0',
  `hab_9` ENUM('0','1','2') NOT NULL DEFAULT '0',
  `hab_10` ENUM('0','1','2') NOT NULL DEFAULT '0',
  `hab_11` ENUM('0','1','2') NOT NULL DEFAULT '0',
  `hab_12` ENUM('0','1','2') NOT NULL DEFAULT '0',
  `hab_13` ENUM('0','1','2') NOT NULL DEFAULT '0',
  `hab_14` ENUM('0','1','2') NOT NULL DEFAULT '0',
  `hab_15` ENUM('0','1','2') NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  FOREIGN KEY (matricule) REFERENCES ems_hopital(id)
);

CREATE TABLE IF NOT EXISTS `lspd_autorisation` (
  `id` INTEGER NOT NULL AUTO_INCREMENT UNIQUE,
  `matricule` INTEGER NOT NULL,
  `add_perm` ENUM('0','1','2','3','4','5') NOT NULL DEFAULT '0',
  `edit_perm` ENUM('0','1','2','3','4','5') NOT NULL DEFAULT '0',
  `delete_perm` ENUM('0','1','2','3','4','5') NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  FOREIGN KEY (matricule) REFERENCES lspd_policier(id)
);

CREATE TABLE IF NOT EXISTS `lspd_plainte` (
  `id` INTEGER NOT NULL AUTO_INCREMENT UNIQUE,
  `deposeur` INTEGER NOT NULL,
  `plainte_sur` INTEGER,
  `detail_plainte` LONGTEXT,
  `enregistrer_par` INTEGER NOT NULL,
  `enregistrer_name` VARCHAR(512),
	`enregistrer_le` DATE NOT NULL,
  `etat` ENUM('0','1','2') NOT NULL DEFAULT '0',
  `fermer_par` INTEGER,
  `fermer_name` VARCHAR(512),
	`fermer_le` DATE,
  PRIMARY KEY (`id`),
  FOREIGN KEY (deposeur) REFERENCES personnes(id),
  FOREIGN KEY (enregistrer_par) REFERENCES lspd_policier(id),
  FOREIGN KEY (fermer_par) REFERENCES lspd_policier(id)
);

CREATE TABLE IF NOT EXISTS `lspd_route` (
  `id` INTEGER NOT NULL AUTO_INCREMENT UNIQUE,
  `vehicule` INTEGER NOT NULL,
  `conducteur` INTEGER NOT NULL,
  `numero_delit` INTEGER NOT NULL,
  `enregistrer_par` INTEGER NOT NULL,
  `enregistrer_name` VARCHAR(512),
	`enregistrer_le` DATE NOT NULL,
  `acquite` ENUM('0','1','2') NOT NULL DEFAULT '0',
  `acquite_par` INTEGER,
  `acquite_name` VARCHAR(512),
	`acquite_le` DATE,
  `remarque` LONGTEXT DEFAULT 'Aucun rapport',
  PRIMARY KEY (`id`),
  FOREIGN KEY (vehicule) REFERENCES vehicules(id),
  FOREIGN KEY (conducteur) REFERENCES personnes(id),
  FOREIGN KEY (numero_delit) REFERENCES lspd_delit(id),
  FOREIGN KEY (enregistrer_par) REFERENCES lspd_policier(id),
  FOREIGN KEY (acquite_par) REFERENCES lspd_policier(id)
);

CREATE TABLE IF NOT EXISTS `lspd_casier` (
  `id` INTEGER NOT NULL AUTO_INCREMENT UNIQUE,
  `personne` INTEGER NOT NULL,
  `numero_delit` INTEGER NOT NULL,
  `enregistrer_par` INTEGER NOT NULL,
  `enregistrer_name` VARCHAR(512),
	`enregistrer_le` DATE NOT NULL,
  `acquite` ENUM('0','1','2') NOT NULL DEFAULT '0',
  `acquite_par` INTEGER,
  `acquite_name` VARCHAR(512),
	`acquite_le` DATE,
  `remarque` LONGTEXT DEFAULT 'Aucun rapport',
  PRIMARY KEY (`id`),
  FOREIGN KEY (personne) REFERENCES personnes(id),
  FOREIGN KEY (numero_delit) REFERENCES lspd_delit(id),
  FOREIGN KEY (enregistrer_par) REFERENCES lspd_policier(id),
  FOREIGN KEY (acquite_par) REFERENCES lspd_policier(id)
);

CREATE TABLE IF NOT EXISTS `ems_intervention` (
  `id` INTEGER NOT NULL AUTO_INCREMENT UNIQUE,
  `personne` INTEGER NOT NULL,
  `numero_intervention` INTEGER NOT NULL,
  `enregistrer_par` INTEGER NOT NULL,
  `enregistrer_name` VARCHAR(512),
	`enregistrer_le` DATE NOT NULL,
  `remarque` LONGTEXT DEFAULT 'Aucun rapport',
  PRIMARY KEY (`id`),
  FOREIGN KEY (personne) REFERENCES personnes(id),
  FOREIGN KEY (numero_intervention) REFERENCES ems_liste_intervention(id),
  FOREIGN KEY (enregistrer_par) REFERENCES ems_hopital(id)
);

CREATE TABLE IF NOT EXISTS `ems_arret_travail` (
  `id` INTEGER NOT NULL AUTO_INCREMENT UNIQUE,
  `personne` INTEGER NOT NULL,
  `motif` LONGTEXT NOT NULL,
  `enregistrer_par` INTEGER NOT NULL,
  `enregistrer_name` VARCHAR(512),
	`enregistrer_le` DATE NOT NULL,
	`fin_le` DATE NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (personne) REFERENCES personnes(id),
  FOREIGN KEY (enregistrer_par) REFERENCES ems_hopital(id)
);

CREATE TABLE IF NOT EXISTS `ems_certificat_ppa` (
  `id` INTEGER NOT NULL AUTO_INCREMENT UNIQUE,
  `personne` INTEGER NOT NULL,
  `etat_ppa` ENUM('0','1') NOT NULL DEFAULT '0',
  `rapport` LONGTEXT,
  `enregistrer_par` INTEGER NOT NULL,
  `enregistrer_name` VARCHAR(512),
	`enregistrer_le` DATE NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (personne) REFERENCES personnes(id),
  FOREIGN KEY (enregistrer_par) REFERENCES ems_hopital(id)
);

CREATE TABLE IF NOT EXISTS `ems_certificat_travail` (
  `id` INTEGER NOT NULL AUTO_INCREMENT UNIQUE,
  `personne` INTEGER NOT NULL,
  `etat_job` ENUM('0','1') NOT NULL DEFAULT '0',
  `job_vise` varchar(64) NOT NULL,
  `motif` LONGTEXT NOT NULL,
  `enregistrer_par` INTEGER NOT NULL,
  `enregistrer_name` VARCHAR(512),
	`enregistrer_le` DATE NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (personne) REFERENCES personnes(id),
  FOREIGN KEY (enregistrer_par) REFERENCES ems_hopital(id)
);

CREATE TABLE IF NOT EXISTS `ems_ordonnance` (
  `id` INTEGER NOT NULL AUTO_INCREMENT UNIQUE,
  `patient` INTEGER NOT NULL,
  `enregistrer_par` INTEGER NOT NULL,
  `enregistrer_name` VARCHAR(512),
	`enregistrer_le` DATE NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (patient) REFERENCES personnes(id),
  FOREIGN KEY (enregistrer_par) REFERENCES ems_hopital(id)
);

CREATE TABLE IF NOT EXISTS `ems_details_ordonnance` (
  `id` INTEGER NOT NULL AUTO_INCREMENT UNIQUE,
  `n_ordonnance` INTEGER NOT NULL,
  `nom_medicament` INTEGER NOT NULL,
  `quantite` INTEGER NOT NULL,
  `periode` INTEGER NOT NULL,
  `frequence` ENUM('1','2','3','4','5','6','7') NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  FOREIGN KEY (n_ordonnance) REFERENCES ems_ordonnance(id),
  FOREIGN KEY (nom_medicament) REFERENCES ems_liste_medicament(id)
);

CREATE TABLE IF NOT EXISTS `lspd_rapport` (
  `id` INTEGER NOT NULL AUTO_INCREMENT UNIQUE,
  `concerne` INTEGER  NOT NULL,
  `editeur` INTEGER  NOT NULL,
  `editeur_name` VARCHAR(512),
  `titre` varchar(128)  NOT NULL,
  `contenu` LONGTEXT NOT NULL,
  `ecrit_le` DATE NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (concerne) REFERENCES lspd_policier(id),
  FOREIGN KEY (editeur) REFERENCES lspd_policier(id)
);

CREATE TABLE IF NOT EXISTS `lspd_rapport_vu` (
  `id` INTEGER NOT NULL AUTO_INCREMENT UNIQUE,
  `policier_id` INTEGER NOT NULL,
  `rapport_id` INTEGER NOT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (policier_id) REFERENCES lspd_policier(id),
  FOREIGN KEY (rapport_id) REFERENCES lspd_rapport(id)
);

CREATE TABLE IF NOT EXISTS `lspd_historique` (
  `id` INTEGER NOT NULL AUTO_INCREMENT UNIQUE,
  `matricule` INTEGER NOT NULL,
  `contenu` TEXT DEFAULT 'Erreur dans le processus',
	`date_even` DATETIME NOT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `ems_historique` (
  `id` INTEGER NOT NULL AUTO_INCREMENT UNIQUE,
  `matricule` INTEGER NOT NULL,
  `contenu` varchar(1024) DEFAULT 'Erreur dans le processus',
	`date_even` DATETIME NOT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `ems_candidature` (
  `id` INTEGER NOT NULL AUTO_INCREMENT UNIQUE,
  `version` INTEGER NOT NULL DEFAULT 1,
  `formulaires` TEXT,
  `attachments` TEXT,
	`date_depot` DATE NOT NULL,
  `etat_act` ENUM('0','1','2') NOT NULL DEFAULT '0',
  `adress_ip` varchar(32) NOT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS `lspd_candidature` (
  `id` INTEGER NOT NULL AUTO_INCREMENT UNIQUE,
  `version` INTEGER NOT NULL DEFAULT 1,
  `formulaires` TEXT,
  `attachments` TEXT,
	`date_depot` DATE NOT NULL,
  `etat_act` ENUM('0','1','2') NOT NULL DEFAULT '0',
  `adress_ip` varchar(32) NOT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS chat (
  `id` INTEGER NOT NULL AUTO_INCREMENT UNIQUE,
  `author` varchar(128) NOT NULL,
  `message` varchar(512) NOT NULL,
  `side` ENUM('cop','ems') NOT NULL,
  `send_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE IF NOT EXISTS lspd_folder (
  `id` INTEGER NOT NULL AUTO_INCREMENT UNIQUE,
  `title` varchar(128) NOT NULL,
  `description` varchar(512),
  `parent` INTEGER NOT NULL DEFAULT 1,
  `habilitations` varchar(512) NOT NULL DEFAULT '0,0,0,0,0,0,0,0,0,0,0,0,0,0,0',
  `grades` varchar(512) NOT NULL DEFAULT '0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0',
  `createdby` INTEGER NOT NULL,
  `updateby` INTEGER,
  `createdat` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updateat` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  FOREIGN KEY (parent) REFERENCES lspd_folder(id),
  FOREIGN KEY (createdby) REFERENCES lspd_policier(id),
  FOREIGN KEY (updateby) REFERENCES lspd_policier(id)
);

CREATE TABLE IF NOT EXISTS lspd_document (
  `id` INTEGER NOT NULL AUTO_INCREMENT UNIQUE,
  `title` varchar(128) NOT NULL,
  `description` varchar(512),
  `parent` INTEGER NOT NULL DEFAULT 1,
  `type` ENUM('document', 'image', 'pdf') NOT NULL DEFAULT 'document',
  `content` TEXT DEFAULT "Document vide",
  `habilitations` varchar(512) NOT NULL DEFAULT '0,0,0,0,0,0,0,0,0,0,0,0,0,0,0',
  `grades` varchar(512) NOT NULL DEFAULT '0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0',
  `isdeleted` BOOLEAN NOT NULL DEFAULT 0,
  `createdby` INTEGER NOT NULL,
  `updateby` INTEGER,
  `deletedby` INTEGER,
  `createdat` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updateat` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deletedat` DATETIME,
  PRIMARY KEY (id),
  FOREIGN KEY (parent) REFERENCES lspd_folder(id),
  FOREIGN KEY (createdby) REFERENCES lspd_policier(id),
  FOREIGN KEY (updateby) REFERENCES lspd_policier(id),
  FOREIGN KEY (deletedby) REFERENCES lspd_policier(id)
);

CREATE TABLE IF NOT EXISTS lspd_document_historique (
  `id` INTEGER NOT NULL AUTO_INCREMENT UNIQUE,
  `id_document` INTEGER,
  `old_content` TEXT DEFAULT "Document vide",
  `new_content` TEXT DEFAULT "Document vide",
  `updateby` INTEGER,
  `updateat` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  FOREIGN KEY (id_document) REFERENCES lspd_document(id),
  FOREIGN KEY (updateby) REFERENCES lspd_policier(id)
);

CREATE TABLE IF NOT EXISTS ems_folder (
  `id` INTEGER NOT NULL AUTO_INCREMENT UNIQUE,
  `title` varchar(128) NOT NULL,
  `description` varchar(512),
  `parent` INTEGER NOT NULL DEFAULT 1,
  `habilitations` varchar(512) NOT NULL DEFAULT '0,0,0,0,0,0,0,0,0,0,0,0,0,0,0',
  `grades` varchar(512) NOT NULL DEFAULT '0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0',
  `createdby` INTEGER NOT NULL,
  `updateby` INTEGER,
  `createdat` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updateat` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  FOREIGN KEY (parent) REFERENCES ems_folder(id),
  FOREIGN KEY (createdby) REFERENCES ems_hopital(id),
  FOREIGN KEY (updateby) REFERENCES ems_hopital(id)
);

CREATE TABLE IF NOT EXISTS ems_document (
  `id` INTEGER NOT NULL AUTO_INCREMENT UNIQUE,
  `title` varchar(128) NOT NULL,
  `description` varchar(512),
  `parent` INTEGER NOT NULL DEFAULT 1,
  `type` ENUM('document', 'image', 'pdf') NOT NULL DEFAULT 'document',
  `content` TEXT DEFAULT "Document vide",
  `habilitations` varchar(512) NOT NULL DEFAULT '0,0,0,0,0,0,0,0,0,0,0,0,0,0,0',
  `grades` varchar(512) NOT NULL DEFAULT '0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0',
  `isdeleted` BOOLEAN NOT NULL DEFAULT 0,
  `createdby` INTEGER NOT NULL,
  `updateby` INTEGER,
  `deletedby` INTEGER,
  `createdat` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updateat` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deletedat` DATETIME,
  PRIMARY KEY (id),
  FOREIGN KEY (parent) REFERENCES ems_folder(id),
  FOREIGN KEY (createdby) REFERENCES ems_hopital(id),
  FOREIGN KEY (updateby) REFERENCES ems_hopital(id),
  FOREIGN KEY (deletedby) REFERENCES ems_hopital(id)
);

CREATE TABLE IF NOT EXISTS ems_document_historique (
  `id` INTEGER NOT NULL AUTO_INCREMENT UNIQUE,
  `id_document` INTEGER,
  `old_content` TEXT DEFAULT "Document vide",
  `new_content` TEXT DEFAULT "Document vide",
  `updateby` INTEGER,
  `updateat` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  FOREIGN KEY (id_document) REFERENCES ems_document(id),
  FOREIGN KEY (updateby) REFERENCES ems_hopital(id)
);

CREATE OR REPLACE VIEW info_lspd AS
  SELECT
    personnes.id AS user_id, lspd_policier.id AS lspd_id, lspd_policier.matricule AS matricule, personnes.nom AS nom, personnes.prenom AS prenom, personnes.phone AS telephone, personnes.nationality AS nationality,
    personnes.photo AS photo, personnes.sexe AS sexe, lspd_grade.nom AS `grade`, lspd_grade.position AS `grade_id`, lspd_policier.note AS `note`, lspd_autorisation.add_perm AS ajouter, lspd_autorisation.edit_perm AS editer, lspd_autorisation.delete_perm AS supprimer,
    lspd_habilitation.hab_1, lspd_habilitation.hab_2, lspd_habilitation.hab_3, lspd_habilitation.hab_4, lspd_habilitation.hab_5, lspd_habilitation.hab_6, lspd_habilitation.hab_7, lspd_habilitation.hab_8,
    lspd_habilitation.hab_9, lspd_habilitation.hab_10, lspd_habilitation.hab_11, lspd_habilitation.hab_12, lspd_habilitation.hab_13, lspd_habilitation.hab_14, lspd_habilitation.hab_15,
   lspd_policier.lspd_admin AS admin
  FROM
    lspd_policier, personnes, lspd_grade, lspd_autorisation, lspd_habilitation
  WHERE
    personnes.id = lspd_policier.personne AND lspd_policier.id = lspd_autorisation.matricule AND lspd_policier.rang = lspd_grade.id AND lspd_habilitation.matricule = lspd_policier.id
  ORDER BY
    lspd_grade.`position` DESC;

CREATE OR REPLACE VIEW info_casier AS
  SELECT
  		lspd_casier.id AS id_delit, lspd_casier.personne AS id_personne, lspd_delit.intitule AS nom, lspd_delit.type_delit AS type_d, lspd_delit.amende AS amende, lspd_delit.temps_prison AS prison,
  		lspd_casier.enregistrer_par AS enregistrer_par, lspd_casier.enregistrer_name AS enregistrer_name, lspd_casier.enregistrer_le AS enregistrer_le, lspd_casier.acquite AS etat,
  		lspd_casier.acquite_par AS acquite_par, lspd_casier.acquite_name AS acquite_name, lspd_casier.acquite_le AS acquite_le, lspd_casier.remarque AS remarque
  	FROM
  		lspd_casier, lspd_delit
  	WHERE
  		lspd_casier.numero_delit = lspd_delit.id;

CREATE OR REPLACE VIEW info_plainte AS
  SELECT
    lspd_plainte.id AS p_id, personnes.nom AS nom_1, personnes.prenom AS prenom_1, personnes.photo AS lien,
    lspd_plainte.detail_plainte AS detail, lspd_plainte.etat AS etat
  FROM
    lspd_plainte, personnes
  WHERE
    lspd_plainte.plainte_sur = personnes.id;

CREATE OR REPLACE VIEW info_route AS
  SELECT
  		lspd_route.id AS delit_id, lspd_route.vehicule AS v_id, lspd_route.conducteur AS conducteur_id, lspd_delit.intitule AS nom, lspd_delit.amende AS amende,
  		lspd_delit.temps_prison AS prison, lspd_delit.id AS delit_ref, lspd_route.enregistrer_par AS enregistreur_id, lspd_route.enregistrer_name AS enregistreur_name, lspd_route.acquite AS etat,
  		lspd_route.enregistrer_le AS date_enregistre, lspd_route.acquite_par AS acquite_par, lspd_route.acquite_name AS acquite_name, lspd_route.acquite_le AS acquite_le,
      lspd_route.remarque AS remarque
  	FROM
  		lspd_route, lspd_delit
  	WHERE
  		lspd_route.numero_delit = lspd_delit.id;

CREATE OR REPLACE VIEW info_voiture AS
  SELECT
  		vehicules.id AS v_id, modeles_vehicules.nom AS nom, modeles_vehicules.lien AS lien, vehicules.couleur AS couleur, vehicules.plaque AS plaque, vehicules.proprietaire AS proprio,
  		vehicules.enregistre_par AS enregistreur, vehicules.enregistrer_name AS enregistreur_name, vehicules.en_circulation AS circulation
  	FROM
  		modeles_vehicules, vehicules
  	WHERE
  		vehicules.modeles = modeles_vehicules.id;

CREATE OR REPLACE VIEW info_ems AS
  SELECT
    personnes.id AS user_id, ems_hopital.id AS ems_id, ems_hopital.matricule AS matricule, personnes.nom AS nom, personnes.prenom AS prenom, personnes.phone AS telephone, personnes.nationality AS nationality,
    personnes.photo AS photo, personnes.sexe AS sexe, personnes.grp_sanguin AS sang, personnes.donneur AS donneur, ems_grade.nom AS `grade`, ems_grade.`position` AS `grade_id`, ems_hopital.note AS `note`,
   ems_habilitation.hab_1, ems_habilitation.hab_2, ems_habilitation.hab_3, ems_habilitation.hab_4, ems_habilitation.hab_5, ems_habilitation.hab_6, ems_habilitation.hab_7, ems_habilitation.hab_8,
    ems_habilitation.hab_9, ems_habilitation.hab_10, ems_habilitation.hab_11, ems_habilitation.hab_12, ems_habilitation.hab_13, ems_habilitation.hab_14, ems_habilitation.hab_15,
   ems_hopital.admin_ems AS admin
  FROM
    ems_hopital, personnes, ems_grade, ems_habilitation
  WHERE
    personnes.id = ems_hopital.personne  AND ems_hopital.rang = ems_grade.id AND ems_habilitation.matricule = ems_hopital.id
  ORDER BY
    ems_grade.`position` DESC;

CREATE OR REPLACE VIEW info_intervention AS
  SELECT
    ems_intervention.id AS inter_id, personnes.id AS id_civil, personnes.nom, personnes.prenom AS prenom, ems_intervention.numero_intervention AS num_inter,
    ems_liste_intervention.intitule AS nom_inter, ems_intervention.enregistrer_par AS enregistre_par, ems_intervention.enregistrer_le AS enregistrer_le, ems_intervention.enregistrer_name AS enregistrer_name,
    ems_intervention.remarque AS rapport
	FROM
		ems_intervention, personnes, ems_liste_intervention
	where
		ems_intervention.personne = personnes.id AND ems_liste_intervention.id = ems_intervention.numero_intervention
	ORDER BY inter_id;

CREATE OR REPLACE VIEW info_rapport AS
	SELECT
		lspd_rapport.id, info_lspd.grade, info_lspd.nom, info_lspd.photo, lspd_rapport.titre, lspd_rapport.editeur, info_lspd.lspd_id
	FROM
		info_lspd, lspd_rapport
	WHERE
		lspd_rapport.concerne = info_lspd.lspd_id;

CREATE OR REPLACE VIEW info_ordonnance AS
	SELECT
		ems_ordonnance.id AS ordonnance_id, ems_details_ordonnance.nom_medicament AS id_med, ems_liste_medicament.nom AS nom_med, ems_liste_medicament.description AS description_med,
		ems_details_ordonnance.quantite AS quantite, ems_details_ordonnance.periode AS periode, ems_details_ordonnance.frequence AS frequence
	FROM
		ems_ordonnance, ems_details_ordonnance, ems_liste_medicament
	WHERE
		ems_ordonnance.id = ems_details_ordonnance.n_ordonnance AND ems_details_ordonnance.nom_medicament = ems_liste_medicament.id;

CREATE OR REPLACE VIEW info_arme AS
	SELECT
		lspd_liste_arme.nom AS nom, lspd_arme.numero, lspd_categorie_arme.nom AS categorie, lspd_categorie_arme.prefix AS prefix,
		lspd_arme.proprio AS proprio, lspd_arme.enregistrer_par AS matricule, lspd_arme.enregistrer_name AS matricule_name, lspd_liste_arme.photo AS photo
	FROM
		lspd_liste_arme, lspd_arme, lspd_categorie_arme
	WHERE
		lspd_arme.`type` = lspd_liste_arme.id AND lspd_liste_arme.categorie = lspd_categorie_arme.id;