-- Migration: Sistema de Notificações e Mensagens - Versão 3.0
-- Data: 2025-01-17

-- ============================================
-- TABELA: mensagens
-- Descrição: Armazena todas as mensagens/avisos
-- ============================================
CREATE TABLE IF NOT EXISTS mensagens (
    id INT AUTO_INCREMENT PRIMARY KEY,
    remetente_id INT NOT NULL,
    remetente_nome VARCHAR(255) NOT NULL,
    remetente_role ENUM('admin', 'promoter', 'produtor') NOT NULL,

    assunto VARCHAR(255) NOT NULL,
    mensagem TEXT NOT NULL,
    tipo ENUM('aviso_geral', 'aviso_evento', 'conversa', 'individual') NOT NULL DEFAULT 'individual',

    -- Filtros para avisos
    destinatario_tipo ENUM('todos', 'promoters', 'produtores', 'evento_ativo', 'evento_especifico', 'individual') NOT NULL,
    evento_id INT NULL,

    prioridade ENUM('baixa', 'normal', 'alta', 'urgente') NOT NULL DEFAULT 'normal',

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    FOREIGN KEY (remetente_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (evento_id) REFERENCES eventos(id) ON DELETE SET NULL,
    INDEX idx_tipo (tipo),
    INDEX idx_destinatario (destinatario_tipo),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- TABELA: mensagens_destinatarios
-- Descrição: Relação de quem deve receber cada mensagem
-- ============================================
CREATE TABLE IF NOT EXISTS mensagens_destinatarios (
    id INT AUTO_INCREMENT PRIMARY KEY,
    mensagem_id INT NOT NULL,
    destinatario_id INT NOT NULL,

    lida BOOLEAN DEFAULT FALSE,
    lida_em TIMESTAMP NULL,

    arquivada BOOLEAN DEFAULT FALSE,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (mensagem_id) REFERENCES mensagens(id) ON DELETE CASCADE,
    FOREIGN KEY (destinatario_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_mensagem_destinatario (mensagem_id, destinatario_id),
    INDEX idx_destinatario (destinatario_id),
    INDEX idx_lida (lida),
    INDEX idx_arquivada (arquivada)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- TABELA: notificacoes
-- Descrição: Notificações do sistema
-- ============================================
CREATE TABLE IF NOT EXISTS notificacoes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,

    tipo ENUM('pagamento', 'novo_evento', 'checkin', 'mensagem', 'sistema', 'comissao', 'saque') NOT NULL,
    categoria ENUM('financeiro', 'eventos', 'geral', 'mensagens') NOT NULL DEFAULT 'geral',

    titulo VARCHAR(255) NOT NULL,
    mensagem TEXT NOT NULL,

    -- Dados relacionados
    referencia_tipo ENUM('evento', 'comissao', 'saque', 'convidado', 'mensagem', 'user') NULL,
    referencia_id INT NULL,

    link VARCHAR(255) NULL,
    icon VARCHAR(50) NULL DEFAULT 'fa-bell',
    cor VARCHAR(20) NULL DEFAULT '#FFD700',

    lida BOOLEAN DEFAULT FALSE,
    lida_em TIMESTAMP NULL,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_lida (user_id, lida),
    INDEX idx_tipo (tipo),
    INDEX idx_categoria (categoria),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- TABELA: user_status
-- Descrição: Status online/offline dos usuários
-- ============================================
CREATE TABLE IF NOT EXISTS user_status (
    user_id INT PRIMARY KEY,
    status ENUM('online', 'ausente', 'offline') NOT NULL DEFAULT 'offline',
    ultima_atividade TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_status (status),
    INDEX idx_atividade (ultima_atividade)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- TABELA: conversas
-- Descrição: Threads de conversas entre usuários
-- ============================================
CREATE TABLE IF NOT EXISTS conversas (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user1_id INT NOT NULL,
    user2_id INT NOT NULL,

    -- Colunas geradas para garantir unicidade independente da ordem
    user_menor INT AS (LEAST(user1_id, user2_id)) STORED,
    user_maior INT AS (GREATEST(user1_id, user2_id)) STORED,

    ultima_mensagem_id INT NULL,
    ultima_atividade TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (user1_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (user2_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_conversa (user_menor, user_maior),
    INDEX idx_users (user1_id, user2_id),
    INDEX idx_atividade (ultima_atividade)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Adicionar campo na tabela users para contadores
-- ============================================
ALTER TABLE users
ADD COLUMN IF NOT EXISTS mensagens_nao_lidas INT DEFAULT 0,
ADD COLUMN IF NOT EXISTS notificacoes_nao_lidas INT DEFAULT 0;

-- ============================================
-- Criar índices adicionais para performance
-- ============================================
CREATE INDEX IF NOT EXISTS idx_users_role ON users(role);
CREATE INDEX IF NOT EXISTS idx_eventos_status ON eventos(status);

-- ============================================
-- Inserir status inicial para usuários existentes
-- ============================================
INSERT IGNORE INTO user_status (user_id, status, ultima_atividade)
SELECT id, 'offline', NOW() FROM users;

-- ============================================
-- FIM DA MIGRATION
-- ============================================
