-- Extend nn_fleet_routing with topology management fields
ALTER TABLE nn_fleet_routing
    ADD COLUMN IF NOT EXISTS location_city       VARCHAR(80)  DEFAULT NULL AFTER notes,
    ADD COLUMN IF NOT EXISTS location_country    CHAR(2)      DEFAULT NULL AFTER location_city,
    ADD COLUMN IF NOT EXISTS datacenter          VARCHAR(80)  DEFAULT NULL AFTER location_country,
    ADD COLUMN IF NOT EXISTS provider            VARCHAR(60)  DEFAULT NULL AFTER datacenter,
    ADD COLUMN IF NOT EXISTS monthly_cost_cad    DECIMAL(8,2) DEFAULT NULL AFTER provider,
    ADD COLUMN IF NOT EXISTS payment_method      VARCHAR(60)  DEFAULT NULL AFTER monthly_cost_cad,
    ADD COLUMN IF NOT EXISTS renewal_date        DATE         DEFAULT NULL AFTER payment_method,
    ADD COLUMN IF NOT EXISTS cpu_cores           TINYINT      DEFAULT NULL AFTER renewal_date,
    ADD COLUMN IF NOT EXISTS ram_gb              SMALLINT     DEFAULT NULL AFTER cpu_cores,
    ADD COLUMN IF NOT EXISTS disk_gb             SMALLINT     DEFAULT NULL AFTER ram_gb,
    ADD COLUMN IF NOT EXISTS bandwidth_tb        SMALLINT     DEFAULT NULL AFTER disk_gb,
    ADD COLUMN IF NOT EXISTS os                  VARCHAR(60)  DEFAULT NULL AFTER bandwidth_tb,
    ADD COLUMN IF NOT EXISTS standby_node_id     INT UNSIGNED DEFAULT NULL AFTER os,
    ADD COLUMN IF NOT EXISTS standby_ready       TINYINT(1)   DEFAULT 0 AFTER standby_node_id,
    ADD COLUMN IF NOT EXISTS wg_peers            VARCHAR(255) DEFAULT NULL AFTER standby_ready,
    ADD COLUMN IF NOT EXISTS canvas_x            SMALLINT     DEFAULT 0  AFTER wg_peers,
    ADD COLUMN IF NOT EXISTS canvas_y            SMALLINT     DEFAULT 0  AFTER canvas_x;

-- Seed topology data for the 7 known nodes
UPDATE nn_fleet_routing SET
    location_city='New York', location_country='US', datacenter='Vultr NYC',
    provider='Vultr', monthly_cost_cad=7.00, payment_method='Crypto',
    cpu_cores=1, ram_gb=1, disk_gb=25, os='Ubuntu 24.04',
    wg_peers='Origin,Shield', canvas_x=120, canvas_y=80
WHERE node_role='dns';

UPDATE nn_fleet_routing SET
    location_city='Amsterdam', location_country='NL', datacenter='Frantech',
    provider='BuyVM', monthly_cost_cad=15.00, payment_method='Crypto',
    cpu_cores=2, ram_gb=2, disk_gb=40, os='Debian 12',
    wg_peers='Origin,Filter,DNS', canvas_x=300, canvas_y=160
WHERE node_role='proxy';

UPDATE nn_fleet_routing SET
    location_city='Las Vegas', location_country='US', datacenter='Frantech',
    provider='BuyVM', monthly_cost_cad=15.00, payment_method='Crypto',
    cpu_cores=2, ram_gb=2, disk_gb=40, os='Alpine Linux',
    standby_ready=1, wg_peers='Origin,Proxy,Shield', canvas_x=480, canvas_y=60
WHERE node_role='filter';

UPDATE nn_fleet_routing SET
    location_city='Toronto', location_country='CA', datacenter='OVH CA',
    provider='OVH', monthly_cost_cad=20.00, payment_method='Card',
    cpu_cores=4, ram_gb=8, disk_gb=80, os='Debian 12',
    wg_peers='Filter,DNS,Payment,Email', canvas_x=300, canvas_y=300
WHERE node_role='shield';

UPDATE nn_fleet_routing SET
    location_city='Nuremberg', location_country='DE', datacenter='Hetzner',
    provider='Hetzner', monthly_cost_cad=25.00, payment_method='Crypto',
    cpu_cores=4, ram_gb=8, disk_gb=160, os='AlmaLinux 9',
    wg_peers='Shield,Payment,Email', canvas_x=480, canvas_y=300
WHERE node_role='origin';

UPDATE nn_fleet_routing SET
    location_city='Toronto', location_country='CA', datacenter='OVH CA',
    provider='OVH', monthly_cost_cad=12.00, payment_method='Card',
    cpu_cores=2, ram_gb=4, disk_gb=60, os='Debian 12',
    wg_peers='Origin,Shield', canvas_x=640, canvas_y=200
WHERE node_role='payment';

UPDATE nn_fleet_routing SET
    location_city='Atlanta', location_country='US', datacenter='Vultr ATL',
    provider='Vultr', monthly_cost_cad=7.00, payment_method='Crypto',
    cpu_cores=1, ram_gb=2, disk_gb=40, os='AlmaLinux 9',
    wg_peers='DNS', canvas_x=120, canvas_y=260
WHERE node_role='email';
