init.sql

CREATE TABLE administrator (
    admin_id SERIAL PRIMARY KEY,
    last_name VARCHAR(100) NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    middle_name VARCHAR(100),
    username VARCHAR(50) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    role VARCHAR(30) NOT NULL DEFAULT 'viewer',
    is_active BOOLEAN NOT NULL DEFAULT true,
    last_login TIMESTAMPTZ,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), 
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), 
    created_by INT,
    CONSTRAINT fk_administrator_creator
        FOREIGN KEY (created_by)
        REFERENCES administrator(admin_id)
        ON DELETE SET NULL
);
CREATE TABLE performance_test (
    test_id SERIAL PRIMARY KEY,
    test_name VARCHAR(100) NOT NULL UNIQUE,
    test_description TEXT,
    unit VARCHAR(20) NOT NULL
);
CREATE TABLE agent_key (
    agent_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    secret_key VARCHAR(255) NOT NULL,
    is_active BOOLEAN NOT NULL DEFAULT true,
    created_by INT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    last_used_at TIMESTAMPTZ,
    CONSTRAINT fk_agent_key_admin 
        FOREIGN KEY (created_by) 
        REFERENCES administrator(admin_id) 
        ON DELETE SET NULL
);
CREATE TABLE computer (
    computer_id SERIAL PRIMARY KEY,
    agent_id UUID NOT NULL UNIQUE,
    inventory_number VARCHAR(50) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    location VARCHAR(100),
    status VARCHAR(20) NOT NULL DEFAULT 'active',
    ip_address INET NOT NULL, 
    mac_address MACADDR NOT NULL,
    operating_system VARCHAR(100),
    first_seen TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    last_seen TIMESTAMPTZ,
    notes TEXT,
    CONSTRAINT fk_computer_agent 
        FOREIGN KEY (agent_id) 
        REFERENCES agent_key(agent_id) 
        ON DELETE CASCADE
);
CREATE TABLE computer_metrics (
    id BIGSERIAL PRIMARY KEY,
    computer_id INT NOT NULL REFERENCES computer(computer_id) ON DELETE CASCADE,
    cpu_load DECIMAL(5, 2),
    cpu_temp DECIMAL(5, 2),
    gpu_load DECIMAL(5, 2),
    gpu_temp DECIMAL(5, 2),
    ram_used_gb DECIMAL(8, 2),
    fan_speed INT,
    network_send_speed DECIMAL(12, 2),
    network_receive_speed DECIMAL(12, 2),
    disk_free_percent DECIMAL(5, 2),
    uptime_seconds BIGINT,
    recorded_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_metrics_comp_time ON computer_metrics (computer_id, recorded_at DESC);

CREATE TABLE log_entry (
    log_id BIGSERIAL PRIMARY KEY,
    computer_id INTEGER NOT NULL REFERENCES computer(computer_id) ON DELETE CASCADE,
    event_time TIMESTAMPTZ NOT NULL,
    received_time TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    log_type VARCHAR(30) NOT NULL,
    event_source VARCHAR(100),
    event_id INT,
    level VARCHAR(20) NOT NULL,
    message TEXT
);

CREATE TABLE test_result (
    result_id BIGSERIAL PRIMARY KEY,
    computer_id INT NOT NULL REFERENCES computer(computer_id) ON DELETE CASCADE,
    test_id INT NOT NULL REFERENCES performance_test(test_id) ON DELETE CASCADE,
    executed_at TIMESTAMPTZ NOT NULL,
    value DECIMAL(12,2) NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'completed',
    error_message TEXT,
    duration_ms INT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE computer_configuration (
    id SERIAL PRIMARY KEY,
    computer_id INT NOT NULL REFERENCES computer(computer_id) ON DELETE CASCADE,
    is_laptop BOOLEAN NOT NULL DEFAULT FALSE,
    cpu_model VARCHAR(150),
    cpu_cores SMALLINT,
    cpu_frequency_mhz DECIMAL(10,2),
    ram_part_numbers TEXT,
    ram_total_gb DECIMAL(8,2),
    ram_type VARCHAR(50),
    ram_slots_total SMALLINT,
    ram_slots_used SMALLINT,
    disk_model VARCHAR(150),
    disk_type VARCHAR(50),
    disk_capacity_gb DECIMAL(12,2),
    gpu_integrated VARCHAR(150),
    gpu_discrete VARCHAR(150),
    integrated_gpu_memory_gb DECIMAL(8,2),
    discrete_gpu_memory_gb DECIMAL(8,2),
    network_adapter VARCHAR(150),
    motherboard_model VARCHAR(150),
    motherboard_manufacturer VARCHAR(150),
    bios_version VARCHAR(100),
    bios_release_date VARCHAR(50),
    laptop_model VARCHAR(150),
    serial_number VARCHAR(100),
    battery_health DECIMAL(5,2),
    recorded_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);