-- Table: clients_table
CREATE TABLE `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;

-- Table: compagnie_table
CREATE TABLE `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;

-- Table: facture_table
CREATE TABLE `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=46 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Table: lettres_table
CREATE TABLE `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;

-- Table: marques_transport
CREATE TABLE `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;

-- Table: menu_items
CREATE TABLE `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',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=52 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Table: menu_table
CREATE TABLE `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;

-- Table: notification_log
CREATE TABLE `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;

-- Table: notifications
CREATE TABLE `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=1327 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Table des notifications syst├¿me';

-- Table: pages_table
CREATE TABLE `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: paiement_mode_table
CREATE TABLE `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;

-- Table: policy_table
CREATE TABLE `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,
  `agent` varchar(60) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `n_policy` (`n_policy`)
) ENGINE=MyISAM AUTO_INCREMENT=80 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Table: policy_type
CREATE TABLE `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;

-- Table: rappels_table
CREATE TABLE `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=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Table: receipts_table
CREATE TABLE `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=177 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Table: roles_table
CREATE TABLE `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;

-- Table: user_permissions
CREATE TABLE `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=134 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Table: users_table
CREATE TABLE `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,
  PRIMARY KEY (`id`),
  UNIQUE KEY `phone_num` (`phone_num`,`user_email`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

