-- dev_seed.sql (PostgreSQL) — development test data
--
-- Running this script is idempotent: existing rows are left untouched
-- and missing rows are inserted.  Safe to run multiple times.
--
-- Load with: cargo run --features ssr --bin seed

-- ── Networks ──────────────────────────────────────────────────────────────────

INSERT INTO networks (name, cidr) VALUES
    ('LAN',       '192.168.1.0/24'),
    ('DMZ',       '192.168.10.0/24'),
    ('Corporate', '10.0.0.0/8'),
    ('VPN',       '172.16.0.0/16')
ON CONFLICT (cidr) DO NOTHING;

-- ── Hosts ─────────────────────────────────────────────────────────────────────

-- LAN — 192.168.1.0/24
INSERT INTO hosts (name, ip, network_id)
SELECT name, ip, (SELECT id FROM networks WHERE cidr = '192.168.1.0/24')
FROM (VALUES
    ('gateway',        '192.168.1.1'),
    ('workstation-01', '192.168.1.10'),
    ('workstation-02', '192.168.1.11'),
    ('workstation-03', '192.168.1.12'),
    ('nas-01',         '192.168.1.20'),
    ('printer-01',     '192.168.1.50')
) AS t(name, ip)
WHERE NOT EXISTS (SELECT 1 FROM hosts WHERE hosts.name = t.name AND hosts.ip = t.ip);

-- DMZ — 192.168.10.0/24
INSERT INTO hosts (name, ip, network_id)
SELECT name, ip, (SELECT id FROM networks WHERE cidr = '192.168.10.0/24')
FROM (VALUES
    ('web-server-01',  '192.168.10.10'),
    ('web-server-02',  '192.168.10.11'),
    ('db-server-01',   '192.168.10.20'),
    ('mail-server-01', '192.168.10.30')
) AS t(name, ip)
WHERE NOT EXISTS (SELECT 1 FROM hosts WHERE hosts.name = t.name AND hosts.ip = t.ip);

-- Corporate backbone — 10.0.0.0/8
INSERT INTO hosts (name, ip, network_id)
SELECT name, ip, (SELECT id FROM networks WHERE cidr = '10.0.0.0/8')
FROM (VALUES
    ('core-switch-01',   '10.0.0.1'),
    ('monitoring-01',    '10.0.1.10'),
    ('backup-server-01', '10.0.1.20'),
    ('log-server-01',    '10.0.1.30')
) AS t(name, ip)
WHERE NOT EXISTS (SELECT 1 FROM hosts WHERE hosts.name = t.name AND hosts.ip = t.ip);

-- VPN — 172.16.0.0/16
INSERT INTO hosts (name, ip, network_id)
SELECT name, ip, (SELECT id FROM networks WHERE cidr = '172.16.0.0/16')
FROM (VALUES
    ('vpn-gateway-01', '172.16.0.1'),
    ('vpn-client-01',  '172.16.1.10'),
    ('vpn-client-02',  '172.16.1.11')
) AS t(name, ip)
WHERE NOT EXISTS (SELECT 1 FROM hosts WHERE hosts.name = t.name AND hosts.ip = t.ip);

-- ── Ports catalog ─────────────────────────────────────────────────────────────

