-- ============================================================
-- RMM PHP — Schéma MySQL (miroir Prisma)
-- ============================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ============================================================
-- USERS
-- ============================================================
CREATE TABLE IF NOT EXISTS `users` (
  `id` CHAR(36) NOT NULL,
  `email` VARCHAR(255) NOT NULL,
  `password_hash` VARCHAR(255) NOT NULL,
  `full_name` VARCHAR(255) NOT NULL,
  `role` ENUM('SUPER_ADMIN','ADMIN','VIEWER') NOT NULL DEFAULT 'ADMIN',
  `active` TINYINT(1) NOT NULL DEFAULT 1,
  `last_login_at` DATETIME(3) NULL,
  `last_login_ip` VARCHAR(45) NULL,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_email_uniq` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- AGENTS
-- ============================================================
CREATE TABLE IF NOT EXISTS `agents` (
  `id` CHAR(36) NOT NULL,
  `hostname` VARCHAR(255) NOT NULL,
  `agent_token` VARCHAR(255) NOT NULL,
  `status` ENUM('ONLINE','IDLE','OFFLINE','DISABLED') NOT NULL DEFAULT 'OFFLINE',
  `last_seen_at` DATETIME(3) NULL,
  `last_ip_address` VARCHAR(45) NULL,
  `os_name` VARCHAR(255) NULL,
  `os_version` VARCHAR(100) NULL,
  `os_build` VARCHAR(50) NULL,
  `cpu_model` VARCHAR(255) NULL,
  `ram_total_mb` INT NULL,
  `agent_version` VARCHAR(50) NULL,
  `tags` JSON NULL,
  `notes` TEXT NULL,
  `enrolled_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  `disabled_at` DATETIME(3) NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `agents_token_uniq` (`agent_token`),
  KEY `agents_status_idx` (`status`),
  KEY `agents_hostname_idx` (`hostname`),
  KEY `agents_last_seen_idx` (`last_seen_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- AGENT INVENTORY
-- ============================================================
CREATE TABLE IF NOT EXISTS `agent_inventory` (
  `id` CHAR(36) NOT NULL,
  `agent_id` CHAR(36) NOT NULL,
  `hardware` JSON NULL,
  `software` JSON NULL,
  `services` JSON NULL,
  `users` JSON NULL,
  `security` JSON NULL,
  `collected_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`),
  UNIQUE KEY `inventory_agent_uniq` (`agent_id`),
  CONSTRAINT `fk_inventory_agent` FOREIGN KEY (`agent_id`) REFERENCES `agents`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- HEARTBEATS (rolling window, partition lite)
-- ============================================================
CREATE TABLE IF NOT EXISTS `heartbeats` (
  `id` CHAR(36) NOT NULL,
  `agent_id` CHAR(36) NOT NULL,
  `cpu_percent` FLOAT NOT NULL,
  `ram_percent` FLOAT NOT NULL,
  `disk_percent` FLOAT NULL,
  `uptime` INT NOT NULL,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`),
  KEY `heartbeats_agent_created_idx` (`agent_id`, `created_at` DESC),
  CONSTRAINT `fk_heartbeat_agent` FOREIGN KEY (`agent_id`) REFERENCES `agents`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- SCREENSHOTS
-- ============================================================
CREATE TABLE IF NOT EXISTS `screenshots` (
  `id` CHAR(36) NOT NULL,
  `agent_id` CHAR(36) NOT NULL,
  `file_path` VARCHAR(500) NOT NULL,
  `width` INT NOT NULL,
  `height` INT NOT NULL,
  `file_size` INT NOT NULL,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`),
  KEY `screenshots_agent_created_idx` (`agent_id`, `created_at` DESC),
  CONSTRAINT `fk_screenshot_agent` FOREIGN KEY (`agent_id`) REFERENCES `agents`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- COMMANDS QUEUE
-- ============================================================
CREATE TABLE IF NOT EXISTS `agent_commands` (
  `id` CHAR(36) NOT NULL,
  `agent_id` CHAR(36) NOT NULL,
  `type` VARCHAR(50) NOT NULL,
  `payload` JSON NULL,
  `status` ENUM('PENDING','DELIVERED','COMPLETED','FAILED','EXPIRED') NOT NULL DEFAULT 'PENDING',
  `issued_by_id` CHAR(36) NULL,
  `issued_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `delivered_at` DATETIME(3) NULL,
  `completed_at` DATETIME(3) NULL,
  `error_message` TEXT NULL,
  PRIMARY KEY (`id`),
  KEY `commands_agent_status_idx` (`agent_id`, `status`),
  CONSTRAINT `fk_command_agent` FOREIGN KEY (`agent_id`) REFERENCES `agents`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- AUDIT LOG
-- ============================================================
CREATE TABLE IF NOT EXISTS `audit_logs` (
  `id` CHAR(36) NOT NULL,
  `user_id` CHAR(36) NULL,
  `action` VARCHAR(100) NOT NULL,
  `resource` VARCHAR(255) NULL,
  `details` JSON NULL,
  `ip_address` VARCHAR(45) NULL,
  `created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`),
  KEY `audit_created_idx` (`created_at` DESC),
  KEY `audit_user_idx` (`user_id`),
  KEY `audit_action_idx` (`action`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- SETTINGS (1 row)
-- ============================================================
CREATE TABLE IF NOT EXISTS `settings` (
  `id` INT NOT NULL DEFAULT 1,
  `organization_name` VARCHAR(255) NOT NULL DEFAULT 'Mon Organisation',
  `heartbeat_interval` INT NOT NULL DEFAULT 30,
  `inventory_interval` INT NOT NULL DEFAULT 3600,
  `screenshot_retention_days` INT NOT NULL DEFAULT 7,
  `heartbeat_retention_days` INT NOT NULL DEFAULT 30,
  `audit_retention_days` INT NOT NULL DEFAULT 365,
  `updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`),
  CHECK (`id` = 1)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Init default settings row
INSERT IGNORE INTO `settings` (`id`) VALUES (1);

SET FOREIGN_KEY_CHECKS = 1;