-- database.sql - Structure de la base de données pour RCI CV Import
USE rcintern_recruter;

-- Table des clients
CREATE TABLE IF NOT EXISTS clients (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    contact_person VARCHAR(255),
    phone VARCHAR(50),
    status ENUM('active', 'inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Table des candidats
CREATE TABLE IF NOT EXISTS candidates (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    phone VARCHAR(50),
    position VARCHAR(255),
    experience_years INT,
    skills TEXT,
    education TEXT,
    languages TEXT,
    cv_filename VARCHAR(255),
    cv_path VARCHAR(500),
    status ENUM('pending', 'processing', 'completed', 'error') DEFAULT 'pending',
    client_id INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE SET NULL
);

-- Table des CV uploadés
CREATE TABLE IF NOT EXISTS cv_uploads (
    id INT PRIMARY KEY AUTO_INCREMENT,
    filename VARCHAR(255) NOT NULL,
    original_name VARCHAR(255) NOT NULL,
    file_path VARCHAR(500) NOT NULL,
    file_size INT NOT NULL,
    mime_type VARCHAR(100) NOT NULL,
    status ENUM('uploaded', 'processing', 'processed', 'error') DEFAULT 'uploaded',
    candidate_id INT NULL,
    processed_data TEXT,
    error_message TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (candidate_id) REFERENCES candidates(id) ON DELETE CASCADE
);

-- Table des assignations candidats-clients
CREATE TABLE IF NOT EXISTS candidate_assignments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    candidate_id INT NOT NULL,
    client_id INT NOT NULL,
    assigned_by VARCHAR(100),
    sent_to_client BOOLEAN DEFAULT FALSE,
    sent_at TIMESTAMP NULL,
    status ENUM('pending', 'sent', 'viewed', 'shortlisted', 'rejected') DEFAULT 'pending',
    client_notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (candidate_id) REFERENCES candidates(id) ON DELETE CASCADE,
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
    UNIQUE KEY unique_assignment (candidate_id, client_id)
);

-- Table des logs d'activité
CREATE TABLE IF NOT EXISTS activity_logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    action VARCHAR(100) NOT NULL,
    entity_type ENUM('candidate', 'client', 'cv_upload', 'assignment') NOT NULL,
    entity_id INT NOT NULL,
    details TEXT,
    user VARCHAR(100),
    ip_address VARCHAR(45),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Table des statistiques journalières
CREATE TABLE IF NOT EXISTS daily_stats (
    id INT PRIMARY KEY AUTO_INCREMENT,
    date DATE NOT NULL UNIQUE,
    cvs_processed INT DEFAULT 0,
    candidates_created INT DEFAULT 0,
    assignments_made INT DEFAULT 0,
    clients_active INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Insertion des clients de test
INSERT INTO clients (name, email, contact_person, phone) VALUES
('Tech Solutions Inc.', 'admin@techsolutions.com', 'Marie Dubois', '+33 1 45 67 89 01'),
('Innovate Corp', 'hr@innovatecorp.com', 'Pierre Martin', '+33 1 45 67 89 02'),
('Startup Lab', 'contact@startuplab.com', 'Sophie Bernard', '+33 1 45 67 89 03')
ON DUPLICATE KEY UPDATE name = VALUES(name);

-- Initialisation des statistiques pour aujourd'hui
INSERT INTO daily_stats (date, cvs_processed, candidates_created, assignments_made, clients_active) 
VALUES (CURDATE(), 24, 18, 12, 3)
ON DUPLICATE KEY UPDATE 
    cvs_processed = VALUES(cvs_processed),
    candidates_created = VALUES(candidates_created),
    assignments_made = VALUES(assignments_made),
    clients_active = VALUES(clients_active);

-- Index pour optimiser les performances
CREATE INDEX idx_candidates_email ON candidates(email);
CREATE INDEX idx_candidates_status ON candidates(status);
CREATE INDEX idx_candidates_client ON candidates(client_id);
CREATE INDEX idx_cv_uploads_status ON cv_uploads(status);
CREATE INDEX idx_assignments_candidate ON candidate_assignments(candidate_id);
CREATE INDEX idx_assignments_client ON candidate_assignments(client_id);
CREATE INDEX idx_activity_logs_date ON activity_logs(created_at);
CREATE INDEX idx_daily_stats_date ON daily_stats(date);