-- Apply after 202609240007. Additive; existing units and events are untouched.
CREATE TABLE unit_display_links (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 source_unit_id BIGINT UNSIGNED NOT NULL,
 target_unit_id BIGINT UNSIGNED NOT NULL,
 priority INT UNSIGNED NOT NULL DEFAULT 100,
 active_from DATE NOT NULL,
 active_until DATE NULL,
 requested_by BIGINT UNSIGNED NULL,
 requested_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 accepted_by BIGINT UNSIGNED NULL,
 accepted_at DATETIME NULL,
 revoked_by BIGINT UNSIGNED NULL,
 revoked_at DATETIME NULL,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 UNIQUE KEY uq_unit_display_direction (source_unit_id,target_unit_id),
 KEY idx_unit_display_source (source_unit_id,accepted_at,revoked_at,priority),
 KEY idx_unit_display_target (target_unit_id,accepted_at,revoked_at),
 CONSTRAINT fk_unit_display_source FOREIGN KEY (source_unit_id) REFERENCES units(id) ON DELETE CASCADE,
 CONSTRAINT fk_unit_display_target FOREIGN KEY (target_unit_id) REFERENCES units(id) ON DELETE CASCADE,
 CONSTRAINT fk_unit_display_requester FOREIGN KEY (requested_by) REFERENCES users(id) ON DELETE SET NULL,
 CONSTRAINT fk_unit_display_accepter FOREIGN KEY (accepted_by) REFERENCES users(id) ON DELETE SET NULL,
 CONSTRAINT fk_unit_display_revoker FOREIGN KEY (revoked_by) REFERENCES users(id) ON DELETE SET NULL,
 CONSTRAINT chk_unit_display_distinct CHECK (source_unit_id<>target_unit_id),
 CONSTRAINT chk_unit_display_dates CHECK (active_until IS NULL OR active_until>=active_from)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;