RMM/insert.sql

-- =========================================================
-- TEST DATA FOR AGENT MANAGEMENT SYSTEM
-- PostgreSQL
-- =========================================================

-- =========================================================
-- 100 USERS
-- =========================================================

INSERT INTO users (
    username,
    password_hash,
    role
)
SELECT
    'user_' || gs,

    md5(random()::text),

    (
        ARRAY[
            'admin',
            'operator',
            'viewer'
        ]
    )[floor(random() * 3 + 1)::int]::user_role

FROM generate_series(1, 100) gs;

-- =========================================================
-- 50 AGENTS
-- =========================================================

INSERT INTO agents (
    hostname,
    token_hash,
    cert_fingerprint,
    os_info,
    status,
    last_seen
)
SELECT
    'agent-' || gs,

    md5(random()::text),

    md5(random()::text),

    jsonb_build_object(
        'os',
        (
            ARRAY[
                'Windows 11',
                'Ubuntu 22.04',
                'Debian 12',
                'CentOS 9',
                'Windows Server 2022'
            ]
        )[floor(random() * 5 + 1)::int],

        'ram_gb',
        floor(random() * 64 + 4),

        'cpu_cores',
        floor(random() * 16 + 1)
    ),

    (
        ARRAY[
            'online',
            'offline',
            'busy',
            'error'
        ]
    )[floor(random() * 4 + 1)::int]::agent_status,

    NOW() - (random() * interval '10 days')

FROM generate_series(1, 50) gs;

-- =========================================================
-- 200 COMMANDS (OPERATIONS HISTORY)
-- =========================================================

INSERT INTO commands (
    agent_id,
    issued_by,
    command_type_id,
    command,
    status,
    output,
    exit_code,
    created_at,
    completed_at
)
SELECT
    random_agent_id,
    random_user_id,
    random_command_type_id,

    (
        ARRAY[
            'whoami',
            'hostname',
            'ipconfig',
            'ifconfig',
            'ls -la',
            'df -h',
            'uptime',
            'systemctl status nginx',
            'docker ps',
            'cat /etc/os-release'
        ]
    )[floor(random() * 10 + 1)::int],

    random_status,

    (
        ARRAY[
            'Command executed successfully',
            'Permission denied',
            'Host unreachable',
            'Completed with warnings',
            'Execution timeout',
            'No output'
        ]
    )[floor(random() * 6 + 1)::int],

    floor(random() * 10)::int,

    created_time,

    CASE
        WHEN random_status IN ('completed', 'failed', 'cancelled')
        THEN created_time + (random() * interval '5 hours')
        ELSE NULL
    END

FROM (
    SELECT
        (
            SELECT id
            FROM agents
            ORDER BY random()
            LIMIT 1
        ) AS random_agent_id,

        (
            SELECT id
            FROM users
            ORDER BY random()
            LIMIT 1
        ) AS random_user_id,

        (
            SELECT id
            FROM command_types
            ORDER BY random()
            LIMIT 1
        ) AS random_command_type_id,

        (
            ARRAY[
                'pending',
                'running',
                'completed',
                'failed',
                'cancelled'
            ]
        )[floor(random() * 5 + 1)::int]::command_status AS random_status,

        NOW() - (random() * interval '30 days') AS created_time

    FROM generate_series(1, 200)
) t;

-- =========================================================
-- 80 FILE TRANSFERS
-- =========================================================

INSERT INTO file_transfers (
    agent_id,
    initiated_by,
    command_id,
    direction,
    filename,
    sha256,
    size_bytes,
    status,
    created_at
)
SELECT
    (
        SELECT id
        FROM agents
        ORDER BY random()
        LIMIT 1
    ),

    (
        SELECT id
        FROM users
        ORDER BY random()
        LIMIT 1
    ),

    (
        SELECT id
        FROM commands
        ORDER BY random()
        LIMIT 1
    ),

    (
        ARRAY[
            'upload',
            'download'
        ]
    )[floor(random() * 2 + 1)::int]::transfer_direction,

    (
        ARRAY[
            'backup.zip',
            'logs.tar.gz',
            'report.pdf',
            'config.json',
            'database.dump',
            'image.iso',
            'script.sh'
        ]
    )[floor(random() * 7 + 1)::int],

    md5(random()::text) || md5(random()::text),

    floor(random() * 1000000000),

    (
        ARRAY[
            'pending',
            'running',
            'completed',
            'failed'
        ]
    )[floor(random() * 4 + 1)::int]::command_status,

    NOW() - (random() * interval '30 days')

