-- ============================================================================
-- Script de Setup do Banco de Dados
-- Integração Mercado Livre ↔ Intercom
-- ============================================================================

-- Criar banco de dados
CREATE DATABASE IF NOT EXISTS ml_integration
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

USE ml_integration;

-- ============================================================================
-- Tabela: tickets
-- Propósito: Rastrear conversas e relacionar pedidos com tickets do Intercom
-- Nota: NÃO há tabela de contacts - o relacionamento é feito diretamente pelo ticket
-- Suporta múltiplas origens: Mercado Livre (ml) e WhatsApp (whatsapp)
-- ============================================================================
CREATE TABLE IF NOT EXISTS tickets (
    id INT NOT NULL AUTO_INCREMENT,
    intercom_ticket_id VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
    mercadolivre_user_id VARCHAR(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'ID do usuário: numérico para ML, string com sufixo para WhatsApp',
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    order_id VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'ID do pedido (apenas para ML)',
    intercom_user_id VARCHAR(35) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    source ENUM('ml', 'whatsapp') DEFAULT 'ml' COMMENT 'Origem da conversa: ml=Mercado Livre, whatsapp=WhatsApp',
    PRIMARY KEY (id) USING BTREE,
    INDEX idx_order (order_id),
    INDEX idx_ml_user (mercadolivre_user_id),
    INDEX idx_intercom_ticket (intercom_ticket_id),
    INDEX idx_intercom_user (intercom_user_id),
    INDEX idx_source (source),
    INDEX idx_user_source (mercadolivre_user_id, source)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC;

-- ============================================================================
-- Tabela: messages
-- Propósito: Evitar processamento duplicado de mensagens
-- Usa o resource_id do Mercado Livre como chave única
-- ============================================================================
CREATE TABLE IF NOT EXISTS messages (
    id BIGINT NOT NULL AUTO_INCREMENT,
    resource VARCHAR(255) COLLATE utf8mb4_unicode_ci NOT NULL,
    intercom_ticket_id VARCHAR(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY idx_resource (resource),
    INDEX idx_ticket (intercom_ticket_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- Views Úteis
-- ============================================================================

-- View: Estatísticas Diárias
CREATE OR REPLACE VIEW vw_daily_stats AS
SELECT
    DATE(created_at) as date,
    COUNT(DISTINCT order_id) as total_orders,
    COUNT(DISTINCT mercadolivre_user_id) as unique_customers,
    COUNT(*) as total_tickets
FROM tickets
GROUP BY DATE(created_at)
ORDER BY date DESC;

-- View: Mensagens por Ticket
CREATE OR REPLACE VIEW vw_messages_per_ticket AS
SELECT
    t.intercom_ticket_id,
    t.order_id,
    t.mercadolivre_user_id,
    t.intercom_user_id,
    COUNT(m.id) as message_count,
    t.created_at as ticket_created_at
FROM tickets t
LEFT JOIN messages m ON t.intercom_ticket_id = m.intercom_ticket_id
GROUP BY t.id
ORDER BY t.created_at DESC;

-- ============================================================================
-- Dados de Exemplo (Comentado - Descomente para testar)
-- ============================================================================

-- INSERT INTO tickets (order_id, mercadolivre_user_id, intercom_user_id, intercom_ticket_id)
-- VALUES ('ORDER_001', '123456789', 'user_123456', 'ticket_001');

-- INSERT INTO messages (resource, intercom_ticket_id)
-- VALUES ('/messages/packs/123/sellers/456', 'ticket_001');

-- ============================================================================
-- Permissões (Ajuste conforme necessário)
-- ============================================================================

-- Criar usuário específico para a aplicação (opcional)
-- CREATE USER 'ml_app'@'localhost' IDENTIFIED BY 'senha_segura';
-- GRANT SELECT, INSERT, UPDATE ON ml_integration.* TO 'ml_app'@'localhost';
-- FLUSH PRIVILEGES;

-- ============================================================================
-- Fim do Script
-- ============================================================================

-- Verificar tabelas criadas
SHOW TABLES;

-- Verificar estrutura das tabelas
DESCRIBE tickets;
DESCRIBE messages;

-- Verificar views
SHOW FULL TABLES WHERE TABLE_TYPE LIKE 'VIEW';

