--- chats-assignees

ALTER TABLE chats
  ADD changed_at DATETIMEOFFSET NOT NULL  DEFAULT sysdatetimeoffset();

UPDATE chats
SET changed_at = (
  CASE
  WHEN opened_at IS NOT NULL
    THEN opened_at
  ELSE created_at
  END
);

CREATE INDEX chats_assignee_id_ix
  ON chats (assignee_id)
  WHERE assignee_id IS NOT NULL;
CREATE INDEX chats_channel_id_changed_at_ix
  ON chats (channel_id, changed_at DESC);
CREATE INDEX chats_channel_id_open_changed_at_ix
  ON chats (channel_id, [open], changed_at DESC);

-- chat-members

ALTER TABLE chat_members
  ADD assigned_at DATETIMEOFFSET;
