-- ============================================================================
-- MONITORAMENTO DE SITES - SCHEMA DO BANCO DE DADOS
-- ============================================================================

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

-- ============================================================================
-- TABELA: users
-- Descrição: Usuários do sistema
-- ============================================================================
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    role ENUM('admin', 'user', 'client') NOT NULL DEFAULT 'user',
    company_name VARCHAR(255),
    phone VARCHAR(20),
    is_active BOOLEAN DEFAULT TRUE,
    last_login TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_email (email),
    INDEX idx_role (role),
    INDEX idx_is_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- TABELA: sites
-- Descrição: Sites monitorados
-- ============================================================================
CREATE TABLE sites (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    url VARCHAR(500) NOT NULL,
    domain VARCHAR(255) NOT NULL,
    technology ENUM('wordpress', 'php', 'laravel', 'symfony', 'custom') DEFAULT 'custom',
    description TEXT,
    check_interval INT DEFAULT 60,
    is_active BOOLEAN DEFAULT TRUE,
    last_check_at TIMESTAMP NULL,
    last_status ENUM('online', 'offline', 'unstable') DEFAULT 'online',
    uptime_percentage DECIMAL(5, 2) DEFAULT 100.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id),
    INDEX idx_is_active (is_active),
    INDEX idx_last_check_at (last_check_at),
    INDEX idx_domain (domain)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- TABELA: checks
-- Descrição: Histórico de verificações de sites
-- ============================================================================
CREATE TABLE checks (
    id INT PRIMARY KEY AUTO_INCREMENT,
    site_id INT NOT NULL,
    status ENUM('success', 'failed', 'timeout') NOT NULL,
    http_code INT,
    response_time INT,
    error_message TEXT,
    checked_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (site_id) REFERENCES sites(id) ON DELETE CASCADE,
    INDEX idx_site_id (site_id),
    INDEX idx_status (status),
    INDEX idx_checked_at (checked_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- TABELA: incidents
-- Descrição: Incidentes/quedas de sites
-- ============================================================================
CREATE TABLE incidents (
    id INT PRIMARY KEY AUTO_INCREMENT,
    site_id INT NOT NULL,
    status ENUM('online', 'offline', 'unstable') NOT NULL,
    error_type VARCHAR(50),
    diagnosis TEXT,
    confidence_level ENUM('alta', 'média', 'baixa') DEFAULT 'média',
    suggestions TEXT,
    started_at TIMESTAMP NOT NULL,
    resolved_at TIMESTAMP NULL,
    duration_minutes INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (site_id) REFERENCES sites(id) ON DELETE CASCADE,
    INDEX idx_site_id (site_id),
    INDEX idx_status (status),
    INDEX idx_started_at (started_at),
    INDEX idx_resolved_at (resolved_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- TABELA: alerts
-- Descrição: Configurações de alertas
-- ============================================================================
CREATE TABLE alerts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    site_id INT,
    name VARCHAR(255) NOT NULL,
    alert_type ENUM('site_down', 'site_up', 'unstable', 'high_response_time') NOT NULL,
    channels JSON NOT NULL,
    email_recipients JSON,
    whatsapp_numbers JSON,
    telegram_ids JSON,
    cooldown_minutes INT DEFAULT 5,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (site_id) REFERENCES sites(id) ON DELETE SET NULL,
    INDEX idx_user_id (user_id),
    INDEX idx_site_id (site_id),
    INDEX idx_is_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- TABELA: notifications
-- Descrição: Histórico de notificações enviadas
-- ============================================================================
CREATE TABLE notifications (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    alert_id INT,
    site_id INT,
    channel ENUM('email', 'whatsapp', 'telegram', 'in_app') NOT NULL,
    recipient VARCHAR(255),
    title VARCHAR(255) NOT NULL,
    message TEXT NOT NULL,
    status ENUM('pending', 'sent', 'failed') DEFAULT 'pending',
    error_message TEXT,
    is_read BOOLEAN DEFAULT FALSE,
    sent_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (alert_id) REFERENCES alerts(id) ON DELETE SET NULL,
    FOREIGN KEY (site_id) REFERENCES sites(id) ON DELETE SET NULL,
    INDEX idx_user_id (user_id),
    INDEX idx_status (status),
    INDEX idx_is_read (is_read),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- TABELA: reports
-- Descrição: Relatórios gerados
-- ============================================================================
CREATE TABLE reports (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    site_id INT,
    report_type ENUM('daily', 'weekly', 'monthly', 'custom') NOT NULL,
    format ENUM('pdf', 'csv') NOT NULL,
    period_start DATE NOT NULL,
    period_end DATE NOT NULL,
    file_path VARCHAR(500),
    total_checks INT,
    successful_checks INT,
    failed_checks INT,
    uptime_percentage DECIMAL(5, 2),
    average_response_time INT,
    is_sent BOOLEAN DEFAULT FALSE,
    sent_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (site_id) REFERENCES sites(id) ON DELETE SET NULL,
    INDEX idx_user_id (user_id),
    INDEX idx_site_id (site_id),
    INDEX idx_report_type (report_type),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- TABELA: cpanel_accounts
-- Descrição: Contas cPanel integradas
-- ============================================================================
CREATE TABLE cpanel_accounts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    hostname VARCHAR(255) NOT NULL,
    username VARCHAR(255) NOT NULL,
    api_token VARCHAR(500) NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    last_sync_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id),
    INDEX idx_is_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- TABELA: activity_logs
-- Descrição: Log de atividades do sistema
-- ============================================================================
CREATE TABLE activity_logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    action VARCHAR(100) NOT NULL,
    entity_type VARCHAR(50),
    entity_id INT,
    description TEXT,
    ip_address VARCHAR(45),
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_user_id (user_id),
    INDEX idx_action (action),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- ÍNDICES ADICIONAIS PARA PERFORMANCE
-- ============================================================================

-- Índices compostos para queries comuns
CREATE INDEX idx_user_site ON sites(user_id, is_active);
CREATE INDEX idx_site_check_time ON checks(site_id, checked_at);
CREATE INDEX idx_incident_period ON incidents(site_id, started_at, resolved_at);
CREATE INDEX idx_alert_user_active ON alerts(user_id, is_active);
CREATE INDEX idx_notification_user_read ON notifications(user_id, is_read, created_at);