
--
-- Table structure for table `clients`
--

CREATE TABLE `clients` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `contact_person` varchar(255) DEFAULT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `status` enum('active','inactive') DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

--
-- Dumping data for table `clients`
--

INSERT INTO `clients` VALUES (1,'Tech Solutions Inc.','admin@techsolutions.com','Marie Dubois','+33 1 45 67 89 01','active','2025-09-02 11:30:49','2025-09-02 11:30:49'),(2,'Innovate Corp','hr@innovatecorp.com','Pierre Martin','+33 1 45 67 89 02','active','2025-09-02 11:30:49','2025-09-02 11:30:49'),(3,'Startup Lab','contact@startuplab.com','Sophie Bernard','+33 1 45 67 89 03','active','2025-09-02 11:30:49','2025-09-02 11:30:49');

--
-- Table structure for table `cv_uploads`
--

CREATE TABLE `cv_uploads` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `filename` varchar(255) NOT NULL,
  `original_name` varchar(255) NOT NULL,
  `file_path` varchar(500) NOT NULL,
  `file_size` int(11) NOT NULL,
  `mime_type` varchar(100) NOT NULL,
  `status` enum('uploaded','processing','processed','error') DEFAULT 'uploaded',
  `candidate_id` int(11) DEFAULT NULL,
  `processed_data` text DEFAULT NULL,
  `error_message` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_cv_uploads_candidate` (`candidate_id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

--
-- Dumping data for table `cv_uploads`
--

INSERT INTO `cv_uploads` VALUES (1,'cv_1756813672_68b6d9681c6fd.pdf','CV (002).pdf','/home/rcintern/api.rcinternational.ca/uploads/cv/cv_1756813672_68b6d9681c6fd.pdf',622095,'','processed',32085,'{\"first_name\":\"\",\"last_name\":\"\",\"email\":\"\",\"phone\":\"\",\"position\":\"\",\"experience_years\":0,\"skills\":\"\",\"education\":\"\",\"languages\":\"\"}',NULL,'2025-09-02 11:47:52','2025-09-02 11:47:52'),(2,'cv_1756819136_68b6eec0aa864.pdf','CV (002).pdf','/home/rcintern/api.rcinternational.ca/uploads/cv/cv_1756819136_68b6eec0aa864.pdf',622095,'','processed',32086,'{\"first_name\":\"\",\"last_name\":\"\",\"email\":\"\",\"phone\":\"\",\"position\":\"\",\"experience_years\":0,\"skills\":\"\",\"education\":\"\",\"languages\":\"\"}',NULL,'2025-09-02 13:18:56','2025-09-02 13:18:56');

--
-- Table structure for table `daily_stats`
--

