INSERT INTO network_types (type) VALUES ('ipv6');

CREATE TABLE ip_addresses (
    -- Mandatory
    ip_address_id serial PRIMARY KEY,			-- Network interface identifier
    interface_id integer REFERENCES interfaces NOT NULL,		-- Which interface
    type text REFERENCES network_types NOT NULL,	-- ipv4 or ipv6
    ip_addr text NOT NULL,   			        -- IP address
    netmask text NOT NULL,                              -- Netmask
    last_updated timestamp without time zone -- When the interface was last updated
) WITH OIDS;
CREATE INDEX ip_addresses_interface_id_idx ON ip_addresses (interface_id);

CREATE OR REPLACE VIEW interface_ip_addresses AS
SELECT interface_id,
array_accum(ip_address_id) AS ip_address_ids
FROM ip_addresses
GROUP BY interface_id;

CREATE OR REPLACE VIEW view_ip_addresses AS
SELECT
ip_addresses.ip_address_id,
ip_addresses.interface_id,
ip_addresses.type,
ip_addresses.ip_addr,
ip_addresses.netmask,
CAST(date_part('epoch', ip_addresses.last_updated) AS bigint) AS last_updated
FROM ip_addresses;

DROP VIEW view_interfaces;
-- FIXME: Why was this needed?
-- DROP VIEW tagvalue_view_interface_id_ifname;
DROP VIEW view_interface_tags;

INSERT INTO ip_addresses (interface_id, type, ip_addr, netmask)
SELECT interface_id, type, ip as ip_addr, netmask FROM interfaces;

ALTER TABLE interfaces DROP COLUMN type;
ALTER TABLE interfaces DROP COLUMN ip;
ALTER TABLE interfaces DROP COLUMN network;
ALTER TABLE interfaces DROP COLUMN broadcast;
ALTER TABLE interfaces DROP COLUMN netmask;
ALTER TABLE interfaces ADD COLUMN if_name text;

CREATE OR REPLACE VIEW view_interfaces AS
SELECT
interfaces.interface_id,
interfaces.node_id,
interfaces.is_primary,
interfaces.method,
interfaces.mac,
interfaces.bwlimit,
interfaces.hostname,
interfaces.if_name,
CAST(date_part('epoch', interfaces.last_updated) AS bigint) AS last_updated,
COALESCE((SELECT interface_tag_ids FROM interface_tags WHERE interface_tags.interface_id = interfaces.interface_id), '{}') AS interface_tag_ids,
COALESCE((SELECT ip_address_ids FROM interface_ip_addresses WHERE interface_ip_addresses.interface_id = interfaces.interface_id), '{}') AS ip_address_ids
FROM interfaces;

CREATE OR REPLACE VIEW view_interface_tags AS
SELECT
interface_tag.interface_tag_id,
interface_tag.interface_id,
tag_types.tag_type_id,
tag_types.tagname,
tag_types.description,
tag_types.category,
interface_tag.value
FROM interface_tag
INNER JOIN tag_types USING (tag_type_id)
INNER JOIN interfaces USING (interface_id);

CREATE TABLE routes (
    -- Mandatory
    route_id serial PRIMARY KEY,			-- Network interface identifier
    node_id integer REFERENCES nodes NOT NULL,          -- Which node
    subnet text NOT NULL,	                        -- Subnet route applies to
    next_hop text NOT NULL,   			        -- Next hop IP address
    interface_id integer REFERENCES interfaces NOT NULL, -- Outgoing interface
    last_updated timestamp without time zone -- When the interface was last updated
) WITH OIDS;
CREATE INDEX routes_node_id_idx ON routes (node_id);

CREATE OR REPLACE VIEW view_routes AS
SELECT
routes.route_id,
routes.node_id,
routes.subnet,
routes.next_hop,
routes.interface_id,
CAST(date_part('epoch', routes.last_updated) AS bigint) AS last_updated
FROM routes;

CREATE OR REPLACE VIEW node_routes AS
SELECT node_id,
array_accum(route_id) AS route_ids
FROM routes
GROUP BY node_id;

ALTER TABLE nodes ADD COLUMN dns text;
ALTER TABLE nodes ADD COLUMN ntp text;

