RelationalDesign: data_load.sql

SET search_path TO project;

TRUNCATE TABLE tenants, users, memberships, environments, env_tokens, env_settings, networks, computer_groups, computers, network_devices, computer_history_current, computer_history, computer_processes_current, computer_processes_history, network_connections_current, network_connections_history, network_services, service_availability, sysmon_events, security_alerts RESTART IDENTITY CASCADE;

-- ---------- Основни податоци ----------
INSERT INTO tenants (name, owner_email) VALUES ('test-tenant', 'admin@test.local');

INSERT INTO users (email, name, picture) VALUES ('admin@test.local', 'Admin User', '');

INSERT INTO memberships (user_id, tenant_id, role) VALUES (1, 1, 'admin');

INSERT INTO environments (tenant_id, name) VALUES (1, 'default'), (1, 'lab'), (1, 'production');

INSERT INTO env_tokens (tenant_id, env_name, token, expires_at) VALUES (1, 'default', 'ENV_TOKEN_DEFAULT_TEST', now() + interval '15 minutes'), (1, 'lab', 'ENV_TOKEN_LAB_TEST', now() + interval '15 minutes');

INSERT INTO env_settings (tenant_id, env_name, save_process_history, save_metrics_history, save_network_history) VALUES (1, 'default', false, false, false), (1, 'lab', true, true, true), (1, 'production', false, true, false);

-- ---------- Мрежна инфраструктура и организациска структура ----------
INSERT INTO networks (tenant_id, env_name, name, cidr, gateway_ip, vlan) VALUES (1, 'default', 'Office-LAN', '192.168.1.0/24', '192.168.1.1', 10), (1, 'lab', 'Lab-Net', '192.168.50.0/24', '192.168.50.1', 50);

INSERT INTO computer_groups (tenant_id, name, description) VALUES (1, 'IT Department', 'Glaven IT oddel'), (1, 'Servers', 'Serveri vo IT oddelot'), (1, 'Office', 'Kancelariski masini');

UPDATE computer_groups SET parent_id = (SELECT id FROM computer_groups WHERE tenant_id=1 AND name='IT Department') WHERE tenant_id=1 AND name='Servers';

INSERT INTO computers (tenant_id, env_name, name, "user", ip, os, first_seen, last_seen, sysmon_available) VALUES (1, 'default', 'PC-ADMIN', 'admin', '192.168.1.10', 'Windows 11 Pro', now() - interval '2 days', now(), true), (1, 'lab', 'DEV-LAPTOP', 'developer', '192.168.50.20', 'Ubuntu 22.04', now() - interval '1 day', now(), true), (1, 'production', 'OFFICE-PC', 'office_user', '192.168.1.30', 'Windows 10', now() - interval '5 hours', now(), false);

UPDATE computers SET network_id=(SELECT id FROM networks WHERE tenant_id=1 AND name='Office-LAN'), group_id=(SELECT id FROM computer_groups WHERE tenant_id=1 AND name='Servers') WHERE tenant_id=1 AND name='PC-ADMIN';
UPDATE computers SET network_id=(SELECT id FROM networks WHERE tenant_id=1 AND name='Lab-Net'), group_id=(SELECT id FROM computer_groups WHERE tenant_id=1 AND name='IT Department') WHERE tenant_id=1 AND name='DEV-LAPTOP';
UPDATE computers SET network_id=(SELECT id FROM networks WHERE tenant_id=1 AND name='Office-LAN'), group_id=(SELECT id FROM computer_groups WHERE tenant_id=1 AND name='Office') WHERE tenant_id=1 AND name='OFFICE-PC';

INSERT INTO network_devices (network_id, name, device_type, ip, mac, status) VALUES ((SELECT id FROM networks WHERE tenant_id=1 AND name='Office-LAN'), 'Core-Router', 'router', '192.168.1.1', 'AA:BB:CC:00:11:22', 'up'), ((SELECT id FROM networks WHERE tenant_id=1 AND name='Office-LAN'), 'Access-Switch-1', 'switch', '192.168.1.2', 'AA:BB:CC:00:11:33', 'up'), ((SELECT id FROM networks WHERE tenant_id=1 AND name='Lab-Net'), 'Lab-AP', 'ap', '192.168.50.2', 'AA:BB:CC:00:22:44', 'down');

-- ---------- Следење: тековна состојба + историја ----------
INSERT INTO computer_history_current (computer_id, cpu_usage, ram_usage, disk_usage, network_sent_mb, network_recv_mb, timestamp) VALUES ((SELECT id FROM computers WHERE tenant_id=1 AND name='PC-ADMIN'), 42.0, 63.1, 71.0, 130.0, 102.3, now() - interval '10 minutes'), ((SELECT id FROM computers WHERE tenant_id=1 AND name='DEV-LAPTOP'), 60.3, 72.8, 66.0, 220.1, 190.0, now() - interval '5 minutes');

INSERT INTO computer_history (computer_id, cpu_usage, ram_usage, disk_usage, network_sent_mb, network_recv_mb, timestamp) VALUES ((SELECT id FROM computers WHERE tenant_id=1 AND name='DEV-LAPTOP'), 55.0, 70.4, 65.3, 210.7, 180.9, now() - interval '20 minutes'), ((SELECT id FROM computers WHERE tenant_id=1 AND name='DEV-LAPTOP'), 60.3, 72.8, 66.0, 220.1, 190.0, now() - interval '5 minutes');

