-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1:3306
-- Generation Time: Sep 15, 2025 at 02:42 PM
-- Server version: 9.1.0
-- PHP Version: 8.3.14

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

--
-- Database: `hb`
--

DELIMITER $$
--
-- Procedures
--
DROP PROCEDURE IF EXISTS `RefreshNotifications`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `RefreshNotifications` ()   BEGIN
        -- Supprimer les anciennes notifications d'appels
        DELETE FROM notifications WHERE type = 'appel_client';
        
        -- Insérer les nouvelles notifications basées sur les polices expirantes
        INSERT INTO notifications (
            type, police_number, client_cin, client_name, client_phone, 
            end_date, days_remaining, is_read, is_dismissed, next_call_date
        )
        SELECT 
            'appel_client' as type,
            p.n_policy as police_number,
            p.client_cin,
            c.client_name,
            c.client_phone,
            p.end_date,
            DATEDIFF(p.end_date, CURDATE()) as days_remaining,
            0 as is_read,
            0 as is_dismissed,
            p.next_call_date
        FROM policy_table p
        LEFT JOIN clients_table c ON p.client_cin = c.client_cin
        WHERE p.end_date <= DATE_ADD(CURDATE(), INTERVAL 7 DAY)
        AND p.end_date >= CURDATE()
        AND (p.appel IS NULL OR p.appel = 0)
        AND (p.next_call_date IS NULL OR p.next_call_date <= CURDATE());
    END$$

DELIMITER ;

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

--
-- Table structure for table `clients_table`
--

DROP TABLE IF EXISTS `clients_table`;
CREATE TABLE IF NOT EXISTS `clients_table` (
  `id` int NOT NULL AUTO_INCREMENT,
  `n_dossier` varchar(15) NOT NULL,
  `client_cin` varchar(10) NOT NULL,
  `client_name` varchar(60) NOT NULL,
  `client_phone` varchar(15) NOT NULL,
  `client_email` varchar(150) NOT NULL,
  `client_genre` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `client_company` varchar(60) NOT NULL,
  `client_adresse` varchar(350) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `client_ice` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `client_cin` (`client_cin`,`client_phone`,`client_email`)
) ENGINE=MyISAM AUTO_INCREMENT=23 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `clients_table`
--

INSERT INTO `clients_table` (`id`, `n_dossier`, `client_cin`, `client_name`, `client_phone`, `client_email`, `client_genre`, `client_company`, `client_adresse`, `client_ice`) VALUES
(5, '1', 'OD1254', 'SAID AHMEDI', '066544658589', 'lahrach829@gmail.com', 'Femelle', 'SAID AHMEDI', 'Lot Lhana N 149 Tasseltante', ''),
(4, '47', 'MC1412701', 'YOUSSEF OMARI', '066544658565', 'lahrach82@gmail.com6', 'Personne Morale', 'YOUSSEF OMARI', 'Lot Lhana N 149 Tasseltante', '21544448778'),
(6, '5', 'ML2549', 'SAID MORABITO', '212665446584', 'lahrach82@gmail.com', 'Personne Morale', 'MPOL', 'BP 29 Tnin ourika', ''),
(16, '14', 'RO25', 'KHALID ELAMIR', '0665446585', 'lahrach82@gmail.com3', 'Personne Morale', 'BALDISS', 'BP 29 Tnin ourika', '2154444877'),
(20, '45', 'E589707', 'NIZARI RACHID', '0663690212', 'nizarirachid@gmail.com', 'Mâle', 'NIZARI RACHID', 'BP 29 Tnin ourika', ''),
(21, '78', 'EC1254', 'MOURAD NAJI', '0665446578', 'ya@gmail.com', 'Mâle', 'MOURAD NAJI', 'Lot Lhana N 149 Tasseltante', ''),
(22, '142', 'MC14127012', 'YOUSSEF LAHRACH', '0665446589', 'lahrach82@gmail.coms', 'Femelle', 'YOUSSEF LAHRACH', 'BP 29 Tnin ourika', '');

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

--
-- Table structure for table `compagnie_table`
--

