-- ============================================================================
-- SCRIPT SQL : Tables Anti-Triche - VERSION CORRIGÉE DÉFINITIVE
-- ============================================================================
-- Date : 29 décembre 2025
-- Base : mathematiques_db
-- Correction : Utilisation des bons noms de colonnes (id_tentative, id_question)
-- ============================================================================

USE mathematiques_db;

-- ============================================================================
-- TABLE 1 : sessions_calc
-- ============================================================================

DROP TABLE IF EXISTS sessions_calc;
CREATE TABLE sessions_calc (
    id INT AUTO_INCREMENT PRIMARY KEY,
    id_tentative INT(11) NOT NULL,
    id_eleve INT(11) NOT NULL,
    session_token VARCHAR(64) UNIQUE NOT NULL,
    
    computer_name VARCHAR(100),
    computer_username VARCHAR(100),
    ip_address VARCHAR(45),
    user_agent TEXT,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_activity TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    disconnected_at TIMESTAMP NULL,
    
    is_active BOOLEAN DEFAULT TRUE,
    
    macro_hash VARCHAR(64) DEFAULT NULL,
    file_hash VARCHAR(64) DEFAULT NULL,
    
    FOREIGN KEY (id_tentative) REFERENCES tentatives_eleves(id_tentative) ON DELETE CASCADE,
    FOREIGN KEY (id_eleve) REFERENCES utilisateurs(id_utilisateur) ON DELETE CASCADE,
    
    INDEX idx_session_token (session_token),
    INDEX idx_active (is_active, last_activity),
    INDEX idx_tentative (id_tentative),
    INDEX idx_eleve (id_eleve)
    
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ============================================================================
-- TABLE 2 : reponses_calc
-- ============================================================================

DROP TABLE IF EXISTS reponses_calc;
CREATE TABLE reponses_calc (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    
    id_tentative INT(11) NOT NULL,
    id_question INT(11) NOT NULL,
    session_token VARCHAR(64) NOT NULL,
    
    reponse_choisie VARCHAR(10) NOT NULL,
    
    saved_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    computer_name VARCHAR(100),
    
    client_timestamp BIGINT DEFAULT NULL,
    time_spent_seconds INT DEFAULT NULL,
    
    FOREIGN KEY (id_tentative) REFERENCES tentatives_eleves(id_tentative) ON DELETE CASCADE,
    FOREIGN KEY (id_question) REFERENCES questions(id_question) ON DELETE CASCADE,
    FOREIGN KEY (session_token) REFERENCES sessions_calc(session_token) ON DELETE CASCADE,
    
    INDEX idx_tentative (id_tentative),
    INDEX idx_question (id_question),
    INDEX idx_session (session_token),
    INDEX idx_saved_at (saved_at)
    
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ============================================================================
-- TABLE 3 : logs_actions
-- ============================================================================

DROP TABLE IF EXISTS logs_actions;
CREATE TABLE logs_actions (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    
    id_tentative INT(11) NOT NULL,
    session_token VARCHAR(64) NOT NULL,
    
    action_type ENUM(
        'connexion',
        'deconnexion',
        'reponse_modifiee',
        'copier_coller',
        'changement_fenetre',
        'inactivite',
        'reconnexion',
        'soumission',
        'autre'
    ) NOT NULL,
    
    action_details TEXT,
    
    computer_name VARCHAR(100),
    ip_address VARCHAR(45),
    
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (id_tentative) REFERENCES tentatives_eleves(id_tentative) ON DELETE CASCADE,
    
    INDEX idx_tentative (id_tentative),
    INDEX idx_action_type (action_type),
    INDEX idx_timestamp (timestamp)
    
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ============================================================================
-- TABLE 4 : fraud_alerts
-- ============================================================================

DROP TABLE IF EXISTS fraud_alerts;
CREATE TABLE fraud_alerts (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    
    id_tentative INT(11) NOT NULL,
    
    alert_type ENUM(
        'multi_connexion',
        'ip_changee',
        'machine_changee',
        'timing_suspect',
        'reponses_identiques',
        'macro_modifiee',
        'fichier_modifie',
        'autre'
    ) NOT NULL,
    
    severity ENUM('low', 'medium', 'high', 'critical') NOT NULL DEFAULT 'medium',
    
    alert_message TEXT NOT NULL,
    alert_details JSON,
    
    detected_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    is_reviewed BOOLEAN DEFAULT FALSE,
    reviewed_at TIMESTAMP NULL,
    reviewed_by INT(11) NULL,
    review_notes TEXT,
    
    FOREIGN KEY (id_tentative) REFERENCES tentatives_eleves(id_tentative) ON DELETE CASCADE,
    FOREIGN KEY (reviewed_by) REFERENCES utilisateurs(id_utilisateur) ON DELETE SET NULL,
    
    INDEX idx_tentative (id_tentative),
    INDEX idx_alert_type (alert_type),
    INDEX idx_severity (severity),
    INDEX idx_detected_at (detected_at),
    INDEX idx_is_reviewed (is_reviewed),
    INDEX idx_reviewed_by (reviewed_by)
    
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ============================================================================
-- VÉRIFICATION
-- ============================================================================

SELECT '✅ Tables créées avec succès !' AS status;

SHOW TABLES LIKE '%calc%';

SELECT 
    TABLE_NAME, 
    ENGINE, 
    TABLE_ROWS, 
    TABLE_COLLATION 
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'mathematiques_db' 
AND TABLE_NAME IN ('sessions_calc', 'reponses_calc', 'logs_actions', 'fraud_alerts');


-- ============================================================================
-- FIN DU SCRIPT
-- ============================================================================