CREATE TABLE IF NOT EXISTS `trip_stop_state` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `trip_id` int(11) NOT NULL,
  `stop_id` int(11) NOT NULL,
  `state` enum('far','near','arrived','departed') NOT NULL DEFAULT 'far',
  `near_notified_at` datetime DEFAULT NULL,
  `arrived_notified_at` datetime DEFAULT NULL,
  `departed_notified_at` datetime DEFAULT NULL,
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_trip_stop` (`trip_id`,`stop_id`),
  KEY `idx_trip` (`trip_id`),
  KEY `idx_stop` (`stop_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;