CREATE DATABASE IF NOT EXISTS phpsite CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE phpsite;

CREATE TABLE IF NOT EXISTS users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY users_username_unique (username)
);

CREATE TABLE IF NOT EXISTS vm_requests (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    vm_name VARCHAR(64) NOT NULL,
    domain_name VARCHAR(64) NOT NULL,
    size ENUM('small', 'medium', 'big') NOT NULL,
    vcpus TINYINT UNSIGNED NOT NULL,
    ram_gb SMALLINT UNSIGNED NOT NULL,
    disk_gb SMALLINT UNSIGNED NOT NULL,
    requested_by VARCHAR(50) NOT NULL,
    status ENUM('queued', 'building', 'ready', 'failed', 'deployed_to_netbox', 'cancelled') NOT NULL DEFAULT 'queued',
    description VARCHAR(500) DEFAULT NULL,
    usernames VARCHAR(500) DEFAULT NULL,
    sudo_usernames VARCHAR(500) DEFAULT NULL,
    netbox_device_id INT UNSIGNED NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY vm_requests_vm_name_unique (vm_name),
    KEY vm_requests_created_at_index (created_at)
);