DROP VIEW user_accessible_emails;
DROP TABLE email_participants;
DROP TABLE emails;
DROP TABLE email_addresses;
DROP TABLE contacts;
DROP TABLE users;
DROP TABLE accounts;

CREATE TABLE accounts
(
  id integer NOT NULL,
  name text NOT NULL,
  CONSTRAINT accounts_pkey PRIMARY KEY (id)
);

INSERT INTO Accounts (id, name)
VALUES (1, 'DUMMY ACCOUNT');

CREATE TABLE users
(
  id integer NOT NULL,
  first_name text,
  last_name text,
  account_id integer NOT NULL,
  CONSTRAINT users_pkey PRIMARY KEY (id),
  CONSTRAINT users_account_id_fkey FOREIGN KEY (account_id)
      REFERENCES accounts (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE
);

INSERT INTO users (id, first_name, last_name, account_id)
values (1, 'Paul', 'Cowan', 1);

INSERT INTO users (id, first_name, last_name, account_id)
values (2, 'Other', 'User', 1);

-- Table: contacts

-- DROP TABLE contacts;

CREATE TABLE contacts
(
  id integer NOT NULL,
  name text,
  assigned_to_id integer,
  ignored boolean DEFAULT false,
  account_id integer NOT NULL,
  CONSTRAINT contacts_pkey PRIMARY KEY (id),
  CONSTRAINT contacts_account_id_fkey FOREIGN KEY (account_id)
      REFERENCES accounts (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE,
  CONSTRAINT contacts_assigned_to_id_fkey FOREIGN KEY (assigned_to_id)
      REFERENCES users (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE SET NULL
);

CREATE INDEX contacts_account_id_index
  ON contacts
  USING btree
  (account_id);

CREATE INDEX contacts_assigned_to_id_index
  ON contacts
  USING btree
  (assigned_to_id);

INSERT INTO Contacts (id, name, assigned_to_id, account_id)
VALUES (1, 'Bob Champion', 1, 1);

INSERT INTO Contacts (id, name, assigned_to_id, account_id)
VALUES (2, 'Michael Collins', 1, 1);

INSERT INTO Contacts (id, name, assigned_to_id, account_id)
VALUES (3, 'Susan Cutter', 1, 1);

INSERT INTO Contacts (id, name, assigned_to_id, account_id)
VALUES (4, 'Patrick Bateman', 1, 1);

INSERT INTO Contacts (id, name, assigned_to_id, account_id)
VALUES (5, 'Colin Pascoe', 1, 1);

CREATE TABLE email_addresses
(
  id integer NOT NULL,
  name text,
  address text NOT NULL,
  "primary" boolean,
  contact_id integer,
  CONSTRAINT email_addresses_pkey PRIMARY KEY (id),
  CONSTRAINT email_addresses_contact_id_fkey FOREIGN KEY (contact_id)
      REFERENCES contacts (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE
);

CREATE INDEX email_addresses_contact_id_index
  ON email_addresses
  USING btree
  (contact_id);

INSERT INTO email_addresses(id, name, address, "primary", contact_id)
VALUES(1, 'work', 'bob.champion@gmail.com', true, 1);

INSERT INTO email_addresses(id, name, address, "primary", contact_id)
VALUES(2, 'work', 'michael.collins@gmail.com', true, 1);

INSERT INTO email_addresses(id, name, address, "primary", contact_id)
VALUES(3, 'work', 'susan.cutter@gmail.com', true, 1);

INSERT INTO email_addresses(id, name, address, "primary", contact_id)
VALUES(4, 'work', 'patrick.bateman@gmail.com', true, 1);

INSERT INTO email_addresses(id, name, address, "primary", contact_id)
VALUES(5, 'work', 'colin.pascoe@gmail.com', true, 1);

CREATE TABLE emails
(
  id integer NOT NULL,
  subject text,
  html text,
  folder text,
  personal boolean DEFAULT false,
  sent_at timestamp without time zone,
  account_id integer NOT NULL,
  sender_user_id integer,
  sender_contact_id integer,
  CONSTRAINT emails_pkey PRIMARY KEY (id),
  CONSTRAINT emails_account_id_fkey FOREIGN KEY (account_id)
      REFERENCES accounts (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE,
  CONSTRAINT emails_sender_contact_id_fkey FOREIGN KEY (sender_contact_id)
      REFERENCES contacts (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE,
  CONSTRAINT emails_sender_user_id_fkey FOREIGN KEY (sender_user_id)
      REFERENCES users (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE
);

CREATE INDEX emails_account_id_index
  ON emails
  USING btree
  (account_id);

CREATE INDEX emails_sender_contact_id_index
  ON emails
  USING btree
  (sender_contact_id);

CREATE INDEX emails_sender_user_id_index
  ON emails
  USING btree
  (sender_user_id);

INSERT INTO emails (id, subject, html, folder, sent_at, account_id, sender_user_id, sender_contact_id)
VALUES (1, 'bob champion', '<b>Html</b>', 'INBOX', now(), 1, null, 1);

INSERT INTO emails (id, subject, html, folder, sent_at, account_id, sender_user_id, sender_contact_id)
VALUES (2, 'michael collins', '<b>Html</b>', 'INBOX', now(), 1, null, 2);

INSERT INTO emails (id, subject, html, folder, sent_at, account_id, sender_user_id, sender_contact_id)
VALUES (3, 'susan cutter', '<b>Html</b>', 'INBOX', now(), 1, null, 3);

INSERT INTO emails (id, subject, html, folder, sent_at, account_id, sender_user_id, sender_contact_id)
VALUES (4, 'patrick bateman', '<b>Html</b>', 'INBOX', now(), 1, null, 4);

INSERT INTO emails (id, subject, html, folder, sent_at, account_id, sender_user_id, sender_contact_id)
VALUES (5, 'colin pascoe', '<b>Html</b>', 'INBOX', now(), 1, null, 5);

INSERT INTO emails (id, subject, html, folder, sent_at, account_id, sender_user_id, sender_contact_id)
VALUES (6, 'other user', '<b>Html</b>', 'INBOX', now(), 1, 2, null);

CREATE TABLE email_participants
(
  id integer NOT NULL,
  kind text NOT NULL,
  email_id integer NOT NULL,
  user_id integer,
  contact_id integer,
  CONSTRAINT email_participants_pkey PRIMARY KEY (id),
  CONSTRAINT email_participants_contact_id_fkey FOREIGN KEY (contact_id)
      REFERENCES contacts (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE,
  CONSTRAINT email_participants_email_id_fkey FOREIGN KEY (email_id)
      REFERENCES emails (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE,
  CONSTRAINT email_participants_user_id_fkey FOREIGN KEY (user_id)
      REFERENCES users (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE
);

INSERT INTO email_participants(id, kind, email_id, user_id, contact_id)
VALUES(1, 'to', 1, 1, null);
INSERT INTO email_participants(id, kind, email_id, user_id, contact_id)
VALUES(2, 'sent', 1, null, 1);

INSERT INTO email_participants(id, kind, email_id, user_id, contact_id)
VALUES(3, 'to', 2, 1, null);
INSERT INTO email_participants(id, kind, email_id, user_id, contact_id)
VALUES(4, 'sent', 2, null, 2);

INSERT INTO email_participants(id, kind, email_id, user_id, contact_id)
VALUES(5, 'to', 3, 1, null);
INSERT INTO email_participants(id, kind, email_id, user_id, contact_id)
VALUES(6, 'sent', 3, null, 3);

INSERT INTO email_participants(id, kind, email_id, user_id, contact_id)
VALUES(7, 'to', 4, 1, null);
INSERT INTO email_participants(id, kind, email_id, user_id, contact_id)
VALUES(8, 'sent', 4, null, 4);

INSERT INTO email_participants(id, kind, email_id, user_id, contact_id)
VALUES(9, 'to', 5, 1, null);
INSERT INTO email_participants(id, kind, email_id, user_id, contact_id)
VALUES(10, 'sent', 5, null, 5);

INSERT INTO email_participants(id, kind, email_id, user_id, contact_id)
VALUES(11, 'to', 6, 1, null);
INSERT INTO email_participants(id, kind, email_id, user_id, contact_id)
VALUES(12, 'sent', 6, 2, null);

CREATE OR REPLACE VIEW user_accessible_emails AS
 SELECT emails.id AS email_id,
    users.id AS user_id
   FROM emails
     JOIN accounts ON emails.account_id = accounts.id
     JOIN users ON users.account_id = accounts.id
  WHERE emails.personal = false
UNION
 SELECT emails.id AS email_id,
    email_participants.user_id
   FROM emails
     JOIN email_participants ON email_participants.email_id = emails.id
  WHERE emails.personal = true AND email_participants.user_id IS NOT NULL;

WITH "contacts_conv" AS
(
	SELECT * FROM
	(
		SELECT "emails"."id", "email_participants"."contact_id", "emails"."sent_at", "emails"."subject",
		rank() OVER (PARTITION BY "email_participants"."contact_id" ORDER BY "sent_at" DESC) AS "contact_rnk"
		FROM "emails"
		INNER JOIN "email_participants"
		ON ("email_participants"."email_id" = "emails"."id")
		INNER JOIN "user_accessible_emails"
		ON ("user_accessible_emails"."email_id" = "emails"."id")
		INNER JOIN "contacts" ON ("emails"."sender_contact_id" = "contacts"."id")
		WHERE
		(
			("emails"."sent_at" IS NOT NULL) AND
			("ignored" IS NOT TRUE) AND
			("emails"."folder" != 'archived') AND
			("user_accessible_emails"."user_id" = 1) AND
			("email_participants"."contact_id" IS NOT NULL) AND
			("email_participants"."kind" = 'sent')
		)
	)
	AS "e" WHERE ("contact_rnk" = 1)
), "users_conv" AS
(
	SELECT * FROM
	(
		SELECT "emails"."id", "email_participants"."contact_id", "emails"."sent_at", "emails"."subject",
		rank() OVER (PARTITION BY "email_participants"."contact_id" ORDER BY "sent_at" DESC) AS "contact_rnk"
		FROM "emails"
		INNER JOIN "email_participants"
		ON ("email_participants"."email_id" = "emails"."id")
		INNER JOIN "user_accessible_emails"
		ON ("user_accessible_emails"."email_id" = "emails"."id")
		WHERE
		(
			("emails"."folder" != 'archived') AND ("emails"."sent_at" IS NOT NULL) AND
			("user_accessible_emails"."user_id" = 1) AND
			("email_participants"."contact_id" IS NOT NULL) AND
			("emails"."sender_user_id" IS NOT NULL)
		)
	)
	AS "e" WHERE ("contact_rnk" = 1)
)
SELECT DISTINCT "emails".* FROM "emails"
INNER JOIN "contacts_conv" ON ("emails"."id" = "contacts_conv"."id")
LEFT JOIN "users_conv" ON ("contacts_conv"."contact_id" = "users_conv"."contact_id")
WHERE
(
	("users_conv"."sent_at" IS NULL) OR
	("contacts_conv"."sent_at" > "users_conv"."sent_at")
) ORDER BY "emails"."sent_at" DESC;