-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Hôte : 127.0.0.1:3306
-- Généré le : mer. 04 juin 2025 à 09:14
-- Version du serveur : 8.3.0
-- Version de PHP : 8.2.18

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

-- Création de la base de données mediatek si elle n'existe pas
CREATE DATABASE IF NOT EXISTS mediatek DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

-- Utilisation de la base
USE mediatek;

/*!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 : `mediatek`
--

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

--
-- Structure de la table `absence`
--

DROP TABLE IF EXISTS `absence`;
CREATE TABLE `absence` (
  `idpersonnel` int NOT NULL,
  `datedebut` datetime NOT NULL,
  `datefin` datetime DEFAULT NULL,
  `idmotif` int NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

INSERT INTO `absence` (`idpersonnel`, `datedebut`, `datefin`, `idmotif`) VALUES
(9, '2025-05-13 19:35:26', '2025-07-26 00:43:56', 3),
(7, '2025-04-25 15:46:59', '2025-09-15 14:19:33', 3),
(9, '2025-03-13 22:15:56', '2025-07-15 02:07:03', 1),
(9, '2025-04-01 13:30:46', '2025-08-14 00:31:13', 3),
(4, '2025-05-20 03:26:29', '2025-07-22 05:09:43', 3),
(10, '2025-03-16 21:48:30', '2025-11-19 15:58:33', 3),
(2, '2025-05-17 00:52:41', '2025-08-07 03:36:50', 4),
(2, '2025-04-06 15:36:20', '2025-11-22 18:35:19', 3),
(6, '2025-05-04 01:05:50', '2025-07-06 03:59:58', 1),
(10, '2025-05-05 08:43:08', '2025-11-19 13:18:57', 4),
(7, '2025-04-15 16:55:47', '2025-11-15 12:40:19', 2),
(9, '2025-05-08 22:22:10', '2025-07-04 21:36:25', 2),
(3, '2025-04-15 06:12:30', '2025-06-14 17:03:55', 3),
(2, '2025-04-28 00:17:05', '2025-11-11 04:24:04', 2),
(7, '2025-04-30 08:23:47', '2025-11-23 01:26:08', 2),
(4, '2025-04-14 18:28:18', '2025-10-11 23:17:23', 2),
(3, '2025-03-27 10:52:47', '2025-11-27 11:31:04', 2),
(7, '2025-03-26 14:52:47', '2025-08-24 03:43:11', 4),
(8, '2025-05-11 11:19:53', '2025-09-09 07:24:46', 2),
(5, '2025-05-04 21:27:38', '2025-09-12 07:38:07', 1),
(7, '2025-04-20 13:40:07', '2025-07-23 15:29:32', 3),
(4, '2025-05-02 17:25:47', '2025-08-13 00:24:45', 4),
(9, '2025-03-30 23:35:42', '2025-10-22 18:18:06', 2),
(5, '2025-04-23 16:43:10', '2025-06-13 02:10:38', 3),
(4, '2025-04-05 06:10:39', '2025-09-12 12:45:00', 1),
(3, '2025-05-15 16:31:09', '2025-09-27 19:31:41', 3),
(5, '2025-04-01 07:32:27', '2025-09-01 04:26:42', 3),
(3, '2025-03-27 02:17:40', '2025-06-14 12:47:39', 1),
(8, '2025-04-08 17:56:06', '2025-06-28 04:51:27', 3),
(9, '2025-03-17 20:50:28', '2025-07-18 21:24:46', 3),
(4, '2025-03-31 22:19:43', '2025-09-13 20:06:41', 1),
(9, '2025-03-27 20:26:13', '2025-07-15 07:39:35', 4),
(6, '2025-04-30 12:52:51', '2025-06-29 09:53:18', 3),
(5, '2025-03-30 14:03:26', '2025-11-24 18:51:20', 2),
(7, '2025-04-16 01:44:37', '2025-06-14 22:01:29', 3),
(6, '2025-04-24 15:47:08', '2025-09-03 21:53:49', 3),
(3, '2025-05-15 02:57:22', '2025-08-29 13:57:01', 2),
(7, '2025-05-06 23:36:26', '2025-08-25 05:17:21', 3),
(3, '2025-04-08 13:05:21', '2025-06-23 04:37:39', 3),
(6, '2025-04-16 09:55:29', '2025-09-01 05:39:53', 4),
(2, '2025-04-20 22:21:41', '2025-06-30 02:21:54', 2),
(6, '2025-04-09 23:59:17', '2025-08-16 09:53:33', 3),
(17, '2005-11-04 00:00:00', '2005-11-18 00:00:00', 3),
(4, '2025-05-04 17:59:47', '2025-08-11 00:54:52', 3),
(10, '2025-04-09 23:13:28', '2025-11-13 23:14:48', 1),
(5, '2025-04-06 16:16:36', '2025-07-03 10:05:30', 2),
(8, '2025-03-29 07:31:45', '2025-11-09 00:17:49', 4),
(5, '2025-05-10 16:00:39', '2025-06-10 07:03:38', 1),
(1, '2025-03-30 16:28:31', '2025-09-07 15:58:17', 3),
(2, '2025-05-16 07:45:18', '2025-08-25 09:02:13', 1),
(11, '2025-04-02 00:00:00', '2025-04-06 00:00:00', 3),
(1, '2024-05-02 00:00:00', '2024-05-03 00:00:00', 2),
(15, '2022-12-15 00:00:00', '2022-12-18 00:00:00', 4),
(15, '2023-08-19 00:00:00', '2023-08-23 00:00:00', 2),
(15, '2023-09-20 00:00:00', '2023-09-21 00:00:00', 3),
(2, '2025-12-02 00:00:00', '2026-12-02 00:00:00', 4),
(17, '2023-01-18 00:00:00', '2023-01-18 00:00:00', 3),
(17, '2025-06-03 00:00:00', '2025-06-15 00:00:00', 2);

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

--
-- Structure de la table `motif`
--

DROP TABLE IF EXISTS `motif`;
CREATE TABLE `motif` (
  `idmotif` int NOT NULL,
  `libelle` varchar(128) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

INSERT INTO `motif` (`idmotif`, `libelle`) VALUES
(1, 'vacances'),
(2, 'maladie'),
(3, 'motif familial'),
(4, 'congé parental');

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

--
-- Structure de la table `personnel`
--

DROP TABLE IF EXISTS `personnel`;
CREATE TABLE `personnel` (
  `idpersonnel` int NOT NULL,
  `nom` varchar(50) DEFAULT NULL,
  `prenom` varchar(50) DEFAULT NULL,
  `tel` varchar(15) DEFAULT NULL,
  `mail` varchar(128) DEFAULT NULL,
  `idservice` int NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

INSERT INTO `personnel` (`idpersonnel`, `nom`, `prenom`, `tel`, `mail`, `idservice`) VALUES
(1, 'Forbes', 'Orli', '03 61 54 24 54', 'orli500@yahoo.couk', 3),
(2, 'Hurst', 'Brock', '03 19 94 42 63', 'brock1824@google.org', 1),
(3, 'Watsonregregrth', 'Finnh', '07 61 52 36 25', 'finn6569@aol.net', 2),
(4, 'Cooley', 'Gabriel', '05 03 88 40 43', 'gabriel1771@google.org', 1),
(5, 'Mcdowell', 'Keiko', '03 54 13 68 29', 'keiko9844@hotmail.couk', 3),
(6, 'May', 'Gray', '09 50 41 64 26', 'gray1072@protonmail.edu', 1),
(7, 'Holloway', 'Emerson', '02 12 67 55 11', 'emerson7107@hotmail.net', 1),
(8, 'Wagner', 'Emery', '09 46 11 15 83', 'emery@outlook.net', 2),
(9, 'Stuart', 'Upton', '05 52 31 63 56', 'upton4520@icloud.ca', 2),
(10, 'Mckenzie', 'Nina', '01 17 66 88 44', 'nina7538@hotmail.couk', 1),
(11, 'JENNIE', 'JENNIE', 'JENNIE', 'JENNIE', 1),
(17, 'Philippe', 'Erwann', '06 47 45 31 84', 'erwann.philippe2@gmail.com', 1);

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

--
-- Structure de la table `responsable`
--

DROP TABLE IF EXISTS `responsable`;
CREATE TABLE `responsable` (
  `login` varchar(64) NOT NULL,
  `pwd` varchar(64) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

INSERT INTO `responsable` (`login`, `pwd`) VALUES
('admin', '5e884898da28047151d0e56f8dc6292773603d0d6aabbdd62a11ef721d1542d8');

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

--
-- Structure de la table `service`
--

DROP TABLE IF EXISTS `service`;
CREATE TABLE `service` (
  `idservice` int NOT NULL,
  `nom` varchar(50) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

INSERT INTO `service` (`idservice`, `nom`) VALUES
(1, 'administratif'),
(2, 'médiation culturelle'),
(3, 'prêt');

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

--
-- Index pour la table `absence`
--
ALTER TABLE `absence`
  ADD PRIMARY KEY (`idpersonnel`,`datedebut`),
  ADD KEY `idmotif` (`idmotif`);

--
-- Index pour la table `motif`
--
ALTER TABLE `motif`
  ADD PRIMARY KEY (`idmotif`);

--
-- Index pour la table `personnel`
--
ALTER TABLE `personnel`
  ADD PRIMARY KEY (`idpersonnel`),
  ADD KEY `idservice` (`idservice`);

--
-- Index pour la table `responsable`
--
ALTER TABLE `responsable`
  ADD PRIMARY KEY (`login`);

--
-- Index pour la table `service`
--
ALTER TABLE `service`
  ADD PRIMARY KEY (`idservice`);

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

--
-- AUTO_INCREMENT pour la table `motif`
--
ALTER TABLE `motif`
  MODIFY `idmotif` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- AUTO_INCREMENT pour la table `personnel`
--
ALTER TABLE `personnel`
  MODIFY `idpersonnel` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=19;

--
-- AUTO_INCREMENT pour la table `service`
--
ALTER TABLE `service`
  MODIFY `idservice` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;
COMMIT;

-- Création de l'utilisateur 'mediatek' avec mot de passe
CREATE USER 'mediatek'@'localhost' IDENTIFIED BY 'motDePasseFort';

-- Attribution de tous les droits sur la base 'mediatek' à l'utilisateur
GRANT ALL PRIVILEGES ON mediatek.* TO 'mediatek'@'localhost';

-- Rechargement des privilèges
FLUSH PRIVILEGES;

/*!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 */;
