-- Migration: Ajouter le support Twilio Voice
-- Date: 2026-02-18

-- Ajouter les colonnes Twilio aux entreprises
ALTER TABLE entreprises 
ADD COLUMN twilio_phone_number VARCHAR(20) NULL AFTER canaux_actifs,
ADD COLUMN voice_welcome TEXT NULL AFTER twilio_phone_number,
ADD COLUMN voice_config TEXT NULL AFTER voice_welcome;

-- Table pour stocker le contexte des appels en cours
CREATE TABLE IF NOT EXISTS 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 pour l'historique des appels vocaux
CREATE TABLE IF NOT EXISTS 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;

-- Nettoyer les anciens contextes (cron job recommandé)
-- DELETE FROM voice_call_context WHERE created_at < DATE_SUB(NOW(), INTERVAL 1 DAY);
