-- Database Schema Updates untuk Features 2-5
-- Jalankan queries ini di database

-- =============================================
-- TABLE: running_applications (Fitur #4)
-- =============================================
CREATE TABLE IF NOT EXISTS running_applications (
    id INT PRIMARY KEY AUTO_INCREMENT,
    agent_id VARCHAR(50) NOT NULL,
    process_id INT,
    process_name VARCHAR(255) NOT NULL,
    window_title VARCHAR(500),
    executable_path VARCHAR(500),
    cpu_usage DECIMAL(5, 2),
    memory_usage INT,
    status VARCHAR(50),
    snapshot_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (agent_id) REFERENCES agents(agent_id) ON DELETE CASCADE,
    INDEX idx_agent_snapshot (agent_id, snapshot_at)
);

-- =============================================
-- TABLE: media_social_activity (Fitur #5)
-- =============================================
CREATE TABLE IF NOT EXISTS media_social_activity (
    id INT PRIMARY KEY AUTO_INCREMENT,
    agent_id VARCHAR(50) NOT NULL,
    platform VARCHAR(50),  -- Facebook, Instagram, TikTok, Twitter, YouTube, WhatsApp, Telegram
    app_name VARCHAR(255),
    window_title VARCHAR(500),
    url VARCHAR(500),
    activity_type ENUM('browser', 'app', 'chat', 'video', 'post') DEFAULT 'browser',
    detected_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (agent_id) REFERENCES agents(agent_id) ON DELETE CASCADE,
    INDEX idx_agent_platform (agent_id, platform),
    INDEX idx_detected (detected_at)
);

-- =============================================
-- TABLE: bandwidth_limits (Fitur #2)
-- =============================================
CREATE TABLE IF NOT EXISTS bandwidth_limits (
    id INT PRIMARY KEY AUTO_INCREMENT,
    agent_id VARCHAR(50) NOT NULL,
    limit_kbps INT,
    status ENUM('active', 'inactive', 'failed') DEFAULT 'inactive',
    issued_by VARCHAR(100),
    issued_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    activated_at TIMESTAMP,
    FOREIGN KEY (agent_id) REFERENCES agents(agent_id) ON DELETE CASCADE,
    INDEX idx_agent_status (agent_id, status)
);

-- =============================================
-- TABLE: shutdown_logs (Fitur #3)
-- =============================================
CREATE TABLE IF NOT EXISTS shutdown_logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    agent_id VARCHAR(50) NOT NULL,
    shutdown_type ENUM('immediate', 'scheduled', 'restart') DEFAULT 'immediate',
    delay_seconds INT DEFAULT 0,
    issued_by VARCHAR(100),
    issued_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    executed_at TIMESTAMP,
    status ENUM('pending', 'executing', 'completed', 'failed') DEFAULT 'pending',
    FOREIGN KEY (agent_id) REFERENCES agents(agent_id) ON DELETE CASCADE,
    INDEX idx_agent_status (agent_id, status)
);