-- ============================================================
-- nn_fleet_routing — Dynamic node routing table
-- Replaces hardcoded WG/API IPs throughout the codebase.
-- Update this table when any node moves; no code rebuild needed.
-- ============================================================

CREATE TABLE IF NOT EXISTS nn_fleet_routing (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  node_id      VARCHAR(10)  NOT NULL,               -- '005', '007', etc.
  node_name    VARCHAR(50)  NOT NULL,               -- 'Origin', 'Email', etc.
  node_role    VARCHAR(30)  NOT NULL DEFAULT '',    -- 'origin','email','payment','shield'...
  wg_ip        VARCHAR(45)  DEFAULT NULL,           -- WireGuard internal IP
  public_ip    VARCHAR(45)  DEFAULT NULL,           -- Public IP (for reference)
  ssh_port     SMALLINT UNSIGNED DEFAULT 33022,
  api_port     SMALLINT UNSIGNED DEFAULT NULL,      -- Service API port
  service_url  VARCHAR(255) DEFAULT NULL,           -- Full service base URL e.g. http://10.x.x.x:8443
  is_reachable TINYINT(1)   NOT NULL DEFAULT 1,    -- 0 = known-down, skip
  last_seen    DATETIME     DEFAULT NULL,           -- Updated by heartbeat
  notes        VARCHAR(255) DEFAULT NULL,
  is_active    TINYINT(1)   NOT NULL DEFAULT 1,
  created_at   DATETIME     DEFAULT CURRENT_TIMESTAMP,
  updated_at   DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uk_node (node_id),
  KEY idx_role (node_role),
  KEY idx_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Seed with current fleet topology
-- Update wg_ip values once WG mesh is fully established
INSERT INTO nn_fleet_routing
  (node_id, node_name, node_role, wg_ip, public_ip, ssh_port, api_port, service_url, notes)
VALUES
  ('001', 'DNS',     'dns',     '10.69.69.5',     '80.78.19.57',     33022, NULL,  NULL,                         'Njalla Sweden — WG mesh only'),
  ('002', 'Proxy',   'proxy',   '10.69.69.3',     '107.189.23.194',  33022, NULL,  NULL,                         'Cloudzy Amsterdam — public ingress'),
  ('003', 'Filter',  'filter',  '10.69.69.4',     '80.96.113.18',    33022, 8380,  'http://10.69.69.4:8380',     'AlexHost Moldova — WAF'),
  ('004', 'Shield',  'shield',  '10.69.69.1',     '194.36.190.111',  33022, 43891, NULL,                         'Standby Amsterdam — hub node'),
  ('005', 'Origin',  'origin',  '10.69.69.2',     '93.95.226.207',   33022, 8443,  NULL,                         '1984 Iceland — main app server'),
  ('006', 'Payment', 'payment', '10.69.69.6',     '37.148.207.105',  33022, 8443,  NULL,                         'Daddy Singapore — isolated, bridge only'),
  ('007', 'Email',   'email',   '10.69.69.7',     '76.13.222.157',   33022, 8443,  'http://10.69.69.7:8443',     'Hostinger Malaysia — Postfix+Dovecot, WG mesh')
ON DUPLICATE KEY UPDATE
  wg_ip       = VALUES(wg_ip),
  public_ip   = VALUES(public_ip),
  service_url = VALUES(service_url),
  notes       = VALUES(notes),
  updated_at  = NOW();

-- View: quick lookup by role
CREATE OR REPLACE VIEW fleet_node_view AS
SELECT
  node_id, node_name, node_role,
  wg_ip, public_ip, ssh_port, api_port, service_url,
  is_reachable, last_seen, notes
FROM nn_fleet_routing
WHERE is_active = 1
ORDER BY node_id;

SELECT 'fleet_routing seeded' AS status, COUNT(*) AS nodes FROM nn_fleet_routing;
