-- Banco de Dados para Sistema de Controle do Launcher
-- Point Blank Royal

CREATE DATABASE IF NOT EXISTS pointblank_launcher CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE pointblank_launcher;

-- Tabela de usuários
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    hwid VARCHAR(255) NOT NULL UNIQUE COMMENT 'Hardware ID único do PC',
    ip_address VARCHAR(45) COMMENT 'Último IP usado',
    pc_name VARCHAR(255) COMMENT 'Nome do computador',
    username VARCHAR(255) COMMENT 'Nome do usuário Windows',
    first_seen DATETIME NOT NULL COMMENT 'Primeira vez que usou',
    last_seen DATETIME NOT NULL COMMENT 'Última vez que abriu launcher',
    last_activity DATETIME COMMENT 'Última atividade (heartbeat)',
    total_launches INT DEFAULT 0 COMMENT 'Total de vezes que abriu',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_hwid (hwid),
    INDEX idx_last_activity (last_activity),
    INDEX idx_last_seen (last_seen)
) ENGINE=InnoDB;

-- Tabela de banimentos
CREATE TABLE IF NOT EXISTS bans (
    id INT AUTO_INCREMENT PRIMARY KEY,
    hwid VARCHAR(255) NOT NULL COMMENT 'HWID banido',
    reason TEXT NOT NULL COMMENT 'Motivo do ban',
    banned_by VARCHAR(255) DEFAULT 'Sistema' COMMENT 'Quem baniu',
    permanent TINYINT(1) DEFAULT 0 COMMENT '1 = permanente, 0 = temporário',
    banned_until DATETIME COMMENT 'Data de desbloqueio (se temporário)',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'Quando foi banido',
    INDEX idx_hwid (hwid),
    INDEX idx_active (permanent, banned_until),
    FOREIGN KEY (hwid) REFERENCES users(hwid) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Tabela de sessões do launcher
CREATE TABLE IF NOT EXISTS launcher_sessions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    hwid VARCHAR(255) NOT NULL COMMENT 'HWID do usuário',
    ip_address VARCHAR(45) COMMENT 'IP da sessão',
    started_at DATETIME NOT NULL COMMENT 'Quando abriu o launcher',
    last_heartbeat DATETIME COMMENT 'Último sinal de vida',
    ended_at DATETIME COMMENT 'Quando fechou',
    INDEX idx_hwid (hwid),
    INDEX idx_started (started_at),
    INDEX idx_active (last_heartbeat),
    FOREIGN KEY (hwid) REFERENCES users(hwid) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Tabela de detecções de processos suspeitos
CREATE TABLE IF NOT EXISTS process_detections (
    id INT AUTO_INCREMENT PRIMARY KEY,
    hwid VARCHAR(255) NOT NULL COMMENT 'HWID do usuário',
    process_name VARCHAR(255) NOT NULL COMMENT 'Nome do processo detectado',
    ip_address VARCHAR(45) COMMENT 'IP quando detectou',
    detected_at DATETIME NOT NULL COMMENT 'Quando foi detectado',
    action_taken VARCHAR(50) DEFAULT 'logged' COMMENT 'Ação tomada',
    INDEX idx_hwid (hwid),
    INDEX idx_process (process_name),
    INDEX idx_detected (detected_at),
    FOREIGN KEY (hwid) REFERENCES users(hwid) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Tabela de processos em tempo real (últimos 5 minutos)
CREATE TABLE IF NOT EXISTS realtime_processes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    hwid VARCHAR(255) NOT NULL COMMENT 'HWID do usuário',
    processes_list TEXT COMMENT 'Lista de processos (JSON)',
    updated_at DATETIME NOT NULL COMMENT 'Última atualização',
    INDEX idx_hwid (hwid),
    INDEX idx_updated (updated_at),
    FOREIGN KEY (hwid) REFERENCES users(hwid) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Tabela de logs de ações administrativas
CREATE TABLE IF NOT EXISTS admin_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    admin_user VARCHAR(255) NOT NULL COMMENT 'Admin que fez a ação',
    action VARCHAR(100) NOT NULL COMMENT 'Tipo de ação',
    target_hwid VARCHAR(255) COMMENT 'HWID afetado',
    details TEXT COMMENT 'Detalhes da ação',
    ip_address VARCHAR(45) COMMENT 'IP do admin',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_admin (admin_user),
    INDEX idx_action (action),
    INDEX idx_created (created_at)
) ENGINE=InnoDB;

-- Tabela de estatísticas diárias (cache)
CREATE TABLE IF NOT EXISTS daily_stats (
    id INT AUTO_INCREMENT PRIMARY KEY,
    stat_date DATE NOT NULL UNIQUE COMMENT 'Data da estatística',
    total_launches INT DEFAULT 0 COMMENT 'Total de aberturas',
    unique_users INT DEFAULT 0 COMMENT 'Usuários únicos',
    new_users INT DEFAULT 0 COMMENT 'Novos usuários',
    hack_detections INT DEFAULT 0 COMMENT 'Detecções de hack',
    bans_issued INT DEFAULT 0 COMMENT 'Banimentos aplicados',
    INDEX idx_date (stat_date)
) ENGINE=InnoDB;

-- Tabela de tickets de suporte
CREATE TABLE IF NOT EXISTS support_tickets (
    id INT AUTO_INCREMENT PRIMARY KEY,
    hwid VARCHAR(255) NOT NULL COMMENT 'HWID do usuário',
    subject VARCHAR(255) NOT NULL COMMENT 'Assunto',
    message TEXT NOT NULL COMMENT 'Mensagem do usuário',
    status ENUM('open', 'in_progress', 'resolved', 'closed') DEFAULT 'open',
    admin_response TEXT COMMENT 'Resposta do admin',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_hwid (hwid),
    INDEX idx_status (status),
    INDEX idx_created (created_at),
    FOREIGN KEY (hwid) REFERENCES users(hwid) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Inserir alguns dados de exemplo
INSERT INTO users (hwid, ip_address, pc_name, username, first_seen, last_seen, total_launches) VALUES
('HWID-EXEMPLO-001', '192.168.1.100', 'PC-GAMER', 'Usuario1', NOW(), NOW(), 5),
('HWID-EXEMPLO-002', '192.168.1.101', 'PC-TESTE', 'Usuario2', NOW(), NOW(), 3);

-- Procedure para limpar dados antigos (executar diariamente via cron)
DELIMITER $$
CREATE PROCEDURE cleanup_old_data()
BEGIN
    -- Remove heartbeats antigos (mais de 7 dias)
    DELETE FROM launcher_sessions WHERE last_heartbeat < DATE_SUB(NOW(), INTERVAL 7 DAY);
    
    -- Remove processos em tempo real antigos (mais de 1 hora)
    DELETE FROM realtime_processes WHERE updated_at < DATE_SUB(NOW(), INTERVAL 1 HOUR);
    
    -- Atualiza estatísticas diárias
    INSERT INTO daily_stats (stat_date, total_launches, unique_users, hack_detections, bans_issued)
    SELECT 
        CURDATE() - INTERVAL 1 DAY,
        COUNT(*),
        COUNT(DISTINCT hwid),
        (SELECT COUNT(*) FROM process_detections WHERE DATE(detected_at) = CURDATE() - INTERVAL 1 DAY),
        (SELECT COUNT(*) FROM bans WHERE DATE(created_at) = CURDATE() - INTERVAL 1 DAY)
    FROM launcher_sessions 
    WHERE DATE(started_at) = CURDATE() - INTERVAL 1 DAY
    ON DUPLICATE KEY UPDATE 
        total_launches = VALUES(total_launches),
        unique_users = VALUES(unique_users);
END$$
DELIMITER ;

-- View para usuários online agora
CREATE OR REPLACE VIEW users_online AS
SELECT 
    u.hwid,
    u.pc_name,
    u.username,
    u.ip_address,
    u.last_activity,
    TIMESTAMPDIFF(MINUTE, u.last_activity, NOW()) as minutes_ago
FROM users u
WHERE u.last_activity > DATE_SUB(NOW(), INTERVAL 5 MINUTE)
ORDER BY u.last_activity DESC;

-- View para estatísticas rápidas
CREATE OR REPLACE VIEW quick_stats AS
SELECT 
    (SELECT COUNT(*) FROM users) as total_users,
    (SELECT COUNT(*) FROM users WHERE last_activity > DATE_SUB(NOW(), INTERVAL 5 MINUTE)) as online_now,
    (SELECT COUNT(*) FROM launcher_sessions WHERE DATE(started_at) = CURDATE()) as launches_today,
    (SELECT COUNT(*) FROM launcher_sessions WHERE started_at > DATE_SUB(NOW(), INTERVAL 7 DAY)) as launches_week,
    (SELECT COUNT(*) FROM bans WHERE permanent = 1 OR banned_until > NOW()) as active_bans,
    (SELECT COUNT(*) FROM process_detections WHERE DATE(detected_at) = CURDATE()) as detections_today;
