-- CipherGuard Automated Backup

-- --------------------------------------------------------
-- Table Structure for `algorithms`
-- --------------------------------------------------------
CREATE TABLE `algorithms` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `passes` int(11) NOT NULL,
  `description` text DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `algorithms` VALUES ('1', 'Zero Fill', '1', 'Writes zeroes to all sectors. Fast but less secure.');
INSERT INTO `algorithms` VALUES ('2', 'DoD 5220.22-M', '3', 'Department of Defense standard. 3 passes (zeros, ones, random).');
INSERT INTO `algorithms` VALUES ('3', 'Gutmann Method', '35', 'Maximum security. 35 passes. Very slow.');


-- --------------------------------------------------------
-- Table Structure for `archived_assets`
-- --------------------------------------------------------
CREATE TABLE `archived_assets` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `pc_name` varchar(100) DEFAULT 'Unknown PC',
  `assigned_to` varchar(100) DEFAULT 'Unassigned',
  `asset_type` enum('HDD/SSD','Laptop','Mobile Device','Network Gear') DEFAULT 'HDD/SSD',
  `drive_letter` varchar(10) DEFAULT NULL,
  `model` varchar(100) DEFAULT 'Generic Asset',
  `file_system` varchar(20) DEFAULT 'Unknown',
  `capacity_gb` int(11) NOT NULL DEFAULT 0,
  `free_gb` int(11) NOT NULL DEFAULT 0,
  `used_gb` int(11) NOT NULL DEFAULT 0,
  `health_percent` int(11) DEFAULT 100,
  `serial_number` varchar(100) NOT NULL,
  `status` enum('Active','In Storage','Pending Disposal','Wiping','Erased','Disposed') DEFAULT 'Active',
  `archived_at` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `serial_number` (`serial_number`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;



-- --------------------------------------------------------
-- Table Structure for `archived_wipe_jobs`
-- --------------------------------------------------------
CREATE TABLE `archived_wipe_jobs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `asset_id` int(11) DEFAULT NULL,
  `algorithm_id` int(11) DEFAULT NULL,
  `status` enum('Pending','In Progress','Completed','Failed','Cancelled') DEFAULT 'Pending',
  `progress_percent` int(11) DEFAULT 0,
  `start_time` datetime DEFAULT current_timestamp(),
  `end_time` datetime DEFAULT NULL,
  `archived_at` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `asset_id` (`asset_id`),
  KEY `algorithm_id` (`algorithm_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;



-- --------------------------------------------------------
-- Table Structure for `asset_categories`
-- --------------------------------------------------------
CREATE TABLE `asset_categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;



-- --------------------------------------------------------
-- Table Structure for `asset_models`
-- --------------------------------------------------------
CREATE TABLE `asset_models` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `category_id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;



-- --------------------------------------------------------
-- Table Structure for `assets`
-- --------------------------------------------------------
CREATE TABLE `assets` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `pc_name` varchar(100) DEFAULT 'Unknown PC',
  `assigned_to` varchar(100) DEFAULT 'Unassigned',
  `asset_type` enum('HDD/SSD','Laptop','Mobile Device','Network Gear') DEFAULT 'HDD/SSD',
  `drive_letter` varchar(10) DEFAULT NULL,
  `model` varchar(100) DEFAULT 'Generic Asset',
  `file_system` varchar(20) DEFAULT 'Unknown',
  `capacity_gb` int(11) NOT NULL DEFAULT 0,
  `free_gb` int(11) NOT NULL DEFAULT 0,
  `used_gb` int(11) NOT NULL DEFAULT 0,
  `health_percent` int(11) DEFAULT 100,
  `serial_number` varchar(100) NOT NULL,
  `status` enum('Active','In Storage','Pending Disposal','Wiping','Erased','Disposed') DEFAULT 'Active',
  PRIMARY KEY (`id`),
  UNIQUE KEY `serial_number` (`serial_number`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `assets_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;



-- --------------------------------------------------------
-- Table Structure for `certificates`
-- --------------------------------------------------------
CREATE TABLE `certificates` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `job_id` int(11) DEFAULT NULL,
  `hash_key` varchar(64) DEFAULT NULL,
  `issued_at` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `hash_key` (`hash_key`),
  KEY `job_id` (`job_id`),
  CONSTRAINT `certificates_ibfk_1` FOREIGN KEY (`job_id`) REFERENCES `wipe_jobs` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;



-- --------------------------------------------------------
-- Table Structure for `live_drives`
-- --------------------------------------------------------
CREATE TABLE `live_drives` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `pc_name` varchar(100) DEFAULT NULL,
  `drive_letter` varchar(10) DEFAULT NULL,
  `model` varchar(100) DEFAULT NULL,
  `capacity_gb` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;



-- --------------------------------------------------------
-- Table Structure for `live_wipe_jobs`
-- --------------------------------------------------------
CREATE TABLE `live_wipe_jobs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `drive_letter` varchar(10) DEFAULT NULL,
  `model` varchar(100) DEFAULT NULL,
  `capacity_gb` int(11) DEFAULT NULL,
  `algorithm_id` int(11) DEFAULT NULL,
  `status` varchar(50) DEFAULT 'In Progress',
  `progress_percent` int(11) DEFAULT 0,
  `start_time` datetime DEFAULT current_timestamp(),
  `end_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


-- --------------------------------------------------------
-- Table Structure for `wipe_jobs`
-- --------------------------------------------------------
CREATE TABLE `wipe_jobs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `asset_id` int(11) DEFAULT NULL,
  `algorithm_id` int(11) DEFAULT NULL,
  `status` enum('Pending','In Progress','Completed','Failed','Cancelled') DEFAULT 'Pending',
  `progress_percent` int(11) DEFAULT 0,
  `start_time` datetime DEFAULT current_timestamp(),
  `end_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `asset_id` (`asset_id`),
  KEY `algorithm_id` (`algorithm_id`),
  CONSTRAINT `wipe_jobs_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `wipe_jobs_ibfk_2` FOREIGN KEY (`asset_id`) REFERENCES `assets` (`id`) ON DELETE CASCADE,
  CONSTRAINT `wipe_jobs_ibfk_3` FOREIGN KEY (`algorithm_id`) REFERENCES `algorithms` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;



