-- CipherGuard Automated Backup
-- Date: 2026-04-25 17:02:51

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.');

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;

INSERT INTO `archived_assets` VALUES ('4', '5', 'test', 'test', 'HDD/SSD', 'N/A', 'test', 'NTFS', '111', '111', '0', '100', 'test', '', '2026-04-21 08:43:02');
INSERT INTO `archived_assets` VALUES ('5', '5', '1', '1', 'HDD/SSD', 'N/A', 'test', 'NTFS', '0', '0', '0', '100', '1', 'Pending Disposal', '2026-04-20 21:52:36');

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;


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;

INSERT INTO `asset_categories` VALUES ('1', '2', 'Laptop');
INSERT INTO `asset_categories` VALUES ('2', '2', 'Mobile Device');
INSERT INTO `asset_categories` VALUES ('3', '2', 'HDD/SSD');
INSERT INTO `asset_categories` VALUES ('4', '2', 'Network Gear');
INSERT INTO `asset_categories` VALUES ('5', '4', 'Laptop');
INSERT INTO `asset_categories` VALUES ('6', '4', 'Mobile Device');
INSERT INTO `asset_categories` VALUES ('7', '4', 'HDD/SSD');
INSERT INTO `asset_categories` VALUES ('8', '4', 'Network Gear');
INSERT INTO `asset_categories` VALUES ('9', '5', 'Laptop');
INSERT INTO `asset_categories` VALUES ('10', '5', 'Mobile Device');
INSERT INTO `asset_categories` VALUES ('11', '5', 'HDD/SSD');
INSERT INTO `asset_categories` VALUES ('12', '5', 'Network Gear');
INSERT INTO `asset_categories` VALUES ('13', '7', 'Laptop');
INSERT INTO `asset_categories` VALUES ('14', '7', 'Mobile Device');
INSERT INTO `asset_categories` VALUES ('15', '7', 'HDD/SSD');
INSERT INTO `asset_categories` VALUES ('16', '7', 'Network Gear');

CREATE TABLE `asset_chats` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `session_id` varchar(50) NOT NULL,
  `company_name` varchar(100) NOT NULL,
  `sender` varchar(10) NOT NULL,
  `message` text NOT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `asset_chats` VALUES ('1', 'SESSION_1776564316_1320', 'hi', 'guest', 'Hello, we are interested in acquiring ASSET-1001 (test).', '2026-04-19 10:05:16');
INSERT INTO `asset_chats` VALUES ('2', 'SESSION_1776564316_1320', 'hi', 'guest', 'test', '2026-04-19 10:05:27');
INSERT INTO `asset_chats` VALUES ('3', 'SESSION_1776564316_1320', 'hi', 'admin', 'haduken', '2026-04-19 10:05:57');
INSERT INTO `asset_chats` VALUES ('4', 'SESSION_1776564579_2378', '1', 'guest', 'Hello, we are interested in acquiring ASSET-1002 (Dell xps).', '2026-04-19 10:09:39');
INSERT INTO `asset_chats` VALUES ('5', 'SESSION_1776564579_2378', '1', 'admin', 'hm', '2026-04-19 10:10:11');
INSERT INTO `asset_chats` VALUES ('6', 'SESSION_1776564579_2378', '1', 'guest', '1', '2026-04-19 10:10:13');
INSERT INTO `asset_chats` VALUES ('7', 'SESSION_1776564579_2378', '1', 'admin', 'deal', '2026-04-19 10:10:16');
INSERT INTO `asset_chats` VALUES ('8', 'SESSION_1776580543_1570_A4', '1', 'guest', 'Hello, we are interested in acquiring ASSET-1004 (test).', '2026-04-19 14:35:43');
INSERT INTO `asset_chats` VALUES ('9', 'SESSION_1776580543_1570_A4', '1', 'guest', 'yey', '2026-04-19 14:35:47');
INSERT INTO `asset_chats` VALUES ('10', 'SESSION_1776580543_1570_A4', '1', 'admin', 'DFADS', '2026-04-19 14:35:58');
INSERT INTO `asset_chats` VALUES ('11', 'SESSION_1776693819_4542_A6', 'HADUKEN', 'guest', 'Hello, we are interested in acquiring ASSET-1006 (test).', '2026-04-20 22:03:39');
INSERT INTO `asset_chats` VALUES ('12', 'SESSION_1776729138_4991_A6', 'GAB', 'guest', 'Hello, we are interested in acquiring ASSET-1006 (test).', '2026-04-21 07:52:18');
INSERT INTO `asset_chats` VALUES ('13', 'SESSION_1776729138_4991_A6', 'GAB', 'admin', 'HELLO', '2026-04-21 07:52:29');
INSERT INTO `asset_chats` VALUES ('14', 'SESSION_1776737058_6086_A6', 'tet', 'guest', 'Hello, we are interested in acquiring ASSET-1006 (test).', '2026-04-21 10:04:18');

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;

