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,
  `discord_id` varchar(32) NOT NULL,
  `nom` varchar(32) NOT NULL,
  `prenom` varchar(32) NOT NULL,
  `phone` varchar(32) NOT NULL,
  `age_IG` INTEGER NOT NULL,
  `age_IRL` INTEGER NOT NULL,
  `tmps_serveur` INTEGER NOT NULL,
  `tmps_life` INTEGER NOT NULL,
  `ancien_flic` ENUM('0','1') NOT NULL DEFAULT '0',
  `detail_flic` varchar(32),
  `dispo_ecole` ENUM('0','1') NOT NULL DEFAULT '0',
  `detail_ecole` varchar(64),
  `dispo_vacance` ENUM('0','1') NOT NULL DEFAULT '0',
  `detail_vacance` varchar(64),
  `dispo_travail` ENUM('0','1') NOT NULL DEFAULT '0',
  `detail_travail` varchar(64),
  `objectif_lspd` varchar(512),
  `motivation_lspd` TEXT,
  `reponse_concat` 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,
  `discord_id` varchar(32) NOT NULL,
  `nom` varchar(32) NOT NULL,
  `prenom` varchar(32) NOT NULL,
  `phone` varchar(32) NOT NULL,
  `age_IG` INTEGER NOT NULL,
  `age_IRL` INTEGER NOT NULL,
  `tmps_serveur` INTEGER NOT NULL,
  `tmps_life` INTEGER NOT NULL,
  `ancien_flic` ENUM('0','1') NOT NULL DEFAULT '0',
  `detail_flic` varchar(32),
  `dispo_ecole` ENUM('0','1') NOT NULL DEFAULT '0',
  `detail_ecole` varchar(64),
  `dispo_vacance` ENUM('0','1') NOT NULL DEFAULT '0',
  `detail_vacance` varchar(64),
  `dispo_travail` ENUM('0','1') NOT NULL DEFAULT '0',
  `detail_travail` varchar(64),
  `objectif_lspd` varchar(512),
  `motivation_lspd` TEXT,
  `reponse_concat` 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;
