-- IS-IS / SPBM consolidated schema
--
-- This replaces the iterative development files update/529.sql..update/532.sql.
-- The number 533 is only a proposal and should be renumbered by upstream if needed.
-- All CREATE statements are IF NOT EXISTS so the file is safe on systems where
-- the earlier test tables already exist.

-- -----------------------------------------------------------------------------
-- Former update/529.sql
-- -----------------------------------------------------------------------------

-- IS-IS / SPBM routing support

CREATE TABLE IF NOT EXISTS `isis_instances` (
  `isis_instance_id` int(11) NOT NULL AUTO_INCREMENT,
  `device_id` int(11) NOT NULL,
  `isis_mib` varchar(64) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL DEFAULT 'ISIS-MIB',
  `isis_instance_index` varchar(64) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL DEFAULT '0',
  `isis_sys_id` varchar(64) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `isis_sys_name` varchar(128) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `isis_admin_state` varchar(32) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `isis_level_type` varchar(32) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `isis_version` varchar(32) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `isis_lsp_count` int(11) DEFAULT NULL,
  `isis_max_paths` int(11) DEFAULT NULL,
  `isis_max_lsp_gen_interval` int(11) DEFAULT NULL,
  `isis_sys_wait_time` int(11) DEFAULT NULL,
  `isis_sys_max_age` int(11) DEFAULT NULL,
  `isis_lsp_buffer_size` int(11) DEFAULT NULL,
  `isis_protocols` varchar(64) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `isis_notification_enabled` varchar(16) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `isis_last_enabled` varchar(64) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `isis_last_spf` varchar(64) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `isis_polled` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`isis_instance_id`),
  UNIQUE KEY `device_mib_instance` (`device_id`,`isis_mib`,`isis_instance_index`),
  KEY `isis_device_state` (`device_id`,`isis_admin_state`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

CREATE TABLE IF NOT EXISTS `isis_areas` (
  `isis_area_id` int(11) NOT NULL AUTO_INCREMENT,
  `device_id` int(11) NOT NULL,
  `isis_mib` varchar(64) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL DEFAULT 'ISIS-MIB',
  `isis_area_index` varchar(128) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
  `isis_area_address` varchar(128) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `isis_area_type` varchar(32) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `isis_area_status` varchar(32) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `isis_area_polled` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`isis_area_id`),
  UNIQUE KEY `device_mib_area` (`device_id`,`isis_mib`,`isis_area_index`),
  KEY `isis_area_device` (`device_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

CREATE TABLE IF NOT EXISTS `isis_ports` (
  `isis_port_id` int(11) NOT NULL AUTO_INCREMENT,
  `device_id` int(11) NOT NULL,
  `port_id` int(11) NOT NULL DEFAULT 0,
  `isis_mib` varchar(64) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL DEFAULT 'ISIS-MIB',
  `isis_circ_index` varchar(64) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
  `isis_circ_ifIndex` int(11) NOT NULL DEFAULT 0,
  `isis_circ_admin_state` varchar(32) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `isis_circ_oper_state` varchar(32) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `isis_circ_type` varchar(32) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `isis_circ_level_type` varchar(32) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `isis_circ_passive` varchar(32) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `isis_circ_metric` int(11) DEFAULT NULL,
  `isis_circ_hello_interval` int(11) DEFAULT NULL,
  `isis_circ_hello_multiplier` int(11) DEFAULT NULL,
  `isis_circ_ext_domain` varchar(32) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `isis_circ_mesh_group` varchar(32) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `isis_circ_small_hellos` varchar(32) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `isis_circ_last_uptime` varchar(64) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `isis_circ_3way_enabled` varchar(32) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `isis_circ_extended_circ_id` int(11) DEFAULT NULL,
  `isis_circ_polled` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`isis_port_id`),
  UNIQUE KEY `device_mib_circuit` (`device_id`,`isis_mib`,`isis_circ_index`),
  KEY `isis_port_device` (`device_id`,`port_id`),
  KEY `isis_port_state` (`device_id`,`isis_circ_admin_state`,`isis_circ_oper_state`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

CREATE TABLE IF NOT EXISTS `isis_adjacencies` (
  `isis_adjacency_id` int(11) NOT NULL AUTO_INCREMENT,
  `device_id` int(11) NOT NULL,
  `port_id` int(11) NOT NULL DEFAULT 0,
  `isis_mib` varchar(64) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL DEFAULT 'ISIS-MIB',
  `isis_adj_index` varchar(128) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
  `isis_circ_index` varchar(64) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `isis_circ_ifIndex` int(11) NOT NULL DEFAULT 0,
  `isis_adj_local_index` varchar(64) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `isis_adj_state` varchar(32) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `isis_adj_3way_state` varchar(32) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `isis_adj_neigh_sysid` varchar(64) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `isis_adj_neigh_name` varchar(128) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `isis_adj_neigh_snpa` varchar(64) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `isis_adj_neigh_type` varchar(32) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `isis_adj_usage` varchar(32) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `isis_adj_hold_timer` int(11) DEFAULT NULL,
  `isis_adj_hold_remaining` int(11) DEFAULT NULL,
  `isis_adj_priority` int(11) DEFAULT NULL,
  `isis_adj_uptime` varchar(64) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `isis_adj_ext_circuit_id` int(11) DEFAULT NULL,
  `isis_adj_restart_status` varchar(32) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `isis_adj_restart_support` varchar(16) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `isis_adj_restart_suppressed` varchar(16) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `isis_adj_polled` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`isis_adjacency_id`),
  UNIQUE KEY `device_mib_adjacency` (`device_id`,`isis_mib`,`isis_adj_index`),
  KEY `isis_adj_device` (`device_id`,`port_id`),
  KEY `isis_adj_state` (`device_id`,`isis_adj_state`),
  KEY `isis_adj_neigh_sysid` (`isis_adj_neigh_sysid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;


-- -----------------------------------------------------------------------------
-- Former update/530.sql
-- -----------------------------------------------------------------------------

-- IS-IS alert-checker status table for existing installations
-- This intentionally does not alter Observium's own legacy isis_instances table.

CREATE TABLE IF NOT EXISTS `isis_status` (
  `isis_status_id` int(11) NOT NULL AUTO_INCREMENT,
  `device_id` int(11) NOT NULL,
  `isis_mib` varchar(64) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL DEFAULT 'ISIS-MIB',
  `isis_instance_index` varchar(64) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL DEFAULT '0',
  `isis_sys_id` varchar(64) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `isis_sys_name` varchar(128) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `isis_admin_state` varchar(32) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `isis_level_type` varchar(32) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `isis_lsp_count` int(11) DEFAULT NULL,
  `isis_areas` int(11) NOT NULL DEFAULT 0,
  `isis_ports` int(11) NOT NULL DEFAULT 0,
  `isis_adjacencies` int(11) NOT NULL DEFAULT 0,
  `isis_adjacencies_up` int(11) NOT NULL DEFAULT 0,
  `isis_adjacencies_down` int(11) NOT NULL DEFAULT 0,
  `isis_polled` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`isis_status_id`),
  UNIQUE KEY `device_mib_instance` (`device_id`,`isis_mib`,`isis_instance_index`),
  KEY `isis_status_device` (`device_id`,`isis_admin_state`),
  KEY `isis_status_adj` (`device_id`,`isis_adjacencies`,`isis_adjacencies_up`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;


-- -----------------------------------------------------------------------------
-- Former update/531.sql
-- -----------------------------------------------------------------------------

-- IS-IS discovered/fabric nodes table.

CREATE TABLE IF NOT EXISTS `isis_fabric_nodes` (
  `isis_fabric_node_id` int(11) NOT NULL AUTO_INCREMENT,
  `device_id` int(11) NOT NULL,
  `port_id` int(11) NOT NULL DEFAULT 0,
  `isis_mib` varchar(64) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL DEFAULT 'ISIS-MIB',
  `isis_node_key` varchar(128) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,
  `isis_node_sysid` varchar(64) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `isis_node_name` varchar(128) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `isis_node_source` varchar(64) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL,
  `isis_node_count` int(11) NOT NULL DEFAULT 0,
  `isis_node_polled` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`isis_fabric_node_id`),
  UNIQUE KEY `device_mib_node` (`device_id`,`isis_mib`,`isis_node_key`),
  KEY `isis_node_device` (`device_id`,`port_id`),
  KEY `isis_node_sysid` (`isis_node_sysid`),
  KEY `isis_node_name` (`isis_node_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;


-- -----------------------------------------------------------------------------
-- Former update/532.sql
-- -----------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `isis_isids` (
  `isis_isid_id` int(11) NOT NULL AUTO_INCREMENT,
  `device_id` int(11) NOT NULL,
  `port_id` int(11) NOT NULL DEFAULT 0,
  `isis_mib` varchar(32) NOT NULL,
  `isid_key` varchar(128) NOT NULL,
  `isid_id` int(11) NOT NULL,
  `isid_name` varchar(128) DEFAULT NULL,
  `isid_type` varchar(64) DEFAULT NULL,
  `isid_vlan` int(11) DEFAULT NULL,
  `isid_vlan_name` varchar(128) DEFAULT NULL,
  `isid_bvlan` int(11) DEFAULT NULL,
  `isid_origin` varchar(64) DEFAULT NULL,
  `isid_source` varchar(64) DEFAULT NULL,
  `isid_ifindexes` text DEFAULT NULL,
  `isid_ports` text DEFAULT NULL,
  `isid_mlt` varchar(128) DEFAULT NULL,
  `isid_polled` int(11) NOT NULL,
  PRIMARY KEY (`isis_isid_id`),
  UNIQUE KEY `device_isid_key` (`device_id`,`isid_key`),
  KEY `device_id` (`device_id`),
  KEY `isid_id` (`isid_id`),
  KEY `isis_mib` (`isis_mib`),
  KEY `port_id` (`port_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

