-- Database: tracker
-- Monitoring System Database Schema

-- =============================================
-- TABLE: agents (Daftar PC yang dipantau)
-- =============================================
CREATE TABLE agents (
    id INT PRIMARY KEY AUTO_INCREMENT,
    agent_id VARCHAR(50) UNIQUE NOT NULL,
    agent_name VARCHAR(100) NOT NULL,
    pc_name VARCHAR(100) NOT NULL,
    pc_ip VARCHAR(15),
    os_info VARCHAR(100),
    status ENUM('online', 'offline', 'active', 'inactive') DEFAULT 'offline',
    last_seen TIMESTAMP,
    installed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_active BOOLEAN DEFAULT TRUE
);

-- =============================================
-- TABLE: pc_activities (Log aktivitas PC target)
-- =============================================
CREATE TABLE pc_activities (
    id INT PRIMARY KEY AUTO_INCREMENT,
    agent_id VARCHAR(50) NOT NULL,
    activity_type ENUM('app_launch', 'app_close', 'browser_open', 'file_access', 'process_info') NOT NULL,
    app_name VARCHAR(255),
    app_path VARCHAR(500),
    website_url VARCHAR(500),
    description TEXT,
    logged_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (agent_id) REFERENCES agents(agent_id) ON DELETE CASCADE,
    INDEX idx_agent_logged (agent_id, logged_at),
    INDEX idx_type_time (activity_type, logged_at)
);

-- =============================================
-- TABLE: running_processes (Proses aktif saat ini)
-- =============================================
CREATE TABLE running_processes (
    id INT PRIMARY KEY AUTO_INCREMENT,
    agent_id VARCHAR(50) NOT NULL,
    process_name VARCHAR(255) NOT NULL,
    process_id INT,
    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: browser_history (Riwayat browser yang diakses)
-- =============================================
CREATE TABLE browser_history (
    id INT PRIMARY KEY AUTO_INCREMENT,
    agent_id VARCHAR(50) NOT NULL,
    browser_name VARCHAR(50),
    website_url VARCHAR(500),
    website_title VARCHAR(255),
    visit_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    duration_seconds INT,
    FOREIGN KEY (agent_id) REFERENCES agents(agent_id) ON DELETE CASCADE,
    INDEX idx_agent_time (agent_id, visit_time),
    FULLTEXT idx_url (website_url)
);

-- =============================================
-- TABLE: bandwidth_control (Kontrol bandwidth)
-- =============================================
CREATE TABLE bandwidth_control (
    id INT PRIMARY KEY AUTO_INCREMENT,
    agent_id VARCHAR(50) NOT NULL,
    control_type ENUM('limit', 'block', 'restore') NOT NULL,
    limit_kbps INT,
    reason TEXT,
    issued_by VARCHAR(100),
    issued_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    executed_at TIMESTAMP,
    is_active BOOLEAN DEFAULT TRUE,
    FOREIGN KEY (agent_id) REFERENCES agents(agent_id) ON DELETE CASCADE,
    INDEX idx_agent_active (agent_id, is_active)
);

-- =============================================
-- TABLE: network_status (Status jaringan internet)
-- =============================================
CREATE TABLE network_status (
    id INT PRIMARY KEY AUTO_INCREMENT,
    agent_id VARCHAR(50) NOT NULL,
    is_connected BOOLEAN,
    internet_speed_down DECIMAL(10, 2),
    internet_speed_up DECIMAL(10, 2),
    signal_strength INT,
    checked_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (agent_id) REFERENCES agents(agent_id) ON DELETE CASCADE,
    INDEX idx_agent_time (agent_id, checked_at)
);

-- =============================================
-- TABLE: remote_commands (Perintah yang dikirim ke agent)
-- =============================================
CREATE TABLE remote_commands (
    id INT PRIMARY KEY AUTO_INCREMENT,
    agent_id VARCHAR(50) NOT NULL,
    command_type ENUM('shutdown', 'restart', 'lock', 'disconnect_internet', 'connect_internet', 'screen_mirror', 'take_screenshot', 'refresh_data') NOT NULL,
    command_params JSON,
    status ENUM('pending', 'executing', 'completed', 'failed') DEFAULT 'pending',
    issued_by VARCHAR(100),
    issued_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    executed_at TIMESTAMP,
    response TEXT,
    FOREIGN KEY (agent_id) REFERENCES agents(agent_id) ON DELETE CASCADE,
    INDEX idx_agent_status (agent_id, status),
    INDEX idx_pending (status, issued_at)
);

-- =============================================
-- TABLE: screenshots (Screenshot PC target)
-- =============================================
CREATE TABLE screenshots (
    id INT PRIMARY KEY AUTO_INCREMENT,
    agent_id VARCHAR(50) NOT NULL,
    screenshot_path VARCHAR(500),
    file_size INT,
    captured_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP,
    FOREIGN KEY (agent_id) REFERENCES agents(agent_id) ON DELETE CASCADE,
    INDEX idx_agent_captured (agent_id, captured_at)
);

-- =============================================
-- TABLE: system_info (Informasi sistem PC target)
-- =============================================
CREATE TABLE system_info (
    id INT PRIMARY KEY AUTO_INCREMENT,
    agent_id VARCHAR(50) NOT NULL,
    cpu_name VARCHAR(255),
    cpu_cores INT,
    cpu_usage DECIMAL(5, 2),
    memory_total INT,
    memory_used INT,
    disk_total INT,
    disk_used INT,
    os_name VARCHAR(100),
    os_version VARCHAR(50),
    last_boot TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (agent_id) REFERENCES agents(agent_id) ON DELETE CASCADE,
    UNIQUE KEY unique_agent (agent_id)
);

-- =============================================
-- TABLE: users (Pengguna yang bisa monitoring)
-- =============================================
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(100),
    role ENUM('admin', 'supervisor', 'viewer') DEFAULT 'viewer',
    email VARCHAR(100),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_login TIMESTAMP,
    INDEX idx_active (is_active)
);

