DROP TABLE IF EXISTS `ohrm_email_queue`;
CREATE TABLE IF NOT EXISTS `ohrm_email_queue` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `to_email` text DEFAULT NULL,
  `cc_email` text DEFAULT NULL,
  `bcc_email` text DEFAULT NULL,
  `reply_to_email` text DEFAULT NULL,
  `from_email` varchar(60) NOT NULL,
  `subject` varchar(1000) NOT NULL,
  `type` varchar(100) NOT NULL,
  `content_type` enum('text/html','text/plain') DEFAULT NULL,
  `body` longtext NOT NULL,
  `status` enum('STARTED','SENT','PENDING','FAILED') CHARACTER SET latin1 DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `has_image` INT DEFAULT NULL,
  `ics_invitation_text` TEXT DEFAULT NULL,
  `ics_invitation_method` TEXT DEFAULT NULL,
  `served_by_process_id` int(5),
  `sender_name` VARCHAR(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

CREATE TABLE IF NOT EXISTS `ohrm_email_attachment` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `file_name` varchar(255) NOT NULL,
    `file_type` varchar(255) NOT NULL,
    `file_size` int(11) NOT NULL,
    `file_content` longblob,
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

CREATE TABLE IF NOT EXISTS `ohrm_email_queue_attachment` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `email_queue_id` int(11) NOT NULL,
    `email_attachment_id` int(11) NOT NULL,
    PRIMARY KEY (`id`),
    CONSTRAINT `fk_email_queue_attachment_email_queue` FOREIGN KEY (`email_queue_id`) REFERENCES `ohrm_email_queue` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
    CONSTRAINT `fk_email_queue_attachment_email_attachment` FOREIGN KEY (`email_attachment_id`) REFERENCES `ohrm_email_attachment` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;