-- dev_seed.sql (SQLite) — 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 OR IGNORE INTO networks (name, cidr) VALUES ('LAN',       '192.168.1.0/24');
INSERT OR IGNORE INTO networks (name, cidr) VALUES ('DMZ',       '192.168.10.0/24');
INSERT OR IGNORE INTO networks (name, cidr) VALUES ('Corporate', '10.0.0.0/8');
INSERT OR IGNORE INTO networks (name, cidr) VALUES ('VPN',       '172.16.0.0/16');

-- ── Hosts ─────────────────────────────────────────────────────────────────────
-- Hosts have no UNIQUE constraint, so we guard each insert with WHERE NOT EXISTS.
-- Network IDs are resolved by subquery on cidr for portability.

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

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

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

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

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

INSERT OR IGNORE 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');

-- ── Host ports ────────────────────────────────────────────────────────────────
-- INSERT OR IGNORE is safe: host_ports has a composite PRIMARY KEY (host_id, port_number).
-- Host IDs are resolved by subquery on (name, ip) to stay independent of auto-increment values.

-- gateway: SSH, DNS, HTTP (admin UI), HTTPS (admin UI)
INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 22  FROM hosts WHERE name = 'gateway'        AND ip = '192.168.1.1';
INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 53  FROM hosts WHERE name = 'gateway'        AND ip = '192.168.1.1';
INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 80  FROM hosts WHERE name = 'gateway'        AND ip = '192.168.1.1';
INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 443 FROM hosts WHERE name = 'gateway'        AND ip = '192.168.1.1';

-- workstations: SSH, RDP
INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 22   FROM hosts WHERE name = 'workstation-01' AND ip = '192.168.1.10';
INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 3389 FROM hosts WHERE name = 'workstation-01' AND ip = '192.168.1.10';
INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 22   FROM hosts WHERE name = 'workstation-02' AND ip = '192.168.1.11';
INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 3389 FROM hosts WHERE name = 'workstation-02' AND ip = '192.168.1.11';
INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 22   FROM hosts WHERE name = 'workstation-03' AND ip = '192.168.1.12';
INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 3389 FROM hosts WHERE name = 'workstation-03' AND ip = '192.168.1.12';

-- nas-01: SSH, HTTP (web UI), HTTPS, SMB, NFS
INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 22   FROM hosts WHERE name = 'nas-01'         AND ip = '192.168.1.20';
INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 80   FROM hosts WHERE name = 'nas-01'         AND ip = '192.168.1.20';
INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 443  FROM hosts WHERE name = 'nas-01'         AND ip = '192.168.1.20';
INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 445  FROM hosts WHERE name = 'nas-01'         AND ip = '192.168.1.20';
INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 2049 FROM hosts WHERE name = 'nas-01'         AND ip = '192.168.1.20';

-- printer-01: HTTP (web UI), HTTPS, JetDirect
INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 80   FROM hosts WHERE name = 'printer-01'     AND ip = '192.168.1.50';
INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 443  FROM hosts WHERE name = 'printer-01'     AND ip = '192.168.1.50';
INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 9100 FROM hosts WHERE name = 'printer-01'     AND ip = '192.168.1.50';

-- web servers: SSH, HTTP, HTTPS
INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 22  FROM hosts WHERE name = 'web-server-01'  AND ip = '192.168.10.10';
INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 80  FROM hosts WHERE name = 'web-server-01'  AND ip = '192.168.10.10';
INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 443 FROM hosts WHERE name = 'web-server-01'  AND ip = '192.168.10.10';
INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 22  FROM hosts WHERE name = 'web-server-02'  AND ip = '192.168.10.11';
INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 80  FROM hosts WHERE name = 'web-server-02'  AND ip = '192.168.10.11';
INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 443 FROM hosts WHERE name = 'web-server-02'  AND ip = '192.168.10.11';

-- db-server-01: SSH, PostgreSQL
INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 22   FROM hosts WHERE name = 'db-server-01'   AND ip = '192.168.10.20';
INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 5432 FROM hosts WHERE name = 'db-server-01'   AND ip = '192.168.10.20';

-- mail-server-01: SSH, SMTP, IMAP, SMTPS, SMTP Submission, IMAPS
INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 22  FROM hosts WHERE name = 'mail-server-01' AND ip = '192.168.10.30';
INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 25  FROM hosts WHERE name = 'mail-server-01' AND ip = '192.168.10.30';
INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 143 FROM hosts WHERE name = 'mail-server-01' AND ip = '192.168.10.30';
INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 465 FROM hosts WHERE name = 'mail-server-01' AND ip = '192.168.10.30';
INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 587 FROM hosts WHERE name = 'mail-server-01' AND ip = '192.168.10.30';
INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 993 FROM hosts WHERE name = 'mail-server-01' AND ip = '192.168.10.30';