INSERT INTO ports (number, description) VALUES
    (22,    'SSH'),
    (25,    'SMTP'),
    (53,    'DNS'),
    (80,    'HTTP'),
    (143,   'IMAP'),
    (161,   'SNMP'),
    (443,   'HTTPS'),
    (445,   'SMB'),
    (465,   'SMTPS'),
    (500,   'IKE / IPSec'),
    (514,   'Syslog'),
    (587,   'SMTP Submission'),
    (873,   'rsync'),
    (993,   'IMAPS'),
    (1194,  'OpenVPN'),
    (2049,  'NFS'),
    (3000,  'Grafana / Gitea'),
    (3306,  'MariaDB / MySQL'),
    (3389,  'RDP'),
    (4500,  'IPSec NAT-T'),
    (5044,  'Logstash Beats'),
    (5432,  'PostgreSQL'),
    (5601,  'Kibana'),
    (6379,  'Redis'),
    (8096,  'Jellyfin'),
    (8123,  'Home Assistant'),
    (8384,  'Syncthing UI'),
    (8920,  'Jellyfin HTTPS'),
    (9000,  'Portainer'),
    (9090,  'Prometheus'),
    (9100,  'node_exporter / JetDirect'),
    (9200,  'Elasticsearch'),
    (9443,  'Portainer HTTPS'),
    (22000, 'Syncthing'),
    (51820, 'WireGuard')
ON CONFLICT (number) DO NOTHING;

-- ── Host ports ────────────────────────────────────────────────────────────────

-- gateway: SSH, DNS, HTTP, HTTPS
INSERT INTO host_ports (host_id, port_number)
SELECT h.id, p FROM hosts h, unnest(ARRAY[22, 53, 80, 443]) p
WHERE h.name = 'gateway' AND h.ip = '192.168.1.1'
ON CONFLICT DO NOTHING;

-- workstation-01: SSH, RDP
INSERT INTO host_ports (host_id, port_number)
SELECT h.id, p FROM hosts h, unnest(ARRAY[22, 3389]) p
WHERE h.name = 'workstation-01' AND h.ip = '192.168.1.10'
ON CONFLICT DO NOTHING;

-- workstation-02: SSH, RDP
INSERT INTO host_ports (host_id, port_number)
SELECT h.id, p FROM hosts h, unnest(ARRAY[22, 3389]) p
WHERE h.name = 'workstation-02' AND h.ip = '192.168.1.11'
ON CONFLICT DO NOTHING;

-- workstation-03: SSH, RDP
INSERT INTO host_ports (host_id, port_number)
SELECT h.id, p FROM hosts h, unnest(ARRAY[22, 3389]) p
WHERE h.name = 'workstation-03' AND h.ip = '192.168.1.12'
ON CONFLICT DO NOTHING;

-- nas-01: SSH, HTTP, HTTPS, SMB, NFS
INSERT INTO host_ports (host_id, port_number)
SELECT h.id, p FROM hosts h, unnest(ARRAY[22, 80, 443, 445, 2049]) p
WHERE h.name = 'nas-01' AND h.ip = '192.168.1.20'
ON CONFLICT DO NOTHING;

-- printer-01: HTTP, HTTPS, JetDirect
INSERT INTO host_ports (host_id, port_number)
SELECT h.id, p FROM hosts h, unnest(ARRAY[80, 443, 9100]) p
WHERE h.name = 'printer-01' AND h.ip = '192.168.1.50'
ON CONFLICT DO NOTHING;

-- web-server-01: SSH, HTTP, HTTPS
INSERT INTO host_ports (host_id, port_number)
SELECT h.id, p FROM hosts h, unnest(ARRAY[22, 80, 443]) p
WHERE h.name = 'web-server-01' AND h.ip = '192.168.10.10'
ON CONFLICT DO NOTHING;

-- web-server-02: SSH, HTTP, HTTPS
INSERT INTO host_ports (host_id, port_number)
SELECT h.id, p FROM hosts h, unnest(ARRAY[22, 80, 443]) p
WHERE h.name = 'web-server-02' AND h.ip = '192.168.10.11'
ON CONFLICT DO NOTHING;

-- db-server-01: SSH, PostgreSQL
INSERT INTO host_ports (host_id, port_number)
SELECT h.id, p FROM hosts h, unnest(ARRAY[22, 5432]) p
WHERE h.name = 'db-server-01' AND h.ip = '192.168.10.20'
ON CONFLICT DO NOTHING;

