-- -----------------------------------------------------
-- Schema Setup for Remote Control System
-- -----------------------------------------------------

-- Drop tables in safe order if they already exist
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS `settings`;
DROP TABLE IF EXISTS `logs`;
DROP TABLE IF EXISTS `notifications`;
DROP TABLE IF EXISTS `sessions`;
DROP TABLE IF EXISTS `files`;
DROP TABLE IF EXISTS `screenshots`;
DROP TABLE IF EXISTS `command_results`;
DROP TABLE IF EXISTS `commands`;
DROP TABLE IF EXISTS `pair_requests`;
DROP TABLE IF EXISTS `devices`;
DROP TABLE IF EXISTS `users`;
SET FOREIGN_KEY_CHECKS = 1;

-- 1. Users Table
CREATE TABLE `users` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `email` VARCHAR(255) UNIQUE NOT NULL,
  `password_hash` VARCHAR(255) NOT NULL,
  `full_name` VARCHAR(100) NULL,
  `role` ENUM('admin', 'user') DEFAULT 'user',
  `status` ENUM('active', 'inactive', 'pending_otp') DEFAULT 'pending_otp',
  `otp_code` VARCHAR(6) NULL,
  `otp_expires_at` DATETIME NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_users_email` (`email`),
  INDEX `idx_users_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. Devices Table (macOS Agents linked to users)
CREATE TABLE `devices` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NOT NULL,
  `device_uuid` VARCHAR(255) UNIQUE NOT NULL,
  `device_name` VARCHAR(100) NOT NULL,
  `os_version` VARCHAR(50) NOT NULL,
  `cpu_model` VARCHAR(100) NULL,
  `cpu_usage` FLOAT DEFAULT 0.0,
  `ram_total` BIGINT DEFAULT 0,
  `ram_usage` FLOAT DEFAULT 0.0,
  `storage_total` BIGINT DEFAULT 0,
  `storage_used` BIGINT DEFAULT 0,
  `battery_level` INT DEFAULT 0,
  `is_charging` TINYINT(1) DEFAULT 0,
  `wifi_ssid` VARCHAR(100) NULL,
  `ip_address` VARCHAR(45) NULL,
  `current_user` VARCHAR(100) NULL,
  `is_online` TINYINT(1) DEFAULT 0,
  `last_active_at` DATETIME NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT `fk_devices_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  INDEX `idx_devices_user_id` (`user_id`),
  INDEX `idx_devices_uuid` (`device_uuid`),
  INDEX `idx_devices_online` (`is_online`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. Pair Requests Table (For linking Mac and Android app via code)
CREATE TABLE `pair_requests` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NULL,
  `device_uuid` VARCHAR(255) NOT NULL,
  `device_name` VARCHAR(100) NOT NULL,
  `pair_code` VARCHAR(6) UNIQUE NOT NULL,
  `status` ENUM('pending', 'verified', 'expired') DEFAULT 'pending',
  `expires_at` DATETIME NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT `fk_pair_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  INDEX `idx_pair_code` (`pair_code`),
  INDEX `idx_pair_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. Commands Table (Action queue generated by Android)
CREATE TABLE `commands` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `device_id` INT NOT NULL,
  `user_id` INT NOT NULL,
  `command_type` VARCHAR(50) NOT NULL,
  `payload` LONGTEXT NOT NULL,
  `status` ENUM('pending', 'sent', 'processing', 'completed', 'failed') DEFAULT 'pending',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT `fk_commands_device` FOREIGN KEY (`device_id`) REFERENCES `devices` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_commands_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  INDEX `idx_commands_device_status` (`device_id`, `status`),
  INDEX `idx_commands_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5. Command Results Table (Returned by macOS Agent)
CREATE TABLE `command_results` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `command_id` INT UNIQUE NOT NULL,
  `stdout` LONGTEXT NULL,
  `stderr` LONGTEXT NULL,
  `exit_code` INT DEFAULT 0,
  `executed_at` DATETIME NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `fk_results_command` FOREIGN KEY (`command_id`) REFERENCES `commands` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 6. Screenshots Table (Stored captures uploaded from Mac)
CREATE TABLE `screenshots` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `device_id` INT NOT NULL,
  `file_path` VARCHAR(512) NOT NULL,
  `file_size` INT NOT NULL,
  `width` INT NOT NULL,
  `height` INT NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `fk_screenshots_device` FOREIGN KEY (`device_id`) REFERENCES `devices` (`id`) ON DELETE CASCADE,
  INDEX `idx_screenshots_device` (`device_id`),
  INDEX `idx_screenshots_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 7. Files Table (Tracks transfers between client/agent)
CREATE TABLE `files` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `device_id` INT NOT NULL,
  `user_id` INT NOT NULL,
  `file_name` VARCHAR(255) NOT NULL,
  `file_path` VARCHAR(512) NOT NULL,
  `remote_path` VARCHAR(1024) NOT NULL,
  `direction` ENUM('upload', 'download') NOT NULL,
  `file_size` BIGINT NOT NULL,
  `status` ENUM('pending', 'transferring', 'completed', 'failed') DEFAULT 'pending',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT `fk_files_device` FOREIGN KEY (`device_id`) REFERENCES `devices` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_files_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  INDEX `idx_files_device` (`device_id`),
  INDEX `idx_files_user` (`user_id`),
  INDEX `idx_files_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 8. Sessions Table (User login tokens refresh)
CREATE TABLE `sessions` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NOT NULL,
  `refresh_token` VARCHAR(255) UNIQUE NOT NULL,
  `ip_address` VARCHAR(45) NULL,
  `user_agent` VARCHAR(255) NULL,
  `expires_at` DATETIME NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `fk_sessions_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  INDEX `idx_sessions_token` (`refresh_token`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 9. Notifications Table (System alerts)
CREATE TABLE `notifications` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NOT NULL,
  `device_id` INT NULL,
  `title` VARCHAR(150) NOT NULL,
  `message` TEXT NOT NULL,
  `is_read` TINYINT(1) DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `fk_notif_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_notif_device` FOREIGN KEY (`device_id`) REFERENCES `devices` (`id`) ON DELETE SET NULL,
  INDEX `idx_notif_user_read` (`user_id`, `is_read`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 10. Audit Logs Table (Logging every action)
CREATE TABLE `logs` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NULL,
  `device_id` INT NULL,
  `event_type` VARCHAR(50) NOT NULL,
  `message` TEXT NOT NULL,
  `ip_address` VARCHAR(45) NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `fk_logs_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_logs_device` FOREIGN KEY (`device_id`) REFERENCES `devices` (`id`) ON DELETE SET NULL,
  INDEX `idx_logs_event` (`event_type`),
  INDEX `idx_logs_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 11. User Settings Table
CREATE TABLE `settings` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT UNIQUE NOT NULL,
  `screen_quality` ENUM('low', 'medium', 'high') DEFAULT 'medium',
  `refresh_rate_ms` INT DEFAULT 500,
  `pointer_speed` FLOAT DEFAULT 1.0,
  `run_at_login` TINYINT(1) DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT `fk_settings_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;