CREATE TABLE IF NOT EXISTS rekognition_events (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  event_name VARCHAR(255) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_rekognition_events_event_name (event_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO rekognition_events (event_name)
SELECT 'Default Event'
WHERE NOT EXISTS (
  SELECT 1 FROM rekognition_events WHERE event_name = 'Default Event'
);

SET @default_event_id := (
  SELECT id FROM rekognition_events WHERE event_name = 'Default Event' LIMIT 1
);

ALTER TABLE rekognition_images
  ADD COLUMN event_id BIGINT UNSIGNED NULL AFTER id;

UPDATE rekognition_images
SET event_id = @default_event_id
WHERE event_id IS NULL;

ALTER TABLE rekognition_images
  MODIFY event_id BIGINT UNSIGNED NOT NULL,
  ADD KEY idx_rekognition_images_event_id (event_id),
  ADD CONSTRAINT fk_rekognition_images_event_id
    FOREIGN KEY (event_id) REFERENCES rekognition_events(id)
    ON DELETE RESTRICT;