-- ============================================================= -- GestElag — Schéma de la base de données -- Encodage : utf8mb4 (accents FR) -- À importer dans phpMyAdmin (créez d'abord une base "gestelag") -- ============================================================= SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------- Utilisateurs (connexion à l'appli) ---------- DROP TABLE IF EXISTS users; CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, nom VARCHAR(100) NOT NULL, email VARCHAR(150) NOT NULL UNIQUE, mot_de_passe VARCHAR(255) NOT NULL, -- hash password_hash() role ENUM('admin','chef','ouvrier') DEFAULT 'admin', cree_le TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ---------- Clients ---------- DROP TABLE IF EXISTS clients; CREATE TABLE clients ( id INT AUTO_INCREMENT PRIMARY KEY, nom VARCHAR(150) NOT NULL, type_client ENUM('particulier','entreprise','collectivite','syndic') DEFAULT 'particulier', telephone VARCHAR(30), email VARCHAR(150), adresse VARCHAR(255), cree_le TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ---------- Chantiers ---------- DROP TABLE IF EXISTS chantiers; CREATE TABLE chantiers ( id INT AUTO_INCREMENT PRIMARY KEY, client_id INT NOT NULL, libelle VARCHAR(150) NOT NULL, type_prestation ENUM('elagage','abattage','rognage','haubanage', 'debroussaillage','entretien_jardin','riviere', 'genie_vegetal','amenagement_bois','amenagement') NOT NULL, adresse VARCHAR(255), latitude DECIMAL(10,7) NULL, -- pour la carte (Leaflet) longitude DECIMAL(10,7) NULL, date_debut DATE NULL, date_fin DATE NULL, statut ENUM('planifie','en_cours','termine','annule') DEFAULT 'planifie', notes TEXT NULL, cree_le TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (client_id) REFERENCES clients(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ---------- Employés ---------- DROP TABLE IF EXISTS employes; CREATE TABLE employes ( id INT AUTO_INCREMENT PRIMARY KEY, nom VARCHAR(100) NOT NULL, prenom VARCHAR(100) NOT NULL, telephone VARCHAR(30), contrat ENUM('cdi','cdd','interim','apprenti') DEFAULT 'cdi', cout_horaire DECIMAL(8,2) DEFAULT 0, statut ENUM('actif','inactif') DEFAULT 'actif', cree_le TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ---------- Qualifications / habilitations ---------- -- Essentiel dans ce métier : CACES nacelle, habilitation électrique (ATEX), -- certificat de grimpeur-élagueur, etc. DROP TABLE IF EXISTS qualifications; CREATE TABLE qualifications ( id INT AUTO_INCREMENT PRIMARY KEY, libelle VARCHAR(120) NOT NULL, -- ex: "CACES nacelle B" code VARCHAR(40) -- ex: "CACES_B" ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- Lien employé <-> qualification (avec date d'expiration) DROP TABLE IF EXISTS employe_qualifications; CREATE TABLE employe_qualifications ( id INT AUTO_INCREMENT PRIMARY KEY, employe_id INT NOT NULL, qualification_id INT NOT NULL, date_obtention DATE NULL, date_expiration DATE NULL, -- pour alerter avant échéance FOREIGN KEY (employe_id) REFERENCES employes(id) ON DELETE CASCADE, FOREIGN KEY (qualification_id) REFERENCES qualifications(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ---------- Indisponibilités (congés, arrêts) ---------- DROP TABLE IF EXISTS indisponibilites; CREATE TABLE indisponibilites ( id INT AUTO_INCREMENT PRIMARY KEY, employe_id INT NOT NULL, date_debut DATE NOT NULL, date_fin DATE NOT NULL, motif ENUM('conge','arret','formation','autre') DEFAULT 'conge', FOREIGN KEY (employe_id) REFERENCES employes(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ---------- Matériel / engins ---------- DROP TABLE IF EXISTS materiel; CREATE TABLE materiel ( id INT AUTO_INCREMENT PRIMARY KEY, nom VARCHAR(120) NOT NULL, -- ex: "Nacelle 16m" type VARCHAR(80), -- nacelle, broyeur, rogneuse... immatriculation VARCHAR(40) NULL, statut ENUM('disponible','en_service','maintenance','hs') DEFAULT 'disponible', cout_horaire DECIMAL(8,2) DEFAULT 0, cree_le TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ---------- Maintenance du matériel ---------- DROP TABLE IF EXISTS maintenance; CREATE TABLE maintenance ( id INT AUTO_INCREMENT PRIMARY KEY, materiel_id INT NOT NULL, date_op DATE NOT NULL, type_op ENUM('controle','reparation','revision') DEFAULT 'controle', description TEXT, FOREIGN KEY (materiel_id) REFERENCES materiel(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ---------- Interventions (tâches datées d'un chantier) ---------- DROP TABLE IF EXISTS interventions; CREATE TABLE interventions ( id INT AUTO_INCREMENT PRIMARY KEY, chantier_id INT NOT NULL, libelle VARCHAR(150) NOT NULL, date_prevue DATE NULL, duree_heures DECIMAL(5,1) DEFAULT 0, qualification_requise INT NULL, -- qualif obligatoire éventuelle FOREIGN KEY (chantier_id) REFERENCES chantiers(id) ON DELETE CASCADE, FOREIGN KEY (qualification_requise) REFERENCES qualifications(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- ---------- Affectations (LE PIVOT du planning) ---------- -- Relie une intervention <-> un employé <-> (optionnel) un matériel, sur un créneau DROP TABLE IF EXISTS affectations; CREATE TABLE affectations ( id INT AUTO_INCREMENT PRIMARY KEY, intervention_id INT NOT NULL, employe_id INT NOT NULL, materiel_id INT NULL, date_debut DATETIME NOT NULL, date_fin DATETIME NOT NULL, role VARCHAR(80) NULL, -- ex: "grimpeur", "homme de pied" FOREIGN KEY (intervention_id) REFERENCES interventions(id) ON DELETE CASCADE, FOREIGN KEY (employe_id) REFERENCES employes(id) ON DELETE CASCADE, FOREIGN KEY (materiel_id) REFERENCES materiel(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; SET FOREIGN_KEY_CHECKS = 1; -- ============================================================= -- Jeu de données de démonstration -- ============================================================= INSERT INTO clients (nom, type_client, telephone, email, adresse) VALUES ('Mairie de Gap', 'collectivite', '0492000000', 'contact@ville-gap.fr', 'Rue Colonel Roux, 05000 Gap'), ('M. Bernard Durand', 'particulier', '0611223344', 'b.durand@email.fr', '12 chemin des Vignes, 05000 Gap'), ('Syndic Les Terrasses', 'syndic', '0492111213', 'gestion@lesterrasses.fr','8 av. des Alpes, 05000 Gap'), ('SARL Bois & Nature', 'entreprise', '0492445566', 'contact@boisnature.fr', 'ZA La Justice, 05000 Gap'); INSERT INTO chantiers (client_id, libelle, type_prestation, adresse, date_debut, date_fin, statut) VALUES (1, 'Élagage platanes parc municipal', 'elagage', 'Parc de la Pépinière, Gap', '2026-07-07', '2026-07-09', 'planifie'), (2, 'Abattage cèdre dangereux', 'abattage', '12 chemin des Vignes, Gap', '2026-07-10', '2026-07-10', 'planifie'), (3, 'Entretien espaces verts résidence','entretien_jardin','8 av. des Alpes, Gap', '2026-07-03', '2026-07-04', 'en_cours'), (1, 'Débroussaillage berges Luye', 'riviere', 'Berges de la Luye, Gap', '2026-06-20', '2026-06-25', 'termine'); INSERT INTO employes (nom, prenom, telephone, contrat, cout_horaire, statut) VALUES ('Martin', 'Luc', '0600000001', 'cdi', 28.00, 'actif'), ('Petit', 'Thomas', '0600000002', 'cdi', 25.00, 'actif'), ('Roche', 'Kevin', '0600000003', 'cdd', 22.00, 'actif'), ('Faure', 'Julien', '0600000004', 'interim', 21.00, 'actif'); INSERT INTO qualifications (libelle, code) VALUES ('CACES nacelle B', 'CACES_B'), ('Certificat grimpeur-élagueur', 'GRIMPEUR'), ('Habilitation électrique (ATEX)', 'ATEX'), ('Permis EB (remorque)', 'PERMIS_EB'); INSERT INTO materiel (nom, type, immatriculation, statut, cout_horaire) VALUES ('Nacelle 16m', 'nacelle', 'AB-123-CD', 'disponible', 35.00), ('Broyeur de branches','broyeur', NULL, 'disponible', 15.00), ('Rogneuse de souche', 'rogneuse', NULL, 'maintenance',18.00), ('Camion benne', 'vehicule', 'EF-456-GH', 'disponible', 12.00);