CREATE DATABASE dogui_tickets CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Table principale des tickets
CREATE TABLE tickets (
    id INT PRIMARY KEY AUTO_INCREMENT,
    ticket_number VARCHAR(50) UNIQUE NOT NULL,
    
    -- Informations client
    customer_name VARCHAR(255) NOT NULL,
    customer_email VARCHAR(255) NOT NULL,
    
    -- Détails du ticket
    category ENUM('technique', 'compte', 'facturation', 'fonctionnalite', 'autre') NOT NULL,
    priority ENUM('low', 'normal', 'high', 'urgent') DEFAULT 'normal',
    subject VARCHAR(500) NOT NULL,
    description TEXT NOT NULL,
    
    -- Statut et suivi
    status ENUM('new', 'in_progress', 'waiting_customer', 'waiting_internal', 'resolved', 'closed') DEFAULT 'new',
    assigned_to INT NULL,
    
    -- Métadonnées
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    first_response_at TIMESTAMP NULL,
    resolved_at TIMESTAMP NULL,
    closed_at TIMESTAMP NULL,
    
    -- Informations supplémentaires
    ip_address VARCHAR(45) NULL,
    user_agent TEXT NULL,
    source VARCHAR(50) DEFAULT 'web_form',
    
    -- Index pour les recherches
    INDEX idx_ticket_number (ticket_number),
    INDEX idx_customer_email (customer_email),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at),
    INDEX idx_category (category),
    INDEX idx_priority (priority)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table des réponses/commentaires
CREATE TABLE ticket_replies (
    id INT PRIMARY KEY AUTO_INCREMENT,
    ticket_id INT NOT NULL,
    
    -- Auteur de la réponse
    author_type ENUM('customer', 'agent', 'system') NOT NULL,
    author_name VARCHAR(255) NOT NULL,
    author_email VARCHAR(255) NULL,
    author_id INT NULL,
    
    -- Contenu
    message TEXT NOT NULL,
    is_internal BOOLEAN DEFAULT FALSE,
    
    -- Métadonnées
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (ticket_id) REFERENCES tickets(id) ON DELETE CASCADE,
    INDEX idx_ticket_id (ticket_id),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table des pièces jointes
CREATE TABLE ticket_attachments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    ticket_id INT NOT NULL,
    reply_id INT NULL,
    
    -- Informations du fichier
    filename VARCHAR(255) NOT NULL,
    original_filename VARCHAR(255) NOT NULL,
    file_path VARCHAR(500) NOT NULL,
    file_size INT NOT NULL,
    mime_type VARCHAR(100) NOT NULL,
    
    -- Métadonnées
    uploaded_by_type ENUM('customer', 'agent') NOT NULL,
    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (ticket_id) REFERENCES tickets(id) ON DELETE CASCADE,
    FOREIGN KEY (reply_id) REFERENCES ticket_replies(id) ON DELETE CASCADE,
    INDEX idx_ticket_id (ticket_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table d'historique des changements de statut
CREATE TABLE ticket_status_history (
    id INT PRIMARY KEY AUTO_INCREMENT,
    ticket_id INT NOT NULL,
    
    -- Changement de statut
    old_status ENUM('new', 'in_progress', 'waiting_customer', 'waiting_internal', 'resolved', 'closed'),
    new_status ENUM('new', 'in_progress', 'waiting_customer', 'waiting_internal', 'resolved', 'closed') NOT NULL,
    
    -- Informations sur le changement
    changed_by_type ENUM('customer', 'agent', 'system') NOT NULL,
    changed_by_id INT NULL,
    comment TEXT NULL,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (ticket_id) REFERENCES tickets(id) ON DELETE CASCADE,
    INDEX idx_ticket_id (ticket_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table des tags/étiquettes
CREATE TABLE ticket_tags (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) UNIQUE NOT NULL,
    color VARCHAR(7) DEFAULT '#3B82F6',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table de liaison tickets-tags
CREATE TABLE ticket_tag_relations (
    ticket_id INT NOT NULL,
    tag_id INT NOT NULL,
    added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    PRIMARY KEY (ticket_id, tag_id),
    FOREIGN KEY (ticket_id) REFERENCES tickets(id) ON DELETE CASCADE,
    FOREIGN KEY (tag_id) REFERENCES ticket_tags(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table des métriques/statistiques (pour le reporting)
CREATE TABLE ticket_metrics (
    id INT PRIMARY KEY AUTO_INCREMENT,
    ticket_id INT NOT NULL,
    
    -- Temps de réponse
    time_to_first_response INT NULL COMMENT 'En minutes',
    time_to_resolution INT NULL COMMENT 'En minutes',
    
    -- Nombre d'interactions
    total_replies INT DEFAULT 0,
    customer_replies INT DEFAULT 0,
    agent_replies INT DEFAULT 0,
    
    -- Satisfaction client
    customer_rating INT NULL COMMENT '1-5 étoiles',
    customer_feedback TEXT NULL,
    
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (ticket_id) REFERENCES tickets(id) ON DELETE CASCADE,
    UNIQUE KEY idx_ticket_id (ticket_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Trigger pour mettre à jour les métriques automatiquement
DELIMITER //

CREATE TRIGGER update_ticket_metrics_on_reply
AFTER INSERT ON ticket_replies
FOR EACH ROW
BEGIN
    -- Créer l'entrée de métrique si elle n'existe pas
    INSERT INTO ticket_metrics (ticket_id, total_replies, customer_replies, agent_replies)
    VALUES (NEW.ticket_id, 0, 0, 0)
    ON DUPLICATE KEY UPDATE ticket_id = ticket_id;
    
    -- Mettre à jour le compteur de réponses
    UPDATE ticket_metrics
    SET 
        total_replies = total_replies + 1,
        customer_replies = customer_replies + IF(NEW.author_type = 'customer', 1, 0),
        agent_replies = agent_replies + IF(NEW.author_type = 'agent', 1, 0)
    WHERE ticket_id = NEW.ticket_id;
    
    -- Si c'est la première réponse d'un agent, mettre à jour first_response_at
    IF NEW.author_type = 'agent' THEN
        UPDATE tickets
        SET first_response_at = COALESCE(first_response_at, NEW.created_at)
        WHERE id = NEW.ticket_id;
    END IF;
END;

-- Données de démarrage pour les tags
INSERT INTO ticket_tags (name, color) VALUES
('Bug', '#EF4444'),
('Amélioration', '#10B981'),
('Question', '#3B82F6'),
('Urgent', '#F59E0B'),
('Documentation', '#8B5CF6'),
('Fermé', '#6B7280');