INSERT INTO `asset_models` VALUES ('1', '2', '3', 'Dell xps');
INSERT INTO `asset_models` VALUES ('2', '5', '11', 'test');
INSERT INTO `asset_models` VALUES ('3', '7', '15', '21');

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;

INSERT INTO `assets` VALUES ('6', '5', '1', '1', 'HDD/SSD', 'N/A', 'test', 'NTFS', '0', '0', '0', '100', '1', 'Erased');
INSERT INTO `assets` VALUES ('8', '7', '123', 'as', 'HDD/SSD', 'N/A', '21', 'NTFS', '1', '1', '0', '100', '12312', 'Active');

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;


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;

INSERT INTO `live_drives` VALUES ('411', '1', 'DESKTOP-SEJ7EES', 'D:', 'External/Local Disk (NTFS)', '0');
INSERT INTO `live_drives` VALUES ('412', '1', 'DESKTOP-SEJ7EES', 'E:', 'External/Local Disk (NTFS)', '465');

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;


CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `email` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  `totp_secret` varchar(255) DEFAULT NULL,
  `role` enum('user','admin') DEFAULT 'user',
  `exe_access` tinyint(1) DEFAULT 0,
  `created_at` datetime DEFAULT current_timestamp(),
  `can_add_asset` tinyint(1) DEFAULT 0,
  `can_wipe_asset` tinyint(1) DEFAULT 0,
  `can_chat_partners` tinyint(1) DEFAULT 0,
  `reset_token` varchar(10) DEFAULT NULL,
  `reset_expiry` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `users` VALUES ('3', 'Luis Gabriel Bautista', 'lbautista.a12345297@umak.edu.ph', '$2y$10$aM7H1frYK4amkunPc0P7ReFUPZ7z3GkHpyDtvddb9jcJevy1TDUty', 'IUPF3EWD22PQZZ6X', 'user', '0', '2026-04-16 13:28:14', '1', '0', '0', NULL, NULL);
INSERT INTO `users` VALUES ('4', 'admin', 'johnsison2005@gmail.com', '$2y$10$DOM3N10tgxT4wClUj6WbGOBsT2O4TI9Su1CgCXUCUGSzCX7TzW.Jq', '4RJUAMT74FWARFEH', 'admin', '1', '2026-04-19 12:26:53', '0', '0', '0', NULL, NULL);
INSERT INTO `users` VALUES ('5', 'season', 'jsison.a12345215@umak.edu.ph', '$2y$10$9UgtmLG.8EErkVTxm1KOHu.7J2Vb0q2YFgCxzruNnJRssk.awKYGG', 'CE2TE2Y7NDGFSX4E', 'user', '1', '2026-04-19 14:23:38', '1', '1', '1', NULL, NULL);
INSERT INTO `users` VALUES ('6', 'bonsay', 'rectojanelle1@gmail.com', '$2y$10$c7kybtmnGYPCYdU98wfJmO.mF2OefOmalaIOMMKXslKr59Zaq.MW2', 'AKSDN6V7P47ERFXU', 'user', '1', '2026-04-19 14:38:45', '1', '1', '1', NULL, NULL);
INSERT INTO `users` VALUES ('7', 'user', 'johnvalisno2005@gmail.com', '$2y$10$nEuSBTgU6DSxsEfbtW7ws.KJA8BHk.j0Jxoqsw7POxtAHxcIMFySS', 'Y66CH2VSRGY2NJZF', 'user', '1', '2026-04-21 10:01:16', '1', '1', '1', '541988', '2026-04-25 17:07:07');

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;


