ALTER TABLE team_message_thread_reads
  ADD COLUMN last_read_message_id CHAR(36) NULL AFTER user_id;

UPDATE team_message_thread_reads read_state
SET last_read_message_id = (
  SELECT msg.id
  FROM team_messages msg
  WHERE msg.law_firm_id = read_state.law_firm_id
    AND msg.team_id = read_state.team_id
    AND msg.thread_id = read_state.thread_id
    AND msg.created_at = read_state.last_read_at
  ORDER BY msg.id DESC
  LIMIT 1
)
WHERE read_state.last_read_at IS NOT NULL
  AND read_state.last_read_message_id IS NULL;