-- Insert the default gateways from the primary interfaces on each node as
-- a route with subnet=0.0.0.0/0 and next_hop=gateway
INSERT INTO routes (subnet, node_id, interface_id, next_hop)
SELECT '0.0.0.0/0' as subnet, node_id, interface_id, gateway as next_hop FROM interfaces WHERE gateway is not NULL and is_primary is true;

-- Set the DNS fields in the nodes table from the DNS fields in the interfaces table
-- for primary interfaces with one dns server
UPDATE nodes SET dns = dns1 FROM interfaces
WHERE is_primary is True and nodes.node_id = interfaces.node_id and dns1 is not NULL and dns2 is NULL;

-- Set the DNS fields in the nodes table from the DNS fields in the interfaces table
-- for primary interfaces with two dns servers
UPDATE nodes SET dns = dns1 || ', ' || dns2 FROM interfaces
WHERE is_primary is True and nodes.node_id = interfaces.node_id and dns1 is not NULL and dns2 is not NULL;

-- now we can drop the gateway and dns columns from interface
ALTER TABLE interfaces DROP COLUMN gateway;
ALTER TABLE interfaces DROP COLUMN dns1;
ALTER TABLE interfaces DROP COLUMN dns2;

DROP VIEW view_nodes;
CREATE OR REPLACE VIEW view_nodes AS
SELECT
nodes.node_id,
nodes.node_type,
nodes.hostname,
nodes.site_id,
nodes.boot_state,
nodes.run_level,
nodes.deleted,
nodes.model,
nodes.boot_nonce,
nodes.version,
nodes.verified,
nodes.ssh_rsa_key,
nodes.dns,
nodes.ntp,
nodes.key,
CAST(date_part('epoch', nodes.date_created) AS bigint) AS date_created,
CAST(date_part('epoch', nodes.last_updated) AS bigint) AS last_updated,
CAST(date_part('epoch', nodes.last_contact) AS bigint) AS last_contact,
CAST(date_part('epoch', nodes.last_boot) AS bigint) AS last_boot,
CAST(date_part('epoch', nodes.last_download) AS bigint) AS last_download,
CAST(date_part('epoch', nodes.last_pcu_reboot) AS bigint) AS last_pcu_reboot,
CAST(date_part('epoch', nodes.last_pcu_confirmation) AS bigint) AS last_pcu_confirmation,
nodes.last_time_spent_online,
nodes.last_time_spent_offline,
peer_node.peer_id,
peer_node.peer_node_id,
COALESCE((SELECT interface_ids FROM node_interfaces
		 WHERE node_interfaces.node_id = nodes.node_id), '{}')
AS interface_ids,
COALESCE((SELECT nodegroup_ids FROM node_nodegroups
		 WHERE node_nodegroups.node_id = nodes.node_id), '{}')
AS nodegroup_ids,
COALESCE((SELECT slice_ids FROM node_slices
		 WHERE node_slices.node_id = nodes.node_id), '{}')
AS slice_ids,
COALESCE((SELECT slice_ids_whitelist FROM node_slices_whitelist
		 WHERE node_slices_whitelist.node_id = nodes.node_id), '{}')
AS slice_ids_whitelist,
COALESCE((SELECT pcu_ids FROM node_pcus
		 WHERE node_pcus.node_id = nodes.node_id), '{}')
AS pcu_ids,
COALESCE((SELECT ports FROM node_pcus
		 WHERE node_pcus.node_id = nodes.node_id), '{}')
AS ports,
COALESCE((SELECT conf_file_ids FROM node_conf_files
		 WHERE node_conf_files.node_id = nodes.node_id), '{}')
AS conf_file_ids,
COALESCE((SELECT node_tag_ids FROM node_tags
		 WHERE node_tags.node_id = nodes.node_id), '{}')
AS node_tag_ids,
COALESCE((SELECT route_ids FROM node_routes
		 WHERE node_routes.node_id = nodes.node_id), '{}')
AS route_ids,
node_session.session_id AS session
FROM nodes
LEFT JOIN peer_node USING (node_id)
LEFT JOIN node_session USING (node_id);

UPDATE plc_db_version SET subversion = 106;
