-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Hôte : 127.0.0.1
-- Généré le : mer. 04 juin 2025 à 10:25
-- Version du serveur : 10.4.32-MariaDB
-- Version de PHP : 8.2.12

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

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

--
-- Structure de la table `contact_a_visiter`
--

DROP TABLE IF EXISTS `contact_a_visiter`;
CREATE TABLE `contact_a_visiter` (
  `id_contact` int(7) NOT NULL,
  `nom_contact` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

--
-- Structure de la table `departements`
--

DROP TABLE IF EXISTS `departements`;
CREATE TABLE `departements` (
  `id_departement` int(11) NOT NULL,
  `nom_departement` varchar(255) NOT NULL,
  `code_departement` varchar(10) DEFAULT NULL,
  `lastmod_departement` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `creation_departement` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;


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

--
-- Structure de la table `departement_user`
--

DROP TABLE IF EXISTS `departement_user`;
CREATE TABLE `departement_user` (
  `id_departement_user` int(11) NOT NULL,
  `id_user` int(11) NOT NULL,
  `id_departement` int(11) NOT NULL,
  `lastmod_departement_user` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `creation_departement_user` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

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

--
-- Structure de la table `fournisseur`
--

DROP TABLE IF EXISTS `fournisseur`;
CREATE TABLE `fournisseur` (
  `id_fournisseur` int(7) NOT NULL,
  `nom_compagnie` varchar(255) NOT NULL,
  `activite` varchar(255) DEFAULT NULL,
  `email` varchar(100) NOT NULL,
  `tel` varchar(15) NOT NULL,
  `banni` tinyint(1) DEFAULT 0,
  `deleted` tinyint(1) DEFAULT 0,
  `observation` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

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

--
-- Structure de la table `pages`
--

DROP TABLE IF EXISTS `pages`;
CREATE TABLE `pages` (
  `id_url` int(32) NOT NULL,
  `nom_url` varchar(255) DEFAULT NULL,
  `comment_url` varchar(255) DEFAULT NULL,
  `creation` timestamp NOT NULL DEFAULT current_timestamp(),
  `lastmod` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci COMMENT='Pages du site web gestionvisiteur';

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

--
-- Structure de la table `pageviews`
--

DROP TABLE IF EXISTS `pageviews`;
CREATE TABLE `pageviews` (
  `id_pv` int(32) NOT NULL,
  `id_user` int(11) NOT NULL,
  `id_url` int(32) DEFAULT NULL,
  `url` text NOT NULL,
  `hostname` varchar(64) NOT NULL,
  `lastmod` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

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

--
-- Structure de la table `parametres`
--

DROP TABLE IF EXISTS `parametres`;
CREATE TABLE `parametres` (
  `nom_site_cap` varchar(250) DEFAULT NULL,
  `nom_site_non_cap` varchar(250) DEFAULT NULL,
  `nom_site_all_cap` varchar(250) DEFAULT NULL,
  `nomborne1` varchar(100) DEFAULT NULL,
  `nomborne2` varchar(100) DEFAULT NULL,
  `nomborne3` varchar(100) DEFAULT NULL,
  `nomborne4` varchar(100) DEFAULT NULL,
  `utiliser_mulitlangue` tinyint(1) DEFAULT NULL,
  `afficher_sidebar` tinyint(1) NOT NULL,
  `utiliser_mode_dark` tinyint(1) NOT NULL,
  `en_maintenance` tinyint(1) NOT NULL,
  `regrouper_menu` tinyint(1) NOT NULL,
  `email_support` varchar(100) DEFAULT NULL,
  `tel_support` varchar(15) DEFAULT NULL,
  `utiliser_etape` tinyint(1) NOT NULL DEFAULT 1,
  `utiliser_seul_date_debut` tinyint(1) NOT NULL DEFAULT 1,
  `affi_fraphe_stat_mon` tinyint(1) DEFAULT 0,
  `affi_fraphe_stat_annu` tinyint(1) DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

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

INSERT INTO `parametres` (`nom_site_cap`, `nom_site_non_cap`, `nom_site_all_cap`, `nomborne1`, `nomborne2`, `nomborne3`, `nomborne4`, `utiliser_mulitlangue`, `afficher_sidebar`, `utiliser_mode_dark`, `en_maintenance`, `regrouper_menu`, `email_support`, `tel_support`, `utiliser_etape`, `utiliser_seul_date_debut`, `affi_fraphe_stat_mon`, `affi_fraphe_stat_annu`) VALUES
('Gestion Représentants', 'gestion représentants', 'GESTION REPRÉSENTANT', 'drht44542', '10.48.191.109', '', '', 0, 0, 0, 0, 0, 'csi.drht@drht-mhi.org', '2123', 0, 0, 0, 0);

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

--
-- Structure de la table `roles`
--

DROP TABLE IF EXISTS `roles`;
CREATE TABLE `roles` (
  `id_role` int(11) NOT NULL,
  `role_name` varchar(256) NOT NULL,
  `role_comment` text DEFAULT NULL,
  `creation` timestamp NOT NULL DEFAULT current_timestamp(),
  `lastmod` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

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

INSERT INTO `roles` (`id_role`, `role_name`, `role_comment`, `creation`, `lastmod`) VALUES
(1, 'Administrateur', '', '2024-07-09 02:26:25', '2025-03-27 18:35:51'),
(2, 'Pilote', '', '2024-07-15 23:34:31', '2025-03-27 18:35:43'),
(3, 'Gestionnaire', '', '2024-07-15 23:35:06', '2025-02-18 20:21:24'),
(4, 'Développeur', '', '2025-03-27 18:30:45', '2025-03-27 18:35:59');

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

--
-- Structure de la table `roles_pages`
--

DROP TABLE IF EXISTS `roles_pages`;
CREATE TABLE `roles_pages` (
  `id_rp` int(11) NOT NULL,
  `id_role` int(11) NOT NULL,
  `id_url` int(11) NOT NULL,
  `creation` timestamp NOT NULL DEFAULT current_timestamp(),
  `lastmod` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

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

INSERT INTO `roles_pages` (`id_rp`, `id_role`, `id_url`, `creation`, `lastmod`) VALUES
(40, 0, 2, '2025-03-27 18:30:45', '2025-03-27 18:30:45'),
(41, 0, 24, '2025-03-27 18:30:45', '2025-03-27 18:30:45'),
(42, 0, 25, '2025-03-27 18:30:45', '2025-03-27 18:30:45'),
(43, 0, 26, '2025-03-27 18:30:45', '2025-03-27 18:30:45'),
(44, 0, 274, '2025-03-27 18:30:45', '2025-03-27 18:30:45'),
(45, 0, 1, '2025-03-27 18:30:45', '2025-03-27 18:30:45'),
(46, 0, 3, '2025-03-27 18:30:45', '2025-03-27 18:30:45'),
(47, 0, 4, '2025-03-27 18:30:45', '2025-03-27 18:30:45'),
(48, 8, 2, '2025-03-27 18:36:47', '2025-03-27 18:36:47'),
(49, 8, 1, '2025-03-27 18:36:47', '2025-03-27 18:36:47'),
(57, 9, 2, '2025-03-27 18:38:59', '2025-03-27 18:38:59'),
(58, 9, 24, '2025-03-27 18:38:59', '2025-03-27 18:38:59'),
(59, 9, 25, '2025-03-27 18:38:59', '2025-03-27 18:38:59'),
(60, 9, 26, '2025-03-27 18:38:59', '2025-03-27 18:38:59'),
(61, 9, 274, '2025-03-27 18:38:59', '2025-03-27 18:38:59'),
(62, 9, 1, '2025-03-27 18:38:59', '2025-03-27 18:38:59'),
(63, 9, 3, '2025-03-27 18:38:59', '2025-03-27 18:38:59'),
(64, 9, 4, '2025-03-27 18:38:59', '2025-03-27 18:38:59'),
(65, 9, 5, '2025-03-27 18:38:59', '2025-03-27 18:38:59'),
(66, 9, 270, '2025-03-27 18:38:59', '2025-03-27 18:38:59'),
(67, 9, 272, '2025-03-27 18:38:59', '2025-03-27 18:38:59'),
(68, 9, 13, '2025-03-27 18:38:59', '2025-03-27 18:38:59'),
(69, 9, 27, '2025-03-27 18:38:59', '2025-03-27 18:38:59'),
(70, 9, 28, '2025-03-27 18:38:59', '2025-03-27 18:38:59'),
(71, 9, 29, '2025-03-27 18:38:59', '2025-03-27 18:38:59'),
(72, 9, 6, '2025-03-27 18:38:59', '2025-03-27 18:38:59'),
(73, 9, 7, '2025-03-27 18:38:59', '2025-03-27 18:38:59'),
(96, 1, 2, '2025-05-30 15:17:36', '2025-05-30 15:17:36'),
(97, 1, 274, '2025-05-30 15:17:36', '2025-05-30 15:17:36'),
(98, 1, 1, '2025-05-30 15:17:36', '2025-05-30 15:17:36'),
(99, 1, 275, '2025-05-30 15:17:36', '2025-05-30 15:17:36'),
(100, 1, 3, '2025-05-30 15:17:36', '2025-05-30 15:17:36'),
(101, 1, 4, '2025-05-30 15:17:36', '2025-05-30 15:17:36'),
(102, 1, 270, '2025-05-30 15:17:36', '2025-05-30 15:17:36'),
(103, 1, 272, '2025-05-30 15:17:36', '2025-05-30 15:17:36'),
(104, 1, 27, '2025-05-30 15:17:36', '2025-05-30 15:17:36'),
(105, 1, 271, '2025-05-30 15:17:36', '2025-05-30 15:17:36'),
(106, 1, 269, '2025-05-30 15:17:36', '2025-05-30 15:17:36'),
(107, 1, 266, '2025-05-30 15:17:36', '2025-05-30 15:17:36'),
(108, 1, 267, '2025-05-30 15:17:36', '2025-05-30 15:17:36'),
(109, 1, 268, '2025-05-30 15:17:36', '2025-05-30 15:17:36'),
(110, 1, 273, '2025-05-30 15:17:36', '2025-05-30 15:17:36'),
(111, 2, 2, '2025-05-30 15:17:44', '2025-05-30 15:17:44'),
(112, 2, 24, '2025-05-30 15:17:44', '2025-05-30 15:17:44'),
(113, 2, 25, '2025-05-30 15:17:44', '2025-05-30 15:17:44'),
(114, 2, 26, '2025-05-30 15:17:44', '2025-05-30 15:17:44'),
(115, 2, 1, '2025-05-30 15:17:44', '2025-05-30 15:17:44'),
(116, 2, 275, '2025-05-30 15:17:44', '2025-05-30 15:17:44'),
(117, 2, 27, '2025-05-30 15:17:44', '2025-05-30 15:17:44');

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

--
-- Structure de la table `secteur_a_visiter`
--

DROP TABLE IF EXISTS `secteur_a_visiter`;
CREATE TABLE `secteur_a_visiter` (
  `id_secteur` int(7) NOT NULL,
  `nom_secteur` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

DROP TABLE IF EXISTS `suggestions`;
CREATE TABLE `suggestions` (
  `id_suggestion` int(11) NOT NULL,
  `id_user` int(11) DEFAULT NULL,
  `suggestion_text` longtext DEFAULT NULL,
  `user_comment` longtext DEFAULT NULL,
  `analyst_comment` longtext DEFAULT NULL,
  `dt_suggestion` date DEFAULT NULL,
  `dt_completed` date DEFAULT NULL,
  `dt_annulation` date DEFAULT NULL,
  `creation` timestamp NOT NULL DEFAULT current_timestamp(),
  `lastmod` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

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

--
-- Structure de la table `users`
--

DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id_user` int(7) NOT NULL,
  `username` varchar(64) NOT NULL,
  `password` varchar(64) NOT NULL COMMENT 'MD5 hash',
  `lastname` varchar(64) DEFAULT NULL,
  `firstname` varchar(64) DEFAULT NULL,
  `email` varchar(128) DEFAULT NULL,
  `comment` text DEFAULT NULL,
  `last_time` datetime DEFAULT NULL,
  `last_url` text DEFAULT NULL,
  `last_host` varchar(64) DEFAULT NULL,
  `valide_au` date DEFAULT NULL,
  `num_licence` int(11) DEFAULT NULL,
  `creation` timestamp NOT NULL DEFAULT current_timestamp(),
  `lastmod` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

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

--
-- Structure de la table `users_roles`
--

DROP TABLE IF EXISTS `users_roles`;
CREATE TABLE `users_roles` (
  `id_ur` int(11) NOT NULL,
  `id_user` int(11) NOT NULL,
  `id_role` int(11) NOT NULL,
  `creation` timestamp NOT NULL DEFAULT current_timestamp(),
  `lastmod` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

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

--
-- Structure de la table `visites_visiteurs`
--

DROP TABLE IF EXISTS `visites_visiteurs`;
CREATE TABLE `visites_visiteurs` (
  `id_visite` int(7) NOT NULL,
  `id_visiteur` int(7) NOT NULL,
  `date_debut` date NOT NULL,
  `heure_debut` time NOT NULL,
  `date_fin` date DEFAULT NULL,
  `heure_fin` time NOT NULL,
  `secteur` int(7) NOT NULL,
  `contact` int(7) NOT NULL,
  `observation` varchar(255) DEFAULT NULL,
  `verifie` tinyint(1) DEFAULT 0,
  `deleted` tinyint(1) DEFAULT 0,
  `deleted_by` int(7) DEFAULT 0,
  `edited_by` int(7) DEFAULT 0,
  `added_by` int(7) DEFAULT 0,
  `date_creation` timestamp NOT NULL DEFAULT current_timestamp(),
  `date_lasmod` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

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

--
-- Structure de la table `visiteurs`
--

DROP TABLE IF EXISTS `visiteurs`;
CREATE TABLE `visiteurs` (
  `id_visiteur` int(5) NOT NULL,
  `id_fournisseur` varchar(255) DEFAULT NULL,
  `civilite` varchar(12) NOT NULL,
  `nom` varchar(100) NOT NULL,
  `prenom` varchar(100) NOT NULL,
  `email` varchar(100) DEFAULT NULL,
  `tel` varchar(15) DEFAULT NULL,
  `banni` tinyint(1) DEFAULT 0,
  `deleted` tinyint(1) DEFAULT 0,
  `observation` varchar(255) DEFAULT NULL,
  `deleted_by` int(7) DEFAULT 0,
  `edited_by` int(7) DEFAULT 0,
  `date_creation` timestamp NULL DEFAULT current_timestamp(),
  `date_lastmod` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Structure de la table `whodidwhat`
--

DROP TABLE IF EXISTS `whodidwhat`;
CREATE TABLE `whodidwhat` (
  `id_cmd` int(32) NOT NULL,
  `id_user` int(11) NOT NULL,
  `hostname` varchar(64) DEFAULT NULL,
  `mode_cmd` enum('insert','update','delete') NOT NULL,
  `table_cmd` varchar(64) NOT NULL,
  `url_cmd` varchar(128) NOT NULL,
  `sql_cmd` text NOT NULL,
  `nrows` int(11) DEFAULT NULL,
  `msg` text DEFAULT NULL,
  `lastmod` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

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

--
-- Index pour la table `contact_a_visiter`
--
ALTER TABLE `contact_a_visiter`
  ADD PRIMARY KEY (`id_contact`);

--
-- Index pour la table `departements`
--
ALTER TABLE `departements`
  ADD PRIMARY KEY (`id_departement`);

--
-- Index pour la table `departement_user`
--
ALTER TABLE `departement_user`
  ADD PRIMARY KEY (`id_departement_user`);

--
-- Index pour la table `fournisseur`
--
ALTER TABLE `fournisseur`
  ADD PRIMARY KEY (`id_fournisseur`);

--
-- Index pour la table `pages`
--
ALTER TABLE `pages`
  ADD PRIMARY KEY (`id_url`);

--
-- Index pour la table `roles`
--
ALTER TABLE `roles`
  ADD PRIMARY KEY (`id_role`);

--
-- Index pour la table `roles_pages`
--
ALTER TABLE `roles_pages`
  ADD PRIMARY KEY (`id_rp`);

--
-- Index pour la table `secteur_a_visiter`
--
ALTER TABLE `secteur_a_visiter`
  ADD PRIMARY KEY (`id_secteur`);

--
-- Index pour la table `suggestions`
--
ALTER TABLE `suggestions`
  ADD PRIMARY KEY (`id_suggestion`);

--
-- Index pour la table `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`id_user`);

--
-- Index pour la table `users_roles`
--
ALTER TABLE `users_roles`
  ADD PRIMARY KEY (`id_ur`),
  ADD KEY `id_user` (`id_user`),
  ADD KEY `id_role` (`id_role`);

--
-- Index pour la table `visites_visiteurs`
--
ALTER TABLE `visites_visiteurs`
  ADD PRIMARY KEY (`id_visite`);

--
-- Index pour la table `visiteurs`
--
ALTER TABLE `visiteurs`
  ADD PRIMARY KEY (`id_visiteur`);

--
-- Index pour la table `whodidwhat`
--
ALTER TABLE `whodidwhat`
  ADD PRIMARY KEY (`id_cmd`);

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

--
-- AUTO_INCREMENT pour la table `contact_a_visiter`
--
ALTER TABLE `contact_a_visiter`
  MODIFY `id_contact` int(7) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT pour la table `departements`
--
ALTER TABLE `departements`
  MODIFY `id_departement` int(11) NOT NULL AUTO_INCREMENT;
COMMIT;

--
-- AUTO_INCREMENT pour la table `departement_user`
--
ALTER TABLE `departement_user`
  MODIFY `id_departement_user` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT pour la table `fournisseur`
--
ALTER TABLE `fournisseur`
  MODIFY `id_fournisseur` int(7) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT pour la table `pages`
--
ALTER TABLE `pages`
  MODIFY `id_url` int(32) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT pour la table `roles`
--
ALTER TABLE `roles`
  MODIFY `id_role` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=10;

--
-- AUTO_INCREMENT pour la table `roles_pages`
--
ALTER TABLE `roles_pages`
  MODIFY `id_rp` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=118;

--
-- AUTO_INCREMENT pour la table `secteur_a_visiter`
--
ALTER TABLE `secteur_a_visiter`
  MODIFY `id_secteur` int(7) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT pour la table `suggestions`
--
ALTER TABLE `suggestions`
  MODIFY `id_suggestion` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT pour la table `users`
--
ALTER TABLE `users`
  MODIFY `id_user` int(7) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT pour la table `users_roles`
--
ALTER TABLE `users_roles`
  MODIFY `id_ur` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT pour la table `visites_visiteurs`
--
ALTER TABLE `visites_visiteurs`
  MODIFY `id_visite` int(7) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT pour la table `visiteurs`
--
ALTER TABLE `visiteurs`
  MODIFY `id_visiteur` int(5) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT pour la table `whodidwhat`
--
ALTER TABLE `whodidwhat`
  MODIFY `id_cmd` int(32) NOT NULL AUTO_INCREMENT;
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 */;
