emails

-- Table: emails

DROP TABLE emails;

CREATE TABLE emails
(
  id serial NOT NULL,
  subject text,
  created_at timestamp without time zone,
  updated_at timestamp without time zone,
  folder text,
  "references" text[],
  message_id text,
  CONSTRAINT emails_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);

-- Index: emails_message_id_index

-- DROP INDEX emails_message_id_index;

CREATE INDEX emails_message_id_index
  ON emails
  USING btree
  (message_id COLLATE pg_catalog."default");

 INSERT INTO emails(
    subject, created_at, updated_at, folder,  "references", message_id)
    VALUES ('root', now(), now(), 'INBOX', '{}', 'CAEBV8YT3W6XZSZph7hq0Nmc-mxZ7Ao+RorC=ZrKm1mh-QumjgA@mail.gmail.com');

INSERT INTO emails(
    subject, created_at, updated_at, folder,  "references", message_id)
    VALUES ('one', now(), now(), 'INBOX', '{CAEBV8YT3W6XZSZph7hq0Nmc-mxZ7Ao+RorC=ZrKm1mh-QumjgA@mail.gmail.com}','536b9d2354247_23fd346dd78f01108f@9369fd83-7877-4eec-8cc8-4b207abbae85.mail');

 INSERT INTO emails(
    subject, created_at, updated_at, folder,  "references", message_id)
    VALUES ('two', now(), now(), 'INBOX', '{CAEBV8YT3W6XZSZph7hq0Nmc-mxZ7Ao+RorC=ZrKm1mh-QumjgA@mail.gmail.com,536b9d2354247_23fd346dd78f01108f@9369fd83-7877-4eec-8cc8-4b207abbae85.mail}','CAEBV8YRm0-bmZ06UdWXbUyezRbTC=BtDo2xztH0-uYwUYtJ0Bw@mail.gmail.com');

INSERT INTO emails(
    subject, created_at, updated_at, folder,  "references", message_id)
    VALUES ('three', now(), now(), 'INBOX', '{CAEBV8YT3W6XZSZph7hq0Nmc-mxZ7Ao+RorC=ZrKm1mh-QumjgA@mail.gmail.com,536b9d2354247_23fd346dd78f01108f@9369fd83-7877-4eec-8cc8-4b207abbae85.mail,CAEBV8YRm0-bmZ06UdWXbUyezRbTC=BtDo2xztH0-uYwUYtJ0Bw@mail.gmail.com}','536b9d5b6d64c_23fd346dd78f0111bf@9369fd83-7877-4eec-8cc8-4b207abbae85.mail');

INSERT INTO emails(
    subject, created_at, updated_at, folder,  "references", message_id)
    VALUES ('four', now(), now(), 'INBOX', '{CAEBV8YT3W6XZSZph7hq0Nmc-mxZ7Ao+RorC=ZrKm1mh-QumjgA@mail.gmail.com,536b9d2354247_23fd346dd78f01108f@9369fd83-7877-4eec-8cc8-4b207abbae85.mail,CAEBV8YRm0-bmZ06UdWXbUyezRbTC=BtDo2xztH0-uYwUYtJ0Bw@mail.gmail.com,536b9d5b6d64c_23fd346dd78f0111bf@9369fd83-7877-4eec-8cc8-4b207abbae85.mail}','CAEBV8YR97FSa5Bf-_+NrkV+edDM0R-NwSQmW0CXGwyg0EuG6qg@mail.gmail.com');

SELECT DISTINCT e.id, folder, subject, message_id, "references", e.updated_at,
(select count(message_id)  from emails  where (select "references"[1] from emails where message_id = e.message_id) = ANY ("references") or message_id = (select "references"[1] from emails where message_id = e.message_id)) as replies
FROM "emails" e
WHERE ("folder" = 'INBOX');