-- =============================================
-- Migration: Système d'escalade et tickets
-- Ajoute le support pour tickets avec numéro de suivi
-- et demandes de chat en temps réel
-- =============================================

-- Table des tickets de support
CREATE TABLE IF NOT EXISTS 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 des demandes d'assistance en temps réel
CREATE TABLE IF NOT EXISTS 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 des notifications pour les agents
CREATE TABLE IF NOT EXISTS 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;

-- Ajouter colonnes à conversations pour l'escalade
-- Note: Ignorer les erreurs si les colonnes existent déjà
ALTER TABLE conversations 
ADD COLUMN escalation_type ENUM('none', 'ticket', 'live_chat') DEFAULT 'none';

ALTER TABLE conversations 
ADD COLUMN ticket_id INT NULL;

ALTER TABLE conversations 
ADD COLUMN live_request_id INT NULL;

ALTER TABLE conversations 
ADD COLUMN escalated_at DATETIME NULL;

-- Compteur de tickets par entreprise (pour générer les numéros)
ALTER TABLE entreprises 
ADD COLUMN ticket_counter INT DEFAULT 0;
