/*M!999999\- enable the sandbox mode */ 
-- MariaDB dump 10.19  Distrib 10.6.19-MariaDB, for Linux (x86_64)
--
-- Host: localhost    Database: rcintern_recruter
-- ------------------------------------------------------
-- Server version	10.6.19-MariaDB

/*!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 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `candidate_activities`
--

DROP TABLE IF EXISTS `candidate_activities`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `candidate_activities` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `candidate_id` int(11) NOT NULL,
  `cats_activity_id` int(11) DEFAULT NULL COMMENT 'ID activité dans CATS',
  `activity_type` enum('email','meeting','call_talked','call_lvm','call_missed','text_message','other') DEFAULT NULL,
  `regarding_job_id` int(11) DEFAULT NULL COMMENT 'ID job lié dans CATS',
  `notes` text DEFAULT NULL,
  `annotation` varchar(100) DEFAULT 'General',
  `activity_date` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `entered_by_id` int(11) DEFAULT NULL COMMENT 'ID utilisateur CATS',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_candidate` (`candidate_id`),
  KEY `idx_type` (`activity_type`),
  KEY `idx_date` (`activity_date`),
  KEY `idx_cats_activity` (`cats_activity_id`),
  CONSTRAINT `candidate_activities_ibfk_1` FOREIGN KEY (`candidate_id`) REFERENCES `candidates` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Activités et interactions avec les candidats';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `candidate_activities`
--

LOCK TABLES `candidate_activities` WRITE;
/*!40000 ALTER TABLE `candidate_activities` DISABLE KEYS */;
/*!40000 ALTER TABLE `candidate_activities` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `candidate_attachments`
--

DROP TABLE IF EXISTS `candidate_attachments`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `candidate_attachments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `candidate_id` int(11) NOT NULL,
  `cats_attachment_id` int(11) DEFAULT NULL COMMENT 'ID attachment dans CATS',
  `filename` varchar(255) NOT NULL,
  `original_filename` varchar(255) DEFAULT NULL,
  `file_size` bigint(20) DEFAULT NULL COMMENT 'Taille en bytes',
  `mime_type` varchar(100) DEFAULT NULL,
  `file_path` varchar(500) DEFAULT NULL COMMENT 'Chemin local si stocké',
  `is_resume` tinyint(1) DEFAULT 0 COMMENT 'Est un CV',
  `is_parsed` tinyint(1) DEFAULT 0 COMMENT 'CV parsé automatiquement',
  `parsing_status` enum('pending','success','failed') DEFAULT 'pending',
  `parsing_data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'Données extraites du parsing' CHECK (json_valid(`parsing_data`)),
  `version` varchar(50) DEFAULT NULL,
  `upload_source` varchar(100) DEFAULT 'cats_sync',
  `upload_date` timestamp NOT NULL DEFAULT current_timestamp(),
  `parsed_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_candidate` (`candidate_id`),
  KEY `idx_resume` (`is_resume`),
  KEY `idx_parsing` (`is_parsed`,`parsing_status`),
  KEY `idx_cats_attachment` (`cats_attachment_id`),
  CONSTRAINT `candidate_attachments_ibfk_1` FOREIGN KEY (`candidate_id`) REFERENCES `candidates` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='CV et pièces jointes des candidats';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `candidate_attachments`
--

LOCK TABLES `candidate_attachments` WRITE;
/*!40000 ALTER TABLE `candidate_attachments` DISABLE KEYS */;
/*!40000 ALTER TABLE `candidate_attachments` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `candidate_custom_fields`
--

DROP TABLE IF EXISTS `candidate_custom_fields`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `candidate_custom_fields` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `candidate_id` int(11) NOT NULL,
  `cats_field_id` int(11) NOT NULL COMMENT 'ID du champ custom dans CATS',
  `cats_definition_id` int(11) DEFAULT NULL COMMENT 'ID définition champ dans CATS',
  `field_name` varchar(150) DEFAULT NULL COMMENT 'Nom du champ pour référence',
  `field_type` enum('text','textarea','checkbox','date','dropdown','radio','checkboxes','user','number') DEFAULT NULL,
  `field_value` text DEFAULT NULL COMMENT 'Valeur du champ',
  `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 `unique_candidate_field` (`candidate_id`,`cats_field_id`),
  KEY `idx_field_name` (`field_name`),
  KEY `idx_field_type` (`field_type`),
  CONSTRAINT `candidate_custom_fields_ibfk_1` FOREIGN KEY (`candidate_id`) REFERENCES `candidates` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Champs personnalisés des candidats depuis CATS';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `candidate_custom_fields`
--

LOCK TABLES `candidate_custom_fields` WRITE;
/*!40000 ALTER TABLE `candidate_custom_fields` DISABLE KEYS */;
/*!40000 ALTER TABLE `candidate_custom_fields` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `candidates`
--

DROP TABLE IF EXISTS `candidates`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `candidates` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cats_id` int(11) NOT NULL COMMENT 'ID candidat dans CATS',
  `first_name` varchar(100) NOT NULL,
  `middle_name` varchar(100) DEFAULT NULL,
  `last_name` varchar(100) NOT NULL,
  `title` varchar(250) DEFAULT NULL COMMENT 'Poste recherché/actuel',
  `email_primary` varchar(150) DEFAULT NULL,
  `email_secondary` varchar(150) DEFAULT NULL,
  `phone_home` varchar(25) DEFAULT NULL,
  `phone_cell` varchar(25) DEFAULT NULL,
  `phone_work` varchar(25) DEFAULT NULL,
  `phone_other` varchar(25) DEFAULT NULL,
  `address_street` varchar(255) DEFAULT NULL,
  `address_city` varchar(100) DEFAULT NULL,
  `address_state` varchar(100) DEFAULT NULL,
  `address_postal_code` varchar(20) DEFAULT NULL,
  `country_code` char(2) DEFAULT 'FR',
  `website` varchar(255) DEFAULT NULL,
  `social_media_urls` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'LinkedIn, etc.' CHECK (json_valid(`social_media_urls`)),
  `current_employer` varchar(250) DEFAULT NULL,
  `current_pay` varchar(100) DEFAULT NULL COMMENT 'Salaire actuel',
  `desired_pay` varchar(100) DEFAULT NULL COMMENT 'Salaire souhaité',
  `date_available` date DEFAULT NULL COMMENT 'Date de disponibilité',
  `key_skills` text DEFAULT NULL COMMENT 'Compétences clés',
  `is_willing_to_relocate` tinyint(1) DEFAULT 0,
  `best_time_to_call` varchar(100) DEFAULT NULL,
  `is_hot` tinyint(1) DEFAULT 0 COMMENT 'Candidat prioritaire',
  `is_active` tinyint(1) DEFAULT 1 COMMENT 'Candidat actif',
  `is_registered` tinyint(1) DEFAULT 0 COMMENT 'Candidat inscrit portail',
  `source` varchar(100) DEFAULT NULL COMMENT 'Source du candidat',
  `notes` text DEFAULT NULL COMMENT 'Notes libres',
  `owner_id` int(11) DEFAULT NULL COMMENT 'ID propriétaire dans CATS',
  `entered_by_id` int(11) DEFAULT NULL COMMENT 'ID créateur dans CATS',
  `contact_id` int(11) DEFAULT NULL COMMENT 'ID contact lié dans CATS',
  `date_created` timestamp NULL DEFAULT NULL COMMENT 'Date création CATS',
  `date_modified` timestamp NULL DEFAULT NULL COMMENT 'Date modification CATS',
  `last_sync` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `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 `cats_id` (`cats_id`),
  KEY `idx_cats_id` (`cats_id`),
  KEY `idx_email_primary` (`email_primary`),
  KEY `idx_name` (`last_name`,`first_name`),
  KEY `idx_location` (`address_city`,`address_postal_code`),
  KEY `idx_status` (`is_active`,`is_hot`),
  KEY `idx_sync` (`last_sync`),
  KEY `idx_employer` (`current_employer`),
  FULLTEXT KEY `idx_skills` (`key_skills`),
  FULLTEXT KEY `idx_notes` (`notes`),
  FULLTEXT KEY `idx_search` (`first_name`,`last_name`,`title`,`key_skills`,`notes`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Table principale candidats synchronisée avec CATS';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `candidates`
--

LOCK TABLES `candidates` WRITE;
/*!40000 ALTER TABLE `candidates` DISABLE KEYS */;
INSERT INTO `candidates` VALUES (4,999999,'DEMONCHY',NULL,'Gwendoline',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'FR',NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,NULL,0,1,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2025-08-22 14:45:41','2025-08-22 14:45:41','2025-08-22 14:45:41'),(5,360241009,'DEMONCHY',NULL,'Gwendoline','Professeure des écoles','gwendoline.demonchy@gmail.com',NULL,NULL,'33661234877',NULL,NULL,' 62, chemin des Chênes	','GRASSE','','06130','FR',NULL,NULL,'NICE MATIN',NULL,NULL,NULL,NULL,1,NULL,0,1,0,'CATS_API_SYNC','Epouse Monsieur Garnier Maxime',569406,569406,NULL,'2021-08-19 16:02:13','2021-08-19 16:05:44','2025-08-25 01:24:26','2025-08-22 15:48:37','2025-08-25 01:24:26'),(12,999001,'Jean',NULL,'Dupont','Développeur Python',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'FR',NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,NULL,0,1,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2025-08-25 06:06:30','2025-08-25 06:06:30','2025-08-25 06:06:30'),(13,999002,'Marie',NULL,'Martin','Infirmière autorisée',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'CA',NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,NULL,0,1,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2025-08-25 06:06:30','2025-08-25 06:06:30','2025-08-25 06:06:30'),(14,999003,'Ahmed',NULL,'Benali','Cuisinier professionnel',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'MA',NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,NULL,0,1,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2025-08-25 06:06:30','2025-08-25 06:06:30','2025-08-25 06:06:30');
/*!40000 ALTER TABLE `candidates` ENABLE KEYS */;
UNLOCK TABLES;

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

DROP TABLE IF EXISTS `work_history`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
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`),
  CONSTRAINT `work_history_ibfk_1` FOREIGN KEY (`candidate_id`) REFERENCES `candidates` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Historique professionnel des candidats';
/*!40101 SET character_set_client = @saved_cs_client */;

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

LOCK TABLES `work_history` WRITE;
/*!40000 ALTER TABLE `work_history` DISABLE KEYS */;
/*!40000 ALTER TABLE `work_history` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!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 */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2025-08-25  3:17:58
