CREATE DATABASE IF NOT EXISTS cipherwipe_db;
USE cipherwipe_db;

-- 1. Users Table (For login and role management)
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    role ENUM('user', 'admin') DEFAULT 'user',
    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 the default wiping algorithms
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. Logical Drives / Volumes Table (Now linked to specific users)
CREATE TABLE drives (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    drive_letter VARCHAR(10) NOT NULL,
    model VARCHAR(100) DEFAULT 'Local Disk',
    file_system VARCHAR(20) DEFAULT 'NTFS',
    capacity_gb INT NOT NULL,
    free_gb INT NOT NULL,
    used_gb INT NOT NULL,
    serial_number VARCHAR(100) UNIQUE NOT NULL,
    status ENUM('Available', 'Wiping', 'Erased', 'Failed') DEFAULT 'Available',
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- 4. Wipe Jobs / History Table (Tracks who wiped what)
CREATE TABLE wipe_jobs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    drive_id INT,
    algorithm_id INT,
    status ENUM('Pending', 'In Progress', 'Completed', 'Failed') 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 (drive_id) REFERENCES drives(id) ON DELETE CASCADE,
    FOREIGN KEY (algorithm_id) REFERENCES algorithms(id)
);

-- 5. Certificates of Destruction Table (For the PDF generation)
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
);