-- 023_network_hosts.sql
-- Authoritative LAN inventory of cluster guests + hosts: id -> ip -> MAC.
-- Source of truth for router DHCP reservations and the infra_audit sanity check.
-- Pool is the whole .2-.254, so every pinned guest needs a static IP + a router
-- reservation on its MAC; this table is where we record the MAC<->IP mapping.
CREATE TABLE IF NOT EXISTS network_hosts (
  id          text PRIMARY KEY,           -- e.g. ct100, vm200, pve-z, qdevice-pi
  kind        text NOT NULL,              -- lxc | vm | pve-host | qdevice
  name        text NOT NULL,
  node        text,                       -- z | Z3 | won | -
  ip          text,
  mac         text,                       -- NULL when not yet captured (host down)
  note        text,
  created_at  timestamptz NOT NULL DEFAULT now(),
  updated_at  timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_network_hosts_ip ON network_hosts(ip);

-- Seed the current inventory (captured 2026-06-08). Idempotent: re-running keeps
-- the row but refreshes ip/mac/note so a later edit-and-migrate stays correct.
INSERT INTO network_hosts (id, kind, name, node, ip, mac, note) VALUES
  ('ct100','lxc','mediastack','z','192.168.1.230','BC:24:11:D8:2B:7F','Docker media host'),
  ('ct102','lxc','ollama','z','192.168.1.185','BC:24:11:06:89:40','Ollama (GPU)'),
  ('ct103','lxc','openwebui','z','192.168.1.231','BC:24:11:98:28:A1','Open WebUI'),
  ('ct104','lxc','bookstack','z','192.168.1.213','BC:24:11:C3:F4:0A','BookStack mirror'),
  ('ct105','lxc','gitea','z','192.168.1.223','BC:24:11:AA:2B:4E','Gitea (static, was DHCP)'),
  ('ct106','lxc','pihole','z','192.168.1.140','BC:24:11:DB:2A:39','Pi-hole DNS adblock'),
  ('ct107','lxc','iventoy','z','192.168.1.150','BC:24:11:9B:01:10','PXE (parked, donatello-vm rootfs)'),
  ('ct108','lxc','tlcapture','z','192.168.1.108','BC:24:11:6D:97:27','Farm Timelapse'),
  ('ct109','lxc','gramps','z','192.168.1.99','BC:24:11:8E:D3:58','Gramps Web'),
  ('ct110','lxc','n8n','z','192.168.1.235','BC:24:11:28:70:30','n8n'),
  ('ct111','lxc','magicmirror','z','192.168.1.224','BC:24:11:6C:D4:E6','MagicMirror (static, was DHCP .27)'),
  ('ct112','lxc','obd2','z','192.168.1.225','BC:24:11:E7:D8:BF','OBD2 telemetry (static, was DHCP .28)'),
  ('ct300','lxc','claude','z','192.168.1.212','BC:24:11:9E:AA:73','Claude Code workspace'),
  ('ct301','lxc','void1','z','192.168.1.11','BC:24:11:4D:B7:CC','Void 1.x legacy'),
  ('ct310','lxc','void2-db','z','192.168.1.215','BC:24:11:49:C6:29','Void 2.0 Postgres'),
  ('ct311','lxc','void2-app','z','192.168.1.216','BC:24:11:9B:B7:3A','Void 2.0 app'),
  ('vm117','vm','Pterodactyl-Deb','z','192.168.1.247','BC:24:11:37:C1:F7','Game panel (static, in-guest)'),
  ('vm200','vm','OpenClaw','z','192.168.1.183','BC:24:11:29:84:B9','OpenClaw agent (static, in-guest)'),
  ('pve-z','pve-host','z','z','192.168.1.124','00:E0:4C:0F:36:00','Cluster node 1 (GPU)'),
  ('pve-z3','pve-host','Z3','Z3','192.168.1.125','6C:0B:5E:78:1C:93','Cluster node 2 (HA target)'),
  ('qdevice-pi','qdevice','retropie','-','192.168.1.254','D8:3A:DD:22:C4:21','QDevice corosync-qnetd — reserve this MAC to .254')
ON CONFLICT (id) DO UPDATE SET
  kind = EXCLUDED.kind, name = EXCLUDED.name, node = EXCLUDED.node,
  ip = EXCLUDED.ip, mac = EXCLUDED.mac, note = EXCLUDED.note, updated_at = now();