-- Script de création de la base de données MediRecrut
-- Base de données : wynter_medirecrut

-- Table des utilisateurs (candidats + recruteurs)
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    user_type ENUM('candidat', 'recruteur', 'manager') NOT NULL,
    nom VARCHAR(100),
    prenom VARCHAR(100),
    telephone VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    last_login TIMESTAMP NULL,
    active TINYINT(1) DEFAULT 1,
    INDEX idx_email (email),
    INDEX idx_user_type (user_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table des fonctions disponibles
CREATE TABLE IF NOT EXISTS fonctions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nom VARCHAR(100) NOT NULL UNIQUE,
    categorie VARCHAR(100),
    description TEXT,
    active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_categorie (categorie)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table des départements français
CREATE TABLE IF NOT EXISTS departements (
    id INT AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(3) NOT NULL UNIQUE,
    nom VARCHAR(100) NOT NULL,
    region VARCHAR(100),
    active TINYINT(1) DEFAULT 1,
    INDEX idx_code (code),
    INDEX idx_region (region)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table des profils candidats
CREATE TABLE IF NOT EXISTS candidats (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    cv_filename VARCHAR(255),
    cv_path VARCHAR(500),
    cv_uploaded_at TIMESTAMP NULL,
    annees_experience INT,
    diplome VARCHAR(255),
    specialite VARCHAR(255),
    disponibilite ENUM('immediate', '1_mois', '2_mois', '3_mois', 'flexible') DEFAULT 'flexible',
    salaire_souhaite_min DECIMAL(10,2),
    salaire_souhaite_max DECIMAL(10,2),
    mobilite ENUM('national', 'regional', 'local') DEFAULT 'regional',
    presentation TEXT,
    competences TEXT,
    langues TEXT,
    permis VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id),
    INDEX idx_experience (annees_experience),
    INDEX idx_disponibilite (disponibilite)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table de liaison candidats - fonctions recherchées
CREATE TABLE IF NOT EXISTS candidat_fonctions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    candidat_id INT NOT NULL,
    fonction_id INT NOT NULL,
    priorite INT DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (candidat_id) REFERENCES candidats(id) ON DELETE CASCADE,
    FOREIGN KEY (fonction_id) REFERENCES fonctions(id) ON DELETE CASCADE,
    UNIQUE KEY unique_candidat_fonction (candidat_id, fonction_id),
    INDEX idx_candidat (candidat_id),
    INDEX idx_fonction (fonction_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table de liaison candidats - départements souhaités
CREATE TABLE IF NOT EXISTS candidat_departements (
    id INT AUTO_INCREMENT PRIMARY KEY,
    candidat_id INT NOT NULL,
    departement_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (candidat_id) REFERENCES candidats(id) ON DELETE CASCADE,
    FOREIGN KEY (departement_id) REFERENCES departements(id) ON DELETE CASCADE,
    UNIQUE KEY unique_candidat_departement (candidat_id, departement_id),
    INDEX idx_candidat (candidat_id),
    INDEX idx_departement (departement_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table des offres d'emploi
CREATE TABLE IF NOT EXISTS offres (
    id INT AUTO_INCREMENT PRIMARY KEY,
    recruteur_id INT NOT NULL,
    titre VARCHAR(255) NOT NULL,
    fonction_id INT NOT NULL,
    departement_id INT NOT NULL,
    salaire_min DECIMAL(10,2),
    salaire_max DECIMAL(10,2),
    experience_min INT,
    experience_max INT,
    type_contrat ENUM('CDI', 'CDD', 'Interim', 'Vacation', 'Liberal') NOT NULL,
    description TEXT,
    missions TEXT,
    profil_recherche TEXT,
    avantages TEXT,
    adresse_etablissement TEXT,
    ville VARCHAR(100),
    code_postal VARCHAR(10),
    urgence ENUM('normale', 'importante', 'critique') DEFAULT 'normale',
    statut ENUM('active', 'pourvue', 'suspendue', 'archivee') DEFAULT 'active',
    date_debut_souhaitee DATE,
    nombre_postes INT DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    expires_at TIMESTAMP NULL,
    FOREIGN KEY (recruteur_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (fonction_id) REFERENCES fonctions(id),
    FOREIGN KEY (departement_id) REFERENCES departements(id),
    INDEX idx_recruteur (recruteur_id),
    INDEX idx_fonction (fonction_id),
    INDEX idx_departement (departement_id),
    INDEX idx_statut (statut),
    INDEX idx_urgence (urgence),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table des candidatures (lien entre offre et candidat)
CREATE TABLE IF NOT EXISTS candidatures (
    id INT AUTO_INCREMENT PRIMARY KEY,
    offre_id INT NOT NULL,
    candidat_id INT NOT NULL,
    score_matching DECIMAL(5,2),
    statut ENUM('nouvelle', 'en_cours', 'validee', 'rejetee', 'entretien', 'retenue') DEFAULT 'nouvelle',
    commentaire_recruteur TEXT,
    validated_by INT NULL,
    validated_at TIMESTAMP NULL,
    lettre_motivation TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (offre_id) REFERENCES offres(id) ON DELETE CASCADE,
    FOREIGN KEY (candidat_id) REFERENCES candidats(id) ON DELETE CASCADE,
    FOREIGN KEY (validated_by) REFERENCES users(id) ON DELETE SET NULL,
    UNIQUE KEY unique_offre_candidat (offre_id, candidat_id),
    INDEX idx_offre (offre_id),
    INDEX idx_candidat (candidat_id),
    INDEX idx_statut (statut),
    INDEX idx_score (score_matching)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table des autorisations d'accès (manager autorisant un recruteur)
CREATE TABLE IF NOT EXISTS autorisations_acces (
    id INT AUTO_INCREMENT PRIMARY KEY,
    manager_id INT NOT NULL,
    recruteur_id INT NOT NULL,
    candidat_id INT NOT NULL,
    autorisation_type ENUM('consultation', 'contact', 'complete') DEFAULT 'consultation',
    motif TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP NULL,
    FOREIGN KEY (manager_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (recruteur_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (candidat_id) REFERENCES candidats(id) ON DELETE CASCADE,
    UNIQUE KEY unique_autorisation (recruteur_id, candidat_id),
    INDEX idx_manager (manager_id),
    INDEX idx_recruteur (recruteur_id),
    INDEX idx_candidat (candidat_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table des logs d'activité
CREATE TABLE IF NOT EXISTS activity_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    action VARCHAR(100) NOT NULL,
    table_name VARCHAR(50),
    record_id INT,
    details TEXT,
    ip_address VARCHAR(45),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_user (user_id),
    INDEX idx_action (action),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insertion des départements français
INSERT INTO departements (code, nom, region) VALUES
('01', 'Ain', 'Auvergne-Rhône-Alpes'),
('02', 'Aisne', 'Hauts-de-France'),
('03', 'Allier', 'Auvergne-Rhône-Alpes'),
('04', 'Alpes-de-Haute-Provence', 'Provence-Alpes-Côte d''Azur'),
('05', 'Hautes-Alpes', 'Provence-Alpes-Côte d''Azur'),
('06', 'Alpes-Maritimes', 'Provence-Alpes-Côte d''Azur'),
('07', 'Ardèche', 'Auvergne-Rhône-Alpes'),
('08', 'Ardennes', 'Grand Est'),
('09', 'Ariège', 'Occitanie'),
('10', 'Aube', 'Grand Est'),
('11', 'Aude', 'Occitanie'),
('12', 'Aveyron', 'Occitanie'),
('13', 'Bouches-du-Rhône', 'Provence-Alpes-Côte d''Azur'),
('14', 'Calvados', 'Normandie'),
('15', 'Cantal', 'Auvergne-Rhône-Alpes'),
('16', 'Charente', 'Nouvelle-Aquitaine'),
('17', 'Charente-Maritime', 'Nouvelle-Aquitaine'),
('18', 'Cher', 'Centre-Val de Loire'),
('19', 'Corrèze', 'Nouvelle-Aquitaine'),
('21', 'Côte-d''Or', 'Bourgogne-Franche-Comté'),
('22', 'Côtes-d''Armor', 'Bretagne'),
('23', 'Creuse', 'Nouvelle-Aquitaine'),
('24', 'Dordogne', 'Nouvelle-Aquitaine'),
('25', 'Doubs', 'Bourgogne-Franche-Comté'),
('26', 'Drôme', 'Auvergne-Rhône-Alpes'),
('27', 'Eure', 'Normandie'),
('28', 'Eure-et-Loir', 'Centre-Val de Loire'),
('29', 'Finistère', 'Bretagne'),
('30', 'Gard', 'Occitanie'),
('31', 'Haute-Garonne', 'Occitanie'),
('32', 'Gers', 'Occitanie'),
('33', 'Gironde', 'Nouvelle-Aquitaine'),
('34', 'Hérault', 'Occitanie'),
('35', 'Ille-et-Vilaine', 'Bretagne'),
('36', 'Indre', 'Centre-Val de Loire'),
('37', 'Indre-et-Loire', 'Centre-Val de Loire'),
('38', 'Isère', 'Auvergne-Rhône-Alpes'),
('39', 'Jura', 'Bourgogne-Franche-Comté'),
('40', 'Landes', 'Nouvelle-Aquitaine'),
('41', 'Loir-et-Cher', 'Centre-Val de Loire'),
('42', 'Loire', 'Auvergne-Rhône-Alpes'),
('43', 'Haute-Loire', 'Auvergne-Rhône-Alpes'),
('44', 'Loire-Atlantique', 'Pays de la Loire'),
('45', 'Loiret', 'Centre-Val de Loire'),
('46', 'Lot', 'Occitanie'),
('47', 'Lot-et-Garonne', 'Nouvelle-Aquitaine'),
('48', 'Lozère', 'Occitanie'),
('49', 'Maine-et-Loire', 'Pays de la Loire'),
('50', 'Manche', 'Normandie'),
('51', 'Marne', 'Grand Est'),
('52', 'Haute-Marne', 'Grand Est'),
('53', 'Mayenne', 'Pays de la Loire'),
('54', 'Meurthe-et-Moselle', 'Grand Est'),
('55', 'Meuse', 'Grand Est'),
('56', 'Morbihan', 'Bretagne'),
('57', 'Moselle', 'Grand Est'),
('58', 'Nièvre', 'Bourgogne-Franche-Comté'),
('59', 'Nord', 'Hauts-de-France'),
('60', 'Oise', 'Hauts-de-France'),
('61', 'Orne', 'Normandie'),
('62', 'Pas-de-Calais', 'Hauts-de-France'),
('63', 'Puy-de-Dôme', 'Auvergne-Rhône-Alpes'),
('64', 'Pyrénées-Atlantiques', 'Nouvelle-Aquitaine'),
('65', 'Hautes-Pyrénées', 'Occitanie'),
('66', 'Pyrénées-Orientales', 'Occitanie'),
('67', 'Bas-Rhin', 'Grand Est'),
('68', 'Haut-Rhin', 'Grand Est'),
('69', 'Rhône', 'Auvergne-Rhône-Alpes'),
('70', 'Haute-Saône', 'Bourgogne-Franche-Comté'),
('71', 'Saône-et-Loire', 'Bourgogne-Franche-Comté'),
('72', 'Sarthe', 'Pays de la Loire'),
('73', 'Savoie', 'Auvergne-Rhône-Alpes'),
('74', 'Haute-Savoie', 'Auvergne-Rhône-Alpes'),
('75', 'Paris', 'Île-de-France'),
('76', 'Seine-Maritime', 'Normandie'),
('77', 'Seine-et-Marne', 'Île-de-France'),
('78', 'Yvelines', 'Île-de-France'),
('79', 'Deux-Sèvres', 'Nouvelle-Aquitaine'),
('80', 'Somme', 'Hauts-de-France'),
('81', 'Tarn', 'Occitanie'),
('82', 'Tarn-et-Garonne', 'Occitanie'),
('83', 'Var', 'Provence-Alpes-Côte d''Azur'),
('84', 'Vaucluse', 'Provence-Alpes-Côte d''Azur'),
('85', 'Vendée', 'Pays de la Loire'),
('86', 'Vienne', 'Nouvelle-Aquitaine'),
('87', 'Haute-Vienne', 'Nouvelle-Aquitaine'),
('88', 'Vosges', 'Grand Est'),
('89', 'Yonne', 'Bourgogne-Franche-Comté'),
('90', 'Territoire de Belfort', 'Bourgogne-Franche-Comté'),
('91', 'Essonne', 'Île-de-France'),
('92', 'Hauts-de-Seine', 'Île-de-France'),
('93', 'Seine-Saint-Denis', 'Île-de-France'),
('94', 'Val-de-Marne', 'Île-de-France'),
('95', 'Val-d''Oise', 'Île-de-France'),
('971', 'Guadeloupe', 'Guadeloupe'),
('972', 'Martinique', 'Martinique'),
('973', 'Guyane', 'Guyane'),
('974', 'La Réunion', 'La Réunion'),
('976', 'Mayotte', 'Mayotte');

-- Insertion des fonctions médicales courantes
INSERT INTO fonctions (nom, categorie, description) VALUES
('Médecin généraliste', 'Médecine', 'Médecin de premier recours'),
('Infirmier(ère) DE', 'Soins infirmiers', 'Infirmier diplômé d''État'),
('Aide-soignant(e)', 'Soins de support', 'Assistant de soins'),
('Médecin urgentiste', 'Médecine d''urgence', 'Spécialiste des urgences'),
('Infirmier(ère) urgences', 'Soins infirmiers', 'IDE spécialisé urgences'),
('Chirurgien', 'Chirurgie', 'Médecin chirurgien'),
('Anesthésiste-réanimateur', 'Anesthésie', 'Médecin anesthésiste'),
('Radiologue', 'Imagerie médicale', 'Médecin radiologue'),
('Cardiologue', 'Cardiologie', 'Spécialiste du cœur'),
('Pédiatre', 'Pédiatrie', 'Médecin pour enfants'),
('Gynécologue-obstétricien', 'Gynécologie', 'Spécialiste santé féminine'),
('Sage-femme', 'Maternité', 'Profession médicale autonome'),
('Kinésithérapeute', 'Rééducation', 'Spécialiste rééducation'),
('Ergothérapeute', 'Rééducation', 'Rééducation fonctionnelle'),
('Orthophoniste', 'Rééducation', 'Troubles du langage'),
('Psychologue', 'Santé mentale', 'Spécialiste santé psychique'),
('Psychiatre', 'Santé mentale', 'Médecin psychiatre'),
('Pharmacien', 'Pharmacie', 'Pharmacien hospitalier ou officine'),
('Préparateur en pharmacie', 'Pharmacie', 'Assistant pharmacien'),
('Manipulateur radio', 'Imagerie médicale', 'Technicien radiologie'),
('Laborantin', 'Biologie médicale', 'Technicien de laboratoire'),
('Brancardier', 'Services logistiques', 'Transport patients'),
('Aide médico-psychologique', 'Soins de support', 'Accompagnement psychologique'),
('Auxiliaire de puériculture', 'Pédiatrie', 'Soins aux enfants'),
('Diététicien(ne)', 'Nutrition', 'Spécialiste nutrition'),
('Cadre de santé', 'Management', 'Encadrement services soins'),
('Directeur des soins', 'Direction', 'Direction service infirmier'),
('Secrétaire médicale', 'Administratif', 'Gestion administrative médicale');