-- =============================================
-- mySupport - Base de données COMPLETE
-- Plateforme de Service Client Intelligent
-- Version: 2.0 - Déploiement Production
-- Date: 2026-02-24
-- =============================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- =============================================
-- CRÉATION DE LA BASE DE DONNÉES
-- =============================================
CREATE DATABASE IF NOT EXISTS mysupport_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE mysupport_db;

-- =============================================
-- TABLE: entreprises
-- Gestion des entreprises clientes (tenants)
-- =============================================
DROP TABLE IF EXISTS entreprises;
CREATE TABLE entreprises (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nom VARCHAR(255) NOT NULL,
    slug VARCHAR(255) UNIQUE NOT NULL,
    secteur VARCHAR(100),
    pays VARCHAR(100) DEFAULT 'Mali',
    region VARCHAR(100),
    email VARCHAR(255),
    telephone VARCHAR(50),
    adresse TEXT,
    logo VARCHAR(255),
    langues_actives TEXT,
    canaux_actifs TEXT,
    twilio_phone_number VARCHAR(20) NULL,
    voice_welcome TEXT NULL,
    voice_config TEXT NULL,
    type_abonnement ENUM('basic', 'pro', 'enterprise') DEFAULT 'basic',
    max_agents INT DEFAULT 5,
    widget_token VARCHAR(64) UNIQUE,
    widget_config TEXT,
    ia_config TEXT,
    api_key VARCHAR(64) UNIQUE NULL,
    ticket_counter INT DEFAULT 0,
    email_notifications BOOLEAN DEFAULT TRUE,
    csat_enabled BOOLEAN DEFAULT TRUE,
    csat_delay_hours INT DEFAULT 1,
    denonciations_enabled BOOLEAN DEFAULT TRUE,
    denonciations_config TEXT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- =============================================
-- TABLE: users
-- Utilisateurs de la plateforme (tous rôles)
-- =============================================
DROP TABLE IF EXISTS users;
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    entreprise_id INT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    nom VARCHAR(100) NOT NULL,
    prenom VARCHAR(100),
    telephone VARCHAR(50),
    avatar VARCHAR(255),
    role ENUM('super_admin', 'admin_entreprise', 'agent') NOT NULL,
    langue_preferee VARCHAR(10) DEFAULT 'fr',
    push_enabled BOOLEAN DEFAULT TRUE,
    notification_sound BOOLEAN DEFAULT TRUE,
    notification_types JSON NULL,
    is_active BOOLEAN DEFAULT TRUE,
    is_online BOOLEAN DEFAULT FALSE,
    last_login DATETIME,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (entreprise_id) REFERENCES entreprises(id) ON DELETE CASCADE,
    INDEX idx_entreprise (entreprise_id),
    INDEX idx_role (role)
) ENGINE=InnoDB;

-- =============================================
-- TABLE: clients
-- Clients finaux / Citoyens
-- =============================================
DROP TABLE IF EXISTS clients;
CREATE TABLE clients (
    id INT PRIMARY KEY AUTO_INCREMENT,
    entreprise_id INT NOT NULL,
    identifiant_externe VARCHAR(255),
    nom VARCHAR(255),
    telephone VARCHAR(50),
    email VARCHAR(255),
    whatsapp_id VARCHAR(100),
    langue_preferee VARCHAR(10) DEFAULT 'fr',
    metadata TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (entreprise_id) REFERENCES entreprises(id) ON DELETE CASCADE,
    INDEX idx_entreprise (entreprise_id),
    INDEX idx_whatsapp (whatsapp_id),
    INDEX idx_email (email)
) ENGINE=InnoDB;

