-- BFD (Bidirectional Forwarding Detection) monitoring support

DROP TABLE IF EXISTS `bfd_sessions`;
CREATE TABLE `bfd_sessions` (
  `bfd_session_id` int NOT NULL AUTO_INCREMENT,
  `device_id` int NOT NULL,
  `bfd_index` varchar(64) COLLATE utf8mb3_unicode_ci NOT NULL,
  `bfd_uid` varchar(128) COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `bfd_semantic_key` varchar(255) COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `bfd_session_type` enum('singleHop','multiHop') COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'singleHop',
  `bfd_local_discriminator` int unsigned NOT NULL,
  `bfd_remote_discriminator` int unsigned DEFAULT NULL,
  `bfd_local_address` varchar(64) COLLATE utf8mb3_unicode_ci NOT NULL,
  `bfd_remote_address` varchar(64) COLLATE utf8mb3_unicode_ci NOT NULL,
  `bfd_address_type` enum('ipv4','ipv6') COLLATE utf8mb3_unicode_ci NOT NULL,
  `bfd_local_port` int DEFAULT NULL,
  `bfd_remote_port` int DEFAULT NULL,
  `bfd_vrf_name` varchar(128) COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `bfd_session_name` varchar(255) COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `bfd_admin_status` enum('enabled','disabled','adminDown') COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'enabled',
  `bfd_oper_status` enum('up','down','adminDown','init','failing') COLLATE utf8mb3_unicode_ci NOT NULL DEFAULT 'down',
  `bfd_diagnostic` varchar(64) COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `bfd_remote_heard` tinyint(1) NOT NULL DEFAULT '0',
  `bfd_desired_min_tx` int unsigned DEFAULT NULL,
  `bfd_required_min_rx` int unsigned DEFAULT NULL,
  `bfd_remote_min_rx` int unsigned DEFAULT NULL,
  `bfd_negotiated_tx` int unsigned DEFAULT NULL,
  `bfd_negotiated_rx` int unsigned DEFAULT NULL,
  `bfd_detect_multiplier` int unsigned DEFAULT NULL,
  `bfd_remote_detect_multiplier` int unsigned DEFAULT NULL,
  `bfd_echo_enabled` tinyint(1) NOT NULL DEFAULT '0',
  `bfd_echo_interval` int unsigned DEFAULT NULL,
  `bfd_auth_type` varchar(32) COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `bfd_last_up` timestamp NULL DEFAULT NULL,
  `bfd_last_down` timestamp NULL DEFAULT NULL,
  `bfd_last_diag_change` timestamp NULL DEFAULT NULL,
  `bfd_session_up_count` int unsigned DEFAULT '0',
  `bfd_session_down_count` int unsigned DEFAULT NULL,
  `bfd_session_change_count` int unsigned DEFAULT NULL,
  `bfd_session_perf_pkts_in` bigint unsigned DEFAULT '0',
  `bfd_session_perf_pkts_out` bigint unsigned DEFAULT '0',
  `bfd_session_perf_pkts_drop` bigint unsigned DEFAULT '0',
  `bfd_poll_enabled` tinyint(1) NOT NULL DEFAULT '1',
  `bfd_last_polled` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `first_seen` datetime DEFAULT NULL,
  `last_seen` datetime DEFAULT NULL,
  `last_state` varchar(16) COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `last_state_change` datetime DEFAULT NULL,
  `flaps` int NOT NULL DEFAULT '0',
  `peer_device_id` int DEFAULT NULL,
  `peer_port_id` int DEFAULT NULL,
  `vrf_id` int DEFAULT NULL,
  `bfd_ignored` tinyint(1) NOT NULL DEFAULT '0',
  `bfd_deleted` tinyint(1) NOT NULL DEFAULT '0',
  `transition_count` int NOT NULL DEFAULT '0',
  `transition_window_start` datetime DEFAULT NULL,
  `last_flap_event` datetime DEFAULT NULL,
  PRIMARY KEY (`bfd_session_id`),
  UNIQUE KEY `device_index` (`device_id`,`bfd_index`),
  KEY `device_id` (`device_id`),
  KEY `bfd_oper_status` (`bfd_oper_status`),
  KEY `bfd_remote_address` (`bfd_remote_address`),
  KEY `last_seen_idx` (`last_seen`),
  KEY `peer_device_idx` (`peer_device_id`),
  KEY `peer_port_idx` (`peer_port_id`),
  KEY `transition_window_start_idx` (`transition_window_start`),
  KEY `diag_idx` (`bfd_diagnostic`),
  KEY `type_idx` (`bfd_session_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

DROP TABLE IF EXISTS `bfd_neighbors`;
CREATE TABLE `bfd_neighbors` (
  `bfd_neighbor_id` int NOT NULL AUTO_INCREMENT,
  `device_id` int NOT NULL,
  `port_id` int DEFAULT NULL,
  `bfd_session_id` int NOT NULL,
  `remote_device_id` int DEFAULT NULL,
  `remote_port_id` int DEFAULT NULL,
  PRIMARY KEY (`bfd_neighbor_id`),
  UNIQUE KEY `bfd_session_id` (`bfd_session_id`),
  KEY `device_id` (`device_id`),
  KEY `remote_device_id` (`remote_device_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

DROP TABLE IF EXISTS `bfd_vendor_data`;
CREATE TABLE `bfd_vendor_data` (
  `bfd_vendor_id` int NOT NULL AUTO_INCREMENT,
  `bfd_session_id` int NOT NULL,
  `vendor` varchar(32) COLLATE utf8mb3_unicode_ci NOT NULL,
  `attribute` varchar(64) COLLATE utf8mb3_unicode_ci NOT NULL,
  `value` text COLLATE utf8mb3_unicode_ci,
  PRIMARY KEY (`bfd_vendor_id`),
  UNIQUE KEY `session_vendor_attr` (`bfd_session_id`,`vendor`,`attribute`),
  KEY `bfd_session_id` (`bfd_session_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;
