ALTER TABLE rekognition_images
  ADD COLUMN processing_status ENUM('pending', 'indexed', 'no_face', 'failed')
    NOT NULL DEFAULT 'pending' AFTER file_size,
  ADD COLUMN detected_face_count INT UNSIGNED NULL AFTER processing_status,
  ADD COLUMN processing_error TEXT NULL AFTER detected_face_count;

UPDATE rekognition_images AS i
SET
  i.processing_status = CASE
    WHEN EXISTS (
      SELECT 1
      FROM rekognition_faces AS f
      WHERE f.image_id = i.id
    ) THEN 'indexed'
    ELSE 'failed'
  END,
  i.detected_face_count = (
    SELECT COUNT(*)
    FROM rekognition_faces AS f
    WHERE f.image_id = i.id
  ),
  i.processing_error = CASE
    WHEN EXISTS (
      SELECT 1
      FROM rekognition_faces AS f
      WHERE f.image_id = i.id
    ) THEN NULL
    ELSE 'Existing image has no indexed face record. Retry processing if needed.'
  END;