-- mail-server-01: SSH, SMTP, IMAP, SMTPS, Submission, IMAPS
INSERT INTO host_ports (host_id, port_number)
SELECT h.id, p FROM hosts h, unnest(ARRAY[22, 25, 143, 465, 587, 993]) p
WHERE h.name = 'mail-server-01' AND h.ip = '192.168.10.30'
ON CONFLICT DO NOTHING;

-- core-switch-01: SSH, SNMP
INSERT INTO host_ports (host_id, port_number)
SELECT h.id, p FROM hosts h, unnest(ARRAY[22, 161]) p
WHERE h.name = 'core-switch-01' AND h.ip = '10.0.0.1'
ON CONFLICT DO NOTHING;

-- monitoring-01: SSH, HTTP, HTTPS, Grafana, Prometheus
INSERT INTO host_ports (host_id, port_number)
SELECT h.id, p FROM hosts h, unnest(ARRAY[22, 80, 443, 3000, 9090]) p
WHERE h.name = 'monitoring-01' AND h.ip = '10.0.1.10'
ON CONFLICT DO NOTHING;

-- backup-server-01: SSH, SMB, rsync
INSERT INTO host_ports (host_id, port_number)
SELECT h.id, p FROM hosts h, unnest(ARRAY[22, 445, 873]) p
WHERE h.name = 'backup-server-01' AND h.ip = '10.0.1.20'
ON CONFLICT DO NOTHING;

-- log-server-01: SSH, Syslog, Logstash Beats, Elasticsearch, Kibana
INSERT INTO host_ports (host_id, port_number)
SELECT h.id, p FROM hosts h, unnest(ARRAY[22, 514, 5044, 9200, 5601]) p
WHERE h.name = 'log-server-01' AND h.ip = '10.0.1.30'
ON CONFLICT DO NOTHING;

-- vpn-gateway-01: SSH, IKE, OpenVPN, IPSec NAT-T
INSERT INTO host_ports (host_id, port_number)
SELECT h.id, p FROM hosts h, unnest(ARRAY[22, 500, 1194, 4500]) p
WHERE h.name = 'vpn-gateway-01' AND h.ip = '172.16.0.1'
ON CONFLICT DO NOTHING;

-- vpn clients: SSH only
INSERT INTO host_ports (host_id, port_number)
SELECT h.id, 22 FROM hosts h
WHERE h.name = 'vpn-client-01' AND h.ip = '172.16.1.10'
ON CONFLICT DO NOTHING;

INSERT INTO host_ports (host_id, port_number)
SELECT h.id, 22 FROM hosts h
WHERE h.name = 'vpn-client-02' AND h.ip = '172.16.1.11'
ON CONFLICT DO NOTHING;

-- ── Applications ──────────────────────────────────────────────────────────────
-- applications has no UNIQUE constraint on name, so we use WHERE NOT EXISTS.

INSERT INTO applications (name)
SELECT v.name FROM (VALUES
    ('Nginx'),
    ('Pi-hole'),
    ('WireGuard'),
    ('OpenVPN'),
    ('PostgreSQL'),
    ('MariaDB'),
    ('Redis'),
    ('Grafana'),
    ('Prometheus'),
    ('Elasticsearch'),
    ('Kibana'),
    ('Portainer'),
    ('Jellyfin'),
    ('Home Assistant'),
    ('Syncthing'),
    ('Vaultwarden')
) AS v(name)
WHERE NOT EXISTS (SELECT 1 FROM applications WHERE name = v.name);

-- ── Application ports ─────────────────────────────────────────────────────────

-- Nginx: HTTP, HTTPS
INSERT INTO application_ports (application_id, port_number) SELECT id, 80  FROM applications WHERE name = 'Nginx' ON CONFLICT DO NOTHING;
INSERT INTO application_ports (application_id, port_number) SELECT id, 443 FROM applications WHERE name = 'Nginx' ON CONFLICT DO NOTHING;

