-- ================================================================
-- STRUCTURE MYSQL OPTIMISÉE - RCI RECRUTEMENT
-- Base de données candidats avec intégration CATS API v3
-- ================================================================

-- Connexion à votre base existante
-- Database: rcintern_recruter  
-- Username: rcintern_rci_app
-- Password: WAt3AnV!(mD(

USE rcintern_recruter;

-- ================================================================
-- 1. TABLE PRINCIPALE CANDIDATS
-- ================================================================
CREATE TABLE candidates (
    -- Identifiants
    id INT PRIMARY KEY AUTO_INCREMENT,
    cats_id INT UNIQUE NOT NULL COMMENT 'ID candidat dans CATS',
    
    -- Informations personnelles
    first_name VARCHAR(100) NOT NULL,
    middle_name VARCHAR(100),
    last_name VARCHAR(100) NOT NULL,
    title VARCHAR(250) COMMENT 'Poste recherché/actuel',
    
    -- Emails (structure CATS)
    email_primary VARCHAR(150),
    email_secondary VARCHAR(150),
    
    -- Téléphones (structure CATS)
    phone_home VARCHAR(25),
    phone_cell VARCHAR(25), 
    phone_work VARCHAR(25),
    phone_other VARCHAR(25),
    
    -- Adresse complète
    address_street VARCHAR(255),
    address_city VARCHAR(100),
    address_state VARCHAR(100),
    address_postal_code VARCHAR(20),
    country_code CHAR(2) DEFAULT 'FR',
    
    -- Coordonnées web
    website VARCHAR(255),
    social_media_urls JSON COMMENT 'LinkedIn, etc.',
    
    -- Informations professionnelles
    current_employer VARCHAR(250),
    current_pay VARCHAR(100) COMMENT 'Salaire actuel',
    desired_pay VARCHAR(100) COMMENT 'Salaire souhaité',
    date_available DATE COMMENT 'Date de disponibilité',
    key_skills TEXT COMMENT 'Compétences clés',
    
    -- Préférences
    is_willing_to_relocate BOOLEAN DEFAULT FALSE,
    best_time_to_call VARCHAR(100),
    
    -- Statuts et flags
    is_hot BOOLEAN DEFAULT FALSE COMMENT 'Candidat prioritaire',
    is_active BOOLEAN DEFAULT TRUE COMMENT 'Candidat actif',
    is_registered BOOLEAN DEFAULT FALSE COMMENT 'Candidat inscrit portail',
    
    -- Données CATS
    source VARCHAR(100) COMMENT 'Source du candidat',
    notes TEXT COMMENT 'Notes libres',
    owner_id INT COMMENT 'ID propriétaire dans CATS',
    entered_by_id INT COMMENT 'ID créateur dans CATS',
    contact_id INT COMMENT 'ID contact lié dans CATS',
    
    -- Timestamps
    date_created TIMESTAMP NULL COMMENT 'Date création CATS',
    date_modified TIMESTAMP NULL COMMENT 'Date modification CATS', 
    last_sync TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Index pour performance
    INDEX idx_cats_id (cats_id),
    INDEX idx_email_primary (email_primary),
    INDEX idx_name (last_name, first_name),
    INDEX idx_location (address_city, address_postal_code),
    INDEX idx_status (is_active, is_hot),
    INDEX idx_sync (last_sync),
    INDEX idx_employer (current_employer),
    
    -- Index de recherche full-text
    FULLTEXT idx_skills (key_skills),
    FULLTEXT idx_notes (notes),
    FULLTEXT idx_search (first_name, last_name, title, key_skills, notes)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 
COMMENT='Table principale candidats synchronisée avec CATS';

-- ================================================================
-- 2. HISTORIQUE PROFESSIONNEL  
-- ================================================================
CREATE TABLE work_history (
    id INT PRIMARY KEY AUTO_INCREMENT,
    candidate_id INT NOT NULL,
    cats_work_id INT COMMENT 'ID historique dans CATS',
    
    -- Poste
    title VARCHAR(250) NOT NULL,
    
    -- Employeur (peut être lié à une entreprise CATS ou libre)
    employer_linked BOOLEAN DEFAULT FALSE,
    employer_company_id INT COMMENT 'ID entreprise CATS si lié',
    employer_name VARCHAR(250),
    employer_city VARCHAR(100),
    employer_state VARCHAR(100),
    
    -- Superviseur (peut être lié à un contact CATS ou libre)
    supervisor_linked BOOLEAN DEFAULT FALSE,
    supervisor_contact_id INT COMMENT 'ID contact CATS si lié',
    supervisor_name VARCHAR(150),
    supervisor_phone VARCHAR(25),
    
    -- Dates et statuts
    start_date DATE,
    end_date DATE,
    is_current BOOLEAN DEFAULT FALSE,
    is_verified BOOLEAN DEFAULT FALSE,
    reason_for_leaving TEXT,
    
    -- Timestamps
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Contraintes et index
    FOREIGN KEY (candidate_id) REFERENCES candidates(id) ON DELETE CASCADE,
    INDEX idx_candidate (candidate_id),
    INDEX idx_dates (start_date, end_date),
    INDEX idx_current (is_current),
    INDEX idx_employer (employer_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Historique professionnel des candidats';

-- ================================================================
-- 3. CV ET ATTACHMENTS
-- ================================================================
CREATE TABLE candidate_attachments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    candidate_id INT NOT NULL,
    cats_attachment_id INT COMMENT 'ID attachment dans CATS',
    
    -- Fichier
    filename VARCHAR(255) NOT NULL,
    original_filename VARCHAR(255),
    file_size BIGINT COMMENT 'Taille en bytes',
    mime_type VARCHAR(100),
    file_path VARCHAR(500) COMMENT 'Chemin local si stocké',
    
    -- Type et statuts
    is_resume BOOLEAN DEFAULT FALSE COMMENT 'Est un CV',
    is_parsed BOOLEAN DEFAULT FALSE COMMENT 'CV parsé automatiquement',
    parsing_status ENUM('pending', 'success', 'failed') DEFAULT 'pending',
    parsing_data JSON COMMENT 'Données extraites du parsing',
    
    -- Version et historique
    version VARCHAR(50),
    upload_source VARCHAR(100) DEFAULT 'cats_sync',
    
    -- Timestamps
    upload_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    parsed_at TIMESTAMP NULL,
    
    -- Contraintes et index
    FOREIGN KEY (candidate_id) REFERENCES candidates(id) ON DELETE CASCADE,
    INDEX idx_candidate (candidate_id),
    INDEX idx_resume (is_resume),
    INDEX idx_parsing (is_parsed, parsing_status),
    INDEX idx_cats_attachment (cats_attachment_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='CV et pièces jointes des candidats';

-- ================================================================
-- 4. CHAMPS PERSONNALISÉS CATS
-- ================================================================
CREATE TABLE candidate_custom_fields (
    id INT PRIMARY KEY AUTO_INCREMENT,
    candidate_id INT NOT NULL,
    cats_field_id INT NOT NULL COMMENT 'ID du champ custom dans CATS',
    cats_definition_id INT COMMENT 'ID définition champ dans CATS',
    
    -- Valeur et métadonnées
    field_name VARCHAR(150) COMMENT 'Nom du champ pour référence',
    field_type ENUM('text', 'textarea', 'checkbox', 'date', 'dropdown', 'radio', 'checkboxes', 'user', 'number'),
    field_value TEXT COMMENT 'Valeur du champ',
    
    -- Timestamps
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Contraintes et index
    FOREIGN KEY (candidate_id) REFERENCES candidates(id) ON DELETE CASCADE,
    UNIQUE KEY unique_candidate_field (candidate_id, cats_field_id),
    INDEX idx_field_name (field_name),
    INDEX idx_field_type (field_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Champs personnalisés des candidats depuis CATS';

-- ================================================================
-- 5. ACTIVITÉS ET INTERACTIONS
-- ================================================================
CREATE TABLE candidate_activities (
    id INT PRIMARY KEY AUTO_INCREMENT,
    candidate_id INT NOT NULL,
    cats_activity_id INT COMMENT 'ID activité dans CATS',
    
    -- Type et contenu
    activity_type ENUM('email', 'meeting', 'call_talked', 'call_lvm', 'call_missed', 'text_message', 'other'),
    regarding_job_id INT COMMENT 'ID job lié dans CATS',
    notes TEXT,
    annotation VARCHAR(100) DEFAULT 'General',
    
    -- Dates et utilisateur
    activity_date TIMESTAMP,
    entered_by_id INT COMMENT 'ID utilisateur CATS',
    
    -- Timestamps
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- Contraintes et index
    FOREIGN KEY (candidate_id) REFERENCES candidates(id) ON DELETE CASCADE,
    INDEX idx_candidate (candidate_id),
    INDEX idx_type (activity_type),
    INDEX idx_date (activity_date),
    INDEX idx_cats_activity (cats_activity_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Activités et interactions avec les candidats';

-- ================================================================
-- 6. TAGS ET ÉTIQUETTES
-- ================================================================
CREATE TABLE tags (
    id INT PRIMARY KEY AUTO_INCREMENT,
    cats_tag_id INT UNIQUE COMMENT 'ID tag dans CATS',
    name VARCHAR(100) NOT NULL,
    color VARCHAR(7) COMMENT 'Couleur hex du tag',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE candidate_tags (
    candidate_id INT,
    tag_id INT,
    assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    PRIMARY KEY (candidate_id, tag_id),
    FOREIGN KEY (candidate_id) REFERENCES candidates(id) ON DELETE CASCADE,
    FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================================
-- 7. SYNCHRONISATION ET LOGS
-- ================================================================
CREATE TABLE sync_logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    sync_type ENUM('full_sync', 'webhook', 'manual', 'partial'),
    entity_type ENUM('candidate', 'work_history', 'attachment', 'activity', 'custom_field'),
    entity_id INT COMMENT 'ID local de l\'entité',
    cats_id INT COMMENT 'ID CATS de l\'entité',
    
    -- Détails de la synchronisation
    action ENUM('create', 'update', 'delete'),
    status ENUM('success', 'failed', 'skipped'),
    error_message TEXT,
    webhook_event VARCHAR(100) COMMENT 'Type d\'événement webhook',
    
    -- Données pour debug
    request_data JSON COMMENT 'Données envoyées à CATS',
    response_data JSON COMMENT 'Réponse de CATS',
    
    -- Timestamps
    started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    completed_at TIMESTAMP NULL,
    
    -- Index
    INDEX idx_sync_type (sync_type),
    INDEX idx_entity (entity_type, entity_id),
    INDEX idx_status (status),
    INDEX idx_date (started_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Logs de synchronisation avec CATS';

-- ================================================================
-- 8. RECHERCHES SAUVEGARDÉES CLIENTS
-- ================================================================
CREATE TABLE saved_searches (
    id INT PRIMARY KEY AUTO_INCREMENT,
    client_name VARCHAR(150) NOT NULL,
    search_name VARCHAR(150) NOT NULL,
    
    -- Critères de recherche  
    filters JSON COMMENT 'Critères de recherche stockés en JSON',
    
    -- Statistiques
    last_executed TIMESTAMP NULL,
    execution_count INT DEFAULT 0,
    results_count INT DEFAULT 0,
    
    -- Timestamps
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Index
    INDEX idx_client (client_name),
    INDEX idx_last_executed (last_executed)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Recherches sauvegardées par les clients';

-- ================================================================
-- 9. STATISTIQUES ET MÉTRIQUES
-- ================================================================
CREATE TABLE candidate_stats (
    id INT PRIMARY KEY AUTO_INCREMENT,
    candidate_id INT NOT NULL,
    
    -- Compteurs d'activité
    total_activities INT DEFAULT 0,
    total_applications INT DEFAULT 0,
    total_interviews INT DEFAULT 0,
    
    -- Dates importantes
    first_contact DATE,
    last_activity TIMESTAMP,
    last_viewed TIMESTAMP,
    
    -- Scores et évaluations
    profile_completeness_score TINYINT DEFAULT 0 COMMENT 'Score sur 100',
    search_ranking_score DECIMAL(5,2) DEFAULT 0,
    
    -- Compteurs de vues
    profile_views INT DEFAULT 0,
    cv_downloads INT DEFAULT 0,
    
    -- Timestamps
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Contraintes et index
    FOREIGN KEY (candidate_id) REFERENCES candidates(id) ON DELETE CASCADE,
    UNIQUE KEY unique_candidate_stats (candidate_id),
    INDEX idx_completeness (profile_completeness_score),
    INDEX idx_ranking (search_ranking_score)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Statistiques et métriques des candidats';

-- ================================================================
-- VUES POUR FACILITER LES REQUÊTES
-- ================================================================

-- Vue candidats avec statistiques
CREATE VIEW v_candidates_complete AS
SELECT 
    c.*,
    cs.profile_completeness_score,
    cs.total_activities,
    cs.last_activity,
    cs.profile_views,
    GROUP_CONCAT(t.name SEPARATOR ', ') as tags_list,
    COUNT(DISTINCT wh.id) as work_experiences_count,
    COUNT(DISTINCT ca.id) as attachments_count
FROM candidates c
LEFT JOIN candidate_stats cs ON c.id = cs.candidate_id
LEFT JOIN candidate_tags ct ON c.id = ct.candidate_id
LEFT JOIN tags t ON ct.tag_id = t.id
LEFT JOIN work_history wh ON c.id = wh.candidate_id
LEFT JOIN candidate_attachments ca ON c.id = ca.candidate_id AND ca.is_resume = TRUE
WHERE c.is_active = TRUE
GROUP BY c.id;

-- Vue pour recherche rapide
CREATE VIEW v_candidates_search AS
SELECT 
    id,
    cats_id,
    CONCAT(first_name, ' ', last_name) as full_name,
    first_name,
    last_name,
    title,
    email_primary,
    phone_cell,
    address_city,
    current_employer,
    key_skills,
    desired_pay,
    is_hot,
    last_sync
FROM candidates 
WHERE is_active = TRUE
ORDER BY last_sync DESC;

-- ================================================================
-- TRIGGERS POUR MAINTENIR LES STATISTIQUES
-- ================================================================

DELIMITER //

-- Trigger pour mettre à jour les stats lors de l'ajout d'activités
CREATE TRIGGER update_candidate_stats_on_activity
AFTER INSERT ON candidate_activities
FOR EACH ROW
BEGIN
    INSERT INTO candidate_stats (candidate_id, total_activities, last_activity)
    VALUES (NEW.candidate_id, 1, NEW.activity_date)
    ON DUPLICATE KEY UPDATE 
        total_activities = total_activities + 1,
        last_activity = GREATEST(last_activity, NEW.activity_date);
END//

-- Trigger pour calculer le score de complétude
CREATE TRIGGER calculate_completeness_score
AFTER UPDATE ON candidates
FOR EACH ROW
BEGIN
    DECLARE score TINYINT DEFAULT 0;
    
    -- Calcul du score de complétude (sur 100)
    IF NEW.first_name IS NOT NULL AND NEW.first_name != '' THEN SET score = score + 10; END IF;
    IF NEW.last_name IS NOT NULL AND NEW.last_name != '' THEN SET score = score + 10; END IF;
    IF NEW.email_primary IS NOT NULL AND NEW.email_primary != '' THEN SET score = score + 15; END IF;
    IF NEW.phone_cell IS NOT NULL AND NEW.phone_cell != '' THEN SET score = score + 10; END IF;
    IF NEW.title IS NOT NULL AND NEW.title != '' THEN SET score = score + 15; END IF;
    IF NEW.key_skills IS NOT NULL AND NEW.key_skills != '' THEN SET score = score + 20; END IF;
    IF NEW.address_city IS NOT NULL AND NEW.address_city != '' THEN SET score = score + 10; END IF;
    IF NEW.current_employer IS NOT NULL AND NEW.current_employer != '' THEN SET score = score + 10; END IF;
    
    INSERT INTO candidate_stats (candidate_id, profile_completeness_score)
    VALUES (NEW.id, score)
    ON DUPLICATE KEY UPDATE profile_completeness_score = score;
END//

DELIMITER ;