Base de données

-- phpMyAdmin SQL Dump
-- version 5.2.1deb1
-- https://www.phpmyadmin.net/
--
-- Hôte : 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
`gestion_hjc`
--

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

--
Table Table:fournisseurs
fournisseurs
CREATE TABLE `fournisseurs` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `nom` VARCHAR(100) NOT NULL,
  `adresse` TEXT DEFAULT NULL,
  `CP` VARCHAR(5) DEFAULT NULL,
  `Ville` VARCHAR(30) DEFAULT NULL,
  `email` VARCHAR(100) DEFAULT NULL,
  `telephone` VARCHAR(20) DEFAULT NULL,
  `mobile` VARCHAR(20) DEFAULT NULL,
  `actif` TINYINT(1) DEFAULT 1,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
--
Table:

Table 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:

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:

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:

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:

Table roles
CREATE TABLE `roles` (
  `id` INT(11) UNSIGNED 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:

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:

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:

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;