INSERT INTO computer_processes_current (computer_id, pid, name, cpu_percent, memory_mb, username, cmdline, timestamp) VALUES ((SELECT id FROM computers WHERE tenant_id=1 AND name='PC-ADMIN'), 1234, 'chrome.exe', 12.5, 350.2, 'admin', E'C:\\Program Files\\Google\\Chrome\\chrome.exe', now() - interval '15 minutes'), ((SELECT id FROM computers WHERE tenant_id=1 AND name='PC-ADMIN'), 5678, 'powershell.exe', 5.2, 120.0, 'admin', 'powershell -ExecutionPolicy Bypass', now() - interval '10 minutes'), ((SELECT id FROM computers WHERE tenant_id=1 AND name='DEV-LAPTOP'), 2222, 'python3', 30.8, 250.4, 'developer', 'python3 client.py', now() - interval '8 minutes');

INSERT INTO computer_processes_history (computer_id, pid, name, cpu_percent, memory_mb, username, cmdline, timestamp) VALUES ((SELECT id FROM computers WHERE tenant_id=1 AND name='DEV-LAPTOP'), 2222, 'python3', 30.8, 250.4, 'developer', 'python3 client.py', now() - interval '8 minutes');

INSERT INTO network_connections_current (computer_id, pid, local_address, remote_address, status, process_name, timestamp) VALUES ((SELECT id FROM computers WHERE tenant_id=1 AND name='PC-ADMIN'), 1234, '192.168.1.10:51234', '8.8.8.8:443', 'ESTABLISHED', 'chrome.exe', now() - interval '10 minutes'), ((SELECT id FROM computers WHERE tenant_id=1 AND name='DEV-LAPTOP'), 2222, '192.168.50.20:40122', '34.117.59.81:80', 'ESTABLISHED', 'python3', now() - interval '6 minutes');

INSERT INTO network_connections_history (computer_id, pid, local_address, remote_address, status, process_name, timestamp) VALUES ((SELECT id FROM computers WHERE tenant_id=1 AND name='DEV-LAPTOP'), 2222, '192.168.50.20:40122', '34.117.59.81:80', 'ESTABLISHED', 'python3', now() - interval '6 minutes');

-- ---------- Мрежни сервиси и достапност ----------
INSERT INTO network_services (computer_id, service_name, port, protocol, status, last_checked) VALUES ((SELECT id FROM computers WHERE tenant_id=1 AND name='PC-ADMIN'), 'RDP', 3389, 'TCP', 'listening', now() - interval '3 minutes'), ((SELECT id FROM computers WHERE tenant_id=1 AND name='DEV-LAPTOP'), 'SSH', 22, 'TCP', 'listening', now() - interval '2 minutes'), ((SELECT id FROM computers WHERE tenant_id=1 AND name='DEV-LAPTOP'), 'HTTP', 8080, 'TCP', 'listening', now() - interval '2 minutes');

INSERT INTO service_availability (service_id, is_available, response_time_ms, timestamp) VALUES ((SELECT id FROM network_services WHERE computer_id=(SELECT id FROM computers WHERE tenant_id=1 AND name='DEV-LAPTOP') AND port=22), true, 12, now() - interval '2 minutes'), ((SELECT id FROM network_services WHERE computer_id=(SELECT id FROM computers WHERE tenant_id=1 AND name='DEV-LAPTOP') AND port=22), true, 15, now() - interval '1 minutes'), ((SELECT id FROM network_services WHERE computer_id=(SELECT id FROM computers WHERE tenant_id=1 AND name='DEV-LAPTOP') AND port=8080), false, NULL, now() - interval '1 minutes');

-- ---------- Безбедносни настани и аларми ----------
INSERT INTO sysmon_events (computer_id, event_id, event_type, message, timestamp, details) VALUES ((SELECT id FROM computers WHERE tenant_id=1 AND name='PC-ADMIN'), 1, 'Process Create', 'Process created: chrome.exe', now() - interval '12 minutes', '{"ProcessName":"chrome.exe","User":"admin"}'), ((SELECT id FROM computers WHERE tenant_id=1 AND name='PC-ADMIN'), 3, 'Network Connection', 'Outbound connection established', now() - interval '11 minutes', '{"DestinationIp":"8.8.8.8","DestinationPort":443}'), ((SELECT id FROM computers WHERE tenant_id=1 AND name='DEV-LAPTOP'), 1, 'Process Create', 'Process created: python3', now() - interval '7 minutes', '{"ProcessName":"python3","User":"developer"}');

INSERT INTO security_alerts (computer_id, alert_type, severity, description, timestamp, resolved) VALUES ((SELECT id FROM computers WHERE tenant_id=1 AND name='PC-ADMIN'), 'Suspicious Network Activity', 'HIGH', 'Multiple outbound connections detected', now() - interval '9 minutes', false), ((SELECT id FROM computers WHERE tenant_id=1 AND name='DEV-LAPTOP'), 'High CPU Usage', 'MEDIUM', 'CPU usage exceeded 60%', now() - interval '5 minutes', true);