-- phpMyAdmin SQL Dump
-- version 5.2.0
-- https://www.phpmyadmin.net/
--
-- Hôte : 127.0.0.1:3306
-- Généré le : jeu. 12 mars 2026 à 12:12
-- Version du serveur : 8.0.31
-- Version de PHP : 8.0.26

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Base de données : `salle_sport_colvert`
--
CREATE DATABASE IF NOT EXISTS `salle_sport_colvert` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE `salle_sport_colvert`;

-- --------------------------------------------------------

--
-- Structure de la table `abonnement`
--

DROP TABLE IF EXISTS `abonnement`;
CREATE TABLE IF NOT EXISTS `abonnement` (
  `id_abonnement` int NOT NULL AUTO_INCREMENT,
  `id_adherent` int NOT NULL,
  `id_type_abo` int NOT NULL,
  `date_debut` date NOT NULL,
  `date_fin` date NOT NULL,
  `prix_paye` decimal(10,2) NOT NULL,
  `etat` enum('Valide','Expiré','Annulé') COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'Valide',
  PRIMARY KEY (`id_abonnement`),
  KEY `id_adherent` (`id_adherent`),
  KEY `id_type_abo` (`id_type_abo`),
  KEY `idx_date_fin` (`date_fin`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Déchargement des données de la table `abonnement`
--

INSERT INTO `abonnement` (`id_abonnement`, `id_adherent`, `id_type_abo`, `date_debut`, `date_fin`, `prix_paye`, `etat`) VALUES
(2, 2, 1, '2024-09-01', '2024-09-30', '49.99', 'Valide'),
(3, 3, 2, '2024-03-01', '2024-05-29', '129.99', 'Valide'),
(4, 4, 9, '2023-11-10', '2024-11-09', '349.99', 'Valide'),
(5, 5, 1, '2024-04-05', '2024-05-04', '49.99', 'Expiré'),
(6, 6, 6, '2023-09-01', '2024-08-31', '599.99', 'Expiré'),
(7, 7, 5, '2024-01-25', '2024-01-31', '19.99', 'Expiré'),
(8, 8, 7, '2024-08-15', '2024-09-14', '69.99', 'Valide'),
(9, 9, 3, '2023-12-01', '2024-11-30', '399.99', 'Valide'),
(10, 10, 1, '2024-02-29', '2024-03-30', '49.99', 'Expiré');

-- --------------------------------------------------------

--
-- Structure de la table `activite`
--

DROP TABLE IF EXISTS `activite`;
CREATE TABLE IF NOT EXISTS `activite` (
  `id_activite` int NOT NULL AUTO_INCREMENT,
  `nom_activite` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
  `description` text COLLATE utf8mb4_general_ci,
  `difficulte` enum('Facile','Moyen','Difficile') COLLATE utf8mb4_general_ci NOT NULL,
  PRIMARY KEY (`id_activite`),
  UNIQUE KEY `nom_activite` (`nom_activite`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Déchargement des données de la table `activite`
--

INSERT INTO `activite` (`id_activite`, `nom_activite`, `description`, `difficulte`) VALUES
(1, 'Zumba', 'Cours de danse fitness énergique.', 'Moyen'),
(2, 'Musculation Libre', 'Accès à l\'espace de musculation sans encadrement.', 'Facile'),
(3, 'Body Sculpt', 'Renforcement musculaire complet.', 'Moyen'),
(4, 'Yoga Doux', 'Séance de yoga axée sur la relaxation et l\'étirement.', 'Facile'),
(5, 'Circuit Training', 'Entraînement en circuit à haute intensité.', 'Difficile'),
(6, 'RPM', 'Cours de vélo en salle.', 'Difficile'),
(7, 'Pilates', 'Méthode de renforcement des muscles profonds.', 'Moyen'),
(8, 'Boxe Fitness', 'Cardio et techniques de boxe.', 'Difficile'),
(9, 'Aquagym', 'Gymnastique aquatique.', 'Facile'),
(10, 'Stretching', 'Séance d\'étirements pour la souplesse.', 'Facile');

-- --------------------------------------------------------

--
-- Structure de la table `adherent`
--

DROP TABLE IF EXISTS `adherent`;
CREATE TABLE IF NOT EXISTS `adherent` (
  `id_adherent` int NOT NULL AUTO_INCREMENT,
  `nom` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
  `prenom` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
  `email` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
  `mot_de_passe` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `telephone` varchar(15) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `date_inscription` date NOT NULL,
  `statut` enum('Actif','Inactif','Suspendu') COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'Actif',
  PRIMARY KEY (`id_adherent`),
  UNIQUE KEY `email` (`email`),
  KEY `idx_nom_prenom` (`nom`,`prenom`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Déchargement des données de la table `adherent`
--

INSERT INTO `adherent` (`id_adherent`, `nom`, `prenom`, `email`, `mot_de_passe`, `telephone`, `date_inscription`, `statut`) VALUES
(2, 'Martin', 'Bernard', 'bernard.martin@mail.com', NULL, '0605060708', '2024-02-20', 'Actif'),
(3, 'Petit', 'Chloé', 'chloe.petit@mail.com', NULL, '0609101112', '2024-03-01', 'Actif'),
(4, 'Lefevre', 'David', 'david.lefevre@mail.com', NULL, '0613141516', '2023-11-10', 'Actif'),
(5, 'Leroy', 'Emma', 'emma.leroy@mail.com', NULL, '0617181920', '2024-04-05', 'Actif'),
(6, 'Moreau', 'Franck', 'franck.moreau@mail.com', NULL, '0621222324', '2023-09-01', 'Actif'),
(7, 'Fournier', 'Géraldine', 'geraldine.fournier@mail.com', NULL, '0625262728', '2024-01-25', 'Inactif'),
(8, 'Roux', 'Hugo', 'hugo.roux@mail.com', NULL, '0629303132', '2024-05-12', 'Actif'),
(9, 'Dupont', 'Inès', 'ines.dupont@mail.com', NULL, '0633343536', '2023-12-01', 'Actif'),
(10, 'Durand', 'Julien', 'julien.durand@mail.com', NULL, '0637383940', '2024-02-29', 'Actif');

-- --------------------------------------------------------

--
-- Structure de la table `coach`
--

DROP TABLE IF EXISTS `coach`;
CREATE TABLE IF NOT EXISTS `coach` (
  `id_coach` int NOT NULL AUTO_INCREMENT,
  `nom` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
  `prenom` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
  `specialite` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `telephone` varchar(15) COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`id_coach`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Déchargement des données de la table `coach`
--

INSERT INTO `coach` (`id_coach`, `nom`, `prenom`, `specialite`, `telephone`) VALUES
(1, 'Garcia', 'Sophie', 'Yoga & Pilates', '0701010101'),
(2, 'Simon', 'Marc', 'Musculation & HIIT', '0702020202'),
(3, 'Robert', 'Laura', 'Cardio & Zumba', '0703030303'),
(4, 'Michel', 'Alex', 'Crossfit', '0704040404'),
(5, 'Perrin', 'Camille', 'Natation', '0705050505'),
(6, 'Richard', 'Thomas', 'Bodybuilding', '0706060606'),
(7, 'Blanc', 'Julie', 'Boxe', '0707070707'),
(8, 'Girard', 'Paul', 'Nutrition & Coaching Perso', '0708080808'),
(9, 'Leroux', 'Manon', 'Fitness Danse', '0709090909'),
(10, 'Renard', 'Lucas', 'Stretching & Rééducation', '0710101010');

-- --------------------------------------------------------

--
-- Structure de la table `reservation`
--

DROP TABLE IF EXISTS `reservation`;
CREATE TABLE IF NOT EXISTS `reservation` (
  `id_reservation` int NOT NULL AUTO_INCREMENT,
  `id_adherent` int NOT NULL,
  `id_seance` int NOT NULL,
  `date_reservation` datetime NOT NULL,
  `statut_reservation` enum('Confirmée','Annulée') COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'Confirmée',
  PRIMARY KEY (`id_reservation`),
  UNIQUE KEY `uk_adherent_seance` (`id_adherent`,`id_seance`),
  KEY `id_seance` (`id_seance`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Déchargement des données de la table `reservation`
--

INSERT INTO `reservation` (`id_reservation`, `id_adherent`, `id_seance`, `date_reservation`, `statut_reservation`) VALUES
(2, 2, 1, '2024-09-26 14:00:00', 'Confirmée'),
(3, 3, 2, '2024-09-27 08:00:00', 'Confirmée'),
(4, 4, 3, '2024-09-27 15:00:00', 'Confirmée'),
(5, 8, 3, '2024-09-28 09:00:00', 'Confirmée'),
(6, 9, 4, '2024-09-28 11:30:00', 'Confirmée'),
(8, 3, 6, '2024-09-28 17:00:00', 'Confirmée'),
(9, 4, 7, '2024-09-28 19:00:00', 'Confirmée'),
(10, 10, 1, '2024-09-29 08:00:00', 'Annulée');

-- --------------------------------------------------------

--
-- Structure de la table `seance`
--

DROP TABLE IF EXISTS `seance`;
CREATE TABLE IF NOT EXISTS `seance` (
  `id_seance` int NOT NULL AUTO_INCREMENT,
  `id_activite` int NOT NULL,
  `id_coach` int DEFAULT NULL,
  `date_heure_debut` datetime NOT NULL,
  `duree_minutes` int NOT NULL,
  `capacite_max` int NOT NULL,
  PRIMARY KEY (`id_seance`),
  KEY `id_activite` (`id_activite`),
  KEY `id_coach` (`id_coach`),
  KEY `idx_date_heure_debut` (`date_heure_debut`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Déchargement des données de la table `seance`
--

INSERT INTO `seance` (`id_seance`, `id_activite`, `id_coach`, `date_heure_debut`, `duree_minutes`, `capacite_max`) VALUES
(1, 1, 3, '2024-09-29 18:00:00', 60, 25),
(2, 4, 1, '2024-09-30 09:30:00', 75, 15),
(3, 5, 4, '2024-09-30 19:00:00', 45, 20),
(4, 3, 2, '2024-10-01 12:30:00', 60, 18),
(5, 6, 2, '2024-10-01 18:30:00', 50, 20),
(6, 7, 1, '2024-10-02 11:00:00', 60, 15),
(7, 8, 7, '2024-10-02 19:30:00', 60, 12),
(8, 10, 10, '2024-10-03 10:00:00', 45, 10),
(9, 1, 3, '2024-10-03 18:00:00', 60, 25),
(10, 9, 5, '2024-10-04 17:00:00', 60, 30);

-- --------------------------------------------------------

--
-- Structure de la table `typeabonnement`
--

DROP TABLE IF EXISTS `typeabonnement`;
CREATE TABLE IF NOT EXISTS `typeabonnement` (
  `id_type_abo` int NOT NULL AUTO_INCREMENT,
  `libelle` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
  `duree_jours` int NOT NULL,
  `prix_initial` decimal(10,2) NOT NULL,
  PRIMARY KEY (`id_type_abo`),
  UNIQUE KEY `libelle` (`libelle`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Déchargement des données de la table `typeabonnement`
--

INSERT INTO `typeabonnement` (`id_type_abo`, `libelle`, `duree_jours`, `prix_initial`) VALUES
(1, 'Mensuel Standard', 30, '49.99'),
(2, 'Trimestriel Gold', 90, '129.99'),
(3, 'Annuel Premium', 365, '399.99'),
(4, 'Mensuel Étudiant', 30, '39.99'),
(5, 'Hebdomadaire Découverte', 7, '19.99'),
(6, 'Annuel Famille', 365, '599.99'),
(7, 'Mensuel Illimité', 30, '69.99'),
(8, 'Trimestriel Zen', 90, '149.99'),
(9, 'Annuel Classique', 365, '349.99'),
(10, 'Carte 10 Séances', 180, '99.99'),
(11, 'carte 20 séances', 360, '150.00');

-- --------------------------------------------------------

--
-- Structure de la table `utilisateur`
--

DROP TABLE IF EXISTS `utilisateur`;
CREATE TABLE IF NOT EXISTS `utilisateur` (
  `id_user` int NOT NULL AUTO_INCREMENT,
  `login` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
  `mot_de_passe` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `role` enum('Admin','Coach') COLLATE utf8mb4_general_ci NOT NULL,
  PRIMARY KEY (`id_user`),
  UNIQUE KEY `login` (`login`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Déchargement des données de la table `utilisateur`
--

INSERT INTO `utilisateur` (`id_user`, `login`, `mot_de_passe`, `role`) VALUES
(1, 'admin_collvert', '$2y$10$UnMotDePasseHacheEtLong...', 'Admin'),
(3, 'admin2', '$2y$10$393NutuXsBc95mEOgyFH4.J6bNLSTh///2Qte.0amtIfguf6Tbu2i', 'Admin');

--
-- Contraintes pour les tables déchargées
--

--
-- Contraintes pour la table `abonnement`
--
ALTER TABLE `abonnement`
  ADD CONSTRAINT `abonnement_ibfk_1` FOREIGN KEY (`id_adherent`) REFERENCES `adherent` (`id_adherent`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `abonnement_ibfk_2` FOREIGN KEY (`id_type_abo`) REFERENCES `typeabonnement` (`id_type_abo`) ON DELETE RESTRICT ON UPDATE CASCADE;

--
-- Contraintes pour la table `reservation`
--
ALTER TABLE `reservation`
  ADD CONSTRAINT `reservation_ibfk_1` FOREIGN KEY (`id_adherent`) REFERENCES `adherent` (`id_adherent`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `reservation_ibfk_2` FOREIGN KEY (`id_seance`) REFERENCES `seance` (`id_seance`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Contraintes pour la table `seance`
--
ALTER TABLE `seance`
  ADD CONSTRAINT `seance_ibfk_1` FOREIGN KEY (`id_activite`) REFERENCES `activite` (`id_activite`) ON DELETE RESTRICT ON UPDATE CASCADE,
  ADD CONSTRAINT `seance_ibfk_2` FOREIGN KEY (`id_coach`) REFERENCES `coach` (`id_coach`) ON DELETE SET NULL ON UPDATE CASCADE;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
