137 lines
6.9 KiB
SQL
137 lines
6.9 KiB
SQL
-- ============================================
|
|
-- WebVal - Gestion API Chatbot
|
|
-- Tables pour la gestion des clés API et modèles
|
|
-- Date: 2026-01-04
|
|
-- ============================================
|
|
|
|
-- Table des clés API des enseignants
|
|
CREATE TABLE IF NOT EXISTS api_keys (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
id_enseignant INT NOT NULL,
|
|
provider ENUM('gemini', 'mistral', 'openai', 'anthropic', 'ollama') NOT NULL,
|
|
api_key_encrypted TEXT, -- Clé chiffrée avec AES-256-CBC
|
|
model_default VARCHAR(100), -- Modèle par défaut (ex: 'gemini-2.0-flash-exp')
|
|
is_active BOOLEAN DEFAULT TRUE,
|
|
quota_max INT DEFAULT NULL, -- Limite mensuelle optionnelle
|
|
quota_used INT DEFAULT 0,
|
|
quota_reset_date DATE, -- Date de reset du quota
|
|
last_used TIMESTAMP NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (id_enseignant) REFERENCES utilisateurs(id_utilisateur) ON DELETE CASCADE,
|
|
UNIQUE KEY unique_provider (id_enseignant, provider),
|
|
INDEX idx_enseignant (id_enseignant),
|
|
INDEX idx_active (is_active)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- Table des modèles disponibles (catalogue)
|
|
CREATE TABLE IF NOT EXISTS available_models (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
provider VARCHAR(50) NOT NULL,
|
|
model_id VARCHAR(100) NOT NULL, -- ID technique du modèle
|
|
display_name VARCHAR(150) NOT NULL, -- Nom affiché
|
|
description TEXT,
|
|
context_length INT DEFAULT 4096, -- Taille contexte en tokens
|
|
is_free BOOLEAN DEFAULT FALSE,
|
|
cost_per_1m_tokens DECIMAL(10,4) DEFAULT NULL, -- Coût par million de tokens
|
|
recommended BOOLEAN DEFAULT FALSE,
|
|
speed_rating TINYINT DEFAULT 3, -- 1=lent, 5=très rapide
|
|
quality_rating TINYINT DEFAULT 3, -- 1=basique, 5=excellent
|
|
active BOOLEAN DEFAULT TRUE,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE KEY unique_model (provider, model_id),
|
|
INDEX idx_provider (provider),
|
|
INDEX idx_recommended (recommended),
|
|
INDEX idx_active (active)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- Table de configuration chatbot par évaluation
|
|
CREATE TABLE IF NOT EXISTS chatbot_eval_config (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
id_evaluation INT NOT NULL,
|
|
id_enseignant INT NOT NULL,
|
|
provider_default VARCHAR(50) DEFAULT 'ollama',
|
|
model_default VARCHAR(100) DEFAULT 'ministral-3:3b',
|
|
allow_student_override BOOLEAN DEFAULT FALSE, -- L'élève peut-il choisir ?
|
|
max_messages_per_question INT DEFAULT 3,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (id_evaluation) REFERENCES evaluations(id_evaluation) ON DELETE CASCADE,
|
|
FOREIGN KEY (id_enseignant) REFERENCES utilisateurs(id_utilisateur) ON DELETE CASCADE,
|
|
UNIQUE KEY unique_eval (id_evaluation),
|
|
INDEX idx_enseignant (id_enseignant)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- Table de configuration par élève (override)
|
|
CREATE TABLE IF NOT EXISTS chatbot_student_config (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
id_evaluation INT NOT NULL,
|
|
id_eleve INT NOT NULL,
|
|
provider_override VARCHAR(50),
|
|
model_override VARCHAR(100),
|
|
created_by INT NOT NULL, -- ID enseignant qui a fait l'override
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (id_evaluation) REFERENCES evaluations(id_evaluation) ON DELETE CASCADE,
|
|
FOREIGN KEY (id_eleve) REFERENCES utilisateurs(id_utilisateur) ON DELETE CASCADE,
|
|
FOREIGN KEY (created_by) REFERENCES utilisateurs(id_utilisateur) ON DELETE CASCADE,
|
|
UNIQUE KEY unique_student_eval (id_evaluation, id_eleve),
|
|
INDEX idx_evaluation (id_evaluation),
|
|
INDEX idx_eleve (id_eleve)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- Pré-remplir les modèles disponibles
|
|
INSERT INTO available_models
|
|
(provider, model_id, display_name, description, context_length, is_free, cost_per_1m_tokens, recommended, speed_rating, quality_rating)
|
|
VALUES
|
|
-- Gemini (Google)
|
|
('gemini', 'gemini-2.0-flash-exp', 'Gemini 2.0 Flash (Expérimental)', 'Modèle ultra-rapide de Google, gratuit avec quotas généreux', 1000000, TRUE, 0, TRUE, 5, 4),
|
|
('gemini', 'gemini-1.5-flash', 'Gemini 1.5 Flash', 'Modèle rapide et équilibré', 1000000, TRUE, 0, FALSE, 4, 4),
|
|
('gemini', 'gemini-1.5-pro', 'Gemini 1.5 Pro', 'Modèle le plus puissant de Google', 2000000, FALSE, 1.25, FALSE, 3, 5),
|
|
|
|
-- Mistral
|
|
('mistral', 'mistral-small-latest', 'Mistral Small', 'Modèle compact et rapide', 32000, FALSE, 0.20, FALSE, 5, 3),
|
|
('mistral', 'ministral-3b-latest', 'Ministral 3B', 'Petit modèle efficace pour le raisonnement', 128000, FALSE, 0.04, TRUE, 5, 3),
|
|
('mistral', 'ministral-8b-latest', 'Ministral 8B', 'Meilleur équilibre qualité/vitesse', 128000, FALSE, 0.10, TRUE, 4, 4),
|
|
('mistral', 'mistral-large-latest', 'Mistral Large', 'Modèle le plus puissant de Mistral', 128000, FALSE, 2.00, FALSE, 3, 5),
|
|
|
|
-- OpenAI
|
|
('openai', 'gpt-4o-mini', 'GPT-4o Mini', 'Petit modèle rapide et économique', 128000, FALSE, 0.15, TRUE, 5, 4),
|
|
('openai', 'gpt-4o', 'GPT-4o', 'Modèle multimodal puissant', 128000, FALSE, 2.50, FALSE, 4, 5),
|
|
('openai', 'gpt-4-turbo', 'GPT-4 Turbo', 'Modèle GPT-4 optimisé', 128000, FALSE, 10.00, FALSE, 3, 5),
|
|
|
|
-- Ollama (local)
|
|
('ollama', 'ministral-3:3b', 'Ministral 3B (Local)', 'Modèle local rapide', 128000, TRUE, 0, TRUE, 4, 3),
|
|
('ollama', 'qwen2-math:1.5b', 'Qwen2 Math 1.5B (Local)', 'Spécialisé en mathématiques', 32000, TRUE, 0, FALSE, 5, 3),
|
|
('ollama', 'llama3.2:3b', 'Llama 3.2 3B (Local)', 'Modèle généraliste compact', 128000, TRUE, 0, FALSE, 3, 4);
|
|
|
|
-- Ajouter colonne provider à la table chat_conversations pour tracking
|
|
ALTER TABLE chat_conversations
|
|
ADD COLUMN IF NOT EXISTS provider VARCHAR(50) DEFAULT 'ollama',
|
|
ADD COLUMN IF NOT EXISTS model_used VARCHAR(100) DEFAULT 'ministral-3:3b',
|
|
ADD COLUMN IF NOT EXISTS api_response_time_ms INT DEFAULT NULL,
|
|
ADD INDEX idx_provider (provider);
|
|
|
|
-- Ajouter colonne provider à la table chat_messages pour analytics
|
|
ALTER TABLE chat_messages
|
|
ADD COLUMN IF NOT EXISTS api_cost DECIMAL(10,6) DEFAULT 0.000000;
|
|
|
|
-- Vue pour les stats d'utilisation enseignant
|
|
CREATE OR REPLACE VIEW v_chatbot_usage_stats AS
|
|
SELECT
|
|
cc.id_enseignant,
|
|
cc.provider,
|
|
cc.model_used,
|
|
COUNT(DISTINCT cc.id_conversation) as total_conversations,
|
|
COUNT(cm.id) as total_messages,
|
|
AVG(cc.api_response_time_ms) as avg_response_time_ms,
|
|
SUM(cm.api_cost) as total_cost,
|
|
DATE_FORMAT(cc.date_debut, '%Y-%m') as month
|
|
FROM chat_conversations cc
|
|
LEFT JOIN chat_messages cm ON cc.id_conversation = cm.id_conversation
|
|
GROUP BY cc.id_enseignant, cc.provider, cc.model_used, month;
|
|
|
|
-- ============================================
|
|
-- FIN DU SCRIPT
|
|
-- ============================================
|