-- =============================================
-- TABLE: user_permissions (Izin akses per user)
-- =============================================
CREATE TABLE user_permissions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    agent_id VARCHAR(50) NOT NULL,
    can_view BOOLEAN DEFAULT TRUE,
    can_control BOOLEAN DEFAULT FALSE,
    can_screenshot BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (agent_id) REFERENCES agents(agent_id) ON DELETE CASCADE,
    UNIQUE KEY unique_user_agent (user_id, agent_id)
);

-- =============================================
-- TABLE: audit_log (Log audit semua aktivitas)
-- =============================================
CREATE TABLE audit_log (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    agent_id VARCHAR(50),
    action VARCHAR(100),
    details TEXT,
    ip_address VARCHAR(15),
    logged_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_user_time (user_id, logged_at),
    INDEX idx_action_time (action, logged_at)
);

-- =============================================
-- TABLE: settings (Konfigurasi system)
-- =============================================
CREATE TABLE settings (
    id INT PRIMARY KEY AUTO_INCREMENT,
    setting_key VARCHAR(100) UNIQUE NOT NULL,
    setting_value TEXT,
    description TEXT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- =============================================
-- INDEXES
-- =============================================
CREATE INDEX idx_agents_status ON agents(status);
CREATE INDEX idx_agents_active ON agents(is_active);

-- =============================================
-- Default Data
-- =============================================
INSERT INTO settings (setting_key, setting_value, description) VALUES
('api_port', '8080', 'Port untuk API komunikasi'),
('screenshot_interval', '300', 'Interval screenshot dalam detik'),
('activity_log_retention', '30', 'Hari retensi activity log'),
('max_bandwidth_limit', '10000', 'Bandwidth limit maksimal dalam Kbps');

-- Buat user admin default
-- Password: admin123 (bcrypt hash)
INSERT INTO users (username, password_hash, full_name, role, email) VALUES
('admin', '$2y$10$rJI/5W5Z1QvLLlQ3U5RqpO5P7Uo0GZVz4Ep9BZEqYc8x5JZVRh5Hy', 'Administrator', 'admin', 'admin@tracker.local');