-- =============================================
-- TABLE: conversations
-- Conversations / Tickets
-- =============================================
DROP TABLE IF EXISTS conversations;
CREATE TABLE conversations (
    id INT PRIMARY KEY AUTO_INCREMENT,
    entreprise_id INT NOT NULL,
    client_id INT NOT NULL,
    agent_id INT NULL,
    canal ENUM('web', 'whatsapp', 'voice') DEFAULT 'web',
    sujet VARCHAR(255),
    categorie VARCHAR(100),
    statut ENUM('open', 'in_progress', 'resolved', 'closed') DEFAULT 'open',
    priorite ENUM('low', 'medium', 'high', 'urgent') DEFAULT 'medium',
    source_langue VARCHAR(10),
    tags JSON NULL,
    is_escalated BOOLEAN DEFAULT FALSE,
    escalation_reason TEXT,
    escalation_type ENUM('none', 'ticket', 'live_chat') DEFAULT 'none',
    ticket_id INT NULL,
    live_request_id INT NULL,
    escalated_at DATETIME NULL,
    satisfaction_score INT,
    satisfaction_comment TEXT,
    csat_sent BOOLEAN DEFAULT FALSE,
    csat_score INT NULL,
    csat_responded_at DATETIME NULL,
    resolution_time INT,
    first_response_time INT,
    metadata TEXT,
    closed_at DATETIME,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (entreprise_id) REFERENCES entreprises(id) ON DELETE CASCADE,
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
    FOREIGN KEY (agent_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_entreprise_statut (entreprise_id, statut),
    INDEX idx_agent (agent_id),
    INDEX idx_created (created_at)
) ENGINE=InnoDB;

-- =============================================
-- TABLE: messages
-- Messages des conversations
-- =============================================
DROP TABLE IF EXISTS messages;
CREATE TABLE messages (
    id INT PRIMARY KEY AUTO_INCREMENT,
    conversation_id INT NOT NULL,
    sender_type ENUM('client', 'agent', 'bot') NOT NULL,
    sender_id INT,
    contenu TEXT NOT NULL,
    contenu_original TEXT,
    langue VARCHAR(10),
    type_message ENUM('text', 'audio', 'image', 'file') DEFAULT 'text',
    fichier_url VARCHAR(500),
    is_read BOOLEAN DEFAULT FALSE,
    metadata TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (conversation_id) REFERENCES conversations(id) ON DELETE CASCADE,
    INDEX idx_conversation (conversation_id),
    INDEX idx_created (created_at)
) ENGINE=InnoDB;

-- =============================================
-- TABLE: faq
-- Questions fréquentes par entreprise
-- =============================================
DROP TABLE IF EXISTS faq;
CREATE TABLE faq (
    id INT PRIMARY KEY AUTO_INCREMENT,
    entreprise_id INT NOT NULL,
    question TEXT NOT NULL,
    reponse TEXT NOT NULL,
    categorie VARCHAR(100),
    langue VARCHAR(10) DEFAULT 'fr',
    keywords TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    views_count INT DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (entreprise_id) REFERENCES entreprises(id) ON DELETE CASCADE,
    INDEX idx_entreprise (entreprise_id),
    FULLTEXT INDEX idx_search (question, reponse)
) ENGINE=InnoDB;

-- =============================================
-- TABLE: support_tickets
-- Tickets de support escaladés
-- =============================================
DROP TABLE IF EXISTS support_tickets;
CREATE TABLE support_tickets (
    id INT PRIMARY KEY AUTO_INCREMENT,
    entreprise_id INT NOT NULL,
    conversation_id INT NULL,
    client_id INT NOT NULL,
    ticket_number VARCHAR(20) UNIQUE NOT NULL,
    sujet VARCHAR(255) NOT NULL,
    description TEXT,
    categorie VARCHAR(100),
    priorite ENUM('low', 'medium', 'high', 'urgent') DEFAULT 'medium',
    statut ENUM('open', 'assigned', 'in_progress', 'pending_client', 'resolved', 'closed') DEFAULT 'open',
    assigned_agent_id INT NULL,
    source ENUM('chat_escalation', 'manual', 'whatsapp', 'voice') DEFAULT 'chat_escalation',
    resolution_notes TEXT,
    resolved_at DATETIME,
    closed_at DATETIME,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (entreprise_id) REFERENCES entreprises(id) ON DELETE CASCADE,
    FOREIGN KEY (conversation_id) REFERENCES conversations(id) ON DELETE SET NULL,
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
    FOREIGN KEY (assigned_agent_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_entreprise_statut (entreprise_id, statut),
    INDEX idx_ticket_number (ticket_number),
    INDEX idx_assigned_agent (assigned_agent_id)
) ENGINE=InnoDB;

-- =============================================
-- TABLE: live_assistance_requests
-- Demandes de chat en temps réel
-- =============================================
DROP TABLE IF EXISTS live_assistance_requests;
CREATE TABLE live_assistance_requests (
    id INT PRIMARY KEY AUTO_INCREMENT,
    entreprise_id INT NOT NULL,
    conversation_id INT NOT NULL,
    client_id INT NOT NULL,
    statut ENUM('waiting', 'assigned', 'active', 'completed', 'abandoned') DEFAULT 'waiting',
    assigned_agent_id INT NULL,
    reason TEXT,
    priority_score INT DEFAULT 50,
    wait_started_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    assigned_at DATETIME,
    completed_at DATETIME,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (entreprise_id) REFERENCES entreprises(id) ON DELETE CASCADE,
    FOREIGN KEY (conversation_id) REFERENCES conversations(id) ON DELETE CASCADE,
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
    FOREIGN KEY (assigned_agent_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_entreprise_statut (entreprise_id, statut),
    INDEX idx_waiting (statut, wait_started_at)
) ENGINE=InnoDB;

-- =============================================
-- TABLE: agent_notifications
-- Notifications pour les agents
-- =============================================
DROP TABLE IF EXISTS agent_notifications;
CREATE TABLE agent_notifications (
    id INT PRIMARY KEY AUTO_INCREMENT,
    entreprise_id INT NOT NULL,
    agent_id INT NULL,
    type ENUM('new_ticket', 'live_request', 'escalation', 'mention', 'assignment', 'urgent') NOT NULL,
    titre VARCHAR(255) NOT NULL,
    message TEXT,
    reference_type ENUM('ticket', 'conversation', 'live_request') NULL,
    reference_id INT NULL,
    is_read BOOLEAN DEFAULT FALSE,
    is_urgent BOOLEAN DEFAULT FALSE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (entreprise_id) REFERENCES entreprises(id) ON DELETE CASCADE,
    FOREIGN KEY (agent_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_agent_unread (agent_id, is_read),
    INDEX idx_entreprise_unread (entreprise_id, is_read)
) ENGINE=InnoDB;

-- =============================================
-- TABLE: email_logs
-- Logs des emails envoyés
-- =============================================
DROP TABLE IF EXISTS email_logs;
CREATE TABLE email_logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    entreprise_id INT NOT NULL,
    recipient VARCHAR(255) NOT NULL,
    type ENUM('ticket_created', 'ticket_resolved', 'escalation_alert', 'csat_survey', 'reminder', 'notification') NOT NULL,
    reference_id INT NULL,
    status ENUM('sent', 'failed', 'pending') DEFAULT 'pending',
    error_message TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (entreprise_id) REFERENCES entreprises(id) ON DELETE CASCADE,
    INDEX idx_entreprise (entreprise_id),
    INDEX idx_type (type),
    INDEX idx_created (created_at)
) ENGINE=InnoDB;

-- =============================================
-- TABLE: csat_tokens
-- Tokens pour liens de feedback CSAT
-- =============================================
DROP TABLE IF EXISTS csat_tokens;
CREATE TABLE csat_tokens (
    id INT PRIMARY KEY AUTO_INCREMENT,
    token VARCHAR(64) UNIQUE NOT NULL,
    conversation_id INT NOT NULL,
    is_used BOOLEAN DEFAULT FALSE,
    expires_at DATETIME NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (conversation_id) REFERENCES conversations(id) ON DELETE CASCADE,
    INDEX idx_token (token),
    INDEX idx_conversation (conversation_id)
) ENGINE=InnoDB;

-- =============================================
-- TABLE: csat_responses
-- Réponses aux enquêtes de satisfaction
-- =============================================
DROP TABLE IF EXISTS csat_responses;
CREATE TABLE csat_responses (
    id INT PRIMARY KEY AUTO_INCREMENT,
    entreprise_id INT NOT NULL,
    conversation_id INT NOT NULL,
    client_id INT NULL,
    token_id INT NULL,
    score INT NOT NULL CHECK (score >= 1 AND score <= 5),
    comment TEXT,
    categories TEXT,
    agent_id INT NULL,
    source ENUM('widget', 'email', 'sms', 'whatsapp') DEFAULT 'widget',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (entreprise_id) REFERENCES entreprises(id) ON DELETE CASCADE,
    FOREIGN KEY (conversation_id) REFERENCES conversations(id) ON DELETE CASCADE,
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE SET NULL,
    FOREIGN KEY (agent_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_entreprise (entreprise_id),
    INDEX idx_conversation (conversation_id),
    INDEX idx_score (score),
    INDEX idx_created (created_at)
) ENGINE=InnoDB;

-- =============================================
-- TABLE: csat_daily_stats
-- Statistiques CSAT agrégées par jour
-- =============================================
DROP TABLE IF EXISTS csat_daily_stats;
CREATE TABLE csat_daily_stats (
    id INT PRIMARY KEY AUTO_INCREMENT,
    entreprise_id INT NOT NULL,
    date DATE NOT NULL,
    total_responses INT DEFAULT 0,
    score_1 INT DEFAULT 0,
    score_2 INT DEFAULT 0,
    score_3 INT DEFAULT 0,
    score_4 INT DEFAULT 0,
    score_5 INT DEFAULT 0,
    average_score DECIMAL(3,2),
    nps_promoters INT DEFAULT 0,
    nps_passives INT DEFAULT 0,
    nps_detractors INT DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (entreprise_id) REFERENCES entreprises(id) ON DELETE CASCADE,
    UNIQUE INDEX idx_entreprise_date (entreprise_id, date)
) ENGINE=InnoDB;

-- =============================================
-- TABLE: conversation_tags
-- Tags des conversations (auto-tagging)
-- =============================================
DROP TABLE IF EXISTS conversation_tags;
CREATE TABLE conversation_tags (
    id INT PRIMARY KEY AUTO_INCREMENT,
    conversation_id INT NOT NULL,
    tag VARCHAR(50) NOT NULL,
    is_auto BOOLEAN DEFAULT TRUE,
    added_by INT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (conversation_id) REFERENCES conversations(id) ON DELETE CASCADE,
    FOREIGN KEY (added_by) REFERENCES users(id) ON DELETE SET NULL,
    UNIQUE INDEX idx_conversation_tag (conversation_id, tag),
    INDEX idx_tag (tag)
) ENGINE=InnoDB;

-- =============================================
-- TABLE: tag_definitions
-- Référentiel des tags prédéfinis
-- =============================================
DROP TABLE IF EXISTS tag_definitions;
CREATE TABLE tag_definitions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    entreprise_id INT NULL,
    tag VARCHAR(50) NOT NULL,
    category ENUM('type', 'sujet', 'sentiment', 'priorite', 'custom') DEFAULT 'custom',
    color VARCHAR(20) DEFAULT '#6366f1',
    description VARCHAR(255),
    is_active BOOLEAN DEFAULT TRUE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (entreprise_id) REFERENCES entreprises(id) ON DELETE CASCADE,
    INDEX idx_entreprise (entreprise_id),
    INDEX idx_category (category)
) ENGINE=InnoDB;

-- =============================================
-- TABLE: push_subscriptions
-- Souscriptions push (PWA)
-- =============================================
DROP TABLE IF EXISTS push_subscriptions;
CREATE TABLE push_subscriptions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    entreprise_id INT NOT NULL,
    endpoint TEXT NOT NULL,
    p256dh VARCHAR(255) NOT NULL,
    auth VARCHAR(255) NOT NULL,
    user_agent VARCHAR(500),
    is_active BOOLEAN DEFAULT TRUE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (entreprise_id) REFERENCES entreprises(id) ON DELETE CASCADE,
    INDEX idx_user (user_id),
    INDEX idx_entreprise (entreprise_id)
) ENGINE=InnoDB;

-- =============================================
-- TABLE: denonciations
-- Système de dénonciations
-- =============================================
DROP TABLE IF EXISTS denonciations;
CREATE TABLE denonciations (
    id INT PRIMARY KEY AUTO_INCREMENT,
    entreprise_id INT NOT NULL,
    client_id INT NULL,
    numero_dossier VARCHAR(50) UNIQUE NULL,
    type ENUM('texte', 'audio', 'photo', 'video') NOT NULL DEFAULT 'texte',
    titre VARCHAR(255),
    description TEXT,
    fichier_path VARCHAR(500),
    fichier_type VARCHAR(100),
    fichier_taille INT,
    categorie VARCHAR(100),
    sous_categorie VARCHAR(100),
    localisation VARCHAR(255),
    date_faits DATETIME NULL,
    anonyme BOOLEAN DEFAULT FALSE,
    statut ENUM('nouveau', 'en_cours', 'traite', 'rejete', 'archive') DEFAULT 'nouveau',
    priorite ENUM('basse', 'normale', 'haute', 'urgente') DEFAULT 'normale',
    agent_id INT NULL,
    notes_internes TEXT,
    note_interne TEXT,
    resolution TEXT,
    date_traitement DATETIME NULL,
    date_cloture DATETIME NULL,
    canal ENUM('widget', 'whatsapp', 'mobile', 'web') DEFAULT 'widget',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (entreprise_id) REFERENCES entreprises(id) ON DELETE CASCADE,
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE SET NULL,
    FOREIGN KEY (agent_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_entreprise (entreprise_id),
    INDEX idx_statut (statut),
    INDEX idx_type (type),
    INDEX idx_priorite (priorite),
    INDEX idx_numero_dossier (numero_dossier),
    INDEX idx_created (created_at)
) ENGINE=InnoDB;

-- =============================================
-- TABLE: denonciation_historique
-- Historique des actions sur les dénonciations
-- =============================================
DROP TABLE IF EXISTS denonciation_historique;
CREATE TABLE denonciation_historique (
    id INT PRIMARY KEY AUTO_INCREMENT,
    denonciation_id INT NOT NULL,
    user_id INT NULL,
    action VARCHAR(50) NOT NULL,
    ancien_statut VARCHAR(50) NULL,
    nouveau_statut VARCHAR(50) NULL,
    commentaire TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (denonciation_id) REFERENCES denonciations(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_denonciation (denonciation_id)
) ENGINE=InnoDB;

-- =============================================
-- TABLE: denonciation_pieces
-- Pièces jointes des dénonciations
-- =============================================
DROP TABLE IF EXISTS denonciation_pieces;
CREATE TABLE denonciation_pieces (
    id INT PRIMARY KEY AUTO_INCREMENT,
    denonciation_id INT NOT NULL,
    user_id INT NULL,
    type ENUM('document', 'photo', 'audio', 'video', 'note') DEFAULT 'document',
    nom_fichier VARCHAR(255) NOT NULL,
    chemin_fichier VARCHAR(500) NOT NULL,
    description TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (denonciation_id) REFERENCES denonciations(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_denonciation (denonciation_id)
) ENGINE=InnoDB;

-- =============================================
-- TABLE: denonciation_assignations
-- Assignations des dénonciations
-- =============================================
DROP TABLE IF EXISTS denonciation_assignations;
CREATE TABLE denonciation_assignations (
    id INT PRIMARY KEY AUTO_INCREMENT,
    denonciation_id INT NOT NULL,
    user_id INT NOT NULL,
    assigne_par INT NULL,
    date_assignation DATETIME DEFAULT CURRENT_TIMESTAMP,
    date_fin DATETIME NULL,
    est_actif BOOLEAN DEFAULT TRUE,
    FOREIGN KEY (denonciation_id) REFERENCES denonciations(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (assigne_par) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_denonciation (denonciation_id)
) ENGINE=InnoDB;

-- =============================================
-- TABLE: voice_calls
-- Historique des appels vocaux Twilio
-- =============================================
DROP TABLE IF EXISTS voice_calls;
CREATE TABLE voice_calls (
    id INT PRIMARY KEY AUTO_INCREMENT,
    entreprise_id INT NOT NULL,
    conversation_id INT NULL,
    client_id INT NULL,
    call_sid VARCHAR(64) UNIQUE NOT NULL,
    direction ENUM('inbound', 'outbound') DEFAULT 'inbound',
    caller_number VARCHAR(20),
    called_number VARCHAR(20),
    status VARCHAR(50),
    duration INT DEFAULT 0,
    recording_url VARCHAR(500),
    transcription TEXT,
    metadata TEXT,
    started_at DATETIME,
    ended_at DATETIME,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (entreprise_id) REFERENCES entreprises(id) ON DELETE CASCADE,
    FOREIGN KEY (conversation_id) REFERENCES conversations(id) ON DELETE SET NULL,
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE SET NULL,
    INDEX idx_entreprise (entreprise_id),
    INDEX idx_conversation (conversation_id),
    INDEX idx_call_sid (call_sid),
    INDEX idx_created (created_at)
) ENGINE=InnoDB;

-- =============================================
-- TABLE: voice_call_context
-- Contexte des appels en cours
-- =============================================
DROP TABLE IF EXISTS voice_call_context;
CREATE TABLE voice_call_context (
    id INT PRIMARY KEY AUTO_INCREMENT,
    call_sid VARCHAR(64) UNIQUE NOT NULL,
    context_data TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_call_sid (call_sid)
) ENGINE=InnoDB;

-- =============================================
-- TABLE: ia_documents
-- Documents base de connaissances IA
-- =============================================
DROP TABLE IF EXISTS ia_documents;
CREATE TABLE ia_documents (
    id INT PRIMARY KEY AUTO_INCREMENT,
    entreprise_id INT NOT NULL,
    titre VARCHAR(255) NOT NULL,
    description TEXT,
    type_fichier VARCHAR(50),
    fichier_path VARCHAR(500),
    contenu_extrait LONGTEXT,
    taille_fichier INT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (entreprise_id) REFERENCES entreprises(id) ON DELETE CASCADE,
    INDEX idx_entreprise (entreprise_id),
    FULLTEXT INDEX idx_contenu (titre, contenu_extrait)
) ENGINE=InnoDB;

-- =============================================
-- TABLE: ai_usage_logs
-- Logs d'utilisation de l'IA
-- =============================================
DROP TABLE IF EXISTS ai_usage_logs;
CREATE TABLE ai_usage_logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    entreprise_id INT NOT NULL,
    tokens_used INT DEFAULT 0,
    model VARCHAR(50) DEFAULT 'gpt-3.5-turbo',
    request_type ENUM('chat', 'sentiment', 'summary', 'tagging') DEFAULT 'chat',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (entreprise_id) REFERENCES entreprises(id) ON DELETE CASCADE,
    INDEX idx_entreprise (entreprise_id),
    INDEX idx_created (created_at)
) ENGINE=InnoDB;

-- =============================================
-- TABLE: alertes
-- Alertes intelligentes
-- =============================================
DROP TABLE IF EXISTS alertes;
CREATE TABLE alertes (
    id INT PRIMARY KEY AUTO_INCREMENT,
    entreprise_id INT NOT NULL,
    type ENUM('volume_plaintes', 'baisse_satisfaction', 'sujet_sensible', 'crise_potentielle') NOT NULL,
    titre VARCHAR(255) NOT NULL,
    description TEXT,
    severite ENUM('info', 'warning', 'critical') DEFAULT 'info',
    donnees TEXT,
    is_read BOOLEAN DEFAULT FALSE,
    is_resolved BOOLEAN DEFAULT FALSE,
    resolved_by INT,
    resolved_at DATETIME,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (entreprise_id) REFERENCES entreprises(id) ON DELETE CASCADE,
    FOREIGN KEY (resolved_by) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_entreprise_read (entreprise_id, is_read)
) ENGINE=InnoDB;

-- =============================================
-- TABLE: cx_index_daily
-- Statistiques CX Index journalières
-- =============================================
DROP TABLE IF EXISTS cx_index_daily;
CREATE TABLE cx_index_daily (
    id INT PRIMARY KEY AUTO_INCREMENT,
    entreprise_id INT NOT NULL,
    date DATE NOT NULL,
    total_conversations INT DEFAULT 0,
    conversations_resolues INT DEFAULT 0,
    temps_reponse_moyen INT DEFAULT 0,
    temps_resolution_moyen INT DEFAULT 0,
    taux_satisfaction DECIMAL(5,2),
    taux_escalade DECIMAL(5,2),
    score_cx DECIMAL(5,2),
    details TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (entreprise_id) REFERENCES entreprises(id) ON DELETE CASCADE,
    UNIQUE INDEX idx_entreprise_date (entreprise_id, date)
) ENGINE=InnoDB;

-- =============================================
-- TABLE: logs_activite
-- Journal d'activité
-- =============================================
DROP TABLE IF EXISTS logs_activite;
CREATE TABLE logs_activite (
    id INT PRIMARY KEY AUTO_INCREMENT,
    entreprise_id INT,
    user_id INT,
    action VARCHAR(100) NOT NULL,
    description TEXT,
    ip_address VARCHAR(45),
    user_agent TEXT,
    donnees TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_entreprise (entreprise_id),
    INDEX idx_user (user_id),
    INDEX idx_created (created_at)
) ENGINE=InnoDB;

-- =============================================
-- TABLE: abonnements
-- Historique des abonnements
-- =============================================
DROP TABLE IF EXISTS abonnements;
CREATE TABLE abonnements (
    id INT PRIMARY KEY AUTO_INCREMENT,
    entreprise_id INT NOT NULL,
    type ENUM('basic', 'pro', 'enterprise') NOT NULL,
    date_debut DATE NOT NULL,
    date_fin DATE,
    montant DECIMAL(10,2),
    statut ENUM('active', 'expired', 'cancelled') DEFAULT 'active',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (entreprise_id) REFERENCES entreprises(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- =============================================
-- TABLE: langues
-- Langues configurées
-- =============================================
DROP TABLE IF EXISTS langues;
CREATE TABLE langues (
    id INT PRIMARY KEY AUTO_INCREMENT,
    code VARCHAR(10) UNIQUE NOT NULL,
    nom VARCHAR(100) NOT NULL,
    nom_local VARCHAR(100),
    is_active BOOLEAN DEFAULT TRUE,
    stt_provider VARCHAR(50),
    tts_provider VARCHAR(50),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

SET FOREIGN_KEY_CHECKS = 1;

-- =============================================
-- DONNÉES INITIALES
-- =============================================

-- Langues par défaut
INSERT INTO langues (code, nom, nom_local, is_active) VALUES
('fr', 'Français', 'Français', TRUE),
('bm', 'Bambara', 'Bamanankan', TRUE),
('ff', 'Peulh', 'Fulfulde', TRUE),
('snk', 'Soninké', 'Sooninkanxanne', TRUE),
('en', 'Anglais', 'English', TRUE);

-- Tags prédéfinis globaux
INSERT INTO tag_definitions (entreprise_id, tag, category, color, description) VALUES
(NULL, 'question', 'type', '#3b82f6', 'Question générale'),
(NULL, 'plainte', 'type', '#ef4444', 'Réclamation ou plainte'),
(NULL, 'demande_info', 'type', '#8b5cf6', 'Demande d\'information'),
(NULL, 'assistance', 'type', '#06b6d4', 'Demande d\'aide'),
(NULL, 'feedback', 'type', '#10b981', 'Retour ou suggestion'),
(NULL, 'urgence', 'type', '#f97316', 'Demande urgente'),
(NULL, 'facturation', 'sujet', '#eab308', 'Sujet facturation'),
(NULL, 'technique', 'sujet', '#6366f1', 'Problème technique'),
(NULL, 'compte', 'sujet', '#ec4899', 'Gestion de compte'),
(NULL, 'livraison', 'sujet', '#14b8a6', 'Sujet livraison'),
(NULL, 'produit', 'sujet', '#f59e0b', 'Question produit'),
(NULL, 'service', 'sujet', '#8b5cf6', 'Question service'),
(NULL, 'remboursement', 'sujet', '#ef4444', 'Demande remboursement'),
(NULL, 'positif', 'sentiment', '#10b981', 'Sentiment positif'),
(NULL, 'négatif', 'sentiment', '#ef4444', 'Sentiment négatif'),
(NULL, 'neutre', 'sentiment', '#6b7280', 'Sentiment neutre'),
(NULL, 'urgent', 'priorite', '#ef4444', 'Priorité urgente'),
(NULL, 'haute', 'priorite', '#f97316', 'Priorité haute'),
(NULL, 'normale', 'priorite', '#3b82f6', 'Priorité normale'),
(NULL, 'basse', 'priorite', '#6b7280', 'Priorité basse');

-- Super Admin par défaut (mot de passe: admin123)
INSERT INTO users (email, password, nom, prenom, role, is_active) VALUES
('admin@mysupport.ml', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Admin', 'Super', 'super_admin', TRUE);

-- Entreprise de démonstration
INSERT INTO entreprises (nom, slug, secteur, pays, email, widget_token, api_key, langues_actives, canaux_actifs) VALUES
('Entreprise Démo', 'entreprise-demo', 'Télécommunications', 'Mali', 'demo@mysupport.ml', 'demo_token_12345', 'ms_demo_api_key_12345', '["fr", "bm"]', '["web", "whatsapp", "voice"]');

-- Admin pour l'entreprise démo (mot de passe: demo123)
INSERT INTO users (entreprise_id, email, password, nom, prenom, role, is_active) VALUES
(1, 'admin@demo.ml', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Admin', 'Démo', 'admin_entreprise', TRUE);

-- Agent pour l'entreprise démo (mot de passe: agent123)
INSERT INTO users (entreprise_id, email, password, nom, prenom, role, is_active) VALUES
(1, 'agent@demo.ml', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Agent', 'Test', 'agent', TRUE);

-- FAQ de démonstration
INSERT INTO faq (entreprise_id, question, reponse, categorie, langue, keywords) VALUES
(1, 'Quels sont vos horaires d\'ouverture ?', 'Nous sommes ouverts du lundi au vendredi de 8h à 18h et le samedi de 9h à 13h.', 'Général', 'fr', 'horaires, ouverture, heures'),
(1, 'Comment puis-je contacter le service client ?', 'Vous pouvez nous contacter par téléphone, email, chat en ligne ou WhatsApp. Notre équipe est disponible pour vous aider.', 'Contact', 'fr', 'contact, téléphone, email, chat'),
(1, 'Comment suivre ma commande ?', 'Vous pouvez suivre votre commande en vous connectant à votre compte client ou en utilisant le numéro de suivi envoyé par email.', 'Livraison', 'fr', 'commande, suivi, livraison, tracking');

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

SELECT '✅ Base de données mySupport créée avec succès!' AS Message;
SELECT CONCAT('Tables créées: ', COUNT(*)) AS Info FROM information_schema.tables WHERE table_schema = 'mysupport_db';