-- Pi-hole: DNS, HTTP (admin UI), HTTPS
INSERT INTO application_ports (application_id, port_number) SELECT id, 53  FROM applications WHERE name = 'Pi-hole' ON CONFLICT DO NOTHING;
INSERT INTO application_ports (application_id, port_number) SELECT id, 80  FROM applications WHERE name = 'Pi-hole' ON CONFLICT DO NOTHING;
INSERT INTO application_ports (application_id, port_number) SELECT id, 443 FROM applications WHERE name = 'Pi-hole' ON CONFLICT DO NOTHING;

-- WireGuard
INSERT INTO application_ports (application_id, port_number) SELECT id, 51820 FROM applications WHERE name = 'WireGuard' ON CONFLICT DO NOTHING;

-- OpenVPN
INSERT INTO application_ports (application_id, port_number) SELECT id, 1194 FROM applications WHERE name = 'OpenVPN' ON CONFLICT DO NOTHING;

-- PostgreSQL
INSERT INTO application_ports (application_id, port_number) SELECT id, 5432 FROM applications WHERE name = 'PostgreSQL' ON CONFLICT DO NOTHING;

-- MariaDB
INSERT INTO application_ports (application_id, port_number) SELECT id, 3306 FROM applications WHERE name = 'MariaDB' ON CONFLICT DO NOTHING;

-- Redis
INSERT INTO application_ports (application_id, port_number) SELECT id, 6379 FROM applications WHERE name = 'Redis' ON CONFLICT DO NOTHING;

-- Grafana
INSERT INTO application_ports (application_id, port_number) SELECT id, 3000 FROM applications WHERE name = 'Grafana' ON CONFLICT DO NOTHING;

-- Prometheus
INSERT INTO application_ports (application_id, port_number) SELECT id, 9090 FROM applications WHERE name = 'Prometheus' ON CONFLICT DO NOTHING;

-- Elasticsearch
INSERT INTO application_ports (application_id, port_number) SELECT id, 9200 FROM applications WHERE name = 'Elasticsearch' ON CONFLICT DO NOTHING;

-- Kibana
INSERT INTO application_ports (application_id, port_number) SELECT id, 5601 FROM applications WHERE name = 'Kibana' ON CONFLICT DO NOTHING;

-- Portainer: HTTP, HTTPS
INSERT INTO application_ports (application_id, port_number) SELECT id, 9000 FROM applications WHERE name = 'Portainer' ON CONFLICT DO NOTHING;
INSERT INTO application_ports (application_id, port_number) SELECT id, 9443 FROM applications WHERE name = 'Portainer' ON CONFLICT DO NOTHING;

-- Jellyfin: HTTP, HTTPS
INSERT INTO application_ports (application_id, port_number) SELECT id, 8096 FROM applications WHERE name = 'Jellyfin' ON CONFLICT DO NOTHING;
INSERT INTO application_ports (application_id, port_number) SELECT id, 8920 FROM applications WHERE name = 'Jellyfin' ON CONFLICT DO NOTHING;

-- Home Assistant
INSERT INTO application_ports (application_id, port_number) SELECT id, 8123 FROM applications WHERE name = 'Home Assistant' ON CONFLICT DO NOTHING;

-- Syncthing: UI, data sync
INSERT INTO application_ports (application_id, port_number) SELECT id, 8384  FROM applications WHERE name = 'Syncthing' ON CONFLICT DO NOTHING;
INSERT INTO application_ports (application_id, port_number) SELECT id, 22000 FROM applications WHERE name = 'Syncthing' ON CONFLICT DO NOTHING;

-- Vaultwarden: HTTP, HTTPS
INSERT INTO application_ports (application_id, port_number) SELECT id, 80  FROM applications WHERE name = 'Vaultwarden' ON CONFLICT DO NOTHING;
INSERT INTO application_ports (application_id, port_number) SELECT id, 443 FROM applications WHERE name = 'Vaultwarden' ON CONFLICT DO NOTHING;