--
-- Host: localhost    Database: planning_db
-- ------------------------------------------------------


--
-- Table structure for table `agents`
--

CREATE TABLE `agents` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `matricule` varchar(20) DEFAULT NULL,
  `nom` varchar(100) NOT NULL,
  `prenom` varchar(100) NOT NULL,
  `email` varchar(150) DEFAULT NULL,
  `telephone` varchar(20) DEFAULT NULL,
  `poste` varchar(100) DEFAULT 'Agent de Sécurité',
  `taux_horaire` decimal(6,2) DEFAULT 0.00,
  `date_embauche` date DEFAULT NULL,
  `actif` tinyint(1) DEFAULT 1,
  `date_creation` timestamp NOT NULL DEFAULT current_timestamp(),
  `date_modification` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_actif` (`actif`),
  KEY `idx_nom` (`nom`,`prenom`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `agents`
--

INSERT INTO `agents` VALUES (4,NULL,'Belkhouane','Djilali',NULL,'0778125791','Agent de Sécurité',0.00,NULL,1,'2025-11-13 19:49:18','2025-11-13 20:09:31'),(5,NULL,'ADDA-BENYOUCEF','ilias','iliasadda@yahoo.com','0764417751','Agent de Sécurité',0.00,NULL,1,'2025-11-13 20:02:59','2025-11-14 11:10:20'),(15,NULL,'iness','iness',NULL,'','Agent de Sécurité',0.00,NULL,1,'2025-11-13 20:09:39','2025-11-13 20:19:48'),(18,NULL,'ADDA-BENYOUCEF','sihem','sihembaradel@hotmail.fr','0638114100','Agent de Sécurité',0.00,NULL,1,'2025-11-13 20:14:59','2025-11-14 09:50:50'),(20,NULL,'IZRI','JUBA','','0750017365','Agent de Sécurité',0.00,NULL,1,'2025-11-13 20:19:54','2025-11-13 20:19:54');

--
-- Table structure for table `compteur_points`
--

CREATE TABLE `compteur_points` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `agent_id` int(11) NOT NULL,
  `mois` tinyint(4) NOT NULL,
  `annee` smallint(6) NOT NULL,
  `heures_realisees` decimal(6,2) DEFAULT 0.00,
  `heures_contractuelles` decimal(6,2) DEFAULT 0.00,
  `heures_sup` decimal(6,2) DEFAULT 0.00,
  `points_gagnes` decimal(6,2) DEFAULT 0.00,
  `points_utilises` decimal(6,2) DEFAULT 0.00,
  `solde_precedent` decimal(6,2) DEFAULT 0.00,
  `solde_final` decimal(6,2) DEFAULT 0.00,
  `date_creation` timestamp NOT NULL DEFAULT current_timestamp(),
  `date_modification` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_compteur` (`agent_id`,`mois`,`annee`),
  KEY `idx_agent_periode` (`agent_id`,`annee`,`mois`),
  CONSTRAINT `compteur_points_ibfk_1` FOREIGN KEY (`agent_id`) REFERENCES `agents` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `compteur_points`
--

INSERT INTO `compteur_points` VALUES (1,18,11,2025,80.00,105.00,0.00,0.00,0.00,0.00,0.00,'2025-11-14 10:26:43','2025-11-14 10:58:44');

--
-- Table structure for table `plannings`
--

CREATE TABLE `plannings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `agent_id` int(11) NOT NULL,
  `type_planning` enum('simple','complet') DEFAULT 'simple',
  `mois` tinyint(4) NOT NULL,
  `annee` smallint(6) NOT NULL,
  `data_json` mediumtext DEFAULT NULL,
  `total_heures` decimal(6,2) DEFAULT 0.00,
  `date_creation` timestamp NOT NULL DEFAULT current_timestamp(),
  `date_modification` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_planning` (`agent_id`,`mois`,`annee`),
  KEY `idx_periode` (`annee`,`mois`),
  CONSTRAINT `plannings_ibfk_1` FOREIGN KEY (`agent_id`) REFERENCES `agents` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=78 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `plannings`
--

INSERT INTO `plannings` VALUES (1,4,'simple',11,2025,'{\"jours\":{\"2025-11-03\":{\"debut\":\"20:00\",\"fin\":\"07:00\"},\"2025-11-04\":{\"debut\":\"20:00\",\"fin\":\"07:00\"},\"2025-11-05\":{\"debut\":\"20:00\",\"fin\":\"07:00\"},\"2025-11-06\":{\"debut\":\"20:00\",\"fin\":\"07:00\"},\"2025-11-10\":{\"debut\":\"18:00\",\"fin\":\"00:00\"},\"2025-11-11\":{\"debut\":\"08:00\",\"fin\":\"12:00\",\"debut2\":\"18:00\",\"fin2\":\"00:00\"},\"2025-11-13\":{\"debut\":\"07:00\",\"fin\":\"00:00\"},\"2025-11-21\":{\"debut\":\"18:00\",\"fin\":\"01:00\"}}}',84.00,'2025-11-13 19:51:41','2025-11-21 14:54:34'),(10,20,'simple',11,2025,'{\"jours\":{\"2025-11-10\":{\"debut\":\"18:00\",\"fin\":\"07:00\"},\"2025-11-11\":{\"debut\":\"18:00\",\"fin\":\"07:00\"},\"2025-11-12\":{\"debut\":\"18:00\",\"fin\":\"07:00\"},\"2025-11-13\":{\"debut\":\"18:00\",\"fin\":\"07:00\"}}}',52.00,'2025-11-13 20:24:16','2025-11-13 21:38:38');

--
-- Table structure for table `types_journee`
--

CREATE TABLE `types_journee` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(20) NOT NULL,
  `libelle` varchar(100) NOT NULL,
  `comptabilise_heures` tinyint(1) DEFAULT 1,
  `genere_points` tinyint(1) DEFAULT 1,
  `forfait_heures` decimal(4,2) DEFAULT 0.00,
  `couleur` varchar(7) DEFAULT '#FFFFFF',
  `actif` tinyint(1) DEFAULT 1,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `types_journee`
--

INSERT INTO `types_journee` VALUES (1,'VACATION','Vacation',1,1,0.00,'#E3F2FD',1),(2,'CP','Congé Payé',1,0,7.00,'#FFF3E0',1),(3,'RTT','RTT',1,0,7.00,'#F3E5F5',1),(4,'FORMATION','Formation',1,0,0.00,'#E8F5E9',1),(5,'MALADIE','Maladie',0,0,0.00,'#FFEBEE',1),(6,'ABSENCE','Absence',0,0,0.00,'#FAFAFA',1),(7,'REPOS','Repos',0,0,0.00,'#F5F5F5',1);


