replies

--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,
  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, message_id)
    VALUES ('howzitgoin', now(), now(), null, '53856b1448c89_23fa9605badd015951@3a139e8c-0b81-42c2-8e59-133c262e96a9.mail');

INSERT INTO emails(
    subject, created_at, updated_at, folder, message_id)
    VALUES ('Re: howzitgoin', now(), now(), 'INBOX', 'CAEBV8YTu_A6LtP_uGuQ-QSVj3zojWUiwcjGZpsPPEz1Pj3_i1A@mail.gmail.com');

 INSERT INTO emails(
    subject, created_at, updated_at, folder, message_id)
    VALUES ('Re: howzitgoin', now(), now(), 'INBOX', '53856b88a2a09_23fa9605badd01601b@3a139e8c-0b81-42c2-8e59-133c262e96a9.mail');

INSERT INTO emails(
    subject, created_at, updated_at, folder, message_id)
    VALUES ('Re: howzitgoin', now(), now(), 'INBOX', 'CAEBV8YT6vx6buOKUga4f=bcNGq_=WzwiqEzm2FWm3HoLZ8SbJA@mail.gmail.com');

INSERT INTO emails(
    subject, created_at, updated_at, folder,  message_id)
    VALUES ('Re: howzitgoin', now(), now(), 'INBOX', 'CAEBV8YRGwkNSb3cxquS5abSCmnwLn37GxCpg74mQe7=3SC5cdQ@mail.gmail.com');

INSERT INTO emails(
    subject, created_at, updated_at, folder,  message_id)
    VALUES ('Not part of thread', now(), now(), 'INBOX', 'CAEBV8YRGwkNSb3cxquS5abSCmnwLn37GxCpg74mQe7=3SC5cdQ@mail.gmail.com');

-- Table: replies

-- DROP TABLE replies;

CREATE TABLE replies
(
  id serial NOT NULL,
  message_id text,
  created_at timestamp without time zone,
  email_id integer NOT NULL,
  CONSTRAINT replies_pkey PRIMARY KEY (id),
  CONSTRAINT replies_email_id_fkey FOREIGN KEY (email_id)
      REFERENCES emails (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE
)
WITH (
  OIDS=FALSE
);


-- Index: replies_email_id_index

-- DROP INDEX replies_email_id_index;

CREATE INDEX replies_email_id_index
  ON replies
  USING btree
  (email_id);

-- Index: replies_message_id_index

-- DROP INDEX replies_message_id_index;

CREATE INDEX replies_message_id_index
  ON replies
  USING btree
  (message_id COLLATE pg_catalog."default");



INSERT INTO replies(message_id, created_at, email_id)
            VALUES ('53856b1448c89_23fa9605badd015951@3a139e8c-0b81-42c2-8e59-133c262e96a9.mail', now(), 2);

INSERT INTO replies(message_id, created_at, email_id)
            VALUES ('53856b1448c89_23fa9605badd015951@3a139e8c-0b81-42c2-8e59-133c262e96a9.mail', now(), 3);

INSERT INTO replies(message_id, created_at, email_id)
            VALUES ('CAEBV8YTu_A6LtP_uGuQ-QSVj3zojWUiwcjGZpsPPEz1Pj3_i1A@mail.gmail.com', now(), 3);

INSERT INTO replies(message_id, created_at, email_id)
            VALUES ('53856b88a2a09_23fa9605badd01601b@3a139e8c-0b81-42c2-8e59-133c262e96a9.mail', now(), 4);

INSERT INTO replies(message_id, created_at, email_id)
            VALUES ('CAEBV8YTu_A6LtP_uGuQ-QSVj3zojWUiwcjGZpsPPEz1Pj3_i1A@mail.gmail.com', now(), 4);

INSERT INTO replies(message_id, created_at, email_id)
            VALUES ('53856b1448c89_23fa9605badd015951@3a139e8c-0b81-42c2-8e59-133c262e96a9.mail', now(), 4);

INSERT INTO replies(message_id, created_at, email_id)
            VALUES ('53856b88a2a09_23fa9605badd01601b@3a139e8c-0b81-42c2-8e59-133c262e96a9.mail', now(), 5);

INSERT INTO replies(message_id, created_at, email_id)
            VALUES ('CAEBV8YTu_A6LtP_uGuQ-QSVj3zojWUiwcjGZpsPPEz1Pj3_i1A@mail.gmail.com', now(), 5);

INSERT INTO replies(message_id, created_at, email_id)
            VALUES ('53856b1448c89_23fa9605badd015951@3a139e8c-0b81-42c2-8e59-133c262e96a9.mail', now(), 5);


SELECT  "emails".id, "emails".message_id, subject, "count", "emails".created_at, "count"
FROM "emails"
LEFT JOIN
(
  SELECT "email_id", count(*) AS "count" FROM "replies" GROUP BY "email_id"
) AS "r"
ON ("emails"."id" = "r"."email_id")
WHERE (("folder" = 'INBOX')
  --AND ("emails"."id" NOT IN (SELECT "email_id" FROM "replies"))
) ORDER BY "created_at" DESC LIMIT 7 OFFSET 0