CREATE DATABASE IF NOT EXISTS cipherwipe_db;
USE cipherwipe_db;

-- 1. Users Table (Includes Email for OTP and EXE Access for Admin Control)
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    role ENUM('user', 'admin') DEFAULT 'user',
    exe_access TINYINT(1) DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 2. Wiping Algorithms Table
CREATE TABLE algorithms (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    passes INT NOT NULL,
    description TEXT
);

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

-- 3. Assets Table (The New Inventory System)
CREATE TABLE assets (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    pc_name VARCHAR(100) DEFAULT 'Unknown PC',
    asset_type ENUM('HDD/SSD', 'Laptop', 'Mobile Device', 'Network Gear') DEFAULT 'HDD/SSD',
    drive_letter VARCHAR(10) NULL,
    model VARCHAR(100) DEFAULT 'Generic Asset',
    file_system VARCHAR(20) DEFAULT 'Unknown',
    capacity_gb INT NOT NULL DEFAULT 0,
    free_gb INT NOT NULL DEFAULT 0,
    used_gb INT NOT NULL DEFAULT 0,
    health_percent INT DEFAULT 100,
    serial_number VARCHAR(100) UNIQUE NOT NULL,
    status ENUM('Active', 'In Storage', 'Pending Disposal', 'Wiping', 'Erased', 'Disposed') DEFAULT 'Active',
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- 4. Wipe Jobs / History Table
CREATE TABLE wipe_jobs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    asset_id INT,
    algorithm_id INT,
    status ENUM('Pending', 'In Progress', 'Completed', 'Failed', 'Cancelled') DEFAULT 'Pending',
    progress_percent INT DEFAULT 0,
    start_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    end_time DATETIME NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (asset_id) REFERENCES assets(id) ON DELETE CASCADE,
    FOREIGN KEY (algorithm_id) REFERENCES algorithms(id)
);

-- 5. Certificates of Destruction Table
CREATE TABLE certificates (
    id INT AUTO_INCREMENT PRIMARY KEY,
    job_id INT,
    hash_key VARCHAR(64) UNIQUE,
    issued_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (job_id) REFERENCES wipe_jobs(id) ON DELETE CASCADE
);

ALTER TABLE users ADD COLUMN totp_secret VARCHAR(255) NULL AFTER password;
ALTER TABLE assets ADD COLUMN assigned_to VARCHAR(100) DEFAULT 'Unassigned' AFTER pc_name;



-- wala sa cloud

 CREATE TABLE live_drives (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    pc_name VARCHAR(100),
    drive_letter VARCHAR(10),
    model VARCHAR(100),
    capacity_gb INT
);

CREATE TABLE live_wipe_jobs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    drive_letter VARCHAR(10),
    model VARCHAR(100),
    capacity_gb INT,
    algorithm_id INT,
    status VARCHAR(50) DEFAULT 'In Progress',
    progress_percent INT DEFAULT 0,
    start_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    end_time DATETIME NULL
);


CREATE TABLE asset_categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE asset_models (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    category_id INT NOT NULL,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE asset_chats (
    id INT AUTO_INCREMENT PRIMARY KEY,
    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
);

CREATE TABLE archived_assets LIKE assets;
CREATE TABLE archived_wipe_jobs LIKE wipe_jobs;

-- Add an extra column to track when it was archived
ALTER TABLE archived_assets ADD COLUMN archived_at DATETIME DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE archived_wipe_jobs ADD COLUMN archived_at DATETIME DEFAULT CURRENT_TIMESTAMP;


ALTER TABLE users 
ADD COLUMN can_add_asset TINYINT(1) DEFAULT 0,
ADD COLUMN can_wipe_asset TINYINT(1) DEFAULT 0,
ADD COLUMN can_chat_partners TINYINT(1) DEFAULT 0;

select * from users;

ALTER TABLE users ADD COLUMN reset_token VARCHAR(10) NULL, ADD COLUMN reset_expiry DATETIME NULL;
select * from users;
