--- chat-user-inboxes

-- id matches user_id
CREATE TABLE chat_user_inboxes (
  id                     BIGINT PRIMARY KEY,
  user_id                BIGINT      NOT NULL,
  change_id              BIGINT,

  total_threads          INTEGER     NOT NULL DEFAULT 0,
  total_open_threads     INTEGER     NOT NULL DEFAULT 0,
  total_assigned_threads INTEGER     NOT NULL DEFAULT 0,

  created_at             TIMESTAMPTZ NOT NULL DEFAULT now(),
  changed_at             TIMESTAMPTZ NOT NULL DEFAULT now(),
  assigned_at            TIMESTAMPTZ
);

CREATE UNIQUE INDEX chat_user_inboxes_user_id_uniq
  ON chat_user_inboxes (user_id);

INSERT INTO chat_user_inboxes (
  id,
  user_id,
  created_at,
  changed_at)
  (SELECT
     m.user_id,
     m.user_id,
     min(m.created_at),
     min(m.created_at)
   FROM chat_members m
   GROUP BY m.user_id
  );

UPDATE chat_user_inboxes
SET total_threads = (
  SELECT count(*)
  FROM chat_user_threads t
  WHERE
    t.user_id = chat_user_inboxes.user_id
);

UPDATE chat_user_inboxes
SET total_open_threads = (
  SELECT count(*)
  FROM chat_user_threads t
  WHERE
    t.open = TRUE
    AND t.user_id = chat_user_inboxes.user_id
);

UPDATE chat_user_inboxes
SET total_assigned_threads = (
  SELECT count(*)
  FROM chats c
    JOIN chat_members m ON m.id = c.assignee_id
  WHERE
    m.user_id = chat_user_inboxes.user_id
);

-- chat-user-inbox-changes

CREATE TABLE chat_user_inbox_changes (
  id                     BIGINT PRIMARY KEY,
  user_id                BIGINT      NOT NULL REFERENCES users (id),
  inbox_id               BIGINT      NOT NULL REFERENCES chat_user_inboxes (id),
  total_threads          INTEGER     NOT NULL DEFAULT 0,
  total_open_threads     INTEGER     NOT NULL DEFAULT 0,
  total_assigned_threads INTEGER     NOT NULL DEFAULT 0,
  timestamp              TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE SEQUENCE chat_user_inbox_changes_id_seq;

CREATE INDEX chat_user_inbox_changes_inbox_id_ix
  ON chat_user_inbox_changes (inbox_id, id DESC);
