CREATE TABLE IF NOT EXISTS `stp_bridge` (`device_id` INT UNSIGNED NOT NULL, `variant` ENUM('stp','rstp','mstp','pvst','rpvst','unknown') NOT NULL DEFAULT 'unknown', `priority` INT UNSIGNED DEFAULT NULL, `designated_root` VARCHAR(32) CHARACTER SET ascii COLLATE ascii_general_ci DEFAULT NULL, `root_cost` INT UNSIGNED DEFAULT NULL, `root_port` INT UNSIGNED DEFAULT NULL, `max_age_cs` INT UNSIGNED DEFAULT NULL, `hello_time_cs` INT UNSIGNED DEFAULT NULL, `fwd_delay_cs` INT UNSIGNED DEFAULT NULL, `hold_time_cs` INT UNSIGNED DEFAULT NULL, `top_changes` BIGINT UNSIGNED DEFAULT NULL, `time_since_tc_cs` INT UNSIGNED DEFAULT NULL, `mst_region_name` VARCHAR(64) DEFAULT NULL, `mst_revision` INT UNSIGNED DEFAULT NULL, `bridge_id` VARCHAR(32) CHARACTER SET ascii COLLATE ascii_general_ci DEFAULT NULL, `domain_hash` VARCHAR(12) AS (LEFT(MD5(CONCAT(variant, COALESCE(mst_region_name, ''), COALESCE(designated_root, ''))), 12)) STORED, `last_change` TIMESTAMP NULL DEFAULT NULL, `updated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`device_id`), KEY `variant` (`variant`), KEY `bridge_id` (`bridge_id`), KEY `domain_hash` (`domain_hash`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `stp_instances` (`stp_instance_id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `device_id` INT UNSIGNED NOT NULL, `instance_key` INT UNSIGNED NOT NULL, `type` ENUM('cist','msti','pvst') NOT NULL, `name` VARCHAR(64) DEFAULT NULL, `designated_root` VARCHAR(32) DEFAULT NULL, `root_cost` INT UNSIGNED DEFAULT NULL, `root_port` INT UNSIGNED DEFAULT NULL, `bridge_priority` INT UNSIGNED DEFAULT NULL, `top_changes` BIGINT UNSIGNED DEFAULT NULL, `hello_time_cs` INT UNSIGNED NULL COMMENT 'Hello time in centiseconds', `max_age_cs` INT UNSIGNED NULL COMMENT 'Max age in centiseconds', `forward_delay_cs` INT UNSIGNED NULL COMMENT 'Forward delay in centiseconds', `updated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`stp_instance_id`), UNIQUE KEY `dev_key_type` (`device_id`,`instance_key`,`type`), KEY `device_id` (`device_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `stp_ports` (`stp_port_id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `device_id` INT UNSIGNED NOT NULL, `port_id` INT UNSIGNED NOT NULL, `stp_instance_id` INT UNSIGNED NOT NULL, `base_port` INT UNSIGNED DEFAULT NULL, `state` ENUM('disabled','blocking','listening','learning','forwarding','discarding','broken','unknown') NOT NULL DEFAULT 'unknown', `role` ENUM('unknown','disabled','alternate','backup','root','designated','master','boundary') NOT NULL DEFAULT 'unknown', `admin_edge` TINYINT(1) DEFAULT NULL, `oper_edge` TINYINT(1) DEFAULT NULL, `point2point` ENUM('unknown','true','false') DEFAULT 'unknown', `path_cost` INT UNSIGNED DEFAULT NULL, `priority` INT UNSIGNED DEFAULT NULL, `designated_bridge` VARCHAR(32) DEFAULT NULL, `designated_port` INT UNSIGNED DEFAULT NULL, `forward_transitions` INT(10) UNSIGNED DEFAULT NULL, `designated_root` VARCHAR(32) DEFAULT NULL, `inconsistent` TINYINT(1) DEFAULT NULL, `guard` SET('bpdu','root','loop','bridge-assurance','bpdu-filter') DEFAULT NULL, `bounce_count` INT UNSIGNED NULL COMMENT 'Number of state transitions', `last_bounce` TIMESTAMP NULL COMMENT 'Last state change timestamp', `updated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`stp_port_id`), UNIQUE KEY `uniq` (`device_id`,`port_id`,`stp_instance_id`), KEY `device_id` (`device_id`), KEY `stp_instance_id` (`stp_instance_id`), KEY `port_id` (`port_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS `stp_vlan_map` (`device_id` INT UNSIGNED NOT NULL, `vlan_vlan` INT UNSIGNED NOT NULL, `stp_instance_id` INT UNSIGNED NOT NULL, PRIMARY KEY (`device_id`,`vlan_vlan`), KEY `stp_instance_id` (`stp_instance_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
ALTER TABLE `stp_ports`  ADD COLUMN `admin_enable` TINYINT(1) DEFAULT NULL AFTER `base_port`;
ALTER TABLE `stp_instances`  ADD COLUMN `regional_root` VARCHAR(32) DEFAULT NULL AFTER `designated_root`,  ADD COLUMN `alt_root_cost` INT UNSIGNED DEFAULT NULL AFTER `root_cost`,  ADD COLUMN `alt_root_port` INT UNSIGNED DEFAULT NULL AFTER `root_port`;
CREATE TABLE IF NOT EXISTS `stp_port_state_cache` ( `stp_port_id` INT UNSIGNED NOT NULL,`last_state` TINYINT UNSIGNED NOT NULL, `last_change` INT UNSIGNED NOT NULL, `w60_start` INT UNSIGNED NOT NULL, `w60_transitions` SMALLINT UNSIGNED NOT NULL, PRIMARY KEY (`stp_port_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE `stp_ports` ADD COLUMN `transitions_5m`   TINYINT  UNSIGNED NOT NULL DEFAULT 0 AFTER `bounce_count`, ADD COLUMN `transitions_60m`  SMALLINT UNSIGNED NOT NULL DEFAULT 0 AFTER `transitions_5m`, ADD COLUMN `state_changed` INT UNSIGNED NOT NULL DEFAULT 0 AFTER `transitions_60m`;
ALTER TABLE `stp_ports`  ADD INDEX `idx_stp_flap_5m`  (`device_id`,`transitions_5m`),  ADD INDEX `idx_stp_flap_60m` (`device_id`,`transitions_60m`);
