-- schema.sql

-- Table of user
CREATE TABLE IF NOT EXISTS users (
    username VARCHAR(255) PRIMARY KEY UNIQUE NOT NULL
);

-- Table of VMs
CREATE TABLE IF NOT EXISTS vms (
    hostname  VARCHAR(255) PRIMARY KEY UNIQUE NOT NULL,
    vmid INT UNIQUE NOT NULL,
    pveuser VARCHAR(255) NOT NULL,
    pvepass VARCHAR(255) NOT NULL
);

-- Table of student access to vms
CREATE TABLE IF NOT EXISTS user_access (
    hostname  VARCHAR(255) NOT NULL,
    username VARCHAR(255) NOT NULL,
    FOREIGN KEY (hostname) REFERENCES vms (hostname),
    FOREIGN KEY (username) REFERENCES users (username),
    PRIMARY KEY (hostname, username)
);

-- Table of VM information to present for users
CREATE TABLE IF NOT EXISTS vm_info (
    hostname VARCHAR(255) PRIMARY KEY NOT NULL,
    dns VARCHAR(255) NOT NULL,
    ip VARCHAR(255) NOT NULL,
    username VARCHAR(255) NOT NULL,
    password VARCHAR(255) NOT NULL,
    services VARCHAR(255) NOT NULL,
    FOREIGN KEY (hostname) REFERENCES vms (hostname)
);
