-- =============================================
-- Migration: Emails & Enquêtes Satisfaction (CSAT)
-- =============================================

-- Table des logs d'emails
CREATE TABLE IF NOT EXISTS 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 des tokens CSAT (pour liens de feedback)
CREATE TABLE IF NOT EXISTS 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 des enquêtes satisfaction (CSAT)
CREATE TABLE IF NOT EXISTS 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 des statistiques CSAT agrégées (par jour)
CREATE TABLE IF NOT EXISTS 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;

-- Ajouter colonne csat_sent à conversations pour éviter les doublons
ALTER TABLE conversations 
ADD COLUMN csat_sent BOOLEAN DEFAULT FALSE;

ALTER TABLE conversations 
ADD COLUMN csat_score INT NULL;

ALTER TABLE conversations 
ADD COLUMN csat_responded_at DATETIME NULL;

-- Configuration email par entreprise
ALTER TABLE entreprises 
ADD COLUMN email_notifications BOOLEAN DEFAULT TRUE;

ALTER TABLE entreprises 
ADD COLUMN csat_enabled BOOLEAN DEFAULT TRUE;

ALTER TABLE entreprises 
ADD COLUMN csat_delay_hours INT DEFAULT 1;
