-- ============================================
-- BASE DE DONNÉES - IEF LOUGA CFEE 2025-2026
-- ============================================

-- Créer la base de données
-- CREATE DATABASE IF NOT EXISTS ieflouga_cfee 
-- CHARACTER SET utf8mb4 
-- COLLATE utf8mb4_unicode_ci;

-- USE ieflouga_cfee;

-- ============================================
-- TABLE: etablissements
-- ============================================
CREATE TABLE IF NOT EXISTS etablissements (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nom VARCHAR(200) UNIQUE NOT NULL,
    systeme VARCHAR(100) NOT NULL COMMENT 'Elementaire, Prescolaire, Application...',
    directeur_matricule VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_systeme (systeme),
    INDEX idx_directeur (directeur_matricule)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- TABLE: personnel
-- ============================================
CREATE TABLE IF NOT EXISTS personnel (
    id INT AUTO_INCREMENT PRIMARY KEY,
    matricule VARCHAR(20) UNIQUE NOT NULL,
    prenom VARCHAR(100) NOT NULL,
    nom VARCHAR(100) NOT NULL,
    corps VARCHAR(100),
    date_naissance DATE,
    sexe ENUM('M', 'F') NOT NULL,
    specialite VARCHAR(100),
    fonction VARCHAR(100),
    etablissement_id INT,
    systeme VARCHAR(50),
    ief VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (etablissement_id) REFERENCES etablissements(id) ON DELETE SET NULL,
    INDEX idx_matricule (matricule),
    INDEX idx_etablissement (etablissement_id),
    INDEX idx_fonction (fonction)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Ajouter la clé étrangère pour directeur après création de la table personnel
ALTER TABLE etablissements 
ADD CONSTRAINT fk_directeur 
FOREIGN KEY (directeur_matricule) REFERENCES personnel(matricule) ON DELETE SET NULL;

-- ============================================
-- TABLE: soumissions
-- ============================================
CREATE TABLE IF NOT EXISTS soumissions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    etablissement_id INT NOT NULL,
    directeur_matricule VARCHAR(20) NOT NULL,
    statut ENUM('soumis', 'valide', 'rejete') DEFAULT 'soumis',
    commentaire TEXT,
    date_soumission TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    date_validation TIMESTAMP NULL,
    valide_par VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_etablissement (etablissement_id),
    FOREIGN KEY (etablissement_id) REFERENCES etablissements(id) ON DELETE CASCADE,
    FOREIGN KEY (directeur_matricule) REFERENCES personnel(matricule),
    INDEX idx_statut (statut),
    INDEX idx_date_soumission (date_soumission)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- TABLE: personnels_selectionnes
-- ============================================
CREATE TABLE IF NOT EXISTS personnels_selectionnes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    soumission_id INT NOT NULL,
    personnel_matricule VARCHAR(20) NOT NULL,
    classes_tenues VARCHAR(100) NOT NULL,
    district_propose VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_selection (soumission_id, personnel_matricule),
    FOREIGN KEY (soumission_id) REFERENCES soumissions(id) ON DELETE CASCADE,
    FOREIGN KEY (personnel_matricule) REFERENCES personnel(matricule),
    INDEX idx_district (district_propose)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- TABLE: logs_connexion
-- ============================================
CREATE TABLE IF NOT EXISTS logs_connexion (
    id INT AUTO_INCREMENT PRIMARY KEY,
    matricule VARCHAR(20),
    success TINYINT(1) DEFAULT 0,
    ip_address VARCHAR(45),
    user_agent TEXT,
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_matricule (matricule),
    INDEX idx_timestamp (timestamp),
    INDEX idx_success (success)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- INDEX COMPOSITES pour optimisation
-- ============================================
CREATE INDEX idx_personnel_etab_fonction ON personnel(etablissement_id, fonction);
CREATE INDEX idx_soumission_etab_statut ON soumissions(etablissement_id, statut);

-- ============================================
-- DONNÉES DE TEST (Compte Admin)
-- ============================================
-- Créer un établissement administratif
INSERT INTO etablissements (nom, systeme) 
VALUES ('Bureau des Examens et Concours', 'Elementaire')
ON DUPLICATE KEY UPDATE nom=nom;

-- Créer un compte admin
INSERT INTO personnel (matricule, prenom, nom, sexe, fonction, systeme, ief, date_naissance)
VALUES ('ADMIN', 'Chef', 'BUREAU', 'M', 'Administrateur', 'Structures Administratives MEN', 'IEF Louga', '1980-01-01')
ON DUPLICATE KEY UPDATE matricule=matricule;

-- ============================================
-- AFFICHER LES TABLES CRÉÉES
-- ============================================
SHOW TABLES;

SELECT 'Base de données créée avec succès !' AS Message;
