Base de données

localhost:3306
-- Généré le : Tue. 03 Jun 2025 à 16:20
-- Version du serveur : 10.11.6-MariaDB-0+deb12u1
-- Version de PHP : 8.2.26

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

--
-- Base de données : `gestion_hjc`
--

CREATE DATABASE IF NOT EXISTS `gestion_hjc` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE `gestion_hjc`;

-- --------------------------------------------------------
-- Table: fournisseurs
CREATE TABLE `users`fournisseurs` (
  `id` int(INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name`nom` varchar(50)VARCHAR(100) NOT NULL,
  `email`adresse` varchar(100)TEXT NOT NULL UNIQUE,
  `password` varchar(255) NOTDEFAULT NULL,
  `role_id`CP` int(10)VARCHAR(5) UNSIGNEDDEFAULT NOTNULL,
  `Ville` VARCHAR(30) DEFAULT NULL,
  `email` VARCHAR(100) DEFAULT NULL,
  `telephone` VARCHAR(20) DEFAULT NULL,
  `mobile` VARCHAR(20) DEFAULT NULL,
  `actif` tinyint(TINYINT(1) DEFAULT 1,
  `token` varchar(255),
  `last_login` datetime DEFAULT NULL,
  `reset_token` varchar(255) DEFAULT NULL,
  `reset_token_expiration` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

TableTable: achats CREATE TABLE `achats` ( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `date` DATE NOT NULL, `semaine` INT(2) NOT NULL, `idFournisseur` INT(10) UNSIGNED NOT NULL, `montantHT` DECIMAL(10,2) NOT NULL, `idUtilisateur` INT(11) NOT NULL, `dateSaisie` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `valide` TINYINT(1) NOT NULL DEFAULT 0, PRIMARY KEY (`id`), KEY `idFournisseur` (`idFournisseur`), CONSTRAINT `achats_ibfk_2` FOREIGN KEY (`idFournisseur`) REFERENCES `fournisseurs` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- Table: clients CREATE TABLE `clients` ( `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, `date` DATE NOT NULL COMMENT 'Date de l'enregistrement', `nbClientsPoissonnerie` INT(11) UNSIGNED NOT NULL DEFAULT 0, `nbClientsComptoir` INT(11) UNSIGNED NOT NULL DEFAULT 0, `valide` TINYINT(1) NOT NULL DEFAULT 0, `dateSaisie` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `idUtilisateur` INT(11) UNSIGNED NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- Table: modesReglement CREATE TABLE `modesReglement` ( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `nom` VARCHAR(50) NOT NULL, `actif` TINYINT(1) UNSIGNED NOT NULL DEFAULT 1, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; INSERT INTO `modesReglement` (`id`, `nom`, `actif`) VALUES (1, 'CB', 0), (2, 'AMEX', 0), (3, 'Bon Cadeau', 0), (4, 'Chèque', 1), (5, 'Virement de fond', 1), (6, 'Espèces', 1), (7, 'Vente à distance', 0), (8, 'Sortie de caisse', 1), (9, 'Uber Virements', 0), (10, 'CB Stripe', 0), (12, 'TPE 1 CB CLESS', 1), (13, 'TPE 1 CB CLESS AMEX', 1), (14, 'TPE 2 CB CLESS', 1), (15, 'TPE 2 CB CLESS AMEX', 1), (16, 'TPE 1 CB EMV', 1), (18, 'TPE 1 CB EMV AMEX', 1), (19, 'TPE 2 CB CBEMV', 1), (20, 'TPE 2 CB CBEMV AMEX', 1), (23, 'TPE CB EMV', 0), (24, 'TPE CB EMV AMEX', 0), (25, 'TPE CB CLESS', 0), (26, 'TPE CB CLESS AMEX', 0), (27, 'TPE CB VAD', 0), (28, 'TPE CB Arrhes', 0), (29, 'Ticket restaurant', 0), (30, 'Espèces Arrhes', 0), (31, 'HT Comptoir', 1); -- Table: ventes CREATE TABLE `ventes` ( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `date` DATE NOT NULL, `dateSaisie` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `semaine` INT(2) NOT NULL, `valide` TINYINT(1) NOT NULL DEFAULT 0, `idUtilisateur` INT(11) NOT NULL DEFAULT 1, `idModeReglement` INT(10) UNSIGNED NOT NULL, `montant` DECIMAL(10,2) NOT NULL, PRIMARY KEY (`id`), KEY `idModeReglement` (`idModeReglement`), CONSTRAINT `ventes_ibfk_2` FOREIGN KEY (`idModeReglement`) REFERENCES `modesReglement` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- Table: roles :

CREATE TABLE `roles` (
  `id` intINT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` varchar(50) NOT NULL AUTO_INCREMENT,
  `nom` VARCHAR(50) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `nom` (`nom`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `roles` (`id`, `nom`) VALUES
(5, 'Administrateur'),
(2, 'Comptabilité'),
(4, 'Direction'),
(3, 'Responsable'),
(1, 'Utilisateur');

-- Table: users
CREATE TABLE `users` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `first_name` VARCHAR(50) NOT NULL,
  `last_name` VARCHAR(50) DEFAULT NULL,
  `email` VARCHAR(100) NOT NULL,
  `password` VARCHAR(255) NOT NULL,
  `role_id` INT(10) UNSIGNED NOT NULL,
  `actif` TINYINT(1) DEFAULT 1,
  `token` VARCHAR(255) DEFAULT NULL,
  `last_login` DATETIME DEFAULT NULL,
  `reset_token` VARCHAR(255) DEFAULT NULL,
  `reset_token_expiration` DATETIME DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`),
  CONSTRAINT `users_ibfk_1` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Table: permissions
CREATE TABLE `permissions` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `nom` VARCHAR(100) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `nom` (`nom`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `permissions` (`id`, `nom`) VALUES
(6, 'gerer etablissements'),
(5, 'gerer modes reglement'),
(4, 'gerer roles'),
(3, 'gerer utilisateurs'),
(1, 'saisir achat'),
(2, 'saisir vente'),
(7, 'voir synthese');

-- Table: role_permissions
CREATE TABLE `role_permissions` (
  `role_id` INT(11) UNSIGNED NOT NULL,
  `permission_id` INT(11) UNSIGNED NOT NULL,
  PRIMARY KEY (`role_id`, `permission_id`),
  KEY `permission_id` (`permission_id`),
  CONSTRAINT `role_permissions_ibfk_1` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE,
  CONSTRAINT `role_permissions_ibfk_2` FOREIGN KEY (`permission_id`) REFERENCES `permissions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

COMMIT;