CREATE TABLE `EmailAddress` (
  `id`           INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name`         VARCHAR(255)     NOT NULL,
  `address`      VARCHAR(255)     NOT NULL,
  `creationDate` TIMESTAMP        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `rawInput`     VARCHAR(255)     NOT NULL,
  `userId`       INT(10) UNSIGNED NULL     DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `email` (`address`),
  INDEX `creationDate` (`creationDate`),
  INDEX `name` (`name`),
  INDEX `FK1-User-id` (`userId`),
  CONSTRAINT `FK1-User-id` FOREIGN KEY (`userId`) REFERENCES `User` (`id`)
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
)
  COLLATE = 'utf8_general_ci'
  ENGINE = InnoDB;

CREATE TABLE `Email` (
  `id`                 INT(10) UNSIGNED    NOT NULL AUTO_INCREMENT,
  `fromEmailAddressId` INT(10) UNSIGNED    NOT NULL,
  `subject`            VARCHAR(255)        NOT NULL,
  `htmlBody`           TEXT                NULL,
  `altBody`            TEXT                NULL,
  `creationDate`       TIMESTAMP           NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `submissionDate`     TIMESTAMP           NULL     DEFAULT NULL,
  `lastUpdate`         TIMESTAMP           NULL     DEFAULT NULL,
  `providerEmailId`    VARCHAR(255)        NULL     DEFAULT NULL,
  `providerName`       VARCHAR(255)        NULL     DEFAULT NULL,
  `isError`            TINYINT(1) UNSIGNED NULL     DEFAULT '0',
  PRIMARY KEY (`id`),
  INDEX `from` (`fromEmailAddressId`),
  INDEX `subject` (`subject`(191)),
  INDEX `creationDate` (`creationDate`),
  CONSTRAINT `FK1-EmailAddress-id` FOREIGN KEY (`fromEmailAddressId`) REFERENCES `EmailAddress` (`id`)
    ON UPDATE NO ACTION
    ON DELETE NO ACTION
)
  COLLATE = 'utf8_general_ci'
  ENGINE = InnoDB;

CREATE TABLE EmailStatus (
  id          INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  title       VARCHAR(50)      NOT NULL,
  code        VARCHAR(50)      NOT NULL,
  isSent      VARCHAR(255)     NOT NULL,
  isDelivered VARCHAR(255)     NOT NULL,
  PRIMARY KEY (id)
)
  ENGINE = INNODB
  CHARACTER SET utf8
  COLLATE utf8_general_ci;

INSERT INTO EmailStatus (id,title, code, isSent, isDelivered) VALUES (1,'pending', 'p', '0', '0');
INSERT INTO EmailStatus (id,title, code, isSent, isDelivered) VALUES (2,'notQueued', 'n', '0', '0');
INSERT INTO EmailStatus (id,title, code, isSent, isDelivered) VALUES (3,'accepted', 'a', '1', '0');
INSERT INTO EmailStatus (id,title, code, isSent, isDelivered) VALUES (4,'delivered', 'd', '1', '1');
INSERT INTO EmailStatus (id,title, code, isSent, isDelivered) VALUES (5,'dropped', 'r', '1', '0');


CREATE TABLE EmailRecipient (
  emailId        INT(10) UNSIGNED         NOT NULL,
  emailAddressId INT(10) UNSIGNED         NOT NULL,
  typeTo         ENUM ('TO', 'CC', 'BCC') NOT NULL,
  emailStatusId  INT(10) UNSIGNED         NOT NULL DEFAULT 1,
  responseDate   TIMESTAMP                NULL     DEFAULT NULL,
  PRIMARY KEY (emailAddressId, emailId, typeTo),
  CONSTRAINT `FK1-EmailStatus-id` FOREIGN KEY (emailStatusId)
  REFERENCES EmailStatus (id)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `FK2-EmailAddress-id` FOREIGN KEY (emailAddressId)
  REFERENCES EmailAddress (id)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `FK3-Email-id` FOREIGN KEY (emailId)
  REFERENCES Email (id)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
)
  ENGINE = INNODB
  CHARACTER SET utf8
  COLLATE utf8_general_ci;