-- core-switch-01: SSH, SNMP
INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 22  FROM hosts WHERE name = 'core-switch-01'   AND ip = '10.0.0.1';
INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 161 FROM hosts WHERE name = 'core-switch-01'   AND ip = '10.0.0.1';

-- monitoring-01: SSH, HTTP, HTTPS, Grafana, Prometheus
INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 22   FROM hosts WHERE name = 'monitoring-01'    AND ip = '10.0.1.10';
INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 80   FROM hosts WHERE name = 'monitoring-01'    AND ip = '10.0.1.10';
INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 443  FROM hosts WHERE name = 'monitoring-01'    AND ip = '10.0.1.10';
INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 3000 FROM hosts WHERE name = 'monitoring-01'    AND ip = '10.0.1.10';
INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 9090 FROM hosts WHERE name = 'monitoring-01'    AND ip = '10.0.1.10';

-- backup-server-01: SSH, SMB, rsync
INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 22  FROM hosts WHERE name = 'backup-server-01' AND ip = '10.0.1.20';
INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 445 FROM hosts WHERE name = 'backup-server-01' AND ip = '10.0.1.20';
INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 873 FROM hosts WHERE name = 'backup-server-01' AND ip = '10.0.1.20';

-- log-server-01: SSH, Syslog, Logstash Beats, Elasticsearch, Kibana
INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 22   FROM hosts WHERE name = 'log-server-01'    AND ip = '10.0.1.30';
INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 514  FROM hosts WHERE name = 'log-server-01'    AND ip = '10.0.1.30';
INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 5044 FROM hosts WHERE name = 'log-server-01'    AND ip = '10.0.1.30';
INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 9200 FROM hosts WHERE name = 'log-server-01'    AND ip = '10.0.1.30';
INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 5601 FROM hosts WHERE name = 'log-server-01'    AND ip = '10.0.1.30';

-- vpn-gateway-01: SSH, IKE, IPSec NAT-T, OpenVPN
INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 22   FROM hosts WHERE name = 'vpn-gateway-01' AND ip = '172.16.0.1';
INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 500  FROM hosts WHERE name = 'vpn-gateway-01' AND ip = '172.16.0.1';
INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 1194 FROM hosts WHERE name = 'vpn-gateway-01' AND ip = '172.16.0.1';
INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 4500 FROM hosts WHERE name = 'vpn-gateway-01' AND ip = '172.16.0.1';

-- vpn clients: SSH only
INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 22 FROM hosts WHERE name = 'vpn-client-01'  AND ip = '172.16.1.10';
INSERT OR IGNORE INTO host_ports (host_id, port_number) SELECT id, 22 FROM hosts WHERE name = 'vpn-client-02'  AND ip = '172.16.1.11';

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

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

-- ── Application ports ─────────────────────────────────────────────────────────
-- application_ports has a composite PRIMARY KEY, so INSERT OR IGNORE is safe.

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

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

-- WireGuard
INSERT OR IGNORE INTO application_ports (application_id, port_number) SELECT id, 51820 FROM applications WHERE name = 'WireGuard';

-- OpenVPN
INSERT OR IGNORE INTO application_ports (application_id, port_number) SELECT id, 1194 FROM applications WHERE name = 'OpenVPN';

-- PostgreSQL
INSERT OR IGNORE INTO application_ports (application_id, port_number) SELECT id, 5432 FROM applications WHERE name = 'PostgreSQL';

-- MariaDB
INSERT OR IGNORE INTO application_ports (application_id, port_number) SELECT id, 3306 FROM applications WHERE name = 'MariaDB';

-- Redis
INSERT OR IGNORE INTO application_ports (application_id, port_number) SELECT id, 6379 FROM applications WHERE name = 'Redis';

-- Grafana
INSERT OR IGNORE INTO application_ports (application_id, port_number) SELECT id, 3000 FROM applications WHERE name = 'Grafana';

-- Prometheus
INSERT OR IGNORE INTO application_ports (application_id, port_number) SELECT id, 9090 FROM applications WHERE name = 'Prometheus';

-- Elasticsearch
INSERT OR IGNORE INTO application_ports (application_id, port_number) SELECT id, 9200 FROM applications WHERE name = 'Elasticsearch';

-- Kibana
INSERT OR IGNORE INTO application_ports (application_id, port_number) SELECT id, 5601 FROM applications WHERE name = 'Kibana';

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

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

-- Home Assistant
INSERT OR IGNORE INTO application_ports (application_id, port_number) SELECT id, 8123 FROM applications WHERE name = 'Home Assistant';

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

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