schema.sql

-- Bootimus Database Schema Reference
-- This is auto-generated by GORM migrations, but provided here for reference

-- Clients table: Network boot clients identified by MAC address
CREATE TABLE IF NOT EXISTS clients (
    id SERIAL PRIMARY KEY,
    created_at TIMESTAMP WITH TIME ZONE,
    updated_at TIMESTAMP WITH TIME ZONE,
    deleted_at TIMESTAMP WITH TIME ZONE,
    mac_address VARCHAR(17) UNIQUE NOT NULL,
    name VARCHAR(255),
    description TEXT,
    enabled BOOLEAN DEFAULT true,
    last_boot TIMESTAMP WITH TIME ZONE,
    boot_count INTEGER DEFAULT 0
);

CREATE INDEX IF NOT EXISTS idx_clients_deleted_at ON clients(deleted_at);
CREATE INDEX IF NOT EXISTS idx_clients_mac_address ON clients(mac_address);

-- Images table: ISO images available for boot
CREATE TABLE IF NOT EXISTS images (
    id SERIAL PRIMARY KEY,
    created_at TIMESTAMP WITH TIME ZONE,
    updated_at TIMESTAMP WITH TIME ZONE,
    deleted_at TIMESTAMP WITH TIME ZONE,
    name VARCHAR(255) UNIQUE NOT NULL,
    filename VARCHAR(255) NOT NULL,
    description TEXT,
    size BIGINT,
    enabled BOOLEAN DEFAULT true,
    public BOOLEAN DEFAULT false,
    boot_count INTEGER DEFAULT 0,
    last_booted TIMESTAMP WITH TIME ZONE
);

CREATE INDEX IF NOT EXISTS idx_images_deleted_at ON images(deleted_at);
CREATE INDEX IF NOT EXISTS idx_images_name ON images(name);

-- Client Images junction table: Many-to-many relationship
CREATE TABLE IF NOT EXISTS client_images (
    client_id INTEGER REFERENCES clients(id) ON DELETE CASCADE,
    image_id INTEGER REFERENCES images(id) ON DELETE CASCADE,
    PRIMARY KEY (client_id, image_id)
);

-- Boot Logs table: Track all boot attempts
CREATE TABLE IF NOT EXISTS boot_logs (
    id SERIAL PRIMARY KEY,
    created_at TIMESTAMP WITH TIME ZONE,
    client_id INTEGER REFERENCES clients(id) ON DELETE SET NULL,
    image_id INTEGER REFERENCES images(id) ON DELETE SET NULL,
    mac_address VARCHAR(17),
    image_name VARCHAR(255),
    success BOOLEAN,
    error_msg TEXT,
    ip_address VARCHAR(45)
);

CREATE INDEX IF NOT EXISTS idx_boot_logs_mac_address ON boot_logs(mac_address);
CREATE INDEX IF NOT EXISTS idx_boot_logs_created_at ON boot_logs(created_at);

-- Example: Add a client
-- INSERT INTO clients (mac_address, name, enabled, created_at, updated_at)
-- VALUES ('00:11:22:33:44:55', 'Lab Machine 1', true, NOW(), NOW());

-- Example: Add an image (auto-synced from filesystem)
-- INSERT INTO images (name, filename, size, enabled, public, created_at, updated_at)
-- VALUES ('ubuntu-24.04', 'ubuntu-24.04-live-server-amd64.iso', 2800000000, true, true, NOW(), NOW());

-- Example: Grant a client access to an image
-- INSERT INTO client_images (client_id, image_id)
-- SELECT
--     (SELECT id FROM clients WHERE mac_address = '00:11:22:33:44:55'),
--     (SELECT id FROM images WHERE name = 'ubuntu-24.04');

-- Example: View client permissions
-- SELECT c.mac_address, c.name, i.name AS image_name, i.filename
-- FROM clients c
-- JOIN client_images ci ON c.id = ci.client_id
-- JOIN images i ON ci.image_id = i.id
-- WHERE c.enabled = true AND i.enabled = true;

-- Example: View boot statistics
-- SELECT
--     c.mac_address,
--     c.name,
--     COUNT(bl.id) AS total_boots,
--     COUNT(CASE WHEN bl.success THEN 1 END) AS successful_boots,
--     MAX(bl.created_at) AS last_boot
-- FROM clients c
-- LEFT JOIN boot_logs bl ON c.id = bl.client_id
-- GROUP BY c.id, c.mac_address, c.name
-- ORDER BY total_boots DESC;