database_script.sql

CREATE DATABASE efs;
USE efs;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    passwordHash VARCHAR(255) NOT NULL,
    certificatePath VARCHAR(255) NOT NULL,
    homeDirectory VARCHAR(255) NOT NULL
);

CREATE TABLE shared_files (
    id INT AUTO_INCREMENT PRIMARY KEY,
    owner_id INT NOT NULL,
    recipient_id INT NOT NULL,
    file_path VARCHAR(255) NOT NULL,
    FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (recipient_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE files (
    id INT AUTO_INCREMENT PRIMARY KEY,
    filename VARCHAR(255) NOT NULL,
    owner_id INT, -- Foreign key to users
    encrypted BOOLEAN DEFAULT TRUE,
    encryption_key VARCHAR(255), -- Store encrypted AES key (if used)
    file_path VARCHAR(255) NOT NULL,
    FOREIGN KEY (owner_id) REFERENCES users(id)
);