-- schema.sql

-- Table of user
CREATE TABLE IF NOT EXISTS users (
    userid INT PRIMARY KEY NOT NULL UNIQUE AUTO_INCREMENT,
    username VARCHAR(255) 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 user access to vms
CREATE TABLE IF NOT EXISTS user_access (
    hostname  VARCHAR(255) NOT NULL,
    userid INT NOT NULL,
    FOREIGN KEY (hostname) REFERENCES vms (hostname),
    FOREIGN KEY (userid) REFERENCES users (userid),
    PRIMARY KEY (hostname, userid)
);

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