-- =====================================================
-- Schéma de base de données pour l'API Up2AI
-- Compatible MySQL 5.7+ / MariaDB 10.2+
-- =====================================================

-- Création de la base de données (à exécuter manuellement dans cPanel)
-- CREATE DATABASE IF NOT EXISTS up2ai_api CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- USE up2ai_api;

-- Table des tenants (clients)
CREATE TABLE IF NOT EXISTS tenants (
    id INT AUTO_INCREMENT PRIMARY KEY,
    slug VARCHAR(128) NOT NULL UNIQUE,
    name VARCHAR(255) NULL,
    timezone VARCHAR(64) DEFAULT 'Europe/Paris',
    active TINYINT(1) DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_slug (slug),
    INDEX idx_active (active),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table des instances voice (VMs)
CREATE TABLE IF NOT EXISTS voice_instances (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    last_seen DATETIME NULL,
    status ENUM('up','down') DEFAULT 'up',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    CONSTRAINT fk_voice_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    
    INDEX idx_tenant_id (tenant_id),
    INDEX idx_last_seen (last_seen),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table des appels
CREATE TABLE IF NOT EXISTS calls (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    call_ext_id VARCHAR(128) NOT NULL,
    started_at DATETIME NULL,
    ended_at DATETIME NULL,
    duration_sec INT NULL,
    outcome ENUM('booked','transfer','resolved','abandoned','unknown') DEFAULT 'unknown',
    hangup_reason VARCHAR(64) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    UNIQUE KEY uniq_call (tenant_id, call_ext_id),
    
    CONSTRAINT fk_calls_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    
    INDEX idx_tenant_id (tenant_id),
    INDEX idx_call_ext_id (call_ext_id),
    INDEX idx_started_at (started_at),
    INDEX idx_ended_at (ended_at),
    INDEX idx_outcome (outcome),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table des événements d'appel (logs bruts)
CREATE TABLE IF NOT EXISTS call_events (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    call_id BIGINT NULL,
    type VARCHAR(64) NOT NULL,
    payload_json JSON NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    CONSTRAINT fk_events_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_events_call FOREIGN KEY (call_id) REFERENCES calls(id) ON DELETE SET NULL,
    
    INDEX idx_tenant_id (tenant_id),
    INDEX idx_call_id (call_id),
    INDEX idx_type (type),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table des métriques (optionnel, pour le futur)
CREATE TABLE IF NOT EXISTS metrics (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NULL,
    metric_name VARCHAR(128) NOT NULL,
    metric_value DECIMAL(15,4) NOT NULL,
    metric_unit VARCHAR(32) NULL,
    tags JSON NULL,
    recorded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    CONSTRAINT fk_metrics_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE SET NULL,
    
    INDEX idx_tenant_id (tenant_id),
    INDEX idx_metric_name (metric_name),
    INDEX idx_recorded_at (recorded_at),
    INDEX idx_tags ((CAST(tags AS CHAR(100))))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table des logs d'audit (optionnel, pour la sécurité)
CREATE TABLE IF NOT EXISTS audit_logs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NULL,
    user_ip VARCHAR(45) NOT NULL,
    user_agent TEXT NULL,
    action VARCHAR(128) NOT NULL,
    resource_type VARCHAR(64) NULL,
    resource_id VARCHAR(128) NULL,
    details JSON NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    CONSTRAINT fk_audit_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE SET NULL,
    
    INDEX idx_tenant_id (tenant_id),
    INDEX idx_user_ip (user_ip),
    INDEX idx_action (action),
    INDEX idx_resource (resource_type, resource_id),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table des rate limits (pour la persistance)
CREATE TABLE IF NOT EXISTS rate_limits (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    ip_address VARCHAR(45) NOT NULL,
    endpoint VARCHAR(255) NOT NULL,
    request_count INT NOT NULL DEFAULT 1,
    window_start TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    UNIQUE KEY uniq_rate_limit (ip_address, endpoint, window_start),
    
    INDEX idx_ip_endpoint (ip_address, endpoint),
    INDEX idx_window_start (window_start),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- INSERTION DES DONNÉES INITIALES
-- =====================================================

-- Insérer le tenant par défaut (à modifier selon vos besoins)
INSERT INTO tenants (slug, name, timezone, active) VALUES 
('raphael.moreno.ct0001', 'Raphael Moreno - Client Test 0001', 'Europe/Paris', 1)
ON DUPLICATE KEY UPDATE 
    name = VALUES(name),
    timezone = VALUES(timezone),
    active = VALUES(active);

-- =====================================================
-- VUES UTILES (optionnel)
-- =====================================================

-- Vue des instances voice avec informations du tenant
CREATE OR REPLACE VIEW v_voice_instances AS
SELECT 
    vi.id,
    vi.tenant_id,
    t.slug as tenant_slug,
    t.name as tenant_name,
    vi.last_seen,
    vi.status,
    vi.created_at,
    vi.updated_at,
    CASE 
        WHEN vi.last_seen IS NULL THEN 'never'
        WHEN vi.last_seen < DATE_SUB(NOW(), INTERVAL 5 MINUTE) THEN 'down'
        ELSE 'up'
    END as health_status
FROM voice_instances vi
JOIN tenants t ON vi.tenant_id = t.id
WHERE t.active = 1;

-- Vue des statistiques des appels par tenant
CREATE OR REPLACE VIEW v_call_stats AS
SELECT 
    t.id as tenant_id,
    t.slug as tenant_slug,
    t.name as tenant_name,
    COUNT(c.id) as total_calls,
    COUNT(CASE WHEN c.outcome = 'booked' THEN 1 END) as booked_calls,
    COUNT(CASE WHEN c.outcome = 'resolved' THEN 1 END) as resolved_calls,
    COUNT(CASE WHEN c.outcome = 'abandoned' THEN 1 END) as abandoned_calls,
    COUNT(CASE WHEN c.outcome = 'transfer' THEN 1 END) as transfer_calls,
    AVG(c.duration_sec) as avg_duration_sec,
    MAX(c.started_at) as last_call_at
FROM tenants t
LEFT JOIN calls c ON t.id = c.tenant_id
WHERE t.active = 1
GROUP BY t.id, t.slug, t.name;

-- =====================================================
-- PROCÉDURES STOCKÉES UTILES (optionnel)
-- =====================================================

DELIMITER //

-- Procédure pour nettoyer les anciens événements
CREATE PROCEDURE IF NOT EXISTS sp_cleanup_old_events(IN days_to_keep INT)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;
    
    START TRANSACTION;
    
    DELETE FROM call_events 
    WHERE created_at < DATE_SUB(NOW(), INTERVAL days_to_keep DAY);
    
    DELETE FROM audit_logs 
    WHERE created_at < DATE_SUB(NOW(), INTERVAL days_to_keep DAY);
    
    DELETE FROM metrics 
    WHERE recorded_at < DATE_SUB(NOW(), INTERVAL days_to_keep DAY);
    
    COMMIT;
END //

-- Procédure pour nettoyer les anciennes instances voice
CREATE PROCEDURE IF NOT EXISTS sp_cleanup_old_instances(IN hours_to_keep INT)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;
    
    START TRANSACTION;
    
    DELETE FROM voice_instances 
    WHERE last_seen < DATE_SUB(NOW(), INTERVAL hours_to_keep HOUR);
    
    COMMIT;
END //

DELIMITER ;

-- =====================================================
-- INDEX SUPPLÉMENTAIRES POUR LES PERFORMANCES
-- =====================================================

-- Index composites pour les requêtes fréquentes
CREATE INDEX idx_calls_tenant_started ON calls(tenant_id, started_at);
CREATE INDEX idx_calls_tenant_ended ON calls(tenant_id, ended_at);
CREATE INDEX idx_events_tenant_type ON call_events(tenant_id, type);
CREATE INDEX idx_events_tenant_created ON call_events(tenant_id, created_at);

-- Index pour les recherches textuelles
CREATE INDEX idx_calls_ext_id_like ON calls(call_ext_id);
CREATE INDEX idx_tenants_slug_like ON tenants(slug);

-- =====================================================
-- COMMENTAIRES ET DOCUMENTATION
-- =====================================================

-- Ajouter des commentaires aux tables
ALTER TABLE tenants COMMENT = 'Table des tenants (clients) de l''API Up2AI';
ALTER TABLE voice_instances COMMENT = 'Table des instances voice (VMs) et leur statut';
ALTER TABLE calls COMMENT = 'Table des appels téléphoniques avec métadonnées';
ALTER TABLE call_events COMMENT = 'Table des événements d''appel (logs bruts JSON)';
ALTER TABLE metrics COMMENT = 'Table des métriques système (optionnel)';
ALTER TABLE audit_logs COMMENT = 'Table des logs d''audit pour la sécurité';
ALTER TABLE rate_limits COMMENT = 'Table des limites de débit par IP et endpoint';