DROP TABLE IF EXISTS `compagnie_table`;
CREATE TABLE IF NOT EXISTS `compagnie_table` (
  `id` int NOT NULL AUTO_INCREMENT,
  `compagnie_name` varchar(60) NOT NULL,
  `adresse` varchar(250) NOT NULL,
  `tel1` varchar(15) NOT NULL,
  `tel2` varchar(15) NOT NULL,
  `fax` varchar(15) NOT NULL,
  `whatsapp` varchar(15) NOT NULL,
  `mail_1` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `mail_2` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `mail_3` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `footer` varchar(250) NOT NULL,
  `facture_ref` varchar(100) NOT NULL,
  `facture_prefixe` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `facture_suffixe` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `use_year_prefix` tinyint NOT NULL DEFAULT '0',
  `use_monthyear_prefix` tinyint NOT NULL DEFAULT '0',
  `use_year_suffix` tinyint NOT NULL DEFAULT '0',
  `use_monthyear_suffix` tinyint NOT NULL DEFAULT '0',
  `reset_number_yearly` tinyint NOT NULL DEFAULT '0',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `logo` varchar(100) NOT NULL,
  `logo_format` enum('wide','square') DEFAULT 'wide',
  `compagnie_ref` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `compagnie_table`
--

INSERT INTO `compagnie_table` (`id`, `compagnie_name`, `adresse`, `tel1`, `tel2`, `fax`, `whatsapp`, `mail_1`, `mail_2`, `mail_3`, `footer`, `facture_ref`, `facture_prefixe`, `facture_suffixe`, `use_year_prefix`, `use_monthyear_prefix`, `use_year_suffix`, `use_monthyear_suffix`, `reset_number_yearly`, `created_at`, `updated_at`, `logo`, `logo_format`, `compagnie_ref`) VALUES
(1, '  Hiba Assurances', 'N°8, AV. Allal El Fassi,IMM A, Opération Elboustane 2,\r\nMarrakech', '+2125242925', '', '', '', 'hiba.assurance@gmail.com', '', '', 'Intermédiaire d\'assurances régi par la loi 17-99. Portant code des assurances. \r\nA gréement N°C2653549002011450 du 29 Décembre 2010.\r\n RC: 44773-IF:40247708 ICE :000222791000001', '255', '', '', 0, 0, 1, 0, 1, '2025-07-24 09:13:47', '2025-07-24 09:14:14', 'data/123456/logo.png', 'square', '123456');

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

--
-- Table structure for table `facture_table`
--

DROP TABLE IF EXISTS `facture_table`;
CREATE TABLE IF NOT EXISTS `facture_table` (
  `id` int NOT NULL AUTO_INCREMENT,
  `facture_date` date NOT NULL,
  `facture_num` varchar(100) NOT NULL,
  `libelle` varchar(100) NOT NULL,
  `objet_assure` varchar(100) NOT NULL,
  `start_date` date NOT NULL,
  `end_date` date NOT NULL,
  `total` decimal(10,2) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=47 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `facture_table`
--

INSERT INTO `facture_table` (`id`, `facture_date`, `facture_num`, `libelle`, `objet_assure`, `start_date`, `end_date`, `total`) VALUES
(39, '2025-07-24', '256/2025', '12347', '14-أ-2542', '2025-07-27', '2025-08-06', 5000.00),
(38, '2025-07-24', '255/2025', '123', '123456789-B-12', '2025-07-01', '2025-08-13', 3000.00),
(40, '2025-07-29', '257/2025', '1233', '11254-أ-26', '2025-07-03', '2025-08-13', 2000.00),
(41, '2025-07-29', '258/2025', '7894444a', '354-أ-78789', '2025-07-03', '2025-08-13', 1500.00),
(42, '2025-07-31', '259/2025', '7956', '4875-ب-2', '2025-07-01', '2025-10-12', 2000.00),
(43, '2025-07-31', '260/2025', '7458', '', '2025-08-27', '2025-09-11', 3000.00),
(44, '2025-08-04', '261/2025', '784', '', '2025-08-04', '2025-09-07', 3000.00),
(45, '2025-08-04', '262/2025', '14789V', '478545-*-26', '2025-08-04', '2025-10-12', 7000.00),
(46, '2025-08-13', '263/2025', '7485', 'MAISON', '2025-08-04', '2025-08-30', 30000.00);

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

--
-- Table structure for table `lettres_table`
--

DROP TABLE IF EXISTS `lettres_table`;
CREATE TABLE IF NOT EXISTS `lettres_table` (
  `id` int NOT NULL AUTO_INCREMENT,
  `lettre` varchar(2) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `lettres_table`
--

INSERT INTO `lettres_table` (`id`, `lettre`) VALUES
(1, 'أ'),
(5, 'B'),
(3, 'ب'),
(4, ' *'),
(6, 'C'),
(7, 'H');

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

--
-- Table structure for table `marques_transport`
--

DROP TABLE IF EXISTS `marques_transport`;
CREATE TABLE IF NOT EXISTS `marques_transport` (
  `id` int NOT NULL AUTO_INCREMENT,
  `marque` varchar(100) NOT NULL,
  `type` enum('voiture','bus','camion','moto') NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=44 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `marques_transport`
--

INSERT INTO `marques_transport` (`id`, `marque`, `type`) VALUES
(1, 'DACIA', 'voiture'),
(2, 'RENAULT', 'voiture'),
(3, 'PEUGEOT', 'voiture'),
(4, 'HYUNDAI', 'voiture'),
(5, 'KIA', 'voiture'),
(6, 'VOLKSWAGEN', 'voiture'),
(7, 'TOYOTA', 'voiture'),
(8, 'NISSAN', 'voiture'),
(9, 'MERCEDES-BENZ', 'voiture'),
(10, 'BMW', 'voiture'),
(11, 'CITROËN', 'voiture'),
(12, 'FIAT', 'voiture'),
(13, 'FORD', 'voiture'),
(14, 'SUZUKI', 'voiture'),
(16, 'MAN', 'bus'),
(17, 'VOLVO', 'bus'),
(18, 'SCANIA', 'bus'),
(19, 'SETRA', 'bus'),
(20, 'IVECO', 'bus'),
(22, 'VOLVO TRUCKS', 'camion'),
(24, 'DAF', 'camion'),
(26, 'RENAULT TRUCKS', 'camion'),
(27, 'ISUZU', 'camion'),
(28, 'TATA', 'camion'),
(29, 'YAMAHA', 'moto'),
(30, 'HONDA', 'moto'),
(32, 'PIAGGIO', 'moto'),
(33, 'KYMCO', 'moto'),
(34, 'SYM', 'moto'),
(35, 'BAJAJ', 'moto'),
(36, 'DAYUN', 'moto'),
(37, 'VESPA', 'moto');

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

--
-- Table structure for table `menu_items`
--

DROP TABLE IF EXISTS `menu_items`;
CREATE TABLE IF NOT EXISTS `menu_items` (
  `id` int NOT NULL AUTO_INCREMENT,
  `title` varchar(100) NOT NULL,
  `icon` varchar(50) DEFAULT NULL,
  `link` varchar(255) DEFAULT NULL,
  `parent_id` int DEFAULT NULL COMMENT 'Pour les sous-menus',
  `is_heading` tinyint(1) DEFAULT '0',
  `is_active` tinyint(1) DEFAULT '0',
  `menu_order` int DEFAULT '0',
  `access` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=65 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `menu_items`
--

INSERT INTO `menu_items` (`id`, `title`, `icon`, `link`, `parent_id`, `is_heading`, `is_active`, `menu_order`, `access`) VALUES
(1, 'Tableau de bord', 'fas fa-fw fa-tachometer-alt text-danger', 'dashboard.php', NULL, 0, 1, 1, 1),
(2, 'Facturation', 'fas fa-file-invoice text-danger', NULL, NULL, 0, 0, 3, 0),
(3, 'Polices', 'far fa-fw fa-window-maximize text-primary', NULL, NULL, 0, 0, 2, 0),
(4, 'Tableau de Polices', NULL, 'policys_table.php', 3, 0, 0, 0, 0),
(6, 'Clients', 'fas fa-address-card text-warning', NULL, NULL, 0, 0, 4, 0),
(7, 'Nouveau Client', NULL, 'add_client.php', 6, 0, 0, 1, 0),
(8, 'Reçu de Paiement Copie', NULL, 'print_receipt_copy.php', NULL, 1, 0, 5, 0),
(9, 'Utilisateurs', 'fas fa-users fa-2x text-info', NULL, NULL, 0, 0, 6, 3),
(10, 'Liste de clients', NULL, 'clients_table.php', 6, 0, 0, 1, 0),
(11, 'Reçu de Paiement', NULL, 'print_receipt.php', 0, 1, 1, 2, 0),
(12, 'Autorisations utilisateur', NULL, 'manage_permissions.php', 9, 0, 0, 2, 3),
(13, 'Liste d\'utilisateurs', NULL, 'users_table.php', 9, 0, 0, 1, 3),
(14, 'Info de Police', NULL, 'info_policy.php', NULL, 1, 1, 1, 0),
(16, 'Modification de Police', NULL, 'edit_policy.php', NULL, 1, 0, 2, 0),
(17, 'Liste de Paiments', NULL, 'liste_paiements_policy.php', NULL, 1, 0, 0, 0),
(18, 'Copier les permissions', NULL, 'copy_permissions.php', 9, 0, 0, 3, 0),
(19, 'Nouvelle police', NULL, 'new_policy.php', 3, 0, 0, 1, 0),
(20, 'Connexion', NULL, 'login.php', NULL, 1, 0, 0, 1),
(21, 'Mot de passe oublié', NULL, 'forgot-password.php', NULL, 1, 0, 0, 1),
(22, 'Vérification du code', NULL, 'verify-code.php', NULL, 1, 0, 0, 0),
(23, 'Informations sur le client', NULL, 'info_client.php', NULL, 1, 0, 0, 0),
(24, 'Modifier un client', NULL, 'edit_client.php', NULL, 1, 0, 0, 0),
(26, 'Reçus de Paiements', NULL, 'liste_recus.php', 27, 0, 0, 0, 0),
(27, 'Paiements', 'fas fa-money-bill fa-2x text-success', NULL, NULL, 0, 0, 2, 0),
(28, 'Banque', NULL, 'liste_cheques_effets.php', 27, 0, 0, 0, 0),
(29, 'Espace Client', NULL, 'espace_client.php', NULL, 1, 0, 0, 0),
(30, 'Caisse', NULL, 'liste_paiements_espece.php', 27, 0, 0, 1, 0),
(31, 'Liste des Factures', NULL, 'liste_factures.php', 2, 0, 1, 1, 0),
(32, 'Facture', NULL, 'facture.php', NULL, 1, 0, 0, 0),
(33, 'Configuration', 'fas fa-solid fa-gear text-dark', NULL, NULL, 0, 0, 7, 0),
(34, 'Ajouter une marque', 'fa-solid fa-car text-warning', 'add_marque.php', 33, 0, 0, 2, 0),
(35, 'Exporter des factures', NULL, 'exportation_factures.php', 2, 0, 0, 1, 0),
(36, 'Configuration Générale', NULL, 'config_compagnie.php', 33, 0, 0, 0, 3),
(37, 'Configuration des Factures', NULL, 'config_facture.php', 33, 0, 0, 1, 3),
(38, 'FAQ', NULL, 'faq.php', 39, 0, 0, 0, 1),
(39, 'Autres', 'fa-solid fa-circle-question text-primary', NULL, NULL, 0, 0, 9, 0),
(40, 'Sauvegarde', NULL, 'backup.php', 64, 0, 0, 0, 0),
(41, 'Journal', NULL, 'view_logs.php', 39, 0, 0, 3, 1),
(42, 'liste de Marques', NULL, 'marques_table.php', 33, 0, 0, 4, 0),
(43, 'Ajouter un Type De police', NULL, 'add_policy_type.php', 33, 0, 0, 5, 0),
(44, 'Liste de Types de Police', NULL, 'policy_types_table.php', 33, 0, 0, 6, 0),
(46, 'Ajouter une Lettre', NULL, 'add_lettre.php', 33, 0, 0, 7, 0),
(47, 'Déconnexion', 'fa-solid fa-arrow-right-from-bracket text-danger', 'logout.php', NULL, 1, 1, 9, 1),
(48, 'Liste de Lettres', NULL, 'lettres_table.php', 33, 0, 0, 8, 0),
(49, 'Appels', NULL, 'appels.php', 3, 0, 0, 2, 0),
(50, 'Échéances d\'aujourd\'hui', NULL, 'echeances_aujourdhui.php', NULL, 1, 0, 0, 0),
(51, 'Rappels Moi', NULL, 'rappels.php', 64, 0, 0, 9, 1),
(52, 'Relevé de compte client', NULL, 'releve_compte.php', NULL, 1, 0, 0, 0),
(53, 'Ajouter un Utilisateur', NULL, 'add_user.php', 9, 0, 0, 0, 3),
(54, 'Mon Profil', NULL, 'profile.php', NULL, 1, 0, 0, 1),
(55, 'Changer le Mot de Passe', NULL, 'change_password.php', NULL, 1, 0, 0, 1),
(56, 'Modifier Mon Profil', NULL, 'edit_profile.php', NULL, 1, 0, 0, 1),
(58, 'Rappels', NULL, 'rappels_jour.php', NULL, 1, 0, 0, 1),
(59, 'Panneau de Contrôle', NULL, 'dashboard_agent.php', NULL, 1, 0, 0, 1),
(60, 'Paiement de la Police', NULL, 'paiement.php', NULL, 1, 0, 0, 0),
(61, 'Reçu de Paiement', NULL, 'recu.php', NULL, 1, 0, 0, 0),
(62, 'Appels Pour les Paiements', NULL, 'appels_paiements_aujourdhui.php', NULL, 1, 0, 0, 0),
(63, 'Infos', NULL, 'infos.php', 39, 0, 0, 5, 3),
(64, 'Outils', 'fa-solid fa-wrench', NULL, NULL, 0, 0, 8, 0);

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

--
-- Table structure for table `menu_table`
--

DROP TABLE IF EXISTS `menu_table`;
CREATE TABLE IF NOT EXISTS `menu_table` (
  `id` int NOT NULL AUTO_INCREMENT,
  `menu_name` varchar(60) NOT NULL,
  `menu_icone` varchar(60) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `menu_table`
--

INSERT INTO `menu_table` (`id`, `menu_name`, `menu_icone`) VALUES
(1, 'test', '');

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

--
-- Table structure for table `notifications`
--

DROP TABLE IF EXISTS `notifications`;
CREATE TABLE IF NOT EXISTS `notifications` (
  `id` int NOT NULL AUTO_INCREMENT,
  `type` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'appel_client',
  `police_number` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `client_cin` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `client_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `client_phone` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `days_remaining` int DEFAULT NULL,
  `is_read` tinyint(1) DEFAULT '0',
  `is_dismissed` tinyint(1) DEFAULT '0',
  `next_call_date` date DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_type` (`type`),
  KEY `idx_is_read` (`is_read`),
  KEY `idx_is_dismissed` (`is_dismissed`),
  KEY `idx_police` (`police_number`),
  KEY `idx_next_call` (`next_call_date`)
) ENGINE=InnoDB AUTO_INCREMENT=2546 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Table des notifications système';

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

--
-- Table structure for table `notification_log`
--

DROP TABLE IF EXISTS `notification_log`;
CREATE TABLE IF NOT EXISTS `notification_log` (
  `id` int NOT NULL AUTO_INCREMENT,
  `policy_id` int NOT NULL,
  `client_phone` varchar(20) NOT NULL,
  `message` varchar(300) NOT NULL,
  `sent_date` date NOT NULL,
  `status` varchar(10) NOT NULL,
  `details` text,
  `client_cin` varchar(15) NOT NULL,
  `notification_type` varchar(10) NOT NULL,
  `recipient` varchar(255) DEFAULT NULL,
  `sent_at` datetime NOT NULL,
  `phone_email` varchar(60) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_client_cin` (`client_cin`),
  KEY `idx_notification_type` (`notification_type`)
) ENGINE=MyISAM AUTO_INCREMENT=94 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `notification_log`
--

INSERT INTO `notification_log` (`id`, `policy_id`, `client_phone`, `message`, `sent_date`, `status`, `details`, `client_cin`, `notification_type`, `recipient`, `sent_at`, `phone_email`) VALUES
(90, 52, '', '', '2025-07-23', 'failed', 'HTTP 302: ', '', 'sms_biling', '0665446585', '0000-00-00 00:00:00', ''),
(89, 52, '', '', '2025-07-23', 'failed', 'HTTP 302: ', '', 'sms_biling', '0665446585', '0000-00-00 00:00:00', ''),
(88, 52, '', '', '2025-07-23', 'failed', 'Email arabe envoyé avec succès', '', 'email_ar', 'lahrach82@gmail.com', '0000-00-00 00:00:00', ''),
(87, 52, '', '', '2025-07-23', 'sent', 'Email français envoyé avec succès', '', 'email_fr', 'lahrach82@gmail.com', '0000-00-00 00:00:00', ''),
(91, 65, '', '', '2025-07-31', 'failed', 'HTTP 302: ', '', 'sms_biling', '066544658589', '0000-00-00 00:00:00', ''),
(92, 65, '', '', '2025-07-31', 'sent', 'Email français envoyé avec succès', '', 'email_fr', 'lahrach829@gmail.com', '0000-00-00 00:00:00', ''),
(93, 65, '', '', '2025-07-31', 'failed', 'Email arabe envoyé avec succès', '', 'email_ar', 'lahrach829@gmail.com', '0000-00-00 00:00:00', '');

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

--
-- Table structure for table `pages_table`
--

DROP TABLE IF EXISTS `pages_table`;
CREATE TABLE IF NOT EXISTS `pages_table` (
  `id` int NOT NULL AUTO_INCREMENT,
  `page_link` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `page_title` varchar(100) NOT NULL,
  `page_icone` varchar(60) NOT NULL,
  `page_menu` varchar(60) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- Table structure for table `paiement_mode_table`
--

DROP TABLE IF EXISTS `paiement_mode_table`;
CREATE TABLE IF NOT EXISTS `paiement_mode_table` (
  `id` int NOT NULL AUTO_INCREMENT,
  `paiement_mode` varchar(60) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `paiement_mode_table`
--

INSERT INTO `paiement_mode_table` (`id`, `paiement_mode`) VALUES
(1, 'ESPECE'),
(2, 'CHEQUE'),
(3, 'EFFET'),
(4, 'VIREMENT BANCAIRE');

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

--
-- Table structure for table `policy_table`
--

DROP TABLE IF EXISTS `policy_table`;
CREATE TABLE IF NOT EXISTS `policy_table` (
  `id` int NOT NULL AUTO_INCREMENT,
  `client_name` varchar(60) NOT NULL,
  `client_cin` varchar(10) NOT NULL,
  `immatriculation` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `n_policy` varchar(60) NOT NULL,
  `policy_type` varchar(60) NOT NULL,
  `policy_category` varchar(100) NOT NULL,
  `marque` varchar(100) NOT NULL,
  `start_date` date NOT NULL,
  `end_date` date NOT NULL,
  `montant_to_paie` decimal(10,2) NOT NULL,
  `montant_paye` decimal(10,2) NOT NULL,
  `total_reste` decimal(10,2) NOT NULL,
  `appel` int NOT NULL DEFAULT '0',
  `next_call_date` varchar(60) NOT NULL,
  `next_paiement` date NOT NULL,
  `agent` varchar(60) NOT NULL,
  `next_call_date_paiement` date DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `n_policy` (`n_policy`)
) ENGINE=MyISAM AUTO_INCREMENT=83 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `policy_table`
--

INSERT INTO `policy_table` (`id`, `client_name`, `client_cin`, `immatriculation`, `n_policy`, `policy_type`, `policy_category`, `marque`, `start_date`, `end_date`, `montant_to_paie`, `montant_paye`, `total_reste`, `appel`, `next_call_date`, `next_paiement`, `agent`, `next_call_date_paiement`) VALUES
(60, 'SAID MORABITO', 'ML2549', '14-أ-2542', '12347', 'AUTOMOBILE', 'V', 'DACIA', '2025-07-27', '2025-08-16', 5000.00, 5000.00, 0.00, 0, '2025-08-18', '0000-00-00', 'LAHRACH YOUSSEF', NULL),
(59, 'YOUSSEF OMARI', 'MC1412701', '123456789-B-12', '123', 'AUTOMOBILE', 'V', 'DACIA', '2025-07-01', '2025-08-13', 3000.00, 3000.00, 0.00, 1, '2025-08-12', '0000-00-00', 'LAHRACH YOUSSEF', NULL),
(61, 'YOUSSEF OMARI', 'MC1412701', '11254-أ-26', '1233', 'AUTOMOBILE', 'V', 'DACIA', '2025-07-03', '2025-08-13', 2000.00, 2000.00, 0.00, 1, '2025-08-12', '0000-00-00', 'LAHRACH YOUSSEF', NULL),
(62, 'SAID MORABITO', 'ML2549', '354-أ-78789', '7894444a', 'TRANSPORT MARCHANDISES', 'V', 'BMW', '2025-07-03', '2025-08-13', 1500.00, 500.00, 1000.00, 1, '2025-08-11', '0000-00-00', 'LAHRACH YOUSSEF', NULL),
(63, 'SAID AHMEDI', 'OD1254', '', '7894', 'AUTOMOBILE', 'RD', '', '2025-07-25', '2025-08-14', 5000.00, 1000.00, 4000.00, 1, '', '0000-00-00', 'LAHRACH YOUSSEF', NULL),
(64, 'SAID AHMEDI', 'OD1254', '87897-أ-5', '2222', 'MOTO', 'V', 'CITROËN', '2025-07-10', '2025-07-24', 4875.00, 875.00, 4000.00, 0, '', '0000-00-00', 'LAHRACH YOUSSEF', NULL),
(65, 'SAID AHMEDI', 'OD1254', '', '22223', 'LOCAL COMMERCIAL', 'RD', '', '2025-07-31', '2025-08-07', 8000.00, 0.00, 8000.00, 0, '2025-08-05', '0000-00-00', 'LAHRACH YOUSSEF', NULL),
(66, 'SAID AHMEDI', 'OD1254', '5545-B-99', '22226', 'TRANSPORT VOYAGEURS', 'V', 'DACIA', '2025-07-26', '2025-08-10', 8000.00, 0.00, 8000.00, 0, '', '0000-00-00', 'LAHRACH YOUSSEF', NULL),
(67, 'SAID MORABITO', 'ML2549', '7894-B-99', '22229', 'TRANSPORT VOYAGEURS', 'V', 'BMW', '2025-07-26', '2025-08-13', 8000.00, 250.00, 7750.00, 1, '2025-08-13', '0000-00-00', 'LAHRACH YOUSSEF', NULL),
(68, 'SAID MORABITO', 'ML2549', '4875-ب-2', '7956', 'MOTO', 'V', 'HONDA', '2025-07-01', '2025-10-12', 2000.00, 1500.00, 500.00, 0, '', '0000-00-00', 'LAHRACH YOUSSEF', NULL),
(69, 'SAID MORABITO', 'ML2549', '', '7458', 'LOCAL COMMERCIAL', 'RD', '', '2025-08-27', '2025-09-11', 3000.00, 500.00, 2500.00, 0, '2025-09-04', '0000-00-00', 'LAHRACH YOUSSEF', NULL),
(70, 'SAID MORABITO', 'ML2549', '', '222278', 'MAISON', 'RD', '', '2025-07-31', '2025-08-10', 8000.00, 1000.00, 7000.00, 0, '', '0000-00-00', 'LAHRACH YOUSSEF', NULL),
(71, 'SAID MORABITO', 'ML2549', '', '95', 'MAISON', 'RD', '', '2025-07-31', '2025-08-10', 7000.00, 2000.00, 5000.00, 0, '', '0000-00-00', 'LAHRACH YOUSSEF', NULL),
(72, 'SAID AHMEDI', 'OD1254', '7845-ب-45', '22225', 'AUTOMOBILE', 'V', 'CITROËN', '2025-07-31', '2025-08-08', 3000.00, 2000.00, 1000.00, 0, '', '0000-00-00', 'LAHRACH YOUSSEF', NULL),
(73, 'KHALID ELAMIR', 'RO25', '', '784', 'MAISON', 'RD', '', '2025-08-04', '2025-09-07', 3000.00, 500.00, 2500.00, 0, '2025-08-28', '0000-00-00', 'LAHRACH YOUSSEF', NULL),
(74, 'KHALID ELAMIR', 'RO25', '7777-B-77', '123A', 'MOTO', 'V', 'BMW', '2025-08-04', '2025-08-31', 2000.00, 1000.00, 1000.00, 0, '2025-08-28', '0000-00-00', 'LAHRACH YOUSSEF', NULL),
(75, 'NIZARI RACHID', 'E589707', '478545-*-26', '14789V', 'MOTO', 'V', 'BMW', '2025-08-04', '2025-10-12', 7000.00, 5500.00, 1500.00, 1, '', '0000-00-00', 'LAHRACH YOUSSEF', NULL),
(76, 'NIZARI RACHID', 'E589707', 'MAISON', '7485', 'MAISON', 'RD', '', '2025-08-04', '2025-08-30', 30000.00, 100.00, 29900.00, 1, '', '0000-00-00', 'LAHRACH YOUSSEF', NULL),
(77, 'MOURAD NAJI', 'EC1254', '789-B-74', '1238', 'AUTOMOBILE', 'V', 'BMW', '2025-08-22', '2025-08-23', 3000.00, 5000.00, 0.00, 1, '', '0000-00-00', 'LAHRACH YOUSSEF', NULL),
(78, 'MOURAD NAJI', 'EC1254', '7899-B-74', '12388', 'AUTOMOBILE', 'V', 'BMW', '2025-08-22', '2025-08-23', 3000.00, 5000.00, 0.00, 1, '', '0000-00-00', 'LAHRACH YOUSSEF', NULL),
(79, 'MOURAD NAJI', 'EC1254', 'LOCAL COMMERCIAL', '12387', 'LOCAL COMMERCIAL', 'RD', '', '2025-08-22', '2025-08-23', 3000.00, 500.00, 2500.00, 1, '', '0000-00-00', 'LAHRACH YOUSSEF', NULL),
(80, 'SAID AHMEDI', 'OD1254', '1478-أ-14', '14793', 'AUTOMOBILE', 'V', 'CITROËN', '2025-08-14', '2025-08-31', 3000.00, 500.00, 2500.00, 0, '2025-08-31', '2025-08-28', 'LAHRACH YOUSSEF', NULL),
(81, 'KHALID ELAMIR', 'RO25', '7849-أ-26', 'q454', 'AUTOMOBILE', 'V', 'BMW', '2025-08-27', '2025-08-30', 3000.00, 1500.00, 1500.00, 0, '2025-08-28', '0000-00-00', 'LAHRACH YOUSSEF', NULL),
(82, 'YOUSSEF OMARI', 'MC1412701', '78945-أ-26', '74859', 'AUTOMOBILE', 'V', 'BMW', '2025-08-27', '2025-09-26', 5000.00, 2500.00, 2500.00, 0, '2025-09-11', '2025-08-29', 'LAHRACH YOUSSEF', NULL);

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

--
-- Table structure for table `policy_type`
--

DROP TABLE IF EXISTS `policy_type`;
CREATE TABLE IF NOT EXISTS `policy_type` (
  `id` int NOT NULL AUTO_INCREMENT,
  `type` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `risque` varchar(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `policy_type`
--

INSERT INTO `policy_type` (`id`, `type`, `risque`) VALUES
(1, 'TRANSPORT MARCHANDISES', 'V'),
(2, 'TRANSPORT VOYAGEURS', 'V'),
(3, 'AUTOMOBILE', 'V'),
(4, 'MOTO', 'V'),
(5, 'MAISON', 'RD'),
(6, 'LOCAL COMMERCIAL', 'RD');

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

--
-- Table structure for table `rappels_table`
--

DROP TABLE IF EXISTS `rappels_table`;
CREATE TABLE IF NOT EXISTS `rappels_table` (
  `id` int NOT NULL AUTO_INCREMENT,
  `date_rappel` date NOT NULL,
  `objet` varchar(255) NOT NULL,
  `agent` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `rappels_table`
--

INSERT INTO `rappels_table` (`id`, `date_rappel`, `objet`, `agent`) VALUES
(19, '2025-08-27', 'zdz', 'azer az'),
(20, '2025-08-27', 'zdz', 'abcd efg');

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

--
-- Table structure for table `receipts_table`
--

DROP TABLE IF EXISTS `receipts_table`;
CREATE TABLE IF NOT EXISTS `receipts_table` (
  `id` int NOT NULL AUTO_INCREMENT,
  `receipt_number` varchar(20) NOT NULL,
  `policy_number` varchar(50) NOT NULL,
  `client_cin` varchar(20) NOT NULL,
  `client_name` varchar(100) NOT NULL,
  `immatriculation` varchar(20) NOT NULL,
  `montant_total` decimal(10,2) NOT NULL,
  `montant_paye` decimal(10,2) NOT NULL,
  `reste_a_payer` decimal(10,2) NOT NULL,
  `paiement_mode` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `paiement_ref` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `echeance_date` date DEFAULT NULL,
  `paiement_statut` int NOT NULL,
  `agent` varchar(50) NOT NULL,
  `date_creation` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `receipt_number` (`receipt_number`),
  KEY `policy_number` (`policy_number`)
) ENGINE=InnoDB AUTO_INCREMENT=184 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `receipts_table`
--

INSERT INTO `receipts_table` (`id`, `receipt_number`, `policy_number`, `client_cin`, `client_name`, `immatriculation`, `montant_total`, `montant_paye`, `reste_a_payer`, `paiement_mode`, `paiement_ref`, `echeance_date`, `paiement_statut`, `agent`, `date_creation`) VALUES
(137, 'R-20250724-1641', '123', 'MC1412701', 'YOUSSEF OMARI', '123456789-B-12', 3000.00, 100.00, 0.00, 'ESPECE', '', '0000-00-00', 1, 'LAHRACH YOUSSEF', '2025-07-24 13:15:04'),
(138, 'R-20250724-1264', '123', 'MC1412701', 'YOUSSEF OMARI', '123456789-B-12', 3000.00, 900.00, 0.00, 'CHEQUE', '124', '2025-08-11', 1, 'LAHRACH YOUSSEF', '2025-07-24 13:16:44'),
(139, 'R-20250724-1767', '12347', 'ML2549', 'SAID MORABITO', '14-أ-2542', 5000.00, 2000.00, 3000.00, 'VIREMENT BANCAIRE', '', '0000-00-00', 1, 'LAHRACH YOUSSEF', '2025-07-24 13:47:37'),
(140, 'R-20250724-8307', '12347', 'ML2549', 'SAID MORABITO', '14-أ-2542', 5000.00, 1500.00, 3000.00, 'ESPECE', '', NULL, 1, 'LAHRACH YOUSSEF', '2025-07-24 13:48:18'),
(141, 'R-20250724-7544', '12347', 'ML2549', 'SAID MORABITO', '14-أ-2542', 5000.00, 500.00, 1500.00, 'VIREMENT BANCAIRE', '', '2025-07-24', 3, 'LAHRACH YOUSSEF', '2025-07-24 13:53:09'),
(142, 'R-20250724-0437', '12347', 'ML2549', 'SAID MORABITO', '14-أ-2542', 5000.00, 500.00, 1000.00, 'ESPECE', '', '2025-07-24', 1, 'LAHRACH YOUSSEF', '2025-07-24 13:53:35'),
(143, 'R-20250724-6564', '12347', 'ML2549', 'SAID MORABITO', '14-أ-2542', 5000.00, 500.00, 500.00, 'EFFET', '1717A', '2025-07-27', 1, 'LAHRACH YOUSSEF', '2025-07-24 13:54:06'),
(144, 'R-20250729-1177', '1233', 'MC1412701', 'YOUSSEF OMARI', '11254-أ-26', 2000.00, 1000.00, 1000.00, 'ESPECE', '', '2025-07-29', 1, 'LAHRACH YOUSSEF', '2025-07-29 19:12:17'),
(145, 'R-20250729-6899', '1233', 'MC1412701', 'YOUSSEF OMARI', '11254-أ-26', 2000.00, 500.00, 1000.00, 'ESPECE', '', '2025-07-29', 1, 'LAHRACH YOUSSEF', '2025-07-29 19:39:47'),
(146, 'R-20250729-7321', '1233', 'MC1412701', 'YOUSSEF OMARI', '11254-أ-26', 2000.00, 200.00, 500.00, 'CHEQUE', '1717A', '2025-08-10', 1, 'LAHRACH YOUSSEF', '2025-07-29 19:43:28'),
(147, 'R-20250729-6422', '7894444a', 'ML2549', 'SAID MORABITO', '354-أ-78789', 1500.00, 500.00, 1000.00, 'ESPECE', '', '2025-07-29', 1, 'LAHRACH YOUSSEF', '2025-07-29 19:48:39'),
(148, 'R-20250729-9684', '123', 'MC1412701', 'YOUSSEF OMARI', '123456789-B-12', 3000.00, 900.00, 0.00, 'EFFET', '789', '2025-08-10', 3, 'LAHRACH YOUSSEF', '2025-07-29 20:08:21'),
(149, 'R-20250729-3803', '123', 'MC1412701', 'YOUSSEF OMARI', '123456789-B-12', 3000.00, 900.00, 0.00, 'EFFET', '1717A', '2025-08-09', 1, 'LAHRACH YOUSSEF', '2025-07-29 20:10:37'),
(150, 'R-20250729-3699', '123', 'MC1412701', 'YOUSSEF OMARI', '123456789-B-12', 3000.00, 1000.00, 0.00, 'CHEQUE', '124', '2025-07-29', 3, 'LAHRACH YOUSSEF', '2025-07-29 21:33:36'),
(151, 'R-20250729-7507', '123', 'MC1412701', 'YOUSSEF OMARI', '123456789-B-12', 3000.00, 1500.00, 0.00, 'ESPECE', '', '2025-07-29', 1, 'LAHRACH YOUSSEF', '2025-07-29 21:34:52'),
(152, 'R-20250729-8895', '123', 'MC1412701', 'YOUSSEF OMARI', '123456789-B-12', 3000.00, 500.00, 0.00, 'ESPECE', '', '2025-07-29', 1, 'LAHRACH YOUSSEF', '2025-07-29 22:25:14'),
(153, 'R-20250730-9002', '1233', 'MC1412701', 'YOUSSEF OMARI', '11254-أ-26', 2000.00, 200.00, 300.00, 'ESPECE', '', '2025-07-30', 1, 'LAHRACH YOUSSEF', '2025-07-30 10:57:15'),
(154, 'R-20250730-6296', '1233', 'MC1412701', 'YOUSSEF OMARI', '11254-أ-26', 2000.00, 100.00, 100.00, 'ESPECE', '', '2025-07-30', 1, 'LAHRACH YOUSSEF', '2025-07-30 15:34:07'),
(155, 'R-20250730-4833', '12347', 'ML2549', 'SAID MORABITO', '14-أ-2542', 5000.00, 100.00, 500.00, 'ESPECE', '', '2025-07-30', 1, 'LAHRACH YOUSSEF', '2025-07-30 17:58:05'),
(156, 'R-20250731-2275', '12347', 'ML2549', 'SAID MORABITO', '14-أ-2542', 5000.00, 200.00, 400.00, 'ESPECE', '', '2025-07-31', 1, 'LAHRACH YOUSSEF', '2025-07-31 08:57:35'),
(157, 'R-20250731-0990', '7894', 'OD1254', 'SAID AHMEDI', '', 5000.00, 1000.00, 4000.00, 'ESPECE', '', '2025-07-31', 1, 'LAHRACH YOUSSEF', '2025-07-31 12:14:09'),
(158, 'R-20250731-6184', '2222', 'OD1254', 'SAID AHMEDI', '87897-أ-5', 4875.00, 875.00, 4000.00, 'ESPECE', '', '2025-07-31', 1, 'LAHRACH YOUSSEF', '2025-07-31 12:23:18'),
(159, 'R-20250731-9665', '22229', 'ML2549', 'SAID MORABITO', '7894-B-99', 8000.00, 250.00, 7750.00, 'ESPECE', '', '2025-07-31', 1, 'LAHRACH YOUSSEF', '2025-07-31 13:13:54'),
(160, 'R-20250731-8316', '95', 'ML2549', 'SAID MORABITO', '', 7000.00, 2000.00, 5000.00, 'CHEQUE', '789', '2025-08-11', 1, 'LAHRACH YOUSSEF', '2025-07-31 13:22:19'),
(161, 'R-20250731-9284', '22225', 'OD1254', 'SAID AHMEDI', '7845-ب-45', 3000.00, 2000.00, 1000.00, 'ESPECE', '', '2025-07-31', 1, 'LAHRACH YOUSSEF', '2025-07-31 13:28:50'),
(162, 'R-20250731-2243', '7458', 'ML2549', 'SAID MORABITO', '', 3000.00, 500.00, 3000.00, 'CHEQUE', '78', '2025-08-09', 3, 'LAHRACH YOUSSEF', '2025-07-31 13:35:31'),
(163, 'R-20250731-2407', '222278', 'ML2549', 'SAID MORABITO', '', 8000.00, 1000.00, 7000.00, 'ESPECE', '', '2025-07-31', 1, 'LAHRACH YOUSSEF', '2025-07-31 13:53:44'),
(164, 'R-20250731-5979', '7956', 'ML2549', 'SAID MORABITO', '4875-ب-2', 2000.00, 1500.00, 2000.00, 'VIREMENT BANCAIRE', '', '2025-07-31', 1, 'LAHRACH YOUSSEF', '2025-07-31 13:59:14'),
(165, 'R-20250804-8428', '12347', 'ML2549', 'SAID MORABITO', '14-أ-2542', 5000.00, 200.00, 200.00, 'ESPECE', '', '2025-08-04', 1, 'LAHRACH YOUSSEF', '2025-08-04 12:03:44'),
(166, 'R-20250804-5609', '784', 'RO25', 'KHALID ELAMIR', '', 3000.00, 500.00, 2500.00, 'ESPECE', '', '2025-08-04', 1, 'LAHRACH YOUSSEF', '2025-08-04 12:43:17'),
(167, 'R-20250804-0058', '123A', 'RO25', 'KHALID ELAMIR', '7777-B-77', 2000.00, 500.00, 1500.00, 'CHEQUE', '1717A', '2025-08-30', 1, 'LAHRACH YOUSSEF', '2025-08-04 12:54:25'),
(168, 'R-20250804-3547', '123A', 'RO25', 'KHALID ELAMIR', '7777-B-77', 2000.00, 500.00, 1500.00, 'ESPECE', '', '2025-08-04', 1, 'LAHRACH YOUSSEF', '2025-08-04 12:54:57'),
(169, 'R-20250804-1221', '14789V', 'E589707', 'NIZARI RACHID', '478545-*-26', 7000.00, 2500.00, 4500.00, 'ESPECE', '', '2025-08-04', 1, 'LAHRACH YOUSSEF', '2025-08-04 16:38:20'),
(170, 'R-20250804-3807', '7485', 'E589707', 'NIZARI RACHID', 'MAISON', 30000.00, 100.00, 29900.00, 'ESPECE', '', '2025-08-04', 1, 'LAHRACH YOUSSEF', '2025-08-04 16:42:22'),
(171, 'R-20250804-2254', '14789V', 'E589707', 'NIZARI RACHID', '478545-*-26', 7000.00, 500.00, 4500.00, 'CHEQUE', '77854', '2025-08-14', 3, 'LAHRACH YOUSSEF', '2025-08-04 16:54:29'),
(172, 'R-20250804-2434', '14789V', 'E589707', 'NIZARI RACHID', '478545-*-26', 7000.00, 500.00, 4500.00, 'EFFET', '8798798', '2025-08-06', 1, 'LAHRACH YOUSSEF', '2025-08-04 16:59:19'),
(173, 'R-20250806-1589', '1238', 'EC1254', 'MOURAD NAJI', '789-B-74', 3000.00, 5000.00, 0.00, 'ESPECE', '', '2025-08-06', 1, 'LAHRACH YOUSSEF', '2025-08-06 12:10:36'),
(174, 'R-20250806-0282', '12388', 'EC1254', 'MOURAD NAJI', '7899-B-74', 3000.00, 5000.00, 0.00, 'ESPECE', '', '2025-08-06', 1, 'LAHRACH YOUSSEF', '2025-08-06 12:11:00'),
(175, 'R-20250806-9323', '12387', 'EC1254', 'MOURAD NAJI', 'LOCAL COMMERCIAL', 3000.00, 500.00, 2500.00, 'ESPECE', '', '2025-08-06', 1, 'LAHRACH YOUSSEF', '2025-08-06 12:12:10'),
(176, 'R-20250806-7903', '14789V', 'E589707', 'NIZARI RACHID', '478545-*-26', 7000.00, 1500.00, 4000.00, 'CHEQUE', '789', '2025-08-11', 1, 'LAHRACH YOUSSEF', '2025-08-06 12:51:25'),
(177, 'R-20250813-6243', '14793', 'OD1254', 'SAID AHMEDI', '1478-أ-14', 3000.00, 500.00, 2500.00, 'ESPECE', '', '2025-08-13', 1, 'LAHRACH YOUSSEF', '2025-08-13 17:55:10'),
(178, 'R-20250813-5497', '14793', 'OD1254', 'SAID AHMEDI', '1478-أ-14', 3000.00, 500.00, 2500.00, 'CHEQUE', '789', '2025-08-21', 3, 'LAHRACH YOUSSEF', '2025-08-13 17:56:59'),
(179, 'R-20250813-6983', '14789V', 'E589707', 'NIZARI RACHID', '478545-*-26', 7000.00, 500.00, 2500.00, 'CHEQUE', '1478', '2025-08-22', 1, 'LAHRACH YOUSSEF', '2025-08-13 17:59:07'),
(180, 'R-20250827-3600', '14789V', 'E589707', 'NIZARI RACHID', '478545-*-26', 7000.00, 500.00, 2000.00, 'CHEQUE', '1717As', '2025-08-27', 1, 'LAHRACH YOUSSEF', '2025-08-27 16:33:37'),
(181, 'R-20250827-1993', 'q454', 'RO25', 'KHALID ELAMIR', '7849-أ-26', 3000.00, 1500.00, 1500.00, 'ESPECE', '', '2025-08-27', 1, 'LAHRACH YOUSSEF', '2025-08-27 16:47:53'),
(182, 'R-20250827-7515', '74859', 'MC1412701', 'YOUSSEF OMARI', '78945-أ-26', 5000.00, 2000.00, 3000.00, 'ESPECE', '', '2025-08-27', 1, 'LAHRACH YOUSSEF', '2025-08-27 17:01:50'),
(183, 'R-20250827-6146', '74859', 'MC1412701', 'YOUSSEF OMARI', '78945-أ-26', 5000.00, 500.00, 3000.00, 'ESPECE', '', '2025-08-27', 1, 'LAHRACH YOUSSEF', '2025-08-27 19:38:23');

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

--
-- Table structure for table `roles_table`
--

DROP TABLE IF EXISTS `roles_table`;
CREATE TABLE IF NOT EXISTS `roles_table` (
  `id` int NOT NULL AUTO_INCREMENT,
  `role_name` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `roles_table`
--

INSERT INTO `roles_table` (`id`, `role_name`) VALUES
(1, 'Super admin'),
(2, 'Admin'),
(3, 'Agent');

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

--
-- Table structure for table `users_compagnie`
--

DROP TABLE IF EXISTS `users_compagnie`;
CREATE TABLE IF NOT EXISTS `users_compagnie` (
  `id` int NOT NULL AUTO_INCREMENT,
  `compagnie_ref` varchar(50) NOT NULL,
  `first_name` varchar(60) NOT NULL,
  `last_name` varchar(60) NOT NULL,
  `phone_num` varchar(20) NOT NULL,
  `user_email` varchar(190) NOT NULL,
  `user_pass` varchar(255) NOT NULL,
  `role` varchar(30) NOT NULL DEFAULT 'admin',
  `status` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_phone_email` (`phone_num`,`user_email`),
  KEY `idx_compagnie_ref` (`compagnie_ref`),
  KEY `idx_user_email` (`user_email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- Table structure for table `users_table`
--

DROP TABLE IF EXISTS `users_table`;
CREATE TABLE IF NOT EXISTS `users_table` (
  `id` int NOT NULL AUTO_INCREMENT,
  `first_name` varchar(60) NOT NULL,
  `last_name` varchar(60) NOT NULL,
  `phone_num` varchar(12) NOT NULL,
  `user_email` varchar(100) NOT NULL,
  `user_pass` varchar(250) NOT NULL,
  `recup_pass` varchar(250) NOT NULL,
  `recup_expiry` datetime NOT NULL,
  `user_role` varchar(20) NOT NULL,
  `status` int NOT NULL,
  `must_change_password` varchar(250) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `phone_num` (`phone_num`,`user_email`)
) ENGINE=MyISAM AUTO_INCREMENT=18 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `users_table`
--

INSERT INTO `users_table` (`id`, `first_name`, `last_name`, `phone_num`, `user_email`, `user_pass`, `recup_pass`, `recup_expiry`, `user_role`, `status`, `must_change_password`) VALUES
(8, 'LAHRACH', 'YOUSSEF', '06654465853', 'lahrach82@gmail.com', '$2y$10$IwiNHzl59kzeSU4t9DUwpeTqUwqsgFpo8itTr0BcfGr4v.NmUm8zC', '', '0000-00-00 00:00:00', 'Super admin', 1, '');

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

--
-- Table structure for table `user_permissions`
--

DROP TABLE IF EXISTS `user_permissions`;
CREATE TABLE IF NOT EXISTS `user_permissions` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_email` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `page` varchar(150) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_user_page` (`user_email`,`page`(50))
) ENGINE=MyISAM AUTO_INCREMENT=204 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

DELIMITER $$
--
-- Events
--
DROP EVENT IF EXISTS `RefreshNotificationsEvent`$$
CREATE DEFINER=`root`@`localhost` EVENT `RefreshNotificationsEvent` ON SCHEDULE EVERY 5 MINUTE STARTS '2025-07-30 18:06:51' ON COMPLETION NOT PRESERVE ENABLE DO CALL RefreshNotifications()$$

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