CREATE TABLE `daily_stats` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date` date NOT NULL,
  `cvs_processed` int(11) DEFAULT 0,
  `candidates_created` int(11) DEFAULT 0,
  `assignments_made` int(11) DEFAULT 0,
  `clients_active` int(11) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `date` (`date`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

--
-- Dumping data for table `daily_stats`
--

INSERT INTO `daily_stats` VALUES (1,'2025-09-02',26,20,12,3,'2025-09-02 11:30:49','2025-09-02 13:18:56');

--
-- Table structure for table `failed_login_attempts`
--

CREATE TABLE `failed_login_attempts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ip_address` varchar(45) NOT NULL,
  `username` varchar(50) DEFAULT NULL,
  `attempt_time` timestamp NOT NULL DEFAULT current_timestamp(),
  `user_agent` text DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

--
-- Dumping data for table `failed_login_attempts`
--

INSERT INTO `failed_login_attempts` VALUES (1,'90.93.195.75','admin','2025-08-29 09:19:17','Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/139.0.0.0 Safari/537.36'),(2,'90.93.195.75','admin','2025-08-29 09:24:50','Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/139.0.0.0 Safari/537.36'),(3,'90.93.195.75','rci_manager','2025-08-29 09:29:12','Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/139.0.0.0 Safari/537.36'),(4,'90.93.195.75','admin','2025-08-29 10:04:19','Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/139.0.0.0 Safari/537.36'),(5,'90.93.195.75','admin','2025-08-29 10:04:43','Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/139.0.0.0 Safari/537.36'),(6,'90.93.195.75','admin','2025-08-29 10:09:09','Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/139.0.0.0 Safari/537.36'),(7,'90.93.195.75','admin','2025-08-29 10:10:52','Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/139.0.0.0 Safari/537.36'),(8,'90.93.195.75','admin','2025-08-29 11:31:59','Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/139.0.0.0 Safari/537.36');

--
-- Table structure for table `rci_services`
--

CREATE TABLE `rci_services` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `target` enum('entreprise','candidat') NOT NULL,
  `price` decimal(10,2) NOT NULL,
  `currency` varchar(3) DEFAULT 'CAD',
  `type` enum('one_time','monthly','yearly') NOT NULL,
  `description` text DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=22 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

--
-- Dumping data for table `rci_services`
--

INSERT INTO `rci_services` VALUES (1,'Recherche de Base','entreprise',99.00,'CAD','one_time','Accès base de données 15,000+ candidats qualifiés',1,'2025-08-26 12:56:25'),(2,'Analyse EIMT','entreprise',50.00,'CAD','','Analyse automatisée EIMT en 24h',1,'2025-08-26 12:56:25'),(3,'Tableau de Bord Premium','entreprise',399.00,'CAD','monthly','Gestion multi-candidats temps réel',1,'2025-08-26 12:56:25'),(7,'CV Premium','candidat',100.00,'CAD','','CV premium avec validation',1,'2025-08-26 12:56:25'),(8,'Recherche de Base','entreprise',99.00,'CAD','one_time','Accès base de données 15,000+ candidats qualifiés',1,'2025-08-26 12:58:55'),(9,'Analyse EIMT','entreprise',249.00,'CAD','one_time','Analyse automatisée EIMT en 24h',1,'2025-08-26 12:58:55'),(10,'Tableau de Bord Premium','entreprise',399.00,'CAD','monthly','Gestion multi-candidats temps réel',1,'2025-08-26 12:58:55'),(11,'Profil Premium - Mensuel','candidat',19.00,'CAD','monthly','Profil premium candidat - facturation mensuelle',1,'2025-08-26 12:58:55'),(12,'Profil Premium - Annuel','candidat',199.00,'CAD','yearly','Profil premium candidat - facturation annuelle',1,'2025-08-26 12:58:55'),(14,'CV Premium','entreprise',100.00,'CAD','','CV premium avec validation',1,'2025-08-26 12:58:55'),(15,'Recherche de Base','entreprise',99.00,'CAD','one_time','Accès base de données 15,000+ candidats qualifiés',1,'2025-08-26 13:00:04'),(16,'Analyse EIMT','entreprise',249.00,'CAD','one_time','Analyse automatisée EIMT en 24h',1,'2025-08-26 13:00:04'),(17,'Tableau de Bord Premium','entreprise',399.00,'CAD','monthly','Gestion multi-candidats temps réel',1,'2025-08-26 13:00:04'),(20,'CV Standard','candidat',39.00,'CAD','one_time','CV standard automatisé',1,'2025-08-26 13:00:04');

--
-- Table structure for table `security_log`
--

CREATE TABLE `security_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `ip_address` varchar(45) NOT NULL,
  `user_agent` text DEFAULT NULL,
  `event_type` varchar(50) NOT NULL,
  `event_details` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`event_details`)),
  `severity` enum('low','medium','high','critical') DEFAULT 'low',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`)
) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

--
-- Dumping data for table `security_log`
--

INSERT INTO `security_log` VALUES (1,1,'90.93.195.75','Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/139.0.0.0 Safari/537.36','login_failed_invalid_password','{\"username\":\"admin\",\"ip\":\"90.93.195.75\"}','medium','2025-08-29 09:19:17'),(2,1,'90.93.195.75','Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/139.0.0.0 Safari/537.36','login_failed_invalid_password','{\"username\":\"admin\",\"ip\":\"90.93.195.75\"}','medium','2025-08-29 09:24:50'),(3,2,'90.93.195.75','Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/139.0.0.0 Safari/537.36','login_failed_invalid_password','{\"username\":\"rci_manager\",\"ip\":\"90.93.195.75\"}','medium','2025-08-29 09:29:12'),(4,1,'90.93.195.75','Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/139.0.0.0 Safari/537.36','login_failed_invalid_password','{\"username\":\"admin\",\"ip\":\"90.93.195.75\"}','medium','2025-08-29 10:04:19'),(5,1,'90.93.195.75','Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/139.0.0.0 Safari/537.36','login_failed_invalid_password','{\"username\":\"admin\",\"ip\":\"90.93.195.75\"}','medium','2025-08-29 10:04:43'),(6,1,'90.93.195.75','Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/139.0.0.0 Safari/537.36','login_failed_invalid_password','{\"username\":\"admin\",\"ip\":\"90.93.195.75\"}','medium','2025-08-29 10:09:09'),(7,1,'90.93.195.75','Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/139.0.0.0 Safari/537.36','login_failed_invalid_password','{\"username\":\"admin\",\"ip\":\"90.93.195.75\"}','medium','2025-08-29 10:10:52'),(8,NULL,'90.93.195.75','Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/139.0.0.0 Safari/537.36','csrf_validation_failed','{\"ip\":\"90.93.195.75\"}','high','2025-08-29 11:30:13'),(9,1,'90.93.195.75','Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/139.0.0.0 Safari/537.36','login_failed_invalid_password','{\"username\":\"admin\",\"ip\":\"90.93.195.75\"}','medium','2025-08-29 11:31:59');

--
-- Table structure for table `work_history`
--

CREATE TABLE `work_history` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `candidate_id` int(11) NOT NULL,
  `cats_work_id` int(11) DEFAULT NULL COMMENT 'ID historique dans CATS',
  `title` varchar(250) NOT NULL,
  `employer_linked` tinyint(1) DEFAULT 0,
  `employer_company_id` int(11) DEFAULT NULL COMMENT 'ID entreprise CATS si lié',
  `employer_name` varchar(250) DEFAULT NULL,
  `employer_city` varchar(100) DEFAULT NULL,
  `employer_state` varchar(100) DEFAULT NULL,
  `supervisor_linked` tinyint(1) DEFAULT 0,
  `supervisor_contact_id` int(11) DEFAULT NULL COMMENT 'ID contact CATS si lié',
  `supervisor_name` varchar(150) DEFAULT NULL,
  `supervisor_phone` varchar(25) DEFAULT NULL,
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `is_current` tinyint(1) DEFAULT 0,
  `is_verified` tinyint(1) DEFAULT 0,
  `reason_for_leaving` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_candidate` (`candidate_id`),
  KEY `idx_dates` (`start_date`,`end_date`),
  KEY `idx_current` (`is_current`),
  KEY `idx_employer` (`employer_name`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Historique professionnel des candidats';

--
-- Dumping data for table `work_history`
--



