-- =============================================
-- mySupport - Schéma de base de données
-- Plateforme de Service Client Intelligent
-- Multi-tenants (Multi-entreprises)
-- =============================================

CREATE DATABASE IF NOT EXISTS mysupport_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE mysupport_db;

-- =============================================
-- TABLE: entreprises
-- Gestion des entreprises clientes (tenants)
-- =============================================
CREATE TABLE IF NOT EXISTS 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,
    type_abonnement ENUM('basic', 'pro', 'enterprise') DEFAULT 'basic',
    max_agents INT DEFAULT 5,
    widget_token VARCHAR(64) UNIQUE,
    widget_config TEXT,
    ia_config TEXT,
    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)
-- =============================================
CREATE TABLE IF NOT EXISTS 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',
    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
) ENGINE=InnoDB;

-- =============================================
-- TABLE: clients
-- Clients finaux / Citoyens
-- =============================================
CREATE TABLE IF NOT EXISTS 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)
) ENGINE=InnoDB;

-- =============================================
-- TABLE: conversations
-- Conversations / Tickets
-- =============================================
CREATE TABLE IF NOT EXISTS 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),
    is_escalated BOOLEAN DEFAULT FALSE,
    escalation_reason TEXT,
    satisfaction_score INT,
    satisfaction_comment TEXT,
    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
-- =============================================
CREATE TABLE IF NOT EXISTS 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
-- =============================================
CREATE TABLE IF NOT EXISTS 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: alertes
-- Alertes intelligentes
-- =============================================
CREATE TABLE IF NOT EXISTS 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
-- =============================================
CREATE TABLE IF NOT EXISTS 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é
-- =============================================
CREATE TABLE IF NOT EXISTS 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
-- =============================================
CREATE TABLE IF NOT EXISTS 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
-- =============================================
CREATE TABLE IF NOT EXISTS 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;

-- =============================================
-- TABLE: ai_usage_logs
-- Logs d'utilisation de l'IA
-- =============================================
CREATE TABLE IF NOT EXISTS 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') 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;

-- =============================================
-- 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);

-- 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, langues_actives, canaux_actifs) VALUES
('Entreprise Démo', 'entreprise-demo', 'Télécommunications', 'Mali', 'demo@mysupport.ml', 'demo_token_12345', '["fr", "bm"]', '["web", "whatsapp"]');

-- 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
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);