FROM generate_series(1, 80);

-- =========================================================
-- 150 AUDIT LOGS
-- =========================================================

INSERT INTO audit_logs (
    actor_id,
    actor_type,
    action,
    target,
    details,
    ip_address,
    created_at
)
SELECT
    (
        SELECT id
        FROM users
        ORDER BY random()
        LIMIT 1
    ),

    'user'::actor_type_enum,

    (
        ARRAY[
            'login',
            'logout',
            'command_created',
            'agent_updated',
            'file_uploaded',
            'settings_changed',
            'password_changed'
        ]
    )[floor(random() * 7 + 1)::int],

    (
        ARRAY[
            'agent',
            'command',
            'system',
            'user',
            'file_transfer'
        ]
    )[floor(random() * 5 + 1)::int],

    jsonb_build_object(
        'result',
        (
            ARRAY[
                'success',
                'failed',
                'warning'
            ]
        )[floor(random() * 3 + 1)::int],

        'session_id',
        md5(random()::text)
    ),

    (
        floor(random() * 255)::int || '.' ||
        floor(random() * 255)::int || '.' ||
        floor(random() * 255)::int || '.' ||
        floor(random() * 255)::int
    )::inet,

    NOW() - (random() * interval '30 days')

FROM generate_series(1, 150);

-- =========================================================
-- 100 HEARTBEATS
-- =========================================================

INSERT INTO agent_heartbeats (
    agent_id,
    received_at,
    metadata
)
SELECT
    (
        SELECT id
        FROM agents
        ORDER BY random()
        LIMIT 1
    ),

    NOW() - (random() * interval '30 days'),

    jsonb_build_object(
        'cpu_usage',
        floor(random() * 100),

        'ram_usage',
        floor(random() * 100),

        'disk_usage',
        floor(random() * 100)
    )

FROM generate_series(1, 100);

-- =========================================================
-- 50 USER SESSIONS
-- =========================================================

INSERT INTO user_sessions (
    user_id,
    refresh_token_hash,
    user_agent,
    ip_address,
    expires_at,
    created_at
)
SELECT
    (
        SELECT id
        FROM users
        ORDER BY random()
        LIMIT 1
    ),

    md5(random()::text),

    (
        ARRAY[
            'Chrome',
            'Firefox',
            'Edge',
            'Safari',
            'Postman'
        ]
    )[floor(random() * 5 + 1)::int],

    (
        floor(random() * 255)::int || '.' ||
        floor(random() * 255)::int || '.' ||
        floor(random() * 255)::int || '.' ||
        floor(random() * 255)::int
    )::inet,

    NOW() + (random() * interval '30 days'),

    NOW() - (random() * interval '30 days')

FROM generate_series(1, 50);

-- =========================================================
-- AGENT GROUPS
-- =========================================================

INSERT INTO agent_groups (
    name,
    description
)
VALUES
('Production', 'Production infrastructure'),
('Testing', 'Testing infrastructure'),
('Development', 'Development infrastructure'),
('Monitoring', 'Monitoring servers'),
('Backup', 'Backup infrastructure');

-- =========================================================
-- AGENT GROUP MEMBERS
-- =========================================================

INSERT INTO agent_group_members (
    agent_id,
    group_id
)
SELECT DISTINCT
    (
        SELECT id
        FROM agents
        ORDER BY random()
        LIMIT 1
    ),

    (
        SELECT id
        FROM agent_groups
        ORDER BY random()
        LIMIT 1
    )

FROM generate_series(1, 100)

ON CONFLICT DO NOTHING;